Data Analytics and SQL
Foreign 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 going through SQL for data analysis full course SQL for data analysis refers to the database querying language use of relational databases as well as its capacity to interface with numerous databases simultaneously one of the most popular and adaptable languages SQL combines an easy learning curve with a depth that enables users to construct sophisticated tools and dashboards for data analytics to get more insights data analysts can interact with data stored in relational databases using the robust programming language SQL for data analysis many businesses have created their own tools to swiftly retrieve data from databases using SQL big data is gathered and organized during the data analysis process in order to extract usable information that can be used to make important business choices technology has made it possible for data analysis to find important factors and predict pattern and Trends which boosts business productivity additionally it adds value to corporate operations because it makes it easier to understand the significance of data by providing a thorough analysis now SQL analytics can be used with languages like python using its libraries numpy Scala and Hadoop which are the three of the most popular currently in the use for data science along with big data management and manipulation however SQL is still widely used because it is a straightforward language capable of executing surprisingly complex data analysis so in today's session we'll be going through an exploratory data analysis with a real-time data sets using SQL more on that soon but before we get started if you're new to the channel and haven't subscribe to our Channel simply learn and stay updated with all the latest Technologies and hit that Bell icon to level miss an update from us so without any further Ado let's get started firstly let us discuss the agenda for today's session we'll start the tutorial with a quick introduction to data analytics and then we'll understand what is SQL and after that we'll see why SQL is needed for data science and then we'll cover basics of SQL after that we'll be going through joints in SQL after that we'll be covering some important aspects of SQL like functions views and procedure and then we'll jump into advanced SQL Concepts where we'll understand some window function CTS Etc and finally we'll perform Hands-On exploratory data analysis using SQL with a real data time sets so let us now jump into straight into today's topic what is a database data in today's world plays a vital role in our everyday lives be it 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 SQL server mongodb and post clear SQL why we need a database now that we have understood what a databases 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 retrieve 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 access 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 it is scattered in various files in different locations and the files may be in different formats as well at any point writing your 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 for 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 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 Donald Chamberlain 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 sequel which was later changed into SQL in the year 1978. The American National Standard Institute ANSI and other International organizations have standardized SQL as a language for database communication in the year 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 structure query languages it allows the user to manage and manipulate the database so you might have got a doubt in your head that you have 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 skills nowadays with the ever increasing amount of data SQL serves as a powerful tool to provide insights to businesses while handling databases SQL is used to define the overall schema that is the complete structure of the database by managing and manipulating the data accordingly and determining 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 utmost 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 Jon is an HR Manager working in an organization he wants the information of all the employees who have joined 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 in hand 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 commands 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 ddlr 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 revoked 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 high speed and fast acquity 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 PCs disadvantages of SQL there are two sites 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 SQL is constantly working on this 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 health care hospitals and other medical centers use SQL to store the details of the patients without any hassle it also helps in maintaining all the documents and bills as well retail and e-commerce with its vast presence in the market retail and e-commerce companies store their customers data to improve their shopping experiences by providing special offers which will in turn help their businesses to grow is one of the significant components of backing sector as well bang 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 retrieval of data are key for all the businesses to make strategies and derive insights from it companies using SQL SQL is used extensively 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 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 postgrew SQL Etc why we need dbms dbms have come into existence in 1960s and since then it had the edge over traditional file system which had certain anomalies and drawbacks like data redundancy inconsistency privacy and security issues and various other 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 view 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 into 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 hence 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 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-tier architecture and finally three type architecture Singletary architecture and 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 records 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-tier architecture in two-time 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 threeter architecture this is an extension to two-tier architecture and also the most widely used dbms architecture it is similar to this two type 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 this client application communicates the communicates with the server application and then internally communicates with the database system present at the server data models and 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 hierarchical 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 hierarchical 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 rdmms 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 rddms stores data in tabular form whereas dbms.
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 and dbms Keys play an important role in relational databases that used to establish a relationship between the tables it is used to fetch information from one or more rows in a table in dbms there are several Keys which are almost interrelated to each other but we look at some important and most used case 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 other number Universal identification number which is uan 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 other 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 other 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 un as the candidate key for this table now each employee Works in various departments and we cannot store the department name in the employee table that's why building 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 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 a CM 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 the 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 care 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 costs are also high as well complexity dbms is an extremely complex software initially one may find it difficult to 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 vulnerability dbms system works on a 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 a 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 maths grades Etc 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 real-time Market data to enable online trading Healthcare hospitals and medical centers use dbms 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 in 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 dbms firstly go to Google and type MySQL you'll find MySQL official website page on the search result click on that once you reach the official website page 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 repositories MySQL shell MySQL workbench Etc but since we are installing it for the first time on our Windows system and we are only concerned with the MySQL workbench and shell I will go with mySQL installer for Windows click on that link now you'll find two options for download uh the first one is Windows 86 32-bit MSI installer it is showing here 32-bit but it will work for a 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 I will find this page where you will have login and sign up for the Oracle account for time being just ignore that and click on no thanks to start my download save it on your system once it's downloaded uh open the file give all the necessary permissions now you will find a setup page where you'll have different options such as developer server client full and custom I will choose custom because we want MySQL workbench and MySQL shell so click on next now you 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 click on that and select the arrow and send it to the other side now scroll down a bit and 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 adder 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 we are saving this on our C drive 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 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 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 I'll find the server configuration type 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 I will have the authentication method you'll be provided with two options I will choose the recommended one which is given by the system click on next again now we have to set a root password 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 I will have to use it at a later stage and also by logging into the MySQL server you will use the same password so click on check so as you can see there's a blue tick mark which means it's verified 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 configuration for the system files for that just click on execute and the system will 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 click on finish click on next so once you click on finish uh MySQL workbench and MySQL will automatically launch so workbench and shella started in the background uh as you can see there's a local instant mysql80 click on that now it will ask you to enter the password which you've set earlier click on that so that's it guys we have successfully installed MySQL workbench on our system but before you get started there's another little process that will 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 files on local disk D local disk C uh click on C drive go to program files click on MySQL open MySQL server 8.
2 click on bin now these are all the files and packages that you have to connect to the server for that double click on 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 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 what you are seeing in my system that is Oracle is registered trademarkov Oracle cooperation and all this thing 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 SQL syntax in order to execute various queries SQL syntax is by default case insensitive that means the system allows the user to write the queries in both uppercase as well as lowercase but if you are 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 generally use to solve a problem and in this case the 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 am 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 Escape 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 tables 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 a numerical data type we have different 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 end and begin 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 0 1 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 9 or up to 16 bit 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 0 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 are again further divided into various types the first one is care carries used to specify a fixed length string that can contain numbers letters and special characters its size can be 0 to 255 characters is similar to care but it stores variable lens strings and size of wirecare is also more than the car data type with a range of 0 to almost 60 000 characters so if you are storing strings with a widely variable lens such as name address email ID then we have to use a wire care 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 date time 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 here month date Hearts minutes and seconds for instance I have 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 timestamp it is also similar to daytime data type the format specification is also the same as well the only difference is it has less a range of values to store it is also used to convert current time 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 operands 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 operands match The Logical condition some logical operators are and not are 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 bit wise 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 are 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 intersect and minus operators are some of the examples offset operations foreign s 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 ddl are 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 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 a create table which is the 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 there might be a chance there might be instance where you have you have given the column such as age and you are mentioning data type as character care which is basically a mismatch the computer doesn't accept it because generally age is basically a numerical value but you are mentioning care but it should ideally be int to store the data so just keep an eye on it when you are creating new tables so let us now look at an example of create statement so I want to create a table name employee for that I am writing as create table space employee and within the parenthesis I am mentioning employee ID as my first column space int which is the data type and I'm giving name worker address worker close the parenthesis and put a semicolon and if you execute this statement this will be the following result it will display uh the table with different columns with the First Column as employee ID the second column is name and the third column as address easy right the next one is SQL alter table the SQL order statement is basically is used to add modify or even delete you know certain columns from the existing table let us look at the syntax of a scale law order command so the SQL alter command is altered 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 requirement let us now look at one of the example now I wanted to add the date of birth of the employee in a new column for that I'll write as alter table employee at and within the parenthesis date of birth and I'll mention the 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 already existing table now due to certain reasons if you want to drop or delete the date of birth column for that we have to write syntax as alter table give the employee table name drop column which is the keyword we use and date of birth so this will be the final output when you execute the query it will completely drop the date of birth from the table next we'll look at SQL drop statement SQL drop statement basically 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 syntax the syntax followed is a drop table followed by the table name that we want to create let us look one of the example uh the syntax followed is a drop table employee and when I execute this it will show an error stating that table employed does not exist which means you have completely deleted all the records 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 you can further add values of your own choice foreign 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 DML are 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 on the table let us first look at the SQL insert statement the syntax followed in SQL insert statement is insert into which is the keyword that we use space table name that we have created values and inside the parenthesis you have to mention the values there is also another method to insert the values where we specify the column name as well as the values now if you execute this so but before executing we'll take an example uh we are creating a table student and we are inserting uh values such as roll number name age and City so the roll 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 the First Column as rule number the next one having name age city with the lower roll numbers as one or Rohan 22 Hyderabad and the second row consists of the Roll number two anjana uh age being 20 and the City Bangalore and the third one is roll number three the name is kaushal age 21 and the city is Mumbai SQL select statement let us now look at the syntax of SQL select statement the SQL is followed the syntax is followed as select column 1 column 2 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 hashtag that is Select star from table name let us look at the example so I want to display the roll number name age from the student table for that I'll write select row number comma name comma H from the table that is student and if I execute this it will only display roll number name age of the students and it will not display the city of the students and if you want to display uh even the city uh so 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 with having ID name age City and salary and if you want to specifically uh you know display uh the employees name uh who live in a 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 show this as output where you will find the ID of aloxyn Ravi Patel who belonged to the city New Delhi SQL update statement it is used to update already 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 keep a condition as well that is where condition let us look an example now if you want to update the student table and if I want to insert uh if I want to change the city of the Roll number three that is cow shells uh City uh I'll write as update student set CT equals to Chennai where roll number is equals to 3 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 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 student table we will write it as delete from student semicolon so this will be the output it will only display the uh column names and the data inside it will be completely erased and if you want to delete only a certain number of rows we have to use Query as delete from student where role number is equals to 1 and when you execute this query it will show like this it will completely erase the records of uh the roll number one student and the final one is data control language or DCL DCL is responsible for maintaining the security which gives control access and permissions of the database commands that come under DCL are 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 SQL Grant statement sometimes user is restricted from creating or making any changes within the table but with the help of Grant statement we can give privilege to the user to create or modify 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 are giving access of permission to the user to perform certain operations on the table that you have created to user we have to mention the username example Grant insert select on employee to Rahul that means Rahul is able to access the employee table and can perform a command such as insert and select SQL revoke statement SQL revoke 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 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 where 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 operative 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 or 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 operand and finally division operator division operator in SQL divides the operand on the left side by the operand 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 operand let us now look at the execution part of the arithmetic operations on MySQL 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 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 a column names as ID name age City and salary and also I've chosen primary key as ID as it uniquely identifies each and every record in the table close the parenthesis and put a semicolon now that you have created a table 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 quotes after that close the parenthesis and 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 insert n number of Records into the table using insert command for this employee table I have taken out values total of 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 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 10 values and it will be displayed now that we have 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 query as select salary plus 10 000 as I'm taking a new column that is employee new salary to specify all these values into the column as employee new salary from the table name that is employee now when you execute this you 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 forty thousand twenty eight thousand has been changed into 38 000 and so on now if I want to display their ID name their 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 employees 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 the plus sign with the minus sign so again for this example uh let us say uh if you want to subtract 2000 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 two thousand as employee new salary from the table that is employee so let us execute this and it will show the new values that is 2000 has been deducted from the salary of each and every employee as you can see 30 000 has been reduced to twenty eight thousand twenty eight thousand has been reduced to twenty six thousand thirty five thousand 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 into two we are using hash asterisk operator as the multiplication sign here as employee new salary from the table name that is employee now if you execute this it'll show the values like uh in this way that is the salary has been doubled if it as you can see the employer Rahul salary 30 000 has been multiplied uh and has changed into 60 000 that is doubled 20 000 has been doubled to fifty six thousand and so on so in this way you can apply multiplication operator and finally let us look at a division operator let us take the same example suppose let's say if you want to divide the salary of each and every employee then you have to write the query as select ID name salary divided by two as employing your salary from the table name that is employee and put a semicolon let us now execute this and as you can see 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 automatic 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 where 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 operand greater than the greater than operator in SQL shows only those data which are greater than the value of right hand operand 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 equals to the value of the right hand operands let us look at the syntax and execute them in the workbench firstly let us look at the equal to operator let's say 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 forty thousand 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 uh pranay Anusha and Prem who are having salary says forty thousand 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 let us put the keyword in the upper case where salary not equals to 35 000.
Let us execute this and let us see the output so as you can see 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 pranay Anusha varsha who are having salices thirty thousand twenty eight thousand forty thousand twenty three thousand and so on which are basically not equals to 35 000.
Next let us look at a greater than operator uh for this let us take an example for instance if you want to access the records of all those employees from the employee table uh whose ID is greater than one or four so for that we'll write the following query as select star from the table that is employee where ID greater than 1.4 for 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 one not five and up until 110. 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 uh such as if you want to display the records of all those employees who are having ID less than 1.5 so for that we'll write the query as select star from the table that is employee where ID is less than 1.5 let us execute this so as you can see it will display all those records of the employees who are having less than one or five that is it will display the records of the employees one or two one one or two one not three and one not for IDs next let us look at uh greater than equals to greater than equal 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 let's say if you want to 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 forty thousand let us execute this and this is will this will be the output it will show the records of all those employees who are having salary greater than or equals to forty thousand so in this case it will display a total of four records of the employee table similarly uh let us look at the 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 salary let's say less than or equals to thirty thousand so for that uh we'll have to write the following query as select star from employee where salary less than or equals to thirty thousand so let us execute this and the output will be like this that is it will display all the employer Records who are having salary less than or equals to thirty thousand a total of three records that is ID employee ID is one not one one or two one or six have been displayed because we are having salary is thirty thousand twenty eight thousand twenty three thousand which are basically less than or equals to thirty thousand so in this way you can 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 operands match The Logical condition and vice versa various logical operators in SQL are and are not and between let us understand the 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 Chinmay who are having salaries 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 our operator in SQL shows the records from the table if any of the conditions separated by the or 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 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 Kirin pranay varsha and Rohit who are having salary is less than 30 000 or else they belong to the City Bangalore so in this way we can use our operator which basically compares the data with more than one condition and unlike 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 record 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 mentioned in the SQL statement let us understand this with the help of an example let's say if you want to access the information of all those employees from the table who are not having salary as 40 000 so for that we'll write the syntax as select star from table name that is employee where not salary equals to forty thousand 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 operands 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 contexts such as to retrieve any value from the table we use select command and for comparison we use where Clause so in this c
Dr Cod boys and Donald Chamberlain 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 sequel which was later changed into SQL in the year 1978. The American National Standard Institute ANSI and other International organizations have standardized SQL as a language for database communication in the year 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 structure query languages it allows the user to manage and manipulate the database so you might have got a doubt in your head that you have 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 skills nowadays with the ever increasing amount of data SQL serves as a powerful tool to provide insights to businesses while handling databases SQL is used to define the overall schema that is the complete structure of the database by managing and manipulating the data accordingly and determining 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 utmost 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 Jon is an HR Manager working in an organization he wants the information of all the employees who have joined 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 in hand 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 commands 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 ddlr 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 revoked 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 high speed and fast acquity 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 PCs disadvantages of SQL there are two sites 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 SQL is constantly working on this 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 health care hospitals and other medical centers use SQL to store the details of the patients without any hassle it also helps in maintaining all the documents and bills as well retail and e-commerce with its vast presence in the market retail and e-commerce companies store their customers data to improve their shopping experiences by providing special offers which will in turn help their businesses to grow is one of the significant components of backing sector as well bang 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 retrieval of data are key for all the businesses to make strategies and derive insights from it companies using SQL SQL is used extensively 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 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 postgrew SQL Etc why we need dbms dbms have come into existence in 1960s and since then it had the edge over traditional file system which had certain anomalies and drawbacks like data redundancy inconsistency privacy and security issues and various other 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 view 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 into 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 hence 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 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-tier architecture and finally three type architecture Singletary architecture and 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 records 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-tier architecture in two-time 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 threeter architecture this is an extension to two-tier architecture and also the most widely used dbms architecture it is similar to this two type 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 this client application communicates the communicates with the server application and then internally communicates with the database system present at the server data models and 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 hierarchical 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 hierarchical 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 rdmms 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 rddms stores data in tabular form whereas dbms.
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 and dbms Keys play an important role in relational databases that used to establish a relationship between the tables it is used to fetch information from one or more rows in a table in dbms there are several Keys which are almost interrelated to each other but we look at some important and most used case 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 other number Universal identification number which is uan 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 other 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 other 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 un as the candidate key for this table now each employee Works in various departments and we cannot store the department name in the employee table that's why building 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 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 a CM 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 the 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 care 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 costs are also high as well complexity dbms is an extremely complex software initially one may find it difficult to 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 vulnerability dbms system works on a 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 a 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 maths grades Etc 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 real-time Market data to enable online trading Healthcare hospitals and medical centers use dbms 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 in 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 dbms firstly go to Google and type MySQL you'll find MySQL official website page on the search result click on that once you reach the official website page 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 repositories MySQL shell MySQL workbench Etc but since we are installing it for the first time on our Windows system and we are only concerned with the MySQL workbench and shell I will go with mySQL installer for Windows click on that link now you'll find two options for download uh the first one is Windows 86 32-bit MSI installer it is showing here 32-bit but it will work for a 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 I will find this page where you will have login and sign up for the Oracle account for time being just ignore that and click on no thanks to start my download save it on your system once it's downloaded uh open the file give all the necessary permissions now you will find a setup page where you'll have different options such as developer server client full and custom I will choose custom because we want MySQL workbench and MySQL shell so click on next now you 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 click on that and select the arrow and send it to the other side now scroll down a bit and 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 adder 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 we are saving this on our C drive 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 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 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 I'll find the server configuration type 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 I will have the authentication method you'll be provided with two options I will choose the recommended one which is given by the system click on next again now we have to set a root password 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 I will have to use it at a later stage and also by logging into the MySQL server you will use the same password so click on check so as you can see there's a blue tick mark which means it's verified 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 configuration for the system files for that just click on execute and the system will 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 click on finish click on next so once you click on finish uh MySQL workbench and MySQL will automatically launch so workbench and shella started in the background uh as you can see there's a local instant mysql80 click on that now it will ask you to enter the password which you've set earlier click on that so that's it guys we have successfully installed MySQL workbench on our system but before you get started there's another little process that will 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 files on local disk D local disk C uh click on C drive go to program files click on MySQL open MySQL server 8.
2 click on bin now these are all the files and packages that you have to connect to the server for that double click on 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 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 what you are seeing in my system that is Oracle is registered trademarkov Oracle cooperation and all this thing 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 SQL syntax in order to execute various queries SQL syntax is by default case insensitive that means the system allows the user to write the queries in both uppercase as well as lowercase but if you are 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 generally use to solve a problem and in this case the 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 am 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 Escape 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 tables 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 a numerical data type we have different 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 end and begin 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 0 1 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 9 or up to 16 bit 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 0 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 are again further divided into various types the first one is care carries used to specify a fixed length string that can contain numbers letters and special characters its size can be 0 to 255 characters is similar to care but it stores variable lens strings and size of wirecare is also more than the car data type with a range of 0 to almost 60 000 characters so if you are storing strings with a widely variable lens such as name address email ID then we have to use a wire care 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 date time 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 here month date Hearts minutes and seconds for instance I have 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 timestamp it is also similar to daytime data type the format specification is also the same as well the only difference is it has less a range of values to store it is also used to convert current time 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 operands 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 operands match The Logical condition some logical operators are and not are 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 bit wise 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 are 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 intersect and minus operators are some of the examples offset operations foreign s 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 ddl are 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 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 a create table which is the 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 there might be a chance there might be instance where you have you have given the column such as age and you are mentioning data type as character care which is basically a mismatch the computer doesn't accept it because generally age is basically a numerical value but you are mentioning care but it should ideally be int to store the data so just keep an eye on it when you are creating new tables so let us now look at an example of create statement so I want to create a table name employee for that I am writing as create table space employee and within the parenthesis I am mentioning employee ID as my first column space int which is the data type and I'm giving name worker address worker close the parenthesis and put a semicolon and if you execute this statement this will be the following result it will display uh the table with different columns with the First Column as employee ID the second column is name and the third column as address easy right the next one is SQL alter table the SQL order statement is basically is used to add modify or even delete you know certain columns from the existing table let us look at the syntax of a scale law order command so the SQL alter command is altered 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 requirement let us now look at one of the example now I wanted to add the date of birth of the employee in a new column for that I'll write as alter table employee at and within the parenthesis date of birth and I'll mention the 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 already existing table now due to certain reasons if you want to drop or delete the date of birth column for that we have to write syntax as alter table give the employee table name drop column which is the keyword we use and date of birth so this will be the final output when you execute the query it will completely drop the date of birth from the table next we'll look at SQL drop statement SQL drop statement basically 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 syntax the syntax followed is a drop table followed by the table name that we want to create let us look one of the example uh the syntax followed is a drop table employee and when I execute this it will show an error stating that table employed does not exist which means you have completely deleted all the records 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 you can further add values of your own choice foreign 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 DML are 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 on the table let us first look at the SQL insert statement the syntax followed in SQL insert statement is insert into which is the keyword that we use space table name that we have created values and inside the parenthesis you have to mention the values there is also another method to insert the values where we specify the column name as well as the values now if you execute this so but before executing we'll take an example uh we are creating a table student and we are inserting uh values such as roll number name age and City so the roll 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 the First Column as rule number the next one having name age city with the lower roll numbers as one or Rohan 22 Hyderabad and the second row consists of the Roll number two anjana uh age being 20 and the City Bangalore and the third one is roll number three the name is kaushal age 21 and the city is Mumbai SQL select statement let us now look at the syntax of SQL select statement the SQL is followed the syntax is followed as select column 1 column 2 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 hashtag that is Select star from table name let us look at the example so I want to display the roll number name age from the student table for that I'll write select row number comma name comma H from the table that is student and if I execute this it will only display roll number name age of the students and it will not display the city of the students and if you want to display uh even the city uh so 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 with having ID name age City and salary and if you want to specifically uh you know display uh the employees name uh who live in a 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 show this as output where you will find the ID of aloxyn Ravi Patel who belonged to the city New Delhi SQL update statement it is used to update already 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 keep a condition as well that is where condition let us look an example now if you want to update the student table and if I want to insert uh if I want to change the city of the Roll number three that is cow shells uh City uh I'll write as update student set CT equals to Chennai where roll number is equals to 3 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 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 student table we will write it as delete from student semicolon so this will be the output it will only display the uh column names and the data inside it will be completely erased and if you want to delete only a certain number of rows we have to use Query as delete from student where role number is equals to 1 and when you execute this query it will show like this it will completely erase the records of uh the roll number one student and the final one is data control language or DCL DCL is responsible for maintaining the security which gives control access and permissions of the database commands that come under DCL are 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 SQL Grant statement sometimes user is restricted from creating or making any changes within the table but with the help of Grant statement we can give privilege to the user to create or modify 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 are giving access of permission to the user to perform certain operations on the table that you have created to user we have to mention the username example Grant insert select on employee to Rahul that means Rahul is able to access the employee table and can perform a command such as insert and select SQL revoke statement SQL revoke 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 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 where 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 operative 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 or 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 operand and finally division operator division operator in SQL divides the operand on the left side by the operand 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 operand let us now look at the execution part of the arithmetic operations on MySQL 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 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 a column names as ID name age City and salary and also I've chosen primary key as ID as it uniquely identifies each and every record in the table close the parenthesis and put a semicolon now that you have created a table 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 quotes after that close the parenthesis and 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 insert n number of Records into the table using insert command for this employee table I have taken out values total of 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 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 10 values and it will be displayed now that we have 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 query as select salary plus 10 000 as I'm taking a new column that is employee new salary to specify all these values into the column as employee new salary from the table name that is employee now when you execute this you 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 forty thousand twenty eight thousand has been changed into 38 000 and so on now if I want to display their ID name their 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 employees 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 the plus sign with the minus sign so again for this example uh let us say uh if you want to subtract 2000 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 two thousand as employee new salary from the table that is employee so let us execute this and it will show the new values that is 2000 has been deducted from the salary of each and every employee as you can see 30 000 has been reduced to twenty eight thousand twenty eight thousand has been reduced to twenty six thousand thirty five thousand 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 into two we are using hash asterisk operator as the multiplication sign here as employee new salary from the table name that is employee now if you execute this it'll show the values like uh in this way that is the salary has been doubled if it as you can see the employer Rahul salary 30 000 has been multiplied uh and has changed into 60 000 that is doubled 20 000 has been doubled to fifty six thousand and so on so in this way you can apply multiplication operator and finally let us look at a division operator let us take the same example suppose let's say if you want to divide the salary of each and every employee then you have to write the query as select ID name salary divided by two as employing your salary from the table name that is employee and put a semicolon let us now execute this and as you can see 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 automatic 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 where 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 operand greater than the greater than operator in SQL shows only those data which are greater than the value of right hand operand 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 equals to the value of the right hand operands let us look at the syntax and execute them in the workbench firstly let us look at the equal to operator let's say 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 forty thousand 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 uh pranay Anusha and Prem who are having salary says forty thousand 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 let us put the keyword in the upper case where salary not equals to 35 000.
Let us execute this and let us see the output so as you can see 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 pranay Anusha varsha who are having salices thirty thousand twenty eight thousand forty thousand twenty three thousand and so on which are basically not equals to 35 000.
Next let us look at a greater than operator uh for this let us take an example for instance if you want to access the records of all those employees from the employee table uh whose ID is greater than one or four so for that we'll write the following query as select star from the table that is employee where ID greater than 1.4 for 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 one not five and up until 110. 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 uh such as if you want to display the records of all those employees who are having ID less than 1.5 so for that we'll write the query as select star from the table that is employee where ID is less than 1.5 let us execute this so as you can see it will display all those records of the employees who are having less than one or five that is it will display the records of the employees one or two one one or two one not three and one not for IDs next let us look at uh greater than equals to greater than equal 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 let's say if you want to 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 forty thousand let us execute this and this is will this will be the output it will show the records of all those employees who are having salary greater than or equals to forty thousand so in this case it will display a total of four records of the employee table similarly uh let us look at the 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 salary let's say less than or equals to thirty thousand so for that uh we'll have to write the following query as select star from employee where salary less than or equals to thirty thousand so let us execute this and the output will be like this that is it will display all the employer Records who are having salary less than or equals to thirty thousand a total of three records that is ID employee ID is one not one one or two one or six have been displayed because we are having salary is thirty thousand twenty eight thousand twenty three thousand which are basically less than or equals to thirty thousand so in this way you can 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 operands match The Logical condition and vice versa various logical operators in SQL are and are not and between let us understand the 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 Chinmay who are having salaries 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 our operator in SQL shows the records from the table if any of the conditions separated by the or 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 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 Kirin pranay varsha and Rohit who are having salary is less than 30 000 or else they belong to the City Bangalore so in this way we can use our operator which basically compares the data with more than one condition and unlike 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 record 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 mentioned in the SQL statement let us understand this with the help of an example let's say if you want to access the information of all those employees from the table who are not having salary as 40 000 so for that we'll write the syntax as select star from table name that is employee where not salary equals to forty thousand 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 operands 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 contexts such as to retrieve any value from the table we use select command and for comparison we use where Clause so in this c