blogs | Data Science | SQL
Powerful SQL practice for a data analyst in just 5 minutes
Here are the most relevant Top 4 SQL Practice Problems that Data analysts need to know.
To download the Data and learn more about SQL visit my other Blog: Join 3 Tables in SQL and Download the Data.
Problem 1/4 Top 4 SQL Practice Problems
Please send a report on the Total number of employees and the average amount by Department with the name of the head of the department.
select count(distinct e.EmployeeNumber) as Number_of_employees, avg(t.Amount) as average_amount, d.Department, d.Department_Head
from [dbo].[transaction] t
join employee e on t.EmployeeNumber = e.EmployeeNumber
join department d on d.Department = e.Department
group by d.Department, d.Department_HeadProblem 2/4 Top 4 SQL Practice Problems
Please share a report on the top 5 employees with the highest amount. We need this report with details such as Employee ID and Full name.
select e.EmployeeNumber, concat(e.EmployeeFirstName, ' ', e.EmployeeMiddleName, ' ', e.EmployeeLastName) as full_name, a.Highest_Amount
from [dbo].[employee] e join
(Select top 5 sum(amount) as Highest_Amount, employeeNumber
from [dbo].[transaction]
where amount > 0
group by employeeNumber
order by sum(amount) desc) a on e.EmployeeNumber = a.EmployeeNumber
group by e.EmployeeFirstName, e.EmployeeMiddleName, e.EmployeeLastName, e.EmployeeNumber, a.Highest_Amount
order by a.Highest_AmountProblem 3/4 Top 4 SQL Practice Problems
Share a report of the total amount, and percentage of the amount by the department with the department name, and name of the Head
select sum(t.Amount) as total_amount, d.Department, d.Department_Head, cast(sum(t.Amount)/sum(sum(t.Amount)) over (order by (select null)) * 100 as Decimal(6,2)) as perc
from [dbo].[transaction] t
join employee e on t.EmployeeNumber = e.EmployeeNumber
join department d on d.Department = e.Department
group by d.Department, d.Department_HeadProblem 4/4 Top 4 SQL Practice Problems
Share a report on full details of employees from 1000 to 1010. We need the department, full name, total amount, age, and DOB.
select e.EmployeeNumber ,concat(e.EmployeeFirstName, ' ', e.EmployeeMiddleName, ' ', e.EmployeeLastName) as full_name, sum(t.Amount) as Total_Amount, e.DateOfBirth,
DATEDIFF(yy, e.DateOfBirth, GETDATE()) + (case when DATEPART(month,GETDATE()) - DATEPART(MONTH, e.DateOfBirth) <0 then -1 else 0 end) as age
from [dbo].[transaction] t
join employee e on t.EmployeeNumber = e.EmployeeNumber
join department d on d.Department = e.Department
where e.EmployeeNumber >= 1000 and e.EmployeeNumber <= 1010
group by e.EmployeeFirstName, e.EmployeeMiddleName, e.EmployeeLastName, e.DateOfBirth, e.EmployeeNumberRead more: How to Install SQL 2022 and SSMS 18 on Windows 8/10/11
Hi! I’ve been reading your blog for a while now and finally
got the bravery to go ahead and give you a shout out from
Austin Texas! Just wanted to say keep up the good
work!
Also visit my site; slot machine app
Hmm is anyone else experiencing problems with the images on this blog loading?
I’m trying to figure out if its a problem on my end or if
it’s the blog. Any responses would be greatly appreciated.
My web site: bigbaazi
Great beat ! I wish to apprentice even as you amend your website,
how can i subscribe for a weblog site? The account aided
me a applicable deal. I were tiny bit acquainted of this your broadcast provided
bright transparent idea
Feel free to visit my blog :: āđāļāļĢāļāļīāļāļāļĢāļĩ āđāļĄāđāļĄāļĩāđāļāļ·āđāļāļāđāļ
Touche. Sound arguments. Keep up the great work.
My web blog … āđāļ§āđāļāļāļĢāļāđāļĄāđāļāđāļēāļāđāļāđāļĒāđāļāļāđāļāļĩāđāļāļĩāļāļĩāđāļŠāļļāļ
Remarkable things here. I am very happy to look your post.
Thank you a lot and I am having a look forward to touch you.
Will you kindly drop me a e-mail?
my site āļāļēāļāļēāļĢāđāļēâ āļĄāļ·āļāļāļ·āļ
Today, I went to the beach front with my children. I found a sea shell and gave
it to my 4 year old daughter and said “You can hear the ocean if you put this to your ear.”
She placed the shell to her ear and screamed. There was a
hermit crab inside and it pinched her ear.
She never wants to go back! LoL I know this is completely off topic but I had to tell someone!
my web-site: slot
Very good write-up. I absolutely appreciate this website.
Stick with it!
My web page: āļĢāļāļĒāļąāļĨ āļāļēāļŠāļīāđāļ
I’m amazed, I must say. Rarely do I come across a blog that’s both equally educative and amusing, and let me tell you, you have hit
the nail on the head. The problem is something not enough folks are speaking intelligently about.
I’m very happy I found this during my hunt for
something concerning this.
Feel free to surf to my blog :: casino
I believe this is among the such a lot significant information for me.
And i am satisfied studying your article.
However want to observation on few general issues, The site
taste is wonderful, the articles is in reality excellent :
D. Good job, cheers
my page; Roulette Online
Wow! After all I got a weblog from where I know how to in fact obtain useful
data regarding my study and knowledge.
Here is my web page … āļāļēāļāļēāļĢāđāļē āđāļ§āđāļāđāļŦāļāļāļĩ
Hi there mates, how is everything, and what you wish for to say on the topic of this paragraph,
in my view its genuinely amazing in favor of me.
Also visit my site – āļāļāļĢāļąāļāđāļāļĢāļāļīāļāļāļĢāļĩ
Greate article. Keep posting such kind of information on your site.
Im really impressed by your blog.
Hello there, You’ve done an incredible job. I will certainly digg it and
for my part suggest to my friends. I am confident they’ll be benefited from this website.
Here is my blog post; āļŠāļĨāđāļāļāđāļāļĢāļāļīāļāļāļĢāļĩ
This post will help the internet viewers for
creating new website or even a weblog from start to end.
my web blog: āđāļāļĢāļāļīāļāļāļĢāļĩ āđāļāđāļŠāļĄāļąāļāļĢāļĨāđāļēāļŠāļļāļ
Greate article. Keep writing such kind of info on your page.
Im really impressed by your blog.
Hey there, You’ve done an incredible job. I will certainly digg it and
personally suggest to my friends. I’m sure they will be
benefited from this site.
Here is my blog post :: āđāļŦāļĨāļ āļŠāļĨāđāļāļ xo
I love what you guys are usually up too. This type
of clever work and coverage! Keep up the terrific works guys I’ve added you guys to my blogroll.
Look at my homepage :: casinodays