Transcript for:
SimplyLearn SQL Full Course

hello everyone I hope you're doing good and staying safe welcome to Simply learns YouTube channel and in today's session we'll be taking you through SQL full course as you all know SQL which is the language of databases is considered as one of the oldest languages in technology domain and is highly significant today as companies across the world are gathering massive amounts of data for their growth SQL is the most commonly used language to communicate with databases and extract data for application development and data analytics it consistently ranked high in the most requested Tech skills and learning it will add Great Value to your array of skills SQL is a powerful and dependable tool for extracting relevant and useful data from large data sets while SQL has traditionally been the domain of highly trained data analysts and programmers it is gaining a lot of popularity nowadays among non-technical professionals as well also looking at the job opportunities we can see an enormous demand for SQL developers across the globe right now from Finance to take companies Consulting to e-commerce companies it is quite evident that SQL is the most in demand skill right now so in today's session we're going to learn about SQL Basics and help you understand various aspects of structured query language if you're a beginner who is on the pursuit of learning SQL language this full course on SQL will help you learn all about the language and its various Concepts from the scratch with theoretical knowledge as well as practical examples but before we begin if you a tech G and love watching Tech videos consider getting subscribed to our Channel simply learn to stay updated with all the latest Technologies and hit that Bell icon to never miss an update from us so without any further Ado let's get started firstly let us discuss the agenda for today's full course tutorial we'll start this tutorial with a quick introduction to SQL and dbms Concepts wherein we'll discuss what is SQL and why we use SQL and we'll also look some applications of SQL language and then we'll see how to install MySQL workbench on your system followed by that we'll understand different types of SQL commands and followed by that we'll discuss some of the major Concepts in SQL like operators and expressions as well as Clauses and see their syntaxes and how they are executed in SQL with practical examples in MySQL workbench next up we'll discuss what are joints in SQL and take a quick look at different types of SQL joints with examples as well as well up next we'll discuss what are keys in SQL and we'll go through different types of SQL Keys as well once this are done we'll have a quick understanding of what are aggregate functions and why we use them in SQL next we'll discuss what is SQL and how it is different from nosql language see how they are different and we'll compare each and every criteria between them and finally we'll understand what are subqueries in SQL so without any further let us jump into today's topic what is a database data in today's world plays a vital role in our everyday lives be your work related data in your laptop your bank account details or even your Gmail password so it's necessary to store this data safely and in an organized way that's where database comes into picture which holds and manages the data a database can be defined as a collection of information or processed data that can be stored and accessed whenever needed for example we can use a database to store the complete details of an employee working in an organization the primary goal of a database is to store and retrieve information efficiently popular databases some popular open- source and commercially available databases are MySQL Oracle database Microsoft exale server mongod DB and post grp SQL why we need a database now that we've understood what a database is let's understand the use of database and the need for it database came into existence in the early 1970s but all the data was stored in computer files before that as the technology kept advancing it became difficult for computers to handle when the number of files increased and the volume of data grew the so-called traditional file system was no longer able to store and ret data efficiently let us now see some of the limitations and drawbacks caused by traditional file system data redundancy and inconsistency redundancy means when the same data is getting duplicated and repeated in different locations this leads to excess and unwanted storage which eventually results in inconsistency where the data in separate files do not match with each other data isolation data is isolated because uh it is scattered in various files in different locations and the files may be in different formats as well at any point writing new application programs to retrieve the appropriate data becomes difficult as the files are separated from each other limited data access file storage systems usually do not have access from multiple users this means multiple users at different places cannot access the data simultaneously it becomes difficult to access important data if multiple users are searching at the same same time security and integrity issues data stored in files can easily be accessed and tampered so it's essential to prevent unauthorized users access to hold the data's Integrity in order to eliminate all these drawbacks we use a database that is controlled by a database management system let us now look at the history of SQL SQL was developed by IBM in the year 1970 Dr Cod boys and Donal champ proposed a paper on usage of relational database management they came up with SQL which can be used to perform operations on data stored in the databases SQL was made publicly available and was accepted for the use of relational database in the year 1974 initially the language was known as Structured English query language pronounced as SQL which was later changed into SQL in the year 1978 The American National Standard in Institute ANSI and other International organizations have standardized SQL as a language for database communication in the 1986 though companies use a different version of SQL nowadays the latest version of it was released in 2019 by Microsoft what is SQL SQL is defined as a structured query languages it allows the user to manage and manipulate the database so you might have got a doubt in your head that we've discussed databases earlier and why we are discussing SQL now well you got it right both database and SQL are interconnected to each other SQL allows you to perform operations like insert update modify and delete in the database in a nutshell SQL is used to communicate with the database systems to retrieve the information features of SQL SQL is one of the most demanding scrip nowadays with the ever increasing amount of data SQL serves as a powerful tool to provide insights to businesses while handling databases skl is used to define the overall schema that is the complete structure of the database by managing and manipulating the data accordingly and retrieving the information whenever required by the user SQL also allows flexibility as it uses simple English words in its queries like create delete Etc SQL can handle large amounts of Records stored in databases with atmost efficiency let us now see how SQL Works a typical SQL database engine includes a storage engine which is a database server and a query processor within the SQL engine to understand simply let us take an example suppose John is an HR Manager working in an organization he wants the information of all the employees who have joined last last year John writes an SQL query in his laptop to retrieve the data in order to execute the query it must interact with relational database management system within the database server and the request should be a valid query before the SQL engine can process it the SQL engine then writes to and retrieves data from the database server both database server and SQL engine work hand inhand together to process the required data the system process the SQL request and sends it to web server to access the information via SQL database and if the information is found in the tables the database server sends the information back to the user in this way John can retrieve the information from the database using SQL types of SQL commands SQL commands give instructions to database to perform specific actions to retrieve the data SQL command s are broadly classified into four types the first one is data definition language or ddl ddl allows the user to define the table and change its overall structure commands that are used in ddl create it is used to create a new table alter it is used to modify the existing table by adding unique attributes drop it is used to delete the whole table and the data stored in it truncate it is used to delete the rows in a table the next one is data manipulation language or DML DML is used to access and manipulate data in tables commands that are used in dmlr select it is used to extract data from the tables update it is used to update a value in the existing table delete unlike the drop command the delete command is used to delete a specific Row in the table insert it is used to insert a new value into the table the next one is data control language or DCL DCL is responsible for maintaining the security which gives control access and permissions to the database commands that come under dclr Grant it is used to Grant permission to user to access the database revoke it is used to cancel or take back the earlier granted permission the last one is transaction control language or TCL TCL has three commands namely commit it is used to permanently save the transaction roll back it is used to restore the transaction that is not saved save point it is used to hold a transaction temporarily it can be rolled back to its previous state at any point let us now look at some of the advantages of SQL one of the main advantage of SQL is that it provides access to data stored in database with its highspeed and fast Acy processing quickly and efficiently SQL is open source that means it is publicly available and can be accessed from the internet it is also straightforward to implement as well SQL also provides the user to have multiple views of their content stored in the database SQL is efficient in retrieving vast amount of data using simple queries and also it is portable as well which means you can perform all these operations at your home or your workplace through your laptops and PC disadvantages of SQL there are two sides to every coin and similarly SQL also has few advantages which are not that significant initially one may find it challenging to work with SQL due to its complex interface since it's a platform based languages most of the commercially available SQL servers costs are relatively higher skl is constantly working on these massive amounts of data stored in the databases and hence maintenance costs are also high as well let us now look at some of the applications in real life of SQL SQL is widely used in various sectors nowadays some of them are education schools and universities use SQL and databases to store and retrieve information about their students faculty and staff Healthcare hospitals and other medical centers use SQL to store the details of the patients without any hassle it also helps in maintaining all their documents and bills as well retail and e-commerce with its vast presence in the market retail and e-commerce companies store the customers data to improve their shopping experiences by providing special offers which will in turn help their businesses to grow banking escal is one of the significant components of banking sector as well Banks store the account details of customers and the transactions done on day-to-day basis in the database Finance Finance is another massive area where SQL queries are used regularly in managing the assets Revenue details shares of the companies Etc faster execution and retrival of data are key for all the businesses to make strategies and derive insights from it companies using SQL SQL is used extens ly every day by some big Tech gen companies like Google Microsoft Oracle Amazon Facebook Etc even small companies and startups heavily rely on SQL to make better decisions and provide Solutions and service to customers with that we have come to the end of the session on introduction to SQL by now you must have got some idea of what SQL is over the years SQL has evolved and is widely used used nowadays worldwide it is one of the most efficient database languages out there it can perform various operations on the database to retrieve the data instantly SQL is very simple to understand and easy to learn as well as all the commands and queries are written using English words unlike other programming languages SQL requires almost no coding it does not require thousands of lines of code the syntax is also user friendly and easy to implement so what is dbms dbms comprises of two words the first one is database database can be defined as a collection of data that is organized as well as structured on the other hand we have Management Systems which directly interact with the database to access the data in a nutshell dbms is a software to store and retrieve database information efficiently and conveniently some popular dbms softwares used by various organizations are MySQL Oracle database post GW SQL Etc why we need dbms dbms have come into existence in 1960s and since then it had the edge of a traditional file system which had certain anomalies and drawbacks like data rency inconsistency privacy and security issues and various other factors factors dbms manages the information stored in databases effectively and effective management of data is key to organizations as it ensures the data is available to the users whenever and wherever needed data volumes continue to grow exponentially and dbms are designed to manage large volumes of database efficiently components of dbms dbms can be divided into five major components the first one is data perhaps the most important component of dbms certainly from the end user point of you in the data because the primary reason behind introducing dbms is to store and maintain the data within the database data acts as a bridge between machine components like Hardware software and human components like applications the next one is Hardware the DPMS requires Hardware to run Hardware refers to the external parts of the computer system which includes storage devices like hard disk and other input output devices it is used in managing and accessing the database software another important component of dbms is the software which controls everything and provides us with the interface to store and access the data the software component comprises the dbms software itself and the application programs to execute on the database user access user access or database access allows the user to access the data to and from the database by writing commands SQL is widely used as a database access language to perform operations like creating new tables inserting and deleting values and perform updations that is on the data that is stored in the databases procedures procedure refers to the instructions and rules to use the dbms the user who is managing the database requires documented procedures on how to run the database this includes understanding the design and structure of the database system setup and log to the databases Etc dbms architecture database Management Systems architecture will help us understand the components of the database systems and the relation among them the data is usually complex in nature and developers generally hide unwanted or irrelevant information from the user this is called as data abstraction which reduces the internal complexity to make the system more efficient the database architecture has mainly three levels and heals it is also called as the three level architecture as well the first one is physical schema it is the lowest level of database architecture it is also called the internal level of the database schema internal level LEL is the physical representation of the database that means it describes how the data is stored in the database logical schema it is also called the conceptual or logical level and it is at a higher level than the physical level this level basically represents the community view of the database and describes what data is stored within the database and the relationship among the data external schema this is the highest level in the three-level architecture and closest to the user it is also known as The View level the external level only shows the relevant database content to the users in the form of view and hides the rest of the data there may be a number of external views for database for different users types of dbms architecture there are three different types of dbms architecture the first one is single or one tire architecture two Tire architecture and finally three Tire architecture single Tire architecture in dbms is the simplest architecture of database in this architecture the database is directly available to the user the user can directly access and use the database for example let's imagine you want to get all the employee reords from the database for that you can directly communicate with the database from your computer itself this is why this architecture is also known as local database system two-tire architecture in two-tire architecture the database system is located on the server machine and the dbms application is present on the client level these two are linked via a reliable Network the two Tire dbms architecture is used when we wish to access the dbms with the help of an application three TI architecture this is an extension to two Tire architecture and also the most widely used dbms architecture it is similar to this two Tire architecture only but there is another separate layer known as application server between the database server and the client in this architecture the client application doesn't communicate directly with the database system present at the server machine instead the client application communicates the communicates with the server application and then internally communicates with the database system present at the server data models in dbms data model defines how data is connected to each other and how they are processed and stored inside the system it also defines the logical structure and design of data in dbms data models are broadly classified into four types the first one is hierarchial model it was one of the first dbms models ever used in this model data is organized in tree like structure and connected to each other by Links the next one is Network model it is an extension to hierarchal model it can represent complex data relationships using graph like structure where the data can have many to many relationships the next one is entity relationship model in this model we represent real life entities in a pictorial form using different shapes finally relational model it is one of the most commonly used models it represents the data in the form of tables what is rdbms let us now discuss one of the most popular data models in dbms which is rdbms rdbms starts for relational database management system all the modern dbms like MySQL Oracle Microsoft SQL Server are based on rdbms Only rdbms Stores the data in the form of tables which is basically a collection of related data this data is organized in the form of rows and columns the data that is placed horizontally in table is known as the row and the vertical arrangement of data is known as columns field every table is broken up into smaller entities called Fields fields in the employee table are employee ID employee name job department number and salary rdbms versus dbms let us now understand the difference between rdbms and dbms though both of them are used to store physical data in the databases there are some difference between them as well rdbms stores data in tabular form whereas dbms store data in individual files for an application like XML or Json format Etc rdbms deals with vast amount of data whereas dbms is designed to handle small amounts of data and is meant for small organizations rdbms can support multiple users on the other hand dbms is limited to a single user rdbms also supports distributed databases wherein you can manage and have the access for multiple databases at the same time whereas dbms do not offer the support for distributed databases these are some differences between rdbms and dbms types of keys in dbms Keys play an important role in relational databases they're used to establish a relationship between the table it is used to fetch information from one or more rows in a table in dbms there are several Keys which are almost inter related to each other but we look at some important and most used keys in rdbms the first one is primary key it is one of the main key that uniquely identifies every row in a table Super Key Super Key contains additional or other sets of attributes that can uniquely identify a row within the table candidate key candidate key are selected from the set of super Keys the only difference is it shouldn't have repeated attributes hence it is also called as minimal Super Key foreign key it is used to create a relationship between two tables with the help of an already existing table basically it acts as a cross reference between two tables let us understand this keys with an example let us consider a table called employee which has different fields like employee ID employee name job role department number pan number AAR number Universal identification number which is U now for the employee table we can take employee ID column as a primary key because it uniquely identifies each record in the table the Super Key can be pan number add number or even un because two employees can have the same name and by definition a Super Key is a set of different attributes which can uniquely identify the table and hence uh different employees have different pan number a number and un as well candidate key can be taken from either of these three super Keys which we have taken earlier except for the primary key employee ID other attributes can be candidate keys so I have taken U as the candidate key for this table now each employee Works in various departments and we cannot store the the department name in the employee table that's why we link the already existing table that is the employee table with a new table by taking the department ID as primary key and creating new attribute named Department name so in this case Department ID is considered as foreign key advantages of dbms one of the main advantages of dbms is it controls data redundancy redundancy means means storing the same data multiple times by having centralized database system unnecessary duplication of data is avoided data Integrity integrity means the data in the database is accurate and consistent dbms ensures that the data is correct and consistent for all the users as it handles multiple databases at the same time data privacy and security data privacy is Paramount for every user and dbms allows only authorized users to access the data from the database hence dbms provides improved data security under any circumstance data consistency data inconsistency occurs when the same files are located in different locations but with dbms they can achieve increased data in data consistency because any changes in the database are immediately reflected to the user ease of sharing data dbms allows a user to share the data in any number of application programs users can also have access to the database simultaneously and share the data between themselves backup and Recovery dbms take carees of recovery and backup on its own users are not required to take regular backups because the dbms does it for them it also restores the database after a system failure or crash to prevent it from reverting to its original state disadvantages of dbms well to store a huge amount of data one needs a huge amount of space as well eventually it requires additional hardware and software which are relatively of higher cost and because of its constant functionality the maintenance cost are also high as well complexity dbms is an extremely complex software initially one may find it difficult to op operate different functions on dbms because of its complexity end users must have proper knowledge while handling database systems otherwise it may result in database failure Speed and Performance issues dbms is made to handle extremely huge data and queries and if the resources become Limited at any point and optimization is not done properly the database will become slow and it reduces the performance of the whole system increased vulnerable ility dbms system works on the centralized system that means all the users from all over the world have access to this database hence any failure of this dbms will impact all the users so there is a high chance of losing the data so these were some of the disadvantages of dbms finally let us look at some of the applications of dbms dbms is widely used in various Fields nowadays some of them are banking dbms is used in the banking sector to store the customer information Account Details and all the transactions done on a daily basis additionally to keep track of the loan amounts account balance sheets ATM and deposit records Etc are maintained with the help of database education schools and universities manage their students information like personal details course details exam marks grades Etc ET all this information is stored in databases and is managed by dbms Finance the database management system is used by companies and corporations to store information about Revenue sales Holdings and purchases of financial instruments such as stocks and bonds and it also stores realtime Market data to enable online trading Healthcare hospitals and medical centers you use gbms to store the details of the patients and assist them with their diagnosis and treatment procedures it also helps in maintaining patient medical record history documents previous bills Etc manufacturing manufacturing companies make products and sell them on a daily basis to manage the supply chain and track the production of items and factories and warehouses maintain records of all details of the product like number of orders purchases Bill amounts Etc we use dbms travel dbms is used by Railways and Airlines to store the booking information of passengers departure and arrival timings and finally e-commerce online shopping is the new normal for everyone nowadays e-commerce platforms store and access details of customers purchase information payments and addresses Etc in the database using bbms firstly go to Google and type MySQL you'll find MySQL official website page on the search result click on that once you reach their official website page uh you'll find download section click on that now scroll down a bit and you'll find MySQL Community GPL downloads click on that link now you'll find various options for download such as various uh repositories mys shell mysale workbench Etc but since we are installing it for the first time on our Windows system and we are only concerned with the mysale workbench and shell we'll go with mycale installer for Windows click on that link now you'll find two options for download uh the first one is Windows 86 32bit MSI installer it is showing here 32-bit but it will work for 62- bit systems as well so don't worry and you can choose either of the two and I'm going with the first one click on download now you'll find this page where you'll have uh login and sign up uh for the Oracle account for time being just ignore that and click on no thanks just start my download save it on your system once it's downloaded uh open the file give all the necessary permissions now you'll find a setup page uh where you'll have different options uh such as developer server client full and custom uh we'll choose custom because we want MySQL workbench and MySQL shell so click on next now we have to select the products which we want to install in our system uh you can find MySQL server click on that and expand it you'll find MySQL server 8 .0 click on that you'll find the latest version of it uh click on that and select the arrow and send it to the other site now scroll down a bit and uh you'll find applications click on that you can see MySQL workbench expand it and you'll find the latest version of it as well click on that and click on the arrow and send it to the other side scroll down a bit and you'll find MySQL shell as well click on that expand it select the latest version click the arrow and send it to the other side now we are good to go and uh click on next now it will ask for the path where you want to install all these files uh we are saving this on our C drive uh just check all the necessary paths and click on next now all the three products are ready to download click on execute again uh now depending upon your internet speed this may take take a while so don't worry just sit back and wait for it to get downloaded you can see all the three are successfully downloaded uh click on next now we need to install all the three products click on execute so you can do the installation process simultaneously with me or just take a note of it and perform it later as well on your PCS and laptop uh this might take a while so we'll wait for it to get installed as you can see all the three of them are successfully installed click on next click on next again now you'll find the server configuration type uh you'll find different port number and protocol Port just leave as it is because it is set by default by the system click on next now we'll have the authentication method you'll be provided with two options uh we'll choose the recommended one which is given by the system click on next again now we have to set a root password uh so by the way guys the root is basically the a default user which will have who will have the access to all the files and programs so enter a password of your own choice and make sure you take a note of it because uh we'll have to use it at a later stage and also while logging into the MySQL server you'll use the same password so click on check so as you can see there's a blue tick mark which means it's verified uh so you can go ahead click on next now I'll find the windows service which is the standard system account choose that and click next now you have to apply all the uh configuration for the system files uh for that just click on execute and the system will uh automatically configure itself so this might take a while so we'll wait for it to complete as you can see the all the files are successfully configured uh click on finish click on next so once you uh click on finish uh mycale workbench and mycale shell will automatically launch so workbench and shell started in the background uh as you can see there's a local instant MySQL 80 click on that now it will ask you to enter the password which you have set earlier click on that so that's it guys we have successfully installed MySQL workbench on our system but uh before you get started there's another little process that with that we are left behind with uh now you have to connect all the files and packages to the server before you start working on the tables so for that we need to locate where the where the MySQL files are stored for that we'll go to file manager since we have saved the uh files on local disk D local disk C uh click on C drive go to program files click on MySQL open MySQL server 8.0 click on bin now these are all the files and packages that you have to connect to the server for that uh double click on uh location path copy the whole address and open command prompt on the command prompt you'll have to type CD that is the current directory and uh paste the address that you have copied earlier and click enter now type MySQL space my U root minus P here minus U is the user which is the root user which you have taken minus p is the password click on enter now it will ask you to enter the password that you have set earlier type the same so if you find uh what you're seeing in my system that is Oracle is registered trademark of Oracle cooperation and all this things that means you have successfully downloaded and installed all the files and packages into the MySQL server and you are good to go and start working on your tables right away what is SQL syntax just like other programming languages SQL follows a unique set of rules and guidelines called syntax we use Simple English words in the SQL syntax in order to execute various queries SQL syntax is by default case insensitive that means the system allows the the user to write the queries in both uppercase as well as lowercase but if you're working with mySQL server then you need to give table names exactly as they exist in the database we know that rdbms is the basis for all modern database systems including SQL which manages and performs various operations on the tables like insert update modify and delete so in order to retrieve the data stored in the database it is necessary to learn SQL syntax first before we move ahead with the topic if you want to learn more about the basics of dbms and SQL make sure you check out our previous playlist videos on introduction to dbms and SQL on our Channel we'll leave the link in the description below SQL Expressions SQL expression is a combination of one or more keywords and values operators data types and other SQL functions these SQL expressions are like a formula which are similar to mathematical formulas which we LLY used to solve a problem and in this case they're written in a query language using a proper syntax SQL statement SQL statements are basically collection of SQL expressions for example let us consider an employee table to understand it in a better way the table is having attributes like employee ID name age City and salary now if I want to fetch the record of all the employees and their IDs I'll write a simple SQL query that is Select ID comma name from employee here select is the database object or the keyword that is used ID and name are the columns from employee employee is the name of the table when I execute this query this will be the output it will display the ID and name of the employee let us look at another query now I want to display uh the salary of employees having uh more than 30,000 for that I'm using the query as select ID name from employee where salary is greater than 30,000 here where is the conditional statement that I'm using or it is a SQL clause also we are using greater than symbol which is an SQL operator so with the help of SQL statements we can fetch uh the records of all the information from SQL tables SQL data types SQL data type specifies which type of values is stored in the database table SQL data types are mainly classified into three categories the first one is numeric numerical data refers to the data that is in the form of numbers in numerical data type we have different uh types as well for example int int holds values of integers and whole numbers without any decimal point further they are again divided into small int and big int also the difference between them is the size and range of the value they can store and operate bit bit data type is an integer data type that can only take values of 0o one or any null value SQL optimizes storage of bit columns if a table has eight or fewer bit columns SQL stores them as one byte if a table has nine or up to 16bit columns SQL stores them as two bytes and so on float float stores the numbers having decimal values depending upon the number of digits after the decimal point SQL gives the size accordingly in its range Boolean it is used to specify Boolean values that is true and false zero is considered as false and non-zero values are considered as true string data type string data types allows us to store fixed or variable character values they're again further divided into various types the first one is car car is used to specify a fixed lens string that can contain numbers letters and special characters its size can be 0 to 255 characters Vare Vare is similar to care but it stores variable lens strings and size of Vare is also more than the car data type with a range of 0 to almost 60,000 characters so if you're storing strings with a widely variable lens such as name address email ID then we have to use Vare text text data type stores any kind of text Data it can hold string value that can contain maximum length of 255 characters date time in SQL datetime data type is used for storing values that can contain both date as well as time the first one is date it is used to specify date format that is year month date in this data type we can store only the value of date and the next one is date time it is used to specify the combination of both date and time the format is year month date hours minutes and seconds for instance I've taken the example as 2022 3 that is the month 20 is the date 23 hours 59 is the minutes and 59 is the seconds time stamp it is also similar to daytime data type the format specification is also the same as well the only difference is it has less uh range of values to store it is also used to convert current into various time zones like UTC GMT Etc we also have XML and Json data types which are not that significant and we do not use as frequently as other data types in SQL SQL operators SQL operators are used to specify certain conditions in an SQL statement SQL operators are broadly classified into five categories the first one is arithmetic arithmetic operators perform mathematical operation on numerical data on the SQL tables these operation performs addition subtraction multiplication and division operations on the numerical operant the next one is logical The Logical operators in SQL perform Boolean operations which gives two results either true or false these operators provide True Value if both operant match The Logical condition some logical operators are and not or between Etc comparison the comparison operator is in SQL Compares two different data in the SQL tables and checks whether they are same greater or lesser the SQL comparison operator are used with a conditional Clause where in the SQL queries equal to is highly used uh comparison operator in SQL bitwise bitwise operators perform bit manipulations between two expressions of any integer data type category bitwise operators convert two integer values to Binary bits and perform and or or not operation on each bit which finally gives the required result final one is set the set operators in SQL combine a similar type of data from two or more SQL database tables basically it merges the result which is extracted from two or more SQL queries into a single result SQL Union intercept set and minus operators are some of the examples of set operations SQL commands we know that SQL commands are broadly classified into four types the first one is data definition language ddl ddl allows the user to define the table and make changes to its overall structure commands that are used in DDR create it is used to create a new table alter it is used to modify the existing table by adding new attributes drop it is used to delete the whole table and the data stored in it truncate it is used to delete the rows in a table now that we have got the idea and understanding of various operators data types and commands uh let us look at the syntax of all these commands the first one is SQL create statement perhaps one of the most important and used SQL statement because if you want to create a table you have to first name the table and then specify the columns and the columns data types so let us look at the syntax of create statements the syntax is followed as uh create table which is the uh keyword that is used followed by table name that you want to create and within the parenthesis you have to mention the column give space and add the data type so in this way you can add a number of columns and mention the data types but make sure guys you have to give the appropriate data type for the columns that you have taken because uh there might be a chance uh there might be an instance where you have you have given the column uh such as age and you're mentioning data type as character care uh which is basically a mismatch the computer doesn't accept it because uh generally uh age is basically a numerical value but you are mentioning car but it should ideally be int to store the data so just keep an eye on it when you're are creating new tables so let us now look at uh an example of create statement so I want to create a table name employee for that I'm writing as create table uh space employee and within the uh parenthesis I'm mentioning employee ID as my first column space int which is the data type and I'm giving name Vare address Vare close the parenthesis and put a semicolon and if you execute this state M this will be the following result it will display uh the table with different columns uh with the First Column as employee ID the second column is name and uh the third column as address easy right the next one is SQL alter uh table the SQL alter statement is basically is used to add uh modify or even uh Delete you know certain columns from the existing table let us look at the syntax of SQL alter command so the scale alter command is Alter table space table name that you have to mention add and within the parenthesis you have to mention the column and the data type similarly you can add a number of data types as per your requirement let us now look at one of the example now I wanted to uh add the date of birth of the employee in a new column for that I'll write as alter table employee add and within the parenthesis date of birth and I'll mention the uh data type as well which is the date and if you execute this it will show the result like this it will add another column date of birth in the already existing table now uh due to certain reasons if you want to uh drop or delete the date of birth uh column for that we have to write syntax as alter table give the employee uh table name drop column which is the keyword we use and date of birth so this will be the final uh output when you execute the uh query it will completely drop the date of birth from the table next we'll look at SQL drop statement SQL drop uh statement basically uh removes all the data and the changes the overall structure of the table by deleting the records in the table so let us look at the uh syntax the syntax followed is a drop table for followed by the table name that uh we want to create let us look one of the example uh the syntax followed is drop table employee and when I execute this it will show an error stating that table employee does not exist which means we have completely deleted all the records uh from the employee table SQL truncate statement let us look at the SQL truncate statement syntax the syntax is truncate table which is the keyword that is used and we have to specify the table name for example if you want to truncate the values in employee table we have to write truncate table employee so this will be the output where it shows null that is uh there are no values in the employee table and uh you can further add values of your own choice let us now look at data manipulation language data manipulation language or DML is used to access and manipulate data tables commands that are used in dmlr insert it is used to insert new values into the table select it is used to extract data from the tables update it is used to update values in the already existing table delete unlike the drop command delete command is used to delete a specific row or all the rows in the table let us first look at the SQL insert statement the syntax followed uh in SQL insert statement is insert into which is the keyword that we use space table name that we have uh created values and inside the par parenthesis you have to mention the values there is also another method uh to insert the values where we specify uh the column name as well as the values now if you execute this so but before executing take an example uh we are creating a table student and we are inserting uh values such as role number name age and City so uh the role number uh is given as one uh the name is given as Rohan the age is given as 22 and the city is given as Hyderabad for the first row similarly uh we'll do the same for the next two rows as well and if you execute this query this will be the final output where you'll have four different uh columns having uh the First Column as role number the next one having name age city with the role numbers as one Rohan 22 Hyderabad and the second row consist of the role number two anjana uh age being 20 and the City Bangalore and the third one is role number three the name is Kell age 21 and the city is Mumbai SQL select statement let us now look at the uh syntax of SQL select statement the SQL is followed the syntax is followed as select column 1 column two and a number of columns from the table name and if you want to uh display all the names from the table you have to use asri that is Select star from table name let us look at the example so I want to display the role number name AG from the student table for that I'll write select role number comma name comma age from the table that is student student and if I execute this it will only display role number name age of the uh students and it will not display the city of the students and if you want to uh display uh even the city uh to which they belong we have to write select star from student then it will display even the city in the uh table SQL select condition statement consider employee table uh with having ID name age City and salary and uh if you want to specifically uh you know display uh the employees name uh who live in uh City New Delhi you have to write select star from employee where city is equals to New Delhi here we are applying a condition that is where which is a SQL clause and we are using an operator as well which is equal to and if you execute this statement it will uh show this as output where you'll find the ID of Alo Singh Ravi Patel who belong to the city New Delhi SQL update statement it is used to update already uh existing values in the statement for example if you consider the syntax it is followed as update space table name that you have created set column name equals to new value that is the updated value that you want to keep into the new table and you have to uh keep a condition as well that is where condition let us look at an example now if I want to update the student table and if I want to insert uh if I want to change the uh city of the role number three that is cel's uh City uh I'll write as update student set City equals to Chennai where role number is equals to three and when you execute this it will show like this so the city uh which is Mumbai has been changed into Chennai SQL delete statement SQL delete statement uh is is used to delete a specific row or even all the rows from the table the syntax is delete from table name for example if you want to delete all the records from the uh student table we'll write it as delete from student semicolon so this will be the output it will only display the uh column names and the the data inside it will be completely erased and if you want to delete only a certain uh number of rows we have to uh use Query as delete from student where role number is equals to one and when you execute this query it will show like this it will completely erase the records of uh the role number one student and the final one is data control language or DCL DCL is responsible for maintaining the secure which gives control access and permissions of the database commands that come under dclr Grant it is used to Grant the permission to user to access the database revoke it is used to cancel or take back the permissions that were earlier granted let us now look at scale Grant statement sometimes user is restricted from creating or making any changes uh within the table but with the help of Grant statement we can give privilege to the user to create or modify uh the records in the table let us now look at the syntax of SQL Grant statement it is followed as Grant space privilege list that is basically the set of commands that you're giving access of permission to the user to perform uh certain operations on the table that you have created to user we have to mention the username example Grant inserch select on employee to Rahul that means Rahul is able to access uh the employee table and can perform uh command such as insert and select SQL revoke statement SQL rork statement is basically the opposite of Grant statement it is used to take back the permissions that were earlier granted the similar the syntax is also similar to the grant statement instead of Grant keyword you have to replace it by revoke let us look at the example revoke insert on employee from Rahul that means Rahul is no longer longer able to insert new values into the employee table what are SQL operators SQL operators are basically reserved words or special characters that are used to query a database SQL provides us with many such operators to ease the process of data manipulation SQL operators are used to perform various tasks including complex mathematical operations like arithmetic and binary to query a database using operators we use a we Clause operators are necessary to define a condition in SQL as they act as a connector between two or more SQL statements based on the operator functionality it manipulates the data accordingly and gives the result types of SQL operators SQL operators are mainly classified into three types the first one is arithmetic operators the arithmetic operators perform the mathematical operation on numerical data of SQL tables they're further classified as addition operator the addition operator is used to perform addition on numerical data using this we can add values of single or multiple columns in the table subtraction operator subtraction operator in SQL performs the subtraction on the numerical data of the database table multiplication operator multiplication operator in SQL performs multiplication on the data items in SQL we can easily multiply the numerical values of two or more Columns of the same table by specifying both the column names as the first and second operant and finally division operator division operator in SQL divides the oper end on the left side by the oper end on the right side we can also divide the numerical values of one column by another column of the same table by specifying both column names as the first and second operant let us now look at the execution part of the arithmetic operations on my skl workbench as you can see workbench has started but before we proceed to the syntax of SQL operators we have to first create a database and then we have to create a table within the database so for time being I've already created a table named employee so let us just briefly go through the syntax of create table so the syntax is followed as create table space now you can declare the employee that is the table name in two ways base that is you can directly mention the table name that is employee or else you can mention database dot table name that is employee and after that in the parenthesis you have to mention the column names followed by its appropriate data types so as far as this table employees concerned I have taken uh column names as ID name age City and salary and also I've chosen primary key as ID as it uniquely Iden I each and every record in the table close the parenthesis and put a semicolon now that youve created a table uh you have to insert values into the table and for that we use insert command so the Syntax for insert command is insert into space table name that is employee and within the parenthesis you have to mention all the column names that you have taken uh in the single Cotes after that close the parenthesis and uh you have to write values which is the keyword and mention different values as per your choice accordingly that you have taken uh into the columns so in this way you can uh insert n number of Records into the table using insert command uh for this employee table I've taken out values total of uh 10 values now that you have created and inserted values into your table and if you want to display all the records of the employees is you have to uh write you have to select the query as select star from the table name that is employee and when you execute this it will show the records of all the employees that you have taken that is their ID their name age City and salary so we have taken total to 10 uh values and it will be displayed now that we've understood all these let us uh proceed with the SQL operators first let us look at uh addition operator uh let us understand this with an help of an example uh suppose you want to add 10,000 to the salary of each employee specified in the table then we have to write the uh query as select salary plus 10,000 as I'm taking a new column that is employe new salary to specify all these values into the uh column as employee new salary from the table name that is employee now when you execute this it will show the result like this that is uh it will add 10,000 to each and everyone's uh values that is 30,000 has been changed into 40,000 28,000 has been changed into 38,000 and so on now if I want to display their ID name the previous salary and the final salary simultaneously I'll write the query as select ID comma space name space salary space salary plus 10,000 as employee new salary from employee now if I execute this it will display like this that is it will uh mention the ID name the previous salary and employee new salary simultaneously so in this way you can add two or more columns in the same table using addition Operator Let us now look at subtraction operator subtraction operator is also similar to that of addition operator the only difference is you have to replace uh the plus sign with the minus sign so again for this example uh let us say uh if you want to subtract 2,000 from salary of each employee given in the employee table then we have to write the query as select ID comma space name comma space salary comma space salary minus 2,000 as employee new salary from the table that is employee so let us execute this and it will show the new values that is 2,000 has been deducted from the salaries of each and every employee as you can see 30,000 has been reduced to 28,000 28,000 has been reduced to 26,000 35,000 has been reduced to 33,000 and so on so next let us look at the multiplication operator again let us take the same example uh if you want to multiply the salary of each and every employee uh then you have to write the query as select ID name salary salary into two we are using hash asri operator as the multiplication sign here as employee new salary from the table name that is employee now if you execute this it it will show the values like uh in this way that is the salary has been doubled if you as you can see the uh employe Rahul salary 30,000 has been multiplied uh and has changed into 60,000 that is doubled 28,000 has been doubled to 56,000 and so on so in this way you can uh apply multiplication operator and finally let us look at uh division operator uh let us take the same example uh suppose let's say if you want to divide this salary of each and every employee then you have to write the query as select ID name salary salary divided by two as employee new salary from the table name that is employee and put a semicolon let us now execute this and as you can see uh the salary has been divided uh into half uh Rahul salary 30,000 has been changed to 15,000 Kiran salary 28,000 has been changed to 40 14,000 and so on so in this way you can use athematic operations to perform various uh operations on your table that is addition subtraction multiplication and division the second type of SQL operators are comparison operators the comparison operators in SQL Compares two different data of SQL tables and check whether they are same greater and lesser the SQL comparison operators are used with a conditional Clause V in the SQL queries they are again further divided into various types the first one is equal to SQL equal to operator is one of the popular and most frequently used operators in SQL queries it shows only that data which matches the specified value in the query less than the less than operator in SQL shows only those data from the database tables which are less than the value of the right right hand side operant greater than the greater than operator in SQL shows only those data which are greater than the value of right hand operant we also have less than equals to and greater than equals to operators as well which basically shows the data in the tables which are less than and equals to as well as greater than and equ equals to the value of the right hand operant let us look at the syntax and execute them in the workbench firstly let us look at the equal to operator let's say uh if you want to access the records of all those employees who are having salary uh equals to 40,000 so for that we'll write the query as select star from the table that is employee we have to use the conditional statement where salary is equals to 40,000 and put a semicolon when you execute this it will show the records of all those employees who are having salary equals to 40,000 as you can see it is showing three IDs uh pranai Anusha and PR who are having salaries is 40,000 next let us look at uh not equals to not equals to is basically the opposite of equal to and uh to understand this let us take an example suppose if we want to access the records of all those employees whose salary is not equals to uh 35,000 so for that we'll write the query as select star from the table that is employee where uh let us put the keyword in the uppercase where salary not equals to 35,000 let us execute this and let us see the output so as you can see uh it will display the records of all those employees who are not having the salary uh 35,000 so it will display the record such as rahul's Kiran pray Anusha Vera who are having salaries is 30,000 28,000 40,000 23,000 and so on which are basically not equals to 35,000 next let us look at uh greater than operator uh for this let us take an example for instance if you want to access the records of all those employees uh from the employ table uh whose ID is greater than 104 so for that we we will write the following query as select star from the table that is employee where ID greater than 104 put a semicolon and let us execute this so as you can see it will display the records of all those employees uh who are having ID greater than 104 that is it will display from 105 and up until 11 similarly uh we have the less than operator as well which is basically the opposite of greater than operator for that uh we'll take an example such as if you want to display the records of all those employees who are having ID less than 105 so for that we'll write the query as select star from the table that is employee where ID is less than 105 let us execute this so as you can see it will display all those records of the employees who are having less than 105 that is it will display the records of the employees 101 102 103 and 104 IDs next let us look at uh greater than equals to greater than equals to is also similar to that of greater than but the thing is we are also mentioning the equal to operator as well so for instance uh let's say if you want to uh access the records of all those employees in the employee table who are having salary greater than or equals to 40,000 so for that we'll write the query as select star from the table employee where salary greater than equals to 40,000 let us execute this and this is will this will be the output it will show the uh records of all those employees who are having salary greater than or equals to 40,000 so in this case it will display a total of four records of the employee table similarly uh let us look at the uh less than equals to operator as well which basically uses less than and equal to operator so suppose if you want to access the records of all those employees from the employee table who's having Sal let's say less than or equals to 30,000 so for that we'll have to write the following query as select star from employee where salary less than or equals to 30,000 so let us execute this and the output will be like this that is it will display all the uh employeer Records who are having salary less than or equals to 30,000 a total of three records that is uh ID employee IDs 101 102 106 are being displayed because they're having salary is 30,000 28,000 23,000 which are basically less than or equals to 30,000 so in this way you can uh use comparison operator to perform various operations on your data and finally we have logical operators The Logical operators in SQL perform Boolean operations which Give two results either true or false these operators provide True Value if both operant match The Logical condition and vice versa various logical operators in SQL are and are not and between let us understand their syntaxes and execute them in the workbench now firstly let us discuss about the and operator and operator in SQL will show the records from a database table if all the conditions separated by the and operator is evaluated to be true let us understand with an example let's say if you want to access the records of all those employees from the employee table whose salary is greater than 25,000 and the city that they belong to is Hyderabad so for that we'll write the following query as select star from table name that is employee where salary is greater than 25,000 and the city that they belong to is Hyderabad so when you execute this statement the following output will will be this that is it will display the uh total three records of the employees that is Rahul Kiran and chinai who are having salary is more than 25,000 and the city that they belong to is Hyderabad so in this way and operator in SQL is used to compare data with more than one condition if and only if all the conditions return true then only it will display the records otherwise it won't next let us look at the r operator R operator in SQL shows the records from the table if any of the conditions separated by the r operator evaluates to be true let us understand with an example for instance if you want to display the records of employees from the employee table who are uh whose salary is greater than 30,000 or the city that they belong to is Bangalore so the following query would be select star from the table that is employee where salary is less than 30,000 or the city that they belong to is Bangalore let us execute this statement now so this will be the following output where you can see a total of four records of the employees uh followed by Kiran pranai vsha and roit who are having salaries less than 30,000 or else they belong to the City Bangalore so in this way uh we can use R operator which basically compares the data with more than one condition and unless like and operator if either of the condition is true it will return the data otherwise it won't next let us look at the between operator between operator in SQL shows the record within a specified range mentioned in the SQL query if there is no value in this given range then this operator shows null value suppose if you want to access the information of all the employees from the employee table whose salary is in between 25,000 and 35,000 then we'll write the following query as select star from table name that is employee where salary between 25,000 and 30, 35,000 let us execute this query now so following is the output where it will display the records of all those employees who are having salaries in between 25,000 and 35,000 and finally let us look at the not operator not operator in SQL shows the records from the table if the condition evaluates to be false that means the not operator is also called as a negation or a negate operator which shows data for the opposite of the conditions that we mention in the SQL statement let us understand this with the help of of an example let's say if you want to access the uh information of all those employees from the table who are not having salary as 40,000 so for that we'll write uh the syntax as select star from table name that is employee where not salary equals to 40,000 so let us execute this query now so as you can see it will display all the records of those employees who are not having the salary as 40,000 so what are SQL Expressions SQL expressions are composed of one or more keywords or values such as operators operant and various other functions that evaluate to a single value or a set of values for a given SQL statement these SQL expressions are like a formula which are similar to that of mathematical formulas we use to solve a problem and in this case they are written in a query language using a proper syntax to perform operations on the data we have stored in our database table for example consider the basic syntax of the select statement here expressions are used in many context such as to retrieve any value from the table we use select command and for comparison we use where Clause so in this case all these are SQL Expressions only types of SQL Expressions SQL Expressions can be classified into following categories the first one is Boolean expression SQL Boolean Expressions fetch data based on one to one matching in other words we can think of it as a query that fetches one result at a time it'll fetch the condition against uh the single value when the query is executed the second one is numeric expression SQL numeric expression is used for performing mathematical operations in a scale query besides athematic operations there are several built-in functions like average sum count as well date expression SQL date expressions are used to compare and get date according to its various date and time related query and conditions they give date time value as the output it you can also return current system date and time values also now that we have understood what exactly SQL expressions are let's execute them in mysale workbench with the help of examples as you can see scale workbench has started and before we get to the execution part of various SQL Expressions we have to first create a table so for that I've already created a table named student within the database simply code the student uh table consists of various column such as role number name of the student age the city they belong to date of birth the stream that they have choosen and the total marks that they have scored in the final exam and here the primary key is role number which basically uniquely identifies each and every record of the students in the table so now that we have created the table if you want to retrieve the information of all the students we'll use select command so the following query would be select star from the table name that is student and semicolon let us execute this and as you can see all the records of the students are being displayed that is the name their age the city they belong to the stream and also the total marks that they have scored in the final exam now that we have created table and inserted values into the table let us understand various SQL expressions firstly let us discuss about Boolean expression for that we'll take a simple example from the table itself suppose let's say you want to display the uh records of those students from the student table who belong to the city coochi for that we'll write the SQL query as select star from the table name that is student where City equals to koochi now let us execute this statement and see the output so as you can see there's a student named Divya whose age is 21 Who belongs to the city Kochi similarly you can uh find for other cities as well so let us take for punee and execute this query so the output would be uh it will show the records of two students uh named Aman and Indra who having age 22 and and 25 who belong to the city Pune and having streams is Tri and MBA and the total marks is 922 and 972 so the Boolean expression not only executes the values against the character values which is in this case is Pune but it can also take the values of numerical values as well for example let's say uh the university has got to know the highest marks scored by an indiv visual in the final exam is 988 and it wants to uh retrieve the details of that student so for that the following query would be select star from table name that is student where total marks is equals to 988 let us execute this query now so as as you can see a student named pratik Who belongs to the stream CSC has scored the highest marks 988 so in this way Boolean expressions are used to perform various operations which basically evaluates a given condition uh to a particular value or you can say a single value and if the condition is true it will return the output and display all those records otherwise it won't up next we have numeric expression numeric Expressions so numeric expressions are basically used to perform various mathematical operations so let us understand this with an example for instance let's say the university has decided to change the total marks weightage from 1,000 to 500 and I'll specify a condition where I'll take the total marks and divide them into half and check all the students who have scored more than 480 out of 500 for that the following query would be select star from the table that is student where total marks divided by 2 is greater than 480 put a semicolon and let us execute this so as you can see there are total four students who have uh scored more than 480 when that when the total marks are divided into half for instance if you take uh the record of Rohan who has scored total marks 977 and if you divided it by two that is approximately 4885 which is satisfying the condition which that is greater than 480 and if you look at this SQL statement carefully there are a lot of SQL Expressions here firstly the student table name that is the student itself because uh it is used to uh retrieve the values from the columns that is uh here the total marks is the column and we also have the oper that is total marks divided by two and the right hand operant that is 480 which can also be considered as an SQL expression here similarly we have other inbuilt functions as we discussed earlier like sum average count minimum and maximum let's say if you want to calculate the average of total marks of each and every student scored in the final exam the following query would be select average which is the SQL function and within the parenthesis mention total marks total marks from the table that is M student let us execute this as you can see it is displaying the total average of total marks that is 950 4 4.375 similarly you can calculate uh the sum of all the marks of the students as well so the query would be select sum total marks total marks from the student table let us execute this and this will be the following output so there is a bit error in the code let me check it once so as you can see it is displaying the sum of total marks that is 7,635 so in this way you can use numeric Expressions to perform various oper ations on the SQL tables and finally we have date expression which basically returns date and time values of the table say let's say if you want to uh display the records of those students who are born after 1995 January so for that the following query would be select star from the table name that is student where date of birth is is greater than 1995 January 1st so let us execute this query and see the output as you can see there are total uh five records of the students who are born after January 1995 similarly you can also display the uh current date and time as well using the uh current timestamp function that is Select current time stamp current time stamp and let us execute this query so as you can see it will uh display the current year uh the date and the month format similarly it is also displaying the uh time which is in the format of hours minutes and seconds so in this way you can use various Expressions to query the database for a specific set of data to retrieve the information from the SQL tables what is SQL database a database in general is a collection of organized data for easier access so that it can be managed and stored effectively as far as SQL databases are concerned a relational database is used to store and manage all the data in the form of tables simple SQL queries are returned to retrieve the data from these SQL database tables in a nutshell you can see that SQL is used to connect with the databases as it directly interacts and communicates with the database to retrieve the information stored in tables popular SQL databases some popular SQL databases are MySQL Microsoft SQL Server Oracle database postre SQL and mongod DB these are some databases that are being widely used nowadays by many companies each of them function in their own way and also the syntax of queries that are written can also slightly vary from each other as well let us now discuss the first topic of today's session that is SQL create database in SQL creating a new database is the first and foremost step to St the data in the tables so in order to create a database users must use create database as a keyword in the syntax so the syntax is followed as create database that is the keyword that we are using here followed by the name of the database name in the syntax database name specifies the name of the database which we want to create in the system also make sure that the database you're creating has a unique name and it does not match with other databases as well for example if you look at the following example I'm creating a database named student so the syntax would be create database followed by the database name that is student so let us now run the syntax on MySQL workbench and see how it is executed in real time as you can see MySQL has opened and if you look carefully at the interface of MySQL workbench on the left hand side you'll find schemas or the various databases that are available in the system at the center you'll find the SQL editor where the queries are return and at the bottom you'll find the console where you can see the status or the output of the query that has been executed so firstly let's go ahead and discuss the first topic that is how to create a database let's say if you want to create a new database uh project so the create database statement would be as followed create database that is the keyword that is used to create a new database followed by that we have to specify the database name that is Project put a semicolon and let us execute this as you can see our database project has been successfully executed and if you refresh the schema you you'll find the uh database name as project so in this way you can create a new project and just for confirmation if you uh want to check the databases created or not you can check it in the list of databases by using show databases keyword so let us see and execute this so as you can see these are the various databases that are available in the system including our database project that we have created earlier let us now discuss our next topic that is how to select a database now that you have create a database now the user wants to perform some operations like creating a new table and inserting values onto the existing database in SQL for that first they have to select the database on which they want to run the database queries without selecting the database they cannot create a new table for instance if I want to create a new table that is student and when I execute this query it will throw an error stating that no database selected select the default database to be used so in this way you won't be able to create a new table without selecting the database any database user can easily select the particular database from the current database server using the use keyword in the SQL statement so the following syntax would be use followed by the database name that you want to select for example let us take uh for instance if I want to work on the database project so the following query would be use project which is the database name here let us execute this so as you can see the query has been successfully executed and uh also you can find the database name project is highlighted in a bold that means you have successfully selected uh the database and you can start creating your new tables so in this way uh when you have multiple databases in your SQL schema before starting your operations you have to select a database where all your operations would be performed also keep in mind that the database that you selecting should be unique finally let us look at how to drop the database in other words dropping a database is to delete a database the SQL drop database statement deletes the existing database permanently from the database system that means the statement deletes all the data and the tables that are stored in the database so let us now look at the syntax of the drop database the syntax is followed as drop database followed by the database name that is Project here in this SQL syntax we have specified the name of the database which we want to delete permanently from the database system after the drop database keyword and let us execute this and see the output as you can see the database has been dropped and even at the uh left hand side if you look at the schemas uh database project has been deleted successfully so in this way you can delete a database using drop database statement but be careful before using this operation because by deleting an existing database it would result in complete loss of information stored in the database what is SQL table tables are the essential elements of a database and in particular for relational databases as it is one of the most used data models to store and retrieve data from tables tables are basically an organized collection of data that consists of rows and columns another point to be noted is that a table has a specified number of columns but can have any number of rows let us understand this with an example here the name of the table is taken as an employee and in SQL naming a table is very important because before performing any operation you have to specify the name also the name of the table should be unique and it should not match with other tables in the database the horizontal values represented in the tables are called as rows or tles and each row represents a unique record similarly the vertical values represent columns are the attributes of that particular table and each column represents a unique field in the record as we can see in the table employee ID employee name job department number salary are the various fields or the column names the data in this multiple columns such as the employee name Rohan who's having employee ID 101 who's working as a data analyst belong to the uh Department 3 and having salary 50,000 is stored in the form of row I hope you've understood what is an SQL table now so let us now discuss some basic SQL queries that are performed on these tables and execute them in MySQL workbench as well SQL create table SQL create table is foremost thing to do in SQL if you want to create a table you should name the table and Define its columns and each columns data type let us now look at the syntax of create table the syntax is followed as create table which is the keyword used here followed by that we have to mention the table name and within the parenthesis you have to mention the column names followed by that you have to mention the data type in this way you can add a number of columns as per your choice now that we have understood the syntax let us jump into my scale workbench and execute this if getting your learning started is half the battle what if you could do that for free visit skillup by simply learn click on the link in the description to know more as you can see my scale workbench has started and on the left side you can see that simply code is the database which we are working on and in that database we are going to create a new table so let us now look at the syntax of create uh table so syntax is followed as create table which is the keyword we are using followed by that we have to specify the table name that is we have taken employee in this case and within the brackets or the parenthesis you have to mention the column names followed by that you have to mention the data types here I've taken column names as ID name City job and salary and finally I've chosen the primary key as ID because it uniquely identifies each and every record in the table in the end you have to close the brackets and mention semicolon let us now execute this query and see the output as you can see our table is successfully created so in order to display the values or whether or not our table is created or not we have we have the query as select star from the table name that is employee let us execute this and see the output as you can see the table is created and it is showing the column names as ID name City job and salary so in this way you can create a new table next let us discuss about drop table drop table in other words is basically is to delete a table the skl drop table statement is used to remove the table definition and the overall structure of the existing table be careful that while using this command because once a table is deleted then all the information available in the data will also be be lost forever the Syntax for the drop table is drop table and then you have to mention the table name so let us now look at the example now for some reasons if I want to uh Delete the employee table so the syntax would be drop table followed by that the name of the table that is employee let us execute this as you can see our table is completely dropped and let us now see if it is actually relateded or not for that we'll use select statement select star from the table name that is employ let me execute this query as you can see it is throwing an error stating that table simply cod. employee does not exist this means You' have successfully deleted the existing table employee let us now discuss the next topic that is SQL insert table SQL insert statement is used to add new rows of data into a table this can be a single or multiple records into the database the Syntax for the insert table is insert into which is the keyword followed by that table name and within the parenthesis you have to mention the column names values and again within the parenthesis you have to mention the values so here the column one column 2 column 3 and in number of columns are the names of the columns in which the table you want to insert the data into you may may not need to specify the column names in the SQL query if you're adding values for all the columns of the table but make sure the order of the values is in the same order as the column in the table let us jump into the execution part now I'm taking the same name that is employee table to insert the new values so the Syntax for inserting new values into the table is followed as insert into table name that is employee and within the brackets we have to mention the columns that we have taken earlier that is ID name City job and salary after that we have to mention the values using the values keyword So within the uh brackets I'm mentioning the ID name as 101 name as Rohan the city that he belongs to is Hyderabad and the job that he does is Da that is data analyst and the salary that he earns is 40,000 so let me execute this query and see the output so as you can see our uh query has been successfully implemented now let us display the results by using select query again let me select this whole query and execute this as you can see the details of employ name that is Rohan whose ID is 101 and his the city he belongs to Hyderabad his job data analyst his salary 40,000 is been successfully displayed so in this way you can insert new values into the table up up next let us discuss about delete table in SQL delete table in SQL is used to remove Row from a table this can be a complete Row from a existing table or a specific row if you want to remove a specific Row from a table you should use where condition but if you do not specify the where condition here it will remove all the rowes from the table the Syntax for delete statement is delete from the table name so let us take an example now suppose if I want to delete the records of the employee who's having employee ID as one or two in the table employee so the following query would be delet from table name that is employee specify the where condition keyword that is where ID is equals to2 let us execute this statement now as you can see our statement has been successfully executed now I'll display the records by using select statement as you can see the employee ID who is having uh one or two has his employee ID has been successfully deleted from the table so in this way you can use delet statement to delete a particular record from the table let us now discuss about truncate table in SQL truncate table statement is used to to remove all the rows from the table that means it deletes the complete data from an existing table it is similar to that of delete statement but here we do not use the wear Clause trunky table is faster and uses lesser resources than the delete table command as it deletes the records at a single time drop table command can also be used to delete the complete data but it also deletes the structure of the table as well truncate table does not delete the structure of the data so the syntax for trunet table is truncate table and after that you have to mention the table name so if I want to delete all the records uh from the employee table the following query would be truncate table followed by that you have to mention the table name that is employee let us execute this and see the output so the query has been successfully uh executed so let me use the select statement to display the records as you can see uh all the information of the employee has been successfully deleted from the table but if you look at uh the output closely the structure hasn't been deleted here the column names of the table that is ID name City job and salary have been retained but only the values in the table have been deleted so in this way you can use truncate statement to delete all the records from the table at a single time make sure that uh you use this statement carefully because the roll back process is not possible after using truncate table statement that means if the data is once deleted it is completely deleted uh permanently next let us discuss about SQL alter table the alter table in SQL is used to add modify and delete Columns of an existing table in many situations you may required to add columns in the existing table so instead of creating a whole table again and again you can easily add single or multiple columns using alter table statement with the help of a keyword at let us now look at the syntax of alter table the syntax is followed as alter table table name add and within the parenthesis you have to mention the new column and the data type so let us now jump into the execution part consider the same table employee again suppose if you want to add a new column that is date of birth of the employee in the above table for this you have to type the following query as alter table mention the table name that is employee after that mention the keyword add and within the brackets mention the new column name that is date of birth and the data type of the data birth is date so I'll be mentioning the date data type close the brackets and put the semicolon let us execute this statement now as you can see our statement has been successfully implemented and I'll use the select statement to display the new records as you can see there's a new column named date of birth has been successfully added to our existing table employee in this way you can use alter table to add or delete the columns from the table let us not discuss about SQL rename table SQL rename table is used in SQL to change the name of the table so in some situations the database user might want to change the name of the table so that they want to give a more relevant name or the updated name to the table any database user can easily change the name of the table by using rename table and alter table statement in SQL let us look at the syntax now the syntax is followed as alter table current table name rename to new table name let us now execute this in MySQL workbench consider the same table employee again and suppose if you want to change the name of the above table employee into let's say employee new for this you have to type the following query that is Alter table old table name that is employee rename to is the keyword that you have to use here and mention the new table name we are taking employee new as our new table name so let us execute this and see the output as you can see our statement has been successfully executed so let let us update the schema here and see whether or not the name of the table is changed so as you can see our table employee which we had previously has been changed into employee new in this way you can use rename statement to give a new name to your existing table finally let us now look at SQL copy table statement if you want to copy the data of one SQL table into another SQL table in the same SQL database then it is possible by using the select into statement in SQL the select into statement in SQL copies the content from an existing table into a new table SQL creates the new table by using the structure of the existing table the syntax is followed as select star into new table name from the old table name let us now execute this in myql workbench and see the output consider the same employee table again and suppose you want to copy the content of the employee table into a new table let's say employee details now as far as myale workbench is concerned there is a slight change in the query now the syntax that we've discussed earlier is applicable to some other SQL databases like Microsoft xql server and Oracle database but for MySQL workbench there is a different syntax to copy a new table so the syntax is followed as create table mention the new table name that is employee details now select star from the old table name that is employee let us now execute this and see the output now it will throw an error stating that the employee table doesn't exist that's because we have renamed our previous employee name table into employee new so instead of employee change it to employe New and let us execute this statement now so to in order to display if the uh employee table is created successfully or not in the database let us use select statement to display the records select star from the table name which is the new table name that is employee details let us execute this now as you can see uh the details that we had earlier in our employee new table has been copied into another table that is employee details in this way you can copy the content from one table uh existing table from another new table by using copy statement in SQL and with that we have come to end of today's session these were some of the queries that were related to SQL table if you want to learn more about SQL make sure to check out our previous playlist in our Channel also we have a dedicated video SQL full course 2022 for beginners wherein we have discussed SQL and various Concepts from the scratch what is SQL select statement SQL select statement is used to fetch the data from a database table which Returns the data in a form of a resultant table in simple words we can say that it is used to access the information from one or more database tables within the database let's look at the syntax of SQL select statement the syntax is followed as select column from table name in the select syntax you can see column name one column name two and N number of columns are the name of those columns in the table whose data you wanted to read and if you want to access all the rows from all the fields of the table you can use the select star operator in the database by using select star from table name command let us look at example if you want to retrieve the information of the student who's having role number name and age we can write a simple query as select role number name age from student in this way we can display the details of the student SQL select condition select statement is used with various Clauses as well that means it also retrieves the SEL ected data that follows a particular condition with the help of a wear Clause by using this command we can access a particular record from a particular column of the table let's understand this with an example consider the employee table here which is having ID name age City and salary as its column in the table let's say if you want to access the information of those employees who's having age as 29 for that we'll write a simple query as select star from employee where age is equals to 29 and if you execute this this will be the following output which shows the information of those employees whose AG is 29 so in this way you can use SQL select condition by specifying a where statement which is used to filter the data in a particular table now that we have understood the basics of SQL select statement let us now jump into MySQL workbench and execute this and understand it in in a more better way as you can see myale workbench has started and for time being I've already created a table employee which is having column names as employee ID employee name age designation date of birth city that they belong to and salary and I've chosen primary key as employee ID because it uniquely identifies each and every record in the table now that uh We've created a table in order to display all these records we have to use this uh select statement ultimately so to display all the values from from each column of the table that is the employee table we have to write the following query as select star from table name that is employee let us execute this and see the output as you can see it will display all the values of the employees their employee ID employee name age designation date of birth the city and their respective salaries so if you want to display a particular column so for that you have to write the query as let's say if I want to select employee ID employee name and the city uh of the employees so for that I'll write select employee ID comma employee name comma let's say uh I want to display the salary so I'm taking a salary from table name that is employee let us execute this now so as you can see only the employee ID employee name and the salary of the employees in the table are being shown here because we have only specified a particular column in this case now similarly we can use we clause also the we Clause is used with select statement to return only those rows from the table which satisfy the specified condition in the query for example uh if I want to uh show the salary of the employee who is having uh more than 30,000 so for that I'll write the query as select star from employee where salary is greater than 30,000 so let us execute this statement and see the output so as you can see it will display the records of all those employees who's having salary more than 30,000 simp similarly you can see uh you can also check whether the city that they belong to is Mumbai so for that we have to take the SQL query as select star from employee where city is equals to suppose I want to display the uh employee who belongs to the city Mumbai so for that I'll take the city as Mumbai and execute this statement okay uh you have to mention Mumbai in uh single Cotes so that's the reason it is showing error here so let us now execute this and see the output so as you can see there are only two employees who belong to the city uh that is the employee cavia and Pavan who is having the employee ID 103 and9 so in this way you can use select statement uh to display a particular uh record by using W Clause now that we've got an idea on how SQL select statement works let us not discuss some select statement functions the first one is Select distinct the uh select distinct in SQL is used to fetch identical or distinct column values from existing table without any duplicate values now suppose in a particular table there might be a higher chance that there exist a duplicate value and if you want to retrieve only unique values in such scenario you use uh SQL SP a listing statement I know it might be a bit confusing so let me make it clear to you guys uh consider the same employee table again here and uh if you look at the table the values in the column City the Hyderabad has been repeated more than once year and not just Hyderabad Chennai has also been repeated twice so in this case in a broader sense if I just wanted to know from which city the employee is from I'll just use the distinct select statement so using this uh instead of multiple uh values that are being displayed the SQL distinct statement makes sure that the value is retrieved only once and there is no room for any repeated or distinct value so let us know understand this syntax with an example the syntax is followed as select distinct now let's say if I want to fetch uh distinct designation of all the employees of the company so for that I'm specifying the column name here as designation from the table that is employee let us now execute this uh there is a bit error in the code let me just check it so let us execute the query now and see the output so as you can see this is the uh output that is being displayed where the unique values uh of the designation of all the employees are being displayed that is business analyst manager HR SD and so on so in this way by using SQL select distinct statement we can fetch distinct values from the existing table let us now discuss about select count statement in SQL the select count is used to get the total number of rows from a table basically it Returns the total number of records present in the database table let's take a simple example consider the same employee table now if you have a record in the table and if you want to count the total number of uh Records in the table for that the following query would be select count star from the table name that is employee let us execute this and see the output now so as you can see there are total 15 records present in the employee table similarly you can use the where condition as well if you want to get the uh total number of rows uh of the employees who are having salary equals to 50,000 in the employee table so for that the following query would be select count asri from employee specif the condition here now where salary is equals to 50,000 let us execute this statement now so as you can see there are total three employees who are having salary equals to 50,000 similarly you can get the total number of rows in a table by using the distinct statement as well let's say if you want to retrieve a unique count of theity of the employees from the table the following query would be select count which is the keyword that we are using and inside the bracket mention distinct keyword followed by the column name that is City from the table that is employee now let us execute this statement and see the output so as you can see there are total uh eight employees who are belong to different cities so in this way you can use uh select count statement to retrieve various number of Records in the particular table next let us discuss about select top or limit statement in a SQL the select top statement in SQL shows The Limited number of Records or rows from the database table the top clause in the statement specifies how many rows we want to display from our table this Clause is used when there are thousands of Records stored in the database table now all database systems do not support top keyword now as far as MySQL is concerned it supports the limit keyword so let us now look at the uh syntax of Select limit statement the syntax tax is followed as select star from employee table limit is the keyword that we have to use and I want to display the first three records of the employees so I'm specifying three here so let us execute the statement so as you can see the first three records of the employees uh who are having ID employee ID as 101 one2 one3 are being displayed let us consider another scenario here if you want to fix the first three employees who got highest salaries from the employee table then the following query would be select star from the table name that is employee order by is the condition that I'm using to display the uh employee details salary DC that means descending now I want to display the records in the uh descending order and I'm limiting the value up to two limit 3 so let us execute the statement now so as you can see it will display uh a total of three Records who are having the salary uh who got the highest salary in the descending order that is is Kamal who got the salary as 60,000 CNU having 50,000 and Kiran who is having 50,000 so in this way you can use limit statement to display the specified rows that is the top rows in the table next let us discuss about select random statement in SQL as the name suggest SQL select random statement is used to return a random Row from a table present in the database it has many real life applications for example if a HR manager wants to send uh 10 random mails to his employees then he can use random function in SQL to send the email it is also used uh to display random questions during an online exams for students so the Syntax for select random is followed as select column name now I want to display the records of all the employees so I'm choosing star operator from the table that is employee order by is the conditional Clause we have to use followed by that we have to mention Rand which is the function let us execute the statement so as you can see the uh details of the employees are being displayed in a mixed manner uh that is they are displayed in a random way as you can see the employee details of employee who is having ID 114 is being displayed here first and then 112 and then 1 103 so in this way you can uh display the values randomly using random function in SQL similarly you can uh limit the values as well if you want to uh show the top three uh random values of the employees you can use limit statement here so limit and if I want to display the first four uh records of the employees so I'm choosing four let us ex execute the statement now so it will show the details of the employees randomly up to uh four that is the top four details of the employees so in this way you can use a random statement to display uh random values within a database table next let us discuss about select in statement in SQL the select in function is used to fetch specific rows or values from an existing table with multiple conditions the conditions are specified with in Clause the operation of Select in is same as R operation select in is used to reduce the multiple or operators in select statement let us understand this with an example so if I want to fix the uh details of employees who are having employee ID as let's say 102 or 104 or 107 from the employee table then the following query would be select star from the table that is employee where employee ID now we have to mention the in keyword here and within the brackets mention the IDS so I'm taking one2 one4 and7 so let us execute the statement and see the output so as you can see it is displaying the values of the employees who are having ID either 102 or 104 or 107 next let us discuss about select date statement in SQL SQL select date is used to retrieve the values of date from a database if you want to find a particular date from a database you can use this statement so let us understand with an example let let's say if you want to uh display the records of all those employees who are born before 1995 Jan 1st so the following query would be select star from the table that is employee where date of birth is less than 1995 Jan 1st make sure you incorporate the uh date in the single quotes otherwise it will throw an error so let me execute this statement so as you can see it is displaying the records of all those employees who are born before 1995 Jan 1st similarly you can use uh greater than operator as well to show the employees who are born after 1995 similarly uh if you want to uh fetch the employees who have Bor between a particular date let's say who are born between 1996 and 1998 then the following query would be select start from employee where date of birth between is the keyword we have to use between let's say uh I'm using 1996 Jan 1st and 1998 1998 Jan 1st so let me execute this statement and see the output so as you can see it is displaying the records of all those employees who are born in between uh the particular date that we have given that is in between 1996 and 1998 so in this way you can use the select date statement to retrieve the date values in the database next let us discuss about select some statement in SQL select sum is used to return the sum of all the values in a specified column now you have to make a note here that some some function is applied only on numeric or numeric related fields let us consider the same employee table again and if you want to get the sum of all the employee salaries in the employee table then the following query would be select sum is the keyword and within the brackets mention the salary column from the table that is employee let us execute the statement and see the output so as you can see it is showing the sum of uh the salaries of all the employees here that is 6 lakh 5,000 is the total uh value of the combined salary of all the employees similarly you can use the wear condition here as well uh to get the sum of the uh salaries of employee by specifying a condition let's say if I want to uh get the total salary of all those employees who belong to the city uh Mumbai so for that I'm writing the query as select some salary from employee where is the conditional Clause City equals to Mumbai make sure Mumbai is in single codes otherwise it will throw an error so let us execute the statement now and see the output so as you can see it is displaying the uh sum of all the salaries of those employes employes belonging to Mumbai as 80,000 so in this way you can use the uh sum select sum statement in SQL to display the sum of values in a particular column and finally let us now look at the select null statement in SQL null in a table represents that the field has no value these null values are used to represent the missing data in a particular table now a null value is different from a zero value the that means a field with a null value is the one that has been left blank during the record creation Now to verify the column value is null or not we can use the keyword is null or is not null now we should make a note here that null value can't compare with operators like equals to greater than or less than so let us take an example to understand the syntax now if you want to fix the details of the uh students uh whose marks are null that means the marks that they have scored in the final exam are yet to be assigned to the students so for that the following query would be uh select select star from the table that is student where is the condition marks is null which is the keyword we are using so let us execute this statement and see the output so as you can see there are two students whose marks are yet to be updated and it also means that their marks are not equals to zero it's simply that their marks have been left out and kept as null what is SQL Clause SQL Clause are basically inbuilt SQL functions that use a certain conditional expression which helps to access a particular of records from the database table Clauses help us to restrict and manage the data using valid constraints on the data in our database now since we have large amounts of data stored in the database we use Clauses to query the table to get the desired data only so the complexity is reduced when condition is applied to an SQL statement SQL Clauses use filters and analyzes the data quickly because it is used to extract only those records that fulfill the specified condition types of SQL Clauses SQL Clauses are divided into three types the first one is the basic clause which uses a condition the second one is order by which is used to sort the data in tables in either ascending or descending order and the last one is groupby which is used for organizing similar data into groups SQL wear Clause scale wear Clause is the most used and integral part of any query to specify a condition in the SQL statement which retrieves only those records which satisfy the given condition the where Clause is not only used in the select statement but it is also used in the update delete statement Etc using logical and comparison operators like greater than less than and equal to operators let us now look at the syntax of we Clause the syntax is followed as select column 1 column two and up to n number of columns from table name where condition here column one column to represents the columns which we want to fetch the data from the table and the condition here represents the required condition to fetch rows based on the requirement it contains column name operator user defined value comparison and logical operators are also used in this condition so in this way we can use we Clause let us now execute some statement which uses we clause in myl workbench as you can see the my scale workbench has started and before we write queries using wire Clause we have to first create a table so I have created a table here employee which is columns employee ID employee name age destination date of birth the city they belong to and their salary and I've taken primary key here as employee ID because it uniquely identifies each and every record in the table now that we've created the table let us now look at uh the simple and basic query using the W Clause now let's say if I want to uh display the details of all those employees who belong to the city Mumbai so for that the following query would be select star from the table that is employee now we have to mention the condition that is the where Clause where city is equals to Mumbai mention Mumbai in single quotes otherwise it will show an error so let us now execute the statement and see the output now when we execute the statement it will show me the output of two employees who belong to the city so let us now take another example now we'll use the greater than operator using the we Clause now let's say if I want to uh display the details of all those employees whose salary is greater than 30,000 so for that the following query would be select star from table that is employee where salary is greater than 30,000 so let us execute the statement now when we execute the statement it will show me the records of all those employees who are having salary more than 30,000 now I think here we have around uh 15 since we have 15 uh Records in the table we have a total of eight employees whose salar is more than 30,000 so in this way you can use a greater than operator similarly you can use the less than operator as well and let us see the output whose salary is less than 35,000 we'll take 35,000 here and see the output so there are uh total three employees who are having the salary less than 35,000 that is Arian pry Akil who are having salary as 30,000 so in this way you can use less than operator as well now let us make another scenario now if I want to mention a range of salaries of how much they earn let's say if I want to display the records of all those employees who are earning salary in between 35,000 and 50,000 000 so for that the following query would be select star from the table that is employee where salary between is the keyword we have to use here 35,000 and 50,000 let us now execute this statement and see the output so now these are all the uh records of the employees who are having salary in between 35,000 and 15,000 so in this way you can use uh various comparison operators to perform uh queries using we Clause similarly uh you can also update the values in the table as well using the update command let's say if we want to update the salary of employee whose employee ID is 104 so for that the following query would be update table name that is employee set is the keyword we have to use here salary now I want to change the salary as uh 25,000 so I'm choosing 25,000 specify the condition where employee ID is equals to uh 106 so let us execute the statement and see the output so as you can see our query successfully implemented and the employee who ID 106 who is having salary like before some he used to have some salary now his salary is changed into 25,000 so let us see whether it is changed or not we'll use the select command again to display the details of all the employees so for that the query would be select star from the table that is employed let us now execute this state M so 106 uh employee name is Wasa who's working as a software developer and salary has been changed to 25,000 so in this way you can use work claw to perform various operations using certain conditions if getting your learning started is half the battle what if you could do that for free visit scale up by simply learn click on the link in the description to know more now that we' have understood how to write a query using the where condition let us now look at some other conditional statements which uses where Clause first let us discuss about SQL and condition SQL and condition is used to specify multiple condition in where clause and condition basically Returns the rows or those values that satisfies both the conditions that are written after the wear clause in simple terms we can say that uh it will only return those values when both the conditions are met let us now understand this with an example let's say if I want to access the records of all those employees whose designation is business analyst and their salary is 35,000 for that the following query would be select star from the table that is employee where I'm specifying the condition here designation is equals to business analyst and is the keyword that we have to specify here salary is equals to 35,000 and let us execute the statement now so as you can see it is displaying the records of two employees whose designation is business analyst and their salary is 35,000 but if you look at the uh employee table carefully let me just display the values uh there's the uh employee ID 101 Who belongs to the designation business analyst and similarly we have another employee whose employed is 109 and whose employed is 111 here we have three records but instead it is displaying only three uh two records that's because there's an employee ID 109 who's having Who belongs to the designation business analyst but is having 40,000 so in this case he's not meeting the other condition that is why it is being displayed only two values so in this way you can use and statement to filter the records from the table let us now look at the next statement that is R SQL R condition is used to specify again the same multiple conditions in where clause which is used to fetch the rows or values which satisfy any of the condition now unlike uh and statement if any of the condition that is provided is true then it will return all those values so let me just uh execute this with an uh example let's say if I want to access the records of all those employees whose designation is manager and the city that they belong to his Chennai so for that I'll write the query as select star from the table that is employee where designation is equals to manager or the city that they belong to is Chennai so let me just execute this statement and see the output so when you execute the statement it will show a total of three records of all those employees whose designation is either manager or the city that they belong to is Chennai now we have uh the records of employee who belongs to City Bangalore indoor as well but it is showing here because the designation is manager that is here it is satisfying either one of the condition that is either the designation that is manager or the city that they belong to is Chennai so in this way you can use R statement to filter the records next let us discuss about the limit condition in SQL the limit condition is used to fetch those records that have only limited number of of values let's say if I want to access the records of first five employees from the table then I'll write the following query as select star from the table that is employee now we have to mention the keyword limit now I want to access the records of first five employees so I'm specifying five as the condition here so let me execute this statement so it will display the first five records of the employees from the employee table now this limit condition is used like when you have thousands of Records in a table and if you want to access the first 100 records of the employees then you can use the limit condition here with the example next let us discuss about SQL as condition statement now SQL as condition is used to rename a column temporarily in a given table now in simple words we can say that SQL as keyword is used to give an alas name to the table or column name in the query and in this way we can increase the readability and understandability of the query and call and also the column headings in the table so let us understand with an example let's say if I want to change the column name of salary to the total salary then I would write the following query as select salary as within the single codes mention the new column name that is total salary from the table that is employee so let us execute this statement and see the output so as you can see salary has been changed into a total salary in this way you can use the and condition statement to change the column of the table temporarily and finally let us discuss the like statement in SQL the like condition statement is used to fetch matching rows or values from the table that satisfies the Wild Card operator now the Wild Card operator in SQL uh basically have two types the first one is percentage sign the percentage sign represents a single or multiple character and the second one is underscore underscore represents a single number or character now you might be a bit confused here so let me just explain with an example let's say if you want to access the records of all those employees whose name starts with k so for that I'll write the query as select star from the table that is employee now specify the condition where employee name like is the keyword we have to use here and within the single codes now we are displaying the records of employees whose name starts with k right so I'm taking K and mention the percentage symbol so let us execute this statement and uh see the output so it will display the uh details of all those employees whose name starts with k so there are total uh three employees in the table whose name starts with k here similarly if you want to display the records of those employees whose name ends with a so for that we have to mention percentage a so let me just execute the statement and see the output so it will display the values of all those employees whose name is ending with the letter A so there are total five employees in the table whose name is ending with a so in this way you can use the like operator which also uses the V Clause so what is order by Clause the SQL order by Clause allows you to sort the results of a query based on a specific column or group of columns that means it helps you to reorder the data that is present in the tables in one or more columns now this sorting can be either ascending or descending order let us now understand the syntax of SQL order by Clause the syntax is followed as SQL select column 1 column 2 up to n number of columns from table name where condition ordered by column ASC or DSC so let me just explain the syntax the column specified after the order by keyword specifies the name of the columns that are used to sort data and after that we use two keywords that is ASC to represent the data in ascending manner or DEC to represent the data in descending order now the reason for using the order by Clause is that the order that the data shows in a uh database table is completely random and sometimes this might not be the order in which you would like to see uh when we run our queries in the database so for that purpose we use order by clause now that we've understood what the SQL order by Clause is let us jump into the execution part as you can see myl workbench has started and on the left side you can view the database simply code which has two tables that is employee and student we are going to work on the employee table now so first let me display the uh records of all the employee details present in the employee table for that I'm going to use the select statement select star from the table name that that is employee let me just execute this statement so it will show me the uh results of details of all the employees with their employee ID employee name age destination date of birth city they belong to and the salary they have so let us just understand the basic query of order by Clause now so the syntax is followed as select star from the table that is employee order by salary now this is a basic statement related to order by and let us execute this so it will display the output and uh in a particular order of the salary and if you look it carefully it is displaying in the uh ascending order now if no keyword is specified after the column based on which we have sorted the records in our table the Sorting will be done by default in ascending order so with that uh that brings us to the to our first order by statement that is order by ascending statement now the order by ascending statement is used to sort the data in ascending order so let us consider another example here now if I want to fit the details of all the employees uh and their employee names in ascending order for that the following query would be select star from the table that is employee order by employee name and mention the keyword ASC so let us execute this statement and see the output so it will display the results uh the employee names in the ascending order that is in alphabetical order starting from Akash Akil bavya Ganesh and up until V so in this way you can use the order by uh ascending statement to display all your records in ascending man man know similarly you can specify the V condition here as well let's say if I want to fetch the uh details of all the employees from the employee table in ascending order of their uh employee name whose age is greater than 26 so for that the following query would be select start from employee where age greater than 26 ordered by employee name as an ASC so let me execute this statement so it will display the records of all those employees whose age is more than 26 and it will uh it will sort the data of employee names in ascending order so in this way you can use the order by ascending if getting your learning started is half the battle what if you could do that for free visit scaleup by simply learn click on the link in the description to no more now let us discuss about order by descending statement the order by descending statement is used to sort the data in the descending order let us understand this with an example let's say if I want to display the details of all the employees from the uh employee table in descending order of their salaries for that the following query would be select star from the table that is employee order by salary and mention the keyword d s so let us execute this statement and see the output so this will be the following output wherein it will show the salaries of all the employees in the descending order that is from highest to lowest uh in this case the first employee who's having the highest salar is 65,000 and up until the lowest salary 25,000 also similarly you can specify the wear condition here also so if we want to display the uh details of employees who employee ID is greater than let's say 106 and want to sort their salaries and descending order for that I'll use the query as select star from employee mention the keyword where employee ID greater than 106 so let us execute this statement and see the output so it will display the records of all those employees uh who's having employed more than 106 and it it will sort the data of the salaries in descending order that is from highest to lowest again so in this way you can use the order by descending statement to display all your reords present in the table in an ascending manner let us now discuss the next order by statement that is SQL order by random and as well as limit statements the order by random statement is used to display the records uh present in the table randomly and the Syntax for the order by random is Select star from the table that is employee order by mention the keyword random is the function we are using here so we have to mention ra ND and let us execute the statement now so it will display the records present in the table in an in a random order similarly we also have a limit statement which is used to uh display only a specific number of columns in the uh database table so let us now look at the syntax of order by limit now let's say if I want to fet fetch the first uh six employee details from the table in descending order of their uh salaries so the following query would be select star from from employee order by salary I want to display the salaries and descending order so I'm specifying the DC keyword and uh I'm limit I'm using the limit function here so let us display the uh output of this query so it will show me the random uh six details of the employees in uh order of their uh salaries from highest to lowest that is in a descending order so in this way you can also use order by random and limit statement in your SQL queries and finally let us now discuss about the order by multiple statement in SQL till now we have discussed only how to uh fetch the records from only a single table and sort them out but you can fetch the rows by Sting multiple rows in either ascending or descending order using order by multiple St statement so let us understand this an example let's say if you want to fetch the details of all the employees from the table in ascending order of their designation as well as descending order of their salary then the following query would be select star from employee order by uh so we're taking the designation as ascending order so designation mention the keyword ASC comma and the salary in descending ma manner so let us display the uh statement so as you can see it will display uh the designation in uh ascending order that is business analyst and then customer K data analyst HR and so on and their respective salaries in descending form so this way you can uh fetch the uh data from multiple rows using the multiple statement in SQL so what is SQL insert statement SQL insert statement is widely used command in SQL which is a part of data manipulation language DML used by various relational databases the insert command is used for inserting one or more rows into a database table with specified table column values let us now understand the Syntax for insert statement now we have two types of syntaxes the first method is insert into table name values and within the brackets you have to mention the values in this first method there is no need to specify the column names where the data will be inserted you need to only insert their values the second method specifies both the column name and the values which you want to insert and the syntax is insert into which is the keyword we are using after that we have to mention the table name and within the brackets you have to mention the columns and then values values one values two values three and so on up to our requirement so this was the syntax uh let us take an example now let's say I've created a table students and I want to insert values into it so the Syntax for inserting the values is insert into table name I'm taking here it as students and I'm mentioning The Columns role number name age City and I'm inserting the values as role number as one name as Rohan age as 22 and city as Hyderabad so in this way you can insert values up to n number of rows into your uh table so now that we've understood and got an idea of what SQL insert statement is let us jump into MySQL workbench and do the execution part choose from over 300 in demand skills and get access to 1,000 plus hours of video content for free visit skillup by simply learn click on the link in the description to know more as you can see my scale workbench has started and uh in order to insert the values into the table we have to first create a table so let us first create a table and the syntax is followed as create table which is the keyword that we use and I'm creating a new table that is customer and which is column names as customer ID customer name their age address the product they have purchased and the purchase date of the product and I'm taking primary key here as customer ID because it uniquely identifies each and every record so let me just use the select statement to uh display whether uh the table is created or not select star from the table that is customer so as you can see our table is successfully created with the column names that we have specified now that we have created a table let us see how to insert the values into it for that first let us discuss the insert into value statement the insert into value statement is used to insert either all the column values or a specified number of column values in the table inserting only specified column data in the row requires the column name should be spe specified in the insert statement so let's say if I want to insert all the uh columns uh into the customer table the following query would be insert into table name which is the customer now within the uh brackets we have to mention the column names so the columns that we have taken are customer ID customer name their age address product that they've purchased and the purchase date close the brackets now mention the values keyword and again within the brackets specify the uh values for each of the uh columns so I'm taking a customer ID as uh let's say 1,1 comma customer name uh let's say Rahul now make sure you uh mention the character values inside the single codes and for integer values you need not mention the uh single codes next their age let's say 25 comma address uh let's say uh the city they belong to is the address so I'm taking as Hyderabad comma next the product that they have purchased let's say they have uh let's say rul has purchased the phone as the product and finally purchase date uh let's say 2022 uh March and let's take date as uh 25 so close the brackets and uh put the semicolon so let us execute this statement and see so as you can see our query has been successfully executed now to display the values I'll use the uh set command again so let us execute the statement so as you can see the values have been successfully inserted into our table uh with respect to their columns as customer ID customer name age address product and the purchase date so here we've inserted a row with all the column values using the insert statement similarly you can insert a row with only specified column values as well uh let's say if you want to insert data for only the columns customer ID customer name and the product they have purchased and leaving the rest of the columns as such the following query would be insert into table name mention the table name that is customer and within the brackets mention the columns that you want to enter the values so I'm only entering the values for customer ID customer name and the product that they have purchased and mention the uh keyword values open the brackets and specify the values as accordingly so I'm taking customer idas uh 1,2 customer name let's say cavia and the product that they purchased is let's say uh as AC close the brackets mention the uh semicolon and let us execute the statement so as you can see our query successfully executed so let me display the values and I'll use the select statement to display the values so as you can see only the customer ID customer name and the product that they've purchased is being displayed here and leaving the rest of the uh columns that is age address and the purchase date as null values so in this way you can also insert uh values into only the specified uh Columns of the table next let us discuss about insert multiple row statement we can insert multiple rows in a single insert statement at a time we can insert multiple row values by grouping row values with open and close brackets and separating each row with a comma Now if you want to insert uh let's say hundreds of records and insert values into it it will take a lot of time and it in the at the same time it will become hectic if you specify the column names each and every time so in order to uh reduce that uh and insert the values quickly we use this multiple statement so let's say if I want to insert uh new values it's are two rows of employee ID 103 and 1,4 so the following query would be insert into customer uh that is the table name and within the brackets uh so before entering the values you have to mention the keyword values and you can directly enter the values now without mentioning the column names so as you can see I've inserted two rows and the data into into the table so let me just execute the statement and let us see the output so our our query has been successfully executed uh let me use the select statement to display the new values so as you can see uh the customer ID uh 2013 and 1,4 details uh has been displayed so in this way you can use the uh insert multiple statement uh to insert uh multiple rows into the table so just keep a note that make sure uh you insert the values accordingly in the order of the columns that you have taken in the table so what is my SQL update statement SQL update statement is used to update the column values in the existing table that means it is used to change the values present in one or more rows in an existing table now where Clause is used in the update statement to update the Matched rows that satisfies this condition that we have specified in the where Clause let us understand the syntax of SQL update statement the syntax is followed as update which is the keyword followed by that we have to mention the table name name and then set column name is equals to new value that is the value that you want to change from the previous value and after that you have to mention the condition that is where specify the condition to understand this in a better way let us take an example consider a table student which is having columns role number name age and city now if I want to update the value of City uh of the student who's having role number as three uh from Mumbai to Chennai so so the following query would be update student which is the table name set city is equals to Chennai where role number is equals to 3 now if you execute this statement this will be the output where the city which is uh previously uh Mumbai has been changed into Chennai so in this way you can use the update statement now that we've got an idea on what uh SQL update statement is and how it works let us jump into mysl workbench uh for the execution part as you can see myale workbench has started and on the left side we can view the tables that are present in our database simply code so I'm going to work on the employee table so let me just display the records that are present in the table for that I'll use the select command select star from the table that is employee so let us execute the statement so it will display the values of all the uh details of the employees having their employee ID employee name age their designation date of birth city and salary now let us look at the update statement first now if I want to update the salary of the uh employee who's uh having employee ID as let's say 106 to uh 35,000 now initially the employee ID uh 106 the employe is having the salary 25,000 now if I want to change into 30,000 the following query would be update table name that is employee use the keyword set mention the column name that you want to change now we are changing the salary set salary equals to from 25,000 to I'm changing into 35,000 where employee ID equals to 106 so let us execute the statement and see the output so as you can see our statement is successfully executed now let us use the select command to display the records so as you can see the employee ID uh 106 is having the salary as 135,000 it is being now changed into 35,000 from 25,000 so in this way you can use the update statement similarly uh you can update multiple column values as well let's say if I want to update the designation and the salary of the employee based on their employee ID for that the following query would be update table name employee set designation now if I want to change the designation of the employ uh lohit who's having the employee ID 103 from HR to senior HR so I'll mention senior HR as our new value and also I want to change the salary so put a comma salary equals to uh let's say I want to change the salary from 45,000 to 60,000 where mention the condition employee ID equals to uh 103 so let us execute the statement and see the output so our query has been successfully executed so as you can see the employee name lohit who's having employee ID 103 his designation has been changed to senior HR and his salary has also been changed into 60,000 so in this way you can use uh update statement to change multiple uh values that are present in the uh columns as well next let us discuss about update date statement in SQL update date statement is used to update the date and time values in the existing table uh all the data date and time value should be specified in the single codes and you can also mention the condition using the we Clause let's take an example let's say if I want to update the date of birth of the employees uh who is having employee ID 104 so the following query would be update table name employee set date of birth now I'm changing the date of birth of the employee uh ID who I 103 so I'm changing the date of birth as 1990 6 Jan 1st mention the condition where employee ID equals to3 so let us execute the statement and see the output so our query has been successfully executed now in the resultant uh table uh you can see the date of birth of the employee who's having employ Adas 103 has been changed to 1996 January 1st in this way you can use the uh update date statement to change the date values let us take another example now you can also update multiple rows present in the table let's say if you want to update the date of joining of all those employees who's having the designation business analyst for that the following query would be update table name that is employee set date of joining equals to uh I'm taking the date of joining as uh 1st January 2022 1st January 2022 now specify the condition where designation equals to business analyst put a semicolon and execute this statement now so let us see the output whether the uh values are changed or not as you can see uh the date of joining for all the business analysts that are present in the table for example uh for the employee ID 101 as well as 109 and 111 their date of joining has been changed to uh Jan January 1st 2022 in this way you can use the update date statement to uh change the values of date and time that are present in the table so what is SQL delete statement SQL delete statement is one of the data manipulation command DML that is used to remove rows from a table that means you will able to delete the existing records from the table now you can either delete a single row multiple rows or values from the existing table depending on the condition that is specified that means delete statement with where condition is used to delete the rows that satisfies the condition with where clause and the remaining rows are not changed also delete statement without where condition is used to delete all the rows from the table let us now understand the syntax of SQL delet statement the syntax is followed as delete from table name where condition here where condition is optional that means you can either use it or you cannot use it so let us take an example let's say if I want to delete uh the details of the employee from the employee table whose employee ID is one1 so for that the following syntax would be delete from employee where employee ID equals to one1 now if you execute this query it will delete the details of that employee who's having employe ideas 1. one in this way you can use delete statement to delete one or more rows from the existing table now that we've got an idea on what SQL delet is let us jump directly into the MySQL workbench and get into the execution part as you can see myale workbench has started and on the left side you can view the V various tables that are present in our uh database simply code so let us consider uh employee table first as an example table to frame SQL queries to perform various operations using the delet statement firstly uh so let me display the values that are present in the employee table for that I'm using the select statement select star from the table that is employee so let me just execute the statement so it will display the records of all the employees uh their employee ID employee name age destination date of birth city and salary so firstly uh let us see how to delete a single Row from the given existing table for that let me take an example let's say if I want to delete one of the employee details uh from the employee table whose uh employee ID is let's say8 for that the following query would be delete from table name that is employee where specify the condition employee ID equals to 107 so let us execute the statement and see the output so our query has been successfully executed uh let me use the select statement to display the records and see whether or not our record is deleted or not so as you can clearly see uh 107 uh record of the employee is missing from the table that means we have success sucessfully deleted the record of that employee who is having employee ID 107 let us now understand how to delete multiple rows from the existing table before that uh let me display the total number of Records present in the table so that we will have a clear idea on how many uh Records have been deleted after executing the query for that I'm using uh count statement select count use the star operator from on the table that is employee so we have total 14 records in the table so let us now execute this statement uh for that we will take an example let's say if I want to delete multiple employee details from the employee table whose designation is let's say business analyst so for that the following query would be delete from table that is employee where designation equals to business [Music] analyst so let us execute the statement and see the output so as you can see our query has been successfully executed let us again use the account statement to see the values that are deleted so as you can see the total count of the employees in the table has been changed to 11 that means there are total of three employees whose uh designation is business analyst and since we have specified the condition where we want to delete only those uh records from the table whose designation is business analyst so in this way you can uh delete multiple rows by using the where condition statement now similarly you can Del multiple records from the table using multiple conditions as well this can be done using various operators like and or between Etc so let us take an example for that uh suppose let's say if I want to delete the employee details uh from the employee table uh whose designation is let's say data analyst and uh their salary is less than 30,000 by using R operator for that the following query would be delete from table that is employee specify the condition where designation equals to data analyst or their salary is less than 30,000 so let us execute the statement now so our qu has been successfully executed and let us see the output for that I'm using the select count statement as well again so as you can see only one record has been deleted from the table that means there's only a record of the employee whose designation is either data analyst or their salary is less than 30,000 so in this way you can use uh multiple conditions to delete the records from table as well let us not take another scenario let's say I want to delete multiple employee details from the employee table whose salary is in between let's say 30,000 and 45,000 for that I'll use the between operator and the following query would be delete from the table employee where salary between is the operator that we have to use 30,000 and 4 5,000 so let us execute this statement and see the output so our query has been successfully executed uh let me use the count statement to display the number of Records now so as you can see previously we had uh 10 records in the table now it has been changed to four that means a total of six records has been deleted from the table wherein uh the uh salary of the employees uh ranging between 30,00 ,000 and 45,000 so in this way you can use the uh Delete statement to delete multiple records from the table by specifying the multiple conditions now we have seen how to delete a single record or multiple uh Records or even multiple records using multiple conditions in the existing table sometimes uh there might be a requirement to delete the entire table data uh to free up the memory or to allocate new data into the table for that uh we use the delete statement to delete the uh whole record from the table as well for that executing delete statement without the V Clause deletes the entire table data so make sure you're careful while using the delet statement because you'll end up deleting the whole table if you're using without we Clause let's say if I want to delete the remaining records of the table from the employee table I'll simply write the query as delete from table that is employee so let me execute this statement and see the output our query has been successfully uh executed so let me just display the records of in the table for that I'll use the select statement so as you can see only the columns have been present but the records that were present in these columns have been completely deleted uh using the delete statement so in this way you can delete uh all the records from the table without specifying the condition using the we Clause so what is SQL joint in relational databases the information you want to retrieve is often stored in various different tables in such scenarios you'll need to join these tables to view data in a much better way this is where SQL joint comes into picture SQL joints is widely used Claus in SQL essentially to combine and retrieve data from two or more tables based on related columns or you can say common fields between them now consider two tables here table one has three columns a b c and three records let's say for reference we'll take them as 1 2 3 similarly table two also has three columns BCD and three records 345 here I've taken a different color combination to represent values that are present in various columns now instead of querying each table every time to retrieve data I'll simply join these two tables and this will be the following resultant table also make sure when you're joining two tables it should compulsorily have a common column here C is the common field which forms the basis to join these two tables here why we use SQL join flexibility SQL join allows the user to access and manage records from more than one table easily let us understand with an example consider Mercedes Benz which is one of the leading car manufacturers in the world and let's say they want to access the records of the customers from the database now in the database let us take they have various different kinds of TBL such as customer table order table vehicle table now if they want to get the vehicle details of so and so customer XY Z for that they have to first query the customer table to get the customer ID now if once if they get the customer ID they have to query the order table to get the order ID and finally with the help of order ID they can get the vehicle details now as you can see this is a time taking process and hectic at the same time now instead of that I'll just simply join these tables which will allow the users to combine rows from two or more tables based on different types of conditions in this way you can access and manage your records easily data redundency SQL join allows uh the user to maintain data redundency as much as low as possible so that we can maintain the amount of data anomalies that is the duplicate values that are repeated in various tables in the database finally efficiency SQL join executes the query faster and shows the result much more quickly because instead of using various subqueries for each and every table individually we can just simply join uh two tables using a uh simple single query types of SQL joints SQL joints are broadly classified into four types they are inner joint outter join left join right join and additionally we also have cross joint which is not that significant uh in its usage because most of the times we use the first four joints now that we have gone through different types of SQL joints let us discuss each of them in detail firstly let us discuss about SQL inner joint SQL inner joint uh joins two tables based on a common column and selects records that have matching values in these columns now when the uh is applied for these columns the query checks all the rows of table one and table two only the rows that satisfy the joint predicate are included in the resultant table let us now understand the uh syntax of the SQL inner joint the syntax is followed as select table 1. column 1 table 1. column 2 table 2. column 1 and so on from table one in a join table two on table 1. column equals to table 2 column now inner joint syntax basically Compares rows of table one with table two to check if any anything matches based on the condition provided in the on clause and when the condition is met it returns matched rows in both tables with the selected columns in the select Clause let us now discuss about the SQL outer join SQL outer join or else it is called as SQL full join or full outer join is used to get all the rows which are present in both the tables that means it will return all the records which are present in either left table that is the table one or the right table that is table two even if there are no matching records present in both the tables let us now understand the syntax the syntax remains same that is Select table one. column 1 table 1. column 2 and so on up to table 2. column 2 from table one full outer join is the keyword that we use here table two on table 1. column equals to table 2. column now here you have to mention the uh the same or the similar column name in the uh after the on predicate statement next we have SQL left join left outer join also known as left join results in a table containing all the rows from the table on the left side of the joint that is the first table and only the rows that satisfy the join condition from the table on the right side of the joint that is the second table any missing values uh for the rows from the right table in the result of the joint tables are represented by null values let's look at the syntax the syntax is followed as select column list that is the column that you want to uh display in your table now make sure you maintain the syntax of the column list that is the table name do column name otherwise it will throw an error so let me just repeat the syntax the syntax is Select column list from table one the uh keyword that we use here is left joint table two on table table 1. column equals to table 2. column so in this way you can use the left joint to display the records next finally we have the uh SQL right join now right join or right outer join is uh opposite to that of the left outer joint now it follows the same rules as the left joint and the only difference is that all the rows from the right table and only the conditions satisfying the rows from the left table are present in the resultant table that means it will return all the rows from the right table and all the matching records that are present in the left table the syntax Remains the Same that is Select column lists that you want to display in your table from table one right join is the keyword we use table two on table 1. column equals to table 2. column now to sum up all these different SQL joints on how they work I've taken a graphical representation of two different tables here which will help us visualize the real-time working of SQL joints now consider table one here which is columns A and B and Records uh two different records let's say record zero record one and similarly we have table two which have uh columns A and C and two records that is record zero and record two now if you apply left join and by the definition of left join it will only uh return return those values which will which are present in the left table and the matching records that are present in the right table now here the uh match Ming value that are present in both the tables is zero and the values that are present in the left table is one and if you consider the right joint it will match the records from both the tables and it will display the only the values from the right table that is the reason it is being displayed uh the record zero and the record two and if you look at the inner join it will match the values from both the tables now the common value that is present in both the tables is record zero and if you finally look look at the outer join it will return all the uh values from both the tables irrespective if they're matching or not that is the reason uh all the records that is record Z record one and record two are being displayed and with that we have come to the end of today's session that was all about SQL joints stay tuned uh for more upcoming videos wherein we'll execute all these types of joints in MySQL workbench using various examples so what is SQL iner joint using the in a joint the tables are combined on the basis of a condition also known as The Joint predicate this condition is applied on The Columns of both the tables on either side of the joint Clause the query checks all the rows of table one and table two now it will display only those values that satisfy the joint predicate in the resultant table that means it finds the matching values or the uh matching records that are found in both the tables for example I have a table a which is records 1 2 3 4 and in table B I have records 3 4 5 6 now if you join these two tables now the resultant output output will be three and four because these are only two records that are present in both the tables that is the table is matching the common values that are present in both the tables let us now understand the syntax of a scale inner joint the syntax is followed as select table 1. column 1 table 1. column 2 table 2. column 2 and so on from table one inner join is the keyword we have to use table two on table 1. column equals to table 2. column here after the join uh expression we are mentioning The Columns uh from which we want to match the both the tables now that we have understood uh what is SQL in a joint let us jump into MySQL workbench and execute it with the help of an example as you can see MySQL workbench has started and on the left side you can see the database that is simply code and the various tables that are present in our database simply code like we have customer table Department table employee table orders table Etc now to perform the SQL inner join operations we'll use the employee table Department table and the projects table now let us display the values that are present in the table for that we have to use the select statement or you can directly click on this table icon here so first let us display the values present in the employee table the employee table has the following Fields such as employee ID employee name age designation the city they belong to salary date of joining and their Department ID next let us display the values that are present in the department table the department table has two fields that is Department ID and Department name and finally let us display the values present in the projects table the projects table has the project ID employee ID the project name and the project manager now let's say we have a query here which says to retrieve the employee details and the department they are working in now before we write a query we have to check uh for all those tables in which these informations are present in for example I can get the employee details from the employee table such as their employee ID employee name the designation salary and so on and also I can get the Department details from the Department table now the expected uh query or the resultant table is based on these two tables now in order to retrieve the records from these two different tables I need to connect them in other words we have to join these two tables this is where the inner join comes into picture with the help of inner join we can connect these uh two tables and uh we can retrieve all those matching records from those tables now the following query uh would be for the inner join is Select mention the column names that you want to display now I want I'll display the employee ID so employee do employ employee ID I'll also display the employee name so employee. employee name I'll also display the uh employee designation so I'm taking employee. designation now I'll also retrieve the information from the Department so I'll have to mention the department table here as well so department DOT Department ID right now the query is continued as from the table table one that is employee I'll just write in the next s uh next sentence from employee inner join is the keyword we have to use here and mention the second table that is department on which is the keyword and mention the condition on the basis of which you are connecting these two tables now I'm connecting the employee and the department table with the with the help of the department ID so I'll mention employee dot Department ID equals to department DOT Department ID so let us now execute the statement and see the output so as you can clearly see it is displaying the values of employee ID ID employee name the designation and the department ID as well now as you clear clearly you can see that the join condition is specified in the inner join Clause after the on keyword as the expression now for each row in the Pro uh in the employee table the query finds a corresponding Row in the department table that has the same matching values based on which we have mentioned here that is the department ID now if there is a match between two rows in both the tables it returns all those rows that contains columns that we have specified for example we have specified employee ID uh name designation and the department that they belong to so in this way you can use the inner join to get the records from both the tables also you can uh get the Department name as well by uh mentioning the department uh names in the uh query the department name dot department. Department name now this will display uh the records of the department name that are present in the department table as well so as you can see uh there are total of uh six records and the employee details and the department names that they are working in and also if you uh notice there are only six records that are present in a resultant table when we have uh a total of 20 records that are present in the employ table that's because the inner only matches those records from those columns that are having matching values from both the tables and and also the condition that we have specified uh in our in a joint statement similarly let us look at another query which says to retrieve the employee details project they're working on and the project manager assisting them now for this to get the employee details again I'll again use the employee table and to get the details of the uh project uh name and the project manager's name I'll use the project table so the following query would be select e. employe ID now here I've have taken the aliyas name for the tables that is you can mention a temporary name for the tables that you have chosen so for employee table I'm taking as e and for projects I'm taking as p in order to save time and uh and to save time we'll write in this way so the quer is followed as select e. employe ID e do employee name p. project name p. project manager from the first table that is employ employe e inner join projects we have taken the name as p on e. employe ID is equals to P do employee ID which is the common column from which we get the matching values from both the tables let us now execute uh the statement and see the output so as you can see we can uh we are able to retrieve the employee ID employee name project name that the employee is working on and the project manager that uh have been assigned to these employees and if you notice uh carefully here like some of the employees are having employee like 1,2,3 1,86 uh they have no project names assigned to their uh to their names that this is because even though we have all the details of employees uh in the employee table we do not have the data that that is being stored in the projects table that is the reason uh only a limited number of records are being shown and as as per the definition of inner join it will fetch only those records which have matching values from both the tables so in this way you can join uh two or more tables using the inner join statement and with that we have come to the end of today's session that was all about SQL inner joint now before getting into the execution part let us just quickly discuss what is skl outer joint the outer joint statement returns all those records which are present in either the left table or the right table table now for example if you consider uh the table here a which has records 1 2 3 4 and the table B which has records 3 4 5 6 now if you apply the full outer joint to these two tables it will display all the records that is 1 2 three and up to six now let us discuss the syntax of scale outer joint the syntax is followed as select table 1. column 1 table 1. column 2 table 2. column 1 and so on these are basically columns that you want to display in your resultant table from table one full outer join table two on table 1. column equals to table 2. column now as far as my skill concerned this syntax is not applicable now if you're working on other databases like post view SQL and Microsoft SQL Server you can apply this uh syntax but if you're working on mycale workbench there is a different syntax which I'll be discussing in a while so so now that we have understood what is SQL a join let us jump into MySQL workbench for the execution part as you can see MySQL workbench has started and on the homepage uh on the left side you can view the databases and tables now I've created uh certain tables U time for time being so let me just display the values that are present in these tables now in order to execute theale outage join will consider the employee table as well as the project tables so let me just display the uh values present in the employe table I'll use the select operator to display those values select star from employee so it will display the records of all those employees having uh employee ID employee name age their designation City salary date of joining and their Department ID now similarly I'll display the records of projects let me just take another tab I'll use again the select state to display the records start from projects now if you execute the statement it will display the uh project ID employee ID project name and the project manager details now let's say if I want to fetch the details of all the employees from the employee table and the project details they're working on from the projects table I'll have to use the full joint to retrieve information from both the tables now as discussed earlier the full outer join keyword is not applicable to my SQL database so I'll have to implement a new logic here which is quite simple as you know the full outer join is the result of combining the left and right table so we'll first use the left join then we'll use the right join and finally we'll use the union operator which is used to combine the resultant set of two or more select statements and uh and execute it in a single statement by the way uh we haven't executed left and right joint yet so stay tuned for the upcoming videos on both these joints as well if getting your learning started is half the battle what if you could do that for free visit scaleup by simply learn click on the link in the description to know more so the following query would be let me just take another tab again so the following query would be select uh now mention all those column details that you want to uh display in your resultant table so I want to display the employee ID employee name project name and the project manager name as well so you have to mention the column names in the format of table name do column name so I'm taking the uh alas name for the employee table as e so e do employee ID comma e do employee name now for the projects table I'm taking the alas name as P so P do project name comma P do project manager name from the table that is um project tables sorry the first table is employe right so we'll mention the employee now use the left join keyword left join on projects on now you have to mention the condition uh on the basis of which your connecting these two tables now if you look at the two tables we have the employee ID that is present in both the tables so that will be the uh common attribute to specify this condition here so e do employee ID equals to p. employ ID now I'll just copy paste this uh same statement and uh instead of using the left join I'll use the right join keyword so let's just replace the left join keyword with the right join and I'll mention the union operator keyword to join these two tables so that's it guys uh this is how you can uh use the full outer join in MySQL workbench now let us just execute this statement and see the output uh so there's a bit of error let me just check it once so I forgot to mention the alas name here for both the tables so that is why it is throwing an error so as you can see this is our output and you can clearly see that the record from both the tables that is the left table which is employee and the right table which is projects are being displayed here now unlike the inner join which only fetches those records which have matching values in both the tables full outer join retrieves all the values irrespective of whether they're matching or not for instance if you look at the employees having employee IDs here one 112 13 1006 and as well as 118 we have uh their information present in table one that is the employee table here but we do not have their information in the projects table here and similarly employees having employee IDs like 1,22 1,23 and 1,24 their information is present in the projects table but it is not in present in the first table that is employ employe you can see only the employee details having their employees ID up to 1020 are being displayed in the employee table also if you notice the resultant table here guys uh some of the employees like K kti Varun and uh Nya have no uh projects assigned to their name that's why we have null values in the project name column that's because though the records are present in the uh employees table but their employees IDs are not present in the project table that means all these employees are yet to be assigned a new project so if the values present in both the tables that does not match it will simply just return null values similarly we can see the other three projects that are present uh in the table but there is no sign of employee details here be their employee ID or employee name and it will just simply return the null values again here because we do not have the employee information in the first table employee even though their IDs are mentioned in the project table and hence it will only return null values now suppose this is just a demo table having around 20 or 25 records but if you consider a company's database in real life it'll have thousands of Records in order to access and manage various database tables and fetch information from those tables all together it becomes quite difficult right so this is why we use full join to connect the tables and retrieve each and every information from them and in if any any column has null values they can just simply update those rows or even delete them as per the requirement let's just have a quick recap on what is SQL left join so SQL left outer join also known as left join results in a table containing all the rows from the table on the left side of the joint that is the first table and only the rows that satisfy the joint condition from the table on the right side of the joint that is the second table the missing values for the rows from the right table in the resultant table of the join are represented by null values for example if you consider here two tables that is table a and table B which is values 1 2 3 4 in table a and 3 4 5 6 in table B now if you apply left joint for both these tables it will display only the values from the left table that is 1 2 and the matching values from the right table that is 3 4 so the resultant uh table will be 1 2 3 4 let us now understand the syntax of the SK a left joint statement the syntax is followed as select column lists the these are basically the list the columns that you want to display in your resultant table from table one left join which is the keyword we are using here table two on table 1. column equals to table 2. column now here you have to mention the uh a common attribute on the basis of which you're joining these two tables so now that we've understood about SQL left joint let us now jump into MySQL workbench to to execute this using various examples as you can see my scale workbench has started and on the homepage on the left side in the schema section you can view the databases and the tables that are present so in this case we have the database simply code and various tables in that such as customer Department table employee orders and Etc now in order to execute this uh left join statement we'll we'll be going to use customer and the ORD table statement tables so let me just display the values from both these tables for that I'll use the select Clause select star from customer which is the table name so let us just execute so as you can see the customer table has various Fields such as customer ID customer name age address and their phone numbers similarly let us go to another Tab and display the values that are present in orders table select star from the table that is order so let us execute the statement so as you can see in the output uh the orders table has various Fields such as product ID customer ID product name that is the product they have purchased quantity the price of the product and the purchase date now let's say if I want to fix the details of all the customers and the product details that they have purchased I'll have to connect these two tables that is customer and the orders table to get the resultant table now for that I'll use the left joint statement so let me just take another Tab and execute the statement so the following query would be select mention the column names that you want to fetch in your resultant table so I'm going to take here alas name for both the customer and the orders table as C and O that's because uh instead of writing the full name of the tables each and every time you can just simply uh use an alas name which is a temporary name which will save time as well so for customer uh table I'm taking a c so I want to display the customer idid so C do customer ID I want to display the customer name customers address so C do address now from the orders table I want to uh mention the products name that they' have purchased the quantity price and purchase date so we have taken o as the allas name for our table so o do purchase name sorry product name that they have purchased I also want to display the quantity so o. quantity it's price as well o do price and the purchase date o do purchase date from the table first table that is customer mention the allias name c left join which is the keyword on the second table that is ordos o on mention the condition on which you're joining these two tables so as you can see we can find the customer ID and the customer ID in both the customer and the orders table so we'll mention the customer ID as the column common attribute here so C do customer ID equals to O do customer ID so let us just execute the statement and see the output so as you can see it will display the customer ID customer name address product name quantity price and purchase date of the uh customers in our resultant table now let us understand what exactly uh the left joint statement is doing here firstly the database checks each and every row of the table and looks for a match in the right table based on the related columns now if a match is found the data from the right table is added to the corresponding row of the left table now you can see uh customers having customer ID 1 1 1 0 1 0120 1 0 13 0 and 10 1340 we have the details present in the first table as well as the second table that is orders so it will display all those records and then if there are multiple matches now in this case we can see a customer name AJ is having two different Records who has bought sofa set and a TV so the rows in the left table are duplicated here that is the records are repeated in the resultant table in order uh to include all the records from the right table so now uh for all the columns that you have fetched from the right table that is orders which is not satisfying the join conditions then the values present in the columns will be resultant as null that means if there's no matching value found from the right table it will simply retain the row from the left table and inserts null into the corresponding Columns of the right table now let me just explain this now if you see here the customer uh customers like adash and PR who are having details in the first table that is uh customer table but they do not have the details in the Pro orders table so there's no sign of uh any their customer ID in the order table that's why uh we do not have their uh details present in the uh resultant table so it will just show the null values here so basically left joint is a combination of inner join we can say because it just checks the matching values from both the tables first and then it will check for any additional or extra values that are left out from the left table that is the table one now you might have a question that when to use and why to use the left joint like there are times when you want to keep rows from the first table that don't have any corresponding records in the second table now if you in this case if you consider our example we might want to see information about all the customers in a resultant table even if they didn't place any orders so in that case we'll use the left join which will be used to combine data from two tables so that all the rows from the left table are included in the resultant set even if there is no matching value from the right table it follows the same rule as the left joint and the only difference is that all the rows from the right table and only the condition satisfying rows from the left table are present in the resultant table so basically the right join returns all the records from the right table and only the matching records from the left table satisfying the condition for example let's say we have two tables table a and table B and when the Left Right join is applied to the two tables it would give all the records from the table B and only the matching records from the table a that is table one let us now look at the syntax of SQL right joint the syntax is same and uh it is followed as select table 1. column 1 table 1. column 2 table 2. column 1 and so on these are basically the columns that you want to display in your resultant table from table one right join is the keyword we use here table two on table one. column equals to table 2. column now that we've understood what is SQL right join let us jump into MySQL workbench for the execution part choose from over 300 in demand skills and get access to 1,000 plus hours of video content for free visit scaleup by simply learn click on the link in the description to know more so myale workbench has started and on the left side you can view the database simply code which has various TBL such as customer Department employee orders now for time being I've already created and inserted values into all these tables and to in order to execute the right join we are going to use the customer table and the orders table so let me just display the values that are present in these tables for that I'm going to use the select statement select star from the customer table so as you can see the customer table has various Fields such as customer ID customer name their age their address and the phone number similarly let us just uh retrieve the records from the orders table as well select star from orders table so the orders table has of various column such as product ID customer ID product name that they have purchased quantity price of the product and the purchase date now if I want to join these two tables I'm going to use the uh right join statement so the following query would be select now I'm going to use a alas name of both these tables for customer table I'm going to take as C and for orders table I'm going to take as o now I now I want to display uh The Columns from the customer table uh only so the tables are the columns are customer name I want to display their address as well so mention the column names customer name c do address now from orders table I'm going to display the product name that they' have purchased quantity price and purchase date so o do product name comma o do quantity comma o. price and finally o. purchase date from the table one that is customer mention the keyword write join mention the second table name that is orders on mention the condition on which you're joining these two tables now now as you can see we have customer ID as well as uh the customer ID present in both the tables customers and orders so we'll be taking the a customer ID here as the common column on c. customer ID equals to to O do customer ID let us now execute the statement and see the output so there is just an error let me check oh okay I didn't mention the alas name here so mention the alas name for both the tables I guess it will display now name okay so as you can see this is our resultant table which uh basically displays the various columns customer name their address product name quantity price and purchase date let us now understand what exactly uh the right join is performing here now basically the right joint Clause starts selecting the data from the right table and matches it with the rows from the left table the right join returns a resultant table that includes all the rows in the right table whether or not they're having matching rows from the left table now if a row in the right table does not have any matching rows from the left table The Columns of the left table in the resultant set will have null values now clearly the SQL statement would return all the rows from the orders table here as you can see we have all the details like uh AC TV phone cooker car sofa set and phone bike Etc in our orders table so it it is basically retrieving all the data from the right table and only those rows from the customer table where the join condition is being satisfied now it will only display the matching values here because can you as you can see we have customer ID 10 1 1 0 1 0120 1 0 13 0 so basically we have one total six records and we have them in the resultant table as well now if you consider the resultant table here it is showing null values for some of the customer names and address because though we have the product names mentioned in the orders table here for example the product name car sofa set bike and the customer IDs are 10141 10 1 160 and 10 1 190 but we do not have their uh details present in the customer table and that is the reason we that it is showing us null here so in this way you can use the SQL right join statement now you might have a doubt that when to use and why to use the right joint statement again it will depend on your own requirement for instance if you want to keep the records from the second table that do not have any corresponding records from the first table in that case we use the uh right join also another point to be noted here that the table that you have mentioned to the right side of the right join keyword that is we have taken your orders right so it will give the highest priority to this table and it will display all the records from the orders table here even if they do not have any matching values similarly just take another scenario here I'm just interchanging the tables here like the first table I'm taking as orders and the second table I'm taking as customers so when we execute this statement it will will display the records from all the records from the customer table that is the first table and even if they do not have the matching values it will just display the null values here that's because now if you consider adash and pray we have their details uh in the employe in the customer table but we do not have their information present in the orders table and that's the reason we can see that only their details are present but they do not have the orders details that we have present here so in this way you can use the right SQL joint statement as well so with that we have come to the end of session guys that was all about the SQL right joint statement to learn more about the SQL Concepts and about SQL joints you can check out our video on our Channel complete SQL tutorial uh 2022 for beginners which will help you out so what are keys in SQL SQL Keys plays an important role in relational databases it is used for identifying unique rows from table a key is a subset of columns in a table that allows a row to be uniquely identified so a key can be more than just a column and every Row in the table will have a unique value for the key or a unique combination of values if the key consists of more than just one column now as you know databases are used to store massive amounts of information which is stored across multiple tables now each table might be uh having more than thousands of Records or rows now needless to say there will be many any duplicate rows with redundant information so how do we deal with that now how do we manage these records that are storing only unique data now for that we might need a combination of one or more columns in the database table to uniquely identify a row in a database so in that case we use the SQL Keys now SQL Keys creates constraints that can be used to enforce data Integrity in SQL now as you know a database must a to certain properties to maintain integrity and quality of the data that is storing keys and constraints are rules that Define what data values are allowed in certain data columns they are an important database concept as well as for SQL and are part of database schema definition defining keys and constraints is part of database design process and ensures that data within a database is reliable and maintains its Integrity let us now understand why we use SQL keys SQL Keys identify each record separately and uniquely now a key is used in the definition of various kinds of Integrity constraints and a table in a database represents a collection of Records or events for a particular relation now since there are thousands and thousands of such records some of which may be duplicated now in order to uh identify these records uniquely and separately we need SQL Keys SQL Keys allows user to establish and identify a relationship between the table tabls as well and finally SQL Keys access or manages the store data quickly and smoothly now that we've understood what SQL keys are and why we use them let us now discuss some of the various types of keys in SQL SQL keys are broadly classified into various types such as primary key Super Key candidate key alternate key composite key foreign key now let us discuss each and every type of key in detail with an example firstly let us look at what exactly is primary key the primary key is one of the most important and commonly used SQL keys in the databases the primary key is in SQL is a single or a group of fields or columns that can uniquely identify a row in a DI putting it simply it is a column that accepts unique values for each row therefore whenever you use the insert into command to insert new values in a table the value for a primary key column or column need to be unique now primary key advantages is mainly it uniquely identifies each row of a table also it gets a unique index for each primary key column that helps with faster access now there are properties uh which are helpful of SQL primary key they are it enforces uniqueness by not accepting any duplicate values and a primary key also uniquely identifies each field and can take only one primary key key for a table now primary key column cannot accept null values as well let us consider an example here consider student table which is having various Fields such as student ID role number name class section age and address now if you look at the table clearly student ID can be taken as a primary key here also you can take the role number but since we are taking the records of all the students in a school that's why we are taking the student ID as the primary key now if you take the records of the students for a particular class then you can take the role number as a primary key as well let us now understand the syntax of primary key Now to create a new table with a column defined as a primary key you can use the keyword primary key at the end of the definition of that column and the syntax is create table table name and within the parenthesis mention the column names and its column types and finally mention the primary key inside the bracket so for example if I want to create a table which is having ID last name first name and age in that I'm going to use the primary key here as ID so in this way you can use the primary key in SQL next let us discuss about the candidate key a candidate key is defined as set of one or more columns that can identify a record uniquely in a table so basically candid key is a Super Key with no repeated attributes by the way uh we'll discuss about the super key in a while uh the primary key should be selected from all the candidate keys and every table must have at least a single candidate key and a table can have multiple candidate keys but only a single primary key now candidate key shouldn't have redundant attributes that means it should not have any duplicate values in the table now unlike primary key the attributes of candidate key can contain null values and as discussed a table can have more than one candidate keys and it is also called as the minimal Super Key guys uh that's because uh we select a candidate key from a set of super Keys such that the selected candidate key is the minimum attribute required to uniquely identify the rows in a table so let us understand this with an example consider the students table here which is having various columns such as student ID RO number name age address and contact now as you know uh we can take the student ID as the primary key and with that we can take the role number as well as the contact of the students as the candidate key here because all these three columns alone can uniquely satisfy the condition of the candidate key here so in this way we can use the candidate keys in s next let us discuss about alternate key in SQL alternate key are subset of candidate keys that can also uniquely identify tles in a table which are not chosen as primary key for example consider the employee table here which has columns employee ID employee name job department number pan number AAR number and U now if you look at the table uh The Columns that can uniquely identify each and every record are basically employee ID pan number a number and U number now since we have taken the employee ID as a primary key and the rest of all the columns that are not chosen as a primary key are consider to be alternate keys so in this case we can take the pan number AAR number U number as our alternate Keys Next let us discuss about the super key in SQL super key is another important key that is used day-to-day usage in SQL databases a Super Key or a simple key is a combination of all possible attributes which can use uniquely identify the row or tles in a table that means that a Super Key may have some extra attributes which isn't necessary for uniquely identifying the rows in the table a Super Key is a in SQL is a super set of primary key candidate key and Alternate key that means basically it is a combination of all the keys such as primary candidate and Alternate Keys as discussed super key will have additional attributes that are not needed for any unique identification and finally super keys with the least number of attributes form the candidate keys so let us take an example again consider the employee table here and if you look at the possible keys that will be for super keys are employee ID p number employee ID a number employee ID u p number AAR number P number u a number U employee ID pan number as well as U and finally p number AAR number and U now all the above keys are able to uniquely identify each row so each of these Keys is a Super Key key now again in this example we have like more than uh six Super keys but all of them cannot become a candidate key here only those super Keys would become a candidate key which have no redundant attribute for example uh if you take employee ID pan number this key cannot be considered as a candidate key because uh when we take the subset of this key we get two attributes that is employee ID as well as the pan number now each of these attributes is basically a candidate key so it is a minimal super key but hence this key is not a candidate key and finally let us discuss about the foreign key in SQL a foreign key is a column or combination of columns that is used to establish and force a link between the data in two tables to control the data that can be stored in the foreign key table in a foreign key reference a link is created between two tables and the column or columns hold the primary key value for one table and are referenced by the column or columns in other table now this Colum column becomes a foreign key in the second table the table with the foreign key is known as the child table and the table with the primary key is known as the referenced or parent table now as foreign key has bought the referential Integrity in SQL uh which means that it requires that a foreign key must have a matching primary key or it must be null this constraint is specified between two tables that is parent and child and it maintains the correspondence between the rows in the tables it means that the reference from a row in one table to another table must be valid now the foreign key uh helps in maintaining the data Integrity of the table and allows easy navigation between two instances or attributes it is also used in Esq to make the database data consistent and it is also used for the prevention of any action that may result in destruction of relation between these two tables let us understand this with an example now consider two tables here employee and Department the employee table has employee ID employee name job department number pan number other number and UN number and the department table has employee ID and the department name now clearly the employee ID which is the primary key in the first table employee is acting as a cross reference for the Department table that is uh the employee ID acts as the foreign key here for the Department table so in this way you can use the foreign key in SQL and similarly we have some other types of uh keys in SQL which are not that significant in its usage the first one being is uni key a uni key is same as a primary key with the difference being the existence of one null value in a table field or a row for example consider a student table having student ID role number and its name now for some reasons if the student is leaving the school then his role number might be deleted although his student ID will be preserved for further assistance so since there's a null value in role number it can be considered as a unique key and student ID can be considered as a primary key similarly we have composite key a composite key is a combination of two or more attributes that can together uh can uniquely identify a tle in a table and that brings us to the end of today's session uh that was all about uh the SQL keys so let us just quickly recap what we' have discussed uh the different types of keys so to summarize this I've created a table here employee ID employee name AAR passport department ID which is the table one that is employee table and we have Department ID and Department columns in the second table that is Department table now here clearly employee ID is the primary key since it uniquely identifies each and every record in the table and employee ID a number password can be taken as candidate keys because they are set of more than one primary keys since so they can be considered as the candidate key here now alternate Keys which are other than the primary key that is employee ID so I'm taking AAR card and passport as the alternate Keys here now the combination of all these keys that such as primary key candidate key alternate key forms a Super Key now I'm taking the passport as the uni key since not everyone has a passport to their name so I'm taking the passport attribute here as the unique key and finally the foreign key is the department ID as it points uh to the reference to the second table that is Department table so in this way you can use the SQL Keys accordingly now we have Akash uh who is basically from a non-technical background and wants to upskill his career as a business analyst and for that he's learning SQL now while learning these SQL concept Concepts he's unable to understand the aggregate functions in SQL so he turns up to his friend Rohan for help now Rohan is already a certified data analyst uh starts explaining him about the aggregate functions in SQL the aggregate functions in SQL are used to perform calculations on multiple rows of single column and returns a single value now in SQL each query delivers filtered results of groups of values as well as field values now SQL has these aggregate functions that can be used to summarize these enormous amount of data that is stored in the table now for an entire group or table this function can generate a single value they work with group groups of rows and returns all the possible results based on these fields or columns these aggregate functions are basically used with the uh Group by and having clause in the select statement the group by Clause basically divides the result set into groups of values and aggregate functions returns a single value for each groups that means the group by basically identifies all the records that are present in a particular field or column and combines them into a single set now that we have understood what are aggregate functions let us go through the various aggregate functions that are present in SQL now in SQL there are broadly five types of aggregate functions they are count sum average Max and minimum let us discuss about them in detail firstly let us discuss about the count function the SQL count function Returns the number of rows in a table satisfying the criteria specified in the we Clause it sets the number of rows or non-null column values only let us look at the syntax the syntax is followed as select count which is the keyword column name from table name where condition Group by column and order by column next let us discuss about the sum function in SQL the sum function Returns the total summation of the value of a specified column value some performs only on numeric columns only sum does not consider null values it has optional arguments like all distinct all keyword is used to take all the values in the specified column now if you specify distinct keyword it will remove all the duplicate values or the Redundant values let us look at the syntax the syntax is followed as select some column name from table name where condition Group by column and order by column next let us look at the average function the SQL average function calculates the average value of columns of numeric types it Returns the average of all null values and the syntax is similar to that of count as well as the sum instead of the count and uh the sum keyword you have to replace it with the average keyword and the syntax is followed as select average column name from table name where condition you can also specify the group by and the order by function as per your requirement here as well next we have the max function the SQL Max function is used to find the maximum value or highest value of a certain column or expression this function is useful to determine the largest of all the selected values of a column and the syntax Remains the Same which is Select Max is the keyword we use here column name from table name you can specify the condition as per your requirement using the V clause and you can use the group by function as well and finally that brings us to the minimum function which is opposite to that of Maximum function and the aggregate SQL minimum function is used to find the minimum value or the lowest value of a column or an expression this function is useful to determine the smallest of all the selected values of a particular column or a field and the syntax Remains the Same Again instead of uh in instead of Max you have to use the minimum keyword here and the syntax is followed as select minimum column name from table name where condition and group by condition as well so now that we've understood all these different types of aggregate functions let us jump into myql workbench for execution part as you can see MySQL workbench has started and on the homepage to the left side we have the simply code database which has various table such as customer Department employee orders and Etc now as far as the SQL aggregate functions are concerned we we are going to use the resturant orders table here so let me just display the reord that are present in this table for that I'm going to use the select statement select star from table name that is restaurant orders so as you can see the table has various columns such as order ID order date item name quantity and price by the way guys I've have taken this data set from kaggle so if you want to work on the same data set we'll leave the link in the description below so you make sure you check this out so first let us discuss about the count function here so if I want to count the total number of records that are present in this table I'll use the count statement here as the following query would be select count use the asri operator because we want to display all the records from the table that is restaurant orders so let me just execute the statement and see the output so as you can see there are a total of 37 records that are present in this table let's take another example now let's say if I want to find the total customers that are present in this table so for that I'll use the order ID as the reference that's because now if you consider uh for any customer we'll get a different order ID when he places an order right so for that I'll use the order ID so the following query would be select count now I'll use the distinct operator here so that we'll have the unique values order ID from the table that is restaurant orders so let me just execute the statement and we'll see the output so as you can see there are total of 48 customers that are present in the table who have placed orders on various items now let's take another example let's say if I want to find the total number of orders of each item that is present in the table then the following query would be select take the column it name that is item name now I want to count all the records that are present so I'm taking the count operator from table name that is restaurant orders now I want to group all the identical values that are present for each of the orders that are present so I'll take the group by function here item name and similarly I'm using order by so that I want to display the orders in their alphabetical order so item name so let me just execute the statement and see the output so as you can see it will display the total number of records that are present on each of the item that the order has been placed now we have a total of uh let's say four records of Alo GOI and eight records of Bombay Alo and so on and ex Etc so let me just uh verify the statement and see uh if the condition that is executed is true or not so I'll use the select statement select star from restaurant orders where item name equals to uh we'll see the uh records of Bombay Alo so let me just copy this field and see the output so as you can see uh we have a total of 1 2 3 4 5 6 7 and 8 so clearly we have a total of eight records that means our count function has been successfully executed so in this way you can use the count function in SQL you can also use the we condition here as per your requirement let's say if I want to display the list of all the items and the total number of order of each and every item whose price is less than $3 and for that I'll use the wear statement so this syntax remain same we'll just use the we condition here where price of the item mention the column is less than $3 so let's just execute the statement and see the output so it will display all those records who's having uh the price less than $3 so let's just verify for example uh we'll take the garlic none here as our example and and uh let's see if this condition is satisfied or not let me just copy this field and I'll paste it here and let us execute the statement and see the output so as you can see that we had four records of garlic none and uh it is being presented here and its price is also 2.95 Which is less than three let us not discuss about the minimum as well as the maximum function in uh aggregate function now let us take an example for that let's say if I want to find uh those items which is having the least amount of price so for that the following query would be select minimum is the keyword and within the brackets mention the column that is price from the table that is restaurant orders so let us select the statement and see the output so as you can see the minimum price of the item in the table is 0.5 so let us take another example here now let's say if I want to find all those items uh in the restaurant order table which is having the cheapest price so for that I'll use the query as select display the item name and its price from the table that is restaurant orders where price equals to now we'll write a sub query here using uh the minimum statement where select minimum price from the table that is restaurant orders so let me just execute the statement and see the output so it will display all the records uh in the restaurant order table and the item details which is having the least or cheapest price for example mango chutney onion Chutney mint sauce and red sauce is having the least price that is 0.5 now we can see there are duplicate values in our resultant table so I'll just take the distinct keyword so that it will dominate all the uh redundant values in the resultant table so let me just execute the statement so as you can see these are the four uh items which is having the least price now similarly you can find the maximum uh price of the order or the item that is present in the uh restaurant order table for that I'll use the max keyword here so let me just execute the statement and see the output so these are the three items uh which is having the maximum or the highest price in our table that is tanduri King prawn masala King Brun balti and King Brun shashlick which is having the price 12.95 which is the highest so in this way you can use the maximum and the minimum function to find out the highest and the lowest value that is present in your table let us now discuss about the sum function in aggregate functions now basically the sum function is used to calculate the total sum of all the records that are present in a particular column let's take an example let's say if I want to find the total to amount that the restaurant has received on a single day then the following query would be select sum now if you look at the orders table uh we have different quantities for each item right so the price will uh so the price will be basically price into quantity that will be the total value of the item so I'll take select some price into quantity from table that is restaurant orders so we have q capital in the record so we'll take as Q as capital so let me just execute the statement and see the output so there's a spelling mistake here let me just correct that and let us see the output now so as you can see it will show me the uh total sum of all the items uh that were placed on that particular day and the value is 89.9 now you can name the column as for your wish as well so I'll just take it as total amount so in this way you can use the uh sum function as well let us now discuss about the average function now average function is also similar to that of some but basically it calculates the average of all the items or the records that are present in the table now let's say if I want to find the average price of all the items that are present in our restaurant orders table then our following query would be select average price from Resturant table so let us see the output so as you can see the average price of all the items that are present is $ 5.04 so in this way you can use the average function as well let's take another scenario for average price where we'll use the wear condition let's say if I want to display all those items that are having price above the average price of all the items that are present in the table then the following query would be select I want to display the item name so I'll take the item name column their price from the table that is restaurant where price is greater than I'll write a subquery select average price from the table that is restaurant orders so let me just execute this statement and see the output so basically these are all the items which are having price more than uh our average price that we have calc Cal ated earlier that is 5.09 so in this way you can use the V condition as well as for your requirement but there's a point to be noted here that the average and the sum function basically calculates only those fields which is having numerical values whereas the count U minimum and maximum function can uh calculate the values of various data types such as character string as well as the numerical values so what are SQL functions we know that a fun function is basically used to perform some particular task and it returns zero or more values as a result now functions are useful while writing escale queries also which can be applied to work on single or multiple records of a table now depending on their application in one or multiple rows scale functions are generally categorized into aggregate functions and single row functions which is Scala functions now we have already discussed about aggregate functions in our previous videos so if you want to know more about it make sure you check that out on our Channel as well well now coming back to scale of functions now unlike the aggregate functions which return a single value after performing calculations on a group of value the scalar functions are a bit different in its Usage Now the function which returns only a single value from a input value is known as a scalar function the scalar function works on each record independently and are based on user input now scalar functions may take single or multiple arguments but they always return a single value result which is which is mandatory the resultant value of the scalar functions can be of any data type so let us now discuss some of the various scalar functions used in SQL now scalar functions in SQL are broadly classified into five types the first one is L case the next one is U case and then we have length and then we have mid and finally we have round let us not discuss about them in detail so these are basically all the scalar functions that are used in general now first we have the L case now the function L case converts all the characters of a string to lower case that is if there is any string value which is in capital letter it will change into lower cases that is into normal text now it takes one argument and returns back only a single value and it basically converts the value of a column field in a table to lower case it works on all values of the data types and converts the string in the values to lower case now similarly we have the upper case which is basically opposite of lower case it is also similar to that of L case but it basically converts all the characters of a string to upper case similarly it takes only one argument and returns back only a single value and it basically converts the value of any column field into upper case next we have length now the length function is used to return the length of the value in the field it counts the number of characters along with spaces and returns a single value integers and similar to that of uppercase and lower case it works on values of all types of data typ types next we have the mid function now the SQL function mid extracts text from a value of a field it takes one argument and works on each value independently so basically it extracts substrings in SQL from a column values having string data type next we have the round function the round function is used to round a numerical value to the number of decimal specified so basically if you're working on decimal data types you can use this round function which basically rounds of any numerical value to its nearest integer value now other than these five majorly used scalar functions SQL we also have another type which is now which is used to return the current system date and time and similarly we have a format function which is used to format the contents of the field it is used to specify how the content of the field should be displayed or returned it similarly works on the values of all data types so now that we've understood the different scalar functions in SQL let us jump straight into into my scale workbench for execution part wherein we'll discuss each and every uh scalar function with certain examples and execute them if getting your learning started is half the battle what if you could do that for free visit scaleup by simply learn click on the link in the description to know more so as you can see my scale workbench has started and in order to perform various operations using scalar functions let us take an example for that we'll consider the athletes events one table first so let me just display the records that are present in that table so let me use the select statement for that select star from athlet events let us execute this and we'll see the data that is present in this table well so the athletes event one table has various Fields such as ID name sex age height weight team no games year season and city right so firstly let us disc about the UK case scalar function first now as we discussed earlier the UK case converts all the characters of a string into uppercase and syntax is also simple that is Select UK case and within the brackets mention the column names that you want in uppercase from table name right so let's say now we have the names of all the athlets in basically in uh Capital as well as the small letters now let's say if you want the names of all those athletes only in capital letters that is in up case then you have to write the following query as select UK case within the brackets the column name which is name from table name that is athletes event one now with UK their name I'll want to display their uh name column as well so I'll take another expression which is the I want to display the names of all those employees as well so I'll just display and execute this well as you can see now there's a contrast between uh the names the name first name that we have taken and after that since we have applied the UK's function here it has changed the characters or the characters in string in the names of the athlets to completely into uppercase that is like a djang has been completely changed into the capital letters and so on like that next let us discuss about the L case function now L case function is similar to that of U case itself uh the L case function is basically used to convert value of a string column to a low case character that means if you want to uh change the the values in a particular column from Capital to small so in that case you use the L case and the syntax is also similar instead of the ukase keyword you have to just replace with L case and the syntax Remains the Same which is Select L case colum name from table name so now in order to execute this let us take another example here let us consider the crypto table here now so I'll just display the records select star from crypto 22 table right so let me just display the values so the crypto 2022 table has various Fields such as ranking crypto name price changes 24 hours changes in 7 Days changes in 30 days changes in one year the market cap and volume in 24 hours so as you can see the crypto name like the name of all the crypto currencies that have mentioned in the table are in uh capital letters or in the upper case now let's say if I want to change them into lower case I'll use the L case function and the query is followed as L case mention the column name which is crypto name I'll just copy the field here from the table that is crypto 2022 also I want to display play the uh Crypton name column again as well and we'll see how it is changed from the original uh one which we have mentioned right so let us just execute this statement and we'll see the output so as you can see earlier we had all the names of cryptocurrencies in uppercase or in capital letters now since we have applied the L case function here it has changed completely into the lowercase or the small letter values now as you can the Bitcoin reges and capital letters has been completely changed into Bitcoin so in this way you can use the lks function in order to convert the values of the strings that are present in the column fields from capital or the uppercase to the lowercase characters Right Moving ahead uh let us now discuss about the Len function in SQL now the Len function is used to return the length of the value in the field so basically it calculates the uh length of the uh characters that are present in The Columns so for that let us and the syntax is also similar to that of the other scalar functions basically the syntax is Select length since we are working on MySQL the keyword we use is length instead of length uh the query and the syntax is Select length column name from table name so let's say if I want to calculate the length of all the characters of the uh cryptocurrencies name in the table so I'll use the length function here and this query would be select length mention the uh column name which is crypto name I just copy paste it from table name which is crypto 2022 right so let us execute this and we'll see the output it will basically display the length of all the cryptocurrencies uh we can similarly mention the uh column of crypto names as well here I'll just just write crypto name column and we'll execute it so as you can see the cryptocurrencies are being displayed as well as the length of their cryptocurrencies names that we have specified here so basically it counts the number of characters along with the spaces including and it runs a single value integer now if you consider Bitcoin which has basically seven characters in total so it is displaying a 7even and if you look at the wrapped Bitcoin we have a space in between wrapped and Bitcoin so it considers the space as well when you are working on the length function so it combines the space as well as the total characters into the resultant set so in this way you can use the length function as well next let us discuss about the mid scalar function now this is perhap one of the most difficult and confusing Scala function in SQL so let me just make it easier and clear for you so mid function basically extracts text from the value of a field so in other words you can say it allows you to extract a substring from a given string that is you can extract only a certain number of characters from a string and the Syntax for the mid function is Select mid column name comma starting position comma length from table name now these parameters basically are the first one is column name that is basically the string you want to extract extract as a substring and next we have the starting position which basically indicates the position in the input string from where the extraction will be started that is from which position you want to extract that substring and finally we have the length it indicates the length of the string which we want to extract from the table so let us understand this with an example uh in order to understand it clearly so now since we have this crypto names right now instead of displaying the complete name of the uh cryptocurrencies I'll just assign a symbol for that so I'll just display the first three characters so in that case I'll write the syntax as select mid mention the column name which is crypto name just copying the field crypto name starting position will be one and the length will be the first three characters so I'm mentioning three from table name that is crypto 2022 right so we are good to go and we'll execute the statement and we'll see the output so basically it will display the first uh three characters of the string that we have taken from the uh field that is crypto name so simultaneously I'll write the I'll display the crypto names as well so that we can have a clear picture on how different it is so as you can see the Bitcoin uh the short form has been Chang it to bit and for V Bitcoin we are giving as V and for huai BTC we are going H and for ethereum eth so in similar way for all the Bitcoins we are giving let's say a alas name in another way like a shorter name from that where we are selecting only certain number of characters as a substring from the original string now you might get a doubt that where you have to use this now generally this is not used uh mostly in SQL like for example if you're working on a field which has let's say uh months in know year like from January to December now in your result and set if you don't want to display the complete name of the month like January and instead you just want to mention the limited number of characters or a substring from that string so you can just mention Jan J the three characters right and similarly Feb March April and so on so in this way you can use the mid function as for your requirement as well and finally we have the round Scala function now the round function is used to round a numerical field to the number of decimal specified so now generally if you're working on a database which has a value of decimals let's say up to uh 5 to 10 uh decimal places now if you want round off that value to let's say just two decimal places then in that case you use the round uh Scala function and the syntax is also simple uh the syntax is followed as select round and within the brackets mention the column name which you want to round up and mention the number of uh positions to which the numerical value should be rounded from table name so let us just take an example to understand this better I'll uh consider the crypto 2022 table again so let me just display the value select start from crypto 2022 right so now if you look into this table uh let's say now we have this change in the value in the last 30 days now we have the decimal places up to two places right now if I want to change that into only one place so in that case what I'll do is I'll write a simple query which says select round mention the column name that you want to round up which is changes 30 right so I'll just copy this field mention it here and I want to uh specify the decimal places up to only one place so I'll just display the uh value as one from table name that is crypto so let us execute this statement and we'll see the output right similarly I'll also mention the crypto names as well crypto so we'll just execute and we'll see the output so as you can see the value the cryptos in the the changes of the value in the last 30 days has been changed to one decimal place here now if you do not mention these uh decimal places it will basically round off the value to its nearest possible value so let us just execute this so as you can see minus 20.1 which is earlier has been changed to minus 20 and similarly so on the other values have also been changed accordingly now it will basically round off the values if the Valu is above 0.5 it will basically round off it to one that is if you have a value around 20.5 it will change into 21 and let's say if you have 35.78 then it will change into 36 so in this way you can use the round function in order to round off the uh number of places of a decimal of a given uh number or a string value so that brings us to the end of today's session guys that was all about the scalar functions we use in uh SQL I hope youve understood how to use the Scala functions the syntax and the execution part so what is SQL Group B now SQL allows the user to store more than one type and up to almost 30 types of data types in as many columns as required so sometimes it becomes difficult to find similar data in these columns now Group by in SQL helps us club together all these identical rows present in The Columns of a data Group by statement is used to group together any rows of a column with the same value stored in them on a function specified in this statement basically Group by Clause is used to group the rows which matching values using the specified condition now generally we use this group by Clause with aggregate functions such as count Max minimums maximum and average by the way guys uh if you want to know more about the aggregate functions make sure to check out our previous video on introduction to aggregate functions where we have discussed about these in detail now uh the skq group by statement uses the split apply combine technique here now the different groups are split with their values and then an aggregate function is applied to these values of these groups and finally the values are combined into a single row now if you consider this example here we have two columns and we have three different items uh a b c having different values now you can see a has two values 47 1 3 and C has 94 now when we split this and apply the sum aggregate function which results in the arithmetic sum of all these row values as you can see now a has values 4 and 7 now when I apply the sum it becomes 11 and b 1 3 it becomes 4 and C 94 it becomes 13 so it will display all these identical rows having matching values and into a resultant set so in this way you can use the SQL Group by let us now understand the syntax of SQL Group by statement the syntax is followed as select column one column two up to a number of columns from table name where condition group Group by column name now the group by statement lets the database system user know that we wish to group the same value rows of the column specified in the statements column names parameter and you can also use there is an optional where clause which can be used to specify any condition According to which the row are to be selected next let us understand what is SQL order by statement the order by clause in SQL sorts the data of a column in the SQL database it helps us sort the column in both ascending as well as the descending order the ASC keyword helps us sort in ascending order while the DC keyword sorts in descending order and if no keyword is specified in which we have to sort the records in the column it will take the default value the order by Clause sorts the record in ascending order by default if it do not mention any specific uh keyword that is ASC or DSC now the order by Clause can only be for select statement and the order by keyword word is used to sort the resulting table in either ascending order b or descending order based on the column specified in the statement's column name now another important thing to be noted here is that the order by statement always appear after the group by statement and is applied to the group of rows form now let us look at the syntax the syntax is followed as select column one column two from table name where condition which is optional as per your requirement Group by column list order by column name and and you can mention the ASC or DEC keyword as per your need now that we have understood about both of these statements let us now understand the differences between Group by and order by now Group by statement is used to group the rows that have the same value whereas the order by is used to arrange the data obtained in the resultant table of a query in sorted form now the group by is always used before the order by clause in the select statement whereas the all order by statement is used after the group by clause in the select statement now in group by statement the attribute under the aggregate function cannot be in group by Clause whereas in order by the attribute under aggregate function can be in order by Clause that means if the group by Clause contains an attribute that is not under select clause or if it is under select Clause but under uh aggregate function then the query becomes uh invalid and it throws an exception hence we can say the group by Clause is always used in collaboration with the select Clause now in group by it is mandatory to use one of the aggregate functions like count sum average minimum maximum Etc whereas in order by statement uh it is not compulsory and mandatory to use the aggregate functions and finally the group by Clause controls the presentation of rows or tles whereas the order by statement controls the presentation of columns that means the group is only concerned uh with the vales of identical rows in the resultant set whereas the order by statement uh basically arranges the data in the columns in either ascending or descending form now that we have understood about both these skq statements let us jump into myale workbench for execution part choose from over 300 in demand skills and get access to 1,000 plus hours of video content for free visit skill up by simply learn click on the link in the description to know more so as you can see myale workbench has started now in order to execute the group by and order by statement we'll consider the data set of an employee which have already downloaded from Google so let me just import the data set uh into the my SK workbench for that all you have to do is just go to the tables Tab and right click on it and you'll find table data import visit click on that now it will ask to import the location from where you have sa so I have saved my file in desktop so I'm just selecting the file now once you have select the file click the next button here now it will ask if uh to name the table whether to use existing table or to create a new table so I'm just taking a new table here employees one and click on next it will display all the columns that are present uh in that CSV file so just click on next click on next and it will start to prepare the input now depending upon the number of Records present in the data set uh it will take bit of time so don't worry now as you can see our data set has been successfully imported so we'll click on next again so uh it is showing that 49 records have been imported successfully so let me just refresh the schema here and as you can see employees one uh has been shown in the table section so let me just display the records that are present in these uh tables for that I'm using the select statement select star from employees one so as you can see our employees one table has employee ID first name last name email phone number hiring date job ID salary manager ID and finally Department ID now firstly let us discuss about the group by Clause uh now let's say if I want to fetch the total employees in all the Departments from the employ one table then the following query would be select now I can take the employ employe ID to display the total number of employees because each and every every employee has a different employee ID to their name so select count employee ID as total comma I want to display their uh Department ID as well so I'm taking Department ID from employees table employee one group by uh now we want to group all their Department ID right so I'm taking Department ID so let me just execute the statement and see the out okay the table name is wrong here so let me just correct it so as you can see it will display the total employees that are present in each and every department now if you look at the department ID column uh we have the values in a random way now in order to display in a systematic and a proper way this is where we use the order by statement so we can uh sort the department ID values in the ascending or descending manner as per as per a requirement so I want to display an ascending order so order by dep Department ID let us execute the statement and see the output so as you can now see that the department ID are being displayed in in the ascending manner that is 10 20 30 40 50 and so on up to 110 so in this way you can use the group by and order by statements so that was all about the group by and order by uh statement so this is just to give a quick uh review on how these two statement works now if you want to learn more about the order by statement make sure to check out our scale order by video on our channel uh where we've discussed about it more in depth and with more examples as well and also you might have a doubt that when to use Group by and when to use the order by statement now if you want to form the group of uh the set of uh Records or The Identical columns having values in them then you must use Group by clause and in case you want to arrange the data of a single a multiple column uh with variable different conditions or in that case you have to use the order by to sort the data in ascending or descending order so as you can see myale workbench has started and before getting into the execution but let us just quickly understand what is between operator in SQL now the SQL between operator is used to test whether an expression is within a range of values or not his operator is inclusive so it includes only the start and end values of the range and the values can be textual numerical type or dat type now between operator can be used with select insert update as well as the delete command so to get a clear picture of how this operator works let us get into the syntax of it now the syntax is followed as select column names from table name where column name between range start and range ends so the columns to be retrieved are specified after the select statement and the and the table The Columns are being retrieved from the specified is the in is in the from statement and then we have the between operator which is used in the we Clause the column we want to apply the range condition on a specified with the column name parameter and the starting value of the range of values is specified in the range start parameter and the ending value in the range and parameter so now that we've understood how exactly the between operator works let us get into some examples and understand its execution part so let us take an example to understand the SQL between operator so I'm taking the new employees as the example here so let me just display the values for that I'm using the select operator select star from new employees let us execute the statement so the new employees uh table has various columns such as employee ID first name last name email phone number hiring date job ID salary manager ID Department ID that's it so right now we'll take a simple example uh to understand the between operator so let's say if you want to retrieve the ID ID and name of the employees with IDs in the range let's say now we have IDs up to range like 198 200 21 so let us take uh the range between 110 and let's say 170 then we'll use the following query as select employe ID comma first name from table that is new employees use the where condition where employee ID between is the keyword 110 and 170 so let us execute this and we'll see the output so as you can see it will display the records of all those employees whose employee ID lies between the range that is 110 and 170 now if any employee who's having their employee ID is even their values are also included in the resultant set and similarly if any employee has 170 in their as their employee ID even their data will be in the result and set now in the table we do not have any employee who's having the employee ID as 170 so we do not have the information now so in this case it will only display only the details of all those employees who having the employee ID range in 110 and 17 and 17 well moving ahead uh let us take next another example now we can use the order by statement also to sort the result based on some columns now let's say if you uh want to retrieve the details of all those employees with salary ranging from 35,000 to let's say 75,000 and in order to sort this result based on the salary we'll use the following query as select I want to display all the details so I'm using the star operator select star from new employee where salary between 35,000 and let us take uh let's say 55,000 right and I'll use the order by salary so that it will display the values of all the salaries in the ascending order so let us execute and we'll see the output so as you can see there are only uh five employees in our new employees table uh for example rensuke Ladwig who's working at as a clerk is having salary 36,000 which is in the bracket of 35,000 55,000 similarly we have Diana Lawrence whose salary is 42,000 similarly we have Jennifer Valen whose salary is 44,000 and so on so in general we use the between operator to find the range of values like for example we are finding the salary range right so in a similar way we can use the bit operator to find uh the range between two given values of a particular column now similarly we can use the not operator with between as well now we can also use the not operator with the between operator to select the values that do not belong to that specified range now instead of using between if I mention the not between keyword here it will basically display all those records of employees whose salary is not in the range of 35,000 and 55,000 now it will display the records of all those employees like if you take Herman bear whose salary is 10,000 which is not in the bracket or in the range of 35,000 and 55,000 similarly it will display the records of all those employees whose salary is not in between that range which is 35,000 and 55,000 in this way you can use the not between keyword as well which will exclude uh the results that are not in the given range now you can use the between operator with the date values as well now when using the between with dates or the date time values we need to remember to enclose the date in single inverted commas otherwise the query will return a syntax error so let us take an example here let's say if I want to retrieve the details of all those employees whose hiring date is in between let's say 2000 and 2007 now as you can see our hiring date is between like 2005 2004 2002 and similarly Etc right so in that in that case I want to display all those employee details whose hiring date was in between 2000 and 2007 so in that case the following query would be select start from new employees again which will display all the records where higher date between mention the inverted commas so I'll take from 1st January 2000 and 31st December 2007 right so let us execute this statement and we'll see the output so it will display uh me the records of all those employees who have their hiring date in between this range which is January 1st 2000 and 31st December 2007 so in this way you can use the between operator with the date values as well right moving ahead uh now we can use the between operator with the text values as well uh between operator can also be used with the character data types uh like for example while using any text values we need to remember to again enclose the data in single inverted commas otherwise it will return a syntax error so let us take an example here let's say uh we want to retrieve all the employee details and along with their names belonging to a range from let's say alphabets A and J that means we want the details of all those employees whose name is in between the first letter that is a and the letter J in that case the following query would be select star from new employees where I'm taking first name right first name between letter a and letter J enclose them in the inverted commas and we'll see the output now so let us also order we'll use the order by statement so that we'll have an ascending order I think there is a no need for that so we'll just directly uh execute this so as you can see it will display the records of all those employees uh whose name is in the range with starting letter a and the starting letter J now you can see Donald okal whose first name starts with d right so it is in the range of a and j it will it lies between a and j and similarly we have Herman we have Alexander and David guy himo Etc so in this way you can use uh the between operator to find the values in a particular range which have only text uh data types and that brings us to the end of today's session guys that was a quick tutorial on how to use the bit operator in SQL if you remember all the SQL between operator rules and syntaxes you can easily customize your queries to retrieve the type of information that you want this tool along with other SQL tools enables us to write different kinds of essential queries now this between operators generally used to find uh the values within a certain range for example you can find the salaries of the employees within a certain brackets you can also find the marks of the students in a particular range and similarly you can find the details of the products U which lie in a certain range so in all these cases you can use the between operator as per your requirement so now that you know about the between operator it is time for you to start using the between operator along with other SQL commands Clauses and operators to query your database tables so firstly we'll discuss what is w Clause the SK W Clause is used to specify a condition while fetching the data from more than one table or by joining with multiple tables if the given condition is satisfied then only it returns a specific value from the table now we can use the W Clause to filter the records and fetch only the necessary records from our database the V Clause is not only used in the select statement but it is also used in the update delete Etc let us now understand the syntax the syntax is simple and is followed as select column 1 column 2 so on from table name where condition let us understand with an example consider the employee table here which is having various columns such as ID name age City and salary now let's say if I want to uh find the uh employee who is having the maximum salary in this table and for that I'll uh write a query stating select star from employee where salary is greater than 50,000 now if if you implement this query it will basically show those records which is having salary more than 50,000 now if you look into the table abay Kumar whose ID is 1175 is having 52,600 uh which is greater than 50,000 so in this way where basically uh meets the condition that is set by the user let us now understand what is having clause in SQL the having Clause is generally used along with the group by clause and having Clause is used to filter the results obtained by the group by Clause based on some specific condition having Clause is quite similar to that of where Clause as both are used to filter records in SQL but where Clause cannot be used with the aggregate functions like count Max sum and Etc which is why having Clause is needed and that is the reason we use the having Clause let us now understand the syntax the syntax is also similar you have to mention the having keyword here instead of where and the syn tax is followed as select column 1 column two from table name Group by column 1 column 2 so on having and mention the condition now Group by Clause is used to arrange the data into groups and having Clause is used in the column operations and it basically uh group all the identical data and gives you the condition that is specified by the U let us understand this with an example again consider the same employee table here which is having ID name H C and S salary now as we know we have to use uh aggregate functions so I'm using the a average function here and since the having clause also uses a group buy um so that is the reason I'm also using the group by now let's say if I want to find the average salary of all the employees from different cities then the following query would be select City average salary from employee Group by city now when I perform this query the this will the following output where it will show the resultant set of all the cities and their average salary now we have New Delhi that is being repeated twice which is two records and we have gazabad which is two records now New Delhi has two records which is having salary of 36,000 and 38,000 now if you consider the average it is basically 37,000 and similarly for gazabad we have three records which have salaries 46,500 42,600 52,600 now when you calculate the average it is showing us 47233 now now that You' have applied this now let us have the having condition here now let's say if I want to find the average salary of all those employees from different cities who is having average salary greater than 40,000 now in this case if I implement this query this will be the following output as you can see both gazabad and NOA has the average salary more than 40,000 that is 47233 and 48,000 so in this way you can use the SQL having Clause as well let us now look at the differences between these two Clauses that is SQL where and having Clause now SQL wear Clause basically filters the individual rows in the table based on the specific condition whereas the having Clause filters groups instead of one row at a time now as discussed earlier wear Clauses can be used with select update delete statement whereas the having uh Clause can be used only with the select statement now where comes before group a which means that where Clause filters row be performing aggregate calculations and having comes after Group by which means the having Clause filters row after performing aggregate uh calculations so consequently having is a slower than where in terms of uh complexity and efficiency as well now as discussed earlier we CLA cannot contain aggregate functions and having CL can contain aggregate functions such as count sum average Etc now SQL Weare is considered as a pre- filter because it performs the row operations first and having Clause is considered as the post filter because it performs the column operations after grouping the data so now that we've understood about these both types of SQL Clauses let us jump into MySQL workbench for the execution part if getting your learning started is half the battle what if if you could do that for free visit scaleup by simply learn click on the link in the description to know more so as you can see myale workbench has started and on the left side you can view the simply code database which is having different table such as customer Department employee employees one and Etc now to perform the where and having Clause statements we'll use the employees one uh table so let me just display the records present in that table so I'll use the select operator select star from employee employee one table click on the execute button as you can see the employees one table has various columns such as employee ID first name last name of the employee their email ID phone numbers hiring date job ID salary manager ID and Department ID so firstly let us discuss about the where uh clause or basically we condition so let's say if we want to fix the records of all those employees from these employees one table whose salary is let's say less than 5,000 and they belong to Department 30 so in that case the following query would be select star from employees table one where salary is less than 5,000 now I'm using the and operator which is basically a logical operator so whenever you're performing the wear condition you can use the logical operators and comparison operators logical operators such as and R between Etc and the comparison operators like less than equal to less than greater than equals to Etc so I'm using the and operator here and Department ID is equals to 30 so let me execute the statement and see the output so as you can see there are total of four employees uh whose salary is less than 50,000 and they belong to the department 30 now you can also as discussed earlier you can also perform the update operations uh using the we uh Clause as well now let's say if I want to update the salary of uh employee who is having employee ID 116 as let's say 10,000 then the query would be update table name that is employee 1 set salary equals to 10,000 where employee ID is equals to uh let's say 116 and the employee Name Is Shel ba so I'm just uh taking that example so let me just execute this query so as you can see our query has been successful fully executed so let me just verify whether it is executed or not so I'll use the select statement select star from employee 1 where employee ID equals to 116 so let me run this query and we'll see the output so as you can see the employee uh shell baa who's having employee ID 116 and their salary has been changed into 10,000 here success sucessfully so in this way you can use the uh wear Clause as well you can also use the wear clause for delete also now let's say if I want to delete uh the details of the employees who's having employee ID as 120 let's say then I'll use the delete statement delete from employee one where employee I ID is equals to 127 so let me just execute this query uh is so as you can see our query has been successfully executed here and the details of the employee who's having employed as 127 has been deleted successfully so in this way you can use the where uh Clause to perform various operations such as select update delete Etc using comparison and logical operators let us now understand about the having clause for that let me take an example let's say if we want to display the records of all those employees present in different departments and whose average salary is uh greater than let's say 5,000 so in that case the following query would be select now I want to display the department ID so I'm taking the department ID column average salary from employees one now as you know while using the uh having Clause we have to use the group by uh statement as well so I'll Group by all the department ID since we have multiple Department IDs present in the table Department ID having average salary greater than 5,000 $5,000 okay uh let me just display the output so our query has been successfully executed and as you can see here uh it is displaying all the different departments uh and their average salaries as well here now I can use different aggregate functions here as well if I want to count uh the the number of employees in each department I can take the count function so let me just use it count employee ID which will basically count all the total employees in the different departments so let me just execute this statement so as you can see it will display the count of all the employees that are present in different departments and having average salary more than 50,000 so in this way you can use the having uh Clause as well now we can also combine both the wear condition as well as the uh having condition as well now we can combine the wear and having Clause together in a select query in this case the we Clause is used first to filter individual rows and then the rows are then grouped to perform the aggregate calculations and finally the having Clause is used to filter the grouped data that is the identical groups that are having the same values now as discussed earlier uh we have to use the we condition before the group byy so I'm just taking the W Clause before the group buy and let's say if I want to display only those uh departments that are having more than 80 as the department ID so Department ID greater than 80 so let me just display so as you can see it will display only those records where the department ID is more than 80 that is 900 1110 and the details of all the employees whose average salary is more than 500 now I can also use the order by uh statement here in order to mention all these records in a systematic manner that is an ascending or descending order so order by Department ID so let us execute this query so as you can see it will showcase the department ID values in the ascending order that is 1900 and 110 so in this way you can use the having uh Clause as well in SQL so as you can see MySQL workbench has started and in MySQL the column operations play an important role because for even for data analysis or even if you're performing queries on your database table it is quite essential because let's say for instance you have created a table as for your require with requirement with a set of uh different columns now later at a different stage you might need to add some additional requirements where the table might need to have some additional columns or even some columns might require to be deleted or some column names need to be changed into a new columns within that existing table now there are mainly two different ways to uh satisfy this condition first is you have to delete the table and recreate a new table as per your new requirement now this is possible and advised only when the table is empty right now if the table is not empty you have have to copy the data and then delete the table or create another table and then copy or load the data into the uh new table now this is time consuming and also not advisable the other way is to add delete or modify the columns and the data present in it to the current Table without touching the existing columns and its corresponding data now this is done by using the alter table statement which fulfills the requirement of uh adding uh new data Within in the existing columns now the alter table statement is used to change the structure of the existing table by adding deleting or modifying The Columns without modifying the data in it so let us discuss what are the different operations that are performed in SQL firstly let us discuss the add column operation in uh SQL for for that let me consider the employ table so let me just display the values in it select star from employe so as you can see uh the employee table has various columns like employee ID employee name age designation City salary date of joining Department ID now let's say I have an additional requirement where I want to add another column let's say if I want to add the AAR number details of the employees for that I'll use the alter statement and the query is Alter table employee add column AAR number and mention the column data type I'm taking worker and we'll specify not null as a constant that means uh it must have a AAR number for every employee in the table so let me just execute the statement so as you can see our query has been successfully executed so let me just display the values so as you can see uh there is a new column AAR number uh that has been created in our existing table without disrupting any columns and its values in the uh employee table now similarly you can add multiple columns to your existing table as well and the query is Alter table employee add column and within the brackets mention the different column names now let's say if I want to add the par number as well as the UN that is the universal account number of the employees so the query will be add column p number mention the column data type I'm taking marcare again comma U number mention the data type ASW care so let us execute the statement and see the output so our query has been executed successfully let us see the output now so as you can see uh we have two other columns pan number and UN number in our employee table since we have not men mentioned any constraints here as null or not null uh so it by default it is taking here as null values so in this way you can use the alter table command to add multiple columns in your table as well now similarly you can even modify or update the column values as well uh let's say if I want to update the existing column name into a new name now for that I'll use the alter table statement again now consider the employee table again so let me just display the values [Music] now if I want to change this salary column name into total salary so for that the following query would be alter table employee change column is the keyword we use in MySQL so mention the keyword change column so we want to change the salary column into total salary and also mention the uh data type as well so so let us execute the query and see the output so our query has been successfully executed so let me just display the records again so as you can see a salary name of the column salary has been changed into the total salary here so in this way you can update the uh column values as well now similarly you can also modify the existing uh column type in the table as well for that the following query would be alter table employee modify is the keyword now let's say if I want to change the age data type so let's see what the initial or the previous data type version uh for age so I'll use the describe employee query here so we basically have Vare as our data type for the age now if I want to change into int so I'll just basically mention the int keyword here so let us EX to the statement and see the output so there was a bit error I forgot to mention the column keyword here so that's how it is showing an error so let me just again uh describe the table employee so as you can see the uh data type of the age column has been changed from barcap to int so in this way you can use the alter table to modify the column data type as well now similarly we can also modify a new column or an existing column with a default value as well uh let's say I want to take a new column uh bonus that is the bonus salary for all the employees so for that the following query would be alter table employee alter column is the keyword we use here bonus is the column that I'm creating set default is the keyword and I'm keeping the bonus salary default as uh let's say 15,000 so let us execute the query and see the output so I forgot to add the column of the bonus so let me just first add the column so alter table imply add column bonus Vare 20 so our bonus uh column has been successfully created here so now we can see our query will be successful here as well so let me just describe the table again now if you scroll down you can see the uh bonus column uh the field which it has the default salary as 15,000 so in this way you can add the uh default value to your existing columns or uh to your new columns as well and finally we can also drop the columns uh the drop column is basically is used to delete the unnecessary or unmounted columns in our database so so let's say if I want to delete theate already created column such as Adar pan and bonus so I'll use the drop statement here to delete the columns that were created earlier so drop so the query would be alter table employee drop column now first let us uh delete a single column here so let's say if I want to delete the other number column so I'll use the other number column here so let us see the output our query has been successfully executed let me just select the uh Records so as you can see uh other number column has been successfully deleted and similarly you can also delete multiple columns as well all you have to do is just write the same query and put a comma and write again the column drop column keyword here and let's say if I want to delete the p number as well so p number as well as the UN number here now so I can use this query here so let me just execute this and see the output so our query has been executed let me just display the records so as you can see uh the UN number as well as the pan number has been successfully uh deleted from the employee table as well and similarly the alter statement table statement is also used to add and drop various constraints on an existing table like which we have discussed earlier uh if you want your column uh a null value then you can mention the null as the constraint there and if you don't want any null values for your columns then you can use the not null keyword in your uh alter table statements so in this way you can use the uh alter table statement to perform various column operations in SQL so that was all about the SQL column operations guys where we've discussed about how to add how to delete and how to update or modify the existing columns in the database so what is a subquery an SQL subquery is a query which is written inside another query a subquery is usually added within the where Clause of another SQL select statement in a subquery the outer queries result is dependent on the result set of the inner query and that is the reason why subqueries are also called as the nested queries a subquery is also called as an inner query or inner select while the statement containing the main query or the parent query is called as the outer query or outer select query statement now the inner query executes first before its uh parent query or the outer query so that the results of an inner query can be passed to the outer query let us understand this with an example here now consider this syntax over here which is followed as select employee name Department from employee where salary equals to select maximum salary from employee now select employee name Department from employees the outer query and the rest part is the inner query now when the above query is applied to the given employee table here the subquery is executed first which select the maximum salary from the employee table then the resultant is the passed onto the we Clause of the outer query now this outer query is executed which selects the row in this case the employee name and the department from the employee table where the salary is equals to the resultant of the subquery which is the it selects the maximum salary from the employee table Returns the final result so let us now look at the types of subqueries that are used in SQL now subqueries in SQL are majorly used with insert delete select update statement along with the comparison operators like less than equals to greater than or equals to between in and Etc uh subqueries are used to execute a query dependent on the outcome of another query and it allows the user to uh fetch the results without writing two distinct queries and it is time saving as well so now that we have understood what is subqueries and a different types let us jump into MySQL workbench uh to execute the various subqueries that are used in SQL so as you can see mycale workbench has started and we have various tables in our database simply code such as customer Department employee employee one and so on firstly let us discuss the subqueries using the select statement now in SQL in most of the cases we use the select statement to perform the subqueries so for that let us consider the table employees one here so let me just display the records that are present in the table for that I'm using the select statement select sta from employee one so let us run the query and see the records so as you can see the employees one table has various columns such as employee ID first name last name email their phone number hiring date job ID salary manager ID and Department ID now let us understand this concept with the help of an example uh for executing the select statement using the subqueries now let's say if I want to find all those employees whose salary is less than the average salary of all the employees that is present in the employees one table so in this scenario we'll use a subquery to get those employees whose salary is less than the average salary from the employee table and the following query would be select now since I want to display all the records I'm using the star operator from employees 1 where salary is less than and within the parenthesis we have to mention the subquery that is Select average salary from employees 1 table so let us execute the statement and see the output uh there's a rer in the code just let me check so yeah I forgot to mention the parenthesis here uh make sure you write the subqueries always within the parenthesis otherwise it will take this query statement to understand in a better way now here the query to find out the uh average salary is the subquery here that is the select average salary from the employee one table so let me just execute this statement so we have the average salary that is 678.com based on the result so the subquery first Returns the average salary that is 678 and this result is passed on to the outer query which fetches the details of all those employees who's having salary less than the average salary that is 678 so when I execute this statement it will display the employees of all those records who's having salary less than 6,7 on8 that is uh 2,600 4,400 and so on in this way so in this way you can use the select statement to perform the software is in SQL if getting your learning started is half the battle what if you could do that for free visit scaleup by simply learn click on the link in the description to know more now other than uh the SQL select statement SQL subqueries can also be used with the insert statement in the insert statement the data returned from the subqueries used to insert the data from one table that is the existing table into another new table now let us consider the employee table here so let me just display the records select star from employee uh let me just display the records now so the employee table has various Fields such as employee ID employee name age designation City total salary date of joining Department ID and bonus now if I want to copy this uh data from the employee table into a new table let's say employee new table so I'll I can use the subqueries with the insert statement here so let me just create a new table here and the query is followed as create table employee new uh the employee new table has the employee ID employee name designation total Sal that's it so I'm taking primary key as employee ID as it uniquely identifies the each record in the table so let me just execute statement now I'll use the select statement employee new now as you can see the employee new table has various Fields employe ID employee name designation and total salary but we do not have any values that are present in the employee new table so we'll use the subquery using the insert statement to into insert values into it now let's say if I want to insert the values from the employee table that is the existing table whose salary is uh greater than 30,000 so I want to fetch only those records from the employee table into the employee new table whose salary is greater than 40,000 so the following query would be insert into the new table that is employee new select now since we are only concerned with the employee ID employee name designation and total salary we'll mention only those columns employee employee ID employee name designation total salary from the previous existing table that is our employee table where total salary I'm using the uh in operator here and within the parenthesis I'll write the subquery that is Select total salary from employe Y where total salary is greater than 40,000 close the parenthesis and let us execute the statement now so our statement has been successfully executed so let me just display the records again select star from the table employee new so as you can see it will uh insert only those records for all those employees total salary is greater than 40,000 so we have only two employees Kiran and a who is having salary is 50,000 and 60,000 now the insert statement basically specifies that new data is added to this table that is the employee new table and as we are copying all the data from the employee table which is our existing table to the new employee new table there is no need to specify the column name in the insert statement otherwise you would need to mention the column name in which you want to add the data the only thing that you have to keep in mind is that the table structure has to remain the same even if the column names are a bit different the column data types have must remain the same in order to insert the data using subqueries from one table to another table so in this way you can write subqueries using the uh insert statement as well also in the similar way you can use the update as well as the Dage statement to modify the existing data that is present in the table to know more about it make sure to check out our SQL full course for beginners 2022 on our channel so that brings us to the end of today's session guys that was all about subqueries in SQL so as you can see MySQL workbench has started and before uh understanding the syntax of how to create a functions let us just understand what a function is actually is now a function in SQL is basically a set of SQL statements that performs a specific task now if you have to repeatedly write a larger SQL queries to perform a task you can simply create a function for that now next time instead of rewriting the whole SQL query you can just simply call that function to get the desired result now these SQL functions are basically programs either developed by the user or already provided by the SQL system now we have several inbu functions or the system defined functions like aggregate functions string functions date functions Etc now other than that we can create a user defined function as well so firstly let us go through the syntax uh on how to create a function so the syntax is followed as D limiter which I explain in a while when we are creating the actual functions so let us keep it aside for a while create function function name and inside the parenthesis mention the parameters now you can create a number of parameters as per your choice uh the thing is you have to mention the parameter name as well as its data type returns data type deterministic begin and inside that we have to write the code that is the actual code for the function and use the end so let me just explain how this syntax Works firstly we are specifying the name of the stored function that we want to create after the create function keyword next we have to list all the parameters of the stored function inside the parenthesis followed by the function name and then we are specifying the data type of the return value in the return statement which can be valid using any data type here as well like int car Etc and then we are specifying if a function is deterministic or not now a deterministic function always results Returns the same uh value for the same input parameters whereas a non-deterministic function returns different results for the same input parameters so let me just explain this an example now if I take a function XYZ and I'm uh specifying a parameter let's say p function so it will always reserve the same answer as long as its input that that is p is the same and finally we'll write the code in the body section after the begin uh keyword so this code will be return uh in the body of the St function in the begin end block now inside the body section you need to specify at least one return statement this return statement basically returns a value to the calling program now whenever the return statement is reached this execution of this function is terminated automatically so let us now understand this with an example uh we have a query here which says like uh create a function bonus status which has one parameter salary of type car and returns Vare of size 20 now we have to use the following operations in the statement to create this function the first one is return eligible for bonus as the statement if salary is greater than 35,000 else return not eligible so let us now write the syntax for this so let me just switch to another Tab and we'll write the query here first write the keyword that is create function now the function is bonus status inside the parenthesis mention the parameter now I'm taking parameter as salary here which is of character I'm giving sizes 20 close the parenthesis now mention the return return statement that is it returns Vare of size 20 deterministic is the keyword that we are using here begin and inside this we have to write our main query now the query that we have earlier discussed is that if salary is greater than 35,000 then return eligible for bonus eligible for bonus and close the parenthesis else return else uh return inside the uh parenthesis not eligible mention the double quot and close the parenthesis and put a semicolon now that's it that is the end of our body so now I'll use the end if statement which will be basically close the body of our function and then I'll use the end uh operator here with the dollar sign and that is it this is basically our code for to create a function so let us now execute this statement and see the output now when I execute the statement it will throw an error and this is where we'll use the uh SQL D limiter now as you know when we write an SQL statement you use the semicolon after under the SQL statement uh like if you write a two separate statement for example uh if I say select star from I'm taking an example as employee and I'm taking another example as select star from orders now when I execute this query it will throw an output because I haven't selected which query I want to execute now for that we'll place an individual semicolon for at the end of our statement right so in a similar way MySQL uses the delimiter function to separate statement and executes each statement separately now however a function consists of a multiple statements separated by a semicolon so if you used to define a function that contains semicolon characters then it will not treat the whole function as a single statement but many statements therefore you must redefine the D limiter temporarily so that you can pass the whole function as a single statement so for that we'll use the D limiter here so let me just incorporate the D limiter here you have to use D limiter with the help of uh double slash or double dollar sign here you have to specify at the beginning as well as the end the limiter and I'll mention two double so that's it now our code is complete and let us execute the statement now so our query has been successfully executed now to see whether our function is created or not let us let's just refresh the schema here and as you can see in function section you can see bonus status which is the function we have created earlier has been created successfully so let us now just verify whether our function is working or not so I'll just write a simple query as select bonus status uh let us take 40,000 as our example so let me just execute this statement and we'll see the output so so as you can see uh the condition which you specified is if the salary is greater than 35,000 then the employees is eligible for bonus now since we have taken the salary as 40,000 it is showing as eligible for bonus now similarly let's say if I take uh 20,000 and if I execute the statement it will definitely show me not eligible so as you can see it will show not eligible for bonus so in this way you can create a use a different function by passing certain parameters to get the desired result of your choice so now that you've created a function you can query an already existing table by calling that function in an SQL statement by passing certain parameters into that function let us understand this with an example consider the employee table here so let me just display the records in that employee table so as you can see the employee table has various columns such as employee ID employee name age designation City total salary date of joining and Department ID Now using this already created functions let's say if I want to know whether the employees eligible for the bonus or not using the total salary I'll use the function and to achieve that I'll simply use the select statement to display the records so let me just write the query here select I want to fetch the employee ID employee name the total salary so I'm mentioning The Columns employee ID employee name their total salary now I'll call the function here that is our bonus status and inside the function I'm going to pass the uh parameter which is the total salary on which we'll know whether the employe is eligible for bonus or not so I'm taking as total salary from the table that is employee so let us execute the statement and see the output so there was a mistake in the function name so let us execute the statement now and we'll see the output so as you can see based on the total salary and the function that we have created earlier it will result whether or not the employees eligible for bonus or not now you can see the employee Rahul who's having salary 40,000 which is above 35,000 so in this case it is showing as eligible for bonus now if you consider another employee vun whose salary is 30,000 Which is less than 35,000 so that is why it is showing is not eligible in this way and for the rest of the employees also it will show the uh same as per the condition that we have specified in our function that is the salary should be greater than 35,000 so in this way you can simply call a function using its name and provide the parameter and get the uh required output now the other important thing to notice here is that we have used the function as a column as a parameter in our select query now we have passed the parameter that is total salary into our function to that is bonus status and the function returned a result of this calculation now in this way SQL function can be very useful as we have avoided writing complex calculations in a select query and also we can reuse this function later in any other queries as well and that brings us to the end of today's session guys I hope you understood how to create a user defined function so what is comment and roll back in SQL commit and roll back are the commonly used transaction control language commands or TCL commands used in SQL these TCL commands or transaction control language commands are basically used for managing and controlling the transactions in a database to maintain consistency and it also helps a user manage all the changes made by the DML commands from maintaining its transactions TCL lets the statements get grouped into a logical transactions now to understand more about commit androll back it is important to understand what exactly are transactions now a transaction is basically a block of SQL query or set of SQL statements executed on the information and data stored in the database management system so any transaction when made happens temporarily or permanently in database now a user needs TCL commands to make these changes permanent or temporary for example if you're creating a record or updating a record or even deleting a record from the table then you're performing a transaction on that table so it is important to control these transaction to ensure that the data Integrity is maintained and it also handles the database errors effectively so generally you will incorporate many SQL queries into a group and you will execute all of them together as a part of a transaction next let us discuss about the commit command in SQL the commit command in SQL is a transaction command that is used to save all the changes made by a particular transaction in rdbms since the last commit or roll back command is used it signifies the end of a successful transaction in an SQL database now generally the commit command is used after a data manipulation language or DML operations like insert delete and update transactions now when you perform a DML operation without a commit statement the changes are only visible to you now you can use a select statement and check the updated request from this modified data but once you use a commit command after a transaction the changes in the table or database are visible to other database users as well now another thing to keep in mind is that the database cannot be restore to its previous state once the commit command is executed all the transaction commands obeys the basic principles of asset properties in SQL and the Syntax for commit in SQL is followed as you the syntax is basically the is uses just one keyword that is commit and you can use this commit using various DML operators like insert update delete statements as well now let us say for example I have an employee table and from that I want to delete an employee record whose ID is 110 now when I perform this query it will delete the employee ID 110 and then if I perform a commit operation then it will permanently save that transaction that is it will completely delete the record from the table next let us discuss about the roll back in SQL the roll back in SQL is a command that is used to rever changes performed by a trans action now whenever a roll back command is used it revers all the changes since the last commit or roll back that we have made in our SQL table the syntax is similar to that it is includes just one keyword that is roll back and it is similarly used with the insert update delete statement and let's say for example I am deleting a record whose ID is 105 and for some reasons if I want that record back in my table then I'll use the roll back command here which will restore the uh deleted record that is the the ID of the employee who is having as 105 so it will revert back the changes that were made in the database by bringing back the original state let us now understand some differences between commit and roll back the commit is used to save the changes permanently in the database whereas the roll back is used to undo the changes and restore its previous States the commit statement is basically used after an intended transaction which has been successfully completed that is if you're performing any SQL operations or transaction in SQL and if you are sure that if there is no changes to be made for that transaction only then you have to use the commit statement whereas a roll back statement is used after a transaction is unsuccessful due to uh any circumstances like system failure Etc after executing commit command any transaction can be used for roll back which you've discussed earlier and on the other hand after executing the roll back command a transaction can be still modified and send for commit again so now that we've understood what commit and roll back are let us jump into MySQL workbench for execution part as you can see MySQL workbench has started and now by default MySQL automatically commits the changes permanently to the database so in order to force MySQL not to commit changes automatically we use a following statement here which is followed as set auto commit to zero so let me just display this U okay it is successfully executed now what this basically does is it will not commit any changes permanently that were done prior to the database earlier or else you can click on edit option here and choose preferences and in preferences you'll find SQL execution under the SQL editor section so you'll find an option which says new connections use autocommit mode so if you find a tick here in this checkbox make sure you untick before you proceed to commit and roll back commands so click on okay so that you're good to go now so let me just consider uh a table here to perform the commit and roll back commands here so for that I'm taking the employe one table as a reference so let me just display the records from that table and I'll use the select statement for that select star from employe EMP 1 so let me execute this statement so in our employee one we have various columns such as employee ID employee name age designation City total salary date of joining and Department ID now to commit the current transaction and make its changes permanent we use the commit statement right so I'll use the commit command here and I'll execute this so now basically what this does is uh it will permanently uh save the changes that were done prior to this database that is database table that is the employ one table so let me just display the values now so now let us perform some transactions on this table so as we discussed earlier we can use any DML operators like insert update or delete to perform operations using the commit and roll back command so I'm going to use the update uh command here so I'm basically updating the salary of an employee uh let's say sanjun whose employee ID is 1,3 whose total salary is 30,000 now I want to update that salary as 35,000 so for that I'm using the update statement update the table name that is employee 1 set total salary as 35,000 where employee ID equals to 1030 right so let us execute this statement so as you can see one row has been affected and our query has been executed successfully let me just display the records again so as you can see uh employee sanjana whose employee ID is 103 her Sal her total salary has been changed to 35,000 which was earlier 30,000 now let's say in future I have this requirement where I want to have the original salary salary or the previous salary now for that I can use the roll back here so when I execute the roll back statement here and select this table again the total salary has been changed to 30,000 again so basically we have done a transaction where we have updated the total salary of the employee San to 35,000 and after that we are again rolling back to its previous state that is again to 30,000 now similarly you can perform uh another transaction let's say using the delet operation now I want to delete all the records from this employee one table whose designation is let's say business analyst now we have a total of business analyst uh 1 2 three right we have total three records so we'll be deleting all these records from the table so delete from employee one where designation equals to business analyst right so let me just execute this statement okay sorry the column name has been returned wrong so that is why it is showing me as error so as you can see our query has been successfully executed and it shows three rows are affected that means all the records of the employees whose designation is business analyst have been deleted see as you can clearly see there are no records of business analyst records right so again I have this requirement where I want to get back the results or the records of the employees who are working as business analyst from this employee one table so for that I can simply use the roll back here so we simultaneously perform to a transaction here firstly we'll update this table again and then delete which was already done so now let me just roll back the statement again so when I roll back the statement and uh display the records from the table as you can clearly see the employee sanjun whose employee ID is 13 her salary which we have uh set as 35,000 has been reverted back to 30,000 to its previous or original state and uh similarly we have deleted uh the records of employees whose designation has business analyst and similarly their records also have been reverted back so in this way you can use the uh roll back command to get back to the current transaction and cancel it changes now let us take another scenario here now what if I perform a transaction before the commit command so for that I'll take another another example where I'll update the age of an employee let's say Rahul is employed is 13 and his age is 26 I want to change it to 27 so I'll use another update statement here update employee one table set age equals to 27 so his previous age was 26 now I'm changing it to 27 where employee ID is uh 101 all right so let me just execute the statement so it is successfully executed and we'll select the records so as you can see rahul's age has been changed to 27 from 26 now let's say if I commit this uh transaction right so I'll just commit this transaction so basically you cannot make any further changes uh to this record since we have committed uh the transaction already so I'll perform the uh two transactions again here which is basically updating the employees total salary to 35,000 and deleting all the records of employees whose designation is business analyst so let me just uh execute this so as you can clearly see all our scale queries has been successfully executed so let me just display the table here now so as you can clearly see that we uh the employee sanana who's having one employee ideas 2013 her salary has been changed to 35,000 and we also have uh no records of those employees whose designation is business analyst and similarly the first transaction which you have done which is updating the salary of uh employee Rahul whose ID is1 to 27 now let's say if I want to roll back this trans all these transactions so let me just roll back here and we'll see what happens so our roll back has been successfully executed and let me just display the records again now all the changes done past the last commit will be reverted if you roll back a transaction since we have uh performed two operations or transactions after commit statement which is basically updating uh the employee salary to 35,000 and delete the records of employees whose designation is business analyst now clearly we can see that their records have been reverted back to their original State uh so like we have this employee name sanjun whose salary was 30,000 which is we have changed to 35,000 and since we have rolled back again to its previous state it is 30,000 and similarly we we have all the records of the business analyst as well but if you look at here we have changed the employees age here like we have taken rahul's age as 27 which was earlier 26 and we have changed that to 27 but even after rolling back this we haven't uh got the original record that is his age is 26 that's because we' have already committed this transaction so it will basically uh not revert back to its previous state so once the commit statement has executed the modification made by the transaction it cannot be rolled back again however once the roll back statement is executed the database reaches its previous state so that brings us to the end of today's session guys now that was all about the commit and roll back commands in SQL so basically to ensure that the changes made by the transactions are permanently saved in the database we use the commit after the transaction successful completion in case the transaction faces any error while execution then to undo or rever the changes done by the transaction or roll back command is used now you might uh get a doubt that where we have to use this and where this is applicable now if you are an SQL Developer or even a beginner who's working on a database let's say you're working on a database which has thousands of records and you're modifying let's say 100 records in that now due to some errors you want to revert back all the changes that you have made right so for that you'll basically use the roll back function for that so in this way it will be helpful uh for getting the records again back to its original state firstly let us understand what is SQL like Clause the like operator is used to find specific characters in a table's column it is also used to compare columns with a specified values together with the we Clause it determines if a pattern matches specific values in a table it uses Wild Card characters which are characters used to replace one or more characters in a string to fulfill this purpose you can also use like when only a portion of the value in that table is known let us now understand the syntax the syntax is simple it is usually used with the select command and the syntax of this is followed as select column 1 column 2 so on up to column n from table name where column name like and pattern now basically the columns to be shown in the result table are specified after the select statement the column that is designed for pattern matching is specified in the we clause and the pattern forms from the specific row selection which is defined in the pattern parameter of the like operator now the like operator is case insensitive that means you can either give capital or small letters as well now multiple patterns can be specified with this operator using the and or R keywords as well and wild card characters are the most vital tools of this operator so let's go over what these are and how they're used with the like operator so what is wild card character now Wild Card characters are special symbols and characters used to represent one or more than one character in a string these are imperative to the like operator as these enable patterns to be specified that is it filters the data using certain patterns to identify a value of a string in the database now the two wild cards used with the like operator are generally percent and the underscore now the percent sign is used to represent zero or one or more characters whereas the underscore is used to represent exactly one character now to get a clear picture of both these characters let us look at an example uh in an SQL statement and see how they're used now consider this following example which is Select employee ID employee name from employees where name like a%c now from the stable employee if you want to know the employee ID of all the employees whose name start with a letter or character a you should use this following query which is the uh character or the pattern we are using here is a percentage now as we discussed earlier you can also use a small letter A instead of capital letter A because the like operator is not K sensitive now there are many operations that can you that you can perform using by specifying different patterns using the like operators now for example if you have to find a value that starts with a then the following query would be where column name like a pattern so basically we are mentioning the pattern here as as a percentage which will retrieve all those uh string values which starts with the character a now similarly if you want to find values that end with let's say sh then you have to use percentage SH now similarly if you want to find values that can have a character a in any position then you have to mention the pattern as where column name like percentage a percentage which basically returns a any values that contain the letter a in any position and similarly you can use the underscore operator as well let's say if you want to return only exact return exactly one character then you have to mention a single underscore sign now let's say if you want to find values that have a character a in third position then the following query would be where column name like and within the single codes you have to mention three underscores that is uh three times you have to specify the underscore that would be present before the character a and then you have to mention the percentage and similarly you have these different types of operations which you can uh take a screenshot of this by pausing this video now so now that we have understood how exactly SQL like works let us jump into MySQL workbench to understand in depth on how these exactly work by using different examples if getting your learning started is half the battle what if you could do that for free visit scaleup by simply learn click on the link in the description to know more as you can see my scale workbench has started and in order to execute the SQL like Operator Let Us consider a following table in our database let's say new employees on which will apply various operations using the like operator so let me first display the records that are present in the new employees table and for that I'm using the select operator and the query select star from new employees so let me just execute this and a new employees table has various columns such as employee ID first name last name email phone number hiring date job ID salary manager ID and department so firstly let us discuss some examples using the person Wild Card which basically uses the percentage sign right so let's say if I want to fetch the details of all those employees whose first name starts with a or let's say s then the following query would be select star from the table name that is new employees where is the conditional Clause first name like is the keyword and within the single codes mention s percentage now it is important to enclose all the patterns that you have specified in a single inverted comma otherwise the query will return a syntax error so let me just display this and execute this so when you execute this query it has returned me seven rows as you can see in the output which basically uh returns all those employees name whose uh first name starts with the character that is s so we have like different employees like Susan maveri Shel Higgins Shel baa sigal toas and Etc now similarly let's say if we want to fetch the details of all those employees whose uh last name or like first name ends with uh let us take a character let's say uh n so let me just display and execute the query so when you execute this query it basically returns all those employees uh details whose first name ends with M like for example if you consider here suzan Mavis whose first name has uh n in the last position and similarly we have Herman Bayer John Chen den rafle and Etc next let us take another example let's say if I want to select all the employees whose first name starts with uh let's say S and ends with let's say n so then the following query would be select star from new employee where first name like now the first character would be S and you have to mention the percentage symbol and the next last character would be n so let me just display execute this statement so as you can see we have a records of three employees whose uh first name have the letter or the character s in the first place and the character n in the last place for example Susan Mavis in the first name we have S in the first place and N in the last place similarly we have Steven Markle and Steven Styles now let us take another example let's say uh I want to find a details of the particular employee now the thing is I don't know the exact name of that employee but I know that uh his salary is somewhere between 30,000 and 50,000 so for that I can use the and or operator here so the following query would be select star from new employee where first name like let's say I know the employee name starts with uh K okay then we'll mention the percentage symbol and we'll mention uh the and operator here and salary between 30,000 ,000 so let us execute this statement and we'll see the output so when you execute this we can see that there are no records in the table that means there is no employee whose name starts with k and his salary is between 30,000 and 50,000 so let us take another example let's say the employees name starts with j so and it salary is somewhere between 30,000 and 15,000 so let me just execute the statement now as you can see in a result set we have three different employees Jennifer Valen Julia ner and Jon Malin whose salary is between 30,000 and 50,000 respectively like we have 44,000 32,000 and 33,000 so in this way you can find the details of employee even if you do not know the complete details but just by specifying the pattern uh of their name or the character that we have in their name you can fetch their details now if you want to if you further know the department ID let's say uh Jennifer Valen Department ID is 10 and she belongs to that department and I want to get the details of that employee only then I can retrieve their details in this way so I hope you've understood how to use the percentage sign to find the different patterns uh in order to find the complete uh string value from the table let us now discuss uh some examples using the underscore while character using the under underscore symbol so let me take an example here let say if we want to fetch the details of all those employees who have exactly three characters in their first name so in that case the following query would be select star from new employees where first name like and within the codes now we we've discussed there should be only three exactly three characters in their name right so we'll mention the underscore three times 1 2 3 close close the single brackets and let us execute this statement so as you can see it has fetched a total of four records uh for example Pat F uh we have only three characters that is p8 in his first name and similarly we have Lex Dean Den rapidly guy himo all these employees have only exactly three characters in their first name let us take another example let's say if we want to select all the employees with the first name that starts with s and are at least five characters in length then in that case the following query would be select star from new employees where first name like single Cotes now the first character would be S so I'm mentioning s and after s we have to mention the underscore four times that basically means it will return an exactly of at least five characters in our employees table who is having the first name having five characters so let me just execute this statement and we'll see the output so as you can see we have two uh records of employees Susan Mavis and seal toas now if you look carefully Susan whose first name has a total of five characters that is sua and that is five and similarly we have seal now if you want at least uh let's say say five characters in length then you have to mention the percentage sign so it basically means uh the employees name will start with s and it will have at least five characters in length so let me just execute the statement so it will display all those records uh whose first name is starts with the character s and have at least five characters in their first name so we have Susan mavz like we have also Shelly who uh who's having let's say 1 2 3 4 5 6 seven characters in total in the in her first name so in this way retrieve all those values whose first name starts with s and basically have at least five characters in length as you can see my scale workbench has started now in order to find the nth highest salary we need an employee table and for that I'm going to consider the new employees table here so let me just display the records that are present in the table and for that I'll use the select statement and the query is followed as select star from new employees so let me just display the results so as you can see the new employees table has various columns such as employee ID first name last name email phone number hiring date salary manager ID and department so firstly before discussing how to find the highest salary let us see how to find the maximum salary now to find the maximum salary in SQL you can simply use the uh aggregate function Max to find the highest salary in SQL so the following query would be select Max salary from the table that is new employees so let me just display the results so as you can see it is showing as 93,000 which is the highest salary in our table now next to find the second highest salary in the above of table we'll use the concept of subquery which means that firstly we'll find the highest salary in the table and then we'll Nest that query to a subquery to find the second highest salary in SQL so the query would basically be as now I'll put this as a subquery here and I'll write the outermost query now that is Select salary from the table that is new employees where salary is less than this inner query or the subquery so let us just execute this query and see the output now it will display all the salaries that are greater that is less than the uh subquery that we have return here that is the maximum salary which is 93,000 now that is not the answer that we want or the results that we want now we want the maximum salary Which is less than the 93,000 so I'm going to take the max function again here so basically how this query works is the firstly the inner query gets executed which is the maximum salary that is 93,000 next the outter query executes that is it will select the maximum salary from the new employee table which is less than the 93,000 so let me just display the results so as you can see it is showing as 90,000 and as the second highest salary so let us just verify whether it is correct or not I'll use the select statement again select star from new employees we'll use the order by order by function order by salary and I'll display the values in descending order that means from highest to lowest so in this way we can find the highest salary and the corresponding salaries that are less than the highest salary so as you can clearly see that we have 93,000 as our highest salary and the next salary as 90,000 as a next second highest salary now till now it is fine now let's say if you're asked to find the fifth highest salary or let's say 20th highest salary or basically nth highest salary from this table now you can't keep on adding subqueries to this already uh return query right it will become time consuming and it becomes complex as well now in general there are two ways to find the nth highest salary the first one is using the limit clause and the second one is using uh the dense rank function which is an inbuilt function provided by SQL which which we are going to discuss about in a while so firstly let us discuss about the uh limit clause and how we'll use the limit Clause to find the nth highest salary here so firstly let us understand the syntax here now the syntax is followed as select column list from table name order by expression limit nus1 comma 1 so let me just explain the syntax here now after the select statement you will have to mention all the column colum that you want to fetch in your resultant table from the table name order by expression which is this basically the column name you have to specify here and you're using the limit keyword and after that we have two parameters here that is n minus one comma 1 now in the syntax the limit nus one comma 1 Clause returns one row that starts after the row N now in other words if I have to Define this the first parameter that is n minus one defines after how many it will start fetching the records in our resultant set and the second parameter will basically return the number of rows in that table so let me just explain this with an example so select now I'll use the distinct keyword here which will basically remove all the uh anomalies or the repeated values in our table so distinct salary from table name that is new employees order by salary limit now let's say if I want to find the 10th highest salary in our table so now our n becomes 10 so the value here it will be n minus 1 so 10 - 1 is basically 9 comma one now what this query basically does is it will return one record after the first nine rows that is basically the 10th row which is the query that we want that is to find the 10th highest salary so let me just execute this statement okay there is an error so yes uh the quer is returning me as 24,000 which is the 10th highest salary here now similarly if you want to find the let's say 24th highest salary I'll just change the value into 24 so sorry so basically if you want to find the 24th highest salary you'll have to mention 23 here that is n minus one right so let us find the output so the next highest is 48,000 so I forgot to mention the descending keyword here that is why it is showing me the uh highest value so if I run this query again so the 24th highest salary here is 29,900 so in this way you can basically use the limit Clause to find the nth highest salary now let's say if I want to fetch the complete details of that employ who's having the 24th highest salary I'll simply use a subquery here so I'll put the this query as a subquery and I'll write another outer query using the select statement select star from new employees where salary is equals to the inner query so let me just execute this query and we'll see the output so now it will display the complete uh details of the employee uh their employee ID first name last name email phone number their salary manager ID department and Etc so in this way if you want to fet the complete details as well you can use this subquery here now let's say if you are asked to find the first five highest salaries in the table then the parameter here will be changed now here our n value becomes one so the N minus 1 becomes zero now and we want to fetch the first five records from the table so we'll mention five as our second parameter which will basically display the first five records so let me just display and execute this statement so as you can see it will display the first five highest salaries starting from 93,000 90,000 next 88,000 83,000 and next 82,000 now if You observe this query carefully it is somewhat similar to that of the top function we use now since top function is not applicable in MySQL we are using the limit Clause here now if you're working on different other databases like SQL server or mongod DB or Oracle you can use the top function now the format will remain the same only the syntax will change with the use of top keyword so in this way you can use the limit Clause to find the nth highest salary let us now discuss the second way that is using the dens rank function now MySQL dens rank function is an ilin function which is used to return sequential numbers starting from one based on the ordering of rows imposed by the order by Clause now when you have two records with the same data set then it will give the same rank to the both rows so firstly let us understand the syntax here the syntax is followed as select column list these are basically the all the columns that you want to fetch in your result and table comma denr is the keyword we use here over and inside the parenthesis we are writing the order by uh clause and we'll mention the expression as and we'll give Alias name to this uh dense rank function from the table name so now basically uh the function is always used with Over clause here which will always assign rank on basis of the order by clause and the rank is also assigned to the rows in a sequential manner that is the rank the assignment of rank to these rows will always start from one and the next value will be one greater than the previous rank aside so let us understand with this with an example now let's say I'm uh I'll use the select Clause here and I want to display the employee ID the first name of the employee last name comma salary and next I'll mention the dens DS rank keyword over and inside the brackets use the order by clause and mention the uh expression now we want to uh specify the condition here which is basically on the salary right we want to find the nth highest salary here so we'll specify the salary here as uh let's give an alas name let's say rank salary from the table that is new employee so let us execute the statement and we'll see the output so as you can see it will display the employee ID the first name last name salary and the rank of the salary now since we haven't given any uh keyword here for descending and ascending it is showing in the ascending order by default so let me just uh write the descending keyword by using the DC so let us now display this as you can clearly see it will display the records from highest to the lowest value and the rank is asside accordingly now for this salary 93,000 it is giving rank as one and for 9,002 for 88,000 3 and so on now if you look here like the uh employee Adam F who's having 82,000 as salary and similarly John Chen who's also having 82,000 salary and the rank is assigned as five which is same now if you carefully look here it will assign rank based on the value and not on the number so even if the uh values are repeated it will assign the same rank to that value so I hope you've understood how to use the dense rank function here now let's say to find the N highest salary we'll have to use a subquery here now basically I'll keep this as a subquery here and I'll use another select statement so basically I want to again display the uh employee ID first name last name and salary in a resultant table so I'll just copy this and keep it as an outer query from this uh table right so and I'll mention a where condition where rank salary is equals to now you can mention the nth highest salary now whatever you want to display you can mention this here now let's say if I want to find the uh 32nd highest salary I'll just mention the rank salary equals to 32 so let us now execute this statement and we'll see the output now it will show me an error basically here which says every derived table must have its own alas name so we have not given uh any a specific alas name to a table here so let us just give alas name let's say as e or you can take EMP or anything as per your choice right so let me just display this and we'll see the output so as you can see the 30 what is that 302 highest salary uh has has been assigned to the employee hasel fi tanker whose salary is 22,000 so let's just check whether it is correct or not I'll just copy this dens rank select statement again so let's just scroll down and as you can see uh 32 which is the rank here has been assigned to the employee hasel fi tanker whose salary is 22,000 so which is correct and you have successfully executed query in this case now similarly if you want to find another uh employee salary let's say if we want to find 18 16th highest salary we take this and we'll we'll execute the statement and it is showing me as the 48,000 which is the 16th highest salary and the employee ID is 105 the employee name is David Austin so what is websql web storage API offers a really nice way to store key value pair data information within the user browser it's self without any hazle and in a convenient way but what if you need to store complex relational data and perform simple or complex queries on this data well web storage does not allow this but websql database does basically websql database is a web browser API specification for storing and managing data in databases that can be queried using a variant of SQL that means just like any other SQL databases websql also provides a way to store the data in the database that can be queried using various SQL statements now the w3c or the worldwide Web Consortium stopped supporting the usage of websql database specification however it is still implemented and supported by some browsers like Google Chrome Android and some mobile versions of safari and Apple iOS as well now although websql tends to be labeled as an HTML 5 feature websql database is not exactly a part of the HTML 5 specification but it is a part of a separate suit of specifications which introduces a set of apis to manipulate client side databases using SQL now client side means that the processing takes place on the user's computer it requires browsers to run the scripts on the client machine without involving any processing on the server now server side means the processing takes place on a web server and this processing is vital and important to execute the task required by the user on the web now since the client side script is executed on the client's computer it is visible onto the client and on the other hand the server side script is executed in This Server hence it is not visible to the users so websql is kind of risky to use because it stores the data at the client side and not on the server side so if the user or someone outside your own browser who has the HTML script or the uh syntax ready of the websql that you are creating he can have the access and he can manage the uh browser or the API as well so that is why websql helps developers to perform database operations on client side like creating databases opening transactions creating tables inserting values to tables deleting and reading data from it the core methods in websql now core methods are an important concept of websql because they are used to perform cred operations like create update delete and read operations on the database now in websql there are mainly Three core methods they are open database transaction and finally we have execute SQL firstly open database now open database it creates a database object using the existing database or it can also create a new one next we have transaction this method is used to control a transaction and can perform form commit or roll back depending on the situation and finally execute SQL it is used basically used to execute read and WR operations to perform the result of the query that means it is used to execute uh the actual SQ query that we are performing on the websql so let us now discuss about them in detail creating an opening database in web esql as you all know to work with SQL queries you must first create a database so the first step we are going to follow is creating a database now in order to start communication with the database you have to use the open database method which will basically create a database object now if you try to uh open a database that doesn't exist the system will automatically create a database for you and eventually a database object will be created for that and open the database we have to use the following syntax as V DB obj DB obj is basically the object that I have taken to our database is equals to open database is the method and inside the parenthesis we have to mention first the database name version number text description size and creation call back so let me just uh explain about these terms so by using this open database function we can basically create a database in websql and open the database and these are basically the five parameters that are accepted by the open database function the first one is database name this argument provides the name of the database which is is mandatory to be provided otherwise you'll get an error next we have the version number the version number is also required now some database may be in version 1.0 or some maybe in 2.0 so if you know the version number of the database then only you can open the uh database that you're working on next we have the text description this argument describes the database and provides information about the database now you can provide any uh any sort of description to your database that you're working on next we have the size of the database this argument decides the size of the database basically uh which is generally in MB and finally we have a creation call back now if the database does not exist and yet it is being created this callback will be invoked so it is optional and is not needed for the database to be created and uh eventually we can apply if we want so in this way you can successfully create an open a database so let me just take an example here I've have taken uh DB as my database object here open database and the name of the database is library and the version is 1.0 and the text description I've given is my library and the size I've taken is 5 into 1024 into 1024 which is basically uh 5 MB now the size is basically from 1 MB to 5 MB Max so you have to take in that range itself next let us discuss about how we create transaction and use execute SQL method now once the database is created and open we want to create a new table to store and manage that data now any SQL operation like creating a table or inserting data into a table has to happen in a transaction a transaction is basically a set of operations in websql now transaction provides uh two features namely roll back and commit if a transaction fails at any point in time or a query has an error then it will be rolled back including all the queries that you have inserted and if all the queries are successfully executed then the transaction will be committed now the Syntax for for creating a transaction is basically DB which is a database object do transaction which is the keyword or the method now inside the uh parenthesis we will write a function TX TX is basically the object of the transaction now since we created our database object DB and using the open database meth me we can perform a transaction here using the database method transaction now the transaction basically can take up to three arguments the first one is transaction call back the second one is error call back and the last one is Success call back now these arguments are optional and so here I'm just using the transaction itself now in the transaction call back I have an attribute TX that as we've discussed earlier which is a transaction object now I'll use the execute uh SQL method of this object to create a new table that is author now I'm using this execute SQL method to create a table author which has ID first name last name as different columns now just like in uh SQL we provide a primary key to one of the column which uniquely identifies each and record similarly we provide with the keyword unique to identify the records uniquely in this so for that I'm taking ID as our unique column and once it's done to execute this query in web we will use the method execute SQL as usual so let us now discuss how to insert a new record in our table to our database so now that you have a database uh that is library and have created a table that is authors earlier we now insert the uh Records into our table now for that I'll be using the execute SQL method again on the transaction instance so the open database Remains the Same now additionally I'll just take the transaction method here following that I'm using a function T so the query would be t. execute xsql and similar to that of what we write in our SQL statement we use the same insert command here as well and the syntaxes insert into authors first name last name and values I'm taking as James priest now consider the below example which is same uh I'm just inserting some more values into our table for that I'm using another execute SQL method using the TX function which is an attribute to our method that we have used in our function so tx. execute SQL insert into author now I'm taking another attribute or say I'm adding another column ID which is unique so first name and last name values as I'm taking ID as to Rohan Nanda similarly I'm taking another record that is three uh Kiran and Kumar so in this way you can use the insert statement to add new records into our table in websql so now that we have inserted some values into a table we need to read the data that we have inserted into a table right so to do that we need to create another new transaction and another execute SQL command should be implemented here so now that we know how to add the data into our databases now you have to read and display those records back to the user in their browser and for that we need to create a simple select statement to read all values from the author's table here now when execute SQL is called this time a call back method is passed that accepts a transaction object and the result set containing the rows that we have inserted into our table will be returned in our SQL statement now you can see the call back method that I have used is display result which I am again putting it in the select statement as well now as the display result method iterates through this rows it formats the person's name in the list and adds it to an an order list with the uh ID as well so so in this way we can execute and finally we can see the output of our data so let me just take an example here so I'll jump to notepad and uh I'll show you a script on uh the websql that I've already created so this is how the uh script or the whole syntax of the queries that are performed in websql will look like now as I've discussed earlier this is somewhat kind of based on HTML so I'm not going into detail about this if you want to learn more about SQL we have a dedicated playlist on our Channel where we have discussed about HTML and JavaScript Concepts in detail so make sure you check that out so to keep it simple the first part this is basically the header part and we have the body and inside the body we basically write our SQL query here now first of all I've created a variable I've called a variable DB obj which is the database object and which I'm calling the open database method so every time this open database method is called a reference of this database will be sent to this database object which is DB obj and if I want to perform any further operations on this database I'll use the same object only instead of creating a new one every time so I've created a database here as my database with version as 1.0 and I've kept the text description as my first websql example you can write description of your own choice uh database size I'm giving it as 2 MB which should be in the format as 2 into 1024 into 1024 and I'm writing the query for opening the database that is the DV obj which is our database object open database method and inside that I'm calling all this variables inside the body now I'm using the execute SQL command here inside the function TX and this method performs a very important role in websql database this method is basically used to execute read and write statements and it provides a call back method to process the result of any query so I've created a table here employee table and I've created column as ID name and their location now I'm using a function insert which will insert all the values into our uh table columns that is ID name and location and finally I'm using the uh call back method here to read the uh data that we have created in our tables using the select operator that is Select start from employee table and I'm us using the uh call back function here as well so that is it now this is all not related this is comp pure basically on HTML which is basically I've created form which will take the input from the user and I've created different tabs for ID name location and I also created an insert button so that the user will insert the data into the values and again this is completely based on HTML guys so you might need a reference of HTML first to understand this so I would recommend you to first check the HTML videos on tags attributes and all so that it will be clear so let me just execute this statement now and let us see the output so we have to save the file in the HTML format so I'm let me just open the file so as you can see our file has been created and let us open this so as you can see it will display ID name location and and it will ask me to insert values into it so let me just uh insert values I've already taken one value so let me just take another here I'm taking IDs 2 name as let's say Rahul location Mumbai so click on the insert button it will insert the values into our table now similarly you can add a number of values as per your choice let us take another example here IDs three name Kiran and location as chenai so in this way we can add values into our table it will keep on adding every time you insert a new value into these three columns that is ID name and location so that was all about websql guys I hope you understood the concept that we covered in today's tutorial so firstly what is cast function the cast function function in SQL is one of the most commonly used conversion function which allows the user to convert from one data type to another it is very useful for concatenating results from various data types now this conversion can be either a single expression or the values from the columns in the SQL tables it also allows the user to perform calculations on two different data types as well now using cast it does not change the data complet in the database this conversion of data from one data type to another is only valid while the query is running and is not permanent that is the changes to the query is only done on a temporary basis however it is possible to convert and insert into a new column or table as well let us now understand the syntax of cast function the syntax is followed as cast expression as data type length expression here represents the data which we want to convert and the data type specifies uh the data type which we want to convert the value and finally the length of the data which is optional you can give it as per your requirement for only certain uh data types such as nare or V care now let us consider an example here which says select cast 76.8 7 as in here I'm taking a string value which is 76.8 7 and I'm converting this data type into the int value now the output is as expected that is 76 which is an integer value in this way you can use the cast function to change one data type to another data type in real time next let us discuss about what is convert function the convert function converts an expression in one data type to corresponding value in other data type now you might be confused that the convert is different from the cast function convert is similar to that of cast function as well while the functionality of both these conversion functions is same the only main difference is the syntax and the usage so you can specify the format to convert as well as the data type in the convert function and it is a very useful function especially for converting the strings to date formats and the syntax is followed as convert expression comma data type now again the data type parameter defines the target data which is to be converted now and the data type parameter can take data types as an input such as big in small end decimal numeric values float values character values variable characters in care Etc and the length is optional parameter which specifies the target data type length and the default of this parameter is around 10 or 20 now we also have the expression this parameter specif is the value in which we want to convert to another data type so let us consider an example here which is Select convert 20220 6.07 as date now this is basically a string value or you can say a part of a decimal value and I'm changing this string or character into date format which is 20122 hyphen 06 hyphen 07 which is converted into date format so in this way you can use the convert function as well let us now understand the difference between the cast and convert function in SQL now both these SQL conversion functions are row functions which are capable of typ casting column values or an expression from one data type to another and the functionality of both of the functions are almost similar but there are some major differences in them as well now both the cast fun cast and convert function are obviously are used to convert one data type to another data type and one of the main difference is the syntax now the syntax of cast is very simple it includes the value to convert and the type of resulting data type it has as as a keyword to separate the data type from the value and there is an option to express the length which is the integer that specifies the length of the target data type and on the other hand the convert syntax mentions the resulting data type first along with the optional length there is another EXP and another optional parameter called style in the convert function which is only specific to the SQL Server database now this style allows formatting the data type and specifies how the convert function should translate or format the data type now apart from this there are some certain differences as well the cast is an ANSI standard function that is cast is uh considered as the American National Standard institute's standard function that means it is applicable to most of the SQL databases out there like post view SQL MySQL Maria DB mongod DB Etc on the other hand conversion function is a specific function that is only restricted to mainly Microsoft SQL Server cast function is also more portable function of these two it means that the cast function can be used by many data bases out there and also cach is considered less powerful and less flexible than the convert function and on the other hand the convert function is generally considered as a flexible function which allows more flexibility and is mostly preferred to use for datetime values Etc convert is also useful in formatting the data's format as well and finally cast function is used to convert a data type without a specific format whereas the convert function does converting and formatting data types simultaneously now for example cast cannot convert a data time to specific format whereas the convert function can do that so there were some major differences between the cast and convert function I know you might be confusing at first because there isn't much difference between these two functions and also there is no difference in the performance of these two functions as well so it's just a matter of preference to choose which function to use you can choose to use cast when you work with multiple relational databases and the syntaxes are pretty standardized easy to remember and since it is an ANSI standard function you can use it in any database as well on the other hand if you need to specify the output styling we can use the convert function for that and even though the style is optional and is limited to certain databases like SQL Server the cast function is often is used to preserve decimal values and places while converting between decimal numeric values whereas the convert function cannot do this so it is a m matter of requirement when to use both these functions and it is up to the user to choose between these both conversion functions so now that we've understood about these two functions let us jump into MySQL workbench for execution part if getting your learning started is half the battle what if you could do that for free visit scaleup by simply learn click on the link in the description to know more as you can see mycale workbench has started and we can now work on our conversion functions firstly let us discuss about the cast function in SQL so let us take an example for this let's say if I want to convert a string value into an integer data type then the following query would be select cast and within the parenthesis let's say I have a string value which is 45. 68 and I want to change this into int data type so I'll mention int as end which is the keyword we use in the cast function so let us now execute the statement and see the output now it will show me an error that's because the int data type is not supported in MySQL workbench if you're working on cast function so instead of int you can take the signed function which is basically a sister group of the int data type so you can take the signed integer as well so let us now display this value so as you can see it will return me the 4 which will round off the decimal value which is 45.6 to 46 now let us take another example let's say I have a string value I want to change it into date format so then the following query would be select cast so let me just uh take some string values like 22 comma 9 comma 24 as date so let us execute this statement and we'll see the output so as you can see the string value that we have taken here that such as 22924 has been changed into date format which is 2022 which is the year 9 is the month and 24 is the date which is in the format of year month and date now if you pass the uh values here which is not in the range of the function that of the data that you have specified in this function for example example instead of 9 if I take 14 and let's say if I want to pass this function then it will show me a null value that's because the value 14 is not in the range of the date value that is 14 there are only like 12 months right from so the values it can take is from only from 1 to 12 now if you pass other than this range then it will show an error now similarly if I want to uh specify this value into datetime function then I can simply mention the datetime function data type as well so let us see the output so it will show me the output in the form of uh date as well as the time format here so let us now understand another example now as discussed earlier uh cast function not only changes the data type of an expression it can also change the data type of the column values in the table as well so for that let us take an example for this let me consider the resturant orders table here now I have this price column which is in decimal values now let's say if I want to round off these values into integer values then the following query would be select cast mention the parameter which is price as sign from the table name that is restorant orders so let us execute and see the output so as you can see the prices have been changed into the integer values now and the decimal values have been rounded off so similarly if I want to mention the columns uh I can mention them as well so I'll take the item name the previous price and the change price so I'm giving this change price as the new price as a new column so let us now execute this statement and see the output so as you can see it will display uh the item name the price and the new price of these items now as you can see the price of the tandor mix Grill which is 11.95 has been rounded off to an integer value that is 12 and similarly all the other values as well now if you scroll down a bit as you can see we have a price which is 0.5 and the new price is changed into zero which is not acceptable right now the price has should be of a value now instead of sign if you can take the decimal data type uh wherein you can mention this and it will change the value into the nearest value of the decimal value now as you can see here the 0.5 has been rounded off to one here and similarly 0.8 which is more than 0.5 is also rounded to 1 so in this way you can use the cast function to change the data type accordingly so now that we have covered the cast functions let us now see a few data type conversions here which will help you understand the usage of the conversion convert function as well now firstly let us start with the decimal data type now as the date as the name suggest decimal produces a decimal value and has two optional parameters which is basically m and d where m is the maximum number of digits that you want to show in the resultant table which is also known as prec and D is the number of digits after the decimal points from the scale are you understand with an example here so the following query would be select convert and inside the convert mention the expression so I'm taking the value as let's say 21. 345 and I want to change this value into decimal data type and inside the decimal I'm mentioning the parameters so I'm taking as let's say 7 as a total number of digits that I I will display and I'm taking only two decimal values that I want to display after the decimal point right so let us just execute the statement and we'll see the output so as you can see it will uh show me only the uh two values that is 35 after the decimal point so the output is 21.35% now similarly you can convert the values into date and time data types as well in using the convert function so let us consider the example for that as well so select convert uh let's say now I have a value 10 comma 25 as date time data type so we'll convert this into date time uh data type so let's see the output so as you can see the value the string value which you have specified here as 11 1025 it will be changed into the datetime data type that is 2011 1025 and the uh time as well and similarly you can use the convert function to change a number or a string value into ear type as well so let's say I'm taking a value 22 and I want to change this uh string value into ear data type so the following query would be select convert 22 as EO so as you can see the output the value the string value that we have taken as 22 has been now converted into 2022 which is in the format of year data type now similarly you can convert a string value into the time as well so let's say I'm taking a value like let's say 09 1726 and I'm specifying the data type as time so let us see the output for this as well so as you can see the string value that we have taken as uh 091 726 which is basically a number and we want to convert that number into time data type so it will display the value as 9726 which is in the format of hours minutes and seconds and that brings us to the end of today's session guys that was all about the MySQL conversion functions we have studied an understood what convert and cast function does how exactly it works its parameters with some examples now you can try converting the value in every valid data type using these two functions that is convert and cast so I hope you found this tutorial helpful and informative what is view in SQL a database view in SQL is like having virtual tables containing a single query and its result the result is usually a virtual table with rows and columns just like the actual table in a database we can either pass a query to store all the rows and Columns of a table or only a part of it now the most significant advantage of a view in SQL is that it stores the query and hence we don't have to write it again and again from scratch now but then then a question arises why not use the stored procedure instead even a stored procedure can hold a query and execute whenever called however the advantage with view is that they are easier and more straight straightforward compared to procedures while procedures can have multiple statements with them views can have only one also views do not accept any parameters as in case with the stor procedure now escale views can be considered as one of the database objects which is created over an SQL query it simply represents the data returned by an SQL query so a view does not actually store the data in the tables but every time we call a view in the database it it Returns the result set of the query in which it is defined so in a nutshell we can say that a view is similar to that of a relational database table which can be treated as a virtual table and it is just a mere representative of an underlying SQL query it always fetches the data from base table that are used to create The View using the view creation query let us now understand the syntax the views in SQL are created with the create view syntax and creating views in SQL is very simple following is the basic syntax to create a view in SQL and it is followed as create view view name as select column 1 column 2 up two so on column n from table one table two table n where condition now to see the data in the view we can query The View using the following select statement now create view is used at the start of the code to initiate the view and then select is used to decide which columns to pick from the tables now table one table up to so on table n denotes the names of the table however you can work on a single table as well now where is used to define the condition which is used to select only a particular number of rows now another significant advantage of views is that it allow certain operations like insert update delete just like uh you perform in a normal SQL table now other than this we have other operations which can be performed using the view now view is used to filter records using we Clause now we know that there is a lot of chunks of data in our database and if we want only a part of it we can filter the data using the we Clause similarly we can select columns from a single table as per our need and in the same way we can use the group by Clause to select only a particular or a summarized result of a resultant set and finally we can also select columns from multiple tables using the join condition as well so now now that you've understood what exactly views are let us jump into my scale workbench for execution part if getting your learning started is half the battle what if you could do that for free visit scaleup by simply learn click on the link in the description to know more so as you can see MySQL workbench started and before we proceed into the execution part and see how to create a view in SQL using various examples let us first quickly understand why we use views in SQL so that we'll have a clear picture uh before we get into the execution part now there are various reasons for using views in SQL the first one is majorly because of security reasons right now views provide security to the data acting as a security mechanism and Views can actually enhance security by restricting data access to users for instance we can limit a user from accessing the actual table that contains sensitive data but provide access to the view that has only insensitive data let's take an example to understand this now in a company there is an employee HR and a manager who might be working on a same database table to fetch some information now because they are from different departments there must be some data that might be irrelevant to HR but it might be relevant to the manager and in other case that data might be relevant to manager but it might be irrelevant to the employee so in that case there should be a security mechanism that would hide irrelevant information of the table from certain Usage Now views allows us to hide or show some data of table depending on the requirement and security with the help of conditions we can hide some data for a particular person now the second reason is because of to reduce the complexity now views are introduced or whe they were actually introduced to reduce the complexity of the multiple tables and deliver data in a simple manner views hide the complexity of the data in the database as they join and simplify multiple tables into a single virtual table which is easier for user to understand and finally because of consistency reasons now views also maintain the data Integrity as it presents a consistent and accurate data from the database you can easily make changes to the Views according to the user requirement and the effect of the same will be seen quickly in a quick manner so now that we've understood why we views in SQL let us just quickly uh go get into the execution part now now we can easily create a view in SQL from a single table or even multiple tables using the create view statement now but before we create a view we'll create a table named customers that we will use throughout this session now so let us just create a table first which is a customer table and the syntax is create table customer and in the customer table I'm going to create various columns of fields such as customer ID customer name age address and I'm giving customer ID as a primary key because it uniquely identifies each and every recording the table so let us just execute the statement and we'll see the output so our query has been successfully executed so let me just use the select statement to display the result select star from customer right so our table has been created and the fields customer ID customer name age and address are being displayed now let us quickly insert uh some values into the in this table now I'll use the insert statement for that insert into table name which is customer values and within the brackets now customer ID let us take as one out one name let us take as kirun and age I'm taking as 25 and address I'm taking is Hyderabad right so let us execute this statement and we'll see the output so our query has been successfully executed and the uh information has been inserted into our table so as you can see the details of Kiran has been successfully executed and displayed in our table now similarly I'll insert some more records into the table so let me just copy this insert statement so let us change the values here 102 13 104 and 105 and name also let us change I'm taking another customer name as Priya kushal web have and and let's say guy three and let us change their age as well I'm taking as 26 24 28 and 23 now you can change their address as well so I'm taking the address for prya as Bangalore for kushal I'm taking as Mumbai let us just remain Hyderabad for WBA itself and let us take as Chennai for GU 3 so let us just execute this statement and we'll see the output so our query has been successfully executed let me just uh display the values now so as you can see a total of five records have been inserted into our table now we can proceed uh into creating a view for our table customer now now to begin with we'll create a simple view from our customer table which is an existing table which we've created just now so the following syntax would be create view which is the keyword we use uh you can give any name to your view let's say uh customer view as select the columns that you want to display in your resultant view table which is a virtual table right so I want to display all the fields in my resultant view table so I'll use the select star operator data select star from customer so let us execute this statement and we'll see the output so our query has been successfully executed and now when you refresh the uh schema section you can see in views you can find a new one which is created as a customer view so let me just uh display the results in the view for that I'll use the select query again select star from customer View so as you can see the exact details of the customers that we have already inserted in our previous table customers has been inserted in the uh customer view as well so that means in this way this view will store all the columns that were uh in the customer table and it will simply copy paste all the records from customer table into the customer view which we have created created now now similarly you can also uh Create A View From the existing table as well so for that let us take an example let's say if I want to create a view of projects table here so let me just display the records from the projects table and we'll see the output so as you can see the project table has various Fields such as project ID employee ID project name project manager now let's say I have I've had a new requirement wherein uh there is a new employee who's joining as a fresher into the company and as a manager I want to uh show the details of all the project names uh that the company is currently working on now I want to hide the employee ID details because I don't want the uh fresher to uh have exact idea on which project the current employees are working so for that I'll create a view a simple view here which will basically uh give all access to the uh fresher or the the employee who have recently joined he can only view the project name and project manager name that's it so let me just create a view here create view let us take the view name as project view again as select the columns now I'll just select the project ID the project name and the project manager name here Managers from the table that is projects so let us execute this statement and we'll see the output so our query has been successfully executed now when you refresh this again you can see another view is formed in the view section which is the project view so let me just display the records that are present in this view I'll use the select statement again select star from projects view so as you can see now only the project ID the project name and the name of the project manager are being displayed without uh displaying the employee idas in this view table so it will basically encapsulate or hide The Unwanted details for the new employee so that he can only view the project name and the project manager's name itself that's it right so that brings us to the next segment wherein we'll understand how to perform various operations in a view in SQL that is how to insert update and delete records in a view now just like actual tables we can easily insert update and delete rows in a view that is not allowed in store procedures now firstly let us discuss how to insert a row in a view for an inserting a row in a view we'll use the project view as an example that we have created earlier now the view already had let's say I think 10 rows and we will insert another row using the insert into command so the following syntax is insert into name of the view that is Project views project view values and inside that uh let's say I'm taking a project ID as 1120 comma project name let us say artificial neural network comma and let us say the project manager name is Akash so let us execute the statement and we'll see the output so has been successfully executed so let me just use the select statement again to display the records so as you can see there's a new record with the project ID 1120 and the project name is artificial neural networks and the project manager name is Akash so as we can see in the output in this way we can uh create another row by inserting in the view which confirms that the insert statement has been successfully executed right so next let us discuss how to update a record in a view now like just like inserting we can also update a row in a view if you notice the previous example uh we have inserted a new row that is with a project ID as 1120 let us update uh one of the record in this let's say if I want to update the project details uh of blockchain Technology as something else I'll use the update statement here so the syntax for the update would be update mention the view name that is Project View Set uh now since you're changing the project name I'll mention the field project name set project name equals to now let's say if I want to change the project ID 110 as let's say SEO optimization or let's say SEO analytics where project ID equals to 11 1 0 right so let us execute this statement and we'll see the output our query has been successfully executed so let me run the select statement again to display the records so as you can clearly see that the project ID which was 1110 which was earlier blockchain technology has been changed into SEO analytics so the updated uh the version of the column name which was blockchain technology G has been changed into Su analytic successfully th it is confirmed that the update statement has been executed successfully as well now finally let us see how to delete a record in a view now just like how we inserted and updated a row we can also delete uh a row using the delete statement for example we will delete the row which is having project ID let's say 810 uh which is Project name as diabetic retinopathy and the project manager name is hsh so let me uh just delete this record so I'll use the delete statement here delete from Project view where is the condition we use here where project ID equals to 810 right so let me just execute this again so our query has been executed successfully let me just display the records so as you can see the record the project ID which is having A10 which is one of the record in this table has been successfully deleted so in this way you can perform various such operations which we actually generally perform in uh the normal SQL tables you can similarly perform them in SQL views as well now for for some reasons if you want to delete the whole view that is the The View that we have already created for some reasons if you want to delete it you can simply use the drop command here which will completely delete the view that you have created so the query would be drop mention the view name which is Project view right so let us just execute this so our query has been successfully executed so let me just uh use the select statement to display the records select star from which is Project view right so so let us execute this now when you execute this it will simply say that project view does not exist which means that our view which we have created has been successfully deleted from the database and the schema as well and that brings us to the end of today's session guys I hope you understood how to create a view in SQL now you might have a doubt that when to use uh views in SQL now since views have both advantages and disadvantages the question arises when to use them right now for instance if you remove an attribute or if you actually delete a table it can impact the functionality of a view as well just like in a related table for instance if a view is using the email column of a table and you drop that column or even a single data of it that is in use by the view the output of the view will be impacted now the short answer to when to use uh views in SQL is when you want to write complex select queries that require Gathering data from multiple tables in that case you can use SQL use SQL is a standard language for relational database systems all the relational database systems like MySQL msais Oracle cbase INX and other nosql psql languages use SQL as their standard database languages SQL comes with a dozen of advantages like it allows you to create and drop databases and tables set permissions on tables procedures and Views and so much more SQL is easy to to learn as the statements comprise of descriptive English words and sentences and are not case sensitive we can create and interact with database using SQL in an efficient and an easy way the benefit with SQL is that we don't have to specify how to get the data from the database rather we simply specify what is to be retrieved and SQL does the rest also called a query language SQL can do much more besides squaring as we discussed earlier it provides statements for defining the structure of the data manipulating data declaring constraints and retrieving data from the database depending on various conditions and our requirements now the working of SQL is also very simple a typical SQL process takes place with the help of a database server and a SQL now if you look at the diagram we have a end user we have a database server SQL engine database and a record now to understand how SQL works let us take an example suppose John is an HR Manager working in an organization he wants information of all the employees who have joined last year for that John writes an SQL query in his laptop to retrieve the data now in order to execute the query it must interact with relational dbms and the request should be a valid query before the SQL engine can process it the SQL engine then writes to and retrieves data from a database server now to retrieve the data the query processor is the SQL engine accepts and executes SQL commands from the data datab server to for forward it to an application server now this application server processes the SQL request and sends it to a web server where the user can access the information via SQL database table now if the required data is found in the tables the database server sends the information back to the user in this way John can retrieve the information from the database using SQL so that was all about SQL and how does it work let us now move to the next question what are the usages of SQL or you might be asked in another way like why we use SQL SQL is responsible for maintaining the relational data and data stores present in the database it is used to query and manipulate data stored in the Rel relational databases some of the features of sqr it is used to execute queries against a database it retrieves the data from the database we can perform various operations like insert update and delet and many other operations on SQL database tables as well and it can also perform complex operations on the database using various Clauses operators and functions in the SQL the a database is an organized collection of structured information or data typically stored electronically in a computer system a database is usually bab management dbms together the data and the DMS along with the applications that are associated with them are referred to as a database system often shorted to just database now data with the most common types of database in operation today is typically modeled in rows and columns in a series of tables to make processing and data quering efficient the data can then be easily accessed managed modified updated and organized in this databases now most databases use SQL for writing queries and to retrieve that information from the databases now some popular SQL databases are postr SQL MySQL Oracle Microsoft SQL server mongodb and others well moving ahead the next question is what do you understand by tables and Fields in a database a table is a set of data that are organized in a model with columns and rows columns can be categorized as a vertical and rows are horizontal Fields a table has specified number of columns called Fields but can have any number of rows which is called a record now if you take an example of a table that is given here the salary is a field in the similar way employee ID employee name job and Department also comeand the field now the vertical rows are categorized as columns and we have five different columns which are emplo employee ID employee name job department and salary and the horizontal rows are categorized as rows or tle which is basically the information that we put in into our database tables right the next question is mention different types of commands used in SQL SQL commands are instructions for communicating with the database and are used to carry out specific task work and functions on database data Now skl commands are further categorized into four different types the first one is data definition language or ddl ddl helps the user to define the database structure or schemas that are capable of creating delete deleting and modifying data create drop alter truncate are some examples of ddl commands or statements we have data manipulation language or DML DML is used to modify or manipulate data present in the database table insert update select and delete are some SQL commands that come under DML next we have data control language or DCL DCL is used to control access to the data stored in the database by granting and revoking permission to the user and finally we have transaction control language TCL it is used for managing and controlling the transactions in a database to maintain consistency commit and roll back commands come under TCL we we also have data query language additionally which is used to retrieve the data from the database select commands lets the user query the database to fetch data from the tables the next question is what are the statements used under DML commands now this might be a followup question the previous question that we have discussed which is types of SQL commands interviewer wants to know whether really understood the statements that you have covered under the DML commands and understood them you know very good way now for that you have to be familiar with all the DML commands so you might asked any one of these commands in your interview as well so there are four different types of statements used under DML commands the first one is Select this command is used to fetch data from a database table next we have insert this command allows inserting data into the table of database that is if you want to enter a new record into our already exting table you can use the insert command next we have the update this command allows modifying a data in the table now for some reasons if you want to change the data that is present in your table you can use the update command and finally we have the delete this command allows deleting specific rows or all the rows from a table moving ahead the next question is what is difference between delet and truncate difference between delete and trunk command is the most common part of an interview question and is asked in various interviews by the recruiters they're mainly used to delete the data from the database but eventually they have some differences between them as well now the first difference is the delete command is used to delete a specified amount of rows that is you can delete either single row or more than one row whereas the trunade command is used to delete all the rows at a time from a table delete is a DML command which is a data manipulation language whereas the truncate comes under the ddl which is the data definition language Now using delete there may be a we clause in the delete command in order to F the records whereas in truncate there is no we clause in the truncate command now in delete command the changes can be rolled back that is if you have deleted a particular of rows you can use the roll back statement to bring back those requests that you have deleted where in case of truncate once the changes are made they cannot be rolled back now delete is slower than truncate whereas the truncate is faster than delete now that's because uh this delete command is very slow because it maintains the transaction laws whereas the truncate command is fast because it deletes entire data at a time without maintaining any transaction laws as in the same way the delit statement occupies more transaction space than the trunade because it maintains a lock for each deleted row whereas the trunc AG statement occupies less transaction space because it Mains a transaction log for the entire data page instead of each row so those are the main differences between delet and trunade Right Moving ahead the next question is what are the constraints in SQL constraints are a set of rules imposed on the tables databases constraints help in maintaining the data accuracy integr integrity and reliability of a database constraints can be imposed at the time of creation of the table or after its creation as well now constraints can be a column level or table level column level constraints applied to a column and the table level constraints apped to the whole table now the following constraints are common which are commonly used in SQL the first one is unique constraint is specified with a column tells that the all values in a column must be unique that is the values in any row of a column must not be repeated next we have primary key a primary key is a field which can uniquely identify each row in a table and this constraint is used to specify a field in a table as a primary key next we have foreign key a foreign key is a fe is a field which can be uniquely identified each row in other table and this constraint is used to specify a field as a foreign key next we have the default constraint this constraint specifies a default value for the column when no value specified by the user next we have the check constraint the check constraint helps to to validate the values of a column to meet a particular condition that is it helps to ensure that the value stored in a column meets a specified condition and finally we have the not null constraint this constraint basically tells that we cannot store a null value in a column that is if a column is specified as not null then we'll not be able to store null value in this particular column anymore and you have to give a value to it so that was all about constraints in SQ right the next question is what do you understand by the term clause and explain various Clauses in SQL now Clauses are buil-in functions available to the user in SQL with the help of Clauses we can deal with data easily stored in the table SQL Clause helps to limit the results said by providing a condition to the query and helps to filter records from the entire set of Records now we have various Clauses in SQL some of them are the first one is is where Clause where Clause is used to filter the records in the database order by Clause order by Clause is used to sort the data either in ascending or descending order using the ASC and the dec keywords in the SQL queries and next we have the group by Clause Group by Clause is used to group entries with identical data and may be used with aggregation methods to obtain summarized database result this can be used when you are performing aggregate uh op operations for the database taes next we have having having is similar to that of we Clause but it filters the records or the rows where aggregate values meet the specified conditions only next we have the limit Clause limit Clause is used to specify only a certain number of results in the query of a resultant set table that is if you want to specify or retri only particular number ofs let's say the top 50 or the first 50 records in the table in that case you can use the limit which will specify only a limited number of records and finally we have the distinct clause which will basically delete all the rows having duplicate values that means if in your database table any record has been repeated multiple times then the distinct Clause is used to discard all those rows moving ahead the next question is what are different types of of operators used in SQL operators are the foundation of any programming language be it SQL or any other programming language like cc++ Java and other programming languages now we can Define operators as a symbols that help us to perform specific mathematical and logical computations on our SQL databases tables now in SQL we have multiple types of operators available which will be discussing shortly now we use SQL operators to specify conditions and statements satisfying a query of a desired data input now the first type of operator is basically the arithmetic operators which are used to perform the to-day operations like addition subtraction multiplication division and remainer and modulus operations next we have the bitwise operators and in bitwise operators we have bitwise and bitwise or bitwise exor Etc next we have comparison operators as a word suggest we use this operator to compare the values in our SQL database table equal to not equal to greater than not greater than less than not less than not equal to Etc are some examples of comparison operators next we have compound operators which are similar to that of arithmetic operators but we examples such as ADD equals multiply equals subtract equals divide equals and modulo equals next we have the logical operators in SQL The Logical operators are used for filtering the data and getting precise result based on a given condition the SQL logical operator are also used to combine multiple conditions these operators can be combined to test for multiple conditions in a select insert update or delete statements examples of logical operators are all and between not exist or in like and is null and finally we have string operators and some examples of string operators are string concatenations Wild Card character matches and exit the next question is name the operator which is used for pattern matching in SQL the SQL like operator is a command that is used for pattern matching that determines whether a specific character string matches a pattern like operator is frequently used in a we Clause to look for a specific pattern in a column now the Esq pattern matching provides for pattern search in data if you have no clue as to what the word should be that is present in your database table now this kind of SQL query uses wild cards to match a string pattern rather than writing the exact word the like operator is used in conjunction with SQL wild cards to fetch the required information using the percentage wild card symbol to perform a simple search which basically matches zero or more one character of any type and can be used to define wild cards both before and after the pattern search in our database table and similarly we have the underscore Wild Card which matches exactly one character rather than multiple characters of any type it can be used in conjunction with the underscore as well as the percentage wild card as well so in this way the SK like operator is the one which is used for pattern matching in SQL moving ahead the next question is what is the difference between SQL and MySQL this is one of the most frequently Asked question in your interviews and the interviewer expects whether you're familiar on the differences between the SQL and MySQL so SQL is basically a programming language which which has a particular syntax in English words and Etc whereas MySQL it is an rdbms which is a relational database management system SQL is used for querying relational database system whereas MySQL is used to modify and delete data in the database in an organized way SQL though considered as a language we have SQL Server which is a li licensed product of Microsoft MySQL is an open source platform managed by Oracle Corporation SQL provides adequate protection to SQL servers against Intruders whereas MySQL being an open source platform security cannot be reliable and can be at risk sometimes and finally SQL doesn't support any connectors whereas MySQL support connectors such as workbench tools and many other such tools to build database Etc next what is the difference what is the main difference between where and having Clause now wear Clause is used to filter the records from the table based on the specified condition whereas having Clause is used to filter records from the group based on the specified condition where Clause can be used without the group by Clause whereas the having Clause cannot be used without using the group by Clause where Clause implements in a row operation whereas the having Clause implements in column operations where Clause cannot contain aggregate functions whereas having Clause can contain aggregate functions and finally where Clause can be used with select update delete statements and on the other hand having Clause can only be used with the select statement so these were the some main differences between where and having clause in SQL now the next question is what are keys in SQL and why do we use them the SQL case is an attribute or set of attributes which helps you to identify a row in a table they also help in establishing a relation between multiple tables in the databases as well now the reason why we use SQL keys are Keys identify each record separately and uniquely now if you're working on a large data sets you might want to uh specify a different key to each column because you want to uh uniquely identify each and every record using certain columns right so in this in that case you have to use Keys now Keys also allows the user to establish and identify relationship between tables and finally it accesses or manages the store data quickly and Right Moving ahead the next question is mention different types of CAS in used in SQL now this is again another followup question to the SQL Cas which you discussed earlier and the interviewer or the recruiter might want to know whether you have the knowledge of V various different types of CAS that are used in SQL now there are primarily four different types of ke use in SQL the first one is basically the primary key the primary key in SQL is a single or group of fields or columns that can uniquely identify a row in a table now let's say if you're working on an employee table so in that case ID as a primary key because it uniquely identifies each and every record or the details of an employee in that table next we have the Super Key a Super Key is basically a combination of all possible attributes which can uniquely identify the rows in a table next we have the candidate key candidate key can be identified or defined as a set of one or more columns that can identify a record a tle uniquely in table so along with the primary key there may be some other Columns of fields where you can easily identify a unique manner so in that case you'll use the candidate key now for example let's say AAR card pan card are some examples of candidate as they also work in a similar fashion to that of primary key which uniquely identifies the records of those employees in the table and finally we have foreign key a foreign key is a set of attributes in a table that refers to the primary key of an another table so these were the different types of keys that are used in SQL right the next question is what the difference between car and Vare data type now speaking of care it is a fixed length character string data type whereas V or variable character is a variable L character string data type car data type can be of multiple byes whereas the car can accept character stings up to 255 bytes car data type can be used when the character length is known whereas Vare data type is used when the character length is not known next the car is used with when the character length of the data is same whereas the is used when the character length of the data is variable now car is considered as a static data type and is and is given static memory allocation because it only uh stores the character values whereas the Vare is considered as a dynamic data type and is given dynamic memory allocation because it has various string like characters and variables to be stored in the tables so that were the main differences between the car and backat data types moving ahead the next question is what are aggregate functions in SQL an aggregate function SQL performs calculation on multip multiple values and returns a single value it allows the user to perform complex calculation on a values to return a single Scala value we often use this aggregate functions with the group by and having Clauses of the select statement now SQL aggregate functions are further classified into five types the first one is Count next one is Su next we have average and then we have minimum and finally we have maximum right next question is write an SQL query to find second highest salary from employee table now other than some def uh definitions and other the theoretical questions you might be asked to write some basic SQL queries during your interviews now we all know to find the highest salary in employee the following query is Select ID employee name Max salary from employee basically we are selecting ID employee name as the columns in a resultant set and we are using the aggregate function Max to the salary field which will basically retrieve the maximum salary of that employee present in that employee table right now this is clear and it's a piece of cake to do right now to find the second highest salary in employee the following query would be select ID employee name maximum salary from employee where salary is less than now we have to use a subquery here which is Select maximum salary from from employee now what this query basically does is it basically processes the inner query which is Select Max salary from employee now once it executes this now the outer query get executed which is it will display the ID employee name maximum salary of the employee whose salary is less than the maximum salary which you have calculated earlier so in this way you can find this second highest salary in employee using a subquery now the next question is what are subqueries in SQL and why we use them which is obviously a followup query that we have discussed earlier a subquery is basically a type of query which is written inside another query a subquery becomes a part of larger query and a subquery is also called as inner query or an nexted query a subquery provides data to the main query also called parent query or the outer query a subquery is basically a select statement that is embedded in a clause of another eql statement and the inner query is executed once before its parent or outer query so that the results of an inner query can be passed to the out query now what I mean is like let's say let's take an example here which we have is Select name listed price from orders where listed price is greater than select average listed price from orders now we have a subquery which is Select average listed price from orders which is basically an inner query or the Nate query now first this part of the query gets exec executed where it will select the average price of the listed uh prices in the orders table and finally it will display the records of all those items and its listed price on the orders which is greater than the average of the listed prices which is basally the inner query so in this way we can use the sub queries to perform complex operations on our table databases Right Moving ahead what are joints in SQL and mention it types the SQL joint Clause is used to combine records or rows from two or more tables in a SQL database based on a related column between one or two tables SQL joints are mostly used when a user is trying to fetch data from multiple tables join keyword merges two or more tables and creates a temporary resultant set of the merge tables we can join the table using a select statement and a join condition and the query is followed as select table 1. column 1 comma table 1. column 2 comma table 2 do column 1 comma table 2 do colum 2 and so on from table one join condition which is basically the type of join you want to apply to your SQL query it it may be in a inner join outter join left join or outter join on table two on provide the condition on which you want to join the tables that is table 1. column is equals to table 2. column so in this way you can use the joints in SQL as well now moving ahead the next question is what are joints in SQL and mention it types now we have basically four different types of joints in SQL which is the first one is inner joint the inner joint basically returns records that that have matching values in both the tables that is if you cons tables and if you want to get the uh common values that are present in both the tables in that case you'll use the inner next we have the outer join or full outer join which basically returns all the records when there is a match either left or right table now basically what it does is no matter uh if it has any matching values or not it will display all the from both the tables that is table one and table two next we have the left joint left joint left out join returns all the records from the left table and only the matching records from the right table now left join is is used only when if you want to display the records from the first table and only the matching values that are present in the second table so in that case you can use the left outer join and finally we have the right outer join or right outer joint which basically returns all the records from right table and only the matching records from the from the left table so in this way you can use different types of joints in your SQL queries as well right the next question is write an SQL query to find names of all the employees that begins with a I think this is a simple query and you know to display the details of all the employees whose name begins with a we need to type this following query which is used with the like operator and the quer is follow as select star from employee where eame which is basically I'm taking as the name like a percentage so this will basically display records of all those employees whose name starts at a and similarly might be asked to display the names of all the employees that ends with a and the following queries are similar to that of the first one we have discussed and the only difference is you have to change the percentage sign and the quer is followed as select star from employee where eame like now instead of uh putting the character a in the first we have to place the percentage wild card first and then the a character which basically display all the records of employees whose name might be starting with any character but it should end with a moving ahead the next question is write an escale query to return even and odd number records from a table now to find and return the records with the odd or even values we need to check the reminder when we divide the column value by two now when the reminder is zero that's an even number otherwise that's an odd number now if you take an example here in order toay even number of Records we we are using the modulus operator here and the quer is followed as select sta from table where ID modulus 2 is equals to zero now if take employee table let's say they're having employee ID starting from 100 now 100 modulus 2 is basically equals to Z it basically Returns the even number records there and similarly all the employes uh having their ID even numbers that is 100 102 104 106 and so on and all those records will be displayed in a resultant set and similarly in order to display odd number records we use the following query select star from table where ID modulus 2 not equals to zero now if you take the employee ID as one1 when you perform the operation one1 modulus 2 you have the reminder one which basically not equal zero and it is a number record for employee having employee IDs like one1 one 35 and and so on in odd numbered places will basically return the Recs so in this way you can return even and odd number records from the table using the modulus operator moving ahead the next question is explain what is an alas in SQL and why we use it alas in SQL is basically a temporary name that is given to a table or column while writing a query this is usually done when the column or table names are long give giving aliases makes the table or column name more readable and is also preferred when there are lot of queries written for a table the is BAS SQL is a temporary change and only exists till the duration of that now to give an alas name for a call the quer is followed as select column name as alah's name from table name for example I have the column name as employee name and I just want to give the alas as en n so in this way I'm using the alas name for the column name as well and similarly if want to give alas name for the select column column two from table name as alas name so just like uh in a way you have given the alas name for your columns you can similarly the alas name for your table as well moving ahead how to create a new table from an existing table now a copy of table can be created from an existing table using the Comm combination of create and select statements using these two statements we can either select all the columns or only a specific columns from an existing table as a result the new table will be replaced with all the values of the ex table and the where Clause can also be used as an optional uh statement to select the specific columns from the table and the syntax is followed as create table new table name one select mention all the columns that you want to return from the existing table which is from existing table one and you can also specify condition using the where cause as well and finally the question is what are the applications of SQL in real life now is used in various fields in nowadays for example SQL is used in banking it is used in education it is used in finance it is used in health it is used in e-commerce sector it is used in traveling sector it is used in manufacturing sector as well now talking about banking SQL is one of the major components of the banking sectors like Banks told the Account Details customers and the transactions that are done on a daily basis in databases now in education uh schools and universities use SQL and databases to store and retrieve information about their students faculty and staff SQL is also used in finance sector where where queries are used regularly in managing the assets Revenue details shares of the companies as well execution and retrieval of data plays an important role for all the businesses and finance uh segments to make strategies and derive insights from it SQL is also used rapidly and extensively in healthcare sector as well where hospitals and other medical centers use SQL to store the details of their patients without any hand hael it also helps in maintaining all the documents and the builds of the patients and similarly in manufacturing as Al manufacturing sector also we have a lot of applications of SQL as well to track the shipments Etc now in travel aspects of uh also we use the SQL wherein the details of the customers who are traveling can also be uh used with the help of SQL and finally SQL is also used in e-commerce like we have uh Amazon which is one of the probably the one of the biggest e-commerce companies in the world which is used to locate or heavily rely on SQL to make better decisions and provide Solutions and service to its customers so these were some of the applications of SQL in real life and with that we have come to the end of today's session guys those were some of the basic SQL interview questions that are being asked the nowadays now the level of the interview questions entirely depend on the position you're applying for it could be a simple SQL question or a query for analyst role or it could be a more complex SQL interview questions and data modeling questions for data engineering roles now in this tutorial we have only discussed some of the basic uh questions or basic interview questions on SQL uh stay tuned for more uh videos wherein we'll discuss a more a further bit of uh questions like inter mediate as well as the advanced questions that we'll be covering in our upcoming videos and that brings us to the end of today's full course tutorial on SQL I hope you guys have understood the various concepts of SQL that we have covered in today's session thank you for watching the video guys if you found this tutorial informative and helpful please leave us a like and make sure to share it with your friends and colleagues if you have any further queries regarding any of the topics covered in today's session feel free to let us know in the comment section below and our team of experts will be more than happy to help resolve all your queries at earliest we'll be coming up with more such interesting videos so make sure you stay tuned to the channel until next time stay safe and keep coding hi there if you like this video subscribe to the simply learn YouTube channel and click here to watch similar videos to nerd up and get certified click here e e e e e e e e e e e