What is meant by SQL?

Structured Query Language is the full form of SQL. It is a high-level language used to communicate with a relational database. It is used for creating, storing and accessing contents of a relational database. SQL is used to write the queries which are executed using database engines like Oracle, SQL Server and so forth.

SQL commands

SQL commands are the instructions, coded in the form of SQL statement. SQL commands are used to communicate with the database and perform the required tasks. SQL commands are not only used to perform search operations but also to perform other operations like creation of database, creation of tables associated with the database, insert data, modify data, set user permission, drop table and so forth.

Types of keys used in SQL commands

The types of keys supported by SQL are listed below.

• Primary key: A column that uniquely identifies every row in the table. Example: EmpID.
• Alternate key: Alternate key is not selected as primary key, but can work as primary key. They are also termed as candidate key. Example: SSN (Social Security Number).
• Foreign Key: A column which creates a relationship between tables. Data integrity is supported by primary key - foreign key relationship.
• Super key: A group of multiple or single key which identifies a row in the table.
• Composite key: Combines multiple columns and identify the row present at the table.

Types of SQL commands

SQL commands are broadly classified into four different types and they are,

Data Definition Language (DDL)

The table structure can be changed with the help of DDL. The changes include table creation, table modification and table deletion. The DDL comments are auto-committed (all the changes are saved permanently) to the database. The following commands are under DDL.

• Create: Used to create table in the database.
• Drop: Used to delete the record and table structure.
• Alter: Used to alter the table structure by modifying the existing attributes or by creating new attributes.
• Truncate: Used to delete rows in the table.

Data Manipulation Language (DML)

The database is modified using DML commands. These commands are not auto-committed (changes cannot be saved permanently). DML allows rollback. The following commands comes under DML.

• Insert: INSERT statement is used to insert records into the table.
• Update: Update statement is used to modify the values of fields stored in the table.
• Delete: Used to delete one or more records that satisfies the condition specified in the WHERE clause of the SQL statement.

Data Control Language (DCL)

The commands of DCL are used to revoke and provide authority to the users of the database. The following are the commands under DCL.

• Grant: Provides the privilege to access the database.
• Revoke: Used to remove access to the database.

Transaction Control Language (TCL)

TCL command is used along with DML commands (INSERT, UPDATE, and DELETE). The operations are automatically committed in the database; however, TCL commands are not used during table creation and deletion. The following commands are under TCL.

• Commit: In a database, all kind of transactions are saved using commit command
• Rollback: Undo the unsaved transactions in the database.
• Savepoint: The transaction can be rolled back to a specific point using this command (without rolling back the whole transaction).

Structure of SQL query

General structure

General structure has three major clauses namely SELECT clause, FROM clause and WHERE clause. The purpose of the SELECT clause is to list the attributes whose values must be displayed in the query result. The WHERE clause is to specify the condition that the records in the query result should satisfy. The FROM clause is used to specify the table name from which the records must be retrieved.

Syntax: SELECT columnName FROM tableName WHERE condition_stmt;

Example query: SELECT * FROM stud_table WHERE class = “II A”;

Comparison

The result of the comparison is based on logical operators like OR, AND, NOT. The comparison condition can be specified in the following ways.

• columnName IN(val1, val2, val3).
• columnName BETWEEN val1 AND val2.
• columnName LIKE “%”.

Syntax: SELECT column_name FROM table_name WHERE condition;

Example query: SELECT s_name FROM stud_table WHERE class = “II A” AND result BETWEEN 85 AND 95;

Grouping

Grouping is used to arrange the related rows as a single group. The grouping functions in SQL queries are COUNT(), AVG(), SUM(), MIN(),MAX(), etc. Generally, the GROUP BY clause is used in the SELECT statement along with the ORDER BY and HAVING clause. However, both the ORDER BY and HAVING clauses are optional.

Syntax: SELECT columnName FROM tableName WHERE condition_stmt GROUP BY columnName [HAVING requirement];

Example query: SELECT s_name, class FROM stud_table GROUP BY class;

Sorting

The records can be displayed either in ascending or descending order. The default sorting order is ascending order and to sort in descending order the keyword DESC is used.

Syntax: SELECT columnName FROM tableName WHERE condition_stmt GROUP BY columnName ORDER BY columnName ASC|DESC;

Example query: Select * FROM stud_table ORDER By s_name DESC;

Set operations supported by SQL

SQL supports certain set operations, that are performed on the database. The various set operations of SQL queries are,

• Union: Combines the result of multiple SELECT statements.
• Union all: Similar to union, but it includes duplicate rows in the query result.
• Intersect: Returns the records that are commonly found in the result returned by both the SELECT statements.
• Minus: Returns the records that found in the result set returned by first query that are not in the result set returned by the second query.

Uses of SQL

• The basic SQL queries are used by the users and professionals to delete, update, and insert data in the relational database. 
• It allows creation of stored procedures, triggers and views.
• The user can set constrains or access permissions at the table level for various users.
• The user can also create, manipulate and drop the database and the table.

Context and Applications

This topic is important for postgraduate and undergraduate courses, particularly for,

• Bachelors in computer science engineering.
• Associate of science in computer science.

Practice Problems

Question 1: In SQL, the purpose of the clause ORDER BY is _______.

a) To change the sequence order

b) To sort the result.

c) Cannot be used in SQL server.

d) None of the above

Answer: Option b is correct.

Explanation: In SQL, the ORDER BY clause is used to sort the result in either ascending or descending order and the default sorting method in ascending order.  These SQL queries are executed using the SQL server.

Question 2: Select the SQL query, which retrieves all the records belonging to the class "II-A" from the stud_table.

a) SELECT * FROM stud_table WHERE class = 'II A';

b) SELECT * FROM stud_table WHERE class = &II A&;

c) SELECT s_name FROM stud_table WHERE class = #II A#;

d) SELECT s_name FROM stud_table WHERE class = %II A%;

Answer: Option a is correct.

Explanation: The SQL query in option a is correct which retrieves the records with class "II-A". Single quotes must be used to enclose data which of type varchar2.

Question 3: Select the non-DDL command.

a) Truncate

b) Create

c) Update

d) Alter

Answer: Option c is correct.

Explanation: Update is a DML command, which modifies the stored data whereas, truncate, alter, drop, create are the DDL commands and this defines the structure of the table and database.

Question 4: ___ is used to update the transaction performance permanently.

a) Rollback

b) Delete

c) Truncate

d) Commit

Answer: Option d is correct.

Explanation: The commit command ends the transaction and saves all the changes permanently. However, delete and truncate are transaction commands.

Question 5: Select the perfect order of various clauses in a SQL query statement.

a) SELECT, WHERE, GROUP BY, HAVING

b) SELECT, GROUP BY, WHERE, HAVING

c) WHERE, SELECT, HAVING, GROUP BY

d) None of the above

Answer: Option b is correct.

Explanation: A SQL query statement should be in the order with the SELECT clause followed by WHERE clause before GROUP BY and Having clause.