Question

Please use the tables in the flights database. Your deliverable should include a single SQL query that I can run against a "fresh copy" of the tables in the flights database.
1. Write a script to select all data from the planes table.
2. Update the planes table so that rows that have no year will be assigned the year 2013. (missing values will display as NULL).
3. Insert a new record to the planes table with the following values:
Tailnum: N15501
Year: 2013
type: Fixed wing single engine
Manufacturer: BOEING
Model: A222-101
Engines: 3
Seats: 100
Speed: NULL (notice this is not a text value but truly the absence of any value: "empty". As such, don't use single or double quotes around NULL when inserting this value).
Engine: Turbo-fan
4. Delete the newly inserted record on step 3.

Answer 1

Answer:

The scripts are:

1. SELECT * FROM planes

2. UPDATE planes SET YEAR = 2013 WHERE year IS NULL

3. INSERT INTO planes (Tailnum, Year, type, Manufacturer, Model, Engines, Seats) VALUES ('N15501',2013,'Fixed wing single engine', 'BOEING', 'A222-101',3,100,NULL)

4. DELETE FROM planes WHERE Tailnum = 'N15501'

Explanation:

1. SELECT * FROM planes

To select all from a table, use select * from [table-name]. In this case, the table name is planes

2. UPDATE $planes\ SET$ YEAR = 2013 WHERE year IS NULL

To do this, we use the update query which is as follows:

UPDATE [table-name] SET [column-name]= [value] WHERE [column] IS NULL

So: the above query will update all YEAR column whose value is NULL to 2014

3. INSERT INTO planes (Tailnum, Year, type, Manufacturer, Model, Engines, Seats) VALUES ('N15501',2013,'Fixed wing single engine', 'BOEING', 'A222-101',3,100,NULL)

To insert is very straight foward.

The syntax is:

INSERT INTO [table-name] (column names) VALUES (values)

4. DELETE FROM planes WHERE Tailnum = 'N15501'

To do this, we use the update query which is as follows:

DELETE FROM [table-name] WHERE [column] = [value]

So: The above query will delete the entry in (3) above