Real heroes are not those who go to wars or pick a fight or engage in any destructive activity. Real heroes are those who make, build, create and educate. These men and women make world a better place. Our culture should reinvent itself. We need to reconfigure our culture in a way that movies and books and comic books depict this kind of heroism. Kyle, you are a real hero. I appreciate your great work and I hope you prosper. People like you deserve to be powerful and rich, because you contribute to others' well being with your power.
I am not gonna argue that people that create and give back are heroes, but as a person who lives in a country that is being torn apart by another rotten country - I am sure thankful for heroes who fight in inhumane conditions to protect their home and our existence.
I have just finished your exercises and came by to take a peek whether or not I was in the right direction. Sure enough I did a pretty decent job thanks to your teaching. I cannot express how much I very much appreciate your job here on youtube. Thank you ever so much and I hope your channel grow atomically!
In the repo You have a misspel in the column name: instead of "length" it is "lenght" Thx for the 60 min intro and for the exercises! You have much more structured lecture and easily percieved speach than other SQL teachers on youtube
This was incredible. As a beginner, some of the exercises were tough but I'm glad I completed all the exercises. Surprisingly, 12 was among the easiest for me.
learning anything is possible with a great teacher, i ahve been trying to understand the structure of the SQL programming until i watched your 60mins video and now i am able to solve all the questions that you explain here. thank you very much, great work, great quality!
You are 10/10 explaining SQL and breaking it down pretty well. This however, is really not complicated at least "solving a problem", unrealistic example by all means wish you would do more to explain the in depth 3 table inner join, with nested queries etc
Looks like many of us are having some issues with #5. Can anyone help me understand why the COUNT(albums.id) doesn't read COUNT(albums.band_id)?? It makes more sense to me and returns the same result...? Way to go everyone. Keep it up!
Just as a side thought, I would think that the better-optimized query would be using WHERE instead of HAVING to define the filter. Because where would take just a limited number of records, and having would consider all of them, so it would be significantly faster. wouldn't it? Again, it is just an opinion :)
DROP in SQL means that entire table will be dropped (the table will not exist after this command) with all data in it. And there is no "Are you sure ..." :) Read about SQL TRUNCATE vs DELETE vs DROP.
Hey Followed all the instructions but its not letting me create the songs table, giving me an error code 1063 : incorrect column specifier for column 'name'
In the exercise 11, his returning the longest song duration for each album, without decimal places, wich means, his auto rounding the values and i don´t know why ? Can someone give me a tip why ? The Query is 100% rigth btw
Hi, I get this error in exercise 5, even when I use the solution code, did anyone get the same? Error Code: 1055. Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'record_company.bands.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
I had the same error & then changed the group by and it worked SELECT b.name AS 'Band Name' FROM bands AS b LEFT JOIN albums AS a ON b.id = a.band_id GROUP BY b.id HAVING COUNT(a.id) = 0;
@@bitetheapple8 I had the same error. Thanks for the solution. I also used a different query. SELECT b.name AS 'Band Name' FROM bands AS b LEFT JOIN albums AS a ON b.id = a.band_id WHERE a.band_id IS NULL;
I solved #12 using nested queries and noticed that your answer does not display that band "Dream Theater" because they have no songs. I believe this is because your joins are left joins and not outer joins
A left join is an outer join, aka left outer join. He uses inner joins. That's why. A simple (just) join is an inner join. At least in MySQL... If he also had a where clause, sometimes the where clause can turn a left join into an inner join. I don't he would ever cover an issue like that...
I have a problemin my charging patient table .the data insert in the table through 8 forms.....the sequence which is define for this table jumped ..and escape the values...the structure of the sequence having min 1 and mix 10 lakh with nocash .. so the problem is that the sequence is jump ..it miss values in the table...whts should be do??? How to fix this problem ???.. i think the problem is in the forms where data is inserted in the table ... Any one help to fix my problem???
AS Something means that you are giving that column other name than its original name. In this case its original name is name with small letters. On the output however it will appear as Name with cap N. It has nothing to do with the value type of the column. You would need apostrophe if more than one word is used for renaming or the word used is somewhat reserved by he SQL syntax . In that same example it puzzles me though why Kyle uses apostrophe for Duration.
For Problem 7: SELECT * FROM albums WHERE release_year IS NULL; UPDATE albums SET release_year =1986 WHERE id =4; You used UPDATE specifically for where the key was id=4, but what if we had a larger data set and we wanted multiple id's. Like id=4, id =10, id=38, id=50. Is there a way in which we could find these id's without manually searching for them and manually putting in id values to update. Thank you for these videos! They are very clear and helpful!
You can use anything in the where section of your update. For example if you wanted to update all albums with a release_year of 2000 you could say WHERE release_year = 2000
I think it is possible to update many rows at once but only it one fixed year. Like all of them to have release_year = 1986. If you want however to have release_year = 1989 for id 4 and release_year=1999 for id 10 ... etc. then I do not think it would be possible other than manually.
I have a problem for you to solve. Let's say you have a temp table with 2 million records that contains some fields. One of the fields contains one of a couple dozen formulas as a string. The couple dozen formulas all occur in some of the 2 million records... The formulas references other fields in your table and might also use a multiplier. I.e. "HrsPerDay*1", "HrsPerDay*1.25", "HrsPerDay*1.5", HrsPerWeek/5*1.25*fte", etc.. There is also a field that has to be calculated that holds the formula's result. How do you do this? The formulas need to be evaluated as quickly as possible. In less than a minute. 10 seconds would be good...
How do we save data from API calls to our DB? make a practical video showing how JS + SQL work and link between backend and front end, you can do a short simple one not 1h project example. Do it with a web component if you're a god
“Let my DBA have a look at my query” Usually he gets so frustrated he does it with me on the spot. Now a year later im the persons newbies ask for help with SQL queries.
it works but it's not ideal, your filtering through the table twice you should do it like this: Update albums Set releaseyr = 1986 where releaseyr is null;
@@DDismas Patience is key. One year ago, I was terrible at everything but right now I am getting things better. Still have lots of work to do but I can see my progress. Don't worry man and keep on learning!
I am more than 10 years in programming and have positioned myself between 6 or 7 on the 0 to 10 scale. It is based on infinite number of problems I have researched and while I learn, try and find the solution I also see how other guys understand it But I still have most of my days feeling bad about my speed of work at my work place. Philosophers say it is number one virtue of sanity to question yourself and doubt yourself.
On Excercise 6 I get this error even when I copy & paste your solution: "17:25:08 SELECT albums.name as Name, albums.release_year as 'Release Year', SUM(songs.length) as 'Duration' FROM albums JOIN songs on albums.id = songs.album_id GROUP BY songs.album_id ORDER BY Duration DESC LIMIT 1 Error Code: 1054. Unknown column 'songs.length' in 'field list' 0.000 sec:
Wanted to comment, on problem #5. Idk if the system was updated or different keywords were used, but the function didn't go through and it gave me an error. However I read through it and changed the 'GROUP BY' command to 'bands.name' instead of 'albums.band.id' which made the query look like this: SELECT bands.name AS 'Band Name' FROM bands LEFT JOIN albums ON bands.id = albums.band_id GROUP BY bands.name HAVING COUNT(albums.id) = 0; Just in case if anyone else had problems like I did.
I was able to do #5 and get the same result with the following query: SELECT bands.name as 'Band Name' FROM albums RIGHT JOIN bands ON albums.band_id = bands.id WHERE albums.name is NULL; However when I try the answer provided in this video as below I'm getting an Error code 1055: SELECT bands.name AS 'Band Name' FROM bands LEFT JOIN albums on bands.id = albums.band_id GROUP BY albums.band_id HAVING COUNT(albums.id) = 0; Would anyone be able to help explain what I may be overlooking?
Real heroes are not those who go to wars or pick a fight or engage in any destructive activity. Real heroes are those who make, build, create and educate. These men and women make world a better place. Our culture should reinvent itself. We need to reconfigure our culture in a way that movies and books and comic books depict this kind of heroism. Kyle, you are a real hero. I appreciate your great work and I hope you prosper. People like you deserve to be powerful and rich, because you contribute to others' well being with your power.
Thank you so much!
I am not gonna argue that people that create and give back are heroes, but as a person who lives in a country that is being torn apart by another rotten country - I am sure thankful for heroes who fight in inhumane conditions to protect their home and our existence.
I have just finished your exercises and came by to take a peek whether or not I was in the right direction. Sure enough I did a pretty decent job thanks to your teaching. I cannot express how much I very much appreciate your job here on youtube. Thank you ever so much and I hope your channel grow atomically!
In the repo You have a misspel in the column name: instead of "length" it is "lenght"
Thx for the 60 min intro and for the exercises!
You have much more structured lecture and easily percieved speach than other SQL teachers on youtube
Thanks Kyle! Watching this after working through the problems on my own helped solidify SQL foundations. Thank you for all your hard work!
The SQL series is awesome. Just watched it to get a quick refresh of MySQL, and really feeling confident in it now. Thanks Kyle!
This was incredible. As a beginner, some of the exercises were tough but I'm glad I completed all the exercises. Surprisingly, 12 was among the easiest for me.
learning anything is possible with a great teacher, i ahve been trying to understand the structure of the SQL programming until i watched your 60mins video and now i am able to solve all the questions that you explain here. thank you very much, great work, great quality!
You sir, are gonna go lengths! Thank you and all the very best!
What an amazing video. It was really helpful to learn and understand the SQL queries. Thank you so much for this.
Is it normal to group by a column that is not specified in the select statement?
I saw that here 7:13 at line 4. also at 10:31 at line 7
Hello this one was really good, Could you make some more of SQL stuff on your channel which focuses on advanced concepts or practise?
3rd question can be done simply by--
select min(release_year) from albums
kindly provide the answer to that.
same output
Nice tutorial, teached me a lot of good songs.
I can't believe this is free! Thank you so much!
using sql every day, you picked here some nice examples to start off! 👍😉
Can't appreciate this more, thanks a lot SQL hero!
This was incredibly helpful and awesome, thank you Kyle!
Your tuts are the best on YT
Thang you so much❤❤❤ you've just saved my midterm😊
Off topic but great taste in music
Thanks for the lessons!
I've done all exercises, except 3 and 5))
Nice job!
You are 10/10 explaining SQL and breaking it down pretty well. This however, is really not complicated at least "solving a problem", unrealistic example by all means wish you would do more to explain the in depth 3 table inner join, with nested queries etc
Jesus bro, calm down
9/11@@MohammedMisselmany
Hi, i'm asking for the auto_increment statement ! Are we can replace it with the identity statement ??
What a great lesson! Thanks!
oh my gosh your channel is so amazing
Thank you so much, this was really beneficial
You are absolutely incredible. Thank you
Looks like many of us are having some issues with #5. Can anyone help me understand why the COUNT(albums.id) doesn't read COUNT(albums.band_id)?? It makes more sense to me and returns the same result...? Way to go everyone. Keep it up!
Loved these questions! Can you make a new video on how to write optimized queries?
loved the way you explain that! thanks a lot ;)
Ohhh “ ;) “ , she tryna flirt with you bro
at 10:27 whats the difference between doing AS Name and AS 'Name'?
I think the quotes are only necessary if the alias contains a space
Why do you use periods when you are already specifying where the column comes from? I don't understand why and how to use it really...
Just as a side thought, I would think that the better-optimized query would be using WHERE instead of HAVING to define the filter. Because where would take just a limited number of records, and having would consider all of them, so it would be significantly faster. wouldn't it? Again, it is just an opinion :)
14:21 can we use "drop" instead of delete?
DROP in SQL means that entire table will be dropped (the table will not exist after this command) with all data in it. And there is no "Are you sure ..." :)
Read about SQL TRUNCATE vs DELETE vs DROP.
Hey Followed all the instructions but its not letting me create the songs table, giving me an error code 1063 : incorrect column specifier for column 'name'
is there a problem here requiring analytic not practice how to input and where to etc. Like real world problem
thank you so much for this!
for the 3rd query can't we just write it as SELECT * FRFOM ALBUMS WHERE RELEASE_YEAR=MIN(RELEASE_YEAR);
i cant solve those tasks based on the tutorial you provided, sure i can google similar cases and copy, this is so frustrating
for question 11, I got an error when I also selected column songs.name. Is there a reason for that? Thanks for any opinions
Hello, I am new to this so this might be a naive question but can we solve ques-3 by using the 'Having' clause?
Super sir..i am beginner.. please provide any pdfs of SQL .to learn
In the exercise 11, his returning the longest song duration for each album, without decimal places, wich means, his auto rounding the values and i don´t know why ?
Can someone give me a tip why ?
The Query is 100% rigth btw
help, my mySQL is being stupid. I copied and paste your codes into mysql and it wont run it for some reason.
in the last question can we group by band.name
Hi, I get this error in exercise 5, even when I use the solution code, did anyone get the same?
Error Code: 1055. Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'record_company.bands.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
I had the same error & then changed the group by and it worked
SELECT b.name AS 'Band Name'
FROM bands AS b
LEFT JOIN albums AS a ON b.id = a.band_id
GROUP BY b.id
HAVING COUNT(a.id) = 0;
@@bitetheapple8 I had the same error. Thanks for the solution. I also used a different query.
SELECT b.name AS 'Band Name'
FROM bands AS b
LEFT JOIN albums AS a
ON b.id = a.band_id
WHERE a.band_id IS NULL;
@bitetheapple8 Perfect. Thank you for sharing 👍😁
Hey, Kyle. In #5, in the GROUP BY albums.band_id line, is the 'albums.' part necessary? It seems to work without it.
It will work without it. The only time you need the table name before the column is if two tables in your query have a column with the same name.
@@WebDevSimplified Gotcha
I solved #12 using nested queries and noticed that your answer does not display that band "Dream Theater" because they have no songs. I believe this is because your joins are left joins and not outer joins
A left join is an outer join, aka left outer join.
He uses inner joins. That's why. A simple (just) join is an inner join. At least in MySQL...
If he also had a where clause, sometimes the where clause can turn a left join into an inner join. I don't he would ever cover an issue like that...
Nice thanks ❤
Mr. @kyle, please create a training for HAR file training to be analyzed. Thanks in advance...
Failed to execute SQL script statement of class path resource
Why and when its come
Please answer this ..
I have a problemin my charging patient table .the data insert in the table through 8 forms.....the sequence which is define for this table jumped ..and escape the values...the structure of the sequence having min 1 and mix 10 lakh with nocash .. so the problem is that the sequence is jump ..it miss values in the table...whts should be do??? How to fix this problem ???.. i think the problem is in the forms where data is inserted in the table ... Any one help to fix my problem???
In 11th question select longest song of each album. how can we display name of that longest song also.
I think in Oracle 'Group by ' doesn't work like in MySQL. Solution 4, 5, 6 doesn't work.
Why there is no table representation? All the way through the video till the last minute i realised there are 3 tables instead of 2
Is it in SQL servwr or in my SQL ?
In exercise #6 when you return the album name as Name (albums.name AS Name) shouldn't Name has apostrophe since it's a VARCHAR variable?
If the alias has only one word apostrophes are not needed
AS Something means that you are giving that column other name than its original name. In this case its original name is name with small letters. On the output however it will appear as Name with cap N. It has nothing to do with the value type of the column.
You would need apostrophe if more than one word is used for renaming or the word used is somewhat reserved by he SQL syntax . In that same example it puzzles me though why Kyle uses apostrophe for Duration.
@@mocococo2877 hey, man , thanks for the huge help for those of us who asked in comments, appreciate
do u have a discord or skype?
For Problem 7:
SELECT * FROM albums
WHERE release_year IS NULL;
UPDATE albums
SET release_year =1986
WHERE id =4;
You used UPDATE specifically for where the key was id=4, but what if we had a larger data set and we wanted multiple id's. Like id=4, id =10, id=38, id=50. Is there a way in which we could find these id's without manually searching for them and manually putting in id values to update.
Thank you for these videos! They are very clear and helpful!
You can use anything in the where section of your update. For example if you wanted to update all albums with a release_year of 2000 you could say WHERE release_year = 2000
I think it is possible to update many rows at once but only it one fixed year. Like all of them to have release_year = 1986.
If you want however to have release_year = 1989 for id 4 and release_year=1999 for id 10 ... etc. then I do not think it would be possible other than manually.
am i the only one getting 'Error while executing SQL query on database 'album': near "AUTO_INCREMENT":'?
You're probably using a different SQL, try using "IDENTITY (1, 1)" instead of that
I have a problem for you to solve.
Let's say you have a temp table with 2 million records that contains some fields. One of the fields contains one of a couple dozen formulas as a string. The couple dozen formulas all occur in some of the 2 million records... The formulas references other fields in your table and might also use a multiplier. I.e. "HrsPerDay*1", "HrsPerDay*1.25", "HrsPerDay*1.5", HrsPerWeek/5*1.25*fte", etc.. There is also a field that has to be calculated that holds the formula's result. How do you do this?
The formulas need to be evaluated as quickly as possible. In less than a minute. 10 seconds would be good...
Please send me the link to the questions
Hi sir
Your video is useful for
Please c language codeing tell me sir
value otherwise is not a member of org.apache.spark.sql.dataframe
Please give me the correct answer
Master brenches are called Main branches for a good reason
900 th like "Me" 😁😁
thanks sir !
Thank you
How do we save data from API calls to our DB? make a practical video showing how JS + SQL work and link between backend and front end, you can do a short simple one not 1h project example. Do it with a web component if you're a god
“Let my DBA have a look at my query”
Usually he gets so frustrated he does it with me on the spot.
Now a year later im the persons newbies ask for help with SQL queries.
For 7th problem,
Can someone verify this:
Update albums
Set releaseyr = 1986
where id in {
Select id from albums where releaseyr is null
} ;
it works but it's not ideal, your filtering through the table twice
you should do it like this:
Update albums
Set releaseyr = 1986
where releaseyr is null;
I've confused this vdo with learn sql in 60 minutes part 2, it's definitely not...is it??
Well, trying to learn this stuff is just a heavy blow on my self-esteem and constantly being reminded that I'm not very quick at learning
good vids though
@@DDismas Patience is key. One year ago, I was terrible at everything but right now I am getting things better. Still have lots of work to do but I can see my progress. Don't worry man and keep on learning!
I am more than 10 years in programming and have positioned myself between 6 or 7 on the 0 to 10 scale. It is based on infinite number of problems I have researched and while I learn, try and find the solution I also see how other guys understand it
But I still have most of my days feeling bad about my speed of work at my work place.
Philosophers say it is number one virtue of sanity to question yourself and doubt yourself.
On Excercise 6 I get this error even when I copy & paste your solution: "17:25:08 SELECT albums.name as Name, albums.release_year as 'Release Year', SUM(songs.length) as 'Duration' FROM albums JOIN songs on albums.id = songs.album_id GROUP BY songs.album_id ORDER BY Duration DESC LIMIT 1 Error Code: 1054. Unknown column 'songs.length' in 'field list' 0.000 sec:
Very hard to understand for a complete beginner😥
Check out alex the analyst on TH-cam.
Couldn't solve 5, 7 and 12
I post it from command line
The video is good and educational but the lecture is very fast when lecturing.
I will give one simple problem can you solve
I did it! ^_^ Imsh
I solve
Wanted to comment, on problem #5. Idk if the system was updated or different keywords were used, but the function didn't go through and it gave me an error.
However I read through it and changed the 'GROUP BY' command to 'bands.name' instead of 'albums.band.id' which made the query look like this:
SELECT bands.name AS 'Band Name'
FROM bands
LEFT JOIN albums ON bands.id = albums.band_id
GROUP BY bands.name
HAVING COUNT(albums.id) = 0;
Just in case if anyone else had problems like I did.
Thank you for this
thanks, i was confused by answer on githab
I was able to do #5 and get the same result with the following query:
SELECT bands.name as 'Band Name' FROM albums
RIGHT JOIN bands ON albums.band_id = bands.id
WHERE albums.name is NULL;
However when I try the answer provided in this video as below I'm getting an Error code 1055:
SELECT bands.name AS 'Band Name'
FROM bands
LEFT JOIN albums on bands.id = albums.band_id
GROUP BY albums.band_id
HAVING COUNT(albums.id) = 0;
Would anyone be able to help explain what I may be overlooking?