SQL Server – Solution of the first part of the SoftUni Exam for SQL Server

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.

sqlserver

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:diablo_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:

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:

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:

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:

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:

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:

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.

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 🙂

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.

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:

Pretty much that is what half of the exam looked like. 🙂 Enjoy the code above 😀

About

VBA Developer

Tagged with: , , , ,