Dbms
Autor: Praveen Kumar • February 12, 2017 • Coursework • 641 Words (3 Pages) • 690 Views
DBMS
- (10%) State what the following query computes:
⨝ ⨝ Vendor_t )[pic 1][pic 2]
Answer:
[pic 3]
In Summary, it gives the vendor names who is selling wall nut at unit price lower than 14
- (20%) Compose a relational algebra expression that would show each order id that requested both of the following products in the same order: End Table and Coffee Table.
𝜋Order_ID (𝜎Product_Name = ‘Coffee Table’ ^ Product_Name = ‘End Table’ Product_t ⨝ Order_Line_t)
- (20%) Compose a SQL statement that is equivalent to Question 1 above. Note, you might want to try and execute this SQL statement against the PVF database in SQL Server 2014 to see if it works as intended.
SELECT V.Vendor_name
FROM Vendor_t V
WHERE V.Vendor_ID IN (SELECT S.vendor_id
FROM Supplies_t S, Raw_Materials_t R
WHERE R.Material_description='Walnut'
AND S.Unit_price<14
AND S.Material_ID=R.Material_ID)
[pic 4]
- (20%) Compose a SQL statement that is equivalent to Question 2 above. Note, you might want to try and execute this SQL statement against the PVF database in SQL Server 2014 to see if it works as intended.
select distinct O.Order_ID
from Order_line_t O, Product_t P
where O.Product_ID=P.Product_ID
AND Product_Name in ('End Table','Coffee Table')
[pic 5]
- (20%) Compose an SQL statement to generate a list of two least expensive vendors (suppliers) for each raw material. In the result table, show the following columns: material ID, material description, vendor ID, vendor name, and the supplier's unit price. Sort the result table by material ID and supplier’s unit price in ascending order. Note: If a raw material has only one vendor (supplier), that supplier and its unit price for the raw material should also be in the result (output) table [hint: use a correlated subquery].
SELECT R.Material_ID, R.Material_description, V.Vendor_ID, V.Vendor_name, S.Unit_price
INTO Low_Cost_Vendors
...