Just need my worked checked
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
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);
SQLMastery Assignment
Let us consider a table named Employee. We are going to use this table to write
different SQL Queries.
Query 1: List the employee whose employee number is 100.
Query 2: List the Employee whose salary is between 50 K to 100 K.
Query 3: List the Employees whose name starts with ‘Ami’.
Query 4: List the Employees whose name starts with A and surname starts with S.
Query 5: List the Employees who’s surname contains kar word.
Query 6: List the Employees whose name starts with P,B,R characters.
Query 7: List the Employees whose name does not start with P,B,R characters.
Query 8: Write a query to fetch the first record from the Employee
table.
Query 9: Write a query to fetch the last record from the Employees table.
Query 10: Write a query to find the 2nd highest salary of Employees using Self join.
Query 11: Write a query to display odd rows from the Employees table.
Query 12: Write a query to display even rows from the Employees table.
Query 13: Write a query to show the max salary and min salary together from Employees
table.
Query 14: Write a query to fetch all the records from Employee whose joining year is 2018.
Query 15: Write a SQL Query to find maximum salary of each department.
Query 16: Write a query to find all Employees and their managers? (Consider there is
manager id also in Employee table).
Query 17: Write a query to display 3 to 7 records from Employee table.
Query 18: Write a query to fetch common records from two different tables Employees and
Employees1 which has not any joining condition.
Query 19: Write a query to validate Email of Employee.
Query 20: Write a query to remove duplicate rows from Employees table.