Knowee
Questions
Features
Study Tools

Create a view named 'train_details_with_deviation' to display the metro train id,position and updated time of all the trains having deviation.Note:1. Do not change the name of the view2. Include only 3 columns,  id, position and updated_time from metro_train table whose train_arrival_time got deviation.3. Use subquery. Tables involved are metro_train and train_arrival_time.4. Submit only the CREATE VIEW query.Instructions:1. Create the view successfully2. Once the view is created, check the correctness of the virtual table by displaying all the records in it.3. DO NOT submit the select query. Submit only the CREATE VIEW query.

Question

Create a view named 'train_details_with_deviation' to display the metro train id,position and updated time of all the trains having deviation.Note:1. Do not change the name of the view2. Include only 3 columns,  id, position and updated_time from metro_train table whose train_arrival_time got deviation.3. Use subquery. Tables involved are metro_train and train_arrival_time.4. Submit only the CREATE VIEW query.Instructions:1. Create the view successfully2. Once the view is created, check the correctness of the virtual table by displaying all the records in it.3. DO NOT submit the select query. Submit only the CREATE VIEW query.

...expand
🧐 Not the exact question you are looking for?Go ask a question

Solution 1

Here is the SQL query to create the view:

CREATE VIEW train_details_with_deviation AS
SELECT metro_train.id, metro_train.position, metro_train.updated_time
FROM metro_train
WHERE metro_train.id IN (
    SELECT train_arrival_time.train_id
    FROM train_arrival_time
    WHERE train_arrival_time.deviation IS NOT NULL
);

This query creates a view named 'train_details_with_deviation' that includes the id, position, and updated_time columns from the metro_train table. It uses a subquery to filter out only those trains that have a deviation in their arrival time, as recorded in the train_arrival_time table.

This problem has been solved

Solution 2

Here is the SQL query to create the view:

CREATE VIEW train_details_with_deviation AS
SELECT metro_train.id, metro_train.position, metro_train.updated_time
FROM metro_train
WHERE metro_train.id IN (
    SELECT train_arrival_time.train_id
    FROM train_arrival_time
    WHERE train_arrival_time.deviation IS NOT NULL
);

This query creates a view named 'train_details_with_deviation' that includes the columns 'id', 'position', and 'updated_time' from the 'metro_train' table. The subquery in the WHERE clause filters out the trains that do not have a deviation in their arrival time. The 'train_arrival_time' table is used to check for the deviation.

This problem has been solved

Similar Questions

Create a view named 'train_details_with_deviation' to display the metro train id,position and updated time of all the trains having deviation.

Write a query to display details of the train_arrival_time which does not have any deviation.Display the records in ascending order based on the metro_train_id.

Create a view named 'meter_details' to select all the details of the buildings whose meter_number starts with 'SG' and ends with '2'.Note:1. Do not change the name of the view2. Include all the columns present in the building table. Select only the building with the meter_number starts with 'SG' and ends with '2'.3. Submit only the CREATE VIEW query.Instructions:1. Create the view successfully2. Once the view is created, check the correctness of the virtual table by displaying all the records in it.3. DO NOT submit the select query. Submit only the CREATE VIEW query.Sample Output:Created view will contain the following same data:

Create a view named 'building_details' to select all the details present in the building table.Note:1. Do not change the name of the view2. Include all the column and rows present in the building table3. Submit only the CREATE VIEW query.Instructions:1. Create the view successfully2. Once the view is created, check the correctness of the virtual table by displaying all the records in it.3. DO NOT submit the select query. Submit only the CREATE VIEW query.Sample Output:Created view will contain the following same data:

Create a view named 'electricity_reading_details' to select all the details of the electricity_reading whose total_units per day is greater than 500 units.Note:1. Do not change the name of the view2. Include all the column present in the electricity_reading table. Select only the records with the total_units greater than 500.3. Submit only the CREATE VIEW query.Instructions:1. Create the view successfully2. Once the view is created, check the correctness of the virtual table by displaying all the records in it.3. DO NOT submit the select query. Submit only the CREATE VIEW query.Sample Output:Created view will contain the following same data:

1/2

Upgrade your grade with Knowee

Get personalized homework help. Review tough concepts in more detail, or go deeper into your topic by exploring other relevant questions.