LESSON-7
Q1)Which of the following joins is the default type of join in a joined table, where a tuple is included in the result only if a matching tuple exists in the other relation?
Inner
Q2). Which of the following functions returns the number of tuples or values as specified in a query?
Count
Q3) Which of the following strategies updates a view when needed by a view query?
Lazy Update
LESSON 8
Q1) Which of the following symbols denotes the SELECT operator?
sigma
Q2) Which of the following operations combines the results of two or more SELECT statements without including duplicates?
Union
Q3) Which of the following operations combines the result of two SELECT statements and returns only those results which belong to the first set of result?
SET DIFFERENCE
LESSON 9
Q1) What is the first step of an algorithm for ER-to-relational mapping?
A). Mapping of regular entity types
Q2) Which step of an algorithm for ER-to-relational mapping involves the following approaches:
A) Mapping of binary 1:1 relationship types
Q3) Which of the following approaches is mostly preferred, as it reduces the number of tables?
A) Foreign key
LAB
LAB-7
1.
SELECT DISTINCT Pnumber, Pname
FROM PROJECT
WHERE Pnumber IN
(SELECT Pnumber
FROM PROJECT, EMPLOYEE_DEPARTMENT, EMPLOYEE_REC
WHERE Dnum = Dnumber AND
Mgr_ssn = Super_ssn AND Lname = ‘Zelaya’)
OR
Pnumber IN
(SELECT Pno
FROM WORKS_ON, EMPLOYEE_REC
WHERE Essn = Ssn AND Lname = ‘Smith’);
2.
SELECT Lname, Fname
FROM EMPLOYEE_REC
WHERE Salary > (SELECT MAX (Salary)
FROM EMPLOYEE_REC
WHERE Dno = 5);
3.
SELECT E.Lname AS Employee_name,
S.Lname AS Supervisor_name
FROM (EMPLOYEE_REC AS E LEFT OUTER JOIN EMPLOYEE_REC AS S
ON E.Super_ssn = S.Ssn);
4.
SELECT SUM (Salary), MAX (Salary), MIN (Salary), AVG (Salary)
FROM (EMPLOYEE_REC JOIN EMPLOYEE_DEPARTMENT ON Dno = Dnumber)
WHERE Dname = ‘Research’;
5.
SELECT COUNT (*)
FROM EMPLOYEE_REC;
6.
SELECT Pnumber, Pname, COUNT (*)
FROM PROJECT, WORKS_ON
WHERE Pnumber = Pno
GROUP BY Pnumber, Pname
HAVING COUNT (*) > 1;
7.
UPDATE EMPLOYEE_REC
SET Salary =
CASE WHEN Dno = 5 THEN Salary + 2000
WHEN Dno = 4 THEN Salary + 1500
WHEN Dno = 1 THEN Salary + 3000
ELSE Salary + 0;
8.
CREATE ASSERTION SALARY_CONSTRAINT
CHECK (EXISTS (SELECT *
FROM EMPLOYEE_REC E,
EMPLOYEE_DEPARTMENT D
WHERE E.Salary > 25000
AND E.Dno = D.Dnumber
AND D.Mgr_ssn = E.Ssn));
9.
CREATE VIEW WORKS_ON1
AS SELECT Fname, Lname, Pname, Hours
FROM EMPLOYEE_REC, PROJECT, WORKS_ON
WHERE Ssn = Essn AND Pno = Pnumber;
10.
ALTER TABLE COMPANY.EMPLOYEE_REC ADD COLUMN Job VARCHAR(12);
LAB-8
1.
SELECT DISTINCT Sex, Salary
FROM EMPLOYEE_REC;
2.
SELECT City FROM EMP_CUSTOMERS
UNION
SELECT City FROM EMP_SUPPLIERS
ORDER BY City;
3.
SELECT *
FROM EMPLOYEE_REC
WHERE Dno = 4 AND Salary > 25000;
4.
Other Links:
See other websites for quiz:
Check on QUIZLET