109 SQL Interview Question and Answer – Part 2

1. SQL Interview Question

51. What is the need for MERGE statement?
52. List the ways in which dynamic SQL can be executed.
53. List some case manipulation functions in SQL.
54. Is semicolon used after sql? Justify why or why not.
55. What is candidate key?
56. What is the difference between JOIN and UNION?
57. What is the difference between order and group by?
58. Write an SQL query to fetch employee names having a salary greater than or equal to 20000 and less than or equal to 10000.
59. What is SQL injection? When does SQL injection occur?
60. What is ENUM?
61. What is the difference between the ATAN and ATAN2 function?
62. What is the difference between the CEIL, FLOOR and ROUND functions?
63. What is the RAND() function?
64. What is the difference between LOCALTIMESTAMP and CURRENT_TIMESTAMP?
65. Name three functions that specify current date and time.
66. Which function returns the difference between two periods? What would the format of the output be?
67. How can we fetch common records from two tables?
68. How can we fetch alternate records from a table?
69. How can we select unique records from a table?
70. What is the command used to fetch the first 5 characters of the string?
71. How to use LIKE in SQL?
72. How can we copy a table in SQL?
73. If we drop a table, does it also drop related objects?
74. What is Live Lock?
75. Can you join a table by itself?
76. Explain Equi join with an example.
77. Explain non-Equi join with an example.
78. State the difference between NVL and NVL2 functions.
79. What does this query achieve? GRANT privilege_name ON object_name TO {user_name|PUBLIC|role_name} [WITH GRANT OPTION]; ?
80. Where is MyISAM table stored?
81. What does myisamchk do?
82. How can we store videos inside SQL server table?
83. Write an SQL query to show the second highest salary from a table.
84. How would you select all the users whose phone number is NULL?
85. Write an SQL query to fetch three max salaries from a table.
86. Write an SQL query to create a new table with data and structure copied from another table.
87. What are the differences between the HAVING clause, and the WHERE clause?
88. What does a BCP command do?
89. Can a view be active if the base table is dropped?
90. When should we use NoSQL and SQL?
91. What is SYSTEM privilege?
92. What are object privileges?
93. Does the data stored in the stored procedure increase access time or execution time? Explain.
94. What is CTE?
95. Does view contain data?
96. Define a temp table.
97. What is the difference between the RANK() and DENSE_RANK() function?
98. What is referential integrity?
99. What does query optimization imply?
100. What are nested triggers?
101. What is schema in SQL server?
102. Write a query to fetch 50% records from an EmployeeInfo table.
103. Write a query to add email validation to your database.
104. What is CTE in SQL server?

2. SQL Interview Answer

51. What is the need for MERGE statement?

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

52. List the ways in which dynamic SQL can be executed.

• Write a query with parameters.
• Using ExEc.
• Using sp_executesql.

53. List some case manipulation functions in SQL.

There are three case manipulation functions in SQL, namely:

• LOWER: This function returns the string in lowercase. It takes a string as an argument and returns it by converting it into lower case. Syntax: LOWER (‘string’)
• UPPER: This function returns the string in uppercase. It takes a string as an argument and returns it by converting it into uppercase. Syntax: UPPER(‘string’)
• INITCAP: This function returns the string with the first letter in uppercase and rest of the letters in lowercase. Syntax: INITCAP(‘string’)

54. Is semicolon used after sql? Justify why or why not.

Some database systems require a semicolon at the end of each SQL statement. Semicolon is the standard way to separate each SQL statement in database systems that allow more than one SQL statement to be executed in the same call to the server.

55. What is candidate key?

A candidate key is a subset of a super key set where the key which contains no redundant attribute is none other than a Candidate Key. In order to select the candidate keys from the set of super key, we need to look at the super key set.

56. What is the difference between JOIN and UNION?

• JOIN

JOIN in SQL is used to combine data from many tables based on a matched condition between them. The data combined using JOIN statement results into new columns.

• UNION

UNION in SQL is used to combine the result-set of two or more SELECT statements. The data combined using UNION statement results into new distinct rows.

57. What is the difference between order and group by?

• ORDER BY

The ORDER BY clause is used in SQL queries to sort the data returned by a query in ascending or descending order. If we omit the sorting order, it sorts the summarized result in the ascending order by default. The ORDER BY clause, like the GROUP BY clause, could be used in conjunction with the SELECT statement. ASC denotes ascending order, while DESC denotes descending order.

The following is the syntax to use the ORDER BY clause in a SQL statement:

SELECT expressions

FROM tables

[where conditions]

ORDER BY expression [ ASC | DESC ];

• GROUP BY

The GROUP BY clause is used in SQL queries to organize data that have the same attribute values. Usually, we use it with the SELECT statement. It is important to remember that we have to place the GROUP BY clause after the WHERE clause. Additionally, it is paced before the ORDER BY clause.

We can often use this clause in collaboration with aggregate functions like SUM, AVG, MIN, MAX, and COUNT to produce summary reports from the database. It’s important to remember that the attribute in this clause must appear in the SELECT clause, not under an aggregate function. If we do so, the query would be incorrect. As a result, the GROUP BY clause is always used in conjunction with the SELECT clause. The query for the GROUP BY clause is grouped query, and it returns a single row for each grouped object.

The following is the syntax to use GROUP BY clause in a SQL statement:

SELECT column_name, function(column_name)

FROM table_name

WHERE condition

GROUP BY column_name;

58. Write an SQL query to fetch employee names having a salary greater than or equal to 20000 and less than or equal to 10000.

By using BETWEEN in the where clause, we can retrieve the Employee Ids of employees with salary >= 20000 and <=1oo0o. e.g.

SELECT FullName

FROM EmployeeDetails

WHERE EmpId

IN (select EmpId FROM EmployeeSalary WHERE Salary BETWEEN 0 AND 10000)

59. What is SQL injection? When does SQL injection occur?

SQL Injection is a type of database attack technique where malicious SQL statements are inserted into an entry field of database in a way that once it is executed, the database is exposed to an attacker for the attack. This technique is usually used for attacking data-driven applications to have access to sensitive data and perform administrative tasks on databases.

60. What is ENUM?

An ENUM is a string object with a value chosen from a list of permitted values that are enumerated explicitly in the column specification at table creation time.

61. What is the difference between the ATAN and ATAN2 function?

• ATAN() Function

ATAN() function in MySQL is used to return the arc tangent of any number x. The arctangent of x is defined as the inverse tangent function of x when x is real (xG r).

• ATAN2() Function

ATAN2() function in MySQL is used for returning the arc tangent between specified two numbers, i.e., x and y. It returns the angle between the positive x-axis and the line from the origin to the point (y, x).

62. What is the difference between the CEIL, FLOOR and ROUND functions?

• ROUND – Rounds a positive or negative value to a specific length.
• CEILING – Evaluates the value on the right side of the decimal and returns the smallest integer greater than, or equal to, the specified numeric expression.
• FLOOR – Evaluates the value on the right side of the decimal and returns the largest integer less than or equal to the specified numeric expression.

63. What is the RAND() function?

The RAND() function will return a value between 0 (inclusive) and 1 (exclusive). The RAND() function will return a completely random number if no seed is provided, and a repeatable sequence of random numbers if a seed value is used.

64. What is the difference between LOCALTIMESTAMP and CURRENT_TIMESTAMP?

LOCALTIMESTAMP returns only time stamp value where as the function CURRENT_TIMESTAMP will return time stamp with Time Zone value.

65. Name three functions that specify current date and time.

SQL Server provides several different functions that return the current date time including: GETDATE(), SYSDATETIME(), and CURRENT_TIMESTAMP

66. Which function returns the difference between two periods? What would the format of the output be?

DATEDIFF() is a basic SQL Server function that can be used to do date math. Specifically, it gets the difference between 2 dates with the results returned in date units specified as years, months, days, minutes, seconds as an int (integer) value.

67. How can we fetch common records from two tables?

Intersection A (ì B of two sets A and B is the set, which contains all the elements of A, which also belong to B (or equivalently, all elements of B that also belong to a), but no other elements.

Let A = { Orange, pineapple, banana} and let B={ spoon,Orange, pineapple, mango}

A n B = {Orange, pineapple}

• Select * from student
• Select * from student1
• (Select * from student) Intersect (Select * from student!)

68. How can we fetch alternate records from a table?

Records can be fetched for both Odd and Even row numbers.

• To display even numbers

Select employeeId from (Select row no, employeeId from employee) where mod(row no,2)=0

• To display odd numbers

Select employeeId from (Select rowno, employeeId from employee) where mod(row no,2)=1

69. How can we select unique records from a table?

Select unique records from a table by using the DISTINCT keyword.

70. What is the command used to fetch the first 5 characters of the string?

SELECT SUBSTRING(‘SQL Tutorial’, 1,5) AS

ExtractString;

71. How to use LIKE in SQL?

The LIKE operator checks if an attribute value matches a given string pattern. Here is an example of LIKE operator:

SELECT * FROM employees WHERE first_name like ‘Steven’;

With this command, we will be able to extract all the records where the first name is like “Steven”.

72. How can we copy a table in SQL?

We can use the SELECT INTO statement to copy data from one table to another. Either we can copy all the data or only some specific columns.

This is how we can copy all the columns into a new table:

SELECT *

INTO newtable

FROM oldtable

WHERE condition;

If we want to copy only some specific columns, we can do it this way:

SELECT column!, column2, column3, ...

INTO newtable

FROM oldtable

WHERE condition;

73. If we drop a table, does it also drop related objects such as constraints, indexes, columns, default, views and sorted procedures?

Yes, SQL server drops all related objects, which exists inside a table like constraints, index, columns, defaults etc. However, dropping a table will not drop views and sorted procedures as they exist outside the table.

74. What is Live Lock?

A live lock is one wherein a request for an exclusive lock is repeatedly denied because a series of overlapping shared locks keep interfering.

75. Can you join a table by itself?

A table can be joined to itself using self join, when you want to create a result set that joins records in a table with other records in the same table.

76. Explain Equi join with an example.

When two or more tables has been joined using equal to operator then this category is called as equi join.

Example:

Select a.Employee_name, b.Department_name 
from Employee a, Employee b 
where a.Department_ID = b.Department_ID

77. Explain non-Equi join with an example.

When two or more tables are joining without an equal to condition then that join is known as Non Equi Join. Any operator can be used here, that is <>,! =,<,>,Between.

Example:

Select b.Department_ID, b.Department_name 
from Employee a, Department b 
where a.Department_id <> b.Department_ID;

78. State the difference between NVL and NVL2 functions.

Both the NVL(exp1, exp2) and NVL2(exp1, exp2, exp3) functions check the value expl to see if it is null. With the NVL(exp1, exp2) function, if expl is not null, then the value of expl is returned; otherwise, the value of exp2 is returned. With the NVL2(exp1, exp2, exp3) function, if expl is not null, then exp2 is returned; otherwise, the value of exp3 is returned.

79. What does this query achieve? GRANT privilege_name ON object_name TO {user_name|PUBLIC|role_name} [with grant option]; ?

The given syntax indicates that the user can grant access to another user too.

80. Where is MylSAM table stored?

Each MylSAM table is stored on disk in three files.

• The “.frm” file stores the table definition.
• The data file has a ‘.MYD’ (MYData) extension.
• The index file has a ‘.MYI’ (MYIndex) extension.

81. What does myisamchk do?

It compresses the MylSAM tables, which reduces their disk or memory usage.

82. How can we store videos inside SQL server table?

By using FILESTREAM datatype, which was introduced in SQL Server 2008.

83. Write an SQL query to show the second highest salary from a table.

Below is the syntax to find 2nd highest salary in SQL:

SELECT name, MAX(salary)

FROM employees

WHERE salary < (select MAX(salary)

FROM employees);

84. How would you select all the users whose phone number is NULL?

SELECT user_name FROM users WHERE

ISNULL(user_phonenumber);

85. Write an SQL query to fetch three max salaries from a table.

SELECT TOP 1 salary FROM ( SELECT TOP 3 salary FROM employee_table ORDER BY salary DESC ) AS emp ORDER BY salary ASC;

86. Write an SQL query to create a new table with data and structure copied from another table.

Using SELECT INTO command- SELECT * INTO newTable FROM EmployeeDetails;

87. What are the differences between the and the WHERE clause?

88. What does a BCP command do?

The Bulk Copy is a utility or a tool that exports/imports data from a table into a file and vice versa.

89. Can a view be active if the base table is dropped?

No, the view cannot be active if the parent table is dropped.

90. When should we use NoSQL and SQL?

SQL stands for structured query language and is majorly used to query data from relational databases. When we talk about a SQL database, it will be a relational database.

But when it comes to NoSQL database, we will be working with non-relational databases.

91. What is SYSTEM privilege?

Rights are given to a user, usually by the DBA, to perform a particular action on the database schema objects like creating tablespaces.

The following are examples of system privileges that can be granted to users:

• CREATE TABLE allows a grantee to create tables in the grantee’s schema.
• CREATE USER allows a grantee to create users in the database.
• CREATE SESSION allows a grantee to connect to an Oracle database to create a user session.

92. What are object privileges?

An object-level privilege is a permission granted to a database user account or role to perform some action on a database object. These object privileges include SELECT, INSERT, UPDATE, DELETE, ALTER, InDeX on tables, and so on.

The following example is of object privileges that can be granted to users:

SELECT ON hr.employees TO myuser INSERT ON hr.employees TO myuser

93. Does the data stored in the stored procedure increase access time or execution time? Explain.

Data stored in stored procedures can be retrieved much faster than the data stored in the SQL database. Data can be precompiled and stored in stored procedures. This reduces the time gap between query and compiling as the data has been pre-compiled and stored in the procedure.

94. What is CTE?

A CTE or common table expression is an expression that contains temporary result set which is defined in a SQL statement.

95. Does view contain data?

No, Views are virtual structures.

96. Define a temp table.

A temp table is a temporary storage structure to store the data temporarily.

97. What is the difference between the

RANK() and DENSE_RANK() function?

The only difference between the RANK() and DENSE_RANK() functions is in cases where there is a “tie”; i.e., in cases where multiple values in a set have the same ranking. In such cases, RANK() will assign non- consecutive “ranks” to the values in the set (resulting in gaps between the integer ranking values when there is a tie), whereas DENSE_RANK() will assign consecutive ranks to the values in the set (so there will be no gaps between the integer ranking values in the case of a tie).

For example, consider the set {25, 25, 50, 75, 75, 100}. For such a set, RANK() will return {1, 1, 3, 4, 4, 6} (note that the values 2 and 5 are skipped), whereas DENSE_RANK() will return {1,1,2,3,3,4}.

98. What is referential integrity?

Set of rules that restrict the values of one or more columns of the tables based on the values of the primary key or unique key of the referenced table.

99. What does query optimization imply?

Query optimization is a process in which a database system compares different query strategies and selects the query with the least cost.

100. What are nested triggers?

Triggers may implement data modification logic by using INSERT, UPDATE, and DELETE statements. These triggers that contain data modification logic and find other triggers for data modification are called Nested Triggers.

101. What is CTE in SQL server?

CTEs are Common Table Expressions that are used to create temporary result tables from which data can be retrieved/ used. The standard syntax for a CTE with a SELECT statement is:

• WITH RESULT AS
• (select COL1, COL2, COL3
• FROM employee)
• SELECT COL1, COL2 FROM RESULT

CTEs can be used with Insert, Update or Delete statements as well.

Few examples of CTEs are given below:

• Query to find the 10 highest salaries with result as:

(select distinct salary, dense_rank() over (order by salary desc) as salary rank from employees) select result. salary from result where the result.salaryrank = 10

• Query to find the 2nd highest salary with the result as:

(select distinct salary, dense_rank() over (order by salary desc) as salaryrank from employees) select result. salary from result where the result.salaryrank = 2

In this way, CTEs can be used to find the nth highest salary within an organisation.

102. What is schema in SQL server?

Our database comprises of a lot of different entities such as tables, stored procedures, functions, database owners and so on. To make sense of how all these different entities interact, we would need the help of schema. So, you can consider schema to be the logical relationship between all the different entities which are present in the database.

Once we have a clear understanding of the schema, this helps in a lot of ways:

• We can decide which user has access to which tables in the database.
• We can modify or add new relationships between different entities in the database.

Overall, you can consider a schema to be a blueprint for the database, which will give you the complete picture of how different objects interact with each other and which users have access to different entities.

103. Write a query to fetch 50% records from an EmployeeInfo table.

Select top 50 percent * from Employee;

104. Write a query to add email validation to your database.

SELECT * FROM student

WHERE s_email LIKE '%@gmail.com';

105. Suppose you have a sample table of Workers with columns Worker_id, first_name,last_name, salary, join_date, department. We have another table bonus with columns worker_ref_id, bonus date, bonus_amt. We also have another table called title and it has cols like worker_ref_id, worker_title, affected_from.

Write an SQL query to print the FIRST_NAME and LAST_NAME from Worker table into a single column COMPLETE_NAME. A space char should separate them.

Select concat(firSt_name, ' ', lAst_name) as 'COMPLETE_NAME' from Worker;

106. Write an SQL query to fetch duplicate records having matching data in some fields of a table.

SELECT WORKER_TITLE, AFFECTED_FROM, COUNT(*)

FROM Title

GROUP BY WORKER_TITLE, AFFECTED_FROM

HAVING COUNT(*) > 1;

107. Write an SQL query to print the name of employees having the highest salary in each department.

SELECT t.DEPARTMENT,t.FIRST_NAME,t.Salary 
from(SELECT max(Salary) as TotalSalary,DEPARTMENT from Worker group by department) as TempNew

Inner Join Worker t on
TempNew.DEPARTMENT=t.DEPARTMENT and TempNew.TotalSalary=t.Salary;

108. Write an SQL query that fetches the unique values of DEPARTMENT from Worker table and prints its length.

 Select distinct length(DEPARTMENT) from Worker;

109. Write a query to fetch the top N records. The SELECT TOP clause allows you to limit the number of rows or percentage of rows returned in a query result set.

Because the order of rows stored in a table is unspecified, the SELECT TOP statement is always used in conjunction with the ORDER BY clause. Therefore, the result set is limited to the first N number of ordered rows.

The following shows the syntax of the TOP clause with the SELECT statement:

SELECT TOP (expression) [PERCENT][WITH ties]

FROM table_name

ORDER BY column_name;

In this syntax, the SELECT statement can have other clauses such as WHERE, JOIN, HAVING, and GROUP BY.