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...
-
'I don't understand this cost report at all', exclaimed Jeff Mahoney, the newly appointed administrator of Bunyip General Hospital. 'Our administrative costs in the new pediatrics clinic are...
-
Show the difference between shaping and behavioral chaining with a human student example.
-
You just inherited $4,000. You need to decide whether to invest this money in a mutual fund, pay down your credit card debt, or simply hold the money in an interest-bearing checking account to pay...
-
At December 31, 2014, Navaro Corporation reported the following plant assets. During 2015, the following selected cash transactions occurred. Apr. 1 Purchased land for $2,200,000. May 1 Sold...
-
Assume the spot rate on the New Zealand dollar is NZ$1.0926, the risk-free nominal rate in the U.S. is 3.8 percent and 4.1 percent in New Zealand. What is the four-year forward rates best establishes...
-
It?s Aa Grindd Coffee Shop It?s A Grind Coffee Shop is an Oakland, California, neighborhood coffee shop. Besides serving gourmet coffee, It?s A Grind dishes up sandwiches and desserts. Local bands,...
-
E-wallet adoption in Malaysia is still in its infancy.The Malaysian government's aspiration is trying to transform the nation into a cashless society by 2025. Therefore, e-wallet providers must...
-
How can companies effectively leverage branding to create competitive advantage? Discuss the key elements of a strong brand, including brand identity, positioning, and personality.
-
what is competitive in Microsoft and is required to alleviate brand pressure. What needs to be developed to create a larger community?
-
You are a junior auditor working in Big-4 Auditing Limited, a world-wide auditing firm. Recently the CEO of Norden acquire external audit service from Big-4 Auditing Limited. As the CEO of Norden...
-
Stevens IT requires users to choose passwords that consist of exactly 10 capitalized letters, which are stored in hashed form using either SHA-1 or SHA-256. Eve compromised Stevens' authentication...
-
Classification of items as per Cost of quality and determine the total amount on each category under appropriate types: Razor Ltdwants to improve its quality and has the following operating data for...
-
Jack Fitzgerald wishes to purchase a used car that has a cash price of$11,000. The installment terms include a down payment of $4,000 and 36 monthly payments of $225. a) What finance charge will Jack...
-
Quadrilateral EFGH is a kite. Find mG. E H <105 G 50 F
-
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...
-
NBC has the rights to televise the Winter Olympics in two years, and is trying to estimate the value of these rights for possible sale to another network. NBC expects it to cost \($40\) million (in...
-
Cyclops Inc., a high-technology company specializing in state-of-theart visual technology, is considering going public. While the company has no revenues or profits yet on its products, it has a...
-
You have been asked to analyze the value of an oil company with substantial oil reserves. The estimated reserves amount to 10 million barrels, and the estimated cost of developing these reserves...
Study smarter with the SolutionInn App