SQL Database Assignment: Queries, Solutions, and Analysis

This document presents solutions to an SQL database assignment, addressing various query-related problems. The assignment covers topics such as retrieving specific data, filtering results based on certain criteria, and performing calculations. The solutions include SQL queries for tasks like finding staff names, calculating total work hours, identifying projects with specific characteristics, and determining relationships between different tables. The document also examines the impact of certain commands, such as deleting records, on database integrity. This assignment is designed to help students understand and apply SQL concepts in a practical context, providing a comprehensive overview of database querying and management.

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;