ITEC 3220 A Using and Designing Database Systems

ITEC 3220 A Using and Designing Database Systems Instructor: Gordon Turpin Course Website: www. cse. yorku. ca/~gordon/itec 3220 S 07 Office: CSEB 3020

Chapter 8 Advanced Structured Query Language (SQL)

SQL Queries • Single table query • Multiple table query – Nesting query (subquery) • Using IN • Using EXISTS – Join Table 3

Examples • SELECT Order_Num FROM ORDERS WHERE Order_Num IN (SELECT Order_Num FROM ORDER_LINE WHERE Part_Num =1234; 4

Examples (Cont’d) • SELECT Order_Num FROM ORDERS WHERE EXISTS (SELECT * FROM ORDER_LINE WHERE ORDERS. Order_Num = ORDERLINE. Order_Num AND Part_Num =1234; 5

Examples (Cont’d) • SELECT S. Last, S. First, C. Last, C. First FROM SALES_REP S, CUSTOMER C WHERE S. Srep_Num = C. Srep_Num 6

SQL Exercise • Write SQL code that will create the relations shown. Assume the following attribute data types: – – – – – Student_ID: integer Student_Name: 25 characters Faculty_ID: integer Faculty_Name: 25 characters Course_ID: 25 characters Course_Name: 15 characters Date_Qualified: date Section_ID: integer Semester: 7 characters 7

SQL Exercise (Cont’d) STUDENT (Primary key: Student_ID) Student_ ID Student_ Name 38214 Letersky 54907 66324 70542 Altvater IS_QUALIFIED (Primary key: Faculty_ID, Course_ID) Faculty_ ID 2143 Course_I D ISM 3112 Date_ Qualified 9/1988 3467 ISM 4212 9/1995 3467 ISM 4930 9/1996 4756 ISM 3113 9/1991 4756 ISM 3112 9/1991 Aiken Marra 8

SQL Exercise (Cont’d) FACULTY (Primary key: Faculty_ID) SECTION (Primary key: Section_ID) Faculty_I D 2143 Faculty_Name Section_ID Course_ID Birkin 2712 ISM 3113 3467 Berndt 2713 ISM 3113 4756 Collins 2714 ISM 4212 2715 ISM 4930 9

SQL Exercise (Cont’d) COURSE ((Primary key: Course_ID) IS_REGISTERED (Primary key: Student_ID, Section_ID) Student_I D 38214 Section_I D 2714 Semester ISM 3112 Course_ Name Syst Analysis Syst Design 54907 2714 I - 2001 ISM 4212 Database 54907 2715 I - 2001 ISM 4930 Networking 66324 2713 I - 2001 Course_ID ISM 3113 I - 2001 10

SQL Exercise (Cont’d) • Write SQL queries to answer the following questions: – Is any instructor qualified to teach ISM 3113 and not qualified to teach ISM 4930? – How many students are enrolled in section 2714 during semester I – 2001? – Display all the courses (Course_Name) for which Professor Berndt has been qualified. – Which students were not enrolled in any courses during semester I – 2001? 11

Exercise • Write SQL codes to create the following tables CUSTOMER ORDER_LINE Customer _ID Customer_ Name City State 1 Value Furniture Plano TX 2 Home furnishings Albany NY 3 Eastern Furniture Carteret NJ 4 Furniture Gallery Plano TX Order_ID Product_ID Quan 1001 1 2 1001 2 2 1002 3 5 1003 3 3 12

Exercise (Cont’d) ORDER Order_ID Order_Date Customer_ID 1001 21 -Oct-2000 1 1002 21 -Oct-2000 4 1003 22 -Oct-2000 2 PRODUCT Product_ID Description Product_finish Standard_price 1 End Table Cherry 175 2 Coffee Table Natural Ash 200 3 Computer Desk Natural Ash 375 13

Exercise (Cont’d) • Use SQL to design the following queries: – How many different items were ordered on order number 1001? – List product ID and standard price for all desks and all tables that cost more than $200. – What furniture is not made of cherry? 14

Exercise (Cont’d) • Use SQL to design the following queries: – List all the customers who live in FL, TX and CA. – Find only states with more than one customer. – What are order numbers that have included furniture finished in natural Ash. – What are the names of all customers who have placed orders? 15

Exercise (Cont’d) • Use SQL to design the following queries: – For each customer who has placed an order, what is the customer’s name and order number? – Which customers have not placed any orders for computer desk? – List the product name and price with the highest standard price. 16