Basic SQL Commands with Examples

Database Management continues to draw the spotlight as ever-increasing data inundates the tech world and our online experience. Now, imagine having the power to manipulate data with a few commands! That's what Structured Query Language (SQL) brings with its broad range of powerful commands, including UPDATE, DELETE, CREATE, DROP and countless more. 

This blog details how these Basic SQL Commands serve as the building blocks of structured data. So, dive in and journey through the essential SQL Commands every database management enthusiast must master!

Table of Contents 

1) A Brief Look at SQL Commands  

2) A List of Basic SQL Commands  

         a) SELECT Commands  

         b) AS Commands 

         c) WHERE Commands

         d) AND command

         e) OR Command

         f) BETWEEN Command

        g) LIKE Command

        h) IN Command

        i) IS NULL Command

        j) IS NOT NULL Command

3) Conclusion 

A Brief Look at SQL Commands 

SQL Commands are Basic Structure of SQL Queries used as instructions to communicate with a database by performing various queries and functions on data. Database Engineers utilise these commands to search a database and perform tasks such as creating tables, adding and modifying their data, and dropping or deleting tables. These SQL statements are written in English and designed in a simple declarative format which helps maintain data security, accuracy, and integrity.

A List of Basic SQL Commands 

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

SELECT Commands 

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

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

SELECT department  FROM teachers;

b) SELECT *:  This variant used in conjunction with the ‘*’ symbol returns every column from the table being queried in this way: 

SELECT * FROM teachers;

c) SELECT DISTINCT: This variant only returns the distinct data, which means that in case of duplicate data records, the command will return simply one copy. This variant is typed in the following way: 

SELECT DISTINCT name  FROM teachers;

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

SELECT * INTO teachers  FROM teachers_backup;

e) SELECT TOP: This command only returns the top ‘x’ percent or numbers 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 

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

SELECT name as last_name  FROM teachers;  FROM Command

This command specifies the table from which the user retrieves data and is written as follows: 

SELECT name  FROM teachers;

WHERE Command 

The ‘WHERE’ command enables the user to filter their query to return the results that match the defined condition. This command can be used with conditional operators such as < , > , = , >= , <=, and so on. It is written as: 

SELECT name  FROM teachers;  WHERE name = ‘Benedict’;

AND Command 

The ‘AND’ command helps developers merge multiple conditions into a single query. All the conditions must be satisfied to return a result and is written as: 

SELECT name  FROM teachers;  WHERE name = ‘Benedict’ and age = 60;

Learn 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) Course.  

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 from 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 

Mastering the Basic SQL Commands outlined in this blog is essential for anyone handling databases and SQL users of all competency levels. From selecting data to updating or deleting records, these commands form the backbone of database management. Practising these commands with real-world examples will enhance your overall data handling and analysis skills.

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