Understanding SQL Queries using Real world Project Scenario for Entry level roles
Created by Hafsa Ahmed
Hello Everyone, I am using MySQL software and I am going to solve analytical problem using SQL in real life project scenario i.e. Student Database Management.
Firstly, I would like to introduce a lit bit about SQL and its basics.
WHAT IS SQL?
SQL is universal programming language designed for managing data in databases. It supports all relational databases as well as No SQL. SQL is easy to understand and access.
With SQL, we can:
- Define data (DDL)
- Manipulate data (DML)
- Retrieve data from databases (DQL)
Understanding the Basics
The most important to understand SQL basis in order to answering difficult questions is the order of operations! Make sure you know this by heart. If you get in the habit of writing each line of a query in this order rather than from top to bottom, I promise you’ll be a SQL guru in no time!.
Order of Operations
FROM: the table your data is in
WHERE: filters the table down to the contents where a certain condition is met
GROUPBY: clusters the data into groups
SELECT: actually pulls the data
ORDER BY: orders the data
LIMIT: puts a cap on the number of returns
I believe following order should be kept in mind to find solution to any query.
Query Order
SELECT
FROM
WHERE
GROUP BY
ORDER BY
LIMIT
Before I begin to query data, I will need to create a database and load into it.
WHAT IS DATA AND DATABASE?
A database is an organized collection of data, and consists of structures such as tables among other elements. A table stores data in rows (tuples) and columns (attributes). A schema refers to a blueprint of how the database is constructed (or divided into database tables).
DEFINE DATA, CREATE TABLE and INSERT DATA
The following set of commands builds the schema (create tables and insert data) for database:
- I am creating a table named Student and insert data into the table.
CREATE TABLE Student
( Student_ID int Not Null Primary Key ,
First_Name varchar(30) not Null,
Last_Name varchar(30) not Null,
Date_of_Birth date,
House_No varchar(100),
City varchar(30),
County varchar(30),
Postal_code varchar(10),
Email varchar(50),
Phone_no bigint,
Course_ID int);
Insert into Student ( Student_ID, First_Name,Last_Name,Date_of_Birth,House_No,City,County,Postal_code,Email,Phone_no,Course_ID)
values (100,’David’,’Tom’,’2002–02–02',’23A’,’London’,’Middlesex’,’HA6 1EF’, ‘d.tom@gmail.com’,’07356484859',’0101'),
(101,’Mary’,’Mcbride’,’2003–01–23',’15',’Slough’,’Berkshire’,’CX12 34R’, ‘m_mcbride2@hotmail.com’,’07534284839',’0078'),
(102,’Sania’,’khan’,’2000–04–15',’436',’Birmingham’,’West Midlands’,’NW1 8RG’, ‘sania.khan@gmail.com’,’07756000859',’9840'),
(111,’Jerry’,’Akbar’,’2002–05–22',’55',’London’,’Middlesex’,’UB1 2GH’, ‘jerry.a.123@yahoo.com’,’07556464959',’3488'),
(103,’June’,’Johnson’,’2004–11–12',’10B’,’Coventry’,’Warwickeshire’,’BR1 2HX’, ‘j.johnson@gmail.com’,’07644324580',’9230'),
(104,’May’,’Sule’,’2003–10–09',’42',’Warwick’,’Warwickshire’,’L12 7TH’, ‘may.s.2345@hotmail.com’,’01256484869',’2319'),
(105,’Robert’,’Atif’,’2003–12–08',’116',’London’,’Middlesex’,’W13 67J’, ‘r.atif12@gmail.com’,’07355686359',’8430'),
(106,’Simon’,’Lane’,’2002–05–26',’Flat no 5C’,’Luton’,’Bedfordshire’,’B12 12R’, ‘s.lane@gmail.com’,’07543643539',’0012'),
(107,’Asha’,’Majadi’,’2000–09–29',’38',’Wakefield’,’Yorkshire’,’YA7 8DR’, ‘a_majadi.123@hotmail.com’,’07356484859',’3488'),
(108,’Ahsan’,’Malik’,’2004–07–12',’111',’Leeds’,’Yorkshire’,’YB4 59H’, ‘a.malik76@yahoo.com’,’07255684859',’10'),
(109,’Ben’,’Gomez’,’2002–06–16',’56',’Watford’,’Herdforshire’,’W10B6 8TH’, ‘b.gomez88@gmail.com’,’01256456800',’0012'),
(112,’Andy’,’Rich’,’2003–12–28',’220A’,’London’,’Middlesex’,’UB1 3TH’, ‘a.rich67@hotmail.com’,’07259466809',’9840'),
(115,’Mohsin’,’Daud’,’2000–06–10',’100',’Watford’,’Herdforshire’,’UB6 5PL’,’mohsin.daud@gmail.com’,’07623536326',’9230');
2. I am creating a table named Course and insert data into the table.
Create table Course
( Course_ID int not NULL Primary Key,
Course_Name varchar(100),
Credit_Hours int);
Insert into Course (Course_ID, Course_Name, Credit_Hours)
values (‘1234’,’English’,’20'),
(‘5674’,’Maths’,’30'),
(‘0012’,’Statistics’,’25'),
(‘9230’,’French’,’15'),
(‘2319’,’Litreture’,’10'),
(‘3488’,’Zoology’,’30'),
(‘3290’,’Physcology’,’20'),
(‘0002’,’Science’,’27'),
(‘0078’,’Arts’,’22'),
(‘0732’,’Biology’,’30'),
(‘8430’,’Physics’,’25'),
(‘9840’,’Chemistry’,’20'),
(‘0101’,’Spanish’,’18');
3. I am creating a table named Enrollment and insert data into the table.
Create table Enrollment
( Enrollment_ID int not NULL Primary Key Auto_Increment,
Course_ID int not NULL ,
Student_ID int not NULL ,
Grade varchar(5),
Enrollment_Date date,
Foreign key(Course_ID) REFERENCES Course(Course_ID)
ON DELETE CASCADE);
Insert into Enrollment (Enrollment_ID,Course_ID,Student_ID ,Grade ,Enrollment_Date)
Values (‘1’,’5674', ‘102’,’A’, ‘2018–04–12’),
(‘2’,’0012', ‘110’,’A*’, ‘2019–05–10’),
(‘3’,’3488', ‘115’,’AA’, ‘2020–03–30’),
(‘4’,’3488', ‘103’,’B’, ‘2016–02–28’),
(‘5’,’3290', ‘104’,’C’, ‘2017–01–15’),
(‘6’,’9840', ‘111’,’B+’, ‘2019–06–02’),
(‘7’,’0101', ‘105’,’A’, ‘2020–08–25’);
So here, I created three tables, which is Students, Course and Enrollment which is connected by Student_ID and Course_ID (primary key and foreign key).
How to Retrieve Data and Sort in SQL?
A select command is used to retrieve data from the database.
Below is a recap of some basic queries with different ways of getting students data:
· All the details from a table
· Limit the results to first N rows (it is used for testing or sampling)
· Specific columns from a table
· Aliases for column names (to make information more readable)
· Distinct column values (unique values from one or more columns)
Below is syntax and output for retrieving data for tables:
I want to get only Students Name , Phone no ,Email, city, Date of Birth from Student table and sort by Date of birth in ascending order who are living in London. Another feature is the concatenation function in SQL; which merge two fields from table and can easily aliases it. So, I combine first and last name and made aliases as Student name which can be seen in below picture:
SELECT concat(First_Name, “ “, LAST_Name) As Students_Name,Phone_no,Email,city, Date_of_birth
From student
Where city=’London’
order by Date_of_birth ;
Moving further to advance features of SQL is Join. I am going to give overview about it:
JOIN TABLES
A SQL join is basically combining 2 or more different tables (sets) to get 1 set of the result based on some criteria. Joining of data is the most common usage of any ETL applications but also is the trickiest and compute heavy operation. Spark offers most of the commonly used joins in SQL.
In data modelling (database design), normalization is a process used to organize data in separate well-structured tables to minimize redundancy and avoid insert, update and delete anomalies. The separation also tends to simplify the logic and enhance the flexibility of assembling precisely the items needed for a given purpose. This assembly is accomplished by means of the ‘JOIN’ operation.
- I want to join table Student and Enrollment in order to get more details about students that have been enrolled in different courses. In this case, I use inner join, a part of JOIN case.
SELECT S.Student_ID,S.First_Name,S.Last_Name,S.Date_of_Birth,E.Enrollment_Date,E.Grade,E.Course_ID
From Student as S
inner join Enrollment as E
On S.Course_ID=E.Course_ID
order by Date_of_Birth;
2. I want to join table Student and Course in order to get more details about students that have been enrolled in different courses who are living in London and sorted ascending by Date of Birth.
SELECT S.Student_ID,S.First_Name,S.Last_Name,S.Date_of_Birth,City,C.Credit_Hours,C.Course_Name,C.Course_ID
From Student as S
inner join Course as C
On S.Course_ID=C.Course_ID
Where City=’London’
order by Date_of_Birth;
3. Additionally, I want to left join table Student and Course in order to get more details about students that have been enrolled in different courses and sorted ascending by Date of Birth.
SELECT S.Student_ID,S.First_Name,S.Last_Name,S.Date_of_Birth,City,C.Credit_Hours,C.Course_Name,C.Course_ID
From Student as S
Left join Course as C
On S.Course_ID=C.Course_ID
order by Date_of_Birth;
4. Now, I want to right join table Student and Enrollment in order to get more details about students that have been enrolled in different courses .
SELECT S.Student_ID,S.First_Name,S.Last_Name,S.Date_of_Birth,S.County,E.Grade,E.Enrollment_ID,E.Enrollment_Date,E.Course_ID
From Student as S
Right join Enrollment as E
On S.Course_ID=E.Course ;
Data Transformations in SQL
Data Transformations are often required in situations where poorly collected data requires cleansing; such processes being called Data Wrangling (or munging). Transformations are also needed to present data reports in a more human-readable format.
SUB QUERY
A sub query is a query that is nested inside a SELECT , INSERT , UPDATE , or DELETE statement, or inside another sub query. A sub query can be used anywhere an expression is allowed. In this example a sub query is used as a column expression named Course ID ‘3488’ in a SELECT statement.
Select * From Course
Where Course_ID In
( Select Course_ID from Enrollment Where Course_ID= ‘3488’);
CREATING AND SELECTING VIEW
A view is a virtual table based on the result set of an SQL statement.
CREATE VIEW Student_Data AS
SELECT concat(First_Name, “ “, LAST_Name) As Students_Name,Phone_no,Email,city, Date_of_birth
From student
order by Date_of_birth ;
Select * from Student_Data;
INDEX IN SQL
An index contains keys built from one or more columns in the table or view. These keys are stored in a structure (B-tree) that enables SQL Server to find the row or rows associated with the key values quickly and efficiently.
CREATE INDEX Student_Database
ON Student (Student_ID, First_Name,Last_Name,Date_of_Birth,House_No,City,
County,Postal_code,Email,Phone_no,Course_ID);
SHOW INDEX FROM Student ;
SQL INSERT INTO SELECT Statement
The INSERT INTO SELECT statement copies data from one table and inserts it into another table.
· INSERT INTO SELECT requires that data types in source and target tables match
· The existing records in the target table are unaffected
I have created new table named Student Enrollment and I have inserted data from selecting from student and Course table.
Create Table Student_Enrollment
(FirstName varchar(20), LastName varchar(20),
PhoneNo bigint,CourseID int ,CourseName varchar(20),Hours int);
Insert into Student_Enrollment (FirstName, LastName,PhoneNo ,CourseID ,CourseName ,Hours )
Select S.First_Name, S.Last_Name,S.Phone_no,S.Course_ID,C.Course_Name,C.Credit_Hours
From Student as S
Inner Join Course as C
on S.Course_ID=C.Course_ID;
Select * from Student_Enrollment;
I would like to end up this project. Actually there are a lot more handy functions in SQL, but I will make it in the next posts. Thank you and I hope that this project which is made by myself can ensure you about my ability in using MySQL.
Dear Recruiters,
Hi, I am Hafsa Ahmed. I am working as Data Admin/Sixth form study supervisor. I have worked as Salesforce administrator and research analyst in multinational organization. Nowadays, I am looking for career transition to Data analyst position. Kindly drop me your email via message so that I can send you my updated resume.Well, allow me to show you about my real project scenarios. I am going to solve analytical problems using SQL. I used MYSQL for real life scenario project.
Thank you.
