SQL stands for Structured Query Language. It is a standardized programming language used to manage and manipulate relational databases.
1. Why we use SQL?
It allows end-users to communicate with databases and perform tasks like creating, updating, and deleting databases. Almost every mid to large-sized organization uses SQL, including Facebook, Microsoft, LinkedIn, and Accenture.
2. What is SQL?
Structured query language (SQL) is a programming language for storing and processing information in a relational database. A relational database stores information in tabular form, with rows and columns representing different data attributes and the various relationships between the data values. You can use SQL statements to store, update, remove, search, and retrieve information from the database. You can also use SQL to maintain and optimize database performance.
3. What does SQL do?
SQL is not like a typical programming language (like JavaScript, for example) that relies on constructs like iteration and recursion. We don’t need to tell SQL how to process data in the database—we simply give SQL server commands to perform a task (like selecting records) and the Query Engine under the hood generates an execution plan that it uses for efficient operation.
Selecting data and working with rows, columns, and tables is only the tip of the SQL iceberg! Most brands of SQL support functionality for aggregating data, indexing, creating user-defined functions and stored procedures, looping, using logical operators and variables, and managing user security.
- Creating a database
- Creating tables
- Inserting records
- Selecting records
- Filtering records
- Updating records
- Modifying tables
2. What is PSQL ?
PostgreSQL, or Postgres, is an object-relational database management system that utilizes the SQL language. PSQL is a powerful interactive terminal for working with the PostgreSQL database. It enables users to execute queries efficiently and manage databases effectively.
Top PSQL Commands in PostgreSQL
Here are the top 22 PSQL commands that are frequently used when querying a PostgreSQL database:
| Serial No. | Command | Description |
|---|---|---|
| 1 | psql -d database -U user -W | Connects to a database under a specific user |
| 2 | psql -h host -d database -U user -W | Connect to a database that resides on another host |
| 3 | psql -U user -h host "dbname=db sslmode=require" | Use SSL mode for the connection |
| 4 | \c dbname | Switch connection to a new database |
| 5 | \l | List available databases |
| 6 | \dt | List available tables |
| 7 | \d table_name | Describe a table such as a column, type, modifiers of columns, etc. |
| 8 | \dn | List all schemes of the currently connected database |
| 9 | \df | List available functions in the current database |
| 10 | \dv | List available views in the current database |
| 11 | \du | List all users and their assign roles |
| 12 | SELECT version(); | Retrieve the current version of PostgreSQL server |
| 13 | \g | Execute the last command again |
| 14 | \s | Display command history |
| 15 | \s filename | Save the command history to a file |
| 16 | \i filename | Execute psql commands from a file |
| 17 | \? | Know all available psql commands |
| 18 | \h | Get help |
| 19 | \e | Edit command in your own editor |
| 20 | \a | Switch from aligned to non-aligned column output |
| 21 | \H | Switch the output to HTML format |
| 22 | \q | Exit psql shell |
Additional Information:
- The
-doption inpsqlcommands is used to state the database name.- The
-Uoption specifies the database user.- The
-hoption indicates the host on which the database server resides.- The
\h ALTER TABLEcan be used to get detailed information on the ALTER TABLE statement.
Why should we use PostgreSQL? (TBD)
PostgreSQL comes with many features aimed to help developers build applications, administrators to protect data integrity and build fault-tolerant environments, and help you manage your data no matter how big or small the dataset. In addition to being free and open source, PostgreSQL is highly extensible. For example, you can define your own data types, build out custom functions, even write code from different programming languages without recompiling your database!
PostgreSQL tries to conform with the SQL standard where such conformance does not contradict traditional features or could lead to poor architectural decisions. Many of the features required by the SQL standard are supported, though sometimes with slightly differing syntax or function. Further moves towards conformance can be expected over time. As of the version 16 release in September 2023, PostgreSQL conforms to at least 170 of the 177 mandatory features for SQL:2023 Core conformance. As of this writing, no relational database meets full conformance with this standard.
Below is an inexhaustive list of various features found in PostgreSQL, with more being added in every major release:
- Data Types
- Primitives: Integer, Numeric, String, Boolean
- Structured: Date/Time, Array, Range / Multirange, UUID
- Document: JSON/JSONB, XML, Key-value (Hstore)
- Geometry: Point, Line, Circle, Polygon
- Customizations: Composite, Custom Types
- Data Integrity
- UNIQUE, NOT NULL
- Primary Keys
- Foreign Keys
- Exclusion Constraints
- Explicit Locks, Advisory Locks
- Concurrency, Performance
- Indexing: B-tree, Multicolumn, Expressions, Partial
- Advanced Indexing: GiST, SP-Gist, KNN Gist, GIN, BRIN, Covering indexes, Bloom filters
- Sophisticated query planner / optimizer, index-only scans, multicolumn statistics
- Transactions, Nested Transactions (via savepoints)
- Multi-Version concurrency Control (MVCC)
- Parallelization of read queries and building B-tree indexes
- Table partitioning
- All transaction isolation levels defined in the SQL standard, including Serializable
- Just-in-time (JIT) compilation of expressions
- Reliability, Disaster Recovery
- Write-ahead Logging (WAL)
- Replication: Asynchronous, Synchronous, Logical
- Point-in-time-recovery (PITR), active standbys
- Tablespaces
- Security
- Authentication: GSSAPI, SSPI, LDAP, SCRAM-SHA-256, Certificate, and more
- Robust access-control system
- Column and row-level security
- Multi-factor authentication with certificates and an additional method
- Extensibility
- Stored functions and procedures
- Procedural Languages: PL/pgSQL, Perl, Python, and Tcl. There are other languages available through extensions, e.g. Java, JavaScript (V8), R, Lua, and Rust
- SQL/JSON constructors, query functions, path expressions, and JSON_TABLE
- Foreign data wrappers: connect to other databases or streams with a standard SQL interface
- Customizable storage interface for tables
- Many extensions that provide additional functionality, including PostGIS
- Internationalisation, Text Search
- Support for international character sets, e.g. through ICU collations
- Case-insensitive and accent-insensitive collations
- Full-text search
There are many more features that you can discover in the PostgreSQL documentation. Additionally, PostgreSQL is highly extensible: many features, such as indexes, have defined APIs so that you can build out with PostgreSQL to solve your challenges.
What is the main purpose of PostgreSQL?
PostgreSQL is used as the primary data store or data warehouse for many web, mobile, geospatial, and analytics applications.
ammujavanotes 