Create a new table named frequent travelers (FreqTrav). It will only store passe

ID: 647293 • Letter: C

Question

Create a new table named frequent travelers (FreqTrav). It will only store passengers who have taken more than 2 trips with the company. It will record the passenger name, passenger number, address, phone number, total trips and total fare paid. Show your CREATE TABLE query and INSERT query. Please insert the records using subquery (i.e. insert from a SELECT query).

Explanation / Answer

CREATE TABLE Query and INSERT Query:

CREATE TABLE FreqTrav (
    PASSENGERNUM INT(20)
    CHECK (PASSENGERNUM IN
    (SELECT PS.PASSENGERNUM
    FROM PASSENGER P, VOYAGE V
    WHERE PS.PASSENGERNUM = V.PASSENGERNUM
    GROUP BY V.PASSENGERNUM
    HAVING COUNT( * ) >2) )

    passName CHAR (20)
    address VARCHAR(50)
    phNum CHAR(20)
        totalTrips INT (20)

INSERT INTO PASSENGERDETAILS( passName,address,phNum,totalTrips) VALUES('John','1st street,'USA','0091999999','4');

INSERT INTO PASSENGERDETAILS( passName,address,phNum,totalTrips) VALUES('Sam','2nd street,'California','0081888','2');

INSERT INTO PASSENGERDETAILS( passName,address,phNum,totalTrips) VALUES('John','11th street,'Candata','000789333','10');

        CHECK (totaltrips IN
(SELECT COUNT(PASSENGERNUM)
FROM VOYAGE
GROUP BY PASSENGERNUM HAVING COUNT(*) >2))
        totalfare INT (20)
     CHECK (totalfare IN
( SELECT SUM(FARE)
FROM VOYAGE
WHERE PASSENGERNUM IN
(SELECT COUNT(PASSENGERNUM)
FROM VOYAGE
GROUP BY PASSENGERNUM HAVING COUNT(*) >2)))
        );

Navigate

Create a new table containing these four columns: Emp#, Lastname, Firstname, and

Create a new table that will be used to track employee location. The attributes

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

Create a new table named frequent travelers (FreqTrav). It will only store passe

Question

Explanation / Answer

Related Questions

Navigate