SQL Questions
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
