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.
0 Comments