Advanced SQL Tutorial

แชร์
ฝัง
  • เผยแพร่เมื่อ 13 ม.ค. 2025

ความคิดเห็น • 71

  • @kamlist2808
    @kamlist2808 4 ปีที่แล้ว +8

    What a legen! His restaurants went bankrupt but now he teaches us SQL. Thank you Jamie Oliver

  • @zackjones8681
    @zackjones8681 5 ปีที่แล้ว +8

    Thanks for this. I finally have PIVOT working! I've converted two stored procedures so far and will be converting more now that I understand how it works.

  • @jonnywright8155
    @jonnywright8155 3 ปีที่แล้ว +1

    I find your vids real helpful as a full stack dev tweaking sprocs every now and then. Thanks for the content.

  • @IanJohnstonblog
    @IanJohnstonblog 3 ปีที่แล้ว +3

    Great Video. Thanks for making your series on SQL... I've learned a ton from your clear and concise explanations.

  • @shreyapansari5114
    @shreyapansari5114 5 ปีที่แล้ว +5

    Amazing ! finally could understand coalesce and pivot. Please make videos on stored procedures, CTE and materialised views.

  • @angelchavez5507
    @angelchavez5507 4 ปีที่แล้ว +1

    Thank you! I was looking for the SQL equivalent of the || operator, and had read about coalesce before. Your video really made it "click" for me.

    • @JamesOliver
      @JamesOliver  4 ปีที่แล้ว +2

      Always like to know I'm helping people. That is what it is all about.

  • @Squeez210
    @Squeez210 2 ปีที่แล้ว +1

    >Thanks for watching
    Thanks for creating

  • @aquaticsplashes
    @aquaticsplashes 4 ปีที่แล้ว

    so at @2:10 you want to only select specific columns that they asked for which was the ecom quantity and store quantity don't need to see the color and all that

  • @preranadas4037
    @preranadas4037 3 ปีที่แล้ว +1

    Loved the video!!! thank you for making this amazing video.

  • @andpiojo
    @andpiojo 2 ปีที่แล้ว

    Great vid! Difficult topics explained in a very clear and easy way to understand! Thanks and keep them coming!

    • @JamesOliver
      @JamesOliver  2 ปีที่แล้ว

      Thank you so much! Will do! :)

  • @philippebalech1252
    @philippebalech1252 ปีที่แล้ว

    Man you just rock thank you for sharing ❤

  • @ritchiedecoma6504
    @ritchiedecoma6504 4 ปีที่แล้ว

    Nice breakdown of pivot table

  • @sgpleasure
    @sgpleasure 3 ปีที่แล้ว +1

    SQL is supposed to be simple human readable. Always had issues with the Pivot, the fact that the Categories values had to be hard-coded in the code makes it static. There are solutions on the internet how to first read the list of categories values in a variable.

  • @sinvalfelisberto
    @sinvalfelisberto ปีที่แล้ว

    Thank you! Great video!

  • @hamletmendez
    @hamletmendez หลายเดือนก่อน

    Thanks for this. If I had a variable amount of categories, how could I pivot?

  • @waltherziemerink
    @waltherziemerink 4 ปีที่แล้ว +1

    Excellent tutorial

    • @JamesOliver
      @JamesOliver  4 ปีที่แล้ว

      Neo-1 I appreciate the kind words

  • @septimusseverus252
    @septimusseverus252 3 ปีที่แล้ว

    Thanks bro... Amazing video

  • @juandavidgomezrestrepo6110
    @juandavidgomezrestrepo6110 4 ปีที่แล้ว

    This is super useful stuff , thanks a lot mate

  • @aquaticsplashes
    @aquaticsplashes 4 ปีที่แล้ว

    @3:17 is there a is not null for the product id or is that a different concept?

  • @DARULULOOMKARACHI
    @DARULULOOMKARACHI 4 ปีที่แล้ว +1

    Hi, We may also use Isnull(s.ProductID, e.ProductID) ProductID instead of coalesce this gives also same output. plz verify.

  • @MichalH-z9p
    @MichalH-z9p ปีที่แล้ว

    at the first query, isn't it the same as union all? instead of left join+coalesce? if not, when do you use which?

  • @YouScrub123
    @YouScrub123 2 ปีที่แล้ว

    Great video!

  • @palanit4191
    @palanit4191 5 ปีที่แล้ว +2

    Thanks for the video. Can you please make a video on how Enterprise manager reports can be leveraged.

    • @JamesOliver
      @JamesOliver  5 ปีที่แล้ว +2

      Great suggestion! I will put that on my list.

  • @iliashterev38
    @iliashterev38 3 ปีที่แล้ว

    Great videos. Thank you. Only if you can control those juicy tong sounds.

  • @sigma_z
    @sigma_z 4 ปีที่แล้ว

    Brilliant! Well done!

  • @ryanjennings6233
    @ryanjennings6233 4 ปีที่แล้ว +1

    In the pivot statement, would it have been possible to select distinct values rather than listing them?

    • @JamesOliver
      @JamesOliver  4 ปีที่แล้ว

      You need to reference the actual column names. A select returns the values. Unless I'm misunderstanding the question.

    • @talantsarman9009
      @talantsarman9009 2 ปีที่แล้ว

      @@JamesOliver i guess he meant making a subquery with (select distinct(c.name or category) from productcategory). can we do that in here?

  • @AnshulMarele-gbaa
    @AnshulMarele-gbaa ปีที่แล้ว

    How to enable viewing syntax and command definition [while typing any command] in MS-SQL as we see in Excel

  • @kavyatripathi2151
    @kavyatripathi2151 2 ปีที่แล้ว

    loved it :)

  • @sgpleasure
    @sgpleasure 3 ปีที่แล้ว

    So what's the value in using OuterApply or CrossApply? Based on the demo, the result could be achieved with Table Joins. Anyway, I can Google for the answer. Was always curious when to use Cross Apply or Outer Apply, never knew the answer until this came up in a job interview.

  • @Stelios.Posantzis
    @Stelios.Posantzis 4 ปีที่แล้ว

    Neat and concise. Great for familiarizing oneself with these features. If it included the UNPIVOT statement it would a make for a nice, complete tutorial.

  • @kshathrugan5238
    @kshathrugan5238 4 ปีที่แล้ว

    Cool, i have a doubt. Suppose i have a table with 3 columns say name,id,address and say 100 records. The table has some of the values in name,address filled with null or blank space. I Need to take report which shows me the count of records with blank spaces,null and this shud be achieved without using group by

  • @FirstNameLastName-kt3zn
    @FirstNameLastName-kt3zn 5 ปีที่แล้ว

    This was a helpful explanation of PIVOT

  • @darrylw99
    @darrylw99 6 หลายเดือนก่อน

    It's rubbish that you have to hard code the columns for the pivot... can you not do a select Distinct to populate that?

  • @vincepeterson2834
    @vincepeterson2834 3 ปีที่แล้ว

    Great video.

    • @JamesOliver
      @JamesOliver  3 ปีที่แล้ว

      Appreciate it very much. :)

  • @nishantm1924
    @nishantm1924 3 ปีที่แล้ว

    how can you import csv file to an already existing table using sql query?
    like suppose you have Customer table and now you want to add data from xyz.csv file and both Customer and csv file have same attribute ,then how to do such task?

  • @iitian2012
    @iitian2012 4 ปีที่แล้ว +1

    hi your advanced sql session is really helpful...could you please upload some more advanced queries?

    • @JamesOliver
      @JamesOliver  4 ปีที่แล้ว +1

      ADITYA ANAND sure will do

    • @iitian2012
      @iitian2012 4 ปีที่แล้ว

      Thanks a lot....I am working on data analysis and i have to use sql with advance functions for transformations etc and so I thought that u could help me....thanks ...

  • @shadow_gaming_sk
    @shadow_gaming_sk 3 ปีที่แล้ว

    Can you prepare basic to advanced series, so every one can learn SQL

  • @hebaza9668
    @hebaza9668 ปีที่แล้ว

    how can i find to this database?

  • @pratikpatra67
    @pratikpatra67 4 ปีที่แล้ว

    Why can't we just go for a simple subquery for the 1st requirement , Select * from table1 t1 where t1.id in (select id from table2)

  • @ZnSstr
    @ZnSstr 4 ปีที่แล้ว

    Not sure if you will ever respond but.. why you didn't used the alias for ProductId in the COUNT function?

  • @midnightrun335
    @midnightrun335 4 ปีที่แล้ว

    If I had u as my datasci professor I couldve been working at IBM by now

  • @rishavbahuguna2655
    @rishavbahuguna2655 4 ปีที่แล้ว

    Will I be able to apply this on mysql??

  • @pwrtricks
    @pwrtricks 4 ปีที่แล้ว

    Hello Mr. Oliver!
    Could you take a doubt please?
    Regarding the part of the query before the pivot?
    I'm using the same base as you and your query, I achieved exactly the same results that you showed in the video.
    select c.name Category,
    count (p.productid) totalproducts
    from production.product p
    inner join production.productcategory c
    on c.productcategoryid = p.productsubcategoryid
    group by c.name
    go
    Accessories 8
    Bikes 32
    Clothing 22
    Components 43
    It's a simple query like you said then I tried to take the test and I couldn't reach the number that the query arrived
    I'll take as an example the accessories category (productcategoryid = 4) that gave a total of 8:
    select * from Production.ProductSubcategory
    where ProductCategoryID = 4
    go
    productsubcategoryid productcategoryid
    26 4
    27 4
    28 4
    29 4
    30 4
    31 4
    32 4
    33 4
    34 4
    35 4
    36 4
    37 4
    Ok, after this result I did this search:
    select
    count(productid) count_products_cat_accessories from Production.Product
    where ProductSubcategoryID between 26 and 37
    count_products_cat_accessories
    29
    If possible, can you show me why I didn't get the result of 8?
    Thank you!!

    • @JamesOliver
      @JamesOliver  4 ปีที่แล้ว +1

      I would have to go back and check but I use this database for other demos/purposes and it is possible I have modified the underlying tables. It has been a little while so would have to check to be sure but that is my guess. Thanks for watching.

    • @pwrtricks
      @pwrtricks 4 ปีที่แล้ว

      @@JamesOliver I thank you! Your videos helped me a lot, they are very didactic!
      Ok, I understood what you said about the db but
      in this case, your query returned exactly the same results as the categories shown in your video.
      I believe that the bases are hitting.
      In the future, if you go back to the original DB AdventureWorks2012, I would appreciate it if you can check where I went wrong. Follow the test print link.
      drive.google.com/file/d/1In0tEldKrakm8YM01qRo_5n5T1or1jXR/view?usp=sharing
      My best regards
      !

  • @IanDangerfield
    @IanDangerfield 4 ปีที่แล้ว

    appreciate the vid brother

  • @Nei月牙天衝-y7u
    @Nei月牙天衝-y7u 2 ปีที่แล้ว +1

    It would be hella cool if you add timestamps

  • @aquaticsplashes
    @aquaticsplashes 4 ปีที่แล้ว

    @2:45 I guess they don't like to see nulls in their data?

    • @JamesOliver
      @JamesOliver  4 ปีที่แล้ว

      Yea just quickly handling the NULLs and converting to zero

  • @shijujohn9969
    @shijujohn9969 3 ปีที่แล้ว

    Thank you

  • @seleniumshyamala5223
    @seleniumshyamala5223 4 ปีที่แล้ว

    Nice!!

  • @reyvenobalan8204
    @reyvenobalan8204 4 ปีที่แล้ว

    what application are you using sir ?

    • @JamesOliver
      @JamesOliver  4 ปีที่แล้ว

      Sql server express and management studio. All free.

  • @dotnetdevni
    @dotnetdevni 3 ปีที่แล้ว +1

    How un earth is this considered advanced

  • @aquaticsplashes
    @aquaticsplashes 4 ปีที่แล้ว

    lost me @4:30 what is this "function"

    • @JamesOliver
      @JamesOliver  4 ปีที่แล้ว

      Ayejax just a function that returns rows

  • @sawseries2939
    @sawseries2939 3 ปีที่แล้ว

    Thx

  • @itreportcambodia8283
    @itreportcambodia8283 4 ปีที่แล้ว

    How are you.

  • @50tigres79
    @50tigres79 3 ปีที่แล้ว

    should be titled TSQL