INF3707 EXAM PACK 2023

JUNE / JULY 2021 INF3707 DATABASE DESIGN AND IMPLEMENTATION Date: 05 July 2021 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 submit 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 shedding. 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 plagiarism 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 disciplinary action. S - The study-notes marketplace 2 INF3720 OCT/NOV [Turn over] 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. S - The study-notes marketplace Downloaded by: sihlengubane | Distribution of this document is illegal S - The study-notes marketplace 3 INF3720 OCT/NOV [Turn over] 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) S - The study-notes marketplace Downloaded by: sihlengubane | Distribution of this document is illegal S - 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) The end © UNISA S - The study-notes marketplace Downloaded by: sihlengubane | Distribution of this document is illegal S - The study-notes marketplace Question 1 1.1 Which Oracle Database management system is prescribed for this module? (2 marks) Oracle 18c XE 1.2 How do you login as the system administrator on the Oracle SQL*plus interface? Provide the code. (2 marks) Enter user-name: sys as sysdba Enter password: oraclepassword1234 1.3 After you have successfully installed Oracle database, how do you open the pluggable databases so that they can automatically open? (4 marks) SQL> ALTER PLUGGABLE DATABASE ALL OPEN; SQL> ALTER PLUGGABLE DATABASE ALL SAVE STATE; 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) You can start or stop the database using Windows Services: 1. From the Start menu, enter in the search field and press Enter. 2. Locate the OracleServiceXE service in the Services window. 3. Right-click the service name, and from the menu, select Start or Stop. 4. To set its startup properties, right-click and select Properties, and in the dialog box, select either Automatic, Manual, or Disabled from the Startup type list. Question 2 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) DESCRIBE MyTable; 2.2 Write an SQL statement that allows you to see the data contained in all the columns of MyTable. (3 marks) SELECT * FROM MyTable; 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) ALTER TABLE MyTable DROP UNUSED COLUMNS; 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) TRUNCATE TABLE mytable; S - The study-notes marketplace Downloaded by: sihlengubane | Distribution of this document is illegal S - The study-notes marketplace 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) ALTER TABLE MyTable MODIFY Col1 VARCHAR(17) MODIFY Col4 VARCHAR2(25); 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) Col3 is marked as NOT NULL. ALTER TABLE myTable MODIFY Col3 VARCHAR2(10) NULL; INSERT INTO MyTable(Col1, Col2, Col4) VALUES('A', 'B', 'D'); 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. INSERT INTO MyTable (Col#, Col1, Col2, Col3, Col4) VALUES(001, 'A', 'B', 'C','D'); (2 marks) Not allowed as primary keys have been set to be always auto-generated, therefore can not be directly inserted. SQL Error: ORA-32795: cannot insert into a generated always identity column 32795.0000 - "cannot insert into a generated always identity column" *Cause: An attempt was made to insert a value into an identity column created with GENERATED ALWAYS keywords. *Action: A generated always identity column cannot be directly inserted. Instead, the associated sequence generator must provide the value. 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) ALTER SEQUENCE seq1 RESTART START WITH 1; ???? 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. CREATE BITMAP INDEX Col2_idx ON MyTable(Col2); SELECT index_name, index_type FROM user_indexes WHERE table_name = 'MYTABLE'; --NOTE CAPITAL LETTERS. VERY NB DROP INDEX Col2_idx; S - The study-notes marketplace Downloaded by: sihlengubane | Distribution of this document is illegal S - The study-notes marketplace Question 3 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) CREATE USER john IDENTIFIED BY sales1234 PASSWORD EXPIRE; GRANT Sales_person TO john; 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) Assuming hr is the schema… GRANT ALL ON mers TO john WITH GRANT OPTION; 3.3 John approached you on a Monday morning and reported that he does not remember his password. Reset John’s password. (3 marks) ALTER USER john IDENTIFIED BY dontforget1234 PASSWORD EXPIRE; 3.4 Write a query to view all privileges that are assigned to the Sales_person role. (4 marks) SELECT name FROM system_privilege_map WHERE name = 'SALES_PERSON'; 3.5 The contract for John has expired, revoke the Sales_person privilege granted to John and remove his user account. (3 marks) REVOKE Sales_person FROM john; DROP USER john; Question 4 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) SELECT order#, , date FROM customers c JOIN orders o USING (customer#) JOIN orderitems oi USING (order#) JOIN books b USING (isbn) WHERE name = 'JAKE' AND ame = 'LUCAS'; S - The study-notes marketplace Downloaded by: sihlengubane | Distribution of this document is illegal S - The study-notes marketplace 4.2 Determine, which orders had a higher total amount due more than order 1008. Use subqueries to answer the question. (10 marks) SELECT #, SUM(quantity*paideach) "Total Amount Due" FROM orderitems oi GROUP BY # HAVING SUM(quantity*paideach) > (SELECT SUM(quantity*paideach) FROM orderitems WHERE order#=1008); 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) SELECT name, category, AVG(retail) FROM books JOIN publisher USING (pubid) WHERE category IN ('CHILDREN', 'COMPUTER') GROUP BY name, category HAVING AVG (retail) > 50; 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. SELECT MAX(retail) FROM books JOIN bookauthor USING (isbn) JOIN author USING (authorid) WHERE lname = 'WHITE' AND fname= 'LISA'; S - The study-notes marketplace Downloaded by: sihlengubane | Distribution of this document is illegal S - The study-notes marketplace INF3707 January/February 2021 1 Next UNIVERSITY EXAMINATIONS JANUARY / FEBRUARY 2021 INF3707 Database Design and Implementation Date: 03 February 2021 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. The marks for each question are given in brackets next to the question. 2. Please answer the questions in order. If you want to do a question later, leave a blank space. 3. Study the tables and data in Annexure A 4. Pocket calculators are not allowed 5. Answer all questions 6. Pledge that you have neither given nor received aid on this assignment/examination (You must respond to this pledge when submitting your assignment.) 7. After writing the exam, upload your answer sheet in PDF format on myUnisa as per instructions provided to you in Exam Tutorial letter S - The study-notes marketplace Downloaded by: sihlengubane | Distribution of this document is illegal S - The study-notes marketplace INF3707 January/February 2021 2 Next Question 1 30 marks For this question, please choose the best answer. Write the correct answer on your answer sheet. 1.1 Which one of the SQL statements would display the value 1890.55 as $1, 890.55? (2 marks) 1. Select to_char(1890.55, ‘$9,999V99’) from dual; 2. Select to_char(1890.55,’$0G000D00’) from dual; 3. Select to_char(1890.55, ‘$99,999D99’) from dual; 4. Select to_char(1890.55, ‘$99G999V99’) from dual; 1.2 You issued the following command: DROP TABLE BOOKS; Which three statements are true? (3 marks) A. All uncommitted transactions are committed B. All indexes and constraints defined on the table being dropped are also dropped C. Sequences used in the Books table becomes invalid D. The space used by the Books table is reclaimed immediately E. The Books table can be recovered using the rollback command F. The Books table is moved to the recycle bin Choose the best combination of answers. 1. A,C,F 2. A,D,E 3. B,C,D 4. A,B,F 1.3 Evaluate the SQL statement: TRUNCATE TABLE BOOKS; Which of the following three statements are true about the SQL statement? (3 marks) A. It releases the storage space used by the table B. It does not release the storage space used by the table C. You can roll back the deletion of rows after the statement executes D. You cannot roll back the deletion of rows after the statement executes E. An attempt to use DESCRIBE on the BOOKS table after the TRUNCATE statement executes will display an error F. You must be the owner of the table or have DELETE ANY TABLE system privilege to truncate the BOOKS table. Choose the best combination of answers. 1. F, B, C 2. B, D,E 3. D, E,F 4. A, D, F S - The study-notes marketplace Downloaded by: sihlengubane | Distribution of this document is illegal S - The study-notes marketplace INF3707 January/February 2021 3 Next 1.4 Which of the following commands eliminates only the user ELPOEZ’s ability to enter new books in the BOOKS table? (2 marks) 1. Revoke insert from elopez; 2. Revoke insert into books from elopez; 3. Revoke insert on books from elopez; 4. Drop insert into books from elopez; 1.5 Evaluate the following SQL statement: DELETE FROM order_items; If there are no other uncommitted transactions on the order_items table, which statement is true about DELETE statement? (2 marks) 1. It removes all the rows as well as the structure of the table 2. It removes all the rows in the table and the deleted rows cannot be rolled back 3. It removes all the rows in the table and the deleted rows can be rolled back 4. It would not remove the rows if the table has a primary key 1.6 Examine the data in the Orderitems tables ORDER# ITEM# QUANTITY ---------- ---------- ---------------- 1007 2 1 1007 3 1 1007 4 1 1008 1 2 1009 1 1 Evaluate the following query: select item#, avg(quantity) from orderitems having avg(quantity) > min(quantity)*0.5 group by item#; Which statement is true regarding the outcome of the above query? (2 marks) 1. It displays the item numbers with their average quantity where the average quantity is more than 0.5 the minimum quantity of that item in the table 2. It gives an error because the having clause should be specified after the GROUP BY clause 3. It gives an error because all the aggregate functions used in the HAVING clause must be specified in the SELECT list. 4. It displays the item numbers with their average quantity where the average quantity is more than 0.5 the overall quantity of the items in the table. S - The study-notes marketplace Downloaded by: sihlengubane | Distribution of this document is illegal S - The study-notes marketplace INF3707 January/February 2021 4 Next 1.7 Consider the structure of the CUSTOMERS table. SQL> desc customers Name Type ------------------- ---------------------------- CUSTOMER# NUMBER(4) LASTNAME VARCHAR2(10) FIRSTNAME VARCHAR2(10) ADDRESS VARCHAR2(20) Which statement adds a constraint that ensures the Lastname column of the CUSTOMERS table holds a value? (2 marks) 1. Alter table customers add constraint lastname_nn check customer_name is not null; 2. Alter table customers modify lastname constraint cust_name_nn not null; 3. Alter table customers modify constraint cust_name_nn check lastname is not null; 4. Alter table customers modify last_name constraints cust_name_nn is not null; 1.8 Which of the following will display the new retail price of each book as 20 percent less than its retail price? (3 marks) 1. SELECT title, retail + 0.20 "New Retail Price" FROM books 2. SELECT title, retail - 0.20 "New Retail Price" FROM books; 3. SELECT title, retail – (0.20 *retail) "New Retail Price" FROM books; 4. SELECT title, retail – (retail*1.20 "New Retail Price" FROM books; 1.9 Which of the following commands changes a table’s name from OLDNAME to NEWNAME? (2 marks) 1. REMANE table FROM oldname TO newname 2. ALTER TABLE oldname MODIFY TO newname 3. CREATE TABLE newname (SELECT * FROM oldname) 4. RENAME oldname TO newname 1.10 The UNIQUE constraint differs from the PRIMARY KEY constraint in what way? (2 marks) 1. The UNIQUE constraint does not allow NULL values. 2. The UNIQUE constraint allows NULL values. 3. The UNIQUE constraint can be created at either the column level or the table level. 4. The UNIQUE constraint ensures that a specific condition is true before a data value is added to a table. 1.11 Where does the Oracle server store information about objects in the database, including information about constraints? (2 marks) 1. in the data reference manual 2. in the data schema 3. in the data dictionary 4. in the data view S - The study-notes marketplace Downloaded by: sihlengubane | Distribution of this document is illegal S - The study-notes marketplace INF3707 January/February 2021 5 Next 1.12 Examine these statements: Create role sales; Grant update on customers to sales; Grant sales to user1, user2, user3; What does this set of SQL statements do? (2 marks) 1. The set of statements contain an error and does not work. 2. It creates a role called SALES, add the MODIFY privilege on the CUSTOMERS object to the role and gives the SALES role to the users 3. It creates a role called SALES, adds the UPDATE privilege on the CUSTOMERS object to the role and gives the SALES role to three users. 4. It creates a role called SALES, adds the UPDATE privilege on the CUSTOMERS object to the role and creates three users with the role. 1.13 Based on the structure of the ORDERITEMS table and the BOOK table: SQL> desc orderitems Name Null? Type -------------- -------- ---------------------------- ORDER# NOT NULL NUMBER(4) ITEM# NOT NULL NUMBER(2) ISBN VARCHAR2(10) QUANTITY NOT NULL NUMBER(3) PAIDEACH NOT NULL NUMBER(5,2) SQL> desc books Name Null? Type --------------- -------- -------------- ISBN NOT NULL VARCHAR2(10) TITLE VARCHAR2(30) PUBDATE DATE PUBID NUMBER(2) COST NUMBER(5,2) RETAIL NUMBER(5,2) DISCOUNT NUMBER(4,2) CATEGORY VARCHAR2(12) Which of the following commands will make certain that the ISBN entered in the ORDERITEMS table actually exists in the ISBN column of the BOOKS table? (3 marks) 1. ALTER TABLE orderitems ADD FOREIGN KEY isbn REFERENCES BOOKS TABLE (isbn); 2. ALTER TABLE orderitems MODIFY FOREIGN KEY (isbn) REFERENCES books(isbn); 3. ALTER TABLE orderitems CREATE FOREIGN KEY (isbn) REFERENCES books(isbn); 4. ALTER TABLE orderitems ADD FOREIGN KEY (isbn) REFERENCES books(isbn); S - The study-notes marketplace Downloaded by: sihlengubane | Distribution of this document is illegal S - The study-notes marketplace INF3707 January/February 2021 6 Next Question 2 40 marks This question refers to the JustLee Books database, see Appendix A. Learning form the E.R diagram and the contents of the BOOKS table, the contents of the Category column are the actual names for each category of books. This structure presents a problem if one user enters COMPUTER for the Computer category and another user enters COMPUTERS. To avoid this and other problems that might occur, the database designers have decided to create a CATEGORY table containing a code (CATCODE) and description (CATDESC) for each category. The structure for CATEGORY table should be as follows: Table name: CATEGORY Column Name Datatype Width CATCODE (Primary key) VARCHAR2 3 CATDESC (Not Null) VARCHAR2 11 Here are some data entries for the CATEGORY table: CATCODE CATDESC BUS BUSINESS CHN CHILDREN COK COOKING COM COMPUTER 2.1 Create the CATEGORY table. (5 marks) 2.2 Verify that the table category was successfully created and view the schema of the table. (2 marks) 2.3 Modify the CATDESC column from the current width of 11 characters to 20 characters. (2 marks) 2.4 Populate the Category table with the given data. (8 marks) 2.5 Add a column to the BOOKS table called CATCODE. Add a FOREIGN KEY constraint that references the CATEGORY table. (6 marks) 2.6 Set the CATCODE values for the rows in the BOOKS table based on each book’s current Category value. (8 marks) 2.7 Verify that the correct categories have been assigned in the BOOKS table. (2 marks) 2.8 Delete the Category column from the BOOKS table. (2 marks) 2.9 Jabulani is a newly appointed trainee in the database administration department. As the manager of the department, create the user Jabulani, with a password that expire and give him access to the CATEGORY table. Assign him privileges to query and add rows to the table. Jabulani should also be able to give other users the same privileges. (5 marks) S - The study-notes marketplace Downloaded by: sihlengubane | Distribution of this document is illegal S - The study-notes marketplace INF3707 January/February 2021 7 Question 3 30 marks 3.1 Use search pattern to find any book title with “A” for the second letter and “N” for the fourth letter. List each book’s ISBN and title. Sort the list by title in descending order. (4 marks) 3.2 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 JOIN ---USING ( ) keywords. (5 marks) 3.3 Based on the tables of the JustLee books database, determine the profit of each book sold to Jake Lucas using the actual price paid by the customer (not the book’s regular price). Sort the results by order date. If more than one book was ordered, sort the results by profit amount in descending order. Perform the search using customer name, not the customer number. Create the query using the keywords (JOIN….USING). (5 marks) 3.4 Create a view that list the name and phone number of the contact person at each publisher. Do not include the publisher’s ID in the view. Name the view CONTACT. (3 marks) 3.5 Change the CONTACT view so that no users can accidentally perform DML operations on the view. (3 marks) 3.6 Determine the total profit generated by sales to customer 1017. Note: quantity should be reflected in the total profit calculation. (5 marks) 3.7 Determine which books cost less than the average cost of other books in the same category. To perform this exercise, use subqueries. (5 marks) The End © UNISA 2021 S - The study-notes marketplace Downloaded by: sihlengubane | Distribution of this document is illegal S - The study-notes marketplace INF-JAN-E-1 1.1 Which one of the SQL statements would display the value 1890.55 as $1, 890.55? (2 marks) 1. Select to_char(1890.55, ‘$9,999V99’) from dual; 2. Select to_char(1890.55,’$0G000D00’) from dual; 3. Select to_char(1890.55, ‘$99,999D99’) from dual; 4. Select to_char(1890.55, ‘$99G999V99’) from dual; 1.2 You issued the following command: DROP TABLE BOOKS; Which three statements are true? (3 marks) A. All uncommitted transactions are committed B. All indexes and constraints defined on the table being dropped are also dropped C. Sequences used in the Books table becomes invalid D. The space used by the Books table is reclaimed immediately E. The Books table can be recovered using the rollback command F. The Books table is moved to the recycle bin Choose the best combination of answers. 1. A,C,F 2. A,D,E 3. B,C,D 4. A,B,F [FLASHBACK TABLE tablename TO BEFORE DROPcommand can restore table] [PURGE TABLE tablename to remove table from recycle bin] 1.3 Evaluate the SQL statement: TRUNCATE TABLE BOOKS; Which of the following three statements are true about the SQL statement? (3 marks) A. It releases the storage space used by the table B. It does not release the storage space used by the table C. You can roll back the deletion of rows after the statement executes D. You cannot roll back the deletion of rows after the statement executes E. An attempt to use DESCRIBE on the BOOKS table after the TRUNCATE statement executes will display an error F. You must be the owner of the table or have DELETE ANY TABLE system privilege to truncate the BOOKS table. Choose the best combination of answers. 1. F, B, C 2. B, D, E 3. D, E, F 4. A, D, F 1.4 Which of the following commands eliminates only the user ELPOEZ’s ability to enter new books in the BOOKS table? (2 marks) 1. Revoke insert from elopez; 2. Revoke insert into books from elopez; 3. Revoke insert on books from elopez; 4. Drop insert into books from elopez; S - The study-notes marketplace Downloaded by: sihlengubane | Distribution of this document is illegal S - The study-notes marketplace INF-JAN-E-1 1.5 Evaluate the following SQL statement: DELETE FROM order_items; If there are no other uncommitted transactions on the order_items table, which statement is true about DELETE statement? (2 marks) 1. It removes all the rows as well as the structure of the table 2. It removes all the rows in the table and the deleted rows cannot be rolled back 3. It removes all the rows in the table and the deleted rows can be rolled back 4. It would not remove the rows if the table has a primary key 1.6 Examine the data in the Orderitems tables ORDER# ITEM# QUANTITY ------ ----- -------- 1007 2 1 1007 3 1 1007 4 1 1008 1 2 1009 1 1 Evaluate the following query: SELECT item#, avg(quantity) FROM orderitems HAVING avg(quantity) > min(quantity)*0.5 GROUP BY item#; Which statement is true regarding the outcome of the above query? (2 marks) 1. It displays the item numbers with their average quantity where the average quantity is more than 0.5 the minimum quantity of that item in the table 2. It gives an error because the having clause should be specified after the GROUP BY clause 3. It gives an error because all the aggregate functions used in the HAVING clause must be specified in the SELECT list. 4. It displays the item numbers with their average quantity where the average quantity is more than 0.5 the overall quantity of the items in the table. 1.7 Consider the structure of the CUSTOMERS table. SQL> desc customers Name Type ----- ------------- CUSTOMER# NUMBER(4) LASTNAME VARCHAR2(10) FIRSTNAME VARCHAR2(10) ADDRESS VARCHAR2(20) Which statement adds a constraint that ensures the Lastname column of the CUSTOMERS table holds a value? (2 marks) 1. Alter table customers add constraint lastname_nn check customer_name is not null; 2. Alter table customers modify lastname constraint cust_name_nn not null; 3. Alter table customers modify constraint cust_name_nn check lastname is not null; 4. Alter table customers modify last_name constraints cust_name_nn is not null; S - The study-notes marketplace Downloaded by: sihlengubane | Distribution of this document is illegal S - The study-notes marketplace INF-JAN-E-1 1.8 Which of the following will display the new retail price of each book as 20 percent less than its retail price? (3 marks) 1. SELECT title, retail + 0.20 "New Retail Price" FROM books 2. SELECT title, retail - 0.20 "New Retail Price" FROM books; 3. SELECT title, retail – (0.20 *retail) "New Retail Price" FROM books; 4. SELECT title, retail – (retail*1.20 "New Retail Price" FROM books; 1.9 Which of the following commands changes a table’s name from OLDNAME to NEWNAME? (2 marks) 1. RENAME table FROM oldname TO newname 2. ALTER TABLE oldname MODIFY TO newname 3. CREATE TABLE newname (SELECT * FROM oldname) 4. RENAME oldname TO newname 1.10 The UNIQUE constraint differs from the PRIMARY KEY constraint in what way? (2 marks) 1. The UNIQUE constraint does not allow NULL values. 2. The UNIQUE constraint allows NULL values. p104 3. The UNIQUE constraint can be created at either the column level or the table level. 4. The UNIQUE constraint ensures that a specific condition is true before a data value is added to a table. 1.11 Where does the Oracle server store information about objects in the database, including information about constraints? (2 marks) 1. in the data reference manual 2. in the data schema 3. in the data dictionary. p28 4. in the data view 1.12 Examine these statements: Create role sales; Grant update on customers to sales; Grant sales to user1, user2, user3; What does this set of SQL statements do? (2 marks) 1. The set of statements contain an error and does not work. 2. It creates a role called SALES, add the MODIFY privilege on the CUSTOMERS object to the role and gives the SALES role to the users 3. It creates a role called SALES, adds the UPDATE privilege on the CUSTOMERS object to the role and gives the SALES role to three users. 4. It creates a role called SALES, adds the UPDATE privilege on the CUSTOMERS object to the role and creates three users with the role. S - The study-notes marketplace Downloaded by: sihlengubane | Distribution of this document is illegal S - The study-notes marketplace INF-JAN-E-1 1.13 Based on the structure of the ORDERITEMS table and the BOOK table: SQL> desc orderitems; Name Null? Type ------ ------- --------- ORDER# NOT NULL NUMBER(4) ITEM# NOT NULL NUMBER(2) ISBN VARCHAR2(10) QUANTITY NOT NULL NUMBER(3) PAIDEACH NOT NULL NUMBER(5,2) SQL> desc books; Name Null? Type --------------- -------- -------------- ISBN NOT NULL VARCHAR2(10) TITLE VARCHAR2(30) PUBDATE DATE PUBID NUMBER(2) COST NUMBER(5,2) RETAIL NUMBER(5,2) DISCOUNT NUMBER(4,2) CATEGORY VARCHAR2(12) Which of the following commands will make certain that the ISBN entered in the ORDERITEMS table actually exists in the ISBN column of the BOOKS table? (3 marks) 1. ALTER TABLE orderitems ADD FOREIGN KEY isbn REFERENCES BOOKS TABLE (isbn); 2. ALTER TABLE orderitems MODIFY FOREIGN KEY (isbn) REFERENCES books(isbn); 3. ALTER TABLE orderitems CREATE FOREIGN KEY (isbn) REFERENCES books(isbn); 4. ALTER TABLE orderitems ADD FOREIGN KEY (isbn) REFERENCES books(isbn); S - The study-notes marketplace Downloaded by: sihlengubane | Distribution of this document is illegal S - The study-notes marketplace INF-JAN-E-1 Question 2 40 marks This question refers to the JustLee Books database, see Appendix A. Learning form the E.R diagram and the contents of the BOOKS table, the contents of the Category column are the actual names for each category of books. This structure presents a problem if one user enters COMPUTER for the Computer category and another user enters COMPUTERS. To avoid this and other problems that might occur, the database designers have decided to create a CATEGORY table containing a code (CATCODE) and description (CATDESC) for each category. The structure for CATEGORY table should be as follows: 2.1 Create the CATEGORY table. (5 marks) CREATE TABLE CATEGORY ( CATCODE VARCHAR2(3) PRIMARY KEY, CATDESC VARCHAR2(11) NOT NULL ); 2.2 Verify that the table category was successfully created and view the schema of the table. (2 marks) DESCRIBE CATEGORY; 2.3 Modify the CATDESC column from the current width of 11 characters to 20 characters. (2 marks) ALTER TABLE CATEGORY MODIFY CATDESC VARCHAR2(20); 2.4 Populate the Category table with the given data. (8 marks) INSERT ALL INTO CATEGORY(CATCODE, CATDESC) VALUES ('BUS', 'BUSINESS') INTO CATEGORY(CATCODE, CATDESC) VALUES ('CHN', 'CHILDREN') INTO CATEGORY(CATCODE, CATDESC) VALUES ('COK', 'COOKING') INTO CATEGORY(CATCODE, CATDESC) VALUES ('COM', 'COMPUTER') SELECT * FROM DUAL; OR INSERT INTO CATEGORY(CATCODE, CATDESC) VALUES ('BUS', 'BUSINESS'); INSERT INTO CATEGORY(CATCODE, CATDESC) VALUES ('CHN', CHILDREN'); . . S - The study-notes marketplace Downloaded by: sihlengubane | Distribution of this document is illegal S - The study-notes marketplace INF-JAN-E-1 2.5 Add a column to the BOOKS table called CATCODE. Add a FOREIGN KEY constraint that references the CATEGORY table. (6 marks) ALTER TABLE BOOKS ADD CATCODE VARCHAR2(3); ALTER TABLE BOOKS ADD CONSTRAINT catcode_fk FOREIGN KEY(catcode) REFERENCES category(catcode); OR ALTER TABLE books ADD (catcode VARCHAR2(3), CONSTRAINT books_catcode_fk FOREIGN KEY(catcode) REFERENCES category(catcode)); 2.6 Set the CATCODE values for the rows in the BOOKS table based on each book’s current Category value. (8 marks) UPDATE BOOKS SET BOOKS.CATCODE = ( SELECT CATEGORY.CATCODE FROM CATEGORY WHERE CATEGORY.CATDESC = BOOKS.CATEGORY ) WHERE CATCODE IS NULL; OR UPDATE books SET catcode = 'BUS' WHERE category = 'BUSINESS'; UPDATE books SET catcode = 'CHN' WHERE category = 'CHILDREN'; UPDATE books SET catcode = 'COK' WHERE category = 'COOKING'; UPDATE books SET catcode = 'COM' WHERE category = 'COMPUTER'; COMMIT; --VERY NB!!! 2.7 Verify that the correct categories have been assigned in the BOOKS table. (2 marks) SELECT * FROM BOOKS; 2.8 Delete the Category column from the BOOKS table. (2 marks) ALTER TABLE BOOKS DROP COLUMN CATEGORY; 2.9 Jabulani is a newly appointed trainee in the database administration department. As the manager of the department, create the user Jabulani, with a password that expire and give him access to the CATEGORY table. Assign him privileges to query and add rows to the table. Jabulani should also be able to give other users the same privileges. CREATE USER jabulani IDENTIFIED BY jabu123 PASSWORD EXPIRE; GRANT SELECT, INSERT ON CATEGORY TO jabulani WITH GRANT OPTION; S - The study-notes marketplace Downloaded by: sihlengubane | Distribution of this document is illegal S - The study-notes marketplace INF-JAN-E-1 Question 3 30 marks 3.1 Use search pattern to find any book title with “A” for the second letter and “N” for the fourth letter. List each book’s ISBN and title. Sort the list by title in descending order. (4 marks) SELECT isbn, title From BOOKS WHERE TITLE LIKE '_A_N%' ORDER BY TITLE DESC; 3.2 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 JOIN ---USING ( ) keywords. (5 marks) SELECT , ory FROM BOOKS b JOIN PUBLISHER pub USING(pubid) WHERE category IN ('CHILDREN', 'COMPUTER') GROUP BY ory, HAVING AVG(l) > 50; 3.3 Based on the tables of the JustLee books database, determine the profit of each book sold to Jake Lucas using the actual price paid by the customer (not the book’s regular price). Sort the results by order date. If more than one book was ordered, sort the results by profit amount in descending order. Perform the search using customer name, not the customer number. Create the query using the keywords (JOIN….USING). (5 marks) SELECT , date, (l - ) profit FROM books b JOIN orderitems oi USING(isbn) JOIN orders o USING(order#) JOIN customers c USING(customer#) WHERE (name = 'JAKE' and ame = 'LUCAS') ORDER BY date, profit DESC; 3.4 Create a view that list the name and phone number of the contact person at each publisher. Do not include the publisher’s ID in the view. Name the view CONTACT. (3 marks) CREATE VIEW CONTACT AS SELECT name, phone FROM publisher; 3.5 Change the CONTACT view so that no users can accidentally perform DML operations on the view. (3 marks) CREATE OR REPLACE VIEW CONTACT AS SELECT name, phone FROM publisher WITH READ ONLY; 3.6 Determine the total profit generated by sales to customer 1017. Note: quantity should be reflected in the total profit calculation. (5 marks) SELECT SUM((retail-cost)*quantity) total_profit FROM books JOIN orderitems USING(isbn) JOIN orders USING(order#) WHERE customer# = 1017; S - The study-notes marketplace Downloaded by: sihlengubane | Distribution of this document is illegal S - The study-notes marketplace INF-JAN-E-1 3.7 Determine which books cost less than the average cost of other books in the same category. To perform this exercise, use subqueries. (5 marks) SELECT , ory, FROM books a, (SELECT category, AVG(cost) averagecost FROM books GROUP BY category) b WHERE ory = ory AND < gecost; S - The study-notes marketplace Downloaded by: sihlengubane | Distribution of this document is illegal S - The study-notes marketplace UNIVERSITY EXAMINATIONS October / November 2020 INF3707 Database Design and Implementation Exam Postponed. New date to be announced S - The study-notes marketplace Downloaded by: sihlengubane | Distribution of this document is illegal S - The study-notes marketplace INF3707 Oct/Nov 2020 Question 1 1.1 i) Sysdate cannot be used within a check constraint. ii) You would need to insert a date within the check constraint because sysdate doesn’t work, thus you would have to enter the full date required for the check constraint. 1.2 INSERT INTO MyCustomer01 VALUES (‘1000’, ‘Jan’, ‘Zwane’); 1.3 GRANT PRIVILEGE select ON MyCustomer01 TO public; 1.4 SELECT TO_CHAR(2090.55,‘$2,090.55’) FROM DUAL 1.5 Firstly, it will search for the pubid and category of all books in the books table which have the word ORACLE in its title and then use this information to display the isbn and title of those books which the pubid and category match those from the subquery. 1.6 SELECT SUBSTR(PHONE,9,4) FROM PUBLISHER WHERE PUBID = 2; 1.7 SELECT isbn, title, (MAX(retail)/0.05), retail FROM BOOKS GROUP BY isbn, title; S - The study-notes marketplace Downloaded by: sihlengubane | Distribution of this document is illegal S - The study-notes marketplace INF3707 Oct/Nov 2020 1.8 Alter table MyCustomers02 ADD CONSTRAINT MyCustomers02_LastName_nn NOT NULL (LASTNAME); 1.9 i) It returns an error because the datatype conversion in the SELECT list does not match the data type conversion in the GROUP BY clause ii) SELECT to_char(orderdate, 'YY'),sum(shipcost) FROM orders GROUP by to_char(orderdate,'YY'); 1.10 SELECT sum(referred) FROM CUSTOMERS WHERE referred NOT NULL; Question 2 2.1 CREATE TABLE MyCustomers03 (myCUSTOMER# NUMBER(4), myLASTNAME VARCHAR2(10) NOT NULL, myFIRSTNAME VARCHAR2(10) NOT NULL, myADDRESS VARCHAR2(20), myCITY VARCHAR2(2), myZIP VARCHAR2(5) ); 2.2 Alter table MyCustomers03 ADD CONSTRAINT MyCustomers03_Customer#_pk PRIMARY KEY(CUSTOMER#); S - The study-notes marketplace Downloaded by: sihlengubane | Distribution of this document is illegal S - The study-notes marketplace INF3707 Oct/Nov 2020 2.3 INSERT INTO MyCustomers03 SELECT Customer#, Lastname, Firstname, Address, City, zip FROM CUSTOMERS; 2.4 Alter table MyCustomers03 ADD (province VARCHAR2(3), email VARCHAR2(30)); 2.5 CREATE INDEX my_customers_state_idx ON MyCustomers03(myprovince); SELECT * FROM USER_INDEXES WHERE TABLE_NAME = ‘MyCustomers03’; DROP INDEX my_customers_state_idx; 2.6 CREATE VIEW Contact As SELECT myLASTNAME, myFIRSTNAME, myemail FROM MyCustomers03; SELECT * FROM Contact; S - The study-notes marketplace Downloaded by: sihlengubane | Distribution of this document is illegal S - The study-notes marketplace INF3707 Oct/Nov 2020 Question 3 3.1 SELECT mer#, c.Lastname, FROM Customers c RIGHT OUTER JOIN orders o USING(customer#) WHERE sum(order# > 1); 3.2 SELECT Customer#, SUBSTR(zip,3,2), INSTR(Customer#,3) FROM CUSTOMERS; Question 4 4.1 SELECT title, category, avg(retail) FROM BOOKS JOIN PUBLISHER USING(pubid) WHERE category IN (‘Children’, ‘Computer’) Group by title, category Having avg(retail) > 50; 4.2 SELECT title, MIN(retail) FROM BOOKS JOIN BOOKAUTHOR USING(isbn) WHERE authorid = ‘S100’ GROUP BY title; 4.3 SELECT title, retail WHERE (SELECT retail > avg(retail)) FROM BOOKS;