Intro to SQL with PostgreSQL
Tags: sql, postgres, opensource, webdev
Published: 2023-01-28 12:55:12

## what is SQL & What is PostgreSQL ?

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dvbmtmkgcv4tt1d44u36.gif)

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](https://onecompiler.com/postgresql) 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','ahmed@email.com') RETURNING *;

INSERT INTO customer("name","email")  
VALUES('abdullh','abdullh@email.com') ,
('mohmed','mohmed@email.com'),
('mostfa','mostfa@email.com'),
('mahmoud','mahmoud@email.com') 
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.