ITI COPA (Computer Operator & Programming Assistant) – Basic Notes

Topic-

Queries, Transactions, Joins and Functions

1. Introduction to Queries

In a database system, a query is a request made by the user to retrieve, insert, update, or delete data from a database. Queries are written using SQL (Structured Query Language). SQL queries help users interact with the database without knowing the internal storage details.

Queries are one of the most important concepts for ITI COPA students because they are widely used in office databases, business applications, reports, and decision-making systems.

2. Concept of Transactions

What is a Transaction?

A transaction is a sequence of one or more SQL operations (such as INSERT, UPDATE, DELETE, or SELECT) that are executed as a single logical unit of work.

In simple words, a transaction ensures that a set of database operations are either completed fully or not executed at all.

Example of a Transaction

Consider a bank transfer:

Deduct ₹1000 from Account A
Add ₹1000 to Account B

Both operations must succeed together. If one fails, the transaction must be rolled back.

Transaction States

A transaction can be in different states:

Active – Transaction is executing
Partially Committed – All statements executed but not yet saved
Committed – Changes permanently saved
Failed – Transaction cannot proceed
Rolled Back – Changes undone

Transaction Control Commands (TCL)

Transaction control commands manage transactions in SQL.

COMMIT
- Saves changes permanently
ROLLBACK
- Undoes changes
SAVEPOINT
- Sets a point to rollback partially

3. ACID Properties of Transactions

The reliability of a transaction is ensured by ACID properties.

ACID stands for:

A – Atomicity
C – Consistency
I – Isolation
D – Durability

1. Atomicity

Atomicity means “all or nothing”.

Either all operations of a transaction are performed
Or none of them are performed

Example:
If power fails during a transaction, the database should not be left in an incomplete state.

2. Consistency

Consistency ensures that the database moves from one valid state to another valid state.

All integrity constraints must be satisfied
No data corruption should occur

3. Isolation

Isolation ensures that multiple transactions executed at the same time do not interfere with each other.

Each transaction behaves as if it is the only one running
Prevents dirty reads and incorrect data

4. Durability

Durability ensures that once a transaction is committed, the changes are permanently saved, even in case of:

System crash
Power failure

4. Transaction Constraints

Transaction constraints are rules that ensure correctness and integrity during transaction execution.

Common Transaction Constraints

Primary key constraints
Foreign key constraints
NOT NULL constraints
UNIQUE constraints
CHECK constraints

These constraints ensure:

No duplicate data
Valid relationships
Accurate and reliable database operations

5. Introduction to Joins

In relational databases, data is stored in multiple tables. To retrieve meaningful information, it is often necessary to combine data from two or more tables. This is done using JOIN operations.

What is a Join?

A join is an SQL operation used to combine rows from two or more tables based on a related column.

6. Types of Joins

1. Inner Join

Returns records that have matching values in both tables
Most commonly used join

Example Use:
Employee table joined with Department table

2. Left Outer Join (LEFT JOIN)

Returns all records from the left table
Matching records from the right table
If no match, NULL values are shown

3. Right Outer Join (RIGHT JOIN)

Returns all records from the right table
Matching records from the left table
Non-matching values show NULL

4. Full Outer Join

Returns records when there is a match in either table
Includes all rows from both tables

5. Self Join

A table is joined with itself
Used when data in the same table is related

Importance of Joins

Data normalization requires multiple tables
Joins help retrieve combined information
Essential for reports and analysis

7. Sub Queries

What is a Sub Query?

A subquery is a query written inside another SQL query. It is also called a nested query.

Characteristics of Sub Queries

Executed first
Result is passed to the main query
Can return single or multiple values

Types of Sub Queries

Single-Row Sub Query
- Returns one value
Multiple-Row Sub Query
- Returns multiple rows
Correlated Sub Query
- Depends on outer query
- Executed repeatedly

Advantages of Sub Queries

Simplifies complex queries
Improves readability
Useful in conditional filtering

8. SQL Functions

SQL functions are predefined operations used to perform calculations or manipulate data.

Types of SQL Functions

Aggregate Functions
Scalar Functions

9. Aggregate Functions

Aggregate functions perform calculations on a group of values and return a single result.

1. SUM()

Returns total of numeric values

Use:
Calculate total salary, total marks, total sales

2. AVG()

Returns average value

Use:
Calculate average marks or salary

3. MAX()

Returns maximum value

Use:
Find highest salary or highest marks

4. MIN()

Returns minimum value

Use:
Find lowest salary or minimum marks

5. COUNT()

Returns number of records

Use:
Count number of students, employees, products

10. Importance of Functions in Queries

Simplify data analysis
Used in reports and summaries
Improve efficiency
Reduce manual calculations

11. Combining Joins and Functions

Joins and functions are often used together to:

Analyze data across tables
Generate summary reports
Support decision making

Example Uses

Total sales department-wise
Average marks subject-wise
Count employees in each department

12. Relevance for ITI COPA Students

Understanding queries, joins, transactions, and functions is important because:

Used in office databases
Required for backend operations
Helps in application development
Essential for CBT and practical exams
Improves employability

MCQ Questions (40 Questions – CBT Exam)

1. A query is used to:

A) Design database
B) Retrieve data
C) Format disk
D) Install software
Ans: B

2. A transaction is:

A) Single SQL command
B) Group of SQL operations
C) Backup process
D) Data model
Ans: B

3. Transaction ensures:

A) Data redundancy
B) Data integrity
C) Data deletion
D) Data loss
Ans: B

4. ACID stands for:

A) Atomicity, Consistency, Isolation, Durability
B) Accuracy, Consistency, Integrity, Durability
C) Atomicity, Control, Isolation, Data
D) Access, Control, Integrity, Data
Ans: A

5. Atomicity means:

A) Partial execution
B) All or nothing
C) Parallel execution
D) Permanent storage
Ans: B

6. Consistency ensures:

A) Database remains valid
B) Fast execution
C) Data duplication
D) Parallel access
Ans: A

7. Isolation ensures:

A) Data backup
B) Independent transactions
C) Faster joins
D) Data encryption
Ans: B

8. Durability means:

A) Temporary storage
B) Permanent storage
C) Data duplication
D) Data deletion
Ans: B

9. COMMIT command is used to:

A) Undo changes
B) Save changes
C) Delete data
D) Create table
Ans: B

10. ROLLBACK command is used to:

A) Save changes
B) Undo changes
C) Create index
D) Grant access
Ans: B

11. SAVEPOINT is used to:

A) Save database
B) Create table
C) Partial rollback
D) Delete records
Ans: C

12. JOIN is used to:

A) Delete tables
B) Combine tables
C) Create index
D) Backup database
Ans: B

13. INNER JOIN returns:

A) All rows
B) Matching rows only
C) Left table rows
D) Right table rows
Ans: B

14. LEFT JOIN returns:

A) Only matching rows
B) All right table rows
C) All left table rows
D) No rows
Ans: C

15. RIGHT JOIN returns:

A) All left table rows
B) All right table rows
C) Only matching rows
D) No rows
Ans: B

16. FULL OUTER JOIN returns:

A) Only matching rows
B) Left table rows only
C) Right table rows only
D) All rows from both tables
Ans: D

17. Self join is used to:

A) Join two different tables
B) Join table with itself
C) Delete records
D) Create index
Ans: B

18. Subquery is also called:

A) Join query
B) Nested query
C) Main query
D) DDL query
Ans: B

19. Subquery is executed:

A) After main query
B) Before main query
C) Together
D) Randomly
Ans: B

20. Subquery inside WHERE clause is common for:

A) Table creation
B) Conditional filtering
C) Backup
D) Index creation
Ans: B

21. SQL functions are:

A) User-defined only
B) Predefined operations
C) Hardware commands
D) OS commands
Ans: B

22. SUM() function is used to:

A) Count rows
B) Find average
C) Find total
D) Find maximum
Ans: C

23. AVG() function returns:

A) Total
B) Count
C) Average
D) Minimum
Ans: C

24. MAX() function returns:

A) Highest value
B) Lowest value
C) Total value
D) Average value
Ans: A

25. MIN() function returns:

A) Highest value
B) Lowest value
C) Average
D) Count
Ans: B

26. COUNT() function returns:

A) Sum
B) Average
C) Number of rows
D) Maximum value
Ans: C

27. Aggregate functions operate on:

A) Single value
B) Group of values
C) Table structure
D) Index
Ans: B

28. Which function counts records?

A) SUM
B) AVG
C) COUNT
D) MAX
Ans: C

29. Joins are mainly used in:

A) Single table database
B) Normalized databases
C) Flat files
D) Text files
Ans: B

30. Transactions improve:

A) Data redundancy
B) Data integrity
C) Data duplication
D) Data loss
Ans: B

31. Which is a transaction control command?

A) SELECT
B) COMMIT
C) INSERT
D) CREATE
Ans: B

32. Dirty read is prevented by:

A) Atomicity
B) Consistency
C) Isolation
D) Durability
Ans: C

33. Multiple transactions at same time is called:

A) Serialization
B) Concurrency
C) Backup
D) Normalization
Ans: B

34. SQL JOIN works based on:

A) Data type
B) Related columns
C) Index only
D) File name
Ans: B

35. Subqueries are useful for:

A) Simple queries only
B) Complex conditions
C) Table creation
D) Index deletion
Ans: B

36. Aggregate functions ignore:

A) NULL values
B) Duplicate values
C) Index
D) Primary key
Ans: A

**37. COUNT(*) counts:**

A) Non-null values
B) Only numeric values
C) All rows
D) Only text values
Ans: C

38. Transactions are mainly used to ensure:

A) Speed
B) Accuracy
C) Reliability
D) Hardware control
Ans: C

39. Which command permanently saves changes?

A) ROLLBACK
B) SAVEPOINT
C) COMMIT
D) DELETE
Ans: C

40. Queries are written using:

A) HTML
B) SQL
C) XML
D) Java
Ans: B

ITI (Trade - COPA) Day-13