 Dear students, today we are going to discuss lecture number 28 of the database management system course. The course code is CS403. This lecture is the continuation of the previous lecture where we are discussing the data manipulation languages rather the data manipulation part of the SQL. This part of the language concerns with the manipulation of the data. The statements in this part of the language they are used to enter the data into the database and to perform different type of operation and to retrieve the data from the database. This part is I think more interesting than the DDL because here you see the practical movement of the data. You see things moving into the table and getting the data from the database. So I think this you will feel it also more interesting. So in the previous lecture we studied the format of the insert statement and as I told in the previous lecture that the insert statement is used to enter the data into tables. So we have not seen any practical example. So we will start today's lecture by discussing an example of the insert statement. This is the table that we are going to use to discuss different examples of the insert statement on this slide. So for your convenience I have given the attributes in the table as well so that you do not have to understand what data we are going to enter and how to use the insert statement. Now if you look at the first statement here, what we have done here is that if you look here, then insert into is the same part that is required, that is we have written the format in the capital, which means that we have to give it as a Although into is optional, if you like, you can skip into the statement will still work. Anyway this is the table name course which we have written on the structure here and look here in this case we have skipped the attribute names because we intend to give the values of all the attributes. That is why we have written the values here and we have skipped the attributes here. Here we have one, two, three, four values because there are four attributes in the table. You see course code, course name, course candidates or program name. So these four attributes were basically included in your table and you have to enter this record because you wanted to give all the values of all the attributes that is why you first of all you skipped the name of the attribute but the second thing that I told you last time was that in this situation the values you have to place here one thing is to keep in mind that you have to give the value of all the attributes, that is if you have the table in the original table, if you have four attributes in it, then you have to enter the four values here. One thing is that when we defined the table, although this is not the definition of the table, when we defined the table, at that time we defined the course code and then the course name, then the candidates and then the program name. So now while entering the values, we have to take care of this order of this table, that is we have to come to the first number, the course code, like here is the course code, after that the course name, we said that it is an operating system, so look at this and apart from that the value of the text type whether it is a character or a bar chart or a text, you have to write it on your course. You have given the start of the value, look at this, the start of the value and the end of the value, the start of the value and the end of the value. Now what we had was the course credits, because it was declared as a number attribute. So the value in it is a number and whenever we have to enter a number value, no type of number in the number, whether it is integer, small int, tiny int, whether it is real or float, that we will enter without the quotes. So we will enter the value in it and one more thing you have to take care of that if it is a real value or float value, you can give a decimal point in it. But if you have declared it as an integer type, then obviously there you will not give it a decimal point, but just a whole number. So when we entered the value, the course credits, so if you see there, at that place we have given a number here, 4 and without quotes, because this is a number value. And after that, because we have the last attribute, the program name. And the program name again is a character type data. So here we have given it in the course, like Vinay general rule, you had told that whoever you have to give as a character, the text type data will be given in the course. So in this you see that we have now inserted a row. In this, whatever you have told me, I have taken care of it. And especially one more thing, you have to see that the value of the primary key should be unique. So in this you have the course code. You have to take care of its value so that it does not repeat. Now here we have another example. In this case, what we have done is that we should enter the value of two attributes here. Instead of giving the value of four attributes of a record, we said that right now we only have two attributes of value, so we are going to enter these two. So insert into statement provides you this facility, this option. So you have to do all this. For example, what you see on the screen, that here you have done the same, insert into, and here the name of the table is course. After that, here you have given the name of the attributes, course code and course name. Because right now you want to enter the value of these two attributes. And mind it, the two you are skipping, in this case you want to skip the credits and the program name. While defining the table, you should not have defined them as not null. Because I have already told you that the attributes that you have declared as not null, while entering the data, you must provide the values for those attributes. Since when we defined the table, we did not declare them as not null, and by default your non key attributes can take the null value. That is why we specifically gave the names of those two attributes, i.e. course code and course name. Then we said the values as we said here, and the value of the course code, CS316 and database systems, we entered here. So in this way, you saw in this example that we have entered only some specific attributes, only their values are entered, instead of entering the value of the whole row. And there is a difference between these two. You saw that when you want to enter all the values, in that case, you do not need to give the attribute list. Whereas if you want to enter some particular attributes, in that case, you have to give the attribute name. But again, you have to take care of the order as well. The attributes that you have in the list, the names of the attributes that you have, you have to keep the same order by entering the values. Let us see another example. Now if you see in this example, here also we have said that we are entering the values for all the attributes. Because we are entering values for all the attributes, that is why we want to skip the attribute list. And one thing, if you are entering all the attributes, even if you give the attribute list, you will not object to the DBMS, but that would be something that is not required. So you will do extra effort on that. So in that case, when you want to enter all the attributes, in that case, it is better to skip the attribute name but it is obvious that in that case, you will have to take care of the order of the values in that order. In this case, the third code that we are entering, obviously we have skipped the attribute name because we wanted to give all the attributes. So we skipped it. After that, first of all, we gave the course code. Because it is a management code, this is a code with MG. So MG103 is the course code. First of all, intro to management, this is the course name. Now here, null, null. We have entered two null values. Now see, this is also a way that if you do not want to give any attribute value, then you can also skip it in this way that you have written the null there. So these two things are similar in one way that you have given the attributes name here and skipped two. You have not given the attributes name here but the attributes value that you wanted, if it was required, then you have nulled them there. So you can also adopt this method to enter the value in the attributes. So these are different examples that you must have understood how we use the insert statement to enter the value into the database. Now see, every insert statement as a result of the insert statement, one row, one record or tuple is entered into the table. So in this way, the record you want to enter, you will use the insert statement and the values will be placed there and you will enter the data in it. Now what you can see on the screen right now is the example data that we have I have shown you a few of the tables here. The examples that we will use further, we will use these tables and this data. If you see a little difference in this that what I have written here they are different from the ones that we have just used. The reason was that based on which I have quoted a little different course here. Otherwise the rest of the data is exactly the same that we will enter through the insert statement. Most of the data that is in different tables, this all has been entered using the insert statement command. So when you want to insert it, you will say insert into student. And one more thing is that here I have less attributes of the student table because of the lack of space. Otherwise when you enter the data you will have to enter all the attributes. Similarly this is the program table this is the program table. This is the course table and this is the enrolment table. And if you look at the enrolment then here is the student ID course code and semester name. These three are the primary key and the rest are the semester marks written here. And the final exam marks are written here. And this is their total. And based on this total this is your grade and this is your grade point. So how did you enter the different students data here and based on this data we will perform the further operations. Dear students, from here we are going to discuss the select statement. As I told you earlier that the select statement is the most used in SQL because see create statement is used to create table of different objects. You do it generally once. You create a table once. If you want to change it then you do it through alter. Otherwise create statement work is over. When we talk about insert we perform the insert statement for each record that is there in the table. If you enter 10,000 record in the table then you execute the insert statement of 10,000 times. But the thing is the data you access and process you do it many times and do it in different ways. For all those things the statement that is used in SQL is the select. There are many variations in it and that is why I am discussing it in different parts so that you do not confuse it. As you can see we have written maximum used command in the DML and it is used not only to select certain rows but also the columns. There is a girl who we were discussing relation algebra. It is used to select certain attributes and select operator is used for the selection of rows. The situation here is both the work means the selection of attributes and the selection of rows for both the work your statement will be used is the select statement The difference is when you want certain attributes and when you want certain rows you select them by giving a condition. The difference is the command uses the select statement. It is also used for different forms of product, that is different joints. In relation algebra we used Cartesian product and then we used different types of joints for all those we use the select statement. Selecting rows from one or more tables. This is the format of the select command. Again I have told you that I will explain it very simply. For example here is the select statement written in the beginning. Select is a word and that you have to give as such. Case does not matter with the same spelling. In curly braces you have to give one of them. Here it is written star or starisk. Star means if you select all the attributes of a table rather than writing the names of all the attributes you simply place a star. When the star comes it means it will automatically understand all the attributes given in the table. If it is not then here it means it is optional of course. You can also give a column name. If you select one, two, three, four certain attributes from that particular table then you will give a column name. In square brackets it is written as comma dot dot dot n. It means if you have to give multiple columns then first column name comma, second column name comma, third column name and like that. What are the options? Either you will give star means all attributes. Either you will give particular columns name first column comma then we have from table name. It means it is written in capital letters that you will give it as such. And table name because in lower case you have to give the name of the table that you have to fetch the data from. So this is a simplified rather very simplified version of select statement. Let's see some examples. Here when I have written a query means you have to access some data obviously no one will give you the SQL query. They will tell you the requirement that I want this. You will transform that requirement into the SQL command. That is why when we will discuss further the format is the query. The query is get the data about students. Remember this from the point of view of exams and in general even in real life when you are asked to get the data about something as we have said get the data about students. So from this generally we have that you want all attributes. Otherwise if someone wants specific attributes normally the names of the attributes are mentioned specifically in the query. But the query the attributes names are not given specifically. In that it happens that they will tell you to give the names of all students like this. Here they simply say give the names of all students. But with the attribute name you have to map it yourself. It is obvious that the user does not know what is the name of the table or what is the name of the attribute store. This is not the concern of the user. The user simply says I want to see the student name and student address and student father name. Now he has to describe it in a general language. This is your responsibility as a designer as a DBA to transform that query with the table name and with the attributes name you have practically used with the table name. As you can see here what we have done is the query we have made is simply to get the data. That means all the data. That is why we have said select star. If we want to make our work difficult by ourselves then the user can say I want to do something special. He can start typing all the attributes. For example if we have 12-13 attributes we can start typing. The first, the second, the third type. That is also fine. But DBA does not object otherwise it is a waste effort. So anyway you said select star means all attribute from the reserved which you have to give and the table name this is your an SQL select statement which is from the student table all attributes and all the rows. I had told you that we select particular attribute by giving the names of the attributes. When we did not use an attribute name here it means we want all attributes. So star means all the attributes. The second is which rows will come. Why? Because we restrict the rows by giving a condition. And the clause used for condition is wear clause. We do not see wear here. It means there is no restriction on rows. So what will happen? As a result all the rows will be accessed. So on the basis of star you are accessing all the attributes columns. Because there is no select condition there is no condition, there is no wear clause on the basis of this you are accessing all the rows. So the output of this statement is on the screen. This is the output of all the attributes. If you look at all the attributes above this for example student ID, student name, father name, student address, phone, program name, current semester and CGPA. These are attribute names of the student table. All the other attributes like 1,2,3,4 for example data base. At this time we have got 8 rows 8 records. Because we have given star all the attributes and because you have given no condition all the rows will be accessed. And you will remember when we said when we read the relational data model we studied the basic properties of the table. So one column has the same value of domain. If you look at the text there is a father name. If you look at the phone number the student phone has a column. All the values if we have 8 records if we have 8,000, 8,000, 8,000 records then the format is that the student phone you get under this is the phone number. This is an attribute which shows the current semester. So in that you get something like for example this is in the first semester Amjad's father name Hussain because in the first semester he hasn't got the CGP yet. So it contains annul here. The record number 7 where we have the student address and the null in the phone. Because we have declared these attributes as null these attributes may have null values so maybe we don't have available. So we haven't entered it yet and that is 100% legal. We have accepted that. Because your student ID is the primary key. That is definitely required. So that must have been entered. And the student name when we defined the table we declared it as not null. So when you enter a record in this case we have entered a new one. Because we didn't say it was not null it means we can move null into those attributes. So we have nulled it. I hope that you have understood the select statement and its link with the output. The next query again the user has specified his requirement in the simple language. The user says give me the name of the students with the program name. You can see here that the attributes you have defined in the tables have not been defined. The second thing is you haven't mentioned the table name. The user simply described his requirement in simple plain language. And if you think a little you will realize that when you are collecting these queries the table has not been defined and these queries are collected during the analysis phase and in that way you haven't created the table. Anyway, the query here is called give the names of the students with the program names. From here you will see that it is correct. Because we need data about the student which means we are going to access the student table. And which attribute is not mentioned but the particular attribute is called name and program name. You can see that this one said that select student name now you are using the attributes name specifically. That attribute name that you have used in the table you have to use it. You said student name and in the same way as I have defined ST name, PR name, program name from student. This star means all attributes. And the second example is where we have used particular attributes. If you look at the output of this this is the output. If you look at this simply in the last example you are looking just two attributes. In the first column you have student name. You can see that the column label is ST name and the other column is program name. All the labels are there and the rest of the data are rows. Because you have restricted only the attributes. But there is no condition on the rows. So you can see the eight records because you have limited only the attributes and not the rows. So you can see on this screen all the records only these two attributes are the values in front of you. Let's go ahead. You have got another option in the select statement that is to assign or to allocate an alias to an attribute. Alias means which we call as Urf here I have written Urf in front of it. So when you define the attributes you have used abbreviated names. Which are meaningful. For example, ST name CR code or S code. You can understand this or if a professional can understand that ST code means STID means student ID ST name means student name STF means student father name. But it can be that your layman who is not familiar with computer technology can understand what STID means. So there is an option when you take your output show the data but the attributes that you have just for the sake of output you give a meaningful name and we call that name Urf or alias as you say like I said Zaheer or Fajhara Now Zaheer's salat is Zaheer but he doesn't know Zaheer because people know more than Zahre. So the same thing is to give his name which is more understandable or more easily understood or more common we have to attach an alias in the output your attribute name will have no difference it will remain the same the alias will be used only in the output One reason to use alias is that your abbreviated attribute names can give a meaningful or explanatory name One reason is that sometimes you also give some calculation or some expression in the select statement You want two attributes one is the salary basic salary and the increments and deductions We have three things employee's basic salary increments and deductions These are the three stores along with the basic salary increments and deductions we will write select basic salary increments deductions These are the three attributes What is their final sum? we want to display basic salary increments deductions This is the gross salary When you include this expression in your select the attributes of the three tables will give the same name as the tables But this column does not have a column so it will not have any name This type of expression where you are calculating and in the case of calculation there will be no name of that attribute whether you are using all the attributes on the table Such a column generated that is being computed You can use alias to give it a name This is a utility a feature to give a user friendliness so look at this example This is the format select star In the case of star you will not give alias You cannot give alias This option is in the same case when you are giving column names If you first want to display all the attributes of the table and you want to use alias with everyone then you will have to give all the names because alias option is only with the column names list So in the case of star then you will give column name and look here square bracket means this is optional The first thing is that it is not optional to use alias that you do not give alias this is not required but even if you want to use alias even in that the as is also optional i.e. alias is optional and even alias is optional you can use column name space as you can skip if you want and then alias because column name and alias are in lower case this means that you have to give them yourself so this is how you said column name as alias for example you said st name as and in quotes student name the attribute name is st name but the output which will label it student name and you will write this on the course and then you can repeat this and mind it it is not necessary that if you have given 6 attributes in column name then if you have attached with alias then you will do it with everyone if you are taking 6 attributes then you have attached with 2 if you have not done it then you will have the same name which is practically in the table let's see this example in this we have given a select statement we said that select st name as student name this query is the same which we discussed in the last slide where we said that we need student name and program name this is the same query student name is attached with alias as we said student name we have used this option as here it is written in capital because it is a general format since we are giving a practical statement then it does not matter in capital or lower case as such then in the course student name see this alias is this attribute student name after that we have given comma so comma is here pr name and pr name is the name of the attribute in the database and this time we have skipped the as after skipping as we have written a program which is basically an alias for this attribute and from student we have set the table and when we look at the output here we have seen as far as rows and columns and they are exactly the same the only difference is that this time the columns have different headings they have this heading due to the alias it is a good option the place where you need to explain things you can use it as I told you alias use is that when we select an expression there is an expression you are calculating there is no single attribute so from the SQL point of view this particular column has no heading, no label in that case to give this expression you use alias but from here it is obvious that you are using expressions like if there is numeric data there will be plus, minus if you have text type data then when the data when the operations are involved you can get it displayed with the help of the select statement it will be for every row there the expression will be evaluated there will be a solution and the result will be displayed and if you give it a heading then it will come under and if you do not give it a heading then it will come without heading but the expression will be placed there let us see this example here is the example of the expression in the query and here again we are going to use an expression in it or what attributes are being used in the query the user will simply give its requirement here display the total sectional marks of each student obtained in each subject first of all what is the total sectional marks that is the sectional marks which we have stored in the table these marks are those marks which the teacher has given you against the activities that he asked you to perform during the semester like quizzes and assignments there were also the marks of the exam that you gave in the exam so this number is practically stored in the table as the sectional marks and the witness marks but when we say total sectional marks that marks that we got during the semester there were both the activities we obtained the number from this see how we will write the query now here is the query here you have written select stid and course code the requirement of the user it does not specifically mention the course code and the student id but here as a designer, as an analyst you have used your own description and you have placed them there in this course code this is the performance where you may feel that why we are not putting the student name and the course name here the reason is that to access the midterm marks and the sectional marks we have to access the enrol table whereas the name and course name they are in other different tables so if we want to do this then we will be accessing multiple tables this is a thing that we are not familiar with that is why our concern is that is only accessing data from a single table that is why we have given student id and after that there is an example of the expression what is midterm i.e. mterm plus sectional marks this is an expression there is an operator and this is a numeric operator and since this is an expression it is not stored in the database as such or it does not have any name in the table so there will be no name of this column because this is your query it will go on every row it will go on the first row from there the student id will be displayed if you do not give alias then this will be the heading bin after that from the first row it will take midterm marks and sectional marks it will be displayed this activity will be performed with all the rows and all the rows you will get but since this is an expression there will be no label to give it a label the alias it is meaningful to explain this is total out of 50 and from enrol because all the data was in enrol see the output here is the output if you see since we have enrol we have only 4 records in this S1015 is the student id and since we did not give alias here we can see the attribute name after that again no alias used so you can see that so you said that student 1015 enrol in the same way 105 enrol in the same way S1018 and 102 these people enrol total out of 50 that is written as such and all the midterm and the session marks are different so in this way how we used an expression and through alias we have given it a name temporarily we have another example of expression in this list the name of the students and they are enrol in the format student studies in program that is let's say Ahmed studies in MCS or Mushtaba studies in BIT so the output you want is in this particular format so again if you see expression is involved but this time expression which we have seen in the last example of expression that was a numeric expression in which numeric calculation numeric operator was involved but in this a different type of expression is involved which is basically a text or string expression see we are writing here case student studies in program so select S.T. name S.T. name plus this plus is between strings between character strings plus is combined with this sibling concatenate first string second string third string so this addition as you will see in the example and one more thing to combine strings in the SQL server we use plus operator when we use plus plus symbol or the symbol of plus precisely what action it will do it depends on the parameters on which it is operating if it is numeric then it will simply add plus number but if it has plus operation if it is strings then it will simply append this for example here S.T. name plus there is a string constant string constant means it will come like this every time that is second name this is the name of the attribute after this if we see the study we have a constant it is the same without any change we have thousands of records thousands of times and then we have plus it will add PR name program name although it has two attributes which one? program name two attributes are available and there is a constant but all this is a single thing because columns when you multiple they are always separated by comma till the time the column name is not common till that time they are not considered as two different attributes here they are used in a single expression when your output comes it will contain a single column but the value of these two attributes will be used how? here if you see this place is written S.T. name this study is in this is your string that you place in the expression and it is simply added and MCS is this program name this value which is written here taken from the database and all this is also being taken from the database and from different rows as you can see in the student table we have got eight rows so here you can see all eight rows but only the selected column from those eight rows but here they are not appearing as two different columns rather they are being used as the values in an expression and here all that expression is coming because we did not give any alias I did not even show it from this point of view but this is now being considered as a single column and because this column is not stored there as such it is being computed this is an expression so here there will be no name we had the option that if we wanted we could give it a name which we did not give the next option we are discussing select distinct distinct means that when you take an output then sometimes it happens that if you are taking a particular column for example if you are taking a name or for example we want a program named display from the student table since multiple people have taken an MCS program then you will get an MCS name again and again so if you want that you do not have a duplicate then you can use a distinct option see and here is the query get the program names in which students are enrolled now see in this we have some programs we have one course but the one in which the students are enrolled so we will not get this data from the student table because we have which students are enrolled so the query we made is select program name from student if you look at the output since we have 8 students in the student table so if you look here we have 8 tables and above we have given it as a program we used it as an alias it is not written as a command we have given it as an alias so we are getting 8 here now see since multiple people were enrolled in MCS so 4 times MCS and 2 people enrolled in BIT so this is the purpose but we do not know how many different programs are enrolled we will have to remove so for that instead of saying select program name from student the last one is select distinct program name from student now see this output so this is the output since we had it before now see here what we have on the left we have got 4 rows in it since we have 4 rows all the programs are enrolled every row this row has only one attribute and this Prana output we have got 8 rows because we had 8 records and with every student's records the program enrolled so this is the difference using the select with the distinct option or without the distinct option dear students from here we are going to the course step there is a clause of the select statement whose objective is we place checks on rows since I have already mentioned that when we have to select some attributes then we do that by simply giving the names of the attributes but that we cannot do with the rows because there are too many rows so we cannot give the names of the rows so to restrict that we use the checks and the clauses we use are the where clause so we will add now the where clause in the select statement see how we will do that limit rows to select certain rows like programs of certain length when you say as I am talking here on the program table the program whose specific length semester wise or credit wise students with particular names show this term to the students or those students who are enrolled or those students whose age or those students who come to a specific place on the basis of these different conditions you restrict or select and in that what you use is the where clause and it is written that we use names to limit columns but rows cannot be named due to the dynamicity we limit the rows using the where clause conditions are defined on the values of one or more attributes or more tables and placed in the where clause that is when you restrict rows then they are restricted on the basis of the values of certain attributes see so far you did not give the where clause or you did not place any condition on rows the result is that all the rows in the table they were being displayed so now you will place after that you will place a condition on the value of one or more attributes and that condition or that value will be compared in every row will be checked those rows which fulfill that condition those rows will be selected and those who do not fulfill will not be restricted now it does not matter how many rows are selected as a result of a select statement it is possible that one row is selected it is possible that two rows and it is possible that in some cases all the rows are selected it does not matter the rule is simple that you give where and you give the condition the same rule is that this condition will be checked against each row in the table and those rows which are displayed will not be displayed Dear students today's lecture we will wind up here today's lecture time is over in this we have studied the different forms of the select statement and in this we have seen how we can access all the rows we can select particular attributes we can select all attributes we also studied the option of what is the use of alias and what is the use of alias and what is the output of alias in the next lecture we will start studying where clause and there are many other things for example how we access the data from multiple tables and how we apply functions all these things we will study in the following lectures