SQL Query Questions
Write a query that pulls the title table and adds a column that says the total amount of titles that each employee has held within the employees database.
SELECT emp_no, title, from_date, to_date, COUNT(title) OVER (PARTITION BY emp_no) AS 'Num_Of_Emp' FROM titles ORDER BY emp_no;
Using the Orders table, write a query that shows all orders for CustomerId 1 on dates 1/1/2021 and 1/2/2021 within the Coffee database.
SELECT * FROM Orders WHERE CustomerId = '1' AND (OrderDate = '2021-1-1' OR OrderDate = '2021-1-2'); /* Alternative*/ SELECT * FROM Orders WHERE CustomerID = 1 AND OrderDate BETWEEN '2021-01-01' AND '2021-01-02';
Using the Orders table, write a query that shows all orders for CustomerId 1 and DrinkId bbb from the Coffee database.
SELECT * FROM Orders WHERE CustomerId = '1' AND DrinkId = 'bbb';
Write a query to get all employee details from the employee's table ordered by the last name in alphabetical order from the HR database.
SELECT * FROM employees ORDER BY last_name ASC; /*Q3 Alternative. - If you don't include ASC in the ORDER BY keyword it will auto-default to ASC*/ SELECT * FROM employees ORDER BY last_name;
Write a query to display the employees table from the HR database.
SELECT * FROM employees; /*alternative*/ SELECT * FROM hr.employees;
Using the Orders table, write a query that shows all orders for CustomerId 2 and 3 within the coffee database.
SELECT * FROM orders WHERE CustomerID = '2' OR CustomerId = '3'; /* Alternative*/ SELECT * FROM orders WHERE CustomerID IN (1,3);
Write a query to get the total number of employees in the employees table for the HR database.
SELECT COUNT(employee_id) AS total_employee_count FROM employees; /*Alternative.(IS NOT NULL isn't needed, but can be included for documentation purposes*/ SELECT COUNT(employee_id) AS 'Total_Employees' FROM employees WHERE employee_id IS NOT NULL;
Write a query to get the maximum and minimum salaries paid to employees within the HR database.
SELECT MAX(salary) AS max_salary, MIN(salary) as min_salary FROM employees;
Write a query to get the average salary paid to employees within the HR database. Round the average salary to 2 decimal places.
SELECT ROUND(AVG(salary), 2) AS average_salary FROM employees;
Write a query to get the total amount of salary paid to all employees from the HR database.
SELECT SUM(salary) AS total_salary FROM employees;
Pull the customer number, customer name, city and state fields; replace null states with address line 2; if address line 2 is null, replace with address line 1 within the classic models database.
SELECT customernumber, customername, city, COALESCE(state, addressline2, addressline1) AS address_state FROM customers;
Pull the customer number, customer name, city, state fields; replace null states with their city for the classic models database.
SELECT customernumber, customername, city, coalesce(state, city) AS city_state FROM customers;
Write a query that compares each emp_no's max salary to their average salary for employees whose max salary is $100,000 or more within the employees database.
SELECT emp_no, MAX(salary) AS emp_max_salary, ROUND(AVG(salary), 2) AS emp_avg_salary FROM salaries GROUP BY emp_no HAVING emp_max_salary >= 100000;
Write a query that compares each emp_no's max salary to their average salary from the employees database.
SELECT emp_no, MAX(salary) AS emp_max_salary, ROUND(AVG(salary), 2) AS emp_avg_salary FROM salaries GROUP BY emp_no;
Write a query when gender is M, replace with male. When gender is not M replace with female for the employee's database.
SELECT emp_no, first_name, last_name, CASE WHEN gender = 'M' THEN 'Male' ELSE 'Female' END AS gender FROM employees;
Write a query that returns each emp_no's salaries listed next to their overall average salary from the employees database.
SELECT emp_no, salary, ROUND(AVG(Salary)OVER(PARTITION BY emp_no),2)AS emp_avg_salary FROM salaries;
Write a query that returns each emp_no's max salary from the employees database.
SELECT emp_no, MAX(salary) AS max_salary FROM salaries GROUP BY emp_no;
Write a query that returns each emp_no's average salary from the employees database.
SELECT emp_no, ROUND(AVG(salary), 2) AS avg_salary FROM salaries GROUP BY emp_no;
Write a query that returns a list of each emp_no's departments from the employees database.
SELECT emp_no, dept_no FROM dept_emp;
Select the employee number, first name, last name, and gender for employees named Mary who are female within the employees database.
SELECT emp_no, first_name, last_name, gender FROM employees WHERE first_name = 'Mary' AND Gender = 'F';
Write a query to show the employee ID, first and last names, job ID, and salaries of the top 10 highest paid employees within the HR database.
SELECT employee_id, first_name, last_name, job_id, salary FROM employees ORDER BY salary DESC LIMIT 10;
Write a query to get the employee ID, first and last name and salaries in descending order (based on salaries) from the employees table within the HR database.
SELECT employee_id, first_name, last_name, salary FROM employees ORDER BY salary DESC;
Write a query to display all of the employee's first names and last names from the HR database.
SELECT first_name, last_name FROM employees;
Write a query to display the name (first_name, last_name) and department ID of all employees in departments 30 or 100 within the HR database.
SELECT first_name, last_name, department_id FROM employees WHERE department_id IN (30 , 100); /*Q12 Alternative.*/ SELECT first_name, last_name, department_id FROM employees WHERE department_id = 30 OR department_id = 100;
Write a query to display the name (first_name, last_name) and hire date for all employees who were hired in 1987 from the HR database.
SELECT first_name, last_name, hire_date FROM employees WHERE hire_date LIKE '1987%'; /*Q14 Alterative*/ SELECT first_name, last_name, hire_date FROM employees WHERE HIRE_DATE BETWEEN '1987-01-01' AND '1987-12-31'; /*Q14 Alterative*/ SELECT first_name, last_name, hire_date FROM employees WHERE HIRE_DATE LIKE '1987-__-__';
Write a query to display the name (first_name, last_name) and salary for all employees whose salary is not in the range $10,000 through $15,000 within the HR database.
SELECT first_name, last_name, salary FROM employees WHERE salary NOT BETWEEN 10000 AND 15000; /*Q11 Alternative.*/ SELECT first_name, last_name, salary FROM employees WHERE salary NOT BETWEEN '10000.00' AND '15000.00'; /*Q11 Alternative.*/ SELECT first_name, last_name, salary FROM employees WHERE salary NOT BETWEEN '1000000' AND '1500000'; /*Q11 Alternative.*/ SELECT first_name, last_name, salary FROM employees WHERE salary < 10000 OR salary > 15000;
Write a query to display the name (first_name, last_name) and salary for all employees whose salary is not in the range $10,000 through $15,000 and are in department 30 or 100 from the HR database.
SELECT first_name, last_name, salary,department_id FROM employees WHERE salary NOT BETWEEN 10000 AND 15000 AND department_id IN (30 , 100);
Select everyone whose name is Elvis and return their hire date, first name and their last name from the employees database.
SELECT hire_date, first_name, last_name FROM employees WHERE first_name = 'Elvis';
Write a query to get the number of employees with the same job from the HR database.
SELECT job_id, COUNT(employee_id) AS count_employees FROM employees GROUP BY job_id;
Write a query to get the average salary for each job ID excluding programmer from the HR database.
SELECT job_id, ROUND(AVG(salary), 2) AS average_salary FROM employees WHERE job_id NOT IN ('it_prog') GROUP BY job_id; /*Q17 Alterative*/ SELECT job_id, ROUND(AVG(salary), 2) AS average_salary FROM employees WHERE job_id <> ('it_prog') GROUP BY job_id;
Write a query to display the last name of employees having 'e' as the third character from the HR database.
SELECT last_name FROM employees WHERE last_name LIKE '__e%';
Look at null data in the states column and replace it with N/A or Unknown for the classic model database
SELECT officecode, coalesce(state, 'N/A') as state_NA FROM classicmodels.offices; /*Or*/ SELECT customernumber, COALESCE(state, 'Unknown') AS state_NA FROM classicmodels.customers;
Write a query that returns each emp_no's title along with how many people also hold that title within the employees database.
SELECT emp_no, title, COUNT(emp_no)OVER(PARTITION BY title) AS emp_per_title FROM titles ORDER BY emp_no ASC;
Write a query that returns each emp_no's titles and dates of employment along with how many people also hold that title within the employees database.
SELECT emp_no, title, from_date, to_date, COUNT(emp_no)OVER(PARTITION BY title) AS emp_count FROM titles ORDER BY emp_no ASC; /*Alternatives*/ select emp_no, title, from_date, to_date, count(emp_no) over (partition by title) as total_num_emp from titles order by emp_no; SELECT *, COUNT(emp_no) OVER(PARTITION BY title) AS emp_per_tittle FROM titles ORDER BY emp_no ASC; SELECT emp_no, title, from_date,to_date, COUNT(emp_no) OVER(PARTITION BY title) AS 'total_emp' FROM titles ORDER BY emp_no ASC; SELECT emp_no, title, from_date, to_date, COUNT(*) OVER(PARTITION BY title) AS 'emp_total' FROM titles ORDER BY emp_no ASC;
Write a query to get the unique department IDs from the employees table using the HR database.
SELECT DISTINCT department_id FROM employees; /*Q3 Alternative.*/ SELECT DEPARTMENT_ID FROM employees GROUP BY DEPARTMENT_ID;
Write a query that returns a list of each emp_no's departments, as well a how many total departments they have worked for within the employees database.
SELECT emp_no, dept_no, count(dept_no)OVER(PARTITION BY emp_no) AS dept_count FROM dept_emp ORDER BY emp_no ASC;
Write a query that returns a list of each emp_no's departments, as well a how many total departments they have worked for emp_no's 1-18 within the employees database.
SELECT emp_no, dept_no, count(dept_no)OVER(PARTITION BY emp_no) AS emp_count FROM dept_emp WHERE emp_no BETWEEN 10001 and 10018 ORDER BY emp_no ASC;
Write a query that looks at null data in the states field of the offices classicmodels.table and replace it with N/A
SELECT state, CASE WHEN state IS NULL THEN 'NA' ELSE 'State Given' END AS state_null FROM classicmodels.offices; /* a better query to use for this example would be the coalesce function. coalesce will take the existing column and fill it with N/A.*/ SELECT coalesce(state, 'N/A') as state_NA FROM classicmodels.offices;
Write a query that returns the names of the different titles at this company and how many people hold each title within the employees database.
select title, count(emp_no) as emp_count from titles group by title;
