SQL Quick Reference: Most Common SQL Commands
In this post, we will cover the basics of SQL commands with short and easy to understand examples.
This list of SQL commands are the ones that you will most likely be using, so get to know them pretty well.
Each SQL command is provided with a description and an example code snippet.
Most Common SQL Commands
SQL statements can be grouped in different categories:
Data Definition Language(DDL) Commands
- CREATE: creates a new database object, such as a table.
- ALTER: used to modify the database object
- DROP: used to delete the objects.
Data Manipulation Language(DML) Commands
- INSERT: used to insert a new data row record in a table.
- UPDATE: used to modify an existing record in a table.
- DELETE: used delete a record from the table.
Data Query Language(DQL) Commands
- SELECT: it is the DQL command to select data from the database.
Data Control Language(DCL) Commands
- GRANT: used to assign permission to users to access database objects.
- REVOKE: used to deny permission to users to access database objects.
Data Transfer Language(DTL) Commands
- COMMIT: used to save any transaction into the database permanently.
- ROLLBACK: restores the database to the last committed state.
In this post, we will cover the commands for DDL, DML and DQL.
CREATE DATABASE
The first thing we need to do to work with SQL is to create a database. The CREATE DATABASE statement does exactly that.
Example:
CREATE DATABASE testDB
CREATE TABLE
The CREATE TABLE statement creates a new table in a database.
Example:
CREATE TABLE Employees (
EmployeeID int,
FirstName varchar(255),
LastName varchar(255),
Department varchar(255)
);
INSERT INTO
The INSERT INTO statement inserts new rows of data into a table
Example:
INSERT INTO Employees (FirstName, LastName, Department)
VALUES ('Sam', 'Burger', 'IT');
SELECT
SELECT is one of the main and most used SQL command. It selects data from a database and returns the table of results, called the result-set.
Example:
SELECT firstName, lastName
FROM Employees;
SELECT *
The SELECT command when used with an asterisk * operator, selects all records from a specified table.
Example:
SELECT * FROM Employees
SELECT DISTINCT
SELECT DISTINCT returns only the data that is distinct; i.e. does not include duplicate entries.
Example:
SELECT DISTINCT Department FROM Employees;
SELECT INTO
The SELECT INTO statement selects specified data from a table and copies it to another table.
Example:
SELECT firstName, entryGraduated INTO StudentAlumni
FROM Students;
SELECT TOP
SELECT TOP specifies the maximum number or percentage of data entries to return in a result-set.
SELECT TOP 50 PERCENT * FROM Customers;
WHERE
The WHERE clause is used to filter results based on a specified condition.
Example:
SELECT * FROM Employees
WHERE department = 'IT';
GROUP BY
The GROUP BY command arranges identical data from different rows into groups, thus creating summarizing rows.
Example:
SELECT COUNT(Department), Department
FROM Employees
GROUP BY Department;
HAVING
The HAVING clause performs the same as the WHERE clause, but the difference is that HAVING only works with aggregate functions. Likewise, WHERE clause doesn’t work with aggregate functions.
Example:
SELECT COUNT(Department), Department
FROM Employees
GROUP BY Department
HAVING COUNT(Department) > 2;
IN
The IN operator includes multiple values into the WHERE clause.
Example:
SELECT * FROM Employees
WHERE Department IN ('IT', 'Graphics', 'Marketing');
BETWEEN
BETWEEN operator filters the results and returns only the ones that fit the specified range.
Example:
SELECT * FROM Employees
WHERE JoiningDate BETWEEN '01-01-2015' AND `01-01-2020`;
AND / OR
The AND and OR are conditional statements. In AND, all conditions must meet a specified criteria. In OR any of the conditions that satisfy a given criteria returns a result.
Example AND:
SELECT * FROM Employees
WHERE Department = 'IT' AND JoiningDate > '01-01-2015';
Example OR:
SELECT * FROM Employees
WHERE Department ='IT' OR Department = 'Graphics';
AS (Alias)
AS works as an alias. With AS, we can rename a column to something more meaningful or shorter in the query, without having to change the name in the database.
Example:
SELECT FirstName AS fname, LastName AS lname
FROM Employees;
INNER JOIN
INNER JOIN combines rows from different tables.
Example:
SELECT Orders.ID, Customers.Name
FROM Orders
INNER JOIN Customers ON Orders.ID = Customers.ID;
LEFT JOIN
LEFT JOIN retrieves records from the left table that match records in the right table.
Example:
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerName;
RIGHT JOIN
Opposite of the LEFT JOIN, the RIGHT JOIN retrieves records from the right table that match records in the left table.
Example:
SELECT Orders.OrderID, Employees.LastName
FROM Orders
RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
ORDER BY Orders.OrderID;
FULL JOIN
FULL JOIN returns all the records that match either in left or right tables.
Example:
SELECT Customers.Name, CustomerOrders.ID
FROM Customers
FULL OUTER JOIN Orders ON Customers.ID = CustomerOrders.customerID
ORDER BY Customers.Name;
DELETE
The DELETE statement removes certain rows from the table that meet a specified condition.
Example:
DELETE FROM Employees
WHERE FirstName = 'Sam' AND LastName = 'Burger';
ALTER TABLE
We use ALTER TABLE to add or remove columns from a table.
Example:
ALTER TABLE Employees
ADD JoiningDate date;
TRUNCATE TABLE
TRUNCATE TABLE removes data entries from a table in a database, but keeps the table structure.
Example:
TRUNCATE TABLE temp_table
DROP TABLE
DROP TABLE statement deletes the whole table with its column parameters and datatype settings.
Example:
DROP TABLE temp_table
DROP DATABASE
DROP DATABASE deletes the whole specified database together with all its parameters and data.
Be extra cautious when using this command.
Example:
DROP DATABASE temp_db