Forem: Yusra Liaqat

churn prediction in telecom industry using python

Yusra Liaqat — Thu, 15 Jun 2023 07:54:10 +0000

I’m going to create a program that can predict customer churn
Customer churn occurs when subscribers or customers stop doing business with the company within a specific amount of time and retaining customer is more important for companies because it boost company’s revenue and Help Company to build meaningful relationship with the company.
In actual, customer retention is more valuable than customer acquisition.

Python Program to Predict Customer Churn

Import Libraries

import pandas as pd
import numpy as np
import sklearn
import matplotlib.pyplot as plt
import seaborn as sns
from sklearn.preprocessing import LabelEncoder
from sklearn.preprocessing import StandardScaler
from sklearn.metrics import classification_report
from sklearn.linear_model import LogisticRegression
from sklearn.model_selection import train_test_split

Load the Dataset In Python

Now, I’m going to load my dataset so I’ve to use Google’s library to do this
Following are the commands to import data in colaboratory

from google.colab import files
uploaded = files.upload()

After running this cell click on choose file and upload the file that you wanted to upload from your customer churn data

Load the Data Into A Data Frame

Here, I’ve created a variable called DF which will be short for data frame. Here I want to look at the data specifically and the first five rows of data.

df = pd.read_csv('WA_Fn-UseC_-Telco-Customer-Churn.csv')
df.head(7)

And now we are able to take a look at the data frame. As we can see we have all of our columns on the top and also up to the bottom we can see our target column which is called “churn”. And we can see the values in this column appears to be yes and no. so no would mean that customer did not churn and off course yes would mean that customer did churn and each row is a costumer.

Show the Number Of Rows And Columns

So just type,

df.shape

(7043, 21)

And we can see that there is seven thousand forty-three rows or customers in this data set and there are twenty one columns or data points on each customer.

Show All of The Columns

df.columns.values

array(['customerID', 'gender', 'SeniorCitizen', 'Partner', 'Dependents', 'tenure', 'PhoneService', 'MultipleLines', 'InternetService', 'OnlineSecurity', 'OnlineBackup', 'DeviceProtection', 'TechSupport', 'StreamingTV', 'StreamingMovies', 'Contract', 'PaperlessBilling', 'PaymentMethod', 'MonthlyCharges', 'TotalCharges', 'Churn'], dtype=object)

So, here we can see all of the columns name and immediately I can see some interesting columns like customer Id, gender, phone service, internet service, contract, monthly charges, tenure and obviously churn

Check For Missing Data or NA Values

So in order to do this just type,

df. isna(). sum()

After running the cell I can see the columns name in the left and number for missing values for each column on the right and right every single value is zero so this tells me that this data has no missing values.

Show Some Statistics

So, to show some statistics just type here,

df.describe()

And here we get some statistics on the data set. So immediately we can see that the maximum tenure was 72 months which is about six years and the minimum tenure was zero months and the mean tenure was 32 points months. Now the maximum monthly charges was 118 dollars with some points for a customer and minimum monthly charge was 18 dollars 250000 cents for a customer and the mean was 64.761692 dollars for the customers

Get Customer Churn Count

My first question is, how many people are churning and how many people are not churning or being retained and stained. So, in order to get that count I just have to type,

df['Churn'] . value_counts()

Now we can see that five thousand one hundred and seventy four customers for this company did not churn and one thousand eight hundred sixty nine customers of this company will churn

Visualize he Count of Customer Churn

So in order to this just type,

sns.countplot(df['Churn'])

Now we can visually see those same counts as bar chart here so it make it little bit more obvious that there are customers that are staying with the company then there are the customer that will left the company.

What Is The Percentage Of The Customers That Are Leaving?

num_retained = df[df.Churn == 'No']. shape[0]
num_Churned = df[df.Churn == 'yes']. shape[0]

#print the percentages of customer that stayed
print( num_retained / (num_retained + num_Churned) * 100, '% of customers stayed with the company.')

#print the percentages of customer that left
print( num_Churned / (num_retained + num_Churned) * 100, '% of customers left with the company.')

73.0 % of customers stayed with the company.
26.0 % of customers left with the company.

As we can see that seventy three percent of the customers stayed with the company and 26 percent people left the company. So, this is important because this tells me that if randomly a customer from this data set and if I had to make a guess that if the customer churn or did not churn usually you would have 50/50 chance but based of this data is very unbalanced. So, I would have a better chance of making a guess that customer stayed with the company for every customer that i randomly choose and by the time I’m done going through the whole data set I would get 73 percent of the customers. So, the model that I built must be better than 73 percent.

Visualize the Churn Count For Both Male And Female

Now I want to visualize the churn count for both male and female. So just type,

sns.countplot(x='gender', hue= 'Churn', data = df)

As we can see there’s doesn’t seem to be a much difference here so it doesn’t look like more males or more females left this company or stay with the company, they seem about even based of this chart. So maybe we don’t want to take a look at the gender to figure out why these customers are the company. I don’t think so gender has anything to do with it.

Visualize the Churn Count For Internet Service

Now we are going to visualize the churn count for internet service. So just type,

sns.countplot( x='InternetService', hue='Churn', data = df)

Now we are getting back something useful so as immediately we can see that the highest number of customer that didn’t churn have dsl internet service and the highest count for the customer that did churn have fiber optic internet service.
Let’s take a look at some of the other columns. So, for this I’m gonna create,

numerical_features = ['tenure', 'MonthlyCharges']
fig, ax = plt.subplots(1, 2, figsize=(28,8))
df[df.Churn =='No'][numerical_features].hist(bins=20, color='blue', alpha=0.5, ax = ax)
df[df.Churn =='Yes'][numerical_features].hist(bins=20, color='orange', alpha=0.5, ax = ax)

So, here I’m gonna look at the two columns which are tenure and monthly charges because they are pretty interesting from the beginning and this time I’ve used histogram instead of bar chart. As we can see that most of the customers that are staying have monthly charges somewhere between 20 and about 30 dollars and that’s very obvious by this big amount on the top graph and what we can also see is that the churn count is a lot higher somewhere between 70 and about 100 dollars for monthly charges. So, that’s seems fairly obvious.
And then we’ll look at the tenure and we can see that most of the customer that churn somewhere between zero and about ten months and for the customers that didn’t churn they seem to basically have a higher tenure so it seems to be somewhere between 65 and 72 months. So, the ones that left have a lower tenure and the ones that stayed have a higher tenure.

Remove Unnecessary Columns

As I can see that customer ID column will be useless for the model so, let’s get rid of this column from our data set. So, I’ve created some variable as follows;

cleaned_df =df.drop('customerID', axis =1)

Look At the Numbers Of Rows And Columns In Data Set

We can look at the numbers of rows and columns in the data set by running following command;

cleaned_df.shape

So, now I can see that there are seven thousand and forty-three rows still but now there’s only 20 rows where before it was 21 columns.

Convert All Of The Non-Numeric Columns To Numeric

Following are the commands to convert non numeric to numeric values;

from sklearn.preprocessing import LabelEncoder

# select only columns with object data types (categorical variables)
cat_cols = cleaned_df.select_dtypes(include=['object']).columns

# convert object data types to categorical codes
for col in cat_cols:
    cleaned_df[col] = cleaned_df[col].astype('category').cat.codes

# convert the remaining numeric columns to float
num_cols = cleaned_df.select_dtypes(include=['float', 'int']).columns
cleaned_df[num_cols] = cleaned_df[num_cols].astype('float')

Show the New Dataset Data Types

Now let’s take a look at the new data set and data types. So, just type;

cleaned_df.dtypes

So now we can see that all the data types for our dataset are numbers.

Show The First Five Rows Of The New Data Set

In this cell I want to show the first five rows our new dataset. So, just type;

cleaned_df.head()

Now we can see, a sample of the data are all numbers as expected.

Scaled the Data

So, I’m gonna create the feature dataset which will be called X and it will be contained all of the columns from DF data except the column churn which is our target.

X = cleaned_df.drop('Churn', axis= 1) #Feature data set
y = cleaned_df['Churn'] #target data set

X = StandardScaler().fit_transform(X)

Split the Data Into 80% Training And 20% Testing

Here, I’m going to split the data into eighty percent training and twenty percent testing, so let’s create variables as:

x_train, x_test, y_train, y_test = train_test_split(X, y, test_size=0.2, random_state = 42)

Create the Model

Now let’s create a variable called model and set it equal to logistic regression as follows:

# create the model
model = LogisticRegression()
#train the model
model.fit(x_train, y_train)

after running these commands, we’ve got following results;

Create the Prediction on the Test Data

In order to make some predictions, let’s create a model and set it equals to as follows:

predictions = model.predict(x_test)

#print the predictions
print(predictions)

After running the commands and getting above results, obviously we can’t see all of the data from printing it but if you want then you can go one by one and look at each prediction for the test data and compare it with the target test data

Check the Precision, Recall, F1-Score

Here I’m going to check the precision, recall and f1-score for our model;

print(classification_report(y_test, predictions))

Let’s run this and see how well the model did?

So, now we can see that our model has about 91% recall which is really good and it has 85% precision which is not bad and as an f1-score of 88% which is pretty good because maximum f1-score can be one hundred percent. And it has accuracy of about 82% which is better than the 73% from just guessing. So, this model will be very useful because it’s better than guessing.

Basic Concepts of Data Analytics

Yusra Liaqat — Fri, 04 Nov 2022 16:21:59 +0000

Introduction:

In this article we'll be looking at the different concept behind data analytics. It will provide you with the clearer understanding of what data analytics and how it allow you to collect, store, review and analyze data to help derive business decisions through insights that have been identified.

Who should read this article?
This article is written to provide you the foundation to data analytics and is ideal for those looking to become data analyst or data scientist

Objectives:

The objectives of this course is to provide you with the understanding of different analytics concepts.
Data types: including structured, semi structured and unstructured data
When you should use data analytics within your business
The process behind running analytics against data

Introduction to Data Analytics:

In this article, I'll provide you with a basic understanding of different concepts to help you understand the concept behind many of the AWS services and architectures used to implement data analysis.

Simply speaking, analytics or data analytics is the science of data transformation, transforming data into meaningful information and insights.
Here we refer data as any input you have like a spreadsheet, a CSV file, historic sales information, a database, raw research data, essentially any data that you may have.

With this basic concepts I mind let’s explore a bit more about data analytics concepts.

Starts with a question:
Everything start with the question to the problem we have, using analytics we want to solve these problems by selecting the right tools to collect or clean the data in an appropriate fashion.

Types of data:

As an input for our analytics, we may have data which can be organized into different categories. For example,

Qualitative data
Quantitative data
Structured data
Semi structured data
Un structured data

If you are new to these concepts and you don’t hear these terms before then don’t worry ill be explaining next in my article.
In this article we will understand which AWS services can be used in the analytics process.

Data types:

Here let’s take a little bit about the input. We have two basics data types where the data is organized

Quantitative Data:
Quantitative refers to numbers, the amount of certain values, like the number of citizen in a given geographical area.
Example: no. of students in class = 16
Boys= 8
Girls= 8

Qualitative data:
Qualitative data refers to attributes of the population not expressed in direct numbers but to qualify their attributes like,

Eye color
Satisfaction level

Main data types:

We also have three main classifications for the data formats which are following

Structured data:
Structured data referred to data with a defined data model like SQL databases, where tables have a fixed DB model and schema. On AWS for example, the RDS or Rational databases services is a complete example of a structured store.

Semi- structured data:
In semi structured data we basically have a flexible data model or tagging mechanism that allows a semantic organization and some kind of hierarchy discovery from the data without having the fixed and rigid rules from SQL database. Non- SQL databases can also be structured but usually they are used in flexible way to complement the limitations from Amazon S3 to SQL databases. Amazon DynamoDB allows each record to have a different numbers of columns but gives fixed indexes for searching. This provides a very flexible schema
Eg. Non-sql, XML, JSON and CSV files are good examples of semi-structured data.

Unstructured data:
And lastly, we have the un structured data where all kind of text information without a data model is classified. Here we have all kind of documents without a proper data model or a schema like;
Books, natural language processing and all sorts of text processing.

Data generation has exploded exponentially in the past decade. We generate data from the moment we wake up to the moment we go to bed or even while sleeping, sensors can be collecting data from our body and environment to improve a series of apps and services. It could be suggested that we are generating too much data much more than we can probably analyze.

When to use data analytics:
To help us define if we need to use data analytics services to find the answers of our problems that are locked within our data. We can look at the different factors
Volume: the first is the volume which refers to the size of the data set or as we usually call it the data size. And size matters in order to decide the right tool to analyze it. Usually, a big data problem will go into scale from gigabits to petabytes of data.

Velocity: There is also what we call the velocity of the data, which indicates how quickly you need to get your answers and is also related to the age of the data. For example, historical records from previous years or real-time alerting and information. This has a great impact on the tools used to analyze the data because, depending on the response time you need, are you comfortable with real-time responses or waiting? By knowing this, we can choose the right tool and technique.
Variety: this refers to the variety of the source data classification; if its structured or unstructured. As often analytic problems will have sources from several types like.

Business intelligence platform data, blogs, CSV data, texts and any sorts of structured or unstructured data.

ANALYTICS TYPES:

The power of analytics grows when we move from batch analytics to real-time analytics and then to predictive analytics, but as always the problem you are trying to resolve will always dictate the best method.

*Batch analytics: * In reporting or BI analysis, data is processed to a job and the results are presented after a period of time. We have years of data in your data warehouse or in log files, in spreadsheets, and we want to find interesting patterns in this data such as potential sales, potential profits, or potential insights from research data.

Real-time analytics: When it comes to real-time analytics, we need to get the answers as soon as possible. If you lose time, there can be serious consequences like fast response to security alerts from intrusion detection systems or responses to advertising campaigns.

Predictive analytics: The last type of data analytics is predictive analytics, which take historical data as an input, it then learn from the history and then leaves us predictions for future behaviors. This is a common case for machine learning like spam detection where based on past behavior we identify malicious messages, predicting and avoiding spam messages.

Data analytic process:

First of all, when you have a problem you usually define it as a question to start your journey into the analytics field. With your question ready, you need the source data which is effectively your starting point. This can be a data warehouse database, rational database tables or a NoSQL store, a csv file, books, text files. In short, every readable format can be used as an input. Selecting the input will depend on the answers you are trying to return from your problem or question.

For example:
The problem might be to count words in book from Shakespeare or on the other end of the scale, the problem might be to analyze DNA to find patterns. So the type of problem will dictate the data and also the processing algorithm.

With your input ready, you need to store it in an accessible place for the tools to then process it, analyze and return the results. The separation using process and analysis is based on the fact that some analytics solutions from AWS will require a previous cleaning or pre-processing from the data for better results and accuracy.

AWS has structured its portfolio around the collect, store, analyze and visualize the methodology for each step which integrated services to perform each function.

Collect/ingest:

The first step in your analytics process is to collect the data that you want to use as an input. The data collection is also called ingestion which is the act of acquiring data and storing it for later usage. In the data collection, we have different type of ingested data. We can have transactional data which is represented by traditional rational databases, reads and writes. We can also have file ingestion reading data from file sources such as like logs, texts, CSV files, book contents and so on and you can have also stream data represented by any kind of streamed content like a clickstream and events on a website, internet of things devices and so on.
The toolset AWS currently offers can ingest data from many different sources. For example; with kinesis streams or firehose we can work easily with streamed data on any source even if they are on-premises.
Store:

After the data is generated or acquired, we need to store it in an accessible place for AWS. This is usually called a data lake. The big pool where your services go to get the source and to deliver back the results. Amazon S3 is one of the core storage services from AWS. As a highly durable object store integrated seamlessly with all other AWS analytic services for data loading ad store. You can also have data on Amazon RDS if the data has structured format or a Redshift. If it has no fixed data model but a basic structure, we can use DynamoDB, the NoSQL solution from AWS TO STORE IT. And if your data has very infrequent access we can use glacier, the archive service from AWS.

Analyze/process:

Batch
Real-time
Predictive Remember the right service or tool depends on the type of problem you have and the velocity of your replies. If you can wait for a while or if you need real-time answers to the problems and if you want to predict future behaviors. Amazon EMR

If your goal is to provide reports based on

Batch processing,
Historical data Analysis
Identifying patterns on large data sets

If you need real-time replies for questions or the results must be displayed on live dashboards, then you might take advantage of stream-based processing with amazon Kinesis, AWS Lambda or Amazon OpenSearch. Kinesis provides streams to load, store and easily consume live stream data and AWS Lambda can protect to theses streams events, executing functions you define.

For predictive analytics where you need to forecast an event based on historical cases, you might take advantage of Amazon Machine Learning services to build highly available predicting applications. Not forgetting data pipeline which can be used orchestrate all these services. As a frame work for data-driven workflows, data pipeline can be used to automate your database loads and for the visualization aspect to get a nice overview or dashboard from you replies you can use Amazon QuickSight which allows you to create rich visualization from your data.

conclusion:

That brings end to this introductory article and now you should have a greater understanding of the Basic concepts behind Data Analytics.
We have covered all the basic concepts of Data Analytics in this article. Thanks for taking the time to read this article. I hope you found it interesting and informative. I hope you understand what is Data Analytics, Analytics types, When you should use data analytics within your business? and The process behind running analytics against data. I hope you enjoy reading this article.

Please share your feedback with us as well.
Thank YOU!!

MySQL Queries: Basics of MySQL

Yusra Liaqat — Fri, 09 Sep 2022 06:30:22 +0000

In this article, We will explore the basics of MySQL query language for beginners to gain a better understanding of MySQL.

content:

• Introduction to SQL
• Data and Database
• Table
• SQL basic queries

Introduction to MySQL:

Modern society is extremely dependent on data, but in order to manage it effectively, one must be able to master data management. A database and data can only be manipulated if we know the language that this database understands, which SQL is.
As the core of relational databases, SQL is used by most companies around the world because of its enormous popularity.

What is SQL?

It is abbreviated as “structured query language”
It is the language used to perform actions as update, retreat, manipulate and store data on rational database.

History of SQL:

SQL was developed at IBM by Donald D. Chamberline and Raymond F. Boyce in the early 1970s.
It was initially called SEQUEL(Structured English Query Language)
SQL is a powerful language that uses simple English sentences
It uses common English words such as select, insert, update and many more to perform
SQL is declarative language( when you write a query you have to tell what needs to be done
And you don’t have to worry regarding the flow of query. It will be handled internally by DBMS using.

Features of SQL:

SQL has well defined standards
SQL is easy to learn
With the help of SQL, one can create multiple views
Probability of code in SQL is a prominent feature

What is Data and Database?

Data:

Data is a collection of facts, figures and values from different sources like as;
Transport, geographical, cultural, scientific, financial, statistical, natural etc.

Database:

"Database is allocation where data is stored in certain n formats".

Example:

An example is a library that contains a large number of books. Now imagine that the library is a database and these books are the data in it
Like as;

firstly, data from user is processed
Next, we converted into meaningful format
Then stored it as schema & raw data

Types of Databases in MySQL:

Distributed database
Object oriented database
Centralized database
Operational database
Graph database
NoSQL Database
Cloud database
Rational database

MySQL Popular databases:

Mongo DB
Words press
Microsoft axes
Microsoft SQL Server
MySQL
ORACLE

How to create a database in MySQL?

MySQL:

Currently, SQL queries are executed against the MySQL database.
It might be a mystery to you why I use MySQL, but let me explain. It's a workbench database management system, whereas SQL is a language for communicating with databases. In other words, MySQL allows us to communicate with the database by using SQL queries.
Let me explain the first topic to you, which is how to create a database, in the following syntax:
create database edureka; (keywords for database creation)

I am using edureka as the name of my database management system. Click on the lightning icon after selecting this entire statement in MySQL. You can see at the bottom that the database with the name edureka has been created.

How to delete a database?

Following that, we will discuss how to delete a database with the following syntax;

Drop database edureka; (keywords for database creation)

Select this entire query and click on the lightning icon. So, we can see that database with name edureka has been deleted

Table:

Next we are going to discuss table. Well,

“table is a collection of data in a tabular form”
Table -----> class
Name

"Person" is the name of the table here. Tables must have names when they are created.

What are tuples?

“Single row of table which contain a single record”, for that relation here we have five tuples in our table.

What are attributes?

Features of an entity is called attributes.
And attribute has a name and a data type. Here are four attributes in this table.

Table constraints in MySQL:

Despite the fact that the table stores the data, the data needs to be of a certain format to ensure data integrity. The format of the data needs to be decided at the time of table creation. The constraint must be changed if we want to change it, and we have to delete the table and create a new one with the new constraints.

So, few of the constraints are following while creating a table in MySQL;

1. Check default
2. Foreign key
3. Index
4. Unique
5. Primary key
6. Not null

MySQL commands:

MySQL queries with examples are following:

create a table command in MySQL Workbench?

create table class(
classID int not null auto_increment,
fname varchar(20),
lname varchar(20),
address varchar(30),
city varchar(15),
marks int,
primary key(classID)
);

(keywords for the creation of table in MySQL)

So the syntax for select statement is;
Select * from student; (key words to display the whole table)

It is important to ensure that the table name is unique. In a database, there cannot be two tables with the same name. Within the table, we have declared the columns along with their data types. The query must be ended with a semicolon. By clicking the lightning icon, you can now execute this query. As a result, we can see that a table named class has been created.

And by executing this we can see the table on MySQL console as follows;

Where command in MySQL:

Filtering records is done with it. By using this clause, we can extract only records that meet the specified criteria.
Let's assume the following scenario to demonstrate the where clause. For example, if I want to display the names of students who are from Sargodha, then my "where" clause query will look like this;

select fname
from class
where city='sargodha';

(key words for where query clause)
Now, select this query and execute it by clicking on the lightning icon. So, this is now our out put.

AND Command in MySQL:

This operator displays a record if all the conditions separated by AND Are TRUE.
Now, let’s look at the syntax.
Select column from table where condition1 and condition 2 is true.

select * from student
where fname='yusra' and lname='liaqat';

(key words for AND statement)
And by executing this, I can get the output for the student name yusra liaqat.

OR Command in MySQL:

OR operator usually displays a record if any of the condition separated by OR is TRUE.
And the syntax for this is follow;
Select column from table where condition1 or condition 2 is true.

select * from student
where fname='yusra' or lname='liaqat';

(key words for OR statement)

NOT Command in MySQL:

This operator displays a record if the condition/conditions are NOT TRUE.
Like, I want to display the details of student who does not have the first name as zeeshan. To achieve this I am using OR operator;

Select * from class
Where not fname= ‘zeeeshan’;

(key words for NOT statement)

INSERT INTO Command in MySQL:

In order to insert any new data into a table, we can use the INSERTY query.
The table name and columns must be specified for that. After that, we need to specify the values.
The syntax for this is as follows;

INSERT INTO  class(fname, lname, address, city, marks)
VALUES(‘ jia’,’nawaz’,#08 mg road’,’multan’,’389); 
Select * from class;

(key words for INSERT INTO statement)

MySQL Data Aggregation functions:

It is a function that groups the values of multiple rows based on certain criteria and writes a single value. We often use aggregate functions with group by and having clauses of the select statement so, we will be discussing them later part of the article.
Aggregate functions are following, so let’s discuss them one by one.

MySQL COUNT Command:

This function returns the number of rows that match by specified criteria. So the syntax is as follows;

select count (classID)
from class

; (key words count statement)

In the output we can see that the count of the student ID is 5

MySQL AVG Command:

This function returns the average value of a numeric column. So the syntax for this is as follows;

select avg (marks)
from class;

(key words for AVG statement)

LET’S EXECUTE THIS STATEMENT BECAUSE I am trying to find the average marks of students. So average marks of the student is following;

MySQL SUM Command:

This function returns the total sum of a numeric column.
And the syntax for it is;

select sum(marks)
from class;

(key words for SUM statement)

And here, in this example I am trying to the total sum of total marks code by all the students. So, select this query and execute it by clicking on the lightning icon.

MySQL MIN Command:

This function returns the minimum value of the selected column.
And the syntax for this is follows;

select fname, lname, min(marks)
from class;

(key words for SUM statement)

And here, in this example we are trying to calculate the minimum marks code by all the students. So, select this query and execute it by clicking on the lightning icon.

MySQL MAX Command:

This function returns the maximum value of the selected column.
And the syntax for it is as follows;

select fname, lname, max(marks)
from class;

(key words for SUM statement)

And here, in this example we are trying to calculate the maximum marks code by all the students. So, select this query and execute it by clicking on the lightning icon.

MySQL GROUP BY Command:

It is used in SQL to arrange identical data into groups with the help of some functions.
For instance, if the column in a table consists of similar data or values in different rows then we can use group by function to group the data and the syntax for this is as follows;

select count(classID), city
from class
group by city;

(key words for GROUP BY statement)

In this program, I am counting the number of students from different cities, and if there is a student from the same city, the count will be incremented at the end, and the output will have the city name along with the number of students from that city.
The count of students from different cities is 1, since the students come from different cities, so we can see how effective this query is if the data is large.

MySQL HAVING Command:

It is used to decide which group will be included in the final result set based on certain conditions.
Because the where keyword could not be used with aggregate functions like sum, count, min, max, and so on, SQL added the HAVING clause. The syntax for this is as follows;

Select fname, sum(marks)
From class
Group by fname
Having sum(marks)>400;

(key words for HAVING statement)

MySQL ORDER BY Command:

Ascending or descending results are sorted using this keyword.
By default, the order by keyword will sort the records ascending or descending.
Let me now explain the syntax by selecting and executing these key words.
The names of cities should be arranged in descending order based on their starting alphabets, for example. The same process will be repeated for asec order, except only the word will be exchanged with the desc.

Select count(classID), city
From class
Group by city
Order by city desc;

(key words for ORDER BY statement)

MySQL UPDATE command:

The update command is used to modify rows in a table.
Now let’s look at the syntax for update statement
Here, I want to change the name of student with student ID 1.

Update class
Set fname = ‘Amar’, lname = ‘Kumar’
Where classID = 1;

(key words for UPDATE statement)

MySQL DELETE command:

The SQL delete command is used to delete rows that are no longer required from the database tables

Delete from student
Where city = ‘sargodha’;

(key words for delete statement)

Conclusion:

We have covered all the basic concepts of MySQL in this article. Thanks for taking the time to read this article. I hope you found it interesting and informative. I hope you understand what MySQL data, a database, a table, and some basic SQL queries are. while executing these queries, I hope you guys have a lot of fun.
Please share your feedback with us as well.
Thank YOU!!