SQL Lecture Summary

Introduction

• SQL is a critical technical skill for every data analyst.
• Learning SQL can be exhausting due to numerous topics: window functions, joins, group by, having, etc.
• Lecture aims to cover all SQL topics from basics to advanced.
• If aiming to become a successful data analyst, consider WCube Tech's premium mentorship program.
• Importance of SQL: allows interaction with databases.

Basics of SQL

What is SQL?

• Stands for Structured Query Language (SQL).
• Interfaces with databases following SQL principles.

Key SQL Concepts

• Databases and Servers: MySQL, PostgreSQL, MS SQL, Oracle, etc.
• SQL Workbench: Tool to run SQL queries.
• Data Manipulation: INSERT, DELETE, UPDATE, SELECT.
• Schema and Data Organization: CREATE DATABASE, TABLES, and understanding primary keys.

SQL Query Structure

Database Operations

• CREATE DATABASE - Initializes a new database.
• CREATE TABLE - Sets up schema for storing information.
• INSERT INTO - Adds records to tables.
• SELECT - Retrieves data from tables.
• DELETE - Removes records based on conditions.
• UPDATE - Modifies records in tables.

SELECT Statement Variations

• SELECT * FROM table_name - Retrieves all columns.
• SELECT column1, column2 FROM table_name - Retrieves specified columns.
• WHERE Clause - Filters records based on conditions.
• ORDER BY - Sorts records in ascending or descending order.
• LIMIT - Limits the number of returned records.

SQL Functions

Aggregate Functions

• SUM, COUNT, AVG, MIN, MAX - Computes aggregate values from numeric data.
• DISTINCT - Removes duplicates before aggregation.
• ROUND(your_value, 2) - Rounds to a specified number of decimal places.

Text Functions

• LENGTH(column_name) - Returns length of the string in column.
• TRIM(column_name) - Removes whitespace.
• UPPER(column_name) - Converts text to uppercase.
• LOWER(column_name) - Converts text to lowercase.
• REPLACE(column_name, 'old', 'new') - Replaces text.
• CONCAT(column1, ' ', column2) - Joins strings.

Handling NULL Values

• IS NULL - Filters records with NULL values.
• IS NOT NULL - Filters records without NULL values.

Joining Tables

Types of Joins

• INNER JOIN - Returns records with matching values in both tables.
• LEFT JOIN - Returns all records from the left table and matched records from the right table.
• RIGHT JOIN - Returns all records from the right table and matched records from the left table.
• FULL JOIN - Returns records when there is a match in one of the tables.
• SELF JOIN - A table joined with itself.
• CROSS JOIN - Returns the Cartesian product of both tables.

Joining Examples

• Syntax: table1 JOIN table2 ON table1.column = table2.column

Advanced SQL Queries

Subqueries

• Queries nested inside other queries.
• Useful for filtering and aggregating data.
• Example: (SELECT column FROM table WHERE condition) AS new_alias

Common Table Expressions (CTEs)

• Temporary result set which can be referenced within another SELECT, INSERT, UPDATE, or DELETE statement.
• Syntax: WITH cte_name AS (SELECT ... FROM ... WHERE ...) SELECT * FROM cte_name

Window Functions

Types of Window Functions

• ROW_NUMBER() - Assigns each row a unique number based on order specified.
• RANK() - Assigns ranks to rows based on order specified.
• DENSE_RANK() - Similar to RANK() but without gaps in ranking.
• LEAD() - Accesses data in subsequent rows.
• LAG() - Accesses data in previous rows.
• NTILE(n) - Divides data into a specified number of buckets.
• SUM() OVER() - Performs a cumulative sum based on the window defined.

Creating Views

• CREATE VIEW view_name AS SELECT ... FROM ... WHERE ... - Creates virtual table from a query.
• Useful for reusing SQL code and simplifying complex queries.

Case Statements and Conditional Logic

CASE Statement

• Allows for if-else type logic within SQL queries.
• Syntax:

  SELECT column,
      CASE
          WHEN condition THEN result
          ELSE result
      END AS alias
  FROM table;
  

Conclusion

• Covered SQL basics to advanced including functions, joins, subqueries, window functions, and views.
• Importance of SQL for data analysis.
• Upcoming workshop starting 21st July 2024 for becoming a successful data analyst with WCube Tech's mentorship.

---

Thank you!