Hello everybody, and welcome to another lecture on the structured query language. This lecture begins the next module in our course, the first module we talked about the sequel language, its standards, where did it come from. A little bit about its history and what happens when you run a sequel query, that got all that introductory stuff out of the way. And now, during this module of our course, we're going to start learning the sequel language and running queries, in order to do that. We need a database environment for you to use. I did not want to force students for this class, to download and install any software on your pc. So, I found a website that we can use called bit.io,bit.io allows us to have a practice sequel environment. And I will have very detailed instructions for you to get connected to bit.io, what does it do? It allows us to create databases, and within those databases create tables, fill them with data. And then I can create databases for you to use, I can make them publicly available, so that you can go in there and run queries against the database. That I've created at bit.io and then you'll be able to run sequel statements against it. One of the reasons why I chose to use bit.io is that, it uses a standard database called Postgre. And you can see the name of it is really Postgres, sequel, but most of us just call it Postgres. Postgres is great, it's very industry standard, it's very much like my sequel it's free and it's very capable. It's highly rated by people out there, who are seeking to use a low cost yet capable relational database engine. So, it gives us everything we need to learn sequel in this class. And, I like bit.io because the people that work there are very accessible to me. So, if I run into something that doesn't work the way I wish it did, I can talk to them about it and they can answer my questions. So, it's been a very handy tool for me to use to put this course together, they have excellent help available. So, if you have questions about any sequel statement, the syntax of the options, features of the language, click on bit.io. And it will take you to the Postgres system documentation, this slide shows you a glimpse of the bit.io interface. And what I'm going to do, instead of showing you this power point slide is just click over here, and take you to the bit.io interface. So, here I am running bit.io in my chrome browser on my Macbook, and I wanted to walk you through the components of this bit.io screen. Over here, in the upper left hand corner is the name of my database, which for bit.io they call that a repository or repo for short. So, I've created a repo and here's its name, it's got my name Alan Paradise/ NW and the NW. Stands for Northwinds, because that's the name of the fictitious company whose database we're going to use to learn sequel. In this left most column of my bit.io interface, it shows the tables that are in my repo and in this case I've created a database with eight tables. Suppliers, shippers, products, employees, customers, categories, orders and order details. I think these appear in the order that I created them. If I pick one table, I can then see all the columns in that table, just by clicking on it, those columns are repeated here. It gives me information about the table, the name of the table and here's some documentation about the table. It's got documentation for each column in the table, like supplier ID. The first column in the table, it's an integer and company name is a variable length character, text field etc. So, this gives me column information for every table in my repo, now over here on the right side of the screen. There are basically two windows, this bottom window which I'm going to clear out. This bottom window allows us to run queries and so, I'm copying and pasting, I'm going to copy over here. Some sequel and I'm going to paste it in here, and run it, so I've got a query here that says select star from. And then it's got my table name, after my repo name, so it's got repo name dot table name. These quotes are necessary, you have to fully qualify your table names when you're running them in bit.io. So anyway, I click run query and it goes out and it runs the query. It grabs the rows and columns from the employees table, and gives me the results here. So, this is my query results window and this is my sequel editor window. So, basically that's the stuff that you'll be using in bit.io, let's go back to our PowerPoint slides then. I'm going to give you some lab exercises to do and in those lab exercises, I'll give you thorough instructions for how to use bit.io. I'll show you how to navigate to bit.io, how to log in, how to connect to the lab repository that I've created for you. And then show you how to run your first queries. So, it will be thorough instructions in the document that describes your first lab exercise. As I mentioned, when you are referencing a table in bit.io. The table name in double quotes is coming after a dot, and you've got to have your repo name described there. So, in double quotes, your repository name dot table name and that's how you have to reference things in bit.io. I want to tell you just a little bit about this Northwinds database. It's important when you're learning sequel, you gotta understand the data that you're querying. And what the data means to the organization, that owns it etc. That knowledge of the business and the data, they used to do the business makes you a much better sequel developer. If you really understand the data that you're using. So, this is a sample relational database that comes from Microsoft. If you were to buy a copy of Microsoft Access, you get a copy of the Northwinds database free with it. So, I took that database and kind of rebuilt it, so that it would work in Postgres. And so what you're going to be seeing in our labs, is a Postgres version of Microsoft's Northwinds database. What do they do for their business? They buy food items, which they call products and they buy those products from suppliers. Then employees who work for Northwinds, sell products to customers through orders, customers place orders, orders contain detail items. Each line item on an order is actually a product with a quantity that they've purchased. There's eight tables in this database, and I've included a picture of a data model for the Northwinds database. I find it to be very handy to have a picture of a data model right in front of me, when I'm writing queries. So that, I can add a glance see table names, column names and even in this data model they've got the keys in bold. So, I can see this is the primary key, and I've got a foreign key here that refers back to this primary key. It shows the one to many relationships, one supplier can provide many products, a product falls into one category. A category can contain many products, customers purchase products through order, so a customer can place an order. That order may have many detailed lines, and each detail line refers back to the product with the quantity and a unit price of what did they buy, on that order. Employees are sales people, and they get credit and they get I think they get commissions and bonuses for all the stuff that their customers buy. So, an employee can be responsible for many orders, an order rolls up to one employee, and the orders are shipped by shippers. So, that is the Northwinds database. And it's good for you to have some understanding of what kind of business it is, and what do they do? So, given all of this, we are going to dive in, in our next lecture and start learning how to code sequel against this database. I think that's it for now, thank you very much
