Chapter 1: Understanding SQL

What is SQL? SQL stands for Structured Query Language, a specialized programming language crafted for managing and manipulating relational databases. It serves multiple purposes, including defining the architecture of a database, creating and modifying elements such as tables and views, and retrieving data.

A SQL command represents a statement that executes a specific action within a database. These commands are generally classified into four primary categories.

SQL Command Categories

1. Data Manipulation Language (DML): This involves commands that manage data within a table. Common DML commands include:
   • SELECT (to retrieve data)
   • INSERT (to add new records)
   • UPDATE (to modify existing records)
   • DELETE (to remove records)
2. Data Definition Language (DDL): These commands are focused on defining the structure of database objects, including:
   • CREATE (to create new tables or views)
   • ALTER (to change existing structures)
   • DROP (to delete structures)
3. Data Control Language (DCL): DCL commands manage access to the database, including:
   • GRANT (to give users specific permissions)
   • REVOKE (to take away permissions)
4. Transaction Control Language (TCL): These commands manage database transactions, such as:
   • ROLLBACK (to undo transactions)
   • SAVEPOINT (to create a rollback point)
   • COMMIT (to save transactions permanently)

Now, let’s shift our focus to SQL clauses and operators that are primarily used for Data Analysis and Manipulation, categorized under DML.

Key SQL Clauses for Data Manipulation

#### SELECT Clause

The SELECT clause is employed to extract data from one or more tables. For instance:

SELECT student_id, score

FROM students;

This command retrieves the student_id and score columns from the students table.

#### DISTINCT Keyword

The DISTINCT keyword, used with SELECT, ensures that only unique values are returned, eliminating duplicates. For example:

SELECT DISTINCT score

FROM students;

This query yields only unique scores from the students table.

#### WHERE Clause

The WHERE clause filters data based on specified conditions, utilizing comparison operators:

• Greater than (>)
• Greater than or equal to (>=)
• Less than (<)
• Less than or equal to (<=)
• Equal to (=)
• Not equal to (<> or !=)

Example:

SELECT student_id, score

FROM students

WHERE score > 70;

This retrieves records for students who scored over 70 points.

#### Logical Operators for Combining Conditions

• AND: All conditions must be true.
• OR: At least one condition must be true.
• NOT: Inverts a condition.

Example:

SELECT student_id, score

FROM students

WHERE score > 70 AND score < 90;

This fetches students whose scores are between 70 and 90.

#### LIMIT

The LIMIT clause restricts the number of rows returned, optimizing screen space and query speed.

SELECT *

FROM students

LIMIT 12;

This returns the first 12 rows from the students table.

#### IN Operator

The IN operator is used with a WHERE clause to filter data against a list of specified values.

SELECT student_id, score

FROM students

WHERE city IN ("London", "Berlin");

This returns students from either London or Berlin.

#### BETWEEN Operator

The BETWEEN operator filters data within a specified range in a column.

SELECT student_id, score

FROM students

WHERE points BETWEEN 80 AND 90;

This retrieves students whose scores range from 80 to 90.

#### LIKE Operator

The LIKE operator filters data based on patterns.

SELECT student_id, score

FROM students

WHERE email LIKE "%@gmail.com";

This command retrieves students with Gmail addresses.

#### IS NULL Operator

The IS NULL operator filters for rows with missing values.

SELECT student_id, score

FROM students

WHERE email IS NULL;

This yields students without email information.

#### ORDER BY

The ORDER BY clause sorts results in ascending or descending order based on one or more columns.

SELECT student_id, score

FROM students

ORDER BY score;

This sorts students by score in ascending order. To sort in descending order, use ORDER BY score DESC.

#### CASE

The CASE expression adds conditional logic to queries.

SELECT student_id, score,

CASE

WHEN score > 90 THEN 'A'

WHEN score > 80 THEN 'B'

ELSE 'Low'

END AS scores_group

FROM students;

This creates a new column scores_group based on the students' scores.

SQL Joins

SQL Joins combine rows from two or more tables based on a common column. They facilitate data extraction from multiple tables for complex queries.

#### Types of SQL Joins

• INNER Join
• LEFT Join
• RIGHT Join
• FULL OUTER Join
• CROSS Join

INNER JOIN

The INNER JOIN fetches data from interconnected tables based on defined conditions, returning only matching rows.

SELECT students.student_id, subject_id

FROM students

INNER JOIN subjects

ON students.student_id = subjects.student_id;

This retrieves rows where student_id matches in both tables.

LEFT JOIN

The LEFT JOIN gathers all rows from the left table, with matching rows from the right table or NULL where there are no matches.

SELECT students.student_id, subject_id

FROM students

LEFT JOIN subjects

ON students.student_id = subjects.student_id;

This ensures all entries from the students table are included.

RIGHT JOIN

The RIGHT JOIN functions inversely, including all rows from the right table while matching from the left.

SELECT students.student_id, subject_id

FROM students

RIGHT JOIN subjects

ON students.student_id = subjects.student_id;

This returns all entries from the subjects table.

FULL OUTER JOIN

The FULL OUTER JOIN retrieves all rows from both tables, regardless of matches.

SELECT students.student_id, subjects.subject_id

FROM students

FULL OUTER JOIN subjects ON students.student_id = subjects.student_id;

This includes all rows, providing NULLs where no matches exist.

#### UNIONS

The UNION operation combines data from multiple tables or queries with similar structures, removing duplicates.

SELECT name, email

FROM students

UNION

SELECT name, email

FROM subjects;

This merges data from both tables into a unified dataset.

#### GROUP BY Clause

The GROUP BY clause groups rows by specified column values, computing summary statistics for each group.

Example:

SELECT student_id, MAX(score), MIN(score)

FROM students

GROUP BY age;

This groups students by age, calculating the maximum and minimum scores.

#### HAVING Clause

The HAVING clause filters grouped data based on aggregate function results, used alongside GROUP BY.

SELECT department, SUM(scholarship_amount) AS total_scholarship

FROM students

GROUP BY department

HAVING SUM(scholarship_amount) > 10000;

This includes only departments where the total scholarship exceeds 10,000.

This guide has explored a range of SQL commands and clauses, serving as a valuable resource for both novices and experienced users. Mastering SQL is a continuous journey, filled with opportunities for learning and growth in the world of structured data.