Structure Data and Unstructured data

Feature Structured Data Unstructured Data

Definition Data that is organized and stored in a Data that does not follow a predefined
predefined format, typically in tables with rows format or structure.
and columns.
Technology Uses SQL-based RDBMS (MySQL, PostgreSQL, Uses NoSQL databases (MongoDB,
Oracle, SQL Server). Cassandra, Hadoop, ElasticSearch).
Flexibility Less flexible; predefined schema required Highly flexible; schema can evolve
before inserting data. dynamically.
Scalability Scales vertically (adding more power to a single Scales horizontally (distributing across
server). multiple servers).
Robustness Strong consistency, ACID compliance ensures Eventual consistency; designed for high
data integrity. availability and big data processing.
Query Fast for structured queries using SQL. Requires specialized tools (NLP, AI,
Performance ML) for querying and analysis.
Data Simple CRUD operations (Create, Read, Requires advanced data mining, AI,
Processing Update, Delete) using SQL. and machine learning for processing.
Storage Cost Can be expensive due to strict data structure Cost-efficient for massive data storage
and indexing. (Hadoop, S3, data lakes).
Use Cases Financial records, CRM databases, Employee Emails, Social media posts, Images,
records. Videos, IoT data, Logs.
Examples Sales database, Employee records, Inventory Social media posts, Medical images,
data. Sensor data, Customer reviews.

Data, Information & Knowledge

Data
Data refers to raw facts that have no meaning by themselves.
It can be numbers, text, symbols, images, etc.
Example:
"19", "Rajeev", "24" → These are just numbers and words without context.
Information
When data is processed and given meaning, it becomes information.
It answers who, what, when, where questions.
Example: if we organize the data into a table format, it becomes useful:
id lastname firstname age
19 Rai Rajeev 24

"Rajeev Rai is 24 years old", making it useful information.

Knowledge
When information is analyzed and interpreted, it becomes knowledge.
It helps in decision-making.
Example:
If a company sees that most employees aged 24 have similar skills, they may decide to create a special training program for them.
This is knowledge derived from information.
Relation (Table)
In Relational Database Management Systems (RDBMS), a relation is a table that stores data in rows and columns.
Example:
id lastname firstname age
19 Rai Rajeev 24
20 Roy Sayan 24
21 Roy Sayan 29

The employee table above is a relation because it stores structured data.

Tuple (Row), Column (Attribute)
A tuple is a single row in a table, representing a record.
A column (or attribute) is a single field in a table that holds a specific type of data.
Example (Row):
The first row in the employee table is a tuple:
19 Rai Rajeev 24

Example (Column):
The lastname column stores employee last names
Each column stores a specific type of data for all rows.
lastname
Rai
Roy
Roy
A database-management system (DBMS) is a collection of interrelated data and a set of programs to access those data. The collection of data, usually
referred to as the database, contains information relevant to an enterprise. The primary goal of a DBMS is to provide a way to store and retrieve
database information that is both convenient and efficient. (**you can also refer definition from notebook)
Database System Applications
Databases are widely used. Here are some representative applications:
• Banking: For customer information, accounts, and loans, and banking transactions.
• Airlines: For reservations and schedule information. Airlines were among the first to use databases in a geographically distributed manner—
terminals situated around the world accessed the central database system through phone lines and other data networks.
• Universities: For student information, course registrations, and grades.
• Credit card transactions: For purchases on credit cards and generation of monthly statements.
• Telecommunication: For keeping records of calls made, generating monthly bills, maintaining balances on prepaid calling cards, and storing
information about the communication networks.
• Finance: For storing information about holdings, sales, and purchases of financial instruments such as stocks and bonds.
• Sales: For customer, product, and purchase information.
• Manufacturing: For management of supply chain and for tracking production of items in factories, inventories of items in warehouses/stores, and
orders for items.
• Human resources: For information about employees, salaries, payroll taxes and benefits, and for generation of pay checks.

View of Data
A database system is a collection of interrelated files and a set of programs that allow users to access and modify these files. A major purpose of a
database system is to provide users with an abstract view of the data. That is, the system hides certain details of how the data are stored and
maintained.

Data Abstraction
For the system to be usable, it must retrieve data efficiently. The need for efficiency has led designers to use complex data structures to represent data
in the database. Since many database-systems users are not computer trained, developers hide the complexity from users through several levels of
abstraction, to simplify users’ interactions with the system:
• Physical level. The lowest level of abstraction describes how the data are actually stored. The physical level describes complex low-level data
structures in detail.
• Logical level. The next-higher level of abstraction describes what data are stored in the database, and what relationships exist among those data.
The logical level thus describes the entire database in terms of a small number
of relatively simple structures. Although implementation of the simple structures at the logical level may involve complex physical-level structures,
the user of the logical level does not need to be aware of this complexity. Database
administrators, who must decide what information to keep in the database, use the logical level of abstraction.
View level. The highest level of abstraction describes only part of the entire database. Even though the logical level uses simpler structures, complexity
remains because of the variety of information stored in a large database. Many users of the database system do not need all this information; instead,
they need to access only a part of the database. The view level of abstraction exists to simplify their interaction with the system. The system may
provide many views for the same database.

Instances and Schemas

Databases change over time as information is inserted and deleted. The collection of information stored in the database at a particular moment is
called an instance of the database. The overall design of the database is called the database schema. Schemas are changed infrequently, if at all. The
concept of database schemas and instances can be understood by analogy to a program written in a programming language. A database schema
corresponds to the variable declarations (along with associated type definitions) in a program. Each variable has a particular value at a given instant.
The values of the variables in a program at a point in time correspond to an instance of a database schema.
Database systems have several schemas, partitioned according to the levels of abstraction. The physical schema describes the database design at the
physical level, while the logical schema describes the database design at the logical level. A database may also have several schemas at the view level,
sometimes called subschemas, that describe different views of the database.
Schema of an employee table

Instance of Customer relation:

customer_id cust_name city grade salesman_id
3002 Nick Rimando New York 100 5001
3007 Brad Davis New York 200 5001
 3005 Graham Zusi California 200 5002

Instance of Order relation:

Ord_no purch_amt ord_date customer_id salesman_id
70001 150.5 5-Oct-2012 3005 5002
70009 270.65 10-Sep-2012 3001 5005
70002 65.26 5-Oct-2012 3002 5001

The Entity-Relationship Model

The entity-relationship (E-R) data model is based on a perception of a real world that consists of a collection of basic objects, called entities, and of
relationships among these objects. An entity is a “thing” or “object” in the real world that is distinguishable from other objects. For example, each
person is an entity, and bank accounts can be considered as entities.
Entities are described in a database by a set of attributes. For example, the attributes account-number and balance may describe one particular account
in a bank, and they form attributes of the account entity set. Similarly, attributes customer-name, customer-street address and customer-city may describe
a customer entity.
A relationship is an association among several entities. For example, a depositor relationship associates a customer with each account that she has. The
set of all entities of the same type and the set of all relationships of the same type are termed an entity set and relationship set, respectively.
Key
A key allows us to identify a set of attributes that suffice to distinguish entities from each other. Keys also help uniquely identify relationships, and
thus distinguish relationships from each other.

An attribute, as used in the E-R model, can be characterized by the following attribute types:
Simple and composite attributes. In our examples thus far, the attributes have been simple; that is, they are not divided into subparts. Composite
attributes, on the other hand, can be divided into subparts (that is, other attributes). For example, an attribute name could be structured as a composite
attribute consisting of first-name, middle-initial, and last-name.
Single-valued and multivalued attributes. The attributes in our examples all have a single value for a particular entity. For instance, the loan-number
attribute for a specific loan entity refers to only one loan number. Such attributes are said to be single valued. There may be instances where an
attribute has a set of values for a specific entity. Consider an employee entity set with the attribute phone-number.
Derived attribute. The value for this type of attribute can be derived from the values of other related attributes or entities. For instance, if the customer
entity set also has an attribute date-of-birth, we can calculate age from date-of-birth and the current date.

Customer entity with different types of attribute:

Ternary Relationship
When three entities are connected to one relationship, that relationship is known as ternary relationship. A n-ary relationship set shows more clearly
that several entities participate in a single relationship.

Weak Entity Sets

An entity set may not have sufficient attributes to form a primary key. Such an entity set is termed a weak entity set. An entity set that has a primary
key is termed a strong entity set. As an illustration, consider the entity set payment, which has the three attributes: payment-number, payment-date, and
payment-amount. Payment numbers are typically sequential numbers, starting from 1, generated separately for each loan. Thus, although each payment
entity is distinct, payments for different loans may share the same payment number. Thus, this entity set does not have a primary key; it is a weak
entity set.
For a weak entity set to be meaningful, it must be associated with another entity set, called the identifying or owner entity set. Every weak entity
must be associated with an identifying entity; that is, the weak entity set is said to be existence dependent on the identifying entity set. The identifying
entity set is said to own the weak entity set that it identifies. The relationship associating the weak entity set with the identifying entity set is called
the identifying relationship.

Specialization
The refinement from an initial entity set into successive levels of entity subgroupings represents a top-down design process in which distinctions are
made explicit. Consider an entity set person, with attributes name, street, and city.
The process of designating subgroupings within an entity set is called specialization.
As another example, suppose the bank wishes to divide accounts into two categories, checking account and savings account. Savings accounts need
a minimum balance, but the bank may set interest rates differently for different customers, offering better rates to favoured customers. Checking
accounts have a fixed interest rate, but offer an overdraft facility; the overdraft amount on a checking account must be recorded. The bank could then
create two specializations of account, namely savings-account and checking-account.

Generalization
The design process may also proceed in a bottom-up manner, in which multiple entity sets are synthesized into a higher-level entity set on the basis
of common features. The database designer may have first identified a customer entity set with the attributes name, street, city, and customer-id, and an
employee entity set with the attributes name, street, city, employee-id, and salary. There are similarities between the customer entity set and the employee
entity set in the sense that they have several attributes in common. This commonality can be expressed by generalization, which is a containment
relationship that exists between a higher-level entity set and one or more lower-level entity sets.

Constraints on Generalizations
The lower-level entity sets may be one of the following:
• Disjoint. A disjointness constraint requires that an entity belong to no more than one lower-level entity set. In our example, an account entity can
satisfy only one condition for the account-type attribute; an entity can be either a savings
account or a checking account, but cannot be both.
• Overlapping. In overlapping generalizations, the same entity may belong to more than one lower-level entity set within a single generalization. For
an illustration, consider the employee work team example, and assume that certain
managers participate in more than one work team. A given employee may therefore appear in more than one of the team entity sets that are lower-
level entity sets of employee. Thus, the generalization is overlapping.

Cardinality:
Mapping cardinalities, or cardinality ratios, express the number of entities to which another entity can be associated via a relationship set. Mapping
cardinalities are most useful in describing binary relationship sets, although they can contribute to the description of relationship sets that involve
more than two entity sets. In this section, we shall concentrate on only binary relationship sets.
For a binary relationship set R between entity sets A and B, the mapping cardinality must be one of the following:
• One to one. An entity in A is associated with at most one entity in B, and an entity in B is associated with at most one entity in A. (See Figure 2.4a.)
• One to many. An entity in A is associated with any number (zero or more) of entities in B. An entity in B, however, can be associated with at most
one entity in A. (See Figure 2.4b.)
• Many to one. An entity in A is associated with at most one entity in B. An entity in B, however, can be associated with any number (zero or more)
of entities in A. (See Figure 2.5a.)
• Many to many. An entity in A is associated with any number (zero or more) of entities in B, and an entity in B is associated with any number (zero
or more) of entities in A. (See Figure 2.5b.)

Database System Structure:

Database Users and User Interfaces
There are four different types of database-system users, differentiated by the way they expect to interact with the system. Different types of user
interfaces have been designed for the different types of users.
• Naive users are unsophisticated users who interact with the system by invoking one of the application programs that have been written
previously. For example, a bank teller who needs to transfer $50 from account A to account B invokes a program called transfer.
• Application programmers are computer professionals who write application programs. Application programmers can choose from many
tools to develop user interfaces.
• Sophisticated users interact with the system without writing programs. Instead, they form their requests in a database query language. They
submit each such query to a query processor, whose function is to break down DML statements into instructions that the storage manager
understands. Analysts who submit queries to explore data in the database fall in this category
• Specialized users are sophisticated users who write specialized database applications that do not fit into the traditional data-processing
framework. Among these applications are computer-aided design systems, knowledge base and expert systems, systems that store data with
complex data types (for example, graphics data and audio data), and environment-modelling systems.

Database Administrator
One of the main reasons for using DBMSs is to have central control of both the data and the programs that access those data. A person who has such
central control over the system is called a database administrator (DBA). The functions of a DBA include:
• Schema definition. The DBA creates the original database schema by executing a set of data definition statements in the DDL.
• Storage structure and access-method definition.
• Schema and physical-organization modification. The DBA carries out changes to the schema and physical organization to reflect the changing
needs of the organization, or to alter the physical organization to improve performance.
• Granting of authorization for data access. By granting different types of authorization, the database administrator can regulate which parts of the
database various users can access. The authorization information is kept in a special system structure that the database system consults whenever
someone attempts to access the data in the system.
• Routine maintenance. Examples of the database administrator’s routine maintenance activities are:
􀀀 Periodically backing up the database, either onto tapes or onto remote servers, to prevent loss of data in case of disasters such as flooding.
􀀀 Ensuring that enough free disk space is available for normal operations, and upgrading disk space as required.
􀀀 Monitoring jobs running on the database and ensuring that performance is not degraded by very expensive tasks submitted by some users.
Storage Manager
A storage manager is a program module that provides the interface between the low level data stored in the database and the application programs
and queries submitted to the system. The storage manager is responsible for the interaction with the file manager. The raw data are stored on the disk
using the file system, which is usually provided by a conventional operating system. The storage manager translates the various DML statements
into low-level file-system commands. Thus, the storage manager is responsible for storing, retrieving, and updating data in the database.
The storage manager components include:
• Authorization and integrity manager, which tests for the satisfaction of integrity constraints and checks the authority of users to access data.
• Transaction manager, which ensures that the database remains in a consistent (correct) state despite system failures, and that concurrent transaction
executions proceed without conflicting.
• File manager, which manages the allocation of space on disk storage and the data structures used to represent information stored on disk.
• Buffer manager, which is responsible for fetching data from disk storage into main memory, and deciding what data to cache in main memory. The
buffer manager is a critical part of the database system, since it enables the database
to handle data sizes that are much larger than the size of main memory.

The storage manager implements several data structures as part of the physical system implementation:
• Data files, which store the database itself.
• Data dictionary, which stores metadata about the structure of the database, in particular the schema of the database.
• Indices, which provide fast access to data items that hold particular values.

The Query Processor

The query processor components include:
• DDL interpreter, which interprets DDL statements and records the definitions in the data dictionary.
• DML compiler, which translates DML statements in a query language into an evaluation plan consisting of low-level instructions that the query
evaluation engine understands. A query can usually be translated into any of a number of alternative evaluation plans that all give the same result.
The DML compiler also performs query optimization, that is, it picks the lowest cost evaluation plan from among the alternatives.
• Query evaluation engine, which executes low-level instructions generated by the DML compiler.

Desirable Properties of Transactions

Transactions should possess several properties, often called the ACID properties; they should be enforced by the concurrency control and recovery
methods of the DBMS. The following are the ACID properties:
■ Atomicity. A transaction is an atomic unit of processing; it should either be performed in its entirety or not performed at all.
■ Consistency preservation. A transaction should be consistency preserving, meaning that if it is completely executed from beginning to end without
interference from other transactions, it should take the database from one consistent state to another.
■ Isolation. A transaction should appear as though it is being executed in isolation from other transactions, even though many transactions are
executing concurrently. That is, the execution of a transaction should not be interfered with by any other transactions executing concurrently.
■ Durability or permanency. The changes applied to the database by a committed transaction must persist in the database. These changes must not
be lost because of any failure.
Conversion of EER diagram to relational database (RDBMS or SQL queries)

Customer Table
customer_id (PK) customer_name customer_street customer_city

Branch Table
branch_name (PK) branch_city assets

Loan Table
loan_number (PK) branch_name (FK) amount

Account Table
account_number (PK) branch_name (FK) balance

Employee Table
employee_id (PK) employee_name telephone_number start_date

Cust_Banker Table (Relationship between Employee and Customer)

customer_id (PK, FK) employee_id (PK, FK) type

Depositor Table
customer_id (PK, FK) account_number (PK, FK) access_date

Employee_Dependents Table (For Multivalued Attribute)

employee_id (PK, FK) dependent_name (PK)
Loan Payment Table
payment_number (PK) loan_number (FK) payment_date payment_amount

ISA Relationship (Account Specialization)

Savings_Account Table
account_number (PK, FK) interest_rate

Checking_Account Table
account_number (PK, FK) overdraft_amount

Customer Table
CREATE TABLE Customer (
customer_id INT PRIMARY KEY,
customer_name VARCHAR(50),
customer_street VARCHAR(100),
customer_city VARCHAR(50)
);
Branch Table
CREATE TABLE Branch (
branch_name VARCHAR(50) PRIMARY KEY,
branch_city VARCHAR(50),
assets DECIMAL(15,2)
);

Loan Table
CREATE TABLE Loan (
loan_number INT PRIMARY KEY,
branch_name VARCHAR(50),
amount DECIMAL(15,2),
FOREIGN KEY (branch_name) REFERENCES Branch(branch_name)
);

Account Table
CREATE TABLE Account (
account_number INT PRIMARY KEY,
branch_name VARCHAR(50),
balance DECIMAL(15,2),
FOREIGN KEY (branch_name) REFERENCES Branch(branch_name)
);

Employee Table
CREATE TABLE Employee (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(50),
telephone_number VARCHAR(20),
start_date DATE
);

CREATE TABLE Cust_Banker (

customer_id INT,
employee_id INT,
type VARCHAR(50),
PRIMARY KEY (customer_id, employee_id),
FOREIGN KEY (customer_id) REFERENCES Customer(customer_id),
FOREIGN KEY (employee_id) REFERENCES Employee(employee_id)
);

Depositor Table
CREATE TABLE Depositor (
customer_id INT,
account_number INT,
access_date DATE,
PRIMARY KEY (customer_id, account_number),
FOREIGN KEY (customer_id) REFERENCES Customer(customer_id),
FOREIGN KEY (account_number) REFERENCES Account(account_number)
);

CREATE TABLE Employee_Dependents (

employee_id INT,
dependent_name VARCHAR(50),
PRIMARY KEY (employee_id, dependent_name),
FOREIGN KEY (employee_id) REFERENCES Employee(employee_id)
);

Loan Payment Table

CREATE TABLE Loan_Payment (
payment_number INT PRIMARY KEY,
loan_number INT,
payment_date DATE,
payment_amount DECIMAL(15,2),
FOREIGN KEY (loan_number) REFERENCES Loan(loan_number)
);

ISA Relationship (Account Specialization)

CREATE TABLE Savings_Account (
account_number INT PRIMARY KEY,
interest_rate DECIMAL(5,2),
FOREIGN KEY (account_number) REFERENCES Account(account_number)
);
CREATE TABLE Checking_Account (
account_number INT PRIMARY KEY,
overdraft_amount DECIMAL(15,2),
 FOREIGN KEY (account_number) REFERENCES Account(account_number)
);
Data Definition Language (DDL): CREATE, ALTER, DROP, RENAME, and TRUNCATE.

CREATE TABLE employees

( employee_id INT PRIMARY KEY,
last_name VARCHAR(50) NOT NULL,
first_name VARCHAR(50),
salary INT
);

ALTER TABLE employees add middle_name VARCHAR(50);

ALTER TABLE employees modify last_name VARCHAR(75);

ALTER TABLE employees DROP COLUMN middle_name;

ALTER TABLE EMPLOYEES RENAME COLUMN LAST_NAME TO lname;

RENAME EMPLOYEES TO EMPLOYEE;

RENAME EMPLOYEE TO EMPLOYEES1;

truncate table EMPLOYEES1;

select * from EMPLOYEES1;

OUTPUT:
no data found

drop table EMPLOYEES1;

Constraints
1. A PRIMARY KEY uniquely identifies each record (row) in a table.
It cannot have duplicate or NULL values.
2. A FOREIGN KEY establishes a relationship between two tables.
It ensures referential integrity, meaning the foreign key value must exist in the referenced table.
3. The UNIQUE constraint ensures all values in a column are different (no duplicates).
Unlike PRIMARY KEY, it allows NULL values.
4. The NOT NULL constraint ensures that a column cannot store NULL values.
5. The CHECK constraint ensures that a column follows a specific condition.
6. The DEFAULT constraint assigns a default value if no value is provided.

create table dept

( d_no varchar(20) primary key,
dname varchar(10),
d_mgr varchar(20),
loc varchar(15));

insert into dept values (1,'ASA',default,'')

insert into dept values (2,'ASA',default,null)
insert into dept values (3,'STAT','RM','SDSOS')

create table emp

( cust_no varchar(20) primary key, check(cust_no like 'c%'),
fname varchar(10) not null, check(fname=upper(fname)),
lname varchar(10), check(lname=lower(lname)),
occupation varchar(20) default 'not assigned yet',
salary int unique,
d_no varchar(20),
foreign key(d_no) references dept(d_no));

OUTPUT (DEFAULT):
insert into emp values( 7872, null, '', default, 7788,2)

OUTPUT (UNIQUE):
insert into emp values( 7872, null, '','CLERK', 7788,2)
ORA-00001: unique constraint (SYSTEM.SYS_C004107) violated

OUTPUT (NOT NULL):

insert into emp values( 7876,null, '','CLERK', 7788,2)
ORA-01400: cannot insert NULL into ("SYSTEM"."EMP"."FNAME")

OUTPUT (CHECK):
insert into emp values(7876,'ADAM','','CLERK', 7788,2)
ORA-02290: check constraint (SYSTEM.SYS_C004109) violated

OUTPUT (FOREIGN KEY):

insert into emp values('c7776','ADAM','bejamin','CLERK',7798,4)
ORA-02291: integrity constraint (SYSTEM.SYS_C004121) violated - parent key not found

alter table emp rename cust_no to emp_no

alter table emp add constraint some check(EMP_NO like 'e%')
alter table emp add did int
alter table dept add did int unique
alter table emp add constraint fk foreign key(did) references dept(did)
rename emp to emp1
truncate table dept
truncate table emp1
drop table dept cascade constraints
drop table emp1

create table dept(

deptno int primary key,
dname varchar(14),
loc varchar(13));

DESC DEPT

INSERT ALL
into DEPT (DEPTNO, DNAME, LOC) values (10, 'ACCOUNTING', 'NEW YORK')
into DEPT (DEPTNO, DNAME, LOC) values(20, 'RESEARCH', 'DALLAS')
into DEPT (DEPTNO, DNAME, LOC)values(30, 'SALES', 'CHICAGO')
into DEPT (DEPTNO, DNAME, LOC)values(40, 'FINANCE', 'BOSTON')
into DEPT (DEPTNO, DNAME, LOC) values(50, 'MANAGEMENT', 'BOSTON')
into DEPT (DEPTNO, DNAME, LOC)values(60, 'LAW', 'BOSTON')
into DEPT (DEPTNO, DNAME, LOC)values(70, 'ADMINISTRATION', 'BOSTON')
SELECT * FROM dual;

SELECT * FROM dept;

DATE FUNCTIONS
ADD_MONTHS, MONTHS_BETWEEN, CURRENT_DATE, SYSDATE, LAST_DAY, NEXT_DAY
TO_CHAR, EXTRACT, TO_DATE
create table for_datefunctions_demo
(empid int primary key,
empname varchar(20),
hiredate date);

insert into for_datefunctions_demo values(1,'dazy','12-june-2002');

insert into for_datefunctions_demo values(2,'lily','5-may-2002');
insert into for_datefunctions_demo values(3,'rose','11-nov-2002');
select empname, add_months(hiredate,2)from for_datefunctions_demo;

select empname, current_date,sysdate,hiredate,

round(months_between(current_date,hiredate)/12)years_worked,
round(months_between(current_date,hiredate))months_worked,
round(months_between(current_date,hiredate))*30 days_worked
from for_datefunctions_demo

select empname,sysdate,hiredate,
trunc(months_between(sysdate,hiredate)/12)as years,
trunc(months_between(sysdate,hiredate)-
(trunc(months_between(sysdate,hiredate)/12)*12))as months
from for_datefunctions_demo

select empname,last_day(hiredate)from for_datefunctions_demo

select empname,next_day(hiredate,'monday')from for_datefunctions_demo

select empname,TO_CHAR(hiredate,'Q') joined_quarter from for_datefunctions_demo;

select empname,
to_char(trunc(hiredate,'cc'),'dd-mm-yyyy')century,
to_char(trunc(hiredate,'y'),'dd-mm-yyyy')year,
to_char(trunc(hiredate,'q'),'dd-mm-yyyy')quarter,
to_char(trunc(hiredate,'w'),'dd-mm-yyyy')dayofweek,
to_char(trunc(hiredate,'d'),'dd-mm-yyyy')closestsunday
from for_datefunctions_demo

select empname,hiredate from for_datefunctions_demo where hiredate between date '1988-12-01'and date '2016-12-31'

select empname,hiredate, extract(year from hiredate)year, extract(month from hiredate)month,extract(day from hiredate)day from
for_datefunctions_demo
 NUMERIC FUNCTIONS
MOD, LOG, POWER, ABS, SQRT, ROUND, EXP, GREATEST, LEAST, TRUNC, CEIL, TO_NUMBER
select greatest(10,20,30) MAX, least(10,20,30) MIN from dual;
SELECT MOD(7,2) FROM DUAL;

MOD(7,2)
1

SELECT LOG(10,1000) FROM DUAL;

LOG(10,1000)
3

SELECT POWER(4,-2)FROM DUAL;

POWER(4,-2)
.0625

SELECT POWER(2,4)FROM DUAL;

POWER(2,4)
16

SELECT ABS(-20.0)ABSOLUTE_VALUE FROM DUAL;

ABSOLUTE_VALUE
20

SELECT SQRT(4)FROM DUAL;

SQRT(4)
2

SELECT ROUND(20.045,2)FROM DUAL;

ROUND(20.045,2)
20.05

SELECT EXP(4) FROM DUAL;

EXP(4)
54.5981500331442390781102612028608784031

SELECT POWER(2,2) FROM DUAL;

POWER(2,2)
4

SELECT MOD(5,2) FROM DUAL;

MOD(5,2)
1

SELECT GREATEST(2,5,0) FROM DUAL;

GREATEST(2,5,0)
5

SELECT LEAST(5,2,0) FROM DUAL;

LEAST(5,2,0)
0

SELECT TRUNC(59.9)FROM DUAL;

TRUNC(59.9)
59
 SELECT CEIL(59.1)FROM DUAL;
CEIL(59.1)
60

SELECT ROUND(59.9)FROM DUAL;

ROUND(59.9)
60

SELECT ROUND (59.1) FROM DUAL;

ROUND(59.1)
59

1. The AND & OR Operators

EMPID EMPNAME HIREDATE
1 dazy 12-JUN-02
2 lily 05-MAY-02
3 rose 11-NOV-02

SELECT * FROM for_datefunctions_demo WHERE EMPNAME = 'dazy' AND EMPID = 2

Output:
No data found
SELECT * FROM for_datefunctions_demo WHERE EMPNAME = 'dazy' OR EMPID = 2
Output:
EMPID EMPNAME HIREDATE
1 dazy 12-JUN-02
2 lily 05-MAY-02

SELECT * FROM for_datefunctions_demo WHERE EMPNAME = 'dazy' AND EMPID = 1

Output:
EMPID EMPNAME HIREDATE
1 dazy 12-JUN-02

SELECT * FROM for_datefunctions_demo WHERE EMPNAME = 'dazy' AND HIREDATE > '1-JUN-2000'
Output:
EMPID EMPNAME HIREDATE
1 dazy 12-JUN-02

SELECT SYSDATE, CURRENT_DATE FROM DUAL

SYSDATE CURRENT_DATE
07-MAR-25 07-MAR-25

create table dept(

deptno int primary key,
dname varchar(14),
loc varchar(13));

create table emp(

empno int primary key,
ename varchar(10),
job varchar(9),
mgr int,
hiredate date,
sal int,
comm int,
deptno,
foreign key (deptno) references dept (deptno) );

INSERT ALL
into DEPT (DEPTNO, DNAME, LOC) values (10, 'ACCOUNTING', 'NEW YORK')
into DEPT (DEPTNO, DNAME, LOC) values(20, 'RESEARCH', 'DALLAS')
into DEPT (DEPTNO, DNAME, LOC)values(30, 'SALES', 'CHICAGO')
into DEPT (DEPTNO, DNAME, LOC)values(40, 'FINANCE', 'BOSTON')
into DEPT (DEPTNO, DNAME, LOC) values(50, 'MANAGEMENT', 'BOSTON')
into DEPT (DEPTNO, DNAME, LOC)values(60, 'LAW', 'BOSTON')
into DEPT (DEPTNO, DNAME, LOC)values(70, 'ADMINISTRATION', 'BOSTON')
SELECT * FROM dual;

INSERT ALL
into emp( empno,ename ,job ,mgr , hiredate, sal , comm , deptno) values( 7839, 'KING', 'PRESIDENT', null, to_date('17-11-1981','dd-mm-yyyy'),
5000, null, 10 )
into emp( empno,ename ,job ,mgr , hiredate, sal , comm , deptno) values( 7782, 'CLARK', 'MANAGER', 7839, to_date('9-6-1981','dd-mm-yyyy'),
2450, null, 10 )
into emp( empno,ename ,job ,mgr , hiredate, sal , comm , deptno) values( 7844, 'TURNER', 'SALESMAN', 7698, to_date('8-9-1981','dd-mm-yyyy'),
1500, 0, 10 )
into emp( empno,ename ,job ,mgr , hiredate, sal , comm , deptno) values( 7934, 'MILLER', 'CLERK', 7782, to_date('23-1-1982','dd-mm-yyyy'), 1300,
null, 20 )
into emp( empno,ename ,job ,mgr , hiredate, sal , comm , deptno) values( 7566, 'JONES', 'MANAGER', 7839, to_date('2-4-1981','dd-mm-yyyy'), 2975,
null, 20 )
into emp( empno,ename ,job ,mgr , hiredate, sal , comm , deptno) values( 7788, 'SCOTT', 'ANALYST', 7566, to_date('13-JUL-87','dd-mm-rr') - 85,
3000, null, 20 )
into emp( empno,ename ,job ,mgr , hiredate, sal , comm , deptno) values( 7902, 'FORD', 'ANALYST', 7566, to_date('3-12-1981','dd-mm-yyyy'), 3000,
null, 20 )
into emp( empno,ename ,job ,mgr , hiredate, sal , comm , deptno) values( 7876, 'ADAMS', 'CLERK', 7788, to_date('13-JUL-87', 'dd-mm-yyyy'), 1100,
null, 20 )
into emp( empno,ename ,job ,mgr , hiredate, sal , comm , deptno) values( 764, 'MARTIN', 'SALESMAN', 7698, to_date('28-9-1981','dd-mm-yyyy'),
1250, 1400, 30 )
into emp( empno,ename ,job ,mgr , hiredate, sal , comm , deptno) values( 7900, 'JAMES', 'CLERK', 7698, to_date('3-12-1981','dd-mm-yyyy'), 950,
null, 30 )
into emp( empno,ename ,job ,mgr , hiredate, sal , comm , deptno) values( 7499, 'ALLEN', 'SALESMAN', 7698, to_date('20-2-1981','dd-mm-yyyy'),
1600, 300, 30 )
into emp( empno,ename ,job ,mgr , hiredate, sal , comm , deptno) values( 784, 'TURNER', 'SALESMAN', 7698, to_date('8-9-1981','dd-mm-yyyy'),
1500, 0, 40 )
into emp( empno,ename ,job ,mgr , hiredate, sal , comm , deptno) values( 7698, 'BLAKE', 'MANAGER', 7839, to_date('1-5-1981','dd-mm-yyyy'), 2850,
null, 40 )
into emp( empno,ename ,job ,mgr , hiredate, sal , comm , deptno) values( 7521, 'WARD', 'SALESMAN', 7698, to_date('22-2-1981','dd-mm-yyyy'),
1250, 500, 50 )
into emp(empno,ename ,job ,mgr , hiredate, sal , comm , deptno) values( 794, 'MILLER', 'CLERK', 7782, to_date('23-1-1982','dd-mm-yyyy'), 1300, '',
50 )
into emp(empno,ename ,job ,mgr , hiredate, sal , comm , deptno) values( 790, 'JAMES', 'CLERK', 7698, to_date('3-12-1981','dd-mm-yyyy'), 950, '', 60 )
into emp( empno,ename ,job ,mgr , hiredate, sal , comm , deptno) values( 7654, 'MARTIN', 'SALESMAN', 7698, to_date('28-9-1981','dd-mm-yyyy'),
1250, 1400, 60 )
into emp( empno,ename ,job ,mgr , hiredate, sal , comm , deptno) values( 786, 'ADAMS', 'CLERK', 7788, to_date('13-JUL-87', 'dd-mm-yyyy'), 1100, '',
70 )
into emp( empno,ename ,job ,mgr , hiredate, sal , comm , deptno) values( 7369, 'SMITH', 'CLERK', 7902, to_date('17-12-1980','dd-mm-yyyy'), 800,
null, 70 )

select * from dual;

select ename, dname, job, empno, hiredate, loc from emp, dept where emp.deptno = dept.deptno order by ename;
AGGREGATE FUNCTIONS (SUM, MAX, MIN, COUNT, AVG),
groupby, having, order by,
PATTERN MATCHING (%, _ , LIKE, NOT LIKE)

select avg(sal) from emp group by DEPTNO having deptno>30;

select job,max(sal),min(sal),sum(sal),avg(sal),count(*) No_of_Employees from emp , dept where emp.deptno = dept.deptno group by job

select dname,max(sal),min(sal),sum(sal),avg(sal),count(*) No_of_Employees from emp , dept where emp.deptno = dept.deptno group by Dname

select deptno, ename

from emp
where deptno>=40
order by DEPTNO desc ;

select dname, count(*) count_of_employees

from dept d, emp e
where d.deptno = e.deptno
group by DNAME
having dname like '%A%'
order by dname

SELECT * FROM DEPT WHERE DNAME NOT LIKE '_E%' and DNAME NOT LIKE '_I%'
SELECT * FROM DEPT WHERE DNAME LIKE '_E%' or DNAME LIKE '_I%'
SELECT * FROM DEPT WHERE CustomerName LIKE 'a%'
Above query Finds any values that start with "a"
SELECT * FROM DEPT WHERE CustomerName LIKE '%a'
Above query Finds any values that end with "a"
SELECT * FROM DEPT WHERE CustomerName LIKE '%or%'
Above query Finds any values that have "or" in any position
SELECT * FROM DEPT WHERE CustomerName LIKE '_r%'
Above query Finds any values that have "r" in the second position
SELECT * FROM DEPT WHERE CustomerName LIKE 'a__%'
Above query Finds any values that start with "a" and are at least 3 characters in length
SELECT * FROM DEPT WHERE ContactName LIKE 'a%o'
Above query Finds any values that start with "a" and ends with "o"

THE WHERE CLAUSE (>,<,>=,<=,=,!=,<>,BETWEEN, IN)

select * from dept where DEPTNO between 20 and 60

select * from dept where DEPTNO not between 20 and 60

SELECT * from dept WHERE DEPTNO IN(20,30,60)

SELECT * from dept WHERE DEPTNO < 30

SELECT * from dept WHERE DEPTNO <= 30
SELECT * from dept WHERE DEPTNO > 30
SELECT * from dept WHERE DEPTNO >= 30
SELECT * from dept WHERE DEPTNO != 30
SELECT * from dept WHERE DEPTNO <> 30

DATA MANIPULATION LANGUAGE (DML)

SELECT, INSERT (ROW), INSERT ALL (ROWS), UPDATE (USING SET), AND DELETE.

select distinct dname from dept;

select distinct job from emp;

insert into DEPT (DEPTNO, DNAME, LOC) values(80, 'ACCOUNTING_try_inserting', 'NEW YORK');
SELECT * from dept

UPDATE Customers
SET LOC = 'TEXAS'
WHERE DEPTNO = 80;

DELETE FROM dept WHERE LOC ='TEXAS';

STRING FUNCTIONS
LOWER, UPPER, INITCAP, ASCII, LENGTH, CONCAT, || ' ' ||, SUBSTR, TRIM, LENGTH(TRIM()) , LTRIM, RTRIM, LPAD, RPAD,
TRANSLATE, INSTR

select DNAME,lower(DNAME),upper(DNAME),initcap(DNAME),ascii(DNAME) Ascii_OF_FIRST_LE

TTER,length(DNAME),concat(DNAME,LOC) as FullName FROM DEPT WHERE DEPTNO = 1;

SELECT DNAME, DNAME || ' ' || ' Designation:' || LOC FULLNAME FROM DEPT WHERE DEPTNO = 1;

Extract a substring from a dname(start at position 6, extract 8 characters): substr(DNAME,6,8)

ACCOUNTING
1 2 3 4 5 6 7 8 9 10

select substr(DNAME,0,6) DNAME, substr(DNAME,13,7) DNAME, substr(DNAME,6,8) DNAME FROM DEPT WHERE DEPTNO = 1;

SELECT DNAME, length(DNAME), TRIM(DNAME) TRIMMED_DNAME, length(TRIM(DNAME)) TRIMMED_DNAME FROM DEPT WHERE
DEPTNO = 1;

SELECT DNAME, length(DNAME), LTRIM(DNAME) LTRIMMED_DNAME, length(TRIM(DNAME)) LTRIMMED_DNAME FROM DEPT

WHERE DEPTNO = 1;

SELECT DNAME, length(DNAME), RTRIM(DNAME) RTRIMMED_DNAME, length(TRIM(DNAME)) RTRIMMED_DNAME FROM DEPT

WHERE DEPTNO = 1;

SELECT LPAD('321012189',18,'SAPID'), RPAD('321012189',11,'FYBSCASA') FROM DEPT WHERE DEPTNO = 1

SELECT TRANSLATE('SUNANDAN', '()Am', '[]a' ) TRANSLATED_DNAME FROM DUAL

SELECT INSTR('SUNANDAN','A') POSITION_OF_A_IN_STRING FROM DUAL

SELECT INSTR('COMPUTER MAINTENANCE CORPORATION','A') FROM DUAL;

SELECT INSTR('COMPUTER MAINTENANCE CORPORATION','A',12,1) FROM DUAL;

SELECT INSTR('COMPUTER MAINTENANCE CORPORATION','A',12,2) FROM DUAL;

Union
The UNION operator is used to combine the result-set of two or more SELECT statements.
• Each SELECT statement within UNION must have the same number of columns
• The columns must also have similar data types
• The columns in each SELECT statement must also be in the same order
UNION Syntax
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
UNION ALL Syntax
The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL:
SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

SELECT d.department_name,
e.employee_name
FROM employees e, departments d
WHERE e.department_id = d.department_id (+)
UNION ALL
SELECT d.department_name,
e.employee_name
FROM departments d, employees e
WHERE d.department_id = e.department_id (+)
AND e.employee_name IS NULL
ORDER BY 1, 2;
DEPARTMENT_NAME EMPLOYEE_NAME
ACCOUNTING CLARK
ACCOUNTING KING
ACCOUNTING MILLER
OPERATIONS -
RESEARCH ADAMS
RESEARCH FORD
RESEARCH SCOTT
SALES ALLEN
SALES BLAKE
SALES JAMES
SALES MARTIN
SALES TURNER
SALES WARD
- JONES

[INNER] JOIN ... ON

An INNER JOIN combines data from two tables where there is a match on the joining column(s) in both tables.

Remember, the INNER keyword is optional. In the examples below, we are returning the DEPARTMENT_NAME and the EMPLOYEE_NAME for
each employee. The OPERATIONS department has a DEPARTMENT_ID of 40, so it is not removed by the filter condition, but there are no
employees in this department, so there is no match and it is not returned in the result set.

SELECT d.department_name,
e.employee_name
FROM departments d
JOIN employees e ON d.department_id = e.department_id
WHERE d.department_id >= 30
ORDER BY d.department_name;

DEPARTMENT_NAME EMPLOYEE_NAME
SALES ALLEN
SALES WARD
SALES TURNER
SALES BLAKE
 SALES JAMES
SALES MARTIN

[INNER] JOIN ... USING

The INNER JOIN ... USING is almost a half-way house between a conventional INNER JOIN and a NATURAL JOIN. The join is made using
columns with matching names in each table, but you have to specify the columns to be used, not the whole condition. This allows you to join on a
subset of the columns common to both tables.
SELECT e.employee_name,
d.department_name
FROM employees e
JOIN departments d USING (department_id)
ORDER BY e.employee_name;

LEFT [OUTER] JOIN

A LEFT [OUTER] JOIN returns all valid rows from the table on the left side of the JOIN keyword, along with the values from the table on the right
side, or NULLs if a matching row doesn't exist.

Using the previous example, but switching to a LEFT OUTER JOIN means we will see the OPERATIONS department, even though it has no
employees.

SELECT d.department_name,
e.employee_name
FROM departments d
LEFT OUTER JOIN employees e ON d.department_id = e.department_id
WHERE d.department_id >= 30
ORDER BY d.department_name, e.employee_name;

DEPARTMENT_NAME EMPLOYEE_NAME
OPERATIONS -
SALES ALLEN
SALES BLAKE
SALES JAMES
SALES MARTIN
SALES TURNER
SALES WARD

RIGHT [OUTER] JOIN

The RIGHT [OUTER] JOIN is the opposite of the LEFT [OUTER] JOIN. It returns all valid rows from the table on the right side of
the JOIN keyword, along with the values from the table on the left side, or NULLs if a matching row doesn't exist. All points raised in the previous
section apply here also.

The following example has altered the order of the tables so a RIGHT [OUTER] JOIN is now required.
SELECT d.department_name,
e.employee_name
FROM employees e
RIGHT OUTER JOIN departments d ON e.department_id = d.department_id
WHERE d.department_id >= 30
ORDER BY d.department_name, e.employee_name;
DEPARTMENT_NAME EMPLOYEE_NAME
OPERATIONS -
SALES ALLEN
SALES BLAKE
 SALES JAMES
SALES MARTIN
SALES TURNER
SALES WARD

FULL [OUTER] JOIN

A FULL [OUTER] JOIN combines all the rows from the tables on the left and right sides of the join. If there is a conventional match it is made. If
either side has missing data, it is replaced by NULLs, rather than throwing the row away.

To see a working example, we need to add another employee who is not assigned to a department.
INSERT INTO employees VALUES (8888,'JONES','DBA',null,to_date('02-1-1982','dd-mm-yyyy'),1300,NULL,NULL);

DEPARTMENT_NAME EMPLOYEE_NAME
ACCOUNTING CLARK
ACCOUNTING KING
ACCOUNTING MILLER
OPERATIONS -
RESEARCH ADAMS
RESEARCH FORD
RESEARCH SCOTT
SALES ALLEN
SALES BLAKE
SALES JAMES
SALES MARTIN
SALES TURNER
SALES WARD
- JONES

CROSS JOIN
A CROSS JOIN is the deliberate creation of a Cartesian product. There are no join columns specified, so every possible combination of rows
between the two tables is produced.

Here is an example of an ANSI CROSS JOIN.

SELECT e.employee_name,
d.department_name
FROM employees e
CROSS JOIN departments d
ORDER BY e.employee_name, d.department_name;

EMPLOYEE_NAME DEPARTMENT_NAME
ADAMS ACCOUNTING
ADAMS OPERATIONS
ADAMS RESEARCH
ADAMS SALES
ALLEN ACCOUNTING
ALLEN OPERATIONS
ALLEN RESEARCH
ALLEN SALES
BLAKE ACCOUNTING
BLAKE OPERATIONS
 BLAKE RESEARCH
BLAKE SALES
CLARK ACCOUNTING
CLARK OPERATIONS
CLARK RESEARCH
CLARK SALES
FORD ACCOUNTING
FORD OPERATIONS
FORD RESEARCH
FORD SALES
JAMES ACCOUNTING
JAMES OPERATIONS
JAMES RESEARCH
JAMES SALES
JONES ACCOUNTING
JONES OPERATIONS
JONES RESEARCH
JONES SALES
KING ACCOUNTING
KING OPERATIONS
KING RESEARCH
KING SALES
MARTIN ACCOUNTING
MARTIN OPERATIONS
MARTIN RESEARCH
MARTIN SALES
MILLER ACCOUNTING
MILLER OPERATIONS
MILLER RESEARCH
MILLER SALES
SCOTT ACCOUNTING
SCOTT OPERATIONS
SCOTT RESEARCH
SCOTT SALES
TURNER ACCOUNTING
TURNER OPERATIONS
TURNER RESEARCH
TURNER SALES
WARD ACCOUNTING
WARD OPERATIONS
WARD RESEARCH
WARD SALES

NATURAL JOIN
A NATURAL JOIN is a variant on an INNER JOIN. The join columns are determined implicitly, based on the column names. Any columns that
share the same name between the two tables are assumed to be join columns. Here is an example using the ANSI join syntax.
SELECT e.employee_name,
d.department_name
FROM employees e
NATURAL JOIN departments d
ORDER BY e.employee_name, d.department_name;

EMPLOYEE_NAME DEPARTMENT_NAME
ADAMS RESEARCH
ALLEN SALES
 BLAKE SALES
CLARK ACCOUNTING
FORD RESEARCH
JAMES SALES
KING ACCOUNTING
MARTIN SALES
MILLER ACCOUNTING
SCOTT RESEARCH
TURNER SALES
WARD SALES

NESTED QUERIES:
DISPLAY THE EMPLOYEE NUMBER AND NAME OF EMPLOYEE WORKING AS CLERK AND EARNING HIGHEST SALARY AMONG
CLERKS.
SELECT EMPNO, ENAME FROM EMP WHERE JOB='CLERK' AND SAL=(SELECT MAX(SAL) FROM EMP WHERE JOB='CLERK');

DISPLAY THE NAMES OF THE SALESMAN WHO EARNS A SALARY MORE THAN THE HIGHEST SALARY OF ANY CLERK.
SELECT ENAME FROM EMP WHERE JOB=’SALESMAN’ AND SAL > (SELECT MAX(SAL) FROM EMP WHERE JOB='CLERK');

DISPLAY THE NAMES OF CLERKS WHO EARN SALARY MORE THAN THAT OF JAMES OF THAT OF SAL LESSER THAN THAT OF
SCOTT
SELECT ENAME FROM EMP WHERE JOB='CLERK' AND SAL<(SELECT SAL FROM EMP WHERE ENAME='SCOTT') AND SAL>(SELECT SAL
FROM EMP WHERE ENAME='JAMES');

DISPLAY THE NAMES OF EMPLOYEES WHO EARN A SAL MORE THAN THAT OF JAMES OR THAT OF SALARY GREATER THAN THAT
OF SCOTT.
SELECT ENAME FROM EMP WHERE SAL < (SELECT SAL FROM EMP WHERE ENAME='SCOTT') AND SAL > (SELECT SAL FROM EMP WHERE
ENAME='JAMES');

DISPLAY THE NAMES OF THE EMPLOYEES WHO EARN HIGHEST SALARY IN THEIR RESPECTIVE DEPARTMENTS.
SELECT * FROM EMP E WHERE SAL = (SELECT MAX(SAL) FROM EMP WHERE DEPTNO=E.DEPTNO)
DISPLAY THE NAMES OF EMPLOYEES WHO EARN HIGHEST SALARIES IN THEIR RESPECTIVE JOB GROUPS.
SELECT * FROM EMP E WHERE SAL IN (SELECT MAX(SAL) FROM EMP GROUP BY JOB HAVING E.JOB=JOB)

DISPLAY THE EMPLOYEE NAMES WHO ARE WORKING IN ACCOUNTING DEPT.

SELECT ENAME FROM EMP WHERE DEPTNO = (SELECT DEPTNO FROM DEPT WHERE DNAME=”ACCOUNTING”);

DISPLAY THE EMPLOYEE NAMES WHO ARE WORKING IN CHICAGO.

SELECT ENAME FROM EMP WHERE DEPTNO = (SELECT DEPTNO FROM DEPT WHERE LOC=’CHICAGO’);

DISPLAY THE JOB GROUPS HAVING TOTAL SALARY GREATER THEN THE MAXIMUM SALARY FOR MANAGERS.
SELECT JOB, SUM(SAL) FROM EMP GROUP BY JOB HAVING SUM(SAL) > (SELECT MAX(SAL) FROM EMP WHERE JOB='MANAGER');

QP PATTERN
SET A SET B
1. Attempt any one out of two [10] 1. Attempt any one out of two [10]
A. Theory A. Theory
OR OR
B. Queries + Thoery B. Theory

2. Attempt any one out of two [10] 2. Attempt any one out of two [10]
A. Theory A. Queries
OR OR
B. Queries B. Queries

3. Attempt any one out of two [10] 3. Attempt any one out of two [10]
A. Queries A. Theory
OR OR
B. Queries B. Queries

4. Attempt any one out of two [10] 4. Attempt any one out of two [10]
A. Queries A. Theory
OR OR
B. Queries B. Queries [10]

5. Attempt any one out of two [10] 5. Attempt any one out of two [10]
A. Theory A. Queries
OR OR
B. Theory + Queries B. Theory + Queries