SQL Execution Order
-
if there is a complicated query having SELECT, FROM, JOINS, WHERE and other SQL commands parameters
then there is order in which each keyword is executed defined by ANSI SQL
Login to DB
| Description | Command | ||
|---|---|---|---|
| Login to database as user=postgres |
|
Create
Create DB & Connect to it
postgres=# create database teachers;
CREATE DATABASE
postgres=# \c teachers; //Connect to DB
You are now connected to database "teachers" as user "postgres".
teachers-#
Create table & insert data
********** Create Table **********
teachers=# CREATE TABLE teachers_login (
id serial PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE,
password_hash TEXT NOT NULL
);
CREATE TABLE
********** List Table **********
teachers=# \dt
********** Insert into Table **********
teachers=# INSERT INTO teachers_login (email, password_hash)
VALUES ('teacher1@test.com', 12117530842303537118);
INSERT 0 1
********** Show contents of Table **********
teachers=# SELECT * FROM teachers_login;
id | email | password_hash
----+-------------------+----------------------
1 | teacher1@test.com | 12117530842303537118
(1 row)
Table Index
-
index can be created before or after table creation.
Meaning of Index
Advantages of Indexing
Types of Indexing (BTree, Hash, Rtree)
// We have Tenants Table
-- Create tenants table
CREATE TABLE tenants (
id SERIAL PRIMARY KEY,
tenant_name VARCHAR(100) UNIQUE NOT NULL
);
// Create btree index on table
CREATE UNIQUE INDEX idx_tenant_name ON tenants(tenant_name);
Read
List Database, List Tables, Show Table schema
********** Lists all available commands **********
postgres=# \h
********** Lists Databases **********
postgres=# \l //It has 3 predefined builtin databases
List of databases
Name | Owner | Encoding | Locale Provider | Collate | Ctype | ICU Locale | ICU Rules | Access privileges
-----------+----------+----------+-----------------+---------+---------+------------+-----------+-----------------------
postgres | postgres | UTF8 | libc | C.UTF-8 | C.UTF-8 | | |
teachers | postgres | UTF8 | libc | C.UTF-8 | C.UTF-8 | | | =Tc/postgres +
| | | | | | | | postgres=CTc/postgres
template0 | postgres | UTF8 | libc | C.UTF-8 | C.UTF-8 | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | libc | C.UTF-8 | C.UTF-8 | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
teachers=# \dt
List of relations
Schema | Name | Type | Owner
--------+----------------+-------+----------
public | teachers_login | table | postgres
(1 row)
********** Table Schema **********
teachers=# \d teachers_login
Table "public.teachers_login"
Column | Type | Collation | Nullable | Default
---------------+------------------------+-----------+----------+--------------------------------------------
id | integer | | not null | nextval('teachers_login_id_seq'::regclass)
email | character varying(255) | | not null |
password_hash | text | | not null |
password_salt | text | | not null |
active | boolean | | not null | false
Update
Add Col
// Adding tenant_id to leads Table.
// tenant_id is FOREIGN key in tenants table
ALTER TABLE leads
ADD COLUMN tenant_id INTEGER NOT NULL,
ADD CONSTRAINT fk_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id);
ALTER TABLE
Change Col name
ALTER TABLE table_name RENAME COLUMN old_name to new_name;
crm=# ALTER TABLE tenants RENAME COLUMN tenant_id to tenant_name;
ALTER TABLE
Remove col from table
crm=# select * from employees;
id | email | passwd_hash | tenant_id | is_admin | created_at
----+-------+-------------+-----------+----------+------------
(0 rows)
crm=# alter table employees drop passwd_hash;
ALTER TABLE
crm=# select * from employees;
id | email | tenant_id | is_admin | created_at
----+-------+-----------+----------+------------
(0 rows)
Update entry in col in table
crm=# select * from employees;
id | email | passwd_hash | tenant_id | is_admin | created_at
----+-------+-------------+-----------+----------+------------
(0 rows)
crm=# update employees set is_admin=t where email='test@gmail.com'
Delete
********** Delete database teachers **********
postgres=# DROP DATABASE teachers;
DROP DATABASE
********** Delete table users **********
postgres=# DROP table users;
DROP TABLE
JOIN 2 tables
-
1. Create 2 tables (Person, PersonFriend)
2. Find friends of Bob from PersonFriend table.
https://onecompiler.com/mysql/42szxs8p5
create table Person(ID int primary key, Person text);
insert into Person values(1, 'alice');
insert into Person values(2, 'Bob');
insert into Person values(99, 'zach');
select * from Person;
create table PersonFriend(PersonID int, FriendID int);
insert into PersonFriend values(1, 2);
insert into PersonFriend values(2, 1);
insert into PersonFriend values(2, 99);
insert into PersonFriend values(99, 1);
select * from PersonFriend;
#1 Person table is aliased as p1. Select Person from Person table
#2 Person table is aliased as p1.
#3 INNER JOIN
# Person Table(ID) & PersonFriend.FriendID
#4 INNER JOIN
# PersonTable=p2.
#5 Filter the records where Person=Bob
SELECT p1.Person #1
FROM Person p1 #2
JOIN PersonFriend ON PersonFriend.FriendID = p1.ID #3
JOIN Person p2 ON PersonFriend.PersonID = p2.ID #4
WHERE p2.Person = 'Bob' #6
Output:
+----+--------+
| ID | Person |
+----+--------+
| 1 | alice |
| 2 | Bob |
| 99 | zach |
+----+--------+
+----------+----------+
| PersonID | FriendID |
+----------+----------+
| 1 | 2 |
| 2 | 1 |
| 2 | 99 |
| 99 | 1 |
+----------+----------+
+--------+
| Person |
+--------+
| alice |
| zach |
+--------+