1 / 9

CS 3 4 0 2 Database Systems

CS 3 4 0 2 Database Systems. Lab 5 TAs. EXISTS,NOT EXISTS vs. IN, NOT IN. EXISTS: SELECT COLUMN1 FROM TABLE1 WHERE EXISTS (subquery) Result type: boolean , Result values: return true if a subquery contains any rows

gavin
Download Presentation

CS 3 4 0 2 Database Systems

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. CS3402 Database Systems Lab 5 TAs

  2. EXISTS,NOT EXISTS vs. IN, NOT IN • EXISTS: SELECT COLUMN1 FROM TABLE1 WHERE EXISTS (subquery) • Result type: boolean , Result values: return true if a subquery contains any rows If the result value of subquery is true, then the record will be returned. (for EXISTS) If the result value of subquery is false, then the record will be returned. (for NOT EXISTS) • IN: SELECT COLUMN1 FROM TABLE 1 WHERE COLUMN2 IN (subquery) • EXISTS can be equivalent to IN but used in different structures. • NOT EXISTS can also be equivalent to NOT IN but used in different structures.

  3. SELECT * FROM ASSIGNMENT B WHERE B.EMPNO=‘7001’ SELECT * FROM ASSIGNMENT B WHERE B.EMPNO=‘7003’ Exists and will be returned • E.g. two tables: EMP(EMPNO,ENAME,DEPTNO) ASSIGNMENT(EMPNO,PNAME) , where PNAME is project name • Find all the employees who are assigned at least one assignment. (Use two different solutions, hints: EXISTS,IN). Return 7001,7002 NOT EXISTS and will not be returned SELECT EMPNO,ENAME FROM EMP A WHERE EXISTS (SELECT * FROM ASSIGNMENT B WHERE B.EMPNO=A.EMPNO) SELECT EMPNO,ENAME FROM EMP WHERE EMPNO IN (SELECT DISTINCT EMPNO FROM ASSIGNMENT)

  4. SELECT EMPNO,ENAME FROM EMP A WHERE NOT EXISTS (SELECT * FROM ASSIGNMENT B WHERE B.EMPNO=A.EMPNO) • Find all the employees who are not assigned any assignment. (two solutions) Return 7003,7004 SELECT * FROM ASSIGNMENT B WHERE B.EMPNO=‘7001’ SELECT EMPNO,ENAME FROM EMP WHERE EMPNO NOT IN (SELECT DISTINCT EMPNO FROM ASSIGNMENT) SELECT * FROM ASSIGNMENT B WHERE B.EMPNO=‘7003’ EXISTS and will not be returned NOT EXISTS but will be returned

  5. Mid-term Quiz • SPJ(S#,P#,J#,QTN), • S#:SUPPLIER,P#:PART,J#:PROJECT,QTN:quantity • Semester 09/10: Find all the PROJECTS which have only one SUPPLIER • HINT: Joining a table to itself

  6. Wrong answer: SELECT J# FROM SPJ GROUP BY J# HAVING COUNT(*)=1; Return result: P3 Correct: (Return result: P2,P1) 1. SELECT DISTINCT J# FROM SPJ A WHERE S# =ALL(SELECT S# FROM SPJ B WHERE B.J# = A.J#); 2. SELECT DISTINCT J# FROM SPJ A WHERE NOT EXISTS (SELECT * FROM SPJ B WHERE B.J# = A.J# AND B.S# != A.S#);

  7. Mid-term Quiz • Semester 10/11 • SELECT DISTINCT P# FROM SPJ X WHERE NOT EXISTS ( SELECT * FROM SPJ Y WHERE Y.P#=X.P# AND Y.J#<>X.J# );

  8. Mid-term Quiz • Get all pairs of part numbers such that some suppliers supply both pars. • Quiz: Write relation algebra. • Now: Write a SQL query.

More Related