Introduction to SQL

Chapter 3
Introduction to SQL

 SQL : Structural Query Language

 It is used for storing, manipulation, and retrieving data from the
database.
 SQL is an ANSI (American National Standards Institute) standard
language, but there are many different versions of the SQL
language.
 SQL history
 Installation of Postgres
SQL Commands
 These commands can be classified into the following groups
based on their nature:
1. DDL: Data definition language
 CREATE, ALTER, DROP
2. DML - Data Manipulation Language
 SELECT, INSERT, UPDATE, DELETE
3. DCL - Data Control Language
 GRANT, REVOKE
4. TCL-Transaction Control Language
 COMMIT, ROLLBACK, SAVEPOINT
 Section 1
Database creation and Updates
DDL statements
Database design
 Steps of database design
1. Conceptual design
2. ER model
3. Database Schema
4. Implementing it using SQL
Data types in
SQL
 bit : It is an integer data type of 1
byte size. In order to save space in
the database, the string values TRUE
and FALSE usually can be converted
to bit values as 1 and 0.
 tinyint : It is an integer data type
with a size of 1 byte, used for integer
data between 0 and 255 values.
 smallint : It is an integer data type
with a size of 2 bytes, which can take
values between -32.768 and 32.768.
 int or integer : It is the primary
integer data type in SQL Server. It is
used for between -[Link] and
[Link] values; takes up 4
bytes size.
 bigint : It is intended for use when
integer values might exceed the
range that is supported by the int
data type. It takes up 8 bytes and
takes values between -
[Link].854.775.808 and
[Link].854.775.807.
Data types in SQL
 decimal or dec : In this data type, the number of
digits of the number and scale to be stored can be
defined. The size depends upon precision. The number
of precision digits can be defined between 1 and 38.
 numeric : Decimal and numeric are functionally
identical and can be used interchangeably.
 float : Used for floating-point numbers which are
approximate numeric values. And the size varies
according to the parameter it takes. Between -1.79E +
308 and 1.79E + 308 values it takes.
 real : The ISO synonym for real is float(24). Between -
3.40E + 38 and
3.40E + 38 values it takes. No parameters are required
when declaring this type, takes up 4 bytes.
 money : Data types that represent monetary or
currency values. It stores data of decimal type
sensitive up to 4 digits. It takes up 8 bytes
 smallmoney : Same as money type only takes up 4
bytes.
Data types in SQL
 date : Its format is YYYY-MM-DD and takes up
3 bytes.
 time : Its default format is
hh:mm:ss[.nnnnnnn] and takes up between 3
to 5 bytes.
 datetime : It defines a combined data type
date with a time of day with fractional
seconds(.nnnnnnn).It takes up 8 bytes.
 smalldatetime : It is used date and time
values only between 1900–01–01 00:00:00
and 2079–06–06 23:59:59. It takes up 4 bytes.
 timestamp : It generates automatically
binary numbers, unique in the database, used
mostly to the rows identification.
 Data types in SQL
 varchar(n) or character varying(n) : It can
contain numbers, letters, and special characters. It
takes up as much as the size data.
 char(n) or character(n) : They can have values up
to 8000 characters. It is a character string with a
fixed width.
It means if you assign a value to a character column
containing fewer characters than the defined length,
the remaining space is filled with blanks characters.
 nchar : Same as char additionally supports
Unicode.
 nvarchar(max) or nvarchar(n) : national
character varying type is the same as varchar
except that it holds standardized multibyte
characters or Unicode characters. If max is
specified, the maximum number of characters is
2GB.(max length : 4000 characters)
 text : Even if a value less than the specified value is
entered, it takes up to its size. It does not support
Unicode.
 ntext : It takes up as much as the entered
character size of data, supports Unicode.
 Data types in SQL
 BLOB(n) : It is used for BLOBs (Binary Large
Objects). It can take up to 65,535 bytes.
 binary : It presents 1 and 0 with fixed length. The
maximum length can be up to 8000 bytes.
 varbinary : It is a binary string of variable width. It
takes up maximum of 8,000 bytes.
 image : It is a binary string of variable width up to
2³¹-1 (2,147,483,647) bytes.
 xml : It is a special data type for storing the XML
data in SQL Server tables. The size is variable.
 CLOB [(length)] : A Character Large OBject (or
CLOB) is a collection of character data in a database
management system, usually stored in a separate
location that is referenced in the table itself.
 Json: allows you to store, query, and manipulate
JSON (structured) data directly within a relational
database. Many databases, such as PostgreSQL,
MySQL, and SQL Server, provide native support for
the JSON data type, allowing you to handle semi-
structured data more efficiently.
Create Database
 It creates new database schema
 Syntax: CREATE DATABASE database_name;
 To check whether a database exists or not
 Syntax: show databases
 Drop database
 Syntax: DROP DATABASE database_name
Create table
 Syntax:
 CREATE TABLE table_name( column1 datatype, column2 datatype, column3
datatype, ..... columnN datatype,
PRIMARY KEY( one or more columns ) );
 Example: CREATE TABLE cutomer(id integer, first_name varchar(10),
last_name varchar(10), city varchar(10), country varchar(15), phone
varchar(15));
 To check schema of a table:
 DESC tablename;
Insert into

 Insert into:
 Syntax: INSERT INTO table_name( column1, column2....columnN) VALUES
( value1, value2....valueN);
 Example:INSERT INTO customer(id , first_name,
last_name ,city ,country,phone)VALUES (2, ‘Ana’, ‘Trujillo’, ‘Mexico’, ‘Mexico’,
(5) 555-4729);
 If users are adding values for all the columns of the table, you don’t
need to specify the column names in the SQL query.
 However, ensure the order of the values is in the same order as the
columns in the table.
Alter and update table
 Alter table:
 Syntax: ALTER TABLE table_name {ADD|DROP|MODIFY} column_name
{data_ype};
 ALTER TABLE table_name RENAME TO new_table_name;
 Update table:
 UPDATE table_name SET column1 = value1, column2 =
value2....columnN=valueN [ WHERE CONDITION ];
 Rename column and table
 ALTER TABLE members RENAME COLUMN join_date TO jd;
 Alter table customer_details rename to cust_details;
Alter and update table
 ALTER TABLE employees ADD hire_date DATE;
 ALTER TABLE employees DROP COLUMN department_id;
 ALTER TABLE employees MODIFY salary DECIMAL(12, 2);
OR
 ALTER TABLE employees ALTER COLUMN salary TYPE DECIMAL(12, 2);
 ALTER DATABASE old_database_name RENAME TO new_database_name;
 DROP DATABASE database_name;
Delete, Drop, Truncate

 The DROP TABLE statement in SQL is used to drop an existing table in a

database.(drop structure also)
 Syntax: DROP TABLE table name;
 Delete: (delete only row)
 DELETE FROM table_name WHERE {CONDITION};
 DELETE FROM employees WHERE employee_id = 2;
 Truncate: (delete only data)
 TRUNCATE TABLE table_name;
 TRUNCATE TABLE employees;
Truncate
 Key Points about TRUNCATE:
 Removes All Rows: It removes all rows from the table, similar to a
DELETE without a WHERE clause, but faster.
 No WHERE Clause: You cannot specify a WHERE clause with
TRUNCATE. It will always remove all rows from the table.
 Resets Identity Columns: If the table has any SERIAL or IDENTITY
columns, the counter will be reset to the starting value.
 Cannot Be Rolled Back in Some Cases: In some systems,
TRUNCATE cannot be rolled back if issued outside a transaction.
 Foreign Key Constraints: You cannot truncate a table that has
foreign key constraints unless you use CASCADE.
Truncate
 Example with CASCADE:
 If the employees table has foreign key constraints and you want to
truncate it and any dependent tables, you can use:
 TRUNCATE TABLE employees CASCADE;

 This will truncate the employees table and any tables that have a foreign key
reference to it.
SQL constraints
 The Constraints in SQL can be specified when the table is created with
the CREATE TABLE statement, or after the table is altered with the ALTER
TABLE statement.
 Syntax: CREATE TABLE table_name ( column1 datatype constraint,
column2 datatype constraint, column3 datatype constraint, .... );
 SQL constraints are used to specify any rules for the records in a table.
 Constraints can be used to limit the type of data that can go into a
table.
 if there is any violation between the constraint and the record action,
the action is aborted.
 Constraints can be column level or table level.
SQL constraints
NOT NULL using alter table
 Add not null constraint:
 While creating:
 CREATE TABLE table_name ( column_name data_type NOT NULL, ... );
 CREATE TABLE employees ( employee_id SERIAL PRIMARY KEY, first_name
VARCHAR(100) NOT NULL, last_name VARCHAR(100) NOT NULL, email
VARCHAR(255), salary DECIMAL(10, 2) NOT NULL );
 Adding constraint to existing table:
 ALTER TABLE table_name ALTER COLUMN column_name SET NOT NULL;
 ALTER TABLE employees ALTER COLUMN email SET NOT NULL;
 Drop constraint
 ALTER TABLE table_name ALTER COLUMN column_name DROP NOT NULL;
 ALTER TABLE employees ALTER COLUMN email DROP NOT NULL;
UNIQUE using alter table
 Add unique constraint:
 While creating:
 CREATE TABLE table_name ( column_name data_type
UNIQUE, ... );
 CREATE TABLE employees ( employee_id SERIAL PRIMARY KEY,
email VARCHAR(255) UNIQUE, -- This will enforce uniqueness on
the email column first_name VARCHAR(100), last_name
VARCHAR(100) );
 Adding constraint to existing table:
 ALTER TABLE table_name ADD CONSTRAINT constraint_name
UNIQUE (column_name);
 ALTER TABLE employees ADD CONSTRAINT unique_email UNIQUE
(email);
 ALTER TABLE employees ADD CONSTRAINT
unique_first_last_name UNIQUE (first_name, last_name);
PRIMARY KEY using alter table
 Add PRIMARY constraint:
 While creating:
 CREATE TABLE table_name ( column_name data_type PRIMARY KEY, ... );
 CREATE TABLE employees ( employee_id SERIAL PRIMARY KEY, first_name VARCHAR(100),
last_name VARCHAR(100), email VARCHAR(255) );
 CREATE TABLE order_items ( order_id INT, product_id INT, quantity INT, PRIMARY KEY (order_id,
product_id) );
 Adding constraint to existing table:
 ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY (column_name);
 ALTER TABLE employees ADD CONSTRAINT pk_employee_id PRIMARY KEY (employee_id);
 ALTER TABLE order_items ADD CONSTRAINT pk_order_items PRIMARY KEY (order_id, product_id);
 Drop constraint
 ALTER TABLE employees DROP CONSTRAINT pk_employee_id;
FOREIGN KEY using alter table
 Add Foreign key constraint:
 While creating:
 CREATE TABLE table_name ( column_name data_type, FOREIGN KEY
(column_name) REFERENCES parent_table(parent_column) );
 CREATE TABLE departments ( department_id SERIAL PRIMARY KEY,
department_name VARCHAR(100) );
 CREATE TABLE employees ( employee_id SERIAL PRIMARY KEY, first_name
VARCHAR(100), last_name VARCHAR(100), department_id INT, FOREIGN KEY
(department_id) REFERENCES departments(department_id) );
 Adding constraint to existing table:
 ALTER TABLE child_table ADD CONSTRAINT constraint_name FOREIGN KEY
(column_name) REFERENCES parent_table(parent_column);
 ALTER TABLE employees ADD CONSTRAINT fk_department FOREIGN KEY
(department_id) REFERENCES departments(department_id);
 Drop constraint
 ALTER TABLE employees DROP CONSTRAINT fk_department;
FOREIGN KEY using alter table
 Add Foreign key constraint:
 Drop constraint
 ALTER TABLE employees DROP CONSTRAINT fk_department;
 ON DELETE CASCADE: When a row in the parent table is deleted, the
corresponding rows in the child table are also deleted.
 ON DELETE SET NULL: When a row in the parent table is deleted, the
foreign key column in the child table is set to NULL.
 ALTER TABLE employees ADD CONSTRAINT fk_department FOREIGN KEY
(department_id) REFERENCES departments(department_id) ON DELETE
CASCADE;
CHECK
 Add Check constraint:
 CREATE TABLE table_name ( column_name data_type, CHECK (condition)
);
 CREATE TABLE employees ( employee_id SERIAL PRIMARY KEY,
first_name VARCHAR(100), last_name VARCHAR(100), salary
DECIMAL(10, 2), CHECK (salary > 0) -- Ensures salary is always greater
than 0 );
 Adding CHECK to an Existing Table:
 ALTER TABLE employees ADD CONSTRAINT check_salary_positive CHECK
(salary > 0);

DROP: ALTER TABLE employees DROP CONSTRAINT check_salary_positive;

DEFAULT
 Add Check constraint:
 CREATE TABLE table_name (column_name data_type DEFAULT
default_value );
 CREATE TABLE employees ( employee_id SERIAL PRIMARY KEY,
first_name VARCHAR(100), last_name VARCHAR(100), hire_date DATE
DEFAULT CURRENT_DATE -- Sets the default value to the current date );
 Adding DEFAULT to an Existing Table:
 ALTER TABLE employees ALTER COLUMN hire_date SET DEFAULT
CURRENT_DATE;
 ALTER TABLE employees ALTER COLUMN hire_date DROP DEFAULT;
INDEX
 Add INDEX constraint:
 CREATE INDEX index_name ON table_name (column_name);
 CREATE INDEX idx_employee_last_name ON employees (last_name);
 DROP INDEX index_name;
Data retrieval
Section 2
SQL Query
 A database most often contains tables. Some name identifies each table. The table
includes records(rows) with Data. To access those records, we need SQL Syntax.
Most of the action you need to perform Database by using the SQL Statement.
 Note: SQL keywords are not case-sensitive (e.g., select as SELECT)
 Keywords include SELECT, UPDATE, WHERE, ORDER BY ETC. Four fundamental
operations that can apply to any databases are:
 Read the Data -- SELECT
 Insert the new Data -- INSERT
 Update existing Data -- UPDATE
 Remove Data –DELETE
 These operations are referred to as the CRUD (Create, Read, Update, Delete).
Movie dataset

 For all the queries in upcoming slides we will use Movie dataset
 Import dataset in postgres
 First try to understand dataset using excel file of the dataset
 In excel file for one table there is one sheet [Upcoming lab session]
SELECT Statement

 The SELECT statement permits you to read data from one or more
tables.
 Syntax:
SELECT column1, column2, ,columnN
FROM table_name;
 Examples:
 Simply print all the movies
 Get movie title and industry for all the movies
SELECT Statement

 Examples:
 Simply print all the movies SELECT * FROM movies;
 Get movie title and industry for all the movies SELECT title, industry FROM
movies;
SELECT DISTINCT
 The SELECT DISTINCT statement is to return the different values. Output:
studio
 Syntax: Hombale Films
Marvel Studios
SELECT DISTINCT column1, column2. column
United Producers
Yash Raj Films
FROM table_name; Vinod Chopra Films
Dharma Productions
 Example: Castle Rock Entertainment
Warner Bros. Pictures
 Get all the unique studio in the movies database Columbia Pictures
 Universal Pictures
SELECT DISTINCT studio FROM movies;
Paramount Pictures
SELECT DISTINCT studio FROM movies WHERE Liberty Films
20th Century Fox
studio IS NOT NULL; Syncopy
Arka Media Works
Zee Studios
Salman Khan Films
Mythri Movie Makers
DVV Entertainment
Government of West Bengal
Vinod Chopra Productions
NULL
WHERE CLAUSE

 The WHERE clause allows the user to filter the data from
the table. The WHERE clause allows the user to extract
only those records that satisfy a specified condition.
 SQL requires single quotes around text values (many
database systems will also use double quotes). And
numeric fields should not be enclosed in quotes.
 Syntax:
SELECT column1, column2,columnN
FROMtable_name WHERE CONDITION;
Example:
 Print all movies from Hollywood
 SELECT * FROM movies WHERE industry = 'Hollywood';
 1) movies(movie_id title industry
release_year imdb_rating studio

SQL AND/OR Clause language_id)

2) Financial(movie_id budget revenue unit
currency)
3) Actors(actor_id name birth_year)
 4) Movie_actor(movie_id actor_id)
To merge more than one conditions
5) Language(language_id name)
 Syntax:
SELECT column1, column2…….columnN
FROM table_name
WHERE CONDITION-1 {AND|OR} CONDITION-2;
 Example:
 Which movies had greater than 9 imdb rating?
 Movies with rating between 6 and 8
 Select all movies whose release year can be 2018 or 2019.
SQL AND/OR Clause
 Example:
 Which movies had greater than 9 imdb rating?
 SELECT * FROM movies WHERE imdb_rating > 9;
 Movies with rating between 6 and 8
 SELECT * FROM movies WHERE imdb_rating > 6 AND imdb_rating < 8;
 Select all movies whose release year can be 2018 or 2019.
 SELECT * FROM movies WHERE release_year = 2018 OR release_year = 2019;
movie_ release_ye imdb_rati
title industry studio language_id
id ar ng
The
Hollywo
111 Shawshank 1994 9.3 Castle Rock Entertainment 5
od
Redemption
The Hollywo
120 1972 9.2 Paramount Pictures 5
Godfather od
IN clause
 IN: Specify multiple possible values for a column
 Syntax:
SELECT column1, column2. columnN
FROM table_name
WHERE column_name IN (val-1, val-2, val-N);
 Example:
 Select all movies whose release year can be 2018 or 2019 or 2022
 SELECT * FROM movies WHERE release_year IN (2018, 2019, 2022);
BETWEEN Clause

 BETWEEN: Between a range

 Syntax:
SELECT column1, column2. columnN
FROM table_name
WHERE column_name BETWEEN val-1 AND val-2;
 Example:
 Movies with rating between 6 and 8

SELECT * FROM movies WHERE imdb_rating

BETWEEN 6 AND 8;
LIKE Clause
movie_id title industry release_year imdb_rating studio language_id

Thor: The Marvel

 103 To find pattern
LIKE: Dark World
Hollywood 2013 6.8
Studios
5

 Syntax:
104
Thor:
Hollywood 2017 7.9
Marvel
5
Ragnarok Studios
SELECT column1, column2. column
Thor: Love Marvel
105 Hollywood 2022 6.8 5
FROM and Thunder
table_name Studios

WHERE column_name LIKE { PATTERN };

 Pattern contains two symbols: %(any number of character) and _(one character)
 Example:
 Select all movies that starts with THOR
 SELECT * FROM movies WHERE title LIKE 'THOR%';
 Select all movies that have 'America' word in it.
movie_id title industry release_year imdb_rating studio language_id
 SELECT * FROM movies WHERE title LIKE '%America%';
Captain
Marvel
137 America: The Hollywood 2011 6.9 5
Studios
First Avenger
Captain
America: The Marvel
138 Hollywood 2014 7.8 5
Winter Studios
Soldier
  COUNT: To calculate total rows in the column
COUNT  Syntax:
Clause SELECT COUNT(column_name) FROM
table_name WHERE CONDITION;
 Example:
 How many total movies do we have in our movies table?
 SELECT COUNT(*) AS total_movies FROM movies;
 How many hollywood movies are present in the
database?
 SELECT COUNT(*) AS hollywood_movies FROM movies
WHERE industry = 'Hollywood';
  IS NULL: To check if column value is empty
IS NULL  Syntax:

and IS NOT SELECT column1, column2. columnN

FROM table_name
NULL WHERE column_name IS NULL;
 Example:
 Print all movies where we don't know the value of the studio
 SELECT * FROM movies WHERE studio IS NULL;
 All movies where imdb rating is not available
 SELECT * FROM movies WHERE imdb_rating IS NULL;
 All movies where imdb rating is available
 SELECT * FROM movies WHERE imdb_rating IS NOT NULL;
ORDER BY
 ORDER BY: To sort rows bases on values of a column
 Syntax:
SELECT column1, column2. columnN
FROMtable_name WHERE CONDITION
ORDER BY column_name {ASC|DESC}
LIMIT top_values_required
OFFSET how_many_top_values_to_skip;
 Example:
 Print first 5 bollywood movies with highest rating
 Select movies starting from second highest rating movie till next 5 movies from bollywood
ORDER BY
 ORDER BY: To sort rows bases on values of a column
 Example:
 Print first 5 bollywood movies with highest rating
 SELECT * FROM movies WHERE industry = 'Bollywood’ ORDER BY imdb_rating DESC LIMIT 5;
 Select movies starting from second highest rating movie till next 5 movies from Bollywood
 SELECT * FROM movies

WHERE industry = 'Bollywood’

ORDER BY imdb_rating DESC

OFFSET 1

movie LIMIT 5;
Summary analytics(MAX, MIN,
AVG,SUM)
 Syntax:
SELECT function(column_name) AS alias
FROM table_name
WHERE CONDITION;
 Example:
 Select highest imdb rating for bollywood movies
 Select lowest imdb rating for bollywood movies
 Print average rating of Marvel Studios movies
 Print min, max, avg rating of Marvel Studios movies
Summary analytics(MAX, MIN,
AVG,SUM)
Select highest imdb rating for bollywood movies

101 K.G.F: Chapter 2 Bollywood 2022 8.4 Hombale Films 3

Summary analytics(MAX, MIN,
AVG,SUM)
Select lowest imdb rating for bollywood movies

139 Race 3 Bollywood 2018 1.9 Salman Khan Films 1

Summary analytics(MAX, MIN,
AVG,SUM)
Print average rating of Marvel Studios movies

AVG 7.5
Summary analytics(MAX, MIN,
AVG,SUM)
Print min, max, avg rating of Marvel Studios movies

6.8
8.4
7.5
GROUP BY
 The GROUP BY used to group rows from the table
 It is often used with aggregate functions like (COUNT, MAX, MIN, SUM,
AVG) to group the result-set by one or more columns.
 Columns in the group by must be in select statement
 Syntax:
SELECT SUM(column_name)
FROM table_name
WHERE CONDITION
GROUP BY column_name;
 Example:
 Print count and average imdb rating of movies for each industry
GROUP BY
 Example:
 Print count and average imdb rating of movies for each industry

1) movies(movie_id title industry

release_year imdb_rating studio
language_id)
2) Financial(movie_id budget revenue unit
currency)
3) Actors(actor_id name birth_year)
4) Movie_actor(movie_id actor_id)
GROUP BY
 Example:
 Count number of movies released by a given production studio(hint: do not
include which doesn’t have studio information)

1) movies(movie_id title industry

release_year imdb_rating studio
language_id)
2) Financial(movie_id budget revenue unit
currency)
3) Actors(actor_id name birth_year)
4) Movie_actor(movie_id actor_id)
GROUP BY
 Example:
 What is the average rating of movies per studio and also order them by
average rating in descending format?

1) movies(movie_id title industry release_year

imdb_rating studio language_id)
2) Financial(movie_id budget revenue unit
currency)
3) Actors(actor_id name birth_year)
4) Movie_actor(movie_id actor_id)
GROUP BY
 Example:
 Find maximum imdb_rating of each industry

1) movies(movie_id title industry

release_year imdb_rating studio
language_id)
2) Financial(movie_id budget revenue unit
currency)
3) Actors(actor_id name birth_year)
4) Movie_actor(movie_id actor_id)
HAVING Clause
 The HAVING clause is added to SQL because the WHERE keyword can not
be used with aggregate functions.
 Syntax:
SELECT SUM(column_name)
FROM table_name WHERE CONDITION
GROUP BY column_name
HAVING (arithematic function condition);
Rules for Having clause
 Order of execution
FROM ->WHERE->GROUP BY ->HAVING->ORDER BY
 Columns used in Having must be present in select.
 Columns used in where may not be in select
 Example:
 Print all the years where more than 2 movies were released
 SELECT release_year, COUNT(movie_id) AS movie_count FROM movies GROUP BY
release_year HAVING COUNT(movie_id) > 2;
Having clause
Example:
 Find all the studios that produced more than 3 movies
SELECT studio, COUNT(movie_id) AS movie_count
FROM movies
WHERE studio IS NOT NULL
GROUP BY studio
HAVING COUNT(movie_count) > 3;
Data retrieval from
multiple tables Complex
Queries
Section 3
Schema
Joins

 The SQL Join help in retrieving data from two or more database tables.
The tables are mutually related using primary keys and foreign keys.
 The INNER JOIN is used to print rows from both tables that satisfy the
given condition.
 The INNER JOIN keyword selects records that have matching values in
both the tables.
Inner join
 Syntax:
SELECT table1.column1, table1.column2, table2.column1,
table2.column2, ... FROM table1
INNER JOIN table2
ON table1.common_column = table2.common_column;
 Example: If you want to join the movies table with the financial table using
movie_id as the common column.
 SELECT [Link], [Link], [Link] FROM movies INNER JOIN
financial ON movies.movie_id = financial.movie_id;
 Queries
 Find all movies that have a rating higher than the average
rating

•Movies(movie_id, title, industry, release_year, imdb_rating, studio, language_id)

•Financial(movie_id, budget, revenue, unit, currency)
•Actors(actor_id, name, birth_year)
•Movie_actor(movie_id, actor_id)
•Languages(language_id, name)
 Queries
 List all movies with actors who were born before 1980

•Movies(movie_id, title, industry, release_year, imdb_rating, studio, language_id)

•Financial(movie_id, budget, revenue, unit, currency)
•Actors(actor_id, name, birth_year)
•Movie_actor(movie_id, actor_id)
•Languages(language_id, name)
 Queries
 Find movies that earned more than 500 million in revenue

•Movies(movie_id, title, industry, release_year, imdb_rating, studio, language_id)

•Financial(movie_id, budget, revenue, unit, currency)
•Actors(actor_id, name, birth_year)
•Movie_actor(movie_id, actor_id)
•Languages(language_id, name)
 Queries
 List movies and their languages

•Movies(movie_id, title, industry, release_year, imdb_rating, studio, language_id)

•Financial(movie_id, budget, revenue, unit, currency)
•Actors(actor_id, name, birth_year)
•Movie_actor(movie_id, actor_id)
•Languages(language_id, name)
 Queries
 Find the total revenue generated by each studio

•Movies(movie_id, title, industry, release_year, imdb_rating, studio, language_id)

•Financial(movie_id, budget, revenue, unit, currency)
•Actors(actor_id, name, birth_year)
•Movie_actor(movie_id, actor_id)
•Languages(language_id, name)
 Queries
 List actors who have acted in more than 2 movies

•Movies(movie_id, title, industry, release_year, imdb_rating, studio, language_id)

•Financial(movie_id, budget, revenue, unit, currency)
•Actors(actor_id, name, birth_year)
•Movie_actor(movie_id, actor_id)
•Languages(language_id, name)
 •Movies(movie_id, title, industry, release_year, imdb_rating, studio, language_id)
•Financial(movie_id, budget, revenue, unit, currency)
•Actors(actor_id, name, birth_year)

Queries
•Movie_actor(movie_id, actor_id)
•Languages(language_id, name)

 List actors who have acted in more than 2 movies

•JOIN movie_actor ON actors.actor_id = movie_actor.actor_id: Joins the actors table

with the movie_actor table using actor_id as the common column.
•GROUP BY actors.actor_id, [Link]: Groups by both the actor_id and name to
ensure the correct aggregation. This is necessary because PostgreSQL requires that any
column in the SELECT clause that's not an aggregate (like COUNT) must be
included in the GROUP BY clause.
•HAVING COUNT(movie_actor.movie_id) > 2: Filters the result to only include actors
who have appeared in more than 2 movies.
 Queries
 Find actors who acted in movies from different industries

•Movies(movie_id, title, industry, release_year, imdb_rating, studio, language_id)

•Financial(movie_id, budget, revenue, unit, currency)
•Actors(actor_id, name, birth_year)
•Movie_actor(movie_id, actor_id)
•Languages(language_id, name)
Left outer join
 The LEFT JOIN returns all the records from the table1 (left table) and the
matched records from the table2 (right table). The output is NULL from
the right side if there is no match.
 Syntax:
SELECT column_name(s) FROM table1 LEFT JOIN table2 ON
table1.column_name = table2.column_name;
 Sholay and inception doesn’t have info in financials
Right join
 The RIGHT JOIN is the opposite of LEFT JOIN.
 The RIGHT JOIN prints all the columns from the table2(right table) even
if there no matching rows have been found in the table1 (left table).
 If there no matches have been found in the table1 (left table), NULL is
returned
 Syntax:
SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON
table1.column_name = table2.column_name;
 402 and 416 doesn’t have info in movies
Full outer join
 The FULL OUTER JOIN keyword returns all records when there are a match
in left (table1) or right (table2) table records.
 Syntax:
SELECT column_name(s) FROM table1 FULL OUTER JOIN table2 ON
table1.column_name = table2.column_name WHERE condition
 Note: Postgres does not support the Full Join, so we can perform left join
and right join separately then take the union of them.
 Syntax:
SELECT * FROM t1 LEFT JOIN t2 ON [Link] = [Link]
UNION
SELECT * FROM t1 RIGHT JOIN t2 ON [Link] = [Link]
Union
 The UNION operator allows the user to combine the result-set of two or
more SELECT statements in SQL.
 Each SELECT statement within UNION should have the same number of
columns.
 The columns in each SELECT statement should also be in the same order.
 The columns should also have similar data types.
 Syntax:
Select column_name(s) from table1
UNION
Select column_name(s) from table2;
Union All
 The UNION operator selects only different values by default.
 To allow duplicate values, the user can use UNION ALL operator.
 Syntax:
SELECT column_name(s) FROM table1 UNION ALL SELECT
column_name(s) FROM table2;
Group_concat
 The GROUP_CONCAT() function is used to concatenate data from multiple rows into one field, In
PostgreSQL, there isn't a direct GROUP_CONCAT() function like in MySQL, but you can achieve similar
results using the string_agg() function. This function allows you to concatenate strings from multiple
rows into a single string, with a specified separator.
 This is an aggregate function which returns a String value, if the group contains at least one
non-NULL value. Otherwise, it returns NULL.
 Syntax:

Select count(*), group_concat(title separator “,”) from

movies group by industry

SELECT [Link], string_agg([Link], ', ') AS actors

FROM movies m
JOIN movie_actor ma
ON m.movie_id = ma.movie_id
JOIN actors a ON ma.actor_id = a.actor_id
Subquery
 A subquery in MySQL is a query, which is nested into another SQL query
and embedded with SELECT, INSERT, UPDATE or DELETE statement
along with the various operators.
 We can also nest the subquery with another subquery. A subquery is
known as the inner query, and the query that contains subquery is
known as the outer query.
 The inner query executed first gives the result to the outer query, and
then the main/outer query will be performed.
 Postgres allows us to use subquery anywhere, but it must be closed
within parenthesis.
Rules for subqueries
 Subqueries should always use in parentheses.
 If the main query does not have multiple columns for subquery, then a
subquery can have only one column in the SELECT command.
 We can use various comparison operators with the subquery, such as >,
<, =, IN, ANY, SOME, and ALL. A multiple-row operator is very useful
when the subquery returns more than one row.
 We cannot use the ORDER BY clause in a subquery, although it can be
used inside the main query.
 If we use a subquery in a set function, it cannot be immediately
enclosed in a set function.
Subqueries
 Syntax:
SELECT column_list (s) FROM table_name
WHERE column_name OPERATOR
(SELECT column_list (s) FROM table_name [WHERE])
SQL queries

 Select a movie with highest imdb_rating

 SELECT title, imdb_rating FROM movies ORDER BY imdb_rating DESC
LIMIT 1;
 select all movies whose rating is greater than *any* of the marvel
movies rating
 SELECT title, imdb_rating FROM movies WHERE imdb_rating > ANY
( SELECT imdb_rating FROM movies WHERE studio = 'Marvel Studios' );
 select all movies whose rating is greater than *all* of the marvel movies
rating
 SELECT title, imdb_rating FROM movies WHERE imdb_rating > ALL
( SELECT imdb_rating FROM movies WHERE studio = 'Marvel Studios' );
SQL Databases
Lecture 4
Advanced SQL concepts
Table of content

 Common table expression (CTE)

 View
Common Table Expressions(CTE)
 common table expression (CTE) is a temporary named result set that you can
reference within a SELECT, INSERT, UPDATE, or DELETE statement.
 You can also use a CTE in a CREATE a view, as part of the view’s SELECT query
 Syntax:
WITH cte_name AS (
-- CTE query
SELECT ...
)
SELECT ...
FROM cte_name;
 Select statement of cte is valid only its scope, after its scope (;) its not valid
Common Table Expressions(CTE)
Find the highest IMDb rating in Bollywood and list movies with a
higher IMDb rating than that in other industries
WITH highest_bollywood_rating AS (
SELECT MAX(imdb_rating) AS max_rating
FROM movies
WHERE industry = 'Bollywood’ )
SELECT title, industry, imdb_rating
FROM movies
WHERE imdb_rating > (SELECT max_rating FROM
highest_bollywood_rating);
Common Table Expressions(CTE)
List the movies with a higher-than-average rating in their
respective industries.
WITH industry_avg_rating AS i(
SELECT industry, AVG(imdb_rating) AS avg_rating
FROM movies
GROUP BY industry )
SELECT [Link], [Link], m.imdb_rating, i.avg_rating
FROM movies m
JOIN industry_avg_rating ON [Link] = [Link]
WHERE m.imdb_rating > i.avg_rating;
Recursive Common Table
Expressions(CTE)
Find movies with multiple sequels. [In this example, movies with similar titles are part of the
same series (e.g., "Thor", "Thor: The Dark World", "Thor: Ragnarok").]
which are useful for hierarchical or sequential data.
WITH RECURSIVE movie_sequels AS(
SELECT movie_id, title, 1 AS sequel_number
FROM movies
WHERE title LIKE 'Thor%’
UNION ALL
SELECT m.movie_id, [Link], ms.sequel_number + 1
FROM movies m
JOIN movie_sequels ms ON [Link] LIKE 'Thor%’
WHERE [Link] LIKE 'Thor%’
)
SELECT * FROM movie_sequels;
Recursive Common Table
Expressions(CTE)
WITH RECURSIVE fibonacci_sequence AS (
-- Base case: starting values for the Fibonacci sequence
SELECT 0 AS n1, 1 AS n2, 1 AS position

UNION ALL
-- Recursive step: calculate the next Fibonacci number
SELECT n2, n1 + n2, position + 1
FROM fibonacci_sequence
WHERE position < 10 -- limiting to the first 10 Fibonacci numbers
)
SELECT n1 AS fibonacci_number
FROM fibonacci_sequence;
Views
 The view is a virtual table based on the result-set of an SQL statement. A
view holds rows and columns, like a real table.
 You can add SQL functions, WHERE, and JOIN statements to a view and
present the data as if the data were coming from one single table.
 Syntax:
CREATE VIEW view_name
AS SELECT column1, column2, ... FROM table_name
WHERE condition;
 A view always shows up-to-date data! The database engine recreates the
data, using the view's SQL statement, every time a user queries a view.
 SQL query

 Create view which has % profit of each movie.

 Create view which has all the details of actors and their age.

•Movies(movie_id, title, industry, release_year, imdb_rating, studio, language_id)

•Financial(movie_id, budget, revenue, unit, currency)
•Actors(actor_id, name, birth_year)
•Movie_actor(movie_id, actor_id)
•Languages(language_id, name)
WITH CHECK OPTION

 The WITH CHECK OPTION in SQL is a CREATE VIEW statement option.

 The objective of the WITH CHECK OPTION is to make sure that all UPDATE and
INSERTs satisfy the condition(s) in the view definition.
 If they do not satisfy the condition(s), the UPDATE or INSERT returns an error.
 Syntax:
CREATE VIEW CUSTOMER_VIEW AS
SELECT name, age FROM customers
WHERE age IS NOT NULL
WITH CHECK OPTION;

CREATE VIEW HighRatedMovies AS SELECT movie_id, title, imdb_rating FROM

movies WHERE imdb_rating > 8.0 WITH CHECK OPTION;
DELETING ROWS INTO A VIEW

 Rows of data can be deleted from a view. The same rules that apply to the
UPDATE and INSERT commands apply to the DELETE command.
 DELETE FROM CUSTOMER_VIEW WHERE age = 25;
 If you are deleting from the table which contains primary key run this
command before;
SET FOREIGN_KEY_CHECKS=0;
 Deletion from the view also deletes from original table;
 DROPPING VIEWS
 Where the user has a view, you need a method to drop the view if it is no
longer needed. The query is straightforward and is given below:
 DROP VIEW view_name;
Difference between CTE and View
 In contrast to a CTE, a view is a physical object in a database and is stored
on a disk.
 However, views store the query only, not the data returned by the query.
The data is computed each time you reference the view in your query.
 For queries that are referenced occasionally (or just once), it’s usually
better to use a CTE. If you need the query again, you can just copy the CTE and
modify it if necessary.
 If you tend to reference the same query often, creating a corresponding view is a
good idea. However, you’ll need create view permission in your database to
create a view.
 Access management. A view might be used to restrict particular users’ database
access while still allowing them to get the information they need. You can give users
access to specific views that query the data they’re allowed to see without exposing
the whole database. In such a case, a view provides an additional access layer.
SQL Databases
Lecture 5
Table of content

 Stored Procedure
 Trigger
Stored procedure
 A stored procedure in a database is a set of SQL statements that are
stored and executed on the database server.
 They allow for the encapsulation of logic that can be reused and
executed multiple times, possibly with different parameters.
 Stored procedures can take input parameters, process data, return
output parameters, and even return result sets.
Stored procedure
CREATE OR REPLACE PROCEDURE procedure_name(parameters)
LANGUAGE plpgsql
AS $$
BEGIN
-- SQL logic here
END;
$$;
Stored procedure
 Execution of the Stored Procedure is very simple by using the
CALL procedure_name;
 Drop procedure use to delete the stored procedure from the databases.
The following query used to delete the stored procedure for the
Database:
DROP Stored_procedure_name;
Procedure to Calculate Profit Percentage for a Movie
CREATE OR REPLACE PROCEDURE calculate_profit_percentage(p_movie_id INT)
LANGUAGE plpgsql
AS $$
DECLARE
v_budget NUMERIC;
v_revenue NUMERIC;
v_profit_percentage NUMERIC;
BEGIN
----Fetch budget and revenue from the Financial table
SELECT budget, revenue INTO v_budget, v_revenue
FROM Financial
WHERE movie_id = p_movie_id;
----If the movie has budget and revenue, calculate profit percentage
IF v_budget IS NOT NULL AND v_revenue IS NOT NULL THEN
v_profit_percentage := (v_revenue / v_budget) * 100;
RAISE NOTICE 'Movie ID: %, Profit Percentage: %', p_movie_id, v_profit_percentage;
ELSE
RAISE NOTICE 'No budget or revenue available for movie ID: %', p_movie_id;
END IF;
END;
$$;
CALL calculate_profit_percentage(p_movie_id);
CALL calculate_profit_percentage(101);
Stored procedure parameters
1. IN Parameters (Input Parameters)
Purpose: Used to pass values into the procedure.
Default behavior: If you don't specify the parameter mode, it's treated as IN by default.
Stored procedure parameters
2. OUT Parameters (Output Parameters)
Purpose: Used to pass values out from the procedure back to the calling program.
Behavior: The calling program does not provide a value for an OUT parameter when calling
the procedure. The procedure will assign values to OUT parameters,
and these values are returned as output.

CALL get_movie_title();
Stored procedure parameters
3. INOUT Parameters (Output Parameters)
Purpose: Used to pass values both into and out of the procedure., and the procedure can modify that value
•and return it.
Behavior: You pass a value into the INOUT parameter, and the procedure modifies it and
sends the modified value back to the caller.
Stored procedure
Variables:
v_budget, v_revenue: Store the budget and revenue from the Financial table.
v_profit: Holds the calculated profit (revenue - budget).
v_profit_percentage: Holds the profit percentage.
Logic:
The procedure fetches the budget and revenue for the given p_movie_id.
It calculates the profit and the profit percentage, then outputs it using
RAISE NOTICE:
The RAISE NOTICE statement is used to output messages (similar to print
statements in other languages).

You can also use variables in control structures like IF, LOOP, and FOR. For
example, you could iterate through multiple movies and calculate their
profits in a loop.
CREATE OR REPLACE PROCEDURE calculate_all_movie_profits()
LANGUAGE plpgsql
AS $$
DECLARE
v_movie_id INT;
v_budget NUMERIC;
v_revenue NUMERIC;
v_profit NUMERIC;
BEGIN
FOR v_movie_id IN SELECT movie_id FROM movies
LOOP
SELECT budget, revenue INTO v_budget, v_revenue
FROM Financial
WHERE movie_id = v_movie_id;

IF v_budget IS NOT NULL AND v_revenue IS NOT NULL THEN

v_profit := v_revenue - v_budget;
RAISE NOTICE 'Movie ID: %, Profit: %', v_movie_id, v_profit;

ELSE
RAISE NOTICE 'No financial data for movie ID: %',
v_movie_id;
END IF;
END LOOP;
TRIGGERS
 Trigger is a stored program that invoked automatically in response to an
event such as insert, delete or update that occurs in the table. Suppose,
you defined a trigger, and you insert a row inside the table, then it will
automatically be invoked before or after the insertion of row.
 CREATE TRIGGER trigger_name
{ BEFORE | AFTER | INSTEAD OF } { INSERT | UPDATE | DELETE }
ON table_name [ FOR EACH ROW | FOR EACH STATEMENT ]
EXECUTE FUNCTION function_name();
Trigger
 The following are the key differences between triggers and stored
procedures:
1. Triggers cannot be manually invoked or executed.
2. There is no chance that triggers will receive parameters.
3. A transaction cannot be committed or rolled back inside a trigger.
Types of Trigger: DML
 DML Triggers (Data Manipulation Language): These triggers are executed in
response to changes in data (inserts, updates, deletes) in the table.
 BEFORE INSERT/UPDATE/DELETE: Executes before the data modification event
happens.
 AFTER INSERT/UPDATE/DELETE: Executes after the data modification event has
occurred.
 INSTEAD OF (for views): Executes instead of the triggering DML operation, often
used for views.
 Use Cases:
• Automatically logging changes to sensitive data.
• Enforcing complex constraints.
• Maintaining summary tables or updating dependent tables.
 DML Trigger
 Suppose we want to automatically log every time a sensor’s value
changes in the Sensor_history table.

Explanation:
•This is a DML AFTER UPDATE trigger.
•It logs every change to a sensor value in the sensor_change_log table.
 Types of Trigger: DDL
 DDL Triggers: These triggers are executed in response to changes in
the schema, such as creating, altering, or dropping tables or other
database objects.
 Use Cases:
 Enforcing auditing when schema changes are made.
 Preventing or logging changes to specific database objects.
 Some databases like Oracle and MySQL support DDL triggers, but
PostgreSQL does not natively support DDL triggers. However, you
can monitor DDL changes using event triggers in PostgreSQL.

• This event trigger logs any DDL command executed

in the database.
• It logs schema changes such as creating, altering,
or dropping tables.
Example of trigger
Trigger in action