Just need my worked checked

profileiwatbo

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);

  • 3 years ago
  • 10
Answer(0)