correlated subqueries and miscellaneous topics
Download
Skip this Video
Download Presentation
Correlated SubQueries and Miscellaneous Topics

Loading in 2 Seconds...

play fullscreen
1 / 21

Correlated SubQueries and Miscellaneous Topics - PowerPoint PPT Presentation


  • 440 Views
  • Uploaded 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
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
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
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
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
EXISTS, NOT EXISTS
  • Test whether a subquery returns any rows.
  • It returns True/False
find faculty who advise at least one student
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
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
SELECT List Containing CorrelatedSub Query

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

WHERE sid=student.sid) AS courses

FROM student;

slide10
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

Note: Calculated field cannot used in the WHERE clause.

multiple levels of nesting
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
Nesting Aggregates
  • SELECT MAX(AVG(gpa))
  • FROM student
  • GROUP BY major;
views
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
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
Updating Through Views
  • A view is updatable if
    • the update command does not violate database constraints;
    • The view is not read only.
materialized views
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
DROP VIEW
  • DROP VIEW viewname
indexes
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
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
ON DELETE
  • ON DELETE SET NULL
  • ON DELETE CASCADE
ad