 So, good afternoon to all, I am Anup Naik, I am a research assistant under Professor Fartek. So I will be covering SQL today, mostly SQL and some features which are special to my SQL. So first of all what is a DBMS? DBMS stands for Database Management Systems and it is a collection of interrelated data. It is a place where we can store large amount of data and it also contains a set of programs to access these data easily efficiently. So database here refers to a collection of data, huge amount of data for a small amount of data and all it is used but it can be effectively used for very large amount of data. So it provides a convenient and efficient way to store and retrieve information. It stores information not data, it stores some information that is some piece of data which has some value. So that is known as information. So it provides a convenient and efficient way to store and retrieve information. So why we are using database management systems? So these are the file systems can also be used for storing data but what are the disadvantages of file systems? Data may be redundant and it is inconsistent. There is difficulty in accessing data, data isolation, there is integrity problem, there is atomicity problem. Atomicity means I have opened one file, some others have opened the same file then there will be conflict in I am writing something, he will not be able to read what I write at the same point of time. So those problems can be solved then concurrent access anomalies, then security issues, there is very less security in file. Let me go into SQL now, what are the basic types? Basic types of data which is stored in SQL, it can be car, wire car, wire car is a variable length car, int, small int, numeric, real double precision float. So what is the difference between car and work car? Like I already told you it is a variable length car. So what is the advantage of car when we are considering work car? I know there are advantages of work car over car but what are the, when we are using car over work car? When we know that the string will be of specific length then there is no need of using work car because work car, work car takes some time for computation, there is overhead on the database for processing work car. So when we are sure that the string will be of specific length then we can use work car like in case of code numbers or pin codes or male, female, f, m. In those cases there is single character and we are sure that nothing will be there except these two. So in those cases we can use car. So now how to create a table? So this is the, this is the syntax of creating a table, create table. So when we are considering a table, so these are known as attributes, columns are known as attributes and these are known as rows are known as tuples. ID and name refers to attributes and the rows, the data with data which we store those are known as tuples. So this is the general terminology which is used and now, so create table, A1 is an attribute, D1 is the data type of that attribute and so on how many you can give as many number of attributes that you want and there are many other integrity constraints like primary key, foreign key, all those things. So here R refers to a relation, in database tables are known as relations, the terminology used is relations. So this is an example of how to create an instruct, create a table instruct. This is the syntax of how to, I mean this is the command of how to log into mysql. So mysql minus u whatever is the root, I mean whatever is the user name, currently I am logging in as root and minus p prompts the user to enter a password. So I have entered into this one mysql. So now I want to know how many databases are there, so the command for that is show databases. So it will show whatever database I have in this computer. So actually the concept of database is like there can be many tables, if we want to categorize table into some specific things like for example all of you might be familiar with Moodle or okay nothing. Suppose I have many tables related to students, so I will be grouping them under students database, suppose I want to, I have some other zoom law, so I will be categorizing those tables under zoom law. So it is just categorizing tables under specific databases that is all. So I have to specify under which database I have to go first before using or creating or any doing anything related to tables under. So the command for that is use whatever test, so I have changed the database to the test database. So I can see whatever tables that defined over there show tables. So these are the two tables in that test database. So now we will do one thing, how to create a new database, this is the syntax for that. So we will name it as interns 2012. So I have created one new database interns 2012 and I will be using that during this session. So I am in that, currently there are no tables. So how to, now we will see how to create a new table. So I have written some queries over here, so I will be just copying those queries. So this is the query which I explained to you, create table, instructor, id, id is the first attribute and it is of char5, name which is of varkar20 and which is not null. This is the constraint which I have mentioned to you, the constraint is that it should not be null. So then department, department number which is of small end and salary which is of numeric 8 comma, I mean 8 comma 2. This refers to, like it can be 8, 8, 8 digits in that out of which 2 are decimal digits. So I will be just copying this to, so I am going to execute this query. So a table has been created. So if I want to know at later point of time, when there are large number of tables, I might not remember all this, the structure of all the tables. So in order to know the structure of tables, the command used is d e s e table name. So this will give the structure of that table. So now it has these things. Now we will insert some values into that table. I have given this constraint null. So there are now 2 rows in that table. So how to see that? So these are the 2 values in that. Now you can see that ids of both the names are same. But in most of the cases like roll numbers, all those things are unique. So in order to specify those, so this is how we have created one table and inserted values into it. Now what is a key? There are many types of key, super key, candidate key, primary key, foreign key, all those. So if r is a relation, r is a relation table, I mean a table and a 1, a 2, a 3 are the attributes of that table. So we define a relation schema as r is equal to this one. And then if k, k is a subset of r, that is k is a some attribute, k can be some column or a collection of column, anything. So k is a subset of r and if k is a super key of r, if values of k are sufficient to identify a unique tuple of each possible relation. For example, the meaning of this sentence is like using roll numbers, I can identify, I can distinguish between each tuple. There will be only one tuple which has that corresponding roll number. So in that those cases, that roll number is known as a super key. And so as I have told you, id is a, id can be a super key, id and if id is a super key, then id, name is also a super key. Even if the name repeats, id is unique. So id and name is a super key and so on, we can have as many sets we want. So minimal super key is a candidate key. In this case, id is a minimal super key. So there can be many other things like phone number, may be phone number, if a family contains only one member, then phone number is also a super, I mean super key and also a candidate key. So and what is a primary key? The user can select one of the candidate's key as a primary key, whatever the user can decide upon that. But that should be a unique thing, I mean it should not repeat, foreign key. So foreign key, what foreign key is, like I can refer one attribute from another table, I will explain to you. Suppose consider the same table for id, name and department number suppose and there is another table, department number, name, department name. So in this case, I know that department value, department number will be one of the, one of the, one which appears in this table. So this one can be given as a foreign key to this one. But there are some constraints of like, I mean before, there can be some problems when using this foreign keys. Like that value, if suppose I am entering some value over here, suppose department number two, an op id one, that department two should exist over here. If there is no such department, it will show an error, it will not, the database will not allow you to insert a tuple. So that should be there. Always before inserting something over here, make sure that that value is over here also. So but one interesting case comes over here. Suppose I have a something, a table structure like this and I have a column hod, hod id. So this hod will be, suppose this is instructor, this is department table. So there is an interesting case over here, hod will also be an instructor. So I can refer, I can give a foreign key like this also. So then what is the problem over here? We cannot insert even a single, single thing, single, single tuple in this. If I insert over here, it will check whether first time, if I insert over here, it will check whether it is there in this one. If I am going to insert over here, it will check over here whether it is present or not. So for those cases, there is a special, what to say, a special facility you can say. We can initially defer this constraint. Similarly we can say to the database, defer it for some time, later on, I mean enable that maybe. So while creating, while creating, while creating a table, you can specify such a constraint. So I will give this example at later point, I will post it on model. Now these are the, these are the integrity constraint which can be there in a table, soft null, primary key, foreign key and how to, how to make a, how to make a attribute primary key. That is, that is why this, this instruction, sorry. So if you want to, if you want to make a attribute primary key, then you should give like this primary key, whatever is the attribute over here or you can do like this also. You can, before this comma, you can give primary key. So and then how to, how to make a, make a, how to make an attribute foreign key. So this is the foreign key, sorry, I mean, I change this, this, consider this as department number. So this, this is department number which references to department. So it go, it checks both the names of both, both these, both these and it knows that this is a foreign key of this. Primary key declaration on an attribute automatically ensures not null constraint. So how to, how to drop a table? Drop and delete should be used carefully. So drop table completely removes the table. It removes the structure of the table also and the delete table only removes the content of the table. Structure of the table still remains. First of all I will, what is the problem? Okay, okay. Sorry, sorry. Delete from table, table is required or just table name. Yeah. So this deleted the table. So now no rows are selected when, when we are selecting that. But DEAC will still work. So you can see the structure. So now this will not work. So how to, how to alter a table? You can add columns to a table at later point of time if you want. So alter table or add an attribute A of D data type. Okay. So where is, yeah, as I explained to you. So how to, how to, how to delete a, delete a column from this, from a, from a table. Alter table drop, whatever attribute you want. So how to update a table? Update table name, set salary equal to something. I mean whatever salary I just, this is just an example. Set salary is equal to something where some constraint ID equal to something. So this will, this will set the salary of that person and then select query. Select as I, as I already used that query, select whatever attribute, select query is used to display whatever you want, whatever specific part of the table you want to display. Just now I used select star from table, select star from instructor. So star is like, it will enable the, it will, it will display all the, all the, all the attributes of the table with data. So select, if you, if you want to selectively select some attribute, select A1, A2, AM from what, whatever relations you have where some constraint. If you want to specify some constraint, you can specify like where salary is greater than 70,000 or where department is equal to something, may be anything and distinct, distinct keyword is used to select only the distinct tuples of that table. If I give distinct, it will, it will, it will hide all the duplicate entries on, from that table, all will display all the tuples of that table. So this is one example, select name from instructor where department number equal to something and salary is greater than 80,000. So this is, so here I am displaying only the name of, name, name from that table. So this is one of the main, important part of SQL, Cartesian product. Suppose if you want to, if you want to combine two tables, like in this case, suppose I have ID, name, department number, but I do not know which department this person belongs to. So we can make a Cartesian product of these two tables. So what Cartesian product does is, it will, it will match, it will match, you can, you can see over here. So this is, this is one entry, it will, it will pair this entry with all the other entries from this one. So you can see this is paired with this CS101, CS315, all these entries and second entry, second entry in the same manner and so on. It will, it will pair all the entries. So if there are 10, 10 tuples in first, first one and 20 tuples in this second one, so there will be about 200, 200 tuples in the Cartesian product. So but what is the use of this Cartesian product? As I, as I told you over here, this does not make sense, this does not makes much sense, but if, if you want to, if you want to know which, which instructor takes which course from this table. So I can, I can do one thing. I can, I can equate this column as well as this column. So if I, if I equate this column, see, only these entries will match, only these three entries will match and all other entries will be discarded. So I can know which person is taking which course. So as I told you for all instructors who have taught a course, find, find the names of, find their names and the, yeah, names and the course ID of courses that they have taught. So this is, this is how we write a Cartesian, Cartesian product and this Cartesian product with, with equating whatever we want. So that is known as JOIN, JOIN. So select name comma course ID from instructor comma teachers. These are two tables, two relations where instructor dot ID equal to teachers dot ID. So it equates instructor dot ID and teachers dot ID. So but if you want to make a Cartesian product of same table, so there will be problem in this case, isn't it? If I want select name comma course from instructor comma instructor, then how will I write over here? Where instructor dot ID equal to instructor dot ID, what, it does not make any sense. So what, yeah, we have to write over here as table, as, as some, some alias name. Instructor as T, comma teachers as S, something like that, where T dot ID equal to S dot ID. So for computer science people this may seem very simple. So we can rename, in the same manner we can rename all the, we can give alias name to some, some other, some other attributes which we want. Like here I have done a calculation over here, select ID comma name comma salary by hunt, by 12. So suppose if I want to know the monthly salary, salary by 12 as monthly, monthly underscore salary from instructor. So it will, it will make, make this calculation and name it as monthly salary. So yeah, the specific case which I, which I told you, find the names of all instructors who have a higher salary than some instructor in the department 101. So here we have to make a Cartesian product of the same, same table. So this is how we do, select distinct if you want, distinct T comma name, T, T dot name from instructor as T, this one and whatever predicate. This is known as, this is known as the predicate part, whatever conditions we give that is known as the predicate part of it. So yeah, now how to, how to make, how to match a string. Suppose if I want to display names of all the people who, where the name begins by A. So these are the two operators which we use, which we use, patterns which we use. One is the percentage pattern and the underscore pattern. So percentage pattern it, it matches a, it matches a string and underscore pattern matches a character. So you will be clear by this example. Select name. Suppose if I want to, as I told you, if I want to display names of all the instructors whose names are starting by A, select name from instructor where name like, like is the operator which is used over there, A percentage. So this is the first character and whatever string which, which comes after that it will match that. And if I want to specifically take out all the names where second character is the, second character is N, then this is the, this is the query which I use, name from instructor where name like underscore, it matches that first, first character, whatever it may be it does, it does not care, second character is the N and whatever comes after that. So it matches that. So how to order? Suppose I want to order a table according to the, according to salary, descending order of salary or something. So for that I use order by clause. So select name, these, these are just examples, do not consider, select name whatever, whatever you want. Whatever, whatever you want to display you can display over there. Select star from instructor, order by name. So it will, it will display those by ascending order, yeah, ascending order, yeah. By default it is ascending order. So we can, we can give either DESC for descending order and we can specify ASC for ascending order. Specifically if you want like by default I think it is ascending order. So if I want to display it by descending order, then order by name DESC. It will display all the names in descending order. So I can, I can use this for multiple attributes also. So order by department name, command name. So what it will do is that first it will order by this department name and for each department name it will order by name. Now between clause, this is one more useful query or construct we can say, insert, I mean select name from instructor where salary between these two. So it will display all the names of those instructors whose salary is between this range. Then there are many aggregate functions. These aggregate functions actually taken as input many, many tuples and give a single, single output, single value as the output more, more specifically. So some average count mean max are the, are some of the aggregate functions. I think even there are some more, I do not know, I do not remember those. So select average of sal from instructor where department number equal to 101. So it will, what it will do is that for 101 it will take those entries. It will make an average of those entries, average of the salary of those entries and display only the salary, average salary. So then group by, suppose if I want to group people according to their departments, then I can use this group by clause. So select department number, average salary. What this query does is it will first group by according to the department number first. Then it will take an average of the salaries in that group and for each group, each such group it will display the average salary of those. For 101, for department number 101 it will display may be 50,000 is the average salary 102 so and so is it this thing. So first it will, first it will group and then take an average. So if somebody wants me to explain it using some examples just tell me, I will do that. So another, another construct or is the having, having clause, another clause is having clause. So suppose if I want to display or consider salaries only above some specific salaries. So I can use this, select department number, average of salary whatever from instructor group by department number having average salary greater than 50,000. So it will first group then take an average and display only those which have an average greater than 50,000. So you can, you can even use nested sub queries, queries inside queries. So this is one of the examples suppose, suppose I want to know, I want to know the courses which are taken in both fall semester as well as the spring semester. So this, this is one of the, one of the examples you can create any, any number of examples. So select course ID from teacher's terms, consider this as the teacher's term table where semester is equal to fall and year equal to 2009 and course ID, course ID of those should be present in this table. It retrieves some, some tuples. So those course, the course ID should be there in this, these tuples. So these things should match whatever I retrieved from this, course ID should be in this one. So whatever I select from that, that should match over that. So if this can be written in many other, many other ways, this is not the only, we can even use simple queries for this. As for explaining the nested sub queries, I gave this example. You can write it in many other formats. It's not necessary that you should use sub queries itself. So now how to install MySQL? So if you are, if I am explaining in, in terms of Linux environment. So if you want to install it, install MySQL, you can open a terminal. Then type sudo apt-get, install MySQL server, MySQL client, these things. Even MySQL client will work on it. And if you, if you, if you want to use synaptic manager, that is if you, if you, you can even install it using synaptic manager, there is a, there is a utility synaptic manager. So go to synaptic manager, just search for this package and apply that. Just mark that package for installation and then apply those changes. Then there is a utility PHP MyAdmin. How many of you know? Right now I am, I am using terminal for all these things. So it's, it's like the user interface is not, not too good. If you want to do things fast, then there should be some utility by which you, you, you can see tables in a graphical format or something like that. So there is a, there are many, many utilities, I think. So PHP My, PHP MyAdmin is written in PHP for accessing MySQL. So I can just log into this. I can see this graphically. These are the tables which you had already seen and not, it's not tables. These are the databases. Now I have created this interns, this thing. We had dropped that table. So there is, currently there is no table. So I can show you other tables, students table. So this has one table and if you want to see the, yeah, if you want to see the data in that, just browse that table and it will show in one specific form. If you want to know the structure of that table, you can do that, these are the structures. Even you can write some SQL queries for this, many other things. Just explore these features. And now one, one main important thing, how to create users in MySQL. I have, I have logged in as root right now. So we can restrict each user to some specific databases. So for those who don't know, how to know, what are the user present in that. So this is the query which is used. My, MySQL is a database, user is a table. So I will describe once again. So these are the databases which are already present, out of which MySQL. MySQL is the table which is used, I mean database which is used by MySQL. So it contains information about MySQL which is installed here. So how to know the different users who are there in, so these are the users and password is encrypted. So suppose if I want to create a new user. So create user Anup or any specific name. So this is, first one is the user name and this is the password. So create user Anup, identified by Anup as the password. So this will create a user, it has created a user, now we will log into, so I have logged in as Anup right now. And now suppose I want to use some databases, just now we created interns 2012. So it is showing an error, access denied, I am not allowed to access those databases. So how will you do that? So for that, there are commands, you can grant permissions to user, this is how you give. Grant all, all means insert, update, whatever, grant all on, demo is the database name, .star, whatever tables are there in the database to this user. So after making this, for this you have to, you should have some permission to give others that permission. Otherwise it, for example I should be root user for giving such a permission or some other user who has got this permission to give permission to others. So otherwise we cannot, this, even this, this command will show an error that you have, you have not got permission to grant permission. So this is how, after suppose if I log in as root right now and execute this command, then it will give permission to whatever database which I have specified over here. Then how to drop a user, I can drop a user, drop user, user name, so that is all. So this is the reference which I used. Do you know where, last time intern had asked me a question, where is the data of this MySQL stored, actually stored? We know that it is stable, but actually it should be some, somewhere, somewhere inside the table, I mean somewhere inside the computer. Actually there is a file, there is a file MySQL dot CNF. In that file it specified where the data is stored, so I will show you that. So it is in ETC MySQL, so MySQL dot com CNF, yeah, see this, but we will not be able to access it directly, I will show you that also. What was the path? Slash var slash lib slash MySQL, slash var slash lib, yeah MySQL, see, even then we can do that. We will see, yeah we will see, so see, whatever databases which we created, those are in something like a directory format, yeah, that is all.