Scribd
Upload
8 views6 pages

Cognizant SQL Cheatsheet

This document is a cheat sheet for Cognizant SQL interviews, listing the top 30 frequently asked SQL queries. It includes examples for various topics such as salary ranking, account summaries, and customer orders. The document emphasizes the importance of practicing these queries to prepare effectively for interviews.

Uploaded by

saiakshaykv
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
8 views6 pages

Cognizant SQL Cheatsheet

This document is a cheat sheet for Cognizant SQL interviews, listing the top 30 frequently asked SQL queries. It includes examples for various topics such as salary ranking, account summaries, and customer orders. The document emphasizes the importance of practicing these queries to prepare effectively for interviews.

Uploaded by

saiakshaykv
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 6

Cognizant SQL Interview Cheat Sheet (Top 30 Questions)

This cheat sheet includes top 30 frequently asked SQL queries for Cognizant fresher interviews. Focus on
these to maximize your preparation.

1. Second Highest Salary

SELECT MAX(salary) AS SecondHighestSalary


FROM Employee
WHERE salary < (SELECT MAX(salary) FROM Employee);

2. Nth Highest Salary

SELECT DISTINCT salary AS NthHighestSalary


FROM Employee
ORDER BY salary DESC
LIMIT N-1, 1;

3. Rank Scores

SELECT score,
DENSE_RANK() OVER (ORDER BY score DESC) AS rank
FROM Scores;

4. Bank Account Summary I

WITH UpdatedUsers AS (
SELECT u.user_id, u.user_name, u.credit + COALESCE(SUM(CASE WHEN u.user_id =
t.paid_to THEN t.amount WHEN u.user_id = t.paid_by THEN -t.amount ELSE 0 END),
0) AS current_credit
FROM Users u
LEFT JOIN Transaction t ON u.user_id = t.paid_to OR u.user_id = t.paid_by
GROUP BY u.user_id, u.user_name, u.credit
)
SELECT user_id, user_name, current_credit AS credit, CASE WHEN current_credit <
0 THEN 'Yes' ELSE 'No' END AS credit_limit_breached
FROM UpdatedUsers;

1
5. Bank Account Summary II

SELECT u.name, SUM(t.amount) AS balance


FROM Users u
JOIN Transactions t ON u.account = t.account
GROUP BY u.name
HAVING SUM(t.amount) > 10000;

6. Employees Earning More Than Their Manager

SELECT e.name
FROM Employee e
JOIN Employee m ON e.managerId = m.id
WHERE e.salary > m.salary;

7. Duplicate Emails

SELECT email
FROM Person
GROUP BY email
HAVING COUNT(email) > 1;

8. Customers Who Never Order

SELECT c.name
FROM Customers c
LEFT JOIN Orders o ON c.id = o.customerId
WHERE o.id IS NULL;

9. Department Highest Salary

SELECT department_id, MAX(salary) AS highest_salary


FROM Employee
GROUP BY department_id;

2
10. Employees Earning the Same Salary

SELECT salary, GROUP_CONCAT(name) AS employees


FROM Employee
GROUP BY salary
HAVING COUNT(*) > 1;

11. Find Total Time Spent by Each Employee

SELECT event_day AS day, emp_id, SUM(out_time - in_time) AS total_time


FROM Employees
GROUP BY event_day, emp_id;

12. Valid Emails

SELECT user_id, name, mail


FROM Users
WHERE mail REGEXP '^[A-Za-z][A-Za-z0-9_.-]*@leetcode\.com$';

13. Rising Temperature

SELECT w1.RecordDate
FROM Weather w1
JOIN Weather w2 ON DATEDIFF(w1.RecordDate, w2.RecordDate) = 1
WHERE w1.Temperature > w2.Temperature;

14. Customers Who Bought Products

SELECT DISTINCT c.name


FROM Customers c
JOIN Orders o ON c.id = o.customerId;

15. Employees with Missing Info

SELECT id, name


FROM Employee
WHERE email IS NULL;

3
16. Combine Two Tables

SELECT c.customer_id, c.name, o.amount


FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id;

17. Delete Duplicate Emails

DELETE e1 FROM Employee e1


INNER JOIN Employee e2
WHERE e1.id > e2.id AND e1.email = e2.email;

18. Rising Salary

SELECT e1.name
FROM Employee e1
JOIN Employee e2 ON e1.id = e2.id-1
WHERE e1.salary > e2.salary;

19. Consecutive Numbers

SELECT num
FROM Logs l1
WHERE NOT EXISTS (
SELECT 1 FROM Logs l2
WHERE l2.num = l1.num - 1
);

20. Unpopular Products

SELECT name
FROM Product p
LEFT JOIN Orders o ON p.id = o.product_id
GROUP BY p.name
HAVING SUM(o.amount) IS NULL;

4
21. Customer Total Orders

SELECT c.customer_id, SUM(o.amount) AS total_order


FROM Customers c
JOIN Orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id;

22. Highest Salary Employee

SELECT name
FROM Employee
WHERE salary = (SELECT MAX(salary) FROM Employee);

23. Employees Without Manager

SELECT name
FROM Employee
WHERE managerId IS NULL;

24. Employees With Bonus

SELECT employee_id, salary * 0.1 AS bonus


FROM Employee
WHERE salary < 50000;

25. Total Sales Per Product

SELECT p.name, SUM(o.amount) AS total_sales


FROM Product p
JOIN Orders o ON p.id = o.product_id
GROUP BY p.name;

26. Customers Who Bought All Products

SELECT customer_id
FROM Orders
GROUP BY customer_id
HAVING COUNT(DISTINCT product_id) = (SELECT COUNT(*) FROM Product);

5
27. Employees Joining 2023

SELECT name
FROM Employee
WHERE YEAR(join_date) = 2023;

28. Salary Update

UPDATE Employee
SET salary = salary * 1.1
WHERE salary < 50000;

29. Employees With Highest Salary Per Department

SELECT e1.department_id, e1.name, e1.salary


FROM Employee e1
WHERE salary = (SELECT MAX(salary) FROM Employee e2 WHERE e2.department_id =
e1.department_id);

30. Active Customers Last Month

SELECT DISTINCT customer_id


FROM Orders
WHERE order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 1 MONTH);

💡 Tips: - Focus on GROUP BY, HAVING, JOINs, Window Functions (RANK, DENSE_RANK), CASE, NULL
handling, subqueries. - Practice these 30 queries; ~80% of Cognizant SQL questions are variations of these.
- Optimize queries to avoid TLE for large datasets.

You might also like