SQL Beginner's Tutorial: SELECT Data to Use in the Game

Hey everyone!

I'm sorry the last post was so short. I was in a bit of a hurry because I wanted to write down a post before I had to leave for my flight to Utah. Yeah, I went to Utah. It was for company meetings, but while I was out there I had a bit of fun. You should read the Post about it. It was a fun meet-up and I look forward to similar, especially if I get to watch someone fan-girl over Crypto Kitties like I got to see!

Source

Anyway, this Post is not about that meet-up. We need to get you guys going with a Project in mind in order to have a goal-oriented focus. So, with games being such a big part of so many lives, I decided that would be a fun goal. I have some C# Beginner Tutorials going (C# For Beginners is the latest and has links to the previous posts as well) that I have started to focus on a game.

I won't go too deep into the game mechanics here and will let you just read about it in the above post URL. Essentially, though, it is Capture-the-Flag-in-Space with Teams. So now we must think of all of the data that we'll need to synchronize in order to keep track of the game tools. From here on out, I'm going to go through the components that we should need in the game (and this, by no means, will be fully fleshed-out database tables) as well as our first deep dive into the world of the SQL SELECT statement. This should give you a decent primer as to how you will use it in your Database Management.

---

Source

Covering My Assets

Ok, so there are a great many risks involved with accessing and using Databases... namely, with the wrong statements, you could quickly wipe out a lot of data. That could spell disaster. You don't want that and I don't want that! There are some things you can do to minimize the risk. As I'm not an expert, I am only going to deal with those items that I'm familiar with: Using the SELECT statement to find exact data before doing things; and using Transactions.

The SELECT statement will save you a lot of pain. I say this because what it allows you to do is pull specific data based on particular tables and filters that you put in place. What you would do is execute the statement and verify the desired results before executing an update/delete (those statements that actually change the data).

Using Transactions is a good habit to begin from the get-go, especially as it relates to Microsoft SQL Server connections. A transaction is, essentially, a query that performs temporary adjustments. Once you execute a transaction it acts upon the data, but you have the option of executing a commit or a rollback on the transaction. Using the commit command finalizes the data and makes the change permanent, while the rollback command conveniently performs an undo action on the transaction.

What's really nice is that once you execute the transaction and before you finalize or undo the results, it allows you to execute other queries against that modified data as if it were live in the system. Then you can verify that the desired results are accurate before deciding whether to commit or rollback. I try to always use transactions because it provides a big safety net for you. In terms of SQL commands, it's like the mock-up below:

BEGIN TRAN t1;
UPDATE [capture_the_flag].[dbo].[space_ship_addons] SET equipped = 'Y'
WHERE user = 'admin' AND ship_id = '12345' AND weapon_id = '321';
--COMMIT TRAN t1;
--ROLLBACK TRAN t1;

Source

Explanation:

The above transaction updates the space_ship_addons table in the capture_the_flag database by setting the equipped field to 'Y'; however, the filters tell us that the only place(s) to make this change is if the user field reads 'admin', the ship_id is '12345' and the weapon_id is '321'. Only when those conditions are met will the change take place... assuming that the ship_id and weapon_id fields are unique fields, this will probably only return a single column, if that.

You will notice that, afterwards are the commit and rollback actions, but there are 2 dashes before each line. The 2 dashes make the lines into comments that are not executed. So here we would have this data temporarily updated and awaiting the decision as to whether a commit or a rollback is needed. I would then use the query in the SQL SELECT, I Choose You! section to verify the data.

For all transactions you need a BEGIN TRAN statement that identifies an arbitrarily-named transaction... in this case I named it 't1' but it could be named almost anything that is not a keyword. Transactions normally end with a semicolon.

---

Source

Game Mechanic Components

This section will be fairly short while being lengthy, because it will basically just be a list of items to consider when formulating the backbone of the game's data elements that are needed.

Tables:
space_ships
space_ship_addons
star_map
team_stats
player_info
weapons
materials
location_types
shields
communication_logs
error_logs
achievements
leaderboard

Fields

location_name
location_id
fire_power
score
user_id
name
space_ship_id
space_ship_color
weapon_name
star_class
defensive_strength
shield_power
power_level
team_name
team_flag_color
density
brightness
material_name
purchase_price
achievement_name
achievement_level
achievement_image
time_stamp
ally_response
enemy_response
error_code
error_message

---

Source

SQL SELECT, I Choose You!

*SELECT * FROM [capture_the_flag].[dbo].[space_ship_addons] *
WHERE user = 'admin' AND ship_id = '12345' AND weapon_id = '321'

The above uses the same filters (WHERE clause) and table as the transaction statement used earlier to identify what was changed. If it's what you want, then you are good to highlight the commit statement (excluding the hyphens) and executing it. If it's not, then highlight and execute the rollback statement (excluding the hyphens).

The format is generally as follows:

SELECT something
FROM somewhere
WHERE some condition happens
GROUP BY some fields
ORDER BY some field in a direction (ASC for ascending and DESC for descending)

This is the basic SELECT format. There are aggregate functions that can be used (COUNT, SUM, MAX, MIN, etc.) to perform actions against certain data as well as some other functions that can further strengthen a query. I will visit some as they come up within the game creation.

Then there's the JOIN statement, which will be set up in its own Post because it's a very important function that can unite data from different tables. It's not difficult to understand the concept, but actually using the JOIN is tricky at times. We'll also dive into Primary Keys, Foreign Keys, and I'll gloss over normalization of tables as a way to generate the strongest datasets available.

---

Source

Your Homework

From here on out you are on your own to consider this information. I'm charging you with some Homework, though. This won't be hard.

Exercise 1: Creatively consider the Tables and Fields in the above Game Mechanic Components section and try to map which Fields should belong to which Tables.

Exercise 2: Think about additional details that might not be populated and post as many good ideas as you can think of. I will give a Gold Star to the best response.

---

SQL-Forbeginners Posts:

sql-beginner-s-tutorial-writing-your-first-queries
sql-beginner-s-tutorial-relational-databases-and-sql

---

Posted on Utopian.io - Rewarding Open Source Contributors

---