CS403 Assignment No. 5 solution
Monday, July 04, 2011 Posted In CS and IT Edit ThisObjective:
To learn and understand basic concepts of “creation and updating” of views.
Instructions:
Please read the following instructions carefully before solving & submitting assignment:
It should be clear that your assignment will not get any credit (marks) if:
The assignment is submitted after due date.
The submitted assignment does not open or file corrupt.
The submitted assignment is in the format other than MS Word (doc).
The assignment is copied (from other student or copy from handouts).
Student ID is not mentioned in the assignment File or name of file is other than student ID.
For any query about the assignment, contact at cs403@vu.edu.pk
GOOD LUCK
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
Guidelines / Instructions
Understand the scenario on the basis of given data only.
Write only SQL statements, don’t show any data.
Solution:
1. Create a view to show all the supplier names along with their Supplier IDs
Answer: SELECT [supplier-name],[supplier-ID] FROM supplier
2. Create a view to show the supplier name and supplier ID, who provide the product “Mirrors”.
Answer:
SELECT [supplier-name],[supplier-ID] FROM product, category, supplier
WHERE supplier.[supplier-ID] = category.[supplier-ID] and
category.[category-ID] = product.[category.ID] and
product. product-name = “Mirrors”
3. Create a view to list down all the product names with their category names
Answer:
SELECT [product-name],[category-name] FROM product, category
WHERE product.[category-ID] = category.[category-ID]
4. Update view to change the Supplier name from “AB Hardwares” to “Uplink Hardwares”.
Answer:
UPDATE supplier SET supplier-name = “Uplink Hardwares”
WHERE supplier-name = “AB Hardwares”
::::::::::::::::::::::::::::::::::
Write SQL statements/queries for each of individual scenario given below from the given tables on the next page.
Solution:
1.Create a view to show all the supplier names along with their Supplier IDs
CREATE VIEW sup_view AS (SELECT supplier_name, supplier_id
FROM supplier)
2.Create a view to show the supplier name and supplier ID, who provide the product “Mirrors”.
CREATE VIEW sup_pro_cat_view AS (SELECT supplier_name, supplier_id
FROM supplier, category, product
WHERE supplier.supplier_id = category.supplier_id
AND category.category_id = product.category_id
AND product.product_name = “Mirrors”)
3.Create a view to list down all the product names with their category names.
CREATE VIEW pro_cat_view (SELECT product_name, caregory_name
FROM product, category
WHERE product.category_id = caregory.category_id)
4.Update view to change the Supplier name from “AB Hardwares” to “Uplink Hardwares”.
UPDATE pro_view set supplier_name = “Uplink Hardwares”
WHERE supplier_name = “AB Hardwares”
Solution:
1. Create a view to show all the supplier names along with their Supplier IDs
Answer: SELECT [supplier-name],[supplier-ID] FROM supplier
2. Create a view to show the supplier name and supplier ID, who provide the product “Mirrors”.
Answer:
SELECT [supplier-name],[supplier-ID] FROM product, category, supplier
WHERE supplier.[supplier-ID] = category.[supplier-ID] and
category.[category-ID] = product.[category.ID] and
product. product-name = “Mirrors”
3. Create a view to list down all the product names with their category names
Answer:
SELECT [product-name],[category-name] FROM product, category
WHERE product.[category-ID] = category.[category-ID]
4. Update view to change the Supplier name from “AB Hardwares” to “Uplink Hardwares”.
Answer:
UPDATE supplier SET supplier-name = “Uplink Hardwares”
WHERE supplier-name = “AB Hardwares”
::::::::::::::::::::::::::::::::::
Write SQL statements/queries for each of individual scenario given below from the given tables on the next page.
Solution:
1.Create a view to show all the supplier names along with their Supplier IDs
CREATE VIEW sup_view AS (SELECT supplier_name, supplier_id
FROM supplier)
2.Create a view to show the supplier name and supplier ID, who provide the product “Mirrors”.
CREATE VIEW sup_pro_cat_view AS (SELECT supplier_name, supplier_id
FROM supplier, category, product
WHERE supplier.supplier_id = category.supplier_id
AND category.category_id = product.category_id
AND product.product_name = “Mirrors”)
3.Create a view to list down all the product names with their category names.
CREATE VIEW pro_cat_view (SELECT product_name, caregory_name
FROM product, category
WHERE product.category_id = caregory.category_id)
4.Update view to change the Supplier name from “AB Hardwares” to “Uplink Hardwares”.
UPDATE pro_view set supplier_name = “Uplink Hardwares”
WHERE supplier_name = “AB Hardwares”
