🔥SQL Tutorial for Beginners 2023 | SQL Tutorial | SQL Full Course 2023 | MySQL | SQL | Simplilearn

thank you hello everyone welcome to this live session on SQL full course video by simply loan 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 postgresql 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 join statements as well as sub queries 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 postgres 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 MySQL 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 mongodb which is a nosql database next we have Microsoft SQL Server next we have Apache Cassandra which is a free and open source nosql database and finally we have postgres SQL now let's learn what is SQL 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 tuple so if you have the player ID which is one zero three here the name of the player is Daniel he's from England and the number of goals here score is seven 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 let 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 ER diagram starting with what is an ER 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 am abisara Ahuja 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 this big e-commerce companies manage their tons of data that keeps updating every second and these data sets keep on updating in the 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 10 year diagram is an entity relationship diagram describes the relationship of entities that needs to be stored in a database ER 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 relationships let's understand the use of ER diagram with the help of a real world example here a school needs all its Student Records to be stored digitally so they approach an I.T company to do so a person from the company will meet the school authorities not all their requirements describe them in the form of ER diagram and get it cross-checked by the school authorities as the school authorities approve the ear 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 ER diagram let us see why it has been so popular The Logical structure of the database provided by ear diagram communicates the landscape of business to different teams in the company which is eventually needed to support the business ear 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 ER diagrams as a blueprint which reduces complexity and helps them save time to build databases quickly ear 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 ear diagram the rectangle symbol represents the entities oval symbol represents attributes a rectangle embedded in a rectangle represents a weak entity a dashed oval represents a derived attribute a diamond symbol represents a relationship among entities double all symbol represents multi-valued attributes now we should dive in and learn about the components of ER diagram there are three main components of hair diagram entity attribute and relationship entities have weak entity attributes are further classified into key attribute composite attribute multi-valued attribute and derived attribute relationships are also classified into one-to-one relationships one to many relationships many to one relationships and many to many relationships let's understand these components of rear diagram starting with entities an entity can be either a living or a non-living component an entity is showcased as a rectangle in an ear diagram let's understand this with the help of an ear diagram here both student and course are in rectangular shape and are called entities and they represent the relationship study in a dimension let's transition to weak entity an entity that makes Airlines 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 it does not have any primary key and the room number attribute here acts only as a discriminator and note of 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 any Air 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 row number and age but here roll 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 composed of several other attributes is known as a composite attribute and oval showcases the composite attribute and the composite attribute over is further connected with other roles 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 multi-valued attribute an attribute that can possess more than one value are called multi-valued attributes these are represented as double old shape in the example below the student entity has attributes phone number roll 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 multi-valued 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 ER 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 ear 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-to-one relationship in the example below 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-to-one relationship let's see the second one when too many relationship when a single element of an entity is associated with more than one element of another entity is called when too 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 obtained 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 VR diagram let us know the points to keep in mind while creating the ER 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 attribute should be in our chip 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 new diagrams there are mainly four types of SQL commands so first we have data definition language or ddl so ddl commands change structure of the table like creating a table deleting a table or altering a table all the commands of ddl are Auto committed which means it permanently save all the changes in the database we have create alter drop and trunk it 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 Auto 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 revoke 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 WHERE Clause to filter out a table based on some conditions so you can see here where condition 1 condition 2 and so on then we have the group by Clause that takes various column names so you can write Group by column one column two 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 length 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 timestamp and others then we have string data type which includes car this war 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 exists and so on finally we have some comparison operators such as equal to not equal to this 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 import and 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 Oracle website that is mysql.com and now we'll move to the downloads page now scroll down and click on my sequel GPL downloads now under Community downloads click on MySQL 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 you 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'll install only the MySQL server MySQL shell and the MySQL World page 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 MySQL workbench version you want to install and click on the Arrow we will do the same thing for MySQL shell let me choose the latest version click on the Arrow these are the products that have to be installed in a system now we'll 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 see the product configuration click next now we'll configure our SQL Server instance here we'll go with the default settings and click next another 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 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 MySQL workbench and MySQL shell after clicking on finish so now the shell and workbench has started now we'll 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 will open command prompt now we'll reach the path where MySQL files are present over this C program files MySQL my SQL Server 8.0 and then and I'll copy this path now I'll open the command prompt and write a command CD space and paste select and press enter now write another command that will be my SQL minus your space to Improvement minus p and press enter now it will ask for your password just enter the password let's press enter now the server has started and now we'll see some commands in MySQL workbench first we will open my SQL workbench now we'll click on the local instance MySQL 80 and enter the password to connect to the localhost the MySQL workbench has started now we'll see some commands the First Command we will see is show databases show databases semicolon and now we'll 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 that are stored already in the database now there are four databases that is information schema MySQL performance schema and sys now I will select one of the database we will use MySQL now we have selected the mySQL 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 command and click on the execute button these are the tables that are stored in this mySQL database that is columns underscore Drive component DB and much more now let me now go ahead and open my MySQL workbench so in the search bar I'll search for MySQL workbench you can see I'm using the 8.0 version I'll click on it and here it says welcome to mySQL 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 ok 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 you 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 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 Ctrl 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 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 Ctrl enter all right you can see the message here it says 1000 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 neem column this country code district and population all right similarly you can check the 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 name 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 MySQL 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 underscore in true let me give a semicolon and hit Ctrl 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 intro okay now within this database we'll create a table called employee details 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 into database so use the command create table and then I'll give my table name that is going to be employee underscore 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 War car 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 car data type or character data type and I'll give the value as 1 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 war card 15 finally we'll have a salary column and salary will 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 gave the column names such as name each 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 to see the structure of the table I'll write describe EMP underscore 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 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 it basically represents the age then m means the gender of 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 tuple similarly the next employee we have is Shane you can see the age and other information then we have Mary this Dwayne Sarah and Ami 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 EMP underscore details if I run this you can see my table here and the values it has so we have the name column the each 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 in our table now if you notice this table clearly we have Chicago Boston Austin this New York and this 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 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 so in that case you can use the count function in the select statement so let me show you how to do that 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 am going to use their name inside the brackets from employee underscore 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 now this column is actually not readable at all so what SQL provides is something called as an alias name so you can give an alias to the resultant output so here I can write select count of name and use an alias as as I can give an alias as count underscore 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 Alias name now suppose we 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 am 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 by 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 is average salary now if you want you can give an alias 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 where Clause to filter rows based on a particular condition so if you want to filter your table based on specific conditions you can use where Clause now where 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 where Clause so let me show you how to do it I'll write select star from my table name that is employee details and after this I'll use my where Clause so I'll write where each 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 where 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 6 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 can use the or operator now the or operator in SQL displays a record if any of the condition separated by or is true so let me show you what I mean so since I want the employees who are from Chicago in 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 where Clause where City equal 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 employees 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 am going to use the in operator so I'll delete this after the where 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 star from EMP details where my date of join that is doj between I'll give my two date values that is first off 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 where 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 greater than 30 and I want sex to be male all right so here you can see I have specified two conditions so 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 results 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 sex comma I want to find the total sum of salary as I'll give an alias name let's say total salary from my table name that is employee details next I am 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 desc keyword so let's say I want to sort my employee details table in terms of salary so I'll write select star from EMP underscore 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 display it in descending order you can use this keyword that is desc 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 plus 20 and I'll give an alias 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 Alias 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 auto complete let's say I want to find the length of country India and I'll give an alias as total length if I run it you see here it returns 5 because there are five letters in India there's another function called 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 uppercase or lowercase 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 uppercase I am not giving in any Alias 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 is 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 lowercase now let's explore a few date and time functions let's say you want to find the current date there is a function called Cur 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 day from a date value so you can use the day function let's say I'll use D and I want to find the day from my current date if I run this you get 28 which is today's d 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 use 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 employee belongs to and the salary of the employee then we saw how you can use the select statement and display all the columns 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 where clause then we used an R 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 have 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 pass in the string let's say I'll write India if you want you can give an alias 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 uppercase similarly let me just copy this and I'll show you if you want to convert a string into a lowercase 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 Alias name to lowercase 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 keys and let's see I'll write India in uppercase let's run 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 Ctrl enter to run MySQL command there you go it has given us the right result which is 5 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 Stu underscore name and you can give the same Alias 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 it 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 nilis has six Whipple has five and above has seven similarly if you see Akshay has six then it has 7 raghav has six come in size 7 rabada has six so on and so forth now instead of using this character length you can also use the function Garland 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 care length now 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 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 alias 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 alias as let's say name underscore each 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 underscore 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 see 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 vegetable which is ideally incorrect I am 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 MySQL 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 past as an argument so see I write select I'll use the left trim function which is L trim and then I am going to purposely give a few spaces in the beginning of the string I'll give a word let's say India and then I'll give some space after the bird India and see how the Ultram box 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 am 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 see I use l trim 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 l trim let's see the difference it gives me 12 the reason being it has deleted five spaces from the left you can count it one two three four and five so 17 minus 5 was 12 which is correct similarly you can use the r trim function which removes the trailing spaces from a string trailing spaces are these pieces when you use left trim it deletes the leading spaces which is this now let me just replace Ultram 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 two three four five six and seven cool you can 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 0. 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 alias as name the sum error here it 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 a fruit now the final function we are going to see is called ASCII so the ASCII function Returns the ASCII value for a specific character let's say I write select ASCII of the letter small a if I run this it will give me the ASCII value which is 97 let's say you want to find the ASCII 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 grouped by and having first we'll understand the basics of group by and having and then jump into MySQL 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 rows 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 where condition and next we have the group by clause and here we include the column names finally we have the order by and the column names now here is an example of the group by 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 employer 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 MySQL 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 underscore 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 will Implement group buy 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 MySQL 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 underscore intro database so I'll write use SQL underscore 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 underscore into database if I write show tables you can see the list of tables that are already present in this database to do a 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 underscore 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 underscore name and my data type would be barcar 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 1 or size of 1. 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 War car 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 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 employee 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 you have the city salary and in total we have instead 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 see you want to know the total number of departments that are present so you can use distinct 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 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.35 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 alias 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 each of all the employees in each of these departments all right 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 alias name as to tell underscore salary let's run it again 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 DSC which stands for descending if I run this query you can see here on the left you have the account 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 joined 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 here from the date of join column I'll give an alias as year next I'll count the employee ID from my table name that is employees and I am going to group it by here date of join if 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 data 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 by 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 seals 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 have successfully created our sales table next we need to insert a few values to our sales table so I 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 one two three and these are the states in which the products are sold so let me just confirm with the select statement I'll write select star from sales where on 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 one two 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 multiplied by quantity column I'll give this an alias 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 two one and one two three 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 one to one and one two three 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 C underscore product I'll give my values for one to one let's see the cost price was 200 and 70 dollars for each and next we have my product as one two three and let's say the cost price for product one two three was two hundred and fifty dollars 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 Dot product underscore ID comma I'll write sum s dot cell underscore price now here C and S are Alias 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 dot quantity close the bracket I'll give an alias name as profit from seals as s so here s stands for the sales table I'm going to use inner join C underscore product table as the Alias name should be C where s dot product underscore ID is equal to C Dot product underscore ID we are using product underscore ID because this column is the common column to both the tables and finally I am going to group it by C dot product underscore ID all right so let me tell you what I have done here so I am selecting the product ID next I'm calculating the profit by subtracting the cost price from the selling price and I have multiplied the quantity column I'm using an inner join 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 dot product ID let's run this there you go so here you can see for product ID one to one we made a profit of eleven hundred dollars and for product ID one two three we made a profit of eight hundred and forty dollars 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 where Clause are kind of similar but where 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 the WHERE conditions next we have the group by 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 user having clause so we have given our condition having count of employee ID should be greater than 2. 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 2. 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 dollars 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 alias name as AVG underscore salary from employees next we'll use the Group by clause and I want to group it by each department and then I am 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 dollars let's run it and see the output there you go so here you can see the total three departments in the company that is sales Finance in HR where the average salary is greater than 75 000 dollars okay next let's say you want to find the cities where the total salary is greater than two hundred thousand dollars 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 Alias name as total from employees Group by City and then I am going to use my having clause I'll pass in my condition as having sum of salary greater than two hundred thousand dollars 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 two hundred thousand dollars 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 am going to use the count function I'll write count Star as employee underscore count or EMP underscore count which is my Alias name from employees next I'll group it by Department having 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 ID where there are more than two employees okay now you can also use a where Clause along with the having clause in an SQL statement so suppose I want to find the cities that have more than two 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 shitty 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 am going to provide the column name that is not present in the select 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 I 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 MySQL workbench so let's get started with what are joints in SQL SQL join 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 order stable and a customer stable 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 canceled 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 customers 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 name so have ordered a product in the last 30 days in this case we want the customer name present in the customers 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 join so the SQL inner join statement returns all the rows from multiple tables as long as the conditions are met from the diagram above 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 join 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 so it'll 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 an 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 underscore joins if I give a semicolon and hit Ctrl 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 joins database created now I'll use this database so I'll write use SQL underscore joins 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 am going to create two columns in this table the First Column is going to be cricket ID then I'm going to give the data type as int and use the auto increment operator I am using Auto increment because my Cricut ID is going to be my primary key then I am going to give the name of the students who are part of the Cricut team and for this I'll use war card data type and give the length as 30 I'll give another comma and I'll assign my Cricut ID as primary key within brackets I'll give Cricket underscore ID Cricket ID is nothing but a unique identifier for each of the players like you have roll numbers in college okay let me just run it all right so we have successfully created our Cricut 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 Cricut 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 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 Cricut table so I'll write cricket and I'll give my name column followed by values and here I'll give some names such as let's say Stuart we'll go 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 his let's say hidden and finally we have let's say 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 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 just do it Johnson and let's say hidden r 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 astral 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 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 am going to write inner join my next table is going to be football as F which is an alias name for the football table then I am going to use the on command or operator and then I'll give the common key that is name here so C dot name is equal to F dot name So based on this name column from both the table my inner Zone operation will be performed so let's just run it there you go so Stuart Johnson and Hayden 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 Dot Cricut underscore ID comma C dot name comma F Dot football underscore ID comma F dot name from I'll write Cricket as C inner join football as f on C dot name is equal to F dot 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 joins 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 yeah 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 canceled 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 star 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 order line number okay so using the product orders and Order details let's perform an inner join so I'll write select O DOT 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 Dot product name now here o p are all Alias 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 alias as Revenue then I'll use my from clause from orders as o inner join order details as I'll use an alias name as OD on I'll write O DOT 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 Dot product code is equal to OD Dot 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 and not order all right now let's run it okay there's still some error it says classic models dot product doesn't exist so again the product name is I mean the table name is products are 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 rows 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 DOT key is equal to B dot 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 orders 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 form 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 Dot 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 Dot 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 Dot customer number equal to O DOT 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 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 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 joints 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 will 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 Dot customer name comma then we have C Dot phone I'll give a space here next I want the employee number from the employee table so I'll write e Dot employee number comma e dot email from customers as C right join employees as e on e dot my common key column is employee number here so I'll write e Dot 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 customers table the phone number of the customer then we have the employee number and the email address so let me run it okay the sum 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 rights on employees which means your employee stable is to the right and then we have the customer name and phone numbers of the customers from the customer table which is actually your left table so you have a few employee number such as 1002 this one zero five six we 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 joints 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 two 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 one zero zero two so if you check for one zero zero two we have Dane Murphy then if I scroll down let's see 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 employee number 1188 we have the manager as one one four three now if I check the table at 1143 we have Anthony bow so so the employee Julie Fraley reports to Anthony Bowe all right now suppose you want to know who is the reporting manager for each employee so for that you can use a self-join so let me show you how to join this employees table I'll write select and then I am going to use a function called concat within brackets I'll start with my Alias name that is m dot then I'll write last name I am going to concat last name followed by a comma then I'll have my first name I'll close this bracket then I am going to give my Alias name let's say manager comma next I am going to concat the same last name and first name and this time I'm going to use a separate Alias let's say e which stands for employee so I'll write e dot last name comma and within single quotes I'll give my comma and then I'll write e DOT first name I close this bracket I'll give an alias as let's say employee from I'll write employees as e inner join employees as m on m dot I'll use my common key column as employee number so I'll write m dot employee number is equal to e dot here I am going to use the reports two 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 Louis bonder the manager is Gerard bonder similarly if I scroll down you have there are multiple employees reporting to this particular manager similarly we have our manager as anthonybo 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 auto 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 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 auto join on MySQL workbench so to sow full outer join I am 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 Dot customer name so for this example I am using the customer table and the order table comma O DOT 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 Dot customer number is equal to O DOT customer number let me just copy this and after this I am 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 restore 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 joins 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 sub query and look at the different types of sub queries then we learn some queries with select statement followed by sub queries with insert statement moving further we will learn sub queries with the update statement and finally we look at sub queries 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 sub query so a sub query is a select query that is enclosed inside another query so if I show you this is how the basic structure of a sub query 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 if we have a question at hand which is to write a SQL query to display Department with maximum salary from employee stable 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 sub query 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 a router query will get executed based on the salary return 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 or the maximum salary if you see in our table the employee is Joseph who owns hundred and fifteen thousand dollars 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 sub query would look something like this so I am 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 sub query or the inner query so first I am 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 hundred and fifteen thousand dollars so our query becomes select employee name from employees where salary equal to 115 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 hundred and fifteen thousand dollars 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 hundred and fifteen thousand dollars and that employees Joseph all right now we learn the different types of sub queries so you can write sub queries 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 some queries with the select statement so sub queries 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 sub query 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 sub query using the select statement okay so for this demo session we'll be using a database that is sub queries you can see it here I have a database called sub queries so I'll use this sub queries database and we'll create a few tables as well okay now if I run it now we are inside the sub queries 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 underscore B uh we'll use this table throughout our demo session all right now for our select sub query 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 start 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 data joint Department City and salary so this is the same table that we saw in our slides okay now for our sub query 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 75 350 dollars 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 sub queries with the insert statement now the insert statement uses the data written from the sub query to insert into another table so this is how the syntax looks like so you write insert into table name followed by select individual column so start from the table use the where clause and then you give the operator followed by the inner query or the sub query 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 order stable 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 sub query all right so first of all let's create a table 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 worker 30. next we have the selling price of that 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 worker I'll give the size as 30. close the bracket and give a 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 one zero one the product is let's say jewelry then the selling price is let's say eight hundred dollars and the product type is it's a luxury product next let's insert one more product detail the product ID is one zero two the product is let's say t-shirt the price is let's say a hundred dollars 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 one zero three the product is laptop foreign dollars 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 order stable 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 would be of type varying 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 you should close the brackets okay let me run it so we have our order stable ready now let's write our insert sub query so I am 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 sale price from my table that is products where I'll write product ID in I'll write my inner queries select broad ID or the product ID from products next I'll give a where Clause where the selling price is greater than one thousand dollars so let me tell you what I am going to do here I am going to insert into 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 product ID from products where the selling price is greater than 1000 if I run this okay there is some issue here the column name is actually prod 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 1000 so it is one zero one and one zero three 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 product underscore 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 order stable I'll write select star from orders if I run it there you go so there were two products from a product table that were jewelry and laptop which have a selling price greater than one thousand dollars so the selling price for jewelry was 1800 and for laptop it was thirteen hundred dollars so this is how you can use a sub query using the insert statement all right now going back to our slides again all right now let's see how you can use sub queries with the update statement now the sub queries can be used in conjunction with the update statement so either single or multiple columns in a table can be updated when using a sub query with the updates statement so this is how the basic syntax of an update sub query looks like so you write update table followed by the table name you set the column name you give the where operator and then you write your inner sub query 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 0.35 only for those employees which have age greater than 27 so we are going to use a new table called employees underscore B for this as well so let's see how to do it so I'll give my comment as update sub query before we see the sub query let's see what we have in the table employees underscore B 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 employee stable and the employees underscore 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 point three five where age in then I'll write select each from my other table that is employees underscore 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 am 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 0.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 ages 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 each 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 sub queries with the delete statement now sub queries can again be used in conjunction with the delete statement so this is how the basic syntax of a delete query using sub query 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 keep my comment as delete sub query so we'll follow the syntax that we saw I'll write delete from my table name that is employees I'll write where each in and then I'll start my inner query or the sub query I'll write select each from employees underscore B where age is let's say greater than equal to 32 or let's say the age 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 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 h less than equal to 32. okay so let me show you from the beginning what we did so first we used our sub queries 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 sub query we use two tables products and our order stable moving ahead we saw how to write sub queries using the update command so we updated the salaries of the employee by a factor of 0.35 for those who had an each greater than equal to 27 and finally we saw how to use the sub query 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 MySQL workbench so let's get started first what are triggers in SQL so a trigger is a user defined 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 skill 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 mySQL workbench where we will learn how to create triggers and Explore More on how it works so I am on MySQL 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 am 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 cast underscore 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 a varka 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 moving ahead I am going to write my before insert trigger command so we'll start with a delimiter so a delimiter 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 each underscore 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 Nu dot each is less than zero then set new dot h equal to 0 which means while inserting your values to the each column in case the age value is negative will convert the age to zero I'll give a semicolon and then I'll write end if I'll give another semicolon and close the delimiter now let me run this okay so we have created our trigger now let's insert a few values on to 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 1 0 2 and this time I am purposely giving the age as minus 40 Which is less than 0 so that you can see the output clearly I have given the customer name as Amy let's insert two more records the customer ID with 103 has an ease of 32 and let's see the name is Ben I'll give another comma and finally we'll insert our fourth customer record the customer ID is one zero four the age I am giving as minus 39 Which is less than 0 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 each verify trigger which would change the values of the each column in case the each 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 one zero two and one zero three 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 our new age to zero if the age was less than 0. 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's 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 customer's 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 auto increment 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 worker and I'll use not null constraint next we'll have the email ID of the customer this is again going to be of Barca 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 we'll have the message ID this is going to be of integer type will have a column called message this will be a varka 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 delimiter next I'll use create trigger command so I'll write create trigger my trigger name would be check underscore null underscore 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'll give the columns as message ID and message values new DOT 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 1 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 keep the column names as name email and birth okay will 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 at the rate abc.com now let's see 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 Krish will give the email ID of Chris so Chris at the rate xyz.com and let's see 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 Alice I'll give the email ID as Alice at the rate 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 data birth so for those two values you have the messages hi Nancy please update your date of birth and then you have high Alice 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 War car 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 delimiter followed by the create trigger command I'll give my trigger name as update underscore trigger let's bring this to the top okay I'll use before update foreign 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 ten thousand dollars I'll write then set new DOT salary equal to 85 000 dollars I'll give a semicolon then I'll use else if new DOT salary is less than ten thousand dollars then I'll set new DOT salary to let's say seventy two thousand dollars 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 ten thousand dollars we'll set the new salary to eighty five thousand dollars else if the salary is less than ten thousand dollars will set the salary column values to seven to two thousand dollars so let's 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 eight thousand dollars now if you see our trigger query we are setting the salary to eight thousand dollars which is clearly less than ten thousand dollars so our new salary would be seven to two thousand dollars 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 eight thousand dollars and according to our trigger query if it's less than ten thousand dollars the salary should be seven to two thousand dollars 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 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 is 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 one zero one followed by a date let's say 2005 May and 0 1 which is first of May 2005. let's say the amount is fifty five thousand dollars I'll give another row of information this time the employee ID is 102 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 one zero three 2006. 0901 and let's say the amount is seventy five thousand dollars 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 rows 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 Del now this will have the IDE the type would be integer and primary key I'll also use the auto increment 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 time stamp 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 underscore 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 an amount now we'll insert a few values I'll write values pulled Dot Eid comma Old Dot valid from comma Old Dot amount I am 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 have deleted one row of information that is for employee id103 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 these to zero we inserted a few records to the customers table and purposely we inserted some negative age records and then we converted this either cost to zero then we learned how an after insert trigger works so we created two tables customers one and message so wherever the customers date of birth was missing we passed in a message saying hi the name of the customer please update your date 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 some 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'll be using the MySQL workbench on Windows so let's get started with sub queries in SQL so let me head over to my MySQL 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 OK 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 sub queries all right so first of all let's understand what a sub query is so a sub query is a query within another SQL query that is embedded within the where Clause from clause or having clause so we'll explore a few scenarios where we can use sub queries so for that I'll be using my database that is SQL underscore intro so I'll write my command use SQL underscore intro now this database has a lot of tables I'll be using the employees table that is present inside SQL underscore intro let me just expand this and you can see here we have an employee's 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 each gender there's date of joint 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 see you want to find the employees whose salary is greater than the average salary in such a scenario you can use a sub query so let me show you how to write a sub query 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 am 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 you get the average salary number we'll use this where condition where salary is greater than the average salary number so the inside sub query let me run it first if I run this this gives you the average salary of all the employees which is 75 350 dollars now I want to display all the employees who have salary greater than 75 350 dollars so let's run our sub query 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 John's salary so we have one employee whose name is John let me run the table once again okay 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 sixty seven thousand dollars 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 sixty seven thousand dollars now I want the employees who are earning more than sixty seven thousand dollars so let's run our sub query 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 employees have a salary greater than sixty seven thousand dollars okay now you can also use sub queries with two different tables so suppose you want to display some information that are present in two different tables you can use sub queries 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 Model so 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 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 MySQL 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 MySQL 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 qriket customers as employees office this orders order lines and many more so for our sub query 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 return 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 or the 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 hundred dollars for this scenario we are going to use two different tables and we are going to write a sub query 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 MSRP of the products which have a price of each product less than hundred dollars so the way I'm going to do is I'll write select product code comma product name now one thing to remember this product name is actually present inside our products table and product code is present in both the tables that is products and Order details here you can see this is the product code column comma MSRP which is present inside the products table again from my table that is products where I'll write product code I am 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 dollars let me run this okay so you can see there are total 83 products in our table which have a price less than hundred dollars 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 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 underscore 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 stored 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 MySQL underscore IQ database will run it so now we are inside the SQL underscore 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 delimiter okay in the delimiter I'll write ambition ampersand next I'll write create procedure followed by the procedure name let's say I want to name my procedure as top underscore 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 6. will give us semicolon then I'll end my procedure with a delimiter that was double ambison next I'll write delimiter 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 our 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 top underscore place in our case put 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 are not goal let me create that procedure again okay it says the procedure top underscore player already exists let's just edit the procedure 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 top underscore 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 place 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 example I'll create a procedure that will fetch or display the top records of employees based on their salaries so if you have a table in our SQL underscore 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 joint 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 we'll use the in parameter so let me show you how to do it I'll write delimiter this time I am going to use forward slash I'll write create procedure followed by the procedure name let's say for stored procedure sort by salary is the name of my procedure and inside this procedure I'll give my parameter in I'll create a variable VAR and assign a data type integer then I'll write begin followed by my select statement where I'll select the name each salary from my table name that is EMP details or employee details I am 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 VAR which I created here I'll end my select statement I'll end my stored procedure with forward slash and I'll go back to my default delimiter that 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 sp underscore sort by salary now you can also check whether the stored 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 sp underscore sort by salary the other two were top underscore player and top underscore players okay now let's call our stored procedure I'll write call space followed by the stored procedure name which is sp underscore sort by salary and inside this I'll give my parameter which was actually VAR and this VAR 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 Amy Sarah and Jimmy but the top three employees who 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 delimiter first which is going to be forward slash 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 worker 20 I'll again use my in parameter I'll write in next my other variable would be new underscore 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 new underscore salary where name is equal to my temporary variable that is temp underscore 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 underscore salary okay now let's see first of all I'll display my records that are present inside employee underscore details table okay so we have six rows of information let's say you want to update the salary of employee Jimmy or let's say Mary from 70 000 to let's say 72 000. or let's say eighty thousand so I'll call my stored procedure that is update underscore 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 eighty thousand dollars 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 successfully updated the salary to eighty thousand dollars now moving ahead we learned 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 underscore 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 two forward slash I'll write create procedure followed by the procedure name it is going to be SP underscore count employees and inside this I am going to give my out parameter and the variable name that is total underscore emps which is total employees and the data type will be integer next I am going to write begin followed by my select statement that is Select I want the count of total employees and the output I am going to put into my new variable that is total underscore emps from my table that is EMP underscore details where 6 is equal to F which means female I'll go by semicolon next I'll end it with the DB limiter and I'm going to change the delimiter to a default delimiter that is colon so let me tell you what I am doing here I am creating a new stored procedure that is sp underscore count employees using this stored procedure I am going to count the total number of female employers that are present in our table EMP underscore details so I've used my out parameter and I'm creating a new variable called total underscore emps the data type is integer here in the select statement I'm counting the names of the employees and the result I am storing it in total underscore emps I have used my wear 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 a new stored procedure SP underscore count employees now to call it I'll write call the name of the procedure that is Count underscore SP underscore count employees within brackets I'll pass in the parameter as at the rate F underscore 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 reverse 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 login triggers in this example we will learn how to use a before insert trigger so we will create a simple students table that will have the student's rule 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 0 a 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 roll number the data type is integer it will have the age of the students again the data type is integer we 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 delimiter 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 am 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 Nu Dot marks is less than zero then will set new DOT marks equal to 50. so this is my condition first we'll check before inserting if any student has marks less than 0 we'll assign a value 50 to that student because usually the marks are not less than 0 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 says trigger already exists so in this case we need to update the trigger name let's say I'll write marks underscore verify underscore student for St 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 and give the values as 501 which is the student roll number the age is let's say 10 the name is it's a root and the marks is let's say 75 point 0 if a comma we'll insert our second student record student rule number is 502 ages 12 the name is let's say Mike and this time I'm purposely giving a value of minus 20.5 give another comma we'll insert the Third record for student rule 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 point let's say 5. 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 four five zero two the marks was minus 20.5 and for 504 for Jacobs the marks was minus 12.5 our trigger automatically converted the negative marks to 50 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 marks underscore viric Pi underscore St I'll just paste this here and if you run this it will automatically delete your trigger 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 are created by joining one or more tables I'll give a comment as views in SQL okay now to learn views I am going to use my table which is present inside classic models database now this database as I mentioned we have 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 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 cast underscore details then you write as select I am going to select a few column names from my original customer table 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 the discussed details will be created let's run it there's 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 foreign 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 cast underscore 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 joins so we'll join two different tables and create a view so for that I am going to use my products table and the products lines table I'm talking about the products 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 alias as 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 join to fetch specific columns from both the tables and our view name is product underscore description let us run it all right so we have our view ready now let me View or display what is present inside our product underscore description View I'll select start from product underscore description let's run it there you go so we have the product name the quantity in stock msrpn 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 product underscore 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 renewed my View so here if I just refresh it and I'll expand this you can see we have the cash details view and we have the vehicle underscore 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 so full tables where table underscore type is equal to within single quote 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 underscore 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 underscore details or cast underscore details view I'll write draw View cast underscore details let's run it you can see here we don't have the cast underscore details view anymore all right now moving to our final section in this demo here we will learn about Windows functions the 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 S12 underscore intro database so we'll find the total combined salary of the employees for each department so first let me switch my database to SQL underscore into 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'll 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 this sum of salary over I want to partition it by Department so I'll write Partition by Department which is Dept and I'll give an alias 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 you have the highest salary so in finance the highest salary of one of the employees was hundred and fifty five thousand dollars 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 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 foreign over my column would be salary so I'll write order by salary I'll give the Alias as row num we 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 salary 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 1 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 create table let's say I'll give a random name that is demo and let's see we have in this table the student ID which is of type integer and we have the student name which is of type worker 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 102 the name is Bradley give a comma this time for one zero three we have two records let's say the name of the student is hereat give a comma I'll copy this and we'll paste it again so we have duplicated one zero three next we have one zero four 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 star from demo if you see this we have a few information that are duplicated in our table that is for student id103 and student ID 105. now I am going to use my row number function to find the duplicate records present in my table I'll write select student underscore ID comma student underscore name I'll give another comma and write Rue underscore number over within brackets I'll write partition by St underscore ID comma St underscore name okay then I'll write order by St underscore ID close the bracket I'll give an alias as runum 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 now 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 here the second record it says to which means there are two records for hirath 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 MySQL so the rank function assigns a rank to a particular column now there are gaps in the sequence of rank 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 will 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 zero two 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 one zero four one zero five we have one zero six and let's say one zero six is also repeated finally we have one zero seven okay let me insert these values to my table that is demo one okay this is done now if I write select VAR underscore a and use my rank function I'll write rank over then I'll order by my variable that is VR underscore a as an alias 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 now if I run this there you go so here if you mark so for variable a101 the test rank is one for one zero two the test tank is 2 but for this value which is 1 0 3 the test rank is repeated because there was a repetition for one zero three so we have skipped the rank 4 here for one zero four the rank is 5 now for one zero five the rank is 6 now 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 MySQL so this function Returns the value of the specified expression with respect to the first row in the window frame all right so what I'm going to do is I am 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 alias 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 hundred and fifteen thousand dollars 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 EMP underscore 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 am going to use partition I am going to partition it by department since I want to know the employee name who has the highest salary in each department and I am going to order by salary descending and I'll give my Alias 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 underscore 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 sub queries in SQL so we initially wrote a simple sub query and then we used our classic models database which was downloaded from the internet and also shown you the link from where you can download this database here we used two different tables and we performed a sub query 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 stored procedures so we saw how to write a 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 Jupiter notebook installed so please go ahead and install them first while installing the MySQL workbench you will 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 have given my username as root and password you can give while installing it we'll 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 dot connector next from mysql.connector I am 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 def 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 am 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 try 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 am going to reassign the connection variable to a method which is MySQL Dot connector dot connect now this mysqlconnector.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 hostname I'll write host equal to hostname which is host underscore name I'll give a comma then I'll write user equal to user underscore name next will be my password and I'll assign the value user underscore password all right now I am going to use a print statement and write MySQL database connection successful after this I'll give my except block 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 am going to use some print formatting techniques using the F letter I'll write error colon and I'll use curly braces give vrr and then I'll close the double quotes after this I am 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 p w and I'll give my password which is simply at the rate one two three four five and then I'm going to give my database name so I'll give database name here I am going to write DB equal to this is the database I want to create which is going to be MySQL underscore 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 localhost that is my host name my username which is root and then I'll give PW which is your password that is exactly simply at the rate one two three four five 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 underscore python so I'll give a comment create MySQL underscore python database again to create this database I am going to create another user defined function using the def keyword I'll write the function name as create database pass in 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's 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 colon and here I am going to use cursor Dot execute within that I am going to pass in my query next I'll use a print statement and the message I am going to display is database created successfully after this I am 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 and I'll write within single course I'll give curly braces error and then I'll close the double quotes next let's use the variable create underscore database underscore query and here I am 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 underscore 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 underscore database underscore query let me just copy it and I'm going to paste it here all right so what I am doing here is I am creating 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 dot cursor function which is often used to execute SQL statements using Python language and then I have created my try and accept blocks so this try block statements will try to create my new database which is MySQL underscore python in case it fails to create the new database the excerpt block will work so here I am writing my SQL query to create a new database which is create database followed by the database name and I am 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 underscore 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 now 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 def keyword I'll write create underscore DB which is for database underscore 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 am going to create my variable which is connection and then I'll assign it to a value none after this I am going to use my exception handling techniques so I'll write my drive block first I am going to reassign my connection variable using the MySQL connector method so I'll write MySQL Dot connector dot connect so this method will take in the parameters so first it will take the hostname I'll write host equal to host underscore name I'll give a comma next it will take the username so user equal to user underscore name give another comma next it will take the user password I'll use pass WD equal to user underscore password we'll give another comma and this time it is going to be the database name so I'll write database equal to DB underscore name now let's use the print statement and the message we are going to print is MySQL database connection successful all right finally will write my accept block I'll write accept error as error give a colon and then I'll use the print statement f within double quotes I'll write error colon within single quotes curly braces I'll write err and will 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 dot 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 try block doesn't work through an error using the print statement within double quotes inside the inside the curly braces I'll write err and close the double quotes all right so let's run it okay so we have successfully created our various functions that were 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 underscore python database so to do that I am going to write my create command in SQL so first we are going to assign our SQL command to a python variable using triple quotes 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 to use triple quotes so the triple quote will ensure I can create my multi-line string inside the triple code I am going to write my create command which is create table here I am going to create an order stable first and inside the orders table I am going to create my column names the First Column would be the order ID it is going to be of type integer 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 varying character so I'll write worker 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 product underscore name product name will be of type varying character the size is let's say 20 and it is also not null next I am 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 unit price can be of type float and finally I'll have the phone number of the customer I'll write phone number phone number can be kept as of type varying character I've assigned a size of 20 now let's give a semicolon and will close the triple quotes all right so this is how the syntax would look like next to run this we are first going to call our create DB function so let me give a comment as connect to the database I'll write connection equal to create underscore DB underscore connection my parameters would be my host name which is localhost my username which is root comma my password and then my database name which is MySQL underscore python so I'll write just DB all right finally let's execute this query using the execute underscore query function that we had created earlier this takes into parameter the first one is connection followed by 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 quotes this should be triple quotes now let's run it okay there is another here let's debug it says name cursor not defined let me just roll it to the above cell if you see here in our execute underscore query function instead of cursor I have written cursor so R is missing let's read on this and now let's run this again there you go you can see here mySQL 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 you can check it on the MySQL workbench so let me show you how to do it so I am on my MySQL workbench and under MySQL underscore python database you have something called Stables 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 a 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 data underscore orders I'll give triple quotes next I'll write my insert into command so 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 1 0 1 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 zero six as the month and the date is let's say 12. we'll give another comma this time we'll pass in the quantity which is 2 let's say the price of each laptop was eight hundred dollars and will give a phone number this is random let's say six to nine three seven three zero let's say 802. all right similarly I am going to insert five more records of different customers and their items that they have purchased to the stable orders so here on my notepad I have my rest of the five records let me just copy it and we'll 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 Jaws 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 underscore DB underscore connection then I'm going to give my same parameters let me just copy it from the top which is localhost the hostname 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 data underscore orders so this will store my insert into command you can see the variable I have used here is data underscore 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 triple quotes and not four now let me rerun it again there you go you can see here mySQL 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 into parameters connection and query then I'll write cursor equal to connection dot 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 query and then I'll give another variable which is result equal to cursor dot fetch all now this virtual method will turn 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 reuse my print statement scroll this down I'll use my formatting f error give a colon followed by a space within single quotes inside curly braces I'll give error and close my double quotes 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 Auto by 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 order stable so the way to do is I'll assign my query to a variable let's say q1 I'll give triple quotes within triple quotes I'll write select star from orders will give a semicolon followed by the triple quotes 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 foreign 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 am going to use a for Loop I'll write four 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 you have successfully printed all the rows in our table which is orders you can see 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 Q2 now instead of star I am going to display only the customer name and let's see the phone numbers of the customer so I'll write phone underscore number all right the rest all Remains the Same let me just recheck it and here instead of q1 will 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 years that are present in the order date so to do that I am going to use the ear function I'll add this query instead of q1 I'll make it Q3 and here I am 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 three let's run it there you go so we have successfully extracted the different years present in the order date column now if you want to display the distinct or the unit 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 the Q3 I'll write Q4 I'll make this as Q4 let's run it you can see 2018 and 2019 are the unique your 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 where 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 2018. so to filter this we are going to use the where 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 2018. now moving ahead let's write a seventh query now let's see how the order 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 am 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 now if you want to order it in descending order you can use the keyword desc 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 result in results I'll assign result to list of results so I am converting the result into a list and then I am going to append it to the empty variable or the empty list which is from underscore DB dot append I'll append the result to my empty list 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 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 quotes 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 am going to pass is from underscore 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 am creating a empty list first and then I am creating a for Loop and I am appending the results to my empty list here you can see I have created my column list and using pd.d to frame I'm converting the list into a data frame if I run this this is append and not appends 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 unit underscore price if you see this let's say I want to set the unit price of trousers from 50 dollars to let's say forty five dollars I want to update this particular record so I'm going to write set unit price column equal to 45 dollars where the order ID equal to one zero three so this query will update the third row in our table which is order id103 so it will update from 50 to 45 dollars I'll close the triple quotes 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 underscore query and this will take into parameters as always which is going to be connection followed by the variable name that is update let's run it you see here it says mySQL database connection successful query was successful now you can recheck that to do it let me just go to the top and we'll just copy our first query which is q1 I'll copy this and I'll paste it here let me just rename this now this will be Q8 and I'll change this as well I'll write select start from orders where my order ID equal to one zero three let's see the unit price of one zero three now you can see here instead of 50 now we have updated it to 45 dollars all right now the last command we are going to say is how you can delete a record from the table I'll write delete command as my comment now to delete a query I'll first keep my variable name which is delete underscore order and I'll pass in within triple quotes next I'll write my delete query which is delete from my table name that is orders then I'll give my where Clause where let's say I want to delete my order ID 105. let me just go to the top and explain you again so if you see this we want to delete the order id105 which was for customer name Maria and shared ordered headphones we want to completely remove this particular record so I have my delete query ready now let me just create my connection and display the results so I'll go to the top and I'll copy this connection command which also has the execute query command and I'll paste it here and I'm going to make a change here instead of update we'll write delete underscore order everything looks good let's just run it you can see that our query was successful and now if you want to print it let me just show you I'll just copy this will paste it here I'll make this as q9 I want to verify if my order id105 was deleted or not instead of the statement I'll write select start from orders and here I'll change this to q9 if I run this you can see it here you can Mark order id105 was deleted and it no more appears in this table all right so this brings us to the end of the demonstration on SQL with python let me just scroll you through what we did so first we imported the important libraries MySQL connector then we imported the error function then we imported pandas using PD we learned how to create a server connection to mySQL database we created a new database that is MySQL underscore python and now we connected to the database we created a function to execute our queries we saw how you can write a create table command then we inserted a few records to our orders table we created a read underscore query command to read the queries and display the results then we started exploring our different SQL commands one by one we saw how to use select query then we selected a few individual columns from our table followed by using a inbuilt function which was here then we saw how to use the distinct keyword after that we used our where Clause to filter our table based on specific conditions we saw how to order your results based on a particular column then we saw how you could convert the table into a data frame using pd.dataframe function finally we learned how to use the update command and the delete command postgres SQL is very popular and widely used database in the industries in this tutorial we will learn postgres SQL or postgres SQL in detail with an extensive demo session so in today's video we will learn what postgres SQL is and look at the history of postgres SQL we will learn the features of postgresql and jump into performing postgres SQL commands on the SQL cell and PG admin so let's begin by understanding what is postgres SQL postgres SQL is an open source object relational database management system it stores data in rows with columns as different data attributes according to the DB engine's ranking postgres SQL is currently ranked fourth in popularity amongst hundreds of databases worldwide it allows you to store process and retrieve data safely it was developed by a worldwide team of volunteers now let's look at the history of postgres SQL so in 1977 onwards the Ingress project was developed at the University of California Berkeley in 1986 the postgres project was led by Professor Michael Stonebreaker in 1987 the first demo version was released and in 1994 a SQL interpreter was added to postgres the first postgres sequel release was known as version 6.0 or 6.0 on January 29 1997 and since then postgresql has continued to be developed by the postgresql global Development Group a diverse group of companies and many thousands of individual contributors now let's look at some of the important features of postgres SQL suppose Chris sequel is the world's most advanced open source database and is free to download it is compatible as it supports multiple operating systems such as Windows Linux and Mac OS it is highly secure robust and reliable postgres SQL supports multiple programming interfaces such as C C plus plus Java and python postgres SQL is compatible with various data types it can work with Primitives like integers numeric string and Boolean it supports structured data types such as date and time array and range it can also work with documents such as Json and XML and finally postgres SQL supports multiversion concurrency control or mvcc now with this Theory knowledge let's look at the postgres SQL commands that we will be covering in the demo so we will start with the basic command such as select update and delete we will learn how to filter data using where clause and having clause in SQL we will also look at how to group data using the group by clause and order the result using the order by Clause you will learn how to deal with null values get an idea about the like operator logical operators such as and and or we will also explore some of the popular inbuilt mathematical and string functions finally we'll see some of the advanced concepts in postgres SQL that is to write case statements sub queries and user defined functions so let's head over to the demo now okay so let's now start with our demo so first we'll connect to postgres SQL using psql cell so here under type here to search I'll search for psql you can see this is the SQL cell I'll click on open let me maximize this okay so for Server I'll just click enter database I'll click enter port number is already taken which is 5432 I'll hit enter username is already given and now it is going to ask for password so here I'll give my password so that I can connect to my postgres SQL database so it has given us a warning but we have successfully connected to postgres SQL all right so now to check if everything is fine you can just run a simple command to check the version of postgres SQL that we have loaded so the command is select version with two brackets and a semicolon I'll hit enter okay you can see the version postgres equal 13.2 okay now let me show you the command that will help you display all the databases that are already there so if I hit slash l and hit enter it will give me the list of databases that are already there so we have postcode SQL there's something called template 0 template one and we have a test database as well okay now for our demo I'll create a new database so first I'll write create space database and I'll give my database name as SQL underscore demo I'll give a semicolon and hit enter you see we have a message here that says create database so we have successfully created our SQL demo database now if you want to connect to the database you can use backslash C space SQL underscore demo there you go it says you are now connected to database SQL underscore demo so here we can now create tables we can perform insert operation select operation update delete alter and much more now I'll show you how to connect to postgres SQL using PG admin so when you install the postgres SQL database you will get the SQL cell and along with that you also have the PG admin so I'll just search for PG you can see here it has prompted PJ admin I'll click on open this will open on a web browser you can see it has opened on Chrome and this is how the interface of PG admin looks like it is a very basic interface so on the top you can see the files we have object this tools and we have the help section as well and here you have dashboard properties SQL statistics dependencies dependence and here on the left panel you have servers let me just expand this so it will connect to one of the databases all right so if I go back you see when I had run backslash L to display the databases it had shown me postgres SQL and test now you can see here we have the postgres SQL database and the test database all right now we also created one more database which was SQL demo so let me show you how to work on this PG admin and the query tool all right so I'll right click on SQL demo and I'll select query tool I'll just show you how to run a few commands on the query tool so let's say you want to see the version of postgresql that you are using so you can use the same command that we did on psql cell which is Select version closed with brackets and a semicolon I'll select this and here you can see we have the execute button so if I hit execute or press F5 it will run that query you can see we have the output at the bottom and it says postgres SQL 13.2 compiled by visual C plus plus it has a 64-bit system okay now let me tell you how to perform a few basic operations using postgres SQL commands so here let's say I'll write select 5 into three I'll give a semicolon select this and hit F5 so this will run the query and it returns me the result that is the product of 5 and 3 which is 15. similarly let's edit this let's say I'll write 5 plus 3 plus let's say 6. I'll select this and hit F5 to run it it gives me the sum of 5 plus 3 plus 6 which is 14. now the same task you can do it on this cell as well let me show you how to do it here so let's say I'll write select let's say I want to multiply 7 into let's say 10. you know the result it should be 70 if I hit enter it gives me 70. now this question mark column question world will deal with this later all right let me go back to my pj admin again let me do one more operation let's say this time I'll write select 5 multiplied by and within brackets I'll write 3 plus 4. I'll give a semicolon so what SQL will do is first it will evaluate the expression that is there inside the bracket that is 3 plus 4 which is 7 and then it will multiply 7 with 5. now let me select this and I'll hit execute so you can see 7 multiplied by 5 is 35. all right now we'll go back to our shell and here I'll show you how to create a table so we are going to create a table called movies on the cell that is psql cell so here we will learn how you can create a table and then you can enter a few data into that table all right let me just scroll down a bit okay so my create command goes something like this so I'll write create table followed by the table name that is movies next my movies table will have a few columns let's see I want the movie ID after the column name we need to give the data type so movie ID I'll keep it as integer so integer is one of the data types that is provided by postgres SQL next my second column the table would be the name of the movie so I'll write movie underscore name so all the variables or the column name should be as per SQL standards so there shouldn't be any space between the column names so I have used underscore to make it more readable so my movie name will be of type VAR car or variable character or varying character and I'll give this size as 40 so that it can hold 40 characters maximum next my third column will have the genre of the movie so I'll write movie underscore Jonah again genres of type worker I'll give the size as let's say 30. and my final and the last column will have the IMDB rating so I'll write IMDb underscore ratings now the ratings will be of type real since it can have floating or decimal point values if I close the bracket I'll give a semicolon and I'll hit enter there you go so we have successfully created a table called movies now let me go back to my PG admin all right so here I have my database that is SQL demo I'll just right click on this and click on refresh now let me go to schemas I'll just scroll down a bit here under schemas we have something called as tables let me expand this okay so you can see we have a table called movies in the SQL demo database now and here you can check the columns that we have just added so our movies table has movie ID movie name genre and ratings all right now there is another way to create a table the previous time we created using the SQL cell now I'll tell you how to create a table using the PG admin so here under tables I'll right click and I have the option to create a table so I'll select table okay so it's asking me to give the name of the table so this time we are going to create a table called students so I'll write my table name as students all right these will be default as it is now I'll go to the columns tab so here you can create the number of columns that you want so you can see on the right I have a plus sign I'll just select this so that I can add a new row so my first column would be let's say the student rule number I'll write student underscore rule number again the column name should be a spur SQL standards the data type I am going to select is integer all right now if you want you can give these constraints such as not null so that student rule number column will not have any null values and I'll also check primary key which means all the values will be unique for roll numbers all right now if you want to add another column you can just click on that plus sign and let's say this time I want to give the student name as my second column so I'll write student underscore name student name will be of type let's say character varying if you want to give the length you can specify the length as well let's say 40 I'll click on the plus sign again to add my final column the final column would be gender so gender I'll keep this time as type character okay now you can click on save so that will successfully create your students table there you go so here on the left panel you can see earlier we had only one table that was movies and now we have two tables so one would be added that was students so if I expand this under columns you can see we have the three columns here student rule number student name and gender you can also check the constraints it will tell you if you have any constants so you can see it says students rule number this one primary key all right all right now let me run a select statement to show The Columns that we have in the movies table so I'll right select star from movies give a semicolon and let me execute this okay so here on the at the bottom you can see we have the movie ID the movie name movie genre and IMDB ratings now the next command we are going to learn is how to delete a table so there is one way by using the SQL command that is drop table followed by the table name let's say you want to delete students you can write drop table students and that will delete the table from the database this is one of the methods so you just select and run it now the other way is to you just right click on the table name and here you have delete slash drop if I select this you get a prompt are you sure you want to drop table students I'll select yes so you can see we have successfully deleted our students table all right now let's perform a few operations and learn a few more commands in postgres SQL so to do that I am going to insert a few records to my movies table so for that I'll use my insert command so I have my insert query written on a notepad I'll just copy this and I'll paste it on my query editor okay so let me just scroll down all right so here you can see I have used my insert command so I have written insert into the name of the table that is movies and we have the movie ID the movie name movie genre and IMDB ratings and these are the records or the rows so we have the first record as movie id101 the name of the movie is a very popular movie which is vertigo then we have the movie genre that is Mystery it is also a romance movie and then we have the IMDB ratings the current IMDb rating that is 8.3 similarly we have Shawshank Redemption we have 12 Angry Men there's the Matrix seven Interstellar and The Lion King so there are total eight records that we are going to insert into our movie stable so let me just select this and hit execute okay you can see it has returned successfully eight records now if I run select star from movies you can see the records that are present in the table so I'll write select star from movies I'll select this and I'll execute it there you go at the bottom you can see eight rows affected if I scroll this down you have the eight records of information in the movies table all right now if you want to describe the table you can go to the SQL cell and here if you write backslash D and the name of the table that is movies this will describe the table so here you have the column names this has the data type and here you can specify if there are any null values or any constraints like default constraint or primary key or foreign key and others let me go back to my PG admin okay now first and foremost let me tell you how to update records in a table so suppose you have an existing table and by mistake you have entered some wrong values and you want to update those records later you can use the update query for that so I am going to update my movies table and I'll set the genre of movie ID 103 which is 12 Angry Men from drama to drama and crime so in our current Table we only have genres drama for 12 Angry Men I am going to update this column which is the movie genre 2 drama and crime okay so let me show you how to do it I'll write update followed by the name of the table that is movies go to the next line I'll write set then I'll give the column name which is movie underscore Jonah equal to I am going to set it as drama comma crime earlier it was only drama and I'll give my condition using the where Clause we'll learn where clause in a bit so I'll write where movie underscore ID is equal to 103 so here our movie ID is the unique identifier so it will first look for movie id103 it will locate that movie and it'll change the genre to drama and crime so now you can see the difference earlier we had 12 Angry Men as drama as the movie genre now if I run this update statement okay you can see we have successfully updated one record now let me run the select statement again okay so here you can see if I scroll down there you go so movie id103 movie name 12 Angry Men we have successfully updated the genres drama comma crime okay now let me tell you how you can delete records from a table so for that you can use the delete command so you'll write delete from the table name that is movies where let's say I want to delete the movie ID 108 which is The Lion King so I'll write where movie underscore ID is equal to 108. this is one of the ways to delete this particular movie or you can give let's see where movie name is equal to The Lion King let me select this and I'll hit execute now if I run my select query again you see this time it has returned seven rows and you cannot find movie with movie ID 108 that was The Lion King so we have deleted it all right next we are going to learn about where clause in postgres SQL so to learn where clause I'll be using the same movie table again let's say we want to filter only those records for which the IMDB ratings of the movies is greater than 8.7 so this is my updated table now I want to display only those records or those movies whose IMDB ratings is greater than 8.7 so we'll display 12 angry man which is 9. then we are going to display The Dark Knight which is again 9 and we are also going to display the Shawshank Redemption which has 9.3 the rest of the movies have and IMDb rating less than 8.7 so we are not going to display those all right so let me show you how to write a where clause so I'll write select star from movies where I'll give my column name that is IMDb underscore ratings is greater than I'll use the greater than symbol then I'll pass my value that is 8.7 I'll give a semicolon and let's run it I'll hit F5 there you go so we have returned the Shawshank Redemption The Dark Knight and 12 Angry Men because only these movies had IMDB ratings greater than 8.7 okay now let's say you want to return only those movies which have IMDB ratings between 8.5 and 9. so for that I am going to use another operator called between along with the where clause so let me show you how to use between with where clause I'll write select star from movies where my IMDb underscore ratings is between I'll write 8.5 I'll give an and operator and 9.0 so all the movies that are between 8.5 and 9.0 ratings will be displayed so let's select this and I'll run it there you go so we have returned the Dark Knight The Matrix the seven Interstellar and we have the 12 Angry Men so a few of the records that we missed out where I think vertigo which has 8.3 and this one more all right now moving ahead let's say you want to display the movies whose movie genre is action you can see in a table we have a few movies whose genre is action movie so you can do that as well I'll write select star from movies where the movie genre I'm writing this time in one line you can break it into two lines as well I'll write movie underscore genre which is my column name equal to I'll give within single quotes action now why single code because action is a string hence we need to put it in single quotes if I run this there you go so we had one movie in our table whose movie genre was action that is The Dark Knight okay now you can also select particular columns from the table by specifying the column names now here in all the examples that we saw just now we are using star now star represents it will select all the columns in the table if you want to select specific columns in the table you can use the column names so you can specify the column names in the select statement let me show you let's say you want to display the movie name and the movie genre from the table so you can write select movie underscore name comma I'll give the next column as movie underscore genre from my table name that is movies where let's say the IMDb underscore ratings is less than 9.0 so this time in our result it will only show two columns that is movie name and movie genre let me run it there you go so these are the movie names and the movie genres you can see that have an IMDB ratings less than 9.0 all right like how you saw the between operator there is one more operator that you can use with the where Clause that is the in operator so the in operator works like a or class or an or operator so let's say I want to select all the columns from my movies table where the IMDb ratings is in 8.7 or 9.0 if I run this it will display only those records whose IMDB ratings is 8.7 or 9.0 all right so up to now we have looked at how you can work on basic operations in SQL like your mathematical operations you saw how a select statement works we created a few tables then we inserted a few records to our tables we saw how you can delete a table from your database and we have performed a few operations like update delete and we saw how aware Clause works now it's time to load a employee CSV file or a CSV data set to postgresql so I'll tell you how you can do that but first of all before loading or inserting the records we need to create an employee table so let me first go ahead and create a new table called employees now SQL underscore demo database so I'll write create table my name of the table would be employees next I'm going to give my column names so my first column would be employee ID so the employee ID will be of type integer it is not going to contain any null values so I'll write not null and I'll give my constraint as primary key so the employee ID as you know is unique for all the employees in a company so once I write primary key it will ensure that there are no repetition in the employee IDs okay next I'll have my employee name so my employee name is going to be of type VAR car and I'll give my size as 40. okay next we'll have the email address of the employee again email address would be of type and the size is 40 again we'll give another comma this time we'll have the gender of the employee gender is again worker of site let's say 10 okay now let's include a few more columns we'll have the department column so I'll write Department let's say the size is 40. then let's say we'll have another column that is called address so the address column will have the country names of the employees and this is also of our car and finally we have the salary of the employee salary I'm going to keep it as type real so real will ensure it will have decimal or floating Point values okay so now let me select this create table statement and execute it all right so we have successfully created our table if you want you can check by using select star from employees let me select this and I'll hit execute all right you can see we have our employee ID as primary key there's employee name email gender this department address and salary but we don't have any records for each of these columns now it's time for us to insert a few records to our employees table now to do that I am going to use a CSV file so let me show you how the CSV file looks like okay so now I am on my Microsoft Excel sheet and on the top you can see this is my employee data.csv file here we have the employee ID the employee name email gender this department address and salary now this data was generated using a simulator so this is not validated and you can see it has a few missing values so under email column you have a few employees who don't have an email ID then you can see on the department also there are some missing values here as well all right so we'll be importing this table or the records present in the CSV file onto postgres SQL all right so here in the left panel under tables let me right click and first refresh this there you go so initially we had only movies table and now we also have the employees table now what we need to do is I'll right click again and here you see we have the option to import or export let me click on this and I don't want to export I need to import so I'll switch on import all right now it is asking me to give the file location so let me show you how to get the file location so this is my file location actually so my Excel file which was this is present in my e Drive under the data analytics folder I have another folder called postgresql and within the postgres SQL folder I have my CSV file that is employee data.csv so I'll just select this you can either do it like this or you can browse and do okay now my format is CSP next I'm going to select my headers as yes and then let me go to columns and check if everything is fine all right so I have all my columns here let's click on OK you can see I have a message here which says import underscore export all right so here you can see successfully completed we can verify this by using select start from employees again if I run this all right let me close this there you go it says 150 rows affected which means we have inserted 150 rows of information to our employees table you can see we have the employee ID these are all unique we have the employee name the email we have the address and the salary let me scroll down so that okay you can see we have 150 rows of information that means we have 150 employees in our table okay now we are going to use this employee stable and explore some Advanced SQL commands now there is an operator called extinct so see if I write select address from employees this is going to give me 150 address of all the employees there's some problem here I did a spelling mistake there should be another d if I run this again I'll query will return 150 rows you can see we have the different country names under address that is Russia we have France there's United States we have Germany okay and I think we have Israel as well yeah now suppose you want to display only the unique address or the country names you can use the distinct keyword before the column name so if I write select distinct address from employee it will only display the unique country names present in the address column if I run this see it has returned us six rows of information so we have Israel Russia Australia United States France and Germany all right now as I said there are a few null values which don't have any information so you can use the is null operator in SQL to display all the null values that are there suppose I want to display all the employee names where the email ID has a null value so I'll select star from employees where email is null so this is another way to use your where clause if I select and run this there you go so you see here for all these employee names there was no email ID present in the table so it has returned us 16 rows of information so around 10 percent of employees do not have an email ID and if you see a few of them do not have an email ID and also they don't have a department so if you want to know for those employees which do not have a department you can just replace where department is null instead of where email is null now if I select this okay it has returned us nine rows of information which means around five percent of employees do not have a department moving ahead now let me show you how the order by Clause Works in SQL now the order by is used to order your result in a particular format let's say in ascending or descending order so the way to use is let's say I want to select all the employees from my table so I'll write select star from employees order by I want to order the employees based on their salary so I'll write order by salary let me select and run it okay there is some problem I made a spelling mistake this should be employees let me run it again okay now if you mark the output a result has been ordered in ascending order so all the employees which have salary greater than 45 000 appear at the top and the employees with the highest salaries appear at the bottom so this has been ordered in ascending order which means your SQL or postgres SQL orders it in ascending order by default now let's say you want to display the salaries in descending order so that all the top ranking employees in terms of salary appear at the top so you can use the desc keyword which means descending if I run this you can see the difference now so all the employees with the highest salary appear at the top while those with the lowest salaries appear at the bottom so this is how you can use an order by clause okay so now I want to make a change in my existing table so here if you see under the address column we only have the country names so it would be better if we change the name of the address column to Country so I want to rename a column you can do this using the alter command in postgres SQL so let me show you how to rename this column that is address so I'll write alter table followed by the table name which is employees then I am going to use rename column address I'll write 2 I want to change it to Country if I give a semicolon and hit execute it will change my column name to Country now you can verify this if I run the select statement again there you go earlier it was address column and now we have successfully changed to Country column okay let me come down now it's time for us to explore a few more commands so this time I'm going to tell you how an and and an or operator Works in SQL so you can use the and and or operator along with the where Clause so let's say I want to select the employees who are from France and their salary is less than eighty thousand dollars so let me show you how to do it I'll write select star from employees where I'm going to give two conditions so I'll use the and clause or the and operator here I'll write where country is equal to France now Mark here I'm not using address because we just updated our table and changed the column name from address to Country so I'll write country equal to France and my next condition would be my salary needs to be less than eighty thousand dollars I'll give a semicolon let me run this all right so it has returned 19 rows of information you can see all my country names of France and the salary is less than eighty thousand dollars so this is how you can use or give multiple conditions in a where Clause using the and operator now let's see you want to use the or operator and let's you want to know the employees who are from country Germany or the department should be sales so I'll write select star from employees where country is equal to Germany and instead of N I am going to use or the department should be sales okay now let's see the output I'll hit F5 this time to run it all right so we have 23 rows of information now let me scroll to the right you can see either the country is Germany or the department is sales you see one of them in the table so here for the first record the country was Germany the second record the department was sales again sales again for the fourth record the country is Germany so this is how the over condition works so if one of the conditions are true it will return the result it need not be that both the conditions should satisfy now in postgres SQL there is another feature that is called limit so postgresql limit is an optional clause on the select statement now this is used as a constraint which will restrict the number of rows written by the query suppose you want to display the top 5 rows in a table you can use the limit operator suppose you want to skip the first five rows of information and then you want to display the next five you can do that using limit and offset so let's explore how limit an offset works I'll write select star from employees let's say I'll use my order by Clause I'll write order by salary let's say in descending and limit it to 5. this is going to display the top five employees which have the highest salary if I run this there you go you see it has given us five rows of information and these are the top five employees that have the highest salary okay so this is one method of or one way of using the limit clause now in case you want to skip a number of rows before returning the result you can use offset Clause placed before the limit clause so I'll write select star from employees let's say order by salary I don't know descending this time I am going to use limit 5 and offset 3. so what this query will do is it will skip the first three rows and then it will print the next five rows if I run this there you go so this is how the result looks like okay now there is another clause which is called Fetch let me show you how that works I'll copy my previous SQL query I'll paste it here and here after descending I am going to write fetch first three row only so my fetch is going to give me the first three rows from the top there you go it has given us the first three rows and you can see the top three employees that have the highest salary since we ordered it in descending order of salary all right you can also use the offset along with the fetch Clause I'll copy this again and let me paste it here now after descending I am going to write offset let's say three rows and fetch first five rows only so what this SQL query is going to do is it will skip the first three rows of information and then it is going to display the next five rows it is going to work exactly the same as we saw for this query let me run it there you go so these are the first five rows of information after excluding the top three rows all right we have another operator that is called as like in postgresql so like is used to do pattern matching so suppose you have a table that has the employee names you forgot the full name of an employee but you remember the few initials so you can use the like operator to get an idea as to which employee name it is now let's explore some examples to learn how the like operator Works in postgres SQL so suppose you want to know the employees whose name starts with a so for that you can use the like operator let me show you how to do it so I want to display the employee name and let's say I want to know their email IDs from the table name that is employee where since I want to know the employees whose name starts with a so I'll write employee name like now to use the pattern is within single course I'll write a and Then followed by percentage now this means the employee name should have an A in the beginning and percentage suggests it can have any other letter following e but in the beginning or the starting should be a if I run this so there is an error here the name of the table is employees and not employee let's run this again there you go you can see there are 16 employees in our table whose name starts with a you can see this column employee name all of them have a letter A in the beginning okay now let me just copy this command or the query I'll paste it here let's say this time you want to know the employees whose name starts with s so instead of a I'll write s so this means the starting letter should be S and followed by it can have any other letter if I run this so there are 10 employees in the table whose name starts with s okay let's copy the query again and this time I want to know the employees whose name ends with d now the way to do it is instead of a percentage I'll write this time percentage D which means at the beginning it can have any letter but the last letter in the string or in the name should be ending with d now let me copy and run this so there are 13 employees in the table whose name ends with a d you can see it here all right now let's say you want to find the employees whose name contains ish or have ish in their names so the way to do is something like this so I'll copy this now here instead of a percentage I'll replace this with percentage ish percentage now this means that in the beginning it can have any letter and towards the end also it can have any letter but this ish should appear within the name let me run and show it to you okay so there is one employee whose name contains ish you can see here there is an ish in the last name of the employee all right now suppose you want to find the employee name which has you as the second letter it can have any letter in the beginning but the second letter of the employee name should have U now the way to do is I'll copy this and instead of a percent I'll write underscore U followed by percent now this underscore you can think of a blank that can take any one letter so the beginning can start with a B C D or any of the 26 alphabets we have then it should contain u as the second letter followed by any other letter or letters let me run this okay so there are 10 employees in the table whose name has a u as the second letter you can see these okay now moving ahead let me show you how you can use basic SQL functions or inbuilt functions so we'll explore a few mathematical functions now so let's say you want to find the total sum of salary for all the employees so for that you can use this sum function that is available in SQL so I'll write sum and inside the sum function I'll give my column name that is salary from my table name that is employees let's see the result this will return one unique value there you go now this is the total salary since the value is very large it has given in terms of E now one thing to note here is if you see the output the column says some real so this output column is not really readable so SQL has a method which can fix this that is called an alias so since we are doing an operation of summing the salary column we can give an alias to this operation by using the as keyword so if I write sum of salary as let's say total salary then this becomes my output column you can see the difference if I run this okay you can see now in the output we have the total salary now this is much more readable than the previous one so this is a feature in Excel where you can use or give Alias names to your columns or your results now similarly let's say you want to find the average of salary for all the employees now SQL has a function called AVG which calculates the mean or the average salary if I write AVG and I can edit my Alias name as well let's see I'll write mean salary let's run it you can see the average salary for all the employees it's around 81 000 dollars okay now there are two more important functions that SQL provides us which is Max and minimum so if I write select maximum or Max which is the function name of salary as let's say instead of total I'll write maximum so this will return me the maximum salary of the employee let's run it and see what is the maximum salary that is present in the salary column all right so we have one like nineteen thousand six hundred and sixteen dollars as highest salary of one of the employees similarly you can use the Min function as well I'll just write minimum and this will return me the minimum salary of one of the employees in the table I'll replace the ice Alias name as minimum okay now run it this will give me the minimum salary that is present at a table so it is 45 685 dollars okay now let's say you want to find the count of Department in the employees table you can use the count function so if I write select count let's say I want to know the distinct Department name so I can write inside the count function distinct Department as total departments from employees let's run this this will return me the total number of departments that are there so it gives me there are 12 departments okay now let me show you one more thing here if I write select Department from employees let's run this okay so it has returned me 150 rows of information but what I'm going to do is I'll place my distinct keyword here just before the column name so that I can verify how many departments are there in total there you go so there are 13 departments and one of them is null so moving ahead we'll replace this null with a department named by updating a table okay so now let's update our department column so what we are going to do is wherever the department has a null value we are going to assign a new Department called analytics so earlier we have also learned how to use the update command so I am going to show it again so we'll write update followed by the table name that is employees I am going to set my column that is Department equal to within single quotes my name of the department would be Analytics where Department is I'll say null so wherever the department has a null value will replace those information with Department that is Analytics let's run this you can see query returned successfully now let's say I'll run this command again and this time you can see the difference there you go so we have 13 rows of information and there is no null department now we have added a new department that is Analytics okay now we are going to explore two more crucial commands or Clauses in SQL that is group bind having so let's learn how Group by Clause Works in postgres SQL so the group by statement groups rows that have the same values into summary rows for example you can find the average salary of employees in each country or city or Department so the group by Clause is used in collaboration with the select statement to arrange identical data into groups so suppose you want to find the average salary of the employees based on countries you can use the group by Clause so let me show you how to do it I'll write select I want the countries and the average salary for each country so I'll use the average function that is evg and inside the function I'll pass my column that is salary I'll give an alias name as let's say average underscore salary from my table name that is employees next I am going to use my group by Clause so I'll write Group by since I want to find the average salary for each country so I'll write Group by country name let's give a semicolon and let me run it I'll use F5 there you go so here on the left you can see the country names we have Israel Russia Australia United States France and Germany and on the right the second column you can see the average salary for each of these countries now you can also order the result in whichever way you want suppose you want to arrange the results based on the average salary so you can use the order by Clause after the group by Clause so I'll write order by here you can use the Alias name that is average salary this is actually average underscore salary and let's say I want to arrange it in descending order so I'll write desc now let's run this you can mark the difference in the average salary column there you go so as per our result in United States the average salary is the highest and if I scroll down the average salary is the lowest in Germany now let's see one more example using Group by suppose this time you want to find the maximum salary of male and female employees you can do that too so let me show you how to do it so I'll write select this time we want to find the maximum salary based on gender so I'll select my gender column comma and this time I'll use my Max function since I want to find the maximum salary for meal and premium employees I'll give an alias name as maximum underscore salary from my table that is employees Group by I'll write gender okay so let's run this there you go you can see so one of the female employees had a higher salary of one lakh 19616 while of that of a meal was one lakh seventeen thousand six hundred and fifty four dollars all right now suppose you want to find the count of employees based on each country you can use the count function along with the group by clause so I'll write the select statement select since I want to count the employees based on each country so I'll first select my country column and then I'm going to use the count function I'll write count EMP underscore ID from my table name that is employees I am going to group it by country so this query will give me the total number of employees from each country you can see here Israel there are four employees in Australia there are four employees in Russia we have 80 employees in France there were 31 in United States we have 27 so on and so forth now let me scroll down okay now it's time to explore one more Clause a very important clause that is used in postgresql that is having so the having Clause works like the where Clause the difference is that where Clause cannot be used with aggregate functions the having Clause is used with the group by Clause to return those rows that meet a condition so suppose you want to find the countries in which the average salary is greater than 80 000 so you can use the group by clause and the having Clause to get the result so I'll write my select statement as select country comma I want the average salary so I'll write evg of salary I can give an alias name as average salary from employees now I am going to group it by each country so Group by country column since I want to find the countries in which the average salary is greater than 80 000 so I'll use having clause after the group by clause I'll write having average of salary is greater than eighty thousand now this condition cannot be specified in the where Clause so we need a having Clause you cannot use aggregate functions along with where clause let me just run it now there you go so we have Russia and United States where the average salary is greater than eighty thousand dollars all right now let's see you want to find the count of employees in each country where there are less than 30 employees so for this I am going to use the count function first let me select the country column then I'm going to use the count function and in the count function I'm going to pass my employee ID so that we can count the number of employees from my table that is employees now if you want you can use an alias name for this as well but I am just keeping it for the time being I'll write Group by country next I'll write having count of employee ID less than 30. so this will return me the countries in which there are less than 30 employees let's run it you can see here Israel Australia United States and Germany are the countries in which there are less than 30 employees okay now if you want you can use the order by Clause as well so suppose I'll write here order by count of employee ID so what this will do is it will arrange my result in ascending order of employee ID count there you can see we have successfully arranged our result in ascending order of employee IDs okay next we are going to explore one more feature of postgres SQL that is of using a case statement now in postgres SQL the case expression is same as if else statement in any other programming language it allows you to add if else logic to the query to form a powerful query now let me just scroll down and I'll show you how to use a case statement this is very similar to your IFL statement that you use on Excel in C plus plus in Python and or any other programming language so what I'm going to do is I'm going to write a SQL query that will create a new column and the name of the column would be let's say salary range so I am going to divide my salary suppose if the salary is greater than 45 000 and if it's less than 55 000 dollars in the new column that is salary range we are going to assign a value low salary now if the salary is greater than 55 000 and if it is less than eighty thousand dollars we are going to assign a value that is medium salary if the salary is greater than eighty thousand dollars we'll assign a value High salary so all this we are going to do using our case expression in postgres SQL so I'll start with my select statement but before that let me show you how to write a comment in postcase SQL so you can write a comment by giving a Double Dash comments are very helpful because they make your codes or the scripts readable I'll write case expression in postgres SQL similarly if you want you can go to the top and let's say here you can write with Double Dash having clause okay let's come down so I'll write my select statement as select I want the department the country and the salary column I'll give a comma and I'll start with my case statement I'll write case when my salary is greater than 45 000. and my salary is less than 55 000 then the result would be within single quotes I'll write low salary so this is exactly like an if else condition next I'll write another case when salary is greater than 55 000 and salary is less than let's say eighty thousand then the result would be medium salary and finally I'll give my last condition that is when salary is greater than 80 000. then the result will be High salary let me write this in a single line then High salary now one thing to remember in postgres SQL the codes are insensitive so you can write your select statement in capital in lower case or in sentence case similarly I can write case as small C or you can write as capital c all right now moving ahead after this I am going to write end I'll give an alias name as salary range now this is going to be my new column in the output let me just come down after this we need to give our table name from employees I'll order it by salary descending okay so what I'm going to do here is I'll first select Department country and salary column from my employees table and then I am creating a new column that is salary range and I'm specifying the range so I have three conditions here for low salary for medium salary and high salary so let's run this and see the output there you go here you can see we have added a new column known as salary range and we have ordered our salary in descending order so all the highest salaries appear at the Top If I just scroll down you can see we have medium salaries here and if I scroll down further you can see these low salaries so case statements are really useful when you want to create a new column based on some conditions in the existing table all right now moving ahead we are now going to see how to write sub queries in postgres SQL so sub queries we write a query inside another query which is also known as nested query so suppose we want to find the employee name Department country and salary of those employees whose salary is greater than the average salary so in such cases you can use sub queries now let me show you how to write a query inside another query first I'll write the select statement I am going to select the employee name comma I want the department comma I also want to display the country name and the salary from the employees table where my salary should be greater than the average salary so after this bear salary greater than I am going to use brackets and write my sub query that is Select average salary from [Music] employees now let me break it down for you so first we are going to select the average salary from the employees so this particular SQL statement will find the average salary from the table we will compare this average salary with salaries of all the employees so whichever employee has the salary greater than the average salary will display the names the department country and their original salary so if you want you can run this statement as well let me select this statement and run it for you can see we have written the average salary of all the employees which is nearly 81 466 dollars so we want the salaries of the employees to be greater than this average value so let me run this and see how many employees have a salary greater than the average salary there you go so we have around 75 employees whose average salary or whose salary is greater than the average salary all right now moving ahead this time I'm going to tell you how to use some inbuilt functions we'll learn some inbuilt mathematical functions and stream functions that are available in postgres SQL so I'll just give a comment there's another way to write a comment instead of a Double Dash you can use the forward slash and asterisks and inside the asterisks you can write let's say SQL functions and you need to close this so I'll give another asterisk and a forward slash so this is also a comment in postgres SQL all right so first of all we'll explore a few math functions so there is a function called ABS which is used to find the absolute of a value so if I write select abs of let's say minus 100 it is going to return me positive 100 or just 100 because as you know the absolute of any value will remove the negative sign involved in that value there you go so our original input was minus 100 the absolute of minus 100 is Plus 100. next let's see another function that is called greatest so the greatest function in postgres SQL will return the greatest number in a range of numbers so suppose I write select greatest inside the greatest function I'll pass in a few numbers let's say 2 I'm just randomly passing a few numbers let's say 4 90 let's say 56.5 and let's say 70. we'll give a semicolon let me run this you will see the greatest function will return the greatest integer value or greatest number that is present in the range of numbers that we have provided so in this case 90 was the largest number of the greatest numbers so we got the result as 90. again you can use an alias for each of these statements Mouse like greatest we also have a function called list which is going to return the least number present in a range of numbers but on this so the result is 2 because 2 is the least number that is present in this selection all right now there's a function called mod which is going to return the remainder of a division so suppose I write select mod and this takes two parameters let's say 54 divided by 10 as you can guess the remainder is 4 and so is our result you can see it has written the remainder 54 divided by 10 the remainder is 4. all right so scroll down now let's see how to use the power function so I'll write select power let's say I want to know power 2 comma 3. which is 2 Cube that is 8. let me just run this there you go so the result is 8 you can also check let's say power of 5 comma 3 it should be 125 all right foreign Ty function that is available in postgresql to find the square root of a number I'll write sqrt and let's say I want to find the square root of 100. you can guess the result the output should be 10. if I run this you can see the output here 10 let's say I want to find the square root of let's say 144 you can again guess the result it should be 12 let's verify it okay there is some error let me verify it again there you go it is 12. now there are a few trigonometric functions as well you can use the sine function the COS function and the tan function let's say I want to know the sign of 0 if you have studied High School mathematics you would know the sine of 0 is 0 you can see the result it is 0 let's say you want to know sine 90 if I run it you can see the output here 0.89 all right now there are other functions like ceiling and floor that you can use so let me show you what the ceiling and floor function does I'll write ceiling let's say I'll pass my floating value as 6.45 and let me run it you can see the ceiling function Returns the next highest integer that is 7 in this case since the next highest integer after 6.45 is 7 let's see what the floor function does and let me run it as you can see the floor function Returns the next lowest integer that is 6 in this case or the nearest lowest integer to any provided decimal value okay now that we saw how to use mathematical functions there are a few string functions available in postgres SQL so let's explore them as well I'll write string functions okay we'll scroll down cool there's a function called character length that gives you the length of a text string suppose I write select give the function as character length and inside this function I am going to pass in a text let's say India is a democracy this is my text let me run this okay you can see the result here which is 20 cents there are 20 characters in my string that I have provided all right now there's another function called concat in postgres SQL so concat is basically used to merge or combine multiple strings so I'll write select concat within brackets I'll give the text string now let's see I want to combine postgres SQL [Music] I'll give a space comma I want to merge postgres SQL is I'll give another comma and write my final word that is interesting now what we have done is inside the concat function we have passed in separate strings and now using the concat function we want to merge the three strings let's see what the result is I'll run it all right let me just expand this you can see here we have concatenated the three strings successfully so the output is postgres SQL is interesting okay now there are functions like left right and mid in postgres SQL so what the left function does is it will extract the number of characters that you specify from the left of a string let's see I'll write select left and I'll pass in my text string as India is a democracy I'll copy this and I'll paste it here let's see I want to extract the first five characters from my string so I'll give five so what it will do is it will count five characters from left so one two three four and five if I run this it should ideally print India for me there you go it has printed India for us all right similarly you can use the right function to extract few characters from the right of a string let's say you want to extract let's say I'll give 12 characters from right so from here onwards it will count 12 characters I'll change left to right now let me select this and run it so you can see here this is the output from the right it has counted 12 characters and returned a democracy okay now there is a function called repeat so the repeat function is going to repeat a particular string the number of times you specify let's say I want to select and use my repeat function and inside the repeat function I am going to pass in let's say India and I want India to be displayed five times I'll give a semicolon and run it in the output you can see India has been printed five times okay let's scroll down there is another function a string function in postgresql called as reverse so what reverse function is going to do is it is going to print any string passed as an input in reverse order so if I write select reverse and inside the reverse function I'll pass in my string that is India is a democracy I'm going to use the same string I'll copy this and I'll paste it here close the codes and the brackets let's print this you can see it here India is a democracy has been printed in reverse order there you go all right now this time we explored a few inbuilt functions that are already present in postgres SQL now postgres SQL also has the feature where you can write your own user defined functions so now we will learn how to write a function of Our Own in postgres SQL so let's create a function to count the total number of email IDs that are present in our employees table so for this we'll write a function a user defined function so let me give my comment as user defined function okay so let me start by first writing create so this is the syntax to write a function in postgres SQL so I'll write create or replace function then I'll give my function name as count emails and as you know functions have brackets then I'll write Returns the return type as integer then an alias with dollar symbol I'll write total emails since I am going to display the total number of email IDs that are present in my table I'll close the dollar symbol then I'm going to declare a variable the variable name is going to be total underscore emails this is of type integer I'll write begin and inside begin I'll write my select statement so I'll write select I want to count the email IDs that are present so I'll pass my column name that is email into total emails from my table name that is employees I'll give a semicolon and then we'll write return total emails as you know user defined functions often return a value so hence we have mentioned the returns treatment as well and now I am going to add my function then the next syntax would be let me just scroll down okay so here I'll give my dollar symbol again followed by total underscore emails next I'll write my language postgres SQL so the way to mention is PL p g SQL let's give a semicolon and end it so this is my user defined function that I have written so I created a function with the function name count underscore emails and this would return integer as an alias which is total underscore emails we declared that variable as an integer then we started with a begin statement that has my select statement where I am selecting the count of email IDs that are present in the employees table and I am putting the value into total underscore email so I have used the into keyword and this Returns the result as total underscore emails and I have ended let's run this okay there is some problem there is an typo so this should be integer okay let me run it once again there you go so you've successfully created a user defined function now the final step is to call that function now to call this function I'm going to use my select statement and the function name that is Count underscore emails I'll give a semicolon let's execute this there you go so here you can see there are 134 email IDs present in our employees table now one thing to Mark is there are total 150 employees in the table but out of them 134 employees have email IDs the rest of them don't have so they would ideally have null values all right so that brings us to the end of this demo session on postgres SQL tutorial let me go to the top we have explored a lot so we started with checking the version of postgres SQL then we saw how to perform basic mathematical operation that is to add subtract multiply then we saw how to create a table that was movies we inserted a few records to our movies table then we used our select Clause we updated a few values then we deleted one row of information then we learned how to use the where clause we learned how to use the between operator we also learned how to use the in operator let me scroll down we created a table called employees and then we learned how the distinct keyword works we also learned how to use is null with where Clause we learned about the order by clause we saw how to alter or rename a column then we explored a few more examples on where Clause we learned about and Anor operator then we learned how to use limit and offset as well as the fetch operator or the fetch keyword in postgres SQL moving further we learned about the like operator in SQL which was used to perform pattern recognition or pattern matching you can say here we saw how to use basic inbuilt postgres SQL functions like sum average minimum count maximum next we saw how to update a value in a column using postgres SQL update command we learned how to use Group by then we learned how to use having clause then we learned how to use case expressions in postgres SQL so we saw how case expression is similar to our if else in any other programming language we explored a few mathematical and string functions and finally we wrote Our Own user defined function so that brings us to the end of this tutorial on postgres SQL now if you want to get this SQL file that we have used in the demo you can give your email IDs in the comment section and our team will share this SQL file with you over email in this session we will learn who is the SQL Developer and what are the responsibilities in a company then we will focus on the skills required to become a successful SQL Developer we'll see the expected average salary in both us and India we will also discuss how to become a SQL Developer and look at the top companies hiring for SQL developers so hey everyone I am abisa rahuja from Simply learn and welcome to this video on how to become a SQL Developer but before we begin if you love watching Tech videos subscribe to our Channel and hit the Bell icon to now miss an update when going through some of the popular job boards online you would easily see SQL Developer is one of the most demanding jobs seeked by companies as we all know today companies collect vast volumes of data and store it in their own databases they want professionals with a good understanding of data who can keep it safe and secure so first we will understand who is a SQL Developer SQL Developer is a person who is accountable in creating and managing huge data assets of a company in other terms SQL Developer is a person who can develop SQL databases as well as write-in test code let's understand this with the help of an example let's say we want to search a particular product on Amazon website and Amazon has stored all its data about products in a database now we will write the product name in the search bar and when we click on the search button it will run a SQL query that fetches all the information about the product and displays us on the website so this is the basic task done by a SQL Developer now we will understand the responsibilities of our SQL Developer all databases have a structure and logic behind how data is stored and retrieved a SQL Developer designs the database accordingly for businesses that is called database smoothly after the database is created and deployed it's the SQL Developer who is responsible for fixing the general issues of the database SQL Developer should be well versed with structured query language to create optimized SQL queries and refines the existing ones to extract information from the database SQL developers run several diagnostic tests to keep a check on the server and the database in order to understand how to organize company's data SQL developers must communicate with tactical and non-technical persons from the business and SQL Developer also gathers client requirements and identifies the features that the database owners want as data is an asset to a company SQL developers backup and restore data for the clients they also perform tasks like data management and data migration that is if a company has stored all its data on a local storage and now it pawn its data to be stored in a cloud storage so the safe transmission of the data from local storage to cloud storage is known as data migration and that is done by the SQL developers after knowing the responsibilities now we should look for skill set required to be a good SQL Developer one should have a good knowledge of SQL commands functions joints and procedures let's have a look at few commands such as create drop alter and truncate these commands come under data definition language we also have insert update and delete commands these commands come under data manipulation language we have Grant and before commands these commands come under data control language we also have Commit rollback and save point commands these come under transaction control language these are the basic commands a SQL Developer should be familiar with and a SQL Developer may have a little command on any one programming language you don't have to be expert in programming all you need is how to interface between application and your database for example you are using MySQL and your web application is based on PHP then you should know how to connect your application to the database and how to issue queries how to fetch results and then how to display it to the users one should have a good understanding of various database Management systems such as MySQL Microsoft Access Oracle postgresql dbase Fox Pro sqlite IBM db2 LibreOffice base mariadb and Microsoft SQL Server one should know integration of databases with data visualization software such as power bi and Tableau which helps businesses to make better decisions and give you an add-on as a SQL Developer one should develop critical thinking and problem solving skills to create optimized queries now we'll see how a SQL Developer is compensated in both United States and India in the United States SQL developers draw an average salary of dollar 72 282 per annum in India the average salary of a SQL Developer is rupees 4 lakh 49532. now we'll see how person can become a good SQL Developer for that one should have a good command on structured query language and know how to interact with database management system and can issue queries to get the desired result earning industry recognized SQL Developer certifications such as Microsoft certified professional developer mcpd or Oracle PL SQL Developer certified associate which usually require a passing score on an exam and it's important to accumulate as much practical experience as possible start by designing creating and querying small databases connected with your hobbies and interests for example your favorite music videos or your personal finances this will enable you to gain experience test your knowledge and build your project portfolio which will be highly beneficial when you start applying for your first full-time position now we will see some major companies recruiting SQL developers first we have the multinational Financial Services Corporation American Express followed by the multinational Professional Services Network ernstein young we also have the messaging company WhatsApp then we have the e-commerce giant Amazon as well as the technology and Computer Solutions company Dell finally in a list of companies we have the multinational technology company Google SQL is a very important technology that is used in the IT industry professionals working as application developers web developers data analysts data scientists software testers and cyber Security Experts need to know SQL in this video we will cover 30 important SQL questions that are most frequently Asked in the interviews now these questions consist of a set of theoretical and practical questions so we will use different commands functions joins and other functionalities in Excel to solve the problems so let's get started with the first question so the first question is to State the difference between where and having clause in SQL now wherein having a very widely used in SQL to solve specific problems now let's look at the difference one by one so where Clause is used to filter the records from the table based on the specified condition and can be used without the group by clause on the other hand having Clause is used to filter the records from groups based on specified conditions now where Clauses cannot have aggregate functions so you cannot use some count average Min and maximum functions in the where clause while in the having Clause you can operate on aggregate functions and those aggregate functions are used to filter values from a group now where Clause is implemented on rows or at row level so for each row of data you have the WHERE condition applied now having Clauses are implemented on columns so for each column of data you have the having condition applied now another important difference between where and having is that the where Clause is executed before the execution of the group by clause and after the execution of the from clause on the other hand the having Clause is executed after groups are created now we will learn more about where and having and understand the usage better when we tackle practical problems in this video now moving to our next question which is how is drop different from truncate so drop and truncate are two different commands so drop command is used to drop the whole table removing the table definition and its contents while trunkey deletes all the rows from the table now we can drop the whole table structure in one go so the view of the table does not exist by using the truncate command the existence of all the rows in the table is lost but the view in the table exists now Integrity constraints will be removed in drop command while the Integrity constraints will not be removed when you use the truncate command now moving to our third question now this is a practical question so we will be using a table which is present in my mySQL database so the table name is called employees and I want to find the lowest salary of the employees in each department you can see here this is how my employees table looks and the output query would be something like this so here since I want to find the lowest salary of the employees so I'm using a Min function to find the lowest salary in the table and then I am grouping all the employees and the results by department so let's do this on MySQL workbench okay so here I am on my MySQL workbench first let me go ahead and create a new SQL script so I'll click on this and that will create a new script okay so here we'll write our commands all right so our question was to find the lowest salary for each department okay so I have given my comment to make my commands and whatever I am doing more readable so first before I proceed I need to select my database so I'm going to use my SQL underscore intro database so I'll write use SQL underscore intro now this database has a lot of tables that we'll be using in this interview questions video I'll run it okay we are in our SQL intro database now there is a table called employees let me show you the table first if I write select star from employees okay so here you can see this is the table I'm talking about now these are the rows that are present in the table we have 20 rows and here you can see the different columns we have the employee ID employee name e gender we have the department City and salary now there's the same table that we have also used in our other videos that are already Live on YouTube okay so let's solve our question to find the lowest salary for each department now for that I'll write select I'll select my department column which is Dept comma and then to calculate the lowest salary or to find the lowest salary I'm going to use the Min function so that will return the minimum salary present in the salary column so I'll write Min salary as I'm giving an alias name as let's say lowest underscore salary from my table name that is employees then I'll write Group by Department now I am grouping it by Department because we want to find the lowest salary of the employees in each department now let's run and see the result there you go I'll just move it to the top so that you can see the results clearly okay so here we have total seven departments you can see at the bottom and in these seven departments you can see in sales 70 000 let's say dollars was the lowest salary in marketing we had 55 000 as the lowest salary similarly if I scroll down we have other departments like Tech it finance and HR so these are the lowest salary in each department all right now moving to our next question so our fourth question is which query will help you fetch unique values from a column in a table so this is a multiple choice question which is another type of question that is often asked in the SQL interviews now we have multiple queries here and you need to choose the right query that will solve our problem so since I want to get the unique values from a column in a table so the right option would be to use the distinct keyword in the query so if I write select distinct followed by the column name let's say you want to find the distinct cities or the distinct departments in the employees table so the query would be select distinct City or let's say Department from the table name that is employees hence this is the right option if you want to find any unique values from a column now moving to the fifth question the fifth question says it is actually a similar question here we want to write the SQL query to fetch unique departments from the employees table so as we saw this is our employees table and if you want to find the unique departments from the employees table you can just write select distinct Then followed by the column name which is departments here so Dept from employees let's do this on our MySQL workbench okay so let me just scroll down and I'll give a comment here that is we want to find the unique departments so the query would be select then I'll use the distinct keyword if I hit tab it will auto complete then I have my column that is Department from my table name that is employees if I run this the query will give us the unique departments that are present in the table so here we have total seven unique departments in the table you can see here we have sales marketing product Tech ID finance and HR but if you see our original table which is the employees table so there are total 20 records and if you see the department column there are multiple departments to which each of these employees belong to okay now moving ahead now the sixth question is to write an SQL query to fetch the unique values of departments and print their length now this question consists of two parts first we need to fetch the unique values of departments that we saw in our fifth question and then we want to print the length of each of the Departments so for that I am using the distant keyword to find the unit departments and then I'm using the inbuilt length function that is present in MySQL to find the length of the department and then I am using an alias that is length underscore Department from employees so let's say this on our workbench okay so let me give a comment I'll say unique departments and length okay so the query would be I'll write select distinct Department and then let me just scroll down and give a comma and use my inbuilt length function see I am hitting tab to autocomplete and inside the length function I'll give my column name that is Department I'll give an alias as Department underscore length from my table that is employees this is pretty simple so first I am finding the unique departments and then using the length function I am finding the length of each department let's run and see okay you can see the result here so sales the length is 5 marketing the length of marketing is nine so basically you're counting the number of characters that are present in each of the Departments so in sales there are total five characters in marketing there are total nine characters in product there are total seven characters in it we have just two letters i and T So hence the length is 2 similarly finances 7 and HR is two because we have just two letters H and R okay so this is another variation of using distinct along with any inbuilt function all right now moving to the seventh question the question is what is the use of DT function in SQL now dative Returns the number of days between two date or date time or time stamp values so D diff is another crucial inbuilt function that is present in MySQL so here is how you can use the DD function so it will return the number of days between two dates or date time or timestamp values so here I have two date values that is 10th of April 2021 and I have 30th of March 2021 so this query will give us the total number of D's that lie between these two dates and similarly here is another example of using the DD function so I want to calculate the total number of days that are present between today's date so I am using the Now function and between 20th of April 2021 so let's do this demo on the workbench all right first I'll give a comment saying d d function okay now the way to use this I'll write select and then you can see here once I type date MySQL workbench is automatically suggesting some of the inbuilt functions I am going to use date div and within D div I am going to pass in two values so I'm passing into date values let's say 2021 April so I'll write 0 4 and let's say 10th of April I'll give another comma and I'll pass in my second date value that is going to be 30th of March 2021 all right now another thing to note here is the date value should be present inside quotations if you want you can given Alias as well I'll write as total days let's run it there you go it has given us the output as 10 so there are total 10 D's between 31st of March to 10th of April now let's see another example I'll write select date div and we'll pass in my Now function so the Now function Returns the current date that is today's date and I'll give another date value let's say 2021 April 20th now we'll see the results if you want you can give an alias name so that the output is more readable here I'll run it okay so between today's date and my previous team that was 20th of April there are total 49 days so today's date is actually 8th of June so between 20th of April and 8th of June there are total 49 days all right now moving ahead now in the eighth question we want to write an SQL query to display the Departments that have more than two employees so this is our employees table that we have been using and here is how the command looks like so here we are using both Group by and having now I am grouping by Department because I want to display the Departments that have more than two employees now for the second filter I am using the count function of the employee IDs and checking if the employees in each department are more than two or not let's do this let me move down a bit and I'll give a comment departments with more than two employees okay now let's write our query so I want to select the department and then I'm using the count function to count the employee IDs in each department or the employees present in each department from my table that is employees going to group by Department and then I'll use having my count of employee ID this should be greater than 2. so our query will return only those departments which have more than two employees let's run and see there you go so we have total four departments that is sales product Tech and it where we have more than two employees you can see here sales has four employees product has three Tech has four and it has three the rest are the Departments like Finance HR and others have less than two employees now if you want you can give an alias as well let's see as total employees okay let me run it again and here you can see I have the column name as total employees okay so moving ahead the ninth question we want to display the details of the employees for all the Departments except marketing now here I have my employees table now if I were to display all the details of the employees apart from marketing department I can give a condition like where Department not equal to marketing so this is the way how you write not equal to so you use the less than and greater than symbol another way to do is use the exclamation mark and give equal to so this means select all the employees where department is not equal to marketing now let's do this I'll move down a bit and will give a comment as details of employees apart from marketing so the query would be select star from employees I'm using star because I want to display all the details that means I want to display all the columns then I am going to use my where condition where Department not equal to marketing I'll put marketing under quotes let's run it and see the results there you go so we have total 18 rows of information and if you see the department column we have all the Departments apart from marketing now if you remember our employees table had total 20 rows of information out of which two are missing which means there were two employees who were from marketing department and those departments were not displaying now another way to do is to use exclamation mark and equal to so this also means select all the employees where department is not equal to marketing let's run this you see here it gives us the same result and has returned us 18 rows of information leaving apart the employees from the marketing department all right now moving ahead so our 10th question is to write an SQL query to print the details of the employees who have joined before April 2010 and after May 2005. so here is my employees table and this is my SQL query so here we are using just a where condition to filter our results and my condition is the employee should have joined after May 2005 so I have written after 31st of May 2005 and before April 2010 which is before 31st of March 2010 let's do this I'll give a comment here we'll write employees joined before April 2010 [Music] and after May 2005. now let's write our SQL query so I'll write select star from employees where my date of join if you see here I have a column called date of join which has the details about the day on which the employee had joined the company so I'm going to write where date of join is greater than 31st of May 2005 so I'll write 2005 this is one of the formats to use date values so this is the year value this is the month value and this is the D value and my date of join should be less than 31st of March 2010 so I'll write 2010 March 31st I hope you were able to understand this date of join greater than 31st of May 2005 means after May 2005 and date of joined less than 31st of March 2010 this means employees joined before 2010 April let's run it okay so we have total three employees in our table you can see the date of join and all of them have joined after May 2005 and before April 2010. okay now moving ahead to the 11th question so here we want to find the employee with the third highest salary from the table so I have my employee staple here and my command would look something like this so here we are going to explore how to write sub queries in SQL so I'm using a sub query here where I am trying to find the employees who have the top three salaries or the top three employees who have the highest salaries and from that I am going to filter out my third highest salary employee so let's do it on our workbench okay so I'll come down and let me give a comment as third highest salary okay so let me first write down my SQL query and then I'll explain so I'll write select star from after that I'll start a bracket and write my inner query so I'll write select star from employees order by salary descending then I'll use my limit keyword and I'll write limit three as I'll give an alias name let's say t and then I am again going to use my order by salary limit one okay now let me explain you the flow so first let me run the inner query I'll select my inner query and I'll run it so the inner query returns my top three employees who have the highest salary and this is ordered in descending order since I used salary descending now let me just use this okay now you can see it clearly so these are my top three employees who have the highest salary so Joseph has the maximum salary followed by Angela and then we have Jack now from this I want to return only the record for Jack because Jack is the employee who earns the third highest salary so for that I am going to use the result written by my sub query or the inner query and on top of this I am going to order the salary and say limit equal to 1 so I'm again ordering this salary in ascending order so what will happen Jack will appear at the top Angela will be at the second place and Joseph will come to the third place and from there I am using limit one so that I can only get the record for Jack now if I run the entire query you see the result I have my employee name as Jack who owns the third highest salary okay now moving ahead to the 12th question the question is to print all the alternate records in a table so here is my employees table and from this table I am going to print all the alternate records for example I am going to print the my employee id101 then I'm going to skip 102 and print 103 followed by one zero five one zero 7109 so all the employee IDs that have an odd value and you can look at this example in another way of displaying the employees which have an employee ID of even numbers so 102 104 106 and so on now the way to do is either you can use a simple select clause and say employee ID percentage or modulus 2 equal to 0 which means I want to filter only those employees which have an even employee ID else what you can do is you can use a CTE as you can see here so CTE or the Common Table expression is a temporary named result set that you can reference within a select insert update or delete statement now it is used to simplify complex joints and sub queries and to provide a means to query hierarchical data now let's write this query on our workbench and see how it works but before that let me explain you I am using a few new functions such as row number this will create a row number for each of the rows or the records present in my table and then from this CTE I am going to use my filter clause or the where clause where RN which is row number percentage 2 equal to equal to 1 so this will return only the odd records from the table let's see how to do it okay so I'll give a comment print alternate records okay now I'll start with my simple query which is Select star from employees where let's say I want to find only the even employee IDs or the employees who have an even employee ID so I can use employee ID percentage 2 equal to 0 so it has to be a perfect division and the result should be 0 only then I can get only the even employees if I run this okay there was an error here it should be employees I'll run it again you can see here I have printed the alternate records in the table so we have skipped one zero one because it is not divisible by two I've started with 1 0 2 followed by one zero four one zero six one zero eight and then finally we have one two zero or 120 now if you want to print only the odd records in the table you can use employee ID percentage 2 equal to 1 so you can see here I have 101 103 105 107 up to one one nine now the best way to do this is to use a Common Table expression or CTE so I'll start with my command that is with CTE as I'll start with my bracket I'll write select star comma then I'm going to use my row number function so I'll write row number then I'll write over then I'll use order by employee ID so this row number for each of the rows is going to be created over my employee ID column and that is sorted in ascending order I'll give an alias as RN which stands for row number from my table that is employees I'll scroll down and then I'm going to close my bracket I'll write select let me remove the space here okay I'll add select star from CTE where my row number percentage 2 equal to 1. now let's see this from the top so I have my commentable expression where I am going to create a row number column named as RN and this row number column is based on my employee ID column which is sorted in ascending order and from that I am going to filter only those row number values that are not divisible by 2 or I am going to print only the odd records from the table so let's run it and see the results there you go so here if you Mark I have my row number column created and I am filtering only the odd records in the table so it starts with 1 3 5 7 and goes up to 19. and similarly here you can see the employee IDs the name the E gender and other information now if you were to print the even records in the table the alternate even records you can use RN percentage 2 equal to zero let's see the difference I'll run this there you go so I have my even row number starting from 2 which goes up to 20. okay so this is how you can print alternate records in a table now moving to the next question thank you now in the 13th question we want to write an SQL query to fetch all the duplicate rows in a table now this is another crucial interview question that is often asked in most of the interviews now I have a table which is named as duplicate table and here you can see I have some employee ID name and each and a few records are duplicated for example 101 Sam 40 the first record is duplicated and you can see here it has the same values now if you mark 103 Mary and 28 is not duplicated because here the age is different so this is a different person than compared to the Third name that we have so this is how you can find the duplicate rows in a table so I am going to select my employee ID name and each and then count all the employee IDs the names and each and compare them with whether they are greater than one or not so if they are greater than 1 then I'll see that particular record is duplicated or is present more than one time in the table let's do this implementation on our workbench okay so I'll come down and let me give my comment as duplicate records okay so let me first show you the records that are present in my table so I'll write select star from dup underscore employees I'll run it okay you can see here I have my table that we saw in the slides so employee id101 with name Sam and each 40 is duplicated so we want to find this duplicate record the way to do is I'll write select e underscore ID which is employee ID comma name comma each comma I'm going to count all of them so I'll write count star as duplicate count this is my Alias name from my table that is dup underscore employees I'll say Group by employee ID name and age and then I'm going to use my having Clause saying having count of employee ID is greater than one and count of name should also be greater than 1 and my count of each should also be greater than 1. let's run it and see the results okay there is some error here the name of the table should be employees okay let's run again we have our correct result that is employee id101 with name Sam and each 40 is duplicated so the value is 2. great now moving ahead now the 14th question is a bit tricky wherein we want to display the employees with exactly two A's in their name so you see here I have my employee names and there are certain employees who have just two e's in their names so I want to find those employees only now the way to do is it is a bit tricky to understand I am going to break it down and make you understand on my MySQL workbench so here you can just have a glance I write select star from employees where I am finding the length of my employee name and then subtracting my length wherein I have replaced my employee name that has a with a blank and if this subtraction is equal to 2 then that particular employee has two A's in their names so let me show this first let me first give my comment as employees with two ease okay so first I'm going to write a select statement okay before that I'll show you the records present in my table okay if you see here Shane is one of the employees that has just one e in its name then we have Mary 1A Dwayne has one A but if you see Sarah Sarah has 2A in the name so we want to extract Sarah then Jack has one e again if you see Angela Angela has 2A it doesn't matter if it is we just need to find if the employees have two e's in their name and similarly if I scroll down let me check if we have any other employee with two A's in their name Marcus has one David has one Sophia has one we also have Amelia that has two A's in its name or in her name and we also have Maya okay now let me go ahead and write a SQL query and then I'll explain you what I'm trying to do I'll write select I'm using the length function and in the length function I am going to replace then I'll use another function called upper on top of employee name I'll write e and then replace it with a blank so I'm using quotations to represent blank from employees okay let's run this and see the results you see here it has given us the length of the employee strings after replacing all the E's with a blank so here I'm essentially converting my employee names to upper case and I am changing wherever there is an E I am replacing that a with a blank and then I am finding the length so this trick I'm going to use in my final query so I'll write select star from employees where I'm going to do is I'll bring this to the next line I'll write length of employee name minus then I am going to use my above condition I'll copy this I'll paste it here and this value should be 2. so what I'm doing is I am first finding out the length of my employee name from that I'm subtracting the length after replacing the A's present in the employee name with a blank and this value should be equal to 2 the reason being we are only trying to find the employees who have two A's in the name let's run and see the results okay if you see here we have Sarah Angela Emilia and Maya who have two A's in the name so you have Sarah that has two a Angela has 2A Amelia has two A's and then Maya also has 2A okay now moving ahead now the 15th question is given a string how will you extract four characters starting from the second position so I have my string here which says Michael Ballack or Michael Ballack you must be aware is a popular footballer he's from Germany and also was the captain of Germany so from the string Michael Ballack I am going to extract four characters starting from the second position you can use two inbuilt MySQL functions too do this the first is called substr wherein we pass in the string then we give the position from where we want to extract the characters so here I am going to extract from the second position so hence 2 and then the number of characters that you want to extract which is four the other way is to use the substring function which is again similar to the substr function in the substring function also I am first passing in my input string and then giving my starting position which is 2 and then I want to extract four characters from it let's do it I'll give a comment extract strings so here I'm going to write select first I am going to use substr which is my inbuilt function I'm going to pass in my input string which says Michael Ballack and then I'll give my starting position which is 2 and then the number of characters that is 4. let's run it and see the results you can see here it has given us icha so if you see m is at the first position from the second position onwards I needed four characters so m c h and E these are the four characters starting from the second position similarly let me just copy this and instead of substr I'm going to use the substring function I'll write just ing that becomes substring let's select and run it and this also Returns the same result suppose you wanted to extract from the fourth position you want to extract let's say three characters let's see the results it gives us H A and E if you see here m is the first position I is at the second position C is at the third position H starts from the fourth position so H A and E are the next three characters starting from the fourth position so hence this is the result all right now moving ahead now my 16th question is on how does self join work so self-joined joins a table to itself so the table must contain a column X that acts as the primary key and a different column let's say y that stores values that can be matched up with the values in column X now this might not be really easy to understand you'll get to know once we do this demo so I have a table which is called employee manager so this is my table now let me tell you how to interpret this table we have five different employees starting from employee id1 to employee id5 Gary Gibbs Smith Latham and Jimmy are the different employees we have you have their salaries and now you have the employees reporting to which manager now if you see here Gary's manager ID is three so if you see a table at three we have Smith so Gary is reporting to three or in other words Smith has a employee under him who is Gary similarly Gibbs is reporting to manager id1 or employee id1 which is Gary so Gibbs manager is Gary similarly if you see for Smith Smith is reporting to the person with manager id4 or employee id4 we have Latham so Latham has one employee under him who is Smith now lead them in our table has no managers then we have Jimmy who's reporting to the employee ID3 who is Smith okay now I want to use the stable and return the name of the manager for each employee to do that I am going to use my self join so here I am using a self join to join both the tables here both the tables mean I'm using same table twice so I have written select e dot employee ID e dot employee name then we have e dot manager ID then I am using another Alias for managers so m dot employee name as manager underscore name from employee underscore manager as e this joint means cell join employee underscore manager SM on E dot manager ID equal to e dot employee ID so this is my common column that is employee ID manager ID let's do it all right I'll give a comment here saying self join and I'll start with my self join I'll write select let me first display my table I'll write select star from and my table name is EMP underscore manager I'll run it you can see the table here there's the same table that we saw in the slides now I'll write my join query I'll write select e Dot employee ID comma e Dot employee name comma e Dot manager ID comma then I'm using another aliasis M for finding the manager name so m dot employee name as manager name there shouldn't be any space in the Alias name then I'll write from EMP underscore manager as e join EMP underscore manager as M on e Dot manager underscore ID equal to m dot EMP underscore ID let me run this and we'll see the results there you go so Gary reports to the manager ID 3 so from the table 3 is for Smith so Gary's manager Smith Gibbs manager is Gary Smith's manager is letham and Jimmy's manager is Smith okay so this is how you can use self join now moving ahead with her next question so the 17th question is on which of the following is called a virtual table in SQL so again we have a multiple choice question so the answer is view you can see here so view is a virtual table that has rows and columns as they are in a real table in the database now we can create a view by selecting fields from one or more tables present in the database next moving to the 18th question so here we want to write an SQL query to fetch the list of employees with the same salary again I am going to use a self join here wherein I'll join the same employees table and this is how my query would look like so I am selecting distinct employee ID so e dot employee ID e dot employee name e dot salary from employees then I'm joining employs as e on employees E1 where e dot salary equal to e1. salary because I want to fetch the list of employees with the same salary and I'm ensuring that the employee IDs from both the tables are different let's do it I'll give a comment here and write employees with same salary and I'll start with my query I'll write select distinct e Dot employee ID comma I'll write e Dot employee name comma dot salary from employees as e then employees as E1 where e dot salary equal to E1 dot salary and e Dot employee ID is not equal to E1 Dot employee ID I'll give a semicolon and let's print the results you see here we have Amelia Bella Sheen and Sarah who have the same salary so Emilia and Shane have the same salary of 55 000 Bella and Sarah have the same salary of seventy two thousand dollars okay now moving to the 19th question so the question is to write an SQL query to print one row twice in results from a table so I have a table here called employees and I want to print one wrote twice in the results so to solve such kind of problems you need to use the union all operator in SQL so Union all combines the result sets of two or more select statements it does not remove duplicate rows between the various select statements all the rows are returned so let's say I want to print the employee name and department for those employees who are from the HR department now to do this we can use the union all operator you can see it here so I am selecting my employee name the department from my table employees as e that I'm giving my where Clause where Edo Department equal to HR and then I'm using the union all operator then I'm again selecting my employee name Department from employees with another Alias as E1 where E1 dot Department equal to HR so let's do this okay so I'll give my comment as print row twice okay now I'll start with my query I'll write select my employee name comma the department from my table that is employees as e then I'll use where e dot Department equal to HR then I am going to use the union all operator so the union all will combine the result set of two or more select statements then I am going to write select EMP underscore name comma my department from employees as E1 where E1 dot Department equal to HR I'll give a semicolon now let's run it there you go you can see here Marcus and Sophie are from the HR department and we have printed them twice in our results coming to the next question the question is using the num table write a query to add 10 when number is 0 20 the number is 1 else print the number itself so you can see here I have my table called num and it has just one column which is num ID or number ID we have some numbers here and from these numbers I want to add 10 if the number is 0 20 if the number is 1 else I would print the number itself so to answer this question we'll use the case statement in SQL so the case statement is sql's way of handling if then logic the case statement is followed by at least one pair of when and then statements so here you can see I have my case statement I'm writing one case my column name and underscore ID equal to 0 then add Plus 10. if it is 1 then add plus 20 else we'll just return the number ID now let's do this okay I'll give a comment saying using case statements okay first of all let me display the values present in my num table you can see here this is my table and there are total 12 rows of numbers now let's start with our query I'll write select and underscore ID comma then I am going to write my case statement so I'll use the keyboard as case when n underscore ID equal to 0 I'll use then n underscore ID plus 10 I'll write when n underscore ID equal to 1 then and underscore ID plus 20 else I'll just print an ID now I'll end my case statement I'll write end as an alias name let's say num underscore add from my table that is num let's run and see the results okay you can see here my first number was zero so since it was 0 we have added num ID or n ID plus 10 so the result is 10 now wherever the num ID is one for example here we have added 20 so then result or the output becomes 21 similarly here the value was 0 so we have added 10 the output is 10. and you see here again here we had another one so we added one two 20 so the result becomes 21 and for the rest of the numbers for example 3 we had two the values are the same okay now moving ahead so we have a table called num1 and using this table we want to write a query to find the sum of all positive values and the negative values so let's see the table first so this is my table which is num1 you can see here we have a list of integers and there are positive as well as negative integers so for positive values and the negative values we want to find the sum separately so again for this we are using the case statement you can see here I have used two case statements I'm writing select and then I'm using the case to find if the number is greater than 0 which means it's a positive number so I am finding out the sum and then I'm checking case when the integer is less than 0 so it's a negative number and I'm finding the sum for the negative numbers as well let's do this okay so I'll give my comment as add positive and negative numbers okay so let's start with our select query I'll write select I'll use the sum function and inside the sum function I'll use my case statement so I'll write case when num underscore integer okay before that let me just print the values present in my table so I'll write select star from my table name that is num1 okay so you can see here these are the values or the list of integers that we have there are some positive as well as negative integers okay let's continue with our query so here I am going to write case when num underscore integer or int which is my column name if it is greater than 0 then I'll return num underscore int else I'll say 0 and end my case statement I'll give an alias as sum of let's say positive numbers give a comma then I'll use another sum function and inside the sum function we'll use the case statement to find the sum of negative numbers or integers so I'll write num underscore end if it is less than 0 then num underscore int else 0 and I'll end okay and I'll give another Alias name as let's say sum underscore any G which stands for negative POS is for positive integers and then I'll write from my table that is num1 let's run it okay there you go we have our results so we have two columns sum of positive integers and sum of negative integers you can see the results 99 minus 14 respectively okay now moving ahead now in question number 22 we want to list the difference between primary key and foreign key so as you can see here primary key is something that can uniquely identify a record in a table so if you consider a school database a primary key would ideally be the rule number or the registration number of the student because that is you need to all the students in a school now foreign key is the field in the table that is primary key to another table so suppose you have another table called courses to which the students of enroll 2 now if you have a role number or a registration number column in the course table that becomes the foreign key now the other difference is primary key don't accept or cannot accept null values while foreign key can accept null values you can have only one primary key in a table but you can have more than one foreign key in a table now coming to the 23rd question what is the difference between primary key and Union so you can have only one primary key in a table as we discussed in the previous question but you can have more than one unique key in a table a primary key column cannot have null values but Union can accept null values now if you assign a column as a primary key you create a clustered index and similarly if you assign a column with Union you create non-cluster index okay now coming to the 24th question what is a check constant in SQL so check constraints are used while creating tables check constraint helps to limit or restrict the values that can be inserted into a column for example we have a table here let's say dummy one and you want to ensure that every time you insert the age of people or persons in the table you want to make sure that each should be always greater than zero reason being a person cannot have or an animal cannot have or a tree cannot have a negative each similarly you can set a constant for the salary column in a table to ensure that it is always greater than 0 because you cannot earn a negative salary or it cannot be less than zero now we are going to create a dummy table on my MySQL workbench and see how check constraints can be applied okay so let's give our comment as check constraints all right so I am going to create a table called dummy let's say underscore SQL now this table will have a few columns first we have let's say Eid which stands for employee ID the data type is integer and I'm going to assign employee ID as my primary key so I'll write primary key here okay I'll give a comma next we'll have a City column City column is of type VAR care or varying character I'll keep the size as 30. and here I am going to apply my check constraint to this City column I am going to check the city should take only values as Mumbai let's see I'll have my final column that is each ages of type integer and here I am going to check my age should always be greater than 0 so I'll write check each greater than 0. okay so our dummy SQL table has one primary key constraint and we have applied two check constraints on City and each column let's run this okay so we have created our table successfully you can see it here now let's insert a few records and we can see the difference I'll write insert into dummy underscore SQL I'll write values I'll have my employee IDs 101 I am giving my city as Mumbai which is what you want and let's see I'll give my age as 10. now this should perfectly work because my city is Mumbai and my age is greater than zero so I should be able to insert This Record successfully you can see here I have inserted this record to the table I'll write select star from dummy SQL so that you can check let's run it uh there is some mistake I should write from okay let's run it you can see it here we have one record or one row returned now let's see we'll insert another record huh and this time I'm going to give my employee IDs 102 because it has to be unique and this time instead of Mumbai I'm going to give my city as let's say Delhi so I'm not following my check constant where I am supposed to give only Mumbai I am purposely giving Delhi here to see the difference if I run this let's see we'll have ages 40 which is greater than 0 let's run it you see here we get an error saying check constraint dummy underscore SQL underscore check is validated similarly let's see I change this to Mumbai again and instead of 40 let's say I'll give us minus 30. so your each can never be in negative numbers if I run this I get the C mirror my check constant has been violated because each should be greater than 0. okay so this is how you can use check constraints in SQL now coming to the 25th question so given two tables A and B we want to write a query to fetch values in table B that are not present in table a now to answer this question you can use the left join so what left join does is it returns all the rows from the left table and only matching rows from the right table and I have used my condition as a DOT ID which is in the right table should be null let's write this query on the workbench okay so I'll give my comment using left join first of all let me display my tables I'll write select star from a if I run this I have the IDs that are total five IDs in table a similarly I'll copy this and will see the values that are present in table B so again I have five values there are a few non-matching values as well so I want to return those non-matching values that are not present in typically for that I'll write select ID from table B I'll use left join e using my ID column this is another way to give the condition or you can use the on operator then I'll give a DOT ID is null let's run and see the results there you go so 150 275 are not present in table a but are present in table B okay now moving ahead so now we look at the last set of five questions so these questions are based on the popular Northwind sample database provided by Microsoft now there are several tables in this database so we have an order table customers employee products region table and others we'll use our Microsoft SQL Server to solve the five questions so my next question which is based on Microsoft SQL Server the question is using the north wind database find the customers who don't have any orders okay so I am on my Microsoft SQL Server management studio so this is how the interface looks like and here you can see I have a list of databases now to solve the last five questions I am going to use the Northwind database if I expand this I'll click on the plus sign you can see there are a lot of tables that are already present in the database you can see here we have customers employees employee territories order details orders products region suppose suppliers territories and more okay let me just collapse this all right so to write my queries I am going to create a new query file and here you see I am currently on my master database I am going to change this to Northwind database so that we can utilize all the tables that are present so my 26th question was to find the customers who don't have any orders for that I am going to use two tables customers and orders so let me first give a comment saying customers with no orders and now let's go ahead and display the columns that we have and see the records present in the tables customers and orders so first time checking all the columns and the rows present in the customers table I'll execute using this button or you can hit F5 as well okay you can see here there are total 91 customers these are all unique customers you have total 91 unique customer IDs you see here the company name of the customer you have the contact name the contact title address City to which the customers belong to then the region postal code Country phone fax number okay now let me just copy this query and now we'll check for the orders table I'll write select star from orders and let's execute it or run it okay so there are total 830 rows of information so there were total 830 orders or order IDs we have the customer ID now this customer ID is actually the foreign key for the order stable but for the customers table customer ID is the primary key and in the orders table order ID is the primary key we also have the employee ID again this employee ID is the foreign key for this order stable you have the order date we have the required date ship date ship wire which means by which medium the order was shipped we have the fluid charges ship name and ship address as well then you have the City from which the item was shipped the region postal code and the country okay now let me use the distinct keyword I'll write select distant I want to find the distinct customer IDs from my customer's table let's run and see how many unique customer IDs we have so there are total 91 customer IDs or customers now let's check for the orders table let's run it and see the difference now if you see here there are total 89 customer IDs in the orders table which means there are two customers missing from the orders table and that implies those two customers don't have any orders so to get the result I will use two different queries to derive at the same output so I'm going to select my customer ID from my table that is customers I'm going to use where my customer ID and then I'm going to use the not in operator I'm going to use a sub query select customer ID from my orders table close the bracket and I'll give a semicolon now what this query essentially does is it is first going to find all the customer IDs that are present in my orders table and in the outer query we are only going to return the customers that are present in both the tables and we are going to exclude those customers which are not present in the orders table so let's run and see the results we should get two customer IDs there you go so these are the two customer IDs which are present in the customers table and are not present in the orders table meaning that they don't have any orders in their name now there is another way to do it that is to use the not exist operator wherein you can write another sub query to get the same result we'll leave that to you you can try this out using the not exist operator as well coming to the 27th question using the orders table in the Northwind database we want to find all the month and orders so such problems you will face while dealing with sales data so very often sales Executives would try hard to meet their month and targets we want to display all the orders made on the last day of the month so let's solve this question on our Microsoft SQL Server okay so let me first give my comment for the 27th question so the question is to find all the month end order so I'll just write month and orders okay now to get the month end orders we are going to use a built-in EO month function which stands for end of month so let's start with the query I'll write select we are going to select the employee ID comma I also want the order ID and we want the order date from my table that is orders I'll then give my where Clause I'll write where my order date equal to and I'll use my inbuilt function that is end of month I have hit tab to autocomplete and inside this function I'll pass in my order date column then I am going to use the order by Clause I am going to order it by employee ID and the order ID I'll just move down and hit tab okay let's run the query there you go we have got 26 rows of information you see on the left side we have the employee IDs the order IDs and then we have the order date column now if you mark the values present in the order date column these are all the end of month date values you see this is 28th of Feb then we have 31st of July we have 30th of June so on and so forth so these are the list of all the orders that were placed on the last day of the month coming to the 28th question we want to find the top five countries with the highest Freight charges in the year 1997. so freight charge is the amount paid to a carrier company for the transportation of goods from origin to another location the Fret charge is calculated based on the mode of transportation and the distance between the pickup place or the pickup location and the destination so let's try this out on the Microsoft server management Studio okay so let me go ahead and give my comment so since we want to find the top five countries with the highest Freight charges I'll just give my comment as highest Freight charges okay now to solve this question I'll just change this to ght okay we are going to use our order stable so the way to do is I'll write select I'm going to use the top five so top is another keyword or an operator so I'm going to find out the ship country and then I'll calculate the average Freight prices so I'm going to use the average inbuilt function on top of my column that is Freight I'll give an alias as let's say average Freight there should be no space I'll give a underscore I'll write from my table that is orders I'll use my where Clause where and then I'm going to find the ear from my order date column and this year should be 1997. I'm going to group it by my ship country and I'll use order by my Alias name that was average Freight and in descending order I'm going to sort it let's see the top 5 shipping countries who have the highest Freight charges these are the results you have Austria Switzerland Sweden Canada and Ireland and here you can see the average Freight prices sorted in descending order okay now moving ahead to the 29th question so here we want to display the total number of products in each category so we have two different tables products and categories so both these tables we are going to use to find a solution to this problem that is to display the total number of products in each category so first let me give my comment to display the total number of products in each category I'll just write print total products okay so to solve this problem we are going to use our two tables so let me first display the rows and columns from both the tables the first table is products okay it is products let's run this and see the columns that we have in this table okay so you have the product ID which is the primary key you have the product name so there are total 77 different products okay and then you have the supplier ID you have the category ID so these two columns are the foreign key columns you have the quantity per unit unit price units in stock units in order and if you want to reorder some product to fill in the stocks so you have information for that as well and if the product has been discontinued or not I'll copy this and let's see what rows and columns we have in my category stable okay let me execute this okay so we have total eight categories of products you can see beverages we have dairy products meat or poultry you have produced Seafood these are the description [Music] okay now to find the total number of products in each category I'll write my query as select I'll write category name comma count star I'll give this an alias name as total products given underscore and write products from my first table that is products given Alias as p I am going to use an inner join with my categories table and I'll give my Alias as C on my common key column that is p Dot category ID is equal to C Dot category ID then I'm going to group my results based on the category names so I'll write category name and we'll use the order by Clause I'll write order by count star as descending so let's see the total number of products that we have in each category so they shouldn't be as descending should be just count star descending okay I'll select and run it there you go so here on the left hand side you have the different category names and on the right you have the total number of products that belong to each of the categories all right now moving on to the final question so the last question is to use the Northwind database and using this database we want to find the list of late orders for all the employees so this is a real world problem that often occurs so the shipment of your orders might get delayed due to logistic issues or if the products are not available on time there can be various other reasons as well so here we want to find those orders for the employees where the required date is less than the shipment date we are going to use two different tables that is orders and employees okay now let me give my comment for the final query that we are going to write in this interview questions tutorial so using the orders table I want to find the list of orders for all the employees so I'll just give list of late orders not just orders for employees I write EMP s which stands for employees so here I am going to use two tables orders and employees order stable we have already seen let's see the rows and columns that are present in the employee stable I'll write select star from employees and let me run it okay so there are total nine employees we have so we have the employee ID this is the primary key column which goes from one to nine we have the last name of the employee the first name of the employee we have the title then we have the title of courtesy where the doctor Mr Mrs we have the birth day of the employee the higher date or the date of joint you have the address City region postal code and other information all right so let's see how you can join the orders table and the employees table to get the list of late orders and we must remember that late orders are those where the required date is less than equal to the shipped date so I'll start with select e Dot I'll write employee ID comma I want the first name comma I'm going to count Star as late orders from my table orders as o then I'll use my inner join I'll write employees as e it doesn't matter actually if you use small e or capital e then I'm going to use my on condition on E Dot on E Dot employee ID equal to o dot employee ID so this is my common key column that is present in both the tables then I'll use my where Clause where you should be careful and you should keep the right Clause so my required date should be less than or equal to my shift date foreign so this is my condition and then I am going to group by E dot employee ID comma e Dot first name and then I'll use my order by Clause order by my Alias name that was late orders and I'll arrange it in descending order so let's run this and see our result okay so you have the employee IDs on the left then you have the first name of the employee IDs and then you have the late orders for each of these employees so if you see here employee id4 has the highest number of late orders followed by employee ID three and eight as well as 9 who have 5 late orders then we have the employee ID 2 which has four late orders even implied is seven has four lead orders employee ID 5 that is Steven has only one late orders so we are done with our demo section and we have covered all our 30 questions out of which 25 questions were based and used on MySQL server and to address the last five questions we used our Northwind database that had a lot of tables you can see it here and using those tables we solve some interesting questions

Channel: Simplilearn

Views: 44,338

Keywords: simplilearn, sql tutorial, sql tutorial for beginners, sql for beginners, sql full course, sql full course for beginners, sql, mysql, learn sql, mysql full course, simplilearn sql, sql basics for beginners, sql complete course, sql complete tutorial, sql interview questions and answers, sql programming, sql programming for beginners, sql structured query language, sql training, sql tutorial advanced, what is sql, what is sql database

Id: ke0ybT9BGMY

Length: 482min 36sec (28956 seconds)

Published: Wed Feb 08 2023