Identify Common Structured Query Language (SQL) Statements

SQL statements allow users to perform various queries and manipulate data.

SQL statements are instructions for managing and inquiring about data in relational databases. They follow specific rules and syntax for data interaction.

SQL statements are crucial for those looking to work with data, such as conducting queries and making updates, deletions, or additions. They enable data professionals to handle data operations smoothly and proficiently, which is essential in various sectors.

There are three logical categories of SQL statements, as follows:

• Data Control Language (DCL)
• Data Definition Language (DDL)
• Data Manipulation Language (DML)

Figure 2.5 summarizes the common SQL statements for the Skills Measured area of the DP-900 exam:

Figure 2.5 – Common SQL statements

In the following sections, you will learn more about these SQL statement categories and some examples to help you understand them.

DCL Statements

Database access is managed with DCL statements. These represent who can access, update, or delete objects in a database.

DCL statements include GRANT, REVOKE, and DENY.

GRANT

This statement permits specific user or role actions on database objects.

This could be permissions such as SELECT, INSERT, UPDATE, DELETE, and EXECUTE on database objects such as tables, views, and stored procedures. The GRANT statement allows for better control over allowing users access/security to database objects, thereby granting only the specified access type on the defined database objects.

The following is an example of a GRANT statement:

GRANT SELECT ON Product TO example_user;

DENY

This statement is used to explicitly refuse one or more permissions to a user or a role on an object within the database, such as a table, view, or procedure. This statement does not allow any permissions beyond this single DENY statement for the specified object.

Users may have been given access to permissions through a GRANT statement, and the access granted might be equal to or greater than the right being denied. However, a DENY statement applies restrictions on top of any granted access. This statement is useful for setting a security policy to stop certain actions for a given user or role, regardless of any other granted permissions.

The following is an example of a DENY statement:

DENY SELECT ON Product TO example_user;

REVOKE

This statement moves previously granted permissions from specific users or roles.

The privileges you granted to the user are revoked, so the user can no longer access the data or feature. The REVOKE statement is critical to database security. It’s the mechanism by which access is controlled; without it, data and features irrelevant to a user would be exposed, and data not intended for them could be changed.

The following is an example of a REVOKE statement:

REVOKE SELECT ON Product FROM example_user;

This will allow database administrators to restrict users’ access to sensitive information when running the statements.

DDL Statements

DDL statements act on the structure of a database.

DDL statements include CREATE, ALTER, and DROP.

CREATE

This statement creates a new database, a new table in an existing database, or database objects such as triggers, views, functions, and so on.

To create a table as an example, you specify the table’s name and define the columns and their data types and constraints. It allows the database structure to be defined, such as the fields used to store the data. It is a core statement in database management.

The following is an example of a CREATE statement:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Department VARCHAR(50),
    Salary DECIMAL(10, 2)
);

ALTER

This statement allows you to change the structure of an existing database object, such as a table, view, or index, on the fly without having to drop and recreate it. This operation includes adding, altering, or deleting a column from a table, changing the data types, renaming objects, or defining constraints. It is often needed to maintain database schemas when changes to requirements occur without losing the existing data.

The following is an example of an ALTER statement:

ALTER TABLE Employees
ADD COLUMN Email VARCHAR(100);

DROP

This statement removes a database object (e.g., a table, an index, a view, or a database) already in the database. Once this statement is executed, the object and all associated data, structures, and dependencies are removed from the system. Being destructive, the statement use of DROP is carefully controlled, typically granting it only to users with higher-level administrative privileges to prevent a user from accidentally deleting data.

The following is an example of a DROP statement:

DROP TABLE Employees;

Using DDL statements, databases can be optimized for performance and scalability.

DML Statements

DML statements are used to access data stored in a database.

DML statements include SELECT, INSERT, UPDATE, and DELETE.

SELECT

This statement queries and returns results from a database. In the SELECT clause, you can return the columns of interest, and in the FROM clause, the table from which to return them.

Results can be filtered using the WHERE clause and ordered using the ORDER BY clause. Results can be grouped using the GROUP BY clause and filtered by groups using the HAVING clause. Joins can be marked using clauses such as JOIN across multiple tables to combine rows. The SELECT statement is at the core of SQL.

The following is an example of a SELECT statement:

SELECT *
FROM Employees
WHERE Department = 'Engineering';
SELECT ProductID, Name, Price
FROM Product
ORDER BY Price ASC;

INSERT

This statement is used to insert new data rows into a database table. It specifies the table where the data should be inserted and the values entered into each table column. The syntax is for the INSERT INTO clause, the name of the table we are inserting data into, an optional list of columns, and the VALUES keyword, followed by a list of values corresponding to the columns we specified. The INSERT statement can be used with the SELECT statement to insert data from another table.

The following is an example of an INSERT statement:

INSERT INTO Employees (EmployeeID, Title, LastName, Department)
VALUES (1, 'Mr', 'blogs', 'IT')
       (2, 'Mrs', 'Miggins', 'RETAIL')

UPDATE

This statement is used to update one or more columns for rows in a table that is a part of a database.

This means it can be used to amend your database content so it matches the records you consider the most correct. With this statement, it is possible to edit one or more columns for all the rows of a table that match a certain condition. The syntax generally contains the table name, the SET clause that includes the columns to be changed and their updated values, and the WHERE condition.

This statement is important for maintaining data integrity and accuracy.

The following is an example of an UPDATE statement:

UPDATE Employees
SET Department = 'Operations'
WHERE EmployeeID = 1;

DELETE

This statement removes one or more rows from a table by evaluating certain conditions.

It has a simple syntax where the table name from which records need to be removed should be specified first. Optionally, a WHERE statement can specify the conditions determining which rows need to be deleted. DELETE statements should be used with caution as they delete data permanently, and without a WHERE clause, they can permanently lose all data.

The following is an example of a DELETE statement:

DELETE FROM Employees
WHERE EmployeeID = 1;

Users can retrieve and modify the data they need from a database using DML statements.

Now that you have progressed your relational database knowledge with some common SQL statement skills, you will learn how to identify some common database objects.