Databases Test 4

• The FULL OUTER JOIN (FULL JOIN) keyword returns all records when there is a match in either left (table1) or right table records and the record with unmatched data from BOTH tables
• The LEFT OUTER JOIN (LEFT JOIN) keyword returns all records when there is a match in either left (table1) or right table records and the record with unmatched data from LEFT tables
• The RIGHT OUTER JOIN (RIGHT JOIN) keyword returns all records when there is a match in either left (table1) or right table records and the record with unmatched data from RIGHT tables

NOT IN returns 0 records when compared against an unknown value.

The inner query return {2, 3, 4, NULL}. When outer query checks whether each eno is not in {2, 3, 4, NULL}, the where condition will return true. Hence one row will be selected.

Given query is:

             Select count (*) total

             from EMPLOYEE where DEPTNO IN (D1, D2)

             group by DEPTNO

having count (*) >5

STEP 1: It will first find the all the entries with department no. as D1 and D2 with the help of natural join from both the relations.

STEP 2: Then in the resulting table, it groups the tuples with the department no. (DEPTNO)

STEP3: Then select all those tuples having count > 5.

STEP 4: Finally, it results in the total number of employees of department D1 and D2 if their total is > 5.

Since increment containing null value is ignored

Average = \(\frac{{19 + 15 +17 + 22 + 12 + 18 + 16\;}}{7} = 119\)

Average = 17

Important Points:

Concept:

• SQL Relations are MULTISET, not SET. So, R or S can have duplicated.
• If S is empty, then R × S = empty and hence it will not equal to R
• If R has duplicates, in that case, due to distinct keyword those duplicates will be eliminated in the final result and SQL query will not be equal to R. So, R cannot have duplicates.

Option 1: R has no duplicates and S is non-empty

This is true:

Option 2: R and S have no duplicates

Since S can be empty ∴ R × S = 0 it is false

Option 3: S has no duplicates and R is non-empty

Since R can be duplicate it is false

Option 4: R and S has same number of tuples

Since R can be duplicate it is false

Example:

R(x, y)

S(y, z)

R × S:

SELECT DISTINCT w, x from R, S

Output:

This proves option 3 and 4 an incorrect statement

Concept:

null + unique = Primary key, Hence seat_no column is the primary key.

count(*)  is the aggregation function that is used to count the total no of rows in the table.

Explanation: (taking the small example)

Student table:

Inner query result: Select seat_no from Student

Output:

Outer query result:

Select count(*) from Student

where seat_no  ≤ All (2, 7, 9);

The only condition true is 2 ≤ All(2, 7, 9)

Therefore count is 1.

Let X ≡ T × V where T.ID = V.ID OR V.Amount = 30K

Modified Query:

SELECT T.Name, sum(P.Marks)

FROM X GROUP BY S.Student_name;

Final output:

The number of rows returned by the above SQL query is 7.

Concept:

• NOT EXISTS operator returns true if the underlying sub query return no record.
• If single record is matched by the inner sub query, the NOT EXISTS operator will return false, and the sub query execution can be stopped.

Explanation:

Example:

Employee table:

Customer table:

• Employee table entry E1 will matched with customer C1 in customer table but rating is GOOD so inner query will not return a record and NOT EXISTS will return true now employee entry e1 will matched with customer C2 but rating is bad so NOT EXISTS will return false and the sub query execution can be stopped and E1 will not print.
• Employee entry E2 will matched with customer C3 but Rating is BAD so NOT EXISTS will return false and E2 will not print.
• Employee entry E3 will matched with customer C4 but Rating is GOOD so NOT EXISTS will return true and E3 will print.
• This is nothing but name of all the employees with all their customers having a GOOD rating.

Concept:

Count the number of StIDs corresponding to a particular department and then write as attribute value of cnt in new relational schema S.

Explanation:

SQL query results                            

AVG(S. cnt) will find out the average of Num values in the returned query.,

\(Average = \;\frac{{3 + 4 + 2 + 3 + 2}}{5} = 2.8\)

A:

B:

A NATURAL JOIN B

SELECT count(*) FROM A NATURAL JOIN B