SQL Course Notes

Introduction to SQL

• Instructor: Mosh Hamedani
• Course Duration: 3 hours
• Target Audience: Beginners and those with basic knowledge of SQL
• Goals: Understanding SQL basics, CRUD operations, database concepts.

Course Overview

• Introduction to SQL (3 mins)
• Installation of tools
• Writing first SQL query
• Topics to Cover:
  • Retrieving, inserting, updating, deleting data
  • Tables, relationships, joins, sub-queries, regex, etc.
• Course includes exercises to reinforce learning.
• Cheat sheet provided in description.

Understanding Databases

• Database: Collection of data stored for easy access.
• DBMS (Database Management System): Software to manage databases.
• Types of DBMS:
  1. Relational: Data stored in tables linked via relationships.
     • Uses SQL for querying and modifying data.
     • Examples: MySQL, SQL Server, Oracle.
  2. Non-relational (NoSQL): No tables or relationships; own query language.

SQL Basics

• Pronunciation: SQL ("ess-que-el") vs. SQUEL ("squell").
• SQL was developed at IBM in the 1970s.
• Course mainly focuses on MySQL.

Installing MySQL

For Mac:

1. Download MySQL Community Edition from MySQL.com.
2. Install using DMG file.
3. Set up root password during installation.
4. Install MySQL Workbench for graphical interface.

For Windows:

1. Download MySQL Installer from MySQL.com.
2. Install using the developer default setup.
3. Set up root password as in Mac.

Overview of MySQL Workbench Interface

• Toolbar for SQL code operations.
• Navigator panel for administration and schemas.
• Query editor for writing SQL code.
• Refresh view to see new databases after creation.

Creating Databases

• Use provided SQL files for creating course databases.
• Execute SQL code in Workbench to create necessary structures.

Introduction to Tables and Data

• Tables consist of columns (fields) and rows (records).
• Primary keys uniquely identify records.
• Relationships between tables via foreign keys.

Basic SQL Queries

1. Selecting Data: Use SELECT statement.
   • Syntax: SELECT columns FROM table WHERE condition;
2. Filtering Data: Use WHERE clause.
3. Sorting Data: Use ORDER BY clause.
4. Limiting Results: Use LIMIT clause.

CRUD Operations

Inserting Data

• Syntax: INSERT INTO table (columns) VALUES (values);
• Can insert multiple rows at once.

Updating Data

• Syntax: UPDATE table SET column = value WHERE condition;
• Can update multiple records using a broader condition.

Deleting Data

• Syntax: DELETE FROM table WHERE condition;
• Be cautious with the WHERE clause to avoid deleting all records.

Using Subqueries

• Allows using a SELECT statement within another SQL statement.
• Useful for filtering updates or deletes based on results from another query.

Joins

• Combine data from multiple tables:
  1. Inner Join: Returns records with matching values in both tables.
  2. Left Join: Returns all records from the left table, and matched records from the right.
  3. Right Join: Returns all records from the right table, and matched records from the left.
  4. Cross Join: All combinations of records from both tables.
  5. Self Join: Joining a table to itself to find relationships within the same table.
• Use aliases to simplify queries.

Conclusion

• The course covers both basic and advanced SQL topics including joins, subqueries, and CRUD operations.
• Encouragement to explore further with provided exercises and real-world applications.