💾Intro to Database Systems Unit 7 – SQL: Querying and Filtering

SQL is a powerful language for managing and querying relational databases. It enables users to efficiently store, organize, and retrieve large amounts of structured data, making it essential for various industries and careers in data management and analysis. This unit covers fundamental SQL concepts, including basic query structure, filtering techniques, sorting, grouping, and joining tables. It also explores advanced topics like subqueries, window functions, and common table expressions, providing a comprehensive foundation for effective database querying and manipulation.

Study Guides for Unit 7

7.1

SELECT statement fundamentals

2 min read

7.2

Filtering and sorting data

3 min read

7.3

Aggregate functions and grouping

3 min read

What's SQL and Why Should I Care?

SQL (Structured Query Language) is a programming language designed for managing and manipulating relational databases
Enables users to create, modify, and query databases to store, organize, and retrieve large amounts of structured data efficiently
SQL is widely used across various industries (e-commerce, healthcare, finance) for data management and analysis
Learning SQL is valuable for careers in data science, business intelligence, web development, and database administration
SQL provides a standardized way to interact with databases, making it easier to work with different database management systems (MySQL, PostgreSQL, Oracle)
Proficiency in SQL allows for effective data extraction, transformation, and analysis to support data-driven decision making
SQL skills are highly sought after in the job market due to the increasing importance of data in modern businesses

The Basics: SELECT, FROM, and WHERE

```
SELECT
```
is used to specify the columns to retrieve from a database table
- Syntax:
```
SELECT column1, column2, ... FROM table_name;
```
- Use
```
SELECT *
```
  to retrieve all columns from a table
```
FROM
```
is used to specify the table from which to retrieve data
- Syntax:
```
SELECT column1, column2, ... FROM table_name;
```
- The specified table must exist in the database
```
WHERE
```
is used to filter rows based on a specified condition
- Syntax:
```
SELECT column1, column2, ... FROM table_name WHERE condition;
```
- Conditions can include comparison operators (
```
=
```
  ,
```
>
```
  ,
```
<
```
  ,
```
>=
```
  ,
```
<=
```
  ,
```
<>
```
  ) and logical operators (
```
AND
```
  ,
```
OR
```
  ,
```
NOT
```
  )
These three clauses form the foundation of a basic SQL query to retrieve specific data from a database table
SQL is case-insensitive for keywords (SELECT, FROM, WHERE), but case-sensitive for table and column names
Use semicolons (
```
;
```
) to end each SQL statement

Filtering Magic: Comparison and Logical Operators

Comparison operators are used to compare values in the
```
WHERE
```
clause
- Equal to (
```
=
```
  ): Checks if two values are equal
- Greater than (
```
>
```
  ), Less than (
```
<
```
  ): Compares numeric values
- Greater than or equal to (
```
>=
```
  ), Less than or equal to (
```
<=
```
  ): Includes the specified value in the comparison
- Not equal to (
```
<>
```
  or
```
!=
```
  ): Checks if two values are not equal
Logical operators are used to combine multiple conditions in the
```
WHERE
```
clause
- ```
AND
```
  : Returns true if all conditions are true
- ```
OR
```
  : Returns true if at least one condition is true
- ```
NOT
```
  : Negates a condition, returning true if the condition is false
Parentheses can be used to group conditions and control the order of evaluation
The
```
BETWEEN
```
operator is used to check if a value falls within a specified range (inclusive)
- Syntax:
```
WHERE column_name BETWEEN value1 AND value2;
```
The
```
IN
```
operator is used to check if a value matches any value in a list
- Syntax:
```
WHERE column_name IN (value1, value2, ...);
```
The
```
LIKE
```
operator is used for pattern matching with wildcard characters (
```
%
```
for zero or more characters,
```
_
```
for a single character)
- Syntax:
```
WHERE column_name LIKE 'pattern';
```

Sorting Things Out: ORDER BY

ORDER BY

is used to sort the result set based on one or more columns

Syntax:

SELECT column1, column2, ... FROM table_name ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

By default,
```
ORDER BY
```
sorts the result set in ascending order (ASC)
- Use
```
DESC
```
  to sort in descending order
Multiple columns can be specified in the
```
ORDER BY
```
clause, separated by commas
- The result set will be sorted by the first column, then the second column, and so on
```
ORDER BY
```
should be the last clause in the SQL query, after
```
SELECT
```
,
```
FROM
```
,
```
WHERE
```
, and other clauses
Sorting can be performed on numeric, string, or date columns
```
NULL
```
values are typically sorted last in ascending order and first in descending order
Sorting can impact query performance, especially for large result sets, so use it judiciously

Grouping Data: GROUP BY and HAVING

```
GROUP BY
```
is used to group rows based on one or more columns and perform aggregate functions on each group
- Syntax:
```
SELECT column1, aggregate_function(column2) FROM table_name GROUP BY column1;
```
Aggregate functions (COUNT, SUM, AVG, MIN, MAX) are used to perform calculations on groups of rows
- ```
COUNT
```
  : Returns the number of rows in each group
- ```
SUM
```
  : Calculates the sum of values in each group
- ```
AVG
```
  : Calculates the average of values in each group
- ```
MIN
```
  : Returns the minimum value in each group
- ```
MAX
```
  : Returns the maximum value in each group
```
GROUP BY
```
is often used with aggregate functions to summarize data by categories or groups
The
```
HAVING
```
clause is used to filter groups based on a specified condition, similar to the
```
WHERE
```
clause for individual rows
- Syntax:
```
SELECT column1, aggregate_function(column2) FROM table_name GROUP BY column1 HAVING condition;
```
```
HAVING
```
is used with aggregate functions to filter groups, while
```
WHERE
```
is used to filter individual rows before grouping
Columns in the
```
SELECT
```
clause must either be listed in the
```
GROUP BY
```
clause or be used with an aggregate function

Joining Tables: The Real Power of SQL

Joins are used to combine rows from two or more tables based on a related column between them

INNER JOIN

returns only the rows that have matching values in both tables

Syntax:

SELECT columns FROM table1 INNER JOIN table2 ON table1.column = table2.column;

```
LEFT JOIN
```
(or
```
LEFT OUTER JOIN
```
) returns all rows from the left table and the matched rows from the right table, with
```
NULL
```
values for non-matching rows in the right table
- Syntax:
```
SELECT columns FROM table1 LEFT JOIN table2 ON table1.column = table2.column;
```
```
RIGHT JOIN
```
(or
```
RIGHT OUTER JOIN
```
) returns all rows from the right table and the matched rows from the left table, with
```
NULL
```
values for non-matching rows in the left table
- Syntax:
```
SELECT columns FROM table1 RIGHT JOIN table2 ON table1.column = table2.column;
```

FULL OUTER JOIN

returns all rows from both tables, with

NULL

values for non-matching rows in either table

Syntax:

SELECT columns FROM table1 FULL OUTER JOIN table2 ON table1.column = table2.column;

Joins are essential for combining data from multiple tables to answer complex queries and perform data analysis
When joining tables, use table aliases to simplify the query and avoid ambiguity when referring to columns with the same name in different tables
- Syntax:
```
SELECT t1.column1, t2.column2 FROM table1 AS t1 JOIN table2 AS t2 ON t1.column = t2.column;
```

Advanced Filtering Techniques

Subqueries are queries nested within another query to filter, calculate, or manipulate data
- Subqueries can be used in the
```
SELECT
```
  ,
```
FROM
```
  ,
```
WHERE
```
  , and
```
HAVING
```
  clauses
- Syntax:
```
SELECT columns FROM table1 WHERE column1 IN (SELECT column2 FROM table2 WHERE condition);
```
Correlated subqueries are subqueries that reference columns from the outer query, creating a dependency between the two queries
- Correlated subqueries are executed for each row in the outer query
- Syntax:
```
SELECT columns FROM table1 WHERE EXISTS (SELECT 1 FROM table2 WHERE table1.column = table2.column);
```
The
```
EXISTS
```
operator is used to check if a subquery returns any rows, without actually returning the rows themselves
- Syntax:
```
SELECT columns FROM table1 WHERE EXISTS (SELECT 1 FROM table2 WHERE condition);
```

The

CASE

expression is used to perform conditional logic within a SQL query

Syntax:

SELECT columns, CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ELSE default_result END AS alias FROM table;

Window functions (ROW_NUMBER, RANK, DENSE_RANK, LEAD, LAG) are used to perform calculations across a set of rows related to the current row
- Syntax:
```
SELECT columns, window_function() OVER (PARTITION BY column1 ORDER BY column2) AS alias FROM table;
```
Common table expressions (CTEs) are named temporary result sets that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement
- Syntax:
```
WITH cte_name AS (SELECT columns FROM table WHERE condition) SELECT columns FROM cte_name WHERE condition;
```

Practical Applications and Common Pitfalls

SQL is used in various real-world applications, such as:
- Business intelligence and reporting
- Data integration and ETL (Extract, Transform, Load) processes
- Web and mobile application development
- Data analysis and data science projects
When working with SQL, be aware of common pitfalls and best practices:
- Always use explicit
```
JOIN
```
  conditions instead of relying on implicit joins using the
```
WHERE
```
  clause
- Be cautious when using
```
NULL
```
  values in comparisons, as
```
NULL
```
  represents an unknown value and behaves differently than other values
- Avoid using
```
SELECT *
```
  in production queries, as it can impact performance and maintainability; instead, explicitly list the required columns
- Use meaningful aliases for tables and columns to improve query readability
- Optimize queries by using appropriate indexes, limiting the result set size, and avoiding unnecessary joins or subqueries
Test your queries on smaller datasets before running them on large production databases to avoid performance issues or unintended results
Regularly monitor and analyze query performance using tools like
```
EXPLAIN
```
or query profiling to identify and optimize slow or resource-intensive queries
Keep your SQL skills up-to-date by learning about new features and best practices in the specific database management system you are using (MySQL, PostgreSQL, Oracle, etc.)