Welcome to this overview and demo of structured query language or SQL. Structured query language Is the language used for managing data in a relational database management system or RDBMS. This database programming language allows for interacting with the database to perform operations such as SELECT, INSERT, UPDATE, DELETE, as well as many others. Some RDBMS implementations vary in support of SQL, and features can vary or differ among various vendors. In this demo, will explore several basic concepts, such as CREATE a DATABASE, CREATE TABLE in a database, QUERY a database table, INSERT records to a database table, UPDATE or modify records in a table, DELETE records from a table. There are many more features and functions that are supported, and many professionals have spent a career mastering the concepts and deep learning required here. Obviously many of the advanced concepts of SQL are beyond the scope of this course. Data definition language or DDL defines the structures of a database, the schema, the database, the tables and constraints. Examples would be CREATE or ALTER statements. And we'll see examples of these in just a moment. Data manipulation language or DML Is used to manipulate data. SELECT, INSERT, UPDATE, and DELETE are examples, and we will shortly see these in MySQL. Data control language or DCL includes commands like GRANT and REVOKE, and focuses on rights, permissions and other controls of the database system. Transaction control language or TCL deals with transactions within a database, COMMIT, ROLLBACK, SAVE POINT and SET TRANSACTION. Finally, joins and subqueries are mechanisms for combining data from different tables into a single result set. Subqueries can be used to return either a single value or a row set. A common use for a subquery may be to calculate the total of all products within our order or an average price of all of our products. Joins are used to return rows. A join will allow us to gather data from normalized database tables and return lots of information in a single row. In this demo, I'll be using MySQL Workbench on a Windows PC. I have a local instance of MySQL server running and I have connected to that server from within MySQL Workbench. Based on our simple design from the previous module, we will create a database called My Store, which will have five tables, customer, category, product, orders, and order details. We will now SQL programming to create the database, the table structures, populate the tables, and execute queries against our new database. You now see the MySQL Workbench environment where will create our database and use it to create tables, populate those tables and execute queries. On the right hand side, in this large white area is where we will type our commands or our programming. The bottom section is the output to give responses for those execution commands. And on the left hand side are all of the schemas or databases. This is a fresh install of MySQL server, so there are currently no user databases here. To get started, we'll use a simple command called CREATE DATABASE. It's not required to enter keywords in all capital letters in SQL programming. I do it for maintainability and readability. So we're going to create a new database called myStore. And then in MySQL Workbench, you can hit Control, Enter to execute a command where your cursor is. So you can see in the bottom here, we have a success on CREATE DATABASE. If I refresh this schemas panel, now you'll see a database here called mystore. It's completely empty. All it did was create the database. Next, we'll tell MySQL to use our new database, so USE myStore. Again, Control, Enter. Notice, we've got a success there as well. And now, the mystore database or schema is bold. Now we can begin creating our tables. I'll create the first one here, and then I'll copy and paste the remaining tables into our script. So we do that with CREATE TABLE, and then the table name. So let's start with Customer. And then open parentheses and enter a couple of times close parentheses and add a semicolon. So within the parentheses is where we'll define the structure of our table. So in the Customer table, we'll have an id and that will be an integer. And it will serve as the primary key for this table. As the primary key, it cannot be null. So, I put Not Null keywords. And then because I don't want to manually manage the ids here, I'm going to have the database, AUTO INCREMENT, which will allow MySQL to select the ids for each customer as they get inserted into the table. Then we'll need a name of a customer, and that'll be a variable character data type of a maximum of 255 characters. We'll have the customer's address, we'll use text as the data type. And then phone which, for now, we'll use text as well. Control, Enter, execute that. And now we have a new table in our database. If I refresh here one more time, you'll see now we have a customer table. Right click, Select Rows, will show us there's nothing in there. But the table does exist as we created with the script. I'll paste in the other CREATE TABLE statements here. What you will have is resources. Okay, so all of those tables have been created. Again, they're all empty at the moment. Our next step would be to populate data into these tables. So again, I will type one INSERT statement as an example and then I'll paste in the rest of the data. So to insert into a table, we'll say INSERT INTO, and then the table name. So we'll again start with customer. And I'm not going to provide ids, because in our table definition, we define that to auto increment. So we're just going to provide name, address and phone. And then what are the values that will be supplying there? We know we need name, address and phone. They're all strings, so I'm going to provide these values inside of single quotes. Our first customer will be John Doe, and his address will be 123 Main Street. And the phone number, Okay, Control, Enter, and I get a success on INSERT there to verify that. I can again return to the customer table, Select Rows. Now you see id number one with John Doe. So next, I'll paste in all of the other INSERT statements, Execute. And we get lots of data inserted into each of our tables now. Just a couple of things to note here on the CREATE tables. We do use foreign keys. So for instance on the Orders table, we have a foreign key defined on customerID, which is field in the Orders table. That particular field references the customer table, the id field within that customer table. So that creates the relationship between customer and orders. So we've used all of that information as we created all of these INSERT statements. So next, we can start to execute queries or retrieve information from our database. To do that, we use the keyword, SELECT. So in this case, we'll select * or select All from Customer. This will give us all the records that are in the customer table which our INSERT statements had two. So we still have John Doe who we hand typed, and then our script added Jane Smith. So one and two for the ids. I do not have to use the asterisk there to select all fields from a database table. I can, for instance, just retrieve the id, the name and the phone, and not retrieve the address. So I'll select the specific fields from Customers. And I can further filter down for just specific customers using a WHERE clause. So, WHERE name equals John Doe. Now I'll retrieve just that one record. Also with a WHERE clause, you can do pattern searching. So I could say WHERE name is LIKE. And I might know that I have a customer with the last name of Smith but I don't remember what their first name was. Percent sign will search patterns, so I can do %Smith and it would retrieve anyone with the last name Smith in our database table. In this case, we only have one and that's Jane Smith. Now if Jane Smith needed to update her phone number in our database, we could do UPDATE table name. So that's customer, UPDATE customer. And we'll set the field that we want to update. So in this case, phone = some new value Like that. Now it's very important to have a WHERE clause here, otherwise we would update all records in this database table with that same phone number which is not typically what you want. So make sure you have a WHERE clause here. Now doing that and going back up here to search for Jane Smith, now you can see her phone number has been updated in our table. That's a simple update. Couple more examples here. Let's select everything from orders WHERE customer ID = 1. So you can see, remember the relationship we talked about, that customer ID is referencing the customer table ID number one. So that would be John Doe, and he has placed three orders with us. We can dive in a little bit deeper here, so we can say SELECT, and I'm going to use an alias here, which I'll describe c.name. So I'm going to get the name, field or column from the customer table. I'm going to use an aggregating function here called SUM. And from the Orders table, I will retrieve total. And I'll name that aggregating function result as grandTotal. So again, to use that alias, I need to define it here. So FROM customer c, now we're going to introduce our inner join on orders 0. So that's where those two aliases come from. Now, those two tables have to be joined by something. That's where your foreign keys come in. So I'm going to join these two tables on the id field in customer table, and the CustomerID field in the orders table. We'll add a WHERE clause here to filter, WHERE customer id = 1. So let's just look at John Doe. And then when I'm using an aggregating function like SUM, and there are several others that we'll see here, I also need a GROUP BY clause. And you're going to group by all fields that are not an aggregating function in your SELECT statement. So here, we'll group by c.name. And now we can see that John Doe has spent $9,271 and some change with us. So there's an inner join. We pulled information from two different tables to return a single row set. Now let's look at all of those orders briefly, SELECT * FROM orders. Notice we also have one order from Jane Smith here, CustomerID number two. Also, one of our orders from John Doe appears to be an error. All of these orders are within a couple $100 of each other but this one, $8,765. So let's assume that is an erroneous order, should have never been placed. So we're going to clean up our database a little bit. So orders also have order details. So we're going to DELETE from orderDetails WHERE the orderID = 2. The orderID here, 2 is the erroneous one that we want to remove. So let's clean up the order details. First, executing that, you'll see we deleted a row of data from order details. And now we can go, DELETE FROM orders WHERE id = 2. And that will clean that up. And then to double check that, let's go execute this query above that will return all orders. Now we only have three orders in here. The ID number two is no longer in this system. And if we go up just a little further and rerun the grand total from John Doe, now we'll see that total is only $506. All right, just a couple more examples here. Suppose we wanted to get a count of products that have been sold, so I would need to know the product name, and then I'll use another aggregating function called COUNT. And we'll get a count of product ids as numSold. This is just a column name that I'm representing as that aggregating function. So we're going to grab this information from product and we'll alias that p. And we'll do another inner join like we did earlier, this time on order details, because we need to know how many of each of these items were sold. And we'll join those on the id column from the product table and the product id column from the order details table. Again, since we have an aggregating function, we need to group by all other fields in the select that are not an aggregating function. So in this case, GROUP BY p.name. And I also want to add one more statement here, ORDER BY p.name. And I'll show you that in just a moment. Executing that shows me that we have sold three reams of copy paper and one printer. If I did not include the GROUP BY here, it would appear that we have sold four printers because it's aggregating everything that it's finding in that table, returning one row. So it's really important that we include the GROUP BY, because we actually sold one printer. Order BY just returns in a specific order. So if I ORDER BY name, it's as if I would just clicked on this column and sorted. It defaults to ascending which is ASC. So that will produce no change. But you can also choose descending order, and that will put printer before copy paper. If you want ascending, you do not have to include it, that's the default. Lastly, if we wanted to get average price of products. So let's SELECT id, name, and price. And now I'm going to show you a subquery. So if I had a query that was an aggregating function of average, I was getting the average price of all products, Right, so this alone is a query. And I can execute this simple SELECT query. And we can see the average price of all products combined, it's $146. So that is a query, that is going to return this number as another field, another column called average price in my outer SELECT here. So that's a subquery. Now back to the outer SELECT, we're going to select some information from the product table, WHERE price >, and I'm going to reuse this subquery. So I only want to retrieve products that are more expensive than the average product price. And we see, we only get the executive desk back, which is $680. The average price is $246. If I wanted the products that were below the average price, I would instead use a less than symbol here, and I would get printer and copy paper. So here you can see we're using that subquery in a couple of different locations. One, to retrieve that information and display it. One, to use that information to make a decision and filter data. So this was a simple and quick overview of SQL programming language. And thanks for joining.
