We may not have the course you’re looking for. If you enquire or give us a call on +49 8000101090 and speak to our training experts, we may still be able to help with your training requirements.
Training Outcomes Within Your Budget!
We ensure quality, budget-alignment, and timely delivery by our expert instructors.
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.
Frequently Asked Questions
DDL statements define the database’s structure, while DML statements manage the database. Additionally, DDL statements impact the whole table, whereas DML statements affect only the defined rows of a table.
SQL commands can be divided into five main categories:
a) Data Definition Language (DDL)
b) Data Query Language (DQL)
c) Data Manipulation Language (DML)
d) Data Control Language (DCL)
e) Transaction Control Language (TCL)
The Knowledge Academy takes global learning to new heights, offering over 30,000 online courses across 490+ locations in 220 countries. This expansive reach ensures accessibility and convenience for learners worldwide.
Alongside our diverse Online Course Catalogue, encompassing 19 major categories, we go the extra mile by providing a plethora of free educational Online Resources like News updates, Blogs, videos, webinars, and interview questions. Tailoring learning experiences further, professionals can maximise value with customisable Course Bundles of TKA.
The Knowledge Academy’s Knowledge Pass, a prepaid voucher, adds another layer of flexibility, allowing course bookings over a 12-month period. Join us on a journey where education knows no bounds.
The Knowledge Academy offers various SQL Courses, including the Introduction to SQL Course and the PostgreSQL Administration Course. These courses cater to different skill levels, providing comprehensive insights into SQL Developer Job Description.
Our Programming & DevOps Blogs cover a range of topics related to SQL, offering valuable resources, best practices, and industry insights. Whether you are a beginner or looking to advance your SQL skills, The Knowledge Academy's diverse courses and informative blogs have got you covered.
Upcoming Programming & DevOps Resources Batches & Dates
Date
Mon 4th Nov 2024
Mon 18th Nov 2024
Mon 2nd Dec 2024
Mon 16th Dec 2024
Mon 13th Jan 2025
Mon 10th Feb 2025
Mon 10th Mar 2025
Mon 7th Apr 2025
Mon 12th May 2025
Mon 9th Jun 2025
Mon 14th Jul 2025
Mon 11th Aug 2025
Mon 8th Sep 2025
Mon 13th Oct 2025
Mon 10th Nov 2025
Mon 8th Dec 2025