Database Systems Design Implementation and Management Tenth Edition
Database Systems: Design, Implementation, and Management Tenth Edition Chapter 7 Introduction to Structured Query Language (SQL)
Introduction to SQL • SQL functions fit into two broad categories: – Data definition language – Data manipulation language • Basic command set has vocabulary of fewer than 100 words • American National Standards Institute (ANSI) prescribes a standard SQL • Several SQL dialects exist Database Systems, 10 th Edition 2
Database Systems, 10 th Edition 3
Database Systems, 10 th Edition 4
Data Definition Commands • The database model – In this chapter, a simple database with these tables is used to illustrate commands: • • • CUSTOMER INVOICE LINE PRODUCT VENDOR – Focus on PRODUCT and VENDOR tables Database Systems, 10 th Edition 5
Database Systems, 10 th Edition 6
Creating the Database • Two tasks must be completed: – Create database structure – Create tables that will hold end-user data • First task: – RDBMS creates physical files that will hold database – Differs substantially from one RDBMS to another Database Systems, 10 th Edition 7
Creating the Database (cont’d. ) • Authentication – DBMS verifies that only registered users are able to access database – Log on to RDBMS using user ID and password created by database administrator Database Systems, 10 th Edition 8
The Database Schema • Schema – Group of database objects that are related to each other • CREATE SCHEMA AUTHORIZATION {creator}; – Command is seldom used directly Database Systems, 10 th Edition 9
Data Types • Data type selection is usually dictated by nature of data and by intended use • Supported data types: – Number(L, D), Integer, Smallint, Decimal(L, D) – Char(L), Varchar 2(L) – Date, Timestamp – Real, Double, Float – Interval day to hour – Many other types Database Systems, 10 th Edition 10
Database Systems, 10 th Edition 11
Database Systems, 10 th Edition 12
Creating Table Structures • Use one line per column (attribute) definition • Use spaces to line up attribute characteristics and constraints • Table and attribute names are capitalized • NOT NULL specification • UNIQUE specification Database Systems, 10 th Edition 13
Creating Table Structures (cont’d. ) • Primary key attributes contain both a NOT NULL and a UNIQUE specification • RDBMS will automatically enforce referential integrity foreign keys • Command sequence ends with semicolon Database Systems, 10 th Edition 14
CREATE TABLE VENDOR ( V_CODE INTEGER V_NAME VARCHAR(35) V_CONTACT VARCHAR(15) V_AREACODE CHAR(3) V_PHONE CHAR(8) V_STATE CHAR(2) V_ORDER CHAR(1) PRIMARY KEY (V_CODE)); NOT NOT NULL UNIQUE, NULL, NULL, CREATE TABLE PRODUCT ( P_CODE VARCHAR(10) NOT NULL UNIQUE, P_DESCRIPT VARCHAR(35) NOT NULL, P_INDATE NOT NULL, P_QOH SMALLINT NOT NULL, P_MIN SMALLINT NOT NULL, P_PRICE NUMBER(8, 2) NOT NULL, P_DISCOUNT NUMBER(5, 2) NOT NULL, V_CODE INTEGER, For my. SQL, use PRIMARY KEY (P_CODE), FOREIGN KEY (V_CODE) REFERENCES VENDOR); REFERENCES VENDOR(V_CODE)); Database Systems, 10 th Edition 15
CREATE TABLE CUSTOMER ( CUS_CODE NUMBER PRIMARY KEY, CUS_LNAME VARCHAR(15) NOT NULL, CUS_FNAME VARCHAR(15) NOT NULL, CUS_INITIAL CHAR(1), CUS_AREACODE CHAR(3) DEFAULT '615' NOT NULL CHECK(CUS_AREACODE IN ('615', '713', '931')), CUS_PHONE CHAR(8) NOT NULL, CUS_BALANCE NUMBER(9, 2) DEFAULT 0. 00, CONSTRAINT CUS_UI 1 UNIQUE(CUS_LNAME, CUS_FNAME)); Database Systems, 10 th Edition 16
CREATE TABLE INVOICE ( INV_NUMBER PRIMARY KEY, CUS_CODE NUMBER NOT NULL REFERENCES CUSTOMER(CUS_CODE), INV_DATE NOT NULL; /* DOES NUT RUN IN my. SQL */ INV_DATE DEFAULT SYSDATE NOT NULL, CONSTRAINT INV_CK 1 CHECK (INV_DATE > TO_DATE('01 -JAN-2012', 'DD-MONYYYY'))); CREATE TABLE LINE ( INV_NUMBER NOT NULL, LINE_NUMBER(2, 0) NOT NULL, P_CODE VARCHAR(10) NOT NULL, LINE_UNITS NUMBER(9, 2) DEFAULT 0. 00 NOT NULL, LINE_PRICE NUMBER(9, 2) DEFAULT 0. 00 NOT NULL, PRIMARY KEY (INV_NUMBER, LINE_NUMBER), FOREIGN KEY (INV_NUMBER) REFERENCES INVOICE(INV_NUMBER) ON DELETE CASCADE, FOREIGN KEY (P_CODE) REFERENCES PRODUCT(P_CODE), CONSTRAINT LINE_UI 1 UNIQUE(INV_NUMBER, P_CODE)); Database Systems, 10 th Edition 17
SQL Constraints • FOREIGN KEY constraint (in PRODUCT table) FOREIGN KEY (V_CODE) REFERENCES VENDOR ON UPDATE CASCADE; – You can not delete a vendor from the VENDOR table if at least one product row references that vendor – If a change is made in an existing VENDOR table’s V_CODE, that change must be reflected automatically in any PRODUCT table V_CODE reference • Makes it impossible for a V_CODE value to exists in the PRODUCT table if there is not parallel entry in the VENDOR table 18
SQL Constraints • NOT NULL constraint – Ensures that column does not accept nulls • UNIQUE constraint – Ensures that all values in column are unique • DEFAULT constraint (NOT IN ACCESS) – Assigns value to attribute when a new row is added to table • CUS_AREACODE CHAR(3) DEFAULT ‘ 615’ NOT NULL CHECK (CUS_AREACODE IN (‘ 615’, ‘ 713’, 931’)) • CHECK constraint (NOT IN ACCESS) – Validates data when attribute value is entered • Minimum order amount must be at least 10 • Date must be after Jan 1, 2013 • CONSTRAINT INV_CHK 1 CHECK (INV_DATE>TO_DATE(‘ 01 -JAN 2012’, ’DD-MON-YYYY’)) 19
SQL Constraints • MS Access and SQL Server support ON DELETE CASCADE and ON UPDATE CASCADE – ON DELETE CASCADE recommended for weak entities to ensure that the deletion of a row in the strong entity automatically triggers the deletion of the corresponding rows in the dependent weak entity • Oracle supports ON DELETE CASCADE • Access and SQL Server do not support SET NULL, Oracle does 20
SQL Indexes • When primary key is declared, DBMS automatically creates unique index – Often need additional indexes – Using CREATE INDEX command, SQL indexes can be created on basis of any selected attribute – Can be in ascending or descending order … ON PRODUCT (P_PRICE DESC) • Composite index – Index based on two or more attributes, Often used to prevent data duplication CREATE INDEX P_INDATEX ON PRODUCT(P_INDATE); CREATE UNIQUE … ensures that no duplicates are allowed in the index (e. g. , a candidate key) – To delete an index use DROP INDEX P_INDATEX 21
Data Manipulation Commands • • • INSERT SELECT COMMIT UPDATE ROLLBACK DELETE Database Systems, 10 th Edition 22
Adding Table Rows • INSERT – Used to enter data into table – Syntax: INSERT INTO tablename VALUES (value 1, value 2, … , value. N); • value 1 thru value. N are in the order the columns were defined in the CREATE TABLE • NULL can be used for those values not known – To insert only a few columns use INSERT INTO tabelname(col 1, col 2, …col. N) VALUES (value 1, value 2, …value. N); 23
Adding Table Rows (cont’d. ) • When entering values, notice that: – Row contents are entered between parentheses – Character and date values are entered between apostrophes – Numerical entries are not enclosed in apostrophes – Attribute entries are separated by commas – A value is required for each column • Use NULL for unknown values 24
Saving Table Changes • Changes made to table contents are not physically saved on disk until: – Database is closed – Program is closed – COMMIT command is used • Syntax: – COMMIT [WORK]; • Will permanently save any changes made to any table in the database • MS Access does not support COMMIT because it automatically saves changes after the execution of each SQL command 25
Listing Table Rows • SELECT – Used to list contents of table – Syntax: SELECT columnlist|* FROM tablename; • Columnlist represents one or more attributes, separated by commas • Asterisk can be used as wildcard character to list all attributes • ORACLE allows you to format column data by declaring – COLUMN P_PRICE FORMAT $99, 9999. 99 – COLUMN P_DESCRIPT FORMAT A 12 TRUNCATE » Truncates description to first 12 characters 26
Updating Table Rows • UPDATE – Modify data in a table – Syntax: UPDATE tablename SET columnname = expression [, columnname = expression] [WHERE conditionlist]; • If more than one attribute is to be updated in row, separate corrections with commas UPDATE PRODUCT SET P_INDATE=‘ 18 -JAN-2012’, P_PRICE=17. 99, P_MIN=10 WHERE P_CODE=‘ 13 -Q 2/P 2’; – Without a WHERE clause, all rows would be updated 27
Restoring Table Contents • ROLLBACK – Undoes changes since last COMMIT – Brings data back to prechange values. Does not undo a COMMIT but undoes any changes not COMMIT’d – my. SQL requires START TRANSACTION; and BEGIN; • Syntax: ROLLBACK; • COMMIT and ROLLBACK only work with commands to add, modify, or delete table rows Database Systems, 10 th Edition 28
Deleting Table Rows • DELETE – Deletes a table row – Syntax: DELETE FROM tablename [WHERE conditionlist ]; • WHERE condition is optional • If WHERE condition is not specified, all rows from specified table will be deleted Database Systems, 10 th Edition 29
Inserting Table Rows with a SELECT Subquery • INSERT – Inserts multiple rows from another table (source) – Uses SELECT subquery – Subquery: query embedded (or nested or inner) inside another query – Subquery executed first – Syntax: INSERT INTO tablename SELECT columnlist FROM tablename; Database Systems, 10 th Edition 30
SELECT Queries • Fine-tune SELECT command by adding restrictions to search criteria using: – Conditional restrictions – Arithmetic operators – Logical operators – Special operators Database Systems, 10 th Edition 31
Selecting Rows with Conditional Restrictions • Select partial table contents by placing restrictions on rows to be included in output – Add conditional restrictions to SELECT statement, using WHERE clause • Syntax: SELECT columnlist FROM tablelist [ WHERE conditionlist ] ; • WHERE V_CODE =21344 • WHERE V_CODE<> 21344 • WHERE PRICE <=10 • WHERE P_CODE < ‘ 1558 -WQ 1’ • WHERE P_INDAT>=‘ 20 -Jan-2012’ 32
Database Systems, 10 th Edition 33
• String comparisons are made from left to right • Note that 5 will come after 44 even though the number 5 comes before the number 44. Database Systems, 10 th Edition 34
Selecting Rows with Conditional Restrictions (cont’d. ) • Using comparison operators on dates – Date procedures are often more software-specific than other SQL procedures • Using computed columns and column aliases – SQL accepts any valid expressions (or formulas) in the computed columns • SELECT … , P_QOH*P_PRICE – Alias: Alternate name given to a column or table in any SQL statement • SELECT … , P_QOH*P_PRICE AS TOTVALUE 35
Arithmetic Operators: The Rule of Precedence • • Perform operations within parentheses Perform power operations Perform multiplications and divisions Perform additions and subtractions Database Systems, 10 th Edition 36
Logical Operators: AND, OR, and NOT • Searching data involves multiple conditions • Logical operators: AND, OR, and NOT • Can be combined – Parentheses enforce precedence order • Conditions in parentheses are always executed first • Boolean algebra: mathematical field dedicated to use of logical operators • NOT negates result of conditional expression 37
Logical Operators: AND, OR, and NOT • WHERE P_PRICE < 50 AND P_INDATE >’ 15 -Jan-2012’ • WHERE (P_PRICE < 50 AND P_INDATE >’ 15 -Jan-2012’) OR V_CODE=24288 • WHERE NOT(V_CODE=21344) 38
Special Operators • BETWEEN: checks whether attribute value is within a range P_PRICE BETWEEN 50. 00 AND 100 • IS NULL: checks whether attribute value is null V_CODE IS NULL 39
Special Operators • LIKE: checks whether attribute value matches given string pattern V_CONTACT LIKE ‘Smith%’ – % mean any and all following or preceding characters • ‘J%’ includes Johnson, Jones, July and J-231 q • ‘Jo%’ includes Johnson and Jones • ‘%n’ includes Johnson – _ means any one character may be substituted for the underscore • ‘_23 -456 -6789’ includes 123…, 223…, 323 • ‘_23 -_56 -678_’ includes 123 -156 -6781, 123 -256 -6782 and 823 -9566788 – Oracle is case-sensitive on searches. You can use UPPER to convert column data to upper case in memory only UPPER(V_CONTACT) LIKE ‘SMITH%’ – Not sure if name is Johnson or Johnsen use V_CONTACT LIKE ‘Johns_n’ 40
Special Operators • IN: checks whether attribute value matches any value within a value list V_CODE IN (21344, 24288) – Can be used in subqueries such as SELECT V_CODE, V_NAME FROM VENDOR WHERE V_CODE IN (SELECT V_CODE FROM PRODUCT) • EXISTS: Execute a command based on the result of another query and runs only if subquery returns at least one row SELECT * FROM VENDOR WHERE EXISTS (SELECT * FROM PRODUCT WHERE P_QOH<=P_MIN); 41
Advanced Data Definition Commands • All changes in table structure are made by using ALTER command • Three options: – ADD adds a column – MODIFY changes column characteristics – DROP deletes a column • Can also be used to: – Add table constraints – Remove table constraints Database Systems, 10 th Edition 42
Changing a Column’s Data Type • ALTER can be used to change data type ALTER TABLE PRODUCT MODIFY (V_CODE CHAR(5)); – If the column contains data, changes can be made to the data type if those changes do not alter the type but just change the size e. g. , DECIMAL (8, 2) to (9, 2) • Some RDBMSs do not permit changes to data types unless column is empty or only let you increase the size of the field Database Systems, 10 th Edition 43
Adding a Column Dropping a Column • Use ALTER to add column – Do not include the NOT NULL clause for new column as the new column will default to a value of NULL ALTER TABLE PRODUCT ADD (P_SALECODE CHAR(1)); • Use ALTER to drop column – Some RDBMSs impose restrictions on the deletion of an attribute ALTER TABLE VENDOR DROP COLUMN V_ORDER; Database Systems, 10 th Edition 44
Advanced Data Updates • UPDATE command updates only data in existing rows • If relationship between entries and existing columns, can assign values to slots • Arithmetic operators are useful in data updates UPDATE PRODUCT SET P_SALECODE=‘ 2’ WHERE P_CODE =‘ 1546 -QQ 2’; WHERE clause can use IN, logical operators, etc. UPDATE PRODUCT SET P_QOH=P_QOH+20 WHERE P_CODE =‘ 1546 -QQ 2’; Any mathematical operation can be done on SET 45
Copying Parts of Tables • SQL permits copying contents of selected table columns – Data need not be reentered manually into newly created table(s) • First create the table structure (PART) • Next add rows to new table using table rows from another table (PRODUCT) INSERT INTO PART (PART_CODE, PART_DESCRIPT, PART_PRICE) SELECT P_CODE, P_DESCRIPT, P_PRICE FROM PRODUCT; Or in one step CREATE TABLE PART AS SELECT P_CODE AS PART_CODE, P_DESCRIPT AS PART_DESCRIPT, P_PRICE AS PART_PRICE FROM PRODUCT; 46
Adding Primary and Foreign Key Designations • When table is copied, integrity rules do not copy – Primary and foreign keys are manually defined on new table • User ALTER TABLE command • ALTER TABLE tablename ADD PRIMARY KEY(fieldname); ALTER TABLE PART ADD PRIMARY KEY(PART_CODE) – For foreign key, use FOREIGN KEY in place of PRIMARY KEY ALTER TABLE PART ADD FOREIGN KEY(V_CODE)REFERENCES VENDOR; – You can do multiple changes in one ALTER statement • One ADD follows another 47
Deleting a Table from the Database • DROP – Deletes table from database – Syntax: • DROP TABLE tablename; • Can drop a table only if it is not the “one” side of any relationship – Otherwise, RDBMS generates an error message – Foreign key integrity violation Database Systems, 10 th Edition 48
Additional SELECT Query Keywords • Logical operators work well in the query environment • SQL provides useful functions that: – Count – Find minimum and maximum values – Calculate averages, etc. • SQL allows user to limit queries to: – Entries having no duplicates – Entries whose duplicates may be grouped Database Systems, 10 th Edition 49
Ordering a Listing • ORDER BY clause is useful when listing order is important • Syntax: SELECT columnlist FROM tablelist [WHERE conditionlist] [ORDER BY columnlist [ASC | DESC]]; • Ascending order by default Database Systems, 10 th Edition 50
Listing Unique Values • DISTINCT clause produces list of only values that are different from one another • Example: SELECT DISTINCT V_CODE FROM PRODUCT; • Access places nulls at the top of the list – Oracle places it at the bottom – Placement of nulls does not affect list contents Database Systems, 10 th Edition 51
Aggregate Functions • COUNT function tallies number of non-null values of an attribute – Can include DISTINCT – Takes one parameter: usually a column name • MAX and MIN find highest (lowest) value in a table – Compute MAX value in inner query – Compare to each value returned by the query – Can be used with DATE fields or as MAX of a computed column SELECT MAX(A*B) … • SUM computes total sum for any specified attribute • AVG function format is similar to MIN and MAX Database Systems, 10 th Edition 52
Aggregate Functions SELECT COUNT (DISTINCT V_CODE) FROM PRODUCT WHERE P_PRICE <=10. 00; //TOTAL ROWS INCLUDING NULLS SELECT COUNT(*) FROM PRODUCT; SELECT MAX(P_PRICE) FROM PRODUCT; SELECT P_CODE, P_DESCRIPT, P_PRICE FROM PRODUCT WHERE P_PRICE = SELECT MAX(P_PRICE) FROM PRODUCT); USING P_PRICE = MAX(P_PRICE) DOES NOT WORK AS MAX CAN ONLY BE USED IN THE COLUMN LIST OF A SELECT STATEMENT. ALSO IN A COMAPRISON OF EQUALITY, YOU CAN ONLY USE A SINGLE VALUE TO THE RIGHT OF THE EQUALS SIGN 53
Grouping Data • Frequency distributions created by GROUP BY clause within SELECT statement • Requires aggregate function in the SELECT column list • Syntax: SELECT FROM [WHERE [GROUP BY [HAVING [ORDER BY Database Systems, 10 th Edition columnlist tablelist conditionlist] columnlist] conditionlist] columnlist [ASC | DESC] ] ; 54
Grouping Data • Rows can be grouped into smaller collections and an aggregate function can then summarize the data within each collection Minimum price within each salecode Average price within each salecode 55
• The GROUP BY clause’s columnlist must include all nonaggregate function columns in the SELECT’s columnlist • The GROUP BY columnlist can include any columns from the tables in the FROM clause even if they do not appear in the SELECT’s 56 columnlist
HAVING Clause • Operates like a WHERE clause in the SELECT statement but applies to the output of the GROUP BY Limit results to products whose average price is less than $10 57
Joining Database Tables • Joining tables is the most important distinction between relational database and other DBs • Join is performed when data are retrieved from more than one table at a time – Equality comparison between foreign key and primary key of related tables • Join tables by listing tables in FROM clause of SELECT statement – DBMS creates Cartesian product of every table • When joining 3 or more tables, a join condition is needed for each pair (n-1 pairs in all) Database Systems, 10 th Edition 58
Joining Database Tables • Join condition is generally composed of an equality compariosn between the FK and the PK of related tables SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE FROM PRODUCT, VENDOR WHERE PRODUCT. V_CODE=VENDOR. V_CODE AND P_INDATE>’ 15 -JAN-2012’ ORDER BY PRODUCT. PRICE; 59
Joining Tables with an Alias • Alias identifies the source table from which data are taken • Alias can be used to identify source table • Any legal table name can be used as alias • Add alias after table name in FROM clause – FROM tablename alias SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE FROM PRODUCT P, VENDOR V WHERE P. V_CODE=V. V_CODE ORDER BY P. PRICE; Database Systems, 10 th Edition 60
Recursive Joins • Alias is especially useful when a table must be joined to itself – Recursive query – Use aliases to differentiate the table from itself Database Systems, 10 th Edition 61
Recursive Joins • Generate a list of all employees with their managers’ names 62
Recursive Joins SELECT E. EMP_MGR, M. EMP_LNAME, E. EMP_NUM, E. EMP_LNAME FROM EMP E, EMP M WHERE E. EMP_MGR = M. EMP_NUM ORDER BY E. EMP_MGR; 63
- Slides: 63