Lists, Views, Describing tables

1. Lists, Views, Describing tables Kris Pepper

2. Where are we • Finishing up integrity, sets and lists, and now adding in views and describe. • Finishing homework 3. • Starting final project.

3. LIST • Compare two lists of similar attributes IN >ANY >SOME >ALL

4. List Comparison - in • Compare to values in a list - in or not in • List student first names of sid 1111 and 2222 Select distinct fname from students where SID in (1111, 2222); Result: Nandita Sydney

5. List Comparison - in • Can use a subquery to create the list: • List student first names who are enrolled in term f96 Select distinct SID from enrolls where term = ‘f96’); Select fname from students where sid in (select distinct sid from enrolls where term = ‘f96’); RESULT: FNAME -------------------- Nandita Sydney Susan Naveen

6. Try One List first names of students in course 1030, 1031 or 1035 Steps: 1: write the base query on students ending with IN (a list of SIDS in the course). 2: write a query of enrolls listing sids in these courses.

7. Answer List List first names of students in course 1030, 1031 or 1035 Steps: 1: write the base query on students ending with IN (a list of SIDS in the course). Select fname from students where sid in (a list of SIDS in the course) 2: write a query of enrolls listing sids in these courses. Select distinct sid from enrolls where lineno in (1030, 1031, 1035) 3. Put it together: Select fname from students where sid in (Select distinct sid from enrolls where lineno in (1030, 1031, 1035));

8. List Comparison - any, all, some > ,< , <>,=, >=, <= • Select student ids with scores greater than all in the list: Select distinct sid from scores where points >= all (90, 340, 70) Substitute a query listing all points

9. List Comparison - any, all, some • Select scores greater than all in the list: Select distinct sid from scores where points >= all (select points from scores); 1111 • You try: List student ids with scores less than all in the list

10. List Comparison - any, all, some Answer: Select distinct sid from scores where points <= all (select points from scores); 2222 Try one more: List student ids with scores which are greater than the average score

11. List Comparison - any, all, some • Not a list answer • Answer: Select distinct sid from scores where points < (select avg(points) from scores);

12. Sub-Query using outer • Sub query acts for every row in the main (outer) query • Use the fields from the outer query in the inner query • List of courses in which all individual components grades are higher than B: Select term, lineno, cno from courses where b <all (select points from scores where scores.lineno = courses.lineno and scores.term = courses.term);

13. Exists • If subquery has any row in result, then exists is TRUE • If no row results, then FALSE Select all courses in which 1111 is enrolled: select distinct cno from courses where exists (select 'a' from enrolls where courses.lineno = enrolls.lineno and sid = ‘1111’); You try: list all the fields from the student table where any score is greater than 90. (use exists)

14. Exists • Answer: • select * from students where exists (select ‘a’ from scores where students.sid = scores.sid and points > 90);

15. Important Stuff • Database checks integrity • Subqueries help make lists for where conditions • In, All, Some, Any • Exists

16. Views • Like creating a new table, but only doesn’t physically hold data. • Can use in other queries. • Format: • CREATE VIEW Viewname AS Select statement

17. VIEWS - SAMPLE • SQL> create view sids_in_course as select distinct sid from enrolls where lineno in (1030, 1031, 1035); • View created. • SQL> select * from sids_in_course; SID ----- 1111 2222 4444 5555 6666 Use it in the query from before: Select fname from students where sid in (select * from sids_in_course);

18. VIEWS • Try one: • Create a new view called course_with_name listing all the courses with the course number and name (linking courses and catalog) • When you are done, format the columns: column ctitle format a10 column lineno format 9999 • Select * from course_with_name; • Select * from cat; Create view course_with_name as select courses.* , ctitle from courses, catalog where courses.cno = catalog.cno; Select * from course_with_name

19. Describe tables • Define with CREATE, ALTER, DROP • Describe command shows basic info • All definitions stored in system tables

20. Some commands to describe • select * from cat; • select view_name, text from user_views;

21. cols • SQL> column table_name format a10 • SQL> column column_name format a10 • SQL> column data_type format a10 • SQL> column data_default format a10 select table_name, column_name, data_type, data_length, data_precision, data_default, nullable from cols TABLE_NAME COLUMN_NAM DATA_TYPE DATA_LENGTH DATA_PRECISION DATA_DEFAU N ---------- ---------- ---------- ----------- -------------- ---------- - ODETAILS QTY NUMBER 22 Y ORDERS ONO NUMBER 22 5 N ORDERS CNO NUMBER 22 5 Y

22. User_cons_columns • select column_name, constraint_name, position from user_cons_columns where table_name = 'ORDERS'; COLUMN_NAM CONSTRAINT_NAME POSITION ---------- ------------------------------ ---------- ONO SYS_C0016471 ONO SYS_C0016472 1 CNO SYS_C0016473 1 ENO SYS_C0016474 1

23. user_constraints • Table_name = table that has the constraint • R_constraint_name = foreign key constraint • Constraint_type = P = primary key; R = foreign key

24. Query on user_constraints • select constraint_name , constraint_type, r_constraint_name, search_condition from user_constraints where table_name = 'COURSES‘ CONSTRAINT_NAME CONSTRAINT R_CONSTRAI SEARCH_CON --------------- ---------- ---------- ---------- SYS_C0011332 C "TERM" IS NOT NULL SYS_C0011333 C "LINENO" IS NOT NULL SYS_C0011334 C "CNO" IS NOT NULL SYS_C0011335 C a > 0 SYS_C0011336 C b > 0 SYS_C0011337 C c > 0 SYS_C0011338 C d > 0 SYS_C0011339 P SYS_C0011340 R SYS_C00113

25. Showing primary keys • select f.table_name, f.constraint_name, f.column_name from user_constraints k, user_cons_columns f where k.table_name = f.table_name and k.constraint_name = f.constraint_name and k.constraint_type = 'P' ; TABLE_NAME CONSTRAINT_NAME COLUMN_NAM ---------- ------------------------------ ---------- COMPONENTS SYS_C0011347 TERM COMPONENTS SYS_C0011347 LINENO COMPONENTS SYS_C0011347 COMPNAME ENROLLS SYS_C0011352 SID ENROLLS SYS_C0011352 TERM ENROLLS SYS_C0011352 LINENO

26. Foreign keys select f.table_name, f.constraint_name, f.column_name from user_constraints k, user_cons_columns f where k.r_constraint_name = f.constraint_name and k.constraint_type = 'R' ; • TABLE_NAME CONSTRAINT_NAME COLUMN_NAM • ---------- ------------------------------ ---------- • COMPONENTS SYS_C0011347 LINENO • COMPONENTS SYS_C0011347 COMPNAME • ENROLLS SYS_C0011352 SID • ENROLLS SYS_C0011352 TERM • ENROLLS SYS_C0011352 LINENO • TEST9 SYS_C0012743 FIELD1

27. Checks • select f.table_name, f.constraint_name, f.column_name, k.search_condition from user_constraints k, user_cons_columns f where k.table_name = f.table_name and k.constraint_name = f.constraint_name and k.constraint_type = 'C' ; TABLE_NAME CONSTRAINT_NAME COLUMN_NAM SEARCH_CON ---------- --------------- ---------- ---------- STUDENTS SYS_C0011330 LNAME "LNAME" IS NOT NULL COURSES SYS_C0011332 TERM "TERM" IS NOT NULL COURSES SYS_C0011333 LINENO "LINENO" IS NOT NULL

28. Important Stuff • Database checks integrity • Subqueries help make lists for where conditions • In, All, Some, Any • Exists • Views remain • Data dictionary can be queried