LEADERS PRIVATE SCHOOL, SHARJAH
INFORMATICS PRACTICES
GRADE: XII
PRACTICAL RECORD WORK-2025
Instructions:
Each SQL must start on a new page. Number each SQL heading
as given. Leave a line or two after the heading and then write the
queries.
Leave a line after each query
The table (if given) to be drawn neatly using pencil & scale on
the plain side of the page
Record work to be neat. No overwriting or striking out is
allowed.
SQL - 1
Write SQL queries for the following:
1. Create a database SCHOOL
CREATE DATABASE SCHOOL;
2. Create a student table with the student id, name, marks, stream as attributes where the
student id is the primary key.
CREATE TABLE STUDENT
(STUD_ID INTEGER PRIMARY KEY,
NAME VARCHAR(25),
MARKS INT(10),
STREAM VARCHAR(25));
3. Insert the details of a 4 new student in the above table.
1) INSERT INTO STUDENT VALUES (101 , “JANET”, 84, “SCIENCE”);
2) INSERT INTO STUDENT VALUES (102, “JAMES” ,67, “COMMERCE”);
3) INSERT INTO STUDENT VALUES (103, “REBECCA”, 99,
“COMMERCE”);
4) INSERT INTO STUDENT VALUES (104, “JACK”, 87, “SCIENCE”);
4. To display the entire content of table.
SELECT * FROM STUDENT;
5. To display student id, Name and Marks of those students who are scoring marks more
than 80.
SELECT STUD_ID , NAME , MARKS FROM STUDENT WHERE MARKS>80 ;
6. To increase marks by 5% for those students who have student id more than 20.
UPDATE STUDENT SET MARKS = MARKS +(MARKS*5/100)
WHERE STUD_ID>20;
7. To display the unique stream available in the table.
SELECT DISTINCT (STREAM) FROM STUDENT;
8. To add a new column email in the above table with appropriate data type.
ALTER TABLE STUDENT ADD EMAIL_ID VARCHAR(15);
9. To add the email id of student 101 in the previously created email column.
UPDATE STUDENT SET EMAIL_ID= “jan@[Link]” WHERE
STUD_ID=101;
10. To display Stud_ID, Name, Marks, stream in descending order of their marks.
SELECT STUD_ID , NAME , MARKS,STREAM FROM STUDENT ORDER BY
MARKS DESC ;
11. Delete the details of the student who secured 67 marks from the above table.
DELETE FROM STUDENT WHERE MARKS=67;
12. Find the min, max, sum, and average of the marks in a student table.
SELECT MIN(MARKS), MAX(MARKS), SUM(MARKS), AVG(MARKS) FROM
STUDENT;
SQL - 2
Consider the CUSTOMERS table having the following records:
ID NAME ADDRESS COUNTRY DEPOSIT_AMT
1 KARTHIK AHMEDABAD INDIA 110050.00
2 KUSHAL DELHI INDIA 600010.00
3 SACHIN KOLKATA INDIA 300000.00
4 BOB DALLAS UNITED 190000.00
STATES
5 SANYA DELHI INDIA 100050.00
6 CHANDER ONTARIO CANADA 106500.00
7 LUCIEN PARIS FRANCE 220500.00
Write SQL queries for the following:
1. To increase the deposit amount by 1000 for the customers whose name start with ‘K’
UPDATE CUSTOMERS SET DEPOSIT_AMT=DEPOSIT_AMT+1000
WHERE NAME LIKE “K%”;
2. Find the total number of customer’s in each country
SELECT COUNTRY, COUNT(NAME) FROM CUSTOMERS GROUP BY
COUNTRY;
3. To display the count of customers from INDIA.
SELECT COUNT(NAME) FROM CUSTOMERS WHERE COUNTRY=
“INDIA”
4. To display the highest and the lowest deposit amount from the customers from each
country.
SELECT COUNTRY, MAX(DEPOSIT_AMT), MIN(DEPOSIT_AMT) FROM
CUSTOMERS GROUP BY COUNTRY;
5. Write the output for the query given:
a) SELECT LENGTH(NAME) FROM CUSTOMERS WHERE ADDRESS=
“DELHI”;
LENGTH(NAME)
b) SELECT LCASE(ADDRESS), LEFT(COUNTRY,2) FROM CUSTOMERS
WHERE NAME LIKE “%N”;
LCASE(ADDRESS) LEFT(COUNTRY,2)
KOLKATA IN
PARIS FR
c) SELECT SUBSTR(NAME,1,5) FROM CUSTOMERS WHERE COUNTRY
= “CANADA” OR COUNTRY = “FRANCE”;
SUBSTR(NAME,1,5)
CHAND
LUCIE
d) SELECT COUNT(NAME) FROM CUSTOMER WHERE
DEPOSIT_AMT>106500.00;
COUNT (NAME)
5
SQL - 3
1. Write the output of the following query:
a) SELECT MOD (11, 3);
MOD (11, 3)
b) SELECT MOD (10.5, 3);
MOD (10.5, 3)
1.5
c) SELECT ROUND (135.375, 2);
ROUND (135.375, 2)
135.38
d) SELECT ROUND (345.156, 0);
ROUND (345.156, 0)
345
e) SELECT POW (8, 3);
POW (8, 3)
512
2. Write the output of the following query:
a) SELECT NOW ( );
b) SELECT DATE (“2025/07/03”);
c) SELECT MONTH (“2024/07/03”);
d) SELECT MONTHNAME (NOW( ));
e) SELECT YEAR (“2023/11/14”);
f) SELECT DAY (“2024/07/03”);
g) SELECT DAYNAME (“2025/10/02”);
SQL - 4
A relation Vehicles is given below:
Write SQL Commands to:
(a) Display the average price of each type of vehicle having quantity more than 20.
SELECT AVG(PRICE), TYPE FROM VEHICLES GROUP BY TYPE
HAVING QTY >20;
(b) Count the type of vehicles manufactured by each company.
SELECT COUNT(*),TYPE FROM VEHICLES GROUP BY TYPE;
(c) Display the total price of all the types of vehicles.
SELECT SUM(PRICE), TYPE FROM VEHICLES GROUP BY TYPE;
SQL - 5
Consider the tables given below while attempting the following questions:
With reference to the above given tables, write commands in SQL for (i)
and (ii) and output for (iii) below:
i. To display the Train name along with its passenger name.
SELECT TNAME, PASSENGER FROM TRAIN T, RESERVATION R WHERE
[Link]=[Link];
ii. To display Train detail which has no reservation yet.
SELECT T.* FROM TRAIN T, RESERVATION R WHERE
[Link]!=[Link];
iii. SELECT T.* from Train T, Reservation R where [Link]=[Link]
AND Source LIKE “%Delhi” OR Destination LIKE “%Delhi”;
