SQL Learning Guide

Project

• Create Database: Employee Report
• Data Cleaning: Change, Update, Modify...
• Data Visualization
• 1. What is the total number of employees, and how many are there in each job title?
• 2. What is the average salary by Job title?
• 3. What is the distribution of experience levels within the company?
• 4. What are the common work settings and their salaries?
• 5. How does employee distribution vary by location?
• 6. What is the salary range within each company size category?

SQL Introduction

• What is SQL?
• Why Learn SQL?

Database Basics

• What is a Database?
• Types of Databases
• How to Install a Database

Database Operations

• Create Database
• Delete Database
• Backup Database
• Use Database

Table Operations

• Create Table
• Insert Data into Table
• Delete Table
• Alter Table
• Update Table

SQL Constraints

• NOT NULL
• UNIQUE
• PRIMARY KEY
• FOREIGN KEY
• CHECK
• DEFAULT
• CREATE INDEX

Advanced Database Features

• Auto Increment
• Views
• Database Hosting

Data Types

• Numeric
• Strings
• Datetime
• Boolean
• JSON

SQL Commands

• Comments in SQL
• Select Statements
  • SELECT
  • SELECT DISTINCT
  • SELECT TOP

SQL Operators

• Arithmetic Operators
• Comparison Operators
• Logical Operators

SQL Functions

• MySQL Functions
  • String Functions
  • Numeric Functions
  • Date Functions

SQL Clauses

• WHERE Clause

Aggregation and Sorting

• Group By
• Order By

Additional Clauses

• HAVING Clause
• LIMIT Clause

SQL Joins

• Inner Join
• Left Join
• Right Join
• Full Join

Combining Queries

• Unions

Conditional Statements

• CASE Statement

Advanced SQL Techniques

• Subqueries
• Window Functions
• Common Table Expressions (CTE)
• Temporary Tables
• Stored Procedures
• Triggers and Events

Descriptions

SQL Introduction

• What is SQL?: Overview of SQL and its purpose in database management.
• Why Learn SQL?: Benefits and applications of learning SQL.

Database Basics

• What is a Database?: Definition and key components of databases.
• Types of Databases: Different types of databases (Relational, NoSQL, etc.).
• How to Install a Database: Steps to install popular databases like MySQL, PostgreSQL, and MongoDB.

Database Operations

• Create Database: SQL command to create a new database.
• Delete Database: SQL command to delete an existing database.
• Backup Database: Methods to back up a database.
• Use Database: Switching and using different databases.

Table Operations

• Create Table: SQL command to create a new table.
• Insert Data into Table: SQL command to insert data into a table.
• Delete Table: SQL command to delete a table.
• Alter Table: SQL command to modify the structure of an existing table.
• Update Table: SQL command to update existing data in a table.

SQL Constraints

• NOT NULL: Ensures that a column cannot have a NULL value.
• UNIQUE: Ensures all values in a column are unique.
• PRIMARY KEY: Uniquely identifies each record in a table.
• FOREIGN KEY: Ensures referential integrity between two tables.
• CHECK: Ensures that all values in a column satisfy a specific condition.
• DEFAULT: Sets a default value for a column.
• CREATE INDEX: Creates an index on a table for faster retrieval.

Advanced Database Features

• Auto Increment: Automatically generates a unique number for a column.
• Views: Virtual tables created from a SQL query.
• Database Hosting: Hosting options for databases (local vs. cloud).

Data Types

• Numeric Data Types: INT, FLOAT, DOUBLE, DECIMAL.
• String Data Types: VARCHAR, CHAR, TEXT.
• Datetime Data Types: DATE, DATETIME, TIMESTAMP.
• Boolean Data Types: BOOLEAN, BIT.
• JSON Data Types: Storing and querying JSON data.

SQL Commands

• Comments in SQL: Adding comments to SQL code.
• Select Statements:
  • SELECT: Retrieve data from a database.
  • SELECT DISTINCT: Retrieve unique records.
  • SELECT TOP: Retrieve the top N records.

SQL Operators

• Arithmetic Operators: +, -, *, /, %.
• Comparison Operators: =, !=, <>, >, <, >=, <=.
• Logical Operators: AND, OR, NOT.

SQL Functions

• MySQL Functions:
  • String Functions: CONCAT, LENGTH, LOWER, UPPER, SUBSTRING.
  • Numeric Functions: AVG, COUNT, MAX, MIN, SUM.
  • Date Functions: NOW, CURDATE, DATEDIFF.

SQL Clauses

• WHERE Clause: Filter records based on conditions (AND, OR, NOT).

Aggregation and Sorting

• Group By: Group records and calculate aggregates (AVG, MAX, MIN, COUNT).
• Order By: Sort records (ASC, DESC).

Additional Clauses

• HAVING Clause: Filter groups based on conditions (difference from WHERE).
• LIMIT Clause: Limit the number of records returned; aliasing columns.

SQL Joins

• Inner Join: Combine records with matching values in both tables.
• Left Join: Include all records from the left table and matched records from the right.
• Right Join: Include all records from the right table and matched records from the left.
• Full Join: Include all records when there is a match in either table.

Combining Queries

• Unions: Combine the result sets of multiple SELECT queries.

Conditional Statements

• CASE Statement: Perform conditional logic in queries.

Advanced SQL Techniques

• Subqueries: Nested queries for complex conditions.
• Window Functions: Perform calculations across a set of table rows.
• Common Table Expressions (CTE): Simplify complex queries.
• Temporary Tables: Store intermediate results in temporary tables.
• Stored Procedures: Reusable SQL code blocks for repeated tasks.
• Triggers and Events: Automate actions based on table changes or scheduled events.