Q-1. Fetch `FIRST_NAME` from Worker table using alias `WORKER_NAME`.
```
SELECT first_name AS WORKER_NAME FROM worker;
```
Q-2. Fetch `FIRST_NAME` from Worker table in upper case.
```
SELECT UPPER(first_name) FROM worker;
```
Q-3. Fetch unique values of `DEPARTMENT` from Worker table.
```
SELECT DISTINCT department FROM worker;
```
Q-4. Print the first three characters of `FIRST_NAME` from Worker table.
```
SELECT SUBSTRING(first_name, 1, 3) FROM worker;
```
Q-5. Find the position of the alphabet 'b' in the `FIRST_NAME` column 'Amitabh' from Worker table.
```
SELECT INSTR(first_name, 'b') FROM worker WHERE first_name = 'Amitabh';
```
Q-6. Print `FIRST_NAME` from Worker table after removing white spaces from the right side.
```
SELECT RTRIM(first_name) FROM worker;
```
Q-7. Print `DEPARTMENT` from Worker table after removing white spaces from the left side.
```
SELECT LTRIM(department) FROM worker;
```
Q-8. Fetch unique values of `DEPARTMENT` from Worker table and print its length.
```
SELECT DISTINCT department, LENGTH(department) FROM worker;
```
Q-9. Print `FIRST_NAME` from Worker table after replacing 'a' with 'A'.
```
SELECT REPLACE(first_name, 'a', 'A') FROM worker;
```
Q-10. Print `FIRST_NAME` and `LAST_NAME` from Worker table into a single column `COMPLETE_NAME`.
```
SELECT CONCAT(first_name, ' ', last_name) AS COMPLETE_NAME FROM worker;
```
Q-11. Print all Worker details from Worker table ordered by `FIRST_NAME` ascending.
```
SELECT * FROM worker ORDER BY first_name;
```
Q-12. Print all Worker details from Worker table ordered by `FIRST_NAME` ascending and `DEPARTMENT` descending.
```
SELECT * FROM worker ORDER BY first_name, department DESC;
```
Q-13. Print details for Workers with the first name as 'Vipul' and 'Satish' from Worker table.
```
SELECT * FROM worker WHERE first_name IN ('Vipul', 'Satish');
```
Q-14. Print details of Workers excluding first names 'Vipul' and 'Satish' from Worker table.
```
SELECT * FROM worker WHERE first_name NOT IN ('Vipul', 'Satish');
```
Q-15. Print details of Workers with `DEPARTMENT` name as 'Admin*'.
```
SELECT * FROM worker WHERE department LIKE 'Admin%';
```
Q-16. Print details of Workers whose `FIRST_NAME` contains 'a'.
```
SELECT * FROM worker WHERE first_name LIKE '%a%';
```
Q-17. Print details of Workers whose `FIRST_NAME` ends with 'a'.
```
SELECT * FROM worker WHERE first_name LIKE '%a';
```
Q-18. Print details of Workers whose `FIRST_NAME` ends with 'h' and contains six alphabets.
```
SELECT * FROM worker WHERE first_name LIKE '_____h';
```
Q-19. Print details of Workers whose `SALARY` lies between 100000 and 500000.
```
SELECT * FROM worker WHERE salary BETWEEN 100000 AND 500000;
```
Q-20. Print details of Workers who joined in Feb'2014.
```
SELECT * FROM worker WHERE YEAR(joining_date) = 2014 AND MONTH(joining_date) = 2;
```
Q-21. Fetch the count of employees working in the department 'Admin'.
```
SELECT department, COUNT(*) FROM worker WHERE department = 'Admin';
```
Q-22. Fetch worker full names with salaries >= 50000 and <= 100000.
```
SELECT CONCAT(first_name, ' ', last_name) FROM worker WHERE salary BETWEEN 50000 AND 100000;
```
Q-23. Fetch the number of workers for each department in descending order.
```
SELECT department, COUNT(worker_id) AS no_of_worker FROM worker GROUP BY department ORDER BY no_of_worker DESC;
```
Q-24. Print details of Workers who are also Managers.
```
SELECT w.* FROM worker AS w INNER JOIN title AS t ON w.worker_id = t.worker_ref_id WHERE t.worker_title = 'Manager';
```
Q-25. Fetch number (more than 1) of same titles in the ORG of different types.
```
SELECT worker_title, COUNT(*) AS count FROM title GROUP BY worker_title HAVING COUNT(*) > 1;
```
Q-26. Show only odd rows from a table.
```
SELECT * FROM worker WHERE MOD(worker_id, 2) <> 0;
```
Q-27. Show only even rows from a table.
```
SELECT * FROM worker WHERE MOD(worker_id, 2) = 0;
```
Q-28. Clone a new table from another table.
```
CREATE TABLE worker_clone LIKE worker;
INSERT INTO worker_clone SELECT * FROM worker;
SELECT * FROM worker_clone;
```
Q-29. Fetch intersecting records of two tables.
```
SELECT worker.* FROM worker INNER JOIN worker_clone USING(worker_id);
```
Q-30. Show records from one table that another table does not have.
```
SELECT worker.* FROM worker LEFT JOIN worker_clone USING(worker_id) WHERE worker_clone.worker_id IS NULL;
```
Q-31. Show the current date and time.
```
SELECT CURDATE();
SELECT NOW();
```
Q-32. Show the top 5 records of a table ordered by descending salary.
```
SELECT * FROM worker ORDER BY salary DESC LIMIT 5;
```
Q-33. Determine the 5th highest salary from a table.
```
SELECT * FROM worker ORDER BY salary DESC LIMIT 4,1;
```
Q-34. Determine the 5th highest salary without using LIMIT keyword.
```
SELECT salary FROM worker w1 WHERE 4 = (SELECT COUNT(DISTINCT w2.salary) FROM worker w2 WHERE w2.salary >= w1.salary);
```
Q-35. Fetch the list of employees with the same salary.
```
SELECT w1.* FROM worker w1, worker w2 WHERE w1.salary = w2.salary AND w1.worker_id != w2.worker_id;
```
Q-36. Show the second highest salary from a table using sub-query.
```
SELECT MAX(salary) FROM worker WHERE salary NOT IN (SELECT MAX(salary) FROM worker);
```
Q-37. Show one row twice in results from a table.
```
SELECT * FROM worker UNION ALL SELECT * FROM worker ORDER BY worker_id;
```
Q-38. List `worker_id` who do not get a bonus.
```
SELECT worker_id FROM worker WHERE worker_id NOT IN (SELECT worker_ref_id FROM bonus);
```
Q-39. Fetch the first 50% records from a table.
```
SELECT * FROM worker WHERE worker_id <= (SELECT COUNT(worker_id)/2 FROM worker);
```
Q-40. Fetch the departments that have less than 4 people in it.
```
SELECT department, COUNT(department) AS depCount FROM worker GROUP BY department HAVING depCount < 4;
```
Q-41. Show all departments along with the number of people in there.
```
SELECT department, COUNT(department) AS depCount FROM worker GROUP BY department;
```
Q-42. Show the last record from a table.
```
SELECT * FROM worker WHERE worker_id = (SELECT MAX(worker_id) FROM worker);
```
Q-43. Fetch the first row of a table.
```
SELECT * FROM worker WHERE worker_id = (SELECT MIN(worker_id) FROM worker);
```
Q-44. Fetch the last five records from a table.
```
(SELECT * FROM worker ORDER BY worker_id DESC LIMIT 5) ORDER BY worker_id;
```
Q-45. Print the name of employees having the highest salary in each department.
```
SELECT w.department, w.first_name, w.salary
FROM (SELECT MAX(salary) AS maxsal, department FROM worker GROUP BY department) temp
INNER JOIN worker w ON temp.department = w.department AND temp.maxsal = w.salary;
```
Q-46. Fetch three max salaries from a table using co-related subquery.
```
SELECT DISTINCT salary
FROM worker w1
WHERE 3 >= (SELECT COUNT(DISTINCT salary) FROM worker w2 WHERE w1.salary <= w2.salary)
ORDER BY w1.salary DESC;
```
Q-47. Fetch three min salaries from a table using co-related subquery.
```
SELECT DISTINCT salary
FROM worker w1
WHERE 3 >= (SELECT COUNT(DISTINCT salary) FROM worker w2 WHERE w1.salary >= w2.salary)
ORDER BY w1.salary DESC;
```
Q-48. Fetch nth max salaries from a table.
```
SELECT DISTINCT salary
FROM worker w1
WHERE n >= (SELECT COUNT(DISTINCT salary) FROM worker w2 WHERE w1.salary <= w2
.salary)
ORDER BY w1.salary DESC;
```
Q-49. Fetch departments along with the total salaries paid for each of them.
```
SELECT department , SUM(salary) AS depSal FROM worker GROUP BY department ORDER BY depSal DESC;
```
Q-50. Fetch the names of workers who earn the highest salary.
```
SELECT first_name, salary FROM worker WHERE salary = (SELECT MAX(salary) FROM worker);
```
0 Comments
Please do not Enter any spam link in the comment box