Database SQL Queries and Solutions

This assignment focuses on Database SQL, requiring students to write queries to answer specific questions about staff, projects, time records, and funders. It includes tasks like retrieving admin names, project titles with the highest PID, identifying projects with multiple workers, and calculating total hours worked on a specific project. The assignment also delves into understanding how foreign keys impact data deletion.

Running head: DATABASE SQL
Database SQL
Name of the Student:
Name of the University:
Author note:

1DATABASE SQL
Question 1
a) Develop an SQL query to answer the following question: What are the names of the
admin people?
Answer:
SELECT name
FROM Staff
WHERE role = ‘admin’;
b) Develop an SQL query that will produce a table such as TimeRecords, but which will
print the title of the project instead of the pid and the name of the staff instead of the
sid.
Answer:
SELECT ResearchProject.pid, Staff.name, TimeRecords.workDate, TimeRecords.workHours
FROM ((TimeRecords
INNER JOIN ResearchProject ON TimeRecords.pid = Staff.pid)
INNER JOIN Staff ON TimeRecords.sid = Staff.sid);
c) Develop an SQL query to answer the following question: What is the title of the
project with the highest pid number?
Answer:
SELECT title AS Max pid Title

2DATABASE SQL
FROM ResearchProject
WHERE pid = (SELECT MAX(pid)
FROM ResearchProject);
d) Develop an SQL query to answer the following question: Which projects had more
than one person working on it?
Answer:
SELECT title
FROM ResearchProject
WHERE pid = (SELECT pid
FROM TimeRecords
GROUP BY pid, sid
HAVING COUNT(*) > 1);
e) What would happen if you tried to execute the following SQL statement? DELETE
from Staff where sid = 12
Answer:
This command will not only delete the entire row with ‘sid’=12 from Staff, but will
also bring changes to the ResearchProject table. The ‘pi’ field with rows having 12 as their
values, will be set to NULL. This is because ‘pi’ is a foreign key that references the ‘sid’ field
from the Staff table.

3DATABASE SQL
Question 2
a) Develop an SQL query to answer the following question: What are the name and role
of the staff member whose sid is 12?
Answer:
SELECT name, role
FROM Staff
WHERE sid = 12;
b) Develop an SQL query to answer the following question: What is the total number of
hours worked on the Tetra project?
Answer:
SELECT SUM(workHours)
FROM TimeRecords
WHERE pid = (SELECT pid
FROM ResearchProject
WHERE title = ‘Tetra’);
c) Develop an SQL query to answer the following question: What is the name of the
project with the highest pid number?
Answer:
SELECT title AS Max pid Title

4DATABASE SQL
FROM ResearchProject
WHERE pid = (SELECT MAX(pid)
FROM ResearchProject);
d) Develop an SQL query to answer the following question: How many hours did Karen
Pitt work on the Takar project on February 19, 2013?
Answer:
SELECT workHours
FROM TimeRecords
WHERE sid = (SELECT sid
FROM Staff
WHERE name = ‘Karen Pitt’)
AND pid = (SELECT pid
FROM ReaserchProject
WHERE title = ‘Takar’)
AND workDate = ‘2013-02-19’)
e) Develop an SQL query to answer the following question: Which funder has
supported more than one project?
Answer:
SELECT funder

5DATABASE SQL
FROM ResearchProject
GROUP BY funder
HAVING COUNT(*) > 1;