MYSQL QUERIES

Q1. Write SQL Commands for (a) to (d) and write the outputs for (e) on the basis of table

TABLE: GRADUATE

S.NO NAME STIPEND SUBJECT AVERA DIV

1 KARAN 400 PHYSICS GE
68 I
2 DIWAKAR 450 COMP SCIENCE 68 I
3 DIVYA 300 CHEMISTRY 62 I
4 REKHA 350 PHYSICS 63 I
5 ARJUN 500 MATHS 70 I
6 SABINA 400 CHEMISTRY 55 II
7 JOHN 250 PHYSICS 64 I
8 ROBERT 450 MATHS 68 I
9 RUBINA 500 COMP SCIENCE 62 I
10 VIKAS 400 MATHS 57 II

a. List the names of those students who have obtained DIV I sorted by NAME.
b. Display a report, listing NAME, STIPEND, SUBJECT and amount of stipend received in a
year assuming that the
STIPEND is paid every month.
c. To count the number of students who are either PHYSICS or COMPUTER SC graduates.
d. To insert a new row in the GRADUATE table: 11,"KAJ0L", 300, "computer sc", 75, 1
e. Give output for the following:
(i) Select MIN(Average) from Graduate where subject="PHYSICS";
(ii) SELECT SUM(STIPEND) from Graduate where Div=2;
(iii) Select AVG(Stipend) from Graduate where Average>=65;
(iv) Select count(Distinct Subject) from Graduate;

Q2.Consider the following EMP table and write the commands for the following:
Table: Emp

EmpNo EmpName City Designation DO J Sal Comm DeptID

8369 SMITH Mumbai CLERK 1990-12-18 800.00 NULL 20

8499 ANYA Varanasi SALESMAN 1991-02-20 1600.00 300.00 30

a. Show the minimum, maximum and average salary of Clerk
b. Count the number of Clerk in the Organization.
c. Display the Designation wise list of employees
d. Count the number of employees who are not getting commission.
e. Show the average salary for all departments with more than 5 working people.
f. List the count of Employees grouped by DeptID.
g. Display the maximum salary of employees in each Department
h. Display the name of Employees along with their Designation
i. Count the number of Employees working in ACCOUNTS department
 Q3. Write SQL Commands for (i) to (iv) and write the outputs for (v) to (vii) on the basis of
table:
Table: SOFTDRINK
DRINKCODE DNAME PRICE CALORIES
101 Lime and Lemon 20,00 120
102 Apple Drink 18.00 120
103 Nature Nectar 15.00 115
104 Green Mango 15,00 140
105 Aam Panna 20.00 135

106 Mango Juice Bahar 12.00 150

(i) To display names and drink codes of those drinks those have more than 120 calories.
(ii) To display drink codes, names and calories of all drinks, in descending order of calories.
(iii) To display names and price of drinks that have price in the range 12 to 18 [both 12 and 18
included)
(iv) Increase the price of all drinks in the given table by 10%.
(v) SELECT COUNT(DISTINCT(PRICE)) FROM SOFTDRINK;
(vi) SELECT MAX (CALORIES) FROM SOFTDRINK;
(vii) SELECT DNAME FROM SOFTDRINK WHERE DNAME LIKE "%Mango%";

Q4. Write SQL Commands for (i) to (iv) and write the outputs for (v) to (vii) on the basis of table:
Table: EXAM

No Name Stipend Subject Average Division

1 Karan 400 English 58 FIRST

2 Aman 680 Mathematics 72 FIRST

3 Javed 500 Accounts 67 FIRST

4 Btshakh 200 Informatics 55 SECOND

5 Sugandha 400 History 35 THIRD

6 Supama 550 Geography 45 THIRD

(i) To list the names of those students, who have obtained Division as FIRST in the
ascending order of NAME.
(ii) To display a report listing NAME, SUBJECT and Annual stipend received assuming that
the stipend column has monthly stipend.
(iii) To count the number of students, who have either accounts or informatics as subject
(iv) To insert a new row in the table EXAM: 6,"Mohan",500,"English",73,"Second"
(v) Write a query to show the current date and time.
(vi) SELECT AVG(Stipend) FROM EXAM WHERE DIVISION="THIRD"
(vii) SELECT COUNT(DISTINCT Subject) FROM EXAM;
(viii) SELECT MIN(Average) FROM EXAM WHERE Subject="English";
Q 5. Write SQL Commands for (a) to (e) and write the outputs for (f) on the basis of table
Table : FURNITURE

NO ITEM NAME TYPE DATEOFSTOCK PRICE DISCOUNT

1 White Lotus Double Bed 2002-02-23 3000 25
2 Pink feathers Babv Cot 2002-01-29 7000 20
3 Dolphin Baby Cot 2002-02-19 9500 20
4 Decent Office Table 2002-02-01 25000 30
5 Comfort zone Double Bed 2002-02-12 25000 30
6 Donald Babv cot 2002-02-24 6500 15
7 Roval Finish Office Table 2002-02-20 18000 30
8 Roval tiger Sofa 2002-02-22 31000 30
9 Econo sitting Sofa 2001-12-13 9500 25
10 Eating Paradise Dinning Table 2002-12-19 11500 25
(a) To show all the information about the Baby cots from the furniture table.
(b) To hst the item name which are priced at more than 15000 from the furniture table.
(c) To hst itemname and type of those items, in which dateofstock is before 2002-02-01
from the furniture table in descending order of item name.
(d) To display item name and dateofstock of those items, in which the discount percentage is
more than 25 from the furniture table.
(e) To count the number of items, whose TYPE is "Sofa" from the furniture table.
(f) Give the output of following SQL statement:
(i). select count (discount type) from furniture:
(ii). Select max(discount) from furniture:
(iii). Select avg(discount) from furniture where type="Baby Cot";
(iv). Select sum(price) from furniture where dateofstock < '2002-02-12':
(v). Select count (*) from furniture;

Q6.Consider the following table and predict the output:

TABLE: CLUB

COACH ID COACHNAME AGE SPORTS DATOFAPP PAY SEX

1 KUKREJA 35 KARATE 1996-03-27 1000 M
2 RAVINA 34 KARATE 1998-01-20 1200 F
3 KARAN 34 SQUASH 1998-02-19 2000 M
4 TARUN 33 BASKETBALL 1998-01-01 1500 M
5 ZUBIN 36 SWIMMING 1998-01-12 750 M
6 KETAKI 36 SWIMMING 1998-02-24 300 F
7 ANKITA 36 SQUASH 1998-02-20 2200 F
8 ZAREEN 37 KARATE 1998-02-22 1100 F
9 KUSH 41 SWIMMING 1998-01-13 900 M
10 SHAILYA 37 BASKETBALL 1998-02-19 1700 M
Give the output of following SQL statements:
1.SELECT LCASE (SPORTS) FROM Club;
2.SELECT MOD (Age, 5) FROM CLUB WHERE Sex =7;
3.SELECT POWER (3,2) FROM CLUB WHERE Sports='KARATE';
4.SELECT SubStr (CoachName, 1,2) FROM CLUB WHERE Datofapp>' 1998-01-31';
5.Write a query to show the current date and time.
Q7. Consider the following table and predict the output:
TABLE: STUDENT

No Name Stipend Stream AvgMark Grade Class

1 Karan 400 Medical 78.5 B 12B
2 Divakar 450 Commerce 89.2 A 11C
3 Divya 300 Commerce 686 C 12C
4 Arun 350 Humanities 73.1 B 12C
5 Sabina 500 Nonmedical 90.6 A 11A
6 John 400 Medical 75.4 B 12B
7 Robert 250 Humanities 644 C 11A
8 Rubina 450 Nonmedical 88.5 A 12A
9 Vikas 500 Nonmedical 920 A 12A
10 Mohan 300 Commerce 67.5 C 12C

1.SELECT TRUNCATE (AvgMark) FROM Student WHERE AvgMark<75;

2.SELECT ROUND(AvgMark) FROM Student WHERE Grade='B';
3.SELECT CONCAT(Name, Stream) FROM Student WHERE Class='12A';
4.SELECT RIGHT (Stream,2) FROM Student
 Q8. Consider the following tables item and Customer. Write SQL Commands for the statement (i) to
(iv) and give outputs for SQL queries (v) to (viii).

i. To display the details of those customers whose city is Delhi.

ii. To display the details of item whose price is in the range of 35000 to 55000 ( both values included)
iii. To display the customer name, city from table Customer, and itemname and price from table Item,
with their corresponding matching I_ID.
iv. To increase the price of all items by 1000 in the table Item.
v. SELECT DISTINCT City FROM Customer;
vi. SELECT ItemName, MAX(Price), Count(*) FROM Item GROUP BY ItemName;
vii. SELECT CustomerName, Manufacturer FROM Item, Customer WHERE Item.
item_Id=Customer.Item_Id
viii. SELECT ItemName, Price* 100 FROM Item WHERE Manufacture= ‘ABC’;
Q9. Consider the following tables Product and Clint. Write SQL commands for the statement (i) to
(iv) and give outputs for SQL queries (v) to (viii)

i.To display the details of those Clients whose City is Delhi.

ii.To display the details of Products Whose Price is in the range of 50 to 100(Both values
included).
iii.To display the ClientName, City from table Client, and ProductName and Price from table
Product, with their corresponding matching P-ID.
iv.To increase the Price of all Products by 10.
v.SELECT DISTINCT City FROM Client”
vi.SELECT Manufacturer, MAX(Price), Min(Price), Count(*) FROM Product GROUP BY
Manufacturer; vii.SELECT ClientName, ManufacturerName FROM Product, Client WHERE
Client.Prod-ID=Product.P_ID;
viii.SELECT ProductName, Price * 4 FROM Product;