SQL Basics (SQL CheetSheet)

SQL : Basic Syntax and Keywords

SQL (Structured Query Language) is a powerful programming language used to communicate with and manipulate databases.

In this guide we'll cover the basics of SQL, including the syntax, keywords, and how to use them. By the end of this guide, you'll have a good understanding of SQL and be able to write basic SQL queries.

What is SQL?

SQL is a programming language used to manage relational databases. It was developed in the 1970s and has become one of the most widely used programming languages. SQL is used to create, modify, and query databases.

SQL syntax

SQL has a specific syntax that you need to follow when writing SQL queries. The basic syntax for SQL is as follows:

SELECT column_name
FROM table_name
WHERE condition;

Let's break down the syntax to understand it:

SELECT: This is used to select the columns that you want to retrieve from the database.
FROM: This specifies the table or tables that you want to retrieve the data from.
WHERE: This is used to filter the data that you want to retrieve based on a specific condition.

SQL keywords

SQL has a number of keywords that you need to be familiar with in order to write effective SQL queries. In this article we will understand all major keywords with examples.

SQL CREATE TABLE

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

CREATE TABLE Syntax

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    salary INT,
    department_id INT
);

Sample Table
|employee_id | first_name | last_name | salary | department_id | |----------|-------------||-------------||-------------||-------------| |1 | John | Smith | 50000 | 1 | |2 | Jane | Doe | 60000 | 2 | |3 | Mike | Johnson | 55000 | 1 | |4 | Sarah | Lee | 65000 | 3 | |5 | David | Brown | 70000 | 2 |

SQL SELECT

SELECT is a keyword used in SQL to retrieve data from a database. It allows you to specify the columns you want to retrieve and the tables you want to retrieve them from. The result of a SELECT statement is a result set, which is a table of rows and columns that meet the specified conditions.

SELECT Syntax:
The basic syntax for the SELECT statement is as follows:
```
SELECT column1, column2, ...
FROM table_name
WHERE condition;
```
Here, column1, column2, ... are the names of the columns you want to retrieve from the table, table_name is the name of the table you want to retrieve data from, and condition is an optional clause that specifies the conditions that the retrieved data must meet.
SELECT Example:
```
SELECT employee_id, first_name, last_name
FROM employees;
```
This will return a result set of all the employee IDs, first names, and last names from the employees table. If we wanted to retrieve only the employees whose salary is greater than 50000, we could add a WHERE clause to the statement like this:
```
SELECT employee_id, first_name, last_name
FROM employees
WHERE salary > 50000;
```
This would return only the rows from the employees table where the salary is greater than 50000.

SQL FROM

FROM is a keyword used in SQL to specify the table or tables from which to retrieve data. It is used in conjunction with the SELECT statement to retrieve data from one or more tables in a database.

FROM Syntax:

//single table
SELECT column1, column2, ...
FROM table_name;

// multiple table
SELECT column1, column2, ...
FROM table_name1, table_name2;

FROM Example:

//single table
SELECT employee_id, first_name, last_name
FROM employees 

// multiple table
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

SQL WHERE

WHERE is a keyword used in SQL to filter data based on specific conditions. It is used in conjunction with the SELECT statement to retrieve data from a table or tables that meet certain criteria.

WHERE Syntax:

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

WHERE Example:

SELECT first_name, last_name
FROM employees
WHERE salary > 50000;

SQL GROUP BY

GROUP BY is a keyword used in SQL to group together rows based on the values in one or more columns. It is used in conjunction with the SELECT statement to aggregate data from a table or tables based on specific criteria.

GROUP BY Syntax:

SELECT column1, column2, ..., aggregate_function(column)
FROM table_name
WHERE condition
GROUP BY column1, column2, ...;

GROUP BY Example:

SELECT department_id, SUM(salary)
FROM employees
GROUP BY department_id;

The GROUP BY clause can also be combined with other keywords such as ORDER BY and HAVING to further refine the results of a query.

SQL HAVING

HAVING is a keyword used in SQL to filter grouped data based on specific conditions. It is used in conjunction with the GROUP BY clause to filter data after it has been grouped and aggregated.

HAVING Syntax:

SELECT column1, column2, ..., aggregate_function(column)
FROM table_name
WHERE condition
GROUP BY column1, column2, ...
HAVING condition;

HAVING Example:

SELECT department_id, SUM(salary)
FROM employees
GROUP BY department_id
HAVING SUM(salary) > 100000;

The HAVING clause can be used to filter on any aggregate function, and can also be combined with other keywords such as ORDER BY to further refine the results of a query.

SQL ORDER BY

ORDER BY is a keyword used in SQL to sort the result set of a query in ascending or descending order based on one or more columns. It is used in conjunction with the SELECT statement to control the order in which data is returned.

ORDER BY Syntax:

SELECT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

ORDER BY Example:

SELECT first_name, last_name, hire_date
FROM employees
ORDER BY hire_date DESC;

The ORDER BY clause can be used to sort on any column or combination of columns, and can also be combined with other keywords such as LIMIT to further refine the results of a query.

SQL INSERT INTO

INSERT INTO is a keyword used in SQL to add new data into a table. It is used in conjunction with the VALUES keyword to specify the values to be inserted into the table.

INSERT INTO Syntax:

INSERT INTO table_name (column1, column2, ..., columnN)
VALUES (value1, value2, ..., valueN);

INSERT INTO Example:

INSERT INTO employees (first_name, last_name, salary, hire_date)
VALUES ('John', 'Doe', 50000, '2022-03-24');

The INSERT INTO statement can be used to insert data into any table and any combination of columns, as long as the number of columns and values match. Additionally, you can insert multiple rows at once by specifying multiple sets of values separated by commas.

SQL VALUES

VALUES is a keyword used in SQL to specify the values to be inserted into a table using the INSERT INTO statement. It is used in conjunction with the INSERT INTO keyword to add new data to a table.

VALUES Syntax:

INSERT INTO table_name (column1, column2, ..., columnN)
VALUES (value1, value2, ..., valueN);

VALUES Example:

INSERT INTO employees (first_name, last_name, salary, hire_date)
VALUES ('John', 'Doe', 50000, '2022-03-24');

SQL UPDATE

UPDATE is a keyword used in SQL to modify existing data in a table. It is used in conjunction with the SET keyword to specify the new values for the columns to be updated.

UPDATE Syntax:

UPDATE table_name
SET column1 = value1, column2 = value2, ..., columnN = valueN
WHERE condition;

UPDATE Example:

UPDATE employees
SET salary = 55000
WHERE employee_id = 123;

The UPDATE statement can be used to modify data in any table and any combination of columns, as long as the condition used in the WHERE clause identifies the correct row(s) to be updated.

SQL SET

SET is a keyword used in SQL to assign new values to columns in an UPDATE statement. It is used in conjunction with the UPDATE keyword to modify the values of one or more columns in a table

SET Syntax:

UPDATE table_name
SET column1 = value1, column2 = value2, ..., columnN = valueN
WHERE condition;

SET Example:

UPDATE employees
SET salary = 55000, hire_date = '2022-03-24'
WHERE employee_id = 123;

SQL DELETE

DELETE is a keyword used in SQL to remove rows from a table. It is used in conjunction with the WHERE keyword to specify which rows to delete from the table.

DELETE Syntax:

DELETE FROM table_name
WHERE condition;

DELETE Example:

DELETE FROM employees
WHERE employee_id = 123;

The DELETE statement can be used to remove any number of rows from a table, as long as the WHERE clause is used to specify the correct rows to be deleted. It is important to use the DELETE statement with caution, as once a row is deleted it cannot be recovered.

SQL JOIN

JOIN is a keyword used in SQL to combine rows from two or more tables based on a related column between them. The resulting table contains all the columns from both tables, with the matching rows from each table appearing on the same row in the result set.

JOIN Syntax:
There are majoraly 4 types of JOIN in SQL, each with their own syntax:
1. INNER JOIN:
Returns only the rows that have matching values in both tables.
```
SELECT *
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;
```
1. LEFT JOIN:
Returns all the rows from the left table, and the matching rows from the right table. If there is no match in the right table, NULL values are returned.
```
SELECT *
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;
```
1. RIGHT JOIN:
Returns all the rows from the right table, and the matching rows from the left table. If there is no match in the left table, NULL values are returned.
```
SELECT *
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;
```
1. FULL OUTER JOIN:
Returns all the rows from both tables, with NULL values in the columns where there is no match.
```
SELECT *
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name;
```
JOIN Example:
```
SELECT employees.employee_id, employees.first_name, employees.last_name, departments.department_name
FROM employees
INNER JOIN departments
ON employees.department_id = departments.department_id;
```
In above example, we are using an INNER JOIN to combine the employees and departments tables based on their matching department_id columns. The SELECT statement specifies the columns we want to include in the result set. The resulting table will contain all the columns from both tables, but only the rows where there is a match between the two tables based on the department_id column.

SQL UNION

UNION is used in SQL to combine the results of two or more SELECT statements into a single result set. The result set contains all the rows from each SELECT statement, with duplicates removed.

UNION Syntax:

SELECT column1, column2, ...
FROM table1
UNION
SELECT column1, column2, ...
FROM table2;

UNION Example:

SELECT name, email
FROM employees
UNION
SELECT name, email
FROM customers;

SQL DISTINCT

DISTINCT is a keyword used in SQL to eliminate duplicate rows from the result set of a SELECT statement. It returns only the unique values from a column or combination of columns.

DISTINCT Syntax:

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

DISTINCT Example:

SELECT first_name, last_name
FROM employees
WHERE salary > 50000;

SQL AS

AS is a keyword used in SQL to alias a column or table with a new name. It allows us to give a new name to a column or table, which can be used to make the output of a SELECT statement more readable or to avoid naming conflicts.

AS Syntax:

// ex1
SELECT column_name AS alias_name
FROM table_name;

// ex2
SELECT column_name
FROM table_name AS alias_name;

AS Example:

SELECT employee_id, CONCAT(first_name, ' ', last_name) AS full_name
FROM employees;

SQL ON

ON is a keyword used in SQL to specify the join condition between two tables in a JOIN operation. It is used to join rows from two or more tables based on a related column or columns.

ON Syntax:

SELECT *
FROM table1
JOIN table2
ON table1.column_name = table2.column_name;

ON Example:

SELECT *
FROM employees
JOIN departments
ON employees.department_id = departments.department_id;

SQL BETWEEN

BETWEEN is a SQL operator that allows you to select values that are within a range of two values, inclusive. The BETWEEN operator is often used in a WHERE clause to specify a range of values for a column.

BETWEEN Syntax:

SELECT column_name
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

BETWEEN Example:

SELECT first_name, last_name, age
FROM students
WHERE age BETWEEN 18 AND 24;

SQL LIKE

LIKE is a SQL operator that allows you to search for patterns in a column of text data. It is used in a WHERE clause to filter results based on a specified pattern.

LIKE Syntax:
```
SELECT column_name
FROM table_name
WHERE column_name LIKE pattern;
```
Here, we are selecting a column named column_name from a table named table_name and filtering the results based on a specified pattern using the LIKE operator.
The pattern used with the LIKE operator can include the following special characters:
- %: Matches any string of zero or more characters.
- _: Matches any single character.

LIKE Example:

SELECT first_name, last_name, email
FROM employees
WHERE email LIKE '%@company.com';

SQL IN

IN is a SQL operator that allows you to specify multiple values in a WHERE clause. It is used to match a value against a list of values.

IN Syntax:

SELECT column_name
FROM table_name
WHERE column_name IN (value1, value2, ...);

IN Example:

SELECT first_name, last_name, department
FROM employees
WHERE department IN ('Sales', 'Marketing');

SQL NOT

NOT is a SQL operator that reverses the meaning of a logical expression. It is used to negate a condition in a WHERE clause or a subquery.

NOT Syntax:

SELECT column_name
FROM table_name
WHERE NOT condition;

NOT Example:

SELECT first_name, last_name, salary
FROM employees
WHERE NOT salary > 50000;

SQL NULL

NULL is a special value in SQL that represents missing or unknown data. It is different from other values because it is not equal to any other value, including itself.

NULL Syntax:

SELECT column_name
FROM table_name
WHERE column_name IS NULL;

NULL Example:

SELECT order_id, customer_name, order_date, ship_date
FROM orders
WHERE ship_date IS NULL;

SQL COUNT

COUNT is a SQL aggregate function that counts the number of rows in a table or the number of rows that match a specified condition in a table.

COUNT Syntax:

//counting the number of rows that have a non-NULL value in the column_name column of the table_name table.
SELECT COUNT(column_name)
FROM table_name;

// counting the number of rows in the table_name table that satisfy the condition.
SELECT COUNT(*)
FROM table_name
WHERE condition;

COUNT Example:

// total number of rows in the employees table
SELECT COUNT(*)
FROM employees;

//number of rows in the employees table where the salary column value is greater than 50000
SELECT COUNT(*)
FROM employees
WHERE salary > 50000;

SQL SUM

SUM is a SQL aggregate function that calculates the sum of a numeric column in a table or the sum of a set of values.

SUM Syntax:

// simple sum 
SELECT SUM(column_name)
FROM table_name;

//sum with condition
SELECT SUM(expression)
FROM table_name
WHERE condition;

SUM Example:

SELECT SUM(sales_amount)
FROM sales;

SELECT SUM(sales_amount)
FROM sales
WHERE product_name = 'Product A';

SQL AVG

AVG is a SQL aggregate function that calculates the average of a numeric column in a table or the average of a set of values.

AVG Syntax:

SELECT AVG(expression)
FROM table_name
WHERE condition;

AVG Example:

SELECT AVG(test_score)
FROM grades
WHERE student_name = 'John Doe';

SQL MIN

MIN is a SQL aggregate function that returns the minimum value of a numeric column in a table or the minimum of a set of values.

MIN Syntax:

SELECT MIN(column_name)
FROM table_name
WHERE condition;

MIN Example:

SELECT MIN(sales_amount)
FROM sales
WHERE product_name = 'Product X';

SQL MAX

MAX is a SQL aggregate function that returns the maximum value of a numeric column in a table or the maximum of a set of values.

MAX Syntax:

SELECT MAX(column_name)
FROM table_name
WHERE condition;

MAX Example:

SELECT MAX(sales_amount)
FROM sales
WHERE product_name = 'Product X';

SQL EXISTS

EXISTS is a logical operator in SQL that tests the existence of a row in a subquery. The subquery returns a boolean value of true or false depending on whether the specified condition is met.

EXISTS Syntax:
```
SELECT column_name(s)
FROM table_name
WHERE EXISTS (subquery);
```
- The subquery can be any valid SQL statement that returns a boolean value, such as a SELECT, INSERT, UPDATE, or DELETE statement

EXISTS Example:

Suppose we have two tables named customers and orders. We want to retrieve all customers who have placed an order. We can use the following SQL statement to achieve this:

SELECT customer_name
FROM customers
WHERE EXISTS (SELECT *
            FROM orders
            WHERE customers.customer_id = orders.customer_id);

SQL ANY

ANY is a comparison operator in SQL that compares a value with a set of values returned by a subquery. The operator returns true if the value matches any of the values returned by the subquery.

ANY Syntax:
```
value operator ANY (subquery);
```
Here, value is the value to be compared, operator is a comparison operator such as =, >, <, etc., and subquery is a valid SQL subquery that returns a set of values.

ANY Example:

SELECT *
FROM products
WHERE price < ANY (SELECT price
                FROM products
                WHERE product_id BETWEEN 101 AND 110);

SQL ALL

ALL is a comparison operator in SQL that compares a value with a set of values returned by a subquery. The operator returns true if the value matches all of the values returned by the subquery.

ALL Syntax:
```
value operator ALL (subquery);
```
Here, value is the value to be compared, operator is a comparison operator such as =, >, <, etc., and subquery is a valid SQL subquery that returns a set of values.

ALL Example:

SELECT *
FROM orders
WHERE order_total > ALL (SELECT order_total
                        FROM orders
                        WHERE customer_id = 100);

SQL CASE

CASE is a conditional expression in SQL that allows you to perform different actions based on different conditions. It's similar to the if-then-else statement in other programming languages.

CASE Syntax:

// based on value
CASE expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ...
    ELSE default_result
END;

// based on condition
CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE default_result
END;

CASE Example:

SELECT employee_id, first_name, last_name, salary,
   CASE
       WHEN salary > 50000 THEN salary * 1.1
       ELSE salary * 1.05
   END AS bonus
FROM employees;

SQL WHEN

WHEN Syntax:

WHEN is a keyword used in conjunction with the CASE statement in SQL. It is used to define specific conditions and their corresponding results that should be returned when evaluating the CASE statement.

CASE
    WHEN condition_1 THEN result_1
    WHEN condition_2 THEN result_2
    ...
    WHEN condition_n THEN result_n
    ELSE result
END

WHEN Example:

SELECT name,
    CASE
        WHEN grade >= 90 THEN 'A'
        WHEN grade >= 80 THEN 'B'
        WHEN grade >= 70 THEN 'C'
        WHEN grade >= 60 THEN 'D'
        ELSE 'F'
    END AS grade_level
FROM students;

SQL THEN

In SQL, the keyword THEN is used in combination with the CASE statement. The THEN keyword is used to specify the result or value that should be returned if the condition specified in the WHEN clause of the CASE statement is true.

THEN Syntax:

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE resultN
END;

THEN Example:

SELECT employee_id,
   first_name,
   last_name,
   CASE
       WHEN salary > 5000 THEN 'High'
       WHEN salary > 3000 THEN 'Medium'
       ELSE 'Low'
   END AS 'Salary Category'
FROM employees;

SQL ELSE

ELSE keyword is used in the context of a conditional statement to specify what action should be taken if none of the preceding conditions in the statement evaluate to true. It is optional and can be used with the CASE statement or the IF statement.

ELSE Syntax:

//ex1
CASE
    WHEN condition_1 THEN result_1
    WHEN condition_2 THEN result_2
    ELSE result_default
END

//ex2
IF condition THEN result_true ELSE result_false END IF

ELSE Example:

SELECT 
    name, 
    salary, 
    bonus, 
    CASE 
        WHEN bonus IS NULL THEN salary 
        ELSE salary + bonus 
    END AS total_income
FROM employees

SQL END.

In SQL, END is a keyword that is used to terminate the block of code started by the CASE statement. The END keyword marks the end of the conditional logic and any subsequent statements in the SQL query will be executed outside of the CASE block.

END Syntax:

SELECT column_name,
    CASE
        WHEN condition1 THEN result1
        WHEN condition2 THEN result2
        ...
        ELSE result
    END
FROM table_name;

END Example:

SELECT product_name,
    CASE
        WHEN price > 10 THEN 'Expensive'
        WHEN price > 5 THEN 'Moderate'
        ELSE 'Cheap'
    END AS price_category
FROM products;

Conclusion,

SQL is a powerful language used to manage and manipulate data in relational databases. It follows a specific syntax and uses a variety of keywords to perform various operations, such as creating tables, inserting data, updating data, and querying data.

Understanding the basic syntax and keywords is essential for anyone working with databases or data analysis. With the knowledge of these basic concepts, one can easily write SQL statements to retrieve, manipulate and manage data effectively.