Question
Transcribed Text
Q1
Consider the following ER diagram. Write SQL statements to create the corresponding
tables and capture as many of the constraints as possible (create tables only).
name
pname
age
ssn
lot
Dependents
Employees
Purchaser
eneficiary
Policies
policyid
cost
Q2. Assume we have the following two tables and instances for them:
CREATE TABLE Employee Work (age INTEGER,
ssn CHAR(10),
name CHAR(50),
pid CHAR(10) Not Null,
PRIMARY KEY (ssn),
FOREIGN KEY (pid) REFERENCES Project)
CREATE TABLE Project( pid CHAR(10),
budget CHAR(10),
PRIMARY KEY (pid))
Project
Employee-work
pid
budget
Ssn
name
pid
100
500K
5380
Guldu
102
102
200K
5381
Ali
100
5382
Jill
100
What will be the new instances of the tables Employee-work and Project after running the
following query?
DELETE FROM Project P
WHERE pid 100
Q3.
A) Are the following queries equivalent?
Queryl:
SELECT S.sname
FROM Sailors S, Reserves R
WHERE S.sid=R.sid AND R.bid=103
Query2:
SELECT S.sname
FROM Sailors S
WHERE S.sid IN (SELECT R.sid
FROM Reserves R
WHERE R.bid=103)
B) Considering the following instances of Sailors and Reserves tables, what are the
results of the query1 and query2 in part A:
sid
bid
day
1
101
10/10/17
sid
sname
rating
age
3
103
11/12/17
1
dustin
7
45.0
3
103
10/10/18
2
zorba
8
55.5
3
102
02/12/19
3
rusty
10
35.0
Q4.
A) Are the following queries equivalent?
Queryl:
SELECT S.sname
FROM Sailors S, Reserves R
WHERE S.sid=R.sid AND R.bid<>103
Query2:
SELECT S.sname
FROM Sailors S
EXCEPT
SELECT S.sname
FROM Sailors Reserves R
WHERE S.sid=R.sid AND R.bid=103;
B) Considering the following instances of Sailors and Reserves tables, what are the
results of the query1 and query2 in part A:
sid
bid
day
1
101
10/10/17
sid
sname
rating
age
3
103
11/12/17
1
dustin
7
45.0
3
103
10/10/18
2
zorba
8
55.5
3
102
02/12/19
3
rusty
10
35.0
Q5 Consider the following relational schema.
Emp(eid: integer ename: string age: integer salary: real
)
Works(eid integer did: integer start: date
Dept(did: integer dname: string budget: real managerid: integer )
The meaning of these relations is straightforward Write the following queries in SQL.
1. Find the id of employees who work in both Hardware and Sale department
2. Find manager (ids) and names of departments that have more than ten employees
3. For each department with more than 20 employees, print did and the number of
employees who work in that department.
4. Find the names of managers who manage the departments with the largest budget.
Q6. Consider the following schema: Suppliers(sid integer,
sname: string, address string) Parts(pid: integer,
pname string, color string) Catalog(sid integer,
pid integer, cost: real)
The Catalog relation lists the prices charged for parts by Suppliers Write the following
queries SQL:
Find the names of suppliers who supply every red part
2. Find the sid of suppliers who supply only red parts
3. Find the sid of suppliers who charge for some part more than the average price of that
part.
Q7. Formulate the following SQL query in relational algebra using an efficient way The
schema is given in the following.
Emp(eno, ename, title, city)
Proj(pno, pname, budget. city)
Works(eno, pno,since)
SELECT ename
FROM Works Emp
WHERE Works pno- P150'
AND Works.eno Emp.eno
Solution Preview
These solutions may offer step-by-step problem-solving explanations or good writing examples that include modern styles of formatting and construction
of bibliographies out of text citations and references. Students may use these solutions for personal skill-building and practice.
Unethical use is strictly forbidden.
Q1. Consider the following ER diagram. Write SQL statements to create the corresponding tables and capture as many of the constraints as possible (create 3 tables only).
CREATE TABLE POLICIES
(
POLICYID VARCHAR2(20) NOT NULL
, COST NUMBER
, CONSTRAINT POLICIES_PK PRIMARY KEY
(
POLICYID
)
);
CREATE TABLE EMPLOYEES
(
SSN VARCHAR2(20) NOT NULL
, NAME VARCHAR2(50)
, LOT VARCHAR2(20)
, CONSTRAINT EMPLOYEES_PK PRIMARY KEY
(
SSN
)
);
CREATE TABLE DEPENDENTS
(
PNAME VARCHAR2(60)
, PSSN VARCHAR2(20) NOT NULL
, AGE NUMBER
, POLICYID VARCHAR2(20) NOT NULL
, CONSTRAINT DEPENDENTS_PK PRIMARY KEY
(
PSSN
)
ENABLE
);...
This is only a preview of the solution. Please use the purchase button to see the entire solution