Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

SQL Questions Consider the following table definitions: sales Null NO NO NO Key PRI Default | Extra NULL AUTO INCREMENT NULL NULL Field salesid product

SQL Questions

image text in transcribedimage text in transcribed

Consider the following table definitions: sales Null NO NO NO Key PRI Default | Extra NULL AUTO INCREMENT NULL NULL Field salesid product id client.id store.id date of purchase quantity price Type int(10) unsigned int(10) unsigned int(10) unsigned int(10) unsigned date int(10) unsigned decimal(10,2) unsigned NO NULL NO NULI NULL YES NO NULL Field id name date of birth Key PRI clients Null NO NO NO Type int(10) unsigned varchar(100) date D efault | Extra NULL AUTO_INCREMENT NULL NULL Extra Field store.id store.nams Type int(11) archar(200) stores Key NO PRI NOT Default NULL NULL v Notes regarding the sales table: Contains info about the all sales that a company made. The quantity can be NULL. If this occurs you must suppose that the quantity is 1 Revenue = price * quantity In the field date of purchase we keep the date of the purchase in the format YYYY- MM-DD Subtask 1 Make a query that will return the monthly revenues of each store, in the format: Month of purchase (YYYY-MM S tore name Revenue while taking into account the following conditions: Only sales that meet all these three conditions are considered: o The client must have been born in the 70's o The client's name must contain "John" Only rows with a revenue greater than 100 must be displayed The results must be ordered by revenue, in descending order Solution: Subtask 2 Propose an indexing strategy for these tables. You may modify the structure of the table or create new tables in order to optimize the search Solution: Consider the following table definitions: sales Null NO NO NO Key PRI Default | Extra NULL AUTO INCREMENT NULL NULL Field salesid product id client.id store.id date of purchase quantity price Type int(10) unsigned int(10) unsigned int(10) unsigned int(10) unsigned date int(10) unsigned decimal(10,2) unsigned NO NULL NO NULI NULL YES NO NULL Field id name date of birth Key PRI clients Null NO NO NO Type int(10) unsigned varchar(100) date D efault | Extra NULL AUTO_INCREMENT NULL NULL Extra Field store.id store.nams Type int(11) archar(200) stores Key NO PRI NOT Default NULL NULL v Notes regarding the sales table: Contains info about the all sales that a company made. The quantity can be NULL. If this occurs you must suppose that the quantity is 1 Revenue = price * quantity In the field date of purchase we keep the date of the purchase in the format YYYY- MM-DD Subtask 1 Make a query that will return the monthly revenues of each store, in the format: Month of purchase (YYYY-MM S tore name Revenue while taking into account the following conditions: Only sales that meet all these three conditions are considered: o The client must have been born in the 70's o The client's name must contain "John" Only rows with a revenue greater than 100 must be displayed The results must be ordered by revenue, in descending order Solution: Subtask 2 Propose an indexing strategy for these tables. You may modify the structure of the table or create new tables in order to optimize the search Solution

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Nested Relations And Complex Objects In Databases Lncs 361

Authors: Serge Abiteboul ,Patrick C. Fischer ,Hans-Jorg Schek

1st Edition

3540511717, 978-3540511717

More Books

Students also viewed these Databases questions

Question

Ensure continued excellence in people management.

Answered: 1 week ago

Question

Enhance the international team by recruiting the best people.

Answered: 1 week ago