Selection operation in DBMS Theory - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select all columns from the table named 'employees'.

DBMS Theory

SELECT [1] FROM employees;

Drag options to blanks, or click blank then click option'

AALL_COLUMNS

B*

CANY

DALL

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'ALL' instead of '*'

Using 'ANY' which is not valid here

Using 'ALL_COLUMNS' which is not a SQL keyword

The asterisk (*) symbol is used in SQL to select all columns from a table.

2fill in blank

medium

Complete the code to select rows where the 'age' column is greater than 30.

DBMS Theory

SELECT * FROM employees WHERE age [1] 30;

Drag options to blanks, or click blank then click option'

A=

B<=

C<

D>

Attempts:

3 left

💡 Hint

Common Mistakes

Using '=' which selects only equal values

Using '<' which selects smaller values

Using '<=' which includes smaller or equal values

The '>' operator selects rows where the value is greater than the specified number.

3fill in blank

hard

Fix the error in the code to select employees with the last name 'Smith'.

DBMS Theory

SELECT * FROM employees WHERE last_name [1] 'Smith';

Drag options to blanks, or click blank then click option'

A=

BLIKE

C==

DIS

Attempts:

3 left

💡 Hint

Common Mistakes

Using '==' which is invalid in SQL

Using 'LIKE' without wildcards for exact match

Using 'IS' which is for NULL comparisons

In SQL, '=' is used to compare values for equality. '==' is not valid in SQL.

4fill in blank

hard

Fill both blanks to select employees with salary greater than 50000.

DBMS Theory

SELECT * FROM employees WHERE salary [1] [2];

Drag options to blanks, or click blank then click option'

A>

B50000

C40000

D<

Attempts:

3 left

💡 Hint

Common Mistakes

Using '<' instead of '>'

Using 40000 instead of 50000

Swapping the blanks

The '>' operator selects salaries greater than the number 50000.

5fill in blank

hard

Fill all three blanks to select employees with age less than 40 and department 'Sales'.

DBMS Theory

SELECT * FROM employees WHERE age [1] [2] AND department [3] 'Sales';

Drag options to blanks, or click blank then click option'

A<

B40

C=

D>

Attempts:

3 left

💡 Hint

Common Mistakes

Using '>' instead of '<' for age

Using wrong age number

Using 'LIKE' instead of '=' for department

The code filters employees younger than 40 and in the 'Sales' department using '<', '40', and '=' operators.

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