I am new to SQL world

  • Kabina

    Old Hand

    Points: 335

    I eager to learn sql , I am currently working on Access . I want to learn SQL i have seen lots of videos and understand how sql query works.

    I found this forum is one of great forum to learn. I wish you all the best. I mostly retrieve data for reporting. I want strong command on my SQL query.

    I have following few question in mind.

    can we use procedure in our query to retrieve data ?

    can we use function in our query to retrieve data ?

    Can sql query result send via outlook with attachment automatically ?

    I need to retrieve data using sql for reporting so that I could use it in power bi and micro strategy,  kindly let me know what should I learn most.

     

    • This topic was modified 2 weeks, 5 days ago by  Kabina.
    • This topic was modified 2 weeks, 5 days ago by  Kabina.
  • Thom A

    SSC Guru

    Points: 98447

    can we use procedure in our query to retrieve data ?

    You can use an SP to return data, yes, but not inside a (SELECT?) query. Executing an SP would be its own statement

    can we use function in our query to retrieve data ?

    Yes

    Can sql query result send via outlook with attachment automatically ?

    You can't send it via Outlook, but you can use sp_send_dbmail which can use POP3/IMAP to send emails.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • Jeff Moden

    SSC Guru

    Points: 995109

    Kabina wrote:

    can we use procedure in our query to retrieve data ? 

    Yes.  Contrary to popular belief, you CAN call a stored procedure  in a SELECT query using things like OPENROWSET.

    Kabina wrote:

    can we use function in our query to retrieve data ? 

    Yes.  However, there are 3 different common types of functions and, when it comes to performance, it makes a huge difference on which type you use and how you use it.

    Heh... and very much against common knowledge and supposed "Best Practices", you can actually call stored procedures from functions.  That's why they called "FUNctions".  You do have to be careful when you do something that wasn't designed to happen or you'll quickly find out what the "FU" in "FUN" stands for. 😀

    Kabina wrote:

    Can sql query result send via outlook with attachment automatically ? 

    Thom covered this.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • Thom A

    SSC Guru

    Points: 98447

    Jeff Moden wrote:

    Kabina wrote:

    can we use procedure in our query to retrieve data ? 

    Yes.  Contrary to popular belief, you CAN call a stored procedure  in a SELECT query using things like OPENROWSET.

    Very true; i was more thinking along the side of "standard" SQL, rather than using Psuedo-Dynamic. 🙂

    Jeff Moden wrote:

    Yes. However, there are 3 different common types of functions and, when it comes to performance, it makes a huge difference on which type you use and how you use it.

    Worth noting that there are 4 now, 2019 finally added inline Scalar functions, however, not applicable on 2012.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • Jeff Moden

    SSC Guru

    Points: 995109

    Thanks, Thom.  Understood on the 2019 stuff.  I was under the impression that it wasn't actually a "4th" type... I was under the impression that if SQL Server could run a non-iTVF function inline, that it would "auto-magically".

    Understood on the "normal" stuff, as well.  I just didn't want to limit anyone's imagination with "normal". 😀

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • Kabina

    Old Hand

    Points: 335

    Thank you all of you for explaining. If I want to retrieve data from SQL server and Oracle. Is there any difference in query structure between SQL and Oracle  or almost same as I saw in SQL we have function Getdate() but in oracle it sysdate()? Are there any more like that , Can I have list ?

  • Jeff Moden

    SSC Guru

    Points: 995109

    Yeah... confirmed.  It's not something that you can assign.  It just happens if you do things right.  You can, however, prevent it.  I don't know why anyone would want to do that.

    https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/scalar-udf-inlining?view=sql-server-ver15

    Heh... I wonder if Joe Celko will recommend turning this feature off because it's Microsoft Proprietary and won't actually migrate. 😉

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • Thom A

    SSC Guru

    Points: 98447

    Jeff Moden wrote:

    Thanks, Thom.  Understood on the 2019 stuff.  I was under the impression that it wasn't actually a "4th" type... I was under the impression that if SQL Server could run a non-iTVF function inline, that it would "auto-magically".

    Yes, true (though I'm not sure how much I trust SQL Server doing it "auto-magically") and will likely ensure that where I can I'll write my Scalar Functions in the inline syntax (CREATE FUNCTION ... RETURNS date AS BEGIN RETURN {expression). but either way the inline scalar function do perform much better than their multi-line versions. 🙂

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • Jeff Moden

    SSC Guru

    Points: 995109

    Kabina wrote:

    Thank you all of you for explaining. If I want to retrieve data from SQL server and Oracle. Is there any difference in query structure between SQL and Oracle  or almost same as I saw in SQL we have function Getdate() but in oracle it sysdate()? Are there any more like that , Can I have list ?

    There are huge differences both in structure an syntax between what is available in SQL Server and Oracle.  Don't get me wrong... there are a lot of similarities but there is a fairly decent paradigm shift between the two.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • Jeff Moden

    SSC Guru

    Points: 995109

    Thom A wrote:

    Jeff Moden wrote:

    Thanks, Thom.  Understood on the 2019 stuff.  I was under the impression that it wasn't actually a "4th" type... I was under the impression that if SQL Server could run a non-iTVF function inline, that it would "auto-magically".

    Yes, true (though I'm not sure how much I trust SQL Server doing it "auto-magically") and will likely ensure that where I can I'll write my Scalar Functions in the inline syntax (CREATE FUNCTION ... RETURNS date AS BEGIN RETURN {expression). but either way the inline scalar function do perform much better than their multi-line versions. 🙂

    Also true... Gail did some simple testing and the difference in performance (where it works) can be absolutely incredible.

    https://sqlinthewild.co.za/index.php/2019/05/07/in-line-scalar-functions-in-sql-server-2019/

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • Jeff Moden

    SSC Guru

    Points: 995109

    Jeff Moden wrote:

    Kabina wrote:

    Thank you all of you for explaining. If I want to retrieve data from SQL server and Oracle. Is there any difference in query structure between SQL and Oracle  or almost same as I saw in SQL we have function Getdate() but in oracle it sysdate()? Are there any more like that , Can I have list ?

    There are huge differences both in structure an syntax between what is available in SQL Server and Oracle.  Don't get me wrong... there are a lot of similarities but there is a fairly decent paradigm shift between the two.

    p.s. The list of differences would cover volumes.  Someone can provide a "short list" but it'll end up ultimately being seriously inadequate.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • Kabina

    Old Hand

    Points: 335

    Hi I am trying to get :

    if today is Monday then it will show result from Friday to Sunday

    Else

    if today is not Monday then I will show you result one day before.

    please note my PODATE is 'yyyymmdd' format.

    This is what I made . Kindly check if it is ok.

     

    select * from PODATA where PODATE BETWEEN

    CASE

    WHEN

    DATENAME(WEEKDAY, GETDATE())='Monday'

    THEN

    CONVERT(CHAR(8), GETDATE(), 112)-3

    ELSE

    CONVERT(CHAR(8), GETDATE(), 112)-1

    END

    AND

    CASE

    WHEN

    DATENAME(WEEKDAY, GETDATE())='Monday'

    THEN

    CONVERT(CHAR(8), GETDATE(), 112)-1

    ELSE

    CONVERT(CHAR(8), GETDATE(), 112)-1

    END

     

    • This reply was modified 2 weeks, 4 days ago by  Kabina.
    • This reply was modified 2 weeks, 4 days ago by  Kabina.
    • This reply was modified 2 weeks, 4 days ago by  Kabina.
  • Thom A

    SSC Guru

    Points: 98447

    This is pseudo-SQL, but seems like you'd be better off with a start and end parameter, which you set at the start of the batch:

    DECLARE @StartDate date = DATEADD(DAY,-CASE DATENAME(WEEKDAY, GETDATE()) WHEN 'Monday' THEN 3 ELSE 1 END,GETDATE()),
    @EndDate date = GETDATE();

    SELECT {Columns}
    FROM dbo.YourTable
    WHERE YourDate >= @StartDate
    AND YourDate < @EndDate;

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • Jeff Moden

    SSC Guru

    Points: 995109

    Kabina wrote:

    Hi I am trying to get :

    if today is Monday then it will show result from Friday to Sunday

    Else

    if today is not Monday then I will show you result one day before.

    please note my PODATE is 'yyyymmdd' format.

    This is what I made . Kindly check if it is ok.

    select * from PODATA where PODATE BETWEEN

    CASE

    WHEN

    DATENAME(WEEKDAY, GETDATE())='Monday'

    THEN

    CONVERT(CHAR(8), GETDATE(), 112)-3

    ELSE

    CONVERT(CHAR(8), GETDATE(), 112)-1

    END

    AND

    CASE

    WHEN

    DATENAME(WEEKDAY, GETDATE())='Monday'

    THEN

    CONVERT(CHAR(8), GETDATE(), 112)-1

    ELSE

    CONVERT(CHAR(8), GETDATE(), 112)-1

    END

    In the future, it would be much more helpful to you as a person with a question, to us as persons who have answers to such questions, and to others who will need both in the future if you were to create a new post for new questions not related to a given thread.

     

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • Kabina

    Old Hand

    Points: 335

    Should I use declare variable above query or I can use in between as well ?

    • This reply was modified 2 weeks, 4 days ago by  Kabina.

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

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