SQL Tutorial (Answer Q's & post your reply)


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: SQL Tutorial (Answer Q's & post your reply)

  1. #1
    bigbastard4 Guest

    SQL Tutorial (Answer Q's & post your reply)


    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"

  2. #2
    Steve Jackson Guest

    Re: SQL Tutorial (Answer Q's & post your reply)

    Did you just paste your homework assignment into this post?

    Steve

    On 16 May 2001 01:58:03 -0700, "bigbastard4" <bigbastard4@yahoo.com>
    wrote:

    >
    >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"



    Steve Jackson, Enterprise Section Leader
    stephenjackson@compuserve.com

  3. #3
    Paul Mc Guest

    Re: SQL Tutorial (Answer Q's & post your reply)


    Do your own goddamn assignment - why should anyone here want to help you cheat?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center