CS403 Database Management Systems Assignment#05 July idea solution 2011

[Vuhelper]

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

[Vuhelper]

cs403 5TH ASSIGNMENT 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”

[allahwahid]

Brother u Must write This code like this in order to create the view

create view [view_name] as SELECT [product-name],[category-name] FROM product, category
WHERE product.[category-ID] = category.[category-ID]

[moon13]

i will try to check your argument