Oracle SQL Tutorial | Oracle DBA | Oracle SQL for Beginners | Great Learning

hello everyone oracle is one of the most widely used database in most of the top tech companies to keep its data safe and secure every company wants to use best of the database to make sure their client data and company details are protected they use structured query language which is also known as sql it is a set of statements with which all programs and users access data in an oracle database sounds amazing right keeping this amazing facts in mind we at great learning came up with an idea on bringing up a tutorial on how to work with oracle sql and what are the basic concepts around the same so that our learners will get an extensive idea on how to use oracle sql and add this skill in their resume so without wasting much time let's get started with this tutorial on oracle sql [Music] if you haven't subscribed to our channel yet i want to request you to hit the subscribe button and turn on the notifications bell so that you don't miss out on any new updates or video releases from great learning if you enjoy this video show us some love and like this video knowledge increases by sharing so make sure you share this video with your friends and colleagues make sure to comment on the video any queries or suggestions and i will respond to your comments okay so now that we are in our agenda let's understand which all topics we are going to cover in this particular tutorial what are the basic concepts that we are going to learn from this oracle scale tutorial so first of all we are going to introduce you to the concept of dbms rdbms sql all these things because you need to understand why do we need to save data and how we can actually save this data earlier where we used to save the data and now in this era 2021 where we are actually saving our data right the next topic we are going to see is creation of table so we are going to understand that in oracle sql or in sql how do we actually create a table first we create a table and then we insert the data so how do we actually create the table and how do we insert the data inside the table those all things you are going to see in this second topic in the third topic we are going to start up with oracle we are going to do the setup and installation will download oracle i will show you the steps from where to download and how to download and then the installation steps as well once our oracle is ready we are going to understand the relational model first we are going to understand selection projection and more of the other things about rdbms which is nothing but relational database management system after that we are going to move to the miscellaneous topics in which we are going to cover most of the small small topics such as alias and then the logical operators those all things we are going to cover in this miscellaneous topics these are some small small topics which are very very essential if you are learning oracle sql the next topic we are going to understand the operators as keywords so all those operators which we are going to learn how to use these operators as keywords in oracle that is what we are going to see in this particular topic moving to the next one we are going to understand what is functions in oracle yes in all priming languages we know what is function but in database what is function that is what i am going to tell you in this particular section so in this topic there are a lot of functions which are predefined and we are going to see how to use those functions to make our work easy the next particular topic is a very important topic if you think about it in a interview perspective because in interviews clauses is one of the topic which has been asked several number of times so please focus on this particular topic very very sincerely because klaus is such a topic which is very vast and there are so many classes which you need to work upon and if you don't practice with clauses you will not be able to get the output or you will not be able to learn sql in a very staggered manner the next topic is nested query so we are going to see how to write a query inside a query now if you don't know what is the query don't worry in the beginning of the session itself which is creation of table i'm going to tell you what is a query right now how to write a query inside a query that is what is called as nested query we are going to look upon that and then we are going to move to the most important topic which is sql commands which is ddl dml dcl and tcl all these commands we are going to see what do they do how do they do it and what these commands syntax are the last topic we are going to understand the database objects some database objects and stored procedure as well so as you can see the agenda topics if you can see here is very fast and there are a lot of topics we have which we are covering so sit back and start watching this particular tutorial it will be really good if you can do the practical things with me in your laptop so that will become easy for you to understand it in within the hour right so now that you are beginning with our particular oracle scale tutorial the first topic is introduction so let's see what is there in this introduction now let's say that we have a particular student in a college right and we want some information from that particular student so what we will have we'll have his name maybe his role number or registration number and then we'll have some address right and then maybe the contact number right or let's say mobile number so these are some of the information which we want from this particular boy right now if you can see these all informations are nothing but what so these are called as data correct now why i'm telling you what is data because there's a lot of difference between data and database correct so now first of all we need to understand that what is the difference between data and database data is something which is very particular about one person or one particular individual now if you talk about a college with a group of students and i want those group of students all details that means i want his detail also her detail also her detail also and his details also right now obviously all of these people all of these students name roll number address mobile number i want it and it will be a huge data right that means i have to create some table and inside that i have to write all these informations right to make it look very structured correct and that is what is nothing but data base so that whole table is nothing but not database so if you talk about data data is one particular information but database is a group of information correct so this is what is the difference between both of these things now if you're very sure about what is data and database now you must be very sure that white is called as oracle data based and not only oracle data right okay let's move to the next part suppose if i ask you that how you will manage this particular data or how you will manage the database how you will you do it so let's say that that i want to record my information somewhere so what i'll do i'll take a sheet of paper and i'll write my information there right correct but does that sheet of paper will be maintained 20 years after or 30 years later will it be the same i don't think so right because it's just a sheet of paper correct now how we can manage those data how we can do that now obviously in your mind one answer will be coming which is computer which we actually save our data right now also if you're using a laptop you have many of your data informations your music file video files images everything is stored in your laptop right okay so how to manage managing refers to storing permanently and retrieving efficiently okay now managing doesn't mean that you're just storing it but it also means that you are retrieving it efficiently let's say that i have a bunch of students and i want all students record details to be in a sheet of paper right now one particular student detail in a sheet of paper is perfectly fine but when you have 50 or 100 students details in a sheet of paper it becomes little bit messier why is it suppose that i have a two sheet of paper and in those two sheet of paper i have all the data of my batch of students all all my 50 200 students details right now i want to find the detail of one particular individual whose name is rohan now i need to search from starting till the last where is rohan detail it will take a lot of time that is what is retrieving so retrieving the data is not efficient here right and that is what we need to also look upon so a sheet of paper will not work here now the question arises is so then how to store data a small volume of facts permanently and how to retrieve get the stored facts efficiently or the another question is how to store database which is large volume of facts and figures permanently and how to retrieve those efficiently right so these are the two questions which we need a solution now so let's go back to some early 90s or a decade ago and let's see what happened there now we used to use a ledger so if you know what is a ledger if you go to a hotel restaurant right now also in many parts of india people are using ledgers if you go to a hotel to stay right now ledger is something like a very big and thick diary in which they store all the informations correct now most of the modern hotels and all they are using tab and pads and obviously the laptops but many of the places are still using the ledgers in ledgers they actually record all of your data and then they use it for next purposes right or to store data efficiently but now let's say that i have a ledger and i am storing these particular data of some students in my ledger okay now in the below ledger one page has data right one page one single page has the data but if you talk about the book the whole book is nothing but the database right if you can see all pages and many books put together forms database which is large volume of facts and figures now is this a correct idea or is this a great idea to save our data or to save the database let's see problems associated in managing data database are as following now let's understand what are the following challenges so we have security challenges obviously why security challenges because to save these ledgers we have to have a go down or we have to have some place where security is there right and we cannot just assign one security guard to actually look upon all the ledgers right not a very great thought correct now backup issues now suppose that you have a 10 years ledger okay 10 years ledger means a lot of books and a lot of ledgers right now how you can back it up that means obviously you won't be keeping all those ledgers for 10 years or 20 years in your go down right it's just a sheet of paper or it's just a diary right it can get rotten or it anything can happen based on weather conditions right it will not be safe inside right so how will you back it up you can't just make a new diary or make a new ledger and copy all the information in the a real ledger to this new ledger you won't be able to do that because it needs a lot of effort it needs a lot of resource it needs a lot of employees to do that right so nobody will copy all the content of the old ledger to a new ledger correct imagine you are just writing and copying down all the things from new ledger to a old ledger it doesn't sounds good right next is space issues obviously all the 10 years ledger 20 years later you won't be able to keep it somewhere it's not a great idea correct human resource issue again you need a lot of people to work around this ledger to keep it safe to keep the data secured right it should not happen like anybody will enter your go down and anybody can change the ledger data they'll cut your name and they'll write your name something like that it should not happen correct so it's not safe it's not secure now costly it's very costly because you have to buy the ledgers you have to keep it safe you have to have human resources you have to have most of the people around those ledgers to keep it safe and secured inefficient in retrieving facts if you want to search your some of the details what you have to do you have to tell the particular people that hey you know i came to this hotel long back ago and i want to search those details now can you please show me the hotel manager will go to their go down they will put up they will they will bring that particular ledger down and then they will search if this is not the ledger they'll go to the next one if next one is the ledger they will again scroll to all of the ended pages and they have to search your name and it can happen that we have the similar names like rhythmic many times in the ledger so you have to find out which particular rhythmic you are correct so like that it will take a lot of effort a lot of time will be wasted in searching or retrieving the facts or data correct so these were all of the issues now computer came into existence looking into these all problems in 1940 right but computers were very costly very heavy and very very expensive right so people did not think of using computers but in late 1950s computers prices came low as compared to 1940 and people thought of using computer to manage facts and figures but there were no means to do it now what is the meaning of no means to do it let's understand pointer by pointer now in 1960s high-level priming language developers suggested to make use of priming language to manage store facts and figures now i think we are close enough to the error that we are talking about right but we were having a lot of problems in this particular era also let's understand what are the problems same programming languages we use a concept called data type variables arrays right and one we can make use of it to store the facts and figures right now don't you think that people in that era were having so many problems around using computer or working with computer people are very happy because computer did not have disadvantages like ledges and now they have a way to store facts and figures in computer and they stored started to store facts and figures in computer using primary languages but as the size of facts and figures increased priming language failed to manage it so prominent languages are also bound to have some limited capacity and that is why these are also now not very much efficient priming language is successful only in managing data but not database okay so using priming language you can save some of the data but not a huge database now this is where the concept of software came comes in which is database management software so this is one software which is used to manage store data permanently and retrieve the large amount of data efficiently and this is what people were looking for now this particular has many examples right now we have oracle database mysql database db2 information and there are lot more so this is one of the particular software which we now use right but now understand what is the difference between this and the ledger and the computer programming languages let's understand that because this is what will lead you to oracle dbms and that's how you will actually use that particular software so suppose we have a ledger and we are saving these informations right so these id name age gender marks send these all are called as fields so we have all the fields and then we have all the values which are which are also called as data right so these are the two main things which we saw in the ledger we have only two main things which we need to save our data right keeping this fact in mind people came up to an idea that how we can actually save this field and value into a hard disk of the computer right because all your data will be saved in the hard disk of the computer right so a hard disk is nothing but where all the data are saved now how we can actually take this build and value and put it inside the hard disk we can't do it like manually correct we can't do it like that so if i have a hard disk we can't put this information directly into our hard disk we need something we were using programming languages but priming languages are not accessible to actually capture all the database or all the different data it is only has limited capacity and it is only being able to access or store limited amount of data that's it so we started to use now which is dbms okay so we use database management system software to say that hey database can you please do the following things now database is having a similar kind of structure which you always maintain okay which they always maintain and if you want to tell the dbms that hey we want to save some data we have to tell him the procedure how to do it so let's say the very first procedure is create the table we need to tell dbms that hey we need to first create the table which table let's say some name student table the field of the ledger should be the columns of the table whatever field we have given in the laser that is nothing but the columns of the table that should be the columns of the table give a name to the table we have already given student insert the data inside the table whatever values or data is inside the ledger that should go inside the table now what you have you have a table the column field names table name in which you have inserted the data this is what you have to follow to keep the data or to store the data present in the ledger onto the hard disk of the computer now dbms will do this and will keep the data saved in your hard disk this is what happens when you use dbms what is the difference between oracle and then sql what is this sql we have understood that oracle is a dbms software database but what is this sql then let's understand so when we talk to any person let's say that she's saying hi goku how are you so so the person says hi sita i'm good what about you what language we are using we are using english language to communicate with our particular person or an individual correct now if i want to talk to all the dbms softwares all all the databases that we have right like we have ms sql we have mysql we have oracle we have hadoop we have post gre sql now to talk to all these particular database we can't use english language correct we need to have some language to talk to these databases to give the commands to these databases to actually tell the database what you have to do right so we have to use here what is called as sql okay so it is a language used to communicate with database management system we'll be using oracle data base to store our data okay but sql will be the language skill will be the actual language which will be writing to tell the dbms what he has to do fine now this was all about the introduction this was all about the historical and the latest trends what is happening in storing and retrieving data or database okay now let's understand how does it appear when you are actually storing a data from a ledger to a dbms right or to your any oracle or any other dbms softwares so if you have a ledger looking like this this particular data using sql will look something like this okay so this is how the table looks like and this is how the fields are the columns understand that the fields id name is gender marks and same these are the columns and the values are nothing but our rows which you can see here correct now let's understand what and how to create a table what is the syntax of it and how we can actually do something to create a table which we have seen earlier in our previous topic right so let's see what is this sql command how to use this sql command and what do we call it as now how to insert data into the dbms by creating the table so if i have this particular data in our ledger how we can actually store it in our dbms using sql so this is one of the query which we have to write now what i'm saying what what is this word i'm using here query right now this query word is very very important if you're learning database because query is something which you write to solve a problem okay or query is something which is a problem by itself right query means a problem so we have a problem what is the problem we want these data to be stored in the table so we write a query this is the query we write this query and once we execute this query this particular table will be created okay so any particular statement a set of statement which you write in sql is nothing but called as query okay so what we are writing here we are writing create table student with we have all the fields in it okay not only the fields but we also have the data types plus we also have the maximum number of uh letters or digits how much you want those things let's understand one by one so create table is a command right create table is a command which is you can say keywords okay so we have to use create table to create a table in a dbms student is the table name which you can always change this is user defined so if you want to write any other or you want to give any other table name you can give it now we have the id we have the name we have the age we have the gender marks and same obviously id age marks and sem you know that these are numbers if you see here 52 26 98 8 these all are numbers right so we have here number right now id we know that it can be only till 10 digits it should not exceed 10 digits right if it is exceeding 10 digit my table or my dbms will is not gonna accept it so you have given a particular limited capacity to it which is 10. similarly to name and gender we have varchar2 which is another data type don't worry we will see what is data types and how does it look like how do we write it in our other topics but right now i'm just telling you that these are the data types where cat 2 why is it named as varchar2 not only varcad that also i'm going to tell you in the topic of data type 30 and six now these are again limited capacity if you if you want to write six letters only in this particular gender if you see male and female that can be the only uh particular gender right so that is why i've written here 6 30 the name will should not be longer than 30 letters correct so this is how you write a particular query once you execute this you will get this kind of thing now one thing is missing here and if you notice that is nothing but our data the values and obviously because we have not written a query to insert the values correct and that is why only the table is created correct now let's move to the next part see if i have a lot of data in our ledger how do we do that so this is another example of employee table and just have a look how does it look like right so this is how we have the fields which is employee id name age address salary department and experience so these are all my fields and i've just created a table now let's understand how to actually insert the data inside this particular table because now we have the table but now we need to insert the data also inside it so let's understand that so we have a query which is insert into student values now understand what is this see insert into is nothing but a command likewise we are having create table similarly we have insert into a student is a table name now if you remember student was the table name which we created we are using the same table name here if you like student one student one you have not still created so it will give an error so you have to use that particular table which is already created then only it can insert values then we have the values and inside the values we have the all values which you want to give now understand that the order of all these values should be the same how you are putting it in the creation of the table so in the creation of the table if you remember we were using id name age gender marks and same and in the same manner i am going to put it here also so we have id name age gender marks and sam right this is how you put it now some of the questions will come into your mind the first question is why the numbers doesn't have a single quote but the names or the gender or the string or character sequence have a single code now that is an interesting fact in any programming language or in any database language even if you see we always write the characters or the strings in a double quote a single quote and the numbers is written just like that okay the next question which will come into your mind is if we have to write the data and if you have want to insert the data in the same order then what if i miss out on some of the data suppose if i'm not writing 52 i'm starting from aman then what happens now what will happen is the sql will check that what is this aman now he knows that id can't be aman right because id is a number she'll put a 0 here by default or null by default right because he can understand that you have not given an id value started from the name value correct but don't do that if you don't have the data then obviously you will not be able to write anything in the data but if you have the data don't just skip that otherwise it will take a null or a zero value correct so this is how i have inserted the data and now you can see that this is how my table is getting appeared right so we have the first row and your second row understand that for each row you have to write this particular query now i'll be thinking that okay see if i have 52 rows that means i have to write all the 52 rows statement like this and this is very long this is time taking right then how to do that in a shorter way that is what why we have the oracle dbms so once you will do the oracle setup and installation once it will download it then you will be able to see that how you can easily create a table without writing the query and how you can insert the data inside the table without writing the query so without writing the queries also you can just directly go to oracle there's a whole system there where you can just put the values once you click on save it will automatically get saved right so we don't have to actually write the queries but for your knowledge you should know what are the queries how to write it and how will it appear correct so for more of this students record you can see that this is how it will look like right so this is one of the great example very easy example but now it's time that we move to practical things and we see how oracle is working right so now let's understand how we can actually do the download of oracle software and how we can install it right so before going to the oracle installation or to the google chrome and understand how we can start it let's understand what is this software and how many types of softwares are like this so if you talk about oracle we have the enterprise edition okay we have the standard edition and we have one edition for only practicing for only learning right so if i move to our google and we just write here oracle download right the very first thing which you can see it is oracle database software downloads so if i open it you will be able to see that we have the enterprise edition which is 19.5 and then if you scroll down you will find the oracle database express editions which are 18c and 11g now 11g is the express edition for windows 64 for linux for windows and these are only for learners to practice right so we'll be using 11g itself so what you do is once you click on right click let's say that i am clicking on that as you can see this particular database page is not taking me to the download part of 11g right that means now oracle has removed 11g from this particular official website okay and they are trying to tell us that you have to go for 18c express edition okay so if i open this you can see now this particular two links which is one for linux and one for windows now 11g also some time ago this was also giving me a page something like this right how 18c is giving me in the same way this page used to come but now it is not that means they have removed it and now you can use 18c so now once you download 18c express edition this is having a lot of features which you can use with oracle sql developer tool okay now what is that so one is oracle at deep database and one is oracle sql developer now oracle sql developer is a software where you actually write the query and you will get the output right in oracle database also you can do that but oracle is more like a database as i said oracle sql developer is a developer tool where you actually write the sql language and get the queries or whatever problem solution you want you find it there okay now this particular express edition for windows you can download it it is for 2gb i think uh it's written here it's of 2gb it will take a long time and you can do that i've already done it as you can see oracle xc18 for windows 64. and it is the same way how your oracle 11g is gonna work but the question is from where you will get 11g because now you are learning and you don't want to use 18c right once you are very fine with 11g express edition then only i want you guys to work on 18c because it's little bit of complex so let's see how we can download 11g right what you do is in google just type oracle database 11g or express edition whatever you want to write right when you do that you will get a lot of websites from oracle.com itself a lot of links from oracle.com itself but there are more some links which you can see here from o7 planning and there are more of random links which you can see right and these people what they have done is they have the dot exe file and they have given if you know publicly free so you can click on any of those and you can get it similarly i have done it you can see here that i have got this particular zip file correct similarly you have to do you have to just download it some people are also putting it in the drive and giving it if you're not finding it we'll put it in this video description so that you will find this particular software in the drive also okay now what you have to do so we have the 11g express edition windows 64 bit what you will do is we will extract this file here itself so we are extracting the file and we are getting this particular disk one right open this click on setup and now we are installing so one message will pop up that do you want to allow this app you can click on yes and let's do the extracting and installation of oracle database 11g experts edition so it is preparing to install and it says welcome to the install shield wizard let's click on next let's accept the terms in license agreement click on next and let's click on next see we can browse but i don't want to change the location i want it to be in the default place so click on next now here you have to set up your password now understand that there are two default database accounts right one is cis and one is system if you see enter and confirm password for the database this password will be used for both this is and system database account so i'll be writing a very default kind of password which you should or anybody will not forget right so normally for all the oracle we use a password which is root one two three i am just going to put root right so that becomes very easy for me to access anywhere whenever i want uh to access the oracle database of mine so i'm clicking on next clicking on install and just wait for some time and it will get installed right now this password you should never get forget because this password make sure that you are working with oracle in any of the laptop and in any situation right so do not forget that particular password so as you can see now we have finished our installation and let's click on finish so let's go to our desktop so now we are in our desktop and as you can see there's a shortcut which is get started with oracle database if i click on it you will see that i am getting an error right now the error is windows cannot find http 127.0 colon there is some http port which is missing here right so we have to correct it now how to correct it will go to our c drive so let's go back to our this pc c drive and in this c drive let's go to our program files and we have to find the oracle software whatever we have downloaded let's move to our program files here so as you can see we have got the oracle exe let's open app let's open oracle let's open product let's open 11.2.0 let's open the server now in the server we can see the get started page right let's click on properties and if you look here we have the http port here and here what we need to have is so you need to just delete the percentage http port percentage not the whole thing now let's click on apply let's click on ok and let's see whether it is opening or not so let's double click on it again and it is opening so let's see how does it appear so we have got the home web page of our oracle and this is how it looks like if you can't see i'm just zooming in so that it appears well for you and you can see here that we have some things oracle database x11.2 and this is how it looks like so let's go to our application express and here it is telling me the username and password right so they remember we have the default username we have one which is sys and another one password which we gave as root let's login and here we are right let's not save the password for now now here you can see that we are in our sys which is our system system right so we are on our sys database and here they are telling me that whether i want to create a new one new uitable database or i want to use the existing one so i don't have any existing one right now so i'm going to create a new one and to this particular database username i'm going to give ruthvig right or okay let's say rhythmic and here also i'm going to give arithmetic password i'm going to keep the same for all of the things so that i never forget what is my password for this or that so for my particular schema we have the root password and for this database also we have the password which is root only and i'm just going to create a workspace now once you create the workspace you will get a particular message which is successfully created workspace with to begin click here to login so i'm going to click here now you can see workspace really quick username right quick i'm going to put the password root and click on login now this is what is your main thing which you need to work upon and this is where you will actually start working on your queries writing the commands and getting the output so let's see what are these things so we have to go to sql workshop and once you click on sql commands you'll be able to see two windows with one is this one and the other one is this particular result right now you must have understood what is this so basically this is nothing but the main place where you will write the query and you will get the result of it right so let's say that i am writing a particular query select sys that from dual okay i've written this small command right let's run this you can see here it is giving me an output which is the system date okay now what is dual what is from what is select you don't worry about that i've just taken an example and i've given an idea that what is this so it is giving me the system date right now what is the date in the system in my system so it's 25 8 and that is what it is showing me correct so this is how you will write the query and you will get the output this is where you will do that now let's go back to our home and let me tell you something more about this particular page let's move to application builder let's see what are the things here so we don't have much of the things here okay you don't have to care about this application builder let's go to sql workshop here most of the things you have to know let's click on object browser now this is one of the place where all of your tables will be appearing so if you see we have all the tables here we have the imp table which is by default it's already created you can see this is how the table is looking we have the column name and we have the data type remember about the ledger and the table this is what it is we have the field names these all are the field names and the column names and these are all the data types with all the values now label means whether that particular column can be null or not so employee number cannot be null but all the other things can be null so this is what is my table if i click on data i will be able to get the data that means the records also so if you can see these are all the records of my table correct if you want to edit you can edit from here you want to edit the row you can edit it so see you don't have to write the query you can do it directly that is what i was saying you now department dept this is also one of the table which is already given right so we have the data now let's move to demo users demo stats something like that we have now let's see how this this emp table appears in my sql query so what we have to do is we have to just go to our command line and just write here select star from emp now if i do that if i run it as you can see you're getting the whole data in front of you that means the whole database the database which are having one one individual data and everything is displayed here now you can see more than 10 rows available increase row selected to view more rows that means there are more rows here so what you have to do you have to do it let's say around 200 let's run and you will see all your data so we have 14 rows which is returned right so this is how you can play with the data you can get all these data accordingly and this is what it is right now let's move to sql workshop again and if you can see here create object is something which is written here and we have the table right now remember i told you that you don't have to write the query of create table or insert into right this is what it is so suppose if i want to create a table let's say i want to create a table named as student right and the column name i want to give here is let's say roll number i want to give here is name i'm going to give here is age i'm going to give here is gender anodic gif here is city so these are the five columns which are under give now select timestamp so roll number obviously will be number this will be the archive 2 age will be again a number gender will be again a varchar2 and city will be again of archive 2 right now you can see in all the numbers we have a precision and we have a scale right and then in varchar2 we have the only scale correct let's do one thing let's click on next right if you can see what is happening the scale for the column name is required gender is required ct is required that means all the particular columns which we have varchar2 it needs a scale value it is much that you should give a scale value so name i want to give it 30 and gender i want to give let's say 8 and city i want to give let's say 10 right so these are the things let's click on next and once you click on next you'll be able to get into the next different section which is primary key now what is primary key what is foreign key what are constants those all things we are going to look upon in the next topic which is relational database main system but right now let's say that i don't have any primary key because we don't know what is primary key right and foreign key also we will not select anything so we'll just click on next constraints also we don't know anything what is it so let's create table now this is asking a confirmation that whether this all things are correct which you have written and it is also giving me a query already written this is the query which is already written and this is what i actually showed you in my slide and we don't have to actually write this particular thing anywhere right we just create it and the table will get created now what we can do is we can copy this particular query you can also click on create you can do that but right now what i'm doing is i'm copying this particular query going to mysql workshop going to mysql command pasting this particular query here and clicking on run what is it showing it shows table created that means now my table is created how can i check whether my table is catered or not one thing what you can do is you can just write here select star from student let's see what happens now if i select this particular query and run it says no data found it doesn't say that table is not there it just says that no data found that means data is not there in this table table is created let's confirm whether the table is created or not so if i scroll down you will see student table right and we have the table columns and all but in the data there is no data here right so if i click on insert row i will get the detail and i can insert it right now here itself so we don't have to write the query of insert into and all blah blah so roll number let's say i'm writing one name let's say i'm writing rhythmic right is let's say i'm writing 26 gender let's say i'm writing male and city let's say i am writing bangalore right let's create this now you can see one of the row is created similarly you can insert another row and let's say that i am writing some of the statements right and let's create so two rows i have inserted and you can see it in the data let's verify whether it is coming in my sql command or not so what you have to write again select star from student from now i think you have understood that what is the select star from student what i am writing here select staff from student is making sure that it is selecting all the data from your table and showing it to you so if i click on run i am getting my two table data which is ritwik and for sachin right so this is how you actually play with your oracle and i hope this looks very easy for you now what we'll do is we'll understand the basic concept the miscellaneous topics and all those things which i have told you in our agenda and we'll start working using this particular software okay so now we are done with our oracle setup download and installation and we have seen how to create the table in an oracle database and how do we actually start creating the table inserting the values and playing with the data right now before moving into the miscellaneous topics or the basic concepts around oracle we have to first understand the topic which is relational model so let's understand what is this particular topic and what is it about so let's consider that we have two tables and something like this so we have this one table which is roll number and student name so let's say that table name here is student table okay and this particular table is marks table [Music] right so we have these two tables these two tables are separate tables so let's see the fields so we have one roll number field and one student name field in student table so every roll number is assigned with one particular student name and in the marks table we have roll number which is again a particular field for marks table and one particular field which is marks obtained for marks table so assigned role number people have these marks obtained now if a question arises that you know we have a query we have the query that let's say we have a problem that we want the details of all the students who have scored more than 40 right or who has scored more than 45 something like that and we want his roll number we want his name and we also want his marks okay so you want all his details now considering these two tables we can get roll number student name and marks right why because we have one particular column which is common in both the tables so roll number is one of the column which is common in both the tables and because of this roll number we are being able to say that we are going to fetch roll number student name and marks obtained imagine that this roll number column is not there in this table will you be able to tell that how much rohit has scored or how much mohan or sanjay or kiran has scored no right we won't be able to tell because marks obtained then will not be assigned with any roll number correct so this is what is my problem here and to the solution of this is basically very easy what we have to do is we should have one particular column which is matching with both the table and we have this particular column roll number which is matching with both the tables and we can use this now the table which will appear will look something like this so we have the roll number we have the student name assigned with that roll number and we have the marks assigned with that particular role number itself so we have this particular table which is a combined table the resultant table correct and this is what is called as relational model what is relational model the relation between the two tables which is the common column so when you have the common column you are actually establishing a relation between the two tables let's take another example so we have two tables again here we have the country and capital so let's say that this is the capital table and this is content currency so let's see that this is the currency table now as you can see current tree is one of the common column field which is in both the tables right so if i'm asking that in greece country which capital which currency is getting used then it's athens and drachma correct similarly if i ask for india then for india country we don't have any capital written here in this table and we don't have any currency also written here so for india there will be null value for capital and currency right but if i ask for italy usa china japan australia and france we have all the capital and currency in both the tables and we will be able to get it this is what is a relational model this is what is a relational table now here are some of the key terms which is related let's understand what are these terms so the first term is nothing but relation now what is this relation relation is nothing but the table so now when i'm saying that this is a capital table we don't say it's a table which say it's a relation capital is one relation currency is another relation tuple tuple is nothing but the row so if i talk about this particular row grease athens right this is one row which is called as tuple so now we will not say that it's a row it's a tuple right next is nothing but attribute attribute is nothing but the column so if i talk about this country and capital which is my column or field we call it as attribute okay so country capital currency these all are my nothing but fields or attribute now the next one is cardinality cardinality is nothing but the number of tuples in a relation the amount of tuples or the amount of rows in a relation or a table is called as cardinality so the number of rows in this is one two three four five six seven so here we have seven tuples which is nothing but the number of cardinality the degree is nothing but the number of attributes that means number of columns or number of fields so degree for this relation is two because we only have two columns or two fields which is country and capital okay domain of an attribute is nothing but the values of an attribute so if you look about this attribute country greece italy usa china japan australia and france are nothing but the domain of an attribute okay so these are the list of a set of values right the next two terms are very important which is primary key and foreign key now understand this in a very easy manner because this is one of the things which are very important to understand this has been asked in a lot of interview questions and many of freshers are getting confused between these two primary key what is primary key primary key are those keys which are actually uniquely defining your table okay so let's say that i have this table which is capital table right then capital table which particular column is uniquely defining country okay country is all unique capital is also unique but country is one of the particular column which is relational which is showing relation with the currency table because in currency table also we have country so country is our primary key in this table okay now suppose if i have here one more column which is let's say country code okay so we have some random codes we have zero zero zero one zero two zero three zero four zero five zero six something like this we have right now country code is now much more a uniquely identified thing when you talk about this table because country code can be a primary key also correct let's say that in a in a school you have a role number right why do we have a role number because maybe one of the student can have a similar name let's say my name is ritwik there is another student whose name is ritwik but right so to distinguish between these two people or person which is raj and kumar we have different role numbers given and those role numbers are all unique right and that is what is our primary key in that case so in all the tables you have to find the primary key now if i talk about this particular table which is roll number roll number is something which is unique right so this is my primary key in this also we have the primary key which is roll number correct okay now we have understood what is primary key that means in one table we will have one primary key in this currency table we have one primary key which is country right we have one primary key always in one table you should and must have one primary key okay if you talk about a student table which we have seen in our oracle database we have seen i roll number name age gender and then code right or the pin code or the city so here we have the roll number as the primary key now let's talk about what is foreign key now understand if you have two tables i am saying two tables okay in primary key every table every each and every table should have one primary key when you talk about foreign key we talk about two tables let's say relation one and relation two now here we have some columns and here we have some columns okay now let's say that here we have the id here we have name and here we have subject id and in this particular relational table we have the id we have the subject name and we have the subject faculty okay now understand what is happening here so here what will happen id is my primary key for this table so for this table also there should be one primary key and that is subject name because all subjects will be different okay so this is my primary key for this table why i'm not putting this id as my primary key why i'm not doing that because i know that these two tables are related and when two tables are related and if already one particular column is a primary key of the other table this can't be a primary key of another table this will be a foreign key of another table okay why it will be a foreign key see if you are living in india you are localized but if you are going to singapore or you are going to usa or you are going to dubai you will become foreign right same thing happens here when it is in r1 table id is nothing but a primary key which is the local id local private key but when it goes to the another table it becomes foreign a very easy concept right so this is what is all about the primary can foreign key now let's move to our miscellaneous topics and let's do it practically all of these things and let's start using these terms again and again so that you will get an habit of using it so now we are in our miscellaneous topic section let's see what all miscellaneous topics do we have in oracle sql and what are the different topics that we are going to see so first we will start with projection and then we'll move to selection and then slowly we'll move to some of the very interesting topics in our article now remember that these are very short topics but very useful and don't think that these are very small topics so let's ignore that no these are very important topics so let's understand one by one projection now projection is something when you want to project something right let's say that you have a college and you have a group of students right now if i want to save data of all the students which will appear something like this right we have usn name gender age and mars correct now projection is nothing but choosing which column or expression the query shall return now suppose that you know when we want to only present name age that's all from one table let's say that we have a student table and we want only name and age to be projected that is what is displaying the result right so projection is a process of displaying the result using project queries so project queries are all those queries which is used to display particular column to the user based on the requirement from the user so if i want only name and age that is what i am giving the command to the oracle we are saying that i want only name and age now suppose if you are writing that i want the details of all the students whose age is greater than 20 so now you have a condition now you are now you want all those details whose age is greater than 20 you are actually wanting those things which you want to project in your resultant table right so that is what is projection now if you look in this particular example right according to display age and marks of all the students so i want to display only age and marks of this student so i'll write select age and marks from student because i only want to display age and math this is what is projection so write equal to display name and usan of the student whose age is greater than 21 now i want to display the name and usa of a student whose age is greater than 21 so i have a condition so there we will use this kind of query select name usn because these are the only two things i want to show i don't want to show age but the age of that particular student should be greater than 21 that is what i want and this is what is this it is showing the next is selection what is selection now selection means which rows to be returned that means which all detail or data i want it to be displayed so actually the process of displaying the result using select queries now let's say right equal to display all the information of a student named kavitham remember the table we have name a gender match and all now i want to write a query to display all the information of the student named kavita so select star from student where name equal because i want all the details so i've written star so select the star this was the selection how many rows do you want to select all the rows i want to select so select star correct then another example of this is write curve display name generation of a student named ravi so the student named ravi what all things you want you want his name gender and us and that's all so select name gender and usm from student do not get confused between projection and selection projection is the data which you want to display selection comes where you want to display that particular row data one particular row data that is what is selection okay don't get much into inside this because in which ways you will do the queries you will write the queries in oracle database you will slowly understand what is happening why are using select and why you are using insert wire using other commands now coming to the next topic which is case sensitivity now obviously in every common language we have case sensitivity and in sql oracle also we have case sensitivity but to make sure that you know where all case sensitivity is required that is why i have this ppt sql keywords are case sensitive select case insensitive sql keywords are case insensitive that means select from where is order by having group by these all are keywords and it can be written in capital or small letters usually it is written in capital so that it clearly defines that these are keywords but you can also write it in lowercase itself now understand these four point these are very important and keep this in mind till this video ends keywords in sql is not case sensitive these are case insensitive you can write it in a small letter or capital network and it will work table names in sql are not case sensitive again table names can also be written in lowercase or uppercase it doesn't care column names in sql are not case sensitive column names are also not case sensitive that means those also can be written in capital small letter it doesn't matter but the data in sql is case sensitive now this is very important data is case sensitive why the reason is suppose if you are saving one particular data is ravi which is small r a v i now if you are asking for a particular data which is capital r a v small i obviously these two are having much and many more differences if you can see right name is same but the writing pattern is different now this ravi can be different person and this ravi can be a different person right so when it comes to data data should match the data letters capital small lowercase uppercase these all things should match if it is not matching then you will not get a result so make sure that you know in any database languages the data is a sensitive other than that everything is insensitive let's move and let's see one of the examples if i'm writing select name is from a student where name equal to ravi if you can see select is a keyword it's case insensitive i've written it in a very different manner here name age these are fields from is a keyword student is a table name where is a keyword name is a field these all things are case insensitive but when it comes to ravi which is a value which is a data is case sensitive so if i write here small r a v i this output will not come because it's capital r a v i that is why it is showing me capital r a v i okay so these are nothing but my keywords and this is my table name and this is my data and this is my column name right so this is what is all about my case sensitivity let's see one more example which is select name is from student where name equal to ravi now this is wrong if you can see this is wrong data so no data found right now coming to the next topic which is data types so what all data types do we have in our database language a derivative specifies a particular type of data we have already seen one or two data which is number and market two we have some more which is integer floating point boolean we will see one by one what are those now if you look here we have in character type care bracket to envircare and then long in number we have the float and in date we have data type that's it so these are some of the built-in data types which we have in sql let's understand one by one what are the different features which they provide so in care it's a fixed length character size okay that means how much ever value you will give to this car only that much letters of characters can be kept and the maximum size is 2 000 bytes per row default size is one byte where care and vertical two these are variable length character data that means it can be increased character is a fixed length which cannot be increased now what is it between where and where can now if you look here the amount of bytes is what is the difference it can store up to 2000 bytes per row but varchar2 can store up to 4000 bytes per row okay some more differences are written here but i am not going to take that because this is not that much important where can if you know the difference of the memory size that's enough so people generally use varchar2 because that is what is having the most uh bytes per row so that is what they use in number we have two things if you remember in oracle database also i have shown you two things so you can see where we have a scale but in number we have precision in scale and that is what it is showing here so we have in number precision and scale precision goes to 1 to 38 and scale goes from minus 84 to 127 we have similarly float and then date and for date we have the normal format which is dd mmm and yy and this is what is the date format if you want to use it you have to use this parameter analysis date format to use the date property but generally we don't use these all things we use it when we want to give date particularly but even with number dates can be written and generally we use number back and to and care that's it okay let's move to the operators and in operators we have a lot of operators which we use in oracle but the most common ones are plus minus multiplication and division now why these are common ones let's understand an operator is a reserved word or a character used primarily in an sql statement where clause to perform operations such as of comparison and arithmetic operations asthmatic operations i have just told you these operators are used to specify conditions and skill spell statement and serve as conjunctions for multiple conditions in and statement what is this what have i read so much long now understand if i go to my database and if i say that let's go to my sql workshop let's go to my object browser let's go to my emp we have a salary section here right if i look into the data we have a salary here right now suppose that to this table i want to write a code to which i want to say that i want the names of the employee with their salary increased by a thousand rupees i want the salary to be increased by a thousand rupees how we will do that so let's say that i am going to spl commands and i am writing a query right let's say i am writing select name from emp right this is what i have written so okay i think it's not it's e name it's not name right so i am getting all my e name can you see i'm getting all the e names correct now what i'm going to do is select ename from emp here what i want to do is i want to use the operator and i want to say that the salary what i want it should get increased by a 2000 rupees and then show me the table so now the king which was having five thousand as a salary now has increased to seven thousand similarly for blake it is now increased from 2008 1050 to 4850 right similarly for all of them the salary is increased with 2 000 rupees another question let's say that somebody is asking you that i want employee names with their annual salary that means in a year how much they are making so what you will do you will actually do multiplication with 12 because you have 12 months salary into 12 will give you how much the annual salary so this is nothing but your 60 000 rupees for king public we have 34 000 and then for clark we have 29 000 right so this is what is their annual income half annual income six that's multiplied with six and you'll get half yearly income right this is where we use the arithmetic operator which is plus minus divide whatever you want to do you can do it from there right the precedence is given here plus minus multiplication and division so board mass rule you already know this is what we use now let's come to the next one which is column alias now what is this column alias remember that we all have one nickname correct so if a guy named as simon agarwal his home name or house name will be something called as monu right or anything else now in sql also when we create a table sometimes we give a column a very big name right or a table a very big name and when we actually write a query every time regarding that table we always have to use that table name or column name which is very long and it's very tedious so what we do we actually make a another nickname for that particular table or for that particular column name okay so let's say that i have usn name gender agent marks okay now i think that usn should be changed to registration number i think that age should be changed to years old and i think that mark should be changed the score so this is what i have done here and this is what is called as alias okay elias means that a temporary name to a particular column field or to a table name right now how to do this so we do this using two methods we actually use as a keyword or we directly write it like this right so generally you must have seen that employee e1 employee e2 why do we do that because i want i don't want to write employee employee every time i want to make it a shortcut i want to write e1 e2 something like that so the alias name will never be reflected in the actual table these are just the temporary names not the actual names which is present in the hard disk of the computer okay so how do we write it select usn as registration number name gender age as years old marks as score and this is how you will get the result right let's move to the next one which is distinct keyword so i want the data which is distinct let's say that i have this table and i want the data of distinct age that means not the repeated ones so 21 is repeated twice 22 is repeated twice i don't want it to display twice i want the distinct data so i want 121 120 and 122. this is what you will get okay this is what is distinct so how do we use this keyword there may be a situation when you need to retrieve unique records and not multiple duplicate records so you want unique data this time we will use distinct keyword it is used to avoid displaying repeated values so select distinct age from student all the unique age will be shown from the student table next one is concatenation operator and concatenation if you remember in any priming language if you know concatenation is nothing but joining of two things right so the operator double pipe operated is used for concatenation operator and is used to combine multiple data or multiple column so if i have this table and i want to use name pipe operator age that means these two columns will be combined together so but i want to use it as a alias name which is name and age so this is where i'm using the alias okay i don't want you to display his name type is i want to display it as name and age this is the column name okay now this is what is the answer round 21 ravi 20 this is how you get the concatenation done right now one more example of this if you can see here this all are nothing but the concatenation now see what i'm doing here name and connect concatenated with aged aged is not a column field it's a string so let's say the name is rhythmic then it will write aged then it will write my age so this is what will get printed read quick age 26 amazing right okay so this is what is concatenation operator now comes the very interesting topic which is dual table so dual table is a default table which is defaultly given by oracle and this particular table is by default present you can use this anytime to do any task now the tool table is a special one row one column table so if i want to show you how the dual table looks like have a look let's say that i am writing select star from dual see i have not created any dual kind of table but still it shows me one row one column the column name is dummy and the row data is x right and this is what is dual okay now what does this dual do let's understand so dual is nothing but kind of a practice table where you can do whatever you want right so let's say that i want to print system date so we use this date which i have shown you already right now if i want to see the description of this dual table so what i can do is i can just write description dual that's it now you can see what is in the description table name is dual column is dummy data type is varchar2 length is one the label yes it can be null and this is what is the dual table description how can i look into any table's description d e s c is the keyword for it correct so want to see how to word table looks like so we use description okay you can also check the system date from the dual table using the following statement and you can also check the arithmetic calculations on the table table using the following statement let's say i have some formula and i want to execute that or an equation i want to execute that so this is what is the dual table will give you so it'll give you the answer whatever answer will come and this is what is my dual table so this was all the miscellaneous topics around sql and oracle database now i will jump to the new topic and let's understand what all things are there in that topic so now we are in our next topic which is operators as keywords and in this particular topic we are going to understand what the different operators which we can use and also the operators which acts as a keyword in oracle database so first of all we are going to understand the relation operator which we already know like the less than greater than equal to those are nothing but our relational operator something like this right we also have not equal to with three symbols so don't get confused in oracle not equal to is actually denoted by three symbols which is exclamation mark equal to and then if you're using less than greater than together and then the exponent and equal to together these all three symbols are nothing but not equal to so don't get confused and these particular relation operators are usually used when you are comparing two values in a condition correct i show you some examples also so let's say that we have this table which we are using from the starting usn name generation marks which is a student table and we have to write a query to display usn name and age of all the students whose age is equal to 22. so condition but based on comparison right that we use relation operator write a query to display us and name and age of all spheres whose age is greater than 20. again comparison relation operator the next question i require you to display username and age of all the sources age is lesser than 22. these are some questions you guys can do it this is very easy you have to just use the operator and that's it you will get the result so try to create as i've already told try to create this particular table and try to solve these questions next question can be write a curve display as a name and age of all sources age is greater than or equal to 20 this also you can do lesser than or equal to 22 this also you can do and usage is not equal to 20 this also we can do so in this particular last question which is age is not equal to 20 you can use any of the three symbols try to do it on your own try to see whether you're getting the answer as imagined or not yes so this is what is your precedence operators so first we give person is equal to then greater than less than greater than equal to less than equal to and it goes on correct operators have keywords now this is very important and this is why this topic was uh discovered so the very first topic which here comes in is between and operator okay now what is this between and operator now let's say that i have the relation operator and i want uh the age greater than 20 or lesser than 20 or equal to 20 right but what if i want that the age should be between 20 and 22 then what will happen correct so how do we solve that to solve that we will use this kind of operator which acts as a keyword so we don't actually use the symbols or something but we use a keyword to do this when the condition has a range of values to be compared we should be using between an operator radically will display all the information of the students whose marks is in between 80 and 90 from the student table so all the students whose margins is in between 80 and 90 we need to use between and so select the star from the student where marks between 80 and 90. so all the student details who have got the marks between 80 and 90 we the details are getting displayed right if you now look upon this particular screenshot we have written select star from the student where marks not between 80 and 90. now we don't want those values which are in between the range but you want those values which are outside that range so we have to just use not between okay so if you want the values to be inside of the range then we directly use between and but if we don't want the values to be in that range then we want when we write not between similar case if you use higher value first and the lesser value later then you will get a result of no data from okay see you are writing between 80 and 90 that makes sense because 80 and then 90 but if you're writing between 90 and 80 it doesn't make sense to oracle or to sql so it will say no data from now whenever comparison has to be done with respect to set of values we have to use the in operator okay and in operator what happens we have to write a query to display all the information of the students whose age is 20 and 22 from the student table so you want all the details of the students whose age is 20 and 22. so have a look so in the range now understand the difference between between and in in will give you a range which will include only those data which you want to see so in the bracket of in you will only put that value which you want to see there is no range as such but in between and we have a range we have 80 to 90 90 to 100 something like that this is what is the difference between between and and in okay so we use select star from student where age in 20 and 22 the students who is having the age of 20 and 22 we use in operator in that right okay now the next one is like keyword now like keyword is very interesting keyword why because this is very useful in many of the sql queries the like of it is used to search for a specified pattern in a column so let's say that i have a particular student table in which there are many students names i want the student table to sort out that means in alphabetical manner so all the uh names which is starting from a should be above then b c d e f i want to sort it out how we can do that we can use like operator okay so suppose i want all the a people names above so how will use that see there are two pattern matching symbols in sql percentage and underscore percentage is also called as modulus it matches zero or more characters so it either matches zero characters or it will match more characters not one but underscore it matches exactly one character so suppose i have one particular name which is raj and i have one particular name which is ram okay and if i'm writing r a underscore so underscore is only taking one character so in that case r a underscore will give me raj also is the output ram also as the output okay but when you talk about modulus then r a modulus if you write raj ram then more names like maybe rat maybe some other name those all things will come into picture let's take some examples so write a query to display all the details of the students names it starts with r a right so all the names which is starting from r a we are getting the output but we are not using underscore we are using modulus that means zero or more characters not one so either zero or more characters so we have one character m and in another name we have v i two characters right so more characters try to create display all the details of the students whose names fourth character is i from the student table whose names fourth character is i only one character we need to find so we need to use underscore so select star from student where name like so now we need to find all the previous four characters but we know the fourth character is i right so you'll use three underscores and then i why three underscores because we know that the fourth one is i so the previous three letters we need to identify and that is what we have done so we have got getting ravi and kavita because ravi and kavita is having the fourth letter i this is how you will use the like keyword now next one is is null is null by looking into and reading the name you must have understood what is it let's understand what is if null it is used to select only the records with null values in the column only the null values in the column will be there write a query to display all the values of this field whose age is null from student table so let's consider a new table because in the previous table we are not having any null values so in this table we are considering a null value which is in 125 usa and 126 usm so we have a particular age value where null values are there and we need to write a query to display all the details of the student whose age is null from student table so what will be the query the query will be select star from a student where age is null whenever wherever the age is null that particular detail will be shown to you now next one is logical operators logical operator we already know in many of forming languages we have and or and not right we also have this particular logical operators here in our oracle nsql then write query to display all the details of the student whose marks is greater than 85 now understand that this is one part of the question write a query to display all details of the sooners mark is greater than 85 and their names should be not start with r from student table don't you think we should use here like operator and don't you think we should use here the relational operator right now there's one more thing which is and so we can use here logical operator and right let's see what will be the answer of this so i have to start from student where marks is greater than 85 and name not like are modulus because we don't want the r names the names starting with r right so we are getting all the names which is not starting with r and the names are scoring greater than 85 right so these are the questions these are the ways the question will be asked and this is how we have covered the next topic which was operators as keywords so now we are in our next topic which is functions let's understand how and what are the functions which is used in oracle to make our work easy to write our queries easy right now before moving into the each one of the functions we should understand that there are two types of functions one is your single row function and one is your multiple row function right now in a skill these two functions are basically used and what is the meaning of single row what is the meaning of multiple row we can understand by its name itself single row means which particular functions will accept a single row as an input okay or it accept multiple rows as input but produces one result per row okay so let's say that um we have some of the functions which is only giving me the result of one row as an output that's it that is what is single row multiple row is when it can apply to multiple rows and it can give me the result of multiple rows that is what is multiple rules now once we will move to the functions you will be able to understand what that is mean so first we will understand single row functions there are a lot of singular functions we'll first complete that and then we'll move to the multiple order functions in multiple row functions we have some aggregate functions which we also call it as uh group functions so these are nothing but sum max min count average now these all functions are multiple functions and these are some of the functions which have been asked a lot of time in many interviews so let's start with the single row function and let's understand what are these so first of all we have this particular function write a query to display name of all the students so we need to display all the name of the students in lower case letters from the table student so you want it in a lower case later so we have this particular function which is lower l-o-w-e-r that's it and whatever column we want to present it in a lower case we write that in the bracket so select lower name from student right so we got this particular output okay so this is nothing but single row functions let's move to the next one which is write a query to display initial letter capital of data rhythmic so i have a data rhythmic okay and i want it to be written in such a manner that the first letter only should be capital right so we will use init cap which is initial capitalize right so this particular init cap method or function will present me this output in which the first letter is capital as is nothing but alias because i want the column name to be initial i have used the dual table here okay the next question is write query to display all the names and gender of the students in lower case where gender is male now understand the question very clearly i want to display all the names and gender of the student in lower case so first of all name and gender should be using lower function and then the condition is where gender is equal to male so this is what is the query and this is what is the output we are getting the next question is write a query to concatenate data raj now one of the ways to do it is this one you already know that we can use the pipe operator to do this but we also have one concat function which we can directly use to do the same so both things will result into same thing as you can see down right the next function is right equally to find the length of the string so if i want to find the length of any string we'll use length function and give me the length of that particular string right the next function is right query to display the sub string of the string sudeshna from third position extract six characters now understand this question very clearly there are a lot of information given and you should be able to use all this information when writing the query we have the string sedation and it once from third position extracts its characters that means there is some function which uses these three parameters so we have function which is sub str which uses three parameters first of all the string with which you want to play and the second parameter is nothing but the position from where you want to start the extraction and the last parameter is nothing but how many characters you want to extract so it will extract dish na because d is my third position and from there six characters are d e s h l a right so this is how the sub str function will work let's move to the next function which is right query to display the position of the character a in the string charter g okay so what is happening here see we want one character position from the string so a is one character the position of that string is three we know that but how can we tell the oracle or sql that you know this is the position of a character so we use in str okay in str will make sure that you get the position of the character from the string so we write this string as the first parameter and then we can press the second parameter let's move to the next questions write a query to trim the leading a in the string akash akash is a string and you want to trim the leading a leading a means the very first step because we have two a in this name and you want to remove the first a that's it now if you see select trimming trim method we have used leading a from akash so when you write like this it understands that you want to remove the first a from the string if you want to remove the next a then you can see here what is happening now this is not the next a this is exactly the last a okay so write a query to trim the trailing a in the string okay so don't think that in between whatever a is coming will be removed no the last a will be removed so if you see trailing a from angelina so angeline will be the answer because last is getting removed if i write here leading then answer will be n g e l i n a correct this is what is trimming now let's move to the pad now look here what is happening write a query to display the data rhythmic in the format at at at that means four prefix acts and then rhythmic how to do that this is what is left padding so if you see we are using l pad and we are writing rhythmic the total character should be 10 and which character i want to add at which side left side so i want to add at the rate in the left side of the string and the total amount or total length of the character should be then 10. so this is how lpad works similarly how our pad will work it will move the accurate symbol to the right of the string and the total length of the string is 10 so this is what you will get the answer not only at the rate but if you want to put any other characters or letters or symbols you can do that with it okay now we are in our math function so have a look what is happening what will be the output of the following query select around 95.528 comma 2 from dual 95.528 is a number in which i am using the round method and saying that it should round it close to two decimal places so 95.528 will become 95.53 but what about trunk now understand trunk is nothing but will truncate so 135.256 now we are not using round so it is not rounding it is just truncating and making my decimal place to 2 which is 135.25 okay so these are made functions which are really very important try to understand in a very good manner okay next one is mod so what does this do now modulus if you know it actually gives you the reminder right so mod if you use 60 by 2 so obviously no reminder will be left it is 0 right same manner right query to display the h if age is null replace it with 0 so do you remember one table where we were having age where it is having null values so if any null value is there normally that is displayed using a dash right but what do we want we want that it should be getting replaced by a 0 wherever null value is there so if any null value is found we have to replace the age value with 0 this is what is done here the next question is write a query to display the age if age is null replace it with 0 else replace it with 28 so if the value is null replace it with 0 otherwise replace the value with 28 so all the other values are replaced with 28 right this is what is nvl2 and the previous one was nvl correct null value logic fine let's move to the next one now these are the date methods so date functions how do we use it write a query to display the number of months between the dates 24th december 2018 24th march 2018. how many number of months are between these so we use months between method right we can also use between and but months between will give you an exact answer if you see here 24th december 2018 24 march 2018 and it's giving me nine months right in the same manner if you want to add some months to a date so you want to add seven months for the day 24th march so you will write like this add months method 24th march 2018 i want to add seven months to it will become 24th october 2018 right the next one if you want to find the next day of a given day so write a query to find the day date of the next friday after the date 14th feb so after the date 14th feb what is the coming friday that's 21st how and what method we have used next day easy a very easy and simple method names are given here now oracle null if function accepts two arguments to understand this it returns a null value if the two arguments are equal so see null if hundred and hundred it returns a null value if the two arguments are equal so it is returning a null value two values are equal if the two values are not equal it will return me the first argument right this is what is null if so what is the benefit of this the benefit of this is that if the two values are equal it will let you know that those values are equal and if d2l is a different then should return the first argument okay now we are close and coming to multiple row functions but this is the last one uh in single row so these are nothing but the format of date format right query to display the lname higher date where the higher date should be displayed in the format ddm select name because i want that and then higher date also i want to show but in which format ddmm that means date and month so i have to obviously change this higher date format to character so i'm using two car method this is very important again i'm repeating the higher date is nothing but what it's a date format i want to change this to character date why because i want to display it in another format it's a two-car okay let's say that there's a number let's say write a query to display l name and salary the salary should be displayed in the format of dollar ninety nine triple nine point nine nine what is this salary you already know that's a number now i want to represent into a character because i have a dollar here can you see and dollar can't be counted in a number so we are changing it to character so we are using two car right so whenever you want to change from one particular area type to another you use two car to num to date to these all things we use when we want to change the data from one data type to another okay so now is the time that we should move and understand multiple row functions and from here we are going to discuss the multiple row functions which are count max min sum and average so write a query to display count of all the rows present in student table count star very easy you will get all the rows present in the student table write according to display the count of a distinct age like that means distinct values are how many not the duplicate ones so distinct values are six two are duplicate okay the next write a query to display the minimum of all age present mean age very easy we get the minimum age maximum max age you get the maximum age right the next is sum so sum of all marks i'm getting here 694 average of all marks i'm getting 86.75 right very easy multiple row functions where we can directly use the function to calculate the count the sum the average the min and the max value these were all of the functions and methods which were used in oracle and sql let's move to the next and very important topic in sql language or oracle database which is clauses so let's understand now what are clauses and why this particular topic is very very important now when you talk about clauses we have a lot of clauses which we can use in our sql language in our oracle dbms database now these clauses basically tells us that what all conditions can be used and how so let me give you an example write a query to display all the details of all the students in the order of age from student table now if you look here this question is a little bit unique other than all the other examples which you have seen till now because if you look at this particular table of student the question is to display all the details of the students in the order of age that means now we have don't have to actually check whether the age is greater than 20 or less than 25 or equal to 20 or in between 20 and 25 not like that we have to now understand and give me give the result in the age order that means ascending order of the age so first 20 should come then 21 then 21 then 22 again 22 and then 25 something like that right so then we should use some of the clauses so let's see which is the first clause the first clause is order by now the output of the sql query can be predicted but the order in which the rows get displayed cannot be predicted right so the till now whatever examples we have done you have seen that the order is very random in all the results right there is no particular order in which the result is coming but now because we have the order by clause when we use order by we will get all the results in a order ascending or descending now what is the syntax of using this order by so we use order by and then column name if you want to do it in a descending order you have to write the particular term d e s c it will come it as descending but if you want to use it in ascending you don't have to write anything because the sending is by default even if you write asc it will give the default ascending order okay so you can see here that i am getting the ascending order in the result 21 21 20 22 25 and then the other values so the null values will come at the last because oracle is confused whether null should come before 20 or after so it is putting it after now the next one is group by let's understand what is this group by clause suppose that i have a question write a query to display age and the least marks scored by each unique age student from the table student now understand the question it sounds very difficult but it's very easy we have to display the age and the least marks scored by our each unique adjustment that means now we are not actually wanting a duplicate value what we want that we want the age group student who is having the same or similar age we want them to be grouped in one group so let's say that i have five students two of them is of age 20 and two more two of them is of age 21 and one of them is of age 22 so i'll group the 20th age students in one group i'll group 21st or 21 age students in one group and the 22nd one student with a single student in one group okay and in all these groups i'll check which particular student is having the least mass scored that means in the first group where i have the student whose age is 20 i'll check with both of them that who has the least score okay something like that will happen so if you look here select age comma min marks that means minimum marks from student group by age okay so see now no repeated data is coming 22 was repeated but i'm only getting 87. why because 22 was having 2 marks 1987 i am getting only 87 am not getting 90 because 22 age group students were in one group and the least one is 87. now if you similarly look at 21 21 we have 2 mark 75 and 85 which is at least 175 so 75 will be recorded can you see here and then 20 and 25 was alone so they have 85 and 88 and the null value was also 2 if you look here null value was also 289 and 95 89 is getting displayed because that is the least one so this is what is group when you have similar value same matching value you group that particular students or that particular data in one group and then you can apply some conditions on that so these are nothing but the clauses let's see one more example write a query to display gender and the least marks scored by each unique gender student from the table student we have only two gender male and female both male and female will be grouped together so there will be only two data two rows will be shown can you see this so from the male the least score is 85 and from the female the list score is 75 okay similarly if you look at this particular statement what we have done till now skill group by clause is used in collaboration with the select statement we have always used the select statement whenever we have used group by to arrange identical data identical data means unique data into groups the group by clause follows where follows the where clause in a select statement and precedes the order by clause now this statement is really very important see we have so many clauses and all the clauses have some order to write okay you can't just write order by and then you're writing where clause or you're writing where clause and then you're writing order by and then you're writing group by you can't do that there's a certain order in which you have to write it so the group by clause follows the where clause okay that means after where clause group by will come in a select statement and precedes the order by clause so group by clause precedes the order by that means the real sequence of order of these are this particular order so the group by clause follows the where clause in a select statement and precedes the order by clause right so this is how you will actually arrange your particular query that means if you have one question in which you have the you have to use the group by also the where also and the order y also then you have to use these three in the same similar manner you can't do shifting of these here and there otherwise it will give error the group by statement is often used with aggregate functions count max means sum and average to group the result set by one or more column okay now that we have understood the order by and group by there is one more clause which is having clause okay but i'm not gonna show you that right away because i have something extra to tell you now there are three clauses which you have seen actually there are four which is where order by group by and having right now why i'm not showing you the having clause because there's a very big question in the id industry that why we have having clause when we already have group by because having clause whatever it does it can also be done by a group by then why is the need of having now understand that group by is often used with aggregate functions but having clause cannot be used with aggregate functions this is the main difference why do we have having clause and we'll understand what are the other differences which makes it distinguish between group by and having okay now let's move to the having clause and let's understand what is there okay before that just one example of group by efficiency select gender max marks from student group by gender right one more question from group by right equality to display age and count of the age for all the students whose age is equal to 21 from the table student we are using count that means we are using multiple row function that means aggregate functions and this is also used only by group by so we have this select age count is from student where a is 21 group by age okay so wherever the count is wherever the age is 21 show me the age show me the count of those age there are two students whose age is 21 that is what i am getting next write a query display age and the maximum marks is scored by each unique age student from the table student same kind of question just twisted select age max marks from student group by age we are getting the maximum marks not the minimum margins time okay right query to display age and the maximum mass is scored by each unique age student whose maximum marks is greater than 85 from the table student now understand what we are doing here see in the earlier till now whatever problems we have seen we are just using where and group by right so after where we are using group by correct till now we have not used any such particular program where we have used something like this now can you see what is happening so the question is write a query to display age and maximum marks so we are displaying age maximum marks scored by each unique age student whose maximum marks maximum marks is greater than 85 from the table student unique age student that is why we are using group by right so understand that how you have to use the query and write a query we can't use here where because maximum mass greater than 85 we are using having clause here we can't use where maximum mass is greater than 85 right because we are using group by also okay let's not get confused let me tell you what is happening here the having clause was added to sql because the where keyword could not be used with aggregate functions so if you look here in this particular statement we are using where clause because there is no aggregate function here right where there is no aggregate function here but when it comes to this particular question we are using here max marks and if we write where here then it is wrong because where cannot be used with aggregate functions like count sum max min and average okay and that is why we should use having clause okay again i have already told you this but reiterating that these all clauses which is where having order by and group by these all have one sequence which you need to follow the second thing is that why there is a group by and having and where clause also because there's a difference between all these three group by actually groups something where and having does the same thing but where cannot be used with aggregate functions having can be used with aggregate functions that is why we have having clause so this is what is the syntax of whole query starting from select from where group by having and then order by this is the sequence which you need to understand and follow whenever you're writing a query next write a query to display age and the maximum mass is scored by all student understand the question in a staggered manner right according to display age and the maximum marks scored by all student whose age is greater than 19 and having maximum mass greater than 85 for each age while displaying the data in ascending order with respect to age so we know that we will be using here the aggregate function so select age max marks from student where age is greater than 19 then grouped by age because it is asking for each age so we know that we have to use group by having a max max greater than 85 why because it is written here maximum mass greater than 85 and we can't use where here because max is a aggregate function that is why we have used having and then it should be ordered by age so this is what we have shown you one example using all our different different clauses so now we are in our next topic which is nested query and this is also one of the most important particular topic because in most of the interviews if any question has been asked you need to identify whether you have to use subquery or net query or is it just a normal answer right and that particular logic if you know if you know that when to use sub query or when not to use sub query you understood sql database okay so let's understand what is this nested query obviously nested query means a query inside a query but let's understand in an example manner what does it mean so we have this table we remember this particular table from the starting and so we need usn name and gender of all students whose marks is more than ravi's marks from the table student so we need to compare the details with ravi's marks and then we need to display okay so select usn name gender from student where marks is greater than select marks from student where name equal to ravi now what is this you can see here inside the bracket this is one query of select statement and then we also have one more student which is a select statement now this is what is called as nested query or sub queries when we have one particular query inside another query that is what is nothing but subquery or nested query now the question arises is which particular query will get solved first so the inner query should get resolved first and then whatever will be the result of it that result will become the solution for the first query as well so let's divide this particular query into some small small chunks and let's understand what we have done so first of all this is the table and this is the query which we have written the inner query says select marks from student where name equal to ravi so you have to select the mark from student where name equal to ravi so ravi's marks if you can see is 88 right so what's what will happen marks equal to 88 that means this whole query now gets changes to 88 so now the next query will be select usa name gender from student where marks is greater than 88 correct we have to actually print the usa name and gender from student where marks is greater than 88 so we have this one and we have this one correct and that is what we will get in the result so we have kavita we we have okay this one also right so this is the answer for that particular query and this is what is called as sub query now understand that when you are reading the question select a query to display us a name and gender of all students whose mass is more than ruby's marks on the table student sounds very easy right sounds like i know claus i know this that so i will be able to do it but this question nobody was knowing that can be solved using sub query in a very easy way so that is what interviewer wants from you they want that you should come up with a very good solution of that question with a very good query right query to display the usn name up for all the student whose age is same as archon's age from the table student so obviously we'll use the subquery usn name from student where age is equal to select age from student where name is equal to arjun i don't think we need an explanation but still let's see what is happening select age from the student where name equal to arjun arjun what is the age 22 so this will change to 22. now we have to use the usn name for student where 8 is equal to 22 so we have 2 arjun and kavita that is what it is showing me right a sub query is a query within another sql query and embedded within the where clause sub query is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved there are some properties of sub queries that should be include enclosed within the parenthesis sub query can have only one column in the select clause unless multiple columns are in the main query for the sub query to compare its selected columns and order by cannot be used in sub query these are some of the pointers which i'm underlining and these are very important when you talk about sub korean nested query order by cannot be used in an sub query although the main query can use an order by so the outer query can have the order y but the inner query cannot have the order by okay the group by can be used to perform the same function as the order by in a sub query so don't use order by in this sub query if you cannot use use group by sub queries that return more than one row can only be used with multiple value operators such as the in operators okay perfect let's move to the next question write a query to display name age whose age is equal to this student named ray ram and marx is greater than a student named anjina from the table student now this particular question is really very frustrating why because you can see here we need to display name age whose age is equal to student name dram and marks is greater than student named anjuna let's understand what we have seen now there will be two sub queries because we have two such statements i hope you will be able to find the two statements select name age from student where age is equal to what equal to student named ram select age from student where name equal to ram and the next is and marks is greater than select marks from student where name is equal to anjuna only one output which is ram 21 so now let's segregate and let's understand this particular query what is happening we have the table here select marks from student where name is anjuna so select marks from student where name is anjuna what is the mark 75 so this particular statement this whole statement will result me what will result me 75 right the next select age from student where name equal to ram so where is the rom name i want the age to be 21 so this will become 21 now the first statement will be that select name age from student where age is equal to 21 and marks is greater than 75 so let's find that select name age so name and age we want one student where 8 is equal to 21 8 is equal to 21 here and here and marks is greater than 75 which is only one which is ram that is what we are getting okay now this is what we have done let's understand one more uh so this is one table which we have created student department okay we have department id usn core city and pin code uh how to create this you already know you have to create a table and you have to insert these values i have written some codes here so that you can actually copy it down and put it in your oracle database but time being let's understand what problems or what questions i'm asking here so write a query to display the department id of all the students from student department table whose usn in student table is equal to u.s indian student department usn in student table student table is the earlier table student department is the this table okay so what is the question here the question is write a query to display the department id of all the students from student department table now understand select department id from student department because that is what i need to see where usn in now this is what we have written the properties that in sub query we can write in wherever we want so in sub queries we have written in select usn from student whatever usn is there in the student table whether it is matching in the student department table if yes show it to me that is what it is saying so let's understand this particular example in staggered manner so we have the student table and we have the student department table we have the student table this one and this is our new table which was student department what is the first line the first sub query select usn from student so this is the usn from student table so this all usns will be recorded so we have 120 121 132 23 24 27 25 and 26 these are the usns which we have got from student table the next is select department id from student department where usn in 120 121 now what is happening here basically all those values are now coming in a bracket within the in method right and what happens in in operator in operator says that all these values should be matching so 120 should match 121 should match 122 123 all these values should match so from the department id which is here which all usns are matching so we have 125 let's cut this 123 let's cut this 124 let's cut this 122 let's cut this and 121 and 126 so these are all my values and depending on that what all department ids we have 21 22 23 24 25 and 26 correct if you see here for 120 and for 127 we have not got any department id right so this is what is the theoretical and practical knowledge on nested query also known as sub query so the question is write a query to display the department id of all the students from stu department table whose usn in student table is equal to usn student department right so we have to display the department id for all the students from stu department table whose usn instrument table is equal to the usn student department so let's say that select department id from student department student where the student department is equal to student usm why this is so easy why we are not using sub query here because here we are only making two things equal the usn of student department and the usn of the student table both are the same things that is why we are not actually needed to use sub query right so you have to understand where you have to use sub query where you have to use the nested queries and where to not so in this example this was easy one if you again read the questions one by one you will understand that where to use sub query and where to not so this was all about nested query let's move to the next topic and let's understand what is that so now we are in our next topic which is joins in oracle sql and this topic is really very important because joins is such a topic which is asked by every interviewers right and from here many questions will be asked and it will be twisted and it can be very hard right so please understand this topic very carefully let's start with the introduction of joins sql is a structured query language right now in structured query language we write a query when this query will run we will get some output okay this output will be in table format right now why this particular query has to be written because there will be a question okay and we have to solve that question that question will be solved using this query let's say that i have two these two tables please have a look at these two tables so we have the impidi name gender age salary the typical table of employee right what a employee table should have an id the name the gender the age and salary then we have the employee department table that means this particular table will have the department information so we have department id employee id now look at this this employee id and this employee id is the primary key okay so in sql we have a concept which is nothing but rd business right relational database management system so what happens in that if you have two tables those two tables should have one common column okay if it is having one common column then only we can say that those two tables can apply relational database management system okay now we have those two tables and both the tables have a one common column which is impiding right perfect then we have the extra columns which is department city and pin code great i have showed you the tables right now what we have to do the question is write a query to display name gender age and marks of all employees whose impiety in the employee table is equal to the implied in the employment department table too much long right the question is like i don't understand what is happening here right so let's divide this question into two parts first part is write a query to display name gender age and marks of all employees this is one part so what we need we need to display the name gender age and marks so that is why i've written here select name age gender okay so it's written here marks let's say your salary right so i need name is gender and salary okay of all the employees from employee department so one particular thing is covered what is the next thing the next thing is the condition see whose employee id in the employee table is equal to the employee id in the employee department that means in this table the employee id from the employee table should be matching with the employee id in the employment department table so let's match it 206 is it there yes it's there 8 is it there no it's not there 2 0 3 yes it is there 2 0 1 yes it is there 2 0 5 yes it is there 2 0 4 no it is not there 2 0 7 and 2 0 10 both are also there right so now we know that there are six items which is matching from both the tables so these all six tables will be shown here you can see here these all data are shown here but what is the condition that we have written where employee dot imp id that means from the employee table impid is equal to employee department inviting easy so we have done the same thing right we have what we have done we have written that we want name a gender and salary from which table from employee and employee department where the employee id should match from both the tables and that is what we have written here very easy query right so we got the output also but the problem here is what if i want to show in the result department city and pin code also till now what we are doing we are only showing name a gender and salary but now i want to show the data or the column of another table also then what you have to do then you have to use the join keyword how we can do that let's see now we'll understand what is join so join is a means for combining fields from two tables by using values common to which so we have two tables and we have to join these two tables using the common column which is there so we have one common column which is employee id in both the tables we can join it there are different types of joins inner left right full natural and cross we are going to discuss these all right away have a look here let's understand what is this have a look table one let's consider that this is the orange color table one then we have the table two which is a green color now these blue color things are nothing but the common data okay these are the common data so when i talk about inner join it means that it will return all rows from both the tables where there is at least one match in both the table so all the common details will be coming in the inner join all the common details in hindi if i speak then joby common data post sorry data in donor table say fetch holder so from both the tables whatever the common data is there that will be coming in inner join when you talk about left join in left join the data of the left table complete data of the left table plus the common one can you see the orange color and the blue color so all the data from the left table but only the common ones from the right one see return all rows from the left table and the match draws from the right table okay if you have understood this then right join will be very easy to understand but if you have not understood the left join don't worry we will go to left john and we will see some examples and we'll understand what is happening there okay so don't worry about that right join simple the same thing opposite to left join here the right table all the data will be coming and only the common data will be coming from the another table right so you can see the uh see the portion which i'm engaging here the blue one and the green one right so basically all the data from the right table but only the common details only the common data from the other table now let's talk about full join full join is very very easy it's nothing but sub everything will be coming in the table in the result resulting table so the left table also complete the right table also complete and the common details also complete everything will be coming so you can see return all rows when there is a match in one of the tables right this is what is your different types of joins the rest of the joins which is nothing but natural cross self those are really very easy if you understand these then this is good to go now let's move to the inner join let's understand with the question you remember the two tables which i showed you employee and employment department let's understand that so when it comes to inner join or join right we can use both the terms we can use join also we can use inner join also now have a look write a query to display all the details from employee and employee department so can i say that this is the first part of the question so let's solve that write a query to display means we want all the details everything every column we want every column from employee and employee department where the value of employee id and employee is equal to the value of employee id in employee department so will i write the query like this right select star why star because all details we want from which all tables i want from employee and from employee department on which condition basic i want i want the condition basis to be employee dot employee id equal to employee department dot employee id great my query is done and i'm getting all the necessary columns can you see employee id name gender age salary these all five columns where they're in employee table then we have department id employee id department city in code these five were from the employee department table right now you can observe that everything is coming here so that means we have joined the two table but one more thing to focus here guys employed id is coming twice right so whenever you are using inner join the common column from the two tables will come in twice that means duplicate column will come so you can see employee id is coming twice the data is also same only the common detail is coming do you remember this this particular drawing what is inner join only the common detail you see only the common detail is coming correct okay we will talk about it later that how can we remove this duplicate column how can we remove it we will talk about it later don't worry let's come here can you see this inner join okay can you see i've written your inner join now i'm changing the screenshot okay let's have a look so you know on a count of three i will remove the screenshot one two three can you see now i'm not written inner join i've written join right and the output is same the output is not changed so either you use join or inner join it doesn't matter it works the same okay it just joins and gives you the common columns the column details the common data now here see again i have changed the screenshot can you see employee e1 so i've given an alias name see employee employee department is such a long name right such a big name i can't use it every time so i've given a alias name to it like you have a home name and you have a uh official name right so we have do we have done like that so employee is the official name but even is the home name like that e2 right so we are using e1 dot impid e2 dot m by d now we are not writing employee dot impid imp department dot impid no we are directly using the alias name this is called as elias okay that means giving another name to the table now we are using inner join we can also use join i hope you understood the concept of join or inner join right now the next topic which is left join so left join left outer join now we have a writer query to display all the details from employee and common details from employee department by using left join it says that we have to use the left join now understand the question write a query to display all the details from employee this is one part that means i want every details from employee but we want only the common details from employee department not everything so what is the situation here the situation is that i from one table i want everything but from other table i want only the common details can we use left join so in the left join what will happen see i have written the left join here right and the table which i have written on the left hand side is which one employee so from employee all the details will come but from the employee department only the common details will come easy what will happen if you swap that swap in the sense what will happen if you write here employee department left join employee then employee departments all detail will come and employ only common details will come that i will show you later on right now let's stick to this perfect so now what you have to do we have to see how it is coming how this thing is working right so if you see remember we have eight data in both of the tables right employee table we have eight data and in employee department also we have eight data if you see here it should show me all the details from employee can you see all the eight data is shown here but from employee department can you see all the data no only six data is coming why only six data is coming because the two data which we have is not common see two zero four and two zero eight do we have 208 and 204 here in this employee department table we don't have it right that means two of the data are not common two of the data details are not given in employee department and that is why that is the reason it is not showing in our left join table also right that is why these are blank and you see these are blank why because the data is not common in the right hand side table okay now i have written here left join so you can see left outer left outer join we can write that also you right left join left outer join doesn't matter then we have the alias name e1 and e2 you can see that so you have taken that you have used left join left outer join also we can use and we'll do the same thing right easy if you have understood left join now i hope you guys have understood let's move to the right join till then and let's understand what is this right join means that right table everything will come but the left table only common details will come so have a look what i've written write a query to display common details from employee now i need only common details from employee but all the details from employee department right now query summons you will get the query in your mind itself right that what it will be select star from employee right join employee department that means the table which is in the right i want all the details from there but the table which is in the left i want only the common details from that and you can see from the employee table only the common detail is coming only six data when we were when we were using left join all the details were coming but now it's opposite it's ulta right see see from the employee department table i'm getting every data all the eight data why there are blank spaces why there are null values because there are two data which is not matching which is not common that is why i'm getting a blank space here correct this is what is your right join can you give example and how it works in condition clauses just surely i will uh try to code one particular code where i can show you that you know how you can put a condition also let me do that i'll go to my oracle and here i have the table let me show you how it looks select star from let's say i'm writing employee okay i'm writing employee and i'm putting right join right and then i'm putting employee department guys please look at this particular query which i am writing right away this is really very important as one person has asked me that please provide a condition or something right right so let's do that right join employee department and here what we will do is okay so we have the condition now we have to write the condition so have a look what we are writing on employee right so that's our condition so let's write on employee dot imp id equal to imp department and then the imp id this is what is my condition if i run this what will happen i will get the same data you can see once again there is a employee write join on employee.mpid okay so here i have not used the dot yeah so you can see you are getting the table the same table which i have added in the screenshot right the same thing but now suppose i want the from this data i want only those particular columns which is having salary greater than 4 4 lakhs or 5 lakhs so what i should write where employee why i'm writing employee because salary column salary column is on employee tables i'm writing employee dot dot salary equal to uh let's say there are most of the things uh most of the salaries which are greater than five lakh so let's write five lakhs okay and run this am i getting all the data which is having salary greater than five lakhs yes i am getting you can see right so this is how you can use the condition clause which is where right and you can get the output perfect let's move to the next topic which is nothing but pull join so here what i have told you it's very easy right everything will come here everything so let's do it and show it to you in this particular example itself i want to show you one more thing guys have a look if i hit enter and if i write here select star from employee let's write full join and let's say imp department okay now you can see there are two queries which i've written here right now if i run this what will happen you can see invalid sql statement right why it is invalid because i have written two queries in the same box what if i'm selecting one of them and then i'm running okay so now it's saying missing keyword because i have not written the conditions how to write the condition as well let me write the condition employee id is equal to imp department dot right let's run this let's do it one by one let's run this okay so i am getting the whole table if you can see what is this the full join right now if you can see from the employee table also i'm getting eight data and from employee department table also i'm getting eight data if you can see who's here five two zero three two three six two three seven till two zero eight eight data and then from the department also you can see i have eight data if you want to see it see it from here also six here and then two here so we have the total eight data from employee table and we have all the eight data from the department also right so this is what is full join now wherever the columns are not matching or wherever the data is not matching there we have the null values can you see this is what is your full join try to do this in your own systems guys if you want try to do it and practice more in your own oracle database engine you will not be able to gain confidence in this okay so try to do that now let's see what we have done full join can we write full outer join yes we can can we use alias we can can we use alias with full outer join yes we can anything is fine you just have to write the query correct okay so we have seen lot of our different types of join but still there are some of the joins which is left right so let's talk about that mainly where we use this query i'll tell you where we use this query see basically every organization every bigger organization or any business right will have database okay let's imagine let's take an example of uh myntra.com right in myntra you guys will shop right what we guys do vega is only shock but we don't consider or think about what happening what's happening in the back end basically our all details our all data is getting recorded somewhere and all those datas are recorded in the table format like this okay now suppose mintra.com wants to know that which particular customer is very frequent in my app who is very frequent in my app like he has opened the myntra app every single day so what he can do he can write a query where he can write select star from intra table where the opening of the app is very high right the amount of opening the app is really very high so all the list of names all the details of those names will come up who have always opened the myntra app so they will get to know that these are our regular customers we have to give something to them or we can give some offers to them so that they can again shop right so these are the queries which actually our organization or most of the companies uses to play with the data to manipulate with data okay suppose they want to find some of the people who are not at all using mindra app from three years then they can remove their account they can remove their account from their database correct so those are the things now here we are coming with the new topic which is natural join now understand what is natural join right because in the diagrammatic representation i did not tell you what is it right so let's understand that natural join is a join operation that creates an implicit join clause for you based on the common columns in the two table being joined so there are two tables okay there are two tables one of the column is common right now what is happening in inner join basically whatever common column is there it's coming duplicate right it's coming twice remember i showed you that imp id column was coming twice in the resulting table right but here in natural join it doesn't happen so see if if a question is there right equal to display all the details that means they want all the details where the value of impid in employee is equal to value of empire in employee department then we have to use natural join and we use natural join then what happens i'm getting only one employee id column can you see there is no other employee id column so duplication of column is not happening here so inner join and natural join is somewhat similar but natural join is better why because here it doesn't duplicate any other column it actually gives me only one column and with all the other data but have a look right this table is looking quite amazing because we don't have any errors in this we don't have any duplicate row we don't have any duplicate column this table is looking perfect this is nothing but your natural join okay one more thing if we can use natural join as an inner join can we use natural join as a left join certainly we can have a look natural left join so whichever table is there in the left hand side it's all data will be presented see all the eight data are present but from the um employee department table only the common details are present right this is what is your natural left join natural right join the same thing will happen so we have understood one thing from here that duplication of the common column will not happen the second thing which you have understood if you have noticed guys if you have noticed we are not writing the condition on employee dot imp id equal to employee department dot empire nothing we are not writing the condition itself this is the use of natural join this is the benefit of using natural join so these are the two major things major uh benefit of natural join right it's a natural right join same thing and naturals full join can you see everything is coming every data is coming eight data from employee table and eight data from employee department but employee id is only one column right so i hope you have understood natural join now we are jumping into the cross join let's see what is cross join this is really very fun because here we multiply everything to anything let me tell you what is this suppose if i have a table okay and i have one row suppose i have three rows one two three four rows okay and i have another table and here also i have four rows 1 2 3 and 4. what will happen in cross join it will cross it what is cross something like matrix multiplication if you if you are from the science stream you must be knowing matrix multiplication what happens this one row will get multiplied with every other row of the table every other row of the other table then after that the second row from this table will get multiplied with every other row of the other table so what are we doing here we are multiplying every row of one table from every other row of other table right have a look artisan join or cross join this is what we also call it as cartesian join or close line returns the cartesian product of the sets of record from the two or more join tables see if i have these two tables what will happen the very first row will get multiplied with the very first row of the second table second drop the second table third row of the second table and it will go on so if the first row is yt multiplied with the first that means eight rows will come one is the first one the second third fourth fifth sixth seventh eighth this will also give me eight this will also give me eight this this this this and this if you add these all those many rows i am getting into my resulting table those many rows i will get into my resulting table and will look something like this so i have eight data from aman eight data from tanisha eight data from her breathe which i have not given because the table was really big this is what is your cross join where do we need it where do we use this cross join can be used in any table any database y see if you want to understand that one particular person is living in which area and what are the different cases like suppose 206 aman male 24 which who is earning 3 lakh 50 000 his department id can be 21 can be 23 can be 22 can be resolved so he can he is the possibility of working on all these department like that if you want to cross you can do it okay this example certainly doesn't look good when you talk about cross join but yes we have certain different examples which will explain why do we use cross one okay the next one is sales join and this is the last one so let's understand how the cells join actually works now the sql self join is used to join table to itself as if the table were two tables now we have seen a lot of joins you have seen inner join left join outer join right join natural join cross join and you have seen a lot of variations in these joys right like if you talk about inner join it actually tells me the common data right and when you talk about cross join it actually cross joins both the tables and gives me a very detailed view of all the data but when you talk about self join there are no two tables there's only one table which is actually joined with itself now you will ask me how does that happen right because if you don't have two tables then there is no meaning of joining anything right now how does that happen now to join the table itself means that each row of the table is combined with itself and with every other row of the table okay now to understand this we have to clearly understand the cross join because it is much more similar than cross join okay now what is happening the table itself is getting divided into two parts right so let's say that i have a table which is a student table and what we will do is we will create student table and this particular self join whenever we will use decision table will be recreated into two tables student a student b and then these two tables will be joined okay now this division we are not doing this division is happening because of cell join itself so if you look here we are using only one table which is table one in the syntax but we have clearly said that it should be divided into two tables table one a table one b and then from a table one we want the column name and from b table one we want the column name the same column name and the condition is that the common field should be matching so if you look here we have this particular table in pi d name gender agent salary just have a look clearly what are the things present in this table because we are going to use the self joint to this table right so self joining means that whatever code we are writing we are writing it in a way that two tables are created so we have employee table employee e1 is one of the table and employee e2 is second table from employee 1 we want employee id and salary but from employee 2 we only want the name where employee one salary is less than employee to salary now you don't know this employee table will get divided how and from where let's say that this is from e1 this is from e1 this is from e2 this is from e2 this is from e2 this is from e1 this is from e2 and this is from e1 some random selection will happen and randomly these all tables will be divided what i have written is just an example now in e2 we have this this and this data and this data and what is it saying that we want only the names from this table so we want the names ram vishal and rohan and from employ one what all things we need we need the id right so we need these ids and we need the salary so this this this and this right so these are the things which we need or which we need to display in our resultant table right but the condition is even salary should be greater than e2 dot salary so if you look here even salary which is 33 lakh 50 000 the next is 4 lakh 60 000 these all should be greater than the e2 salary which is eight like ninety thousand feel like fifty eight thousand five lakh sixty thousand and seven lakh right now what are those data so those data if you look here see three lakh fifty thousand if you just see the salary part right the tick ones are e2 and the underlying ones are e1 now you have to make sure that which ones even is greater than e2 so 4 lakh 60 000 if you look here now to do this it's a very tedious task right we can't do this manually so let's see what we have come up with now if you look here i have created one impide salary for employee one and the only name from employee two right now all these rows all the rows from each of the tables will get multiplied with each of the rows from the another table and this will give me an output of something like this so we have 206 tanisha and then three like 50 000 206 similarly for all the things now we have to understand that even salary is greater than e2 salary where and all it is there right so that is how you actually do these self join now we are in our next topic which is select from multiple tables without join now this is very easy we don't have to use join topic without join we have to select from multiple tables this we have already seen but let's see it once more write a query to display all the details of employees whose employee id in the employee table is equal to the employee id in the employee department table without using joins imp id in the employee table and imp id in the employee department table without using joins now if you talk about this particular topic we have already seen one of the statement where we are not using joins which is this select star from employee e1 imp department e2 where e1 dot impide is equal to e2 dot imp d we are not using joins but still we are getting the all joined tables resultant table right now this is one thing join multiple tables with conditions now with conditions also we have seen one example but here we will see one more example and try to understand that how we can join multiple tables with condition so write a query to display salary employee id from employee table this is one part and department city from employee department table this is another part whose employee id in implied table is equal to the employee id in the employee demand payable and salary is greater than 4 lakh so this is one of the condition now that salary should be greater than 4 lakh so if you look at the query select e1 dot salary e1 dot imp id e2 dot department e2.ct from employee 1 inner join employee department e2 on employee e1 dot implied equal to e2 dot m5d where even that salary is greater than 4 lakh this is what i wanted and we are using a condition so we have only these details where the employment salary is greater than 4 lakhs right so you can use a condition like this by using a where clause now the very important topic and the last topic in joins which is how to join three tables in sql so how you can actually join three tables in sql let's first consider three tables we have employee employee department and department very small table for this particular example so we have an employee where we have employee id employee first name employee last name we have employee department where we have employee id and department id we have department where we have employee id department name and department head id now if you see employee id is common in all the tables right that means these are relational and if you clearly look here we have the employee first name and last name in the employee table department id which is again a primary key for employee department and department name and department head id is something which is in department table right now we have to join these three tables can we join first of all the question is can we join three tables yes we can join three tables but the queries should be written perfectly now if you look here step by step i'm going to show you what all queries we have written and how we are joining so first we have written select employee.employee first name comma employee dot employee last name for employee very easy what you will get you will get all the employee first name and employee last name from the employee table that is what i am getting right perfect these are the just three tables which i have shown you here to show you that what are the content in the table so that you don't get confused next now what i am writing select employee dot employee first name employee dot employee last name from employee join employee department on employee loading pid equal to employee department dot infinity now we are using the join keyword with employee department that means this one and this table is getting joined together on what basis on employee id basis but we are only displaying first name and last name make sure about that so we are only displaying first name and last name but with in department table so two one and four twos and one threes that is why these data is also getting repeated so till now we have joined two tables let's see what is next now select employee dot employee first name employee dot employee last name from employee join employee department these are things we have done next i'm adding department or department name now i want the department dot department name this part also in my result with first name and last name so department name first name and last name these three things will be shown on the basis of join department employee id dot employee id so this is what is the result on the basis of these three unique ids or primary key we are getting this particular result where we have the department name first name and last name and this is how you actually join the three tables so this was all about joins let's move to the next topic and let's see what is there in the next topic so now let's understand and clear out what are the different sql commands in oracle database and what do they do and all the different types of sql commands what are their works individually right so first of all let's see what all typical sq commands do we have so if you can see here we have ddl dml dcl and tcl now these are some of the four main commands which we actually use ddl is data definition dml is data manipulation dc news data control and tcls transaction control language okay now these all are different in uh work okay but we'll try to see all of these in a separate and individual basis we'll see all these commands individual commands one by one by example and we'll try to understand how these all are different from each other so let's first start with ddl and let's see how this create drop alternate truncate works now before jumping into that let me tell you that this is data definition language that means it defines the data right so when you want to create a particular table that is where you are defining the data dropping the table that means when you are deleting that is defining the data alter order means when you want to change the data that is definition truncate again delete so that is also definition now you will try to see here that drop delete truncate are three of the commands which is looking like a similar command right drop delete and truncate all of them are nothing but deleting but what is it deleting that what we have to see so let's start with ddl and let's understand what is data definition so how to define data ddl changes the structure of the table like creating a table deleting a table altering a table these are nothing but changing the definition of the table all the command of ddl are auto committed auto committed means that once you are writing that particular program it's auto committed it's it can't be changed okay that means it permanently save all the changes in the database okay the first one is create so it creates a new table in the database this particular command no need to again revise because we already know how to do this we are using create table name table table name and then the column name data is whatever you want to put this is what is the syntax of create table we already know about this one question based on this write equally to create the table codes with columns such as course id course name course duration hospitality this is how you will write the create table command for this particular question and i don't need to explain it because we already know this let's move to the next one which is drop now understand what drop does it is used to delete both the structure and the record store in the table that means if you have designed a table there will be a structure right like two rows or three columns or four columns there'll be a structure and another thing which comes here is data so inside this table you'll have data now when you're doing drop drop means dropping that particular table the structure also the record also everything so when you're dropping the table you are actually deleting everything of that particular table cite a query to delete the structure and record of the table then you have to use drop drop table codes so once you do that after dropping the table course if you try to find the content of the course it will say table or view does not exist nothing exists even the structure is not there right and once you try to find out the description of that particular course table now what is happening is object to be described could not be found because there is no table as such there is no structure also so object to describe is could not be formed okay now let's move to the next one which is what happens if we try to drop dual tables so this is one of the question dual table is a default table right and if you want to drop it then what will happen so if you want to drop the table dual table or view does not exist see dual table is a default table which is provided by a oracle right now it is not a particular permanent table it's just a table from which you can play or which you can practice so you can't drop dual table now let's come back to the alter what is alter it is used to alter the structure of the database this change could be either to modify the characteristics of an existing attribute or probably to add a new attribute so if you want to add something or to modify something we use this now these three are very important please understand it one by one to add a new column in the table let's say that i have three columns but i want to add one more column it can happen right that you have name age and gender but later on you decided that we should have ct also then alter table table name add column name and then column definition this is how you will write a one particular syntax or enquiry to add a new column to your table to modify existing column in the table that means if you have a column already and you want to modify that then alter table table name modify the column name which you already have what particular change you want there you rename existing column name in the table if you want to rename the existing column name let's say that my column name is age and i want to permanently change it to years old not an alias do not get confused alias is a temporary name change this which you are doing is renaming that means permanently changing the name so rename column old column name you can see here old column name to new column name okay let's do some questions write a query to add a new column in the table course with columns such as course fees so i want to add a new column alter table course course is the table name add course fees course views is my new column name with this description right next question now if you see the description of the course table you can find course pc right okay now let's see next question write query to modify the column course fees in the table course which size is 20 now if you look here the size here is 30 right and now this particular column is already there in the table now what i want to do is i want to edit this particular column i want to alter something in this column so i want to modify the column and i want to write the precision to be 20 not 30 so alter table course modify course is number 20 instead of 30 i've written 20 and you can see that it is not changed to 20 right next one write a query to rename the column name course faculty in the table codes with course trainer so i want to replace the name course faculty course trainer so alter table course rename column course faculty which is the old name to course trainer right so table is altered and you can see here that course faculty is not changed to course trainer right so this is what is altering let's move to the next one write a query to rename the table name course to course description now i want to actually rename the table itself so table name is course but i want it to be our course description there are two ways alter table old table name rename to new table name or otherwise what you can do just write rename old table name to new table name so we are just writing alter table course rename to course description and that is done or you can do rename course to course description right why i'm adding this screenshots here to show you because this is the proof that it works right so don't think that i'm not going to the oracle and i'm showing you because it will take time that is why i've added directly the description or the screenshots right so now when you see the description of code description you can find here that the table name has been changed permanently okay so now let's move to the next question which is write a query to delete one of the column from the course table so i want to only delete one of the column alter table course course is a table name drop column course name if you want to only drop one of the column then drop column course name course n is the column name this is done and you can see here that column name is not there right okay the next one the last particular command is truncate let's see how is it different from drop it is used to delete all the rows from the table and free the space containing the table it is used to delete all the rows from the table and free the space containing the table but for structure there is nothing written here so if i want to write a query to delete all the rows and not this structure then we have to use truncate so no data is found but it is not saying the table doesn't exist it is just saying that data is not there now once i want to see the description of the this particular table what is happening you can see here that description when i'm truncating it is not deleting the structure the structure is as it is it's just that the data is gone okay so truncate will just delete the data or the rows or the values but not the structure of the table whereas drop actually drops everything okay now we are done with the ddl so now we are jumping into the dml which is data manipulation language that means how to manipulate the data that means how we can actually insert something inside or delete something or update something inside the values not to the table now gmail commands are used to modify the database it is responsible for all form of changes in the database the command of dml is not auto committed remember that ddl is auto committed but this is not auto committed that means it can't permanently save all the changes in the database it can be rolled back it can be done undo okay so insert the inserted statement is sql query we already know what does it do right so insert actually inserts the values inside the table we already know about that write a query to insert all the values to all the columns of the table codes we do this and all the values will be inserted it will look something like this right okay the next one this command is used to update or modify the value of a column in the table so once you want to update or modify the value of a column then we use update how do we do it update table name set column name equal to value 1 column name code value whatever columns you want to change if you have any condition you can put the where condition as well so write a query to update the value of the course fees from 6000 to ten thousand where course faculty is rhythmic in the table course so we have the course we see here and we want that six thousand should be changed to ten thousand wherever the core standard is written so here and here right so this is what i want how to update this update course which is the table name set course fees equal to ten thousand where course training is equal to lithium very easy this is how you can do the updation so select star from course now you can see the rhythmic course trend is having ten thousand course fees right now delete so it is used to remove one or more row from a table again drop drops everything truncate just delete the rows or the values from the table but keeps the structure delete is used to remove one or more row from a table that means one data or one of the sale data only that will be removed which one you want to remove that will be removed write a query to delete all the rows from the table course where the course is taken by so i want to delete only those course details which is taken by arithmetic so delete from course where question is equal to arithmetic i hope you understood the exact meaning of these three things drop truncate and delete so do not get confused because this is one of the very important question asked in the interview so you can see rhythmic information is now gone next what happens when no condition is specified in delete statement example delete from course so what happens when no condition is specified in related statement let's say i'm just putting delete from course so everything will be deleted that means every data will be deleted okay so if i write select start from course it says no data found and if i want to see the description of the course it's it is having this structure the data is just gone it's having the structure that means if i write delete without the condition it works like truncate isn't it truncate also does the same thing right it just keeps the structure and deletes all the rows so if you are using delete command in dml and not using any condition it works like truncate itself right that brings us to our third command which is dcl which is data control language so how to control data right now how to control data is nothing but there are two things either we can control it by giving permission or taking permission right so there are two commands which is brand or revoked now if you see here dcl commands are used to grant and take back authority from any database user so either i can give privilege or i can take the privilege that is what we do in dcl now grant it is used to give user access privileges to a database how do we do that grand privilege name on object name to whomsoever you want to give now what is this privilege name public and role name privilege name is the access right of privilege granted to the user object name is the name of the database object like table and username is named the user to whom an access right is being granted i'll tell you how to do this let's understand with an example so once you download oracle you'll be able to find sql command line itself let me show you so if you go to your start and if you scroll down we'll be able to find oracle folder and inside that you will be able to find run sql command line once you open that this particular sql plus command line will open and you can start writing your grand revo commands right now let's see what happens here i'm not going to show you here but in the ppt everything is given so let's move back so here what you can see is what we have done here we have the we are trying to do the connection so we use pawn con is used to do the connection we have given the username which is system and password i have given root it's connected and then what i'm doing is create user students i have created another user which is named as student and i want to give the password also how do you give the password you give it identified by a password so password also is the student for me and the username is also student first user is created right now once the user is created what i can do i can connect to student username and not system so what i can do here you can see con username student password i have given a student once i hit enter it says user student lags create session privilege logon denied so what is happening it is throwing error because system user has not provided any privilege to the new user student so from where i created this student user from system right so system has to give privilege to student so that this new student user can be created but we have not done that right so what you have to do we have to grant all privilege to student case here grant all privileges to student once i do that then what will happen you will be able to create a user and you'll be able to use that so we have to reconnect to the user system to grant privileges we just disconnected and we again created it you can see on system we created it and grant all will be registered student grant succeeded earlier it was not succeeded because we were out of the system user authority okay so users using system user to grant all privileges to a new user student and that is done now you can see what we have done so now we are trying to access con connections student user connection it's connected and you're creating a table stud with two of the things id and name id being a number and name being a varchar that means now if i go back to my oracle database the port number 8080 and if i try to log into student database i will be able to find this stud table let's see so when i go back i can find here in my student particular database you can see username student and i have the stud table here with two of the things id and name so this is how from system user we have grant privileges to student user and thus you have created the things or the tables so checking through database home page right okay now this was all about grant but what is revoke revoke is taking back so it's just like grant but it's the opposite so rework will take all the accessibility from your connection so now what we will do here is you can see i've connected to system and i'm revoking all privileges from student okay so revoke succeeded what i have done here basically is i have taken the privilege from student now it won't be able to create anything so you can see here now if i try to login with a student user student likes creates such a privilege logon denied so when trying to access new user student it throws an error because i have revoked all the privileges how to list users in the oracle database what all users you have created how can you see all those users see by default oracle also gives us the all the list of users right and what you have created will also be shown so to list all users that are visible to the current user that means if you are in system user then for system user which all users are visible to see that you have to use select star from all users okay and if you want to see it in an order ascending order descending order then you can use order by created okay now list all users in your oracle database no matter what user you are using your insist or system or student select staff from dba users will give you all the users which is getting created in the oracle database of yours oracle database again you can sort it by order by show the information of the current user you want to see only the information of the current user like which is system then you can write select star from user underscore users from these all commands now let me show you some of these screenshots which we have covered so you can see here select star from user users right so you can see user users as i told that it will show you the current system detail so we are in our system it is showing with the current system detail select star from user users when you'll be in student it will show you the student details correct so this is what is my dcl which is data control now let's move to the tcl which is transaction control and transaction control can only use with dml commands like insert delete and update what are this transaction control let's understand that it is used to control transactional processing okay now what is this transaction processing let's understand by understanding two of the keywords which are commit and roll back now understand when we were learning about dml commands we understood that insert delete and update these are all non auto commit things that means permanently these are not getting saved okay when you talk about ddl these are all permanently getting saved because these are auto committed commands dml are not auto committed and that is why tcl can only be applied to dml commands because here what happens is we can commit or roll back using the commands so we have a command to commit and roll back ddl we don't need it dml we need it so in dmil dml we use commit commit command is used to save all the transaction to the database so if suppose if i have this particular query which is delete from department where department name equal to marketing right so one row is deleted but i haven't i committed till now right now i have committed it committed means it's permanently done so statement is processed correct now if i do select start from department i won't be able to find that data okay the next rollback rollback command is used to undo transactions that have not already been saved to the database so we can actually roll back we can undo so let's see delete from department by department name equal to admin so you want to delete from the department table by department name is equal to admin so two rows are deleted right we are roll backing that means we are undoing it now you can see here that we have the admin details it's not deleted so it's rollback it's undo correct that is what is your pcl which is also nothing but commit and rollback so you can do this as well you can see here delete from department where department name equal to admin commit and then roll back now understand once you have committed you can't roll back if you see admin is not showing here again right now what is safe point commit roll back we have understood but what is safe point understand it in a very easy manner save points are like check points so in a marathon you have some check points right you reach one checkpoint and then you go for another checkpoint same thing happens here so we save we we designed the checkpoints that once this transaction is completed put a save point there put a checkpoint then move to the next some other queries and then put a checkpoint so if i have to actually see the table of student one i have some of these things id name gender is and pass out here id is one name is rhythmic gender is male age is 26 passive years 2015 correct like that we have some more data right now what we are doing here is update student 1 set age equal to 29 where name equal to with weight so this is updated and i have created a save point here that means one flag i have raised here okay that i can come back sometime later and i can roll back this save point that is upon me i can do that so update student1 again i'm doing that age 30 where name is equal to 1 again i have safe point at it right so i have again raised the flag here why i'm raising a flag because i am not sure whether the age is 30 of rohan or whether the age is 29 of earthquake it can be something else and i can come back and i can roll back these particular statements so those are safe points okay now you can see here that this has been changed right okay now you can see roll back to save point one now i am roll backing to save point sp1 so you can see i am rollbacking to sp1 that means sp2 will be removed sp1 will be kept so you can see rhythmic is 29 only but rohan 30 is now roll back to 28. if i would have written rollback complete rollback then it would have been changed age also right so those were all the sql commands which is ddl dcl dml and dcl i hope easily you have understood what are these things so now we're in our last topic which is database objects let's understand what are these and why do we use it in sql language or oracle database so first of all database object is any defined object in the database it is actually created to make our life easier when we are writing a query okay so it can be creating a temporary table or it can be creating a sequence of numbers it can be creating a particular index for one particular table can be anything such like that now it is used to store a different data anything which is make from create command is known as database objects so anything which you create using create command is known as database object it can be table also because while creating a table we use create command so it can be table it can be a view it can be sequence index or synonym now what are these let's understand table basic unit of storage we use it to store data it is composed of rows and column we have already seen a lot of times so table is one of the object view is logically represent subsets of data from one or more tables so let's say that you have two tables student and student department now from these both tables you want to take some data and you want to create a new temporary data for your own purpose okay so you want to take data some data from student you want to take some data from student department and you want to combine together and create a another kind of table which is called as view okay and give a separate name for that view but that data or that table is not permanent it's temporary sequences which generates primary key values like 1 2 3 4 5 6 7 8 or starting from 10 11 12 13 right that is sequence index it improves the performance of some queries we'll try to understand what is index and synonym is just an alternative name for all the objects so for table and alternative name will be there view for already name will be there for sequence alternative name that is what is synonym so let's start understanding how to create this table view synonyms index and sequence okay so table now we already understood how to create table and you can see here what they have done create table department department id number two department name market two departmental banker two and table is created and once they describe the department table they can see that it is created with all the necessary columns correct this is what is stable coming to view now i've already told you that it uses some of the permanent table data and creates a temporary table for you for your own purpose so create view impinfo imp info is one of the view which you have created for your own purpose what does this imp info contains it contains select imp id id number it contains the employee id fname and name concatenated salary into 12 which is the annual salary from which table from employee table where department id is 22. so this particular query will be solved let me write it again this particular query till here to till here will be solved whatever the output will come that output table will be the part of imp info so it's a view it's just a window from where you are actually watching the original table okay so just a temporary table for your own purpose it's not a permanent table okay it's just that from one table you are putting out some data and putting it inside a different temporary table for your own purpose so select star from info is giving me you can see here the info is nothing but will contain the data which is having department ids 22 and with their employee id name f name and annual salary these all things are coming here this is what is your imp info which is a view not a table and if you look here if i want to use this info i can use it select name annual salary from in pin 4 where annual salary is greater than 5 lakhs so i'm getting these all things now understand that this particular output is coming from this table and not the actual employee table right cool right it's very cool okay let's move to the next one which is sequence if you want to create a set of integers like one two three which is automatically generated and supported by many databases systems we can use sequence how do we do that create sequence sequence name start with initial value first value whether you want it to be 0 2 4 10 whatever increment by how much you want to implement by a two by one minimum value should be how much maximum value should be how much cycle no cycle means whether the sequence of integer will repeat or not repeat a very descriptive information about all those things sequence name name of the sequence initial value is starting value input value by how much it will increment minimum maximum value cycle means when sequence reaches its set maximum value it again repeats and no cycle means even if it reaches the maximum it will go on it will not repeat so here is one of the screenshots so you can see create sequence sequence one start with one increment by one minimum value zero maximum value hundred right so this particular value will start with one till hundred same way increment by minus one minimum value one maximum value hundred so this is nothing but to do it in descending order because i am doing minus one so starting from hundred it will go 99 98 and goes on okay coming to this particular portion now you can see how we can apply the sequence to my table you can see the name of my sequence is sequence underscore 1 and sequence underscore 2 this is what i have given the name to my sequences insert into department values when i'm inserting any value to my particular table i'll use sequence underscore 1 dot next val next val is something which gives the next value the sequence underscore 1 next val will give me the first data that means first sequence right so i'm getting one here sequence underscore one dot next value is giving me second if i would have used a sequence underscore two dot next val then it would have been start with 99.98 like this right so this is what it is your sequence let's move to the index indexes are a special lookup table that a database search engine can use to speed up data retrieval now what are these these are basically kind of a table which is used to actually make your task easier because search engine can find these indexes very soon single column index syntax multiple column index index and unique index syntax there is no change you can see here that if you want to have a single column index index will write only one column multiple column index syntax will write two column of three column and then unique index will have unique index the same syntax will be there and index helped to speed up select queries and where clauses but it slows down data input with the update and insert statements indexes can be created or dropped with no effect on the data okay create index dpt on department department id so i'm creating an index for the department id column of department table index is created create index department 1 and creating an index for department 1 on department which are columns for department id department name and department trainer i am creating a unique index now this is single this is multiple and this is your unique one right so i'm creating a unique index department too on department only for department name right so this is how you create an index and you can use this index whenever you want but when it should be avoided when the table is small when the table is small don't do that the columns are not often used as a condition don't do that it's a waste of time right you have already table then why do you want to create an index column is updated frequently so don't use index at that time how can you remove an index just write drop index index if you want to drop the view drop view view if you want to drop the table drop table table and you want to drop sequence drop sequence sequence this is how you can actually drop any of the objects whatever you create drop table we have already seen in the ddl statements correct confirming indexes syntax if you want to confirm whether we have that particular index or not select start from user index if you do this all the indexes whichever you have created the list will come up and show it to you select star from user synonyms whatever synonyms you have created whatever views you have created whatever tables you have created everything will be shown to you once you write these statements okay dropping index we have done select start from user indexes you can see department 2 is now not there so we have dropped the index which is department 2 right synonym what is synonym synonym is basically to create an alternative name for a database that means for table view sequence or procedure for whatever you want to create a different name you can create so create synonym branch for department okay that means for department table i want to rename it to branch i have created synonym i can use branch also to fetch the um details of department table in the same way view can also be have synonym create synonym basic info for input info was my view do you remember and we are changing it to basic info then for sequence remember we were having one sequence which was sequence underscore one and changing its name to sequence underscore number one and i'm using this and i'm still getting the values right next one for index create synonym department id for department this was my first index changing the name this was my second index changing the name right and thus you your synonyms are created you can see here select star from user index when you are doing it is not showing me the synonyms which i have created it is actually showing me the actual name why because synonyms are not doing the permanent change synonyms are just synonyms it's temporary for trial use or temporary use right so this was all about oracle database and this was all about sql i hope that we have covered all the topics around oracle and i hope that you have understood it very clearly and precisely so thank you so much for watching this particular video i hope you enjoyed it now before ending this particular video let's revise what all things we have understood so at first we understood something about introduction to sql introduction to dbms introduction to ledgers computers and then how the history of database came into picture then we did the oracle setup and installation we downloaded it i showed you how to work with it from where you will get which all things and then we started using creating tables inserting the data inserting the values inside a table and slowly slowly we moved into the part where i have shown you how you can basically work with miscellaneous topics which are nothing but your all different kind of topics like alias and the operators the operators as keywords and then we also learned about some of the sql commands like ddl dcl dml and tcl and later on we also understood how we can create views tables synonyms indexes and all those things not only that we have also taken care about joins and sub queries which are the most important topics when you talk about sql language or any database so thank you so much for watching the video i hope you really liked it and i hope that you have understood how oracle works and how sql works in oracle database thank you so much bye bye take care if you haven't subscribed to our channel yet i want to request you to hit the subscribe button and turn on the notifications bell so that you don't miss out on any new updates or video releases from great learning if you enjoy this video show us some love and like this video knowledge increases by sharing so make sure you share this video with your friends and colleagues make sure to comment on the video any queries or suggestions and i will respond to your comments you

Channel: Great Learning

Views: 63,138

Keywords: Oracle Tutorial, Oracle SQL Tutorial for Beginners, Oracle DBA, Oracle DBA tutorial, Oracle DBA Training, Oracle DBA tutorial for Beginners, oracle dba, how to become database administrator, databases, relational database tutorial, database management system, introduction to databases, database fundamentals, databases for beginners, oracle dba certification, oracle dba salary, oracle database tutorial, oracle database architecture, Oracle dba course

Id: ObbNGhcxXJA

Length: 208min 29sec (12509 seconds)

Published: Sat Aug 28 2021