Ultimate SQL Course For Beginners | Avetis Ghukasyan | Skillshare

Playback Speed


  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x

Ultimate SQL Course For Beginners

teacher avatar Avetis Ghukasyan, Web Developer

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Lessons in This Class

43 Lessons (4h 41m)
    • 1. Thank You

      0:30
    • 2. What To Expect?

      1:45
    • 3. What Is SQL?

      4:15
    • 4. What Is SQL Server?

      4:10
    • 5. Download SQL Server 2017

      1:19
    • 6. Install SQL Server 2017

      2:56
    • 7. Download SQL Server Management Studio

      1:00
    • 8. Install SQL Server Management Studio

      0:49
    • 9. Connect to SQL Server

      0:42
    • 10. Simple Hierarchy

      6:51
    • 11. Why Relational?

      10:19
    • 12. Indexes

      10:35
    • 13. Data Definition Language Commands

      1:24
    • 14. How To Create?

      18:08
    • 15. How To Modify?

      5:32
    • 16. How To Truncate?

      1:46
    • 17. How To Drop?

      4:08
    • 18. Normalization

      9:57
    • 19. Download and Import Adventure Works Database

      2:16
    • 20. More On Index Creation

      7:57
    • 21. Data Manipulation Language Commands

      1:04
    • 22. Begin With Retrieval

      8:58
    • 23. Scalar Functions

      12:37
    • 24. Sorting

      3:53
    • 25. Column And Row Logic

      13:14
    • 26. Joins

      13:47
    • 27. Distinct Data

      3:08
    • 28. Aggregate Functions

      16:59
    • 29. Sub Queries

      18:35
    • 30. Set Operations

      9:01
    • 31. Manipulate Data

      5:32
    • 32. Data Control Language Commands

      2:36
    • 33. Grant And Revoke

      2:36
    • 34. Introduction To Section

      0:29
    • 35. Problem 1

      4:41
    • 36. Problem 2

      7:51
    • 37. Problem 3

      12:24
    • 38. Problem 4

      6:52
    • 39. Problem 5

      14:30
    • 40. Problem 6

      5:34
    • 41. Problem 7

      13:18
    • 42. Problem 8

      5:35
    • 43. Farewell

      1:13
  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels
  • Beg/Int level
  • Int/Adv level

Community Generated

The level is determined by a majority opinion of students who have reviewed this class. The teacher's recommendation is shown until at least 5 student responses are collected.

268

Students

--

Projects

About This Class

Microsoft SQL Server also known as SQL Server is a mature technology which can be used not just by developers but by marketers, sales teams, product managers and much more - it is a query language that allows you to ask questions from securely stored data.

Think of it this way - any business on this planet makes money with data. The fancy user interfaces, cool animations are nice but that's not what makes the money. Think about it... when you go to Facebook or Instagram do you care about the fancy animations and user interface or do you care to see your friends, what they are up to, check out events and be part of the goings on of that entire digital ecosystem?

Of course it is the second choice... the fancy user interfaces are cool and make the experience pleasant but in the end you care about your friends and what they are up to, you care about being part of the ecosystem...

Now your friends, followers, events, what everyone is up to is data and as I mentioned before that is what makes companies money.

Microsoft SQL Server (SQL Server) is one of the most mature technologies that allows companies to handle data, store it securely, access it quickly and much more.

Now if you know about Microsoft SQL Server (SQL Server) and you know how to create database objects, how to normalize tables, how to retrieve data, prepare reports, insert and delete data safely and you know about best practices then you are an amazingly valuable asset to the company.

If you have a business that you are trying to grow and have data which you are trying to handle then Microsoft SQL Server (SQL Server) is a great choice which is another reason for you to learn this skill.

Now guess what? All of those things we will be learning in this course and MUCH more!

Take my advice and learn this valuable skill, you will not regret it. After dedicating only a few days and really focusing you can acquire this valuable skill and set a new course for your life.

Meet Your Teacher

Teacher Profile Image

Avetis Ghukasyan

Web Developer

Teacher

I believe that with hard work, dedication and consistency anybody who sets their mind towards learning a particular skill can do so - even if that individual has no prior knowledge of it and feels like a complete beginner.

I believe that understanding concepts is much more important than memorizing them. That is the core idea I keep in mind while making the courses.

See full profile

Class Ratings

Expectations Met?
  • Exceeded!
    0%
  • Yes
    0%
  • Somewhat
    0%
  • Not really
    0%
Reviews Archive

In October 2018, we updated our review system to improve the way we collect feedback. Below are the reviews written before that update.

Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

Learn From Anywhere

Take classes on the go with the Skillshare app. Stream or download to watch on the plane, the subway, or wherever you learn best.

Transcripts

1. Thank You: Just wanted to quickly thank you for purchasing this course and wanted to welcome you inside. In the next video, we will learn about what to expect from this course. And then right after that, we will dive into definitions and diagrams to get familiar with the query language of SQL and relational database management systems. So enjoy the course and if you have any questions at any point, either check out the Q and a for existing answers or don't hesitate to leave your own question. I get back to my students very quickly. So you won't have to wait for too long at all. 2. What To Expect?: So what to expect from this course? Firstly, we will talk about common misconceptions and definitions that people usually have when it comes to SQL, query language and SQL Server. Then we will download and install SQL Server with SQL Server Management Studio and talk about what they are will then lightly explore how datastore with SQL servers so you see what is happening under the hood. After we'll learn about DDL commands, these commands are there to help us create, modify, and delete database objects. And after that, we will import Adventure Works 2017 database full of preexisting tables and data that we will use for our examples later on. Then we will learn about DSL commands. These commands are there to help us create, modify, and delete data within the database objects that we have created using DDL commands later will lightly go over dCL commands. These commands are there to help us take care of SQL server, administration and security. But since it will require its own course or even a set of courses, because this is a very complex topic. As mentioned, I will only go read lightly and show you the main commands and ideas. Lastly, we'll go through common business problems and solve them using T-SQL. These are basically missions that will be given to you and then you will have to solve them using T-SQL. Now you can approach it two ways. You can either watch the mission and then pause the video and go and solve it your own way and then play the video again and compare your solution to my solution. Or you can watch the whole thing and just take notes whichever way works for you. The bottom line is that this action is there for us to put all that we've learned into a more practical application. And once again, more business problems will be added over time. So always check back. 3. What Is SQL?: So what does SQL, before jumping into defining what SQL is, I would like to clarify that there is often a confusion between the query language of SQL and SQL databases. In this video, we're going to be talking about the query language of SQL. And then in the next video, we'll explore the SQL databases. Now the definition of SQL query language is, It's a standard computer language that lets users communicate with data stored in a relational database management system. Let's take a look at it from a high level. As you can see on the left side we have the user and the user writes a SQL query. In this case, it's very simple select statement that's grabbing information from the user table. And it happens to grab the first name and last name. Now we'll be going over select statements in a much more depth, but this is just kind of a sample of what you could write, write. So you write this and it can be anything could be a select statement, can be an insert statement, wasn't that we'll be going over all of that, but you write the statement that's the first part is a SQL query language part. And then what happens is that SQL gets processed by the relational database management system. In our case, the relational database management system is SQL Server, also known as Microsoft SQL Server. So once that SQL gets processed, it returns either result set or it returns errors back. If the query language is faulty or we miss something, then we will get back airs. Otherwise, if everything is proper and everything matches up, we'll get back a result set just like we got it here. And in our case it would be something like this. We would have the firstName, lastName columns, and then we will have the rows under each column. And it can be millions of rows. It can be however many rows there are in the table. But essentially, this is the main idea. We have our query language. This is the coding part. This is where we write code. And then we have the other side, which is the SQL Server engine side that's taking our code and it's making sense of what we're trying to ask it. And then it's going to be returning AS a result. Or it's going to tell us that our question that we're asking it via code makes no sense. And in that case is going to return us errors. So that's kinda the higher level view. Now, as we already talked about this briefly, generally there are three components of SQL query language. We have DDL, which stands for data definition language. And this allows, these commands allow us to create modified delete databases and database objects. Html stands for data manipulation language. This allows us to delete, update, add, and retrieve data within those database objects. And then we have dCL data control language that allows us to maintain proper security for the database. This is where the SQL server administration comes into place. Now if you've ever done research on SQL or if you've heard things about SQL and you haven't had much experience with it. You might have a question, why are there so many other SQL? And that's a very fair question. So to clarify this confusion, here's what happened. Many major software vendors like Oracle, Mike. 4. What Is SQL Server?: So now the question is, what is SQL Server? And as you have already guessed, it is the relational database management system. It has been manufactured by Microsoft. And in the previous video, just to summarize, we learned about the query language of SQL, which is just a standard computer language to interact with data stored in an RDBMS. In RDBMS is just an acronym for relational database management system. So in this course we're going to be focusing on SQL Server and the SQL query language associated with SQL Server is called transact SQL or SQL for short. Now why even use SQL Server or just relational database management systems in general? Why not just store everything in a text file or an Excel workbook? Now this is a very fair question, and the reason is that RDBMS doesn't just give us a place to store data, but it takes care of very important business and technology related problems. First is integrity, relating data to each other and introducing constraints. We can enforce integrity, whereas in an Excel workbook, we can store all kinds of data that are disjointed from one another. And there's no way for us to say which piece of data relates to which other data. There is no way for us to enforce integrity. So it makes it very difficult for us to keep our data clean, performs his next, regardless of how much our data grows, we have tools in place to manipulate and access our data quickly. In an Excel workbook or a text file, we can obviously have millions of rows, but if we do over time is going to be very difficult to deal with that one file. And if it grows even more, it might even freeze our computer as we're trying to open that giant file. But with SQL server, we can have millions or even billions of rows and we can still access our data very quickly. Reliability is next, we have tools and techniques in place to reliably access our data. And security gives us the ability to grant and revoke access and have user hierarchy that lets our data stay secure with an Excel workbook, if you have a multi-user company that need to use the data and some users, you know, should not be able to access a certain part of the data is going to be very difficult for you to hide that data from them. But with SQL Server, you can grant and revoke access to certain users. And this is where SQL Server Administration comes into play. Sql Server promises to deliver above-mentioned solutions and it delivers perfectly. It also has been around for 20 years. So it's been tested and proven and it integrates flawlessly with many other Microsoft products, making it a great choice for an RDBMS. These are the benefits and the reason why we are going to be focusing on SQL Server. And one thing to know is that SQL Server is not just one program, such as Visual Studio or Microsoft Word is an ecosystem. So you have at its core, you have the SQL Server engine. And then once you install the SQL Server engine, many add-on software that you can choose or ignore as you wish doing your installation. The add-on software, either services or applications. And one of the most common add-ons, which is what we're going to use in this course is the SQL Server Management Studio, which is just the GUI administration tool for working against SQL Server engine. It just makes it more convenient to deal with SQL Server engine because it gives you a user-interface. And that's basically what SQL Server Management Studio is right here. This is just a quick diagram to show you the entire ecosystem of SQL Server. So at, at its core you have SQL Server engine and then around it you have applications or services. You can see reporting service, the integration service, SQL Server Profiler, Configuration Manager, SQL Server Management Studio. So this is just the higher-level look of what you can choose to install or ignore doing the installation. But in our case, we're going to just install the SQL Server engine and we're going to install SQL Server Management Studio. After seeing this diagram shows you the power of how much more you can do with SQL Server. 5. Download SQL Server 2017: So in this video, we're going to learn how to download Microsoft SQL server 2017. In order to do that, go to Google and type in. So we see here SQL server downloads, click on that. And as you can see, right here we have the Developer Edition. This says SQL Server 2017 developer is a full featured free edition licensed to use as a development and test database in a non-production environment, does it exactly our case. So go ahead and click Download. Now, at this point, this is SQL Server 2017. But in the future obviously it will be upgraded, so don't be rattled if the website changes, I'll be upgrading the course. If I see there is a need and there is a drastic change. But for example, if it's 22-23 and the course works perfectly with it, then I won't be upgrading it since there is no need. You can download and install 22-23, even though I have 2017 into video. If I see there is a breaking change our Then I will note it in my notes and I will make a new video showing you how to download the newest version that works. At this point, we have downloaded SQL Server 2017 and we're ready to install it. And it's exactly what we're going to be doing in the next video. 6. Install SQL Server 2017: At this point we have the file, download it, and we can go ahead and install SQL Server 2017. So all we have to do is go to our downloads or whatever your download location is. You go and you just double-click on the file and you open it up. And then you will see a window like this that gives you a few options, basic custom and download media. Click on custom. And then after that, make sure you have enough space to download and install it. It's going to download extra files for the installation. So you're going to need some time for that and it's going to vary upon your Internet connection speed. So now click install, and after that you're going to see a window like this. Click on the installation label, and then click on the new SQL Server stand-alone installation or add features to an existing installation. So click on that. And now we are going to specify the free edition, which is developer. We don't need to pay for anything, so there's a free version. And all we need to do is just leave it like this and click Next. Licensing terms. Accept the license terms. Click next. Just give it a few more minutes. The only problem at found was the windows firewall and that is not an issue. So we can just click next. The feature selection is basically what else we would like to install as part of SQL Server, as you've seen on the diagram before. And in this case, all we want is the SQL Server engine. So we're going to say database engine services and then click Next. Then we have two choices. A default instance and named instance where we'll be talking about instances in our next lectures. But for now, just keep it as a default instance and click Next. Once again, leave everything as their default settings, and then click Next. Here, we're going to choose Windows authentication mode instead of mixed-mode. Windows authentication mode is recommended for security purposes. So that's exactly what we're going to do. And then you are going to come here and say add current user. So I click on that. It's going to take a few seconds and then add your username. So you're going to use your Windows username to log into SQL Server. And then we're going to say next. Now this is just giving us the list of things that are going to be installed. So we can go ahead and verify and then click Install. After that we're all set. So all we now need to do is just sit back and wait until the installation is complete. So pause the video, but right now you can see that SQL Server 2017 installation has been successful and now we're ready to go ahead and download and install the SQL Server Management Studio. You can go ahead and click Close, but leave this window open because we're going to use that in our next video. 7. Download SQL Server Management Studio: All right, so remember how we had this window open. So now just go ahead and click on the install SQL server management tools. When you click on that, new window will open up. And here we're going to be able to actually download the SQL Server Management Studio installation files so we can install them on the next video. Once again, the current version is 18.3.1, but in the future it will obviously be bumped up as it improved. If there are any breaking changes, I will be updating them or you will see an article about it. Otherwise, using an older or a new version should not make a difference. So go ahead and click on SQL, SQL Server Management Studio 18.3.1. So it is now download it. And in the next video, we're gonna go ahead and install the SQL Server Management Studio 18.3.1. And then we're going to use that to connect to SQL Server 2017 incidence. 8. Install SQL Server Management Studio: So now go ahead and open up the installation file that you have downloaded from the previous video and wait for it until it opens up. So this is the installation process for Microsoft SQL Server Management Studio. Makes sure that you see the location properly entered into location input box. And if you want to change it, you can do so with leaving it as default is what we're going to do. And then click Install. Once you do that, it's actually going to go through the entire process and you don't have to go through any more steps. All you have to do now is wait until the overall progress finishes. Once the process finishes, you will see this window and this message. And after that, you should go ahead and restart your computer. Once the restart, we will then use Microsoft SQL Server Management Studio to connect to SQL Server 2017. 9. Connect to SQL Server: So now let's go ahead and access SQL Server Management Studio. Go to your start menu and type in SQL Server Management Studio. Wait for it to open up. And now you see the server name and the user that we created during our installation. All you need to do is click connect. And as you can see, it has been successful. So we just successfully connected to our SQL Server 2017 instance through a SQL Server Management Studio 18 1.3.1. And now we are ready to write queries and learn about all of the various commands. 10. Simple Hierarchy: So at the top level, you have a SQL Server instance. This is a copy or an instance of the database engine SQL Server dot EXE executable. So remember how in our installation process we had to choose a named or a default SQL Server instance. All this means is that we could go back and rerun that installation process, and this time we'll could have installed another instance. All that would do is create another version of SQL Server 2017. But this time we could allocate a separate amount of memory to it. We can give it it its own services. This time maybe we can install a reporting service or some other services or applications that are attached to this new instance with its own settings. And it would be separate from this current instance that we have. We're not gonna do that, but it's just good to know what a SQL Server instance is. Now then within each instance you can have multiple databases, and each database can have multiple schemas. And what a schema is, is basically a collection of container of database objects. It is associated with a username which is called the schema owner. The schema owner is the owner of all the database objects within the schema. So let's say you are a user named Mike and you own a schema. That means you are the schema owner, the username Mike is the schema owner. And then if that schema has, let's say a 100 tables and 200 views and other database objects, then you are the schema owner, meaning you also all, all of the database objects within that schema. That's all that means is just the container that helps us organize our structures within the database. Now the default schema for a newly created database object is DIYbio. It just happens to be DIYbio as something that's predefined and SQL Server and that's just how it's called DIYbio, which is owned by the DIYbio user account that is also a predefined and created already as you install SQL Server. Now there are other predefined schemas, but we won't be talking about them in this course since that is moving into SQL Server Administration. But we will learn how to create our own schemas and then put objects, the database objects within those schemas. Now, each schema can contain views, stored procedures, tables, and more other database objects. In this course, specifically, we're going to focus and hone in on tables. That's what we're going to focus on. That is one of the database objects that can be inside of a schema. Now suck a little bit more about tables. Each table is made up of rows and columns. So if you think of a table as a grid, the CMS go from left to right across the grid and each entry of data is listed down as a row. So if you look at this diagram here, the columns go from left to right. So calm, walk onto counter. And each entry of data, these are all the data entries are the rows. So each column when defined, is set to hold a specific type of data by specifying the datatype. So data types are simply a way of defining the type of data that the calm can contain. And this is just another way for us to enforce. Because we can trust that this column contains one type of data, that this other column contains another type of data. And then we can be more confident about what kind of data we're retrieving from that column. And there are generally three important kinds of datatype, numeric character, or date, or datetime. Here we have some characters or character inputs, we have some numeric inputs, and we have some date inputs in our rows. Another important attribute of a column is whether that column can contain null values, which means no data. And an important lesson to know is that an empty space is not the same as a null. Empty space is seen as a piece of data and null is absence of any data. Here we see we have a null cell within this row, and we also have an empty cell within the same row. Now this empty cell is still count as a piece of data. It just happens to be an empty cell. That's all. It's just empty string, an empty character, it has no value, but it is still seen as a piece of data when we specifically assign null to a cell, what we're essentially saying is that it is an absence of any data. It does not have any data. So as you've learned above, columns describe your data, right? So column one says that we are going to have some character data com two says that we're going to have some numeric data and CA3 says that we're going to have some date data and we're going to later on learn how to actually encode, specify that stuff. But this is just an example of what it would be like. And so once again, cams describe your data and the rows are the actual substance. So you say I need some character data, but what is that data? That is, right, so you have row one, you have wrote to your BrO3, you have reform. That is the substance of your table. If you have a 100 rows once again, that just means you have a 100 records. So in this case we have four rows, that means we have four records. And the last thing that we need to know about tables is the fact that table data is organized with an eight kilobyte pages. This is not something that you're going to have to deal with, but it's good to know how it works under the hood, how SQL Server organizes tables or table data rather. And in this case we can see we have data pages here. That's how it's organized within SQL Server. But you're not going to have to deal with that. You're going to see it as a table, but under the hood, it will be organized within data pages. And in this case, we have only four rows that would just fit in one data page. But if we have millions of rows, then in that case it would be separated into multiple data pages. Now let's take a look at the high level. So just a high-level view of what we just went over, whatever machine and that machine, we can have multiple SQL server instances. We can have two, we can have three, right? And then after that, within each SQL Server instance, we can have databases, we can have one or many, and we happen to have three in each instance, in this example. And then within each database you have, you can have a schema. Any schema contains database objects, and those database objects can be stored procedures views, tables. In this course, as mentioned, we are focusing on tables, but these tables then are organized within data pages and that's something that SQL Server is dealing with and you are not going to know bought it as you're writing SQL queries and you're interacting with SQL Server. But it's good to know once again what is happening under the hood. So that's it about simple hierarchy and structure. 11. Why Relational?: So now that we know the basic hierarchy and structure within SQL Server, a question you might have is why relational? Where does it come into play? And the answer is as follows. The values within each table are related to each other, and each table can be related to other tables as well. An example would be if we had two tables called a customers and orders customers table values are all about customer information and order table values are all about order information. Customers table contains one record for each customer who has ever ordered an orders table has one record for each order placed. So as we can see, our customer and orders tables are clearly two related tables. But how do we show this relationship? And the answer is by adding primary and foreign key constraints. That's how we create relationships between tables. So if we look at the customer table, we can see it has three columns and three rows in our Order table has three columns and four rows. And we have customer 123 and we have order ID 1234. So customer ID 1-2-3, an order ID wanted to before. We can also see that Customer ID repeats on the order table. So this is how you create a relationship between two tables, that customer ID and order ID, RD primary keys. And the Customer ID is the foreign key because it's from a foreign table. So in this case, what's happening is that we're saying customer id 11, so it's the same customer may to purchases. So a customer one was that Nate Smith, right. Nate Smith May to purchases order ID1 and order ID two for the amount of 10.70. So the total of $80, Nate Smith made a purchase for and then we have customer a2. So made one purchase, that's Nick Lopez and customer ID3 is Breanna Smith made only one purchase again. So we have seen that there is a relationship between these two tables and recreated that relationship by adding primary and foreign key constraints. Now, what are SQL constraints? Primary and foreign keys are types of constraints and not the only once we have available constraints are there to let us limit the type of data that can get into the table. They can be column level or table level if there is any violation between the constraint and the data being inserted into a table, that action will be aborted and nothing will be inserted. This once again helps with the integrity and reliability of the data being stored. So before we go over how this helps us with the integrity and reliability, let me go over the rest of the constraints that we have available in SQL Server. We have things such as not null, unique default and check very quickly. Not null just says that by default, SQL Server allows you to put in null values into the columns. And if you enforce a not null constraint, then it will say you cannot put a null value here. So that's all it is. A unique, just, once again, ensures uniqueness for the column just like primary key, but you can have many unique constraints per table and only one primary key. And we will go over why you cannot only one primary key per table in our indexes lecture. Then the default constraint, that's very simple. Default just basically says, if you are not giving me any value, I'm going to use a default value. And example would be. Order date, right? And if you don't provide any order date, then we can say by default just put in the current date. So if application, whether it's a mobile application, a web application, inserting a value in to the table. And we are not specifying an order date for whatever reason, we can just use the default value that is the current date or anything else that we provided. But in our example it was the current date. And then we have the check constraint. The check constraint is once again, very simple. It's just as limits the value range that can be placed in a column. So in this case, an example would be, let's say a column called age. And we have a check constraint that says nothing. Nobody that is below the age of 21 can get in to this table, right? So it looks for values that are 21 or above soil. If you insert something that's says 19, it will be a violation of that constraints so that row will not be inserted into the table. So these are the constraints. And now let's look at why the constraints help us with the integrity and the reliability of the data being stored. So once again, back to the primary and foreign keys, customer ID in order ID in these two tables and the previous tables that we've seen are commonly referred to as primary keys. And they have two main functions. First of all, they guarantee that the column will have a unique value for every row in a table. So we have 123 here, we have 1234 here. So it's uniquely identifying each row. And it is often the case that database tables are designed to generate sequential numbers automatically for the primary key column as new rows are added, this is called an auto-increment feature. All that is is that whenever we're creating a table and we're on, we're specifying columns for that table, there is a feature called auto increment that we can add to the column, which then will automatically generate a new IDE each time a row is inserted so that we don't have to do it ourselves. We don't have to go and figure out what the next unique ID will be. And this is just very helpful because we can just sit back and insert values into the table and the auto increment will take care of the next unique value as secondly, they allow you to relate one table to another easily as you've already seen, if you look at the customer ID column, you will notice that besides simply pointing to the customers table, it also appears in the Orders table. We've seen that before. And just to summarize the idea, again, this is called a foreign key. So this customer ID is a foreign key and this is how you relate tables to one another. In other words, in our customers and orders tables, the meaning behind our current primary and foreign keys is that we only want to orders to exist from existing customers in other way. If we entered another row here, let's say order five. And for customer ID would put five, that would violate our primary and foreign key constraint because we don't have customer five. So that would be aborted because it violates the constraint. And that helps us with the integrity of data because we can trust that whatever is in our Order table, whichever customer that is. That exists in the order table, also exist in the customer table. So relating tables to each other was again In this way enforces that data integrity that we talked about, since we cannot end up having disjointed data. Now speaking of relationships, there are three kinds of relationships in SQL server or in general relational database design. We have one-to-one, one-to-many, many-to-one, many-to-many. The first one is one to one relationship. This is not the most common type of relationship. It stays that row in a table a can only have one matching row in table B. An example for this would be, let's say table a would be our employee and table B would be our payment. And each employee will have their unique hourly pay, right? I mean, you can't have two Arly pays. You only have one hourly pay as an employee within a company. So we could also put them all in one table. We definitely could because one hourly pay can't repeat for multiple employees. So each employee has their own pay, but for certain security or legal reasons, we might have to keep them separate. So if that's the case, then we will have a one-to-one relationship because we can only have one payment per one employee. And in this case you can see payment ideas 1-2-3 employee id is 123, and that directly matches with the payment ID on the second table. So this is our one to one relationship and it's not the most common one. Now let's talk about the one-to-many or many-to-one relationship. So this is the most commonly used relationship. A row in table a can have many matching rows in table B is what it states. An example would be table a, being a table full of cities. And table BY being a table full of customers, one city can be associated with many customers, but each customer can only be from one city. So you can see here that we have city ID 1-2-3 and customer ID 1-2-3. But Customer ID 12 associated with city ID 11. So there are from the same city, but each city is associated with a new customer. But each customer can be associated with the same city. And that's where the one to many or many to one relationship comes into play. The last one is many-to-many relationship. It stays that a row in table a can have multiple matches in table B and vice versa, there's relationship gets created via an intermediary table or a junction table. The junction table just has the two primary keys from table a and table B, which is how it creates the many-to-many relationship. Example would be table AB and customers table BPM products and junction table being customer product, which connects the two tables via primary keys. It would mean that one customer can have many products and one product can have many customers. Let's take a look at the example. So in this example we have customers, we have three rows, 123 and we have three products, 123. Now, NR customer product, we have four rows. We have customer product ID 1234. But you see that Customer ID one, which is Nate Smith, can have two different products. So we're just basically it's another way of saying Nate Smith has purchased product 12, which is a cell phone and a chair. And we can also see that Product ID 33 is associated to deaf to different customers. So the same product can go to two different customers. So product at E3 is cameras. So a camera is owned by Nick Lopez and Brenda Smith, but Nate Smith owns cell phone and chair. So this is a many-to-many relationship. 12. Indexes: So remember when we were talking about constraints, these were rules that would allow rejects certain data getting into a table. Primary keys were one of the constraints. This is the constraint that allows one to uniquely identify rows in a table. An important fact about primary keys is that moment they are added to the tables, column or columns, they create an index. Do you also remember that old table data is organized within eight kilobyte pages. So when a table doesn't have an index, those eight kilobyte pages are stored in an unordered structure called a heap. There is an order there, just put there in no order whatsoever. We introduce an index. We stored the data pages in an ordered manner, which speeds up our retrieval. We will see this later in a diagram. So there are two types of indexes with clustered and non clustered. Let's talk about the cluster 1 first. So we'll cluster index orders the physical structure of the table itself, which is why we can only have one cluster index per table. Wasn't table has a clustered index. It is called a clustered table since it is no longer a heap, AKA stored in an unordered manner, a non clustered index creates the order and a separate structure from the data pages itself. So it doesn't change the structure of the table, instead, it points to it. And once again, we'll see this in a diagram, but for now, just get the definitions down and then elect a clustered index. You can have many non cluster indexes. That's because the structure gets stored in a separate place. And it doesn't modify the structure of the table itself. And that's the reason why you can have many non clustered index isn't one clustered index. And then the last thing is that cluster index gets created by default as you already learned when adding a primary key constraint. But it is possible to manually set it that a non cluster index gets created instead. But we won't go over that because it's not such a common scenario. So just know that whenever you create a primary key index, a clustered index gets created automatically for it. Now let's take a look at an example. So remember how I told you that when there is no clustered on non clustered index, then our data gets stored in an unordered manner, which is called a heap. So this is a heap table, and these are all of the eight kilobyte pages, right? And in this case we have Million Rows about employee data and we don't really need to know what the employee data looks like. You can imagine it has first name and last name, which in this case a, it has an employee ID, firstname, and lastname. And we have millions of them. As you can see, it's not really ordered because we have employee IDs that are not really anything is just the, just a column and it has value that goes from 101 to 2 thousand as one goes from one to 1000, this one goes to 200123 thousand and so on and so forth until it gets to a million. And as you can see, there's no sort order. So if we were to look for the employee, 3 thousand, we can't really rely on the order. There is no way for us to know which one is going to be employed, 3 thousand. So we're going to have to look throughout the entire table to figure out where that employee is. And when we're looking at a piece of data or pieces of data in a unordered table, which is the heap that is called a table skin. So a table scan happens when we start from the physical beginning of the table and go through every row in a table. If row matches the criteria, then we say, okay, but if not, then we keep going. So table scan is not bad when the table is small, but when it starts getting into high millions or billions is not a good idea to perform a table scan because it can slow it down. And if it increases even further than you're going to have a very, very slow query at a very slow table that you're dealing with. So now let's talk about what happens when we add a clustered index to the heap table. Firstly, it becomes a cluster table. Notice how all of the heap table data has now been organized from one to 10000100001 to 2000200123 thousand and so on and so forth simply means that the heap table structure got sorted and organized into a B tree data structure. The actual physical structure of our table has changed. You don't need to know much about B-trees other than the fact that it is a data structure in computer science which allows one to order data in a way which can be retrieved very quickly. Short story is that when developers are designing RDBMSs, they decided to use the idea of B-tree in computer science as part of their technology, which is what we use for quick retrieval. That's about it. Now let's talk about our example. We have 1 million records saved in a clustered index, level one and level two. So level one and level two are what is called index pages. So the root node and the intermediate level pages are called index pages. Indexed pages into root and intermediate levels contained the clustering key, aka employee ID in our case, and a pointer to the next level. So it points down to here, and these point down to the level 0. And this will continue until the tree reaches its leaf node level. Leaf node, which are these and level-0 leaf nodes and data pages in this case are interchangeable. They mean the same exact thing. The last level of the tree, which is the leaf node, once again, is where the data, actual data of the table is stored. But in this case, as mentioned before, the data is now organized, as you can see, from one to 10000100001 to two thousand and two thousand, one hundred three thousand. So as an example, if we wanted to look for employee id three, we would see that it's less than 500 thousand. So we would be in this index page and basically ignored the entire side, which has already saving us a lot of time. So we're here now, right? Then from there, SQL server would figure out that three is less than 10000 obviously. So it figures out that next it should go here. So it will go to the first leaf node or the data page of level 0, which is this guy. And there would find that employee ID3 is there. As you can see it in the diagram below, that is how the data page would be organized with stored employee IDs. This is just a part of the first page, but you can imagine it. It will have 100 rows. It's just that I wasn't going to make the entire table, so I just made a portion of it, but this is basically the partial representation of how this eight kilobyte page would be represented. So it find that this is employee ID three. And then it would retrieve this data. Now compared this to the previous table scan, which would have to go through the entire table. And this is way faster because we don't have to do that. And you can see we're just hopped on from this next page to that, and we had to ignore this entire side. And then from that we will hopped on to the next level 0 data page. And from there we just found what we're looking for. And this process is called index seek. Another thing is if we try to grab old table data from an index table or a search on a non-indexed column. Then we will perform what is known as an index scan. Idea is similar to table scan, but Table scan operation happens only on a heap table. The moment we add an index and want to look through all of the table data or once again, as mentioned, search on a non-indexed column. We're going to perform an index scan operation. Don't worry about this, since we will see more of this in code later in this section. Now let's talk about the non clustered index. So once again, it creates a B-tree structure, as you can see, but this time the index pages are stored in a separate structure as denoted by the yellow square. This is the reason why you can have multiple non cluster indexes but only one clustered index. Also note the fact that the heap table is still unordered. So it's from 101 to 2001210000. Just like before, it's unordered. But the non clustered index above, which is an, a separate structure that one is ordered leaf nodes, which before they were the actual data pages. Now they're not data pages any longer. What they do contain is they contain pointers to the actual heap table. So if you want it to look for the employee three, just like in our previous example, the same process would happen very similar to the previously described, except the fact that once you get to the leaf node level 0, you will then have to go from the pointer to the actual heap table and load the data from there. Instead of loading data from the level 0, which is what we were doing before. And that's the difference between clustered and non clustered indexes. And we will learn more about how to create clustered and non cluster indexes in the next lectures. So you will see them used in a more practical application if you don't fully understand how the B-tree works and how the specific calculations are performed. That is not a problem at all. The goal of this lecture was to show you how it all works under the hood on a high level so that you grasp the main concept of relational databases. If you would like to learn more about B-trees out will be released in a course that goes more in depth, but that is going to be targeted towards advanced users. For now, just understand these bags and you are ready to move on. If you don't have indexes, your data gets stored in a heap table. And it is fast when it comes to inserting data, but over time it becomes difficult to retrieve data. And that is because when you're trying to look for some specific, you have to go through all of the data in a table. When you introduce a cluster index, the physical structure of a table changes and it gets sorted, which speeds up retrieval but slows down insertion since the indexes to get reorganized each time when you introduce it'll non clustered index the physical structure of the table stays the same, but the data gets organized and stored in a separate structure which points to the original heap table. So just understand these points and you're ready to move on. 13. Data Definition Language Commands: Now it's time for us to talk about DDL commands. Ddr stands for data definition language. This part of the SQL language is used to create, modify, and delete database objects. The main commands are create, dropped, truncate, and alter. Create lets you create a new database altogether or objects within that database. Drop lets you delete an entire database or just objects within the database. Truncate, deletes all records from a table irreversibly and resets table identity to initial value. Table identity is that auto-increment feature. Remember, when we were trying to add new rows and have a uniquely generated IID created for us. That was the autoincrement features, so it would be one, and then if we added another row, it will be two and then another one will be three and so on and so forth. That was the autoincrement feature on the table. So if you don't use truncate when deleting all your records from a table, then it will not reset the table identity. But if you use truncate, then it will reset the identity and it will start from the initial value that it's set to alter allows you to modify database options, database object properties. In the next lecture, we're going to see how these commands are used to create databases or objects within those databases. 14. How To Create?: Okay, so now it's time to learn how to create databases and database objects within those databases. Firstly, we're going to go over the slides real quickly just to go over the commands and what commands me to be used and more parameters need to be passed to those commands. And once we go over it real quickly, then we will go into SQL Server Management Studio and actually run those commands. And we'll see them work in real life. So in order to create a database, we use the create and database reserved keywords. And another thing to note is that in yellow I will be putting down all the keywords that SQL Server expects. And in white are the things that you can put as your own. So I chose to name my database as our database and you can choose to name a database one or anything else you want. So anything in white is a parameter and anything in yellow is a reserved keyword says a command that SQL Server understand. So if you didn't write it that way, it will return you back an error. So as you can see, you say create and then Database, and then after that, you put the name of the database. So this is how you create a database, pretty simple. Next is how to create a schema. Instead of saying database, then you use the word schema. So it's as simple as that. And then you put the name of the Schema. One thing to note is that as you create new database objects within the database, as already mentioned, if you don't specify a schema, then it's going to be put in DIYbio schema. In this case, we're creating a new schema so we don't have to worry about that. But what it will be assigned is a default schema owner. And as you remember, the default schema owner was DIYbio. So in this case, our schema will have its schema owner as DIYbio. But if we want to give it a custom schema owner, then we can't use the reserved keyword authorization and then put the user that we want to be the schema owner. Guest is just another predefined user that we have in SQL Server. So that's where we're putting down as an example. But you can essentially have any other username. And that username is going to be the schema owner of your schema. So just two ways of creating a schema. One is, by default is going to have DIYbio as a scheme honor, and another one where you are specifying it scheme owner. Let's move on. Next thing is we're going to look at the most common data types in SQL server. So we have N'T, datetime, decimal, float and bit. And this is the integer type and it is used to store whole numbers. Can be negative, positive, just can't have decimal places. Datetime, pretty simple, just uses to store the date and time. Decimal and float both allow for storing values with decimal points. Our decimal allows you to specify the number of decimal points to maintain and float does not. Bit stores a Boolean value and is often used as a flag or an indicator of some sort. For example, a yes or no field, active or inactive field. Next is the character values. So we have four types who have varchar car and varchar and n car. So they're pretty similar, but here the differences. So for varchar and car, boat types are used to store non unicode meaning no national characters, and that just means no other languages. So both types are used to sort of non Unicode character values and both allocate one byte for each character. So cartoony or varchar 20 means 20 bytes that total allocated and one byte per character. And the difference between current varchar is that car is of fixed size. So if you only insert four characters into the field and the total is 20 bytes, you will end up with 16 bytes of wasted memory. Varchar is variable length, so if the total is 20 bytes, but only for characters are inserted, then it will only use four bytes and not waste the rest. Embark her NCAR. Pretty similar as far as car being and NCAR being fixed and n varchar being variable length. But both types are used to store Unicode and non unicode characters. So you can store national characters are the languages. And the caveat is that both allocate two bytes instead of one byte per character. So as is mentioned, rest isn't properties that are pretty much the same as above. But the only difference is that they allocate two bytes instead of one byte per character. Now the reason why we learnt about the datatypes is so that now we can learn how to create tables. Because inside a tables we have to provide columns and columns need to have datatypes, as you've learned before. The way we do it is by saying create and then table. So this is a reserved keyword part. Then we can just say our table, but if we do that, then it's going to be placed in a DIYbio schema. But in this case we want to put our table in our own schema. So the way you do it is to provide the name of your schema. Then you say dot, and then you provide the name of your table, then you have parentheses, and then within that per each line you provide your columns. In this case, I have three columns and one of them is called our table, and other one is our table name and other one is our table value, the first one. So you say the column name, then you say the column data type. Then after that you put any constraints. In this case we have a not null constraint. So what this means is that our table ID column is of type integer, so we can put whole numbers negative, positive, and it cannot be null. Then the second one says it's varchar 50, so it's a variable length. You can put up to 50 characters, and it's character values that you can put in. And the third column is our table value, which is just an integer. And since it doesn't have a not null constraint, you can put null values here. Now, the second table, our table to once again is put aside our schema, a pretty similar to the one above, but the difference is that now it has a primary key constraint. The way you put a primary key constraint, which will guarantee each row to be unique is by providing the primary key, reserved keyword. So you say primary key, just like so. And then within the parentheses you put the column that you want to be the primary key. So in this case it's our table ID. So our table ID guarantees uniqueness for your table. Now remember how we said that if we don't have an auto-increment feature, then if we put in a value, let's say we put one here and we try to put another row that has one. That will not work because it will violate the constraint, which is great. But then we have to keep track of the IDs. And every time we insert, we have to figure out what is a unique ID that we need to generate in order to not violate the constraints. So in order to make our lives easier, we at identity. So that's a type for a column that is going to say, hey, every time you add a new row, I'm going to generate a new unique ID. So in this case, once again, we have the name of the table or name of the table column, and then we have the data type, and then we have the constraint which is not null. And then after that we're saying that it's an identity type called what that says is it's going to start with one and increment by one. That's what one comma one mean. If we had, for example, 1060, so ten comma 60, that would mean that it will start at ten and increment by 60. And this case it starts at one, increments by one. That's all that means. And once again, it has a primary key. And the primary key is our Table ID. Then that's a new table that we created that's going to be called our table three. Now the last one is very similar to the rest of them, but the difference is that it has a foreign key, so everything else is the same except we have one more column, it says our table three ID, and it also is a not null. And note that in here I say primary key is our Table ID. Pretty similar, pretty standard, just the way we have above. But then I have foreign key. And the foreign key says it's the art table three ID. So it says that our table three ID is a foreign key. And this column it says references are table 3s, right? Our table threes are table ID. So essentially what we're saying is that our table three id will be connected to our table ID. So the, our table three ideas of foreign key from our table three, that's all that means. And the way we specify that is by foreign key, reserved keyword. And then after that, we put which column we want to be the foreign key. And then we say references, once again is a reserved keyword. And then we say which table it's connected to, which is what we're doing here. We're saying our table three, our table threes are table ID column. So that's what our table four is all about. Then lastly, we're going to learn how to create a clustered index and a non cluster index. The way you do that is by using create cluster index reserved keywords, and then you put the name of your cluster index. Then you say on, which is also a reserved keyword, then you provide the name of your table. In this case it's our table, then you have parentheses and within that, you provide the name of the column. In this case it's our table ID. So create clustered index, our cluster index on our schema, our table, and then our table ID. In other words, we're saying we want to create a cluster index. Cold are clustered index on table, our table on the column, our table ID and non clustered index is very similar to Clustered index and guards to how it's being created. And all you do there is just use non cluster instead of clustered reserved keyword. And that's it. Now let's actually go ahead and do this in code. As you can see, some of my code is green and some of it is colorized. This green just means that it's commented out. And what commented out means is that SQL Server does not treat it as an actual command. It just sees it as some remarks, so it doesn't know how to run it. The moment we uncommented, it will bring it back and treated as a command if is an actual commands. So there are two ways in which you can comment things out. One is you can put two dashes and then write whatever you want. And if you have multi-line, then this is what you can do. And then you can actually put more lines in between. Here. In this case, I only care about one lie, so I just put one liners. And another thing is that you can do this automatically. So if you had this line and you wanted to comment it out, there's a shortcut is control k and c. And if you click that or press all those, then it'll comment it out. And then if you want to bring it back, you say control KU. So once again control Casey and then control KU. Control KU. So this is how we create a database. So I'm going to highlight it. And also before I even start, since I only had all the code pre-written, I want to show you how to even get to this page. And the way you do it is by clicking New Query right here. And then you can have a place where you can actually write commands. In this case, I already clicked it and I wrote the commands. But just imagine you clicking the New Query and then you just have to say create database or whatever else commands that you have. So database and then our database. And you can then run it. But in my case, I already have everything pre-written for the sake of keeping everything tightened, neat and time efficient so I can spend more time on explaining things. So I'm going to close this and we're gonna come back here. So once again, we have create and database and note how SQL Server just highlights that and not SQL Server, but the GUI tool. So the Microsoft SQL Server Management Highlights are create database telling us that it's actually a command and our database instance in black, that just means that it can be anything else. So that's the way it denotes it. In my presentation, I used different colors, but it's the same principle. So we're going to select it and what we're going to do is click Execute. And here it says commence completed successfully. So you can also select it and press F5. That would do the same thing in this case, we can do it because it says database. Our database already exists. So you have to choose differ name. We don't have to, we have already created a database. If you want to see your database, you can come here, right-click and refresh and you'll see your database. Next thing is, we are going to uncomment this out. And we're going to create schema. Our schema. Commands completed successfully, which is great. And remember this one is going to be assigned to the DIYbio and this one is going to be assigned to the guest. Now, a quick thing is you might ask, how do we know if they're assigned to the correct users? That's a very good question. The way you do it is by listing all the SQL schemas and their schema owners in SQL Server it. So there's a way in which you can do that. And I will provide you with the script. You don't really need to understand what the script does. Just look at the results of the script to understand and to see that we've actually achieve what we want it to do. So we assigned our schema to the B0 and our schema to two guessed. So this script is just there to show you the results of the connection between schema and scheme owners. But don't worry about it. If you don't understand what the script does, you don't have to describe that I'm talking about is this. And you can write it on your own end so that you can use it as well. So just select this, write it, and then select it and execute it. So once again here I have to understand what it does, just look at the results of it. And in this case, we're seeing all the schema names and skim owners as a connection between the two. And we see DIYbio dot our default one. And with the default schema owner DIYbio, we see the guest and most importantly, we see our schema, which is says our schema and the default is connected to DIYbio and our schema two is connected to guest. So the schema owner, guest owns our schema to just a way we have created it, right? So that's great, so far, so good. Once again, control Casey to comment it out. And let's keep moving forward. So the next this AS creating a table. So we're going to create our table in schema, our schema. And once again, you use create a table reserved keywords. These are the data types. So we have the varchar, we have int, and we are saying that it's a not null, so just select it and F5 commands complete a successfully, great. And we can do the same thing with the rest of the tables. So I haven't right here. And let's do this first. Table. T2 is created successfully as well. And parentheses all the way there. So I'm going to bring it back. And F5, that's our table three, successfully created as our table four. As you can see, our table four is connected to our table A3, right? Just the way we talked about it. So it's saying it's table threes are table ID is its foreign key. And now what we're going to do is we're actually going to add a clustered index to our table, which was this guy. Okay. So let's go ahead and do that. Control KU and their ego. Command completed successfully. Great. And let's create a non clustered in next. And see another thing is that we can't have two clustered indexes, but we can have a non clustered index on the same table. And you'll see that it'll go in successfully. And there we go. So as if seen, this still doesn't do much because all we have done really is create empty tables. But you have at least seen the structure and how it's done. So first of all, you can create database and then you can create schemas within those databases, just like we talked about in the simple hierarchy. Remember that diagram I showed you. So you have a database within that. You can have schemas, multiple schemas. Then within those schemas you can have database objects. So we're creating our database objects, we created our table one are not our table, our table than our table two, then our table three, and then our table four. And then we added indexes to those tables. This is just to kind of get you in there and get you comfortable writing SQL. That's all I wanted to do with this lecture. And as we move forward, we'll add more and more into it. So you will see how to actually select data, insert data, manipulate data. All of that is coming is just that for now, I want you to understand the structure and then use commands to create those structures. So right now we're in a pretty good place and we are going to be moving on to adding more commands to it. 15. How To Modify?: So now we're going to go over on how to alter a table. You can also alter databases and schemas, but those are not done as often. So we're going to only go over how to alter table. And now the thing to know is that when working at a company, you will very rarely have to create a new database or even alter. It is mentioned that database will usually be made already and you would need to create or altered tables within the database. One of the only times you would need to create a database as a developer is if you're working on a greenfield project or if you are designing a completely new sub-system within an existing system, either case doesn't happen as often as working on an existing application. So back to the altar table though, the alter table statement has multiple functions. You can add a calm Modified column or drop a column. So let's go over each scenario. Here. We're going to go over on how to add a new column. So you have the alternate table reserved keywords, then you put the name of the table, you put the schema. If you have a schema and the name of your table, then you use the.add reserved keyword. Then you put the name of the column that you want to add and it's type, in this case it's an integer. If you would like to add multiple columns, all you have to do is just put a comma and then put another name after it, which is your another column. And then the datatype, this k is an integer. It can be a varchar or any other tab that you would like. So that's how you add new columns. Next is how to edit an existing column so you can edit its type. Once again, you say alter table, you say are schema.rb table because that's what the table that we're referring to. It can be anything else. If you don't have a schema, can be DIYbio. So if you don't specify a schema, it's just going to be DIYbio physiology. No. And then after that, you say altar column. That's another reserved keyword, has to be in this order. And nothing is. It doesn't have to be in separate lines. It can just be in one line. I just happened to organize my code this way, but it doesn't have to be. So you say altar column, those reserved keywords and then new column that can be anything. In this case it's new columns, so the name of the column and then, well it's new column because that's what I am modifying. But what I'm trying to say is that new column is the new column that you're editing. So in the previous slide we created a new column, and here we're just altering it and we're altering its type. So new column was an integer, but now we're going to turn it into a varchar that will hold up to ten characters. So this is how you can edit the type of your column within a table. But then you can also dropped edge so you can remove, get rid of that column. So once again, we're talking about the same calmed a new column and you say the same thing, alter table reserved keywords, the name of your table, along with the schema if you have one, but then you say drop columns, so you use the reserved keywords drop column, and then you specify the name of your column. In this case it's new column. So that's how you add, modify, and delete. A couple of other things, is that you can alter certain properties of indexes, such as storage properties and a few others, but you cannot change the columns making up the index and just something good to know. He can alter a schema by moving the ownership to someone else. Once again, it doesn't happen very often, very rarely. But as mentioned, we will not be going over these since they're not as relevant to the main curriculum of discourse. Now, let's look at how this is done in code. So right here, I already have everything pre-written. So you see alter table or a schema or table. Once again, I can have it in one line, I can have it in two lines. I just happened to have it in two lines. So here we're going to add the new column. But before we do that, notice I have statement here. This is called the select statement, which will be going over in the next section as well. But all this does is it's going to retrieve the values from our table, is going to show our table. And the reason why I want to show our table is so there were not changing things blindly. We can at least see the end result. So right now if I was to run this, by the way, you can run it by selecting your statement and pressing F5. You don't have to always click execute. It can just do that. It's a shortcut. You do that and you see all the columns now all of our tables are empty, so we just see the column names are table ID, our table name or table value, right? And that's what we had before. But now if I was to add a new column, you see commands completed successfully. And I select it again. You can see new column, right? And if you want to add multiple columns, then this, you would do it. But I'm not going to run it because we've already done the adding. So instead, I'm going to try to edit the type, the datatype. So new column refers back to this, right? So all I would have to do is run this statement and that would change the type. So it ran successfully. We can't really see that. But if were to try to insert something that does not belong there, it would break since the type has been changed. So once again, it's going to say that new column is there, but it's not going to show the type of it. And then the last thing we learn is how to drop Get rid of that column. So in this case it's the new column that we're talking about, right? So all of these refer to the same thing. And what we're going to do is run this and that will drop the column. So if we run this select statement again, we will see our table ID or table name and our table value, but there is no new column. 16. How To Truncate?: Okay, so this one is going to be very short. I'm just going to talk about very quickly on how to truncate a table. So there are two ways of deleting data from a table. One is using the delete command, which we're going to go over in the next section. And the other way is to use truncate. So the way you do it is by saying truncate table. So you use the reserved keywords truncate and then table, and then you put the table name. And obviously if you have a schema, you put the name of the schema dot table name. So the difference between delete and truncate, it is the fact that delete is not going to reset the identity, but the truncate will. And by identity I mean the identity reserved keyword. Remember how we said identity 11. So it's going to start with one and then increment by one. So let's say you had three rows, right? It will be 123. And then if you truncated the table and then you kept inserting again, it would still generate 1-2-3. But if you deleted after 123 and then you insert it the fourth one, it would actually give it an ID of four. So I would not reset the identity. And that's really one of the main differences between truncate and delete. And we're going to click quickly see how to do it. Server management studio. Once again, we don't really have in our table, in our table called our table. But the way we do it is just like we saw in the slide. You would use truncate reserved keyword and then table reserved keyword. And then you would put the schema name and the table name, and then you would just run that. And it says command completed successfully, which it did because we truncate at the table. But obviously in this case there was nothing in there to truncate. But if we are data, it would all be removed and the identity would be reset. 17. How To Drop?: Now let's learn how to remove database objects. So in order to do that, we're going to be using the drop reserved keyword. First, we're going to learn how to drop indexes clustered and non clustered is going to vary very familiar from how to create indexes. It's going to be very similar to that. So what we say first is we say drop and then index, we use those reserved keywords. Then we put the name of our index. Then we say on which is a reserved keyword, and then we put the name of our table. We don't need to provide any columns. We just say that on this table we want to remove this index and that's all. We do that and run it. That index will be dropped. Know that we also don't specify that it's a cluster on non cluster index. We just say that that index with that name on that table needs to be dropped. And by dropped we mean removed. And the same thing goes for the non cluster one. Once again, in this case, the only difference is that we just put a different name, but we don't have any reserved keywords that helps us differentiate whether it's it's clustered index or non cluster index. So in this case, we're going to be dropping our non cluster index, the same format, dropping next name of the index on and then the name of the table along with the schema. If you have one, then this is how we drop a table. We just say drop and then we say table. So we use these tours of keywords, then we provide the name of the table, and that's all very simple. Now how to draw up a schema. This one is a little bit tricky. So you say drop schema. Once again, that person will just use a reserved keywords drop and schema and the name of the schema. But here's the thing. If a schema contains any objects, it will fail. So you need to make sure to remove all objects before removing it. So once we look at the code, you'll see that when I try to remove a schema and it has database objects, it will actually fail. Once I remove all the objects, then it will succeed. And we'll see that in just a little bit. And then finally, this is how you drop a database and entire database that we created from the very beginning, the named was our database that can be dropped by using drop database reserved keywords, and then we put the name of the database. Now let's actually take a look at how this is done in code. So here it is. I have the code written. So first we have drop-in next. So let's try to drop the clustered index when successfully. Then this is how you drop a non clustered index when successfully. Now let's try to drop our table. So this is just one table in the ER schema, schema when successfully. Now here is what's going to happen with the schema. C cannot drop schema because it is being referenced by object and it basically lists the name of the object. So we can drop the table two and then drop the schema again. Once again, it has some stuff in it we can drop table for and then try to drop the schema. Now it says Table three is still there it so one by one, I was trying to show you that it will still fail unless it's empty. So then we drop or table three and now there are no more database objects within the our schema, schema. Now if we say drop schema, so these commands are pretty much the same. I squared at the bottom. So this time it will work and I run it, see the schema has been dropped. And the lastly, when I say drop database, our database, this will drop the database. So here we have our database. I do that. And then I can go here and refresh. And you can see that our database has gone. So this is how you delete database objects and also an existing database. 18. Normalization: Well, let's talk about normalization. Normalization is a database design technique where we built an organized a table in such a way that it reduces redundancy and dependency of data. It divides the larger tables to smaller ones and uses the relational feature of RDBMS to link those tables together. There are four phases to the database normalization process. We're going to learn about the first three, since in most practical applications, normalization achieves its best results in the third phase. The fourth phase are first, second, third, normal forms. Those are the ones that we're gonna go over. And then there's also a voice code, Normal Form B, cnf, and other ones are being developed. Let's take a look at this table. We're going to start out with this table that is not normalized and everything is saved in the same table. We have some weird number along with a comma and the full name, the full name, and then two commas with a repeat. We have to book titles separated by comma, another book title separated by a comma. So this is some weird data in this table. Let's see what we can do with it. So first normal form, it has to follow these four conditions. Each cell of a table has only one value. So we can have something comma, something else like we had before. All the data in a column must mean the same thing. So, you know, if it's a first name, first name, but it's a customer types to customer type. It's a book title. It's a book title. We can have name, comma number, or we can have name here and then a number here and a customer type underneath it. It has to be the same thing. It must mean the same thing. Each row of a table, so each row must be unique. And then a table should not have any repeating groups. So we can have book title one, book title to book titled to your customer type one. We can't have stuff like that and columns like that are called repeating groups. So what did we do here? We remove 99, 97. So if you have stuff like that and you can make a decision to make a column for it and then add that data and add some default values for the rest of the rows. I just decided to remove it, so it's a decision that you need to make. I suffered a full name to firstName, lastName. That could be debatable because if your application looks at a full name as just one row, then that's fine. But I just try to keep it clean and I just separated it from full_name to first name and last name. Then we made the repeat and non repeat into a separate column, even though it could have been a Boolean value for the sake of simplicity, though we're keeping it as a varchar that either says repeat are non repeat. We now also have a customer ID column, which is an ID for each customer. It is not a primary key though since it's not unique, because you can see that 3344 are repeating even though Customer ID repeats at times like for 34 as I've shown, we can still identify the record uniquely by looking at the book title. So the customer ID and the book title or composite key, meaning we're using a combination of two or more columns to identify a row. This means that all of our rows are unique. Because look, even though 3344 repeat, if you combine that with the book title, then it's unique. So it's three would Selfish Gene. Three would par of now for, with astrophysics for people in her and four with the power of now. So it's unique. So this is not the most ideal, but it passes the first normal form requirements. And let's take a look at the second normal form. So let's actually. Try to make this a bit more complex and let's add another column called customer rating, which shows each customer's rating for the particular book to have rented. So we have 55 with four. We have three, right? So just customer ratings were just trying to make the table more complex so we can start to trim it down and try to create a more realistic example out of which we're going to normalize it into a second and then third normal form. So the problem with the first normal form is that there is a lot of redundancy. And if you change one record it has other ones which are related to it, you would need to make the same type of change, otherwise they will be wrong. And over time this can get very cumbersome. So we change a customer type for James, for example, in the previous table, would have to do it twice. If we had them, they're millions of times, we would have to perform the change millions of times. So if we actually go back, if we change the customer type here, we also have to change it here. And if we are millions of rows, we had to do it millions of times. This is known as functional dependency. And the current setup, you can see that the table information has been separated out into a new table book id primary key has been added to ensure uniqueness. It follows for as normal form. So we took that book out and put it in a new table. So the book table has book title and we have Book ID wanted to before, and each customer has 1234. So as mentioned once again, we have a customer information in a separate table as well. We're still follows first normal form and as less entities or entries rather, which then got rid of redundancy. And now the Customer ID is also a primary key because it's uniquely identifies each row. Customer rating is the rating information about the book by each customer. So there's new table right here. Both customer and book tables each contain information about one entity. So a customer is about a customer and a book is about a book. Customer doesn't contain any information about a book, and a book doesn't contain any information about a customer. And both customer and book tables each have their non-key fields directly related to the primary key and nothing else, meaning that non-key fields, the firstName, lastName and customer type, or in the book is the book title there directly relating to the primary key. So first, Heinz, the firstName, lastName, and the non-repeating all have to do with the customer ID. The book title has to do with the book id. And then the customer review has repeating customer IDs for 3344. But once again, does not a prom because in conjunction with the book id, they make up a composite key which is able to uniquely identify the row. And so in this case, also, the table is only about one thing that is about ratings. And the ratings are functionally dependent on the composite key. So the rating is related to its composite key. And the composite key is a customer ID together with a book ID, in this case, we have Customer ID to E3. But with a book idea becomes unique because we have 3332. So that makes it unique. So once again, what makes it into a second normal form, right? So first of all, it has to be already in first normal form. And then there's two other conditions when it says each non-key field must be functionally dependent on the primary key. All that means and fancy terms is that non-key fields are these guys that are not primary keys. And they have to be functionally dependent, meaning that they're about the key, that they refer to the key. That's what that means. And that's exactly how it is. Firstname is about Customer ID. This firstName, lastName is about customer ID, and this customer type is about Customer ID. Same thing with a book title. It's about the book Id and the rating is about this composite ID. So it passes this one as well. And table must contain data about one type of entity. Customer table is about a customer, book, table is about a book and customer ratings are about customer ratings. So now, third normal form, table is in third normal form. If Table is already in second normal form and the second which it is. And the next condition is there is no other non-key attribute that you would need to change in a table if you changed a non-key attribute, also known as transitive functional dependencies. Meaning that if you had, let's say, two other columns or let's say these cons, right? If I change a customer type, I don't also have to change any other non-key fields. So these are not dependent on this. So they're only dependent on the customer ID, but they're not dependent on each other. So the good news is that our current setup passes the third normal form as well, since we don't have any transitive functional dependencies on any of our tables. So let's try to add two more columns once again to the book table, just like we did before, just to make the example more complex so we can mess it up and then try to fix it by putting it in third normal form so that you can see how it works. So once again, let's try to add two more columns to the book table to see how a transitive functional dependency would look like. Right now, we added two columns. So we add a publisher idea and recent publisher. And both of these fields are functionally dependent on the primary key, which is great. However, they're also functionally dependent on each other, which means that if the book decides to go with a different publishers in the future, there we have to change the publisher ID and the recent publisher. And this is what is known as the transitive functional dependency, meaning that the publisher ID and the recent publisher are still about the book id. But they also depend on each other to publish your ideas about a recent publisher. And a recent publisher is about the publisher ID. So if we change the ID, we also have to change the recent publisher. And that's why they're related to each other. They are not only related to the book ID, and that is basically the transitive functional dependency. In order to solve that, what we do is we separate the publishing information into a publisher table so that if we ever needed to change, the publisher would only change the ID right here in the book Table, and that would then be associated with the new publisher name automatically instead of us having to update it as well. So if we wanted to, let's say there's another row, publisher ID five. And the, let's say the Selfish Gene wanted to go with the publisher ID one, no problem, we can just change the ID here and we automatically would get that name because it wouldn't be associated back to it, right, primary and foreign keys, as you remember from our previous lectures. And so that is table normalization. 19. Download and Import Adventure Works Database: So in this video we're going to download and import Adventure Works 2017 database. It's basically a test database that has premade data for us pre-made tables that we can just use for our examples as we're moving along. So what you need to do is go to Google, type in adventure works 2017 database. And then for the results, look for docs dot Microsoft.com and installing configure Adventure Works sample database. Click on that. Go and find the one that says Adventure Works 2017, mixture has 2017 because I'm gonna be using that. So my examples will be different and will have different results if you have a different version on yours, I mean, you can still test out the commands, but the results at the end will be different. So just make sure you're following along. So then just click on it, make sure it downloads. Right now I'm downloading it. And then what I do usually just to make it easier for myself, just go to the location where it is, download it, grab it. So once it finishes downloading, grab it, and then bring it to your C drive, just makes it more, just makes it easier to find a later. So then once it's there, go to Microsoft SQL Server Management Studio and right-click on the Databases, click restore database, click on device. And then dot, dot, dot, Choose File for the backup media type. Click on Add, and then just click on see, which as you can see, it's just simpler to find it. And then click on Adventure Works 2017, ba K, That's the backup file that you just download it. And that's it. And then say, okay, okay. Okay, one more time. And it says database Adventure Works 2017, restored successfully. So let's take a look. This is our new database, just like our database that we created this database, but it just has more tables in a lot more stuff. So we can see that it has just a bunch of tables. So it has person and customer addresses, right? So we can actually right-click, say select top 100 rows. And you can see that just has a lot of stuff in here. And we're going to be working with this data in our next section. 20. More On Index Creation: So in this video, I'm going to show you more about cluster and non cluster indexes. And the reason why I wanted to wait is because I wanted to go over all the syntax of how to create tables had a drop tables, had a create indexes cluster and that cluster a drop-down just so you're more familiar and you're less confused when you're looking at this code. And now combined with that also, you know from all the theory that we've learned about indexes, the B-tree is and everything else. All the practical application of the theory combined is this code basically, and this code will be available in GitHub just the way I'm going to have other codes available in GitHub as well so you can follow along and not feel lost and any point, so right now, I'm saying use Adventure Works 2017. That just means in this database is where all my operations are going to happen. And then I'm just creating a table. We've already went over this. I say create table reserved keywords. And then when the table is test customer I have four columns in that customer ID, first name, last name, account number. As you can see, I don't have any primary keys, so this is a heap table. Let's go ahead and create it. It will go. And then this statement right here, you see a lot of insert selects inner joins. So what I'm saying here is that this inserting some test data into the new test customer table. Don't worry about the syntax. We'll go over enter, inserts, insert, insert and join later. And insert joins and selects are commands that we'll be going over in the next lecture. But we needed them right now because we need some test data in our test customer right now, if you actually try to look into our test customer, will say that there's nothing. But after we run this, it'll add 19,119 rows in there so we can actually play around with it, do something. So now I run the select statement and you see that there's actually some data in there. So that's great. So now we can play around with it. So let's look at the next one. It says, even when looking for something specific in a heap table, which it is a heap table. We don't have any indexes. We're going to perform a table scan. So remember how when we didn't have any indexes or we're trying to look for something that table was unordered. So we had to perform a table scan. That's exactly what's going to happen. So when we run this, we expect the operation to be table scan. But how do we know about that? The way you know about that is by turning on a live query statistics. So come here, click on include life query statistics. And if you don't have that, it should be something along the lines of execution plans. So if there is any tab that you have, it may be a newer version. As long as it has says execution plan. Then if you click on that, it should show the execution plan off your query. And if you actually have to include Live Queries statistics, and just click on that. So let's run this and we expect that table scan. And as mentioned to table scan. So now let's add a cluster index. I'm gonna go ahead and do that. Really know about syntax. So I'm not gonna go over that. She's going to add that. And after the clustering next is added, we can perform an index seek. Remember it's a B-tree. So now we can actually look for some specific and not have to go to each and every, each and every row. So let's see if this is in fact going to be an index seek. And as you know, has expected clustered index seek. Great. Now let's drop the cluster index and add a non clustered index and test that. So we're going to drop are clustered in next and create a non clustered right? Non clustered index on test customer. Same thing on Customer ID. So after non cluster indexes added were still performing an index seek. Alright, so let's see if this is also going to be an index seek. Once again, index seek now clustered. So let's search on the account number of the test customer table now. And remember, the actual table is now a heap because remember the clustered next. When you add that it turns it into a cluster table. So change the physical structure of a table, right? Your entire table changes, but a cluster and non cluster index is saved in a separate structure about the table stays the heap tables still. So once again, I'm just saying and remember the actual table is now a heap non cluster index is saved in a separate structure. You will see that it performs a table skins is non cluster index is only set on the customer ID. So since it's a non cluster index, the index will only perform an index seek if we search on that column that has an index. In this case it's the customer ID, but the rest of the table is a heap. So if you actually run this, even though the customer ID has associated index, the whole table is a heap. So we expect this to be a table skin. And just like expected its a table scan. So now let's dropped a non cluster index and add a clustered index back on the customer ID, we're going to drop the non clustered index, right? And I'm going to add a clustering index back again. Now let's search on the account number again, right? So even though we added the customer ID index as the clustered index on the customer ID, we're searching on an unrelated, unrelated column. So let's search on that again and we'll see that it's an index scan this time. The reason is because the actual physical structure has changed. So it's not an index seek is not a table scan. It's an index skin, right? So it's searching on an ordered structured. So we're going to do that. And we can see it's an clustered indexes skin. Lastly, let's see what happens if we try to add another classroom next to the same table. It breaks, is you can only have one. I just want to demonstrate that it will break. So create clustered index. And this is just the account number, let's say we added on the account number, right? So you do that. And it'll break, cannot create more than one cluster index on the table test customer. So that's exactly what we expected. We can add another non cluster index as mentioned though. So create a non clustered index. Let's say I'll just add non clustered in front of it. Not that it matters, but it's just good to keep track of the index names. I like doing that. So create non clustered index, non clustered customer account number index right, on the test customer, on the account number. So this should work because it's just a non clustered index. This structure is going to be, it's going to be saved in a separate structure. That table is not changed. So that went just fine. So performing the same account numbers search again will perform an index seek. Now, since the column now has an associated index with an organized structure that is saved somewhere else. Right? So when it wasn't. So right here, when the table was just an clustered table, even though it performance index skin, it's still had to go through each and every page because, you know, the only index that had that it was associated with was the customer ID. But now, even on that clustered table, we have added another non clustered index to one of its other columns. That was the account number. So now this account number has its own associated none clustered index that's saved in a separate structure. So let's run the statement that looks for the specific account number on our cluster table. That also has a non clustered index on the account number. And we expect this to be an index IQ. And just as expected, index seek non clustered. And at the end we can just drop our table. And here we go. 21. Data Manipulation Language Commands: So now it's time to talk about the data manipulation language commands. This part of the SQL language is used to retrieve, insert, modify, and delete data present in the database. So already went over some of the commands, but now we're actually going to go over them in a lot more detail. So we have a select, insert, update and delete. Select just lets us retrieve data from a database, or more specifically, an object that is within the database. Insert allows us to insert data into a table and update allows us to update data within an existing table, delete. We went over this one, we're going over truncate, but this is the one where it allows you to delete records from a table and was again, can be used with conditional statements unlike truncate. So you can say delete where and then put a condition so you don't have to delete the entire table. You only delete a subset of a table, only one row. So you can do that with delete, you cannot do that with truncate. Once again, the main differences between truncate and delete are truncate is all or nothing is much faster at recess the identity column and delete does not. 22. Begin With Retrieval: So now we're going to learn how to retrieve data from our database tables. And the ability to retrieve data from our database tables is very crucial. And in order to accomplish that, we can use the reserved keywords select as we went over. It is a reserved keyword which has other associated keywords that allow us to retrieve data in an even more of a flexible and intuitive manner. And in this section we're going to learn about all of those associated keyword. Here's a simple select statement though. This will retrieve everything from a person table. And this person table is the one that is available from the Adventure Works 2017 database, which we have downloaded an imported. And let's go over the statement real quick. So the asterisk here has a special meaning, and it means to get all of the columns from a table. That's all i means. From reserved keyword. It's in yellow, so it's a reserved keyword is one of those associated reserved keywords that helps to select statements specify which table to retrieve data from. So here it's simply saying select all of the columns from Person table in person schema. Now the asterisk is not the best practice to include in more permanent scripts that you will be writing. Instead, it's much better to specify specific column names, such as how we have it right here. So we are specifying business entity ID, first name, last name suffix from Person table from person schema. Now the second select statement is just showing us how we would retrieve column names which have a space. Now in the person table, we don't have firstName having a space in between. But if we had first_name like this, where it had a space in between, we would retrieve that column like this would put square brackets around the column and would return it that way. So when we are returning a column lists, we're not just limited to the specific columns on a table. We can actually perform modifications and change how we would like the column list to be displayed. This is called a calculated field. So here, right here, we're calculating a new field which does not exist on a table we'll will exist in our final results said That is the ordered quantity times the price of the units sold in order to see the total sale price. This is called a calculated field. Doesn't exist on a table, but it's going to exist on the result set. If you run this query, you will notice that the column name of the calculator field is unknown. So in order to make our results at cleaner and more intuitive, we can give it a name by using a column alias. So as you can see the query below, this one which is doing the same thing, but it's specifying a name for the column by using a column alias. And it can do it two ways. The OR is not reserved key or by the way, I'm just saying you can do it either like this or like that. First way we use the reserved keyword as after which we provide our column name inside square brackets. And the second way is to omit the reserved keyword As and just provide the com name, but it's just a shortcut and they both mean the same exact thing. So it's up to you. You want to write it first weighs more explicit. Second way is more implicit. That is all. Also the way you have column aliases. You also have table aliases. So down here, in a third query, as you can see, I have a column alias just like before, and then I have a table alias for sales ordered detailed table. I just put the keyword as right here and then provide the name, which can be anything and in my case is the first letter of each word. Thats all. So S OD sales or detail. So what happens is this. Ellie has now becomes the table so to speak, and we can use column names from that. So you might be asking why use it in this case, in this case we don't need it. Once we start joining tables together, you will see the need for them. Also note that I'm getting my columns out of the table alias, as I've already mentioned. And once again, even though I don't have to do it in this case, once we started using multiple tables with the same column name, because there might be the same column name and two different tables. We will need to specify where it's coming from. So you'll see the need for table aliases. So with calculated fields, we're not just also limited to performing arithmetic operations, but we can also return literal values and combine columns into one column. So you can see right here in the first example, you see we're combining first_name and last_name. And we also have a space in between and naming that result as fullname. So it's a column alias. So that becomes a new column on our column list that we return back. The process of combining characters, character words together or characters together is called concatenation, which is the fancy name. But this is what it is, right? We just put characters or character phrases. Any type of character values will put together is concatenation. That's all it is. The second example right here. You can see how we're returning a literal value person. And you can see how we're returning a literal value that is always going to be showing the same thing and that is the person. And we're also able to provide a column alias, which is entity, and we're renaming that column as well. Rest of it is pretty much the same, but right now we just learned about concatenations. So putting two columns together and renaming it if we want to, and also providing a literal value and renaming it as well. So now let's go ahead and see how SQL Server interprets these statements. So once again, I have the code ready to go. And this is once again, all the codes for all lectures are going to be available in my GitHub repository, which I'm going to link to this course in the article at the end. So you can go and check out all of these statements and queries and just follow along so you are not lost at any moment because I know I'm not writing them as I'm talking about him, but it's just more efficient that way. And if you have it right in front of you, you can just go over it. And then at anytime if you want to write your own query and experiment of queries, just pause the video, experiment with it, and then come back to the video. And also, if you have any questions at anytime, just pop in a question into the Q and a. I guarantee you I'll get back to you within a few minutes. And if not, if I'm really busy is going to be a few hours, but it's going to be within the same day. It's very rare that I take more than a day. So that's just good to know. Okay, so what we're going to do for us, we're going to try to get all the information from a person table, grab it, and then press F5. And here we see all the rows and columns from the person table. Pretty simple. Now we only want business entity ID, first name, last name is suffix. So we're gonna grab that and an F5. See, narrowed it down. Now, let's see what happens when we return some column that doesn't exist on the table? How does it going to handle it? So F5, and we can see it says no column name, right? Remember, so if we want to put a column name on it, we use a column alias and we can say as. But I chose to do the shortcut way. So you can do both ways. So now you can see it says total price. Okay? Next one is table alias. In this case it's not really going to make a difference. So if we run it, it's going to be the same thing. But as I said, once we start joining tables and using multiple table who had same column names, we're going to see how this is used and how is useful. So then the next is concatenation. You have firstName, lastName, and full name. Full name doesn't exist on the person, but we're going to create it in our result set. So we have this and then we say firstName plus. And there's actually a function that can perform concatenation a lot cleaner, but this is the first way we're going to learn. So it says plus, then we have a literal value, plus and then last name. So we're just basically calculating a new value saying displayed as and then putting a full name, the name that one and displayed ads. So select it, run it. And here you see that this Entity ID and then a full name doesn't exist on the table, but exists in our result set. And now we're going to see how to return back a literal value. I mean, this space is a little value combined with other values, but in this case we're going to actually show a literal value, right? So we have person and then we say as entity showed as entity column entity will want a business entity ID, first name, last name, everything else is the same, so it's just the literal value, that's something new. And here we go you can see person named the calm is named entity bins, Entity ID on the full name. If we took out the column alias, you can already guess what's going to happen is just going to say column doesn't have a name and we go, column doesn't have a name. Okay, I'm gonna bring it back. And there you go. So once again, this script is going to be available in my GitHub, and I'm not going to mention it again because you already know that all the scripts are going to be available on GitHub. So at any moment that you want to just hop into GitHub, get the codes from their habit in front of you so he can go along with the course. 23. Scalar Functions: So now let's talk about scalar functions. We have two kinds of functions in SQL Server, we have scalar and aggregate functions. This lecture is only going to be talking about scalar functions, and then we will go over aggregate functions later. Scalar function just means that our function is going to be performed on a single column. There are many other predefined scale of functions in SQL servers, so we will only cover the main ones. The main purpose of a scalar function is so taken a field, manipulate it, and then returned to manipulate a result. And it is up to your needs which scalar function you choose. Here are the main categories of scalar functions. Each category contains its own set of functions, which we will look at in the next couple of slides. So we've got string functions, Date and Time Functions are arithmetic functions and converging functions. Let's look at string functions first. So string functions allow us to manipulate character data. Some of the main string functions are concat, substring, upper, lower, L, trim, our trim and TRIMP. Once again, there is a lot more but we're going to be going over the main ones. So now here are the string functions. And the first one is our concat, which is the similar functionality are basically does the same exact thing as using plus a last name plus the empty space plus lastname. But we're using a function. It's a much cleaner way of doing it. Substring allows us to get only part of a given string. The first parameter is the column to use as a string. Second parameter is the beginning of where to start. So cut from the first string if the first character of the string. And then the third parameter is the last Lent of homage to cut. So start from one, go old way to force a length of four from the firstname. If the length exceeds the total number of the string, it will just grab the remaining length up to give a string. Then we have upper and lower, pretty simple. They just allow you to convert the calm data into uppercase or lowercase respectively. Trim removes any trailing and leading spaces from the given string. Since all of our data is pretty clean, I'm demonstrating it on a literal value which has leading and trailing spaces. Also note, as you can see, I've select without a from. So select without a froms still allows you to return a column list. But since you don't have a table to return it from, you have to specify your own column values, such as literal values and or calculator fields. You can test off functions with literal values using this, but it is not a good practice to have it in a production environment. That's exactly what we're doing here. And then lastly, Ultram and our trim, or tested the same way with a select without a from. And both just remove spaces from left and right sides respectively. So L trim removes trailing, trailing space from the left side and are trimmed removes a trailing space from the right side. So then date functions. The functions allow us to manipulate date and time values. We have four main. Once we have a good date, date name, day DEF, and date AD. And let's quickly take a look at. They do. So get function or get date function returns a current date and time hasn't arguments that it takes in just, you know, you say get date, parentheses to call the function and it'll give you the current date, date, name. Function gets you back to specify part of a date. So it has year, month, and day, and some of the other parameters that you can provide. But in this in this case we're saying year. So want the year part of the date. They did function returns the difference between two dates on the specified part of the date. In my case, it is the day. So I'm trying to get the difference from the day of the two specifies dates. And it'll calculate the difference. In this case it's between 107. And then lastly, date add function allows you to add to the specified part of the date and returns you the calculate it date result. Once again, we're saying we want to add it to the day part of the date. That's the first parameter. Second is how many days we want to add. And then lastly, the date that we'll want to add it to. Now, then the next category is arithmetic functions, and these allow us to manipulate numeric values. We have four main wants, apps ran, ceiling and floor. And this is how they work. So once again, we're using selects to test these functions. Abs function simply returns back the absolute value of the given number, which is it's only parameter. Rent function will give you back a randomly generated number. If a number is not specified, then it'll give you back a random number between 01. When the parameter is specified to the given render function, it will keep giving back the same random number. Ceil function returns the smallest integer number that is equal to or greater than the specified parameter. In other words, it runs up to number floor function returns the largest integer number that is equal to or less than the specified parameter. In other words, it runs down the number. And then we have conversion functions. So conversion functions allow us to convert from one data type to another and have waste to deal with null values. Some of the main conversion functions are cast, coalesce and is null. In this example, we will see how they're used. And there is a couple of caveats which we're going to learn about just in a few seconds. So is no function allows you to specify a full bag result in case the first parameter happens to be null. So we say is null. And the fallback. But if it's null, had null and null, it will return back a null. So in other words, if there is no backup value is null, won't break if coalesce has no backup values. And the last one, the last backup value is null, it'll break. And also Cola's can provide more than one value. So you can have one value here, one valley here, one value here, and then the last backup, let's say it's here, right? And if all of them fail, it'll get back to the next one that exists. And also we can have an integer and varchar here. So it will depend, the return type will depend on the backup value types. So this can be an integer, this will be a varchar. And then lastly, cast function allows you to convert from one data type to another. In my case, I'm going from a string representation, a date, time. You provide the parameter inside cast as the value to be converted. And then you put as a data type, you want it to be converted to. So we're saying the strength of the varchar and that's going to be converted to a datetime, so as reserved keyword and then datetime. Once again, everything that's a yellow needs to be that way. In this case, NHL doesn't have to be that way, but null is a reserved keyword, so I'm putting it as yellow, but it could have been also value just like we have it here. And then anything in white, as I've already mentioned, is the parameter. So now let's see how this all used in SQL Server. So as you can see already have everything pre-written and you can access. This goes, well, the first one is pretty similar to what we had, but instead of us saying plus we're using concat function that's available to us, just much clearer way of doing it. So if we run that, we can see everything comes back the full name. Now, there's no column name. We could have just used a column alias. I just didn't use it to keep it simple because we're just talking about functions this time. But you can definitely use as and then the name of the column, right? So call one or just full name in this case. And or you can just skip that and just do it like that and it would still work. And full name in this case, right? So then the next one is substring. So what this does is only grabs the part of the string. So let's actually look at the full string next to the substring. And you can see that it says Catherine. So the first letter is C, So we're grabbing the C and then from there we grab How many characters, one graph from it. So 1234, which is C a th, and that's the substring of it. Okay? So that's our substring. Then we can do upper and lower cases. So see the firstName is uppercase and last name is or all lowercase. That's a function that we have available. And now let's go over the trends. So trims we have trailing left and right. So I'm going to run it and see we get a value back. Let's copy the value here. So I've seen no trailing spaces. If we run this one, if we copy this back, you see I have this on the right, so there's a trailing space on the right, but the left one has been ruled. This one has been completely taken out. So if we run this once again. You see, it doesn't have anything next to it. But if we run this one, we copy that value. You see there's a trailing space on the right. Now, this is going to remove the one on the right, but the one on the left is going to stay right. So that's all l trim. Trim. So let's now go over the good date. This is going to simply give us back the current date. And then now this date name is going to give us the year from the given date. In this case, it should be 2019. They difference just says once again from two dates give the difference based on the part of the data. In this case it's day. So it's going to be difference between 710 and let's see what it says. Three. So there is difference between three, so three, so added to seven, you get ten right? Date add, we're gonna add one to this dates day. In this case, it's going to be seven, so this needs to turn into eight. And as mentioned, 20190908. So absolute will turn negative 5.2.5. Rend will give a random number from 0 to one. And then if a number is specified, so if you run this again, C is a different number every time from 0 to one. But if you provide it with a number, c number, then it's always going to give you back the same number. Okay? Ceiling will rounded up, floor will round it down. Same number, different result now is null. In this case, the force value exists. So we get my name. In this case, the original value is null, so we're gonna get the backup name. Coalesce, once again is going to act very similar to isn't all the first case of His null says my name. Second case should also act just like is null my backup name. And now will get it'll go from here to here to my backup name. And if we were to take this out to you'll see that I'll go into my last backup name C. And if we take out everything, this will break as mentioned. So you see, and then if we take, if we put a null is null, you'll see that it'll just give us back a null. As one of the caveats between Israel and coalesce is one of the things that you would need to know. It might be a interview question or just as you're writing code, it's good to know. So now here we're going to convert our varchar or character value into a datetime. And what we do is we have cast reserved keyword. Within that we put our character and then we turn it into datetime and there's other types that you can convert it to. But in this case, we decided to convert from varchar two datetime. 24. Sorting: So now let's talk about sorting. So up to this point, when we were retrieving data, the results were coming back in no order whatsoever. We have a way of returning our data back sorted, remembered the from reserved keyword which was associated with the select statement, right? There other reserved keywords which get associated with select statement to enhance its functionality. One of them being ordered by, this is the format that you have to have it in. Select the name of your columns can be one, can be many from your table. You can have your joins there as well, but you have your table then order by, after that, and then the list of your columns that you want to order it by. And we here have some examples sorting used. So you can see here we're just grabbing everything the same way. So nothing is different in each and every one of these scripts except this one that's a calculated field, firstName, lastName concatenated as full name, right? Let's go over the first one. Just order by first name. So it's going to order this entire result set by first name. And the default is the ascending order. If you would like to change the order, then you could specify a DSC for ascending and DEC for descending order, like I have the one below. You can also use orderBy with multiple columns and first sort by the first column and then by the second one. And this case, down below, I have firstName, lastName. But then you can change the order in which it can be sorted for each column. So in my third case where I have first name and last name, it's by default that's ascending, but I can do it so that the first one is by ascending and the last name is descending. And it can also be a third column by ascending or descending, It depends on your needs, but you have this possibility. The very last statement right here shows how you can have a calculated field width, a column alias, and then use that calculated field in order to sort the table by that, while still specifying the order in this case be in descending. So we have a full name that we calculated on this result set. And there we're saying we want to order it by that fullname in descending order. Now this will, may once again does not exist on the table, but it exists on the result said that we get back from the table. And we can use that calculator field to sort by it. Let's see how all this works. Once again, all the code is ready. First one is a simple order BY. So you see that we're selecting firstName, lastName in an ascending order. Here we go. Only by first name, that is, then we switch firstname to sort in descending order. So it starts with Z. Then we sort by first name and last name, both ascending order. So it's a and then starts with L. So in the third case, we have it being sorted by first name and last name. And it's going to by default sort both of them by ascending order. Now we can also, as I mentioned, sort by first_name and have it be ascending. We don't have to specify where we try to be explicit and then by last name, which is going to be in a descending order. So it's sorted by that. And then the last one is a full name. So we have a concat here that creates a full name, and that's our calculated field. And then we order by that field. So the name of that in square brackets and we just say one ordered by descending. So we run that and we see that now the actual full name is being sorted by descending order. 25. Column And Row Logic: So in order to perform conditional logic on a column or columns, wanting to use the case statement with associated reserved keywords when else and end. And simple example illustrating it would look like this. So note that we don't just list the column. Instead we're wrapping it up in the case statement, and that is where we perform the conditional logic column. Note how we have the column name right after the case key word, and that tells the case statements. So we have it right after the case keyword. And it tells a case statement that we'll be using this one column for all of our conditional checks. Then r When keyword, so when keywords right here, check for the possibilities. And then we can have more than one whence they move, which are followed by the Then keyword. That just says, if it's something, then do something else. So in this case we're saying when it's can, then display can found. We can also have an else statement which is just saying if none of the West statements match the condition, then it will fall back on the else it is just a default condition. So this guy right here, if you don't have a default condition and none of the conditions match, then the common, then the column value will end up being null. End keyword just denotes that the case statement is over. So this just says that it's over. And as you have noticed, we're using a column alias in order to give this column name. In this case, we're using a simple what's known is as a simple case format, which allows us to use only one column for our conditional checks. So we can only use forest name for it. But we have another format available which is called search format and that allows us to have more than one column for conditional checks. So this is the format for search format. And this format Note that we don't have our column name after the case statement. So before it was here, now it's not. And we provide column names after the Wang keyword. We can have more than one When keyword n, just like before. But in this case we can have more than one column by providing new columns and each of the new when keywords we can use the same one. We can also use different ones. In this case, we're saying firstname and last name. So when firstName is something, then display something battles. When lastName is something, then display something else. Otherwise display first_name, and then at the end, displayed that column value under this column name. So calm logic allows us to manipulate our columns based on a given condition before we get back the result set. Now, this is the role logic. So roll logic allows us to retrieve only certain rows based on a given condition. Quick demonstration of calm logic versus Roe logic is at the bottom. You can notice how some cells have the word changed and others have data in them. That is calm logic. We have some sort of logic on column one and column three that either show changed or data. Doesn't matter what the, why the logic is there. Just a, we have a logic that says either showed changed or data just the way we showed it. It says when it's something, then show changed, when it's something else, then show data or else show data. On the second table you see less Rose returned, which demonstrates what role logic would accomplish. And in this case we have some sort of role logic that filters out other three rows and just returns the first two. So the role logic to calm logic just changes the values within the column row logic allows you to filter out row. So in this case we have some role logic that filter out these three and only returned these two. Let's talk about where and top. The way would provide role logic to our select statement is by using a where clause, which is yet another associated reserved keyword that enhances the functionality of our select statement. We can also use top to filter out the amount of rows we expect to get back. We can also use where and top in the same select statement. We'll see that later. But so the entire format now becomes like this. Select You'll columns from your table along with joints will go with that later. And then you have your ware, which is your, your role logic. And then the order by which you already know it's the order by columns you provided with. Below you can see the entire format. That is the entire format. In the examples below, we're using a select statement just like before, but this time adding aware, reserved keyword right here at the end and providing our select statement with a condition. So this is our condition. And that condition states that we should only get back rows which have a suffix column that has a column of junior, so column value of Junior. And we'll run this later and we'll see what it returns back. But looking at the example below, you can see that we're using a new reserved keyword called Top. Right here. This new reserved keyword tells a select statement to only bring back the amount of rows that is provided right after the top reserved keyword, which in our case is ten. And then as mentioned, we can also use top and where together in our last case, we are using the where reserved keyword to only bring back records with a suffix of Junior. And then from that result we're grabbing the top ten results. So we have our ware, which is filters out all the rows that have only suffix has to equal to junior. And then from that, we're grabbing only the top ten. Let's talk more about where. So where reserved keyword has other reserved keywords that are associated with only where and they enhance its functionality. Some of the main ones are and, or in is. And we also have not an exist, but we'll see those in the practical examples later. So once again, this is how it looks. Select from where and order BY. So one quick thing. So below, obviously you can see the new ordering of our enhance select statement, where we're adding the where reserved keyword. And I've showed you this before if you'll few slides before. But this one thing to note is that this is the ordered that we need to follow. If you're rearranged the reserved keyword orders. In another way, SQL Server will not recognize it as a properly written statement and the action will be aborted. So now this is our enhanced where first way with select statement just like before and all the rest is pretty similar except the end reserved keyword, which is used along with the where reserved keyword. All that the end is doing is enhancing the logic for the wear. And it is saying that the rows which have a suffix that equals to junior at the same time have a firstname that is den, should be returned back and the rest should be ignored. So we're just enhancing the logic. We're saying return back rows that have suffix of Junior and first name of Dan. One below is very similar except the fact that this is saying the rows which have suffix of junior or rows which have firstName of den. So it's a little bit of a different logic. So it's either going to be suffix, suffixes that are junior or firstname that have done so. It also might be rows that are, don't have a suffix of Junior, but if they have a firstname of Dan, they will be returned. And Arthur select statement with where is using the reserved keyword in. We're just saying that we should only return rows that have the firstname of Eric or Adam. So right here, this is essentially giving us a capability to check our column against a list of possibilities. So you are saying, is this column available in this list? And you can have a bigger list or different types. So you can have integers or you can have more cars. Our last statement is an important one as it demonstrates how to handle null values in our whereClause, right here. Whenever we're dealing with null values in our whereClause to check if the column is null or not want to use in conjunction with is reserved keyword, like we have here. If we say Suffolk equals to null or not equals null, like we have it here. So using the standard arithmetic operation will get an inaccurate result. The reason is that null is an unknown. And so any comparison to it also is unknown, which results in false and the column would drop. So instead of using arithmetic operations, you special syntax that SQL provides which is null or is not null. So if you want to check whether the suffix is null, just say is null. And if you want to make sure that it is not null, then just say is not null. Now let's see how this is used in SQL Server. So once again, everything is ready to go. We have our case statements here. So I'm going to run this first. And we can see that in this case, first of all, we have to have a name, Ken, right? And in this case we can actually grab Kim because we just saw that. So let's actually do it with an existing name as grabbed that and graph Sam, right? So change these up. And just so you can see that actually happening. And you can see Kim found, and then Sam found the rest of them are just fine. So you change the values within the column and change it back. So same thing here. This is going to basically just look at the first name and last name. So you can run this as well. You can kind of play around with the names in the existing table. The person, see how it looks, but this is how you would do multi column logic. Now is actually go over the role logic. So once again, we'll the whenever the format. That's why I'm kind of going through these commands quickly because we already went over the, you know, the commands and the format and also the order in which they need to happen. You'd run, but it has to be select where and then select from and then where. And the way of our orderBy, which in this case, I just kinda skip that because we're only talking about role logic. So with that said, we ran this statement and what this is saying is only grab a suffixes that have JR. So I'm not displaying that, but if I was to display suffix as well, you would see that all of them say junior, right? And in this case, I'm going to omit it. But once again, you can play around with these scripts to see, you know, maybe a search on a different condition or enhance your conditions. And that way you can learn even more. So then we're using the top ten. So we're gonna grab only top ten results from our results set. And then the next one, it says, grab the top ten results. So first we're going to grab our suffixes, right? So let's actually put the suffix here so we can see what's going on. And here you can see that I'm grabbing the juniors, right? So in this case there's a lot more. But in this case, I'm grabbing juniors only, but then the top ten from that other set. So then the next one is going to be an enhanced conditions. So it's suffix of Junior and then firstName is Dan. So in this case, There's less rows because it's Junior and forest name has to be done. So there's, you know, it's just less rows that have bound. Now this one is actually going to be a lot more because it's suffix Junior, but also it can be, it doesn't have to be and it can be, or firstName is down, so it doesn't have to be a suffix of junior. It can just be firstName, is then, or is suffixes junior. So let's see what happens. As mentioned is a lot more Rosa, 37 rows got back. And another one is we're comparing our firstName against the list. So we have Eric, Adam, and while I know that there's another name called Sam, right? So let's actually put that in there as well. See what happens. So we get back a result. We got Sam, Adam, erik. Now let's say if I took out Sam, you'd see the SAM part will be gone from the firstName. So c is just add a atom and Eric. And then the last one is, we're going to see all of the rows where the suffix is not null. And let me actually prove that to you. So if I put the suffix here, you see that they are not null. But if I actually say is null, that is going to bring only the ones that have it in a null, right? So you see, that's how we deal with nulls. And that's our column and row logic. 26. Joins: So let's talk about joints this entire time. We have been talking about single tables for the power of relational databases comes into play when we're able to relate tables together via primary and foreign keys and retrieve values across from multiple tables. We accomplish this task by using joins. There are four types of joins in SQL, along with the feature of self joining, which we will talk about at the end. So we have available energy joint or join, left outer join or also known as left join, right outer join, also known as right join and full outer join, which is known as full join. And then we have the cell joining feature. Once again here I'm just telling you that the table values into scripts, all the joints are gonna be available in the script that's going to be provided to you. But for now, just follow along the scripts so we can get the concepts down before writing any code. Also in the code, you will notice that I don't have a foreign key on the customer order table. And that is because the customer ID doesn't connect back to the customer, customer ID. So these don't connect back. And that is because I needed to do that in order to illustrate how right join and full outer join work, you'll see why later. But for now, these are the tables that we're going to use, for example, so we have customer and we have customer order. So what does an inner join? The result of an inner join is only the matching rows from customer and customer order. And anything that doesn't match will be ignored. So in this case, we're only grabbing what matches for. So what customer IDs are in customer order? 12. So one repeats twice, then we have to then three repeats twice. Then we have 10111011 are not there. So we're going to get back 123. And we will see that that's the case when we run the code afterwards. So that's our inner join. And once again, here is, as an example, we don't really have to run the code just yet, but you can see that what it will bring back. So we already looked at, for example, the two tables and we did it manually, but here is how it would look like. So once again, 11233. And that will be common in what's in customer order table. So we're getting the middle part. That's inner join, which is also known as Joint. Please note how I am writing inner join in my query instead of the abbreviated version of joint, this is usually the way it goes in the real world. People specify that an inner join is happening instead of just writing a join, even though both would do the same exact thing. And this case is expected, we're only getting rose from both tables that we have a common match. Anything else gets dropped. Another thing to note is our use of table aliases. So see here, I have c. I'm just not saying as I'm using the shortcut and then and then for the for the customer order, I'm using CO2 as the first letters of the word. And that is the column alias. Since we have Customer ID on both, we need that in order to specify that this one is from customer, This one is from customer order. And then the way we write it is like so we have from the table and then we have our inner join, and then we join it to the table. So once again we have the inner join reserved keyword. Then we have the name of the table wanted to join a two, we use the table alias and then we say on reserved keywords. So we're saying on what do we wanna join it to? And then we actually specified the keys. So in this case, we are trying to join to the Customer ID, customer id and customer order customer ID. That's how we're going to attach the tables together to get the common results. So right here. Now talk about the left outer join or a left join. The result of a left joint is the matching rows from the customer. So in this case, the left side, right, and customer order including olden non-matching rose from customer as well. So customer, this matches what customer order right here, but there's other rows on a customer that don't match to customer order m, but those will also be returned. Once again, note that we are getting 123, but 45, root and Michael remember that they did not have a customer order. So that's the reason why we're getting null bec because there is no match. So notice how I'm using left join instead of the longer version of left outer join right here. And the format is basically the same. We just say left join. So the reason why is because in the real world, developers don't write outer edges right, left join, even though this might be different from company to company, The bottom line is that both would perform the same exact operation. In this case, we're getting all of the rows from customer table is you've seen regardless if they had a match of the customer or a table, as you can see customer for, right, we want to already went over that, but customer foreign five never put any orders. But they still get returned back. Though. They don't have a match on the customer order ID. That's the reason why we are putting nulls here. That's what you would get back. Now let's talk about the right outer join. The result of a right join is the matching rows from customer and customer order, including olden non-matching rose from customer order as well. So this is just the opposite of left join. We're also getting older comma results. But in this case, instead of getting old and non-matching rose from customer, we're getting old and non-matching rose from customer order. Once again, note that I'm using the abbreviated version, right join instead of rightOuterJoin. That is because once again, same reason is it varies from company to company, but it's known to be written as right join. Instead of rightOuterJoin. Once again, they do the same exact thing. In this case, if we look here, all the matching, a non-matching Rosen customer order table are showing up. You can see that customer order 67. Have no matching roles in the customer table, since the customer table does not have customer 1011. Also, you might have already noticed, but you can perform the same exact operation by using left joint and just switching the tables around, meaning select from customer order. So selecting from customer order and then left joining to the customer instead. Give it a try and compare with the original right Joan results. You'll see that it's the same exact thing. And full outer joins are full join. The result of that is matching Grossman customer and customer order, including older non-matching Rose Bowl from customer and customer order. So this returns us everything non-matching from both tables and also everything that's matching. Once again, I'm using full join for the same exact reason, full outer join, full joined to the same exact thing. In this case, you can see that all of the matching rows are showing up and both non-matching rose from customer and customer order showing up as well. So right here, we have 4-5 customer 45. They did not have an order. So we're returning null. They get returned back. And customer order ID 67 get also returned back, but they don't have a match on the customer because they're connected to a customer that does not exist. And that's the reason why I had to not put a foreign key on it because it would violate it and wouldn't be able to see the actual example. And now self joining. So you see that we have a table and it's only one table. And self joining is a technique in which we are able to join the same table with itself. And we can use any one of our Joins Inner left, right, or full. When we have a table which has columns that referred to one another, we can perform self joining. And in that process, what basically is happening is that we're joining the same table to itself on a different column. Almost look at it as if the primary key and foreign key, we're on the same table. In our current example, we have a table called employee readier, NF2 columns, which are important to understand employee ID and Manager ID. Now the employee id is the primary key of the table, and it is just dare to identify the current employee with a unique identifier. And the manager ID is just a field, which is, as you can see, can be a null as well. But this field is there to show which employee is the manager of the employee of the current row, for example, the employee id2. Right here. We see that the Manager ID is one. So the manager ID is one. In other words, ND is Virginia's managers. So ND is Virginia's manager because employee id is one of ND, right? Note also the entity does not have a manager ID and that just means right here we have null. That just means that he or she is a top manager and does not have anybody managing. Now if we wanted to write a query that give us all the employee and manager relationships from this table would need to use self joining technique as you can see in the query next to the employee table. You can notice also that I'm using some of the things that we've learned already. To further reinforce these lessons such as column aliases and scalar function such as concatenation. So in my query right here, obviously I have concatenated columns. Then I'm selecting only from one table, that is the employee and I have the table alias. And then I'm saying inner join from the same table. But what the different alias. And in this case it's m. In other words, I'm making a copy of the employee table and treating it like a table of managers. Almost look at it as if employee table just made a copy of itself and we're joining to that copy. So we're inner joining to the Manager ID of the employee table from the manager. So the employee copy, which is now the manager managers tables employee id. So we're saying for this employee, who is the manager, that's what we're saying with this expression for this employee, who is the Manager ID? And the Manager ID will be gotten from this manager table because we are basically getting the same table, a copy of the table, but using a different column. And this is going to give us a result back that shows us all of the employees and their managers. Now let's go ahead and look at all of this encode. Once again, I have everything in code already written. So we have two tables, the customer and customer order, as you've seen in the examples. So I'm going to create them. And once again we have insert statements and we haven't gone over that, but you already know what they do. They basically insert data into our tables and we'll go over them in lot more detail later in this section that is bad for knowledge. Just insert all of those values that we see in our presentation into the actual table. And then the same thing with the orders. And you can see that the customer's look like this. The orders look like this. Now we're going to do an inner join. And this says that we have customer one, customer two, and customer three, making orders. And remember how customer 12, we repeat it. That's exactly what we see. That's an inner join, left join. As you remember, I said that 45, which is root and Michael, they never made any purchases. That's the reason why we're getting null back from customer order table. The right join says that, listen, customer order ID 67 are made by customers that don't exist in the customer table. If we look at the customer ID once again or customer order, you will see that 67 are connected to customer IDs 1011. If we look at customer, we can see that there is no 1011. That's the reason why four are right. Join, we see that there is no connection. We've been the customer ID, that basically customer order ID, which is the order that a customer made. There's no connection between that customer and the actual customer table. That's why we're getting all nulls for this customer. And then we do a full join which is going to return us all of the results. Now, these customers never make purchases, and these purchases don't have associated customers, but we also get older matching rows as well. Now let's perform the cell join example. We're going to create a table employee. Once again use the insert statement to insert values into it. Let's look at it. So we see manager ID is nuff Randy. And now we're going to see with this statement which employee is associated with which manager. Let's actually just take a guess so we can say that Andy is a top manager and Andy is the manager of Virginia and Virginia is the manager of Bruce and Bruce is the manager of Ruth, and Ruth is the manager of Michael. Let's see if that is the case. So Virginia is the employee managers, MD bruce, employee, Virginia's Manager, Ruth employee, bruises manager, Michael employee, and Ruth is the manager. So that's it about joints, inner left, rightful, and self joining using self-referencing tables. 27. Distinct Data: Let's now talk about distinct data. It is a very common need to be able to return unique roles from a given table. In order to accomplish this, we use the distinct reserved keyword. You need to put the distinct reserved keyword right before the select statement where you will be specifying your columns. Whichever columns you provide in the select distinct statement will be used to check for uniqueness. So now we have select distinct your columns, whichever ones you want to be unique from your tables along with the joins where conditions orderBy your columns, which you want to order them by. Now here we have two statements. If you try running the first statement, you will notice that only 67 rows come back from a table which has a total of 2290 rows. How is that possible? Well, with distinct keyword and the combination of provided columns were saying, Look into the employees table, job title column and see how many unique job titles are listed in this case, there are 67 unique job titles. That means 223 job titles are just repetitions of those 67 unique job titles. The results of the distinction change as you add or remove more columns. For example, if we look at the second statements and try running it, we will get back 93 rows back, which is more than 67. That was our first result from the first statement. The reason is because the meaning has changed behind the statement, instead of asking how many unique job titles are there, where instead of asking how many unique job titles are there per gender? And so in this case, we might have a job title repeated since it could be held by more than one gender, for example, before we would only get software developer, but now we might get mail software developer and a female software developer. In other words, we're summarizing data at this point. Instead of just deciding on the amount of columns as we would like to get back or how many rows would like to get back. We are now able to summarize our data, which tells us a story. In our next lecture, we'll be learning about aggregate functions, which allows us to summarize our data even further. Now let's actually look how the distinct works in SQL Server. So once again, I have all the codes ready and we have the human resources schema and then the employee table within it and the job title. So let's run that. And we see 67 rows. And these are all the unique job titles. Now if I run the gender and then job title, we will see that the job titles are for males and females. And we can actually order by job title. So that you can see that there are duplicate job title accounts now for each gender, male or female, we have their own specific job titles. So a mail can be an account and female can be an accountant. So this is how we return distinct values from a table. 28. Aggregate Functions: So now let's talk about grouping data. So far we have dealt with this kind of an enhanced select statement. Select our column lists from our tables with joins where our conditions and orderBy with our columns did we want to order it by the above gives us control over which cons we would like to see and how based on conditional statements, which rows we would like to see based on one or more conditions and how to order it all when we get our data back, The next step is to learn how to group our data together. Now, if you remember when we were talking about scalar functions, you'll remember how the function was executed only on a single set of a table. So only on a single cell of a table. Like you see in the diagram below, right here. So if you were to use the scalar function concat, then you will be executing that function on row one and column one, row two of column one and row three, column one. So these guys here. But if we were to run an aggregate function on column one, then we're running dysfunction on all of the rows at the same time. So we can see in the diagram, we're grabbing all of the rows instead of grabbing them one by one, just like so, grabbing them all at the same time. This goes for all aggregate functions. And also, these are the most commonly used aggregate functions which are predefined in SQL is for us average, max, min, sum, and count. We're going to go over all of them. At the end. There's going to be a script that's going to help us to create this table and run the aggregate functions. But for now, just notice that the table is pretty simplistic and not perfectly normalized because I wanted to keep it nice and simple for the aggregation examples. And didn't want to separate them and add more columns just to make the table legitimate, but then make it harder for you to understand. So what's a grassy aggregate functions and grouping, you can go ahead and run those functions on more normalized and realistic tables with actual understanding and then get better results that way. And the following select statement right here. What we're doing is we're grabbing all of the rows from the amount column and using the Sum aggregate function to add all of the values and return a result back. So all of these are going to be passed into the sum, and then that's some will calculate the total and return us back one result and that's going to be 1830. What is more interesting is that we can use a where reserved keyword to choose which rows we would like to specifically grab and then pass those rows into the Sum aggregate function to get back to total. As you can see, an aggregation function takes an input in an array of rows. So we're grabbing these rows and these rows based on a condition. And this array of rows could be one or many. In this case, we're only grabbing two rows from the amount column, which are nine hundred and eight hundred. And those get passed into the some function which then uses those numbers to add them up or we turn back to total result. Same thing goes for other aggregate functions such as min-max and average. Also, as you can see down below, you can have multiple aggregate functions in one select statement. So this will return us the maximum based on all the rows selected. And this is going to return us the sum total of older row selective. Now let's talk about the count. The count aggregate function is a bit more involved because there are three ways in which we can use it. But before we dive into it, let's define what it actually does. So cowed usually gets confused with some, but the difference is that sum adds up all of the values from all the rows of the selected column and gets back the total counts simply counts the amount of rows that have been passed into it. Now let's go over all the ways in which we can use it. So as you can see, the first way right here is we're passing in an asterisk, which simply means return the count of the number of rows from all the columns. In this case, the number is 71234567. Even if you made the order ID, this guy nullable. Let's say it wasn't a nullable and we made a novel. And we said seven to be null so that the seventh row, the entire row is null. So null, null, null, null. Even in that case. If we run count star, we would get seven. Y is because cows star would still return seven because they're physically seven existing rows. Now the second one, count column name, which is this one right here. The difference is that now we are looking to count only the existing values in rows of the specified columns. So in our second case, the customer ID null seventh row, which means that the seventh row does not have data for the specified column and it does not get counted. Hence why the result is six. So right now it's 1234567 is null and we don't count novels when we're using count and the name of the column. And our last case, which is this, we're saying conduit to distinct, so count and inside of it we're using distinct inside of the function. The way this works is like this first that this thing keyword goes ahead and selects only the unique rows for the specified column. By the way, null also get selected. So to distinct function would give us back for values 123 and null. So 123 and null. So those are all unique. And then this list gets passed into account aggregate function and account looks at it and treats the result as the above, meaning that count with a column name. So remember how the null was ignored. So it's going to look for the existing row values with the specified column. And in this case, 1-2-3, or existing values of course, but null is not. So in this case, the total will be three in the select statement. Now you can see how we're selecting the customer ID column. And then we're using the Sum aggregate function to calculate the total sum of all amounts associated with the Customer ID. After the phone, we're using the groupby reserved keyword and put the same exact name of the columns, in this case customer ID right after the group by to columns you have in your select right here. So those columns in the select statement should always appear after your grew by reserved keyword as well. It is just another way of saying I'm selecting these columns, in this case Customer ID to be grouped by and display for a specific aggregate function, which is sum. That's going to be counting the amounts for each customer. And that's what grew by helps us to do. So we're selecting the columns. We're creating a group via group by based on those columns. And then for those columns, we're running our aggregate function. So in this case, it would be 123 and null. And One-two-three and null are the groups. By the way, there are grouped by customer ID and then part each group, we're going to get a total. So in this case we're customer one, we're going to get the sum of its all of its purchases. So in this case, or purchases or the amounts, right. So it's 900 plus 800. The result will be for Customer ID, then ten plus 30 plus 40 will be the result for the customer IDE2. And the same thing goes for each and every other customer ID. Now what we have done is we simply added an extra column to grew by, and that is a category. So what is going to happen now is we're going to have a subgroup of categories within each customer ID. In other words, we will see that we have Customer ID one, but then we will see that Customer ID made purchases in two unique categories. Customer ID two has also made purchases and into unique categories. And customer ID three has only made one purchase in one category, so there's no change on that one. So when we run the statement, we will see that that is exactly what happens. And a good example is customer to furniture category. So customer to furniture category, well, we see that the total is 40 because we're only adding up the amounts under the subgroup and in this case is the category. So once again, we say customer ID and category ID. So we're creating another group. So we're saying groupBy customer IDs category, which the customer ID can have two categories, in this case, electronics and furniture. And then sum those values up. And the reason why Customer ID is good because there's two amounts. So the customer ID will show up twice for miscellaneous and for furniture, for miscellaneous is going to be 40, but for furniture is going to be ten plus 30 instead of templates 30 plus 40, like it was on the previous one. Just the way we can select which rows to get back with the where reserved keyword. We can decide which groups to get back with the new reserved keyword called Having. The way we provide a condition after r where reserved keyword is the same way we provide a condition after are having reserved keyword. The main thing to remember once again is that where relates to the rows of the table while having relates to the groups that we create from the rows of the table. So in this case, we're saying select Customer ID, category ID, get the sum, everything is still the same. But what we're saying is grab all of the groups, and we had other groups before as well. Remember the groups for furniture and miscellaneous, miscellaneous and null. So only grab groups for which the sum of the amount is greater than a 100. For furniture, subcategory is definitely below a 100 for miscellaneous, customer, two is blow, a 150 is below hundreds. The only ones that the sum is above. 100 is nine hundred and eight hundred and it's not even a sum. It's just that a number happens to be above a 100, but it's still passes a condition, so we only return. These groups, notice that we can use where and having only one select statement, meaning you can choose the rows you would like to use for the grouping, and then you can choose after that which groups you would like to show. So once again, let's put it all together. We have select all the columns that you want to select from your table and all your inner joins, left joins, right joins, full joins. And then you have your where condition to check for any Rosie would like to select the row condition. And then groupBy will group your columns based on your select statement. And then having is the condition that allows you to select the groups that you have. An order by at the very end allows you to order by older columns or any columns you would like. And you can specify ascending or descending. So now let's put it all together. If you look at the following example, we first see that we have our select from where portion right here. So right here, which basically simply grabs all rows from a table where the amount is greater than ten. This grabs every row except the third rule, because the amount is 1010 is not greater than ten, so we ignore it. So this wrote never gets selected because of where condition. Then on the rest of the rows we performed a grouping and we grouped by category as a sub-category of customer ID. We end up having four customer ID groups won for a customer ID, one another for customer a2 and a3, and last one for null. Then we have subcategories within those group just like before. And we sum up the amounts within each category. Then we have our having reserved keyword, which is going to filter out all our groups. And the condition being is that the sum of the amounts within each subgroup has to be greater than 50. We, as you know, it's right here, the condition for groups. So we end up only choosing Customer ID one for electronics and customer ID one for furniture. The total amount ends up being greater than 50. And in this case it's just the number happens to be greater than 50. So what happens is, first of all, this, right, does not choose this row, and then we get the rest of the row. So we get 124567 order IDs. Order ID three does not get returned because of this condition. Then we start grouping it by customer writing a category ID just like before. So we have Customer ID, customer ID, customer ID, Customer ID is null, but still we group it by that and then by category ID. So Electronics and furniture, and then Furniture, miscellaneous, miscellaneous and null. And then based on each for each category or subcategory within the Customer ID. We then applied the aggregate function, which is the sum. In this case there is none to sum up, so it's 900, this is 800, this is 304050 and null. But then we have a condition for all our groups. So we go through all our groups and it says, well, the sum has to be above 50. That is the sum for this one above 15. No, so we ignore that. We ignored this. It's 50 is not greater than 50, so we ignore this, we ignore 40, we ignore 30, and this one was never returned. It's not part of any group. And then the only one that ends up passing the condition is eight hundred and nine hundred, which they, once again, the number happens to be greater than 50. Let's actually go ahead and see how this works in SQL Server. So once again, I have already and we're going to create the orders table. And then we're going to insert all of the values just like we've seen on this lights with the insert statements. And now let's go ahead and see how the Sum aggregate function works. We're going to say select the name of the aggregate function, parentheses the name of the column from and from the table that we want to select it. And 1830, just one thing has been returned, so that's the sum of all these amounts right? Now let's see what happens when we're just wanna grab it for Customer ID one, which in this case Customer ID one is this. So there's going to be 900 plus 800. So that's 1700. Now we can actually return multiple aggregate function results only for customer one. So now we see it's nine hundred. Nine hundred is the max number, right? So nine hundred, eight hundred, nine hundred is max. And sum is sum of the both of the numbers for only customer one. Now let's look at the count. Looking at the count for orders, asterisk says seven, right? So 1-2-3, 4-5-6, seven. If we provide it with the column name, customer ID, one of them is null and null does not get counted when we use the count and column name. So that is going to give us six. And then four distinct. This thing is going to look at the customers and say One is this thing too, is this thing. Then three is distinct and forest distinct. So we have 123 and null, but remembered then we pass that into account and count ignores nulls when we have a column name. So this is going to say three as we expect it. Now let's look at the groupBy. So for each customer ID, we're going to get there some. For null. Obviously it's null for customer one, it's 900 plus 804 customer to it's 103040. So this is 7080. Customer three is just 50. There's nothing else to add up. And then we can do by customer ID and category. In this case, we have subgroups within the group. So in this case we see just like in our slides, 9008404050. And now we're going to try to select only the amounts. So these guys that are greater than a 100. So now we already have grouped it by amount. But we are going to give it a condition to say only grab ones which are above 100. So a total sum of above a 100. So it's greater than a 150 is not greater than a 140 is, 40 is not. Once again, null is not the only ones that we're going to end up with our nine hundred and eight hundred. Let's see if that happens. And there we go. Nine hundred and eight hundred. Once again, if we go back, these are the only once. And if we choose this, then we're dropping everything else and only grabbing the ones who want. And once again, this was the last thing that example that we just went over. And this is using where and having the wear is going to drop any rows that don't match this condition. Grew by is going to allow us to group and then having is going to allow us to choose which group we want to select. If we run this. Once again, it's the same result because we end up having the customer ID, one end for electronics and furniture have an amount that's greater than 50. So that's aggregate functions. 29. Sub Queries: Now let's talk about subqueries. Simply put, a subquery is a select statement which is included in another select, insert, update or delete statement or in another subquery entirely. In this lecture we will now be going over insert, update, delete. We will talk more about them in the upcoming lectures and in the practical examples lectures. This lecture is going to focus on subquery is related to the select statement. So a subquery can be included in any part of the select statement except the groupBy. The only difference is the way it is used, which depends on where it is placed, part of the column list, tables or conditions. So once again, we have the entire format of a select statement. Select from where group by having ordered by it. And then we say what belongs next to a select. We have columns, then we have tables for from where has the condition grew by has columns again that are listed and select having his conditions and orderBy has columns that we want to order it by. So when used as part of the columns, we are talking about Select and orderBy. When used as part of the tables. We're talking about the from when used as part of the conditions. We're talking about where and having, and we're going to go over each and every scenario. So these are the tables that we're going to be using for our examples as we're going over oldest sub-queries. And once again, a squib, we will be ready right after this. So let's take a look at this. As you can see, we're calculating a row, which is this guy base on a separate query and defining it as its own field. This is just like a calculator field, but instead of using a function, we're using a subquery to calculate the rho value and then assigning it a name in our case, being purchased total. So we're basically grabbing the entire total for a given customer ID. And then we're using the value of that to calculate a column. So there's a calculated column based on a subquery. And our second example, which is this guy, we have same query use into orderBy. So we use it in the order by and in this case, the subquery calculates a value for each customer ID, which we then use to order that table by, and in our case, in a descending order. So a few keywords would need to learn is inner query and outer query. The inner query, as the name suggests, is the inner one which is within nested and other one is the one outside which uses the inner query. Let's go over what we have in our first example. The first select is the outer query. So we have this outer query. And the select which calculates the sums is the inner query. So this is the inner query. The second example, once again to first select this one is the outer query and the second select statement with an order by is the inner query. So this one is the inner query. Another thing to learn as a difference between correlated and uncorrelated subqueries. This is just a way of saying if your inner query and outer query are related. In our case, we have, as you guess, a correlated query because we're using customer ID from our outer query, right? We're using the outer queries Customer ID to calculate the value from within the inner query. And same thing for the second one. In other words, we cannot run the inner query without also running the outer query. Or the inner query is not completely independent of the other query. Now another scenario is we can also use subqueries in are from reserved keyword, just like so when we're selecting from a table, usually Wu joined to a sub-query which gets executed first to give us back a result set to join two. So we're left joining, but this is not a table, it's almost like a virtual table. And this is where we're running our query, a separate query that then gets executed, gets results back, and then we are joining two. This result said that's within the parenthesis. Also notice that in this case we have an uncorrelated subquery because our inner query can be rent independently from the outer query. We can run this without having any connections with the outer query. Another thing to notice is that just like with table aliases, we have given an alias to our sub-query right here. We have as reserved cure and then say by count. So we're essentially treating it like a virtual table, as mentioned, that exists only when we're running the query and then were able to join to it. So we're doing, what we're trying to do here is we're trying to figure out who is a repeat customer and who is not. And we're doing that by first calculating the amount of purchases inside of our inner query and getting back once who have purchased more than once right here. Then once we have that information, we left joined to it. So we're left joining to the inner query result via the customer ID. And whichever road does not get joined, we know they did not have more than one purchase, so we run a conditional statement on it and display information right here. That makes sense. If the results are null, then it is a non repeat customer. Otherwise it is a repeat customer. So once again, we're just doing a simple count and seeing how many amounts are there per customer and whoever does not have more than one. So if you if you're a customer and you only have one amount listed, then you will not be included in this result set. You have more than one, then you will be included. Then all these customers are a part of this results in within this virtual table. Then we joined to it via a left join, via customer ID. And we're joining to the customer table. Now on the customer table we have all of the customers, even the ones who purchased once or twice, but the ones who purchase only once won't be available on here. So they will drop and we will get a null result just like we went over in the left join and just joins lecture. So once that happens, we will be able to catch it in our column logic. So we'll say case when by count amount of purchases, which is right here. So if the amount of purchases is null, as in nothing came back, then we know it's a non repeat customer. Otherwise it is a repeat customer. So that's what happens here. The question might arise as to if we could do the same thing, similar with a joint and skip the subquery. And the answer is no because the moment we add the having count at the bottom. So if we didn't have that entire sub-query in the middle, right? And we had a, just a groupBy and having on the entire thing, it would affect our entire result set. So we will only see repeat customers, but the none repeat customers will drop. Whereas in our sub-query example would first perform our calculation, then we would left joined to the entire Customers table. So whichever customer didn't have a calculation made for them, we knew they were a non repeat. So we're able to run a conditional statement and label them appropriately here. To having count has to do with the entire results it on the previous one. Right here. The having count only had to do with this, but the rest of it was the entire customer table. So then we would join only what we calculated back to the customer. So moving on, when it comes to where we have two main techniques for using sub-queries in our filtration process. So first by using the in reserved keyword and using the Xist reserved keyword is the other one. The rule of domestic use in against smaller tables and use exists against multi column and are bigger tables when checking for existence. Let's go over that in first. As you can see, we're adding it right after the where reserved keyword. Then we put the column name for which we want to check the existence for another set of data. In this case, we're saying select all of the rows from customer order that also exist in the customer table. To also exist in the customer table is where the in reserved keyword helps us, right here. So you see after the customer ID, we have the in reserved keyword. And then inside of our sub-query, right here, we have our sub-query. Note that in our sub-query we're selecting the same column name. So customer id and customer ID here. So it's the same column name as the one we're checking for. In this case Customer ID. This is how we connect the two together. So we're saying that customer order should return rows that are only in this list, that exist in this list. So we're saying customer order has Customer ID column on it, but only returned the ones that are in this result set. And the results set is a result of a sub-query, which in this case it's the customer IDs from the customer. Now let's talk about the exists reserved keyword. We're accomplishing the same exact thing as on the left. So these are similar results that are getting back just written differently. Once again, the difference is in performance. So for small tables use in and for bigger ones use exists. Also if you need to join two more tables, exist is a lot cleaner way of doing it. Now let's look at the syntax for both. The outer query is pretty similar. So red here is pretty similar from customer order, from customer order. But after the where reserved keyword, it becomes different. We have our new exists in here as the reserved keyword. Then we have our parentheses, and inside of that we have our sub-query. This is, as you can see, a correlated subquery, since the inner and outer queries are dependent on each other, customer ID relates to the customer ID of the customer order. So C, Customer ID, SEO, customer ID, they're dependent on each other. Now, another thing that might seem a little off is the equator. We're saying select null instead of right here, select null from. Instead of any specific columns. And the reason is because for the inner query, we don't really care for any calm specifically, we're not going to be displaying them or anything else. We more care about the roles of the table and being able to check customer customer IDs against customer order customer IDs. And that's exactly what this is doing. And so a knowledge just one way to say that we're telling SQL server not to worry about looking up the columns, even though if we wanted to, we could put them there, but there's just no 0.1. Last thing is we can negate both are ins and exists conditions by using the not reserved keyword. In other words, if we wanted to see customer orders from customers that do not exist in the customer table. We would say we're not exists or customer not in. But there are specific cases. All customer orders have associated customers, so we would not get any rows BEC. Now the very last example is how we have a subquery on the having reserved keyword. Although you usually use subqueries as search conditions in the where clause, sometimes we can use them in having clause as well. When a subquery appears in the having clause, like any expression into having clause, it is used as part of the row group selection in the following example when we're trying to accomplish is the following. We're left joining to customer, to customer order and calculating the average amount per customer and grouping the calculated average by customer ID, first name, and last name. Oldest stuff is not new and we already know what all of this means. So right here we're saying left, joined to customer order and group by customer ID, first name, last name, and just give us for each customer id basically because the firstName, lastName are going to be the same, give us the average of all the amounts purchased. However, the new thing comes into play when we see a subquery on the having clause. So let's talk about that. C Average says Average having. So we're selecting which group to get back and we're saying having average of all the amounts that's greater than and then we have a subquery. So once again, we say having, then after that we have a condition saying the average amount per customer should be greater than and then we have a subquery. The subquery is not correlated and just simply goes around the entire customer order table and calculates the average amount based on all of the amounts for all to customer. So what our condition is basically is us saying if this particular customers calculated average amount is greater, then that calculated average amount based on all customers include the current customer. Otherwise dropped a customer. This demonstrates how to have subqueries as part of the having reserved keyword. But usually we use subqueries in the where clause, though it is good to know the full range of possibilities. So once again, everything is ready. So let's create our tables. First, customer than customer order. And then let's insert the values. As we've seen in our table drawn in the presentation. And then we're going to include all the customer orders. As you can see, this is our customer. And these are our customer orders. Now, let's see what happens when for each customer, we're calculating their total amount, total purchase amount via a subquery. So what happens is we go to Customer one, right? And customer one will be Kimberly. And for customer one, We have two amounts. So customer ID and then the amount 1020 and that the total is 30. And it's exactly what we calculated via the subquery. And we can do the same thing. And the order by the order by all that does is it's going to order the customer based on their amount there total amount. So in this case, we're saying descending. So the top one is going to be Louise, and then it's going to be stephanie, and then it's going to be Kimberly and then it's going to be better. And we can actually selected just so we can see that in here as well while we're ordering it. So you see Luis definitely Kimberly embedding. Next. This is a bit more complex. But this one is where we're figuring out whether a customer is repeat or not. You can see actually that this is a non correlated query. So that if we run this, you can see that it says customer 124 are repeats because they have purchase amounts of two. But what we do is we left, join this to our customer. And then whichever customer can be joined to this customer ID, we will pick it up and it won't have an amount of purchases as null. Otherwise, if it does have it as null, then we know it's a non repeat customer. So let's give it a shot. And here we go. Two repeat customers on top and third one at the bottom. And then we have a non repeat customer, that's Betty carrot. I'll check it out. So 123 customer three doesn't have any repeats, and the rest of them, 112244. So that's great. And now you can see that if we were to try to do the same thing without a subquery, as we saw in the example. This will actually get rid of the non repeat customer because it's only going to pick up anything that has a count of greater than one. This is just to show you that we needed the sub-query in that case. Now, we're going to put the sub-query in the where. And in this case we're grabbing everything from the customer ordered that also is in our customer, which is going to give us the following result. So all of these customer orders have a connected customer because of the in reserved keyword. And the same thing we can do by using an exists same result. Come back. If we say not. That's the way in which we can negate it. But in this case, nothing's going to come back because we have connections with everything and where none exists, which is also going to not return anything since we have a connection with all customers and customer orders. So once again, at the bottom, I just have examples for you so you can play around with it, whether it's not or it's without a not. And lastly, we have our sub-query in the having reserved keyword. In this case, we're saying that we want to calculate the average amount for each customer. So let's actually go ahead and see their averages. So the average for each and every one of them is right here. And it says 15302020. And what we're saying is we want to look at the total of all customer averages. And if the current customers average is greater than the total amount of averages, then we're going to include that customer. So actually let's go ahead and include that in here as well. And let's run it without the having clause. So in this case the total average is 21.4. So this will drop, this will drop, 15 will drop. So customer ID two is the only one whose personal average amount is greater than the total average for everyone including theirs, because greater than 21. So let's actually see what happens. This should only return as one rollback as exactly where it is. So those are the sub-queries. And once again, the script will be included in GitHub so you can check it out and play around with it. 30. Set Operations: Now let's talk about the set operations. The set operation allows us to combine select queries together. I'd like subqueries were not nesting select statements within each other. We have two or more separate select queries which get their own results. And then via set operations, those results get combined. We have the following set operations available to us in T-SQL, we have union, union all intersect and except. So here are the tables we will be using to run our examples on. The reason why I'm making two different tables or making the two tables look differently. Because most examples make it very simplistic and almost unrealistic at time. So we will add a bit of reality into our examples by making our two sets a little different. And then see how to handle that situation. Because if they're exactly the same, that is the easiest situation and there is nothing to solve in the following tables, we have firstName, lastName, which is what is common amongst into tables. Then we have member id and customer ID. For this example, we will pretend that ids, even though into different tables, have different names, they have the same meaning, so they're interchangeable. Another thing is that Customer table has h column, which member does not have. We'll see how to take care of that later. Now let's go ahead and go over the set operations. First is the union operation. Union combines results sets of two or more select queries. In this case, we will get back members and customer data combined in one result set. Now you might be saying, well what happens in the middle part? And that's where data gets overlapped. But that is also where the difference between union and union all comes in. As you can see in the results on the right side, right here, we have one more extra row into union all. So this guy, and if you count is going to be ten here and it's only going to be nine year. So that result is customer three Tom cup. In both tables, Tom has the same ID, same firstName, lastName, and age as well, which happens to be null, but they're identical in both tables. So what a union, all rows like that will be returned when a union only one of those roles will be returned. So in this case they're identical, but with union all we still get him back. With union. It combines it into one. So even if there are five queries instead of 25 identical matches come back with union, you will only see one, but we'd union all, you will see all five. This is union and union all inaction. And this is how we actually write the statements. So you have two separate select statements and then union or union all reserved keyword in the middle of them, and that is all. And there are three rules to follow in order for these statements to work. First rule is that the column list from all queries being combined neat to be in the same exact order. So these have to be in the same exact order which we have in this case. You see we have IDs first, then firstName, lastName than age. We didn't have to rename member id to customer id, but doing it keeps it clean. Also notice that the member doesn't have an age. But we're creating a new column just to match it up with customer. And we can set it as a literal value or a calculated value, and that is up to our needs. Another way to match them up is to ignore the age column from the customer and only matched the IDs first name and last name. Second rule is that there needs to be the same amount of columns and all queries. As I've explained, we already have that in our queries written right here. It's the same amount for 444. And lastly, the third rule is that all columns into queries being combined need to be compatible or have the exact datatype. The next set operation is intersect, and this allows you to only see the common matches between queries. In this case, we will see only one, which is Tom, as we've already found out from the previous slide. So this is going to show us what's common between member and customer tables. And once again, it is Tom. That is, if we select the customer ID, first name, last name, and age. So in this case, we gave the member Tom from members table Null age, which is identical with what we have in our customer's table. That is the common data between the two, the common role between the two tables. And that's exactly what we get back. This is how we write our intersect statement. Once again, we have to select queries and then we have reserved keyword intersect right in the middle of them. And the same rules apply here as well. We have to have the same datatypes, we have to have the same order and we have to have the same amount of columns to last set operation that we will be going over is the except, which allows you to get the result said that is only in one set and completely not in the other. As you can see in our case, the blue portion is what the acceptable return, which would be members that are just in the member table but not in the customer table. And another example where we have all the customers which are only in the customer and not in the member table. So right here, we have members that are only in the member table, 12451245. And they're not in the customer table. Why? Because of the age and the same thing as because the customer 1245. So 1245, they're not in the member for 45 is the firstName, lastName, and H for the rest of them, it's age. But you see that for Tom cops, since we're going to provide ages null, that is in both tables. So that is not being returned because that's something that's common and we ignore anything that's common or something that is also in the customer table. We only grab what is in members table or any table that we want to specify. And in this case, we do the same thing with our customer table. Let's take a look at how this is done in code. So our first set query is going to return us older rows that are in the member table, that are not in the customer table. So in this case it is going to be everything else besides Tom COP, which is customer ID or member id, more specifically 1245. So right here and what we have right now is we have, we specify the table that we say we only want to get rows from that table and nothing that's in common with the other table. And we don't definitely don't get anything from the second table. So this is what the first query is going to specify it. And if we want to get anything that is only in customer, then we just write the customer first as the first query. Then we say, except we use the accept reserved keyword and then we put the next table after that. So now let's take a look at how this is done in SQL Server. Once again, I have old a COS ready. So we're gonna go ahead and create the same member and customer tables. Here we go, we're going to insert some of the test data that you've seen on the slides previously. In membrane in customer. And then right now we're just going to run the union statement. This is the first one. And if you remember the results, this is going to only return one Tom. So you see Susan twice, James wise Tom only once. And the rest of them, the first name and last names were different anyways. But Tom is common in both tables and we're specifying null for age. And in customer who actually run this, we'll see that Tom has ages null. And if we run this same thing is being returned. But when, when we run a union statement, we see that it's only being returned once. Would union all the differences that once again, we see Tom once and then twice. So it returns all the duplicates as well for the intersect. Once again, we expect to only get one row which has Tom. And that is the reason is because that's only in this common in-between two tables for except as we just went over, this is only going to return us. The first table is what we specified to say compared to the second one and only return rows that are just in the first table, in this case in the member table. So 1245, not shared. And then the same thing with the customer. If we run that. 1245 are not in the member table. Once again, these codes are in GitHub. So you should already have them in front of you. And if you want to just have a quick practice, you can just write them as you go by. Just pause the video, right? Um, and then run them afterwards. But this is basically how you run set operations. 31. Manipulate Data: So we have learned all the main caveats that had to do with retrieving data. Now it's time to learn how to insert, modify, and delete data. We're using the insert statement with an associated reserved keyword called into to specify the table, one to insert into. And after that, we specify the columns of the table, then we use the reserved keyword values, like so. And by the way, we provide the name of the columns within parentheses. Then we use the reserved keyword values and put in our values that we would like to insert into order to match the columns listed above. For my second column, I'm using a scalar function get date in order to specify the current date and time that is going to be inserted into the tables Modified Date column. So once again, we use insert into reserved keywords. We provide the name of the table. Inside parentheses would provide the name of the columns. And then we say values. We used a reserved keyword values. And then in the parentheses, once again, we provide the values that we're going to insert into our table columns. And the order should match and the types should match, then we are using the update statement. This is going to let us update values on a table. So we write update, we used the update reserved keyword, then we use the associate a reserved keyword set in that order and provide the update statement with the column name. Then we would like to update along with the value that we would like to set it to, just like so, so we say Name and then Emergency. So name is the name of the column, emergency is the value that we would like to assign to the name column. Note that my name column is in square brackets and that is there because word name happens to be a reserved keyword in SQL. So we're putting square brackets to let it know that we mean to refer to a column name on our table. Lastly, we're using the where conditional reserved keyword, which is also associated with the update statement that is going to tell us which row is going to receive the updates. In this case, we're targeting the column phone number type ID that equals to four, just like in a select statement. Since from our previous answer statement we inserted a new row. There is an auto-increment identity column on the phone number type table, which set the phone number type ID of our previous record, 24. So that's why we're grabbing the fourth record. So in other words, we inserted a value emergency phone number, but now we're updating that value to emergency. And lastly, we're going to learn how to delete records from a table. We're using the delete statement, which is going to allow us to delete old records or a specified record from a given table. So we can say delete from Person.new phone number type, which would basically delete all records from the phone number type table that is located in the person's schema. But unlike truncate, you can also specify which specific rosy would like to delete. And that's how you do it by using a where reserved keyword and then providing a condition. So our condition right now is we say Go and find the phone number type ID column, and then find the value that equals to four. And when you find matching row, then delete that row. So it's pretty much similar to a select statement, but instead of us retrieving data, we're deleting data based on a condition. Now let's go ahead and see how it's done in SQL Server. And once again, all the code is available to you. And now we're actually going to mess with some of the Adventure Works 2017 tables. First of all, let me go and try to grab the emergency phone number. We're basically saying select from phone number type and giving it a condition, saying grab the row for which the name is emergency phone number. This shouldn't return anything because there's nothing like it. And once again, there's nothing there. If we were to grab the whole table, see that there is actually three rows on itself, home and work. And we're trying to add another one that's emergency phone number. So right now there's nothing that says emergency phone number, so we don't get any rows. Where once we run the insert statement, it says one row affected and we run the same thing again. You'll see that now we have an extra row. So I'm going to select it. And then you see we have four rows now. So we can just grab, all of them are just grabbed a fourth one. And you see we're saying grab it where phone number type ideas for. And just like the where you wanna select, we can use the wear on an update. So right now we're just selecting that row and then updating the name column to emergency. So once we run the update statement, you will see that once I run the same statement, again, the select statement, and you'll see that it turned from emergency phone number to emergency. So if I select the whole table, you'll see that that's exactly true and went from emergency phone number to emergency. And lastly, we're going to delete this record. So this is the fourth row. And we're going to delete it by using a delete statement with a condition of using where reserved keyword with it. So one row affected. And if we run this again, it does not exist. And if we basically try to select a whole table, you'll see it only has three rows, just like before. 32. Data Control Language Commands: So now it's time to talk about data control language commands. This part of the SQL language is used to enforce database security and a multiple user database environment. There are two commands that are used to accomplish these tasks, grant and revoke. And the next lecture we'll be talking more about these commands. But in this lecture we need to go over some structural concepts in SQL Server to better understand how security is handled within the database. So first privileges. Privileges and SQL Server are access rights given to the database user for a database, objects could be a table view stored procedure. As you can see, there are two types of privileges, system and object system privileges dare to allow the user to manipulate database objects by using create, alter, or drop commands. Object privileges are there to let the user manipulate data within the database objects or access to data by using select, insert, update, delete, and execute. As you can see, system privileges are just there to let users create or destroy or edit new database objects. And object privileges are there to access data from those database objects or manipulate data within those database object. Now let's talk about roles. Roles are basically a group of privileges that can be assigned to many users. When assigning a privileged to a user, you only do it for that one user. But when there are many users for a given database, it could become very difficult to manage all the privileges for each person or each user. A role could define a set of privileges for multiple users so that when you change a privilege on their role, it automatically syncs it up to all the users attached to it. Let's look at a diagram. So right here you have select, insert and update, and you have three users. Select needs to be assigned to all the people incidents. There'll be assigned older people and update. It needs to be assigned to all the people. We do them separately. Now these are only three users. But as you can see, if we have more than this, the web will grow over time and is going to be very cumbersome to deal with it. Now with a role, it allows you to group a set of privileges into a container, just like so. And then you can assign that role to each user, which is a lot cleaner. And if you ever wanted to change the privileges related to the role, you would need to do it only once, only in one place. And the changes with sync up to all the users after that. So if you were to take out answer privilege than all the users will be updated at once instead of you having to do it one by one. So Rawls introduced more of a convenience. 33. Grant And Revoke: So now let's talk about grant and revoke. The most important thing to know about grant and revoke before starting to use them is that only the database administrator or owner of the database object can grant revoke privileges on a database object. Now below you can see both formats for grant and revoke. Let's go over the grant first. We have the name of the privilege right after the grant reserved keyword. And that just means what kind of privilege it is, system or object. So this is where you will write, select, or create or execute or anything else that you would want. And if you would like to assign multiple, then just separate them by commas. So that's the first one. Next is the database object. So right after the on-reserve keyword, and this is where a database object that you are allowing these privileges on goes. So this could be a table view or any other database object. So that's where you put the next parameter. So grant that type of privilege on than database object. And the next one as whether you're granting this to a specific user in the database are saying it is available to all users of the database by saying public. Or if it is a role which we went over, role being a collection of privileges. So you're saying grant the name of the privilege on database object then too. And it could be a user, specific user public, which means it's going to be available to everyone in the database or a role_name. Now the last part says with grant option, and this is an optional part. But what it means is this, if you include it as part of your query, then whomever you grant the permissions to will be able to grant those permissions to others into database. And even if you revoke those permissions later for the same user, it will not revoked the permissions of the people whom that person granted to. In the meantime, you will need to be very careful with this one. Now let's talk about revoke. Its simpler and it operates the same way. After revoke reserved keyword, you have the name of the privilege, and that is where it is a system or an object privilege than after the on-reserve keyword comes a database object which is either a table or view, et cetera. And lastly, after the from reserved keyword, we have to provide who you are revoking this privilege from to only catch with revoke is that you cannot revoke a privilege if you have not originally granted it. Another catch is that a user might be granted the same privilege from different other users and until both of those users don't revoke the privilege than the user being revoked can continue on using those privileges. 34. Introduction To Section: So now a quick introduction to this new section before we dive in other word Claire, about SQL query language and SQL Server, how datastore within SQL Server, DDL and DSL commands. We're ready to see them being used in more practical and real life applications. In this section, we have multiple real-life scenarios where there is a business requirement and the solution is to use SQL language to query data from SQL Server. As time goes on, more and more scenarios will be added to this section. So please do check back to learn more. 35. Problem 1: Right, so it's time for us to work on some problems that are going to be more realistic. The first problem is as such. It says in the employee table we have male and female employees and they all have associated job titles. Your job is to write a query which only shows job titles for male employees that are not taken by female employees, then do the opposite and show all job titles for female employees that are not taken by male employees. Has go to SQL Server Management Studio. And let's start writing some code. Okay, so first, let's go ahead and get familiar with this table. So the table we're talking about is called employee. And is this guy right here? Let's select everything from it. So I'm going to say select everything from human resources, DOD, that's a schema. And then I'm gonna say employee. And then we're going to select everything from it. So it's a pretty big table. Now what I'm gonna do is I'm only going to get the jobs. And here are the job titles. All the job titles are here. So what I wanna do is only C job titles for males. And then I want to have a separate query that shows me job titles for females and I just want to unique job titles. So once again, we're only want to grab one column, so that's the jump title. And first I'm going to grab everything for males. So we also have the gender on it right here. So I'm going to say where, where gender equals M, that's going to give us all job titles for males. Now the problem is that we have a lot of duplicate data. I'm going to bring this up a bit. So I have a lot of duplicate data. So production, taxation, WC 60, we have marketing specialists listed three times, Research and Development listed a few times. So what I'm gonna do is I'm only going to get distinct values from it, distinct rows. And in order to do that, we use the distinct keyword. And let me run this again. So we see now we don't have any of the duplicates. So this is all the male job titles. Now I'm gonna get old female job titles. Ok. So these are only held by females, old Egypt titles. Now what it says is we want to grab all of the jobs that are held by males and not by females. And then we want to reverse it. So grab all the job titles that are held by females and not by males. So what I'm going to do is use the keyword, reserved keyword except because remember that's what it would allow us to do. So I'm going to say except, and what this is going to do is it's going to grab the first table and compare it to the second one. And whatever day it match on, it's going to be excluded. So this will allow us to only see job titles that are associated with gender male and non-gender female. So let's run this real quick. And here we see all the job titles that are only held by males and not by females. And if we want to reverse this logic, all we would do is just reverse F and M. And now we will see older job titles there are held by females and not by Mao's. So I'm gonna go ahead and do a quick check. I'm going to say select everything from human resources dot employee. And then I'm going to say where job title equals. And we're going to grab one of these job titles. I'm going to say, let's say information, serve as manager. This is only supposed to have rows that say gender is female. And it only has one row and the gender is in fact the email. Now let's test. Just do a spot checking just to make sure that we got the right data and you can check all of them. But I'm just going to do is going to be checking random ones. So a marketing manager, this is only supposed to be held by males and it only has one row associated with it. And the gender is in fact mail. So this is how we get something that is only in one table and not in the other. We use the except. And this was a more realistic situation in which we are using these reserved keywords to get the desired outcome. 36. Problem 2: Alright, so we're on to problem number two in the special offer table, we have different special offers with start date and end date. These days show when the offer will be active and when it will be terminated. Write a query to find all the special offers that are beginning between 0.5.0, 120119302011. Make sure that your query includes all offers between the specified date range down to a millisecond. So let's see what we can do. First, let's check out the special offers table. So I will say select everything from sales dot special offer. It's an Sales schema because he can see, and here we have some special offers. Drag it up a bit. So you can see it has start date and end date. And let me actually order by start date. And we'll we'll see that now the start dates are ordered. So it goes from 2011 oh, five or 1201403 or so 20140331. So these are the date ranges. What we want is we want from 20110501. So this guy all the way from 2011930. So let's count and we here we are. 2011 ends here. And would then 201105 is the only one. So we basically wanted these rows, 12123456. We want these rows to be returned. So let's go ahead and write a query that does just that. I will say where start date is greater than or equals to 20110501. My insert was on, that's why the character was keep getting removed, but basically 2011051. And then we're going to say end start date is less than or equal to 2011930. So let's check this out. And we're getting 1-2-3, 4-5-6. So we're getting these back. So it seems like this is the query that they would want us, but they specified down to the millisecond. So what if we had a new offer that basically went up until 930 and then it has some times attached to it. Let's actually go ahead and do that the way you can actually generate scripts, By the way, you can do it through SQL Server Management Studio. So you can go to sales special offer, which is right here, right-click and then say script table as. And so to clipboard that got generated for you. And then you can basically paste it. So this is going to generate an insert statement for you. Remember the table insert into reserved keywords, all the columns and then the value. So for the values, we're going to have to put some fake stuff in there for now. For descriptions, i'm gonna put so first of all, yeah, description is the first one. So that's great. I'm gonna say new. For the discount. I'm going to say 0, doesn't really matter. So the type is new product. Once again, I can put it as anything else you want category, let's say reseller, just the way it has it here. And now, start, end date. Let's just skip it for now. Min and Max, I'm going to say 00. And for this one there is actually a function that can generate width for us, which is just a unique ID that it also has. I mean, we can give it anything else, but this is going to generate a proper type of data for us. But once again, this is not the one we care about. What we care about is the start and end date. So I don't really worry about the data that you put in. Instead of nu, you can say old instead of new product. You can say anything else you want. We're just kinda faking a row to see what it would actually do in case of a real information entered. And then for modified date, as you've already guessed, we're going to use good day. Not that the casing matters, but I would like to keep it consistent. So start date, I'm gonna say 2011. It's actually copy one of these pasted here. And then we're going to modify it afterwards. Okay, so 2011, and then I'm gonna say all nine, and then I will say 30. So this is right at the break and then I'm going to change the time, so down to the millisecond, right? So we're going to actually ask sometimes on it. So I ate, ate, and let's just say eight, right? Just some random time, anything that's above all, 930. And then up until is going to run, this doesn't matter, but we're just gonna put a date in this case. So let's just say 1005. Really, the only thing we care about is this. And then let's insert this row and one row affected. Let's go back and rerun. So I'm going to actually remove this part. So we have more space. I'm going to run this. And here we see we don't have that row. We should have grabbed everything down to 20110930 and we didn't grab it. So if I grab everything once again, and if I just skip this one, but I grab everything else, we see that we actually do have the new product right here. And that's where it is. But we didn't grab it even though it falls into a range that we're required to grab. So the thing that we can do and a good practice that is often known is to, first of all, grab the next day, the entire next day. So specify one day over and then just say less than which will cover the previous days, everything. So 938th and anytime within that day. So if I run this, you see that now the new product is here. You see that? So we actually grabbed it and start, start as 0930080808. But once again, so this is 17th, right? So special offer ID is 17. But let me go back to what we had before, which was nine, are just nine and then 30. And if we do that and you see it's not there. So we need to specify 1001 so that the entire day of all 930 ETH is grabbed down to a millisecond, which is what is happening right now. Okay? So this is it. And once again, all these scripts are going to be available in GitHub. So this is problem two. 37. Problem 3: Now problem three, in the product review table, we have product or products that are associated with product number. So dash B19, C19, dash M. Your job is to find all the product reviews associated with dimension product number in the product review table and update the comments so it appends. Thanks again. At the end of each associated product review, let's see what we can do about this. So first thing first, let's get familiar with the tables. So select everything, select everything from production product, Thus the product table into production schema. Okay, so we see it has Product ID and it doesn't have any reviews. Okay, let's look here, production product, production product review. Okay, so let's take a look at that. Copy this and just add review next to it. So we see here, so we see product review has ids and it is a primary key, right? So we can also expand the table and look at the columns. It says Product id is pk stands for primary key, Product id is F. K stands for foreign key. So basically what happens is the product and product review have a relationship. And the comment that we're talking about is right here. So you can see the comments. It's pretty long comment and can at least copy one. And it goes on and on and on, but it doesn't have. So basically what we're saying is that we gotta find the associated product number with the review. And thanks again at the end. So first of all, let's do this. Let's find that product number. So we're going to copy this and we're going to add a where next to it. So we're going to say where the product number, whereas the product number there you go, equals and then the product number that they gave US. So dash b, 909, dash M, i believe it was. And here we go. We got the product. So the product ID is 709. Grab that comment this so that we just have it in front of us. But we don't really neat SQL to understand what it means. It's just a remark that we put down. And now let's actually connect product and product review together. Let's see how that goes. So select, I'm going to say everything for now from production, dot-product, like our final query is not going to have star on it, but these are just throw-away queries that we can just right to get ourselves familiar with and help us with the final query. So select everything from production product. And then I'm gonna say inner join because I want everything that matches up. And then production, dot-product review. So product review. And I'm going to say product is p, the table alias and product review is p bar. And I'm gonna jo join pee on product ideas. We've found out product id is a primary key and PR product key. And let's see what does gives us back. So this is gonna give us the reviews associated with each product since there is only, there were only, let's see, let's count how many products there where or product reviews there where. So there where for product reviews, which is why we're inner joining, we're only gonna find rows that match up. So there's only four reviews. We're only going to match for products or for product reviews. And the rest of the products will be dropped because they didn't have reviews because we're doing an inner join. If we didn't left join, it would bring everything, as you remember, help. So they would bring the non matches plus the matches. So let's see what we can do to find the product review that's associated with the product number S, O dash P90, nine dash M. So as you can see, what we can do here is just add. So this is a select. Let's actually break this down a bit. So it says from table, and this is our table list, right? Then the next thing we can do, as you remember, we could just add row logic, right? So say from all those rows, just grab the product number and actually aren't even after right there, right? Because we already have the logic here. So we say where we just need the product number, column name. And if we do this, see this is the one that they were talking about. So there we're talking about the Prague number associated with that as the mountain bike socks M. So that also has a review. It has also has a comment. So what we can do, I mean, he can ask the customer is to what columns they want or whatever their need is. I mean, they can ask you to generate a report out of this. This could be part of some application that they're going to insert it in. So it depends on which comms they wanna see. But for now, we're just going to say product ID because that is important to see. We're going to say product review. So product review ID. And then we're gonna say comments. For now, that's all we want to see. Everything else to us is useless. And Product ID. That's the reason, by the way, the reason why that broke is because as I, as I mentioned, we now need to use column aliases. If we don't, it says ambiguous column name Product ID because it exists on both tables, so it doesn't know which one to grab it from, going to say grab it from P. So product, grab product review ID from PR. And then, and we can even keep a consistent, we can say PR product ID, PR product review ID, and then comments from PR. So product review once again. So let's grab that. And we see that we have everything here and we can also have product. So the product number also, I think that's also important. Just I mentioned that. So product number here. So now this will grab the whole thing they wanted to. So if we actually try to see what this comment says, See it says it doesn't have Thanks again after it. So what we need to do is we need to basically this product that we just selected, we need to be able to update the common for it. Now how do we update such row? We will learn how to update one row, so we say updates set. But actually what we can also do is we can have an update with the join and we're going to learn how to do that right now. So we're going to say Update. And we're gonna say PR, does, doesn't exist, which is the column alias, but it's going to exist just in a minute. So this is a separate query by two. I am just writing above it. And I'm going to say set. So I can actually do like that. So better visibility. Spacing doesn't matter. Once again, it's just up to how you want it to look like. And PR comments. And I'm going to say concat. And concat PR comments with space. And then thanks again, which is what they wanted us to do. So thanks once again. Okay. Now we're gonna say from production product review. But actually, instead of me writing that query down here, we can actually use the one that we have up here. So we can grab this whole thing, bring it up here, and now see how the PR was underlined red. That's because it doesn't know what we're talking about. But now we bring it in and now it knows what we're talking about. It is talking about product review. So once again, let's go over this query. So what happened here? This is an update statement just like we had before. So we could have just not had a join. Join is really the new thing because before we were all you went over updates and we would basically have to have it a bit different. So if we didn't have a join, would say update a certain table like production dot-product, and then set, Let's say a product, a number if we wanted to update a right to nothing. And then we'll say where product ID equals one, like this would be a sample simple update statement, but this doesn't have any joints and you sometimes need joins with an update because the table did you're updating, which is this might be dependent on another tables information, which is what we have in our case. So in our case, we wanted to update the comments. But in order to update the comments, we need information from another table that is related to our current table that we're updating. So we're saying update and then the column alias. And then we say set reserved keyword. And then we basically set all the fields that I want to update. In this case, we're saying that the comments on the product review are going to equal to its comments, it's own comments plus a space plus saying thanks again. That's exactly what their requirement was. Then we'll say from where we want to grab the source from. And that's our table was again, the table doesn't have that information. So we then inner join this table to whatever we want and we can have conditions or we cannot have condition. And it doesn't matter in this case we need it to because we wanted to grab the product number that is associated with the product review. But the product review doesn't have the product number. So what we're going to do right now is we're going to run it. And we have two things here. It doesn't matter, I'm just going to leave that, leave it here as well. We can actually just go above here and just run our product review. And you see here that we still have four, so nothing changed there. But the ones that we want was this. And now we have the updated comment. Let's check it out. And at the end it says Thanks again. Okay, so this is how we do an update with a join. So once again, we have update our table alias. If previously I said a column alias, that definitely wasn't mistake. I meant a table alleys if I said that. But so basically you say update, you say table alias, and then you have set reserved keyword, then you have the columns that you want to update. You could have, you could have said anything that he could have said, hey, it doesn't matter. It's just that in this case, the requirement was add thanks again to the existing comments, which is what I did. Then from the, you know, the entire result set. And in this case, the results set is our product, a product from production schema joined to product review. So from this entire thing, we're updating the comments. And once again, the reason why we need to join is because product review doesn't have the product number. But we were only given this piece of information, so we had to backtrack ourselves where to go to product number, which was specified to us on the product table. Then grab the product ID of that product table, and then match that up to product review table. And once we got the product review associated with that product ID, that's basically the product number that we're looking for. And then we grab that comments on that product review and change it. And that's basically what happened here. 38. Problem 4: So problem four, we need a query that generates a result set representing employees full employment history should include the following information with the given column names, employees, full name as fullname, employees, gender as employee gender, employee's department as department name, employee's department start date as start on, and lastly, their last day as ended on. So let's go to SQL Server Management Studio and write these scripts. So the first thing for LA know all the table. So I'm gonna give them to you and then you can pause the video and can write it yourself. But we're first going to look at the tables before we're writing a query. So it's person, human resources, employee. And here we have all the information, right? We don't have the forest name or last name though for the full name. So in order to get that, we need to go to the person. And the person has a business entity ID, which we can connect to Employee Business Entity ID, and that'll give us the first name and last name. So then the gender is also on the employee. So we can grab that from there. And employee department and the history of where they worked that we need to grab from, select everything from, and then Human Resources, that employee department history. So now this employee department history has a business entity ID which is connected to the same employee business entity ID. They employ his department history. That can repeat. So you see 1616. So that means that the employee work than warrant department and went to another department and there's dates that we can grab. Now, start and end date are on the employment history, but we don't really know what this number means. So in order to get that number, to have a meaning, we need to go and grab that number from employee. Actually, it's not employee is just Department. So these are the tables that we need and I can do the joins yourself and then you can come back and we can do the rest together or you can just see a watch me do it. So first I'm gonna select star and then I'm going to change it later. That's actually one technique I use. I don't really care about which columns I am going to choose. I'm going to make sure that the older joins are properly done. So first we're going to say human resources employee. And then I'm gonna give it E as the table alias. Then inner join two person. So to a person, person and p, and I'm going to say P dot Business Entity ID equals e dot biz insanity id. So here we already have the full name. We can actually grab the full name. So how we do it, let's use concat. I'm gonna say concat, P firstName and P lastName. We have the employees full name. Now. We can just say full name here, just the way they wanted it in the report. So fullname is right here. And then the next thing is the gender. We can also grab that. We can grab it by e dot. So actually we need a comma here and come here and say gender. And this one is going to be employee gender. So that's what they wanted to be named. So thats how are we going to name it? Now? We need the department name and then the date's. Okay. So the next table, as I said, is human resources employee department history. So what I'm gonna do is I'm gonna go inner join. And I'm going to grab that table. I'm going to name it e, d, h on ETH. Business entity ID is going to join two employee business entity ID. That's going to give us the history. And now we can actually join this. Lastly, inner join this to human resources department and call it D and D dot farm and ID is going to join to the employee department histories. Department id. You see that? And now D will have the department name, which is just the name. And we can just put it in square brackets because it's obviously Name is a reserved keyword. And we're going to name this department. Department name. Okay, so we have this so far and then start and end date from ETH. So the start date, we needed to name it as started on. Started on. And then they want it ETH end date to be as ended on. Okay. So this is something that a customer or somebody from within the company might ask you to send them either a query like this or give them a report that all has all these datasets on it with the proper lean named headers. So this is just some that might be request data from a customer or from within but request like these are going to come through pretty often if you're a full-stack developer, lets say a customer might ask you this, or a developer might ask you this. You might be interested in generating a report for something that you're working on. So this is just some that is good to know that you can do multi joins and you just need to know the tables. The problem is, you need to know the tables and what they mean and how they relate to each other. And that is not a technical problem, that's a business problems. So you need to understand the business, maybe talk to one of the bins analysts or people that know about the business, whoever it is, in whichever old they are, you need to talk to them, understand the business, and then after you do that, then you can use your SQL technical skills to perform joins and add column aliases. Table, table alias is to be able to put it all together because in that case, data makes sense. So right now I'm gonna run this statement and we see fullname, employee gender, and department name started on and ended on. In this case, if it's null, I guess is they're still present, but one of them, you know, they they ended at this date and it might be repeated somewhere else. So this is the entire record. 39. Problem 5: Okay, so onto Problem number five, in the person table, we have records, information about individuals who have an attribute called title. These people in the person table are also employees with job titles. The associated table giving you that information is called Employee. We've already seen that many people can have the same job title. Your job is to count how many people in each job title have titles and how many don't. Let's see what's going on. So first of all, let's get familiar with the tables. Select everything from Person, person, select everything. Okay, so we get everything and then here's the title. So some people have a title, some people don't. And all that a title says is whether to call them miss mister, you know, stuff like that. So that's the title. Now. The employee table in the human resources. So the table, Human Resources dot employee just shows more information about the person we've already seen. It has marital status, gender, birthday, and also has a jump title. So many people can have the same job title. And we can see that by actually doing a quick count. So we can come here and just say, for each job title, right? In here, count the rows. So make a group based on the job title. And then within that count everything. So, and then we have to move by job title. Remember, so let's actually formatted a bit so we can see it better. So we can see what's going on better. And so selecting from the employees table and a grouping by job titles. So this will say, you know, there's some job titles that are held only by one person. There's some job titles held by 17 different people in the employee table. Some 14, right. So there's different numbers. Now what we're trying to do is we're trying to say, okay, so some people have in different job titles. We're also trying to see how many people in each job title also have titles. And we also want to see the amount that don't have a job title. So we're trying to do is we're trying to see how many people within each Job Title have titles also and how many don't. So let's say we choose one of them like marketing specialists. So we're trying to see within, Let's say marketing specialists, when we get to that, out of those five people that are marketing specialists, how many have titles and how many don't. And we're trying to do it for all of these, right? So if we were to try to do it manually, we could do something like this and grab the marketing specialist and say, okay, well, let's just see. How many? So we've seen it's five people, right? So let's just see who those five people are from human resources, that employee. And then we say where job title equals marketing specialist. So that will give us five rows. And it has all these IDs, right? So these are the IDs. Let's now go to the person. Person. And we will say where the business and any ID in the list that we just grabbed. And we are going to separate them by commas. And all we're trying to do is we're trying to get the employees that are in the person table. And here they are. So let's look at the titles. So only one of them has a title. Okay, so now we've ran through what we wanted to do manually. Now what we are trying to do is write a script that does it automatically for all of these columns and just gives us a count back like a report. So let's get on that. Now. I'm going to remove this. And let's start writing the code. So I'm going to say select everything from. And once again as my technique that I use, I just don't care what the columns are yet until the whole thing is complete. And then what I'm going to do is I'm actually going to say from and then I'm going to say from a subquery result. So then we're going to use subqueries. And inside of the subquery, I'm going to, so the inner query, I'm going to do a quick count and you'll see what I mean in just a minute. So I'm going to say select everything from once again, just to set my query up. And then person, person. And then I'm gonna give that p as the table alias. And I'm gonna say enter join two human resources employee. Just two. Formatted real quick. And then IE, Business Entity ID equals P, business entity ID. Okay, so now we've joined these two tables together. What I'm going to do now is I'm going to use some column logic. I'm going to say case when P tot title. So title. So person title. When p dot title is null, then just say one. Else, say 0. And I'm going to end it. And I'm going to give this column, calculated column a name. I'm going to say no title. Okay? And then I'm going to use another case statement. Case. And then inside that, I'm going to say when p dot title is not null, then return one. Otherwise return 0. We'll actually need a comma here. Thus the reason why it's underlined red. Okay, let's keep going. So right now we're saying select. And then we have a case statement. Two case statements that is, and what we're going to do is we're going to say here, instead of no title, we're gonna say with title. Okay? So this, as you can see, is an uncorrelated query. So we can run this without having the outer query. So let's actually run this and see what happens when we run it. What it does is it shows us the, we can actually let me do this. So it's actually less confusing because all we're saying is ones and zeros. So I'm going to say P dot Business Entity ID. And then you'll see what I mean. Okay, so if we run this, see, we see for each person that's also an employee because we're doing an inner join. We're saying Who has a title also and who does not have a title. So this person does have a title, this person or does not have a title. This person has a title. That's all we're doing inside of this subquery. Okay? So this is basically, so this is basically going to be a virtual table. And it is a virtual table that is created by this subquery, right? And this is what we're selecting from now. So imagine like we have a table that has a business entity, no tidal, wind tidal. And no title means that it doesn't have a title would titled means it has a title. So that's what we created first. Sometimes the problem might be complex and have multiple factors to it. So what you want to do is break it down into simpler problems and then solve those problems one by one. So what we did here is we broke it down. We said let's just count who has a title over who does not have a title. That's what we did first. And now we're going to take care of the part where we're saying how many people within a given job title have a title and who does not have a title. Okay, so first thing first, let's actually give this sub-query in name. And in order to do that, we're going to use the as reserved keyword and then just give it the name. Let's say name it counted. Okay? And then what I'm going to do is I'm going to grab this, which is the table name in our case. And I'm going to do the following. So first of all, I don't need business entity ID. What I'm going to actually select from it is the job title. Okay. And it's not from the person is from the employee. So job title. Okay. I'll just select it one more time. It's going to say that this job title has no title. Just jump to this job title has a title, the designer, right? So in order for us to clarify it even further, let's actually order by job title II job title. And then we'll see much clearly what this actually does. So you see, now we get the actual counts. So therefore people who hold application specialist job title, and all four of them don't have a title. There is a case. For example, let's see here, there are 1234. Actually. Let's count the marketing specialists. So they all looked alike. That's why. So marketing specialist 12345. So all of them are selected and we have one with no title. Another one would no title. Another one would know Title One of the people who holds a marketing specialist title has a title. And then the last one does not have a title. So it's 1234. So it's basically four of them out of five total, don't have a title, right? So now we get that information. Now it's all about putting it in a way where it's more readable. So that's what we're going to do in our outer query. So what I'm going to say is I'm going to say counted. And you can, you don't have to put square brackets around, but you can do it either way. So we can just do it like this as well. But let's actually, so you can say dot and then that's going to give you the names of columns that you have available on here, right? So no title with title. So it treats it like a virtual table. So dot job title. And then guess what? We're going to group them by. So we want, so if we have it here, right, we have 12345. What do we wanna do? So what did we do really, without even writing any code, we took one, said plus one, plus one, plus one, so that's four, and then we only had one here. So what does that remind you of an aggregate function, right? So that's the sum. We're going to say some counted. And then we're going to say no title. And then we're going to say some counted. And we're going to say width title. So then we can give it another name here, so more readable, say with title. And notice we have a space here and it doesn't have to be, but it just more intuitive, more readable. So then this one is going to be no title. And that's it. But one thing we forgot, we have to have a goodbye because we're grouping by a job title. And here we're going to say counted job titles, so we're grouping by the job title. Okay, so this is our entire query. Let me just push it down. Here we go. So first of all, we're counting individual individuals who has the title, who does not. And then we are joining to that table. And it's almost you can treat this as a table then we just select from and just do a Group II. But in this case we don't have a table. So we created first and we selected from that table, and then we give it an alias. And from that alias we say, okay, grew by job title. And within each job title, select the ones who have a title and select the ones who don't have a title and some of both. So this is basically our query. And one other thing is that we can't use orderBy in this case. So in this case this is becoming a virtual table. We can do that to kind of like make sense of data and select it individually. But when we're basically joining to it as a virtual table, we can't use orderBy and it's just one of the rules. So we're gonna take this out. And now let's actually run the query because we don't need orderBy anyways where he, we don't really care about the order here. We can order outside of it. So we can order by and we can order by outside of it such as this, so counted job title, so that we're ordering by job title. But let's run this now. And here we go. So as you can see, this is giving us exactly what we asked it to do. So see here we have summarized data, only one job title and then that job title gives us the actual sum instead of saying account, account in another account is not repeating rows. Those rows have been aggregated into only one row. And then here's where we get the summary right. So let's go to marketing specialist and remember marketing specialists. So we had four ones and only 11 on the width title. So this is exactly what we're seeing here and hasn't been summarized over here. So this is basically we just counted the individuals within a job title, who has a title and who does not have a title. And this is what we get. 40. Problem 6: So now we're onto ProM six. As you might have already noticed, we have a table called person and a table called employee. Some individuals who are recorded in the person table are also recorded in the employee table, but not all, your task is to find the amount of people who are only in-person table, but not in the employee table and vice versa. Please provide two separate queries. So now let's see what we can do. Let's actually count everything that is in the person table. So person, person, let's count it. It's 19,972 rows. It's actually put that here. Okay. And then let's select and see how many there are in the employee table. So human resources, that employee, Ok. So that's 290. And now what we want to see is how many there are in person that are not in the employee as well. And we want to do it with an Xist this time just so we can have different ways of approaching it. So I'm going to say Select, once again, select count from person to person, which is just basically selecting the person from the person's schema. And I'm going to say where not exists, right? So we're saying select all the person rose, only the ones where they do not exist in this following list. And well, not enlist But in this query. So I'm going to say select null from, right, because we don't really care about returning the columns. And I say Human Resources dot employee ie. And then I'm going to say where e dot Business Entity ID equals p dot Business Entity ID. So this says that we only want the rows from a person that do not match the Business Entity ID of employee. And let's run this. And it counted 19,682. So let's put that here. 19,880 to 682, sorry, 19682. Okay, so now if we just do a quick calculation, we'll see that. Let's see. So 1997 to minus 196, a2 is 290. So it seems like I'm not really sure yet, right. But the number, it seems like that all employees might be matching up with the person, but obviously not all person rows are in employee. So we can actually do the opposite. So we can say select, count and then human resources employee. And then we just change that. And I don't have to, but just to make it clear, person, person p, and this one is good to go. We don't really need to change that. So let's take this out. So now we're saying select all the employees from employee table that are not in the person table. Let's see what the count is. And C at 0, which means that all of the employees are in the person that just verified it for us. So there are no employee rows that do not match up with the person, but there are 19,682 rows into person. Do not match up with the employee. Well, we can do so now we have the scripts. These are the scripts that we needed to provide. Now what we can do is we can actually do a quick test. Instead of counting, let's actually get the p business. And a DID because these are going to be the ones that should not exist in the employee. And then what we can do is just do a quick select statements, select everything from human resources dot employee, where the Business Entity ID is as such. And this does not exist as we have predicted. And this one shouldn't exist as well. Or any one of these shouldn't exist, right? So that's basically it. Now we know that these are the numbers, right? This is basically how you can do it to see if one table exists than another or if one doesn't exist in another. And you can also use other things. You have other tools, as I've mentioned, you can have set operations as well. Certain ones that fit the situation, or you can use the n or exists. So there are different tools and I'm trying to kind of throw different tools at similar problems just so that you can see it from different angles. But this is it for now. 41. Problem 7: Okay, we're onto prom seven Now. Please look up all the records in person table that are not in the customer table. Then out of those records filter out the ones which have suffix of JR. dot. So junior, once you find these records, please insert all of them into the customer table under ID 934 and territory ID1, please provide one dynamic query in case more records need to be moved in the future. So let's see. So once again, our style was to get familiar with the tables before we write the query. So let's actually go to these tables. First one is going to be the Store table. And it's Sales dot store. That's in the Sales schema. And then we're going to go to the sales, sales territory. Okay, so these two tables, now they want us to insert people that we find under store ID 934. So I'm going to say where business entity ID, which is going to be the store ID. This is basically the primary key on the store, so 934. Let's see what that is. So that's just simple bike store. Okay, sounds good. And then we have sales territory. So sales territory, where territory ID is one. Let's see what that's about. And that just says north-west. Okay. So these are just some of the criteria that they want us to insert the customers under. So okay, so we have that part. Now let's actually go to the table that we already know about person, that person, and then select everything from sales, DOD, customer. So it's the customer table that we're looking for. So this is, these are the customers and then these are the people, the individuals. So what we're looking for to do is we are looking to find all of the records and person table that are not in the customer table. So we can say where not exists. And then we can say select null from sales dot customer. So we already looked at that table. And then give both of them table aliases. And say where p dot business I, Entity ID, so the person primary key. And then let's look at the customer. What's the primary key on that? And that has customer ID and a person ID. So basically, what is the meaning behind the Customer ID and personality is that a person can be a customer. So Person is a foreign key and customer is a primary key. So there can be different customers. Obviously, each one has to be unique, but there can be three different customer IDs, but the same person ID, what that would mean is that the same person was a customer three times. So it could just go into another store and into another territory. I can get into another into the same store, into the same territory. Now, you know as well, but it's going to be a different customer. So it's just basically how many times did you go to certain store in certain areas and made a purchase as a person. And then once you do that, you come in as a customer under a customer ID. So we're doing right now is we're saying we're going to join to the c_ dot Person ID. What this is going to do is it's going to look at the customer table. And it's going to say, OK, well, from the customers, from all those customers, who are the people that join to the Business Entity ID, which is the primary key of the person. So these two are basically identical. And this way we're saying what person in the person table does not exist in the customer. Let's run this and here we have 853 rows and we can actually count these. So let's run a quick count, flips it around and 53. So select from person where not exists. So where the person does not exist. And then select null from sales customer where it joins to the primary key. So get rows from a person that are not able to be joined to the customer table. That's what we're saying. And in this case, these are the ones that we got. Let's grab them once again. Ok, so these are the ones. Now we can actually check it out. We can go and grab this bit insanity ID and go to the customer and say where person ID is one. And we will see that it does not exist. If we say where it does exist, we'll actually see that we're getting a lot more BEC we're actually getting let me see. So we're getting 19 thousand and showed the number 19,119 in this case, get old. The person is older people from the person table where they exist in the customer. So in this case, let's actually grab one of them and I'll show you that it in fact does exist. So this is going to select all the rows from the person that do exist and the customer. So we said that person one does not exist, right? Okay. Well, in this case, 291. Should exist c. So now we have a query that gets all of the, I'm just going to say not. So it's gonna get all the rows from the person table that don't exist in the customer. Then the next condition was two out of these, filter out the ones which have a suffix of junior. So all we have to do is just have another statement in R where so we use the end, right? So as we say and, and then we say suffix. So suffix, the suffix is present on the person, so we can just say P dot suffix. And then that equals to junior. Ok, so in this case, let's see what it gives us back. So we have a lot less now. Okay, so now these are the people that we were looking for. So these are the ones that we need to actually insert. So then says once you find these records, please insert all of them into the customer table underscore id 3934 and territory ID one. So once again, let's go to the customer. So let's say Everything from sales dot customer, and let's actually see what's under that right now. So where the store ID equals nine, 3-4, and territory ID equals one. So right now there are two things. There are actually two rows under that, right? But after we perform our insert, once we write our insert, we have to insert all of these rows there. So we have 123456789. So it's nine plus two. So we expect 11 rows at the end. So we can say expect 11 at the end. And we'll see how that goes. Now this is the interesting part. This is where we have to write our insert statement. So our insert statement is not going to be as simple as you know how we had it before insert into and then the tables. So like person, that person. And then we had the column names like firstName and I'm gonna put all the column names, but we had all the column names than values reserved keyword, and then actually, you know, the values that we want to put into those columns. So in this case we can't do that because it should be a dynamic query. So we have to somehow get this data and we have to put it inside of the customer. So in, right now that's exactly what we're going to do. So we already have this part ready. So let's actually grabbed that. I'm actually going to leave it here and then grab just a copy of it. Let's put it here. And let us write the insert statement. And then we're going to put it all together. So I'm going to say, let's actually grab the insert statement for customer. So we can say right-click table as and then say insert two and then clipboard. Come here. And Let's take out all of this. So the first part is going to be pretty much the same insert into name of the table that I want to insert into. And then all of the, you know, all of the column names. So that part stays the same. Basically what happens is we can have an insert and then instead of us providing specific values, we have a select statement with joins and whereClause or anything else that has columns specifically selected in the select statement, which is the next step that we're going to do. And it grabs all of those rows and puts them into the customer table. It doesn't automatically. So this is a dynamic query. So right now what we need to provide for the insert statement is a person ID Store ID territory id, row GUID and modified, modified date. Rogue would once again, it's one of those unique IDs to identify the row width. It's just another unique identifier. You don't have to worry about that, but it's just the new ID method. So once we say new ideas is going to generate that for us. So that's what we're going to use for that. So we have that part, but let's just start from the person ID. So what is the person ID? The person ID is p dot Business Entity ID because that's what it is, right? Once we, let's just select as one more time these out. So you see that the insanity ID is the person ID basically because that's the foreign key inside of the customer table. So we're going to say just that. And then we're going to say the next column that needs to be Store ID. So the store ID, they told us that it needs to be 934, correct? That was our requirements. So we're gonna say 934. So 934. And then I'm going to say the next thing, territory, territoriality was one, correct. So that's that. And then Rog width is going to be new ID. That's the method that's going to automatically generate a unique ID for us and modified date is just get date. We're just getting the current date. Okay, so this should insert. We just select it without inserting. This is what is going to be generated. See the Id, this, this, and then all these are generated automatically for us. So if later on in the person ID or in the Customer ID, we basically get more data that will be different or anything gets shifted around or more people get inserted. This is a dynamic query that will grab them in the future as well. So now I'm going to go ahead and I'm going to run the insert, this along with the insert. So once again, nine rows affected nods Actually go ahead. I'm gonna take this out. Let's go ahead and go here and expect it 1234567891011. And as you can see, we have 11 rows that we put in. We can look at the Day 2019. And these were from the existing ones. So now we have everything that we want it right away. You might look at this and say, well, where does the account number come? And the account number is just the computer field. If you actually look right here, it's not then that we can insert is just the computer field based on whatever logic that have, right? So what we needed to insert where the following person ID story, the territory ID, roguish, and modified date. So this is how you do an insert with a select. 42. Problem 8: Now problem mate, we need to delete data from Person.all email address table that email records that need to be deleted are for individuals associated to those emails who have a suffix of JR. please provide one dynamic query which will perform the operation described above. We might have more cases like these in the future. So as mentioned, the query has to be dynamic. So now let's take a look at it. First. Let's get used to these tables. So first, let's go to the person's email address and see what that looks like. So email addresses, but incentive DID email address and has been centered ideas connected to the, by the way, the way you can check it is by right-clicking on the table, which I will label you want clicking on design, then right-clicking. And you can then say relationships. And within that you can go here and inside of that click dot, dot, dot, and you'll see that it says Primary key table is the person and foreign key table is email address. You can do that and you can also click on the keys and just look at what is going on here. But that's another way of looking at what the relationship is between the tables. More in-depth if it doesn't say it in the name of the key, so close that and then come back. And so these are the email addresses and then select from person, person, DOD person. Alright. And we have that already. So what we're trying to do is the following. We need to find the email records that need to be deleted for individuals associated to those emails who have a suffix of junior. So let's go ahead and do that. Let's first write a select statement that shows us who these people are. So I'm going to say select everything from email address. So first I'll do you have that right here? Just kinda format a real quick so it's easier to look at. And then I'm going to say inner join, two person dot Person p. And then this is going to be e-mail address. And right now we're going to say P business entity ID equals a dot Business Entity ID for each person we haven't associating. Alright, let's see what does, does. First of all, this works, but then we want the one where we have emails were associated with people with a suffix of junior. Ok. So these are the ones we have. Let's see how many there are. So in total we have 33 of them. So these are the people that need to be deleted. Okay? So the problem here is that we only have seen how to delete, right? So we said delete from a certain table. And then we would say where and we provide whatever we needed to. But in this case, there is nowhere, right. I mean, we kind of need to provide an actual list of people. So there's other ways you can do within the wear. But what we're going to do is we're going to do a delete that allows us to attach a select that might or might not have a join or a where clause on it. That's exactly what we're going to do right now. We have the people that need to be deleted, 33 of them. And what we need to do is write a delete statement now. So I will write it down below here. I will say delete. So delete. And then I will say from person dot e-mail addresses. Yay. And then we're gonna put the EI here so it has to be delete and then, you know, the table alias. So that in and then from person email address. And then we have the LA is there. And then inner join. And then we will say either I don't even have to write it right, because we already have it right here. So look at that. This is exactly what we need to do. So it says delete from Person.new email addresses, EA, which is the table alias, then we're saying, okay, but what are we trying to delete? So when we're trying to delete, is the email addresses that are inner join to the person that match up with the person are associated with that person. But not only that out of doors, associated people will want the ones who have a suffix of junior, grab those, and delete those. So let's run this. And 33 rows affected. Let's run this again and this should not return anything. Actually, we can do a count and we should expect 0. And he would go nothing. So we have deleted everything and this should not return rows obviously, so no rows. So this is how you do a delete that you can provide it a select and where. So this allows you more flexibility as to what you actually want to delete. Because sometimes you might want to delete something from some table that needs information from some other table or tables. And that's where you're going to join and do all this kind of flexibility. You have this flexibility in your delete statements. 43. Farewell: I said guys, so the course is over and I hope you have enjoyed it, just a couple of last words. So I hope that you have learned about TC SQL, SQL Server and any misconceptions that you might have had before. I hope that you have learned what is happening under dilute when SQL Server stores data. I hope you've learned about data definition language commands, data manipulation language commands and data control language commands and hopefully the practical SQL scenarios and how to use SQL to solve them helped you to understand how to put SQL into more practical use. And another thing is that more examples will be added to the practical SQL scenario section as time goes on. So always check back for more updates. So thank you for watching. Good luck in your future SQL endeavors. And if you've enjoyed the course, leave a positive review. If you don't like anything about the course, I would appreciate it if you can message it to me. And I'm always up to improving the course and making it better for students. So if there's any complaints you didn't like anything or something can be improved. Definitely send me a message and I'll definitely improve it for you and for other students as well. Take care. And I'll see you in the next course.