22CS44-Quiz-2

Team A

100

_________ clause is used for specifying a selection condition on groups rather than on individual tuples

i) where    ii) order by   iii) group by  iv) having 

having

100

A populated table is formally known as ____________  in a relation 

state

100

Say true or false

Adding an ORDER By clause in a view is legal.

False

200

Write an SQL statement to do the following :

For each employee, retrieve the employee's name, and the name of his or her immediate supervisor.

SELECT  E.FNAME, E.LNAME, S.FNAME, S.LNAME
  FROM   EMPLOYEE E S
  WHERE  E.SUPERSSN=S.SSN

300

Construct relational algebraic query for the statement:

Find those salesmen with all information who gets the commission within a range of 0.12 and 0.14

π_sid, sname, commission (σ_commission >= 0.12 AND commission <= 0.14 (Salesmen))

Team B

100

Say true or false 

views are stored in local drives 

No! they are virtual tables and do not have any storage

100

No duplicate tuple in a relation is an example of _____________ constraint

inherent or implicit 

100

what does this SQL statement yield?

select dno,count(ename) from emp;

error !!any column or expression in SELECT list that is not an aggregate function  must be in group by clause.

200

Write an SQL statement to do the following

Show the effect of giving all employees who work on the 'ProductZ' project a 10% decrease.

SELECT   FNAME, LNAME, SALARY/1.1
  FROM  EMPLOYEE, WORKS_ON,   PROJECT
  WHERE  SSN=ESSN AND PNO=PNUMBER   AND PNAME='ProductZ’

300

Construct relational algebraic query for the statement:

Display all the customers, who are either belongs to the city New York or not had a grade above 100.

π_cid, cname, city, grade (σ_city='New York' ∨ grade <= 100 (Customers))

Team C

100

_____________ function is used to check whether the result of a correlated nested query is empty or not ?

i) not exists      ii)  exists   iii) check   iv) assertions

EXISTS function

100

In a relation a primary key is one chosen arbitrarily among the several _____________ keys.

candidate

100

Say true or false 

Tuples are deleted from only one table at a time unless CASCADE is specified on a referential integrity constraint

true

200

Write an SQL statement to do the following 

Retrieve a list of employees and the projects each works in, ordered by the employee's department, and within each department ordered alphabetically by employee last name

SELECT   DNAME, LNAME, FNAME, PNAME
        FROM   DEPARTMENT, EMPLOYEE,   WORKS_ON, PROJECT
  WHERE  DNUMBER=DNO AND SSN=ESSN   AND PNO=PNUMBER
  ORDER BY  DNAME, LNAME

300

Generate relational algebraic query for the statement:

Find that customer with all information who does not get any grade except NULL 

π_cid, cname, grade (σ grade IS NULL (Customers))