In this article we will learn about database query, SQL commands and its types.
Database Query:
Database Query is a request for data from a database. Usually, the request is to retrieve or manipulate data in a database. In simple words query is a question. It is similar to performing some sort of CRUD (Create, Read, Update, Delete) operations. A number of database query languages are available and one such query language is SQL. It is designed for relational database and displays results in the form of rows and columns.
SQL commands:
SQL commands are instructions used to perform operations such as
- Create and drop database and tables
- Retrieve information from tables and database
- Create a view, stored procedure and functions in a database
- Set permissions for users
- Add data to database
- Modify database
Types of SQL commands:
SQL commands are differentiated based on their functionality as
- DDL (Data Definition Language)
- DML (Data Manipulation Language
- DCL (Data Control Language)
- TCL (Transaction Control Language)
- DQL (Data Query Language)
DDL :
Data Definition Language consists of commands that can be used to define the database schema and deals with its description. It changes the structure of the table like creating, deleting or altering etc.,
All DDL commands are auto-commited which means changes are permanently saved in the database.
Create
It is used to create a database and its objects like tables, views, procedures.
Syntax
CREATE DATABASE database_name;
CREATE TABLE table_name( column name datatypes[,…]);
CREATE PROCEDURE procedure_name
AS
sql statement logic
GO;
Example
CREATE TABLE Employee(Name varchar(20), Age int(3),Id int(3));
Drop
The Drop is used to delete the existing database objects in a database.
Syntax
DROP TABLE table_name;
DROP DATABASE database_name;
DROP PROCEDURE procedure_name;
Example
DROP TABLE Employee;
Alter
This is used to change the structure of the database. It modifies the existing records in a database.
Syntax
ALTER TABLE table_name ADD column_name column_definition;
Example
ALTER TABLE Employee ADD City varchar(50);
Truncate
Truncate is used to delete all data from the table except for the database schema(structure).
Syntax
TRUNCATE TABLE table_name;
Example
TRUNCATE TABLE Employee;
Rename
Rename is used to rename table existing in the database. SQL server does not have any statement that directly renames a table but it has a stored procedure sp_rename which allows to change the name of the table.
Syntax
EXEC sp_rename ‘old_table_name’, ‘new_table_name’;
Example
EXEC sp_rename ‘Employee’, ‘Employee_details’;
Comment
It is used to add comments to the data dictionary.
There are two types of comments in SQL
- Single-line comments starts with double hypens (- -).
- Multi-Line comments starts with /* and ends with */.
DML
DML stands for Data Manipulation Language. These commands are used to modify the database. It is responsible for all form of changes in the database. DML commands include insert, update, delete, merge, call, explain plan, lock table.
If we enter wrong information in a table, we can easily modify the table by changing and roll backing it. There are two types of DML
- Procedural: The user specifies what data is needed and how to get that data. E.g.: Relational Algebra.
- Non-Procedural (Declarative): In this method only what data is needed is specified. It is easier for user but this is not as efficient as Procedural languages. E.g.: Tuple Relational Calculus.
Insert:
Insert command is used to insert new records or new rows in a database table.
Syntax:
INSERT INTO table_name(colum1, column2,…) VALUES(value1,value2,…)
Example: INSERT INTO Employee (Id, Name) VALUES ( 1001, “John” );
Update:
Update is used to update or modify existing data in the table. Using Update we can modify single column or multiple columns at the same time.
Syntax:
UPDATE table_name SET col1 = value1, col2=value2,…colN=valueN WHERE condition;
Example:
UPDATE Employee SET salary = 10000 WHERE Id = 1001; (Modify Single Column)
UPDATE Employee SET salary = 10000, Name = “John” WHERE Id=1001; (Modify Multiple Columns)
WHERE clause in UPDATE command is optional. If we omit WHERE clause then salary = 10000 will be updated for all employees.
Delete:
Delete is used to delete records or entire table. It can be used with WHERE clause.
Syntax:
DELETE FROM table_name WHERE condition;
Example:
DELETE FROM Employee WHERE Id = 1001; (single record deleted)
DELETE FROM Employee WHERE salary=15000; (multiple record deleted)
DELETE FROM Employee; ( all records of the table deleted)
Merge:
Merge is used to UPSERT (Update and Insert) operations. It merges the two rows of existing tables in a database.
Call:
It is used to call PL/SQL or Java Subprogram.
Lock Table:
It is used for concurrency control. It locks the privileges as either read or write.
Example:
LOCK TABLE Employee read.
Explain Plan:
It is used for the interpretation of data access path.
DQL:
It is also known as Data Query Language. Purpose is to get some schema relation based on the query passed to it. It has only one command.
SELECT:
SELECT command is used in combination with other SQL clauses to fetch data from database or table based on certain conditions.
Syntax:
SELECT *FROM table_name;
SELECT col1,col2…colN FROM table_name;
Example:
SELECT *FROM Employee;
SELECT Id, Name FROM Employee;
DCL:
This is Data Control Language (DCL). It allows users to retrieve and edit data held in database. It also allows us control access within the database. It controls the distribution of privileges among various users of the database. Privileges are of two types:
- System: This includes permission of creating sessions, tables etc., and all other system privileges.
- Object: This includes permission for access to any command or query to perform any action on the database.
Command types are: GRANT and REVOKE.
GRANT:
User access privileges are given by this command.
System Privileges:
Syntax:
GRANT privilege_name TO user_name [WITH ADMIN OPTIONS];
Example:
GRANT CREATE TABLE TO John;
Allows user John to create table.
GRANT CREATE TABLE TO John WITH ADMIN OPTIONS;
Allows user John to create table and assign the same privileges to other users.
Object Privileges:
Syntax:
GRANT privilege_name ON object_name To {user_name | Public| role_name} [WITH ADMIN OPTIONS];
Example:
GRANT SELECT ON Employee TO John;
This will grant Select Privilege on object Employee to user John.
GRANT SELECT ON Employee TO John WITH GRANT OPTIONS;
This will grant Select privilege on object Employee to user John and he can also grant this privilege to other users.
REVOKE:
It removes or take back certain privileges given to users.
Syntax:
REVOKE privilege_name ON object_name TO user_name[CASCADE | RESTRICT];
[CASCADE | RESTRICT] clause is optional.
Example:
REVOKE SELECT ON Employee FROM John;
TCL:
TCL stands for Transaction Control Language. This deals with transactions within the database. It is used to handle DML modifications. This saves the state of the transaction.
COMMIT:
This command saves all the transactions in the database.
Syntax:
COMMIT;
Example:
Update Employee SET salary = 15000 WHERE Id=1001;
COMMIT;
ROLLBACK:
Rollback is used to undo transactions that have not already been saved in the database. This command can only be used since the last COMMIT or ROLLBACK is used. It restores the database to original state since the last COMMIT.
Syntax:
ROLLBACK;
Example:
DELETE FROM Employee WHERE Id=1001;
ROLLBACK;
SAVEPOINT:
Savepoint is used to rollback the transaction to a certain point without rolling back the entire transaction.
Syntax:
SAVEPOINT savepoint_name;
Example:
SAVEPOINT s1;
DELETE FROM Employee WHERE Id=1001;
SAVEPOINT s2;
These are the SQL commands and its types that we see in a database.