Assignment
Class XII (Computer Science-083)
SQL
1) Consider the given tables BOOK and SUPPLIER, write SQL queries given
below:
Table – BOOK
Publishe Pric
B_ID BookName
r e
B01 Python BPB 10
B02 SQL TMH 5
B03 Java BPB 5
B04 C++ Pearson 4
Table: SUPPLIER
S C
u i
p t
p y
li
e
r
N
a
m
e
K
n
o
w
D
l
e
e
l
d
h
g
i
e
H
u
b
T
e
M
c
u
h
m
B
b
o
a
o
i
k
s
R
e
a
d
D
e
e
r
l
s
h
P
i
o
i
n
t
S
m
C
a
h
r
e
t
n
S
n
t
a
o
i
r
e
i Update the price of all books by increasing it by 2 where publisher is TMH.
ii Display publisher-wise average price.
iii Insert a new record into the BOOK table with the following details :
B_ID: B05
BookName: Data Science
Publisher: TMH
Price: 12
iv Display supplier name and book name where city is Delhi.
2) Consider the given tables STUDENT and CLASS, write SQL queries given
below:
Table – STUDENT
S_ID StudentName Stream Marks
S01 Riya Science 85
S02 Aman Commerce 78
S03 Neha Science 92
S04 Kunal Arts 68
Table – CLASS
C_ID ClassTeacher RoomNo S_ID
1 Mr. Sharma 101 S01
2 Ms. Verma 102 S02
3 Mr. Khan 103 S03
4 Ms. Roy 104 S04
i) Display stream-wise average marks.
ii) Update marks by increasing 5 where stream is ‘Science’.
iii) Insert a new record into STUDENT table:
S_ID = S05, StudentName = Rohit, Stream = Commerce, Marks = 81.
iv) Display student name and class teacher where marks are greater than 80.
3) Consider the given tables PRODUCT and DEALER, write SQL queries given
below:
Table – PRODUCT
ProductNam Pric
P_ID Brand
e e
P01 Mouse Logitech 800
P02 Keyboard HP 1200
P03 Printer Canon 6500
P04 Scanner Epson 5000
Table: DEALER
D_ID DealerName City P_ID
1 Tech World Pune P01
2 Digital Hub Delhi P02
3 Office Mart Pune P03
Jaipu
4 Smart Systems P04
r
i Display total number of dealers in each city.
ii Delete all dealer records where the city is Jaipur.
iii Update the city of dealer ‘Tech World’ to Mumbai.
iv Display dealer name and product name where price is greater than 1000.
v Find cities having more than one dealer.
vi Display dealer names who sell HP products.
vii Display average price of products brand-wise.
viii Change dealer city to 'Nagpur' where dealer name is 'Office Mart'.
b) Consider the given tables COURSE and TEACHER, write SQL queries
given below:
Table – COURSE
CourseNam
C_ID Duration Fees
e
C01 Python 6 Months 15000
C02 Java 8 Months 20000
C03 SQL 3 Months 8000
C04 AI 12 Months 40000
Table: TEACHER
T C
e i
a t
c y
h
e
r
N
a
m
e
R D
a e
h l
u h
l i
N M
e u
h m
a b
a
i
D
A
e
m
l
a
h
n
i
C
P h
o e
o n
j n
a a
i
i Display total number of teachers city-wise.
ii Insert a new course ‘Machine Learning’ of duration 11 Months with fees
35000.
iii Display course name and teacher name for courses longer than 6 months.
iv Update course fees by adding 3000 where duration is more than 8 Months.
v Display course name and teacher name sorted by course name.
vi Find teachers who teach courses longer than 10 months.
vii Display maximum course fees.
viii Update course duration to 9 months where course name is 'Java'.
b) Consider the given tables MOVIE and THEATRE, write SQL queries:
Table – MOVIE
MovieNam Languag Fee
M_ID
e e s
M01 Inception English 9
M02 Dangal Hindi 8
M03 Avatar English 9
M04 RRR Telugu 8
Table: THEATRE
T C
h i
e t
a y
t
r
e
N
a
m
e
D
P e
V l
R h
i
I M
N u
m
O b
X a
i
C
i
D
n
e
e
l
p
h
o
i
li
s
H
C
y
a
d
r
e
n
r
i
a
v
b
a
a
l
d
i Display total number of theatres city-wise.
ii Update the city of theatre ‘INOX’ to ‘Pune’.
iii Insert a new movie with the following details into the MOVIE table:
M_ID = M05, MovieName = Interstellar, Language = English, Rating = 9.
iv Display theatre name and movie name with rating greater than 8.
v Display theatre name, movie name, and city sorted by city.
vi Find movies not shown in any theatre.
vii Display average rating language-wise.
viii Update movie language to 'Hindi' where movie name is 'RRR'.