Development course SQL databases in node

1

CONFIDENTIAL

Agenda of the lecture

• Important Aspects of Relational Databases;
• Create Relationships

1

CONFIDENTIAL

DATABASES

SQL PL/pgSQL

SQL

SQL PL/SQL

SQL TSQL

1

CONFIDENTIAL

Important Aspects of Relational Databases

1

CONFIDENTIAL

Create Relationships among Tables

One-to-One (1:1)

One-to-Many (1:N)

Many-to-Many (N:М)

PK

PK

PK

PK

PK

FK

FK

FK

FK

PK - Primary Key

FK -

1

SQL Commands

CONFIDENTIAL

1

DDL- Data Definition Language

CONFIDENTIAL

Data Definition Language is used to define the

1

CONFIDENTIAL

DML-Data Manipulation Language

DML statements are used for managing data within schema

1

DDL AND SIMPLE DML EXAMPLES

CONFIDENTIAL

1

CONFIDENTIAL

1

2

3

4

1

CONFIDENTIAL

5

6

7

8

9

10

1

CONFIDENTIAL

13

11

12

14

15

1

DML QUERY EXAMPLES

CONFIDENTIAL

1

CONFIDENTIAL

1

2

3

4

1

CONFIDENTIAL

5

6

7

8

9

10

1

CONFIDENTIAL

11

12

13

14

1

DML QUERY WITH INDEX EXAMPLES

CONFIDENTIAL

1

2

3

1

CONFIDENTIAL

14

4

5

6

7

8

1

CONFIDENTIAL

SQL Joins

1

DML QUERY JOINS EXAMPLES

CONFIDENTIAL

1

CONFIDENTIAL

1

2

3

4

5

6

1

CONFIDENTIAL

8

7

9

1

CONFIDENTIAL

10

11

1

CONFIDENTIAL

DCL- Data Control Language

A Data Control Language is a syntax similar

1

CONFIDENTIAL

TCL- Transaction Control Language

Transaction Control Language commands are used to manage

1

CONFIDENTIAL

Transaction isolation levels vs read phenomena

1

CONFIDENTIAL

Lost Update

Dirty Read

Non-Repeatable-Read

Phantom Reads

1

CONFIDENTIAL

Serialization Anomalies

XA - Transaction

1

TCL DEMO EXAMPLES

CONFIDENTIAL

1

1

CONFIDENTIAL

2

3

4

5

1

CONFIDENTIAL

Native node - postgresql driver (Connection)

Link Nodejs driver api: https://node-postgres.com
Free Database(
20MB

1

CONFIDENTIAL

Native node - postgresql driver (Queries)