SQL Subqueries

Front

What SQL query retrieves employees working in companies located in Hyderabad?

Back

SELECT ename FROM employee e WHERE EXISTS (SELECT 1 FROM company c JOIN city ct ON c.lid = ct.lid WHERE c.cid = e.cid AND ct.lname = 'Hyderabad');

Front

How can you find companies that have at least one employee?

Back

SELECT cname FROM company c WHERE EXISTS (SELECT 1 FROM employee e WHERE e.cid = c.cid);

Front

What is the SQL query to get employees working for companies located in cities with more than one company?

Back

SELECT ename FROM employee e WHERE EXISTS (SELECT 1 FROM company c1 WHERE c1.cid = e.cid AND EXISTS (SELECT 1 FROM company c2 WHERE c1.lid = c2.lid AND c1.cid <> c2.cid));

Front

How do you find cities that have employees working in any company located there?

Back

SELECT lname FROM city ct WHERE EXISTS (SELECT 1 FROM company c JOIN employee e ON c.cid = e.cid WHERE c.lid = ct.lid);

Front

What query retrieves employees not assigned to any company?

Back

SELECT ename FROM employee e WHERE NOT EXISTS (SELECT 1 FROM company c WHERE c.cid = e.cid);

Front

How can you find companies that don’t have any employees?

Back

SELECT cname FROM company c WHERE NOT EXISTS (SELECT 1 FROM employee e WHERE e.cid = c.cid);

Front

What is the SQL query to get cities with no companies?

Back

SELECT lname FROM city ct WHERE NOT EXISTS (SELECT 1 FROM company c WHERE c.lid = ct.lid);

Front

How do you find employees not working in companies located in Bangalore?

Back

SELECT ename FROM employee e WHERE NOT EXISTS (SELECT 1 FROM company c JOIN city ct ON c.lid = ct.lid WHERE c.cid = e.cid AND ct.lname = 'Bangalore');

Front

What query retrieves employees working in companies located in Hyderabad or Bangalore?

Back

SELECT ename FROM employee WHERE cid IN (SELECT cid FROM company WHERE lid IN (SELECT lid FROM city WHERE lname IN ('Hyderabad', 'Bangalore')));

Front

How can you find employees in companies that also have someone with designation = 'Manager'?

Back

SELECT ename FROM employee WHERE cid IN (SELECT cid FROM employee WHERE designation = 'Manager');