Using the BIKES database structure and file create the BIKES objects. Be sure to create the...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
Using the BIKES database structure and file create the BIKES objects. Be sure to create the schema first (then you no longer need the command). 2. Using the Excel Spreadsheet With Data create all the necessary INSERT statements to populate all 4 tables. 3. You will need to submit a .sql (text file) that includes all your INSERTS and your queries below (do NOT include any screenshots - you .sql code should adequately answer the questions). Demonstrate that each object has data in it 4. /* PRODUCTS Table Data */ 5. /* BRANDS Table Data */ 6. /* CATEGORIES Table Data */ 7. /* STOCKS Table Data */ Complex Queries 8. /* Create a query that shows all the data attributes exactly like the spreadsheet */ Should look something like: Should look something like: Results Messages category_id 6 6 1 2 3 4 5 6 category_name brand_id brand name model year product_name Mountain Bikes Trek 820-2016 9 Trek 2016 Mountain Bikes 5 2016 2016 8 9 2016 3 2016 8 2016 6 6 6 Ritchey Timberwolf Frameset-2016 Surty Wednesday Frameset-2016 Trek Fuel EX 829-2016 Heller Shagamaw Frame-2016 Surty Ice Cream Truck Frameet-2016 Trek Slagh 9. /* Create a query that shows how many bikes (qty) of brands names are in each category name */ Mountain Bikes Mountain Bikes Ritchey Surty Trek Heller Surty Mountain Bikes Mountain Bikes untain Bikes list price product_id quantity store_id 379.99 1 1 27 749.99 2 5 1 999.99 3 6 1 23 1 22 1 2899.99 4 1320.99 469.99 3999.00 1 5 6 0 1 2 3 4 5 6 Children Bicycles Children Bicycles Children Bicycles Comfort Bicycles Comfort Bicycles cles 10./* Create a query that shows category names, brand names and how many each store has */ 1 2 Results Messages 456700 category_name Children Bicycles Children Bicycles store_id 1 1 3 1 1 8 Results 1 1 brand_name aty 84 12 9 3 69 1 1 1 Electra Haro Strider Sun Bicycles Trek Electra Sun Bicycle actra Messages category_name Children Bicycles Children Bicycles Children Bicycles Children Bicycles Children Bicycles Comfort Bicycles Comfort Bicycles Cruisers Bicycles ers Bic 57 27 192 brand_name qty 28 4 3 1 23 19 9 64 3 Electra Haro Strider Sun Bicycles Trek Electra Sun Bicycles Electra Pure 11./* Show total quantity and amount of inventory for each store based upon list price broken out by category of bike and brand. Brand total dollars should look like $12,456.00 */ 11./* Show total quantity and amount of inventory for each store based upon list price broken out by category of bike and brand. Brand total dollars should look like $12,456.00 */ 23 1 1 1 4 Results 56789 store_id 1 1 1 1 1 1 1 10 1 Messages category_name Children Bicycles Children Bicycles Children Bicycles Children Bicycles Children Bicycles Comfort Bicycles Comfort Bicycles Cruisers Bicycles Cruisers Bicycles Criners Bicycles Bicycle brand_name qty brand total Electra 28 Haro 4 Strider 3 1 Sun Bicycles Trek Electra 23 19 Sun Bicycles 9 Electra 64 Pure Cycles 3 $9,249.72 $999.96 $629.97 $109.99 $5,989.77 $14,979.81 $4,243.91 $50,329.27 $1,327.00 Bicycles 11 $5,315.89 7.99 - create schema bikes; /****** Object: Table [production]. [brands] Script Date: 10/31/2021 4:55:23 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO DROP TABLE IF EXISTS [bikes]. [stocks]; CREATE TABLE [bikes]. [stocks] ( [store_id] [int] NOT NULL, [product_id] [int] NOT NULL, [quantity] [int] NULL DROP TABLE IF EXISTS [bikes]. [products]; CREATE TABLE [bikes]. [products] ( [product_id] [int] NOT NULL PRIMARY KEY, [product_name] [varchar] (255) NOT NULL, [brand_id] [int] NOT NULL, [category_id] [int] NOT NULL, [model year] [smallint] NOT NULL, [list_price] [decimal] (10, 2) NOT NULL D DROP TABLE IF EXISTS [bikes]. [brands]; CREATE TABLE [bikes]. [brands] ( [brand_id] [int] NOT NULL PRIMARY KEY, [brand_name] [varchar] (255) NOT NULL DROP TABLE IF EXISTS [bikes]. [categories]; CREATE TABLE [bikes]. [categories]( [category_id] [int] NOT NULL PRIMARY KEY, [category_name] [varchar] (255) NOT NULL ALTER TABLE [bikes] . [products] ADD FOREIGN KEY ([brand_id]) REFERENCES [bikes].[brands] ([brand_id]); ALTER TABLE [bikes]. [products] ADD FOREIGN KEY ( [category_id]) REFERENCES [bikes]. [categories] ([category_id]); ALTER TABLE [bikes]. [stocks] ADD PRIMARY KEY ([store_id], [product_id]); ALTER TABLE [bikes]. [stocks] ADD FOREIGN KEY ([product_id]) REFERENCES [bikes]. [products] ([product_id]); Database Diagrams Tables System Tables File Tables External Tables dbo.customers dbo.DEPARTMENT dbo.employees dbo.offices dbo.orderdetails +dbo.orders dbo.payments dbo.productlines + dbo.products Using the BIKES database structure and file create the BIKES objects. Be sure to create the schema first (then you no longer need the command). 2. Using the Excel Spreadsheet With Data create all the necessary INSERT statements to populate all 4 tables. 3. You will need to submit a .sql (text file) that includes all your INSERTS and your queries below (do NOT include any screenshots - you .sql code should adequately answer the questions). Demonstrate that each object has data in it 4. /* PRODUCTS Table Data */ 5. /* BRANDS Table Data */ 6. /* CATEGORIES Table Data */ 7. /* STOCKS Table Data */ Complex Queries 8. /* Create a query that shows all the data attributes exactly like the spreadsheet */ Should look something like: Should look something like: Results Messages category_id 6 6 1 2 3 4 5 6 category_name brand_id brand name model year product_name Mountain Bikes Trek 820-2016 9 Trek 2016 Mountain Bikes 5 2016 2016 8 9 2016 3 2016 8 2016 6 6 6 Ritchey Timberwolf Frameset-2016 Surty Wednesday Frameset-2016 Trek Fuel EX 829-2016 Heller Shagamaw Frame-2016 Surty Ice Cream Truck Frameet-2016 Trek Slagh 9. /* Create a query that shows how many bikes (qty) of brands names are in each category name */ Mountain Bikes Mountain Bikes Ritchey Surty Trek Heller Surty Mountain Bikes Mountain Bikes untain Bikes list price product_id quantity store_id 379.99 1 1 27 749.99 2 5 1 999.99 3 6 1 23 1 22 1 2899.99 4 1320.99 469.99 3999.00 1 5 6 0 1 2 3 4 5 6 Children Bicycles Children Bicycles Children Bicycles Comfort Bicycles Comfort Bicycles cles 10./* Create a query that shows category names, brand names and how many each store has */ 1 2 Results Messages 456700 category_name Children Bicycles Children Bicycles store_id 1 1 3 1 1 8 Results 1 1 brand_name aty 84 12 9 3 69 1 1 1 Electra Haro Strider Sun Bicycles Trek Electra Sun Bicycle actra Messages category_name Children Bicycles Children Bicycles Children Bicycles Children Bicycles Children Bicycles Comfort Bicycles Comfort Bicycles Cruisers Bicycles ers Bic 57 27 192 brand_name qty 28 4 3 1 23 19 9 64 3 Electra Haro Strider Sun Bicycles Trek Electra Sun Bicycles Electra Pure 11./* Show total quantity and amount of inventory for each store based upon list price broken out by category of bike and brand. Brand total dollars should look like $12,456.00 */ 11./* Show total quantity and amount of inventory for each store based upon list price broken out by category of bike and brand. Brand total dollars should look like $12,456.00 */ 23 1 1 1 4 Results 56789 store_id 1 1 1 1 1 1 1 10 1 Messages category_name Children Bicycles Children Bicycles Children Bicycles Children Bicycles Children Bicycles Comfort Bicycles Comfort Bicycles Cruisers Bicycles Cruisers Bicycles Criners Bicycles Bicycle brand_name qty brand total Electra 28 Haro 4 Strider 3 1 Sun Bicycles Trek Electra 23 19 Sun Bicycles 9 Electra 64 Pure Cycles 3 $9,249.72 $999.96 $629.97 $109.99 $5,989.77 $14,979.81 $4,243.91 $50,329.27 $1,327.00 Bicycles 11 $5,315.89 7.99 - create schema bikes; /****** Object: Table [production]. [brands] Script Date: 10/31/2021 4:55:23 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO DROP TABLE IF EXISTS [bikes]. [stocks]; CREATE TABLE [bikes]. [stocks] ( [store_id] [int] NOT NULL, [product_id] [int] NOT NULL, [quantity] [int] NULL DROP TABLE IF EXISTS [bikes]. [products]; CREATE TABLE [bikes]. [products] ( [product_id] [int] NOT NULL PRIMARY KEY, [product_name] [varchar] (255) NOT NULL, [brand_id] [int] NOT NULL, [category_id] [int] NOT NULL, [model year] [smallint] NOT NULL, [list_price] [decimal] (10, 2) NOT NULL D DROP TABLE IF EXISTS [bikes]. [brands]; CREATE TABLE [bikes]. [brands] ( [brand_id] [int] NOT NULL PRIMARY KEY, [brand_name] [varchar] (255) NOT NULL DROP TABLE IF EXISTS [bikes]. [categories]; CREATE TABLE [bikes]. [categories]( [category_id] [int] NOT NULL PRIMARY KEY, [category_name] [varchar] (255) NOT NULL ALTER TABLE [bikes] . [products] ADD FOREIGN KEY ([brand_id]) REFERENCES [bikes].[brands] ([brand_id]); ALTER TABLE [bikes]. [products] ADD FOREIGN KEY ( [category_id]) REFERENCES [bikes]. [categories] ([category_id]); ALTER TABLE [bikes]. [stocks] ADD PRIMARY KEY ([store_id], [product_id]); ALTER TABLE [bikes]. [stocks] ADD FOREIGN KEY ([product_id]) REFERENCES [bikes]. [products] ([product_id]); Database Diagrams Tables System Tables File Tables External Tables dbo.customers dbo.DEPARTMENT dbo.employees dbo.offices dbo.orderdetails +dbo.orders dbo.payments dbo.productlines + dbo.products
Expert Answer:
Related Book For
Business Analytics Data Analysis And Decision Making
ISBN: 9780357109953
7th Edition
Authors: S. Christian Albright, Wayne L. Winston
Posted Date:
Students also viewed these databases questions
-
A regular language is a language that can be defined by a regular expression. 0 2 . 1 Complete the unshaded cells of Table 1 to show which of the statements about regular languages are true and which...
-
Design a Java class that represents a cache with a fixed size. It should support operations like add, retrieve, and remove, and it should evict the least recently used item when it reaches capacity.
-
3. Questions A venture capitalist (VC) is willing to invest 100m for 20% ownership of a start-up that is looking to achieve scale. All existing shares are common shares, and this deal would result in...
-
Please be sure to include the following: Introduction of the organization Overview of the IT department and personnel Discuss the occurrence or breach or disaster that has happened. Perform a...
-
The brake shown in the figure has a coefficient of friction of 0.30, a face width of 2 in, and a limiting shoe lining pressure of 150 psi. Find the limiting actuating force F and the torque capacity.
-
Box \(\mathrm{A}\), which has an inertia of \(2 \mathrm{~kg}\), and box \(\mathrm{B}\), which has an inertia of \(3 \mathrm{~kg}\), are \(2.0 \mathrm{~m}\) apart on a low-friction floor. Box B is...
-
1 (a) what source of power does Carl have (b) What type of power is she using? (c) Which influencing tactic is Carl using during the meeting? (d) Is negotiation and/or the (e) Exchange tactic...
-
Explain the issues that an auditor may need to deal with in the cash accounts, especially if the company has foreign bank accounts for international transactions. Also, would a schedule of interbank...
-
Ritz Productss materials manager, Tej Dhakar, must determine whether to make or buy a new semiconductor for the wrist TV that the firm is about to produce. One million units are expected to be...
-
Amazing Fashion House Bhd (AFHB) was set up 5 years ago by En Rizman and his friends, AFHB sells clothing, with a strategy of selling high fashion items under the AF brand name. New ranges of clothes...
-
solve for y x 5 y + 2 0 = 0
-
For R1= 6.8k, and R2= 1k, Then: V(1) = v V. 1(2) = mA.
-
strictly evaluate the questions as you are thinking about them in general, NOT in relation to the product. i DO NOT want to hear that they need a good streaming TV service. I want to hear about their...
-
Drinder corp has declared that will offer dividend per share of 1.50 one year from now, 2 two year from now 1.75 three years from now you believe that you can sell the stock at the end of the third...
-
The following data is given regarding three investments Gov Bond, Zam Beef and Zam Chicken. Predicted rate of return (%) State Probability GOV BOND ZAM BEEF ZAM CHICKEN Boom 0.3 10 25 14 Normal 0.45...
-
The COVID-19 pandemic has also placed immense pressures on organizations ability to compete effectively across a number of industries. In the case of Nobel hygiene only 20% of their workforce is...
-
Assessing simultaneous changes in CVP relationships Braun Corporation sells hammocks; variable costs are $75 each, and the hammocks are sold for $125 each. Braun incurs $240,000 of fixed operating...
-
Will the chi-square test ever conclude, at the 5% significance level, that data are not normally distributed when you know that they are? Check this with simulation. Specifically, generate n normally...
-
Suppose Winters method is used to forecast quarterly U.S. retail sales (in billions of dollars). At the end of the first quarter of 2018, the seasonal indexes are: quarter 1, 0.94; quarter 2, 1.02;...
-
In the past, 60% of all undergraduate students enrolled at State University earned their degrees within four years of matriculation. A random sample of 95 students from the class that matriculated in...
-
The employees of a midsized printed circuit-board manufacturer had been excited about their empowerment program during its first several months of operation. A number of solid suggestions for...
-
Explain the concept of workforce readiness as it relates to empowerment.
-
Distinguish between empowerment and enlistment.
Study smarter with the SolutionInn App