Postgresql

INTRODUCTION OF  POSTGRESQL

SQL stands for Structured Query Language, which is a computer language for storing, manipulating, and retrieving data stored in a relational database. SQL is the first commercial language introduced for E.F Codd’s Relational model. SQL is one of the most popular query languages in use today.

Relational database management systems use structured query language (SQL) to store and manage data. The system stores multiple database tables that relate to each other. MS SQL Server, MySQL,PostgreSQL or MS Access are examples of relational database management systems. 

Brief overview of PostgreSQL

PostgreSQL, also known as Postgres, is an open-source relational database management system (RDBMS). It was originally developed at the University of California, Berkeley in the 1980s and has since became one of the most advanced databases available.

 PostgreSQL is highly extensible and allows for custom functions to be written in various programming languages such as C, Python, etc.. PostgreSQL supports advanced features such as Multi-Version Concurrency Control (MVCC), point in time recovery, tablespaces, asynchronous replication, and nested transactions.

PostgreSQL has long been associated with the elephant due to its official logo, which features an elephant named "Slonik." This common symbolism of the elephant emphasizes their shared focus on strength, memory, and reliability

SQL is used to :

Create new databases, or new tables in a database

Execute quarries

Retrieve data from database

Insert records into a database

Update records in a database

Delete records from a database

Types of SQL Commands

 There are four types of SQL commands:

 DDL, DML, DCL, and TCL.

Data Definition Language (DDL)

DDL, or Data Definition Language, is a type of SQL (Structured Query Language) used to define and manage all aspects of a database. DDL commands are used to create, modify, and delete database structures but not data.

CREATE Command - Create new database/ Table

CREATE DATABASE database_name;

CREATE TABLE table_name (

                column1 datatype,

                column2 datatype,

                                 … );

ALTER Command - Modifies the structure of database/table

ALTER TABLE table_name ADD COLUMN new_column_name column_type;

ALTER TABLE table_name DROP COLUMN column_name;

ALTER TABLE table_name ALTER COLUMN column_name TYPE new_type;

ALTER DATABASE database_name RENAME TO new_database_name;

DROP Command - Deletes a database/ table

DROP TABLE employees;

TRUNCATE - Remove all table records including allocated table spaces

TRUNCATE TABLE table_name;

TRUNCATE TABLE employees;

RENAME - Rename the database/table

ALTER TABLE old_table_name

RENAME TO new_table_name;

ALTER TABLE employees

RENAME TO staff;

ALTER TABLE table_name

RENAME COLUMN old_column_name TO new_column_name;

ALTER TABLE staff

RENAME COLUMN empID TO employeeID; 

DML Commands in SQL

DML is an abbreviation of Data Manipulation Language.

The DML commands in Structured Query Language change the data present in the SQL database. We can easily access, store, modify, update and delete the existing records from the database using DML commands.

Following are the four main DML commands in SQL:

SELECT Command - The select command contains several clauses that we can use to write a query easily. The basic task while performing the select command is to query data from tables within the database.

  SELECT select_list  

FROM table_name;

INSERT Command - is used to insert new rows into a table. We can insert a single row or multiple row values at a time into the particular table.

UPDATE Command - is used to change the present records in a table. To update the selected rows, we have to use the WHERE clause; otherwise, all rows would be updated.

UPDATE table_name    

SET column1 = value1,   

column2 = value2....,   

columnN = valueN    

WHERE condition;     

DELETE Command - is used to delete all existing records from a table. And the WHERE clause is used to remove the selected records or else, all the data would be eliminated.

DELETE FROM table_name    

WHERE [condition];    

DCL - (Data Control Language )

DCL Statements control the level of access that users have on database objects. 

GRANT -  give users access privileges to database, Grant allows users to read/write on certain database objects.

GRANT privilege_list | ALL ON table_name TO role_name;

REVOKE - withdraw access privileges given with the Grant command, Revoke keeps users  from read/write permission on database objects.    

  REVOKE privilege [, ...] ON object [, ...] FROM { PUBLIC | GROUP groupname | username }

TCL (Transaction Control Language )

TCL Statements allows you to control and mange transactions to maintain the integrity of data within SQL statements.

COMMIT Transaction -  Commits a transaction.

COMMIT;

ROLLBACK Transaction - ROLLBACK a transaction in case of any error. 

  ROLLBACK;

SAVE point Command:Is used to temporarily save a transaction so that you can rollback to that point whenever necessary.  

  BEGIN; INSERT INTO table1 VALUES (1); SAVEPOINT my_savepoint; INSERT INTO table1 VALUES (2); ROLLBACK TO SAVEPOINT my_savepoint; INSERT INTO table1 VALUES (3); COMMIT;

In SQL, data types define the type of data that can be stored in a column of a table. Each column in a table must have a specific data type. 

Numeric Types: 

INT or INTEGER: Integer data type

FLOAT: Float point number

DECIMAL or NUMERIC: Fixed-point number with a specified precision and scale

Character String type:

CHAR(n) : Fixed length character string.

VARCHAR(n): Variable length character string with maximum length of n

TEXT: Variable length character string with no maximum length. 

Date and Time Types: 

DATE: Date(YYYY-MM-DD)

TIME OF DAY: Time of Day (HH:MI:SS)

DATETIME or TIMESTAMP: Data and Time 

Boolean Type

BOOLEAN OR BOOL: Represents true or false values

Binary Large Object(BLOB) Types:

BINARY(n):Fixed-length binary data. 

VARBINARY(n):Variable -length binary data.