Team A
_________ 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
Say true or false
views are stored in local drives
No! they are virtual tables and do not have any storage
_____________ 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
A populated table is formally known as ____________ in a relation
state
No duplicate tuple in a relation is an example of _____________ constraint
inherent or implicit
In a relation a primary key is one chosen arbitrarily among the several _____________ keys.
candidate
Say true or false
Adding an ORDER By clause in a view is legal.
False
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.
Say true or false
Tuples are deleted from only one table at a time unless CASCADE is specified on a referential integrity constraint
true
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
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’
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
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))
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))
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))