Advanced SQL Full Course 2025 | Advanced SQL Tutorial for Beginners | Advanced SQL | Simplilearn

Key Takeaways

Hey there, are you ready to learn the skill that could change your career? Welcome to the advanced SQL full course by simply look. Let me tell you why you are in the right place. You know how every app you use somehow knows things about you? How Google shows you exactly what you're looking for? Or how your favorite shopping sites suggest products you actually want? Well, that's one thing powering all of these magic. SQL. SQL stands for structured query language. But don't let the fancy name scare you. It's basically just a way to talk to database and get answers from data. And here's the cool part. Every single company uses it. From the coffee shop down the street tracking their sales to Netflix figuring out which to recommend you next, they are all using SQL. So, what are we going to learn together? We'll start super simple. I'll show you what SQL actually is and how you could use to write your very first basic query. And don't worry if you've never done this before. I have got you covered. Then we'll get on into fun stuff like joining tables together. It's like connecting puzzle pieces, creating views, working with different database system like Postgra SQL and SQL Server. But here's what I'm really excited about. We are going to work on a real project together. No boring practice exercises. We are going to analyze actual data and find real insights just like you would do at your job. And because I know many of you want to get to land into that dream job, we'll also go through most common SQL interview questions you'll face. And by the time we are done, you'll feel confident walking into any interview. After this course, you'll be able to take any pile of data and turn it into useful information that help businesses make better decision. That's a pretty awesome superpower to have. So, let's get started. Now, here's a quick information. If you're interested to supercharge your career in data analytics, then the professional certificate program in data analytics and generative AI by ENIC Academy, IT Goarti is the perfect choice for you. This is an 11-month live online course which offers interactive master classes by IIT Goharti faculty and industry experts combining cutting edge tools like Genaii, JPT, Python, Tableau and SQL. You'll get to experience hands-on learning with real world projects and immerse yourself in the campus environment with a special session at IIT Gojhati. Plus, you earn an executive alumini status from IT Gojharti and IBM recognized certifications to stand out in the job market. And with practical learning, career assistance, and a curriculum tailored in today's industry needs, this program is your pathway to become a data analytics and AI expert. So, what are you waiting for? Hurry up and enroll now and you can find the course link below. In this session, we are going to learn about databases, how data is stored in relational databases, and we'll also look at some of the popular databases. Finally, we'll understand various SQL commands on MySQL server. Now, let's get started with what is a database. So, according to Oracle, a database is an organized collection of structured information or data that is typically stored electronically in a computer system. A database is usually controlled by a database management system or DBMS. So it is a storage system that has a collection of data. Relational databases store data in the form of tables that can be easily retrieved, managed and updated. You can organize data into tables, rows, columns and index it to make it easier to find relevant information. Now talking about some of the popular databases, we have MySQL database. We also have Oracle database. Then we have MongoDB which is a NoSQL database. Next we have Microsoft SQL Server. Next we have Apache Cassandra which is a free and open source NoSQL database. And finally we have Postgress SQL. Now let's learn what is SQL. So SQL is a domain specific language to communicate with databases. SQL was initially developed by IBM. Most databases use structured query language or SQL for writing and querying data. SQL commands help you to store, process, analyze, and manipulate databases. With this, let's look at what a table is. So this is how a table in a database looks like. So here you can see the name of the table is players. On the top you can see the column names. So we have the player ID, the player name, the country to which the player belongs to and we also have the goals scored by each of the players. So these are also known as fields in a database. Here each row represents a record or a tpple. So you have the player ID which is 103. Here the name of the player is Daniel. He's from England. And the number of goals he has scored is seven. So you can use SQL commands to query, update, insert records, and do a lot of other tasks. Now we'll see what the features of SQL are. SQL lets you access any information stored in a relational database. With SQL queries, data is extracted from the database in a very efficient way. The structured query language is compatible with all database systems from Oracle, IBM to Microsoft and it doesn't require much coding to manage databases. Now we will see applications of SQL. SQL is used to create a database, define its structure, implement it and let you perform many functions. SQL is also used for maintaining an already existing database. SQL is a powerful language for entering data, modifying data and extracting data in a database. SQL is extensively used as a client server language to connect the front end with the back end supporting the client server architecture. SQL when deployed as data control language DCL helps protect your database from unauthorized access. Today we will learn about what is DBMS. So let's start with an example. Imagine a library where all the books are meticulously organized, categorized and cataloged. This library can quickly provide any book you request thanks to its efficient management system. Similarly, a database is like this library but for data. Whether it's customer information for a bank, patient records for a hospital, or product inventories for an e-commerce site, databases efficiently store and manage data and making it easily accessible and updatable. So now we'll see the different types of databases that cater to various needs just like different sections in a library cater to different genres of books. Understanding these basics is crucial for appreciating the advanced functionalities of database management systems. So in technical terms, a database is an organized collection of data generally stored and accessed electronically from a computer system. Databases can store various types of data such as text, numbers, images, and more. So let's begin with understanding what a DBMS really is. Essentially, a DBMS is software designed to manage databases. It helps in storing, retrieving and updating data efficiently. Think of a DBMS as a sophisticated library system where each book is a piece of data and the DBMS ensures that each book is organized properly and can be easily found and is kept secure. Imagine you walk into a massive library filled with millions of books. Each book represents a piece of data. In this library, there are librarians who know exactly where each book is and can quickly find any book you need and ensure that that the books are safe and well organized. This library is like a database and the librarians are like a DBMS. And in the 1960s, as the amount of data grew exponentially, we needed something more efficient than manual methods. This led to the creation of DBMS, a software specifically designed to manage, retrieve, and secure data efficiently. Today, examples of popular DBMS include MySQL, Oracle, and SQL Server, which power many of the applications we use daily. Now that we have a clear understanding of what a DBMS is and its important in managing data, let's delve deeper into the heart of these systems. Just like any complex machinery, a DBMS is made up of several key components, each playing a crucial role in ensuring the smooth operation and management of databases. So, let's explore these components in detail. So DBMS consists of several key components that work together to manage data efficiently. Let's look at these components. Number one is database engine. This is the core service for assessing and processing data. And then comes the database schema that defines the logical structure of the database. Then comes the query processor that interprets and executes database queries. Additionally, there are other components like the storage manager which manages the physical storage of data and the transaction manager which ensures data consistency and handles transactions. So let's understand this with the help of an example. Imagine a DBMS as a factory that processes raw materials into finished products. The database engine is like the factory floor where all the action happens. The database schema is the blueprint outlining how everything should be organized. The query processor acts like the machinery executing specific task based on instructions. And the storage manager is the warehouse ensuring all raw materials and finished products are stored correctly. Lastly, the transaction manager. It's like the quality control team ensuring everything is consistent and transactions are smoothly handled. Now, let's explore the core functions of database management system or DBMS. These functions are crucial for efficient data management and ensure that the systems operate smoothly and effectively from storing and retrieving data to managing transactions and ensuring data integrity and security. Each function plays a vital role in the overall operation of a DBMS. So let's start with the functions of DBMS. So DBMS stores large amounts of data in a structured way and retrieves data efficiently and allows update to be made easily. So let's start with the functions of DBMS. The functions are data storage, retrieval and update. So DBMS stores large amounts of data in a structured way, retrieves data effectively and efficiently and allows update to be made easily. Then comes the transaction management that handles multiple operations on the database while ensuring data consistency. It manages transactions to ensure they are processed accurately and securely. Then comes the data integrity and security that ensures that data remains accurate and consistent throughout its life cycle. that implements security measures to protect data from unauthorized access and breaches. Now moving to the advantages of using DBMS. So using a database management system offers numerous advantages that enhance the efficiency, security and usability of data. So let's explore these benefits in detail. From ensuring data consistency and integrity to improving data sharing and providing robust security measures, DBMS plays a crucial role in modern data management. Additionally, it offers data abstraction and independence making data handling more flexible and manageable. So imagine you are running a large company with numerous departments with hundreds of employees. Managing data manually would be chaotic and errorprone but with a DBMS. Data consistency and integrity are maintained ensuring that everyone works with accurate and up-to-date information. The system also promotes improved data sharing allowing different departments to collaborate seamlessly. Enhanced security features protect sensitive data from unauthorized access just like having a robust security system for your company's valuable assets. Furthermore, the separation of data from applications known as data abstraction and independence allows you to update or change your applications without affecting the underlying data structure making your data management more flexible and efficient. So now moving to types of DBMS that is database management system types. Data management systems comes in various types each designed to handle different kinds of data and use cases. So let's explore the main types of DBMS. Number one is relational DBMS. Then comes NoSQL DBMS. Then we have object-oriented DBMS and then we have hierarchical DBMS and network DBMS. Each type has its unique features and applications making them suitable for different scenarios. So let's start with relational DBMS. It stores data in tables with rows and columns and uses SQL that is structured query language for quering and managing data. Examples are MySQL, Oracle SQL Server. And then coming to NoSQL DBMS that is designed for unstructured data and big data applications and it supports a variety of data models including document key value, column family and graph. Examples for these are MongoDB, Cassandra, Radius and more. Now coming to object- oriented DBMS that stores data as objects similar to object-oriented programming that supports complex data types and relationships. Examples for these are DB 40 and object DB. Now moving to hierarchal DBMS that organize data in a treel like structure with parent child relationships and that is efficient for specific applications like directory services and the examples are IBM information management system that is IMS. Now going to the next that is network DBMS that uses a graph structure to represent data and its relationships and it supports many to many relationships between data and the example for these are integrated data store that is ids. Now imagine you are managing different types of libraries. A relational DBMS is like a traditional library with books organized in rows and columns easily searchable with a catalog. Now coming to NoSQL. So, NoSQL DBMS is like a digital library that supports various media types and formats handling massive and unstructured collections efficiently. Then comes object-oriented DBMS. That's like a specialized library for complex collections where each book is an object with detailed attributes and relationships. Then comes hierarchical DBMS that is similar to an archive with a treel like organization making it easy to navigate through related documents. Finally, a network DBMS that's like a web of interconnected libraries supporting many to many relationships making it easier to explore interconnected collections. So with that types of DBMS now coming to common use cases of DBMS. So database management systems are widely used across various industries to handle massive amounts of data efficiently and securely. So let's look at some of the use cases where DBMS plays a vital role. From banking systems and airline reservation systems to telecommunication networks and financial systems, DBMS ensures data is managed effectively to support critical business operations. So talking about banking systems, it manages customer data, transaction records and financial statements to ensure accurate and secure banking operations. Now we talk about a reservation systems. So DBMS handles booking information, flight schedules and passenger details to streamline the reservation process and improve customer service. Now coming to telecommunication networks that manages subscriber data, call records and network performance metrics to ensure reliable and efficient communication services. Now coming to financial systems that supports trading, risk management and compliance by managing large volumes of financial data with precision and security. So imagine walking into a bank and being able to access your account details instantly. This is made possible by the DVMS that manages all customer data and transactions seamlessly. When you book a flight, the reservation system quickly retrieves available seats and schedules thanks to DBMS handling all booking information and flight schedules. In the telecommunication sector, DBMS ensures your old records and subscriber data are managed efficiently, providing you with reliable service. And similarly in the financial industry, DBMS supports various operations like trading and risk management by securely managing large volumes of data. These are just a few examples of how DBMS support critical operations across different industries. And with that guys, now we'll discuss the future trends in DBMS. So as technology continues to evolve, so do database management systems. So let's explore some of the emerging trends in DBMS that are shaping the future of database management system. These trends include big data and NoSQL databases, distributed databases, cloud databases and the integration of machine learning. So talking about big data and NoSQL database that is designed to handle massive volumes of unstructured and semiructured data that support for various data models like document, key value, column family and graph. Examples are MongoDB, Cassandra, Hadoop. Now coming to distributed databases. So these databases that run on multiple servers on nodes providing high availability and scalability. So in this data is distributed across different locations but appears as a single database to users. Examples are Google Spanner, Apache Cassandra and Amazon Dynamo DB. Now coming to cloud databases. So these databases are hosted and managed on cloud platforms offering flexibility and scalability that eliminates the need for physical infrastructure and allows for easy scaling and maintenance. Examples are Amazon RDS, Microsoft Azure SQL database and Google Cloud Spanner. Now coming to machine learning integration. So incorporating machine learning algorithms into DBMS for advanced data analytics and predicting modeling that helps in automating data management task and extracting valuable insights from data. Examples are Google's BigQuery, Amazon Redshift ML and Oracle Autonomous Database. So let's understand what 10ear diagram is. An entity relationship diagram describes the relationship of entities that needs to be stored in a database. EI diagram is mainly a structural design for the database. It is a framework made using specialized symbols to define the relationship between entities. EI diagrams are created based on the three main components entities, attributes and relationships. Let's understand the use of ER diagram with the help of a real world example. Here a school needs all its student records to be stored digitally. So they approach an IT company to do so. A person from the company will meet the school authorities, note all their requirements, describe them in the form of EA diagram and get it cross-cheed by the school authorities. As the school authorities approved the year diagram, the database engineers would carry further implementation. Let's have a view of an air diagram. The following diagram showcases two entities student and course and the relationship. The relationship described between student and course is many to many as a course can be opted by several students and a student can opt for more than one course. Here student is the entity and it possesses the attributes that is student ID, student name and student age. And the course entity has attributes such as course ID and course name. Now we have an understanding of year diagram. Let us see why it has been so popular. The logical structure of the database provided by year diagram communicates the landscape of business to different teams in the company which is eventually needed to support the business. Your diagram is a GUI representation of the logical structure of a database which gives a better understanding of the information to be stored in a database. Database designers can use EI diagrams as a blueprint which reduces complexity and helps them save time to build databases quickly. EI diagrams helps to identify the entities that exist in a system and the relationships between those entities. After knowing its uses, now we should get familiar with the symbols used in year digger. The rectangle symbol represents the entities. Oval symbol represents attributes. A rectangle embedded in a rectangle represents a weak entity. A dashed oval represents a derived attribute. A diamond symbol represents a relationship among entities. Double O symbol represents multivalued attributes. Now we should dive in and learn about the components of ER diagram. There are three main components of P diagram. Entity, attribute and relationship. Entities have weak entity. Attributes are further classified into key attribute, composite attribute, multivalued attribute and derived attribute. Relationships are also classified into one to one relationships, one to many relationships, many to one relationships and many to many relationships. Let's understand these components of VI diagram. Starting with entities. An entity can be either a living or a non-living component. An entity is showcased as a rectangle in any diagram. Let's understand this with the help of an diagram. Here both student and course are in rectangular shape and are called entities and they represent the relationship study in a diamond shape. Let's transition to weak entity. An entity that makes reliance over another entity is called a weak entity. The weak entity is showcased as a double rectangle in ER diagram. In the example below, the school is a strong entity because it has a primary key attribute school number. Unlike the school, the classroom is a weak entity because it does not have any primary key and the room number attribute here acts only as a discriminator and not a primary key. Now let us know about attributes. Attribute. An attribute exhibits the properties of an entity. An attribute is illustrated with an oval shape in any diagram. In the example below, student is an entity and the properties of student such as address, age, name and role number are called its attributes. Let's see our first classification under attribute that is key attribute. The key attribute uniquely identifies an entity from an entity set. The text of a key attribute is underlined. In the example below, we have a student entity and it has attributes name, address, role number and age. But here role number can uniquely identify a student from a set of students. That's why it is termed as a key attribute. Now we will see composite attribute. An attribute that is composed of several other attributes is known as a composite attribute. An oval showcases the composite attribute and the composite attribute oval is further connected with other os. In the example below we can see an attribute name which can have further subp parts such as first name, middle name and last name. These attributes with further classification is known as composite attribute. Now let's have a look at multivalued attribute. An attribute that can possess more than one value are called multivalued attributes. These are represented as double oval shape. In the example below the student entity has attributes phone number, role number, name and age. Out of these attributes, phone number can have more than one entry and the attribute with more than one value is called multivalued attribute. Let's see derived attribute. An attribute that can be derived from other attributes of the entity is known as a derived attribute. In the year diagram, the derived attribute is represented by dashed over. And in the example below, student entity has both date of birth and age as attributes. Here age is a derived attribute as it can be derived by subtracting current date from the student date of birth. Now after knowing attributes let's understand relationship in ER diagram. A relationship is showcased by the diamond shape in the year diagram. It depicts the relationship between two entities. In the below example student study course. Here both student and course are entities and studies the relationship between them. Now let's go through the type of relationship. First is onetoone relationship. When a single element of an entity is associated with a single element of another entity, this is called onetoone relationship. In the example below, we have student and identification card as entities. We can see a student has only one identification card and an identification card is given to one student. It represents a onetoone relationship. Let's see the second one. One to many relationship. When a single element of an entity is associated with more than one element of another entity is called one to many relationship. In the below example, a customer can place many orders. But a particular order cannot be placed by many customers. Now we will have a look at many to one relationship. When more than one element of an entity is related to a single element of another entity, it is called many to one relationship. For example, students have to opt for a single course. But a course can be opted by number of students. Let's see many to many relationship. When more than one element of an entity is associated with more than one element of another entity is called many to many relationship. For example, an employee can be assigned to many projects and many employees can be assigned to a particular project. Now after having an understanding of Ver diagram. Let us know the points to keep in mind while creating the year diagram. First identify all the entities in the system embed all the entities in a rectangular shape and label them appropriately. This could be a customer, a manager, an order, an invoice, a schedule, etc. Identify relationships between entities and connect them using a diamond in the middle illustrating the relationship. Do not connect relationships. Connect attributes with entities and label them appropriately and the attribute should be in oval shape. Assure that each entity only appears a single time and eradicate any redundant entities or relationships in the EI diagram. Make sure your EI diagram supports all the data provided to design the database. Make effective use of colors to highlight key areas in your diagrams. There are mainly four types of SQL commands. So first we have data definition language or DDL. So DDL commands change the structure of the table like creating a table, deleting a table or altering a table. All the commands of DDL are autocommitted which means it permanently save all the changes in the database. We have create, alter, drop and truncate as DDL commands. Next we have data manipulation language or DML. So DML commands are used to modify a database. It is responsible for all forms of changes in the database. DML commands are not autocommitted which means it can't permanently save all the changes in the database. We have select, update, delete and insert as DML commands. Now select command is also referred to as DQL or data query language. Third, we have data control language or DCL. So DCL commands allow you to control access to data within the database. These DCL commands are normally used to create objects related to user access and also control the distribution of privileges among users. So we have grant and revoke which are the examples of data control language. Finally we have something called as transaction control language or TCL. So TCL commands allow the user to manage database transactions. Commit and roll back are example of TCL. Now let's see the basic SQL command structure. So first we have the select statement. So here you specify the various column names that you want to fetch from the table. We write the table name using the from statement. Next we have the where clause to filter out a table based on some conditions. So you can see here where condition one, condition two and so on. Then we have the group by clause that takes various column names. So you can write group by column 1, column 2 and so on. Next we have the having clause to filter out tables based on groups. Finally we have the order by clause to filter out the result in ascending or descending order. Now talking about the various data types in SQL. So we have exact numeric which has integer, small int, bit and decimal. Then we have approximate numeric which are float and real. Then we have some date and time data types such as date, time, time stamp and others. Then we have string data type which includes car this war car and text. Finally we have binary data types and binary data types have binary v binary and image. Now let's see some of the various operators that are present in SQL. So first we have our basic arithmetic operators. So you have addition, the subtraction, multiplication, division and modulus. Then we have some logical operators like all, and any, or, between, exists and so on. Finally, we have some comparison operators such as equal to, not equal to, there's greater than, less than, greater than, equal to, or less than equal to, not less than or not greater than. Now let me take you to my MySQL workbench where we will learn to write some of the important SQL commands use different statements, functions, data types and operators that we just learned. In this session, we will learn how to install MySQL workbench and then we will run some commands. Firstly, we will visit the official Oracle website that is mysql.com. And now we'll move to the downloads page. Now scroll down and click on MySQL GPL downloads. Now under community downloads, click on MySQL installer for Windows. The current versions are available to download. I will choose this installer and click the download button. Now here just click on no thanks. Just start my download. Once the installer has downloaded, open it. You may be prompted for permission. Click yes. This opens the installer. You'll be asked to choose the setup type. We will go with custom. Click next. Now you have to select the products you want to install. We will install only the MySQL server, MySQL shell, and the MySQL workbench. Expand MySQL servers by double clicking on it and choose the version you want to install and click on this arrow. Now you have to do the same thing for applications. Expand applications and choose the MySQL Workbench version you want to install and click on the arrow. We'll do the same thing for MySQL shell. We'll choose the latest version. Click on the arrow. So these are the products that have to be installed in a system. Now we'll click next. Now click execute to download and install the server. This may take a while depending on your internet speed. As the download is completed, click next. Now you see the product configuration. Click next. Now we'll configure our SQL server instance. Here we will go with the default settings and click next. And under authentication, select use strong password encryption for authentication which is recommended and click on next. Now here set the password for your root user. By the way, root is the default user. This user will have access to everything. I will set my password. Now I'll click on next. And here also we'll keep the default settings and click on next. Now to apply configuration we will execute the process. Once all the configuration steps are complete click finish. Now you will see the installation is complete. It will launch MySQL workbench and MySQL shell. After clicking on finish. Now the shell and workbench has started. Now we'll connect by clicking on the root user. It will ask for a password. Enter the password and it will connect successfully. >> [clears throat] >> Yeah, the work page has started. Now we'll just connect the server. So first we'll open command prompt. Now we'll reach the path where my files are present. I'm going to this PC local C program files MySQL MySQL server 8.0 bin and I'll copy this path. [clears throat] I'll open the command prompt and write a command cd space and paste the link and press enter. Now we'll write another command that will be my SQL minus u space root minus t and press enter. Now it will ask for your password. Just enter the password [clears throat] and press enter. Now the server has started and now we'll see some commands in MySQL workbench. First we will open MySQL workbench. Now we'll click on the local instance MySQL 80 and enter the password to connect to the local host. Now the MySQL workbench has started. Now we will see some commands. The first command we will see is show databases. Show databases semicolon. And now we will select the whole command and click on this execute button. And here we will see the result in the result grid. These are the databases that are stored already in the database. Now there are four databases that is information schema, MySQL, performance schema and SS. Now we will select one of the database. We'll use uh MySQL. Now we have selected the MySQL database and now in this database we will see which tables are stored in this MySQL database. To see that we'll run a command show tables. We'll select the command and click on the execute button. These are the tables that are stored in this MySQL database that is columns private component DB and much more. Now let me now go ahead and open my MySQL workbench. So in the search bar I'll search for MySQL Workbench. You can see I'm using the 8.0 version. I'll click on it. And here it says welcome to MySQL Workbench. And below under connections you can see I have already created a connection which says local instance. Then you have the root the local host and the port number. Let me click on it. You can see the service the username is root and I'll enter my password and hit okay. Now this will open the SQL editor. So this is how the MySQL workbench looks like. Here we learn some of the basic SQL commands. So first let me show you the databases that are already present. So the command is so databases. You can hit tab to autocomplete. I'll use a semicolon. I'll select this. And here you on the top you can see the execute button. So if I run this below you can see the output. It says show databases. Seven rows are returned which means currently there are seven databases. You can see the names. All right. Now, let's say I want to see the tables that are present inside this database called world. So, I'll use the command use world, which is the database name. Now, let me run it. So, currently I'm using the world database. So to display the tables that are present in the world database I can use the show command and write show tables give a semicolon and I'll hit control enter this time to run it. All right so you can see the tables that are present inside this world database. So we have three tables in total city country and country language. Now if you are to see the rows that are present in one of the tables you can use the select command. So I'll write select star which basically means I want to display all the columns. So star here means to display all the columns. Then I'll write my from the table name that is city. So this command is going to display me all the rows that are present inside the city table. So if I hit control enter. All right. You can see the message here. It says 1 th00and rows were returned which means there were total thousand records present inside the city table. So here you can see there's an ID column, a name column, this country code, district and population. All right. Similarly, you can check the structure of the table by using the describe command. So I'll write describe and then I'll give the table name that is city. Now let's just run it. There you go. The field shows the column names. So we have ID, name, country code, district, population. Type here shows the data type of each of the columns. So district is character 20. ID is an integer. Population is also integer. Null says yes or no, which means if no, then there are no null values. If it's yes, which means there are null values in your table. Key here represents whether you have any primary key or foreign key. And these are some extra information. Now let's learn how to create a table in MySQL. So I'll use the create table command for this. And before that let me create a database and I'll name it as SQL intro. So the command is create database and I'll give my database name that is SQL intro. Let me give a semicolon and hit control enter. So you can see I have created a new database. Now if I run this command that is show databases. You can see this newly created database that is SQL intro. If I scroll down there you go. You can see the name here SQL intro. Okay. Now within this database we'll create a table called employee details. Now this will have the details of some employees. So let me first show you how to create a table that will be present inside the SQL intro database. So I'll use the command create table and then I'll give my table name that is going to be employee details. Next the syntax is to give the column names. So my first column would be the name column which is basically the employee name followed by the data type for this column since name is a text column. So I'll use var and I'll give a value of 25. So it can hold only 25 characters. Okay. Next I also want the age of the employee. Now age is always an integer. So I'll give int. Okay. Then we can have the gender of the employee. So gender can be represented as F or M. F for female and M for male. So I'm using the car data type or character data type and I'll give the value as one. Then let's have the date of join or DOJ and this is going to be of data type date. All right. Next we'll have the city name that is the city to which the employee belongs to. So again this is going to be worker 15. Finally we'll have a salary column and salary we'll keep it as float since salary can be in decimal numbers as well. Now I'll give a semicolon. All right. So let me just quickly run through it. So first I wrote my create command then the table which is also a keyword followed by the table name which is employee details here and then we give the column names such as name, age, this gender, date of join, city and salary. For each of the columns we also give the data type. All right. So let me just run it. Okay. So here you can see we have successfully created our first table. Now you can use the describe command to see the structure of the table. I'll write describe empetails. If I run this there you go. So under field you can see the column names. Then you have the data types. Null represents if the table can accept null values or not. And these are basically empty and we haven't set any default constraint. All right, moving ahead. Now let's learn to add data to our table using the insert command. So on a notepad, I have already written my insert statement. So let me just copy it and then I'll explain it one by one. All right. So if you see this so we have used an insert into statement or a command followed by the table name that is emp details. Then this is the syntax using values I have passed in all the records. So first we have Jimmy which is the name of the employee. Then we have 35 which basically represents the age. Then M means the gender or the sex. Then we have the date of join. Next we have the city to which the employee belongs to and finally we have the salary of the employee. So this particular information represents one record or a tuple. Similarly the next employee we have is Shane. You can see the age and other information. Then we have Mary Dwayne Sara and Ammy. All right. So let me go ahead and run this. So this will help you insert the values in the table that you have created. You can see we have successfully inserted six records. Now to display the records, let me use the select statement. So I'm using select star from EMP details. If I run this, you can see my table here and the values it has. So we have the name column, the age column, the state of join, city, salary and these are the values that you can see here. Moving ahead now let's say you want to see the unique city names present in the table. So in this case you can use the distinct keyword along with the column name in the select statement. So let me show you how you can print the distinct city names that are present in our table. Now if you notice this table clearly we have Chicago, Seattle, Boston, Austin, there's New York and there's Seattle repeated again. So I only want to print the unique values. So for that I can write my select statement as select distinct. Then I'll give my column name which is city from my table name that is EMP details. If I run this, you can see my query has returned five rows and these are the values. So we have Chicago, Seattle which was repeated twice is just been shown once. Then we have Boston, Austin and New York. Now let's see how you can use inbuilt aggregate functions in SQL. So suppose you want to count the number of employees in the table. In that case you can use the count function in the select statement. So let me show you how to do that. So I'll write select. I'll use my function name that is count. Now since I want to know the total number of employees, I'm going to use their name inside the brackets from employee details. Now if I run this, this will return the total number of employees that are present in the table. So we have six employees in total. Now if you see here in the result it says count name. Now this column is actually not readable at all. So what SQL provides is something called as an alias name. So you can give an alias to the resultant output. So here I can write select count of name and use an alias as as I can give an alias as count name and run this statement again. There you go. You can see here in the resultant output we have the column name as count name which was our alias name. Now suppose you want to get the total sum of salaries you can use another aggregate function called sum. So I'll write my select statement and this time instead of count I'm going to write sum and since I want to find the sum of salaries. So inside the bracket I'll give my salary column from my table name that is employee details. If I run this, this will result in the total sum of salaries. So basically it adds up all the salaries that were present in the salary column. Now let's say you want to find the average salary. So instead of sum you can write the average function which is avg. So this will give you the average salary from the column salary. So you can see it here. This says average salary. Now if you want you can give an alias name to this as well. Now you can select specific columns from the table by using the column names in the select statement. So initially we were selecting all the columns. For example like you saw here the star represents that we want to see all the columns from the employee details table. Now suppose you want to see only specific columns. You can mention those column names in the select statement. So let's say I want to select just the name, age and the city column from my table that is employee details. So this will result in displaying only the name, age and city column from the table. If I run it, there you go. It has given only three columns to me. Now SQL has a wear clause to filter rows based on a particular condition. So if you want to filter your table based on a specific conditions, you can use wear clause. Now wear clause comes after you give your table name. So suppose you want to find the employees with age greater than 30. In this case, you can use a wear clause. So let me show you how to do it. I'll write select star from my table name that is employee details. And after this I'll use my wear clause. So I'll write where age greater than 30. If I run this, it will give me the output where the age is only greater than 30. So I excluded everything that is less than 30. So we have four employees whose age is greater than 30 here. Now suppose you want to find only female employees from the table. You can also use a wear clause here. So I'll write select. Let's say I want only the name the gender which is sex here, city from my table that is employee details where I'll give my column name that is sex is equal to since I want only the female employees I'll give f and run this statement. Okay, you can see here our employee table has three female employees. Now suppose you want to find the details of the employees who belong to Chicago or Austin. In this case, you can use the or operator. Now the or operator in SQL displays a record if any of the condition separated by or is true. So let me show you what I mean. So since I want the employees who are from Chicago and Austin, I can use an or operator. So I'll write select star from EMP details which is my table name. Then I'll give my where clause where city equal to I'll give my city name as Chicago and then I'm going to use the or operator or city equal to I'll write Austin. I'll give a semicolon and let me run it. There you go. So in the output you can see all the employees who belong to the city Chicago and Austin. Now there is another way to write the same SQL query. So you can use an inop operator to specify multiple conditions. So let me just copy this and instead of using the or operator this time I'm going to use the in operator. So I'll delete this. After the wear clause, I'm going to write where city and use the in operator. Inside bracket, I'll give my city names as Chicago and I want Austin. So, I'll give a comma and write my next city name that is Austin. So, this query is exactly the same that we wrote on top. Let me run this. You will get the same output. There you go. So we have Jimmy and Dwayne who are from Chicago and Austin respectively. Now SQL provides the between operator that selects values within a given range. The values can be numbers, text or dates. Now suppose you want to find the employees whose date of join was between 1st of Jan 2000 and 31st of December 2010. So let me show you how to do it. I'll write select star from emp details where my date of join that is doj between I'll give my two date values that is 1st of Jan 2000 and I'll give my second value the date value that 31st of December 2010. So every employee who has joined between these two dates will be displayed in the output. If I run it, we have two employees who had joined between 2000 and 2010. So we have Jimmy and Mary here who had joined in 2005 and 2009 respectively. All right. Now in wear clause you can use the and operator to specify multiple conditions. Now the and operator displays a record if all the conditions separated by and are true. So let me show you an example. I'll write select star from employee details table where I want the age to be greater than 30 and I want sex to be male. All right. So here you can see I have specified two conditions. So if both the conditions are true only then it will result in an output. If I run it, you can see there are two employees who are male and their age is greater than 30. Now let's talk about the group by statement in SQL. So the group by statement groups rows that have the same values into summary rows. Like for example, you want to find the average salary of customers in each department. Now the group by statement is often used with aggregate functions such as count, sum, and average to group the result set into one or more columns. Let's say we want to find the total salary of employees based on the gender. So in this case, you can use the group by clause. So I'll write select let's say sex comma I want to find the total sum of salary as I'll give an alias name let's say total salary from my table name that is employee details next I'm going to group it by sex Okay, let me run it. There you go. So, we have two genders, male and female. And here you can see the total salary. So, what this SQL statement did was first it grouped all the employees based on the gender and then it found the total salary. Now, SQL provides the order by keyword to sort the result set in ascending or descending order. Now, the order by keyword sorts the records in ascending order by default. To sort the records in descending order, you can use the deesc keyword. So let's say I want to sort my employee details table in terms of salary. So I'll write select star from empore details and I'll use my order by clause on the salary column. So this will sort all the records in ascending order of their salary which is by default. You can see the salary column is sorted in ascending order. Now suppose you want to sort the salary column and display it in descending order. You can use this keyword that is deesc. Let me run it. You can see the output. Now this time the salary is sorted in descending order and you have the other values as well. Now let me show you some basic operations that you can do using the select statement. So suppose I write select and do an addition operation. Let's say 10 + 20 and I'll give an alias name as addition. If I run this, it will give me the sum of 10 and 20 that is 30. Similarly, you can use the subtraction operator and you can change the alias name as let's say subtract. Let's run it. You get minus 10. Now there are some basic inbuilt functions. There are a lot of inbuilt functions in SQL but here I'll show you a few. Suppose you want to find the length of a text or a string. You can use the length function. So I'll write select and then use the length function. I'll hit tab to autocomplete. Let's say I want to find the length of country India and I'll give an alias as total length. If I run it, you see here it returns five because there are five letters in India. There's another function called repeat. So let me show you how repeat works. So I'll write select repeat. Let's say I want to repeat the symbol that is at the rate. I'll put it in single quotes because it is a text character. And I want to repeat this character for 10 times. Close the bracket and let's run it. You can see here in the output it has printed at the rate 10 times. You can count it. All right. Now let's say you want to convert a text or a string to uppercase or lower case. You can do that as well. So I'll write select and use the function called upper. Let's say I want to convert my string that is India to uppercase. I'm not giving in any alias name. If I run this,