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

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

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

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

Hint

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

Practice

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

easy

A. To change the structure of a table

B. To delete rows from a table

C. To add new columns to a table

D. To retrieve only rows that meet specific conditions

Solution

1. Step 1: Understand the role of SELECT

   The SELECT statement is used to get data from a table.

2. Step 2: Understand the role of WHERE clause

   The WHERE clause filters rows to include only those that meet given conditions.

3. Final Answer:

   To retrieve only rows that meet specific conditions -> Option D

4. Quick Check:

   SELECT + WHERE = filtered rows [OK]

Hint: WHERE filters rows; SELECT retrieves data [OK]

Common Mistakes:

• Confusing WHERE with DELETE
• Thinking WHERE adds columns
• Believing WHERE changes table structure

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 * FROM Employees WHERE Age > 30;

B. SELECT * 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 starts with SELECT, then columns or *, then FROM table name.

2. Step 2: Check WHERE clause syntax

   WHERE must be followed by a condition like Age > 30.

3. Final Answer:

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

4. Quick Check:

   Correct SELECT + FROM + WHERE syntax [OK]

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

Common Mistakes:

• Omitting FROM keyword
• Placing WHERE before FROM
• Missing semicolon at end

3. Consider the table Products with columns ProductID, Name, and Price. What will be the result of this query?

SELECT Name FROM Products WHERE Price <= 50;

medium

A. All product names regardless of price

B. All product names with price less than or equal to 50

C. All product names with price greater than 50

D. An error because Price <= 50 is invalid

Solution

1. Step 1: Understand the SELECT clause

   The query selects only the Name column from the Products table.

2. Step 2: Understand the WHERE condition

   The condition Price <= 50 filters rows to those with price 50 or less.

3. Final Answer:

   All product names with price less than or equal to 50 -> Option B

4. Quick Check:

   WHERE Price <= 50 filters products [OK]

Hint: WHERE filters rows by condition; SELECT picks columns [OK]

Common Mistakes:

• Confusing <= with >= operator
• Expecting all products without filter
• Thinking query causes error

4. Identify the error in the following SQL query:

SELECT * FROM Customers WHERE City = 'New York'

medium

A. Missing FROM keyword

B. Incorrect use of single quotes around string

C. Missing semicolon at the end

D. WHERE clause should be after ORDER BY

Solution

1. Step 1: Check SQL syntax completeness

   SQL statements should end with a semicolon to mark the end.

2. Step 2: Verify other parts

   FROM keyword is present, single quotes around string are correct, WHERE comes before ORDER BY.

3. Final Answer:

   Missing semicolon at the end -> Option C

4. Quick Check:

   SQL statements end with ; [OK]

Hint: Always end SQL statements with a semicolon [OK]

Common Mistakes:

• Forgetting semicolon
• Misplacing WHERE clause
• Using double quotes instead of single quotes

5. You have a table Orders with columns OrderID, CustomerID, and Status. You want to select all orders that are either 'Pending' or 'Processing'. Which SQL query correctly implements this selection?

hard

A. SELECT * FROM Orders WHERE Status IN ('Pending', 'Processing');

B. SELECT * FROM Orders WHERE Status = 'Pending' AND 'Processing';

C. SELECT * FROM Orders WHERE Status = 'Pending' OR 'Processing';

D. SELECT * FROM Orders WHERE Status = 'Pending', 'Processing';

Solution

1. Step 1: Understand the condition for multiple values

   To select rows where Status matches multiple values, use IN or multiple OR conditions.

2. Step 2: Compare options

   IN ('Pending', 'Processing') is correct and concise. OR requires full conditions like Status = 'Pending' OR Status = 'Processing'. A lone string after OR like 'Processing' makes the condition always true, selecting extra rows. AND between values or commas cause syntax errors.

3. Final Answer:

   SELECT * FROM Orders WHERE Status IN ('Pending', 'Processing'); -> Option A

4. Quick Check:

   Use IN for multiple values in WHERE [OK]

Hint: Use IN for multiple OR conditions in WHERE [OK]

Common Mistakes:

• Using AND instead of OR
• Incorrect syntax with commas in WHERE
• Not using quotes around string values