SQL Interview Questions Part - 1

1. What is a Primary key?

• A Primary key is a column (or collection of columns) or a set of columns that uniquely identifies each row in the table.
• Uniquely identifies a single row in the table
• Null values not allowed

2. What is a Foreign key?

• Foreign key maintains referential integrity by enforcing a link between the data in two tables.
• The foreign key in the child table references the primary key in the parent table.
• The foreign key constraint prevents actions that would destroy links between the child and parent tables.

3. Difference between primary key and unique key?

Unique Key (UK): It's a column or a group of columns that can identify a uniqueness in a row.

Primary Key (PK): It's also a column or group of columns that can identify a uniqueness in a row.

So the Primary key is just another name for unique key, but the default implementation in SQL Server is different for Primary and Unique Key.

By Default:

• PK creates a Clustered index and UK creates a Non Clustered Index.
• PK is not null, but UK allows nulls (Note: By Default)
• There can only be one and only one PK on a table, but there can be multiple UK's
• You can override the default implementation depending upon your need.
• It really depends what is your aim when deciding whether to create a UK or PK. It follows an analogy like "If there is a team of three people, so all of them are peers, but there will be one of them who will be a pair of peers: PK and UK has similar relation.".

4. What is the difference between clustered and non clustered index in SQL?

The differences between the clustered and non clustered index in SQL are :

• Clustered index is used for easy retrieval of data from the database and its faster whereas reading from non clustered index is relatively slower.
• Clustered index alters the way records are stored in a database as it sorts out rows by the column which is set to be clustered index whereas in a non clustered index, it does not alter the way it was stored but it creates a separate object within a table which points back to the original table rows after searching.
• One table can only have one clustered index whereas it can have many non clustered index.

5. What do you mean by Denormalization?

Denormalization refers to a technique which is used to access data from higher to lower forms of a database. It helps the database managers to increase the performance of the entire infrastructure as it introduces redundancy into a table. It adds the redundant data into a table by incorporating database queries that combine data from various tables into a single table.

6. Explain different types of Normalization?

There are many successive levels of normalization. These are called normal forms. Each consecutive normal form depends on the previous one.The first three normal forms are usually adequate.

• First Normal Form (1NF) – No repeating groups within rows.
• Second Normal Form (2NF) – Every non-key (supporting) column value is dependent on the whole primary key.
• Third Normal Form (3NF) – Dependent solely on the primary key and no other non-key (supporting) column value.

7. What is ACID property in a database?

ACID stands for Atomicity, Consistency, Isolation, Durability. It is used to ensure that the data transactions are processed reliably in a database system.

Atomicity: Atomicity refers to the transactions that are completely done or failed where transaction refers to a single logical operation of a data. It means if one part of any transaction fails, the entire transaction fails and the database state is left unchanged.

Consistency: Consistency ensures that the data must meet all the validation rules. In simple words, you can say that your transaction never leaves the database without completing its state.

Isolation: The main goal of isolation is concurrency control.

Durability: Durability means that if a transaction has been committed, it will occur whatever may come in between such as power loss, crash or any sort of error.

8. What is difference between varchar and varchar2 datatype?

Varchar can store up to 2000 bytes and varchar2 can store up to 4000 bytes of memory space.Varchar will occupy the space for null values whereas varchar2 can not occupy the space for null values.So varchar2 is good to use not to face performace related problems.varchar2 is faster than varchar datatype.

9. What is Index?What is use of index in SQL?

Index is optional structure associated with the table which may or may not improve the performance of Query.In simple words suppose we want to search the topic in to book we go to index page of that book and search the topic which we want.Just like that to search the values from the table when indexing is there you need not use the full table scan.

Indexes are used to improve the performance of the query.

10. What is unique index?

To create unique index you must have CREATE ANY INDEX privilege.Here the concept is bit different.User needs to check the values of the table to create unique index.If table contains uniquely identified values in specified column then you should use unique index.Especially while creating the table if we specify the primary key then unique index is automatically created on that column.But for Unique key constraint columns you separately need to do indexing.Kindly make sure that Unique key indexes created on the columns which has unique values only.

11. What is Correlated Subquery?

Correlated Query is nothing but the subquery whose output is depending on the inner query used in that query.Correlated query is the query which is executed after the outer query is executed.The outer query is always dependent on inner query.The approach of the correlated subquery is bit different than normal subqueries.In normal subqueries the inner queries are executed first and then the outer query is executed but in Correlated Subquery outer query is always dependent on inner query so first outer query is executed then inner query is executed.Correlated Subqueries always uses operator like Exist,Not Exist,IN,Not IN.

12. What is subquery?

Subquery is query within query.The output of outer query is assigned to the column which is used in where condition of outer query.The subquery output is returning only one output value and based on that output value the outer query is executed.Subqueries are used in various real life scenarios like report development,Application logic development,Performance tuning of query.

13. What will be the output of following Query?

select case when null=null then ‘Amit’ Else ‘Pradnya’ from dual;

In SQL null value is not equal to itself.So null=null is false and the output of above query is ‘Pradnya’.

14. In a query, is it possible for the users to avoid the duplicate records? How this can be done?

Yes, the same is possible and there are many methods that can help users to get the favorable fortune in this matter. The best one is to deploy the SQL SELECT DISTINCT query which is sued to return the unique values. All the repeated values or the ones which are duplicate get deleted automatically.

15. How can you say Normalization is a useful process in database management?

It is basically an approach with one of its primary aim is to simply impose a strict upper limit on the redundancy of the data. The users are free to go ahead with many of the normalizations forms present in the SQL and a few of them are First, second, third and Boyce Normal Form.

16. In SQL, what is the best thing about the Views you have come across?

These are several good things about them. The very first thing is they consume almost no space which makes them good enough to be considered at every situation. At the same time, the users are able to consider views for simply retrieving the outcomes that belongs to queries which are complicated in nature. The same may need to be executed frequently. It is possible to consider this when it comes to restricting the access to the database.

17. What are the different types of SQL’s statements?

1. DQL - Data Query Language ( or) Data Retrival Language

• SELECT Statement

2. DML – Data Manipulation Language

DML is used for manipulation of the data itself.

• INSERT Statement
• UPDATE Statement
• DELETE Statement

3. DDL – Data Definition Language

DDL is used to define the structure that holds the data.

• CREATE Statement
• ALTER Statement
• DROP Statement
• RENAME Statement
• TRUNCATE Statement

4. DCL – Data Control Language

DCL is used to control the visibility of data.

• GRANT Statement
• REVOKE Statement

5. TCL - Transaction Control Language

• COMMIT Statement
• ROLLBACK Statement
• SAVEPOINT Statement

18. What is nvl?

The NVL( ) function is available in Oracle, and not in MySQL or SQL Server. This function is used to replace NULL value with another value. It is similar to the IFNULL Function in MySQL and the ISNULL Function in SQL Server.

For example, if we have the following table,

Table Sales_Data

Store_Name	Sales
Store A	300
Store B	NULL
Store C	150

The following SQL,

SELECT SUM (NVL(Sales,100)) FROM Sales_Data;

would generate result below:

SUM (NVL(Sales,100))

550

19. What is the Difference Between (Null Value Function) nvl() & coalesce()?

Nvl is an oracle function whereas Coalesce is an ANSI Function and also coalesce performance is very high as compare to NVL Function.

NVL Function internally uses implicit conversions i.e NVL Function returns a value if the exp1, exp2 is not belong to same datatype also if exp2 automatically converted into exp1 where as in coalesce function exp1, exp2 must belongs to same datatype.

Examples1::

SELECT nvl(‘a’, sysdate) FROM dual;

Output::

a

Examples2::

SELECT Coalesce(‘a’, sysdate) FROM dual;

Error: inconsistent datatypes: expected CHAR got DATE

20. What does the term “Tuple” Mean in Relational Databases?

A row is not a tuple. Tuples are unordered sets of known values with names. Thus, the following tuples are the same thing (I'm using an imaginary tuple syntax since a relational tuple is largely a theoretical construct):

(x=1, y=2, z=3)
(z=3, y=2, x=1)
(y=2, z=3, x=1)

assuming of course that x, y, and z are all integers. Also note that there is no such thing as a "duplicate" tuple. Thus, not only are the above equal, they're the same thing. Lastly, tuples can only contain known values (thus, no nulls).

A row is an ordered set of known or unknown values with names (although they may be omitted). Therefore, the following comparisons return false in SQL:

(1, 2, 3) = (3, 2, 1)
(3, 1, 2) = (2, 1, 3)

Note that there are ways to "fake it" though. For example, consider this INSERT statement:

INSERT INTO point VALUES (1, 2, 3)

Assuming that x is first, y is second, and z is third, this query may be rewritten like this:

INSERT INTO point (x, y, z) VALUES (1, 2, 3)

Or this:

INSERT INTO point (y, z, x) VALUES (2, 3, 1)

but all we're really doing is changing the ordering rather than removing it.

And also note that there may be unknown values as well. Thus, you may have rows with unknown values:

(1, 2, NULL) = (1, 2, NULL)

but note that this comparison will always yield UNKNOWN. After all, how can you know whether two unknown values are equal?

And lastly, rows may be duplicated. In other words, (1, 2) and (1, 2) may compare to be equal, but that doesn't necessarily mean that they're the same thing.

21. What is an alias in SQL statements?

Alias is a user-defined alternative name given to the column or table. By default column alias headings appear in upper case. Enclose the alias in a double quotation marks (“ “) to make it case sensitive. “AS” Keyword before the alias name makes the SELECT clause easier to read.

For ex: Select empname AS name from employee; (Here AS is a keyword and “name” is an alias).

22. What is a Literal? Give an example where it can be used?

A Literal is a string that can contain a character, a number, or a date that is included in the SELECT list and that is not a column name or a column alias. Date and character literals must be enclosed within single quotation marks (‘ ‘), number literals need not.

For ex: Select last_name||’is a’||job_id As “emp details” from employee; (Here “is a” is a literal).

23. Describe different types of General Function used in SQL?

General functions are of following types:

• NVL: Converts a null value to an actual value. NVL (exp1, exp2) .If exp1 is null then NVL function return value of exp2.
• NVL2: If exp1 is not null, nvl2 returns exp2, if exp1 is null, nvl2 returns exp3. The argument exp1 can have any data type. NVL2 (exp1, exp2, exp3)
• NULLIF: Compares two expressions and returns null if they are equal or the first expression if they are not equal. NULLIF (exp1, exp2)
• COALESCE: Returns the first non-null expression in the expression list. COALESCE (exp1, exp2… expn). The advantage of the COALESCE function over NVL function is that the COALESCE function can take multiple alternative values. Conditional Expressions: Provide the use of IF-THEN-ELSE logic within a SQL statement. Example: CASE Expression and DECODE Function.

24. What are STUFF and REPLACE function?

STUFF Function: This function is used to overwrite existing character or inserts a string into another string.

Syntax:

STUFF(string_expression,start, length, replacement_characters)

where,

• string_expression: it is the string that will have characters substituted
• start: This refers to the starting position
• length: It refers to the number of characters in the string which are substituted.
• replacement_string: They are the new characters which are injected in the string

.

REPLACE function: This function is used to replace the existing characters of all the occurrences.

Syntax:

REPLACE (string_expression, search_string, replacement_string)

Here every search_string in the string_expression will be replaced with the replacement_string.

25. Does Oracle make a distinction between a ROWID and ROWNUM? If so, what is the difference between ROWNUM and ROWID?

Just as your home address uniquely identifies where you live, an Oracle ROWID uniquely identifies where a row resides on disk. The information in a ROWID gives Oracle everything he needs to find your row, the disk number, the cylinder, block and offset into the block.

The ROWNUM is a "pseudo-column", a placeholder that you can reference in SQL*Plus. The ROWNUM can be used to write specialized SQL and tune SQL.