* The basic commands and functions of SQL
* How to use SQL for data administration (to create tables, indexes, and views)
* How to use SQL for data manipulation (to add, modify, delete, and retrieve data)
* How to use SQL to query a database to extract useful information
Introduction to SQL Part I
* The relational DBMS is the standard for database management.
* The Structured Query Language, SQL, is the standard for working with them
* This chapter is an introduction to essential SQL.
SQL strengths
Covers both
*
Data definition
*
Data manipulation
SQL is relatively easy to learn.
ANSI prescribes a standard SQL.
SQL Weaknesses
*
Some eccentric notation
o
use of ' marks; in strings, rather than "
o
Wildcards: % instead of *
*
Some things are hard to do
*
Different Vendors implement different dialects
*
Not a good conceptual match to most programming language
*
Strictly DDL and DML no standard procedural language.
DB2 concepts
* DB2 consists of multiple "instances" on each server (we have one)
* Within each instance there are databases: we have two, SAMPLE and DBMS, and will be using DBMS
* Within the databases are schemas one for each user.
* Authorization for users is via the system (cs1 account). Your schema name and user name are the same as your username on CS1
Setup for demonstrations
1. Use the winsql program to connect to the datasource DBMS using your CS1 username and password
2. Perform the following commands to create the tables:
drop table vendor; drop table product; drop table customer;
create table vendor like CH06_SALESCO.vendor;
insert into vendor select * from CH06_SALESCO.vendor;
alter table vendor add primary key (v_code);
create table product like CH06_SALESCO.product;
insert into product select * from CH06_SALESCO.product;
alter table product add primary key (p_code)
add foreign key (v_code) references vendor on delete set null on update restrict;
Data Definition Commands
The Database Model
Simple Database -- PRODUCT and VENDOR tables
Each product is supplied by only a single vendor.
A vendor may supply many products.
Data Definition Commands
The Tables and Their Components
*
The VENDOR table contains vendors who are not referenced in the PRODUCT table. PRODUCT is optional to VENDOR.
*
Existing V_CODE values in the PRODUCT table must have a match in the VENDOR table.
*
A few products are supplied factory-direct, a few are made in-house, and a few may have been bought in a special warehouse sale. That is, a product is not necessarily supplied by a vendor. VENDOR is optional to PRODUCT.
Common SQL Datatypes
Data Type
SQL
Numeric
NUMBER(L,D)
DECIMAL(L,D)
INTEGER
SMALLINT
Character
CHAR (L)
VARCHAR (L)
Date
DATE
Data Definition Commands
Creating the Database Structure
This varies among databases. In DB2 there are instances
within each instance there are databases
within each database there are schemas (one for each user). DB2 users are the same as Operating System users.
Statements in DB2 referencing a table include schema (SELECT * FROM schema.tablename) (current schema are implicit).
Data Definition Commands
Creating Table Structures
CREATE TABLE
(
);
Data Definition Commands
CREATE TABLE VENDOR(
V_CODE INTEGER NOT NULL PRIMARY KEY DEFAULT 0,
V_NAME VARCHAR(15),
V_CONTACT VARCHAR(50),
V_AREACODE VARCHAR(3),
V_PHONE VARCHAR(8),
V_STATE VARCHAR(2),
V_ORDER VARCHAR(1)
)
Data Definition Commands
CREATE TABLE PRODUCT(
P_CODE VARCHAR(10) NOT NULL PRIMARY KEY,
P_DESCRIPT VARCHAR(35),
P_INDATE DATE,
P_ONHAND SMALLINT DEFAULT 0,
P_MIN SMALLINT DEFAULT 0,
P_PRICE DECIMAL(15, 2) DEFAULT 0,
P_DISCOUNT DOUBLE DEFAULT 0,
V_CODE INTEGER DEFAULT 0 REFERENCES VENDOR(V_CODE)
)
Data Definition Commands
SQL Integrity Constraints
Entity Integrity
PRIMARY KEY
NOT NULL and UNIQUE
Referential Integrity
FOREIGN KEY
ON DELETE
ON UPDATE
Check Constraint
Validates data when an attribute value is entered
Basic Data Management
Data Entry
INSERT INTO
VALUES (attribute 1 value, attribute 2 value, ... etc.);
INSERT INTO VENDOR VALUES(26000, 'Quality Tools', 'Johnson', '915','555-3234', 'TX', 'N');
INSERT INTO PRODUCT VALUES('14 ABC12', 'Concrete Saw', '09/02/1996', 2, 1, 510.99, 0.00, 26000, '');
Basic Data Management
Committing Changes
Changes do not take place until they are committed assuming autocommit is off. Many end user query environments (including ours) do not support turning autocommit off.
COMMIT ;
Listing the Table Contents
SELECT * FROM PRODUCT;
SELECT P_CODE, P_DESCRIPT, P_INDATE, P_ONHAND,
P_MIN, P_PRICE, P_DISCOUNT, V_CODE
FROM PRODUCT;
Basic Data Management
Making a Correction
UPDATE PRODUCT SET P_INDATE = '2003-11-15'
WHERE P_CODE = '13-Q2/P2';
UPDATE PRODUCT SET P_INDATE = '2003-11-15', P_PRICE = 15.99, P_MIN = 10
WHERE P_CODE = '13-Q2/P2';
Restoring the Table Contents (assumes autocommit).
ROLLBACK
Basic Data Management
Deleting Table Rows
DELETE FROM PRODUCT WHERE P_CODE = '2238/QPD';
DELETE FROM PRODUCT WHERE P_MIN = 5;
Delete is a dangerous command. Typing:
DELETE FROM
will neatly delete all the records in the table!
Queries
Partial Listing of Table Contents
SELECT FROM
WHERE ;
SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE
FROM PRODUCT
WHERE V_CODE = 21344;
SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE
FROM PRODUCT
WHERE V_CODE <> 21344;
SELECT P_DESCRIPT, P_ONHAND, P_MIN, P_PRICE
FROM PRODUCT
WHERE P_PRICE <= 10; Queries Using Mathematical Operators on Character Attributes SELECT P_DESCRIPT, P_ONHAND, P_MIN, P_PRICE FROM PRODUCT WHERE P_CODE < '1558-QWI'; Using Mathematical Operators on Dates SELECT P_DESCRIPT, P_ONHAND, P_MIN, P_PRICE, P_INDATE FROM PRODUCT WHERE P_INDATE >= '01/01/2004';
Queries
Logical Operators: AND, OR, and NOT
SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE
FROM PRODUCT
WHERE V_CODE = 21344 OR V_CODE = 21225;
SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE
FROM PRODUCT
WHERE P_PRICE <> '01/01/2004';
SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE
FROM PRODUCT
WHERE (P_PRICE <> '01/01/2004')
OR V_CODE = 24288;
Queries: Special Operators
*
BETWEEN - used to define range limits.
*
IS NULL - used to check whether an attribute value is null
*
LIKE - used to check for similar character strings.
*
IN - used to check whether an attribute value matches a value contained within a (sub)set of listed values.
*
EXISTS - used to check whether an attribute has a value. In effect, EXISTS is the opposite of IS NULL.
Queries: Special Operators
BETWEEN is used to define range limits.
SELECT *
FROM PRODUCT
WHERE P_PRICE BETWEEN 10.00 AND 100.00;
SELECT * FROM PRODUCT
WHERE P_PRICE > 10.00 AND P_PRICE <>
ADD ( );
Changing a Column's Data Type
Probably illegal in DB2
ALTER TABLE PRODUCT MODIFY (V_CODE CHAR(5));
ALTER TABLE PRODUCT MODIFY (P_PRICE DECIMAL(9,2));
Adding a New Column to the Table
ALTER TABLE PRODUCT ADD column P_SALECODE CHAR(1) ;
Updating Data
UPDATE PRODUCT SET P_SALECODE = '2' WHERE P_CODE = '1546-QQ2';
UPDATE PRODUCT SET P_SALECODE = '1' WHERE P_CODE IN ('13-Q2/P2', '2232/QTY');
UPDATE PRODUCT SET P_SALECODE = '2' WHERE P_INDATE < '01/01/2004'; UPDATE PRODUCT SET P_SALECODE = '1' WHERE P_INDATE >= '01/01/2004'AND P_INDATE < '10/20/2004'; Copying Tables Copying table definitions and data CREATE TABLE NEWPRODUCT LIKE PRODUCT; INSERT INTO NEWPRODUCT SELECT * FROM PRODUCT; Copying Parts of Tables CREATE TABLE PART (PART_CODE CHAR(8) NOT NULL UNIQUE, PART_DESCRIPT CHAR(35), PART_PRICE DECIMAL(8,2), PRIMARY KEY(PART_CODE)); INSERT INTO PART (PART_CODE, PART_DESCRIPT, PART_PRICE) SELECT P_CODE, P_DESCRIPT, P_PRICE FROM PRODUCT; Deleting a Table from the Database DROP TABLE
;
DROP TABLE PART;
Primary and Foreign Key Designation
(Note we did these when we created the table)
ALTER TABLE PRODUCT ADD PRIMARY KEY (P_CODE);
ALTER TABLE PRODUCT ADD FOREIGN KEY (V_CODE) REFERENCES VENDOR;
ALTER TABLE PRODUCT ADD PRIMARY KEY (P_CODE) ADD FOREIGN KEY (V_CODE) REFERENCES VENDOR;
Notes
No comments:
Post a Comment