SQL based questions with answers

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

SQL based questions with answers for class 12 Computer Science

Q1. Write 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 XII 1200 F 2005-09-19
Suman X 950 F 2008-06-16

i. Display all records from table student.
Ans. Select * from student;

ii. List names of all students from table student.
Ans. Select name  from student;

iii. List name and class of all students from table student.
Ans. Select name,class  from student;

iv. List all students studying in class XII.
Ans. Select * from student where class=’XII’;

v. List names of male students .
Ans. Select name  from student where gender=’M’;

vi. Show information of students having fee more than 1000.
Ans. Select * from student where fee>1000;

vii. Show name, fee and class of students having fee below or equal to 1100.
Ans. Select name,fee,class from student where fee<=1100;

viii. View all records in ascending order of student’s name.
Ans. Select * from student order by name;

ix. View name and fee of all students in descending order of fee.
Ans. Select name,fee from student order by fee desc;

x. List male students in descending order of their class.
Ans. Select * from student where gender=’M’ order by class;

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

Relation : Teacher
TName Incharge Salary Gender Dateofjoin
Rahul XII 12000 M 2015-02-11
Mehul XII 12500 M 2014-12-21
Manisha XI 13500 F 2016-10-22
Sujoy XI 21050 M 2016-10-22
Sonakshi XII 21000 F 2015-08-19
Suman X 16950 F 2018-06-26

i. Show information of teachers having salary more than 20000.
Ans. Select * from teacher where salary>20000;

ii. List teachers who are incharge of classes X or XII.
Ans. Select * from teacher where incharge=’X’ or incharge=’XII’;
OR
Select * from teacher where incharge in (‘X’,’XII’);

iii List teachers teaching class X or having salary more than 10000.
Ans. Select * from teacher where incharge=’X’ or salary>10000;

iv. View those records  where salary is between 10000 and 20000 .
Ans. Select * from teacher where salary between 10000 and 20000;
OR
Select * from teacher where salary>=10000 and  salary<=20000;

v. List female teachers having salary 21000 .
Ans. Select * from teacher where gender=’F’ and salary=21000;

vi. List different classes available in above table (no duplicates) .
Ans. Select distinct incharge from teacher;

vii. List salary and names of teachers having salary less than 15000 or more than 20000 .
Ans. Select salary,tname from teacher where salary<15000 or salary>20000;

viii. List name, salary and annual salary(salary*12) of all teachers.
Ans. Select tname, salary, salary*12  from teacher;

ix. List name of all teachers teaching class XII.
Ans. Select tname from teacher where incharge=’XII’;

x. List name and  salary of all teachers who joined before ‘2016-10-01’.
Ans. Select tname, salary from teacher where dateofjoin<‘2016-10-01’;

Output based SQL statement with answers

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. Show information of  patients who visited  after ‘2020-01-01’.
Ans. Select * from hospital where dateofvisit>’2020-01-01′;

ii. Show information of  patients who visited  between  ‘2018-12-01’ and ‘2019-12-01’.
Ans. 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. Show information of  patients who visited  before  ‘2018-12-01′ or after 2019-12-01’.
Select * from hospital where dateofvisit< ‘2018-12-01’ or dateofvisit> ‘2019-12-01’;

iv. List those patients whose name starts with alphabet ‘M’.
Select * from hospital where pname like ‘M%’;

v. List those patients whose name ends with alphabet ‘m’.
Select * from hospital where pname like ‘%m’;

vi. List those patients whose name contains alphabet ‘a’.
Select * from hospital where pname like ‘%a%’;

vii. List those patients whose name starts with alphabet ‘M’ and ends with ‘n’.
Select * from hospital where pname like ‘M%n’;

viii. List those patients whose name contains 4 characters.
Select * from hospital where pname like ‘_ _ _ _ ‘;

ix. List those patients whose fee is null.
OR
List those patients who have not been charged any fee.
Select * from hospital where fee is null;

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



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

Relation : Emp
Empid EmpName Department Salary Gender
101 Mahesh Finance 32000 M
303 Vijay HR 42500 M
401 Mansha Finance 31500 F
603 Kamal Computer 32150 M
604 Vandana HR 42000 F
631 Sujata Finance 39500 F

i. Increase salary of all employees by 1000.
Ans. update emp set salary=salary+1000;

ii. Decrease value of EmpID of male employees by 10.
Ans. update emp set empid=empid-10 where gender=’M’;

iii. Department of  ‘Mahesh’ should be updated as ‘HR’.
Ans. update emp set department=’HR’ where empname=’Mahesh’;

iv. Name should be updated as ‘Manish Saini’ and salary as 50000 for employee id 603.
Ans. update emp set empname=’Manish Saini’, salary=50000 where empid=603;

v. ‘Finance’ department should be updated as ‘Fin’.
Ans. update emp set department=’Fin’ where department=’Finance’;

vi. Delete records of female employees.
Ans. delete from emp where gender=’F’;

vii. Delete all records of Emp table.
Ans. delete from emp;

viii. Insert a new record with data as 700,’Raman’,’HR’,40000,’M’
Ans. insert into emp values(700,’Raman’,’HR’,40000,’M’);

ix. Add a new field Phoneno of integer type to table.
Ans. Alter table emp add phoneno int;

x. Set the size of EmpName field to 40.
Ans. Alter table emp modify empname varchar(40);

Output based SQL statement with answers

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

Relation : Employee
id Name Designation Sal
101 Naresh Clerk 32000
102 Ajay Manager 42500
103 Manisha Clerk 31500
104 Komal Advisor 32150
105 Varun Manager 42000
106 NULL Clerk 32500

i. Count number of records in the table.
Ans. select count(*) from employee;

ii. Count number of names in the table.
Ans. select count(name) from employee;

iii. Count number of designations.
Ans. select count(distinct designation) from employee;

iv. Count number of clerks.
Ans. select count(*) from employee where designation=’Clerk’;

v. Find sum of salaries of all employees.
Ans. select sum(sal) from employee;

vi. Find maximum salary in the table.
Ans. select max(sal) from employee;

vii. Find minimum salary in the table.
Ans. select min(sal) from employee;

viii. Find average salary in the table.
Ans. select avg(sal) from employee;

ix. Find minimum and maximum salary of Managers.
Ans. select max(sal),min(sal) from employee where designation=’Manager’;

x. Display number of records for each individual designation.
Ans. select designation, count(*) from employee group by designation;

xi. Display number of records along with sum of salaries for each individual designation where number of records are more than 1.
Ans. select designation, count(*), sum(sal) from employee group by designation having count(*)>1;

xii. Display sum of salaries of clerks and managers
Ans. select designation, sum(sal) from employee group by designation having designation in (‘Clerk’,’Manager’);

Output based SQL statement with answers

Q6. Write SQL queries on the basis of following tables.

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
102 45000 9888000909
103 33000 8008078787
104 40000 7009876543

i. View Name and salary of all employees.
Ans. 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. List Name, designation and salary of all Clerks.
Ans. 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. Display id, Name and salary of all employees.
Ans. 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;

iv. Display Name and salary of all employees having salary more than 40000
Ans. 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;

Output based SQL statement with answers

 

Spread the love
Lesson tags: 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
Spread the love