Complete 12-Day Roadmap to Learning SQL

Sample Database Schema

CREATE DATABASE CompanyDB;
USE CompanyDB;

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50)
);

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT,
    hire_date DATE,
    salary DECIMAL(10, 2),
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

CREATE TABLE projects (
    project_id INT PRIMARY KEY,
    project_name VARCHAR(50),
    start_date DATE,
    end_date DATE
);

CREATE TABLE employee_projects (
    employee_id INT,
    project_id INT,
    PRIMARY KEY (employee_id, project_id),
    FOREIGN KEY (employee_id) REFERENCES employees(employee_id),
    FOREIGN KEY (project_id) REFERENCES projects(project_id)
);

INSERT INTO departments (department_id, department_name) VALUES
(1, 'HR'),
(2, 'Sales'),
(3, 'Engineering'),
(4, 'Marketing');

INSERT INTO employees (employee_id, first_name, last_name, department_id, hire_date, salary) VALUES
(1, 'John', 'Doe', 2, '2015-03-01', 60000),
(2, 'Jane', 'Smith', 3, '2016-04-22', 80000),
(3, 'Michael', 'Brown', 4, '2017-05-10', 75000),
(4, 'Emily', 'Davis', 1, '2018-06-30', 65000);

INSERT INTO projects (project_id, project_name, start_date, end_date) VALUES
(1, 'Project Alpha', '2019-01-01', '2019-12-31'),
(2, 'Project Beta', '2020-02-01', '2020-11-30');

INSERT INTO employee_projects (employee_id, project_id) VALUES
(1, 1),
(2, 2),
(3, 1),
(4, 2);

If you want download more data please go this website. Link

Day 1: Introduction to Databases

• Concept of Databases:
• Learn what a database is and why it’s essential for data management.
• Example: The CompanyDB database contains tables for employees, departments, projects, and their relationships.
• Relational Databases and SQL:
• Understand relational databases and how SQL is used to interact with them.
• Basic Structure of SQL Queries:

  SELECT column1, column2
  FROM table_name
  WHERE condition;

Day 2: Basic SQL Syntax

• SQL Syntax: Statements, clauses, and keywords.
• SELECT Statement:

  SELECT first_name, last_name
  FROM employees;

• Conditions and Filters:

  SELECT first_name, last_name
  FROM employees
  WHERE department_id = 2;

Day 3: Retrieving Data from Multiple Tables

• Joins:
• INNER JOIN: SELECT e.first_name, e.last_name, d.department_name FROM employees e INNER JOIN departments d ON e.department_id = d.department_id;
• LEFT JOIN: SELECT e.first_name, e.last_name, d.department_name FROM employees e LEFT JOIN departments d ON e.department_id = d.department_id;
• RIGHT JOIN:
  sql SELECT e.first_name, e.last_name, d.department_name FROM employees e RIGHT JOIN departments d ON e.department_id = d.department_id;

Day 4: Aggregate Functions

• COUNT, SUM, AVG, MIN, MAX:

  SELECT COUNT(employee_id) AS total_employees
  FROM employees;

  SELECT department_id, SUM(salary) AS total_salary
  FROM employees
  GROUP BY department_id;

  SELECT AVG(salary) AS average_salary
  FROM employees;

  SELECT MIN(salary) AS minimum_salary, MAX(salary) AS maximum_salary
  FROM employees;

Day 5: Subqueries

• Subqueries in SELECT, WHERE, FROM Clauses:

  SELECT first_name, last_name
  FROM employees
  WHERE department_id IN (SELECT department_id FROM departments WHERE department_name = 'Engineering');

  SELECT department_name
  FROM departments
  WHERE department_id = (SELECT department_id FROM employees WHERE first_name = 'John' AND last_name = 'Doe');

Day 6: Data Manipulation Language (DML)

• INSERT, UPDATE, DELETE:

  INSERT INTO employees (first_name, last_name, department_id, hire_date, salary)
  VALUES ('Mark', 'Johnson', 3, '2019-07-01', 70000);

  UPDATE employees
  SET salary = 75000
  WHERE first_name = 'Mark' AND last_name = 'Johnson';

  DELETE FROM employees
  WHERE first_name = 'Mark' AND last_name = 'Johnson';

Day 7: Data Definition Language (DDL)

• CREATE TABLE, ALTER TABLE, DROP TABLE:

  CREATE TABLE managers (
    manager_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
  );

  ALTER TABLE employees
  ADD email VARCHAR(100);

  DROP TABLE managers;

Day 8: Data Control Language (DCL)

• GRANT, REVOKE:

  GRANT SELECT, INSERT ON employees TO user_name;

  REVOKE SELECT, INSERT ON employees FROM user_name;

Day 9: Transactions

• COMMIT, ROLLBACK:

  BEGIN TRANSACTION;
  INSERT INTO employees (first_name, last_name, department_id, hire_date, salary)
  VALUES ('Lisa', 'Williams', 4, '2020-01-15', 68000);
  COMMIT;

  BEGIN TRANSACTION;
  DELETE FROM employees
  WHERE first_name = 'Lisa' AND last_name = 'Williams';
  ROLLBACK;

Day 10: Views

• Create, Modify, Drop Views:

  CREATE VIEW high_salary_employees AS
  SELECT first_name, last_name, department_id, salary
  FROM employees
  WHERE salary > 70000;

  ALTER VIEW high_salary_employees AS
  SELECT first_name, last_name, department_id, salary, hire_date
  FROM employees
  WHERE salary > 70000;

  DROP VIEW high_salary_employees;

Day 11: Indexes

• Creating and Managing Indexes:

  CREATE INDEX idx_last_name
  ON employees (last_name);

  DROP INDEX idx_last_name ON employees;

Day 12: Optimization Techniques

• Query Tuning and Normalization:
• Indexing Strategies: Use indexes to speed up data retrieval.
• Avoiding Subqueries: Use JOINs instead of subqueries where possible.
• Normalization: Organize data to reduce redundancy and improve integrity.
• Example:
  sql SELECT first_name, last_name FROM employees WHERE department_id = 3 ORDER BY last_name;

Day 13: Advanced SQL Functions

• String Functions: CONCAT, SUBSTRING, LENGTH.

  SELECT CONCAT(first_name, ' ', last_name) AS full_name
  FROM employees;

  SELECT SUBSTRING(first_name, 1, 3) AS short_name
  FROM employees;

  SELECT LENGTH(first_name) AS name_length
  FROM employees;

• Date Functions: GETDATE, DATEADD, DATEDIFF.

  SELECT GETDATE() AS current_date;

  SELECT DATEADD(year, 1, hire_date) AS next_year
  FROM employees;

  SELECT DATEDIFF(day, hire_date, GETDATE()) AS days_worked
  FROM employees;

Day 14: Advanced SQL Concepts

• Stored Procedures:

  CREATE PROCEDURE GetEmployeeDetails
  @EmployeeID INT
  AS
  BEGIN
    SELECT first_name, last_name, department_id, salary
    FROM employees
    WHERE employee_id = @EmployeeID;
  END;

• Triggers:

  CREATE TRIGGER trgAfterInsert
  ON employees
  AFTER INSERT
  AS


  BEGIN
    PRINT 'Record Inserted';
  END;

• Common Table Expressions (CTEs):

  WITH SalesCTE (employee_id, total_sales) AS
  (
    SELECT employee_id, SUM(salary)
    FROM employees
    GROUP BY employee_id
  )
  SELECT e.first_name, e.last_name, s.total_sales
  FROM employees e
  INNER JOIN SalesCTE s ON e.employee_id = s.employee_id;

By following this detailed 12-day roadmap and using the provided sample database, you can practice each topic and solidify your understanding of SQL.