Advanced SQL Tutorial

No video

Advanced SQL Tutorial | Temp Tables

Рет қаралды 252,259

Alex The Analyst

Күн бұрын

Пікірлер: 141

@kstevens0915 3 жыл бұрын

Great tutorial! Only thing I would add is that temp tables are session or query window specific. If you close your current session the table will not be there as you expect a regular table would. I am always learning so I cant wait to watch more!

@AlexTheAnalyst 3 жыл бұрын

Good note to add - thanks for watching!

@Ohallors208 2 жыл бұрын

Thanks, this is the one piece of information I needed. The difference to a regular table.

@AkporHari Жыл бұрын

Thank you, Alex. You give the quickest easy-to-understand tutoring I have received on my DA journey. You explain these things well, and you do not overload the learner with more than they need per time. One helpful thing I do is practice while I watch and play around with it as you teach. I often break something, though, and that helps too.

@rajpreetcheema4075 3 жыл бұрын

Hi Alex, You are so sweet and positive. I been through many of your videos and to be honest I feel hope from you. Seeing you gives hope. Thanks for existing. 🙂

@AlexTheAnalyst 3 жыл бұрын

Glad to exist :D Thanks for watching! I appreciate it!

@user-uu2ph1qi2e Жыл бұрын

For anyone following along in postgreSQL, use TEMP or TEMPORARY to create your temporary tables. Unfortunately, postgreSQL doesn't recognize the # sign in creating temp tables. EX: CREATE TEMP TABLE temp_employee( employeeid INT, jobtitle VARCHAR(100), salary INT );

@castilloerickae.8545 Жыл бұрын

Thank you! Xampp mysql doesn't recognize the # sign this is really a big help.

@Zinberry Жыл бұрын

In MySQL used CREATE TEMPORARY TABLE

@nauikun652 Жыл бұрын

thanks , the syntax is not always the same , the trick is to google or ask chat gpt, the proper syntax for each sql , haha

@aucksun 11 ай бұрын

god bless your soul

@mohamedhassany5984 6 ай бұрын

@@castilloerickae.8545 try using @

@SevenRingsofSaturn 9 ай бұрын

Wow! I love the "DROP TABLE IF EXISTS..." command. Really useful too! Thanks Alex. You're GREAT!

@OmarMohamed-od9ms 9 ай бұрын

when i make it tell me temp is areade in database any solution ?

@shanh54 2 жыл бұрын

Once again a great tutorial!! This is the next concept in the Google data analytics course. Thank you so much you made it smooth digestible.

@sanilkumarbarik9151 Жыл бұрын

Liking the video even before start watching it. That's the faith I have on Alex.

@Plus1HD 3 жыл бұрын

I'm preparing for a interview, very helpful video. Thank you

@seekerj1643 2 жыл бұрын

Such a great example. I was looking for a solution for something I’m working on at work and this is ‘drop table is exist’ is perfect. Thank you.

@rachrach9871 Жыл бұрын

Great tutorial! This is like querying a query. I’m learning a lot from your videos. Thank you Alex

@oguzozakn6305 2 жыл бұрын

I got exactly the information I was looking for and I started following your channel, thanks.

@jacquelineconerly9150 11 ай бұрын

Ten minutes goes by fast. This was much needed.

@terihandrick2333 8 ай бұрын

I just want to say thank you so much for your video's I have learnt more today than I have ever known about SQL and will continue subscribe. You are a HUGE help!!

@sisiphosidiya6485 3 жыл бұрын

Alex Thanks man! All the way from South Africa

@tristanwait4itlegendary 4 ай бұрын

You can also check the existence of temporary tables using object explorer. In the object explorer, expand TEMPDB database folder, and then exapand TEMPORARY TABLES folder, and you should see the temporary table that we have created.

@oscarnests 6 ай бұрын

Thanks for the good content created for free, it is very useful. I would like to add a little bit of information, if you use one # sign the temp table would be considered local (you can use it only in your currect query window), if you use double ## sign the table would be global (you can use it in a new query window). As they mentioned before, when you close the session the tables will be gone forever, so you need to create them again. My two cents!

@ahmetalperenyildirim6184 2 жыл бұрын

Simple and functional as always, thanks Alex, data loves you! :)

@sanjanakale855 Жыл бұрын

Hello Alex, Could you please explain the difference between Temp tables and CTEs. Also, when should you use which one?

@watzjamming Жыл бұрын

CTE are only created in memory however temp table can be used multiple times, as long as you use DROP TABLE F EXIST

@cloudkungfu 2 жыл бұрын

Nitro shot of knowledge to the brain just when I needed it 💯

@yon_draper Жыл бұрын

Helpful video, especially the DROP TABLE tip. Thank you sir

@ash1983 2 ай бұрын

Thank you for this video and for all your tutorials. Question: what is the difference between using a temp table to store a part of a table and using a view to store the same logic and the same part of that table?

@cleobychristine Жыл бұрын

Hi Alex, kindly explain the difference between 'text' and 'varchar' and why you have to use a parenthesis right after the varchar?

@eddiej1414 Жыл бұрын

VARCHAR is best suited for storing short to medium-length strings, while TEXT is better suited for storing large amounts of textual data. the number placed in parentheses after VARCHAR designates the character length allowed in that field, so in the case of this video, the field is restricted to 100 characters in length. Text has some default (but very large) length of possible characters.

@aarthi_navin07 6 ай бұрын

I have been using mysql, but when I use drop table if exists, it shows error in my syntax, Is there any other way to query for my sql

@abhay6276 9 күн бұрын

No need to drop #temp table if you close the current session it will automatically delete from the tempdb 😊

@tristanwait4itlegendary 4 ай бұрын

Its a very interesting idea

@1440MAP 9 ай бұрын

Awesome content! Learning a ton!

@carriegoff3030 Жыл бұрын

These tutorials are amazing! Thanks so much, Alex. Question: Why would a person use a temp table if it is just session-specific. Why wouldn't one just create a new table using a past query so that it is stored permanently, for use later?

@NB20079 Жыл бұрын

Good day Ms.carrie, we same question, have you know already how to find Temp table?

@rechouka7287 2 жыл бұрын

Amazing tutorial as always :)

@anthonyd9043 Жыл бұрын

Thanks for this Alex!

@hsoley 2 жыл бұрын

Gold content!

@ResilientFighter 7 ай бұрын

Shoot, this is genius! Computational efficiency.

@samose100 3 жыл бұрын

Hey Mike! Great content, your SQL videos are easy to go for quick brush up or interview preparation. Keep it coming Kudos from india🔥

@md.mahmudulhasan9272 Жыл бұрын

Great helpful tutorials.

@SEAN-ku5xh 5 ай бұрын

Wow, love this!

@romanvasiura6705 2 жыл бұрын

Yes, I like this video 😀 Thank you!

@rbsorg Жыл бұрын

Is it advisable to use a #TempTable when importing data in PowerBi for a "DirectQuery". So when Alex has the DROP TABLE IF EXIST... and we would refresh our query, would we get the latest data imported and stored in our #TempTable or would PowerBI not even be able to bring back results? Thanks and I am getting more from this free course than my paid subscriptions! Great job Alex!

@anthonynkem 2 жыл бұрын

Great video tutorial

@olenaqwerty7895 2 жыл бұрын

good explanation, thanks

@uwillnevahno6837 3 жыл бұрын

3:52 if your from table had a 4th column of data (let's say state) and you did "SELECT *" to pull everything would an error result or would that 4th column be ignored?

@AlexTheAnalyst 3 жыл бұрын

Yes, there would be an error and what you would need to do is after the #Temp_Employees you would need to do an open parenthesis and specify the columns you're pulling in - Like this: Insert Into #Temp_Tables (column1, column2, column3) Select * From.... Hope that helps!

@YasinKAYA-do4ek 2 жыл бұрын

Thank you but I don't understand the advantage of temp tables. Would ot using queries without them be faster? You just added more steps. This is my first time seeing a video regarding temp tables btw.

@goldencricketstars3060 Жыл бұрын

Hi Alex your tutorial are game-changing for me. can we combine a temporary table and cte

@kemibello6812 Жыл бұрын

Thanks, really helfpul.

@willymwangi3865 10 ай бұрын

Thank you Alex.

@shekhark1139 2 жыл бұрын

Very useful trick..Thank you so much

@businessacademics3334 3 жыл бұрын

Excellent stuff, can you just do something for stored procedures,if I say what's the best way to handle sp in sql

@AlexTheAnalyst 3 жыл бұрын

For sure - I will definitely be doing a video on that as part of this series :)

@businessacademics3334 3 жыл бұрын

@@AlexTheAnalyst one thing more,about sub queries

@akshaypatil8155 Жыл бұрын

How do i improve the skill of knowing different ways to write a code for the same output. Does learning python improves vocabulary of coding? I am aspiring data analyst and i want to master this skill of yours? plz reply....u speak simple and clear in ur videos. Thanks.

@coteten 7 ай бұрын

Hi Alex, how did you populate the table #temp_Employee using the command...Select * From SQLTutorial..EmployeeSalary? It seems very practical but I don't know where those values came from!

@ashnahida1 3 жыл бұрын

Temp tables is considered advanced sql? I think I'm shorting myself when it comes to how I describe my self and where I am sql wise

@AlexTheAnalyst 3 жыл бұрын

Depends on how you use them, but typically you're not using them as a beginner for the most part. I use them in a lot of SP and more advanced queries so I would consider the actual implementation of Temp Tables in your tool kit as more advanced.

@mckenzie6973 4 ай бұрын

love your tutorial Alex. I just don't get the use of temp tables as well as ctes. Like why?

@juanvergara5814 3 жыл бұрын

When i should use a cte, temp table or regular table? I mean, in which scenario fits each one better than other

@allsocialmonkey1847 2 жыл бұрын

Geat tutorial. Please, what do the two dots (..) between the table and column mean? Thanks for the video!

@ahmetalperenyildirim6184 2 жыл бұрын

Hi, its using to select table from related database.

@asiagladden3080 9 ай бұрын

Incredible video!! I did have an error message creating #temp_Employee2 but I see a column was missing for EmployeesPerJob COUNT( and deleted that part of the script and it ran correctly! Wow I love this stuff

@user-gq2eq4gw2k 5 ай бұрын

What is the difference between materialized view and a temp table then? I had this qiestion after watching this video. I know materialized view also allows us to do the same thing and improve the processing by retrieving already stored query and the result and make it quicker to retrieve the data.

@SyedShadabHussaini1996 3 жыл бұрын

Thanks for this. Do you ever plan to make tutorials at beginner level?

@AlexTheAnalyst 3 жыл бұрын

I actually already have a beginner and intermediate series :)

@SyedShadabHussaini1996 3 жыл бұрын

@@AlexTheAnalyst Ohh. Will check it out then

@ezehconfidenceadaeze6189 Жыл бұрын

Thanks Alex

@ghiegomez 6 ай бұрын

Alex would you consider a one on one training?

@BCTAHbKA 2 жыл бұрын

Это лучшее видео на свете

@alaeddinmsetri4788 Жыл бұрын

Nice and easy thanks

@arcaneknight9799 3 жыл бұрын

what other advanced tutorials can we expect?

@AlexTheAnalyst 3 жыл бұрын

Sys tables, exporting data, stored procedures, and more :)

@mfundohg958 2 жыл бұрын

Great thanks 👍👌

@helenasalvetova4775 10 ай бұрын

Hi Great tutorial. I have question about dropping table. I understand that it is done not to get an error. But why to again and again create and insert data into temporary table since we are using it to reduce calculation load. If we do it over and over again are we not adding unnecessary calculation load? Why, once the temporary table is created and filled, don't we rather delete or comment out those CREATE and INSERT INTO queries? Thanks Michal

@shokoofehheidari5345 Жыл бұрын

Thanks a lot!

@arslonboyisorov845 2 жыл бұрын

super, thank you

@juanmanuelarevalomadrid1937 9 ай бұрын

what do you mean when you use the expression hit off ??? is very confusing

@tasleemf2279 5 ай бұрын

How to insert values in cells in a table in SQL where first column is filled with values while the 2nd and 3rd columns are not?(eg: employeeid, age, jobtitle are the three columns, employeeid is filled with values like 100 to 300 while the other 2 columns are empty and has to be filled, will it have to be done one by one or is there any other way?)

@nothingtoseehere5760 6 ай бұрын

Wait so you drop and create a table on each query? Or how is the data updated? Why wouldn't you just use views?

@viaticspace Жыл бұрын

Alex often uses the expression to hit sth. off of sth. What does it mean? To copy? To extract? I tried to google this expression, but couldn't find anything that would apply to this context. Can anyone help?

@Kavi-learn Жыл бұрын

can you provide a timestamp of where it was used?

@santiagofajardo4949 Жыл бұрын

thank You Sr

@OmorFarukbablu 10 ай бұрын

Hey Alex, what is the difference between "Ultimate Beta Course Bundle" to ""Lifetime Access to Analyst Builder"?

@tristanwait4itlegendary 4 ай бұрын

does it show up in the object explorer

@alemabebe5647 2 жыл бұрын

Thanks much alot Whenever I created and inserted data in temp table and shut down my computer and come back and select the temp table it doesn’t work I have to Crete and insert datas again and again when ever I come back into it. Can you please give me some solution for me Thanks

@secretnobody6460 Жыл бұрын

can you explain the meaning of varchar? i get the int as integer type of data, but what is varchar? why not put string?

@shekhark1139 2 жыл бұрын

Shall we add multiple rows from multiple tables into temp table, if column names are same in temp table ?

@karanjadavid2178 2 жыл бұрын

You know what's magical / lifesaving? Putting a query result in a temp table.

@maryamnaz576 Жыл бұрын

Can anyone explain what the number is we put inside parenthese in fron of VARCHAR?

@hipeacok Жыл бұрын

When I inserted values, I got two the same rows while I did the same as you. Do you know why two same rows has been created for me (EmployeeID: 1001)?

@vasilis7076 2 жыл бұрын

What would happen if groupby columns names were different than the tem_table column names?

@pabloct3 2 жыл бұрын

Whats the point of creating a temp table over a regular view? Is it mainly to do with storage efficiencies. Why not just Drop the table when you're down with it? Or is it because temp tables are good for stored procedures because its more efficient? thanks! just trying to figure it out

@pabloct3 2 жыл бұрын

And when you are doing joins why not just inert into a regular table? whats the point of the temp? memory?

@creativitytools4576 6 ай бұрын

if OBJECT_ID('tempdb..#temp_Employee') is not null drop table #temp_Employee create table #temp_Employee ( EmployeeID int, JobTitle varchar(100), Salary int ) Select * From #temp_Employee insert into #temp_Employee values ( '1001', 'HR', '45000' I don't know why I am not able to insert values ?

@TravelingWhileBlack360_Espanol 3 жыл бұрын

"Literally the only difference between a temp table and a regular table is the #" ? I think you meant to say.. Its the only difference in the definition of the two types of tables.. Temp tables live in TEMPDB Unlike physical regular tables... Temp tables dont scale very well with volumes of data.. compared to regular tables...

@AlexTheAnalyst 3 жыл бұрын

Absolutely correct - only difference in the syntax is what I meant

@MiningForPies 11 ай бұрын

Temp tables can scale just as well as ordinary tables as long as you’ve setup your SQL correctly. They’re just temporary. They also live in tempdb only when they get above a certain size and need to be flushed to disk.

@ekanshmishra4517 2 жыл бұрын

hey Alex can you help me find a platform where i can practise these queries on different tables other than creating them.

@ChrisMavroeidis 2 жыл бұрын

I would like to ask a question. is there any book that you suggest to read for SQL Server? I want a book with full 100% documentation and to learn in deep SQL server.

@shamsbehery5432 7 ай бұрын

(drop table if exists ) doesn't work in sql 2014?!

@mcfunthomas_mc Жыл бұрын

I don't think # is a pound sign. I call it a hash sign. :)

@momohtimothy6898 7 ай бұрын

Ha, first watch looks difficult, i'll re-watch to understand. I go again.

@NB20079 Жыл бұрын

Where can I find the temp table

@grandetaco4416 6 ай бұрын

I don't understand why you didn't demonstrate the easy way to create a temp table. The INTO statement. You could have created a temp table without the pain of the create statement. select * into #emp from Employee where age =30 This would give you a complete temp table without out having to set up a create statement every time.

@anshusharma7626 8 ай бұрын

why am i facing issue in creating a temp table CREATE TABLE #temp_Employee ( EmployeeID int, JbbTitle varchar(100), Salary int ) where input is this And output is that ERROR: syntax error at or near "#" LINE 1: CREATE TABLE #temp_Employee ( But its running smoothly when I am using this as an input CREATE TEMPORARY TABLE temp_table ( EmployeeID int, JobTitle varchar(100), Salary int )

@Farheeeezy Жыл бұрын

The first use case is not really straightforward to me. Can someone point me to a different example that I might find more clear please?

@nathaliamendesdealbuquerqu3406 2 жыл бұрын

Hello Alex, thanks for these videos. How can I delete duplicates on my table?

@ysonmencidor6834 2 жыл бұрын

use distinct when you select your table

@davidi.ezekiel9345 2 жыл бұрын

You could check out the Row_Number() function

@nickmccluskey9400 3 жыл бұрын

Any recommendations for certifications on Tableau on Coursera??

@kelthekonqrr Жыл бұрын

Nice

@skylar9105 3 жыл бұрын

I'm a college student, and I recently decided to pursue a career as a data analyst. I don't know anyone in that particular field, is there any way I can get in contact with you?

@AlexTheAnalyst 3 жыл бұрын

Sure thing - you can message me on LinkedIn - I have my link on my KZfaq page on my banner :)

@skylar9105 3 жыл бұрын

@@AlexTheAnalyst Will do! Thank you for responding, I look forward to contacting you soon.

@kammelna 2 жыл бұрын

I want to retrieve a row by a max value in a specific column… Example: I have location, date, and temperature columns. I want to have max temperature in each location with corresponding date. Anyone can help?

@jyrrin 2 жыл бұрын

3:47 not quite sure, but could someone clarify what “SQLTutorial..” is referencing in the FROM clause here? I understand “EmployeeSalary” is the table we’re storing into the temp table, but “SQLTutorial..” seems kinda random - I’m assuming this is the name of the database “EmployeeSalary” is from but was it necessary to add that? Thanks anyone,

@Zaqhry 2 жыл бұрын

SQLTutorial is the name of the database, so if you were in another database named SQLPractice, but had a table that was present in the SQLTutorial database that you wanted to access. Then you would have to use SQLTutorial along with the table name in order to access that table, even if you are in the SQLPractice database. Basically it is allowing you to use tables from different databases, while in another database that doesn't contain that table. So I'm in SQLPractice database, but want to access EmployeeSalary Column, then I'd do SELECT ____ FROM SQLTutorial..EmployeeSalary Hope that made some sense haha, have a great day!

@samuelraj5613 Жыл бұрын

Done

@jamalalkelani4439 Жыл бұрын

Why not to use CREATE TABLE IF NOT EXISTS instead of dropping it and re-creating it again ?

@MiningForPies 11 ай бұрын

Because you want to start with an empty table. If you don’t drop the database the next insert will be inserting into a table with records in it already 🤔

@amarnadhgunakala2901 3 жыл бұрын

What about NoSQL?

@AlexTheAnalyst 3 жыл бұрын

NoSQL is awesome - I may do a video comparing SQL and NoSQL sometime.

@MannyDelaCruzBOOM4U 2 жыл бұрын

Instead of doing DROP TABLE IF EXISTS - what about CREATE TABLE IF NOT EXISTS...?

@MiningForPies 11 ай бұрын

You can, but you’d be left with the data from the last run.

@mrsebastianhendricks Жыл бұрын

I normally use drop table if exists #temp, #temp2 For multiple tables, do u know a way to drop more then 3 tables without listing them Eg I have 10 temp tables , Instead of using this syntax drop table if exists #temp, #temp2 , etc Is there a better way to do this?