Vision
To create global citizens committed to excellence, innovation and creativity through holistic learning.
Name: Grade: 12 Div:
Subject: IP Topic: Revision – MySQL commands and Functions
Term:1 Date : .09.2025
Revision Worksheet -1
1 Consider a table "MYPET" with the following data:
Table: MYPET
Pet_id Pet_Name Breed LifeSpan Price Discount
101 Rocky Labrador Retriever 12 16000 5
202 Duke German Shepherd 13 22000 10
303 Oliver Bulldog 10 18000 7
404 Cooper Yorkshire Terrier 16 20000 12
505 Oscar Shih Tzu NULL 25000 8
Write SQL queries for the following:
(i) Display the Breed of all the pets in uppercase.
(ii) Display the total price of all the pets.
(iii) Display the average life span of all the pets.
2 (a) Consider the given table and write the following queries in MySQL:
Table: Sports
Sid SName Fees DateofPlay CoachId
1 Karate 1200 2024-08-24 S1
2 Football 1800 2024-09-13 S2
3 Cricket 1500 2024-06-14 S3
4 Lawn Tennis 2500 2024-09-25 S4
5 Badminton 1800 2024-10-20 S5
(i) To display Sid and name of those sports which are to be played in the month of September.
(ii) To display all Sports names in lower case.
(iii) To display last two characters of all sports names whose fees is less than 1500.
(b) Predict the output of the following queries based on the table Sports given above:
(i) SELECT UCASE (TRIM(SName)) Sid=5; FROM Sports WHERE
(ii) SELECT LEFT (SName, 3) FROM Sports WHERE DateofPlay>"2024-09-13";
(iii) SELECT SUBSTR (CoachId, 1) FROM Sports WHERE SName="Cricket" or Fees>=2000;
3 Consider the following table: PRODUCT
Table: PRODUCT
PID PNAME PRICE QUANTITY
P1001 Eraser 10.50 5
P1002 Ball Pen 15.00 2
P1003 Gel Pen 25.10 3
P1004 Ruler 5.00 1
Find the output of the following SQL queries:
(i) SELECT 10+MOD (QUANTITY, 3) FROM PRODUCT WHERE PNAME "Eraser";
(ii) SELECT ROUND (PRICE, 2)*QUANTITY FROM PRODUCT WHERE QUANTITY > 2;
(iii) SELECT UCASE(RIGHT(PNAME, 2)) FROM PRODUCT;
4 Ms. Mohini is working in a school and stores the details of all students in a table SCHOOLDATA.
TABLE: SCHOOLDATA
Admno Name Class House Percent Gender Dob
20150001 Aditya Das 10 Green 86 Male 2006-02-20
20140212 Harsh Sharma 11 Red 75 Male 2004-10-05
20090234 Swapnil Pant 10 Yellow 84 Female 2005-11-21
20130216 Soumen Rao 9 Red 91 Male 2006-04-10
20190227 Rahil Arora 10 Blue 70 Male 2005-05-14
20120200 Akasha Singh 11 Red 64 Female 2004-12-16
Write SQL statements from the above given table to:
(i) To remove leading spaces from the column Name.
(ii) Display the names of students who were born on Sunday.
(i) Predict the output of the following SQL queries from the given table: SCHOOLDATA
(i) SELECT MAX(Percent) FROM SCHOOLDATA;
(ii) SELECT LEFT (Gender, 1), Name FROM SCHOOLDATA WHERE YEAR (Dob)=2005;
5 Consider the following table:
Table: SALESMAN
Scode Sname Area Qtysold Dateofjoin
S001 Ravi North 120 2015-10-01
S002 Sandeep South 105 2012-08-01
S003 Sunil NULL 68 2018-02-01
S004 Subh West 280 2010-04-01
S005 Ankit East 90 2018-10-01
S006 Raman North NULL 2019-12-01
Predict the output for the following SQL queries:
(i) SELECT MAX(Qtysold), MIN(Qtysold) FROM SALESMAN;
(ii) SELECT COUNT (Area) FROM SALESMAN;
(iii) SELECT LENGTH (Sname) FROM SALESMAN WHERE MONTH (Dateofjoin)=10;
(iv) SELECT Sname FROM SALESMAN WHERE RIGHT (Scode, 1)=5;
Based on the given table SALESMAN write SQL queries to perform the following operations:
(i) Count the total number of salesman.
(ii) Display the maximum qtysold from each area.
(iii) Display the average qtysold from each area where number of salesman is more than 1.
(iv) Display all the records in ascending order of area.
6 Find the output of the following SQL queries:
(i) SELECT SUBSTR("FIT INDIA MOVEMENT", 5);
(ii) SELECT INSTR("ARTIFICIAL INTELLIGENCE", "ΙΑ");
(i) SELECT RIGHT ("CHANDRAYAN3", 4);
(ii) SELECT ROUND (76345.456,2);
(i) SELECT ROUND (7658.345,2);
(ii) SELECT MOD (ROUND (13.9,0),3);
Write the names of SQL functions to perform the following operations:
(i) Display name of the Month from your date of birth.
(ii) Convert email-id to lowercase.
(iii) Count the number of characters in your name.
7 Excellent Consultancy Pvt. Ltd. maintains two tables for all its employees.
Table: Employee
Employee_ id First_ name Last_ name Salary Joining date Department
E101 Monika Das 100000 2019-01-20 Finance
E102 Mehek Verma 600000 2019-01-15 IT
E103 Manan Pant 890000 2019-02-05 Banking
E104 Shivam Agarwal 200000 2019-02-25 Insurance
E105 Alisha Singh 220000 2019-02-28 Finance
E106 Poonam Sharma 400000 2019-05-10 IT
E107 Anshuman Mishra 123000 2019-06-20 Banking
Table: Reward
Employee_id Date_reward Amount
E101 2019-05-11 1000
E102 2019-02-15 5000
E103 2019-04-22 2000
E106 2019-06-20 8000
Write suitable SQL queries to perform the following task:
(i) Change the Department of Shivam to IT in the table Employee.
(ii) Remove the record of Alisha from the table Employee.
(iii) Add a new column Experience of integer type in the table Employee.
(iv) Display the first name, last name and amount of reward for all employees from the tables Employee and Reward.
(v) Display first name and salary of all the employees whose amount is less than 2000 from the tables Employee and
Reward.
Write suitable SQL queries for the following task:
(i) Display the year of joining of all the employees from the table Employee.
(ii) Display each department name and its corresponding average salary.
(iii) Display the first name and date of reward of those employees who joined on Monday from the tables Employee and
Reward.
(iv) Display sum of salary of those employees whose reward amount is greater than 3000 from the tables Employee and
Reward.
(v) Remove the table Reward.
Teacher’s Feedback:
Name and Signature of the Teacher:
