Q1. Create table employee.
SQL> create table employee
2 (no int,name char(6),salary int,zone char(6),age int,grade char,dept int);
Table created.
Q2. Insert tuples in table employee.
SQL> insert into employee
2 values(1,'Mukul',30000,'West',28,'A',10);
1 row created.
SQL> insert into employee
2 values(2,'Kriti',35000,'Centre',30,'A',10);
1 row created.
SQL> insert into employee
2 values(3,'Naveen',32000,'West',40,NULL,20);
1 row created.
SQL> insert into employee
2 values(4,'Uday',38000,'North',38,'C',30);
1 row created.
SQL> insert into employee
2 values(5,'Nupur',32000,'East',26,NULL,20);
1 row created.
SQL> insert into employee
2 values(6,'Moksh',37000,'South',28,'B',10);
1 row created.
SQL> insert into employee
2 values(7,'Shelly',36000,'North',26,'A',30);
1 row created.
Q3. To view contents of table employee.
SQL> select *
2 from employee;
NO NAME SALARY ZONE AGE G DEPT HIREDATA
------
1 Mukul 30000 West 28 A 10
2 Kriti 35000 Centre 30 A 10
3 Naveen 32000 West 40 20
4 Uday 38000 North 38 C 30
5 Nupur 32000 East 26 20
6 Moksh 37000 South 28 B 10
7 Shelly 36000 North 26 A 30
7 rows selected.
Q4 Create table department.
SQL> create table department
2 (dept int,dname char(8),minsal int,maxsal int,hod int);
Table created.
Q5 Insert tuples in table department.
SQL> insert into department
2 values(10,'Sales',25000,32000,1);
1 row created.
SQL> insert into department
2 values(20,'Finance',30000,50000,5);
1 row created.
SQL> insert into department
2 values(30,'Admin',25000,40000,7);
1 row created.
Q6. To view contents of table employee.
SQL> select *
2 from department;
DEPT DNAME MINSAL MAXSAL HOD
------
10 Sales 25000 32000 1
20 Finance 30000 50000 5
30 Admin 25000 40000 7
Q7 Display the salary, zone and grade of all the employees.
SQL> select salary,zone,grade
2 from employee;
SALARY ZONE G
------
30000 West A
35000 Centre A
32000 West
38000 North C
32000 East
37000 South B
36000 North A
7 rows selected.
Q8 Display the details of all the employees who are below 30 years of age.
SQL> select *
2 from employee
3 where(age<30);
NO NAME SALARY ZONE AGE G DEPT
------
1 Mukul 30000 West 28 A 10
5 Nupur 32000 East 26 20
6 Moksh 37000 South 28 B 10
7 Shelly 36000 North 26 A 3
Q9 Display the names of various zones from employee. A zone name should appear only once.
SQL> select distinct zone
2 from employee;
ZONE
------
Centre
East
North
South
West
Q10 Display the details of all the employees of department 10 who are above 30 years of age.
SQL> select *
2 from employee
3 where(dept=10 AND age>30);
no rows selected
Q11 Display the names of all the employees who are working in department 20 or 30.
SQL> select *
2 from employee
3 where(dept=20 OR dept=30);
NO NAME SALARY ZONE AGE G DEPT
------
3 Naveen 32000 West 40 20
4 Uday 38000 North 38 C 30
5 Nupur 32000 East 26 20
7 Shelly 36000 North 26 A 30
Q12 Display the details of all the employees whose salary is between 32000 and 38000.
SQL> select *
2 from employee
3 where(salary BETWEEN 32000 AND 38000);
NO NAME SALARY ZONE AGE G DEPT
------
2 Kriti 35000 Centre 30 A 10
3 Naveen 32000 West 40 20
4 Uday 38000 North 38 C 30
5 Nupur 32000 East 26 20
6 Moksh 37000 South 28 B 10
7 Shelly 36000 North 26 A 30
6 rows selected.
Q13 Display the name, salary, and age of all the employees whose names starts with ‘M’.
SQL> select name,salary,age
2 from employee
3 where(name LIKE 'M%');
NAME SALARY AGE
------
Mukul 30000 28
Moksh 37000 28
Q14 Display the name, salary, and age of all the employees whose names ends with ‘a’.
SQL> select name,salary,age
2 from employee
3 where(name LIKE '%a');
no rows selected
Q15 Display the sum and average of the salaries of all the employees.
SQL> select sum(salary),avg(salary)
2 from employee;
SUM(SALARY) AVG(SALARY)
------
240000 34285.7143
Q16 Display the highest and lowest salaries being paid in department 10.
SQL> select max(salary),min(salary)
2 from employee;
MAX(SALARY) MIN(SALARY)
------
38000 30000
Q17 Display the number of employees working in department 10.
SQL> select count(no)
2 from employee
3 where(dept=10);
COUNT(NO)
------
3
Q18 Display the details of all the employees in the ascending order of their salaries.
SQL> select *
2 from employee
3 order by salary asc;
NO NAME SALARY ZONE AGE G DEPT
------
1 Mukul 30000 West 28 A 10
3 Naveen 32000 West 40 20
5 Nupur 32000 East 26 20
2 Kriti 35000 Centre 30 A 10
7 Shelly 36000 North 26 A 30
6 Moksh 37000 South 28 B 10
4 Uday 38000 North 38 C 30
7 rows selected.
Q19 Delete the records of all the employees whose grade is C and salary is below 30000.
SQL> delete from employee
2 where (grade='C'AND salary<30000);
0 rows deleted.
Q20 Delete the records of all the employees of department 10 who are above 40 years of age.
SQL> delete from employee
2 where (dept=10 AND age>40);
0 rows deleted.
Q21 Add another column HireDate of type Date in the employee table.
SQL> alter table employee
2 add (hiredata date);
Table altered.