SQL Interview Question

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

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?
To execute queries against a database
To retrieve data from a database
To inserts records in a database
To updates records in a database
To delete records from a database
To create new databases
To create new tables in a database
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?


Data definition language (DDL)
Data manipulation language (DML)
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?


Arithmetic operators
Logical operators
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.

• One table can have only one clustered index but it can have many non clustered index.(approximately 250).
• 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.
• reading from a clustered index is much faster than reading from non clustered index from the same table.
• 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 :

• column level constraint
• table level constraint

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


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.


SELECT TOP 1 salary   
FROM (  
SELECT TOP 3 salary  
FROM employee_table  
ORDER BY salary DESC ) AS emp  
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                                                                    TRUNCATE1) 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 tabl :  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:

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.

Java Basics Interview Questions              :      Java OOPs Interview Questions
Java Multithreading Interview Questions     :     Java String & Exception Interview Questions
Java Collection Interview Questions        :    JDBC Interview Questions
Servlet Interview Questions                        :    JSP Interview Questions
Spring Interview Questions                         ;   Hibernate Interview Questions
PL/SQL Interview Questions                       :     SQL Interview Questions
Oracle Interview Questions                        :    Android Interview Questions
SQL Server Interview Questions                 :     MySQL Interview Questions


47)When is the Explicit Cursor Used ?


If the developer needs to perform the row by row operations for the result set containing more than one row, then he unambiguously declares a pointer with a name. They are managed by OPEN, FETCH and CLOSE.%FOUND, %NOFOUND, %ROWCOUNT and %ISOPEN characteristics are used in all types of pointers.



48)Name some commands that can be used to manipulate text in T-SQL code. For example, a command that obtains only a portion 

of the text or replace a text string, etc.

• CHARINDEX( findTextData, textData, [startingPosition] ) – Returns the starting position of the specified expression in a character string. The starting position is optional.
• LEFT( character_expression , integer_expression ) – Returns the left part of a character string with the specified number ofcharacters.
• LEN( textData ) – Returns integer value of the length of the string, excluding trailing blanks.
• LOWER ( character_expression ) – Returns a character expression after converting uppercase character data to lowercase.
• LTRIM( textData) – Removes leading blanks. PATINDEX( findTextData, textData ) – Returns integer value of the starting position of text found in the string.
• REPLACE( textData, findTextData, replaceWithTextData ) – Replaces occurrences of text found in the string with a new value.
• REPLICATE( character_expression , integer_expression ) – Repeats a character expression for a specified number of times.
• REVERSE( character_expression ) – Returns the reverse of a character expression.
• RTRIM( textData) – Removes trailing blanks. SPACE( numberOfSpaces ) – Repeats space value specified number of times.
• STUFF( textData, start , length , insertTextData ) – Deletes a specified length of characters and inserts another set of characters at a specified starting point.
• SUBSTRING( textData, startPosition, length ) – Returns portion of the string.UPPER( character_expression ) – Returns a character expression with lowercase character data converted to uppercase.

48) What is the native system stored procedure to execute a command against all databases?

• The sp_MSforeachdb system stored procedure accepts the @Command parameter which can be exetecuted against all databases. The ‘?’ is used as a placeholder for the database name to execute the same command.
• The alternative is to use a cursor to process specific commands against each database.

50) What is the SQL CASE statement used for? Explain with an example?

It allows you to embed an if-else like clause in the SELECT clause.

SELECT Employee_Name, CASE Location
WHEN 'alex' THEN Bonus * 2
WHEN 'robin' THEN Bonus *, 5
ELSE Bonus
END
"New Bonus"
FROM  Intellipaat_employee;