this is a assignment but not a idea solution
Assignment No. 04View more random threads:
- CS506 assignment no 5 fall 2010 solution 29-01-2011
- CS504 Software Engineering Assignment No.2 Fall Semester...
- CS507 Assignment No. 3 idea solution spring June 2011
- cs601 assignment no 1 April 2012 Due date 19 April 2012
- CS607 Artificial Intelligance Assignment No.2 Fall Semester...
- CS101 Assignment#4( help required plz)
- CS101 Introduction to Computing Assignment No. 03 Semester...
- CS605 Software Engineering-II Assignment No.4 January 2013
- CS101 Introduction to Computing Assignment No 5 Solution...
- Assignment No. 3 Semester: Fall 2010 CS201: Introduction to...
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”
Sponsored Links
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”
this is a assignment but not a idea solution
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"
ok thanks a lot
There are currently 1 users browsing this thread. (0 members and 1 guests)