IIMT ENGINEERING COLLEGE,

MEERUT,(U.P.)

Recognized by AICTE and Approved by Dr. APJ AKTU, Lucknow

DEPARTMENT OF COMPUTER SCIENCE AND

ENGINEERING

PRACTICAL FILE

SESSION (2024-2025)

NAME- Mayank Mathur ROLL NO. 2201270100061

BRANCH- CSE

SECTION- A

SUBJECT- DATABASE AND MANAGEMENT SYSTEM

SUBJECT CODE- BCS-501 SUBMITTED TO- Mr. Kuldeep

Kumar Sir
 INDEX

Program
No. Program Name Date Signature

To install SQL Server in Windows

1
Creating E-R Diagram Using the Case Tool Draw. in
( Customer –Order-Shipment )
2
Writing SQL Statement Using Oracle/SQL.
1. Writing the basic SQL statement .
2. Restricting and sorting data.
3. Displaying the data from multiple table.
3
4. Manipulating the data .
5. Creating and managing the table.

Normalization of a Table

4
Creating the Procedure & Function.
5
Creating the Trigger & Package.
6
Creating the View and explain.
7
Creating Database and Describe the Join Operation in SQL
8

9 To implement the concept cursor in SQL.

To implement the basics of PL/SQL.

10
write the step of automatic backup file and recovery file in
11 SQL
 PRACTICAL No: - 01

To install SQL Server on a Windows machine, follow these steps:

1. Download SQL Server Installer

 Go to the official Microsoft SQL Server download page.
 Choose the version you want to install (e.g., SQL Server 2022, SQL Server Express, or Developer Edition).
 Click the Download button and choose the Basic or Custom installation option depending on your preference.

2. Run the SQL Server Installer

 Once the installer is downloaded, double-click the executable file to launch the installation process.
 If prompted by the Windows User Account Control (UAC), click Yes to allow the installer to make changes.

3. Choose Installation Type

 In the SQL Server Installation Center, click on New SQL Server stand-alone installation or add feature to an
existing installation.

4. Accept the License Terms

 Review the license terms and select I accept the license terms.
 Click Next to continue.

5. Choose Feature(s) to Install

 In the Feature Selection screen, select the components you want to install (e.g., SQL Server Database Engine,
SQL Server Replication, Full-Text and Semantic Extractions, etc.).
 For a typical installation, you can select the Database Engine Services feature. Click Next.

6. Choose Instance Name

 In the Instance Configuration screen, you can choose the default instance or a named instance.
o Default instance name: MSSQLSERVER
o Named instance: You can give it a custom name.
 Click Next to proceed.

7. Server Configuration
 Set the SQL Server Agent to Automatic or Manual based on your requirements.
 Configure the authentication mode:
o Windows Authentication: Uses your Windows account for access.
o Mixed Mode: Allows both Windows Authentication and SQL Server Authentication. You will need to set
a password for the sa (system administrator) account.
 Add the SQL Server administrators by clicking Add Current User (or manually adding users).
 Click Next.
 8. Choose Installation Location
 You can specify where SQL Server will be installed (by default, it will install to C:\Program Files\Microsoft
SQL Server).
 Click Next.

9. Choose Data Directories

 You can specify the directories for SQL Server data, logs, and backup files. You can leave them as default or
change them if needed.
 Click Next.

10. Choose Feature Installation

 If you selected any additional features, configure them as needed.
 Click Next.

11. Install
 Review all the settings and click Install to begin the installation.
 The installer will start installing SQL Server and related components.

12. Complete the Installation

 Once the installation process is complete, the Install Success page will appear.
 Click Next, and then Close to exit the installer.

13. Install SQL Server Management Studio (SSMS)

 If you selected the Database Engine Services, you will likely want to install SQL Server Management Studio
(SSMS) to manage your SQL Server.
 Download SSMS from the official Microsoft SSMS download page.
 Run the installer, and follow the prompts to complete the installation.

14. Connect to SQL Server

 Once installation is complete, launch SQL Server Management Studio (SSMS).
 In the Connect to Server dialog, enter:
o Server Name: (either the default instance or the name of the instance you configured).
o Authentication Mode: (choose either Windows Authentication or SQL Server Authentication based on
your configuration).
 Click Connect to access the SQL Server.

15. Verify Installation

 Once connected to SQL Server in SSMS, run the following query to verify the installation:
 SELECT @@VERSION;
 This will return the version of SQL Server, confirming that it was installed successfully.
 PRACTICAL No: - 02
Objective: Creating E-R Diagram Using the Case Tool Draw. in ( Customer –Order-Shipment )

Tabular Form of E-R Diagram of Customer –Order- Product

CREATE TABLE CUSTOMER18

(
CUSTOMER_ID NUMBER NOT NULL,
CUST_FIRST_NAME VARCHAR2(20) NOT NULL,
CUST_LAST_NAME VARCHAR2(20) NOT NULL,
CUST_STREET_ADDRESS1 VARCHAR2(60),
CUST_STREET_ADDRESS2 VARCHAR2(60),
CUST_CITY VARCHAR2(30),
CUST_STATE VARCHAR2(2),
CUST_POSTAL_CODE VARCHAR2(10),
PHONE_NUMBER1 VARCHAR2(25),
PHONE_NUMBER2 VARCHAR2(25),
CREDIT_LIMIT NUMBER(9,2),
CUST_EMAIL VARCHAR2(30)
);

INSERT INTO CUSTOMER18 (

CUSTOMER_ID,
CUST_FIRST_NAME,
CUST_LAST_NAME,
CUST_STREET_ADDRESS1,
CUST_STREET_ADDRESS2,
CUST_CITY,
CUST_STATE,
CUST_POSTAL_CODE,
PHONE_NUMBER1,
PHONE_NUMBER2,
CREDIT_LIMIT,
CUST_EMAIL
) VALUES (
1,
'Rock',
'Doe',
'203 Main St',
'Apt 801',
'Australia',
'AUS',
'40007',
'565-0234',
'555-9678',
8000.00,
'rock@email.com'
);

INSERT INTO CUSTOMER18 (

CUSTOMER_ID,
CUST_FIRST_NAME,
CUST_LAST_NAME,
CUST_STREET_ADDRESS1,
CUST_STREET_ADDRESS2,
CUST_CITY,
CUST_STATE,
CUST_POSTAL_CODE,
PHONE_NUMBER1,
PHONE_NUMBER2,
CREDIT_LIMIT,
CUST_EMAIL
 ) VALUES (
2,
'John',
'Cena',
'456 Oak St',
NULL,
'Los Angeles',
'CA',
'90701',
'5568-1111',
NULL,
9000.00,
'john@email.com'
);

Select *from CUSTOMER18;

Output:

Explanation:
 CUSTOMER_ID: A numeric field for the unique ID of the customer. It is marked as NOT NULL and should be the
primary key for the table (though this is not explicitly stated in the code).
 CUST_FIRST_NAME and CUSTOMER_LAST_NAME: These are required fields (NOT NULL), each having a
maximum length of 20 characters.
 CUST_STREET_ADDRESS1 and CUSTOMER_STREET_ADDRESS2: These store the customer's address lines with a
maximum length of 60 characters each.
 CUST_CITY: The customer's city, with a maximum length of 30 characters.
 CUST_STATE: A 2-character field to store the state code.
 CUST_POSTAL_CODE: A postal code with a maximum length of 10 characters.
 PHONE_NUMBER1 and PHONE_NUMBER2: Fields for phone numbers with a maximum length of 25 characters.
 CREDIT_LIMIT: A numeric field for the customer's credit limit, which allows up to 9 digits with 2 decimal
places.
 CUST_EMAIL: The customer's email address, with a maximum length of 30 characters.

To create an ORDER table where CUSTOMER_ID is a foreign key (referring to the CUSTOMER18 table) and ORDER_ID is
the primary key,

SQL for the ORDER Table:

CREATE TABLE ORDER999

(
ORDER_ID NUMBER NOT NULL,
CUSTOMER_ID NUMBER NOT NULL,
ORDER_DATE DATE NOT NULL,
SHIP_DATE DATE,
SHIP_ADDRESS VARCHAR2(100),
ORDER_TOTAL NUMBER(10, 2),
CONSTRAINT PK_ORDER_ID PRIMARY KEY (ORDER_ID),
CONSTRAINT FK_CUSTOMER_ID FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER18(CUSTOMER_ID)
);
INSERT INTO ORDER999 (ORDER_ID, CUSTOMER_ID, ORDER_DATE, SHIP_DATE, SHIP_ADDRESS, ORDER_TOTAL)
VALUES (1, 1,'2024-5-11','2024-06-05', '109 Main St, Australia', 550.50);

INSERT INTO ORDER999 (ORDER_ID, CUSTOMER_ID, ORDER_DATE, SHIP_DATE, SHIP_ADDRESS, ORDER_TOTAL)

VALUES (2, 2,'2024-10-02',NULL, '676 Oak St, Los Angeles', 475.00);

Select *from ORDER999;

OUTPUT:

Explanation:
 ORDER_ID: This is the primary key for the order. It is a numeric value and must be unique for each order.
 CUSTOMER_ID: This is the foreign key that references the CUSTOMER_ID in the CUSTOMER18 table,
establishing the relationship between customers and their orders.
 ORDER_DATE: The date when the order was placed. It is a required field.
 SHIP_DATE: The date when the order was shipped (optional).
 SHIP_ADDRESS: The address where the order is shipped. You can adjust the size (currently 100 characters)
based on your needs.
 ORDER_TOTAL: The total amount of the order, defined with two decimal places (e.g., NUMBER(10,2) allows
values up to 99999999.99).

Constraints:
 PK_ORDER_ID: This constraint enforces that ORDER_ID is the primary key and unique.
 FK_CUSTOMER_ID: This constraint ensures that CUSTOMER_ID is a valid reference to the CUSTOMER_ID field in
the CUSTOMER18 table.

To create a SHIPMENT table that is related to the ORDER01 table, you would typically include fields that describe the
shipment, such as the shipment ID, the ORDER_ID (which will be a foreign key), shipment status, tracking number, and
shipment date, among others.

SQL for the SHIPMENT Table:

CREATE TABLE SHIPMENT990

(
SHIPMENT_ID NUMBER NOT NULL,
ORDER_ID NUMBER NOT NULL,
SHIP_DATE DATE NOT NULL,
DELIVERY_DATE DATE,
SHIP_METHOD VARCHAR2(50),
TRACKING_NUMBER VARCHAR2(50),
SHIP_STATUS VARCHAR2(30),
SHIP_ADDRESS VARCHAR2(100),
CONSTRAINT PK_SHIPMENT_ID PRIMARY KEY (SHIPMENT_ID),
CONSTRAINT FK_ORDER_ID FOREIGN KEY (ORDER_ID) REFERENCES ORDER999(ORDER_ID)
);
-- Insert sample data into the SHIPMENT table
INSERT INTO SHIPMENT990 (
SHIPMENT_ID,
ORDER_ID,
SHIP_DATE,
DELIVERY_DATE,
SHIP_METHOD,
TRACKING_NUMBER,
SHIP_STATUS,
SHIP_ADDRESS
) VALUES (
1,
1,
'2024-7-02',
'2024-7-10',
'FedEx',
'103ABC',
'Delivered',
'103 Main St, Australia'
);

INSERT INTO SHIPMENT (

SHIPMENT_ID,
ORDER_ID,
SHIP_DATE,
DELIVERY_DATE,
SHIP_METHOD,
TRACKING_NUMBER,
SHIP_STATUS,
SHIP_ADDRESS
) VALUES (
2,
1,
'2024-8-05',
NULL,
'UPS',
'556DF',
'In Transit',
'456 Oak St, Los Angeles'
);

Select *from SHIPMENT990;

OUTPUT:

Explanation:
 SHIPMENT_ID: This is the primary key for the shipment, uniquely identifying each shipment.
 ORDER_ID: This is a foreign key that links to the ORDER999 table's ORDER_ID field. It ensures that each
shipment is associated with an order.
 SHIP_DATE: The date the shipment is sent. This field is mandatory.
 DELIVERY_DATE: The date when the shipment is delivered (optional).
 SHIP_METHOD: The shipping method (e.g., "Standard", "Express", etc.).
 TRACKING_NUMBER: A field to store the tracking number for the shipment, which is optional but commonly
used.
 SHIP_STATUS: A field that tracks the shipment's current status (e.g., "Shipped", "In Transit", "Delivered").
 SHIP_ADDRESS: The address where the shipment is being sent, which may differ from the billing address.

Constraints:
 PK_SHIPMENT_ID: This constraint enforces that SHIPMENT_ID is unique and serves as the primary key.
 FK_ORDER_ID: This constraint establishes the foreign key relationship, ensuring that each shipment is linked to
an existing order from the table.

E-R Diagram to show the Relationship of Table and Entity

CUSTOMER011 ORDER01 SHIPMENT001

 PRACTICAL No: - 03

Objective: Writing SQL Statement Using Oracle/SQL.

a) Writing the basic SQL statement .
b) Restricting and sorting data.
c) Displaying the data from multiple table.
d) Manipulating the data .
e) Creating and managing the table.

Create Table Employee151(

emp_no number(5)notNULL, emp_name VARCHAR(30), designation char(10),
doj date,
salary NUMERIC(9,2),address VARCHAR(30),dept_name CHAR(30)
);

Insert into Employee151 (emp_no,emp_name,designation,doj,salary,address,dept_name)

VALUES ('1001','Mayank','officer','12-dec-2000','2000','Delhi','marketing'),
('1002','Khushi','clerk','14-may-2001','5000','Noida','accounts'),
('1003',’Ayush’ ,'manager','23-dec-2000','3500','Bombay','sales');

SELECT * FROM Employee151;

SQL QUERIES:
1. List the Empname,doj from Employee15 table.
SELECT emp_name,doj FROM Employee15;

2. List the names of Employee whois getting 1000Rs.

SELECT emp_name FROM Employee15 WHERE salary=1000;
3. List the name of emp who belong to Bombay and depart name is Sales.
SELECT emp_name FROM Employee15 WHERE address='Bombay' AND dept_name='sales';

4. List the average salary of Khushi.

SELECT avg(salary)FROM Employee15 WHERE emp_name=’Khushi’;

5. List the employees who have joined after 1-Jan-1984 in the order of the joining date.
SELECT emp_name FROM Employee15 WHERE doj>('01-jan-1984');
 PRACTICAL No: - 04

Objective: Normalization a Table

A practical of normalization, we are building a database to store information about students, courses, and
enrollments in a school system. We'll demonstrate how to normalize the database from an unnormalized
state (1NF) to higher forms of normalization (2NF, 3NF).

Example 1: Unnormalized Database (0NF)

Initially, we have a simple table that contains all the data about students, their courses, and grades. The
data might look like this:

Student_ID Student_Name Course_1 Grade_1 Course_2 Grade_2

001 Mayank Btech A MBA B
002 Ayush Btech C Mtech C

This table violates the First Normal Form (1NF), which requires that each field contain only atomic
values (no repeating groups or arrays). Here, the courses and grades for each student are stored in separate
columns, which violate 1NF.

Step 1: First Normal Form (1NF)

To bring this table into 1NF, we need to remove the repeating groups (i.e., multiple courses and grades).
Each row should contain only atomic values, so we'll separate each course and grade into a new row:

Now, each field contains only atomic values, and the table is in 1NF.
Student_ID Student_Name Course Grade
001 Mayank Btech A
001 Mayank MBA B
002 Ayush Btech B
002 Ayush Mtech C
Step 2: Second Normal Form (2NF)

To bring the table into 2NF, we need to eliminate partial dependencies. A partial dependency occurs
when a non-key column depends only on part of the primary key, rather than the whole key.

In this case, Student_Name depends only on Student_ID, not on Course or Grade. The primary key is
composite (both Student_ID and Course), and Student_Name depends only on Student_ID, so we need
to split the table into two:

Students Table (holds information about students):

Student_ID Student_Name
001 Mayank
002 Ayush

Enrollments Table (holds information about enrollments and grades):

Student_ID Course Grade

001 Btech A
001 MBA B
002 Mtech C
002 Btech B

Now, the database is in 2NF because all non-key attributes depend on the whole primary key
(Student_ID and Course), and there are no partial dependencies.

Step 3: Third Normal Form (3NF)

To bring the database into 3NF, we need to remove transitive dependencies. A transitive dependency
occurs when a non-key column depends on another non-key column.

In the Enrollments table, there is no transitive dependency between Course and Grade, but if we added a
Instructor column, we might see a transitive dependency: the Instructor depends on Course, and the
Course is dependent on the primary key (Student_ID, Course).
 To remove transitive dependencies, we would split the table further into:

Students Table (remains unchanged):

Student_ID Student_Name
001 Mayank
002 Ayush

Courses Table (holds information about courses):

Course Instructor
Btech Dr. Arjun
MBA Dr. Shivani
Mtech Dr. Ajay

Enrollments Table (holds information about student enrollments):

Student_ID Course Grade

001 Btech A
001 MBA B
002 Mtech C
002 Btech B

Now, the Enrollments table no longer contains any redundant information about instructors, and all
attributes are non-transitively dependent on the primary key, so the database is in 3NF.

Summary of Normalization Steps

 0NF: Normalized, repeating groups of data.

 1NF: Eliminate repeating groups; make fields atomic.
 2NF: Eliminate partial dependencies by splitting the table into related entities.
 3NF: Eliminate transitive dependencies.

This process reduces redundancy and ensures that the data is organized efficiently. When designing a
database for a real-world application, these normalization steps are critical for maintaining data integrity
and avoiding anomalies when updating or deleting records.
 PRACTICAL No: - 05

Objective: Creating the Procedure & Function.

A PL/SQL procedure is a named block of code that performs a specific task, but it does not return a
value. It can take input parameters, perform operations (such as querying or modifying the database),
and return output through parameters or via side effects (like inserting, updating, or deleting data).

Here is an overview of how to create and use procedures in PL/SQL:

Basic Syntax for Creating a Procedure:

A simple PL/SQL procedure has the following structure:

CREATE OR REPLACE PROCEDURE procedure_name

[ (parameter1 [IN | OUT | IN OUT] datatype,

parameter2 [IN | OUT | IN OUT] datatype, ...) ]

IS

-- Declaration section (optional)

variable_name datatype;

BEGIN

-- Executable statements

statement1;

statement2;

-- Exception handling section (optional)

EXCEPTION

WHEN exception_name THEN

-- Error-handling code

END procedure_name;
  CREATE PROCEDURE: This creates a new procedure. You can use OR REPLACE if you want to
replace an existing procedure with a new definition.

 Procedure Name: The name of the procedure being created.

 Parameter List (Optional): You can specify parameters that the procedure can accept, which
can be IN, OUT, or IN OUT.

 IS / AS: This indicates the start of the procedure’s declaration part, where you can define
variables, constants, or exceptions.

 BEGIN / END: These marks the body of the procedure where the actual processing logic
occurs.

Creating a Simple Procedure Example:

Here is an example of a procedure that adds two numbers and prints the result:

CREATE OR REPLACE PROCEDURE add_two_numbers (

num1 IN NUMBER, -- First input parameter

num2 IN NUMBER -- Second input parameter

IS

result NUMBER; -- Variable to store the sum

BEGIN

-- Calculate the sum

result := num1 + num2;

-- Print the result

DBMS_OUTPUT.PUT_LINE('The sum of ' || num1 || ' and ' || num2 || ' is: ' || result);

END add_two_numbers;

 Num1 and num2 are input parameters of type NUMBER.

 Result is a local variable used to store the sum.

 DBMS_OUTPUT.PUT_LINE is used to print the result.

Executing a Procedure:
To execute a procedure in PL/SQL, you simply call it using an anonymous block, or you can use
SQL*Plus or any PL/SQL interface to invoke the procedure.

EXEC add_two_numbers(10, 40);

This will output:

The sum is: 50

Dropping a Procedure:

DROP PROCEDURE add_two_numbers;

 PRACTICAL No: - 06

Objective: Creating the Trigger & Package.

In SQL, a trigger is a special kind of stored procedure that automatically executes or "fires" when
certain events occur in the database, such as after an insert, update, or delete operation on a table.
Triggers are used for enforcing business rules, auditing, validation, and automatic data modifications.
They are particularly useful for maintaining data integrity and consistency.

Example of a Trigger in SQL

Let’s walk through a practical example of a trigger that tracks changes to an employee's salary in a
company. We’ll use a simple scenario with the following requirements:

 Table Structure:
o Employees91 : Contains employee information.
o salary_audit001 : A table that logs changes to employees' salaries.

Step 1: Create the Tables

CREATE TABLE Employees91 (

employee_id NUMBER NOT NULL PRIMARY KEY,

first_name VARCHAR2(30) NOT NULL,

last_name VARCHAR2(30) NOT NULL,

salary NUMBER(9,2) NOT NULL

);

INSERT INTO Employees91 (employee_id, first_name, last_name, salary)

VALUES (1, 'Mayank', ‘Mathur', 50000.00);

INSERT INTO Employee199 (employee_id, first_name, last_name, salary)

VALUES (2, 'Ayush', 'Tanwar', 60000.00);

Select *from Employees91;

OUTPUT:
CREATE TABLE salary_audit010 (

audit_id NUMBER NOT NULL PRIMARY KEY,

employee_id NUMBER NOT NULL,

old_salary NUMBER(9,2) NOT NULL,

new_salary NUMBER(9,2) NOT NULL,

change_date DATE NOT NULL,

CONSTRAINT fk_employee FOREIGN KEY (employee_id) REFERENCES Employees91(employee_id)

);

INSERT INTO salary_audit01 (audit_id, employee_id, old_salary, new_salary, change_date)

VALUES (1, 1, 80000.00, 85000.00,'2024-7-11');

INSERT INTO salary_audit01 (audit_id, employee_id, old_salary, new_salary, change_date)

VALUES (2, 2, 60000.00, 65000.00,’2024-8-25');

Select *from salary_audit010;

OUTPUT:

Step 2: Create a Trigger to Track Salary Changes

Next, we create a trigger that will automatically log changes made to the salary field in the Employees91
table. The trigger will be fired After an update on the salary column, so it will capture the old value after any
changes are applied.

CREATE OR REPLACE TRIGGER trg_track_salary_changes

AFTER UPDATE OF salary ON Employee91

 FOR EACH ROW

WHEN (OLD.salary <> NEW.salary)

BEGIN

INSERT INTO salary_audit010 (

audit_id,

employee_id,

old_salary,

new_salary,

change_date

) VALUES (

salary_audit010_seq.NEXTVAL, -- Assuming a sequence for audit_id:OLD.employee_id,

:OLD.salary,

:NEW.salary,

SYSDATE

);

END;

Step 3: Testing the Trigger

UPDATE Employees91

SET salary = 100000.00

WHERE employee_id = 1;

SELECT * FROM salary_audit010;

OUTPUT:

Explanation of the Trigger:

 After UPDATE OF salary: This specifies that the trigger will fire after an update is made to the salary
column of the Employees91 table.

 FOR EACH ROW: This means the trigger will fire for each row that is updated, not just once for the entire
operation.

 :OLD.salary: Refers to the old value of the salary before the update.

 :NEW.salary: Refers to the new value of the salary after the update.

 salary_audit_seq.NEXTVAL: We assume you have a sequence salary_audit_seq that generates unique

audit_id values for each record in the salary_audit010 table.

Step 2: Drop the Trigger

If you no longer need the trigger, you can drop it using:

DROP TRIGGER trg_track_salary_changes;

 PRACTICAL No: - 07

Objective: Creating the View and explain.

In SQL, a view is a virtual table that is derived from the result of a query. It can represent a complex
query that combines data from one or more tables, or it can simplify repetitive queries by abstracting
them into a single object that can be referenced like a regular table. Views are used for several purposes,
including simplifying complex queries, improving security by restricting access to certain data, and
providing a layer of abstraction.

Scenario:

You are working with an online store database, and you need to generate reports on customer orders, but
you often need to join the customers, orders, and order_items tables. Instead of writing the same
complex query multiple times, you decide to create a view to simplify this.

Step 1: Create Tables for the Example

Creating the necessary tables to represent a basic e-commerce database with customers, orders, and
order items.

CREATE TABLE Customer549 (

customer_id NUMBER NOT NULL PRIMARY KEY,

first_name VARCHAR2(50) NOT NULL,

last_name VARCHAR2(50) NOT NULL,

email VARCHAR2(100) UNIQUE

);

INSERT INTO Customer549 (customer_id, first_name, last_name, email)

VALUES (1, 'Arjun', 'Rajput', 'arjun@example.com');

INSERT INTO Customer549 (customer_id, first_name, last_name, email)

VALUES (2, 'Manan', 'Sharma', 'manan@example.com');

Select *from Customer549;

OUTPUT:

CREATE TABLE Order090 (

order_id NUMBER NOT NULL PRIMARY KEY,

customer_id NUMBER NOT NULL,

order_date DATE NOT NULL,

total_amount NUMBER(10,2) NOT NULL,

CONSTRAINT FK_customer_id FOREIGN KEY (customer_id) REFERENCES Customer549(customer_id)

);

INSERT INTO Order090 (order_id, customer_id, order_date, total_amount)

VALUES (1, 1,'2024-02-27’, 500.00);

INSERT INTO Order090 (order_id, customer_id, order_date, total_amount)

VALUES (2, 2,'2024-11-28', 450.50);

Select *from Order090;

OUTPUT:

CREATE TABLE Order_Items091 (

order_item_id NUMBER NOT NULL PRIMARY KEY,

order_id NUMBER NOT NULL,

product_name VARCHAR2(100) NOT NULL,

quantity NUMBER NOT NULL,

 unit_price NUMBER(10, 2) NOT NULL,

CONSTRAINT FK_ORDER_ID FOREIGN KEY (order_id) REFERENCES Order090(order_id)

);

INSERT INTO Order_Items091 (order_item_id, order_id, product_name, quantity, unit_price)

VALUES (1, 1, 'CPU', 1, 12000.00);

INSERT INTO Order_Items091 (order_item_id, order_id, product_name, quantity, unit_price)

VALUES (2, 1, 'Monitor', 1, 2500.50);

INSERT INTO Order_Items091 (order_item_id, order_id, product_name, quantity, unit_price)

VALUES (3, 2, 'Keyboard', 2, 450.00);

Select *from Order_Item091

OUTPUT:

Step 2: Create a View for Order Details

Create a view that joins the Customer29, Order37, and Order_items67 tables to give us detailed
information about each order, including customer details, order items, and total amounts.

CREATE VIEW CustomerOrderDetails01 AS

SELECT

c.customer_id,

c.first_name || ' ' || c.last_name AS customer_name,

c.email,

o.order_id,

o.order_date,

o.total_amount,

i.product_name,

i.quantity,
 i.unit_price,

(i.quantity * i.unit_price) AS total_item_price

FROM

Order090

INNER JOIN

Customer549 c ON o.customer_id = c.customer_id

INNER JOIN

Order_Items091 i ON o.order_id = i.order_id;

Explanation:
 CREATE OR REPLACE VIEW OrderDetails: Creates the view named CustomerOrderDetails01
If a view with the same name already exists, it will be replaced.
 SELECT statement: The view combines data from the Customer549, Order090, and
Order_item091 tables. It also calculates the total for each item (item_total = quantity *
unit_price).
 JOIN operations: We join the three tables (Customer549, Order090, Order_item091) based on
their relationships (i.e., customer_id and order_id).

Step 3: Query the View

Now that the view is created, you can query it as if it were a regular table. This simplifies the process of
fetching detailed order information.

SELECT * FROM CustomerOrderDetails01;

 PRACTICAL No: - 08
Objective: Creating Database and Describe the Join Operation in SQL.

In SQL, a join operation is used to combine rows from two or more tables based on a related column
between them. There are different types of joins, such as INNER JOIN, LEFT JOIN, RIGHT JOIN,
and FULL JOIN.

Here's a practical example using two sample tables:

1. Employees table:

employee_id employee_name department_id

1 Mayank 101

2 Khushi 102

3 Manvi 101

4 Ayush 103

2. Departments table:

department_id department_name

101 HR

102 IT

103 Marketing

Example 1: INNER JOIN

This operation returns rows when there is a match in both tables.

 SELECT

Employees001.employee_id,

Employees001.employee_name,

Employees001.department_name

FROM

Employees001

INNER JOIN

Departments002

ON

Employees001.department_id = Departments002.department_id;

Result:

employee_id employee_name department_name

1 Mayank HR

2 Khushi IT

3 Manvi HR

4 Ayush Marketing

Example 2: LEFT JOIN

This operation returns all rows from the left table (Employees), and the matched rows from the right
table (Departments). If there is no match, NULL values are returned for columns from the right table

SELECT

Employees001.employee_id,

Employees001.employee_name,

Employees001.department_name

FROM
 Employees001

INNER JOIN

Departments002

ON

Employees001.department_id = Departments002.department_id;

Result:

employee_id employee_name department_name

1 Mayank HR

2 Khushi IT

3 Manvi HR

4 Ayush Marketing

(Note: In this example, all employees have matching departments, so the result is similar to the INNER
JOIN. However, if there were employees without a department, the department_name would show as
NULL.)

Example 3: RIGHT JOIN

This operation returns all rows from the right table (Departments), and the matched rows from the left
table (Employees). If there is no match, NULL values are returned for columns from the left table.

SELECT

Employees001.employee_id,

Employees001.employee_name,

Employees001.department_name

FROM

Employees001

INNER JOIN
 Departments002

ON

Employees001.department_id = Departments002.department_id;

Result:

employee_id employee_name department_name

1 Mayank HR
2 Khushi IT
3 Manvi HR
4 Ayush Marketing

(Note: If there were departments without any employees, the employee_id and employee_name would
show as NULL.)
 PRACTICAL No: - 09

Objective: Creating & implement cursor in SQL.

A cursor in SQL is a database object used to retrieve, manipulate, and navigate through a result set row
by row. Cursors are particularly useful when you need to process each row individually, such as when
performing operations that are difficult or impossible to express in a single SQL query.

Here is a practical example that demonstrates how to create and use a cursor in SQL.

Scenario

we have the following table of Employees909:

employee_id employee_name salary

1 Mayank 5000

2 Khushi 6000

3 Manvi 7000

4 Ayush 8000

The goal is to use a cursor to update each employee's salary by increasing it by 10% individually.

Steps to Create and Use a Cursor:

1. Declare the cursor – This defines the SQL query that retrieves the rows.
2. Open the cursor – This makes the cursor operational.
3. Fetch the data – This retrieves the rows one by one from the cursor.
4. Process the data – Perform operations like UPDATE or INSERT on each row.
5. Close the cursor – After processing, release the cursor.

SQL Code Example:

 DECLARE

CURSOR emp_cursor IS

SELECT employee_id, salary FROM Employees909

v_employee_id Employees909.employee_id%TYPE;

v_salary Employees909.salary%TYPE;

BEGIN

OPEN emp_cursor;

LOOP

FETCH emp_cursor INTO v_employee_id, v_salary;

EXIT WHEN emp_cursor%NOTFOUND;

UPDATE Employees909

SET salary = v_salary * 1.10

WHERE employee_id = v_employee_id;

END LOOP;

CLOSE emp_cursor;

COMMIT;

END;

Select *from Employees909;

OUTPUT:
Before executing the PL/SQL block

After executing the PL/SQL block

 Explanation:

1. Declare the cursor:

The DECLARE employee_cursor CURSOR FOR command defines the cursor and the SQL query that
retrieves the employees' data (employee_id, employee_name, and salary).
2. Open the cursor:
The OPEN command activates the cursor, making it ready to retrieve data.
3. Fetch the data:
The FETCH NEXT FROM employee_cursor INTO ... statement retrieves the next row from the result
set into variables (@EmployeeID, @EmployeeName, and @Salary).
4. Process the data:
Inside the WHILE loop, the UPDATE command increases each employee's salary by 10%. You can also
perform other operations like logging or further computations here.
5. Close the cursor:
After processing all rows, you close the cursor with CLOSE and release the associated resources using
DEALLOCATE.

Key Notes:

 Cursor operations can be resource-intensive, especially for large result sets. It's best to use cursors only when
necessary (e.g., when complex logic requires row-by-row processing).
 Alternative approaches: In many cases, set-based operations (such as UPDATE, INSERT, or SELECT with a
JOIN) can be more efficient than using cursors. Cursors should be considered a last resort when set-based
solutions are not feasible.

Performance Considerations:
 Cursors can cause performance issues, especially on large result sets, because they process rows one at a time.
When possible, always try to use set-based operations (such as an UPDATE statement that processes all rows at
once) instead of row-by-row cursors.
 PRACTICAL No: - 10

Objective:- To implement the basics of PL/SQL.

Introduction – PL/SQL bridges the gap between database technology and procedural
programming languages. It can be thought of as a development tool that extends the facilities of
Oracles SQL database language. Via PL/SQL you can insert, delete, update and retrieve table
data as well as use procedural techniques such as writing loops or branching to another block of
code.

PL/SQL Block structure-

DECLARE
Declarations of memory variables used later
BEGIN
SQL executable statements for manipulating table data.
EXCEPTIONS
SQL and/or PL.SQL code to handle
errors. END;

Displaying user Messages on the screen – Any programming tool requires a method through
which messages can be displayed to the user.

dbms_output is a package that includes a number of procedure and functions that accumulate
information in a buffer so that it can be retrieved later. These functions can also be used to
display message to the user.

put_line: put a piece of information in the buffer followed by a end of line marker. It can also
be used to display message to the user.
Setting the server output on:

SET SERVER OUTPUT ON:

Example: Write the following code in the PL/SQL block to display message to user
DBMS_OUTPUT.PUT_LINE(‘Display user message’)

Conditional control in PL/SQL-

Syntax:
IF <condition> THEN
<Action>
ELSEIF<condition>
<Action>
ELSE
<Action>
ENDIF;
The WHILE LOOP:
Syntax:-
WHILE <condition> LOOP
<Action>
END LOOP;

The FOR LOOP statement:

Syntax:
FOR variable IN [REVERSE] start—end
LOOP
<Action>
END LOOP;

The GOTO statement: The goto statement allows you to change the flow of control within a
PL/SQL Block.
 PRACTICAL No: - 11

In SQL Server, automatic backups and recovery processes are crucial for maintaining database
integrity and ensuring that data can be restored in case of failure. Below are the steps for setting up
automatic backups and recovery.

Setting Up Automatic Backup in SQL Server

Step 1: Configure Backup Plan Using SQL Server Agent

SQL Server Agent is a tool that can be used to automate jobs such as backups. Here's how to set up an
automated backup plan:

1. Open SQL Server Management Studio (SSMS)

o Connect to your SQL Server instance.

2. Enable SQL Server Agent (if not already enabled)

o In SSMS, under "Object Explorer," expand the SQL Server instance.
o Right-click on "SQL Server Agent" and select Start (if it's not running).

3. Create a New Backup Job

o Expand the "SQL Server Agent" node in Object Explorer.
o Right-click on "Jobs" and select New Job.
o In the New Job window, provide a name for the backup job (e.g., "Automated Full Backup").

4. Add a Step for the Backup Command

o Under the Steps tab, click on New to create a step.
o In the New Job Step window:
 Step Name: Give it a descriptive name (e.g., "Full Backup Step").
 Type: Select "Transact-SQL script (T-SQL)".
 Database: Choose the database to back up.
 Command: Enter the T-SQL command for the backup, such as:
 BACKUP DATABASE [YourDatabaseName]
 TO DISK = 'C:\Backups\YourDatabaseName_full.bak'
 WITH FORMAT, MEDIANAME = 'YourBackupMedia', NAME = 'Full Backup of
YourDatabase';
 Click OK to save the step.

5. Set a Schedule for the Backup Job

o Under the Schedules tab, click New to define a backup schedule.
o Set the frequency (e.g., daily, weekly) and the time of the backup.
o Example: Daily at 4:00 AM.
o Click OK to save the schedule.

6. Enable the Job

 o Under the Notifications tab (optional), you can configure an email notification if the job succeeds or
fails.
o Click OK to create the backup job.

Now, your SQL Server will automatically run the backup job as per the schedule you've defined.

Step 2: Configure Differential and Transaction Log Backups (Optional)

If you need a more granular backup strategy, you can also automate differential backups and transaction log
backups in a similar way:

 Differential Backup Command:

 BACKUP DATABASE [YourDatabaseName]
 TO DISK = 'C:\Backups\YourDatabaseName_diff.bak'
 WITH DIFFERENTIAL;

 Transaction Log Backup Command:

 BACKUP LOG [YourDatabaseName]
 TO DISK = 'C:\Backups\YourDatabaseName_log.trn';
2. Setting Up Recovery for SQL Server

To perform a recovery of a SQL Server database, you need the backup files and the recovery model
configuration (Full, Simple, or Bulk-logged). The following steps outline how to recover a database from a
backup:

Step 1: Restore a Full Backup

1. Open SQL Server Management Studio (SSMS) and connect to your SQL Server instance.
2. Restore Full Backup:
o In Object Explorer, right-click on Databases and select Restore Database.
o Select the Device option and then choose the backup file.
o If the backup file is located on disk, click Add and locate the .bak file.
o Click OK to restore the database.

Alternatively, you can use T-SQL to restore the database:

RESTORE DATABASE [YourDatabaseName]

FROM DISK = 'C:\Backups\YourDatabaseName_full.bak'
WITH REPLACE;
Step 2: Restore a Differential Backup (if needed)

If you have a differential backup after the full backup, you will need to restore the full backup first, and then
apply the differential backup.

RESTORE DATABASE [YourDatabaseName]

FROM DISK = 'C:\Backups\YourDatabaseName_full.bak';

RESTORE DATABASE [YourDatabaseName]

FROM DISK = 'C:\Backups\YourDatabaseName_diff.bak'
WITH NORECOVERY;
The WITH NORECOVERY option ensures that the database remains in a restoring state, allowing you to apply
transaction log backups afterward.

Step 3: Restore Transaction Log Backups (if needed)

If you have transaction log backups, you can restore them to bring the database to the most recent point in time.

1. Restore the Transaction Log Backup:

2. RESTORE LOG [YourDatabaseName]
3. FROM DISK = 'C:\Backups\YourDatabaseName_log.trn'
4. WITH NORECOVERY;

5. Repeat the Process for subsequent transaction log backups, if needed, until the database is restored to
the desired point in time.
6. Finalize the Restore Process: Once all necessary backups have been restored, you can bring the
database online with:
7. RESTORE DATABASE [YourDatabaseName]
8. WITH RECOVERY;
Step 4: Recovery Model Considerations

Make sure your database’s recovery model is set correctly based on your backup and recovery strategy:

 Full Recovery Model: Supports full, differential, and transaction log backups.
 Simple Recovery Model: Only supports full and differential backups; transaction log backups are not available.
 Bulk-Logged Recovery Model: Similar to the full recovery model, but allows minimal logging for bulk operations.

You can check and set the recovery model using:

-- Check recovery model

SELECT name, recovery_model_desc
FROM sys.databases
WHERE name = 'YourDatabaseName';

-- Change recovery model

ALTER DATABASE [YourDatabaseName]
SET RECOVERY FULL;
3. Automating Backups with SQL Server Maintenance Plans

SQL Server also provides Maintenance Plans that can help automate backup tasks without the need for SQL
Server Agent jobs. You can create a maintenance plan using the Maintenance Plan Wizard in SSMS:

1. Right-click on Management > Maintenance Plans > New Maintenance Plan.

2. Use the Maintenance Plan Wizard to define tasks, including backups.
3. Define the schedule and choose which databases to back up.
4. Save and schedule the plan.

Conclusion

By following these steps, you can set up automatic backups in SQL Server and also recover your database
when needed. It's important to implement a proper backup strategy (including full, differential, and transaction
log backups) to ensure data integrity and quick recovery in case of failure.