SQL Practice Project

4 min readMay 10, 2023

I have been learning and practicing numerous data analytics competencies in Python, SQL, Tableau and Excel. Below is a practice exercise on a small database for a hypothetical organization.

Project Idea: Create a hypothetical organization with employee and customer data. Employees’ details will include an employee ID, first name, last name, age, role and salary. Customer details will include their customer ID, first name, last name, address and telephone number.

First stop involves the creation of a customer table, followed by imputing customer details. Consequently, an employee demographics, salary and contact details. The tables are consequently populated with data for individual employees.

Create Tables (Customers, EmployeeContacts, EmployeeSalary

CREATE TABLE Customers 
(CustomerID INT,
FirstName varchar(50),
LastName varchar(50),
Address varchar(50),
Contact int,
Primary Key (CustomerID),
)

CREATE TABLE EmployeeContacts 
(EmployeeID INT,
FirstName varchar(50),
LastName varchar(50),
Address varchar(50),
Contact int,
JobTitle varchar(50),
Primary Key (EmployeeID),
)

CREATE TABLE EmployeeSalary 
(EmployeeID INT,
JobTitle varchar(50),
Salary INT
Primary Key (EmployeeID),
)

CREATE TABLE EmployeeDemographics
(EmployeeID INT,
FirstName varchar(50),
LastName varchar(50),
Age INT,
Gender Varchar(50),
Primary Key (EmployeeID),
)

Insert Values into Tables

Insert Into Customers VALUES 
(7847, 'Rachel', 'Green' , 'Austin, Texas', '0718169157'),
(7849, 'Chandler', 'Bing', 'Austin, Texas' ,'0718169159'),
(7850, 'Joey', 'Tribianni', 'Austin, Texas','0718169160'),
(7851, 'Phoebe', 'Buffay', 'Austin, Texas', '0718169161'),
(7852, 'Monica', 'Geller', 'Austin, Texas', '0718169162'),
(7853, 'Ross', 'Geller', 'Austin, Texas', '0718169163'),
(7854,'Michael', 'Scott', 'Miami, Florida', '0718169164'),
(7855, 'Sara', 'Tancredi', 'Miami, Florida', '0718169165'),
(7856, 'Theodore', 'Bagwell', 'Miami, Florida', '0718169166'),
(7857, 'Marl', 'Spencer', 'Miami, Florida', '0718169167'),
(7859, 'Luke', 'Meyer', 'Miami, Florida', '0718169168'),
(7860, 'Mary', 'Rock', 'Denver, Colorado', '0718169169'),
(7861, 'Jerry', 'Stone', 'Denver, Colorado', '0718169170'),
(7862, 'Chris', 'Rock', 'Denver, Colorado', '0718169171')


Insert Into EmployeeContacts Values
(2001, 'Laura', 'Scone', 'Austin, Texas', '0724110731', 'HR'),
(2002, 'Julius', 'Maguire', 'Austin, Texas', '0724110732', 'Accountant'),
(2003, 'Lilian', 'Tobby', 'Denver, Colorado', '0724110733', 'Financial Analyst'),
(2004, 'Wesley', 'Akamo', 'Austin, Texas', '0724110734', 'Auditor'),
(2004, 'Malanie', 'Afuwa', 'Austin, Texas', '0724110735', 'Store Manager'),
(2005, 'Fidelis', 'Otieno', 'London', '0724110736', 'Data Analyst'),
(2005, 'Liam', 'Lawson', 'Manchester', '0724110737', 'Supply Manager'),
(2006, 'Rose', 'Shaw', 'Leicester', '0724110738', 'Intern'),
(2007, 'Jimmy', 'Galagher', 'London', '0724110739', 'Driver'),
(2008, 'Daniel', 'Olise', 'London', '0724110740', 'Lawyer'),
(2009, 'Tim', 'Sean', 'London', '0724110741', 'Cleaner'),
(2010, 'Ellaine', 'Susan', 'London', '0724110742', 'Messenger')


Insert Into EmployeeSalary Values 
(2001, 'HR', 50000),
(2002, 'Accountant', 78000),
(2003, 'Financial Analyst', 95000),
(2004, 'Auditor', 92000),
(2004, 'Store Manager', 85000),
(2005, 'Data Analyst', 120000),
(2005, 'Supply Manager', 112000),
(2006, 'Intern' 37000),
(2007, 'Driver', 65000),
(2008, 'Lawyer', 132000),
(2009, 'Cleaner', 56000),
(2010, 'Messenger', 53000)


INSERT INTO EmployeeDemographics VALUES
(2001, 'Laura', 'Scone',29, 'Female'),
(2002, 'Julius', 'Maguire',32, 'Male'),
(2003, 'Lilian', 'Tobby',35, 'Female'),
(2004, 'Wesley', 'Akamo', 42, 'Male'),
(2004, 'Malanie', 'Afuwa', 37, 'Female'),
(2005, 'Fidelis', 'Otieno',45, 'Male'),
(2005, 'Liam', 'Lawson',54, 'Male'),
(2006, 'Rose', 'Shaw', 25, 'Female'),
(2007, 'Jimmy', 'Galagher', 45, 'Male'),
(2008, 'Daniel', 'Olise',45, 'Male'),
(2009, 'Tim', 'Sean', 29, 'Male'),
(2010, 'Ellaine', 'Susan', 37, 'Female')

Insights and Queries from the Tables

The tables with employees’ data have a primary key; the employeeID. Therefore, the various tables can be joined to have a better view of the data. For example, to Join the EmployeeDemographics table on employeeSalary Table to view employeID, FirstName, LastName, Age, Gender and JobTitle:

SELECT EmployeeDemographics.EmployeeID, FirstName, LastName, Age, Gender, JobTitle, Salary
From EmployeeDemographics
Inner join EmployeeSalary
ON EmployeeDemographics. EmployeeID=EmployeeSalary.EmployeeID

Compute the average salary for data analysts

SELECT JobTitle, AVG(Salary)
From EmployeeDemographics
Inner join EmployeeSalary
ON EmployeeDemographics. EmployeeID=EmployeeSalary.EmployeeID
WHERE JobTitle='Data Analyst'
GROUP BY JobTitle

Compute Average salary for Auditors

Highlight Employees over 50 years, earning 50,000 and higher

SELECT EmployeeDemographics.EmployeeID, FirstName, LastName, age, gender, JobTitle,Salary
FROM EmployeeDemographics
join EmployeeSalary
ON EmployeeDemographics.EmployeeID=EmployeeSalary.EmployeeID
WHERE Salary >50000 AND age> 30
ORDER BY Salary ASC

Highlight Employees over 50 years, or earning 30,000 and higher

SELECT EmployeeDemographics.EmployeeID, FirstName, LastName, age, gender, JobTitle,Salary
FROM EmployeeDemographics
join EmployeeSalary
ON EmployeeDemographics.EmployeeID=EmployeeSalary.EmployeeID
WHERE Salary >50000 or age> 30
ORDER BY Salary ASC

The illustration above shows the creation of tables and querying insights for a better understanding of the entity’s operations.

SQL Practice Project

Written by Mark Mulika