An Introduction to PostgreSQL Queries

Technical

This blog will provide an introduction to PostgreSQL queries and explain the fundamentals of using PostgreSQL queries.

Database Creation

When creating a database, you have two distinct methods at your disposal. The first involves crafting SQL queries to initiate database creation, while the second option allows you to create a database manually by clicking through the user interface.

Create through SQL query :

 CREATE DATABASE database_name;

In this picture, we can see that our database was created.

create through UserInterface:

Access your localhost and right-click on the database, triggering the appearance of a "CREATE DATABASE" popup. Here, you can specify the database name, designate an owner, and confirm your selection by clicking "Save." This action results in the creation of your database. To verify its existence, return to localhost and check for its presence.

Delete DATABASE :

DROP DATABASE database_name;

You can observe that our database has been deleted. Alternatively, you can manually delete it by right-clicking on our database and selecting the 'Delete/Drop Database' option, like in the picture below.

Create Table:

We can create a table using this SQL query.

CREATE TABLE sales (order_line INT PRIMARY KEY,order_id 
VARCHAR,order_date DATE,ship_date DATE,ship_mode 
VARCHAR,customer_id VARCHAR,product_id 
VARCHAR,sales NUMERIC,quantity INT,discount NUMERIC,profit NUMERIC);

In this query representing :create table “your tablename”(“your_column_name” datatype primary key, “your_column_name” datatype , etc…..);

Value add to table :

To do that, we are using the INSERT INTO command. We have two types of insert methods.

* with a column.

* without a column.

INSERT WITH COLUMN :

INSERT INTO sales (order_line, order_id, order_date, ship_date,
ship_mode, customer_id, product_id, sales, quantity, discount, 
profit) VALUES (9995,'CM-2017-619984','2017-05-06','2017-05-11', 
'Second Class', 'TB-21400', 'OFF-BI-10002026', 85.98, 10, 0.4, 5.369);

When inserting multiple records, utilize parentheses ( ) and commas to separate each record. By enclosing the values for each record within parentheses and separating them with commas, you can efficiently insert multiple records in a structured manner, simplifying data management and insertion processes, as shown in the SQL query below.

INSERT INTO sales (order_line, order_id,order_date,
ship_date, ship_mode,customer_id,product_id,sales,quantity,discount,profit)
VALUES(value1,value2,value3,........),(value1,value2,value3,........),
(value1,value2,value3,........);

Value inserting without mentioning columns:

INSERT INTO sales VALUES (9996, 'CZ-2017-619084', '2017-05-07', '2017-05-12', 'Second Class', 'TB-21900', 'ONF-BI-10009026', 85.98, 10, 0.4, 5.369);

We can insert records into their respective columns without explicitly specifying the column names.

Retrieve values :

We can open tables and each column using the select * command.

Select  *  from sales ;

In this command representing select * from "table_name" ; the * will represent each column of a table. If you run this query, our table sales will open.

We can select a particular column using the command select quantity,profit from sales; This command will represent select “column_name” from “table_name” we can get output like this picture.

We can select data without duplication using the DISTINCT command.

select distinct customer_name from customer; this query will represent select distinct “column_name” from “table name” Run this query, and we can get the result without duplication.

WHERE

We can set conditions in our selection using with ‘where’ clause.

select customer_name ,customer_id ,age from customer where age>30 and age<50; this query will represent select “column_name” from “table_name” where “condtion”;