Correlated subqueries and miscellaneous topics
Download
1 / 21

Correlated SubQueries and Miscellaneous Topics - PowerPoint PPT Presentation


  • 438 Views
  • Updated On :

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.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Correlated SubQueries and Miscellaneous Topics' - albert


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

Correlated subqueries l.jpg
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.


Slide3 l.jpg

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


Passing values from main query to subquery l.jpg
Passing Values from Main Query to SubQuery

  • SELECT pid, pname, budget

  • FROM project

  • WHERE budget <

  • (SELECT SUM(Amount) FROM Expense WHERE pid=project.pid);


Find students taking at least 3 courses l.jpg
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);


Exists not exists l.jpg
EXISTS, NOT EXISTS

  • Test whether a subquery returns any rows.

  • It returns True/False


Find faculty who advise at least one student l.jpg
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);


Find students taking 3 courses l.jpg
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);


Select list containing correlated sub query l.jpg
SELECT List Containing CorrelatedSub Query

SELECT sid, sname, (SELECT COUNT(cid) FROM registration

WHERE sid=student.sid) AS courses

FROM student;


Slide10 l.jpg

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.


Multiple levels of nesting l.jpg
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));



Nesting aggregates l.jpg
Nesting Aggregates

  • SELECT MAX(AVG(gpa))

  • FROM student

  • GROUP BY major;


Views l.jpg
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’;


Read only views l.jpg
READ ONLY Views

  • CREATE VIEW viewname AS query

    • WITH READ ONLY;

  • Ex:

    • CREATE VIEW readEmp

    • AS (SELECT * FROM emp)

    • WITH READ ONLY;


Updating through views l.jpg
Updating Through Views

  • A view is updatable if

    • the update command does not violate database constraints;

    • The view is not read only.


Materialized views l.jpg
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;


Drop view l.jpg
DROP VIEW

  • DROP VIEW viewname


Indexes l.jpg
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;


Maintaining referential integrity l.jpg
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');


On delete l.jpg
ON DELETE

  • ON DELETE SET NULL

  • ON DELETE CASCADE


ad