Scribd
Upload
8 views3 pages

SQL and PL/SQL Code Samples Guide

The document provides SQL and PL/SQL code samples, covering advanced SQL queries such as joins, subqueries, aggregate functions, and the use of GROUP BY and HAVING clauses. It also introduces PL/SQL concepts including blocks, IF statements, loops, procedures, and functions. These examples serve as practical applications for database management and programming in SQL and PL/SQL.

Uploaded by

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

SQL and PL/SQL Code Samples Guide

The document provides SQL and PL/SQL code samples, covering advanced SQL queries such as joins, subqueries, aggregate functions, and the use of GROUP BY and HAVING clauses. It also introduces PL/SQL concepts including blocks, IF statements, loops, procedures, and functions. These examples serve as practical applications for database management and programming in SQL and PL/SQL.

Uploaded by

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

Week 3: SQL and PL/SQL Code Samples

Day 1–2: Advanced SQL Queries

1. Joins

-- INNER JOIN Example


SELECT [Link], departments.dept_name
FROM employees
INNER JOIN departments ON employees.dept_id = [Link];

-- LEFT JOIN Example


SELECT [Link], departments.dept_name
FROM employees
LEFT JOIN departments ON employees.dept_id = [Link];

-- RIGHT JOIN Example


SELECT [Link], departments.dept_name
FROM employees
RIGHT JOIN departments ON employees.dept_id = [Link];

-- FULL JOIN Example (for databases that support it)


SELECT [Link], departments.dept_name
FROM employees
FULL OUTER JOIN departments ON employees.dept_id = [Link];

2. Subqueries

-- Subquery to find employees earning more than the average salary


SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- Correlated Subquery
SELECT name
FROM employees e1
WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e1.dept_id =
e2.dept_id);
3. Aggregate Functions

-- Total number of employees


SELECT COUNT(*) FROM employees;

-- Average salary
SELECT AVG(salary) FROM employees;

-- Maximum salary
SELECT MAX(salary) FROM employees;

-- Minimum salary
SELECT MIN(salary) FROM employees;

4. GROUP BY and HAVING

-- Grouping by department and filtering


SELECT dept_id, AVG(salary) as avg_salary
FROM employees
GROUP BY dept_id
HAVING AVG(salary) > 50000;

Day 3–4: Introduction to PL/SQL

1. PL/SQL Block

DECLARE
bonus NUMBER;
BEGIN
bonus := 500;
DBMS_OUTPUT.PUT_LINE('Bonus amount is: ' || bonus);
END;

2. IF Statement and Loop

DECLARE
marks NUMBER := 75;
BEGIN
IF marks >= 90 THEN
DBMS_OUTPUT.PUT_LINE('Grade A');
ELSIF marks >= 75 THEN
DBMS_OUTPUT.PUT_LINE('Grade B');
ELSE
DBMS_OUTPUT.PUT_LINE('Grade C');
END IF;
END;

3. WHILE Loop Example

DECLARE
i NUMBER := 1;
BEGIN
WHILE i <= 5 LOOP
DBMS_OUTPUT.PUT_LINE('Iteration: ' || i);
i := i + 1;
END LOOP;
END;

4. Procedure Example

CREATE OR REPLACE PROCEDURE ShowBonus (emp_id IN NUMBER) IS


emp_bonus NUMBER;
BEGIN
SELECT salary * 0.1 INTO emp_bonus FROM employees WHERE id = emp_id;
DBMS_OUTPUT.PUT_LINE('Bonus: ' || emp_bonus);
END;

5. Function Example

CREATE OR REPLACE FUNCTION GetBonus (salary NUMBER) RETURN NUMBER IS


BEGIN
RETURN salary * 0.1;
END;

You might also like