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.
The first thing we need to do to work with SQL is to create a database. The
CREATE DATABASE statement does exactly that.
CREATE DATABASE testDB
CREATE TABLE statement creates a new table in a database.
CREATE TABLE Employees ( EmployeeID int, FirstName varchar(255), LastName varchar(255), Department varchar(255) );
INSERT INTO statement inserts new rows of data into a table
INSERT INTO Employees (FirstName, LastName, Department) VALUES ('Sam', 'Burger', 'IT');
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.
SELECT firstName, lastName FROM Employees;
SELECT command when used with an asterisk
* operator, selects all records from a specified table.
SELECT * FROM Employees
SELECT DISTINCT returns only the data that is distinct; i.e. does not include duplicate entries.
SELECT DISTINCT Department FROM Employees;
SELECT INTO statement selects specified data from a table and copies it to another table.
SELECT firstName, entryGraduated INTO StudentAlumni FROM Students;
SELECT TOP specifies the maximum number or percentage of data entries to return in a result-set.
SELECT TOP 50 PERCENT * FROM Customers;
WHERE clause is used to filter results based on a specified condition.
SELECT * FROM Employees WHERE department = 'IT';
GROUP BY command arranges identical data from different rows into groups, thus creating summarizing rows.
SELECT COUNT(Department), Department FROM Employees GROUP BY Department;
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.
SELECT COUNT(Department), Department FROM Employees GROUP BY Department HAVING COUNT(Department) > 2;
IN operator includes multiple values into the WHERE clause.
SELECT * FROM Employees WHERE Department IN ('IT', 'Graphics', 'Marketing');
BETWEEN operator filters the results and returns only the ones that fit the specified range.
SELECT * FROM Employees WHERE JoiningDate BETWEEN '01-01-2015' AND `01-01-2020`;
AND / OR
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.
SELECT * FROM Employees WHERE Department = 'IT' AND JoiningDate > '01-01-2015';
SELECT * FROM Employees WHERE Department ='IT' OR Department = 'Graphics';
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.
SELECT FirstName AS fname, LastName AS lname FROM Employees;
INNER JOIN combines rows from different tables.
SELECT Orders.ID, Customers.Name FROM Orders INNER JOIN Customers ON Orders.ID = Customers.ID;
LEFT JOIN retrieves records from the left table that match records in the right table.
SELECT Customers.CustomerName, Orders.OrderID FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID ORDER BY Customers.CustomerName;
Opposite of the LEFT JOIN, the
RIGHT JOIN retrieves records from the right table that match records in the left table.
SELECT Orders.OrderID, Employees.LastName FROM Orders RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID ORDER BY Orders.OrderID;
FULL JOIN returns all the records that match either in left or right tables.
SELECT Customers.Name, CustomerOrders.ID FROM Customers FULL OUTER JOIN Orders ON Customers.ID = CustomerOrders.customerID ORDER BY Customers.Name;
DELETE statement removes certain rows from the table that meet a specified condition.
DELETE FROM Employees WHERE FirstName = 'Sam' AND LastName = 'Burger';
ALTER TABLE to add or remove columns from a table.
ALTER TABLE Employees ADD JoiningDate date;
TRUNCATE TABLE removes data entries from a table in a database, but keeps the table structure.
TRUNCATE TABLE temp_table
DROP TABLE statement deletes the whole table with its column parameters and datatype settings.
DROP TABLE temp_table
DROP DATABASE deletes the whole specified database together with all its parameters and data.
Be extra cautious when using this command.
DROP DATABASE temp_db