SQL-Structured Query Language

Question

Consider the Following Table Employee(emp_id, name,dept_id, email, mobile_number, join_date,salary, mgr_id) 1) Query to display the name and salary for all the employees whose salary is not in the range of Rs. 10,000 and 50,000. 2) Make sure that the EmailId of each employee is common and unique to that employee only. 3) Query to display the name and Department number of all the Employees in department number 8 and 12 in an alphabetical order by name. 4) Write a query to display the name of employees who have an ‘a’ and ‘e’ in their name. 5) Write a query to display the name, job, salary of all the employees whose job is clerk or teacher and whose salary is not equal to 15,000.

Answer 1

1) Query to display the name and salary for all the employees whose salary is not in the range of Rs. 10,000 and 50,000.

Select Name,Salary from Employee where salary not between 10000 and 50000

Select Name,Salary from Employee where salary <10000 and salary>50000

2) Make sure that the EmailId of each employee is common and unique to that employee only.

ALTER TABLE Employee ADD CONSTRAINT UQ_Employee_Email UNIQUE (name,email)

3) Query to display the name and Department number of all the Employees in department number 8 and 12 in an alphabetical order by name.

Select Name,Dept_id from Employee where dept_id in (8,12) order by name

4) Write a query to display the name of employees who have an ‘a’ and ‘e’ in their name.

Select Name from Employee where name like '%[ae]%'

5) Write a query to display the name, job, salary of all the employees whose job is clerk or teacher and whose salary is not equal to 15,000.

--considering job is stored in a table Department as (dept_id ,DepartName )

Select Employee.Name,Department.DepartmentName as Job,Employee.Salary

from Employee

inner join Department on Employee.dept_id=Department.dept_id

where Department.DepartmentName in ('clerk','teacher') and Employee.Salary<>15000