10.2 SQL basics for data retrieval and manipulation

SQL is the key to unlocking data in databases. It lets you grab specific info, filter results, and combine data from different tables. With SQL, you can pull out exactly what you need and organize it just how you want.

Knowing SQL basics gives you the power to dig into databases and find stories. You'll learn how to retrieve, sort, and manipulate data, making it easier to spot trends and uncover insights for your reporting.

Basic SQL Queries

Retrieving Data with SELECT

• SQL (Structured Query Language) provides a standard way to store, manipulate, and retrieve data from relational databases
• The SELECT statement retrieves data from one or more tables, allowing you to specify columns, tables, and optional filtering conditions
• The FROM clause specifies the table(s) from which data should be retrieved, followed by the table name(s)

Filtering and Sorting Results

• The WHERE clause filters results based on specified conditions, retrieving only rows that meet certain criteria using comparison operators (=, <, >, <=, >=) and logical operators (AND, OR, NOT)
• The ORDER BY clause sorts retrieved data based on one or more columns, specifying the column(s) and sorting order (ASC for ascending, DESC for descending)
• The LIMIT clause restricts the number of rows returned by the query, useful for pagination or retrieving a subset of results (top 10 rows)

Refining Query Results

Selecting Columns and Joining Tables

• The SELECT clause specifies columns to retrieve using the asterisk (*) for all columns or individual column names separated by commas
• The FROM clause specifies the table(s) for data retrieval, separating multiple tables with commas
• Clauses can be combined in a single SELECT statement to refine query results according to specific requirements (filtering, sorting, limiting)

Aggregating and Grouping Data

• Aggregate functions (COUNT, SUM, AVG, MAX, MIN) calculate summary values across multiple rows, providing insights into data
• The GROUP BY clause groups rows based on specified columns, often used with aggregate functions to calculate values for each group
• The HAVING clause filters grouped results based on conditions, similar to the WHERE clause but applied after grouping

Data Manipulation with SQL

Inserting and Updating Data

• The INSERT statement inserts new rows into a table, specifying the table name and values for each column
• The INSERT INTO clause is followed by the table name and optional column names, with values provided in the same order if column names are omitted
• The UPDATE statement modifies existing data in a table, updating specific columns of one or more rows based on conditions
• The SET clause specifies columns to be updated and their new values, separated by commas
• The WHERE clause determines which rows should be updated based on specified conditions

Deleting Data

• The DELETE statement removes one or more rows from a table based on specified conditions
• The FROM clause specifies the table from which rows should be deleted
• The WHERE clause determines which rows should be deleted based on conditions, ensuring only intended rows are removed

Combining Data with JOINs

Types of JOINs

• INNER JOIN (or JOIN) returns only rows with matching values in both tables, combining data based on a related column
• LEFT JOIN (or LEFT OUTER JOIN) returns all rows from the left table and matched rows from the right table, using NULL for non-matching columns
• RIGHT JOIN (or RIGHT OUTER JOIN) returns all rows from the right table and matched rows from the left table, using NULL for non-matching columns
• FULL OUTER JOIN returns all rows from both tables, including matched and unmatched rows, using NULL for non-matching columns

Specifying Join Conditions

• The ON clause specifies the condition that determines how tables are related, defining the column(s) on which tables should be joined
• The USING clause simplifies the JOIN syntax when the join condition involves columns with the same name in both tables, specifying the common column name