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
Database creation and
Updates
DDL statements
Section 1
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);
 Drop constraint
 ALTER TABLE employees ALTER COLUMN email DROP unique_email ;
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
 SELECT DISTINCT studio FROM movies; Universal Pictures
Paramount Pictures
SELECT DISTINCT studio FROM movies WHERE Liberty Films

studio IS NOT NULL; 20th Century Fox

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
FROM table_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 language_id)

SQL AND/OR Clause 2) Financial(movie_id budget revenue unit

currency)
3) Actors(actor_id name birth_year)
4) Movie_actor(movie_id actor_id)
5) Language(language_id name)
 To merge more than one conditions
 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_i release_ye imdb_ratin

title industry studio language_id
d ar g
The
Hollywoo
111 Shawshank 1994 9.3 Castle Rock Entertainment 5
d
Redemption
The Hollywoo
120 1972 9.2 Paramount Pictures 5
Godfather d
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 Dark

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

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

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 titlemovies WHERE
SELECT * FROM industry
title LIKErelease_year
'%America%';imdb_rating studio language_id

Captain
137 America: The Hollywood 2011 6.9 Marvel Studios 5
First Avenger
Captain
138 America: The Hollywood 2014 7.8 Marvel Studios 5
Winter Soldier
COUNT  COUNT: To calculate total rows in the column
 Syntax:
Clause SELECT COUNT(column_name) FROMtable_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 and  Syntax:

IS NOT NULL SELECT column1, column2. columnN

FROM table_name
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
FROM table_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)
5) Language(language_id name)
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)
5) Language(language_id name)
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)
5) Language(language_id name)
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)
5) Language(language_id name)
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
GROUP BY [Link];
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 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)
 SQL query
 Create view which has % profit of each movie.

•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;
END;
$$;
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