SQL : Basics

Basics

  • relational databases are made up of tables
  • tables are made of rows and columns
  • Each table in relational databases also have primary key, primary key can be used to uniquely identify each and every row in a database table
  • we can always have only one primary key column per table , you can't have multiple columns as primary key in a SQL table
  • In SQL columns are also called attributes and rows records
  • In database we usually have multiple tables and related with each other through relationships known as foreign keys
  • Tables may have additional constraints as well , like some column or collection of columns are unique (Example you have a user table and there is a email column , it will be a good idea to make this column unique since having multiple rows in database table with same email will be a data design problem going forward)
  • Indexing: it is an important feature in database tables, it basically using hash maps under the hood, so when you query data instead of scanning whole table , indexes allow accessing the data by focusing searhc on a group of data hence it make querying faster, indexing is usually done on columns which are more often used for filtering the data
  • Tables in relationnal databases may have
  • One to one mapping - One row in a table is mapped to another row in other table
  • One to many mapping - One row in a table is mapped to multiple rows in another table
  • Many to many mapping - Many rows in a table is mapped to multiple rows in another table

Data types in SQL

Just like many other programming language, it will be a good practice to define data types of our columns in a SQL table
Declaring data type help in more efficient operations and storage strategy for your data and hence querying/creating data becomes more optimized

Common datatypes

Numerical

  • Integer : smallint, bigint etc.
  • Numeric: decimal
  • real: double precision
  • serial: small/big serial, autoincrement

Text

  • char(n)
  • varchar(n)

datetime

  • timestamp
  • date
  • time

others

  • boolean

Postgres specific datatypes

  • json
  • array
  • uuid
    etc.

What does database schema mean?

Think of schema as collective information about all the tables , their structures, constraints, relationships etc.

How to run a postgres instance as a docker container?

docker run --name my_pg_inst -p 5432:5432 -e POSTGRES_PASSWORD=admin123 -e POSTGRES_USER=admin_test_db -d postgres:15.4

This command will create custom user password port mapping and a default database same as username assigned above

Confirm your db instance has come up properly docker container ls -a
Output: 

CONTAINER ID   IMAGE           COMMAND                  CREATED          STATUS                   PORTS                                       NAMES
b956e23ad3af   postgres:15.4   "docker-entrypoint.s…"   23 seconds ago   Up 21 seconds            0.0.0.0:5432->5432/tcp, :::5432->5432/tcp   my_pg_inst

If you are running postgres as a docker container how you can access it using command line ?

To access a PostgreSQL database running in a Docker container from the command line, follow these steps:

  1. Identify the container name or ID: List your running Docker containers to find the one running PostgreSQL: bash docker ps

  2. Run psql inside the container: If your container has psql installed, you can directly execute the command inside the container: bash docker exec -it <container_name_or_id> psql -U <username> -d <database>

Replace <container_name_or_id> with the actual container name or ID, <username> with your PostgreSQL username (commonly postgres), and <database> with the database name.

  1. Access via host machine: If you want to access PostgreSQL from your host machine and the container exposes the PostgreSQL port (5432 by default), use the following: bash psql -h localhost -p <port> -U <username> -d <database> Ensure the port is correctly mapped in your Docker container (docker run -p 5432:5432).

If needed, you can check the mapped ports using docker ps.

How to list all databases?

To list all databases in PostgreSQL, you can run the following command after accessing the PostgreSQL instance via psql:

  1. Access the PostgreSQL instance: After connecting to the database as described earlier, enter the interactive psql prompt.

  2. List all databases: Once you're inside the psql prompt, run: sql \l This will display all available databases.

Alternatively, you can use the following SQL query to list the databases:

SELECT datname FROM pg_database;

how to list all tables?

To list all tables in a specific PostgreSQL database, follow these steps:

  1. Connect to the database: Access the desired database using psql: bash psql -U <username> -d <database>

  2. List all tables: Once inside the psql prompt, you can list all tables using: sql \dt

This will show a list of tables in the current database, along with the schema, name, type, and owner.

Alternatively, you can use an SQL query to get a list of tables:

SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';

This query lists all tables in the public schema (you can change the schema name if needed).

how to quit psql terminal/ exit loggedin session?

exit the current session:

\q

Creating and removing the database

How to create a new database?

create database lms;

Don't forget to end statement with semicolon

How to remove database?

DROP DATABASE lms;

Create Schemas

CREATE SCHEMA lms_schema;

if due to any reason you want to drop some schema, use following command

DROP SCHEMA lms_schema;

Create table

CREATE TABLE course (
    id SERIAL PRIMARY KEY,
    title varchar(100),
    description TEXT
);

to drop table use command 

DROP TABLE course;

Once you have tables created inside your schema , simply doing a DROP SCHEMA schema_name won't work, you have to drop tables as well first then only you can drop schema or you can use CASCADE command

DROP SCHEMA lms_schema CASCADE

How to create foreign key relationships?

Let say you have following tables:

CREATE TABLE course (
    id SERIAL PRIMARY KEY,
    title varchar(100),
    description TEXT
);

CREATE TABLE student (
    id SERIAL PRIMARY KEY,
    name varchar(100),
    email varchar(100),
    age INT
);

Now to create a many to many foreign relationship between course and student , we can do it like this

CREATE TABLE student_course_map (
    id SERIAL PRIMARY KEY ,
    course_id INT,
    student_id INT,
    FOREIGN KEY (course_id) REFERENCES course (id),
    FOREIGN KEY (student_id) REFERENCES student (id)
);

Alter table and add a column

ALTER TABLE course
ADD COLUMN duration INT;

Alter table and drop a column

ALTER TABLE course
DROP COLUMN duration;

Alter data type in a given column

ALTER TABLE course
ALTER COLUMN duration SET DATA TYPE VARCHAR(50);

How to change the column name

ALTER TABLE course
RENAME COLUMN duration TO course_duration;

How to create enumerated data type ?

CREATE TYPE course_level AS ENUM ('beginner', 'intermediate', 'advanced');

ALTER TABLE course
ADD COLUMN level course_level;

Inserting data into tables

Single row insert

INSERT INTO course (title, description, course_duration, level)
VALUES ('Python Programming', 'An introductory course on Python programming', 30, 'beginner');

Multi row insert

INSERT INTO course (title, description, course_duration, level)
VALUES 
    ('Data Science with Python', 'A course on data science fundamentals using Python', 40, 'intermediate'),
    ('Advanced Machine Learning', 'An advanced course on machine learning techniques', 50, 'advanced'),
    ('Database Systems', 'A comprehensive guide to database systems and SQL', 25, 'beginner');

We are going to use a open database available on github in further tutorial DB LINK

Restore above database into your postgres instance

Reading data

read all rows

SELECT * FROM album;

* in above sql statement mean select all columns in above table

if you only want to show certain columns in your output, here is the sql query for that

SELECT title, album_id FROM album;

Limit number of rows to be returned, let say we only want to return 10 rows from the result

SELECT title, album_id FROM album limit 10;

returning all columns or all rows have computation and read cost associated with it, so while you are playing with your queries, building them it will be good to use limit query

Create table from the rows which are response to another select query

CREATE TABLE album_temp AS (SELECT title, album_id FROM album limit 10);

How to create aliases for column and table names

SELECT al.title AS t, al.album_id AS id FROM album AS al;

Building aliases like this when you are working with multiple tables in same quesry and both tables have common names in it

links

social