CS403 Database Management Systems Assignment No. 04 Semester Spring 2011

[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"