Computing · JC 1 · Data Representation and Databases · Semester 1

SQL: Data Manipulation Language (DML)

Using Structured Query Language to retrieve, filter, and modify data stored in databases.

• MOE Syllabus Outcomes
• MOE: Data Representation and Databases - JC1

About This Topic

SQL Data Manipulation Language (DML) teaches students to interact with relational databases using commands like SELECT, INSERT, UPDATE, and DELETE. With SELECT, they retrieve and filter data through clauses such as WHERE, ORDER BY, GROUP BY, and JOIN. INSERT adds new records, UPDATE modifies existing ones, and DELETE removes data, all while considering conditions to avoid unintended changes. These skills apply to real-world scenarios, like querying student grades or updating inventory in a school system.

This topic fits the MOE JC1 Computing curriculum in Data Representation and Databases, Semester 1. Students examine how inefficient queries degrade web application speed, for example through unnecessary data fetches or missing indexes. They also learn SQL injection risks, where malicious inputs manipulate queries, and mitigations like prepared statements. Distinguishing data mining's pattern analysis from simple retrieval sharpens analytical thinking.

Active learning excels for DML because students execute queries on live databases, observe performance metrics, and debug errors firsthand. Group debugging sessions and vulnerability simulations make abstract concepts concrete, build confidence in query writing, and reinforce secure practices through trial and reflection.

Key Questions

1. How can a poorly written SQL query impact the performance of a web application?
2. What are the security risks associated with SQL injection and how are they mitigated?
3. How does data mining differ from simple data retrieval?

Learning Objectives

• Analyze the performance impact of inefficient SQL clauses like SELECT * or missing JOIN conditions on a sample web application dataset.
• Evaluate the security vulnerabilities of SQL injection by simulating common attack vectors on a test database.
• Create complex SQL queries using JOINs, GROUP BY, and aggregate functions to answer specific business questions from a provided dataset.
• Compare and contrast data mining techniques with simple data retrieval using SQL DML commands.
• Design and implement INSERT, UPDATE, and DELETE statements with appropriate WHERE clauses to maintain data integrity in a simulated inventory system.

Before You Start

Introduction to Databases and Relational Models

Why: Students need a foundational understanding of tables, rows, columns, and primary/foreign keys to manipulate data effectively.

Basic Data Types and Structures

Why: Understanding different data types (e.g., integer, string, date) is essential for correctly inserting and querying data.

Key Vocabulary

SQL Injection	A code injection technique that exploits security vulnerabilities in an application's use of SQL, allowing attackers to interfere with the queries an application makes to its database.
Prepared Statements	A feature used in SQL to execute a SQL statement multiple times with high efficiency. It also helps prevent SQL injection attacks by separating the SQL command from the data.
Data Mining	The process of discovering patterns and insights from large datasets, often using statistical methods and machine learning, going beyond simple data retrieval.
Aggregate Functions	Functions like COUNT, SUM, AVG, MIN, and MAX that perform a calculation on a set of values and return a single value, often used with GROUP BY.

Watch Out for These Misconceptions

Common Misconception

SELECT * is efficient for any query.

What to Teach Instead

This fetches all columns, increasing load times and memory use, especially on large tables. Compare timed runs of SELECT * versus specific columns in pairs; students see real performance gaps and learn to specify only needed fields.

Common Misconception

Inputs in queries are always safe without checks.

What to Teach Instead

Unescaped inputs enable SQL injection, altering query logic. Simulate attacks in small groups on demo apps; rewriting with parameters shows protection, helping students internalize security through hands-on failure and fix.

Common Misconception

UPDATE or DELETE without WHERE affects only one row.

What to Teach Instead

These modify all matching rows without WHERE, risking data loss. Practice safe queries in chained activities; group reviews prevent errors and build habits for conditional clauses.

Active Learning Ideas

Pairs: Query Performance Duel

Pairs receive a database with sales data and tasks like finding top products. They write SELECT queries, time execution using EXPLAIN, then refine for efficiency by adding indexes or limiting columns. Pairs compare results and explain improvements to the class.

35 min·Pairs

Small Groups: SQL Injection Simulation

Provide vulnerable PHP code snippets connected to a test database. Groups craft malicious inputs to bypass logins or delete data, then rewrite code with prepared statements and PDO. Test fixes and document risks versus solutions.

45 min·Small Groups

Whole Class: Data Modification Chain

Display a shared database projection. Class suggests INSERT, UPDATE, DELETE statements step-by-step to simulate a customer order process, voting on each via polls. Execute valid ones and discuss errors as a group.

30 min·Whole Class

Individual: Query Puzzle Stations

Set up 5 stations with printed datasets and tasks requiring DML. Students rotate, write queries on worksheets, then verify against teacher keys. Collect for feedback.

40 min·Individual

Real-World Connections

• Database administrators at e-commerce companies like Shopee use SQL DML to manage product catalogs, customer orders, and inventory levels, ensuring accurate stock counts and efficient order processing.
• Financial analysts at DBS Bank write complex SQL queries to extract transaction data, calculate risk metrics, and identify fraudulent activities by analyzing patterns in large datasets.
• Web developers for government portals, such as the Singapore Land Authority's website, use SQL DML to retrieve and display property listings, manage user accounts, and update public records securely.

Assessment Ideas

Quick Check

Present students with a scenario: 'A user reports slow loading times on the product page.' Ask them to identify two specific SQL DML clauses or practices that might be causing this issue and suggest one way to optimize them.

Exit Ticket

Provide students with a small table of sample data. Ask them to write an SQL query to: 1. Find the average price of products in a specific category. 2. Update the stock quantity for a particular product ID. 3. Delete records for products that have not been sold in over a year.

Discussion Prompt

Facilitate a class discussion using the prompt: 'Imagine you are building a new online forum. What are the top three security considerations you would have regarding user input that could be used in SQL queries, and why are these important?'

Frequently Asked Questions

How does a poorly written SQL query affect web app performance?

Poor queries like SELECT * on unindexed large tables cause slow response times, high CPU usage, and timeouts in web apps. Students learn via timed executions: adding WHERE filters or indexes cuts query time from seconds to milliseconds, directly linking code to user experience in e-commerce or school portals.

What are SQL injection risks and how to mitigate them?

SQL injection lets attackers inject code via inputs, enabling data theft, deletion, or unauthorized access. Mitigation uses prepared statements and parameterized queries, binding user input separately. Classroom simulations with vulnerable code let students exploit and fix issues, embedding secure habits.

How does data mining differ from simple SQL retrieval?

Simple retrieval uses DML for specific data pulls, like SELECT with filters. Data mining analyzes patterns across datasets using aggregates, JOINs, or tools like Python. JC1 activities blend both: start with queries, then group for mining tasks to show progression from fetch to insight.

How can active learning help teach SQL DML?

Active approaches like live database querying and group challenges let students iterate queries, view errors, and measure impacts immediately. Pairs debugging inefficient code or simulating injections make performance and security tangible. This builds deeper understanding than lectures, as reflection on real outcomes strengthens query skills and problem-solving.