After creating the tables, insert the sample data from Project into each table,

Question

After creating the tables, insert the sample data from Project into each table, and print the statements used for each insert transaction. After all rows have been inserted, run a SELECT statement on each table to list all contents (all columns and all rows). Print the SELECT statement and output for each table before proceeding to Step III.

ResDetail Customer CustlD CustFName CustLName CustPhone CustType LoyaltylD Reservation ResID CheckinDate CheckOutDate CustID AgentID Room CustType CustType CustTypeDesc RoomNum RateType RateAmt RoomType Agent AgentlD AgentFName AgentLName AgentType RateType RateType RateTypeDesc RoomType RoomType RoomTypeDesc AgentType AgentType AgentTypeDesc

Explanation / Answer

create table CUST_TYPE (CUST_TYPE INT NOT NULL AUTO_INCREMENT PRIMARY KEY, CUST_TYPE_DESC VARCHAR(20))

create table CUSTOMER (CUST_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, CUST_FNAME VARCHAR(255) ,CUST_LNAME VARCHAR(255) ,CUST_PHONE VARCHAR(10) ,CUST_TYPE INT, CONSTRAINT FK_CustTypeCustomer FOREIGN KEY (CUST_TYPE) REFERENCES CUST_TYPE(CUST_TYPE), LOYALITY_ID VARCHAR(20))

create table RESERVATION (RES_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, CHECK_IN_DATE DATE, CHECK_OUT_DATE DATE, CHECK_IN_DATE DATE , CUST_ID INT, CONSTRAINT FK_CustomerReservation FOREIGN KEY (CUST_ID) REFERENCES CUSTOMER(CUST_ID), AGENT_ID INT, CONSTRAINT FK_AgentReservation FOREIGN KEY (AGENT_ID) REFERENCES AGENT(AGENT_ID))

create table RES_DETAIL (RES_ID INT NOT NULL, ROOM_NUM INT NOT NULL , RATE_TYPE VARCHAR(20), RATE_AMT VARCHAR(20) PRIMARY KEY (RES_ID, ROON_NUM), FOREIGN KEY(RES_ID) REFERENCES RESERVATION(RES_ID), FOREIGN KEY (ROOM_NUM) REFERENCES ROOM(ROOM_NUM))

create table AGENT_TYPE (AGENT_TYPE INT NOT NULL AUTO_INCREMENT PRIMARY KEY, AGENT_TYPE_DESC VARCHAR(255))
CREATE TABLE AGENT (AGENT_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, AGENT_FNAME VARCHAR(255), AGENT_LNAME VARCHAR(255), AGENT_TYPE INT , CONSTRAINT FK_AgentTypeAgent FOREIGN KEY(AGENT_TYPE) REFERENCES AGENT_TYPE(AGENT_TYPE))

create table ROOM_TYPE (ROOM_TYPE INT NOT NULL AUTO_INCREMENT PRIMARY KEY , ROOM_TYPE_DESC VARCHAR(255))
create table RATE_TYPE (RATE_TYPE INT NOT NULL AUTO_INCREMENT PRIMARY KEY, RATE_TYPE_DESC VARCHAR(255))

CREATE TABLE ROOM (ROOM_NUM INT NOT NULL AUTO_INCREMENT PRIMARY KEY, ROOM_TYPE INT , CONSTRAINT FK_RoomTypeRoom FOREIGN KEY(ROOM_TYPE) REFERENCES ROOM(ROOM_TYPE))

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Insert Queries for CUST_TYPE

INSERT INTO CUST_TYPE (CUST_TYPE, CUST_TYPE_DESC)
VALUES ("CustType1", "Description of CustType1");

INSERT INTO CUST_TYPE (CUST_TYPE, CUST_TYPE_DESC)
VALUES ("CustType2", "Description of CustType2");

INSERT INTO CUST_TYPE (CUST_TYPE, CUST_TYPE_DESC)
VALUES ("CustType3", "Description of CustType4");

INSERT INTO CUST_TYPE (CUST_TYPE, CUST_TYPE_DESC)
VALUES ("CustType4", "Description of CustType5");

-- Insert Queries for AGENT_TYPE

INSERT INTO AGENT_TYPE (AGENT_TYPE, AGENT_TYPE_DESC)
VALUES ("AgentType1", "Description of AgentType1");

INSERT INTO AGENT_TYPE (AGENT_TYPE, AGENT_TYPE_DESC)
VALUES ("AgentType2", "Description of AgentType2");

INSERT INTO AGENT_TYPE (AGENT_TYPE, AGENT_TYPE_DESC)
VALUES ("AgentType3", "Description of AgentType3");

INSERT INTO AGENT_TYPE (AGENT_TYPE, AGENT_TYPE_DESC)
VALUES ("AgentType4", "Description of AgentType4");

-- Insert Queries for RATE_TYPE

INSERT INTO RATE_TYPE (RATE_TYPE, RATE_TYPE_DESC)
VALUES ("RateType1", "Description of RateType1");

INSERT INTO RATE_TYPE (RATE_TYPE, RATE_TYPE_DESC)
VALUES ("RateType2", "Description of RateType2");

INSERT INTO RATE_TYPE (RATE_TYPE, RATE_TYPE_DESC)
VALUES ("RateType3", "Description of RateType3");

INSERT INTO RATE_TYPE (RATE_TYPE, RATE_TYPE_DESC)
VALUES ("RateType4", "Description of RateType4");

-- Insert Queries for ROOM_TYPE

INSERT INTO ROOM_TYPE (ROOM_TYPE, ROOM_TYPE_DESC)
VALUES ("RoomType1", "Description of RoomType1");

INSERT INTO ROOM_TYPE (ROOM_TYPE, ROOM_TYPE_DESC)
VALUES ("RoomType2", "Description of RoomType2");

INSERT INTO ROOM_TYPE (ROOM_TYPE, ROOM_TYPE_DESC)
VALUES ("RoomType3", "Description of RoomType3");

INSERT INTO ROOM_TYPE (ROOM_TYPE, ROOM_TYPE_DESC)
VALUES ("RoomType4", "Description of RoomType4");

Insert Queries for CUSTOMER

INSERT INTO CUSTOMER(CUST_FNAME, CUST_LNAME, CUST_PHONE, CUST_TYPE, LOYALITY_ID)
VALUES ("John", "Smith", "9898984444", "Type1", 100);

INSERT INTO CUSTOMER(CUST_FNAME, CUST_LNAME, CUST_PHONE, CUST_TYPE, LOYALITY_ID)
VALUES ("Jaden", "Smith", "9894984444", "Type2", 101);

INSERT INTO CUSTOMER(CUST_FNAME, CUST_LNAME, CUST_PHONE, CUST_TYPE, LOYALITY_ID)
VALUES ("Joe", "Mario", "9898974444", "Type3", 102);

INSERT INTO CUSTOMER(CUST_FNAME, CUST_LNAME, CUST_PHONE, CUST_TYPE, LOYALITY_ID)
VALUES ("Jefrey", "Boycott", "9398984444", "Type4", 103);

INSERT INTO CUSTOMER(CUST_FNAME, CUST_LNAME, CUST_PHONE, CUST_TYPE, LOYALITY_ID)
VALUES ("Adam", "Strange", "9898984244", "Type1", 101);

---Insert RES_DETAIL : 1

INSERT INTO CUSTOMER(CUST_FNAME, CUST_LNAME, CUST_PHONE, CUST_TYPE, LOYALITY_ID)
VALUES ("John", "Smith", "9898984444", "CustType1", 100);
SET @CUST_ID = LAST_INSERT_ID();

INSERT INTO AGENT(AGENT_FNAME, AGENT_LNAME, CUST_TYPE)
VALUES ("Wilfred", "Songa", "9898914444", "AgentType1");
SET @AGENT_ID = LAST_INSERT_ID();

INSERT INTO RESERVATION(CHECK_IN_DATE, CHECK_OUT_DATE, CUST_ID, AGENT_ID)
VALUES (TO_DATE('7/2/2018', 'DD/MM/YYYY'), TO_DATE('9/2/2018', 'DD/MM/YYYY'), @CUST_ID, @AGENT_ID);
SET @RES_ID = LAST_INSERT_ID();

INSERT INTO ROOM(ROOM_TYPE) VALUES ("RoomType1");
SET @ROOM_NUM = LAST_INSERT_ID();

INSERT INTO RES_DETAIL(RES_ID, ROOM_NUM, RATE_TYPE, RATE_AMOUNT)
VALUES (@RES_ID, @ROOM_NUM, "RateType1", 1000);

---Insert RES_DETAIL : 2

INSERT INTO CUSTOMER(CUST_FNAME, CUST_LNAME, CUST_PHONE, CUST_TYPE, LOYALITY_ID)
VALUES ("Jaden", "Smith", "9898384444", "CustType2", 101);
SET @CUST_ID = LAST_INSERT_ID();

INSERT INTO AGENT(AGENT_FNAME, AGENT_LNAME, CUST_TYPE)
VALUES ("Will", "Songa", "985914444", "AgentType2");
SET @AGENT_ID = LAST_INSERT_ID();

INSERT INTO RESERVATION(CHECK_IN_DATE, CHECK_OUT_DATE, CUST_ID, AGENT_ID)
VALUES (TO_DATE('3/2/2018', 'DD/MM/YYYY'), TO_DATE('9/2/2018', 'DD/MM/YYYY'), @CUST_ID, @AGENT_ID);
SET @RES_ID = LAST_INSERT_ID();

INSERT INTO ROOM(ROOM_TYPE) VALUES ("RoomType2");
SET @ROOM_NUM = LAST_INSERT_ID();

INSERT INTO RES_DETAIL(RES_ID, ROOM_NUM, RATE_TYPE, RATE_AMOUNT)
VALUES (@RES_ID, @ROOM_NUM, "RateType1", 1200);

---Insert RES_DETAIL : 3

INSERT INTO CUSTOMER(CUST_FNAME, CUST_LNAME, CUST_PHONE, CUST_TYPE, LOYALITY_ID)
VALUES ("William", "Eagle", "9898984424", "CustType3", 102);
SET @CUST_ID = LAST_INSERT_ID();

INSERT INTO AGENT(AGENT_FNAME, AGENT_LNAME, CUST_TYPE)
VALUES ("Wilfred", "Bones", "9898914424", "AgentType3");
SET @AGENT_ID = LAST_INSERT_ID();

INSERT INTO RESERVATION(CHECK_IN_DATE, CHECK_OUT_DATE, CUST_ID, AGENT_ID)
VALUES (TO_DATE('7/2/2018', 'DD/MM/YYYY'), TO_DATE('10/2/2018', 'DD/MM/YYYY'), @CUST_ID, @AGENT_ID);
SET @RES_ID = LAST_INSERT_ID();

INSERT INTO ROOM(ROOM_TYPE) VALUES ("RoomType3");
SET @ROOM_NUM = LAST_INSERT_ID();

INSERT INTO RES_DETAIL(RES_ID, ROOM_NUM, RATE_TYPE, RATE_AMOUNT)
VALUES (@RES_ID, @ROOM_NUM, "RateType3", 2000);

---Insert RES_DETAIL : 4

INSERT INTO CUSTOMER(CUST_FNAME, CUST_LNAME, CUST_PHONE, CUST_TYPE, LOYALITY_ID)
VALUES ("Adam", "lopez", "9823984444", "CustType4", 103);
SET @CUST_ID = LAST_INSERT_ID();

INSERT INTO AGENT(AGENT_FNAME, AGENT_LNAME, CUST_TYPE)
VALUES ("ibrah", "Son", "9858914444", "AgentType4");
SET @AGENT_ID = LAST_INSERT_ID();

INSERT INTO RESERVATION(CHECK_IN_DATE, CHECK_OUT_DATE, CUST_ID, AGENT_ID)
VALUES (TO_DATE('1/2/2018', 'DD/MM/YYYY'), TO_DATE('3/2/2018', 'DD/MM/YYYY'), @CUST_ID, @AGENT_ID);
SET @RES_ID = LAST_INSERT_ID();

INSERT INTO ROOM(ROOM_TYPE) VALUES ("RoomType4");
SET @ROOM_NUM = LAST_INSERT_ID();

INSERT INTO RES_DETAIL(RES_ID, ROOM_NUM, RATE_TYPE, RATE_AMOUNT)
VALUES (@RES_ID, @ROOM_NUM, "RateType4", 3000);

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SELECT * FROM CUST_TYPE
SELECT * FROM CUSTOMER
SELECT * FROM RESERVATION
SELECT * FROM RES_DETAIL
SELECT * FROM ROOM
SELECT * FROM AGENT
SELECT * FROM AGENT_TYPE
SELECT * FROM RATE_TYPE
SELECT * FROM ROOM_TYPE

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.