DBMS Unit-IV

 


1) Explain about Oracle and write about tools in Oracle? 4M

a) Oracle

Oracle is an object relational database system (ORDBMS) that allows us to store the data in the form of tables in a database. It supports various commands.

Oracle contains various tools for database, they are:

  1. SQL
  2. PL/SQL

SQL:

SQL stands for Structured Query Language. It allows to store, retrieve, edit, execute and manage data using SQL. We can perform calculations, column modifications and other types of operations and format the query result in the form of reports.

PL/SQL:

PL/SQL stands for Procedural Language/SQL. PL/SQL blocks contain SQL statements integrated with control statements (block of statements).

2) Write about Aggregate Functions in SQL.? 10M

These functions are applied to a group of records. For example, the functions are represented as follows:

  1. AVG()
  2. MAX()
  3. MIN()
  4. SUM()
  5. COUNT()

AVG():

It is used to display the average value for the specified column.

SELECT AVG(tot) FROM student;

Example:

AVG(tot)
50000

MAX():

It is used to display the maximum value for the specified column.

SELECT MAX(tot) FROM student;

Example:

MAX(tot)
150

MIN():

It is used to display the minimum value for the specified column.

SELECT MIN(tot) FROM student;

Example:

MIN(tot)
125

SUM():

It is used to display the sum of the specified column.

SELECT SUM(tot) FROM student;

Example:

SUM(tot)
4789

COUNT():

It is used to count the number of rows from a given table.

SELECT COUNT(tot) FROM student;

Example:

COUNT(tot)
4789

3) Write about JOIN in SQL? 4M

  • The purpose of joins is used to combine two or more tables.
  • The join is actually performed by WHERE condition.
  • It is used to display the columns from two or more tables.
  • There are different types of joins in SQL. For example, they are:
    • Equi Join
    • Non-Equi Join
    • Self Join
    • Outer Join

Equi Join:

A join which is based on equality (=) is called Equi Join.

SELECT * FROM emp, dept
WHERE emp.deptno = dept.deptno;

Non-Equi Join:

A join which is based on an inequality such as <, >, <=, >= is called a Non-Equi Join.

SELECT * FROM emp, dept
WHERE emp.deptno != dept.deptno;

Self Join:

Joining a table itself is called a Self Join.

SELECT * FROM emp emp1, emp emp2
WHERE e1.deptno = e2.deptno;

Outer Join:

Outer join returns all the rows returned by the simple join from one table and does not match any row from the other table.

SELECT * FROM emp, dept
WHERE emp.deptno = dept.deptno(+);

4) Write about SQL Commands (or) Write about DDL, DML, TCL Commands?                                                                                                                                 10M

SQL:

SQL stands for Structured Query Language. It contains a collection of commands. The commands are divided into 3 types:

  1. Data Definition Language (DDL)
  2. Data Manipulation Language (DML)
  3. Transaction Control Language (TCL)

Data Definition Language (DDL) Commands:

These commands are used to create the table, alter the table and truncate the table.

Create the Table:

The functionality of the CREATE command is used to create a new table.

Syntax:

CREATE TABLE tablename (field description);

Example:

CREATE TABLE student (
    sno NUMBER,
    sname CHAR(20),
    m1 NUMBER,
    m2 NUMBER
);

Alter the Table:

The functionality of the ALTER command is used to change the existing table structure, like adding columns or deleting columns or modifying the column.

Syntax:

ALTER TABLE tablename ADD field description;

Example:

ALTER TABLE student ADD total NUMBER;

Truncate the Table:

The functionality of the TRUNCATE command is used to delete the data permanently and where all data is represented as follows:

TRUNCATE TABLE tablename;

Example:

TRUNCATE TABLE student;

Drop the Table:

The functionality of the DROP command is used to delete the table permanently.

Syntax:

DROP TABLE tablename;

Example:

DROP TABLE student;

Data Manipulation Language (DML):

These commands are used to insert the data, update the data, and delete the data.

Insert the Data

The functionality of the INSERT command is used to insert a new row into the table.

Method – I

INSERT INTO tablename (fields) VALUES (data);

Example:

INSERT INTO student VALUES (1, 'aaa', 60, 60, 60);

Method – II

INSERT INTO tablename (fields)
VALUES (sno, sname, m1, m2, m3);

Example:

Enter sno: 2
Enter sname: 'bbb'
Enter m1: 70
Enter m2: 70
Enter m3: 70

Data inserted

Update the Data:

The functionality of the UPDATE command is used to update the data and also perform calculations.

Syntax:

UPDATE tablename SET field = value/expression;

Example:

UPDATE student SET total = m1 + m2 + m3;

Delete the Data

The functionality of DELETE is used to delete the rows from the database.

Syntax:

DELETE FROM tablename;

Example:

DELETE FROM student;

Select the Data

The functionality of the SELECT command is used to display the data from the database.

Syntax:

SELECT */columns FROM tablename;

Example:

SELECT * FROM student;
SELECT sno, sname, total FROM student;

Transaction Control Language (TCL):

These commands are used to apply the transactions, either permanently or temporarily. It contains COMMIT, ROLLBACK and SAVEPOINT commands.

COMMIT:

The functionality of the COMMIT command is used to apply the transactions permanently.

COMMIT;

ROLLBACK:

The functionality of the ROLLBACK command is used to undo the transactions.

ROLLBACK;

SAVEPOINT:

The functionality of the SAVEPOINT command is used to apply the transaction either permanently or temporarily up to the user's point.

Example:

EX-1                         EX-2
SAVEPOINT A;                 SAVEPOINT B;
COMMIT TO A;                 COMMIT TO B;

5) Write about Views in SQL (or) How to create a Virtual Table in SQL? 4M

  • A view is an object in SQL.
  • It is also called a virtual table.
  • It is used to create columns from one or more tables.
  • It can also contain aggregate functions.
  • Views are automatically updated.
  • It can be created by using the CREATE VIEW command.

Syntax:

CREATE VIEW viewname AS SELECT query;

Example:

CREATE VIEW V1 AS
SELECT MAX(sal), MIN(sal)
FROM emp;

In this example, the view contains two columns. It can be displayed with the help of the SELECT command.

SELECT columns FROM viewname;

It displays maximum salary and minimum salary.

MAX(sal)       MIN(sal)
100000         20000

The DROP command is used to delete the view from the database.

DROP VIEW viewname;

Example:

DROP VIEW V1;

6) Write about Query, Subquery and Correlated Query? 4M

a) Query

A query is a SQL statement. It is used to access the data from the database.

A SELECT command contains another SELECT command, which is called a sub-query.

It is also called a nested query.

It can be used to retrieve the data from the table.

Example:

SELECT * FROM emp
WHERE deptno = (SELECT deptno FROM dept);

b) Subquery

A subquery is performed only once, whereas a correlated subquery is performed for every row processed by the parent statement.

It is represented as follows:

SELECT * FROM emp
WHERE 3 > (
    SELECT COUNT(sal)
    FROM emp
    WHERE e.sal > sal
);

The above example displays the data for the highest salary or top three employees.

7) Write about Mathematical or Numerical Functions in SQL? 10M

The numerical functions are used to apply or operate on numeric values. It can produce a numeric result.

For example, they are:

  • SQRT()
  • POWER()
  • ABS()
  • TRUNC()
  • ROUND()

SQRT():

The functionality of SQRT() is used to display the square root of a given number.

SELECT SQRT(25) FROM dual;
5

POWER():

The POWER() function is used to display the power value.

SELECT POWER(2,3) FROM dual;
8

ABS():

The functionality of ABS() is used to display the absolute value of a given number. It converts a negative number into a positive number.

SELECT ABS(-3) FROM dual;
3

TRUNC():

The functionality of TRUNC() is used to truncate the given number up to the required position.

SELECT TRUNC(63.789,2) FROM dual;
63.78

ROUND():

The functionality of ROUND() is used to round the given number up to the required position.

SELECT ROUND(63.789,2) FROM dual;
63.79

8) Write about Character Functions (or) String Functions in SQL? 10M

The text functions are used to operate on characters as well as strings.

For example:

  • LOWER()
  • UPPER()
  • LENGTH()
  • LEFT()
  • RIGHT()

LOWER():

The functionality of the LOWER() function is used to display the text as lowercase.

SELECT LOWER('ADITYA') FROM dual;
aditya

UPPER():

The functionality of the UPPER() function is used to display the text as uppercase.

SELECT UPPER('aditya') FROM dual;
ADITYA

LENGTH():

The functionality of the LENGTH() function is used to display the length of the string.

SELECT LENGTH('aditya') FROM dual;
6

LEFT():

The functionality of the LEFT() function is used to display the left-most n characters.

SELECT LEFT('aditya',3) FROM dual;
adi

RIGHT():

The functionality of the RIGHT() function is used to display the right-most n characters.

SELECT RIGHT('aditya',3) FROM dual;
tya

9) Explain about Set Operators in SQL? 10M

  • Set operators are one of the features of SQL.
  • Set operators are commonly used to combine the data from two or more tables.
  • A query containing set operators is called a compound query.
  • The result set of all queries must have the same number of columns.

There are different types of set operators. For example, they are:

  1. UNION
  2. UNION ALL
  3. INTERSECT
  4. MINUS

Set Operators in SQL

Example Tables

Table EMP1

enoename
1aaa
2bbb
3ccc

Table EMP2

enoename
3ccc
4ddd
5eee

1) UNION

The functionality of UNION set operator is used to combine two or more tables without duplicate rows.

Example:

SELECT * FROM emp1
UNION
SELECT * FROM emp2;

It can display the following rows as a result:

enoename
1aaa
2bbb
3ccc
4ddd
5eee

2) UNION ALL

The functionality of UNION ALL is to combine the result of two tables using the SELECT command.

  • Duplicate rows will not be eliminated.
  • The rows are retained in the result.

Example:

SELECT * FROM emp1
UNION ALL
SELECT * FROM emp2;

It can display the following rows:

enoename
1aaa
2bbb
3ccc
3ccc
4ddd
5eee

3) INTERSECT

The functionality of INTERSECT set operator is used to combine two or more tables using the SELECT command.

  • It displays the rows which are common from both tables.

Example:

SELECT * FROM emp1
INTERSECT
SELECT * FROM emp2;

Result:

enoename
3ccc

4) MINUS

The functionality of MINUS set operator is used to display the rows which are present in the first query but not in the second query.

  • It does not allow duplicate rows.

Example:

SELECT * FROM emp1
MINUS
SELECT * FROM emp2;

Result:

enoename
1aaa
2bbb

10) Write about Selection and Projection in SQL?4M

SelectionProjection
Selection operation is based on tuples (rows).Projection operation is based on attributes (columns).
It is denoted by Sigma (σ).It is denoted by Pi (π).
It is also known as horizontal partition.It is also known as vertical partition.
It is performed before projection.It is performed after selection.
Select operator is used in selection operation.Projection operator is used in projection operation.
Select is used to select all columns.Project is used to select specific columns.
It is represented by σ(R).It is represented by π(R).
In this representation, we use a select condition where filtering is performed.In this representation, we can use select columns.

11) Write about Table Manipulation in SQL? 10M

SQL stands for Structured Query Language. It contains three types of commands:

1. Data Definition Language (DDL)

  • CREATE
  • ALTER
  • TRUNCATE
  • DROP

2. Data Manipulation Language (DML)

  • INSERT
  • UPDATE
  • DELETE

3. Data Transaction Control Language (TCL)

  • COMMIT
  • ROLLBACK

Data Definition Language (DDL):

ALTER Command

It is used to change the existing table structure. It contains various properties like ADD, MODIFY & DROP.

ALTER TABLE – ADD

It is used to add the required columns into an existing table.

ALTER TABLE tablename
ADD columnname datatype;

Example:

ALTER TABLE student
ADD total NUMBER;

ALTER TABLE – MODIFY

It is used to modify the columns in an existing table.

ALTER TABLE tablename
MODIFY columnname description;

Example:

ALTER TABLE student
MODIFY studentname VARCHAR2(30);

ALTER TABLE – DROP

It is used to delete unnecessary columns from an existing table.

ALTER TABLE tablename
DROP COLUMN columnname;

Example:

ALTER TABLE student
DROP COLUMN total;

No comments:

Post a Comment

Subscribe to: Posts (Atom)

DBMS Unit V

  (PL/SQL) PROCEDURAL LANGUAGE / STRUCTURED QUERY LANGUAGE 1) Explain about the structure of PL/SQL program. How to create and execute a ...