Develop a database using MySQL. DB name: called AIR_ROUTES with one table called

Question

Develop a database using MySQL.

DB name: called AIR_ROUTES with one table called ROUTE. That table contains the following columns:

Airline Char(3)

From_Airport Char(3)

To_Airport Char(3)

1. Using DDL construct the database and table.

2. Load the table with the information contained in the .csv file Routes.csv. (Hint: there are

load utilities that are part of the MySQL software.)

3. Add a new table to the database called ROUTE2. It contains the same three fields as ROUTE

and the exact names but also has the additional column of Miles INT NOT NULL with a default

of 0.

4. Construct a SQL DML query to move all the data from ROUTE to ROUTE2.

5. Display a few rows of ROUTE2 to ensure the data are there and the Miles column has a value

of 0. (Hint: use a select * statement with a limit clause to limit the number of displayed rows.)

6. Construct SQL to alter the table ROUTE2 to add a new column called Last_Update DATE with

a default of the current date.

7. Display a few rows of ROUTE2 to ensure the new column is there and it has been set to the

current date.

---------routes.csv (a portion)----------

2B,SVX,NJC
2B,SVX,NUX
2B,SVX,OVB
2B,TGK,DME
2B,UUA,DME
2B,UUA,LED
2B,UUA,NJC
2G,BTK,IKT
2G,BTK,OVB
2G,CEK,OVB
2G,HTA,IKT
2G,IKT,BTK
2G,IKT,HTA
2G,IKT,KCK

Explanation / Answer

1)
CREATE TABLE ROUTE(
    Airline CHAR(3),
    From_Airport CHAR(3),
    To_Airport CHAR(3)
);

2)
LOAD DATA INFILE 'Routes.csv' INTO TABLE ROUTE FIELDS TERMINATED BY ',';

3)
CREATE TABLE ROUTE2(
    Airline CHAR(3),
    From_Airport CHAR(3),
    To_Airport CHAR(3),
    Miles INT NOT NULL DEFAULT 0
);

4)
INSERT INTO ROUTE2 SELECT * FROM ROUTE;
DELETE FROM ROUTE;

5)
SELECT * FROM ROUTE2 WHERE Miles = 0 LIMIT 3;

6)
ALTER TABLE ROUTE2 ADD Last_Update TIMESTAMP;

7)
SELECT * FROM ROUTE2 WHERE DATE(Last_Update) = CURDATE();

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

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