CREATE TABLE ALLDRINKS( /* All legal drinks */
DRINK VARCHAR(30) NOT NULL,
CONSTRAINT DRINKNAME_PKEY PRIMARY KEY(DRINK) );

CREATE TABLE LOCATED( /* Pubs have locations */
PUB VARCHAR(30) NOT NULL,
STREET VARCHAR(30) NOT NULL,
BLDG# NUMBER(4) NOT NULL,
CONSTRAINT LOCATED_PKEY PRIMARY KEY(PUB) );

CREATE TABLE SERVES( /* Pubs serve drinks */
PUB VARCHAR(30) NOT NULL,
DRINK VARCHAR(30) NOT NULL,
PRICE NUMBER(5,2) NOT NULL,
CONSTRAINT SERVES_PKEY PRIMARY KEY(PUB, DRINK),
CONSTRAINT SERVES_FKEY1 FOREIGN KEY(PUB)
REFERENCES LOCATED(PUB),
CONSTRAINT SERVES_FKEY2 FOREIGN KEY(DRINK)
REFERENCES ALLDRINKS(DRINK) );

CREATE TABLE LIKES( /* Drinkers like drinks */
DRINKER VARCHAR(30) NOT NULL,
DRINK VARCHAR(30) NOT NULL,
RATING NUMBER(1) NOT NULL,
CONSTRAINT LIKES_PKEY PRIMARY KEY(DRINKER, DRINK),
CONSTRAINT LIKES_FKEY1 FOREIGN KEY(DRINK)
REFERENCES ALLDRINKS(DRINK) );

CREATE TABLE ORDERS( /* Drinkers visit pubs and consumes
drinks */
DRINKER VARCHAR(30) NOT NULL,
PUB VARCHAR(30) NOT NULL,
ODATE DATE NOT NULL,
DRINK VARCHAR(30) NOT NULL,
DRINK# NUMBER(2) NOT NULL, /* A sequence number of a drink
*/
CONSTRAINT ORDERS_PKEY PRIMARY KEY(DRINKER, PUB, ODATE, DRINK, DRINK#),
CONSTRAINT ORDERS_FKEY1 FOREIGN KEY(PUB, DRINK)
REFERENCES SERVES(PUB, DRINK),
CONSTRAINT ORDERS_FKEY2 FOREIGN KEY(PUB)
REFERENCES LOCATED(PUB) );


INSERT INTO ALLDRINKS VALUES('BEER');
INSERT INTO ALLDRINKS VALUES('RED WINE');
INSERT INTO ALLDRINKS VALUES('WHITE WINE');
INSERT INTO ALLDRINKS VALUES('CHAMPAGNE');
INSERT INTO ALLDRINKS VALUES('VODKA');
INSERT INTO ALLDRINKS VALUES('PORT');
INSERT INTO ALLDRINKS VALUES('COGNAC');
INSERT INTO ALLDRINKS VALUES('RUM');
INSERT INTO ALLDRINKS VALUES('WHISKY');
INSERT INTO ALLDRINKS VALUES('YABTSOK');

INSERT INTO LOCATED VALUES('LAZY LOBSTER', 'STATION ST.', 45);
INSERT INTO LOCATED VALUES('GREASY FORK', 'VICTORIA AVE.', 345);
INSERT INTO LOCATED VALUES('CAPTAIN MOORE', 'KING ST.', 45);
INSERT INTO LOCATED VALUES('LONG JOHN', 'STATION ST.', 89);
INSERT INTO LOCATED VALUES('LITTLE PIRATE', 'OXFORD ST.', 345);

INSERT INTO SERVES VALUES('LAZY LOBSTER', 'BEER', 5.00);
INSERT INTO SERVES VALUES('LAZY LOBSTER', 'RED WINE', 7.00);
INSERT INTO SERVES VALUES('LAZY LOBSTER', 'PORT', 8.00);
INSERT INTO SERVES VALUES('LAZY LOBSTER', 'COGNAC', 10.20);
INSERT INTO SERVES VALUES('LAZY LOBSTER', 'WHISKY', 6.90);
INSERT INTO SERVES VALUES('GREASY FORK', 'BEER', 5.20);
INSERT INTO SERVES VALUES('GREASY FORK', 'RED WINE', 7.40);
INSERT INTO SERVES VALUES('LONG JOHN', 'BEER', 5.90);
INSERT INTO SERVES VALUES('LONG JOHN', 'RED WINE', 8.00);
INSERT INTO SERVES VALUES('LONG JOHN', 'WHITE WINE', 9.00);
INSERT INTO SERVES VALUES('LONG JOHN', 'VODKA', 6.00);
INSERT INTO SERVES VALUES('LONG JOHN', 'PORT', 9.35);
INSERT INTO SERVES VALUES('LONG JOHN', 'COGNAC', 12.90);
INSERT INTO SERVES VALUES('LONG JOHN', 'WHISKY', 7.15);
INSERT INTO SERVES VALUES('LITTLE PIRATE', 'BEER', 4.50);
INSERT INTO SERVES VALUES('LITTLE PIRATE', 'RED WINE', 7.90);
INSERT INTO SERVES VALUES('LITTLE PIRATE', 'WHITE WINE', 5.40);
INSERT INTO SERVES VALUES('LITTLE PIRATE', 'CHAMPAGNE', 10.90);
INSERT INTO SERVES VALUES('LITTLE PIRATE', 'VODKA', 5.25);
INSERT INTO SERVES VALUES('LITTLE PIRATE', 'RUM', 12.00);
INSERT INTO SERVES VALUES('LITTLE PIRATE', 'WHISKY', 10.80);
INSERT INTO SERVES VALUES('CAPTAIN MOORE', 'BEER', 4.50);

INSERT INTO LIKES VALUES('TOM', 'BEER', 6);
INSERT INTO LIKES VALUES('JANUSZ', 'VODKA', 6);
INSERT INTO LIKES VALUES('JANUSZ', 'RUM', 5);
INSERT INTO LIKES VALUES('JANUSZ', 'BEER', 6);
INSERT INTO LIKES VALUES('JANUSZ', 'CHAMPAGNE', 6);
INSERT INTO LIKES VALUES('JANUSZ', 'RED WINE', 6);
INSERT INTO LIKES VALUES('JANUSZ', 'WHITE WINE', 5);
INSERT INTO LIKES VALUES('JANUSZ', 'PORT', 5);
INSERT INTO LIKES VALUES('PETER', 'CHAMPAGNE', 4);
INSERT INTO LIKES VALUES('PETER', 'COGNAC', 3);
INSERT INTO LIKES VALUES('PETER', 'RUM', 3);
INSERT INTO LIKES VALUES('PETER', 'WHISKY', 6);
INSERT INTO LIKES VALUES('MARY', 'CHAMPAGNE', 5);
INSERT INTO LIKES VALUES('MARY', 'VODKA', 1);
INSERT INTO LIKES VALUES('MARY', 'COGNAC', 4);
INSERT INTO LIKES VALUES('JOHN', 'CHAMPAGNE', 4);
INSERT INTO LIKES VALUES('JOHN', 'VODKA', 2);
INSERT INTO LIKES VALUES('JOHN', 'RUM', 6);
INSERT INTO LIKES VALUES('JOHN', 'WHISKY', 1);
INSERT INTO LIKES VALUES('JOHN', 'BEER', 6);
INSERT INTO LIKES VALUES('JAMES', 'CHAMPAGNE', 6);
INSERT INTO LIKES VALUES('JAMES', 'COGNAC', 5);
INSERT INTO LIKES VALUES('JAMES', 'RUM', 4);
INSERT INTO LIKES VALUES('SERGIEY', 'VODKA', 6);
INSERT INTO LIKES VALUES('SERGIEY', 'RUM', 6);
INSERT INTO LIKES VALUES('SERGIEY', 'CHAMPAGNE', 3);
INSERT INTO LIKES VALUES('CLAUDE', 'CHAMPAGNE', 6);
INSERT INTO LIKES VALUES('CLAUDE', 'WHITE WINE', 5);
INSERT INTO LIKES VALUES('CLAUDE', 'COGNAC', 4);
INSERT INTO LIKES VALUES('CLAUDE', 'WHISKY', 3);
INSERT INTO LIKES VALUES('CLAUDE', 'RED WINE', 6);

INSERT INTO ORDERS VALUES('JANUSZ', 'LONG JOHN', '8-JAN-00', 'BEER', 1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LONG JOHN', '8-JAN-00', 'BEER', 2);
INSERT INTO ORDERS VALUES('JANUSZ', 'LONG JOHN', '9-JAN-00', 'RED WINE',
1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LONG JOHN', '10-JAN-00', 'BEER', 1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LONG JOHN', '10-JAN-00', 'BEER', 2);
INSERT INTO ORDERS VALUES('JANUSZ', 'LONG JOHN', '10-JAN-00', 'BEER', 3);
INSERT INTO ORDERS VALUES('JANUSZ', 'LONG JOHN', '11-JAN-00', 'WHITE WINE',
1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LONG JOHN', '12-JAN-00', 'BEER', 1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LONG JOHN', '13-JAN-00', 'BEER', 1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LONG JOHN', '14-JAN-00', 'RED WINE',
1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LONG JOHN', '14-JAN-00', 'RED WINE',
2);
INSERT INTO ORDERS VALUES('JANUSZ', 'LONG JOHN', '14-JAN-00', 'WHITE WINE',
3);
INSERT INTO ORDERS VALUES('JANUSZ', 'LAZY LOBSTER', '15-JAN-00', 'PORT',
1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LAZY LOBSTER', '16-JAN-00', 'WHISKY',
1);
INSERT INTO ORDERS VALUES('JANUSZ', 'GREASY FORK', '17-JAN-00', 'BEER', 1);
INSERT INTO ORDERS VALUES('JANUSZ', 'CAPTAIN MOORE', '18-JAN-00', 'BEER',
1);
INSERT INTO ORDERS VALUES('JANUSZ', 'CAPTAIN MOORE', '18-JAN-00', 'BEER',
2);
INSERT INTO ORDERS VALUES('JANUSZ', 'CAPTAIN MOORE', '18-JAN-00', 'BEER',
3);
INSERT INTO ORDERS VALUES('JANUSZ', 'CAPTAIN MOORE', '18-JAN-00', 'BEER',
4);
INSERT INTO ORDERS VALUES('JANUSZ', 'LAZY LOBSTER', '19-JAN-00', 'PORT',
1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LAZY LOBSTER', '19-JAN-00', 'PORT',
2);
INSERT INTO ORDERS VALUES('JANUSZ', 'LAZY LOBSTER', '19-JAN-00', 'PORT',
3);
INSERT INTO ORDERS VALUES('JANUSZ', 'CAPTAIN MOORE', '01-FEB-00', 'BEER',
1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LAZY LOBSTER', '02-FEB-00', 'PORT',
1);
INSERT INTO ORDERS VALUES('JANUSZ', 'GREASY FORK', '03-FEB-00', 'BEER', 1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LAZY LOBSTER', '04-FEB-00', 'PORT',
1);
INSERT INTO ORDERS VALUES('JANUSZ', 'CAPTAIN MOORE', '05-FEB-00', 'BEER',
1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LAZY LOBSTER', '06-FEB-00', 'PORT',
1);
INSERT INTO ORDERS VALUES('JANUSZ', 'GREASY FORK', '15-FEB-00', 'BEER', 1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LITTLE PIRATE', '16-FEB-00', 'CHAMPAGNE',
1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LITTLE PIRATE', '17-FEB-00', 'CHAMPAGNE',
1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LAZY LOBSTER', '19-FEB-00', 'PORT',
1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LAZY LOBSTER', '20-FEB-00', 'PORT',
1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LAZY LOBSTER', '22-FEB-00', 'PORT',
1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LAZY LOBSTER', '01-MAR-00', 'PORT',
1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LAZY LOBSTER', '03-MAR-00', 'PORT',
1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LAZY LOBSTER', '04-MAR-00', 'PORT',
1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LAZY LOBSTER', '05-MAR-00', 'PORT',
1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LAZY LOBSTER', '01-APR-00', 'PORT',
1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LAZY LOBSTER', '02-MAY-00', 'PORT',
1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LAZY LOBSTER', '03-MAY-00', 'PORT',
1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LAZY LOBSTER', '04-MAY-00', 'PORT',
1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LAZY LOBSTER', '01-JUN-00', 'PORT',
1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LAZY LOBSTER', '02-JUN-00', 'PORT',
1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LONG JOHN', '8-JUN-00', 'BEER', 1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LONG JOHN', '9-JUN-00', 'BEER', 1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LONG JOHN', '10-JUN-00', 'BEER', 1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LONG JOHN', '11-JUN-00', 'BEER', 1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LONG JOHN', '12-JUL-00', 'BEER', 1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LONG JOHN', '13-JUL-00', 'BEER', 1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LONG JOHN', '14-AUG-00', 'BEER', 1);

INSERT INTO ORDERS VALUES('PETER', 'LONG JOHN', '10-JAN-00', 'WHITE WINE',
1);
INSERT INTO ORDERS VALUES('PETER', 'LONG JOHN', '10-JAN-00', 'WHITE WINE',
2);
INSERT INTO ORDERS VALUES('PETER', 'LONG JOHN', '10-JAN-00', 'WHITE WINE',
3);
INSERT INTO ORDERS VALUES('PETER', 'LONG JOHN', '10-JAN-00', 'WHITE WINE',
4);
INSERT INTO ORDERS VALUES('PETER', 'LONG JOHN', '10-JAN-00', 'WHITE WINE',
5);
INSERT INTO ORDERS VALUES('PETER', 'LONG JOHN', '11-JAN-00', 'RED WINE',
1);
INSERT INTO ORDERS VALUES('PETER', 'LAZY LOBSTER', '09-FEB-00', 'PORT', 1);
INSERT INTO ORDERS VALUES('PETER', 'GREASY FORK', '03-MAR-00', 'BEER', 1);
INSERT INTO ORDERS VALUES('PETER', 'LONG JOHN', '19-APR-00', 'BEER', 1);
INSERT INTO ORDERS VALUES('PETER', 'LONG JOHN', '19-APR-00', 'BEER', 2);
INSERT INTO ORDERS VALUES('PETER', 'LONG JOHN', '19-APR-00', 'BEER', 3);
INSERT INTO ORDERS VALUES('PETER', 'LONG JOHN', '19-APR-00', 'BEER', 4);
INSERT INTO ORDERS VALUES('PETER', 'LONG JOHN', '19-APR-00', 'BEER', 5);
INSERT INTO ORDERS VALUES('PETER', 'LONG JOHN', '19-APR-00', 'BEER', 6);
INSERT INTO ORDERS VALUES('PETER', 'LONG JOHN', '19-APR-00', 'BEER', 7);

INSERT INTO ORDERS VALUES('MARY', 'LONG JOHN', '10-JAN-00', 'RED WINE', 1);
INSERT INTO ORDERS VALUES('MARY', 'LAZY LOBSTER', '04-FEB-00', 'RED WINE',
1);
INSERT INTO ORDERS VALUES('MARY', 'CAPTAIN MOORE', '03-MAR-00', 'BEER', 1);
INSERT INTO ORDERS VALUES('MARY', 'LONG JOHN', '05-APR-00', 'RED WINE', 1);
INSERT INTO ORDERS VALUES('MARY', 'GREASY FORK', '24-APR-00', 'BEER', 1);

INSERT INTO ORDERS VALUES('JOHN', 'LONG JOHN', '13-JAN-00', 'WHITE WINE',
1);
INSERT INTO ORDERS VALUES('JOHN', 'LAZY LOBSTER', '04-FEB-00', 'RED WINE',
1);
INSERT INTO ORDERS VALUES('JOHN', 'LAZY LOBSTER', '04-FEB-00', 'RED WINE',
2);
INSERT INTO ORDERS VALUES('JOHN', 'LAZY LOBSTER', '04-FEB-00', 'RED WINE',
3);
INSERT INTO ORDERS VALUES('JOHN', 'LONG JOHN', '12-FEB-00', 'WHITE WINE',
1);
INSERT INTO ORDERS VALUES('JOHN', 'LONG JOHN', '12-FEB-00', 'RED WINE', 2);
INSERT INTO ORDERS VALUES('JOHN', 'LONG JOHN', '12-FEB-00', 'WHITE WINE',
3);
INSERT INTO ORDERS VALUES('JOHN', 'LONG JOHN', '12-FEB-00', 'RED WINE', 4);
INSERT INTO ORDERS VALUES('JOHN', 'LONG JOHN', '12-FEB-00', 'WHITE WINE',
5);
INSERT INTO ORDERS VALUES('JOHN', 'LONG JOHN', '12-FEB-00', 'RED WINE', 6);
INSERT INTO ORDERS VALUES('JOHN', 'LONG JOHN', '03-MAR-00', 'WHITE WINE',
1);
INSERT INTO ORDERS VALUES('JOHN', 'LONG JOHN', '04-APR-00', 'RED WINE', 1);
INSERT INTO ORDERS VALUES('JOHN', 'CAPTAIN MOORE', '15-APR-00', 'BEER', 1);

INSERT INTO ORDERS VALUES('JAMES', 'LONG JOHN', '10-JAN-00', 'BEER', 1);
INSERT INTO ORDERS VALUES('JAMES', 'LAZY LOBSTER', '04-FEB-00', 'COGNAC',
1);
INSERT INTO ORDERS VALUES('JAMES', 'LAZY LOBSTER', '04-FEB-00', 'COGNAC',
2);
INSERT INTO ORDERS VALUES('JAMES', 'LAZY LOBSTER', '04-FEB-00', 'COGNAC',
3);
INSERT INTO ORDERS VALUES('JAMES', 'LONG JOHN', '03-MAR-00', 'BEER', 1);

INSERT INTO ORDERS VALUES('SERGIEY', 'LONG JOHN', '10-JAN-00', 'BEER', 1);
INSERT INTO ORDERS VALUES('SERGIEY', 'LONG JOHN', '10-JAN-00', 'BEER', 2);
INSERT INTO ORDERS VALUES('SERGIEY', 'LONG JOHN', '10-JAN-00', 'BEER', 3);
INSERT INTO ORDERS VALUES('SERGIEY', 'LAZY LOBSTER', '12-JAN-00', 'WHISKY',
1);
INSERT INTO ORDERS VALUES('SERGIEY', 'LAZY LOBSTER', '12-JAN-00', 'WHISKY',
2);
INSERT INTO ORDERS VALUES('SERGIEY', 'GREASY FORK', '14-JAN-00', 'BEER',
1);
INSERT INTO ORDERS VALUES('SERGIEY', 'LONG JOHN', '04-FEB-00', 'BEER', 1);
INSERT INTO ORDERS VALUES('SERGIEY', 'LONG JOHN', '06-FEB-00', 'BEER', 1);
INSERT INTO ORDERS VALUES('SERGIEY', 'LONG JOHN', '23-FEB-00', 'BEER', 1);
INSERT INTO ORDERS VALUES('SERGIEY', 'LAZY LOBSTER', '03-MAR-00', 'WHISKY',
1);
INSERT INTO ORDERS VALUES('SERGIEY', 'LAZY LOBSTER', '09-MAR-00', 'WHISKY',
1);
INSERT INTO ORDERS VALUES('SERGIEY', 'LITTLE PIRATE', '04-APR-00', 'VODKA',
1);
INSERT INTO ORDERS VALUES('SERGIEY', 'LONG JOHN', '12-JAN-01', 'BEER', 1);
INSERT INTO ORDERS VALUES('SERGIEY', 'LONG JOHN', '12-JAN-01', 'BEER', 2);
INSERT INTO ORDERS VALUES('SERGIEY', 'LONG JOHN', '12-JAN-01', 'BEER', 3);


INSERT INTO ORDERS VALUES('CLAUDE', 'LONG JOHN', '10-JAN-00', 'WHITE WINE',
1);
INSERT INTO ORDERS VALUES('CLAUDE', 'LONG JOHN', '12-JAN-00', 'WHITE WINE',
1);
INSERT INTO ORDERS VALUES('CLAUDE', 'LONG JOHN', '12-JAN-00', 'WHITE WINE',
2);
INSERT INTO ORDERS VALUES('CLAUDE', 'LONG JOHN', '12-JAN-00', 'WHITE WINE',
3);
INSERT INTO ORDERS VALUES('CLAUDE', 'LONG JOHN', '12-JAN-00', 'WHITE WINE',
4);
INSERT INTO ORDERS VALUES('CLAUDE', 'LONG JOHN', '15-JAN-00', 'WHITE WINE',
1);
INSERT INTO ORDERS VALUES('CLAUDE', 'LONG JOHN', '15-JAN-00', 'RED WINE',
2);
INSERT INTO ORDERS VALUES('CLAUDE', 'GREASY FORK', '19-JAN-00', 'BEER', 1);
INSERT INTO ORDERS VALUES('CLAUDE', 'LAZY LOBSTER', '04-APR-00', 'RED WINE',
1);
INSERT INTO ORDERS VALUES('CLAUDE', 'LAZY LOBSTER', '05-APR-00', 'RED WINE',
1);
INSERT INTO ORDERS VALUES('CLAUDE', 'LAZY LOBSTER', '19-APR-00', 'RED WINE',
1);
INSERT INTO ORDERS VALUES('CLAUDE', 'GREASY FORK', '20-APR-00', 'BEER', 1);
INSERT INTO ORDERS VALUES('CLAUDE', 'LONG JOHN', '12-JAN-01', 'WHITE WINE',
1);
INSERT INTO ORDERS VALUES('CLAUDE', 'LONG JOHN', '13-JAN-01', 'WHITE WINE',
1);
INSERT INTO ORDERS VALUES('CLAUDE', 'LONG JOHN', '16-JAN-01', 'WHITE WINE',
1);
INSERT INTO ORDERS VALUES('CLAUDE', 'LONG JOHN', '17-JAN-01', 'WHITE WINE',
1);
INSERT INTO ORDERS VALUES('CLAUDE', 'LONG JOHN', '19-JAN-01', 'RED WINE',
1);

COMMIT;

1 Queries with the complex Boolean expressions, row, and group functions


Expresses the following queries as SELECT statements. Insert SELECT statements
into a
script a3select1.sql and execute it.

? "Find the total number of drinks consumed by JANUSZ in February and
March
2000 in LONG JOHN pub"
? "Find how many drinkers visited LAZY LOBSTER pub in the last 3 years
from the
current date (SYSDATE)"

2 Set algebra queries
Expresses the following queries as set algebra expressions with SELECT statements.
Insert
the set algebra expressions into a script a3select2.sql and execute it.

? "Find the names of all pubs located along STATION ST. and serving
BEER, and
visited at least one time by JANUSZ"
? "Find the names of pubs where JANUSZ ordered at least one drink not
included
on his list of preferences."

3 Queries with GROUP BY clause
Expresses the following queries as SELECT statements with GROUP BY clauses.
Insert
SELECT statements into a script a3select3.sql and execute it.

? "For each pub, find its name and the total amount of money spent
by all drinkers
in a pub. You may ignore the pubs with no money earned so far"
? "For each drinker find his/her name and the total number of drinks
consumed
2000 and the total number of drinks consumed in 2001"

4 Join queries
Expresses the following queries as SELECT statements with join conditions.
Insert
SELECT statements into a script a3select4.sql and execute it.

? "Find the names and prices of all drinks consumed by JANUSZ in any
pub located
along STATION street"

? "Find the names and order dates of three identical drinks ordered
in a row (one
by one) on the same day by JANUSZ"

5 Outer join queries
Expresses the following queries as SELECT statements with outer join conditions.
Insert
SELECT statements into a script a3select5.sql and execute it.

? "For each drink, find its name together with the total number of
pubs where a
drink is served. Do not ignore the drinks not served in any pub !"
? "For each drinker find his/her name and the total number of drinks
consumed. Do
not ignore drinkers who provided their preferences and consumed no drinks
!"

6 Nested queries
Expresses the following queries as nested SELECT statements. Insert SELECT
statements
into a script a3select6.sql and execute it.

? "Find the names and prices of all drinks consumed by JANUSZ. (Use
multiple
attribute join condition)"
? "Find the names all drinkers who provided their preferences and who
never
ordered a drink in GREASY FORK pub"

7 Nested queries with existential quantifiers
Expresses the following queries as SELECT statements with existential/negated
existential
quantifiers. Insert SELECT statements into a script a3select7.sql and execute
it.

? "Find the names of all drinkers who provided their preferences and
who visited at
least one GREASY FORK pub"
? "Find the names and prices of all drinks offered by LONG JOHN pub
and never
ordered there"

8 Queries with ANY and ALL clauses
Expresses the following queries as SELECT statements with ANY/ALL clauses.
Insert
SELECT statements into a script a3select8.sql and execute it.

? "Find the names and locations of all pubs that serve BEER. Use ANY
clause"
? "Find a name and price of the most expensive drink together with
a name of pub
where the drink is served. Use ALL clause"

9 Queries with GROUP BY and HAVING clauses
Expresses the following queries as SELECT statements with GROUP BY and HAVING

clauses. Insert SELECT statements into a script a3select9.sql and execute
it.

? "Find the names of all drinkers who consumed the largest number of
drinks"
? "Find the names of all pubs visited by more than 6 distinct drinkers
in 2000"

10 Queries with universal quantifiers
Expresses the following query as SELECT statement with two negated existential

quantifiers. Insert SELECT statement into a script a3select10.sql and execute
it.

? "Find the names of all drinkers who visited all pubs"

11 Queries with subqueries in FROM clause quantifier
Expresses the following query as SELECT statement with at least one subquery
in FROM
clause. Insert SELECT statement into a script a3select11.sql and execute
it.

? "Find the names of all pubs visited by more than 6 distinct drinkers
in 2000"

12 Recursive queries
Expresses the following queries as SELECT statement with CONNECT BY clause.
Insert
SELECT statement into a script a3select12.sql and execute it.

? "Find the first drink and all consecutive drinks consumed by JANUSZ
while
visiting LONG JOHN pub on 14 JANUARY, 2000"