SQL Notes

Introduction

  • SQL is used by companies to interact with their databases and access information, which is crucial for making informed business decisions.

Pronunciation of SQL

  • The correct pronunciation is either "S. Q. L." or "Sequel."

What is SQL?

  • SQL is a query language used to interact with databases.
  • Example: SELECT∗FROMORDERSLIMIT10SELECT * FROM ORDERS LIMIT 10
    • This query retrieves all information from the "orders" database and displays the first 10 items.
  • SQL queries are written in plain English using predefined commands.

Importance of SQL

  • Valuable skill across many roles.
  • Important foundation for other tools.
  • Essential skill for the digital age.

Popularity of SQL

  • High performance.
  • Highly accessible.
  • Highly scalable.
  • Great transactional support.
  • Great security features.
  • Suited for organizations of any size.
  • Open-source.

Why Learn SQL?

  • SQL is widely used by companies like Uber and Netflix.
  • SQL is in high demand, appearing in a significant percentage of data job listings.
  • SQL remains a top language for data work, used by a large percentage of people with jobs in data.

Professions that Benefit from SQL

  • Marketing and Growth
  • Operations and Support
  • Finance
  • Developers
  • Product Managers

Real-Life Examples of SQL Use

  • Finding high-value customer segments.
  • When spreadsheets are insufficient.
  • Unlocking the power of BI tools.
  • Finding bugs.
  • Finding suspicious transactions and fraud.

Relational Database Management Systems (RDBMS) and Databases

  • Examples of RDBMS:
    • Microsoft SQL Server
    • Oracle
    • SAP
    • Sybase
    • DB2
    • MySQL
    • Microsoft Access
    • PostgreSQL

Popular Relational Databases

  • MySQL
  • PostgreSQL
  • SQL Server

MySQL Data Types

  • Data type characteristics:
    • Type of values (fixed or variable).
    • Storage space required.
    • Indexability.
    • Comparison of values.

MySQL Data Types Categories

  • Numeric
  • String
  • Date and Time
  • Binary Large Object (BLOB)
  • Spatial
  • JSON

MySQL Database

  • A database stores a collection of records in an organized manner, using tables, rows, columns, and indexes for efficient information retrieval.
  • Commands include:
    • CREATE DATABASE
    • SELECT DATABASE
    • SHOW DATABASE
    • DROP DATABASE

MySQL Commands

  • Data Definition Language (DDL):
    • CREATE
    • ALTER
    • DROP
    • TRUNCATE
  • Data Manipulation Language (DML):
    • INSERT
    • UPDATE
    • DELETE
    • SELECT
  • Data Control Language (DCL):
    • GRANT
    • REVOKE
  • Transaction Control Language (TCL):
    • COMMIT
    • ROLLBACK

DDL Command: CREATE TABLE

  • Organizes data into rows and columns.
  • Requires:
    • Table name
    • Field names
    • Field definitions

DDL Command: ALTER TABLE

  • Modifies existing tables.
  • Used to:
    • Change table name
    • Change field names
    • Add or delete columns
  • Used with ADD, DROP, and MODIFY commands.

DDL Command: DROP TABLE

  • Deletes an existing table and its data permanently.
  • Requires caution due to data loss.

DDL Command: TRUNCATE TABLE

  • Removes all data from a table while preserving its structure.
  • Cannot be rolled back.
  • Not usable when a table is referenced by a foreign key or participates in an indexed view.

DML Command: INSERT STATEMENT

  • Adds data to a MySQL table.
  • Can insert:
    • Single row
    • Multiple rows

DML Command: UPDATE STATEMENT

  • Modifies data in a MySQL table.
  • Uses SET clause to change column values.
  • Can update single or multiple columns.
  • Used with the WHERE clause.

DML Command: DELETE STATEMENT

  • Removes records from a MySQL table.
  • Deletes full rows.
  • Can delete multiple records with a single query.
  • Can be used with conditions.

DML Command: SELECT STATEMENT

  • Fetches data from one or more tables.
  • Retrieves records from all or specified fields that match specified criteria.
  • Most commonly used SQL query.

MySQL Clauses

  • Keywords or statements to handle information.
  • Includes:
    • FROM
    • WHERE
    • ORDER BY
    • GROUP BY
    • HAVING
    • DISTINCT

FROM Clause

  • Selects records from a table.
  • Can retrieve records from multiple tables using JOIN.
  • Requires at least one table to be selected.
  • Multiple tables are typically joined using joins.

WHERE Clause

  • Specifies a condition for fetching data.
  • Filters records to return only necessary ones.
  • Used in SELECT, UPDATE, and DELETE statements.
  • Uses comparison or logical operators (>, <, =, LIKE, NOT, etc.).

ORDER BY Clause

  • Sorts data in ascending or descending order based on one or more columns.
  • Mixes different kinds of expressions.
  • Expressions do not have to be part of the query output.
  • Can have unlimited expressions separated by commas.

GROUP BY Clause

  • Summarizes data for analysis.
  • Aggregates data based on one or more columns.
  • Example: Summing daily sales by quarter or counting employees in each department.

HAVING Clause

  • Conditional clause used with GROUP BY.
  • Returns rows where aggregate function results match given conditions.
  • Used because WHERE cannot be combined with aggregate results.
  • Restricts data on group records rather than individual records.
  • Can be used with WHERE in a single query.

DISTINCT Clause

  • Removes duplicate records from a table.
  • Fetches only unique records.
  • Used with the SELECT statement.
  • Returns unique values for one expression or unique combinations for multiple expressions.
  • Does not ignore NULL values.

MySQL CASE Expression

  • Part of the control flow function.
  • Writes if-else or if-then-else logic to a query.
  • Can be used in SELECT, WHERE, ORDER BY, etc.
  • Validates conditions and returns the result when the first condition is true.
  • Executes the else block if no condition is true; otherwise, returns NULL.

MySQL Joins

  • Method of linking data between tables based on common column values.
  • Used in the SELECT statement after the FROM clause.
  • Types of joins:
    • INNER JOIN
    • LEFT OUTER JOIN (LEFT JOIN)
    • RIGHT OUTER JOIN (RIGHT JOIN)
    • CROSS JOIN (Cartesian product)

INNER JOIN

  • Joins two tables based on a join predicate.
  • Compares each row from the first table with every row from the second table.
  • Creates a new row if values satisfy the join condition.

LEFT JOIN

  • Returns all records from the left-side table and matched records from the right-side table.
  • Returns NULL if no matching records are found in the right-side table.

RIGHT JOIN

  • Returns all rows from the right-hand table and only those results from the other table that fulfill the join condition.
  • If it finds unmatched records from the left side table, it returns Null value.

CROSS JOIN

  • Combines all possibilities of two or more tables.
  • Returns the Cartesian product of all associated tables.
  • Similar to INNER JOIN without a join condition.

MySQL Window Functions

  • Performs calculations across a set of rows related to the current row.
  • Does not group results into one row, unlike aggregate functions.
  • Performs operations on a set of rows and produces an aggregated value for each row.
  • Introduced in MySQL version 8.

Types of Window Functions

  • Aggregate Functions:
    • Operates on multiple rows and produces a single row result.
    • Examples: COUNT, SUM, AVG, MIN, MAX.
  • Ranking Functions:
    • Ranks each row of a partition in a given table.
    • Examples: RANK, DENSERANK, PERCENTRANK, ROWNUMBER, CUMEDIST.
  • Analytical Functions:
    • Examples: NTILE, LEAD, LAG, NTH, FIRSTVALUE, LASTVALUE.

MySQL Window Functions: Syntax

  • Basic syntax:
    window<em>function</em>name(expression)OVER([partition<em>defintion][order</em>definition])window<em>function</em>name(expression) OVER( [partition<em>defintion] [order</em>definition] )

Ranking Function: ROW_NUMBER()

  • Returns the sequential number for each row within its partition.
  • Starts from 1 to the number of rows in the partition.
  • Not supported before MySQL version 8.0.

Ranking Function: DENSE_RANK()

  • Assigns a rank for every row within a partition without any gaps.
  • Rows are assigned in consecutive order.

Ranking Function: RANK()

  • Assigns a rank for every row within a partition with gaps.
  • If there is a tie between the values, then the rank() function will assign it with the same rank, and the next rank value will be its previous rank plus a number of duplicate numbers.

Analytical Function: LEAD()

  • Allows looking forward at succeeding rows to get the value of that row from the current row.
  • Useful for calculating the difference between the current and subsequent rows.

Analytical Function: LAG()

  • Allows looking backward at preceding rows to get the value of a previous row from the current row.
  • Useful for calculating the difference between the current and the previous row.

Common Table Expression (CTE)

  • Names temporary result sets within the execution scope of a statement.
  • Defined using the WITH clause.
  • Can reference other CTEs defined earlier in the same WITH clause.
  • Execution scope is limited to the particular statement.

CTE Syntax

  • Basic syntax:
    WITHcte<em>name(column</em>names)AS(query)SELECT∗FROMctename;WITH cte<em>name (column</em>names) AS (query) SELECT * FROM cte_name;

Benefits of CTE

  • Provides better readability of the query.
  • Increases the performance of the query.
  • Allows it as an alternative to the VIEW concept.
  • Can be used as chaining of CTE for simplifying the query.

Case Study #1: Danny's Diner

  • This case study involves analyzing customer spending and visit patterns at Danny's Diner.
  • Example questions include:
    • Total amount spent by each customer.
    • Number of days each customer visited the restaurant.
    • First item purchased by each customer.
    • Most purchased item and its purchase count.
    • Most popular item for each customer.
    • Item purchased first after becoming a member.
    • Item purchased just before becoming a member.
    • Total items and amount spent before membership.
    • Points earned by each customer based on spending and sushi purchases.
    • Points earned by customers A and B in the first week after joining the program.

Case Study #2: Foodie-Fi

  • This case study focuses on analyzing customer data for a subscription-based service called Foodie-Fi.
  • Example questions include:
    • Total number of customers Foodie-Fi has ever had.
    • Monthly distribution of trial plan start dates.
    • Plan start date values after the year 2020, broken down by plan name.
    • Customer count and percentage of churned customers.
    • Number and percentage of customers who churned straight after their initial free trial.
    • Number and percentage of customer plans after their initial free trial.
    • Number of customers who upgraded to an annual plan in 2020.
    • Average number of days for a customer to upgrade to an annual plan.
    • Breakdown of this average value into 30-day periods.
    • Number of customers who downgraded from a pro monthly plan to a basic monthly plan in 2020.