SQL Sample Database

SQL Sample Database

A sample database is a simplified dataset used to demonstrate SQL concepts like creating tables, querying data, applying constraints, and joining tables. Below is an example of a sample database for a company that manages employees, departments, and projects.

Database Structure

1. Tables Overview

• Departments: Stores department information.
• Employees: Contains details about employees.
• Projects: Information about ongoing projects.
• Assignments: Tracks which employees are assigned to which projects.

SQL Script to Create the Sample Database

-- Create Departments Table
CREATE TABLE Departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL
);

-- Create Employees Table
CREATE TABLE Employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    hire_date DATE NOT NULL,
    salary DECIMAL(10, 2) NOT NULL,
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES Departments(department_id)
);

-- Create Projects Table
CREATE TABLE Projects (
    project_id INT PRIMARY KEY,
    project_name VARCHAR(100) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE,
    budget DECIMAL(15, 2) NOT NULL
);

-- Create Assignments Table
CREATE TABLE Assignments (
    assignment_id INT PRIMARY KEY,
    employee_id INT,
    project_id INT,
    assigned_date DATE NOT NULL,
    hours_worked INT DEFAULT 0,
    FOREIGN KEY (employee_id) REFERENCES Employees(employee_id),
    FOREIGN KEY (project_id) REFERENCES Projects(project_id)
);

Inserting Sample Data

-- Insert Data into Departments Table
INSERT INTO Departments (department_id, department_name) VALUES
(1, 'HR'),
(2, 'IT'),
(3, 'Finance');

-- Insert Data into Employees Table
INSERT INTO Employees (employee_id, first_name, last_name, hire_date, salary, department_id) VALUES
(101, 'John', 'Doe', '2015-01-10', 60000, 1),
(102, 'Jane', 'Smith', '2018-03-15', 75000, 2),
(103, 'Bob', 'Brown', '2020-07-23', 50000, 3),
(104, 'Alice', 'White', '2019-11-12', 80000, 2);

-- Insert Data into Projects Table
INSERT INTO Projects (project_id, project_name, start_date, end_date, budget) VALUES
(201, 'Website Redesign', '2023-01-01', NULL, 50000),
(202, 'Cloud Migration', '2023-06-15', NULL, 150000),
(203, 'Payroll System Upgrade', '2023-04-01', NULL, 30000);

-- Insert Data into Assignments Table
INSERT INTO Assignments (assignment_id, employee_id, project_id, assigned_date, hours_worked) VALUES
(301, 102, 201, '2023-01-15', 120),
(302, 104, 202, '2023-06-20', 200),
(303, 103, 203, '2023-04-05', 80),
(304, 101, 201, '2023-02-01', 60);

Sample Queries

1. List All Employees

SELECT * FROM Employees;

2. List Employees in the IT Department

SELECT first_name, last_name
FROM Employees
WHERE department_id = (SELECT department_id FROM Departments WHERE department_name = 'IT');

3. List All Projects and Their Budgets

SELECT project_name, budget
FROM Projects;

4. Total Hours Worked on Each Project

SELECT p.project_name, SUM(a.hours_worked) AS total_hours
FROM Projects p
JOIN Assignments a ON p.project_id = a.project_id
GROUP BY p.project_name;

5. Employees Assigned to a Specific Project

SELECT e.first_name, e.last_name, p.project_name
FROM Employees e
JOIN Assignments a ON e.employee_id = a.employee_id
JOIN Projects p ON a.project_id = p.project_id
WHERE p.project_name = 'Website Redesign';

6. The department with the Highest Total Salaries

SELECT d.department_name, SUM(e.salary) AS total_salary
FROM Departments d
JOIN Employees e ON d.department_id = e.department_id
GROUP BY d.department_name
ORDER BY total_salary DESC
LIMIT 1;

7. Projects That Have Not Ended

SELECT project_name, start_date
FROM Projects
WHERE end_date IS NULL;

8. Average Salary by Department

SELECT d.department_name, AVG(e.salary) AS avg_salary
FROM Departments d
JOIN Employees e ON d.department_id = e.department_id
GROUP BY d.department_name;

Visualization of Data

Departments Table

department_id	department_name
1	HR
2	IT
3	Finance

Employees Table

employee_id	first_name	last_name	hire_date	salary	department_id
101	John	Doe	2015-01-10	60000	1
102	Jane	Smith	2018-03-15	75000	2
103	Bob	Brown	2020-07-23	50000	3
104	Alice	White	2019-11-12	80000	2

Projects Table

project_id	project_name	start_date	end_date	budget
201	Website Redesign	2023-01-01	NULL	50000
202	Cloud Migration	2023-06-15	NULL	150000
203	Payroll System Upgrade	2023-04-01	NULL	30000

Assignments Table

assignment_id	employee_id	project_id	assigned_date	hours_worked
301	102	201	2023-01-15	120
302	104	202	2023-06-20	200
303	103	203	2023-04-05	80
304	101	201	2023-02-01	60

Conclusion

This sample database provides a solid foundation for practicing SQL queries. It includes relationships, constraints, and real-world use cases. You can expand it by adding more tables or data as needed for more complex queries.