Question

please answer 56789

Tasks Download a file solution1.sql and insert into the file the implementations of the following queries as SELECT statements of SQL. Your implementation must directly follow a comment with a specification of a subtask. (1) Find the pub names which located at KING ST. or OXFORD ST.. (2) Find the pub names that serve WHISKY, or VODKA, or COGNAC. Display each pub name only once. (3) Find the drinkers who have ordered drinks in January 2020. Display each drinker only once. (4) Find the drinkers who have ordered the drink WHITE WINE at LONG JOHN. Display each drinker only once. (5) Find the drink and the rating of drinks that liked. The results must be displayed in the descending order of the ratings, and for all drinks that have the same rating the results must be displayed in the ascending order of drinks. Display each pair only once. (6) Find the drink and the lowest price of each drink served in pubs. (7) Find the drinker and the total number of drinks ordered by each drinker in the first three months of 2020. (8) Find the drink and the total amount of ratings of drinks for each drink. Do not display a drink if it hasn't been rated. (9) Find the pub and the total number of drinks served in each pub that has more than three types of drinks. (10)Find the pub, the drink, and the price of pubs that drinks contain a letter E.

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

CREATE TABLE DRINKERS ( /* All drinkers */
DRINKER   VARCHAR(30)   NOT NULL,
   CONSTRAINT DRINKERS_PKEY PRIMARY KEY (DRINKER));

CREATE TABLE LOCATED(   /* Pubs have locations */
PUB           VARCHAR(30)   NOT NULL,   /* Pub name   */
STREET       VARCHAR(30)   NOT NULL,   /* Street name   */
BLDG_NO       DECIMAL(4)   NOT NULL,   /* Building number   */
   CONSTRAINT LOCATED_PKEY PRIMARY KEY(PUB) );

CREATE TABLE SERVES(   /* Pubs serve drinks */
PUB           VARCHAR(30)   NOT NULL,   /* Pub name   */
DRINK       VARCHAR(30)   NOT NULL,   /* Drink name   */
PRICE       DECIMAL(5,2)   NOT NULL,   /* Drink price   */
   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,   /* Drinker name   */
DRINK       VARCHAR(30)   NOT NULL,   /* Drink name   */
RATING       DECIMAL(1)   NOT NULL,   /* Rating of the drink   */
   CONSTRAINT LIKES_PKEY PRIMARY KEY(DRINKER, DRINK),
    CONSTRAINT LIKES_FKEY1 FOREIGN KEY(DRINK) REFERENCES ALLDRINKS(DRINK),
   CONSTRAINT LIKES_DKEY2 FOREIGN KEY(DRINKER) REFERENCES DRINKERS(DRINKER));

CREATE TABLE ORDERS(   /* Drinkers visit pubs and consumes drinks */
DRINKER       VARCHAR(30)   NOT NULL,   /* Drinker name   */
PUB           VARCHAR(30)   NOT NULL,   /* Pub name   */
ODATE       DATE       NOT NULL,   /* Order date   */
DRINK       VARCHAR(30)   NOT NULL,   /* Drink name   */
DRINK_NO   DECIMAL(2)   NOT NULL,    /* A sequence number of a drink */
   CONSTRAINT ORDERS_PKEY PRIMARY KEY(DRINKER, PUB, ODATE, DRINK, DRINK_NO),
   CONSTRAINT ORDERS_FKEY1 FOREIGN KEY(PUB, DRINK) REFERENCES SERVES(PUB, DRINK),
   CONSTRAINT ORDERS_FKEY2 FOREIGN KEY(DRINKER) REFERENCES DRINKERS(DRINKER)   );
COMMIT;
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);

NSERT INTO ORDERS VALUES('JANUSZ', 'LONG JOHN', STR_TO_DATE('8-JAN-2020', '%d-%M-%Y'), 'BEER', 1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LONG JOHN', STR_TO_DATE('8-JAN-2020', '%d-%M-%Y'), 'BEER', 2);
INSERT INTO ORDERS VALUES('JANUSZ', 'LONG JOHN', STR_TO_DATE('9-JAN-2020', '%d-%M-%Y'), 'RED WINE', 1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LONG JOHN', STR_TO_DATE('10-JAN-2020', '%d-%M-%Y'), 'BEER', 1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LONG JOHN', STR_TO_DATE('10-JAN-2020', '%d-%M-%Y'), 'BEER', 2);
INSERT INTO ORDERS VALUES('JANUSZ', 'LONG JOHN', STR_TO_DATE('10-JAN-2020', '%d-%M-%Y'), 'BEER', 3);
INSERT INTO ORDERS VALUES('JANUSZ', 'LONG JOHN', STR_TO_DATE('11-JAN-2020', '%d-%M-%Y'), 'WHITE WINE', 1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LONG JOHN', STR_TO_DATE('12-JAN-2020', '%d-%M-%Y'), 'BEER', 1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LONG JOHN', STR_TO_DATE('13-JAN-2020', '%d-%M-%Y'), 'BEER', 1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LONG JOHN', STR_TO_DATE('14-JAN-2020', '%d-%M-%Y'), 'RED WINE', 1);
INSERT INTO ORDERS VALUES('JANUSZ', 'LONG JOHN', STR_TO_DATE('14-JAN-2020', '%d-%M-%Y'), 'RED WINE', 2);
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 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);

Answer 1

(5) SELECT statement with ORDER BY ... DESC to order rating in descending order.
SELECT DRINK, RATING
FROM LIKES
ORDER BY RATING DESC;

(6) SELECT statement with GROUP BY clause to min function to find minimum price of drink.
SELECT DRINK, MIN(PRICE)
FROM SERVES
GROUP BY DRINK;

(7) SELECT statement with WHERE clause for condition and GROUP BY to count total drink of drinker.
SELECT DRINKER, COUNT(DRINK) AS TOTALDRINK
FROM ORDERS
WHERE ODATE > "1-JAN-2020"
AND ODATE < "31-MAR-2020"
GROUP BY DRINKER;

(8) SELECT statement with GROUP BY clause for using sum function for rating.
SELECT DRINK, SUM(RATING) AS TOTALRATING
FROM LIKES
GROUP BY DRINK;

(9) Subquery display pub with total drinks, and main query display the same but with the condition of total drinks more than 3.
SELECT t1.PUB, t1.TOTALDRINKS FROM
(SELECT PUB, COUNT(DRINK) AS TOTALDRINKS FROM SERVES
GROUP BY PUB) t1
WHERE t1.TOTALDRINKS > 3;