SQL Fundamentals in PostgreSQL With Code Example

SQL Fundamentals in PostgreSQL

Declarative programming language SQL is the industry standard for relational databases, and ANSI and ISO uphold its standards. Offering many contemporary features, PostgreSQL is an object-relational database management system (ORDBMS) that supports a large percentage of the SQL standard. Despite not supporting SQL at first, PostgreSQL was renamed PostgreSQL to reflect the addition of SQL in 1994.

SQL Fundamentals

In order to work with a database, SQL is mostly used to build and maintain data structures as well as to add, modify, retrieve, and remove data. SQL is declarative, as opposed to imperative languages, which specify a comprehensive data processing algorithm. The precise sequence of operations and algorithms is left to the database itself, while developers identify the tables involved, the required data format, and the processing rules.

Three primary components make up the SQL language:

Data Definition Language (DDL): Used to specify and modify the database’s relational structure. Database object creation, modification, and deletion instructions are included in this.

Data Manipulation Language (DML): You may get, insert, update, and delete relation data with DML. DML basics are DELETE, UPDATE, INSERT, and SELECT.

Data Control Language (DCL): Data Control Language manages relation and database object permissions and access rights.

SQL is fundamentally based on a relational paradigm, in which a database is a collection of relations, or tables as they are more widely known. Every table is made up of tuples, or rows, which stand for individual records, and attributes, or columns, which specify the properties of the data that is kept in each row. Rules for data integrity in these tables are further defined by constraints.

SQL Lexical Structure

The semicolon (;) or the end of the input stream marks the end of each command or statement that makes up SQL input. Tokens are the smallest meaningful units in the language and make up a command.

There are multiple types of tokens:

Keywords: SQL words like SELECT, UPDATE, and VALUES that have a specific meaning. PostgreSQL documentation lists keywords extensively.

Identifiers: Identifiers are database object names like tables, columns, and functions. Without double quotes (“”), PostgreSQL identifications are case-insensitive. Qualified object names include schema_name.table_name.column_name.

Literals (Constants): String constants are character sequences in single quotes. Double ‘Dianne’s horse’ to include it in a string. Dollar quoting ($$string content$$ or $tag$string content$tag$) makes complex string literals easier to write in PostgreSQL. Function definitions benefit from not having to escape backslashes or single quotes in strings.

A decimal point or exponent sign may be included in numerical constants, which are made up of digits. Bit string constants have 0s and 1s or hexadecimal digits, respectively, and are prefixed by B’ (binary) or X’ (hexadecimal). You can use CAST() or :: to indicate data type constants (e.g., ’10’::integer or CAST(’10’ AS integer)).

Operators: Mathematical operators (+, -, *, /) and comparison operators (=, <, >) allow inputs and return values.The precedence of an operator determines expression execution order.

Special Characters: Semicolons, commas, brackets and brackets are special characters. Spaces, tabs, and newlines can divide tokens to improve readability. The server rejects single-line comments with double dashes (–) or C-style block comments with /*… */, which can be nested in SQL.

Code Example:

CREATE TABLE students (
    id SERIAL PRIMARY KEY,   
    name TEXT,
    marks INTEGER
);
INSERT INTO students (name, marks)
VALUES 
    ('Alice', 85),          
    ('Bob', 90),
    ('Charlie', 75);
SELECT name, marks, (marks + 5)::integer AS bonus_marks   
FROM students
WHERE marks > 80;  

Output:

CREATE TABLE
INSERT 0 3
 name  | marks | bonus_marks 
-------+-------+-------------
 Alice |    85 |          90
 Bob   |    90 |          95
(2 rows)

PostgreSQL’s extended type system lets users build additional data types, functions, operators, and index strategies. In addition to JSON and JSONB, this offers geometric, network address, and tsvector/tsquery text searches. C Language, Python, and Perl user-defined functions help PostgreSQL. Des méta-commandes de style shell (par exemple, \h pour l’aide, \d pour les définitions de table et \q pour quitter) sont accessibles dans le client en ligne de commande psql pour la gestion et l’interrogation des bases de données.