I need my SQL assignment looked over. I’m currently using MS management studio
1. List the employee whose employee number is 100.
Select * from Employee where employee_Num=100;
2. List the Employee whose salary is between 50 K to 100k.
Select * from Employee where salary between 50000 and 100000;
Select * from Employee where salary >= 50000 and salary <= 100000;
3. List the Employees whose name starts with ‘Ami’.
Select * from Employees where name like ‘Ami%’;
4. List the Employees whose name starts with A and surname starts with S.
Select * from Employees where name like ‘A%’ and surname like ‘S%’;
5. List the Employees whos surname contains kar word.
Select * from Employees where surname like ‘%kar%’;
6. List the Employees whose name starts with P,B,R characters.
Select * from Employees where name like ‘[PBR]%’;
7. List the Employees whose name not starts with P,B,R characters.
Not Operator Symbol
Select * from Employees where name like ‘[!PBR]%’;
Not Operator
Select * from Employees where name not like ‘[PBR]%’;
8. Write a query to fetch first record from Employee table?
Select * from Employees where rownum=1;
9. Write a query to fetch the last record from Employees table?
Select * from Employees where rowid = select max(rowid) from Employee;
10. Write a query to find the 2nd highest salary of Employees using Self Join
Select * from Employees a where 2 = select count (distinct salary) from Employee where a.salary <= b.salary;
11. Write a query to display odd rows from the Employees table
Select * from(select rownum as rno,E.*from Employees E) where Mod(rno,2)=1;
12. Write a query to display even rows from the Employees table
Select * from(Select rownum as rno,E.* from Employees) where Mod(rno,2)=0;
13. Write a query to show the max salary and min salary together form Employees table
Select max (salary) from Employees
Union
Select min (salary) from Employees;
14. Write a query to fetch all the record from Employee whose joining year is 2018
Select * from Employees where substr(convert(varchar,joining_date, 103),7,4)= ’2018′
15. Write a SQL Query to find maximum salary of each department
Select Dept_id,max(salary) from Employees group by Dept_id;
16. Write a query to find all Employees and their managers (Consider there is manager id also in Employee table).
Select e.employee_name,m.employee name from Employees e,Employees m where e.Employee_id=m.Manager_id;
17. Write a query to display 3 to 7 records from Employee table
Select * from (Select rownum as ‘No_of_Row’,E.* from Employee E)
18. Write a query to fetch common records from two different tables Employees and Employees1 which has not any joining conditions
Select * from Employees
Intersect
Select * from Employees1;
19. Write a query to validate Email of Employee
SELECT
EMAIL
FROM
EMPLOYEE
Where NOT REGEXP_LIKE(Email, ‘[A-Z0-9._%+-]+@[A-Z0-9.-]+.[A-Z]{2,4}’, ‘i’);
20. Write a query to remove duplicate rows from Employees table
Select Employee_No FROM Employees WHERE ROWID < >
(Select max (rowid) form Employees b where Employee_No =b.Employee_No);
