ITEC 3220A Using and Designing Database Systems

1. ITEC 3220AUsing and Designing Database Systems Instructor: Gordon Turpin Course Website: www.cse.yorku.ca/~gordon/itec3220S07 Office: CSEB3020

2. Chapter 8 Advanced Structured Query Language (SQL)

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

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

5. 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;

6. 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

7. 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

8. Faculty_ ID Student_ ID Student_ Name Course_ID Date_ Qualified 2143 38214 Letersky ISM3112 9/1988 54907 3467 Altvater ISM4212 9/1995 66324 3467 Aiken ISM4930 9/1996 70542 4756 Marra ISM3113 9/1991 4756 ISM3112 9/1991 SQL Exercise (Cont’d) STUDENT (Primary key: Student_ID) IS_QUALIFIED (Primary key: Faculty_ID, Course_ID)

9. Faculty_ID Section_ID Faculty_Name Course_ID 2143 2712 Birkin ISM3113 2713 3467 Berndt ISM3113 4756 2714 Collins ISM4212 2715 ISM4930 SQL Exercise (Cont’d) FACULTY (Primary key: Faculty_ID) SECTION (Primary key: Section_ID)

10. Student_ID Course_ID Section_ID Course_ Name Semester 38214 ISM3113 2714 Syst Analysis I - 2001 ISM3112 54907 2714 Syst Design I - 2001 54907 ISM4212 2715 Database I - 2001 ISM4930 66324 2713 Networking I - 2001 SQL Exercise (Cont’d) COURSE ((Primary key: Course_ID) IS_REGISTERED (Primary key: Student_ID, Section_ID)

11. 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?

12. Exercise • Write SQL codes to create the following tables CUSTOMER ORDER_LINE

13. Exercise (Cont’d) ORDER PRODUCT

14. 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?

15. 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?

16. 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.