INTRODUCTION
It has been two weeks of intensive learning, exploring sql concepts from scratch at luxdevHQ.
Structured Query Language(SQL) is basically the language used to communicate with databases.
Sql is one of the core tools in data analysis.
It allows users to retrieve, clean, transform, combine, and analyze data stored in databases.
There are various Databases management systems used which have different functionalities such as Postgresql, Oracle, Mysql, Microsoft Sqlserver.
For the case of this article, we will be using Postgresql.
Sql Functions
Functions are built in operations that perform specific tasks on the data.
They help you calculate, transform, clean, and analyze data.
Some of the functions include;
i. Row_Number()
This function assigns unique sequential numbers to each row.
The most common uses is finding the top record within each group.
ii. Rank()
Ranking with gaps for ties.
It gives tied rows the same rank and skips the next rank.
iii. Dense Rank()
This ranks without gaps
Simply put, it does not skip a rank when there is a tie.
iv. Ntile(n)
It distributes into n buckets.
It also assigns each row a group number.
v. Lag()/Lead()
It accesses previous /next row.
They're very useful when comparing periods.
select
visit_date,
total_bill_kes,
lag(total_bill_kes) over (order by visit_date ) as previous
from visits;
select
visit_date,
total_bill_kes,
lead(total_bill_kes) over (order by visit_date ) as next
from visits;
vi. Count()
This function counts rows present in a given table.
select
insurance_provider,
count(patient_id) no_patients
from visits
where insurance_provider not in('None')
group by insurance_provider
order by count(patient_id) desc
limit 1;
vii. Sum()
The function is used to add numeric functions.
select
insurance_provider,
sum(total_bill_kes) as revenue
from visits
group by insurance_provider
order by sum(total_bill_kes) desc
limit 1;
viii. First_Value()
This function returns the value from the first row in the window.
ix. Last_Value()
Returns the value from the last row in the window.
x. Max()
This function returns the largest value in the column.
select
insurance_provider,
max(total_bill_kes) as Highest
from visits
group by insurance_provider
order by max(total_bill_kes)
limit 1;
x. Min()
Returns the smallest value in the column.
select
insurance_provider,
Min(total_bill_kes) as smallest
from visits
group by insurance_provider
order by max(total_bill_kes)
limit 1;
Order by()
It is used to sort data.
The ORDER BY clause allows you to sort the result set of a query by one or more columns, either in ascending (ASC) or descending (DESC) order.
select
insurance_provider,
sum(total_bill_kes) as revenue
from visits
group by insurance_provider
order by sum(total_bill_kes) desc
limit 1;
Partition by
It is used with analytic (window) functions to divide the result set into partitions or logical groups, where calculations are independently performed for each partition.
select
insurance_provider,
total_bill_kes,
ntile(4) over (partition by insurance_provider order by total_bill_kes) as group
from visits;
Over()
Over() tells SQL to perform aggregate calculation across a set of rows without removing the individual rows.
Sql Joins
A join combines rows from two or more tables based on a related column between them, usually a key that exists in both.
i. Inner Join
Returns all rows from the left table plus matching rows from the right table.
Where there's no match the right table's columns come back as null.
ii. Left Join
A left join returns all rows from the left table (the table mentioned before the LEFT JOIN keyword), along with matching rows from the right table.
If there are no matching rows in the right table, NULL values are included in the result set.
iii. Right Join
A right join returns all rows from the right table (the table mentioned after the RIGHT JOIN keyword), along with matching rows from the left table. If there are no matching rows in the left table, NULL values are included in the result set.
iv. Full outer Join
A full join returns all rows from both tables, matching rows from both tables where available.
If there is no match, NULL values are included in the result set.
select
D.department_name,
V.patient_satisfaction_score
from department d
full join visits v
on d.department_id = v.department_id;
v. Cross Join
A cross join returns the Cartesian product of the two tables, meaning it returns all possible combinations of rows from both tables. It doesn't require a join condition.
vi. Self Join
It's when a table is joined to itself.
It is useful when records in the same table have relationships with other records in that same table.
Common Table Expressions
A Common Table Expression (CTE) is a temporary result set that simplifies and structures SQL queries.
It is defined using the WITH keyword and can improve query readability and reusability
i. multiple CTEs
This is where you create multiple CTEs in the same query.
with doctor_totals as (
select
doctor_id,
SUM(total_bill_kes) AS total_bill
from visits
group by doctor_id
),
average_bill as (
select
AVG(total_bill) as avg_bill
from doctor_totals
)
select
d.doctor_name,
dt.total_bill
from doctor_totals dt
join doctors d
on dt.doctor_id = d.doctor_id
cross join average_bill ab
where dt.total_bill > ab.avg_bill;
ii. Recursive CTEs
This type of CTE allows us to work with hierarchical or recursive data structures.
A CTE can be used to calculate aggregated metrics like total bill, avg_patient satisfaction score and then reused in further calculations or to filter out unnecessary data.
This can optimize performance by avoiding complex aggregation in the main query
Subqueries
A subquery is a SQL query written inside another query.
The outer query uses the result of the inner query as part of its logic.
i. Scalar Subqueries
Returns exactly one row and one column — a single value.
select
visit_id,
total_bill_kes
from visits
where total_bill_kes > (
select AVG(total_bill_kes)
from visits
);
ii. Correlated Subqueries
A correlated subquery references columns from the outer query, meaning it is executed once for each row of the outer query.
This makes it more powerful but potentially more expensive.
iii. Non-Correlated Subqueries
A subquery that does not depend on the outer query.
It can run independently of the outer query and usually executes once.
iv. Nested Subqueries
A nested subquery is a subquery inside another subquery.
Top comments (0)