SQL

SQL Tutorial

SQL, or Structured Query Language, is a programming language designed to manage data stored in relational databases. It can be used to create, modify, and query databases, as well as to manage the data they contain. Some common tasks that can be performed using SQL include:

  • Creating new databases and tables
  • Inserting, updating, and deleting data from tables
  • Retrieving data from tables
  • Setting permissions on tables and procedures

There are many different implementations of SQL, including MySQL, Oracle, and Microsoft SQL Server. Each implementation has its own specific syntax and features, but all use the same basic commands and concepts.

SQL Syntax

Here is a basic overview of the syntax of SQL:

Data Definition Language (DDL)

DDL statements are used to define the database schema. They create, alter, and drop database objects such as tables, indices, and views.

CREATE TABLE table_name (
column1 datatype PRIMARY KEY,
column2 datatype NOT NULL,
column3 datatype DEFAULT 0
);
ALTER TABLE table_name
ADD column_name datatype;
DROP TABLE table_name;

Data Manipulation Language (DML)

DML statements are used to manage data within schema objects. They select, insert, update, delete, and lock rows in tables.

SELECT * FROM table_name;
INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);
UPDATE table_name
SET column1 = value1,
column2 = value2
WHERE condition;
DELETE FROM table_name
WHERE condition;

Data Control Language (DCL)

DCL statements are used to control access to the data. They grant and revoke permissions on the database objects.

GRANT SELECT, INSERT, UPDATE
ON table_name
TO user_name;
REVOKE SELECT, INSERT, UPDATE
ON table_name
FROM user_name;

Transaction Control Language (TCL)

TCL statements are used to manage the transactions in a database. A transaction is a unit of work that is performed in a database.

BEGIN TRANSACTION;

ROLLBACK  TRANSACTION;

COMMIT TRANSACTION;

SQL SELECT Statement

The SELECT statement is used to retrieve data from a database. It is the most commonly used SQL statement.

Here is the basic syntax of the SELECT statement:

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

The SELECT clause specifies the columns that you want to retrieve. The FROM clause specifies the table that you want to retrieve the data from. The WHERE clause specifies a condition that must be met for the row to be included in the result set.

You can also use the * operator to select all columns in a table:

SELECT * FROM table_name;

You can also use the DISTINCT keyword to select only unique values:

SELECT DISTINCT column1 FROM table_name;

You can use the ORDER BY clause to sort the result set by a specific column:

SELECT column1, column2
FROM table_name
ORDER BY column1;

You can also use the LIMIT clause to limit the number of rows returned:

SELECT column1, column2
FROM table_name
LIMIT 10;

You can use the GROUP BY clause to group the result set by a specific column:

SELECT column1, SUM(column2)
FROM table_name
GROUP BY column1;

You can use the HAVING clause to specify a condition for the groups:

SELECT column1, SUM(column2)
FROM table_name
GROUP BY column1
HAVING SUM(column2) > 100;

You can use the JOIN clause to join multiple tables:

SELECT t1.column1, t2.column2
FROM table1 AS t1
JOIN table2
AS t2
ON t1.column3 = t2.column3;

SQL SELECT DISTINCT Statement

The SELECT DISTINCT statement is used to retrieve unique values from a database. It filters out any duplicate rows in the result set.

Here is the basic syntax of the SELECT DISTINCT statement:

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

The SELECT DISTINCT clause specifies that you want to retrieve only unique values. The FROM clause specifies the table that you want to retrieve the data from. The WHERE clause specifies a condition that must be met for the row to be included in the result set.

For example, if you have a table called “Customers” with the following data:

CustomerID Name City
1 John New York
2 Mary London
3 John Paris
4 Mary London

If you run the following SELECT DISTINCT statement:

SELECT DISTINCT Name
FROM Customers;

The result set would be:

Name
John
Mary

As you can see, the result set only includes unique values for the “Name” column. The duplicate rows for “John” and “Mary” are filtered out.

SQL WHERE Clause

The WHERE clause is used to filter the result set of a SELECT, UPDATE, DELETE, or MERGE statement. It specifies a condition that rows must meet to be included in the result set.

Here is the basic syntax of the WHERE clause:

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

The WHERE clause is added after the FROM clause and before the ORDER BY clause.

The condition can be any expression that returns a Boolean value (true or false). If the condition evaluates to true, the row is included in the result set. If the condition evaluates to false, the row is excluded from the result set.

You can use comparison operators such as =, <, >, <=, >=, and <> in the condition. You can also use logical operators such as AND, OR, and NOT to combine multiple conditions.

For example, if you have a table called “Customers” with the following data:

CustomerID Name City
1 John New York
2 Mary London
3 John Paris
4 Mary London

If you run the following SELECT statement with a WHERE clause:

SELECT *
FROM Customers
WHERE City = 'London';

The result set would be:

CustomerID Name City
2 Mary London
4 Mary London

As you can see, the result set only includes rows where the “City” column is equal to “London”.

SQL AND, OR and NOT Operators

The AND, OR, and NOT operators are used to combine multiple conditions in a SELECT, INSERT, UPDATE, or DELETE statement.

The AND operator displays a record if all the conditions separated by AND are TRUE.

The OR operator displays a record if any of the conditions separated by OR is TRUE.

The NOT operator displays a record if the condition is NOT TRUE.

Here is an example using AND and OR:

SELECT * FROM customers
WHERE country='Mexico' AND city='Mexico City' OR city='Monterrey';

This statement would return all customers from Mexico City and Monterrey.

Here is an example using NOT:

SELECT * FROM customers
WHERE NOT country='Mexico';

This statement would return all customers who are not from Mexico.

It’s important to use parentheses to specify the order of the conditions in your statement, like this:

SELECT * FROM customers
WHERE (country='Mexico' AND city='Mexico City') OR city='Monterrey';

Without the parentheses, the AND operator would be evaluated before the OR operator, which may not produce the desired result.

SQL ORDER BY Keyword

The ORDER BY keyword is used in a SELECT statement to sort the result set by one or more columns.

Here is an example:

SELECT * FROM customers
ORDER BY last_name, first_name;

This statement would sort the result set by the last_name column and then by the first_name column. The result set would be sorted in ascending alphabetical order by default. If you want to sort the result set in descending order, you can use the DESC keyword.

SELECT * FROM customers
ORDER BY last_name DESC, first_name DESC;

You can also specify the ORDER BY clause with a column number, like this:

SELECT * FROM customers
ORDER BY 2, 3;

This would sort the result set by the second and third columns in the SELECT statement.

It’s important to note that the ORDER BY clause should always be the last clause in a SELECT statement, unless you are using the LIMIT clause.

SQL INSERT INTO Statement

The INSERT INTO statement is used to insert new records in a table.

Here is the basic syntax:

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

For example:

INSERT INTO customers (customer_id, last_name, first_name, email)
VALUES (1, 'Smith', 'John', '[email protected]');

This would insert a new record in the customers table with the values 1, ‘Smith’, ‘John’, and ‘[email protected]‘ in the customer_id, last_name, first_name, and email columns, respectively.

You can also insert multiple records in one statement by using the following syntax:

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

It’s important to note that the order of the values must match the order of the columns in the table. Also, if you don’t specify a value for a column, it will be set to NULL by default.

SQL NULL Values

n SQL, NULL represents a missing or unknown value. A NULL value is different from a zero value or a blank space. If a column in a table is optional, you can use NULL to represent the absence of a value.

Here is an example of how to use NULL in an INSERT statement:

INSERT INTO customers (customer_id, last_name, first_name, phone)
VALUES (1, 'Smith', 'John', NULL);

In this example, the phone column for the new customer would have a NULL value, since no value was provided.

You can use the IS NULL and IS NOT NULL operators to test for NULL values in a SELECT, INSERT, UPDATE, or DELETE statement.

For example:

SELECT * FROM customers
WHERE phone IS NULL;

This statement would return all customers with a NULL value in the phone column.

SELECT * FROM customers
WHERE phone IS NOT NULL;

This statement would return all customers with a non-NULL value in the phone column.

It’s important to note that you cannot compare a value to NULL using the = operator. Instead, you must use the IS NULL or IS NOT NULL operator.

SQL UPDATE Statement

The UPDATE statement is used to modify existing records in a table.

Here is the basic syntax:

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

For example:

UPDATE customers
SET email = '[email protected]'

WHERE customer_id = 1;

This statement would update the email of the customer with an ID of 1 to ‘[email protected]‘.

You can update multiple columns in a single statement by separating them with a comma:

UPDATE customers
SET email = '[email protected]', phone = '555-555-5555'

WHERE customer_id = 1;

It’s important to include the WHERE clause in the UPDATE statement, otherwise all records in the table will be updated.

You can also use the UPDATE statement with a JOIN clause to update records in one table based on values in another table.

For example:

UPDATE customers
INNER JOIN orders ON customers.customer_id = orders.customer_id
SET customers.status = 'inactive'

WHERE orders.order_date < '2022-01-01';

This statement would set the status of all customers with orders placed before 2022 to ‘inactive’.

SQL DELETE Statement

The DELETE statement is used to delete existing records in a table.

Here is the basic syntax:

DELETE FROM table_name
WHERE condition;

For example:

DELETE FROM customers
WHERE customer_id = 1;

This statement would delete the customer with an ID of 1 from the customers table.

It’s important to include the WHERE clause in the DELETE statement, otherwise all records in the table will be deleted.

You can also use the DELETE statement with a JOIN clause to delete records in one table based on values in another table.

For example:

DELETE customers
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id
WHERE orders.order_date < '2022-01-01';

This statement would delete all customers with orders placed before 2022 from the customers table.

It’s important to use caution when deleting records, as the operation cannot be undone. It may be a good idea to create a backup of your database before performing a DELETE operation.

SQL TOP, LIMIT, FETCH FIRST or ROWNUM Clause

The TOP, LIMIT, FETCH FIRST, and ROWNUM clauses are used to specify the number of records to return in a SELECT statement.

The syntax for the TOP clause depends on the database you are using. Here is the syntax for the TOP clause in Microsoft SQL Server:

SELECT TOP number|percent column_name(s)
FROM table_name;

For example:

SELECT TOP 10 * FROM customers;

This statement would return the top 10 records from the customers table.

The syntax for the LIMIT clause depends on the database you are using. Here is the syntax for the LIMIT clause in MySQL:

SELECT column_name(s)
FROM table_name
LIMIT number;

For example:

SELECT * FROM customers
LIMIT 10;

This statement would return the first 10 records from the customers table.

The syntax for the FETCH FIRST clause depends on the database you are using. Here is the syntax for the FETCH FIRST clause in IBM DB2:

SELECT column_name(s)
FROM table_name
FETCH FIRST number ROWS ONLY;

For example:

SELECT * FROM customers
FETCH FIRST 10 ROWS ONLY;

This statement would return the first 10 records from the customers table.

The syntax for the ROWNUM clause depends on the database you are using. Here is the syntax for the ROWNUM clause in Oracle:

SELECT column_name(s)
FROM table_name
WHERE ROWNUM <= number;

For example:

SELECT * FROM customers
WHERE ROWNUM <= 10;

This statement would return the first 10 records from the customers table.

It’s important to note that the order of the records returned by these clauses may not be consistent unless you specify an ORDER BY clause.

SQL MIN() and MAX() Functions

The MIN() and MAX() functions are used to find the minimum and maximum values in a set of values, respectively.

Here is the syntax for the MIN() function:

SELECT MIN(column_name)
FROM table_name;

For example:

SELECT MIN(price)
FROM products;

This statement would return the minimum value in the price column of the products table.

Here is the syntax for the MAX() function:

SELECT MAX(column_name)
FROM table_name;

For example:

SELECT MAX(price)
FROM products;

This statement would return the maximum value in the price column of the products table.

You can also use the MIN() and MAX() functions with a WHERE clause to find the minimum or maximum value in a specific group of records.

For example:

SELECT MIN(price)
FROM products
WHERE category = 'Clothing';

This statement would return the minimum price of products in the Clothing category.

It’s important to note that the MIN() and MAX() functions ignore NULL values. If you want to include NULL values in your calculation, you can use the COALESCE() function.

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

The COUNT(), AVG(), and SUM() functions are used to perform calculations on a set of values.

The COUNT() function returns the number of records in a table or the number of non-NULL values in a column.

Here is the syntax for the COUNT() function:

SELECT COUNT(column_name)
FROM table_name;

For example:

SELECT COUNT(*) FROM customers;

This statement would return the number of records in the customers table.

SELECT COUNT(email) FROM customers;

This statement would return the number of customers with a non-NULL email value.

The AVG() function returns the average value of a numeric column.

Here is the syntax for the AVG() function:

SELECT AVG(column_name)
FROM table_name;

For example:

SELECT AVG(price)
FROM products;

This statement would return the average price of all products.

The SUM() function returns the sum of all values in a numeric column.

Here is the syntax for the SUM() function:

SELECT SUM(column_name)
FROM table_name;

For example:

SELECT SUM(quantity)
FROM orders;

This statement would return the total quantity of all orders.

You can also use the COUNT(), AVG(), and SUM() functions with a WHERE clause to perform calculations on a specific group of records.

For example:

SELECT SUM(quantity) FROM orders
WHERE status = 'shipped';

This statement would return the total quantity of shipped orders.

It’s important to note that the COUNT() function ignores NULL values, while the AVG() and SUM() functions include NULL values in their calculations. If you want to exclude NULL values from the calculation, you can use the COALESCE() function.

SQL LIKE Operator

The LIKE operator is used in a WHERE clause to search for a specific pattern in a column.

The syntax for the LIKE operator is:

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;

The pattern can be a string of characters, or it can include wildcard characters such as % (percent sign) and _ (underscore).

The % wildcard matches any number of characters, including zero characters.

The _ wildcard matches a single character.

Here are some examples of using the LIKE operator:

SELECT * FROM customers
WHERE last_name LIKE 'Smi%';

This statement would return all customers with a last name that starts with ‘Smi’.

SELECT * FROM customers
WHERE last_name LIKE '%ith';

This statement would return all customers with a last name that ends with ‘ith’.

SELECT * FROM customers
WHERE last_name LIKE '%mit%';

This statement would return all customers with a last name that contains ‘mit’.

SELECT * FROM customers
WHERE email LIKE '%@gmail.com';

This statement would return all customers with a Gmail email address.

It’s important to note that the LIKE operator is case-insensitive by default. If you want to perform a case-sensitive search, you can use the BINARY keyword.

SELECT * FROM customers
WHERE last_name LIKE BINARY 'Smi%';

This statement would return all customers with a last name that starts with ‘Smi’ (case-sensitive).

SQL Wildcards

In SQL, wildcards are characters that can be used to substitute for any other character or characters in a string. Wildcards are often used with the LIKE operator in a WHERE clause to search for a specific pattern in a column.

There are two wildcards in SQL:

  • % (percent sign)
  • _ (underscore)

The % wildcard matches any number of characters, including zero characters.

The _ wildcard matches a single character.

Here are some examples of using wildcards:

SELECT * FROM customers
WHERE last_name LIKE 'Smi%';

This statement would return all customers with a last name that starts with ‘Smi’.

SELECT * FROM customers
WHERE last_name LIKE '%ith';

This statement would return all customers with a last name that ends with ‘ith’.

SELECT * FROM customers
WHERE last_name LIKE '%mit%';

This statement would return all customers with a last name that contains ‘mit’.

SELECT * FROM customers
WHERE email LIKE '%@gmail.com';

This statement would return all customers with a Gmail email address.

It’s important to note that the LIKE operator is case-insensitive by default. If you want to perform a case-sensitive search, you can use the BINARY keyword.

SELECT * FROM customers
WHERE last_name LIKE BINARY 'Smi%';

This statement would return all customers with a last name that starts with ‘Smi’ (case-sensitive).

 

SQL IN Operator

The IN operator allows you to specify multiple values in a WHERE clause.

For example:

SELECT * FROM employees
WHERE last_name IN ('Smith', 'Johnson', 'Williams');

This would return all rows from the employees table where the last_name is either ‘Smith’, ‘Johnson’, or ‘Williams’.

You can also use the IN operator with a subquery:

SELECT * FROM employees
WHERE last_name IN (SELECT last_name FROM customers);

This would return all rows from the employees table where the last_name is in the list of last_name values in the customers table.

The IN operator is often used as a shorter alternative to using multiple OR conditions in a WHERE clause.

 

SQL BETWEEN Operator

The BETWEEN operator is used in a WHERE clause to select a range of values.

For example:

SELECT * FROM employees
WHERE salary BETWEEN 50000 AND 75000;

This would return all rows from the employees table where the salary is greater than or equal to 50000 and less than or equal to 75000.

You can also use the BETWEEN operator with dates:

SELECT * FROM orders
WHERE order_date BETWEEN '2022-01-01' AND '2022-12-31';

This would return all rows from the orders table where the order_date is between January 1, 2022 and December 31, 2022.

Note that the BETWEEN operator is inclusive, meaning that it will include values that are equal to the lower and upper bounds in the range. If you want to exclude the bounds, you can use the > and < operators instead.

 

SQL Aliases

SQL aliases are used to give a temporary name to a table or a column.

Aliases are often used to make column names more readable or to make it easier to join multiple tables together.

Here is an example of how to use an alias to give a table a temporary name:

SELECT * FROM customers c

In this example, the customers table is given the alias c. You can then use the alias to refer to the table in the rest of the query, like this:

SELECT c.first_name, c.last_name FROM customers c

Aliases can also be used with columns:

SELECT c.first_name AS customer_first_name,
c.last_name AS customer_last_name
FROM customers c

In this example, the first_name column is given the alias customer_first_name and the last_name column is given the alias customer_last_name. These aliases can then be used in the rest of the query to refer to the columns.

Aliases are especially useful when you are joining multiple tables together and need to specify which table a column belongs to.

SELECT c.first_name, o.order_date FROM customers c
JOIN orders o ON c.customer_id = o.customer_id

In this example, the customers table is given the alias c and the orders table is given the alias o. This makes it clear which table each column belongs to.

SQL Joins

SQL joins are used to combine rows from two or more tables based on a related column between them.

There are several types of joins:

  • INNER JOIN: Returns rows that have matching values in both tables.
  • OUTER JOIN: Returns all rows from both tables, filling in NULL values for missing matches on either side.
  • LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left table, and any matching rows from the right table.
  • RIGHT JOIN (or RIGHT OUTER JOIN): Returns all rows from the right table, and any matching rows from the left table.

Here is an example of an INNER JOIN:

SELECT * FROM customers
INNER JOIN orders
ON customers.customer_id = orders.customer_id;

This would return all rows from the customers and orders tables where there is a matching customer_id value in both tables.

Here is an example of a LEFT JOIN:

SELECT * FROM customers
LEFT JOIN orders
ON customers.customer_id = orders.customer_id;

This would return all rows from the customers table, and any matching rows from the orders table. If there is no match, NULL values would be returned for the right-side columns.

Joins are a powerful way to combine data from multiple tables and can be used in a wide variety of SQL queries.

 

SQL RIGHT JOIN Keyword

The RIGHT JOIN keyword is used in SQL to return all rows from the right table and any matching rows from the left table.

Here is an example of a RIGHT JOIN:

SELECT * FROM customers
RIGHT JOIN orders
ON customers.customer_id = orders.customer_id;

This would return all rows from the orders table, and any matching rows from the customers table. If there is no match, NULL values would be returned for the left-side columns.

RIGHT JOIN is similar to LEFT JOIN, but the order of the tables is reversed.

You can also use the RIGHT OUTER JOIN keyword, which has the same effect as RIGHT JOIN.

SELECT * FROM customers
RIGHT OUTER JOIN orders
ON customers.customer_id = orders.customer_id;

RIGHT JOIN is often used in conjunction with GROUP BY and HAVING clauses to filter and group data.

SQL FULL OUTER JOIN Keyword

The FULL OUTER JOIN keyword is used in SQL to return all rows from both tables, whether there is a match or not.

Here is an example of a FULL OUTER JOIN:

SELECT * FROM customers
FULL OUTER JOIN orders
ON customers.customer_id = orders.customer_id;

This would return all rows from the customers and orders tables, whether there is a match or not. If there is no match, NULL values would be returned for the missing columns.

FULL OUTER JOIN is similar to LEFT JOIN and RIGHT JOIN, but it includes all rows from both tables, not just the matching ones.

You can also use the FULL OUTER JOIN keyword in conjunction with the UNION keyword to achieve the same result:

SELECT * FROM customers
LEFT JOIN orders
O
N customers.customer_id = orders.customer_id
UNION
SELECT * FROM customers
RIGHT JOIN orders
ON customers.customer_id = orders.customer_id;

FULL OUTER JOIN is often used to combine data from multiple tables and fill in missing values with NULL.

SQL Self Join

A self join is a type of join in which a table is joined to itself.

Self joins are useful when you want to compare rows within a table or find relationships between rows within the same table.

Here is an example of a self join:

SELECT e1.first_name, e1.last_name,
e2.first_name,
e2.last_name
FROM employees e1
JOIN employees e2
ON e1.manager_id = e2.employee_id;

This query would join the employees table to itself, using the manager_id and employee_id columns to match rows. The result would be a list of employees and their managers.

You can also use self joins with the INNER JOIN, LEFT JOIN, and RIGHT JOIN keywords to specify the type of join you want to use.

For example, you could use a LEFT JOIN to include all employees in the result, even if they don’t have a manager:

SELECT e1.first_name,
e1.last_name,
e2.first_name,
e2.last_name
FROM employees e1
LEFT JOIN employees e2
ON e1.manager_id = e2.employee_id;

Self joins can be useful for a variety of tasks, such as finding the highest-paid employee in each department or finding all employees who report to a specific manager.

SQL UNION Operator

The UNION operator is used to combine the result sets of two or more SELECT statements into a single result set.

The UNION operator removes duplicate rows between the result sets, unless the ALL keyword is specified.

Here is an example of how to use the UNION operator:

SELECT first_name, last_name FROM employees
UNION
SELECT first_name, last_name FROM customers;

This would return a list of unique first and last names from both the employees and customers tables.

To include duplicates, you can use the UNION ALL operator:

SELECT first_name, last_name FROM employees
UNION ALL
SELECT first_name, last_name FROM customers;

The UNION operator can be useful for combining data from multiple tables or for combining the results of multiple SELECT statements.

Note that the SELECT statements must have the same number of columns and the corresponding columns must have compatible data types in order to use the UNION operator.

SQL GROUP BY Statement

The GROUP BY statement is used in SQL to group together rows that have the same values in one or more columns.

For example:

SELECT department, COUNT(*) as num_employees FROM employees
GROUP BY department;

This would return a list of departments and the number of employees in each department. The rows would be grouped by the department column and the COUNT(*) function would be used to count the number of employees in each group.

You can also use the GROUP BY statement with multiple columns:

SELECT department, job_title, COUNT(*) as num_employees FROM employees
GROUP BY department, job_title;

This would return a list of departments, job titles, and the number of employees in each department and job title combination. The rows would be grouped by both the department and job_title columns.

The GROUP BY statement is often used in conjunction with aggregate functions such as COUNT, SUM, MIN, and MAX to perform calculations on the grouped data.

You can also use the HAVING clause to filter the grouped data based on a condition.

SELECT department, COUNT(*) as num_employees FROM employees
GROUP BY department
HAVING COUNT(*) > 10;

This would return a list of departments with more than 10 employees.

SQL HAVING Clause

The HAVING clause is used in SQL to filter the results of a GROUP BY statement.

The HAVING clause is used in combination with the GROUP BY statement and works similar to the WHERE clause, but operates on grouped data rather than individual rows.

Here is an example of how to use the HAVING clause:

SELECT department, COUNT(*) as num_employees FROM employees
GROUP BY department
HAVING COUNT(*) > 10;

This would return a list of departments with more than 10 employees. The rows would be grouped by the department column and the COUNT(*) function would be used to count the number of employees in each group. The HAVING clause would then filter out any groups with less than 10 employees.

You can use any of the comparison operators (>, <, =, etc.) in the HAVING clause, as well as any of the aggregate functions (COUNT, SUM, MIN, MAX, etc.).

The HAVING clause is often used to filter the results of a GROUP BY statement based on a specific condition. It can be used in a variety of scenarios, such as finding the departments with the highest or lowest number of employees or the departments with the highest or lowest average salary.

SQL EXISTS Operator

The EXISTS operator is used in SQL to test whether a subquery returns any rows.

The EXISTS operator returns a Boolean value of true or false. If the subquery returns at least one row, the EXISTS operator will return true. If the subquery returns no rows, the EXISTS operator will return false.

Here is an example of how to use the EXISTS operator:

SELECT * FROM employees e
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.employee_id = e.employee_id);

This would return all rows from the employees table where there is at least one row in the orders table with a matching employee_id value.

You can use the NOT operator with the EXISTS operator to find rows that do not match the subquery:

SELECT * FROM employees e
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.employee_id = e.employee_id);

This would return all rows from the employees table where there are no rows in the orders table with a matching employee_id value.

The EXISTS operator can be useful for optimizing queries and is often used in combination with the IN, ANY, and ALL operators.

SQL ANY and ALL Operators

The ANY and ALL operators are used in SQL to compare a value to a list of values.

The ANY operator returns true if the value is equal to any value in the list.

The ALL operator returns true if the value is equal to all values in the list.

Here is an example of how to use the ANY operator:

SELECT * FROM employees
WHERE salary >ANY (SELECT salary FROM employees WHERE department = ‘Sales’);

This would return all rows from the employees table where the salary is greater than any of the salaries in the employees table for the ‘Sales’ department.

Here is an example of how to use the ALL operator:

SELECT * FROM employee
WHERE salary > ALL (SELECT salary FROM employees WHERE department = 'Sales');

This would return all rows from the employees table where the salary is greater than all of the salaries in the employees table for the ‘Sales’ department.

The ANY and ALL operators can be used with any comparison operator (>, <, =, etc.) and are often used in conjunction with the IN operator.

SELECT * FROM employees
WHERE salary >ANY (SELECT salary FROM employees WHERE department IN (‘Sales’, ‘Marketing’));

This would return all rows from the employees table where the salary is greater than any of the salaries in the employees table for the ‘Sales’ or ‘Marketing’ departments.

SQL SELECT INTO Statement

The SELECT INTO statement is used in SQL to create a new table and insert data into it from a SELECT statement.

Here is an example of how to use the SELECT INTO statement:

SELECT * INTO new_table FROM existing_table WHERE condition;

This would create a new table called new_table and insert all rows from existing_table that meet the condition into the new table.

The new table will have the same column names and data types as the original table, and the data will be inserted in the same order as it appears in the original table.

You can also specify the column names and data types for the new table:

SELECT column1, column2, column3 INTO new_table (column1, column2, column3) FROM existing_table WHERE condition;

This would create a new table called new_table with three columns (column1, column2, column3) and insert only the specified columns from existing_table into the new table.

The SELECT INTO statement is a convenient way to create a new table from an existing table or query and is often used to make a backup copy of a table.

SQL INSERT INTO SELECT Statement

The INSERT INTO SELECT statement is used in SQL to insert data into a table from the result of a SELECT statement.

Here is an example of how to use the INSERT INTO SELECT statement:

INSERT INTO new_table (column1, column2, column3)
SELECT column1, column2, column3 FROM existing_table WHERE condition;

This would insert data into the new_table from the existing_table where the condition is met. The column1, column2, and column3 values would be inserted into the corresponding columns in the new_table.

You can also use the INSERT INTO SELECT statement to insert data into multiple tables at the same time:

INSERT INTO new_table1 (column1, column2, column3)
SELECT column1, column2, column3 FROM existing_table
UNION
INSERT INTO new_table2 (column1, column2, column3)
SELECT column1, column2, column3 FROM existing_table;

This would insert data into both new_table1 and new_table2 from the existing_table.

The INSERT INTO SELECT statement is a convenient way to insert data into a table from the result of a SELECT statement and can be used to insert data into multiple tables at the same time.

SQL CASE Expression

The CASE expression is used in SQL to create conditional logic.

Here is an example of how to use the CASE expression:

SELECT first_name, last_name,
CASE
WHEN salary > 50000 THEN 'High salary'
WHEN salary > 40000 THEN 'Medium salary'

ELSE 'Low salary'

END as salary_group
FROM employees;

This would return a list of employees with a salary_group column that indicates whether their salary is ‘High’, ‘Medium’, or ‘Low’ based on the salary value.

The CASE expression has three parts:

  1. The WHEN clause, which specifies a condition to test.
  2. The THEN clause, which specifies the value to return if the condition is true.
  3. The ELSE clause, which specifies the value to return if all conditions are false.

You can use multiple WHEN clauses in a single CASE expression to test multiple conditions.

SELECT first_name, last_name,
CASE

WHEN salary > 50000 THEN 'High salary'

WHEN salary > 40000 THEN 'Medium salary'

WHEN salary > 30000 THEN 'Low salary'
ELSE 'Very low salary'
END as salary_group
FROM employees;

This would return a list of employees with a salary_group column that indicates their salary group based on a range of salary values.

The CASE expression is a powerful tool for creating conditional logic in SQL queries and is often used in conjunction with the GROUP BY and ORDER BY clauses.

SQL NULL Functions

QL has several functions that can be used to handle NULL values. These functions include:

  1. ISNULL() – This function replaces NULL with a specified value.
SELECT ISNULL(column, 0) FROM table;

This would replace any NULL values in the column with 0.

  1. COALESCE() – This function returns the first non-NULL value in a list.
SELECT COALESCE(column1, column2, column3) FROM table;

This would return the first non-NULL value from column1, column2, or column3.

  1. NULLIF() – This function returns NULL if two values are equal.
SELECT NULLIF(column1, column2) FROM table;

This would return NULL if column1 and column2 are equal, and the value of column1 if they are not.

  1. IFNULL() – This function is similar to ISNULL(), but is available in MySQL and SQLite.
SELECT IFNULL(column, 0) FROM table;

This would replace any NULL values in the column with 0.

These functions can be useful for handling NULL values in SQL queries and ensuring that the resulting data is accurate and useful.

SQL Stored Procedures for SQL Server

A stored procedure is a group of pre-compiled SQL statements that can be executed by calling the procedure by name. Stored procedures are used to perform a specific task or set of tasks, and can accept input parameters and return multiple output values.

Stored procedures can be used to improve the performance of a database by reducing the number of times the same set of SQL statements are executed. They can also be used to enforce business logic, improve security, and reduce the amount of code that needs to be written and maintained.

Here is an example of how to create a stored procedure in SQL Server:

CREATE PROCEDURE GetEmployees
@Department varchar(50)
AS
BEGIN
SELECT * FROM employees
WHERE department = @Department
END

This stored procedure, called GetEmployees, accepts a single input parameter called @Department and returns all rows from the employees table where the department column is equal to the @Department parameter.

To execute the stored procedure, you can use the following statement:

EXEC GetEmployees 'Sales';

This would execute the GetEmployees stored procedure and return all rows from the employees table where the department column is equal to ‘Sales’.

Stored procedures can be used in a variety of scenarios and can be a useful tool for improving the performance and maintainability of a database.

SQL Comments

SQL comments are used to add explanatory notes to SQL code. They are ignored by the database and are not executed as part of the code.

There are two types of comments in SQL:

  1. Single-line comments – These comments start with two hyphens (–) and extend to the end of the line.
-- This is a single-line comment
  1. Multi-line comments – These comments start with /* and end with */.
/* This is a
multi-line comment */

Here is an example of how to use comments in SQL:

SELECT * FROM employees; -- This retrieves all rows from the employees table
/*
This is a multi-line comment.
It can span multiple lines and is often used to add explanations or
documentation to the code.
*/

Comments can be useful for adding explanations or documentation to SQL code, and can make it easier for others to understand and maintain the code.

SQL Operators

SQL has a variety of operators that can be used to perform operations on data and expressions.

Here is a list of some common SQL operators:

  1. Arithmetic operators – These operators perform basic arithmetic operations, such as addition (+), subtraction (-), multiplication (*), and division (/).
SELECT 10 + 5; -- returns 15
SELECT 10 - 5; -- returns 5
SELECT 10 * 5; -- returns 50
SELECT 10 / 5; -- returns 2
  1. Comparison operators – These operators compare two expressions and return a Boolean value of true or false. The comparison operators include:
  • = – equal to
  • <> or != – not equal to
  • > – greater than
  • < – less than
  • >= – greater than or equal to
  • <= – less than or equal to
SELECT 10 = 5; -- returns false

SELECT 10 <> 5; -- returns true
SELECT 10 > 5; -- returns true

SELECT 10 < 5; -- returns false

SELECT 10 >= 5; -- returns true
SELECT 10 <= 5; -- returns false
  1. Logical operators – These operators perform logical operations, such as AND, OR, and NOT.
SELECT 10 > 5 AND 5 < 3; -- returns false

SELECT 10 > 5 OR 5 < 3; -- returns true
SELECT NOT (5 < 3); -- returns true
  1. Set operators – These operators perform set operations, such as UNION and INTERSECT.
SELECT * FROM table1
UNION
SELECT * FROM table2;
SELECT * FROM table1
INTERSECT
S
ELECT * FROM table2;
  1. Other operators – There are a variety of other operators in SQL, such as the BETWEEN operator, the IN operator, and the LIKE operator.
SELECT * FROM table
WHERE value BETWEEN 10 AND 20;
SELECT * FROM table
WHERE value IN (10, 20, 30);
SELECT * FROM table
WHERE value LIKE 'A%';

These are just a few examples of the operators available in SQL. There are many more operators available, and they can be used in a variety of ways to manipulate data and perform operations

Scroll to Top