SQL Query Cheatsheet using PostgreSQL

 

 

 

 

Introduction:

PostgreSQL is an open-sourced object-relational database system using the SQL query to automate and customize data queries.

As an analyst, I have used SQL for a number of systems and have found that PostgreSQL is one of the most comprehensive and straightforward systems used for learning the SQL query.

 I hope to be able to convince you as well.

 I have been in the middle of a good course in Udemy titled 'Master SQL for Data Science'
(link below).

Some of these queries have been previously learned through PostgreSQL, but this is likely applicable through every application you choose to use.

 

 To follow along exactly, you can start by downloading PostgreSQL online, installing default values and setting it up in your system. 

 

However, this is not necessary to follow along, as most of these queries will be applicable through any SQL system.

Without further ado, Lets get started.

The PostGreSQL interface seems very intuitive so far

Scenario:
So you have a list of shopping things, some important, others not so important. These are basically categories.

Such a list would look like this:

Food | Price | Importance
Chicken | $8 | high
Eggs | $12 | high
Cereal | $5 | Low

Then maybe you have a brother who wants chips, so you add another category 

Buyer | Food | Price | Importance
You | Chicken | $8 | high
You | Eggs | $12 | high
You | Cereal | $5 | Low
Brother | chips | $2 | High

Then maybe you have a thousand people who ask you for things as well.  You cannot keep track of every person with a simple name, so you need to give them an ID to keep track of the order. But you also want to add a transaction ID to keep track of each order too:

ID | person_ID | Buyer | Food | Price | Importance
001 | 001         |  You | Chicken | $8 | high
002 | 001         | You | Eggs | $12 | high
003 | 001         | You | Cereal | $5 | Low
004 | 002         | Brother | chips | $2 | High

Now we have a table to work with

 Queries:

CRUD	Script
Create col in Tbl	create table TblX (ID primarykey int, First_Name varchar(100), Last_Name(100))
Update col in Tbl	alter table Tbl4 add column ID varchar(100) FIRST
Insert row in column	insert into tbl4 values(5,39,'Riverside',16)
Insert value in col	update Tbl1 as a set a.Name = "Helix" WHERE a.ID = 5
Delete row in col	delete from tbl4 where city = 'Riverside'
select col from Tbl; describe table;	select a.id, a.comments from tbl2 as a; describe Tbl;