SQL-Queries-Cheat-Sheet-image

SQL Queries Cheat Sheet for beginners

0 / 0 20 Mins Admin

December 7, 2022

10 Comments

Looking for a quick and easy way to learn essential SQL Queries? Look no further than our SQL Queries Cheat Sheet. This cheat sheet provides concise, yet comprehensive, coverage of all the essential SQL queries you need to know.

Whether you’re a beginner or an experienced SQL user, you’ll find everything you need to master SQL right here.

Query data in columns from a table

/* syntax */
SELECT column1, column2, ... FROM tableName;

/* example */
SELECT `ID`, `post_author`, `post_date` FROM `wp_posts`;

Get all rows and columns from a table

/* syntax */
SELECT * FROM tableName;

/* example */
SELECT * FROM `wp_posts`;

DISTINCT – return only distinct (different) values.

A column within a table frequently contains many duplicate values, and you may only want to list without any duplicates in a column.

/* syntax */
SELECT DISTINCT column FROM tableName;

/* example */
SELECT DISTINCT `post_author` FROM `wp_posts`;

DISTINCT statement with a condition.

/* syntax */
SELECT DISTINCT column FROM tableName WHERE condition;

/* example */
SELECT DISTINCT `post_author` FROM `wp_posts` WHERE `ID`=1;

Filter records on the basis of a condition, use the WHERE clause.

/* syntax */
SELECT column1, column2, ...
FROM tableName
WHERE condition;

/* example */
SELECT `ID`, `post_author` FROM `wp_posts` WHERE `ID` = 1;

The keyword ORDER BY is used to sort the result set in ascending or descending order.

By default, the ORDER BY keyword sorts the records in ascending order. Use the DESC keyword to sort the records in descending order.

/* syntax */
SELECT column1, column2 FROM tableName ORDER BY column1 ASC;

/* example */
SELECT `post_author`,`post_date` FROM `wp_posts` ORDER BY `ID` ASC;
/* syntax */
SELECT column1, column2 FROM tableName ORDER BY column1 DESC;

/* example */
SELECT `post_author`,`post_date` FROM `wp_posts` ORDER BY `ID` DESC;

SQL’s AND, OR, and NOT operators


The WHERE clause can be used in conjunction with the AND, OR, and NOT operators.

To filter records based on more than one condition, use the AND and OR operators:

  • If all of the conditions separated by AND are TRUE, the AND operator displays a record.
  • If any of the conditions separated by OR is TRUE, the OR operator displays a record.
  • If the condition(s) is NOT TRUE, the NOT operator displays a record.

/* AND Syntax */

SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;

/* AND Example */
SELECT * FROM Customers
WHERE Country='USA' AND City='New York';



/* OR Syntax */

SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR condition3 ...;

/* OR Example */
SELECT * FROM Customers
WHERE City='New York' OR City='Jersey City';



/* NOT Syntax */

SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;

/* UK Example */
SELECT * FROM Customers
WHERE NOT Country='UK';

Insert data into tables

/* syntax */
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

/* example */
INSERT INTO Customers (Name, Age)
VALUES ('Mark', '25');

SQL NULL Values

A field with a NULL value is one that has no value.

It is possible to insert a new record or update an existing record without adding a value to an optional field in a table. The field will then be saved with a NULL value.

/* IS NULL Syntax */

SELECT column_names
FROM table_name
WHERE column_name IS NULL;

/* example */
SELECT Name, Address
FROM Customers
WHERE Address IS NULL;

/* IS NOT NULL Syntax */

SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;

/* example */
SELECT CustomerName, ContactName, Address
FROM Customers
WHERE Address IS NULL;

The UPDATE SQL Statement


The UPDATE statement modifies existing records in a table.

/* syntax */

UPDATE tableName
SET column1 = value1, column2 = value2, ...
WHERE condition;

/* example */

UPDATE Students
SET Name = 'John', City= 'Landon'
WHERE email = '[email protected]';

The SQL DELETE Statement

To delete existing records in a table, use the DELETE statement.

/* syntax */
DELETE FROM tableName WHERE condition;

/* example */
DELETE FROM Countries WHERE CountryName='USA';



/* syntax - delete all records from table */
DELETE FROM tableName;

/* example */
DELETE FROM Countries;

SQL TOP / LIMIT / FETCH FIRST

The SELECT TOP clause is used in SQL Server and MS Access to retrieve a specific number of rows from a database.

/* syntax - SELECT TOP */
SELECT TOP number|percent column|columns
FROM tableName
WHERE condition;

/* example with number */
SELECT TOP 3 * FROM Customers;

/* example with percentage */
SELECT TOP 50 PERCENT * FROM Customers;

/* syntax - LIMIT */
SELECT column|columns
FROM tableName
WHERE condition
LIMIT number;

/* example */
SELECT * FROM Customers
LIMIT 3;
/* syntax - FETCH FIRST */
SELECT column|columns
FROM tableName
ORDER BY column|columns
FETCH FIRST number ROWS ONLY;

/* example with number */
SELECT * FROM Customers
FETCH FIRST 10 ROWS ONLY;

/* example with percentage */
SELECT * FROM Customers
FETCH FIRST 10 PERCENT ROWS ONLY;

SQL MIN() and MAX() Functions

The MIN() function returns the column with the smallest value.

The MAX() function returns the column with the highest value.

/* syntax - MAX() */
SELECT MIN(columnName)
FROM tableName
WHERE condition;

/* example - MAX() */
SELECT MAX(Price) FROM Products;


/* syntax - MIN() */
SELECT MAX(columnName)
FROM tableName
WHERE condition;

/* example - MIN() */
SELECT MIN(Price) FROM Products;

SQL COUNT(), AVG() and SUM() Functions

  • COUNT() returns the number of rows that full fill a condition.
  • AVG() returns the average value of a numerical column.
  • SUM() returns the entire sum of a numerical column.
/* syntax - COUNT() */
SELECT COUNT(columnName)
FROM tableName
WHERE condition;

/* example - COUNT() */
SELECT COUNT(ID) FROM `wp_posts`;


/* syntax - AVG() */
SELECT AVG(columnName)
FROM tableName
WHERE condition;

/* example - AVG() */
SELECT AVG(Price) FROM Products;


/* syntax - SUM() */
SELECT SUM(columnName)
FROM tableName
WHERE condition;

/* example - SUM() */
SELECT SUM(Quantity) FROM Products;

The SQL LIKE Operator

In a WHERE clause, the LIKE operator is used to search for a particular pattern in a column.

The LIKE operator is frequently used in conjunction with two wildcards:

The percent sign (%) denotes the presence of zero, one, or multiple characters.

The underscore symbol (_) denotes a single character.

/* syntax - SUM() */
SELECT column1, column2, ...
FROM tableName
WHERE columnN LIKE pattern;

/* example - LIKE operator */
/* selects all Students whose name begins with "G" */
SELECT * FROM Students
WHERE student_name LIKE 'G%';

/* selects all Students whose name ends with "A" */
SELECT * FROM Students
WHERE student_name LIKE '%A';

/* match names which contain "A" in any position */
SELECT * FROM Students
WHERE student_name LIKE '%A%';

/* match names which starts with "a" and ends with "s" */
SELECT * FROM Students
WHERE student_name LIKE 'a%s';

/* selects all Students whose name do not begins with "G" */
SELECT * FROM Students
WHERE student_name NOT LIKE 'G%';

/* selects all names that have "g" in the second position */
SELECT * FROM Students
WHERE student_name LIKE '_g%';

/* selects all names that starts with "S" and are at least 3 characters in length */
SELECT * FROM Students
WHERE student_name LIKE 'S__%';

The SQL IN operator

In a WHERE clause, you can use the IN operator to specify multiple values.

The IN operator denotes multiple OR conditions.

/* syntax - IN operator */
SELECT columnName
FROM tableName
WHERE columnName IN (value1, value2, ...);

/* example - IN operator */
SELECT name
FROM Students
WHERE ID IN (10, 20, 21);

The SQL BETWEEN Operator

The BETWEEN operator selects values from a specified range. Numbers, text, and dates can all be used as values.

/* syntax - BETWEEN operator */
SELECT columnName
FROM tableName
WHERE columnName BETWEEN value1 AND value2;

/* example - BETWEEN operator */
SELECT * FROM Students
WHERE ID BETWEEN 1 AND 50;

SQL Aliases

You can temporarily rename a table or a column by giving it another name known as Alias. Table aliases are used to rename a table in a specific SQL statement.

/* syntax - SQL Alias Column */
SELECT columnName AS alias_name
FROM tableName;

/* example - SQL Alias Column */
SELECT student_name AS name
FROM Students;



/* syntax - SQL Alias Table  */
SELECT columnName
FROM tableName AS alias_name;

/* example - SQL Alias Table */
SELECT o.OrderID, c.CustomerName
FROM Customers AS c, Orders AS o
WHERE c.CustomerName='John' AND c.CustomerID=o.CustomerID;

The SQL Joins clause

The SQL Joins clause is used to join records from two or more database tables. A JOIN is a method of combining fields from two tables by using values that are shared by both.

/* syntax - SQL Joins  */
SELECT column1, column2, column3
   FROM Table1, Table2
   WHERE  Table1.column = Table2.column;

/* example - SQL Joins  */
SELECT ID, NAME, AGE, AMOUNT
   FROM Customer_table, Orders_table
   WHERE  Customer_table.ID = Orders_table.CUSTOMER_ID;

SQL JOINs of Various Types

The following are the various types of SQL JOINs:

(INNER) JOIN: Returns records from both tables that have matching values.

LEFT (OUTER) JOIN: Returns all records from the left table as well as the records that match from the right table.

RIGHT (OUTER) JOIN: Returns all records from the right table as well as the records that match from the left table.

FULL (OUTER) JOIN: Returns all records when either the left or right table matches.

SQL JOINs of Various Types

SQL INNER JOIN

The INNER JOIN keyword selects records from both tables that have matching values.

/* syntax - SQL INNER JOIN  */
SELECT column1, column2
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;


/* example - SQL INNER JOIN  */
SELECT orders.order_id, customers.customer_id
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id;

SQL LEFT JOIN

The LEFT JOIN keyword retrieves all records from the left table (table 1) as well as the matching records from the right table (table2). 

/* syntax - SQL LEFT JOIN  */
SELECT column1, column2
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

/* example - SQL LEFT JOIN  */
SELECT customers.customer_name, orders.order_id
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id
ORDER BY customers.customer_name;

The RIGHT JOIN

The RIGHT JOIN keyword retrieves all records from the right table (table2) as well as the matching records from the left table (table1).

/* syntax - SQL RIGHT JOIN  */
SELECT column1,column2
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

/* example - SQL RIGHT JOIN  */
SELECT orders.order_id, employees.last_name, employees.first_name
FROM orders
RIGHT JOIN employees ON orders.employee_id = employees.employee_id
ORDER BY orders.order_id;

FULL OUTER JOIN

FULL OUTER JOIN and FULL JOIN are interchangeable. When there is a match in the left (table1) or right (table2) table records, the FULL OUTER JOIN keyword returns all records.

/* syntax - SQL FULL OUTER JOIN  */
SELECT column1, column2
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;

/* example - SQL FULL OUTER JOIN  */
SELECT customers.customer_name, orders.order_id
FROM customers
FULL OUTER JOIN orders ON customers.customer_id=orders.customer_id
ORDER BY customers.customer_name;

The SQL UNION Operator

The UNION operator in SQL selects rows from two or more tables. If two rows of a table are the same, they are only included in the result set once. It removes the duplicate.

/* syntax - SQL UNION  */
SELECT column_or_columns FROM table1
UNION
SELECT column_or_columns FROM table2;

/* example - SQL UNION  */
SELECT city FROM addresses
UNION
SELECT city FROM suppliers
ORDER BY city;

GROUP BY Statement

The GROUP BY statement aggregates rows with similar values, for example, “find the number of students in each school.”

The GROUP BY statement is frequently used with aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more columns.

/* syntax - GROUP BY  */
SELECT column_or_columns FROM table WHERE condition
GROUP BY column_or_columns
ORDER BY column_or_columns;

/* example - GROUP BY  */
SELECT COUNT(student_id), school_name
FROM schools
GROUP BY school_name;

SQL HAVING Clause

In SQL, a HAVING clause specifies that a SQL SELECT statement must only return rows with aggregate values that meet the specified conditions.

/* syntax - SQL HAVING Clause  */
SELECT column_or_columns
FROM table_name
WHERE condition
GROUP BY column_or_columns
HAVING condition
ORDER BY column_or_columns;

/* example - SQL HAVING Clause  */
SELECT name, SUM(salary) FROM employee
GROUP BY name
HAVING SUM(salary)>10000;

The SQL EXISTS Operator

The EXISTS operator is useful to determine whether a record in a subquery exists. If the subquery contains one or more records, the EXISTS operator returns TRUE.

/* syntax - SQL EXISTS Operator  */
SELECT column_or_columns
FROM tableName
WHERE EXISTS
(SELECT column1 FROM tableName WHERE condition);

/* example - SQL EXISTS Operator  */
SELECT school_name
FROM students
WHERE EXISTS (SELECT * FROM schools WHERE students.school_name = schools.name);

ANY and ALL operators

The ANY and ALL operators allows you to compare a single column value to a variety of other values.

/* syntax - SQL ANY Operator  */
SELECT ALL columns
FROM tableName
WHERE condition;

/* example - SQL ANY Operator  */
SELECT product_name FROM products
WHERE product_id = ANY
  (SELECT product_id FROM orders
  WHERE quantity = 10);

/* syntax - SQL ALL Operator  */
SELECT ALL name
FROM table
WHERE condation;

/* example - SQL ALL Operator  */
SELECT name
FROM products
WHERE product_id = ALL
  (SELECT product_id
  FROM orders
  WHERE quantity = 10);

Conclusion

This blog post is the concluding SQL cheat sheets for starters. SQL is the most important skill a developer or a data analyst can have. In this post, we have tried to give you a short and crisp cheat sheet with the most basic SQL queries. You can start with these.