SQL is a structured query language used to store, manipulate and retrieve data present on a database server.
MySQL is a relational database management system. It supports large databases and is customizable. This article will cover basic statements in SQL.
SQL commands
• SQL statements are instructions. Accustomed to communicating with the database. It is also used to execute specific tasks, functions, and data queries.
• SQL can perform various tasks such as creating a table, adding data to tables, deleting a table, modifying a table, and setting permissions for a user.
These SQL statements fall mainly into four categories:
• DDL – Data Definition Language
• DQl – Data Query Language
• DML – Data Manipulation Language
• DCL – Data Control Language
What is DDL?
Data Definition Language helps you define a database structure or schema. Let's learn about DDL statements with syntax.
The five types of DDL statements in SQL are:
CREATE
CREATE statements are used to define the database structure schema:
Syntax:
CREATE TABLE TABLE_NAME (COLUMN_NAME DATATYPES[,….]);
For example:
Create a database university;
Create a table of students;
Create view for_students;
DECREASE
Drops commands remove tables and databases from an RDBMS.
Syntax
DOP TABLE ;
For example:
Drop object_type object_name;
Drop database university;
Folding table student;
ALTER
The Alters command enables you to change the database structure.
Syntax:
Adding a new column to a table
ALTER TABLE table_name ADD column_name column definition;
To edit an existing column in a table:
ALTER TABLE MODIFY(COLUMN DEFINITION….);
For example:
Alter table theiotacademy add subject varchar;
SHORTEN:
This command removes all rows from a table and frees up the space containing the table.
Syntax:
TRUNCATE TABLE table_name;
Example:
TRUNCATE table students;
What is a data manipulation language?
Data Manipulation Language (DML) allows you to modify a database instance by inserting, changing, and deleting its data. It is responsible for performing all types of data modifications in the database.
The three basic constructs that allow database programs and users to enter data and information are:
Here are some basic DML statements in SQL:
INSERT
UPDATE
DELETE
INSERT:
This command is an SQL query. This command is utilized to insert data into a table row.
Syntax:
INSERT INTO TABLE_NAME (column1, column2, column3,… column N)
VALUES (value1, value2, value3, …. value);
Or
INSERT INTO TABLE_NAME
VALUES (value1, value2, value3, …. value);
For example:
INSERT INTO students (RollNo, FirstName, LastName) VALUES ('60', 'Tom', Erichsen');
UPDATE:
This command is utilized to update or modify a column value in a table.
Syntax:
UPDATE table_name SET [column_name1 = value1,…column_nameN = valueN] [NOTE WHERE]
For example:
Student UPDATE
SET First Name = 'Jhon', Last Name = 'Wick'
WHERE StudID = 3;
DELETE:
This command is utilized to delete one or more rows from a table.
Syntax:
DELETE FROM table_name [WHERE condition];
For example:
DELETE FROM students
WHERE Name = 'Jhon';
What is DCL?
DCL (Data Control Language) contains commands like GRANT and REVOKE that help grant "rights and permissions." Additional permissions control database system parameters.
Examples of DCL commands:
Commands that fall under DCL:
Grant
Cancel
Grant:
This command is used to grant a user access rights to the database.
Syntax:
GRANT SELECT, UPDATE ON MY_TABLE TO SOME_USER, ANOTHER_USER;
For example:
GRANT SELECT ON Users TO'Tom'@'localhost;
Cancel:
It is helpful to back up permissions from the user.
Syntax:
DELETE privilege_name ON object_name FROM {user_name |PUBLIC |role_name}
For example:
DELETE SELECT, UPDATE STUDENT FROM BCA, MCA;
Transaction Control Language (TCL):
Transaction Control Language (TCL) is utilized in the database to handle transactions. This command is used to process edits to DML statements. TCL allows you to connect your statements into logical transactions.
Below are some TCL commands:
COMMIT Command: Commit is used to save all transactions in the database.
Syntax: COMMIT;
Example: UPDATE Employee SET DOB=’1995-02-17’ WHERE Emp_Name=’Joe’;
COMMIT;
This example inserts dob into a table named =Joe and then COMMITs the changes to the database.
ROLLBACK Statement: If any grouped SQL statement produces an error, all modifications must be rolled back. The term "rollback" directs to the process of rolling back changes. This command can only roll back transactions since the last COMMIT or ROLLBACK command.
Syntax: ROLLBACK;
Example: UPDATE Employee SET DOB=’1995-02-17’ WHERE Emp_Name=’Joe’;
ROLLBACK;
This example would insert dob into a table named =Joe and then ROLLBACK the changes in the database. So this operation does not affect the table.
SAVEPOINT statement: Used to roll back a transaction to a specific point, not the entire transaction.
Syntax: SAVEPOINT SavepointName;
Among all transactions, this command is used exclusively to create a SAVEPOINT.
ROLLBACK is a statement that is used to undo a set of transactions.
Syntax of the rollback to savepoint command:
ROLLBACK TO SavepointName;
Example: SAVEPOINT S1; //save point created
DELETE FROM Employee WHERE Emp_Name = 'Joe'; //deleted
SAVEPOINT S2; //The saved point has been created.
Data Query Language (DQL):
DQL statements are used to query data contained in schema objects. The goal of DQL statements is to return a schema relation depending on the query. DQL can be defined as follows: It is the part of an SQL statement that allows you to retrieve data from a database and put it in order. The SELECT statement is included. This command will enable you to extract data from a database and perform its actions. This is the exact as the relational algebra projection process. When a SELECT statement is achieved on a table or set of tables, the result is assembled into a new temporary table, which is then displayed or received by the program, i.e., the front-end.
The database server looks at the select clause, which is the first and one of the last clauses of the select statement. This is because we need to know all the alternative columns that could be included in the resulting result set before deciding what will be in it.
Syntax: SELECT expressions FROM Tables WHERE conditions;
Example: SELECT Emp_Name FROM Employee WHERE Mobile=9935467903;
The above command selects a record from the 'Employee' table where the mobile number is '9935467903'.
Conclusion
In this blog, we have discussed all the five categories of SQL statements, i.e., DCL, DML, DDL, TCL, and DQL, and their subtypes. We have gone through each command in detail with its syntax and an example to help you write queries. SQL statements allow you to create and manipulate different database objects using various statements. After going through this blog, you can easily play SQL commands and work with their database.
Advanced Certification in Applied Data Science, Machine Learning & IoT By E&ICT Academy, IIT Guwahati
9 months
Certification in Data Science and Machine Learning With Python By The
IoT Academy Noida
180 Hrs
