3.
Implement Database using SQL data definition
Constraints and Views
1.Creating table with constraints:
CREATE TABLE customer1(cust_id NUMBER(6)PRIMARY
KEY,cust_nameVARCHAR2(50)NOTNULL,mobile_noNUMBER(10)UNIQUECHECK(L
ENGTH(mobile_no)=10)ageNUMBER(2)CHECK(age>=18,city_id
NUMBER(3)REFERENCES city(city_id));
INSERT INTO customer_1VALUES(1000,'mohan',9042982079,20,10);
INSERT INTO customer_1VALUES(2000,'vijay',1234567890,23,20);
INSERT INTO customer_1VALUES(3000,'ganesh',8825462254,25,30);
select*from customer_1;
Create the table for city:
CREATE TABLE city (City_id INTEGER PRIMARY KEY,City_name TEXT);
INSERT INTO city VALUES(10,'chennai');
INSERT INTO city VALUES(20,'mumbai');
INSERT INTO city VALUES(30,'delhi');
select*
from city;
2.Creating the view:
create or replace view customer_1_v as select cust_id,cust_name,mobile_no,city_id
from customer_1;
select*from customer_;
4.Query the database using SQL Manipulation:
Data manipulation language is insert, update, delete, select.
1.Creating database using SQL Manipulation:
CREATE TABLE customer_2(cust_id
NUMBER(6),cust_nameVARCHAR2(50),mobile_no NUMBER(10),dob
DATE,city_name VARCHAR2(20),email_id VARCHAR2(50));
2.Insert record:
INSERTINTOcustomer(cust_id,cust_name,mobile_no,city_name,dob,email_id)VAL
UES(123456,'mohan',9874561230,'santhur',TO_DATE('12/16/2024','mm/dd/
yyyy'),'mohan@gmail.com');
INSERTINTOcustomer_2(cust_id,cust_name,mobile_no,city_name,dobemail_id)V
ALUES(222,'null',9874561230,'santhur',TO_DATE('12/16/2024','mm/dd/
yyyy'),'user@gmail.com');
3.Update record:
UPDATE customer_2SET cust_name='Tamil'WHERE cust_id=222;
4.Select statement: Select *from customer_2:
5.Delete statement:Delete from customer_2;
5. Querying/Managing the database using SQL Programming -
Stored Procedures/Functions -Constraints and security using
Triggers:
Creating Table:
CREATE TABLE employee_records (
emp_id NUMBER,
emp_name VARCHAR2(100),
emp_position VARCHAR2(100),
salary NUMBER);
Stored Procedure:
CREATE OR REPLACE PROCEDURE insert_emp_records (emp_id IN
NUMBER,emp_name IN VARCHAR2, VARCHAR to VARCHAR2
emp_position IN VARCHAR2, salary IN NUMBER) IS
BEGIN
INSERT INTO employee_records (emp_id, emp_name, emp_position, salary)
VALUES (emp_id, emp_name, emp_position, salary);END;
/BEGIN
insert_emp_records(3, 'Tamil', 'employee', 5000);
END;
Function:
CREATE OR REPLACE FUNCTION salary_hike_fun(salary NUMBER)RETURN
NUMBER IS
BEGIN RETURN salary * 2; END;/
SELECT emp_name, salary, salary_hike_fun(salary) AS new_salary
FROM employee_records;
Contraint:
ADD CONSTRAINT pk_emp_id PRIMARY KEY(emp_id);
ALTER TABLE employee_records
Trigger:
CREATE TABLE employee_records_bkp_trigger (
emp_id NUMBER,
emp_name VARCHAR2(100),
emp_position VARCHAR2(100),
salary NUMBER,
deleted_date DATE,
deleted_by VARCHAR2(50));
CREATE OR REPLACE TRIGGER after_delete_trig
AFTER DELETE ON employee_records
FOR EACH ROW
BEGIN
INSERT INTO employee_records_bkp_trigger
VALUES(:old.emp_id, :old.emp_name, :old.emp_position, :old.salary,
SYSDATE, USER);
END;
/
Delete the record:
DELETE FROM employee_records WHERE emp_id = 1;
8.Create a table for Employee details with Employee Number as
primary key and following fields: Name, Designation, Gender,
Age, Date of Joining and Salary. Insert at least ten rows and
perform various queries using any one Comparison, Logical, Set,
Sorting and Grouping operators.
1.Creating Table:
create table Employee_details(emp_number number PRIMARY KEY,emp_name
VARCHAR(50) NOT NULL,designation VARCHAR(50),gender
VARCHAR(10),age number,date_of_joining DATE,salary DECIMAL(10,2));
INSERT INTO Employee_details (emp_number, emp_name, designation, gender,
age, date_of_joining, salary)VALUES(1,'John','manager','Male',45,
(TO_DATE('12/16/201','mm/dd/yyyy')),75000.00);
INSERT INTO Employee_details (emp_number, emp_name, designation, gender,
age, date_of_joining, salary) VALUES
(2,'Smith','Developer','Female',30,(TO_DATE('12/16/2022','mm/dd/yyyy')),
60000.00);
INSERT INTO Employee_details (emp_number, emp_name, designation, gender,
age, date_of_joining, salary) VALUES(3, 'Johnson', 'Designer','Female',28,
(TO_DATE('12/16/2023','mm/dd/yyyy')),55000);
INSERT INTO Employee_details (emp_number, emp_name, designation, gender,
age, date_of_joining, salary) VALUES
(4,'Bob', 'Tester', 'Male', 35,(TO_DATE('12/16/2024','mm/dd/yyyy')), 50000.00);
INSERT INTO Employee_details (emp_number, emp_name, designation, gender,
age, date_of_joining, salary) VALUES
(5,'Charlie','HR', 'Male', 40,(TO_DATE('12/16/2025','mm/dd/yyyy')), 58000.00);
INSERT INTO Employee_details (emp_number, emp_name,
designation,gender,age,date_of_joining,salary)
VALUES(6,'Diana','Developer','Female',32,(TO_DATE('12/16/2026','mm/dd/
yyyy')), 62000.00);
INSERT INTO Employee_details (emp_number, emp_name,
designation,gender,age,date_of_joining,salary)
VALUES(7,'Ethan','Manager','Male',50,(TO_DATE('12/16/2027','mm/dd/yyy)),
80000.00);
select* from Employee_details;
2.Comparison Operator:
SELECT * FROM Employee_details WHERE salary > 60000;
3.Logical Operator:
SELECT * FROM Employee_details WHERE gender = 'Male' AND age > 40;
4.Set Operator:
SELECT * FROM Employee_details WHERE date_of_joining <
(TO_DATE('12/16/2023','mm/dd/yyyy'))
UNION
SELECT * FROM Employee_details WHERE date_of_joining >
(TO_DATE('12/16/2028','mm/dd/yyyy'));
5.Sorting:
SELECT * FROM Employee_details ORDER BY salary DESC;
6.Grouping:
SELECT designation, AVG(salary) AS average_salary
FROM Employee_details GROUP BY designation;
9.Write a PL/SQL to update the rate field by 20% more than the
current rate in inventory table which has the following fields:
Prono, ProName and Rate. After updating the table a new field
(Alter) called for Number of item and place for values for the
new field without using PL/SQL block.
Creating Table:
CREATE TABLE customer_dts ( name VARCHAR2(500),proname
VARCHAR2(500), salary NUMBER,job VARCHAR2(500));
INSERT INTO customer_dts (name, proname, salary, job) VALUES ('Arivu', 'K',
2500, 'Saluman');
INSERT INTO customer_dts (name, proname, salary, job) VALUES ('Krish', 'M',
3000, 'Manager');
PL/SQL block:
CREATE OR REPLACE PROCEDURE update_salary (
p_name IN VARCHAR2,
p_proname IN VARCHAR2,
p_percent IN NUMBER)
AS v_salary NUMBER;
v_rate NUMBER;
BEGIN
SELECT salary INTO v_salary
FROM customer_dts
WHERE name = p_name AND
proname = p_proname;
v_rate:=v_salary*p_percent/100; DBMS_OUTPUT.PUT_LINE('Increase Amount:
' || v_rate); UPDATE customer_dts SET salary = salary + v_rate
WHERE name=p_name AND
proname = p_proname;
DBMS_OUTPUT.PUT_LINE('Salary Updated Successfully');
EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error!
Check the code: '||SQLERRM);
END;/
Update the Salary:
BEGIN
update_salary('Arivu', 'K', 20);
END;
/
SELECT * FROM customer_dts;
SELECT 500 * 20 / 100 FROM dual;
SELECT * FROM all_objects WHERE object_type = 'TABLE';
6.Database design using Normalization –bottom-up approach:
CREATE TABLE EmployeeData (emp_id INT,emp_name
VARCHAR(50),emp_addressVARCHAR(100),emp_departmentVARCHAR(50),em
p_salaryDECIMAL(10,2),emp_manager VARCHAR(50));
insert into EmployeeData
values(1, 'John Doe', '123 Main St', 'IT', 70000, 'Alice Smith');
insert into EmployeeData
values (2, 'Jane Doe', '456 Elm St', 'HR', 65000, 'Bob Johnson');
Apply First Normal Form (1NF):
CREATE TABLE Employee (
emp_id INT PRIMARY KEY,
emp_name VARCHAR(50),
street VARCHAR(50),
city VARCHAR(50),state VARCHAR(50),zip_code VARCHAR(10));
INSERT INTO Employee (emp_id, emp_name, street, city, state, zip_code)
VALUES
(1, 'John Doe', '123 Main St', 'Springfield', 'IL', '62701'),
INSERT INTO Employee (emp_id, emp_name, street, city, state, zip_code)
VALUES
(2, 'Jane Doe', '456 Elm St', 'Chicago', 'IL', '60601');
select* from Employee;
Apply Second Normal Form (2NF):
CREATE TABLE Department (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(50));
INSERT INTO Department (dept_id, dept_name) VALUES (1, 'IT');
CREATE TABLE Manager (
manager_id INT PRIMARY KEY,
manager_name VARCHAR(50));
INSERT INTO Manager (manager_id, manager_name)VALUES (1, 'Alice
Smith');
CREATE TABLE EmployeeDepartment (
emp_id INT,
dept_id INT,
PRIMARY KEY (emp_id, dept_id),
FOREIGN KEY (emp_id) REFERENCES Employee(emp_id),
FOREIGN KEY (dept_id) REFERENCES Department(dept_id)
);
INSERT INTO EmployeeDepartment (emp_id, dept_id)
VALUES (1, 1);
select*from EmployeeDepartment;
Apply Third Normal Form (3NF):
CREATE TABLE EmployeeManager
( emp_id INT, manager_id INT,
PRIMARY KEY (emp_id, manager_id),
FOREIGN KEY (emp_id) REFERENCES Employee(emp_id),
FOREIGNKEY (manager_id) REFERENCES Manager(manager_id));
INSERT INTO EmployeeManager (emp_id, manager_id)
VALUES (1, 1);
Final Database Design After Normalization:
SELECT e.emp_name, d.dept_name
FROM Employee e
JOIN EmployeeDepartment ed ON e.emp_id = ed.emp_id
JOIN Department d ON ed.dept_id = d.dept_id;
7.Develop database applications
PL/SQL BLOCK:
CREATE OR REPLACE PACKAGE
XXTTC_NURSERY_MANAGEMENT_PKG AS PROCEDURE ITEM_MASTER (
p_item_code IN VARCHAR2,
p_item_description IN VARCHAR2,
p_item_type IN VARCHAR2,
p_unit_of_measure IN VARCHAR2,
p_inventory_item_flag IN VARCHAR2,
p_facility_code IN VARCHAR2,
p_creation_date IN DATE,
p_created_by IN VARCHAR2,
p_last_update_date IN DATE,
p_last_update_by IN VARCHAR2);
END XXTTC_NURSERY_MANAGEMENT_PKG;/
CREATE OR REPLACE PACKAGE BODY
XXTTC_NURSERY_MANAGEMENT_PKG AS
PROCEDURE ITEM_MASTER (
p_item_code IN VARCHAR2,
p_item_description IN VARCHAR2,
p_item_type IN VARCHAR2,
p_unit_of_measure IN VARCHAR2,
p_inventory_item_flag IN VARCHAR2,
p_facility_code IN VARCHAR2,
p_creation_date IN DATE,
p_created_by IN VARCHAR2,
p_last_update_date IN DATE,
p_last_update_by IN VARCHAR2) AS
v_item_count NUMBER;
p_item_id NUMBER;
v_facility_id VARCHAR2(50);
BEGIN
SELECT facility_id
INTO v_facility_id
FROM XXTTC_FACLITY_DETAILS
WHERE facility_code = p_facility_code;
SELECT item_id.NEXTVAL
INTO p_item_id
FROM dual;
SELECT COUNT(*)
INTO v_item_count
FROM XXTTC_ITEM_MASTER
WHERE item_code = p_item_code;
IF v_item_count > 0 THEN
DBMS_OUTPUT.PUT_LINE('Inserted data already exists.');
ELSE
INSERT INTO XXTTC_ITEM_MASTER (
item_id, item_code, item_description, item_type, unit_of_measure,
inventory_item_flag, facility_id, creation_date, created_by,
last_update_date, last_update_by)
VALUES (p_item_id, p_item_code, p_item_description, p_item_type,
p_unit_of_measure,
p_inventory_item_flag, v_facility_id, p_creation_date, p_created_by,
p_last_update_date, p_last_update_by);
DBMS_OUTPUT.PUT_LINE('Data inserted successfully.');
END IF;COMMIT;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Logical error occurred: ' || SQLERRM);
END ITEM_MASTER;
END XXTTC_NURSERY_MANAGEMENT_PKG;
BEGIN
XXTTC_NURSERY_MANAGEMENT_PKG.ITEM_MASTER(
'W0000016', 'Senthura sedi', 'sedi', 'EACH', 'Y', 'SAN',
SYSDATE, 'Mohan Ganesh', SYSDATE, 'Mohan Ganesh'
);
END
;/
SELECT * FROM USER_ERRORS WHERE NAME =
'XXTTC_NURSERY_MANAGEMENT_PKG';
CREATE SEQUENCE item_id START WITH 1 INCREMENT BY 1;
10. Querying the Object-relational database using
Object Query language
Creating Table and Inserting Values:
Author table:
CREATE TABLE Author (
AuthorID NUMBER PRIMARY KEY,
Name VARCHAR2(100) NOT NULL
);
INSERT INTO Author (AuthorID, Name) VALUES (1, 'J.K. Rowling');
INSERT INTO Author (AuthorID, Name) VALUES (2, 'George Orwell');
Select*fromAuthor;
Publisher table:
CREATE TABLE Publisher (
PublisherID NUMBER PRIMARY KEY,
Name VARCHAR2(100) NOT NULL);
INSERT INTO Publisher (PublisherID, Name) VALUES (1, 'Penguin Books');
INSERT INTO Publisher (PublisherID, Name) VALUES (1, 'Penguin Books');
Select*from Publisher;
Book table:
CREATE TABLE Book (
BookID NUMBER PRIMARY KEY,
Title VARCHAR2(200) NOT NULL,
Author NUMBER REFERENCES Author(AuthorID),
Publisher NUMBER REFERENCES Publisher(PublisherID),
Price NUMBER(10,2)
);
INSERT INTO Book (BookID, Title, Author, Publisher, Price)
VALUES (1, ‘Harry Potter;, 1, 1, 25.50);
INSERT INTO Book (BookID, Title, Author, Publisher, Price)
VALUES (2, ‘1984’, 2, 2, 15.99);
Select*from Book;
CREATE TABLE Ebook (
EbookID NUMBER PRIMARY KEY,
Title VARCHAR2(200) NOT NULL,
FileFormat VARCHAR2(10) NOT NULL);
INSERT INTO Ebook (EbookID, Title, FileFormat)
VALUES (1, ‘Digital Fortress’, ‘PDF’);
1. Get Book Titles and Author Names
SELECT b.Title, a.Name
FROM Book b
INNER JOIN Author a ON b.Author = a.AuthorID;
2. Get Books Published by “Penguin Books”
SELECT b.Title, p.Name
FROM Book b
INNER JOIN Publisher p ON b.Publisher = p.PublisherID
WHERE p.Name = ‘Penguin Books’;
3. Get Books Where Price > 20
SELECT b.Title, b.Price
FROM Book b
WHERE b.Price > 20;
4. Get Ebooks in PDF Format
SELECT e.Title, e.FileFormat
FROM Ebook e
WHERE e.FileFormat = ‘PDF’;
5. Get Books and Publisher Names, Ordered by Title
SELECT b.Title, p.Name
FROM Book b
INNER JOIN Publisher p ON b.Publisher = p.PublisherID
ORDER BY b.Title;
6. Get Count of Books by Each Author
SELECT a.Name, COUNT(b.BookID) AS BookCount
FROM Author a
LEFT JOIN Book b ON b.Author = a.AuthorID
GROUP BY a.Name;
7. Get Books with Author and Publisher Names
SELECT b.Title, a.Name AS AuthorName, p.Name AS PublisherName
FROM Book b
INNER JOIN Author a ON b.Author = a.AuthorID
INNER JOIN Publisher p ON b.Publisher = p.PublisherID;
