 Assalamu alaikum. Dear students, this is lecture number 26 of the database management system course. The course code is CS403. The particular segment or the phase of the course that we are discussing nowadays is SQL, the structured query language. In a way, this is a very interesting part of this course because here you see the things practically happening. So, you are creating the database properly, accessing it, it creates interest in the subject. So, I hope that you are taking interest in this phase and the other thing is this phase needs maximum practice because until you do not practice this, you will not be clear about the SQL commands and their purposes. And unless and until you are not clear, you cannot use them properly. So, I would recommend that take a very good time on this session. In the lecture number 25, we were discussing different types of SQL commands. And we also discussed the particular database system, the particular tables that we will be using during our SQL practice. And in this, the system that we selected was the exam system of any education institution. Today, we will start the SQL commands. So, let us start today's lecture. Dear students, as we have discussed before that there are three broad categories of the SQL commands. The DDL, Data Definition Language, the DML, Data Manipulation Languages, and the DCL, Data Control Language. So, these different types of commands are used for different purposes. First of all, we are discussing the DDL, Data Definition Language. And as the name suggests, this part of the languages, the statements, the commands included in this part of the language, they are used to create different things. It includes right from the database, tables, indexes, users, everything. So, it deals basically with the structure of the objects that are included in the database. Dear students, let me explain one thing over here. If you see the format or the general structure of each of these commands in any book or manual or any online book, online help, you will see that it is very complex. And if we try to discuss each of these commands as a whole, considering all different options, it will become very difficult for you to understand all the commands at the same time, at one time. For this, I have explained the commands in different parts and their examples. When you will be familiar with different parts of the command, then it will be easy to understand the command as a whole. So, you will see that, for example, it is a create command. Create command is used to create different things. So, if we look at the whole, it will include all the options. But we will look at it step by step. Similarly, if you see the alter command, it is used to make changes in so many different things and so many different changes. So, if we look at all of them together, then the alter command becomes complex. So, that is why I have explained every command in steps. And if you practice it, then when you combine them, then you will get the help that you can understand the functioning of the whole command and its working. So, let us start. Create command. Like its name says that this is used to create different types of objects in a database, different things in a database. And it is logical that in order to process something, in order to use something, first of all, you have to create that thing. You have to define the structure of that thing. And create command is used for that purpose. But the create command is used to create different types of things. It is used to create a database. As we have already defined that a database is a collection of logically related data. So, there will be different things in a database. There will be users, tables, and dexes in it. So, first of all, you will define a database, create it, and then you will define the specific things in it. And you will jointly name the database that you have named. Let us see what is the format of this statement. It is to create a database and then a database name. As we have already read the rules of the format, the things that are written in the capital, you have to give them as such in the same order. And in the lower case, you have to give by yourself. You have to define them yourself and give them their name. For example, if we see that create a database exam. Now, this is a command that we can use practically. So, if we see that when we have issued the command practically, we have not taken care of the case. In the upper case, in the lower case, in the mix. Because I told you earlier that SQL is not that sensitive. So, we are seeing that when we have given the command, we did not say that in the format that was written in the upper case, we do not write the same thing. Now, see one thing. The thing that is written in the format, the database name, where we have written the place of the exam. This means that the exam is going to be the name of your database. And it is not necessary that you keep the name of the exam. For example, when you are practicing, you keep the name of the exam system, you keep the examination, or you keep the exam system of ABC Institute, whatever. I told you once that the name should be reflective, the purpose of the thing. For example, the name of the same system, in which we are making the database about exams, that name even if we name it as, let us say, admission or library or production or sales, DBMS won't object, but it will be a bad practice. This is recommended that you use the meaningful names. When you have created the databases, after that you will create different tables. Like I told you before, almost every DBMS has two approaches to give you an opportunity or functionality to create things. You will have a tool with you in which you will give commands graphically, select different options, and things will be done automatically. The second thing is that you use the SQL commands explicitly. Obviously, the easiest thing is to use the tool. But as a database professional, you should also have underlying SQL commands. For example, if we take our tool, the SQL Server example, we have two components, one is Enterprise Manager, and the other is Cury Analyzer. You can also create databases using both of these. You can also create tables. You can also create other objects. Keep in mind that using Enterprise Manager is much easier because it will give you different options, different menus and options. You will select them and it will be created. In case of SQL, if you are using the SQL, you will have to give the proper command. You will have to take care of the syntax and requirements. Create a table. The command is used to create a table. Define attributes of the table with data types. Define different constraints on attributes or on tables like primary and foreign keys. Check Constraint, not null, Default Value, etc. The Create command works step by step. We will discuss it in a simple and complex form. Let's see how the Create command functions. Here you see the format and the general structure of the Create command. When we look at the format, it looks a bit complex. But if you look at it from the screen, it is not that complex. It is very easy to understand. For example, if you see the Create table, it has been written in capital letters. This means that you have to give it as such in this order. After that, if you look ahead, we have square brackets. At square brackets, they denote the optional things. If you want, if you need, then give. If you want to avoid it, you can avoid it. For example, an appropriate format recommended in SQL Server is that first, you give the name of the database. The name of the database, as we gave the name of the database as an exam. After that, the owner means that the database is going to indicate its owner. In this, you see the database name is .owner. You have to give it or the second option is that if you look at it, what is happening is that only you have the name of the database that is optional. What will happen in this? You will say the name of the database that is an owner and the option we will give would give you one thing. The name of the database and the name of the table or the name, only the name of theaff니다, the name of the table of the name. There are two options here. First is the name of the database and or speechimiento. Then, you will see in one database, and you are the owner of that database, the table you are creating, you can leave the name of the database and leave the name of the owner. So by default, the database you are working in, and you as the owner, you will be considered. So you just give the name of the table. The name of the table has been shown as compulsory here, as required. You will have to give the name of the table, but the name of the database and the name of the owner, these are optional. If you need them, then give them. If you don't need them, then don't hesitate. You will remember that I told you that one instance of the SQL Server, it can manage, it can control different databases. For example, as you just created an exam database, it is possible that this instance of the SQL Server, it contains the library system, the admission system, in the same way, it can be of any other institution, of any other organization. We also have a payroll, sales, purchase. So the concept of the database name, the purpose of it is that if you are creating a table at the time, then what is your current database? It is possible that the database you are using is someone else, but the table you are creating is someone else. Obviously, you will need permission or authorization for that. But at the time, we will leave this for now, and to simplify it, I would like to tell you that the name of the database and the name of the owner would be, if you are using any other database, but you want to create a table in which database, similarly, you want to create it as a different owner. But because initially, we will work in a simple environment, we will talk about that. So you just need to remember that you have to say create table or table name, that is it. For now, the name of the database and the name of the owner, you can ignore it and bind it. The table name here, this is not shown as optional. This is being shown as required. Let us move on. After this, we have our left parenthesis. And on this, you said that in the current addresses, column definition. And column definition is, we call it Angle Bracket Ceshavarans. I will tell you the explanation of this later. But one option we have is that the left parenthesis means that this is required, you have to give it. Then we have curly braces. The things in curly braces are different options. You have to give one of them. Either you will give column definition, one option, column name as computed column expression. This will give and this will give table constraint. So you will give one of these three things. Other than this, either you have to give it primary or unique. And then comma and dot means that this thing can repeat. That it can go ahead. So you see that the curly braces you have, the curly braces means that you have to give one of these things. After that, the option is that you can add primary or unique. And then the written option in the square brackets means that this thing can repeat. Now what we have written in the angle brackets, what is written in the shavarans? What does it mean? It means that whatever thing is written in the angle bracket, it means that this thing will be explained later. And the explanation follows. For example, the column definition there is written in the shavarans. This means that you will define it as column name as written here. Space data type. And after that, what you have to say here is the option is that default. Default means that this attribute, the default value of this column, and mind it, this has been shown as optional. Similarly, after you have further default, you have another option, that you give the column constraint. That is, whatever column you define here, you apply a constraint on it. So, this is the example. Similarly, another thing is that as we have given the column constraint in the angle brackets, what does it mean? It means that you will write the constraint or the constraint name. And mind it, this has been shown as optional. This means that you have to write the constraint with the word and if you write it, you will write it as such. This is the spelling, this is the location. And then in the lower case, there is the constraint name. You will give a name to the constraint here. After that, in the curly braces, it means that you will have to give one thing out of all the things. And what is there? First is null, not null. This is the form of constraint. By default, it is null, it is allowed unless it is the primary key. Sometimes, it happens that you want some attribute to be compulsorily entered, like name or address, like phone number, like qualification. Anyone, apart from the primary key. You can declare that as not null. This means that whenever the data enters, the DBMS will make it sure that the value for that attribute is entered. It will not let it go forward. The next option that you are looking at is primary key or unique. If you want to declare any attribute as a primary key, or if it is not a primary key, still you want it to be unique. You can declare it this way. The next option is foreign key. You want to declare some attribute as foreign key. In this, you can see that the two words of foreign key are optional. You can write it or not. But if you want to declare someone as a foreign key, in that case, you will have to give a phrase of references so that the home table and the attribute will have to be declared. That is why it is written as references and reference table. The reference column has been declared as optional. The logic is that if you have the name of the home table and the primary key, in that case, you do not need to give explicitly the name of the column. Just give the name of the table and the link will be established. Again, since it is not compulsory that the name of the foreign key is the same, the option is that if they are different or even if they are the same, still you can give the name. It is not that if the name is the same, then if you give the name of the reference column, then it will be wrong. It will be clear to you when we discuss its examples. Apart from this, when we declare the foreign key, we have two options. Cascade or no action. What is the meaning of this? When we were discussing the Referential Integrity Constraint, at that time I told you that it is a very major tool to maintain, to implement the consistency of the database. The cross references in the database they are valid, they are correct. So through the foreign key, we are implementing the Referential Integrity Constraint. The question arises that we have a table used as the foreign key in another table. The question arises that you implement the main table which is being referenced, which is also called master table in some cases. So if its primary key is being deleted or updated, then what should DBMS do? There are different options in this. For example, one is as mentioned here, Cascade. The meaning of Cascade is that if you the main table in which the primary key is, you delete the primary key. If you delete the primary key then it is not done like this. In fact, you delete the record because this is the primary key. So this value exists just once. The thing is this value is being referenced in another table. So in that case, what should DBMS do? Cascade means you move, you continue this change up to the next table. If your table is being deleted then all the days in which this key is being referenced as foreign key, delete all those days. See, this is a serious thing. Because it is possible that this record is referring to how many records. And secondly, on the next level, on the second level, which your records are being deleted, this is quite possible that the primary keys of this table is being used as a foreign key in another table. It can go ahead. So if you call this Cascade, then maybe its effect will go a bit further. So if you want the Cascade, then you have to think you have to be careful on how many levels it will go ahead. So Cascade means when you delete a record and the primary key of it if it is being used as foreign key in another table, delete all the records of that table in which it is being used. This is called Cascade. The second option here is no action. It means that if there is a single record that is referring the primary key of the record being deleted, then this deletion operation will be disallowed. Until you have any referencing table in which foreign key is declared, there is one record that is going to delete this record. This is referring to the primary key. You cannot delete this. Dbms will generate an error and it will be cancelled. And this is the case on update. These are the two options. Cascade and no action. The change you will do on a level, the same value will be copied to all the referencing record to the previous value. If you had a student ID S105 Now we have another table in which the reference was being used. So if you have turned S105 to S110 then when you have updated it, the first S105 will automatically become S110. If you realize how much assistance and help you have from the Dbms or even the data model, the relational data model the integrity of the data the consistency of the data keeps it maintained. So that if first as a developer you forget something Dbms will make it sure that you do not do such a basic mistake. So in this way you can maintain the integrity of the data model. In addition, we have another option, check key. Check means that you want to check in your parenthesis the expression that you want to check on this attribute and we will see its example in the future. Now if you look at the column constraint in the angle brackets then you will understand that the constraint and the constraint name are optional. So we will discuss what is the difference if we give this name but if you look at the rest then we have different options either give or give any of the primary key or the foreign key and check in the end. This does not mean that you can give only one of them you can give any of them two or three of them as we will see in the future. This is a very simple and basic command for creating a table. Here you can see that the create table command is written in capital letters but I am reminding you again and again that this is not required. If you write it in lower case or mix then it will not make any difference. If you look at the name of the table this is our table name and if you look at the reference utility or its purpose of the table we store the names or the details of different programs that are being offered in our institute. This is the purpose of the table and the attribute we have defined if you look at it as we have seen in the syntax column name attribute name and data type. For now we have not included any constraint in it. It is very simple. First is the program name PR name and if you give one space it does not matter but you will give one space. Next is character 4 Dear students here again a note of caution that you have to associate with any attribute again you have to consider that an attribute will contain in it what kind of value will come the length and both these things not for today but for some future time as well because this matters let us say in this case we have said that the program name is of four characters four characters so if you want to store five six or seven characters this attribute will not be able to store more than four characters so that is why you have to pay attention recommended is that whatever length is generally assumed if you want to give one or two characters extra so that you can handle any exception but if you are sure if you are given the surety that this is the length then you can bound it but generally we practice that the length normally expected we give some extra for example if we have a program name in general on three characters then we have done it on four apart from this next attribute is total semester and this is the type of tiny int because we need the smallest number smallest form of number to store the total number of semesters and if you look at all the programs that we offer the duration of it if you look at the semesters they are four eight or maximum can be 10 or 12 so do not store it in a big number so you will remember that the range of tiny int is from 0 to 255 so that is sufficient for us that is why we have declared it as tiny int if you look at the program how many credits are there then again the tiny int is sufficient that is also sufficient but where the declared is small int this is the command through which a table will be created the name is program and this is the attribute and this is the type how you have to work in SQL Server the first step is that you will install it the second step is in this you have a service manager through service manager generally you run the SQL Server instance in some cases you run it by default so that when you turn on the system when you log in when the system is up it will run but if you have not given the automatic option then through service manager generally you run the instance what you have installed you have run it through service manager these are the two things after that you will do the query analyzer now in the query analyzer basically you will give commands through which you will do different work for example this query database you have created it in the name of exam now you are working in the exam database working in it and in that you have on this command create table and create table program so you have created that table the lecture notes will have full details about how you have to do all these steps otherwise if you have the online help of SQL Server through which you can understand how to enter your commands so this is the command through which you have created a table create table for example with different options they are given see in this we have created another table student there are more attributes and all the attributes are student ID student name, father name address, phone, program name in which it is unrolled current semester or CGPA and then see different data types like father name character 25 current semester and like that so this is another example of create table next thing is create table with constraint and keep in mind one option is when you are going to create a table you can see all the structure of that table after that when we have seen physical database design at that time with every table and what constraints we have to impose you can write it but it is not necessary that you must know all the constraints all the things while creating the table first if you have missed something later sometimes need arises if you had to add or include then you can still do it so obviously it is recommended that before creating the table all the structures all the attributes all the constraints you must know about all these but it is not necessary it is not necessary that you think that it is a complex work that I should list all the things if you have missed something it is not something that is permanently fixed it can be modified now if you see you have the same statement create table student and please do not do it as I have done that first you create table then again say create table student and do not add constraint no one will do it obviously because you cannot create two tables with the same name if you have said create table student and if you give this statement then dbms will object it will not let you so on the same table I am giving you an example but you would do one of them now here I have said student id 5 and here I have said constraint and after that I have given st underscore pk and see here the case here do not confuse it let's write it in capital it would have been correct stpk now we have named a constraint as I had told you that name should be meaningful so I have done this st means student and underscore pk means primary key so it is obvious that this is the constraint of student key and before that I have written which is the constraint where we are going to specify the primary key now see as far as the name is concerned what is the benefit of this what is the use of it keep in mind that every constraint that would be included in a table will have its name if you do not even give a name to it because we have studied writing the constraint to word and giving the constraint name so if you will ignore it then the name will still be of this constraint but if you missed it then the name will be provided the name will be given by the dbms itself it has its own scheme so it will be named now the question is what is the benefit of this name the benefit is that you can later refer to this constraint by name if you say that first you have to delete a constraint then when it is named you can reference that name and do anything if it is not named then it will be difficult to refer to it that is why the logic of the name is whether you give a name by name whether you use the dbms name it won't make any difference and if the dbms name is also written then you can use the name which will be accessible this is your constraint the first constraint has been applied that is the primary key after this without giving a comma after giving a space let's say we want to add another constraint into the same attribute because as I told you that you have different options you can take multiple options here we have done one thing that we have declared the student key as the primary key the second thing is that we have given it the data type the third thing is that we have declared it as the primary key and along with that we have given it a name the primary key constraint that is what we have done here we want to apply another constraint onto that and the purpose of it the purpose of it is that till the length of this it can come but we want to restrict it for that we have the check constraint we will use that and what we do is we will check the values we will scrutinize what you will give on any attribute now what we have said we have said that the attribute that we are defining has been declared as the primary key STID like like is a reserved word reserved word means that you have to write it like this if you want to use it then you have to write it on this same place like we have given it a format if you look at this format we have written it that it was written on the course and you have written it as S means that you will enter any value in STID the first character should be S how do we define this constraint when we discuss the system with the organization with the people in the system they said that to distinguish the student's ID we start with S and whatever value goes into STID it starts with S so you have given it that check and then in parenthesis STID like like is a co-operator how? first you have given it S the first character is S then if you look you have given 4 different options i.e. first option second option third option after S we should have 4 characters because we have given 4 different options and every option is saying 0-9 i.e. one character will come here 0-9 i.e. 0, 1, 2, 3, 4, 5, 6, 7, 8, 9 i.e. in 10 digits it can come here the third character again option is 0-9 the fourth character 0-9 and the fifth one this means that although you have specified the data type as character 5 the character can come here but with this check constraint you have restricted it in data S S first then 1, 2, 3, 4 digits S, 5, 3, 4, 9, 5 S, 0, 1, 3, 8, 5 S, 1, 9, 3, 7, 5 the first character S then 4 digits what is the benefit of this see the consistency i.e. you have controlled that the data that will go into the database what will be the format as far as the data type is concerned it allows you A, B, C, D, E, F sigma, alpha, beta, bravo all these are legal character data and if the length is 5 then this is 100% acceptable as far as character 5 is concerned but when you post this check then you have a constraint that no character 5 is there but further it should be like this so you can see how you can check the fields and attributes of the database so that it has legal data legal with respect to the organization correct with respect to the system that is the data similarly here you see that after you have a comma i.e. when the check constraint is over that is the comma it means that now we are going to define the next attribute then there was student name and after space we said character 25 i.e. its length will be 25 type will be its character and along with that we have said not null this means that whenever you are entering a record of a student you will have to give it as null and what about STID LASMI because you have declared STID as primary key and primary key which cannot be null by definition means that when you enter a student's data then you will give student ID as LASMI because it is the primary key and you will also give student's name as LASMI because it has been declared as not null by a constraint similarly here we have written character 25 just this means that here we can give null we have text the address then we have character the student's phone and we have character 4 and we have given the current semester a small length and we have given the default values i.e. when we are entering the data and if we do not enter the current semester then by default its value will be 1 so if you have entered the value explicitly let us say you want to enter 1 even then you will enter 1 and that will be accepted fine but in the case of one value you can ignore entering the value you just leave that column blank or DBMS will automatically place 1 there however if you want to place another value like 2, 3, 4 or anything else in that case you will have to place it explicitly definitely in that case you will have to place it you cannot ignore that so the benefit of default is that the value that you frequently occur in the records by declaring default the advantage is that by entering the data you do not need to practically enter the value you will skip it so that value will come there after this the final attribute is CGPA so this is an example of the table student that we have created or we have created it with constraints let us see see here as last time when we saw the format we said that constraint and the name of the constraint they are optional as an example last slide we declared student ID as primary key and we said constraint stpk primary key so if we want the optional part that is constraint or stpk any constraint word as such and the name of this constraint if we want to skip these two then we can give it like that student ID name of the attribute character 5 the data type and primary key and the constraint name if you skip these two then this is also fine it will also work another example of create table in which we apply constraint and mind it I am telling you again when I am creating student table you do not have to do it again you will do it once but I am giving you an example on that student table see what we have done then see the same check see the same world and what we have already checked student like we have written it we have made it not null this is same as before this is same as before program name is same as before after that current semester and first we have done small end this time we have done tiny end because current semester can be set 12 or 8 or like that if we do small end we are giving it more space so we are sort of wasting our storage this time we have done tiny end and we do not have to underline it I have done underline only because you can see that it has changed then in this we have done CGPA as we did before now another table see create table semester the purpose of semester is that the semester table which you launch once it stores data about it for example we say summer 04 summer 04 means when it will start when it will end so this data will be stored in the semester table in this semester name character 5 primary key fine start date small date time like this end date small date time here you see that we have added a constraint now this constraint is not an attribute constraint this is a table constraint the difference between attribute constraint and table constraint is attribute constraint is on a particular attribute but table constraint is on a table or on multiple attributes like this we have a constraint the purpose of this constraint is that our semester record the purpose is to store the semester name and start date of the semester and end date of the semester now our real life system when we run the semester system in any place normally a semester from 14 weeks 15, 16, 17, maximum 18 weeks your semester goes now in one way to control this that the person entering the data does not enter such a date which is practically not possible there may be a mistake at some point this semester start date and end date when you enter that semester you will feel bad like the semester of bad that was only 2 weeks or first do it as if a semester is a few years old if we had to say that it started in January in January let's say in 04 and it ended in May in 05 now in the wrong place in May first do it in August or September or in May in May 04 he or she enters May 06 because of that your semester instead of 5 months becomes 2.5 years old again this is something that makes the database incorrect your real life your real life system cannot be that long but when your data base your semester is being represented when it is so small and big to control this to catch this possible error and mind it no one will ask you to add this kind of constraint this is your own thinking you will think that it may be a mistake why not to check it you have added a constraint here which makes sure that your date center the difference between 14 to 19 weeks should be maximum when it is less even then your dbms will object and if it is more even then it will object because this is not illegal value so here on the screen which you can see here we have imposed that constraint but one more thing this constraint is being used using the features of the SQL server because in this we have used a special function which is called date-diff date-diff means it gives you the difference between two dates and what it will give whether you take the difference in weeks whether in days whether in years whether it is a date-diff it is a function which provides you the SQL server and the format used here is the date-diff this is the name of the function and then the week whether you have to give the weeks it has start date, end date which means the date-diff function between start date and end date which means you have given a date of January which is the start date and then you have given a date of April which is your end date so the date-diff function is between these two dates how many weeks it will give you a number you have checked that between 14 and 19 which means between the two dates the value should be between 14 and 19 look again how beautiful it is that you have added a check in the definition of that table through which the data you are entering on the database you have checked it so you have reduced the effect of the error now it cannot be that your two dates should be entered that they should be very small and very big and interestingly you have covered another mistake that from the mistake your starting date should be given and the end date for example you say that the semester started in January 04 it should have ended in May 04 but they did it wrong in May 03 now what is happening in this that the end date you have reduced the start date so this check constraint will also control that and because the difference in this requirement should be between 14 and 19 so if your end date is small then this check your value enter will raise the error dear students now it is time to conclude our lecture number 26 in today's lecture we have started the DDL Data Definition Language part of the SQL there are those statements through which we create the structure of the data and make different changes in it till now that is create command or create command we have created the database and also the tables when creating tables the create command is used to create table to define the attributes of the table to assign a data type to the attributes and also to impose different forms of constraints on the attributes we have included default and also we have seen the example of check and from this you can see the beauty of this is that the data which is entering the database is going to be checked and in that some kind of mistake or some kind of error is controlled create command and the rest of the SQL we will read in the next lecture