Vuhelper
07-04-2011, 08:17 PM
Q 1: Write SQL statements/queries for each of individual scenario given below from the given tables on the next page. 15 marks (3+4+4+4)
Create a view to show all the supplier names along with their Supplier IDs
Create a view to show the supplier name and supplier ID, who provide the product “Mirrors”.
Create a view to list down all the product names with their category names.
Update view to change the Supplier name from “AB Hardwares” to “Uplink Hardwares”.
“You will have to give the reason, If the query for any of above statement cannot execute”
Product
Product ID
Product Name
Category ID
PID-1
Bumper Cover
CT-1
PID-2
Bumper
CT-1
PID-3
Fender
CT-2
PID-4
Mirrors
CT-3
PID-5
Hood
CT-4
Category
Category ID
Category Name
Supplier ID
CT-1
Bumper & Components
SP-1
CT-2
Fenders & Components
SP-2
CT-3
Mirrors & Components
SP-2
CT-4
Hoods & Components
SP-3
Supplier
Supplier ID
Supplier Name
SP-1
S & S corporations
SP-2
AB Hardwares
SP-3
CG Brothers
Create a view to show all the supplier names along with their Supplier IDs
Create a view to show the supplier name and supplier ID, who provide the product “Mirrors”.
Create a view to list down all the product names with their category names.
Update view to change the Supplier name from “AB Hardwares” to “Uplink Hardwares”.
“You will have to give the reason, If the query for any of above statement cannot execute”
Product
Product ID
Product Name
Category ID
PID-1
Bumper Cover
CT-1
PID-2
Bumper
CT-1
PID-3
Fender
CT-2
PID-4
Mirrors
CT-3
PID-5
Hood
CT-4
Category
Category ID
Category Name
Supplier ID
CT-1
Bumper & Components
SP-1
CT-2
Fenders & Components
SP-2
CT-3
Mirrors & Components
SP-2
CT-4
Hoods & Components
SP-3
Supplier
Supplier ID
Supplier Name
SP-1
S & S corporations
SP-2
AB Hardwares
SP-3
CG Brothers