SQL
1. Consider the following two tables: PRODUCT and CLIENT.
Table: Product
Table: Client
Write SQL statements for the following:
a) To display the ClientName and City of all Mumbai and Delhi-based clients in the Client
table.
b) Increase the price of all the products in the Product table by 10%.
c) To display the ProductName, Manufacturer, ExpiryDate of all the products that expired
on or before ‘2010-12-31’.
d) To display C_ID, ClientName, City of all the clients (including the ones that have not
purchased a product) and their corresponding ProductName sold.
e) To display the total number of Manufacturer from the table Product.
f) To display C_ID, ClientName and City starts with the letter ‘M’ from the table.
Solution:
a) Select ClientName, City from Client where City = ‘Mumbai’ or City = ‘Delhi’;
b) Update Product set Price = Price + 0.10 * Price;
c) Select ProductName, Manufacturer, ExpiryDate from Product where ExpiryDate < =
‘2010-12-31’;
d) Select C_ID, ClientName, City, ProductName from Client Left Join Product on
Client. P_ID = Product.P_ID;
e) Select COUNT(DISTINCT Manufacturer) from Product;
f) Select C_ID, Client_Name, City from Client where City Like ‘M%’;
2. Write SQL commands based on table SPORTS:
Table: SPORTS
a) Display the names of the students who have grade ‘C’ in either Game1 or Game2 or both.
b) Display the number of students getting grade ‘A’ in Cricket.
c) Display the names of the students who have the same game for both Game1 and Game2.
d) Display the game taken up by the students, whose name starts with ‘A’.
e) Add a new column named ‘Marks’.
f) Assign a value 200 for marks for all those who are getting grade ‘B’ or grade ‘A’ in both
Game1 and Game2.
Solution:
(a) SELECT Name for SPORTS where grade1=‘C’ or Grade2=‘C’;
(b) SELECT Count(*) from SPORTS where grade=‘A’;
(c) SELECT name from SPORTS where game1 = game2;
(d) SELECT game,game2 from SPORTS where name like ‘A%’;
(e) ALTER TABLE SPORTS add (marks int(4));
(f) UPDATE SPORTS set marks=200 where grade=‘A’;
3. Consider the following two tables: STATIONERY and CONSUMER.
Table: Stationery
Table: Consumer
Write SQL statements for the following:
a) To display details of all the Stationery Items in the Stationery table in descending order
of StockDate.
b) To display details of that Stationery item whose Company is XYZ and price is below 10.
c) To display ConsumerName, Address from the table Consumer and Company and Price
from Stationery table, with their corresponding S_ID.
d) To increase the price of all the stationery items in the Stationery table by 2.
e) To display number of Address from the table Consumer;
f) Select StationeryName, price * 3 from Stationery where Company = ‘CAM’;
Solution:
a) Select * from Stationery order by StockDate desc;
b) Select * from Stationery where Company = ‘XYZ’ and Price < 10;
c) Select ConsumerName, Address, Company, Price from Stationery, Consumer where
Stationery. S_ID = Consumer.P_ID;
d) Update Stationery Set Price = Price + 2;
e) Select COUNT(DISTINCT Address) from Consumer;
f) Select StationeryName, price * 3 from Stationery where Company = ‘CAM’;
4. Create a table with constraints and Write SQL commands for the following:
MOVIE
Movie Id Movie Name Type Cast Rating Qty Price
M001 Gone With the Wind Drama Clark Gable A 10 39
M002 Doctor DoLittle Comedy Eddie Murphy B 3 40
M003 Coyote Ugly Drama Piper Perabo C 4 35
M004 Rush Hour Comedy Jackie Chan A 5 1
M005 Bourne Identity Action Matt Damon B 7 22
M006 Casino Royale Action Daniel Craig B 3 90
M007 Runaway Bride Comedy Julia Roberts C 1 19
a) Find the movies starring Julia Roberts.
b) To display the different types of movies available.
c) Display all movies which have a price range between 30 and 40.
d) Display all movies where the Rating is A
e) Display count of movies by type.
f) Display movie names which are starting from ‘R’.
5. Consider the following table:
Table: Employee
Table: Job
Write SQL Queries for the following:
a) To display employee ids, names of employees, job ids with corresponding job titles.
b) To display names of employees, sales and job titles who have achieved sales more than
1300000.
c) To display names and job titles who have ‘SINGH’ (anywhere) in their names.
d) Write SQL command to change the jobid to 104 of the employee with ID as E4 in the table
Employee.
e) Display the contents of the tables.
f) Display the structure of the tables.
g) Show the average salary for all jobtitle with 2 or more than 2 for a job.
h) Display only the jobs with maximum salary greater than or equal to 80000.
i) Find out the number of employees having “manager” as job.
j) List the count of employees corresponding to their jobid.
k) List the sum of employees’ salaries corresponding to their jobtitle.
l) List the maximum salary of employees corresponding to their jobid.
Solution:
a) Select employeeid, name,[Link], jobtitle from employee, job where [Link] = [Link];
b) Select name, sales, jobtitle from employee, job where sales>1300000 and [Link] = [Link];
c) Select name, jobtitle from employee, job where name like “%singh%” and [Link] = [Link];
d) Update employee set jobid=104 where employeeid = “E4”;
e) Select * from employee, job;
f) Desc employee; desc job;
g) Select avg(salary), jobtitle from job group by jobtitle having count(*)>=2;
h) Select jobtitle from job where salary>=80000;
i) Select jobtitle, count(*) from job where jobtitle like”%manager%”;
j) Select jobtitle, count(*) from job group by jobtitle;
k) Select sum(salary), jobtitle from employee , job where [Link] = [Link] group by jobtitle;
l) Select max(salary), [Link] from employee, job where [Link] = [Link] group by [Link];
Python – SQL connectivity
1. Write a menu-driven program in Python to establish connection between
Python and MySQL to create a table ‘PRODUCT’ with the following
description.
P_ID ProductName Manufacturer Price ExpiryDate
TP01 Talcum Powder LAK 40 2011-06-26
FW05 Face Wash ABC 45 2010-12-01
BS01 Bath Soap ABC 55 2010-09-10
SH06 Shampoo XYZ 120 2012-04-09
FW12 Face Wash XYZ 95 2010-08-15
Perform following operations using the connection:
i) Insert at least 5 rows with appropriate data.
ii) To display the records in descending order of Price Column.
iii) Increase the price of all the products in Product table by 10%
2. Write a menu-driven program in Python to establish connection between
Python and MySQL to create a table ‘STATIONARY’ with the following
description.
S_ID StationaryName Company Price StockDate
DP01 Dot Pen ABC 10 2011-03-31
PL02 Pencil XYZ 6 2010-01-01
ER05 Eraser XYZ 7 2010-02-14
PL01 Pencil CAM 5 2009-01-09
GP02 Gel Pen ABC 15 2009-03-19
Perform following operations using the connection
i) Insert at least 5 rows with appropriate data.
ii) Increase the price of all the stationery items in Stationery table by 2
iii) To display details of that Stationery item whose Company is XYZ
3. Write a menu-driven program in Python to establish connection between
Python and MySQL to create a table ‘COMPANY’ with the following
description.
CID COMPANYNAME CITY PRODUCTNAME
111 Sony Delhi TV
222 Nokia Mumbai Mobile
333 Onida Delhi TV
444 Sony Mumbai Mobile
555 BlackBerry Bangalore Mobile
666 Dell Delhi Laptop
Perform following operations using the connection
i) Insert at least 5 rows with appropriate data.
ii) To display the company name which starts with ‘S’.
iii) To remove the records whose productname is ‘mobile’.
4. Write a menu-driven program in Python to establish connection between Python
and MySQL to create a table ‘EMPLOYEE’ with the following description.
E_ID Fname Lname Hire_Date Salary
102 Amit Mishra 12-10-1998 12000
103 Nitin Vyas 24-12-1994 8000
104 Rakshit Soni 18-05-2001 14000
105 Rashmi Malhotra 11-09-2004 11000
Perform following operations using the connection:
i) Insert at least 5 rows with appropriate data.
ii) To display F_ID, FirstName, LastName of all faculties whose Salary is
in the range of 10000 and 14000.
iii) To display the total of salary.