I need the SQL statements for these. If you aren\'t sure of your answers its oka

Question

I need the SQL statements for these. If you aren't sure of your answers its okay, they dont have to be 100% right. I would just like them all answered, and as correct as possible. Thank you.

Table for #4:

table for #5:

4) Define a view named ORDER SUMMARY. It consists of the order number and order total for each order currently on file. (The order total is the sum of the number ofunits ordered times the quoted price on each order line for each order.) Sort the rows by order number. Use ORDER TOTAL as the name for the order total. a) Write what the actual VIEW command would be. b) Execute the SELECT portion of the VIEW command. c) Write the command to retrieve the order number and order total for only those orders totaling more than $1,000. d) Write the query that the DBMS actually executes. 5) Create a view named MAIN TRIPS. It consists of the trip ID, trip name, start location, distance, maximum group size, type, and season for every trip located in Maine (ME). Use the Colonial Adventure Tours database a Write what the actual VIEW command would be (in your Word document. b) Execute the SELECT portion of the VIEW command. d) Write the command to retrieve the trip ID, trip name, and distance for every Biking trip, e) Write the query that the DBMS actual executes.

Explanation / Answer

4)

a)
create view order_summary as
select order_num,sum(units*quoted_price) as ORDER_TOTAL
from orders
group by order_num

b)
select order_num,sum(units*quoted_price)
from orders
group by order_num

c)
select * from (select order_num,sum(units*quoted_price) as ORDER_TOTAL
from orders
group by order_num) as totals where totals.ORDER_TOTAL>1000

d)
select order_num,sum(units*quoted_price) as ORDER_TOTAL
from orders
group by order_num   

5)

a)
create view main_trips as
select trip_id,trip_name,start_location,distance,max_grp_size,type,season
from Tours
where state='ME'

b)
select trip_id,trip_name,start_location,distance,max_grp_size,type,season
from Tours
where state='ME'

d)
select trip_id,trip_name,distance
from Tours where type='Biking'

e) select trip_id,trip_name,start_location,distance,max_grp_size,type,season
from Tours
where state='ME'

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

---