1

Simple Queries in SQL Class 11 Computer

science /Information practices Solution
Q1 = Write a query to display EName and Sal of employees whose salary is
greater than or equal to 2200 from table Empl.
Table : Empl

Answer: -

Select EName, Sal from Empl

Where sal >= 2200 ;

Q2= write a query to display details of employees who are not getting
commission from table Empl.
Answer =

Select * from Empl

Where comm is NULL;

Q3= Write a query to display employee name and salary of those employee
who don't have there salary in the range of 2500 to 4000.

Answer :-

Select ename , sal from Empl

Where sal not between 2500 and 4000 ;

Q4 = Write a query to display the name, job title and salary of employee who
do not have manager.

Answer :-

Select ename , job , sal from Empl

Where job <> manager ;
 2

Q5= Write a query to display the name of employee whose name contains
'A' as third alphabet.

Answer =

Select ename from Empl

Where ename like " _ _A% ";

Q6 = Write a query to display the name of employee whose name contains T

as the last alphabet.

Answer =

Select ename from Empl

Where ename like “%T”;

Q7= Write a query to display the name of employee whose name contains
'M' as first alphabet 'L' as third alphabet.

Answer =

Select ename from Empl

Where ename like “M_L%” ;

Q8 = Write a query on the customers table whose output will exclude all
customers with a rating <= 100, unless they are located in Shimla.

Q9 = Write a query that selects all orders (Order table) except those with
zeros or NULLS in the amt field.

Answer =

Select * from Order

Where amt is not null and amt <> 0 ;

Q 10 =Write SQL commands for the following on the basis of given table
STUDENT:

1= Display the names of the students who are getting a grade 'C' in either
GAME or SUPW.

2 =Display the different games offered in the school.

 3

3 = Display the SUPW taken up by the students, whose name starts with 'A'.

Answer:-

(1)

Select name from student

Where grade1 = "C" or grade2 = "C" ;

(2)

Select distinct GAME from student ;

(3)

Select Name, SPUW from student.

Where name like “A%” ;

Q12= Write SQL commands for the following on the basis of given table
CLUB :

(a) To show all information about the swimming coaches in the club.

(b) To list names of all coaches with their date of appointment (DATOFAPP) in
descending order.

(c) To display a report, showing coachname, pay, age and bonus (15% of
pay) for all the coaches.

Answer =

(a). Select * from club

Where sports = “SWIMMING” ;

(b). Select coachname , datofapp from club

Order by datodapp desc ;

(c). Select coachname , pay , age , pay * 0.15 as bonus from Club ;

Q13. Table: CLUB Give the output of following SQL statements:

(i) SELECT COUNT (DISTINCT SPORTS) FROM Club;

(ii) SELECT MIN(Age) FROM CLUB WHERE Sex = 'F';
 4

(iii) SELECT AVG(Pay) FROM CLUB WHERE Sports = 'KARATE' ;

(iv) SELECT SUM(Pay) FROM CLUB WHERE Datofapp > '31/01/98'

Answer :-

(i)
COUNT
(DISTINCT
SPORTS)
4

(ii)
MIN(Age)
34

(iii)
AVG(Pay)
1100

(iv)
SUM(Pay)
7800

Q14= Write SQL commands for the following on the basis of given table
STUDENT1 :

(a) Select all the Nonmedical stream students from STUDENT1.

(b) List the names of those students who are in class 12 sorted by Stipend.

(c) List all students sorted by AvgMark in descending order.

(d) Display a report, listing Name, Stipend, Stream and amount of stipend
received in a year assuming that the Stipend is paid every month.

Answer =

a. select * from student1

where stream = “Nonmedical” ;

b. select name from student1

where class like “12%”

order by stipend ;

c. select * from student1

 5

order by AvgMark desc;

d. select name , stipend , stream , stipend * 12 as stipend_in_year from student1 ;

Q15. Give the following table:- Table: STUDENT

Give the output of following SQL statements :
(i) SELECT MIN(AvgMark) FROM STUDENT WHERE AvgMark < 75 ;
(ii) SELECT SUM(Stipend) FROM Student WHERE Grade = 'B' ;
(iii) SELECT AVG(Stipend) FROM Student WHERE Class = '12A' ;
(iv) SELECT COUNT(DISTINCT) FROM Student ;

Answer :-

(i)
MIN(AvgMark
64.4

(ii)
SUM(Stipend)
1150

(iii)
AVG(Stipend)
475.0
(iv)
It will give an error.

Q16 = Write SQL commands for the following on the basis of given table :
(a) Select all the PROG type published by BPB from Library.
(b) Display a list of all books with Price more then 130 and sorted by Qty.
(c) Display all the books sorted by Price in ascending order.
 6

Answer =

(a) Select * from library

Where type = ‘PROG’ AND pub = ‘BPB’ ;

(b) Select * from library

Where price >130

Order by Qty;

(c ) Select * from library

Order by price ;

Q17. Write SQL commands for the following on the basis of given table MOV :
(a) Display a list of all movies with Price over 20 and sorted by Price.
(b) Display all the movies sorted by QTY in decreasing order.
(c) Display a report listing a movie number, current value and replacement
value for each movie in the above table. Calculate the replacement value for
all movies as QTY * Price * 1.15 .

Answer =

(a) Select * from MOV

Where price > 20

Order by price ;

(b) Select * from MOV

Order by QTY desc ;

 7

(c) Select no, price, qty* price * 1.15 as replacement_value from MOV ;

Q18 = Write SQL commands for the following on the basis of given table
GRADUATE :

(a) List the names of those students who have obtained Rank 1 sorted by
NAME.

(b) Display a report, listing NAME, STIPEND, SUBJECT and amount of stipend
received in a year assuming that the STIPEND is paid every month.

Answer =

(a) Select name from GRADUATE

Where Rank = 1

Order by NAME ;

(b) Select name ,stipend , subject , stipend * 12 as stipend_in_year from graduate ;

Q19= Write SQL commands for the following on the basis of given table
relation Teacher:

(a) To show all information about the teacher of history department .

(b) To list the names of female teachers who are in Hindi department .

(c) To list names of all teachers with their date of joining in ascending order.
 8

Answer =

(a) Select * from Teacher

Where department = ‘History’ ;

(b) Select name from Teacher

Where sex = ‘F’ and department = ‘Hindi’ ;

(c) Select name, dateofjoin from Teacher

Order by dateofjoin ;

Q20= Given the following table :Give the output of following SQL statement :

(i) =SELECT TRUNCATE(AvgMark) FROM Student1 WHERE AvgMark < 75;

(ii) =SELECT ROUND(AvgMark) FROM Student1 WHERE Grade = 'B';
(iii)= SELECT CONCAT(Name, Stream) FROM Student1 WHERE Class = '12A';
(iv) = SELECT RIGHT(Stream, 2) FROM Student1;

Answer
i = error

ii =
Round(AvgMark
79
73
75

iii =

Concat (Name,Stream)
RubinaNonmedical
VikasNonmedical

iv =

RIGHT(Stream,2)
al
ce
ce
es
al
 9

al
es
al
al
ce