Microsoft SQL Server Certification Course
- 5k Enrolled Learners
- Weekend
- Live Class
SQL or Structured Query Language is a standard language for dealing with relational databases. With the humongous amount of data present, it is very important for us to understand how to use queries to retrieve the required data. In this article on SQL Query Interview Questions, I will discuss a few queries which you must practice to become a Database Administrator and will also help you ace your interviews.
Top SQL Query Interview Questions
For, your better understanding, I will be considering the following tables to write queries.
EmpID | EmpFname | EmpLname | Department | Project | Address | DOB | Gender |
1 | Sanjay | Mehra | HR | P1 | Hyderabad(HYD) | 01/12/1976 | M |
2 | Ananya | Mishra | Admin | P2 | Delhi(DEL) | 02/05/1968 | F |
3 | Rohan | Diwan | Account | P3 | Mumbai(BOM) | 01/01/1980 | M |
4 | Sonia | Kulkarni | HR | P1 | Hyderabad(HYD) | 02/05/1992 | F |
5 | Ankit | Kapoor | Admin | P2 | Delhi(DEL) | 03/07/1994 | M |
EmpID | EmpPosition | DateOfJoining | Salary |
1 | Manager | 01/05/2024 | 500000 |
2 | Executive | 02/05/2024 | 75000 |
3 | Manager | 01/05/2024 | 90000 |
2 | Lead | 02/05/2024 | 85000 |
1 | Executive | 01/05/2024 | 300000 |
Let us start by taking a look at some of the most frequently asked SQL Query interview questions,
SELECT UPPER(EmpFname) AS EmpName FROM EmployeeInfo;
SELECT COUNT(*) FROM EmployeeInfo WHERE Department = 'HR';
You can write a query as follows in SQL Server:
SELECT GETDATE();
You can write a query as follows in MySQL:
SELECT SYSTDATE();
SELECT SUBSTRING(EmpLname, 1, 4) FROM EmployeeInfo;
Using the MID function in MySQL
SELECT MID(Address, 0, LOCATE('(',Address)) FROM EmployeeInfo;
Using SUBSTRING
SELECT SUBSTRING(Address, 1, CHARINDEX('(',Address)) FROM EmployeeInfo;
Using the SELECT INTO command:
SELECT * INTO NewTable FROM EmployeeInfo WHERE 1 = 0;
Using the CREATE command in MySQL:
CREATE TABLE NewTable AS SELECT * FROM EmployeeInfo;
SELECT * FROM EmployeePosition WHERE Salary BETWEEN '50000' AND '100000';
SELECT * FROM EmployeeInfo WHERE EmpFname LIKE 'S%';
By using the TOP command in SQL Server:
SELECT TOP N * FROM EmployeePosition ORDER BY Salary DESC;
By using the LIMIT command in MySQL:
SELECT * FROM EmpPosition ORDER BY Salary DESC LIMIT N;
SELECT CONCAT(EmpFname, ' ', EmpLname) AS 'FullName' FROM EmployeeInfo;
SELECT COUNT(*), Gender FROM EmployeeInfo WHERE DOB BETWEEN '02/05/1970 ' AND '31/12/1975' GROUP BY Gender;
To order the records in ascending and descnding order, you have to use the ORDER BY statement in SQL.
SELECT * FROM EmployeeInfo ORDER BY EmpFname desc, Department asc;
To fetch details mathcing a certain value, you have to use the LIKE operator in SQL.
SELECT * FROM EmployeeInfo WHERE EmpLname LIKE '____a';
SELECT * FROM EmployeeInfo WHERE EmpFname NOT IN ('Sanjay','Sonia');
SELECT * FROM EmployeeInfo WHERE Address LIKE 'DELHI(DEL)%';
SELECT E.EmpFname, E.EmpLname, P.EmpPosition FROM EmployeeInfo E INNER JOIN EmployeePosition P ON E.EmpID = P.EmpID AND P.EmpPosition IN ('Manager');
SELECT Department, count(EmpID) AS EmpDeptCount FROM EmployeeInfo GROUP BY Department ORDER BY EmpDeptCount ASC;
To retrieve the even records from a table, you have to use the MOD() function as follows:
SELECT EmpID FROM (SELECT rowno, EmpID from EmployeeInfo) WHERE MOD(rowno,2)=0;
Similarly, to retrieve the odd records from a table, you can write a query as follows:
SELECT EmpID FROM (SELECT rowno, EmpID from EmployeeInfo) WHERE MOD(rowno,2)=1;
SELECT * FROM EmployeeInfo E WHERE EXISTS (SELECT * FROM EmployeePosition P WHERE E.EmpId = P.EmpId);
To retrieve two minimum salaries, you can write a query as below:
SELECT DISTINCT Salary FROM EmployeePosition E1 WHERE 2 >= (SELECTCOUNT(DISTINCT Salary)FROM EmployeePosition E2 WHERE E1.Salary >= E2.Salary) ORDER BY E1.Salary DESC;
To retrieve two maximum salaries, you can write a query as below:
SELECT DISTINCT Salary FROM EmployeePosition E1 WHERE 2 >= (SELECTCOUNT(DISTINCT Salary) FROM EmployeePosition E2 WHERE E1.Salary <= E2.Salary) ORDER BY E1.Salary DESC;
SELECT Salary FROM EmployeePosition E1 WHERE N-1 = ( SELECT COUNT( DISTINCT ( E2.Salary ) ) FROM EmployeePosition E2 WHERE E2.Salary > E1.Salary );
SELECT EmpID, EmpFname, Department COUNT(*) FROM EmployeeInfo GROUP BY EmpID, EmpFname, Department HAVING COUNT(*) > 1;
Select DISTINCT E.EmpID, E.EmpFname, E.Department FROM EmployeeInfo E, Employee E1 WHERE E.Department = E1.Department AND E.EmpID != E1.EmpID;
SELECT * FROM EmployeeInfo WHERE EmpID <=3 UNION SELECT * FROM (SELECT * FROM EmployeeInfo E ORDER BY E.EmpID DESC) AS E1 WHERE E1.EmpID <=3;
SELECT TOP 1 salary FROM( SELECT TOP 3 salary FROM employee_table ORDER BY salary DESC) AS emp ORDER BY salary ASC;
To display the first record from the EmployeeInfo table, you can write a query as follows:
SELECT * FROM EmployeeInfo WHERE EmpID = (SELECT MIN(EmpID) FROM EmployeeInfo);
To display the last record from the EmployeeInfo table, you can write a query as follows:
SELECT * FROM EmployeeInfo WHERE EmpID = (SELECT MAX(EmpID) FROM EmployeeInfo);
SELECT Email FROM EmployeeInfo WHERE NOT REGEXP_LIKE(Email, ‘[A-Z0-9._%+-]+@[A-Z0-9.-]+.[A-Z]{2,4}’, ‘i’);
SELECT DEPARTMENT, COUNT(EmpID) as 'EmpNo' FROM EmployeeInfo GROUP BY DEPARTMENT HAVING COUNT(EmpD) < 2;
SELECT EmpPosition, SUM(Salary) from EmployeePosition GROUP BY EmpPosition;
SELECT * FROM EmployeeInfo WHERE EmpID <= (SELECT COUNT(EmpID)/2 from EmployeeInfo);
Intermediate scenario-based SQL Interview questions
To retrieve the last five records from a database using a SQL query, you can use the ORDER BY clause combined with LIMIT. Here’s an example query:
SELECT *
FROM your_table
ORDER BY id DESC
LIMIT 5;
Replace your_table_name with the name of your table and your_primary_key_column with the primary key column that defines the order of records. The ORDER BY your_primary_key_column DESC part sorts the records in descending order based on the primary key column, and LIMIT 5 limits the result to the last five records.
Here’s an example SQL query:
SELECT salary
FROM (
SELECT salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM Employee
) AS ranked_salary
WHERE row_num = 10;
In this query:
To get the last record from a table, you can use a query like this:
SELECT * FROM your_table_name ORDER BY your_primary_key_column DESC LIMIT 1;
Replace your_table_name with the name of your table and your_primary_key_column with the primary key column that defines the order of records. The DESC keyword sorts the records in descending order, and LIMIT 1 ensures you only get the last record.
So this brings us to the end of the SQL Query Interview Questions article.
Get more SQL Interview Questions and Answers which will help you ace your job interview. All the best for your interview!
Related Post Oracle SQL Interview Questions
Check out this https://www.edureka.co/microsoft-sql-server-certification-training by Edureka, a trusted online learning company with a network of more than 250,000 satisfied learners spread across the globe. This course trains you on the core concepts & advanced tools and techniques to manage data and administer the MySQL Database. It includes hands-on learning on concepts like MySQL Workbench, MySQL Server, Data Modeling, MySQL Connector, Database Design, MySQL Command line, MySQL Functions, etc. End of the training you will be able to create and administer your own MySQL Database and manage data.
Got a question for us? Please mention it in the comments section of this “SQL Query Interview Questions” article and we will get back to you as soon as possible.
Course Name | Date | Details |
---|---|---|
Microsoft SQL Server Certification Course | Class Starts on 19th October,2024 19th October SAT&SUN (Weekend Batch) | View Details |
Microsoft SQL Server Certification Course | Class Starts on 14th December,2024 14th December SAT&SUN (Weekend Batch) | View Details |
edureka.co
Thank you so much edureka ,it helped me a lot to crack Data Analyst interviews.