Exam (elaborations)
INF3707 EXAM PACK 2022
INF3707 EXAM PACK 2022
1.1 Which Oracle Database management system is prescribed for this module? (2 marks)
1.2 How do you login as the system administrator on the Oracle SQL*plus interface? Provide the code.
(2 marks)
1.3 After you have successfully installed Oracle database, how do you op...
[Show more]
Preview 4 out of 155 pages
Uploaded on
October 21, 2022
Number of pages
155
Written in
2022/2023
Type
Exam (elaborations)
Contains
Questions & answers
$2.99
100% satisfaction guarantee
Immediately available after payment
Both online and in PDF
No strings attached
Stuvia.com - The study -notes marketplace INF3707 EXAM PACK 2022 UNIVERSITY EXAMINATIONS JUNE / JULY 2022 INF3707 DATABASE DESIGN AND IMPLEMENTATION Date: 05 July 2022 Duration: 2 hours Total marks: 100 Examiners: First: Dr B. Chipangura Second: Dr L. Motsi External: Dr P. Nkomo This is a closed book examination. Instructions to students 1. Unisa examination policies apply. 2. Answer all questions. 3. Study the tables and data in Annexure A. 4. The marks for each question are given in brackets next to the question. 5. Please answer the questions in order. If you want to do a question later, leave a blank space. 6. Pocket calculators are not allowed. 7. Pledge that you have neither given nor received aid on this examination (You must respond to this pledge when submitting your assignment.) 8. After writing the exam, upload your answer sheet in PDF format on myUnisa as per instructions provided to you in Exam Tutorial letter. Do not add any other form of protection on your document. Incorrect file formats or unreadable files will be assigned a zero mark. 9. Students who do not s ubmit their answer sheets within the scheduled session time will be marked as absent. Those who could not participate in the examination must formally apply for an aegrotat opportunity. This rule also applies to students who may be affected by load sheddin g. You should supply sufficient evidence in an application for an aegrotat due to load shedding. Note that supplementary students do not qualify for an aegrotat regardless of the circumstances. 10. You are not allowed to copy any text from the textbook or slides; other students’ notes or answers; templates, memos or example answers from any previous assignments, online tutors, the internet, or any other sources. All questions must be answered in your own, original words. All scripts will be put through a pl agiarism checking tool. Cases, where copying and pasting, patchwriting, plagiarism, collaboration or other authenticity issues are suspected, will receive a zero mark and the cases will be reported to the Student Disciplinary Section in DSAA for disciplina ry action. [Turn over] 1.1 Which Oracle Database management system is prescribed for this module? (2 marks) 1.2 How do you login as the system administrator on the Oracle SQL*plus interface? Provide the code. (2 marks) 1.3 After you have successfully installed Oracle database, how do you open the pluggable databases so that they can automatically open? (4 marks) 1.4 By default, Oracle pluggable databases remain closed and must be opened manually or set to automatically open. Which Oracle 18c XE service must you configure to do that? (2 marks) Assume that a table named MyTable exist in your database. MyTable has five columns Col#, Col1, Col2, Col3 and Col4. NAME NULL TYPE Col# NOT NULL Number Primary key Col1 NOT NULL VARCHAR2(2) Col2 NOT NULL VARCHAR2(10) Col3 NOT NULL VARCHAR2(10) Col4 NOT NULL VARCHAR2(10) 2.1 Write an SQL statement that enables you to view the structure of MyTable. (2 marks) 2.2 Write an SQL statement that allows you to see the data contained in all the columns of MyTable. (3 marks) 2.3 MyTable contains some columns that are marked as unused. Write an SQL code that drops all the columns marked as unused. (4 marks) 2.4 All the data entered into MyTable is not correct. Write an SQL code that deletes the data from the table but leaving it intact. (3 marks) 2.5 You realised that Col1 and Col4 are too small to accommodate large variables, write an SQL code that increases the size of Col1 from 2 to 17 and Col4 from 10 to 25. (5 marks) 2.6 Insert data into three columns of MyTable, by populating Col1, Col2 and Col4 with values A, B and D in that order. Do not insert any data into Col3, store a null value. (5 marks) 2.7 If the primary key of this table was created with the following statement: Col# Number GENERATED AS IDENTITY PRIMARY KEY; What output do you expect after inserting the following data into MyTable? Explain your answer. [Turn over] Question 1 10 marks Question 2 40 marks Insert into MyTable (Col#, Col1, Col2, Col3, Col4) value (001, ‘A’, ‘B’, ‘C’,’D’); (2 marks) 2.8 You created a sequence that generated some numbers for Col3 of MyTable. The sequence is as follows: Create sequence seq1 Increment by 10 Maxvalue 200 Cycle Nocache; After generating some numbers from the sequence a few times, you want to reinitialize the sequence so that it can reuse the numbers already generated.? Write an SQL code to achieve that. (5 marks) 2.9 Create a bitmap index on MyTable to speed up queries for searching information based on Col2. Verify that the index exists. Delete the index (11 marks) JustLee Books has recently hired a temporary salesman. The name of the new employee is John. The login credential for John should expire at the end of his contract. As a salesperson, John should be granted some object privileges on the JustLee Books database. 3.1 Create the user John with a password that expire, with the role of Sales_person. (5 marks) 3.2 As a salesperson, John has full responsibility for the CUSTOMERS table of the JustLee Books. Grant John object privileges to perform any activity on the customers table and rights to grant the privileges to other users. (4 marks) 3.3 John approached you on a Monday morning and reported that he does not remember his password. Reset John’s password. (3 marks) 3.4 Write a query to view all privileges that are assigned to the Sales_person role. (4 marks) 3.5 The contract for John has expired, revoke the Sales_person privilege granted to John and remove his user account. (3 marks) This question refers to the JustLee Books, see Annexure A for the structure of the tables 4.1 Determine which books customer Jake Lucas purchased. Perform the search using the customer name, not the customer number. If he has purchased multiple copies of the same book, duplicate the results. Generate the report using the (JOIN…..USING) keywords. (10 marks) 4.2 Determine, which orders had a higher total amount due more than order 10 08. Use subqueries to answer the question. (10 marks) [Turn over] Question 3 19 marks Question 4 31 marks Downloaded by: sihlengubane | sihle13@gmail.com Distribution of this document is illegal 4 INF3720 OCT/NOV 4.3 Determine the average retail price of books by publisher name and category. Include only the categories Children and Computer, and the groups with an average retail price greater than $50. Use the BOOKS and CATEGORY tables to create the query. (6 marks) 4.4 What is the retail price of the most expensive book written by Lisa White? Use the BOOKS, BOOKAUTHOR and AUTHOR tables to create the query. (5 marks) The end © UNISA
