Agenda
• MYSQL Installation
• Create Database
• Create Table
• TRUNCATE
• DROP
• INSERT INTO
• WHERE
• DISTINCT
• JOINS
• INNER/LEFT/RIGHT/FULL
• Aggregate Functions
• SUM/COUNT/AVG/MIN/MAX
• GROUP BY
• HAVING
• ORDER BY
Agenda
• SQL Aliases
• SQL LIKE Operator
• SQL IN
• SQL NOT IN
• SQL Between
• SQL Null Values
• SQL LIMIT
• SQL Stored Procedures
What is Database?
Drawbacks of File System
Advantages of DBMS
Disadvantages of DBMS
Types of Databases
Relational Database
Relational Database
MySQL
• MySQL is a relational database management system (RDBMS) developed
by Oracle that is based on structured query language (SQL).
• A database is a structured collection of data. It may be anything from a
simple shopping list to a picture gallery or a place to hold the vast amounts
of information in a corporate network.
• In particular, a relational database is a digital store collecting data and
organizing it according to the relational model. In this model, tables consist
of rows and columns, and relationships between data elements all follow a
strict logical structure.
• An RDBMS is simply the set of software tools used to actually implement,
manage, and query such a database.
Installation
• https://dev.mysql.com/downloads/installer/
Installation
• https://dev.mysql.com/downloads/workbench/
CREATE Database
• CREATE DATABASE statement is used to create a new database in
SQL.
• Example Query:
This query will create a new database in SQL and name the database
as my_database.
CREATE Table
• The CREATE TABLE statement is used to create a table in SQL. We
know that a table comprises of rows and columns.
• So while creating tables we have to provide all the information to SQL
about the names of the columns, type of data to be stored in
columns, size of the data etc.
CREATE Table
CREATE Table
TRUNCATE Table
• TRUNCATE statement is a Data Definition Language (DDL) operation
that is used to mark the extents of a table for deallocation (empty for
reuse). The result of this operation quickly removes all data from a
table, typically bypassing a number of integrity enforcing
mechanisms. It was officially introduced in the SQL:2008 standard.
The TRUNCATE TABLE mytable statement is logically (though not
physically) equivalent to the DELETE FROM mytable statement
(without a WHERE clause).
TRUNCATE Table
DROP Table
• DROP is used to delete a whole database or just a table.The DROP
statement destroys the objects like an existing database, table, index,
or view.
A DROP statement in SQL removes a component from a relational
database management system (RDBMS).
DROP Command
DROP vs TRUNCATE Command
• Truncate is normally ultra-fast and its ideal for deleting data from a
temporary table.
• Truncate preserves the structure of the table for future use, unlike
drop table where the table is deleted with its full structure.
• Table or Database deletion using DROP statement cannot be
rolled back, so it must be used wisely.
INSERT INTO Statement
• The INSERT INTO statement of SQL is used to insert a new row in a
table. There are two ways of using INSERT INTO statement for
inserting rows:
1. Only values: First method is to specify only the value of data to be inserted
without the column names.
INSERT INTO Statement
2. Column names and values both: In the second method we will specify both
the columns which we want to fill and their corresponding values as shown
below:
WHERE clause
• WHERE keyword is used for fetching filtered data in a result set.
• It is used to fetch data according to a particular criteria.
• WHERE keyword can also be used to filter data by matching patterns.
• Basic Syntax: SELECT column1,column2 FROM table_name WHERE
column_name operator value;
DISTINCT clause
• The distinct keyword is used in conjunction with select keyword. It is
helpful when there is a need of avoiding duplicate values present in any
specific columns/table. When we use distinct keyword only the unique
values are fetched.
• NOTE: If distinct keyword is used with multiple columns, the distinct
combination is displayed in the result set.
JOINS
• SQL Join statement is used to combine data or rows from two or
more tables based on a common field between them. Different types
of Joins are as follows:
• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• FULL JOIN
JOINS
JOINS – INNER JOIN
• The INNER JOIN keyword selects all rows from both the tables as long
as the condition is satisfied. This keyword will create the result-set by
combining all rows from both the tables where the condition satisfies
i.e value of the common field will be the same.
JOINS – INNER JOIN
JOINS – INNER JOIN
JOINS – INNER JOIN
• Example Query
JOINS – LEFT JOIN
• This join returns all the rows of the table on the left side of the join
and matches rows for the table on the right side of the join. For the
rows for which there is no matching row on the right side, the result-
set will contain null. LEFT JOIN is also known as LEFT OUTER JOIN.
JOINS – LEFT JOIN
JOINS – LEFT JOIN
JOINS – LEFT JOIN
JOINS – RIGHT JOIN
• RIGHT JOIN is similar to LEFT JOIN. This join returns all the rows of the
table on the right side of the join and matching rows for the table on
the left side of the join. For the rows for which there is no matching
row on the left side, the result-set will contain null. RIGHT JOIN is also
known as RIGHT OUTER JOIN.
JOINS – RIGHT JOIN
JOINS – RIGHT JOIN
JOINS – RIGHT JOIN
JOINS – FULL JOIN
• FULL JOIN creates the result-set by combining results of both LEFT
JOIN and RIGHT JOIN. The result-set will contain all the rows from
both tables. For the rows for which there is no matching, the result-
set will contain NULL values.
JOINS – FULL JOIN
JOINS – FULL JOIN
Aggregate Functions
• In database management an aggregate function is a function where
the values of multiple rows are grouped together as input on certain
criteria to form a single value of more significant meaning.
Aggregate Functions
Aggregate Functions
• Count():
Count(*): Returns total number of records .i.e 6.
Count(salary): Return number of Non Null values over the column salary. i.e 5.
Count(Distinct Salary): Return number of distinct Non Null values over the
column salary .i.e 4
Aggregate Functions
• Sum():
sum(salary): Sum all Non Null values of Column salary i.e., 310
sum(Distinct salary): Sum of all distinct Non-Null values i.e., 250.
Aggregate Functions
• Avg():
Avg(salary) = Sum(salary) / count(salary) = 310/5
Avg(Distinct salary) = sum(Distinct salary) / Count(Distinct Salary) = 250/4
Aggregate Functions
• Min():
Min(salary): Minimum value in the salary column except NULL i.e., 40.
Aggregate Functions
• Max():
Max(salary): Maximum value in the salary i.e., 80.
GROUP BY
• The GROUP BY Statement in SQL is used to arrange identical data into
groups with the help of some functions. i.e if a particular column has
same values in different rows then it will arrange these rows in a
group.
• Important Points:
• GROUP BY clause is used with the SELECT statement.
• In the query, GROUP BY clause is placed after the WHERE clause.
• In the query, GROUP BY clause is placed before ORDER BY clause if used any.
GROUP BY
GROUP BY
GROUP BY
• Group By single column: Group By single column means, to place all the rows
with same value of only that particular column in one group. Consider the query
as shown below:
• As you can see in the above output, the rows with duplicate NAMEs are grouped
under same NAME and their corresponding SALARY is the sum of the SALARY of
duplicate rows. The SUM() function of SQL is used here to calculate the sum.
GROUP BY
• Group By multiple columns: Group by multiple column is say for
example, GROUP BY column1, column2. This means to place all the rows with
same values of both the columns column1 and column2 in one group. Consider
the below query:
• As you can see in the above output the students with both same SUBJECT and
YEAR are placed in same group. And those whose only SUBJECT is same but not
YEAR belongs to different groups. So here we have grouped the table according to
two columns or more than one column.
HAVING Clause
• We know that WHERE clause is used to place conditions on columns
but what if we want to place conditions on groups?
• This is where HAVING clause comes into use. We can use HAVING
clause to place conditions to decide which group will be the part of
final result-set. Also we can not use the aggregate functions like
SUM(), COUNT() etc. with WHERE clause. So we have to use HAVING
clause if we want to use any of these functions in the conditions.
HAVING Clause
HAVING Clause
ORDER BY Clause
• The ORDER BY statement in SQL is used to sort the fetched data in
either ascending or descending according to one or more columns.
• By default ORDER BY sorts the data in ascending order.
• We can use the keyword DESC to sort the data in descending order and the
keyword ASC to sort in ascending order.
ORDER BY Clause
• Sort according to one column:
• To sort in ascending or descending order we can use the keywords ASC or
DESC respectively.
ORDER BY Clause
• Sort according to multiple columns:
• To sort in ascending or descending order we can use the keywords ASC or
DESC respectively. To sort according to multiple columns, separate the names
of columns by the (,) operator.
ORDER BY Clause
ORDER BY Clause
• Sort according to a single column:
ORDER BY Clause
• Sort according to multiple columns:
ORDER BY Clause
Note: ASC is the default value for the ORDER BY clause. So, if we don’t
specify anything after the column name in the ORDER BY clause, the
output will be sorted in ascending order by default.
ALIASES
• Aliases are the temporary names given to table or column for the
purpose of a particular SQL query. It is used when name of column or
table is used other than their original names, but the modified name
is only temporary.
• Aliases are created to make table or column names more readable.
• The renaming is just a temporary change and table name does not change in
the original database.
• Aliases are useful when table or column names are big or not very readable.
• These are preferred when there are more than one table involved in a query.
ALIASES
ALIASES
LIKE Operator
• Sometimes we may require tuples from the database which match
certain patterns. For example, we may wish to retrieve all columns
where the tuples start with the letter ‘y’, or start with ‘b’ and end
with ‘l’, or even more complicated and restrictive string patterns. This
is where the LIKE Clause comes to rescue, often coupled with the
WHERE Clause in SQL.
• There are two kinds of wildcards used to filter out the results:
• % : Used to match zero or more characters. (Variable Length)
• _ : Used to match exactly one character. (Fixed Length)
LIKE Operator
LIKE Operator
LIKE Operator
• Application: The LIKE operator is extremely resourceful in situations
such as address filtering wherein we know only a segment or a
portion of the entire address (such as locality or city) and would like
to retrieve results based on that. The wildcards can be resourcefully
exploited to yield even better and more filtered tuples based on the
requirement.
BETWEEN Operator
• The SQL BETWEEN condition allows you to easily test if an expression
is within a range of values (inclusive). The values can be text, date, or
numbers. It can be used in a SELECT, INSERT, UPDATE, or DELETE
statement. The SQL BETWEEN Condition will return the records where
expression is within the range of value1 and value2.
BETWEEN Operator
BETWEEN Operator
BETWEEN Operator
IN Operator
• IN operator allows you to easily test if the expression matches any
value in the list of values. It is used to remove the need of multiple OR
condition in SELECT, INSERT, UPDATE or DELETE. You can also use NOT
IN to exclude the rows in your list. We should note that any kind of
duplicate entry will be retained.
IN Operator
IN Operator
IFNULL
• IFNULL(): This function is available in MySQL, and not in SQL Server or
Oracle. This function take two arguments. If the first argument is not
NULL, the function returns the first argument. Otherwise, the second
argument is returned. This function is commonly used to replace
NULL value with another value.
IFNULL
LIMIT
• If there are a large number of tuples satisfying the query conditions, it
might be resourceful to view only a handful of them at a time.
• The LIMIT clause is used to set an upper limit on the number of tuples
returned by SQL.
• It is important to note that this clause is not supported by all SQL versions.
• The LIMIT clause can also be specified using the SQL 2008 OFFSET/FETCH
FIRST clauses.
• The limit/offset expressions must be a non-negative integer.
LIMIT
LIMIT with OFFSET
• LIMIT x OFFSET y simply means skip the first y entries and then return
the next x entries.
OFFSET can only be used with ORDER BY clause. It cannot be used on
its own.
OFFSET value must be greater than or equal to zero. It cannot be
negative, else returns error.
LIMIT with OFFSET
LIMIT ALL
Stored Procedures
• A stored procedure is nothing but a series of declarative SQL
statements which can be stored in the database catalogue. A
procedure can be thought of as a function or a method. They can be
invoked through triggers, other procedures, or applications on Java,
PHP etc.
All the statements of a block are passed to Oracle engine all at once
which increases processing speed and decreases the traffic.
Stored Procedures
• Advantages:
• They result in performance improvement of the application. If a procedure is
being called frequently in an application in a single connection, then the
compiled version of the procedure is delivered.
• They reduce the traffic between the database and the application, since the
lengthy statements are already fed into the database and need not be sent
again and again via the application.
• They add to code reusability, similar to how functions and methods work in
other languages such as C/C++ and Java.
Stored Procedures
• Disadvantages:
• Stored procedures can cause a lot of memory usage. The database
administrator should decide an upper bound as to how many stored
procedures are feasible for a particular application.
• MySQL does not provide the functionality of debugging the stored
procedures.
Stored Procedures
Stored Procedures
• To execute the store procedure, you can use the CALL keyword.
Below is syntax:
• CALL [Procedure Name] ([Parameters]..)
• In the syntax:
1. The procedure name must be specified after the CALL keyword
2. If the procedure has the parameters, then the parameter values must
be specified in the parenthesis
Stored Procedures
Stored Procedures
• The MySQL Stored procedure parameter has three modes: IN,
OUT, and INOUT. When we declare an IN type parameter, the
application must pass an argument to the stored procedure. It is
a default mode.
• The OUT type parameter, the stored procedure returns a final
output generated by SQL Statements. When we declare the
INOUT type parameter, the application has to pass an argument,
and based on the input argument; the procedure returns the
output to the application.
Stored Procedures
• When we create a stored procedure, the parameters must be
specified within the parenthesis. The syntax is following:
Stored Procedures
DROP a procedure
• To drop the stored procedure, you can use the drop procedure
command. The syntax is following
• Drop procedure [IF EXISTS] <Procedure Name>
• In the syntax, the name of the stored procedure must be
followed by the Drop Procedure keyword. If you want to drop
the sp_getCustomers procedure from the sakila database, you
can run the following query.
DROP a procedure
• When you try to drop the procedure that does not exist on a
database, the query shows an error:
• ERROR 1305 (42000): PROCEDURE
sakila.getCustomer does not exist
• To avoid this, you can include the [IF EXISTS] option in the drop
procedure command. When you include the IF EXISTS keyword,
instead of an error, the query returns a warning:
• Query OK, 0 rows affected, 1 warning (0.01 sec)
1305 PROCEDURE sakila.getCustomer does not
exist
