CS403 Database Management Systems Assignment No. 04 Semester Spring 2011

[Vuhelper]

View more random threads:

• CS610 Computer Networks Assignment no 04 FALL January 2012
• CS704 Assignment No. 01 Solution January 2016
• CS507-Information Systems Assignment No. 04 Fall 2012 Due...
• IT430 E Commerce Assignment No 03 Semester 31 May Spring...
• cs615 assignment no 4 idea solution spring 2011
• CS201 Introduction to Programming Assignment No 02 idea...
• Assignment 4 Of MTH202 solution Fall 2010
• CS605 VU Current Assignment No. 4 Fall January 2012
• data communication 2 assignment solution fall 2010
• CS402 Theory of Automata VU Assignment No. 4 Spring 2012

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



Sponsored Links


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