SIMPLE QUERIES IN SQL

Consider table Empl given in solved problems and answer the following questions

1)Write a query to display EName and Sal of employees whose salary is greater than or
equal to 2200 from table Empl.

Ans: Select EName, Sal from Empl Where sal >= 2200 ;

2)Write a query to display details of employees who are not getting commission from
table Empl.

Ans: Select * from Empl Where comm is NULL;

3)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.

Ans:Select ename , sal from Empl Where sal not between 2500 and 4000 ;

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

Ans: Select ename , job , sal from Empl Where job <> "manager" ;

5)Write a query to display the name of employee whose name contains 'A’ as third
alphabet.

Ans: Select ename from Empl Where ename like " _ _A% ";

6)Write a query to display the name of employee whose name contains "T” as the last
alphabet.

Ans: Select ename from Empl Where ename like “%T”;

7)Write a query to display the name of employee whose name contains ‘M’ as first
alphabet ‘L’ as third alphabet.

Ans: Select ename from Empl Where ename like “M_L%” ;

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

Ans: Select * from customers Where rating > 100 or city = “Shimla” ;

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

Ans: Select * from Order Where amt is not null and amt <> 0 ;

Q10) 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. Display the SUPW taken up by the students, whose name starts with 'A'.

Answer:-(1)Select name from studentWhere grade1 = "C" or grade2 = "C" ;(2)Select distinct
GAME from student ;(3)Select Name, SUPW from student.Where name like “A%” ;

Q11. Write SQL commands for the following on the basis of given table SPORTS:

a. Display the names of the students who have grade 'A' in either Game1 or Game2 or
both.
b. Display the names of students who have the same game for both Game1 and
Game2.
c. Display the games taken by the students whose name starts with 'A'.Answer :-(a)
Select name from sportsWhere grade1 = "c" or grade2 = "c" ;(b) Select name from
sports Where game1 = game2 ;(c) Select game1 , game2 from sports 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 clubOrder by datodapp desc ; (c)Select coachname , pay ,
age , pay * 0.15 as bonus from Club ;

13) Give the output of following SQL statements:

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

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

(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)

(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 student1where stream = “Nonmedical” ;b.select name from

Q15. Give the following table:-

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.40

(ii)

SUM(Stipend)

1150.00

(iii)

AVG(Stipend)

475.00

(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.

Answer =(a)Select * from libraryWhere type = ‘PROG’ AND pub = ‘BPB’ ;(b)Select title from
libraryWhere price >130Order by Qty;

(c)Select title from libraryOrder 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 title from MOVWhere price > 20Order by price ;(b)Select title from
MOVOrder by QTY desc ;(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 GRADUATEWhere Rank = 1Order 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.Answer =
(a)Select * from TeacherWhere department = ‘History’ ;(b)Select name from
TeacherWhere sex = ‘F’ and department = ‘Hindi’ ;(c)Select name, dateofjoin from
TeacherOrder by dateofjoin ;

Q. Given the following table:

(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 = errorii =

Round(AvgMark

79

73

75

iii =

Concat (Name,Stream)

RubinaNonmedical

VikasNonmedical

iv =
RIGHT(Stream,2)

al

ce

ce

es

al

al

es

al

al

ce