Queries and Sub Queries

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. 

EXAMPLE FOR SUBQUERIES 

STUDENT TABLE 

 

 

ENROLLMENT TABLE 

 

 

1. WHERE Clause

Q: Find names of students who are enrolled in the 'DBMS' course.

SELECT Name

FROM Students

WHERE StudentID IN (

    SELECT StudentID

    FROM Enrollments

    WHERE Course = 'DBMS'

);

2. SELECT Clause 

Q: Display each student’s name and the total number of courses they are enrolled in.

SELECT Name,

  (SELECT COUNT(*) 

   FROM Enrollments e 

   WHERE e.StudentID = s.StudentID) AS TotalCourses

FROM Students s;

3. FROM Clause 

Q: Show the average number of courses per student.

SELECT AVG(CourseCount) AS AvgCourses

FROM (

    SELECT StudentID, COUNT(*) AS CourseCount

    FROM Enrollments

    GROUP BY StudentID

) AS CourseSummary;