Database Management System
Md. Manowarul Islam
Lecturer
Dept. of CSE
Jagannath University.
Database example
■ Consider the following relational schemas for
the Banking Enterprise:
CSE-2203 Md. Manowarul Islam, Dept. of CSE, Jagannath University, Dhaka-1100.
CSE-2203 Md. Manowarul Islam, Dept. of CSE, Jagannath University, Dhaka-1100.
Example
■ Create the database
create database Bank
■ Create the table loan
create table loan
(
loan_number char(255),
branch_name char(255),
amount int,
primary key(loan_number)
);
CSE-2203 Md. Manowarul Islam, Dept. of CSE, Jagannath University, Dhaka-1100.
Insert data
insert into loan
values('L-11','Round Hill',900)
insert into loan
values('L-14','Downtown',1500);
insert into loan
values('L-17','Downtown',1000);
insert into loan
values('L-15','Perryridge',1500);
insert into loan
values('L-23','Redwood',2000);
insert into loan
values('L-16','Perryridge',1300);
insert into loan
values('L-93','Mianus',500);
CSE-2203 Md. Manowarul Islam, Dept. of CSE, Jagannath University, Dhaka-1100.
Basic structure of SQL queries
❑ The basic structure of SQL expression consists of
three clauses:
✔ i. select ii. from iii. Where
✔ select – It retrieves data from a specified table or multiple
related tables in a database or the result of an expression.
✔ from – It indicates the source table or tables from which the
data is to be retrieved.
✔ where - It includes a comparison predicate, used to restrict
the no.of rows returned by the query.
CSE-2203 Md. Manowarul Islam, Dept. of CSE, Jagannath University, Dhaka-1100.
Select
■ Format
■ The asterisk symbol “ * ” can be used to
denote “all attributes.”
CSE-2203 Md. Manowarul Islam, Dept. of CSE, Jagannath University, Dhaka-1100.
Example
select *
from loan;
CSE-2203 Md. Manowarul Islam, Dept. of CSE, Jagannath University, Dhaka-1100.
Example
■ Find all branch name
■ Find all loan number
Select branch_name
from loan Select loan_number
from loan
CSE-2203 Md. Manowarul Islam, Dept. of CSE, Jagannath University, Dhaka-1100.
Example
■ We use distinct keyword –
remove duplicates.
■ Find all branch name.
Select branch_name Select distinct branch_name
from loan from loan
CSE-2203 Md. Manowarul Islam, Dept. of CSE, Jagannath University, Dhaka-1100.
■ Run the following command
select loan_number,branch_name,amount+100
from loan
The select clause can
contain arithmetic
expressions involving the
operation, +, –, ∗, and /,
and operating on
constants or attributes of
tuples.
CSE-2203 Md. Manowarul Islam, Dept. of CSE, Jagannath University, Dhaka-1100.
The WHERE Clause
■ The WHERE clause is used to filter records.
■ The WHERE clause is used to extract only those
records that fulfill a specified criterion.
■ SQL WHERE Syntax
CSE-2203 Md. Manowarul Islam, Dept. of CSE, Jagannath University, Dhaka-1100.
The WHERE Clause
■ Example: The "Persons" table:
■ Now we want to select only the persons living in the city
"Sandnes" from the table above.
CSE-2203 Md. Manowarul Islam, Dept. of CSE, Jagannath University, Dhaka-1100.
The WHERE Clause
■ Now we want to select only the persons living in the city
"Sandnes" from the table above.
■ SELECT * FROM Persons
WHERE City='Sandnes'
CSE-2203 Md. Manowarul Islam, Dept. of CSE, Jagannath University, Dhaka-1100.
The WHERE Clause
■ We want to find “Name of all loan number at the
Perryridge branch”
CSE-2203 Md. Manowarul Islam, Dept. of CSE, Jagannath University, Dhaka-1100.
The WHERE Clause
■ We want to find “all loan number at the Perryridge
branch”
select loan_no
from loan
where branch_name = ‘Perryridge’;
CSE-2203 Md. Manowarul Islam, Dept. of CSE, Jagannath University, Dhaka-1100.
Filtering data
CSE-2203 Md. Manowarul Islam, Dept. of CSE, Jagannath University, Dhaka-1100.
Operators Allowed in the WHERE
CSE-2203 Md. Manowarul Islam, Dept. of CSE, Jagannath University, Dhaka-1100.
The WHERE Clause
■ SQL AND & OR Operators
❑ The AND operator displays a record if both the
first condition and the second condition is true.
❑ The OR operator displays a record if either the
first condition or the second condition is true.
CSE-2203 Md. Manowarul Islam, Dept. of CSE, Jagannath University, Dhaka-1100.
The WHERE Clause
■ We want to find “all loan number at the Perryridge
branch with loan amounts greater than 1200”
select loan_number,amount
from loan
where branch_name='Perryridge‘ and amount>1200
CSE-2203 Md. Manowarul Islam, Dept. of CSE, Jagannath University, Dhaka-1100.
The WHERE Clause
■ We want to find “all loan number at the Perryridge
branch with loan amounts between 1000 and 1400.”
select loan_number,amount from loan where
branch_name='Perryridge' and amount>=1000 and
amount<=1400
CSE-2203 Md. Manowarul Islam, Dept. of CSE, Jagannath University, Dhaka-1100.
The WHERE Clause
■ We want to find “all loan number at the Perryridge
branch with loan amounts between 1000 and 1400.”
select loan_number,amount from loan where
branch_name='Perryridge' and amount between 1000
and 1400
CSE-2203 Md. Manowarul Islam, Dept. of CSE, Jagannath University, Dhaka-1100.
The WHERE Clause
■ “Get all the loan number that is not belongs to
Downtown.”
select *
from loan
where branch_name !='Downtown'
select *
from loan
where branch_name <>'Downtown'
CSE-2203 Md. Manowarul Islam, Dept. of CSE, Jagannath University, Dhaka-1100.
The WHERE Clause
■ AND Operator Example
❑ The "Persons" table:
■ we want to select only the persons with the first name
equal to "Tove“ AND the last name equal to "Svendson"
CSE-2203 Md. Manowarul Islam, Dept. of CSE, Jagannath University, Dhaka-1100.
The WHERE Clause
■ AND Operator Example
■ we want to select only the persons with the first name
equal to "Tove“ AND the last name equal to "Svendson"
SELECT * FROM Persons
WHERE FirstName='Tove'
AND LastName='Svendson‘;
CSE-2203 Md. Manowarul Islam, Dept. of CSE, Jagannath University, Dhaka-1100.
The WHERE Clause
■ OR Operator Example
❑ The "Persons" table:
■ we want to select only the persons with the first name
equal to "Tove" OR the first name equal to "Ola"
CSE-2203 Md. Manowarul Islam, Dept. of CSE, Jagannath University, Dhaka-1100.
The WHERE Clause
■ OR Operator Example
■ we want to select only the persons with the first name
equal to "Tove" OR the first name equal to "Ola"
SELECT * FROM Persons
WHERE FirstName='Tove'
OR FirstName='Ola';
CSE-2203 Md. Manowarul Islam, Dept. of CSE, Jagannath University, Dhaka-1100.
The WHERE Clause
■ AND - OR Operator Example
❑ The "Persons" table:
■ we want to select only the persons with the last name
equal to "Svendson" AND the first name equal to "Tove"
OR to "Ola"
SELECT * FROM Persons
WHERE LastName='Svendson'
AND (FirstName='Tove' OR FirstName='Ola');
CSE-2203 Md. Manowarul Islam, Dept. of CSE, Jagannath University, Dhaka-1100.
SQL IN and NOT IN
■ The IN operator allows you to determine if a
specified value matches any value in a set of
values or returned by a subquery.
CSE-2203 Md. Manowarul Islam, Dept. of CSE, Jagannath University, Dhaka-1100.
The WHERE Clause
■ We wants to find “all loan number where the branch
name is Round Hill or Perryridge.”
select loan_number, branch_name, amount
from loan
where branch_name IN('Round Hill','Perryridge')
CSE-2203 Md. Manowarul Islam, Dept. of CSE, Jagannath University, Dhaka-1100.
MySQL UPDATE statement
■ The UPDATE statement modifies existing data in a table.
■ You can also use the UPDATE statement change values
in one or more columns of a single row or multiple rows.
■ The UPDATE statement is used with the SET, and
WHERE clauses.
CSE-2203 Md. Manowarul Islam, Dept. of CSE, Jagannath University, Dhaka-1100.
MySQL UPDATE statement
■ Table orders
Insert into orders
Values(5004,10,'2029-05-01')
Update the supplier ID to 10010
Update orders
set supplier_id=10010
WHERE order_id=5004
CSE-2203 Md. Manowarul Islam, Dept. of CSE, Jagannath University, Dhaka-1100.
MySQL DELETE statement
■ DELETE statement is used to delete data
from the MySQL table within the database.
■ By using delete statement, we can delete
records on the basis of conditions.
CSE-2203 Md. Manowarul Islam, Dept. of CSE, Jagannath University, Dhaka-1100.
MySQL DELETE statement
■ Table orders
Delete row of order 5004
DELETE FROM orders
WHERE order_id=5004
CSE-2203 Md. Manowarul Islam, Dept. of CSE, Jagannath University, Dhaka-1100.
Thanks a lot
CSE-2203 Md. Manowarul Islam, Dept. of CSE, Jagannath University, Dhaka-1100.
