SQL in DBMS - PK Series

Dear Readers, 
sql dbms
SBI SO written results are out, congratulations to all who cleared it and all the best for interviews. 
Today we'll cover important areas in SQL. Raise your doubts in comments section!

==>> This article is a part of PK Series

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% '


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;


It removes a savepoint that you have created. 

syntax - RELEASE SAVEPOINT savepoint_name; 


It can be used to specify characteristics for the transaction that follows.

syntax - SET TRANSACTION [ read write | read only ];  

Quote of the day

I'm afraid we must make the world honest before we can honestly say to our children that honesty is the best policy   - Bernard Shaw

                                                                                                                                         Deepak A


Join 40,000+ readers and get free notes in your email


Post a Comment

Thanks for commenting. It's very difficult to answer every query here, it's better to post your query on IBPSToday.com