Outline

 Create and Delete Database

 Create and Delete Table
 Insert records Into Table
 Delete records into Table
 Update or Modify records into Table
 Read records from Table
 Where and Order By Clause
 Group By Clause
 Aggregate function
 Set operation
 Sub Queries
Create and Delete Database

 Create database online_event

 Delete database online_event
Create Table
CREATE TABLE MyGuests (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP
)

MyGuests

id firstname lastname email reg_date

Delete Table
drop table MyGuests

MyGuests

id firstname lastname email reg_date

Insert records Into Table
INSERT INTO MyGuests (firstname, lastname, email)
VALUES
('John', 'Doe', 'john@example.com')
(‘zahid', hasan', ‘hasan@gmail.com')

MyGuests

id firstname lastname email reg_date

1 John Doe john@example.com 04-26-2020

2 zahid hasan hasan.ice@gmail.com 04-26-2020

Delete records Into Table

delete * from MyGuests

MyGuests

id firstname lastname email reg_date

1 John Doe john@example.com 04-26-2020

2 zahid hasan hasan.ice@gmail.com 04-26-2020

‘Where’ Clause

 The WHERE clause is used to extract only those records that

fulfill a specified condition.
Update or Modify records into Table

 UPDATE MyGuests SET lastname=‘Abraham' WHERE id=1

MyGuests

id firstname lastname email reg_date

1 John Abraham john@example.com 04-26-2020

2 zahid hasan hasan.ice@gmail.com 04-26-2020

Read records from Table

SELECT id, firstname, lastname FROM MyGuests

MyGuests

id firstname lastname email reg_date

1 John Abraham john@example.com 04-26-2020

2 zahid hasan hasan.ice@gmail.com 04-26-2020

Read Specific records from Table

 SELECT * FROM MyGuests WHERE lastname='Abraham'

MyGuests

id firstname lastname email reg_date

1 John Abraham john@example.com 04-26-2020

2 zahid hasan hasan.ice@gmail.com 04-26-2020

Order By Clause
 The ORDER BY clause is used to sort the result-set in ascending or descending order.
 The ORDER BY clause sorts the records in ascending order by default. To sort the records in
descending order, use the DESC keyword.

SELECT id, firstname, lastname

SELECT id, firstname, lastname
FROM MyGuests ORDER BY lastname desc
FROM MyGuests ORDER BY lastname
MyGuests MyGuests
id firstname lastname id firstname lastname
1 John Abraham 2 zahid hasan

2 zahid hasan 1 John Abraham

Group By Clause
 The GROUP BY statement groups rows that have the same values
into summary rows, like "find the number of customers in each
country".

 The GROUP BY statement is often used with aggregate functions

(COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or
more columns.
 Group By Clause

Number Country
1 Germany
2 Mexico
1 UK

SELECT COUNT(CustomerID) as Number, Country FROM Customers

GROUP BY Country;
AGGREGATE FUNCTIONS
AGGREGATE FUNCTIONS
Five (5) aggregate functions namely:
 COUNT
 SUM
 AVG
 MIN

 MAX
 Why use aggregate functions
 From a business perspective, different organization levels have different
information requirements. Top levels managers are usually interested in
knowing whole figures and not necessary the individual details.
>Aggregate functions allow us to easily produce summarized data from our database.
 For instance, from our test database , management may require following
reports
> Least rented movies.
> Most rented movies.
> Average number that each movie is rented out in a month.
COUNT Function movierentals

 The COUNT function returns

the total number of values in
the specified field.
 It works on both numeric and
non-numeric data types.
• Let's suppose that we want to get the number of times that
the movie with id 2 has been rented out

SELECT COUNT(`movie_id`) FROM `movierentals`

WHERE `movie_id` = 2;

Output: 3
 DISTINCT Keyword movierentals

 The DISTINCT
keyword that allows
us to omit duplicates
from our results.
SELECT `movie_id` FROM `movierentals`;

SELECT DISTINCT `movie_id` FROM `movierentals`;

MIN function
Movies
 The MIN function Movie_Name year_released
returns the smallest Sharlock holmes 2003
value in the Quarentine 2005
specified table field.
As an example, let's suppose we want to know the year in which
the oldest movie in our library was released

SELECT MIN(`year_released`) FROM `movies`;

Output: 2003
MAX function
Movies
 The MAX function Movie_Name year_released
returns the highest Sharlock holmes 2003
value in the Quarentine 2020
specified table field.
As an example, let's suppose we want to know the year in which
the recent movie in our library was released

SELECT MAX(`year_released`) FROM `movies`;

Output: 2020
SUM function payments

 SUM function which

returns the sum of
all the values in the
specified column.

SELECT SUM(`amount_paid`) FROM `payments`;

Output: 10500
AVG function payments

 AVG function
returns the average
of the values in a
specified column.

SELECT AVG(`amount_paid`) FROM `payments`;

Output: 3500
 Mysql Aliases payments

 MySQL aliases are

used to give a table, or
a column in a table, a
temporary name.
 Aliases are often used
to make column
names more readable.
 An alias only exists for SELECT AVG(`amount_paid`) as Average FROM `payments`;
the duration of the
query. Average
3500
‘Between’ and ‘AND’
Select name from instructor where salary between 8000 and 12000;
instructor
instructor
Name salary
Zahid 1000 Name salary
Morium 6000 Sobuj 8000
Bob 15000 Moin 11000
Sobuj 8000
Moin 11000
Delete Operation
 Deletes all tuples from the instructor relation. The instructor relation itself still exists, but it is
empty.

delete from instructor;

Delete Operation
Delete Operation
For example, suppose that we want to delete the records of all instructors with salary below
the average at the university. We could write

delete from instructor where salary < (select avg (salary) from instructor);
Update Operation
Suppose that annual salary increases are being made, and salaries of all instructors are to be increased
by 5 percent. We write

Update instructor set salary = salary * 1.05;

If a salary increase is to be paid only to instructors with salary of less than $70,000, we can write:

Update instructor set salary=salary * 1.05 where salary < 70000;

Update Operation

“Give a 5 percent salary raise to instructors whose salary is less than average ” as follows:

Update instructor set salary = salary * 1.05 where salary <

( select avg (salary) from instructor);
Ordering the Display of Tuples
To list in alphabetic order all instructors in the Physicsdepartment, we write:

We wish to list the entire instructor relation in descending order of salary. If several
instructors have the same salary, we order them in ascending order by name. We express this query in
SQL as follows:
Set Operations
The SQL operations union, intersect, and except operate on relations and correspond to the
mathematical set-theory operations ∪,∩, and −.
The Union Operation
To find the set of all courses taught either in Fall 2009 or in Spring 2010, or both, we write
The Union Operation
To find the set of all courses taught either in Fall 2009 or in Spring 2010, or both, we write
The Intersect Operation
To find the set of all courses taught in the Fall 2009 as well as in Spring 2010 we write:
The Except Operation
To find all courses taught in the Fall 2009 semester but not in the Spring 2010 semester,
we write:
Nested Query using Set Operation
We begin by finding all courses taught in Spring 2010, and we write the subquery

We then need to find those courses that were taught in the Fall 2009 and that appear in the set of
courses obtained in the subquery.
Nested Query using Set Operation
We use the not in construct in a way similar to the in construct.

For example, to find all the courses taught in the Fall 2009 semester but not in the Spring
2010semester, we can write:
Nested Query using Set Operation
The following query selects the names of instructors whose names are neither “Mozart” nor
“Einstein”.