Q .Consider the table student given below where ID is primary key.
Table:Student
ID Name City Stream Percentage
120 Amar Kamar Lucknow Science 85
1
120 Dimpy Singh Agra Art 80
2
120 Esha Chataurvedi Kanpur Art 75
3
120 Himanshu Sharma Agra Art 87
4
120 Shyam Yadav Lucknow Science 90
5
i) Display the student name and stream who lives in lucknow.
->mysql> select Name,Stream from student where City='Lucknow';
Name Stream
Amar Kumar Science
Shyam Yadav Science 2 rows in set (0.58 sec)
ii) Display the student ID and name whose percentage is greater than 80.
->select ID,Name from student where percentage>80;
ID Name
1201 Amar Kumar
1204 Himanshu
Sharma
3 rows in set (0.10 sec)
1205 Shyam Yadav
iii) Count the numbers of student who opted Science Stream.
->mysql> select count(stream) from student where stream='Science';
Count(Stream
)
2 1 row in set (0.10 sec)
iv) Display the student names who is from Art Stream and whose percentage is
less than 80.
->mysql> select Name from student where Stream='Art' and Percentage<80;
Name
Esha Chaturvedi
1 row in set (0.44 sec)
v) Display all the details of those students whose name start with ‘E’.
->mysql> select * from student where Name like 'E%';
ID Name City Stream Percentage
1203 Esha Chaturvedi Kanpur Art 75
1 row in set
(0.53 sec)
vi) Delete the details of the student whose ID 1205.
->mysql> delete from student where ID=1205;
Query OK, 1 row affected (0.05 sec)
vii) Add new column to a table date of birth of datatype date.
->mysql> alter table Student add date_of_birth date;
Query OK, 0 rows affected (2.47 sec)
Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE Student
2. Consider the following table. Write commands in SQL for (i) to (vii)
i) Create the following table teacher.
->mysql> CREATE TABLE Teacher (TID int primary key,TName varchar(50) not
null, TSub varchar(50) not null, TClass int, TSalary int);
Query OK, 0 rows affected (0.14 sec)
ii) Insert the following records.
TID TName TSub TClass TSalary(>5000)
2021 Upendra Kumar English 12 20000
2022 Rajender Singh Hindi 10 25000
2023 Neelam Gupta Hindi 12 30000
2024 Ritu Agarwal Science 12 35000
2025 Shyam Yadav Mathemetics 10 27000
2026 Ravi Singh Mathemetics 12 32000
->mysql> Insert into Teacher (TID, TName, TSub, TClass, TSalary) VALUES
(2021, 'Upendra Kumar', 'English', 12, 20000),
(2022, 'Rajender Singh', 'Hindi', 10, 25000),
(2023, 'Neelam Gupta', 'Hindi', 12, 30000),
(2024, 'Ritu Agarwal', 'Science', 12, 35000),
(2025, 'Shyam Yadav', 'Mathematics', 12, 27000),
(2026, 'Ravi Singh', 'Mathematics', 12, 32000);
Query OK, 6 rows affected (0.19 sec)
Records: 6 Duplicates: 0 Warnings: 0
iii) To display the teacher name and salary of all teacher whose department in
hindi.
TName TSalary
Rajender Singh 25000
Neelam Gupta 30000
->mysql> select TName, Tsalary from Teacher where TSub=’hindi’;
2 rows in set (0.05 sec)
iv) To display the name of those teachers who teach in class 10.
->mysql> select TName from Teacher where TClass = 10;
TName
Rajender Singh
Shyam Yadav 2 rows in set (0.00 sec)
v) To display the ID and Name of those teachers who teach in class 12 and their
salary is greater than 20000.
->mysql>select TID, TName from Teacher where TClass = 12 and TSalary >
20000;
TID TName
2023 Neelam Gupta
2024 Ritu Agarwal
2026 Ravi Singh
3 rows in set (5.02 sec)
vi) To Display the details of teachers whose salary is in the range 20000 and
30000.
->mysql> SELECT * from Teacher where TSalary between 20000 and 30000;
TID TName TSub TClass TSalary
2021 Upendera Kumar English 12 20000
2023 Rajender Singh Hindi 10 25000
2023 Neelam Gupta Hindi 12 30000
2025 Shyam Yadav Mathematics 10 27000
4 rows in set (2.02 sec)
vii) Increase the salary of all teachers by 2000.
->mysql> update Teacher set TSalary = TSalary + 2000;
Query OK, 6 rows affected (0.58 sec)
Rows matched: 6 Changed: 6 Warnings: 0
3. Consider the following table Movie. Write commands in SQL for (i) to (vii).
MovieID MovieName Category ReleaseDate ProductionCost BusinessCost
001 Hindi_Movie Musical 2018-04-23 124500 130000
002 Tamil_Movie Action 2016-05-17 112000 118000
003 English_Movie Horror 2017-08-06 245000 360000
004 Bengali_Movie Adevntur 2017-01-04 72000 100000
e
005 Telugu_Movie Action - 100000 -
006 Punjabi_Movi Comedy - 30500 -
e
i) Retrieve movies' information without mentioning their column names .
MovieID MovieName Category ReleaseDate ProductionCost BusinessCost
1 Hindi_Movie Musical 2018-04-23 124500 130000
2 Tamil_Movie Action 2016-05-17 112000 118000
3 English_Movie Horror 2017-08-06 245000 360000
4 Bengali_Movie Adventur 2017-01-04 72000 100000
e
5 Telugu_Movie Action NULL 100000 NULL
6 Punjabi_Movi Comedy NULL 30500 NULL
e
->mysql> select * from movie;
6 rows in set (0.52 sec)
ii) List business done by the movies showing only MovieID, MovieName amd
BusinessCost.
->mysql> select MovieID, MovieName, BusinessCost from Movie;
MovieID MovieName BusinessCost
1 Hindi_Movie 130000
2 Tamil_Movie 118000
3 English_Movie 360000
4 Bengali_Movie 100000
5 Telugu_Movie NULL 6 rows in set (0.57 sec)
6 Punjabi_Movie NULL iii) List the different categories of
movies.
->mysql> select distinct Category from Movie;
Category
Musical
Action
Horror
Adventure 5 rows in set (0.23 sec)
Comedy
iv) Find the net profit of each movie showing its ID, Name, and Net Profit.
(Hint: Net Profit = BusinessCost - ProductionCost)
->mysql> select MovieID, MovieName, (BusinessCost - ProductionCost) as
NetProfit from Movie;
MovieID MovieName NetProfit
1 Hindi_Movie 5500
2 English_Movie 6000
3 Tamil_Movie 115000
4 Bengali_Movie 28000
5 Telugu_Movie NULL 6 rows in set (0.24 sec)
6 Punjabi_Movie NULL
v) List all movies with ProductionCost greater than 80,000 and less than
1,25,000 showing ID, Name, and ProductionCost.
->mysql> select MovieID, MovieName, ProductionCost from Movie where
ProductionCost > 80000 and ProductionCost < 125000;
MovieID MovieName ProductionCost
1 Hindi_Movie 124500
2 Tamil_Movie 112000
5 Telgu_Movie 100000 3 rows in set (0.11 sec)
vi) List all movies which fall in the
category of Comedy or Action.
MovieI MovieName Category ReleaseDat ProductionCost BusinessCost
D e
2 Tamil_Movie Action 2016-05-17 112000 118000
5 Telugu_Movie Action NULL 100000 NULL
6 Punjabi_Movi Comedy NULL 30500 NULL
e
->mysql> select * from Movie where category in ('Comedy', 'Action');
3 rows in set (0.18 sec)
vii) List the movies which have not been released yet.
MovieI MovieName Category RealeaseDate ProductionCost BusinessCost
D
5 Telugu_Movie Action NULL 100000 NULL
6 Punjabi_Movi Comedy NULL 30500 NULL
e
->mysql> select * from Movie where ReleaseDate is null;
2 rows in set (0.50 sec)
