Selection operation in DBMS Theory - Step-by-Step Execution

Concept Flow - Selection operation

Start with a Table

↓

Apply Selection Condition

↓

Check each Row

↓

Include Row

↓

Form Result Table with Selected Rows

↓

End

The selection operation scans each row of a table and includes only those rows that satisfy a given condition, forming a new table.

Execution Sample

DBMS Theory

Table: Employees
Condition: Salary > 3000
Result: Rows where Salary > 3000

Selects employees whose salary is greater than 3000 from the Employees table.

Analysis Table

Step	Row Data	Condition (Salary > 3000)	Include in Result?
1	{ID: 1, Name: 'Alice', Salary: 2500}	2500 > 3000 = False	No
2	{ID: 2, Name: 'Bob', Salary: 3200}	3200 > 3000 = True	Yes
3	{ID: 3, Name: 'Carol', Salary: 4000}	4000 > 3000 = True	Yes
4	{ID: 4, Name: 'Dave', Salary: 2800}	2800 > 3000 = False	No
5	{ID: 5, Name: 'Eve', Salary: 3500}	3500 > 3000 = True	Yes
6	No more rows	-	-

💡 All rows checked; selection complete.

State Tracker

Variable	Start	After Row 1	After Row 2	After Row 3	After Row 4	After Row 5	Final
Result Table Rows	[]	[]	[Row 2]	[Row 2, Row 3]	[Row 2, Row 3]	[Row 2, Row 3, Row 5]	[Row 2, Row 3, Row 5]

Key Insights - 2 Insights

Why are some rows excluded even though they exist in the original table?

Rows are excluded if they do not satisfy the selection condition, as shown in execution_table rows 1 and 4 where Salary is not greater than 3000.

Does the selection operation change the original table?

No, the selection operation creates a new result table with only the rows that meet the condition; the original table remains unchanged.

Visual Quiz - 3 Questions

Look at the execution_table at Step 3, is the row included in the result?

A Yes, because Salary is 4000 which is greater than 3000

B No, because Salary is less than 3000

C No, because the row is missing data

D Yes, because all rows are included

💡 Hint

Refer to execution_table row 3 under 'Include in Result?' column.

At which step does the selection condition become false for the first time?

A Step 4

B Step 1

C Step 2

D Step 5

💡 Hint

Check execution_table rows 1 and 2 under 'Condition' column.

If the condition changed to Salary > 3500, which row would no longer be included?

A Row 5 (Salary 3500)

B Row 3 (Salary 4000)

C Row 2 (Salary 3200)

D Row 4 (Salary 2800)

💡 Hint

Look at variable_tracker and execution_table for Salary values and inclusion.

Concept Snapshot

Selection Operation in DBMS:
- Filters rows from a table based on a condition.
- Only rows satisfying the condition are included in the result.
- Original table remains unchanged.
- Result is a new table with selected rows.
- Commonly used to find specific data subsets.

Full Transcript

The selection operation in a database management system takes a table and a condition as input. It checks each row in the table to see if it meets the condition. If the row satisfies the condition, it is included in a new result table. If not, it is excluded. This process continues until all rows are checked. The original table is not changed. The output is a new table containing only the rows that meet the condition. For example, selecting employees with salary greater than 3000 will include only those employees whose salary is above 3000. This operation helps to find specific data from large tables easily.

Practice

1. What is the main purpose of the SELECT operation with a WHERE clause in a database?

easy

A. To delete all rows from a table

B. To add new rows to a table

C. To change the structure of a table

D. To filter rows that meet a specific condition

Solution

1. Step 1: Understand the SELECT operation

   The SELECT operation retrieves data from a table.

2. Step 2: Role of WHERE clause

   The WHERE clause filters rows based on a condition, returning only matching rows.

3. Final Answer:

   To filter rows that meet a specific condition -> Option D

4. Quick Check:

   Selection with WHERE filters rows [OK]

Hint: WHERE clause filters rows based on condition [OK]

Common Mistakes:

• Confusing SELECT with INSERT or DELETE
• Thinking WHERE changes table structure
• Assuming SELECT without WHERE deletes data

2. Which of the following is the correct syntax to select all columns from a table named Employees where the Age is greater than 30?

easy

A. SELECT * Employees WHERE Age > 30;

B. SELECT ALL FROM Employees WHERE Age > 30;

C. SELECT * FROM Employees WHERE Age > 30;

D. SELECT * FROM Employees AGE > 30;

Solution

1. Step 1: Check SELECT syntax

   The correct syntax to select all columns is SELECT * FROM table_name.

2. Step 2: Verify WHERE clause usage

   The WHERE clause must follow FROM and include a condition like Age > 30.

3. Final Answer:

   SELECT * FROM Employees WHERE Age > 30; -> Option C

4. Quick Check:

   Correct SELECT with WHERE syntax [OK]

Hint: Use SELECT * FROM table WHERE condition; [OK]

Common Mistakes:

• Omitting FROM keyword
• Using ALL instead of *
• Placing condition outside WHERE

3. Consider the table Students with columns ID, Name, and Score. What will be the result of the query?

SELECT Name FROM Students WHERE Score >= 75;

medium

A. Names of students with scores 75 or more

B. Names of students with scores less than 75

C. All columns of students with scores 75 or more

D. Error due to missing columns

Solution

1. Step 1: Analyze SELECT clause

   The query selects only the Name column from Students.

2. Step 2: Analyze WHERE condition

   WHERE Score >= 75 filters students with scores 75 or higher.

3. Final Answer:

   Names of students with scores 75 or more -> Option A

4. Quick Check:

   WHERE filters Score >= 75, SELECT Name only [OK]

Hint: WHERE filters rows; SELECT chooses columns [OK]

Common Mistakes:

• Thinking it selects all columns
• Confusing >= with >
• Assuming it returns scores too

4. Identify the error in the following SQL query:

SELECT * FROM Orders WHERE OrderDate = '2023-13-01';

medium

A. Table name Orders is incorrect

B. Invalid date format in WHERE clause

C. SELECT * is not allowed

D. Missing semicolon at end

Solution

1. Step 1: Check date format in WHERE clause

   The date '2023-13-01' has month 13, which is invalid.

2. Step 2: Verify other syntax parts

   SELECT * FROM Orders; and semicolon are correct; table name assumed valid.

3. Final Answer:

   Invalid date format in WHERE clause -> Option B

4. Quick Check:

   Date month must be 1-12 [OK]

Hint: Check date values carefully in WHERE clause [OK]

Common Mistakes:

• Ignoring invalid month number
• Thinking missing semicolon causes error
• Assuming SELECT * is wrong

5. You have a table Products with columns ProductID, Name, and Price. How would you write a query to select only products priced between 50 and 100 inclusive?

hard

A. SELECT * FROM Products WHERE Price >= 50 AND Price <= 100;

B. SELECT * FROM Products WHERE Price > 50 AND Price < 100;

C. SELECT * FROM Products WHERE Price >= 50 OR Price <= 100;

D. SELECT * FROM Products WHERE Price = 50 OR Price = 100;

Solution

1. Step 1: Understand inclusive range selection

   Inclusive means including 50 and 100, so use >= and <= operators.

2. Step 2: Construct the WHERE clause

   Use Price >= 50 AND Price <= 100 to filter the range correctly.

3. Step 3: Compare options

   The query with > 50 AND < 100 excludes boundaries; the one with = 50 OR = 100 selects only extremes; the one with >= 50 OR <= 100 selects all rows.

4. Final Answer:

   SELECT * FROM Products WHERE Price >= 50 AND Price <= 100; -> Option A

5. Quick Check:

   Inclusive range uses >= and <= [OK]

Hint: Use >= and <= for inclusive range [OK]

Common Mistakes:

• Using > and < excludes boundary values
• Using OR instead of AND for range
• Using OR with inequalities selects all rows