1 / 21

Correlated SubQueries and Miscellaneous Topics

Correlated SubQueries and Miscellaneous Topics ISYS 464 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.

albert
Télécharger la présentation

Correlated SubQueries and Miscellaneous Topics

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. Correlated SubQueries and Miscellaneous Topics ISYS 464

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

  3. Project Table: PID, Pname, Budget ProjExpense Table: PID, EDate, Amount • Find projects where total expenses exceeding budget. • Method 1: Using natural join and subtotal • SELECT pid, pname, budget, Sum(Amount) • FROM project NATURAL JOIN Expense • GROUP BY pid, pname,budget • HAVING budget < SUM(Amount); • If Sum(Amount) is not required to show: • SELECT pid, pname, budget • FROM project NATURAL JOIN Expense • GROUP BY pid, pname,budget • HAVING budget < SUM(Amount); • Method 2:Can we use sub query to avoid join? • SELECT pid, pname, budget FROM project • WHERE budget < (SELECT SUM(expense) FROM Expense);

  4. Passing Values from Main Query to SubQuery • SELECT pid, pname, budget • FROM project • WHERE budget < • (SELECT SUM(Amount) FROM Expense WHERE pid=project.pid);

  5. Find students taking at least 3 courses. • 1. Join: • SELECT sid, sname • FROM student NATURAL JOIN registration • GROUP BY sid, sname • HAVING COUNT(cid) >=3; • 2. Subquery: • SELECT sid, sname FROM student • WHERE sid IN (SELECT sid FROM registration GROUP BY sid • HAVING COUNT(cid) >=3); • 3. Correlated query: • SELECT sid, sname FROM student • WHERE 3 <= (SELECT COUNT(cid) FROM registration WHERE sid = student.sid);

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

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

  8. Find students taking 3 courses • 1. Sub query: • SELECT sid, sname FROM student • WHERE sid IN (SELECT sid FROM registration GROUP BY sid • HAVING COUNT(cid) = 3); • 2. Correlated query: • SELECT sid, sname FROM student • WHERE 3 = (SELECT COUNT(cid) FROM registration WHERE sid=student.sid);

  9. SELECT List Containing CorrelatedSub Query SELECT sid, sname, (SELECT COUNT(cid) FROM registration WHERE sid=student.sid) AS courses FROM student;

  10. Display project budget and total expenses Select pid,pname,budget,(Select Sum(amount) From projexpense Where pid=project.pid)as TotalExpense From project; Find projects where total expenses exceeding budget Select pid,pname,budget,totalexpense from (Select pid,pname,budget, (Select sum(amount) from projexpense where pid=project.pid) as TotalExpense From project) Where budget<totalExpense; Note: Calculated field cannot used in the WHERE clause.

  11. Multiple Levels of Nesting • Find student who are taking 1-unit course. • Regular subquery: • SELECT * FROM student WHERE sid IN ( • SELECT sid FROM registration WHERE cid IN(SELECT cid FROM course WHERE units = 1)); • Correlated subquery • SELECT sid,sname FROM student WHERE exists (SELECT sid FROM registration WHERE sid=student.sid and exists (SELECT * FROM course WHERE cid=registration.cid and units = 1));

  12. Miscellaneous Topics

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

  14. Views • A database view is: • a virtual or logical table based on a query. • a stored query. • CREATE VIEW viewname AS query; • CREATE VIEW femalestudent AS • SELECT * FROM student WHERE sex=‘f’; • CREATE OR REPLACE VIEW femalestudent AS SELECT * FROM student WHERE sex=‘f’;

  15. READ ONLY Views • CREATE VIEW viewname AS query • WITH READ ONLY; • Ex: • CREATE VIEW readEmp • AS (SELECT * FROM emp) • WITH READ ONLY;

  16. Updating Through Views • A view is updatable if • the update command does not violate database constraints; • The view is not read only.

  17. Materialized Views • Database snapshot • A physical copy • CREATE MATERIALIZED VIEW mvOrders • REFRESH COMPLETE • START WITH SYSDATE NEXT SYSDATE+7 • AS SELECT * FROM Orders; • DROP MATERIALIZED VIEW viewname;

  18. DROP VIEW • DROP VIEW viewname

  19. Indexes • Field declared as PRIMARY KEY will have an index. • CREATE INDEX indexname • ON tablename (columnnames separated by commas); • Ex: • CREATE INDEX fkFID • ON student (fid); • DROP INDEX indexname;

  20. 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 ); Insert into parent values ('p1','peter'); Insert into parent values ('p2','paul'); Insert into child values ('c1','mary','p1'); Insert into child values ('c2','john','p1'); Insert into child values ('c3','mary','p2');

  21. ON DELETE • ON DELETE SET NULL • ON DELETE CASCADE

More Related