Databases Test ...

In which case would you use a FULL OUTER JOIN?

The number of rows returned by SQL query on the given EMP table is ________

SELECT *

FROM EMP

WHERE eno NOT IN (

SELECT manager FROM EMP);

Given two tables EMPLOYEE (EID, ENAME, DEPTNO)

            DEPARTMENT (DEPTNO. DEPTNAME)

Find the most appropriate statement of the given query:

              Select count (*) total

              from EMPLOYEE where DEPTNO IN (D1, D2)

              group by DEPTNO

              having count (*) >5 

Consider a relation EdTech(Year, Famous, Popular) columns contain turnover in crores of Famous and Popular organization. If a cell contains null means turnover was not calculated for that particular year.

SQL query on the above relation is SELECT AVG(Popular)  FROM EdTech.

What is the output of the given SQL query (answer upto 1 decimal place)?

Given relations R(w, x) and S(y, z), the result of SELECT DISTINCT w, x from R, S is guaranteed to be same as R, provided.

Consider a Student table, it has two attributes which are the student_name and student seat_no. The student seat number is not null and also it is unique.  

The SQL statement is

Select count(*) from Student

where seat_no  ≤  All (Select seat_no from Student);

What is the output of the above statement?

A relational database contains two tables Traveler and Visited as shown below:

The primary key of the Traveler table is ID. For the Visited table, the columns

ID and City together form the primary key. Consider the SQL query given

below:

SELECT T.Name, sum(V.Amount)

FROM Traveler T, Visited V

WHERE T.ID = V.ID OR V.Amount = 30 K

GROUP BY T.Name;

Consider the following relational schema:

employee(empId,empName,empDept)

customer(custId,custName,salesRepId,rating)

salesRepId is a foreign key referring to empId of the employee relation. Assume that each

employee makes a sale to at least one customer. What does the following query return?

SELECT empName FROM employee E

WHERE NOT EXISTS (SELECT custId FROM customer C WHERE C.salesRepId = E.empId

AND C.rating <> ’GOOD’);

Consider a database with the relation schema STUDENT(StId, StName, and CourseName). An instance of the schema STUDENT is given below 

SQL query on the above instance is are given below.

The output of executing the SQL query is _________.

Database table name Tuition_Records is given below

SELECT count(*) FROM ((SELECT students, Teachers FROM Tuition_Records) AS A NATURAL JOIN (SELECT Teachers, Fees FROM Tuition_Records))