Relational databases can be managed and manipulated using the programming language SQL (Structured Query Language). Here are some crucial SQL command categories to be aware of for data analysis:
Data Definition Language (DDL)
what are the DDL commands?
DDL modifies the structure of the table by adding, removing, or changing tables, among other things.
All DDL commands are automatically committed, which permanently saves all database modifications.
The following commands are included in DDL:
Create
This command builds a new table and has a predefined syntax. The CREATE statement syntax is:
CREATE TABLE [table name] ([column definitions]) [table parameters];
For example:
CREATE TABLE Employee (Employee Id INTEGER PRIMARY KEY, First name CHAR (50) NULL, Last name CHAR (75) NOT NULL);
Alter
An alter command modifies an existing database table. This command can add up additional column, drop existing columns and even change the data type of columns involved in a database table.
An alter command syntax is:
ALTER object type object name parameters;
For example:
ALTER TABLE Employee ADD PRIMARY KEY (employee_pk);
In this example, we added a unique primary key to the table to add a constraint and enforce a unique value. The constraint “employee_pk” is a primary key and is on the Employee table.
Drop
A drop command is used to delete objects such as a table, index or view. A DROP statement cannot be rolled back, so once an object is destroyed, there’s no way to recover it.
Drop statement syntax is:
DROP object type object name;
For example:
DROP TABLE Employee;
In this example, we’re deleting the Employee table.
Truncate
Similar to DROP, the TRUNCATE statement is used to quickly remove all records from a table. However, unlike DROP that completely destroys a table, TRUNCATE preserves its full structure to be reused later.
Truncate statement syntax is:
TRUNCATE TABLE table_name;
For example:
TRUNCATE TABLE Employee;
DQL Command
Data Query Language
DQL is used to fetch the data from the database.
A group of SQL commands known as Data Query Language (DQL) are used to extract data from databases. The DQL command that is used the most is SELECT. Here are a few DQL command examples:
It uses only one command:
SELECT
a. SELECT: This is the same as the projection operation of relational algebra. It is used to select the attribute based on the condition described by WHERE clause.
Syntax:
SELECT expressions
FROM TABLES
WHERE conditions;
SELECT
- SELECT * FROM customers;
Using this operation, the “customers” table’s whole data are fetched. - SELECT first_name, last_name, email FROM customers; The “first_name”, “last_name”, and “email” fields from the “customers” table are obtained by this command.
- SELECT DISTINCT city FROM customers;
Using this command, the “customers” table’s “city” column’s distinct values are obtained. - SELECT * FROM orders WHERE order_date > ‘2022-01-01’;
With this operation, all information from the “orders” table that has a “order_date” greater than January 1st, 2022 is retrieved. - SELECT products.product_name, orders.order_date, order_items.quantity
FROM products
INNER JOIN order_items ON products.product_id = order_items.product_id
INNER JOIN orders ON order_items.order_id = orders.order_id;
This command connects the “product_id” and “order_id” columns with the “product_name,” “order_date,” and “quantity” columns from the “products,” “order_items,” and “orders” tables.
DML COMMAND
The database can be changed by using DML commands. It is in charge of making any kind of database modifications.
DML commands cannot permanently save all database changes since they are not auto-committed. They are rollbackable.
The four primary DML commands in SQL are as follows:
- SELECT Command
- INSERT Command
- UPDATE Command
- DELETE Command
SELECT DML Command
In Structured Query Language, the most significant data manipulation command is SELECT. The records from the chosen table are shown by the SELECT command. Additionally, by utilising the WHERE clause, it displays a specific record from a certain column.
Examples of SELECT Command
Example 1: This example shows all the values of every column from the table.
SELECT * FROM Student;
This SQL statement displays the following values of the student table:
Example 2: The values of every column in the table are displayed in this example.
SELECT Emp_Id, Emp_Salary FROM Employee;
INSERT DML Command
Another crucial data manipulation operation in SQL is Put, which enables users to insert data into database tables.
Example 1: This illustration demonstrates how to add a record to a database table.
INSERT INTO Student (Stu_id, Stu_Name, Stu_Marks, Stu_Age) VALUES (106, mahesh, 89, 19);
Data Control Language (DCL)
To control access to database objects, use these instructions. GRANT and REVOKE are a couple of examples of commands in this group.
A person or group of users can be granted special rights with this command. The GRANT command has the following syntax:
GRANT
user_name privilege_type ON object_name;
“Object_name” refers to the name of the database object (such as a table or view) on which the permission is being granted, “user_name” refers to the name of the user or group of users to whom the permission is being granted, and “privilege_type” refers to the specific type of permission that is being granted (such as SELECT, INSERT, UPDATE, or DELETE).
REVOKE:
This command is used to remove a user’s or a group of users’ access to a particular set of privileges. The REVOKE command has the following syntax:
REMOVE privilege_type FROM user_name on item named;
The terms “privilege_type”, “object_name”, and “user_name” in this syntax have the same definitions as in the GRANT command. The REVOKE command strips the user or group of users of the given rights.
Transaction Control Language (TCL)
The management of transactions, which are collections of database activities that are handled as a single unit of work, is done using these commands. The commands COMMIT and ROLLBACK are examples of this class.
Commit
The COMMIT command is used to save transaction changes to the database. As an example, let’s say you’ve begun a transaction to update the “orders” database. The COMMIT command can be used to save the appropriate adjustments to the database after you’ve finished.
ROLLBACK
The ROLLBACK command is used to undo transaction changes and return the database to its original state. Use the ROLLBACK command to return to the database’s before state, for example, if you introduced a transaction in order to update the “orders” table but something went wrong and you want to undo the changes.
SAVEPOINT
The SAVEPOINT command establishes a specific location within a transaction to which you may later roll back. Consider updating multiple tables in a single transaction, for example. A chosen point within the transaction that you can roll back in the event something goes wrong can be created using the SAVEPOINT command.
ROLLBACK TO SAVEPOINT:
With the ROLLBACK TO SAVEPOINT command, you can roll back a transaction’s changes up to a specified SAVEPOINT and return the database to its previous state. Use the ROLLBACK TO SAVEPOINT command to return to the database’s prior state at that SAVEPOINT, for instance, if you created a SAVEPOINT in a transaction but something went wrong and you want to undo the modifications made up to that point.