SQL Server Query

Asked

Viewed 40 times

-2

can help me with this consultation?

CREATE TABLE Region
(
    idRegion INT PRIMARY KEY IDENTITY(1,1)
    ,cdRegion VARCHAR(50)
)

CREATE TABLE Store(
    idStore INT PRIMARY KEY IDENTITY(1,1)
    ,cdStore VARCHAR(50)
    ,dsStore VARCHAR(200)
)


CREATE TABLE StorexRegion
(
    idStore INT FOREIGN KEY REFERENCES Store(idStore)
    ,idRegion INT FOREIGN KEY REFERENCES Region(idRegion)
)

CREATE TABLE Sales(
    idSales INT PRIMARY KEY IDENTITY(1,1) 
    ,idStore INT FOREIGN KEY REFERENCES Store(idStore)
    ,idRegion INT FOREIGN KEY REFERENCES Region(idRegion)
    ,vlTotal FLOAT
)

Using the above tables, create a query that links a store to the corresponding region(s)

I tried this consultation, I don’t know if it’s correct:

SELECT 
  * 
FROM 
  STORE S 
  INNER JOIN REGION R ON S.IDSTORE = R.IDREGION

Create a query that devotes the total amount of sales from one store per Region

SELECT 
  SUM(S.VLTOTAL) 
FROM 
  SALES S 
  INNER JOIN STORE ST ON S.IDSALES = ST.IDSORTE
  INNER JOIN REGION R ON = S.IDSALES = R.IDREGION
GROUP BY R.IDREGION
sql sql-server

  • help us can, show us what you’ve done, put the select who has tried

  • Tentei essas duas:
1-
SELECT * FROM STORE S INNER JOIN REGION R
ON S.IDSTORE = R.IDREGION

2- 
SELECT SUM(S.VLTOTAL) * FROM SALES S INNER JOIN STORE ST
ON S.IDSALES = ST.IDSORTE
INNER JOIN REGION R
ON = S.IDSALES = R.IDREGION
GROUP BY R.IDREGION

  • Put an example with registration in http://sqlfiddle.com/ this way we can help you more easily

  • edit the question and place the query there to get a better view

  • edited the question, apologies for the way I did here..

  • @Lucaslira edited the question, but we still need an example of data to help you

Show 1 more comment

1 answer

0

What you need to do is just cross all the tables, and then bring the results as requested.

Using the above tables, create a query that links a store to the corresponding region(s):

Select sxr.idStore, sto.dsStore, sxr.idRegion, reg.cdRegion
FROM StorexRegion sxr
INNER JOIN Store sto
  ON sto.idStore = sxr.idStore
INNER JOIN Region reg
  ON reg.idRegion = sxr.idRegion

Create a query that devotes the total amount of sales from one store per Region:

Select sxr.idStore, sto.dsStore, sxr.idRegion, reg.cdRegion, COUNT(sal.idSales) Qtd_vendas
FROM StorexRegion sxr
INNER JOIN Store sto
  ON sto.idStore = sxr.idStore
INNER JOIN Region reg
  ON reg.idRegion = sxr.idRegion
INNER JOIN Sales sal
  ON sal.idStore = sto.idStore
  AND sal.idRegion = reg.idRegion
GROUP BY sxr.idStore, sto.dsStore, sxr.idRegion, reg.cdRegion

  • Man, thank you so much..

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.