i. To get the name and salary of employees whose salary is greater than each and every employee working under managerid 14, you can use a subquery to first get the maximum salary of employees under managerid 14, and then compare this with the salary of all employees. Here is the SQL query:

```sql
SELECT ename, salary
FROM Employee
WHERE salary (SELECT MAX(salary) FROM Employee WHERE mid = 14);
```

ii. To get the name of employees who have not been assigned any department, you can use a LEFT JOIN to join the Employee and Department tables on the manager id, and then filter for employees where the department id is null. Here is the SQL query:

```sql
SELECT E.ename
FROM Employee E
LEFT JOIN Department D ON E.mid = D.mid
WHERE D.did IS NULL;
```

iii. To get the employee names whose location is Kota, you can use a JOIN to join the Employee and Department tables on the manager id, and then filter for employees where the location is Kota. Here is the SQL query:

```sql
SELECT E.ename
FROM Employee E
JOIN Department D ON E.mid = D.mid
WHERE D.location = 'Kota';
```

iv. To get the name of the manager along with the count of employees assigned to him/her in descending order, you can use a GROUP BY clause to group by the manager name, and then order by the count of employees in descending order. Here is the SQL query:

```sql
SELECT M.mname, COUNT(E.eid) as employee_count
FROM Manager M
JOIN Employee E ON M.mid = E.mid
GROUP BY M.mname
ORDER BY employee_count DESC;
```

Question

i. To get the name and salary of employees whose salary is greater than each and every employee working under managerid 14, you can use a subquery to first get the maximum salary of employees under managerid 14, and then compare this with the salary of all employees. Here is the SQL query:

```sql
SELECT ename, salary
FROM Employee
WHERE salary > (SELECT MAX(salary) FROM Employee WHERE mid = 14);
```

ii. To get the name of employees who have not been assigned any department, you can use a LEFT JOIN to join the Employee and Department tables on the manager id, and then filter for employees where the department id is null. Here is the SQL query:

```sql
SELECT E.ename
FROM Employee E
LEFT JOIN Department D ON E.mid = D.mid
WHERE D.did IS NULL;
```

iii. To get the employee names whose location is Kota, you can use a JOIN to join the Employee and Department tables on the manager id, and then filter for employees where the location is Kota. Here is the SQL query:

```sql
SELECT E.ename
FROM Employee E
JOIN Department D ON E.mid = D.mid
WHERE D.location = 'Kota';
```

iv. To get the name of the manager along with the count of employees assigned to him/her in descending order, you can use a GROUP BY clause to group by the manager name, and then order by the count of employees in descending order. Here is the SQL query:

```sql
SELECT M.mname, COUNT(E.eid) as employee_count
FROM Manager M
JOIN Employee E ON M.mid = E.mid
GROUP BY M.mname
ORDER BY employee_count DESC;
```

Knowee AI · Accepted Answer