oracle database chapter 6 hands on solution
Hands-On Assignments To perform the following assignments, refer to the tables in the JustLee Books database. 1. Create a sequence for populating the Customer# column of the CUSTOMERS table. When setting the startand increment values, keep in mind that data already exists in this table. The optons should be set to not cycle the values and not cache any values, and no minimum or maximum values should be declared. 2. Add a new customer row by using the sequenoe created in Queston 1. The only data currently available for the customer is as follows: last name Shoulders, first name - Frank, and zp 23567 3. Create a sequenoe that generates integers starting with the value 5. Each value should be hree less than the previous value generated. The lowest possible value should be 0, and the sequence shouldn't be allowed to cyde. Name the sequence MY_FIRST SEQ. 4. Issue a SELECT statement that displays NEXTVAL for MY FIRST_SEQ three times. Because the value isn't being placed in a table, use the DUAL table in the FROM clause of he SELECT statement What causes the error on the third SELECT? 5. Change the setting of MY FIRST_SEQ so that the minimum value that can be generated is -1000 6. A new table has been requested to support tracking automated emails sent to customers. Create the table and add data as described below. Tablename: email log Columns: emailid (numeric), emaildate (datetime), customer# (numeric) Primary key: emailid column, define as an Identity Column Add the following data rows and display resulting rows (if any errors occur, explain why the error is expected) - 1. Emaildate = current date, customer-1007 2. Emailid specity to use the column default value, emaildate- current date, customer# 1008 3. Emaild-25, emaildate-current date, customer#-1009 7. Create a private synonym that enables you to referenoe the MY ARST SEQ object as 8. Use a SELECT statement to view the CURRVAL of NUMGEN. Delete the NUMGEN 9. Create a bitmap index on the CUSTOMERS table to speed up queries that search for NUMGEN. synonym and MY FIRST SEQ. austomers based on their state of residence. Verity that the index exists, and then delete he index querying the data dictionary. Remove the index from the database and shipping dates). Create an index that might improve the performance of these queries 10. Create a B-tree index on the customer's Lastname column. Verity that the index exists by 11. Many queries search by the number of days to ship (number of days between the order Advanced Challenge Hands-On Assignments To perform the following assignments, refer to the tables in the JustLee Books database. 1. Create a sequence for populating the Customer# column of the CUSTOMERS table. When setting the startand increment values, keep in mind that data already exists in this table. The optons should be set to not cycle the values and not cache any values, and no minimum or maximum values should be declared. 2. Add a new customer row by using the sequenoe created in Queston 1. The only data currently available for the customer is as follows: last name Shoulders, first name - Frank, and zp 23567 3. Create a sequenoe that generates integers starting with the value 5. Each value should be hree less than the previous value generated. The lowest possible value should be 0, and the sequence shouldn't be allowed to cyde. Name the sequence MY_FIRST SEQ. 4. Issue a SELECT statement that displays NEXTVAL for MY FIRST_SEQ three times. Because the value isn't being placed in a table, use the DUAL table in the FROM clause of he SELECT statement What causes the error on the third SELECT? 5. Change the setting of MY FIRST_SEQ so that the minimum value that can be generated is -1000 6. A new table has been requested to support tracking automated emails sent to customers. Create the table and add data as described below. Tablename: email log Columns: emailid (numeric), emaildate (datetime), customer# (numeric) Primary key: emailid column, define as an Identity Column Add the following data rows and display resulting rows (if any errors occur, explain why the error is expected) - 1. Emaildate = current date, customer-1007 2. Emailid specity to use the column default value, emaildate- current date, customer# 1008 3. Emaild-25, emaildate-current date, customer#-1009 7. Create a private synonym that enables you to referenoe the MY ARST SEQ object as 8. Use a SELECT statement to view the CURRVAL of NUMGEN. Delete the NUMGEN 9. Create a bitmap index on the CUSTOMERS table to speed up queries that search for NUMGEN. synonym and MY FIRST SEQ. austomers based on their state of residence. Verity that the index exists, and then delete he index querying the data dictionary. Remove the index from the database and shipping dates). Create an index that might improve the performance of these queries 10. Create a B-tree index on the customer's Lastname column. Verity that the index exists by 11. Many queries search by the number of days to ship (number of days between the order Advanced Challenge
