
Quick Contact
SQL & My SQL Tutorial
- INTRODUCATION
- What is SQL?
- SQL Microsoft
- SQL Management Tools
- SQL Developer
- Docker Commands
- Composite Key in SQL
- SQL Constraints
- Transactions in SQL Server
- SQL Server Data Types
- SQL Update Join
- SQL Operators
- SQL Clauses
- SQL Commands
- SQL Alter Command
- Distinct Keyword in SQL
- SQL Statements
- SQL Index
- SQL Injections
- Wildcards in SQL
- Alter and Truncate Command in SQL
- SQL Null Functions – ISNULL, IFNULL, Combine, & NULLIF
- SQL Sequence
- How to Find Duplicate Records in SQL
- Primary and Foreign Key in SQL with Examples
- COUNT Function
- SUM Function in SQL
- Dynamic SQL
- Database Tuning
- Pseudocolumn in Oracle SQL
- Triggers in SQL
- Embedded SQL in DBMS
SQL Statements
SQL statements update, manipulate and maintain the data in a database, or retrieve data from a database. Some of the common statements that we use are DDL and DML statements.
The basic SELECT, UPDATE and DELETE statements are used extensively by developers in integration and deployment of even industry-level applications.
The CRUD operations which we perform extensively are also done using the SQL statements.
Demo Database
Let us know have a look at our demo database – IBM.
Query:
SELECT * FROM IBM;
Uses of SQL Statements
Some of the uses of SQL statements are as follows:
- Allow us to perform CRUD operations.
- Provides us with the flexibility to maintain and create the database.
- DML statements help us to manipulate the data.
- DDL statements help us to declare the data.
Types of SQL Statements
SQL works by running the commands provided by the user. We can divide the SQL commands into five distinctions based on the functionality they provide:
-
DDL (Data Definition Language)
DDL is used to define the structure or schema of the database and to specify additional properties of the data.
These statements define the implementation of the schema and are mostly hidden from the user, also the data stored in the database is made to follow certain constraints.
Some of the DDL commands are as follows :
-
Create
To create objects in the database
CREATE TABLE tableName( col1datatype, col2datatype, col3datatype…..);
Example:
Let us now see the query to create our IBM database.
CREATE TABLE IBM( emp_idvarchar(5), namevarchar(50), locationvarchar(50), experienceint);
-
Alter in SQL
Alters the structure of the database
Syntax
ALTER TABLE tableName
ADD columnNamedatatype;
Example:
ALTER TABLE IBM
ADD emp_idvarchar(5);
Drop in SQL
Delete objects from the database
Syntax:
DROP TABLE tableName;
Example: Let us now see how to drop the table IBM.
Query:
DROP TABLE IBM;
-
-
Truncate in SQL
Deletes all the data stored in a table
Syntax:
Example: Let us now see how to truncate a table.
-
DML is used for managing data contained in the schema objects. DML’s can be divided into two subtypes:
-
Procedural DML:
In these types of DML’s we need to specify the type of data we need and how to get that data.
-
Declarative or Non-Procedural DML:
Here, we only need to know what data is required, but the specifications on the process to get the data is not required. The database, on its own, figures out how to get the data efficiently.
-
Insert in SQL
Used to insert data in the database
-
Update in SQL
Used to modify the existing data in the database
Syntax:
UPDATE tableName SET col1 = val1, col2 = val2...., WHERE condition; Example: Let us run an update query on our table IBM.
Query:
UPDATE IBM SET emp_id = 'A33' WHERE name='Rajan';
Delete data in SQL
Used to delete all the records contained
Syntax:
DELETE FROM tableName WHERE condition; Example: Let us now try to delete the row corresponding to emp_id ‘A33’.
Query:
DELETE FROM IBM WHERE emp_id = ‘A33’;
-
DQL (Data Query Language)
Some of the DQL statements are as follows:
-
Select in SQL
Used to view the database.
-
TCL (Transaction Control Language)
TCL commands are used to handle transactions in the database. These statements come in handy to manage and track the changes being made by the DML queries on our database.
Some of TCL statements are as follows :
-
Commit in SQL
Used to permanently save any transaction into the database.
Syntax:
Commit;
Example: Let us now save the transaction using commit.
Query:
Commit;
-
Rollback in SQL
Used to restore the database to the last committed state and is also used with the Savepoint command to move over to any saved state in a transaction.
-
Savepoint in SQL
It is used to temporarily store a transaction so that we can move to the point we need whenever desired.
Query:
TRUNCATE TABLE IBM;
Some of DML statements are as follows:
Syntax:
INSERT INTO TABLE_NAME(col1, col2, col3,...) VALUES(val1, val2, val3,...); Example: Let us now try to insert a record in our database.
Query:
INSERT INTO IBM VALUE('I33','Ram','Pune',4); SELECT * FROM IBM;
Syntax:
SELECT * FROM tableName;
Example: Let us view our database – DataFlair.
Query:
SELECT * FROM IBM;
Syntax:
ROLLBACK TO savedTransaction;
Example: Let us now move back to the saved state.
Query:
ROLLBACK TO initialState;
Syntax:
SAVEPOINT pointName;
Example: Let us now save the present state of database transaction.
Query:
SAVEPOINT initialState;
E. DCL (Data Control Language)
DCL commands are similar to the computer programming language in syntax.
They are a component of the Structured Query Language while using DCL commands we need to be cautious as some databases don’t allow rollback command on the DCL command.
One such example is the Oracle database – when we execute DCL on an oracle database it results in an implicit commit and thus rollback is not allowed.
Some of the DCL statements are as follows:
-
Grant in SQL
Used to allow a specific set of users to perform some specified tasks.
Syntax:
GRANT privilege ON privilege_level TO account_name;
Example: Let us now provide the update access to the user.
Query:
GRANT UPDATE ON IBM TO user;
-
Revoke in SQL
Used to cancel all the previously granted or denied permissions.
Syntax:
REVOKE privilegeName ON tableName FROM userName;
Example: Let us now revoke the update privilege from the user.
Query:
REVOKE Update ON IBM FROM user;