Test: SQL

According to standard SQL answer should be C. Refer If we talk about different SQL implementations like MySQL, then option B is also right. But in question they seem to be talking about standard SQL not about implementation. For example below is a P is correct in most of the implementations. HAVING clause can also be used with aggregate function. If we use a HAVING clause without a GROUP BY clause, the HAVING condition applies to all rows that satisfy the search condition. In other words, all rows that satisfy the search condition make up a single group. See this for more details. S is correct . To verify S, try following queries in SQL.

CREATE TABLE temp
      (
             id INT,
             name VARCHAR(100)
      );
INSERT INTO temp VALUES (1, "abc"); INSERT INTO temp VALUES (2, "abc"); INSERT INTO temp VALUES (3, "bcd"); INSERT INTO temp VALUES (4, "cde");

SELECT Count(*)
FROM temp
GROUP BY name;

Output:

count(*)
--------
2
1
1

The meaning of “ALL” is the A.Age should be greater than all the values returned by the subquery. There is no entry with name “arun” in table B. So the subquery will return NULL. If a subquery returns NULL, then the condition becomes true for all rows of A (See this for details). So all rows of table A are selected. 

When a subquery uses values from outer query, the subquery is called correlated subquery. The correlated subquery is evaluated once for each row processed by the outer query. The outer query selects 4 entries (with pids as 0, 1, 5, 3) from Reservation table. Out of these selected entries, the subquery returns Non-Null values only for 1 and 3.

I and II describe the division operator in Relational Algebra and Tuple Relational Calculus respectively. See Page 3 of this and slide numbers 9,10 of this for more details.

The subquery “SELECT P.pid FROM Parts P WHERE P.color<> ‘blue’” gives pids of parts which are not blue. The bigger subquery “SELECT C.sid FROM Catalog C WHERE C.pid NOT IN (SELECT P.pid FROM Parts P WHERE P.color<> ‘blue’)” gives sids of all those suppliers who have supplied blue parts. The complete query gives the names of all suppliers who have supplied a non-blue part

Let the employee(empId, name, department, salary) have the following instance. empId name department salary ----------------------------------

Now the actual result should contain empId : e1 , e3 and e5 ( because they have salary greater than anyone employee in the department '5') -------------------------------------------------------- Now Q1 : Note : EXISTS(empty set) gives FALSE, and NOT EXISTS(empty set) gives TRUE.

Q1 will result only empId e1. --------------------------------------------------------- whereas Q2 :

Q2 will result empId e1, e3 and e5. -------------------------------------------------------- Hence Q2 is the correct query.

False: Check assertions are not sufficient to replace foreign key. Foreign key declaration may have cascade delete which is not possible by just check insertion.

False: Foreign key in one table should uniquely identifies a row of other table. In above table definition, table S has a foreign key that refers to field 'a' of R. The field 'a' in table S doesn't uniquely identify a row in table R.

The given query uses below inner query.

The inner query produces last max hire-date in every department located at location id 1700. The outer query simply picks all pairs of inner query. Therefore, the query produces correct result.

The solution of this question lies in the data set(tuples) of Relations R and S we define. If we miss some case then we may get wrong answer. Let's say, Relation R(BCA) with attributes B, C and A contains the following tuples.

And Relation S(AMN) with attributes A, M, and N contains the following tuples.

Now ,the original Query will give result as: "select * from R where a in (select S.a from S) " - The query asks to display every tuple of Relation R where R.a is present in the complete set S.a.

Option A query will result in : "select R.* from R, S where R.a=S.a"

Option B query will result in : " select distinct R.* from R,S where R.a=S.a"

----------------------------------------------------------------------------------------------------------- Option C query will result in : "select R.* from R,(select distinct a from S) as S1 where R.a=S1.a" B C A --------- 7 2 1 7 2 1 8 9 5 8 9 5 ----------------------------------------------------------------------------------------------------------- Option D query will result in : NULL set "select R.* from R,S where R.a=S.a and is unique R" ---------------------------------------------------------------------------------------------------------- Hence option C query matches the original result set. Note : As mentioned earlier, we should take those data sets which can show us the difference in different queries. Suppose in R if you don't put identical tuples then you will get wrong answers. (Try this yourself, this is left as an exercise for you).

If any employee has received rating other than 'good' from some customer, then there will be some rows returned by the inner query.
And not exists will return false so that employee won't be printed only those employees which have got rating good from all their customers will be printed.

drop is used to delete a table completely

Query 1 and Query 2 will give the same result if all the customers have distinct balance. So, for some databases, the result of query 1 and query 2 will be same. 
Now, let us consider a fact that all the entries in the database have the same value for balance. Ideally, all the customers should have rank 1, but both the queries will give all the customers a rank equal to the number of customers in the database. So, both the queries do not give us the required output. 
Thus, C is the correct choice. 
Please comment below if you find anything wrong in the above post.

The result of the natural join is the set of all combinations of tuples in R and S that are equal on their common attribute names. What is the maximum possible number of tuples? The result of natural join becomes equal to the Cartesian product when there are no common attributes. The given tables have a common attribute, so the result of natural join cannot have more than the number of tuples in larger table.

What is the maximum possible number of tuples? It might be possible that there is no rollnumber common. In that case, the number of tupples would be 0.

In this SQL query, we have

(select avg (salary) from Employee) ----- Having the average salary greater than the average salary of all employees in the organization.

So, this query would return the name of all departments in which the average salary of male employees is greater than the average salary of all employees in the company. Hence, D is the correct choice.  Please comment below if you find anything wrong in the above post.

Cross product (x) combines the tuples of one relation with all the tuples of the other relation. Thus, tuples of relation r1, r2 …. rn are combined. 

Select operator  is used to select resultant tuples. 

Projection operator  is used to select a subset of attributes from the resultant tuples by specifying the names of the attributes. So attributes a1, a2, an are projected from the resultant tuples. 
 
Thus, option (A) is correct. 
 
Please comment below if you find anything wrong in the above post.

The query gives the name of all the students who have scored "A" grade in any of the courses that are taught by Korth. So, C is the correct choice. 
 
Please comment below if you find anything wrong in the above post.

For all values smaller than 5, x < 5 will always be true but x = 5 will be false.

Background Reading: The above query is an example of nested query i.e. query within a query. Firstly the inner query is solved and then the outer one depending on the result of the inner query.

• WHERE IN returns values that matches values in a list or subquery.
• WHERE IN is a shorthand for multiple OR conditions.

Here, firstly the inner query is solved. It returns all the Enrollment
Numbers (SELECT S2.EnrollNo) of students where the students’ enrollment
number matches with the enrollment number of the courses
(WHERE S2.EnrollNo = E2.EnrollNo) which have the course IDs whose Course
Name is “OS” (E2.CourseID = C2.CourseID and C2.Name = “OS”).

Hence all the enrollment IDs are filtered out for the students who are enrolled for the “OS” course.

The outer query works similarly and filters out all the all tuples where the Students Enrollment Number matches with the Enrollment Number where the course ID’s are for the course names “DBMS”
(S.EnrollNo = E.EnrollNo AND C.Name =”DBMS” AND E.CourseID = C.CourseId) and additionally matches with the ones that are returned by the inner query i.e. Enrollment Number of students who are enrolled for the course “OS”.

Hence the above queries returns names of all students (SELECT S.Name) who have enrolled for both courses “DBMS” and “OS”. Hence option (B).

Background:

1. WHERE EXISTS tests for the existence of any records in a subquery.
2. EXISTS returns true if the subquery returns one or more records.
3. EXISTS is commonly used with correlated subqueries.

Here in the above question, there is a correlated subquery because the subquery references the enclosing query (relation Employee renamed as E) The subquery (SELECT E2.salary FROM Employee E2 WHERE E2.DeptName = 'CS') Filters out E2 relation as (all tuples where DeptName is CS and the respective salaries) Now the correlated query works as follows: SELECT E.ID FROM Employee E WHERE EXISTS (SELECT E2.salaryFROM Employee E2 WHERE E2.DeptName = 'CS' AND E.salary > E2.salary) It takes one tuple from the Employee Relation and displays its ID if the WHERE EXISTS returns true i.e. the subquery returns one or more records. This happens in the case when the tuple from the Employee Relation E has the value of the salary attribute greater than any one of the values of the salary attribute filtered out above. So tuples filtered out would be all the tuples that have their salary attribute value greater than the salary values of at least one from the E2 relation (3000 and 7000). ID salary DeptName 2 40000 EC 4 40000 ME 5 50000 ME 6 60000 ME 7 70000 CS Finally it displays their ID’s and the output would be: 2 4 5 6 7 Hence option (C) 5 rows.

Select operation is equivalent to the projection operation in relational algebra, except that select in SQL retains duplicates and on the contrary projection removes the duplicates.

Below is result of given query. Note that there are only two student names and query prints sum(P.Marks) for every student.

When the ALL condition is followed by a list, the optimizer expands the initial condition to all elements of the list and strings them together with AND operators. When the ANY condition is followed by a list, the optimizer expands the initial condition to all elements of the list and strings them together with OR operators, as shown below. 

T3 followed by T2 followed by T1  If this sequence is not followed then it may happen that Officer of one slab get benefited twice. Say an officer is having commission as 99,999,He must be get updated commision according to T2.But then 99,999*1.04=1,03998 and he again becomes eligible for next slab of commision. T3 followed by T2 followed by T1 will be check all border cases like above.So Answer is D