Structured Query Language Review

1. Structured Query Language Review ISYS 650

2. Language Overview • Three major components: • Data definition language, DDL • Create, Drop and Alter Tables or Views • Data manipulation language, DML • Updating database: • Insert, Delete, Update • Query database: • Select • Data control language (DCL) • Help DBA control the database: • Grant/revoke privileges to access the database, creating procedures, etc.

3. DDL • Table structure: • Field name • Field data type • Primary key • Integrity constraints: • Null • Domain constraint • Referential integrity • Etc.

4. CREATE TABLE • CREATE TABLE tableName(fields and data type separated by commas); • Ex. • CREATE TABLE employee( eid CHAR(5), ename VARCHAR(40), sex CHAR, salary NUMERIC(9,2), hire_Date DATE);

5. Integrity Enhancement Feature • Required data: NOT NULL • eid CHAR(5) NOT NULL, • Default value:DEFAULT sex CHAR DEFAULT ‘M’, • Field domain: CHECK(condition) • salary NUMERIC(9,2) CHECK (salary >= 100 AND salary <=10000), • sex CHAR DEFAULT ‘M’ CHECK (sex in (‘M’,’F”)), • PRIMARY KEY • PRIMARY KEY(sid) • PRIMARY KEY(sid, cid) • Unique – allow null value, the PRIMARY KEY constraint does not allow null. • ESSN CHAR(9) UNIQUE

6. Example • CREATE TABLE employee( eid CHAR(5) PRIMARY KEY, ename VARCHAR(40), sex CHAR DEFAULT ‘M’ CHECK (sex in (‘M’,’F’)), salary NUMERIC(9,2), hire_Date DATE);

7. Creating Table Through SubQuery • CREATE TABLE tableName • AS (Select query) • Ex. • CREATE TABLE newEmp • AS (SELECT empid, ename,salary FROM emp);

8. SQL Insert Command INSERT INTO tableName VALUES (field values separated by commas); INSERT INTO tableName (Column names separated by commas)VALUES (field values separated by commas); Ex 1. Customer table with CID, CNAME, CITY, RATING. a. INSERT INTO CUSTOMER VALUES (‘C1’, ‘SMITH’, ‘SF’, ‘A’); b. INSERT INTO CUSTOMER (CID, CNAME,RATING) VALUES (‘C1’, ‘SMITH’, ‘A’);

9. Record with Date Field • Oracle date format: • ‘dd-mmm-yyyy’ • Example: • insert into orders values('O7','c2','s1','10-oct-2007');

10. Inserting records from an existing table (Append) • INSERT INTO stu2 • (select * from student);

11. SQL Delete Command DELETE FROM tableName [WHERE criteria]; Ex 1. Delete a record from the Customer table. DELETE FROM CUSTOMER WHERE CID = ‘C1’;

12. SQL Update Command UPDATE tableName SET field = new value [WHERE criteria]; Ex 1. UPDATE CUSTOMER SET RATING = ‘A’ WHERE CID=‘C1’; Ex 2. UPDATE CUSTOMER SET CITY = ‘SF’, RATING = ‘A’ WHERE CID=‘C1’;

13. Dealing with Null • Null is a key word. We can use Null in the INSERT, UPDATE, and DELETE command. • Use IS NULL (or IS NOT NULL) in a criteria. • Examples: • INSERT INTO emp VALUES (‘e95’,’June’,’f’,NULL,5000); • UPDATE emp SET salary=null where empid=‘e99’; • SELECT * FROM emp WHERE salary IS NULL; • SELECT * FROM emp WHERE salary= NULL (not working);

14. Maintaining Referential Integrity • FOREIGN KEY (field) REFREENCES parentable DROP TABLE parent; CREATE TABLE parent ( pid char(3) not null, pname varchar(20), PRIMARY KEY (pid) ); drop table child; CREATE TABLE child ( cid char(3) not null, cname varchar(20), pid char(3), PRIMARY KEY (cid), FOREIGN KEY (pid) REFERENCES parent ON DELETE SET NULL );

15. ON DELETE • ON DELETE SET NULL • ON DELETE CASCADE

16. Basic SQL Select Commands

17. Basic Relational Query Operations • Selection • Projection • Natural Join • Sorting • Aggregation: Max, Min, Sum, Count, Avg • Total • Sub totals: Group By • Calculated field • Sub Queries

18. Examples • University database: • Student: SID, Sname, Sex, Major, GPA, FID • Account: SID, Balance • Faculty: FID, Fname, Phone • Course: CID, Cname, Credits, FID • Registration: SID, CID • Download the batch file OracleUniv.SQL to create the database.

19. Selection • Selection operation works on a single relation and defines a relation that contains records that satisfy the criteria.

20. Selection Syntax • SELECT * FROM tableName WHERE criteria; • Criteria: • =, <>, <, >, <=, >= • (), NOT, AND, OR • BETWEEN • WHERE salary BETWEEN 1000 AND 10000; • LIKE, NOT LIKE: % • WHERE ename LIKE ‘C%’ • Cannot say: WHERE ename = ‘C%’ • IN, NOT IN • WHERE eid IN (‘e1’,’e3’) • IS NULL • WHERE rating IS NULL • Arithmetic expression in criteria: • WHERE salary*0.1 < 500

21. ROWNUM Field • ROWNUM field is a pseudocolumn that applies to every table even though it is not displayed through a SELECT * command. • SELECT ROWNUM, empid FROM emp; • SELECT * FROM emp WHERE ROWNUM < = 3;

22. Projection • Projection operation works on a single relation and defines a vertical subset of the relation, extracting the values of specified attributes.

23. Projection Syntax SELECT fields FROM tableName; • Ex: SELECT empID, ename FROM Employee; • Alias: • Ex: SELECT eid AS empID, ename AS empName FROM emp; • Projection and Selection: • SELECT fields FROM tableName WHERE criteria;

24. Duplications due to Projection • Registration file: • SID CID • ------ -------- • s1 bics263 • s1 acct101 • s2 bics363 • s2 bics464 • s2 fin350 • s3 bics263 • SELECT sid FROM Registration; • Note: What is the meaning of this result?

25. DISTINCT/UNIQUE • SELECT DISTINCT fields FROM tableName WHERE criteria; • Ex. SELECT DISTINCT sid FROM Registration; • UNIQUE • SELECT UNIQUET fields FROM tableName WHERE criteria; • Ex. SELECT UNIQUE sid FROM Registration;

26. Calculated Fields • SELECT empid,ename,salary*0.1 AS tax FROM emp;

27. Oracle Functionshttp://download.oracle.com/docs/cd/B19306_01/server.102/b14200/toc.htm • Text functions: • UPPER, LOWER, INITCAP • Ex. SELECT INITCAP(Fname) FROM Faculty; • SUBSTR(text,starting position, # of chars) • LPAD(text,length of text,padding symbol) • LPAD(salary,10,’*”) • CONCAT(text1,text2) • We can also use concatenation operator, || • Date functions: • SYSDATE,NEXT_DAY • Ex. NEXT_DAY(SYSDATE,’MONDAY’) • MONTHS_BETWEEN(date1,date2) • Ex. SELECT Ename, MONTHS_BETWEEN(SYSDATE, Hire_Date) FROM Employee; • ADD_MONTHS(date,# of months added)

28. Using the SQL CASE Function SELECT sid, sname, CASE WHEN gpa < 2.0 THEN 'Poor' WHEN gpa < 3.0 THEN 'Good' ELSE 'Excellent' END AS GPAGroup FROM student;

29. Natural Join • The two relations must have common attributes. • Combines two relations to form a new relation where records of the two relations are combined if the common attributes have the same value. One occurrence of each common attribute is eliminated.

30. Natural Join Syntax • SELECT * FROM table1 NATURAL JOIN table2; • Example: • SELECT * FROM student NATURAL JOIN faculty; • Note: What if the FID in the student table is named AdvisorID?

31. Standard Join Syntax • SELECT * FROM table1, table2 WHERE table1.JoinAttribute = table2.JoinAttribute; • SELECT * FROM student, faculty • WHERE student.fid = faculty.fid; • Table name alias: • SELECT * FROM student s, faculty f • WHERE s.fid = f.fid; • On: • SELECT * FROM student s JOIN faculty f ON s.fid=f.fid; • Difference between natural join and standard join?

32. Self Join • Recursive relationship: • Employee supervise Employee • Ex: EmpFile: empID, empName, superID • Find employees’ supervisor name: • SELECT e1.empid, e1.empname,e2.empname as sueprname • FROM empfile e1, empfile e2 • WHERE e1.superid=e2.empid;

33. Sorting • ORDER BY fieldName [DESC] • SELECT * FROM student ORDER BY sname; • SELECT * FROM student ORDER BY sname DESC; • More than one field: • SELECT * FROM student ORDER BY major, sname; • Note 1: Don’t name a table “ORDER” because ORDER is a SQL keyword. • Note 2: If there is a WHERE clause, then the ORDER BY clause should be placed after the WHERE clause.

34. Aggregates • SELECT AVG(fieldName) FROM tableName; • COUNT(fieldName), COUNT(*) • COUNT(DISTINCT fieldName) • MAX(fieldName) • MIN(fieldName) • SUM(fieldName) • More than one aggregate: • SELECT AVG(fieldName), MAX(fieldName), MIN(fieldName) FROM tableName; • With alias: • SELECT AVG(gpa) AS avggpa, COUNT(sid) AS scount • FROM student;

35. GROUP BY • GROUP By one field: • SELECT groupingFields, function(fieldname) FROM tablename GROUP BY groupingFields; • SELECT major, count(sid) FROM student GROUP BY major; • GROUP BY more than one field: • SELECT sex, major, count(sid) FROM student GROUP BY sex, major; • Compute more than one subtotals: • SELECT major, count(sid), avg(gpa) FROM student • GROUP BY major • Note 1: All grouping fields in the SELECT clause must be included in the GROUP BY clause). • Note 2:WHERE clause must come before the GROUP BY: • SELECT major, count(sid) FROM student WHERE GPA > 3.0 GROUP BY major;

36. Compute Subtotals from a Join • Compute the number of courses taken by each student: • SELECT sid, sname, COUNT(cid) • FROM student NATURAL JOIN registration • GROUP BY sid, sname; • Compute the number of students advised by each faculty advisor: • SELECT fid, fname, COUNT(sid) • FROM faculty NATURAL JOIN student • GROUP BY fid, fname;

37. Oracle’s Natural Join May Produce Incorrect Results select sid,sname,sum(units) from student natural join registration natural join course group by sid,sname; select sid,sname,sum(units) from (student natural join (registration natural join course)) group by sid,sname;

38. Adding a Criteria for the Sub Totals with HAVING • Find majors that have at least 5 students: • SELECT major, count(sid) FROM student • GROUP BY major • HAVING count(sid) > 5; • Sometime the aggregates are not required to display: • SELECT major FROM student • GROUP BY major • HAVING count(sid) > 5;

39. Nesting Aggregates • SELECT MAX(AVG(gpa)) • FROM student • GROUP BY major;

40. University Database Questions • Q1: Display College of Business students’ ID and name. • Q2: Display students’ ID and name who owe university more than $2000. • Q3: Display faculty’s name and phone if the student’s GPA is lower than 2.0. • Q4: Display faculty’s name and phone if the faculty advises at least 2 student. • Q5: Display students’ ID and name who are taking at least 2 course. • Q6: Display students’ ID and name who are taking 464 and GPA < 2.0. • Q7: Display CID, Cname and the number of students in each course. • Q8: Display SID, Sname, TotalUnits

41. SQL Sub (or Nested ) Query

42. Examples • Q: Find students whose GPA is below the average. • The criteria itself requires a SQL statement. • SELECT * FROM student • WHERE gpa < (SELECT AVG(gpa) FROM student); • Q: Find employees with higher than average salary.

43. Sub Query with IN • SELECT sid, sname FROM student • WHERE sid in (‘S1’, ‘S2’); • Use a subquery to retrieve keys that meet criteria. • Example: Display students’ ID, name who owe university more than $2000. (Note: not showing balance) • SELECT sid,sname • FROM student • WHERE SID IN (SELECTSIDFROM account WHERE balance > 2000); • Example: Display students’ ID, name and Balance who owe university more than $2000.

44. Sub Query with IN, NOT IN • Use a subquery to retrieve keys that meet criteria. • Q: Display faculty’s ID and name if the faculty advises at least one student. • SELECT fid, fname FROM faculty • WHERE fid IN (SELECT DISTINCT fid FROM student); • Q: Display faculty’s name and name if the faculty does not advise any student. • SELECT fid, fname FROM faculty • WHERE fid NOT IN (SELECT DISTINCT fid FROM student);

45. Subquery in the SELECT List • SELECT eid,ename,salary,(select AVG(salary) from employee)as AvgSal FROM employee; • SELECT eid,ename,salary-(select AVG(salary) from employee)as DeviateFromMeanFROM employee;

46. Multiple Levels Subquery • Find students who are taking one-unit courses and display their ID and name. • Join: • Select sid, sname, cid from student natural join registration natural join course where units=1; • Without Join: • Select sid, sname From Student • Where sid in (select sid from registration where cid in • (select cid from course where units=1));

47. Correlated SubQueries • A correlated subquery references values of the main query. • In a correlated subquery, the main query provides values which are used by subquery’s WHERE clause. • The subquery is executed repeatedly, once for each row that might be selected by the main query.

48. Passing Values from Main Query to SubQuery • Find students taking more than 2 courses: • select * from student s • where (select count(cid) from registration where sid=s.sid)>2 ;

49. EXISTS, NOT EXISTS • Test whether a subquery returns any rows. • It returns True/False

50. Find faculty who advise at least one student. • 1. Subquery: • SELECT fid,fname FROM faculty • WHERE fid in (SELECT DISTINCT fid FROM student); • 2. Correlated query: • SELECT fid,fname FROM faculty • WHERE EXISTS (SELECT * FROM student • WHERE fid = faculty.fid);