40 SQL Query Questions and Answers for Practice.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "31844b27",
   "metadata": {},
   "source": [
    "## 40 SQL Query Questions and Answers for Practice"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "33af5ecc",
   "metadata": {},
   "source": [
    "### Sample Table – Worker\n",
    "![](images/s1.png)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "349336ec",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "88bc5ac9",
   "metadata": {},
   "source": [
    "## Sample Table – Bonus\n",
    "![](images/s1.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e709b37b",
   "metadata": {},
   "source": [
    "## Sample Table – Title\n",
    "![](images/s3.png)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e1eda454",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "f1cd701f",
   "metadata": {},
   "source": [
    "## SQL Script to Seed Sample Data.\n",
    "```\n",
    "CREATE DATABASE ORG;\n",
    "SHOW DATABASES;\n",
    "USE ORG;\n",
    "\n",
    "CREATE TABLE Worker (\n",
    "\tWORKER_ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,\n",
    "\tFIRST_NAME CHAR(25),\n",
    "\tLAST_NAME CHAR(25),\n",
    "\tSALARY INT(15),\n",
    "\tJOINING_DATE DATETIME,\n",
    "\tDEPARTMENT CHAR(25)\n",
    ");\n",
    "\n",
    "INSERT INTO Worker \n",
    "\t(WORKER_ID, FIRST_NAME, LAST_NAME, SALARY, JOINING_DATE, DEPARTMENT) VALUES\n",
    "\t\t(001, 'Monika', 'Arora', 100000, '14-02-20 09.00.00', 'HR'),\n",
    "\t\t(002, 'Niharika', 'Verma', 80000, '14-06-11 09.00.00', 'Admin'),\n",
    "\t\t(003, 'Vishal', 'Singhal', 300000, '14-02-20 09.00.00', 'HR'),\n",
    "\t\t(004, 'Amitabh', 'Singh', 500000, '14-02-20 09.00.00', 'Admin'),\n",
    "\t\t(005, 'Vivek', 'Bhati', 500000, '14-06-11 09.00.00', 'Admin'),\n",
    "\t\t(006, 'Vipul', 'Diwan', 200000, '14-06-11 09.00.00', 'Account'),\n",
    "\t\t(007, 'Satish', 'Kumar', 75000, '14-01-20 09.00.00', 'Account'),\n",
    "\t\t(008, 'Geetika', 'Chauhan', 90000, '14-04-11 09.00.00', 'Admin');\n",
    "\n",
    "CREATE TABLE Bonus (\n",
    "\tWORKER_REF_ID INT,\n",
    "\tBONUS_AMOUNT INT(10),\n",
    "\tBONUS_DATE DATETIME,\n",
    "\tFOREIGN KEY (WORKER_REF_ID)\n",
    "\t\tREFERENCES Worker(WORKER_ID)\n",
    "        ON DELETE CASCADE\n",
    ");\n",
    "\n",
    "INSERT INTO Bonus \n",
    "\t(WORKER_REF_ID, BONUS_AMOUNT, BONUS_DATE) VALUES\n",
    "\t\t(001, 5000, '16-02-20'),\n",
    "\t\t(002, 3000, '16-06-11'),\n",
    "\t\t(003, 4000, '16-02-20'),\n",
    "\t\t(001, 4500, '16-02-20'),\n",
    "\t\t(002, 3500, '16-06-11');\n",
    "\n",
    "CREATE TABLE Title (\n",
    "\tWORKER_REF_ID INT,\n",
    "\tWORKER_TITLE CHAR(25),\n",
    "\tAFFECTED_FROM DATETIME,\n",
    "\tFOREIGN KEY (WORKER_REF_ID)\n",
    "\t\tREFERENCES Worker(WORKER_ID)\n",
    "        ON DELETE CASCADE\n",
    ");\n",
    "\n",
    "INSERT INTO Title \n",
    "\t(WORKER_REF_ID, WORKER_TITLE, AFFECTED_FROM) VALUES\n",
    " (001, 'Manager', '2016-02-20 00:00:00'),\n",
    " (002, 'Executive', '2016-06-11 00:00:00'),\n",
    " (008, 'Executive', '2016-06-11 00:00:00'),\n",
    " (005, 'Manager', '2016-06-11 00:00:00'),\n",
    " (004, 'Asst. Manager', '2016-06-11 00:00:00'),\n",
    " (007, 'Executive', '2016-06-11 00:00:00'),\n",
    " (006, 'Lead', '2016-06-11 00:00:00'),\n",
    " (003, 'Lead', '2016-06-11 00:00:00');\n",
    " \n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b2d08eab",
   "metadata": {},
   "source": [
    "### 1.Write an SQL query to fetch “FIRST_NAME” from Worker table using the alias name as WORKER_NAME.\n",
    "```\n",
    "SELECT FIRST_NAME AS WORKER_NAME FROM ORG.Worker;\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "de14cf21",
   "metadata": {},
   "source": [
    "### 2.Write an SQL query to fetch “FIRST_NAME” from Worker table in upper case.\n",
    "```\n",
    "SELECT UPPER( FIRST_NAME) FROM ORG.Worker;\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "65b58fd1",
   "metadata": {},
   "source": [
    "### 3.Write an SQL query to fetch unique values of DEPARTMENT from Worker table.\n",
    "This query returns DEPARTMENT Column from table\n",
    "```\n",
    "SELECT DEPARTMENT  FROM ORG.Worker;\n",
    "```\n",
    "\n",
    "This query gives the unique values of DEPARTMENT column\n",
    "```\n",
    "SELECT DISTINCT (DEPARTMENT)  FROM ORG.Worker;\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e66e9b88",
   "metadata": {},
   "source": [
    "### 4.Write an SQL query to print the first three characters of  FIRST_NAME from Worker table.\n",
    "```\n",
    "SELECT SUBSTRING(FIRST_NAME,1,3) FROM ORG.Worker; \n",
    "```\n",
    "\n",
    "\n",
    "\n",
    "## Explanation of SUBSTRING() function :\n",
    " - Function in MySQL is used to derive substring from any given string .It extracts a string with a specified length, starting from a given location in an input string. The purpose of substring is to return a specific portion of the string.\n",
    "\n",
    "#### Syntax :\n",
    "```\n",
    "SUBSTRING(string, start, length)\n",
    "OR\n",
    "SUBSTRING(string FROM start FOR length)\n",
    "```\n",
    "### Parameters :\n",
    "\n",
    "- string – Input String from which to extract.\n",
    "- start – The starting position. If it is a positive number, this function extracts from the beginning of the string. If it is a negative number, this function extracts from the end of the string.\n",
    "- length – It is optional. It identifies the number of characters to extract. If it is not given The whole string is returned from the starting position."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3ed3f953",
   "metadata": {},
   "source": [
    "### 5. Write an SQL query to find the position of the alphabet (‘a’) in the first name column ‘Amitabh’ from Worker table.\n",
    "\n",
    "```\n",
    "SELECT INSTR(BINARY FIRST_NAME,'a') FROM ORG.Worker \n",
    "WHERE FIRST_NAME = 'Amitabh';\n",
    "```\n",
    "\n",
    "\n",
    "## Explanation of INSTR() function:\n",
    "- This function in MySQL is used to return the location of the first occurrence of a substring within a given string.\n",
    "\n",
    "#### Syntax :\n",
    "```\n",
    "INSTR(string_1, string_2)\n",
    "``` \n",
    "\n",
    "#### Parameters :\n",
    "This function accepts 2 parameters.\n",
    "\n",
    "- string_1 – The string where searching takes place.\n",
    "- string_2 – The string/sub-string which will be searched in string_1.\n",
    "\n",
    "\n",
    "#### Returns :\n",
    "It returns the position of the first occurrence of a substring within a given string.\n",
    "#### Note:\n",
    " >- The function will return 0 if string_2 is not found in string_1.\n",
    " >- INSTR() function only performs case-insensitive searches.\n",
    " >- Using Binary operator will make INSTR work as the case-sensitive function.\n",
    " \n",
    "### Examples:\n",
    "```\n",
    "SELECT INSTR(\"Python is a powerful Language\", \"powerful\")  AS Found;\n",
    "```\n",
    " \n",
    " \n",
    "```\n",
    "SELECT  \n",
    " INSTR(\"Python is a powerful Language\", \"IS\")  AS 'Found1';\n",
    " INSTR(\"Python is a powerful Language\", \"is\")  AS 'Found2';\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "97a5efba",
   "metadata": {},
   "source": [
    "### 6. Write an SQL query to print the FIRST_NAME from Worker table after removing white spaces from the right side.\n",
    "```\n",
    "SELECT RTRIM(FIRST_NAME) FROM ORG.Worker; \n",
    "```\n",
    "\n",
    "###### ------------------------------------------------------------------------------------------\n",
    "### Explanation of LTRIM() Function:\n",
    "- This function in MySQL is used to remove leading spaces from a string.\n",
    "\n",
    "#### Syntax :\n",
    "```\n",
    "LTRIM(str)\n",
    "```\n",
    "\n",
    "#### Parameter :\n",
    "- str – The string from which we want to remove leading spaces.\n",
    "\n",
    "#### Returns :\n",
    "- It returns a string after truncating all leading spaces.\n",
    "\n",
    "###### ------------------------------------------------------------------------------------------------\n",
    "\n",
    "### Explanation of RTRIM() Function:\n",
    "- This function in MySQL is used to remove trailing spaces from a string.\n",
    "\n",
    "#### Syntax :\n",
    "```\n",
    "RTRIM(str)\n",
    "```\n",
    "\n",
    "#### Parameter :\n",
    "- str – The string from which we want to remove trailing spaces.\n",
    "\n",
    "#### Returns :\n",
    "- It returns a string after truncating all trailing spaces."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0a994ba7",
   "metadata": {},
   "source": [
    "### 7. Write an SQL query that fetches the unique values of DEPARTMENT from Worker table and prints its length.\n",
    "- This query gives us just count of all the unique values means a single value is returnted by this query.\n",
    "\n",
    "```\n",
    "SELECT COUNT(DISTINCT DEPARTMENT) FROM ORG.Worker;\n",
    "```\n",
    "------------------------\n",
    "- This query gives us count value of each column's unique entry. \n",
    "```\n",
    "SELECT DISTINCT LENGTH(DEPARTMENT) FROM ORG.Worker;\n",
    "```\n",
    "-----------------------\n",
    "- This query returns count value of each column's unique entry with column name.\n",
    "```\n",
    "SELECT DISTINCT LENGTH(DEPARTMENT), DEPARTMENT  FROM ORG.Worker;\n",
    "```\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "43c8f5e5",
   "metadata": {},
   "source": [
    "### 8. Write an SQL query to print the FIRST_NAME from Worker table after replacing ‘a’ with ‘A’.\n",
    "```\n",
    "SELECT REPLACE(FIRST_NAME,'a', 'A') FROM ORG.Worker;\n",
    "```\n",
    "\n",
    "\n",
    "#### REPLACE() function\n",
    "- MySQL REPLACE() replaces all the occurrences of a substring within a string.\n",
    "\n",
    "#### Syntax:\n",
    "```\n",
    "REPLACE(str, find_string, replace_with)\n",
    "```\n",
    "#### Arguments:\n",
    "- find_string : A string which is present one or more times within the string str.\n",
    "- replace_with : A string which will replace every time it finds find_string within str\n",
    "\n",
    "#### Note:\n",
    ">- It is a case-sensitive function."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ebf90449",
   "metadata": {},
   "source": [
    "### 9. 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.\n",
    "```\n",
    "SELECT CONCAT(FIRST_NAME, ' ', LAST_NAME) AS FULL_NAME FROM ORG.Worker;\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "430e6dad",
   "metadata": {},
   "source": [
    "### 10. Write an SQL query to print all Worker details from the Worker table order by FIRST_NAME Ascending.\n",
    "```\n",
    "SELECT * FROM ORG.Worker ORDER BY FIRST_NAME ASC;\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3302490e",
   "metadata": {},
   "source": [
    "### 11. Write an SQL query to print all Worker details from the Worker table order by FIRST_NAME Ascending and DEPARTMENT Descending.\n",
    "```\n",
    "SELECT * FROM ORG.Worker \n",
    "ORDER BY FIRST_NAME ASC, DEPARTMENT DESC ;\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "71eaeebd",
   "metadata": {},
   "source": [
    "### 12. Write an SQL query to print details for Workers with the first name as “Vipul” and “Satish” from Worker table.\n",
    "- First Method\n",
    "```\n",
    "SELECT * FROM ORG.Worker \n",
    "WHERE (FIRST_NAME = 'Vipul') or (FIRST_NAME = 'Satish');\n",
    "```\n",
    "\n",
    "- Second Method\n",
    "```\n",
    "SELECT * FROM ORG.Worker \n",
    "WHERE FIRST_NAME in ('Vipul', 'Satish');\n",
    "```\n",
    "\n",
    "------------------------\n",
    "### MySQL IN Condition\n",
    "- The MySQL IN condition is used to reduce the use of multiple OR conditions in a SELECT, INSERT, UPDATE and DELETE statement.\n",
    "\n",
    "#### Syntax:\n",
    "```\n",
    "expression IN (value1, value2, .... value_n);\n",
    "```\n",
    "\n",
    "#### Parameters\n",
    "- value1, value2, ... or value_n: These are the values to test against expression. If any of these values matches expression, then the IN condition will evaluate to true. This is a quick method to test if any one of the values matches expression."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4b5ae2d5",
   "metadata": {},
   "source": [
    "### 13.  Write an SQL query to print details of workers excluding first names, “Vipul” and “Satish” from Worker table.\n",
    "```\n",
    "SELECT * FROM ORG.Worker \n",
    "WHERE FIRST_NAME not in ('Vipul', 'Satish');\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f8e3491f",
   "metadata": {},
   "source": [
    "### 14. Write an SQL query to print details of Workers with DEPARTMENT name as “Admin”.\n",
    "First Method\n",
    "```\n",
    "SELECT * FROM ORG.Worker \n",
    "WHERE DEPARTMENT ='Admin';\n",
    "```\n",
    "\n",
    "Second Method\n",
    "```\n",
    "SELECT * FROM ORG.Worker \n",
    "WHERE DEPARTMENT like 'Admin';\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "57035840",
   "metadata": {},
   "source": [
    "### 15. Write an SQL query to print details of the Workers whose FIRST_NAME contains ‘a’.\n",
    "```\n",
    "SELECT * FROM ORG.Worker \n",
    "WHERE FIRST_NAME  LIKE '%a%';\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c3a03307",
   "metadata": {},
   "source": [
    "### 16. Write an SQL query to print details of the Workers whose FIRST_NAME ends with ‘a’.\n",
    "```\n",
    "SELECT * FROM ORG.Worker \n",
    "WHERE FIRST_NAME  LIKE '%a';\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b4ee54d4",
   "metadata": {},
   "source": [
    "### 17. Write an SQL query to print details of the Workers whose FIRST_NAME ends with ‘h’ and contains six alphabets.\n",
    "```\n",
    "SELECT * FROM ORG.Worker \n",
    "WHERE FIRST_NAME LIKE '______a';\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dde2ec4e",
   "metadata": {},
   "source": [
    "### 18. Write an SQL query to print details of the Workers whose SALARY lies between 100000 and 500000.\n",
    "- First Method:\n",
    "```\n",
    "SELECT * FROM ORG.Worker \n",
    "WHERE SALARY BETWEEN 100000 AND 900000;\n",
    "```\n",
    "\n",
    "- Second Method\n",
    "```\n",
    "SELECT * FROM ORG.Worker \n",
    "WHERE SALARY >= 100000 AND SALARY <= 900000;\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "066411d8",
   "metadata": {},
   "source": [
    "### 19. Write an SQL query to print details of the Workers who have joined in Feb’2014.\n",
    "```\n",
    "SELECT * FROM ORG.Worker \n",
    "WHERE year(JOINING_DATE)= 2014 and month(JOINING_DATE) = 2;\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a0957bfc",
   "metadata": {},
   "source": [
    "### 20. Write an SQL query to fetch the count of employees working in the department ‘Admin’.\n",
    "\n",
    "```\n",
    "SELECT COUNT(*) FROM ORG.Worker \n",
    "WHERE DEPARTMENT ='Admin'; \n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0c29a37f",
   "metadata": {},
   "source": [
    "### 21. Write an SQL query to fetch worker names with salaries >= 50000 and <= 100000.\n",
    "- First Method\n",
    "\n",
    "```\n",
    "SELECT CONCAT(FIRST_NAME,' ', LAST_NAME ) AS WORK_NAMES, SALARY  FROM ORG.Worker \n",
    "WHERE SALARY >= 50000 AND SALARY <= 100000;\n",
    "``` \n",
    "\n",
    "- Second Method\n",
    "```\n",
    "SELECT CONCAT(FIRST_NAME,' ', LAST_NAME ) AS WORK_NAMES, SALARY  FROM ORG.Worker \n",
    "WHERE SALARY BETWEEN  50000 AND 100000;\n",
    "```\n",
    "\n",
    "- Third Method\n",
    "```\n",
    "SELECT CONCAT(FIRST_NAME, ' ', LAST_NAME) As Worker_Name, Salary\n",
    "FROM ORG.Worker \n",
    "WHERE WORKER_ID IN \n",
    "(SELECT WORKER_ID FROM Worker \n",
    "WHERE Salary BETWEEN 50000 AND 100000);\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ff627127",
   "metadata": {},
   "source": [
    "### 22. Write an SQL query to fetch the no. of workers for each department in the descending order.\n",
    "```\n",
    "SELECT COUNT(DEPARTMENT) AS NO_OF_WORKERS, DEPARTMENT \n",
    "FROM ORG.Worker\n",
    "GROUP BY DEPARTMENT\n",
    "ORDER BY NO_OF_WORKERS DESC;\n",
    "```\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b91a9c0c",
   "metadata": {},
   "source": [
    "### 23.  Write an SQL query to print details of the Workers who are also Managers.\n",
    "\n",
    "```\n",
    "SELECT CONCAT(W.FIRST_NAME,' ', W.LAST_NAME) AS WORKER_NAME, T.WORKER_TITLE\n",
    "FROM Worker W\n",
    "INNER JOIN Title T \n",
    "ON W.WORKER_ID = T.WORKER_REF_ID \n",
    "AND T.WORKER_TITLE IN ('Manager');\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "09fabb35",
   "metadata": {},
   "source": [
    "### 24. Write an SQL query to fetch duplicate records having matching data in some fields of a title table like worker_title, affected_from.\n",
    "```\n",
    "SELECT WORKER_TITLE, AFFECTED_FROM , COUNT(*)\n",
    "FROM Title\n",
    "GROUP BY WORKER_TITLE, AFFECTED_FROM\n",
    "HAVING COUNT(*)>1;\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fcec3f32",
   "metadata": {},
   "source": [
    "### 25. Write an SQL query to show only odd rows from a table.\n",
    "\n",
    "```\n",
    "SELECT * FROM ORG.Worker w WHERE MOD(WORKER_ID,2) <> 0;\n",
    "```\n",
    "\n",
    "- Here MOD(WORKER_ID, 2) <> 0 , IT means that if worker_id is divided by 2 and its reminder is not equal to 0."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "abc47ab8",
   "metadata": {},
   "source": [
    "### 26. Write an SQL query to show only even rows from a table.\n",
    "```\n",
    "SELECT * FROM Worker w \n",
    "WHERE MOD(WORKER_ID ,2)=0;\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "81e71c23",
   "metadata": {},
   "source": [
    "### 27. Write an SQL query to show the current date and time.\n",
    "\n",
    "- Following MySQL query returns the current date:\n",
    "```\n",
    "SELECT CURDATE();\n",
    "```\n",
    "\n",
    "- Following MySQL query returns the current date and time:\n",
    "```\n",
    "SELECT NOW();\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "14d7c528",
   "metadata": {},
   "source": [
    "### 28. Write an SQL query to show the top n (say 10) records of a table order by salary column./ Write an SQL query to show the First n (say 10) records of a table by salary column.\n",
    "```\n",
    "SELECT * FROM Worker w \n",
    "ORDER BY SALARY DESC LIMIT 10;\n",
    "```\n",
    "\n",
    "Second Part:\n",
    "```\n",
    "SELECT * FROM Worker w \n",
    "ORDER BY SALARY LIMIT 10;\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "64dfce71",
   "metadata": {},
   "source": [
    "### 29. Write an SQL query to determine the nth (say n=5) highest salary from a table.\n",
    "```\n",
    "SELECT SALARY FROM Worker\n",
    "ORDER BY SALARY DESC LIMIT 4,1\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3e850e5e",
   "metadata": {},
   "source": [
    "### 30. Write an SQL query to fetch the list of employees with the same salary.\n",
    "```\n",
    "SELECT DISTINCT w.FIRST_NAME, w.WORKER_ID, w.SALARY\n",
    "FROM Worker w , Worker w2 \n",
    "WHERE w.SALARY = w2.SALARY  and w.WORKER_ID != w2.WORKER_ID ;\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1b75be7b",
   "metadata": {},
   "source": [
    "### 31. Write an SQL query to determine the 5th highest salary without using TOP or limit method.\n",
    "\n",
    "```\n",
    "SELECT Salary\n",
    "FROM Worker W1\n",
    "WHERE 4 = (\n",
    " SELECT COUNT( DISTINCT ( W2.Salary ) )\n",
    " FROM Worker W2\n",
    " WHERE W2.Salary >= W1.Salary\n",
    " );\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5f3f539c",
   "metadata": {},
   "source": [
    "### 32. Write an SQL query to show the second highest salary from a table.\n",
    "```\n",
    "SELECT MAX(SALARY) \n",
    "from Worker w \n",
    "where SALARY  NOT IN (\n",
    "SELECT MAX(SALARY)\n",
    "FROM Worker w2 \n",
    ");\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1496442f",
   "metadata": {},
   "source": [
    "### 33. Write an SQL query to show one row twice in results from a table.\n",
    "```\n",
    "select FIRST_NAME, DEPARTMENT from worker W where W.DEPARTMENT='HR' \n",
    "union all \n",
    "select FIRST_NAME, DEPARTMENT from Worker W1 where W1.DEPARTMENT='HR';\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bc0b9488",
   "metadata": {},
   "source": [
    "### 34. Write an SQL query to fetch the first 50% records from a table.\n",
    "\n",
    "```\n",
    "SELECT * FROM Worker w \n",
    "WHERE WORKER_ID <= \n",
    "(SELECT COUNT(WORKER_ID)/2 FROM Worker w2 );\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a886fb95",
   "metadata": {},
   "source": [
    "### 35. Write an SQL query to fetch the departments that have less than five people in it.\n",
    "\n",
    "```\n",
    "SELECT DEPARTMENT, COUNT(WORKER_ID) as 'Number of Workers' \n",
    "FROM Worker \n",
    "GROUP BY DEPARTMENT \n",
    "HAVING COUNT(WORKER_ID)<3;\n",
    "````"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9efac85e",
   "metadata": {},
   "source": [
    "### 36. Write an SQL query to show all departments along with the number of people in there.\n",
    "\n",
    "```\n",
    "SELECT DEPARTMENT, COUNT(WORKER_ID) AS \"Number of workers\"\n",
    "FROM Worker w \n",
    "GROUP BY DEPARTMENT ;\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2f221885",
   "metadata": {},
   "source": [
    "### 37. Write an SQL query to show the last record from a table.\n",
    "- FIRST METHOD:\n",
    "\n",
    "```\n",
    "SELECT * FROM Worker w \n",
    "WHERE WORKER_ID = \n",
    "(SELECT MAX(WORKER_ID) FROM Worker w2 );\n",
    "```\n",
    "\n",
    "- SECOND METHOD:\n",
    "    \n",
    "```\n",
    "SELECT * FROM Worker w \n",
    "ORDER BY WORKER_ID DESC LIMIT 1;\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a5a2ec27",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4622e66f",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}