Hello and welcome to my course: C Master Class from Zero to Hero with SQL Server. My name is Frank Le. I am a senior software developer with more than 20 years of experience working with SQL. C is the language of data, and it's one of the most important skills you need to master in today's world. Whether you want to work with data science, business intelligence, web development, or any other field that involves data, SQL will help you get there.

In this course, I will teach you everything you need to know about SQL, from the basics to the advanced topics. You will learn how to create and manage databases, how to insert, update, and delete data, and how to query data using different criteria and functions. How to sort and group data and how to join data from multiple tables. You will also learn how to use variables, conditional statements, loops, subqueries in SQL, and how to create temporary tables, table variables, how to create and use stored procedures and functions, and how to write and execute dynamic SQL.

But this is not just a theoretical course; this is a practical course where you will get to apply your SQL skills to real-life scenarios. I have designed the course based on my own experience working with SQL in various projects over the past two decades. You will get to solve real-world problems using SQL inside many assignments in each one of these sections. In this course, you will learn SQL with SQL Server, one of the most popular and powerful database platforms in the world. SQL Server is used by many leading companies and organizations, and it offers many features and tools for data storage, analysis, and management. SQL Server is also the number one database platform in terms of job opportunities, according to LinkedIn jobs at the moment of recording this video. SQL Server has more than 990,000 open jobs in the United States alone, which is more than double the second position, which is MySQL.

I have organized this course in a logical and progressive way so that you can build your SQL knowledge step by step. Each section of this course is based on the previous one so that you can use what you learned in a section in the next one. You will also get many assignments to practice your skills. By the end of this course, you will have a solid understanding of SQL. You will be able to use SQL to perform various tasks on data, and most importantly, you will be very confident to put SQL on your resume. Whether you want to pursue a career in data science, business intelligence, or web development, or any other field that involves working with data, this course will help you achieve your goals and become proficient in SQL. Feel free to check out the landing page and the free preview video lectures. I hope to see you in the course.

So what is SQL? SQL stands for Structured Query Language. It is for managing data in structured databases, also called relational databases. A structured database stores data, obviously, in a structured format, typically in tables. A table is like an Excel spreadsheet with columns representing attributes and rows representing records. A database can store one or many tables like this. When data is stored across many tables, managing data in them seems to be an overwhelming task. This is where SQL comes to rescue. SQL is a language that a database user can use to talk to the relational database. To define tables and their relationships, they can also use SQL to insert data, update data, delete data, and query data in the relational database. So, in a nutshell, SQL is a computer language that a user uses to manage structured data that are stored in tables of a relational database.

If all of these concepts are still very overwhelming, don't worry. If you just follow along in this course and take one step at a time, by the end of the course, you will know exactly what SQL is, and you will be able to use it to manage data in a relational database.

In this video, let's cover DBMS, database, and tables. Just as one or many tables are organized into a relational database, multiple databases are organized into a DBMS. DBMS stands for Database Management System. It's a piece of software that manages one or more databases. So one of the important things I would like you to understand is the logical relationship between all of these elements. So a DBMS can have one or more databases, and a database can have one or more tables.

Now that you know the logical relationship between these elements, another thing I would like you to understand is that DBMS stands for Database Management System. It's a piece of software that helps to manage one or more databases. Now, where is SQL in this picture? When a database user tries to manage data inside a database, the user cannot actually execute a query directly in the database. The user actually has to talk to the DBMS with the SQL language, and then this SQL language will be executed by the DBMS software. After all, only the DBMS is a software that can execute computer languages. A database is simply a collection of data, whereas a database management system is a piece of software that can interact with database users. After you watch this video, you need to understand the relationship between these elements, and you also need to understand that it is the DBMS who is interpreting the SQL language and executing the language against a specific database. Okay, that's the end of this video. I'll see you in the next one.

From the previous video, you understand that the SQL language can only be executed by a DBMS, right, a database management system. So we need to choose a DBMS to install on our work computer so that we can learn and practice SQL. However, there are many popular database management systems, such as Microsoft SQL Server, MySQL, Oracle, or DB2, so on and so forth. And each one of the DBMS has their own flavor of SQL. But don't worry, there is a great similarity between them. As long as you learn SQL against one of these DBMS, you will be able to easily transfer your knowledge of SQL and use SQL in other DBMS. But in this course, my choice of DBMS is Microsoft SQL Server. It is one of the most popular DBMS, especially for business users, and also because of my personal experience having been using SQL Server for more than 20 years for developing reports, creating softwares, APIs. So therefore, in this video, let's try to install Microsoft SQL Server.

First, let's try to install Microsoft SQL Server, which is the database management system. We can go to Google and search for SQL Server download, and then we can see that here is SQL Server downloads, and click on it. And if you scroll down a little bit, you can see this Developer Edition and the Express Edition. I highly recommend you to install the Developer Edition, which is a full-featured, free edition licensed for use as a development and test database in a non-production environment, right? So this is full-featured, whereas the Express is not actually full-featured; it's not the best for learning SQL. So let's download the Developer Edition. Once you learn this, you can basically simply transfer all your knowledge onto the professional or Enterprise edition of SQL Server, or you can use your knowledge on Azure, the on-cloud edition. So let's download the Developer Edition now. And it's downloaded, and we can double-click on it and click on yes to allow this app to make changes to my computer. We're not going to do anything special, so we are not going to use the custom; let's just go with basic. So selecting the basic installation and then click on accept and just choose your location. We can accept the default location and click on install. Of course, you can also change to a different location on your computer. So this is going to actually download and install the SQL Server application, and it's going to take a long time. I'm going to pause the recording of this video, and after it's installed, I'll come back.

Okay, the installation of SQL Server 2022 Developer Edition is completed successfully, and you can see the instance name, which is right here, and it gives you the connection string. Don't worry about what connection string means, and then it tells you which user is the default administrator. It's basically the user you use to log in with, and then you use that login to install the SQL Server Developer Edition, so that user becomes the default administrator. Okay, and then it tells you some default folders. Another thing we need to install is SQL Server Management Studio. A database management system is usually a program or application that runs without a user interface. To prove to you, I can open up a task manager, and you can see all of the programs that are currently running, and most of them actually don't have a user interface. Here, I can type in SQL, and you can see SQL Server or Windows NT. So this is Microsoft SQL Server, which is a database management system. It is currently running and takes a lot of memory, you can see, but you cannot actually see it on the taskbar, why? Because it doesn't actually have a user interface. So therefore, you need something else to connect to the DBMS in order to perform tasks against the database management system. So for SQL Server, we need SSMS, and it stands for SQL Server Management Studio. You can see that it has a message; it says that an integrated environment for accessing, configuring, managing, and administrating and developing all components for SQL Server, right? So this one provides a UI, and through that UI, you as a SQL Developer can access any SQL Server that you want to access. So let's click on this button and try to install it. So it takes you to download SQL Server Management Studio, and then you could scroll down, and it says free download for SQL Server Management Studio 19.2. So this is the latest at this moment, and today is December 12, 2023. So by the time that you watch this video, a SQL Server Management Studio may become 20 something, may become 30 something, something. It doesn't really matter; the basics of using SQL Server Management Studio hasn't changed for decades. Let's try to download the latest version now. So click on the link, and then it's trying to download the SQL Server Management Studio now.

Okay, that took about a minute. Let's go to the folder, and we can see SQL Server Management Studio setup, and just double-click on it to install the Management Studio. We can accept the default location and click on install. Of course, you can change any folder you want. So click on the install button and click on yes to allow this app to make changes to my computer, and this is also going to take a while. Now the SQL Server Management Studio is installed, and it says setup completed, and let's click on the close button, and now we are back at the SQL Server 2022 Developer Edition installation dialog. Since we have already installed both SQL Server itself and SQL Server Management Studio, we can click on this close button, and ask me, are you sure you want to access? Just click on yes. All right, that's everything I want to cover in this video. In the next video, we're going to use SQL Server Management Studio to connect to our newly installed Microsoft SQL Server to learn about how to execute SQL statements in SQL Server Management Studio. I will see you in the next one.

In this video, let's cover some basics of SQL Server Management Studio. In the previous video, we have installed Microsoft SQL Server, which is the DBMS itself, and we also installed SQL Server Management Studio, and we covered the reason why we do this. This is because SQL Server is an application that doesn't have a UI; therefore, we need something—in this case, it's SQL Server Management Studio—that has a UI so that we can use it to connect to Microsoft SQL Server, and then we execute SQL commands or SQL statements through SQL Management Studio. But first of all, I don't want you to be confused to think that these two are always a pair, and SQL Server Management Studio can only connect to this one that we just installed. Instead, the SQL Server Management Studio, once it's installed on your computer, you can use it to connect to any SQL Server, right? It doesn't have to be the local instance of SQL Server; it can be a SQL Server on the cloud; it can be a SQL Server in some other company's network; it can be a SQL Server on another computer in your home network; it can be any SQL Server that is accessible through the network. That's the first thing I need you to understand: that a SQL Server Management Studio can connect to any SQL Server. All right. Now let's actually type in SQL, and then SQL Server Management Studio shows up right here. So let's click on SQL Server Management Studio 19, and now it shows up here. It is a blank screen at the beginning because it's not connected to any SQL Server. So in order to connect to a SQL Server, you can click on this connect button over here, and then click on database engine, right? So database engine is basically referring to the DBMS application. So click on it, then this dialog shows up. It may show up when you launch SQL Server Management Studio, right? This dialog may show up directly, but this is how you actually launch it if it's not showing up directly. Right-click on this connect button and then click on database engine. Okay, so now it asks for server name. So here you can use an IP address; you can use a computer name or DNS. In my case here, I'm using localhost, and then you can choose authentication as Windows authentication. So basically, I'm just saying I want to connect to the SQL Server on my local computer, and I want to use Windows authentication, which means that I'm using Windows to authenticate myself, right? Currently, I'm logged in as a Windows user, and SQL Server is now depending on Windows to authenticate me as a database user, right? Because I, as the user, which is this user here, I used this user to install SQL Server, and I'm considered as the default administrator. So therefore, I have an account to connect to my own SQL Server on my own computer. So now if I click on connect, I should be able to connect. You see that I am connected. Let me make this a little bit bigger so that you can see better. So I'm going to go to tools, and then go to options, and then I'm going to go to fonts and colors. Here, I'm going to change the size to, let's say, 16, and then click okay. So it's this part is not changing, so I'm going to do it again. Now, instead of choosing text editor, I'm going to choose environment, and you can see that automatically it's sized and it's pretty small. So I am going to choose a font, and then I'm going to change to 16 as well. So click on okay. This may look a little bit weird, but it's probably good for presentation. Okay, so the first thing we need to pay attention here is that I'm using SQL Management Studio. I am connected to one SQL Server. This is my local SQL Server. You can see that it says SQL Server 16, which is the version number of SQL Server 2022, and then it also tells me that I'm logged in as this user. And as I mentioned that this SQL Server Management Studio, this tool, you can use it to connect to multiple SQL Server instances. To show you that, I can also click on this connect again and then select database engine, and then I am going to connect to another SQL Server. This is a SQL Server on Azure that I have myself. Here, I cannot use Windows authentication anymore because it's not installed in the same computer as my SQL Server Management Studio. So therefore, I can I can choose to use SQL Server authentication, and because I have already created this login and password on Azure, my own account, I can use this account to log in, and then I click on connect. Now you can see that we are using one SQL Server Management Studio connecting to two different SQL Servers. Yeah, don't worry about what's inside just yet. At this point, I just want you to firmly understand that a SQL Server Management Studio can connect to multiple SQL Servers. Okay, so now let's don't worry about this Azure SQL Server. Let's disconnect, and I also want to disconnect from the localhost, which is my local SQL Server, because there are different ways to connect to the same SQL Server DBMS. I want to show you a couple of different ways before I disconnect this. You can see that I have frank-laptop, so this is my computer name. I can try to connect by using my computer name. So if I do this again, connect, database engine, and then I type, I type in frank-laptop again, I choose Windows authentication, and click on connect. You see, I am connected again. This is the same SQL Server instance; it's not anything different. Another way to connect to the local instance of SQL Server database management system is is to use localhost. All right, let me demonstrate that. So let me disconnect by clicking on this disconnect button, and then I can click on this, this little button here or here. So let me try this time, try to click on this little plug button, and you can see the same thing shows up. This time, instead of using my computer name, I'm going to say local, but not just local, I have a bracket, right? So bracket local bracket, and and then I'm again using Windows authentication, and then I click on connect. Now I am connected to my local SQL Server. The next thing I want to show you is let's go to this diagram here, and a couple videos ago, we have one DBMS, right, one database management system manages different databases, right? So here, this is the instance of the DBMS system, which is our SQL Server, right? I want to show you that inside the SQL Server, there are multiple, or at least there can be multiple databases. So let's open it up, and let's open this up, and you can see there, there's no databases, right? Because we haven't created any database, but that's actually wrong. SQL Server is a complicated system. It by itself has some system databases. So here, there's a folder; open it up; see, it already has databases showing. This picture is to help you to understand that one SQL Server, one DBMS, can manage multiple databases. Here, you can see that it's managing four different system databases. And again, let's go back to this diagram here. One database can have multiple tables. So let's open up one of them; let's choose the master database; open it up; you can see tables. And if we open up the tables folder again, there are no tables because we haven't created our own table inside the master database, but it by itself has some system tables, and you can see these system tables exist right over here. Here, before I finish this video, I want you to understand that one SQL Server, one DBMS, can have multiple databases. I can manage multiple databases, and one database can manage multiple tables. Okay, that's everything I want to show you in this video. If you have any questions, please let me know. If not, I'll see you in the next one.

In the previous video, we used the SQL Server Management Studio to connect to different databases. In this video, let's continue with SQL Server Management Studio basics, and we are going to cover how to execute SQL statements. So let's go back to SQL Server Management Studio here. In order to execute SQL statements, we need to click on this button over here, right? You can also go to file, new, and then query with current connection, but let's choose the basic one. So click on this new query, and you can see, hovered mouse over this tab over here, you see that this query, it's sort of associated with the database that we chose over here, right? So you can see this is connected to the master database. You can switch to different databases here, but don't actually worry about that in this video. I want you to focus on this area, which is the text editor. Remember, I changed the font size of the text editor in the previous video so that I can do my presentation better in this video. So let's type in some SQL statements. Most of the SQL queries start with select, right? So we're selecting something or pulling something from the database. Instead of actually pulling any actual data from our system databases, I'm going to say select hello world. So this is a SQL statement; it doesn't actually return any extra data from the database; it's returning a string that are hardcoded over here. So in order to execute this, there are a few ways. One is to click on this execute button, and you can see that you can use a keyboard shortcut, which is F5. So let's try to use this button first; use my mouse to click on it. You can see that the result shows up in the result tab over here, and it says hello world. Once this result tab shows up, I can use control+R, so ctrl+R on my keyboard, to make it disappear. If

Wanted to show up. I can do control-R again to make it show up, so let us make it disappear first. Now let's let's try the second way to execute the command. So if I just press on the F5 key again on my keyboard, now you can see the result again. If you think that what I just did is I I pressed on control-R to make this show up, let me hide it again. Okay, and then let me change this to "hello R," and then I press on F5. Now you can see it says "hello rows." Okay, so this is the second way to execute the command.

Another thing about executing commands or SQL statements is about highlighting or selection. So let's say that we instead of having just one select statement or SQL statement, I have multiple. So now if I just press on F5 or let's click on the execute button, I get all three results right in different sections. If I just want to execute, for example, the middle one, how do I do it? I can highlight it, and then I click on the execute button, or I can highlight and press on F5.

So far you have learned actually four different things. Let me summarize for you: First thing is, in order to run a SQL statement, you need to open up a new query window. Click on this, open up new query window. Second thing is, to execute a statement, we can click on the execute button or press on F5, which is keyboard shortcuts. And the third thing is, in order to execute a specific SQL statement inside a bunch of SQL statements, right, we can highlight it and then either click on the button or press on F5, and we get the result for the highlighted one. And then the last thing that we learn is to use control-R to hide or make the result show up. Okay, those are the four things we learned so far.

I want to add another thing to this video, which is the results. So results can be in different formats. In this case, it's in a grid. You can see this is a column, this is a row, and we can actually change that. Let's go to the query menu over here, and then here there's a sub menu item here. There's "Results to Grid," which is the default one, which is the current one, right? And if we change to "Results to Text" and then we run again, let's say we select this one and run it. Now you can see that the result is in text format. All right, what if I execute all of them? You can see that there's three different sections; all of them are in text. And of course, there's another way to render the result, which is "Render to File." So let's select that and click on this. Now it asks me me to select a place where the file is saved. So let's say that this is just "results," and then click on "Save." Then I can go to the folder, and then you can see that it saved into a report. I can open it up with, let's say I open it up with Notepad, and now you can see the result in text format, but it's saved in the file. So again, we learned five different things. I summarized four different things for you and then added the last one, which is the result format. Uh, the way to set it is go to go to menu and then go to the results. Let me change it back to "Results to Grid." Okay, that's everything I want to cover in this video, and if you have any questions, let me know. If not, I will see you in the next one.

In this section of this course, we're going to learn about Data Definition Language; it's DDL. DDL is for defining the database and all of its objects within the database. So in this first video of the section, we are going to learn about the SQL statement for creating a database. So let's go to our SQL Management Studio, and we're going to connect to our local database. So click on this plug button; we're going to connect to our local database. Select the Windows Authentication, click on the Connect button. Now you can see that we don't actually have any user-defined database yet. Let's click on "New Query," and by default this system database is selected. Remember, I showed you that there are four different system databases. The default database is the master database, but don't worry about what is the master database; at least in this lesson, it is just that this query window has connected to a specified database; that's all. Don't worry about why we need to select this database instead of other databases; just use the default master database. Now we can learn about the first SQL statement in this course, which is "CREATE DATABASE." Okay, you can see that SQL Server Management Studio automatically color-codes this with the blue color. So if I made a mistake, for example, instead of "CREATE," I misspell, so then you can see the red squiggly line here, right? And in fact, it also adds another red squiggly line in the "database" itself because it doesn't recognize the combination of the statement. But as soon as I change it back, the red squiggly lines underneath the two words disappeared, but it shows a little red squiggly line right here. Well, that's because it's expecting something after this, which is the name of the database. Let's call our first database just "first_database," and and then we can execute this. Remember how to execute a SQL statement? We can just simply click on the execute button. I normally just press F5, and then in the messages tab it tells me that the command completed successfully. Now if I come to the databases folder here and click on this refresh button, you can see our "first_database" right here.

Now that we learn how to create a database, we might as well learn how to delete the database. The statement to delete the database is not "DELETE DATABASE"; it is actually "DROP DATABASE," and then followed by the name of the database. So we're going to say "first_database." By the way, notice that I'm writing SQL statements in uppercase letters. This is not a requirement, but usually SQL statements are written in uppercase. You don't have to do it; you can do it with lowercase, uppercase, with mixed upper or lower case; it's still going to work. SQL statements are not actually case-sensitive, but because in all of the DBGs documentations of SQL everybody writes SQL statements in uppercase, so it makes sense to write SQL statements in uppercase. Now I have two SQL statements. In order to execute the second one, I'm going to highlight it and then press on F5, and now you can see that it says "Commands completed successfully" again. Now if I go to the databases folder and click on this refresh button, now you can see the database is gone. So we have learned to create and delete a database with very simple SQL statements; it's just called "CREATE DATABASE" followed by the name and "DROP DATABASE" followed by the name.

Before I finish this video, I want to show you how to actually create a database with the UI. Right-click on the databases folder and then click on "New Database." Now there's so many different options, but for now don't worry about anything other than the database name itself. So let's again call it "first_database" and click on "OK." Now the same thing happened; we see our "first_database" created right here. And if we want to delete it, we simply right-click again and just click on this "Delete" menu item, and of course it's going to warn us that we are going to delete a database object. Are we sure? Click on "OK." Now the database is gone. Another thing I want to show you before I finish this video is, well, let's create a new database again, so "first_database." Now let's pay attention to this path, and let's right-click, "Copy," and click on "OK." Now the "first_database" is created. Now let's go to this folder here, and we are inside this folder, and you can see that inside the folder we have the "first_database" file and a whole bunch of other files. The reason why I want to show you this is that any database in Windows or any operating system actually exists as files. It's just that we have our database management system, in this case SQL Server, that is running efficiently, managing those files efficiently. So if you create your software based on your own files, that's fine, but you may not achieve the same efficiency, same storage management as the DBMS itself. Okay, that's everything I want to cover in this video. Just remember these two statements; those are the first two statements you learned in this course. If you have any questions, let me know. If not, I'll see you in the next one.

In this video, let's learn the SQL statement to create a table. Previously we mentioned that a table is like an Excel spreadsheet; it has its rows and its columns. Now the rows are representing the data. When we try to create a table, we are not concerned about the data yet; we are only concerned about the structure of the table. Now what determines the structure of the table? It is those columns that determine the structure of the table. Therefore, when we try to create a table, we need to specify the columns. And know that this course is all about learning SQL; we are not going to actually cover database design in this course, but I still need to mention a little bit, which is when we try to design a table on paper, we use the table name followed by the column names to represent the table. So for example, here I have "person" as the table name, and then "name" and "age" as the column names. Therefore, the SQL statement that we use to create a table also follows the same kind of representation. So let's go into SQL Server Management Studio. If "CREATE DATABASE" are the keywords for creating a database, then what will be the keywords for creating a table? Obviously, it's going to be "CREATE TABLE" followed by the table name. We're going to use the same example, so we're going to create a "person" table, and then the "person" table has two columns; it's going to have a "name" column and then the "age" column. Now you can see that there's red squiggly lines, meaning that there's syntax errors, right? So there's problems with this representation of the table, why? Because in the database we need to specify the data type. Right here, the data type needs to be specified right after the column name. Here, the name of the person should be a string; we're going to use characters to represent the string, and the length of the character is going to be 50, and then "age" is going to be a number. So here I can say "INT," which represents integer. Now you can see the red squiggly line is gone. Another thing I want to mention is, typically a table may have many columns, so when we write SQL statements to create a table, typically we want to break this into different lines. So this is what we usually do; we break from here and then have each column occupy a line so that it looks really really clear that we have these number of columns and what what are the types of columns. Another thing I want to mention before we actually create a table is that we have our SQL Management Studio connected to a SQL server, which is a database management system. Remember, one database management system can have many databases, and inside each database we can have one or many tables. So when we create a table, we have to be very clear about inside which database are we going to create a table. Here I want to create the "person" table inside my "first_database," but from this dropdown list you can see that I I'm currently targeting the "master" database, which is a system database, so this is incorrect. So there are two different ways to specify which database I'm going to use to create a table. One way is to use the UI, and I can just open up the dropdown and select the database. Now when I try to run this SQL statement by clicking on the execute button, it tells me that "Commands completed successfully," and if we open up the "first_database," open up the "Tables" folder, highlight the "Tables" folder and click on the refresh button here, you can see the "person" table is created. And if we open up this, there's a "Columns" folder; we open up the "Columns" folder, we can see the "name" and "age" created for us. Now there's a different way to specify which database we're going to use. So imagine I'm going to create another table that is called "person_one." If I'm currently on the "master" database, if I just run the execute statement, "person_one" is going to be created inside the "master" database, but I want to create the "person_one" table inside the "first_database." So instead of using the SQL Management UI, I can use a SQL statement, which is the "USE" statement. So I'm going to say "USE," and then followed by the name of the database. So I'm going to say "first_database." When I do that and then I click on the execute execute button, even though on the UI the current selected database is the "master" database, this "USE" SQL statement is going to switch databases first and then run the "CREATE TABLE" SQL statement. Okay, so let's try to do that. Click on the execute button. Now now you can see "Commands completed successfully," and then you pay attention to this dropdown; it's already switched to the "first_database," right? Because our "USE" statement helped us to switch to the database first and then run this SQL statement. So let's open up and just make sure that we actually created the table. Refresh, and now "person_one" shows up right here. Again, we learned how to create a table. Now let's try to delete those tables. So if "DROP DATABASE" is the SQL command or SQL statement to delete the database, then what is the SQL statement meant to delete a table? You guessed right; it's "DROP TABLE" followed by the table name. So I'm going to say "DROP TABLE"; I'm intentionally using lowercase letters to show you that SQL statements are not actually case-sensitive, and then followed by the table name, so there's "person_one," and I don't have to use uppercase; I'm just going to use lowercase, like I said, even the name is not actually case-sensitive. Okay, so I highlighted because I have multiple statements; I don't want to create the table again. I'm just highlighting this. At the same time, I make sure that I'm using the correct database, and then I press on F5, so "Commands completed successfully." Now I highlight the "Tables" folder, refresh it, and I don't see the "person_one" table anymore. I can do the same thing for the "person" table, but this time I want to use the UI to delete the table. Just right-click on the table and and then click on "Delete," and confirm that we want to delete it. Click on "OK." Now the table is deleted. Before I finish the video, I also want to show you how to create a table with the SQL Management Studio itself, with the UI itself. So right-click on the "Tables" folder and click on the "New" menu item and select "Table." Now you can see that by default the name of the table is "Table_1." We can change the table name later, but now we can specify the column name and then the data type, right? So we're going to use the same thing, "CHAR(50)," and don't worry about this just yet, and we're going to specify "age" as the second column, and the type is "INT." Now I can just click on this "Save" button, and it asks for the name of the table. I can change it to "person" and click on "OK." Now the table is actually created, and let's refresh this "Tables" folder, and we have the "person" table created. Now you go ahead and try to delete this with a SQL statement, so this is exercise for you. I'm going to wait until you finish. Hopefully you have done it: just "DROP TABLE" followed by the name of the table. So this time I want to use uppercase, like I said, usually SQL statements are written in uppercase, so I'm going to do control-shift-U; immediately all of the letters are changed to uppercase, and then I'm going to highlight the statement that I want to execute and then press on F5. Now "Commands completed successfully." If I highlight the "Tables" folder and click on the refresh button, I don't see the table anymore; the table is deleted, or we can say the table is dropped. Okay, that's everything I want to cover in this video. I'll see you in the next one.

Starting from this video, we're going to learn about data types while creating tables. For a market management system, for a market, for example, a supermarket, we need at least a product table, and the product table contains several columns. For example, we need "category," like product category, whether it's vegetable or fruit or beverage or meat, and then the product name, the product description, the weight of the product, the price, quantity, launch date. So let's say we have these columns, and in this video we are going to cover the first three columns, and the data type of these three columns are string types. Let's jump into SQL Management Studio. First of all, let's create a database. So let's select SQL Server itself and click on "New Query." So by default, the "master" database is selected. Here's a question for you: We want to create a database, and the name of the database is "Market_Management." Can you try to create it yourself? Okay, I'm going to do it now, and we're going to type in "CREATE DATABASE," and we're going to name the database as "Market_Management." I'm going to press on F5, so the database is created now, right here. Okay, the next exercise or the next question for you is that notice that currently the "master" database is the currently selected database. We want to switch to the newly created database, which is the "Market_Management" database, with a SQL statement. How do you do that? Let's use the "USE" statement; switch the database to "Market_Management," and press on F5. You can see that it is switched to "Market_Management" right here. Now I can type in the SQL command "CREATE TABLE" or SQL statement "CREATE TABLE," and I'm going to create a table that is called "Products." You can name it as singular or plural; I like to name it as plural, so "Products" instead of "Product," and now we are going to only worry about those three different columns. Let's create the first column, which is the category. There are different ways to create the category column. In this video, we are going to use a string type. This doesn't make too much sense later on; you will know why, and we're going to change the way we specified the category in the "Products" table, but in this video we're talking about data types, right? The string types, so it makes sense to use a string type for the category. So the name of the column is going to be called "category," and I want to use three characters to represent the category of the product, and for that purpose I can use the data type "CHAR," right? "CHAR" means character, and for "CHAR" we always need to specify the length of the string, right? So if I say "3," that means I'm allocating three characters of space for the category column. Now let's move on to the next one, which is the name of of the product. Here I want the name to be no more than 50 characters. In order to represent that, instead of "CHAR," I use "VARCHAR," so variable characters. So this means that it's a character, but it has variable length, but if I specify "50" here, it means that the length of the name cannot exceed 50, but if it's less than or equal to 50, that's fine. Now for the description, I also want to use "VARCHAR," and the description should be less than or equal to 200 characters. Now we are good, and if I press on F5, the "Products" table is created. So let's take a look at the "Products" table. Right here, it shows up right here. Let's populate some data. I know that I'm going to teach you how to insert data into tables later, but an easier way to do that is to use SQL Management Studio. Right-click on the table and then click on "Edit Top 200 Rows." So I'm going to show me the top rows so that I can edit it. First of all, you see this "NULL." What this means is that there's no data, or data is missing, or unknown data. When data is not provided, it shows "NULL." It's just telling us that this column doesn't have any data; that is what "NULL" means. And next thing is, remember that "category" has only three characters. Let's say we have a product for meat, so I want to use "M" to represent the category, and I'm allowed to do that. So don't worry about this exclamation mark. Let's move on to the "name" column. So let's say here we are storing product "beef," and here I'm going to say "AAA beef," and now I click on the next row. Now you can see the exclamation mark disappeared.

That means the data is actually inserted into the table. Now, here's a special thing I want you to pay attention to. We mentioned that the category column's type is char(3), but now it's only storing two characters. It looks like the char type has variable length; that's not actually true. Why do I say so? So, if I click into "beef" and then I copy it out and paste it over here, you can see that I have 1, 2, 3, four characters, even though we specified that the name column stores up to 50, it still only occupies four characters. If I click into "category" and then I copy it out, I paste it over here; it looks like I only have two characters, but there is a space behind it. So, let's say that I change this to "M" to represent meat, and then I click out. Now, the category is again saved in the database. Now, if I click into here, you already see that there are spaces behind it. You cannot delete that space. I can try to delete the space; when I click it out and then click back in, you can see the space is right there. If I paste that "M" here, you can see one, two, and three; there's still three characters. So, what that tells you is that if you want to use char, the column always stores the same number of characters that you specified for the column. So, in our case, "category" always stores three characters, and if you try to store less than three, the server adds spaces at the end so that it occupies three characters.

So now the question is, should you use char or should you use varchar? Each one of them has pros and cons. The char characters occupy more space than it needs to, and varchar saves space. However, because it saves space, the length of the column is unpredictable, so therefore, the performance of the column is not as good as char. So, it's for you to determine whether you want to use char or varchar. In our case, if we determine that "category" can only be three characters—cannot be less than three characters, it's always three characters—then it makes sense to use char as the data type. So, in that case, we enforce the rule that "category" has always three characters. So, therefore, a category like "meat" it will be "MEA"; a category of "vegetable" will be "VEG"; and let's insert something like "cabbage," and maybe I'm going to say "California cabbage," and for "fruit" will be "FRU," and maybe "watermelon," and this is maybe "Mexican watermelon." Okay, so in this case, I arbitrarily enforce that category can only be three characters—cannot be more, cannot be less. In this case, I want to use char, but for the name or descriptions, the length itself varies; therefore, I'm using varchar, another data type for string.

I want to mention is nvarchar. So, nvarchar, nvarchar, it just means that the column supports Unicode. So, Unicode is good for storing other languages like Chinese, Japanese, whereas if you just use English, you can get away without the n here, right? So, if your table needs to deal with, uh, let's say our product table needs to deal with products from other countries that the language is only supported by Unicode, then you have to use nvarchar for the name here, perhaps not the category. Category can always be, uh, used English letters, but the name you want it to support Unicode, and perhaps description you also wanted to support Unicode. Unicode to databases, it just occupies more space; it doubles the space of char. So, when I say nchar(3), this occupies the same space as char(6), right, because it doubles the storage space. Okay, in this video you learned about char and varchar; you learn about unique differences between them, and that's everything I want to cover in this video. If you have any questions, let me know; if not, I'll see you in the next one.

In this video, let's continue to cover data types while creating this products table. In this video, we are going to be concerned about the weight column and the price column. So, previously we created these three columns. So, in order to add more columns to this, there are different ways. The simplest way is to drop the table and recreate it. Since we haven't actually added any data into it, we can simply drop the table. So, drop table products, and then I'm highlighting it, and price on at five. So, now the table is dropped; I should be able to recreate it again. So, we have category, name, and description. Next one we will be dealing with is weight. So, I'm going to say weight, that's the name, and then I specify the data type here. I want the weight to be in kilos. Most of the time, weight is not a whole number, right? So, for example, beef can be 1.2 kilo, 1.3; therefore, we cannot use a whole number, and we can only use data types like float or real. So, we can choose one. The difference between float and real is just that the range of number is different. Real data type is a single-precision floating point, and it uses four bytes of storage, whereas the float data type is a double-precision floating point that uses eight bytes of storage. Therefore, real data type stores a smaller range of numbers compared with the float data type. Both of them are storing numbers in approximate value; that means the data stored here is not accurate. But if you're not concerned about accuracy, for example, weight, I don't mind that I miss a few milligrams, so I can use float. And if I highlight this and execute this create table statement again, then the table is recreated. So, to enter the data, I right-click on the table and then click on "Edit Top 200 Rows." Now, I can re-enter the data again. This is going to be meat, so "MEA," and the name is "beef," and description, just some run-up description, and the weight, I can give it, let's say I have 323 kilograms, 567. And when I click out of the row, the data is stored here. You can see that 567 is stored here accurately. So, accuracy is not a problem when we only have like two or three decimal places. When we have a lot of decimal places, then accuracy is a problem; it's just because the way that the data is stored in SQL Server for float type or a real type is approximate value, right? So, the rounding is not accurate; therefore, it's not very good for storing financial, for example, uh, storing money when accuracy is very, very important. But if you're dealing with like scientific data, astronomical data, for example, uh, the distance between planets, it doesn't really matter if you're off by a few millimeters. So, in that case, we can use float or real. Although it's not very accurate, it's very efficient; the performance of calculation, storage is higher than other types of numbers. And what is the other type of number that I'm talking about? Let's move on to the next column, which is the price column. We're storing money value; the name of the column is price, and I want to, instead of using float or real, I want to use decimal. Decimal number is accurate, and you have to specify two values, so A and B. What A stands for is the total number of digits that are allowed in this column. Let's say totally I need 18 digits, but within the 18 digits, I only want two decimal places. So, therefore, for example, um, if I have a dollar amount 3,567,120.3 and 56, so this can be stored in the decimal because the total digits is less than 18 digits, and we have two digits as decimal places. Let's try to create this table again. Let's drop the table first. So, highlight this and click on the execute. By the way, this becomes green because I used -- to comment that out. So, this becomes a comment; when this becomes a comment, I cannot execute it anymore. So, if I highlight the whole thing, I cannot execute it; it still says "Commands completed successfully," but there's nothing is executed; this is just a comment, right? So, for example, I execute this create table statement; now the table is created. If I highlight this again and click on this, it says "Commands completed successfully," but the table is not actually dropped because we are not actually executing the actual drop table statement. If you want to execute the drop table statement, just highlight the statement, although this is in comment, I can still execute it like this. Now, why do I comment out the drop table statement here? Because SQL statements like drop or delete, they are very dangerous, so I want to comment them out in case that I forgot to highlight the statement that I want to execute, and I just press on F5 accidentally, the drop table statement will not be executed, right? So, this is very, very important when you are debugging live database, production database, for example. So, I always comment out dangerous statements like drop database, drop table, delete from table, which you're going to learn later. Okay, so now let's go to the table, right-click on it, and add "Edit Top 200 Rows" again. I have this again; I'm entering "MEA" and "beef," and uh, description is "Triple A beef," uh, weight is I don't know, 560 kg, 34, and price, I'm going to copy this over; instead of 56, let's say 56.78, and then I'm going to copy this over to the price column, and when I click out, you can see that it says, you know, "this row was successfully committed to the database," but it's not actually, um, pulling data because it did a rounding; that's why it has this problem. What we can do is we can close it, and then we can try to "Edit Top 200 Rows" again. Now, let's take a look at this; it did a rounding for us automatically. So, we entered 56.78, and because we specified the data to have only two decimal places, so therefore, it rounded it to 57 instead of 56; therefore, you can see that decimal data type is very accurate. If you have a price column that needs to store money or any number that needs accuracy, um, decimal is recommended; float and real data type is not recommended. But keep in mind that decimal has a slower performance comparing with float and real, so it really depends on the requirement of your project. Also, in SQL Server, there is a money data type; I actually don't recommend you to use money data type; sometimes the money data type also has rounding problems. Ironically, I personally prefer the decimal data type for something like "Price," right, which needs accuracy. Okay, that's everything I want to cover in this video; I will see you in the next one.

In this video, let's continue talking about data types, and we are going to talk about date types while we are creating this last column, which is "launch date" column. Let's come back to our SQL Management Studio here, and let's open up a new query. So, right-click on the database that we're going to run SQL statement against, click on "New Query." Currently, I have the products table over here, and I have these columns. I want to drop the table, but before I drop it, I need to get the create table statement. So, one way to generate it is to use SQL Management Studio. I right-click on the table, and then here is the menu for "Script Table As," and then I can choose "Create To," and then I can choose "Clipboard." When I choose "Clipboard," it's going to generate the create table statement inside my clipboard. So, I'm going to click on it; everything is in the clipboard; I can paste it over here. Don't worry about all of this; let's delete it. You can see that we have the create table statements generated for me; I don't have to remember it. And don't worry about this "ON PRIMARY"; we can delete it, and for now, also don't worry about this "NULL" thing here; I can actually delete them. Now that I have the create table statement, I can drop the table. Before we go any further, you notice that this, there's this square bracket generated by SQL Server Management Studio when you define any database objects, including table here; uh, we can use square brackets. I can point out one use case for the square bracket; for example, if I want my column to be with a space in it, uh, let's say we have a column that is called "gross amount," and the type is float, you can see that it is allowed to have a column name that is a space in between, but if you don't use a square bracket, it's going to show you the, the red squiggly line; it's not going to be allowed, right? So, if you have something like this in the column name, you have to use a square bracket. Now, let's talk about date data types. There are, generally speaking, three different date data types. The first one is date, and that's what we're going to use for the launch date. Okay, so "launch date" data type is date. What date represents is only the date portion of the date, right? It doesn't include the time. To make it clear for you, uh, I can come up with another column; let's call it "launch time," and now I can use another data type, which is time. So, time is the opposite of date, right? It has the time portion without a date part. Now, let's say I have another column that is called "expiry date," and this I wanted to include both date and time, then we're going to use the datetime data type. So, let's try to create this table. I highlight this, click on "execute"; now the table is created. So, I refresh the table; I should have all of the new columns over here. All right, so let's right-click and "Edit Top 200 Rows." By default, all of the columns are not populated; therefore, NULL is being displayed. Let's focus on the last three columns that we just added. So, the launch date is the date without time. So, let's say today is 2023 December 14th, and the time right now is 11:35:00. Let me copy this first, and watch; when I click out, you see that the time portion is dropped; it automatically drops the time portion because the data type of this column is date; it's date only; it doesn't actually want to include the time portion. Now, if I paste this thing over here, here, and "launch time," because the data type for the "launch time" column is time, so therefore, this is not a valid data structure. If I click out, it gives me this "invalid value for cell row number one column number seven." Right-click on "okay." We have to remove the date part, then if we click out, you can see that it accepts the time. Now, "expiry date," on the other hand, includes both date portion and the time portion; therefore, I can paste this over here, and if I click out, it accepts it. So, if I close this and try to open it again, so instead of "Edit Top 200 Rows," let's click on the "Select Top 1,000 Rows." Although we have not learned about select statement, we can use it to display the data that we entered. Now, we have "launch date," which only stores the date part, and then we have "launch time," which only stores the time part; well, you can see there's seven digits after the second, so it's very, very accurate, and then you have "expiry date"; "expiry date," you have three digits after the second part, so it's less accurate, but it includes both date and time. Okay, that's everything I want to show you in this video; I will see you in the next one.

In this video, let's talk about column constraints, specifically the NOT NULL constraint. Let's go to our SQL Management Studio here, and let's look at what we have done with the SQL statement previously. When we create tables, we were using the table name, and then we specify the column name followed by the data type, another column name followed by the data type. Actually, after the data type, we can add another thing, which is called column constraint. Column constraints are rules that apply to the values of a single column; right, it limits the possibilities of the data in the column. Because we didn't add any column constraint, so none of the column actually have any constraint, and because of this, when we try to add a data, let's say for this record, I put in 100 kilograms, and then the price is $19.99; when I click out, SQL Server accepts this record, but this record does not make sense because we don't know which product has 100 kilograms and $19.99 for the price. At least we need to specify the name of the product. To do that, when we create a table, we can specify the NOT NULL constraint right after the data type of the column. In this case, the column name can no longer accept NULL value; right, NULL value means value not provided. Let's drop the table and recreate it in order to see the data again. Let's close this and right-click on the table and edit it again. Now, let's recreate the same scenario; weight is 100 kilo, price is $119.99. Now, if I try to click out, immediately see the error window here. So, no row is added into the database; it says "insert fails," and what is the reason? It says "cannot insert NULL value into column 'name'," right, because the column "name" does not accept NULL anymore. Let's click "okay" and add "beef" as the product name. Now, if I click out, you can see that it accepts the data, and the data is actually makes sense; at least now we know that it is beef that has 100 kilos and the price is $119.99. Perhaps the category needs to be, uh, also enforced. So, let's add NOT NULL to category, description; that's fine; we don't have to have description; perhaps we don't have to have weight, price; when the product is just inserted in the table, we don't know the price yet; we don't have to insert the price; so, therefore, we don't have to add the NULL constraint. "Launch date," some of the products' launch dates are unknown; that's acceptable as well. At least we need to have these two columns that have the NOT NULL column constraint. Okay, that's everything I want to cover in this video; I'll see you in the next one.

In this video, let's learn about another column constraint, which is the UNIQUE column constraint. For example, in our products table, if we apply enforce a rule and we say that there cannot be multiple products that have the same name, we can use the UNIQUE column constraint to apply to the name column in order to enforce this rule. So, let's go to SQL Management Studio over here, and we can apply the UNIQUE constraint right after the NOT NULL constraint, just like this. You can apply the UNIQUE constraint before NOT NULL; it doesn't really matter. So, let's drop the existing products table and recreate it. Now, let's right-click on the products table and click on "Edit Top 200 Rows," and let's populate the product row again. Now that we have to enter information in the category, let's use "MEA," which stands for meat, and then here we're going to say "beef," and everything else we can leave as NULL, which means missing data. Now, I try to enter "beef" into it again; watch what's going to happen. So, click out; immediately you see this "violation of unique key." So, when we apply a UNIQUE constraint to a column, so the server creates a unique key, and this unique key is used to enforce the uniqueness constraint on the column; so, therefore, we cannot store this data. Now, if I change this to, let's say change to "pork," now if I click out, both "beef" and "pork" are stored in the table. That's the UNIQUE column constraint.

In this video, let's learn about the CHECK column constraint. So, for example, in our products table, we can apply the CHECK constraint to our weight column so that the weight is greater than or equal to zero. Of course, we can apply the same column constraint to the price column or the quantity column, right? Let's try to do that on the weight column. In this lesson, let's go back to our SQL Server Management Studio, and let's try to apply the CHECK constraint to the weight column here. So, I can say CHECK followed by the brackets, and then inside the bracket, I can specify a logic expression. So, for example, I can say weight has to be greater than or equal to zero. Now, I can drop the table and recreate it. So, after I recreate, right-click on the table and click on "Edit Top 200 Rows." Unfortunately, I have to enter the data again. So, again "beef," I'm going to leave the description as NULL, and wait here, as you can see, we did not actually apply the NOT NULL constraint, so I can leave this as NULL; it's going to accept it. So, let me click out; you see the data is accepted, but if you want to enter any data, it has to be greater than or equal to zero. Let's say I enter zero; this, when I click out

is accepted. I've entered 10. This is also going to be accepted when I click out. You can see that it's accepted. But if I enter minus 0.1, when I click out immediately, you can see the check constraint is doing something. It says the conflict occurred in the database; the table column is wait. The problem is that the check constraint is not satisfied; therefore, we cannot enter anything that is less than zero. Right? So if I enter a big number, it's still going to be accepted. This is the check column constraint. Remember to use it after the data type, with open and close brackets, and within the bracket you're going to specify an expression with the same column that you are using the check constraint against. You cannot reference other columns though.

In this video, let's learn about the primary key column constraint. When the primary key column constraint is applied to a column, that column is usually used as a unique identifier for the table, and usually a primary key is going to be the ID column. In this case, we can call it product ID. So typically, we apply the primary key column constraint to the ID column. Let's jump into Management Studio and try to use the primary key column constraint. Let's add the product ID column, and it's going to be integer. When we try to apply a column constraint, we can just add the column constraint after the column data type. When I say primary key here, it would enforce the uniqueness for the data in this column and also it would enforce the NOT NULL column constraint. You can sort of say the primary key column constraint is a combination of the unique column constraint and the NOT NULL column constraint. Even though we apply the unique column constraint and the NOT NULL column constraint to the name column, the name is not the primary key in SQL Server. When we apply the primary key to a column, in addition to the uniqueness and NOT NULL column constraint, SQL Server also creates a clustered index, which improves the table's performance. Another thing is that a table can only have one primary key. You cannot apply the primary key constraint to, for example, the product ID column and then you try to apply the same thing to the name column; that's not allowed. By default, SQL Server can only have one primary key. Okay, let's add a comma and try to create this table here. All right, the table is created. Let's refresh the table here and then open it up. You can see product ID has a PK here, which stands for primary key. And if we go to Keys, we can see that we have two keys: one is the primary key, which is applied to the product ID column, and then the other one is the unique key that is applied to the name column.

Another way you can apply the primary key is at the bottom. So let's drop the table first, and then let's remove this primary key here. What we can do is add a comma, add a comma, and then we can say CONSTRAINT, and now we can provide a primary key name. So we can start with PK_ and just call it PK_product. This stands for the primary key for the products table, and then I specify the type of the constraint, which is PRIMARY KEY. Which column do we apply the primary key to? Uh, it is going to be the product ID column. So that's it. We can run this. Uh, again, before I run it, pay attention to the name of the primary key right here, right? So after I execute it and then I refresh the table, let's take a look at the columns. Columns didn't change, but let's take a look at the key now. The name of the key is changed to PK_products this time; it has a better name, right? So this is the second way to apply the primary key.

Another thing I want to cover is that usually when we apply the primary key to the ID column, we make the column auto-increment, which means it automatically increments the number. To do that, we specify that this column is going to be an IDENTITY column, and the initial number is one, so the first ID is one, and then when it increments, it increments by one. So we can do this way. Of course, you can increment uh by two or three, any number, or you can make the ID start at any number, for example 100, but usually we start from one, and every time we increment, we increment by one. Let's drop the table again and run this CREATE TABLE statement. Now we have a new table, and we try to edit the table; we see this product ID column here. However, if you try to type in, right, type in anything, like for example one, it doesn't go in because it's an auto-increment column. Let's say I add a category and a name. Now when I click out, data is going to be inserted into the table, and you can see that automatically the product ID is generated. If I add another one and click out, you can see that the second ID is generated. Now you can see the IDENTITY auto-increment attribute is working; the product ID starts from one; every time it increments, it increments by one. Okay, that's everything I want to cover in this video. I will see you in the next one.

We have been working on creating tables in this video; let's work on updating tables. So I'm already connected to SQL Server. I'm going to right-click on the database and click on New Query so I can have a space to work with. First thing I want to show you, instead of updating the database, I want to show you a stored procedure that we can use to see the details of the table that we want to see. So that stored procedure is called sp_help. Don't worry about what a stored procedure is; you can just consider it as a SQL command, right? So sp_help followed by the table name. So we have a table that is called products. If we want to execute this stored procedure, uh, we can use a command called EXEC. This stands for execute. So basically, we're saying we're trying to execute a stored procedure, and the stored procedure name is sp_help, and with all of that, we can press on F5. You can see the result has several sections: the properties of the table, all of the detailed information about each one of the columns, this is about identity columns, and then some file information, you don't need to worry about indexes information, constraint information. So all of the information about the table, which can be quite helpful when you really want to look into the details of the table information. Okay. The next thing I want to cover in this video is to be able to rename a table. To rename a table, again we want to use another stored procedure, so EXEC, and then sp_rename. This stored procedure takes two parameters, and the first one is the old table name. So let's try to rename from products to product, and then I want to rename it back. So once I have this, I press on F5. The table is renamed. Uh, let me go to the tables and then refresh the tables folder. Now you can see that the name is changed to product. I want to change it back. The next SQL statement we want to learn is the SQL statement to add a column. So for that, we are basically modifying the table, and remember to create a table; we said CREATE TABLE. Now to change a table or update a table, we say ALTER TABLE, okay, followed by the table name. So in this case, it's the table name is product, and then we can say ADD. Let's just call the column extra_column, then the data type of the column, uh, I'm going to use INTEGER, and then the column constraint. So let's say that column constraint is NULL, and then I press on F5. Now if I open up the table and open up, open up columns, well, first of all, I have refreshed tables, and then I'm looking at the correct table; open up columns. I see uh the extra_column at the end, and the column constraint is NULL, so which is by default. So this is how you add a column. Next thing is we want to uh drop the column that we just added. Again, we are making a change to the table, so we're going to say ALTER TABLE followed by the table name, and we're going to say DROP COLUMN followed by the column name, so extra_column, and if I highlight it and press on F5, then the column is dropped. So if I refresh the columns folder, you can see the extra_column column is dropped. Now if I want to make a change to an existing column, for example, I want to change the launch_date. Instead of using the data type DATE, I want to use DATETIME, and just to be more accurate. So again, I'm making a change, so I say ALTER TABLE followed by the table name, and then I'm making a change to the column, so I'm going to say ALTER COLUMN followed by the column name launch_date. Now I just need to specify the new data type, which is going to be DATETIME, and I don't have to specify the column constraint if I don't; that means there's no constraint. Notice that we are using the same pattern as in the CREATE TABLE SQL statement, so we have column name followed by the DATETIME followed by the column constraint. Now I can highlight it and press on F5, and if I refresh the columns folder, you can see the launch_date is changed from DATE to DATETIME. Uh, I want to change it back, actually, change it back to DATE. Now refresh the columns folder; it's changed back to DATE. So the next SQL statement I want to cover is to add a column constraint. Okay, so again ALTER TABLE, I want to add a column constraint, so I say ADD CONSTRAINT, and then I provide the name of the constraint, and I'm going to say CHECK_PRICE. The type of the constraint is a CHECK constraint, and then followed by a logical expression. So here I want to make it work against the price column, and the price has to be greater than or equal to zero, and that's it. Want to execute this; press F5, and then if I refresh the columns, you don't see any change here, but if you go to constraint here, you can see I have a CHECK_PRICE constraint over here. Now I can drop the constraint by saying ALTER TABLE products DROP CONSTRAINT followed by the constraint name, and then I highlight it and press on F5. Now if I've refreshed the constraint folder, you can see that constraint is deleted. In this video, we covered a lot of things about making changes to an existing table. If you have any questions, please let me know; if not, I'll see you in the next one.

In this video, let's go through our first assignment. There are four things you need to do in this first assignment. The first one is you need to add a quantity column to the products table. So you need to think about the data type for the quantity column; you need to think about the column constraint for the quantity column. The second one is to remove the category column from the products table. Remember I said that using characters as the data type for the category column does not make sense, so let's remove the category column altogether, and then we will try to add it back later. This is for preparation for later lectures. The third one is that you need to add a category table, and the table name is categories. There are two columns for the categories table. First column is category_ID; so the category_ID here is going to be a primary key, and don't forget to make it auto-increment. The second column is name. Again, you need to think about the data type and you need to think about the column constraint. The fourth one is the order table, and let's call it orders. It also has an order_ID; again, it's a primary key, and you make it auto-increment. order_date; I want it to have both the date and the time in it. And third, one is customer_email_address. Those are the four different tasks you need to do in this assignment. Try to do that yourself, and then in the next video I will provide my answer.

In this video, I'm going to provide my answer to all of these four different questions or tasks. So let's go to our Management Studio here. I'm already connected to my SQL Server database management system, and currently am using my market management database. So the first task that we want to perform is to add a quantity column to the products table. To do that, we're going to use the ALTER TABLE SQL statement. So we're going to say ALTER TABLE followed by the table name, and then we are going to add a column, and the name of that column is going to be called quantity. So what's going to be the data type for the quantity? I can use INTEGER, or there's another data type that is available that is called BIGINT, right? So either INT or BIGINT; BIGINT just means that the data range is much bigger than INTEGER, but INTEGER itself is enough as well. And what would be the column constraint for quantity? Well, we don't have to add any column constraint, but if we have to, then we can add the CHECK constraint, so we can say CHECK, and the quantity has to be greater than or equal to zero. Okay, so that finishes our first task. So let me press on F5, and then this time I don't want to use the uh the UI here, right, the try here; I just want to use the thing that we just learned from the previous lecture. We can use sp_help followed by the table name. Then I highlighted; press on F5. Now I can see I have the quantity column added properly; the type is INTEGER, and then about the constraint, we can scroll down here on the right; there's a scroll bar; you can see that we have a CHECK column constraint on the quantity column. Uh, it automatically generates a name for me, and the constraint logic here is the quantity must be greater than or equal to zero. Uh, I can press on Ctrl+R key combination, Ctrl+R, to hide the result. So this is my first, first assignment, right? So try to practice this. That I added a comment. Okay, so in your SQL code, try to add some comments to make your code easier to read. Second assignment, let's go to the assignment; should remove the category column from the products table. Again, this is making a change to an existing table, so we're going to use ALTER TABLE followed by the table name, and then we want to remove a column, which is the DROP COLUMN command. Okay, so we can say DROP COLUMN followed by the column name, and we want to remove the category column. Now we have multiple statements on the screen, so let's highlight the one and then press on F5. So now if I execute this sp_help stored procedure, I can see that the category column no longer appears in the column list. Again, let's press on Ctrl+R so that we can have a full screen to work with. Now the third one is to add this category table, right, categories table. So we can just say CREATE TABLE, and the name of that is categories, then we have brackets. In the first column is the category_ID; the type of the column, I wanted to use INTEGER; it is going to be an IDENTITY column; the initial value of the ID is going to be one, and every time it increases, it increases by one. Now I want to add a column constraint because this is going to be a primary key, so I'm going to say PRIMARY KEY. Okay, that's my first column definition. The second column is the name column. Before I continue, I want to point out one thing: you see this becomes color-coded, right, blue; this means that name is a keyword in SQL Server. Keywords may do something special for you. We can use the square brackets; this kind of takes away the special functionality that comes with the keyword. Now what data type do we want? I want to use VARCHAR. Why do I use VARCHAR? Because the name of the category can change; it can be called fruit; it can be called vegetables; it can be called many different things, so the length of the word can change, so I cannot determine how long the name should be; that's why I use VARCHAR, right, variable characters. The maximum length, let's say 500, that should be enough; that should be enough for a supermarket or any grocery store. What type of column constraint I want to add to it? Perhaps I want the name to be unique, right? You don't want to have multiple fruit records in the categories table; it does not make sense at all, so I want to make it unique. I can add the UNIQUE column constraint like this. Another column constraint I want to add is NOT NULL, right? It does not make sense that I have a category that only has an ID; doesn't have a name, so I want to add the NOT NULL constraint. Now I can highlight this and press on F5, and now do Ctrl+R to hide it, then I can change this sp_help statement; you change the table name to categories, then highlight it. Now you can see all of these sections show up; I have the table information; it even tells me the create date and time; I have the category_ID; I have the name; I can see that this column is the IDENTITY column, right, the seed, which is the initial value is one, and I increment by one; I can see my primary key here, and it is on my category_ID, and I can see my unique key here that is on my name column. So this finishes the, the third task. Now comes to the fourth task; we want to add the order table. Again, I want to add a comment, and I say Fourth task. Well, instead of assignment, I should have said task. So let me do Ctrl+H, and I'm going to replace assignment to task, and then I click on this button; I replace four words here. So fourth task is to create the order table, and I'm going to copy the task over here. So this is the table, table structure I want. Again, I'm going to use CREATE TABLE; name of the table is orders, and then I want to have an order_ID column; the data type is INTEGER; it is an IDENTITY column, and it's a primary key, so I apply the PRIMARY KEY constraint. Now the second one I want to add to it is the order_date, and because the requirement is to have both date and time in it, that's why I'm going to use DATETIME for the order_date, and I want this date to always be there, so I want to use a NOT NULL column constraint. And the fourth one is going to be uh customer_email. To make it a little shorter, instead of customer_email_address, I'm going to just say customer_email; this doesn't really matter. And here I want to use VARCHAR, and perhaps I say 500 is the max length. Uh, what kind of column constraint do I add to it? I want to add a NOT NULL column constraint because for an order there must be a customer, so I'm going to use customer_email_address to represent the customer; therefore, there cannot be an order without the email address, so I'm applying the NOT NULL column constraint. Okay, let me highlight this SQL statement and press on F5, and the table is created. So if I highlight this, the procedure to help me to describe the, another, the categories table, but the orders table, then I press on F5, I get the table information, the columns information, the identity column information, the primary key, and the primary key constraint information on the order_ID column. Another thing we can do is to come over here by using the SQL Management Studio UI; we, we can check the tables. So we created the orders table; we created the categories table; let's check the columns; do they look correct? They look correct; everything seems to be fine, and that's the end of this video. If you have any questions, please let me know; if not, I'll see you in the next one.

In this video, let's learn about table relationships, specifically the one-to-many relationship. In the world, we see relationships like teacher to students. So one teacher can teach many students in one class, and within one class, a student can only be taught by one teacher. So this is a one-to-many relationship. Another relationship is like the market management system that we are trying to create uh with our SQL Server, which is one category can have many products, and one product can only belong to one category. Again, this is a one-to-many relationship. So let's think about how do we establish this type of relationship in the real world, especially with this type of relationship between people. We establish that in the real world by asking students to remember their own teacher's name, but we never expect a

Teacher, remember all of the students' names. The teacher may or may not. So the relationship between these two types of data is established by, you know, the student remembers the teacher's name. So if we abstract the second scenario and try to establish a data model for a database system, then it would look something like this. We have categories and we have products. The categories table would store all of the categories, and the products, and the products table will store all of the products. The relationship between these two is one to many. This is how we represent a one-to-many relationship on paper when we do database design. Sometimes people use a star on the right-hand side instead of using M. So M here stands for many, right? So a one-to-many relationship. And because in the real-world scenario, we ask the end of the many to remember the end of the one, therefore, in the computer world, right, in database design, we ask each record on the end of the many to remember the end of the one. In this case, we ask each product to remember its category.

Uh, let's go to our CLE server management Studio here. We already have a categories table before the class. I've already added these two categories: so fruit and meat. Heh, let's close this. Uh, but I didn't add any products. So let's click on add it and let's, um, add some products now. Like I mentioned, the responsibility of remembering the relationship is on the end of the many. So in this case, it's product here. Each product will need to remember its category. How does it do it? We need to add an additional column here so that beef knows its category. And if we open up our categories table, we have category ID and we have category name. We can use the name, but that's not ideal because the name can change in the categories table. When that changes, if we use the name to remember the category in the products table, then when the name changes in the categories table, we also have to update the products table. So therefore, the ideal design here is to use the category ID in the products table to link back to the category table. So we're going to change the products table, and we are going, going to add an additional column, and the name of the column we can just call it category ID. Of course, the type is the same as this primary key here, which is integer, and we can add a column constraint NOT NULL because, uh, we need to enforce that each product needs to provide its category ID. So let me press on F5. You can see that it says ALTER TABLE only allows columns to be added that can contain NULLs. So right now, because we already have records here, so it doesn't allow me to enforce this rule because once I add this category ID here, that means, uh, we're going to have two records that don't have any category ID. But if we don't allow it to be there, then you can actually add the column. So let's remove this NOT NULL column constraint. Now I can add it. Let me close this and re-edit the products table here. Now I can see the category ID. Now, in order to refer back to the categories, I just need to populate the category ID. So beef belongs to meat, which is category number one. So let's, let's take a look. So SELECT TOP 1,000 rows. You can see that meat corresponds to category ID number one. Food corresponds to category number two. So here I put one, and here for pork I put one as well. And if we add Apple, then I have to enter number two. If I click out, apple is added, and it refers to number two. So you can see that this naturally establishes a relationship, right? The end of the many, which is the product, knows its own category ID, just like each student remembers their teacher's name. But inside the categories table over here, we never ask the category to remember all of its products' IDs. It does not make sense.

Now that we populated the category ID for each record here, we can go back and enforce the column constraint. So here I'm going to say ALTER COLUMN category ID, and we'll have to add the NOT NULL column constraint. And when we execute it, it says completed successfully. And let's close this and go back to the products and try to add it again. Now if I add, let's say I add banana, and if I just click out, it's going to tell me that cannot insert NULL into the column category ID. So I have to provide the category ID now. And what is the ID I need to provide? That's number two. However, I can provide number three, although number three doesn't actually exist in the category table. That's a problem, and we're going to resolve that in the next video. Let's just use number two correctly. Okay, so this is what a one-to-many relationship is, and this is how you establish a one-to-many relationship. You need to add a column on the right-hand side, right, on the end of the many, to refer to the primary key on the end of the one. That's it for the video. If you have any questions, please let me know. If not, I'll see you in the next one.

In this video, let's learn about foreign constraints. Previously, we have mentioned, although we have this category ID here, we can enter something that doesn't even exist in the category table, right? So, uh, again, if I run this SELECT TOP 1,000 rows, you can see that I only have two records, and category IDs are just one and two. However, I can click out from here, and you can see that this banana refers to category number ID 10, which doesn't really exist. So we need to add a constraint to the products table so that we can protect data integrity in the table, and that's what we call the foreign key constraint. So let's go back to the diagram here. In this case, we have categories tables and we have products tables. Each product remembers its category ID. So each product refers to the categories table's category ID here. We need to reinforce a constraint that the category ID entered in the products table has to exist in the categories table as the primary key. And how do we do that? We can use an SQL statement. So again, we're going to alter the products table, and now we are going to add a constraint. Just like before, we add a constraint; we can provide a name here. We say FK, which stands for foreign key, and this is a foreign key from the products table to the categories table. So we can say product_category. So this is the name of the foreign key, followed by the type of the constraint, which is FOREIGN KEY. Which column should be limited by this constraint in the products table? That's the category ID column. And this ID column needs to reference the categories table. Which column in the category table? Again, that's the category ID column. So this is the SQL statement to establish the foreign key from a column in one table to another column in another table. Before I execute this, let's fix the data. Okay, so let me fix the data. I put number two back, and then I can execute this. Now if I use the UI here and let's open up here and let's, let's refresh the columns here. You can see FK is added to the category ID, and the little key here is edited, right? This key is not solid; only the primary key here is solid. And you can see under the keys folder, I have an FK product_to_category. So the name is pretty meaningful; that means we are pointing from product to category. Now if I right-click on it and click on edit top 200 rows, if I add another, let's say another fruit, and over here if I put number 10 and if I click out, right now it refuses to insert data, right? It says that foreign key constraint FK_product_category is not satisfied; it's a conflict. So we cannot insert data. However, if I enter a category ID that does exist, for example, number two, then I click out, everything is okay. You can see how a foreign key constraint protects data integrity in our database between tables. Remember, a foreign key can only be established between a column in the current table to the primary key of another table. This is everything I want to cover in this video. If you have any questions, please let me know. If not, I will see you in the next one.

In this video, let's study the many-to-many relationship first. First of all, let's look at a real-life example. I have many courses and many students. One course can have one or many students; actually, one course can have zero or many students. Same is true in a one-to-many relationship. Let's actually go back. Uh, one teacher can have zero or many students. So let's not forget the possibility of zero. Okay, let's come back to many-to-many. So one course can have zero or many students, and one student can enroll in zero or many courses. So obviously, this is a many-to-many relationship because on both ends there can be a possibility of many. So then how do we map this type of relationship in real life? We keep a registration book like this. Now we can know that course one has two students enrolled, course two also has two students enrolled, course three also has two students enrolled. And if we look at the same relationship from the right-hand side, the first student enrolled into course one and course three, the second student enrolled into course one and course two, and the third student enrolled into course two and course three. So this registration table maps out the many-to-many relationship very well. Similarly, in database design, we use the same technique to express the many-to-many relationship. So in database design, we will have a courses table on the left-hand side, and we will have a students table on the right-hand side. In between, we need to have a registration table, and the registrations table contains records like course and student, at least those two records. One course will show up multiple times in the registration table, just like here you can see course one, course two, right, multiple times, but course one only exists in the courses table once. Therefore, from the courses table to the registration table, it's a one-to-many relationship. And same thing here, one student can appear in the registration table multiple times, but one student can only appear in the student table once. So therefore, this is also a one-to-many relationship from the right-hand side to the left-hand side, but this time we use letter N here instead of letter M. So when you see this type of design on the paper, you will know that the table in between is a mapping table for the table on the left and on the right. From the table on the left to the middle is a one-to-many relationship. From the table on the right to the middle is a one-to-many relationship. Therefore, from the table on the left to the table on the right is a many-to-many relationship. Same is true from the table on the right to the table on the left; it's a many-to-many relationship.

Now you know this example, let's do an exercise. Currently, we have an orders table in our SQL Server database already created, and we have a products table in our SQL Server database already created. What do you think the relationship between the orders table and the products table is? So how do you know this? Let's do the exercise this way. Is it true that one order can have many products? Well, that's true, right? You can order many products at once. Therefore, one order can have one or many products. Now you look at the relationship from the right-hand side to the left. One product can be in multiple orders. Is that true? It is also true because one product can be ordered by different customers in different orders. Therefore, this is a many-to-many relationship, and typically in e-commerce database design, we use the order_line table to map the relationship between the orders and the products. And when we implement this table, where this relationship table in the middle, it's actually pretty easy because from this table to this table it's one-to-many, from this table to this table is a one-to-many, and you have already learned about how to design a one-to-many relationship table. And the only thing that is special about it is to create a foreign key to map from the many to the one. That means you just need to create two foreign keys: the map from the middle to the left and then another foreign key to map from the middle to the right. So let's jump into CLE server management Studio to see how we implement the order lines relationship table.

Okay, so this is our Market management database, and currently in our database we have an orders table and we have a products table. There is a relationship between the orders and products, but currently that relationship is not implemented. So how do we implement that relationship? We will need to create a new table, and we will call it order_lines table, right? That order_lines table is that relationship table in between orders and products. So let's go ahead and create a table by using the SQL statement that we have already learned. So CREATE TABLE, and then we're going to call it order_lines. Now brackets, and in the bracket we will add columns. So we will need an order_id that we can use to map to the orders table, and of course, uh, it's going to be the same data type as the orders_id here in the orders table, so it's INTEGER, and it cannot be NULL, of course. Now we need a product_id that maps from the middle table to the products table. So again, it's going to be the same data type as the product_id in the product table, so INTEGER as well, and it cannot be NULL. Now to protect the data integrity, I also need to add foreign keys. I can add foreign keys directly at the column level. So I'm going to say FOREIGN KEY REFERENCES orders(order_id), and same thing here, I'm going to say FOREIGN KEY REFERENCES products(product_id). There's a red squiggly line here. I think SQL Server is having a glitch about the existence of the table, so let's don't worry about it for now. In addition to the order_id and product_id, we also need to know how many products are included in this mapping, right? So how many products are included in this order? So then we add a quantity column, and the data type is going to be INTEGER, and we cannot allow it to be NULL, and we can also add a column constraint. We're going to say CHECK (quantity >= 1). Also, we want to add a primary key to this, and the best choice for a primary key is, uh, just an ID, and we can call it order_line_id. The data type again is INTEGER. It is going to be an IDENTITY column. The seed is one, which is the initial value, and every time increment by one, and now we can add a primary key column constraint. Okay, I think we are ready for everything, and let's click on the execute button here, and you can see that it says commands completed successfully. And if we come to the tables folder and click on this refresh button, we should be able to see our order_lines table added over here. We have the columns, we have the primary key, and the foreign key is established correctly. Now if I right-click on it and click on edit, I can see all of the columns, but do we have an order already in our table? We don't actually have an order. So in order to insert the data, we can go over here and add our first order. We can populate the order_date; it can use today's date. The customer email is, let's say customer1@gmail.com. Okay, and then I click out; I generated the first order. Now let's populate the order line. For the first order, I want to buy the first product; I believe it's beef, and I want to buy 10 kilograms of beef. We can also include the second product in the order, uh, I think it's pork. So let's say I buy 5 kg of pork and click out. In this first order, we have two products, and each product has different quantities. Now I can go to the orders table and create a second order. So I'm going to use the same date, and the second order is placed by the second customer. So order_id is number two. Product is beef; this person buys about 20 kg of beef. And the second order also includes this third product; I think it's a type of fruit. The quantity is, let's say, 3 kg of that fruit. I think it's banana. Now you can see that this order_lines table, which is the in-the-middle relationship table, it maps the orders to the products, and this is a many-to-many relationship. You can see that one order can have many products, and one product—especially pay attention to this product number one, which is beef—one product maps to multiple orders: number one and number two both include beef. So this is how we implement the many-to-many relationship in a database management system. The most important thing you need to remember is that we use a relationship table to express this kind of relationship, and then when you implement it, just remember that from the left table to the relationship table is one-to-many, so you just need to add a reference column from the middle to the left, and the same thing, you need to reference the right table from the middle to the right. Before we finish, I just want to mention that in database design, there's another way to express the many-to-many relationship, which is to ignore the middle table, and here we use N and M, or you can put M on the left, N on the right. So this means that from the left to the right it's a many-to-many relationship. Same is true from the right to the left, but this type of design is more detailed, but you know exactly how to implement it in a database management system. Okay, that's everything I want to cover in this video. If you have any questions, please let me know. If not, I will see you in the next one.

In this video, let's go through the second assignment in our course. So there are three different tasks in this assignment. The first one is to identify the relationships between the following tables: posts and comments, books and authors, departments and employees, last but not least, movies and actors. When you try to identify the relationships, ask yourself, for example, does one post have many comments or just one comment? And then you can go from the right-hand side and say, is one comment belong to one post or many posts? Right? Same as the books: is one book written by one author or can be written by many authors? And then from on the right-hand side, can one author write many books or just one book? And those questions will help you to identify the relationships. Now comes to the second task: to design and create a blogs database that has the following tables. So in this task, you will need to use the SQL statement to create a database. Once you create a database, then you create the tables in the database. I did not point out the columns for each table. You can come up with a minimum number of columns as long as they make sense for each table, and then you will need to establish the relationship between the tables. Same as this third one: you need to create the movies database, and then you create these two tables in the database. Another thing you need to pay attention to: if any of these tables have a many-to-many relationship, that means you will need to add a relational table in the middle of the two tables. So I did not write that relationship tables for you; you will need to do that mapping for yourself. Okay, try to work on the assignment yourself, and in the next video, I will provide my answer.

In this video, I'm going to provide my answer to assignment number two. The first task: what is the relationship between the following tables? So in the blog system, we have posts and we have comments. One post can have many comments, and one comment can only belong to one post. So therefore, the relationship between the two tables is one-to-many from post to comment. Now comes to the second one: books and authors. One book can be written by many authors; they call this co-authoring, right? And then one author can write many books. So therefore, from books to authors, this is a many-to-many relationship. The third one: departments and employees. Inside a company, one department can have many employees, but one employee usually only belongs to one department. Hence, from department to employees, it's a one-to-many relationship. From movies to actors, one movie can have many actors.

And one actor can show up in many movies, so therefore this is also a many-to-many relationship. Now let's work on the second task: design and create a blocks database that has the following tables. From the previous task, we already know from post to comments it's a one-to-many relationship. So let's work on this task now.

First of all, let's open up our SSMS (Server Management Studio). I'm going to close all of the windows. Go to Window, and then close all documents and click on No. Now everything is clear here. I am going to right-click on the server and click on New Query. Now don't worry about the default database that is selected; just start by creating a new database. So create database, and the name of the database is called Blocks. I press on F5, and the Blocks database is created. Now I need to create Posts and Comments tables. So I'm going to delete this, and then I'm going to write USE Blocks. Okay, so this is going to switch my database, my current database, to the Blocks database. Again, SSMS (Server Management Studio) right now doesn't know the existence of the Blocks database. I think SSMS (Server Management Studio) needs to be improved in this.

So now I'm going to create a table, and the name of the table is Posts. I have brackets, and then inside the bracket I'm going to define my columns. First of all, I'm going to have a primary key, and the data type is integer, and the column constraint is primary key. This is going to be an identity column, so the seed is one and increment by one. So this is the first column, and the second one, and I want to have a title of the post, and I want to use NVARCHAR. Why do I want to use NVARCHAR? Because, you know, a blog system needs to support different languages because different bloggers may use different languages. So I want to use NVARCHAR instead of VARCHAR, and the title I give it 100, so the maximum length of the title is 100. And then I want to use the minimum number of columns, so I don't want to add like subtitles, right? So uh, I want to just have the content, and the content is going to also use NVARCHAR, and I can provide MAX, so the max number of characters here. Now perhaps it makes sense to add a date column to specify when is this blog posted, right? So I'm going to say DatePosted, and then I specify the data type is going to be DATETIME. Now I forgot to specify the column constraint for Title. I think a blog post needs to have a title at least. Uh, for Content, well you can write a title and then come back to write a Content, so we can leave it as default; we don't provide NOT NULL column constraint. DatePosted, uh, I think it needs to be specified, so I add NOT NULL constraint. So that's our first table. So before I execute it, I, I can directly create my second table. So CREATE TABLE, and then Comments. Again, I'm going to add my primary key here, so that's the integer, and then it's an identity column with initial value one, increment by one, and this is going to be a primary key, so I add the primary key column constraint. So Comment doesn't have a title; I can just have Content, and again I'm going to use NVARCHAR and MAX with maximum length. So, so for a comment, does it make sense to have an empty comment? Uh, it doesn't make sense, right? Otherwise you just don't comment. So I apply my NOT NULL column constraint, and I also want to record when it is posted, so I'm going to have the same thing, and I also want to add another column to specify who actually posted this comment, right? So, uh, let's say User Name, and here I can say 100 NVARCHAR. You can post anonymously, so therefore I'm allowing NULL to be here, so I can, I can either not specifying the column constraint or I just say NULL. Okay. Now I need to establish a relationship between the Comments and Posts; therefore I need to add a foreign key here. So I'm going to press CTRL+R to hide the result area, and then add another column. I'm going to say PostPostID, and this corresponds to the PostID here, so therefore the data type is integer, and we're going to create a foreign key. So let me say FOREIGN KEY REFERENCES which table, the Posts table, which column, the PostID. Now I'm going to press on F5 and execute all of this. So I don't have to select all of this; I just press on F5, and everything is executed correctly. Now if I go to Database and refresh the database, I see my Blocks database created, and it has two tables, Posts and Comments. Posts table has the correct number of columns, and the Comments table also has the correct number of columns, including the PostID as the foreign key. All right, we have done our job. Let's delete all of them and let's work on the fourth assignment, which is to create a Movies database with Movies table and Actors' table.

Okay, let's work on creating the, uh, database first. So CREATE DATABASE, and we're going to call it Movies, and I'm going to press on F5. Now I'm going to press CTRL+R to hide the area and delete this. I'm going to use the USE statement to switch to the Movies database, and now I can work on creating tables. So the first table is actually the Movies table. Again, I want to have my MovieID, which is an integer, and it's an identity column, and it is my primary key. Title column and NVARCHAR, right, because this can be a foreign movie, maybe a Chinese movie; it has Chinese characters in it. I'm going to use NVARCHAR to support Unicode, and the title may be, uh, let's limit it by 100 characters; it doesn't make sense to be too long, and I'm going to enforce the NOT NULL column constraint. And for the movie, uh, it needs to have a launch date, right? So I'm going to specify DATE as the launch date data type, and this one maybe I allow it to be NULL. A movie may have, you know, many, many columns in it, but I think these are the minimum columns that make sense for a movie. Now I need to create the second table, which is the Actors table, and I'm just going to copy this line here and paste it over here, and I'm going to change from MovieID to ActorID, and then the, uh, ActorName. In the ActorName, I'm going to use NVARCHAR, and perhaps I'm going to use 200 in case a name is very, very long, and of course this is NOT NULL. Maybe I can, uh, add a Country column here, and I'm going to say VARCHAR 100, and for this one I'm using the NULL column constraint. I can add information about the actor like birthday, even height, weight, all those information, but I think these are the minimum information that makes sense. Now I need to add a relationship between these two tables. We know that one movie can have many actors, and one actor can show up in many movies, so therefore it's a many-to-many relationship. So for a many-to-many relationship, we need to create a relational table in between. I'm going to actually create a relational table in between. CREATE TABLE, and to name the relational table, the easiest way is to use the two words and combine them together, so I'm going to say MovieActor, and we can again create a primary key here; we don't have to, but I would like to do that. So MovieActorID, and then we'll need a column to refer to this table, right, the, the table on the left-hand side, and then another column to refer to the table on the right-hand side. So here I'm going to say MovieID; this is an integer, and this is going to be a foreign key, and this foreign key REFERENCES which table, the Movies table, on the MovieID column. Again, the same thing is going to happen on the right-hand side, which is the Actors table, so I'm going to say ActorID, and then it REFERENCES the Actors table, and the column is going to be ActorID. Now you see that this shows a red squiggly line; it just means that because I put this table right behind Movies, right, so it doesn't point out any problems because, um, although the Movies table, Movies table is not actually created, but it knows that when we execute this whole batch of SQL statement, by the time it comes to create the MovieActor relational table, it already has the Movies table created. However, we don't have the Actors table created, so we cannot reference the Actors table, so we will actually have to cut this out and move this over to the bottom. Now you can see the, the red squiggly line is gone. Now we need to pay attention to the relational table. Whenever you create a relationship table, you need to, uh, think about is there any information between the relationship, right? Just like from Order to Product, there is information that belongs to that relationship, which is the quantity, right? One order can have, can order the same product multiple times, so you need to have the quantity of the product here. This movie can have this actor in it; is there any extra information it should be attached to this relationship? Perhaps for movie management company, this is, there is a, let's say, payment rate, right? So when we hire an actor for this movie, like how much do we pay? So I'm going to use DECIMAL(10, 2), total digits, and within the 10 digits we have two as the decimal places. Is there any other, uh, information that is related to the relationship from Movie to Actor? There could be, uh, contract date, right, contract expiry date, all those information, right? But I don't want to include those; I just want to provide one as an example. Okay. Now I can press on F5 to execute all of this SQL statement. Okay, I press on F5. Now everything is executed. Now you can, uh, refresh Database, and you can see the Movies database right here. Now I'm going to delete everything, and I'm going to use EXEC, and just to practice this, and then sp_help, uh, I want to look at our tables for the Movies database. We have our Movies, and you can see all those information for Movies. Uh, I want to see Actors, so I highlighted F5, so now I'm looking at information about the Actors. Now I want to examine the relationship, so sp_help MovieActor. Now we can see all of the information about this relational table, and we can see the foreign keys and primary keys, right? So we're referring to the ActorID in the Actors table here; we're referring to the MovieID in the Movies table. So all the information is described right here with the sp_help procedure. Okay, that's everything I want to cover in this video. If you have any questions, please let me know. If not, I will see you in the next one.

Get Our Mobile App

SQL Tutorial for Beginners with SQL Server | Full Course | 2024

Transcription