How To Solve SQL Problems
ฝัง
- เผยแพร่เมื่อ 15 มิ.ย. 2024
- In this video I will be solving all of the SQL problems in my Learn SQL GitHub repository. I will be explaining my thought process and the solution as I go to help build a stronger understanding of why I do certain things. If you haven't already watched my Learn SQL In 60 Minutes video or done the exercise problems first, make sure to checkout the links below and do both those first.
IMPORTANT: Exercises Worksheet Repository:
github.com/WebDevSimplified/L...
Learn SQL In 60 Minutes:
• Learn SQL In 60 Minutes
Outline:
[00:29] - Problem 1
[02:40] - Problem 2
[03:20] - Problem 3
[04:30] - Problem 4
[05:57] - Problem 5
[07:42] - Problem 6
[10:24] - Problem 7
[11:47] - Problem 8
[13:52] - Problem 9
[15:05] - Problem 10
[15:54] - Problem 11
[17:36] - Problem 12
Twitter:
/ devsimplified
GitHub:
github.com/WebDevSimplified
CodePen:
codepen.io/WebDevSimplified
#SQL #LearnSQL #MySQL
![ทัศนศึกษาโรงงานผลิตโค้ก [THAI/ENG SUB]](http://i.ytimg.com/vi/M9y-TJBNem0/mqdefault.jpg)
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 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!
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 incredibly helpful and awesome, thank you 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.
You sir, are gonna go lengths! Thank you and all the very best!
What a great lesson! Thanks!
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!
I can't believe this is free! Thank you so much!
Can't appreciate this more, thanks a lot SQL hero!
using sql every day, you picked here some nice examples to start off! 👍😉
3rd question can be done simply by--
select min(release_year) from albums
Your tuts are the best on YT
You are absolutely incredible. Thank you
Hello this one was really good, Could you make some more of SQL stuff on your channel which focuses on advanced concepts or practise?
Nice tutorial, teached me a lot of good songs.
What an amazing video. It was really helpful to learn and understand the SQL queries. Thank you so much for this.
Thanks for the lessons!
I've done all exercises, except 3 and 5))
Nice job!
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
Thank you so much, this was really beneficial
Thang you so much❤❤❤ you've just saved my midterm😊
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
oh my gosh your channel is so amazing
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
Hi, i'm asking for the auto_increment statement ! Are we can replace it with the identity statement ??
Nice thanks ❤
thanks sir !
Hello, I am new to this so this might be a naive question but can we solve ques-3 by using the 'Having' clause?
Off topic but great taste in music
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
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...
for question 11, I got an error when I also selected column songs.name. Is there a reason for that? Thanks for any opinions
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 :)
Failed to execute SQL script statement of class path resource
Why and when its come
Please answer this ..
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
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
for the 3rd query can't we just write it as SELECT * FRFOM ALBUMS WHERE RELEASE_YEAR=MIN(RELEASE_YEAR);
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
in the last question can we group by band.name
at 10:27 whats the difference between doing AS Name and AS 'Name'?
i cant solve those tasks based on the tutorial you provided, sure i can google similar cases and copy, this is so frustrating
Super sir..i am beginner.. please provide any pdfs of SQL .to learn
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?
Mr. @kyle, please create a training for HAR file training to be analyzed. Thanks in advance...
help, my mySQL is being stupid. I copied and paste your codes into mysql and it wont run it for some reason.
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.
Is it in SQL servwr or in my SQL ?
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
Please send me the link to the questions
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
Hi sir
Your video is useful for
Please c language codeing tell me sir
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.
value otherwise is not a member of org.apache.spark.sql.dataframe
Please give me the correct answer
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...
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.
900 th like "Me" 😁😁
Master brenches are called Main branches for a good reason
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!
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 👍😁
I did it! ^_^ Imsh
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 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
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.
“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.
Couldn't solve 5, 7 and 12
Very hard to understand for a complete beginner😥
Check out alex the analyst on TH-cam.
I will give one simple problem can you solve
The video is good and educational but the lecture is very fast when lecturing.
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
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: