PostgreSQL - Day 4

• Thu, Jun 9, 2022
• 3-minute read

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

22. Like and iLike

like operator is used to match text values against a pattern.

• get data which email is ending in .com

  SELECT * FROM person
  WHERE email LIKE '%.com';
  

  • % is a wildcard
  • _ means this has to match single characters.
    • get data which email start with 8 characters followed by @.

      SELECT * FROM person
      WHERE email LIKE '________@';
      

ILIKE has the same functionality, but it’s not case sensitive.

23. Group By

The GROUP BY statement groups rows that have the same values into summary rows

GROUP BY allows us to group data based on a column.

The GROUP BY statement is often used with aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more columns.

• find how many people are there in these countries by using COUNT() function

  SELECT country_of_birth, COUNT(*) FROM person
  GROUP BY country_of_birth ORDER BY country_of_birth;
  

  

24. Group By Having

The HAVING Clause enables you to specify conditions that filter which group results appear in the results.

• showing country where more than 5 people born

  SELECT country_of_birth, COUNT(*) FROM person
  GROUP BY country_of_birth
  HAVING COUNT(*) > 5;
  

  

aggregate functions

https://www.postgresql.org/docs/current/functions-aggregate.html

Aggregate functions compute a single result from a set of input values.

• count
• max
• min
• sum

25. Adding New Table and Data Using Mockaroo

Generate new data about cars by Mockaroo.

The course used a different schema.

create table car (
	id BIGSERIAL NOT NULL PRIMARY KEY,
	make VARCHAR(100) NOT NULL,
	model VARCHAR(100) NOT NULL,
	price NUMERIC(19, 2) NOT NULL
);

Thus, we must remove $ characters from all the rows in the SQL.

Finally, create a table by the \i <path of car sql> command.

26. Calculating Min, Max & Average

This section is going to show how to use the essential aggregation function.

Max

Use the Max() command to query the most expensive car from data.

SELECT Max(price) FROM car;

Min

AVG

ROUND

Use the ROUND command to round avg price.

Advance practice

Get the minimum car price for each make.

SELECT make, MIN(price) FROM car
GROUP BY make;

27. SUM

The SUM() command computes the sum of the non-null input values.

Get the sum of cars of each make.

SELECT make, SUM(price) FROM car GROUP BY make;

28. Basics of Arithmetic Operators

+ - * / ^ % !

example: make a discount (50% off)

SELECT *, price * 0.5 FROM car;

29. Arithmetic Operators - Round

You can specify the decimal places of Round

SELECT *, ROUND(price * 0.5, 2) FROM car;