SQL Course Notes

Introduction to SQL

• Instructor: Mosh Hamedani
• Duration: 3 hours
• Objectives:
  • Learn SQL from scratch
  • Fill in gaps for those with basic knowledge
• Skills to be acquired:
  • Retrieve, insert, update, delete data in databases
  • Understand tables, relationships, joins, sub-queries, regular expressions, etc.
• Course includes many exercises for practice.

Overview of Databases

• Definition: A collection of data stored for easy access.
• Database Management System (DBMS): Software to manage databases.
  • Connect to DBMS to execute queries.

Types of Databases

1. Relational Databases: Data stored in tables linked by relationships.
   • Each table represents a specific type of entity (e.g., customers, products).
   • SQL is the language used for relational DBMS.
2. Non-Relational Databases (NoSQL): No tables or relationships, uses different query languages.

Common Relational Database Systems

• MySQL, SQL Server, Oracle
• This course will focus on MySQL.

Installation

Installing MySQL on Mac

1. Visit MySQL.com and download MySQL Community Edition.
2. Install MySQL and set up root user password.
3. Download MySQL Workbench (graphical tool).
4. Create a local connection in Workbench.

Installing MySQL on Windows

1. Download MySQL Installer for Windows and follow the setup wizard.
2. Install MySQL Server and Workbench as above.

Using MySQL Workbench

• Overview of interface:
  • Toolbar for various SQL operations
  • Navigator panel for databases
  • Query editor for writing SQL queries.

Creating Databases

• Use provided SQL files to create databases for practice.
• Execute SQL scripts in Workbench for setup.

Basic SQL Queries

Selecting Data

• Use the SELECT statement to retrieve data from tables.
• Example: SELECT * FROM customers;
• Use WHERE clause for filtering:
  • Example: SELECT * FROM customers WHERE points > 3000;
  • Use ORDER BY for sorting:
    • Example: ORDER BY first_name;

Inserting Data

• Use the INSERT INTO statement:
  • Example: INSERT INTO customers (first_name, last_name) VALUES ('John', 'Doe');
• Insert multiple rows:
  • Example: INSERT INTO customers (first_name) VALUES ('Alice'), ('Bob');

Updating Data

• Use the UPDATE statement:
  • Example: UPDATE customers SET points = points + 15 WHERE birth_date < '1990-01-01';

Deleting Data

• Use the DELETE FROM statement:
  • Example: DELETE FROM customers WHERE id = 1;
  • Be cautious: omitting WHERE deletes all records.

Joins in SQL

• Inner Join: Returns records that have matching values in both tables.
• Left Join: Returns all records from the left table, and matched records from the right.
• Right Join: Returns all records from the right table, and matched records from the left.
• Cross Join: Returns Cartesian product of both tables.
• Self Join: Joins a table with itself.

Subqueries

• Use subqueries within SELECT, INSERT, UPDATE, and DELETE statements for more complex data retrieval.
  • Example: UPDATE invoices SET status = 'Paid' WHERE client_id IN (SELECT id FROM clients WHERE name = 'John Doe');

Using Functions in SQL

• Built-in functions for operations like calculating date, aggregates, etc.
  • Example: LAST_INSERT_ID() to get the last insert ID.

Closing

• Review of learned topics and encouragement to explore more in SQL.
• Links to course materials and further resources.