Oracle SQL for Beginners | SQL Complete Tutorial for Beginners | SQL Full Course | SQL Tutorial

[Music] in our today's session we are going to learn about how to install oracle 21c with hr schema import so this is a single video to have all the setup so kindly follow and you can able to install it these are the steps we have to follow for the complete installation first step download the software which i have given in the description link and extract the software by using winrar urban zip third step install oracle 21c database once installed we can configure the sql developer and import hr schema i have given all the queries and commands in the description so just copy it and follow it so watch the session till the end you can able to install completely oracle 21c without any issues if you haven't subscribed our channel please subscribe the channel and click on bell icon so that you'll be getting all the notifications so without wasting your time let us begin our installation thank you welcome to the session oracle has different editions we have enterprise editions which is a commercial use and we have the standard edition this is also we will use in commercial environment and this express edition we will use it for academic version it's a lightweight version so all the features are available here also we can go ahead with this express edition so 21c is the latest version c is nothing but it's a cloud-based computing and cloud-based database so first we will download the software in the description go to description i have given the link download this 21c express edition and if after downloading you will be seeing these folders so first the oracle express edition 64-bit version is available it's 1.83 gb so you can right click and you can if you have wind rare in your mission so just unzip it so extract to the last option you can give it will get extracted okay so just we will extract the oracle software double click on this so if you see here inside you'll be seeing the setup file so you can double click on the setup file this is an application file you'll be getting yes or no click yes here and then it will start installing the software okay it will prepare to install so you'll be getting this particular wizard so oracle database 21c express edition so click on next here you have to accept the policy so click on i accept go to next click on next year so it will get installed here no need to change anything here just click on next so it will ask user name and password so if you look at here this password will be used for sys system and pdb admin okay these accounts so we have to enter the password i'm just entering the password as admin admin all in lowercase and confirm password also you can enter the same and click on next it will get installed so it will start installing it it will take several minutes okay if you look at here this may take several minutes maybe 15 to 20 minutes or even half an hour it will take so we will wait for that to complete then we'll continue so it will take some time it's based on your system speed and your system configuration it may take 20 to 30 minutes also okay after installation you will be seeing this particular page you can take the screenshot of this particular installation so if you check here it is a pluggable database you have the local host the host name is localhost port number will be 1521 and the connection string is xep db1 so this is the one we have to make sure that connection string okay after giving here just click on finish it will get finished so we have completed one step here we have just completed installation of the oracle express edition so once we have installed we can go to this sql developer so i have given link for the sql developer also you can you can download this sql developer you can right click so you can extract this sql developer so make sure that you have winrar in your system if it is windows it will extract very correctly so if you do not have indra you can download and install winrar so it has been unzipped now so you can see here so this is what the unzipped file double click go to sql developer so you can see this application file so sql developer is an application okay so what is this so i will tell you the setup so that you will get clear idea so if you ask oracle 21c this is the database okay this is the database you can see here so this is oracle 21c so we have oracle 21c here and express edition the edition is express edition so the database we can have multiple schemas in the database okay we can have the multiple schemas in the database for an example i'm going to split up this into multiple schemas so already system schema we have okay the admin schema system admin and then we we can import any other schema so if you want to create the user schema you can create it so logical division of our database so it's a logical division only so this is our system schema so system admin you can say it's a system underscore admin the user name will be system okay the password is nothing but which we are given admin which we have given while installing the software the username will be says our system the password will be the admin okay so this is what the username and password we can import any other schema so what we are going to do after configuring this system we for the practicing for in order to practice our sql we are going to import the sketchup schema so in our previous versions we will have by default we will have a hr schema but in our 21c we do not have hr schema by default we have to import it i will tell you how to import it so that you can follow the same steps you can import it it just schema tables and you can create any other schema the right hand side so you can create a test schema development schema or any schema okay our core schema so whatever you want to create you can create it okay this is our own schema so this also we will import it so this is the database right then what is sql developer so sql developer is an application it's a gui graphical user interface by using this sql developer it's a front-end tool if i want to query this database then i have to use the tool called sql developer okay so this is the tool the front end we have the tool called sql developer you heard about this stored for oracle right so anything you can use it so we have the tool for oracle we have the sql developer for oracle so here we are the tool for oracle so whatever the front-end tool you are using the back end we have the database okay we can use any front-end tool not an issue but the underlying database only one database okay we have the system admin the user and we can connect to hr schema we can connect to div schema any schema if you want to connect it you can connect and then you can query what data is available in the database okay so oracle is a database to store the data and sql developer is an application it's an application to see what data is available in the database and sql is nothing but is a language to query this database okay so let us open this sql developer and then from sql developer we will connect this underlying database and then we will import the hr schema okay very first time it may ask yes or no you know here and then this is what you can see here okay so oracle database express edition so this is the database connection so click on this plus symbol so what is this plus symbol right we are creating the connection from sql developer to underlying database which we have already installed so this is what so what is the name name you can give any name so i am just giving system okay system or system admin anything you can give the username will be system okay the username will be system the password which we have given while installing we have given us admin you can give admin host name will be localhost port number will be 1521 and service name you can choose what is the service name we have chosen right so here you can you could see the service name is x e p d b one so x e p d b one so th this you have to give if you're giving only xc you'll get error so just click on test so from this sql developer the underlying database it will ping okay from sql developer it will start bringing this underlying database it will establish the connection okay this connection will be established and you can see here this has been success okay this has been success now then click on connect so if you want to save the password you can click the save password and you can save it so click on connect it will be connected so here in the right hand side it will open workspace so this is what the workspace okay this is what the workspace you can see so what are the different so you can query here select start from all underscore tables you can query so what different tables are available what different users are available you may not have any users like hr users okay lot of tables should be available this is admin schema you are we are calling it this is admin schema we are carrying it so you can see all underscore table all underscore users so that you can check it here just ctrl enter it will be executed you can see so we do not have any hr schema here so we can make ascending order so you can see there is no it just schema right so there is no hschema so for what we have to do we have installed oracle 21c this is what we have to install it so here itself we can create a tables but it is not advisable to create the tables here in the admin schema okay we will import the hr schema tables what is the hr schema tables we do have seven tables predefined tables we are going to import it for an example we have the tables called we are the tables called employees so we have the departments table we have the locations table like this we have seven tables so how to import it so we have the dump here i have given the hr schema so you can extract okay you can extract it so it'll be get extracted so this is what you will be having the human resource schema so copy this particular folder so copy this particular folder go to c drive so you'll be seeing the folder called app so go to this app so you will be seeing some different name here so based on your computer name you will be seeing this folder so go to this folder product 21c okay so here you can find db home xe okay db home xe then you can find the demo folder so you'll be seeing the schema so here you have to place here you have to place this hr schema okay so whatever the different schema we have so that we have so this instead of lenovo so you might be having different path in your system so that path you have to make sure that okay what is the path we are giving place the unzipped hr dump the below path so we have placed the file right so the same path okay the same path i have placed then execute the below steps in admin schema okay we have to execute the pillow steps in admin schema in sql developer okay so in sql developer i will give the steps you can just follow the steps so you'll be seeing this this particular path okay so in your system this path might be different okay this path might be different so go to this one so you can see so you have to make sure that you are in system underscore admin schema you have to use this art symbol one space we have to give so give one space at symbol and then where is this hr.main.sql is present so that particular path we have to give if you go to this particular folder human resources inside you are saying hr main dot sql hr underscore main.sql it is nothing but it will have some sql statements okay sql statements it will be there if you look at here so whenever we are going to execute this one rem is nothing but it will not execute it's like a commented one okay so this is what it will clear it will create all the permissions all the users and everything it will call this particular code so what we are going to do now we are going to execute this particular line in sql developer itself in admin schema make sure that this particular path you have to mention your system path okay just i am going to execute this particular line we will see now it will ask enter value for one so i have given so what is the value we have to give here so if you look at here enter the parameter one is hr so hr we have to give and we enter the value for two which is nothing but specify password for hr that's the parameter we have given specify default table space for hr so deep default table space we can give users so that's what users we can give so just you can give users at the space the third one temporary table space okay temporary table space you can give temp temp temp then fourth one specify password for system okay system what is the password we have given we are given the password assertment so just to give the password as admin admin admin then it will ask for the log path so what is the log path we have to give so you can give this path oracle home it will automatically take that particular home path it's a parameterized one you can give the same path then connection string okay connection string as parameter six so what is the connection string here i have given so this is the one right localhost you remember we have copied from here localhost colon1521 slash xcp db1 so this is what we have given so just we will take this ctrl c ctrl v make sure that you are giving correctly click on ok it will alter the session and it will do all the things okay you can see this one so you might be seeing something like error but not an issue it's showing like hr user does not exist after that it has created okay user has been created user has been altered so you can see all the permission has been given and this is what you can see this execution so we have done with main hr main we will connect it so we will see whether this particular user is available go to new connection new connection you can give the connection name is hr username we have given hr and password is hr okay so localhost what is the service name xepdb1 so this is what we have to give click test you can see the success so now we have the hr schema so we it will connect it see here two schemas we have one is system admin another one is hr schema okay in hr schema if you see left hand side so you you might be seeing all the different objects right different objects you will be seeing that tables is one of the object views and indexes packages procedures functions triggers all this you are seeing it so if you click on the tables you are not seeing any tables as of now so we do not have just we have created a chart user that's all what we have to do now we are to follow the remaining steps execute the below steps in hr schema make sure that you are running this particular hr schema and also this particular path you have to make sure so which path you are giving okay so this is very important if you change this path if you are just giving this lenovo then you may get error in your system check what is the path so replace that particular path and then you can execute one by one so if you run this particular hr schema so we are not able to see the tables as of now just we have created the schema then we have to execute the blue steps we have to execute these statements in hr schema so you have to give correctly in order one by one we out execute so go here and so just simply you can make use of this particular line so you'll be seeing this table has been created and views has been created populate the table with records so record is inserted in the table and create the indexes so different codes will be there just execute one by one so execute all these particular steps so you are seeing this commit complete then you are saying finally you can execute this one okay after that you can you can clear this one so after that if you execute this select start from employees you can see the tables so you can see the sample tables the left hand side you can right click on the table you can refresh it so you can see the seven tables right so you can see the seven tables so by using this you can practice so whatever we want you can create a table you can do even if you want to create any other schema you can create it in this admin schema so i have given all the steps and notes on the dump everything in the descriptions downloaded all the softwares i have given as a winrar file so download it and install in your extract it and install your mission and then start practicing it so if you find any difficulties in the installation please post your queries in the comments i will respond to it thank you [Music] sometimes after installing oracle 21c express edition or 18c express edition or 12c express edition so we are trying to connect this system admin by using system user and password is whatever the password where we have given while installing just i have given us admin and we are making that host name is localhost port number is one five two one and service name is xcp db1 so this is the service name you can see here pluggable database 21c localhost 1521 xcpdb1 so the same connection details here we are making but we may get this particular error io error the network adapter could not establish the connection so this particular error we make it if you get this particular error go to c drive go to c drive so you can go to this folder app your system name product 21c or 18cr12c then you can go to this particular path homes you can find this particular folder or a db21 you can go to this path in this path you can go to network here admin so this is the path then go to if you check this listener check this listener open this listener you can see this in the listener the host name mentioned like this ip address the host name mentioned like this ip address in your system you can check it so what is the listener we are having it so copy this particular host address then paste it here then try to connect it so you'll be getting success so this is the host name we have to make and then you can connect and you can connect with any other schema here you can connect with hr schema so i have already posted video how to import hr schema just to follow it you can able to rectify this particular error thank you for watching i have posted already oracle sql sessions please watch it and practice it if you have any other questions post your questions in the comments i will answer it thank you hello friends welcome to nikeite academy in this series we are going to learn oracle from basic to advanced level including interview questions and answers in our today's session we are going to start with so what is oracle so how the oracle will be used what are the different schemas are there how to install the software and how to query this database everything we are going to learn this is the agenda for today's session introduction to sql and what is sql what is rdbms what is oracle database what are the different databases are there what is schema what is sql developer what is stored and everything how to create a sample table so that we are going to learn in our series next next sessions we are going to learn all this rest of the topics one by one please watch the session till the end i have given notes and exercises for each and every sessions please practice the session if you have any questions or any suggestions please add a comment so that i can respond if you haven't subscribed my channel please subscribe the channel and click on the bell icon so that whenever i am going to upload a new video you will get notified let us start our day one session happy learning so database is nothing but it is used to store the data we have difference between database and sql sometimes if you ask which database you have experience most of them will tell us i'm working on sql database see sql is the language sql is a language database you might be working on any one of the database say for an example we do have a lot of databases are there if you consider so this is the database this database used to store the data if i want to query this database so what data is available you need to know the language called sql sql is the language to query this database this database we do have multiple databases number market one of the leading databases the oracle database we have the provider is oracle oracle database and then microsoft sql server sql server is another database and then from ibm we have db2 and lot of databases are there from teradata we have data data here and netizer we have ibm netizer and lot of other databases are there s like and then you have a database called mysql postgresql like this we have a lot of databases mysql and postgresql both are like open source and other than that we have a lot of databases are there in the market okay these are on the database provider if you are going for any database for your company right you can you can go for any one of the database or multiple databases based on your company needs in our real time project we'll be using these databases it's based on the company either they will use one or two databases or more databases it's based on the company's need in order to interact with these databases i need to know the language called sql so sql is the language structured query language so that language we need to know oracle sql is different sql server sql is different teradata sql is different this mysql sql is different okay it's based on the database it will differ little bit not completely like 90 percentage of the queries are same only 10 percentage of the keywords will be different okay this is the database this is a server actually on top of the server we will be having a front-end tool so here we will be using some gui tool as our client tool so we can say it's a client tool this is a database one place it will be there the server will be there in our mission if we are the developer we will be using any of the client tool okay here we will be using the client tool in our mission the client tooling will be installed and we'll be using the gui tool gui is nothing but your graphical user interface by using that you will be querying this database by using the sql language these databases if it is oracle database if you assume that this is the oracle database we are the latest version of oracle is 21c if this is the database and here we can use the gui tool called sql developer see sql developer is the front-end tool front-end tool you can use sql developer you can use stored code for oracle anything you can use it okay so don't for article any tool you can use it sql plus any tool you can use it so these tools is the front-end tool is the front-end tool by using this tool you can query this database this sql developer toad will not have any data it will not store any data whatever the data which is available in this database we can pull the data and then we can see it here and you can write the join query sub queries multiple queries we can write okay if it is oracle then you say sql developer untold if it is a sql server then you will be having sql server management studio okay if you put stereo data you'll be having sql assistant so like this different different client tool will be there in the real-time projects okay that's what a client tool will be having the graphical user interface tool so this is the database we have we do have different databases right so you can ask me if all the databases queries are different so should they learn all the different database queries each and every time no so learn any one of the sql variable like either oracle sql or sql server sql or paradig sql the other sql you can learn within just one week or 10 days okay it's very simple only okay now if you consider the oracle database we do have multiple database versions are available if you consider the databases multiple versions are available the latest version as of now is 21c before this 21c we have 19c and we have article 18c see these are all the versions okay before that we have oracle 12c and article 11g and they can have the oracle 10g 9i so like this you'll be having lot of questions okay so the latest version is you have 21 c by learning you can use any version no issues by learning sql you can use any versions like either you can use 19c or 21c or 12c or 11g also it's fine since we are using informatica this leveragey we can use it no issues for the sql purpose you can still we can use for this for our querying the database okay these are all the the different oracle versions are available okay version and also some editions are there some additions what is the additions here see different editions are there in the article so one is enterprise edition the enterprise edition is cost okay enterprise edition is cost cost wise uh it's like a like commercial one then we have the express edition we are the express edition express edition is academic purpose that's what we'll be having the xe we'll be having the xc right the oracle 11g is coming with xc okay enterprise edition you have one more edition called standard edition these are all the uh editions different editions are there in our real time projects so in real time projects we'll be using enterprise edition only express edition will not be used in real time so here we are using enterprise express edition why because it's a non-commercial academic version okay here that is what we are using oracle 11g express edition you can go for standard edition also no issues the express edition will be very lightweight in the express edition they are giving commonly two different schemas first of all if you ask what is schema the databases are you can divide this database into multiple subset you can divide the database into multiple subset so why we are dividing for the easy access and privileges usages and everything we are splitting the database into multiple schemas we will call it as in oracle we will call it as a schema in a sql server or teradata we will call it as it's a database so in the data database we have three different databases in oracle database the other three different schema schema is nothing but if you look at schema so you may ask a schema you'll be hearing like a lot of things like a schema right schema is nothing but logical division of your database okay in express edition say for an example if you are using express edition in express edition we'll be having the schema called admin schema admin schema on schema will be there that's what we'll be having the admin schema so if you haven't installed the software please install the oracle any versions no issues 11g also it's fine any virtually you can install it if you are going for any informatica then go for article 11g it is compatible with oracle 11g only so no issues on the sql queries sql queries are same okay mostly on the versions so handling the server handling more users it will be different okay admin schema the user name is you can go for username is system r sys these are the username okay the password is whatever the password while installing you have given so that is the password so you can have this username is system password is admin okay so this is the username and password so this is what the username and this is what the password okay so now they are giving one another schema called hr schema they are giving one schema called hr schema to practice ourselves to practice ourselves they are giving schema called hr so for this hr username is hr password also hr so initially they get charge schema will be unlocked state you have to unlock it so that i will i have explained in our installation of our oracle so you can watch that video you can able to install it and you can create any number of schema any number of schema you can create for example i want to create one schema for testing purpose you can create a schema called test schema okay here you can create schemas called test schema that you have to create by using the admin schema so username username is you can have any name just you can create usernames test and password is also test and this is called schema inside the schema you'll be having lot of different objects see our object in the sense the table is one of the object okay so table is one of the object will be having views is another object okay indexes is another object like this you'll be having a lot of objects are there like tables views indexes synonym sequences lot of objects are there so these are all the object we will call it as an object okay if you consider table right so we do have tables in our schema like employees is one table okay already pre-defined table departments is one more table department stable like this locations see a lot of tables will be there whenever it just came out like this so you can use any any tables for our purpose you can practice it so test schema you can create it see you remember we have created the table called t underscore employees table for our informatica purpose you can create any table here so this is what the here instead of test you can create you can name it like a course schema work schema any schema you can you can have it this is for the setup of our database so how many schemas are there here three schemas other admin schema in real time dba only will have the access dba means database administrator only dba will have access to this particular schema they will be providing us which schema you want to have access okay it's a logical division say for example you have one tv of hard disk you are splitting the what type of hard disk into multiple scheme uh drives right c drive e drive f diagonal the same way in real time project for the easy access and privileges and security purpose we are splitting the database into multiple schemas okay this is what the schema will be there in cloud and all it will be stored in different different instances different different servers okay this is what the keema and database setup so if you haven't installed oracle software please install the software this database first and then install this sql developer from sql developer you can query this so this is what sql developer if you look at here how many schemas are there here five schemas are there so this is one schema this is another schema your oracle is running see our oracle is running here this is the oracle database it's running here okay you can see here article 11g express edition since i have used informatica i have installed oracle 11g if you are going to have only oracle then you can go for any version of oracle no issues okay see if you look if from sql developer this is a sql developer right i want to connect the underlying database from sql developer i want to connect the underlying database underlying schema called hr schema how to connect it so you have to make the connection from here you have to make the connection so that's what one time you have to make so here you can go for this is the connection you can make hr schema this is after connection you will be having it so simply you are making the connection right so if you click on the plus symbol the connection dialog box will be opened so if you look at here this is a new connection right so you can create the connection name and provide your username watford in real time the host name port number service name or sid they will be providing you who will provide the database administrator will provide us by collecting this information we have to connect it okay so since we have connected just we have so if you look at here you can go and worry this database table like we are the one database table called employees it's already there okay we can see here the tables are there it's a predefined table oracle they are providing us for the practicing purpose okay here we are the table and then by using this query i have to create some tables load the data and everything i have to create one table called customer you assume that i have to create i have to create one tablecloth customer in order to load our customer data into the database okay if you look at this table the table name is the table name is customer and these are all the column names i want to store my customer data in the database then i have to create a table i want to store my employees data then i have to create one table i have to store my product information location information so each and every data we have to create a table okay in order to create a table we can use create table but before that we should know what is data type right without data type we cannot able to create a table okay so this will have only unique numbers so each and every customers we will assign a unique numbers this is what the customer id then customer name it is a character right mobile number it's a number date of birth it's a date so here it's a number again in email id you may have a special character's number and everything okay that is what we have to go with the basic data types see what all the basic data types are there in in oracle the data types we can split these data types into these data types one is numeric data type the numeric data type okay we have different numeric data type i will provide you one sheet you can go and watch that sheet but as of now you can have the number data trick see number data type is it will hold all the poll number decimal number and everything okay number data what is this number data type low right so number so you can mention simply number or you can mention the number of characters okay you can have number of characters if it is number of eight you can go up to eight by the eight numbers you can go for eight characters you can go for up to this number you can store in the particular column okay if i'm going to use number of 8 comma 2 8 comma 2 this is decimal number 8 comma 2 means you can have totally eight digit out of eight digit two digits are decimal this is what number of eight comma two totally eight after this two digits are decimal okay you can have this number of two comma two up to which number it will be stored totally two digit two digits are decimal so zero point nine nine into store why because totally two all the two are decimal we might be thinking okay so it will store up to 99.99 no it will not store you can test it so this is the number data type other data types are there in teacher float and all just you can explore that okay then next one is called character data type or string data type you can go for character you can have the character you can have the care it's a predefined length then we are the varca okay where care and we have the varchar2 okay so where can i do in our recent version the varchar they have deprecated the oracle they have deprecated the curve so even if you are mentioning varchar data type it will not take a smart car it will it [Music] then what is the difference between care and vaca so that i have posted one video you can watch it simply see nowadays we are using varchar2 only okay welcome to only for an example 2 you can go up to 4 000 bytes okay maximum you can go for 4000 bytes if you have more bytes then you can go to the large object okay then we are the three third data type is called date date the other date data type see date simply convince mention like date so what is the default format of date so we are the default format so this is the default format in oracle so this is y format only 0 9 merge this one so this is a default format other format we can define so these are all the main data types we will use in our article so we do have other data types i will explain this in later okay as of now we will take it these are all the data types okay we do have other data types like a boolean and we have that's boolean it's a pls sql data type and we have the other data types like lob large object character large charger binary large object so that i will explain later point of time these are the three main data types we use for numeric we'll use number for character we will use bearcat 2 for date we will use date okay these are all the data type you will use for an example i want to create this particular table right this particular table then i can go and create like this how to create this table right in our go to sql developer as i told if you haven't installed sql developer please install it first the oracle and sql developer in order to create this table right in order to create this table i have to use create table the syntax okay create table syntax i have given in the article day one you can you can watch it so here i have given all the nodes you can just you can watch it here create table so you can see here create table table name and all this is the syntax of creating the table you can create a table by using this syntax okay how to create it's very simple create table table name okay customer open bracket make it this way open bracket then you can go for the column names what are the column names customer id customer name so you can go for customer id it's a number data type okay it is a number data type so you can mention that number of eight you can go for eight digit update digit then customer name cust name this is varchar 2 worker 2 as of now if you are using varchar also it will take worker 2 only you can go for 100 characters okay radka 2 then what is the column mobile number so as of now this mobile number will take only number so that's why i'm taking number of 10 i'm taking okay next one date of birth so bob it's a date simply convention date and city and email id so you can mention that city it's again it's a varchar2 where cat2 of 100 can go for up to 4000 characters and email id you can go for again where to go for 100 characters you can close it see whenever you are opening it you have to close and why we are making this semicolon right we are telling oracle that okay this statement has been ended so you can use like this and then you can execute it in order to execute you can select and then click on this green color icon or you can select and control enter or you can keep the cursor here inside inside anywhere the statement you can keep the cursor anywhere in the statement ctrl enter this particular statement will get executed this is what that table has been created you can just go through this table select star from the table name table name called customer nothing will be there just we have created six columns are there nothing will be there okay you can describe the table you can make describe the table always make the semicolon ctrl enter that particular line will be executed you can see here describing it name of the column and then whether it is a nullable column null means nothing it's empty okay and then data type so whatever the data type we have so this is what we'll go for the data types okay hello friends welcome to nikkei academy in our previous session we have just seen the introduction about oracle sql and just we have created the table and in our today's session we are going to learn about all the ddl dml dr tcl and dcl please watch the session without scripting it i have given the notes in the description please download and practice it if you have any suggestions or any comments please add the comments the channel if you haven't subscribed my channel or if your first time you are coming to the channel please subscribe the channel for more updates let us continue our session [Music] the sql has been classified into five different languages one is ddm if you look at here oracle the sql so this is what sql that's all all the sqls are coming to this five different language only okay you can remember this if you know this sql is very easy oracle sql is very easy first one is data definition language it always deals with the structure of the table we will call it as a ddl okay ddl structure of the table so in order to create the table in order to alter means after creating it if i want to add a column or drop a column or renaming the column name or something you can go for alter okay you have to change the data length or data type then you can go for the alter you can use modify then renaming the table or renaming a column truncate truncate means you are erasing all the data from the table the table data will be there but just you are erasing all the data see here i will tell you what is truncate tunnel i hope this is you are clear creating means you are creating the table say for an example as of now i have created the table with six columns you assume that six columns are five columns i just created after loading data sometimes later we want to add one more column you can add it here on the right hand side you want to add one more column you can add it this is called altering the structure or you can remove any of the column this particular column you can remove it like excel sheet right this is called altering the structure that's what we will have alter table alter okay rename renaming a column renaming a table okay truncate what is the truncate so i will explain this very clearly so that you can understand but just the one word you can have i have the table it has some data truncate means the table structure will be maintained inside that all the data will get deleted okay we are just erasing it remember we are erasing it and drop means we are just dropping this table dropping means we are removing this table okay just we are removing this table completely we are we are erasing this table okay remember this so what is truncate and drop so truncate means if i have the table data will get truncated but the data will get truncated it will keep the structure of the table this is called truncate drop means that data structure and everything will get dropped that's all it will erase this table that's all okay nothing will be there simply it will erase all the data as well as structure no table will be there that is what drop see it will deals with structure of the table see whenever you are executing any data definition language it will be automatically committed committed means it will be saved the database okay it will be saved in the database it will auto commit i will tell you what is this dml operations dml is nothing but it's a data manipulation language we have four different languages insert update delete and merge please run the force for different languages we have insert means inserting the data into the table updating the record okay we have already record is present we are just updating it so if you look at here i'm just inserting one data one more data that is called inserting it this phone number i'm updating this is called updating okay this is called updating this mobile number i'm changing it like this then this is called renaming a column not updating update means data should be updated data should be updated insert means new new record will get inserted delete means we are deleting this particular record okay delete means we are deleting the record okay then what is the difference between delete and truncate see here truncate means see even if you have millions of data it will truncate all the data it will erase all the data okay we cannot mention okay partially you delete this but delete you can partially mention that okay you are you are having city okay whoever is belongs to city mumbai you delete it we can tell it will delete only the customer from mumbai our customer from pune customer from hyderabad you can define it but truncate we cannot define it and also if you have if you have done some mistake okay you can roll back here the dml operation you can roll back unless otherwise you are committing it you can roll back it that is what user commit so we have to commit it unless it will not be stored permanently in the database that's what user commit merging then select operation drl you know right select selecting the data from the table it will select only it will not modify any data transaction control fourth one is transaction control it's a tcl we have three different languages called commit rollback save point i will tell you what it is committing means permanently we are saving the data in the database rollback means we can roll back up to last commit point save point means the bookmark we can make some bookmark and we can roll back up to that mark or commit up to the bookmark grant and revoke this one dcl data control language will be maintained by the dbs we are not going to have as a developer we are not going to have grant and revoke see these are all the different languages are available sql okay first we have seen create table just we have created this statement we just created the table called customer just we have created and we have to insert the data into the table this is what the syntax of the insert statement we can have this insert statement you can see here insert into table name all the column names the corresponding value you have to mention here values so what is the value here you can mention the customer id customer name and the mobile number mobile number and we have to mention the date column like this only date column like this one two underscore date off see we have to mention single quotes the date format and we have to mention the date format which is a parameter it's a mmddy why we are mentioning right so we are telling oracle that okay we are mentioning the format like this it's a yeah it's a fourth august or 20k okay chennai and then this is the email editor see all the character columns should be there in single quotes single quotes all the wear columns should be there in single quotes so each and every column should be separated by the comma so you can have this and then you can insert the data into the table if you look at here we are going to insert just enter that see we are getting some error invalid identifier if you are getting invalid identifier that means the column name is wrong okay that's what the invalid identifier see here we have mentioned mobile but here we have mentioned like mobile number that's a reason so you can remove this and then try to insert it so we are getting one row got inserted okay one row got inserted then if you go and check this table you can have this data the one row got inserted here so you'll be having this so i have executed it it is available but if you go and check here select start on the table it is available but after that i'm going to roll back it remember i'm going to apply rollback if i play rollback so that one rate data whatever we have seen it will not be there but the table structure will be maintained why create table is the ddl statement so ddl statement auto committed that's what the table is available in the sql server or teradata role there is no concept of rollback see whenever you are going to insert it all the data will get inserted automatically it will be committed okay there is no concept of role backing or not so we have inserted the data so you can insert like this many data so if i go here and go for see one more record you can go for one more record so here i haven't mentioned the column name if you haven't mentioned the column name you can just mention insert into table name directly values you can mention the values here okay this is what just you can mention the values you can just insert it it will get inserted one row got inserted either you can go this way also then you can query this and one data is available you can query you can insert this also second record it will get inserted okay i have inserted two records then i can go and commit it just to commit commit your two executed executed then if you are seeing it two datas are there after committing it if you are executing rollback it will not be rolled back it will say rollback complete it will not be rolled back okay you can see here this data will be there that's what you can understand what is rollback and commit okay ddl statements are automatically committed but dml statement like insert update delete we have to commit it so this is what we will go for the commit and roll back and we can we can have other columns also say for example i want to insert one more record i'm trying to insert but here i may get error not enough values why why because in the table we have six columns but i'm mentioning only five columns so since i do not have mobile number for this customer as of now i am not i do not want to insert it what i have to do either the mobile number column you can mention like this null okay null null after this one i can mention like null r i can go for only that particular column alone i can mention here see what is the column so like this i have to mention by removing this mobile number column see look at your corresponding customer id customer id customer name name data birth i have data but ct cp and then email id see all the columns so what what the data will be inserted for this mobile number for this particular customer will get inserted with null null okay null values i want to update this value after some time this is called update okay you can go for update see you can go for update here so i'm mentioning like update okay here after inserting and i'm just committing it commits it'll get committed then i'm going for update see update after committing it if you look at here so three records will be there okay what is the data for this particular mobile number it's null you can go for update statement i can mention this mobile i'm going to update it showing like three rows updated three rows got updated why three rows got updated since i haven't mentioned the wire class it has updated all the three records with this mobile number actually it is wrong right can i roll back to the previous state yes i can roll back why because its update is the dml statement you can roll back it will be rolled back up to last commit point you can see here it will be rolled back up to last commit point so this is what we can see here mobile number now you can update save so what update to identify this record i will use the customer id this side so i can use where customer id equal to this id customer underscore id should i make single quotes for this no need to have it's a numeric column only to mention the single quotes select and then one row got updated if you go and check here table one row got updated see this is what mobile number got updated after update what you have to do you have to commit you can ask me each and every insert and update i have to commit a rollback no finally you can do it no issues okay finally wherever you want it's based on the scenario you can do it that is what update okay what is the adding a column so i want to add a column called the country see after email id i have to make one column called the country see whenever you are going for in oracle if you make two iphone right that particular line will be commented on so add a oracle will not execute this particular line if you are making two iphone it's a commented out alter table table name add column column this is the alt statement i have given all the syntax you can have this whenever you are adding a column a new column will be added at the last only you can execute table customer has been altered should be available here okay then you can go for select star from the customer table one more column will be added see since you have executed the ddl statement here right up to here it will be committed okay it will be committed up to here you haven't committed here but if you have executed ddl statement up to here it will commit okay remember this this is also very important then you can see one extra column is there can i update all the customers from india yes you can update just you can go for update this one you can go for update customer set country equal to india and it will be updated you can see here it will get updated here okay india i'm thinking that i do not want this particular column so can i drop this column yes i can drop by using this drop a column see here alter table table name drop column column name remember this drop column column name you can drop this one column will get dropped only one column you can see here this one column cut route okay you can ask me see here i have updated i have wrongly updated can i roll back can i do roll back now whether it will get rolled back no why why because we have executed alter statement since we have altered it will not be rolled back it also likely complete but it will not be rolled back same data will be available after update since we have executed the ddl statement it will not be rolled back so this is what the adding a column or dropping a column and then you can insert the number of values on so this one this is what you have to practice it if you look at this table customer table you can see here we have loaded three records three records and some six columns right customer id customer name mobile data birth email id and country if you describe the table if you describe the table customer it will show you the columns here customer id customer name mobile data birth email country see all the columns will be displayed here right okay so now we can go for see now the thing is see we have we have seen how to alter how to add a column how to drop a column everything we have seen now if i am going to insert this particular record in the table if i am going to insert this particular record customer id customer name okay mobile data about the individual i'm going to insert it i'm going to insert see here we have i'm go so here i have used eight digit but here i'm using nine digit okay here also i will use plus nine one iphone plus nine one iphone i will use it okay plus nine one iphone i will use it if i am going to insert this particular data it will not get inserted you are saying that value larger than specified precision allowed for this column so this is what you'll be getting the error so what is this error right so this error due to this column and this column okay this column this is data type itself is changing okay so in order to rectify this one i will check i will describe the table i will check it what is the column length and data type then i can go for reduce the number of columns it's a eight digit right eight digit i can go for some other digit like this i can try to insert it now also it will not get inserted we're showing like invalid number so what is this invalid number right since if you look at here the mobile column should be a number of 10 but here i am giving away column say plus is nothing but it's a character column hyphen is nothing but it's a character column that's why it is not taking invalid number means it is expecting a number but we are giving a character column that's why we are getting it the thing is our customers say uh already mentioned that we have to go with this type of column instead of only numeric column and also you have to go with more columns how can i do it see already we have the table like this can i change the data type for example from customer id can i change this into number of in i have to change it yes it is possible you can change it and also this mobile can be possible to change from number of 10 to wire card to a 15 worker 2015 yes it is possible but we have to do some workaround what is this see remember if the table contains data if the table contains data you can increase the column length but you cannot decrease the column length remember this if the table contains data you can you can increase the column length look at here i'm going to increase the column length from uh from customer id number of 8 to number of 10 i am going to increase it whether it will get increased yes it will get increased it will say like customer table has been altered if you go and describe the table you can see here the column has been increased from number of eight to previously number of eight right now it has been increased to a number of ten but here even after that after changing it can i change to number of nine it is not possible if the table contains data it is not possible okay if the table contains data it is not possible look at here it is not possible see you will be getting error like column to be modified must be empty to decrease appreciation or scale if i want to decrease the precision or scale you are to the column should be empty that is what and also you cannot change the data type say for an example the mobile column i want to change the data type from number to varchar2 is it possible if you look at here this one i'm going to take so mobile alter table table name modify column the new length our data type okay if you are mentioning like if i'm going to execute it it will not be executed it will not be created if you look at here error you are getting it what is the error column to be modified must be empty to change the data type to change the data type that particular column should be empty in real time project we do have the data see customer id we have some customer id okay six digit cuts customer id customer name is some name is there okay mobile number some mobile number is there okay data but we are having some data but i can take some uh data but here if i have the data like this i want to change the data type of this one from number to where cap if the table contains data you cannot change it you have to delete it showing like the you have to have empty column that means it should have a null value if you are deleting like this right in production how will you again how will you make this mobile number for each and every customer you'll be having millions of record right you cannot do it so that's why we will do one workaround first of all we will take the backup of this table backup of this table how to take the backup you can make customer underscore backup we will take one backup of this table then truncate this table second option you can truncate disable since we have taken the backup we can truncate the table there is no data now you can modify it it will get modified after that from backup to main table you can load it validate the data and then backup table you can drop it clear see these things you have to make it suggest you take the backup of table you take the backup table then truncate the waste table truncate means erasing all the data from the base table modify the data type now okay modify the data type or decrease the length restore the data from backup table and drop the backup table after data validation so this is what we will follow okay one scenario we have taken for truncate modify and everything this is a scenario i will do it now okay how to do a backup of table two ways you can do one is if you look at here i will create a backup table create table table name customer underscore backup as select star from customer table customer table it will create a backup table it will create a backup table so select start from customer this is these are all the scenarios we will use in our informatica okay these scenarios and all you'll use to take the backup of existing table if you look at select star from it is selecting the table so same kind of select we have to create one table and just executing it customer backup table has been created if you are using where one equal to two what is the meaning right where only equal to two see if you look at here customer backup select start from backup it will create same like the main table same like main table it will create like this all the data has been loaded but if you are going for customer one backup one has been created but if you look at this table it will not load with any data it will create only structure if you're going like this statement it will create only structure of the table so this is the way we will create a backup table okay first we have created a backup table since we have validated data you can truncate the table the main table you can rank it don't truncate the backup table truncate the main table truncate table table name customer truncated truncate means it will delete all the data from the table select start from customer it will truncate all the data from the table okay you are thinking that i have wrongly truncated can i roll back you cannot roll back why why because it's a ddl statement if it is ddl statement you cannot roll back it if you truncate that's all truncated that's all okay that's what we have truncated now can i modify yes i can modify it look at here i'm going to modify it i can modify it has been altered now okay alter now you can you can even you can decrease the length look at here previously we have taken like number of 10. now yeah now i have made it like a number of nine then if you are describing it it will be described like this number of nine like this will be described you can see mobile will be vacat 15 after that we have to restore the data right if you look at the customer table it doesn't have any data we have to restore the data w restore insert into customer bracket select star from customer underscore bkp table okay we want to select it see whatever the select we are using that should be loaded into this table remember whenever we are using star here the all the column structure should be same with customer and customer backup should not have any extra color three rows got inserted after insert you have to do comment so why because after insert we have to do commit okay we have committed now if you go and check here select start from customer table the data has been restored right the other data now we can load with the plus nine one unknown if you go here you can previously we are getting this another invalid number but now if you go and insert it it will get inserted one row got inserted right inserted you can see go and check here select star from the table it will get inserted plus nine one iphone you can go up to 15 digit and also you can go up to nine digit here this is what we have to modify it length okay modify the length okay we have seen these three under drop the backup table if you do not want the backup table you can drop it select star from backup table you have the backup table called customer underscore pkb you can see here the backup table is this is what the backup table if you do not want this backup table you can drop it but before dropping it say for an example i want to delete this particular record from this table can i use delete statement yes you can use delete delete from just i want to tell you how the delete will work delete from table name delete from customer backup if you are deleting it all the record will will get deleted three rows will get deleted then what is the difference between delete and truncate truncate also is truncating all the data delete also truncating all the data but truncate we cannot roll back if you look at here after deleting it if you look at here there is no data in the table but you can roll back okay roll back before committing you can do anything it will be rolled back now the data are restored okay now i want to delete only particular data i can go for where customer id equal to this one customer id equal to this one customer id equal to this customer id is it possible in truncate no it is not possible that is what this is the difference between truncate and delete so delete you can use wire class and delete you can go for the rollback see after deleting it if you look at here the backup table there won't be the data like this and then i'm just committing it after committing it you cannot roll back it okay do i need this backup table now no i can drop the table so drop table table name it will get dropped so table got dropped here that is what drop table we have done okay backup table you have to drop see whenever you are using that to drop or truncate be careful don't drop it like a main table or not after that you cannot retrieve see even in our real time and all when we are going for any truncate or drop or delete so definitely our project team ask us to create on backup table and then do that uh this drop and drag it on we will keep for backup for next five to ten days after that we will purge the data see this is what we will go for the backup table and everything so i have given the code and then you can use it okay so now we can go for so what is this these are all the things we have seen create table we have seen alter rename we will see now truncate drop insert update delete we have seen merge will see at last commit and roll back everything we have seen now we will see the rename so what is rename we can rename a table or we we can rename a column say for an example here i want to rename customer table i have to rename the mobile to mobile number day dob 2 data birth so how can i rename it's very simple alter alter table table name this is all the syntax you have to remember okay alter table table name rename i'm going to rename a column name okay rename column your old column name mobile to mobile underscore number this is what i have to rename may be renamed right you can see here mobile column it will be renamed okay now i want to rename data but same way alter table table name rename column dob to date of birth date of birth so like this you have to change it the column will be renamed okay how to rename the table okay how to rename it's very simple rename all the table name old table name is customer to one ayatri name something like this customer underscore one i have to rename so i can rename table renamed if you look at here select star from customer table customer table you cannot see this table tableau view does not exist that is what you will be getting the error so instead of customer you can go for customer underscore one you can able to see the data this is what you can rename the table and i hope you are clear about your create alter rename truncate drop and everything so this is what the scenario here so you have the table just i'm creating this table just two columns i have created if you look at this table there is two column select star from customer underscore test you can see here two two columns are there so okay now i am going to insert the data okay insert the data without any save point i'm trying to insert the record it will get inserted all the four rows got inserted you can see here select star from the table name customer underscore test if you look at this table then there are four records are there can i roll back yes i can roll back if i'm going to roll back it will be rolled back up to last commit point since we haven't committed since we have created a create table up to here it will be committed so only table structure will be there okay you want to commit these two alone or you want to roll back these two alone then i have to go with save point concept see what is the save point right so here i can go for save point not it is not necessary that each and every record we have to go for save time it's based on the scenario how many records you want to go for save point you can go for the save point but think so how to use the save point look at here see whenever we are going to create a table right and inserting the data whenever i am going to insert the data i have given the save point now i am checking this right i want to roll back only this statement how can i do roll back to c i can use roll back to c up to here it will be rolled back up to here it will be rolled back only one insert statement you can apply roll back to see the rollback complete you will get only one record so this is what you can see this roll back but there is no commit here just you can see roll back up to this save point this is the same point okay you can do some bookmark i hope everyone is clear about all the statement uh merge and grant and revoke and all we are seen in the installation itself grant and revoke granting the permission normally this will be taken care by dbs team we are going we are not going to do merge statement we will see after some time after this we will go for select statement so this is what you will be having a create alter rename so kindly practice it this one completely then only the next session will be useful for you [Music] hello friends welcome to nikkei academy in our today's session we are going to learn about constraints we have already completed two day sessions introduction to sql rdbms and oracle db and about schema setup and everything we have seen in our oracle day one and how to create a table what is ddl dml trl tcl and dcl we have seen in the day 2 session today we are going to learn about all the constraints related to oracle database if you haven't watched my previous session please watch the session without skipping it and practice it and come to this constraint so that it will be very easy to understand i have given all the sql notes and queries and exercises in the descriptions you can download and you can practice it this is the very first time you are coming to our channel kindly subscribe the channel and click on the bell icon so that you'll be getting all the notifications whenever i'm uploading a video if you have any suggestions or if you have any questions add comment so that i can respond to the comments thank you let us start our session [Music] so in this session we will learn about constraints constraints in oracle this constraints is very important in oracle the constraint is nothing but whenever we are going to insert the data into the table before inserting the data into the table we are validating it if it is a valid data we will insert it otherwise we will reject that particular record okay the data validation before inserting the data into the table it's called constraints data validation before inserting the data into the table let's call it as constraints in oracle we do have five different constraints in oracle the first one is primary key and the second one is not null third one is unique check constraint and foreign key constraint what is this we will see one by one if you look at this table if i look at this table you have the data customer data and customers are located in different different cities so we do have operation in different four cities chennai pune hyderabad and delhi and here we have customer id like customers are located in different different locations since i could not make all the data like customer data and city data in one single table due to this normalization we have splitted this table into two tables whenever needed i will join this one and this one i can say this customer is from hyderabad this customer is from delhi this customer is from chennai so i can say this is called joining joining we will see later a later point of time but this is what okay before inserting the data into this table i need to do some validation so what is the validation i can do here can i give the same customer id for one more customer is it a valid no we should not allow this record into the same table can i have null value without any customer id can i have customer name it's also not possible see this this particular validation we can say it is a primary key so primary key is nothing but if i give any column as primary key that particular column will not allow any duplicate it will not allow null values and also only one primary key per table only one primary key per table in city id in city table ct id is the primary key city id is the primary key you can give this as a primary key then customer name can i have duplicate in the customer name yes we can have two names two customer can have the same name right we can have can i have null value on the customer no it is not possible right without customer name i should not have any customer data here i can go for not null constraint not null means it should not have any null value but it can have duplicate not null means it is a not null value okay so it should not have any null value but it can have duplicate value okay the next one next one is called unique constraint so what is a unique constraint right so unique constraint means the name itself suggests that is a unique it will not allow duplicate it will not allow duplicate so say mobile number can i have multiple customers can i have the same number number no not possible but customer without mobile number is it possible yes we are allowing okay we are allowing this is called uniq so unique is nothing but it will allow null value it can allow n number of null value in oracle okay it can allow n number of null values in oracle it will allow null value but it will not it will not allow duplicate value in the particular column this is called unique constraint okay then age age you can go for check constraint so what is this check constraint right check constraint will do check means some data validation i'm checking whether all my customers are above 18 years above 18 years greater than or equal to 18 years i'm checking so only that age i will allow so if i'm going to have some 15 years i should not allow this record okay that's the regard i should not know see what is this column i'm going to have this column as far in key remember this if i want to make a column a sparring key it's a relationship between two tables so here if i'm going to have the column as city id as the foreign key since i have four different locations four different cities i have operations i need to have customers from these four cities only from these four cities only okay can i have 50 here no i should not allow 50 is not valid only because 50 the city itself i do not have is it a valid record no i should not allow right so i should not allow this 50 and also if i'm going to mention a columnist foreign key it should be a primary key in another table we have to give the reference you have to give the reference of these two table so this is what the foreign key primary key relationship okay this one you can go for normal clear what is primary key what is not null what is unique check foreign okay we can go for can i have combination of one or more columns as primary key yes we can combine this column and this column then create as primary key that will be a character's composite key that i will tell you what is composite here now now i will create the table if you look at here primary key is nothing but it will not allow duplicate value it will not allow null value only one primary key in a table and in employee table employee id will be a primary key product table product id will be a primary key customer table customer id user table user id accounts table account id so like this you can have a primary key okay what is composite key like we can combine the column you can make this column as the combination of columns primary key okay we have to give primary key of customer id and mobile number this combination we should not get the duplicate in order to maintain some history we will go for this composite key we will i will do it practically so that you can able to understand what is not null so it's a mandatory field whenever you are giving mandatory field the diastix symbol that column in the back end we have to define that columns not null column not null means it will not allow any null value but it will allow duplicate value n number of duplicate value you can go for first name column or last name columns not null column or any column we can go for mandatory columns you can make it like a not null what is unique it will not allow duplicate since it is a unique and also it will allow null value so any number of null values will be allowed okay check constraint what is this check constraint you can check whether age is greater than or equal to 18 or check length of the zip code equal to six that you can check it foreign key what is foreign key relationship between two tables relationship between two tables it should be it will accept duplicate value foreign key will accept duplicate value okay in one city you can have many customers parent key will accept duplicate it should be a primary key in another table without primark it will not allow and any number of foreign key in the table any number of foreign key in a table that's also possible see how to create a foreign key you have to create foreign key city id references city dot city city table city if you are violating the foreign key you are getting violation integrity constraints violation okay constraints violated parent key not found integrity constraint validated parent key not found so this is what you'll be getting there are integrity constraint violation errors okay so if you are violating primary key you'll be getting unique key violation unique key also you'll be getting unique evaluated okay you have to describe the table you have to find which table we are getting there okay so now i will create a table so that you can able to understand the create table and everything okay now if you look at this table create table table name customer id number of six so second option the first one will be a column name second option will be a column data type the third will be your constraint so you can give i have given all the constraint in one single create table statement customer id will be a primary key customer name will be a not null mobile number is a unique okay unique as well as check constraint can i give two constraint for a single column yes okay it's a depends on the constraint you can give more than one constrain also here i am giving unique constrain as well as i'm checking the column length length of the mobile number equal to 10. if it is less than 10 or greater than then i'm not 11. then age age is greater than or equal to 18 ctid references city table city id just i'm giving it okay i'm trying to create this table so while creating the table we are getting the error table rvu does not exist why we are getting error right since we are giving the reference table the reference table is not there okay reference table is not available if you look at here select star from the city table is not available this is a reference table so that table should be available first okay just i'm going to create this table city table it's very simple i'm creating a table here create table table name city id number for city name i'm just giving and create this table table city has been created if you go down and then you can see here i am just inserting the data into the table you can see insert into city values i'm just inserting i'll be executing all the records after executing it i'll be committing it okay there is no after executing i am just committing it there is no duplicate value on the city id okay now can i create this table without trying to create now you will not get that particular error but you are getting some different error no matching unique or primary key so this error we are getting it so what is this error no matching unique our primary key for this column list what is this error right so as i told if you are referencing with another table this table is this from this table we are referencing another table for this column this is the parent okay this is the parent you can see here so this is the parent right you can go for so this is the parent one so this is a child one okay this is the child one this table this is the parent this is a child this is the parent customer table this is the parent okay customer id will be the parent this will be your child whenever we are referencing it this should be a primary keywrite but we haven't previously we haven't created a primary key that's why it is not creating it customer table not able to create we are getting no matching unique or primary key for this column list so after that after creating a table can i make the columns primary key yes you can make so you have to use alter statement alter table city okay ct so what is the column you have to add primary key of primary key of ct id you have to use city id to use cte here so what is this right city id is nothing but you'll be having primary key if you are doing it this primary key this should be a unique value they should have a unique value then only you can alter it so table has been altered now i am going to create this table this table will be created now it has been created okay so now i can check whether it is correctly validating all the data yes we will check it one by one so if you look at here i am going to execute the statement select insert you know we know already insert statement or insert into customer table values this one i'm going to insert it i'm going to insert it everything is valid 20 is nothing but it's a city id it's a valid record i'm going to insert it one row got inserted again i'm going to execute the same statement it will not get inserted we are getting error like unique constrained violated y we are violating the two things one is we are violating the primary key and also unique key in order to rectify this error we have to go for another record can i have the same name here yes we can have okay the second column we have given us not null only we can have the duplicate so not null column will accept duplicate but null value will not accept look at here null value it will not accept next to one null value i am passing here it will not be accepted cannot insert null into not null column this is the error you'll be getting it you have to give the column here not null okay can i have the same mobile number here same mobile number here no you cannot do it you are getting again unique constrain violated okay if you are getting error like this right so in real time how will you identify which column we are violating which current we are violating how will you identify we are getting simply unique constraint validated since we have created we know just now we have created we know that this column has unique this column has unique but in real time project someone has already created you are trying to insert you are getting the error like unique constraint related by describing it can you find it no by describing it you cannot find which column is unique column we can find which column is not null column but you cannot find which column is unique column so how to find it you have to go with one table called select star from okay all underscore constraints all underscore constraints this is the one table where one or equal to hr see datas are case sensitive in oracle the datas are case sensitive order equal to hr and table underscore name equal to what is the table name table name equal to customer so you can use customer see for the customer table it will show you what all the different constraints are there since we haven't given the constraint name system itself it is defined the constraint okay here u is nothing but unique constraint r is nothing but referential constraint p is nothing but primary key c is nothing but check constraint check constraint are not very constrained see you can see here all this but you don't know why which column it is right see here unique constraint has been defined but you don't know which column it is right see is there any economy details here no here there is no column details what is the constraint name constraint name is 9883 so you can take this constraint name you'll be having one more table all underscore constraints underscore column this is the table you can you can execute the statement now you can find what is the column name 883 right you can see mobile number is the column so you can identify okay mobile number we have defined as unique constraint okay do i need to manually check like this no you can go for join okay you can go for join like this you can execute the join query you can get the i have joined these two table all underscore constraints all underscore constraints uh columns okay these two by using the constraint name just i'm executing it you can see whatever the column we here we have we can find which is the check constraint which is the primary key constraint the unique key constraint all this you can execute the statement you can able to find which column is or this constraint has been defined so definitely you'll be using this kind of code in real time okay now this is what the primary key violating will identify okay this this column is the primary key column we can go for another mobile number just you can execute it will get executed the one row got inserted now you can go and check this table select star from customer select star from customer table you can see this data will be loaded based on our validation only the data will get loaded you will not get any age is less than 18. if you look at here i'm trying to insert the data with the ages less than 18. you'll be getting violation like check constrain violated we are getting checked constrained violated like this you'll be getting the error then this one whether it will be inserted no it will not get inside why because here 60 you are getting foreign key constraint that is integrity constrained violated parent key not found this is what you will be getting error integrity constrain violated parent key not found so what is that reason 60 is not available parent key not found see i'm trying to insert 60 here the 60 is not available here that is what so either you have to add 60 here or you have to remove the city from 60 60 from here you can go for 30 itself 30 or 10 or anything 20 or anything you can go for i can go and insert 30 now it will get inserted you can see here select start from the table all the data has been inserted with correct validation you can commit it once inserted you can commit it normally this is what we will go for primary key foreign relationship in our real time projects okay so this is what the primary key foreign relationship are now if if you look at this table i'm going to delete this particular record this particular record from this table i want to delete this record from the table table called customer is it possible to delete so now i'm trying to delete delete from table name where customer underscore id equal to some customer id this customer id i'm going to delete it which delete so i'm going to delete this customer id is it possible to delete from customer yes it is possible you can delete it from this table you can delete it okay from this table you cannot delete it so for an example i am trying to delete ct table delete from cp where ct underscore id equal to 30 i am trying to delete it from the city table i am going to delete this table city but it is referencing here ok you cannot delete from here so you cannot delete from here integrity constraint violated child record found see what is this error child record founder that means for this record we have a child record for this 30 here child record found that's why we are getting error in order to delete this one we have to delete these two first then you have to delete here okay you have to delete these two first then you have to delete so how to delete delete from table name where again we have to delete the city id from here okay delete customer table customer table two rows deleted then you can delete it it will get deleted look at here so that is what but i'm not doing anything i will be going for rollback i'll do roll back now so this is called parentkey child relationship product okay now i have one more option called on delete cascade so what is this on delete cascade right is nothing but whenever i'm deleting a record from this table the corresponding record in this table the child should also be deleted this is called on delete cascade if you are deleting here the child also cascading that okay that's what on delete cascade that error okay the how to do it first of all while making the table itself while making this column itself we have to make on delete casket so like this we have to make on delete cascade like this we have to make since we haven't made this options while creating the table after creating the table is it possible yes it is possible how to do it so we will check what is the constraint name we will check that what is the constraint name here we will check it what is the constraint name constraint name is there for hr schema just execute for hr schema what is the relationship constraint we have this eight four at eight four we will we will drop it drop constrain constraint name just we can drop see here so why i'm dropping it already constraint we have created without on delete cascade just i'm dropping this constraint this one this is this relationship constraint right i will drop this constraint the foreign key constraint now it has been deleted okay so you cannot delete as it is that's why i have checked what is the constraint name i have deleted now now i'm going to create this table foreign key i'm going to make it this column is foreign key with on delete cascade option okay alter table table name alter table table name add constrained constraint name i'm giving this is system constraint now i'm giving a constraint name foreign key of city id references city city id on delete cascade since i haven't given previously i have just i am executing it it will be given now you can check here see all will have the system consign name but here since we are given the name it has taken this is called constraint okay now if i'm trying to delete this record from here see look at here select star from ct select start from city you have you have four records select star from customer how many records we have we have four records i am trying to delete 30 from the city table how many records are there before deleting it four records are there in customer i am deleting only from ct table record 30 okay how many records should be there in city three records 30 should be deleted customer also the 30 record got deleted okay i have deleted from here only from here only i have deleted since i have given on delete cascade has deleted a child table as well so that is why the on date cascade is very very important okay in real time and all they will ask an interview what is it or not okay this is what you would be having if i want to check metadata tables all the column table name column names and everything you can go for all underscore tables all underscore tables where one or equal to hr nothing but schema name see these are all the tables are available in hr schema you can check all the table list and also all underscore tab underscore columns all tab column means column level details should be there i want to find out in hr schema how many tables salary column is present how many tables column salary is present see these many tables salary column is present okay the column name salary is present on these tables so like this you can check some metadata tables all underscore table all underscore table underscore columns all underscore constraints all underscore constraint underscore column see these are all the metadata tables we can have in our oracle team okay so you can check it so now i will go to the i hope you are clear if it is little bit confusing don't worry just to practice it if you are practicing it definitely you can able to get it okay i will tell you what is composite key now what is composite key i will drop the existing table i'm going to drop the table drop table draft table table name can i drop customer table or can i drop city table first drop table city can i drop it i'm going to drop you're getting error why because it is referenced warranty reference first you have to drop this table customer table then you have to drop the ct table this is what the parent key parent and child okay so i have dropped i'm going to create a table with composite key look at your composite key so like this i'm trying to create since we have dropped the ct table we are getting the error this error we are getting okay tableau viewer does not exist so i am going to create this table with the primary key table has been created and entered now i'm going to create this table create table table name this the customer table i'm trying to create with customer id mobile number is primary key okay customer id and mobile number both the column is primary key okay what is the use of this one you can look at here insert the table like this if you look at here so i'm going to insert this record it will get inserted again i'm going to insert you'll be getting error unique constrained violator why because the combination again i'm repeating same with same statement i executed if i'm going to insert this particular record it will get inserted why because here even though this is a same value since we are not giving this column alone as primary key we have given both combinations primary key since this is different number this will allow now okay same again it should not be repeated look at here same zero should not be repeated again okay can i have the same mobile number with the different customer id yes we can give that is also allowed so you can look at here so different different data same earn only but here same customer id but different mobile number here same mobile number but different customer rate the combination should not be repeated okay that is called the composite key in order to maintain a history we will go for a composite key can i have where care columns primary key yes we can have see can i have vacations primary yes we can have see if we look at here country table so i'm country code customer country code i'm creating as primary key and country name i'm making like both are like if i'm going to have this one the first record will get inserted second record will not inserted why same id you are passing same id we are passing this is the primary key this one should be a us then it will get inserted can i have bad care columnist primary key yes see i have given the all the statement i have given please go through this and practice it day two and they'll be doing the hands on all the constrained related questions you can ask you can go through that but all the different tables are there just to practice it you can able to get all the constrained related stuff in our next session we will see the select statement before go to the next session please complete the exercise on day one on day two all the exercises you try to complete day one day to day three all the day threes you have to complete it okay next session we will meet [Music] hello friends welcome to nikkei academy in our today's session we are going to learn about session 4 select statement in our previous session we have seen the first session we have seen what is sql what is rtbms and we have installed and then we have seen the setup and second session we have seen all the ddl dml how to create a table alter the table and delete truncate drop everything we have seen and third session we have seen the constraints in our article all the constraints like primary key foreign key and check constrained not null and unique constraint all the constraint we have seen with example and then we have seen what is on delete cascade and everything we have seen and what all the metadata tables are there for maintain the constraints that we have seen if you haven't watched the previous session please watch the previous session in the order then you come to the fourth day session it will be useful for you and moreover you have to practice the session i have given the all the sql queries in the link you can download and you can install the software and you can practice it thank you for watching the session let us start our session if you haven't subscribed my channel please subscribe the channel and click on the bell button so that you'll be getting all the notifications [Music] in our today's session we will learn about all the select statement and single row functions in oracle the single row functions and select statement will be used in informatica or any other languages any other softwares any other tools very much so watch this session without skipping it and also you can practice it you have to practice it then only all the single row functions will get memorized okay so now we will have this see in our hr schema we have a table called employees table so we have the table called employees table if you look at here this is employees table in our hr schema you haven't installed oracle software please watch the installation and then install it and then practice it so select start from the star is nothing but all the columns so you'll be having all the columns here see how many columns are there in the table all the columns will be populated here okay star and we haven't mentioned anywhere class so we'll be getting all the rows so in our hr schema how many records are there you can right click on the data we can go for count rows we'll be seeing one not sound records so totally we have one not seven records all the not second records with all the columns you are getting it but if you want to restrict some columns you can go for you can restrict some columns here you can see here we can mention the column names say employee id first name email higher date salary department id say out of 11 columns we have just mentioned some six columns we can see here all the six columns here only six columns will be showing here okay this is called restricting the column and we can go for something called alias so alias is nothing but for an example if you are looking at here the employee id as associate id okay associate id just i'm making that it's our last name okay last name this is called call malia's column alias is nothing but we can unleash the we can give while selecting the column you can give some other name okay look at here this column has been aligned with associated it will not physically renamed we have seen in our previous session renaming the column renaming a column is nothing but it will physically change the column name but alias is nothing but it will while selecting it it will change the column okay so either you can make this way or you can go this way also the column name you can mention space some name it will take it as a layers name if you look at here so this is what it will take the last name this is called a last name either you can mention this way or this way if you look at here i'm going to make the column employee id first name command last name also i'm going to make last underscore name but i'm going to make first name and last name i'm going to concatenate so how to concat this is called column concatenation so i want to make concatenate of both the columns and go for column concatenation column concatenation so concatenation is nothing but you can concatenate the column i have the first name last name i want to concatenate both i can mention one more time here come on make it like concatenation is nothing but concat of the column use concat concat of we can have first name comma last name okay if you look at here this is what we have first name comma last name you can only ask it as full name or we can make this way as full name full name if you look at here this will be denoted as full name but i need some space between the first name and last name so how can i make space here so in order to make the space if i'm going for concat this concat function will take only two arguments first you have to take single quotes okay single quotes we have to take one space one space with single quotes then again you have to go for concat say concat of this resultant comma last name why because this concat function will take only two arguments first concatenating by using first name and space and then resultant i am concatenating this one this is called nested function okay nested function we can see here full name you can see this is the full name if you look at here the column concatenation either you can go this way or you can go for pipe symbol first name pipe symbol last name okay space and last name pipe symbol you can go for above enter you'll be having two pipe symbol so you can use this and you can have the single quotes again pipe symbol last name then you can make this as fully okay this is what we can go for this is also called as column concatenation so you can see this concatenation many many times you will use concatenation in a real time project please make sure that say for an example if i'm going to give here so this way so i forgot to mention the salary comma here what will happen i forgot to mention comma here higher date i forget to mention comma salary so whether it will throw error no it will not throw error it will give you the higher date column as salary see here higher grade column the earliest as salary y because here whatever the column name comma whatever the column name space some name we are giving it will take it as a last name okay it will not throw error so this is also a column layers okay next one is called column calculation so you can see here column calculation so we can go for something called column calculation is nothing but i have the salary port salary plus thousand i'm adding thousand dollars with existing salary will be having the new salary see a new salary you can see salary column and then thousand dollar has been added as new salary sales name this is what you can able to see here okay this is called column calculation so we can do i want to know the instead of new salary i want to find out annual selling this is salary into 12 i can make this as the annual salary annual salary here you can execute this will be your annual salary okay so this is called column calculation you can go for plus symbol of any number of columns we can make plus symbol of any number of columns it will be calculated the values but it should be a numeric one so if i have many values i have many values the if you look at yes employees table i will show you the data so that you can able to understand so this is employee id you will have the unique key so here it's a primary key unique value should be there then the first name last name email id phone number of the employee higher date and then job id that's a designation and then salary commission percentage commission percentage means whether this particular employee is getting commission for the particular month or not some of them are getting commission some of them must not getting commission manager id department id so you have all the employees are working in different different departments okay you can say see this department here and this is what the department id will be there okay i can go for i want to know how many departments are there so if i'm going to select only select a department id department underscore id from employees you can see only the department id and see the department id see all the one not seven records will be selected but i want how many unique department ids are there 90 we have 60 we have 100 we have right how many unique departments id we have then that's called distinct okay distinct distinct department id you can go for these are all the distinct department id if you want to make order by you can make ascending order if you click on this one it will make ascending order from 10 to 140 we have all the departments so some employees stacked with null value null value means the sum employee is not tagged with any department that's what we have the distinct department id okay how many distinct department ids are there so how many distinct department ideas are they can go for count they can go for count see remember the account will not consider null value so only it will it will it will show you only 13 so 13 unique departments are there that's what it will give you any aggregate function will not take the null value it will not consider the null value this is called distinct value distinct we are taking the distinct okay column concatenation column calculation column layers we have seen column distinct we have seen now we will go for something like a where class the var class is nothing but restricting the data you can see where class i will show you i have given all the nodes so please practice it see i'm going to use this where class this is basic that class only you can go for a number of where class after some time some complex work loss select star from employees bear salary greater than 10 those who are getting more than ten thousand it will be taking so that's what we'll be getting the those who are getting more than ten thousand here okay those who are getting less than three thousand salary it will be populated here okay i want to take here if you go here these are all the salary you can take different different views if you look at here so this is salary greater than 5000 and less than 7000 five thousand and seven thousand it will not include five thousand why because it's greater than five thousand less than seven thousand it will give you salary between this five thousand one to seven thousand uh less than seven thousand it will not include five thousand seven thousand if you want to include you can go for greater than or equal to less than or equal to something uh you will be getting the seven thousand also you can go for between keyword this between keyword will give you five thousand to seven thousand it will include five thousand and seven thousand it is equal to the above syntax you can able to see here five thousand to seven thousand it will include the five thousand and seven thousand also it's a so you have to make sure that if you are using between you have to use under keyword between five thousand and seven thousand not between that means below five thousand greater than five seven thousand that's what you will be having the not between so you'll be having all the records here so this is what you can use somewhere class on the numeric column select start from employees where department id department underscore id equal to 30 i'm using so what could which those who are working in department 30 it will be picked up so you can see only the employees working in 30th department that is what you can able to see here and the employee need to work on 30 and 60 also can i use this way no i cannot use it if i have more than one value on the same column instead of equal you have to go for in keyword in department id in 60 and 30 like this if i have more than one value on the same column same column if i want to mention more than one one value then you have to go for the in keyboard so i can use in 30 60 and you can use any other column any other value also 70 also you can use it so this is what you'll be having the 30 60 70 all this i can go for not in also not in what is not in so not in this particular value so you can go for 30 and 50 and 80. so i'm going with this one see those who are working other than these three department i'm going to take so that's what you can have this other than these three departments you can get it see 10 20 then 40 then 60 like this you're getting it so this is called in norton you can go for any other character column also here so you can go for where job id in you can use like this character column also you can go for job underscore id you can make something like single quotes you have to use single quotes and then job id i'm using the tit programmer okay single quotes this is a character column right you have to go for single quotes so you can use how many see if i have more than one value on the same column you have to go for in keyword see in these three job ids only eight employees are working you can see here only these eight employees are working you can go for in or not in okay if you look at this one what is this condition where department id equal to 80 and salary should be greater than or equal to eight ten thousand so if i'm using and condition both the condition should be satisfied the person the employee needs to work on 80th department and getting salary greater than 10 000 so you can get this value and you can make this 80 greater than 10 000 here you will be having the department id equal to 60 and our salary greater than 15 000 or salary department id should be 60 or the salary greater than 15 000 see here salary greater than 15 000 right you can see here it is not from department id 60. see either one of the condition should be satisfied that's all here you can go for nested condition also either the department id should be 60 or 2 conditions should be satisfied so that particular record only it will be taking so you can see you can validate this record so either the either the employees are working in 60th department are both the conditions should be satisfied you can see here this is what you can see the values and you can go for row number so what is the row number right row number means say for an example in in the real time project you'll be having millions of record in the table so always don't use select start from the table name okay if you are going to use it will get impacted query will not be working fine okay you the query may be hand if it is transaction table or some other table if there is no partitions so definitely it will get hang you should not use select start from the table name so if i have millions of data i want to sample i want to take only five records or 10 records you can go for row number so each and every record in oracle you have the row number if you look at here i will show you the row number what is the row number you can see select row num row number comma row id row id from employees table so what is this row number right see the left hand side whatever you are seeing here so this is the row number say row number you are seeing this row number one two three four five all the one not seven record will have the row number row number in future it will change for each and every record if you are deleting this particular record the row number 34 will become 33 but row id will not change it's a memory location row id it's a unique id for each and every record in the oracle database remember row id is the unique id in oracle database okay it will not change even if you are deleting this particular record this record will this row id will get deleted it will not be assigned to any other record after some time sometime only it will assign see here you have a capital letter small letter some special characters and all we have okay then this is what it will be making the row id then it will not be repeated in the near future okay so i want to select can i select row id comma all the column star can i make like this i cannot make like this you'll be getting error can i make star with another column remaining column no if i want to make star with other columns right you have to use like this you have to alias like table name as employees as e this is table alias previously we have seen column layers this is table yes table i have a list like e then e dot star you can make e dot star so this is what you can see each and every record will have the row number and row id row id is unique row number also unique only but it will change in the future but row id will not change okay this is what even if you are not aliasing like this you can make employees dot star okay employees dot star it will fetch you the same result so this is what you can go for so what is the meaning of employees where row number less than or equal to five you are selecting only five records so out of all the records you are selecting only five records the row number will work with less than or less than or equal to only it will not work with equal symbol it will not work with equal symbol it will not work with greater than symbol you can remember this okay all this work with less than less than or equal if you look at this our table employees table select star from employees where commission percentage it has some null value commission percentage is null so if you look at this table if you look at this our employee table some employees are not getting commission some some of the employees are getting commission okay i want to know how many employees are not getting commission is null is null is not known you should not you should not make equal to null equal to null will not work okay you have to make is null is null is not none it's null means those who are not getting commission it's a blank column okay blank empty null are same is not null it's not null means those who are getting commission see this is what you are getting it okay how many of them are getting commission so i can go for select count of star you can make count of star count of star you can make it count of star 35 employees are getting commission how many employees are not getting commission not getting commission can i go for this way see this is the way you want to go like where commission percentage is null you can go for 72 you have to make select count of star you should not make any other column star means all the columns so this is what you have to make the values null value normal value all this you have to make this view okay count of star if you are making count of commission percentage what could be the result it will take only the not null value why because i i i already told you that count will take only the values it will not count it will not consider null values so only 35 employees so this is equal to this one okay so okay clear that is what you have to go with and so you never uh in our our employees table if you look at this employees table right i'm selecting only the select start from employees for executing this one whatever i have selected that only will be executed in our employees table we have a higher date date column say i want to take only year part from the date column so i can go for something called character function or character i can use character i'm converting the higher date into character so this is date column this is character column so i'm i'm taking that only year part so only 2003 so you can always sit like year year after higher higher date so i want to make year and month i can go for two care of month you can go for month mm mm means number you will be getting mon means month you will be getting look at here i'm going to use m o n see these are all very much used in our real time project so definitely for the database is oracle so look at here definitely will be using it and it will be slightly changed for the other uh other one if you are using m capital then first letter will be capital here m o n t h m o n t h means full you will be getting the month you can go for d d you can go for ddtd is nothing but date so you can go for date and even you can go for day also see what is the day of this one either monday or tuesday you want to first letter capital then first letter capital see monday tuesday wednesday that you have to use here like this so i want to know how many of them joined this particular year okay then i have to use where class right see where class you have to use i want to know how many of them joined on 2005 see this is the way i have converted higher date into character and i'm using this one 2005 this is one other way other way we can use it in the two date column two date function i will show you after some time okay this is to date 2005 so those who are who have joined on 2005 you can take it here this is what you can take we can go for two date function also so this is nothing but those who have joined from september 2005 to september 2006 this is what only four employees joined this is what you can able to see here okay between you can use and you can use if i want to filter out the date column if i want to filter out that the month february month those who have joined on february month if i want to take in oracle this is what you can take and march 2005 march 2005 this many employees have joined and you know that whichever the employee has joined on monday you should not use day alone you have to use a firm date okay fm day this is called format month okay so format this one so you can use you have to use f from day and then you can use this way also d also b means day weekday weekday one is nothing but sunday two is nothing but monday so you can use weekday so this is what you can use a monday or tuesday or something you can use it like this okay and what is dual so if you look at your dual is the dummy table so select star from dual is dual is nothing but dummy table so you will not have any any value in it so w table you want to go for and i want to i want to know what is the today's date i can go for system date this date from dual i can go for say today's date is 19 december 2021 you can go for current day also current you can check it current underscore date you can go for current date we can go for system timestamp also system timestamp it will give you the timestamp along with all this it will give you the timestamp you can see along with timestamp we can make selects date plus one tomorrow's date it will show you okay since date plus 10 after 10 days it will show you since date minus 10 will go for before 10 days so you can use any date column like this system date system date plus minus you can go for any columns okay this is what you can go for the columns okay now you can we can see the pattern matching so what is the pattern matching right i i do not know exact value of the column you can go for pattern matching see what is the pattern matching right like keyword you can use like keyword within single quotes if you know the value you can mention that value if you don't know you can mention the percentage percentage means if you are using two percentage left hand side you can have anything right hand side you can have anything in between so it will be coming anything so if you know the left hand side very well remove this and then ending with anything or you can keep like this starting with anything and ending with one value something okay this is what you can go for like keyword is a pattern matching if you look at here select star from employees where first name like a percentage starting with a first name starting with a a percentage means starting with a after that you can have any number of character any values okay starting with a can i make starting with small a oracle is case sensitive as i told in oracle datas are case sensitive if any value starting with a it will be any first name starting with a it will show you otherwise it will not show you starting with j yeah we have one employee starting with small j ending with small s okay lower case you can have ending with s in between you can have a n so all the first name will be having a n in between and something starting with capital s ending with small n in between you can have anything that's what the meaning of this one so like this you can go for any pattern matching and what is this underscore right so if i'm using one two three four five six seven seven underscore so seven characters it will be displayed the length should be seven characters first name seven characters length the first name six characters second character should be a that is what is this one first name should be six characters seven characters the second character is a first name is six characters second character is a fourth character is t like this you can take something like you can go for select star from employees where salary you can make like keyword like like something like this you can have any characters one characters you can use 500 what is the meaning it should be only four digit it should be ending with 500 salaries should be four digits ending with 500 five digit ending with 500 so like this five digit ending with 500 so like this you can go for so you can have zero zero also zero zero or something like this any characters okay five characters any characters after this that is what you can go for the underscore you can use underscore so these are all the pattern matching and everything then order by class so what is the meaning of order by right the order by is nothing but if you look at here select star from the table name it will select based on the employees what is order we have inserted the data into the table we have uh inserted the data in this order that's what it is showing i want to make order by order by first name i want to make order by first name first underscore main so this is what you can use first name here can go for order by first name alphabetic order to make see whenever you are making art order by right see this capital a is different small a is different based on the ascii value it will define okay this will take the ascii value of this one that is what it will take as key of a is different as kf small a is different capital a 65 small a is 91 so based on that if you look at here it is making the order by you can see here after w you are having the j why j is lower case j this john is lower case it will not be coming with this jar null why because the ascii value of this j is higher than the this this w that's why after this you are having it based on the ascii value it will make okay i can go for order by first name descending order descending order means if you don't use the descending by default make ascending order descending order means highest to least so since we are the j is lower case first it is coming then after that you are having the descending order so this is also disturbing order okay i want to make order by deciding on the salary column order by salary descending i can make salary descending order okay if you look at here it is making the salary descending order okay but how it is making the order two employees are getting same seller if two employees are getting same salary how it is ordering this is the order then shelly will ask question why i am the second person among us right then how can i go for order i can go for one more column i can go for one more column order by salary descending comma are making higher date those who have joined very first to our company based on the order it will make see if you look at here first it will order by on the salary column only descending order if you look at here listening order if we are getting any duplicate value on the first column duplicate value on the first column then it will go to the second order second order the higher date higher date june 2002 then august 2002 you can see here here i have four records on ten thousand salary okay two thousand two two thousand four five six it is ordering based on the second column you can go for n number of columns if it is column is value we have duplicate value then it will go for the second the first column itself order by if you are making employee id it will not check second column why because employee id is unique you're using unique column then it will not go for the second column instead of like this order by 8 i can use order by 8 is nothing but 8th column see what is the eighth column one two three four five six seven eighth column is salary order by salary descending then sixth column is ascending higher date is ascending this is what you can see this is the value okay salary column descending order then ascending order you can sometimes you you'll be seeing this kind of scenario right select star from employees okay implies order by 2 order by 3 something like this order by 3 means star we are making all the columns so one two three third column is the last name it is ordering okay so now is that same select star instead of star i'm making employee id i'm making employee id first name okay first name email email and then salary if i'm going to make order by three is that order based on this last name no it will make first column second column third account third column is email so that's what email column it will order clear so whatever the column you are selecting that particular order it will make so this is the order by we can see i have mentioned everything you can go through this and you can have this so these are all some of the select statement in our oracle database please go through that all the select statement you'll be getting the idea and moreover you have to practice it you have to practice it then only you'll be getting lot of lot of concept here i will post some exercises so you can complete that exercises then you'll be getting most of the stuff here okay then we do have single row functions in our next session we will see that single row functions [Music] hello friends welcome to nikkei academy in our oracle sql series so far we have seen four days of session we have completed introduction about sql and then we have seen what is rdbms what is oracle db what is schema how to set up our article how to do installation everything we have seen in our first day and second day we have seen how to create a table and how to alter the table ddl dml dr everything we have seen and third day we have seen the constraints like primary key foreign key not null unique constraint and check concern every constraint we have seen and the fourth day we have seen the select statement all about the select statement what are the different select statements are available in our sql that we have seen and today we are going to learn about all the single row functions in oracle so single row functions are very much used in our real time projects so learn the single row functions kindly practice it i have given all the sql queries in the description so please follow it and you can practice it if you haven't subscribed my channel please subscribe the channel click on the bell icon so you'll be getting all the notifications let us continue our session [Music] now today's session we are going to learn about single row functions in oracle we do have different functions single row functions aggregate functions analytical function that's a window function we have we will learn about single row functions the single row functions are mostly used widely used functions in our informatica sql pl sql queries this single row function we have to learn one by one very clearly if you learn this and then if you are practicing it so definitely you can solve lot of business logic by seeing it it will be looking like very uh easy but the logic where we are creating it right we may go for some nested function they would have used some registered function that time you should know what this function will do it's very easy only if you practice it it's very easy we will see single row function single row function is nothing but aggregate function we have sum of count of minimum of max of average of these functions will aggregate the data and then it will give you the result but the single row function each and every record it will give you the result okay that's what the single row function will do if you look at here i will share this document to you you can practice it no are no issues watch the session and then practice it if you look at here i'm selecting select employee underscore id comma first name and i'm making like upper first name lower first name init kappa first name length of first name reverse or first name see what is the upper first name it will convert the first name into upper case this is the first name actual first name then it is making this first name into uppercase lowercase initial letter capital then length of the first name and reverse order of the first thing this is what the function will do okay what is the use of these functions for an example i want to know how many of the employees named as john i want to know how will you make select star from employees where first name right first name equal to john so which john i can go for can i go this way if i go this way it will select only this john only it will not select capital john or lowercase john so you have to select then you have to go for r class or you have to use r class like this you have to use first name like john r first name like this john okay capital john capital r you can go for this way like small jump so how many is there anything like yeah so we have some terms right if you are using this way only three records you are selecting that's wrong actually you might be missing two records right see like this you have but in real time we cannot mention all the cases that you can go for upper case how upper case if you consider this is upper case of first name it will make all the john into upper case right it'll convert all the case into upper case then you can equate to upper case if you look at here where upper first name upper of first name we are making the first name into uppercase and then equate with uppercase then you are selecting it you can ask me you are you are equating only upper case right why you are selecting all the records we are selecting select star from employees this is where class only where class you are converting this into upper case and equating with upper and you are selecting the records okay that's what you will not miss any value something like this country equal to india you are getting it if i use low enough address equal to chennai here you have to use like this low error of order equal to chennai lowercase upper of address equal to uppercase so like this you have to take so this is what the upper of lower of length of reverse of all this to be used substring function if you look at the substring function substring function will take either two arguments string given string and from position or three arguments it's very simple only substring given string from position and number of character number of character if i'm giving like this sub string of welcome to india okay substring of welcome to india this is hard coded value i'm just giving it that's what i'm using dual in the dummy table 12 comma 5 starting position of the from position that is 12 and 5 characters 5 characters not position here 5 characters what is the 12th position one two three four five six seven eight nine ten eleven twelve twelve is high so from here to five characters one two three four five india it will give you the result okay substring and in string is very much used in our informatica piece you can practice it one by one from position then you can mention that you can simply mention 12 alone 12 alone 12 alone means from 12th character right hand side it will take okay from 12 right hand side it will take you can make minus also you can make minus minus 12 something like this minus 5 or minus 8 you can mention minus 8 what is the minus 8 right minus means from right hand side 1 2 3 4 5 6 7 8. so india 3 exclamatory symbol you will be getting it i want to take only india only india how to take you can make minus eight comma five only five characters so this is the this is the way you have to take it okay and if i'm going to use like this 1 comma 4 so what is 1 comma 4 1 comma 4 is nothing but char by d from first character 4 characters from first position i have to take four characters from fourth character i have to take what i can do four that's all four comma four means from fourth character four characters from fourth characters one underscore is there if you look at here one underscore is there in between that's what from fourth character four characters why three characters only we are getting only three from fourth character only three characters only available in this that is what you can have the sub string you can have the sub string here and you can go for minus and all this this is called a substring okay i can go for in string in string will return position of the character in string will return position of the character what is that in string will do if you look at here in string of this value in string of corporate floor so i'm having like r so where is our first occurrence of r it will give you this is the first current side you can take faster currents of r if i want to take second occurrence see second occurrence you have to mention this is r from first character second occurrence from first character second occurrence so to take second occurrence is nothing but see this is six for getting one two three four five six second occurrence so like this you can take 1 comma 2 what is a b carbon floor is there any a b if there is no letter with a b then it will give you a is there but we are checking a b then it will give you zero if there is no matching then you'll be getting zero okay zero means there is no match from third character second occurrence this is four o r is nothing but from third character second occurrence second occurrence if you are giving only three arguments three arguments here three arguments like this three you are giving see we have given two arguments we have given four arguments if i'm giving three arguments what will happen so this is the qr it is giving you the five five means this is the one it is taking from third character from third character it will take from third character first occurrence it will give you see three comma one is same three comma one is same okay three is nothing but from third character first occurrence what is the first occurrence from third character this is the one first occurrence right first occurrence from beginning it will count one two three four five okay this is what even you are mentioning or it will starting position of the character it will be return so that is what five okay if i am making like four four what is the result you'll be getting same result from fourth character if i'm making tenth character in the character first occurrence so this is the one it will return 14th one from here you have to have to check it will be 14. so this is what it can go for mean string okay where it will be used normally in stringer see how it will be used in string [Music] instinct say for an example you have something like this character i am getting the in our informatica and all you will get like this i'm getting one particular value like department insurance i'm getting value like this insurance and tilt symbol i'm getting the location as something less i want to take the location id and i am taking hr department and location is something like 4 30 something like i have the value so whatever the number of character you have you have to take insurance alone you have to take hr alone the location id alone you have to split after the till symbol so how can i take i have to write the query to to take insurance hr alone under 200 30 alone first time first we will write one query for taking the insurance alone how can i take insurance select substring of suv strf substring up this is given string that's what i can make single quotes given string from first character how many characters i can mention how many character i have to mention one two three four five six seven eight nine nine characters one command nine i have to mention can i do like this hard coded if i do like this it will work only for this value only this this particular code will not work for this one i'm going to give this one instead of this one it will not work right if i want to take instead of 9 i have to go for the dynamic way of finding the 9 how to make it you can go for in string function where this position is coming this tilt symbol right find the tilt symbol if it is tilted simple position is 10 10 minus 1 is 9 right then that you have to make it here can i make like this select gin string of in a string of the string you can make where is the finding letter is the tilt symbol and then first occurrence right where the occurrence you can go for from dual from first position occurrence it will give you 10 but i want to know the minus 9 up to 9 right you can go for sub string it is giving you the 10 okay instead of 9 you place that minus 1 minus 1 it will give you correctly i'm not hardcoding that one value instead of this this value right if i'm placing this value here it will give you correctly see any other value is coming so normally you'll go for column name here since i'm using hard coded one it will work correctly it will remove it will take only hr from here same way you can take 200 you have to here we have used minus 1 right there you have to use select substring of plus 1 substring of the given string plus 1 right hand side you have to take right plus 1 right hand side 430 and if you are giving this one it will take 200 correctly if you look at here it should take 200 correctly see this is what you'll be using in string and substring in our real-time project so definitely this kind of scenario we will be getting it in real-time project and only then you will be taking it the same way if i'm having some some employees you are getting email id okay email id i want to take from this email id i want to take user name alone from this email id i have to take user name alone how can i take same way how we did it for this one right same way here also from email id instead of this i'm going for some other email id okay some other email id i'm going with id of this one so i'm making this one so it will take correctly okay this will take correctly even if i'm going to make underscore here it will take the value correctly clear on this this is for dynamic code dynamic code we are writing clear this is what we will take in string and substring in our real time project okay next one is l pad so what is the l pad right see if you look at here l pad l pad means left hand side padding that's what we have we have welcome it should be 15 characters if it is not 15 characters play star on the left hand side star on the left hand side you can see if you count it it will be 15 characters if you make a length of l e and g th length of this one it will become 15 characters if you are making 30 here yes it will be 30. so why because we are making 30 characters should be there if it is not 30 characters make star on the left hand side okay this is one star on the right hand side our pad is nothing but right hand side whenever we are making something like uh in the demand draft demand draft we are making we are making one lakh something like that you are writing after that they will be having some space right hand side right you can make that space for example 500 characters they are giving 500 characters totally 500 characters you can make you have to write the maximum character is 500 if you are writing simply one lakh remaining characters you can make star on the right hand side no one will can able to fill the fill right after writing it that's what we'll be using this function nested function look at here sometimes account number should be 15 digit if it is not 15 digit left hand left hand side you make 0 right normally you will do in the bank account number and all it should be some 12 digit if it is not 12 digit make left hand side how many characters remaining characters you change it like 0 so this is what l pad this is salary column it should be a 12 characters if it is not 12 left hand side you make the zero how many zeros remaining zeros it will add it should be at all characters all the way so this is what l pad and r pad will be used okay next one is trim function trim function i have given some exercises on this substring and in string please go through that you can able to complete in the description i have given some exercises for the substring and in string please complete that the next function is called trim function l trim left hand side if you have any space it will trim r trim only right hand side of the space it will trim okay it will not trim in between if i have any space it will not trim trim function if i'm using left hand side it will trim right hand side it will trim but in between it will not trim if i want to use in between you have to go for replace character that's what we will have if you look at here l trim means left hand side it will trim if i have space on the left hand side it will trim you will be getting like this it will trim only on the right hand side left hand side space is there it will trim both but it will not trim in between characters if i want to use rim in between i have to use replace one so in between if i want to replace you will see separately but i will use here replace of this trim comma this is nested function you want to replace with space with the null space with not simply like this you can use this will remove the in between space it will remove space then you are having without space so like this you can make this is what you can go for trim function with replace functional law it's a nested function in real time project under we will be going with tested function only okay so you can use l trim or r trim for instead of space you can go for some other character also what is the meaning of l trim of this one right l trim of this one left hand side 0 it will trim left hand side 0 it will trim that is what l trim the character i have given then i'm making that 0 it will left hand side you will trim if i want to trim some special character and all you have to go for regular expression that we will see separately okay here what is the meaning of this one here only one character i have given but here i have given three characters it is not combination it is not combined one it is a separate one either zero r two r one from the left hand side either zero it will trim trim it will trim up to here it will be trimmed up to here it will be trimmed from the left hand side only so you'll be getting from from this one you'll be getting all the records even in between you have 0 or 2 1 or something it will not come here right hand side see right hand side it will trim up to here 6 5 you will be having up to here ok this one both the side it will trim we are using nested function l trim right trim off both the side we are using left hand side up to here right hand side up to here this is what it will do the trim function you can use trim functions for space as well as the character also you can go for any other number character also you can trim this is what l trim and r trim what is replace replace is nothing but i told right if i have the word called jack and jio wherever j comes replace with bl okay j comes replaced with the bl it will wherever j comes it will replace with the bl black and blue it will convert okay wherever j comes it will change to beer what is this one job we are taking job is one of the column from employee wherever manager comes wherever manager comes it will replace with the bars okay think about one time we got a manager like this one time manager like this time manager like this so if the above above syntax the query will change only this one only will change this manager only these two manager it will not change it if i want to change it then you have to use upper of job upper of charm you have to use upper of job your con your converting job into upper case then equating with manager all there will become a manager this kind then whenever manager comes it will change it to bus okay this is what the upper of lower functions will be used very much this is called replace what is the meaning of this one right phone number replace of phone number dot command null this is dot actually dot command null see wherever dart comes wherever dart comes replace with null that means remove the dot from the phone number remove the dot from the phone number i want to add some plus nine one and all then you can go for single quotes plus 9 1 iphone single quotes concatenation so like this you can use look at here plus 9 1 if one this is the way i want to make first five characters then iphone 65 characters how will you make you have to go for substring substring right first five characters hyphen next to five characters you have to use like this this is what in real time and all if they are asking some questions you have to use first five characters substring can i use s u b s t r substring of this one one comma five right one comma five five characters concatenation single quotes hyphen single quotes concarnation then i can go for you can go for six comma five six comma five look at here see first plus nine one iphone first five characters iphone next to five characters this is what you can go for this is you can make new phone number the alias name is called the new phone number so instead of having column this much level you can make alias it this is what you can have this this is phone number this is new phone number this is what the functions are used here we have used substring replays right and also we have used alias columbus so that's what can be used concatenation everything we have used in one single statement okay then next one is called translate so what is this function translate will do right position wise it will translate say for example if i'm giving like this a b c d translate to x y z if a comes it will replace with x b comes replaced with the y c comes replaced with the z and d comes to replace with the null so this is what you can use it see if i'm using the same thing for replace whenever abcd comes then only it will replace with xyz right if it is translate position wise translate yeah it will position wise x that is what it will translate if i'm giving like this then it will translate like this if i come to chennai if i'm using like this then translate wherever a comes it will replace with w b comes it will replace with x c position wise it will translate okay e and f it will replace with the null why because here i do not have any character for six fifth position and sixth position this is what it will convert something like if you want to go for some encoding decoding and all you can go like this okay then if you make reverse order it will replace with correctly so you have to give this one replace with this one so it will be replaced okay then null values if i want to deals with null values if i do any arithmetic operations on null you'll be getting null value only so value plus null value null value plus that is known value plus unknown value will be getting unknown value only known value minus unknown value will be getting unknown value if you do any arithmetic operations on null you'll be getting null only if you look at here we are the salary column select employee underscore id comma first underscore name comma taking the salary comma commission percentage so what is the commission for this particular employee for this particular month you can make i have to make total selling salary plus salary into commission percentage i can go for commission underscore percentage this is what you can make total underscore salary this is what total salary from employees total salary is nothing but your last name so you're executing it look at here for the employees who are getting commission your the employees salary this this much and commission is this much and then total salary we are not getting it actually the total salary should be populated as 24 000 a year right but the employees who are getting commission it is populating directly why because salary into commission if it is null value null okay this is null null plus salary you are getting null value again that's what you're going with the total salary as null okay we should not go like this we have to go with the function should be uh used what is the function right to deal with null value we have four functions one is nvl nvl2 null if and collise function what is nvl no value or null value that is called nvl nvl total nvl it will take two arguments nvl2 will take three arguments null if it will take only two arguments collage function will take n number of arguments okay you have to practice it then only to you can able to understand this if you go here nvl of argument 1 comma or even 2 if argument 1 is null so what is the meaning if argument 1 is null it will take the argument 2 if the argument 1 is not null it will take this value itself nbl of 5 comma 6 it has value so it will give you 5. nvl of null comma 6 will take 6 okay that's what it will give you the result you can see here nvl of 5 comma 6 it will give you 5 null comma 6 null null command both are null so it will give you null so that is what the nvl function will do where it will be used you can use like this say like this you can use nvl of commission percentage comma 0 what is the meaning if commission percentage is null replace with 0 that's the meaning then it will calculate correctly okay if it is null replace with 0 0 plus salary you will be getting 0 here so look at here will be getting 0 0 into salary 0 0 plus salary salary that's what you are getting the salary here those who are getting commission also calculating correctly so that is what nvl function used in our real time so whenever you are you want to deal with any arithmetic operations on null you have to use nvl function or collise function also coil is function it will take n number of argument it will return first not null value okay what is that nbl2 nvl2 as i told it will take three arguments if nvl if first argument is null remember first argument is null it will take the third argument it will give you the result third argument if it is not null it will give you the second argument it's like if then else logic okay if you look at here this is what you will be getting nvl 2 of 4 comma 8 comma 12. what could be the result it should result give you the 8 if it is not null it will give you this one if it is null it gives you 12. okay third argument so where it can be used if you look at here i do not have any table like this but for an example this is what will be used in our real time project see we have the employee id employee name allocation id if the employee has any allocation id if it has value then it will show like allocated as employee allocation status if employee is null then waiting for a project it will return this one either it will return allocated or waiting for a project it will not allow it will not written allocation id allocation id we have to select here that's what we can go for if then else logic okay so now null if what is a null if right so non-leaf is nothing but we can take two arguments if both of the arguments are equal then it will give you a none if not equal it will give you argument one so nullif 5 comma 8 if not equal it will give you the first argument if both are not equal it will give you the first first argument if it is equal then it will give you null value where it will be used see the the both statements are same the above one is same the below one also same the both the sql queries are same first name equal to last name our name is first name equal to last name we are checking here also we are taking first name equal to last name what is that meaning if both are equal you'll be getting none correct null leaf of this one you'll be getting null equating with null that's what you'll be getting the same result if i want to confuse you in the code i can take like this okay i can write something like this both are equal only okay this is what nullifunction college function you can go for n number of arguments it will return first not null value okay it will return the first normal value you can see here we have used salary right you can go for instead of nvl you can use collage function also both are same if you look at here both are same so where it can be used in a real time if you look at here i'm going with this one this is also another example i'm i'm making commission percentage manager id and department id whatever the first nut not null value it will return if everything null it will return 0. if you look at here first normal value is 90 right 90 first normal value 100 is returning 100 so some of the values are first normal value is 0.4 we are getting the 0.4 like this you'll be getting the collise function where it will be used in the real time look at here so i am collecting from from our employee or customer i'm connect collecting mobile number official number then residential number some employee giving mobile number some employee giving both some employee giving all the three some employees giving only office numbers only residential number some of them are not giving but our preference is if they have given mobile number take that mobile number if they haven't given the mobile number check for office number if both are null you take the residential number if everything is null you take the you return like no phone number that is what you can go for the coils function here this is what will be used here okay then round function see round and truncate we do have numeric function here uh you can go for round function and all say look at a round function so what is round round it will make if it is more than 0.5 it will give the next value if it is less than 0.5 it will give you the base value round off 0.7 if it is more than 0.5 it will give you 1 as the value two point eight it will give you three four point three five will give you four this one also five point three five point this is decimal five point three five point seven eight point seven eight we are we are giving it and point seven is more than that it will give you five four three six okay five four three six here point three will give you five four three five only then here we have five four three five point seven eight two decimal point i'm giving two argument here two decimal point consider two decimal point here the third one if you are considering is more than five it will give you seven nine it will it will give one carry forward to the previous digit then it will give you 0.79 why 0.79 so point two decimal point okay the third one is more than five it will give you one carry forward here two decimal point see since it is nine nine it will give one carry for here five four three six it will give you five four three six okay here you can go for three so what is three three digit but here fourth digit is two then it will give you seven eight three only but here four digit so one two three four but fifth digit five will give you three three so like this you can go for even you can go for minus also what is minus 2 minus 2 means or minus 1 see what is minus 1 minus 2 and all minus 1 minus 1 means here it will go for minus sign from decimal point left hand side it will go minus 1 means it will make this as 0 if it is making this this as 0 since it is more than 5 it will give you 1 carry forward it will give you 5 4 4 0. okay it will the value will get increased but here if i am going to use 0 something like a 4 here 3 4 i am using it will make this characters 0 but since it is a 4 it will not give any carry forward it will give you 3 0 only okay 3 0 only that's what it will give you look at here minus 2 you can take minus 2 minus 2 just to practice it then you will get it minus 2 minus 1 means here minus 2 this one it will make both of the digits 0 but that the last digit is 3 it will not give any carry forward here so 5 4 0 0 only but if i'm giving 6 here it will give you what is the value so make 0 here 0 here but it will give you 5 here 5 5 0 0. okay that's what you will be getting minus characters in round function see whenever you are going for round either it will go for left hand side or right hand side base value are highest value so if i have one one here either it should take more than 0.5 it will take one so less than 0.5 it will take this value right this value but trunk function trunk function always it will take the base value trunk function always will take the base value remember this if i am going to use the trunk function always it will take the base value trunk of zero point seven eight seven eight it'll give you zero five four three five this is also five four three five it will take always two decimal point seven eight only three decimal point 783 only no need to calculate here four decimal point it will take four only so even you can go for minus and r and go for minus if i am going to use minus so minus two i'm going to use minus 1 or minus 2 so minus 2 it will make 0 0 it will not make any upward and it will not go for any carry for whatever okay it will take this this is called trunk function trunk function you can use it in the timestamp function also you can use a trunk so what is system times done see for if in oracle if you are getting any any column with the timestamp if you want to take only date column use trunk function use trunk function like this trunk of system system timestamp then this is what you'll be getting the only date column you'll be taking ceiling and floor so what is the ceiling and floor ceiling means always will take the top value floor means it always will take the base value ceiling and floor here if you look at here ceiling and floor floor means base value always will take the base value 5.999 is almost equal to six right but even it will take five only this is also floor floor means in our home via the floor and ceiling right ceiling means always will take the top value this is almost equal to five only right look at here five five point zero zero zero zero point one it's almost equal to five only but if i'm going to use ceiling ceiling is highest value next value so it will take six only okay so ceiling and this one we cannot go for any argument so only single argument it will take what is mod function mod function will return always the remainder in the division operation it will give you that then the division operation will give you the remainder what is the remainder 55 comma 4 remainder you can make 3 same way what is the remainder for this it will give you remainders 1 where it will be used if i have the higher date if i want to take only leap year i can go for mod of you know already took care of this function this function i am taking the air part alone then dividing by four for zero those who are those who are higher on leap year leap year two thousand four eight all the sleepier you are getting not equal to zero you are getting non leap years okay non-leap year then you can go for odd number even number so this is what you can use the functions so odd number even number so alternate record if i want to take you can use like this so these are all the functions practice all the functions okay i have given the notes in the description follow it practice it okay next function is called month between so month between is nothing but if you give two dates it will find how many months are there the between two dates month between two dates so here is the syntax month between date one comma day two if you consider this particular statement you consider this particular statement month between so this is the month you can see here so just i have given two two date of two data just i'm giving the month one date i'm giving 1 1 20 22 1 1 20 10 so we have almost 10 years so how many months are there between these two date we can find okay one twenty months so it is an earliest name kalamali as we can see so this is the 120 months so if you consider next one extra date so a month between today's date system date to 112k so how would you be that so if you consider so this is our month between 2000 so you can execute almost 21 years right so we have these many months so now it's giving this this many uh decimal point if i want to convert this decimal into normal one you can go for round off so round off this particular one comma we want to give some decimal point i can give decimal point r if you do not want to give the decimal point we can remove it it will get round off so here so this is what it will be round off 264 months so this is what we can do the roundup function also we can do it on the resultant okay next one can goes this is month between next one is days between how many days are there between two dates the other date one minus day two we can give so here if you good if you see here so this is what we can see so this is one date this is another date so this will give you how many days are there between this date so we can see here select says date so we can use this date minus one particular date i can give from system system date to so this particular date how many dates are there we can define this one comma and give this one from viewer so from dual can execute it will give you these many days are there between these two dates okay these are all the month between and days between you can find next one is called next day so next day this function will give you next week day of the given date so if i'm going to give 31st march 20 then this is what the next friday after this date when is the next friday you can go for this particular date next friday is coming on third april 20 so this is what we can see one and i want to know from today's date from today's date when is the next thursday if i want to find out next day state of next thursday it will find you it will give you okay next days thursday is coming on 23rd december 2021 so this is what we can go for next today function okay next one is last day last day function will give you last day of the given month whatever we are giving that last day of the given month if i'm going to give last day of the system date this month last a date this month last day 31st december 2021 so this is what it will give you and then last day of state plus 25 i'm adding 25 dates with this today's date then if i add this this one will become any date in between the january so it will give you january last day 31st jan 2022 so this is what you'll be getting it the result and when this will be equal system date equal to last year of six assisted this will be equal only on month end only on month ends it will be equal whenever we want to run any job on last day of the month we can put this logic next function is called add month add month is nothing but so you can get the date so if i want to add number of month you can give that month here so from this date i want to add three months then i can go for add month of this one it will add three months from this particular date this is number first 2003 so this is what it will give you and i want to say for an example sometimes you make some loans for five years right then i can go for if i am going to take one particular loan when is a closer date i want to know then 60 is nothing but number of month so 5 years and then you can see 21st december 2026 this loan will get completed this is what you can see the add month add month is the one of the function so these are all the function like add month last day next day month between all this function related to the date come and we have round and truncate of dates we can do round and truncate on numbers just we have seen and we can do the same thing on the year the date column also here we can do the round off quarter we can do the round off our trunk of our month or day we can rule so how to do it so just i have given this particular document please go through this document it is very useful for you so i will explain this if i'm going to take here i'm going to take year the year will be divided into two part six month six month so we go here it will be divided into six months so if you take this is the year you take this is the year and we can take six months six month so this is january january to june and this is what we can take jan and then december so i'm going to take one year so up to january to june i can take from here and july to december i can take from here july to december i can take from here this is what i can go for the year so if i take here the round off is nothing but taking off and off right so whenever if you are giving any date if the date falls on the first off of the year so it will give you the first day of the year if any date falls on second off of the year it will give you first day of the next year so if the year is 20 21 so it will give you if any date falls on this particular one it will give you 20 21 jan 1st 2021 if any date falls on the second half then it will give you 1st jan 2022 first jan 2022 this is what it will give the result so here we are taking the year into two half then look at here i'm going to round off this one so round off on the year so i'm going to take a round off on the year if you could see here rounding off 22nd august 2021 say august 2021 it's like second half of the year august 2021 second half of the year then it will give you 1st jan 2022 we'll give you the result as fast jan 2022 next one 22nd april so april i'm rounding off on the year path then it will give you 1st jan 2021 i'm taking the system date today's date i'm rounding off then it will give you first day of next year first day of next year first jan 2022 so this is what it will give you the result on year part on rounding up okay so where to be used in real time say for an example i want to find out last day of the year last day of the year i can go for this one so last day of the year so i can take round of state comma year and then this one i want to take last day of the year i want to go for last day of the next year if you add 360 day from here it will be one of the december month right december date then if you take last day of the month it will give you 31st december 2021 so this is the way you can find last day of the year you can find first day of the year you can find first day of the year after that you can find last day of the year our first day of the year minus one we can go with this is what last day of the year then you can go for quarter so the quarter is nothing but we are splitting this into the queue three months we are taking january february march we are taking this jan feb march if you look at here the same thing we are going for john 5th march so instead of here i am going for the quarter so this is the one you can go for q q here jan feb and march so this is the one i'm going with if any date falls on first first half of the quarter then this will be your first day of the quarter if any date falls on second off of the quarter first day of next quarter so this is if the year is 20 21 the quarter is january then we can go for first day 1 1 20 21 this is one this is what you will get so here if second off then it will give you first one for okay first april 2021 first day of that next quarter so this is what it will give you the result if you rounding off on the queue okay if you look at here so this is what explanation i have given please go through it round off select round off this one i'm going for the queue select round off 22nd august see august where it will be july august september august will be second half of the third quarter second half of the third quarter then it will give you july august september then october 1st is the result see october 1st is the result for this you can see here october 1st first october 16 so you have to practice it like i given this all the statement in the link you can description you can practice it and you can see here 13th april 13th april 2020 so where is the april so april may june first off of the queue then it will give you this particular first day of the quarter so first day of the quarter is first april 2020 so this is what it will give you the result okay next one so this is the one i can go for the queue number second number so you can tell second number it will give you first october so this is what that q you can go for q then month is nothing but month it will do the month of so first half of the month second half of the month so it will take if any date falls on second half of the month first day of the next month it will give you first of first first day of the next month okay this is first off of the month so it will give you first day of the the particular month then this is also first day of the month so this is what it will give you the month then we can go for the day so weekday so we can go for weekday so since we have seven days in a week it will split up the weekday into 3.5 days 3.5 days if any date falls on if you look at here then go with sunday monday tuesday wednesday thursday friday saturday this is the one if any date falls on first half of the week then this sunday date it will give you if any date falls on second off of the week it will give you this sunday date not saturday date this sunday date it will give you so this is what on the day surrounding of the day so we can go for rounding of the day so if you are running this if you look at here so i'm going to run this so this is 22nd august 2016 22nd august 2016 you can see here so what is the date and you can take the system date or today's date you can take and what could be the date so today's days you can take today's 21st december tuesday that is what it is giving last sunday date and we can see this this is the one and then you can able to find whether it's the last sunday or the current sunday next sunday this is what it will give you okay so this is what the day on rounding off but we are going with the trunk right so trunk function will always give you the first day of the year first day of the quarter first day of the month or first day of the week you can see here so this is what the trunk no need to do any calculations just you can go for so this is here so if you're rounding up the year sorry trunk do trunk on the year it will give you first day of the year first day of the year so you can see first day of the year first day of the quarter first day of the quarter this is also first day of the quarter then this is what first day of the month first day of the month this is also first day of the month first day of the week sunday date so this should be a sunday date you can see and i'm adding that five days or seven days then it will give you next sunday date might be first day of next week and this is what you can find each and every day so can we do sis date plus three yes we can do it no issues we can do it so this is what i have asked one question before in our channel so if i'm going to if i'm going to take two date month between so whether it will give you numeric date or it will give you numeric value or it will give you date column so here i'm going to execute this month between it will give you numeric only i'll give you the numeric result and also i'm going to take date minus one more date it will give you the numeric data only output is numeric only so how many days are there how many months are there so remember this i have asked this question previously okay this is what rank and round of all the functions i have given so this is the where to be used to this trunk and round off all the functions right so if you look at here i want to find out first day of the month see all these four functions will give you four queries will give you the first day of the month so if you look at here so i'm going to find out from system date i'm going to take only year and month and that i'm going to add 0 1 in the in the before so i'm going to add this is the first day of the month it will give you first day of the month okay then we know already trunk of any date it will give you first day of the month then find last day last day of the month okay then drunk off on the month it will give you first day of the month then last day of the previous month this will give you last day of the previous month then add plus one last day of the previous month it will give you 30th number 2021 if you add plus one it will give you first day of the so how can i find so this one always you first you go to the inside one so add month system date minus one so it will go one month before so one month before we are going with and then we are taking last day of the particular month so last day of the previous month then you are adding one so this is what you can find last day of the month i have given this and you can find first day and last day of the quarter so how to find first day of the quarter we know already trunk trunk of system date my queue it will give you the first day of the quarter so it will give you first day of the quarter i want to find out last day of the quarter so this is what they will ask you in interview last day of the quarter you find normally directly we do not we could not find it just i'm making that trunk of system date it will give you the first day of the month right first day of the quarter then i'm finding last date so last date i'm going to find so first day of the quarter then adding 75 days from the quarter adding 75 days of the quarter say for an example this is first day and adding 75 days of the quarter it will give you so somewhere around third month right third month i'm finding last day of the third month then it will give you last day of the quarter so this is what you have to do even 70 also will give you the same thing 70 also i'm going for it will give you the same result so adding 70 days in the existing one that we can make so this is the one last day of the particular quarter so this is what we can find last year of the quarter instead of system date i'm giving a hard-coded yet it will find you the date so this is what we will find find first and last day of the last quarter last quarter we have to find out so this is what i have given this code just to go through this code you can able to find and we have something like the type casting functions typecasting is nothing but next one is called typecasting functions conversion functions so you can see this is the number i want to convert this number into a character we can convert like this okay character and this is also we can convert this into a character like character here and this is again the character we are converting so this into a character adding dollar and making some comma this one this is now character only you can see here this is 7 3 0 it is binding and also i'm making only one here so since it is 8 it will give you 1 carry forward that is what it will give you the 8 here so if you look at here so one two one zero point eight that means it is giving one carry forward to the adjacent characters okay this is one system date i'm taking system date in different format so you can take this format so this is what in oracle we will do if i want to display the system date in different different format you can make this format m o n is nothing but three character of the month and ddth is nothing but a date and y y so this is what you can find the m is nothing but the numeric numbers and then whatever the way you want to get it so you can get like this and i want to know the system date in this format after that i want to add plus 1 after converting it i'm adding it so this is number this is character character i'm converting as number number i'm adding plus 1 that is what you are getting 20 32 okay so here two care of system date plus one this is different so this is different and this is different and then system date i'm making like hours minute second look at here hh24 colon mi colon yes yes minute and seconds so it will give you in this way so this is 24 hours minutes second and even date also and a.m and p.m so it will it will be a total loss so this is what you can able to find the date and time format okay next function is called abs so abs is nothing but absolute value so if i'm going to give any negative value it will find the the positive value okay so these are all the functions the single row functions please go through all the functions in addition to to this we have many functions are there we will see uh if rest of the functions like aggregate function analytical function or advanced function later point of time so kindly practice this session if you have any suggestions or if you have any questions raise your questions through comments so add comments and i will respond to the comments thank you for watching we will meet in the next session [Music] hello friends welcome to nikkei academy in our today's session we are going to learn about joints in our oracle sql series we have already covered five day sessions uh so far we have covered all these five sessions if you haven't watched these five session please watch one by one and kindly practice it i have given all the sql queries in the notes you can complete that five day sessions and then you can come to the sixth day session in our today's session we will learn about completely on the joints what are the different joints are there what is eq joint non-equation cartesian product in eq join what are the different joints we have like inner join left out the right outer floater and how to write the join condition by using implicit method and ansi method on how to write in using keyword so everything we will see one by one and after completing this you can we can go with aggregate function i have given all the notes and exercises in the description please please subscribe the download and then click on the bell icon so that you will be getting all the notifications thank you for watching let us start our session we have majorly two types of join one is equi join so we can classify this join into uh two types one is equivalent other one is non-equation these are the two types of joints are available majorly in our oracle database equi join is nothing but we will have equal conditions in the join condition we'll be having equal symbol non-equi join is nothing but non-equality you can have less than or we can have greater than or we can have less than or equal to or greater than or equal to r not equal also this is what condition we can write in the join condition so if you look at here the joints are two types of joints one is equijoined an eq join is a type of joint that combines stable based on matching values and has specified columns so this is what equal symbol will be mentioned non-equijoin is nothing but the same way non-equality we will test it like less than greater than less than or equal to all this expression we will use it so non-equi joints we will see after some time eq join we will start with eq joints again splitted into four type of joints one is inner join left outer join right outer join full outer join if you take two tables so this is what the inner join if you consider inner join is nothing but if you take two tables here i have the one table this is left table so this is right table if you take two tables if you do inner join so this is left table this is right table inner join will give you the result of common record between both the tables common record between both the tables the first table if you are mentioning in our join con join will be a left table second table we are mentioning in the join with the right table so inner join is nothing but common record between both the tables if the record is matching the result will be getted the record is not matching we will not get the result second join is called left outer chart so what is left outer join right all the record from the left table so left outer join is nothing but irrespective of right table all the record from the left table are matching the cut from the right table so we will see in detail about all this with some examples so that you will be getting very clear idea the third one is called right outer join it is opposite to left outer join the first table whatever we are taking is a left table and second table we are taking the right table and if you do write outer join of these two we'll be getting all the record from the right table all the record from the right table and matching record from the left table if it is not matched we will be getting null value on the left hand side here null value on the right hand side if it is not matching we will see in practically so that you will get clear idea fourth one is called full outer join the same way first table is left table the second table is right table you can go for right table so this is what the lift table we have right table if you are going for full outer so matching non-matching record from both the tables matching non-matching record from both the tables so this is what full outer chime so we'll be getting full outer chime these are all the four types of joints are available in oracle inner join left outer right outer and full outer we will see the joints practically consider these two tables this is what the customer table this is what the country table in customer table i have customer related data like customer id customer name mobile number email country id in country table i have country informations like for this particular country what is a country name so if i want to know from which country this customer is belongs to then you can say that this is country 203 be checking here 203 what is this country then 203 203 are matching and then we can say he is from singapore right this customer 202 202 is nothing but usa this customer is from usa that's what i can join it and then i can get so how did i join we can take customer dot country id and then country dot country id the column name might be different column name might be different column data type should be matching in order to join i need to have a common column so one column two columns more than two columns also it's possible but the column should be matching here country ad country id you can have another column also mobile number mobile number or country information zip code zip code so that's also we can do some join but as of now we are seeing only one column joins this is the join condition customer dot country equal to country dot country d so we are connecting it so now if you if you take the customer table and country table if i do inner join what could be the result so i can take customer data so customer i want to know each customer's details and their country details i want to know then i can go for the inner join so if you do inner join see all the records so we can take this particular record country id also we can take this particular record we can take the country id you can make it here and then country name 200 is nothing but india we can take 200 india 204 is nothing but uk so i can you i can take uk here 202 is nothing but usa so you can take 2 usa here and 203 is singapore and take the singapore from here and next record like 1004 this record the country 205 so if i'm seeing here country a205 is not available so inner for inner join this record will get filtered out it will be opted out so it will not be populated in the inner join result so you can go for other records same way you can join it 200 it is india then 202 it's usa so this is what we'll be connecting so this is the result of inner join this is a result of inner join so for inner join we'll be getting the matching record from both the tables if you look at here 201 china is available on right hand side but the 201 country is not available in the customer table this record also will not be coming on the right hand side so matching record between both the tables that is what inner join will do what is left outer join so left outer join is nothing but irrespective of right table irrespective of right table we can take all the record from the left table so with respect to your right table we can take all the records from the table then so for each and every record we have to match my condition if it is 200 so i can put 200 what is a 200 200 is nothing but india 200 we can mention that india so you can fill all the 200 as india and 204 uk so you can take uk so 202 is usa nothing but singapore and singapore but 205 i do not have any value so it will be null so you'll be getting null value on the right hand side so this is what you'll be getting it if it is not matching you'll be getting null value on the right hand side if it is left outer side okay this is what you'll be getting at 202 is usa we can populate all the values so this is the resultant of left outer join okay so now we will go to the right outer join so what is the router right outer join we'll be getting all the records from the inner joint resultant we'll be getting all the resultant then for right outer join so you'll be getting all the record from the right table so right table we have 201 china so this is what we have and here you'll be getting null value on the left hand side you'll be getting null value why because i do not have any customer details for this particular country so this is what you'll be getting right out of time all the records on the right table if it is matching you'll be getting the value if it is not matching you'll be getting null value on the left hand side if it is full outer join then it's a matching non-matching record between both the tables so this is what you'll be getting the full outer join so resultant of loader joint here will be going with full sultan of louder joint so this is what the result inner join left outer right outer and floater so when will you go for inner join if i have matching column i will go for normally in the join only when will you go for left outer in order to get all the records from the left table i can go with the left outer chart then say same way you can if you want to get right hand side say for an example i want to know in in which country i do not have customer in which country i do not have customer i want to take two not one china we can do right outer join in addition to the right outer join we can make one condition like customer id is null okay you can make one more condition is customer id is null then this record alone will be getting it that is what we can take okay in this particular country i do not have any customer same way you can go for full outer join also matching non-matching record from both the tables this is what you can get for an example if i'm having a record like this instead of 205 i have two not one here all the record is matching with all the record on the right hand side okay record is matching with right hand side 200 is matching 204 it's available 202 is available 203 available 201 also it's available 200 200 to 200 everything is available we'll be getting this particular result 201 201 here and here we'll be getting china's country name so that this is for the inner joint result so this is the result for all other remaining joints left outer join also this is the same result right router also full outer also so why because all the record is getting matched that's what we'll be getting the record how to write the queries for this the same table i have created so i have given the script in the descriptions you can download the script from here in the descriptions you can practice it if you are finding any difficulties or any issues or any suggestions you can write your suggestions in the comments i will answer it so we can write join in oracle by using two methods normally we can write a join by using two methods one is implicit method another one is ansi method we will see all the two methods why because some if you are going for a real time project there might be so one of the developer writing queries in implicit method one of the writers might be writing in ansi method we should know all the two types of methods so that's what i have mentioned here first i have created a table here we can select the table select start from customer you can see here this is for the customer table same way we can do the select start from country we i have posted all the four select start from country we can have this table okay so now inserted all the five records so now we will write ansi method how to write the join queries we can use first implicit method by using implicit method how to write it you will see now okay implicit method and nancy method we will see one by one there are two methods are available we'll see one by one so implicit method is nothing but you can go to select column names this is what you can do the practice implicit method join is very simple if you follow this join is very simple select all the column names from first table first table is customer you can see here first table is customer okay comma second table second table is country country where we have to write the join condition by using where clause where customer dot country equal to country dot country d this is the join condition this is called join conditional this is called join condition so we have to write the joint condition after that you can make under condition filter conditions that we can write fulcrum filter conditions this is joint condition okay now after writing it you can you can select the column names always you have to select the column names customer dot customer dot customer id so instead of making every time customer dot customer id and everything i can i can alias it this table as a this k plus b so instead of customer you can make a dot this is called table layers we have already seen that in our previous session if you haven't watched please watch the previous session you will get all the stuff very clearly so a dot customer id then a dot customer name the same way you can mention the customer name then mobile number so the all this column from a a table so you can mention that a dot mobile number comma a dot email comma a dot country d so all the columns from a table come on we can go for b dot so i need b dot no need to take country id so country we have already taken from the left table we can go for b dot country name so we can mention b dot country name so this is what finally you no need to mention the comma here just you can mention the so no need to mention the comma here so this is what the implicit method join so after writing it here also you can change the queries a dot country d here also you can mention that p dot country d here also we have to change the conditions then you can copy this go here you can execute the statement we can get the result so if you look at here so for all the customers we are getting the country name if i want to get only customers are from india then you have to mention that and b dot country underscore name equal to india so case sensitive you have to make in this way only yeah this is what only india we have three customers this is what i can find out by joining the columns so this is called inner join this is called inner join implicit method in implicit method we are written the inner join okay how to do the inner join and ansi method see ansi method is very simple ansi method is very simple we can copy this the same way we can go to this same way we can up to here it is simple the same way we have right select all the column names select all the column names from first table inner join or join join or inner join inner join or join we can write inner join second table is country b then you have to use the keyword called on so there we will be using bad class here we have to use on so on a dot country id common column is a dot country d equal to b dot country underscore id this is what we have to write and this is called ansi method so normally which method will be used most of the time in real time project most of the time we'll be using ansi method performance wise the ansi method is better one you can see the same result you'll be getting either from ansi method or implicit method we'll be writing it see what is the difference here from first table comma second table where class here first table inner join or join second table on okay you have to use on if i want to know any filter condition then you have to use where class here so again where plus so here we might be using that under class right here we have to use bar class we can put this and then you can select it it will select only customers are from india so this is what we can write and see method for indesign okay how to write left outer join so left outer join means see all the record from the left table matching record from the right table so in order to write the left table in implicit method in implicit method you have to make this is the join condition right in join condition the right hand side we have to make plus symbol on the bracket if you are making this is the syntax if you are making plus symbol the bracket on the right hand side of the join condition it will become a left outer join left outer join if you look at here all the record from the left table matching record on the right table if i if it is not matching will be getting null value on the right hand side so this is what left outer join okay how will you write left outer join here so simply we can write left join or left outer join both are equal you can see here select all the column names from first table left to join second table on condition you can mention that on condition just time executing it the same result you'll be getting it all the record from the left table matching record from the right table if it is not matching null value on the right hand side this is what left outer join and c method how to write right outer join in implicit method very simple you can take the plus symbol on the left hand side of the join condition this will become a right outer chime all the record from the right table and see all the record from the right table you can get all value on the left hand side if it is not matching okay you can see here country id you are getting null value right country id you are getting null value but if i want to know country id for this 201 for china then instead of taking country id from a table instead of taking country id from this table so 201 it will not be available here that's what we are getting null value so instead of taking country id from a table take country id from b table so if you are taking that this is what you are getting it i want to know how many countries i do not have any customers then i can make one more condition here and a dot customer id is null if you take it this way you can find 201 china i do not have any customs if any other country is also there it will be populated here you can remove the other columns you can just remove other columns okay this is what you can use the use of right outer chain okay this is what the use of right outer chair so how to write in ansi method instead of left join we have to make right join right child simply right join be getting all the records from the right table matching record from the left table so you can have here v dot country this is what you'll be getting right outer join okay how to write full outer join can we write full outer join plus symbol both both the side no it will not work okay in order to write full outer join we have to go with set operations here first of all i will do left outer join union okay union will remove the duplicate union right outer join union right outer chain it will remove the duplicate and this is the way we will write the full outer join if you go and check here this is what the floater join it will give you matching non-matching record from both the tables you can look at here right hand side also you are getting null value left hand side also we are getting values so this is what full load or join but in ansi method is very simple instead of right you can make full full join or full outer join both are equal so this is the fourth type of join we have to understand clearly that okay i have given all the queries just to create the table you can watch this video and you can get the details and we will go to the another tables also we can have two tables three tables all this if you this is what i have given implicit join all the joins if you take the tables say for an example i have the three tables here so current customer city and country same way we can create look at here i have i have the shankar from here i want to know which country is from which country is from okay so in order to get the country details i cannot direct connection from customer table to country table i do not have any direct connection to this table i have to go via this particular city table so we can take customer dot city id equal to city.ctid firstly you have to join it you have to get this detailed region id then reach an id from city table okay region id from city table city.region id equal to country.com column name might be different data type should be matching so you can see here 2.12.1 so this customer is from china so what so this customer varies from this customer so one not two one or two is nothing but 200 200 is nothing but india so this is what i can get join this is the result of inner join so i want to know each and every customer see details on country details then i have to go for joining so the same way in our in our hr schema we do have three tables we know already we do have three tables like we have table called employees table departments table location stable how these tables are related so employees we have the department id in department stable we have a department id so employees are department id equal to department's not department id you can join it you can get the department name from here and we can go to the location id we can take the location id from here okay this particular department where it is located so location id 2005 sorry location id you can get this id then location id from here location table i can say is from chennai india right so that's what i can do the joints so for joining you need to have that common column same way in our table also in our hr schema now schema we have three tables select star from employees we can see the employees table i have the employees table all the employee details are available select start from departments table departments table if you haven't have this but if you do not have this particular environment oracle setup i have posted already video you can also check the description i have given the oracle installation step you can install it any other version and then you can practice it we are the two tables like employees table and departments stable so here we are the department id department name and location id and first of all we will do the joints on these two tables so how to do the joins so this is what joins simply first first two tables and joining it so select all the column names from first table employees e comma departments d where class e dot department id equal to d department id so this is called implicit method so why it is simply implicit method if you are seeing more than one table in the from class then that is a join more than one table in the from class with the comma that means it's a join and also you are seeing the var class without any plus symbol that's what it's a inner join inner join in the join of two tables employees and departments okay this one is again inner join of two tables employees table and department stable here we are writing inner join this is called nc method this is called ansi method if you look at here the same result you'll be getting it you're getting same result we can get it here same result for each and every employees we can get the department name and location okay so this is what i can get left outer join yeah plus symbol on the right hand side right outer join so in the ansi method you can go for left outer join left join or left outer join both are equal so if you do left outer join or left join so you'll be getting all the record from the left table all the record from the left table if it is matching you'll be getting the value if it is not matching you'll be getting null value on the right hand side so for this particular employee there is no department id tag so for this department id in this employee table not packed with any id that's what we are getting null value on the department name okay this is what left outer join right outer join so plus symbol on the right hand side you can see here all the record from the right table you are getting it right they're getting it so what is the use of this right outer join so i want to know how many departments i do not have any employees how many departments i do not have any employees you can see here this department i do not have any employees these departments i do not have any employees sorry because here return said you are getting null value right so you can execute it and along with this you can use one more condition and e dot employee id is null okay employee id is is null then only this department names you are getting for all these departments i do not have any employee so i do not want these columns i can remove the e dot column names so that if they are asking an interview how to find all the record from the right table and matching the cut from the right left table this is what you can tell them okay if they are asking questions like this so you want to do all the records from the right table so you the record is there in write table not in left table so that is what you have to make the queries like this okay then we can go for full outer join full outer join full outer join is nothing but so this is what we can write for louder join as i have mentioned you can go for union also so full outer join is nothing but matching non-matching record from both the tables matching record non-matching record from both the tables this is what you'll be getting it okay next one is called so this is what full outer channel how to do the three tables join how to do three table join here so i have employees table departments table locations if i want to do three table join we can write select all the column names from three tables whatever the column we want we have to mention if it is employee table just time analyzing like ee departments table d location stability so how many tables are there you can mention all the tables in the from class this is called implicit method we have already seen that if i have one more table jobs we can make comma here jobs we can mention four tables five tables you can go for a number of tables here but the thing is you need to do the join condition you need to do the join condition here you need to do the join condition so you take all the columns from the tables from the table name and where class you can put that join condition this is filter condition that means i want to know how many employees are king from uk that's what so without joining i cannot find it how many employees are working from uk so without joining can i find no i cannot find so after joining it we can make one more condition here okay these many employees 35 employees are working in uk that is what i can get the details okay so how can i change this one into a ansi method see very simple first table inner join second table instead of where class you can go for on on class then you have to remove this okay first you have to do the first two table joints so more than two table you cannot join in the ansi method first table inner join second table on condition again you have to do the join okay inner join inner join third table on condition on condition you can remove the comma from here just i have copied inner join third table on condition here you can use where class where plus is for filter condition this is what the class you can use you can the same result you'll be getting it same result you will be getting it this is what i have tried if i have one more table like jobs how to do the join so here in a china again you have to go for join or indesign inner join jobs j on j dot job id okay you can go for java d equal to e dot java like this you can go for the join condition and more than two tables three tables four tables this is what you have to use inner join fourth table write the condition inner join put the table write the condition then this is what the where class is nothing but filter condition after joining it if you look at here after joining it i want to take only uk then i can write uk here pair class so this is what the four tablespike tables all this you can do it okay so you can practice it i have given this query just in our hr schema you can practice it then you'll be getting all the stuff very clearly so what is the meaning here so after joining it this is called the same way how i have mentioned we are getting country equal to ca and getting salary more than 10 000 so this country from l table country id from l table salary column from e table so you have to make sure that from which table you are taking that columns okay this is what joined and four tables joined inner sign okay four tables see you can take all the columns first you can take select all the columns from first table inner join second table on condition inner join third table on condition inner join fourth table on condition you can go for four table five table see here instead of writing table name here we have written on query itself so you can write this query itself department id department name location id from departments d so you can write sub curry also here so instead of writing the table name we can write that queries also this is what four tables five tables giant query you can see here just i'm right i'm joining it and i'm getting the country from united kingdom salary getting more than ten thousand dollars so this is what the query you will be getting it so this many customers right customers are from united kingdom and we are getting their salary these customers are getting salary more than ten thousand dollars so this is what you are trying from first table inner join second table second table instead of table i'm writing the query so here so if you write this is called subquery right subcuri so this resultant will be placed here inner join this one [Music] d will be taken as d you can take whatever the column from here d dot department id d dot department name d dot location id so that's what we have taken d dot and on class on join condition inner join or join both are equal join locations the third table on condition join fourth table unconditional join fifth table on condition you can go n number of tables here same way in implicit method also here also you have to go with implicit method also you have to go with so these are all the joint types we have four type of joints so we have seen four types of joints inner join left outer right outer floater i hope you are clear on this particular join so after practicing it i have given some exercises in the description itself kindly practice it so now we will go with cross join our cartesian product so what is the cross join or cartesian product if you look at here this is the this is the join here so here we are writing the join condition right if you don't write this join condition to become a cross joint so what it will do it will take one record from here since we haven't mentioned the join condition it will take this record it will join with all the records so one record here five record here eight the same way for eight records eight cross five you will be getting 40 records out of it if you forget to mention any join condition in the join query it will go for cartesian product okay that's what we have to understand what is cartesian product so here cartesian product is nothing but so this is what is this joint what is this joint now it's a inner joint right from first table comma second table where joint so i forgot to mention this join condition what will happen here i have one not seven record here i have one not seven record department stable i have 27 records you'll be getting two eight eight nine records so look at here i forgot to mention join condition then you'll be getting two eight eight nine records right see one record on the right hand side table it is connecting with all the records here then another record so take another record from the right hand side table so you look at here another marketing after administration you can go for marketing you do join with all the 107 so that is what you'll be getting the all the records okay how to do a cross join in this ansi method just you can mention that cross join okay cross join then you'll be getting that same result from first table you can do the cross join second table just you can execute it it will give you the cross joint result look at here same result you'll be getting it so this is what cross join will be getting it sometimes if i want to do if i want to load some data some dummy data into the table i will write insert into table name i can go for cross join cartesian product then dummy data i can load it into the table for more records it will create some millions of record i want to create then i can create in this way okay so this is what cross join will perform okay next one is called self join see what is the self join right if you look at here the self join so you can take this particular table say employees table so employee id is there for each and every employee there is some manager id tagged it so who is the manager of this particular employee for this employee manager is one not three so i want to know each and every employee is manager then i have to go for self join what is the subject why we are going for self join you take the manager id from here one not three on the same table you join it or not three and i can say alexander is manager of only right this is the way we can do it who is the manager of daniel then i can go with here so i can take hundred i can take one not eight so one not eight is nothing but nancy is manager of daniel so how to write query here same table we have to join it select column names from first table so i can take employees table employees e i can take e1 comma save employees table right hand side you consider okay employees table e2 where e1 dot manager id e1 dot manager id you take the manager id from here take the manager id from here e1 e1 dot manager id you take the e1 dot manager id here and on the same table you can join with employee so e2 dot employee underscored okay then i can go with e1 dot employee id employee underscore id comma e1 dot first name so i can take employee id and first name i can take so one simple hardcoded value reports to okay reports to then i can go with comma e2 dot employee id and first name e2 dot employee id and d2 dot first name you can remove this comma here just execute it it will give you this result but it is not in order i can go for order by one first column so neenah reports to stephen so lex reports to steven so all this you can see here daniel reports from nancy so you are getting all the results right correctly okay so why the 100 is not coming since it is a inner join it will not come if i want to get it instead of inner join i have to go with left outer join if i do left outer join he'll be getting steven so steven reports to null means he is the top most person he is not reporting to anybody so that's what you can see the same query how can i write in in the ansi method it's very simple again select all the column names from first table inner join right inner join or left outer join what is the way you want to do left to join so instead of that class here you can go for on class so remove this plus symbol here okay we have returned this so this is what it will give you the same result okay this is what the self join we can see the self join here so i have given all the notes kindly practice it so this is what the join will work these are all about equesign let us start with non-equine so non-eq join will it will in the join condition will be having greater than less than all the symbols so non-eq join is nothing but whenever we are making the joint condition if you are making any non-equal condition of the joint condition then it will become a non-equation if you consider this particular joint normally if you are not writing this article symbol this is equi-joint this is simply to join whenever the department id is matching with the department's table department id then we'll be getting it for each and every employees we are getting the department's name but whenever we are making non-equal right so here we are going for non-equity so it will it will give you more results so instead of 107 record it's like the same like a cartesian product but what is this non-equity say for an example department id 10 in the employees table so here we have the department id 10 and then here we have 20 departments table we have 20 and 30 so like this we have a lot of records so we made like non-eq right so not equal so this record alone it will not be populated so for this 10 this 20 is not equal so that 20 will it will be coming see look at here so this is what you'll be getting all the records for administration we'll be getting all the records again for marketing you'll be getting all the records one record it will not be matched so other than that you'll be getting other records so this is called non-equivalent normally we will not write uh more uh join in our real time project we will be writing only eq joints so this is what joins so kindly practice all the giants if you have any queries or any suggestions or if you need any modification please raise your questions through chat through comments i will respond to it thank you very much we will meet in the next session [Music] hi everyone welcome to nikkei academy in this series we are learning oracle sql completely in our today's session we are going to learn about aggregate functions in oracle we have seen six different sessions previously like first session we have seen introduction then ddl dml constraints all the single row functions select statement then joins we have seen this session is completely on aggregate functions so what is aggregate function whatever different aggregate functions are available so this aggregate functions are very much used in our sql statement so please pause the session without skipping it and that finally i have given some questions on aggregate functions so try to answer all the questions if you have any questions please post your questions in the comments i will respond to it so if you haven't subscribed the channel please subscribe the channel and click on the bell icon so that you will be getting all the notifications thank you once again let us start our session welcome to the session now today's session we are going to learn about aggregate function in sql so this aggregate function in sql is very much important for our day to day sql queries and informatica also so if you consider aggregate function we have different aggregate function one is minimum of okay this function will be used i mean enough so we have five different function max of sum of average of and count off so these are all the mainly used functions in aggregate function in sql okay so if you consider this particular table if you consider this particular student table you can see here student id student name year of study mathematics physics chemistry biology all the marks we have and i want to know so this is the table sometimes interview they will ask this type of questions okay they have given the table and they want they are asking like okay i want to find out each and every student total mark each and every student sum of mark if they are asking this way so can you find each and every student's total mark if they are asking this way row level then most of us will answer like okay we can go for some of mathematics physics comma chemistry comma in that way we will explain so if i am asking like a row level for each and every student total marks or average mark we should not go for aggregate function this is simple function normally if i ask okay this is a table if i have then i will go for the this is row level sum row level sum means you have to add plus so mathematics plus physics plus chemistry plus biology so that's what you can find the total marks in select statement itself you can able to find if i ask the average mark you have to make all the mark total and then you have to find divided by 4 so that's what average mark but this aggregate function right this aggregate function is column level okay so like this column level if i want to know the minimum of all of this mark okay the minimum of all of this mark is 72 right so this is the minimum of mark maximum of mark 98 okay 98 what is the sum of mark okay some 779 then our age of mark average of mark is 86.5 in mathematics what is the average mark so how many members how many students appeared for this exam nine members that's a counter so so remember whenever i'm doing the aggregate function if any null values are there so aggregate function will not consider the null values so if you ask account of chemistry it will give you only eight not nine okay even for average or some or anything it will not consider this null value so this is what we have okay just i want to create this table so i want to create this table and i have to find out the total marks on average how can i easily create a table and [Music] find out this value and aggregate function all this so you can go to this this particular database okay so we do not have table as of now just we can create so table table name i'm just creating a table so open bracket you can paste all the columns and this one is number so number of number of five or six i can give number of six student name year of study so i can go for number here so mathematics number physics it's a number data type chemistry yes the number data type biology also number data then finally we should not make the comma then close it then semicolon then ctrl enter so this will be created so table has been created if you go to the hr schema so we will load it data you load the data refresh and you can see here the table will be available here students table then what i will do i will go to data here so as of now we do not have any data so i will manually add add this data so it's like insert into i have nine records right so i'll go for so nine records then i will add okay so keep the cursor here paste it it will be pasted then commit so simply within a minute you can create the table the commit successfully you can see here commit successful then if you go and check here select star from the student then this particular select star from students this particular table has been created with the data okay so i will give this particular table creation so that you can make use of this okay so this is what the table i have all the data i have then select star from the table name students right so i have the select start from the student table you can see so this is what the data so i want to find out first we will go for each and every student's total mark so there is no function like this okay so some of them will tell like okay some of the students or some of the candidate in interview they will say okay i will go for some of this mathematics sum of mathematics come up physics okay remember this aggregate function will not take more than one column okay so aggregate function take will not take more than one column if you are going for like this then this is wrong okay this is wrong actually so we do not have any function like this okay any aggregate function aggregate function will not take more than one value so if i'm asking questions like row level you have to go for the row you have to add it the column you have to add it so here i'm just adding student id so each and every column i can take so in the select statement i can take each and every column like student id okay student name year of study mathematics physics chemistry then biology then i will add that sum we have to make this addition that's all so we have to make addition like this so you can always like total underscore marks total marks alias it so from students see here the total marks so here we are getting null value because it has null value on this so then you can go for nvl function so nvl so i will make one believe for nvl of chemistry so what is the total mark so nvl of chemistry comma zero so that means if null value then make it like a 0 envelope chemistry command 0 so you can see 272 now value has been calculated okay so this is what you will go for row level addition i want to go for row level average so our age marks average mark for each and every student so if interview they are asking like questions like this you have to go for this way only in informatica how will you do in informatica we'll go for expression transformation so in this particular scenario we will go for expression transformation this is what each and every student's average mark okay so you can go for so this is the way we will do for the question but now we will go for the what is the maximum mark in mathematics what is the maximum mark in mathematics i will go for select max of maths from students this is what you can go for maximum mark is 98 so what is the minimum mark so minimum mark some average count or count so what is the minimum work minimum mark is 72. so what is the count how many members attended max exam so 9 members then what is the sum sum of mathematics will give you all the sum okay so this is what sum of mathematics you have the 779 same way you are getting and what is the average so average of mathematics you can see 86.5 if you want only two digit you can go for round function we have already seen right so round off average of this one comma two it will give you a two digit decimal point if you do not want to two digit just to simply make round that's all so it will give you 87 so this is the average mark in mathematics so this is what you have to find out okay then the question is okay this is what average mark some mark even irrespective of number of records even if you have one lakh student or ten thousand student irrespective of number of student the aggregate function will give you only one result only one result okay we have two years here so first year and second year i want to group first year alone i want to find out maximum minimum sum i want to go for second year second year i want to go for what is the sum of our age of all this then i can go for group by so group by so here i have to make say for an example what is the sum of mark or average of mark in each year okay our maximum of mark in each year so how can i go for each year i want to go for then i can go for here group by so group by year of study if i take group by year of study then we have 94 98 i may not know okay so what is this particular year so whether it is a first year student mark or this is the second year student mark then i can select this one here first year student 94 is the maximum mark second year student 98 is the maximum mark so this is what i can find okay here you may ask question so i'm finding the maximum mark can i find who got this particular mark so maximum mark is 98 i want to know who got this maximum mark this way i want to find out student name no i cannot select it i cannot select okay this is the error we'll be getting so what is error so i cannot select non-aggregate column along with aggregate column okay this is what i'll be getting error okay then how can i find the student name can i go for so like this so here how how can we find this is non-aggregate column this is aggregate column we made it like a group by can i go for group by then so group by of student name so what will happen the query will work it's logically wrong group by student name some of most of the candidate will answer like this also okay group by student name i will go for if you make a group by student name all the names are unique names so it will not group by with any of the name it will not group it with any name so their name their marks will be getting it we are not going to get maximum arc person it's like a student name their marks we are selecting not maximum the highest student we are not taking okay if i want to take higher student either we have to go for rank or we have to go for sub query subquery we will see after some time so this is what we will go for the aggregate functions so if you look at here student from group by all this when will you go for group by so group by means we are grouping it the data and then finding the sum or average or count say for an example here i have the student gender so male and female so i want to find out so male candidate how many of them appeared for it for exam female candidate how many of them appear for the exam then i can go for yeah we can go for the count of student okay count of student group by gender so we have to group by so each and every group so whenever we are having like a keyword like vice okay gender wise country wise product wise transaction status wise so different okay this kind of wise keyword then we have to go for group by keyword okay whenever the interview they are asking okay what is the this is some some some of all this like a country wise what is the total city wise what is the total product wise how will you find then you can go for the group by column okay so group by column this is what we have to make group by i hope you are clear we will go with one more example so that you will get clear idea about all these functions okay we have in our hr schema we have a table called employees table select star from employees in this table also we will find if you take this select star from employees table in the employees table we have employee id first name last name email phone number higher date and job id salary all the columns are available then i want to find out what is the maximum salary i'm giving for the employee and how can i find what is the highest salary i'm giving max of salary so what is the highest salary max of salary from employees maximum salary is this is the one only one record we will be getting it so minimum of salary so what is the minimum of salary i am giving out of all the employees what is the minimum salary we are giving so 2100 what is the average of salary for each and every employee how much we are average we are giving okay seven eight to one okay you can go for round function to round off then how many employees we are giving the salary i can go for count of so you can make count of okay count of salary 107 employees you are giving the salary so what is the total sum of salary i'm giving for all the employees okay so we have one company organization so we have some 100 employees working under that particular company i want to find out okay month end how much total salary i am giving so this is what the total salary some salary i'm giving okay so in our particular employees table we have some departments different departments i want to go for each and every department so this is the sama salary okay each and every department how much i am giving i want to go for then i can go for each and every department department wise someone salary what will you do so we have to make group by so group by department underscore id so this is what we can find so this is what we will get it so we have 14 unique departments that's why each and every department we are getting but we may not know which department this particular salary which department right i can go for the department id you can select here so remember always if you want to select a non-aggregate column here so that column should be there in group by otherwise another way whatever the column we are making in group by that column alone you can select along with aggregated column okay so can i go for one more aggregate function here yes we can go for so department-wise sum of salary on count of employees so how many employees are we are giving count of star or count of employee id all this we can go for okay this is the department right so this is a department id so in this department id sum of salary this much and four employees we are giving so that's what we can we can find okay but we cannot make one more column here can i make job id here can i make job id here no i cannot make so you will be getting this particular error so we can select if you want to select a job id so that job id we should make here okay so each and every job in this particular department how much we are giving so that we are going for then we have to make job id here the same order we have to make job id comma department id so look at here in this particular job id the department id we have only 60 okay so only 60 at the department or you can go for this particular order by class okay in 110 department we have two job ids one is accountant and manager so accountant we are giving this much salary for managers we are giving this much salary only one employee okay then for 10th department one 90th department then hundred so like this each and every department we can go for okay in 50 of the department we do have three different java ids one is sh clerk st clerk st man manager so each and every department so 20 sorry each and every in this particular department each and every job id we have 20 members 20 20 and 5 members so this is the seller so like this we can find you can go for order by just we have made it like order by job id this is the way we can find okay this is the way we can find more than one column also but whatever the column we are here we are making so that particular column we have to make it here okay if you look at here this is what i want to go for department id in order then i can go for order by department id i can make order by department id it will make order by department id either you can go for order by department id or you can go for so some queries they should have written order by one or two or something one is nothing but first column so if you look at here one is nothing but first column so this is what okay intent the department this is the summer salary in 20th department job id we have made it like a group so this is the way if if you consider this 10th department we have one employee this is that salary 20 of the department we have two employees and this is a total salary 30th so then we are getting it then null also we are getting null value that means for one employee we do not have mentioned the department id in the table itself so that's why his salary is 7000 let's say if you are taking group by definitely this is also unique record right this is what you'll be getting it sometimes if you use group by the performance wise it will be better when compared to distinct say for an example i want to know how many distinct department ids are there or how many distinct job ids are there in the particular table if you look at here how many distinct unique department ids are there so unique 90 we have 60 we have 100 140 30 50 like that we have right so how many unique departments are there i can go for distinct department id right distinct department underscore id so how many are there we can choose here 14 all rows switched 14 you can make order by to be having all the departments along with null value the same thing you can achieve so in different way select department id okay so group by department type so if you use group by department id so what will you get group by department id each department it will make the group by and we are selecting the department id so this is also same these two queries are same sometimes if you are facing like performance issue on the distinct keyword then you can go for the group by so group by on the column and if you take the same column then you'll be getting this this will perform very easily okay so this is what you can use group by okay you may ask here if this is the way if i want to go for can i find who is getting this maximum salary highest salaried employee how can i get can i go for first name here no we have already seen right this scenario we have already seen the student table itself we cannot select in this way okay we cannot select this way either we have to go for we have to go for sub query or we have to go for rank function so here sub query means select we we can select max of salary right maximum salary this is the way we will select and we have to go for one more query here select star from employees start from star means all the columns where salary equal to equal to this salary i am passing okay so if you if you run this particular one this query alone inner query alone this will give you the salary then select star from employees where salary equal to this one i may get it right so this is what first highest salary so this is what you have to find out if somebody asked an interview you have to tell the second salvia or third highest salary that is different scenario that we can make it in a rank function itself but this is what i have to find out okay so if you look at here each and every department we found each and every department we found the sum of salary we found the sum of salary here right department-wise sum of salary we found okay so i want to make order so order by order by one one means first column will be order like this first column will be order you can see order by first some of these this much salary i'm giving out of this much salary for each and every department i'm giving like this if you add all the salary to be equal to this particular salary okay so if you look at here the the interviewer may ask different questions or you may get in real time different way also what are the departments i'm giving some of salary more than 50 000 so what are all the department i'm giving some more salary more than 50 000. this particular department i'm giving some of salary more than 50 000 right and this particular department and this particular department right so these three departments i'm giving salary more than fifty thousand so how can i find the query more than fifty thousand see remember whenever i want to filter out record whenever i want to filter out record from the group by resultant this is the group by resultant right so group by resultant then i have to go for having class i should not use var class see where class means if i i do not want this null value then i can go for where class here so where class here only we have right where department underscore id is not null if you are writing like this so this null value will not be available so where class after from class you have to write the word class okay then only you have to use the group by okay so out of this department i want to know which departments we are getting more than 50 000 salary then how can i find group by department id then i have to use the having class having is nothing but having class it is used to filter out the group by resultant so if i want to apply filter on group by resultant group by resultant then you can go for the having class so remember this so whenever you are getting scenario in this way you can use having always it will come with aggregate function having some salary so whatever the aggregate function so having classes always come with aggregate functionality having some of having count of having average of okay so having some more salary greater than 50 000 this is what you can get it so 50 000 more than 50 000 okay i want to know this is what you have to find out okay i want to know so what are all the department name i want to know the department name here which department what is 50 how do i know the department name so in order to know the department name i have to go for the department's table right so what is the department name 50 i have to go here i have to check 50 80 90. so 50 is nothing but shipping shipping department 80 is nothing but sales department 90 is nothing but executive department so these are all the three departments i have to take so how can i take in this query i have to go for giants right so we know already how to rule the joints if you look at here so how will you do the joints so select column names select column names from table name so employees e so i can always sit like e i can go for implicit method departments d so d where so here join condition and filter condition we will use it in pair class only e dot department underscore id equal to d dot department underscore id under so one more join condition right here we will write then so here i want to take the department name so department underscore name so since department name and department id is from two different table we have to write okay which depart which table this department id so i'm taking e table employee stable i'm taking the department id d table department name so you remember when whenever we are selecting the columns that column should be there in group by right so group by how many columns i have written only one column okay as of now only one column then so e dot department id is null so everywhere you have to write this way then if you are going to execute it will not be executed so group by you need to have so whenever i am selecting this column that column should be there in group byte right so you have to use this column in group by so department id comma this one having this is the way we have to write okay 50 shipping department this is the way we can write and if you look at here so what is the order of in sql statement if i am writing all the statements grouped by all this if i am writing first we have to write the from class okay from class then i have to write the var class then i have to use the group by okay group by then i have tries having class then i have to go for the order by okay so this is the order we have to execute in oracle so in oracle sql so this is the order if you are changing the order then query will not be executed so this is the way we are to find out all this okay if you consider this is the way we have to find out group by having order by all this in single function okay i hope you are clear if you look at this employees table so i have given some exercise here so you can find okay this exercises the answer so this is the employee skills i think i have created the table employees table let me check whether i have created the table employee employee name data but salary skill set city and country okay so table is already available i'm going to add all the data so how many records are there i have 26 so 26 record manually i will add it okay i will add it 26 record choose the state format ctrl c and control v will commit it okay so now it has been committed so we have created the table and loaded the data so in this table so some questions i have asked so you can can answer this question okay say for an example find the total number of employees it's very simple so you can also create this table you can do it so find the total number of employee select count of star from employee this is what i can find okay how many employees are there 26 employees are there 26 then find the city wise total number of employees city wise so each and every city how many employees are there so we have hyderabad bangalore pune right chennai we have so city wise new york we have so different cities we have city wise total number of employees so how can i find so we have seen already city wise we have then we have the group by so each and every city group by city then we can find so here i have to make city comma okay these many distinct 14 distinct cities are there so that's why we are getting this money okay each and every city each and every city how many employees are working this is the way okay if you want to make order by okay order by on the citywise then i can go for order by two order by two column then i can get ascending order or i can go for descending order then highest number of employees working in this particular city next guy yes next highest you can see this way right then find out total number of employees in each country this is also very simple each country i want to know i can instead of city i can go for country so country and here also country each country i can go for this one then this is what i can make okay in india 11 employees are working in usa 15 employees okay then find the total number of employees in india alone for india alone i want to find out so how can i find total number of employees we should not go for group by that's why i have given so we are given the value right india then you have to go for where class where country equal to india so how many employees are working so data's are case sensitive make sure that you are giving correctivity so 11 employees are working in india okay find the cities where more than three employees are working in india so waterless it is where more than three employees are working in india so if you look at here look at this particular one so this is what the city wise count right city-wise count if i'm asking one more question city-wise count i want only in india only for india india only i want to find out city for city-wise count then what will you do so here i will use where plus so where class before group by so where india group by city right so in india there are four different cities so these are all the employees are working in each city okay so here the question is different find the cities where more than three employees working in india so more than three employees are working in india we have only one city so how will you make so more than 30 after group by we have to use having okay having count of star greater than three only for india so if you do not want india then you can remove this var class it's for entire okay so this is what we have to find out then who is the youngest person then this is what you can order by order by on the date of birth you can take the top one you can go for these results subqueries only in which skill set highest number of employees are working so in which skill set so group by on the skill set we have to make so group by on the skill set count descending order so you can make skill set here and the order by this one descending so look at here so java we have six employees if you want to take top one yeah we have to go for sub query or we can go further rank rank we can take different way top one okay this is what you have to find out the is there any employee with the same name is there any employee with the same name i want to find out so if i have more number of employees i cannot find out right so easily i cannot find out is there any employee with same name so how will you find you have to group by on the employee name group by say for an example two neenahs are there two day bits are there group by count it the count is more than one yes the other duplicate employee name so we can go for select okay select the name of the column name is employee name okay select employee name comma count of star count of star from from employee table group by employee name okay having having count of star greater than one is there any name yeah we have one employee name so two times we have the same employee name we have if you go and check here so this john but employee id is different this is different john so one more john should be there so here we have right yeah so two employees are there that's what you can find all the questions so i will give you this particular document so please go through this document and find out all the questions okay and one more question is there any duplicate employee id so find out duplicate value on the particular column this one normal question right how will you find out duplicate on the column so this is the same way so whatever the column if i'm going to ask so that particular column we have to make it here okay so employ id so if i wan if i'm asking this okay there is no there is no duplicate on the employee id column okay employee comma count of star from employee group by on the employee id having count of star greater than 1 so this is the normal question they will ask in interview so can you find is there any duplicate on the particular column or particular table yes this this is the way you can find out so these are all about all the aggregate functions so please go through this i will give you all the documents and notes so please go through it and you can able to find so whenever they are asking questions related to aggregate function you can able to answer it okay skill set wise city wise country wise okay so youngest person that that means data birth minimum maximum so that we have to find out okay that we have to go for who is the youngest person who is the youngest person then how will you find this is one more question right so select youngest means maximum max of dvob from employee table right so which is the maximum this one who who is so we have to select select star from employee table where to be equal to we will be equal to this one so this is the way okay he is the youngest person eldest person minimum of enough okay 2014 so this is the way we have to find out they will ask different way so can you find out how many employees are there in female how many employees are there in mail so this is the way we have to make group byte don't use it where class where class means in mail how many employees are there that you can go for where gender equal to male so they have given the hard coded value right that's what you have to go for the where class and group by so i hope you are clear on this so kindly practice it if you have any queries you can ask me okay so this order by also definitely will be there in in our informatica i will tell you so that you will get clear idea so sorting algorithm sorting in sql order by class so how to make order by class in sql so whenever we are going for order by class so we have to make at last only if you look at here the employees table if you look at this employees table select star from employees i want to make order by on the particular column i want to make order by on salary then i can go for order by salary i can go for order by salary salary will be ordered based on the ascending order so here we have two thousand hundred two thousand two hundred this is the way it will it is ordering it then i want to make order by on the highest salary to list salary descending order then this is the way it will make the sending order so can i use order by on the on the instead of numeric column can i use it for a character column yes we can use order by on first name so first underscore name so how it will order so how it will order it will order based on the ascii value if you look at here first name this is what it is making order so if you look at here finally it is making john so why this john is at last so based on the ascii value this j is lower case right the j is lower case the lower case has key value will be more okay the ascii value of each and every so if you look at here select ascii of capital a from dual so you can see some value 65 right 65 select ascii of small a the lower case a will be getting 97 so this is what we are getting lower case in at last when we are making ascending order if you are going for descending order first name descending reason we are getting john at first so 97 is the first trade so that's what we are getting this lower case in first okay so order by on the first name column okay can i make the same order by on the date column yes we can make select start from employees order by higher date so we can make based on the hired date the employees are arranged higher date descending so latest higher so this is what but if you look at here two person joined on the same date if two persons hired on same date how will you order and that's the question even if you are going for any column salary i am making like descending order if two or three persons are getting the same salary how can i order if you look at here order by its already descending three employees are getting same seller okay so how can i order so order by salary only but how can i order these three implies internally if you are going for any the marks right mark wise if you are going for any uh cut off medical cut off or anything engineering cut off so mark wise we are going with total markers we will go for total mark then if so many of these students are getting same mark then we will go for the next mark mathematics physics chemistry biology and then we will go for the name wise also data but wise also so we have to make it like another column if salaries are same then next column we can go for salaries are same okay how can i order these four employees then i can go for another column order by salary can go for higher date order by salary descending first it will make order by salary and descending okay deciding order only if same salary then it will make order by on the higher date okay so higher date so this is what if you look at here 7th june 2002 then 17th august 2002 and here also ok 11 000 11 000 11 000 2002 2004 2007 so this is what it will go for okay four 2002 and six so this is what you can make order by on more than one column okay so how can i order if i'm if i'm having null values how can i order so how the null will be treated so order by commission percentage order by commission percentage remember whenever you have null value null will be treated as highest value so always so in sql null will be treated as highest value so if you make order by commission percentage this is the least value ascending order right least value then it is making ascending order 0.15 0.2 0.35 0.4 after that it is making null value okay if you are making ascending descending order then null value will be the first null value will be treated as highest value so remember this null treated as highest value okay same way first name employee id okay so here can i go with employee id order by yes order by employee id yes i can go with employee id order by employee id so it's already ordered by employee id only so i'm going with employee id yeah so this is the way employee id is unique here unique value so can i go with one more column here employee id comma first name can i go if i'm going like this second one will not be considered so because the employee id itself uniq if i have duplicate value on employee id then only it will go for the second column right so here same salary then only we go to the second column this is what you can go for so since this particular column is unique it will not consider the second column order by the same thing we will be having in informatica also okay so sometimes we will write this way also say for an example sometimes you will write in sql query we will write salary column is how many what is the number of column the order one two three four five six seven eighth column eighth column with the salary column then you can make column number order by eight descending that means eighth column will be order okay so if eighth column is same order we have to go for sixth column higher date so eight comma six eight descending comma six ascending so this is what so if some numbers are there so this is what you can go for the order by class so order by and group by so what is the difference so group by you know aggregate whenever we are doing the aggregate function then you can go for the group by column order by is nothing but sorting algorithm so whenever we want to do the sorting algorithm we can go for the order by so i hope you are clear so how the order by class will be working so always remember whenever you are writing sql queries this order by will be at the last okay so the last we should have the order by class i hope you are clear kindly practice it so that you will be getting all the stuff very clearly hi everyone welcome to nikeite academy we are learning oracle sql in this series in our today's session we are going to learn about subqueries we have already completed 7th session in sql if you haven't watched please check the description and watch full sql series as per the database now let us move on to today's agenda we are going to learn first what is single row sub-query what is multi-row sub-curry what is multi-column sub-curry and what is correlated subquery so for all the subqueries we are going to learn more examples for each and every subqueries and finally we are going to see how to find a duplicate record in the table and how to delete a duplicate record the table these subcurious concepts are very much used in our real time project so please watch the session without skipping it and practice it i have given all the queries in the descriptions you can download it and practice it if you haven't subscribed our channel please subscribe the channel and click on the bell icon so that you'll be getting all the notifications so let us start our session welcome to the session now today's session we are going to learn about sub queries so we have seen single row functions joints and aggregate functions today we are going to learn about subqueries so whenever we are writing any sql queries normally we used to write select statement right select star from the table select start from the table name and then we will write where class but subqueries is nothing but writing select and one more select statement inside this select statement so this query we will call it as subquery this query we will call it as subquery then this one will call it as outer query so this is outer query this is inner curry so normally any sub queries the inner query will be executed first then the outer query will be executed the outer query will be executed okay so this is what the sub query will be executed so we can have either select within select or we can write any dml operations like you you want to insert some record based on some select statement yes we can write this is also sub queries then update so we can take update and go for select and go for delete so inside we can go for select operations insert update delete all the dml operation buttons the inner query should be a select statement so inner query should be a select statement so this is what we will go for the sub queries so what where it will be used so we do have different types of subqueries on a single row subquery the first one is single row sub-query the second one is multi-row sub-query multi-row sub-query third one is multi-columns of fourth one is correlated sub curry fourth one is correlated sub query so these are all the four difference of queries are available in oracle or any other sql so what is that we will see one by one so first we will start with single rows of query so single row sub query it means whenever we are writing sub queries in this way the inner query should return only one row the inner inner query should return only one row that is called a single row support so if it is returning more than one row that will become multi-row supporti so we have to write in different way whenever we are writing single rows of curry here we will be placing some relational operators so that relational operator will be either it is equal to okay any different operators like you can go for equal symbol equal not equal so not equal you can go for this way or this way not equal and less than less than or equal to greater than greater than or equal to any of this symbol we can use it in our single row support we will write one single row sub query so here we know there is select statement select star from employees table if you consider this employees table if you consider this employees table so we do have many employees details i want to know who is getting highest salary we know so which is highest salary if i want to take the highest salary then we can take select tax of salary this is highest salary rate we already we have seen select max of salary from am price this is the highest salary we are taking but i want to know who is getting the salary so we know already that we cannot write any name here not aggregated column we cannot write so that we have seen already in our aggregate function itself so i want to know the first name other columns also then i can go for the sub queries so how can i go so select star from employees where salary equal to the salary right so this salary i can put so this is the salary right highest seller i can make this one if you look at here this person only this employee only is getting highest salary but i cannot write always hardcoded value instead of this value instead of this value i can make the query itself so today it's this is the highest salary tomorrow if any other salary is coming if any other salary is highest salary every time we cannot go and replace right so that's why we will keep the query itself here so what it will do so this query returns this value the highest salaried employee so this will return highest salary in the employee table so only one particular route will return maximum salary then we are equating so this is called subquery this is called single row sub curry single row sub query means the inner query returns only one row the inner query returns only one row it will not return more than one row if it is returning more than one row it will become multi-row support but here it is a inner single row support so now if you if you see here so this is a signal query you have to use only these relational operators only these relational operators we can use okay this is called single row sub query so if you look at here sub query the inner query can be executed without outer query the inner query can be executed without outer query but outer query cannot be executed without inner query can i execute no we cannot execute and up to here you can execute if you want you can execute up to here but we cannot execute this alone right this alone we cannot execute so this is called single row sub-query okay so one more one more thing i want to take who are all getting more than the average of seller so select the employee list who are getting more than the average of salary so first you will find what is the average of sell select average of salary from employees you can take coverage of salary here so who is getting more than the average of salary so i want to take right so select star from employees where salary this is the salary greater than or equal to who's getting more than or equal where salary greater than this particular query so we can use the bracket so this is called single rows of query so it will select the employees list we're all getting more than the average of selling so outrage salary we have seen it will select the employees list overall getting more than the average of salary so these are all single row subqueries okay so this is what we have to write single row suffering so what is multi-row support for an example for this scenario i want to take each and every department okay each and every department i want to take highest salaried employee each and every department i need to take highest salaried employee the one thing is we can go for rank analytical function we can write the other one is we can write in sub query itself i want to take each and every department then i can go for group by right so group by department id so here i will be using the group by department id can i select this particular inner query yes this inner query will be executed so for each and every department it will be executed with this is the value but i want to know who is getting this particular salary so i can go with if i am going to execute this is the error we will be getting it so what is error we are getting the error like single rows of query returns more than one row if you are getting this particular error now you can say because of this relational operator oracle assume that okay this will be a single row sub query oracle assumes that it will be a single row sub query so this will be your equal symbol not equal any operators relational operator if you are using so this will become a single row sub query if i want to take all the records then i have to use multi-row for this i have to go with multi-rows of curry so how can i use multi-row sub-curry so multi-row sub-curry means instead of relational operator you have to use the keyword called in norton so multi-row sub-query if i want to use we are to go within not in greater than any less than any or all so this all or any always will come along with the relational operator so in i can go with so what is in here instead of equal symbol i can go with ins in here so instead of equal i can i have to go with in so why i am going within so this inner query returns more than one row it returns more than one row right so how many those 14 rows it is returning if it is returning with more than one row then i have to go with in or not in or any or all okay if i am going to select so this is what we are selecting okay so what is a use case of this particular query we have to select each and every department maximum salaried employee the highest salaried employee for each and every department if you look at here then the department it is taking one record 20 department taking one record third year the department yes one 40th department one then 50th department or query is returning two records 6500 also highest salaried employee 8200 also highest salary implied why it is returning six thousand five hundred right so six thousand five hundred is highest salary for this inner coin so that's the reason since we are using in keyword who are all getting this particular salary it is returning look at here who are all getting this particular salary it is returning that's not our use case our use cases for this particular department we have to take so this is something like some logical error then how can we solve this so instead of writing like this we can select the department id also select with department id comma max salary department id comma max of salary from employees group by now if you select this particular inner query it will select two different column so multi and multi-column so multiple row also multiple column also it is returning then if i'm going to run you will get error so i am getting error it's showing like too many values so why it is too many values instead of selecting this way so inner query is returning two different columns but here outer query we are equating with only one column we have to equate with same number of columns here so department id comma salary if i am going to run this also it will not be executed whenever we are writing this var class so make sure that we are making that within the bracket okay within the bracket we are right if i'm going to execute now you can get the correct value so each and every department you are getting highest salaried employee so only one record we are taking if two persons are getting same salary that's okay we can take two two employees list so here if you look at here so this inner query is returning multiple column and multiple row multiple column is two columns and the multiple rows we are returning right so this particular query we are passing to the outer query that means this is called multi-row multi-column sub-query if you look at this above query what we have seen this query will give you only the logical error right the logical error it will give you logical error who are all getting this particular salary it will pick the employees from here outer core this is wrong actually this particular scenario this is wrong actually this query is wrong will give you the wrong result so for that we are going for the multi-column multi-row i hope you are clear if you consider these four statement select star from employees per department id greater than any of this value so i want to select the employees list who are all working greater than any of these departments that's i mean so where department is greater than any of this value i am going to run this outer query if you look at here what is the var class right so this should be your inner query in the query resultant i have given as hard coded value you can write select department id from this table you can write some based on some condition you can take department equal to this one but here i am writing 30 60 90s some hardcoded value if you select select the departments select this employees table if you take 90 department id 90 where department id is greater than any of this value greater than any of this value yes 90 is greater than 30 one of this value right yes we can write this way and then 90 yes 9 60 is greater than any of the value yes 60 is greater than 30 100 greater than all of the value the same way we can go with but if i have 20 20 is greater than any of this value no 20 is not greater than any of the value so that's why the 10 and 20 10 20 30 these three departments you will not get it so from 40 you will be getting it so select the employees where the department is greater than any of this value any one of the value so instead of writing this 30 60 90 we can go for sub query also so this is the actual query say for example here we are writing select department id department underscore id from departments where some condition i'm writing okay so you assume that this is inner query where department name in to explain you i am writing this way this inner query will give you the department id 30 60 or 90 so that's what i have written here so this is in this is sub query right if you are going to execute this query this query will give you from this so this is what i am i'm writing this sub query okay less than any of this value select star from employees where department id less than any of this value 10 is less than any of this value yes 20 yes 30 yes up to 80 of the department will be getting the record from 10 to up to 80 we will be getting it so 80 is less than 90 so that's why less than any of this value select star from employees where department id less than all of this value has done all of this value so 10 is less than all of the value yes 20 is less than all of the value yes 30 no so 10 and 20 only you'll be getting it then select star from employees where department id is greater than all of this value that means greater than all of this value from 100 will be getting it some scenario if if the writing if you are getting use case in this way you can go for any are all so this is this is what you can go for the any are all statement okay i hope you are clear on any and all we will see more example in our single row subqueries so first one whose employee job is the same as job of stephen so if you look at here first we will check what is this job of stephen okay first we will select select start from employees if you check here so we have the job ids here in our employees table and what is this job for steven so we'll check where first name equal to stefan right the datas are case sensitive so we have to make sure whether we are making the first name very correctly so if it is stiff and s then we will not get anything lowercase the case sensitive so make sure that we are making the correct one or we are to make with upper of lower the stefan job is st underscore clerk the question is whose employee job is is same job as stefan so who are all working as st clerk that's a direct question so we can take this is the job id right we can take only job id from here only job id from here job id and we can go with sub queries select star from employees where job underscore id equal to so this particular job id okay you can see here so this is the job id right so we can get the job ids so who are all working as st underscore clerk we'll be getting all the employees okay this is what the job id how many record this inner core is returning only one row this is called a single row sub query okay say for an example so here we can see one more example if you look at here i'm going for james going for the name of employees james so here i'm getting two different record two different james are there for this we are getting two records right two records so but both are st underscore clerk if i'm going to directly write this particular query this cannot be executed so we are getting the error like single row sub query returns more than one row so as we have seen whenever we are making equal symbol then oracle assume that it's a single row sub query but inner query how many records we are getting more than one row two james are there that's why more in one row if i want to make this particular query in single row query itself single row sub query itself then i can select distinct job id if i am going to use distinct job id we'll be getting only one job for this particular scenario for this particular scenario the results will be getting one and then we can use it the record we are getting now same as t clerk we are getting right say for an example some other jobs some other record we have in this particular table we have some other name so i am going to select one more record with steven so if you look at this particular query for this scenario for this particular query i am getting two different job ids can i make distinct job id again if i am going to use this again i am going to get two different two different records only two different records we are going to get it so even though i'm going to make distinct so definitely i'll be getting two different records how can i select so for this scenario see this is what we'll be getting it definitely will be getting two different even though we are making distinct we are getting two different records so that's why single row sub query if you use equal symbol we'll make single row software for this scenario we have to go with multi row sub query so how can i use multi multi-row subquery so instead of equal symbol i have to go with in okay so in keyword so in we know already how to use it so in is nothing but we can go for multi-row subcode multi row means the inside query we are returning more than one row so for that we have to use in if you look at here ad underscore precedent sd underscore clerk so these two different jobs we are getting it so this is what we have to use all the queries i have given here kindly practice it okay the next question is so whose salary is more than the maximum salary of the sales manager okay so first of all we need to get the maximum salary of the sales manager so how can i get sales manager if you look at here select star from employees select start from employees do we have any sales manager here so we do not have any directly the sales manager how can i get it the sales manager i can go and select in jobs table in hr schema the other jobs table go and check here jobs table where if you look at here jobs table so this is a job id this is a job description job title so we will take pair job title equal to where job underscore title equal to the sales manager reuse so what is the job id here i am getting a c underscore man so for the sales manager i have to use sa man here so i have to use job id job underscore id select job id from josh where job title equal to sales manager so this will return job id as a man so where i have to pass so this one to here okay maximum salary of the sales manager so what is the maximum salary of the sales manager star from employees select star from employees where job id equal to this particular query i can use this query will give you who are all working as sales manager if you look at here overall working is sales manager what is the question whose salary is more than the maximum salary of sales manager what is the maximum salary of sales manager 50 000 so here i have to get the maximum salary so max of salary so max of salary so i can get maximum salary so 50 000 who is getting more than the salary right who is getting more than the salary whose salary is more than the maximum salary of the job is sales manager okay so you can go with select star from employees where salary greater than where salary greater than this particular query we have to use we have to close it so we have written three different queries here so this is inner corey this resultant will be passed to this this outer query this resultant will be passed to the outer query so this is what nested subquery so this person is the answer for this question whose salary is more than the maximum salary of the job is sales manager so nested sub query okay next question is whose employee job is same as job id clm okay so first we will go with this one whose employee job is same as job of ellen this query will give you right select job id from employees where first name equal to ln so what is the java id of ellen s a underscore rip so this will give you the first answer s underscore rep and who are earning salary more than the ellen seller so i have to find out what is ellen's salary select salary from employees where first name equal to ln so this is two different condition so who is getting who are all working as a say underscore rep and getting more than the 11 000 that's what the question right so if i'm going to execute we'll be getting okay this particular employee is the answer whose employee job is same as ellen that lisa's job is same as ellen say underscore rep and getting salary more than ellen salary so ellen's salary is 11 000. this salary is 11500 so that's what we can write so this is also one of the under condition we are writing here this is also one of the sub queries here so we can make use of the subquery okay next question display the senior employee among all the employees display the senior employee so if you look at here in employees table select start from employees table select start from employees table if you look at this employees table we have the column called higher date if you make order by on the higher date so we can see this particular person joined first and the next person so he is the senior member so we can go for ranking on this particular column and take the top one in sub query how can i take so first we will take the minimum of higher date so minimum of higher date is the first higher date right 13th jan 2001 who are all joined on this particular date we have to take that's a senior member right so who are all joined on this particular date select minimum of higher date from employees that particular higher date equal to in employees table if you look at here the same answer we are getting it so lex is the senior member among all the employees next question find the second highest salary from the employees table okay so this is what the question so if you look at the question find the second highest salary of the employee table this is the question so first of all we will find what is the first highest salary okay the question is second highest salary only not second highest salaried employee so first highest salary is this one and the second highest salary so whatever the next highest salary i'm asking so first we will take the highest salary here first we will take the highest salary here and which sees the less than this particular salary if you look at here the maximum salary is this one select salary from employees where salary less than this particular query where salary less than this particular query i can make this way to understand better so these are all the less than of this maximum seller maximum salary is this much less than the maximum survey among all the salary which is the next highest one if you go with next highest one is 50 000 among all this we are going to take maximum so that will be your second highest salary right so that's what instead of taking salary i'm taking salary maximum salary second highest salary select max of salary from employees this salary will be passed to outer query select maximum salary among all this where salary less than this particular salary where less than this particular salary this will give you the second highest salary i want to know if i'm asking second highest salaried employee then who is getting the salary for that person right you can go for what is the salary here 50 000 who is getting this salary so we will take one more query select start from employees pair salary equal to this particular query we have to use if you look at here then this is what the second highest salaried employee in the employees table so just to practice it you can able to understand clearly the next example sum of salary of jobs if the sum of salary of the jobs are more than the sum of salary of job is clerk first of all we have to find out more than the sum of salary of the job ideas clerk so how can i find job ideas clerk so do we have any directly clerk so how many distinct clock is there if you look at here select star from employees we have different clerks actually so we have st clerk we have a sketch clerk so some different clicks is there right so among all the clerk what is the samoa salary sum of salary of the jobs clerk so how can i take all the clutch jobs so we can go with job id like job id like percentage clerk this one sum of this one so what is the salary 1 lakh 33 900 this is the sum of salary of the clerk more than the sum of salary of jobs what is the sum of salary of chops sum of salary of chops if you look at here sum of salary of chops if the sum of salary of job are more than okay sum of salary of jobs first you will find based on the job id these are all the sum of jobs if you look at here sum of jobs so what is the more than this particular charge more than this particular salary this job right sc underscore rip among all the jobs each and every job id each and every destination this is the job id sum of salary among all the sum of salary what is the maximum of salary this particular job that's a question sum of salary of jobs if the sum of salary of jobs are more than so if i am going to run this we'll get it so this is the group by resultant right so this is the group by resultant from the group by resultant if i want to select this particular salary then i have to use having class we have seen this particular query in our aggregate function if you haven't watched our create function please watch the previous session day 7 session so this is the answer for this question then we will go to this discussion select the departments where no employees are working select the departments where no employees are working so if you consider particular two queries if you take employees table employees table the employees are working from 90 60 100 30 right 50 and 70 all this employees are working this particular department but how many departments are there in my department's table from 10 to 270 so for all the departments whether the employees are working no so up to 10 to 120 or something employees are working so i i have to select so by manually comparing we cannot select right if i have more data so we can write some queries so how can i write see here so how many distinct departments are there in the employees table right so select department id so i'm going to select department id from employees this way i'm going to write so how many departments are there these many departments employees are working it is repeating if i want to select only unique records i can go for the distinct right so distinct distinct department id from employees okay how many departments we have from 10 to 20 30 these many departments employees are working then 120 150 up to 270 i have to take right select the departments we can take this way select the departments where department id department id not in not in this particular list am i correct so not in this value if i'm going to select what will happen not in this value if i'm going to select i'm not getting any value so why if you look at here select department id from employees okay so this this is giving value select star from departments where department id not in so not in we have some value right so we have some value why we are not getting any value so remember whenever we are writing sub queries so remember this option whenever we are writing sub queries whatever the column we are selecting here so that particular column should not contain any null value okay if you have any null value while equating with null values with outside the outer query you will not get any record so to avoid that we are to go with we are to remove the null value from the inner query where department id department underscore id is not null where department id is not null so why we are writing if you look at here this indar query is returning null values because of this null value this outer query is returning null value all the values are null so that's why we are removing the null value by using department id is not null see here from 10 to 140 we are writing then i am going to execute it so now you are getting it so 120 yeah 150 160 this is what i'm getting so this is called sub queries okay this is called subcuri right this is inner corey inner curry will be executed without outer query but outer query will not be executed without inner query remember this okay the same same queries we can write right outer join also what is outer join for an example i am going to select employees table here employee stable va here we have the data so how many datas are there so employees are from these departments in departments table i have department id from 1 to 270 for this scenario can i go with right outer join yes what the right outer join will do all the record from the right table all the record from the right table matching record from the left table up to here it'll you'll get matched up to 110 130 140. so up to here it will be matching right for this value it will get matched for these values you'll get null value on the left hand side correct you will write now then i will show you select how will you write join select all the column names from first table first table i'm going with the employees table i can always sit like e right outer join so i will right select all the column names from first table employees right join you can go for right outer join or right join both are equal right join departments d on e dot department id equal to d dot department underscore id why i am writing joining here the above query we can write in join also e dot employee id so what is the result we will be getting see for some of the employees we are getting null values right why we are getting null values here so you can take e dot star also e dot star means all the columns from employees table so employee stable we are taking all the columns if you look at here from 100 we are getting department name but some of the values so here we are getting the value on the right hand side we are getting the values on the right hand side but null value on the left hand side right null value on the left hand side if i want to take this record alone this record alone what should i do so i can put where class where e dot employee underscore id is so this is what so no need to take all the columns if i'm going to execute same result 14 records so here also 14 right here also 14 records 120 150 160 same result you'll be getting so based on the scenario we have to go for write out rejoin our left outer jack how will you write the same query in in implicit method same way here you'll go with employees table comma department stable instead of on will go for where class so here you will use und this is inner joint result so for right outer join this is the way we are right so if you look at here same result 14 records we are getting it okay so this is what single row sub query multi row sub query multi column sub query then joins also sometimes instead of going for sub queries we can write joins also okay i hope you are clear on all the three different uh sub queries now we will go to the correlated subquery so correlated subquery means it's a co-related okay it's a co-related that's a meaning of correlated sub curry if you look at here the inner query is related to the outer query outer query is related to inner query so how it will be executed for every one record for every one record in outer query it will execute the inner query okay for every one record in outer query it will execute the inner query so if you consider this particular scenario this particular two examples find the employees having at least one person reporting under him okay this can be achieved by using normal query also find the employee name having at least one person reporting under him so overall having at least one person so select star from employees where employee id in so i'm going to take manager id so this is distinct manager id so that means if you consider this employees table for this particular employee who is manager one not three is the manager right or not three is the manager then david or not three then for this employee 1 4 this employee 100 is the manager so to take this particular these are all the manager id right so at least one person should be there for so that's why i am taking the inner query select manager id from employees group by manager ide so i am taking that we are getting this value right so here i am taking manager id here i am taking employee that's why no need to put this null value so we can take so this is what we are taking it clear these are all the employees at least one particular employee is reporting under okay the same can be achieved by using this is normal query only this is inner curry this one is outer query but correlated sub query means select star from employees a okay so i am taking this as a where 1 is less than or equal to we are taking so if you look at here this cannot be executed select count of star so what is this count of star will return count of star will give you the count if i'm going to select one yes one means one will be equal to one it will return that particular record so it will take this particular record you take this particular record first record for every outer record it will be executed inner core you take outer query first record to the sub this particular query select count of star from employees same table i am taking as b pair b dot manager id equal to a dot employee id you remember the same query we have did it like self join right b dot manager id so this manager id equal to this employee id who is manager of this particular employee so we'll take manager id equal to employee right employee that's what we will write here so this will select okay for this particular employee this will select manager id which is not equal so it will not select any any value right for this particular record it will not select any value okay this particular record so how many record one it will be selected one will be selected right so that one will be getting here if it is matching this 100 this 100 is matching this query will be returned so that's what the correlated support is returning the values okay so this is what the correlated sub-query so execute the inner query using the values i can give you more examples also i will put one session on correlated sub query separately so that you can understand very clearly the correlated sub query so these are all the sub queries it's available in our oracle database if i have duplicate record on the table how can i identify the duplicate record if you consider select star from employees underscore 1 if you consider this particular table whether this particular table contains duplicate record is there any duplicate record on employee id how can i find so i will make yes this way if you look at here is there any duplicate record on the employee id yes we have duplicate record right we have a duplicate record one not four and one not five is a duplicate record so i have to identify so here very less number of records are there so we can easily find out it has duplicate record but in real time project we may have huge volume of record so that time we cannot write we cannot go and check manually so how can i write the query so this is aggregate function itself we have seen select employee id this column only whichever the column we have duplicate so that particular column you can select select employee id from employees underscore one table employs underscore one table group by the particular column the employee id this is what we have to select right we have seen this already in our aggregate function itself so employee stable we will not have any duplicate but employees underscore one yes we'll be having a duplicate employee table is higher employees table has primary key that's why we will not have any duplicate so this is what the employee id we are getting but this is what distinct employee id we are selecting it will not show you any any duplicate this is equal to select distinct employee so distinct employee id if you are selecting whether it will show you the duplicate no it will select only distinct but if i want to select duplicate record if i want to select duplicate record you can go with you can take count here count of star how much is the count for each and every employee id so these two are duplicate right whichever the count is greater than one then this is the duplicate how can i filter out on the group by resultant we have to use having having count of star greater than one we found that okay we found there is two records are there in duplicate we found okay the other two different records are having duplicate or not four and one not five it has duplicate yes i have to remove out of two i have to remove only one out of two we have to remove only one record this is wrong actually we should not give the same employee id for two records out of two records we have to select only one record can i go like this select or delete from table name delete from employees table employees underscore one where employee id employee id in shall i go like this what will happen can i go like this no i cannot go right so here we have that two records two columns it will not be selected we have to select only employee id okay whichever the employee id here i have if i'm going to delete it what will happen the two records will get deleted one not four we have two or not five we have two all the two will get deleted right all the two will get deleted four rows got deleted we should not go in this way what can we do i can go for rollback can i do roll back yes delete statement you can go roll back so now if you go and check here yes you'll be getting i should not go like this i have to select only one record out of it only one record out of it how can i select deleting a duplicate record how to find a duplicate record so this is what we found so 146 we have duplicate 205 we have duplicate then we found like this okay count is two here count is three years whichever the count is greater than one we have taken so that's what but i have to delete the duplicate record so how can i delete the duplicate record so this is one way so deleting duplicate record in oracle we can do six five to six ways so this is one way so how can i delete it in oracle each and every record will have unique row id and number also row number row number is nothing but each and every record will have the row number one first row second row third row this is what if i'm going to delete this particular record as of now this record is having five row number if i'm going to delete this particular record this will become 5 this 146 will become 5 so row number will change at any time but row id will not change at any time okay at any cost this row id will not it will not be changed if you look at here in our employees one table it implies one table can i take row id row number yes if you look at here select row number row number row num comma row id from employees underscore one if i select it for each and every record we are getting this row number and row id this is what we are getting for each and every record i have to select we cannot take all the other columns rates can it take star gear no i cannot select star here if i want to select all the columns we have to go for tableaus so here i can use table alias here then i can i can select no if you look at here each and every record is having unique row id so even for one not four also if you look at here okay we will make order by order by third column will make order by three you can see this these two are duplicate right these two are duplicate but even though it is duplicate the row id will be different row number yes it's different only but row id will be different definitely so each and every record unique row id so with this row id we can delete so how can i delete we will keep one row id for each and every record how can i take one row id can i go with the max of row id from employees underscore one so what it will do implies underscore one max of row id means it will select maximum one group by group by employee so for each and every employee for each and every employee i am taking maximum one if i am going to take maximum one only one record is there maximum or minimum anything you can go for only one only one pro id so out of these two row id it will take max overlord out of these three it will take maximum one right i will not select this one that green color whatever i have mentioned it is a unique row id we can leave this record unique row id record we can delete other than this right whatever i have mentioned with other colors we can delete these records correct how can i take unique records or id max of row id from employees one group by employee you can you can see how many records we are getting one not seven out of 1.9 so we have to keep these unique records and delete other records how can we do it so delete from subquery right we can go for delete from table name implies underscore one where row id not in where row id not in this one row id not in this particular that's what you can see now two rows only deleted two rows only deleted right if you go and check here it will not give you any result having count of star greater than one previously we were getting two records right now we are not getting any record that means there is no duplicate record in the table there is no duplicate record in the table in the column there is no duplicate it has been deleted you can see here i'm going to make order by there is only one record and it has deleted other records okay so this is what we can go with sub queries this is also sub query only so delete from table name this is also sub query only the inner query will be executed then outer query will be executed so we can go with sub queries this is the way so i will take separate i will i will post separate session for sub queries the correlated sub queries and something like with the class so we'll be writing joins with statement right so temporary table we'll be creating temporary table so that particular table we can make it like some a a here select star from a so that is also possible that i will take after some time i will take this concept in separate session i hope you are clear on this sub queries so kindly practice it i will give you all the whatever here we have practice i will give you all the statements practice it you have to practice hi everyone welcome to nikkei academy in this series we are learning oracle sql completely now today's session we are going to learn about set operators so set operators we have union union all intersect minus we are going to see everything clearly and we will have practical sessions and before the set operators we have completed eight sessions on sql basic so if you haven't watched these eight sessions please watch it i have given the notes in the descriptions please check the description if you haven't subscribed the channel please subscribe the channel and click on the bell icon you'll be getting all the notifications thank you for watching let us begin our session welcome now today's session we are going to learn about set operators so set operators is very much used in our real time projects wherever we have same structure table if i want to combine the row level data then i can go for the set operators if you consider these two table customer one and customer two customer one i have some customer list list of data and customer 2 i have some list of data and i want to combine these two customer data in single resultant then i can go for the set operators the important thing in set operators we have to have the same structure table if it is customer id first one is customer id customer name customer name mobile number here it is a phone number column name might be different but the column data type should be matching the data should be matching and city and city we can combine these two data into one single resultant then i can go for the set operators if it is joined two different table customer table and location table customer table customer country table some different different tables two different tables we will join based on the common column is called joins but here we will combine the row into a single resultant this is the set operators okay we have the four different set operators in all the sql the first one is union the union operation the second one is called union on the third one is intersect and fourth one is minus operation so what is this union union all intersect minus should they use only two tables to combine it no there is no limitations you can go up to n number of tables but the thing is all the table should be in same structure table if this customer yeah you will have all the customer data if it is employees data you'll have all of the remaining table should be employees data and if it is locations yeah we we need to have the locations data so if i want to combine the row level data then i can go for the setup then what is the difference of these four operations right if i do union operation when i combine these two table it will remove the duplicate record in the resultant for an example union will remove duplicate it will remove duplicate record when row level merge is happening union all it will not remove duplicate record when it is going to merge union all will be faster why because it will not go and check whether it is a duplicate record or not that's what union all will be faster it will be faster then intersect is nothing but it's a common record between both the tables okay so common record so common record between both the tables and minus is nothing but differences so what is the difference we have so differences so these are the four different operations we will perform in set operations before going to see this particular example we will see one more example then we will move on to this particular example so this particular example if you consider i have single column table called a table a single column table b single column this is a table so table a then this is a table b i have okay if i want to do a union operation between these two table can i do single column yes we can do a union operation it will remove duplicate if you look at here if i am going to use definitely a union b one one time only it will come so there is no two here then three and four one time then we are the five six and we do not have seven at eight we have only one time nine then the other ten so 10 we have 11 we have 11 and 12 we have so 12 also it will come one time then 13 we have one time it will come it will remove duplicate it will not the same data it will not allow 14 will come one time so 15 and 20 and 23 20 and 23 whether this one will come no it will not come it's already available so it will not allow duplicate record so this is the resultant of a union b what is a union all b so you can take all the record from first table and you can take all the records from the second table so this is the resultant of union all okay it will not remove any duplicate you will get all the records what is a intersect b these operations same as our whatever we have seen in our mathematics right the same operation only a intersect b so common record one we will get then we will get the four so you can see four is available in both the tables and the eight is available 13 is available 13 20 and 23 this is what you will get so whenever we want to find out common record between two tables then i can go with intersection then a minus b i want to find out the differences of these two tables so you if you take a minus b consider the a table and if the same record is available here it will get minus if you consider this table only is available here it will get minus 3 yes 3 is not available it will come 4 it will get minus 5 consider this table alone this table or not if the same record is available here it will get minus so 5 you will get 8 it will get minus 9 we will get it then 11 we will get it there is no 11 here 13 it will get minus 14 yes 14 we will get it 20 it will get minus 1 it will get minus here from 23 also it will get minus so this is the resultant of a minus b what is b minus a you take 1 it will get minus 4 minus 4 minus here before it will get minus 6. so you get six here and eight will get minus ten so you get ten here then we are the twelve is 12 you will get it and 13 you get minus then 15. so this is the resultant of b minus a in real time project whenever we want to compare two different tables we are migrating the data from one table to another table we want to check it whether the data has been migrated correctly we will go for minus operations so if you if you know this particular operation very clearly then understanding the union operation union all operations very very easy okay now we will go to this particular table if you consider these two tables are same structure table then can we do set operation yes we can do if you consider the first one first record so we will take this first record then second record that is available so we will take this one third record yes we will take it then this record it's already it's available okay so one more time it will not come then this one is already available here four is not available i can take four here and i can take five also three also it's available already so it's a full row duplicate only it will not allow if any one column is changed right so one column value say for an example here i'm making rakesh kumar okay something in this way then this record so here we have one time it has the data but this record again it will come okay so this is the union operation so these two are different data right it will it will check for the full duplicate only okay so remember this so i will remove this one i will remove this particular data as of now this is the resultant of customer one union customer two okay i have created a table here select star from customer underscore one if you look at here the same table i have created i have given the create table statement in the below you can also create this table okay now i will go to the second table select star from customer underscore 2 so this is also some different data but i want to do a union b so first table union second table so we will check it whether we will get it correct record or not so here union b we are getting five records right you can see here the same five record we got it here same five record we got it here this is what the resultant of a union b the structure if the structure is same and you can have the same column see here i have column mobile here but column 4 number here so what is the resultant column the first whatever the column we have so that's the column name you can get it you will not get to take phone number okay the column since the columns are same you can use the star here if the columns are different different number of columns then you cannot use a star then you have to mention the column names so we will check but also after some time so this is a union b so what is a union all b a union on so we will take first all the records from here all the record from this table i will take this all the records from this table all the records from this table this is a union all b you will get eight records okay so you'll get eight records if you go here so this is what the resultant of the a union on the a union on a stable union can i have one more table yes you can have one more table you can put any operation union so resultant of this one will be union with other table so you can have one more table also this is the way but the thing is all the table should be same structure all the table should be in same structure if you have the table called three then we can do union ud and all intersect minus anything the resultant of first two will be union with the third one okay this is what we can have so what is intersect now that we will go with intersect so intersect is nothing but common direct cut between both the tables if you look at here what is the common record so common means full row duplicate so this record this record is available it will come on time and this record do we have here no so this record yes it is available this is the common record then i will take this one whether it is a common record yes we have already noted then one more time it will not come so you can make here first table intersect second table you can make intersect here intersect here then you can check now so you'll get only two records the same result the same result we got it okay this is what the intersect what is b minus a what is first table minus second table then we will go to the minus operations what is minus the differences so here consider customer one minus customer two i'm going to take this record will get minus here second one so there is more record you will get it third one yes is there available here it will get minus this also click at points so only one record will get it a minus p so first table minus second table minus so only one record second table minus first table so what is the result second table minus first table and take this record it's available it will get minus then this one 4 you will get it 3 it will get minus 5 you will get it so 4 and 5 you will get it in the b minus a see here 4 and 5 we are getting it this is what two different table can i have if i have one more column here as country can i do union operation of these two table as i have mentioned we need to go for the same structure table so here do we have same structure table no right now it is not same structure table but if you consider these four columns it's the same structure so i'm going to add i will explain these two table we can go for set operation no issues either we should not consider this column if you consider this column r if you consider this column then you have to add null here null value here unless particular so we will do it now i will add one more column alter table customer one customer one add country as far cat 2 of 100 i have added and i am going to update the data update update table name customer underscore one set country equal to india for all the record i want to make it like country called india that's why i'm not using their class just i'm going to update all the 4 record got updated then whenever i'm using update dml operation i have to commit it if you go and check this particular table we have five columns right but this second table we have only four columns can i do set operations now whether this query will work no it will not work now so why two different structure if you look at here so this is what the error we will be getting this is what the error we'll be getting what is error corey block has incorrect number of result columns this is what we'll be getting it so why so here five columns are there here four columns of it so instead of taking column names as star we have to go this way so how to we are to first we have to select this table so we have to mention the column names here so what are the column names so we can take the column name same from here all the column names from here customer id customer name mobile city i will take only these columns from this first table second table also i will take the four columns instead of star i will use four columns here it is the phone number phone now it will work so why because i have mentioned the column names here but if you are thinking okay i need the column country here will it work now no it will not work so country five columns here but four columns here it will not match it right so that's what we will be getting this particular error will be getting it so how can i do how can i rectify this particular error if you have this way either you have to match only that four columns or you can go with null here nonetheless country null is country if you look at here so for the other table you will get null value the other table record you will get 12 values this is what so but remember whenever we are going with any set operations both the table should be in same structure i hope you are clear on this set operations i have a two different table now customer table then locations table can i do set operation of these two table no is a customer table different table location is different table we have the common column called location id so what i can do now for these two table i can combine the columns i can combine the column in this way this is called joints many of us will get confused for this scenario do we need to go for joints or do we need to go for set operations if i have same structure table if i want to merge the rows then i can go for the set operations if i have two different table we have the common column then i can go for the joins here okay so this is what we have to make sure whether we have to go for joins set operations if you look at here i will take the table name called employees underscore union underscore one this is first table they will take this is our first table how many records are there three records are there how many columns eleven columns all the column structures are same select star from employees union two two here same number of columns same number of columns but the record is different here i have seven records here i have seven records if i'm going to do a union b select this one select this one union select this one since columns are exactly matching i'm making star here if it is not matching select the column names okay so you mentioned the column names explicitly if you look at here if i'm going to use select union how many records you'll be getting it unique record between both the tables right it will remove the duplicate 100 101 102 then these three from the first table then second table 3 4 5 6 7 8 so totally eight records will be getting it right if you consider so this is what you'll be getting eight records it will remove the duplicate and you'll be getting it a union all b will be getting all the records without removing duplicate also if you look at here duplicate we have but full row duplicate remember full row duplicate if if you are going for union it will remove the full draw duplicate only union all so everything you will get what is intersect k intersect b so what is the result you will be getting a intersect b common record between both so these two are common right or not one and one or two so this is what we'll be getting one not one and one not two okay then what is minus a minus b first one minus b this one minus this one hundred it is not available here so hundred it will come one not one one or two is available here so it will get minus so first one minus second one you will be getting one only second one minus first one second table minus first table what is the result you will be getting or not one will get minus two will get minus from or not eight you will be getting so how many records five records will be getting it i hope you are clear on union union all intersect minus i have a customer table it has some customer location id here i have location id can i go for union no we cannot go with reunion these two table structures are different structure this is customer table this is location table i cannot go with union instead i can go for join i can join the columns like this okay you take all the columns from here okay join these three columns here column level if i want to go for then i can go for join row level i can go for union i hope you are clear now i have a patient table so here i have a patient table here i have a doctor table so which operation i have to go set operations or join operations i can go with the join operations so patient and doctor based on the doctor id so i can check okay for this particular patient so which doctor is assigned so they this one i can go with join so patient dot doctor id equal to doctor1 dot doctor id so here i have doctor one table it has two different hospital you assume that hospital we have two different hospital under one management this is one doctor list we have another one doctor list now they are going to merge these two in one single some doctors are there here also some doctors are there here also so if i want to go for union it will remove the duplicate it will have the distinct list for these two table character 1 and doctor 2 we have to go for set operations clear doctor id doctor name mobile specialist and here also specialization okay here also i can dr id doctor name mobile number specialization these two table doctor one and doctor two i can go with set operations and doctor and patient i can go with china i hope you are clear how this table will work set operations right okay so here i have one questions they will ask you in interview if you consider these two table so i want to go for join and i have to go for set operations okay if you know this it's very clear sometimes they will ask i have a table a i have a table b i have a table a and table b for these two table i have only one column column one here i have table b column two i have only one column okay you assume that but this one i want to go for join inner join table a inner join with the table b i have some data here i have some data here so what could be the result select a dot column one select a dot column comma b dot column 2 from table a comma table b pair a dot column on equal to b dot column 2 i'm making inner join right this one equal to this one so i can take one and one here this one will be equal to this one yes whether this one will be equal to two no so for this only one record will be getting second one for this one this one will be matching right this one and this one will be matching so one one you will be getting it this one will be matched with this one no you will not get it second one this record will not match with one this record will match with two so you'll be getting 2 and 2 here so this is the resultant of inner join so what is the left outer join so left outer join will be getting all this and 3 null so if you have doubt then you can create the table you load the data and then you can check it right outer join right outer join so all the records from here so if it is matching you'll be getting this one one will get match one will get matched with this one so one one one one so you'll be getting this one and this two will be matching with this two two two that's all right all the records from the right table matching record from the left table and then what is the full outer join this is the full outer join right matching non-matching record from both the tables this is what the full outer join okay so what is the union operation so table a union table b the column name so what is the union operation it will remove the duplicate one two three that's all this is union operation what is union all we'll be selecting all and one on two the union all intersect common record between both so one and two is common minus a minus b a minus b it'll be getting only 3 b minus a will be getting null b minus a you will be getting null so create this table execute this particular query you'll be getting it okay sometimes interview they will change the numbers here so they will have numbers like this 1 1 1 1 all the ones here all the ones here like this also they will give so what is the result so this one will join with this one right this one will join with this one yes one and one you'll be getting it and this one so same one will be joining with this one also so for one record in left hand side you'll be getting two record as resultant for this one one will be equal to one one will be equal to one that's what a dot column one equal to b dot column two again for this one two records will be getting third record two fourth record two so you'll be getting like this this is the result for all other joints sometimes they will ask questions can i get same result for all the four type of joint yes if it is matching you'll be getting all the records the same for this scenario for inner join left outer right outer full router for all this all the values for all the resultant you'll be getting the same same values so what is the union union all intersect minus so union you'll be getting only one record union all all six you'll be getting it okay all six ones you'll be getting it intersect common record between both only one is common right one is only one common minus null a minus b b minus a null so this is what so whenever we are going for any etl testing etl testing or in our informatica if you are going for any testing so here i have the table here i have the target table here i have 100 records okay here i have trans transferred all the 100 records then if you do b minus a if you're getting null that means i have transferred everything correctly right then you are having 1000 records here you are having thousand records here but you are getting only 100 records here b minus a you'll be getting null but a minus b you'll be getting some value right so that's why you have to do a minus b b minus a target minus source source minus target so both you have to do it in order to check the whether we have migrated the data correctly even if i have any transformation logic so that transformation logic we have to write in source query so please practice all this then you will get to know if you have any questions you can ask me i have given the table here i have given the tables here so create tables i have given i have given that a table b table all this select statement and everything so please practice it you'll get to know all this okay intersect minus and everything so this is what the set operations so what is the difference between set operation and join set operations same structure table joints we will go with different structure table different table also set operation will be having customer table with customer table employees table with employees table location table with location ship but joins you will go with employee table with the department's table employee table with the locations table right product table with product description table some different different tables you will go with that's called joints i hope you are clear on set operations so please practice it then if you have any questions you can ask me [Music] hi everyone welcome to nikeit academy we are learning oracle sql in this series now today's session we are going to learn about analytical function this analytical function is very much used in our real time projects we do have rank tens rank row number lead and lag functions if you haven't watched our previous session please watch the previous sessions i have posted nine sessions you can check the descriptions and you can practice it if you haven't subscribed our channel please subscribe the channel and click on the bell icon so that you'll be getting all the notifications without wasting your time let us begin our session welcome in our today's session we are going to learn about analytical functions so analytical functions are nothing but window functions so we may call it as window function as well so we do have different window function out of it these five window functions are very much used in our real-time project the first one is rank function so we have the rank and we are the dense rank and the third one is row number row underscore number then fourth one we have lead function and fifth one we have the lag function so these five functions are very much used in our real time project we will see one by one what is this rank function if you consider this particular table we do have different employees are getting different different salary i want to arrange these employees based on the cell from highest to least so i will do order by on this particular column right so i will do order by on the salary column then i can do okay this is what the from least to highest then i have to make order by under descending so this employee is getting highest salary template the next highest next highest salary so we have already seen aggregate function that we can able to take only this particular highest salary and we have also seen sub queries where we can take the highest salaried employee the entire row we can take the second highest salaried employee third highest salaried employee that we can take it but if i want to take for example i want to rank the employees based on the salary or based on the higher date or based on any other column like you can go for transaction table based on the transact transaction id we want to order it we want to go for transactional date and transaction amount so different columns we want to do and rank so for this scenario we can go with this analytical function okay now if you consider i have taken only the salary column for the explanation if you consider this is the salary column so different employees are getting different different salary one employee is getting fifty thousand another employees getting forty thousand twenty thousand then fifteen thousand so some employees are getting out of all this salary so what is the maximum salary so i have to compare all this record right i have to compare all this record then i can say okay this is the highest salary then i can go with another highest i can go with the next highest salary so next highest salary will be sixty thousand next highest salary will be fifty thousand in that way i have to go with one by one whenever i want to do a window function or analytical function first of all i need to do order by on this particular column so here just i'm doing the order by so consider i'm going to do order by on this particular column so i'm going to make largest to to smallest i'm making this largest to smallest i'm just making largest to smallest order by so this is what i have ordered now then i can go with the rank rank is nothing but the first highest salary will be one first round then second highest salary will be two then three then four next il seller will be five so two employees are getting same salary then rank will be five and five so how we will rank in our school days right the same way here also we will do the rank five and five next will be seven so if there is a tie then the one rank will be skipped out here so even if it is five five five next will be eight only so rank will get skipped so here eight and eight and next will be ten 10 11 12 12 12 so since it is three members are getting same salary we will make rank as 12 12 12 the next rank will be 15 15 16 17. so this is one and so on it will go with the next highest salary okay so this is what the rank then what is dense rank dense rank is nothing but say for an example first rank will be one so up to here it is same only up to here it is same only so one two three four five and five but next if there is a tie if it is dense rank there won't be any skip so next isr will be six only so consecutive rank will be assigned six seven and seven the next one will be eight nine then ten ten ten eleven twelve so this is what we will do the rank dense rank then i will go for the row number so what is row number row number is nothing but same how we will have the roll number right how we will have the row number its a sequence of number so this is what we will be getting it all these three so rank dense rank and row number okay you may ask questions so where it will be used what is the difference different scenarios we will use so if you consider this one salary i want to take top 10 employees based on the selling so in front of you 100 employees are standing based on salary you have to take only 10 employees only 10 employees then you can go for rank you can go for rank 10 employees you can take so from highest to least whatever the employees are getting this salary you can go for rank but your your question is different now the question is select unique 10 salaried employees unique 10 salaried employees so you can go with dense rank but this time so instead of getting 10 employees you may get how many employees you may get 14 employees so 10 different salaries 10 top 10 salaried employees salary we are getting unique salary so that's what this is highest salary next highest next highest next highest next highest salary okay two employees are there we will take two employees also so this is what i can go with dense rank row number whenever we have the same record if i want to assign a different number out of these two numbers then i can go with the row number so based on some transaction so if you consider some transaction table transaction happened on this particular date if i want to rank it based on the transactional timestamp then i can give the row number so this is what within the particular day each and every transaction i want to give the number and row number you can go with so this is what the difference of these three then you can consider then consider so this is rank so how can i write rank i will explain so simple it's very simple you can go with rank is nothing but always you have to write a rank of over within bracket order by column order by whatever the column ranking column so ranking column so remember this is the formula for rank rank okay ranking column either it will be ascending or descending okay so this is the formula for ranking what is formula for dense rank so dense rank is nothing but the same way here we have to make dens underscore rank that's all the row number same way you can go with row number over order by so this is for entire table right entire table 1000 records are there i want to rank based on the salary then i can go for the entire table i can go with order back but here take one more scenario there are four departments in our organization so each and every employees are from different different departments out of these four different different departments now i have to rank on employees based on salary within department consider this is the 10th department next 10th department next salaried employee next one so out of these four employees i have to rank it so if you consider so out of these four highest salaried employee this one this person the next highest salary will be thirty five thousand then twenty thousand then twelve thousand so i have to rank one two three and four within ten so this is what we can go for within the department id we can do the ranking one two three four five this is called group by right so grouping it department id we are grouping it and then we are doing the ranking so in analytical function the group by we will call it as partition byte in analytical function we will call it as group by is nothing but partition by so you can consider so normally how will you write a group by normally how will you write group by so before order by we will write group by right the same way if i want to write any group by here so i have to go with rank of over so before order by i have to use partition by so in analytical function group by is call it as partition by so partition by group column so without the column we want to go for so that particular column we have to mention it if you consider so this is called this is called within the group we are making the function rank so one more example i will show you so you can go for dense rank rank any any functions on this group by also if you consider this is the employees table we are the employee first name last name all this so each and every employees are getting salary i want to rank based on the salary alone then i can go for order buy on the salary column descending if i want to go for group by on the department id so here this is what the group buy on the department id within this group so what is the salary within the group what is the salary so within the group what is the salary this is what group boy on the department id so this one we can say it's the group by on the department id consider this particular table i have the employee id first name and salary so for each and every employees i want to find out the salaries are defined i want to find out total salary total salary say for an example here i have the total salary all the employees salary here so 1 lakh 23708 so that should be applied for all this it's nothing but over keyword over keyword so if you use over keyword it will be applied on all this and if you consider the department id 90 so within this particular department id what is the seller so this is what the salary 58 000 and with within this 60th department what is the salary so 28 800 and within this particular one 100 department via the salary like sum of salaries 36908 right this is what if you are if you sum all this so it says sum all this it will become this one one lakh twenty three thousand seven zero eight so how can i take it this one we can use the keyword called over so that i will explain now how the over will be used in our real time project so sometimes in the rank analytical function we'll be seeing that over keyword so the keyword is over so what is the keyword called over so i will tell you what is this over if you consider this particular scenario so there are 10 different representatives are there 10 different representative so representative 1 2 3 4 5 they are doing some sales value so representative one he did some sales value for thousand he did sales value for two thousand thousand five hundred two thousand five hundred three thousand the same way so many representative if i want to know each and every representative sales value then i can go for this field i want to go for for all the representative one two five i have the manager one okay manager one so i have to find out okay out of all the representative what is the manager once target this is one he has achieved manager two manager two is for representative six to ten so this is what manager 2 here over class will come into picture then senior manager he has manager 1 and manager 2 then this is what we have to here we have to use the over keyword okay our keyword how to achieve this i will show you if you look at here select i will take employees table i will take employees table select star from employees table so what is this over keyword will do employees table this is what my salary salary column right i want to find out so higher salaried employee or whatever the way but i want to find out over keyword so what is over will do select the column names i will select all the column names so if you look at here what is this over keyword will do this is salary column right this is salary column this is a department id column so can i find the sum of salary here along with employee id first name all this can i find sum of salary okay if you look at here select star from employees employee id first name salary department id can i select sum of salary here can i select sum of salary here in our aggregate function we have seen is it possible to select sum of salary we cannot select okay say for an example this particular scenario i want to take each and every representative salary their sum of salary that means this salary and some salary so 10 000 can i take like this here their salary and sum of salary okay if i'm going to take it will give you error it's not a single row function right not a single group group function this is what the error will be getting then how can i find out is it possible we can use the keyword called over okay if you use overwrite along with this you will be getting this value you can see employee id their salary their salary individual salary their department id and overall salary so out of this much salary this employee is getting this much salary out of this much salary this employee is getting this much salary out of this much salary this employee like this he can find out okay can i can i find okay out of this much salary this 90 at the department how much we are getting can i find yes that is also possible how can i find so 90 other department i have to go for okay so look at here i have to go this way their department id over we have to make group by on the department id correct group by on the department id so how can i make group by write in in analytical function we have to use partition by okay so partition by department id so here you have to use partition by department id if you look at here so this is individual salary okay so some of i can make alias name i can make a last name here here i will go for so department wise some salary okay some layers name that's all and if you look at here this is what we are getting it right individual salary is this much only one employee is there the department that's what department-wise 4400 and out of total okay out of this much salary 20 at the department if you take 20 of the department alone out of this much salary this department is getting 19 000 out of 19 000 only two employees are there one employee is getting thousand one employee is getting six thousand okay this is what we can use over class over and partition by partition means here group by so whenever you want to go for group by on analytical function we have to go for partition by i hope you are clear if you look at here so 30 other department there are six employees are there so out of these six employees so out of this much salary 30 at the department they are getting this much and they are getting this much same scenario is applicable in this particular scenario if they are asking like this question okay how can i find out this is the way for each and every representative manager wise total and senior manager is total we have to tell them i will go for the over partition by clear this is what okay now how can i find out rank rank is very very simple the same way if you look at here so i'm going for rank rank off okay so we have to make the keyword called rank of over within the bracket you have to make ranking column on the order by so which column i am going for ranking that column we have to go for the order by least to highest means order beyond ascending order a rank highest to least means descending order so that's the way you have to make same way a rank of over order by salary descending highest to list then how can i make rank on each and every department this is the rank column so we will do first one then i will i will go for department wise so this is the one employee id first name all this i can go for rank so i can use rank of so all the columns rank of over order by this one this is called rank r and k you can use rank from employees look at here employee id first name their salary department id then rank so how the rank is calculating first salary right ascending order we have made it first salary one the next salary will be 2 2 so next will be 4 so 4 4 and it is making 6 6 7 8 9 10 11 then next will be 12. so why all the six same salary all the employees are getting same salary i can go for descending also rank off over order by salary descending highest to least this is what it will make highest to least salary employee id first name salary all the column i can make descending order this is what i have to go for rank can i go for dense rank yes we can go for dens underscore rank dense underscore rank we can go for you can see here highest salary next to highest two three four five six seven seven next will be eight only this is called dense rank 8 9 10 10 10 next will be 11 so this is what so dense rank means consecutive rank will be assigned this is what okay this is called rank condense rank okay we found the rank condenser rank so what is row number same way instead of dense rank we have to go for row number so we can go for the row number so row underscore number of so each and every person it will make row number here so one two three four five so if i want to find out based on the salary i want to go for each and every person uniquely then i can go for the row number column this is what i can go for then here if you look at here this is the salary and now consider there are four departments for this scenario i am taking there are four departments in our employees table so this employee is from 20th department getting 50 000 salary this employee from 30th department getting 40 000 a salary this amplified so different way we are getting the salary right this is the way we are getting different different salaries then the question is so department-wise you do the rank so 10th department you take 10 department so one employee two employees three and four employees are there in 10th department so out of 10th department which is the highest one forty thousand next guy is thirty five next guy is twelve next year is twenty thousand next highest twelve thousand so this is what i'm ordering it so highest to list here i'm making department id as the group by column in analytical function group by column we will call it as partition byte so always group by it will come before order by write so that's why whenever we are writing group by column here in this scenario if i want to write a group by so group by always come before the order by right so here partition by partition by group by column department id so you have to practice it you have to practice it so then only same scenario if i am going to give in different way you will get to know all this so this is what you have to find out each and every department we can find the salary if you look at here i'm going with over partition by department id so this is what you can so we have to use rank of so rank of over partition by so use rank of over partition by department id all this then each and every department will be getting the rank so 20 other department what is this 30th department the rank right one two three four five even if you have a tie then it will make based on the rank see here 50 of the department if there is a tie so here 11 11 then it is making 13 right so this is what it is making the rank you can go for dense rank also row number also anything you can find you can go with so dense underscore rank so this is what you will make rank okay one question here this is a common question they will ask an interview right so i want to find out top five ranked employees top five ranked employees so if you look at here so top five salaried employee top five salary template right so here this is what the rank i'm finding i have to find out top 5 salaried employee that means i have to filter out can i use where r and k less than or equal to 5. no i cannot use it why because if i'm going to select this this rnk is not a not a physical column in the table if any physical column then i can use the rank physical column i can use this column less than or equal to 5 but here r and k is a derived column we cannot use this way we'll be getting error here so we'll be getting error so to avoid this we can go for the subquery say consider this one is the table if you consider this is a table entire select statement okay if you consider this one as a table then r and k is the column right so how can i make this one as a table select star from star from so this is the table select star from the table where r and k right so you can use it this is the way you can use it okay top highest top five salaried employee second highest salaried employee it's very simple second highest salaried employee you can go for the same way if you want all the columns you have to select all the columns here instead of denser rank you can select rank itself so rank of over second highest salary less than or equal to two second highest salaried employee if i'm asking second highest salary then you have to go for rank equal to two second highest salaried employee third highest salary employee any way you can put this way okay i can i go with second highest salary employee in each department in each department it's very simple right each department are teaching by partition by department id so each and every department you will be getting second so why 10 is not available 40 is not available because in 10th department i have only one employee so these these are all the employees are getting second highest salaried in each department second highest salary third highest salary if they are asking questions this is the way you have to tell them i want to go for top salary top salaried employee top salaried employee you can you can go for rank equal to one top salaried employee so you remember we have seen this one in our aggregate function itself i have told you that if i want to take soft tops or i did imply either you have to go for sub query or we have to go for the rank here i'm making the rank top top highest salary in each department each and every department who is getting highest salary so this is what we have to find out okay each and every department see here i want to know the highest salaried employee in each in each department or second one right so this is the top five employees but i want to know which department they are from department name i need is it possible to bring the department name here if i want to take the department name i have to go for a join why because department name is there in departments table so what is 90 i have to check in departments table so for this i have to go for joins how can i make joins it's very simple we know already select column names right select column names from first table so consider this is the first table so from this is the first table i can always sit like e okay e inner join or comma also i can put inner join second table department's d okay on on e dot department underscore id equal to d dot department underscore id okay so this is what we have to do the joints then i can make i want in e column i want all the columns e dot star that means e dot star means whatever the column i have selected from the e table so that will be selected here comma d dot department underscore name so this is what i want to go for where rank so you can say select okay i want to go for order by class order by order by rank rnk so you can see here so the highest salaried employee from executive department next highest will be sales department next highest will be executive department so this is what you have to write the joins rank where class order by class the same one can be achieved by using the implicit join method also instead of inner join they should have written like this comma instead of on you have to go for where class so here you have to use und okay this is the way also you can write same so this is ansi method below is the implicit method employees e department's d where then uncondition then this is what so this is what you can go for the rank dense rank row number all this i have given all the queries here so group by partition by so how can i make least five salaried employee so rank off right so least five salaried employee rank of we have to make order by on the ascending order five earners top five salaried employee this is what employee with the department name that's what we made it now so instead of selecting a dot star here i used all the columns name separately okay so this is what i can go for the functions then we do have the lead and lag function in analytical function we do have lead function and lag function what is the lead function and lag function will do right if you consider this particular query select employee id first name all the columns i have taken so lead of select star from employees i will explain first so i want to know so who has joined before this particular person who has joined before this particular person if i want to know how can i do it who are joined before this person or after this person who has joined after this person i have to make order by on the higher date so if i if i'm going to order by on the higher date who has joined after this legs okay after lex william has joined after william herman has joined so this is the way i can find right after means lead before means lag so who is getting more than salary of william then i have to go for salary right order buy so 8300 who is getting more than the william of salary jack is getting more than the claim of salary so that's what i have to find out lead and lag so the same way lead off higher date it will take the date over order by higher date that means after higher the first name first name means which name it will take okay so it will make order by on the higher date for each and every person it will make the after legs william has been hired on this particular date okay next one william after william herman has been hired on this particular date this is what lead and lag function will do lag means the before you can go for lag also and lag also you can go for column name who is getting salary higher than me i want to find out then i can go for this way not only this particular person for all the person so why here it is null he's the first person highest he joined first the company that's why before hire no one is there so you can see this is the way lead and lag i have given all the function all the statement here just to go through all the statement you'll get to know all the statements very clearly so these are all about analytical functions so start practicing it you can get able to get it so when will you use row number this is the way for an example if i have sample data i have given some queries here so take this particular table create this table here the transaction date transaction id account number name credit or debit amount balance amount we are doing the transactions i want to know select top five balanced account top five balanced account so this is a balanced amount is there based on the balanced amount we have to make top five so order by on the balance amount descending take the rank five select latest five transaction for each account latest five transaction for each account latest transaction five transaction do the order buy on the transaction id to the transaction id why because transaction id is unique descending order ok group by account number each and every account number we have to find out latest five transaction group by on account number take the transaction id on rank descending order then take top five which is a top amount transaction in each day that means balance based on the balance amount so each day we have to do the ranking so if you want to take enter row then you have to go for the rank function only if i want to go for enter row we have to go for the rank only if i want to go for top or bottom in the column level enter row you want to take then i have to go for rank if i want to take amount only then i can go for the aggregate functions so i will give you this particular statement to you so just to go through you can able to find out all the rank contents rank very clearly if you have any questions you can ask i will give you this document also [Music] [Music] [Music] [Music] [Music] [Music] [Music] [Music] [Music] [Music] [Music] [Music] [Applause] [Music] [Music] [Music] [Music] [Music] [Music] [Music] [Music] [Music] [Applause] [Music] [Music] [Music] [Music] [Music] [Music] [Music] [Music] [Music] [Music] you

Channel: NIC IT ACADEMY

Views: 453,505

Keywords: sql interview questions and answers, sql server interview questions and answers, sql questions and answers, sql interview questions for testers, sql interview questions for freshers, sql interview questions pdf, sql interview questions for beginners, sql interview questions, sql interview questions for data analyst, sql interview questions youtube, sql interview questions for business analyst, sql interview questions for experienced, interview questions sql, sql tutorial, sql

Id: iuvzjf7nU1I

Length: 434min 22sec (26062 seconds)

Published: Tue Jun 28 2022