SQL and PL/SQL Fundamentals
Aggregations
Aggregations in databases refer to operations that process multiple rows of data to produce a single summary value. Common aggregation functions include:
- SUM: Adds up all the values in a specified column.
- AVG: Calculates the average value of a specified column.
- COUNT: Counts the number of rows.
- MAX: Finds the maximum value in a specified column.
- MIN: Finds the minimum value in a specified column.
Difference Between UNION and UNION ALL
– **UNION**: Combines the results of two or more SELECT queries into a single result set and removes duplicate rows.
“`sql
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
“`
– **UNION ALL**: Combines the results of two or more SELECT queries into a single result set but includes all duplicate rows.
“`sql
SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;
“`
### What is a Subquery?
A subquery, also known as an inner query or nested query, is a query nested inside another query. Subqueries can be used in various SQL clauses, including `SELECT`, `FROM`, `WHERE`, and `HAVING`.
#### Example of a Subquery:
Let’s say you want to find employees who earn more than the average salary of all employees.
“`sql
SELECT employee_name
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
“`
### Example of JOIN with Sample Example
A JOIN clause is used to combine rows from two or more tables, based on a related column between them.
#### Example of an INNER JOIN:
Let’s assume we have two tables: `employees` and `departments`.
– **employees** table:
“`
employee_id | employee_name | department_id
————|—————-|—————
1 | Alice | 10
2 | Bob | 20
3 | Charlie | 10
“`
– **departments** table:
“`
department_id | department_name
————–|—————–
10 | HR
20 | Finance
“`
To get a list of employees and their corresponding department names, you can use an INNER JOIN:
“`sql
SELECT employees.employee_name, departments.department_name
FROM employees
INNER JOIN departments
ON employees.department_id = departments.department_id;
“`
#### Result:
“`
employee_name | department_name
————–|—————–
Alice | HR
Bob | Finance
Charlie | HR
“`
This query joins the `employees` table with the `departments` table on the `department_id` column, providing a combined result set.
### Q-3 Answer the following
#### (a) Justify: PL/SQL is more Advantageous than SQL
PL/SQL (Procedural Language/Structured Query Language) is an extension of SQL (Structured Query Language) used in Oracle databases. PL/SQL adds procedural capabilities to SQL, making it more powerful and flexible for certain tasks.
**Advantages of PL/SQL over SQL:**
1. **Procedural Capabilities**:
– PL/SQL allows for procedural constructs such as loops, conditionals, and exception handling, enabling more complex and sophisticated programming logic compared to SQL, which is purely declarative.
2. **Modularity**:
– PL/SQL supports the creation of reusable code blocks like procedures, functions, and packages. This modularity promotes code reuse, simplifies maintenance, and enhances organization.
3. **Performance Improvement**:
– PL/SQL can send an entire block of statements to the database in one go, reducing network traffic and improving performance. Stored procedures and functions also allow for pre-compilation and efficient execution.
4. **Error Handling**:
– PL/SQL provides robust error handling mechanisms through exception handling, which is not available in plain SQL. This allows developers to catch and manage runtime errors gracefully.
5. **Tight Integration with SQL**:
– PL/SQL tightly integrates with SQL, allowing for seamless execution of SQL queries within PL/SQL blocks. This makes it possible to manipulate data and manage database operations effectively within a single environment.
#### (b) Explain DML statement with appropriate Example.
DML (Data Manipulation Language) statements are used to manage data within database tables. Common DML statements include `INSERT`, `UPDATE`, `DELETE`, and `SELECT`.
**Examples**:
1. **INSERT**:
– Adds new records to a table.
“`sql
INSERT INTO employees (employee_id, employee_name, department_id, salary)
VALUES (1, ‘Alice’, 10, 50000);
“`
2. **UPDATE**:
– Modifies existing records in a table.
“`sql
UPDATE employees
SET salary = 55000
WHERE employee_id = 1;
“`
3. **DELETE**:
– Removes records from a table.
“`sql
DELETE FROM employees
WHERE employee_id = 1;
“`
4. **SELECT**:
– Retrieves data from one or more tables.
“`sql
SELECT employee_name, salary
FROM employees
WHERE department_id = 10;
“`
#### (c) Explain String functions with Example (any five).
String functions are used to manipulate and perform operations on string data types. Here are five common string functions:
1. **CONCAT**:
– Concatenates two or more strings into one.
“`sql
SELECT CONCAT(‘Hello’, ‘ ‘, ‘World’) AS greeting;
“`
– **Result**: `Hello World`
2. **SUBSTR** (or **SUBSTRING**):
– Extracts a substring from a string starting at a specified position.
“`sql
SELECT SUBSTR(‘Oracle Database’, 8, 8) AS extracted_string;
“`
– **Result**: `Database`
3. **LENGTH**:
– Returns the length of a string.
“`sql
SELECT LENGTH(‘Hello World’) AS string_length;
“`
– **Result**: `11`
4. **UPPER**:
– Converts all characters in a string to uppercase.
“`sql
SELECT UPPER(‘Hello World’) AS upper_case;
“`
– **Result**: `HELLO WORLD`
5. **LOWER**:
– Converts all characters in a string to lowercase.
“`sql
SELECT LOWER(‘Hello World’) AS lower_case;
“`
– **Result**: `hello world`
These functions are essential for data manipulation and transformation within SQL queries, allowing for effective and efficient handling of string data.
### Q.2 Answer the Following
#### 1. Create BookMaster table with different constraints
Creating a `BookMaster` table with various constraints can ensure data integrity and consistency. Below is an example of how to create this table with constraints such as primary key, foreign key, unique, not null, and check constraints.
“`sql
CREATE TABLE BookMaster (
BookID INT PRIMARY KEY,
Title VARCHAR(255) NOT NULL,
AuthorID INT NOT NULL,
PublisherID INT NOT NULL,
Price DECIMAL(10, 2) CHECK (Price > 0),
PublishedYear INT CHECK (PublishedYear >= 1900 AND PublishedYear ISBN VARCHAR(13) UNIQUE,
FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID),
FOREIGN KEY (PublisherID) REFERENCES Publishers(PublisherID)
);
“`
#### 2. DCL Statements
DCL (Data Control Language) statements are used to control access to data in a database. The primary DCL statements are `GRANT` and `REVOKE`.
– **GRANT**: Gives privileges to users.
“`sql
GRANT SELECT, INSERT ON BookMaster TO user1;
“`
– **REVOKE**: Removes privileges from users.
“`sql
REVOKE INSERT ON BookMaster FROM user1;
“`
#### 3. What are Index and Views? Explain with suitable examples
– **Index**: An index is a database object that improves the speed of data retrieval operations on a table at the cost of additional storage and slower write operations (insert, update, delete). Indexes can be created on one or more columns of a table.
**Example**:
“`sql
CREATE INDEX idx_title ON BookMaster (Title);
“`
– **View**: A view is a virtual table that is based on the result-set of an SQL query. It provides a way to present data in a particular format or to simplify complex queries.
**Example**:
“`sql
CREATE VIEW BooksWithPrice AS
SELECT Title, Price
FROM BookMaster
WHERE Price > 20;
“`
#### 4. What are Joins? Explain Types of Joins
Joins are used in SQL to combine rows from two or more tables based on a related column between them. The main types of joins are:
1. **INNER JOIN**: Returns records that have matching values in both tables.
“`sql
SELECT employees.employee_name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id;
“`
2. **LEFT JOIN (or LEFT OUTER JOIN)**: Returns all records from the left table, and the matched records from the right table. The result is NULL from the right side, if there is no match.
“`sql
SELECT employees.employee_name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.department_id;
“`
3. **RIGHT JOIN (or RIGHT OUTER JOIN)**: Returns all records from the right table, and the matched records from the left table. The result is NULL from the left side, when there is no match.
“`sql
SELECT employees.employee_name, departments.department_name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.department_id;
“`
4. **FULL JOIN (or FULL OUTER JOIN)**: Returns all records when there is a match in either left or right table. Records that do not match in either table will have NULLs.
“`sql
SELECT employees.employee_name, departments.department_name
FROM employees
FULL OUTER JOIN departments ON employees.department_id = departments.department_id;
“`
5. **CROSS JOIN**: Returns the Cartesian product of both tables.
“`sql
SELECT employees.employee_name, departments.department_name
FROM employees
CROSS JOIN departments;
“`
#### 5. Explain NVL function with suitable example
The `NVL` function in SQL is used to replace `NULL` values with a specified replacement value. This is useful for handling `NULL` values in a database.
**Example**:
Assume you have a table `Sales` with columns `SaleID`, `ProductID`, and `Commission`. If the `Commission` column can have `NULL` values and you want to replace `NULL` with 0 in your query results, you can use `NVL`.
“`sql
SELECT SaleID, ProductID, NVL(Commission, 0) AS Commission
FROM Sales;
“`
In this query, if `Commission` is `NULL`, the `NVL` function will replace it with 0.
### Q.3 Attempt
#### 1. Write a PL/SQL block to print the Minimum of three numbers.
Here’s a PL/SQL block to find and print the minimum of three numbers:
“`sql
DECLARE
num1 NUMBER := 15;
num2 NUMBER := 10;
num3 NUMBER := 20;
min_num NUMBER;
BEGIN
IF num1
min_num := num1;
ELSIF num2
min_num := num2;
ELSE
min_num := num3;
END IF;
DBMS_OUTPUT.PUT_LINE(‘The minimum number is: ‘ || min_num);
END;
“`
In this block, three numbers (`num1`, `num2`, `num3`) are compared using conditional statements to determine the smallest number, which is then printed using `DBMS_OUTPUT.PUT_LINE`.
#### 2. Explain the procedure with example.
A procedure in PL/SQL is a subprogram that performs a specific action. It can take parameters and can be stored in the database for reuse. Procedures are created with the `CREATE PROCEDURE` statement.
**Example**:
Here’s an example of creating and using a procedure:
1. **Creating a Procedure**:
“`sql
CREATE OR REPLACE PROCEDURE UpdateSalary (
emp_id IN NUMBER,
new_salary IN NUMBER
) IS
BEGIN
UPDATE employees
SET salary = new_salary
WHERE employee_id = emp_id;
COMMIT;
END;
“`
2. **Calling the Procedure**:
“`sql
BEGIN
UpdateSalary(1, 60000);
END;
“`
**Explanation**:
– The `UpdateSalary` procedure takes two parameters: `emp_id` (employee ID) and `new_salary` (new salary amount).
– Inside the procedure, an `UPDATE` statement modifies the salary of the specified employee.
– The `COMMIT` statement makes the change permanent.
Procedures help encapsulate business logic, making code more modular, reusable, and easier to maintain.
#### 3. Explain the advantages of PL/SQL.
PL/SQL offers several advantages over standard SQL, making it a powerful language for database programming. Here are some key advantages:
1. **Procedural Language**:
– PL/SQL adds procedural constructs to SQL, allowing for the use of loops, conditions, and exception handling. This makes it possible to write complex and sophisticated business logic directly within the database.
2. **Modularity**:
– PL/SQL supports the creation of modular code using procedures, functions, and packages. This modularity promotes code reuse and makes maintenance easier.
3. **Performance**:
– PL/SQL allows for the execution of blocks of SQL statements in a single call to the database, reducing network traffic and improving performance. Stored procedures and functions can be precompiled for faster execution.
4. **Error Handling**:
– PL/SQL provides robust error handling through exceptions. This allows developers to catch and handle errors gracefully, ensuring better control over runtime conditions.
5. **Integration with SQL**:
– PL/SQL is tightly integrated with SQL, allowing for seamless execution of SQL queries within PL/SQL code. This enables efficient data manipulation and database operations.
6. **Security**:
– PL/SQL procedures and functions can be used to enforce security policies. By encapsulating business logic in PL/SQL, direct access to data can be restricted, ensuring that only authorized operations are performed.
7. **Portability**:
– PL/SQL code can be ported across different Oracle databases with minimal changes, providing flexibility and ease of deployment.
These advantages make PL/SQL a preferred choice for developing robust, efficient, and secure database applications.