There is given sql interview questions and answers that has been asked in many companies. For PL/SQL interview questions, visit our next page.
SQL stands for structured query language. It is a database language used for database creation, deletion, fetching rows and modifying rows etc. sometimes it is pronounced as se-qwell.
It appeared in 1974.
No, SQL doesn't have loop or Conditional statement. It is used like commanding language to access databases.
Data definition language(DDL) allows you to CREATE, ALTER and DELETE database objects such as schema, tables, view, sequence etc.
Data manipulation language makes user able to access and manipulate data. It is used to perform following operations.
Data control language allows you to control access to the database. It includes two commands GRANT and REVOKE.
GRANT: to grant specific user to perform specific task.
REVOKE: to cancel previously denied or granted permissions.
There are mainly two type of indexes in SQL, Clustered index and non clustered index. The differences between these two indexes is very important from SQL performance perspective.
1) One table can have only one clustered index but it can have many non clustered index. (approximately 250).
2) clustered index determines how data is stored physically in table. Actually clustered index stores data in cluster, related data is stored together so it makes simple to retrieve data.
3) reading from a clustered index is much faster than reading from non clustered index from the same table.
4) clustered index sort and store data rows in the table or view based on their key value, while non cluster have a structure separate from the data row.
There is a built in function in SQL called GetDate() which is used to return current timestamp.
The knowledge of JOIN is very necessary for an interviewee. Mostly used join is INNER JOIN and (left/right) OUTER JOIN.
Trigger allows you to execute a batch of SQL code when an insert, update or delete command is executed against a specific table.
Actually triggers are special type of stored procedures that are defined to execute automatically in place or after data modifications.
Self join is often very useful to convert a hierarchical structure to a flat structure. It is used to join a table to itself as like if that is the second table.
Union, intersect or minus operators are called set operators.
Constraints are representators of a column to enforce data entity and consistency. There are two levels :
The main differences between SQL DELETE and TRUNCATE statements are given below:
| No. | DELETE | TRUNCATE |
|---|---|---|
| 1) | DELETE is a DML command. | TRUNCATE is a DDL command. |
| 2) | We can use WHERE clause in DELETE command. | We cannot use WHERE clause with TRUNCATE |
| 3) | DELETE statement is used to delete a row from a table | TRUNCATE statement is used to remove all the rows from a table. |
| 4) | DELETE is slower than TRUNCATE statement. | TRUNCATE statement is faster than DELETE statement. |
| 5) | You can rollback data after using DELETE statement. | It is not possible to rollback after using TRUNCATE statement. |
ACID property is used to ensure that the data transactions are processed reliably in a database system.
A single logical operation of a data is called transaction.
ACID is an acronym for Atomicity, Consistency, Isolation, Durability.
Atomicity: it requires that each transaction is all or nothing. It means if one part of the transaction fails, the entire transaction fails and the database state is left unchanged.
Consistency: the consistency property ensure that the data must meet all validation rules. In simple words you can say that your transaction never leaves your database without completing its state.
Isolation: this property ensure that the concurrent property of execution should not be met. The main goal of providing isolation is concurrency control.
Durability: durability simply means that once a transaction has been committed, it will remain so, come what may even power loss, crashes or errors.