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