HARAMAYA UNIVERSITY
COLLEGE OF COMPUTING AND INFORMATICS
DEPARTMENT OF INFORMATION TECHNOLOGY
FUNDAMENTALS OF DATABASE SYSTEMS
LAB SESSION - 2
2 CONTENTS
Introduction
DBMS
DB DATA-TYPES
DDL and DML
Practical Examples (Query and UI Based)
o Create Database
o Create Tables
o Insert | Update | Delete Data (Records)
3
INTRODUCTION
Practically, database is a collection of tables that stores related data.
Used as container of tables.
Table stores data in the form of columns and rows.
DATABASE: LEARNING MANAGEMENT SYSTEM (LMS)
TABLE: INSTRUCTOR TABLE: STUDENT
RECID INSTNAME DEPT RANK RECID STUDNAME DEPT BATCH
1 Ujulu SENG Lecturer 1 Obang SENG 2015
TABLE: COURSE
RECID COURSECODE COURSETITLE CREDITHR.
1 SENG2031 DB 3
4
DBMS … (1)
A software that allow us to create database and its tables is called
Database Management Systems (DBMS).
DBMSs are software systems used to manage and manipulate data in a
database. Used to store, retrieve, and run queries on data.
A DBMS serves as an interface between an end-user and a database,
allowing users to create, read, update, and delete data in the database.
DBMS manage the data, the database engine, and the database
schema, allowing for data to be manipulated or extracted by users and
other programs.
This helps provide data security, data integrity, concurrency, and
uniform data administration procedures.
5
DBMS … (2)
DBMS Used to:
Create Database and Tables
Perform CRUD operations on data (records) in the table
o CRUD {CREATE, READ, UPDATE, DELETE}
Search specific data from the table
Retrieve sorted data (ASCENDING and DESCENDING)
Apply functions such as COUNT(), MAX(), MIN(), SUM(), AVERAGE()
6
DBMS … (3)
POPULAR DBMS SOFTWARES
DEVELOPER: MICROSFOT DEVELOPER: ORACLE DEVELOPER: ORACLE
DEVELOPER: MICROSFOT
FIRST RELEASE: APRIL 1989 FIRST RELEASE: MAY 1995 FIRST RELEASE: 1979
FIRST RELEASE: NOV 1992
TYPE: RDBMS TYPE: RDBMS TYPE: Multi-Modal
TYPE: RDBMS
Written In: C and C++ Written In: C and C++ Written In: C , C++, and Assembly
Written In: C++
OS: Windows, Linux OS: Windows, Linux, MAC OS: Windows, Linux, MAC
OS: Windows
7
COMMON DB DATA-TYPES
DATATYPE DESCRIPTION
INT / BIGINT Used to store Integers
VARCHAR Used to store strings (text)
DATETIME Used to store Full date with time combination
DATE Used to store Date Only
TIME Used to store Time Only
YEAR Used to store Year Only
FLOAT /DOUBLE Used to store numbers in decimal point
BLOB Used to store Image
BOOLEAN True/False or (1/0)
8
DDL and DML … (1)
DDL (Data Definition Language) : DDL commands define and manage the structure of a database.
These commands affect the schema and are mostly related to creating, modifying, or deleting
database objects.
Common DDL Commands:
CREATE – Creates a new table, database, or other database objects.
ALTER – Modifies an existing table structure (e.g., adding/removing columns).
DROP – Deletes a table or database permanently.
TRUNCATE – Removes all records from a table but keeps its structure.
9
DDL and DML … (2)
DML (Data Manipulation Language)DML commands are used to manipulate and manage data
within database tables.
These commands focus on inserting, updating, and deleting records.
Common DML Commands:
INSERT – Adds new records into a table.
UPDATE – Modifies existing records in a table.
DELETE – Removes records from a table.
SELECT- Retrieve/access records from table
10
PRACTICAL EXAMPLES … (1)
PRACTICE 1 Note: Text in a blue
color are Keywords
CREATE DATABASE
Syntax: CREATE DATABASE Database_Name;
o CREATE DATABASE LMS;
11
PRACTICAL EXAMPLES … (2)
PRACTICE 2 Note: Text in a blue
color are Keywords
CREATE TABLES
Syntax: CREATE TABLE Table_name(column_1 Datatype, column_2 Datatype ….Column_n Datatype);
CREATE TABLE Instructor ( CREATE TABLE Student ( CREATE TABLE Course (
RecID INT, RecID INT, RecID INT,
InstName VARCHAR(50), StudName VARCHAR(50), CourseCode VARCHAR(50),
Dept VARCHAR(50), Dept VARCHAR(50), CourseTitle VARCHAR(50),
Rank VARCHAR(50) Batch VARCHAR(50) CreditHr INT
); ); );
12
PRACTICAL EXAMPLES … (3)
PRACTICE 3 Note: Text in a blue
color are Keywords
INSERT RECORDS IN TO TABLES
Syntax: INSERT INTO Table_name (column_1, column_2, …. column_n) VALUES (value_1, value_2, … value_n);
INSERT INTO Instructor (RecID, InstName, Dept, Rank) VALUES (1, “Ujulu”, “SENG”, “Lecturer”);
INSERT INTO Student (RecID, StudName, Dept, Batch) VALUES (1, “Obang”, “SENG”, “2015”);
INSERT INTO Course (RecID,CourseCode, CourseTitle, CreditHr.) VALUES (1, “SENG2031”, “DB”, 3);
13
PRACTICAL EXAMPLES … (4)
PRACTICE 4 Note: Text in a blue
color are Keywords
UPDATE RECORD IN A TABLE
Syntax: UPDATE Table_name SET column_name=“new value” ;
UPDATE Instructor SET Dept=“CS”;
14
PRACTICAL EXAMPLES … (5)
PRACTICE 5 Note: Text in a blue
color are Keywords
DELETE RECORD FROM A TABLE
Syntax: DELETE FROM Table_name WHERE column_name=“value” ;
DELETE FROM Instructor WHERE RecID=1;
15
PRACTICAL EXAMPLES … (6)
PRACTICE 6 Note: Text in a blue
color are Keywords
ALTER A TABLE | Modifying Existing table (Add/Remove Column)
Syntax: ALTER TABLE Table_name ADD COLUMN Column_Name Datatye AFTER Column_name; // Add Column
Example: ALTER TABLE instructor ADD COLUMN Phone INT AFTER RANK;
Add one column called “Phone” after the existing column
“Rank” with Data-Type of Integer in table “Instructor”
16
PRACTICAL EXAMPLES … (7)
PRACTICE 7 Note: Text in a blue
color are Keywords
ALTER A TABLE | Modifying Existing table (Add/Remove Column)
Syntax: ALTER TABLE Table_name DROP COLUMN Column_Name; // Removing existing Column
Example: ALTER TABLE instructor DROP COLUMN Rank;
Remove “Rank” Column from the table “Instructor”
TEACHING YOU IS GOOD LUCK
