Grade 12 Lab Tasks
MY SQL
Queries Set 1 (DDL Commands)
Suppose your school management has decided to conduct cricket matches between students of Class
XI and Class XII. Students of each class are asked to join any one of the four teams – Team Titan,
Team Rockers, Team Magnet and Team Hurricane. During summer vacations, various matches will be
conducted between these teams. Help your sports teacher to do the following:
1. Create a database “Sports”.
2. Create a table “TEAM” with following considerations:
a. It should have a column TeamID for storing an integer value between 1 to 9, which refers
to unique identification of a team.
b. Each TeamID should have its associated name (TeamName), which should be a string of
length not more than 10 characters.
c. Using constraint, make TeamID as the primary key.
d. Show the structure of the table TEAM using a SQL statement.
e. As per the preferences of the students four teams were formed as given below. Insert
these four rows in TEAM table:
i. Row 1: (1, Titan)
ii. Row 2: (2, Rockers)
iii. Row 3: (3, Magnet)
iv. Row 4: (4, Hurricane)
f. Show the contents of the table TEAM using a DML statement.
g. Now create another table MATCH_DETAILS and insert data as shown below. Choose
appropriate data types and constraints for each attribute.
Second Team
MatchID MatchDate First TeamID Second TeamID First Team Score
Score
M1 2018-07-17 1 2 90 86
M2 2018-07-18 3 4 45 48
M3 2018-07-19 1 3 78 56
M4 2018-07-19 2 4 56 67
M5 2018-07-18 1 4 32 87
M6 2018-07-17 2 3 67 51
Queries Set 2 (Database Fetching records)
[1] Consider the following MOVIE table and write the SQL queries based on it.
Release Production Business
Movie_ID MovieName Type Date Cost Cost
M001 The Kashmir Files Action 2022-01-26 1245000 1300000
M002 Attack Action 2022-01-28 1120000 1250000
M003 Looop Lapeta Thriller 2022-02-01 250000 300000
M004 Badhai Do Drama 2022-02-04 720000 68000
M005 Shabaash Mithu Biography 2022-02-04 1000000 800000
1. Display all the records from the table movie.
2. Display the different type of movies.
3. Display movieid, moviename, total earning by calculating business done by movie using the
sum of production cost and business cost .
4. Display movieid, moviename and productioncost for all movies with productioncost greater than
150000 and less than 1000000.
5. Display the movie of type action and romance.
6. Display the list of movies which are going to release in February, 2022.
Queries set 3 (Based on Two Tables)
Write SQL Queries for the following :
1. To display employee ids, names of employees, job ids with corresponding job titles.
2. To display names of employees, sales and corresponding job titles who have achieved sales more than
1300000(not included).
3. To display names and corresponding job titles of those employees who have 'SINGH' (anywhere) in
their names.
4. Show employee name ,job title and salary of those employees who are working in any one of the
mentioned job( President,Vice President, Accountant)
5. Write SQL command to change the JOBID to 104 of the EMPLOYEE with ID as E4 in the table
'EMPLOYEE'.
Queries Set 4 (Group by , Order By)
Consider the following ‘stock’ table to answer the queries:
Itemno item dcode qty price stockdate
S005 Ballpen 102 100 10 2018-04-22
S003 Gel Pen 101 150 15 2018-03-18
S002 Pencil 102 125 5 2018-02-25
S006 Eraser 101 200 3 2018-01-12
S001 Sharpner 103 110 5 2018-06-11
S004 Compass 102 60 25 2018-05-10
S009 A4 Papers 102 160 5 2018-07-15
1. Display all the items in the ascending order of stockdate.
2. Display number of items,total price for each dealer individually as per dcode from stock.
3. Display all the items in descending orders of itemnames.
4. Display maximum price of items for each dealer individually as per dcode from stock which has
maximum price more than 10.
5. Display the sum of quantity for each dcode from stock which has price between 10 to 30.
Queries Set 5 (Based on Functions)
1. Write a query to display cube of 5.
2. Write a query to display the number 563.854741 rounding off to the next hnudred.
3. Write a query to display “put” from the word “Computer”.
4. Write a query to display today’s date into DD.MM.YYYY format.
5. Write a query to display ‘DIA’ from the word “MEDIA”.
6. Write a query to display moviename – type from the table movie.
7. Write a query to display first four digits of productioncost.
8. Write a query to display last four digits of businesscost.
9. Write a query to display weekday of release dates.
10. Write a query to display dayname on which movies are going to be released.
SQL Database Connectivity Programs
1. Write a MySQL connectivity program in Python to
o Create a database school
o Create a table students with the specifications – ROLLNO integer, STNAME
character(10) in MySQL and perform the following operations:
▪ Insert two records in it
▪ Display the contents of the table
2. Perform all the operations with reference to table Employee through MySQL-Python connectivity.
i)Write a python program to insert 5 records in Employee table. Take these 5 records as an
input from the user (One record at a time). Note the following to establish connectivity
between Python and MySQL: Username is root Password is 12345 The table exists in a
MySQL database named company. The table has five attributes (Emp_ID, Emp_Name,
DOJ, Gender, Salary)
ii) Update name of employee in Employee table whose employee id is ‘E1001’ (Take
name as an input from the user).
3.Write a python program that display first 8 rows fetched from student table of MySQl database
student_dbl
4.Write a python database connectivity program that deletes record from student table of database
that have name = “Meena”
