Scribd
Upload
12 views4 pages

SQL PL SQL Solutions

The document provides a series of SQL and PL/SQL program solutions, including DDL and DML commands for managing an Employees table, examples of functions, operators, joins, grouping, indexing, subqueries, and views. It also contains PL/SQL examples for basic tasks such as displaying messages, adding numbers, checking for prime numbers, calculating factorials, and finding the position of a character in a string. Overall, it serves as a comprehensive guide to various SQL and PL/SQL programming techniques.

Uploaded by

devanshb377
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
12 views4 pages

SQL PL SQL Solutions

The document provides a series of SQL and PL/SQL program solutions, including DDL and DML commands for managing an Employees table, examples of functions, operators, joins, grouping, indexing, subqueries, and views. It also contains PL/SQL examples for basic tasks such as displaying messages, adding numbers, checking for prime numbers, calculating factorials, and finding the position of a character in a string. Overall, it serves as a comprehensive guide to various SQL and PL/SQL programming techniques.

Uploaded by

devanshb377
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 - Solutions

1. Program Solution:

-- DDL Commands

CREATE TABLE Employees (

ID INT PRIMARY KEY,

Name VARCHAR(50),

Salary INT

);

ALTER TABLE Employees ADD Department VARCHAR(50);

DROP TABLE Employees;

2. Program Solution:

-- DML Commands

INSERT INTO Employees (ID, Name, Salary, Department) VALUES (1, 'John', 50000, 'HR');

UPDATE Employees SET Salary = 55000 WHERE ID = 1;

DELETE FROM Employees WHERE ID = 1;

3. Program Solution:

-- Functions Example

SELECT ABS(-5), ROUND(123.456, 2), UPPER('hello'), TO_CHAR(SYSDATE, 'YYYY-MM-DD')

FROM dual;

4. Program Solution:

-- Operators

SELECT * FROM Employees WHERE Salary > 30000 AND Department = 'IT';
SELECT * FROM Employees WHERE Salary BETWEEN 30000 AND 60000;

SELECT * FROM Employees WHERE Department IN ('IT', 'HR');

5. Program Solution:

-- Joins

SELECT A.ID, A.Name, B.DepartmentName

FROM Employees A

INNER JOIN Departments B ON A.DepartmentID = B.ID;

SELECT A.ID, A.Name, B.DepartmentName

FROM Employees A

LEFT JOIN Departments B ON A.DepartmentID = B.ID;

6. Program Solution:

-- Grouping and Indexing

SELECT Department, COUNT(*) FROM Employees GROUP BY Department HAVING COUNT(*) > 1;

SELECT * FROM Employees ORDER BY Salary DESC;

CREATE INDEX idx_salary ON Employees(Salary);

7. Program Solution:

-- Subqueries and Views

SELECT Name FROM Employees WHERE DepartmentID = (SELECT ID FROM Departments WHERE

DepartmentName = 'IT');

CREATE VIEW IT_Employees AS SELECT * FROM Employees WHERE DepartmentID = 1;

8. Program Solution:

-- Hello World PL/SQL

BEGIN

DBMS_OUTPUT.PUT_LINE('Hello World');
END;

9. Program Solution:

-- Add Two Numbers

DECLARE

a NUMBER := 5;

b NUMBER := 10;

sum NUMBER;

BEGIN

sum := a + b;

DBMS_OUTPUT.PUT_LINE('Sum is: ' || sum);

END;

10. Program Solution:

-- Prime Numbers Check

DECLARE

n NUMBER := 7;

i NUMBER := 2;

flag BOOLEAN := TRUE;

BEGIN

WHILE i <= n/2 LOOP

IF MOD(n, i) = 0 THEN

flag := FALSE;

EXIT;

END IF;

i := i + 1;

END LOOP;
IF flag THEN

DBMS_OUTPUT.PUT_LINE(n || ' is a Prime Number');

ELSE

DBMS_OUTPUT.PUT_LINE(n || ' is not a Prime Number');

END IF;

END;

11. Program Solution:

-- Factorial Program

DECLARE

n NUMBER := 5;

fact NUMBER := 1;

BEGIN

FOR i IN 1..n LOOP

fact := fact * i;

END LOOP;

DBMS_OUTPUT.PUT_LINE('Factorial is: ' || fact);

END;

12. Program Solution:

-- First occurrence of 'a'

DECLARE

pos NUMBER;

BEGIN

SELECT INSTR(last_name, 'a') INTO pos FROM employees WHERE ROWNUM = 1;

DBMS_OUTPUT.PUT_LINE('Position of a: ' || pos);

END;

You might also like