Question

The relational schema for the Academics database is as follows:

DEPARTMENT(deptnum, descrip, instname, deptname, state, postcode) ACADEMIC(acnum, deptnum*, famname, givename, initials, title) PAPER(panum, title) AUTHOR(panum*, acnum*) FIELD(fieldnum, id, title) INTEREST(fieldnum*, acnum*, descrip)

Some notes on the Academics database: ● An academic department belongs to one institution (instname) and often has many academics. An academic only works for one department. ● Research papers (PAPER) are often authored by several academics, and of course an academic often writes several papers (AUTHOR). ● A research field (FIELD) often attracts many academics and an academic can have interest in several research fields (INTEREST). Primary keys are underlined and foreign keys are marked with *.

Write ONE SQL query for each question to extract information from the database (except the last two questions).

Do not supply the output of the query or the script used to create the tables. Only the SQL query is required for each question.

2.5) List the number of papers that contain data analysis-related research? (data analysis-related papers are papers whose title contains the word “data” in upper or lower case letters. (it should not be part of a word – eg database etc) [HINT: your query should result in 1 row]

2.6) List the panum and title of papers written by the academic with ‘pr’ occuring in their surname. The list should be in the decreasing order of panum. [HINT: your query should result in 152 rows]

2.7) List the acnum of authors and the “number of papers” they have written, (if they have written at least 40 papers). The list should be ordered from most papers to least. [HINT: your query should result in 9 rows]

2.8) List (in ascending order) the acnum of academics belonging to “CS” departments (departments that have CS in the description). [HINT: your query should result in 228 rows]

Answer 1

2.5

SELECT COUNT(*) AS Num_Of_Papers
FROM PAPER WHERE
title LIKE '%data' AND title LIKE 'data%';

2.6

SELECT PAPER.panum,PAPER.title FROM
PAPER INNER JOIN AUTHOR ON PAPER.panum = AUTHOR.panum
INNER JOIN ACADEMIC ON AUTHOR.acnum = ACADEMIC.acnum
WHERE AUTHOR.famname LIKE '%pr%';

2.7

SELECT AUTHOR.acnum, COUNT(*)
FROM PAPER INNER JOIN AUTHOR ON PAPER.panum = AUTHOR.panum
GROUP BY AUTHOR.acnum
HAVING COUNT(*) > 40
ORDER BY COUNT(*) DESC;

2.8

SELECT ACADEMIC.acnum FROM
ACADEMIC INNER JOIN DEPARTMENT ON
ACADEMIC.deptnum = DEPARTMENT.deptnum
WHERE DEPARTMENT.descrip LIKE '%cs%'

ORDER BY ACADEMIC.acnum ;