Database Management System
Course-code: CSE 3103
Prepared By:
Sumaia Rahman
Assistant Professor, Dept. of CSE,
Varendra University
What is Database?
A database is a collection of information that is organized so that it can be easily accessed,
managed and updated.
Most commonly used databases are Oracle 12c(enterprise database), MySQL (mostly
used for web based application), MongoDB, DocumnetDB, Redis (Non RDBMS),
MariaDB (is a fast RDBMS) and so on.
What is DBMS?
A database management system (DBMS) is system software for creating and managing
databases. DBMS provides an API between the database and user.
What is SQL?
SQL stands for Structured Query Language which is a very powerful and diverse
database language use to storing, manipulating and retrieving data into databases.
SQL is a greater tool with web languages such as PHP, Python, Java, ASP and so
on to build dynamic web applications.
Types of SQL Statement
1. DML (Data Manipulation Language)
Select, insert, update, delete etc.
2. DDL (Data Definition Language)
create, alter, drop, truncate, rename etc.
3. DCL (Data Control Language)
comment etc.
Create Table Construct
create table tableName (columnName datatype,
columnName dataType)
Example:
create table branch
(branch_name char(15),
branch_city char(30),
assets integer)
Integrity Constraints on Tables
1. not null
2. primary key (A1, ..., An )
Example: Declare branch_name as the primary key for branch table
.
create table branch
Not null is must be written if
(branch_name char(15) not null, you want to make
branch_city char(30), branch_name primary key
assets integer,
primary key (branch_name))
If you forget to add a Primary Key then you can follow this query after creating the
table:
Alter table branch
Add Primary key (branch_name);
Basic Insertion and Deletion of Tuples
• Newly created table is empty
• Add a new tuple to branch table
insert into branch Values ('Zeropoint','Rajshahi',10000);
• Delete all tuples from branch table
delete from branch
Note: Will see later how to delete selected tuples
Drop and Alter Table Constructs
The drop table command deletes all information about the dropped relation from the
database.
drop table branch;
The alter table command is used to add attributes to an existing relation:
Alter table TableName Add ColumName DataType;
Alter table branch Add branch_id int;
• The alter table command can also be used to drop attributes of a relation:
alter table tableName drop column columnName
alter table branch drop column branch_id;
The select Clause
The select clause list the attributes desired in the result of a query
Example: find the names of all branches in the branch relation:
Select branch_name from branch
• To force the elimination of duplicates, insert the keyword distinct after select.
• Find the names of all branches in the branch relations, and remove duplicates
select distinct branch_name from branch
• The keyword all specifies that duplicates not be removed
select all branch_name from branch;
The select Clause (cont.)
The select clause can contain arithmetic expressions involving the operation, +, –, *, and /, and operating on constants or
attributes of tuples.
select branch_name, branch_city, assets*100
from branch
• Suppose you have two tables like
Borrower loan
• Find the customer name, loan number and loan amount of customers.
select customer_name, borrower.loan_number,[Link]
from borrower,loan
where borrower.loan_number=loan.loan_number;
To find all loan number for loans made at the ZeroPoint branch with loan amounts greater than $32000.
Select loan_number from loan where branch_name=‘ZeroPoint’ and amount > 32000
• Comparison results can be combined using the logical connectives and, or, and not.
• SQL includes a between comparison operator
• Find the loan number of those loans with loan amounts between $30,000 and $60,000
Select loan_number from loan where amount between 30000 and 60000
Rename Operation:
SQL allows renaming relations and attributes using the as clause:
old-name as new-name
E.g. Find the name, loan number and loan amount of all customers; rename the column name
loan_number as loan_id.
select customer_name, borrower.loan_number as loan_id, amount
from borrower as borrowers, loan as loans
where borrower.loan_number = loan.loan_number
String Operations
SQL includes a string-matching operator for comparisons on character strings.
percent (%). The % character matches any substring.
underscore (_). The _ character matches any character
Ordering the Display of Tuples
List the names of all customers alphabetically where each
customers having an amount greater than $27000. borrower
Select borrower.customer_name,[Link]
from borrower,loan
where borrower.loan_number=loan.loan_number loan
and amount > 27000
order by customer_name
We may specify desc for descending order or asc for ascending order, for each
attribute; ascending order is the default.
Example: order by customer_name desc
Set Operations
The set operations union, intersect, and except operate on relations and correspond to the relational algebra
operations
Each of the above operations automatically eliminates duplicates; to retain all duplicates use the corresponding
multiset versions union all, intersect all and except all.
Find all customers who have a loan, an account, or both:
(select customer_name from depositor)
union
(select customer_name from borrower)
borrower
depositor
Output
Set Operation(cont.)
Find all customers who have both a loan and an account.
(select customer_name from depositor)
intersect
(select customer_name from borrower)
Find all customers who have an account but no loan
(select customer_name from depositor)
except
(select customer_name from borrower)
Aggregate Functions
These functions operate on the multiset of values of a column of a relation,
and return a value
avg: average value
min: minimum value
max: maximum value
sum: sum of values
count: number of values
Aggregate Functions (Cont.)
Find the branch name and average assets at the kazla branch.
select branch_name, avg (assets) from branch where branch_name ='kazla'
group by branch_name; branch
Find the number of tuples in the branch relation.
select count (*) from branch
Find the number of depositors in the bank.
select count (distinct customer_name) from depositor;
Aggregate Functions
Find the number of depositors for each branch.
select branch_name, count (distinct customer_name) depositor account
from depositor, account
where depositor.account_number = account.account_number
group by branch_name
Note: Attributes in select clause outside of aggregate functions must
appear in group by list
Find the names of all branches where the average account balance is more than
$14,000.
select branch_name, avg (balance)
from account
group by branch_name
having avg (balance) > 14000
Null Values
A field with a NULL value is a field with no value.
The predicate is null can be used to check for null values.
The predicate is not null can be used to check for not null values.
How to insert a null value in a table?
insert into loan values (120,’Boalia’,NULL);
Find those loan number for loan relation where amount is null .
select loan_number from loan where amount is null;
The result of any arithmetic expression involving null is null
Example: 5 + null returns null
All aggregate functions ignore NULL values
Find the number of amount tuples in loan relation.
select count (amount) from loan;
Sub queries using IN and all constructs
Find all customers who have both an account and a loan at the bank. Depositor
Borrower
select customer_name from borrower
where customer_name in (select customer_name
from depositor )
Find the names of all branches that have greater assets than all branches located in ZeroPoint.
select branch_name
from branch
where assets > all
(select assets
from branch branch
where branch_name = ‘ZeroPoint’ )
Modification of the Database – Update
• Pay 5% interest to all balances of account relation:
update account
account
set balance = balance 1.05
Pay 5% interest on those balance in account relation where balance is greater than
average value:
update account
set balance=balance * 1.05
where balance > (select avg (balance) from account)