Create the Two Trees database

- [Instructor] For this course, we're going to continue working with data for a company called TwoTrees that manufactures a line of olive oil products. I want to make sure that everyone gets the same results as I do in 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 database that includes a number of different tables and rows of data so that we have something interesting to work with. I'm going to close the three tabs that are currently open in my Azure Data Studio window. Then I'm going to go and start with the SQL server instance I'll right click on it and choose new query. The first thing we need to do is create a database. I'm going to call this two trees with an underscore between the two words and then I'll follow that with underscore DBF three. This will make it clear that this database is the one that corresponds to the third part of the database foundation series. I'll press the run button and the database will get created on the server. I can then right click on the databases folder over here and choose refresh and I should see it right there. Now we need to add in all of the data. To do that, I've created a script inside of the exercise files for the course. Inside of the chapter zero folder is a file called two trees database dot TXT. I can open that up in my default text editor and then press control or command a to select everything and then control our command C to copy it to my clipboard. Then we're returning back into Azure Data Studio. I'm going to highlight the create database line and get rid of it and then paste in everything from that exercise file. Now, this is a really long script. If I adjust my screen a little bit, you can see that it's made up of over 300 different lines. It's going to create some tables and fill in a bunch of different rows of data. Let's make sure that we're targeting the new to trees database by using this dropdown menu here and choosing it from the dropdown list. And then I'll press the run button to execute all of those commands. When it's done, we should be able to go over here to the table folder on the left and expand it and see the different tables that were created. You might need to right click on it and she was refresh in order to see those tables. At the very bottom of the scripts we have some select demands. This will allow us to view the data in each of the tables that was created. So let's take a look inside of the inventory schema in the categories table. Here, we have three different categories of products that were added. These all have a unique category ID. In the inventory dot products table, we have all the different products that are in the database. They have that category ID, the references, the inventory that categories table. We also have a schema for sales and inside of there ,we have a table for our different customers and there they are. Each of those customers, places different orders. There are the order numbers and each order is made up of many lines and we can take a look at that data by running line 329. This will allow us to match the different skews of the products to the order lines that were placed by the different customers. So that's the database setup on our SQL server instance. Let's go ahead and close this tab down and I'm not going to save any changes to the script and we'll do the same thing for our PostgreSQL. First, I'll right click on the database servers name and choose new query. Then we'll create a database again. I'll press the run button that'll create the database and then I can right click on the databases folder and choose refresh to see it right there. Then once again, we'll go out to our exercise files and I'll copy everything to my clipboard. I'll go back into Azure Data Studio, paste all of that in and then switch my database context to the new two trees DBF three database. I'll press the run button and this time I'm actually getting some errors and that's because there's some slight differences in syntaxes that we need to pay attention to for PostgreSQL. So let me go back up to the very top of the script. Here, it tells me that for PostgreSQL I need to make some changes. We need to comment out lines 23, 25 and 27. If I scroll down to the script, we'll find those right here and these are the go commands that SQL server needs but PostgreSQL does not use. So I'm going to type in two hyphens here on line 23. We'll do the same thing for line 25 and line number 27. The other thing that I need to do is make a change to the way that identity values are defined. If I scroll back up here to the top, it tells me that I need to also modify line 63 and 76. If I scroll down to line number 63 and 76 we'll see them right here in sequel server, we use the syntax identity, 100 comma one, to automatically assign identity values for the different order IDs. And PostgreSQL uses a different syntax and I have that over here on the right. Instead of just identity in PostgreSQL we need generated always as identity, start with 100 increment by one. I'm going to highlight this and copy it to my clipboard. Then I'll scroll back here to the left and I'll replace this value here, identity 100 comma one with all of that text. This is the way that PostgreSQL needs it written out. We'll also do the same thing on line number 76. Here, I'll scroll down and instead of identity one comma one, we need generated always as identity, start with one, increment by one. So once again, I'm going to copy this bit of text and I'll paste it over the identity one comma one value. Now we should be able to run through the entire script. So make sure nothing is selected. In fact, I can go ahead and scroll back up here to the very top and click my mouse cursor here at the beginning of line number one. Then I'll press the run button and this time I get the expected results. All the different data tables were created and the values were filled in. If I come back over to the connection sidebar and right-click and choose refresh on the two trees database we can then expand the tables folder and there are all the different tables. Once again, if I go back to my script, scroll down to the very bottom, I can run through the different select statements. So there are my inventory categories. Here are my inventory products. Here are my sales dot customers values. The sales dot orders values and the sales dot order line values. So now we have a number of data tables to work with as we explore some of the different management objects and techniques that are available when building a relational database.