Scribd
Upload
15 views9 pages

SQL Record Questions(XII CS)

The document contains a series of SQL commands and queries related to managing employee and department data in a database. It includes instructions for displaying databases, creating tables, inserting records, and various select queries to retrieve specific employee information. Additionally, it provides examples of SQL outputs based on the emp and dept tables.

Uploaded by

remiatha1
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
15 views9 pages

SQL Record Questions(XII CS)

The document contains a series of SQL commands and queries related to managing employee and department data in a database. It includes instructions for displaying databases, creating tables, inserting records, and various select queries to retrieve specific employee information. Additionally, it provides examples of SQL outputs based on the emp and dept tables.

Uploaded by

remiatha1
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 9

XII Computer Science SQL Record Questions

1. Display all databases.


show databases;
2. Enter in to the database IES
use IES;
3. Display all tables
show tables;

Table emp:
Table dept:

4. Create table emp.


Create table emp(empno int primary key,empname varchar(20),
job varchar(20), mgr int, hiredate date, sal decimal(7,2), comm
int,deptno int);

5. Insert a record into emp table.


Insert into emp
values(1,’ARUN’,’CLERK’,100,’2020/10/15’,50000,5000,10);

6. Display emp table.


Select * from emp;
7. Display dept table.
Select * from dept;

8. Show the table structure of emp table.


Describe emp; (or) desc emp;

9. Display details of employees in deptno 10 and 20.


Select * from emp where deptno=10 or depno=20;

10. Display the name and salary of salesmen with salary more than
15000.
Select empname,sal from emp where job=’SALESMAN’ and
sal>15000;

11. Display the details of employees whose salary is not equal to 30000
Select * from emp where sal != 30000;

12. Display employee name,salary and annual salary(sal*12).


Select empname,sal,sal*12 as ‘Annual salary’ from emp;

13. Display details of employees whose salary is in the range 20000 to


30000.
Select * from emp where sal between 20000 and 30000;

14.Display the details of salesmen,managers and clerks.


Select * from emp where job in(‘SALESMAN’,’MANAGER’,’CLERK’);

15. Display the details of all employees other than managers and
salesman.’
Select * from emp where job not in(‘MANAGER’,’SALESMAN’);
16. Display the different jobs without duplicate values.
Select distinct job from emp;
17. Display the details of employees whose name starts with ‘S’.
Select * from emp where empname like ‘S%’;

18. Display the details of employees without commission(NULL).


Select * from emp where comm is null;

19. Display the details of employees with commission(NOT NULL).


Select * from emp where comm is NOT NULL;

20. Display the records in the descending order of employee name.


Select * from emp order by empname desc;

21. Display maximum and minimum salary for each job


Select job, max(sal), min(sal) from emp group by job

22. Display total and average salary.


Select sum(sal) , avg(sal) from emp;

23. Display the number of records in the emp table.


Select count(*) from emp;

24. Display the number of employees having commission.


Select count(comm) from emp;

25. Display the job and average salary of each job.


Select job,avg(sal) from emp group by job;

26. Display the deptno and number of employees of those departments


with more than 3 employees.
Select deptno,count(*) from emp group by deptno having
count(*)>3;
27. Display employee name,job,department number, department name
and location of all employees.
Select empname,job,emp.deptno,dname,location from emp,dept
where emp.deptno=dept.deptno;

28. Display employee number,employee name,department number and


department name of all salesmen(Use table alias).
Select empno,empname,e.deptno,dname from emp e,dept d where
e.deptno= d.deptno and job=’SALESMAN’;

29.Add a column to the table dept called Place with data type varchar
Alter table dept add place varchar(20)

30.Drop the column Place from dept


Alter table dept drop Place

31.Increase the employee salary by 10% of all clerks


Update emp set sal=sal+sal*10/100 where job=”CLERK”
32. Delete the record of employee with name ALLEN
Delete from emp where ename=”ALLEN”
33.Display the details of employees whose name starts with letter A
Select * from emp where ename like ‘A%’
34.Display details of employees whose name ends with letter A
Select * from emp where ename like ‘%A’
35.Display all details of employee table and department table using
natural join
Select * from emp natural join dept
Write the output of the following SQL statements based on emp
table and dept table:

1. Select empname from emp where job=’MANAGER’ and deptno=20;

empname
JONES

2. Select max(hiredate), min(hiredate) from emp;

max(hiredate) min(hiredate)
1993-01-12 1990-12-17

3. Select count(distinct job) from emp;

count(distinct job)
5

4. Select empname from emp where job=’CLERK’ order by empname;

empname
ADAMS
JAMES
MILLER
SMITH

5. Select empname from emp where empname like ‘%O%’;

empname
JONES
FORD
SCOTT

6. Select avg(comm) from emp;

avg(comm)
550

7. Select job,max(sal) from emp group by job order by max(sal) desc;

job max(sal)
PRESIDENT 50000
ANALYST 30000
MANAGER 29750
SALESMAN 16000
CLERK 13000

8. Select job,count(*) from emp group by job having min(sal)>20000;

job count(*)
ANALYST 2
MANAGER 3
PRESIDENT 1

9. Select empname,dname from emp,dept where


emp.deptno=dept.deptno and emp.deptno=10;

empname dname
KING SALES
CLARK SALES

10. Select empname,d.deptno, location from emp e,dept d where


e.deptno=d.deptno and d.deptno<20;

empname deptno location


KING 10 BANGALORE
CLARK 10 BANGALORE
******************

You might also like