Database miscellaneous
- Consider the table employee (empId, 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 salary than anyone in the department 5, which one of the statements is true for any arbitrary employee table?
Q1 : Select e.empId
From employee e
Where not exists
(Select * From employee s where s.department = “5” and s.salary>=e.salary)
Q2 = Select e.empId
From employee e
Where e.salary > Any
(Select distinct salary from employee s Where s.department = "5")
-
View Hint View Answer Discuss in Forum
As per given,
employee (empId, name, department, salary)
This suggests that an employees is to be denoted using the employee Id, name, department, salary
Query1 It selects an employee but does not compute the result as after the ‘where not exists’, it does not have statement to produce the result.
Query2 It selects an employee who gets higher salary than anyone in the department 5. Therefore, the query2 is correct.Correct Option: B
As per given,
employee (empId, name, department, salary)
This suggests that an employees is to be denoted using the employee Id, name, department, salary
Query1 It selects an employee but does not compute the result as after the ‘where not exists’, it does not have statement to produce the result.
Query2 It selects an employee who gets higher salary than anyone in the department 5. Therefore, the query2 is correct.
Direction: Consider the following relational schemas :
Suppliers (sid : integer, sname : string, city : string, street : string)
Parts (pid : integer, pname : string, color : string)
Catalog (sid : integer, pid : integer, cost : real)
- Assume that, in the suppliers relation above, each supplier and each street within a city has a unique name, and (sname, city) forms a candidate key. No other functional dependencies are implied other than those implied by primary and candidate keys. Which one of the following is true about the above schema?
-
View Hint View Answer Discuss in Forum
As given that each supplier and each street within a city has a unique name, it means (same ,city) and (street ,city) combination is unique. So these will be candidate keys. There is no other FD other than CK determining attributes. In all FD, LHS will be SK.
Example:
Hence 2 candidate Keys are possible
(1) (Sname, city) ⇒ (QR)
(2) (Street, city) ⇒ (SR)
⇒ The relation suppcier is in BCNF So the relation should be in BCNF.Correct Option: A
As given that each supplier and each street within a city has a unique name, it means (same ,city) and (street ,city) combination is unique. So these will be candidate keys. There is no other FD other than CK determining attributes. In all FD, LHS will be SK.
Example:
Hence 2 candidate Keys are possible
(1) (Sname, city) ⇒ (QR)
(2) (Street, city) ⇒ (SR)
⇒ The relation suppcier is in BCNF So the relation should be in BCNF.
- Consider the following relational query on the above database :
SELECT S.sname
FROM Suppliers S
WHERE S.sid NOT IN (SELECT C. sid
FROM Catalog C
WHERE C.pid NOT IN (SELECT P.pid
FROM Parts P
WHERE P.color < > ‘blue’))
Assume that relations corresponding to the above schema are not empty. Which one of the following is the correct interpretation of the above query?
-
View Hint View Answer Discuss in Forum
S name is selected form Supplier S. Now this S.sid should not be in C.sid of Catalog C.
Therefore, it will find the names of all suppliers who have supplied a non-blue part.Correct Option: A
S name is selected form Supplier S. Now this S.sid should not be in C.sid of Catalog C.
Therefore, it will find the names of all suppliers who have supplied a non-blue part.
- A relational schema for a train reservation database is given below.
Passenger (pid, pname, age)
Reservation (pid, class, tid)
Table: Passenger
What pids are returned by the following SQL query for the above instance of the tables ?
SELECT pid
FROM Reservation
WHERE class = AC AND
EXISTS (SELECT *
FROM Passenger
WHERE age > 65 AND
Passenger.pid = Reservation.pid)
-
View Hint View Answer Discuss in Forum
The solution is best understood in steps
1. select the process Id from reservation whose class is AC the condition is satisfied by 0, 1, 5, 3
2. select a passenger whose age > 65 and the pId of passenger id = pId of reservation id passenger whose ages > 65 are those of pId 1, 2, 3
and the pId common to reservation is 1 and 3.
Correct Option: C
The solution is best understood in steps
1. select the process Id from reservation whose class is AC the condition is satisfied by 0, 1, 5, 3
2. select a passenger whose age > 65 and the pId of passenger id = pId of reservation id passenger whose ages > 65 are those of pId 1, 2, 3
and the pId common to reservation is 1 and 3.
The solution is best understood in steps
1. select the process Id from reservation whose class is AC the condition is satisfied by 0, 1, 5, 3
2. select a passenger whose age > 65 and the pId of passenger id = pId of reservation id passenger whose ages > 65 are those of pId 1, 2, 3
and the pId common to reservation is 1 and 3.
The solution is best understood in steps
1. select the process Id from reservation whose class is AC the condition is satisfied by 0, 1, 5, 3
2. select a passenger whose age > 65 and the pId of passenger id = pId of reservation id passenger whose ages > 65 are those of pId 1, 2, 3
and the pId common to reservation is 1 and 3.
- Consider a database table T containing two columns X and Y each of type integer. After the creation of the table, one record (X = 1, Y = 1) is inserted in the table. Let MX and MY denote the respective maximum values of X and Y among all records in the table at any point in time. Using Mx + 1, 2*MY + 1 respectively. It may be noted that each time after the insertion, values of MX and MY change. What will be the output of the following SQL query after the steps mentioned above the carried out?
SELECT Y FROM T WHERE X = 7;
-
View Hint View Answer Discuss in Forum
X Y 1 1 2 3 3 7 4 15 5 31 6 63 7 127
For X= 7 ⇒ Y = 127Correct Option: A
X Y 1 1 2 3 3 7 4 15 5 31 6 63 7 127
For X= 7 ⇒ Y = 127
