1. This relational schema models a company schema with departments, employees, a
ID: 3671961 • Letter: 1
Question
1. This relational schema models a company schema with departments, employees, and locations. Primary keys are underlined and foreign keys are indicated below each relation.
department(dno, dname, mgrID)
mgrID is a foreign key to employee
dept-location(dno, location)
dno is a foreign key to department
employee(eID, name, salary, superID, dno)
superID is a foreign key to employee
dno is a foreign key to department
dependent(eID, depname)
eID is a foreign key to employee
Write SQL queries to satisfy the following data requests using the schema above. Use good SQL syntax or WinRDBI syntax. You may use intermediate queries. Your queries should not reference any information not explicitly given in the problem.
Give the department name, department number, and employee count for departments with more than 2 employees that have a department location in Italy.
2. This relational schema models webpages with embedded graphics and links. A webpage may have multiple graphics, a graphic has a type, and a webpage may reference many other webpages (and be referenced by many other webpages.) Primary keys are underlined.
webpage(webID, webTitle, URL, base, hits)
link(sourceWebID, targetWebID)
sourceWebID is a foreign key to webpage
targetWebID is a foreign key to webpage
graphic(gID, gType, gLocation)
display(webID, gID)
webID is a foreign key to webpage
gID is a foreign key to graphic
Write SQL queries to satisfy the following data requests using the schema above. Your queries should not reference any information not explicitly given in the problem.
For each group of pages belonging to a base, give the base and the average number of hits in that group where the average is greater than 100.
3. This relational schema models a company schema with departments, employees, and locations. Primary keys are underlined and foreign keys are indicated below each relation.
department(dno, dname, mgrID)
mgrID is a foreign key to employee
dept-location(dno, location)
dno is a foreign key to department
employee(eID, name, salary, superID, dno)
superID is a foreign key to employee
dno is a foreign key to department
dependent(eID, depname)
eID is a foreign key to employee
Write SQL queries to satisfy the following data requests using the schema above. Please use SQL syntax discussed in class. You may use intermediate queries. Your queries should not reference any information not explicitly given in the problem.
a. for each department, give the department name, department manager’s name, and the department manager’s supervisor’s name.
b. Give department names and locations where the department has a department manager who has no dependents.
4. This relational schema models webpages with embedded graphics and links. A webpage may have multiple graphics, a graphic has a type, and a webpage may reference many other webpages (and be referenced by many other webpages.) Primary keys are underlined.
webpage(webID, webTitle, URL, base, hits)
link(sourceWebID, targetWebID)
sourceWebID is a foreign key to webpage
targetWebID is a foreign key to webpage
graphic(gID, gType, gLocation)
display(webID, gID)
webID is a foreign key to webpage
gID is a foreign key to graphic
Write SQL queries to satisfy the following data requests using the schema above. Your queries should not reference any information not explicitly given in the problem.
a. Which pages have a graphic with type (gType) equal to “jpeg” but not any of type “gif?” Give the webID and webTitle for these pages.
c. Which webpages contain a link to another webpage? List web ID and title for both the source and target web page.
Explanation / Answer
1.
SELECT d.dname as department_name, d.dno as department_no, count(d.mrgID) as emp_count
FROM department d
JOIN dept-location dl ON d.dno = dl.dno
JOIN employee e ON e.eID = d.mgrID
WHERE d.location = 'Italy' and count(d.mrgID) > 2
GROUP BY d.no;
2.
SELECT base, sum(hits) / count(webID)
FROM webpage
WHERE sum(hits) / count(webID) > 100;
3.
a.
SELECT d.name as department_name, emp.name as manager_name, supervisor.name as supervisor_name
FROM department d
JOIN employee emp ON emp.empID = d.mgrID
JOIN employee supervisor ON emp.superID = supervisor.empID;
b.
SELECT d.name as department_name, dl.location as location
FROM department d
JOIN dept-location dl ON d.dno = dl.dno
WHERE d.mgrID NOT IN (SELECT eID FROM dependent);
Please post the last question as separate question.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.