Bookmark this page

SQL PlayGround

Quick referece for SQL with practice options

Dataset

We are working with a dataset for a small, fictional animal shelter. We have tables for animals, employees, adoptions, and donations.

Table of content

Basic Queries
JOINS
Aggregates
Group By
Having
Sub Queries
Data Operations
Data Definition Language (DDL)
SQL Views

Basic Queries

SELECT statement

Select all rows and columns from the Animals table.

SELECT * FROM Animals;

SELECT * FROM Animals;

Select only some columns (names and species) from the Animals table.

SELECT name, species FROM Animals;

SELECT name, species FROM Animals;

SQL aliases are used to give a table, or a column in a table, a temporary name . An alias is created with the AS keyword in Animals table.

SELECT animal_id AS ID FROM Animals;

SELECT animal_id AS ID FROM Animals;

Filtering using WHERE clause

Select only the rows that meet filter conditions.

SELECT * FROM Animals WHERE status = 'available';

SELECT * FROM Animals WHERE status = 'available';

SELECT * FROM Animals WHERE status = 'available' AND age > 1;

SELECT * FROM Animals WHERE status = 'available' AND age > 1;

SELECT first_name, last_name, position, hire_date FROM Employees WHERE hire_date > '2022-01-01';

SELECT first_name, last_name, position, hire_date FROM Employees WHERE hire_date > '2022-01-01';

Use LIKE to get records with partial string match.

SELECT * FROM Animals WHERE name LIKE 'L%';

SELECT * FROM Animals WHERE name LIKE 'L%';

Use IN to get records matching a column against a number of values.

SELECT * FROM Animals WHERE breed IN ('Labrador', 'Bulldog', 'German Shepherd');

SELECT * FROM Animals WHERE breed IN ('Labrador', 'Bulldog', 'German Shepherd');

Use BETWEEN to get records matching a column against a range of values.

SELECT * FROM Animals WHERE age BETWEEN 2 AND 4;

SELECT * FROM Animals WHERE age BETWEEN 2 AND 4;

Use IS NULL to get records missing value in a column.

SELECT * FROM Animals WHERE age IS NULL;

SELECT * FROM Animals WHERE age IS NULL;

Use NOT operator to do negative check on rows.

SELECT * FROM Animals WHERE species IS NOT 'Cat' AND name LIKE 'L%';

SELECT * FROM Animals WHERE species IS NOT 'Cat' AND name LIKE 'L%';

Sorting using ORDER BY

You can sort your query results to ORDER BY. By default sort order is Ascending (ASC). You can sort in the Descending (DESC) order also.

SELECT * FROM Animals WHERE species = 'Dog' ORDER BY name ASC;

SELECT * FROM Animals WHERE species = 'Dog' ORDER BY name ASC;

SELECT * FROM Animals WHERE species = 'Dog' ORDER BY name DESC;

SELECT * FROM Animals WHERE species = 'Dog' ORDER BY name DESC;

LIMIT and OFFSET to limit or skip records.

Limit the results returned from the Animals table to be 4.

SELECT * FROM Animals LIMIT 4;

SELECT * FROM Animals LIMIT 4;

Skipping the first two records from the above limited records.

SELECT * FROM Animals LIMIT 2 OFFSET 2;

SELECT * FROM Animals LIMIT 2 OFFSET 2;

JOINS

INNER Join

Join Animals and Adoptions to list adopted animals and their adopters. (Simple JOIN refers to INNER JOIN itself)

SELECT a.name, a.species, ad.adopter_name, ad.adoption_date
  FROM Animals a
  JOIN Adoptions ad ON a.animal_id = ad.animal_id;

SELECT a.name, a.species, ad.adopter_name, ad.adoption_date
  FROM Animals a
  JOIN Adoptions ad ON a.animal_id = ad.animal_id;

LEFT Join

Get a list of all animals, including those that have not been adopted yet.

SELECT a.name, a.species, a.breed, ad.adopter_name, ad.adoption_date
  FROM Animals a
  LEFT JOIN Adoptions ad ON a.animal_id = ad.animal_id;

SELECT a.name, a.species, a.breed, ad.adopter_name, ad.adoption_date
  FROM Animals a
  LEFT JOIN Adoptions ad ON a.animal_id = ad.animal_id;

RIGHT Join

Get a list of all adoptions, and include animals even if they were not found in the Animals table.

SELECT ad.adopter_name, ad.adoption_date, a.name, a.species, a.breed
  FROM Adoptions ad
  LEFT JOIN Animals a ON ad.animal_id = a.animal_id;

SELECT ad.adopter_name, ad.adoption_date, a.name, a.species, a.breed
  FROM Adoptions ad
  LEFT JOIN Animals a ON ad.animal_id = a.animal_id;

FULL Join or FULL OUTER Join

Join Animals and Adoptions to get a list of all adoptions and all Animals. (UNION of LEFT and RIGHT JOIN can imitate FULL JOIN when it is not supported by certain SQL DBMS like MySQL, SQLite etc...)

SELECT a.name, a.species, a.breed, ad.adopter_name, ad.adoption_date
  FROM Animals a
  LEFT JOIN Adoptions ad ON a.animal_id = ad.animal_id
  UNION ALL
  SELECT a.name, a.species, a.breed, ad.adopter_name, ad.adoption_date
  FROM Adoptions ad
  LEFT JOIN Animals a ON ad.animal_id = a.animal_id

SELECT a.name, a.species, a.breed, ad.adopter_name, ad.adoption_date
  FROM Animals a
  LEFT JOIN Adoptions ad ON a.animal_id = ad.animal_id
  UNION ALL
  SELECT a.name, a.species, a.breed, ad.adopter_name, ad.adoption_date
  FROM Adoptions ad
  LEFT JOIN Animals a ON ad.animal_id = a.animal_id

Aggregates

Count the number of available animals.

SELECT COUNT(*) AS available_animals
  FROM Animals
  WHERE status = 'available';

SELECT COUNT(*) AS available_animals
  FROM Animals
  WHERE status = 'available';

Calculate the average salary of employees.

SELECT AVG(salary) AS average_salary
  FROM Employees;

SELECT AVG(salary) AS average_salary
  FROM Employees;

Sum the total donations received.

SELECT SUM(amount) AS total_donations
  FROM Donations;

SELECT SUM(amount) AS total_donations
  FROM Donations;

Group By

Count the number of animals by species.

SELECT a.name, a.species, ad.adopter_name, ad.adoption_date
  FROM Animals a
  JOIN Adoptions ad ON a.animal_id = ad.animal_id;

SELECT a.name, a.species, ad.adopter_name, ad.adoption_date
  FROM Animals a
  JOIN Adoptions ad ON a.animal_id = ad.animal_id;

Having

Filtering using HAVING clause

Display the position where the sum of salaries is 80,000 or more. The HAVING clause was added to SQL because the WHERE keyword cannot be used with aggregate functions.

SELECT position , SUM(salary) FROM Employees GROUP BY position HAVING SUM(salary) >= 80000;

SELECT position , SUM(salary) FROM Employees GROUP BY position HAVING SUM(salary) >= 80000;

Sub Queries

Find the employee with the highest salary.

SELECT first_name, last_name, salary
  FROM Employees
  WHERE salary = (SELECT MAX(salary) FROM Employees);

SELECT first_name, last_name, salary
  FROM Employees
  WHERE salary = (SELECT MAX(salary) FROM Employees);

List all animals that have not been adopted.

SELECT *
  FROM Animals
  WHERE animal_id NOT IN (SELECT animal_id FROM Adoptions);

SELECT *
  FROM Animals
  WHERE animal_id NOT IN (SELECT animal_id FROM Adoptions);

Data Operations

INSERT rows

Create a new row in the Animals table.

INSERT INTO Animals (animal_id, name, species, breed, age, date_admitted, status) VALUES
(100, 'Jimmy', 'Dog', 'Labrador', 3, '2024-02-15', 'available')

INSERT INTO Animals (animal_id, name, species, breed, age, date_admitted, status) VALUES
(100, 'Jimmy', 'Dog', 'Labrador', 3, '2024-02-15', 'available')

Create multiple rows in the Animals table.

INSERT INTO Animals (animal_id, name, species, breed, age, date_admitted, status) VALUES
(200, 'Jane', 'Dog', 'Bulldog', 4, '2024-03-16', 'available'),
(201, 'Vicky', 'Dog', 'Rottweiler', 6, '2024-05-16', 'available')

INSERT INTO Animals (animal_id, name, species, breed, age, date_admitted, status) VALUES
(200, 'Jane', 'Dog', 'Bulldog', 4, '2024-03-16', 'available'),
(201, 'Vicky', 'Dog', 'Rottweiler', 6, '2024-05-16', 'available')

UPDATE rows

Update a single animal record using update keyword in Animals Table

UPDATE Animals SET name ='Alfredy',species='Cat' WHERE animal_id = 200;

UPDATE Animals SET name ='Alfredy',species='Cat' WHERE animal_id = 200;

Update a multiple animal record using update keyword in Animals Table

UPDATE Animals SET species = 'Feline', status = 'adopted' WHERE species = 'Cat' AND status = 'available';

UPDATE Animals SET species = 'Feline', status = 'adopted' WHERE species = 'Cat' AND status = 'available';

DELETE Rows

Delete a single animal record by its ID.

DELETE FROM Animals WHERE animal_id = 1;

DELETE FROM Animals WHERE animal_id = 1;

Delete multiple animal records with a specific status.

DELETE FROM Animals WHERE status = 'adopted';

DELETE FROM Animals WHERE status = 'adopted';

Data Definition Language (DDL)

CREATE TABLE

The CREATE TABLE statement is used to create a new table in a database.

CREATE TABLE People (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
    );

CREATE TABLE People (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
    );

DROP TABLE

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

DROP TABLE People;

DROP TABLE People;

TRUNCATE TABLE

It is used to delete all the data present in the table.

TRUNCATE TABLE People;

TRUNCATE TABLE People;

ALTER TABLE

ALTER TABLE - ADD Column

ALTER TABLE People ADD Email varchar(255);

ALTER TABLE People ADD Email varchar(255);

ALTER TABLE - DROP COLUMN

ALTER TABLE People DROP COLUMN Email;

ALTER TABLE People DROP COLUMN Email;

ALTER TABLE - RENAME COLUMN

ALTER TABLE People RENAME COLUMN Email to Person_Email;

ALTER TABLE People RENAME COLUMN Email to Person_Email;

SQL Views

Create views to simplify complex queries and reuse them.

CREATE VIEW AvailableAnimals AS
    SELECT name, species, breed, age
    FROM Animals
    WHERE status = 'available';

  CREATE VIEW EmployeeSalaries AS
    SELECT first_name, last_name, salary
    FROM Employees;

CREATE VIEW AvailableAnimals AS
    SELECT name, species, breed, age
    FROM Animals
    WHERE status = 'available';

  CREATE VIEW EmployeeSalaries AS
    SELECT first_name, last_name, salary
    FROM Employees;

Retrieve available animals from the AvailableAnimals view.

SELECT * FROM AvailableAnimals;

SELECT * FROM AvailableAnimals;

Retrieve employee salaries from the EmployeeSalaries view.

SELECT * FROM EmployeeSalaries;

SELECT * FROM EmployeeSalaries;