SQL Interview Questions Part 2


SQL Interview Questions Part 2
1.) Does SQL support programming?
No, SQL doesn't have loop or Conditional statement. It is used like commanding language to access databases.

2.) What is data definition language?
Data definition language (DDL) allows you to CREATE, ALTER and DELETE database objects such as schema, tables, view, sequence etc.

3.) What is data manipulation language?
Data manipulation language makes user able to access and manipulate data. It is used to perform following operations.

•    Insert data into database
•    Retrieve data from the database
•    Update data in the database
•    Delete data from the database

4.) What is data control language?
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.

5.) How do you implement one-to-one, one-to-many and many-to-many relationships while designing tables?
•    One-to-One relationship can be implemented as a single table and rarely as two tables with primary and foreign key relationships.
•    One-to-Many relationships are implemented by splitting the data into two tables with primary key and foreign key relationships.
•    Many-to-Many relationships are implemented using a junction table with the keys from both the tables forming the composite primary key of the junction table.

6.) What is a stored procedure?
 

Stored Procedure is a function which contains a collection of SQL Queries. The procedure can take inputs, process them and send back output.

7.) What are the advantages a stored procedure?
Stored Procedures are precompiled and stored in the database. This enables the database to execute the queries much faster. Since many queries can be included in a stored procedure, round trip time to execute multiple queries from source code to database and back is avoided.

8.) What is recursive stored procedure?
A stored procedure which calls by itself until it reaches some boundary condition. This recursive function or procedure helps programmers to use the same set of code any number of times.

9.) What is a trigger?
When database are inserted, deleted or updated, a procedure is executed in response to that action. Such SQL procedures are called as trigger.

Example: When a new student is added to the student database, new records should be created in the related tables like Exam, Score and Attendance tables.

10.) What is difference between TRUNCATE, DELETE & DROP?
•    TRUNCATE commits after deleting entire table i.e., cannot be rolled back. Database triggers do not fire on TRUNCATE
•    DELETE allows the filtered deletion. Deleted records can be rolled back or committed. Database triggers fire on DELETE.
•    Drop command is used to drop the table or keys like primary, foreign from a table.

11.) What is Union, minus and Interact commands?
•    UNION operator is used to combine the results of two tables, and it eliminates duplicate rows from the tables.
•    MINUS operator is used to return rows from the first query but not from the second query.
•    INTERSECT operator is used to return rows returned by both the queries.

12.) What is the difference between WHERE clause and HAVING clause?
WHERE and HAVING both filters out records based on one or more conditions. The difference is, WHERE clause can only be applied on a static non-aggregated column whereas we will need to use HAVING for aggregated columns.

13.) What is a Cursor?
A database Cursor is a control which enables traversal over the rows or records in the table. This can be viewed as a pointer to one row in a set of rows. Cursor is very much useful for traversing such as retrieval, addition and removal of database records.

14.) What is a constraint?


Constraint can be used to specify the limit on the data type of table. Constraint can be specified while creating or altering the table statement.

15.) What is an integrity constraint?
Integrity constraint is a rule that restricts values to a column in a table.

16.) What is referential integrity constraint?
Maintaining data integrity through a set of rules that restrict the values of one or more columns of the tables based on the values of primary key or unique key of the referenced table.

17.) Where the integrity constraints are stored in data dictionary?
The integrity constraints are stored in USER_CONSTRAINTS.

18.) How will you activate/deactivate integrity constraints?
The integrity constraints can be enabled or disabled by ALTER TABLE ENABLE CONSTRAINT / DISABLE CONSTRAINT.

19.) If unique key constraint on DATE column is created, will it validate the rows that are inserted with SYSDATE? It won’t, Because SYSDATE format contains time attached with it.

20.) What’s an SQL injection?
SQL Injection is when form data contains an SQL escape sequence and injects a new SQL query to be run.

21.) What is a transaction and what are ACID properties?
A transaction is a logical unit of work in which, all the steps must be performed or none. ACID stands for Atomicity, Consistency, Isolation, and Durability. These are the properties of a transaction.

22.) What are properties of a transaction?
Properties of the transaction can be summarized as ACID Properties.

1. Atomicity
A transaction consists of many steps. When all the steps in a transaction gets completed, it will get reflected in DB or if any step fails, all the transactions are rolled back.

2. Consistency
The database will move from one consistent state to another, if the transaction succeeds and remain in the original state, if the transaction fails.

3. Isolation
Every transaction should operate as if it is the only transaction in the system.

4. Durability
Once a transaction has completed successfully, the updated rows/records must be available for all other transactions on a permanent basis.

23.) What is a Database Lock?
Database lock tells a transaction, if the data item in questions is currently being used by other transactions.

24.) What are the type of locks?
•    Shared Lock: When a shared lock is applied on data item, other transactions can only read the item, but can't write into it.
•    Exclusive Lock: When an exclusive lock is applied on data item, other transactions can't read or write into the data item.

25.) What is the sub-query?
Sub-query is a query whose return values are used in filtering conditions of the main query.

26.) What is correlated sub-query?
Correlated sub-query is a sub-query, which has reference to the main query.

27.) What are the properties and different Types of Sub-Queries?
Properties of Sub-Query:
•    A sub-query must be enclosed in the parenthesis.
•    A sub-query must be put in the right hand of the comparison operator, and
•    A sub-query cannot contain an ORDER-BY clause.
•    A query can contain more than one sub-query.

Types of Sub-Query:
•    Single-row sub-query, where the sub-query returns only one row.
•    Multiple-row sub-query, where the sub-query returns multiple rows, and
•    Multiple column sub-query, where the sub-query returns multiple columns

28.) What is a database link?
Database link is a named path through which a remote database can be accessed.

29.) What are the types of cursors?
The different types of cursors are, Static cursor, dynamic cursor, Forward only cursor and Keyset driven cursor.

30.) What is Collation?
Collation in SQL is set of rules to determine data stored and compared. There are different types of collation like Case sensitivity, Accent sensitivity and kana sensitivity.

31.) What are all different types of collation sensitivity?
Following are different types of collation sensitivity

Case Sensitivity – A and a and B and b.
Accent Sensitivity.
Kana Sensitivity – Japanese Kana characters.
Width Sensitivity – Single byte character and double byte character.

32.) What is Online Transaction Processing (OLTP)?
Online Transaction Processing or OLTP manages transaction based applications which can be used for data entry and easy retrieval processing of data. This processing makes like easier on simplicity and efficiency. It is faster, more accurate results and expenses with respect to OTLP.

Example: Bank Transactions on a daily basis.

--------------------------------
Prepared by: V. Raga Malika

0 comments:

Post a Comment