SQL Query Interview Questions
SQL stands for Structured Query Language. Initially, it was named Structured English Query Language (SEQUEL). It is used in relational database management systems to store and manage data (RDMS).
It is a relational database system with standard language. A user can create, read, update, and delete relational databases and tables with it. SQL is the standard database language used by every RDBMS, including MySQL, Informix, Oracle, MS Access, and SQL Server. SQL allows users to query the database using English-like phrases in a variety of ways.
SQL is also distinct from other computer languages because it specifies what the user wants the machine to perform rather than how it should accomplish it. (In more technical terms, SQL is a declarative rather than procedural language.) There are no IF statements in SQL for testing conditions, and no GOTO, DO, or FOR statements for controlling program flow. SQL statements, on the other hand, indicate how a collection of data should be organized, as well as what data should be accessed or added to the database. The DBMS determines the sequence of actions to complete such tasks.
SQL queries are one of the most frequently asked interview questions. You can expect questions ranging from basic SQL queries to complex SQL queries:
- What is Query in SQL?
- Write an SQL query to report all the duplicate emails. You can return the result table in any order.
- Construct a SQL query to provide each player's first login date. You can return the result table in any order.
- Write an SQL query to report all customers who never order anything. You can return the result table in any order.
- Write an SQL query to report the movies with an odd-numbered ID and a description that is not "boring". Return the result table ordered by rating in descending order.
- Construct a SQL query to display the names and balances of people who have a balance greater than $10,000. The balance of an account is equal to the sum of the amounts of all transactions involving that account. You can return the result table in any order.
- Write a SQL query that detects managers with at least 5 direct reports from the Employee table.
- Construct an SQL query for the following questions.
So, without any further ado, let us look at the most frequently asked SQL Query Interview Questions and Answers which are categorised into the following:
SQL Query Interview Questions for Freshers
1. What is Query in SQL?
A query is a request for information or data from a database table or set of tables. For example, let us assume that we have a database that stores details about books written by various authors. Now, if we want to know how many books have been written by a particular author, then this question can be referred to as a query that we want to do to the database.
Use cases of SQL:
- SQL is an interactive question language. Users write SQL commands into interactive SQL software to extract information and display them on the screen, making it a useful and simple tool for ad hoc database queries.
- SQL is a database programming language. To access the information in a database, programmers incorporate SQL instructions into their utility packages. This method of database access is used by both user-written packages and database software packages
- SQL is a server/client language. SQL allows personal computer programs to interface with database servers that store shared data through a network. Many well-known enterprise-class apps use this client/server design.
- SQL is a distributed database language. SQL is used in distributed database control systems to help spread data across numerous linked computer structures. Every device's DBMS software program uses SQL to communicate with other systems, issuing requests for information access. Learn More
2. Let us consider the following schema:
Here, id is the primary key column for this table. Email represents the email id of the person. For the sake of simplicity, we assume that the emails will not contain uppercase letters. Write an SQL query to report all the duplicate emails. You can return the result table in any order.
Input: Person table:
Explanation: email@example.com is repeated two times.
- Approach 1:
We can first have all the distinct email ids and their respective counts in our result set. For this, we can use the GROUP BY operator to group the tuples by their email id. We will use the COUNT operator to have the total number of a particular email id in the given table. The query for obtaining this resultant set can be written as:
select email, count(email) as email_count from Person group by email;
Now, we query in the above resultant query set to find out all the tuples which have an email id count greater than 1. This can be achieved using the following query:
select email from ( select email, count(email) as email_count from Person group by email ) where email_count > 1;
- Approach 2:
The HAVING clause, which is significantly simpler and more efficient, is a more popular technique to add a condition to a GROUP BY. So, we can first group the tuples by the email ids and then have a condition to check if their count is greater than 1, only then do we include it in our result set. So we may change the solution above to this one.
select email from Person group by email having count(email) > 1;
- Approach 3:
We can use the concept of joins to solve this problem. We will self-join the Person table with the condition that their email ids should be the same and their ids should be different. Having done this, we just need to count the number of tuples in our resultant set with distinct email ids. For this, we use the DISTINCT operator. This can be achieved using the following query:
SELECT DISTINCT p1.email FROM Person p1, Person p2 WHERE p1.email = p2.email and p1.id != p2.id;
3. Let us consider the following schema:
This table's primary key is (playerId, eventDate). The activities of numerous game participants are depicted in this table. Each row indicates a person that logged in and played a particular number of games (perhaps 0) before moving on to another device at a later date. Construct a SQL query to provide each player's first login date. You can return the result table in any order.
Input: Activity table:
The player with playerId 1 has two login event dates in the example above. However, because the first login event date is 2021-04-07, we display it. Similarly, the first login event date for the player with playerId 2 is 2021-06-25, and the first login event date for the player with playerId 3 is 2021-07-03.
- Approach 1:
We can first group the tuples by their player_id. Now, we want the most initial date when the player logged in to the game. For this, we can use the MIN operator and find the initial date on which the player logged in. The query can be written as follows:
select playerId, min(eventDate) as firstLogin from Activity group by playerId
- Approach 2:
We can partition the tuples by the player_id and order them by their event_id such that all the tuples having the same player_id are grouped together. We then number every tuple in each of the groups starting with the number 1. Now, we just have to display the event_date for the tuple having row number 1. For this, we use the ROW_NUMBER operator. The SQL query for it can be written as follows:
SELECT playerId, eventDate AS firstLogin FROM ( SELECT playerId, eventDate, ROW_NUMBER() OVER (PARTITION BY playerId ORDER BY eventDate) AS seq FROM Activity ) AS t WHERE seq = 1
- Approach 3:
We follow a similar kind of approach as used in Approach 2. But instead of using the ROW_NUMBER operator, we can use the FIRST_VALUE operator to find the first event_date. The SQL query for it can be written as follows:
select distinct(playerId), FIRST_VALUE(eventDate) OVER(PARTITION BY playerId ORDER BY eventDate) as firstLogin from Activity;
4. Given the following schema:
The primary key column for this table is id. Each row in the table represents a customer's ID and name.
The primary key column for this table is id. customerId is a foreign key of the ID from the Customers table. The ID of an order and the ID of the customer who placed it are listed in each row of this table. Write an SQL query to report all customers who never order anything. You can return the result table in any order.
Input: Customers table:
Explanation: Here, the customers Sachin and Ram have placed an order having order id 1 and 2 respectively. Thus, the customers Rajat and Ankit have never placed an order. So, we print their names in the result set.
- Approach 1:
In this approach, we first try to find the customers who have ordered at least once. After having found this, we find the customers whose customer Id is not present in the previously obtained result set. This gives us the customers who have not placed a single order yet. The SQL query for it can be written as follows
select customers.name as 'Customers' from customers where customers.id not in ( select customerid from orders );
- Approach 2:
In this approach, we use the concept of JOIN. We will LEFT JOIN the customer table with the order table based on the condition that id of the customer table must be equal to that of the customer id of the order table. Now, in our joined resultant table, we just need to find those customers whose order id is null. The SQL query for this can be written as follows:
select c.name as 'Customers' from Customers c left join Orders o ON (o.customerId = c.id) where o.id is null
Here, we first create aliases of the tables Customers and Orders with the name ‘c’ and ‘o’ respectively. Having done so, we join them with the condition that o.customerId = c.id. At last, we check for the customers whose o.id is null.
5. Given the following schema:
The primary key for this table is id. Each row includes information about a movie's name, genre, and rating. rating is a float with two decimal digits in the range [0, 10]. Write an SQL query to report the movies with an odd-numbered ID and a description that is not "boring". Return the result table ordered by rating in descending order.
Input: Cinema table:
There are three odd-numbered ID movies: 1, 3, and 5. We don't include the movie with ID = 3 in the answer because it's boring. We put the movie with id 5 at the top since it has the highest rating of 9.1.
This question has a bit of ambiguity on purpose. You should ask the interviewer whether we need to check for the description to exactly match “boring” or we need to check if the word “boring” is present in the description. We have provided solutions for both cases.
- Approach 1 (When the description should not be exactly “boring” but can include “boring” as a substring):
In this approach, we use the MOD operator to check whether the id of a movie is odd or not. Now, for all the odd-numbered id movies, we check if its description is not boring. At last, we sort the resultant data according to the descending order of the movie rating. The SQL query for this can be written as follows:
select * from cinema where mod(id, 2) = 1 and description != 'boring' order by rating DESC;
- Approach 2 (When the description should not even contain “boring” as a substring in our resultant answer):
In this approach, we use the LIKE operator to match the description having “boring” as a substring. We then use the NOT operator to eliminate all those results. For the odd-numbered id, we check it similarly as done in the previous approach. Finally, we order the result set according to the descending order of the movie rating. The SQL query for it can be written as follows:
SELECT * FROM Cinema WHERE id % 2 = 1 AND description NOT LIKE '%boring%' ORDER BY rating DESC;
6. Consider the following schema:
The account is the primary key for this table. Each row of this table contains the account number of each user in the bank. There will be no two users having the same name in the table.
trans_id is the primary key for this table. Each row of this table contains all changes made to all accounts. The amount is positive if the user received money and negative if they transferred money. All accounts start with a balance of 0.
Construct a SQL query to display the names and balances of people who have a balance greater than $10,000. The balance of an account is equal to the sum of the amounts of all transactions involving that account. You can return the result table in any order.
Input: Users table:
- Ram's balance is (8000 + 8000 - 3000) = 11000.
- Tim's balance is 4000.
- Shyam's balance is (7000 + 7000 - 4000) = 10000.
- Approach 1:
In this approach, we first create aliases of the given two tables' users and transactions. We can natural join the two tables and then group them by their account number. Next, we use the SUM operator to find the balance of each of the accounts after all the transactions have been processed. The SQL query for this can be written as follows:
SELECT u.name, SUM(t.amount) AS balance FROM Users natural join Transactions t GROUP BY t.account_number HAVING balance> 10000;
7. Given the following schema:
All employees, including their managers, are present at the Employee table. There is an Id for each employee, as well as a column for the manager's Id. Write a SQL query that detects managers with at least 5 direct reports from the Employee table.
In this problem, we first find all the manager ids who have more than 5 employees under them. Next, we find all the employees having the manager id present in the previously obtained manager id set.
The SQL query for this can be written as follows:
SELECT Name FROM Employee WHERE id IN (SELECT ManagerId FROM Employee GROUP BY ManagerId HAVING COUNT(DISTINCT Id) >= 5);
8. Consider the following table schema:
Construct an SQL query to retrieve duplicate records from the Employee table.
Now answer the following questions:
1. Construct an SQL query that retrieves the fname in upper case from the Employee table and uses the ALIAS name as the EmployeeName in the result.
SELECT UPPER(fname) AS EmployeeName FROM Employee;
2. Construct an SQL query to find out how many people work in the "HR" department
SELECT COUNT(*) FROM Employee WHERE department = 'HR';
3. Construct an SQL query to retrieve the first four characters of the ‘lname’ column from the Employee table.
SELECT SUBSTRING(lname, 1, 4) FROM Employee;
4. Construct a new table with data and structure that are copied from the existing table ‘Employee’ by writing a query. The name of the new table should be ‘SampleTable’.
SELECT * INTO SampleTable FROM Employee WHERE 1 = 0
5. Construct an SQL query to find the names of employees whose first names start with "S".
SELECT * FROM Employee WHERE fname LIKE 'S%';
6. Construct an SQL query to count the number of employees grouped by gender whose dateOfBirth is between 01/03/1975 and 31/12/1976.
SELECT COUNT(*), gender FROM Employee WHERE dateOfBirth BETWEEN '01/03/1975 ' AND '31/12/1976' GROUP BY gender;
7. Construct an SQL query to retrieve all employees who are also managers.
SELECT emp.fname, emp.lname, sal.position FROM Employee emp INNER JOIN Salary sal ON emp.id = sal.id AND sal.position IN ('Manager');
8. Construct an SQL query to retrieve the employee count broken down by department and ordered by department count in ascending manner.
SELECT department, COUNT(id) AS DepartmentCount FROM Employee GROUP BY department ORDER BY DepartmentCount ASC;
9. Construct an SQL query to retrieve duplicate records from the Employee table.
SELECT id, fname, department, COUNT(*) as Count FROM Employee GROUP BY id, fname, department HAVING COUNT(*) > 1;
SQL Query Interview Questions for Experienced
1. Consider the following Schema:
Here, id is the primary key column for this table. id represents the unique identity of a tree node and parent_id represents the unique identity of the parent of the current tree node. The id of a node and the id of its parent node in a tree are both listed in each row of this table. There is always a valid tree in the given structure.
Every node in the given tree can be categorized into one of the following types:
1. "Leaf": When the tree node is a leaf node, we label it as “Leaf”
2. "Root": When the tree node is a root node, we label it as “Root”
3. "Inner": When the tree node is an inner node, we label it as “Inner”
Write a SQL query to find and return the type of each of the nodes in the given tree. You can return the result in any order.
Input: Tree Table
- Because node 1’s parent node is null, and it has child nodes 2 and 3, Node 1 is the root node.
- Because node 2 and node 3 have parent node 1 and child nodes 5 and 4 respectively, Node 2 and node 3 are inner nodes.
- Because nodes 4 and 5 have parent nodes but no child nodes, nodes 4, and 5 are leaf nodes.
In this approach, we subdivide our problem of categorizing the type of each of the nodes in the tree. We first find all the root nodes and add them to our resultant set with the type “root”. Then, we find all the leaf nodes and add them to our resultant set with the type “leaf”. Similarly, we find all the inner nodes and add them to our resultant set with the type “inner”. Now let us look at the query for finding each of the node types.
- For root nodes:
SELECT id, 'Root' AS Type FROM tree WHERE parent_id IS NULL
Here, we check if the parent_id of the node is null, then we assign the type of node as ‘Root’ and include it in our result set.
- For leaf nodes:
SELECT id, 'Leaf' AS Type FROM tree WHERE id NOT IN (SELECT DISTINCT parent_id FROM tree WHERE parent_id IS NOT NULL) AND parent_id IS NOT NULL
Here, we first find all the nodes that have a child node. Next, we check if the current node is present in the set of root nodes. If present, it cannot be a leaf node and we eliminate it from our answer set. We also check that the parent_id of the current node is not null. If both the conditions satisfy then we include it in our answer set.
- For inner nodes:
SELECT id, 'Inner' AS Type FROM tree WHERE id IN (SELECT DISTINCT parent_id FROM tree WHERE parent_id IS NOT NULL) AND parent_id IS NOT NULL
Here, we first find all the nodes that have a child node. Next, we check if the current node is present in the set of root nodes. If not present, it cannot be an inner node and we eliminate it from our answer set. We also check that the parent_id of the current node is not null. If both the conditions satisfy then we include it in our answer set.
At last, we combine all three resultant sets using the UNION operator. So, the final SQL query is as follows:
SELECT id, 'Root' AS Type FROM tree WHERE parent_id IS NULL UNION SELECT id, 'Leaf' AS Type FROM tree WHERE id NOT IN (SELECT DISTINCT parent_id FROM tree WHERE parent_id IS NOT NULL) AND parent_id IS NOT NULL UNION SELECT id, 'Inner' AS Type FROM tree WHERE id IN (SELECT DISTINCT parent_id FROM tree WHERE parent_id IS NOT NULL) AND parent_id IS NOT NULL ORDER BY id;
In this approach, we use the control statement CASE. This simplifies our query a lot from the previous approach. We first check if a node falls into the category of “Root”. If the node does not satisfy the conditions of a root node, it implies that the node will either be a “Leaf” node or an “Inner” node. Next, we check if the node falls into the category of “Inner” node. If it is not an “Inner” node, there is only one option left, which is the “Leaf” node.
The SQL query for this approach can be written as follows:
SELECT id AS `Id`, CASE WHEN tree.id = (SELECT aliastree.id FROM tree aliastree WHERE aliastree.parent_id IS NULL) THEN 'Root' WHEN tree.id IN (SELECT aliastree.parent_id FROM tree aliastree) THEN 'Inner' ELSE 'Leaf' END AS Type FROM tree ORDER BY `Id`;
In this approach, we follow a similar logic as discussed in the previous approach. However, we will use the IF operator instead of the CASE operator. The SQL query for this approach can be written as follows:
SELECT aliastree.id, IF(ISNULL(aliastree.parent_id), 'Root', IF(aliastree.id IN (SELECT parent_id FROM tree), 'Inner','Leaf')) Type FROM tree aliastree ORDER BY aliastree.id
2. Consider the following schema:
The table contains a list of students. Every tuple in the table consists of a seat id along with the name of the student. You can assume that the given table is sorted according to the seat id and that the seat ids are in continuous increments. Now, the class teacher wants to swap the seat id for alternate students in order to give them a last-minute surprise before the examination. You need to write a query that swaps alternate students' seat id and returns the result. If the number of students is odd, you can leave the seat id for the last student as it is.
For the same input, the output is:
- Approach 1:
In this approach, first we count the total number of students. Having done so, we consider the case when the seat id is odd but is not equal to the total number of students. In this case, we simply increment the seat id by 1. Next, we consider the case when the seat id is odd but is equal to the total number of students. In this case, the seat id remains the same. At last, we consider the case when the seat id is even. In this case, we decrement the seat id by 1.
The SQL query for this approach can be written as follows:
SELECT CASE WHEN MOD(id, 2) != 0 AND counts != id THEN id + 1 -- for odd ids WHEN MOD(id, 2) != 0 AND counts = id THEN id -- special case for last seat ELSE id - 1 -- For even ids END as id, student FROM seat, (SELECT COUNT(*) as counts FROM seat) AS seat_count ORDER by id;
- Approach 2:
In this approach, we use the ROW_NUMBER operator. We increment the id for the odd-numbered ids by 1 and decrement the even-numbered ids by 1. We then sort the tuples, according to the id values. Next, we assign the row number as the id for the sorted tuples. The SQL query for this approach can be written as follows:
select row_number() over (order by (if(id%2=1,id+1,id-1)) ) as id, student from seat;
3. Given the following schema:
id is the primary key column for this table. departmentId is a foreign key of the ID from the Department table. Each row of this table indicates the ID, name, and salary of an employee. It also contains the ID of their department.
id is the primary key column for this table. Each row of this table indicates the ID of a department and its name. The executives of an organization are interested in seeing who earns the most money in each department. A high earner in a department is someone who earns one of the department's top three unique salaries.
Construct a SQL query to identify the high-earning employees in each department. You can return the result table in any order.
Input: Employee table:
- Kim has the greatest unique income in the Marketing department - Ram and Saket have the second-highest unique salary.
- Will has the third-highest unique compensation.
In the HR department:
- Divya has the greatest unique income.
- Tim earns the second-highest salary.
- Because there are only two employees, there is no third-highest compensation.
In this approach, let us first assume that all the employees are from the same department. So let us first figure out how we can find the top 3 high-earner employees. This can be done by the following SQL query:
select emp1.Name as 'Employee', emp1.Salary from Employee emp1 where 3 > ( select count(distinct emp2.Salary) from Employee emp2 where emp2.Salary > emp1.Salary );
Here, we have created two aliases for the Employee table. For every tuple of the emp1 alias, we compare it with all the distinct salaries to find out how many salaries are less than it. If the number is less than 3, it falls into our answer set.
Next, we need to join the Employee table with the Department table in order to obtain the high-earner employees department-wise. For this, we run the following SQL command:
SELECT d.Name AS 'Department', e1.Name AS 'Employee', e1.Salary FROM Employee e1 JOIN Department d ON e1.DepartmentId = d.Id WHERE 3 > (SELECT COUNT(DISTINCT e2.Salary) FROM Employee e2 WHERE e2.Salary > e1.Salary AND e1.DepartmentId = e2.DepartmentId );
Here, we join the Employee table and the Department table based on the department ids in both tables. Also, while finding out the high-earner employees for a specific department, we compare the department ids of the employees as well to ensure that they belong to the same department.
In this approach, we use the concept of the DENSE_RANK function in SQL. We use the DENSE_RANK function and not the RANK function since we do not want the ranking number to be skipped. The SQL query using this approach can be written as follows:
SELECT Final.Department, Final.Employee, Final.Salary FROM (SELECT D.name AS Department, E.name AS Employee, E.salary AS Salary, DENSE_RANK() OVER (PARTITION BY D.name ORDER BY E.salary DESC) Rank FROM Employee E, Department D WHERE E.departmentId = D.id) Final WHERE Final.Rank < 4;
Here, we first run a subquery where we partition the tuples by their department and rank them according to the decreasing order of the salaries of the employees. Next, we select those tuples from this set, whose rank is less than 4.
4. Given the following schema:
date_visited is the primary key for this table. The visit date, the stadium visit ID, and the total number of visitors are listed in each row of this table. No two rows will share the same visit date, and the dates get older as the id gets bigger. Construct a SQL query to display records that have three or more rows of consecutive ids and a total number of people higher than or equal to 100. Return the result table in ascending order by visit date.
Input: Stadium table:
The four rows with ids 5, 6, 7, and 8 have consecutive ids and each of them has >= 100 people attended. Note that row 8 was included even though the date_visited was not the next day after row 7.
The rows with ids 2 and 3 are not included because we need at least three consecutive ids.
- Approach 1:
In this approach, we first create three aliases of the given table and cross-join all of them. We filter the tuples such that the number of people in each of the alias’ should be greater than or equal to 100.
The query for this would be
select distinct t1.* from stadium t1, stadium t2, stadium t3 where t1.count_people >= 100 and t2.count_people >= 100 and t3.count_people >= 100;
Now, we have to check for the condition of consecutive 3 tuples. For this, we compare the ids of the three aliases to check if they form a possible triplet with consecutive ids. We do this by the following query:
select t1.* from stadium t1, stadium t2, stadium t3 where t1.count_people >= 100 and t2.count_people >= 100 and t3.count_people >= 100 and ( (t1.id - t2.id = 1 and t1.id - t3.id = 2 and t2.id - t3.id =1) or (t2.id - t1.id = 1 and t2.id - t3.id = 2 and t1.id - t3.id =1) or (t3.id - t2.id = 1 and t2.id - t1.id =1 and t3.id - t1.id = 2) );
The above query may contain duplicate triplets. So we remove them by using the DISTINCT operator. The final query becomes as follows:
select distinct t1.* from stadium t1, stadium t2, stadium t3 where t1.count_people >= 100 and t2.count_people >= 100 and t3.count_people >= 100 and ( (t1.id - t2.id = 1 and t1.id - t3.id = 2 and t2.id - t3.id =1) or (t2.id - t1.id = 1 and t2.id - t3.id = 2 and t1.id - t3.id =1) or (t3.id - t2.id = 1 and t2.id - t1.id =1 and t3.id - t1.id = 2) ) order by t1.id;
- Approach 2:
In this approach, we first filter out all the tuples where the number of people is greater than or equal to 100. Next, for every tuple, we check, if there exist 2 other tuples with ids such that the three ids when grouped together form a consecutive triplet. The SQL query for this approach can be written as follows:
with cte as (select * from stadium where count_people >= 100) select cte.id, cte.date_visited, cte.count_people from cte where ((cte.id + 1) in (select id from cte) and (cte.id + 2) in (select id from cte)) or ((cte.id - 1) in (select id from cte) and (cte.id - 2) in (select id from cte)) or ((cte.id + 1) in (select id from cte) and (cte.id - 1) in (select id from cte))
5. Given the following schema:
Here, id is the id of the employee. company is the name of the company he/she is working in. salary is the salary of the employee Construct a SQL query to determine each company's median salary. If you can solve it without utilising any built-in SQL functions, you'll get bonus points.
- Approach 1:
In this approach, we have a subquery where we partition the tuples according to the company name and rank the tuples in the increasing order of salary and id. We also find the count of the total number of tuples in each company and then divide it by 2 in order to find the median tuple. After we have this result, we run an outer query to fetch the median salary and the employee id for each of the companies.
The SQL query for this can be written as follows:
select table.id, table.company, table.salary from (select id, company, salary, dense_rank() over (partition by company order by salary, id) as Ranking, count(1) over (partition by company) / 2.0 as EmployeeCount from Employee ) table where Ranking between EmployeeCount and EmployeeCount + 1;
SQL Query MCQ Questions
Which of the following are TCL commands?
Which of the following claims about SQL queries is TRUE?
P: Even if there is no GROUP BY clause in a SQL query, it can have a HAVING clause.
Q: If a SQL query has a GROUP BY clause, it can only have a HAVING clause.
R: The SELECT clause must include all attributes used in the GROUP BY clause.
S: The SELECT clause does not have to include all of the attributes used in the GROUP BY clause.
Following is a database table having the name Loan_Records.Borrower Bank Manager LoanAmountManoj Chatterjee 9000.00Divya Krishna 6000.00Rajat Chatterjee 8000.00
What is the result of the SQL query below?
SELECT Count(*) FROM ( ( SELECT Borrower, BankManager FROM Loan_Records) AS table1 NATURAL JOIN ( SELECT BankManager, LoanAmount FROM Loan_Records) AS table2 );
Consider the table employee(employeeId, name, department, salary) and the two queries Q1 , Q2 below. Assuming that department 5 has more than one employee, and we want to find the employees who get higher salaries than anyone in department 5, which one of the statements is TRUE for any arbitrary employee table?
Select emp1.employeeId From employee emp1 Where not exists (Select * From employee emp2 where emp2.department = “5” and emp2.salary >= emp1.salary)
Select emp1.employeeId From employee emp1 Where emp1.salary > Any (Select distinct salary From employee emp2 Where emp2.department = “5”)
Given the following statements:
P: A check assertion in SQL can always be used to replace a foreign key declaration.
Q: Assume that there is a table “Sample” with the attributes (p, q, r) where the primary key is (p, q). Given the table definition for the table “Sample”, the following table definition also holds true and valid.
CREATE TABLE Test ( p INTEGER, t INTEGER, u INTEGER, PRIMARY KEY (t), FOREIGN KEY (p) references Sample)
Given the following schema:
employees(employeeId, firstName, lastName, hireDate, departmentId, salary)
departments(departmentId, departmentName, managerId, locationId)
For location ID 100, you are required to display the first names and hire dates of all recent hires in their respective departments. You run the following query for it:
SQL> SELECT firstName, hireDate FROM employees WHERE (departmentId, hireDate) IN ( SELECT departmentId, MAX(hireDate) FROM employees JOIN departments USING(departmentId) WHERE locationId = 100 GROUP BY departmentId);
Which of the following options is the correct outcome of the above query?
SQL allows tuples in relations, and correspondingly defines the multiplicity of tuples in the result of joins. Which one of the following queries always gives the same answer as the nested query shown below:
select * from R where a in (select S.a from S)
Consider the relation account (customer, balance), in which the customer is the primary key and no null values exist. Customers should be ranked in order of decreasing balance. The consumer with the highest balance is placed first. Ties are not broken, but ranks are skipped: if two customers have the same balance, they both get rank 1, and rank 2 is not issued.
select A.customer, count(B.customer) from account A, account B where A.balance <=B.balance group by A.customer
select A.customer, 1+count(B.customer) from account A, account B where A.balance < B.balance group by A.customer
Consider these statements about Query1 and Query2.
1. Query1 will produce the same row set as Query2 for some but not all databases.
2. Both Query1 and Query2 are correct implementations of the specification
3. Query1 is a correct implementation of the specification but Query2 is not
4. Neither Query1 nor Query2 is a correct implementation of the specification
5. Assigning rank with a pure relational query takes less time than scanning in decreasing balance order assigning ranks using ODBC.
Which of the following option is correct?
Consider the two relations: “enrolled(student, course)” and “paid(student, amount)”. In the relation enrolled(student, course), the primary key is (student, course) whereas in the relation “paid(student, amount)”, the primary key is a student. You can assume that there are no null values and no foreign keys or integrity constraints. Given the following four queries:
select student from enrolled where student in (select student from paid)
select student from paid where student in (select student from enrolled)
select E.student from enrolled E, paid P where E.student = P.student
select student from paid where exists (select * from enrolled where enrolled.student = paid.student)
Which of the following statements is the most accurate?
The relation book (title, price) contains the titles and prices of different books. Assuming that no two books have the same price, what does the following SQL query list?
select title from book as B where (select count(*) from book as T where T.price > B.price) < 5
The employee information in a company is stored in the relation:
Employee (name, sex, salary, deptName)
Consider the following SQL query
select deptName from Employee where sex = 'M' group by deptName having avg (salary) > (select avg (salary) from Employee)
It returns the names of the departments in which