SQL Course by Mosh Hamedani

Course Overview

• Instructor: Mosh Hamedani
• Duration: 3 hours
• Objective: Learn SQL from scratch to intermediate level.
• Target Audience: Beginners and those with basic knowledge who want to fill gaps.
• Outcomes: Retrieve, insert, update, and delete data. Understand tables, relationships, joins, subqueries, and regular expressions.
• Tools: MySQL, MySQL Workbench

Introduction to Databases

• Database: Collection of data stored in a format that can be easily accessed.
• DBMS (Database Management System): Software to manage databases.
• Types of DBMS:
  • Relational (RDBMS): Stores data in tables linked by relationships.
  • Non-Relational (NoSQL): Does not use tables or relationships.
• Popular RDBMS: MySQL, SQL Server, Oracle.
• SQL (Structured Query Language): Used to query and modify data in RDBMS.

Installing MySQL and MySQL Workbench

• Mac Installation:
  • Download MySQL Community Edition from MySQL.com.
  • Install MySQL using the DMG file.
  • Set admin password for MySQL.
  • Download and install MySQL Workbench.
  • Create a new connection in MySQL Workbench.
• Windows Installation:
  • Download MySQL Community Edition from MySQL.com.
  • Install MySQL using the MySQL Installer.
  • Set admin password for MySQL.
  • Download and install MySQL Workbench.
  • Create a new connection in MySQL Workbench.

Overview of MySQL Workbench

• Interface:
  • Navigator Panel: Contains Administration and Schemas tabs.
  • Query Editor: Write SQL queries.
  • Output Window: Shows results of executed queries.
• Creating Databases:
  • Use SQL scripts to create databases.
  • Example: Create SQL_Store database with tables like customers, orders, products, etc.

Basic SQL Commands

• SELECT: Retrieve data from tables.
  • Syntax: SELECT columns FROM table.
  • Use * to select all columns.
  • Use WHERE clause to filter data.
  • Use ORDER BY to sort data.
• INSERT: Add new rows to tables.
  • Syntax: INSERT INTO table (columns) VALUES (values).
  • Example: INSERT INTO customers (first_name, last_name) VALUES ('John', 'Doe').
• UPDATE: Modify existing data.
  • Syntax: UPDATE table SET column = value WHERE condition.
  • Example: UPDATE customers SET points = points + 10 WHERE customer_id = 1.
• DELETE: Remove rows from tables.
  • Syntax: DELETE FROM table WHERE condition.
  • Example: DELETE FROM customers WHERE customer_id = 1.*

Advanced SQL Concepts

• Joins:
  • INNER JOIN: Combines rows from two tables where the join condition is met.
  • LEFT JOIN: Returns all rows from the left table, and the matched rows from the right table.
  • RIGHT JOIN: Returns all rows from the right table, and the matched rows from the left table.
  • FULL JOIN: Returns all rows when there is a match in either left or right table.
• Subqueries: Nested queries used within another SQL query.
  • Example: SELECT * FROM customers WHERE customer_id IN (SELECT customer_id FROM orders).
• Aggregate Functions: Perform calculations on multiple rows of a table.
  • Examples: COUNT(), SUM(), AVG(), MIN(), MAX().
• Group By: Groups rows that have the same values in specified columns into summary rows.
  • Example: SELECT state, COUNT(*) FROM customers GROUP BY state.
• Having: Used to filter groups based on aggregate functions.
  • Example: SELECT state, COUNT(*) FROM customers GROUP BY state HAVING COUNT(*) > 1.
• Indexes: Improve the speed of data retrieval operations.
  • Example: CREATE INDEX idx_customer_name ON customers (first_name, last_name).
• Transactions: Sequence of one or more SQL operations treated as a single unit.
  • Example: START TRANSACTION; ... COMMIT;.

Working with Data in SQL

• Filtering Data: Using WHERE, AND, OR, NOT, BETWEEN, IN, LIKE, IS NULL.
  • Example: SELECT * FROM customers WHERE state = 'CA' AND points > 1000.
• Sorting Data: Using ORDER BY clause.
  • Example: SELECT * FROM customers ORDER BY first_name ASC.
• Joining Multiple Tables: Using multiple JOIN clauses.
  • Example: SELECT orders.order_id, customers.first_name FROM orders JOIN customers ON orders.customer_id = customers.customer_id.
• Using Aliases: Shorten table and column names for readability.
  • Example: SELECT o.order_id, c.first_name FROM orders o JOIN customers c ON o.customer_id = c.customer_id.

Exercises and Practical Applications

• Exercises: Various exercises to practice SQL queries.
  • Insert multiple rows.
  • Update specific records.
  • Delete records based on conditions.
  • Join multiple tables to retrieve comprehensive data.
  • Use subqueries to filter and update data.

Conclusion

• Resources: Complete SQL course available on Mosh's website.
• Cheat Sheet: Summary notes and cheat sheet provided.
• Feedback and Support: Encouragement to support the course by liking and sharing.
• Advanced Topics: Further topics available in the full course including database design, security, advanced queries, and more.