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

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