From the course: Complete Guide to Advanced SQL Server

GameScore database final project setup - SQL Server Tutorial

From the course: Complete Guide to Advanced SQL Server

Start my 1-month free trial Buy for my team

GameScore database final project setup

“

Let's wrap up this course with a final project that will require you to leverage what you've learned across several of the topics that we've covered. I'd like you to create a database that'll house the player scores for a video game. This includes recording scoring events as players earn points, and maintaining a table of current player scores that account for all the points that have been awarded to each player. I'd also like you to keep a historical record of the total number of points earned in our game, so that we can see how the points awarded has grown over time. So we'll need to record a global score value with a timestamp in a separate table. Your task is to use triggers, custom functions, stored procedures, and other techniques that you've seen throughout this course to maintain the Game Score database. I've outlined specific steps that I'd like you to complete in each of the starting files that you can find in the course Exercise Files Section5 folder. Let's take a quick look. To get you started, I have provided a script that will create a couple of tables in support of the Game Scores database. You'll find it labeled as 1_ProjectSetup.sql in the Section 5 folder of the course exercise files. There's a few lines at the top of the script that will drop the database if you want to clear everything out and start over again. Then it creates a database and the game schema. Next, we're going to create a table to store the player records. This includes the player ID, player name, their current score, and a bonus multiplier that can enhance their score. Notice that the ID is an auto-generated with a identity specification, the score has a default starting value of zero, and the bonus multiplier has a default value of one. Next up, we have a table to store the score events. Any time any player is awarded points, that event is recorded in this table. This table has columns for the ID of the scoring event, the date and time the points were awarded, the player ID that earned the points, and the score that they earned. Finally, we insert two starting players, Alfa and Bravo, and then we take a look at the tables we have so far with these two SELECT statements. So you'll want to execute this entire script to get started. And then you can move on to the specific tasks that are noted at the top of each of the subsequent starting SQL files in the course exercise files. So take your time, have fun, and feel free to explore the problem space outside of the boundaries of the project, if you so desire. Over the next few videos, I'll go through my approach to each step in this project.

Contents