8 Key SQL Commands With Examples

SQL, or structured query language, is a widely adopted programming language for managing data housed in a relational database. Much like commands in a computer program, SQL statements or queries provide directives for interacting with relational databases. At its core, SQL queries form the foundation of user interaction with databases, streamlining tasks such as data retrieval and manipulation. 

Read on to learn more about SQL, including its origin, practical applications, and commonly used commands. 

The origin of SQL

SQL has a rich and enduring history dates back to the early 1970s. Initially named SEQUEL and developed as part of a project at IBM led by Edgar F. Codd, SQL quickly became the industry standard for relational database management systems (RDBMS). In 1979, Oracle released the first commercial implementation of SQL. 

What is SQL used for?

SQL empowers backend developers, coders, and data scientists to add, locate, or modify data within relational databases. Tabular in structure, a relational database organises information into rows and columns, reflecting various data attributes and the interconnections between data values. Other common utilities of SQL include:

• Setting up a database

• Creating new tables or deleting existing ones

• Configuring user access privileges

• Adding fresh data to an existing database

• Eliminating rows of data

Which industries use SQL?

SQL finds its application across diverse industries, from health care to marketing and social media. For instance, social media managers use SQL to analyse social networks. SQL may be employed in healthcare to access and update patient records. Furthermore, SQL in marketing aids in customer segmentation based on various criteria, such as demographics and website activity, allowing for improved personalized ads.

How does SQL work? 

Structured query language uses a server machine to execute database queries and return corresponding results. The process entails the use of the following software units:

• Parser: Tokenises the SQL statement while also verifying the correctness of syntax and authorisation (user permissions)

• Relational engine (query processor): Generates an optimised plan for data retrieval, addition, or modification using byte code 

• Storage engine (also known as database engine): Deciphers the byte code and executes the SQL statement, reading and storing data in database files on physical storage before returning results to the application requesting data

8 SQL commands with examples

The following are eight commonly used SQL commands, along with their syntax and usage scenarios.

1. CREATE 

The create command facilitates the creation of a database structure, including its components such as tables, views, indexes, functions, and more. 

Syntax: 

1. For creating a new database: CREATE DATABASE name_of_database; 

2. For creating a new table: CREATE TABLE name_of_table

(

column_A data_type(size),

column_B data_type(size),

column_C data_type(size),

….

);

Datatypes may include character/string (e.g., CHAR, VARCHAR), numeric (e.g., INT, FLOAT), or date and time (e.g., DATE, TIME, DATETIME, TIMESTAMP) type, amongst others. Each type serves a distinct purpose. For example, the CHAR datatype accommodates fixed-length character strings, while the VARCHAR datatype stores variable-length character strings.

Example: A registrar could use SQL to create a database of students enrolled in one class.

CREATE TABLE EnroledStudents 

(

    StudentID INT(5),

    StudentName VARCHAR(15),

);

Here, “Students” is the name of the database, while “EnroledStudents” is the table's name. Within the table, StudentID and StudentName represent column names. The StudentName column, holding string values, utilises the VARCHAR (variable character) datatype, allowing a maximum variable length of 15 characters. The StudentID column, intended for numeric values of size 5, uses INT, the integer datatype.

2. INSERT

This SQL command lets you add a new row or record to a table. You can do this in two ways. The first approach involves specifying only the values of the data to be inserted without mentioning the column names. The second or alternative method necessitates specifying the columns you wish to populate and their corresponding values.

Syntax:

1. For inserting values only: 

INSERT INTO table_name VALUES (value1, value2, value3); 

Example 1: If you're looking to include a new record for a student who just enrolled in your school programme, you could use the following SQL insert command:

INSERT INTO EnroledStudents VALUES ('4', 'Vijaya');

This query will insert a new row into the "EnroledStudents" table, specifying student ID and student name as '4' and 'Vijaya' respectively.

2. For inserting both column names and values: 

INSERT INTO table_name (column1, column2, column3) VALUES ( value1, value2, value3);

Example 2: Suppose you have a table named "Orders" with columns "OrderID," "CustomerName," and "OrderDate." Should you wish to insert a new customer order into this table, you may use the following query:

INSERT INTO Orders (OrderID, CustomerName, OrderDate) VALUES (1001, 'Lalita', '2024-03-27');

Your “Orders” table will now include a new record with the specified OrderID (1001), CustomerName ('Lalita'), and OrderDate ('2024-03-27').

3. ALTER

The ALTER command helps you make changes to a table that already exists. Specifically, you can add, delete, or modify table columns and constraints with the ALTER command. 

Syntax: 

1. For adding new column(s) to a table: 

ALTER TABLE table_name ADD (columnname_1  datatype, columnname_2  datatype, …columnname_n  datatype);

Example 1: Let's say you have a table named "Employees" with columns like "FirstName," "LastName," "EmployeeID," and "Age." Now, you want to add a new column called "Department" to store each employee's department information.

ALTER TABLE Employees ADD Department VARCHAR (25); 

This command will alter the "Employees" table by adding a new column named "Department" of datatype VARCHAR with a maximum length of 25 characters.

2. For deleting column(s) from a table: 

ALTER TABLE table_name DROP COLUMN column_name;

Example 2: To reduce bias in employment opportunities, you wish to exclude age details from the “Employees” table.

ALTER TABLE Employees DROP COLUMN Age;

This command will remove the "Age" column from the "Employees" table.

3. For modifying existing column(s) in a table:

ALTER TABLE table_name MODIFY column_name column_type;

Example 3: As per Example 1, the “Department” column has a VARCHAR datatype with a maximum variable length of 25 characters. If there's a preference to switch this datatype to CHAR, indicating a fixed-length character string, you can execute the following command:

ALTER TABLE Employees MODIFY Department CHAR(15);

The "Department" column within the "Employees" table now gets a CHAR datatype, which signifies a fixed-length character string with a maximum length of 15 characters. 

4. UPDATE

The UPDATE command, paired with a WHERE clause, lets you modify existing records within a table. It’s possible to update single or multiple rows and columns.

Syntax: UPDATE table_name

SET column1 = value1, column2 = value2,..., columnn = valuen

WHERE [condition];

Example: Priya, an employee of your company, moved to a different location. You now wish to update her address in the “Employees” table.

UPDATE Employees SET Address = 'Pune' WHERE EmployeeID = 206;

This SQL query updates Priya's address as Pune, with “206” employee ID as the condition. The inclusion of the WHERE clause is crucial. Omitting this clause from the query would update all rows in the “Employees” table. 

5. Select

The SELECT statement is your tool for pulling or obtaining data from a database. The retrieved data is stored in a result table, also known as the result set.

Syntax: 

1. To retrieve any column from a database: SELECT column1, column2 FROM table_name;

Example 1: A few employees in your organisation have identical first names. To avoid confusion, you retrieve the first and last name attributes from your "Employees" table.

SELECT FirstName, LastName FROM Employees;

2. To retrieve the complete table or all fields within the table: SELECT * FROM table_name; 

The “*” or asterisks implies all attributes of a table. 

Example 2: To gain an overview of sales efforts, you opt to retrieve all fields or attributes from your sales table. 

SELECT * FROM Sales;

6. Join 

The SQL JOIN command helps you merge data or rows from two or more tables based on a shared field. While several types of joins exist, INNER JOIN is the basic one. It's worth noting that JOIN is equivalent to INNER JOIN. 

Syntax: 

For INNER JOIN: SELECT 

table1.column1, table1.column2, table2.columnn 

FROM table1 

INNER JOIN table2 

ON table1.matching_column = table2.matching_column;

Here, the matching column denotes the common columns to table 1 and table 2. 

Example: Consider two tables, "Orders" and "Customers." To retrieve the order details along with the corresponding customer names, you could use an INNER JOIN.

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate

FROM Orders

INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

This query will match the CustomerID in both tables and return the order details and the corresponding customers' names. 

7. COMMIT

The COMMIT command ensures that all transactions made since the last COMMIT or ROLLBACK (used for undoing changes) are permanently saved to the database.

Syntax: COMMIT;

Example: If you wish for any deletions made to the table to become permanent, you can employ the commit command.

DELETE FROM Employees WHERE EmployeeID = 101;

COMMIT;

This query deletes a record of an employee with EmployeeID 101 from the "Employees" table. Subsequently, the COMMIT command ensures that the changes made by the DELETE statement are saved. 

8. REVOKE 

The REVOKE command withdraws any previously granted user privileges on database objects. Particularly, if a privilege is revoked from a specific user, any privileges granted to other users by that user become null.

Syntax: REVOKE privileges ON object FROM user;

Here, “object” could be any database object, including a table. 

Example: As Rohit is a former employee of your firm, you intend to revoke his SELECT SQL privileges.

REVOKE SELECT ON Employees FROM Rohit;

This query will revoke the SELECT privilege on the "Employees" table from the user "Rohit."

Benefits of using SQL

Regardless of the type of industry, here are a few notable advantages of using standard query language:

• Easy to use: SQL commands constitute common English phrases, so they are relatively easy to understand. 

• Compatible with other programming languages: SQL integrates seamlessly with other programming languages, including Python and R, facilitating efficient interoperability.

Challenges in using SQL

Every tool has its limitations. A couple of downsides to using the standard query language are as follows: 

• Doesn’t support real-time analytics: SQL's design caters to batch processing, rendering it unsuitable for real-time data analytics. 

• Difficult to configure: Setting up SQL databases can be challenging, often requiring skilled database administrators to configure them effectively.

Learn SQL with Coursera 

SQL is an intuitive programming language for interacting with and manipulating database data. A career in data science or engineering requires a solid understanding of databases and SQL. Develop these necessary skills with IBM’s SQL: A Practical Introduction for Querying Databases course on Coursera. This course is designed for beginners and includes hands-on labs to help you construct and execute SQL queries. You might need approximately 21 hours to finish this course.