Assignment Questions for B.Sc.
CA Sem - IV
Paper C10 SQL Practical
Create and use the following tables to answer the following queries in SQL
1. Employee
Column Name Data type Constraint
empno Char(5) Primary key
ename Varchar2(20) Not null
Hiredate Date
Salary Number(10,2)
Dno Char(5)
Designation Varchar2(20)
create table employee(empno char(5) primary key,ename varchar2(20),salary
number(10,2),hiredate date,dno char(5),designation varchar2(20));
insert into employee values('101','mark','20000','20-jan-2010','d1','clerk');
insert into employee values('102','steve','120000','2-dec-2010','d2','general manager');
insert into employee values('103','bill','80000','7-sep-2013','d3','officer');
insert into employee values('104','zuckerberg','70000','29-jun-2015','d1','assitsant
manager');
insert into employee values('105','smith','90000','13-sep-2020','d1','chief manager');
2. Department
Column name Data type Constraint
Dno Char(5) Primary key
Dname Varchar2(20) Not null
Dlocation Varchar2(30)
create table department(dno char(5) primary key,dname varchar2(20) not null,dlocation
varchar2(30));
insert into department values('d1','marketing','ranchi');
insert into department values('d2','accounts','patna');
insert into department values('d3','research','gumla');
insert into department values('d4','production','ranchi');
Write SQL query to
1. Display records of all the employee
select * from employee;
2. Display empno, ename and hiredate of all the employees.
select empno,ename,hiredate from employee;
3. Display records of employees who joined after 1-jan-2010.
select * from employee where hiredate>='1-jan-2010';
4. Display records of employees who joined in the year 2020.
select * from employee where hiredate>='1-jan-2020' and hiredate<='31-dec-2020';
5. Display records of employees in alphabetical order of name.
select * from employee order by ename;
6. Display total employees in each department.
select dno,count(*) from employee group by dno;
7. Display average and maximum salary in each department.
select dno,avg(salary) as "average salary",max(salary) as "maximum salary" from
employee group by dno;
8. Display records of employees whose name begin with 'a' and ends with 'k'.
select * from employee where ename like 'a%k';
9. Display records of employee working in d1,d2 and d3 departments.
select * from employee where dno in ('d1','d2','d3');
10. Display name of employees whose name start with ‘a’ or 'j'
select ename from employee where ename like 'a%' or ename like 'j%';
11. Display empno,ename and dname of employees.
select empno,ename,dname from employee,department where
employee.dno=department.dno;
12. Display empno, ename, and dname of employee working in department located at patna.
select empno,ename,dname from employee,department where
employee.dno=department.dno and dlocation='patna';
13. Display empno, ename, and dname of employee working in department located at patna or
ranchi.
select empno,ename,dname from employee,department where
employee.dno=department.dno and dlocation in ('patna','ranchi');
14. Display records of employees who joined after 1-sep-2020 and working in ranchi location.
select * from employee,department where employee.dno=department.dno and
dlocation='ranchi' and hiredate>='1-sep-2020';
15. Display records of employee who earn more than 50000.
select * from employee where salary>=50000;
16. Display name, designation and salary of employees not in the range 10000 and 50000.
select ename,designation,salary from employee where salary not between 10000 and
50000;
17. Display name of employees working in d1 department in alphabetical order of name
select ename from employee where dno='d1' order by ename;
18. Display name and salary of employees in descending order of salary.
select ename,salary from employee order by salary desc;
19. Display list of designations of employees.
select distinct designation from employee;
20. Explain the following SQL concepts with SQL examples.
A. Alter table and Drop table
B. Group by
C. Subquery
D. Join
E. Index
F. Insert, delete and update command
G.. Primary key and Foreign key constraint
H. Union, intersect and minus
