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.
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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.
PID, Pname, Budget
PID, EDate, Amount
SELECT sid, sname, (SELECT COUNT(cid) FROM registration
WHERE sid=student.sid) AS courses
Select pid,pname,budget,(Select Sum(amount) From projexpense Where pid=project.pid)as TotalExpense
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
Where budget Note: Calculated field cannot used in the WHERE clause.
Note: Calculated field cannot used in the WHERE clause.
DROP TABLE parent;
CREATE TABLE parent (
pid char(3) not null,
PRIMARY KEY (pid) );
drop table child;
CREATE TABLE child (
cid char(3) not null,
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');