Assignment

Course Code: CSE 312

Course Title: Database Management System Lab

Topic Name: Basic SQL & Oracle commands for DML & DDL (All
Together).

Submitted to:
Mr. Mayen Uddin Mojumdar
Lecturer (Senior Scale)
Department of CSE
Daffodil International University

Submitted by:
Name: Rafiul Hasan Sazib
ID: 0242310005101924
Section: 64_G1
Department: CSE

Submission: 22 / 04 /2025
1

Day 1 (CREATE, DROP, INSERT, SELECT)

Task 1: To create a Table.
Syntax:-
CREATE TABLE table_name (column_name DATA_TYPE, column_name DATA_TYPE );

Practice:- Create a student Table who has id,name, age,cgpa columns.

Code:-
CREATE TABLE employee (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
job_id VARCHAR(30),
department_id INT NOT NULL,
salary INT NOT NULL,
hire_date DATE NOT NULL
);
Output:

Task 2: Insert data into Table

Syntax:
INSERT INTO table_name (column_name, column_name) VALUES (value_of_column1,
value_of_column2 );
2

Practice:- Insert few data in students table

Code:-
//taking values
INSERT INTO employee VALUES(100, 'Sazib', 'Khan', 'AD_pres', 90, 100000,
TO_DATE('2025-03-10', 'YYYY-MM-DD'));
INSERT INTO employee VALUES(101, 'Resad', 'Mim', 'AD_Vip', 95, 70000, TO_DATE('1999-
05-24', 'YYYY-MM-DD'));
INSERT INTO employee VALUES(102, 'SIzer', 'Vai', 'AD_Vip', 95, 60000, TO_DATE('1996-10-
30','YYYY-MM-DD'));
INSERT INTO employee VALUES(103, 'Reemon', 'Coderu', 'IT_prog', 80, 50000,
TO_DATE('2000-06-25','YYYY-MM-DD'));
INSERT INTO employee VALUES(104, 'Siyam', 'Laddu', 'IT_prog', 80,
24000,TO_DATE('2002-02-19','YYYY-MM-DD'));
INSERT INTO employee VALUES(105, 'Nuhin', 'Talukder', 'IT_prog', 80,
25000,TO_DATE('2003-05-09','YYYY-MM-DD'));
OUTPUT:

Task 3:
Retrieving data from the table:
Syntax: SELECT * FROM table_name;
3

Code:-
SELECT * FROM students;
Output:
4

Task: 4::
Drop the table:
Syntax:
DROP TABLE students;
Output:

Retrieving selective data:

SELECT first_name, job_id, salary FROM employee;
Output:
5

Filtering data using WHERE clause:

SELECT first_name, department_id, salary FROM employee WHERE salary >=
2500;
Output:

Sorting data by using ORDER clause:

SELECT first_name, last_name, job_id, salary FROM employee WHERE
department_id = 80 ORDER BY salary;
Output:
6

BETWEEN:
SELECT first_name, job_id, department_id, salary FROM employee WHERE
salary BETWEEN 20000 AND 50000;
Output:

IN:
SELECT first_name, job_id, department_id, salary FROM employee WHERE
employee_id IN (100, 101, 105);
Output:

LIKE:
SELECT first_name, last_name, employee_id, salary FROM employee WHERE
first_name LIKE '_a%';
Output:
7

LIKE AND OR TOGETHER:

SELECT first_name, last_name, employee_id, salary FROM employee WHERE
first_name LIKE '_a%' OR last_name LIKE '_a%';
Output:

SEARCH BY DATE:
SELECT last_name, job_id, department_id FROM employee WHERE hire_date =
TO_DATE('2003-05-09','YYYY-MM-DD');
Output:

Aliases:
SELECT last_name AS "Name", salary*12 AS "Annual Salary" FROM employee;

Output:
8

Concatenation Operator Using Literal Character Strings:

SELECT last_name ||' is a '||job_id AS "Employee Details" FROM employee;
Output:

DISTINCT:
SELECT DISTINCT department_id FROM employee;
OUTPUT:

COMPARISON OPERATION:
SELECT last_name, salary FROM employee WHERE salary <= 30000;
OUTPUT:
9

Range Conditions Using the BETWEEN Operator:

➔ SELECT last_name, salary FROM employee WHERE salary BETWEEN
25000 AND 6500;
➔ SELECT last_name, salary FROM employee WHERE salary BETWEEN
25000 AND 85000;
OUTPUT:

LOGICAL OPERATION:
SELECT employee_id, last_name, job_id, salary FROM employee WHERE
salary >= 10000 AND job_id LIKE '%AD%';
output:
10

SUBSTITUTION VARIABLE:
SELECT employee_id, last_name, salary, department_id FROM employee
WHERE job_id = &employee_num;
Output:

ALTER:
CREATE TABLE student (

rollno INT PRIMARY KEY,

name VARCHAR(50),

marks INT NOT NULL,

grade VARCHAR(1),

city VARCHAR(20)

);

INSERT:
INSERT INTO student (rollno, name, marks, grade, city)

VALUES (101, 'Sazib', 78, 'C','Rajshahi');

INSERT INTO student (rollno, name, marks, grade, city)

VALUES (102, 'Remon', 93, 'A', 'Bogura');

INSERT INTO student (rollno, name, marks, grade, city)

VALUES (103, 'Resad', 85, 'B', 'Mymanshing');

INSERT INTO student (rollno, name, marks, grade, city)

VALUES (104, 'Siyam', 95, 'A', 'Bogura');

INSERT INTO student (rollno, name, marks, grade, city)

11

VALUES (105, 'Minu', 20, 'F', 'Bogura');

INSERT INTO student (rollno, name, marks, grade, city)

VALUES (106, 'Sizer', 82, 'B', 'Chorttragram');

ADD TABLE:
ALTER TABLE student ADD age NUMBER DEAULT 24 NOT NULL;
OUTPUT:

DROP COLUMN:
ALTER TABLE student DROP COLUMN age;
OUTPUT:
12

AVG, SUM, MIN and MAX function:

SELECT AVG(marks), MAX(marks), MIN(marks), SUM(marks)
FROM student;
Output:

GROUPBY and COUNT function:

SELECT city, COUNT(rollno), AVG(marks)
FROM student GROUP BY city
ORDER BY AVG(marks) ASC;
Output:

HAVING CLUSE:
SELECT count(name) AS num_of_student, city FROM student GROUP BY city
HAVING max(marks) > 70 ORDER BY city;
13

OUTPUT:

COUNT, AVG, MIN, MAX FUNCTION:

CREATE TABLE student (

rollno INT PRIMARY KEY,

name VARCHAR(50),

marks INT NOT NULL,

grade VARCHAR(1),

city VARCHAR(20)

);

INSERT INTO student

(rollno, name, marks, grade, city)

VALUES

(101, "Sazib", 78, "C", "Rajshahi"),

(102, "Remon", 93, "A", "Bogura"),

(103, "Resad", 85, "B", "Mymanshing"),

(104, "Siyam", 95, "A", "Bogura"),

(105, "Minu", 20, "F", "Bogura"),

(106, "Sizer", 82, "B", "Chorttragram");

SELECT COUNT(name), MIN(marks), MAX(marks), AVG(marks)

FROM student;
OUTPUT:
14

JOIN

CREATE TABLE student (

student_id INT PRIMARY KEY,

name VARCHAR(50),

marks INT NOT NULL,

grade VARCHAR(1),

city VARCHAR(20)

);

INSERT INTO student

(student_id, name, marks, grade, city)

VALUES

(101, "Sazib", 78, "C", "Rajshahi"),

(102, "Remon", 93, "A", "Bogura"),

(103, "Resad", 85, "B", "Mymanshing"),

(104, "Siyam", 95, "A", "Bogura"),

(105, "Minu", 20, "F", "Bogura"),

(106, "Sizer", 82, "B", "Chorttragram");

CREATE TABLE couse(

student_id INT PRIMARY KEY,

couse VARCHAR(40)

);

INSERT INTO couse

(student_id, couse)

VALUES

(102, "English"),

(105, "Math"),

(103, "Science"),

(107, "Computer Science");

15

INNER JOIN:
SELECT *
FROM student as S
INNER JOIN couse as C
ON S.student_id = C.student_id;
OUTPUT

LEFT OUTER JOIN

SELECT *
FROM student as S
LEFT JOIN couse as C
ON S.student_id = C.student_id;
OUTPUT:

RIGHT OUTER JOIN:

SELECT *
FROM couse as C
16

RIGHT JOIN student as S

ON C.student_id = S.student_id;
OUTPUT:

FULL OUTER JOIN:

SELECT *
FROM student as S
LEFT JOIN couse as C
ON S.student_id = C.student_id
UNION
SELECT *
FROM student as S
RIGHT JOIN couse as C
ON S.student_id = C.student_id;
OUTPUT:
17

View CREATING VIEW:

CREATE VIEW PlantLocationsWithType AS

SELECT pl.PlantName, pt.TypeName
FROM PlantLocations pl
JOIN PlantTypes pt ON pl.TypeID = pt.TypeID;
SELECT * FROM PlantLocationsWithType;

DROP A VIEW :

DROP VIEW PlantLocationsWithType;

UPDATING DATA THROUGH A VIEW:

UPDATE PlantLocations
SET PlantName = 'New Plant Name'
WHERE PlantName = 'Existing Plant Name';
18

Trigger CREATE A
TRIGGER:

CREATE TRIGGER SetLastWateredOnInsert

BEFORE INSERT ON PlantLocations
BEGIN
UPDATE PlantLocations SET LastWateredDate = date('now')
WHERE PlantID = NEW.PlantID AND NEW.LastWateredDate IS NULL;
END;

REMOVE TRIGGER:

DROP TRIGGER SetLastWateredOnInsert;

19

NoSQL
IN A NOSQL DATABASE:

{
"id": 101,
"plant_name": "Echeveria",
"location": "Sunny windowsill in kitchen"
}

Transactions START A
TRANSACTION:

START TRANSACTION
UPDATE PlantLocations
SET LocationDescription = 'New Sunny Spot'
WHERE PlantID = 101;

USING SAVEPOINT:

SAVEPOINT after_location_update;

COMMIT THE TRANSACTION:

COMMIT;

ROLLBACK IF SOMETHING WRONG:

ROLLBACK;