MySQL Functions

Functions
A function is a predefined command set that performs some operation and returns the single
value.

SQL Aggregate Functions

SQL aggregate functions return a single value, calculated from values in a column.
Useful aggregate functions:
 AVG() - Returns the average value
 COUNT() - Returns the number of rows
 FIRST() - Returns the first value
 LAST() - Returns the last value
 MAX() - Returns the largest value
 MIN() - Returns the smallest value
 SUM() - Returns the sum
SQL Scalar functions
SQL scalar functions return a single value, based on the input value.
Useful scalar functions:
 UCASE() - Converts a field to upper case
 LCASE() - Converts a field to lower case
 MID() - Extract characters from a text field
 LEN() - Returns the length of a text field
 ROUND() - Rounds a numeric field to the number of decimals specified
 NOW() - Returns the current system date and time
 FORMAT() - Formats how a field is to be displayed
Numeric Functions
> POWER(): Returns the argument raised to the specified power. POW () works the same way.
Example:(i)POW(2,4):Result:16 (ii)POW(2,-2):Result:0.25 (iii)POW(-2,3):Result: -8
> ROUND(): ROUND(X) Rounds the argument to the zero decimal place, where as ROUND(X,d)
rounds the argument to d decimal places.
Example :
(i) ROUND(-1.23); Result: -1
(ii) ROUND(-1.58); Result: -2
(iii) ROUND(1.58); Result: 2
(iv) ROUND(3.798, 1); Result: 3.8
(v) ROUND(1.298, 0); Result: 1
(vi) ROUND(23.298, -1); Result: 20
(vii) ROUND( 25.298,-1); result: 30
> TRUNCATE(): Truncates the argument to specified number of decimal places.
Example: (i) TRUNCATE (7.29,1) Result: 7.2 (ii) TRUNCATE(27.29,-1) Result: 20
> SIGN(): Returns sign of a given number.
Example: (i) SIGN (15) Result: 1 : (ii) SIGN (-15) Result : -1 : (iii) SIGN (0) Result : 0.
> SQRT: Returns the square root of given number.
Example: (i) SQRT (25) Result: 5
Character/String Functions
> LENGTH(): Returns the length of a string in bytes/no.of characters in string.
Example: LENGTH(‘INFORMATICS’); Result:11
> CHAR(): Returns the corresponding ASCII character for each integer passed.
Example: CHAR(65) ; Result : A
> CONCAT(): Returns concatenated string i.e. it adds strings.
Example: CONCAT(‘Informatics’,’ ‘,‘Practices’); Result : Informatics Practices’
> INSTR(): Returns the index of the first occurrence of substring.
Example: INSTR(‘Informatics’,’ mat’); Result : 6(since ‘m’ of ‘mat’ is at 6th place)
> LOWER()/ LCASE(): Returns the argument after converting it in lowercase.
Example: LOWER(‘INFORMATICS’); Result: informatics
> UPPER()/ UCASE(): Returns the argument after converting it in uppercase.
Example: UCASE(‘informatics’); Result: INFORMATICS
> LEFT () : Returns the given number of characters by extracting them from the left side of the
given string
Example : LEFT(‘INFORMATICS PRACTICES’, 3); Result : INF
> RIGHT(): Returns the given number of characters by extracting them from the right side of the
given string
Example : RIGHT(‘INFORMATICS PRACTICES’,3); Result: CES
> MID(): Returns a substring starting from the specified position in a given string.
Example: MID(‘INFORMATICS PRACTICES’,3,4); Result : FORM
> SUBSTR(): Returns a substring from a given string.
Example: SUBSTR(‘INFORMATICS’ , 3 , 4 ) ; Result : FORM
SUBSTR(‘INFORMATICS’ , -3 , 2 ) ; Result : IC
> LTRIM(): Removes leading spaces.
Example : LTRIM(‘ INFORMATICS’); Result: ‘INFORMATICS’
> RTRIM(): Removes trailing spaces.
Example : RTRIM(‘INFORMATICS ‘); Result: ‘INFORMATICS’
> TRIM(): Removes leading and trailing spaces.
Example: TRIM(‘ INFORMATICS ‘); Result: ‘INFORMATICS’
Date/Time Functions
> CURDATE(): Returns the current date
Example: CURDATE(); Result: ‘2012-09-18’
> NOW(): Returns the current date and time
Example: NOW(); Result : ‘2010-07-21 13:58:11’
> SYSDATE(): Return the time at which the function executes
Example: SYSDATE(); Result: ‘2010-07-21 13:59:23’
> DATE(): Extracts the date part of a date or datetime expression
Example: DATE(‘2003-12-31 01:02:03’); Result:: ‘2003-12-31’
> MONTH() Returns the month from the date passed
Example: MONTH(‘2010-07-21’); Result : 7
>MONTHNAME() Returns the name of month from the date passed
Example: MONTHNAME(‘2010-07-21’); Result : JULY
> YEAR(): Returns the year.
Example: YEAR(‘2010-07-21’); Result : 2010
> DAYNAME(): Returns the name of the weekday
Example: DAYNAME(‘2010-07-21’); Result : WEDNESDAY
> DAYOFMONTH(): Returns the day of the month (0-31)
Example: DAYOFMONTH(‘2010-07-21’); Result: 21
> DAYOFWEEK(): Returns the weekday index of the argument
Example: DAYOFWEEK(‘2010-07-21’); Result: 4 (Sunday is counted as 1)
> DAYOFYEAR(): Return the day of the year(1-366)
Example: DAYOFYEAR(‘2010-07-21’); Result: 202

Operators used with WHERE clause

 Arithmetic operators
 Relation operators
 Logical operators
 BETWEEN operators
 IN operators
 LIKE operators
Arithmetic operators
 We can use arithmetic operators to define expressions in DML SQL commands.
 Arithmetic operators are used with numeric values.
 Following table depicts various arithmetic operators:
Arithmetic Operator Description
+ For adding values
– For subtracting values
* For multiplying values
/ For dividing values
% For finding remainders
Example:
Relational operators
 We can use Relational operators to compare values in SQL.
 Mostly used to define criteria in WHERE clause to filter records.
 Following table depicts various Relational operators:
Relational Operator Description
= Equal to
> Greater than
< Lesser than
>= Greater than equal to
<= Lesser than equal to
!= or <> Not equal to
Example:
Logical operators
 Logical operators are used to combine multiple criteria/expression in DML SQL
commands
 Mostly used to combine criteria in WHERE clause to filter records.
 Following table depicts various Logical operators:
Logical Operator Description
AND Returns true when all criteria/expressions are true
OR Returns true when at least a criteria/expression is true
NOT Negates the result
BETWEEN operator
 It is used to define range of values for a particular field in WHERE clause.
 It can be used to define range of numerical or chronological values
 It includes upper and lower bound given in the range.
Example:
IN operator
 It is used to set multiple text comparison for a particular field in WHERE clause.
 It can only be used for text comparision.
Example:
LIKE operator
 It is used to define string expressions (pattern matching) in WHERE clause.
 It uses different wildcards (described in image given below) to define string expression.
Wildcard Description
% Used to match the occurrence of set of characters of any length
_ Underscore used to match occurrence of any single character
Example:
Order by clause
 Used to display records in sequential manner in data resultset retrieved by WHERE
clause.
 Bydefault displays records in ascending order.
 Keyword ASC (Ascending) or DESC (descending) can be used with order by to arrange
the data resultset.

Indexing in Databases
Indexing improves database performance by minimizing the number of disc visits required to
fulfill a query. It is a data structure technique used to locate and quickly access data in
databases. The main key or candidate key of the table is duplicated in the first column, which is
the Search key. To speed up data retrieval, the values are also kept in sorted order. The second
column is the Data Reference or Pointer which contains a set of pointers holding the address of
the disk block where that particular key value can be found.
MySQL UNION
Summary: in this tutorial, you will learn how to use MySQL UNION operator to combine two or
more result sets from multiple SELECT statements into a single result set.
MySQL UNION operator
MySQL UNION operator allows you to combine two or more result sets of queries into a single
result set. The following illustrates the syntax of the UNION operator:
SELECT column_list
UNION [DISTINCT | ALL]
SELECT column_list
UNION [DISTINCT | ALL]
SELECT column_list
...Code language: SQL (Structured Query Language) (sql)
To combine result set of two or more queries using the UNION operator, these are the basic
rules that you must follow:
 First, the number and the orders of columns that appear in all SELECT statements must
be the same.
 Second, the data types of columns must be the same or compatible.
By default, the UNION operator removes duplicate rows even if you don’t specify the DISTINCT
operator explicitly.
UNION vs. JOIN
A JOIN combines result sets horizontally, a UNION appends result set vertically. The following
picture illustrates the difference between UNION and JOIN:

JOIN
Joins are used in relational databases to combine data from multiple tables based on a common
column between them. A foreign key may be used to reference a row in another table and join
can be done based on those columns. Two or more tables may have some related data, and to
combine all the data from multiple tables joins are used.
There are different types of joins in MySQL.
1. INNER JOIN
2. LEFT JOIN
3. RIGHT JOIN
4. CROSS JOIN
INNER JOIN
The INNER JOIN produces the output by combining those rows which have matching column
values.
Syntax:
SELECT column_names
FROM table1
INNER JOIN table2 ON table1.common_column=table2.common_column
INNER JOIN table3 ON table1.common_column=table3.common_column
...;
LEFT JOIN
The LEFT JOIN returns all the rows from the left table ‘A’ and the matching rows from the right
table ‘B’ in the join. The rows from the left table, which have no matching values in the right
table will be returned with a NULL value in the link column.
Syntax:
SELECT column_names
FROM table1
LEFT JOIN table2 ON table1.common_column=table2.common_column;
RIGHT JOIN
The RIGHT JOIN returns all the rows from the right table ‘B’ and the matching rows from the left
table ‘A’ in the join. The rows from the right table, which have no matching values in the left
table will be returned with a NULL value in the link column.
Syntax:
SELECT Column_names
FROM table1
RIGHT JOIN table2 ON table1.common_column=table2.common_column;
CROSS JOIN
CROSS JOIN returns the cartesian product of rows from the tables in the join. It combines each
row of the first table with each row of the second table. If there are X rows in the first table and
Y rows in the second table then the number of rows in the joined table will be X*Y.
Syntax:
SELECT column_names
FROM table1
CROSS JOIN table2;
GROUP BY
The GROUP BY clause is used to arrange the rows in a group using a particular column value. If
there are multiple rows with the same value for a column then all those rows will be grouped
with that column value.
Syntax:
SELECT column1,column2,…
FROM table_name
WHERE condition
GROUP BY column1,column2, …
Order BY column1, column2, ….
The GROUP BY clause is generally used with aggregate functions like SUM, AVG, COUNT, MAX,
MIN. The aggregate functions provide information about each group.
HAVING
The HAVING clause is used with the GROUP BY clause in a query to specify come conditions and
filter some groups or aggregates that fulfill those conditions. The difference between WHERE
and HAVING is, WHERE is used to filter rows, and HAVING is used to filter groups by applying
some conditions.
Syntax:
SELECT column1, column2, …
FROM table_name
WHERE conditions
GROUP BY column1, column2, …..
HAVING conditions
Primary Key
In the relational model, a table shall not contain duplicate rows, because that would create
ambiguity in retrieval. To ensure uniqueness, each table should have a column (or a set of
columns), called primary key, that uniquely identifies every record of the table. For example, an
unique number customerID can be used as the primary key for the customers table; productCode for
products table; isbn for books table. A primary key is called a simple key if it is a single column; it is
called a composite key if it is made up of several columns. Most RDBMSs build an index on the
primary key to facilitate fast search. The primary key is often used to relate to other tables.
Foreign Key
A foreign key of a child table is used to reference the parent table. Foreign key constraint can be
imposed to ensure so-called referential integrity - values in the child table must be valid values
in the parent table.
Sub-Query
Results of one query can be used in another SQL statement. Subquery is useful if more than one
tables are involved.
SELECT with Subquery
In the previous many-to-many product sales example, how to find the suppliers that do not
supply any product? You can query for the suppliers that supply at least one product in the
products_suppliers table, and then query the suppliers table for those that are not in the previous
result set.
mysql> SELECT suppliers.name from suppliers
WHERE suppliers.supplierID
NOT IN (SELECT DISTINCT supplierID from products_suppliers);
View
Views: A view is a virtual table whose contents are taking from other tables depending upon a
condition.
Table: Student
Roll. No. Name Marks

101 Anu 85
 102 Riya 70

103 Ankit 78
Definition of the VIEW:
CREATE VIEW toppers AS SELECT * FROM Student WHERE Marks > 75;
Here name of the view is toppers
Base table is students
toppers(A virtual table based on Student table)
Roll. No. Name Marks

101 Anu 85

102 Ankit 78
mysql> DROP VIEW IF EXISTS patient_view;

mysql> CREATE VIEW patient_view

AS
SELECT
patientID AS ID,
name AS Name,
dateOfBirth AS DOB,
TIMESTAMPDIFF(YEAR, dateOfBirth, NOW()) AS Age
FROM patients
ORDER BY Age, DOB;

ACID Properties in DBMS

DBMS is the management of data that should remain integrated when any changes are done in
it. It is because if the integrity of the data is affected, whole data will get disturbed and
corrupted. Therefore, to maintain the integrity of the data, there are four properties described
in the database management system, which are known as the ACID properties. The ACID
properties are meant for the transaction that goes through a different group of tasks, and there
we come to see the role of the ACID properties.
ACID Properties
The expansion of the term ACID defines for:
1) Atomicity
The term atomicity defines that the data remains atomic. It means if any operation is
performed on the data, either it should be performed or executed completely or should not be
executed at all.
2) Consistency
The word consistency means that the value should remain preserved always. In DBMS, the
integrity of the data should be maintained, which means if a change in the database is made, it
should remain preserved always.
3) Isolation
The term 'isolation' means separation. In DBMS, Isolation is the property of a database where
no data should affect the other one and may occur concurrently. In short, the operation on one
database should begin when the operation on the first database gets complete. It means if two
operations are being performed on two different databases, they may not affect the value of
one another.
4) Durability
Durability ensures the permanency of something. In DBMS, the term durability ensures that the
data after the successful execution of the operation becomes permanent in the database. The
durability of the data should be so perfect that even if the system fails or leads to a crash, the
database still survives.
Enforcing integrity constraints in DBMS

Enforcing integrity constraints in a Database Management System (DBMS) is critical to ensuring

that the data stored in the database is accurate, consistent, and reliable. Integrity constraints are
rules that data must comply with to be considered valid.
Here are the primary types of integrity constraints and how they are enforced in a DBMS:

1. Domain Constraints:
Domain constraints specify that the values of an attribute must be from a specific domain. For
example, an age attribute might be constrained to integer values between 0 and 120.
Enforcement:
Data Type Check: The DBMS enforces domain constraints by checking that the data entered
matches the specified data type (e.g., integer, string, date).
Range Check: Values are checked to ensure they fall within the specified range or set (e.g., age
between 0 and 120).
Pattern Check: For string data, patterns (e.g., regular expressions) can enforce formats like email
addresses or phone numbers.
2. Entity Integrity
Entity integrity is a type of Enforcing integrity constraints, it ensures that each entity (or row) in
a database is uniquely identifiable by a primary key. The primary key must be unique and not
null.
Enforcement:
Primary Key Constraint: The DBMS enforces uniqueness by disallowing duplicate values in the
primary key column(s).
NOT NULL Constraint: Ensures that no null values are allowed in the primary key column(s).
3. Referential Integrity
Referential integrity ensures that a foreign key value always points to an existing, valid record in
another table. This maintains the logical consistency of the data.
Enforcement:
Foreign Key Constraint: The DBMS checks that the foreign key value in one table corresponds to
a primary key value in the referenced table.
Cascading Actions: The DBMS can enforce referential integrity through cascading actions such as
ON DELETE CASCADE or ON UPDATE CASCADE, which automatically propagate changes to
foreign keys when the referenced primary key changes.
4. Unique Constraints
Unique constraints ensure that all values in a column or a group of columns are unique across
the table.
Enforcement:
Unique Index: The DBMS creates a unique index on the column(s) specified, disallowing duplicate
values.
5. Check Constraints
Check constraints enforce a condition that each row in the table must satisfy. These are custom
rules defined by the user.
Enforcement:
Check Clause: The DBMS evaluates the condition specified in the CHECK clause whenever data is
inserted or updated. If the condition is violated, the operation is rejected.
6. NULL Constraints
NULL constraints specify whether a column can accept NULL values or not.
Enforcement:
NOT NULL Constraint: The DBMS ensures that columns specified with NOT NULL cannot store
NULL values.

List of SQL Commands

SELECT
SELECT is probably the most commonly-used SQL statement. You'll use it pretty much every
time you query data with SQL. It allows you to define what data you want your query to return.

For example, in the code below, we’re selecting a column called name from a table
called customers.

SELECT name
FROM customers;
SELECT *
SELECT used with an asterisk (*) will return all of the columns in the table we're querying.

SELECT * FROM customers;

SELECT DISTINCT
SELECT DISTINCT only returns data that is distinct — in other words, if there are duplicate
records, it will return only one copy of each.

The code below would return only rows with a unique name from the customers table.

SELECT DISTINCT name

FROM customers;
SELECT INTO
SELECT INTO copies the specified data from one table into another.

SELECT * INTO customers

FROM customers_backup;
SELECT TOP
SELECT TOP only returns the top x number or percent from a table.

The code below would return the top 50 results from the customers table:

SELECT TOP 50 * FROM customers;

The code below would return the top 50 percent of the customers table:

SELECT TOP 50 PERCENT * FROM customers;

AS
AS renames a column or table with an alias that we can choose. For example, in the code
below, we’re renaming the name column as first_name:

SELECT name AS first_name

FROM customers;
FROM
FROM specifies the table we're pulling our data from:
 SELECT name
FROM customers;
WHERE
WHERE filters your query to only return results that match a set condition. We can use this
together with conditional operators like =, >, <, >=, <=, etc.

SELECT name
FROM customers
WHERE name = ‘Bob’;
AND
AND combines two or more conditions in a single query. All of the conditions must be met for
the result to be returned.

SELECT name
FROM customers
WHERE name = ‘Bob’ AND age = 55;
OR
OR combines two or more conditions in a single query. Only one of the conditions must be met
for a result to be returned.

SELECT name
FROM customers
WHERE name = ‘Bob’ OR age = 55;
BETWEEN
BETWEEN filters your query to return only results that fit a specified range.

SELECT name
FROM customers
WHERE age BETWEEN 45 AND 55;
LIKE
LIKE searches for a specified pattern in a column. In the example code below, any row with a
name that included the characters Bob would be returned.

SELECT name
FROM customers
WHERE name LIKE ‘%Bob%’;
Other operators for LIKE:

 %x — will select all values that begin with x

 %x% — will select all values that include x
 x% — will select all values that end with x
 x%y — will select all values that begin with x and end with y
 _x% — will select all values have x as the second character
 x_% — will select all values that begin with x and are at least two characters long. You can add
additional _ characters to extend the length requirement, i.e. x___%

IN
IN allows us to specify multiple values we want to select for when using the WHERE command.

SELECT name
FROM customers
WHERE name IN (‘Bob’, ‘Fred’, ‘Harry’);
IS NULL
IS NULL will return only rows with a NULL value.

SELECT name
FROM customers
WHERE name IS NULL;
IS NOT NULL
IS NOT NULL does the opposite — it will return only rows without a NULL value.

SELECT name
FROM customers
WHERE name IS NOT NULL;
CREATE
CREATE can be used to set up a database, table, index or view.

CREATE DATABASE
CREATE DATABASE creates a new database, assuming the user running the command has the
correct admin rights.

CREATE DATABASE dataquestDB;

CREATE TABLE
CREATE TABLE creates a new table inside a database. The terms int and varchar(255) in this
example specify the datatypes of the columns we're creating.

CREATE TABLE customers (

customer_id int,
name varchar(255),
age int
);
CREATE INDEX
CREATE INDEX generates an index for a table. Indexes are used to retrieve data from a database
faster.

CREATE INDEX idx_name

ON customers (name);
CREATE VIEW
CREATE VIEW creates a virtual table based on the result set of an SQL statement. A view is like a
regular table (and can be queried like one), but it is not saved as a permanent table in the
database.

CREATE VIEW [Bob Customers] AS

SELECT name, age
FROM customers
WHERE name = ‘Bob’;
DROP
DROP statements can be used to delete entire databases, tables or indexes.

It goes without saying that the DROP command should only be used where absolutely
necessary.

DROP DATABASE
DROP DATABASE deletes the entire database including all of its tables, indexes etc as well as all
the data within it.

Again, this is a command we want to be very, very careful about using!

DROP DATABASE dataquestDB;

DROP TABLE
DROP TABLE deletes a table as well as the data within it.

DROP TABLE customers;

DROP INDEX
DROP INDEX deletes an index within a database.

DROP INDEX idx_name;

UPDATE
The UPDATE statement is used to update data in a table. For example, the code below would
update the age of any customer named Bob in the customers table to 56.

UPDATE customers
SET age = 56
WHERE name = ‘Bob’;
DELETE
DELETE can remove all rows from a table (using ), or can be used as part of a WHERE clause to
delete rows that meet a specific condition.

DELETE FROM customers

WHERE name = ‘Bob’;
ALTER TABLE
ALTER TABLE allows you to add or remove columns from a table. In the code snippets below,
we’ll add and then remove a column for surname. The text varchar(255) specifies the datatype of
the column.

ALTER TABLE customers

ADD surname varchar(255);
ALTER TABLE customers
DROP COLUMN surname;
AGGREGATE FUNCTIONS (COUNT/SUM/AVG/MIN/MAX)
An aggregate function performs a calculation on a set of values and returns a single result.

COUNT
COUNT returns the number of rows that match the specified criteria. In the code below, we’re
using *, so the total row count for customers would be returned.

SELECT COUNT(*)
FROM customers;
SUM
SUM returns the total sum of a numeric column.

SELECT SUM(age)
FROM customers;
AVG
AVG returns the average value of a numeric column.

SELECT AVG(age)
FROM customers;
MIN
MIN returns the minimum value of a numeric column.

SELECT MIN(age)
FROM customers;
MAX
MAX returns the maximum value of a numeric column.
SELECT MAX(age)
FROM customers;
GROUP BY
The GROUP BY statement groups rows with the same values into summary rows. The statement
is often used with aggregate functions. For example, the code below will display the average
age for each name that appears in our customers table.

SELECT name, AVG(age)

FROM customers
GROUP BY name;
HAVING
HAVING performs the same action as the WHERE clause. The difference is that HAVING is used
for aggregate functions, whereas WHERE doesn’t work with them.

The below example would return the number of rows for each name, but only for names with
more than 2 records.

SELECT COUNT(customer_id), name

FROM customers
GROUP BY name
HAVING COUNT(customer_id) > 2;
ORDER BY
ORDER BY sets the order of the returned results. The order will be ascending by default.

SELECT name
FROM customers
ORDER BY age;
DESC
DESC will return the results in descending order.

SELECT name
FROM customers
ORDER BY age DESC;
JOINS (INNER, LEFT, RIGHT, FULL)
A JOIN clause is used to combine rows from two or more tables. The four types of JOIN are
INNER, LEFT, RIGHT and FULL.

INNER JOIN
INNER JOIN selects records that have matching values in both tables.

SELECT name
FROM customers
INNER JOIN orders
ON customers.customer_id = orders.customer_id;
LEFT JOIN
LEFT JOIN selects records from the left table that match records in the right table. In the below
example the left table is customers.

SELECT name
FROM customers
LEFT JOIN orders
ON customers.customer_id = orders.customer_id;
RIGHT JOIN
RIGHT JOIN selects records from the right table that match records in the left table. In the
below example the right table is orders.

SELECT name
FROM customers
RIGHT JOIN orders
ON customers.customer_id = orders.customer_id;
FULL JOIN
FULL JOIN selects records that have a match in the left or right table. Think of it as the “OR”
JOIN compared with the “AND” JOIN (INNER JOIN).

SELECT name
FROM customers
FULL OUTER JOIN orders
ON customers.customer_id = orders.customer_id;
GRANT
GRANT gives a particular user access to database objects such as tables, views or the database
itself. The below example would give SELECT and UPDATE access on the customers table to a
user named “usr_bob”.

GRANT SELECT, UPDATE ON customers TO usr_bob;

REVOKE
REVOKE removes a user's permissions for a particular database object.

REVOKE SELECT, UPDATE ON customers FROM usr_bob;

SAVEPOINT
SAVEPOINT allows you to identify a point in a transaction to which you can later roll back.
Similar to creating a backup.

SAVEPOINT SAVEPOINT_NAME;
COMMIT
COMMIT is for saving every transaction to the database. A COMMIT statement will release any
existing savepoints that may be in use and once the statement is issued, you cannot roll back
the transaction.

DELETE FROM customers

WHERE name = ‘Bob’;
COMMIT
ROLLBACK
ROLLBACK is used to undo transactions which are not saved to the database. This can only be
used to undo transactions since the last COMMIT or ROLLBACK command was issued. You can
also rollback to a SAVEPOINT that has been created before.

ROLLBACK TO SAVEPOINT_NAME;
UNION
UNION combines multiple result-sets using two or more SELECT statements and eliminates
duplicate rows.

SELECT name FROM customersUNIONSELECT name FROM orders;

UNION ALL
UNION ALL combines multiple result-sets using two or more SELECT statements and keeps
duplicate rows.

SELECT name FROM customers

UNION
SELECT name FROM orders;
We hope this page serves as a helpf

Key components of SQL include:

1. Data Definition Language (DDL): DDL is used to define and manage the structure of a
database.

DDL Commands:

 CREATE (creates a table)

 ALTER (adds, deletes, modifies, and renames the attributes of the relation)
 DROP (deletes the data)

2. Data Manipulation Language (DML): DML is used for manipulating data within the database.
DML Commands:

 SELECT (retrieve data)

 INSERT (add new data)
 UPDATE (modify existing data)
 DELETE (remove data)

3. Data Query Language (DQL): DQL is a subset of DML focused on retrieving data.

DQL Commands:

 SELECT (used to query the database and retrieve specific information)

4. Data Control Language (DCL): DCL is used to control access to data within the database.
Commands like GRANT and REVOKE are used to assign and revoke permissions, determining
who can access or modify certain data.

DCL Commands:

 GRANT (used for permitting the users)

 REVOKE (used for removing the authorization)

5. Transaction Control Language (TCL): TCL is used to manage transactions within a database.

TCL Commands:

 COMMIT (to save changes)

 ROLLBACK (to undo changes)

1. SELECT Statement

The SELECT statement in SQL is used to retrieve data from one or more tables in a database. It is
a fundamental and powerful command that allows you to query and display information stored
in a relational database.

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

Example:

1. SELECT * FROM employees; (* gives you the complete data of the table).
2. SELECT first_name, last_name FROM employees; (gives you all the data of the first name and
last name from the table employees).

2. SELECT DISTINCT

The SELECT DISTINCT statement is used to retrieve unique values from a specific column in a table.
It eliminates duplicate rows and returns only distinct values.

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

Example:

1. SELECT DISTINCT first_name, last_name FROM students; (gives you the unique values of the
first name and last name from the table students).

3. WHERE Clause

The WHERE clause in SQL is used to filter the rows returned by a SELECT statement based on a
specified condition. It allows you to retrieve only the rows that meet certain criteria.

Syntax: SELECT column1, column2, … FROM table_name WHERE condition;

Example:

1. SELECT * FROM students WHERE age > 20; (gives you the data of students whose age is
above 20).

4. ORDER BY Clause

The ORDER BY clause in SQL is used to sort the result set of a SELECT statement based on one or
more columns. It allows you to specify the order in which the rows should be displayed, either
in ascending (default) or descending order.

Syntax: SELECT column1, column2, ... FROM table_name ORDER BY column1 [ASC | DESC],
column2 [ASC | DESC], ...;

Example:

1. SELECT * FROM students ORDER BY age; (organizes the data of the table by age in
ascending/descending order).
5. LIMIT and OFFSET

The LIMIT and OFFSET clauses in SQL are used to control the number of rows returned by
a SELECT statement, allowing you to retrieve a specific subset of the result set. These clauses are
often used together to implement pagination or to fetch a limited number of rows starting from
a certain position.

Syntax: SELECT column1, column2, … FROM table_name LIMIT number_of_rows OFFSET

offset_value;

Example:

1. SELECT * FROM students LIMIT 2 OFFSET 2; (the LIMIT 2 restricts the result set to two rows,
and the OFFSET 2 skips the first two rows.)

6. INSERT Statement

The INSERT statement in SQL is used to insert new records (rows) into a table. It adds data to an
existing table by specifying the values to be inserted into each column.

Syntax: INSERT INTO table_name (column1, column2, …) VALUES (value1, value2, …);

Example:

1. INSERT INTO students (student_id, first_name, last_name, age, grade) VALUES (7, ‘Alice’,
‘Johnson’, 21, ‘B’); (adds a new student to the students’ table with the required details).

7. UPDATE Statement

The UPDATE statement in SQL is used to modify existing records (rows) in a table. It allows you to
change the values of one or more columns in the specified rows based on a specified condition.

Syntax: UPDATE table_name SET column1 = value1, column2 = value2, … WHERE condition;

Example:

1. UPDATE students SET age = 20, grade = ‘B’ WHERE student_id = 3; (update the students’
table with the student’s age as 20, grade as B where id is 3).

8. DELETE Statement
The DELETE statement in SQL is used to remove records (rows) from a table based on a specified
condition. It permanently removes data from the table.

Syntax: DELETE FROM table_name WHERE condition;

Example:

1. DELETE FROM students WHERE student_id = 4; (deletes the entire data of student whose id is
4 from the table students).

9. COUNT() Function

The COUNT function in SQL is used to count the number of rows in a table or the number of rows
that satisfy a particular condition in a specified column.

Syntax: SELECT COUNT(*) FROM table_name WHERE condition;

Example:

1. SELECT COUNT(*) FROM students; (displays the count of total number of students from the table
students).

10. SUM() Function

The SUM function in SQL is used to calculate the sum of values in a numeric column, typically
within a specified group or for all rows in a table.

Syntax: SELECT SUM(column_name) FROM table_name WHERE condition;

Example:

1. SELECT SUM(amount) FROM sales; (displays the total amount from the sales table).

11. GROUP BY Clause

The GROUP BY clause in SQL is used to group rows that have the same values in specified
columns into summary rows, like “total sales per product” or “average score per student.” It’s
often used with aggregate functions like SUM, COUNT, AVG, etc.

Syntax: SELECT column1, column2, …, aggregate_function(column) FROM table_name WHERE

condition GROUP BY column1, column2, …;
Example:

1. SELECT product, SUM(amount) as total_sales FROM sales GROUP BY product; (groups the
data by the “product” column and displays the result.)

12. HAVING Clause

The HAVING clause in SQL is used in conjunction with the GROUP BY clause to filter the results of a
grouped query. While the WHERE clause filters rows before grouping, the HAVING clause filters
groups after the grouping has taken place. It is commonly used with aggregate functions
like SUM, COUNT, AVG, etc.

Syntax: SELECT column1, column2, …, aggregate_function(column) FROM table_name WHERE

condition GROUP BY column1, column2, … HAVING condition;

Example:

1. SELECT product, SUM(amount) as total_sales FROM sales GROUP BY product HAVING

total_sales > 1000; (it initially sums up the sales amount and groups the data and then displays
only that data which is greater than 1000).

13. JOIN Statements

In SQL, the JOIN statement is used to combine rows from two or more tables based on a related
column between them.

There are different types of joins, including INNER JOIN, LEFT JOIN (or LEFT OUTER JOIN), RIGHT
JOIN (or RIGHT OUTER JOIN), and FULL JOIN (or FULL OUTER JOIN).

Syntax: SELECT columns FROM table1 JOIN table2 ON table1.column = table2.column;

Example:

1. SELECT students.first_name, students.last_name, courses.course_name FROM students JOIN

courses ON students.student_id = courses.course_id; (it combines rows from the “students”
and “courses” tables based on the relationship defined in the ON clause.)

14. Subqueries
A subquery, also known as a nested query or inner query, is a query embedded within another
query. It can be used to retrieve data that will be used in the main query as a condition to
further restrict the data to be retrieved. Subqueries can be used in various parts of a SQL
statement, including the SELECT, FROM, WHERE, and HAVING clauses.

Syntax: SELECT column1, column2, … FROM table_name WHERE column_name operator

(SELECT column_name FROM another_table WHERE condition);

Example:

1. SELECT first_name FROM students WHERE student_id IN (SELECT student_id FROM grades
WHERE grade = ‘A’); (it finds the student_id values from the “grades” table where the grade is ‘A’.
The main query then selects the first_name of students whose student_id is in the result set of the
subquery.)

15. Views

A view is a virtual table based on the result of a SELECT query. It allows you to simplify complex
queries, encapsulate logic, and present a subset or transformation of the data to users without
exposing the underlying table structure.

Views are often used to enhance data security by restricting direct access to certain tables and
providing a controlled way to access specific data.

Syntax: CREATE VIEW view_name AS SELECT column1, column2, … FROM table_name WHERE
condition;

Example:

1. CREATE VIEW high_performers AS SELECT student_id, first_name, last_name, grade FROM

students WHERE grade = ‘A’;

“high_performers” view is created, which includes the columns student_id, first_name, last_name,
and grade from the “students” table. Only the rows where the grade is ‘A’ are included in the
view.

SQL Commands
Codecademy Team

Share
Glossary of commonly used SQL commands.

Background

SQL, Structured Query Language, is a programming language designed to manage data stored in
relational databases. SQL operates through simple, declarative statements. This keeps data
accurate and secure, and it helps maintain the integrity of databases, regardless of size.

Here’s an appendix of commonly used commands.

Commands

ALTER TABLE

ALTER TABLE table_name

ADD column_name datatype;

ALTER TABLE lets you add columns to a table in a database.

AND

SELECT column_name(s)
FROM table_name
WHERE column_1 = value_1
AND column_2 = value_2;

AND is an operator that combines two conditions. Both conditions must be true for the row to
be included in the result set.

AS

SELECT column_name AS 'Alias'

FROM table_name;

AS is a keyword in SQL that allows you to rename a column or table using an alias.

AVG()

SELECT AVG(column_name)
FROM table_name;

AVG() is an aggregate function that returns the average value for a numeric column.

BETWEEN
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value_1 AND value_2;

The BETWEEN operator is used to filter the result set within a certain range. The values can be
numbers, text or dates.

CASE

SELECT column_name,
CASE
WHEN condition THEN 'Result_1'
WHEN condition THEN 'Result_2'
ELSE 'Result_3'
END
FROM table_name;

CASE statements are used to create different outputs (usually in the SELECT statement). It is SQL’s
way of handling if-then logic.

COUNT()

SELECT COUNT(column_name)
FROM table_name;

COUNT() is a function that takes the name of a column as an argument and counts the number of
rows where the column is not NULL.

CREATE TABLE

CREATE TABLE table_name (

column_1 datatype,
column_2 datatype,
column_3 datatype
);

CREATE TABLE creates a new table in the database. It allows you to specify the name of the table
and the name of each column in the table.

DELETE

DELETE FROM table_name

WHERE some_column = some_value;
DELETE statements are used to remove rows from a table.

GROUP BY

SELECT column_name, COUNT(*)

FROM table_name
GROUP BY column_name;

GROUP BY is a clause in SQL that is only used with aggregate functions. It is used in collaboration
with the SELECT statement to arrange identical data into groups.

HAVING

SELECT column_name, COUNT(*)

FROM table_name
GROUP BY column_name
HAVING COUNT(*) > value;

HAVING was added to SQL because the WHERE keyword could not be used with aggregate
functions.

INNER JOIN

SELECT column_name(s)
FROM table_1
JOIN table_2
ON table_1.column_name = table_2.column_name;

An inner join will combine rows from different tables if the join condition is true.

INSERT

INSERT INTO table_name (column_1, column_2, column_3)

VALUES (value_1, 'value_2', value_3);

INSERT statements are used to add a new row to a table.

IS NULL / IS NOT NULL

SELECT column_name(s)
FROM table_name
WHERE column_name IS NULL;
IS NULL and IS NOT NULL are operators used with the WHERE clause to test for empty values.

LIKE

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

LIKE is a special operator used with the WHERE clause to search for a specific pattern in a column.

LIMIT

SELECT column_name(s)
FROM table_name
LIMIT number;

LIMIT is a clause that lets you specify the maximum number of rows the result set will have.

MAX()

SELECT MAX(column_name)
FROM table_name;

MAX() is a function that takes the name of a column as an argument and returns the largest
value in that column.

MIN()

SELECT MIN(column_name)
FROM table_name;

MIN() is a function that takes the name of a column as an argument and returns the smallest
value in that column.

OR

SELECT column_name
FROM table_name
WHERE column_name = value_1
OR column_name = value_2;

OR is an operator that filters the result set to only include rows where either condition is true.
ORDER BY

SELECT column_name
FROM table_name
ORDER BY column_name ASC | DESC;

ORDER BY is a clause that indicates you want to sort the result set by a particular column either
alphabetically or numerically.

OUTER JOIN

SELECT column_name(s)
FROM table_1
LEFT JOIN table_2
ON table_1.column_name = table_2.column_name;

An outer join will combine rows from different tables even if the join condition is not met. Every
row in the left table is returned in the result set, and if the join condition is not met,
then NULL values are used to fill in the columns from the right table.

ROUND()

SELECT ROUND(column_name, integer)

FROM table_name;

ROUND() is a function that takes a column name and an integer as arguments. It rounds the
values in the column to the number of decimal places specified by the integer.

SELECT

SELECT column_name
FROM table_name;

SELECT statements are used to fetch data from a database. Every query will begin with SELECT.

SELECT DISTINCT

SELECT DISTINCT column_name

FROM table_name;

SELECT DISTINCT specifies that the statement is going to be a query that returns unique values in
the specified column(s).

SUM
SELECT SUM(column_name)
FROM table_name;

SUM() is a function that takes the name of a column as an argument and returns the sum of all
the values in that column.

UPDATE

UPDATE table_name
SET some_column = some_value
WHERE some_column = some_value;

UPDATE statements allow you to edit rows in a table.

WHERE

SELECT column_name(s)
FROM table_name
WHERE column_name operator value;

WHERE is a clause that indicates you want to filter the result set to include only rows where the
following condition is true.

WITH

WITH temporary_name AS (
SELECT *
FROM table_name)
SELECT *
FROM temporary_name
WHERE column_name operator value;

WITH clause lets you store the result of a query in a temporary table using an alias. You can also
define multiple temporary tables using a comma and with one instance of the WITH keyword.

The WITH clause is also known as common table expression (CTE) and subquery factoring.
In the era where data is being generated in humongous amounts, there is a constant need
to handle data in databases. Relational databases are one of the most popular databases, and
SQL is the basis of relational databases. Therefore SQL skills are indispensable in most of the
job roles. In this article on SQL Commands, I will discuss the top commands and statements that
you need to understand in SQL.

Comments in SQL
There are two ways in which you can comment in SQL, i.e. either the Single-Line Comments or
the Multi-Line Comments.

Single-Line Comments

The single line comment starts with two hyphens (–). So, any text mentioned after (–), till the
end of a single line will be ignored by the compiler.

Example:

1 --Select all:

2 SELECT * FROM Employee_Info;

Multi-Line Comments

The Multi-line comments start with /* and end with */. So, any text mentioned between /* and
*/ will be ignored by the compiler.

Example:

1 /*Select all the columns

2 of all the records

3 from the Employee_Info table:*/

4 SELECT * FROM Students;

DDL Commands in SQL

This section of the article will give you an insight into the commands through which you can
define your database. The commands are as follows:


 CREATE
 DROP
 TRUNCATE
 ALTER
 BACKUP DATABASE

CREATE
This statement is used to create a table or a database.

The ‘CREATE DATABASE’ Statement

As the name suggests, this statement is used to create a database.

Syntax
CREATE DATABASE DatabaseName;
Example
1 CREATE DATABASE Employee;

The ‘CREATE TABLE’ Statement

This statement is used to create a table.

Syntax
CREATE TABLE TableName (
Column1 datatype,
Column2 datatype,
Column3 datatype,
....

ColumnN datatype
);
Example
1 CREATE TABLE Employee_Info
2 (
3 EmployeeID int,

4 EmployeeName varchar(255),

5 Emergency ContactName varchar(255),

6 PhoneNumber int,

7 Address varchar(255),

8 City varchar(255),

9 Country varchar(255)

);
10
You can also create a table using another table. Refer the below sytax and example:

The ‘CREATE TABLE AS’ Statement

Syntax
CREATE TABLE NewTableName AS
SELECT Column1, column2,..., ColumnN
FROM ExistingTableName
WHERE ....;
Example
1 CREATE TABLE ExampleTable AS

2 SELECT EmployeeName, PhoneNumber

3 FROM Employee_Info;

DROP

This statement is used to drop an existing table or a database.

The ‘DROP DATABASE’ Statement

This statement is used to drop an existing database. When you use this statement, complete
information present in the database will be lost.

Syntax
DROP DATABASE DatabaseName;
Example
1 DROP DATABASE Employee;

The ‘DROP TABLE’ Statement

This statement is used to drop an existing table. When you use this statement, complete
information present in the table will be lost.

Syntax
DROP TABLE TableName;
Example
1 DROP Table Employee_Info;

TRUNCATE
This command is used to delete the information present in the table but does not delete the
table. So, once you use this command, your information will be lost, but not the table.

Syntax
TRUNCATE TABLE TableName;
Example
1 TRUNCATE Table Employee_Info;

ALTER

This command is used to delete, modify or add constraints or columns in an existing table.

The ‘ALTER TABLE’ Statement

This statement is used to add, delete, modify columns in an existing table.

The ‘ALTER TABLE’ Statement with ADD/DROP COLUMN

You can use the ALTER TABLE statement with ADD/DROP Column command according to your
need. If you wish to add a column, then you will use the ADD command, and if you wish to
delete a column, then you will use the DROP COLUMN command.

Syntax
ALTER TABLE TableName
ADD ColumnName Datatype;

ALTER TABLE TableName

DROP COLUMN ColumnName;
Example
1 --ADD Column BloodGroup:

3 ALTER TABLE Employee_Info

4 ADD BloodGroup varchar(255);

6 --DROP Column BloodGroup:

7
 8 ALTER TABLE Employee_Info

9 DROP COLUMN BloodGroup ;

Discover the secrets of efficient data management through our SQL Online Course.

The ‘ALTER TABLE’ Statement with ALTER/MODIFY COLUMN

This statement is used to change the datatype of an existing column in a table.