Basic SQL Commands with Examples

Handling database is a constantly growing need for organisations managing massive sets of customer information. As a result, the demand for experienced database administrators and engineers has increased significantly. The most popular relational databases rely on Structured Query Language (SQL) as the operating language. You need to learn SQL Commands to use the database effectively. 

According to the 2022 State of Developer Ecosystem Report, about 50 per cent of all developers have programmed with SQL within the year. The same report reveals that half of these developers are involved in back-end programming. Read this blog to learn the various SQL Commands like SELECT, WHERE, UPDATE, DELETE, CREATE, DROP, and more. 

Table of Contents 

1) A brief look at SQL Commands  

2) A list of basic SQL Commands  

   a) SELECT commands  

   b) AS commands 

   c) From commands 

   d) WHERE commands 

3) Conclusion 

A brief look at SQL Commands 

SQL commands are basic structure of SQL queries used as instructions to communicate with a database. The instructions are executed to perform various tasks, functions and queries on data. Database Engineers can use these commands to search a database and to perform other functions like creating tables, adding data into them, modifying their data and dropping or deleting tables. SQL statements are written in English and designed in a simple declarative format. This format helps maintain data accuracy, security and integrity.

A list of basic SQL Commands 

Here is a detailed list of the basic SQL Commands you must learn and be familiar with.

SELECT commands 

The SELECT command is the most utilised statement in SQL. Database Engineers and Administrators execute them for data queries in databases. The commands help them define the data they want their query to return. Here are the various SELECT commands: 

a) SELECT: This is the most basic use of the SELECT command. It is used to select a column from a table. For example, you can select a column called ‘department’ from a table called ‘teachers’ in the following way: 

SELECT department 
FROM teachers; 

b) SELECT *: This variant used with the ‘*’ symbol returns all the columns from the table being queried, in the following way: 

SELECT * FROM teachers; 

c) SELECT DISTINCT: This variant returns only the distinct data, which means that if there are duplicate data records, the command will return just one copy. This variant is written in the following way: 

SELECT DISTINCT name 

FROM teachers; 

d) SELECT INTO: This variant copies the user-specified data from a table into another in the following way: 

SELECT * INTO teachers 
FROM teachers_backup; 

e) SELECT TOP: This variant returns only the top ‘x’ numbers or per cent from a table in the following way: 

Top ‘x’ results: 

SELECT TOP 100 * FROM teachers; 

Top ‘x’ per cent: 

SELECT TOP 100 PERCENT * FROM customers; 

AS command 

The ‘AS’ command renames the table or its column with a user-defined pseudonym or alias. For example, you can rename the ‘name’ column as ‘last_name’ using the following function: 

SELECT name as last_name 

FROM teachers; 

FROM Command 

The ‘FROM’ command in SQL specifies the table from which the user retrieves data. It is written as follows: 

SELECT name 

FROM teachers; 

WHERE command 

The ‘WHERE’ command in SQL lets the user filter their query to return the results that match the defined condition. They can use this command with conditional operators like < , > , = , >= , <=, and so on. It is written as: 

SELECT name 

FROM teachers; 

WHERE name = ‘Benedict’; 

AND command 

The ‘AND’ command in SQL merges two or more conditions into a single query. All the conditions must be satisfied to return a result. It is written as: 

SELECT name 

FROM teachers; 

WHERE name = ‘Benedict’ and age = 60; 

Learn the how to create tables in SQL and SQL Server debugger by joining our Advanced SQL course.  

OR command 

The ‘OR’ command in SQL merges two or more conditions into a single query. However, only one of the conditions must be satisfied to return a result. It is written as: 

SELECT name 

FROM teachers; 

WHERE name = ‘Benedict’ OR age = 60; 

BETWEEN command 

The ‘BETWEEN’ command in SQL lets the user filter their query to return the results which fit their specified range. It is written as: 

SELECT name 

FROM teachers; 

WHERE age BETWEEN 55 and 60; 

LIKE Command

The ‘LIKE’ command in SQL lets the user search for specified patterns in the column. It is written as: 

SELECT name 

FROM teachers; 

WHERE name like ‘%Benedict’; 
 
The LIKE command can be used with other operators such as: 

a) %x – selects all the values which start with x 

b) %x% - selects all the values that contain x 

c) x% - selects all the values which end with x 

d) x%y  - selects all the values that start with x and end with y 

e) _x% - selects all the values which contain x as their second character 

f) x_% - selects all the values which start with x and at least two characters long. Users can add more characters to extend the length, e.g. x__% 

IN Command 

The ‘IN’ command in SQL allows users to specify multiple values they want to retrieve when executing the ‘WHERE’ command. It is written as: 

SELECT name 

FROM teachers 

WHERE name IN (‘Benedict’, ‘Fred’, ‘Harry’); 

IS NULL command 

The ‘IS NULL’ command in SQL returns the rows containing a NULL value. It is written as: 

SELECT name 

FROM teachers 

WHERE name IS NULL; 

IS NOT NULL command 

The ‘IS NOT NULL’ command in SQL works opposite to the ‘IS NULL’ command. It returns the rows without a NULL value.  

SELECT name 

FROM teachers 

WHERE name IS NOT NULL; 

CREATE DATABASE command 

The ‘CREATE DATABASE’ command in SQL creates a new database for the user, assuming they have the required administrative rights. It is written as: 

CREATE DATABASE dataquestDB; 

CREATE TABLE command 

The ‘CREATE TABLE’ command in SQL creates a new table for the user in the database. It can be written with the variables ‘int’ and ‘varchar(255)’, as follows: 

CREATE TABLE teachers ( 
teacher_id int, 
name varchar(255), 
age int 

); 

Learn Database Concepts by signing up for the Introduction to MySQL course.  

CREATE INDEX command 

The ‘CREATE INDEX’ command in SQL generates a table index, which users use to retrieve the required data faster. 

CREATE INDEX index_name 

ON teachers (name); 

CREATE VIEW command 

The ‘CREATE VIEW’ command in SQL generates a virtual table created from the result data set of an SQL statement. A view is a normal table on which a user can perform queries, but it cannot be saved like a permanent table in the database. It is written as: 

CREATE VIEW [Benedict Teachers] AS 

SELECT name, age 

FROM teachers 

WHERE name = ‘Benedict’; 

DROP command 

The DROP statement in SQL can be used to remove a whole database, index or table. Administrators should take care to execute the DROP command with caution. The variants of the DROP statement are as follows: 

a) DROP DATABASE:  

The ‘DROP Database’ command erases the entire database, including its data. This is a command that users must use judiciously. It is written as:

DROP DATABASE dataquestDB;

b) DROP TABLE: 

The ‘DROP TABLE’ command deletes the table and its data. It is written as:

DROP TABLE teachers;

c) DROP INDEX: 

The ‘DROP INDEX’ command deletes the index from within the database. It is written as: 

DROP INDEX idx_name; 

UPDATE command 

The ‘UPDATE’ statement updates the data in a table. For example, the code below will update the age of the teacher named ‘Benedict’ in the teacher’s table to 61.  

UPDATE teachers 

SET age = 61 

WHERE name = ‘Benedict’; 

DELETE command 

The ‘DELETE’ Command removes all the rows from a table using the ‘WHERE’ clause to erase the rows which satisfy the specific condition. It is written as: 

DELETE FROM teachers 

WHERE name = ‘Benedict’; 

ALTER TABLE command 

The ‘ALTER TABLE’ command lets users add or remove columns from the table. Below are two codes that users can follow the structure of, to add and remove a column for ‘surname’. The text varchar(255) specifies the column’s datatype. The code snippets can be written as follows:  

a) Add a column:

ALTER TABLE teachers 

ADD surname varchar(255); 

b) Remove a column: 

ALTER TABLE teachers 

DROP COLUMN surname; 

Aggregate Functions 

The Aggregate functions in SQL are COUNT, AVG, SUM, MIN and MAX. These functions let the user perform calculations on values and return a single result. The aggregate functions in SQL can be written as follows: 

a) COUNT: The count function returns the rows matching the user-specified criteria. Users can refer to the code structure as shown below, using the * symbol to return the row count for teachers.

SELECT COUNT(*) 

FROM customers; 

b) SUM: The sum function returns the sum of a numeric column to the user, written as follows:

SELECT SUM(age) 

FROM teachers;

c) AVG: The avg function returns the average value of a numeric column to the user, written as follows:

SELECT AVG(age) 

FROM teachers;

d) MIN: The min function returns the smallest value of the numeric column, written as follows:

SELECT MIN(age) 

FROM teachers;

e) MAX: The max function returns the largest value of the numeric column, written as follows:

SELECT MAX(age) 

FROM teachers; 

f) GROUP BY: The group by statement collects rows containing the same values into summary rows. This statement is generally used with aggregate functions. For example, the code below displays the average age for every name appearing in the ‘teacher’s’ table.

SELECT name, AVG(age) 

FROM teachers 

GROUP BY name;

g) HAVING: This statement performs the same action as the ‘WHERE’ clause. The ‘having’ function is used for the aggregate functions, and the ‘WHERE’ clause does not work with them. Written below is an example that returns the number of rows for every name, specifically the ones related to more than two records:

SELECT COUNT(teacher_id), name 

FROM teachers 

GROUP BY name 

HAVING COUNT(teacher_id) > 2;

h) ORDER BY: This statement lets the administrator fix the order of the returned results, where the order will be in an ascending fashion. The code can be written as follows:

SELECT name 

FROM teachers 

ORDER BY age;

i) DESC: This statement returns the result to the user in a descending order. The code can be written as follows:

SELECT name 

FROM teachers 

ORDER BY age DESC;

j) OFFSET: This statement works in the same query with the ORDER BY statement. It basically specifies the number of rows to be skipped before returning the rows from the query. The code can be written as follows:

SELECT name 

FROM teachers 

ORDER BY age 

OFFSET 15 ROWS;

k) FETCH: This statement is used when the user wants to specify the number of rows to be returned after the ‘OFFSET’ clause has been executed. The clause is mandatory, whereas the FETCH statement is an optional clause. The code can be written as:

SELECT name 

FROM teachers 

ORDER BY age 

OFFSET 15 ROWS 

FETCH NEXT 15 ROWS ONLY; 

Learn how to design and create a report by using SQL Server reporting by joining our SQL Server Reporting Services (SSRS) Masterclass.  

JOINS 

The Join clause in SQL is used to merge the rows from two or more tables in a database. The JOIN clause comprises four types are INNER JOIN, LEFT JOIN, RIGHT JOIN AND FULL JOIN (or FULL OUTER JOIN). Here are code snippet examples for each join: 

a) INNER JOIN: The inner join selects the records which have matching values in both the tables. Below is a code snippet example:

SELECT name 

FROM teachers 

INNER JOIN subjects 

ON teachers.teacher_id = subjects.teacher_id;

b) LEFT JOIN: The left join selects the records from the first table which match the records from the second table. Below is a code snippet example, where the first table is ‘teachers’:

SELECT name 

FROM teachers 

LEFT JOIN subjects 

ON teachers.teachers_id = subjects.teachers_id;

c) RIGHT JOIN: The right join selects the records from the second table that match the records from the first table. Below is a code snippet example, where the second table is ‘subjects’:

SELECT name 

FROM teachers 

RIGHT JOIN subjects 

ON teachers.teachers_id = subject.teachers_id;

d) FULL JOIN: The full join, also called the ‘full outer join’ selects the records that match fro the first and second table. Below is a code snipper example:

SELECT name 

FROM teachers 

FULL OUTER JOIN subjects 

ON teachers.teacher_id = subjects.teachers_id; 

GRANT command 

The grant command in SQL allows specific users access to the SQL database objects like tables, the database or views. Below is a code snippet example using the ‘SELECT’ command with the ‘GRANT’ command in a single query: 

GRANT SELECT, UPDATE ON teachers TO usr1_benedict; 

COMMIT command 

The commit command in SQL allows users to save their transactions to the database. The command will remove the active save points, followed by the statement’s execution. At this point, the user cannot roll back the transaction. Below is a code snippet using the ‘COMMIT’ command: 

DELETE FROM teachers 

WHERE name = ‘Benedict’; 

COMMIT 

ROLLBACK command 

The rollback command in SQL lets users undo the executed transactions. The condition for the rollback is that the transaction should not be saved to the database. The command can be used only to undo executions for transactions since the last ROLLBACK or COMMIT command. SQL also allows users to roll back to the most recent SAVEPOINT. The command can be written as follows: 

ROLLBACK TO SAVEPNT_NAME; 

TRUNCATE command 

The truncate command erases all the data records from a table in the database. The table and its structure are still retained. This command is like the DELETE command. The command can be written as follows: 

TRUNCATE TABLE teachers; 

Conclusion 

This blog acts as a reference guide for Database Administrators and Engineers to execute their commands in SQL. The different SQL Commands and their variations can be written according to their databases and requirements. The above commands will also help beginners play around with different operations to observe the outcomes of the database data. SQL users of all competency levels will benefit from the above list for job interviews and real-world projects. 

Get ahead in your data career with our SQL courses! Gain real-world skills, hands-on experience, & certifications that matter.