DATABASE MANAGEMENT SYSTEM

Queries:

A query in SQL is a request for information from a database. The primary SQL command for querying data is the SELECT statement. Here is a basic example:

SELECT column1, column2
FROM table_name
WHERE condition;

• SELECT: Specifies the columns to be retrieved.
• FROM: Specifies the table from which to retrieve the data.
• WHERE: Specifies conditions that filter the rows to be retrieved (optional).

Example:

SELECT first_name, last_name
FROM employees
WHERE department = 'IT';

Sub-Queries:

A sub-query (or inner query) is a query nested within another SQL statement. Sub-queries can be used in various parts of a SQL statement, such as the SELECT, FROM, WHERE, or HAVING clauses. They help to break down complex queries into smaller, more manageable parts.

Sub-queries in WHERE Clause:

SELECT column1, column2
FROM table_name
WHERE column_name operator (SELECT column_name FROM another_table WHERE condition);

Example:

SELECT product_name, price
FROM products
WHERE category_id = (SELECT category_id FROM categories WHERE category_name = 'Electronics');

Sub-queries in FROM Clause:

SELECT column1, column2
FROM (SELECT column1, column2 FROM table_name) AS alias_name;

Example:

SELECT product_name, average_price
FROM (SELECT category_id, AVG(price) AS average_price FROM products GROUP BY category_id) AS avg_prices;

Sub-queries in SELECT Clause:

SELECT column1, column2, (SELECT column3 FROM another_table WHERE condition) AS subquery_result
FROM table_name;

Example:

SELECT employee_name, salary, (SELECT MAX(salary) FROM employees) AS max_salary
FROM employees;

Sub-queries provide a way to retrieve data dynamically based on the results of another query. They can be powerful tools for building flexible and complex SQL queries.