Computing · JC 1 · Databases and Data Management · 1.º Período

Structured Query Language (SQL)

Writing SQL statements to define, manipulate, and query data. Students will practice SELECT, INSERT, UPDATE, and DELETE commands with various clauses.

• MOE Syllabus Outcomes
• 9569 4.3.1 Write SQL statements to create and modify tables
• 9569 4.3.2 Write SQL queries using SELECT, WHERE, ORDER BY, and JOIN

About This Topic

SQL (Structured Query Language) is the standard language for interacting with relational databases. Students learn how to write queries to retrieve specific information, filter results, and join data from multiple tables. This moves them from designing the 'skeleton' of a database to actually working with the 'meat' of the data. SQL is a powerful tool for data analysis and is a core skill for any developer or data scientist.

In the JC1 syllabus, we also cover the security aspects of SQL, specifically how to prevent SQL injection attacks. This is a critical lesson in the importance of sanitizing user input. In Singapore's data-driven economy, being able to safely and efficiently query large datasets is a highly valued skill. Students grasp this concept faster through structured discussion and peer explanation of their query logic.

Key Questions

1. How do we retrieve specific data using the SELECT statement?
2. How can we join multiple tables in a single query?
3. What is the difference between DDL and DML commands?

Learning Objectives

• Compare the efficiency of a flat-file database versus a relational database for managing complex, interconnected data.
• Explain the fundamental purpose of a Database Management System (DBMS) in data integrity and access control.
• Justify the necessity of database systems over simple file systems when handling large, structured datasets.
• Identify key components and terminology within a relational database model, such as tables, records, and fields.

Before You Start

File Systems and Data Organization

Why: Students need a basic understanding of how data is stored in files to appreciate the advantages of structured databases.

Data Types and Structures

Why: Familiarity with basic data types (text, numbers, dates) and simple structures (like lists or arrays) is foundational for understanding database fields and records.

Key Vocabulary

Database	An organized collection of structured information, or data, typically stored electronically in a computer system. Databases are designed for efficient storage, retrieval, and management of data.
Flat-file database	A database stored in a single table or file, where all records have the same structure. This model is simple but can lead to data redundancy and is inefficient for complex relationships.
Relational database	A database that organizes data into one or more tables (relations) where related data points are linked using keys. This model reduces redundancy and allows for complex queries.
Database Management System (DBMS)	Software used to create, manage, and interact with databases. A DBMS provides tools for data definition, manipulation, and control, ensuring data integrity and security.
Record (Row)	A single entry or item within a database table, representing a complete set of related data. In a relational database, a record corresponds to a row in a table.
Field (Column)	A single piece of information within a database record, representing a specific attribute or characteristic of the data. In a relational database, a field corresponds to a column in a table.

Watch Out for These Misconceptions

Common Misconception

SQL is just for searching for data.

What to Teach Instead

SQL is also used to insert, update, and delete data (DML), as well as to define the structure of the database (DDL). A 'database lifecycle' activity helps students see the full range of SQL's capabilities.

Common Misconception

The order of clauses in a SELECT statement doesn't matter.

What to Teach Instead

SQL has a very strict syntax order (SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY). Using 'syntax puzzles' where students have to reorder scrambled query parts helps reinforce the correct structure.

Active Learning Ideas

Inquiry Circle: The SQL Detective

Students are given a database of a fictional crime scene (e.g., flight logs, phone records). They must write increasingly complex SQL queries to filter through the data and identify the 'suspect' based on a set of clues.

50 min·Small Groups

Peer Teaching: Query Optimization

Pairs are given a slow, inefficient query (e.g., using multiple nested subqueries). They must work together to rewrite it using JOINs and then explain to another pair why their version is more efficient for the database engine.

35 min·Pairs

Simulation Game: SQL Injection Role Play

One student acts as a 'Web Form' and another as a 'Database.' A third student (the 'Hacker') tries to pass a 'malicious' string that tricks the Database into revealing secret data. This demonstrates the need for input validation.

30 min·Small Groups

Real-World Connections

• E-commerce platforms like Shopee and Lazada use relational databases to manage vast inventories, customer orders, and user accounts. This allows them to efficiently process millions of transactions daily and provide personalized recommendations.
• Libraries worldwide, such as the National Library Board in Singapore, employ database systems to catalog books, manage borrower information, and track loan histories. This ensures quick access to resources and efficient library operations.
• Airlines like Singapore Airlines utilize complex database systems to manage flight schedules, passenger bookings, and crew assignments. This enables real-time updates and efficient coordination across their global operations.

Assessment Ideas

Discussion Prompt

Pose the scenario: 'Imagine you are managing a school's student records using only individual text files for each student. What problems would you encounter when trying to find all students living in a specific postal code or list all students taking a particular subject?' Facilitate a class discussion comparing these issues to the benefits of a database.

Quick Check

Provide students with a simple scenario, e.g., managing a small collection of books. Ask them to draw a simple table structure for a flat-file database and then a relational database structure (e.g., one table for books, one for authors). Have them identify at least two fields and two records for each structure.

Exit Ticket

On a slip of paper, ask students to write: 1. One reason why a relational database is better than a flat-file database for managing student information. 2. The role of a DBMS in ensuring data is accurate and accessible.

Frequently Asked Questions

What is the difference between a WHERE clause and a HAVING clause?

The WHERE clause is used to filter individual rows before any grouping occurs. The HAVING clause is used to filter groups of data after the GROUP BY clause has been applied. It is usually used with aggregate functions like SUM or COUNT.

How do I explain a LEFT JOIN simply?

Use a Venn diagram. A LEFT JOIN takes all the records from the 'left' table and only the matching records from the 'right' table. If there is no match, the result will show 'NULL' for the right side. It's like a guest list where everyone invited shows up, but only some bring a plus-one.

How can active learning help students understand SQL?

Active learning through 'Live Querying' sessions, where students see the results of their code in real-time, is very effective. Even better is 'Peer Debugging,' where students have to find the error in a classmate's complex JOIN statement. This forces them to read SQL like a computer would, which builds a much stronger understanding of the logic flow.

What is SQL injection and why is it dangerous?

SQL injection is a type of cyberattack where a hacker inserts malicious SQL code into an input field (like a login box). If the program isn't careful, it might execute that code, allowing the hacker to steal data or delete the entire database. It is one of the most common and dangerous web vulnerabilities.