1.8.6.2 Constraint NOT NULL and DEFAULT valuesAnthony GalanoCS157A – Computer Assignment 1Section 14-13-0412 is wrong ------------------------------------------------------------------------------------------------------------COMPLETE SQL CODE BEGINS HEREThis code was run as the mySQL source file. See runtime output below.-- BUILD ALL THE TABLES ----------------------------------------CREATE TABLE HOTELS(HID int(1) PRIMARY KEY NOT NULL,NAME varchar(12),CITY varchar(12),STATE varchar(2),LUXURY int(1),AIRPORT_DISTANCE int(2),DOWNTOWN_DISTANCE int(2));CREATE TABLE ROOMS(RID int(1) PRIMARY KEY NOT NULL,NUM int(3),HID int(1) NOT NULL,BEDS int(1),PRICE decimal(4,2),TV enum('T','F') NOT NULL,FRIDGE enum('F','T') NOT NULL,FOREIGN KEY (HID) REFERENCES HOTELS(HID) ON DELETE CASCADE);CREATE TABLE RESERVATIONS(RSVID int(2) PRIMARY KEY,RID int(1),GUEST varchar(15),DATEIN varchar(9),DAYS int(1),NUM int(2),FOREIGN KEY (RID) REFERENCES ROOMS(RID) ON DELETE CASCADE);-- INSERT HOTEL DATA ----------------------------------------INSERT INTO HOTELS(HID,NAME,CITY,STATE,LUXURY,AIRPORT_DISTANCE,DOWNTOWN_DISTANCE)VALUES(1,'Days In','College Park','MD',1,20,10);INSERT INTO HOTELS(HID,NAME,CITY,STATE,LUXURY,AIRPORT_DISTANCE,DOWNTOWN_DISTANCE)VALUES(2,'Hilton','Washington','DC',3,7,1);INSERT INTO HOTELS(HID,NAME,CITY,STATE,LUXURY,AIRPORT_DISTANCE,DOWNTOWN_DISTANCE)VALUES(3,'Ritz Carlton','Washington','DC',4,6,1);INSERT INTO HOTELS(HID,NAME,CITY,STATE,LUXURY,AIRPORT_DISTANCE,DOWNTOWN_DISTANCE)VALUES(4,'Holiday In','Washington','DC',2,5,1);INSERT INTO HOTELS(HID,NAME,CITY,STATE,LUXURY,AIRPORT_DISTANCE,DOWNTOWN_DISTANCE)VALUES(5,'Holiday In','Washington','DC',1,7,1);INSERT INTO HOTELS(HID,NAME,CITY,STATE,LUXURY,AIRPORT_DISTANCE,DOWNTOWN_DISTANCE)VALUES(6,'Holiday In','Arlington','VI',2,2,6);-- INSERT ROOMS DATA ----------------------------------------INSERT INTO ROOMS(RID,NUM,HID,BEDS,PRICE,TV,FRIDGE)VALUES(1,25,1,1,80.00,'T','F');INSERT INTO ROOMS(RID,NUM,HID,BEDS,PRICE,TV,FRIDGE)VALUES(2,20,1,2,120.00,'T','F');INSERT INTO ROOMS(RID,NUM,HID,BEDS,PRICE,TV,FRIDGE)VALUES(3,100,2,2,200.00,'T','F');INSERT INTO ROOMS(RID,NUM,HID,BEDS,PRICE,TV,FRIDGE)VALUES(4,20,2,2,600.00,'T','F');INSERT INTO ROOMS(RID,NUM,HID,BEDS,PRICE,TV,FRIDGE)VALUES(5,40,3,2,999.00,'T','T');INSERT INTO ROOMS(RID,NUM,HID,BEDS,PRICE,TV,FRIDGE)VALUES(6,120,4,2,150.00,'T','T');INSERT INTO ROOMS(RID,NUM,HID,BEDS,PRICE,TV,FRIDGE)VALUES(7,120,4,1,120.00,'T','F');INSERT INTO ROOMS(RID,NUM,HID,BEDS,PRICE,TV,FRIDGE)VALUES(8,80,5,1,100.00,'T','F');INSERT INTO ROOMS(RID,NUM,HID,BEDS,PRICE,TV,FRIDGE)VALUES(9,90,6,2,140.00,'T','F');-- INSERT RESERVATIONS DATA ----------------------------------------INSERT INTO RESERVATIONS(RSVID,RID,GUEST,DATEIN,DAYS,NUM)VALUES(1,1,'A.B. Holmes','03-Feb-02',2,1);INSERT INTO RESERVATIONS(RSVID,RID,GUEST,DATEIN,DAYS,NUM)VALUES(2,3,'SIGX-conference','03-Feb-02',5,80);INSERT INTO RESERVATIONS(RSVID,RID,GUEST,DATEIN,DAYS,NUM)VALUES(3,4,'King of Z','05-Feb-02',7,1);INSERT INTO RESERVATIONS(RSVID,RID,GUEST,DATEIN,DAYS,NUM)VALUES(4,3,'Kings service','05-Feb-02',7,3);INSERT INTO RESERVATIONS(RSVID,RID,GUEST,DATEIN,DAYS,NUM)VALUES(5,5,'D.D. A.','04-Feb-02',3,1);INSERT INTO RESERVATIONS(RSVID,RID,GUEST,DATEIN,DAYS,NUM)VALUES(6,7,'D.D. B.','04-Feb-02',1,1);INSERT INTO RESERVATIONS(RSVID,RID,GUEST,DATEIN,DAYS,NUM)VALUES(7,9,'D.D. C.','03-Feb-02',1,1);INSERT INTO RESERVATIONS(RSVID,RID,GUEST,DATEIN,DAYS,NUM)VALUES(8,9,'D.D. D.','04-Feb-02',1,1);INSERT INTO RESERVATIONS(RSVID,RID,GUEST,DATEIN,DAYS,NUM)VALUES(9,9,'D.D. E.','05-Feb-02',1,1);INSERT INTO RESERVATIONS(RSVID,RID,GUEST,DATEIN,DAYS,NUM)VALUES(10,9,'D.D. F.','06-Feb-02',1,1);-- RUN VARIOUS SELECTED QUERIES -------------------------------------- Show content of the HOTELS table SELECT *FROM HOTELS;-- Show content of the ROOMS table SELECT *FROM ROOMS;-- Show content of the RESERVATIONS table SELECT *FROM RESERVATIONS;-- Show names of hotels and their room prices that cost more then 500 dolarsSELECT NAME, PRICEFROM HOTELS,ROOMSWHERE PRICE >= 500.00 AND HOTELS.HID = ROOMS.HID;-- Show room ids and the corresponding room prices for rooms that have fridge and cost less then 130 dollars.SELECT DISTINCT RID, PRICEFROM ROOMSWHERE ROOMS.FRIDGE = 'T' AND ROOMS.PRICE < 130.00;-- Show the average room priceSELECT AVG(PRICE) AS avgPriceFROM ROOMS;-- Show guest or group of guests paying the most in total and the corresponding amount of money paid for the roomsSELECT max(DAYS*RESERVATIONS.NUM*PRICE) AS totalCost, RESERVATIONS.GUESTFROM ROOMS,RESERVATIONSWHERE ROOMS.RID=RESERVATIONS.RIDGROUP BY GUEST ORDER BY totalCost DESC limit 1;-- Show the hotel with most expensive room SELECT NAME,MAX(PRICE) AS maxPriceFROM HOTELS,ROOMSWHERE HOTELS.HID = ROOMS.HIDGROUP BY NAMEORDER BY maxPrice DESC LIMIT 1;-- Show the number of hotels in HOTELS tableSELECT DISTINCT COUNT(NAME) AS hotelCountFROM HOTELS;-- Show hotels with rooms available on Feb 5, 2002SELECT NAME,CITYFROM HOTELS,ROOMS,RESERVATIONSWHERE RESERVATIONS.RID = ROOMS.RID AND ROOMS.RID = HOTELS.HID AND RESERVATIONS.DATEIN != '05-FEB-02';-- Show total number of rooms in Washington, DCSELECT SUM(ROOMS.NUM) AS roomCountFROM HOTELS,ROOMSWHERE HOTELS.HID = ROOMS.RID AND HOTELS.CITY='Washington' AND STATE='DC';-- Reduce the price of all rooms in College Park by 50%UPDATE ROOMS,HOTELSSET ROOMS.PRICE=ROOMS.PRICE * .5WHERE ROOMS.HID = HOTELS.HID and HOTELS.CITY='College Park';-- Show the data from ROOMS table for all roomsSELECT *FROM ROOMS;-- Attemp and Fail -- NOT POSSIBLE: Must be enforced in application, seeattached documentation from mySQL-- INSERT INTO ROOMS VALUES (20, 25, 1, 1, 80.00, 'T', 'C'); -- Attempt and FailINSERT INTO ROOMS VALUES (21, 25, null, 1, 80.00, 'T', 'F'); -- Attemp and FailDELETE HOTELS WHERE City = 'College Park';-- Delete all hotels from College ParkDELETE HOTELS,ROOMS,RESERVATIONSFROM HOTELS,ROOMS,RESERVATIONSWHERE HOTELS.CITY = 'College Park' AND HOTELS.HID = ROOMS.HID AND ROOMS.HID = RESERVATIONS.RID;-- Show data from ROOMS tableSELECT *FROM ROOMS;-- Drop all tablesDROP TABLE HOTELS,ROOMS,RESERVATIONS;END mySQL SOURCE FILE------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------mySQL RUNTIME OUTPUT ON ABOVE SOURCE FILE CODEC:\mysql\bin>mysql -vvvWelcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 21 to server version:
View Full Document
Unlocking...