Day-8 14 Frequently Asked SQL Query Interview Questions
Tags: beginners, programming, sql, interview
Published: 2024-03-04 16:49:41

## Hey, fellow code adventurers! Get ready to hop on the SQL, I am very excited to move to the next step,

![Night Coder](https://user-images.githubusercontent.com/74038190/212748842-9fcbad5b-6173-4175-8a61-521f3dbb7514.gif)
<hr>
Today's Agenda- 
## 14 Frequently Asked SQL Query Interview Questions 
<hr>


**Question 1: SQL Query to find the second highest salary of Employee**

*Answer:*
To find the second highest salary in SQL, you can use the following query with a Subquery:

```sql
SELECT MAX(Salary) 
FROM Employee 
WHERE Salary NOT IN (SELECT MAX(Salary) FROM Employee);
```

For more approaches, check out "How to find the second highest salary in SQL."

---

**Question 2: SQL Query to find Max Salary from each department**

*Answer:*
To find the maximum salary for each department, you can use the GROUP BY clause. If you need department names, consider joining the Employee table with the Department table:

```sql
SELECT DeptName, MAX(Salary) 
FROM Employee e RIGHT JOIN Department d ON e.DeptId = d.DeptID 
GROUP BY DeptName;
```

Remember to understand joins; a SQL Bootcamp course can be beneficial.

---

**Question 3: Write SQL Query to display the current date?**

*Answer:*
You can use the `GetDate()` function to display the current timestamp in SQL:

```sql
SELECT GetDate();
```

---

**Question 4: Write an SQL Query to check whether the date passed to Query is the date of the given format or not?**

*Answer:*
Use the `ISDATE()` function to check if the provided value is a date in the specified format:

```sql
SELECT ISDATE('1/08/13') AS "MM/DD/YY";
```

---

**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:*
Use the `BETWEEN` clause to get distinct employee names with DOB in the specified range:

```sql
SELECT DISTINCT EmpName 
FROM Employees 
WHERE DOB BETWEEN '01/01/1960' AND '31/12/1975';
```

---

**Question 6: Write an SQL Query to find the number of employees according to gender whose DOB is between 01/01/1960 to 31/12/1975.**

*Answer:*
Count the number of employees based on gender within the specified DOB range:

```sql
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 to or greater than 10000.**

*Answer:*
Select employees with a salary equal to or greater than 10000:

```sql
SELECT EmpName FROM Employees WHERE Salary >= 10000;
```

---

**Question 8: Write an SQL Query to find the name of an employee whose name starts with ‘M’.**

*Answer:*
Retrieve employees whose names start with 'M':

```sql
SELECT * FROM Employees WHERE EmpName LIKE 'M%';
```

---

**Question 9: Find all Employee records containing the word "Joe," regardless of the case.**

*Answer:*
Search for employee records with the word "Joe" regardless of case:

```sql
SELECT * FROM Employees WHERE UPPER(EmpName) LIKE '%JOE%';
```

---

**Question 10: Write an SQL Query to find the year from date.**

*Answer:*
Get the year from the current date in SQL Server:

```sql
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:*
To find duplicates:

```sql
SELECT * FROM emp a 
WHERE rowid = (SELECT MAX(rowid) FROM EMP b WHERE a.empno = b.empno);
```

To delete duplicates:

```sql
DELETE FROM emp a 
WHERE rowid != (SELECT MAX(rowid) FROM emp b WHERE a.empno = b.empno);
```

---

**Question 12: Find all students whose marks are greater than average marks.**

*Answer:*
Use a subquery to find students with marks above the average:

```sql
SELECT student, marks 
FROM table 
WHERE marks > (SELECT AVG(marks) FROM table);
```

---

**Question 13: How do you find all employees who are also managers?**

*Answer:*
Utilize a self-join to identify employees and their respective managers:

```sql
SELECT e.name, m.name 
FROM Employee e, Employee m 
WHERE e.mgr_id = m.emp_id;
```

---

**Question 14: Will an index be used if only two out of three columns are provided in the WHERE clause of a select query?**

*Answer:*
If the provided columns are part of a composite index, the index may still be used for the operation.
<hr>
The next blog will continue this for MongoDb. Stay connected. Please, visit the [github](https://github.com/Pranjal-sharma-SDE/AI_Mind_Hub).

Drop by our [Telegram Channel](https://t.me/+J2qk3bDFR-piZmU1) and let the adventure begin! See you there, Data Explorer! 🌐🚀