YSQL Tutorial: Fundamentals

In this tutorial you’ll learn how to use YugaByte DB’s PostgreSQL-compatible YSQL interface to query data from the Northwind sample database using SELECT, FROM, ORDER BY, LIMIT and other basic clauses and operators. For detailed information concerning specific clauses or operators, please refer to the official PostgreSQL documentation.

To start, make sure you have all the necessary prerequisites and have logged into the YSQL shell.

Note: These instructions last tested with YugaByte DB 1.3

Let's begin!

SELECT

SELECT data from one column

In this exercise we will select all the data in the company_names column from the customers table.

select company_name from customers;

The query should return 91 rows.

SELECT data from multiple columns

In this exercise we will select all the data in three columns from the employees table.

select employee_id,first_name,last_name,birth_date from employees;

The query should return 9 rows.

SELECT data from all the columns and rows in a table

In this exercise we will select all the data, from all columns in the order_details table.

select * from order_details;

The query should return 2155 rows.

SELECT with an expression

In this exercise we will combine the first_name and last_name columns to give us full names, along with their titles from the employees table.

select first_name || '' || last_name as full_name,title from employees;

The query should return 9 rows.

SELECT with an expression, but without a FROM clause

In this exercise we will use an expression, but omit specifying a table because it doesn't require one.

SELECT 5 * 3 AS result;

The query should return 1 row with a result of 15.

SELECT with a column alias

In this exercise we will use the alias title for the contact_title column and output all the rows.

SELECT contact_title AS title FROM customers;

The query should return 91 rows.

SELECT with a table alias

In this exercise we will use the alias details for the order_details table and output all the rows.

SELECT product_id, discount FROM order_details AS details;

The query should return 2155 rows.

ORDER BY

SELECT with an ascending ORDER BY

In this exercise we sort employees by first_name in ascending order.

SELECT first_name,
       last_name,
       title,
       address,
       city
FROM employees
ORDER BY first_name ASC;

The query should return 9 rows.

SELECT with an descending ORDER BY

In this exercise we sort employees by first_name in descending order.

SELECT first_name,
       last_name,
       title,
       address,
       city
FROM employees
ORDER BY first_name DESC;

The query should return 9 rows.

SELECT with a ascending and descending ORDER BYs

In this exercise we sort employees by first_name in ascending order and then by last name in descending order.

SELECT first_name,
       last_name
FROM employees
ORDER BY first_name ASC,
         last_name DESC;

The query should return 9 rows

SELECT DISTINCT

SELECT with DISTINCT on one column

In this exercise we query the orders table and return only the distinct values in the order_id column.

SELECT DISTINCT order_id
FROM orders;

The query should return 830 rows

SELECT with DISTINCT including multiple columns

In this exercise we query the orders table and return only the distinct values based on combining the specified columns.

SELECT DISTINCT order_id,
                customer_id,
                employee_id
FROM orders;

The query should return 830 rows

SELECT with DISTINCT ON expression

In this exercise we query the orders table and ask to keep just the first row of each group of duplicates.

SELECT DISTINCT ON (employee_id)employee_id AS id_number,
                   customer_id
FROM orders
ORDER BY id_number,
         customer_id;

The query should return 9 rows

WHERE

WHERE clause with an equal = operator

In this exercise we'll query the orders table and return just the rows that equal the employee_id of 8.

SELECT order_id,
       customer_id,
       employee_id,
       order_date
FROM orders
WHERE employee_id=8;

This query should return 104 rows.

WHERE clause with an AND operator

In this exercise we'll query the orders table and return just the rows that have an employee_id of 8 and a customer_id equal to "FOLKO".

SELECT order_id,
       customer_id,
       employee_id,
       order_date
FROM orders
WHERE employee_id=8
  AND customer_id= 'FOLKO';

This query should return 6 rows.

WHERE clause with an OR operator

In this exercise we'll query the orders table and return just the rows that have an employee_id of 2 or 1.

SELECT order_id,
       customer_id,
       employee_id,
       order_date
FROM orders
WHERE employee_id=2
  OR employee_id=1;

This query should return 219 rows.

WHERE clause with an IN operator

In this exercise we'll query the order_ details table and return just the rows with an order_id of 10360 or 10368.

SELECT *
FROM order_details
WHERE order_id IN (10360,
                   10368);

This query should return 9 rows.

WHERE clause with a LIKE operator

In this exercise we'll query the customers table and return just the rows that have a company_name that starts with the letter "F".

SELECT customer_id,
       company_name,
       contact_name,
       city
FROM customers
WHERE company_name LIKE 'F%';

This query should return 8 rows.

WHERE clause with a BETWEEN operator

In this exercise we'll query the orders table and return just the rows that have an order_id between 10,985 and 11,000.

SELECT order_id,
       customer_id,
       employee_id,
       order_date,
       ship_postal_code
FROM orders
WHERE order_id BETWEEN 10985 AND 11000;

This query should return 16 rows.

WHERE clause with a not equal <> operator

In this exercise we'll query the employees table and return just the rows where the employee_id is not eqal to "1".

SELECT first_name,
       last_name,
       title,
       address,
       employee_id
FROM employees
WHERE employee_id <> 1;

This query should return 8 rows.

LIMIT

SELECT with a LIMIT clause

In this exercise we'll query the products table and return just the first 12 rows.

SELECT product_id,
       product_name,
       unit_price
FROM products
LIMIT 12;

This query should return 12 rows.

SELECT with LIMIT and OFFSET clauses

In this exercise we'll query the products table and skip the first 4 rows before selecting the next 12.

SELECT product_id,
       product_name,
       unit_price
FROM products
LIMIT 12
OFFSET 4;

This query should return 12 rows.

SELECT with LIMIT and ORDER BY clauses

In this exercise we'll query the products table, order the results in a descending order by product_id and limit the rows returned to 12.

SELECT product_id,
       product_name,
       unit_price
FROM products
ORDER BY product_id DESC
LIMIT 12;

This query should return 12 rows.

FETCH

SELECT with FETCH and ORDER BY clauses

In this exercise we'll query the customers table and order the results by _company_name in ascending order, while limiting the rows returned to 7.

SELECT customer_id,
       company_name,
       contact_name,
       contact_title
FROM customers
ORDER BY company_name ASC FETCH NEXT 7 ROWS ONLY;

This query should return 7 rows.

SELECT with FETCH, OFFSET and ORDER BY clauses

In this exercise we'll query the customers table and order the results by _company_name in ascending order, while limiting the rows returned to the 7 that come after the first 2.

SELECT customer_id,
       company_name,
       contact_name,
       contact_title
FROM customers
ORDER BY company_name ASC
OFFSET 2 FETCH NEXT 7 ROWS ONLY;

This query should return 7 rows.

IN

SELECT with an IN clauses

Query

Results

SELECT with a NOT IN clause

Query

Results

SELECT with an IN clause in a subquery using CAST

Query

Results

BETWEEN

SELECT with BETWEEN

Query

Results

SELECT with NOT BETWEEN

Query

Results

LIKE

SELECT with a LIKE operator

Query

Results

SELECT with a LIKE operator using % and _

Query

Results

SELECT with a NOT LIKE operator

Query

Results

SELECT with an ILIKE operator

Query

Results

IS NULL

SELECT with a IS NULL operator

Query

Results

SELECT with a IS NOT NULL operator

Query

Results