Find Jobs
Hire Freelancers

539479 Labs 4-7 and Projects 4-7

N/A

En curso
Publicado hace más de 12 años

N/A

Pagado a la entrega
Lab 4-7 Building the Physical Model Scenario/Summary Lab #4-7 will introduce the various aspects of the SQL select statement and the methods of retrieving data from the database tables. The lab will utilize a set of tables found in the script file ([login to view URL]) found in the Doc Sharing area of the website. You will need to download the file, then run the script in your SQL*Plus session. These tables will be used for the remaining labs in this class. The SELECT statement is the primary means of extracting data from database tables, and allows you to determine exactly which data you want to extract by means of different comparison operators used in the WHERE clause. This includes the use of specific "wild card" characters which allow you to search for character or number patterns within the data. You can also perform mathematical expressions within the SELECT statement to create derived output. The ORDER BY clause allows you to sort the output data in either ascending (the default) or descending order. Lab #4 will explore all of these applications of the SELECT statement. General Lab Information and Considerations Each query in the script file you will create must be numbered (use - -1 comments for numbering) and listed in order. The SQL for the following exercises should be written using Notepad and run in SQL*Plus. Read each problem carefully and follow the directions as stated. A Clean Script File: A script file is meant to be like a program. The file can be run every time the code needs to be executed without having to retype the code again each time. For this reason, it is important that there are no errors in the code inside the file. You can go back and forth between Notepad and Oracle when creating your script file to check your queries and verify if they work or not, but you do not want to create your final output file until after you have verified that everything in your script is correct by running it, in its entirety at least once and viewing the output. Once this has been done, you can create your final output file, with echo on to create the document you can turn in with your lab. Remember in using a spool session, you must type "SPOOL OFF" at the SQL> prompt after your script stops spooling to capture all of your data! Lab Do's and Don't's • Do Not include the [login to view URL] as part of your lab script. o Do use Notepad to write your query script file. • Do Not write your queries in Word. o Do test each query before moving on to the next. • Do Not include extra queries for a problem unless the problem explicitly asks for more than one query. o Do test your queries before creating your final output file. • Do Not turn in a script file that has queries with errors. o Do number each query using - -1 comment notation. • Do Not start your query on the same line as the comment. o Do remember to check your final output and script file for accuracy. • Do Not turn in your lab without first checking your output file to verify that it is correct. Things to keep in mind: If you are not sure of the table names in your user schema, you can use the following select statement to list them. SELECT * FROM TAB; If you want to know the name of the columns in a particular table, you can use the following command to list them. DESC Making a script file containing a series of describe statements for each table and then spooling the output will give you a listing of all the tables with column names. Be sure to review and verify your final output when you are finished. DO NOT assume anything. Write queries for each of the stated problems in the steps below that will return a result set of data to satisfy the requirements. When finished, your script file should have a total of 13 queries and your resulting output file should show both the query and result set for each. Deliverables The deliverable for this lab will include: 1. Your script file with the 13 queries in it. Be sure your name, course number, and lab number are in a comment area at the top of your file. 2. An output file created using SET ECHO ON showing both the SQL code and the results. 3. Both documents are to be zipped into a single file before submitting to the iLab Dropbox for Week 4. L A B S T E P S STEP 1: Using the BOOKS table, write a query that will list the categories for the books in inventory. List each category only once in your result set. STEP 2: Using the BOOKS table, write a query that will list the title and publisher ID for each book in the table. Use the column heading of "Publisher ID" for the pubid field. STEP 3: Using the BOOKS table, write a query that will list the book title, retail price, and the amount of markup for each book. The amount of markup is the retail price minus the cost. Use the column heading “Price Markup” for the arithmetic expression column. STEP 4: Using the BOOK_CUSTOMER table, write a query that will list the customer's first name, last name, and city for those customers living in zip code 31206. STEP 5: Using the BOOK_ORDER table, write a query that will list everything about the orders placed prior to April 2, 2009. STEP 6: Using the BOOK_ORDER table, write a query that will list everything about the orders that have not been shipped yet. STEP 7: Using the BOOK_CUSTOMER table, write a query using the AND and OR operators that will list the customer information for those customers living in either Florida or New Jersey who have not been referred by another customer. STEP 8: Using the BOOKS table, write a query that will list all information about those books that are not computer books and do not cost more than $30.00 retail. STEP 9: Using the AUTHOR table, write a query that will list all information about authors whose first name ends with an “A”. Put the results in descending order of last name, and then ascending order by first name. This should be done using a single query. STEP 10: Using the BOOK_ORDER table, write a query using the > and < operators that will list the orders that were placed between April 1, 2009 and April 4, 2009. Only show the orders for April 2nd and 3rd in your result set. STEP 11: Using the BOOK_ORDER table, write a query that will list the orders that were placed between April 2, 2009 and April 4, 2009 including those placed on the 2nd and 4th. Use a different approach (operator) in writing this query than used in the query for #10, that is, do not use > and < signs in your query. STEP 12: Using the BOOKS table, write a query that will list the book title, publisher ID, and published date for all books that were published by publisher 4 or after January 1, 2001. Order the results in ascending order by publisher ID and give the publish date and publisher ID columns meaningful titles. STEP 13: Many organizations use percentage of markup (e.g., profit margin) when analyzing financial data. To determine the percentage of markup for a particular item, simply subtract the cost for the item from the retail price to obtain the dollar amount of profit, and then divide the profit by the cost of the item. The resulting solution is then multiplied by 100 to determine the percent of markup. Using a SELECT statement, display the title of each book and its percent of markup. For the column displaying the percent of markup, use “Markup %” as the column heading. This is the end of lab #4 Lab 5 Retrieving data from Multiple Tables L A B O V E R V I E W Scenario/Summary Lab 5 deals with writing queries using multiple tables as covered in the reading and lecture material this week. In some cases, you will be given the option to write a query using either the TRADITIONAL method of joining tables or using the JOIN key word. In some cases, you will be asked to use a specific approach. Using the incorrect process when the process is specified will result in points being taken off for that problem. The SELECT statement is the primary means of extracting data from database tables, and allows you to determine exactly which data you want to extract by means of different comparison operators used in the WHERE clause. This includes the use of specific "wild card" characters which allow you to search for character or number patterns within the data. You can also perform mathematical expressions within the SELECT statement to create derived output. The ORDER BY clause allows you to sort the output data in either ascending (the default) or descending order. Lab #5 will explore all of these applications of the SELECT statement. General Lab Information and Considerations This lab will utilize a set of tables found in the script file ([login to view URL]) found in the Doc Sharing area of the website. If you have not yet downloaded this file from Doc Sharing then do so now. Once you have downloaded the script file, import and run the script in SQL*Plus. Each query in the script file you will create must be numbered (use --1 comments for numbering) and in order. The SQL for the following exercises should be written using notepad and run in SQL*Plus. A Clean Script File: A script file is meant to be like a program. The file can be run every time the code needs to be executed without having to retype the code again each time. For this reason, it is important that there are no errors in the code inside the file. You can go back and forth between Notepad and Oracle when creating your script file to check your queries and verify if they work or not, but you do not want to create your final output file until after you have verified that everything in your script is correct by running it, in its entirety at least once and viewing the output. Once this has been done, you can create your final output file, with echo on to create the document you can turn in with your lab. If using a Spool Session, be sure to type "SPOOL OFF" AT THE SQL> after your Script Stops Spooling! Lab Do's and Don't's • Do Not include the [login to view URL] as part of your lab script. o Do use Notepad to write your query script file. • Do Not write your queries in Word. o Do test each query before moving on to the next. • Do Not include extra queries for a problem unless the problem explicitly asks for more than one query. o Do test your queries before creating your final output file. • Do Not turn in a script file that has queries with errors. o Do number each query using --1 comment notation. • Do Not start your query on the same line as the comment. o Do remember to check your final output and script file for accuracy. • Do Not turn in your lab without first checking your output file to verify that it is correct. Things to keep in mind: If you are not sure of the table names in your user schema, you can use the following select statement to list them. SELECT * FROM TAB; If you want to know the name of the columns in a particular table, you can use the following command to list them. DESC Making a script file containing a series of describe statements for each table and then spooling the output will give you a listing of all the tables with column names. Be sure to review and verify your final output when you are finished. Do Not assume anything. Write queries for each of the stated problems in the steps below that will return a result set of data to satisfy the requirements. When finished, your script file should have a total of 13 queries and your resulting output file should show both the query and result set for each. Deliverables Submit for grading: 1. Your script file with the 13 queries in it. Be sure your name, course number, and lab number are in a comment area at the top of your file. Submitting ONLY your spooled output will result in a 10-point deduction. 2. An output file created using SET ECHO ON showing both the SQL code and the results. 3. Both documents must be zipped into a single file before submitting to the iLab Dropbox for Week 5. L A B S T E P S STEP 1: Using the BOOKS and PUBLISHER tables, create a query using the traditional join method that will return a list containing the book title, publisher contact person, and publisher phone number for all publishers whose publisher name starts with an ‘R'. STEP 2: Using the DUAL table, create a query that will return the day of the week, hour, minutes, and seconds of the current date setting on a computer. The day should be in all upper case. STEP 3: Using the BOOK_CUSTOMER and BOOK_ORDER tables, create a query using the NATURAL JOIN method that will return a list containing the customer first name and last name and the order number for all orders that have been shipped. Give the customer name column an alias of "Customer Name" and order the output by the customer number in the BOOK_ORDER table in ascending order. STEP 4: Using the BOOK_ORDER table, create a query that will return the order number, order date, shipping address, city, state, and zip code for all orders going to Atlanta or Austin. Format the order date to display as Month DD, YYYY and give the column an alias of "Date Ordered". Be sure the month begins with a capital letter. STEP 5: Using the BOOK_ORDER table, create a query using the correct function to return the order number, the date ordered, the date shipped, and a column representing the number of months between the two dates for all columns where a date shipped exists. Format the number returned from the function to display only two decimals, and give the column an alias of "Months Between". NOTE: Be sure that all of the numbers in the fourth column are positive numbers. STEP 6: Using the correct tables in your schema, create a query using either join operation that will list the title of each book and author name(s). Give the title column an alias of "Book Title". Sort the results by title and then by author last name. STEP 7: Using the BOOKS table, create a query that will return the book title, cost, and retail price for all books with a title starting with the letter ‘H'. Use the correct conversion function to format the cost and retail columns to show dollars and cents with a dollar sign (e.g., a price of $25 would display $25.00 in the result set). STEP 8: Using the BOOK_ORDER, ORDER_ITEMS, and BOOKS tables, create a query using an OUTER JOIN operation that will list the book title, order date, and order number for all books in the BOOKS table. Order your output in descending order by ORDER_ITEMS.BOOKID. There are three books that have never been ordered which should show up at the top of your listing. STEP 9: Using the correct tables, create a query using the traditional join operation that will list the customer first and last name, book title, and order date (formatted as MM/DD/YYYY with an alias of “Order Date”) for all the customers who have purchased books published by 'PRINTING IS US'. STEP 10: Using the BOOKS and ORDER_ITEMS table, write a query using the correct Relational Set Operator that will show all of the Book IDs in the BOOKS table that have not been ordered. STEP 11: Using the BOOK_CUSTOMER, BOOK_ORDER, ORDER_ITEMS, and BOOKS tables, create a query using traditional join conditions based on comparisons between primary and foreign keys that will list the customer number, first and last name, and book title. Limit your listing to only those books in the ‘FITNESS' category. STEP 12: Using the BOOKS, ORDER_ITEMS, and BOOK_ORDER tables, create a query that will list the title, retail, quantity, and order date for all books ordered after April 30, 2009. STEP 13: Using the correct tables, create a query using either join operation you wish that will list the order id, order date, quantity ordered, and retail price for every book that has been ordered. Format the date as MM/DD/YYYY with an alias of “Order Date” and format the retail price column using the correct function to show dollars and cents with a dollar sign ( $ ) and a column alias of “Retail”. This is the end of lab #5 Lab 6: Oracle Single Row and Group Functions L A B O V E R V I E W Scenario/Summary Lab #6 will introduce the various aspects of the Single-Row and Group Functions available in the Oracle Database. Most functions can be used in either the SELECT statement or the WHERE clause, but more commonly are used in the SELECT. Chapters 5 and 6 cover most all of the normal processing you use in these functions. In some cases, you will be asked to used more than one function within a single SELECT statement. General Lab Information and Considerations This lab will utilize the same set of tables found in the script file ([login to view URL]) that were used for Labs 4 and 5. Each query in the script file you will create must be numbered (use --1 comments for numbering) and in order. The SQL for the following exercises should be written using notepad and run in SQL*Plus. Read each problem carefully and follow the directions as stated. A Clean Script File: A script file is meant to be like a program. The file can be run every time the code needs to be executed without having to retype the code again each time. For this reason, it is important that there are no errors in the code inside the file. You can go back and forth between notepad and Oracle when creating your script file to check your queries and verify if they work or not. However, you do not want to create your final output file until after you have verified that everything in your script is correct by running it, in its entirety at least once and viewing the output. Once this has been done, you can create your final output file, with echo to create the document, you can turn in with your lab. Remember in using a spool session, you must type "SPOOL OFF" at the SQL> prompt after your script stops spooling to capture all of your data! Lab Do's and Don't's • Do Not include the [login to view URL] as part of your lab script. o Do use Notepad to write your query script file. • Do Not write your queries in Word. o Do test each query before moving on to the next. • Do Not include extra queries for a problem unless the problem explicitly asks for more than one query. o Do test your queries before creating your final output file. • Do Not turn in a script file that has queries with errors. o Do number each query using - -1 comment notation. • Do Not start your query on the same line as the comment. o Do remember to check your final output and script file for accuracy. • Do Not turn in your lab without first checking your output file to verify that it is correct. Things to keep in mind: If you are not sure of the table names in your user schema, you can use the following select statement to list them. SELECT * FROM TAB; If you want to know the name of the columns in a particular table you can use the following command to list them. DESC Making a script file containing a series of describe statements for each table and then spooling the output will give you a listing of all the tables with column names. Be sure to review and verify your final output when you are finished. Do Not assume anything. Write queries for each of the stated problems in the steps below that will return a result set of data to satisfy the requirements. When finished, your script file should have a total of 13 queries, and your resulting output file should show both the query and result set for each. Deliverables Submit for grading: 1. Your script file with the 13 queries in it. Be sure your name, course number, and lab number are in a comment area at the top of your file. 2. An output file created using SET ECHO ON showing both the SQL code and the results. 3. Both documents are to be zipped into a single file before submitting to the iLab Dropbox for Week 6. L A B S T E P S STEP 1: Using the BOOK_CUSTOMER table and the NVL function, create a query that will return a list containing the customer number, first name, last name, and the characters ‘NOT REFERRED' if the customer was not referred by another customer. Give the derived column an alias of REFERRED BY. Do not list any customers that were referred by another customer. STEP 2: Using the BOOKS table and the SUBSTR function, create a query that will return a list of book ISBN codes and the book title for all the computer books. Format the ISBN code to display as 9-999-99999-9 (e.g., 1059831198 would display as 1-059-83119-8) STEP 3: Using the BOOKS table, create a query using the correct functions to return the category name, the total retail of all books per category with a column alias of "Total Retail", and the average retail of all the books per category with an alias of "Average Retail" for all categories with a total retail greater than $40.00. Format the total retail and average retail columns using the correct function to show dollars and cents with a dollar sign. STEP 4: Using the correct tables in your schema, create a query using either join operation you wish that will list the book title and number of authors for all books that have been written by more than one author. Give the title column an alias of "Book Title" and the column showing the number of authors an alias of "Number of Authors". STEP 5: Using the BOOK_AUTHOR and AUTHOR tables for the upper most query, create a sub query that will return the BOOKID, and the first and last name of the author(s) who have written the book(s) that have been purchased the most. To successfully accomplish this, you will need to first find out the maximum total number of copies (based on quantity) sold of a book (based on bookid) in the ORDER_ITEMS table. This will be done in the bottom most query. The return from this query will be used to compare against the BOOKID in the ORDER_ITEMS table that occurs an equal number of times in the middle query. The return of this query will then be used to compare to a BOOKID related to an Author in the tables of the top most query. (HINT: Be careful, you will need both GROUP BY and HAVING clauses for your solution.) STEP 6: Using the correct tables, create a sub query using either join operation you wish that will list the customer number, first and last name concatenated together, and city for all customers who have placed an order for the most expensive book (based on retail price). Give the combined customer names column and alias of "Customer Name". STEP 7: Using the ORDER_ITEMS table, create a query using the correct functions to return the item#, the total quantity of items ordered with an alias of "Total", the average quantity per order with an alias of "Average" (since averages are often in decimals, only show two decimal places), the minimum quantity ordered with an alias of "Minimum", and the maximum quantity ordered with an alias of "Maximum". Your output results set should have five columns and four rows of data. STEP 8: Using the BOOKS table, create a query using the DECODE function that will display the book title, the published date, and the publisher name (this is where the decode function comes in). Give the publisher name column an alias of "Publisher Name", and order the publisher names in descending order. The publisher names used should be the same as those in the PUBLISHER table. DO NOT join to the Publisher table for this query. STEP 9: Using the PUBLISHER table, create a query that will return a line of output like the one below for each record in the table. Be sure to use the correct functions in the SELECT statement, so that your output looks like the example below. The contact person for Printing Is Us Publishing is Tommie Seymour. Be sure to use the correct function to display the publisher name and publisher contact with only the first letter of each word capitalized. STEP 10: Using the correct tables, create a query using the JOIN ON method to display a listing of all customers who have purchased more than two books. List the customer's last name, the customer's city and state, and the number of books they have purchased. Give the number purchased column and alias of "Number Purchased". STEP 11: Using only the BOOK table for the upper most statement, create a sub query that will update the retail price of all books published by PRINTING IS US by five percent (retail * 1.05). Remember, all you have is the publisher "name". Use the sub query to find the publisher ID. To verify your update, provide a select statement on the BOOKS table before your update statement, and then a second select on the BOOKS table after the update. Once you have verified the results, you can issue a ROLLBACK statement to return the data to the original state. STEP 12: Using the BOOK_CUSTOMER table, write a sub query that will return the names of those customers who were referred by the same individual who referred Jorge Perez. Do not display Jorge Perez's name in the returned results (Hint: you should only have two customers displayed and the exclusion for Jorge Perez must be associated with the upper query). STEP 13: Using the BOOKS table, create a query using the correct functions to return the category name, total number books per category with a column alias of "Category Total", and the total cost of all the books per category with an alias of "Cost". Format the cost column using the correct function to show dollars and cents with a dollar sign. This is the end of Lab #6 Lab 7: Working with Additional Database Objects L A B O V E R V I E W Scenario/Summary There is a lot more to a database than just tables and the data in them. A very important part of database security has to do with using and understanding Database Views. Being able to enhance the efficiency of the database tables by using additional indexes is also a very important aspect of database development. Finally, being able to create and use Database Sequences to automate some of the sequential processes in the database can not only save time but improve efficiency. In Lab 7, you will have a chance to work with all three of these areas. General Lab Information and Considerations This lab will utilize the same set of tables found in the script file ([login to view URL]) that were used for Labs 4 through 6. Each query in the script file you will create, must be numbered (use either –1 or REM 1 comments for numbering) and in order. The SQL for the following exercises should be written using notepad and run in SQL*Plus. Read each problem carefully and follow the directions as stated. A Clean Script File: A script file is meant to be like a program. The file can be run every time the code needs to be executed without having to retype the code again each time. For this reason, it is important that there are no errors in the code inside the file. You can go back and forth between notepad and Oracle when creating your script file to check your queries and verify if they work or not. However, you do not want to create your final output file until after you have verified that everything in your script is correct by running it in its entirety at least once and viewing the output. Once this has been done, you can create your final output file, with echo on to create the document, you can turn in with your lab. Remember in using a Spool Session, you must type "SPOOL OFF" at the SQL> PROMPT after your Script stops spooling to capture all of your data! Lab Do's and Don't's • Do Not include the [login to view URL] as part of your lab script. o Do use Notepad to write your query script file. • Do Not write your queries in Word. o Do test each query before moving on to the next. • Do Not include extra queries for a problem unless the problem explicitly asks for more than one query. o Do test your queries before creating your final output file. • Do Not turn in a scrip file that has queries with errors. o Do number each query using either --1 or REM 1 comment notation. • Do Not start your query on the same line as the comment. o Do remember to check your final output and script file for accuracy. • Do Not turn in your lab without first checking your output file to verify that it is correct. Things to keep in mind: If you are not sure of the table names in your user schema, you can use the following select statement to list them. SELECT * FROM TAB; If you want to know the name of the columns in a particular table, you can use the following command to list them. DESC Making a script file containing a series of describe statements for each table and then spooling the output will give you a listing of all the tables with column names. Be sure to review and verify your final output when you are finished. Do Not assume anything. Write queries for each of the stated problems in the steps below that will return a result set of data to satisfy the requirements. When finished, your resulting output file should show both the query statement and result set for each query. Deliverables The deliverable for this lab will include: 1. Your script file with the seven queries and oneexplanation in it. Be sure your name, course number, and lab number are in a comment area at the top of your file. 2. An output file created using SET ECHO ON showing both the SQL code and the results. Be sure that you include your answer to question #3 as a comment in this file. Both documents are to be zipped into a single file before submitting to the iLab Dropbox for Week 7. L A B S T E P S STEP 1: Create a simple view named CUST_VIEW using the book_customer table that will display the customer number, first and last name, and the state for every customer currently in the database. Now insert the following data into the book_customer TABLE using an INSERT statement. (Do not use the View for this insert.) CUSTOMERID - 1021, FIRSTNAME – EDWARD, LASTNAME – BLAKE, STATE – TX. Now query your view and display the new record. STEP 2: Create a complex view named CUST_ORDER that will list the customer number, last name, and state from the BOOK_CUSTOMER table, in addition to the order number and order date from the BOOK_ORDER table. Insert the following data into this view (use the view for the insert statement): CUSTOMERID - 1022, LASTNAME – smith, STATE – KS, ORDERID - 1021, and ORDERDATE – 10-OCT-2004. STEP 3: In your own words, explain why the insert statement for the view you created in Step 2 did not work. STEP 4: Create a sequence that can be used to assign a publisher ID number to a new publisher. Define the sequence to start with seven, increment by two, and stop at 1000. Name the sequence PUBNUM_SEQ. STEP 5: Insert two new publishers into the PUBLISHER table, one named Double Week with a contact name of Jennifer Close at 800-959-6321, and the second one named Specific House with a contact name of Freddie Farmore at 866-825-3200. Use your new sequence to create the PUBID for each record. Now, query your PUBLISHER table to see your two new records. STEP 6: Using a single query, query the PUBNUM_SEQ to determine what both the current sequence number is and the next sequence number will be. STEP 7: Create a unique index on the combined columns ORDERID and CUSTOMERID in the BOOK_ORDER table. Give the index a name of BOOK_ORDER_IDX. STEP 8: Determine how many objects you currently own in your schema by querying the USER_OBJECTS view in the Data Dictionary. Your result set should list the different object types that you find and include a count by object type. This is the end of Lab #7 Projects 4-7 Logical Model Use iLab solution from Week 3 as a guide to complete the projects. (attatched) Using the Oracle SQL*Plus editor, develop a database for MiniQuest based on your data model. The database should contain all the tables and attributes discussed in the project specifications. Be sure to use your data dictionary when creating your tables. A key component to every task submission is the team responsibilities document. This document should outline the contributions that each member of the team made to completing the task deliverable. To submit this task, you need to create a file in notepad called [login to view URL], where X is your team identifier. For example, Team A would name the file TEAM_A_TASK2.TXT. In this file, create a heading called CREATE STATEMENTS, and then develop the CREATE TABLE statements required to create your database tables. Be sure to include the DROP TABLE statements at the top of your file so that you can reuse the file. If you include the wording CASCADE CONSTRAINTS as part of each DROP TABLE statement, then the order of the DROP statements will not mater. For example, to drop a table named customer, you would state: DROP TABLE CUSTOMER CASCADE CONSTRAINTS PURGE; After testing and verifying that all of your create statements work, create a spooled output file with the SET ECHO ON session command set so that your code and the results will show. Deliverables for this task: 1. Task 2 comprised of the CREATE TABLE script file and the output file showing that it works. Include all documents for this deliverable in a separate zip file from your lab and class exercise, and put it in the. Project 5 - Verification of Logical Model Develop the SQL statements to insert records into the database. You should ensure that the data in the database is sufficient to allow for each of the reports requested by Ms. Smith to be tested. You need to show a minimum of three insert queries for each database table that you have created. Some tables will obviously require more to ensure that all of the required data is in the database (i.e., the packages table, program table, etc). To submit this task, add a comment heading called INSERT STATEMENTS to your corrected SQL script from Task 2. Add your INSERT statements for each table after this heading. Keep in mind that you will need to insert data into the tables in the same order that the tables were created. This will minimize any integrity constraint errors you might encounter. Keep in mind also that any values being inserted into a field defined as a foreign key field must first exist in a previous table's primary key field. Save the file as [login to view URL], where X is your team identifier. After testing your statements to verify that your data will insert into your tables, create a spooled output file with the SET ECHO ON session command set for submitting. Note – Although you are only required to have three records per table in most of your tables for this deliverable, you will need a considerable amount of data in your tables for the final project. After testing and verifying that all of your create statements work, create a spooled output file with the SET ECHO ON session command set so that your code and the results will show. Deliverables for this task: 1. Task 3 comprised of the script file containing DROP TABLE, CREATE TABLE, and INSERT statements, and the output file showing that it works. Project 6 - Verification of Transaction Processing Develop a query that will identify and mark inactive those customers that have bills overdue by more than 30 days (this can usually be done using a sub-query). Remember, you are only marking a customer as inactive, not actually deleting the customer record from the system. To submit this task, create a heading called MODIFY STATEMENTS in your group project SQL script, and name the file TEAM_X_TASK4.TXT. This should be the same script that contains your DROP TABLE, CREATE TABLE, and INSERT statements. Once you have verified that your SQL statement or statements work, create a spooled output showing the results of your statements. Deliverables for this task: 1. Task 4 comprised of the complete script file (DROP, CREATE, INSERT, SELECT, and UPDATE statements) and the output file showing that it works. Project 7 - Reports and Virtual Notebook Develop the queries to support each of the reports that are required by Ms. Smith. It is expected that the information returned by the queries is adequate to be used in the reporting process. Make sure your output contains enough information! The following is a recommendation and the MINIMUM amount of data you should have access to: • Customer Table: A minimum of 12 customers (this will allow several channels to have more than one customer selecting it as a favorite channel) • Billing Table: Records to support the above customers • Program Table: A minimum of 25 programs (your program guide should have enough programs for various channels to support the program guide report) • Other tables: A sufficient amount of data to support the above when reports are run Create a heading of REPORTING STATEMENTS as a comment at the bottom of your group project database script file. Add the six report queries to this area. In instances where you are asked to allow for user input, please specify an appropriate value based upon the data in your script. For example, Report 1 is a DATE driven report; therefore, you should use a single date in your WHERE clause to display the information. Task 5 is the final task for the project and is designed to demonstrate that you have adequately satisfied the requirements of the project. The following is what you are to submit for Task 5: Your virtual notebook containing : 1. The Data Dictionary, ERD, and complete database script. The script should include DROP TABLE and CREATE TABLE statements, appropriate CONSTRAINTS, INSERT statements, UPDATE statement, and 6 REPORT QUERIES. 2. A Spool file for each of the required scripts. All the files that you need are attached.
ID del proyecto: 2285420

Información sobre el proyecto

Proyecto remoto
Activo hace 12 años

¿Buscas ganar dinero?

Beneficios de presentar ofertas en Freelancer

Fija tu plazo y presupuesto
Cobra por tu trabajo
Describe tu propuesta
Es gratis registrarse y presentar ofertas en los trabajos
Adjudicado a:
Avatar del usuario
Dear Sir, this is a very easy project, you won't regret if you chose to work with me , i can complete all the labs for you so fast.
$50 USD en 0 día
0,0 (0 comentarios)
0,0
0,0

Sobre este cliente

Bandera de UNITED STATES
Oceanside, United States
5,0
1
Forma de pago verificada
Miembro desde sept 8, 2009

Verificación del cliente

¡Gracias! Te hemos enviado un enlace para reclamar tu crédito gratuito.
Algo salió mal al enviar tu correo electrónico. Por favor, intenta de nuevo.
Usuarios registrados Total de empleos publicados
Freelancer ® is a registered Trademark of Freelancer Technology Pty Limited (ACN 142 189 759)
Copyright © 2024 Freelancer Technology Pty Limited (ACN 142 189 759)
Cargando visualización previa
Permiso concedido para Geolocalización.
Tu sesión de acceso ha expirado y has sido desconectado. Por favor, inica sesión nuevamente.