hello everyone welcome to this live session on SQL full course video by simply learn in this video we will learn everything to master structured query language or SQL this full course video will help you write SQL commands using MySQL post SQL as well as SQL server our highly experienced trainers will take you through this course but before we begin make sure to hit the Subscribe button and stay updated with all the latest tech videos so we will start by learning the basics of SQL along with understanding entity relationship diagrams then you will learn how to install MySQL workbench on Windows we look at the popular SQL commands to select insert update and delete records and perform other operations on database tables you will see how to use the top numerical and text functions in SQL next we will cover how to use Group by and having an SQL followed by joints so you will learn the different types of joint statements as well as subqueries in SQL moving further we will understand triggers stored procedures and window functions in MySQL you will get an idea about another important topic that is to write SQL queries with python on jupyter Notebook then we will look at another free and open source relational database management system that is postris SQL with extensive Hands-On demonstration finally you will learn how to become a SQL Developer and see the top SQL interview questions that are frequently Asked in the interviews so let's get started with what is SQL in this session we are going to learn about databases how data is stored in relational databases and we'll also look at some of the popular databases finally we'll understand various SQL commands on my SQL Server now let's get started with what is a database so according to Oracle a database is an organized collection of structured information or data that is typically stored electronically in a computer system a database is usually controlled by a database management system or dbms so it is a storage system that has a collection of data relational databases store data in the form of tables that can be easily retrieved managed and updated you can organize data into tables rows columns and index it to make it easier to find relevant information now talking about some of the popular databases we have mySQL database we also have Oracle database then we have mongod DV which is a no SQL database next we have Microsoft SQL Server next we have Apache cassendra which is a free and open source nosql database and finally we have postest SQL now let's learn what is SQL so so SQL is a domain specific language to communicate with databases SQL was initially developed by IBM most databases use structured query language or SQL for writing and querying data SQL commands help you to store process analyze and manipulate databases with this let's look at what a table is so this is how a table in a database looks like so here you can see the name of the table is players on the top you can see the column names so we have the player ID the player name the country to which the player belongs to and we also have the goals scored by each of the players so these are also known as fields in a database here each row represents a record or a tle so if you have the player ID which is 103 here the name of the player is Daniel he is from England and the number of goals here scor is s so you can use SQL commands to query update insert records and do a lot of other tasks now we'll see what the features of SQL are SQL lets you access any information stored in a relational database with SQL queries data is extracted from the database in a very efficient way the structured query language is compatible with all database systems from Oracle IBM to Microsoft and it doesn't require much coding to manage databases now we will see applications of SQL SQL is used to create a database Define its structure implement it and lets you perform many functions SQL is also used for maintaining an already existing database SQL is a powerful language for entering data modifying data and extracting data in a database SQL is extensively used as a client server language to connect the front end with the back end the supporting the client server architecture SQL when deployed as data control language DCL helps protect your database from unauthorized access in this session we will learn about ear diagram starting with what is an ear diagram and why it's been so much used by the companies then we will learn about the symbols used in the ear diagram and get familiar with the components of it so hey everyone I'm abisara from Simply learn and welcome to this video on ER diagram in dbms but before we begin if you love watching Tech videos subscribe to our Channel and hit the Bell icon to never miss an update let's start have you ever wondered how these big e-commerce companies manage their tons of data that keeps updating every second and these data sets keep on updating in their databases to perform operations on this data they should have a conceptual understanding of these databases and this is done by understanding ER diagrams of the databases so let's understand what an year diagram is an entity relationship diagram describes the relationship of entities that needs to be stored in a database e diagram is mainly a structural design for the database it is a framework made using specialized symbols to define the relationship between entities ER diagrams are created based on the three main components entities attributes and relationships let's understand the use of ER diagram with the help of a real world example here a school needs all its student re boards to be stored digitally so they approach an IT company to do so a person from the company will meet the school authorities note all their requirements describe them in the form of ear diagram and get it cross checked by the school authorities as the school authorities approve the year diagram the database Engineers would carry further implementation let's have a view of an ear diagram the following diagram showcases two entities student and course and the relationship the relationship described between student and course is many to many as a course can be opted by several students and a student can opt for more than one course here student is the entity and it possesses the attributes that is student ID student name and student age and the course entity has attributes such as course ID and course name now we have an understanding of year Di let us see why it has been so popular The Logical structure of the database provided by ER diagram communicates the landscape of business to different teams in the company which is eventually needed to support the business year diagram is a GUI representation of The Logical structure of a database which gives a better understanding of the information to be stored in a database database designers can use e diagrams as a blueprint which reduces complexity and helps them save time to build databases quickly ER diagrams helps you identify the entities that exist in a system and the relationships between those entities after knowing its uses now we should get familiar with the symbols used in a diagram the rectangle symbol represents the entities oral symbol represents attributes a rectangle embedded in a rectangle represents a weak entity a dashed toal represents a derived attribute a diamond symbol represents a relationship among entities double all symbol represents multivalued attributes now we should dive in and learn about the components of ER diagram there are three main components of ER diagram entity attribute and relationship entities have weak entity attributes are further classified into key attribute composite attribute multivalued attribute and derived attribute relationships are also classified into one to one relationships one to many relation relationships many to one relationships and many to many relationships let's understand these components of Fe diagram starting with entities an entity can be either a living or a non-living component an entity is showcased as a rectangle in a near diagram let's understand this with the help of a near diagram here both student and course are in rectangular shape and are called entities and they represent the relationship study in a diamond sh let's transition to weak entity an entity that makes Reliance over another entity is called a weak entity the weak entity is showcased as a double rectangle in ER diagram in the example below the school is a strong entity because it has a primary key attribute School number unlike the school the classroom is a weak entity because does not have any primary key and the room number attribute here acts only as a discriminator and note a primary key now let us know about attributes attribute an attribute exhibits the properties of an entity an attribute is Illustrated with an oval shape in an ER diagram in the example below student is an entity and the properties of student such as address age name and role number are called its attributes let's see our first classification under attribute that is key attribute the key attribute uniquely identifies an entity from an entity set the text of a key attribute is underlined in the example below we have a student entity and it has attributes name address role number and age but here role number can uniquely identify a student from a set of students that's why it is termed as a key attribute now we will see composite attribute an attribute that is is composed of several other attributes is known as a composite attribute an oval showcases the composite attribute and the composite attribute OV is further connected with other ows in the example below we can see an attribute name which can have further subparts such as first name middle name and last name these attributes with further classification is known as composite attribute now let's have a look at multivalued attribute an attribute that can process more than one value are called multivalued attributes these are represented as double O shape in the example below the student entity has attributes phone number role number name and age out of these attributes phone number can have more than one entry and the attribute with more than one value is called multivalued attribute let's see derived attribute an attribute that can be derived from other attributes of the entity is known as a derived attribute in the ear diagram the derived attribute is represented by dashed over and in the example below student entity has both date of birth and age as attributes here age is a derived attribute as it can be derived by subtracting current date from the student date of birth now after knowing attributes let's understand relationship in ER diagram a relationship is showcased by the diamond shape in the year diagram it depicts the relationship between two entities in the below example student study course here both student and course are entities and studies the relationship between them now let's go through the type of relationship first is one to one relationship when a single element of an entity is associated with a single element of another entity this is called one toone relationship in the example below we we have student and identification card as entities we can see a student has only one identification card and an identification card is given to one student it represents a one toone relationship let's see the second one one to many relationship when a single element of an entity is associated with more than one element of another entity is called one to many relationship in the below example a customer can place many orders but a particular order cannot be placed by many customers now we will have a look at many to one relationship when more than one element of an entity is related to a single element of another entity it is called many to one relationship for example students have to opt for a single course but a course can be opted by number of students let's see many to many relationship when more than one element of an entity is associated with more than one element of another entity is called many to many relationship for example an employee can be assigned to many projects and many employees can be assigned to a particular project now after having an understanding of V diagram let us know the points to keep in mind while creating the year diagram first identify all the entities in the system embed all the entities in a rectangular shape and label them appropriately this could be a customer a manager an order an invoice a schedule Etc identify relationships between entities and connect them using a diamond in the middle illustrating the relationship do not connect relationships connect attributes with entities and label them appropriately and the attributes should be in all shape assure that each entity only appears a single time and eradicate any redundant entities or relationships in the ear diagram make sure your ear diagram supports all the data provided to design the database make effective use of colors to highlight key areas in your diagrams there are mainly four types of SQL commands so first we have data definition language or ddl so ddl commands change the structure of the table like creating a table deleting a table or altering a table all the commands of ddl are autoc committed which means it permanently save all the changes in the database we have create alter drop and truncate as ddl commands next we have data manipulation language or DML so DML commands are used to modify a database it is responsible for all forms of changes in the database DML commands are not autoc committed which means it can't permanently save all the changes in the database we have select update delete and insert as DML commands now select command is also referred to as dql or data query language third we have data control language or DCL so DCL commands allow you to control access to data within the database these DCL commands are normally used to create objects related to user access and also control the distribution of privileges among users so we have Grant and rork which are the examples of data control language finally we have something called as transaction control language or TCL so TCL commands allow the user to manage database transactions commit and roll back are example of TCL now let's see the basic SQL command structure so first we have the select statement so here you specify the various column names that you want to fetch from the table we write the table name using the from statement next we have the wear Clause to filter out our table based on some conditions so you can see here where condition one condition two and so on then we have the group by Clause that takes various column names so you can write Group by column 1 column 2 and so on next we have the having Clause to filter out tables based on groups finally we have the order by Clause to filter out the result in ascending or descending order now talking about the various data types in SQL so we have exact numeric which has integer small int bit and decimal then we have approximate numeric which are float and real then we have some date and time data types such as date time Tim stamp and others then we have string data type which includes car the VAR car and text finally we have binary data types and binary data types have binary VAR binary and image now let's see some of the various operators that are present in SQL so first we have our basic arithmetic operators so you have addition the subtraction multiplication division and modulus then we have some logical operators like all and any or between exist and so on finally we have some comparison operators such as equal to not equal to that's greater than less than greater than equal to or less than equal to not less than or not greater than now let me take you to my MySQL workbench where we will learn to write some of the important SQL commands use different statements functions data types and operators that we just learned in this session we will learn how to install MySQL workbench and then we will run some commands firstly we will visit the official website that is myql.com and now we'll move to the downloads page now scroll down and click on MySQL GPL downloads now under Community downloads click on my SQL installer for Windows the current versions are available to download I will choose this installer and click the download button now here just click on no thanks just start my download Once the installer has downloaded open it you may be prompted for permission click yes this opens the installer we will be asked to choose the setup type we will go with custom click next now you have to select the products you want to install we will install only the MySQL server my SQL shell and the MySQL workbench expand MySQL servers by double clicking on it and choose the version you want to install and click on this Arrow now you have to do the same thing for applications expand applications and choose the my SQL workbench version you want to install and click on the arrow and will do the same thing for my SQL shell and choose the latest version click on the Arrow these are the products that have to be installed in a system now we will click next I'll click execute to download and install the server this may take a while depending on your internet speed as the download is completed click next now you'll see the product configuration click next now we'll configure our SQL Server instance here we will go with the default settings and click next and under authentication select use strong password encryption for authentication which is recommended and click on next now here set the password for your root user by the way root is the default user this user will have access to everything I will set my password now I'll click on next and here also we'll keep the default settings and click on next now to apply on configuration we will execute the process once all the configuration steps are complete click finish now you will see the installation is complete it will launch my SQL workbench and my SQL shell after clicking on finish now the shell and workbench has started now we will connect by clicking on the root user it will ask for a password enter the password and it will connect successfully yeah the workbench has started now we'll just connect the server so first we'll open command prompt now we will reach the path where MySQL files are present we go to this PC local DC program files my SQL my SQL Server 8.0 bin and now we copy this path now we'll open the command prom and write a command CD space and paste the link and press enter now we'll write another command that will be my SQL minus U space root minus p and press enter now it will ask for your password just enter the password and press enter now the server has started and now we'll see some commands in my SQL workbench first we will open my SQL workbench now we'll click on the local instance my SQL 80 and enter the password to connect to the Local Host the my SQL workbench has started now we will see some commands the First Command we will see is show databases show databases semicolon and now we will select the whole command and click on this execute button and here we will see the result in the result grid these are the databases dat are stored already in the database now there are four databases that is information schema MySQL performance schema and SS now we will select one of the database we'll use uh my SQL now we have selected the my SQL database and now in this database we will see which tables are stored in this my SQL database to see that we will run a command show tables we'll select the command and click on the execute button these are the tables that are stored in this my SQL database that is columns _ PR component DP and much more now let me now go ahead and open my MySQL workbench so in the search bar I'll search for my SQL workbench you can see I'm using the 8.0 version I'll click on it and here it says welcome to my SQL workbench and below under connections you can see I have already created a connection which says local instance then you have the root the local host and the port number let me click on it you can see the service the username is root and I'll enter my password and hit okay now this will open the SQL editor so this is how the MySQL workbench looks like here we learn some of the basic SQL commands so first let me show you the databases that are already present so the command is so databases you can hit tab to autocomplete I'll use a semicolon I'll select this and here on the top you can see the execute button so if I run this below you can see the output it says show databases seven rows are returned which means currently there are seven databases you can see the names all right right now let's say I want to see the tables that are present inside this database called world so I'll use the command use World which is the database name now let me run it so currently I'm using the world database so to display the tables that are present in the world database I can use the show command and write show tables give a semicolon and I'll hit cut control enter this time to run it all right so you can see the tables that are present inside this world database so we have three tables in total City Country and Country language now if you are to see the rows that are present in one of the tables you can use the select command so I'll write select star which basically means I want to display all the columns so star here means to display all the columns then I'll write my from the table name name that is City so this command is going to display me all the rows that are present inside the city table so if I hit control enter all right you can see the message here it says, rows were returned which means there were total thousand records present inside the city table so here you can see there's an ID column a name column this country code district and population all right similarly you can check this structure of the table by using the describe command so I'll write describe and then I'll give the table name that is City now let's just run it there you go the field shows the column names so we have ID name country code district population type here shows the data type of each of the columns so district is character 20 ID is an integer population is also integer null says yes or no which means if no then there are no null values if it's yes which means there are null values in your table key here represents whether you have any primary key or foreign key and these are some extra information now let's learn how to create a table in my SQL so I'll use the create table command for this and before that let me create a database and I'll name it as SQL intro so the command is create database and I'll give my database name that is SQL intro me give a semicolon and hit control enter so you can see I have created a new database now if I run this command that is show databases you can see this newly created database that is SQL intro if I scroll down there you go you can see the name here SQL inro okay now within this database we'll create a table called employee details now now this will have the details of some employees so let me first show you how to create a table that will be present inside the SQL intro database so I'll use the command create table and then I'll give my table name that is going to be employee uncore details next the syntax is to give the column names so my first column would be the name column which is basically the employee name followed by the data type for this column since name is a text column so I'll use varar and I'll give a value of 25 so it can hold only 25 characters okay next I also want the age of the employee now age is always an integer so I'll give int okay then we can have the gender of the employee so gender can be represented as F or m f for female and M for male so I'm using the card data type or character data type and I'll give the value as one then let's have the date of join or doj and this is going to be of data type date all right next we'll have the city name that is the city to which the employee belongs to so again this is going to be warar 15 finally we'll have a salary column and salary will'll keep it as float since salary can be in decimal numbers as well now I'll give a semicolon all right so let me just quickly run through it so first I wrote my create command then the table which is also a keyword followed by the table name which is employee details here and then we give the column names such as name age this gender date of join City and salary for each of the columns we also give the data type all right so let me just run it okay so here you can see we have successfully created our first table now you can use the describe command want to see the structure of the table I'll write describe empore details if I run this there you go so under field you can see the column names then you have the data types null represents if the table can accept null values or not and these are basically empty and we haven't set any default constraint all right moving ahead now let's learn to add data to our table using the insert command so on a notepad I have already written my insert statement so let me just copy it and then I'll explain it one by one all right so if you see this so we have used an insert into statement or a command followed by the table name that is EMP details then this is the syntax using values I have passed in all the records so first we have Jimmy which is the name of the employee then we have 35 which basically represents the age then m means the gender or the sex then we have the date of join next we have the city to which the employee belongs to and finally we have the salary of the employee so this particular information represents one record or a toule similarly the next employee we have is Shane you can see the age and another information then we have Mary this Dwayne Sara and ammy all right so let me go ahead and run this so this will help you insert the values in the table that you have created you can see we have successfully inserted six records now to display the records let me use the select statement so I'm using select star from empore details if I run this you can see my table here and the values it has so we have the name column the age column the state of join City salary and these are the values that you can see here moving ahead now let's say you want to see the Unique city names present in the table so in this case you can use the distinct keyword along with the column name in the select statement so let me show you how you can print the distinct city names that are present present in our table now if you notice this table clearly we have Chicago Seattle Boston Austin this New York and there's Seattle repeated again so I only want to print the unique values so for that I can write my select statement as select distinct then I'll give my column name which is City from my table name that is EMP details if I run this you can see my query has returned five rows and these are the values so we have Chicago cattl which was repeated twice is just been shown once then we have Boston Austin and New York now let's see how you can use inbuilt aggregate functions in SQL so suppose you want to count the number of employees in the table when that case you can use the count function in the select statement so let me show you how to do that so so I'll write select I'll use my function name that is Count now since I want to know the total number of employees I'm going to use their name inside the brackets from employee uncore details now if I run this this will return the total number of employees that are present in the table so we have six employees in total now if you see here in the result it says count name name now this column is actually not readable at all so what SQL provides is something called as an alas name so you can give an alas to the resultant output so here I can write select count of name and use an alas as as I can give an alas as countor name and run this statement again there you go you can see here in the resultant output we have the column name as count name which was our alas name now suppose you want to get the total sum of salaries you can use another aggregate function called sum so I'll write my select statement and this time instead of count I'm going to write sum and since I want to find the sum of salaries so inside the bracket I'll give my salary column from my table name that is employee details if I run this this will result in the total sum of salaries so basically it adds up all the salaries that were present in the salary column now let's say you want to find the average salary so instead of sum you can write the average function which is AVG so this will give you the average salary from the column salary so you can see it here this says average salary now if you want you can give an alas name to this as well now you can select specific columns from the table by using the column names in the select statement so initially we were selecting all the columns for example like you saw here the star represents that we want to see all the columns from the employee details table now suppose you want to see only specific columns you can mention those column names in the select statement so let's say I want to select just the name age and the city column from my table that is employee details so this will result in displaying only the name agent City column from the table if I run it there you go it has given only three columns to me now SQL has a we Clause to filter rows based on a particular condition so if you want to filter your table based on specific conditions you can use we Clause now we Clause comes after you give your table name so suppose you want to find the employees with age greater than 30 in this case you can use a wear Clause so let me show you how to do it I'll write select star from my table name that is employ details and after this I'll use my wear Clause so I'll write where age greater than 30 if I run this it will give me the output where the age is only greater than 30 so it excluded everything that is less than 30 so we have four employees whose age is greater than 30 here now suppose you want to find only female employees from the table you can also use a wear Clause here so I'll write select let's say I want only the name the gender which is sex here comma City from my table that is employee details where I'll give my column name that is sex is equal to since I want only the female employees I'll give F and run this statement okay you can see here our employee table has three female employees now suppose you want to find the details of the employees who belong to Chicago or Austin in this case you you can use the or operator now the or operator in SQL displays a record if any of the condition separated by R is true so let me show you what I mean so since I want the employees who are from Chicago and Austin I can use an or operator so I'll write select star from EMP details which is my table name then I'll give my wear Clause where City equal Al to I'll give my city name as Chicago and then I'm going to use the or operator or city equal to I'll write Austin I'll give a semicolon and let me run it there you go so in the output you can see all the employes who belong to the city Chicago and Austin now there is another way to write the same SQL query so you can use an in operator to specify multiple conditions so let me just copy this and instead of using the or operator this time I'm going to use the in operator so I delete this after the we Clause I'm going to write where City and use the in operator inside bracket I'll give my city names as Chicago and I want Austin so I'll give a comma and write my next city name that is Austin so this query is exactly the same that we wrote on top let me run this you will get the same output there you go so we have Jimmy and Dwayne who are from Chicago and Austin respectively now SQL provides the between operator that selects values within a given range the values can be numbers text or dates now suppose you want to find the employees whose date of join was between 1st of Jan 2000 and 31st of December 2010 so let me show you how to do it I'll write select start from EMP details where my date of join that is doj between I'll give my two date values that is 1st of Jan 2000 and I'll give my second value the date value that is 31st of December 2010 so every employee who has joined between these two dates will be displayed in the output if I run it we have two employees who had joined between 2000 and 2010 so we have Jimmy and Mary here who had joined in 2005 and 2009 respectively all right now in we Clause you can use the and operator to specify multiple conditions now the and operator displays a record if all the conditions separated by and are true so let me show you an example I'll write select star from employee details table where I want the age to be great than 30 and I want sex to be male all right so here you can see I have specified two conditions so if both the conditions are true only then it will result in an output if I run it you can see there are two employees who are male and their age is greater than 30 now let's talk about the group by statement in SQL so the group by statement groups rows that have the same values into summary rows like for example you want to find the average salary of customers in each department now the group by statement is often used with aggregate functions such as count sum and average to group the result set into one or more columns let's say we want to find the total salary of employees based on the gender so in this case you can use the group by Clause so I'll write select let's say SE X comma I want to find the total sum of salary as I'll give an alas name let's say total salary from my table name that is employee details next I'm going to group it by sex okay let me run it there you go so we have two genders male and female and here you can see the total salary so what this SQL statement did was first it grouped all the employees based on the gender and then it found the total salary now SQL provides the order by keyword to sort the result set in ascending or descending order now the order by keyword sorts the records in ascending order by default to sort the records in descending order you can use the dec keyword so let's say I want to sort my employee details able in terms of salary so I'll write select star from empore details and I'll use my order by clause on the salary column so this will sort all the records in ascending order of their salary which is by default you can see the salary column is sorted in ascending order now suppose you want to sort the salary column and dis play it in descending order you can use this keyword that is DC let me run it you can see the output now this time the salary is sorted in descending order and you have the other values as well now let me show you some basic operations that you can do using the select statement so suppose I write select and do an addition operation let's say 10 + 20 and I'll give an alas name as addition if I run this it will give me the sum of 10 and 20 that is 30 similarly you can use the subtraction operator and you can change the alas name as let's say subtract let's run it you get minus 10 now there are some basic inbuilt functions there are a lot of inbuilt functions in SQL but here I'll show you a few suppose you want to find the length of a text or a string you can use the length function so I'll write select and then use the length function I'll hit tab to autoc complete let's say I want to find the length of country India and I'll give an alas as total length if I run it you see here it returns five because there are five letters in India there's another function called repeat repeat so let me show you how repeat works so I'll write select repeat let's say I want to repeat the symbol that is at the rate I'll put it in single quotes because it is a text character and I want to repeat this character for 10 times close the bracket and let's run it you can see here in the output it has printed at the rate 10 times you can count it all right now let's say you want to convert a text or a string to upper case or lower case you can do that as well so I'll write select and use the function called upper let's say I want to convert my string that is India to upper case I'm not giving in any alas name if I run this see my input was capital I and everything else was in small letter in the output you can see it has converted my input to all caps similarly you can change this let's say you want to print something in lower case you can use the lower function let's say this time everything is in upper case if I run it it converts India to lower case now let's explore a few date and time functions let's say you want to find the current date there's a function called C which stands for current and this is the function I'm talking about which is current date if I run this you will get the current date that is 28th of Jan 2021 and let's say you want to extract the D from a date value so you can use the D function let's say I'll use D and I want to find the D from my current date if I run this you get 28 which is today's day now similarly you can also display the current date and time so for that you can use a function that is called now so this will return the current date and time you can see this is the date value and then we have the current time all right and this brings us to the end of our demo session so let me just scroll through whatever we have learned so first I showed you how you can see the databases present in MySQL then we used one of the databases and checked the tables in it then we created another database called SQL intro for our demo purpose we used that database and then we created this table called employee details with column names like name integer the sex date of joints City and salary I showed you the structure of the database let me run this again so you get an idea you can see this was the structure of our table then we went ahead and inserted a few records so we inserted records for six employees so you have the employee name the age the gender the date of join the city to which the employe belongs to and the salary of the employee then we saw how you can use the select statement and display all the colums present in the table we learned how you can display the Unique city names we learned how to use different aggregate functions like count average and sum then we learned how you could display specific columns from the table we learned how to use wear Clause then we used an or operator we learned about in operator the between operator then we used an and operator to select multiple conditions finally we learned about group by order by and some basic SQL operations now it's time to explore some string functions in MySQL so I given a comment string functions first let's say you want to convert a certain string into upper case so I can write select the function I'll use is upper and within this function you can can pass in the string let's say I'll write India if you want you can give an alas name as let's say uppercase I'll give a semicolon and let's run it there you go so my input was in sentence case and using the upper function we have converted everything into upper case similarly let me just copy this and I'll show you if you want to convert a string into a lower case you can use the lower function I'll run this you can see the result everything is in lower case now of course I need to change the alas name to lower case instead of using lower as the function there is another function that MySQL provides which is called the L case so I'll just edit this and write L case and let's see I'll write India in uppercase let's run it it returns me the same result cool moving on let's say you want to find the length of a string you can use the character length function I'll write select use the function character length and I'm again going to pass in my string as India as let's say total length let's run it this time I'm going to hit control enter to run my SQL command there you go it has given us the right result which is five because India has five characters in it now these functions you can also apply on a table now let me show you how to do it let's say we already have the students table and you want to find the length of each of the student names so here you can pass sore name and you can give the same alas name let's say total length and then you can write from table name that is students if I run this you can see the output it has given me total 20 rows of information is not readable actually let me also display these student names so that we can compare their length all right I'll run this again and now you can see the result so Joseph has six characters NES has six vipul has five anubhab has seven similarly if you see Aki has six Tanish has seven ragav has six cins has seven rabada has six so on and so forth now instead of using this character length you can also use the function car length it will work the same way let's see the result there you go it has given us the same result you can either use character length or car length there's another very interesting function called concat so the concat function adds two or more expressions together let's say I'll write select I use the function concatenate the function is actually concat and I'm going to pass in my string values let's say India is in Asia let's run this and see our result you can see here it has concatenated everything let us make it more readable I'll give a space in between so that you can read it clearly now this is much more readable India is in Asia and if you want you can give an alas name as well as let's say merged there you go now the same concat operation you can also perform on a table I'm going to use the same students table let's say I want to return the student ID followed by the student name and then I am going to merge the student name followed by a space followed by the age of the student and I can give an alas as let's say name undor AG from my table that is students let's see how this works okay you see here the result is very clear we have the student ID the student name and the concatenated column that we created which was name undor each where we have the student name with a space followed by the age of the student if I scroll down you can see the rest of the results cool now moving ahead let's see how the reverse function Works in MySQL so the MySQL reverse function returns a string with the characters printed in reverse order so suppose I write select reverse I'll use the same string again let's say I have India let's run it you will see all the characters printed in reverse order again you can perform the same operation on a table as well let's say I'll write select reverse and I'll pass in the column as student name from my table that is students let's run it it gives you 20 students and all the names have been printed in reverse order okay now let's see what the replace function does so the replace function replaces all occurrences of a substring within a string within a new substring so let me show you what I mean I'll write select replace I'll pass in my input string which is let's say orange is a veget which is ideally incorrect I'm purposely writing this so that I can replace the word vegetable with fruit okay so what this replace function does is it is going to find where my word vegetable is within the string my input string and it is going to replace my word vegetable with fruit let's run it and see the output there you go now this is correct which is Orange is a fruit all right now my SQL also provides some trim functions you can use the left trim right trim and just the trim function so let me show you how this left trim Works left trim or L trim removes the leading space characters from a string passed as an argument so see I write select I'll use the left trim function which is lrim and then I'm going to purposely give a few Spees in the beginning of the string I'll give a word let's say India and then I'll give some space after the word India and see how the lrim works if I run this it gives me India which is fair enough but before that let's first find the length of my string so I'll use my length function here and within this function I'm going to find the length of my string which has India along with some leading and trailing spaces I'll paste this here give a semicolon and I'll run it okay so the entire string is 17 characters long or the length of the string is 17 now say I use lrim on my same string what it returns me is India and if I run length over it you can see the difference as in you can see how many spaces were deleted from the left of the string you can see here now it says 17 and I'm going to use lrim let's see the difference it gives me 12 the reason being it has deleted five spaces from the left you can count it 1 2 3 4 and 5 so 17 - 5 is 12 which is correct similarly you can use the rri function which removes the trailing spaces from a string trailing spaces are these spaces when you use left Rim it deletes the leading spaces which is this now let me just replace L trim with r trim which stands for right trim and see the result so the length is 10 now the reason being it has deleted seven spaces from the right of the string if you can count it one 2 3 4 5 6 and 7 cool you can also also use the trim function which will delete both the leading and the trailing spaces so here if I just write trim and I'll run it it gives me five because India is total five characters long and it has deleted all the leading and the trailing spaces all right there's also a function called position in MySQL the position function Returns the position of the first occurrence of a substring in a string so if the substring is not found with the original string the function will return zero so let's say I'll write select position I want to find where fruit is in my string that is Orange is a fruit I'll give an alas as name there some error here this should be within double quotes now let's run it and see the result okay it says at the 13th place or at the 13th position we have the word fruit in our string which is Orange is of fruit now the final function we are going to see is called asky so the aski function Returns the aski value for a specific character let's let's say I write select ask key of the letter small a if I run this it will give me the ask key value which is 97 let's say you want to find the ask key value of 4 let's see the result it gives me 52 all right in this session we are going to learn two important SQL statements or Clauses that are widely used that is Group by and having first we'll understand the basics of group by and having and then jump into my SQL workbench to implement these statements so let's begin first what is Group by in SQL so the group by statement or Clause groups records into summary rows and returns one record for each group it groups the rowes with the same group by item expressions and computes aggregate functions for the resulting group a group by Clause is a part of Select expression in each group no two rows have the same value for the grouping column or columns now below you can see the syntax of group by so first we have the select statement and Then followed by the column names that we want to select from we have the table name followed by the we condition and next we have the group by clause and here here we include the column names finally we have the order buy and the column names now here is an example of the group buy Clause so we want to find the average salary of employees for each department so here you can see we have the employees table it has the employee ID the employee name the age of the employee we have the gender the date on which the employee had joined the company then we have the department to which each of these employees belong to we have the city to which the employees belong to and then we have the salary in dollars so actually we'll be using this employees table on my SQL workbench as well so if you were to find the average salary of employees in each department so this is how your SQL query with Group by Clause would look like so we have selected department and then we are using an aggregate function that is AVG which is average and we have chosen the salary column and here we have given an alias name which is average _ salary which appears in the output you can see here from employees and we have grouped it by department so here in the output you can see we have the department names and the average salary of the employees in each department now let me take you to my MySQL workbench where we'll Implement Group by and solve specific problems okay so I am on my MySQL workbench so let me make my connection first I'll enter the password so this will open my SQL editor so first of all let me check the databases that I have so I'll use my query that is show databases let's run it okay you can see we have a list of databases here I'm going to use my SQL inro database so I'll write use SQL intro so this will take us inside this database I'll run it all right now you can check the tables that are present in SQL intro database if I write show tables you can see the list of tables that are already present in this database to do our demo and understand Group by as well as having let me first create an employee table so I'll write create table employees next I'll give my column name as employee ID which is the ID for each employee I'll give my data type as integer and I'll assign employee ID as my primary key next I'll give employee uncore name and my data typee would be varar I'll give the size as 25 my third column would be the age column age would obviously be an integer then I have my gender column I'll use character data type and assign a value of one or size of one next we have the date of join and the data type will be date we have the Department column as well this is going to be of varar and 20 will be the size next we have the city column which is actually the city to which the employee belongs to and finally we have the salary column which will have the salary for all the employees okay now let me select and run this this you can see here we have successfully created our table now to check if our table was created or not you can use the describe command I'll write describe employees you can see the structure of the table so far all right now it's time for us to insert a few records into this employees table so I'll write insert into employees and I'll copy paste the records which have already written on a notepad so let me show you so this is my EMP notepad and you can see I have already put the information for all the employees so let me just copy this and we'll paste it here all right let me go to the top and verify if all the records are fine all right so let's run our insert query okay so you can see here we have inserted 20 rows of information and now let's check the table information or the records that are present in our employees table I'll write select star from employees if I run it you can see here I have my employee ID the employee name age gender we have the city salary and in total we have inserted 20 records now let me run a few SQL commands to check how the structure of our table is let's say I want to see the distant cities that are present in our table so I'll write select distinct City from employees if I run this you see here there are total eight different cities present in our employees table so we have Chicago the Seattle Boston we have New York Miami and Detroit as well now let's say you want to know the total number of departments that are present so you can use dising Department if I run this all right you can see we have seven rows returned and here are the department names so we have sales marketing product Tech it finance and HR all right now let me show you another SQL command now this is to use an aggregate function so I want to find the average age of all the employees from the table so so I can write select AVG which is the aggregate function for average inside that I have passed my age column from employees if I run this so the average age of all the employees in our table is 33.3 now say you want to find the average age of employees in each department so for this you need to use the group by Clause I'll give a comment here I want to find the average age in each department so I'll write select Department comma I'll write average of age from employees Group by Department now if I run this you can see here we have our seven departments on the left and on the right you can see the average age of employees in each of these departments now you can see here in the output it says AVG of age which is not readable so I can give an alas name as average age all right I can bring this down and if you want you can round the values also so you can round the decimal places so I'll use a round function before the average function and the round function takes two parameters one is the variable and the decimal place you want to round it to so if I run this there you go you can see here we have the average age of all the employees in each of these departments all right now now suppose you want to find the total salary of all the employees for each department so you can write select Department comma Now I want the total salary so I'll use the sum function and I'll pass my column as salary from employees Group by Department let's run this query you can see here in the output we have the different departments and on the right you can see the total salary of all the employees in each of these departments now here also you can give an alas name as total unor salary let's run it again and and you can see the output here all right now moving ahead you can also use the order by Clause along with the group by Clause let's say you want to find the total number of employees in each City and group it in the order of employee ID so to do this I can use my select query I'll write select count of let's say employee ID and I want to know the city as well from employees Group by City And next you can use the order by Clause I'll write order by count of employee ID and I'll write DC which stands for descending if I run this query you can see here on the left you have the count of employees and on the right you can see the city names so in Chicago we had the highest number of employees working that was four then we had Seattle Houston Boston Austin and the remaining also had two employees so in this case we have ordered our result based on the count of employee ID in descending order so we have the highest number appearing at the top and then followed by the lowest okay now let's explore another example suppose we want to find the number of employees that join the company each year we can use the year function on the date of joining column then we can count the employee IDs and group the result by each year so let me show you how to do it so I'll write select I'm going to extract Year from the date of join column I'll give an alas as year next I'll count the employee ID from my table name that is employees and I'm going to group it by Year date of join I give a semicolon all right so let's run this great you see here in the result we have the year that we have extracted from the date of join column and on the right you can see the total number of employees that joined the company each year so we have in 2005 there was one employee similarly we have in 2009 there were two employees if I scroll down you have information of other years as well now if you want you can order this as well based on year or count okay now you can also use the group bu to join two or more tables together so to show you this operation let me first create a sales table so I'll write create table sales and the sales table will have columns such as the product ID which is going to be of integer type then we have the selling price of the product now this will be a float value then we have the quantity sold for each of the products so I'll write quantity quantity will of integer type next we have the state in which the item was sold and state I'll put it as worker and give the sizes 20 let's run this so that we'll create our sales table all right so we are successfully created our sales table next we need to insert a few values to our sales table so I've have already written the records in a notepad let me show you okay so here you can see I have my sales text file let me just copy these information I'll just paste it on the query editor okay now let me go ahead and run this insert command all right so you can see here we have successfully inserted nine rows of information so let me just run it through what we have inserted so the First Column is the product ID column then we have the selling price at which this product was sold then we have the quantity that was sold and in which state it was sold so we have California Texas Alaska then we have another product ID which is 1 12 3 and these are the states in which the products were sold so let me just confirm with the select statement I'll write select star from sales I run this you can see we have successfully created our table okay now suppose you want to find the revenue for both the product IDs one to one and let's say 1 to three since we have just two product IDs here so for that you can use the select query so I'll write select product ID next I want to calculate the revenue so revenue is nothing but selling price multiplied by the quantity so I'll use the sum function to find the total revenue and inside the sum function I'll use my selling price column multipied by my quantity column I'll give this an alas name as revenue from my table name that is sales finally I'll group it by product ID let's run it there you go so here you can see we have the two product IDs one 121 and 1 123 and here you can see the revenue that was generated from these two products all right now let's see we have to find the total profit that was made from both the products 1 to 1 and 1 to 3 so for that I'll create another table now this table will have the cost price of both the products so let me create the table first I'll write create table let's say the table name is C product which stands for the cost price of the products I'll give my first column as product ID this will be an integer and I'll have my second column as cost price cost price will have floating type values let's run this so we have successfully created our product cost table now let me insert a few values into the C product table so I'll write insert into ccore product I'll give my values for one to one let's say the cost price was $270 for each and next we have my product as 123 and let's say the cost price for product 1 12 3 was $250 let's insert these two values okay next we'll join our sales table and the product cost table so this will give us the profit that was generated for each of the products so I'll write select C do productor ID comma I'll write sum s do cellor price now here C and S are alas names so if I subtract my cost price from the selling price that will return the profit that was generated I'll multiply this with s do quantity close the bracket I'll give an alas name as profit from sales as s so here s stands for the sales table I'm going to use inner join ccore product table as the alas name should be C where s do productor ID is equal to C do productor ID we are using product uncore ID because this column is the common column to both the tables and finally I'm going to group it by C do productor ID all right so let me tell you what I have done here so I'm selecting the product ID next I'm calculating the profit by subtracting the cost price from the selling price and I multiplied the quantity column I'm using an inner joint to connect my sales and the product cost table and I am joining on the column that is product ID and I have grouped it by C do product ID let's run this there you go so here you can see for product ID 1 to1 we made a profit of $1,100 and for product ID 1 123 we made a profit of $840 so now that we have learned Group by in detail let's learn about the having clause in SQL the having clause in SQL operates on grouped records and returns rows where aggregate function results matched with given conditions only so now having and wear Clause are kind of similar but wear Clause can't be used with an aggregate function so here you can see the syntax of having Clause you have the select statement followed by the column names from the table name then we have have the wear conditions next we have the group bu finally we have having and at last we have order by column names so you can see here we have a question at hand we want to find the cities where there are more than two employees so you can see the employee table that we had used in our group by Clause as well so if you were to find the cities where there are more than two employees so this is how your SQL queries should look like so we have selected the employee ID and we are finding out the count using the count function next we have selected the city column from employees we have grouped it by City And then we have used our having Clause so we have given our condition having count of employee ID should be greater than two so if you see the output we have the different city names and these were the cities where the count of employees was greater than two all right so let's go to our MySQL workbench and Implement how having works so suppose you want to find those departments where the average salary is greater than $75,000 you can use the having clause for this so let me first run my table which is employees if I run this you can see we had inserted 20 rows of information and the last column we had was salary so the question we have is we want to find those departments where the average salary is greater than $75,000 so let me show you how to do it so I'll write select Department comma I'll use the aggregate function that is average salary I'll give an alas name as AV Gore salary from employees next we'll use the group by clause and I want to group it by each department and then I'm going to write my having Clause so in having Clause I'll use my condition that is having average of salary greater than $75,000 let's run it and see the output there you go so here you can see there were total three departments in the company that is sales finance and HR where the average salary is greater than $75,000 okay next let's say you want to find the cities where the total salary is greater than $200,000 so this will again be a simple SQL query so I'll write select City comma I want to find the total salary so I'll use the sum function and I'll pass my column as salary as I'll give a alas name as total from employees Group by City And then I'm going to use my having clause I'll pass in my condition as having sum of salary greater than $200,000 all right so let's run this query there you go so so the different cities are Chicago Seattle and Houston where the total salary was greater than $200,000 now suppose you want to find the Departments that have more than two employees so let's see how to do it I'll write select Department comma this time since I want to find the number of employees I'm going to use the count function I'll write count Star as employee uncore count or empore count which is my alas name from employees next I'll group it by Department have I'll give my condition count star greater than 2 let's run this okay so you have departments such as sales product Tech and it where there are more than two employees okay now you can also use a wear Clause along with the having clause in an SQL statement so suppose I want to find the cities that have more than 2 employees apart from Houston so I can write my query as select City comma count Star as EMP count from employees where I'll give my condition City not equal to Houston I'll put it in double quotes since I don't want to see the information regarding Houston I'll group it by City having count of employees greater than 2 so if I run this query you see we have information for Chicago and Seattle only and we have excluded the information for Houston now you may also use aggregate functions in the having Clause that does not appear in the select Clause so if I want to find the total number of employees for each department that have an average salary greater than $75,000 I can write it something like this so select Department comma count Star as EMP count from employees Group by department and in the having Clause I'm going to provide the column name that is not present in the cect expression so I'll write having average salary greater than 75,000 this is another way to use the having Clause let's run this all right you can see we have department sales finance and HR and you can see the employee count where the average salary was greater than 75,000 okay so let me run you from the beginning what we did in our demo so first we created a table called employee then we inserted 20 records to this table next we explored a few SQL commands like distinct then we used average and finally we started with our group by Clause followed by looking at how Group by can be used along with another table and we joined two tables that was sales and product cost table to find out the profit then you learned how to use the having Clause so we explored several different questions and learned how to use having an SQL in this session we will learn about joints in SQL joints are really important when you have to deal with data that is present on multiple tables I'll help you understand the basics of joints and make you learn the different types of joints with Hands-On demonstrations on my SQL workbench so let's get started with what are joints in SQL SQL joint statement or command is often used to fetch data present in multiple tables SQL joints are used to combine rows of data from two or more tables based on a common field or column between them now consider this example where we have two tables an orders table and a customer table now the order table has information about the order ID which is unique here we have the order date that is when the order was placed then we have the shipped date this has information about the date on which the order was shipped then we have the product name which basically is the names of different products we have the status of delivery whether the product was delivered or not or whether it was cancelled then we have the quantity which means the number of products that were ordered and finally we have the price of each product similarly we have another table called customers and this customer table has information about the order ID which is the foreign key here then we have the customer ID which is the primary key for this table we also have the phone number customer name and address of the customers now suppose you want to find the phone numbers of customers who have ordered a laptop now to solve this problem we need to join both the tables the reason being the phone numbers are present in the custom customer table as you can see here and laptop which is the product name is present in the orders table which you can see it here so using a join statement you can find the phone numbers of customers who have ordered a laptop now let's see another problem where you need to find the customer names who have ordered a product in the last 30 days in this case we want the customer name present in the customer table and the last 30 days order information which you can get from the order date column that is present in the orders table okay now let's discuss the different types of joints one by one so first we have an inner joint so the SQL inner joint statement returns all the rows from multiple tables as long as the conditions are met from the diagram ADB you can see that there are two tables A and B A is the left table and B is the right table the orange portion represents the output of an inner joint which means an inner join Returns the common records from both the tables now you can see the syntax here so we have the select command and then we give the list of columns from table a which you can see here is the left table followed by the inner join keyword and then the name of the table that is B on a common key column from both the tables A and B now let me take you to the MySQL workbench and show you how inner join Works in reality so here I'll type MySQL you can see I have got my SQL workbench 8.0 version installed I'll click on it it will take some time to open okay I'll click on this local instance and here I'll give my password okay so this is how SQL editor on MySQL workbench looks like so first of all let me go ahead and create a new database so I'll write create database this is going to be my command followed by the name of the database that is going to be SQL joints if I give a semicolon and hit control enter this will create a new database you can see here one row affected now you can check whether the database was created or not using show databases command if I run it here you can see I have SQL joints database created now I'll use this database so I'll write use SQL joints okay now to understand inner join consider that there is a college and in every College you have different teams for different sports such as cricket football basketball and others so let's create two tables cricket and football so I'll write create table and my table name is going to be Cricket next I'm going to create two columns in this table the First Column is going to be cricet ID then I'm going to give the data type PR int and use the auto increment operator I'm using Auto increment because my Cricket ID is going to be my primary key then I'm going to give the name of the students who are part of the cricket team and for this I'll use VAR card data type and give the length as 30 I'll give another comma and I'll assign my cricet ID as primary key within brackets I'll give Cricket uncore ID Cricket ID is nothing but a unique identifier for each of the players like you have role numbers in college okay let me just run it all right so we have successfully created our cricket table similarly let me just copy this and I'll paste it here I'll create another table called football this will have the information of all the students who are part of the football team and instead of cricket I am going to give this as football ID all right and the name column will have the names of the students I'll change my primary key to football ID all right let me run this okay so now we have also created our football table The Next Step step is to insert a few player names into both the tables so I'll write my insert into command first let's load some data to our cricket table so I'll write cricet and I'll give my name colum followed by values and here I'll give some names such as let's say Stewart we give another comma the next player I'll choose is let's say Michael similarly I'll add a few more let's say we have Johnson the fourth player I'll take is let's say Hayden and finally we have let's a Fleming okay now I'll give a semicolon and run this okay so let me just check if all the values were inserted properly for this I'll use select star from table that is Cricket if I run it you can see I have created a table and have successfully inserted five rows of information now similarly let's insert a few student names for our football table so I'll change this to to football and obviously there would be students who will be part of both cricket and football team so I'll keep a few repeated names let's say Stuart Johnson and let's say Hayden are part of both cricket and football team then we have let's say Langer and let's say we have another player in the football team that is astal I'll just run it okay you can see there are no errors so we have successfully inserted values to our football team as well let me just recheck it I'll write select star from football all right so we have five players in the football team as well okay now the question is is suppose you want to find the students that are part of both the cricket and football team in this case you can use an inner join so let me show you how to do it so I'll write select star from cricket as I'm using an alias name as C which stands for Cricket then I'm going to write inner join my next table is going to be football as F which is an alas name for the football table then I'm going to use the on command or operator and then I'll give the common key that is name here so C do name is equal to F do name So based on this name column from both the table my inner John operation will be performed so let's just run it there you go so Stuart Johnson and hiden are the only three students who are part of both the teams all right now you can also individually select each of the columns from both the tables so let's say I write select C do ccore ID comma C do name comma F do football uncore ID comma f. name from I'll write Cricket as C inner join football as F on C do name is equal to F do name now if I run this you see we get the same output here as well all right now let's explore another example to learn more about inner joints so we have a database called classic models let me first use classic models I'll run this okay now let me just show the different tables that are part of classic tables all right so here you can see there are tables like customers there's employees office there's office details orders payments products and product lines as well all right so let me use my select statement to show what are the columns present in the products table okay so this product table has information about different product names you have the product code now this product code is unique here we also have the product vendor a little description about the product then we have the quantity in stock buying price and MSRP let's see what we have in product lines if I run it you see here we have the product line which is the primary key for this table then we have the textual description for each of the products this is basically some sort of an advertisement all right now suppose you want to find the product code the product name and the text description for each of the products you can join the products and product lines table so let me show you how to do it I'll write my select statement and choose my columns as product code then we have product name and let's say I want the text description so I'll write this column name okay then I'll use from my first table that is products inner join product lines I can use using the common key column that is product line close the bracket I'll give a semicolon and if I run it there you go so you can see the different product codes then we have the different product names and the textual description for each of the products so this we did by joining the products table and the product lines table all right now suppose you want to find the revenue generated from each product order and the status of the product to do this task we need to join three tables that is orders order details and products so first let me show you what are the columns we have in these three tables we have obviously seen for the products table now let me show you for orders and Order details table so I'll write select star from orders if I run it you can see it has information about the order number the date on which the order was placed we also have the shipment date we also have the status column which has information regarding whether the order was shipped or cancelled then we have some comments column we also have the customer number who ordered this particular product similarly let's check what we have under order details so I'll write select start are from order details if I run it you can see it has the order number the product code quantity of each product we have the price of each product then we have the orderline number okay so using the product orders and Order details let's perform an iner join so I'll write select o do order number comma o dot status comma I need the product name which I'll take from the products table so I'll write P do product name now here o p are all alas name for the tables orders products and I'll use OD for order details comma since we want to find the revenue we actually need to find the product of quantity ordered into price of each product so I'll use a sum function and inside the sum function I'll give quantity ordered multiplied by the price of each item I'll use an alas as Revenue then I'll use my from Clause from orders as o inner join order details as I'll use an alas name as OD on I'll write o do order number is equal to OD dot order number I'll use another inner join and this time we'll join the products table so I'll write inner join products as p on P do product code is equal to OD do product code and finally I'll use the group by clause and group it by order number all right let me run this okay there's some mistake here we need to debug this it says you have an error in your SQL syntax check the manual all right okay I think the name of the tables is actually orders are not order all right now let's run it okay there's still some error it says classic models. product doesn't exist so again the product name is I mean the table name is products and not product so let's run it again all right there you go so we have the order number the status the product name and the revenue this we got it using inner join from three different tables now talking about left joins the SQL left join statement returns all the row from the left table and the matching rows from the right table so if you see this diagram you can see we have all the rows from the left table that is a and only the matching rows from the right table that is B so you can see this overlapped region and the Syntax for SQL left join is something like this so you have the select statement and then you give the list of columns from table a which is your left table then you use the left join keyword followed by the next table that is table B on the common key column so you write a do key is equal to B do key okay now in our classic models database we have two tables customers and orders so if you want to find the customer name and their order ID you can use these two tables so first let me show you the columns that are present in customers and orders I think order ERS we have already seen let me first show you what's there in the customers table okay so you can see we have the customer number the name of the customer then we have the contact last name the contact first name we have the phone number then there's an address column there are two address columns actually we have the city name the state and we have other information as well and similarly we have our orders table so I'll write select start from orders so I'll write select star from orders if I run this you can see these are the information available in the orders table okay so let's perform a left join where we want to find the customer name and their order IDs so I'll write select C do customer name or let's say first we'll choose the customer number comma then I want the customer name so I'll write C do customer name then we have the order number column which is present in the orders table and let's say I also want to see the status then I'll give my left table that is customers as C left join orders as o on C do customer number equal to O do customer number let's run it okay again there is some problem all right so the table name is customers let's run it so there's another mistake here this is customer number so B is missing cool let me run it all right so here you can see we have the information regarding the customer number then the respective customer names we have the order number and the status of the shipment so if I scroll down you'll notice one thing there are a few rows you can see which have null values this means for customer number 125 and for this particular customer name there were no orders and similarly if I scroll down down you will find a few more null values you can see here there are two null values here for customer number 168 and 169 there were no orders available all right now to check those customers who haven't placed any orders you can use the null operator so what I'll do is here I'll just continue with this I'll use a where clause and write where order number is is null now let me run this okay so here you can see there are 24 customers from the table that don't have any orders in their names okay now talking about right joins so SQL right join statement returns all the rows from the right table and only matching rows from the left table so here you can see we have our left table as a and the right table as B so the right join will return all the rows from the right table and only the matching rows from the left table now talking about the syntax so here you can see we have the select statement followed by the select statement you'll have the list of columns that you want to choose from table a right join table B on the common key column from both the tables all right now to show how right join works I'll be using two tables that is customers and employees so let's see the rows of data that are present in the customer table first so I'll write select star from customers let's run it so here you have the customer number the customer name then we have the phone number the address of the customers you also have the country to which the customer belongs to the postal code and the credit limit as well similarly let's see for the employees table here I'll change customers to employees let's run it okay so we have the employee number the last name the first name we have the extension the email ID the job title and also reports to here means the manager okay so based on these two tables we'll find the customer name the phone number of the customer and the email address of the employee and join both the tables that is customers and employees so let me show you the command so I'll write select C do customer name comma then we have C do phone I'll give a space here next I want the employee number from the employee table so I'll write e do employee number comma e do email from customers as C right join employees as e on E do my common key column is employee number here so I'll write e do employee number is equal to C dot we have sales representatives employee number and I'm also going to order it by the employee number column okay so you can see I have my customer name selected from the customer table the phone number of the customer then we have the employee number and the email address so let me run it okay there's some problem all right so the table name is customers actually let's run it once again there you go so you can see here we have all the values selected from our right table which is the employees table you can see right on employees which means your employees table is to the right and then we have the customer name and phone number numbers of the customers from the customer table which is actually your left table so you have a few employee number such as one2 this 1056 which don't have any customer name or phone numbers okay so there's another popular join which is very widely used in SQL known as self joins so self joins are used to join a table to itself so in our database we have a table called employees let me show you the table first all right so here you can see we have the employee number the last name the first name of the employee we have the email ID and here if you see we have a column called reports to now this you can think of as the manager column so the way to read is for example for employee number 1056 the manager is one2 so if you check for 1 do we have Dane Murphy then if I scroll down let's say for employee number 1102 yeah for employee number 1102 the manager is 1056 so here you can see who is at 1056 you have Mary Patterson similarly if I scroll down let's say for employe number 11 188 we have the manager as 1143 now if I check the table at 1143 we have Anthony B so so the employee Julie feli reports to Anthony bow all right now suppose you want to know who is the reporting manager for each employee so for that you can use a self jooin so let me show you how to join this employees table I'll I'll write select and then I'm going to use a function called concat within brackets I'll start with my alas name that is m dot then I'll write last name I'm going to conat last name followed by a comma then I'll have my first name I'll close this bracket and then I'm going to give my alas name let's say manager comma next I'm going to concat the same last name and first name and this time I'm going to use a separate alas let's say e which stands for employee so I'll write e do last name comma and within single codes I'll give my comma and then I'll write e do first name I'll close this bracket I'll give an alas as let's say employee from I'll write employees as e inner join employees at as M on M do I'll use my common key column as employee number so I'll write M do employee number is equal to e dot here I'm going to use the reports 2 column and then I'll order it by let's say manager okay now let's run this there you go so you have your two columns as manager and employee so for employee Louie bonder the manager is zarad Bounder similarly if I scroll down you have there are multiple employees reporting to this particular manager similarly we have our manager as Anthony B and we have different employees who are reporting to this particular manager and so on all right now moving ahead now let's see what a full join is so SQL full outer join statement returns all the rows when there is a match in either left or right table now you must remember that MySQL workbench does not support full outer join by default but there's a way to do it so by default this is how the syntax of full outer join looks like like now this statement will work on other SQL databases like Microsoft SQL server but it won't work on MySQL workbench I'll show you the right way of using full outer join on my SQL workbench so to show full outer join I'm going to first use a left join and then we'll also use a right join and finally we'll use a union operator so the union operator is used to combine the result set of two or more select statements so first of all let me write C do customer name so for this example I'm using the customer table and the order table comma o do order number so I just want to know the customer name and the order number related to the customer from I have customers as C left join I'll write orders as o on C do customer number is equal to O do customer number let me just copy this and after this I'm going to use my union operator so Union operator is used to merge results from two or more tables so basically this performs a vertical join and next I am going to use my right join operation so here instead of left join I'll write right rest all looks fine let me just run it there you go so we have successfully run our full outer join operation you can see we have the different customer names and the order that each customer had placed all right so that brings us to the end of our demo session so let me just run through whatever we did in this session so first we created a database called SQL joints then we created two tables like cricket and football then we had inserted a few rows to each of these tables then we used this table to learn about inner join next we used a database called classic models it had multiple tables so we explored all of these tables like products there was product lines orders customers and employees and learned how to use inner join left join self join right join as well as full outer join in this video we will learn what is a subquery and look at the different types of subqueries then we learn subqueries with select statement followed by subqueries with insert statement moving further we will learn subqueries with the update statement and finally we look at subqueries with delete statement all this we will be doing on our MySQL workbench so before I begin make sure to subscribe to the simply learn Channel and hit the Bell icon to never miss an update so let's start with what is a subquery so a subquery is a select query that is enclosed inside another query so if I show you this is how the basic structure of a subquery looks like so here whatever is present inside the brackets is called as the inner query and whatever is present outside is called the outer query so first the inner query gets executed and the result is returned to the outer query and then the outer query operation is performed all right now let's see an example so we have a question at hand which is is to write a SQL query to display Department with maximum salary from employees table so this is how our employees table looks like it has the employee ID the employee name age gender we have the date of joint Department City and salary now to solve this query my subquery would look like this so I'll first select the department from my table that is employees where I'll use the Condition salary equal to and then I'll pass in my inner query which is Select Max of salary from employees so what this does is it will first return the maximum salary of the employees in the table then our outer query will get executed based on the salary returned from the inner query so here the output is department sales has the maximum salary so one of the employees from the sales department earns the highest of the maximum salary if you see in our table the employe is Joseph who earns $115,000 all right and Joseph is from the sales department now let's see how this query works so here we have another question which is to find the name of the employee with maximum salary in the employees table so this is our previous employees table that we saw and to find the employee who has the maximum salary my subquery would look something like this so I'm selecting the employee name from my table that is employees where I'm using the Condition salary equal to and then I'm passing in my subquery or the inner query so first I'm selecting the maximum salary this will return a particular value that is the highest salary from the table and if you see our table the highest salary is $115,000 so our query becomes select employee name from employees where salary equal to $15,000 so the employee name is Joseph here and that's the output now if you want to break it down here you can see first the inner query gets executed so our SQL query will first execute the inner query that is present inside brackets select maximum salary from employees the result is $115,000 and then based on the returned result our outer query gets executed so the query becomes select employee name from employees where salary equal to $15,000 and that employee is Joseph all right now we'll learn the different types of subqueries so you can write subqueries using select statement update statement delete and insert statement we'll explore each of this with the help of example on my MySQL workbench so let's learn sub queries with the select statement so subqueries are majorly used with the select statement and this is how the syntax looks like you select the column name from the table name then you have the WHERE condition followed by the columns that you want to pass the operator and inside that you have this subquery so here is an example that we will perform on our MySQL workbench so in this example we want to select all the employees who have a salary less than average salary for all the employees this is the output so let's do this on my MySQL workbench all right so let me log into my local instance I'll give my password okay so you can see I'm on my MySQL workbench so let's start by writing our subquery using the select statement okay so for this demo session we'll be using a database that is subqueries you can see it here I have a database called subqueries so I'll use this subqueries database and we'll create a few tables as well okay if I run it now we are inside the subqueries database so let me just show you the tables that are present inside this database I'll write show tables if I run it okay there are two tables employees and employees _ B uh we'll use this table throughout our demo session all right right now for our select subquery we want to fetch the employee name the department and the salary whose salary is less than the average salary so we will be using the employees table so let me first show you the records and the columns we have in the employees table so I'll write select star from employees and run it okay you can see here we have 20 rows of information we have the employee name the employee ID age gender date of joint Department City and salary so this is the same table that we saw in our slides okay now for our subquery I'll write select I want to choose the employee name the department and the salary there should be a comma here instead of a period next I'll give my table name that is employees where my salary is less than and after this I'll start my inner query or the sub query I'll write select average salary so I'm using the AVG function to find the average salary of all the employees from my table that is employees if I give a semicolon and run this you'll see the output so we have total 12 employees in the table whose salary is less than the average salary now if you want you can check the average salary so the average salary is $753 now the employees who have a salary less than the average salary so these are the people all right now moving back to our slides okay now let's see how you can use subqueries with the insert statement now the insert statement uses the data return from the subquery to insert into another table so this is how the syntax looks like so you write insert into table name followed by select individual columns or start from the table use the wear clause and then you give the operator followed by the inner query or the subquery so here we will explore a table called products table we are going to fetch few records from the products table based on a condition that is the selling price of the product should be greater than $100 so only those records will fetch and put it in our orders table all right so we are going to write this query on my my SQL workbench so let's do it I'll give my comment as update subquery all right so first of all let's create a team that is products so I'll write create table products then we'll give our column names the First Column would be the product ID of type integer then we have the column as item or the product which is of type Vare 30 next we have the selling price of the product the selling price will be of type float and finally we have another column which is called the product type and again product type is of the data type V car I'll give the size as 30 close the bracket and give us semicolon now let's just run it okay so we have successfully created our products table now let's insert a few records to our products table so I'll write insert into products followed by values I'll give four records the first product ID is 101 the product is let's say jewelry then the selling price is let's say $800 and the product type is it's a luxury product next let's insert one more product detail the product ID is 102 the product is let's say t-shirt the price is let's say $100 and the product type is non-luxury [Music] next I'll just copy this to reduce our task we'll edit this the third product's ID is 1 103 the product is laptop and let's say the price is $300 and it's a luxury product I'll paste again and finally I'll enter my fourth product which is let's say table and the price is $400 and it's a non-luxury product I'll give a semicolon and we'll insert these four records to our products table you can see we have inserted four records let's just print it now so I'll write select star from products if I run it you can see we have our four products ready now we need to create another table where we are going to put some records from our products table so that new table is going to be the orders table so I'll write create table orders now it will have three columns the order ID order ID will be of type integer then we have product underscore sold this will be of type varing character of size 30 and finally we have the selling price column this will be of type float let's create our orders table the table name should be orders and there is some mistake here okay should close the brackets okay let me run it so we have our orders table ready now let's write our insert subquery so I'm going to insert into my table that is orders and I'll select the product ID comma the item and the selling price or the sell price from my table that is products where I'll write product ID in I'll write my inner query select prodad ID or the product ID from products next I'll give a wear Clause where the selling price is greater than $1,000 so let me tell you what I'm going to do here I'm going to insert into to my orders table the product ID the item name and the selling price from my products table where the product ID has this condition so let me first run this condition for you which is Select prod ID from products where the selling price is greater than 1,000 if I run this okay there is some issue here the column name is actually prodad ID now let's run it again so that we can see the product IDs of the products which have a selling price greater than 1,000 so it is 101 and 103 now let's run the entire query there is another mistake here let's debug the mistake now this should be product ID instead of productor in let's insert again all right so we have successfully inserted two records to our table that is orders now let's see the orders table I'll write select star from orders if I run it there you go so there were two products from our product table that were jewelry and laptop which have a selling price greater than $1,000 so the selling price for jewelry was $1,800 and for laptop it was $1,300 so this is how you can use a subquery using the insert statement all right now going back to our slides again all right now let's see how you can use subqueries with the update statement now the subqueries can be used in conjunction with the update statement so either single or multiple columns in a table can be updated when using a subquery with the update statement so this is how the basic syntax of an update subquery looks like so you write update table followed by the table name you set the column name you give the we operator and then you write your inner subquery so we are going to see an example where we will use this employees table and using this employees table we will update the records of the salaries of the employees by multiplying it with a factor of 35 only for those employees which have age greater than 27 so we are going to use a new table called employees Corb for this as well so let's see how to do it so I'll give my comment as update subquery before we see the subquery let's see what we have in the table employees Corb this is basically a replica of the employees table there you go it has the same records that our employees table has we are going to use both the employees table and the employees _ B table to update our records so I'll write update employees set salary equal to let me bring this to the next line I'll write set salary equal to salary multiplied by 35 where age in then I'll write select age from my other table that is employees B where age is greater than equal to let's say 27 all right so let me run through this query and tell you what we are going to do so I'm going to update the records of the employees table specifically for the salary column so I'm checking if the age is greater than 27 then we'll multiply the salaries of the employees with a factor of. 35 in the employees table let me just run this then we'll see our output okay so it says 18 rows affected which means there are total 18 employees in the table out of the 20 employees whose age is greater than 27 now if you see I'll write select star from employees you can see the difference in the salaries if I scroll to the right you can see these are the updated salaries okay now if you check for employees who have an age less than or equal to 27 for example Marcus whose age is 25 his salary is the same we haven't updated his salary then if you see if we have okay there is one more employee Maya we haven't updated the salary of Maya because the age is less than 27 all right now let's go back to our slides again as you can see we got the same output on our MySQL workbench now let's explore how you can write subqueries with the delete statement now subqueries can again be used in conjunction with the delete statement so this is how the basic syntax of a delete query using subquery would look like you write delete from the table name where Clause the operator value followed by the inner query in within brackets so here we are going to use the employees table and what we are going to do is we'll delete the employees whose ages greater than equal to 27 so let's see how you can do it all right so I'll give my comment as delete subquery so we'll follow the syntax that we saw I'll write delete from my table name that is employees I'll write where age in and then I'll start my inner query or the subquery I'll write select age from employees uncore B where AG is let's say greater than equal to 32 or let's say the AG is less than equal to 32 close the bracket and I'll give my semicolon let me first run the inner query for you so that you get an idea of the employees who are less than 32 years of age so there are nine employees in the table who have an age less than equal to 32 so we are going to delete the records if if I run this okay it says nine records deleted now let's print or display what we have in the employees table if I run this there you go so if you see the age table we have total 11 employees now and all the ages are greater than 32 because we have deleted all those employees who had an age less than equal to 32 okay so let me show you from the beginning what we did so first we used our subqueries database then we used our employees table so we started by looking at how you can use the subquery with a select statement this should be insert instead of update so we learned how to write an insert subquery we use two tables products and our orders table moving ahead we saw how to write subqueries using the update command so we updated the salaries of the employee by a factor of 35 for those who had an age greater than equal to 27 and finally we saw how to use the subquery using the delete statement so we deleted all those records for the employees whose age was less than equal to 32 in this video we will discuss an advanced SQL concept called triggers we will learn what triggers are and how they can be used to solve problems we look at how trigger queries are written on my SQL workbench so let's get started first what are triggers in SQL so a trigger is a userdefined SQL command that is invoked automatically when an event occurs an event can be related to inserting updating or deleting records in a table in MySQL triggers can be executed automatically by the Oracle server below is how the syntax of writing a trigger is so you write create trigger followed by the trigger name now the trigger name should be a valid SQL name then we have the trigger time the trigger event and then on the name of the table then we write for each row begin followed by the list of SQL statements and finally we write end now in the syntax the trigger time represents either before or after an event and a trigger event can be an insert update or delete operation now let me take you to my SQL workbench where we will learn how to create triggers and Explore More on how it works so I am on my SQL workbench so first let me go ahead and create my connection I'll give my password all right now this will take you to my SQL editor okay so first and foremost let me create a new database called triggers so I'll write my SQL command as create database triggers let me run this okay so you can see here one row affected I have created my database that is triggers next I'm going to move inside this database so I'll write the command use triggers let's run this okay now inside this triggers database we'll be creating our different tables so let's check if we have any tables I'll use the command show tables now you see there are no tables in this database all right first let's see how before insert trigger works so for before insert trigger we'll create a table called customers and verify the age of each customer so I'll give a comment before insert trigger so let me go ahead and create my customers table I'll write create table customers and I'll give my column names as custor ID which is customer ID this is going to be an integer then we have the age of the customer this is again an integer and we'll have the name of the customer the name of the customer will be of varart type I'll give the size as 30 all right let's create this table okay so we have successfully created our customers table now move moving ahead I'm going to write my before insert trigger command so we'll start with a D limiter so a d limiter is a marker for the end of each command next I'll write my create trigger followed by the trigger name I'll give a valid trigger name such as _ verify I'll write before trigger it should be before insert on my table name would be customers then I'll write for each row if new DOT each is less than zero then set new. AG equal to 0er which means while inserting your values to the age column in case the age value is negative we'll convert the age to zero I'll give a semicolon and then I'll write end if I'll give another semicolon and I'll close the delimer now let me run this okay so we have created our trigger now let's insert a few values onto customers so I'll write insert into customers values 101 which is the customer ID I'll give the age as 27 and let's say the name of the customer is James let's insert another record give the value as 102 and this time I'm purposely giving the age as minus 40 Which is less than zero so that you can see the output clearly I've given the customer name is Amy let's insert two more records the customer ID with 103 has an AE of 32 and let's see the name is Ben we give another comma and finally we'll insert our fourth customer record the customer ID is 104 the age I'm giving as minus 39 Which is less than zero again and let's say the customer name is Angela all right now let me run this okay so we have successfully inserted four rows of information to our customers table now let's see the difference since we had created a age verifi trigger which would change the values of the age column in case the age value is less than zero so now that we have inserted four records to a customer so let's see what we have in the customers table now let me run it there you go so if you see this for those customers where the age was less than zero such as 102 and 103 you can see here our trigger has automatically converted those values to zero if you can see the trigger we had created we had set a new age to Z if the age was less than zero and this is the output we get so here we looked at how a before insert trigger works all right now moving ahead let's understand how an after insert trigger works so I'll give a comment here as after insert trigger now in this example we'll create two tables customers and message now the trigger will be fired if the birthday column has a null value in the customer table you will get a message to update your date of birth okay so first let me create my customers table now since we already have one customers table I'll name it as customers one and let me give the column names so the First Column would be the ID column this is going to be an integer and I'll use the autoincrement parameter and this will be my primary key as well next we'll have the name of the customer this is going to be of type varar and I'll use not null constraint next we'll have the email ID of the customer this is again going to be of varar type and the column we are interested in is the birthday column so I'll write birth date this is going to be of type date okay so let's create our table all right now we'll create our second table which is going to be message let's create the columns this will have ID int and I'll use the auto increment operator next next we'll have the message ID this is going to be of integer type we'll have a column called message this will be of VAR card type let's say 300 characters and not null and I'm going to set my primary Keys as ID and message ID let's create our second table then so we have some error here let's debug the error okay so I forgot to close the bracket here all right now let me run this table all right so we have created our message table as well next I'll write my after insert trigger command so I'll write D limiter next I'll use create trigger command so I'll write create trigger my trigger name would be check underscore nullcore date of birth after insert on customers one for each row begin if new DOT birth date is null then we are going to insert into our message table we give the columns as message ID and message values new. ID comma I'm going to use the concat function where I'll give my message let's say hi followed by the name followed by let's say please update your date of birth okay now I'll write end if I'll close the delimiter okay so let me go to the top and recheck if you have created a trigger correctly so I have my trigger name after insert on customers one for each row begin if our new birthday has a null value then we'll pass in a message saying hi the name of the customer then the message would be please update your date of birth all right so let's run the trigger okay now the next step is to insert a few records to customer table so my name of the table is customers one okay I'll give the column names as name email and birth okay we'll pass in a few values so our first customer's name let's say is Nancy comma I'll give my email ID let's say Nancy atate abc.com now let's say I'm purposely passing the date of birth as null I'll give a comma my next customer let's say the name of the customer is Ronald and then I'll give the email ID such as Ronald at the rate xyz.com and this time I'm going to pass in the date of birth let's say 1998 November and let's say 16th okay now we'll add another customer let's say chish we'll give the email ID of Chris so Chris at the xyz.com and let's say we'll give the date of birth as 1997 08 20 and finally we'll add one more record for another customer let's say Alish I'll give the email ID as Alish at the abc.com and this time I'll purposely pass the date of birth as null all right so let's run the insert query okay now let me display the message table for you so that you can see the output and how our after insert trigger works there you go so if you see this we had inserted four rows of information out of which the first customer ID and the fourth ID had null date of birth so for those two values you have the messages High Nancy please update your date of birth and then you have high Alis please update your date of birth now you can give a space between High and the name of the customer all right now moving ahead let's understand how before update trigger works so I'll give a comment here as before update okay now to show you how before update works first we'll create a table called employees all right so let me create my table that is create table employees I'll give my column as employee ID this is going to be an integer type and it will be my primary key next we'll have the employee name this will be of varar type and let's say the size would be 25 comma then we'll have the age of the employee age will be of integer type and finally we'll have the salary of the employee salary will be of type float let's create this table all right now the next step is to insert a few records to our employees table okay so here on the notepad I have already written my insert query so I have inserted nearly seven records so let me paste it here all right I'll just go ahead and run this insert query so that we'll insert seven employees to our table okay next we'll write our before update trigger command so first and foremost I'll write my D limiter followed by the create trigger command I'll give my trigger name as update uncore trigger let's bring this to the top okay I'll use before update on my table that is employees for each row I'll write begin my condition would be if new dot salary is equal to let's say $10,000 I'll write then set new DOT salary equal to $85,000 I'll give a semicolon then I'll use LF new do Sal is less than $110,000 then I'll set new DOT salary to let's say $72,000 I'll give a semicolon I'll end the if statement I'll close the delimiter by giving a semicolon okay so if you see the employees table we have the salary values here now in case while updating the table if the salary is equal to $10,000 we'll set the new salary to $85,000 else if the salary is less than $10,000 we'll set the salary column values to $72,000 so let's go go ahead and run this trigger query all right now let me use my update command so I'll write update employees I'll write set salary is equal to let's say $8,000 now if you see our trigger query we are setting the salary to $8,000 which is clearly less than $10,000 so our new salary would be $72,000 so let me run this okay so you can see here six rows affected so I'll write now select star from employees let's run this okay if you see this we have converted our salary column to $72,000 since we updated the salary to $88,000 and according to our trigger query if it's less than $10,000 the salary should be $72,000 which you can see here all right now moving to our final demo this time we are going to explore how before delete trigger command works so in this example we'll create a table called salary we'll store the deleted record in a new table using the before delete trigger so first I'll give my comment as before trigger comments are always useful since they make your query more readable so let me now create my salary table so I'll write create table salary I'll give my column names as Eid which is m employee ID and I'll set it as primary key comma I'll give another column let's say valid from which means the date from which this salary was effective this going to be of type date and not null and then I'll have the amount amount will be of type float and again not null constraint let me run the create table command okay so we have created our salary table now the next thing is to insert a few values so I'll write insert into salary I'll give my column names as Eid comma valid from comma amount I'll write values my first employee ID would be 101 followed by a date let's see 2005 me and 01 which is 1st of May 2005 let's say the amount is $55,000 I'll give another row of information this time the employee ID is two and I'll give a valid from date let's say the amount is 68,000 I'll give another employee ID let's say 103 2006 091 and let's say the amount is $75,000 follow okay let's run this all right you can verify also so I'll write select star from salary you can see here we have inserted three rows of information all right the next step is to create another table that will store the deleted rowes so I'll write create table let's say the name of the table is create or let's say salary delete I'll write salary deel now this will have the ID the type would be integer and primary key I'll also use the autoincrement operator next we have the Eid which is of type integer I'll write valid from date and not null then I have the amount column which is of type float and not null my final column would be deleted at the data type will be timestamp I'll use the default constraint as now so this will return the time and the date at which the record was deleted all right let me create this table okay finally let me write my before delete trigger command so I'll first declare my delimiter let's say this time I'm using the dollar symbol I'll write create trigger my trigger name would be salary undor delete before delete on my table that is salary for each row I'll write begin next let's insert into salary delete I'll give my columns as employee ID valid from and amount now we'll insert a few values I'll write values Old Dot Eid comma Old Dot valid from comma old do amount I'm going to close with the delimiters all right so let's run our before delete trigger all right now let me go ahead and delete one of the records from the salary table so I'll write delete from salary where employee ID is let's say 103 let me run this query okay so you have successfully deleted one of the records in the table now if you see our other table that is salary delete you will find this information present in the salary delete column so we had deleted one row of information that is for employee ID 103 which you can find it here now the final column that is delete at shows the time at which the information was deleted or the row was deleted all right so let me go to the top and just give you a recap of what we did in this demo so first we created a database called triggers then we started with before insert trigger where we inserted customer table and then we verified the age of the customers so we saw if the age was less than zero then we set the age to zero we inserted a few records to the customer table and purposely we inserted some negative age records and then we converted this age records to zero then we learned how an after insert trigger works so we created two tables customers 1 and message so wherever the customer's date of birth was missing we passed in a message saying hi the name of the customer please update your dat of birth all right now moving ahead we saw how before update trigger works and finally we learned about before delete trigger so in this video we will learn sub queries in SQL we'll also have a look at stored procedures and learn about triggers in SQL we'll cover views in SQL and look at some of the important Windows functions in SQL now to learn all of these we we will be using the MySQL workbench on windows so let's get started with subqueries in SQL so let me head over to my myql workbench So currently I am on my MySQL workbench let me connect to the local instance so I'll give my password I'll click on okay all right so this is my MySQL workbench query editor so first we are going to learn sub queries let me give a comment and write subqueries all right so first of all let's understand what a subquery is so a subquery is a query within another SQL query that is embedded within the we Clause from clause or having Clause so we'll explore a few scenarios where we can use subqueries so for that I'll be using my database that is SQL intro so I'll write my command use SQL unor intro now this database has a lot of tables I'll be using the employees table that is present inside SQL intro Let me just expand this and you can see here we have an employees table so let me first show you the contents within this table I'll write select star from employees let me execute it okay you can see here we have the employee ID employee name e age gender there's date of join Department City and salary and we have information for 20 employees if I scroll down you can see there are 20 employees present in our table so let's say you want to find the employ whose salary is greater than the average salary in such a scenario you can use a subquery so let me show you how to write a subquery I'll write the select statement in the select statement I'll pass my column names that I want to display so the column names I want are the employee name then I want the department of the employee and the salary of the employee from my table name that is employees next I'll use a where condition where my salary should be greater than the average salary of all the employees so I'll write salary greater than after this I'm going to write my sub query so I'll give select average of salary from my table name that is employees and I'll close the bracket and give a semicolon so what it does is first it is going to find the average salary of all the employees that are present in our table once we get the average salary number we'll use this where condition where salary is greater than the average salary number so the inside subquery let me run it first if I run this this gives you the average salary of all the employees which is $75,400 now I want to display all the employees who have salary greater than $75,400 so let's run our subquery there you go so there are eight employees in our table who have a salary greater than the average salary of all the employees all right next let's see another example suppose this time you want to find the employees whose salary is greater than Jon's salary so we have one employee whose name is John let me run the table once again okay if if I scroll down you see we have an employee as John you see this our employee ID 116 is John and his salary is $67,000 I want to display all the employees whose salary is greater than John's salary so basically all the employees who are earning more than $65,000 I want to print them so let's see how to do it I'll write select I want the employee name comma the gender of the employee I also want the department and salary from my table name that is employees I'll write where salary is greater than I'll start my opening bracket inside the bracket I'm going to give my inner query that is Select salary from employees where the employee name is John So within single quotations I'll give John as my employee I'll end with a semicolon so let me first run my inner query so this will give us the salary that John has which is $67,000 now I want the employees who are earning more than $67,000 so let's run our subquery okay so you can see 12 rows returned which means there are 12 employees in our table who are earning more than $67,000 you see here all these employs have a salary greater than $67,000 okay now you can also use subqueries with two different tables so suppose you want to display play some information that are present in two different tables you can use subqueries to do that so for this example we'll use a database that is called classic models you can see the first database so let me use this database called classic models I'll write use classic models now this database was actually downloaded from the internet there's a very nice website I'll just show you the website name so this is the website that is MySQL tutorial.org you can see here they have very nice articles blogs from where you can learn my SQL in detail so we have downloaded the database that is classic models from this website you see here they have a MySQL sample database if you click on this it will take you to the link where you can download the database so they have this download link which says download my SQL sample database and the name of the database is classic Models All right so we are going to use this classic models database throughout our demo session if I expand the tables section you can see there are a lot of tables that are present inside this classic models database we have cricet customers as employees office there's orders order lines and many more so for our subquery we'll be using two tables that is order details and products table first let me show you the content that is present inside the products table first if I run this you see here it says 110 rows returned which means there are 110 different products that are present in our table which has the product code the product name product line we have the product vendor description quantity in stock Buy price MSRP the other table we are going to use is order details which has the details of all the orders let me show you the records order details tables has okay so there are thousand records present in this table you have the order number the product code quantity ordered price of each item you have the order line number as well okay now we want to know the product code the product name and the MSRP of the products whose price of each product is less than $100 for this scenario we are going to use two different tables and we are going to write a subquery okay so if you see here in the order details table we have a column called price each I want to display the product code the product name and the MS RP of the products which have a price of each product less than $100 so the way I'm going to do is I'll write select product code comma product name now one thing to remember that this product name is actually present inside our products table and product code is present in both the tables that is products and audit details here you can see this is the product code column comma MS RP which is present inside the products table again from my table that is products where I'll write product code I'm going to use the in operator next I'll write my inner query that is Select product code from my table order details where my price of each product is less than $100 let me run this okay so you can see there are total 83 products in our table which have a price less than $100 you can see the price here okay now we learn another Advanced Concept in SQL which is known as stored procedures I'll just give a comment saying stored procedure okay so first let's understand what is a stored procedure a stored procedure is an SQL code that you can save so that the code can be reused over and over again so if you you want to write a query over and over again save it as a stored procedure and then call it to execute it so in this example I want to create a stored procedure that will return the list of players who have scored more than six goals in a tournament so I have a database called SQL IQ these are a few databases that I've already created so this database has a table called players if I expand the tables option you see we have a table called players and you can see the columns player ID the name of the player the country to which the player belongs to and the number of goals each player has scored in a particular tournament so I'll write a store procedure that will return the list of top players who have scored more than six goals in a tournament so first of all let me Begin by using my SQL IQ database we'll run it so now we are inside the SQL IQ database let me select star from players to show the values that we have in the players table you can see there are six players in our table we have the player ID the names of the players the country to which these players belong to and the goals they have scored so I'll write a stored procedure so the stored procedure syntax is something like this it should start with a D limiter okay in the D limiter I'll write erson erson next I'll write create procedure followed by the procedure name let's say I want to name my procedure as topor players next statement is begin after begin I'll write my select statement I want to select the name of the player the country and the goals each player has scored from my table that is players where I'll write goals is greater than six it give a semicolon then I'll end my procedure with a d limiter that was double Amberson next I'll write D limiter and give a semicolon now the semicolon suggests this is a default delimiter and there should be a space okay now let's run our stored procedure there you go so you have successfully created a store procedure now the way to run a store procedure is you need to use the call method and give the procedure name that is topor players in our case with brackets and a semicolon let's execute it okay there is some problem here so we made a mistake while creating a procedure the name of the column is goals and not goal let me create that procedure again okay it says the procedure topor player already exist let's just edit the procedure name name instead of top player we'll write it as top players and similarly we'll edit here as well now let's create it again okay now to call my procedure I'll write call space followed by the procedure name which is topor players if I run this you can see we have two players in our table who have scored more than six goals so we consider them as the top players in a particular tournament all right now there are other methods that you can use while creating a stored procedure one of the methods is by using an in parameter so when you define an in parameter inside a stored procedure the calling program has to pass an argument to the stored procedure so I'll give a comment stored procedure using in parameter all right so for this examp example I'll create a procedure that will fetch or display the top records of employees based on their salaries so if we have a table in our SQL IQ database which is called employee details I'm going to use this table you can see we have the name of the employee the age sex then we have the date of join City and salary using this table I'll create a procedure that will fetch or display the top records of employees based on their salaries and and we'll use the in parameter so let me show you how to do it I'll write D limiter this time I'm going to use forward slash I'll write create procedure followed by the procedure name let's say SP for store procedure sort by salary is the name of my procedure and inside this procedure I'll give my par meter in I'll create a variable V and assign a data type integer then I'll write begin followed by my select statement where I'll select the name age salary from my table name that is EMP details or employee details I'm going to order this by salary descending and I want to display limited number of Records so I'm using this limit keyword and my variable V which I created here I end my select statement I end my stored procedure with forward slash and I'll go back to my default D limiter that is is semicolon all right so let me run this there should be a space here all right so let's run this okay you can see we have successfully created our second stored procedure which is Spore sort by salary now you can also check whether the store procedure was created or not here you have an option to see the stored procedures let me just refresh this and you can see we have three stored procedures that we have created so far one is Spore sort by salary the other two where topor player and topor players okay now let's call our store procedure I'll write call space followed by the stor procedure name which is Spore sort by salary and inside this I'll give my parameter which was actually V and this V we have used in limit let's say I want to display only the top three records of the employees who have the top three highest salaries okay so let me run it there you go so ammy Sara and Jimmy were the top three employees who have have the highest salary so you saw how you could use the in parameter in a stored procedure we created a variable and that variable we used in our select statement and we called our stored procedure and passed in that variable okay now instead of a select statement inside a stored procedure you can also use other statements let's say update so I'll create a stored procedure to update the salary of a particular employee so in this procedure instead of Select statement we'll use the update command in this example we'll use the in operator twice so let me show you how to do it I'll write my D limiter first which is going to be for then I'll write create procedure my name of the procedure is going to be update salary and inside the update salary name I'll write in and then temp underscore name which will be a temporary name variable and the type I'll assign is varar 20 I'll again use my in parameter I'll write in next my other variable would be newcore salary and the data type would be float I'll write begin and write my update command or update statement I'll write update table name that is employee details set salary equal to newcore salary where name is equal to my temporary variable that is tempore name so this is my update command and I'll end the delimiter all right so let's run this okay we have successfully created our stored procedure if I refresh this you can see I have my stored procedure update undor salary okay now let's say first of all I'll display my records that are present inside employee _ details table okay so we have six rows of information let's say you want to update the salary of of employee Jimmy or let's say Mary from 70,000 to let's say 72,000 or let's say 880,000 so I'll call my store procedure that is update uncore salary and this time I'm going to pass in two parameters the first parameter will be the employee name and next with a comma I'll give my new salary that I want to so my employee name let's say is Mary and the salary I want to be updated is let's say $80,000 I'll give a semicolon and I'll run it you can see it says one row affected now let's check our table once again there you go if you see this record for Mary we have SU successfully updated the salary to $880,000 now moving ahead we learn to create a stored procedure using the out parameter so I'll give a comment stored procedure using out parameter okay so suppose we want to get the count of total female employees we will create total employees as an output parameter and the data type would be an integer the count of the female employees is assigned to the output variable which is total _ emps using the into keyword let me show you how to write a stored procedure using the out parameter so first I'll declare my delimiter to forward slash I'll write create procedure followed by the procedure name it is going to be Spore count employees and inside this I'm going to give my out parameter and the variable name that is total uncore emps which is total employees and the data type will be integer next I'm going to write begin followed by my select statement that is Select I want the count of total employees and the output I'm going to put into my new variable that is total _ emps from my table that is empore details where sex is equal to F which means female I'll give a semicolon next I'll end it with the D limiter and I'm going to change the D limiter to a default D limiter that is colon so let me tell you what I'm doing here I'm creating a new stored procedure that is Spore count employees using this stored procedure I'm going to count the total number of female employees that are present in our table empore details so I've used my out parameter and I'm creating a new variable called total uncore emps the data type is integer here in the select statement I'm counting the names of the employees and the result I'm storing it in total _ emps I have used my we condition where the gender of the sex is female so let's run this okay so we have created our stored procedure let's refresh this okay you can see we have our new stored procedure Spore count employees now to call it I'll write call the name of the procedure that is countor Spore count employees within brackets I'll pass in the parameter as at the rate fcor EMP I'll give a semicolon then I'll write select at the rate f underscore EMP as female employees okay so as is an alias name let's run this one by one first I'll call my procedure and then we'll display the total number of female employees you can see in our table we have three female employees all right now with this understanding let's move on to our next Topic in this tutorial on Advanced SQL now we are going to learn about triggers in SQL so I'll give a comment here triggers in SQL so first let's understand what is a trigger so a trigger is a special type of stored procedure that runs automatically when an event occurs in the database server there are mainly three types of triggers in SQL we have the data manipulation trigger we have the data definition trigger and Logan triggers in this example we learn how to use a before inser trigger so we will create a simple students table that will have the students role number the age the name and the students marks so before inserting the records to our table we'll check if the marks are less than zero so in case the marks are less than zero our trigger will automatically set the marks to a random value let's say 50 so let's go ahead and create our table that is students all right so I'll write create table student now this table will have the student role number the data type is integer it will have the age of the students again the data type is integer we have have the names of the students so the third column would be name the data type would be variable or varying character size I'm giving it as 30 finally we have the marks as floating type so let's create this table which is student so we have created our table now I'll write my trigger command so trigger command will start with D limiter like how our usual stored procedures have next this time I'll write create trigger then you need to give the name of the trigger that is Mark underscore let's say verify I'm going to use a before insert trigger so I'll write before insert on my table name that is student next I'll write for each row if new DOT marks is less than zero then we'll set new do mark marks equal to 50 so this is my condition first we'll check before inserting if any student has marks less than zero we'll assign a value 50 to that student because usually the marks are not less than zero in any exam I'll write end if semicolon and I'll close the delimiter so this is my trigger command I'll run it it says trigger already exists in this case we need to update the trigger name let's say I'll write marks uncore verify uncore student for STD let's run it again okay there is an error here because in our table the column name is Mark and not marks so here we need to change it as Mark instead of marks all right let's run it okay so we have created our trigger now let me insert a few records to the student table so I'll write insert into student I'll write values I'll give the value as 501 which is the student role number the age is let's say 10 the name is let say Ruth and the marks is let's say 75.0 give a comma we'll insert our second student record student role number is 502 age is 12 the name is let's say Mike and this time I'm purposely giving a value of - 20.5 give another comma we'll insert the third record for student role number 503 age is 13 the name is Dave and let's say the marks obtained by Dave is 90 now we'll insert our final record for student number 504 the age is 10 name I'll enter as Jacobs and this time again I'm purposely giving the marks in negative 12 let's say 5 I close the bracket and give a semicolon and I'll run my insert statement okay so we have inserted four rows of information to our student table now let me run the select query I'll write select star from student if I run this you see the difference there you go so originally we had inserted for 502 the marks was minus 20.5 and for 504 for Jacobs the marks was - 12.5 our trigger automatically converted the negative marks to 5050 because when we created our trigger we had set our marks to 50 in case the marks were less than zero so this is how a trigger works now you can also drop a trigger or delete a trigger you can just write drop trigger followed by the trigger name in this case our trigger name is maxcore verore St I'll just paste this here and if you run this it will automatically delete your trigger we give this as a comment okay now moving on now we are going to learn about another crucial Concept in SQL which is very widely used this is known as views so views are actually virtual tables that do not store any data of their own but display data stored in other tables views created by joining one or more tables I'll give a comment as views in SQL okay now to learn views I'm going to use my table which is present inside classic models database now this database as I mentioned we had downloaded we had downloaded it from the internet so first of all let me write use classic models so I'll switch my database first all right now we are inside classic models so here let me show you one of the tables which is called customers so I'll write select star from customers okay I missed s here let's run it again so this is my customer table which is present inside classic models database it has the contact last name the contact first name the customer name custom customer number we have the address State country and other information now I'll write a basic view command using this customer table the way to write is I'll write create view followed by The View name which is custor details then you write as select I'm going to select a few column names from my original customer team which is this one so I need the customer name let's say I need the phone number and the city so you have this information here you have the phone number and the city all right I'll write from my table that is customers if I run this my view that is cust details will be created let's run it this some error here because the name of the table is customers and not customer I'll give an S and I'll run it again all right so you can see we have created our view and to display the contents that are present inside our view I can write select star from followed by The View name that is custor details let's run it there you go so we have the customer name the phone number and the City of the different customers that we have in our table all right now let's learn how you can create views using joints so we join two different tables and create a view so for that I'm going to use my products table and the products lines table I'm talking about the product table and the product lines table present inside classic models database so before I start let me display the records that are present inside the products table let's run it so these are the different products you can see here now let's see what we have in product lines table so we have the product line the text description and there's some HTML description and image so I'll create a view by joining these two tables and we'll fetch specific records that are present in both the tables so let me first start by writing create view followed by The View name that is product underscore description as I'll write select product name comma then I'll write quantity in stock I also want the MSRP now these three columns are present inside the products table and next from the product lines table I want the text description of the products so I'll write from products table I'll give an s s p followed by Inner join my other table that is product lines as let's say PL on the common column that is product line so P dot product line is equal to I'll give a space PL dot product line okay so here we have used an inner joint to fetch specific columns from both the tables and our view name is productor description let us run it all right so we have our view ready now let me view or display what is present inside our productor description view I I select star from productor description let's run it there you go so we have the product name the quantity in stock MSRP and textual descriptions of the different products in the table okay now there are a few other operations that you can perform let's say you want to rename a view instead of productor description you want to give some other name so I'll just give a comment rename description so to rename a description you can use the rename statement I'll write rename table product underscore description which is my old name I want to change this name to let's say I'll give vehicle description since all our products are related to some of the other vehicle so I'll write vehicle description okay let us run it all right so here you can see I have renamed my view so here if I just refresh it and I'll expand this you can see we have the Cur details view and we have the vehicle _ description view okay now either you can view all the views from this panel or you can use a command let's say I'll write display views is the comment now to show all the views you can use show full tables where table underscore type is equal to within single code I'll write view so this is the command that will display all the views that are present inside a database there is some error here let's debug the error this should be okay so instead of table types it should be table type equal to view let's run it you can see the two different views that we have one is customer details another is vehicle _ description okay now you can also go ahead and delete a view for that you can use the drop command so I'll write drop view followed by The View name let's say I want to delete customer _ details or custor details view I'll write drop view castore details let's run it you can see here we don't have the custor details view anymore all right now moving to our final section in this demo here we will learn about Windows functions Windows functions were Incorporated in MySQL in the 8.0 version so Windows function in MySQL are useful applications in solving analytical problems so using the employees table present inside my SQL inro database so we'll find the total combined salary of the employees for each department so first let me switch my database to SQL intro database I'll run it okay I'll display my table that is employee so here we have 20 employees in our table using this table we are going to find the combined salary of the employees for each department so we will partition our table by department and print the total salary and this we are going to do using some windows functions in MySQL so I'll write select I want the employee name the age of the employee and the department of the employee comma next I'll write the sum of salary over I want to partition it by department so I'll write Partition by Department which is D and I'll give an alas as total salary so that it will create a new column with the name total salary from my table that is employees the output will be a little different this time let's execute it and see the result there you go so here we have created another column in our result that is total salary and for each of the employees and the respective departments we have the highest salary so in finance the highest salary of one of the employees was $155,000 similarly if I come down we have the highest salary from HR if I scroll further we have the highest salary from it marketing product sales and and the tech Team all right now we'll explore a function which is called row number now the row number function gives a sequential integer to every row within its partition so let me show you how to use the row number function I'll write select rore number function over my column would be salary so I'll write order by salary I'll give the alas as rum give a comma and I want to display the employee name and the salary of the employee from my table that is employees and I'll order by Sal so let's see how our row number function will create sequential integers okay you can see here we have a row num column and we have successfully given row numbers to each of the records you can see it starts from one and goes up till 20 okay now this row number function can be used to find duplicate values in a table to show that first I'll create a table I'll write write create table let's say I'll give a random name that is demo and let's say we have in this table the student ID which is of type integer and we have the student name which is of type varar the size is 20 I'll create the small table with a few records let's create this table first now we are going to insert a few records to our demo table so I'll write insert into demo values I'll give 101 the name is Shane give a comma I'll insert the second student name one2 the name is Bradley we give a comma this time for 103 we have have two records let's say the name of the student is her give a comma I'll copy this and we'll paste it again so we have duplicated 103 next we have 104 the name of the student let's say is Nathan then again let's say for the fifth student which is Kevin we have two records I'll copy this and I'll paste it here let me give a semicolon and we'll insert these records to our table demo all right now let me just run this table for you I'll write select start from demo if you see this we have a few information that are duplicated in our table that is for student ID 103 and student ID 105 now I'm going to use my row number function to find the duplicate records present in my table I'll write select student _ ID comma student uncore name I'll give another comma and write rore number over within brackets I'll write Partition by store ID comma store name okay then I'll write order by store ID close the bracket I'll give an alas as ronam from my table that is demo let's just run it you can see here okay let me just delete n from here and do it again all right if you see here there is just one student in the name Shane we have one student in the name Bradley but here if you see for her the second record it says two which means there are two records for H and if I scroll down there is one record for Nathan and there are two records for Kevin which means Kevin is also repeated okay now we are going to see another Windows function that is called rank function in my SQL so the rank function assigns a rank to a particular column now there are gaps in the sequence of ranked values when two or more rows have the same rank so first of all let me create a table and the name of the table would be a random name we'll give it as let's say demo one and it will have only one column let's say variable a of type integer we'll create this table first okay now let's go ahead and insert a few records to our table which is demo one so I'll write value 101 102 let's say 103 is repeated I'm doing this purposely so that in the output you can clearly distinguish what the rank function does next we have 104 105 we have 106 and let's say 106 is also repeated finally we have 107 okay let me insert these values to my table that is demo 1 okay this is done now if I write select Vore a and use my rank function I'll write rank over then I'll order by my variable that is Vore a as an alas name let's say test rank from my table that is demo one let me execute this and show you how the rank function works if I run this there you go so here if you mark so for variable a101 the test rank is 1 for 102 the test rank is two but for this value which is 103 the test rank is repeated because there was a repetition for 103 so we have skipped the rank four here for 104 the rank is five now for 105 the rank is 6 now for 106 again since the record was repeated twice we have skipped the eighth Rank and our rank function assigned the same value which is 7 for 106 and for the last value 107 the rank is 9 all right now moving ahead we'll see our final Windows function which is called first value so first value is another important function in my SQL so this function Returns the value of the specified expression with with respect to the first row in the window frame all right so what I'm going to do is I'm going to select the employee name the age and salary and I'll write first underscore value which is my function and pass in my employee name and then I'll write over order by my column that is salary descending I'll give an alas as highest underscore salary from my table that is employees so let me run this and see how the first underscore value function works all right so in our table Joseph was the employee who had the highest salary which was $115,000 so the first value function populated the same employee name throughout the table you can see it here now you can also use the first underscore value function over the partition so let's say you want to display the employee name who has the highest salary in each department so for that you can use the partition I'll write select empore name comma I want the department and the salary comma I'll use my function that is first underscore value followed by the name of the employee inside my first value parameter I'll write over here I'm going to use part partition I'm going to partition it by department since I want to know the employee name who has the highest salary in each department and I'm going to order by salary descending and I'll give my alas again as highest salary from my table that is employees so let's run this and see the difference in the output okay so as you can see here we have the employee who had the highest salary from each department so for finance Jack had the highest salary from HR it was Marcus similarly in it it was William if I scroll down for marketing it was John for product it was Alice who had the highest salary similarly in sales we had Joseph and in Tech we had Angela so this is how you can use the first uncore value function using partition all right so that brings us to the end of this demo session on our tutorial so let me just scroll through and show you what we did from the beginning first we learned about subqueries in SQL so we initially wrote a simple subquery and then we used our classic models database which was downloaded from the internet i' also shown you the link from where you can download this database here we used two different tables and we performed a subquery operation we learned how to create stored procedures so we learned how you can use the in operator or the in parameter as well as the out parameter in stored procedure after stored procedure we learned another crucial Concept in SQL which is called triggers now triggers are also special kind of stor procedures so we saw how to write a b before insert trigger you can see it here next we learned how to delete a trigger we also saw how to work with views in SQL so views are basically virtual tables that you can create from existing tables we also saw how you can use views using two different tables and an inner join and we learned how to display views how to rename view names how to delete a view and finally we explored a few Windows function in this tutorial we will learn how to work with databases and tables using SQL with python to do this demo we'll be using our jupyter notebook and the MySQL workbench you can see it here so we will write our SQL queries in the jupyter notebook with python like syntax if you don't have MySQL or jupyter notebook installed so please go ahead and install them first while installing the MySQL workbench you'll be asked to give the username and password let me show you so I am on my MySQL workbench so once you connect it will ask for the username and the password so I've given my username as root and password you can give while installing it we will be using the same user ID or the username and the password to make our connection so let's get started with our Hands-On demonstration part first and foremost let me go ahead and import the necessary libraries I'll give a comment as import libraries all right so first I'll import MySQL do connector next from MySQL doc connector I'm going to import my error method or the error module next I want to import pandas as PD so let's run this okay there is some error here this should be capital E and not small e all right you can see I have imported my important libraries now I'm going to create a function that will help us create a server connection so I'll write my user defined function by using the DF keyword I'll write create underscore server underscore connection this is going to be my function name and it will take in three parameters first is the host name next is the username and then we have the user password all right I'll give a colon and then in the next line I'm going to Define a variable which is going to be connection and I'll assign it to a value called none now we'll be using exception handling techniques to connect to our MySQL server the tri block lets you test a block of code for errors and the accept block will handle the errors so I'll write try and give a colon and then I'm going to reassign the connection variable to a method which is MySQL do connector do connect now this MySQL connector. connect method sets up a connection so it establishes a session with the MySQL server if no arguments are passed it uses the already configured or default values so here we are going to pass in three parameters the first is the host name I'll write host equal to host name which is hostor name I'll give a comma then I'll write user equal to user uncore name next will be my password and I'll assign the value user uncore password all right now I'm going to use a print statement and write mySQL database connection successful after this I'll give my accept blog so I'll use the keyword accept here I'll write error as err give a colon and then I'm going to use the print statement here I'm going to use some print format techniques using the F letter I'll write error colon and I'll use curly braces give VR and then I'll close the double codes after this I'm going to return my connection all right let me give a comment here we are going to assign our password so we need to put our MySQL terminal password so this password you assign it while installing MySQL workbench I'll write PW and I'll give my password which is simply at the rate 1 2 3 4 5 and then I'm going to give my database name so I'll give database name here I'm going to write DB equal to this is the database I want to create which is going to be MySQL python let me just scroll this down okay now I'll say connection equal to I'll pass in my user defined function name which is create server connection and the parameters which are going to be Local Host that is my host name my username which is root and then I'll give PW which is my password that is exactly simply at the rate 1 2 3 4 5 let's just run it now okay there is an error here we need to remove this double quotation all right made another mistake here this this should be root okay you can see here mySQL database connection successful all right next we are now going to create a database that is MySQL python so I'll give a comment create MySQL python database again to create this database I'm going to create another user defined function function using the DF keyword I'll write the function name as create database passing the parameters as connection comma query give a semicolon and in the next line I'll write cursor equal to going to make the connection so I'll write connection dot cursor and I'll give the parenthesis so this MySQL cursor of MySQL connector python is used to execute statements to communicate with the mySQL database the MySQL cursor class initiates objects that can execute operations such as the MySQL statements okay next I'm going to again use my try and accept block so I'll write try give a coolon and here I'm going to use cursor dot execute within that I'm going to pass in my query next I'll use a print statement and the message I'm going to display is database created successfully after this I'm going to write my accept block I'll write accept error as err give a colon and then I'll use a print statement I'll write print I'll use the formatting again error colon then I'll write within single codes I'll give curly braces err and then I'll close the double codes next let's use the variable create underscore database underscore query and here I'm going to write my SQL query to create the database so I'll write create database and followed by that I'll give my database name which is going to be MySQL python okay after this I'll call my function which is create database and I'll pass in the parameters the first one is connection and next the query is create _ database _ query let me just copy it and I'm going to paste it here all right so what I'm doing here is I am creating a a new function that is to create a new database with the name MySQL underscore python which you can see it here now this function takes in two parameters connection and query I'm using the connection. cursor function which is often used to execute SQL statements using Python language and then I have created my try and except blocks so this TR block statements will try to create my new database which is MySQL python in case it fails to create the new database the exer block will work so here I'm writing my SQL query to create a new database which is create database followed by the database name and I'm assigning it to a variable which is create database query and then I'm calling my function create database and passing in the two parameters connection and the query all right so let's just run it all right you can see here it has created my database successfully now you can verify this by checking the MySQL workbench or the MySQL shell you can see on the MySQL workbench here on the left panel under schemas there is a database called MySQL python let me just expand it now we haven't created any table so it's not showing it now the next step we are going to connect to this database so let's go ahead and connect to our database that we have just created I'll write the comment as connect to database now to connect to a database I'm again going to create a user defined function using the DF keyword I'll write create uncore DB which is for database _ connection and the parameters it will take is the host name followed by the username then we have the user password and finally we have the database name I'll give a colon in the next line I'm going to create my variable which is connection and then I'll assign it to a value none after this I'm going to use my exception handling techniques so I'll write my tri block first I'm going to reassign my connection variable using the MySQL connector method so I'll write MySQL do connector do connect so this method we take in the parameters so first it will take the host name I'll write host equal to hostor name I'll give a comma next it will take the usern name so user equal to user name give another comma next it will take the user password I'll use pass WD equal to user uncore password I'll give a another comma and this time it is going to be the database name so I'll write database equal to dbor name now let's use the print statement and the message we are going to print is mySQL database connection successful all right finally we'll write my except block I'll write accept error as err give a colon and then I'll use the print statement F within double codes I'll write error colon within single codes curly braces I'll write err and we'll close the double quotes finally this function will return the connection value all right let's run it and there you go it has run successfully so we have connected to our database now it's time for us to execute SQL queries I'll give another comment saying execute SQL queries all right now to execute our SQL queries I'll use another user defined function which is execute underscore query and I'll pass in the parameters as connection and query give a colon I'm going to write cursor equal to connection do cursor now this is used to establish a connection and run SQL statements next we'll use the try and accept block so I'll write try cursor Dot execute this will take in one parameter which is going to be my query and then I'll write connection dot commit which is another method now let's use the print statement so I'll write print let's say the message would be query was successful and then we'll write our accept block which is accept if the tri block doesn't work through an error using the print statement within double codes inside the inside the curly braces I'll write ER r and close the double codes all right so let's run it okay so we have successfully created our various functions that we needed to create a database establish a connection and to execute our queries all right now it's time for us to create our first table inside the MySQL python database so to do that I'm going to write my create command in SQL so first we are going to assign our SQL command to a python variable using triple codes to create a multi-line string so let me show you how to do that I'll write my variable name which is going to be create orders table it is always recommended to use relevant variable names to make it more readable and now I'm going going to use triple Cotes so the triple code will ensure I can create my multi-line string inside the triple code I'm going to write my create command which is create table here I'm going to create an orders table first and inside the orders table I'm going to create my column names the First Column would be the order ID it is going to be of type integer and and I'll assign this order ID as my primary key column we'll give a comma next the second column would be customer underscore name the customer name column would be of type varing character so I'll write varar and I'll give a size of 30 and this is also going to be not null moving ahead my fourth column would be the product name column so I'll write productor name product name will be of type varing character the size is let's say 20 and it is also not null next I'm going to create my fourth column which is the date on which the item was ordered or the product was ordered so I'll write date ordered the data type will be date next I'll create a quantity column to keep track of the number of quantities that were ordered this is of type integer my next column would be unit price which will basically have information about the price of each unit of product I'll have the phone number of the customer I'll write phone number phone number can be T of type weing next to run this we are first going takes in two parameter the first one is connection the variable name which is create orders underscore table let us run it okay there is some error here let's see what's the error okay so here we have put four double is another here let's debug it it says name cursor not find roll it to the above cell if you see here in our execute _ query function instead of cursor I have return cursor so R is missing let's redun this this again there you go you can see here my SQL database connection successful even our query was also successful now if you want to recheck if the table that is orders was created or not my SQL workbench so let me show you how to do it so I am on my MySQL workbench and under let me just right click and I'll select refresh all there you go you can see this Arrow just click on this arrow and here you can see we have a table called orders so we have created our table called orders now you can check the columns as well you have the order ID you have the order ID the customer name product name ordered date quantity unit price and phone number now it's time for us to insert a few records to this table which is orders now to insert records I'll give a comment as insert data I'll start with the variable name let's say the variable name is dataor orders I'll give triple Cotes next I'll write my insert into command so I I'll write insert into my table name that is orders followed by values and now I'll start entering my records for each of the rows so first I'll give one1 which is the order ID then I'll give the customer's name let's say Steve and the product he had ordered is let's say laptop then I'll give my date in which the item was ordered let's say it is 2018 I'll choose 06 as the month and the date is let's say 12 we give another comma this time we'll pass in the quantity which is two let's say the price of each laptop was $800 and we'll give a phone number this is random let's say 62 9 3 7 3 0 let's say 802 all right similarly I'm going to insert five more records of different customers and their items that they have purchased to this table orders so here on my notepad I have my rest of the five records let me just copy it and will paste it in the cell here this will save us some time okay let me recheck if everything is fine I'll give a comma here all right so we have six customers in our table which have their customer IDs from 101 to 106 you have Steve jaw Stacy Nancy Maria and Danny you have the different items they have purchased laptop books trousers t-shirts headphones and smart TV is the date on which they had ordered this item the number of quantities they had ordered and then we have the unit price and some random phone numbers so let's create the connection now I'll write connection equal to I'll write create uncore dbor connection then I'll going to give my same parameters let me just copy it from the top which is Local Host the host name root is my username then we have password and the database name and then I'll use the same query as above which is execute query I'll copy this paste it here and instead of create orders table variable I'll put as dataor orders so this will store my insert into command you can see the variable I've have used here is dataor orders now it's time let's just run it all right there was some mistake here let's debug it again this should be Cotes and not four now let me rerun it again there you go you can see here my SQL database connection successful and my query was also successful now we'll create another user defined function which will help us read query and display the results so I'll write my function name as DF read underscore query this will take in two parameters connection and query then I'll write cursor equal to connection do cursor I'll put my result as none and then I'll use my try and accept block I'll write try cursor dot execute this will take in one parameter which is quy and then I'll give another variable which is result equal to cursor dot fetch all now this fetchall method will return all the results in the table I'll write return result next we'll use the accept block so I'll write accept error as err give a colon and I'll use my print statement just scroll this down I'll use my formatting F error give a colon followed by a space within single Cotes inside curly press I'll give err and close my double Cotes let's run it all right so now we are all set now we are going to use our select Clause having whereby then we'll see how to use autoby Clause some inbuilt functions we'll update some records delete some records and do a lot of other stuff so let's start with our first query so our first query is going to be using the select statement all right so suppose I want to display all the records that we have inserted into our orders table so the way to do is I'll assign my query to a variable let's say q1 I'll give triple Cotes within triple quotes I'll write select star from orders we give a semicolon followed by the triple codes now we'll establish the connection so let me just go to the top and I'll copy this line which is to connect to our database I'll paste it here now we'll create a variable called results that will store the result of this query and we are going to assign this variable to our function that is read query and this read query will have two parameters the connection and the variable name which is q1 for the query next to display I'm going to use a for Loop I'll write for results for result in results print I'll say result now we are done let's just run this query there you go you can see here we have successfully printed all the rows in our table which is we have six records in total now we are going to explore a few more queries so let me just copy this and we are going to edit in the same query I'll paste it here next let's say you want to display individual columns from the table and not all the columns so let me let me create the variable Q to now instead of star I'm going to display only the customer name and let's say the phone numbers of the customer so I'll write phore number all right the rest all Remains the Same let me just recheck it and here instead of q1 we'll put Q2 and let's run this cell all right you can see here now we have displayed only two columns the First Column is the customer name and then we have the respective phone numbers okay now let me just paste that query again now we are going to see how you can use an inbuilt function that is in our table we have the order date and from the order date we are only going to display the different ear that are present in the order date so to do that I'm going to use the year function I'll edit this query instead of q1 I'll make it Q3 and here I'm going to write select here which is my function name from my column which is date ordered from orders and here I'll change this to Q3 q1 Q2 Q3 are basically query 1 query 2 and query 3 let's run it there you go so we have successfully extracted the different ears present in the order date column now if you want to display the distinct or the unique dates present in the column you can use the distinct keyword in the select statement so the way to do it is I'll write select distinct give a space the rest of the query Remains the Same and here Q3 I'll write Q4 I'll make this as Q4 let's run it you can see 2018 and 2019 are the unique year values that are present in the order date column okay now moving ahead let's write our fifth query and this time we are going to explore how you can use the wear Clause so I'll change this to Q5 before I write my query so let's say you want to display all the orders that were ordered before 31st of December member 2018 so to filter this we are going to use the wear Clause so I'll write select star from orders next I'll write where my date underscore ordered is less than within course I'll give my date value which is 2018 December 31st so all the items or the products that were ordered before 31st of December 2018 will be displayed so let's run it all right you can see here there are three orders in our table which have been ordered before 31st of December now moving ahead we want to display all the orders that were made after 31st of December so here what you can do is I'll just copy the above query again I'll copy this line so instead of less than 31st of December 2018 I'll make it as greater than so every order that was placed after 31st of December will be displayed if you run it so you can see here there are three orders in our table which were ordered after 31st of December [Music] 2018 now moving ahead let's write a seventh query now let's see how the auto by Clause Works in SQL so you can filter your results based on a particular column or sort it based on a particular column so this is going to be my query 7 I'll write it from scratch again let's see you want to display all the columns from the table so I'll write select star from orders then I'm going to use order by unit price I'll give a semicolon let's run this query and see the output now if you see the result here and you mark the unit price column the result has been ordered in ascending order of unit price you see here it starts with the lowest price and then goes on with the highest price towards the end if you want to order it in descending order you can use the keyword DC so this will ensure your top or the most expensive products appear at the top and the least expensive products appear at the bottom all right next let's see how you can create a data frame from the given table so as you know using jupyter notebook and pandas you can create data frames and work on it very easily so with this table also we can create our own data frame so for that let me create an empty list first I'll write from DB equal to I'll assign this as an empty list so we are going to return a list of lists and then create a pandas data frame next I'll write my for Loop I'll write for for result in results I'll assign result to list of results so I'm converting the result into a list and then I'm going to append it to the empty variable or the empty list which is from DB do append I'll append the result to my empty list next we need to pass in the columns now that will be part of our data frame so I'll write columns equal to this column I'll pass it within a list so I'll give my first column as order ID then we have the customer name next I have my my product name then I have the date on which it was ordered give a comma then we'll have the quantity column let me write it in the next line next we have the unit price column and finally we have the phone number column so I'll write within double codes phone number and this we are going to assign it to a data frame so I'll be using PD dot data frame which is my function to convert a list into a data frame my variable I'm going to pass is prore DB and I'll write my next argument is columns equal to my variable name that is columns finally let's display the data frame which is DF all right so here I'm creating a empty list first and then I am creating a for Loop and I'm appending the results to my empty list here you can see I have created my column list and using pd. data frame I'm converting the list into a data frame if I run this this is append and not appens all right you can see we have our data frame ready this is the index column it starts from zero onwards and then we have the different column names okay now let's see how to use the update command now suppose you want to change the unit price of one of the orders you can use the update command so the way to do it I'll first create my variable let's say update and I'll give three quotes or triple quotes then I'll use my update command which is update followed by the table name that is orders next I'll write set let's say unitor price if you see this let's say I want to set the unit price of trousers from $50 to let's say $45 I want to update this particular record so I'm going to write set unit price column equal to $45 where the order ID equal to 103 so this query will update the third row in our table which is order ID 103 so it will update from 50 to $45 I'll close the triple Cotes and now I'll use the connection queries again let me just paste it here all right I'll delete these three lines of code and instead of that I'll put execute Ute underscore query and this will take into parameters as all B which is going to be connection followed by the variable name that is update let's run it you see here it says my SQL d
