本次Oracle代写是编写查询语句、函数、存储过程等对现有数据库进行检索

158337, S1, 2020 Project Instructions (Part B)
PART B
Queries (SQL/LINQ), Triggers, Procedures, Function, Cursor (PL
SQL) & MongoDB (NoSQL)
Download following two files provided in a compressed folder (PartB.zip) under the
Assignment heading from the Stream site:
University database file (UniversityDatabase.sql) – for Sections A, B and C.
The file provides code to create the required database in your account. Seven tables
created are – LOCATION, FACULTY, STUDENT, TERM, COURSE,
COURSE_SECTION, and ENROLLMENT.
Collection file (NoSQL.txt) – for Section D.
Use a new script file using some text editor (e.g. Notepad++) for writing your solution
code. Name your file correctly (e.g. SectionA.sql). Include query number (e.g. a)
along with the given query question (e.g. Write a query that will list…) as part of the
comment before writing solution code (SQL / PL/SQL / LINQ) in this file. Annotate
your code with suitable comments wherever necessary.
Use a word file for your project report (this can be later converted into a pdf should
you wish to submit a pdf report). Report will be mainly used for grading so will need
to transfer your final working code in this report as well. Here you also need to
include the output results of your code. The work included in the report should be
well formatted (proper alignment, appropriate column names, etc.) for readability
purposes. The code file will be used wherever necessary (for example may not use for
simple queries as can see the output but may use for the other code to ensure that it
works in different simulated situations).
For PL/SQL (triggers, procedures, etc.) besides the creation code, the output produced
from creation (e.g. procedure successfully created, etc.) also include the testing
158337, S1, 2020 Page 2 of 7 Project Instructions (Part B)
examples (e.g. execution of the procedure using some quality test data example(s) to
prove that procedure/trigger works the way it is intended to work).
All (SQL/LINQ) queries must only be based on the information provided in the
assignment question itself (e.g. Write a query that will list…). Do not use a different
criterion to arrive at the expected equivalent query result set. Also, do not manipulate
the query results by using ROWID to sort and get the first record, etc.
Please make sure the query results display the asked information and the columns that
are necessary to easily evaluate your query results. That doesn’t mean use * to display
everything – use your judgement and fulfil the requirements. Your code should work
correctly even when database is modified (e.g. added more records, deleted some,
modified some values, etc.).
Note: For all exercises asking you to display names, list first name and last name as a
single column instead of separate columns.
Remember to include all three the asked question (as comment), the code (SQL /PL
SQL /LINQ/ MapReduce) and its output in your report. Certain questions can have
other additional requirements, so meet those as well.
Section A (SQL Queries) (20 marks)
Note: For each of the questions (a)-(j) in Section A, write code using a SINGLE
statement ONLY (i.e. you cannot write two different select statements to arrive at an
answer; however, select can be used more than once in a statement i.e. only one semicolon in your code for one question). Use only the given criteria. We will either give
full or zero marks for each pf the questions in this section so make sure your answer is
fully correct.
a. Write a query that will list all the students who have had their 25th birthday (i.e.
aged 25 or over). Display students’ ages in complete years (e.g. 27) and in the
order of decreasing age. (1 mark)
b. Write a query that will list the total building capacity of various buildings. The
rooms with a capacity of less than five must be excluded when generating a
building’s total capacity. This list (in the increasing order of the total capacity)
should only contain the buildings with a total building capacity of 150 or over. (1
mark)
c. Write a query that will list all faculty supervisors and their respective students.
Arrange your list in the order of faculty supervisor’s names. (1 mark)
d. Write a query that will list all the faculty members (along with the building code
and room number) who are located in the (BUS)iness building. (1.5 marks)
158337, S1, 2020 Page 3 of 7 Project Instructions (Part B)
e. Write a query that will list students who enrolled in the courses offered in either
the Fall term of 2017 or the Fall term of 2018. Do not display the duplicate student
names in the output. (1.5 marks)
f. Write a query that will list all the students (along with their grade and course
details) who got at least B or better grade (i.e. B or A) in any of their courses. The
list should be in the order of student id. (2 marks)
g. Write a query listing the details of the faculty member(s) who supervise(s) the
highest number of students. The result should also display the number of students.
The query should also work in situations when more than one supervisor has
highest students (e.g. 2 supervisors each having 10 students, 10 being highest).
(3 marks)
h. Write a query that will list students enrolled with a total of 15 or more course
credit points. List your results in the decreasing order of total credit points. Do not
assume or hard code the value of the course credits (e.g. 3, 6, etc.). (3 marks)
i. Write a query that will list student(s) enrolled with the highest total course credit
points. The result should also display the number of courses a student is enrolled
for along with the highest total credit points. (3 marks)
j. Write a query that lists all the courses (with their course names) and the course
sections that are offered either on a (M)onday or at least four times a week. Also,
display the number of the days that the courses are offered (e.g. 5 days). (3 marks)
Note: In table Course_Section, the attribute c_sec_day lists weekdays, where the
first letter represents a weekday (e.g. M-Monday,……F-Friday; for Thursday R is
used). The course section weekdays are listed in the order M(onday) to (F)riday
i.e. Monday being the first.
158337, S1, 2020 Page 4 of 7 Project Instructions (Part B)
Section B (PL SQL) (26 marks)
Comprehensive testing examples needed for all PL/SQL (triggers, procedures, cursor
and function). You need to capture and show that you tested your code using good test
cases. May include some exception handling if deemed necessary.
k. Think of some useful business rules or situations where it would be appropriate for
your triggers to fire. Do not write triggers to do something that could be done
using other database design constraints (e.g., simple referential integrity checking,
default values to attributes, or simply saying record is being inserted, or an
attribute has a null value, etc.). Provide sensible and useful trigger examples and
do not use the already given or similar triggers for this question.
Start with first clearly explaining the context, purpose (what they will do) of your
triggers. Then provide the PL SQL code and the results. Triggers should be based
on the tables already provided. Do not unnecessarily create too many and/or
similar tables. Adding one or two tables may be okay – but justification needed.
Altering a table (adding a field) is fine.
Write two triggers one statement level and another row level. Display the
successful creation and running of the triggers. Please ensure that you also display
the relevant tables before and after (results of the trigger) the trigger is fired.
Remember to provide the purpose of your triggers (as stated in question l. below,
this helps us to evaluate your work against the stated requirements). (10 marks)
l. Write a trigger that does not allow more than two ‘Full’ ranked professors as part of
the faculty (For example, trigger should fire if a new (third) Full professor is added
or rank of one of the existing Associate professors is updated to Full). Provide
comprehensive test data and results to confirm that the trigger works. (4 marks)
m. Write a procedure to insert a new faculty record. The procedure should also
automatically calculate the faculty salary value. This calculated salary is 15% less
than the average salary of the existing faculty members.
Provide rest of the attribute values as input parameters. Execute your procedure to
insert at least one faculty record. (3 marks)
n. Write a trigger to check that when salary is updated for an existing faculty the raise
is not over 4%. (4 marks)
o. Write a cursor to list course sections for all the MIS courses (along with their
courses names and credits). (3 marks)
p. Write a function, which can be used to format faculty member’s salary to
$9,999,999.99. Do not hard code the exact salary datatype (i.e. your function
should work even if in future some minor changes are made to the salary data
type/size). Call this function in a SQL statement for displaying a faculty member’s
salary. (2 marks)
158337, S1, 2020 Page 5 of 7 Project Instructions (Part B)
Section C (LINQ Queries) (6 marks)
For (q) – (u) below, write queries using LINQ.
q. List all faculty who earn 80,000 or over. (1 mark)
r. List all courses that have MIS in their course number. (1 mark)
s. List all faculty and their location details. (1 mark)
t. Display the total number of rooms in each building. (1.5 marks)
u. Display total number of students supervised by each faculty in the order of faculty
last name. (1.5 marks)
158337, S1, 2020 Page 6 of 7 Project Instructions (Part B)
Section D (NoSQL) (8 marks)
Use the code provided in NoSQL.txt to create a collection of eleven rows called
dragons.