inerview
Write an SQL query to fetch the departments that have less than five people in it
SELECT DEPARTMENT, COUNT(WORKER_ID) as 'Number of Workers' FROM Worker GROUP BY DEPARTMENT HAVING COUNT(WORKER_ID) < 5;
Write an SQL query to fetch the no. of workers for each department in the descending order
SELECT DEPARTMENT, count(WORKER_ID) No_Of_Workers FROM worker GROUP BY DEPARTMENT ORDER BY No_Of_Workers DESC;
Write an SQL query to fetch departments along with the total salaries paid for each of them
SELECT DEPARTMENT, sum(Salary) from worker group by DEPARTMENT;
Write an SQL query to print all Worker details from the Worker table order by FIRST_NAME Ascending and DEPARTMENT Descending.
Select * from Worker order by FIRST_NAME asc,DEPARTMENT desc;
Write an SQL query to print all Worker details from the Worker table order by FIRST_NAME Ascending.
Select * from Worker order by FIRST_NAME asc;
Write an SQL query to print details of Workers with DEPARTMENT name as "Admin"
Select * from Worker where DEPARTMENT like 'Admin%';
Write an SQL query to print details for Workers with the first name as "Vipul" and "Satish" from Worker table.
Select * from Worker where FIRST_NAME in ('Vipul','Satish');
Write an SQL query to print details of the Workers whose FIRST_NAME contains 'a'
Select * from Worker where FIRST_NAME like '%a%';
Write an SQL query to print details of the Workers whose FIRST_NAME ends with 'a'
Select * from Worker where FIRST_NAME like '%a';
Write an SQL query to print details of the Workers whose FIRST_NAME ends with 'h' and contains six alphabets
Select * from Worker where FIRST_NAME like '_____h';
Write an SQL query to print details of workers excluding first names, "Vipul" and "Satish" from Worker table.
Select * from Worker where FIRST_NAME not in ('Vipul','Satish');
Write an SQL query to print details of the Workers whose SALARY lies between 100000 and 500000
Select * from Worker where SALARY between 100000 and 500000;
Write an SQL query to show the last record from a table
Select * from Worker where WORKER_ID = (SELECT max(WORKER_ID) from Worker);
Write an SQL query to fetch the first row of a table
Select * from Worker where WORKER_ID = (SELECT min(WORKER_ID) from Worker);
Write an SQL query to print details of the Workers who have joined in Feb'2014
Select * from Worker where year(JOINING_DATE) = 2014 and month(JOINING_DATE) = 2;
Write an SQL query to print the FIRST_NAME and LAST_NAME from Worker table into a single column COMPLETE_NAME. A space char should separate them.
Select CONCAT(FIRST_NAME, ' ', LAST_NAME) AS 'COMPLETE_NAME' from Worker;
Write an SQL query to fetch "FIRST_NAME" from Worker table using the alias name as <WORKER_NAME>
Select FIRST_NAME AS WORKER_NAME from Worker;
Write an SQL query to find the position of the alphabet ('a') in the first name column 'Amitabh' from Worker table.
Select INSTR(FIRST_NAME, BINARY'a') from Worker where FIRST_NAME = 'Amitabh';
Write an SQL query to print the DEPARTMENT from Worker table after removing white spaces from the left side.
Select LTRIM(DEPARTMENT) from Worker;
Write an SQL query to print the FIRST_NAME from Worker table after replacing 'a' with 'A'.
Select REPLACE(FIRST_NAME,'a','A') from Worker;
Write an SQL query to print the FIRST_NAME from Worker table after removing white spaces from the right side.
Select RTRIM(FIRST_NAME) from Worker;
Write an SQL query to fetch unique values of DEPARTMENT from Worker table.
Select distinct DEPARTMENT from Worker;
Write an SQL query to fetch the list of employees with the same salary
Select distinct W.WORKER_ID, W.FIRST_NAME, W.Salary from Worker W, Worker W1 where W.Salary = W1.Salary and W.WORKER_ID != W1.WORKER_ID;
Write an SQL query that fetches the unique values of DEPARTMENT from Worker table and prints its length.
Select distinct length(DEPARTMENT) from Worker;
Write an SQL query to show the second highest salary from a table
Select max(Salary) from Worker where Salary not in (Select max(Salary) from Worker);
Write an SQL query to print the first three characters of FIRST_NAME from Worker table.
Select substring(FIRST_NAME,1,3) from Worker;
Write an SQL query to fetch "FIRST_NAME" from Worker table in upper case.
Select upper(FIRST_NAME) from Worker;
Write an SQL query to fetch intersecting records of two tables
(SELECT * FROM Worker) INTERSECT (SELECT * FROM WorkerClone);
Write an SQL query to fetch the first 50% records from a table
SELECT * FROM WORKER WHERE WORKER_ID <= (SELECT count(WORKER_ID)/2 from Worker);
Write an SQL query to show records from one table that another table does not have
SELECT * FROM Worker MINUS SELECT * FROM Title;
Write an SQL query to show only odd rows from a table
SELECT * FROM Worker WHERE MOD (WORKER_ID, 2) <> 0;
Write an SQL query to show only even rows from a table
SELECT * FROM Worker WHERE MOD (WORKER_ID, 2) = 0;
Write an SQL query to fetch the last five records from a table
SELECT * FROM Worker WHERE WORKER_ID <=5 UNION SELECT * FROM (SELECT * FROM Worker W order by W.WORKER_ID DESC) AS W1 WHERE W1.WORKER_ID <=5;
Write an SQL query to fetch worker names with salaries >= 50000 and <= 100000
SELECT CONCAT(FIRST_NAME, ' ', LAST_NAME) As Worker_Name, Salary FROM worker WHERE WORKER_ID IN (SELECT WORKER_ID FROM worker WHERE Salary BETWEEN 50000 AND 100000);
Write an SQL query to fetch the count of employees working in the department 'Admin'
SELECT COUNT(*) FROM worker WHERE DEPARTMENT = 'Admin';
Write an SQL query to show all departments along with the number of people in there
SELECT DEPARTMENT, COUNT(DEPARTMENT) as 'Number of Workers' FROM Worker GROUP BY DEPARTMENT;
Write an SQL query to print details of the Workers who are also Managers
SELECT DISTINCT W.FIRST_NAME, T.WORKER_TITLE FROM Worker W INNER JOIN Title T ON W.WORKER_ID = T.WORKER_REF_ID AND T.WORKER_TITLE in ('Manager');
Write an SQL query to fetch the names of workers who earn the highest salary
SELECT FIRST_NAME, SALARY from Worker WHERE SALARY=(SELECT max(SALARY) from Worker);
Write an SQL query to fetch duplicate records having matching data in some fields of a table
SELECT WORKER_TITLE, AFFECTED_FROM, COUNT(*) FROM Title GROUP BY WORKER_TITLE, AFFECTED_FROM HAVING COUNT(*) > 1;
Write an SQL query to fetch three max salaries from a table
SELECT distinct Salary from worker a WHERE 3 >= (SELECT count(distinct Salary) from worker b WHERE a.Salary <= b.Salary) order by a.Salary desc;
Write an SQL query to fetch three min salaries from a table
SELECT distinct Salary from worker a WHERE 3 >= (SELECT count(distinct Salary) from worker b WHERE a.Salary >= b.Salary) order by a.Salary desc;
Write an SQL query to fetch nth max salaries from a table
SELECT distinct Salary from worker a WHERE n >= (SELECT count(distinct Salary) from worker b WHERE a.Salary <= b.Salary) order by a.Salary desc;
Write an SQL query to print the name of employees having the highest salary in each department
SELECT t.DEPARTMENT,t.FIRST_NAME,t.Salary from(SELECT max(Salary) as TotalSalary,DEPARTMENT from Worker group by DEPARTMENT) as TempNew Inner Join Worker t on TempNew.DEPARTMENT=t.DEPARTMENT and TempNew.TotalSalary=t.Salary;
Write an SQL query to show one row twice in results from a table
select FIRST_NAME, DEPARTMENT from worker W where W.DEPARTMENT='HR' union all select FIRST_NAME, DEPARTMENT from Worker W1 where W1.DEPARTMENT='HR';