Description
This is the first of two assignments dealing with SQL. Both assignments will use the same (or almost
62 the same) small database. This first assignment is simpler than the second one as this is the first
63 time you are actually writing and executing Oracle queries.
64 2.2 Assignments
65 1. (a) You are already supposed to know how to run SQL queries/commands on the Oracle
66 systems at CIMS. You were asked to familiarize yourself with and follow the instructions
67 in How_To_Use_Oracle_At_CIMS.pdf.
68 You were asked to do that earlier in order to save you time while working on this
69 homework.
70 So it is assumed that you know how to do that.
Typeset: 2021-10-24T23:07:53Z MD5:
Page 3 of 4
71 (b) Look at the files ER06.drawio and relational06.architect. They will help
72 you understand the database schema defined in the files script06.sql and
73 dataSetupScript06.sql. These files fully specify the application.
74 (c) Read script06.sql and dataSetupScript06.sql carefully. These scripts both define
75 and create the sample database and serves as the placeholder for putting in your solutions.
76 Look carefully over ANSWER0 there. It shows you how to insert a result of a query into
77 an empty table. It also uses the temp table TEMP0, just to demonstrate the usage of
78 temp tables.
79 (d) Input your queries into script06.sql after doing what is requested in Item 1e of Sec80 tion 2.2.
81 Please note which operations to use. Use only the operations that were introduced
82 in Unit 4 and the CREATE TABLE … AS …, used in the example of ANSWER0 in the
83 script. So base your queries on the operations of selection, projection, cartesian product,
84 minus, union, and intersection, with renaming and creation of new tables: creation, as
85 shown in the script. Do not, e.g., use JOINs of any kind. To reiterate: A solution that
86 used other operations (including those covered in Unit 5) will not be acceptable
87 In addition, use DISTINCT and ORDER BY as described below. If the output is to be
88 sorted in a different way, use an appropriate variant of ASC and DESC (ascending and
89 descending) and list the sorting instructions in the appropriate order,
90 For each query, unless something else is required by the query make sure to
91 i. Remove duplicates from the answer (unless requested otherwise); that’s what DIS92 TINCT does
93 ii. Sort the result in ascending order (unless requested otherwise); that’s what ORDER
94 BY does
95 This is extremely important to make the grading more manageable.
96 So, for example, assuming that you are going to select a and b and rename b as c, you
97 should actually explicitly use:
98 SELECT DISTINCT a, b AS c
99 …
100 ORDER BY a ASC, c ASC;
101 Do not rely the on default removal of duplicates and sorting order. Add the DISTINCT
102 and ORDER BY instructions even if you think that they are not necessary.
103 You may use temporary tables. If you choose to do that, use tables TEMP1, TEMP2,
104 . . . , TEMP20.
105 (e) Replace “zk1” in script06.sql with your NetID.
106 (f) Do not remove the existing sample query.
Typeset: 2021-10-24T23:07:53Z MD5:
Page 4 of 4
107 (g) Execute script06.sql, which internally calls dataSetupScript06.sql and produces a
108 spool file spool06.txt. The spool file must only include the details of the queries from
109 script06.sql. The spool file created will be a part of the submission.
110 Do not be concerned that there are more placeholder ANSWERs, than the queries that
111 you are supposed to produce.
112 For reference, spool06.txt corresponding to the given script06.sql (with one sample
113 query) is enclosed.
114 The requested queries are listed below. Your answers must work for every
115 instance of the database and not only for the specific instance provided. The
116 tables are named AnswerX, where “X” stands for the item number below. So, as the first
117 item is item number 1, the first table is Answer1.
118 1. Produce table AnswerX(RegNumber, TIN) that lists all the companies, sorted ascend119 ing by RegNumber first and descending by TIN. Note that the order of the attributes
120 is not the same in both places.
121 2. Produce table AnswerX(RegNumber, TIN) that lists all the big companies that work
122 in the ‘IT’ domain and are managed by people with familyName ‘Garcia’.
123 3. Produce table AnswerX(helperTIN, helpedTIN) that lists all the companies that help
124 each other. Note that (TIN1, TIN2) and (TIN2, TIN1) are not duplicates if TIN1 and
125 TIN2 are not the same.
126 4. Produce table AnswerX(RegNumber, TIN) that lists all the companies that work
127 in the ‘IT’ domain, and are helped by both companyOne with TIN ‘59515298’, and
128 companyTwo with TIN ‘51251930’.
129 5. Produce table AnswerX(RegNumber, TIN) that lists all the companies (big or small)
130 that do not help any company.
131 6. Produce table AnswerX(RegNumber, TIN) that lists all the companies, sorted as132 cending by RegNumber first and ascending by TIN, that do not work in the ‘IT’
133 industry.
134 7. Produce table AnswerX(RegNumber, TIN) that lists all the small companies that work
135 in ‘banking’ domain that work in the ‘IT’ domain, and are helped by small companies
136 in ‘IT’ domain.
137 2.3 What to submit
138 Please upload 2 files, named exactly as specified and in the format exactly as specified.
139 1. script06.sql, the script with your answers
140 2. spool06.txt, the resulting spool file
Typeset: 2021-10-24T23:07:53Z MD5:
