Scribd
Upload
4 views2 pages

Advanced SQL and PLSQL Programs 250407 210106

The document outlines SQL and PL/SQL programming concepts, including the creation of tables with constraints, triggers for managing data changes, and aggregate functions for data analysis. It provides examples of SQL queries using GROUP BY, HAVING, and ORDER BY clauses, as well as PL/SQL code for generating a multiplication table. Additionally, it includes triggers for logging actions on student and supermarket tables during insertions and deletions.

Uploaded by

varaprasad2k5
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
4 views2 pages

Advanced SQL and PLSQL Programs 250407 210106

The document outlines SQL and PL/SQL programming concepts, including the creation of tables with constraints, triggers for managing data changes, and aggregate functions for data analysis. It provides examples of SQL queries using GROUP BY, HAVING, and ORDER BY clauses, as well as PL/SQL code for generating a multiplication table. Additionally, it includes triggers for logging actions on student and supermarket tables during insertions and deletions.

Uploaded by

varaprasad2k5
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

SQL & PL/SQL Programs (Constraints, Triggers, Functions)

4a) Constraints

-- 4a) Constraints using different tables

-- Table with UNIQUE and PRIMARY KEY


CREATE TABLE Employees (
emp_id INT PRIMARY KEY,
email VARCHAR(100) UNIQUE
);

-- Table with CHECK


CREATE TABLE Products (
product_id INT PRIMARY KEY,
price NUMBER CHECK (price > 0)
);

4b) Trigger Before Delete

-- 4b) Trigger: BEFORE DELETE


CREATE OR REPLACE TRIGGER before_delete_supermarket
BEFORE DELETE ON supermarket
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('Deleting item: ' || :OLD.item_name);
END;
/

5a) Aggregate Functions

-- 5a) Aggregate Functions


SELECT COUNT(*) FROM Products;
SELECT MAX(price) FROM Products;
SELECT MIN(price) FROM Products;
SELECT AVG(price) FROM Products;
SELECT SUM(price) FROM Products;

5b) Multiplication Table

-- 5b) PL/SQL to print multiplication table

DECLARE
num NUMBER := 5;
i NUMBER := 1;
BEGIN
WHILE i <= 10 LOOP
DBMS_OUTPUT.PUT_LINE(num || ' x ' || i || ' = ' || (num * i));
i := i + 1;
END LOOP;
END;
/

6a) Group by, Having, Order by

-- 6a) GROUP BY, HAVING, ORDER BY

-- GROUP BY and HAVING


SELECT product_id, COUNT(*)
FROM Products
GROUP BY product_id
HAVING COUNT(*) > 1;

-- ORDER BY
SELECT * FROM Products
ORDER BY price DESC;

6b) Trigger on Student Table

-- 6b) Trigger on student table


CREATE OR REPLACE TRIGGER before_insert_student
BEFORE INSERT ON Students
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('Inserting student: ' || :[Link]);
END;
/

7a) Keywords AND, IS NULL, NOT

-- 7a) Using AND, IS NULL, NOT

SELECT * FROM Students WHERE Name IS NULL;


SELECT * FROM Students WHERE Marks > 50 AND Marks < 90;
SELECT * FROM Students WHERE NOT Name = 'John';

7b) Trigger on Student Table

-- 7b) Trigger on student table


CREATE OR REPLACE TRIGGER after_update_student
AFTER UPDATE ON Students
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('Student updated from ' || :[Link] || ' to ' || :[Link]);
END;
/

You might also like