×
☰ See All Chapters

Structured Query Language (SQL)

SQL stands for Structured Query Language in DBMS and is used to communicate with database. SQL is a standard proposed by ANSI. With each vendor’s SQL implementation, however, you find that there are enhancements that serve the purpose for each database server. These enhancements, or extensions, are additional commands and options that are simply a bonus to the standard SQL package and available with a specific implementation. So when you are working with any particular database you need to check the database manual.

SQL Sessions

An SQL session is an occurrence of a user interacting with a relational database through the use of SQL commands. When a user initially connects to the database, a session is established. Within the scope of an SQL session, valid SQL commands can be entered to query the database, manipulate data in the database, and define database structures, such as tables. A session may be invoked by either direct connection to the database or through a front-end application. In both cases, sessions are normally established by a user at a terminal or workstation that communicates through a network with the computer that hosts the database.

CONNECT Command

When a user connects to a database, the SQL session is initialized. The CONNECT command is used to establish a database connection. With the CONNECT command, you can either invoke a connection or change connections to the database. For example, if you are connected as USER1, you can use the CONNECT command to connect to the database as USER2. When this happens, the SQL session for USER1 is implicitly disconnected. You would normally use the following:

CONNECT user@database

When you attempt to connect to a database, you are automatically prompted for a password that is associated with your current username. The username is used to authenticate you to the database, and the password is the key that allows entrance.

DISCONNECT and EXIT Commands

When a user disconnects from a database, the SQL session is terminated. The DISCONNECT command is used to disconnect a user from the database. When you disconnect from the database, the software you are using might still appear to be communicating with the database, but you have lost your connection. When you use EXIT to leave the database, your SQL session is terminated, and the software that you are using to access the database is normally closed.

DISCONNECT

Types of SQL Commands

The main categories are

  • Data Definition Language (DDL) 

  • Data Manipulation Language (DML) 

  • Data Query Language (DQL) 

  • Data Control Language (DCL) 

  • Data administration commands 

  • Transactional control commands 

Data Definition Language (DDL)

It is the part of SQL that enables a database user to create and restructure database objects, such as the creation or the deletion of a table, views, index, etc...

  1. CREATE statement for creating database tables, views, indexes, etc.. 

  2. ALTER statement for changing the structure of the database tables, views, indexes, etc… 

  3. DROP statement for removing database tables, views, indexes, etc… 

  4. TRUNCATE TABLE command is used to delete complete data from an existing table. 

  5. Column and table modifier/constraints: Applying below restrictions to group of columns  

    • NOT NULL constraint  

    • UNIQUE CONSTRAINT  

    • PRIMARY KEY constraint  

    • FOREIGN KEY constraint  

    • CHECK CONSTRAINT  

    • DEFAULT  

Data Manipulation Language (DML)

It is the part of SQL used to manipulate data within objects of a relational database.

  • INSERT STATEMENT for adding data to tables. 

  • UPDATE and DELETE statement for modifying data 

Data Query Language (DQL)

It is the most concentrated focus of SQL for modern relational database users. The base command is SELECT.

A query is an inquiry to the database for information. A query is usually issued to the database through an application interface or via a command-line prompt. You can easily create queries, from simple to complex, from vague to specific.

 

Data Control Language (DCL)

DCL commands are normally used to create objects related to user access and also control the distribution of privileges among users. Some data control commands are as follows:

  • ALTER PASSWORD 

  • GRANT 

  • REVOKE 

  • CREATE SYNONYM 

Data administration commands

Data administration commands enable the user to perform audits and perform analyses on operations within the database. They can also be used to help analyze system performance. Two general data administration commands are as follows:

  • START AUDIT 

  • STOP AUDIT 

Do not get data administration confused with database administration. Database administration is the overall administration of a database, which envelops the use of all levels of commands. Data administration is much more specific to each SQL implementation than are those core commands of the SQL language.

Transactional Control Commands

Transactional commands are commands that enable the user to manage database transactions:

  • COMMIT—Saves database transactions 

  • ROLLBACK—Undoes database transactions 

  • SAVEPOINT—Creates points within groups of transactions in which to ROLLBACK 

  • SET TRANSACTION—Places a name on a transaction 

 

All Chapters
Author