SQL Interview Questions and Answers

There is given sql interview questions and answers that has been asked in many companies. For PL/SQL interview questions, visit our next page.

---

1) What is SQL?

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.

---

2) When SQL appeared?

It appeared in 1974.

---

3) What are the usages of SQL?

o    To execute queries against a database

o    To retrieve data from a database

o    To inserts records in a database

o    To updates records in a database

o    To delete records from a database

o    To create new databases

o    To create new tables in a database

o    To create views in a database

---

4) Does SQL support programming?

No, SQL doesn't have loop or Conditional statement. It is used like commanding language to access databases.

---

5) What are the subsets of SQL?

1.    Data definition language (DDL)

2.    Data manipulation language (DML)

3.    Data control language (DCL)

6) 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.

---

7) 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

---

8) 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.

---

9) What are tables and fields in database?

A table is a set of organized data. It has columns and rows. Columns can be categorized as vertical, and Rows are horizontal.

A table contains specified number of column called fields but can have any number of rows which is known as record.

---

10) What is a primary key?

A primary key is a combination of fields which uniquely specify a row. This is a special kind of unique key. Primary key values cannot be NULL.

---

11) What is a foreign key?

A foreign key is specified as a key which is related to the primary key of another table. Relationship needs to be created between two tables by referencing foreign key with the primary key of another table.

---

12) What is a unique key?

A Unique key constraint uniquely identifies each record in the database. This provides uniqueness for the column or set of columns.

---

13) What are the type of operators available in SQL?

1. Arithmetic operators
2. Logical operators
3. Comparison operator

---

14) What is view in SQL?

A view is a virtual table which contains a subset of data within a table. Views are not virtually present, and it takes less space to store. View can have data of one or more tables combined, and it is depending on the relationship.

---

15) What is an Index in SQL?

Index is used to increase the performance and allow faster retrieval of records from the table. An index creates an entry for each value and it will be faster to retrieve data.

---

16) Which are the different types of indexes in SQL?

There are three types of Indexes in SQL:

• Unique Index
• Clustered Index
• NonClustered Index

---

17) What is Unique Index?

Unique Index:

This indexing does not allow the field to have duplicate values if the column is unique indexed. Unique index can be applied automatically when primary key is defined.

---

18) What is Clustered Index in SQl?

Clustered Index:

The clustered index is used to reorder the physical order of the table and search based on the key values. Each table can have only one clustered index.

---

19) What is NonClustered Index in SQL?

NonClustered Index:

NonClustered Index does not alter the physical order of the table and maintains logical order of data. Each table can have 999 non-clustered indexes.

---

20) What is the difference between SQL, MySQL and SQL Server?

SQL or Structured Query Language is a language which is used to communicate with a relational database. It provides a way to manipulate and create databases. On the other hand, MySQL and Microsoft's SQL Server both are relational database management systems that use SQL as their standard relational database language.

---

21) What is the difference between SQL and PL/SQL?

SQL or Structured Query Language is a language which is used to communicate with a relational database. It provides a way to manipulate and create databases. On the other hand, PL/SQL is a dialect of SQL which is used to enhance the capabilities of SQL. It was developed by Oracle Corporation in the early 90's. It adds procedural features of programming languages in SQL.

---

22) Is it possible to sort a column using a column alias?

Yes. You can use column alias in the ORDER BY clause for sorting.

---

23) What is the difference between clustered and non clustered index in SQL?

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.

---

24) What is the SQL query to display current date?

There is a built in function in SQL called GetDate() which is used to return current timestamp.

---

25) Which are the most commonly used SQL joins?

Most commonly used SQL joins are INNER JOIN and (left/right) OUTER JOIN.

---

26) What are the different types of joins in SQL?

Joins are used to merge two tables or retrieve data from tables. It depends on the relationship between tables.

Following are the most commonly used joins in SQL:

• Inner Join
• Right Join
• Left Join
• Full Join

---

27) What is Inner Join in SQL?

Inner join:

Inner join returns rows when there is at least one match of rows between the tables.

---

28) What is Right Join in SQL?

Right Join:

Right join is used to retrieve rows which are common between the tables and all rows of Right hand side table. It returns all the rows from the right hand side table even though there are no matches in the left hand side table.

---

29) What is Left Join in SQL?

Left Join:

Left join is used to retrieve rows which are common between the tables and all rows of Left hand side table. It returns all the rows from Left hand side table even though there are no matches in the Right hand side table.

---

30) What is Full Join in SQL?

Full Join:

Full join return rows when there are matching rows in any one of the tables. This means, it returns all the rows from the left hand side table and all the rows from the right hand side table.

---

31) What is "TRIGGER" in SQL?

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.

---

32) What is self join and what is the requirement of self join?

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.

---

33) What are set operators in SQL?

Union, Intersect or Minus operators are called set operators.

---

34) What is the difference between BETWEEN and IN condition operators?

The BETWEEN operator is used to display rows based on a range of values. The IN condition operator is used to check for values contained in a specific set of values.

---

35) What is a constraint? Tell me about its various levels.

Constraints are representators of a column to enforce data entity and consistency. There are two levels :

1. column level constraint
2. table level constraint

---

36) Write an SQL query to find names of employee start with 'A'?

1.    SELECT * FROM Employees WHERE EmpName like 'A%'  

---

37) Write an SQL query to get third maximum salary of an employee from a table named employee_table.

1.    SELECT TOP 1 salary   

2.    FROM (  

3.    SELECT TOP 3 salary  

4.    FROM employee_table  

5.    ORDER BY salary DESC ) AS emp  

6.    ORDER BY salary ASC;      

---

38) What is the difference between DELETE and TRUNCATE statement in SQL?

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.

---

39) What is ACID property in database?

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.

---

40) What is the difference among NULL value, zero and blank space?

Ans: A NULL value is not same as zero or a blank space. A NULL value is a value which is 'unavailable, unassigned, unknown or not applicable'. On the other hand, zero is a number and blank space is treated as a character.

---

41) What is the usage of SQL functions?

SQL functions are used for following purpose:

• To perform calculations on data.
• To modify individual data items.
• To manipulate the output.
• To format dates and numbers.
• To convert data types.

---

42) Which are the different case manipulation functions in SQL?

There are three case manipulation functions in SQL:

• LOWER
• UPPER
• INITCAP

---

43) What is the usage of NVL function?

The NVL function is used to convert NULL value to a actual value.

---

44) Which function is used to return remainder in a division operator in SQL?

The MOD function returns the remainder in a division operation.

---

45) What is the syntax and use of the COALESCE function?

The syntax of COALESCE function:

1.    COALESCE(exp1, exp2, ... expn)  

The COALESCE function is used to return the first non-null expression given in the parameter list.

---

46) What is the usage of DISTINCT keyword?

The DISTINCT keyword is used to ensure that the fetched value is only a non-duplicate value.

SQL Interview Questions

What is the difference between SQL and MySQL or SQL Server?

SQL or Structured Query Language is a language; language that communicates with a relational database thus providing ways of manipulating and creating databases. MySQL and Microsoft’s SQL Server both are relational database management systems that use SQL as their standard relational database language.

What is the difference between SQL and PL/SQL?

PL/SQL is a dialect of SQL that adds procedural features of programming languages in SQL. It was developed by Oracle Corporation in the early 90's to enhance the capabilities of SQL.

 

 What are various DDL commands in SQL? Give brief description of their purposes.

Following are various DDL or Data Definition Language commands in SQL −

• CREATE − it creates a new table, a view of a table, or other object in database.
• ALTER − it modifies an existing database object, such as a table.
• DROP − it deletes an entire table, a view of a table or other object in the database.

What are various DML commands in SQL? Give brief description of their purposes.

Following are various DML or Data Manipulation Language commands in SQL −

• SELECT − it retrieves certain records from one or more tables.
• INSERT − it creates a record.
• UPDATE − it modifies records.
• DELETE − it deletes records.

What are various DCL commands in SQL? Give brief description of their purposes.

Following are various DCL or Data Control Language commands in SQL −

• GRANT − it gives a privilege to user.
• REVOKE − it takes back privileges granted from user.

Can you sort a column using a column alias?

Yes. A column alias could be used in the ORDER BY clause.

 

 Is a NULL value same as zero or a blank space? If not then what is the difference?

A NULL value is not same as zero or a blank space. A NULL value is a value which is ‘unavailable, unassigned, unknown or not applicable’. Whereas, zero is a number and blank space is a character.

Say True or False. Give explanation if False.
If a column value taking part in an arithmetic expression is NULL, then the result obtained would be NULLM.

True.

If a table contains duplicate rows, does a query result display the duplicate values by default? How can you eliminate duplicate rows from a query result?

A query result displays all rows including the duplicate rows. To eliminate duplicate rows in the result, the DISTINCT keyword is used in the SELECT clause.

What is the purpose of the condition operators BETWEEN and IN?

The BETWEEN operator displays rows based on a range of values. The IN condition operator checks for values contained in a specific set of values.

How do you search for a value in a database table when you don’t have the exact value to search for?

In such cases, the LIKE condition operator is used to select rows that match a character pattern. This is also called ‘wildcard’ search.

What is the default ordering of data using the ORDER BY clause? How could it be changed?

The default sorting order is ascending. It can be changed using the DESC keyword, after the column name in the ORDER BY clause.

What are the specific uses of SQL functions?

SQL functions have the following uses −

• Performing calculations on data
• Modifying individual data items
• Manipulating the output
• Formatting dates and numbers
• Converting data types

What are the case manipulation functions of SQL?

LOWER, UPPER, INITCAP

Which function returns the remainder in a division operation?

The MOD function returns the remainder in a division operation.

What is the purpose of the NVL function?

The NVL function converts a NULL value to an actual value.

What is the difference between the NVL and the NVL2 functions?

The NVL(exp1, exp2) function converts the source expression (or value) exp1 to the target expression (or value) exp2, if exp1 contains NULL. The return value has the same data type as that of exp1.
The NVL2(exp1, exp2, exp3) function checks the first expression exp1, if it is not null then, the second expression exp2 is returned. If the first expression exp1 is null, then the third expression exp3 is returned.

What is the use of the NULLIF function?

The NULLIF function compares two expressions. If they are equal, the function returns null. If they are not equal, the first expression is returned.

Discuss the syntax and use of the COALESCE function?

The COALESCE function has the expression COALESCE(exp1, exp2, …. expn)
It returns the first non-null expression given in the parameter list.

Which expressions or functions allow you to implement conditional processing in a SQL statement?

There are two ways to implement conditional processing or IF-THEN-ELSE logic in a SQL statement.

• Using CASE expression
• Using the DECODE function

You want to display a result query from joining two tables with 20 and 10 rows respectively. Erroneously you forget to write the WHERE clause. What would be the result?

The result would be the Cartesian product of two tables with 20 x 10 = 200 rows.

 

 What is the difference between cross joins and natural joins?

The cross join produces the cross product or Cartesian product of two tables. The natural join is based on all the columns having same name and data types in both the tables.

 

 What is the purpose of the group functions in SQL? Give some examples of group functions.

Group functions in SQL work on sets of rows and returns one result per group. Examples of group functions are AVG, COUNT, MAX, MIN, STDDEV, SUM, VARIANCE.

Say True or False. Give explanation if False.By default the group functions consider only distinct values in the set.

By default, group functions consider all values including the duplicate values.

Say True or False. Give explanation if False.
The DISTINCT keyword allows a function consider only non-duplicate values.

True.

Say True or False. Give explanation if False.All group functions ignore null values.

True.

Say True or False. Give explanation if False.COUNT(*) returns the number of columns in a table.

False. COUNT(*) returns the number of rows in a table.

 

 What’s wrong in the following query?

SELECT subject_code, count(name)  
FROM students;

It doesn’t have a GROUP BY clause. The subject_code should be in the GROUP BY clause.

   SELECT subject_code, count(name)
   FROM students
   GROUP BY subject_code;

 

 What’s wrong in the following query?

   SELECT subject_code, AVG (marks)
   FROM students
   WHERE AVG(marks) > 75
   GROUP BY subject_code;

The WHERE clause cannot be used to restrict groups. The HAVING clause should be used.

   SELECT subject_code, AVG (marks)
   FROM students
   HAVING AVG(marks) > 75
   GROUP BY subject_code;

Say True or False. Give explanation if False.Group functions cannot be nested.

False. Group functions can be nested to a depth of two.

What do you understand by a subquery? When is it used?

A subquery is a SELECT statement embedded in a clause of another SELECT statement. It is used when the inner query, or the subquery returns a value that is used by the outer query. It is very useful in selecting some rows in a table with a condition that depends on some data which is contained in the same table.

Say True or False. Give explanation if False.A single row subquery returns only one row from the outer SELECT statement

False. A single row subquery returns only one row from the inner SELECT statement.

Say True or False. Give explanation if False.A multiple row subquery returns more than one row from the inner SELECT statement.

True.

Say True or False. Give explanation if False.Multiple column subqueries return more than one column from the inner SELECT statement.

True.

What’s wrong in the following query?

   SELECT student_code, name
   FROM students
   WHERE marks = 
               (SELECT MAX(marks)
                  FROM students
                  GROUP BY subject_code);

Here a single row operator = is used with a multiple row subquery.

What are the various multiple row comparison operators in SQL?

IN, ANY, ALL.

What is the pupose of DML statements in SQL?

The DML statements are used to add new rows to a table, update or modify data in existing rows, or remove existing rows from a table.

Which statement is used to add a new row in a database table?

The INSERT INTO statement.

Say True or False. Give explanation if False.While inserting new rows in a table you must list values in the default order of the columns.

True.

How do you insert null values in a column while inserting data?

Null values can be inserted into a table by one of the following ways −

• Implicitly by omitting the column from the column list.
• Explicitly by specifying the NULL keyword in the VALUES clause.

Say True or False. Give explanation if False.INSERT statement does not allow copying rows from one table to another.

False. INSERT statement allows to add rows to a table copying rows from an existing table.

How do you copy rows from one table to another?

The INSERT statement can be used to add rows to a table by copying from another table. In this case, a subquery is used in the place of the VALUES clause.

What happens if you omit the WHERE clause in the UPDATE statement?

All the rows in the table are modified.

Can you modify the rows in a table based on values from another table? Explain.

Yes. Use of subqueries in UPDATE statements allow you to update rows in a table based on values from another table.

Say True or False. Give explanation if False.The DELETE statement is used to delete a table from the database.

False. The DELETE statement is used for removing existing rows from a table.

What happens if you omit the WHERE clause in a delete statement?

All the rows in the table are deleted.

Can you remove rows from a table based on values from another table? Explain.

Yes, subqueries can be used to remove rows from a table based on values from another table.

Say True or False. Give explanation if False.Attempting to delete a record with a value attached to an integrity constraint, returns an error.

True.

Say True or False. Give explanation if False.You can use a subquery in an INSERT statement.

True.

What is the purpose of the MERGE statement in SQL?

The MERGE statement allows conditional update or insertion of data into a database table. It performs an UPDATE if the rows exists, or an INSERT if the row does not exist.

Say True or False. Give explanation if False.A DDL statement or a DCL statement is automatically committed.

True.

What is the difference between VARCHAR2 AND CHAR datatypes?

VARCHAR2 represents variable length character data, whereas CHAR represents fixed length character data.

Say True or False. Give explanation if False.A DROP TABLE statement can be rolled back.

False. A DROP TABLE statement cannot be rolled back.

Which SQL statement is used to add, modify or drop columns in a database table?

The ALTER TABLE statement.

What is a view? Why should you use a view?

A view is a logical snapshot based on a table or another view. It is used for −

• Restricting access to data;
• Making complex queries simple;
• Ensuring data independency;
• Providing different views of same data.

Say True or False. Give explanation if False.A view doesn’t have data of its own.

True.

Starting up Database

Before we access oracle database, we must start oracle database. Starting up oracle database means starting oracle instance and associating oracle instance with an oracle database so that oracle instance can access the database.

The process is very length and complicated. Several steps are involved in it. But fortunately we do not have to know all that happens when a database starts. We just need to select an option or two to startup database.

Generally you do not have to startup database in case of Oracle Server running on Windows NT/Windows 2000 as oracle server automatically starts in this case. However, if you ever have to start oracle database on Windows NT/Windows 2000, follow the steps given below:

1.    Start services program using Administrative Tools -> Service in Windows/2000 or Control Panel -> Service on Windows NT.

2.    If service OracleServiceOracle8i has not yet started, click on it with right button and select start option from popup menu.

The exact name of the service depends on the name you have given to oracle instance at the time of installing it.

Note: Starting and shutting down the database is the job of Database Administrator. As this books assumes that you are an application developer, it doesn’t get into those details.

Starting up database in Personal Oracle

Unlike Oracle Server in Personal Oracle, Oracle Instance doesn’t start on its own. The Oracle Instance must be explicitly started. The following are the steps to start oracle on Personal Oracle:

1.    Select start database option in Personal Oracle8i for windows menu.

2.    When a dialog box is displayed wait until the message Oracle Instance Started appears.

3.    Click on Close button to close the dialog box.

 

Starting SQL*PLUS

Sql*plus is a tool that comes along with Oracle. It is used to issue SQL and SQL*PLUS commands. It provides command line interface through which we can enter SQL and SQL*PLUS command.

To start SQL*PLUS, take the steps given below:

1.    Select start->programs->Oracle - Oracle8i.

Oracle8i is the name of the instance. It may be different on your system.

2.    Then select Application Development -> SQL Plus.

3.    When Log On dialog box is displayed, enter username, password and Host string. Use tab key to move from one field to another.  For more information about each of these fields, see next section.

4.    Click on OK.

5.    If the information supplied is valid then you enter into Oracle and SQL*PLUS will display SQL> prompt.

Username, Password and Host String

Oracle is a multi-user database. Whoever is access the database must log on to database.  To log on we have to supply username and password.  When the given username and password are recognized by Oracle, it will allow us to access data.  A user can access only the data that belongs to his/her and not the data of others.  However, it is possible for a user to grant privileges to others so that other can access his/her data.

Creation of users and management of overall security is the responsibility of Database Administrator (DBA).  DBA is the person who makes sure that database is functioning smoothly. He is responsible for operations such as taking backup of the database, recovering the database in the event of failure, fine tuning database to get best performance. So, if you want to have a new account under your name, please consult administrator of your database.

Username & Password

Every user who wants to access oracle database must have an account in the database. These accounts are created by DBA. Each account is associated with username and password.

Oracle comes with a set of predefined accounts. The following are the usernames and passwords of these accounts.

Username                   Password

system                                    manager

sys                               change_on_install

Scott                            tiger

Demo                           demo

Note: when you enter into oracle using either system or sys then you become DBA. That means you get special privileges to perform major operations such as creating users etc.

Host String

Host string is a name that is used to access oracle server that is running on a different machine from client. This is required only when you are trying to access oracle server that is not on the current machine. That means, you never need to use host string for Personal Oracle as client and oracle always run on the same machine in Personal Oracle.

Host string is required when you are trying to connect to Oracle Server running on remote machine.  Host string is actually called as net service name. Net service name is a name that is stored in TNSNAMES.ORA file on the client to provide the following information.

Host	Name of the machine or IP address of the machine on which oracle server is running.
Instance name	Name of the Oracle Instance running on the remote machine.
Port Number	Port number of the listener, a program that takes requests from clients.  Port number is an integer that uniquely identifies the program on the server.

How to enter SQL statements?

SQL*PLUS allow to types of command to entered at the prompt  - SQL and SQL*PLUS.

SQL commands include commands of ANSI/ISO SQL and extra commands added to ANSI SQL by oracle.

The following are the rules to be followed while entering SQL commands.

1.    An SQL statement may be entered in multiple lines.

2.    It is not possible to break a word across lines.

3.    SQL statement must be terminated by semicolon (;).

The following is an example of SQL command.  What this command does is not important at this moment.

SQL> select ccode,name

  2  from courses

3    where fee > 5000;

In the above command, we entered the command in three lines. When you enter semicolon and press enter key then SQL*PLUS will take it as the end of the command. Also note that you have to press enter key at the end of each line.

Note: Both SQL and SQL*PLUS commands are NOT case sensitive.

 
How to enter SQL*PLUS statements?

SQL*Plus statements are available only in SQL*PLUS. They are not part of standard SQL. SQL*Plus commands are mainly used for two purposes – editing SQL commands and formatting result of query.

The following rules are to be followed while entering these commands.

1.    The entire command must be entered on a single line.

2.    No need to terminate command with semicolon (;).

3.    Commands can be abbreviated. However, the amount of abbreviation is not fixed.  Some commands are abbreviated to one letter some are abbreviated to 2 and so on.

The following example show how to use CLEAR SCREEN command of SQL*PLUS.

SQL>clear screen

Or it can be abbreviated to  

SQL>cl scr

Common Errors

The following are the common errors that you get while you are trying to log on to Oracle.

Ora-01017: invalid username/password; login denied

The reason for this error is that you have entered a username or password that is not valid. Check whether username you are entering is really existing and password is correctly typed.  Sql*plus gives you three chances to type username and password correctly. If you  cannot log on successfully in three chances then Sql*plus will exit. However, you can restart Sql*plus again.

ORA-01034: ORACLE not available

The reason for this message is that Oracle Instance is not up and running. You have to first make sure you have started Oracle Instance. Actually there are a  few other problems that occurs when Oracle Instance has not started successfully. If this is case in Oracle Server, notify administrator. If this is the case with Personal Oracle, make sure you start database as mentioned in “starting up database” section.

 

Summary

In this chapter, we have seen what is Oracle and what is the difference between Oracle Server and Personal Oracle. We have seen how to connect to Oracle through SQL*Plus.

In the next chapter, we start creating tables and understanding elementary statements in SQL.