SQL Training Summary by Joy Blue

Contact Information

• Various contact details if you need to reach out or access other training videos by the instructor.

Overview of Training

• Duration: Approximately 30-40 minutes
• Content:
  • Introduction to SQL
  • Creating a database and table
  • Inserting data
  • Selecting data
  • Using WHERE clauses
  • Updating data
  • Deleting data
  • Modifying tables
  • Primary keys
  • Indexes
  • Creating and joining tables
  • Advanced features: Functions and GROUP BY

Introduction to SQL

• SQL: Structured Query Language.
• Purpose: Programming language to interact with databases.

Types of Databases

• SQL Server: The focus of this tutorial.
• Others: Oracle, MySQL, PostgreSQL, SQLite, DB2.

Tools for Writing SQL

• SQL Server Management Studio (SSMS): Used for this training.
• Others: SSMS alternatives, SQL Developer (Oracle), SQL Workbench (MySQL), Toad.

Getting Started with SQL Server Management Studio (SSMS)

1. Connecting to SQL Server: Localhost, named instances (e.g., SQL 2016).
2. Object Explorer: Navigate and manage databases.
3. Creating a Database: Using CREATE DATABASE statement.

Creating and Managing Tables

1. Creating a Table: Using CREATE TABLE statement.
   • Example: CREATE TABLE customer (firstName VARCHAR(50), lastName VARCHAR(50), age INT);
2. Inserting Data: Using INSERT INTO statement.
   • Example: INSERT INTO customer (firstName, lastName, age) VALUES ('Joey', 'Blue', 40);
3. Selecting Data: Using SELECT statement.
   • Example: SELECT * FROM customer;
   • Filtering: Using WHERE clause (e.g., WHERE firstName = 'Mike').*

Advanced Data Manipulation

1. Updating Data: Using UPDATE statement with WHERE clause.
   • Example: UPDATE customer SET age = 35 WHERE firstName = 'Joey';
2. Deleting Data: Using DELETE statement with WHERE clause.
   • Warning: DELETE without WHERE will delete all data in the table.
3. Dropping Tables: Using DROP TABLE statement.

Modifying Tables

1. Adding Columns: Using ALTER TABLE statement.
   • Example: ALTER TABLE customer ADD city VARCHAR(50);
2. Updating Specific Columns: Example: UPDATE customer SET city = 'New York';
3. Primary Keys: Creating unique identifiers for rows in a table.
   • Example: CREATE TABLE customer (ID INT PRIMARY KEY IDENTITY(1,1), firstName VARCHAR(50), lastName VARCHAR(50), age INT, city VARCHAR(50));

Creating and Managing Multiple Tables

1. Orders Table:
   • Example: CREATE TABLE orders (orderID INT PRIMARY KEY IDENTITY(1,1), customerID INT, productID INT, orderDate DATETIME);
2. Products Table:
   • Example: CREATE TABLE product (productID INT PRIMARY KEY IDENTITY(1,1), productName VARCHAR(50), price FLOAT);

Foreign Keys and Referential Integrity

1. Creating Foreign Keys:
   • ALTER TABLE orders ADD FOREIGN KEY (customerID) REFERENCES customer(ID);
   • ALTER TABLE orders ADD FOREIGN KEY (productID) REFERENCES product(ID);
2. Ensuring Data Integrity: Prevents invalid data entries in related tables.

Joining Tables

• INNER JOIN: Combines rows from different tables based on a related column.
  • Example: SELECT o.orderDate, p.productName, p.price, c.firstName, c.lastName FROM orders o INNER JOIN product p ON o.productID = p.productID INNER JOIN customer c ON o.customerID = c.ID;

Functions and Aggregations

1. Summing and Averaging:
   • Example: SELECT SUM(price) AS total FROM orders;
   • Using GROUP BY for grouped aggregations.
2. Aliasing: Simplifies table and column references (e.g., product AS p).

Final Remarks

• Further Learning: Instructor has additional training material available online (e.g., a 10-hour detailed course).
• Comments and Feedback: Encouraged for continuous improvement.