INF3707 Assignment 3 2023.

INF3707 Assignment 3 2023.100% CORRECT questions, answers, workings and explanations. for assistance. ........... Question 1: Chapter 6 (11 marks) 1.1 Create a sequence that generates integers starting with the value 5. Each value should be three less than the previous value generated. The lowest value should be 0, and the sequence should not be allowed to cycle. Name the sequence MY_FIRST_SEQ. (5 marks) 1.2 Issue a SELECT statement that displays NEXTVAL for MY_FIRST_SEQ three times. Since the value is not being stored in the table, use dual table in the FROM clause of the SELECT statement. What caused the error on the third SELECT? (2 marks) 1.3 Evaluate the following SQL commands: CREATE SEQUENCE ord_seq Increment by 10 Start with 120 Maxvalue 9999 Nocycle; CREATE TABLE ord_items (ord_no NUMBER (4) DEFAULT ord_seq.NEXTVAL NOT NULL, Item_no NUMBER(3), qty NUMBER(3) CHECK (qty BETWEEN 100 AND 200), expiry_date date CHECK (expiry_date > SYSDATE), CONSTRAINT its_pky PRIMARY KEY (ord_no, item_no), CONSTRAINT ord_fky FOREIGN KEY(ord_no) REFERENCES orders(order#) ); The command to create the table fails. What caused the failure? Rewrite the CREATE Table ord_items code to correct the error. (4 marks) Question 2: Chapter 7 (14 marks) JustLee Books has recently hired a contract salesperson. The name of the new employee is Peter. The login credential for Peter should expire at the end of his contract. As a salesperson, Peter should be granted some object privileges on the JustLee Books database. 2.1 Create the user Peter with a password that expire with the role of salesperson. (4 marks) 2.2 As a salesperson, Peter has full responsibility for the CUSTOMERS table of the JUSTLee Books and need to have object privileges to perform any activity on the customers’ table. Peter needs to have the right to grand the privileges to other users. (2 marks) 2.3 Peter approached you on a Monday morning and reported that he does not remember his password. Reset Peter’s password. (2 marks) 2.4 Write a query to view all privileges that are assigned to the Salesperson role. (3 marks) 2.5 The contract for Peter has expired, revoke the Salesperson privilege granted to Peter and remove his user account. (3 marks) Question 3: Chapter 8 (7 marks) To perform the following questions, refer to the tables in the JustLee books database. 3.1 A manager at JustLee Books requested a list of the titles of all books generating a profit of at least $10.00. The manager wants the books listed in descending order, based on each books’ profit. (3 marks) 3.2 A customer service representative is trying to identify all books in the Computer or Family life categories published by Publisher 1 or Publisher 3. However, the results should not include any book selling for less than $45.00. Write SQL code to achieve this query. (4 marks) Question 4: Chapter 9 (9 marks) Refer to tables in the JustLeee Books database. 4.1 Determine which books customer Jake Lucas purchased. Perform the search using the customer’s name, not the customer number. If he has purchased multiple copies of the same book, do not duplicate the results. Generate the report using the (JOIN…..USING) keywords. (5 marks) 4.2 Which books were written by an author with the last name Adams? Perform the search using the author’s name. Generate the report using the (JOIN…USING) keywords. (4 marks) Question 5: Chapter 10 (4 marks) 5.1 Display the current day of the week, hour, minutes, and seconds of the current date setting of the computer you are using. (2 marks) 5.2 Create a list of all book titles and calculate the cost of each book. Precede each book’s cost with an asterisk so that the width of the displayed cost field is 12. (2 marks) Question 6: Chapter 11 (5 marks) Determine, which orders had a higher total amount due than order 1008. Use subqueries to answer the question. (5 marks) Question 7: Chapter 12 (23 marks) 7.1 Determine which books cost less than the average cost of other books in the same category. Use subqueries to answer the question. (5 marks) 7.2 Determine which books were shipped to the same state as order 1014. Use subqueries to answer the question. (4 marks) 7.3 List the shipping city and state for the order that had the longest shipping delay. (4 marks) 7.4 Determine which customers placed orders for the least expensive book (in terms of regular retail price) carried by the JustLee Books. (5 marks) 7.5 Determine the number of different customers who have placed an order for books written or co-authored by James Austin. (5 marks) Question 8: Chapter 13: (5 marks) 8.1 Create a view that lists 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. (2 marks) 8.2 Change the Contact view so that no user can accidentally perform DML operations on the view. (2 marks) 8.3 Create a view called Homework 13 that includes the columns named Col1, Col2 from the FirstAtatempt table. Make sure the view is created even if the FirstAttempt table does not exist. View the structure of homework13. (1 marks) Question 9 (22 marks) 9.1 Create the following table. (10 marks) Table Name: MyCustomers NAME NULL TYPE -------- ------ --------- CUSTOMER# NUMBER (4) LASTNAME NOT NULL VARCHAR2(10) FIRSTNAME NOT NULL VARCHAR2(10) ADDRESS VARCHAR2(20) CITY VARCHAR2(2) ZIP VARCHAR2(5) 9.2 The MyCustomers table does not have a primary key, make CUSTOMER# the primary key. (1 marks) 9.3 Populate the MyCustomers table with data from the CUSTOMER table of the JustLee Books. (2 marks) 9.4 The MyCustomers table does not have the columns for capturing the client’s e_mail address as well as the Province. Add the two columns to the MyCustomers table. (2 marks) 9.5 Create a bitmap index and name it my_customers_state_idx on the MyCustomers table to speed up queries for searching customers based on their province of residence. Verify that the index is created and then delete the index. (5 marks) 9.6 Create a view based on the MyCustomers table that lists the customer’s last name, first name and email. Do not include the customer# in the view. Name the view Contact. View the contents of the view. (2 marks) The END