SQL / PSQL Commands

The Devil is in the details.

Home » SQL / PSQL Commands

SQL / PSQL Commands

SQL (Structured Query Language)

SQL is a domain-specific language used for managing and manipulating relational databases. It provides a standardized way to interact with databases, allowing users to perform tasks such as creating, modifying, querying, and deleting data in a database. SQL is used by a wide range of relational database management systems (RDBMS), including PostgreSQL, MySQL, SQL Server, Oracle, and SQLite.

PSQL, on the other hand, is not a command in itself, but rather it refers to the command-line utility for interacting with PostgreSQL databases. PostgreSQL is a powerful open-source relational database management system that uses SQL as its query language. PSQL is a command-line tool that allows users to connect to a PostgreSQL database and execute SQL commands and queries directly from the terminal. Download the lastest PostgreSQL version.

Relational Database Basics

SQL, which stands for Structured Query Language, is the primary language used by relational database management systems (RDBMS) for accessing and manipulating data, as well as creating and managing databases and their objects.

History of SQL

The concept of relational databases was introduced by Dr. E.F. Codd in 1970 through his work “A Relational Model of Data for Large Shared Data Banks.” Following this, IBM began developing a relational database system. Between 1979 and 1982, Oracle, Relational Technology, Inc., and IBM released commercial relational databases, all adopting SQL as their query language. In 1986, SQL was standardized by the American National Standards Institute (ANSI). Despite the existence of this standard, different RDBMS implementations have their own extensions, but the core SQL remains largely the same.

Understanding Relational Databases

A relational database organizes data into tables, which are composed of rows and columns. Each table has a unique name and can relate to other tables through shared values.

Components of Tables

  • Rows: Represent individual records within a table, also referred to as tuples.
  • Columns: Represent attributes or characteristics of the records, also known as fields.

Relationships in Databases

Tables in a relational database can be linked through relationships, enabling data in one table to be connected to data in another.

Data Types in Tables

Columns in a table have specific data types that determine the kind of data they can store. Common data types in PostgreSQL include:

  1. boolean – stores true or false values.
  2. date – stores calendar dates.
  3. integer – stores whole numbers.
  4. text – stores strings of text.
  5. time – stores times of day.

For more details, refer to the PostgreSQL Data Types documentation.

Primary Keys

A primary key is a column or set of columns that uniquely identifies each record in a table. For example, a table of users might use a unique id column as its primary key.

Foreign Keys

A foreign key is a column that creates a link between two tables by referencing the primary key of another table. For instance, an author_id column in a books table might reference the id column in an authors table, thereby connecting books to their authors.

Relational Database Management Systems (RDBMS)

An RDBMS is software designed to create, manage, and manipulate relational databases. Although commonly referred to simply as databases, the term RDBMS more accurately describes this software. In this guide, PostgreSQL will be the RDBMS used.

PostgreSQL Overview

PostgreSQL is a robust, open-source RDBMS that adheres closely to the SQL standard while offering additional features.

Here are some common SQL and PSQL commands:

  1. SELECT: Used to retrieve data from one or more tables.
SELECT column1, column2 FROM table_name WHERE condition;

2. INSERT: Used to insert new data into a table.

INSERT INTO table_name (column1, column2) VALUES (value1, value2);

3. UPDATE: Used to modify existing data in a table.

UPDATE table_name SET column1 = new_value WHERE condition;

4. DELETE: Used to delete data from a table.

DELETE FROM table_name WHERE condition;

5. CREATE TABLE: Used to create a new table in the database.

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    
);

6. ALTER TABLE: Used to modify an existing table structure.

ALTER TABLE table_name ADD column_name datatype;