SQL Assignment 1
Consider the tables given below and answer the questions that follow:
Table: Employee
No Name Salary Zone Age Grade Dept
1 Mukul 30000 West 28 A 10
2 Kritika 35000 Centre 30 A 10
3 Naveen 32000 West 40 NULL 20
4 Uday 38000 North 38 C 30
5 Nupur 32000 East 26 NULL 20
6 Moksh 37000 South 28 B 10
7 Shelly 36000 North 26 A 30
Table: Department
Dept DName MinSal MaxSal HOD
10 Sales 25000 32000 1
20 Finance 30000 50000 5
30 Admin 25000 40000 7
WRITE SQL COMMANDS TO:
Create Table
[Link] the table Employee.
[Link] the table Department.
Insert data in a table
[Link] data in the table Employee
[Link] data in the table Department.
Simple Select
[Link] the Salary, Zone, and Grade of all the employees.
[Link] the name of all the employees along with their annual salaries.
The new column should be given the name “Annual Salary”.
Conditional Select using Where Clause
[Link] the details of all the employees who are below 30 years of age.
Using DISTINCT Clause
[Link] the names of various zones from the table Employee. A zone name should
appear only once.
Using Logical Operators (NOT, AND, OR)
[Link] the details of all the employees who are getting a salary of more than 35000
in the department 30.
[Link] the names and salaries of all the employees who are not working in
department 20.
[Link] the details of all the employees whose salary is between 32000 and 38000.
Using IN Operator
[Link] the names of all the employees who are working in department 20 or 30.
(Using IN operator)
Using BETWEEN Operator
[Link] the details of all the employees whose salary is between 32000 and 38000.
[Link] the details of all the employees whose grade is between ‘A’ and ‘C’.
Using LIKE Operator
[Link] the name, salary, and age of all the employees whose names start with ‘M’.
[Link] the name, salary, and age of all the employees whose names contain ‘a’ in
the descending order of their names.
[Link] the details of all the employees whose names contain ‘a’ as the second
character.
Using Aggregate functions
[Link] the highest and the lowest salaries being paid in department 10.
[Link] the number of employees working in department 10.
Using ORDER BY clause
[Link] the name and salary of all the employees in the ascending order of their
salaries.
Using GROUP BY clause
[Link] the total number of employees in each department.
[Link] the highest salary, lowest salary, and average salary of each zone.
Using UPDATE, DELETE, ALTER TABLE
[Link] the grade B for all those whose grade is NULL.
[Link] the salary of all the employees above 30 years of age by 10%.
[Link] the records of all the employees whose grade is C and salary is below 30000.
[Link] another column HireDate of type Date in the Employee table.
JOIN of two tables
[Link] the name, salary of all the employees who work in Sales department.
[Link] the Name and Department Name of all the employees.
[Link] the names of all the employees whose salary is out of the specified range
for the corresponding department.
[Link] the name of the department and the name of the corresponding HOD for
all the departments.
