Description

3 1 General Instructions 1
4 2 Homework 2
5 2.1 Description . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
6 2.2 Assignments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2 7 2.3 What to submit . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
8 1 General Instructions
9 1. You need to follow carefully the instructions for the assignment as written below.
10
11
12
13
14
15 2.
16 3.
17
18
19
20
21
22 4.
23
24
25 5.
26
27
28
29 6.
30 31
32
33 7.
34
35
36 8.
37
38
39
40
41
It is advisable to print out this document and check off various points as they are addressed. It is easy to miss something when switching between the assignment and the solution on a single screen, especially on a laptop with a relatively small screen.
If you do not have access to a printer, at least review your solution before the submission to make sure that you did all that you were requested to do and only what you were requested to do.
If you want to refer to a specific line in this document, refer to the small numbers in the left margin.
If you have questions concerning this homework email Shubham Srivastava, mailto:ss14687@nyu.edu, in the way specified in the course description.
If you still have unresolved questions, email Zvi Kedem, mailto:zk1@nyu.edu, including all relevant correspondence with the assistant(s) listed above, in the way specified in the course description.
It is important that you follow the directions precisely. Also, please check that you submitted what you intended to submit, as you are responsible for making sure of that. The best way to do is to download what you submitted to check that.
And the best way to manage your work is to dedicate a folder/directory to each assignment.
Do not email your submission to any of the assistants. If you did not submit your solution on time, please email Zvi Kedem, mailto:zk1@nyu.edu, in the way specified in the course description with an explanation of what has happened, and if you have a solution (possibly partial), email the solution also.
44 Do not communicate with any of the graders concerning a late submission.
48 one-by-one case. All such requests need to be addressed to Zvi Kedem, mailto:zk1@nyu.edu in the way 49 specified in the course description, with a reason for such a request.
50 2 Homework
51 Reminder: If you are not officially registered in the class and the class does not show on Albert for you, do not 52 submit any assignments.
53 Please read and follow carefully the instructions in Section 1.
54 2.1 Description
55 This is the second of two assignments dealing with SQL. Both assignments use the same small database with very 56 similar instances.
57 2.2 Assignments
58 1. (a) You are already supposed to know how to run SQL queries/commands on the Oracle sys59 tems at CIMS. You were asked to familiarize yourself with and follow the instructions in
60 How_To_Use_Oracle_At_CIMS.pdf.
61 You were asked to do that earlier in order to save you time while working on this homework. 62 So it is assumed that you know how to do that.
63 (b) Look at the files ER07.drawio and relational07.architect. They will help you understand the
64 database schema defined in the files script07.sql and dataSetupScript07.sql. These files fully 65 specify the application.
66 (c) Read script07.sql and dataSetupScript07.sql carefully. They both define and create the sample 67 database and script07.sql serves as the placeholder for putting in your solutions.
68 Look carefully over ANSWER0 there. It shows you how to insert a result of a query into an empty 69 table. It also uses the temp table TEMP0, just to demonstrate the usage of temp tables.
70 (d) Input your queries into script07.sql after doing what is requested in Item 1e of Section 2.2.
71 For each query, unless something else is required by the query make sure to
72 i. Remove duplicates from the answer (unless requested otherwise); that’s what DISTINCT does
73 ii. Sort the result in ascending order (unless requested otherwise); that’s what ORDER BY does
74 This is extremely important to make the grading more manageable.
75 So, for example, assuming that you are going to select a and b and rename b as c, you should actually 76 explicitly use:
77 SELECT DISTINCT a, b AS c
78 …
79 ORDER BY a ASC, c ASC;
80 Do not rely the on default removal of duplicates and sorting order.
82 (e) Replace “zk1” in script07.sql with your NetID.
83 (f) Do not remove the existing sample query.
84 (g) Notice that the script07.sql internally executes dataSetupScript07.sql followed by the queries 85 you write in script07.sql, and produces a spool file spool07.txt that contains just the details of the 86 queries specified in the script script07.sql. The spool should only contain the details while running 87 script07.sql and not the data setup script. The spool file created will be a part of the submission. 88 Do not be concerned that there are more placeholder ANSWERs that the queries that you are supposed
89 to produce.
90 For reference, spool07.txt corresponding to the given script07.sql (with one sample query) is
91 enclosed.
92 The requested queries are listed below. Your answers must work for every instance of
93 the database and not only for the specific instance provided. Do not use subqueries to 94 produce your output. The tables are named AnswerX, where “X” stands for the item number below. 95 So, as the first item is item number 1, the first table is Answer1.
96 1. Produce table AnswerX(RegNumber, TIN) that lists all the big companies that do not have a
97 manager.
98 2. Produce table AnswerX(TIN, SalaryRatio) that lists the TIN of the big companies that spend more 99 than 10 percent of their value on manager salary.
100 3. Produce table AnswerX(TIN) that lists the TIN of companies that work in at least 3 domains and 101 have a TIN greater than or equal to 20000000.
102 Your answer should work if the question were phrased with 500 instead of 3 if you replace 3 by 500
103 in your answer.
104 4. Produce table AnswerX(TIN) that lists the companies that work at least in all the domains that 105 company with TIN ‘99448276’ works in.
106 5. Write and execute a query to delete from table Company the company with TIN ‘74939103’. Please 107 do what’s requested in Item 1h of Section 2.2.
108 6. Company ‘95499546’ has hired a new manager with PersonalName ‘Mickey’, FamilyName ‘Aldrin’with 109 salary 100000. Write and execute a query to add/change the name of the store’s manager in our 110 records wherever necessary.
111 Please do what’s requested in Item 1h of Section 2.2.
112 7. Company ‘35983220’ has made huge mistakes in the recent past, and lost a lot of its value. As 113 a result, they have to fire their current manager. They would now be a small company. Write 114 and execute a query to remove the company from big companies, and make the company a small
115 company.
116 Please do what’s requested in Item 1h of Section 2.2.
117 8. Write and execute a query to create a record of company with TIN ‘74939103’ helping company 118 with TIN ‘90309251’.
119 Please do what’s requested in Item 1h of Section 2.2.
120 9. MySQL does not have a very useful operator we studied: MINUS. Review the definition of MINUS
121 from Unit 05.
122 Do not use MINUS for this question. Produce table AnswerX(A, B), which is R MINUS S. Use 123 LEFT OUTER JOIN in your answer. Tables R and S are defined in the script.
124 (h) Examine the database and fill out text07.txt. For each “AnswerX” (of course replacing “X” with the 125 correct value), supply the information requested between the two square brackets, in [ ]. The form is
128 • copy the error into text07.txt, and
131 If you do not get an error, state so.
132 2.3 What to submit
133 Please upload 3 files, named exactly as specified and in the format exactly as specified.
134 1. script07.sql, the script with your answers
135 2. spool07.txt, the resulting spool file
136 3. text07.txt, the file requested in Item 1h of Section 2.2