Understanding an example database and write basic SQL queries [12 Marks] We provide you with an...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
Understanding an example database and write basic SQL queries [12 Marks] We provide you with an Oracle sample database which is based on a global fictitious company that sells computer hardware including storage, motherboard, RAM, video card, and CPU https://www.oracletutorial.com/getting-started/oracle-sample-database/. The company maintains the product information such as name, description standard cost, list price, and product line. It also tracks the inventory information for all products including warehouses where products are available. Because the company operates globally, it has warehouses in various locations around the world. The company records all customer information including name, address, and website. Each customer has at least one contact person with detailed information including name, email, and phone. The company also places a credit limit on each customer to limit the amount that customer can owe. Whenever a customer issues a purchase order, a sales order is created in the database with the pending status. When the company ships the order, the order status becomes shipped. In case the customer cancels an order, the order status becomes canceled. In addition to the sales information, the employee data is recorded with some basic information such as name, email, phone, job title, manager, and hire date. The following illustrates the sample database diagram: CUSTOMERS CUSTOMER_ID NAME ADDRESS WEBSITE CREDIT LIMIT ORDERS ORDER D CUSTOMER ID STATUS SALESMAN ID ORDER DATE ORDER ITEMS ORDER ID *ITEM_ID PRODUCT_ID QUANTITY UNIT_PRICE PRODUCTS *PRODUCT_ID PRODUCT_NAME DESCRIPTION STANDARD COST LIST PRICE CATEGORY_ID 49- ++ CONTACTS CONTACT_ID FIRST NAME LAST NAME EMAIL PHONE CUSTOMER_ID EMPLOYEES *EMPLOYEE D FIRST NAME LAST NAME EMAIL PHONE HIRE_DATE MANAGER ID JOB_TITLE INVENTORIES PRODUCT_ID *WAREHOUSE_ID QUANTITY PRODUCT_CATEGORIES CATEGORY_ID CATEGORY_NAME REGIONS *REGION_ID REGION NAME COUNTRIES COUNTRY_ID COUNTRY NAME REGION ID LOCATIONS *LOCATION ID ADDRESS POSTAL CODE CITY STATE COUNTRY_ID WAREHOUSES +H *WAREHOUSE ID WAREHOUSE_NAME LOCATION_ID He He To do the following tasks, it needs to execute schema.sql to build the database schema, i.e., create the tables, and then run data.sql to insert the data into the created tables. We have provided the training and practice at the workshop session of Week 2. 2 Tips: There are several commands that may be useful. Select table_name from user_tables; It can be used to check the existence of the tables. Suppose you have downloaded and placed your sql file at your Deakin mapped network drive H:. Then you can run by the command, @schema.sql. Additional information - To execute sql files remotely, you must connect Deakin VPN by following https://support.deakin.edu.au/kb view customer.do?sys kb id=69baaed84f671bc429ed30b01310c7 2f and set up "Map Network Drive" by following the webpage https://help.deakin.edu.au/ithelp?id=kb_article&sysparm article=KB0010355&sys kb_id=flc42a3 Odbc689907991aaf6059619f0&spa-1 for Windows users, https://help.deakin.edu.au/ithelp?id=kb_ article&sysparm article-KB0010357&sys_kb_id=756171f 8db028d901acbdbf2f3961998&spa=1 for Mac users. If you have access issue with your Deakin accounts, then you need to contact IT help via https://help.deakin.edu.au/ithelp or you can alternatively use MySQL or the implemented MySQL in XAMPP. Understanding an example database and write basic SQL queries [12 Marks] We provide you with an Oracle sample database which is based on a global fictitious company that sells computer hardware including storage, motherboard, RAM, video card, and CPU https://www.oracletutorial.com/getting-started/oracle-sample-database/. The company maintains the product information such as name, description standard cost, list price, and product line. It also tracks the inventory information for all products including warehouses where products are available. Because the company operates globally, it has warehouses in various locations around the world. The company records all customer information including name, address, and website. Each customer has at least one contact person with detailed information including name, email, and phone. The company also places a credit limit on each customer to limit the amount that customer can owe. Whenever a customer issues a purchase order, a sales order is created in the database with the pending status. When the company ships the order, the order status becomes shipped. In case the customer cancels an order, the order status becomes canceled. In addition to the sales information, the employee data is recorded with some basic information such as name, email, phone, job title, manager, and hire date. The following illustrates the sample database diagram: CUSTOMERS CUSTOMER_ID NAME ADDRESS WEBSITE CREDIT LIMIT ORDERS ORDER D CUSTOMER ID STATUS SALESMAN ID ORDER DATE ORDER ITEMS ORDER ID *ITEM_ID PRODUCT_ID QUANTITY UNIT_PRICE PRODUCTS *PRODUCT_ID PRODUCT_NAME DESCRIPTION STANDARD COST LIST PRICE CATEGORY_ID 49- ++ CONTACTS CONTACT_ID FIRST NAME LAST NAME EMAIL PHONE CUSTOMER_ID EMPLOYEES *EMPLOYEE D FIRST NAME LAST NAME EMAIL PHONE HIRE_DATE MANAGER ID JOB_TITLE INVENTORIES PRODUCT_ID *WAREHOUSE_ID QUANTITY PRODUCT_CATEGORIES CATEGORY_ID CATEGORY_NAME REGIONS *REGION_ID REGION NAME COUNTRIES COUNTRY_ID COUNTRY NAME REGION ID LOCATIONS *LOCATION ID ADDRESS POSTAL CODE CITY STATE COUNTRY_ID WAREHOUSES +H *WAREHOUSE ID WAREHOUSE_NAME LOCATION_ID He He To do the following tasks, it needs to execute schema.sql to build the database schema, i.e., create the tables, and then run data.sql to insert the data into the created tables. We have provided the training and practice at the workshop session of Week 2. 2 Tips: There are several commands that may be useful. Select table_name from user_tables; It can be used to check the existence of the tables. Suppose you have downloaded and placed your sql file at your Deakin mapped network drive H:. Then you can run by the command, @schema.sql. Additional information - To execute sql files remotely, you must connect Deakin VPN by following https://support.deakin.edu.au/kb view customer.do?sys kb id=69baaed84f671bc429ed30b01310c7 2f and set up "Map Network Drive" by following the webpage https://help.deakin.edu.au/ithelp?id=kb_article&sysparm article=KB0010355&sys kb_id=flc42a3 Odbc689907991aaf6059619f0&spa-1 for Windows users, https://help.deakin.edu.au/ithelp?id=kb_ article&sysparm article-KB0010357&sys_kb_id=756171f 8db028d901acbdbf2f3961998&spa=1 for Mac users. If you have access issue with your Deakin accounts, then you need to contact IT help via https://help.deakin.edu.au/ithelp or you can alternatively use MySQL or the implemented MySQL in XAMPP.
Expert Answer:
Answer rating: 100% (QA)
Solution 11 Task 11 requires us to write an SQL query to list the Customer Name and the total purchase amount for all orders This query will help you identify the total amount each customer has spent ... View the full answer
Related Book For
Management Accounting Information for Decision-Making and Strategy Execution
ISBN: 978-0137024971
6th Edition
Authors: Anthony A. Atkinson, Robert S. Kaplan, Ella Mae Matsumura, S. Mark Young
Posted Date:
Students also viewed these databases questions
-
Determine the logging and auditing deficiencies of the current environment. Then, create official policies for the following: Logging (Think about what you want to capture and whether it is...
-
Planning is one of the most important management functions in any business. A front office managers first step in planning should involve determine the departments goals. Planning also includes...
-
Can someone please summarize the case study below: if you know about the case IKEA Looks to Further Penetrate the U.S. Market 10 CASE Synopsis: IKEA is known around the world for its stylish,...
-
On January 1, Year 2, PAT Ltd. acquired 90% of SAT Inc. when SATs retained earnings were $900,000. There was no acquisition differential. PAT accounts for its investment under the cost method. SAT...
-
Stan Ott is evaluating two recent transactions involving exchanges of equipment. In one case, the exchange has commercial substance. In the second situation, the exchange lacks commercial substance....
-
A fair die is rolled 15 times. Calculate the probability that there are: a. Exactly three 6s and three 5s b. Exactly three 6s, three 5s, and four 4s c. Exactly two 6s What is the expected number of...
-
The magnitude of the gravitational force exerted by Earth on an object of inertia \(m_{1}\) is \(m_{1} g\). (a) What is the magnitude of the force exerted by the object on Earth (inertia...
-
Performance Measures. Information from the Form 990 for the American Heart Association for the fiscal year ending June 30, 2007, follows. Required a. Compute the following performance measures using...
-
Part A A string that is 9.6 m long is tied between two posts and plucked. The string produces a wave that has a frequency of 320 Hz and travels with a speed of 192 m/s. How many full wavelengths of...
-
For the system shown in Figure P4.8, a step torque is applied at 1 (t). Find: a. The transfer function, G(s) = 2 (s)/T(s) b. The percent overshoot, settling time, and peak time for 2 (t). ff 2...
-
There has been a concern that oil companies do not readjust prices to benefit consumers when prices fall. The companies are nevertheless quick to adjust upwards when prices rise. This is said not to...
-
A possessor 1. has a duty to ferret out all hidden dangers for an invitee. 2. will not be liable for injuries resulting to an invitee as a result of a faulty design or construction that existed...
-
The Tarasoff holding 1. blurs the line for professionals between protecting others and preserving the confidentiality of communications between themselves and their clients. 2. holds professionals...
-
The members of the offensive line of the Denver Broncos weigh 275, 281, 285, 265, and 292 lb, respectively. (a) Calculate the mean and median weight of the offensive line. (b) Calculate the variance...
-
Compare the use of the mean to the use of the median as a measure of central tendency. If you were taking a tough calculus class where 3 brilliant students out of 20 nevertheless received perfect...
-
In major league baseball, rookies earn a minimum salary of $100,000, whereas superstar players earn as much as $5 million per year. Do you think the mean or the median of major league salaries would...
-
A four-pole, three-phase synchronous generator has a fieldwinding with a total of 148 series turns and a winding factorkr=0.939. The rotor length is 72 cm, and its radius is 19 cm. Theair gap length...
-
Give codons for the following amino acids: (a) Th (b) Asp (c) Thr
-
Manufacturing is developing a sales and production plan as part of its master budgeting process. Following are the projected monthly sales, which occur uniformly during each month, for the upcoming...
-
1. How does efficiency (the ratio of income to sales) affect return on investment? 2. How does productivity (the ratio of sales to investment) affect return on investment?
-
The Monteiro Manufacturing Corporation manufactures and sells folding umbrellas. The corporation's condensed income statement for the year ended December 31, 2011, follows: Monteiro's budget...
-
Imagine that the EU flagship Human Brain Project has succeeded in producing a highperformance computer that can simulate the entire network of 8.61010 neurones and their 1015 connections. (a) Will it...
-
The discipline of neuroscience makes use of network theory to identify the structures relating to functionality of the brain. Consider the paper by Vrtes et al. [469]. You do not need to study all...
-
Recall the discussion around Fig. 3.2. Though the model was introduced to describe magnetic phase transitions, it has been used much more widely. Applications include, for example, gas theory,...
Study smarter with the SolutionInn App