January 31, 2020
Amitraj
SQL Commands
-> SQL commands are instructions. It is used to communicate with the database. It is also used to perform specific tasks, functions, and queries of data.
-> SQL defines following ways to manipulate data stored in an RDBMS.
Types Of SQL Command
Five types of SQL command show in fig -
1. Data Definition Language (DDL) -
-> This includes changes to the structure of the table like creation of table, altering table, deleting a table etc.
-> All DDL commands are auto-committed. That means it saves all the changes permanently in the database.
1. create - to create new table or database.
2. truncate - to delete data from table.
3. rename - to rename a table.
4. drop - to drop a table.
5. Alter - for Alteration. This change could be either to modify the characteristics of an existing attribute or probably to add a new attribute.
2. Data Manipulation Language (DML) -
-> DML commands are used to modify the database. It is responsible for all form of changes in the database.
-> The command of DML is not auto-committed that means it can't permanently save all the changes in the database. They can be rollback.
1. insert - to insert a new row
2. update - to update existing row
3. delete - to delete a row
4. merge - merging two rows or two tables
3. Data control language (DCL) -
Data control language are the commands to grant and take back authority from any database user.
Grant: It is used to give user access privileges to a database.
Example:
GRANT SELECT, UPDATE ON MY_TABLE TO SOME_USER, ANOTHER_USER;
Revoke: It is used to take back permissions from the user.
Example:
REVOKE SELECT, UPDATE ON MY_TABLE FROM USER1, USER2;
4. Transaction Control Language (TCL) -
TCL commands can only use with DML commands like INSERT, DELETE and UPDATE only.
These operations are automatically committed in the database that's why they cannot be used while creating tables or dropping them.
1. Commit: Commit command is used to save all the transactions to the database.
Syntax:
COMMIT;
Example:
DELETE FROM CUSTOMERS
WHERE AGE = 30;
COMMIT;
2. Rollback: Rollback command is used to undo transactions that have not already been saved to the database.
Syntax:
ROLLBACK;
Example:
DELETE FROM CUSTOMERS
WHERE AGE = 25;
ROLLBACK;
3. SAVEPOINT: It is used to roll the transaction back to a certain point without rolling back the entire transaction.
Syntax:
SAVEPOINT SAVEPOINT_NAME;
5. Data Query Language (DQL) -
DQL is used to fetch the data from the database.
-> It uses only one command:
SELECT
select - retrieve records from one or more table.
Syntax:
SELECT expressions
FROM TABLES
WHERE conditions;
In this below Pdf file I will cover most important 41 DBMS Queries (SQL commands) that are very useful for students. so click on the pdf icon and read the query and its syntax: click below
-> SQL commands are instructions. It is used to communicate with the database. It is also used to perform specific tasks, functions, and queries of data.
-> SQL defines following ways to manipulate data stored in an RDBMS.
Types Of SQL Command
Five types of SQL command show in fig -
1. Data Definition Language (DDL) -
-> This includes changes to the structure of the table like creation of table, altering table, deleting a table etc.
-> All DDL commands are auto-committed. That means it saves all the changes permanently in the database.
1. create - to create new table or database.
2. truncate - to delete data from table.
3. rename - to rename a table.
4. drop - to drop a table.
5. Alter - for Alteration. This change could be either to modify the characteristics of an existing attribute or probably to add a new attribute.
2. Data Manipulation Language (DML) -
-> DML commands are used to modify the database. It is responsible for all form of changes in the database.
-> The command of DML is not auto-committed that means it can't permanently save all the changes in the database. They can be rollback.
1. insert - to insert a new row
2. update - to update existing row
3. delete - to delete a row
4. merge - merging two rows or two tables
3. Data control language (DCL) -
Data control language are the commands to grant and take back authority from any database user.
Grant: It is used to give user access privileges to a database.
Example:
GRANT SELECT, UPDATE ON MY_TABLE TO SOME_USER, ANOTHER_USER;
Revoke: It is used to take back permissions from the user.
Example:
REVOKE SELECT, UPDATE ON MY_TABLE FROM USER1, USER2;
4. Transaction Control Language (TCL) -
TCL commands can only use with DML commands like INSERT, DELETE and UPDATE only.
These operations are automatically committed in the database that's why they cannot be used while creating tables or dropping them.
1. Commit: Commit command is used to save all the transactions to the database.
Syntax:
COMMIT;
Example:
DELETE FROM CUSTOMERS
WHERE AGE = 30;
COMMIT;
2. Rollback: Rollback command is used to undo transactions that have not already been saved to the database.
Syntax:
ROLLBACK;
Example:
DELETE FROM CUSTOMERS
WHERE AGE = 25;
ROLLBACK;
3. SAVEPOINT: It is used to roll the transaction back to a certain point without rolling back the entire transaction.
Syntax:
SAVEPOINT SAVEPOINT_NAME;
5. Data Query Language (DQL) -
DQL is used to fetch the data from the database.
-> It uses only one command:
SELECT
select - retrieve records from one or more table.
Syntax:
SELECT expressions
FROM TABLES
WHERE conditions;
In this below Pdf file I will cover most important 41 DBMS Queries (SQL commands) that are very useful for students. so click on the pdf icon and read the query and its syntax: click below
 |
| DBMS SQL QUERY |
