Description
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.
81 You may use temporary tables. If you choose to do that, use tables TEMP1, TEMP2, . . . , TEMP20.
82 (e) Replace “zk1” in script07.sql with your NetID.
83 (f) Do not remove the existing sample query.
Typeset: 2021-11-04T17:35:03Z MD5:
Page 3 of 4
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
126 clear but to elaborate it is asking you to specify which tables (if any) were changed and why. You may
127 get an error message pertaining to this question. If you do get it,
128 • copy the error into text07.txt, and
Typeset: 2021-11-04T17:35:03Z MD5:
Page 4 of 4
129 • explain in your own words what the error was, and do not just copy the message that Oracle gave
130 you, though you can also quote it. You may consult the Web, if you like.
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
Typeset: 2021-11-04T17:35:03Z MD5:
