How to work with the exercise files

- [Instructor] If you'd like to follow along on your own computer, then you're going to want to download the exercise files from the course overview page. Once it's unzipped, you'll find a folder for each chapter inside of this course. And then inside of each of these chapter folders, are the individual files that we'll work with. Now, throughout this database foundation series, we've been using a couple of database servers running inside of Docker containers, and we connected them with a program called Azure data studio. We're going to continue with this setup for this course. So I assume that you probably have a working environment that looks something like what I have here on the screen right now. In the connection sidebar over on the left side of the screen, I'm connected with both the RDBMS server for SQL server as well as our PostgreSQL database. Now, if you're already at this point, then fantastic. If you need to get everything set up though, you'll want to review the instructions in the server setup.txt file, that you'll find inside of the chapter zero folder of the exercise files. This file will describe all the software that you need and the commands to create and start these two servers. For a video walkthrough, check through the first course in the series database foundations, intro to databases. The movies in chapter two, will set up a database playground which we'll show you the entire process step by step. Now for this course, we're going to continue working with the data for a company called Two Trees that manufactures a line of olive oil products. I want to make sure that everyone gets the same results as I do when we're working with the data. And I don't want things to get mixed up with databases that you may have created in the prior courses. So let's take a moment and create a fresh copy of the database that we can work with. First, I'm going to right click on my SQL server database instance and say new query. Then we can create a new database. I'll call it Two Trees_DBF4. This will make it clear that this is the database that we're going to use, for this forest course in database foundation series. Feel free to name the database whatever you'd like though. I'll press the run button to create the database on the SQL server instance, and then we'll run the same command on our PostgreSQL server. Now that they're both created, we need to populate them with the tables and data that we have to work with. I'm going to go ahead and close both of these tabs and not save the changes. Then we'll go back out to the exercise files and I'll open up the file called Two_Trees.databas.txt. Let's copy everything out of it to our clipboard, and then we can close was the file. Then we can go into our database server. I'm going to right click on the SQL server instance and choose refresh, then we'll go into the databases folder and we'll find it right there. Now right click on it and choose new query. Then I'll paste everything in, and that pace in lines number one, down to 329. Let me just scroll here to the very top, so you can see what it starts with here at the top. I'll press the run button, and then we'll go through and create all the tables and them with data. So that takes care of the database for the SQL server instance. Let's go ahead and close this tab. We need to do the same thing for the PostgreSQL server. So once again, I'll right click on the PostgreSQL server or the databases folder and choose refresh. Then I'll right click on the Two_Trees.DBS4 database and choose new query and paste in all that text again. This time though we need to make a couple of changes to the script. So I'll scroll up here to the very top, and we'll see that I have outlines online three and four, for the things that we need to change. So I need to comment outlines number 23, 25 and 27. Let me scroll down and make those changes here just by typing in two dashes on line 23, 25 and 27. Then back at the very top, I also need to modify lines 63 and 76. Let's go down to those lines. And the thing that we need to change here is this identity statement. Inside of PostgreSQL it uses a slightly different syntax, and I have that over here on the far right. So I'm just going to copy generated always as identity, start with 100 increment by one, and I'll copy that to my clipboard, and then I'll come back to the beginning and replace identity 100,1 with all that text. We'll do the same thing down here on line number 76. So I'll copy this generated always statement. We'll come back here to the beginning and paste it over this identity statement. That'll make the script that we used on the SQL server database, compatible with PostgreSQL. Now, I'll make sure that nothing is selected and press the run button, and we should get all the tables and the data put into the database. So that finishes entering in the data. Let's go ahead and close this tab, so that's all we need to do to create the new databases for this course. If you want to, feel free to delete the prior databases from the older courses if you're no longer using them. To do that, right click on each server and choose new query and then issue the command drop database, and the name of the databases that you no longer need. In this case, I'll get rid of DBF three from the third course in the series. That'll remove it from the server, and if I refresh things you'll see that it's no longer there. Now, if you run into any errors with these commands, especially with the PostgreSQL server, you might need to stop the Docker container and then restart it before deleting the database. So now we have a fully fleshed out database that we can use to explore some relational database administration concepts.