DbSchema PostgreSQL starts when you begin the lesson

Lesson 1

Tables, rows and columns

For readers who have never written SQL. Nothing to install: every example runs in this page.

After this lesson you can say what a table, a row and a column are, and run your first query on a real database.
  • What a database holds
  • Tables, rows and columns
  • Column types
  • Your first query
  • When a name is wrong
  • 4 exercises

Start the lesson

Every query runs on real PostgreSQL, in your browser. It downloads once, about 6 MB, then starts in a few seconds.

For a reference page on the same statements, read SQL Tutorial for Beginners with Real Examples.

The database

A database keeps its data in tables

🔗 Foreign Key orders_customer_id_fkey orders ➔ customers ( customer_id ) 🔗 Foreign Key orders_customer_id_fkey orders ➔ customers ( customer_id ) customer_id 🔗 Foreign Key orders_employee_id_fkey orders ➔ employees ( employee_id ) 🔗 Foreign Key orders_employee_id_fkey orders ➔ employees ( employee_id ) employee_id 🔗 Foreign Key order_items_order_id_fkey order_items ➔ orders ( order_id ) 🔗 Foreign Key order_items_order_id_fkey order_items ➔ orders ( order_id ) order_id 🔗 Foreign Key order_items_product_id_fkey order_items ➔ products ( product_id ) 🔗 Foreign Key order_items_product_id_fkey order_items ➔ products ( product_id ) product_id 🔗 Foreign Key employees_department_id_fkey employees ➔ departments ( department_id ) 🔗 Foreign Key employees_department_id_fkey employees ➔ departments ( department_id ) department_id 🔗 Foreign Key employees_manager_id_fkey employees ➔ employees ( manager_id -> employee_id ) 🔗 Foreign Key employees_manager_id_fkey employees ➔ employees ( manager_id -> employee_id ) manager_id Table public.customers ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The people who buy from the shop.customers 🔑 Pk customers_pkey ( customer_id ) ⧉ customer_id * int4 ↙ orders( customer_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The number that identifies the customer.customer_id 🔗 Referred by orders ( customer_id ) ⧉ first_name * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The first name of the customer.first_name ⧉ last_name * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The last name of the customer.last_name 🔍 Unq customers_email_key ( email ) ⧉ email * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The email address of the customer. No two customers share one.email ⧉ phone text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The phone number of the customer. NULL when the customer gave none.phone ⧉ country * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The country the customer lives in.country ⧉ signup_date * date ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The day the customer created an account.signup_date Table public.orders ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ One purchase by one customer. Its products are the rows of order_items.orders 🔑 Pk orders_pkey ( order_id ) ⧉ order_id * int4 ↙ order_items( order_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The number that identifies the order.order_id 🔗 Referred by order_items ( order_id ) ⧉ customer_id * int4 ↗ customers( customer_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The customer who placed the order: a customer_id of customers.customer_id 🔗 References customers ( customer_id ) ⧉ employee_id int4 ↗ employees( employee_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The employee who took the order: an employee_id of employees. NULL for an order placed online.employee_id 🔗 References employees ( employee_id ) ⧉ order_date * date ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The day the order was placed.order_date ⧉ status * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ Where the order stands: pending, shipped, delivered or cancelled.status Table public.order_items ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The lines of an order: one row for each product in it.order_items 🔑 Pk order_items_pkey ( order_id, line_no ) ⧉ order_id * int4 ↗ orders( order_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The order this line belongs to: an order_id of orders.order_id 🔗 References orders ( order_id ) 🔑 Pk order_items_pkey ( order_id, line_no ) ⧉ line_no * int4 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The number of the line within its order. With order_id, it identifies the line.line_no ⧉ product_id * int4 ↗ products( product_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The product on this line: a product_id of products.product_id 🔗 References products ( product_id ) ⧉ quantity * int4 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ How many pieces of the product were ordered.quantity ⧉ unit_price * numeric(10,2) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The price of one piece on this line, kept with the order so that a later change of products.price leaves it as it was.unit_price Table public.products ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ What the shop sells.products 🔑 Pk products_pkey ( product_id ) ⧉ product_id * int4 ↙ order_items( product_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The number that identifies the product.product_id 🔗 Referred by order_items ( product_id ) 🔍 Unq products_name_key ( name ) ⧉ name * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The name of the product. No two products share a name.name ⧉ category * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The group the product belongs to, such as Accessories or Displays.category ⧉ price * numeric(10,2) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The current price, with two decimals. Always more than 0.price ⧉ stock * int4 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ How many pieces are in the warehouse. Never below 0.stock Table public.employees ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The people who work for the shop.employees 🔑 Pk employees_pkey ( employee_id ) ⧉ employee_id * int4 ↙ employees( manager_id ) ↙ orders( employee_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The number that identifies the employee.employee_id 🔗 Referred by employees ( manager_id -> employee_id ) Referred by orders ( employee_id ) ⧉ first_name * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The first name of the employee.first_name ⧉ last_name * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The last name of the employee.last_name 🔍 Unq employees_email_key ( email ) ⧉ email * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The work email address. No two employees share one.email ⧉ department_id * int4 ↗ departments( department_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The department the employee works in: a department_id of departments.department_id 🔗 References departments ( department_id ) ⧉ manager_id int4 ↗ employees( employee_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The employee this employee reports to: an employee_id of this same table. NULL for the head of a department.manager_id 🔗 References employees ( manager_id -> employee_id ) ⧉ hire_date * date ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The day the employee started.hire_date ⧉ salary * numeric(10,2) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The yearly salary, with two decimals.salary Table public.departments ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The departments of the shop. Every employee works in one of them.departments 🔑 Pk departments_pkey ( department_id ) ⧉ department_id * int4 ↙ employees( department_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The number that identifies the department.department_id 🔗 Referred by employees ( department_id ) 🔍 Unq departments_name_key ( name ) ⧉ name * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The name of the department, such as Sales. No two departments share a name.name
The practice database, drawn by DbSchema.

Every lesson uses the same database: a small shop. Each box in the diagram is a table. Customers place orders, each order lists the products it holds, and employees in departments take the orders. The lines between the boxes link one table to another, which lesson 8 explains.

Hover a table or a column in the diagram to read its comment: what it holds, and what it links to. The slides that query a table show its diagram too.

The idea

A table is a grid of rows and columns

The departments table with its two columns, department_id and name. The column name is outlined, the row of department 3, Engineering, is highlighted, and the value Support is marked.

Every column has a name, such as name, and every row is one thing: here, one department. Where a row meets a column there is exactly one value.

A value can also be missing. SQL calls a missing value NULL, and lesson 4 is about it.

The idea

Every column holds one kind of value

Table public.products ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ What the shop sells.products 🔑 Pk products_pkey ( product_id ) ⧉ product_id * int4 ↙ order_items( product_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The number that identifies the product.product_id 🔗 Referred by order_items ( product_id ) 🔍 Unq products_name_key ( name ) ⧉ name * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The name of the product. No two products share a name.name ⧉ category * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The group the product belongs to, such as Accessories or Displays.category ⧉ price * numeric(10,2) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The current price, with two decimals. Always more than 0.price ⧉ stock * int4 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ How many pieces are in the warehouse. Never below 0.stock
Hover a table or a column to read its comment.

This is how the shop's products table was created:

create table products (
    product_id integer primary key,
    name       text not null unique,
    category   text not null,
    price      numeric(10, 2) not null check (price > 0),
    stock      integer not null check (stock >= 0)
);

Each line names a column and its type: integer for whole numbers, text for words, and numeric(10, 2) for amounts with two decimals. PostgreSQL refuses a value of the wrong type, so a price is always a number. The rest of each line comes back in lesson 8.

Try it

Ask for a whole table

Table public.departments ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The departments of the shop. Every employee works in one of them.departments 🔑 Pk departments_pkey ( department_id ) ⧉ department_id * int4 ↙ employees( department_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The number that identifies the department.department_id 🔗 Referred by employees ( department_id ) 🔍 Unq departments_name_key ( name ) ⧉ name * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The name of the department, such as Sales. No two departments share a name.name
Hover a table or a column to read its comment.
select * from departments;

The answer is itself a table: 2 columns and 7 rows, one per department.

The idea

A query asks for rows, and the answer is a table

The query select star from products. The star means every column, products is the table. Under it, the answer: a table with the columns product_id, name, category, price and stock, and its first three rows.

select starts a query. The star means every column, and from names the table to read. PostgreSQL sends back a new table, which the page shows under the editor. A query only reads: the table stays as it was.

Try it

Look at the products

Table public.products ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ What the shop sells.products 🔑 Pk products_pkey ( product_id ) ⧉ product_id * int4 ↙ order_items( product_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The number that identifies the product.product_id 🔗 Referred by order_items ( product_id ) 🔍 Unq products_name_key ( name ) ⧉ name * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The name of the product. No two products share a name.name ⧉ category * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The group the product belongs to, such as Accessories or Displays.category ⧉ price * numeric(10,2) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The current price, with two decimals. Always more than 0.price ⧉ stock * int4 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ How many pieces are in the warehouse. Never below 0.stock
Hover a table or a column to read its comment.
select * from products;

26 rows, and every row has the same five columns.

Watch out

A table name must be spelled exactly

The table is called products, with an s. Without it, PostgreSQL finds no such table:

select * from product;

ERROR: relation "product" does not exist

PostgreSQL calls a table a relation, which is the word in its message. The Tables button lists every table and its columns, so you never have to remember a name.

Exercise 1 of 4

Show every row and every column of the departments table.

Table public.departments ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The departments of the shop. Every employee works in one of them.departments 🔑 Pk departments_pkey ( department_id ) ⧉ department_id * int4 ↙ employees( department_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The number that identifies the department.department_id 🔗 Referred by employees ( department_id ) 🔍 Unq departments_name_key ( name ) ⧉ name * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The name of the department, such as Sales. No two departments share a name.name
Hover a table or a column to read its comment.
Expected result

It shows once PostgreSQL is ready.


    
Each check sends the exercise, your SQL and any PostgreSQL error to dbschema.com, without your IP address, so that the hints can improve.

    
    

        Show answer
        
select * from departments

    






    
Exercise 2 of 4

    




Show every row and every column of the employees table.












  

    
        
    
    
        
        
        
        
        
        
        
        
        
        
        
        
        
    
    
        
        
    
    
        
        
    
    
        
        
        
        
    
    
         
         
        
             
             
        
    
    
        
        
    
    
        
        
    
    
        
        
        
        
        
        
        
        
        
        
        
        
        
    

  
    
    
    
  
  
    
    
    
  
  
    
    
    
    
  
  
    
    
  
  
    
  
  
    
  
  
      
      
  
  
      
      
  







🔗  Foreign Key employees_manager_id_fkey
employees ➔ employees ( manager_id -> employee_id )

🔗  Foreign Key employees_manager_id_fkey
employees ➔ employees ( manager_id -> employee_id )

manager_id




Table public.employees
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      The people who work for the shop.employees
  🔑  Pk employees_pkey ( employee_id ) ⧉  employee_id
     * int4
 ↙ employees( manager_id )
 ↙ orders( employee_id )
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      The number that identifies the employee.employee_id
🔗  Referred by employees ( manager_id -> employee_id ) 
Referred by orders ( employee_id ) 
  ⧉  first_name
     * text
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      The first name of the employee.first_name
  ⧉  last_name
     * text
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      The last name of the employee.last_name
  🔍  Unq employees_email_key ( email ) ⧉  email
     * text
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      The work email address. No two employees share one.email
  ⧉  department_id
     * int4
 ↗ departments( department_id )
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      The department the employee works in: a department_id of departments.department_id
🔗  References departments ( department_id ) 
  ⧉  manager_id
     int4
 ↗ employees( employee_id )
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      The employee this employee reports to: an employee_id of this same table. NULL for the head of a department.manager_id
🔗  References employees ( manager_id -> employee_id ) 
  ⧉  hire_date
     * date
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      The day the employee started.hire_date
  ⧉  salary
     * numeric(10,2)
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      The yearly salary, with two decimals.salary


Hover a table or a column to read its comment.



    

    

        Expected result
        
It shows once PostgreSQL is ready.

    

    

    
Each check sends the exercise, your SQL and any PostgreSQL error to dbschema.com, without your IP address, so that the hints can improve.

    
    

        Show answer
        
select * from employees

    






    
Exercise 3 of 4

    




How many rows does the customers table have? Run a query here to find out, then pick the answer.





    

    

        
        
        
        
    

    
Each answer sends the exercise and whether the answer was right to dbschema.com, without your IP address.

    





    
Exercise 4 of 4

    




Which column of the orders table holds the employee who took the order? The Tables button lists the columns of every table.



    

    

        
        
        
        
    

    
Each answer sends the exercise and whether the answer was right to dbschema.com, without your IP address.