DATABASE MANAGEMENT SYSTEM Chapter 6 Structured Query Language
DATABASE MANAGEMENT SYSTEM Chapter 6 Structured Query Language (SQL) 1
Introduction to SQL • SQL meets ideal database language requirements: – SQL coverage fits into two categories: • Data definition • Data manipulation – SQL is relatively easy to learn. – ANSI prescribes a standard SQL. 2
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. 3
• 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. 4
5
• Creating the Database Structure CREATE DATABASE <database name>; – Example: CREATE DATABASE CH 3; 6
Some Common SQL Data Types Data Type Format Numeric NUMBER(L, D) INTEGER SMALLINT DECIMAL(L, D) Character CHAR(L) VARCHAR(L) Date DATE 7
• Creating Table Structures CREATE TABLE <table name>( <attribute 1 name and attribute 1 characteristics, attribute 2 name and attribute 2 characteristics, attribute 3 name and attribute 3 characteristics, primary key designation, foreign key designation and foreign key requirements>); 8
CREATE TABLE VENDOR (V_CODE FCHAR(5) NOT NULLUNIQUE, V_NAME VCHAR(35) NOT NULL, V_CONTACT VCHAR(15) NOT NULL, V_AREACODE FCHAR(3) NOT NULL, V_PHONE FCHAR(3) NOT NULL, V_STATE FCHAR(2) NOT NULL, V_ORDER FCHAR(1) NOT NULL, PRIMARY KEY (V_CODE)); 9
CREATE TABLE PRODUCT( P_CODE VCHAR(10) NOT NULL UNIQUE, P_DESCRIPT VCHAR(35) NOT NULL, P_INDATE NOT NULL, P_ONHAND SMALLINT NOT NULL, P_MIN SMALLINT NOT NULL, P_PRICE DECIMAL(8, 2) NOT NULL, P_DISCOUNT DECIMAL(4, 1) NOT NULL, V_CODE SMALLINT, PRIMARY KEY (P_CODE), FOREIGN KEY (V_CODE) REFERENCES VENDOR ON DELETE RESTRICT ON UPDATE CASCADE); 10
• SQL Integrity Constraints – Entity Integrity • PRIMARY KEY • NOT NULL and UNIQUE – Referential Integrity • FOREIGN KEY • ON DELETE • ON UPDATE 11
SQL Command Coverage 12
Basic Data Management • Data Entry INSERT INTO <table name> VALUES (attribute 1 value, attribute 2 value, … etc. ); INSERT INTO VENDOR VALUES(‘ 21225, ’Bryson, Inc. ’, ’Smithson’, ’ 615’, ’ 223 -3234’, ’TN’, ’Y’); INSERT INTO PRODUCT VALUES(‘ 11 QER/31’, ’Power painter, 15 psi. , 3 -nozzle’, ’ 07/02/1999’, 8. 5, 109. 99, 0. 00, 25595); 13
• Saving the Table Contents COMMIT <table names>; COMMIT PRODUCT; • 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; 14
• Making a Correction UPDATE PRODUCT SET P_INDATE = ‘ 12/11/96’ WHERE P_CODE = ‘ 13 -Q 2/P 2’; UPDATE PRODUCT SET P_INDATE = ‘ 12/11/96’, P_PRICE = 15. 99, P_MIN=10 WHERE P_CODE = ‘ 13 -Q 2/P 2’; • Restoring the Table Contents ROLLBACK 15
• Deleting Table Rows DELETE FROM PRODUCT WHERE P_CODE = ‘ 2238/QPD’; DELETE FROM PRODUCT WHERE P_MIN = 5; 16
Queries • Partial Listing of Table Contents SELECT <column(s)> FROM <table name> WHERE <conditions>; SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE = 21344; 17
Mathematical Operators 18
SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE <> 21344; 19
SELECT P_DESCRIPT, P_ONHAND, P_MIN, P_PRICE FROM PRODUCT WHERE P_PRICE <= 10; 20
• Using Mathematical Operators on Character Attributes SELECT P_DESCRIPT, P_ONHAND, P_MIN, P_PRICE FROM PRODUCT WHERE P_CODE < ‘ 1558 -QWI’; 21
• Using Mathematical Operators on Dates SELECT P_DESCRIPT, P_ONHAND, P_MIN, P_PRICE, P_INDATE FROM PRODUCT WHERE P_INDATE >= ‘ 08/15/1999’; 22
• Logical Operators: AND, OR, and NOT SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE = 21344 OR V_CODE = 24288; 23
SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE P_PRICE < 50 AND P_INDATE > ‘ 07/15/1999’; 24
SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE (P_PRICE < 50 AND P_INDATE > ‘ 07/15/1999’) OR V_CODE = 24288; 25
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. 26
BETWEEN is used to define range limits. SELECT * FROM PRODUCT WHERE P_PRICE BETWEEN 50. 00 AND 100. 00; SELECT * FROM PRODUCT WHERE P_PRICE > 50. 00 AND P_PRICE < 100. 00; 27
IS NULL is used to check whether an attribute value is null. SELECT P_CODE, P_DESCRIPT FROM PRODUCT WHERE P_MIN IS NULL; SELECT P_CODE, P_DESCRIPT FROM PRODUCT WHERE P_INDATE IS NULL; 28
LIKE is used to check for similar character strings. SELECT * FROM VENDOR WHERE V_CONTACT LIKE ‘Smith%’; SELECT * FROM VENDOR WHERE V_CONTACT LIKE ‘SMITH%’; 29
IN is used to check whether an attribute value matches a value contained within a (sub)set of listed values. SELECT * FROM PRODUCT WHERE V_CODE IN (21344, 24288); EXISTS is used to check whether an attribute has value. DELETE FROM PRODUCT WHERE P_CODE EXISTS; SELECT * FROM PRODUCT WHERE V_CODE EXISTS; 30
Advanced Data Management Commands • Changing Table Structures ALTER TABLE <table name> MODIFY (<column name> <new column characteristics>); ALTER TABLE <table name> ADD (<column name> <new column characteristics>); 31
• Changing a Column’s Data Type ALTER TABLE PRODUCT MODIFY (V_CODE CHAR(5)); • Changing Attribute Characteristics ALTER TABLE PRODUCT MODIFY (P_PRICE DECIMAL(9, 2)); • Adding a New Column to the Table ALTER TABLE PRODUCT ADD (P_SALECODE CHAR(1)); 32
The Arithmetic Operators 33
• Deleting a Table from the Database – DROP TABLE <table name>; DROP TABLE PART; 34
- Slides: 34