Blog: CS403 Assignment No. 5 solution

Objective:

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”