SQL Cheat Sheet

Example: Adds a new column named ‘email_address’ to a table named ‘users’.

ALTER TABLE users
ADD email_address varchar(255);

Example: Adds a new PRIMARY KEY constraint named ‘user’ on columns ID and SURNAME.

ALTER TABLE users
ADD CONSTRAINT user PRIMARY KEY (ID, SURNAME);

Example: Adds a new boolean column called ‘approved’ to a table named ‘deals’.

ALTER TABLE deals
ADD approved boolean;

Example 2: Deletes the ‘approved’ column from the ‘deals’ table

ALTER TABLE deals
DROP COLUMN approved;

Example: In the ‘users’ table, make the column ‘incept_date’ into a ‘datetime’ type.

ALTER TABLE users
ALTER COLUMN incept_date datetime;

Example: Returns the users with a higher number of tasks than the user with the highest number of tasks in the HR department (id 2)

SELECT first_name, surname, tasks_no
FROM users
WHERE tasks_no > ALL (SELECT tasks FROM user WHERE department_id = 2);

Example: Returns events located in London, United Kingdom

SELECT * FROM events
WHERE host_country='United Kingdom' AND host_city='London';

Example: Returns products from the products table which have received orders – stored in the orders table – with a quantity of more than 5.

SELECT name
FROM products
WHERE productId = ANY (SELECT productId FROM orders WHERE quantity > 5);

Example: Aliases north_east_user_subscriptions column

SELECT north_east_user_subscriptions AS ne_subs
FROM users
WHERE ne_subs > 5;

Example: Apples, Bananas, Peaches, Raddish

Example 1: Selects stock with a quantity between 100 and 150.

SELECT * FROM stock
WHERE quantity BETWEEN 100 AND 150;

Example 2: Selects stock with a quantity NOT between 100 and 150. Alternatively, using the NOT keyword here reverses the logic and selects values outside the given range.

SELECT * FROM stock
WHERE quantity NOT BETWEEN 100 AND 150;

Example: Returns users and their subscriptions, along with a new column called activity_levels that makes a judgement based on the number of subscriptions.

SELECT first_name, surname, subscriptions
CASE WHEN subscriptions > 10 THEN 'Very active'
WHEN Quantity BETWEEN 3 AND 10 THEN 'Active'
ELSE 'Inactive'
END AS activity_levels
FROM users;

Example 1 (MySQL): Makes sure any users added to the users table are 18 or over.

CREATE TABLE users (
  first_name varchar(255),
  age int,
  CHECK (age>=18)
);

Example 2 (MySQL): Adds a check after the table has already been created.

ALTER TABLE users
ADD CHECK (age>=18);

Example: Creates a new database named ‘websitesetup’.

CREATE DATABASE websitesetup;

Example: Creates a new table called ‘users’ in the ‘websitesetup’ database.

CREATE TABLE users (
    id int,
    first_name varchar(255),
    surname varchar(255),
    address varchar(255),
    contact_number int
);

Example 1 (MySQL): Creates a new table called Products which has a name column with a default value of ‘Placeholder Name’ and an available_from column with a default value of today’s date.

CREATE TABLE products (
    id int,
    name varchar(255) DEFAULT 'Placeholder Name',
    available_from date DEFAULT GETDATE()
);

Example 2 (MySQL): The same as above, but editing an existing table.

ALTER TABLE products
ALTER name SET DEFAULT 'Placeholder Name',
ALTER available_from SET DEFAULT GETDATE();

Example: Removes a user with a user_id of 674.

DELETE FROM users WHERE user_id = 674;

Example: Raddish, Peaches, Bananas, Apples

Example: Removes the first_name column from the users table.

ALTER TABLE users
DROP COLUMN first_name

Example: Deletes a database named ‘websitesetup’.

DROP DATABASE websitesetup;

Example (MySQL): Removes the default value from the ‘name’ column in the ‘products’ table.

ALTER TABLE products
ALTER COLUMN name DROP DEFAULT;

Example: Removes the users table.

DROP TABLE users;

Example: Lists any dealerships with a deal finance percentage less than 10.

SELECT dealership_name
FROM dealerships
WHERE EXISTS (SELECT deal_name FROM deals WHERE dealership_id = deals.dealership_id AND finance_percentage < 10);

Example: Selects data from the users table.

SELECT area_manager
FROM area_managers
WHERE EXISTS (SELECT ProductName FROM Products WHERE area_manager_id = deals.area_manager_id AND Price < 20);

So instead of:-

SELECT * FROM users
WHERE country = 'USA' OR country = 'United Kingdom' OR country = 'Russia' OR country = 'Australia';

You can use:-

SELECT * FROM users
WHERE country IN ('USA', 'United Kingdom', 'Russia', 'Australia');

Example: Adds a new vehicle.

INSERT INTO cars (make, model, mileage, year)
VALUES ('Audi', 'A3', 30000, 2016);

Example: Returns users that haven’t given a contact number.

SELECT * FROM users
WHERE contact_number IS NULL;

Example: Returns true if the user’s first_name ends with ‘son’.

SELECT * FROM users
WHERE first_name LIKE '%son';

Example: Returns true if the user’s first_name doesn’t end with ‘son’.

SELECT * FROM users
WHERE first_name NOT LIKE '%son';

Example: Returns users that live in either Sheffield or Manchester.

SELECT * FROM users
WHERE city = 'Sheffield' OR 'Manchester';

Example: Returns countries in alphabetical order.

SELECT * FROM countries
ORDER BY name;

Example: Returns the top 10 countries from the countries table.

SELECT * FROM countries
WHERE ROWNUM <= 10;

Example  1: Selects all columns from all users.

SELECT * FROM users;

Example  2: Selects the first_name and surname columns from all users.xx

SELECT first_name, surname FROM users;

Example: Creates a backup table using data from the users table.

SELECT * INTO usersBackup2020
FROM users;

Example: Returns all countries from the users table, removing any duplicate values (which would be highly likely)

SELECT DISTINCT country from users;

Example: Returns the top 3 cars from the cars table.

SELECT TOP 3 * FROM cars;

Example: Updates the value and quantity values for an order with an id of 642 in the orders table.

UPDATE orders
SET value = 19.49, quantity = 2
WHERE id = 642;

Example: Returns the top 5 users from the users table.

SELECT TOP 5 * FROM users;

Example: Empties the sessions table, but leaves the table itself intact.

TRUNCATE TABLE sessions;

Example: Returns the cities from the events and subscribers tables.

SELECT city FROM events
UNION
SELECT city from subscribers;

Example 1 (MySQL): Adds a unique constraint to the id column when creating a new users table.

CREATE TABLE users (
    id int NOT NULL,
    name varchar(255) NOT NULL,
    UNIQUE (id)
);

Example 2 (MySQL): Alters an existing column to add a UNIQUE constraint.

ALTER TABLE users
ADD UNIQUE (id);

Example: Updates the mileage and serviceDue values for a vehicle with an id of 45 in the cars table.

UPDATE cars
SET mileage = 23500, serviceDue = 0
WHERE id = 45;

Example: Adds a new car to the cars table.

INSERT INTO cars (name, model, year)
VALUES ('Ford', 'Fiesta', 2010);

Example: Returns orders with a quantity of more than 1 item.

SELECT * FROM orders
WHERE quantity > 1;