Application
This unit describes the skills and knowledge required to produce structured query language (SQL) statements to work with server-side scripts, enabling web developers to interact with web server databases.
The unit applies to individuals in a range of roles who are responsible for creating server-side interaction with dynamic web pages, using SQL as a means of communicating with databases.
No licensing, legislative or certification requirements apply to this unit at the time of publication.
Elements and Performance Criteria
1. Identify database requirements | 1.1 Determine information required from database 1.2 Identify tables holding this information 1.3 Identify primary keys in these tables 1.4 Identify relationships between these tables including foreign keys |
2. Build and implement SQL in relational databases | 2.1 Identify and build SQL statements according to task requirements 2.2 Create tables in a database using SQL statements 2.3 Create primary and foreign keys required in database table 2.4 Manipulate data in a database using SQL statements 2.5 Query database using SQL statements 2.6 Retrieve information from database using written SQL statements |
3. Test and verify SQL results | 3.1 Construct test data and test SQL statements according to organisational procedures 3.2 Determine expected results of SQL statements 3.3 Verify result of constructed SQL statements against expected results and document findings 3.4 Confirm task requirements are met and obtain sign-off from required personnel |
Evidence of Performance
The candidate must demonstrate the ability to complete the tasks outlined in the elements, performance criteria and foundation skills of this unit, including evidence of the ability to:
apply structured query language (SQL) to a relational database and gather required information according to task requirements.
In the course of the above, the candidate must:
generate queries for one or more tables and provide required data
add, modify, retrieve and delete records from database tables as required test and verify SQL statements.
Evidence of Knowledge
The candidate must be able to demonstrate knowledge to complete the tasks outlined in the elements, performance criteria and foundation skills of this unit, including knowledge of:
organisational policies and procedures relating to SQL application including:
identifying SQL requirements from job specifications
testing and verifying SQL results
features and application of primary and foreign keys in database tables, aggregate functions and clause functions required in SQL application
principles of "combining and/or condition" in SQL statements and Boolean operators
IN and BETWEEN conditional operators
mathematical operators
table joins (relationships)
documentation techniques relevant to applying structured query language in relational databases
SQL statement testing methodologies.
Assessment Conditions
Skills in this unit must be demonstrated in a workplace or simulated environment where the conditions are typical of those in a working environment in this industry.
This includes access to:
task specification documentation
structured query language (SQL)
industry standard software packages
an integrated development environment (IDE)
a database server
required hardware, software and its component.
Assessors of this unit must satisfy the requirements for assessors in applicable vocational education and training legislation, frameworks and/or standards.
Foundation Skills
Reading | Analyses and consolidates information and data from a range of sources, against defined criteria and requirements |
Writing | Uses clear and specialised technical syntax when creating codes and interrogating databases Prepares and produces, diagrammatic models and associated documents and conveys complex relationships between data |
Self-management | Takes responsibility for planning, sequencing and prioritising tasks and own workload |
Problem-solving | Makes routine decisions and implements standard procedures for routine tasks, using formal decision-making processes for more complex and non-routine situations Addresses less predictable problems and initiates standard procedures in response to these problems, applying problem-solving processes in determining a solution |
Technology | Uses familiar digital technologies and systems to access information, search and enter, data and code, present information and communicate with others, cognisant of data security and safety |
Sectors
Web