SQL : S TRUCTURED Q UERY L ANGUAGE

1. SQL : STRUCTURED QUERY LANGUAGE

2. Relational Algebra (formal mathematical approach) – not required • QBE ( Query By Example) - The MS Access approach (originally designed by Zloof at Borland for dBase and Paradox) • SQL (Structured Query Language) – Oracle and typical mainframe approach QUERIES

3. SQL (STRUCTURED QUERY LANGUAGE) • developed at IBM Research Labs at San Jose, Ca. in 1970's • D. Chamberlin was the leader of research group • originally spelled (and pronounced) SEQUEL • dominant data base language on mainframe computers • but available on mini and pc based dbms • ANSI (American National Standards Institute) approved

4. OUR EXAMPLE DATABASE student ( stuid, stuname, major, credits ) faculty ( facid, facname, dept, rank ) class ( course#, facid, sched, room ) enroll ( course#, stuid, grade )

5. to define the structure, we • CREATE TABLE (also ALTER TABLE and DROP TABLE) • to query and manipulate the information in the database, we • SELECT • UPDATE • INSERT • DELETE • CREATE VIEW FUNDAMENTAL SQL VERBS FOR ACTIVITIES

6. CREATE TABLE - THE SYNTAX (EASIER UNDERSTOOD BY EXAMPLES TO FOLLOW) • CREATE TABLE base-table-name (col-name data type [NOT NULL [WITH DEFAULT]][,col-name data type [NOT NULL [WITH DEFAULT]] ...,[PRIMARY KEY (col-name, [,col-name],...)],[FOREIGN KEY (col-name,[,col-name]...) REFERENCES (other-base-table-name)])

7. CREATE A TABLE CREATE TABLE student (stuid CHAR(9) NOT NULL, stuname CHAR(20) NOT NULL, major CHAR(20), credits SMALLINT, PRIMARY KEY (stuid));

8. Some Data Types in SQL • INTEGER • SMALLINT • Efficient way of representing integers between –215 to (215 – 1) • DECIMAL (p,q) • A decimal number p digits long, with q of these being decimal places; so, DECIMAL (5,2) represents a number with three digits to the left of the decimal, and two digits to the right of the decimal point. • CHAR (n) • Character string n characters long • DATE • Dates in the form DD-Month-YY or MM/DD/YYYY ; thus, February 16, 2010 could be represented as 16-February-2010 or 2/16/2010

9. CREATE A TABLE FORCING REFERENTIAL INTEGRITY CREATE TABLE class (course# CHAR(7) NOT NULL, facid CHAR(9), sched CHAR(7), room CHAR(4), PRIMARY KEY (course#), FOREIGN KEY (facid) REFERENCES faculty);

10. TO ALTER A TABLE STRUCTURE BY ADDING A FIELD ... ALTER TABLE class ADD ctitle CHAR(30);

11. THE SELECT STATEMENT (TO RETRIEVE DATA) SELECT [DISTINCT] col-name [,col-name] ... FROM table-name [,table-name]... [WHERE predicate] [GROUP BY col-name [,col-name] ... [HAVING predicate]] [ORDER BY col-name [,col-name] ...];

12. GET NAMES, ID'S AND CREDITS OF ALL IT MAJORS • These attributes are all in the student table • The SELECT is similar to the project operator of relational algebra, • but, unfortunately, is not at all like the select operator in the relational algebra • and, also, unfortunately, does not eliminate duplicates. ------------------------------------------------------------------- SELECT stuname, stuid, credits FROM student WHERE major = ‘IT';

13. GET ALL THE ATTRIBUTES OF IS FACULTY {The asterisk ( * ) in SELECT gives us all attributes} --------------------------------------------------------------- SELECT * FROM faculty WHERE dept = ‘IS'; <is equivalent to> SELECT facid, facname, dept, rank FROM faculty WHERE dept = ‘IS';

14. GET THE COURSE# FOR ALL COURSES IN WHICH STUDENTS HAVE EARNED GRADES {We will use the enroll table for actual enrollment and grades} SELECT course# FROM enroll; {The above will yield duplicate courses; to eliminate duplicates, use DISTINCT} SELECT DISTINCT course# FROM enroll;

15. GET ALL INFORMATION ABOUT ALL STUDENTS SELECT * FROM student;

16. GET NAMES AND ID'S OF FACULTY, ALPHABETIZED SELECT facname, facid FROM faculty ORDER BY facname; <if two faculty have the identical name, suppose we decide to order by dept.> SELECT facname, facid FROM faculty ORDER BY facname, dept;

17. GET NAMES OF ALL IT MAJORS WHO HAVE MORE THAN 60 CREDITS {in WHERE predicates, we can use the standard comparison operators >, <, >=, <=, =, and the standard logical operators AND, OR and NOT } SELECT stuname FROM student WHERE major = ‘IT' AND credits > 60;

18. FIND ID'S AND NAMES OF ALL STUDENTS TAKING IS431 {We can SELECT based on multiple tables.In enroll table we have the course#'s and stuid's. In student table, we have the stuid's and stunames. We will do the equivalent of a “cartesian product" in relational algebra. Records from each table must match on common stuid field.} SELECT enroll.stuid, stuname FROM student, enroll WHERE course# = ‘IS431' AND enroll.stuid = student.stuid {Note: in SELECT line, stuid needs to be qualified by either enroll (as we have done) or student}

19. {First, from class table, get course#s taught by given facid. Then, from enroll table, pick records with matching course#'s and join.} SELECT stuid, grade FROM class, enroll WHERE facid ='767564343' AND class.course# = enroll.course# ORDER BY stuid ASC; FIND ID AND GRADE OF ALL STUDENTS TAKING COURSES FROM FAC ID 767564343 AND ARRANGE IN ORDER BY STUID

20. FIND COURSE#'S, STUNAME'S AND MAJORS OF ALL STUDENTS ENROLLED IN COURSES TAUGHT {This requires attributes from THREE tables.} {From class table, get course#'s taught by given facid} {Then, from enroll table, get stuid's of students enrolled in course#s}{Then, from student table, get names and majors of selected students} SELECT enroll.course#, stuname, major FROM class, enroll, student WHERE class.course# = enroll.course# AND enroll.stuid = student.stuid;

21. each SQL function operates on a single column of a table • the SQL functions eliminate any null values, and work on non-null values • each SQL function returns a single value, defined as follows: • COUNT returns the number of values in a column • SUM returns the sum of values in a column • AVG returns the average of the values in the column • MAX returns the maximum of the values in the column • MIN returns the minimum of the values in the column • COUNT (DISTINCT column-name) eliminates duplicate values in the column • COUNT ( * ) counts the total number of rows including nulls & duplicates SQL'S BUILT-IN FUNCTIONS

22. FIND THE TOTAL NUMBER OF STUDENTS ENROLLED IN IS431 SELECT COUNT ( stuid) FROM enroll WHERE course# = 'IS431';

23. FIND THE NUMBER OF DEPARTMENTS WHICH HAVE FACULTY IN THEM SELECT COUNT (DISTINCT dept) FROM faculty;

24. FIND THE AVERAGE NUMBER OF CREDITS STUDENTS HAVE SELECT AVERAGE (credits) FROM student;

25. FIND THE STUID AND STUNAME WITH THE MAXIMUM NUMBER OF CREDITS {First find the maximum number of credits acheived by any student, in a subquery, then find the student(s) whose credits equal this maximum number of credits} SELECT stuid, stuname FROM student WHERE credits = ( SELECT MAX (credits) FROM student);

26. FIND THE NAMES AND ID'S OF ALL STUDENTS WHO HAVE LESS THAN THE AVERAGE NUMBER OF CREDITS OF ALL STUDENTS IN THE DATABASE SELECT stuname, stuid FROM student WHERE credits < (SELECT AVG (credits) FROM student) ;

27. ASSUMING EACH COURSE IS 3 CREDITS, LIST, FOR EACH STUDENT, THE NUMBER OF COURSES SELECT stuid, ' number of courses = ', credits/3 FROM student;

28. FOR EACH COURSE, SHOW THE NUMBER OF STUDENTS ENROLLED {The GROUP BY clause allows us to lump together all the records with the identical specified field value} SELECT course#, COUNT (DISTINCT stuid) FROM enroll GROUP BY course#;

29. FIND ALL COURSES HAVING FEWER THAN 3 STUDENTS SELECT course# FROM enroll GROUP BY course# HAVING COUNT ( * ) < 3;

30. GET THE COURSE#, SCHED & ROOM OF ALL IS COURSES {Want all IS-prefixed courses from IS118 to IS799} {We will use a LIKE in predicate and wildcards in the match string} {A " % " denotes a string of any length} SELECT course#, sched, room FROM class WHERE course# LIKE 'IS%' ;

31. FIND THE STUID AND COURSE# FOR ALL STUDENTS WHOSE GRADES ARE MISSING {In the WHERE clause, we can have "IS NULL" for a particular field.} SELECT stuid, course# FROM enroll WHERE grade IS NULL;

32. THE UPDATE OPERATOR UPDATE table-name SET column-name = expression [column-name = expression] ... [WHERE predicate]; {the SET is like an assignment statement in a HLL} {it is not necessary to specify the current value of a field}

33. CHANGE THE MAJOR OF STUDENT 123456789 TO IT UPDATE student SET major = ‘IT' WHERE stuid = '123456789';

34. UPDATE SEVERAL FIELDS IN A RECORD - CHANGE ANNBER TO IS DEPT AND ASSISTANT UPDATE faculty SET dept = ‘IS' rank = 'assistant' WHERE facname = 'annber';

35. THE INSERT OPERATOR • {The INSERT operator is used to put records into a table} • INSERT INTO table-name [(col-name [,col-name]...)] VALUES (constant [,constant]...); • {If we are inserting into all fields of a record, we don't have to list the individual fields}

36. INSERT A NEW FACULTY RECORD FOR JONES, ID#987654321 IN CHE AS INSTRUCTOR INSERT INTO faculty VALUES ('987654321', 'jones', 'che', 'instructor');

37. THE DELETE OPERATOR DELETE FROM table-name WHERE predicate; Aside: You must be careful when "deleting" to not delete a primary key from one table which is referenced by a foreign key in another table.

38. DELETE FROM ENROLLMENT ALL RECORDS FOR STUDENT 135792468 DELETE FROM enroll WHERE stuid = '135792468';