Scribd
Upload
27 views3 pages

Solved Queries

The document contains a series of SQL queries designed to retrieve specific information from an employee database. Queries include retrieving employee names from the Sales department, listing employees with their departments and cities, finding departments with no employees, and calculating average salaries. Additional queries focus on employee names with double letters, hiring dates, city employee counts, highest salaries in departments, and total salaries based on certain conditions.

Uploaded by

hend mohamed
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
27 views3 pages

Solved Queries

The document contains a series of SQL queries designed to retrieve specific information from an employee database. Queries include retrieving employee names from the Sales department, listing employees with their departments and cities, finding departments with no employees, and calculating average salaries. Additional queries focus on employee names with double letters, hiring dates, city employee counts, highest salaries in departments, and total salaries based on certain conditions.

Uploaded by

hend mohamed
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Questions:

1) Write a query to retrieve the names of employees who work in


the ‘Sales’ department.
2) Write a query to list the names of employees along with their
department and city, even if the employee has no department
assigned.
3) Write a query to find departments that have no employees.
4) Write a query to display departments where the average salary
exceeds $55,000, showing department name and average salary.
5) Write a query to find employees whose first or last names contain
double letters
6) Write a query to find employees hired in 2020 and whose last
names end with “son”
7) Write a query to list cities and the number of employees working
in each, ordered by count descending.
8) Write a query to find the employee(s) with the highest salary in
each department.
9) Write a query to find the total salary of employees who:
 Work in cities that start with 'S'
 Were hired after Jan 1, 2020
 Have a salary above the company average
1) SELECT e.name
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE d.name = 'Sales';

2) SELECT e.name, d.name AS department, e.city


FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

3) SELECT d.name
FROM departments d
LEFT JOIN employees e ON d.id = e.department_id
WHERE e.id IS NULL;

4) SELECT d.name AS department, AVG(e.salary) AS average_salary


FROM employees e
JOIN departments d ON e.department_id = d.id
GROUP BY d.name
HAVING AVG(e.salary) > 55000;

5) SELECT name
FROM employees
WHERE first_name ~ '(.)\1'
OR last_name ~ '(.)\1';

6) SELECT name
FROM employees
WHERE EXTRACT(YEAR FROM hire_date) = 2020
AND last_name LIKE '%son';

7) SELECT city, COUNT(*) AS num_employees


FROM employees
GROUP BY city
ORDER BY num_employees DESC;

8) SELECT e.name, e.salary, d.name AS department


FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE (e.department_id, e.salary) IN (
SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
);

9) SELECT SUM(salary) AS total_salary


FROM employees
WHERE city LIKE 'S%'
AND hire_date > '2020-01-01'
AND salary > (SELECT AVG(salary) FROM employees);

You might also like