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.fetch
Note: These instructions last tested with YugaByte DB 1.3
Let's begin!
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.
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 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 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.
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.
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.
SELECT with an IN clauses
In this exercise we will query the shippers table and return only the rows that have a shipper_id of 1, 2, 3 or 4.
SELECT *
FROM shippers
WHERE shipper_id IN (1,2,3,4);
This query should return 4 rows
SELECT with a NOT IN clause
In this exercise we will query the shippers table and return all the rows, except those that have a shipper_id of 3 or 4.
SELECT *
FROM shippers
WHERE shipper_id NOT IN (3,4);
This query should return 4 rows
SELECT with an IN clause in a subquery
In this exercise we will query the orders table and return all the rows using a subquery to find all the orders who have an order_date of 1998-05-06.
SELECT
customer_id
FROM
orders
WHERE
CAST (order_date AS DATE) = '1998-05-06';
This query should return 4 rows
SELECT with BETWEEN
In this exercise we will query the products table and find all the products who have a product_id between 10 and 20.
SELECT product_id,
product_name,
quantity_per_unit
FROM products
WHERE product_id BETWEEN 10 AND 20
This query should return 11 rows
SELECT with NOT BETWEEN
In this exercise we will query the products table and find all the products who have a product_id that is not between 10 and 20.
SELECT product_id,
product_name,
quantity_per_unit
FROM products
WHERE product_id NOT BETWEEN 10 AND 20
This query should return 66 rows
SELECT with a LIKE operator
In this exercise we will query the products table and find all the products whose names have the letter C in them.
SELECT product_id,
product_name,
quantity_per_unit
FROM products
WHERE product_name LIKE '%C%'
This query should return 17 rows
SELECT with a LIKE operator using % and _
In this exercise we will query the products table and find all the products whose names have a single character before the letter E appears in them.
SELECT product_id,
product_name,
quantity_per_unit
FROM products
WHERE product_name LIKE '_e%'
This query should return 5 rows
SELECT with a NOT LIKE operator
In this exercise we will query the products table and find all the products whose names do not start with the letter C.
SELECT product_id,
product_name,
quantity_per_unit
FROM products
WHERE product_name NOT LIKE 'C%'
This query should return 68 rows
SELECT with an ILIKE operator
In this exercise we will query the products table using the ILIKE operator (which acts like the LIKE operator) to find the products that have the letter C in them. In addition, the ILIKE operator matches value case-insensitively.
SELECT product_id,
product_name,
quantity_per_unit
FROM products
WHERE product_name ILIKE '%C%'
This query should return 37 rows
SELECT with a IS NULL operator
In this exercise we will query the customers table and find all the customers who do not have a region assigned to them.
SELECT contact_name,
contact_title,
city,
country,
region
FROM customers
WHERE region IS NULL;
This query should return 60 rows
SELECT with a IS NOT NULL operator
In this exercise we will query the customers table and find all the customers who do have a region assigned to them.
SELECT contact_name,
contact_title,
city,
country,
region
FROM customers
WHERE region IS NOT NULL;
This query should return 31 rows
SELECT with a GROUP BY
In this exercise we will query the products table and group the results by product_id, then product_name, and finally unit_price.
SELECT product_id,
product_name,
unit_price
FROM products
GROUP BY product_id,
product_name,
unit_price;
This query should return 77 rows
SELECT with a GROUP BY and a function
In this exercise we will query the orders table and group the results by employee_id, while only returning the total counts of order_id.
SELECT count(order_id),
employee_id
FROM orders
GROUP BY employee_id;
This query should return 9 rows
SELECT with a HAVING clause
In this exercise we will query the products table, group the results and return only those that have a category_id of "5".
SELECT product_id,
product_name,
unit_price,
category_id
FROM products
GROUP BY product_id,
product_name,
unit_price,
category_id
HAVING category_id=5;
This query should return 7 rows
SELECT with a HAVING clause and a function
In this exercise we will query the products table, group the results and return only those unit_price when multiplied by units_in_stock is greater than $2800.
SELECT product_name,
sum(unit_price * units_in_stock),
units_in_stock
FROM products
GROUP BY product_name,
unit_price,
units_in_stock
HAVING unit_price * units_in_stock > 2800;
This query should return 7 rows
SELECT with a HAVING clause and COUNT
In this exercise we will query the products table, group the results and return only those unit_price greater than 28.
SELECT product_name,
sum(unit_price) units_in_stock
FROM products
GROUP BY product_name,
unit_price,
units_in_stock
HAVING unit_price > 28
This query should return 26 rows
SELECT with a HAVING clause and a less than operator
In this exercise we will query the products table, group the results and return only those whose category_id is less than 4.
SELECT product_id,
product_name,
unit_price,
category_id
FROM products
GROUP BY product_id,
product_name,
unit_price,
category_id
HAVING category_id < 4;
This query should return 37 rows
SELECT with a UNION
In this exercise we will query the suppliers and customers table and combine the result sets while removing duplicates it finds.
SELECT company_name
FROM suppliers
UNION
SELECT company_name
FROM customers;
This query should return 120 rows
SELECT with a UNION ALL
In this exercise we will query the suppliers and customers tables and combine the result sets without removing duplicates if they exist.
SELECT company_name
FROM suppliers
UNION ALL
SELECT company_name
FROM customers;
This query should return 120 rows
SELECT with a UNION and ORDER BY
In this exercise we will query the suppliers and customers tables, combine the result sets without removing duplicates if they exist and ordering them in descending order.
SELECT company_name
FROM suppliers
UNION ALL
SELECT company_name
FROM customers
ORDER BY company_name DESC
This query should return 120 rows
SELECT with an INTERSECT
In this exercise we will query the orders and country tables and return the result sets that are found in both tables.
SELECT ship_country
FROM orders INTERSECT
SELECT country
FROM suppliers;
This query should return 12 rows
SELECT with an INTERSECT AND ORDER BY
In this exercise we will query the orders and country tables, return the result sets that are found in both tables and oder them by ship_country.
SELECT ship_country
FROM orders INTERSECT
SELECT country
FROM suppliers
ORDER BY ship_country;
This query should return 12 rows
SELECT with an EXCEPT
In this exercise we will query the orders and country tables and return the distinct rows from the first query that are not in the output of the second.
SELECT ship_country
FROM orders
EXCEPT
SELECT country
FROM suppliers;
This query should return 9 rows
SELECT with a GROUP BY and GROUPING SETS
In this exercise we will query the suppliers table and group the number_of_people results into city, country and contact_title sets.
SELECT contact_title,
count(contact_title) AS number_of_people,
city,
country
FROM suppliers
GROUP BY GROUPING
SETS (city,
country,
contact_title);
This query should return 60 rows
SELECT with a GROUP BY and CUBE
In this exercise we will query the products table, group the results and then generate multiple grouping sets from the results.
SELECT product_id, supplier_id, product_name,
SUM(units_in_stock)
FROM products
GROUP BY product_id, cube(product_id, supplier_id)
This query should return 154 rows
SELECT with a GROUP BY and a partial CUBE
In this exercise we will query the products table, group the results and then generate a subset of grouping sets from the results.
SELECT product_id, supplier_id, product_name,
SUM(units_in_stock)
FROM products
GROUP BY product_id, cube(supplier_id)
This query should return 200 rows
SELECT with a GROUP BY and ROLLUP
In this exercise we will query the products table, group the results by product_id and then generate multiple grouping sets from the results using ROLLUP.
SELECT product_id,
supplier_id,
product_name,
SUM(units_in_stock)
FROM products
GROUP BY product_id, ROLLUP(supplier_id, product_id);
This query should return 154 rows
SELECT with a GROUP BY and PARTIAL ROLLUP
In this exercise we will query the products table, group the results by product_id and then generate multiple grouping sets from the results using ROLLUP.
SELECT product_id,
supplier_id,
product_name,
SUM(units_in_stock)
FROM products
GROUP BY product_id,
ROLLUP(supplier_id);
This query should return 154 rows
SELECT with a Subquery and an IN operator
In this exercise we will query the products table and specify a subquery in the WHERE clause which limits the results to those who have a supplier_id greater than 5.
SELECT product_id,
product_name,
unit_price
FROM products
WHERE unit_price >
(SELECT avg(unit_price)
FROM products
WHERE supplier_id > 5)
This query should return 25 rows
SELECT with a Subquery and an IN operator
In this exercise we will query the products table and specify a subquery in the WHERE clause which limits the results to those who have a quantity greater than 100.
SELECT product_id,
product_name,
quantity_per_unit
FROM products
WHERE product_id IN
(SELECT product_id
FROM order_details
WHERE quantity > 100)
This query should return 12 rows
SELECT with a Subquery and an EXISTS operator
In this exercise we will query the customers table and specify a subquery in the WHERE clause which limits the results to those who EXIST in both the customers and suppliers tables.
SELECT company_name,
contact_name,
phone,
fax
FROM customers
WHERE EXISTS
(SELECT a.country,
b.country
FROM customers AS a,
suppliers AS b
WHERE a.country=b.country)
This query should return 12 rows
SELECT with a Subquery and an IN operator
In this exercise we will query the products table and specify a subquery in the WHERE clause which limits the results to suppliers from 'Japan'.
SELECT product_name
FROM products
WHERE supplier_id=any
(SELECT supplier_id
FROM suppliers
WHERE country='Japan');
This query should return 6 rows
SELECT with an ALL Operator
In this exercise we will query the products table and specify a subquery in the WHERE clause which limits the results to suppliers with a supplier_id of 7.
SELECT product_name,
product_id,
supplier_id
FROM products
WHERE supplier_id=all
(SELECT supplier_id
FROM suppliers
WHERE supplier_id=7);
This query should return 5 rows
INSERT one row
In this exercise we will insert one row into the shippers table.
INSERT INTO shippers
VALUES (7, 'UPS', '(800) 888-8888');
This query should insert 1 row
INSERT multiple rows
In this exercise we will insert multiple rows into the shippers table.
INSERT
INTO
shippers (shipper_id,
company_name,
phone)
VALUES (9,'United Postal Service','(800) 888-8889'),
(10,'Le Poste','(800) 888-8810'),
(11,'Deutsche Post','(800) 888-8811'),
(12,'Royal Mail','(800) 888-8812');
This query should insert 4 rows
INSERT with a DEFAULT value
In this exercise we will first ALTER the shippers table to have defauly values on several columns. Next, we'll INSERT data into the table making use of the default values.
ALTER TABLE shippers
ALTER phone
SET DEFAULT '+978 93937650'
ALTER TABLE shippers
ALTER company_name
SET DEFAULT 'Adidas Sports'
ALTER TABLE shippers
ALTER shipper_id
SET DEFAULT 10
INSERT INTO shippers (shipper_id, company_name, phone)
VALUES ('15','Nelson Technologies', DEFAULT);
INSERT INTO shippers (shipper_id, company_name, phone)
VALUES (DEFAULT, 'Amazon', '+91 968611321');
INSERT INTO shippers (shipper_id, company_name, phone)
VALUES ('43', DEFAULT, '+53 456558521');
This query should insert 3 rows
INSERT with data from another table
In this exercise we will insert multiple rows into the shippers table by first selecting data from the suppliers table.
INSERT INTO shippers
SELECT supplier_id,
company_name,
phone
FROM suppliers
WHERE supplier_id = 24
This query should insert 1 row
INSERT with a RETURNING clause
In this exercise we will insert a row into the shippers table and return the last shipper_id .
INSERT INTO shippers (shipper_id, company_name, phone)
VALUES ('52', 'Flipkart', '+91 8861009134') RETURNING shipper_id;
This query should insert and return 1 row
UPDATE with a WHERE clause
In this exercise we will update the customers table with a new company_name where the customer_id equals FAMIA.
UPDATE customers
SET company_name='Family Agriculture'
WHERE customer_id='FAMIA';
This query should update 1 row
UPDATE all rows in a table
In this exercise we will update the shippers table and set the phone number for all the companies to '1-999-999-9999'.
UPDATE shippers
SET phone='1-999-999-9999'
This query should update 16 rows
DELETE with a WHERE clause
In this exercise we will create a simple table, add values and delete a specific row.
CREATE TABLE employeestest (
employee_id smallint not NULL,
last_name character varying(20) NOT NULL,
first_name character varying(10) NOT NULL
);
INSERT into employeestest
VALUES
(1, 'Smith', 'Bill'),
(2, 'McKay', 'Sally');
DELETE FROM employeestest WHERE employee_id = '1';
This final query should delete 1 row
DELETE all rows
In this exercise we will delete all the rows in the employeestest table.
DELETE FROM employeestest;
This query should delete all rows
SELECT with an EXISTS Operator
In this exercise we select data from the customers table that meets the conditions of our subquery using the EXISTS operator.
SELECT company_name,
contact_name,
phone,
fax
FROM customers
WHERE EXISTS
(SELECT a.country,
b.country
FROM customers AS a,
suppliers AS b
WHERE a.country=b.country)
This query should return 91 rows
SELECT with a NOT EXISTS Operator
In this exercise we select data from the customers table that meets the conditions of our subquery using the NOT EXISTS operator. From the result we can see that all countries are represented in the customers and suppliers tables.
SELECT company_name,
contact_name,
phone,
fax
FROM customers
WHERE NOT EXISTS
(SELECT a.country,
b.country
FROM customers AS a,
suppliers AS b
WHERE a.country=b.country);
This query should return no rows
SELECT with EXISTS and NULL Operators
In this exercise we select data from the customers table that meets the conditions of our subquery using the EXISTS and NULL operators.
SELECT company_name,
contact_name,
phone,
fax
FROM customers
WHERE EXISTS
(SELECT NULL);
This query should return 91 rows
SELECT with EXISTS and AND Operators
In this exercise we select data from the products table that meets the conditions of our subquery using the EXISTS and AND operators.
SELECT product_id,
product_name,
quantity_per_unit,
unit_price
FROM products a
WHERE EXISTS
(SELECT a.supplier_id,
b.supplier_id
FROM suppliers AS b
WHERE a.supplier_id=b.supplier_id
AND unit_price > 30)
This query should return 24 rows
UPDATE with an EXISTS Operator
In this exercise we UPDATE data in the suppliers table that meets the conditions of our subquery using the EXISTS operator.
UPDATE products
SET product_name=
(SELECT suppliers.company_name
FROM suppliers
WHERE products.product_id = suppliers.supplier_id)
WHERE EXISTS
(SELECT 1
FROM suppliers
WHERE products.product_id = suppliers.supplier_id);
This query should update 29 rows
INSERT ON CONFLICT
In this exercise we will insert data into the customers table specifying that if there is a conflict on customer_id to execute a DO UPDATE statement.
INSERT INTO customers
VALUES ('ALFKI', 'Alfreds Futterkiste Inc.', 'Maria Anders', 'Sales Representative', 'Obere Str. 57', 'Berlin', NULL, '12209', 'Germany', '030-0074321', '030-0076545') ON conflict (customer_id) DO
UPDATE
SET company_name = EXCLUDED.company_name;
This statement should update one row
Create a View
In this exercise we will create a view using data from the products table.
CREATE VIEW suppliers_by_id AS
SELECT product_id,
product_name,
supplier_id
FROM products
WHERE supplier_id > 5;
Dropping a View
In this exercise we will drop the view we just created.
DROP VIEW suppliers_by_id;
Create an Updatable View
In this exercise we will create an updatable view.
CREATE VIEW us_states_view AS
SELECT *
FROM us_states;
DELETE
FROM us_states_view
WHERE state_id > 4;
Like what you see? Don't forget to star us!