36. What is an SQL View?
Answer:
An SQL View is a virtual table based on the result of a SQL query. It doesn’t store actual data but derives data from one or more base tables. Views are used to simplify complex queries, enhance security by restricting data access, and provide a layer of abstraction.
Example:
sql
CREATE VIEW EmployeeDetails AS
SELECT Name, Department, Salary
FROM Employees
WHERE Active = 1;
37. What is a Cartesian Product in SQL?
Answer:
A Cartesian Product or Cross Join occurs when every row in one table is paired with every row in another table. This happens when there’s no WHERE clause or join condition specified.
Example:
sql
SELECT * FROM Products, Categories;
If Products has 10 rows and Categories has 5 rows, the result will be 10 x 5 = 50 rows.
38. What is the outcome of a SQL query that joins two tables without a WHERE clause?
Answer:
When you join two tables without a WHERE clause, SQL performs a Cross Join, resulting in a Cartesian product. Every row from the first table gets matched with every row from the second table.
Use Case: Useful in generating test data but dangerous with large datasets due to exponential row increase.
39. What does the below SQL query return?
Query Explanation:
If the query filters student records with grades ‘C’, it will return all course IDs where a student received a ‘C’.
Example:
sql
SELECT course_id
FROM enrolled
WHERE grade = ‘C’;
40. What is the output of a SQL query with WHERE clause after HAVING?
Answer:
In SQL, the correct order of execution is:
- FROM
- WHERE
- GROUP BY
- HAVING
- SELECT
If a query uses HAVING before or without GROUP BY, it will throw an error.
Incorrect Query:
sql
SELECT Company, AVG(Salary)
FROM AV1
HAVING AVG(Salary) > 1200
GROUP BY Company
WHERE Salary > 1000;
This will throw a syntax error because WHERE must come before GROUP BY.
41. What is the output when joining ENROLLED and STUDENT tables and filtering for grades?
Answer:
The query joins ENROLLED and STUDENT tables and filters those who took course 15-415 and got grade ‘A’ or ‘B’. If no such data exists in the given tables, the output is zero records.
42. SQL Query to Find Students Who Have Taken More Than One Course
Answer:
To find students enrolled in more than one course:
sql
SELECT student_id
FROM enrolled
GROUP BY student_id
HAVING COUNT(DISTINCT course_id) > 1;
This query groups by student_id and filters those with more than one distinct course.
43. What happens with ON DELETE CASCADE when (2,4) is deleted?
Answer:
If table A has a foreign key relationship with table C and ON DELETE CASCADE is used:
- Deleting row (2,4) in C leads to deletion of rows where C = 2 → e.g., (5,2), (7,2)
- Then rows with primary keys 5 and 7 in A are deleted
- This can cascade to more deletions like (9,5)
44. What does a NATURAL JOIN between two subqueries return?
Answer:
This query performs a NATURAL JOIN on Bank_Manager, returning the count of matched rows between:
- (Borrower, Bank_Manager)
- (Bank_Manager, Loan_Amount)
Sample Output: Count of records where Bank_Manager matches in both subqueries.
45. Why does the following query throw an error?
Error Query:
sql
SELECT Company, AVG(Salary)
FROM AV1
HAVING AVG(Salary) > 1200
GROUP BY Company
WHERE Salary > 1000;
Reason: WHERE must come before GROUP BY. SQL follows strict query structure.
Corrected Query:
sql
SELECT Company, AVG(Salary)
FROM AV1
WHERE Salary > 1000
GROUP BY Company
HAVING AVG(Salary) > 1200;
46. SQL Query to Find Second Highest Salary
Answer:
Using Subquery:
sql
SELECT MAX(Salary) AS SecondHighest
FROM Employees
WHERE Salary < (SELECT MAX(Salary) FROM Employees);
This excludes the maximum salary and returns the next highest value.
47. SQL Query to Find Max Salary From Each Department
Answer:
sql
SELECT D.DepartmentName, MAX(E.Salary) AS MaxSalary
FROM Employee E
RIGHT JOIN Department D ON E.DeptID = D.DeptID
GROUP BY D.DepartmentName;
Note: Using RIGHT JOIN ensures even departments with no employees are shown.
48. SQL Query to Display Current Date
Answer:
sql
SELECT GETDATE(); — For SQL Server
SELECT CURRENT_DATE; — For MySQL/PostgreSQL
SEO Focus Keywords: SQL current date, get system date in SQL
49. SQL Query to Get Employees Born Between Two Dates
Answer:
sql
SELECT DISTINCT Name
FROM Employees
WHERE DOB BETWEEN ‘1960-01-01’ AND ‘1975-12-31’;
This filters employees born between 1 Jan 1960 and 31 Dec 1975.
50. SQL Query to Find Employees With Salary ≥ 10000
Answer:
sql
SELECT *
FROM Employees
WHERE Salary >= 10000;
This returns all employee records meeting the salary condition.
51. How to Find and Delete Duplicate Rows in SQL?
Question:
Write an SQL query to find duplicate rows in a database and then write a query to delete them.
Answer:
To find duplicates, we can use GROUP BY and HAVING clauses. Assume we want to detect duplicate rows in a table named Employees based on the Name and Email columns:
sql
SELECT Name, Email, COUNT(*)
FROM Employees
GROUP BY Name, Email
HAVING COUNT(*) > 1;
To delete duplicates while keeping only one record:
sql
DELETE FROM Employees
WHERE id NOT IN (
SELECT MIN(id)
FROM Employees
GROUP BY Name, Email
);
🔹 52. How to Find Employees Who Are Also Managers?
Question:
How do you find all employees who are also managers using a self-join?
Answer:
Use a self join to join the employee table with itself:
sql
SELECT e1.EmpName AS Employee, e2.EmpName AS Manager
FROM Employee e1
JOIN Employee e2 ON e1.mgr_id = e2.EmpID;
If you want to include employees without managers (e.g., CEO):
sql
SELECT e1.EmpName AS Employee, e2.EmpName AS Manager
FROM Employee e1
LEFT JOIN Employee e2 ON e1.mgr_id = e2.EmpID;
🔹 53. SQL Query to Calculate Cancellation Rate Between Dates
Question:
Given a Trips and Users table, write a SQL query to find the cancellation rate for unbanned users between Oct 1 and Oct 3, 2013.
Answer:
sql
SELECT result.Request_at AS Day,
ROUND(
SUM(CASE WHEN result.Status = ‘completed’ THEN 0 ELSE 1 END) * 1.0 / COUNT(*),
2
) AS “Cancellation Rate”
FROM (
SELECT Driver_Id, Status, Request_at
FROM Trips
JOIN Users ON Trips.Client_Id = Users.Users_Id
WHERE Users.Banned = ‘NO’
) result
JOIN Users ON result.Driver_Id = Users.Users_Id
WHERE Users.Banned = ‘NO’
AND result.Request_at BETWEEN ‘2013-10-01’ AND ‘2013-10-03’
GROUP BY result.Request_at;
🔹 54. How to Find Duplicate Emails in SQL?
Question:
Write a SQL query to find duplicate emails from a Person table.
Answer:
sql
SELECT Email
FROM Person
GROUP BY Email
HAVING COUNT(*) > 1;
This will return all emails that occur more than once.
🔹 55. SQL Query to Compare Temperatures Between Days
Question:
Write a query to find dates where the temperature was higher than the previous day.
Answer:
sql
SELECT w1.Id
FROM Weather w1
JOIN Weather w2 ON DATEDIFF(w1.Date, w2.Date) = 1
WHERE w1.Temperature > w2.Temperature;
⚠️ Ensure DATEDIFF is supported by your DBMS (MySQL, SQL Server, etc.).
The Next 15 Questions -II: SQL
