CS403 Database Management Systems Assignment No. 04 Semester Spring 2011

[Vuhelper]

Assignment No. 04
Semester Spring2011
Database Management Systems-CS403
Total Marks: 15

Due Date: 20/06/2011

Objective:
To learn and understand basic concepts of SQL statements with the emphasis on “where” clause.
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:

o The assignment is submitted after due date.
o The submitted assignment does not open or file corrupt.
o The submitted assignment is in the format other than MS Word (doc).
o The assignment is copied (from other student or copy from handouts).
o Student ID is not mentioned in the assignment File or name of file is other than student ID.




Q 1: Write SQL statements/queries to show the data for each of individual scenario given below from the given tables on the next page. 15 marks (3+3+3+3+3)



1. Show all the Category names with their Supplier names.


2. Show all the names of Products with their IDs.


3. Show all the products of “Bumper & Components” Category.


4. Show all the Product names and their Supplier names.


5. Show all those Product IDs supplied by “S & S corporations”.


“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.


“Try to upload the assignment before BONUS day”

[moon13]

this is a assignment but not a idea solution

[Vuhelper]

Create table product (product_id varchar(10), product_name varchar(20) , category_id varchar(20) )

Create table category(category_id varchar(10), category_name varchar(20) , supplier_id varchar(20) )

Create table supplier(supplier_id varchar(10), supplier_name varchar(20) )

INSERT INTO `category` (`category_id`, `category_name`, `supplier_id`) VALUES
('CT-1', 'Bumper & Components', 'SP-1'),
('CT-2', 'Fenders & Components', 'SP-2'),
('CT-3', 'Mirrors & Components', 'SP-2'),
('CT-4', 'Hoods & Components', 'SP-3');

INSERT INTO `product` (`product_id`, `product_name`, `category_id`) VALUES
('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');

INSERT INTO `product` (`product_id`, `product_name`, `category_id`) VALUES
('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');


Above steps are not Required as part of assignment Its just for you to create and populate the table data to verify the commands

Show all the Category names with their Supplier names.
SELECT Category_Name, Supplier_Name
FROM Category, Supplier
WHERE category.supplier_id = supplier.supplier_id;



Show all the names of Products with their IDs.
SELECT Product_Name, Product_ID
FROM Product;

Show all the products of “Bumper & Components” Category.
SELECT product_name
FROM product, category
WHERE product.category_id = category.category_id
AND category_name = "Bumper & Components"


Show all the Product names and their Supplier names.
SELECT product_name, supplier_name
FROM product, category, supplier
WHERE product.category_id = category.category_id
AND category.supplier_id = supplier.supplier_id


Show all those Product IDs supplied by “S & S corporations”.
SELECT product_ID, supplier_name
FROM product, category, supplier
WHERE product.category_id = category.category_id
AND category.supplier_id = supplier.supplier_id
AND supplier_name = "S & S corporations"

[moon13]

ok thanks a lot