Database miscellaneous
- Consider the set of relations shown below and the SQL query that follows:
Students: (Roll_number, Name, Data_of_birth)
Courses: (Course number, Course_name, Instructor)
Grades: (Roll_number, Course_number, Grade)
Select distinct Name
from Students, Courses, Grades
where Students. Roll_number = Grades Roll_number and Grades.grade
and courses. Instructor = korth
and Courses.course - number = Grades.course - number
Roll_number = Grades.Roll_number and Grades.grade = A
Which of the following sets is computed by the above query?
-
View Hint View Answer Discuss in Forum
The relations are as given,
Students: (Roll_number, Name, Date_of_birth)
Courses: (Course number, Course_name, Instructor)
Grades: (Roll_number, Course_number, Grade)
Now, the distinct name is to be selected, where
1. Name of the student is selected on the basis of the grade.
2. Instructor of the course in Korth.
3. Courses selected on the basis of grade.
4. Grade should be A.
The query thus, computed is name of the students who gets A grade in atleast one of the courses taught by Korth .Correct Option: C
The relations are as given,
Students: (Roll_number, Name, Date_of_birth)
Courses: (Course number, Course_name, Instructor)
Grades: (Roll_number, Course_number, Grade)
Now, the distinct name is to be selected, where
1. Name of the student is selected on the basis of the grade.
2. Instructor of the course in Korth.
3. Courses selected on the basis of grade.
4. Grade should be A.
The query thus, computed is name of the students who gets A grade in atleast one of the courses taught by Korth .
- 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 avtg (salary) from Employee)
It returns the name of the department in which
-
View Hint View Answer Discuss in Forum
SQL query will compute as follows
1. Selects the name from the department
2. Selects the male employee
3. Computes that the average salary of male employees is more than the average salary in the company.Correct Option: D
SQL query will compute as follows
1. Selects the name from the department
2. Selects the male employee
3. Computes that the average salary of male employees is more than the average salary in the company.
- 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
-
View Hint View Answer Discuss in Forum
The computation is as follows
1. Select the name of the title
2. title is selected from the book
3. Initiates a count
4. consider book as T
5. compute the title of five most expensive booksCorrect Option: D
The computation is as follows
1. Select the name of the title
2. title is selected from the book
3. Initiates a count
4. consider book as T
5. compute the title of five most expensive books
- Consider the relation account (customer, balance) where customer is a primary key and there are no null values. We would like to rank customers according to decreasing balance. The customer with the largest balance gets rank 1. Ties are not broken but ranks are skipped: If exactly two customers have the largest balance they each get rank 1 and rank 2 is not assigned.
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 Query2 and Query2 are correct implementation of the specification.
3. Query1 is a correct implementation of the specification but Query2 is not.
4. Neither Query1 nor Query2 is 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 two of the above statements are correct?
-
View Hint View Answer Discuss in Forum
Query1 where A.balance<=B.balance → this line computes the A group of customers and the balance is less than or equal to the group B.
Query2 where A.balance < B.balance → this line computes the A group of customers and the balance is less than the group B.
Therefore, the row produced by query1 and query2 will be same but not in every case as the results of query1 and query2 differ in case where query1 has the situation <= but query2 has only <.
None of the query provides the correct implementation of the specification.Correct Option: C
Query1 where A.balance<=B.balance → this line computes the A group of customers and the balance is less than or equal to the group B.
Query2 where A.balance < B.balance → this line computes the A group of customers and the balance is less than the group B.
Therefore, the row produced by query1 and query2 will be same but not in every case as the results of query1 and query2 differ in case where query1 has the situation <= but query2 has only <.
None of the query provides the correct implementation of the specification.
- Consider the relation enrolled (student, course) in which (student, course) is the primary key, and the relation paid (student, amount) where student is the primary key. Assume no null values and no foreign keys or integrity constraints. Given the following four queries :
Query1 : select student from enrolled where student in (select student from paid)
Query2 : select student form paid where student in (select student from enrolled)
Query3 : select E.student from enrolled E, paid P where E.student = P. student
Query4 : select student from paid where exists
(select * from enrolled where enrolled.student = paid.student)
Which one of the following statements is correct?
-
View Hint View Answer Discuss in Forum
The output of Query2, Query3 and Query4 will be identical. Query1 may produce duplicate rows. But rowset produced by all of them will be same.
Table enrolledstudent course ----------- ----------- abc c1 xyz c1 abc c2 pqr c1 
Output of Query 1
abc
abc
xyz
Output of Query 2
abc
xyz
Output of Query 3
abc
xyz
Output of Query 4
abc
xyz
Correct Option: A
The output of Query2, Query3 and Query4 will be identical. Query1 may produce duplicate rows. But rowset produced by all of them will be same.
Table enrolledstudent course ----------- ----------- abc c1 xyz c1 abc c2 pqr c1
Output of Query 1
abc
abc
xyz
Output of Query 2
abc
xyz
Output of Query 3
abc
xyz
Output of Query 4
abc
xyz
