INF3707 Assignment 3 solution 2024

INF3707 Assignment 3 solution 2024: To perform the following assignments, refer to the tables in the JustLee books database. 1.1 Create a sequence for populating the Custeomer# column of the CUSTOMERS table. When setting the start and the increment values, beer in mind that data already exist in the table. Hence, you need to know the last customer number stored in the database. The options should be set not to cycle the values and not to cache any values. No minimum or maximum values should be declared. (2 marks) 1.2 Add a new customer row by using the sequence created in Question 1.1. the only data currently available for the customer is as follows: last name = Shoulders, first_name = Frank, and zip = 23567. (3 marks) Question 2 Chapter 7 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. 2.1 create the user John with a password that expire with the role of sales_person (3 marks). 2.2 As a salesperson, John 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. John needs to have the right to grand the privileges to other users. (2 marks) Question 3 Chapter 8 To do the following questions, refer to tables created in the JLDB_B script at the beginning of the chapter. Give SQL statements for the following requests. 3.1 Which customers live in Georgia or New Jersey? Put the results in ascending order by last name. List each customer’s customer number, last name and state. (3 marks) 3.2 List authors whose last name contains the letter pattern “IN”. Put the results in order of last name, then first name. List each author’s last name and first name. (2 marks) Question 4 Chapter 9 (10 marks) Refer to tables in the JustLeee Books database. 4.1 Produce a list of all customers who live in the state of Florida and have ordered books about computers. (5 marks) 4.2 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. (5 marks) Question 5: Chapter 10 5.1 Determine the total profit generated by the book purchased on order 1002. Display the book title and profit. The profit should be formatted to display a dollar sign and two decimal places. Consider that the customer might not pay the full retail price and each item ordered can involve multiple copies. (3 marks) 5.2 Display a list of all book titles and percentage of mark-up for each book. The percentage of mark-up should be displayed as a whole number (that is, multiplied by 100) with no decimal position, followed by a percent sign (for example, 0.2793 = 28%). The percentage of mark-up should reflect the difference between the retail and cost amounts as a percent of the cost. (2 marks) Question 6 Chapter 11: Group functions List the customers living in Georgia or Florida who have recently placed an order totaling more than $80. (5 marks) Question 7: Chapter 12 Subqueries 7.1 Determine which customers placed orders for the least expensive book (in retails of regular retail price) carried by the JustLee Books. (5 marks) 7.2 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 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. (3 marks) Question 9 - Apply all what you know to answer the questions This question refers to Annexure A, the JustLee Books database. 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 VARCHAR2 3 CATDESC VARCHAR2 11 Here are some data entries for the CATEGORY table: CATCODE CATDESC BUS BUSINESS CHN CHILDREN COK COOKING COM COMPUTER 9.1 Create the CATEGORY table. (3 marks). 9.2 The table that you have created does not have a primary key, make the column CATCODE the primary key of the table. (3 marks) 9.3 The CATDESC column can accept null values, add a constraint to ensure that it does not allow null values. (2 marks) 9.4 Modify the CATDESC column from the current width of 11 characters to 20 characters. (2 marks) 9.5 Populate the Category table with the given data. Save the changes permanently. (8 marks) 9.6 Add a column to the BOOKS table called Catcode. Add a FOREIGN KEY constraint that requires all category codes entered in the BOOKS table to already exist in the CATEGORY table. (5 marks) 9.7 Delete the Category column from the BOOKS table. (2 marks)