sub querry in database management systems

sub querry in database management systems

1. School of Computing Science and Engineering Program: B.TECH Course Code:E2UC302B Course Name: Database Management System

2. Program Name: B.TECH Program Code: E2UC302B School of Computing Science and Engineering C ourse Code :E2UC302B Course Name: DBMS Consider the following schema: Suppliers( sid : integer, sname : string, address: string) Parts( pid : integer, pname : string, color: string) Catalog( sid : integer, pid : integer, cost: real) The Catalog relation lists the prices charged for parts by Suppliers. Write the following queries in SQL: Create the Suppliers table CREATE TABLE Suppliers ( sid INTEGER PRIMARY KEY, sname VARCHAR(100) NOT NULL, address VARCHAR(255) );

3. Program Name: B.TECH Program Code: E2UC302B School of Computing Science and Engineering C ourse Code :E2UC302B Course Name: DBMS Create the Parts table CREATE TABLE Parts ( pid INTEGER PRIMARY KEY, pname VARCHAR(100) NOT NULL, color VARCHAR(50) ); Create the Catalog table CREATE TABLE Catalog ( sid INTEGER, pid INTEGER, cost REAL, PRIMARY KEY ( sid , pid ), FOREIGN KEY ( sid ) REFERENCES Suppliers( sid ), FOREIGN KEY ( pid ) REFERENCES Parts( pid ) );

4. Program Name: B.TECH Program Code: E2UC302B School of Computing Science and Engineering C ourse Code :E2UC302B Course Name: DBMS Insert sample data into Suppliers INSERT INTO Suppliers ( sid , sname , address) VALUES (1, 'Supplier A', '123 Elm St, Springfield'), (2, 'Supplier B', '456 Oak St, Springfield'), (3, 'Supplier C', '789 Pine St, Springfield'), (4, 'Supplier D', '321 Maple St, Springfield'), (5, 'Supplier E', '654 Cedar St, Springfield'); Insert sample data into Parts INSERT INTO Parts ( pid , pname , color ) VALUES (1, 'Part X', 'Red'), (2, 'Part Y', 'Blue'), (3, 'Part Z', 'Green'), (4, 'Part W', 'Yellow'), (5, 'Part V', 'Black'); Insert sample data into Catalog INSERT INTO Catalog ( sid , pid , cost) VALUES (1, 1, 10.50), (1, 2, 15.75), (2, 1, 11.00), (3, 3, 12.25), (4, 5, 9.99);

5. Program Name: B.TECH Program Code: E2UC302B School of Computing Science and Engineering C ourse Code :E2UC302B Course Name: DBMS 1.Find the names of suppliers who supply some red part.” Since there is not subscript under the joins, the joins are natural joins, i.e., the common attributes are equated. 2. Find the names of all red parts.

6. Program Name: B.TECH Program Code: E2UC302B School of Computing Science and Engineering C ourse Code :E2UC302B Course Name: DBMS 3. Find the IDs of suppliers who supply some red or green part.

7. Program Name: B.TECH Program Code: E2UC302B School of Computing Science and Engineering C ourse Code :E2UC302B Course Name: DBMS 4. Find the IDs of suppliers who supply some red part or are based at 21 George Street.” 5. Find the IDs of suppliers who supply some red part and some green part.

8. Program Name: B.TECH Program Code: E2UC302B School of Computing Science and Engineering C ourse Code :E2UC302B Course Name: DBMS 6. Find the names of suppliers supplying some red part for less than 100 Quid 7. Find all prices for parts that are red or green. (A part may have different prices from different manufacturers.) 8 . Find the sIDs of all suppliers who supply a part that is red or green.

9. Program Name: B.TECH Program Code: E2UC302B School of Computing Science and Engineering C ourse Code :E2UC302B Course Name: DBMS

10. Program Name: B.TECH Program Code: E2UC302B School of Computing Science and Engineering C ourse Code :E2UC302B Course Name: DBMS 1. Display name and commission for all the salesmen. SELECT name, commission FROM salesman;

11. Program Name: B.TECH Program Code: E2UC302B School of Computing Science and Engineering C ourse Code :E2UC302B Course Name: DBMS 2. Retrieve salesman id of all salesmen from orders table without any repeats. SELECT DISTINCT salesman_id FROM orders;

12. Program Name: B.TECH Program Code: E2UC302B School of Computing Science and Engineering C ourse Code :E2UC302B Course Name: DBMS 3. Display names and city of salesman, who belongs to the city of Paris. SELECT name,city FROM salesman WHERE city='Paris';

13. Program Name: B.TECH Program Code: E2UC302B School of Computing Science and Engineering C ourse Code :E2UC302B Course Name: DBMS 4. Display all the information for those customers with a grade of 200. SELECT * FROM customer WHERE grade = 200;

14. Program Name: B.TECH Program Code: E2UC302B School of Computing Science and Engineering C ourse Code :E2UC302B Course Name: DBMS 5. Display the order number, order date and the purchase amount for order(s) which will be delivered by the salesman with ID 5001. ord _ SELECT ord_no , ord_date , purch_amt FROM orders WHERE salesman_id = 5001;

15. Program Name: B.TECH Program Code: E2UC302B School of Computing Science and Engineering C ourse Code :E2UC302B Course Name: DBMS 6. Show the winner of the 1971 prize for Literature. SELECT winner FROM nobel_win WHERE year = 1971 AND subject = 'Literature'; 7. Show all the details of the winners with first name Louis. SELECT * FROM nobel_win WHERE winner LIKE 'Louis%';

16. Program Name: B.TECH Program Code: E2UC302B School of Computing Science and Engineering C ourse Code :E2UC302B Course Name: DBMS 8. Show all the winners in Physics for 1970 together with the winner of Economics for 1971. SELECT * FROM nobel_win WHERE (subject = 'Physics' AND year = 1970) UNION (SELECT * FROM nobel_win WHERE (subject = 'Economics' AND year = 1971) );

17. Program Name: B.TECH Program Code: E2UC302B School of Computing Science and Engineering C ourse Code :E2UC302B Course Name: DBMS 9. Display all the customers, who are either belongs to the city New York or not had a grade above 100. SELECT * FROM customer WHERE city = 'New York' OR NOT grade > 100; 10. Find all those customers with all information whose names are ending with the letter 'n'.

18. Program Name: B.TECH Program Code: E2UC302B School of Computing Science and Engineering C ourse Code :E2UC302B Course Name: DBMS Solution 10. SELECT * FROM customer WHERE cust_name LIKE '%n';

19. Program Name: B.TECH Program Code: E2UC302B School of Computing Science and Engineering C ourse Code :E2UC302B Course Name: DBMS

20. Program Name: B.TECH Program Code: E2UC302B School of Computing Science and Engineering C ourse Code :E2UC302B Course Name: DBMS 11. Find the name and city of those customers and salesmen who lives in the same city. SELECT C.cust_name S.name S.city FROM salesman AS S customer AS C WHERE S.city = C.city

21. Program Name: B.TECH Program Code: E2UC302B School of Computing Science and Engineering C ourse Code :E2UC302B Course Name: DBMS 12. Display all the orders issued by the salesman ' PaulAdam ' from the orders table. SELECT * FROM orders WHERE salesman_id = (SELECT salesman_id FROM salesman WHERE name = 'Paul Adam');

22. Display all the orders which values are greater than the average order value for 10th October 2012. SELECT * FROM orders WHERE purch_amt > (SELECT AVG( purch_amt ) FROM orders WHERE ord_date = '2012-10-10');