SQL in DBMS | IBPS Clerk Prelims 2024 Preparation - Bank Exams PDF Download

SQL Commands

Basic SQL commands cane be classified in following groups based on their nature:

1. Data Definition Language (DDL)

• CREATE: create a new table, a view of table or other objects in database
• ALTER: modifies an existing database object, such as table
• DROP: deletes an entire table, a view of table or other objects.

2. Data Manipulation Language (DML)

• INSERT: creates a record
• UPDATE: modifies records
• DELETE: deletes records

3. Data Control Language (DCL)

• GRANT: gives a privilege to user
• REVOKE: take back a privilege from user

4. Data Query Language (DQL)

SELECT: retrieves certain records from one or more tables

Clauses in SQL

1. WHERE clause 

It is used to specify a condition while fetching data from single table or multiple tables. WHERE clause is used with SELECT, UPDATE & DELETE statements. 

Basic Syntax:

SELECT col_1 col_2

FROM table_name

WHERE <condition>

Condition can be specified using comparison or logical operators like >, <, =, NOT, LIKE etc. 

2. LIKE clause

It is used to compare a value to similar values using wildcard operators. There are 2 wildcards used in conjunction with LIKE operator:

Percent sign (%) - represents zero, one or multiple characters

Underscore ( _ ) - represents a single number or character

Basic Syntax:

SELECT from table_name

WHERE column LIKE ' %xxx% '

and

SELECT from table_name

WHERE column LIKE ' _xxx_ '

3. TOP clause

It is used to fetch TOP N numbers or X percents records from a table. All the databases do not support TOP clause. MySQL uses LIMIT to fetch limited number of records and Oracle uses ROWNUM to produces similar results. 

Basic Syntax:

SELECT TOP number|percent col_name

FROM table_name

WHERE <condition>

e.g. SELECT TOP 3 * FROM employee - this would fetch first 3 rows with all columns from employee table

4. ORDER BY Clause
It is used to sort the data in ascending or descending order, based on one or more columns. Usually ascending order is chosen by default. 

Basic Syntax:

SELECT * from Employee

ORDER BY salary DESC; 

5. GROUP BY Clause
It is used in combination with SELECT statement to arrange identical data into groups. It follows the WHERE clause and precedes the ORDER BY clause. 

Basic Syntax:

SELECT col_1 col_2

FROM table_name

WHERE <condition>

GROUP BY col_1 col_2

ORDER BY col_1

6. HAVING Clause
It enables you to specify conditions that filter which group results appear in final results. WHERE clause places conditions on selected columns but HAVING clause places conditions on groups created by GROUP BY clause. 

Basic Syntax:

SELECT col1,  col2

FROM tab1,  tab2

WHERE <conditions>

GROUP BY col1, col2

HAVING <conditions>

ORDER BY col1, col2

SQL Transactions

Transactions are sequences of work accomplished in logical order whether manually or by a database automatically.
All the transactions should maintain following properties/achieve following ends after occurring:

• Atomicity
• Consistency
• Isolation
• Durability

Following transaction control statements are used in SQL:

1. COMMIT command

• It saves all changes to the database since the last COMMIT or ROLLBACK command.
• syntax - COMMIT; 

2. ROLLBACK command

• It is used to undo transactions that have not already been saved to the database.
• syntax - ROLLBACK; 

3. SAVEPOINT command

• It is a point in transaction control when you can roll the transaction back to a certain point without rolling back the entire transaction
• syntax - SAVEPOINT savepoint_name;

4. RELEASE_SAVEPOINT command

• It removes a savepoint that you have created. 
• syntax - RELEASE SAVEPOINT savepoint_name; 

5. SET TRANSACTION command

• It can be used to specify characteristics for the transaction that follows.
• syntax - SET TRANSACTION [ read write | read only ];