Selection operation in DBMS Theory - Mini Project: Build & Apply

Selection Operation in DBMS

📖 Scenario: You are working with a simple database table called Employees that stores information about employees in a company. You want to learn how to use the selection operation in SQL to find specific employees based on certain conditions.

🎯 Goal: Build an SQL query step-by-step that selects employees from the Employees table who meet a specific condition using the selection operation.

📋 What You'll Learn

Create a table named Employees with columns EmpID, Name, and Department.

Insert specific employee records into the Employees table.

Write a selection query to find employees in the Sales department.

Complete the query to select only the Name and Department columns.

💡 Why This Matters

🌍 Real World

Selection operations are used in databases to find specific records that meet certain criteria, such as finding all employees in a particular department.

💼 Career

Database administrators and developers use selection queries daily to retrieve meaningful data from large datasets efficiently.

1

Create the Employees table

Write an SQL statement to create a table called Employees with three columns: EmpID as an integer, Name as text, and Department as text.

DBMS Theory

-- Write the CREATE TABLE statement for Employees
-- Your code here

Hint

Use CREATE TABLE Employees (EmpID INT, Name TEXT, Department TEXT); to create the table.

2

Insert employee records

Write SQL statements to insert these exact records into the Employees table: (1, 'Alice', 'Sales'), (2, 'Bob', 'HR'), (3, 'Charlie', 'Sales').

DBMS Theory

CREATE TABLE Employees (
  EmpID INT,
  Name TEXT,
  Department TEXT
);
-- Insert the three employee records below
-- Your code here

Hint

Use INSERT INTO Employees (EmpID, Name, Department) VALUES (1, 'Alice', 'Sales'), (2, 'Bob', 'HR'), (3, 'Charlie', 'Sales');

3

Select employees from Sales department

Write an SQL SELECT statement to select all columns from Employees where the Department is exactly 'Sales'. Use WHERE Department = 'Sales'.

DBMS Theory

CREATE TABLE Employees (
  EmpID INT,
  Name TEXT,
  Department TEXT
);
INSERT INTO Employees (EmpID, Name, Department) VALUES
  (1, 'Alice', 'Sales'),
  (2, 'Bob', 'HR'),
  (3, 'Charlie', 'Sales');
-- Select all columns for employees in Sales department
-- Your code here

Hint

Use SELECT * FROM Employees WHERE Department = 'Sales'; to get all Sales employees.

4

Select only Name and Department columns

Modify the previous SELECT statement to select only the Name and Department columns for employees in the Sales department.

DBMS Theory

CREATE TABLE Employees (
  EmpID INT,
  Name TEXT,
  Department TEXT
);
INSERT INTO Employees (EmpID, Name, Department) VALUES
  (1, 'Alice', 'Sales'),
  (2, 'Bob', 'HR'),
  (3, 'Charlie', 'Sales');
-- Select only Name and Department columns for Sales employees
-- Your code here

Hint

Use SELECT Name, Department FROM Employees WHERE Department = 'Sales'; to get only those columns.

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