SQL

SQL Syntax

SQL uses identifiers(such as table name), keywords(such as SELECT), a literal(a constant such as a string or number) and special character symbols(semicolon ; for one).

Queries in SQL

Syntax is: 

SELECT col1, col2 FROM tablename, othertablename,...
JOIN (table3) ON ---
    WHERE queries
    GROUP BY coln
    HAVING condition
    ORDER BY coln ASC/DSC
    LIMIT limits OFFSET offset;

  • DISTINCT removes duplicates from the columns. 

    SELECT DISTINCT colname ---remaining query

  • Multiple column Order is possible simply by using , after each coln asc/dsc

  • AS expression can be written and new column can be created 

    SELECT col1,(col2*some operation) AS new_name --"new name" can also be used

  • JOIN We can join multiple tables in a database explicitly stating primary keys. 

    SELECT col1, col2 FROM tablename
INNER JOIN anothertablename
    ON tablename.pk = anothertablename.its_pk

    We can nest joins to include multiple databases. 

    INNER JOIN (anothertablename JOIN table3 ON (anothertablename.pk = table3.pk)) ON (tablename.pk = anothertablename.its_pk)

  • Aggregate Functions are used to get a single result from a range of data. PostgreSQL Aggregate Functions here. 

    SELECT func(column/expr) as name
     from tablename
GROUP BY some_column
HAVING group_condition;

    Group BY can be used to get result of group of data. HAVING is used with GROUP BY, it is identical with WHERE.

  • WHERE with or requires to give something to compare on both sides. For example where title like '%flyby%' or '%fly by%' will result in an error and correct way to write it is where title like '%flyby%' or title like '%fly by%'

    • Regex can be used in WHERE clause. For example where title ~* 'fly.*by' wil

Sargeable Queries in SQL

Sargeable queries are queries that can be broken down into smaller queries. For example 

SELECT * FROM table1
WHERE col1 = 'some value'
AND col2 = 'some other value';

can be broken down into 

SELECT * FROM table1
WHERE col1 = 'some value';
SELECT * FROM table1
WHERE col2 = 'some other value';

Data manipulation in SQL

Creating and destroying table is SQL

To create a table we use

CREATE TABLE tablename(
    col1 datatype,
    col2 datatype,
);

  • NOT NULL is used to make a column not null

  • UNIQUE is used to make a column unique

  • PRIMARY KEY is used to make a column primary key

Special datatypes are:

  • SERIAL is a unique number for each row. Can be used as primary key.

Droping tables

  • DROP TABLE is used to delete a table.

  • DROP TABLE IF EXISTS is used to delete a table if it exists.

Copying from file

  • COPY is used to copy data from a file to a table.

  • COPY tablename FROM 'file_path' DELIMITER ',' CSV HEADER;

Inserting data in SQL

INSERT INTO

Basic syntax of INSERT INTO is 

INSERT INTO tablename
(col1, col2,...coln)
VALUES (value1, value2...valuen)
       (valu1, valu2,...valun)

Column names are optional and need not to all be in order or full list of column available to a table. This can also be used to insert multiple rows from another table. For example: 

INSERT INTO tablename(
    col1, col2, col3

SELECT col1, col2, col3 FROM othertablename
WHERE condition

Type casting in SQL

  • value::type is used to cast a value to a type.

Constraints in SQL

  • To add a constraint to a table we use 

    ALTER TABLE tablename
ADD CONSTRAINT constraint_name
constraint_type (col1, col2,...coln);

Types of constraints

  • PRIMARY KEY is used to make a column primary key

  • FOREIGN KEY is used to make a column foreign key

  • UNIQUE is used to make a column unique

  • CHECK is used to make a column check

  • NOT NULL is used to make a column not null

PostgreSQL

Resources: + Postgresql playground

psql

Login

  • General login psql -U username -p port -h hostname

  • To login with ubuntu sudo -u <username> psql. Default is generally 'postgres'.

Modify database

  • To create a database CREATE DATABASE databasename;

  • To delete a database DROP DATABASE databasename;

Special commands

  • To list all databases \l

  • To list all tables \dt

  • To list all columns of a table \d tablename

  • To run commands in a shell \! command

  • To run a .sql file \i filename.sql

  • \copy is used to copy data from a file to a table. 

    \copy tablename FROM 'file_path' DELIMITER ',' CSV HEADER;

It even works on client side.

  • \x is used to toggle between vertical and horizontal display of data.

Schema in PostgreSQL

  • On the top there are clusters, which are a collection of databases.

  • Clusters have databases.

  • Databases have schema and schema have tables, views, functions, etc.

    • Schema is a collection of database objects.

    • By default there is a schema called public.

createdb and dropdb

  • sudo -u <username> createdb <dbname>

Creates a database with dbname name.

  • sudo -u <username> dropdb <dbname>

Deletes database with dbname name.

\dn

To show all schemas

\dt <schema_name>.*

To show all relations of schema

Relations

Sequence

Sequence is a special kind of relation that generates unique integer values

  • It is stored in pg_catalog.pg_sequence relation.

  • It is named as <table_name>_<column_name>_seq.

  • CREATE SEQUENCE seq_name is used to create a sequence.

  • nextval() is used to get the next value of a sequence.

SQLite

Backlinks