Intro to SQL with PostgreSQL

what is SQL & What is PostgreSQL ?

SQL (Structured Query Language) is a programming language used to manage and manipulate relational databases. It is the standard language for interacting with databases, and is used to create, update, and query data in a database.

PostgreSQL is a powerful, open-source relational database management system that is built on SQL. It is known for its robustness, scalability, and support for advanced data types and features.

In this series of articles, we will explore the basics of SQL and its role in relational databases, compare and contrast SQL and PostgreSQL, and discuss the features and capabilities of PostgreSQL that make it a popular choice for enterprise-level applications. We will also provide examples of common SQL commands and how they can be used in PostgreSQL, as well as offer tips and best practices for optimizing performance and scalability in PostgreSQL.

Tools & resources

we will use ready online compiler to interact with PostgreSQL and write SQL queries

please make sure you open the compiler to follow up with the article and try SQL commands & queries

Database in SQL

with PostgreSQL we can easily create a Database with CREATE keyword see the following syntax :
CREATE DATABASE database_name;

assume you want to create a database with the name store
we can use the next query

 CREATE DATABASE store;

if we need to delete the database there is another keyword it's DROP with the syntax below :
DROP DATABASE database_name;

so if you want to delete your store database it will be :

DROP DATABASE store;

if you got this error it's fine just you don't have permission to create or delete the database:

psql: commands.sql:1: ERROR:  database "store" does not exist

Tables in SQL

the database is a set of tables, so the table is the place we can make our queries, and the previous keywords CREATE, DROP we can use them to make or delete a table like this :
CREATE TABLE table_name(
field_name type constraint,
field_name type constraint,
);

we can make a customer table with the following :

CREATE TABLE customer(
id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY , 
name VARCHAR(50) NOT NULL,
email TEXT NOT NULL
);

and we can drop tables with this syntax :
DROP TABLE table_name ;
we can apply it to our customer table

DROP TABLE customer ;

I will explain the data types like TEXT , VARCHAR , INTEGER and others in another article

Case Sensitivity in PostgreSQL

SQL is case-insensitive in most implementations, meaning commands and keywords can be written in uppercase or lowercase. However, PostgreSQL is case-sensitive when it comes to the names of database objects such as tables, columns, and indexes.
This means table names "customer" and "CUSTOMER" would be treated as different tables in PostgreSQL.
This can lead to confusion and errors if not handled properly, so it's important to be consistent when naming objects in your database.

operations on a table

we can perform many operations from simplest to most complex ones, but they're a main 4 operations we can do to a table like:

Create a row

we can create row/rows with the following syntax :

INSERT INTO table_name(column1,column2,column3) VALUES('value','value',int_value)

we can easily run the queries below and see the result, but make sure you created the customer table again :

INSERT INTO customer("name","email")  VALUES('ahmed','[e-mail]') RETURNING *;

INSERT INTO customer("name","email")  
VALUES('abdullh','[e-mail]') ,
('mohmed','[e-mail]'),
('mostfa','[e-mail]'),
('mahmoud','[e-mail]') 
RETURNING *;

returning let us show columns and * make it return all columns

Updating row

we can update any row/rows but we have a more powerful keyword it's WHERE after we add it we can update just the row we need with the following syntax :
UPDATE table_name SET column1='value' WHERE column2='search value'
for example

UPDATE customer SET name='tarek' WHERE name='mostfa' RETURNING *

Deleting a row

delete is similar to update you just type the word DELETE
with the following syntax :

DELETE FROM table_name SET WHERE column='search value'
DELETE FROM customer WHERE name='mostfa' RETURNING *

Querying row

I think most courses or tutorials introduce their course with querying and how to get data from DB, but I think it needs to be delayed because I think you need to know how to insert, delete and update data before querying it.

the most important keyword in Querying or maybe in SQL language it's SELECT you can make multiple behaviors with it like :

SELECT CONCAT('hello ',' world') AS hello; -- to print words  or 
SELECT NOW() --know what time is it and other ;

and the main role selects data with the following syntax :
SELECT column1,column2,column3 FROM table where column='search term'
like

SELECT * FROM customer; -- we can use * to select all columns 
SELECT name, id FROM customer where id >1 ; 
SELECT count(*) AS TOTAL FROM customer where id >1 -- to count all rows that have id >1 ; 

What next

we can say this is an intro of a series of articles and we will continue to explain SQL and how to use it right and maybe give you some tips& tricks.