SQL Queries

SQL Queries

SQL (Structured Query Language) is used to interact with relational databases.

Basic SQL Syntax

SELECT Statement

Basic Form:

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Example:

SELECT name, age
FROM Student
WHERE age > 20;

Aggregate Functions

Common Aggregates:

• COUNT(): Count rows
• SUM(): Sum of values
• AVG(): Average of values
• MAX(): Maximum value
• MIN(): Minimum value

Example:

SELECT COUNT(*), AVG(age)
FROM Student;

GROUP BY

Groups rows with same values.

Example:

SELECT dept, COUNT(*)
FROM Student
GROUP BY dept;

HAVING

Filters groups (used with GROUP BY).

Example:

SELECT dept, COUNT(*)
FROM Student
GROUP BY dept
HAVING COUNT(*) > 10;

Joins

INNER JOIN

Returns matching rows from both tables.

Syntax:

SELECT *
FROM table1
INNER JOIN table2
ON table1.key = table2.key;

Example:

SELECT s.name, e.grade
FROM Student s
INNER JOIN Enrollment e
ON s.sid = e.sid;

LEFT JOIN (LEFT OUTER JOIN)

Returns all rows from left table and matching rows from right.

Example:

SELECT s.name, e.grade
FROM Student s
LEFT JOIN Enrollment e
ON s.sid = e.sid;

RIGHT JOIN (RIGHT OUTER JOIN)

Returns all rows from right table and matching rows from left.

FULL OUTER JOIN

Returns all rows from both tables.

CROSS JOIN

Cartesian product of two tables.

Subqueries

Subquery is a query within another query.

Scalar Subquery

Returns single value.

Example:

SELECT name
FROM Student
WHERE age > (SELECT AVG(age) FROM Student);

Correlated Subquery

Subquery references outer query.

Example:

SELECT s.name
FROM Student s
WHERE EXISTS (
    SELECT 1
    FROM Enrollment e
    WHERE e.sid = s.sid
);

IN Subquery

Example:

SELECT name
FROM Student
WHERE sid IN (
    SELECT sid
    FROM Enrollment
    WHERE grade = 'A'
);

Set Operations

UNION

Combines results (removes duplicates).

Example:

SELECT name FROM Student
UNION
SELECT name FROM Teacher;

UNION ALL

Combines results (keeps duplicates).

INTERSECT

Returns common rows.

EXCEPT (MINUS)

Returns rows in first but not in second.

GATE CS Important Points

1. JOIN Types: Know INNER, LEFT, RIGHT, FULL OUTER joins
2. Aggregates: COUNT, SUM, AVG, MAX, MIN with GROUP BY
3. Subqueries: Scalar, correlated, IN, EXISTS
4. Set Operations: UNION, INTERSECT, EXCEPT
5. Query Optimization: Understand query execution

Practice Tips

1. Write Queries: Practice writing SQL for various scenarios
2. JOIN Practice: Master all join types
3. Subqueries: Practice nested queries
4. Aggregates: Use GROUP BY and HAVING
5. Previous Year Questions: Solve GATE SQL questions