Database Systems Design Implementation and Management Eighth Edition
Database Systems: Design, Implementation, and Management Eighth Edition Chapter 7 Introduction to Structured Query Language (SQL)
Database Systems, 8 th Edition 2
SQL commands 1. Capitalized words denote the command syntax 2. Lowercase words denote values that must be supplied by the user. 3. Brackets enclose optional syntax. 4. Ellipses (. . . ) indicate that the accompanying syntactic clause may be repeated as necessary. 5. Each SQL command ends with a semicolon (; ) Database Systems, 8 th Edition 3
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, 8 th Edition 4
Steps of creating a database: The command: CREATE SCHEMA AUTHORIZATION {creator} • E. g. CREATE SCHEMA AUTHORIZATION RAGHAD Database Systems, 8 th Edition 5
Creating Tables 1. Identify the appropriate data type including length if required for each attribute. 2. Identify those columns that should accept null values 3. Identify those columns that need to be unique, 4. Identify all primary key-foreign key mates In the VENDOR table: PRIMARY KEY (V_CODE)In the PRODUCT table FOREIGN KEY (V_CODE) REFERENCES VENDOR ON UPDATE CASCADE Database Systems, 8 th Edition 6
Creating Tables. . continued Besides the PRIMARY KEY and FOREIGN KEY constraints, jh the ANSI SQL standard defines the following constraints: (NOT NULL, UNIQUE, DEFAULT, CHECK) • 6. Identify any columns for which domain specifications may be stated that are more constrained than those established by data type. CHECK is used as a column constraint it may be possible to establish validation rules for values to be inserted into the database. Database Systems, 8 th Edition 7
Creating Tables. . continued • 5. Determine values to be inserted in any columns for which default value is desired. DEFAULT can be used to define a value that is automatically inserted when no value is inserted during data entry. Database Systems, 8 th Edition 8
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, 8 th Edition 9
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, 8 th Edition 10
Creating Table Structures (continued) • 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, 8 th Edition 11
SQL Constraints • NOT NULL constraint – Ensures that column does not accept nulls • UNIQUE constraint – Ensures that all values in column are unique • DEFAULT constraint – Assigns value to attribute when a new row is added to table • CHECK constraint – Validates data when attribute value is entered Database Systems, 8 th Edition 12
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, 8 th Edition 13
Another example of the CHECK constraint: CREATE TABLE INVOICE ( INV_NUMBER CUS_CODE NUMBER PRIMARY KEY, NOT NULL REFERENCES CUSTOMER (CUS_CODE), INV_DATE DEFAULT SYSDATE NOT NULL, CONSTRAINT INV_CK 1 CHECK (INV_DATE > TO_DATE(’ 01 -JAN-2008’, ’DDMMM-YYYY’))); Database Systems, 8 th Edition 14
• CREATE TABLE lets you define constraints in 2 different places: • When you create the column definition (column constraint) • When you use the CONSTRAINT keyword ( table constraint) Database Systems, 8 th Edition 15
Creating Tables. . . continued 7. create the table and any desired indexes using the CREATE TABLE and CREATE INDEX statements. • a common practice is to create an index on any field that is used as a search key • to delete an index, use the DROP INDEX command: DROP INDEX indexname Database Systems, 8 th Edition 16
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 • Composite index – Index based on two or more attributes – Often used to prevent data duplication Database Systems, 8 th Edition 17
SQL INDEXES • Many RDBMs, including Access, automatically create a unique index on the PK attributes(s) when you declare the PK. • A common practice is to create an index on any field that is used as a search key. • By default all indexes produce results that are listed in ascending order, but you can create an index that yields output in descending order. CREATE [UNIQUE]INDEX indexname ON tablename(column 1, [, column 2]) e. g. • • • CREATE INDEX P_INDATEX ON PRODUCT(P_INDATE); CREATE UNIQUE INDEX P_CODEX ON PRODUCT (P_CODE); CREATE UNIQUE INDEX EMP_TESTDEX ON TEST(EMP_NUM, TEST_CODE, TEST_DATE); (each employee does 1 test on a given date) prevents you from entering a duplicate P_CODE value, and if you do the error message “duplicate value in index. ” Database Systems, 8 th Edition 18
SQL Indexes • If the index should be in a descending order then this should be used: CREATE INDEX PROD_PRICEX ON PRODUCT(P_PRICE DESC); To delete an index, use the DROP INDEX COMMAND • DROP INDEX indexname • DROP INDEX PROD_PRICEX; • . After creating the tables and some indexes, you are ready to start entering data. Database Systems, 8 th Edition 19
Data Manipulation Commands • • • INSERT SELECT COMMIT UPDATE ROLLBACK DELETE Database Systems, 8 th Edition 20
Adding Table Rows • INSERT – Used to enter data into table – Syntax: • INSERT INTO tablename VALUES (value 1, value 2, . . . , valuen) • INSERT INTO VENDOR VALUES (21225, ’BRYSON, Inc. ’, ’Smithson’, ’ 615’, ’ 223 -3234’, ’TN’, ’Y’); • • INSERT INTO VENDOR • VALUES (21226, ’Superloo, Inc. ’, ’Flushing’. ’ 904’, ’ 215 -995’, ’FL’, ’N’); • (To see the contents of the PRODUCT table, use the SELECT * FROM PRODUCT; command. ) Database Systems, 8 th Edition 21
Adding Table Rows (continued) • 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 Database Systems, 8 th Edition 22
Inserting Rows with Null Attributes INSERT INTO PRODUCT VALUES (‘BRT-345’. ’Titanium drill bit’, ’ 18 -Oct-07’, ’ 75’, 10, 4. 50, 0. 06, NULL); • There might be occasions when more than one attribute is optional. Rather than declaring each attribute as NULL in the insert command, you can indicate just the attributes that have required values. • INSERT INTO PRODUCT (P_CODE, P_DESCRIPT) VALUES (‘BRT-345’, ’Titanium drill bit’); • Database Systems, 8 th Edition 23
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 doesn’t support the COMMIT command because it automatically changes after the execution of each SQL command. ) Database Systems, 8 th Edition 24
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 Database Systems, 8 th Edition 25
Listing Table Rows. . . continued • SQL commands can be grouped together on a single line, but complex command sequences are best shown on separate lines, with space between the SQL command the command’s components. That makes it easy to trace the SQL logic, and to make corrections SELECT FROM P_CODE, P_DESCRIPT, P_INDATE, P_QOH, P_MIN, P_PRICE, P_DISCOUNT, V_CODE PRODUCT; Database Systems, 8 th Edition 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 Database Systems, 8 th Edition 27
UPDATE SET [WHERE PRODUCT columnname = expression [, columnname = expression] conditionlist]; • If you want to change P_INDATE from December 13, 2007, to January 18, 2008, in the second row of the PRODUCT table, use the primary key (13 Q 2/P 2) to locate the correct (second) row. UPDATE PRODUCT SET PINDATE = ’ 18 -JAN-2008’ WHERE P_CODE = ’ 13 -Q 2/P 2’; • If more than one attribute is to be updated in the row, separate the corrections with commas: UPDATE PRODUCT SET P_INDATE = ’ 18 -JAN-2008’, P_PRICE =17. 99, P_MIN = 10 WHERE P_CODE ’ 13 -Q 2/P 2’; ( if the WHERE condition wasn’t specified, the values of the named attributes will change in all rows of the table specified) Database Systems, 8 th Edition 28
• To confirm the correction(s) use the SELECT command to check the PRODUCT table’s listings. • SELECT * FROM PRODUCT; Database Systems, 8 th Edition 29
Restoring Table Contents • ROLLBACK – Undoes changes since last COMMIT – Brings data back to pre-change values • Syntax: – ROLLBACK; • COMMIT and ROLLBACK only work with commands to add, modify, or delete table rows it will undo only the results of the INSERT and UPDATE commands. Database Systems, 8 th Edition 30
Restoring. . continued • MS Access doesn’t support the ROLLBACK command • . Because MS commits each row automatically, doing a ROLLBACK afterward wouldn’t have undone anything. Database Systems, 8 th Edition 31
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, 8 th Edition 32
Deleting Rows. . continued • e. g. DELETE FROM WHERE PRODUCT P_CODE = ‘BRT-345’; • (the primary key value lets SQL find the exact record to be deleted. ) Database Systems, 8 th Edition 33
Inserting Table Rows with a SELECT Subquery • INSERT – Inserts multiple rows from another table (source) – Uses SELECT subquery – Subquery: query embedded (or nested) inside another query – Subquery executed first – Syntax: INSERT INTO tablename SELECT columnlist FROM tablename; Database Systems, 8 th Edition 34
SELECT Queries • SELECT, coupled with appropriate search conditions, is an incredibly powerful tool that enables you to transform data into information. • Fine-tune SELECT command by adding restrictions to search criteria using: – Conditional restrictions – Arithmetic operators – Logical operators – Special operators Database Systems, 8 th Edition 35
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 ] ; Database Systems, 8 th Edition 36
SELECT columnlist FROM tablelist [WHEREconditionlist]; • e. g. SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE = 21344; • COMPARISON OPERATORS (=, <, <=, >, >=, <> or !=) SELECT FROM WHERE P_DESCRIPT, P_QOH, P_MIN, P_PRICE PRODUCT P_PRICE <= 10; Database Systems, 8 th Edition 37
• Using Comparison Operators on Character Attributes SELECT FROM WHERE P_CODE, P_DESCRIPT, P_QOH, P_MIN, P_PRICE PRODUCT P_CODE < ‘ 155 -QW 1’; • . according to ASCII code value for the letter B is greater than the value of the letter A, if follows that A is less than B, it follows that A is less that B) so 11 -QER/31<1558 -QW 1 Database Systems, 8 th Edition 38
Using Comparison Operators on Dates SELECT FROM WHERE P_DESCRIPT, P_QOH, P_MIN, P_PRICE, P_INDATE PRODUCT P_INDATE >= ‘ 20 -Jan-2008’; Using Computed Columns and Column Aliases SELECT FROM P_DESCRIPT, P_QOH, P_PRICE, P_QOH*P_PRICE PRODUCT; (SQL accepts any valid expressions (or formulas) in the computed columns. Access automatically adds and Expr label to all computed columns. (The first computed column would be labled Expr 1; the second, Expr 2; and so on). Oracle uses the actual formula text as the label for the computed column Database Systems, 8 th Edition 39
The SQL permits the use of aliases for any column in a SELECT statement • An alias is an alternative name given to a column or table in any SQL STATEMENT. SELECT FROM P_DESCRIPT, P_QOH, P_PRICE, P_QOH*P_PRICE AS TOTVALUE PRODUCT; Database Systems, 8 th Edition 40
You could also use a computed column, an alias, and date arithmetic in a single query MS Access version: SELECT P_CODE, P_INDATE, DATE() – 90 AS CUTDATE FROM PRODUCT WHERE P_INDATE <= DATE() – 90; Oracle version: SELECT FROM WHERE P_CODE, P_INDATE, SYSDATE – 90 AS CUTDATE PRODUCT P_INDATE<= SYSDATE – 90; Database Systems, 8 th Edition 41
Do this one! Suppose a manager wants a list of all products, the dates they were recieved, and the warranty expiration date (90 days from when the product was received). How would you generate that list? Database Systems, 8 th Edition 42
The command will be SELECT FROM P_CODE, P_INDATE + 90 AS EXPDATE PRODUCT; Database Systems, 8 th Edition 43
Arithmetic Operators: The Rule of Precedence • Perform operations within parentheses • Perform power operations • Perform multiplications and divisions • Perform additions and subtractions Database Systems, 8 th Edition 44
• Rules of Presedence: – Perform operations within parenthesis – Perform power operations – Perform multiplications and divisions – Perform additions and subtractions E. g. 8+2*5 =8+10=18 (8+2)*5 = 10 *5 = 50 Database Systems, 8 th Edition and so on 45
Logical Operators: AND, OR, and NOT • Searching data involves multiple conditions • Logical operators: AND, OR, and NOT • Can be combined – Parentheses placed to enforce precedence order – Conditions in parentheses always executed first • Boolean algebra: mathematical field dedicated to use of logical operators • NOT negates result of conditional expression Database Systems, 8 th Edition 46
SELECT FROM WHERE P_DESCRIPT, P_INDATE, P_PRICE, V_CODE PRODUCT (P_PRICE < 50 AND P_INDATE > ’ 15 -JAN-2008’) OR V_CODE = 24288; • note the use of parentheses to combine logical restrictions. • Conditions listed within parentheses are always executed first. Database Systems, 8 th Edition 47
• The NOT logical operator is used to find the rows that do not match a certain condition. e. g. if you want to see a listing of all rows for which the vendor code is not 21344, use the command sequence: SELECT * FROM PRODUCT WHERE NOT (V_CODE =21344); • note that the condition is enclosed in parentheses; that practice is optional, but it is highly recommended for clarity. • . The logical NOT can be combined with AND and OR Database Systems, 8 th Edition 48
Special Operators • BETWEEN: checks whether attribute value is within a range • IS NULL: checks whether attribute value is null • LIKE: checks whether attribute value matches given string pattern • IN: checks whether attribute value matches any value within a value list • EXISTS: checks if subquery returns any rows Database Systems, 8 th Edition 49
BETWEEN SELECT FROM WHERE * PRODUCT P_PRICE BETWEEN 50. 00 AND 100. 00; (ORACLE Users always specify the lower range first) If your DBMS does not support BETWEEN, you can use: SELECT FROM WHERE * PRODUCT P_PRICE > 50. 00 AND P_PRICE <100. 00 Database Systems, 8 th Edition 50
IS NULL • (suppose you want to list all products that do not have a vendor assigned (V_CODE is null) SELECT P_CODE, P_DESCRIPT, V_CODE FROM PRODUCT WHERE V_CODE IS NULL; ( we cannot just say where V_CODE = NULL because NULL is not a “value” the way a 0 or the blank space is, but instead a NULL is a special property of an attribute that represents precisely the absence of any value. ) Database Systems, 8 th Edition 51
LIKE Used to check whether an attribute value matches a given string pattern. • ( Standard SQL allows you to use the percent sign (%) and (_) underscore wild characters to make matches when the entire string is not known. • % means any and all following or preceding characters are eligible. – ‘J%’ includes Johnson, Jones, July and J-231 Q. – ‘JO % ‘ includes Johnson and Jones. – ‘%n’ includes Johnson and Jernigan. • Means any one character may be substituted for the underscore – _23 -456 -6789’ includes 123 -456 -6789, 223 -456 -6789. . . etc. – ‘_o_es’ includes Jones, Cokes, totes, and roles. Database Systems, 8 th Edition 52
• E. g. SELECT V_NAME , V_ CONTACT, V_AREACODE, V_PHONE VENDOR V_CONTACT LIKE ‘Smith%’; FROM WHERE • • If you want to avoid the case sensitivity then change the last line to WHERE UPPER(V_CONTACT) LIKE ‘SMITH%’; • The preceding query produces a list including all rows that contain a last name that begins with Smith , regardless of uppercase or lowercase letter combinations such as Smith, smith, and SMITH. • (ORACLE case sensitive, MS Access is not) Database Systems, 8 th Edition 53
The logical operators may be used with the special operators SELECT FROM WHERE V_NAME , V_ CONTACT, V_AREACODE, V_PHONE VENDOR V_CONTACT NOT LIKE ‘Smith%’; • We can combine wildcards • e. g. ‘_l%’ can yield the strings Al, Alton, Elgin, Blakeston, blank, bloated and eligible. Database Systems, 8 th Edition 54
IN Used to check whether an attribute value matches any value within a value list. Many queries that would require the use of the logical OR can be more easily handled with the help of the special operator IN. e. g. SELECT FROM WHERE OR * PRODUCT V_CODE = 21344 V_CODE = 24288; Database Systems, 8 th Edition 55
Can be handled more efficiently with SELECT FROM WHERE * PRODUCT V_CODE IN (21344, 24288); (all values in the list must be of the same data type) The IN operator is especially valuable when used in conjunction with subqueries. Suppose you want to list the V_CODE and V_NAME of only those vendors who provide products. SELECT FROM WHERE V_CODE, V_NAME VENDOR V_CODE IN (SELECT V_CODE FROM PRODUCT); Database Systems, 8 th Edition 56
EXISTS special operator • Can be used whenever there is a requirement to execute a command based on the result of another query. That is, if a subquery returns any rows, run the main query; otherwise don’t. • E. g. SELECT FROM WHERE * VENDOR EXISTS (SELECT * FROM PRODUCT WHERE P_QOH < P_MIN); This will list all vendors, but only if there are products to order. Database Systems, 8 th Edition 57
Try to do this one! The EXISTS special operator is used to list all vendors, but only if there are products with the quantity on hand, less than double the minimum quantity Database Systems, 8 th Edition 58
SELECT FROM WHERE Database Systems, 8 th Edition * VENDOR EXISTS (SELECT * FROM PRODUCT WHERE P_QOH < P_MIN*2); 59
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, 8 th Edition 60
Changing a Column’s Data Type • ALTER can be used to change data type • Some RDBMSs do not permit changes to data types unless column is empty E. g. ALTER TABLE PRODUCT MODIFY (V_CODE CHAR(5)); Changes the number V_CODE in the PRODUCT table to a character V_CODE Database Systems, 8 th Edition 61
Changing a Column’s Data Characteristics • Use ALTER to change data characteristics • Changes in column’s characteristics permitted if changes do not alter the existing data type E. g. If you want to increase the width of the P_PRICE column to 9 digits, use the command: ALTER TABLE PRODUCT MODIFY(P_PRICE DECIMAL(9, 2)); If you list the table, you see that the column width has increased by one digit Database Systems, 8 th Edition 62
Adding a Column • Use ALTER to add column – Do not include the NOT NULL clause for new column (at first it will be nulls!) ALTER TABLE PRODUCT ADD(P_SALECODE CHAR(1)); Database Systems, 8 th Edition 63
Dropping a Column • Use ALTER to drop column – Some RDBMSs impose restrictions on the deletion of an attribute ALTER TABLE VENDOR DROP COLUMN V_ORDER; Some RDBMSs impose restrictions on attribute deletion. E. g. you may not drop attributes that are involved in foreign key relationships, nor may you delete an attribute of a table that contains only that one attribute. Database Systems, 8 th Edition 64
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 useful in data updates • In Oracle, ROLLBACK command undoes changes made by last two UPDATE statements Database Systems, 8 th Edition 65
To enter the P_SALECODE value ‘ 1’ for P_CODE value in the 3 rd row, use the UPDATE command together with the primary key P_CODE ‘ 154 QQ 2’ UPDATE SET WHERE PRODUCT P_SALECODE = ‘ 1’ P_CODE = ‘ 154 -QQ 2’; Database Systems, 8 th Edition 66
If you want to enter the P_SALECODE value ‘ 2’ for the P_CODE values ‘ 2232/QWE’ and ‘ 2232/QTY’; UPDATE SET WHERE PRODUCT P_SALECODE = ‘ 2’ P_CODE IN (‘ 2232/QWE’, ’ 2232/QTY’); Database Systems, 8 th Edition 67
Try this one If you want to place sales codes based on P_INDATE into the table using the following schedule: P_INDATE P_SALECODE Before Dec 25 2007 2 Between Jan 16, 2008 and Feb 10, 2008 1 Database Systems, 8 th Edition 68
UPDATE SET WHERE PRODUCT P_SALECODE =‘ 2’ P_INDATE< ‘ 25 -Dec-2007’; UPDATE SET WHERE PRODUCT P_SALECODE= ‘ 1’ P_INDATE >=‘ 16 -Jan-2008’ AND P_INDATE <= ‘ 10 -Feb-2008’; To check the results of those commands: SELECT P_CODE, P_DESCRIPT, P_INDATE, P_PRICE, P_SALECODE FROM PRODUCT; Database Systems, 8 th Edition 69
Database Systems, 8 th Edition 70
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 • Next add rows to new table using table rows from another table Database Systems, 8 th Edition 71
If you want to copy P_CODE, P_DESCRIPT, P_PRICE, and V_CODE from the PRODUCT table to a new table named PART, you create the PART table structure first: CREATE TABLE PART( PART_CODE CHAR(8) NOT NULL PART_DESCRIPT CHAR(35), PART_PRICE DECIMAL(8, 2), V_CODE INTEGER, PRIMARY KEY (PART_CODE)); Database Systems, 8 th Edition UNIQUE, 72
You need to add the rows to the new PART table, using the PRODUCT table rows. To do that you use the INSERT command: INSERT INTO PART(PART_CODE, PART_DESCRIPT, PART_PRICE, V_CODE) SELECT P_CODE, P_DESCRIPT, P_PRICE, V_CODE FROM PRODUCT; Database Systems, 8 th Edition 73
You can also create the PART table as follows: CREATE TABLE PART AS SELECT P_CODE AS PART_CODE, P_DESCRIPT AS PART DESCRIPT, P_PRICE AS PART_PRICE, V_CODE FROM PRODUCT; Database Systems, 8 th Edition 74
Database Systems, 8 th Edition 75
Adding Primary and Foreign Key Designations • When table is copied, integrity rules do not copy – Primary and foreign keys manually defined on new table • Use ALTER TABLE command – Syntax: ALTER TABLE tablename ADD PRIMARY KEY(fieldname); – For foreign key, use FOREIGN KEY in place of PRIMARY KEY Database Systems, 8 th Edition 76
Adding Primary and Foreign Key Designations ALTER TABLE ADD PART FORIEGN KEY (V_CODE) REFERENCES VENDOR; If neither the PART table’s primary key nor its foreign key has been designated. ALTER TEBLE PART ADD PRIMARY KEY (PART_CODE) ADD FORIEGN KEY (V_CODE) REFERENCES VENDOR; Database Systems, 8 th Edition 77
Even composite primary keys and multiple foreign keys can be designated in a single SQL command ALTER TABLE ADD ADD LINE PRIMARY KEY(INV_NUMBER, LINE_NUMBER) FOREIGN KEY (INV_NUMBER) REFERENCES INVOICE FOREIGN KEY (PROD_CODE) REFERENCES PRODUCT; Database Systems, 8 th Edition 78
Deleting a Table from the Database • DROP – Deletes table from database – Syntax: DROP TABLE PART; • 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, 8 th Edition 79
Advanced SELECT Queries • 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, 8 th Edition 80
Ordering a Listing • ORDER BY clause useful when listing order important to you. • Syntax: SELECT columnlist FROM tablelist [WHERE conditionlist] [ORDER BY columnlist [ASC | DESC]]; • Ascending order by default Database Systems, 8 th Edition 81
• If you want the contents of the PRODUCT table listed by P_PRICE in ascending order, use: SELECT FROM ORDER BY P_CODE, P_DESCRIPT, P_INDATE, P_PRICE PRODUCT P_PRICE; Try to produce the list in descending order! Database Systems, 8 th Edition 82
It will be : SELECT P_CODE, P_DESCRIPT, P_INDATE, P_PRICE FROM PRODUCT ORDER BY P_PRICE DESC; Database Systems, 8 th Edition 83
Cascading order sequence Ordering listings are used frequently e. g. Phone directory. It would be helpful if you could produce an ordered sequence (last name, first name, initial) in 3 stages 1. ORDER BY last name. 2. Within the last names, ORDER BY first name. 3. Within the first and last names, ORDER BY middle initial. Such a multilevel ordered sequence is known as a cascading order sequence Database Systems, 8 th Edition 84
ORDER BY with other SQL commands Typically, invoice due dates are listed in descending order. Or the largest prices first. SELECT P_DESCRIPT, V_CODE, P_INDATE, P_PRICE FROM PRODUCT WHERE P_INDATE <=‘ 21 -Jan-2008’ AND P_PRICE<= 50. 00 ORDER BY V_CODE, P_PRICE DESC; Database Systems, 8 th Edition 85
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, 8 th Edition 86
Listing Unique Values How many different vendors are currently represented in the PRODUCT table? A simple listing (SELECT) is not very useful if the table contains several thousand rows and you have to sift through the vendor codes manually. DISTINCT clause produces a list of only those values that are different from one another. SELECT DISTINCT V_CODE FROM PRODUCT; (Access places the null V_CODE at the top of the list, Oracle places it at the bottom) Database Systems, 8 th Edition 87
Aggregate Functions • COUNT function tallies number of non-null values of an attribute – 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 • SUM computes total sum for any specified attribute • AVG function format similar to MIN and MAX Database Systems, 8 th Edition 88
COUNT in conjunction with DISTINCT You want to find out how many different vendors are in the PRODUCT table, not counting the null values. SELECT COUNT(DISTINCT V_CODE) FROM PRODUCT; Result: COUNT (DISTINCTV_CODE) --------------------6 Database Systems, 8 th Edition 89
SELECT COUNT(DISTINCT V_CODE) FROM PRODUCT WHERE P_PRICE <= 10. 00; Result: COUNT (DISTINCTV_CODE) -------------------3 Database Systems, 8 th Edition 90
SELECT COUNT (*) FROM PRODUCT WHERE P_PRICE <= 10. 00 COUNT (DISTINCTV_CODE) --------------------5 Database Systems, 8 th Edition 91
MAX and MIN Help you find the answers to problems such as: • Highest (maximum) price in the PRODUCT table • Lowest (minimum) price in the PRODUCT table. SELECT P_CODE, P_DESCRIPT, P_PRICE FROM PRODUCT WHERE P_PRICE = MAX(P_PRICE); This will not yield the expected results, because the use of MAX(P_PRICE)to the right side of a comparison operator is incorrect n 92
To answer the query you must compute the maximum price first then compare it to each price returned by the query • To do that you need a nested query. • In this case the nested query is composed of 2 parts. – The inner query, which is executed first – The outer query, which is executed last (remember, the outer query is always the first SQL command you encounter- in this case SELECT) Database Systems, 8 th Edition 93
MAX in a nested query SELECT FROM WHERE P_CODE, P_DESCRIPT, P_PRICE PRODUCT P_PRICE = (SELECT MAX(P_PRICE) FROM PRODUCT); Database Systems, 8 th Edition 94
SUM SELECT FROM SUM(CUS_BALANCE) AS TOTBALANCE CUSTOMER; This computes the total amount owed by your customers. Database Systems, 8 th Edition 95
AVG • Similar to MAX and MIN SELECT AVG(P_PRICE) FROM PRODUCT; SELECT FROM WHERE ORDER BY P_CODE, P_DESCRIPT, P_QOH, P_PRICE, V_CODE PRODUCT P_PRICE> (SELECT AVG(P_PRICE) FROM PRODUCT) P_PRICE DESC; Database Systems, 8 th Edition 96
Grouping Data • Syntax: SELECT FROM [WHERE [GROUP BY [HAVING [ORDER BY Database Systems, 8 th Edition columnlist tablelist conditionlist] columnlist] conditionlist] columnlist [ASC | DESC] ] ; 97
GROUP BY • Frequency distributions can be created quickly and easily using the GROUP BY clause within the SELECT statement. • The GROUP BY clause is valid ONLY when used in conjunction with one of the SQL aggregate functions (COUNT, MIN, MAX, AVG and SUM) • Nulls are included within the grouping. Database Systems, 8 th Edition 98
SELECT V_CODE, SUM(P_QOH * P_PRICE) AS TOTCOST FROM PRODUCT GROUP BY V_CODE HAVING (SUM(P_QOH*P_PRICE) >500 ORDER BY SUM (P_QOH *P_PRICE) DESC; THIS WILL 1. Aggregate the total cost of products grouped by V_CODE 2. Select only the rows having totals that exceed $500 3. List the results in descending order by the total cost (note the syntax used in the HAVING and ORDER BY clauses; you must specify the column expression(formula) used in the SELECT statement’s columnlist, rather than the column alias (TOTCOST). Some RDBMSs allow you to substitute with the column alias, while others don’t 99
A wrong use of GROUP BY SELECT V_CODE, P_DESCRIPT, P_PRICE FROM PRODUCT GROUP BY V_CODE; It will generate an error message saying that it is not a GROUP BY expression. Database Systems, 8 th Edition 100
A correct use of GROUP BY SELECT V_CODE, COUNT(DISTINCT(P_CODE)) FROM PRODUCT GROUP BY V_CODE; V_CODE ------21225 21231 21344 COUNT(DISTINCT(P_CODE)) --------------------2 1 4 2 Produces 4 rows with a row that has V_CODE with Null values. Database Systems, 8 th Edition 101
The GROUP BY feature’s HAVING clause • The HAVING operates very much like the WHERE clause in the SELECT statement. • However the WHERE clause applies to columns and expressions for individual rows, while : • the HAVING clause is applied to the output of a GROUP BY operation. Database Systems, 8 th Edition 102
When using the GROUP BY clause with a SELECT statement • The SELECT’s columnlist must include a combination of column names and aggregate functions. • The GROUP BY clauses columnlist must include all non aggregate function columns specified in the SELECT’s columnlist. If required, you could also group by any aggregate function columns that appear in the SELECT’s columnlist. • The GROUP By clause columnlist can include any columns from the tables in the FROM clause of the SELECT statement, even if they do not appear in the SELECT’s columnlist. Database Systems, 8 th Edition 103
SELECT V_CODE, COUNT(DISTINCT (P_CODE)), AVG(P_PRICE) FROM PRODUCT GROUP BY V_CODE; (GROUP BY with the aggregate function COUNT and AVG) SELECT V_CODE, COUNT(DISTINCT (P_CODE)), AVG(P_PRICE) FROM PRODUCT GROUP BY V_CODE HAVING AVG (P_PRICE) <10; Database Systems, 8 th Edition 104
Virtual Tables: Creating a View • View is virtual table based on SELECT query • Create view by using CREATE VIEW command • Special characteristics of relational view: – Name of view can be used anywhere a table name is expected – View dynamically updated – Restricts users to only specified columns and rows – Views may be used as basis for reports Database Systems, 8 th Edition 105
CREATING A VIEW Suppose that at the end of every day, you would like to get a list of all products to reorder, that is, products with a quantity on hand that is less than or equal to the minimum quantity. Instead of typing the same query at the end of every day wouldn’t it be better to permanently save that query in the database? That’s the function of a relational view. SYNATAX : CREATE VIEW; CREATE VIEW viewname AS SELECT query Database Systems, 8 th Edition 106
Creating a virtual table with the CREATE VIEW command CREATE VIEW PRICEGT 50 AS SELECT P_DESCRIPT, P_QOH, P_PRICE FROM PRODUCT WHERE P_PRICE > 50. 00; Database Systems, 8 th Edition 107
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 Database Systems, 8 th Edition 108
NATURAL JOIN SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE FROM PRODUCT, VENDOR WHERE PRODUCT. V_CODE = VENDOR. V_CODE; The order of the columns is not relevant To generate a more predictable list by using ORDER BY clause SELECT P_DESCRIP, P_PRICE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE FROM PRODUCT, VENDOR ORDER BY P_PRICE th Edition 109 Database Systems, 8 (always from lowest price to highest price
• When joining 3 or more table, you need to specify a join condition for each pair of tables. • The number of join conditions will always be N 1, where N represents the number of tables listed in the FROM clause. • E. g. – 3 tables you have 2 conditions – 5 tables you must have four join conditions And so on. Database Systems, 8 th Edition 110
SELECT FROM WHERE AND AND ORDER BY CUS_LNAME, INV_NUMBER, INV_DATE, P_DESCRIPT CUSTOMER, INVOICE, LINE, PRODUCT CUSTOMER. CUS_CODE=INVOICE. CUS_CODE INVOICE. INV_NUMBER=LINE. INV_NUMBER LINE. P_CODE=PRODUCT. P_CODE CUSTOMER. CUS_CODE= 10014 INV_NUMBER; Database Systems, 8 th Edition 111
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 Database Systems, 8 th Edition 112
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; The aliases P and V are used to label the PRODUCT and VENDOR tables in the next command sequence. Database Systems, 8 th Edition 113
Recursive Joins Outer Joins • Alias especially useful when a table must be joined to itself – Recursive query – Use aliases to differentiate the table from itself • Two types of outer join – Left outer join – Right outer join Database Systems, 8 th Edition 114
Recursive JOIN SELECT FROM WHERE ORDER BY E. EMP_MGR, M. EMP_LNAME, E. EMP_NUM, E. EMP_LNAME EMP E, EMP M E. EMP_MGR=M. EMP_NUM E. EMP_MGR To list the employees with their managers and ordered by the managers Database Systems, 8 th Edition 115
OUTER JOINS SELECT FROM P_CODE, VENDOR. V_CODE, V_NAME VENDOR LEFT JOIN PRODUCT ON VENDOR. V_CODE = PRODUCT. V_CODE; ( this will show all the v_code from the VENDOR table with no null values and the nulls in the P_Code) SELECT FROM P_CODE, VENDOR. V_CODE, V_NAME VENDOR RIGHT JOIN PRODUCT ON VENDOR. V_CODE = PRODUCT. V_CODE; (this will show all the P_CODE without null values and the nulls in the V_CODE) Database Systems, 8 th Edition 116
Summary • SQL commands can be divided into two overall categories: – Data definition language commands – Data manipulation language commands • The ANSI standard data types are supported by all RDBMS vendors in different ways • Basic data definition commands allow you to create tables, indexes, and views Database Systems, 8 th Edition 117
Summary (continued) • DML commands allow you to add, modify, and delete rows from tables • The basic DML commands: – SELECT, INSERT, UPDATE, DELETE, COMMIT, and ROLLBACK • SELECT statement is main data retrieval command in SQL Database Systems, 8 th Edition 118
Summary (continued) • WHERE clause can be used with SELECT, UPDATE, and DELETE statements • Aggregate functions – Special functions that perform arithmetic computations over a set of rows • ORDER BY clause – Used to sort output of SELECT statement – Can sort by one or more columns – Ascending or descending order Database Systems, 8 th Edition 119
Summary (continued) • Join output of multiple tables with SELECT statement – Join performed every time you specify two or more tables in FROM clause – If no join condition specified, DBMX performs Cartesian product • Natural join uses join condition to match only rows with equal values in specified columns • Right outer join and left outer join select rows with no matching values in other related table Database Systems, 8 th Edition 120
- Slides: 120