1.
Install oracle/ MYSQL
Step-by-Step Installation Guide for Oracle Database 21c XE on Windows
Step 1: Check System Requirements
Before downloading:
• OS: Windows 10/11 (64-bit)
• RAM: At least 2 GB (4 GB recommended)
• Disk space: At least 10 GB free
Step 2: Download Oracle 21c XE
1. Go to the official Oracle download page:
https://www.oracle.com/database/technologies/xe-downloads.html
2. Scroll to Oracle Database 21c Express Edition for Windows and click "Download Windows
x64".
3. You’ll need to sign in or create a free Oracle account to download.
Step 3: Extract the Installer
1. After downloading the .zip file (e.g., oracle-database-xe-21c.zip), right-click it and select
Extract All.
2. Extract to a folder like C:\OracleXE.
Step 4: Run the Installer as Administrator
1. Go into the extracted folder.
2. Right-click on setup.exe → Choose Run as Administrator.
Step 5: Install Oracle XE
1. The installer will launch a wizard. Follow the steps:
o Accept License Agreement.
o Choose destination folder (default is okay).
o Set a password for the SYS and SYSTEM users (write this down!).
2. Click Install.
This may take 10–20 minutes. Be patient.
Step 6: Finish Installation
When done:
• You’ll see a message saying “Oracle Database was installed successfully.”
• Click Close.
Step 7: Verify Installation
1. Press Windows Key, type Services, open it.
2. Look for a service like:
o OracleServiceXE
o OracleXETNSListener
3. Make sure the Status is Running.
Step 8: Login to Oracle using SQL Developer (Optional but Recommended)
Install SQL Developer from:
https://www.oracle.com/tools/downloads/sqldev-downloads.html
After installing SQL Developer:
1. Open it and create a new connection:
o Username: system
o Password: (the one you set during install)
o Connection type: Basic
o Hostname: localhost
o Port: 1521
o Service Name: XE
2. Click Connect. You should now be connected to Oracle Database.
Step 9: (Optional) Set Environment Variables
If you want to use Oracle from command line:
1. Add C:\oracle\product\21c\... to the PATH variable in system environment variables.
2. Restart the command prompt.
2.Create Entity-Relationship
Diagram using case tools.
What are CASE Tools?
CASE = Computer-Aided Software Engineering
They help in designing software systems, including ER diagrams, UML diagrams, and more.
Some popular CASE tools for ERDs:
• dbdiagram.io (Web-based, easy)
• Lucidchart (Web-based, visual)
• Draw.io (diagrams.net) (Free, simple)
• Oracle SQL Developer Data Modeler (Best for Oracle)
Step-by-Step Guide to Create ERD Using dbdiagram.io (Best for Beginners)
Step 1: Go to Website
Visit: https://dbdiagram.io
You can sign in with Google or GitHub to save your work, or use it without signing in.
Step 2: Create a New Diagram
Click on:
• "New Diagram"
• Give it a name like Library System or Student Database
Step 3: Write Table Definitions (Simple Code)
Here’s an example for a Student-College ERD:
Table Student {
student_id int [pk]
name varchar
age int
college_id int [ref: > College.college_id]
}
Table College {
college_id int [pk]
name varchar
location varchar
}
• pk = Primary Key
• ref: > College.college_id = Foreign Key
It auto-generates the diagram as you type.
Step 4: View the ER Diagram
• The diagram will appear on the right.
• You can drag tables, zoom, and view relationships.
Step 5: Export or Share
You can:
• Export as PNG or PDF
• Save your project
• Share link with teammates
Optional: Using Oracle SQL Developer Data Modeler
If you're using Oracle, here's a more professional option:
Steps:
1. Download SQL Developer Data Modeler:
https://www.oracle.com/database/sqldeveloper/technologies/datamodeler.html
2. Install and run it.
3. Go to File > New > Relational Model.
4. Add Entities and Attributes using the GUI.
5. Define Primary/Foreign Keys.
6. Export the ERD as an image or PDF.
Write SQL statements Using ORACLE
/MYSQL:
a) Write basic SQL SELECT statements.
b) Restrict and sort data.
c) Display data from multiple tables.
d) Aggregate data using group function.
e) Manipulate data.
f) Create and manage tables
a) Basic SQL SELECT Statements
SELECT first_name, last_name FROM employees;
b) Restrict and Sort Data
SELECT * FROM employees
WHERE department_id = 10
ORDER BY salary DESC;
c) Display Data from Multiple Tables (JOIN)
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;
d) Aggregate Data Using Group Functions
SELECT department_id, AVG(salary) AS average_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 5000;
e) Manipulate Data (INSERT, UPDATE, DELETE)
-- INSERT
INSERT INTO employees (employee_id, first_name, last_name, salary, department_id)
VALUES (207, 'Amit', 'Kumar', 5500, 10);
-- UPDATE
UPDATE employees
SET salary = salary + 1000
WHERE employee_id = 207;
-- DELETE
DELETE FROM employees
WHERE employee_id = 207;
f) Create and Manage Tables
-- CREATE TABLE
CREATE TABLE departments (
department_id NUMBER PRIMARY KEY,
department_name VARCHAR2(100),
location VARCHAR2(100)
);
-- ALTER TABLE (add a column)
ALTER TABLE departments
ADD manager_id NUMBER;
-- DROP TABLE
DROP TABLE departments;
4.Normalization.[Skipped]
5. Create cursor.
-- Sample table
CREATE TABLE employees (
emp_id NUMBER,
name VARCHAR2(50),
salary NUMBER
);
DECLARE
-- Declare variables to hold each row's data
v_name employees.name%TYPE;
v_salary employees.salary%TYPE;
-- Declare the cursor
CURSOR emp_cursor IS
SELECT name, salary FROM employees;
BEGIN
-- Open the cursor
OPEN emp_cursor;
-- Loop through each row
LOOP
FETCH emp_cursor INTO v_name, v_salary;
EXIT WHEN emp_cursor%NOTFOUND; -- Exit loop if no more rows
-- Display data (in SQL*Plus or anonymous block)
DBMS_OUTPUT.PUT_LINE('Name: ' || v_name || ', Salary: ' || v_salary);
END LOOP;
-- Close the cursor
CLOSE emp_cursor;
END;
--Output (example):
Name: John, Salary: 5000
Name: Alice, Salary: 6000
...
6.Create procedure and
functions.
What is a Procedure?
A procedure is a named PL/SQL block that performs an action. It does not return a value
directly, but can use OUT parameters.
CREATE OR REPLACE PROCEDURE procedure_name (
param1 IN datatype,
param2 OUT datatype
IS
BEGIN
-- Procedure logic here
END;
Example: Procedure to Calculate Bonus
CREATE OR REPLACE PROCEDURE calc_bonus (
p_salary IN NUMBER,
p_bonus OUT NUMBER
IS
BEGIN
p_bonus := p_salary * 0.10;
END;
Call the Procedure:
DECLARE
v_bonus NUMBER;
BEGIN
calc_bonus(5000, v_bonus);
DBMS_OUTPUT.PUT_LINE('Bonus is: ' || v_bonus);
END;
What is a Function?
A function is a named PL/SQL block that returns a single value using the RETURN keyword
CREATE OR REPLACE FUNCTION function_name (
param1 IN datatype
) RETURN datatype
IS
BEGIN
-- Function logic here
RETURN value;
END;
Summary: Procedure vs Function
Feature Procedure Function
Returns Value? No (use OUT params) Yes (RETURN value)
Used in SQL? No Yes (can be)
Syntax CREATE PROCEDURE CREATE FUNCTION
8.Create packages and triggers .
PART 1: Packages in Oracle
What is a Package?
A package is a collection of procedures, functions, variables, and cursors grouped together
in two parts:
1. Package Specification – declares what’s inside (the interface)
2. Package Body – contains the actual code
Example: Package for Employee Bonus
Step 1: Package Specification
CREATE OR REPLACE PACKAGE emp_bonus_pkg IS
PROCEDURE calc_bonus (p_salary IN NUMBER, p_bonus OUT NUMBER);
FUNCTION get_bonus (p_salary IN NUMBER) RETURN NUMBER;
END emp_bonus_pkg;
Step 2: Package Body
CREATE OR REPLACE PACKAGE BODY emp_bonus_pkg IS
PROCEDURE calc_bonus (p_salary IN NUMBER, p_bonus OUT NUMBER) IS
BEGIN
p_bonus := p_salary * 0.10;
END;
FUNCTION get_bonus (p_salary IN NUMBER) RETURN NUMBER IS
BEGIN
RETURN p_salary * 0.10;
END;
END emp_bonus_pkg;
PART 2: Triggers in Oracle
What is a Trigger?
A trigger is a stored PL/SQL block that automatically runs when a specific event occurs (like
INSERT, UPDATE, DELETE).
Example: Trigger to Log Employee Inserts
Assume you have these two tables:
CREATE TABLE employees (
emp_id NUMBER,
name VARCHAR2(50),
salary NUMBER
);
CREATE TABLE emp_log (
log_id NUMBER GENERATED ALWAYS AS IDENTITY,
emp_id NUMBER,
action_type VARCHAR2(10),
action_time TIMESTAMP
);
