SQL Problem Solving for Information Processing Engineer Exam
SQLInformation Processing EngineerPractical ExamDatabaseProblem Solving
Read in about 7 min read
Published: 2025-07-09
Last modified: 2025-07-09
View count: 112
Summary
Learn basic SQL syntax like INSERT, UPDATE, etc., by solving SQL problems for the Information Processing Engineer practical exam step-by-step. Includes actual exam question types and answer formats.
This guide explains the query for each situation, followed by a problem. At the very end, there is a review section with all the problems to solve again.
Get a feel for SQL by looking at the problems.
📋 Table of Contents
Expand Table of Contents
- ➕ When you need to add data to a table
- ➖ When you need to delete table data
- ✏️ When you need to modify table data
- 🗑️ When removing a table
- 🔑 Revoking permissions
- 🖼️ Creating a view
- 📉 Sorting two columns in descending order
- 📇 Creating an index
- 🎯 Querying data with values within a specific range
- 🔍 Querying data that starts with a specific character
- 🤔 Querying data with exactly one character before a specific character
- 📊 Querying with a condition on the sum
- ❓ Querying NULL value data
- 🏗️ Altering table structure
- 🔄 Using a subquery
- 🔢 Counting the number of data
- 🔃 Review Problems
➕ When you need to add data to a table
sql
INSERT INTO table_name (column1, column2, column3, ... : optional)
VALUES (value1, value2, value3, ...);| Problem | Fill in the blank. SQL statement to add new employee information to the Employee table. INSERT INTO Employee(EmployeeID, Name, Age) _____ (100,'John Doe',20); |
| Your Answer | |
| Correct Answer | Reveal Answer |
➖ When you need to delete table data
sql
DELETE FROM table_name
WHERE condition;| Problem | Fill in the blank. SQL statement to delete an employee who has resigned. DELETE _____ Employee WHERE EmployeeID = 100; |
| Your Answer | |
| Correct Answer | Reveal Answer |
✏️ When you need to modify table data
sql
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;| Problem | Fill in the blank. SQL statement to modify the address of a department in the Employee table. UPDATE Employee _____ Address = 'Seoul' WHERE Department = 'Development'; |
| Your Answer | |
| Correct Answer | Reveal Answer |
🗑️ When removing a table
If the table is not referenced, it's deleted; if it is referenced, deletion fails
sql
DROP TABLE table_name RESTRICT;| Problem | Write a DDL statement to delete the Employee table only if it is not referenced by another table. |
| Your Answer | |
| Correct Answer | Reveal Answer |
Deletes the table and any referencing tables in a cascade
sql
DROP TABLE table_name CASCADE;| Problem | Fill in the parentheses with the correct option. You are trying to DROP a table that has a foreign key constraint. To remove the table and all referencing tables in a cascade, use the ( 1 ) option. To prevent deletion if the table is being referenced, use the ( 2 ) option. |
| Your Answer | |
| Correct Answer | Reveal Answer |
| Problem | Write a DDL statement to remove the Company table and all data that references it. |
| Your Answer | |
| Correct Answer | Reveal Answer |
🔑 Revoking permissions
sql
REVOKE privilege_type ON TABLE_NAME FROM user_name;| Problem | Write an SQL statement for an administrator to revoke the UPDATE permission on the Employee table from a USER. |
| Your Answer | |
| Correct Answer | Reveal Answer |
🖼️ Creating a view
sql
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;| Problem | You want to create a view named 'DepartmentView' using the Company table. Fill in the blank. CREATE VIEW DepartmentView _____ SELECT DepartmentName, DepartmentLocation FROM Company; |
| Your Answer | |
| Correct Answer | Reveal Answer |
📉 Sorting two columns in descending order
To sort by column1 in descending order first, and then by column2 in descending order...
sql
SELECT column1, column2, ...
FROM table_name
ORDER BY column1 DESC, column2 DESC;| Problem | Write an SQL statement to sort the data in the Company table by DepartmentLocation and DepartmentName in descending order. (Sort by DepartmentLocation descending first, and if locations are the same, then by DepartmentName descending) (Select all columns) |
| Your Answer | |
| Correct Answer | Reveal Answer |
📇 Creating an index
sql
CREATE INDEX index_name ON table_name (column1, column2, ...);| Problem | Write an SQL statement to create an index named 'DepartmentIndex' on the DepartmentName and DepartmentLocation columns of the Company table. |
| Your Answer | |
| Correct Answer | Reveal Answer |
🎯 Querying data with values within a specific range
BETWEEN
sql
SELECT column1, column2, ...
FROM table_name
WHERE column1 BETWEEN value1 AND value2;| Problem | Write an SQL statement to query departments from the Company table where the DepartmentID is between 1 and 3. (Select all columns) |
| Your Answer | |
| Correct Answer | Reveal Answer |
IN
sql
SELECT column1, column2, ...
FROM table_name
WHERE column1 IN (value1, value2, ...);| Problem | Write an SQL statement to query departments from the Company table where the DepartmentName is 'Development', 'HR', or 'General Affairs'. (Select all columns) |
| Your Answer | |
| Correct Answer | Reveal Answer |
🔍 Querying data that starts with a specific character
sql
SELECT column1, column2, ...
FROM table_name
WHERE column1 LIKE 'value%';| Problem | Write an SQL statement to query departments from the Company table that start with 'Dev'. (Select all columns) |
| Your Answer | |
| Correct Answer | Reveal Answer |
🤔 Querying data with exactly one character before a specific character
sql
SELECT column1, column2, ...
FROM table_name
WHERE column1 LIKE '_value';| Problem | Write an SQL statement to query departments from the Company table where the name has exactly one character before 'Dept'. (Select all columns) |
| Your Answer | |
| Correct Answer | Reveal Answer |
📊 Querying with a condition on the sum
sql
SELECT column2, SUM(column1) AS total
FROM table_name
WHERE condition;
GROUP BY column2;
HAVING SUM(column1) condition;| Problem | Write an SQL statement to query the department name and the total number of employees for departments with 10 or more employees. (Company table columns: DepartmentName, NumberOfEmployees) |
| Your Answer | |
| Correct Answer | Reveal Answer |
❓ Querying NULL value data
Used to find records where a specific column's value is NULL.
sql
SELECT column1, column2, ...
FROM table_name
WHERE column_name IS NULL;| Problem | Write an SQL statement to query the names of employees from the 'Employee' table who have not yet been assigned a department (DepartmentCode is NULL). (Employee table columns: Name, DepartmentCode) |
| Your Answer | |
| Correct Answer | Reveal Answer |
🏗️ Altering table structure
Used to add a new column to an existing table.
sql
ALTER TABLE table_name
ADD column_name datatype;| Problem | Write an SQL statement to add a 'Contact' column of type VARCHAR(20) to the 'Employee' table. |
| Your Answer | |
| Correct Answer | Reveal Answer |
🔄 Using a subquery
A query nested inside another SQL statement, useful for handling complex conditions.
sql
SELECT column1, column2, ...
FROM table_name
WHERE column_name IN (SELECT column_name FROM another_table WHERE condition);| Problem | Write an SQL statement using a subquery to retrieve the names of all employees from the 'Employee' table who work in a department located in 'Seoul'. (Department table columns: DepartmentCode, DepartmentName, Location) (Employee table columns: EmployeeID, Name, DepartmentCode) |
| Your Answer | |
| Correct Answer | Reveal Answer |
🔢 Counting the number of data
Used to count the number of rows that satisfy a specific condition.
sql
SELECT COUNT(column_name)
FROM table_name
WHERE condition;| Problem | Write an SQL statement to calculate the total number of employees in the 'Development' department from the 'Employee' table. |
| Your Answer | |
| Correct Answer | Reveal Answer |
🔃 Review Problems
| Problem | Write an SQL statement to query departments from the Company table where the name has exactly one character before 'Dept'. (Select all columns) |
| Your Answer | |
| Correct Answer | Reveal Answer |
| Problem | Fill in the blank. SQL statement to modify the address of a department in the Employee table. UPDATE Employee _____ Address = 'Seoul' WHERE Department = 'Development'; |
| Your Answer | |
| Correct Answer | Reveal Answer |
| Problem | Write an SQL statement to query departments from the Company table where the DepartmentID is between 1 and 3. (Select all columns) |
| Your Answer | |
| Correct Answer | Reveal Answer |
| Problem | Write an SQL statement to calculate the total number of employees in the 'Development' department from the 'Employee' table. (Employee table columns: Name, Department) |
| Your Answer | |
| Correct Answer | Reveal Answer |
| Problem | Write an SQL statement for an administrator to revoke the UPDATE permission on the Employee table from a USER. |
| Your Answer | |
| Correct Answer | Reveal Answer |
| Problem | Write an SQL statement to query departments from the Company table where the DepartmentName is 'Development', 'HR', or 'General Affairs'. (Select all columns) |
| Your Answer | |
| Correct Answer | Reveal Answer |
| Problem | You want to create a view named 'DepartmentView' using the Company table. Fill in the blank. CREATE VIEW DepartmentView _____ SELECT DepartmentName, DepartmentLocation FROM Company; |
| Your Answer | |
| Correct Answer | Reveal Answer |
| Problem | Fill in the blank. SQL statement to add new employee information to the Employee table. INSERT INTO Employee(EmployeeID, Name, Age) _____ (100,'John Doe',20); |
| Your Answer | |
| Correct Answer | Reveal Answer |
| Problem | Write an SQL statement to query the department name and the total number of employees for departments with 10 or more employees. (Company table columns: DepartmentName, NumberOfEmployees) |
| Your Answer | |
| Correct Answer | Reveal Answer |
| Problem | Write an SQL statement using a subquery to retrieve the names of all employees from the 'Employee' table who work in a department located in 'Seoul'. (Department table columns: DepartmentCode, DepartmentName, Location) (Employee table columns: EmployeeID, Name, DepartmentCode) |
| Your Answer | |
| Correct Answer | Reveal Answer |
| Problem | Write a DDL statement to remove the Company table and all data that references it. |
| Your Answer | |
| Correct Answer | Reveal Answer |
| Problem | Write an SQL statement to query departments from the Company table that start with 'Dev'. (Select all columns) |
| Your Answer | |
| Correct Answer | Reveal Answer |
| Problem | Write a DDL statement to delete the Employee table only if it is not referenced by another table. |
| Your Answer | |
| Correct Answer | Reveal Answer |
| Problem | Write an SQL statement to query the names of employees from the 'Employee' table who have not yet been assigned a department (DepartmentCode is NULL). (Employee table columns: Name, DepartmentCode) |
| Your Answer | |
| Correct Answer | Reveal Answer |
| Problem | Write an SQL statement to create an index named 'DepartmentIndex' on the DepartmentName and DepartmentLocation columns of the Company table. |
| Your Answer | |
| Correct Answer | Reveal Answer |
| Problem | Fill in the blank. SQL statement to delete an employee who has resigned. DELETE _____ Employee WHERE EmployeeID = 100; |
| Your Answer | |
| Correct Answer | Reveal Answer |
| Problem | Fill in the parentheses with the correct option. You are trying to DROP a table that has a foreign key constraint. To remove the table and all referencing tables in a cascade, use the ( 1 ) option. To prevent deletion if the table is being referenced, use the ( 2 ) option. |
| Your Answer | |
| Correct Answer | Reveal Answer |
| Problem | Write an SQL statement to sort the data in the Company table by DepartmentLocation and DepartmentName in descending order. (Sort by DepartmentLocation descending first, and if locations are the same, then by DepartmentName descending) (Select all columns) |
| Your Answer | |
| Correct Answer | Reveal Answer |
| Problem | Write an SQL statement to add a 'Contact' column of type VARCHAR(20) to the 'Employee' table. |
| Your Answer | |
| Correct Answer | Reveal Answer |