Write a SQL queries for the following questions in the below image?

• pcreddy1005

  Valued Member

  Points: 66

  More actions

  August 3, 2018 at 12:59 am

  #355438

  

• Eirikur Eiriksson

  SSC Guru

  Points: 182912

  More actions

  August 3, 2018 at 1:08 am

  #2000118

  pcreddy1005 - Friday, August 3, 2018 12:59 AM

  

  Please post the DDL (create table) scripts, sample data as an insert statement, the expected output and what you have tried so far.
  😎

  These are simple queries but your samples are too vague.

• SGT_squeequal

  SSCertifiable

  Points: 7167

  More actions

  August 3, 2018 at 2:21 am

  #2000122

  for beginners may i recommend Sams tech yourself SQL in 10 minutes 🙂 10 minutes later you will be able to write the SQL to answer these questions yourself 🙂

  ***The first step is always the hardest *******

• Eirikur Eiriksson

  SSC Guru

  Points: 182912

  More actions

  August 3, 2018 at 2:31 am

  #2000123

  SGT_squeequal - Friday, August 3, 2018 2:21 AM

  for beginners may i recommend Sams tech yourself SQL in 10 minutes 🙂 10 minutes later you will be able to write the SQL to answer these questions yourself 🙂

  It is a good book for a beginner!
  😎
  Had a look at it few years back when a joker friend of mine gave me a copy, didn't have it for long as a gave it to a production DBA 😉
  

• Grant Fritchey

  SSC Guru

  Points: 398890

  More actions

  August 3, 2018 at 4:28 am

  #2000131

  We have an excellent primer on T-SQL right here on this web site. I'd suggest you read through that and if anything doesn't make sense, please ask. However, I'm not doing your homework for you.

  "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
  - Theodore Roosevelt

  Author of:
  SQL Server Execution Plans
  SQL Server Query Performance Tuning

• David Burrows

  SSC Guru

  Points: 65142

  More actions

  August 3, 2018 at 5:07 am

  #2000135

  Grant Fritchey - Friday, August 3, 2018 4:28 AM

  However, I'm not doing your homework for you.

  Oh Grant you're so tetchy :laugh:

  Far away is close at hand in the images of elsewhere.
  Anon.

• Beatrix Kiddo

  SSC-Dedicated

  Points: 32407

  More actions

  August 3, 2018 at 5:15 am

  #2000137

  Not so much as a please.

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  August 3, 2018 at 7:52 am

  #2000157

  Beatrix Kiddo - Friday, August 3, 2018 5:15 AM

  Not so much as a please.

  Won't even transcribe the questions. Asking for good manners is even harder.

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  August 3, 2018 at 7:53 am

  #2000158

  Since I'm feeling a little bit sympathetic, I'll share this article with you that could help you solve the first 2 questions.
  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - SQLServerCentral

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• prabhu.st

  SSCrazy

  Points: 2552

  More actions

  August 3, 2018 at 9:04 am

  #2000179

  Hi PC Reddy, 

  here is the query you asked for

  Question 1
  (Need to czategorize the column as positive and negative and sum it as below.)

  
SELECT SUM([Positive Numbers]) AS [Positive Numbers], SUM([Netagive Numbers]) AS [Netagive Numbers]
FROM (
    SELECT SUM(CASE WHEN Num>0 THEN Num ELSE 0 END) AS [Positive Numbers],
        SUM(CASE WHEN Num<0 THEN Num ELSE 0 END) AS [Netagive Numbers]
    FROM #TableA
    GROUP BY Num
) ResultSet


  Question 2
  (Need to Just pivot the column)
  
SELECT [Chandra] [FirstName], [Sekhar] [MiddleName], [Reddy] [LastName], [Peddireddy] [SurName]
FROM
(SELECT Name
  FROM #TableB) AS SourceTable
PIVOT
(
MIN(Name)
FOR Name IN ([Chandra], [Sekhar], [Reddy], [Peddireddy])
) AS PivotTable;


  Question 3
  (Just use the case statement with Modulus (Mod = "%"))
  
SELECT id, name, (CASE id%2 WHEN 1 THEN 'M' ELSE 'F' END) AS Gender
FROM #TableC


• drew.allen

  SSC Guru

  Points: 76988

  More actions

  August 3, 2018 at 9:43 am

  #2000189

  prabhu.st - Friday, August 3, 2018 9:04 AM

  Hi PC Reddy, 

  here is the query you asked for

  Question 1
  (Need to czategorize the column as positive and negative and sum it as below.)

  
SELECT SUM([Positive Numbers]) AS [Positive Numbers], SUM([Netagive Numbers]) AS [Netagive Numbers]
FROM (
    SELECT SUM(CASE WHEN Num>0 THEN Num ELSE 0 END) AS [Positive Numbers],
        SUM(CASE WHEN Num<0 THEN Num ELSE 0 END) AS [Netagive Numbers]
    FROM #TableA
    GROUP BY Num
) ResultSet


  Question 2
  (Need to Just pivot the column)
  
SELECT [Chandra] [FirstName], [Sekhar] [MiddleName], [Reddy] [LastName], [Peddireddy] [SurName]
FROM
(SELECT Name
  FROM #TableB) AS SourceTable
PIVOT
(
MIN(Name)
FOR Name IN ([Chandra], [Sekhar], [Reddy], [Peddireddy])
) AS PivotTable;


  Question 3
  (Just use the case statement with Modulus (Mod = "%"))
  
SELECT id, name, (CASE id%2 WHEN 1 THEN 'M' ELSE 'F' END) AS Gender
FROM #TableC


  In your first query, the derived table is only going to return one row, so there is no need to SUM again in the outer query.  In fact, you can just do away with the outer query altogether.

  Your other two queries are ad hoc.  That is, they will only work with the specific data provided and are not a general solution.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• prabhu.st

  SSCrazy

  Points: 2552

  More actions

  August 3, 2018 at 10:29 am

  #2000198

  drew.allen - Friday, August 3, 2018 9:43 AM

  prabhu.st - Friday, August 3, 2018 9:04 AM

  Hi PC Reddy, 

  here is the query you asked for

  Question 1
  (Need to czategorize the column as positive and negative and sum it as below.)

  
SELECT SUM([Positive Numbers]) AS [Positive Numbers], SUM([Netagive Numbers]) AS [Netagive Numbers]
FROM (
    SELECT SUM(CASE WHEN Num>0 THEN Num ELSE 0 END) AS [Positive Numbers],
        SUM(CASE WHEN Num<0 THEN Num ELSE 0 END) AS [Netagive Numbers]
    FROM #TableA
    GROUP BY Num
) ResultSet


  Question 2
  (Need to Just pivot the column)
  
SELECT [Chandra] [FirstName], [Sekhar] [MiddleName], [Reddy] [LastName], [Peddireddy] [SurName]
FROM
(SELECT Name
  FROM #TableB) AS SourceTable
PIVOT
(
MIN(Name)
FOR Name IN ([Chandra], [Sekhar], [Reddy], [Peddireddy])
) AS PivotTable;


  Question 3
  (Just use the case statement with Modulus (Mod = "%"))
  
SELECT id, name, (CASE id%2 WHEN 1 THEN 'M' ELSE 'F' END) AS Gender
FROM #TableC


  In your first query, the derived table is only going to return one row, so there is no need to SUM again in the outer query.  In fact, you can just do away with the outer query altogether.

  Your other two queries are ad hoc.  That is, they will only work with the specific data provided and are not a general solution.

  Drew

  Hi Drew, 

  I accept your feedback for the second query (but, since there is no much information about the DDL, just tried with the given samples)

  but for the thir one is generic as per the data and the DDL, if you notice the sample data, all the odd numbers are marked mistakenly as "F" and the even numbers are as "M", based on this I posted my queries..

  I believe there may be some other better way, but I just wrote whichever there in the top of my mind that time..

  thank you so much for the valueable feedback.. it could correct me on my mistakes...

• drew.allen

  SSC Guru

  Points: 76988

  More actions

  August 3, 2018 at 11:24 am

  #2000202

  prabhu.st - Friday, August 3, 2018 10:29 AM

  but for the thir one is generic as per the data and the DDL, if you notice the sample data, all the odd numbers are marked mistakenly as "F" and the even numbers are as "M", based on this I posted my queries..

  The point is that the numbers are arbitrarily assigned to the names.  Your "solution" is ad hoc because it will not work for every (or even most) arbitrary assignments of numbers to names.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• Jeff Moden

  SSC Guru

  Points: 1004363

  More actions

  August 3, 2018 at 6:30 pm

  #2000238

  pcreddy1005 - Friday, August 3, 2018 12:59 AM

  

  Dude... do your own homework.  We're not the ones that need to pass the final exam... you are.  Sit down and science it out as if your future jobs depended on it... because they do. 😉

  If this is a pre-exam for an interview, then you might be in trouble for real because it appears that you've applied for a job that you can't actually do.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Jeff Moden

  SSC Guru

  Points: 1004363

  More actions

  August 3, 2018 at 6:38 pm

  #2000239

  prabhu.st - Friday, August 3, 2018 9:04 AM

  Hi PC Reddy, 

  here is the query you asked for

  Question 1
  (Need to czategorize the column as positive and negative and sum it as below.)

  
SELECT SUM([Positive Numbers]) AS [Positive Numbers], SUM([Netagive Numbers]) AS [Netagive Numbers]
FROM (
    SELECT SUM(CASE WHEN Num>0 THEN Num ELSE 0 END) AS [Positive Numbers],
        SUM(CASE WHEN Num<0 THEN Num ELSE 0 END) AS [Netagive Numbers]
    FROM #TableA
    GROUP BY Num
) ResultSet


  Question 2
  (Need to Just pivot the column)
  
SELECT [Chandra] [FirstName], [Sekhar] [MiddleName], [Reddy] [LastName], [Peddireddy] [SurName]
FROM
(SELECT Name
  FROM #TableB) AS SourceTable
PIVOT
(
MIN(Name)
FOR Name IN ([Chandra], [Sekhar], [Reddy], [Peddireddy])
) AS PivotTable;


  Question 3
  (Just use the case statement with Modulus (Mod = "%"))
  
SELECT id, name, (CASE id%2 WHEN 1 THEN 'M' ELSE 'F' END) AS Gender
FROM #TableC


  So if the person that posted the original request with absolutely no effort on their part gets your job because you helped him with his homework or an interview, are you going to feel bad? 😉

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)