How to solve this 2 query

Question

Post reply

How to solve this 2 query

daveriya

SSCrazy

Points: 2352
More actions
February 25, 2011 at 10:23 am

#232658

i am new to sql,i just know basic.

can anybody help me to solve this 2 query

1) A table named "merchants" contains merchant information and has the following fields:

name data type description

merchid integer merchant id number

merchname text name of merchant

category text category of merchant

A table named "bank_trans" contains purchase transaction data and has the following fields:

name data type description

transid integer unique id of transaction

merchid integer merchant id number

transdate date date of transaction 'YYYY-MM-DD'

cardnum text account number of transactor

amount numeric(9,2) dollar amount of transaction

a) Write a SQL query that will display the number of transactions and total dollars spent at XYZ, Inc. for each month during 2009.

2) At the end of each month, a new table is created for each bank that contains monthly metrics consolidated at the account level.

The table naming convention is bankX_YYYYMM where X represents the numeric designation of the bank and YYYYMM indicates the 4 digit year and 2 digit month.

The tables contain the following fields:

name data type description

account text account number

registered boolean indicates whether the account is registered

num_trans integer number of transactions made during the time period

spend numeric(9,2) total spend during the time period

a) Write a SQL query that will display the total number of transactions and total spend for "Bank1" during the 4th quarter of 2009.

b) Write a SQL query that will display the total number of transactions and total spend at "Bank1" and "Bank2", broken out by registered vs. non-registered accounts, during January 2010

3) We have the same table as defined in question 2.

a) How can we check that the consolidated tables were generated correctly?

b) How can we find if there are any duplicates on the account number?

thanks in advance

Viewing 15 posts - 1 through 15 (of 46 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Sounds like homework.

Since most of us already have their degree we don't need to answer those questions anymore. 😉

But we'd be glad to help you understand how it works if you would at least give it a try.

Show us what you've done and where you get stuck and we'll try to help.

Step one would be to help us help you by providing infromation in a ready to use format. An example is explained in the first article referenced in my signature.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 2

I'll give you a few hints if you'll answer my final question.

daveriya (2/25/2011)

a) Write a SQL query that will display the number of transactions and total dollars spent at XYZ, Inc. for each month during 2009.

You want to look into the GROUP BY clause and WHERE clause.

a) Write a SQL query that will display the total number of transactions and total spend for "Bank1" during the 4th quarter of 2009.

You're looking for the UNION ALL operator.

b) Write a SQL query that will display the total number of transactions and total spend at "Bank1" and "Bank2", broken out by registered vs. non-registered accounts, during January 2010

This comes under subqueries, and the JOIN mechanic.

a) How can we check that the consolidated tables were generated correctly?

There's a number of ways and I can't see that being in the same list of problems as the beginning of this list.

b) How can we find if there are any duplicates on the account number?

thanks in advance

Again, you're looking at the GROUP BY clause.

My question, and yes it's a serious one, not just a rant:

2) At the end of each month, a new table is created for each bank that contains monthly metrics consolidated at the account level.

The table naming convention is bankX_YYYYMM where X represents the numeric designation of the bank and YYYYMM indicates the 4 digit year and 2 digit month.

Are they STILL teaching this horrible mechanic to people?! Is this seriously what the book is teaching? If so, please give me the ISBN, I'd like to write a few scorching reviews. That technique should only be taught in advanced courses under the header of "Break only in emergency".

I've been shown the light, and this isn't as horrendous as I believed it to be. I still don't think it should be in a first or second year course without explicit conversation and methodology instructions.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

daveriya SSCrazy Points: 2352 More actions · Answer 3

i know , i need to solve this on my own,

i solve first one,but in the second one

Write a SQL query that will display the total number of transactions and total spend for "Bank1" during the 4th quarter of 2009.

can i write like this

select count(num_trans),sum(spend) from bank1_200910

unionall

select count(num_trans),sum(spend) from bank1_200911

union all

select count(num_trans),sum(spend) from bank1_200912

atleast give me hints

WayneS SSC Guru Points: 95405 More actions · Answer 4

daveriya (2/25/2011)

i know , i need to solve this on my own,

i solve first one,but in the second one

Write a SQL query that will display the total number of transactions and total spend for "Bank1" during the 4th quarter of 2009.

can i write like this

select count(num_trans),sum(spend) from bank1_200910

unionall

select count(num_trans),sum(spend) from bank1_200911

union all

select count(num_trans),sum(spend) from bank1_200912

atleast give me hints

~~That looks like it would work.~~ Have you tried it out?

Edit: Actually, those rollup tables have the totals already. What you need to do is to sum them up across all three months. Basically, make the above a subquery, and sum up the 3 rows you'll be getting from the tables.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

daveriya SSCrazy Points: 2352 More actions · Answer 5

daveriya

SSCrazy

Points: 2352

February 25, 2011 at 1:59 pm

#1291765

i dont understand you this is not working.

write some query and show me.

this givesme result in 3 rows.how cam i make it subquery

WayneS SSC Guru Points: 95405 More actions · Answer 6

This link should give you some good examples of using subqueries.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

daveriya SSCrazy Points: 2352 More actions · Answer 7

daveriya

SSCrazy

Points: 2352

February 25, 2011 at 2:26 pm

#1291781

i tried sunquery,i cant join them coz i think there is no pk->fk releation between them.

nothing is worked

WayneS SSC Guru Points: 95405 More actions · Answer 8

daveriya (2/25/2011)

i tried sunquery,i cant join them coz i think there is no pk->fk releation between them.

nothing is worked

SELECT SUM(num_trans), SUM(spend)



  FROM (

        select num_trans,spend from bank1_200910

        union all

        select num_trans,spend from bank1_200911

        union all

        select num_trans,spend from bank1_200912) AS SubQuery

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

daveriya SSCrazy Points: 2352 More actions · Answer 9

daveriya

SSCrazy

Points: 2352

February 25, 2011 at 2:57 pm

#1291793

hi,

its working ,thanks a lot.

i m trying to solve another one based on that,but thanks a lot

daveriya SSCrazy Points: 2352 More actions · Answer 10

2(b) Write a SQL query that will display the total number of transactions and total spend at "Bank1" and "Bank2", broken out by registered vs. non-registered accounts, during January 2010

SELECT SUM(num_trans) as 'No of Transactions', SUM(spend) as 'Total Spend'

FROM (

select num_trans,spend from bank1_201001 where registered = 0

union all

select num_trans,spend from bank2_201001 where registered = 0

)

registered

union all

SELECT SUM(num_trans) as 'No of Transactions', SUM(spend) as 'Total Spend'

FROM (

select num_trans,spend from bank1_201001 where registered = 1

union all

select num_trans,spend from bank2_201001 where registered = 1

)

nonregistered

Is this true?

WayneS SSC Guru Points: 95405 More actions · Answer 11

daveriya (2/25/2011)

2(b) Write a SQL query that will display the total number of transactions and total spend at "Bank1" and "Bank2", broken out by registered vs. non-registered accounts, during January 2010

Is this true?

No.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2