Download a file solution1.sql and insert into the file the implementations of the following modifications of the structures, consistency constraints, and/or the contents of the sample database. Note, that you are not allowed to modify and/or to drop any consistency constraints. You may need more than one SQL statement to implement a single subtask listed below.Your implementation must directly follow a comment with a specification of a subtask.

i am not sure what codes are needed for the following questions. also what's the function of dbdrop.sql and dbdrop.sql

(6) Add to the database information about a customer MIKE orders two drinks in a pub. All the other information about the two new orders is up to you. (

7) A manager of pub LITTLE PIRATE decides to give 10% discount on all drinks. Modify the price of all drinks served in this pub.

(8) A customer JAMES decides to change the rating of a drink RUM to 6.

(9) A pub SWEET DREAMS closed down. Remove from the database information about the pub SWEET DREAMS.

(10) Remove from the database information about a drinker JAMES

dbcreate.sql

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;

dbload.sql

abcount.sql SELECT (SELECT COUNT(*) FROM ALLDRINKS ) "ALLDRINKS", (SELECT COUNT(*) FROM DRINKERS ) "DRINKERS", (SELECT COUNT(*) FROM LOCATED ) "LOCATED", (SELECT COUNT(*) FROM SERVES ) "SERVES", (SELECT COUNT(*) FROM LIKES ) "LIKES", (SELECT COUNT(*) FROM ORDERS ) "ORDERS" FROM DUAL;