Output based SQL queries with answers

You must first complete SQL commands For class XII CBSE before viewing this Lesson



Q1. Write output of the SQL queries on the basis of following table.

Relation :  Student
Name Class Fee Gender DOB
Rahul XII 1200 M 2005-02-01
Mehul XII 1200 M 2004-12-11
Manisha XI 1050 F 2006-10-12
Sujoy XI 1050 M NULL
Sonakshi X 1200 F 2005-09-19

i. Select * from student;

Rahul XII 1200 M 2005-02-01
Mehul XII 1200 M 2004-12-11
Manisha XI 1050 F 2006-10-12
Sujoy XI 1050 M NULL
Sonakshi X 1200 F 2005-09-19

ii. Select name  from student;

Rahul
Mehul
Manisha
Sujoy
Sonakshi

iii. Select name,class from student;

Rahul XII
Mehul XII
Manisha XI
Sujoy XI
Sonakshi X

iv. Select * from student where class=’XII’;

Rahul XII 1200 M 2005-02-01
Mehul XII 1200 M 2004-12-11

v. Select name  from student where gender=’M’;

Rahul
Mehul
Sujoy

 

 

 

vi. Select * from student where fee>1100;

Rahul XII 1200 M 2005-02-01
Mehul XII 1200 M 2004-12-11
Sonakshi X 1200 F 2005-09-19

 

 

 

vii. Select name,fee,class from student where fee<=1100;

Manisha 1050 XI
Sujoy 1050 XI

viii. Select * from student order by name;

Mehul XII 1200 M 2004-12-11
Manisha XI 1050 F 2006-10-12
Rahul XII 1200 M 2005-02-01
Sonakshi X 1200 F 2005-09-19
Sujoy XI 1050 M NULL

ix. Select name,fee from student order by fee desc;

Rahul 1200
Mehul 1200
Manisha 1050
Sujoy 1050
Sonakshi 1200

x. Select * from student where gender=’M’ order by class;

Mehul XII 1200 M 2004-12-11
Rahul XII 1200 M 2005-02-01
Sujoy XI 1050 M NULL

Q2. Write output of  SQL queries on the basis of following table.

Relation : Teacher
TName Incharge Salary Gender Dateofjoin
Suresh XII 12000 M 2015-02-11
Mohnish XII 12500 M 2014-12-21
Manisha XI 13500 F 2016-10-22
Munish XI 21050 M 2016-10-22
Suman X 16950 F 2018-06-26

i. Select * from teacher where salary>15000;

Munish XI 21050 M 2016-10-22
Suman X 16950 F 2018-06-26

ii. Select * from teacher where incharge=’X’ or incharge=’XII’;
OR
Select * from teacher where incharge in (‘X’,’XII’);

Suresh XII 12000 M 2015-02-11
Mohnish XII 12500 M 2014-12-21
Suman X 16950 F 2018-06-26

iii Select * from teacher where incharge=’X’ or salary<13000;

Suresh XII 12000 M 2015-02-11
Mohnish XII 12500 M 2014-12-21
Suman X 16950 F 2018-06-26

iv. Select * from teacher where salary between 13000 and 20000;
OR
Select * from teacher where salary>=13000 and  salary<=20000;

Manisha XI 13500 F 2016-10-22
Suman X 16950 F 2018-06-26

v. Select * from teacher where gender=’F’ and salary=16950;

Suman X 16950 F 2018-06-26

vi. Select distinct incharge from teacher;

XII
XI
X

vii.  Select salary,tname from teacher where salary<15000 or salary>20000;

12000 Suresh
12500 Mohnish
13500 Manisha
21050 Munish

viii.  Select tname, salary, salary*12  from teacher;

Suresh 12000 144000
Mohnish 12500 150000
Manisha 13500 162000
Munish 21050 252600
Suman 16950 203400

ix. Select tname from teacher where incharge=’XII’;

Suresh XII 12000 M 2015-02-11
Mohnish XII 12500 M 2014-12-21

x. Select tname, salary from teacher where dateofjoin<‘2016-10-01’;

Suresh 12000
Mohnish 12500

Q3. Write SQL queries on the basis of following table.

Relation : Hospital
PName Fee Gender Dateofvisit
Ramesh 200 M 2020-02-11
Mohnish 250 M 2019-12-22
Muskan 350 F 2019-11-22
Sunil 250 M 2018-12-02
Sonam null F 2019-01-19
Sahil 16950 F 2019-02-26

i. Select * from hospital where dateofvisit>’2020-01-01′;

Ramesh 200 M 2020-02-11

ii. Select * from hospital where dateofvisit between ‘2018-12-01’ and ‘2019-12-01’;
OR
Select * from hospital where dateofvisit>= ‘2018-12-01’ and dateofvisit<= ‘2019-12-01’;

Muskan 350 F 2019-11-22
Sunil 250 M 2018-12-02
Sonam null F 2019-01-19
Sahil 16950 F 2019-02-26

iii. Select * from hospital where dateofvisit< ‘2018-12-01’ or dateofvisit> ‘2019-12-01’;

Ramesh 200 M 2020-02-11
Mohnish 250 M 2019-12-22
Muskan 350 F 2019-11-22

iv.Select * from hospital where pname like ‘M%’;

Mohnish 250 M 2019-12-22
Muskan 350 F 2019-11-22

v. Select * from hospital where pname like ‘%m’;

Sonam null F 2019-01-19

vi. Select * from hospital where pname like ‘%a%’;

Ramesh 200 M 2020-02-11
Muskan 350 F 2019-11-22
Sonam null F 2019-01-19

vii. Select * from hospital where pname like ‘M%n’;

Muskan 350 F 2019-11-22

viii. Select * from hospital where pname like ‘_ _ _ _ _ ‘;

Sunil 250 M 2018-12-02
Sonam null F 2019-01-19
Sahil 16950 F 2019-02-26

ix. Select * from hospital where fee is null;

Sonam null F 2019-01-19

x. Select * from hospital where fee is not null;

Ramesh 200 M 2020-02-11
Mohnish 250 M 2019-12-22
Muskan 350 F 2019-11-22
Sunil 250 M 2018-12-02
Sahil 16950 F 2019-02-26



Q4. Write output of following SQL queries on the basis of following table.

Relation : Employee
id Name Designation Sal
101 Naresh Clerk 30000
102 Ajay Manager 40000
103 Manisha Clerk 30000
104 Komal Advisor 32000
105 Varun Manager 42000
106 NULL Clerk 33000

i. select count(*) from employee;

6

ii  select count(name) from employee;

5

iii. select count(distinct designation) from employee;

3

iv. select count(*) from employee where designation=’Clerk’;

3

v. select sum(sal) from employee;

207000

vi. select max(sal) from employee;

42000

vii.  select min(sal) from employee;

30000

viii. select avg(sal) from employee;

34500

ix. select max(sal),min(sal) from employee where designation=’Manager’;

42000          40000

x. select designation, count(*) from employee group by designation;

Clerk 3
Manager 2
Advisor 1

xi.  select designation, count(*), sum(sal) from employee group by designation having count(*)>1;

Clerk 3 93000
Manager 2 82000
Advisor 1 32000

xii. select designation, sum(sal) from employee group by designation having designation in (‘Clerk’,’Manager’);

Clerk 93000
Manager 82000

Q5. Write output of following SQL queries on the basis of tables as given.

Relation : Employee
id Name Designation
101 Naresh Clerk
102 Ajay Manager
103 Manisha Clerk
104 Komal Advisor

 

Relation : Details
id Salary PhoneNo
101 32000 9898090909
103 33000 8008078787
104 40000 7009876543

i. select name,salary from employee, details where employee.id=details.id;
OR
select name,salary from employee E, details D where E.id=D.id;

Naresh 32000
Manisha 33000
Komal 40000

ii. select name,designation salary from employee, details where employee.id=details.id and designation=’Clerk’;
OR
select name,designation salary from employee E1, details D1 where E1.id=D1.id and designation=’Clerk’;

Naresh Clerk 32000
Manisha Clerk 33000

iii. Select employee.id, name,salary from employee, details where employee.id=details.id;
OR
select E1.id, name,salary from employee E1, details D1 where E1.id=D1.id;

101 Naresh 32000
103 Manisha 33000
104 Komal 40000

iv. select name, salary from employee, details where employee.id=details.id and salary<40000;
OR
select name, salary from employee E, details D where E.id=D.id and salary<40000;

Naresh Clerk 32000
Manisha Clerk 33000



Lesson tags: find output of sql queries, output based sql commands, sql based questions, sql question answers class 11 informatics practices, sql question answers class 12 computer science, sql questions and answers class xi, sql questions and answers class xii, sql questions and anwers
Back to: CBSE class 12 Computer Science notes