Consider you have a worker table with following fields:
- first_name
- last_name
- salary
- worker_id(Primary Key)
- department
- department_name
Along with you have some meta data tables like title and bonus which contains following fields:
title
Q. How do you retrieve the first name from the Worker table using an alias "WORKER NAME"?
Answer
SELECT first_name AS worker_name FROM Worker;
Q. How can you convert the first name from the Worker table to uppercase?
Answer
SELECT UPPER(first_name) AS first_name FROM Worker;
Q. What SQL query would you use to fetch distinct department names from the Worker table?
Answer
SELECT DISTINCT department FROM Worker;
Q. How can you select the first three characters of the first name from the Worker table?
Answer
SELECT SUBSTRING(first_name, 1, 3) AS first_name FROM Worker;
Q. Write a query to find the position of 's' in the first name "Manish" within the Worker table.
Answer
SELECT POSITION('s' IN first_name) AS position_of_s FROM Worker WHERE first_name = 'Manish';
Q. How do you trim whitespace from the right side of the first name in the Worker table?
Answer
SELECT RTRIM(first_name) AS trimmed_first_name FROM Worker;
Q. Write a query to remove whitespace from the left side of the department field in the Worker table.
Answer
SELECT LTRIM(department) AS trimmed_department FROM Worker;
Q. How can you fetch unique department names from the Worker table and display their lengths?
Answer
SELECT DISTINCT department, LENGTH(department) AS department_length FROM Worker;
Q. What query would replace 'a' with 'A' in the first name from the Worker table?
Answer
SELECT REPLACE(first_name, 'a', 'A') AS first_name FROM Worker;
Q. How do you concatenate the first name and last name from the Worker table into a single column "COMPLETE NAME"?
Answer
SELECT CONCAT(first_name, ' ', last_name) AS complete_name FROM Worker;
Q. Write a query to list all worker details from the Worker table ordered by first name in ascending order.
Answer
SELECT FROM Worker ORDER BY first_name ASC;
Q. How can you list all worker details from the Worker table ordered by first name ascending and department descending?
Answer
SELECT FROM Worker ORDER BY first_name ASC, department DESC;
Q. Write a query to fetch details for Workers with the first names "
Manish" and "Arhan" from the Worker table.
Answer
SELECT FROM Worker WHERE first_name IN ('Manish', 'Arhan');
Q. Write a query to list details of workers excluding first names "Manish" and "Arhan" from the Worker table.
Answer
SELECT FROM Worker WHERE first_name NOT IN ('Manish', 'Arhan');
Q. Write a query to fetch details of Workers with the department name "Admin".
Answer
SELECT FROM Worker WHERE department_name = 'Admin';
Q. Write a query to fetch details of Workers whose first name contains 'a'.
Answer
SELECT FROM Worker WHERE first_name LIKE '%a%';
Q. Write a query to fetch details of Workers whose first name ends with 'a'.
Answer
SELECT FROM Worker WHERE first_name LIKE '%a';
Q. Write a query to fetch details of Workers whose first name ends with 'h' and contains six alphabets.
Answer
SELECT FROM Worker WHERE first_name LIKE '%h' AND CHAR_LENGTH(first_name) = 6;
Q. Write a query to fetch details of Workers whose salary lies between 100000 and 500000.
Answer
SELECT FROM Worker WHERE salary BETWEEN 100000 AND 500000;
Q. Write a query to list Workers who joined in February 2014.
Answer
-- Assuming there's a joining_date field in the Worker table, which is not listed in the initial table information. -- SELECT FROM Worker WHERE YEAR(joining_date) = 2014 AND MONTH(joining_date) = 2; -- This answer is based on an assumed field not explicitly mentioned in the table schema provided.
Q. Write a query to fetch the count of employees working in the department 'Admin'.
Answer
SELECT COUNT() FROM Worker WHERE department_name = 'Admin';
Q. Write a query to fetch worker names with salaries between 50000 and 100000.
Answer
SELECT CONCAT(first_name, ' ', last_name) AS full_name, salary FROM Worker WHERE salary BETWEEN 50000 AND 100000;
Q. Write a query to fetch the number of workers for each department in descending order.
Answer
SELECT department, COUNT() AS num_workers FROM Worker GROUpBY department ORDER BY num_workers DESC;
Q. Write a query to list details of Workers who are also Managers, assuming a title table contains info about worker titles.
Answer
SELECT w. FROM Worker w JOIN title t ON w.worker_id = t.worker_id WHERE t.worker_title = 'Manager';
Q. Write a query to count the number of titles in the organization of different types.
Answer
SELECT worker_title, COUNT() FROM title GROUpBY worker_title HAVING COUNT() > 1;
Q. Write a query to show only odd rows from the Worker table.
Answer
SELECT FROM Worker WHERE MOD(worker_id, 2) = 1;
Q. Write a query to show only even rows from the Worker table.
Answer
SELECT FROM Worker WHERE MOD(worker_id, 2) = 0;
Q. Write a query to clone a new table from another table (e.g., worker_clone from worker).
Answer
-- Step1: Create a clone table with the same structure as Worker CREATE TABLE worker_clone LIKE Worker; -- Step2: Copy all data from Worker to worker_clone INSERT INTO worker_clone SELECT FROM Worker;
Q. Write a query to fetch intersecting records of two tables (worker and worker_clone).
Answer
SELECT w. FROM Worker w INNER JOIN worker_clone wc ON w.worker_id = wc.worker_id;
Q. Write a query to show records from one table that another table does not have.
Answer
SELECT w. FROM Worker w LEFT JOIN worker_clone wc ON w.worker_id = wc.worker_id WHERE wc.worker_id IS NULL;
Q. Write a query to show the current date and time.
Answer
SELECT NOW();
Q. Write a query to show the topn (e.g., 5) records of a table ordered by descending salary.
Answer
SELECT
FROM Worker ORDER BY salary DESC LIMIT 5;
Q. Write a query to determine the nth (e.g., 5th) highest salary from a table.
Answer
SELECT DISTINCT salary FROM Worker ORDER BY salary DESC LIMIT 1 OFFSET 4;
Q. Write a query to find the 5th highest salary without using the LIMIT keyword.
Answer
SELECT salary FROM Worker w1 WHERE 4 = (SELECT COUNT(DISTINCT w2.salary) FROM Worker w2 WHERE w2.salary > w1.salary);
Q. Write a query to list employees with the same salary.
Answer
SELECT w1. FROM Worker w1, Worker w2 WHERE w1.salary = w2.salary AND w1.worker_id != w2.worker_id;
Q. Write a query to show the second highest salary from a table using a sub-query.
Answer
SELECT MAX(salary) FROM Worker WHERE salary NOT IN (SELECT MAX(salary) FROM Worker);
Q. Write a query to show one row twice in results from a table.
Answer
SELECT FROM Worker WHERE worker_id = (SELECT MIN(worker_id) FROM Worker) UNION ALL SELECT FROM Worker WHERE worker_id = (SELECT MIN(worker_id) FROM Worker);
Q. Write a query to list worker ids who do not receive a bonus.
Answer
SELECT worker_id FROM Worker WHERE worker_id NOT IN (SELECT worker_id FROM bonus);
Q. Write a query to fetch the first 50% records from a table.
Answer
SELECT FROM Worker WHERE worker_id <= (SELECT FLOOR(COUNT() / 2) FROM Worker);
Q. Write a query to fetch the departments that have less than 4 people in them.
Answer
SELECT department, COUNT() AS dept_count FROM Worker GROUpBY department HAVING dept_count < 4;
Q. Write a query to show all departments along with the number of people in there.
Answer
SELECT department, COUNT() AS dept_count FROM Worker GROUpBY department;
Q. Write a query to show the last record from a table.
Answer
SELECT FROM Worker ORDER BY worker_id DESC LIMIT 1;
Q. Write a query to fetch the first row of a table.
Answer
SELECT FROM Worker ORDER BY worker_id ASC LIMIT 1;
Q. Write a query to fetch the last five records from a table.
Answer
SELECT FROM Worker ORDER BY worker_id DESC LIMIT 5;
Q. Write a query to print the names of employees having the highest salary in each department.
Answer
SELECT w.department, w.first_name, w.salary FROM Worker w INNER JOIN (SELECT department, MAX(salary) AS max_salary FROM Worker GROUpBY department) AS dept_max ON w.department = dept_max.department AND w.salary = dept_max.max_salary;
Q. Write a query to fetch three max salaries from a table using a co-related subquery.
Answer
SELECT DISTINCT salary FROM Worker w1 WHERE 3 >= (SELECT COUNT(DISTINCT w2.salary) FROM Worker w2 WHERE w2.salary >= w1.salary) ORDER BY salary DESC;
Q. Write the order of executions of SQL operations.
Answer
Execution Order:
- FROM
- ON
- WHERE
- GROUP BY
- Aggregation functions (COUNT, SUM, MIN/MAX, AVG)
- HAVING
- SELECT
- DISTINCT
- UNION, INTERSECT, EXCEPT
- ORDER BY
- LIMIT, TOP