PostgreSQL - Day 3

Wed, Jun 8, 2022
3-minute read

Keep learning PostgreSQL by Learn PostgreSQL Tutorial - Full Course for Beginners.

13. Generate 1000 Rows With Mockaroo

Mockaroo is simply a data generator

options
- propotion of blank data
- rows
- format
  - xls
  - sql
  - …
- include CREATE TABLE

Then you can download your data.

Modify it with any text editor.

Also, don’t forget to add column id, which you saw previously.

We can use \i command to execute the downloaded command.

But before doing this, we must add the country column first. There is two way:

drop the table and create a new one (not recommended)
alter table (this may include in a later section)

Now we can insert the mock data into our DB.

\i <path to mock data>

The result

14. SELECT FROM

To grab the records from table, use SQL command

SELECT * FROM <table_name>;

* means you want to select every single column from this table.

Replace * with the columns you want (separate the column names by comma). Then you can get the records only with the columns you specified.

SELECT first_name, last_name FROM person;

What would happen if the fields were empty?

It returns data with null entries.

15. Order By

Sort data using the order by keyword.

SELECT * FROM person ORDER BY country_of_birth;

ASC(ASCENDING) and DESC(DESCENDING) are the keywords we use in conjunction with ORDER BY.

SELECT * FROM person ORDER BY country_of_birth ASC;
SELECT * FROM person ORDER BY country_of_birth DESC;

descending

You can combine multiple columns when you sort.

SELECT * FROM person ORDER BY country_of_birth, email;

16. Distinct

The SELECT DISTINCT statement is used to return only distinct (different) values.

SELECT DISTINCT country_of_birth FROM person ORDER BY country_of_birth;

17. Where Clause and AND

Where Clause allows us to filter data on condition.

SELECT * FROM person WHERE gender = 'Female';

Multiple condition

Add multiple conditions by AND plus another condition.

SELECT * FROM person WHERE gender = 'Female' AND country_of_birth = 'Poland';

more complicated condition

use OR

SELECT * FROM person WHERE gender = 'Female' AND (country_of_birth = 'Poland' OR country_of_birth = 'China');

18. Comparison Operator

It allows us to perform arithmetic operations, comparisons, and bitwise and logical operations. And it returns true or false.

You can use comparison operators to filter down your data in the WHERE clause.

SELECT * FROM person WHERE gender <> 'Male';

not equal

SELECT 1<>2

19. Limit, Offset & Fetch

LIMIT: limit the results returned by this query.

SELECT * FROM person LIMIT 10;

OFFSET: OFFSET says to skip that many rows before beginning to return rows. It’s a feature supported by Postgres.

SELECT * FROM person OFFSET 5 LIMIT 5;

FETCH : LIMIT is not a keyword by SQL standard. The official way to limit the results coming from a query is FETCH.

SELECT * FROM person FETCH FIRST 5 ROW ONLY;

20. IN

Instead of using OR to gather different groups of data, you can use IN easily.

IN keyword takes an array of values and then returns a query matching those values.

SELECT * FROM person WHERE country_of_birth = 'China' OR country_of_birth = 'France' OR country_of_birth = 'Brazil';

SELECT *
FROM person
WHERE country_of_birth in ('China', 'Brazil', 'France');

21. Between

Use the Between keyword to select data from a range.

SELECT * FROM person
WHERE date_of_birth
BETWEEN DATE '2000-01-01' AND '2015-01-01';

learn PostgreSQL Database