SQL is the Structured Query Language used to store, manipulate, and retrieve data present in a database server.
MySQL is a relational database management system. It supports large databases and is customizable. This article will discuss the important commands in SQL.
SQL Commands
SQL commands are instructions that are used to communicate with the database. It is used to perform specific tasks, work, and functions with data in the database.
Data Definition Language
Data Definition Language helps you to define the database structure or schemas. DDL commands are capable of creating, deleting, and modifying data.
Here are some commands that come under DDL:
- CREATE
- ALTER
- DROP
- TRUNCATE
CREATE
It is used to create a new table in the database.
Syntax:
CREATE TABLE TableName (
Column1 datatype,
Column2 datatype,
Column3 datatype,
ColumnN datatype
);
Example:
CREATE TABLE Employees {
Emp_Id int(3),
Emp_Name varchar(20)
};
This will create a table Employees with Emp_ID and Emp_Name.
ALTER
Alter is used to alter the structure of the database.
Syntax:
ALTER TABLE TableName
ADD ColumnName Datatype;
ALTER TABLE TableName
DROP COLUMN ColumnName;
Example:
ALTER TABLE Employees
ADD BloodGroup varchar(255);
This will add a column BloodGroup to the existing table Employees.
ALTER TABLE Employees
DROP BloodGroup varchar(255);
This will drop the column BloodGroup from the existing table.
DROP
It is used to delete both the structure and record in the table.
Syntax:
DROP TABLE TableName;
Example:
DROP TABLE Employees;
This SQL command will remove the table structure along with its data from the database.
TRUNCATE
This truncate command is used to delete all the rows from the table and free the space.
Syntax:
TRUNCATE TABLE TableName;
Example:
TRUNCATE TABLE Employees;
Data Manipulation Language
Data Manipulation Language (DML) is the language that gives users the ability to access, or manipulate the condition that the appropriate data model has inherited.
Here are some SQL commands that come under DML:
- INSERT
- UPDATE
- DELETE
INSERT
INSERT command is used to insert new rows or records in a table.
Syntax:
INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)
VALUES (value1, value2, value3,...valueN);
Example:
INSERT INTO Employees(Emp_Id, Emp_Name) VALUES(04, “Sam Tully”);
UPDATE
This command is used to update or modify the value of a column in the table.
Syntax:
UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];
Example:
UPDATE Employees
SET Salary = 1000
WHERE Emp_Id = 04;
The above code will modify the salary of the employee with Emp_ID= 04;
DELETE
DELETE is used for removing one or more rows from the table.
Syntax:
DELETE FROM TableName
WHERE Condition;
Example:
DELETE FROM Employees
WHERE Emp_Id = 04;
This will delete the record of the Employees whose Emp_ID is 4.
Data Control Language
Data Control Language is used to manage roles, permissions, and referential integrity on the database.
Here are some commands that come under DCL:
- GRANT
- REVOKE
GRANT
GRANT command is used to give access or permission to specific users.
Syntax:
GRANT object_privileges ON table_name TO user_name1;
Example:
GRANT SELECT, UPDATE ON Student TO John Doe;
REVOKE
REVOKE is used for taking back permission, which is given to the user.
Syntax:
REVOKE object_privileges ON table_name FROM user1, user2,… userN;
Example:
REVOKE UPDATE ON Student FROM John Doe;
Transaction Control Language
TCL manages the issues and matters related to the transactions in any database. They are used to rollback or commit the changes in the database.
Here are some commands that come under TCL:
- COMMIT
- ROLLBACK
COMMIT
The COMMIT command is used to save all the transactions to the database.
Syntax:
COMMIT;
Example:
DELETE FROM Employees
WHERE AGE = 21;
COMMIT;
ROLLBACK
The rollback command is used to undo transactions that have not already been saved to the database.
Syntax:
ROLLBACK;
Example:
DELETE FROM CUSTOMERS
WHERE AGE = 21;
ROLLBACK;
GROUP BY
The SQL GROUP BY command is used with the SELECT command to group the rows which have the same value.
Syntax:
SELECT column1, column2
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2
ORDER BY column1, column2;
Example:
Consider the following data:
If you want to know the total points of each customer, then the GROUP BY query would be used as follow:
This would produce the following results-
HAVING
The HAVING clause allows you to filter the results based on the groups. The SQL HAVING clause is used with the GROUP BY clause.
Consider the following dataset:
Following is an example, which would display a record for a similar city that would be equal to ‘Hampton’
This would produce the following result:
ORDER BY
The ORDER BY keyword in SQL is used to sort the result in ascending or descending order.
Note: By default, the results are sorted in ascending order.
Syntax:
SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC
Example:
SELECT first_name, last_name
FROM customers
ORDER BY first_name;
Gain expertise in the latest Business analytics tools and techniques with the Post Graduate Program in Business Analysis. Enroll now!
Conclusion
It is crucial to understand the important commands in SQL to understand the basics of how to use and manipulate the dataset. The commands help us in various functions which include writing, updating, modifying, and deleting the data from the tables. Check out our tutorial on replace function in SQL.
Now that you know about the SQL commands, it’s time for you to start querying and manipulating this updated and existing data in different datasets. This is an essential step to moving forward in your journey to becoming an SQL expert. If you liked this article, and want to get certified, check out Simplilearn’s Business Analyst Master’s Program. This comprehensive program covers SQL in-depth, and earning your certification in this field can help to jumpstart your career.
Do you have any questions for us? Please leave them in the comments section, and we’ll have our experts in the field answer them for you!