Question

CREATE TABLE person

(

pid INTEGER NOT NULL

,pname VARCHAR(30) NOT NULL

,PRIMARY KEY (pid)

);

CREATE TABLE organization

(

oid INTEGER NOT NULL

,oname VARCHAR(30) NOT NULL

,PRIMARY KEY (oid)

);

CREATE TABLE venue

(

vid INTEGER NOT NULL

,area CHAR(1) NOT NULL

,capacity INTEGER NOT NULL

,PRIMARY KEY (vid)

);

CREATE TABLE calendar

(

vid INTEGER NOT NULL

,date DATE NOT NULL

,price NUMERIC(6,2) NOT NULL

,PRIMARY KEY(vid,date)

,FOREIGN KEY(vid) REFERENCES venue(vid)

);

CREATE TABLE event

(

eid INTEGER NOT NULL

,type VARCHAR(15) NOT NULL

,PRIMARY KEY (eid)

);

CREATE TABLE schedule

(

vid INTEGER NOT NULL

,date DATE NOT NULL

,eid INTEGER NOT NULL

,note VARCHAR(30)

,PRIMARY KEY(vid,date)

,FOREIGN KEY(eid) REFERENCES event(eid)

,FOREIGN KEY(vid,date) REFERENCES calendar(vid,date)

);

CREATE TABLE participate

(

pid INTEGER NOT NULL

,eid INTEGER NOT NULL

,PRIMARY KEY (pid,eid)

,FOREIGN KEY(pid) REFERENCES person(pid)

,FOREIGN KEY(eid) REFERENCES event(eid)

);

CREATE TABLE host

(

oid INTEGER NOT NULL

,eid INTEGER NOT NULL

,PRIMARY KEY (oid,eid)

,FOREIGN KEY(oid) REFERENCES organization(oid)

,FOREIGN KEY(eid) REFERENCES event(eid)

);

Question: List the venue ids, areas, capacities of all the venues that the organization with oid 6 has ever used to host events. - using a correlated subquery. Order the output by vid and capacity. Using psql

Answer 1

using a correlated subquery.

In a correlated subquery, inner query is executed, for each value of outer query. Then the inner query values are used to qualify or disqualify the outer query value. This method is computationally expensive. Alternative is to use views or joins.

SQL QUERY

SELECT v.vid,v.area, v.capacity
FROM schedule s
JOIN venue v
ON s.vid=v.vid
WHERE EXISTS (
select h.eid
from host h
where h.oid=6 and h.eid=s.eid)
ORDER BY v.vid, v.capacity;