TTkqmfpK

1DROP TABLE INTEREST;
2DROP TABLE FIELD;
3DROP TABLE AUTHOR;
4DROP TABLE PAPER;
5DROP TABLE ACADEMIC;
6DROP TABLE DEPARTMENT;
7
8create table DEPARTMENT
9(
10    DEPTNUM	integer		not null,
11    DESCRIP	char(10),
12    INSTNAME	char(80),
13    DEPTNAME	char(80),
14    STATE	char(4),
15    POSTCODE	integer,
16	primary key (DEPTNUM)
17);
18
19
20create table ACADEMIC
21(
22    ACNUM	integer		not null,
23    DEPTNUM	integer		not null,
24    FAMNAME	char(20),
25    GIVENAME	char(20),
26    INITIALS	char(10),
27    TITLE	char(10),
28	primary key (ACNUM),
29	foreign key (DEPTNUM) references DEPARTMENT
30);
31
32
33create table PAPER
34(
35    PANUM	integer		not null,
36    TITLE	char(100),
37	primary key (PANUM)
38);
39
40
41create table AUTHOR
42(
43    PANUM	integer		not null,
44    ACNUM	integer		not null,
45	foreign key (PANUM) references PAPER,
46	foreign key (ACNUM) references ACADEMIC,
47	primary key (PANUM, ACNUM)
48);
49
50
51create table FIELD
52(
53    FIELDNUM	integer		not null,
54    ID		char(10)	not null,
55    TITLE	char(80),
56	primary key (FIELDNUM)
57);
58
59
60create table INTEREST
61(
62    FIELDNUM	integer		not null,
63    ACNUM	integer		not null,
64    DESCRIP	char(80),
65	foreign key (FIELDNUM) references FIELD,
66	foreign key (ACNUM) references ACADEMIC,
67	primary key (FIELDNUM, ACNUM)
68);
69
70--1. For each academic, give the acnum, givename, famname and the total number of papers 
71-- s/he has written. Note that if an academic has not written any paper, his/her total 
72-- should be zero. You can use or not use JOIN operators. 
73SELECT academic.ACNUM, academic.GIVENAME, academic.FAMNAME, Count(author.PANUM) 
74FROM academic
75LEFT JOIN author on academic.ACNUM = author.ACNUM group by academic.ACNUM, academic.GIVENAME, academic.FAMNAME;
76
77
78--2. List departments where at least one academic does not have any research interest. 
79-- List the deptnum, deptname and instname of these departments. You must use a subquery.
80SELECT deptnum, deptname, instname
81FROM department 
82where exists (select *
83from academic
84where exists (select *
85from interest
86where descrip = null));
87--3. List the fieldnum, title and the total number of interested academics (under the 
88-- heading "NO. ACADEMICS INTERESTED") for each research field that some academics are 
89-- interested in. The list should be in increasing order of fieldnum.  Note: research 
90-- fields that no academics are interested in are excluded.  
91select title, count(interest.fieldnum) as NUM_ACADEMICS_INTERESTED
92from field, interest
93where interest.FIELDNUM = field.FIELDNUM group by title
94order by NUM_ACADEMICS_INTERESTED;
95
96select field.fieldnum, field.title, count(interest.acnum) as NO_ACADEMICS_INTERESTED
97from field
98join interest
99on interest.fieldnum = field.fieldnum
100group by field.fieldnum, field.title
101order by field.fieldnum;
102--missing fieldnum unsure how to add in?
103
104
105--4.Find research fields that have at least ten interested academics.  Give the fieldnum,
106-- title and the number of interested academics for these research fields. 
107select field.fieldnum, field.title, count(interest.acnum)
108from field
109join interest
110on interest.fieldnum = field.fieldnum
111having count (interest.acnum) >= 10
112group by field.fieldnum, field.title;
113
114--5. Return the acnum of academic(s) who wrote the largest number of papers. You must NOT use MAX.
115-- An SQL query that lists all academics in decreasing order of their total number of papers 
116-- is incorrect. 
117SELECT academic.ACNUM, Count(author.PANUM) as papercount
118FROM academic
119LEFT JOIN author on academic.ACNUM = author.ACNUM
120group by academic.ACNUM
121order by papercount desc
122where rownum = 1;
123
124
125
126--6. Give the total number of academics who have not written any papers.  
127-- You must use the NOT IN operator.
128SELECT academic.ACNUM, Count(author.PANUM) as papercount
129FROM academic
130LEFT JOIN author on academic.ACNUM = author.ACNUM
131group by academic.ACNUM
132order by papercount;
133
134select panum, count(*)
135from author
136group by PANUM;
137
138
139--7. Find research fields whose title contains the string 'data' and has at least one interested 
140--academic from the department with deptnum 100. List the fieldnum and title of these research 
141-- fields. You must use the EXISTS operator. Ensure your query is case-insensitive. 
142select *
143from field
144where upper(title) = '%DATA%' 
145
146--8. List papers (panum) that have authors from the same department. Do NOT use any JOIN 
147-- operator.  Do NOT use any subqueries.
148
149--9. The SQL query below is meant to list research fields (fieldnum) where no academics from 
150-- ‘RMIT CS’ (deptnum=126) have an interest. But it is incorrect. Give the correct SQL query.
151-- select fieldnum
152-- from interest, academic
153-- where interest.acnum=academic.acnum and deptnum != 126;
154
155--10. 10.	Consider the SQL query given below. Give the English explanation for the output of 
156-- a) the subquery, and b) the whole SQL query. Literal explanation will receive zero marks. 
157-- select S.acnum
158-- from interest S 
159-- where upper(S.descrip) like '%LOGIC%'
160--   and exists (select fieldnum
161--                    from interest
162--                    where acnum = S.acnum 
163--                        and upper(descrip) not like '%LOGIC%');