SQL in DBMS - PK Series

Dear Readers, 

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!

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;

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 ];  

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

This entry passed through the Full-Text RSS service - if this is your content and you're reading it on someone else's site, please read the FAQ at http://ift.tt/jcXqJW.