INF3707 - Database Design And Implementation (INF3707)
Examen
INF3707-Database Design And Implementation EXAM PACK 2022 .
8 vues 0 achat
Cours
INF3707 - Database Design And Implementation (INF3707)
Établissement
University Of South Africa
INF3707-Database Design And Implementation EXAM PACK 2022 . Question 1 10 marks
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 ...
inf3707 database design and implementation exam pack 2022
inf3707 database design and implementation
inf3707 database design and implementat
École, étude et sujet
University of South Africa
INF3707 - Database Design And Implementation (INF3707)
Tous les documents sur ce sujet (44)
Vendeur
S'abonner
DoctorReinhad
Avis reçus
Aperçu du contenu
INF3707 EXAM PACK 2022
written by
StudyScene
www.stuvia.com
Downloaded by: sihlengubane | sihle13@gmail.com
Distribution of this document is illegal
, 2
Question 1 10 marks
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)
Question 2 40 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]
Downloaded by: sihlengubane | sihle13@gmail.com
Distribution of this document is illegal
, Stuvia.com - The study-notes marketplace
3 INF3720
OCT/NOV
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)
Question 3 19 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)
Question 4 31 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 1008. Use subqueries to
answer the question. (10 marks)
[Turn over]
Downloaded by: sihlengubane | sihle13@gmail.com
Distribution of this document is illegal
, Stuvia.com - The study-notes marketplace
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)
Downloaded by: sihlengubane | sihle13@gmail.com
Distribution of this document is illegal
Les avantages d'acheter des résumés chez Stuvia:
Qualité garantie par les avis des clients
Les clients de Stuvia ont évalués plus de 700 000 résumés. C'est comme ça que vous savez que vous achetez les meilleurs documents.
L’achat facile et rapide
Vous pouvez payer rapidement avec iDeal, carte de crédit ou Stuvia-crédit pour les résumés. Il n'y a pas d'adhésion nécessaire.
Focus sur l’essentiel
Vos camarades écrivent eux-mêmes les notes d’étude, c’est pourquoi les documents sont toujours fiables et à jour. Cela garantit que vous arrivez rapidement au coeur du matériel.
Foire aux questions
Qu'est-ce que j'obtiens en achetant ce document ?
Vous obtenez un PDF, disponible immédiatement après votre achat. Le document acheté est accessible à tout moment, n'importe où et indéfiniment via votre profil.
Garantie de remboursement : comment ça marche ?
Notre garantie de satisfaction garantit que vous trouverez toujours un document d'étude qui vous convient. Vous remplissez un formulaire et notre équipe du service client s'occupe du reste.
Auprès de qui est-ce que j'achète ce résumé ?
Stuvia est une place de marché. Alors, vous n'achetez donc pas ce document chez nous, mais auprès du vendeur DoctorReinhad. Stuvia facilite les paiements au vendeur.
Est-ce que j'aurai un abonnement?
Non, vous n'achetez ce résumé que pour €2,77. Vous n'êtes lié à rien après votre achat.
