Output based SQL queries with answers for Class 12 Computer Science | Output based SQL statements with answers for Class 12 IP
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;
ii. Select name from student;
Rahul
Mehul
Manisha
Sujoy
Sonakshi
iii. Select name,class from student;
iv. Select * from student where class=’XII’;
v. Select name from student where gender=’M’;
vi. Select * from student where fee>1100;
vii. Select name,fee,class from student where fee<=1100;
viii. Select * from student order by name;
| Rahul | XII | 1200 | M | 2005-02-01 |
ix. Select name,fee from student order by fee desc;
x. Select * from student where gender=’M’ order by class;
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;
ii. Select * from teacher where incharge=’X’ or incharge=’XII’;
OR
Select * from teacher where incharge in (‘X’,’XII’);
iii Select * from teacher where incharge=’X’ or salary<13000;
iv. Select * from teacher where salary between 13000 and 20000;
OR
Select * from teacher where salary>=13000 and salary<=20000;
v. Select * from teacher where gender=’F’ and salary=16950;
vi. Select distinct incharge from teacher;
vii. Select salary,tname from teacher where salary<15000 or salary>20000;
viii. Select tname, salary, salary*12 from teacher;
ix. Select tname from teacher where incharge=’XII’;
x. Select tname, salary from teacher where dateofjoin<‘2016-10-01’;
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′;
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’;
iii. Select * from hospital where dateofvisit< ‘2018-12-01’ or dateofvisit> ‘2019-12-01’;
iv.Select * from hospital where pname like ‘M%’;
v. Select * from hospital where pname like ‘%m’;
vi. Select * from hospital where pname like ‘%a%’;
vii. Select * from hospital where pname like ‘M%n’;
viii. Select * from hospital where pname like ‘_ _ _ _ _ ‘;
ix. Select * from hospital where fee is null;
x. Select * from hospital where fee is not null;
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;
xi. select designation, count(*), sum(sal) from employee group by designation having count(*)>1;
xii. select designation, sum(sal) from employee group by designation having designation in (‘Clerk’,’Manager’);
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;
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’;
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;
| 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;