Structured Query Language (SQL) is a programming language specifically created to access, organize and manipulate

data within relational databases. In a relational database, information is arranged into one or more tables, each
containing columns and rows of interconnected data entries that relate to each other in some way.

Select * from employee;

All data stored in a relational database is of a certain data type. Some of the most
common data types are:

INTEGER, a positive or negative whole number, TEXT, a text string

DATE, the date formatted as YYYY-MM-DD, REAL, a decimal value

A statement refers to a piece of text that the database system can interpret as a valid command.
It is important to note that SQL statements are terminated by a semicolon ; which indicates the end of the statement.

CREATE TABLE table_name (

column_1 data_type,
column_2 data_type,
column_3 data_type
);
CREATE TABLE is a clause.
In SQL, clauses are utilised to execute specific functions or operations.
It is customary to write clauses in SQL statements using capital letters.
In SQL, clauses can also be referred to as commands as they are used to perform specific actions or operations on the
data stored within a database..
table_name refers to the name of the specific table on which a particular command or operation is being performed.

(column_1 data_type, column_2 data_type, column_3 data_type) are parameters.

A parameter is a set of values, columns, or data types that are provided to a clause as an input argument.

Create table
Using a CREATE TABLE statement in SQL enables the creation of a new table in the database.
This statement can be used whenever a new table needs to be created, starting with a blank slate.
The example statement below demonstrates the creation of a new table called student.
CREATE TABLE student (
Student_id INT,
Student_Name TEXT,
Department TEXT
);

/* Write a query to create a table 'employee', with columns employee_id, employee_Name and Department.
Update the blanks below to solve this problem */

CREATE TABLE employee

(
Employee_id ___ , /* Integer Data type assigned to the variable */
Employee_Name ____, /* TEXT Data type assigned to the variable */
Department ____ /* TEXT Data type assigned to the variable */
);
The above query will create an empty table 'student' as mentioned below:
Student_id Student_Name Department
Insert table
Rows are added into a table using the INSERT INTO statement.
Below is the query to add the details of 'Abel George' to the existing table student.

INSERT INTO student(Student_id,student_Name,Contact_Number)

VALUES (34,'Abel George',910023432);
The clause INSERT INTO is used to append the specified row or multiple rows to a table.
student is the table the row is added to.
(Student_id,student_Name,Contact_Number) are the parameters used to specify the columns into which data will be
inserted.
VALUES is used to specify the data that is being inserted.
(34,'Abel George',910023432) is a parameter identifying the values being inserted.
34: an integer that will be added to Student_id column
'Abel George': text that will be added to student_Name column. It should always be in single quotes.
910023432: Number that will be added to Contact_Number column.
Multiple rows can be added to a table in a single query by separating the parameters(used to insert data to a single row)
by a comma ','

Task: Write a query to insert the below mentioned employee details to the table 'employee'.

1>Employee_id - 4, Employee_Name - 'Marcus Garcia', Department - 'Product'

2>Employee_id - 5, Employee_Name - 'Samantha Park', Department -'Hr'

Solution
/* Solution as follows*/

/*Lets add the details of 2 employees to the table 'employee' */

INSERT INTO employee (Employee_id,Employee_Name,Department)
VALUES (4,'Marcus Garcia','Product'),
(5,'Samantha Park','Hr');
Alter table
The ALTER statement is used to append a new column to an existing table.
Below is the query to add a new column 'Department' and set a default value, to the existing table student.

ALTER TABLE student

ADD COLUMN Department TEXT default NULL;
While altering the table we can either keep the newly added column blank or we could set a default value (as mentioned
above) to it. Lets run the query by adding a default value to the newly added column.

Task: Write a query to do the following Add a column 'Designation' to the table 'employee' and set 'Null' as the default
[Link] the entire table.
Original table has the following rows
┌─────────────┬────────────────┬────────────┐
│ Employee_id │ Employee_Name │ Department │
├─────────────┼────────────────┼────────────┤
│1 │ Kayla Thompson │ Sales │
│2 │ Ethan Chen │ Operations │
│3 │ Julia Lee │ Hr │

alter table employee

add column Designation Text default Null;
select * from employee;
update table

The UPDATE statement is used to edit a row or multiple rows in a table.

Below is the query to Set the Age as 6, for the student with student_id - 23 to the existing table student.

UPDATE student
SET Age = 6
WHERE student_id = 23;
The 'WHERE' condition can be applied for any column. We will learn more about 'WHERE' in the next module

Task :Write a query to do the following

Set the Department as 'HR', for the employee with employee_id - 02 to the existing table employee
Output all the entries of the table
Original table has the following entries
┌─────────────┬────────────────┬────────────┐
│ Employee_id │ Employee_Name │ Department │
├─────────────┼────────────────┼────────────┤
│1 │ Kayla Thompson │ Sales │
│2 │ Ethan Chen │ Operations │
│3 │ Julia Lee │ Hr │

/*Write a query to set the Department as 'HR', for the employee with employee_id 2 to the existing table employee. */
update employee
SET Department = 'HR'
where employee_id = 2;

select * from employee;

Alter table
You are given a table - employee (mentioned below)

Write a query which does the following

Add a new column 'Hourly_Pay' to the table employee and set the value as 100 by default.
Output the entire table
Original table has the following rows
┌─────────────┬────────────────┬────────────┐
│ Employee_id │ Employee_Name │ Department │
├─────────────┼────────────────┼────────────┤
│1 │ Kayla Thompson │ Sales │
│2 │ Ethan Chen │ Hr │
│3 │ Julia Lee │ Operations │
│4 │ Marcus Garcia │ Hr │

/* Write a query which does the following

- Add a new column 'Hourly_Pay' to the table employee and set the value as 100 by default.
- Output the entire table */

ALter table employee

Add column Hourly_Pay INT default 100;
select * from employee;

Update table
In the previous problem we've added a new column 'Hourly_Pay'(mentioned below).
Now write a query which does the following

Set the 'Hourly_Pay' to 150 for the employees in Hr department.

Output all the entries of table.
Original table 'employee' has the following rows
┌─────────────┬────────────────┬────────────┬────────────┐
│ Employee_id │ Employee_Name │ Department │ Hourly_Pay │
├─────────────┼────────────────┼────────────┼────────────┤
│1 │ Kayla Thompson │ Sales │ 100 │
│2 │ Ethan Chen │ Hr │ 100 │
│3 │ Julia Lee │ Operations │ 100 │
│4 │ Marcus Garcia │ Hr │ 100 │

/* Write a query to do the following - Set hourly_pay to 150 for HR employees - Output the entire table */

update employee
set hourly_pay = 150
where department = 'Hr';
select * from employee;

Delete From
The DELETE FROM statement is used to remove one or multiple rows from a table.
You can use the statement when you want to delete existing records.

Below is the query to delete all rows in the student table with student_id - 08 (table added below for reference).

DELETE FROM student

WHERE student_id = 08;
Task Write a query which does the following

Delete all rows in the employee table whose Department is 'Hr'. Output all the entires of the table
Original table has the following rows
┌─────────────┬────────────────┬────────────┐
│ Employee_id │ Employee_Name │ Department │
├─────────────┼────────────────┼────────────┤
│1 │ Kayla Thompson │ Sales │
│2 │ Ethan Chen │ Operations │
│3 │ Julia Lee │ Hr │
│4 │ Marcus Garcia │ Product │

/* Write a query which does the following - Delete all rows in the employee table whose Department is 'Hr'.- Output all
the entires of the table. */

Delete from employee

where Department ='Hr';

select * from employee;

Constraints
Constraints provide details about the usage of a column and are applied after specifying the column's data type.
They enable the database to reject any inserted data that violates a particular constraint. The following statement is
used to impose constraints on the "employee" [Link] is the query to create a table student with a set of
constraints.
CREATE TABLE student(
student_id INTEGER PRIMARY KEY,
student_Name TEXT UNIQUE,
Department TEXT NOT NULL);

PRIMARY KEY can be utilized to uniquely identify a row in a table.

When attempting to insert a row with the same value as an existing row in the table, a constraint violation will occur,
preventing the insertion of the new row.
UNIQUE columns contain distinct values for each row, similar to "PRIMARY KEY" columns, but unlike primary key
columns, a table can have multiple unique columns..
NOT NULL columns must have a value assigned to them.
When attempting to insert a row without providing a value for a "NOT NULL" column, a constraint violation will occur,
preventing the insertion of the new row.
Task
Write a query to create a table employee with the mentioned constraints on the columns :

employee_id - INTEGER PRIMARY KEY,

employee_Name - TEXT UNIQUE,
Department - TEXT NOT NULL

create table employee(

employee_id integer PRIMARY KEY,
employee_Name TEXT UNIQUE,
Department TEXT Not NULL
);

select * from employee

Delete From
You are given a table - employee (mentioned below).

Write a query which does the following

Delete the row where the department is Client.
Output the entire table.
Original table has the following rows
┌─────────────┬────────────────┬────────────┐
│ Employee_id │ Employee_Name │ Department │
├─────────────┼────────────────┼────────────┤
│1 │ Kayla Thompson │ Sales │
│2 │ Ethan Chen │ Operations │
│3 │ Julia Lee │ Client │
│4 │ Marcus Garcia │ Product │

Delete from employee

where department ='Client';

select * from employee;

Debug this query

The Query written in the console is trying to insert data to the table [Link] this query to run the problem
[Link]’t worry about the actual values as long as you get the query to [Link] database is named 'employee'
and has the following columns
Id PRIMARY KEY(INT)
Name (TEXT),
Age (INT),
Address UNIQUE (TEXT)
/* Debug this query to run the problem successfully.
- Don’t worry about the actual values as long as you get the query to run. */

INSERT INTO employee (Id,Name,Age,Address)

VALUES (1, 'John Smith', 25, '123 Main St'),
(1, 'Sarah Johnson', 30,'456 Broadway'),
(1, 'Michael Brown', 45, '123 Main St'),
(4, 'Jessica Davis', 28, '321 Elm St');

ANs
INSERT INTO employee (Id,Name,Age,Address)
VALUES (1, 'John Smith', 25, '123 Main St'),
(2, 'Sarah Johnson', 30,'456 Broadway'),
(3, 'Michael Brown', 45, '1234 Main St'),
(4, 'Jessica Davis', 28, '321 Elm St');

Introduction to Queries
In this module, let us learn the different commands to QUERY a single table database. Queries are used to talk to the
database and carry out specific actions. Throughout this module, let us use the Flights table to understand how
passengers have booked their flight tickets. Let us check the data currently stored in the Flights table. Click on 'Submit'
to proceed.

select * from Flights

┌──────────────┬────────────────┬────────┬──────────┬─────────────┐
│ Passenger_id │ Passenger_name │ Gender │ Origin │ Destination │
├──────────────┼────────────────┼────────┼──────────┼─────────────┤
│ 10001 │ Jackson │ Male │ Mumbai │ New York │
│ 10002 │ Riya │ Female │ Mumbai │ Delhi │
│ 10003 │ Roy │ Male │ London │ Delhi │
│ 10004 │ Anthony │ Male │ Mumbai │ Cairo │
│ 10005 │ Salim │ Male │ Ohio │ New York │
│ 10006 │ Dia │ Female │ New York │ Cairo │
│ 10007 │ Jackson │ Male │ New York │ London │
│ 10008 │ Dia │ Female │ Beijing │ Mumbai │
│ 10009 │ Riya │ Female │ Damascus │ Mumbai │
│ 10010 │ Betty │ Female │ Beijing │ Cairo

SELECT Query
As you saw in the problem earlier, the Flights table had the following information in columns

Passenger_id with datatype INT

Passenger_name with datatype VARCHAR
Gender with datatype VARCHAR
Origin with datatype VARCHAR
Destination with datatype VARCHAR
To view data entries in specific columns of a table, the following syntax is used
 select column_1, column_2
from Flights;
To view ALL rows of a table, the following syntax is used

select *
from Flights;
Task
Write a query which does the following

Let us fetch the entry specifically from 2 columns - 'Passenger_name' and 'Gender'.
Expected output : select Passenger_name,Gender from flights;
┌────────────────┬────────┐
│ Passenger_name │ Gender │
├────────────────┼────────┤
│ Jackson │ Male │
│ Riya │ Female │
│ Roy │ Male │
│ Anthony │ Male │
│ Salim │ Male │
│ Dia │ Female │
│ Jackson │ Male │
│ Dia │ Female │
│ Riya │ Female │
│ Betty │ Female │

DISTINCT
In the Flights table, what all 'Origins' exist? The following query should give us the result.

Select Origin from Flights; However, if we want to find the unique origin locations, we will use the DISTINCT syntax in
the following format.

Select Distinct Origin from Flights; Write the above query in the IDE to get the unique origin locations.

WHERE
The WHERE clause helps us obtain information which meets specific conditions.

In the previous problem, we saw the 'Origins' of flights.

Let us try and identify flights that originate out of 'Mumbai' using the following syntax.

Select * from Flights WHERE Origin = 'Mumbai';

BETWEEN
The BETWEEN clause is used along with WHERE to filter the table based on 2 [Link] values can be text
select * from Flights
where passenger_name BETWEEN 'A' AND 'D';

The values can be integers

select * from Flights
where passenger_id BETWEEN 10001 AND 10007;

The values can be dates as well.

Task : Let us try the 1st example - Write a query to do the following
Output all entries from the table
where passenger_name between 'A' and 'D'
Expected Output
┌──────────────┬────────────────┬────────┬─────────┬─────────────┐
│ Passenger_id │ Passenger_name │ Gender │ Origin │ Destination │
├──────────────┼────────────────┼────────┼─────────┼─────────────┤
│ 10004 │ Anthony │ Male │ Mumbai │ Cairo │
│ 10010 │ Betty │ Female │ Beijing │ Cairo │

select * from flights

where passenger_name Between 'A' AND 'D';

Let us combine what we have learnt from our 'SELECT', 'DISTINCT' and 'WHERE' queries.

From the 'Flights' table - let us find the following

Where the origin of the flight is 'New York'

Output the passenger_name and gender
Expected Output
┌────────────────┬────────┐
│ Passenger_name │ Gender │
├────────────────┼────────┤
│ Dia │ Female │
│ Jackson │ Male │
└────────────────┴────────┘
Remember that the column details are as follows : Passenger_id ,Passenger_name,Gender, Origin ,Destination
select Distinct passenger_name, Gender from flights
where origin ='New York'

Debug this query

Let us combine what we have learnt from our 'SELECT', 'DISTINCT' and 'WHERE' queries.
The Query written in the console is trying to do the following. Where the origin of the flight is 'Mumbai'
Output the 'Distinct' names of 'Male' passengers Debug this query to get the correct output!

Remember that the column details are as follows Passenger_id, Passenger_name, Gender, Origin, Destination
select distinct passenger_name from flights where gender = 'Male' and origin = 'Mumbai';

Rename columns using As

Before we begin aggregate functions, a useful concept to know is renaming of columns during output.

In SQL, the keyword 'AS' allows you to rename a column or table using an alias.
Sample syntax:

SELECT employee_id AS 'Serial' FROM employee;

Task Write a query to output all from the following columns in the table 'employee'

Rename employee_id as 'Serial'

Rename employee_name as 'Name'
Rename department as 'Dept'
Expected output
┌────────┬────────────────┬────────────┐
│ Serial │ Name │ Dept │
├────────┼────────────────┼────────────┤
│ 1 │ Kayla Thompson │ Sales │
│ 2 │ Ethan Chen │ Operations │
│ 3 │ Julia Lee │ Hr │
│ 4 │ Marcus Garcia │ Product │
│ 5 │ Samantha Park │ Operations │
select employee_id as "Serial",
employee_name as "Name",
department as 'Dept' from employee;

COUNT()
Using the COUNT() function is the most efficient method for determining the number of rows in a table.
This function accepts the name of a column as a parameter and calculates the total count of non-empty values in that
column.

Below is the query to count the rows of the table 'customer'.

SELECT COUNT(*)
FROM customer;
However, some rows of a column can be NULL values.
The query below will provide the count of rows of the table 'customer' for a specified column 'column_1' ignoring the
null values.

SELECT COUNT((column_1))
FROM customer;
Task
Write a query to count the rows of the table EMPLOYEE.
Rename the column header as 'Count'.
┌──────────┐
│ Count │
├──────────┤
│5 │
SELECT COUNT(*) as 'Count' FROM EMPLOYEE;

MAX() and MIN()

The MAX() and MIN() functions retrieve the maximum and minimum values from a column, correspondingly.

Below is the query to find the highest and lowest age of the customers from the table customer
SELECT MAX(Age) FROM customer;
SELECT MIN(Age) FROM customer;
Task Write a query to find the highest and lowest 'Hourly_pay' of the employees from the table 'employee'.

Rename the column header for highest pay as 'max_pay' ,Rename the column header for lowest pay as 'min_pay'
Expected output
┌─────────┐
│ max_pay │
├─────────┤
│ 55
┌─────────┐
│ min_pay │
├─────────┤
│ 28
select Max(Hourly_pay) as 'Max_pay' from employee;
select min(Hourly_pay) as 'Min_pay' from employee;
ROUND()
Let us introduce the ROUND() function as it is routinely used with aggregate functions.
Sql uses the ROUND() functions to display numeric values rounded to a specified precision.
The precision parameter indicates the number of decimal places to which the number should be rounded.

The ROUND() function requires two parameters enclosed in parentheses: a column name and an integer value.
Below is the query to display Total_Purchase rounded to 1 decimal place from the table customer

SELECT ROUND(Total_Purchase, 1) FROM CUSTOMER;

Task Write a query to display the values in the column Taxable_Pay rounded to 2 decimal places from the table emp.
Rename the column header as 'taxable_pay' Code it out in the IDE.
┌─────────────┐
│ taxable_pay │
├─────────────┤
│ 21.35 │
│ 19.45 │
│ 40.82 │
│ 33.29 │
│ 19.0 │ select Round(Taxable_Pay,2) as Taxable_Pay from employee;

Problem - COUNT() MAX() and MIN() You are given a table employee (mentioned below).
┌─────────────┬────────────────┬────────────┬────────────┐
│ Employee_id │ Employee_Name │ Department │ Hourly_Pay │
├─────────────┼────────────────┼────────────┼────────────┤
│1 │ Kayla Thompson │ Sales │ 44 │
│2 │ Ethan Chen │ Operations │ 26 │
│3 │ Julia Lee │ Hr │ 66 │
│4 │ Marcus Garcia │ Product │ 34 │
│5 │ Samantha Park │ Operations │ 43 │
│6 │ Brandon Kim │ Operations │ 28 │
│7 │ Olivia Nguyen │ Sales │ 30 │
│8 │ Dylan Patel │ Operations │ 35 │
│9 │ Chloe Davis │ Hr │ 31 │
│ 10 │ Brandon Adams │ Product │ 43 │
Task Write 3 separate queries to output the entries for the following:
Count the number of employees in the department 'Sales'.Rename the column header as 'count_sales'
Maximum Hourly pay for the department 'Operations'. Rename the column header as 'ops_max_pay'
Minimum Hourly pay for the department 'Operations'.Rename the column header as 'ops_min_pay'
Expected output
┌─────────────┐
│ count_sales │
├─────────────┤
│2
┌─────────────┐
│ ops_max_pay │
├─────────────┤
│ 43 │
┌─────────────┐
│ ops_min_pay │
├─────────────┤
│ 26
select Count(*) as 'count_sales' from employee where department = "Sales";
select max(Hourly_pay) as 'ops_max_pay' from employee where department ='Operations';
select min(Hourly_pay) as 'ops_min_pay' from employee where department='Operations';
select Round(Payout,2) as 'payout' from employee;
Expected output
┌─────────┬─────────┐
│ min_pay │ max_pay │
├─────────┼─────────┤
│ 123.54 │ 789.43 │
└─────────┴─────────┘
/* Solution as follows */

select round(min(Payout),2) as 'min_pay',

round(max(Payout),2) as 'max_pay'
from employee;

 Table / db manipulation
o CREATE, ALTER, INSERT, DELETE allow us to create a table or make changes to
an existing table
 Queries
o SELECT allows us to view entries in a table
o WHERE, BETWEEN, LIKE, AND, OR can be added along with SELECT to check
which entires meet certain conditions
 Aggregate functions
o SQL also allows us to use aggregate functions such as COUNT, MAX /
MIN, SUM, AVG to view aggregate information of the table
o GROUP BY statement in SQL are used to combine rows with identical values into
summary rows.
GROUP BY is frequently used with the syntax HAVING to apply filters at a group
level

Introduction - Combining tables manually

Till now we have dealt with data in a single table. Now let's try to merge information from various tables and understand
them as a whole.

Task Below mentioned are the tables in a university data base. Find out name of the professor who teaches Linear
Algebra to David Lee.

Table student:
St_id St_Name Department Course_id
1001 John Smith Computer Science CS101
1002 Emily Brown History HIS102
1003 David Lee Mathematics MAT202
1004 Sarah Johnson English ENG201
1005 Michael Chen Biology BIO103

Table course:
Course_id Course_Name Credits Prof_id
CS101 Introduction to Computer Science 3 2001
HIS102 World History II 3 2004
MAT202 Linear Algebra 2 2002
ENG201 Advanced Writing 4 2003
BIO103 Principles of Biology 4 2005

Table professor:
Prof_id Professor_Name Department Mail_id
2001 Michael Lee Computer Science [Link]@[Link]
2002 Karen Kim Mathematics [Link]@[Link]
2003 Sarah Johnson English [Link]@[Link]
2004 David Lee History [Link]@[Link]
2005 Rachel Lee Biology [Link]@[Link]

Combining tables with SQL In the previous problem our task was to find name of David Lee's Mathematics professor.

We were able to do that manually because the number of tables and data in them were [Link] tables manually
takes a lot of effort and is not [Link] SQL, we use the concept of JOIN to achieve [Link] is the query to join two
tables 'employee' and 'department' in an organisation database.

SELECT * FROM employee JOIN department ON employee.employee_id = department.employee_id;

The above query does the following Joins two tables and outputs a single [Link] column 'employee_id' is used to
match rows of the tables. i.e. Rows of the tables 'employee' are matched with the rows of the table 'department' which
has the same employee_id by applying the condition employee.employee_id = department.employee_id.
Many a times multiple tables will have similar column names, thus to identify a particular column of a table we use the
syntax table_name.column_name. Below mentioned are the tables 'employee' and 'department'

Table employee:
employee_id employee_Name Desination
1001 John Smith Sales Manager
1002 Emily Brown Operations Executive
1003 David Lee HR Executive
Table department:
employee_id department_id department_Name
1001 SL01 Sales
1002 OP01 Operations
1003 HR01 Humar Resouce
The output of the above query is mentioned below:
employee_id employee_Name Desination employee_id department_id department_Name
1001 John Smith Sales Manager 1001 SL01 Sales
1002 Emily Brown Operations Executive 1002 OP01 Operations
1003 David Lee HR Executive 1003 HR01 Humar Resouce

Write a query which does the following

Join the tables 'student' and 'course' Uses 'Course_id' to match both the tables and output the joined table Output all
entries from the joined table
select * from student join course on student.Course_id = course.Course_id;

Inner Joins
In the previous problem our task was to join the table 'student' and 'course’. There could be cases where none of the
students has opted for a particular course.

In such cases, when the tables are joined, the rows which does not match are excluded by default.
The row which has the name of the course which IS NOT opted by any of the student WILL BE EXCLUDED when both the
tables are joined. When the tables are joined in this manner its called Inner Joins.

Task Write a query to do the following Join the tables 'student' and 'course' and output all its entries. Check if you can
find the course with id ENG201 in the output.
Expected output
St_id St_Name Department Course_id Course_id Course_Name Credits Prof_id
1002 Emily Brown History HIS102 HIS102 World History II 3 2004
1005 Michael Chen Biology BIO103 BIO103 Principles of Biology 4 2005
select * from student join course on student.Course_Id = course.Course_Id;

Left Joins
We've learned that by default SQL removes the rows which doesn't match while joining tables.

However, if we wish to join two tables whose rows doesn't match, we can do that using LEFT [Link] two tables are
joined using 'LEFT JOIN', and if the rows don't match,

All the rows in the first table(left) will be kept as such and Whenever a row doesn't a corresponding row in the second
table (right), those columns will be kept [Link] is the query to join the table 'customer' and 'order' using LEFT JOIN

SELECT *
FROM customer
LEFT JOIN order
ON customer.cust_id = order.cust_id;
Task
Write a query to do the following:

JOIN the tables 'student' and 'course' using 'Course_id' to match both the tables and output the joined table.
LEFT JOIN the tables 'student' and 'course' using 'Course_id' to match both the tables and output the joined table.
Expected output

St_id St_Name Department Course_id Course_id Course_Name Credits Prof_id

1001 John Smith Computer Science CS101 CS101 Introduction to Computer Science 3 2001
1002 Emily Brown History HIS102 HIS102 World History II 3 2004
1003 David Lee Mathematics MAT202 MAT202 Linear Algebra 2 2002
1004 Sarah Johnson English ENG201 ENG201 Advanced Writing 4 2003

St_id St_Name Department Course_id Course_id Course_Name Credits Prof_id

1001 John Smith Computer Science CS101 CS101 Introduction to Computer Science 3 2001
1002 Emily Brown History HIS102 HIS102 World History II 3 2004
1003 David Lee Mathematics MAT202 MAT202 Linear Algebra 2 2002
1004 Sarah Johnson English ENG201 ENG201 Advanced Writing 4 2003
1005 Michael Chen Biology BIO103 NULL NULL NULL NULL

/* Write a query to do the following:

- JOIN the tables 'student' and 'course' using 'Course_id' to match both the tables and output the joined table.
- LEFT JOIN the tables 'student' and 'course' using 'Course_id' to match both the tables and output the joined table. */
SELECT *
FROM student
JOIN course
ON student.Course_id = course.Course_id;

SELECT *
FROM student
LEFT JOIN course
ON student.Course_id = course.Course_id;

Data Transformation
the concept of data transformation using 'Subqueries'. Subqueries (also known as nested queries or inner queries) are
used to transform data in a table by creating a new table that is based on the results of a subquery. This new table can
be used as a source for further analysis or used to create a new table. This is referred to as data transformation or table
transformation.

Here are some examples of table transformation using subqueries:

Filtering based on subquery: A subquery can be used to filter rows from a table based on a condition.
Creating a new table using subquery: A subquery can be used to create a new table based on the results of a query.
Updating a table using subquery: A subquery can be used to update a table by setting the values of one or more columns
based on a condition. We'll use restaurant database to learn about subqueries.
Task Write a query to output the first 3 rows of the table 'food'

Expected output
┌──────┬────────────┬────────┬─────────────┐
│ f_id │ f_name │ f_cost │ f_type │
├──────┼────────────┼────────┼─────────────┤
│ 1 │ Pizza │ 10 │ Continental │
│ 2 │ Burger │ 8 │ Continental │
│ 3 │ Fried Rice │ 12 │ Chinese select * from food limit 3;

Non-Correlated Subqueries
A subquery is a query nested inside another query.A non-correlated subquery is a subquery that can be executed
independently of the outer query.

The subquery does not depend on the outer query for its results.
Non-correlated subqueries are typically used to retrieve a single value or a set of values that are used in the WHERE
clause or the HAVING clause of the outer query.
Let us take an example for a non-correlated subquery

Suppose you have customer information in the table 'customers' and their restaurant order information in the table
'orders'
Below is the query to get the customer information of those who have placed an order with order value >1000.
Query:

SELECT * FROM customers WHERE customer_id IN ( SELECT customer_id FROM orders WHERE order_value >1000);
Task
Write a query to fetch Name and type of the food from the table 'food' which has got rating less than 3 in the table
'ratings'.
Expected output
┌────────┬─────────┐
│ f_name │ f_type │
├────────┼─────────┤
│ Tacos │ Mexican │
Table 'food' has the following columns: f_id (int) ,f_name (text), f_cost (int), f_type (int).
Table 'ratings' has the following columns: f_id (int) , f_rating (text).
SELECT f_name, f_type
FROM food
WHERE f_id in (
SELECT f_id
FROM ratings
WHERE f_rating < 3
);
Non-Correlated Subqueries
Write a query to do the following Find the dishes which cost more than the average cost of all the dishes at the
restaurant You need to output f_name, f_cost, f_type for such dishes Hint: You need to use a subquery on the table
'food' Expected output
┌──────────────────┬────────┬──────────┐
│ f_name │ f_cost │ f_type │
├──────────────────┼────────┼──────────┤
│ Sushi │ 20 │ Japanese │
│ Tandoori Chicken │ 15 │ Indian │
│ Beef Stroganoff │ 18 │ Russian │
│ Paella │ 25 │ Spanish │
│ Moussaka │ 16 │ Greek

Table Format Table 'food' has the following columns:

f_id (int) ,f_name (text), f_cost (int), f_type (int).

Table 'ratings' has the following columns:

f_id (int) , f_rating (text).
SELECT f_name, f_cost, f_type
FROM food
WHERE f_cost > (SELECT AVG(f_cost) FROM food);

Correlated Subqueries
Correlated Subqueries as the name suggests, its inner and outer queries are related. The subquery is dependent on the
outer query. Let us understand this via an example
Suppose we have a table consisting the following information - 'Employee_id', 'Department' and 'Salary' - employees can
belong to various departments - Marketing / Sales / HR / Ops
Suppose we want to find the employee id of those employees whose salary is less than the average salary of the
employees ONLY in his department.
This is how the query will work
For each employee_id in the outer query, the subquery will run.
The subquery will check the department of the employee and then compute the average salary of his department
The outer query will then take this average salary - and compare if the employee's salary is less than this average
If yes - then the outer query will include this employee in the output. This process will run for each row in the table
Query:

SELECT employee_id FROM employee AS e WHERE salary < (SELECT AVG(salary) FROM employee WHERE department=
[Link]);
Task Write a query to retrieve the names of food items which cost less than the average cost of 'Continental' food
type(f_type).

Expected output
┌────────┐
│ f_name │
├────────┤
│ Pizza │
│ Burger │
│ Tacos Table 'food' has the following columns: f_id (int) f_name (text) f_cost (int) f_type (text).
SELECT f_name
FROM food as f
WHERE f_cost <
(SELECT avg(f_cost)
FROM food
WHERE f_type = 'Continental' );
Correlated Subqueries
Let us find out more details about highly rated dishes.

Task Write a query to do the following. Try and use the concept of sub-queries.
You need to output details of the dish - 'f_name', 'f_cost' and 'f_type' ONLY if the following condition is satisfied
Average rating of the dish is greater than or equal to 4
Expected output
┌─────────────────┬────────┬─────────────┐
│ f_name │ f_cost │ f_type │
├─────────────────┼────────┼─────────────┤
│ Pizza │ 10 │ Continental │
│ Fried Rice │ 12 │ Chinese │
│ Pad Thai │ 14 │ Thai │
│ Sushi │ 20 │ Japanese │
│ Beef Stroganoff │ 18 │ Russian │
│ Paella │ 25 │ Spanish
Table Formats Table 'food' has the following columns: f_id (int) f_name (text) f_cost (int) f_type (int).
Table 'ratings' has the following columns: f_id (int) f_rating (text).
SELECT f_name, f_cost, f_type
FROM food
WHERE f_id IN (
SELECT f_id
FROM ratings
GROUP BY f_id
HAVING AVG(f_rating) >= 4
);

Union All
In the module on Multiple Tables we have learned that the UNION operations are done to stack a table or a column over
the other.
But UNION operation doesn't entertain duplicates. i.e. while combining two tables using UNION, the duplicate entries
will be removed and the final output will have unique data.
The above concern can be solved using the concept of UNION ALL.
When two tables/columns are combined using UNION ALL, all the data will be combined and added to the resulting
table, including the duplicates.

Below is the format for the same:

SELECT * FROM table_1

UNION ALL
SELECT * FROM table_2;
Note: table_1 and table_2 should necessarily have the same count of columns

UNION combines and eliminates duplicates from the result sets, while UNION ALL combines all
rows without eliminating duplicates.
Intersect
The INTERSECT operator combines two SELECT statements, but only returns the rows that are common to both SELECT
statements.
Below is the format for the same:
SELECT * FROM table_1
INTERSECT
SELECT * FROM table_2;

Task Consider a supermarket database

Table 'fruit' has the list of all fruits available in the market(few of them could be out of stock).
Table 'inventory' has the updated list of items in the supermarket.
Write a query to find the list of fruits available in the supermarket. (f_name column has the name of the fruits and
inv_name has the name of the items in the inventory, you are suppose to output the name of the fruits.)

Expected output
┌────────────┐
│ f_name │
├────────────┤
│ Banana │
│ Cherry │
│ Grape │
│ Kiwi │
│ Pear │
│ Pineapple │
│ Watermelon
SELECT f_name FROM fruit
INTERSECT
SELECT inv_name FROM inventory;

Except
Previously we learned the concept of INTERSECT, now lets see how EXCEPT works.
EXCEPT is directly opposite to that of INTERSECT.
EXCEPT retrieves unique records from the first SELECT statement that are not present in the output of the second
SELECT statement.

Below is the format for the same:

SELECT * FROM table_1
EXCEPT
SELECT * FROM table_2;

Task Consider the same supermarket database we used in the previous problem.
Write a query to output the name of the fruits (f_name) from the table 'fruit' which are not present in the table
inventory.
f_name column has the name of the fruits and inv_name has the name of the items in inventory.

Expected output
┌────────┐
│ f_name │
├────────┤
│ Apple │
│ Mango │
│ Orange

SELECT f_name FROM fruit

EXCEPT
SELECT inv_name FROM inventory;

Conditional Aggregates Introduction

We have learned the concept of Aggregate function in Learn SQL, that it gives a single output value based on the
calculation on multiple input values.
Now, lets learn the concept of Conditional Aggregate function. In this concept we add a set of condition to the existing
Aggregate functions.
Below mentioned are some of the commonly used Aggregate functions:

COUNT() - counts the number of rows that meet the given conditions
MAX() & MIN() - return the largest & smallest value that meet the query conditions
SUM() & AVG() - return the sum and average of the values in the column
GROUP BY - used to combine rows with identical values into summary rows. It is typically used with aggregate functions
such as COUNT, SUM, etc
Task Write a query to output the first 5 rows of the table 'marks
select * from marks Limit 5;

Null
While analyzing a table in a database, many a times you'll come across cells which are empty. Those cells are denoted as
NULL. IS NULL and IS NOT NULL are the keywords used to check if a cell has a Null value or note.

Below is a query to output the name of the students who has not added their guardian contact number.

SELECT St_name
FROM Student
WHERE Guardian_contact IS NULL;
Note: In the above query we didn't use, WHERE Guardian_contact = 'NULL'. It would've given an error if used.

Task Choose the correct answer from the given MCQ.

IS NULL returns all records that contain a NULL value in the specified column.
IS NOT NULL returns all records that do not contain a NULL value in the specified column.
IS NULL can be used with any data type.

Case - When
CASE WHEN are used to add conditional logic to the sql queries.
Let's try it out with an example. Imagine we want to get a count of employees of an organisation categorised based on
their pay as follows:

Less than Rs.20000 : Level 1

Rs.20001- Rs.40000 : Level 2
More than Rs.40000 : Level 3
The query for the same is as mentioned below:

SELECT
CASE
WHEN pay < 20000 THEN 'Level 1'
WHEN pay BETWEEN 20001 AND 40000 THEN 'Level 2'
WHEN pay >= 40000 THEN 'Level 3'
 ELSE 'NA' -- If the above 3 conditions are not met, the row entry will be NA
END AS Pay_category, -- Renaming the column as Pay_category
COUNT(*) as emp_count
FROM employee
GROUP BY 1;
If the ELSE condition is satisfied, then a new category 'NA' will be added. However, it is not necessary to add the ELSE
statement. In the absence of ELSE, if none of the cases satisfies then it will return a NULL value. 'Pay_category' is the
alias for the CASE statement.

Task Write a query to do the following

Categorise the students based on the marks into grades.

Marks Less than 50 - C,
Marks between 50 and 80 - B,
Marks more than 80 - A
You need to output the following - 'Grades' and 'student_count'
Give the Alias name for the CASE as 'Grades'
Count the students in each category and assign it the alias 'student_count'
Your table 'marks' has the following columns : St_id ,Marks

SELECT
CASE
WHEN marks < 50 THEN 'C'
WHEN marks BETWEEN 50 AND 80 THEN 'B'
WHEN marks > 80 THEN 'A'
ELSE 'NA'
END AS Grades,
COUNT(*) AS Student_count
FROM marks
GROUP BY 1;

Count using Case

Previously we have used COUNT to fetch the total number of rows in a table or a specific column.
Using the Case statement, we can add certain conditions and those cells which satisfy condition will only be counted.
Below is a query to count the number of employees who have a salary more than 200,000 in each departments:
SELECT department, COUNT(CASE WHEN salary> 200000 THEN 1 ELSE NULL END) as High_Salary FROM employee
GROUP BY department;
In the above query, all the cells in the column 'salary' is checked if it is more than 200000.
1. If its satisfies it will return 1
2. Else return NULL.
3. The count is applied to the 1's and NULL's are ignored.
Task Write a query to count the number of students across departments who has scored more than 80 marks.
Alias the count coulmn as 'Dept_HighScore_Count' Your table 'student' has the following columns:
St_id , St_name, Marks , Department
Expected output
┌────────────┬──────────────────────┐
│ department │ Dept_HighScore_Count │
├────────────┼──────────────────────┤
│ Biology │ 0 │
│ English │ 0 │
│ History │ 3 │
│ Math │4 │
│ Physics │ 4
SELECT department,
COUNT(CASE WHEN Marks> 80 THEN 1 ELSE NULL END) AS Dept_HighScore_Count
FROM student
GROUP BY department;

Sum using Case

We've learned the concept of SUM, that its used to find the sum of the cells of a particular column. Adding CASE to the
sum, helps us to filter out the cells which are to be considered while calculating the sum of a column Below is a query to
find the sum of salaries of the employee across department who has an experience more than 3 years , from table
'employee':

SELECT Department, SUM(CASE WHEN Exp >3 THEN Salary ELSE 0 END) as Sum_High_Salary FROM employee
GROUP BY 1;

In the above query,

The CASE statement is used to check if the Exp column value is greater than 3
If the condition is true, the Salary of the employee is added to the sum; otherwise, 0 is added.
The SUM function then calculates the sum of all the salaries that meet the condition.
The resulting sum is given an alias of Sum_High_Salary.

Task Write a query to find the sum of fee paid by the students, aged above 20 across departments. Alias the sum column
as 'Sum_Senior_Fee'. You need to output the columns - 'Department' and 'Sum_Senior_Fee'.

Expected output
┌────────────┬────────────────┐
│ Department │ Sum_Senior_Fee │
├────────────┼────────────────┤
│ English │ 5700 │
│ History │ 1800 │
│ Math │ 3700 │
│ Science │ 4700
Your table 'student' has the following columns: St_id , St_name, Fee, Department ,Age

SELECT Department,
SUM(CASE WHEN Age >20 THEN Fee ELSE 0 END) as Sum_Senior_Fee
FROM student
GROUP BY 1;

Combining Aggregates

In the previous problem we've used 'CASE' to add a condition to find the [Link] can also be used to find the
ratios or percentage using a [Link] is a query to find what percentage of the organization's total payout is
paid as a salary to the employees who has an experience more than 3 years , from table 'employee':

SELECT Department,(100*(SUM(CASE WHEN Exp >3 THEN Salary ELSE 0 END))/SUM(Salary)) as High_Salary_percentage
FROM employee GROUP BY 1;

In the above query, the CASE statement is used to check if the Exp column value is greater than 3.
If the condition is true, the Salary of the employee is added to the sum; otherwise, 0 is added.
The first SUM function then calculates the sum of all the salaries that meet the condition.
And, second SUM calculates the total salary across all employees.
Once both the SUM's are calculated we divide them and multiply by 100 to get the percentage.
The resulting percentage is given an alias of High_Salary_percentage.
Task Write a query to find the percentage of fee paid by the students, aged above 20 to the total fee by all the students
across [Link] the resulting percentage column as Senior_Fee_Percentage. Output the columns 'Department'
and 'Senior_Fee_Percentage'.

Expected output

│ Department │ Senior_Fee_Percentage │

├────────────┼───────────────────────┤

│ English │ 75 │

│ History │ 32 │

│ Math │ 48 │

│ Science │ 51

Your table 'student' has the following columns: St_id , St_name, Fee , Department, Age
SELECT Department,
(100*(SUM(CASE WHEN Age >20 THEN Fee ELSE 0 END))/sum(Fee)) as
Senior_Fee_Percentage
FROM student
GROUP BY 1;
Analytics case studies Programming Practice Problem Course Online - CodeChef
SQL: Topic-wise practice Programming Practice Problem Course Online - CodeChef
Learn Applying SQL at Work (Real-Life SQL) Practical Excercise - CodeChef