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

Selection Operation Implementation

📖 Scenario: You are working with a database of employees in a company. You want to find all employees who work in the 'Sales' department.

🎯 Goal: Build a SQL query that selects all columns from the employees table where the department is 'Sales'.

📋 What You'll Learn

Create a table called employees with columns id, name, and department.

Insert the exact data rows provided into the employees table.

Write a SQL SELECT query to retrieve all columns for employees in the 'Sales' department.

Use a WHERE clause to filter by the department column.

💡 Why This Matters

🌍 Real World

Filtering data in databases is a common task in business applications to find specific records.

💼 Career

Database querying skills are essential for roles like data analyst, database administrator, and backend developer.

1

Create the employees table and insert data

Write SQL statements to create a table called employees with columns id (integer), name (text), and department (text). Then insert these exact rows: (1, 'Alice', 'Sales'), (2, 'Bob', 'HR'), (3, 'Charlie', 'Sales'), (4, 'Diana', 'IT').

DBMS Theory

-- Create the employees table and insert data
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Set up the selection condition

Create a variable or placeholder for the department name to filter by. Assign the exact string 'Sales' to a variable called target_department.

DBMS Theory

CREATE TABLE employees (
  id INTEGER,
  name TEXT,
  department TEXT
);

INSERT INTO employees (id, name, department) VALUES
  (1, 'Alice', 'Sales'),
  (2, 'Bob', 'HR'),
  (3, 'Charlie', 'Sales'),
  (4, 'Diana', 'IT');

-- Define the target department to filter
-- Your code here

Need a hint?

In SQL, variables are not always supported, so just note the filter value clearly.

3

Write the SELECT query with WHERE clause

Write a SQL SELECT statement to get all columns from the employees table where the department is exactly 'Sales'. Use SELECT * and a WHERE clause with department = 'Sales'.

DBMS Theory

CREATE TABLE employees (
  id INTEGER,
  name TEXT,
  department TEXT
);

INSERT INTO employees (id, name, department) VALUES
  (1, 'Alice', 'Sales'),
  (2, 'Bob', 'HR'),
  (3, 'Charlie', 'Sales'),
  (4, 'Diana', 'IT');

-- In SQL scripts, variables are not standard, but for demonstration, use a comment
-- target_department = 'Sales'

-- Write the SELECT query with WHERE clause
-- Your code here

Need a hint?

Use SELECT * to get all columns and WHERE to filter rows.

4

Complete the selection operation implementation

Ensure the SQL query is complete and ready to run. Confirm the query selects all employees from the employees table where the department is 'Sales'.

DBMS Theory

CREATE TABLE employees (
  id INTEGER,
  name TEXT,
  department TEXT
);

INSERT INTO employees (id, name, department) VALUES
  (1, 'Alice', 'Sales'),
  (2, 'Bob', 'HR'),
  (3, 'Charlie', 'Sales'),
  (4, 'Diana', 'IT');

-- In SQL scripts, variables are not standard, but for demonstration, use a comment
-- target_department = 'Sales'

SELECT * FROM employees WHERE department = 'Sales';

-- Finalize the query if needed
-- Your code here

Need a hint?

The query is complete as is. No additional code needed.