Overview
This lecture introduces the fundamentals of SQL querying, focusing on selecting, filtering, sorting, joining, and aggregating data from tables, essential for business intelligence tasks.
Introduction to SQL Querying
- SQL (Structured Query Language) is used to query and manage data in relational databases.
- Business Intelligence (BI) tools often require SQL to prepare and filter data efficiently for analysis.
- Creating views in SQL allows working with smaller, relevant data sets instead of full tables.
Basic SELECT Statements
- Use
SELECT * FROM table to return all fields and rows from a table.
- Specify specific fields:
SELECT field1, field2 FROM table.
- Use the
AS keyword to create aliases for columns or to rename them in the results.*
Filtering Data with WHERE
- Use
WHERE to filter rows based on conditions, e.g., WHERE weight = 190.
- Operators:
=, >, >=, <, <=, AND, OR.
- Combine multiple conditions with
AND (both must be true) or OR (either can be true).
Filtering Text Fields
- Use
WHERE field = 'value' for exact text matches.
- The
LIKE operator allows pattern matching with % for multiple characters and _ for single characters.
- Use
% to search for values that start, end, or contain certain text._
Other WHERE Operators
IN allows matching multiple exact values: WHERE player_name IN ('name1', 'name2').BETWEEN filters numeric ranges: WHERE weight BETWEEN 180 AND 190.IS NULL or IS NOT NULL checks for empty (null) values.
Sorting Data
- Use
ORDER BY field to sort results in ascending order (default).
- Use
DESC to sort in descending order: ORDER BY field DESC.
Joining Tables
- Use JOINs to combine data from multiple tables based on related columns.
INNER JOIN merges rows with matching values in specified columns.- Table and column aliases (
AS a, AS b) make queries cleaner and easier to read.
Aggregating and Grouping Data
- Aggregate functions:
SUM(), AVG(), COUNT(), etc., are used to calculate totals or averages.
GROUP BY groups results based on one or more columns.HAVING filters aggregated data (applied after GROUP BY), e.g., HAVING AVG(rating) > 85.
Key Terms & Definitions
- SQL (Structured Query Language) — language for managing and querying relational databases.
- SELECT — command to retrieve data from tables.
- WHERE — clause to filter rows based on conditions.
- JOIN — operation to combine rows from multiple tables.
- ALIAS (AS) — renames columns or tables in the result set.
- AGGREGATE FUNCTION — function that performs a calculation on a set of values (e.g., SUM, AVG).
- GROUP BY — clause to group results based on specified columns.
- HAVING — clause to filter groups after aggregation.
- ORDER BY — clause to sort results.
Action Items / Next Steps
- Practice writing basic SQL queries using SELECT, WHERE, ORDER BY, and JOIN.
- Experiment with aggregation using GROUP BY and HAVING.
- Explore a sample database (like the FIFA soccer database) to reinforce concepts.