Top 4 SQL Practice Problems That Data Analysts Need To Know
| |

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_Head

Problem 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_Amount

Problem 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_Head

Problem 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.EmployeeNumber

Read more: How to Install SQL 2022 and SSMS 18 on Windows 8/10/11



Similar Posts

15 Comments

  1. 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

  2. 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

  3. 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

  4. 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

Leave a Reply

Your email address will not be published. Required fields are marked *