Nazarbayev University

SEDS CS
CSCI 341, Fall 2024
Instructor: Yesdaulet Izenov

Lecture
Structured Query Language (SQL)
Data Definition Language (DDL)
Constraints
 Relational Data Model
Data Model properties: structure, constraints and operations
 Structured data - CSV :: ~25k out of 50k datasets
 Semistructured data - XML/JSON :: ~3k out of 50k datasets
 Unstructured data - images, video, audio, etc.

Schema of a relation
 Rows (tuple, record)
 Columns (attribute, feature, field)
 Structured Query Language (aka. SQL)
Declarative language (abstraction)
No Direct Access to Database Storage
Only via SQL language to interact with (R)DBMS to consequently access databases

Relational Data Model Operations

Sublanguages:
 Data Definition Language (DDL) – CREATE, DROP, ALTER
 Data Manipulation Language (DML) – INSERT, UPDATE, DELETE
 Data Query Language (DQL) – SELECT
Input = Table(s) and Output = Table

Arithmetic Algebra: 527 * 14 + 3 / ((4 + 2) – 4) - 3 + 2

 CREATE DATABASE
CREATE DATABASE db_name;  Creates an empty database
 Sets default database parameters
 CREATE TABLE
CREATE TABLE table_name (  Creates only the table schema (structure, header)

attribute_name1 DATA_TYPE,  No tuple/rows

attribute_name2 DATA_TYPE  Creates attribute constraints on attributes

)
 Column Constraints (Data Types)
Primitive data types only (no containers)
 INTEGER
 DECIMAL (3, 2) – total digits, decimal digits. Ex: -9.99 to +9.99
 CHAR (10) – memory allocation size (fixed size): 10 bytes
 VARCHAR (10) – memory allocation size (variable size): 1,2,…10 bytes
 DATE
 etc.
 DROP TABLE / DATABASE
DROP DATABASE db_name  Table/Relation is deleted from the database
 Table schema/structure is deleted from the database

DROP TABLE table_name  All tuples/rows are deleted from the database

TRUNCATE TABLE table_name

 ALTER TABLE
ALTER TABLE table_name RENAME TO new_table_name
ALTER TABLE table_name ADD COLUMN column_name DATA_TYPE
ALTER TABLE table_name RENAME COLUMN column_name TO new_column_name
ALTER TABLE new_city ALTER COLUMN other_column TYPE DATA_TYPE
ALTER TABLE table_name DROP COLUMN column_name

Modify the schema of an existing table/relation

 ALTER DATABASE

ALTER DATABASE db_name RENAME TO new_db_name

ALTER DATABASE db_name OWNER TO new_owner
ALTER DATABASE db_name SET parameter_name TO value

CREATE SCHEMA schema_name ← a namespace in a database

similar to namespaces in C/C++
 Relational Data Model (Constraints)
Constraint types
1. Column(s) and a single row in a single table:

Data types (INTEGER, DECIMAL, CHAR, VARCHAR, etc.).

Domain constraints (NULL, CHECK, DEFAULT)
set or range of allowed values

No containers are allowed (arrays, lists, vectors, etc.)
2. Column(s) and all rows in a single table:

Primary Key, UNIQUE
3. Column(s) and all rows in multiple tables:

Foreign Keys
4. Complex constrains on multiple rows and tables:

Assertions and Triggers
 Column Constraints (NULL)
No NULL values on a given attribute

CREATE TABLE table_name (

attribute_name1 DATA_TYPE,
attribute_name2 DATA_TYPE NOT NULL
)
 Column Constraints (DEFAULT)
The value that is used when the attribute value is not specified

CREATE TABLE table_name (

attribute_name1 DATA_TYPE,
attribute_name2 DATA_TYPE DEFAULT (‘Unknown’)
)
 Column Constraints (CHECK)
 Specific condition(s) on a single attribute
 Any condition(s) from WHERE clause can be applied

CREATE TABLE table_name (

attribute_name1 DATA_TYPE,
attribute_name2 DATA_TYPE CHECK (attribute_name2 OPERATOR condition)
)
 Column/Row Constraints (CHECK)
 Specific condition(s) on multiple attributes
 Any condition(s) from WHERE clause can be applied

CREATE TABLE table_name (

attribute_name1 DATA_TYPE,
attribute_name2 DATA_TYPE,
CHECK (attribute_name1 OPERATOR condition
AND attribute_name2 OPERATOR condition)
)
 Column Constraints (UNIQUE)
 Can be applied on a single or multiple attributes
 Distinct/different attribute values across all the tuples/rows in a given table

no two tuples with the same value on the UNIQUE attribute
 Multiple NULL values on the UNIQUE attribute
 UNIQUE attribute builds an index – useful for query optimization

CREATE TABLE table_name (

attribute_name1 DATA_TYPE,
attribute_name2 DATA_TYPE UNIQUE
)
 Column/Row Constraints (UNIQUE)
Separately applied on multiple attributes
CREATE TABLE table_name (
attribute_name1 DATA_TYPE UNIQUE,
attribute_name2 DATA_TYPE UNIQUE
)
Jointly applied on multiple attributes
CREATE TABLE table_name (
attribute_name1 DATA_TYPE,
attribute_name2 DATA_TYPE,
UNIQUE (attribute_name1, attribute_name2)
)
 Nazarbayev University
SEDS CS
CSCI 341, Fall 2024
Instructor: Yesdaulet Izenov

Lecture
Key Constraints
Data Manipulation Language
 Column/Row Constraints (PRIMARY KEY)
 Applied on a single attribute
 Applied on multiple attributes (composite key)
 Distinct/different attribute values across all the tuples/rows in a table

no two tuples with the same Primary Key value
 No NULL values on the Primary Key
 Primary Key attribute builds an index – useful for query optimization
 There can be only one Primary Key per table
 Column/Row Constraints (PRIMARY KEY)
CREATE TABLE table_name (
attribute_name1 DATA_TYPE PRIMARY KEY,
attribute_name2 DATA_TYPE
)

CREATE TABLE table_name (

attribute_name1 DATA_TYPE,
attribute_name2 DATA_TYPE,
PRIMARY KEY (attribute_name1, attribute_name2)
)
 Foreign Keys
 Applied on a single attribute
 The values MUST be from the referred Primary Key (or UNIQUE) values
 There can be duplicate attribute values
 Multiple NULL values on the Foreign Key

 Applied on multiple attributes (composite key)

 Multiple Foreign Keys per table

 Recommended to build indexes on Foreign Keys

 Foreign Keys
CREATE TABLE table_name1 (
attribute_name1 DATA_TYPE REFERENCES table_name2 (attribute_name),
attribute_name2 DATA_TYPE
)

CREATE TABLE table_name1 (

attribute_name1 DATA_TYPE REFERENCES table_name2 (attribute_name1),
attribute_name2 DATA_TYPE REFERENCES table_name3 (attribute_name2)
)

CREATE TABLE table_name1 (

attribute_name1 DATA_TYPE,
attribute_name2 DATA_TYPE,
FOREIGN KEY (attribute_name1, attribute_name2) REFERENCES table_name2 (attribute_name1, attribute_name2)
)
 Foreign Keys
The values must be from the referred Primary Key (or UNIQUE) values

Cross-table constraint type (different tables)

Referential Integrity Constraint
 Referential Integrity Constraints (INSERT)
 New Foreign Key values must exist in the Primary Key (or UNIQUE) attribute
 The ORDER of INSERT statements are important
 Circular Referencing:

NOT DEFERRABLE (by default)

DEFERRABLE

INITIALLY DEFERRED

INITIALLY IMMEDIATE
 Referential Integrity Constraints (UPDATE)

 Handling UPDATE statements:


Reject

Set NULL

Set DEFAULT

Cascade
 Referential Integrity Constraints (UPDATE)
CREATE TABLE table_name1 (
attribute_name1 DATA_TYPE
REFERENCES table_name2 (attribute_name) ON UPDATE SET NULL,
attribute_name2 DATA_TYPE
)

CREATE TABLE table_name1 (

attribute_name1 DATA_TYPE DEFAULT value
REFERENCES table_name2 (attribute_name) ON UPDATE SET DEFAULT,
attribute_name2 DATA_TYPE
)

CREATE TABLE table_name1 (

attribute_name1 DATA_TYPE,
attribute_name2 DATA_TYPE,
FOREIGN KEY (attribute_name1)
REFERENCES table_name2 (attribute_name) ON UPDATE CASCADE
)
 Referential Integrity Constraints (DELETE)

 Handling DELETE statements:


Reject

Set NULL

Set DEFAULT

Cascade

rows in FK attribute can be deleted

rows in PK attribute cannot be deleted if the rows are referred.
 Referential Integrity Constraints (DELETE)
CREATE TABLE table_name1 (
attribute_name1 DATA_TYPE,
attribute_name2 DATA_TYPE
REFERENCES table_name2 (attribute_name) ON DELETE CASCADE
)

CREATE TABLE table_name1 (

attribute_name1 DATA_TYPE,
attribute_name2 DATA_TYPE
REFERENCES table_name2 (attribute_name)
ON DELETE CASCADE ON UPDATE SET NULL
)
 Constraints
CREATE TABLE table_name1 (
attribute_name1 DATA_TYPE PRIMARY KEY,
attribute_name2 DATA_TYPE CHECK (attribute_name2 OPERATOR condition),
attribute_name3 DATA_TYPE NOT NULL,
attribute_name4 DATA_TYPE DEFAULT (‘Unknown’),
attribute_name5 DATA_TYPE UNIQUE,
attribute_name6 DATA_TYPE REFERENCES table_name2 (attribute_name)
)
 Constraints Naming
CREATE TABLE table_name1 (

attribute_name1 DATA_TYPE,

attribute_name2 DATA_TYPE,

attribute_name3 DATA_TYPE,

attribute_name4 DATA_TYPE DEFAULT (‘Unknown’),

attribute_name5 DATA_TYPE,

attribute_name6 DATA_TYPE,

CONSTRAINT my_pk PRIMARY KEY (attribute_name1),

CONSTRAINT my_check CHECK (attribute_name2 OPERATOR condition),

CONSTRAINT my_null CHECK (attribute_name3 IS NOT NULL),

CONSTRAINT my_unique UNIQUE (attribute_name5),

CONSTRAINT my_fk FOREIGN KEY (attribute_name6) REFERENCES table_name2 (attribute_name)

)
 Assertions
CREATE ASSERTION assertion_name
CHECK (
boolean condition
);
 Data Conditions To /From Database Constraints
Data Model properties:
1. Structure
2. Constraints
3. Operations

a) Existing data TO a new database:

Integrate the data conditions to the database constraints.
b) Existing/new database TO generate/export a new data:
The data is structured, clean, and consistent.
 Data Conditions To /From Database Constraints

1. Constraints are created via CREATE statements.

2. Constraints are changed via ALTER statements.
3. Constraints are applied on each DML query (UPDATE, DELETE, INSERT).
4. Constraints are automatically applied in DBMS.
 Data Conditions To /From Database Constraints
Constraint types
1. Column(s) and a single row in a single table:

Data types (INTEGER, DECIMAL, CHAR, VARCHAR, etc.).

Domain constraints (NULL, CHECK, DEFAULT)
set or range of allowed values

No containers are allowed (arrays, lists, vectors, etc.)
2. Column(s) and all rows in a single table:

Primary Key, UNIQUE
3. Column(s) and all rows in multiple tables:

Foreign Keys
4. Complex constrains on multiple rows and tables:

Assertions and Triggers
Constraints vs Assertions
 Structured Query Language (aka. SQL)
Declarative language (abstraction)
No Direct Access to Database Storage
Only via SQL language to interact with (R)DBMS to consequently access databases

Relation Data Model Operations

Sublanguages:
 Data Definition Language (DDL) – CREATE, DROP, ALTER
 Data Manipulation Language (DML) – INSERT, UPDATE, DELETE
 Data Query Language (DQL) – SELECT
 INSERT new tuples
INSERT INTO table_name (attribute_name1, attribute_name2) VALUES (value1, value2);

INSERT INTO table_name VALUES (value1, value2);

INSERT INTO table_name (attribute_name1) VALUES (value1);

INSERT INTO table_name (attribute_name1, attribute_name2) VALUES (value1, value2), (value3, value4);

INSERT INTO table_name1 (attribute_name1, attribute_name2)

SELECT attribute_name1, attribute_name2

FROM table_name2;

COPY table_name FROM ’path_to_file/data.csv’ DELIMETER ',' NULL '';

 UPDATE existing tuples
UPDATE table_name
SET attribute_name1 = value1;

UPDATE table_name
SET attribute_name1 = value1, attribute_name2 = value2;

UPDATE table_name
SET attribute_name1 = value1, attribute_name2 = value2
WHERE attribute_name1 > value3;

UPDATE table_name1
SET attribute_name1 = value1, attribute_name2 = value2
WHERE attribute_name1 IN (SELECT attribute_name2 FROM table_name2);
 DELETE existing tuples

DELETE FROM table_name;

TRUNCATE TABLE table_name; ← (faster, no WHERE clause)

DELETE FROM table_name WHERE attribute_name > 1;

DELETE FROM table_name1

WHERE attribute_name1 IN (SELECT attribute_name2 FROM table_name2);
 Nazarbayev University
SEDS CS
CSCI 341, Fall 2024
Instructor: Yesdaulet Izenov

Lecture
Data Query Language (DQL)
 Relational Data Model (Schema)
Data Model properties: structure, constraints, and operations

Schema of a relation
 Rows (tuple, record)
 Columns (attribute, feature, field)
 Structured Query Language (aka. SQL)
Declarative language (abstraction)
No Direct Access to Database Storage
Only via SQL language to interact with (R)DBMS to consequently access databases

Relation Data Model Operations

Sublanguages:
 Data Definition Language (DDL) – CREATE, DROP, ALTER
 Data Manipulation Language (DML) – INSERT, UPDATE, DELETE
 Data Query Language (DQL) – SELECT … FROM ...
 SQL Query Skeleton
SELECT result_table_schema ← (output = table)
FROM input_tables
[ WHERE selection_predicates AND join_predicates ]
[ GROUP BY grouping_attributes ]
[ HAVING group_selection_predicates ]
[ ORDER BY sorting_attributes ]
[ LIMIT number ]
 Data Query Language (aka. DQL)
Operations on a SINGLE table:
1. Input (FROM) – input table
2. Projection (SELECT) – cut table horizontally
 The result is the output of the question or input to another SQL query

3. Selection (WHERE clause) – cut table vertically

 Predicate/condition types: AND, OR, NOT, IS, NULL, LIKE, BETWEEN, IN, ANY, ALL, EXISTS, etc.

4. Grouping (GROUP BY clause) – vertically grouping

 Selection within each group (HAVING clause) – cut each group vertically

5. Aggregate Functions (COUNT, SUM, MAX, MIN, AVG) – aggregating values

 Output is a table with a single column and single row

6. Scalar Functions (CONCAT, LENGTH, CEIL, ROUND, etc.)

7. Others: AS, DISTINCT, SORT, LIMIT/TOP, etc.
 Data Query Language (aka. DQL)
Operations on MULTIPLE tables:
1. Joins

Two-way and Multi-way joins

Inner Join, Outer Join, Cross Join, Self Join
2. Subqueries

Independent and Correlated subqueries
3. Set Operators: UNION, INTERSECT, EXCEPT
 Queries on a Single Relation
SELECT result_table_schema ← (output attributes = table)
FROM input_table; ← (one input table)
Input = Table(s) and Output = Table

Arithmetic Algebra: 527 * 14 + 3 / ((4 + 2) – 4) - 3 + 2

 Example Database

Product (maker, model, type)

PC (model, speed, ram, hd, price)
Laptop (model, speed, ram, hd, screen, price)
Printer (model, color, type, price)
 English question to SQL query
Question example:
SELECT column(s) ← (output attributes = result table)
Show all laptop details? FROM input_table(s);

SELECT model, speed, ram,

hd, screen, price
FROM Laptop;

SELECT *
FROM Laptop;
 Projection (SELECT) on a single Relation
SELECT column(s) ← (output attributes = result table)
Question example:
FROM input_table(s);
How much do laptops cost?

SELECT price
FROM Laptop;
 Alias (AS)
 Query readability
SELECT Product.model, Product.maker, Product.type
 Fast query writing (shorter table and column names)
FROM Product;
 Renaming input tables and output columns
 To eliminate ambiguity in choosing column names
SELECT model, maker, type
 Naming expression and calculation FROM Product;

SELECT model AS mo, maker AS ma, type AS t

FROM Product;

SELECT column(s) AS nick_name

FROM input_table(s) AS nick_name;

 Alias (AS)
SELECT SUM(model + 5) AS my_summation
FROM Product;

SELECT 5 AS my_value
FROM Product;

SELECT maker || ' ' || type AS description

FROM Product;
 Alias (AS)
SELECT *
FROM Product, Laptop
WHERE model = model; ← AMBIGUITY

SELECT *
FROM Product, Laptop
WHERE Product.model = Laptop.model;

SELECT *
FROM Product AS P, Laptop AS L
WHERE P.model = L.model;
 Alias (AS)

SELECT *
FROM Product, Product ← AMBIGUITY
WHERE model != model; ← AMBIGUITY

SELECT *
FROM Product AS pr1, Product AS pr2
WHERE pr1.model != pr2.model;
 Alias (AS)
 Query readability
SELECT Product.model, Product.maker, Product.type
 Fast query writing (shorter table and column names)
FROM Product;
 Renaming input tables and output columns
 To eliminate ambiguity in choosing column names
SELECT model, maker, type
 Naming expression and calculation
FROM Product;
 Referencing in other clauses
 Naming subquery
SELECT model AS mo, maker AS ma, type AS t

FROM Product;

SELECT column(s) AS nick_name

FROM input_table(s) AS nick_name;

 Duplicate Elimination (DISTINCT)
Question example:
Show me all different types of Laptop screen sizes?
SELECT column(s) ← (output attributes = result table)
FROM input_table(s);
SELECT DISTINCT screen
FROM Laptop;

SELECT DISTINCT ON (screen) screen, hd

FROM Laptop;
Aggregation Functions (COUNT, MIN, MAX, SUM, AVG)
SELECT column(s) ← (output attributes = result table)
Question example:
FROM input_table(s);
How many laptops do we have?

SELECT COUNT(*)
FROM Laptop;
or you can write

SELECT COUNT(hd)
FROM Laptop;

How many laptops do we have with

different hard disk sizes?

SELECT COUNT(DISTINCT hd)

FROM Laptop;
 Scalar Functions (CONCAT, LENGTH, ROUND, etc.)

SELECT CONCAT(maker, type)

FROM Product;

SELECT LENGTH(maker)
FROM Product;

SELECT EXTRACT(year FROM date_attribute)

FROM Product;

*many other built-in scalar and aggregate functions in different DBMSs

 Selection (WHERE clause) on a single Relation
Question example: SELECT column(s) ← (output attributes = result table)
What laptops have speed at least 2.00 GHz? FROM input_table(s)

WHERE conditions;
SELECT speed
FROM Laptop
WHERE speed >= 2.00;
 Selection predicates in WHERE clause
Tree-Valued Logic: True, False, NULL (unknown)
SELECT column(s) ← (output attributes = result table)
FROM input_table(s)
CONJUNCTION: AND
WHERE conditions;
DISJUNCTION: OR

NEGATION: NOT

NUMERIC COMPARISONS: =, <, >, >=, <=, <>

STRING COMPARISONS: %, _, =

Numeric attributes: BETWEEN val1 AND val2

String attributes: LIKE

Tuple conditions: IS NULL

Membership checking: IN
 Grouping (GROUP BY) on a single Relation
Question example:
SELECT MIN(price)
What is cheapest price for laptops in each ram size category? FROM Laptop
GROUP BY ram;
 Selection in Grouping (GROUP BY + HAVING)
SELECT ram
Question example:
FROM Laptop
What ram sizes have at least 3 different hard-drive sizes?
GROUP BY ram
HAVING COUNT(DISTINCT hd) >= 3;
 Sorting ( ORDER BY [ASC | DESC] )
Question example:
SELECT *
Show the laptops sorted by their price from expensive to cheapest?
FROM Laptop
ORDER BY price DESC;
 Sorting ( ORDER BY [ASC | DESC] )
Question example:
SELECT *
Show the laptops sorted by their ram size and price from expensive to
cheapest? FROM Laptop
ORDER BY ram, price DESC;
 Select first rows (LIMIT)
Question example: SELECT *
Show first 5 laptop details?
FROM Laptop
LIMIT 5;
 Question Decomposition
Question example:
What ram sizes have at least 3 different hard-drive sizes?

SELECT ram
FROM Laptop
GROUP BY ram
HAVING COUNT(DISTINCT hd) >= 3;
 Nazarbayev University
SEDS CS
CSCI 341, Fall 2024
Instructor: Yesdaulet Izenov

Lecture
Data Query Language (DQL)
Joins
 Data Query Language (aka. DQL)
Operations on a SINGLE table:
1. Input (FROM) – input table
2. Projection (SELECT) – cut table horizontally
 The result is the output of the question or input to another SQL query

3. Selection (WHERE clause) – cut table vertically

 Predicate/condition types: AND, OR, NOT, IS, NULL, LIKE, BETWEEN, IN, ANY, ALL, EXISTS, etc.

4. Grouping (GROUP BY clause) – vertically grouping

 Selection within each group (HAVING clause) – cut each group vertically

5. Aggregate Functions (COUNT, SUM, MAX, MIN, AVG) – aggregating values

 Output is a table with a single column and single row

6. Scalar Functions (CONCAT, LENGTH, CEIL, ROUND, etc.)

7. Others: AS, DISTINCT, SORT, LIMIT/TOP, etc.
 SQL Query Skeleton
SELECT result_table_schema ← (output = table)
FROM input_tables
[ WHERE selection_predicates AND join_predicates ]
[ GROUP BY grouping_attributes ]
[ HAVING group_selection_predicates ]
[ ORDER BY sorting_attributes ]
[ LIMIT number ]
 Selection in Grouping (GROUP BY + HAVING)
SELECT ram
Question example:
FROM Laptop
What ram sizes have at least 3 different hard-drive sizes?
GROUP BY ram
HAVING COUNT(DISTINCT hd) >= 3;
Input = Table(s) and Output = Table

Arithmetic Algebra: 527 * 14 + 3 / ((4 + 2) – 4) - 3 + 2

 Data Query Language (aka. DQL)
Operations on MULTIPLE tables:
1. Joins

Two-way and Multi-way joins

Cross Join, Inner Join, Outer Join, Self Join
2. Subqueries

Independent and Correlated subqueries
3. Set Operators: UNION, INTERSECT, EXCEPT
Join
Why do we need join operators?
Why join operators are important?

Two-way join vs Multi-way join

 Example Database

Product (maker, model, type)

PC (model, speed, ram, hd, price)
Laptop (model, speed, ram, hd, screen, price)
Printer (model, color, type, price)
 Cross Join (Cartesian Product)
Expensive operator (result = |T1| x |T2|)
SELECT column(s) ← (output attributes = result table)
FROM input_table(s)

WHERE join_predicates;
SELECT *
FROM Product, Laptop;
(join not specified)

SELECT *
FROM Product JOIN Laptop;

SELECT *
FROM Product CROSS JOIN Laptop;
Cross Join Question example:

What manufacturers produce laptops?

 Inner Join
SELECT column(s) ← (output attributes = result table)
FROM input_table(s)

WHERE join_predicates;

Types of Inner Join:

1.Equi join
2.Theta join
3.Natural join
 Equi Inner Join
Question example: SELECT column(s) ← (output attributes = result table)
What manufacturers produce laptops?
FROM input_table(s)

WHERE join_predicates;
SELECT *
FROM Product, Laptop
WHERE Product.model = Laptop.model;

SELECT *
FROM Product INNER JOIN Laptop ON Product.model = Laptop.model;

SELECT *
FROM Product JOIN Laptop ON Product.model = Laptop.model;
Equi Inner Join
Question example:

What manufacturers produce laptops?

 Theta Inner Join
SELECT *
SELECT column(s) ← (output attributes = result table)
FROM Product, Laptop
FROM input_table(s)
WHERE Product.model > Laptop.model; WHERE join_predicates;

SELECT *
FROM Product INNER JOIN Laptop ON Product.model > Laptop.model;

SELECT *
FROM Product JOIN Laptop ON Product.model > Laptop.model;
Theta Inner Join
 Natural Inner Join
SELECT column(s) ← (output attributes = result table)
FROM input_table(s);

SELECT *
FROM Product NATURAL JOIN Laptop;

- If there are multiple attributes with same name,

the join will be on all those attributes.
- The columns with the same names will be
merged.
Natural Inner Join
 Outer Join
SELECT column(s) ← (output attributes = result table)
FROM input_table(s)

WHERE join_predicates;

Types of Outer Join:

1.Full join
2.Right join
3.Left join
 Full Outer Join
Inner Join +
SELECT column(s) ← (output attributes = result table)
Unmatched Left +
FROM input_table(s);
Right table results

SELECT *
FROM Product FULL OUTER JOIN Laptop
ON Product.model = Laptop.model;

SELECT *
FROM Product FULL JOIN Laptop ON
Product.model = Laptop.model;
Full Outer Join
 Left Outer Join
SELECT column(s) ← (output attributes = result table)
Inner Join +
FROM input_table(s);
Unmatched Left table results

SELECT *
FROM Product LEFT OUTER JOIN Laptop
ON Product.model = Laptop.model;

SELECT *
FROM Product LEFT JOIN Laptop ON
Product.model = Laptop.model;
Left Outer Join
 Right Outer Join
SELECT column(s) ← (output attributes = result table)
Inner Join +
FROM input_table(s);
Unmatched Right table results

SELECT *
FROM Product RIGHT OUTER JOIN
Laptop ON Product.model = Laptop.model;

SELECT *
FROM Product RIGHT JOIN Laptop ON
Product.model = Laptop.model;
Right Outer Join
 Self Join
SELECT column(s) ← (output attributes = result table)
FROM input_table(s)

WHERE join_predicates;
SELECT *
FROM Product AS pr1, Product AS pr2
WHERE pr1.model < pr2.model;
Self Join