SQL

Question 1

How do you fetch common records from two tables?

Answer 1

Use the INTERSECT statement.

Select studentID from student
INTERSECT
Select StudentID from Exam

Question 2

BETWEEN statement exclusivity.

Answer 2

BETWEEN is Inclusive for Date ranges and Exclusive for letter ranges

Question 3

How to fetch Even or Odd rows from a table?

Answer 3

Use MOD statement

Even Rows
where mod(rowno,2)=0
Odd Rows
where mod(rowno,2)=1

Question 4

How to select unique records?

Answer 4

Use DISTINCT

Select DISTINCT EmailAddress From Blacklist

Question 5

How to select substring of characters?

Answer 5

SUBSTRING function

Select SUBSTRING(StudentName,1,5) as studentname from student

Question 6

Pattern Matching command and syntax?

Answer 6

LIKE operator

SELECT Name FROM Student LIKE ‘%brad%’

% - Matches zero or more characters.
_(Underscore) – Matching exactly one character.

Question 7

What is the difference between TRUNCATE and DROP statements?

Answer 7

TRUNCATE removes all the rows from the table, and it cannot be rolled back. DROP command removes a table from the database and operation cannot be rolled back.

Question 8

How can you create an empty table from an existing table?

Answer 8

SELECT INTO NewTable statement

Select * into studentcopy from student where 1=2

Question 9

What is an ALIAS command?

Answer 9

ALIAS name can be given to a table or column. This alias name can be referred in WHERE clause to identify the table or column

Select st.StudentID, Ex.Result from student st, Exam as Ex where st.studentID = Ex. StudentID

Question 10

How do you combine the result of two Select statements with the same schema structure without duplicates?

Answer 10

UNION Statement

Question 11

How do you get a result set that include all rows of the Left table while excluding any that are present in the Right table?

Answer 11

MINUS Statement

Question 12

What is CLAUSE?

Answer 12

SQL clause is defined to limit the result set by providing condition to the query.

ex. WHERE, GROUP BY, ORDER BY

Question 13

What is collation?

Answer 13

Collation is defined as set of rules that determine how character data can be sorted and compared. This can be used to compare A and, other language characters and also depends on the width of the characters.

ASCII value can be used to compare these character data.

Question 14

What will be the result of the query below? Explain your answer and provide a version that behaves correctly.

select case when null = null then ‘Yup’ else ‘Nope’ end as Result;

Answer 14

This query will actually yield “Nope”, seeming to imply that null is not equal to itself! The reason for this is that the proper way to compare a value to null in SQL is with the is operator, not with =.

To fix
select case when null is null then ‘Yup’ else ‘Nope’ end as Result;

Question 15

LEFT JOIN

Answer 15

Statement that joins two tables on a condition, and keeps all rows of the LEFT table regardless of if the condition is met.

Question 16

RIGHT JOIN

Answer 16

Statement that joins two tables on a condition, and keeps all rows of the RIGHT table regardless of if the condition is met.

Question 17

INNER JOIN

Answer 17

Statement that joins two tables on a condition, and discards all rows for the tables that do not match the condition.

Question 18

OUTER JOIN

Answer 18

Statement that joins all the rows of two tables and any tuples that meet the condition join together.

Question 19

CROSS JOIN

Answer 19

Statement that joins all rows of LEFT table with all rows of the RIGHT table.

Question 20

How do you find a specific character in a string in SQL

Answer 20

MySQL - LOCATE function
LOCATE (‘ ‘, FullName)

SQL Server - CHARINDEX
CHARINDEX(‘ ‘, FullName) - 1)

Question 21

How to get a substring in SQL

Answer 21

MySQL - MID function
SELECT MID(FullName, 0, LOCATE(' ',FullName)) FROM EmployeeDetails

SQL Server - SUBSTRING function
SELECT SUBSTRING(FullName, 0, CHARINDEX(' ',FullName)) FROM EmployeeDetails

Question 22

If you have an Employee Table with the Schema
{id, name, manager_id, date_joined}

How do you find employees who are also managers?

Answer 22

You would use a self-join on the Employee table.

SELECT DISTINCT E.FullName
FROM EmpDetails E
INNER JOIN EmpDetails M
ON E.EmpID = M.ManagerID;

Question 23

How would you filter on a subquery where the result is not NULL

Answer 23

Using EXISTS

SELECT * FROM EmployeeDetails E 
WHERE EXISTS (SELECT * FROM EmployeeSalary S WHERE  E.EmpId = S.EmpId);

Question 24

How would you get duplicate records from a call?

Answer 24

Using GROUB BY + HAVING

SELECT EmpId, Project, Salary, COUNT()
FROM EmployeeSalary
GROUP BY EmpId, Project, Salary
HAVING COUNT() > 1;

Question 25

Getting current Datetime?

Answer 25

MySQL - NOW()
SQL Server - getdate()
Oracle - SELECT SYSDATE FROM DUAL;

Question 26

How to fetch the top N records?

Answer 26

MySQL - Limit
SELECT * FROM EmployeeSalary ORDER BY Salary DESC LIMIT N

SQLServer - SELECT TOP
SELECT TOP N * FROM EmployeeSalary ORDER BY Salary DESC

Oracle - ROWNUM
SELECT * FROM (SELECT * FROM EmployeeSalary ORDER BY Salary DESC)
WHERE ROWNUM <= 3;