Urgent : SQL Queries [message #645515] |
Mon, 07 December 2015 23:21 |
|
akriti
Messages: 1 Registered: December 2015
|
Junior Member |
|
|
Hi,
Please help me in following queries :
Scenario: The database "Cellphones Information" contains details on cell phone sales or transactions.
Details stored are: Dim_Manufacturer, Dim_Model, Dim_Customer, Dim_Location and Fact_Transactions. The first four store entries for the respective elements and Fact_Transactions stores all the information about sales of specific cellphones.
Object Structure : Please find it attached.
Need queries for following:
1. What state in the US is buying more 'Samsung' cell phones? Display state and number of cell phone transactions, sort by state.
Keep in mind that some manufacturers can be stored in this format: "Name of Manufacturer - Country Code" (e.g. Samsung Chile, Samsung Peru, Motorola Brazil). Also, some of them can be stored in uppercase or lowercase.
2. Show the number of transactions for each model per zip code per state. Display manufacturer, model name, state and zip code, even if there are no transactions for a model. Sort by manufacturer name.
3. Find out the average price for each model in the top 5 manufacturers in terms of sales quantity, and order by average price;
4. List if there is any model that was in the top 5 in terms of quantity, simultaneously in 2008, 2009 and 2010;
5. List the names of the customers and the average amount spent in 2009, where the average is higher than 500.00;
6. List all the states in which we have customers who have bought cellphones from 2005 till today;
7. Show the manufacturers that sold cell phones in 2010 but didn't in 2009.
It is urgent, I am very thankful if you respond immediately.
Thanks,
Akriti.
|
|
|
Re: Urgent : SQL Queries [message #645518 is a reply to message #645515] |
Mon, 07 December 2015 23:39 |
|
Michel Cadot
Messages: 68659 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Nothing is urgent in a forum but you to read our rules.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals.
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
In addition, we don't do homework but we can help to achieve it. Post what you already tried and explain us where you are stuck.
[Updated on: Mon, 07 December 2015 23:41] Report message to a moderator
|
|
|
|
|
|
Re: : How to proceed with the given schema diagram [message #670915 is a reply to message #670914] |
Thu, 02 August 2018 04:06 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Still far too vague.
SQL does work on tables. If you don't have an tables then you can't run any queries.
If you've got documentation about tables then you can use that to write queries, but can't run them without actual tables in an actual DB.
Someone has asked you do to something.
What, exactly, have they asked you to do?
What, exactly, have they given you to accomplish your task?
|
|
|
Re: : How to proceed with the given schema diagram [message #670916 is a reply to message #670915] |
Thu, 02 August 2018 04:11 |
|
Aaashi
Messages: 4 Registered: August 2018
|
Junior Member |
|
|
<< THE EXACT QUESTION IS >>>
Business Scenario: The database "Cellphones Information" contains details on cell phone sales or transactions. Detailes stored are: Dim_manufacturer, Dim_model, Dim_customer, Dim_Location and Fact_Transactions. The first four store entries for the respective elements and Fact_Transactions stores all the information about sales of specific cellphones.
Data Availability: Assume that, you have given access data base with below schema
Questions:
Write queries to find out the following:
1. List all the states in which we have customers who have bought cellphones from 2005 till today.
2. What state in the US is buying more 'Samsung' cell phones?
3. Show the number of transactions for each model per zip code per state. 4. Show the chepest cellphone
5. Find out the average price for each model in the top5 manufacturers in terms of sales quantity and order by average price.
6. List the names of the customers and the average amount spent in 2009, where the average is higher than 500.00
7. List if there is any model that was in the top 5 in terms of quantity, simultaneously in 2008, 2009 and 2010
8. Show the manufacturer with the 2nd top sales in the year of 2009 and the manufacturer with the 2nd top sales in the year of 2010.
9. Show the manufacturers that sold cellphone in 2010 but didn't in 2009.
10. Find top 100 customers and their average spend, average quantity by each year. Also find the percentage of change in their spend.
-
Attachment: schema.PNG
(Size: 133.43KB, Downloaded 1671 times)
|
|
|
|
|
|
|
|
|
Re: : How to proceed with the given schema diagram [message #671498 is a reply to message #670914] |
Sat, 01 September 2018 20:53 |
|
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
Aaashi wrote on Thu, 02 August 2018 01:36
... My query is using MS access and with the given diagram , how should i proceed?
* should I create tables in access and then insert some records manually and then proceed with queries ?
I'm very confused , please help.
This is a forum for SQL queries on Oracle databases. The SQL queries used on MS Access databases have entirely different syntax. You need to post your question on a MS Access forum, not an Oracle forum.
|
|
|