1 / 36

Relational Operators, SQL, and Access Query

Relational Operators, SQL, and Access Query. ISYS 562. Relational Operators. Set operators: Union, intersection, difference, Cartesian product Relational operators: Selection, projection, join, etc. Union. Set1={A, B, C} Set2={C, D, E} Union: Members in Set 1 or in Set 2

blaze-rowe
Télécharger la présentation

Relational Operators, SQL, and Access Query

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Relational Operators, SQL, and Access Query ISYS 562

  2. Relational Operators • Set operators: Union, intersection, difference, Cartesian product • Relational operators: Selection, projection, join, etc.

  3. Union • Set1={A, B, C} • Set2={C, D, E} • Union: Members in Set 1 or in Set 2 • Set1 U Set 2 = {A, B, C, D, E} • Or

  4. Intersect • Members in Set 1 and in Set 2 • Set1 ∩ Set2={C} • And

  5. Difference • Set1 – Set2: Members in Set1 but not in set2 = {A,B} • Set2 – Set1:Members in Set2 but not in set1 = {D, E} • Set1-Set2 ≠ Set2 – Set1

  6. Use Union and Difference to Simulate Intersect • Set1 ∩ Set2 = Set1 – (Set1 – Set2)

  7. Files as Sets • Business students’ file: BusSt • Science student’s file: SciSt • BusSt U SciSt: • BusSt ∩ SciSt • BusSt – SciSt • Spring 04 Student file: S04St • Fall 04 Student file: F04St • S04St – F04St • F04St – S04St

  8. Product • Set1 = {a, b, c} • Set2 = {X, Y, Z} • Set1 X Set2 = {aX, aY aZ, bX, bY, bZ, cX, cY, cZ}

  9. Faculty File: • FID Fname • F1 Chao • F2 Smith • Student File: • SID Sname FID • S1 Peter F1 • S2 Paul F2 • S3 Smith F1 • Faculty X Student:

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

  11. Duplications due to Projection • WorkLog file: • EID PjID Hours • E1 P2 5 • E1 P1 4 • E2 P2 6 • E2 P1 8 • E3 P1 4 • Project eid from (WorkLog) • Project Hours from (WorkLog) • In practice, users determine whether to eliminate duplicates: • SELECT DISTINCT EID FROM WorkLog; • SELECT HOURS FROM WorkLog WHERE PjID = ‘P1’;

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

  13. Aggregate Functions • Max, Min, Sum, Count, Avg • Aggregates by grouping fields

  14. Student: SID,Sname, GPA, Sex, Major • Enroll: SID, CID • Course: CID, Cname, Credits • Queries: • Number of students in each course • CID, CName, NumbeOfStudents • Total credits for each student: • SID, Sname, TotalCredits

  15. Outer Join • Records in a relation that do not have matching values are included in the result relation. Missing values are set to null.

  16. Outer Join Exmple • Product Table: • PID Pname • P1 TV • P2 VCR • P3 Computer • P4 Tape • P5 DVD • TotalSales • PID TotalSales • P1 50 • P3 60 • P5 40 • Product Join TotalSales • Product OuterJoin Totalsales

  17. Branch: BID City B1 SF B2 SM B3 SJ Full Outer Join: BID City PID B1 SF P3 B2 SM Null B3 SJ P2 Null LA P1 Right Outer Join: BID City PID B1 SF P3 B3 SJ P2 Null LA P1 Property: PID City P1 LA P2 SJ P3 SF Left Outer Join: BID City PID B1 SF P3 B2 SM Null B3 SJ P2

  18. Structured Query Language

  19. Language Overview • Two major components: • Data definition language • Create Table • Data manipulation language • Updating database: • Insert, Delete, Update • Query database: • Select

  20. 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);

  21. 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’);

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

  23. 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’, SET RATING = ‘A’ WHERE CID=‘C1’;

  24. Selection • SELECT * FROM tableName WHERE criteria; • Criteria: • =, <>, <, >, <=, >= • (), NOT, AND, OR • BETWEEN • WHERE salary BETWEEN 1000 AND 10000; • LIKE, NOT LIKE: *, %, _ • WHERE ename LIKE “C*” • IN, NOT IN • WHERE eid IN (‘e1’,’e3’) • IS NULL • WHERE rating IS NULL

  25. Projection: • SELECT fields FROM tableName WHERE criteria; • SELECT DISTINCT fields FROM tableName WHERE criteria; • Field name alias: AS • Ex: SELECT eid AS empID, ename AS empName FROM emp; • Product: • SELECT fields FROM table1, table2; • table name alias: • Ex: SELECT s.*, f.* FROM student s, faculty f;

  26. Natural Join • SELECT * FROM table1 NATURAL JOIN table2; • SELECT * FROM student NATURAL JOIN faculty; • 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; • Other forms: • FROM student s JOIN faculty f ON s.fid=f.fid; • FROM student s INNER JOIN faculty f ON s.fid=f.fid; • FROM student JOIN faculty USING fid; • Not supported by Oracle

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

  28. Set Operators • Union compatible • (SELECT * FROM table1) • UNION (SELECT * FROM table2); • INTERSECT (SELECT * FROM table2); • MINUS (SELECT * FROM table2);

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

  30. GROUP BY • SELECT groupingField, function(fieldname) FROM tablename GROUP BY groupingField; • SELECT major, count(sid) FROM student GROUP BY major; • All field names in the GROUP BY clause must be included in the retrieved fields. • Compute the number of courses taken by each student: • SELECT sid, sname, COUNT(cid) • FROM student NATURAL JOIN enroll • GROUP BY sid, sname; • WHERE clause must come before the GROUP BY: • SELECT major, count(sid) FROM student WHERE GPA > 3.0 GROUP BY major;

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

  32. Sub (or Nested ) Query • Q: Find students whose GPA is below the average. • The criteria itself required a SQL statement. • SELECT * FROM student • WHERE gpa < (SELECT AVG(gpa) FROM student);

  33. Sub Query with IN • Q: Find students who take at least one course and display their ID and name. • SELECT sid, sname FROM • student NATURAL JOIN enroll • GROUP BY sid • HAVING COUNT(cid) > 1; • SELECT sid, sname FROM student • WHERE sid IN (SELECT DISTINCT sid FROM enroll); • Q: Find students who take more than 5 courses and display their ID and name. • SELECT sid, sname FROM student • WHERE sid IN (SELECT sid FROM enroll GROUP BY sid • HAVING COUNT(cid) > 5);

  34. Sub Query with ALL/SOME/ANY • Q: Find students whose gpa is greater than all/some bus majors’ gpa: • SELECT sid, sname FROM student • WHERE gpa > ALL(SELECT gpa FROM student WHERE major=‘bus’); • SELECT sid, sname FROM student • WHERE gpa > SOME (SELECT gpa FROM student WHERE major=‘bus’); • SELECT sid, sname FROM student • WHERE gpa > ANY (SELECT gpa FROM student WHERE major=‘bus’);

  35. Access Query • Selection, Projection, Product, Join, Outer Join • Calculated field, alias, parameter query • GroupBy, Having • Union, Intersect, Minus • Wizards: CrossTab, Find Duplicates, Find Unmatched • Others: • Make table, update

  36. Examples • University database: • Student: SID, Sname, Sex, Major, GPA, FID • Account: SID, Balance • Faculty: FID, Fname • Course: CID, Cname, Credits • StudentCourse: SID, CID • Questions: • Display student names who owe at least 2000. • Display faculty names who advise at least one student • Display faculty names who do not advise any student • Display faculty names who advise more than 2 students • Display total credits for all students • Find students enrolled in 263 • Find students enrolled in 263 and 363

More Related