Home » SQL & PL/SQL » SQL & PL/SQL » Urgent : SQL Queries (SQL)
Urgent : SQL Queries [message #645515] Mon, 07 December 2015 23:21 Go to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 67367
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 #670912 is a reply to message #645518] Thu, 02 August 2018 03:23 Go to previous messageGo to next message
Aaashi
Messages: 4
Registered: August 2018
Junior Member
Hello , I have to attempt a similar question .
Could u please guide me about how to proceed with this .
I mean i will attempt the queries myself , i just want to know that with the given data , where we are only provided with a schema diagram .
How should i proceed . There are no detailed tables with any records . How will i extract the data as expected in the queries ?
Re: : How to proceed with the given schema diagram [message #670913 is a reply to message #670912] Thu, 02 August 2018 03:27 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
That's far too vague a question.
What actual problem is stopping you from writing the queries you need?
Re: : How to proceed with the given schema diagram [message #670914 is a reply to message #670913] Thu, 02 August 2018 03:36 Go to previous messageGo to next message
Aaashi
Messages: 4
Registered: August 2018
Junior Member
Well actually i am new to this . I am bit confused .
As per my understanding i've always written queries in SQL where we have tables , then tables have some records , then we write any XYZ queries to extract whatever information we want .

Here , in the question, I don't have any actual data only a schema diagram is given , where the interrelation between the tables is shown .
Please correct me if i am wrong .

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.
Re: : How to proceed with the given schema diagram [message #670915 is a reply to message #670914] Thu, 02 August 2018 04:06 Go to previous messageGo to next message
cookiemonster
Messages: 13894
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 Go to previous messageGo to next message
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 611 times)
Re: : How to proceed with the given schema diagram [message #670917 is a reply to message #670916] Thu, 02 August 2018 04:36 Go to previous messageGo to next message
gazzag
Messages: 1082
Registered: November 2010
Location: Bristol, UK
Senior Member
What has this got to do with Oracle?
Re: : How to proceed with the given schema diagram [message #670918 is a reply to message #670917] Thu, 02 August 2018 04:40 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
So you need to write some queries for a test/homework question. You don't need to actually run code on a real system.
If you want to create tables to test what you are doing for yourself then do so.
Otherwise just answer the questions.
Re: : How to proceed with the given schema diagram [message #670919 is a reply to message #670918] Thu, 02 August 2018 05:01 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
And if you want an oracle DB to use to test stuff you can always download and install oracle XE.
Re: : How to proceed with the given schema diagram [message #670920 is a reply to message #670919] Thu, 02 August 2018 05:13 Go to previous messageGo to next message
Aaashi
Messages: 4
Registered: August 2018
Junior Member
Thanks .
Re: : How to proceed with the given schema diagram [message #670921 is a reply to message #670920] Thu, 02 August 2018 05:17 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
So how did the interview go? Wink
Re: : How to proceed with the given schema diagram [message #670922 is a reply to message #670921] Thu, 02 August 2018 06:35 Go to previous messageGo to next message
Bill B
Messages: 1969
Registered: December 2004
Senior Member
This looks like a class assignment for homework. If we spoon feed you the answer you will never learn how to do it. Show us what you have done and we can give you hints.
Re: : How to proceed with the given schema diagram [message #671498 is a reply to message #670914] Sat, 01 September 2018 20:53 Go to previous message
Barbara Boehmer
Messages: 8922
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.

Previous Topic: Oracle ad-hoc report issues
Next Topic: Help in YIELD [Excel Function] formula in PLSQL
Goto Forum:
  


Current Time: Wed Sep 23 00:57:06 CDT 2020