Basic SQL Cheat Sheet Interview ready Quick Revision
SQL or Structured Query Language is a basic query language that every programmer should have in their skillset. SQL stores, retrieves, and manipulates data within relational databases. This ‘Basic SQL Cheat Sheet for Interview ready’ article will provide you with a quick walkthrough to face interviews or to get started.
Using SQL, we can interact with a database by writing queries.
A table is created in a database using CREATE TABLE.
CREATE TABLE Customers(
--columnName datatype
CustomerID INT PRIMARY KEY,
firstName varchar(255),
lastName varchar(255),
City varchar(50),
Country varchar(10), -- variable sized string data
CONSTRAINT Ucp UNIQUE (CustomerID) -- prevents two identical values
);INSERT INTO is used to insert new records in a table in two ways.
INSERT INTO Customers VALUES (14, 'Akash', 'Obroi', 'Mumbai', 'India');
INSERT INTO Customers (CustomerID, firstName, lastName)
VALUES (10, 'Tom', 'Erichsen'); -- City and Country is NULL at this momentTo select everything from the ‘products’ table:
SELECT * FROM products;
Selecting only the ‘product_id’ and ‘price’ of each product from the products table:
SELECT product_id, price FROM products;Aliases are used to provide a column with a temporary title. It’s also used for complex queries.
SELECT firstName AS 'Name' FROM Customers;
WHERE Clause is used for filtering based on the applied conditions.
SELECT product_id, item, price FROM products WHERE price >= 500;
SELECT * FROM Customers WHERE Country = 'Germany';
SELECT * FROM Customers WHERE CustomerID <> '11'; -- CustomerID is not 11
SELECT * FROM Customers WHERE City IS NULL; -- or check IS NOT NULLDISTINCT is used to eliminate duplicate rows from the result.
SELECT DISTINCT Country FROM Customers;
The UPDATE statement is used to modify one or more existing records in a table.
UPDATE Customers
SET firstName = 'Alfred', City= 'New York'
WHERE CustomerID = 10;RENAME is capable of changing a table name.
RENAME TempOrders TO Orders;
GROUP BY groups rows based on one or more columns and it is often used with aggregate functions to group the result set by one or more columns.
ORDER BY keyword is used to sort the result set. By default, sorting is done in ascending order.
SELECT * FROM Customers
ORDER BY CustomerName;
SELECT orderCount, CustomerID FROM Orders
ORDER BY orderCount DESC; -- No of orders by users in descending order
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country;
SELECT orderCount, CustomerName, COUNT(price) FROM Orders
GROUP BY CustomerName
ORDER BY orderCount DESC; -- most frequent buyer, name, total spent by the userThe LIMIT clause is used to specify the number of records to return.
SELECT * FROM Customers
LIMIT 2, 5;
-- select 5 customers after offset value 2, Selecting 3rd to 7th Customer rows.
SELECT TOP 10 PERCENT * FROM Customers;
-- selects the first 10% of the records
Aggregate functions like AVG(), MIN(), MAX(), COUNT(), SUM(), etc. perform a calculation on multiple values and return a single value.
SELECT AVG(Price) FROM Products;
SELECT MAX(Price) FROM Products;
SELECT MIN(Price) FROM Products;
SELECT COUNT(*) FROM Customers WHERE Country='Germany';The Having clause can do condition checking in place of WHERE when we’ve used Aggregated functions. Usually, it is used to apply a filter on the result of GROUP BY based on the specified condition.
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5;LIKE is used in a WHERE clause to search for a specified pattern in a column. Wildcards are to substitute one or more characters in a string.
After the LIKE follows the matchable pattern. The query selects Customer names starting with ‘a’ and can have anything after that.
SELECT * FROM Customers
WHERE CustomerName LIKE 'a%';
-- similary '%a%' means 'a' can be at any indexSelect 4 letters long Customer names having the second letter ‘a’.
SELECT * FROM Customers
WHERE CustomerName LIKE '_a__';
-- similary '%a_ _ _' means 'a' is second fourth last letter of any word of random length.
sql SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20;
Read More: Linux Command Cheatsheet for Beginners (opens in a new tab)
DROP is used to delete an entire table, view, or other database objects. It also deletes the structure of a table.
DROP TABLE Orders;TRUNCATE is used to remove all data from a table, but unlike DROP it retains the table’s structure.
TRUNCATE TABLE Orders;
DELETE is used to remove existing records in a table without affecting the rest of the rows.
DELETE FROM Customers WHERE firstName = 'Akash';UNION operator is used to combining the output from the result of two or more SELECT command queries into a single distinct result set. Whereas UNION ALL does not remove duplicates.
SELECT * FROM Customers
UNION
SELECT * FROM Customers_Old;
-- using UNION ALL will also include duplicate rows
INTERSECT is used to return only the rows that are common (distinct rows) to the result sets of two or more SELECT statements.
SELECT * FROM Customers
INTERSECT
SELECT * FROM Customers_Old;EXCEPT is used to produce only the rows that are in the first result set but not in the second or more SELECT statements. In other words, it’s used to subtract the result obtained by the first SELECT query from the result set obtained by the second SELECT query.
SELECT * FROM Customers
EXCEPT
SELECT * FROM Customers_Old; -- same as MINUS for MySQL
EXISTS returns Boolean true if a subquery returns at least one row.
SELECT * FROM Customers
WHERE EXISTS (SELECT * FROM Orders WHERE Customers.CustomerID = Orders.CustomerID);SELECT * FROM Customers
WHERE Country IN ('USA', 'UK');
-- Selects Customers from both countries
WHERE Country NOT IN ('USA'');
-- Any customer except from USACAST is used to convert an expression of one data type to another.
SELECT CAST(Price AS INT) FROM Products;
Few of the functions that are used to return the Date, Time, etc.
SELECT NOW();
SELECT CURDATE();
SELECT CURTIME();
SELECT YEAR(OrderDate) FROM Orders;
SELECT MONTH(OrderDate) FROM Orders;
SELECT DAY(OrderDate) FROM Orders;SELECT * FROM Orders
WHERE OrderDate BETWEEN '2022-01-01' AND '2022-12-31';CASE expression goes through conditions similar to if-else and returns a value when a condition is met.
SELECT CustomerName,
CASE
WHEN Country = 'USA' THEN 'North America'
WHEN Country = 'UK' THEN 'Europe'
ELSE 'Other'
END AS Region
FROM Customers;
-- Selects CustomerName and RegionANY is used to compare a value to any value in a list or returned by a subquery whereas the ALL clause is used to compare a value to all values in a list or returned by a subquery
SELECT * FROM Orders
WHERE Quantity = ANY (SELECT Quantity FROM OrderDetails);
SELECT * FROM Orders
WHERE Quantity > ALL (SELECT Quantity FROM OrderDetails);JOIN clause is used to combine rows from two or more tables. There are a few types of join- Cross or cartesian join, Left Join, Right Join, Inner Join, Outer Join, and Self Join.
To visualize the Joins in SQL check out this Visualizer page (opens in a new tab) which lets you understand how joins work by interacting and seeing it visually
SELECT Customers.CustomerName, Orders.OrderDate FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID; -- same as JOIN
SELECT Customers.CustomerName, Orders.OrderDate FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;SELECT Customers.CustomerName, Orders.OrderDate FROM Customers
RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;ALTER TABLE is used to make changes like adding, deleting, or modifying columns in an existing table.
ALTER TABLE Customers
ADD COLUMN email;Dynamically adding rows in the email column, based on the customer’s first name & last name.
UPDATE Customers SET email = CONCAT(firstName + lastName + '@gmail.com');
-- Coditional based updating using concatenation operator ||
UPDATE students
SET email = firstName || lastName ||
CASE
WHEN gender = 'M' THEN '@indgeek.com'
WHEN gender = 'F' THEN '@gmail.com'
END
-- use REPLACE(firstName, ' ', '') if having spaces in firstName
-- e.g. email field of Akash Obroi is set to 'akashobroi@indgeek.com'SUBSTRING is used to extract a specific part of a String. If the email field consists of ‘Akash@gmail.com’, we are to extract only the email provider from the field or the name from the email value.
SELECT SUBSTRING(email, 1, INSTR(email, '@')-1) as domain FROM Customers;
-- it finds the index of @ in email and returns string from 1st index to that index [firstName + lastName]
SELECT SUBSTRING(email, INSTR(email, '@')+1, LENGTH(email)) as domain FROM Customers;
-- it finds the index of @ in email and returns string from that index to size of string [gmail.com]Transaction in SQL Server is a sequential group of statements or queries to perform single or multiple tasks in a database. Either all modification is successful when the transaction is committed or Rollback to the previous state to retain the ACID Property of DBMS.
BEGIN;
UPDATE Customers SET balance = balance + 100 WHERE first_name='Robert';
UPDATE Customers SET balance = balance - 100 WHERE first_name='Betty';
COMMIT; -- e.g Transaction of Betty sending 100 bucks to Robert
ROLLBACK; -- use rollback to undo a transactionA view is a virtual table based on the result set of an SQL statement. It is used to allow users to access data, without granting the users permission to directly access the underlying tables.
CREATE VIEW [USA Customers] AS
SELECT CustomerName, ContactName
FROM Customers
WHERE Country = 'USA';
SELECT * FROM [USA Customers];Summing up
I believe this SQL Cheat Sheet will be enough for revision of the SQL queries either for interviews, development, or exams.
If you’re someone starting out to learn SQL, you may opt for Oracle DevGym (opens in a new tab) which is a free course on ‘Database for Developers’.
Comment below if I’ve missed anything to add to the cheat sheet or if you find anything imprecise & even let us know what you think about the SQL cheat sheet.
