A Practical File
Based on
Computer
Science[083]
Session :2025-2026
Topic:- SQL
Submitted By: Submitted To:
Varun Mrs. Amrita
Grade-XII Commerce PGT (C.S.)
Roll Number Sign:
Contents
No. Practical
1. Queries Set 1 (Database Fetching Records)
2. Queries Set 2 (Based on Functions)
3. Queries Set 3 (DDL Commands)
4. Queries Set 4 (Based on Two Tables)
5. Queries Set 5 (Group by, Order by)
Q1. Consider the following MOVIE table and write the SQL queries
based on it.
Movie_ID MovieName Type ReleaseDate ProductionCost BusinessCost
M001 The Action 2022/01/26 1245000 1300000
Kashmir
Files
M002 Attack Action 2022/01/28 1120000 1250000
M003 Loop Thriller 2022/02/01 250000 300000
Laptea
M004 Badhai Do Drama 2022/02/04 720000 68000
M005 Shabaash Biography 2022/02/04 1000000 800000
Mithu
M006 Gehraiyaan Romance 2022/02/11 150000 120000
a) Display all information from movie.
Ans. Select * from Movie;
b)
D
isplay the type of movies.
Ans. Select distinct Type from Movie;
c) Display movieid, moviename, total_earning by showing the business
done by the movies. Calculate the business done by movie using the
sum of productioncost and businesscost.
Ans. Select Movie_id, MoveName, productionCost+BusinessCost
‘total_earning’ from movie;
d)
D
isplay movieid, moviename and productioncost for all movies with
productioncost greater than 150000 and less than 1000000.
Ans. Select movie_id, moviename, productioncost from movie where
productioncost>150000 and productioncost <1000000;
Q2. Write the following queries:
a) Write a query to display cube of 5
Ans. Select pow(5,3)
b) Write the number 563.854741rounding off to the next hundred.
Ans. Select round(563.85474,-2);
c). Write a query to display “put” from the word “Computer”.
Ans. Select mid(“Computer”,4,3);
d). Write a query to display “DIA” from the word “MEDIA”.
Ans. Select right(“media”,3);
e). Write a query display moviename – type from the table movie.
Ans. Select concat(moviename,concat(‘-‘,type)) from movie;
f). Write a query to display dayname on which movies are going to be
released.
Ans. Select dayname(releasedate) from movie;
Q3. Write Queries for the following questions based on the given
table:
Table Name: STU
a). Write a query to delete the details of Roll number 8.
Ans. Delete from stu where Rollno=8;
b). Write a query to change the fees of studentto 170 whose roll
number is 1, if the existing fees is less than 130.
Ans. Update stu set fees=170 where Rollno=1 and fees<130.
c). Write a query to add a new column Area of type varchar in
table STU.
Ans. Alter table stu add area varchar(20);
d). Write a query to display name of all students whose area
contains NULL.
Ans. Select name from stu where Area is NULL;
Q4. Consider the following tables: Company and Model.
Table : Company
Comp_Id CompName CompHO ContacrPerson
1 Titan Okhla C.B. Ajit
2 Ajanta Najafgarh R. Mehta
3 Maxima Shahdara B. Kohli
4 Seiko Okhla R. Chadha
5 Ricoh Shahdara J. Kishore
Comp_ID is a primary key
Table : Model
Model_Id Comp_Id Cost DateOfManufacture
T020 1 2000 2010-05-12
M032 4 7000 2009-04-15
M059 2 800 2009-09-23
A167 3 1200 2011-01-12
T024 1 1300 2009-10-14
Model_ID is a primary key
Comp_ID is a Foreign key referencing Com_ID of Company table.
Write SQL commands for queries:
a) Write a query to create table Company and model.
Ans. Create table company(comp_id int primary key, compname varchar(20),
compHO varchar(20), contactperson varchar(20));
Create table model(model_id varchar(30) primary key, comp_id int
references company(com_id), cost int, dateofmanufacture date);
b) To display the detail of all models in the model table in ascending
order of DateOfManufacture.
Ans. Select * from model order by dateofmanufacture;
c) To display details of those models manufactured in 2011 and whose
cost Is below 2000.
Ans. Select * from model where year(dateofmanufacture) = 2011 and
cost<2000;
d). To display the Model_ID, Comp_ID, cost from the table Model,
Compname and Contactperson from company table, with their
corresponding comp_id.
Ans. Select model.model_id, model.comp_id, [Link],
[Link], [Link] from model, company
where [Link]=company.comp_id;
e). To decrease the cost of all the models in Model table by 15%.
Ans. Update model
Set cost=cost-(0.15*cost);
Q5. Consider the following table and write the queries:
a). Display all the items in the ascending order of atockdate.
Ans. Select * from stock order by stockdate;
b). Display maximum price of items for each dealer individually as per
dcode from stock.
Ans. Select dcode, max(unitprice) from stock group by dcode;
c). Display all the items in descending orders of itemnames.
Ans. Select * from stock order by item desc;
d). Display average price of items for each dealer individually as per
dcode form stock which average price is more than 5.
Ans. Select dcode, avg(unitprice) from stock group by dcode having
avg(unitprice)>5;
e). Display the sum of quantity for each dcode.
Ans. Select dcode, sum(qty) from stock group by dcode;