as a fellow office fan and an aspiring data analyst i love your content

This was exactly what I needed for one of my assignments in my course! Thanks a ton!

Thanks for creating this content, Alex. I request you to create a video on Ranking Window functions like Rank (), Dense_rank (), Row_Number () with the same database.

loved the "Flenderson - Fired" ... good idea to keep the video informative and fun ... very clever.

Apparently you and Michael both hate Toby a lot. First giving him 0.0001% raise and now this.

@AlexTheAnalyst

3 жыл бұрын

He deserves it just for being Toby

@sonatakca5032

10 ай бұрын

@@AlexTheAnalyst💀

Great video :)! Thanks so much! is it possible to do tutorials or a video with your opinions on while loops, cursors or dynamic sql? I'm wondering how useful they are/use cases for each and is it worth it to spend time studying them...

I love this series please make more! I cant wait for your portfolio project.

@AlexTheAnalyst

3 жыл бұрын

Same! I only have one or two more videos and then onto the projects!

@JamesAndrewA

3 жыл бұрын

@@AlexTheAnalyst I want you to know how much I really appreciate your content. I don't want to sound like I am kissing your butt, but hear me out, I cannot gush enough about you. I think I am a bit ahead due to taking classes on the subject and using SQL, but your content is so clean and clear I just wanted you to know how great it is and how much you have helped me. I got a degree in electrical engineering, hated it, and couldn't find work other than a dream job I turned down because I am an idiot who didn't want to move. I said screw it, I can't find work, I might as well go back to school for a masters. "Well what the do I do now?" I liked MATLAB (which for anyone in EE reading this, no one remotely cares about or uses) So I picked Software Engineering. Then in a random KZread search I found your videos while looking for help with homework. You showed some cool stuff and actually sat down with your subscribers live and talked about real word experience and gave advice on the field. Insane for today. I ended up taking a database management class and am currently in "advanced database systems" and love both of them. I love SQL and really hope after I finish school this year I can get a job working with databases. Right now I am looking at other tutorials online for using visual studio and learning integration and reporting services. This summer semester I am going to try to get an internship with the school working with databases and the following semester I will be taking a course on data warehousing and mining. Again, I hope to you and anyone reading this I don't sound over dramatic, but you and a rare unicorn of a professor in my college gave me some direction and I really appreciate it.

@KritikaKabra

3 жыл бұрын

@@JamesAndrewA totally agreed! What a gem 💎 I'm pursuing Bachelor's in Chemical Engineering (which is an interesting field but not so much for me) and I found Alex at such a good time. So grateful for his insights and guidance!

@CE-vd2px

3 жыл бұрын

@@JamesAndrewA Project series is now available

@CareerHubSpot

Жыл бұрын

​@@KritikaKabra ❤

very well explained. Thank you for sharing contents like this

This was so clever! Thank you!

Awesome tutorial. Thanks Alex!

@AlexTheAnalyst

Жыл бұрын

Glad you've been enjoying the series! :D

Thank you! I did learn a lot!

Learned. Thank you!

Many thanks for great tutorial. 🙌🏻

this course is very useful and quick not boring at all 😍🤩🤩

I don’t know if this helps, but I found out you can run TRIM, LTRIM, RTRIM, Lower and Upper, all in one go from Select. Example: SELECT EmployeeID, LTRIM(1002) As IDTRIM, LastName, RTRIM(‘John’) As Name, LastName, UPPER(‘Jim’) As New FROM EmployeeErrors

Hey Alex, awesome video. Do you have a video for Windowing functions?

great lesson

amazing tutorials

Good expalanation thanks

Proper works fine too, just tried it

I didn't see bad data at all hahahah It'd not be a surprise if Michael Scott put Fired as the last name of Toby and write it wrong, and put Beasely and Jimbo nicknames as well 😂😂Gotta love the consistency with the show in this tutorial

Thank you so much Alex, you've been a great help to my Data analytics journey. I noticed the original inputs on the error table doesn't change after using the TRIM functions, how do we achieve this?

Thanks well explained Looking forward to your py videos, are you doing any pandas ones?

@AlexTheAnalyst

3 жыл бұрын

Oh yes - I plan on a whole series of just Pandas :)

I am curious to know... Why Alex put comma(s) before line (Create/Insert statement)? Thank you for these tutorial videos 😀

Hello sir can you please make one separate video on substring 🙂

You explain everything so smoothly I appreciate your work! In the last part of this video, is there a way to only lowercase the "O" in "TOby"? Or is it all upper / all lower?

@vrushalikulkarni4452

Жыл бұрын

Hi Willie. This will help SELECT error.FirstName, UPPER(SUBSTRING(error.FirstName, 1,1)) + LOWER(SUBSTRING(error.FirstName, 2, LEN(error.FirstName) - 1)) FROM EmployeeErrors as error

@mayavik1034

Жыл бұрын

Did you mean like ‘Proper’ in Excel?

@pensenaute

2 ай бұрын

@@vrushalikulkarni4452hi! Why isn’t it enough to just lower the “o” using your substring query?

Great video :)! Thanks so much!

Hey alex thanks for the video Just a question Why do sometime i need to refer to whole database and sometimes just the table name while refering to the tables during join or some other operations ?

Can you join on multiple fields? In other words, have multiple ON statements in a single join?

Thanks Alex, I'm curious if there's a TITLE string command to revert back to "Title" case from either "UPPER" or "lower", something I come across occasionally.

@annepivovarov2484

10 ай бұрын

Hello, we have the same question. Mind sharing the answer if you found one already? Thanks!

I am really enjoying this. at first I tried all programming languages but now I think I know what I want. I want to be a data scientist and am sure taking a data analytics course to master these skills first isn't wrong right?

@ritizadhikari7984

7 ай бұрын

same here for me. Tried going into cyber security but didn't find so much enthusiasm in that. Now here learning SQL and python libraries like numpy and pandas

Whoa the sound is suddenly better.

Can you use the UPPER or LOWER case Stringfunction on only the First Letter for example?

@AlexTheAnalyst

3 жыл бұрын

I believe there is something called PROPER() - or something like that. I don't use it myself, but something to look into.

Hi Alex, I want to ask what's difference replace and update?

Hello Alex My name is John all the way from Uganda(africa) I wanted to follow a path to becoming a data analyst through self study but all I need from you is what exactly do I need to study n concentrate on. otherwise thanks for the good work

And if we wanted to lower case just the second letter of the name TOby for example, or any other name in which the second letter is uppercase we could use the following: *LEFT meaning only the letters starting from the LEFT, and the number 1 means only 1 letter. Basically only the first left letter. *LEN meaning the length of the name, so basically this will use the maximum length of the name. SELECT FirstName, UPPER(LEFT(FirstName,1)) + SUBSTRING(LOWER(FirstName),2,LEN(FirstName)) AS CorrectedName FROM SQLTutorial..EmployeeErrors But is there a better and shorter way ?

Hi sir we have PROPER function in EXCEL do we have anything like that in SQL

I noticed that some commands were written in lowercase and some in uppercase; is there a best practice procedure using either format?

Hi Alex, thank you for this tutorial. I am getting an error for the substring - Invalid object name ‘EmployeeDemographics’ Please how do I sort this? I’m stuck

Hi Alex, Thanks for the boot camp, Really helpful for newbies. Just a question, Why can't we just use the update command instead of replace, Like: Update EmployeeErrors Set LastName = 'Flenderson' Where Firstname = 'Toby' The outcome is the same.

@rebellis7904

5 ай бұрын

YOU CAN, IS JUST OTHER WAY TO GET THE SAME RESULT

thnak you for the git hub, can you add that to all your videos? thank you.

What if we want to filter numbers instead of strings based on first 3 or last 3 values from a column,is there a specific alternative to substring function?

Length function can be used to understand change post RTRIM better

Hey Alex how are you? Hope you’re doing good I’m gonna start school in the next summer ,the studies that I want are statistics with Data science. I wanted to ask you 2 questions: 1. How can I know for sure that I like the data science studies? 2.unfortunately, the first course in the degree is Java and I heard that it’s very hard Compared to python or sql.. I’m new in the programming world and I want to start the basic python course soon..how can I prepare myself better for the degree and Java studies? Thanks and stay safe:)

@AlexTheAnalyst

3 жыл бұрын

Hey! 1. The best way to know is to try! You can always take some courses on Udemy beforehand and see if you enjoy those. 2. Yes, Java is complicated comparatively, but can be useful to know. I don't use it at all, but I've taken some courses on it. Not my thing to be honest lol My biggest advice is to start studying now so it isn't a total shock when you start.

@kobi981

3 жыл бұрын

@@AlexTheAnalyst thanks man!

More information on this.... need to watch more than once 😂

Done

Hi Alex, I'm a finalist on a bachelor and I just got an academic internship for a BI team.. I want to apply for my masters (at night) and although different I love both BI and Data Science... In terms of future, what would give me more job stability? Any thoughts? Thx in advance and thx A LOT for all the videos you make they really help!!!

@YoSoyWerlix

Жыл бұрын

Which one did you chose?🎉

@uchindamiphiri1381

7 ай бұрын

😀😆@@YoSoyWerlix

I like those references to The Office 😂

Hi Alex, Nice video. I am currently doing the job role of Rpa Developer, specialized in Uipath. But I do not have a future as I am not an expert in Coding. Is it advisable for me to switch my career to Data Analyst? Please reply.

@AlexTheAnalyst

3 жыл бұрын

Hard to say without more information, but if you like databases and visualizations could be a good move?

Is this for checking purpose or editing purpose?

hey man, do you any ENFPs who love being a Data analyst?

Great Video, I was wondering if there is an efficient way to convert the string into Proper case? since SQL doesn't have any PROPER function..

@AlexTheAnalyst

Жыл бұрын

Maybe using substring with upper/lower. Substring the first letter and make it Upper and substring the rest and make it lower? Haven't tested it out myself through lol just a thought

@anjaliwadhwani5425

Жыл бұрын

@@AlexTheAnalyst select firstname, (upper(substring(firstname,1,1)))+(lower(substring(firstname,2,100))) as new_name from EmployeeErrors where FirstName='sam' I tried this and it is working😀

Can we apply lower on a substring function? Like select firstname, lower(substring(firstname,2,3)) from tablename

@nicolasm9408

2 жыл бұрын

Sure, but that would only output the substring you mentioned. You could do a CONCAT(UPPER(SUBSTRING(first letter),LOWER(SUBSTRING(Rest of letters))

Hey Alex, I'm a cybersecurity major who has been exposed to python, java, HTML, and some networking as well. Does a sports analyst fit my major?

@mayavik1034

Жыл бұрын

Did you do the Sports Analytics course on Coursera?

Great video) I got one question, how we can replace last name, here is the situation. I put where condition it did not work FirstName Last Name Alison -Plunket Madison -Plunket

8:05 can someone tell me how he could just type [EmployeeDemographics] without adding the sources [SQLTutorial] like the previous Queries??

@AbiCabadin

3 ай бұрын

He is on the SQLTutorial database. He does not need to specify the location of the table.

Hi Alex, I have a question here about using UPPER and lower. How do we make the first letter as UPPER and all other letters following it as lower letters??

@jwatts24

Жыл бұрын

It would be something as follows: UPPER(LEFT(FirstName, 1)) + LOWER(SUBSTRING(FirstName, 2, LEN(FirstName))) To break it down - the first section is taking the starting on the left side of the FIrstName and only uppercasing the first letter of the name. The second section lowercases the remaining characters that are in the FirstName place. Using the substring function, you are gathering all the characters that are in the FirstName that starts at the second character. From that, you use the LOWER function to make sure everything that was captured in the substring function to be lowercased. I use this frequently for work. I'm sure there is a better way to do this but this is just memory at this point.

@mayavik1034

Жыл бұрын

Did you mean like ‘Proper ‘ in Excel?

Hi Alex, thank you ones again for putting this together. I have a little challenge. The substring didn't fully work for me. The command was right. It displayed only the column but didn't display the datas. The other string worked just right. How do I fix this please?

@horangheeeeee

Жыл бұрын

Hi! I hope you figured it out already. But if not, I hope I can help you with it. Maybe because you are missing some information on EmployeeDemographics table. In the previous tutorial, Alex gave an example wherein we replaced new information on the EmployeeDemographics table ( I can't fully remember which tutorial it was), so we deleted that Employee and replaced a new one. Therefore you need to add the information first on EmployeeDemographics table before joining it to a new one (EmployeeError table). This will be the code if you are missing that data. Insert into EmployeeDemographics Values ('1005', 'Toby', 'Flenderson', 35, 'Male') Then you can now join these two tables mentioned in this tutorial.

@catemarble149

11 ай бұрын

@@horangheeeeee thank you for this!!

Hi Alex! Thank you for this amazing tutorial! I run the substring code that you have kindly shared with us and that i put below but it show me just Jim and Pam but not TOby. Maybe it's keysensitive or something else? I know that i gave you just few infromations and i don't expect an answer but i thank you for this beautiful video. I send you an hug! Select Substring(err.FirstName,1,3), Substring(dem.FirstName,1,3), Substring(err.LastName,1,3), Substring(dem.LastName,1,3) FROM EmployeeErrors err JOIN EmployeeDemographics dem on Substring(err.FirstName,1,3) = Substring(dem.FirstName,1,3) and Substring(err.LastName,1,3) = Substring(dem.LastName,1,3)

@gagatonukari6482

Жыл бұрын

The same thing happened when I executed the substring code, were you able to find a solution?

@jrgelpe8434

Жыл бұрын

I think it has something to do with the INNER JOIN OR JOIN in the query. Since Tob is different to TOb, it will not satisfy the matching.

@TheMarioBrotherz

Жыл бұрын

I found the solution, Toby got deleted from the intermediate class UPDATING/DELETING SQL data. need to reenter his information

How it could be if I need to change the case of one specific character alone. -- Select FirstName , Lower(Substring(LastName, 1,1) from Table_Name; -- # I am changing the first letter of my last name into lower case. Do you think is this a right approach? if not, kindly rply with the correct method to do.

As a beginner, is it necessary to SQL projects to showcase my skill in SQL? If so, what kind of projects do you suggest for a beginner to do?

@freakhead10

3 жыл бұрын

No, it is not in my opinion. You need just take a course maybe and watch some KZread videos. In the interview, they typically will have you do an example of a group by, join, or temp table. At least for the entry-level positions. They won't ask to see an SQL project. Although, they may ask about your past projects and it's challenges.

Dear Alex, I can't use Trim function in SQL 2018. SQL show messege : 'TRIM' is not a recognized built-in function name. Tks for help Alex. :D

What's the difference between using the master server and the SQL Tutorial ? l am often confused which one to use usually

@AlexTheAnalyst

3 жыл бұрын

Just different databases. Use whichever one you put your table in.

i just wanna ask there are upper and lower ..what about the proper is it exist or not ?

Can’t u use update instead of the replace . Because I used update and it got it done

👍

Hello Alex, I have a little issue here. While inserting values to my EmployeeError table, the blank spaces inserted in the EmployeeID did not reflect on the table. My table executed without any blank space in the EmployeeID. What could I be doing wrong?

@ignaciorepetto1061

Жыл бұрын

Hello! The same happened to me, probably caused by the data type. see that he has varchar(50) for the EmployeeID.

Hi Alex, How can we do a proper case in SQL? Is there a way to do that?

@rajkumarrajan8059

9 ай бұрын

Like in the above example 'TOby' to 'Toby'

Why can't you do it as replace(lastname, '-%',' ') ? Is there a reason you can't use the wild card?

I seem to be having trouble with the REPLACE function. I have the query written out exactly as it is shown on screen, however, the last name is not being changed in the new column. When I tried to change the last option to change the last name, the same thing happened and nothing new was inputted into the new column. Any advice?

@timemage729

2 жыл бұрын

... never mind.. I was missing a space between the "- Fired" ..

I did not understand the fuzz matching and why only TOby came as outcome?

how do i correct table.EmployeeErrors ID-1002 row of extra spacing through sql not manual replacing

Hi Alex! by running substring code , I didn't get the result in the table?? select err.FirstName, substring(err.FirstName, 1,3), dem.FirstName, substring(dem.FirstName, 1,3) From EmployeeErrors err join EmployeeDemographics dem on substring(err.FirstName, 1,3) = substring(dem.FirstName, 1,3)

Hi Alex, under STRING FUNCTIONS + USE CASES video - I tried to execute the Fuzzy match example you ran, however it is not showing me any results at all - I tried to Drop the EmployeeErrors table and created it again but still nothing is showing up I can only see column headers but no 'Toby' data :(

@franzjosefbenjaminmacapaga278

Жыл бұрын

update: I got it now, it is because earlier in the videos we removed Toby in the EmployeeDemographics table so there is no result when I do fuzzy match query, what I did is to enter back toby's details in EmployeeDemo

Should've made the error for Pam as 'Pan' "Okay, see you later Pan" - Dwight

@AlexTheAnalyst

2 жыл бұрын

Missed opportunity right there

How can you fix TOby by make only the first letter upper and the rest lower?

@mercymallel2074

Жыл бұрын

Select FirstName, UPPER(SUBSTRING(FirstName,1,1)) + LOWER(SUBSTRING(FirstName,2,8)) FROM EmployeeErrors

why do i have two rows of TOby after executing ?

The fuzzy matching code does not work for TOby on my end, it only works for Jim and Pam

@yanpaucon1043

3 ай бұрын

same, Have you figured it out already? Thanks

But is this trim and replace stays in the table permanently ?

@AlexTheAnalyst

Жыл бұрын

If it's in the Select portion, then no. You'd have to update the columns/table itself to permanently update it.

Hi, how to fix this. After creating a table, I can't see where the table is?Before I had the success of creating a table but now i can't see the table after creating the table, can anyone help me? Thank you

How does your column name automatically change to its name in the column.. .I struggle a lot in writing exactly my table.column_name...pls explain to anyone!

@5:09 This query is not working in MySQL.

learning this is great, but how can these changes/fixes be made permanent? when i go through the examples, the table with errors still contain the errors and the data doesn't update..

@pensenaute

2 ай бұрын

Hi! I’m wondering the same, did you figure it out?

Using Replace: Flenderson-fired nothing Changed. Mine didn't work. Any help

@emekahenry8140

7 ай бұрын

Solved it. I had to put space between ‘-' and fired for it to work. Great Tutorial Mr. Alex

Why use LTRIM and RTRIM at all? Why not just TRIM everything?

Hi everyone! I don't get it why TOby doesn't appear in my table when I do the Substring.

@miulerbm

Жыл бұрын

Hi, I was having the same problem. I think it was caused by some previous part of the bootcamp where Alex deleted that row. Inserting it again worked for me INSERT INTO EmployeeDemographics VALUES (1005, 'Toby', 'Flenderson', 32, 'Male') After that, run again the substring part and it should work.

im a marketing major trying to learn data analytics, i do not have any background in programming so i dont understand the substring function at all I cant imagine a use for it other than getting a few characters from a first name for example. watched it over still dont get it😂

No regular expressions :(

@AlexTheAnalyst

3 жыл бұрын

Coming soon to a YT video near you :)

4:47 Here, "- Fired" should be " - Fired" Otherwise, you will return "Flenderson " (with 1 space character)

Its not working for me

😂😂Not Michael learning SQL to insert 'Fired' CREATE TABLE #Damnit_Michael (EmployeeID varchar(50), FirstName varchar(50), LastName varchar(50), Age int, Gender varchar(50)) INSERT INTO #Damnit_Michael SELECT * FROM EmployeeDemographics UPDATE #Damnit_Michael SET LastName = LastName + ' - Fired' WHERE EmployeeID = 1005 SELECT * FROM #Damnit_Michael

It only showed jim and Pam, I don't know why it didn't show Toby.

Hey Alex,great fan of your work by the way, but the SUBSTRING concept was poorly explained.

1:16 NO GOD! NO GOD! PLEASE NO!!! NO NO NOOOOOOOOOO

again this toby caught me offguard, finally toby got fired after didnt get raise salary and got deleted from list XD

CREATE TABLE dd/mm/yyyy ? Teach immediately thank you

Can we apply lower on a substring function? Like select firstname, lower(substring(firstname,2,3)) from tablename

@romanvasiura6705

Жыл бұрын

you can try)

How does your column name automatically change to its name in the column.. .I struggle a lot in writing exactly my table.column_name...pls explain to anyone!