Database miscellaneous
- Database table by name Loan_Records is given below.
Borrower Bank_Manager Loan_Amount Ramesh Sunderajan 100000.00 Suresh Ramgopal 5000.00 Mahesh Sunderajan 7000.00
What is the output of the following SQL query ?
SELECT count (*)
FROM(
(SELECT Borrower. Bank_Manager FROM Loan_ Records)
AS S
NATURAL JOIN
(SELECT Bank_Manager, Loan_Amount FROM Loan_Records) AS T
);
-
View Hint View Answer Discuss in Forum
Correct Option: C
Direction: Consider the following relations A, B and C :
- How many tuples does the result of the following SQL query contain?
SELECT A. Id
FROM A
WHERE A. Age > ALL (SELECT B. Age
FROM B
WHERE B. Name = ‘Arun’)
-
View Hint View Answer Discuss in Forum
The meaning of "ALL" is the A. Age should be greater than all the values returned by the sub-query. There is no entry with name "arun" in table B. So the subquery will return NULL. If a sub-query returns an empty table or NULL, then the condition becomes true. Therefore, all the three row id's of A will be selected from table A.
Correct Option: B
The meaning of "ALL" is the A. Age should be greater than all the values returned by the sub-query. There is no entry with name "arun" in table B. So the subquery will return NULL. If a sub-query returns an empty table or NULL, then the condition becomes true. Therefore, all the three row id's of A will be selected from table A.
- How many tuples does the result of the following relational algebra expression contain? Assume that the schema of A ∪ B is the same as that of A.
(A ∪ B)A.ld>40∨C.ld<15C
-
View Hint View Answer Discuss in Forum
A ∪ B will have 5 rows because two rows in B are already in A. When cross product of A ∪ B and C is done, the result is:
The condition is: either A.Id > 40 or C.Id < 15. For first 5 rows, C. Id < 15. For 8th and 10th rows, A. Id > 40. So total rows in result will be 7. So correct option is (A)Correct Option: A
A ∪ B will have 5 rows because two rows in B are already in A. When cross product of A ∪ B and C is done, the result is:
The condition is: either A.Id > 40 or C.Id < 15. For first 5 rows, C. Id < 15. For 8th and 10th rows, A. Id > 40. So total rows in result will be 7. So correct option is (A)
- Given the following statements: S1: A foreign key declaration can always be replaced by an equivalent check assertion in SQL. S2: Given the table R(a,b,c) where a and b together form the primary key, the following is a valid table definition.
CREATE TABLE S (
a INTEGER,
d INTEGER,
e INTEGER,
PRIMARY KEY (d),
FOREIGN KEY (a) references R)
Which one of the following statements is CORRECT ?
-
View Hint View Answer Discuss in Forum
Both S1 and S2 are false.
Correct Option: D
Both S1 and S2 are false.
- Given the following schema: employees (emp-id, first-name, last-name, hire-date, deptid, salary)
departments (dept-id, dept-name, manager-id, location-id)
You want to display the last names and hire dates of all latest hires in their respective departments in the location ID 1700. You issue the following query:
SQL > SELECT last-name, hire-date
FROM employees
WHERE (dept-id, hire-date) IN
(SELECT dept-id, MAX (hire-date)
FROM employees JOIN departments USING (dept-id)
WHERE location-id = 1700
GROUP BY dept-id),
What is the outcome?
-
View Hint View Answer Discuss in Forum
It executes and gives the correct result. As we see there is nothing wrong with the Given query so it displays the expected result.
Correct Option: B
It executes and gives the correct result. As we see there is nothing wrong with the Given query so it displays the expected result.
