select * from Customers
select lower(CustomerName) from Customers
select upper(CustomerName) from Customers
Select first 2 characters of Country from Customers
select *, substring(COUNTRY, 1 ,2) from Customers
Get position of 'l' in name 'John' from employee table
select *, CHARINDEX('l',CustomerName,0) from Customers
Get length of FIRST_NAME from employee table
select *,len(CONTACTNAME) from Customers
Get ContactName and Country as single column from Customers table separated by a '->'
select ContactName + ' --> ' + Country from Customers
Select * from employee order by FIRST_NAME asc,SALARY desc
Select * from EMPLOYEE where FIRST_NAME like '___n' (Underscores)
Select * from EMPLOYEE where FIRST_NAME like '%n%'
Select * from EMPLOYEE where FIRST_NAME like '%n'
Get employee details from employee table whose Salary between 500000 and 800000
Select * from EMPLOYEE where Salary between 500000 and 800000
Get names of employees from employee table who has '%' in Last_Name. Tip : Escape character for special characters in a query.
Select FIRST_NAME from employee where Last_Name like '%[%]%'
Get department wise minimum salary from employee table order by salary ascending
select DEPARTMENT,min(SALARY) MinSalary from employee group by DEPARTMENT order by MinSalary asc
Select employee details from employee table if data exists in incentive table ?
select * from EMPLOYEE where exists (select * from INCENTIVES)
Explanation: Here "exists" statement helps us to do the job of If statement. Main query will get executed if the sub query returns at least one row. So we can consider the sub query as "If condition" and the main query as "code block" inside the If condition. We can use any SQL commands (Joins, Group By , having etc) in sub query. This command will be useful in queries which need to detect an event and do some activity.
How many sub queries can be nested in a query?
A sub query can be nested inside the WHERE or HAVING clause of an outer SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery. Up to 32 levels of nesting is possible, although the limit varies based on available memory and the complexity of other expressions in the query.
What is SQL Injection ?
SQL Injection is one of the the techniques uses by hackers to hack a website by injecting SQL commands in data fields.
Select FIRST_NAME from employee where Last_Name like '%[%]%'
Select DEPARTMENT,count(FIRST_NAME),sum(SALARY) Total_Salary from employee group by DEPARTMENT order by Total_Salary descending
SQL Query Interview Questions and Answers
Question 1: SQL Query to find second highest salary of Employee
Answer: There are many ways to find second highest salary of Employee in SQL, you can either use SQL Join or Subquery to solve this problem. Here is SQL query using Subquery:
select MAX(Salary) from Employee WHERE Salary NOT IN (select MAX(Salary) from Employee );
See How to find second highest salary in SQL for more ways to solve this problem.
Question 2: SQL Query to find Max Salary from each department.
Answer: You can find the maximum salary for each department by grouping all records by DeptId and then using MAX() function to calculate maximum salary in each group or each department.
SELECT DeptID, MAX(Salary) FROM Employee GROUP BY DeptID.
These questions become more interesting if Interviewer will ask you to print department name instead of department id, in that case, you need to join Employee table with Department using foreign key DeptID, make sure you do LEFT or RIGHT OUTER JOIN to include departments without any employee as well. Here is the query
SELECT DeptName, MAX(Salary) FROM Employee e RIGHT JOIN Department d ON e.DeptId = d.DeptID GROUP BY DeptName;
In this query, we have used RIGHT OUTER JOIN because we need the name of the department from Department table which is on the right side of JOIN clause, even if there is no reference of dept_id on Employee table.
Question 3: Write SQL Query to display the current date.
Answer: SQL has built-in function called GetDate() which returns the current timestamp. This will work in Microsoft SQL Server, other vendors like Oracle and MySQL also has equivalent functions.
SELECT GetDate();
Question 4: Write an SQL Query to check whether date passed to Query is the date of given format or not.
Answer: The value to test whether it is a date. SQL has IsDate() function which is used to check passed value is a date or not of specified format, it returns 1(true) or 0(false) accordingly. Remember ISDATE() is an MSSQL function and it may not work on Oracle, MySQL or any other database but there would be something similar.
SELECT ISDATE('1/08/13') AS "MM/DD/YY";
It will return 0 because passed date is not in correct format.
SELECT ISDATE('2014-05-01');
Result: 1
SELECT ISDATE('2014-05-01 10:03');
Result: 1
SELECT ISDATE('2014-05-01 10:03:32');
Result: 1
SELECT ISDATE('2014-05-01 10:03:32.001');
Result: 1
SELECT ISDATE('techonthenet.com');
Result: 0
SELECT ISDATE(123);
Result: 0
Question 5: Write an SQL Query to print the name of the distinct employee whose DOB is between 01/01/1960 to 31/12/1975.
Answer: This SQL query is tricky, but you can use BETWEEN clause to get all records whose date fall between two dates.
SELECT DISTINCT EmpName FROM Employees WHERE DOB BETWEEN ‘01/01/1960’ AND ‘31/12/1975’;
Question 6: Write an SQL Query find number of employees according to gender whose DOB is between 01/01/1960 to 31/12/1975.
Answer :
SELECT COUNT(*), sex from Employees WHERE DOB BETWEEN '01/01/1960' AND '31/12/1975' GROUP BY sex;
Question 7: Write an SQL Query to find an employee whose Salary is equal or greater than 10000.
Answer :
SELECT EmpName FROM Employees WHERE Salary>=10000;
Question 8: Write an SQL Query to find name of employee whose name Start with ‘M’
Answer :
SELECT * FROM Employees WHERE EmpName like 'M%';
Question 9: find all Employee records containing the word "Joe", regardless of whether it was stored as JOE, Joe, or joe.
Answer :
SELECT * from Employees WHERE UPPER(EmpName) like '%JOE%';
Question 10: Write an SQL Query to find the year from date.
Answer: Here is how you can find Year from a Date in SQL Server 2008
SELECT YEAR(GETDATE()) as "Year";
Question 11: Write SQL Query to find duplicate rows in a database? and then write SQL query to delete them?
Answer: You can use the following query to select distinct records:
SELECT * FROM emp a WHERE rowid = (SELECT MAX(rowid) FROM EMP b WHERE a.empno=b.empno)
to Delete:
DELETE FROM emp a WHERE rowid != (SELECT MAX(rowid) FROM emp b WHERE a.empno=b.empno);
Question 12: There is a table which contains two column Student and Marks, you need to find all the students, whose marks are greater than average marks i.e. list of above average students.
Answer: This query can be written using subquery as shown below:
SELECT student, marks from table where marks > SELECT AVG(marks) from table)
SQL Query Interview Questions and Answers
Question 13: How do you find all employees which are also manager? .
You have given a standard employee table with an additional column mgr_id, which contains employee id of the manager.
Employee department SQL Query question
Answer: You need to know about self-join to solve this problem. In Self Join, you can join two instances of the same table to find out additional details as shown below
SELECT e.name, m.name FROM Employee e, Employee m WHERE e.mgr_id = m.emp_id;
this will show employee name and manager name in two column e.g.
name manager_name
John David
One follow-up is to modify this query to include employees which don't have a manager. To solve that, instead of using the inner join, just use left outer join, this will also include employees without managers.
How to find third or nth maximum salary from salary table?
N minimum salary:
SELECT MIN(EmpSalary)
FROM Salary
WHERE EmpSalary IN(SELECT TOP N EmpSalary FROM Salary ORDER BY EmpSalary DESC)
for Ex: 3 minimum salary:
SELECT MIN(EmpSalary)
FROM Salary
WHERE EmpSalary IN(SELECT TOP 3 EmpSalary FROM Salary ORDER BY EmpSalary DESC)
N maximum salary:
SELECT MAX(EmpSalary)
FROM Salary
WHERE EmpSalary IN(SELECT TOP N EmpSalary FROM Salary ORDER BY EmpSalary ASC)
for Ex: 3 maximum salary:
SELECT MIN(EmpSalary)
FROM Salary
WHERE EmpSalary IN(SELECT TOP 3 EmpSalary FROM Salary ORDER BY EmpSalary ASC)
Question 14: You have a composite index of three columns, and you only provide the value of two columns in WHERE clause of a select query? Will Index be used for this operation? For example if Index is on EmpId, EmpFirstName, and EmpSecondName and you write query like
SELECT * FROM Employee WHERE EmpId=2 and EmpFirstName='Radhe'
If the given two columns are secondary index column then the index will not invoke, but if the given 2 columns contain the primary index(first column while creating index) then the index will invoke. In this case, Index will be used because EmpId and EmpFirstName are primary columns.
Finding nth highest salary example and explanation
Let’s step through an actual example to see how the query above will actually execute step by step. Suppose we are looking for the 2nd highest Salary value in our table above, so our N is 2. This means that the query will look like this:
SELECT *
FROM Employee Emp1
WHERE (1) = (
SELECT COUNT(DISTINCT(Emp2.Salary))
FROM Employee Emp2
WHERE Emp2.Salary > Emp1.Salary)
You can probably see that Emp1 and Emp2 are just aliases for the same Employee table – it’s like we just created 2 separate clones of the Employee table and gave them different names.
Understanding and visualizing how the query above works
Let’s assume that we are using this data:
Employee
| Employee ID | Salary |
| 3 | 200 |
| 4 | 800 |
| 7 | 450 |
For the sake of our explanation, let’s assume that N is 2 – so the query is trying to find the 2nd highest salary in the Employee table. The first thing that the query above does is process the very first row of the Employee table, which has an alias of Emp1.
The salary in the first row of the Employee table is 200. Because the subquery is correlated to the outer query through the alias Emp1, it means that when the first row is processed, the query will essentially look like this – note that all we did is replace Emp1.Salary with the value of 200:
SELECT *
FROM Employee Emp1
WHERE (1) = (
SELECT COUNT(DISTINCT(Emp2.Salary))
FROM Employee Emp2
WHERE Emp2.Salary > 200)
So, what exactly is happening when that first row is processed? Well, if you pay special attention to the subquery you will notice that it’s basically searching for the count of salary entries in the Employee table that are greater than 200. Basically, the subquery is trying to find how many salary entries are greater than 200. Then, that count of salary entries is checked to see if it equals 1 in the outer query, and if so then everything from that particular row in Emp1 will be returned.
Note that Emp1 and Emp2 are both aliases for the same table – Employee. Emp2 is only being used in the subquery to compare all the salary values to the current salary value chosen in Emp1. This allows us to find the number of salary entries (the count) that are greater than 200. And if this number is equal to N-1 (which is 1 in our case) then we know that we have a winner – and that we have found our answer.
But, it’s clear that the subquery will return a 2 when Emp1.Salary is 200, because there are clearly 2 salaries greater than 200 in the Employee table. And since 2 is not equal to 1, the salary of 200 will clearly not be returned.
So, what happens next? Well, the SQL processor will move on to the next row which is 800, and the resulting query looks like this:
SELECT *
FROM Employee Emp1
WHERE (1) = (
SELECT COUNT(DISTINCT(Emp2.Salary))
FROM Employee Emp2
WHERE Emp2.Salary > 800)
Since there are no salaries greater than 800, the query will move on to the last row and will of course find the answer as 450. This is because 800 is greater than 450, and the count will be 1. More precisely, the entire row with the desired salary would be returned, and this is what it would look like:
It’s also worth pointing out that the reason DISTINCT is used in the query above is because there may be duplicate salary values in the table. In that scenario, we only want to count repeated salaries just once, which is exactly why we use the DISTINCT operator.
