Login

HTML Links

Links are found in nearly all web pages. Links allow users to click their way from page to page.

HTML Links - Hyperlinks

HTML links are hyperlinks.

You can click on a link and jump to another document.

When you move the mouse over a link, the mouse arrow will turn into a little hand.

Note: A link does not have to be text. A link can be an image or any other HTML element!

HTML Links - Syntax

The HTML <a> tag defines a hyperlink. It has the following syntax:

<a href="url">link text</a>

The most important attribute of the <a>

The link text is the part that will be visible to the reader.

Clicking on the link text, will send the reader to the specified URL address.

Example

This example shows how to create a link to W3Schools.com:

<a href="https://www.w3schools.com/">Visit W3Schools.com!</a>

By default, links will appear as follows in all browsers:

  • An unvisited link is underlined and blue
  • A visited link is underlined and purple
  • An active link is underlined and red

Tip: Links can of course be styled with CSS, to get another look!

HTML Links - The target Attribute

By default, the linked page will be displayed in the current browser window. To change this, you must specify another target for the link.

The target attribute specifies where to open the linked document.

The target attribute can have one of the following values:

  • _self - Default. Opens the document in the same window/tab as it was clicked
  • _blank - Opens the document in a new window or tab
  • _parent - Opens the document in the parent frame
  • _top - Opens the document in the full body of the window

Example

Use target="_blank" to open the linked document in a new browser window or tab:

<a href="https://www.w3schools.com/" target="_blank">Visit W3Schools!</a>

Absolute URLs vs. Relative URLs

Both examples above are using an absolute URL (a full web address) in the href attribute.

A local link (a link to a page within the same website) is specified with a relative URL (without the "https://www" part):

Example

<h2>Absolute URLs</h2>
<p><a href="https://www.w3.org/">W3C</a></p>
<p><a href="https://www.google.com/">Google</a></p>

<h2>Relative URLs</h2>
<p><a href="html_images.asp">HTML Images</a></p>
<p><a href="/css/default.asp">CSS Tutorial</a></p>

HTML Links - Use an Image as a Link

To use an image as a link, just put the <img> tag inside the <a> tag:

Example

<a href="default.asp">
<img src="smiley.gif" alt="HTML tutorial" style="width:42px;height:42px;">
</a>

Link to an Email Address

Use mailto: inside the href attribute to create a link that opens the user's email program (to let them send a new email):

Example

<a href="mailto:[email protected]">Send email</a>

Button as a Link

To use an HTML button as a link, you have to add some JavaScript code.

JavaScript allows you to specify what happens at certain events, such as a click of a button:

Example

<button onclick="document.location='default.asp'">HTML Tutorial</button>

Tip: Learn more about JavaScript in our .

Link Titles

The title attribute specifies extra information about an element. The information is most often shown as a tooltip text when the mouse moves over the element.

Example

<a href="https://www.w3schools.com/html/" title="Go to W3Schools HTML section">Visit our HTML Tutorial</a>

More on Absolute URLs and Relative URLs

Example

Use a full URL to link to a web page: 

<a href="https://www.w3schools.com/html/default.asp">HTML tutorial</a>

Example

Link to a page located in the html folder on the current web site: 

<a href="/html/default.asp">HTML tutorial</a>

Example

Link to a page located in the same folder as the current page: 

<a href="default.asp">HTML tutorial</a>

You can read more about file paths in the chapter .

Chapter Summary

  • Use the <a> element to define a link
  • Use the href attribute to define the link address
  • Use the target attribute to define where to open the linked document
  • Use the <img> element (inside <a>) to use an image as a link
  • Use the mailto: scheme inside the href attribute to create a link that opens the user's email program

HTML Link Tags

Tag Description
Defines a hyperlink

For a complete list of all available HTML tags, visit our .


[+:

PostgreSQL is an advanced relational database system.

PostrgeSQL supports both relational (SQL) and non-relational (JSON) queries.

PostgreSQL is free and open-source.

Learning by Doing

In this tutorial you get a step by step guide on how to install and create a PostgreSQL database. You will learn how to create a project where you can create, read, update, and delete data.

You will learn how to query, filter, and sort data from the database.

Examples in Each Chapter

In the tutorial we will use examples to show what SQL statements returns from the database:

Example

Sort the table by year:

SELECT * FROM cars
ORDER BY year;
 

What is PostgreSQL?

PostgreSQL is an free open-source database system that supports both relational (SQL) and non-relational (JSON) queries.

PostgreSQL is a back-end database for dynamic websites and web applications.

PostgreSQL supports the most important programming languages:

  • Python
  • Java
  • C/C++
  • C#
  • Node.js
  • Go
  • Ruby
  • Perl
  • Tcl

PostgreSQL supports basically all features that other database management systems support.

PostgreSQL History

PostgreSQL was invented at the Berkeley Computer Science Department, University of California.

It started as a project in 1986 with the goal of creating a database system with the minimal features needed to support multiple data types.

In the beginning, PostgreSQL ran on UNIX platforms, but now it can run on various platforms, including Windows and MacOS.

 
PostgreSQL Intro

Download PostgreSQL

To install PostgreSQL locally on your computer, visit the installer by EDB, and download the newest version compatible with your operating system.

I will choose the newest Windows version:

Start the Install

When the downloading is complete, double click the downloaded file and start the installation:

Specify Directory

You can specify the location of PostgreSQL, I will go with the default choice:

Select Components

To use PostgreSQL, you will need to install the PostgreSQL Server. In this tutorial we will also use the pgAdmin 4 component, and the Command Line Tools:

Storage Directory

You can also choose where to store the database data, I will go with the default choice:

Select Password

You will have to select a password to get access to the database. Since this is a local database, with no incoming connection, I will choose the password 12345678:

Select Port

You can set the port the server should listen on, I will go with the default choice:

Select Locale

Select the geographically location of the database server:

Final Check

If everything looks OK, click 'Next' to continue:

Start Installation:

Click 'Next' to start the installation:

Installing

This can take a while, please wait.

Complete!

Now you have installed PostgreSQL on your computer, and in the next chapter you will start using it!

Connect to the Database

If you have followed the steps from the Install PostgreSQL page, you now have a PostgreSQL database on you computer.

There are several ways to connect to the database, we will look at two ways in this tutorial:

  • SQL Shell (psql)
  • pgAdmin 4

Both of them comes with the installation of PostgreSQL

SQL Shell (psql)

SQL Shell (psql) is a terminal based program where you can write and execute SQL syntax in the command-line terminal.

Open SQL Shell (psql)

You will find the SQL Shell (psql) tool in the start menu under PostgreSQL:

Tip: If you cannot find it, try searching for "SQL Shell" on your computer.

Once the program is open, you should see a window like the one below.

Insert the name of the server.

The suggested choice is [localhost], which is correct, press [Enter] to accept:

Database

The suggested database is [postgres], which is correct, press [Enter] to accept:

Port

The suggested port is [5432], which is correct, at least in my case, press [Enter] to accept:

Username

The suggested username is [postgres], which is correct, at least for me, press [Enter] to accept:

Password

Enter the password you chose when you installed the PostgreSQL database, my password is 12345678:

Result

The result might look like an error, but if it shows psql (15.2) or any other version, and in the end you see the postgres=# command (and maybe a warning in between), then you have successfully connected to the database!

Execute SQL Statements

Once you have connected to the database, you can start executing SQL statements.

Our database is empty, so we cannot query any tables yet, but we can check the version with this SQL statement:

SELECT version();

To insert SQL statements in the SQL Shell command, just write them after the postgres=# command like this:

Press [Enter] and the result should look like this:

Remember the Semicolon

Note: Always end SQL statements with a semicolon ;

SQL Shell waits for the semicolon and executes all lines as one SQL statement.

A multiple lines SQL statement is not executed before we include a semicolon at the end.

Example

Same statement, but in two lines:

Connect to the Database

In the previous chapter we learned how to connect to the database using the SQL Shell (psql) application, which is a command-based application that allows us to interact with the PostgreSQL database.

There is another application that comes built-in with the PostgreSQL installation, the pgAdmin 4 application, which also offers to interact with the database, but in a more user-friendly way.

Let us take a look and see how it works.

Start pgAdmin4

You will find the pgAdmin4 application in the start menu under PostgreSQL:

Tip: If you cannot find it, try searching for "pgAdmin4" on your computer.

Once the program has started, you should see a window like the one below, choose a master password, Since this is a local database that will run only on my computer, I will once again choose the password 12345678:

pgAdmin4

Once you are inside the program, try to perform a simple SQL query.

To do that we have to navigate to the database.

Start by opening the [Servers] option in the menu on the left:

Connect to Server

Now you need to enter the password that you created when you installed PostgreSQL, my password is 12345678:

Find Database

Click on the [Database] option on in the menu on the left:

Open Query Tool

You should find a database named postgres, right-click it choose the "Query Tool":

Query Tool

In the Query Tool we can start executing SQL statements.

Write SQL Statements

Our database is empty, so we cannot query any tables yet, but we can check the version with this SQL statement:

SELECT version();

To insert SQL statements in the Query Tool, just write in the input box like this:

Execute SQL Statements

To execute a SQL statement, click the "Play" button above the input box:

Result

The SQL statement is executed, and you can see the result in the "Data Output" area:

Now we have learned two ways of connection to a database and execute SQL statements on it:

  • SQL Shell (psql)
  • pgAdmin 4

Connect to the Database

To create a new database table using the SQL Shell, make sure you are connected to the database. If not, follow the steps in the Get Started chapter of this tutorial.

Once you are connected, you are ready to write SQL statements!

Create Table

The following SQL statement will create a table named cars in your PostgreSQL database:

CREATE TABLE cars (
  brand VARCHAR(255),
  model VARCHAR(255),
  year INT
);

When you execute the above statement, an empty table named cars will be created, and the SQL Shell application will return the following:

CREATE TABLE

In the SQL Shell application on your computer the operation above might look like this:

SQL Statement Explained

The above SQL statement created an empty table with three fields: brand, model, and year.

When creating fields in a table we have to specify the data type of each field.

For brand and model we are expecting string values, and string values are specified with the VARCHAR keyword.

We also have to specify the number of characters allowed in a string field, and since we do not know exactly, we just set it to 255.

For year we are expecting integer values (numbers without decimals), and integer values are specified with the INT keyword.

Display Table

You can "display" the empty table you just created with another SQL statement:

SELECT * FROM cars;

Which will give you this result:

 brand | model | year
-------+-------+------
(0 rows)

In the SQL Shell application on your computer the operation above might look like this:

In the next chapters we will learn how to insert data into a table, and also more on how to retrieve data from a table.

Insert Into

To insert data into a table in PostgreSQL, we use the INSERT INTO statement.

The following SQL statement will insert one row of data into the cars table you created in .

INSERT INTO cars (brand, model, year)
VALUES ('Ford', 'Mustang', 1964);

The SQL Shell application will return the following:

INSERT 0 1

Which means that 1 row was inserted.

Don't think about the 0, for now, just accept that it represents something else and will always be 0.

SQL Statement Explained

As you can see in the SQL statement above, string values must be written with apostrophes.

Numeric values can be written without apostrophes, but you can include them if you want.

Display Table

To check the result we can display the table with this SQL statement:

SELECT * FROM cars;

Which will return this result:

 brand |  model  | year
-------+---------+------
 Ford  | Mustang | 1964
(1 row)

Insert Multiple Rows

To insert multiple rows of data, we use the same INSERT INTO statement, but with multiple values:

INSERT INTO cars (brand, model, year)
VALUES
  ('Volvo', 'p1800', 1968),
  ('BMW', 'M1', 1978),
  ('Toyota', 'Celica', 1975);

The SQL Shell application will return the following:

INSERT 0 3

Which means 3 rows were successfully inserted.

Display Table

To check the result we can display the table with this SQL statement:

Example

SELECT * FROM cars;

Click the "Run Example" button to see the result of the select statement.

PostgreSQL Exercises

Test Yourself With Exercises

Exercise:

Write the correct SQL statement to insert new records into the cars table:

 cars (brand, model, year)
 ('Ford', 'Mustang', 1964);

Select Data

To retrieve data from a data base, we use the SELECT statement.

Specify Columns

By specifying the column names, we can choose which columns to select:

Example

SELECT brand, year FROM cars;

Return ALL Columns

Specify a * instead of the column names to select all columns:

Example

SELECT * FROM cars;

PostgreSQL Exercises

Test Yourself With Exercises

Exercise:

Write the correct SQL statement to select all columns, and all records, from the cars table:

 FROM cars;

The ALTER TABLE Statement

To add a column to an existing table, we have to use the ALTER TABLE statement.

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

The ALTER TABLE statement is also used to add and drop various constraints on an existing table.

ADD COLUMN

We want to add a column named color to our cars table.

When adding columns we must also specify the data type of the column. Our color column will be a string, and we specify string types with the VARCHAR keyword. we also want to restrict the number of characters to 255:

Example

Add a column named color:

ALTER TABLE cars
ADD color VARCHAR(255);

Result

ALTER TABLE

Display Table

To check the result we can display the table with this SQL statement:

Example

SELECT * FROM cars;

As you can see, the cars table now has a color column.

The new column is empty, you will learn how to fill it with values in the .

PostgreSQL Exercises

Test Yourself With Exercises

Exercise:

Write the correct SQL statement to add a column named colors to the cars table:

 cars
 color VARCHAR(255);

The UPDATE Statement

The UPDATE statement is used to modify the value(s) in existing records in a table.

Example

Set the color of the Volvo to 'red':

UPDATE cars
SET color = 'red'
WHERE brand = 'Volvo';

Result

UPDATE 1

Which means that 1 row was affected by the UPDATE statement.

Note: Be careful with the WHERE clause, in the example above ALL rows where brand = 'Volvo' gets updated.

Display Table

To check the result we can display the table with this SQL statement:

Example

SELECT * FROM cars;

Warning! Remember WHERE

Be careful when updating records. If you omit the WHERE clause, ALL records will be updated!

Example

Without the WHERE clause, ALL records will be updated:

UPDATE cars
SET color = 'red';

Result

UPDATE 4

Which means that all 4 row was affected by the UPDATE statement.

Display Table

To check the result we can display the table with this SQL statement:

Example

SELECT * FROM cars;

Update Multiple Columns

To update more than one column, separate the name/value pairs with a comma ,:

Example

Update color and year for the Toyota:

UPDATE cars
SET color = 'white', year = 1970
WHERE brand = 'Toyota';

Result

UPDATE 1

Which means that 1 row was affected by the UPDATE statement.

Display Table

To check the result we can display the table with this SQL statement:

Example

SELECT * FROM cars;

PostgreSQL Exercises

Test Yourself With Exercises

Exercise:

Write the correct SQL statement to update the value of the model column to 'Bronco' for any record where the brand is 'Ford':

 cars
 model = 'Bronco'
WHERE brand = 'Ford';

The ALTER TABLE Statement

To change the data type, or the size of a table column we have to use the ALTER TABLE statement.

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

The ALTER TABLE statement is also used to add and drop various constraints on an existing table.

ALTER COLUMN

We want to change the data type of the year column of the cars table from INT to VARCAHR(4).

To modify a column, use the ALTER COLUMN statement and the TYPE keyword followed by the new data type:

Example

Change the color column from VARCHAR(255) to VARCHAR(30):

ALTER TABLE cars
ALTER COLUMN year TYPE VARCHAR(4);

Result

ALTER TABLE

Note: Some data types cannot be converted if the column has value. E.g. numbers can always be converted to text, but text cannot always be converted to numbers.

Change Maximum Allowed Characters

We also want to change the maximum number of characters allowed in the color column of the cars table.

Use the same syntax as above, use the ALTER COLUMN statement and the TYPE keyword followed by the new data type:

Example

Change the color column from VARCHAR(255) to VARCHAR(30):

ALTER TABLE cars
ALTER COLUMN color TYPE VARCHAR(30);

Result

ALTER TABLE

PostgreSQL Exercises

Test Yourself With Exercises

Exercise:

Write the correct SQL statement to change the data type of the year field from INT to VARCHAR(4):

 cars
 year  VARCHAR(4);

The ALTER TABLE Statement

To remove a column from a table, we have to use the ALTER TABLE statement.

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

The ALTER TABLE statement is also used to add and drop various constraints on an existing table.

DROP COLUMN

We want to remove the column named color from the cars table.

To remove a column, use the DROP COLUMN statement:

Example

Remove the color column:

ALTER TABLE cars
DROP COLUMN color;

Result

ALTER TABLE

Display Table

To check the result we can display the table with this SQL statement:

Example

SELECT * FROM cars;

As you can see in the result, the color column has been removed from the cars table.

PostgreSQL Exercises

Test Yourself With Exercises

Exercise:

Write the correct SQL statement to remove a column from a table.

Remove the column year from the table cars:

 cars
 year;

The DELETE Statement

The DELETE statement is used to delete existing records in a table.

Note: Be careful when deleting records in a table! Notice the WHERE clause in the DELETE statement. The WHERE clause specifies which record(s) should be deleted.

If you omit the WHERE clause,
all records in the table will be deleted!.

To delete the record(s) where brand is 'Volvo', use this statement:

Example

Delete all records where brand is 'Volvo':

DELETE FROM cars
WHERE brand = 'Volvo';

Result

DELETE 1

Which means that 1 row was deleted.

Display Table

To check the result we can display the table with this SQL statement:

Example

SELECT * FROM cars;

-

Delete All Records

It is possible to delete all rows in a table without deleting the table. This means that the table structure, attributes, and indexes will be intact.

The following SQL statement deletes all rows in the cars table, without deleting the table:

Example

Delete all records in the cars table:

DELETE FROM cars;

Result

DELETE 3

Which means that all 3 rows were deleted.

Display Table

To check the result we can display the table with this SQL statement:

Example

SELECT * FROM cars;

-

TRUNCATE TABLE

Because we omit the WHERE clause in the DELETE statement above, all records will be deleted from the cars table.

The same would have been achieved by using the TRUNCATE TABLE statement:

Example

Delete all records in the cars table:

TRUNCATE TABLE cars;

Result

TRUNCATE TABLE

Display Table

To check the result we can display the table with this SQL statement:

Example

SELECT * FROM cars;

-

 

The DROP TABLE Statement

The DROP TABLE statement is used to drop an existing table in a database.

Note: Be careful before dropping a table. Deleting a table will result in loss of all information stored in the table!

The following SQL statement drops the existing table cars:

Example

Delete the cars table:

DROP TABLE cars;

Result

DROP TABLE

Display Table

To check the result we can display the table with this SQL statement:

Example

SELECT * FROM cars;

Which will result in an error, because the cars table no longer exists:

Result

ERROR: relation "cars" does not exist
LINE 1: SELECT * FROM cars;
                      ^

PostgreSQL Exercises

Test Yourself With Exercises

Exercise:

Write the correct SQL statment to delete the entire table cars:

 cars;

Demo Database

Up until now in this tutorial we have worked with a very small and simple table in our PostgreSQL database.

Now we want to create more tables with more content to be able to demonstrate more database features.

We will create these 6 tables in our PostgreSQL database:







Below we have listed all the SQL statements you need to create those tables, with content.

You are not required to create the tables on your own system to continue with this tutorial, but you might better understand how PostgreSQL and SQL statements work.

Make sure you are connected to the database. If not, follow the steps in the chapter of this tutorial.

Once you are connected, you are ready to write SQL statements!

CATEGORIES

The following SQL statement will create a table named categories:

CREATE TABLE categories

CREATE TABLE categories (
  category_id SERIAL NOT NULL PRIMARY KEY,
  category_name VARCHAR(255),
  description VARCHAR(255)
);

Result

CREATE TABLE

The following SQL statement will fill the categories table with content:

INSERT INTO categories

INSERT INTO categories (category_name, description)
VALUES
  ('Beverages', 'Soft drinks, coffees, teas, beers, and ales'),
  ('Condiments', 'Sweet and savory sauces, relishes, spreads, and seasonings'),
  ('Confections', 'Desserts, candies, and sweet breads'),
  ('Dairy Products', 'Cheeses'),
  ('Grains/Cereals', 'Breads, crackers, pasta, and cereal'),
  ('Meat/Poultry', 'Prepared meats'),
  ('Produce', 'Dried fruit and bean curd'),
  ('Seafood', 'Seaweed and fish');

Result

INSERT 0 8

The result can also be seen here:

CUSTOMERS

The following SQL statement will create a table named customers:

CREATE TABLE customers

CREATE TABLE customers (
  customer_id SERIAL NOT NULL PRIMARY KEY,
  customer_name VARCHAR(255),
  contact_name VARCHAR(255),
  address VARCHAR(255),
  city VARCHAR(255),
  postal_code VARCHAR(255),
  country VARCHAR(255)
);

Result

CREATE TABLE

The following SQL statement will fill the customers table with content:

INSERT INTO customers

INSERT INTO customers (customer_name, contact_name, address, city, postal_code, country)
VALUES
  ('Alfreds Futterkiste', 'Maria Anders', 'Obere Str. 57', 'Berlin', '12209', 'Germany'),
  ('Ana Trujillo Emparedados y helados', 'Ana Trujillo', 'Avda. de la Constitucion 2222', 'Mexico D.F.', '05021', 'Mexico'),
  ('Antonio Moreno Taquera', 'Antonio Moreno', 'Mataderos 2312', 'Mexico D.F.', '05023', 'Mexico'),
  ('Around the Horn', 'Thomas Hardy', '120 Hanover Sq.', 'London', 'WA1 1DP', 'UK'),
  ('Berglunds snabbkoep', 'Christina Berglund', 'Berguvsvegen 8', 'Lulea', 'S-958 22', 'Sweden'),
  ('Blauer See Delikatessen', 'Hanna Moos', 'Forsterstr. 57', 'Mannheim', '68306', 'Germany'),
  ('Blondel pere et fils', 'Frederique Citeaux', '24, place Kleber', 'Strasbourg', '67000', 'France'),
  ('Bolido Comidas preparadas', 'Martin Sommer', 'C/ Araquil, 67', 'Madrid', '28023', 'Spain'),
  ('Bon app', 'Laurence Lebihans', '12, rue des Bouchers', 'Marseille', '13008', 'France'),
  ('Bottom-Dollar Marketse', 'Elizabeth Lincoln', '23 Tsawassen Blvd.', 'Tsawassen', 'T2F 8M4', 'Canada'),
  ('Bs Beverages', 'Victoria Ashworth', 'Fauntleroy Circus', 'London', 'EC2 5NT', 'UK'),
  ('Cactus Comidas para llevar', 'Patricio Simpson', 'Cerrito 333', 'Buenos Aires', '1010', 'Argentina'),
  ('Centro comercial Moctezuma', 'Francisco Chang', 'Sierras de Granada 9993', 'Mexico D.F.', '05022', 'Mexico'),
  ('Chop-suey Chinese', 'Yang Wang', 'Hauptstr. 29', 'Bern', '3012', 'Switzerland'),
  ('Comercio Mineiro', 'Pedro Afonso', 'Av. dos Lusiadas, 23', 'Sao Paulo', '05432-043', 'Brazil'),
  ('Consolidated Holdings', 'Elizabeth Brown', 'Berkeley Gardens 12 Brewery ', 'London', 'WX1 6LT', 'UK'),
  ('Drachenblut Delikatessend', 'Sven Ottlieb', 'Walserweg 21', 'Aachen', '52066', 'Germany'),
  ('Du monde entier', 'Janine Labrune', '67, rue des Cinquante Otages', 'Nantes', '44000', 'France'),
  ('Eastern Connection', 'Ann Devon', '35 King George', 'London', 'WX3 6FW', 'UK'),
  ('Ernst Handel', 'Roland Mendel', 'Kirchgasse 6', 'Graz', '8010', 'Austria'),
  ('Familia Arquibaldo', 'Aria Cruz', 'Rua Oros, 92', 'Sao Paulo', '05442-030', 'Brazil'),
  ('FISSA Fabrica Inter. Salchichas S.A.', 'Diego Roel', 'C/ Moralzarzal, 86', 'Madrid', '28034', 'Spain'),
  ('Folies gourmandes', 'Martine Rance', '184, chaussee de Tournai', 'Lille', '59000', 'France'),
  ('Folk och fe HB', 'Maria Larsson', 'Akergatan 24', 'Brecke', 'S-844 67', 'Sweden'),
  ('Frankenversand', 'Peter Franken', 'Berliner Platz 43', 'Munchen', '80805', 'Germany'),
  ('France restauration', 'Carine Schmitt', '54, rue Royale', 'Nantes', '44000', 'France'),
  ('Franchi S.p.A.', 'Paolo Accorti', 'Via Monte Bianco 34', 'Torino', '10100', 'Italy'),
  ('Furia Bacalhau e Frutos do Mar', 'Lino Rodriguez ', 'Jardim das rosas n. 32', 'Lisboa', '1675', 'Portugal'),
  ('Galeria del gastronomo', 'Eduardo Saavedra', 'Rambla de Cataluna, 23', 'Barcelona', '08022', 'Spain'),
  ('Godos Cocina Tipica', 'Jose Pedro Freyre', 'C/ Romero, 33', 'Sevilla', '41101', 'Spain'),
  ('Gourmet Lanchonetes', 'Andre Fonseca', 'Av. Brasil, 442', 'Campinas', '04876-786', 'Brazil'),
  ('Great Lakes Food Market', 'Howard Snyder', '2732 Baker Blvd.', 'Eugene', '97403', 'USA'),
  ('GROSELLA-Restaurante', 'Manuel Pereira', '5th Ave. Los Palos Grandes', 'Caracas', '1081', 'Venezuela'),
  ('Hanari Carnes', 'Mario Pontes', 'Rua do Paco, 67', 'Rio de Janeiro', '05454-876', 'Brazil'),
  ('HILARION-Abastos', 'Carlos Hernandez', 'Carrera 22 con Ave. Carlos Soublette #8-35', 'San Cristobal', '5022', 'Venezuela'),
  ('Hungry Coyote Import Store', 'Yoshi Latimer', 'City Center Plaza 516 Main St.', 'Elgin', '97827', 'USA'),
  ('Hungry Owl All-Night Grocers', 'Patricia McKenna', '8 Johnstown Road', 'Cork', '', 'Ireland'),
  ('Island Trading', 'Helen Bennett', 'Garden House Crowther Way', 'Cowes', 'PO31 7PJ', 'UK'),
  ('Koniglich Essen', 'Philip Cramer', 'Maubelstr. 90', 'Brandenburg', '14776', 'Germany'),
  ('La corne d abondance', 'Daniel Tonini', '67, avenue de l Europe', 'Versailles', '78000', 'France'),
  ('La maison d Asie', 'Annette Roulet', '1 rue Alsace-Lorraine', 'Toulouse', '31000', 'France'),
  ('Laughing Bacchus Wine Cellars', 'Yoshi Tannamuri', '1900 Oak St.', 'Vancouver', 'V3F 2K1', 'Canada'),
  ('Lazy K Kountry Store', 'John Steel', '12 Orchestra Terrace', 'Walla Walla', '99362', 'USA'),
  ('Lehmanns Marktstand', 'Renate Messner', 'Magazinweg 7', 'Frankfurt a.M. ', '60528', 'Germany'),
  ('Lets Stop N Shop', 'Jaime Yorres', '87 Polk St. Suite 5', 'San Francisco', '94117', 'USA'),
  ('LILA-Supermercado', 'Carlos Gonzalez', 'Carrera 52 con Ave. Bolivar #65-98 Llano Largo', 'Barquisimeto', '3508', 'Venezuela'),
  ('LINO-Delicateses', 'Felipe Izquierdo', 'Ave. 5 de Mayo Porlamar', 'I. de Margarita', '4980', 'Venezuela'),
  ('Lonesome Pine Restaurant', 'Fran Wilson', '89 Chiaroscuro Rd.', 'Portland', '97219', 'USA'),
  ('Magazzini Alimentari Riuniti', 'Giovanni Rovelli', 'Via Ludovico il Moro 22', 'Bergamo', '24100', 'Italy'),
  ('Maison Dewey', 'Catherine Dewey', 'Rue Joseph-Bens 532', 'Bruxelles', 'B-1180', 'Belgium'),
  ('Mere Paillarde', 'Jean Fresniere', '43 rue St. Laurent', 'Montreal', 'H1J 1C3', 'Canada'),
  ('Morgenstern Gesundkost', 'Alexander Feuer', 'Heerstr. 22', 'Leipzig', '04179', 'Germany'),
  ('North/South', 'Simon Crowther', 'South House 300 Queensbridge', 'London', 'SW7 1RZ', 'UK'),
  ('Oceano Atlantico Ltda.', 'Yvonne Moncada', 'Ing. Gustavo Moncada 8585 Piso 20-A', 'Buenos Aires', '1010', 'Argentina'),
  ('Old World Delicatessen', 'Rene Phillips', '2743 Bering St.', 'Anchorage', '99508', 'USA'),
  ('Ottilies Keseladen', 'Henriette Pfalzheim', 'Mehrheimerstr. 369', 'Koln', '50739', 'Germany'),
  ('Paris specialites', 'Marie Bertrand', '265, boulevard Charonne', 'Paris', '75012', 'France'),
  ('Pericles Comidas clasicas', 'Guillermo Fernandez', 'Calle Dr. Jorge Cash 321', 'Mexico D.F.', '05033', 'Mexico'),
  ('Piccolo und mehr', 'Georg Pipps', 'Geislweg 14', 'Salzburg', '5020', 'Austria'),
  ('Princesa Isabel Vinhoss', 'Isabel de Castro', 'Estrada da saude n. 58', 'Lisboa', '1756', 'Portugal'),
  ('Que Delicia', 'Bernardo Batista', 'Rua da Panificadora, 12', 'Rio de Janeiro', '02389-673', 'Brazil'),
  ('Queen Cozinha', 'Lucia Carvalho', 'Alameda dos Canarios, 891', 'Sao Paulo', '05487-020', 'Brazil'),
  ('QUICK-Stop', 'Horst Kloss', 'Taucherstrasse 10', 'Cunewalde', '01307', 'Germany'),
  ('Rancho grande', 'Sergio Gutiarrez', 'Av. del Libertador 900', 'Buenos Aires', '1010', 'Argentina'),
  ('Rattlesnake Canyon Grocery', 'Paula Wilson', '2817 Milton Dr.', 'Albuquerque', '87110', 'USA'),
  ('Reggiani Caseifici', 'Maurizio Moroni', 'Strada Provinciale 124', 'Reggio Emilia', '42100', 'Italy'),
  ('Ricardo Adocicados', 'Janete Limeira', 'Av. Copacabana, 267', 'Rio de Janeiro', '02389-890', 'Brazil'),
  ('Richter Supermarkt', 'Michael Holz', 'Grenzacherweg 237', 'Genève', '1203', 'Switzerland'),
  ('Romero y tomillo', 'Alejandra Camino', 'Gran Via, 1', 'Madrid', '28001', 'Spain'),
  ('Santa Gourmet', 'Jonas Bergulfsen', 'Erling Skakkes gate 78', 'Stavern', '4110', 'Norway'),
  ('Save-a-lot Markets', 'Jose Pavarotti', '187 Suffolk Ln.', 'Boise', '83720', 'USA'),
  ('Seven Seas Imports', 'Hari Kumar', '90 Wadhurst Rd.', 'London', 'OX15 4NB', 'UK'),
  ('Simons bistro', 'Jytte Petersen', 'Vinbeltet 34', 'Kobenhavn', '1734', 'Denmark'),
  ('Specialites du monde', 'Dominique Perrier', '25, rue Lauriston', 'Paris', '75016', 'France'),
  ('Split Rail Beer & Ale', 'Art Braunschweiger', 'P.O. Box 555', 'Lander', '82520', 'USA'),
  ('Supremes delices', 'Pascale Cartrain', 'Boulevard Tirou, 255', 'Charleroi', 'B-6000', 'Belgium'),
  ('The Big Cheese', 'Liz Nixon', '89 Jefferson Way Suite 2', 'Portland', '97201', 'USA'),
  ('The Cracker Box', 'Liu Wong', '55 Grizzly Peak Rd.', 'Butte', '59801', 'USA'),
  ('Toms Spezialiteten', 'Karin Josephs', 'Luisenstr. 48', 'Manster', '44087', 'Germany'),
  ('Tortuga Restaurante', 'Miguel Angel Paolino', 'Avda. Azteca 123', 'Mexico D.F.', '05033', 'Mexico'),
  ('Tradicao Hipermercados', 'Anabela Domingues', 'Av. Ines de Castro, 414', 'Sao Paulo', '05634-030', 'Brazil'),
  ('Trails Head Gourmet Provisioners', 'Helvetius Nagy', '722 DaVinci Blvd.', 'Kirkland', '98034', 'USA'),
  ('Vaffeljernet', 'Palle Ibsen', 'Smagsloget 45', 'Arhus', '8200', 'Denmark'),
  ('Victuailles en stock', 'Mary Saveley', '2, rue du Commerce', 'Lyon', '69004', 'France'),
  ('Vins et alcools Chevalier', 'Paul Henriot', '59 rue de l Abbaye', 'Reims', '51100', 'France'),
  ('Die Wandernde Kuh', 'Rita Moller', 'Adenauerallee 900', 'Stuttgart', '70563', 'Germany'),
  ('Wartian Herkku', 'Pirkko Koskitalo', 'Torikatu 38', 'Oulu', '90110', 'Finland'),
  ('Wellington Importadora', 'Paula Parente', 'Rua do Mercado, 12', 'Resende', '08737-363', 'Brazil'),
  ('White Clover Markets', 'Karl Jablonski', '305 - 14th Ave. S. Suite 3B', 'Seattle', '98128', 'USA'),
  ('Wilman Kala', 'Matti Karttunen', 'Keskuskatu 45', 'Helsinki', '21240', 'Finland'),
  ('Wolski', 'Zbyszek', 'ul. Filtrowa 68', 'Walla', '01-012', 'Poland');

Result

INSERT 0 91

The result can also be seen here:

PRODUCTS

The following SQL statement will create a table named products:

CREATE TABLE products

CREATE TABLE products (
  product_id SERIAL NOT NULL PRIMARY KEY,
  product_name VARCHAR(255),
  category_id INT,
  unit VARCHAR(255),
  price DECIMAL(10, 2)
);

Result

CREATE TABLE

The following SQL statement will fill the products table with content:

INSERT INTO products

INSERT INTO products (product_id, product_name, category_id, unit, price)
VALUES
  (1, 'Chais', 1, '10 boxes x 20 bags', 18),
  (2, 'Chang', 1, '24 - 12 oz bottles', 19),
  (3, 'Aniseed Syrup', 2, '12 - 550 ml bottles', 10),
  (4, 'Chef Antons Cajun Seasoning', 2, '48 - 6 oz jars', 22),
  (5, 'Chef Antons Gumbo Mix', 2, '36 boxes', 21.35),
  (6, 'Grandmas Boysenberry Spread', 2, '12 - 8 oz jars', 25),
  (7, 'Uncle Bobs Organic Dried Pears', 7, '12 - 1 lb pkgs.', 30),
  (8, 'Northwoods Cranberry Sauce', 2, '12 - 12 oz jars', 40),
  (9, 'Mishi Kobe Niku', 6, '18 - 500 g pkgs.', 97),
  (10, 'Ikura', 8, '12 - 200 ml jars', 31),
  (11, 'Queso Cabrales', 4, '1 kg pkg.', 21),
  (12, 'Queso Manchego La Pastora', 4, '10 - 500 g pkgs.', 38),
  (13, 'Konbu', 8, '2 kg box', 6),
  (14, 'Tofu', 7, '40 - 100 g pkgs.', 23.25),
  (15, 'Genen Shouyu', 2, '24 - 250 ml bottles', 15.5),
  (16, 'Pavlova', 3, '32 - 500 g boxes', 17.45),
  (17, 'Alice Mutton', 6, '20 - 1 kg tins', 39),
  (18, 'Carnarvon Tigers', 8, '16 kg pkg.', 62.5),
  (19, 'Teatime Chocolate Biscuits', 3, '10 boxes x 12 pieces', 9.2),
  (20, 'Sir Rodneys Marmalade', 3, '30 gift boxes', 81),
  (21, 'Sir Rodneys Scones', 3, '24 pkgs. x 4 pieces', 10),
  (22, 'Gustafs Kneckebrod', 5, '24 - 500 g pkgs.', 21),
  (23, 'Tunnbrod', 5, '12 - 250 g pkgs.', 9),
  (24, 'Guarani Fantastica', 1, '12 - 355 ml cans', 4.5),
  (25, 'NuNuCa Nui-Nougat-Creme', 3, '20 - 450 g glasses', 14),
  (26, 'Gumber Gummiberchen', 3, '100 - 250 g bags', 31.23),
  (27, 'Schoggi Schokolade', 3, '100 - 100 g pieces', 43.9),
  (28, 'Rassle Sauerkraut', 7, '25 - 825 g cans', 45.6),
  (29, 'Thoringer Rostbratwurst', 6, '50 bags x 30 sausgs.', 123.79),
  (30, 'Nord-Ost Matjeshering', 8, '10 - 200 g glasses', 25.89),
  (31, 'Gorgonzola Telino', 4, '12 - 100 g pkgs', 12.5),
  (32, 'Mascarpone Fabioli', 4, '24 - 200 g pkgs.', 32),
  (33, 'Geitost', 4, '500 g', 2.5),
  (34, 'Sasquatch Ale', 1, '24 - 12 oz bottles', 14),
  (35, 'Steeleye Stout', 1, '24 - 12 oz bottles', 18),
  (36, 'Inlagd Sill', 8, '24 - 250 g jars', 19),
  (37, 'Gravad lax', 8, '12 - 500 g pkgs.', 26),
  (38, 'Cote de Blaye', 1, '12 - 75 cl bottles', 263.5),
  (39, 'Chartreuse verte', 1, '750 cc per bottle', 18),
  (40, 'Boston Crab Meat', 8, '24 - 4 oz tins', 18.4),
  (41, 'Jacks New England Clam Chowder', 8, '12 - 12 oz cans', 9.65),
  (42, 'Singaporean Hokkien Fried Mee', 5, '32 - 1 kg pkgs.', 14),
  (43, 'Ipoh Coffee', 1, '16 - 500 g tins', 46),
  (44, 'Gula Malacca', 2, '20 - 2 kg bags', 19.45),
  (45, 'Rogede sild', 8, '1k pkg.', 9.5),
  (46, 'Spegesild', 8, '4 - 450 g glasses', 12),
  (47, 'Zaanse koeken', 3, '10 - 4 oz boxes', 9.5),
  (48, 'Chocolade', 3, '10 pkgs.', 12.75),
  (49, 'Maxilaku', 3, '24 - 50 g pkgs.', 20),
  (50, 'Valkoinen suklaa', 3, '12 - 100 g bars', 16.25),
  (51, 'Manjimup Dried Apples', 7, '50 - 300 g pkgs.', 53),
  (52, 'Filo Mix', 5, '16 - 2 kg boxes', 7),
  (53, 'Perth Pasties', 6, '48 pieces', 32.8),
  (54, 'Tourtiare', 6, '16 pies', 7.45),
  (55, 'Pate chinois', 6, '24 boxes x 2 pies', 24),
  (56, 'Gnocchi di nonna Alice', 5, '24 - 250 g pkgs.', 38),
  (57, 'Ravioli Angelo', 5, '24 - 250 g pkgs.', 19.5),
  (58, 'Escargots de Bourgogne', 8, '24 pieces', 13.25),
  (59, 'Raclette Courdavault', 4, '5 kg pkg.', 55),
  (60, 'Camembert Pierrot', 4, '15 - 300 g rounds', 34),
  (61, 'Sirop d arable', 2, '24 - 500 ml bottles', 28.5),
  (62, 'Tarte au sucre', 3, '48 pies', 49.3),
  (63, 'Vegie-spread', 2, '15 - 625 g jars', 43.9),
  (64, 'Wimmers gute Semmelknadel', 5, '20 bags x 4 pieces', 33.25),
  (65, 'Louisiana Fiery Hot Pepper Sauce', 2, '32 - 8 oz bottles', 21.05),
  (66, 'Louisiana Hot Spiced Okra', 2, '24 - 8 oz jars', 17),
  (67, 'Laughing Lumberjack Lager', 1, '24 - 12 oz bottles', 14),
  (68, 'Scottish Longbreads', 3, '10 boxes x 8 pieces', 12.5),
  (69, 'Gudbrandsdalsost', 4, '10 kg pkg.', 36),
  (70, 'Outback Lager', 1, '24 - 355 ml bottles', 15),
  (71, 'Flotemysost', 4, '10 - 500 g pkgs.', 21.5),
  (72, 'Mozzarella di Giovanni', 4, '24 - 200 g pkgs.', 34.8),
  (73, 'Red Kaviar', 8, '24 - 150 g jars', 15),
  (74, 'Longlife Tofu', 7, '5 kg pkg.', 10),
  (75, 'Rhenbreu Klosterbier', 1, '24 - 0.5 l bottles', 7.75),
  (76, 'Lakkalikeeri', 1, '500 ml ', 18),
  (77, 'Original Frankfurter gr�ne Soae', 2, '12 boxes', 13);

Result

INSERT 0 77

The result can also be seen here:

ORDERS

The following SQL statement will create a table named orders:

CREATE TABLE orders

CREATE TABLE orders (
  order_id SERIAL NOT NULL PRIMARY KEY,
  customer_id INT,
  order_date DATE
);

Result

CREATE TABLE

The following SQL statement will fill the orders table with content:

INSERT INTO orders

INSERT INTO orders (order_id, customer_id, order_date)
VALUES
  (10248, 90, '2021-07-04'),
  (10249, 81, '2021-07-05'),
  (10250, 34, '2021-07-08'),
  (10251, 84, '2021-07-08'),
  (10252, 76, '2021-07-09'),
  (10253, 34, '2021-07-10'),
  (10254, 14, '2021-07-11'),
  (10255, 68, '2021-07-12'),
  (10256, 88, '2021-07-15'),
  (10257, 35, '2021-07-16'),
  (10258, 20, '2021-07-17'),
  (10259, 13, '2021-07-18'),
  (10260, 55, '2021-07-19'),
  (10261, 61, '2021-07-19'),
  (10262, 65, '2021-07-22'),
  (10263, 20, '2021-07-23'),
  (10264, 24, '2021-07-24'),
  (10265, 7, '2021-07-25'),
  (10266, 87, '2021-07-26'),
  (10267, 25, '2021-07-29'),
  (10268, 33, '2021-07-30'),
  (10269, 89, '2021-07-31'),
  (10270, 87, '2021-08-01'),
  (10271, 75, '2021-08-01'),
  (10272, 65, '2021-08-02'),
  (10273, 63, '2021-08-05'),
  (10274, 85, '2021-08-06'),
  (10275, 49, '2021-08-07'),
  (10276, 80, '2021-08-08'),
  (10277, 52, '2021-08-09'),
  (10278, 5, '2021-08-12'),
  (10279, 44, '2021-08-13'),
  (10280, 5, '2021-08-14'),
  (10281, 69, '2021-08-14'),
  (10282, 69, '2021-08-15'),
  (10283, 46, '2021-08-16'),
  (10284, 44, '2021-08-19'),
  (10285, 63, '2021-08-20'),
  (10286, 63, '2021-08-21'),
  (10287, 67, '2021-08-22'),
  (10288, 66, '2021-08-23'),
  (10289, 11, '2021-08-26'),
  (10290, 15, '2021-08-27'),
  (10291, 61, '2021-08-27'),
  (10292, 81, '2021-08-28'),
  (10293, 80, '2021-08-29'),
  (10294, 65, '2021-08-30'),
  (10295, 85, '2021-09-02'),
  (10296, 46, '2021-09-03'),
  (10297, 7, '2021-09-04'),
  (10298, 37, '2021-09-05'),
  (10299, 67, '2021-09-06'),
  (10300, 49, '2021-09-09'),
  (10301, 86, '2021-09-09'),
  (10302, 76, '2021-09-10'),
  (10303, 30, '2021-09-11'),
  (10304, 80, '2021-09-12'),
  (10305, 55, '2021-09-13'),
  (10306, 69, '2021-09-16'),
  (10307, 48, '2021-09-17'),
  (10308, 2, '2021-09-18'),
  (10309, 37, '2021-09-19'),
  (10310, 77, '2021-09-20'),
  (10311, 18, '2021-09-20'),
  (10312, 86, '2021-09-23'),
  (10313, 63, '2021-09-24'),
  (10314, 65, '2021-09-25'),
  (10315, 38, '2021-09-26'),
  (10316, 65, '2021-09-27'),
  (10317, 48, '2021-09-30'),
  (10318, 38, '2021-10-01'),
  (10319, 80, '2021-10-02'),
  (10320, 87, '2021-10-03'),
  (10321, 38, '2021-10-03'),
  (10322, 58, '2021-10-04'),
  (10323, 39, '2021-10-07'),
  (10324, 71, '2021-10-08'),
  (10325, 39, '2021-10-09'),
  (10326, 8, '2021-10-10'),
  (10327, 24, '2021-10-11'),
  (10328, 28, '2021-10-14'),
  (10329, 75, '2021-10-15'),
  (10330, 46, '2021-10-16'),
  (10331, 9, '2021-10-16'),
  (10332, 51, '2021-10-17'),
  (10333, 87, '2021-10-18'),
  (10334, 84, '2021-10-21'),
  (10335, 37, '2021-10-22'),
  (10336, 60, '2021-10-23'),
  (10337, 25, '2021-10-24'),
  (10338, 55, '2021-10-25'),
  (10339, 51, '2021-10-28'),
  (10340, 9, '2021-10-29'),
  (10341, 73, '2021-10-29'),
  (10342, 25, '2021-10-30'),
  (10343, 44, '2021-10-31'),
  (10344, 89, '2021-11-01'),
  (10345, 63, '2021-11-04'),
  (10346, 65, '2021-11-05'),
  (10347, 21, '2021-11-06'),
  (10348, 86, '2021-11-07'),
  (10349, 75, '2021-11-08'),
  (10350, 41, '2021-11-11'),
  (10351, 20, '2021-11-11'),
  (10352, 28, '2021-11-12'),
  (10353, 59, '2021-11-13'),
  (10354, 58, '2021-11-14'),
  (10355, 4, '2021-11-15'),
  (10356, 86, '2021-11-18'),
  (10357, 46, '2021-11-19'),
  (10358, 41, '2021-11-20'),
  (10359, 72, '2021-11-21'),
  (10360, 7, '2021-11-22'),
  (10361, 63, '2021-11-22'),
  (10362, 9, '2021-11-25'),
  (10363, 17, '2021-11-26'),
  (10364, 19, '2021-11-26'),
  (10365, 3, '2021-11-27'),
  (10366, 29, '2021-11-28'),
  (10367, 83, '2021-11-28'),
  (10368, 20, '2021-11-29'),
  (10369, 75, '2021-12-02'),
  (10370, 14, '2021-12-03'),
  (10371, 41, '2021-12-03'),
  (10372, 62, '2021-12-04'),
  (10373, 37, '2021-12-05'),
  (10374, 91, '2021-12-05'),
  (10375, 36, '2021-12-06'),
  (10376, 51, '2021-12-09'),
  (10377, 72, '2021-12-09'),
  (10378, 24, '2021-12-10'),
  (10379, 61, '2021-12-11'),
  (10380, 37, '2021-12-12'),
  (10381, 46, '2021-12-12'),
  (10382, 20, '2021-12-13'),
  (10383, 4, '2021-12-16'),
  (10384, 5, '2021-12-16'),
  (10385, 75, '2021-12-17'),
  (10386, 21, '2021-12-18'),
  (10387, 70, '2021-12-18'),
  (10388, 72, '2021-12-19'),
  (10389, 10, '2021-12-20'),
  (10390, 20, '2021-12-23'),
  (10391, 17, '2021-12-23'),
  (10392, 59, '2021-12-24'),
  (10393, 71, '2021-12-25'),
  (10394, 36, '2021-12-25'),
  (10395, 35, '2021-12-26'),
  (10396, 25, '2021-12-27'),
  (10397, 60, '2021-12-27'),
  (10398, 71, '2021-12-30'),
  (10399, 83, '2021-12-31'),
  (10400, 19, '2022-01-01'),
  (10401, 65, '2022-01-01'),
  (10402, 20, '2022-01-02'),
  (10403, 20, '2022-01-03'),
  (10404, 49, '2022-01-03'),
  (10405, 47, '2022-01-06'),
  (10406, 62, '2022-01-07'),
  (10407, 56, '2022-01-07'),
  (10408, 23, '2022-01-08'),
  (10409, 54, '2022-01-09'),
  (10410, 10, '2022-01-10'),
  (10411, 10, '2022-01-10'),
  (10412, 87, '2022-01-13'),
  (10413, 41, '2022-01-14'),
  (10414, 21, '2022-01-14'),
  (10415, 36, '2022-01-15'),
  (10416, 87, '2022-01-16'),
  (10417, 73, '2022-01-16'),
  (10418, 63, '2022-01-17'),
  (10419, 68, '2022-01-20'),
  (10420, 88, '2022-01-21'),
  (10421, 61, '2022-01-21'),
  (10422, 27, '2022-01-22'),
  (10423, 31, '2022-01-23'),
  (10424, 51, '2022-01-23'),
  (10425, 41, '2022-01-24'),
  (10426, 29, '2022-01-27'),
  (10427, 59, '2022-01-27'),
  (10428, 66, '2022-01-28'),
  (10429, 37, '2022-01-29'),
  (10430, 20, '2022-01-30'),
  (10431, 10, '2022-01-30'),
  (10432, 75, '2022-01-31'),
  (10433, 60, '2022-02-03'),
  (10434, 24, '2022-02-03'),
  (10435, 16, '2022-02-04'),
  (10436, 7, '2022-02-05'),
  (10437, 87, '2022-02-05'),
  (10438, 79, '2022-02-06'),
  (10439, 51, '2022-02-07'),
  (10440, 71, '2022-02-10'),
  (10441, 55, '2022-02-10'),
  (10442, 20, '2022-02-11'),
  (10443, 66, '2022-02-12'),
  (10444, 5, '2022-02-12'),
  (10445, 5, '2022-02-13'),
  (10446, 79, '2022-02-14'),
  (10447, 67, '2022-02-14'),
  (10448, 64, '2022-02-17'),
  (10449, 7, '2022-02-18'),
  (10450, 84, '2022-02-19'),
  (10451, 63, '2022-02-19'),
  (10452, 71, '2022-02-20'),
  (10453, 4, '2022-02-21'),
  (10454, 41, '2022-02-21'),
  (10455, 87, '2022-02-24'),
  (10456, 39, '2022-02-25'),
  (10457, 39, '2022-02-25'),
  (10458, 76, '2022-02-26'),
  (10459, 84, '2022-02-27'),
  (10460, 24, '2022-02-28'),
  (10461, 46, '2022-02-28'),
  (10462, 16, '2022-03-03'),
  (10463, 76, '2022-03-04'),
  (10464, 28, '2022-03-04'),
  (10465, 83, '2022-03-05'),
  (10466, 15, '2022-03-06'),
  (10467, 49, '2022-03-06'),
  (10468, 39, '2022-03-07'),
  (10469, 89, '2022-03-10'),
  (10470, 9, '2022-03-11'),
  (10471, 11, '2022-03-11'),
  (10472, 72, '2022-03-12'),
  (10473, 38, '2022-03-13'),
  (10474, 58, '2022-03-13'),
  (10475, 76, '2022-03-14'),
  (10476, 35, '2022-03-17'),
  (10477, 60, '2022-03-17'),
  (10478, 84, '2022-03-18'),
  (10479, 65, '2022-03-19'),
  (10480, 23, '2022-03-20'),
  (10481, 67, '2022-03-20'),
  (10482, 43, '2022-03-21'),
  (10483, 89, '2022-03-24'),
  (10484, 11, '2022-03-24'),
  (10485, 47, '2022-03-25'),
  (10486, 35, '2022-03-26'),
  (10487, 62, '2022-03-26'),
  (10488, 25, '2022-03-27'),
  (10489, 59, '2022-03-28'),
  (10490, 35, '2022-03-31'),
  (10491, 28, '2022-03-31'),
  (10492, 10, '2022-04-01'),
  (10493, 41, '2022-04-02'),
  (10494, 15, '2022-04-02'),
  (10495, 42, '2022-04-03'),
  (10496, 81, '2022-04-04'),
  (10497, 44, '2022-04-04'),
  (10498, 35, '2022-04-07'),
  (10499, 46, '2022-04-08'),
  (10500, 41, '2022-04-09'),
  (10501, 6, '2022-04-09'),
  (10502, 58, '2022-04-10'),
  (10503, 37, '2022-04-11'),
  (10504, 89, '2022-04-11'),
  (10505, 51, '2022-04-14'),
  (10506, 39, '2022-04-15'),
  (10507, 3, '2022-04-15'),
  (10508, 56, '2022-04-16'),
  (10509, 6, '2022-04-17'),
  (10510, 71, '2022-04-18'),
  (10511, 9, '2022-04-18'),
  (10512, 21, '2022-04-21'),
  (10513, 86, '2022-04-22'),
  (10514, 20, '2022-04-22'),
  (10515, 63, '2022-04-23'),
  (10516, 37, '2022-04-24'),
  (10517, 53, '2022-04-24'),
  (10518, 80, '2022-04-25'),
  (10519, 14, '2022-04-28'),
  (10520, 70, '2022-04-29'),
  (10521, 12, '2022-04-29'),
  (10522, 44, '2022-04-30'),
  (10523, 72, '2022-05-01'),
  (10524, 5, '2022-05-01'),
  (10525, 9, '2022-05-02'),
  (10526, 87, '2022-05-05'),
  (10527, 63, '2022-05-05'),
  (10528, 32, '2022-05-06'),
  (10529, 50, '2022-05-07'),
  (10530, 59, '2022-05-08'),
  (10531, 54, '2022-05-08'),
  (10532, 19, '2022-05-09'),
  (10533, 24, '2022-05-12'),
  (10534, 44, '2022-05-12'),
  (10535, 3, '2022-05-13'),
  (10536, 44, '2022-05-14'),
  (10537, 68, '2022-05-14'),
  (10538, 11, '2022-05-15'),
  (10539, 11, '2022-05-16'),
  (10540, 63, '2022-05-19'),
  (10541, 34, '2022-05-19'),
  (10542, 39, '2022-05-20'),
  (10543, 46, '2022-05-21'),
  (10544, 48, '2022-05-21'),
  (10545, 43, '2022-05-22'),
  (10546, 84, '2022-05-23'),
  (10547, 72, '2022-05-23'),
  (10548, 79, '2022-05-26'),
  (10549, 63, '2022-05-27'),
  (10550, 30, '2022-05-28'),
  (10551, 28, '2022-05-28'),
  (10552, 35, '2022-05-29'),
  (10553, 87, '2022-05-30'),
  (10554, 56, '2022-05-30'),
  (10555, 71, '2022-06-02'),
  (10556, 73, '2022-06-03'),
  (10557, 44, '2022-06-03'),
  (10558, 4, '2022-06-04'),
  (10559, 7, '2022-06-05'),
  (10560, 25, '2022-06-06'),
  (10561, 24, '2022-06-06'),
  (10562, 66, '2022-06-09'),
  (10563, 67, '2022-06-10'),
  (10564, 65, '2022-06-10'),
  (10565, 51, '2022-06-11'),
  (10566, 7, '2022-06-12'),
  (10567, 37, '2022-06-12'),
  (10568, 29, '2022-06-13'),
  (10569, 65, '2022-06-16'),
  (10570, 51, '2022-06-17'),
  (10571, 20, '2022-06-17'),
  (10572, 5, '2022-06-18'),
  (10573, 3, '2022-06-19'),
  (10574, 82, '2022-06-19'),
  (10575, 52, '2022-06-20'),
  (10576, 80, '2022-06-23'),
  (10577, 82, '2022-06-23'),
  (10578, 11, '2022-06-24'),
  (10579, 45, '2022-06-25'),
  (10580, 56, '2022-06-26'),
  (10581, 21, '2022-06-26'),
  (10582, 6, '2022-06-27'),
  (10583, 87, '2022-06-30'),
  (10584, 7, '2022-06-30'),
  (10585, 88, '2022-07-01'),
  (10586, 66, '2022-07-02'),
  (10587, 61, '2022-07-02'),
  (10588, 63, '2022-07-03'),
  (10589, 32, '2022-07-04'),
  (10590, 51, '2022-07-07'),
  (10591, 83, '2022-07-07'),
  (10592, 44, '2022-07-08'),
  (10593, 44, '2022-07-09'),
  (10594, 55, '2022-07-09'),
  (10595, 20, '2022-07-10'),
  (10596, 89, '2022-07-11'),
  (10597, 59, '2022-07-11'),
  (10598, 65, '2022-07-14'),
  (10599, 11, '2022-07-15'),
  (10600, 36, '2022-07-16'),
  (10601, 35, '2022-07-16'),
  (10602, 83, '2022-07-17'),
  (10603, 71, '2022-07-18'),
  (10604, 28, '2022-07-18'),
  (10605, 51, '2022-07-21'),
  (10606, 81, '2022-07-22'),
  (10607, 71, '2022-07-22'),
  (10608, 79, '2022-07-23'),
  (10609, 18, '2022-07-24'),
  (10610, 41, '2022-07-25'),
  (10611, 91, '2022-07-25'),
  (10612, 71, '2022-07-28'),
  (10613, 35, '2022-07-29'),
  (10614, 6, '2022-07-29'),
  (10615, 90, '2022-07-30'),
  (10616, 32, '2022-07-31'),
  (10617, 32, '2022-07-31'),
  (10618, 51, '2022-08-01'),
  (10619, 51, '2022-08-04'),
  (10620, 42, '2022-08-05'),
  (10621, 38, '2022-08-05'),
  (10622, 67, '2022-08-06'),
  (10623, 25, '2022-08-07'),
  (10624, 78, '2022-08-07'),
  (10625, 2, '2022-08-08'),
  (10626, 5, '2022-08-11'),
  (10627, 71, '2022-08-11'),
  (10628, 7, '2022-08-12'),
  (10629, 30, '2022-08-12'),
  (10630, 39, '2022-08-13'),
  (10631, 41, '2022-08-14'),
  (10632, 86, '2022-08-14'),
  (10633, 20, '2022-08-15'),
  (10634, 23, '2022-08-15'),
  (10635, 49, '2022-08-18'),
  (10636, 87, '2022-08-19'),
  (10637, 62, '2022-08-19'),
  (10638, 47, '2022-08-20'),
  (10639, 70, '2022-08-20'),
  (10640, 86, '2022-08-21'),
  (10641, 35, '2022-08-22'),
  (10642, 73, '2022-08-22'),
  (10643, 1, '2022-08-25'),
  (10644, 88, '2022-08-25'),
  (10645, 34, '2022-08-26'),
  (10646, 37, '2022-08-27'),
  (10647, 61, '2022-08-27'),
  (10648, 67, '2022-08-28'),
  (10649, 50, '2022-08-28'),
  (10650, 21, '2022-08-29'),
  (10651, 86, '2022-09-01'),
  (10652, 31, '2022-09-01'),
  (10653, 25, '2022-09-02'),
  (10654, 5, '2022-09-02'),
  (10655, 66, '2022-09-03'),
  (10656, 32, '2022-09-04'),
  (10657, 71, '2022-09-04'),
  (10658, 63, '2022-09-05'),
  (10659, 62, '2022-09-05'),
  (10660, 36, '2022-09-08'),
  (10661, 37, '2022-09-09'),
  (10662, 48, '2022-09-09'),
  (10663, 9, '2022-09-10'),
  (10664, 28, '2022-09-10'),
  (10665, 48, '2022-09-11'),
  (10666, 68, '2022-09-12'),
  (10667, 20, '2022-09-12'),
  (10668, 86, '2022-09-15'),
  (10669, 73, '2022-09-15'),
  (10670, 25, '2022-09-16'),
  (10671, 26, '2022-09-17'),
  (10672, 5, '2022-09-17'),
  (10673, 90, '2022-09-18'),
  (10674, 38, '2022-09-18'),
  (10675, 25, '2022-09-19'),
  (10676, 80, '2022-09-22'),
  (10677, 3, '2022-09-22'),
  (10678, 71, '2022-09-23'),
  (10679, 7, '2022-09-23'),
  (10680, 55, '2022-09-24'),
  (10681, 32, '2022-09-25'),
  (10682, 3, '2022-09-25'),
  (10683, 18, '2022-09-26'),
  (10684, 56, '2022-09-26'),
  (10685, 31, '2022-09-29'),
  (10686, 59, '2022-09-30'),
  (10687, 37, '2022-09-30'),
  (10688, 83, '2022-10-01'),
  (10689, 5, '2022-10-01'),
  (10690, 34, '2022-10-02'),
  (10691, 63, '2022-10-03'),
  (10692, 1, '2022-10-03'),
  (10693, 89, '2022-10-06'),
  (10694, 63, '2022-10-06'),
  (10695, 90, '2022-10-07'),
  (10696, 89, '2022-10-08'),
  (10697, 47, '2022-10-08'),
  (10698, 20, '2022-10-09'),
  (10699, 52, '2022-10-09'),
  (10700, 71, '2022-10-10'),
  (10701, 37, '2022-10-13'),
  (10702, 1, '2022-10-13'),
  (10703, 24, '2022-10-14'),
  (10704, 62, '2022-10-14'),
  (10705, 35, '2022-10-15'),
  (10706, 55, '2022-10-16'),
  (10707, 4, '2022-10-16'),
  (10708, 77, '2022-10-17'),
  (10709, 31, '2022-10-17'),
  (10710, 27, '2022-10-20'),
  (10711, 71, '2022-10-21'),
  (10712, 37, '2022-10-21'),
  (10713, 71, '2022-10-22'),
  (10714, 71, '2022-10-22'),
  (10715, 9, '2022-10-23'),
  (10716, 64, '2022-10-24'),
  (10717, 25, '2022-10-24'),
  (10718, 39, '2022-10-27'),
  (10719, 45, '2022-10-27'),
  (10720, 61, '2022-10-28'),
  (10721, 63, '2022-10-29'),
  (10722, 71, '2022-10-29'),
  (10723, 89, '2022-10-30'),
  (10724, 51, '2022-10-30'),
  (10725, 21, '2022-10-31'),
  (10726, 19, '2022-11-03'),
  (10727, 66, '2022-11-03'),
  (10728, 62, '2022-11-04'),
  (10729, 47, '2022-11-04'),
  (10730, 9, '2022-11-05'),
  (10731, 14, '2022-11-06'),
  (10732, 9, '2022-11-06'),
  (10733, 5, '2022-11-07'),
  (10734, 31, '2022-11-07'),
  (10735, 45, '2022-11-10'),
  (10736, 37, '2022-11-11'),
  (10737, 85, '2022-11-11'),
  (10738, 74, '2022-11-12'),
  (10739, 85, '2022-11-12'),
  (10740, 89, '2022-11-13'),
  (10741, 4, '2022-11-14'),
  (10742, 10, '2022-11-14'),
  (10743, 4, '2022-11-17'),
  (10744, 83, '2022-11-17'),
  (10745, 63, '2022-11-18'),
  (10746, 14, '2022-11-19'),
  (10747, 59, '2022-11-19'),
  (10748, 71, '2022-11-20'),
  (10749, 38, '2022-11-20'),
  (10750, 87, '2022-11-21'),
  (10751, 68, '2022-11-24'),
  (10752, 53, '2022-11-24'),
  (10753, 27, '2022-11-25'),
  (10754, 49, '2022-11-25'),
  (10755, 9, '2022-11-26'),
  (10756, 75, '2022-11-27'),
  (10757, 71, '2022-11-27'),
  (10758, 68, '2022-11-28'),
  (10759, 2, '2022-11-28'),
  (10760, 50, '2022-12-01'),
  (10761, 65, '2022-12-02'),
  (10762, 24, '2022-12-02'),
  (10763, 23, '2022-12-03'),
  (10764, 20, '2022-12-03'),
  (10765, 63, '2022-12-04'),
  (10766, 56, '2022-12-05'),
  (10767, 76, '2022-12-05'),
  (10768, 4, '2022-12-08'),
  (10769, 83, '2022-12-08'),
  (10770, 34, '2022-12-09'),
  (10771, 20, '2022-12-10'),
  (10772, 44, '2022-12-10'),
  (10773, 20, '2022-12-11'),
  (10774, 24, '2022-12-11'),
  (10775, 78, '2022-12-12'),
  (10776, 20, '2022-12-15'),
  (10777, 31, '2022-12-15'),
  (10778, 5, '2022-12-16'),
  (10779, 52, '2022-12-16'),
  (10780, 46, '2022-12-16'),
  (10781, 87, '2022-12-17'),
  (10782, 12, '2022-12-17'),
  (10783, 34, '2022-12-18'),
  (10784, 49, '2022-12-18'),
  (10785, 33, '2022-12-18'),
  (10786, 62, '2022-12-19'),
  (10787, 41, '2022-12-19'),
  (10788, 63, '2022-12-22'),
  (10789, 23, '2022-12-22'),
  (10790, 31, '2022-12-22'),
  (10791, 25, '2022-12-23'),
  (10792, 91, '2022-12-23'),
  (10793, 4, '2022-12-24'),
  (10794, 61, '2022-12-24'),
  (10795, 20, '2022-12-24'),
  (10796, 35, '2022-12-25'),
  (10797, 17, '2022-12-25'),
  (10798, 38, '2022-12-26'),
  (10799, 39, '2022-12-26'),
  (10800, 72, '2022-12-26'),
  (10801, 8, '2022-12-29'),
  (10802, 73, '2022-12-29'),
  (10803, 88, '2022-12-30'),
  (10804, 72, '2022-12-30'),
  (10805, 77, '2022-12-30'),
  (10806, 84, '2022-12-31'),
  (10807, 27, '2022-12-31'),
  (10808, 60, '2023-01-01'),
  (10809, 88, '2023-01-01'),
  (10810, 42, '2023-01-01'),
  (10811, 47, '2023-01-02'),
  (10812, 66, '2023-01-02'),
  (10813, 67, '2023-01-05'),
  (10814, 84, '2023-01-05'),
  (10815, 71, '2023-01-05'),
  (10816, 32, '2023-01-06'),
  (10817, 39, '2023-01-06'),
  (10818, 49, '2023-01-07'),
  (10819, 12, '2023-01-07'),
  (10820, 65, '2023-01-07'),
  (10821, 75, '2023-01-08'),
  (10822, 82, '2023-01-08'),
  (10823, 46, '2023-01-09'),
  (10824, 24, '2023-01-09'),
  (10825, 17, '2023-01-09'),
  (10826, 7, '2023-01-12'),
  (10827, 9, '2023-01-12'),
  (10828, 64, '2023-01-13'),
  (10829, 38, '2023-01-13'),
  (10830, 81, '2023-01-13'),
  (10831, 70, '2023-01-14'),
  (10832, 41, '2023-01-14'),
  (10833, 56, '2023-01-15'),
  (10834, 81, '2023-01-15'),
  (10835, 1, '2023-01-15'),
  (10836, 20, '2023-01-16'),
  (10837, 5, '2023-01-16'),
  (10838, 47, '2023-01-19'),
  (10839, 81, '2023-01-19'),
  (10840, 47, '2023-01-19'),
  (10841, 76, '2023-01-20'),
  (10842, 80, '2023-01-20'),
  (10843, 84, '2023-01-21'),
  (10844, 59, '2023-01-21'),
  (10845, 63, '2023-01-21'),
  (10846, 76, '2023-01-22'),
  (10847, 71, '2023-01-22'),
  (10848, 16, '2023-01-23'),
  (10849, 39, '2023-01-23'),
  (10850, 84, '2023-01-23'),
  (10851, 67, '2023-01-26'),
  (10852, 65, '2023-01-26'),
  (10853, 6, '2023-01-27'),
  (10854, 20, '2023-01-27'),
  (10855, 55, '2023-01-27'),
  (10856, 3, '2023-01-28'),
  (10857, 5, '2023-01-28'),
  (10858, 40, '2023-01-29'),
  (10859, 25, '2023-01-29'),
  (10860, 26, '2023-01-29'),
  (10861, 89, '2023-01-30'),
  (10862, 44, '2023-01-30'),
  (10863, 35, '2023-02-02'),
  (10864, 4, '2023-02-02'),
  (10865, 63, '2023-02-02'),
  (10866, 5, '2023-02-03'),
  (10867, 48, '2023-02-03'),
  (10868, 62, '2023-02-04'),
  (10869, 72, '2023-02-04'),
  (10870, 91, '2023-02-04'),
  (10871, 9, '2023-02-05'),
  (10872, 30, '2023-02-05'),
  (10873, 90, '2023-02-06'),
  (10874, 30, '2023-02-06'),
  (10875, 5, '2023-02-06'),
  (10876, 9, '2023-02-09'),
  (10877, 67, '2023-02-09'),
  (10878, 63, '2023-02-10'),
  (10879, 90, '2023-02-10'),
  (10880, 24, '2023-02-10'),
  (10881, 12, '2023-02-11'),
  (10882, 71, '2023-02-11'),
  (10883, 48, '2023-02-12'),
  (10884, 45, '2023-02-12'),
  (10885, 76, '2023-02-12'),
  (10886, 34, '2023-02-13'),
  (10887, 29, '2023-02-13'),
  (10888, 30, '2023-02-16'),
  (10889, 65, '2023-02-16'),
  (10890, 18, '2023-02-16'),
  (10891, 44, '2023-02-17'),
  (10892, 50, '2023-02-17'),
  (10893, 39, '2023-02-18'),
  (10894, 71, '2023-02-18'),
  (10895, 20, '2023-02-18'),
  (10896, 50, '2023-02-19'),
  (10897, 37, '2023-02-19'),
  (10898, 54, '2023-02-20'),
  (10899, 46, '2023-02-20'),
  (10900, 88, '2023-02-20'),
  (10901, 35, '2023-02-23'),
  (10902, 24, '2023-02-23'),
  (10903, 34, '2023-02-24'),
  (10904, 89, '2023-02-24'),
  (10905, 88, '2023-02-24'),
  (10906, 91, '2023-02-25'),
  (10907, 74, '2023-02-25'),
  (10908, 66, '2023-02-26'),
  (10909, 70, '2023-02-26'),
  (10910, 90, '2023-02-26'),
  (10911, 30, '2023-02-26'),
  (10912, 37, '2023-02-26'),
  (10913, 62, '2023-02-26'),
  (10914, 62, '2023-02-27'),
  (10915, 80, '2023-02-27'),
  (10916, 64, '2023-02-27'),
  (10917, 69, '2023-03-02'),
  (10918, 10, '2023-03-02'),
  (10919, 47, '2023-03-02'),
  (10920, 4, '2023-03-03'),
  (10921, 83, '2023-03-03'),
  (10922, 34, '2023-03-03'),
  (10923, 41, '2023-03-03'),
  (10924, 5, '2023-03-04'),
  (10925, 34, '2023-03-04'),
  (10926, 2, '2023-03-04'),
  (10927, 40, '2023-03-05'),
  (10928, 29, '2023-03-05'),
  (10929, 25, '2023-03-05'),
  (10930, 76, '2023-03-06'),
  (10931, 68, '2023-03-06'),
  (10932, 9, '2023-03-06'),
  (10933, 38, '2023-03-06'),
  (10934, 44, '2023-03-09'),
  (10935, 88, '2023-03-09'),
  (10936, 32, '2023-03-09'),
  (10937, 12, '2023-03-10'),
  (10938, 63, '2023-03-10'),
  (10939, 49, '2023-03-10'),
  (10940, 9, '2023-03-11'),
  (10941, 71, '2023-03-11'),
  (10942, 66, '2023-03-11'),
  (10943, 11, '2023-03-11'),
  (10944, 10, '2023-03-12'),
  (10945, 52, '2023-03-12'),
  (10946, 83, '2023-03-12'),
  (10947, 11, '2023-03-13'),
  (10948, 30, '2023-03-13'),
  (10949, 10, '2023-03-13'),
  (10950, 49, '2023-03-16'),
  (10951, 68, '2023-03-16'),
  (10952, 1, '2023-03-16'),
  (10953, 4, '2023-03-16'),
  (10954, 47, '2023-03-17'),
  (10955, 24, '2023-03-17'),
  (10956, 6, '2023-03-17'),
  (10957, 35, '2023-03-18'),
  (10958, 54, '2023-03-18'),
  (10959, 31, '2023-03-18'),
  (10960, 35, '2023-03-19'),
  (10961, 62, '2023-03-19'),
  (10962, 63, '2023-03-19'),
  (10963, 28, '2023-03-19'),
  (10964, 74, '2023-03-20'),
  (10965, 55, '2023-03-20'),
  (10966, 14, '2023-03-20'),
  (10967, 79, '2023-03-23'),
  (10968, 20, '2023-03-23'),
  (10969, 15, '2023-03-23'),
  (10970, 8, '2023-03-24'),
  (10971, 26, '2023-03-24'),
  (10972, 40, '2023-03-24'),
  (10973, 40, '2023-03-24'),
  (10974, 75, '2023-03-25'),
  (10975, 10, '2023-03-25'),
  (10976, 35, '2023-03-25'),
  (10977, 24, '2023-03-26'),
  (10978, 50, '2023-03-26'),
  (10979, 20, '2023-03-26'),
  (10980, 24, '2023-03-27'),
  (10981, 34, '2023-03-27'),
  (10982, 10, '2023-03-27'),
  (10983, 71, '2023-03-27'),
  (10984, 71, '2023-03-30'),
  (10985, 37, '2023-03-30'),
  (10986, 54, '2023-03-30'),
  (10987, 19, '2023-03-31'),
  (10988, 65, '2023-03-31'),
  (10989, 61, '2023-03-31'),
  (10990, 20, '2023-04-01'),
  (10991, 63, '2023-04-01'),
  (10992, 77, '2023-04-01'),
  (10993, 24, '2023-04-01'),
  (10994, 83, '2023-04-02'),
  (10995, 58, '2023-04-02'),
  (10996, 63, '2023-04-02'),
  (10997, 46, '2023-04-03'),
  (10998, 91, '2023-04-03'),
  (10999, 56, '2023-04-03'),
  (11000, 65, '2023-04-06'),
  (11001, 24, '2023-04-06'),
  (11002, 71, '2023-04-06'),
  (11003, 78, '2023-04-06'),
  (11004, 50, '2023-04-07'),
  (11005, 90, '2023-04-07'),
  (11006, 32, '2023-04-07'),
  (11007, 60, '2023-04-08'),
  (11008, 20, '2023-04-08'),
  (11009, 30, '2023-04-08'),
  (11010, 66, '2023-04-09'),
  (11011, 1, '2023-04-09'),
  (11012, 25, '2023-04-09'),
  (11013, 69, '2023-04-09'),
  (11014, 47, '2023-04-10'),
  (11015, 70, '2023-04-10'),
  (11016, 4, '2023-04-10'),
  (11017, 20, '2023-04-13'),
  (11018, 48, '2023-04-13'),
  (11019, 64, '2023-04-13'),
  (11020, 56, '2023-04-14'),
  (11021, 63, '2023-04-14'),
  (11022, 34, '2023-04-14'),
  (11023, 11, '2023-04-14'),
  (11024, 19, '2023-04-15'),
  (11025, 87, '2023-04-15'),
  (11026, 27, '2023-04-15'),
  (11027, 10, '2023-04-16'),
  (11028, 39, '2023-04-16'),
  (11029, 14, '2023-04-16'),
  (11030, 71, '2023-04-17'),
  (11031, 71, '2023-04-17'),
  (11032, 89, '2023-04-17'),
  (11033, 68, '2023-04-17'),
  (11034, 55, '2023-04-20'),
  (11035, 76, '2023-04-20'),
 

Operators in the WHERE clause

We can operate with different operators in the WHERE clause:

= Equal to
< Less than
> Greater than
<= Less than or equal to
>= Greater than or equal to
<> Not equal to
!= Not equal to
LIKE Check if a value matches a pattern (case sensitive)
ILIKE Check if a value matches a pattern (case insensitive)
AND Logical AND
OR Logical OR
IN Check if a value is between a range of values
BETWEEN Check if a value is between a range of values
IS NULL Check if a value is NULL
NOT Makes a negative result e.g. NOT LIKE, NOT IN, NOT BETWEEN

Equal To

The = operator is used when you want to return all records where a column is equal to a specified value:

Example

Return all records where the brand is 'Volvo':

SELECT * FROM cars
WHERE brand = 'Volvo';

Less Than

The < operator is used when you want to return all records where a column is less than a specified value.

Example

Return all records where the year is less than 1975:

SELECT * FROM cars
WHERE model < 1975;

Greater Than

The > operator is used when you want to return all records where a columns is greater than a specified value.

Example

Return all records where the year is greater than 1975:

SELECT * FROM cars
WHERE model > 1975;

Less Than or Equal To

The <= operator is used when you want to return all records where a column is less than, or equal to, a specified value.

Example

Return all records where the year is less than or equal to 1975:

SELECT * FROM cars
WHERE model <= 1975;

Greater Than or Equal to

The >= operator is used when you want to return all records where a columns is greater than, or equal to, a specified value.

Example

Return all records where the year is greater than or equal 1975:

SELECT * FROM cars
WHERE model >= 1975;

Not Equal To

The <> operator is used when you want to return all records where a column is NOT equal to a specified value:

Example

Return all records where the brand is NOT 'Volvo':

SELECT * FROM cars
WHERE brand <> 'Volvo';

You will get the same reult with the != operator:

Example

Return all records where the brand is NOT 'Volvo':

SELECT * FROM cars
WHERE brand != 'Volvo';

LIKE

The LIKE operator is used when you want to return all records where a column is equal to a specified pattern.

The pattern can be an absolute value like 'Volvo', or with a wildcard that has a special meaning.

There are two wildcards often used in conjunction with the LIKE operator:

  • The percent sign %, represents zero, one, or multiple characters.
  • The underscore sign _, represents one single character.

Example

Return all records where the model STARTS with a capital 'M':

SELECT * FROM cars
WHERE model LIKE 'M%';

The LIKE operator is case sensitive.

ILIKE

Same as the LIKE operator, but ILIKE is case insensitive.

Example

Return all records where the model start with a 'm':

SELECT * FROM cars
WHERE model ILIKE 'm%';

AND

The logical AND operator is used when you want to check more that one condition:

Example

Return all records where the brand is 'Volvo' and the year is 1968:

SELECT * FROM cars
WHERE brand = 'Volvo' AND year = 1968;

OR

The logical OR operator is used when you can accept that only one of many conditions is true:

Example

Return all records where the brand is 'Volvo' OR the year is 1975:

SELECT * FROM cars
WHERE brand = 'Volvo' OR year = 1975;

IN

The IN operator is used when a column's value matches any of the values in a list:

Example

Return all records where the brand is present in this list: ('Volvo', 'Mercedes', 'Ford'):

SELECT * FROM cars
WHERE brand IN ('Volvo', 'Mercedes', 'Ford');

BETWEEN

The BETWEEN operator is used to check if a column's value is between a specified range of values:

Example

Return all records where the year is between 1970 and 1980:

SELECT * FROM cars
WHERE year BETWEEN 1970 AND 1980;

The BETWEEN operator includes the from and to values, meaning that in the above example, the result would include cars made in 1970 and 1980 as well.

IS NULL

The IS NULL operator is used to check if a column's value is NULL:

Example

Return all records where the model is NULL:

SELECT * FROM cars
WHERE model IS NULL;

NOT

The NOT operator can be used together with LIKE, ILIKE, IN, BETWEEN, and NULL operators to reverse the truth of the operator.

Example: NOT LIKE

Return all records where the brand does NOT start with a capital 'B' (case sensitive):

SELECT * FROM cars
WHERE brand NOT LIKE 'B%';

Example: NOT ILIKE

Return all records where the brand does NOT start with a 'b' (case insensitive):

SELECT * FROM cars
WHERE brand NOT ILIKE 'b%';

Example: NOT IN

Return all records where the brand is NOT present in this list: ('Volvo', 'Mercedes', 'Ford'):

SELECT * FROM cars
WHERE brand NOT IN ('Volvo', 'Mercedes', 'Ford');

Example: NOT BETWEEN

Return all records where the year is NOT between 1970 and 1980:

SELECT * FROM cars
WHERE year NOT BETWEEN 1970 AND 1980;

The NOT BETWEEN operator excludes the from and to values, meaning that in the above example, the result would not include cars made in 1970 and 1980.

Example: IS NOT NULL

Return all records where the model is NOT null:

SELECT * FROM cars
WHERE model IS NOT NULL;

The cars table has no columns with NULL values, so the example above will return all 4 rows.

PostgreSQL Exercises

Test Yourself With Exercises

Exercise:

Write the correct SQL statement to select all records where the value of the field year is less than 1970:

SELECT * FROM cars
WHERE ;

Select Data

To retrieve data from a data base, we use the SELECT statement.

Specify Columns

By specifying the column names, we can choose which columns to select:

Example

SELECT customer_name, country FROM customers;

Return ALL Columns

Specify a * instead of the column names to select all columns:

Example

SELECT * FROM customers;

PostgreSQL Exercises

Test Yourself With Exercises

Exercise:

Write the correct SQL statement to select the columns city and country from the customers table.

;

The SELECT DISTINCT Statement

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

Inside a table, a column often contains many duplicate values and sometimes you only want to list the different (distinct) values.

Example

Select only the DISTINCT values from the country column in the customers table:

SELECT DISTINCT country FROM customers;

Even though the customers table has 91 records, it only has 21 different countries, and that is what you get as a result when executing the SELECT DISTINCT statement above

SELECT COUNT(DISTINCT)

We can also use the DISTINCT keyword in combination with the COUNT statement, which in the example below will return the number of different countries there are in the customers table.

Example

Return the number of different countries there are in the customers table:

SELECT COUNT(DISTINCT country) FROM customers;

PostgreSQL Exercises

Test Yourself With Exercises

Exercise:

Write the correct SQL statement to select the column country from the table customers.

But return only different values:

 country FROM customers;

Filter Records

The WHERE clause is used to filter records.

It is used to extract only those records that fulfill a specified condition.

If we want to return only the records where city is London, we can specify that in the WHERE clause:

Example

SELECT * FROM customers
WHERE city = 'London';

Text Fields vs. Numeric Fields

PostgreSQL requires quotes around text values.

However, numeric fields should not be enclosed in quotes:

Example

SELECT * FROM customers
WHERE customer_id = 19;

Quotes around numeric fields will not fail, but it is good practice to always write numeric values without quotes.

Greater than

Use the > operator to return all records where customer_id is greater than 80:

Example

SELECT * FROM customers
WHERE customer_id > 80;

Quotes around numeric fields will not fail, but it is good practice to always write numeric values without quotes.

PostgreSQL Exercises

Test Yourself With Exercises

Exercise:

Write the correct SQL statement to select all records from the customers table where the city value is "Berlin":

SELECT * FROM customers
;

Sort Data

The ORDER BY keyword is used to sort the result in ascending or descending order.

The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.

Example

Sort the table by year:

SELECT * FROM products
ORDER BY price;

DESC

The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.

Example

Sort the table by year, in descending order:

SELECT * FROM products
ORDER BY price DESC;

Sort Alphabetically

For string values the ORDER BY keyword will order alphabetically:

Example

Sort the table by brand:

SELECT * FROM products
ORDER BY product_name;

Alphabetically DESC

To sort the table reverse alphabetically, use the DESC keyword:

Example

Sort the table by brand, in descending order:

SELECT * FROM products
ORDER BY product_name DESC;

PostgreSQL Exercises

Test Yourself With Exercises

Exercise:

Write the correct SQL statement to sort the result alphabetically by customer_name:

SELECT * FROM customers
;

The LIMIT Clause

The LIMIT clause is used to limit the maximum number of records to return.

Example

Return only the 20 first records from the customers table:

SELECT * FROM customers
LIMIT 20;

The OFFSET Clause

The OFFSET clause is used to specify where to start selecting the records to return.

If you want to return 20 records, but start at number 40, you can use both LIMIT and OFFSET.

Note: The first record is number 0, so when you specify OFFSET 40 it means starting at record number 41.

Example

Return 20 records, starting from the 41th record:

SELECT * FROM customers
LIMIT 20 OFFSET 40;

PostgreSQL Exercises

Test Yourself With Exercises

Exercise:

Write the correct SQL statement to select first 10 records from the table customers.

 * FROM customers
;

MIN

The MIN() function returns the smallest value of the selected column.

Example

Return the lowest price in the products table:

SELECT MIN(price)
FROM products;

MAX

The MAX() function returns the largest value of the selected column.

Example

Return the highest price in the products table:

SELECT MAX(price)
FROM products;

Set Column Name

When you use MIN() or MAX(), the returned column will be named min or max by default. To give the column a new name, use the AS keyword.

Example

Return the lowest price, and name the column lowest_price:

SELECT MIN(price) AS lowest_price
FROM products;

PostgreSQL Exercises

Test Yourself With Exercises

Exercise:

Use the correct function to return the lowest value from the field price in the table products.

SELECT 
FROM products;

COUNT

The COUNT() function returns the number of rows that matches a specified criterion.

If the specified criterion is a column name, the COUNT() function returns the number of columns with that name.

Example

Return the number of customers from the customers table:

SELECT COUNT(customer_id)
FROM customers;

Note: NULL values are not counted.

By specifying a WHERE clause, you can e.g. return the number of customers that comes from London:

Example

Return the number of customers from London:

SELECT COUNT(customer_id)
FROM customers
WHERE city = 'London';

PostgreSQL Exercises

Test Yourself With Exercises

Exercise:

Use the correct function to return the number of records in the customers table with the column name customer_id:

SELECT (customer_id)
FROM customers;

SUM

The SUM() function returns the total sum of a numeric column.

The following SQL statement finds the sum of the quantity fields in the order_details table:

Example

Return the total amount of ordered items:

SELECT SUM(quantity)
FROM order_details;

Note: NULL values are ignored.

PostgreSQL Exercises

Test Yourself With Exercises

Exercise:

Use the correct function to return the total sum of all values of the field price in the products table.

SELECT 
FROM products;

AVG

The AVG() function returns the average value of a numeric column.

Example

Return the average price of all the products in the products table:

SELECT AVG(price)
FROM products;

Note: NULL values are ignored.

With 2 Decimals

The above example returned the average price of all products, the result was 28.8663636363636364.

We can use the ::NUMERIC operator to round the average price to a number with 2 decimals:

Example

Return the average price of all the products, rounded to 2 decimals:

SELECT AVG(price)::NUMERIC(10,2)
FROM products;

PostgreSQL Exercises

Test Yourself With Exercises

Exercise:

Use the correct function to return the average number of all values of the field price in the products table.

SELECT 
FROM products;

LIKE

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

There are two wildcards often used in conjunction with the LIKE operator:

  • % The percent sign represents zero, one, or multiple characters
  • _ The underscore sign represents one, single character

Starts with

To return records that starts with a specific letter or phrase, add the % at the end of the letter or phrase.

Example

Return all customers with a name that starts with the letter 'A':

SELECT * FROM customers
WHERE customer_name LIKE 'A%';

Contains

To return records that contains a specific letter or phrase, add the % both before and after the letter or phrase.

Example

Return all customers with a name that contains the letter 'A':

SELECT * FROM customers
WHERE customer_name LIKE '%A%';

ILIKE

Note: The LIKE operator is case sensitive, if you want to do a case insensitive search, use the ILIKE operator instead.

Example

Return all customers with a name that contains the letter 'A' or 'a':

SELECT * FROM customers
WHERE customer_name ILIKE '%A%';

Ends with

To return records that ends with a specific letter or phrase, add the % before the letter or phrase.

Example

Return all customers with a name that ends with the phrase 'en':

SELECT * FROM customers
WHERE customer_name LIKE '%en';

The Undescore _ Wildcard

The _ wildcard represents a single character.

It can be any character or number, but each _ represents one, and only one, character.

Example

Return all customers from a city that starts with 'L' followed by one wildcard character, then 'nd' and then two wildcard characters:

SELECT * FROM customers
WHERE city LIKE 'L_nd__';

PostgreSQL Exercises

Test Yourself With Exercises

Exercise:

Write the correct SQL statement to select all records where the value of the field model starts with the capital letter 'M'.

SELECT * FROM cars
WHERE model ;

IN

The IN operator allows you to specify a list of possible values in the WHERE clause.

The IN operator is a shorthand for multiple OR conditions.

Example

Return all customers FROM 'Germany', France' or 'UK':

SELECT * FROM customers
WHERE country IN ('Germany', 'France', 'UK');

NOT IN

By using the NOT keyword in front of the IN operator, you return all records that are NOT any of the values in the list.

Example

Return all customers that are NOT from 'Germany', France' or 'UK':

SELECT * FROM customers
WHERE country NOT IN ('Germany', 'France', 'UK');

IN (SELECT)

You can also us a SELECT statement inside the parenthesis to return all records that are in the result of the SELECT statement.

Example

Return all customers that have an order in the orders table:

SELECT * FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders);

NOT IN (SELECT)

The result in the example above returned 89 records, that means that there are 2 customers that haven't placed any orders.

Let us check if that is correct, by using the NOT IN operator.

Example

Return all customers that have NOT placed any orders in the orders table:

SELECT * FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);

PostgreSQL Exercises

Test Yourself With Exercises

Exercise:

Select all customers where the country field is one of the the values in the following list:

('Norway', 'Sweden', 'Denmark')

SELECT * FROM customers
WHERE country ;

BETWEEN

The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates.

The BETWEEN operator is inclusive: begin and end values are included.

Example

Select all products with a price between 10 and 15:

SELECT * FROM Products
WHERE Price BETWEEN 10 AND 15;

BETWEEN Text Values

The BETWEEN operator can also be used on text values.

The result returns all records that are alphabetically between the specified values.

Example

Select all products between 'Pavlova' and 'Tofu':

SELECT * FROM Products
WHERE product_name BETWEEN 'Pavlova' AND 'Tofu';

If we add an ORDER BY clause to the example above, it will be a bit easier to read:

Example

Same example as above, but we sort it by product_name:

SELECT * FROM Products
WHERE product_name BETWEEN 'Pavlova' AND 'Tofu'
ORDER BY product_name;

BETWEEN Date Values

The BETWEEN operator can also be used on date values.

Example

Select all orders between 12. of April 2023 and 5. of May 20203:

SELECT * FROM orders
WHERE order_date BETWEEN '2023-04-12' AND '2023-05-05';

PostgreSQL Exercises

Test Yourself With Exercises

Exercise:

Select all cars where the field year has a value between 1970 and 1980:

SELECT * FROM cars
WHERE year ;

Aliases

SQL aliases are used to give a table, or a column in a table, a temporary name.

Aliases are often used to make column names more readable.

An alias only exists for the duration of that query.

An alias is created with the AS keyword.

Example

Using aliases for columns:

SELECT customer_id AS id
FROM customers;

AS is Optional

Actually, you can skip the AS keyword and get the same result:

Example

Same result without AS:

SELECT customer_id id
FROM customers;

Concatenate Columns

The AS keyword is often used when two or more fields are concatenated into one.

To concatenate two fields use ||.

Example

Concatenate two fields and call them product:

SELECT product_name || unit AS product
FROM products;

Note: In the result of the example above we are missing a space between product_name and unit. To add a space when concatenating, use || ' ' ||.

Example

Concatenate, with space:

SELECT product_name || ' ' || unit AS product
FROM products;

Using Aliases With a Space Character

If you want your alias to contain one or more spaces, like "My Great Products", surround your alias with double quotes.

Example

Surround your alias with double quotes:

SELECT product_name AS "My Great Products"
FROM products;

PostgreSQL Exercises

Test Yourself With Exercises

Exercise:

Make an alias for the field customer_name so that it will be dispalyed as Company:

SELECT customer_name  FROM customers;

JOIN

A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

Let's look at a selection from the products table:

 product_id |  product_name  | category_id
------------+----------------+-------------
         33 | Geitost        |           4
         34 | Sasquatch Ale  |           1
         35 | Steeleye Stout |           1
         36 | Inlagd Sill    |           8

Then, look at a selection from the categories table:

 category_id | category_name
-------------+----------------
           1 | Beverages
           2 | Condiments
           3 | Confections
           4 | Dairy Products

Notice that the category_id column in the products table refers to the category_id in the categories table. The relationship between the two tables above is the category_id column.

Then, we can create the following SQL statement (with a JOIN), that selects records that have matching values in both tables:

Example

Join products to categories using the category_id column:

SELECT product_id, product_name, category_name
FROM products
INNER JOIN categories ON products.category_id = categories.category_id;

If we pull out the same selection from products table above, we get this result:

Result

 product_id |  product_name  | category_name
------------+----------------+----------------
         33 | Geitost        | Dairy Products
         34 | Sasquatch Ale  | Beverages
         35 | Steeleye Stout | Beverages
         36 | Inlagd Sill    | Seafood

Different Types of Joins

Here are the different types of the Joins in PostgreSQL:

  • INNER JOIN: Returns records that have matching values in both tables
  • LEFT JOIN: Returns all records from the left table, and the matched records from the right table
  • RIGHT JOIN: Returns all records from the right table, and the matched records from the left table
  • FULL JOIN: Returns all records when there is a match in either left or right table

PostgreSQL Exercises

Test Yourself With Exercises

Exercise:

Insert the missing parts in the JOIN clause to join the two tables orders and customers, using the customer_id field in both tables as the relationship:

SELECT * FROM orders
LEFT JOIN customers
 = ;

INNER JOIN

The INNER JOIN keyword selects records that have matching values in both tables.

Let's look at an example using our dummy testproducts table:

 testproduct_id |      product_name      | category_id
----------------+------------------------+-------------
              1 | Johns Fruit Cake       |           3
              2 | Marys Healthy Mix      |           9
              3 | Peters Scary Stuff     |          10
              4 | Jims Secret Recipe     |          11
              5 | Elisabeths Best Apples |          12
              6 | Janes Favorite Cheese  |           4
              7 | Billys Home Made Pizza |          13
              8 | Ellas Special Salmon   |           8
              9 | Roberts Rich Spaghetti |           5
            10 | Mias Popular Ice        |          14
(10 rows)

We will try to join the testproducts table with the categories table:

 category_id | category_name  |                       description
-------------+----------------+------------------------------------------------------------
           1 | Beverages      | Soft drinks, coffees, teas, beers, and ales
           2 | Condiments     | Sweet and savory sauces, relishes, spreads, and seasonings
           3 | Confections    | Desserts, candies, and sweet breads
           4 | Dairy Products | Cheeses
           5 | Grains/Cereals | Breads, crackers, pasta, and cereal
           6 | Meat/Poultry   | Prepared meats
           7 | Produce        | Dried fruit and bean curd
           8 | Seafood        | Seaweed and fish
(8 rows)

Notice that many of the products in testproducts have a category_id that does not match any of the categories in the categories table.

By using INNER JOIN we will not get the records where there is not a match, we will only get the records that matches both tables:

Example

Join testproducts to categories using the category_id column:

SELECT testproduct_id, product_name, category_name
FROM testproducts
INNER JOIN categories ON testproducts.category_id = categories.category_id;

Result

Only the records with a match in BOTH tables are returned:

 testproduct_id |      product_name      | category_name
----------------+------------------------+----------------
              1 | Johns Fruit Cake       | Confections
              6 | Janes Favorite Cheese  | Dairy Products
              8 | Ellas Special Salmon   | Seafood
              9 | Roberts Rich Spaghetti | Grains/Cereals
(4 rows)

Note: JOIN and INNER JOIN will give the same result.

INNER is the default join type for JOIN, so when you write JOIN the parser actually writes INNER JOIN.

PostgreSQL Exercises

Test Yourself With Exercises

Exercise:

Choose the correct JOIN clause to select all records from the two tables where there is a match in both tables:

SELECT * FROM orders
 customers
ON orders.customer_id = customers.customer_id;

LEFT JOIN

The LEFT JOIN keyword selects ALL records from the "left" table, and the matching records from the "right" table. The result is 0 records from the right side if there is no match.

Let's look at an example using our dummy testproducts table:

 testproduct_id |      product_name      | category_id
----------------+------------------------+-------------
              1 | Johns Fruit Cake       |           3
              2 | Marys Healthy Mix      |           9
              3 | Peters Scary Stuff     |          10
              4 | Jims Secret Recipe     |          11
              5 | Elisabeths Best Apples |          12
              6 | Janes Favorite Cheese  |           4
              7 | Billys Home Made Pizza |          13
              8 | Ellas Special Salmon   |           8
              9 | Roberts Rich Spaghetti |           5
            10 | Mias Popular Ice        |          14
(10 rows)

We will try to join the testproducts table with the categories table:

 category_id | category_name  |                       description
-------------+----------------+------------------------------------------------------------
           1 | Beverages      | Soft drinks, coffees, teas, beers, and ales
           2 | Condiments     | Sweet and savory sauces, relishes, spreads, and seasonings
           3 | Confections    | Desserts, candies, and sweet breads
           4 | Dairy Products | Cheeses
           5 | Grains/Cereals | Breads, crackers, pasta, and cereal
           6 | Meat/Poultry   | Prepared meats
           7 | Produce        | Dried fruit and bean curd
           8 | Seafood        | Seaweed and fish
(8 rows)

Note: Many of the products in testproducts have a category_id that does not match any of the categories in the categories table.

By using LEFT JOIN we will get all records from testpoducts, even the ones with no match in the categories table:

Example

Join testproducts to categories using the category_id column:

SELECT testproduct_id, product_name, category_name
FROM testproducts
LEFT JOIN categories ON testproducts.category_id = categories.category_id;

Result

All records from testproducts, and only the matched records from categories:

 testproduct_id |      product_name      | category_name
----------------+------------------------+----------------
              1 | Johns Fruit Cake       | Confections
              2 | Marys Healthy Mix      |
              3 | Peters Scary Stuff     |
              4 | Jims Secret Recipe     |
              5 | Elisabeths Best Apples |
              6 | Janes Favorite Cheese  | Dairy Products
              7 | Billys Home Made Pizza |
              8 | Ellas Special Salmon   | Seafood
              9 | Roberts Rich Spaghetti | Grains/Cereals
             10 | Mias Popular Ice       |
(10 rows)

Note: LEFT JOIN and LEFT OUTER JOIN will give the same result.

OUTER is the default join type for LEFT JOIN, so when you write LEFT JOIN the parser actually writes LEFT OUTER JOIN.

RIGHT JOIN

The RIGHT JOIN keyword selects ALL records from the "right" table, and the matching records from the "left" table. The result is 0 records from the left side if there is no match.

Let's look at an example using our dummy testproducts table:

 testproduct_id |      product_name      | category_id
----------------+------------------------+-------------
              1 | Johns Fruit Cake       |           3
              2 | Marys Healthy Mix      |           9
              3 | Peters Scary Stuff     |          10
              4 | Jims Secret Recipe     |          11
              5 | Elisabeths Best Apples |          12
              6 | Janes Favorite Cheese  |           4
              7 | Billys Home Made Pizza |          13
              8 | Ellas Special Salmon   |           8
              9 | Roberts Rich Spaghetti |           5
             10 | Mias Popular Ice       |          14
(10 rows)

We will try to join the testproducts table with the categories table:

 category_id | category_name  |                       description
-------------+----------------+------------------------------------------------------------
           1 | Beverages      | Soft drinks, coffees, teas, beers, and ales
           2 | Condiments     | Sweet and savory sauces, relishes, spreads, and seasonings
           3 | Confections    | Desserts, candies, and sweet breads
           4 | Dairy Products | Cheeses
           5 | Grains/Cereals | Breads, crackers, pasta, and cereal
           6 | Meat/Poultry   | Prepared meats
           7 | Produce        | Dried fruit and bean curd
           8 | Seafood        | Seaweed and fish
(8 rows)

Note: Many of the products in testproducts have a category_id that does not match any of the categories in the categories table.

By using RIGHT JOIN we will get all records from categories, even the ones with no match in the testproducts table:

Example

Join testproducts to categories using the category_id column:

SELECT testproduct_id, product_name, category_name
FROM testproducts
RIGHT JOIN categories ON testproducts.category_id = categories.category_id;

Result

All records from categories, and only the matched records from testproducts:

 testproduct_id |      product_name      | category_name
----------------+------------------------+----------------
              1 | Johns Fruit Cake       | Confections
              6 | Janes Favorite Cheese  | Dairy Products
              8 | Ellas Special Salmon   | Seafood
              9 | Roberts Rich Spaghetti | Grains/Cereals
                |                        | Condiments
                |                        | Meat/Poultry
                |                        | Beverages
                |                        | Produce
(8 rows)

Note: RIGHT JOIN and RIGHT OUTER JOIN will give the same result.

OUTER is the default join type for RIGHT JOIN, so when you write RIGHT JOIN the parser actually writes RIGHT OUTER JOIN.

PostgreSQL Exercises

Test Yourself With Exercises

Exercise:

Choose the correct JOIN clause to select all the records from the customers table plus all the matches in the orders table:

SELECT * FROM orders
 customers
ON orders.customer_id = customers.customer_id;

FULL JOIN

The FULL JOIN keyword selects ALL records from both tables, even if there is not a match. For rows with a match the values from both tables are available, if there is not a match the empty fields will get the value NULL.

Let's look at an example using our dummy testproducts table:

 testproduct_id |      product_name      | category_id
----------------+------------------------+-------------
              1 | Johns Fruit Cake       |           3
              2 | Marys Healthy Mix      |           9
              3 | Peters Scary Stuff     |          10
              4 | Jims Secret Recipe     |          11
              5 | Elisabeths Best Apples |          12
              6 | Janes Favorite Cheese  |           4
              7 | Billys Home Made Pizza |          13
              8 | Ellas Special Salmon   |           8
              9 | Roberts Rich Spaghetti |           5
             10 | Mias Popular Ice       |          14
(10 rows)

We will try to join the testproducts table with the categories table:

 category_id | category_name  |                       description
-------------+----------------+------------------------------------------------------------
           1 | Beverages      | Soft drinks, coffees, teas, beers, and ales
           2 | Condiments     | Sweet and savory sauces, relishes, spreads, and seasonings
           3 | Confections    | Desserts, candies, and sweet breads
           4 | Dairy Products | Cheeses
           5 | Grains/Cereals | Breads, crackers, pasta, and cereal
           6 | Meat/Poultry   | Prepared meats
           7 | Produce        | Dried fruit and bean curd
           8 | Seafood        | Seaweed and fish
(8 rows)

Note: Many of the products in testproducts have a category_id that does not match any of the categories in the categories table.

By using FULL JOIN we will get all records from both the categories table and the testproducts table:

Example

Join testproducts to categories using the category_id column:

SELECT testproduct_id, product_name, category_name
FROM testproducts
FULL JOIN categories ON testproducts.category_id = categories.category_id;

Result

All records from both tables are returned.

Rows with no match will get a NULL value in fields from the opposite table:

 testproduct_id |      product_name       | category_name
----------------+-------------------------+----------------
              1 | Johns Fruit Cake        | Confections
              2 | Marys Healthy Mix       |
              3 | Peters Scary Stuff      |
              4 | Jims Secret Recipe      |
              5 | Elisabeths Best Apples  |
              6 | Janes Favorite Cheese   | Dairy Products
              7 | Billys Home Made Pizza  |
              8 | Ellas Special Salmon    | Seafood
              9 | Roberts Rich Spaghetti  | Grains/Cereals
             10 | Mias Popular Ice        |
                |                         | Condiments
                |                         | Meat/Poultry
                |                         | Beverages
                |                         | Produce
(14 rows)

Note: FULL JOIN and FULL OUTER JOIN will give the same result.

OUTER is the default join type for FULL JOIN, so when you write FULL JOIN the parser actually writes FULL OUTER JOIN.

CROSS JOIN

The CROSS JOIN keyword matches ALL records from the "left" table with EACH record from the "right" table.

That means that all records from the "right" table will be returned for each record in the "left" table.

This way of joining can potentially return very large table, and you should not use it if you do not have to.

Let's look at an example using our dummy testproducts table:

 testproduct_id |      product_name      | category_id
----------------+------------------------+-------------
              1 | Johns Fruit Cake       |           3
              2 | Marys Healthy Mix      |           9
              3 | Peters Scary Stuff     |          10
              4 | Jims Secret Recipe     |          11
              5 | Elisabeths Best Apples |          12
              6 | Janes Favorite Cheese  |           4
              7 | Billys Home Made Pizza |          13
              8 | Ellas Special Salmon   |           8
              9 | Roberts Rich Spaghetti |           5
             10 | Mias Popular Ice       |          14
(10 rows)

We will try to join the testproducts table with the categories table:

 category_id | category_name  |                       description
-------------+----------------+------------------------------------------------------------
           1 | Beverages      | Soft drinks, coffees, teas, beers, and ales
           2 | Condiments     | Sweet and savory sauces, relishes, spreads, and seasonings
           3 | Confections    | Desserts, candies, and sweet breads
           4 | Dairy Products | Cheeses
           5 | Grains/Cereals | Breads, crackers, pasta, and cereal
           6 | Meat/Poultry   | Prepared meats
           7 | Produce        | Dried fruit and bean curd
           8 | Seafood        | Seaweed and fish
(8 rows)

Note: The CROSS JOIN method will return ALL categories for EACH testproduct, meaning that it will return 80 rows (10 * 8).

Example

Join testproducts to categories using the CROSS JOIN keyword:

SELECT testproduct_id, product_name, category_name
FROM testproducts
CROSS JOIN categories;

Result

All categories for each testproduct will be returned:

 testproduct_id |      product_name      | category_name
----------------+------------------------+----------------
              1 | Johns Fruit Cake       | Beverages
              1 | Johns Fruit Cake       | Condiments
              1 | Johns Fruit Cake       | Confections
              1 | Johns Fruit Cake       | Dairy Products
              1 | Johns Fruit Cake       | Grains/Cereals
              1 | Johns Fruit Cake       | Meat/Poultry
              1 | Johns Fruit Cake       | Produce
              1 | Johns Fruit Cake       | Seafood
              2 | Marys Healthy Mix      | Beverages
              2 | Marys Healthy Mix      | Condiments
              2 | Marys Healthy Mix      | Confections
              2 | Marys Healthy Mix      | Dairy Products
              2 | Marys Healthy Mix      | Grains/Cereals
              2 | Marys Healthy Mix      | Meat/Poultry
              2 | Marys Healthy Mix      | Produce
              2 | Marys Healthy Mix      | Seafood
              3 | Peters Scary Stuff     | Beverages
              3 | Peters Scary Stuff     | Condiments
              3 | Peters Scary Stuff     | Confections
              3 | Peters Scary Stuff     | Dairy Products
              3 | Peters Scary Stuff     | Grains/Cereals
              3 | Peters Scary Stuff     | Meat/Poultry
              3 | Peters Scary Stuff     | Produce
              3 | Peters Scary Stuff     | Seafood
              4 | Jims Secret Recipe     | Beverages
              4 | Jims Secret Recipe     | Condiments
              4 | Jims Secret Recipe     | Confections
              4 | Jims Secret Recipe     | Dairy Products
              4 | Jims Secret Recipe     | Grains/Cereals
              4 | Jims Secret Recipe     | Meat/Poultry
              4 | Jims Secret Recipe     | Produce
              4 | Jims Secret Recipe     | Seafood
              5 | Elisabeths Best Apples | Beverages
              5 | Elisabeths Best Apples | Condiments
              5 | Elisabeths Best Apples | Confections
              5 | Elisabeths Best Apples | Dairy Products
              5 | Elisabeths Best Apples | Grains/Cereals
              5 | Elisabeths Best Apples | Meat/Poultry
              5 | Elisabeths Best Apples | Produce
              5 | Elisabeths Best Apples | Seafood
              6 | Janes Favorite Cheese  | Beverages
              6 | Janes Favorite Cheese  | Condiments
              6 | Janes Favorite Cheese  | Confections
              6 | Janes Favorite Cheese  | Dairy Products
              6 | Janes Favorite Cheese  | Grains/Cereals
              6 | Janes Favorite Cheese  | Meat/Poultry
              6 | Janes Favorite Cheese  | Produce
              6 | Janes Favorite Cheese  | Seafood
              7 | Billys Home Made Pizza | Beverages
              7 | Billys Home Made Pizza | Condiments
              7 | Billys Home Made Pizza | Confections
              7 | Billys Home Made Pizza | Dairy Products
              7 | Billys Home Made Pizza | Grains/Cereals
              7 | Billys Home Made Pizza | Meat/Poultry
              7 | Billys Home Made Pizza | Produce
              7 | Billys Home Made Pizza | Seafood
              8 | Ellas Special Salmon   | Beverages
              8 | Ellas Special Salmon   | Condiments
              8 | Ellas Special Salmon   | Confections
              8 | Ellas Special Salmon   | Dairy Products
              8 | Ellas Special Salmon   | Grains/Cereals
              8 | Ellas Special Salmon   | Meat/Poultry
              8 | Ellas Special Salmon   | Produce
              8 | Ellas Special Salmon   | Seafood
              9 | Roberts Rich Spaghetti | Beverages
              9 | Roberts Rich Spaghetti | Condiments
              9 | Roberts Rich Spaghetti | Confections
              9 | Roberts Rich Spaghetti | Dairy Products
              9 | Roberts Rich Spaghetti | Grains/Cereals
              9 | Roberts Rich Spaghetti | Meat/Poultry
              9 | Roberts Rich Spaghetti | Produce
              9 | Roberts Rich Spaghetti | Seafood
             10 | Mias Popular Ice       | Beverages
             10 | Mias Popular Ice       | Condiments
             10 | Mias Popular Ice       | Confections
             10 | Mias Popular Ice       | Dairy Products
             10 | Mias Popular Ice       | Grains/Cereals
             10 | Mias Popular Ice       | Meat/Poultry
             10 | Mias Popular Ice       | Produce
             10 | Mias Popular Ice       | Seafood
(80 rows)

PostgreSQL Exercises

Test Yourself With Exercises

Exercise:

How many rows will be returned if we use CROSS JOIN to join a table with 10 records with a table with 8 records?

The number of returned records will be:

UNION

The UNION operator is used to combine the result-set of two or more queries.

The queries in the union must follow these rules:

  • They must have the same number of columns
  • The columns must have the same data types
  • The columns must be in the same order

Example

Combine products and testproducts using the UNION operator:

SELECT product_id, product_name
FROM products
UNION
SELECT testproduct_id, product_name
FROM testproducts
ORDER BY product_id;

UNION vs UNION ALL

With the UNION operator, if some rows in the two queries returns the exact same result, only one row will be listed, because UNION selects only distinct values.

Use UNION ALL to return duplicate values.

Let's make some changes to the queries, so that we have duplicate values in the result:

Example - UNION

SELECT product_id
FROM products
UNION
SELECT testproduct_id
FROM testproducts
ORDER BY product_id;

Example - UNION ALL

SELECT product_id
FROM products
UNION ALL
SELECT testproduct_id
FROM testproducts
ORDER BY product_id;

GROUP BY

The GROUP BY clause groups rows that have the same values into summary rows, like "find the number of customers in each country".

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

Example

Lists the number of customers in each country:

SELECT COUNT(customer_id), country
FROM customers
GROUP BY country;

GROUP BY With JOIN

The following SQL statement lists the number of orders made by each customer:

Example

SELECT customers.customer_name, COUNT(orders.order_id)
FROM orders
LEFT JOIN customers ON orders.customer_id = customers.customer_id
GROUP BY customer_name;

PostgreSQL Exercises

Test Yourself With Exercises

Exercise:

List the number of customers in each country:

SELECT (customer_id), country FROM customers
;

HAVING

The HAVING clause was added to SQL because the WHERE clause cannot be used with aggregate functions.

Aggregate functions are often used with GROUP BY clauses, and by adding HAVING we can write condition like we do with WHERE clauses.

Example

List only countries that are represented more than 5 times:

SELECT COUNT(customer_id), country
FROM customers
GROUP BY country
HAVING COUNT(customer_id) > 5;

More HAVING Examples

The following SQL statement lists only orders with a total price of 400$ or more:

Example

SELECT order_details.order_id, SUM(products.price)
FROM order_details
LEFT JOIN products ON order_details.product_id = products.product_id
GROUP BY order_id
HAVING SUM(products.price) > 400.00;

Lists customers that have ordered for 1000$ or more:

Example

SELECT customers.customer_name, SUM(products.price)
FROM order_details
LEFT JOIN products ON order_details.product_id = products.product_id
LEFT JOIN orders ON order_details.order_id = orders.order_id
LEFT JOIN customers ON orders.customer_id = customers.customer_id
GROUP BY customer_name
HAVING SUM(products.price) > 1000.00;

EXISTS

The EXISTS operator is used to test for the existence of any record in a sub query.

The EXISTS operator returns TRUE if the sub query returns one or more records.

Example

Return all customers that is represented in the orders table:

SELECT customers.customer_name
FROM customers
WHERE EXISTS (
  SELECT order_id
  FROM orders
  WHERE customer_id = customers.customer_id
);

The result in example above showed that 89 customers had at least one order in the orders table.

NOT EXISTS

To check which customers that do not have any orders, we can use the NOT operator together with the EXISTS operator :

Example

Return all customers that is NOT represented in the orders table:

SELECT customers.customer_name
FROM customers
WHERE NOT EXISTS (
  SELECT order_id
  FROM orders
  WHERE customer_id = customers.customer_id
);

ANY

The ANY operator allows you to perform a comparison between a single column value and a range of other values.

The ANY operator:

  • returns a Boolean value as a result
  • returns TRUE if ANY of the sub query values meet the condition

ANY means that the condition will be true if the operation is true for any of the values in the range.

Example

List products that have ANY records in the order_details table with a quantity larger than 120:

SELECT product_name
FROM products
WHERE product_id = ANY (
  SELECT product_id
  FROM order_details
  WHERE quantity > 120
);

ALL

The ALL operator:

  • returns a Boolean value as a result
  • returns TRUE if ALL of the sub query values meet the condition
  • is used with SELECT, WHERE and HAVING statements

ALL means that the condition will be true only if the operation is true for all values in the range.

Example

List the products if ALL the records in the order_details with quantity larger than 10.

Note: This will of course return FALSE because the quantity column has many different values (not only the value of 10):

SELECT product_name
FROM products
WHERE product_id = ALL (
  SELECT product_id
  FROM order_details
  WHERE quantity > 10
);

CASE

The CASE expression goes through conditions and returns a value when the first condition is met (like an if-then-else statement).

Once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause.

If there is no ELSE part and no conditions are true, it returns NULL.

Example

Return specific values if the price meets a specific condition:

SELECT product_name,
CASE
  WHEN price < 10 THEN 'Low price product'
  WHEN price > 50 THEN 'High price product'
ELSE
  'Normal product'
END
FROM products;

With an Alias

When a column name is not specified for the "case" field, the parser uses case as the column name.

To specify a column name, add an alias after the END keyword.

Example

Same example, but with an alias for the case column::

SELECT product_name,
CASE
  WHEN price < 10 THEN 'Low price product'
  WHEN price > 50 THEN 'High price product'
ELSE
  'Normal product'
END AS "price category"
FROM products;

You can read more about aliases in our .

PostgreSQL Exercises

You can test your PostgreSQL skills with W3Schools' Exercises.

Exercises

We have gathered a variety of PostgreSQL exercises (with answers) for each PostgreSQL Chapter.

Try to solve an exercise by editing some code, or show the answer to see what you've done wrong.

Count Your Score

You will get 1 point for each correct answer. Your score and total score will always be displayed.

Start PostgreSQL Exercises

Good luck!

If you don't know PostgreSQL, we suggest that you read our from scratch.


PostgreSQL Quiz

You can test your PostgreSQL skills with W3Schools' Quiz.

The Test

The test contains 25 questions and there is no time limit.

The test is not official, it's just a nice way to see how much you know about PostgreSQL.

Count Your Score

You will get 1 point for each correct answer. At the end of the Quiz, your total score will be displayed. Maximum score is 25 points.

Start the Quiz

Good luck!

If you do not know PostgreSQL, we suggest that you read our from scratch.


Login
ADS CODE