SQL Introduction - Type of SQL Statements, SQL Process Flow
What is SQL?
SQL stands for Structured Query Language use for storing, manipulating and retrieving relational database data.
SQL queries to retrieve data from database same as you can adding and manipulating database data.
SQL is a very powerful and diverse database language use to storing data into databases. SQL is loosely typed language so you can learn easily.
In this SQL tutorial, we use command line examples to know about executing speed of SQL. It's take very bit of time for executing and retrieving result.
SQL is a greater tool with web languages such as PHP, Python, Java, ASP et cetera to build dynamic web applications.
Before starting SQL, relational databases have several point that are important to keep in mind.
- RDBMS stands for Relational Database Management System.
- Data Integrity : Store data only once and avoiding data duplication.
- SQL Constraints : Constraints are the rules which are apply to table columns to store valid data and prevents the user to storing/entering invalid data into table columns.
- Better security : Assign grant or privilege to a individual User. Using this grant user can store confidential data into table by using username or password.
- Database Normalization : Database normalization is the process to store database data very efficiently. No need to store same data more then one time and reduce the Data redundancy.
- Different types of relationships : One to one, One to many, Many to many
- One to one relationship : merging for two tables.
- One to many relationship : create a foreign key from an parent table to the child table.
- Many to many relationship : create a new relation table.
Keep in your mind...
- SQL is not case sensitive.
- But SQL Data is a case sensitive.
SQL Process Flow
When you execute SQL query come to a SQL Server. SQL Server work is managing database, load balancing, transaction management etc. SQL server call to a actual physical database table and return the result. This process is take small bit of time and depend of query and SQL server load.
Rules of Semicolon(;) or Forward Slash(/) in SQL
There is a huge difference in SQL between the meaning of a /
and a ;
because they work differently.
- SQL database systems require semicolon(;) at the end of statement to know it's ending.
- Use forward slash(/) once at the end of each script, to tell SQL that there is not more lines of code. you can't use forward slash(/) at the middle of the script.
The ;
means terminate the current statement execute it and store it to the "SQL buffer", whereas the /
executes whatever statement script is in the current "SQL buffer".
Type of SQL Statement (DDL, DML, DCL, TCS, SCS Commands)
SQL statements are divided into five different categories: Data definition language (DDL), Data manipulation language (DML), Data Control Language (DCL), Transaction Control Statement (TCS), Session Control Statements (SCS).
Data Definition Language (DDL) Statements
Data definition statement are use to define the database structure or table.
Statement | Description |
---|---|
CREATE | Create new database/table. |
ALTER | Modifies the structure of database/table. |
DROP | Deletes a database/table. |
TRUNCATE | Remove all table records including allocated table spaces. |
RENAME | Rename the database/table. |
Data Manipulation Language (DML) Statements
Data manipulation statement are use for managing data within table object.
Statement | Description |
---|---|
SELECT | Retrieve data from the table. |
INSERT | Insert data into a table. |
UPDATE | Updates existing data with new data within a table. |
DELETE | Deletes the records rows from the table. |
MERGE | MERGE (also called UPSERT) statements to INSERT new records or UPDATE existing records depending on condition matches or not. |
LOCK TABLE | LOCK TABLE statement to lock one or more tables in a specified mode. Table access denied to a other users for the duration of your table operation. |
CALL EXPLAIN PLAN |
Statements are supported in PL/SQL only for executed dynamically. CALL a PL/SQL program or EXPLAIN PATH access the data path. |
Data Control Language (DCL) Statements
Data control statement are use to give privileges to access limited data.
Statement | Description |
---|---|
GRANT | Gives privileges to user for accessing database data. |
REVOKE | Take back for given privileges. |
ANALYZE | ANALYZE statement to collect statistics information about index, cluster, table. |
AUDIT | To track the occurrence of a specific SQL statement or all SQL statements during the user sessions. |
COMMENT | Write comment to the data table. |
Transaction Control Statement (TCS)
Transaction control statement are use to apply the changes permanently save into database.
Statement | Description |
---|---|
COMMIT | Permanent work save into database. |
ROLLBACK | Restore database to original form since the last COMMIT. |
SAVEPOINT | Create SAVEPOINT for later use ROLLBACK the new changes. |
SET TRANSACTION | SET TRANSACTION command set the transaction properties such as read-write/read only access. |
PL/SQL Transaction commit, rollback, savepoint, autocommit, Set Transaction read more.
Session Control Statements (SCS)
Session control statement are manage properties dynamically of a user session.
Statement | Description |
---|---|
ALTER SESSION | ALTER SESSION statement to modify conditions or parameters that are affect to your database connection. |
SET ROLE | SET ROLE statement to enable or disable the roles that are currently enabled for the session. |