What is SQL ?

by
  • SQL stands for Structured Query Language.
  • SQL language is designed for maintaining the data in relational database management systems(RDBMS).
  • SQL is a standard language for accessing and manipulating database.
  • In SQL data is stored in table format i.e. Row and Column

Types of SQL Commands

What is SQL ?

DDL Commands

  • DDL stands Data Definition Language it consists of the SQL commands that can be used to define the database schema. It simply deals with descriptions of the database schema and is used to create and modify the structure of database objects in the database. 
  • It also used to define datatype of column and validation

1. CREATE

This command is used to create a new database or table.

Syntax to create database: 
CREATE Database Database_Name; 

Syntax:
CREATE TABLE table_name(
column1 datatype,
column2 datatype,
column3 datatype,
column4 datatype
);


Example:
CREATE TABLE Student
(
RollNO int;
FirstName varchar(255),
LastName varchar(255),
AddressLine varchar(255),
);
RollNOFirstNameLastNameAddress
Student table

2. ALTER Command

ALTER command is used to change or modify the existing structure of the database, and it also changes the schema of database objects.

Suppose, you want to add the ‘Father’s_Name’ column in the existing Student table. 

ALTER TABLE name_of_table ADD column_name column_definition; 

ALTER TABLE Student ADD Father's_Name Varchar(60);

3. DROP

DROP command is used to delete or remove the database or objects from the SQL database. You can easily remove the entire table, view, or index from the database using this DDL command.

Syntax: 
DROP DATABASE Database_Name;



DROP TABLE Table_Name; 

Example:  DROP TABLE Student;  

DELETE Customers WHERE CuntomerName = "aasifcodes

4. TRUNCATE

A truncate SQL statement is used to remove all rows (complete data) from a table. It is similar to the DELETE statement with no WHERE clause.

Suppose, we want to delete the record of the Student table.

Syntax:

TRUNCATE TABLE table_name;

Example: TRUNCATE TABLE Employee;

DML Commands

  • DML stands for Data Manipulation Language.
  • DML commands manipulate data which is present in the database.
  • By using DML commands you can easily easily access, store, modify, update and delete the existing records of the database

1. SELECT 

The SELECT command shows the records of the specified table. It also shows the particular record of a particular column by using the WHERE clause.

Syntax of SELECT DML command
SELECT * FROM table_name; 

example: SELECT * FROM Student;

Roll_NoStudent_NameStudent_Marks
12Rahul86
23Seema60
56Divya90
Student table
If you want to access all the records of those students whose marks is 80 from the above table, then you have to write the following DML command in SQL:

SELECT * FROM Student WHERE Student_Marks = 85;  
Roll_NoStudent_NameStudent_Marks
12Rahul86
56Divya90

2. INSERT 

INSERT is a most important DML command in SQL. INSERT command is used to insert data(entries/data items) into table of database.

Syntax of INSERT Command
INSERT INTO TABLE_NAME ( column_Name1 , column_Name2 , …. column_NameN )  VALUES (value_1, value_2, …. value_N ) ; 
Examples: 

INSERT INTO Student (Stu_id, Stu_Name, Stu_Marks,) VALUES (1044, Shiva, 89);

3. UPDATE 

UPDATE is a DML command in SQL which allows users to update or modify the existing data in database tables

Syntax of UPDATE Command

UPDATE Table_name SET [column_name1= value_1, ….., column_nameN = value_N] WHERE CONDITION;
Roll_NoStudent_NameStudent_Marks
12Rahul86
23Seema60
56Divya90

Suppose, you want to update the Student_Marks of the student whose Roll_No is 23. To do this, you have to write the following DML UPDATE command:

UPDATE Student SET Student_Marks = 95 WHERE Roll_No = ’23’ ; 

4. DELETE 

DELETE is a DML command which is used to remove single or multiple existing records from the database tables.

This command of Data Manipulation Language does not delete the stored data permanently from the database. We use the WHERE clause with the DELETE command to select specific rows from the table.

Syntax of DELETE Command
DELETE FROM Table_Name WHERE condition;  

 Example 1 : how to delete a single record from the table.

Roll_NoStudent_NameStudent_Marks
12Rahul86
53Seema60
56Divya90

DELETE FROM Student WHERE Roll_No = ’53’ ;  

 Example 2 :  how to delete the multiple records or rows from the database table.

DELETE FROM Student WHERE Student_Marks > 60 ; 
DML Commands in sql

DCL Commands

  • DCL stands for Data Control Language
  • these commands provide the rights, permission and other control of the database system such as GRANT and REVOKE.

1. GRANT

GRANT command is used to give user access privileges to a database.
It is used to give authority to user to access the database. 

Example:  

GRANT SELECT, UPDATE ON MY_TABLE TO SOME_USER, ANOTHER_USER;

2. REVOKE

REVOKE command is used to take back permissions from the user.

Example:

REVOKE SELECT, UPDATE ON MY_TABLE FROM USER1, USER2;

TCL Commands

  • TCL stands for Transaction Control Language
  • Here are some commands that come under TCL:
  • COMMIT
  • ROLLBACK
  • SAVEPOINT
  • TCL commands can only use with DML commands like INSERT, DELETE and UPDATE only

1. COMMIT

 This command is used to save all the transactions to the database.

Syntax:
COMMIT;  

Example:

DELETE FROM Student
WHERE Roll_No= 23;  
COMMIT;

2. ROLLBACK

This command is used to undo transactions that have not already been saved to the database

Syntax:
ROLLBACK;  

Example: 

DELETE FROM Student
WHERE Roll_No= 23;  
ROLLBACK;

3. SAVEPOINT

SAVEPOINT command in TCL is used to roll the transaction back to a certain point without rolling back the entire transaction.

Syntax: 
SAVEPOINT SAVEPOINT_NAME;

Leave a comment