Develop software using SQL
This topic covers developing software using SQL, including querying, manipulating data, and managing database objects. Learners will learn to control user access and work with multiple tables.
Assessment criteria
Topic Overview
The City & Guilds Level 2 Award in ICT Systems and Principles provides a foundational understanding of information and communication technology, covering hardware, software, networks, and data management. This qualification is designed for students who want to develop practical skills and theoretical knowledge essential for entry-level IT roles or further study. It aligns with the UK's digital skills agenda, preparing learners for the modern workplace where ICT competence is increasingly vital.
The course explores how computer systems operate, from basic components like CPUs and memory to operating systems and application software. Students learn about network fundamentals, including LANs, WANs, and internet protocols, as well as data security and ethical considerations. By the end of the award, learners can troubleshoot common ICT issues, understand system maintenance, and apply principles of data protection, making them valuable assets in any tech-driven environment.
This qualification fits into the broader ICT curriculum by bridging basic digital literacy with more advanced concepts in computing. It is ideal for those pursuing apprenticeships, further qualifications like the Level 3 Diploma, or careers in IT support, network administration, or cybersecurity. The practical assessments ensure students can apply theory to real-world scenarios, enhancing employability and confidence in using technology.
Key Concepts
Core ideas you must understand for this topic
- →Hardware components: Understand the function of the CPU, RAM, storage devices (HDD/SSD), motherboard, and peripherals, and how they interact within a computer system.
- →Software types: Differentiate between system software (operating systems, drivers) and application software (word processors, spreadsheets), and their roles in managing hardware and performing tasks.
- →Networking fundamentals: Know the difference between LAN and WAN, IP addressing, DNS, and common protocols like TCP/IP, HTTP, and FTP. Understand how data is transmitted across networks.
- →Data security: Grasp principles of confidentiality, integrity, and availability (CIA triad), along with methods like encryption, firewalls, and password policies to protect data.
- →System maintenance: Learn routine tasks such as disk cleanup, defragmentation, software updates, and antivirus scans to keep systems running efficiently and securely.
Learning Objectives
What you need to know and understand
- know how to Query and display data from a single table, Query and display data from multiple tables, Manipulate data in tables, Create and manage tables, views and indexes, Control user access
- know how to Query and display data from a single table, Query and display data from multiple tables, Manipulate data in tables, Create and manage tables, views and indexes, Control user access
- know how to Query and display data from a single table, Query and display data from multiple tables, Manipulate data in tables, Create and manage tables, views and indexes, Control user access
- know how to Query and display data from a single table, Query and display data from multiple tables, Manipulate data in tables, Create and manage tables, views and indexes, Control user access
- know how to Query and display data from a single table, Query and display data from multiple tables, Manipulate data in tables, Create and manage tables, views and indexes, Control user access
Assessment Criteria
Key criteria assessors look for in your portfolio
- Write SQL queries to retrieve data from single and multiple tables.
- Use JOINs to combine data from multiple tables.
- Manipulate data using INSERT, UPDATE, DELETE.
- Create and manage tables, views, and indexes.
- Control user access with GRANT and REVOKE.
- Award credit for demonstrating the ability to construct SELECT statements with appropriate filtering, sorting, and aggregation on a single table.
- Expect evidence of using various JOIN types (INNER, LEFT, RIGHT) correctly to combine data from multiple tables without Cartesian products.
- Credit should be given for accurate use of INSERT, UPDATE, and DELETE statements, including the use of subqueries and maintaining referential integrity.
- Look for creation of tables with appropriate data types and constraints, use of views to simplify complex queries, and management of indexes to optimize performance.
- Award marks for implementing user access control using GRANT and REVOKE statements, demonstrating an understanding of principle of least privilege.
- Write SELECT queries with WHERE, JOIN, and GROUP BY clauses.
- Use INSERT, UPDATE, and DELETE statements correctly.
- Create tables with appropriate data types and constraints.
- Create views and indexes to optimise data access.
- Grant and revoke user permissions.
- Write SELECT queries with WHERE, JOIN, and GROUP BY clauses.
- Use INSERT, UPDATE, DELETE to manipulate data.
- Create and manage tables, views, and indexes.
- Implement user permissions and security controls.
- Write SELECT queries to retrieve data from single and multiple tables.
- Use INSERT, UPDATE, DELETE to manipulate data.
- Create and manage database objects like tables and views.
- Implement user access controls and permissions.
Assessment Guidance
Guidance for achieving higher grades
- 💡Practice writing queries with sample databases.
- 💡Understand the order of SQL clauses (SELECT, FROM, WHERE, etc.).
- 💡Use aliases to simplify complex queries.
- 💡Always verify query output matches the expected results by testing with known data sets before final submission.
- 💡Include clear comments in your SQL scripts to explain the logic, which can earn additional credit for demonstrating understanding.
- 💡Practice using alias names for tables and columns to make multi-table queries more readable and less error-prone.
- 💡When tasked with data manipulation, first perform SELECT statements with the same conditions to confirm the scope of affected rows.
- 💡For user access tasks, document the rationale behind assigned permissions to show an understanding of security principles.
- 💡Practise writing queries on sample databases.
- 💡Learn the order of execution of SQL clauses.
- 💡Remember that indexes speed up queries but slow down updates.
- 💡Practice writing queries with sample databases.
- 💡Understand the order of SQL clause execution.
- 💡Know the difference between DDL, DML, and DCL.
- 💡Practice writing queries on a sample database.
- 💡Understand the order of SQL clauses.
- 💡Use aliases to simplify complex queries.
- 💡Use specific terminology: In exams, always use correct technical terms (e.g., 'CPU' not 'processor box') and define acronyms when first used. This shows depth of knowledge and can earn you marks for precision.
- 💡Show practical understanding: When answering questions about troubleshooting or maintenance, describe step-by-step processes and justify your choices. For example, explain why you would run a disk cleanup before defragmentation.
- 💡Link concepts to real-world scenarios: If asked about network security, mention examples like a school network using a firewall to block unauthorised access. This demonstrates application of theory.
Common Mistakes
Common errors to avoid in your coursework
- Forgetting to use WHERE clause in UPDATE/DELETE.
- Confusing INNER JOIN with OUTER JOIN.
- Not normalising tables properly.
- Confusing INNER JOIN with OUTER JOIN, leading to unintended omission of non-matching rows.
- Omitting the WHERE clause in UPDATE or DELETE statements, causing accidental modification or removal of all rows.
- Misunderstanding the difference between DDL and DML commands, resulting in syntax errors or unintended schema changes.
- Overlooking the need to handle NULL values in query conditions, producing incorrect result sets.
- Creating views without considering underlying query performance, which can cause slow data retrieval in large databases.
- Forgetting to use JOIN conditions leading to Cartesian products.
- Using DELETE without a WHERE clause accidentally.
- Misunderstanding the difference between CHAR and VARCHAR.
- Forgetting to use JOIN conditions correctly.
- Omitting WHERE clause in UPDATE/DELETE statements.
- Creating indexes without considering performance impact.
- Forgetting to use JOIN conditions correctly.
- Not handling NULL values appropriately.
- Failing to test queries with sample data.
- Misconception: 'RAM is the same as storage.' Correction: RAM is volatile memory used for temporary data while the computer is on; storage (HDD/SSD) retains data permanently. Confusing them leads to misunderstanding system performance.
- Misconception: 'The internet and the World Wide Web are the same.' Correction: The internet is a global network of computers; the Web is a service that uses the internet to share web pages. Other services like email also use the internet.
- Misconception: 'Antivirus software guarantees complete safety.' Correction: Antivirus is essential but not foolproof; it must be combined with safe browsing habits, regular updates, and firewalls for robust security.
Frequently Asked Questions
Common questions students ask about this topic
Pass / Merit / Distinction Evidence Checklist
How your portfolio evidence is graded for CITY & GUILDS LIMITED Develop software using SQL
Every vocational unit is marked against named criteria rather than an exam percentage. Your tutor's brief lists the exact codes for this unit — here is what each band is asking you to do.
Demonstrate baseline knowledge, accurate terminology, and core practical application.
Provide detailed analysis, structured explanations, and clear workplace reasoning.
Deliver thorough evaluation, original problem solving, and fully justified recommendations.
Before You Start
Prior knowledge that will help with this topic
- •Basic digital literacy: Familiarity with using a computer, managing files, and browsing the internet is assumed. This includes knowing how to save, open, and organise documents.
- •Understanding of simple mathematical concepts: Basic arithmetic and logical thinking help with topics like binary numbers, data sizes (KB, MB, GB), and network addressing.
- •No formal prior qualification is required, but a keen interest in how technology works will greatly aid comprehension.
Coursework AI Review
Paste your assignment brief and check your draft against its P/M/D criteria
Key Terminology
Essential terms to know
- know how to Query and display data from a single table, Query and display data from multiple tables, Manipulate data in tables, Create and manage tables, views and indexes, Control user access
- know how to Query and display data from a single table, Query and display data from multiple tables, Manipulate data in tables, Create and manage tables, views and indexes, Control user access
- know how to Query and display data from a single table, Query and display data from multiple tables, Manipulate data in tables, Create and manage tables, views and indexes, Control user access
- know how to Query and display data from a single table, Query and display data from multiple tables, Manipulate data in tables, Create and manage tables, views and indexes, Control user access
- know how to Query and display data from a single table, Query and display data from multiple tables, Manipulate data in tables, Create and manage tables, views and indexes, Control user access
Ready to learn?
AI-powered learning tailored to this unit