Advanced SQL Queries

Main Tables: Employees, Products, Customers, and Orders

Here are the main tables used for many of the queries below:

1. Subqueries

A subquery is a query nested inside another query. It's used to fetch results that will be used by the main query. Example:

SELECT name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

This query retrieves the names of employees whose salary is greater than the average salary.

Additional Subquery Example:

SELECT product_name FROM products WHERE price > (SELECT AVG(price) FROM products);

This query retrieves all products that are priced above the average product price.

2. JOINS

Advanced SQL queries often involve joining multiple tables to retrieve meaningful results. Example:

SELECT customers.name, orders.order_id FROM customers 
JOIN orders ON customers.customer_id = orders.customer_id;

This query retrieves customer names along with their respective order IDs by joining the `customers` and `orders` tables.

Additional Join Example:

SELECT employees.name, departments.department_name FROM employees 
JOIN departments ON employees.department_id = departments.department_id;

This query retrieves employee names along with their respective department names by joining the `employees` and `departments` tables.

3. Views

A SQL view is a virtual table based on the result-set of a query. Example:

CREATE VIEW employee_salaries AS 
SELECT name, salary FROM employees;

This query creates a view that shows employee names and their respective salaries.

Using the View Example:

SELECT * FROM employee_salaries;

This query retrieves data from the `employee_salaries` view.

4. Indexes

Indexes are used to speed up the retrieval of rows by using a pointer. Example:

CREATE INDEX idx_customer_name ON customers (name);

This query creates an index on the `name` column of the `customers` table to speed up queries that filter or sort by name.

Using Index in a Query Example:

SELECT * FROM customers WHERE name = 'John Doe';

This query retrieves all records where the customer’s name is 'John Doe' using the created index for faster lookup.

5. Aggregate Functions

SQL provides several aggregate functions to perform calculations on sets of values. Example using `COUNT`:

SELECT COUNT(*) FROM orders WHERE order_total > 1000;

This query counts the number of orders with an order total greater than $1000.