Scribd
Upload
63 views

Practice Test SQL

Uploaded by

avinashk9548
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
63 views

Practice Test SQL

Uploaded by

avinashk9548
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 4

PRACTICE TEST ( Topic : MySQL

CLASS 12 SUB- COMPUTER SCIENCE(083)


1. What SQL statement do we use to display the record of all students whose last (a) SELECT
name contains 5 letters ending with “A”?
(a)* FROM STUDENTS WHERE LNAME LIKE ‘_ _ _ _A’;
(b) SELECT * FROM STUDENTS WHERE LNAME LIKE ‘ _ _ _ _ _A%’;
(c) SELECT * FROM STUDENTS WHERE LNAME LIKE ‘ ????A’;
(d) SELECT * FROM STUDENTS WHERE LNAME LIKE ‘%A’;

2. Write the outputs of the SQL queries (a) to (c) based on the relation Furniture

No Itemname Type Dateofstock Price Discount


1 White lotus Double Bed 23/02/2002 30000 25
2 Pink feather Baby Cot 20/01/2002 7000 20
3 Dolphin Baby Cot 19/02/2002 9500 20
4 Decent Office Table 01/01/2002 25000 30
5 Comfort Zone Double Bed 12/01/2002 25000 25
6 Donald Baby Cot 24/02/2002 6500 15
7 Royal finish Office Table 20/02/2002 18000 30
8 Royal tiger Sofa 22/02/2002 31000 30
9 Econo sitting Sofa 13/12/2001 9500 25
10 paradise Dining Table 19/02/2002 11500 25
11 Wood Comfort Double Bed 23/03/2003 25000 25
12 Old Fox Sofa 20/02/2003 17000 20
13 Micky Baby Cot 21/02/2003 7500 15

(a) SELECT Itemname FROM Furniture WHERE Type="Double Bed";


(b) SELECT MONTHNAME(Dateofstock) FROM Furniture WHERE Type="Sofa";
(c) SELECT Price*Discount FROM Furniture WHERE Dateofstock>31/12/02;

3. Consider the following table GAMES

GCode GameName Number PrizeMoney ScheduleDate


101 Carom Board 2 5000 23‐Jan‐2004
102 Badminton 2 12000 12‐Dec‐2003
103 Table Tennis 4 8000 14‐Feb‐2004
105 Chess 2 9000 01‐Jan‐2004
108 Lawn Tennis 4 25000 19‐Mar‐2004
Write the output for the following queries :
(i) SELECT COUNT(DISTINCT Number) FROM GAMES;
(ii) SELECT MAX(ScheduleDate),MIN(ScheduleDate) FROM GAMES;
(iii) SELECT SUM(PrizeMoney) FROM GAMES;
4. Based on given table “DITERGENTS” answer following questions.
PID PName Price Category Manufacturer
1 Nirma 40 Detergent Powder Nirma Group
2 Surf 80 Detergent Powder HL
3 Vim Bar 20 Disc washing Bar HL
4 Neem Face Wash 50 Face Wash Himalaya
a. Write SQL statement to display details of all the products not manufactured by HL.
b. Write SQL statement to display name of the detergent powder manufactured by HL.
c. Write SQL statement to display the name of the Product whose price is more than 0.5
hundred.

d. Write SQL statement to display name of all such Product which start with letter ‘N’

5. In SQL, what is the error in following query :

SELECT NAME,SAL,DESIGNATION WHERE DISCOUNT=NULL;

6. Consider the following table Sports and write the outputs of queries from i) to iv) based
on it.

Table:Sports

Rno Class Name Game Grade Marks


10 7 Sameer Cricket B 61
11 8 Sujit Tennis A 75
12 7 Kamal Swimming B 60
13 9 Veena Tennis C 49
15 10 Arpit Cricket A 78

i) Select Game, count(*) From Sports Group by Game;

ii) Select max(Marks), min(Marks) From Sports;

iii) Select Distinct Game From Sports;

iv) Select Name , Game,Marks from Sports where marks between 60 and 75;

7. Write the outputs of the SQL queries (i) to (iii) based on the relations Doctor and Place
given below:
Table : Doctor
DID DName Age Department Date_of_join Salary Gender
1 Rakesh 34 General 10/01/2017 120000 M
2 Parveen 31 Ortho 24/03/2008 200000 F
3 Satyajeet 32 ENT 12/12/2016 300000 M
4 Yogita 35 Heart 01/07/2015 400000 F
5 Chirag 42 General 05/09/2007 250000 M
6 Vijay 50 ENT 27/06/2008 300000 M
7 Kamlesh 44 Ortho 25/02/2017 210000 M
8 Seema 33 Heart 31/07/2018 200000 F

Table : Place
PID Department City
1 General Ajmer
2 Heart Udaipur
3 Ortho Jodhpur
4 ENT Jaipur

i. SELECT Department, count(*) FROM Doctor GROUP BY Department;


ii. SELECT Max(Salary),Min(salary),Max(Date_of_join ) FROM Doctor;

iii. SELECT Doctor.Dname, Doctor.Department, Place.city


FROM Doctor, Place WHERE Doctor.Department = Place.Department AND
Place.city in (“Jaipur”,”Jodhpur”);

8. A school KV is considering to maintain their eligible students’ for scholarship’s data


using SQL to store the data. As a database administer, Abhay has decided that :

• Name of the database - star

• Name of the table - student

• The attributes of student table as follows:

SNo - numeric

Name – character of size 20


Stipend - numeric

Stream – character of size 20

AvgMark – numeric

Grade – character of size 1

Class – character of size 3

Table ‘student’

SNo Name Stipend Stream AvgMark Grade Class

1 Karan 400.00 Medical 78.5 B 12B


2 Divakar 450.00 Commerce 89.2 A 11C
3 Divya 300.00 Commerce 68.6 C 12C
4 Arun 350.00 Humanities 73.1 B 12C
5 Sabina 500.00 Nonmedical 90.6 A 11A
6 John 400.00 Medical 75.4 B 12B
7 Robert 250.00 Humanities 64.4 C 11A
8 Rubina 450.00 Nonmedical 88.5 A 12A
9 Vikas 500.00 Nonmedical 92.0 A 12A
10 Mohan 300.00 Commerce 67.5 C 12C

i. Which column is suitable to be a primary key attribute.

ii. What is the degree and cardinality of table student.

iii. Write the statement to:

a) Write query to create table.

b) Write query to change the grade of karan from ‘B’ to ‘A’

iv) Write the statement to:

a) Display the details of student in ascending order of name.

b) Add a new column ADDRESS in the table with data type as varchar with 30 characters.

You might also like