Just a day ago I have participated at an exam, organized from SoftUni for SQL server. The exam can be found here and the questions for it are here. In the current article I will present my answers from the first part of the exam, e.g. Part II – SQL Queries.
So, let’s start. In order to build the database. Just open a SQL Server, make a query and paste this code in it. Within seconds, you will have the following DataBase:
It’s a good one, plenty of it is self-explanatory while looking at the column names, thus you do not need to take a look of the data in the tables a lot. Anyhow, let’s start with the solutions:
Problem 1:
Display all characters in alphabetical order. Submit for evaluation the result text comma separated with headers.
Solution:
Pretty much a trivial problem, just to make sure that everything works, when you are submitting the solution. Here is it:
|
1 2 3 |
use Diablo select Name from Characters order by Name asc |
Problem 2:
Find the top 50 games ordered by start date, then by name of the game. Display only games from 2011 and 2012 year. Display start date in the format “YYYY-MM-DD”. Submit for evaluation the result text comma separated with headers.
Solution:
Well, I was surprised that I had something that hard as a second problem, I was expecting a few easier before these. Anyhow, the way to solve it is to use CONVERT, conditions and top. The YYYY-MM-DD format is 126. This is how the query looks like:
|
1 2 3 4 5 6 |
use Diablo select top(50) Name [Game], convert(char(10), start,126) [Start] from Games where Start >= '20110101' and Start < '20130101' order by start,name |
Problem 3:
Find all users along with information about their email providers. Display the username and email provider. Sort the results by email provider alphabetically, then by username. Submit for evaluation the result text comma separated with headers.
Solution:
Here we need to make some magic with the strings. At first I was thinking of regex in sql, but later I have simply decided to try with RIGHT and LEN, quite familiar from MS Excel. So I have managed to build it like this:
|
1 2 3 4 5 |
use Diablo select Username [Username], RIGHT(Email, LEN(Email) - CHARINDEX('@', email)) [Email Provider] from users order by [Email Provider], Username |
Problem 4:
Find all users along with their IP addresses sorted by username alphabetically. Display only rows that IP address matches the pattern: “***.1^.^.***”. Legend: * – one symbol, ^ – one or more symbols
Solution:
Here it is important to know how to use ‘LIKE’, ‘%’, ‘_’.
‘_’ stands for one sign, ‘%’ for many. The rest is easy:
|
1 2 3 4 5 6 |
use Diablo select Username [Username], IpAddress [IP Address] from users where IpAddress LIKE '___.1%.%.___' order by Username |
Problem 5:
Find all games with part of the day and duration sorted by game name alphabetically then by duration and part of the day. Parts of the day should be Morning (time is >= 0 and < 12), Afternoon (time is >= 12 and < 18), Evening (time is >= 18 and < 24). Duration should be Extra Short (smaller or equal to 3), Short (between 4 and 6 including), Long (greater than 6) and Extra Long (without duration).
Solution:
Now we have something to think about. It seems that we have to generate values in columns, based on their values. With cases. And we should even modify the values in the cases, while extracting, because the time value was given in date format and we needed only the hour. This looks like programming to me 🙂 and not simple SQL building. Anyhow, this is how to do it:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
use Diablo select Name [Game], Start = Case when DATEPART(Hour, Start) >= 0 and DATEPART(Hour, Start) < 12 then 'Morning' when DATEPART(Hour, Start) >= 12 and DATEPART(Hour, Start) < 18 then 'Afternoon' else 'Evening' end, Duration = Case when Duration <= 3 then 'Extra Short' when Duration >= 4 AND Duration <=6 then 'Short' when Duration >6 then 'Long' else 'Extra Long' end from Games order by [Game], Duration, Start |
Problem 6:
Find number of users for email provider from the largest to smallest. Submit for evaluation the result text comma separated with headers.
Solution:
It seems that we should use the all mighty GROUP BY here and the code we wrote in Problem 3. Well, I managed to achieve it like this:
|
1 2 3 4 5 6 7 8 9 |
use Diablo select RIGHT(Email, LEN(Email) - CHARINDEX('@', email)) [Email Provider], COUNT(Email) [Number Of Users] from users group by RIGHT(Email, LEN(Email) - CHARINDEX('@', email)) order by [Number Of Users] DESC, [Email Provider] ASC |
Problem 7:
Find all user in games with information about them. Display the game name, game type, username, level, cash and character name. Sort the result by level in descending order, then by username and game in alphabetical order. Submit for evaluation the result text comma separated with headers.
Solution:
Well, here you should simply join the tables correctly. You have six columns from different tables, going through many-to-many relationship. Playing a little with left and right join is going to do the trick.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
use Diablo select Games.Name [Game], GameTypes.Name [Game Type], Users.Username [Username], UsersGames.Level [Level], UsersGames.Cash [Cash], Characters.Name [Character] From Games right join GameTypes ON Games.GameTypeId = GameTypes.Id right join UsersGames ON Games.Id = UsersGames.GameID right join Users ON UsersGames.UserId = Users.Id right join Characters ON Characters.Id = UsersGames.CharacterId Order by Level desc, Username asc, game asc |
Problem 8:
Find all user in games with information about them. Display the game name, game type, username, level, cash and character name. Sort the result by level in descending order, then by username and game in alphabetical order. Submit for evaluation the result text comma separated with headers.
Solution:
This was a hard one. I even have decided to use alias for the db here 🙂 You have sum, count, group by, having count() and 4 joins. I was really happy to see it working 🙂
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
select u.Username, g.Name [Game], Count(i.Id)[Items Count], Sum(i.Price)[Items Price] from Users u join UsersGames ug on u.id = ug.UserId join Games g on ug.GameId = g.Id join UserGameItems ugi on ugi.UserGameId = ug.Id join Items i on i.Id = ugi.ItemId group by u.username, g.Name having Count(i.Id) >= 10 order by [Items Count] desc,[Items Price] desc, Username asc |
Problem 10:
Find all items with statistics larger than average. Display only items that have Mind, Luck and Speed greater than average Items mind, luck and speed. Sort the results by item names in alphabetical order.
Solution:
Well, to be honest this has taken me more time than needed, due to a trick from the examinators – they have named their table Statistics, and whenever I was referencing to it, the SQL Server was returning an error, because this is a reserved word. The point is that I have not realized it from the beginning and I tought that the problem is somewhere with my joins. Thus, after some 20 minutes of wondering (the temperatures in Sofia were about 40 degrees that day and I was really slow) I found out what was wrong. What I did? I simply renamed the DB, as far as I was submitting the generated output and not the code. Concerning the avg calculation, I had a similar problem here. Using it I was able to solve it.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
use Diablo select i.Name, i.Price, i.MinLevel, s.Strength, s.Defence, s.Mind, s.Speed, s.Luck from Items i left join StatisticsA s on s.Id = i.StatisticId WHERE s.Mind > (select AVG(CAST(s.Mind as bigint)) from StatisticsA s) and s.Speed > (select AVG(CAST(s.Speed as bigint)) from StatisticsA s) and s.Luck > (select AVG(CAST(s.Luck as bigint)) from StatisticsA s) order by i.Name ASC |
Problem 10:
Find all items and information whether and what forbidden game types they have. Display item name, price, min level and forbidden game type. Display all items. Sort the results by game type in descending order, then by item name in ascending order.
Solution:
Here the trick was probably not to get too tired and to take a look at the DB to understand what is a forbidden game type. Then to make the joins correctly and to enjoy the full marks:
|
1 2 3 4 5 6 7 8 9 10 11 |
Select i.Name[Item], i.Price, i.MinLevel, gt.Name[Forbidden Game Type] from Items i left join GameTypeForbiddenItems gtfi on i.Id = gtfi.ItemId left join GameTypes gt on gtfi.GameTypeId = gt.Id order by gt.Name desc , i.Name asc |
Pretty much that is what half of the exam looked like. 🙂 Enjoy the code above 😀