Unit 43: Programming with SQL
Learning Outcomes
A candidate following a programme of learning leading to this unit will be able to:
• Create queries to retrieve information from relational databases using both ANSI standard and Oracle specific syntax
• Understand the application of Data Manipulation Language to insert, update and delete data held in a database
• Understand the application of Data Definition Language to create, modify and remove Tables, Views, Indexes, Synonyms and Sequences
• Understand the basics of user access control.
NB Before starting this unit please refer to the Vendor website for up-to-date specifications, examination objectives and availability.
Assessment Objectives Knowledge, Skills and Understanding 1 Create queries to retrieve information
a Describe the capabilities of SQL SELECT statements
Describe the use of SELECT statements for projection, selection and joining.
b Describe basic SELECT statements To include:
• Syntax of basic select statements • Selecting a column and/or selecting all
columns
• Mathematical and concatenation operators • Use of parenthesis
• Literal character strings • DISTINCT keyword c Describe the limiting of rows retrieved by a
query To include: • WHERE clause • Comparison operators • Logical operators • NULL condition • Operator precedence d Describe the sorting of rows retrieved by a
query
ORDER BY clause
e Describe various categories of functions available in SQL
To include the following functions: • Single row and multiple row
• Character, number, date and conversion f Describe the use of character, number, and
date functions in SELECT statements
To include functions:
• Case manipulation functions (LOWER, UPPER, INITCAP)
• Character manipulation functions (CONCAT, SUBSTR, LENGTH, INSTR, LPAD/RPAD, TRIM, REPLACE)
• Number functions (ROUND, TRUNC, MOD) • Date functions (MONTHS_BETWEEN,
ADD_MONTHS, NEXT_DAY, LAST_DAY, ROUND, TRUNC, SYSDATE)
• Date arithmetic • Default date format g Describe the use of conversion functions To include:
• Implicit data type conversion
• Explicit data type conversion (TO_CHAR, TO_NUMBER, TO_DATE)
• Elements of the date format model • Null conversions (NVL, NVL2, NULLIF) • COALLESE
Assessment Objectives Knowledge, Skills and Understanding
h Use conditional expressions To include: • CASE • DECODE i Use SELECT statements to access data
from more than one table using equality and non-equality joins
To include:
• Cartesian product
• Oracle specific syntax (equijoin, non-equijoin)
• ANSI standard syntax (CROSS JOIN, NATURAL JOIN, JOIN USING, JOIN ON) j Describe how to view data that generally
does not meet a join condition by using outer joins
To include:
• Oracle specific syntax (outer joins using (+)) • ANSI standard syntax (LEFT/RIGHT/FULL
OUTER JOIN) k Use a self join to join a table to itself
l Use the available group functions AVG, COUNT, MAX, MIN, STDDEV, SUM, VARIANCE.
m Describe how to group data GROUP BY clause. n Describe how to include or exclude grouped
rows
HAVING clause.
o Define subqueries and the types of problem that they can solve
To include:
• Subquery syntax
• Effect of NULL values in a subquery p Identify the types of subqueries To include:
• Single row subqueries • Multiple row subqueries q Use single-row and multiple-row subqueries To include:
• Single row operators
• Multiple row operators (IN, ANY, ALL)
2 Insert, update and delete data in a database
a Describe how to add a new row into a table To include:
• INSERT statement • Inserting NULL values • Explicit default values • Date values
• Copying data from another table • Inserting into a subquery
b Describe how to remove a row from a table To include:
• DELETE statement
Assessment Objectives Knowledge, Skills and Understanding
c Describe how to change the data in a table To include:
• UPDATE statement
• Using a subquery to update two columns • Updating rows based upon another table • Integrity constraint errors
d Describe how to conditionally insert or update data in a table
MERGE statement.
3 Create, modify and remove database objects
a Describe the main database objects Objects to include: • Table
• View • Sequence • Index • Synonym b Describe the creation of tables To include:
• Table and column naming restrictions • CREATE TABLE
• Referencing another users table • DEFAULT values
• Creating a table using a subquery c Describe the use of data types that can be
used when specifying column definitions
To include:
• Standard data types (VARCHAR2, CHAR, NUMBER, DATE, LONG, CLOB, BLOB, BFILE, ROWID)
• Basic date time data types (TIMESTAMP and variations, INTERVAL YEAR TO MONTH, INTERVAL DAY To SECOND) d Use statements to alter table definitions ALTER TABLE statements (ADD, MODIFY,
DROP COLUMN, SET UNUSED). e Use statements to drop, rename, and
truncate tables
To include:
• DROP TABLE • RENAME
• TRUNCATE TABLE
f Use constraints To include:
• NOT NULL • UNIQUE • PRIMARY KEY • FOREIGN KEY • CHECK
g Describe the creation and maintenance of constraints
Constraints to include:
• Table and column level definitions • Naming conventions
Assessment Objectives Knowledge, Skills and Understanding
h Describe a view • Simple and complex views
i Use statements to create, replace the definition of, and drop a view
To include:
• CREATE OR REPLACE • DROP VIEW
j Describe the retrieval of data through a view k Describe how to insert, update, and delete
data through a view
l Use and create an inline view m Use of top-n analysis
n Use statements to create and maintain use of sequences
TO include:
• CREATE SEQUENCE • ALTER SEQUENCE • DROP SEQUENCE
• NEXTVAL and CURVAL pseudocolumns o Create and maintain indexes To include:
• REATE INDEX • DROP INDEX
• Automatic and manual creation of indexes • Function based indexes
p Create private and public synonyms To include:
• CREATE SYNONYM • DROP SYNONYM
4 User access control
a Create users CREATE USER statement
b Create roles to ease set up and maintain security of the model
CREATE ROLE statement
c Grant and revoke object privileges To include:
• GRANT and REVOKE statements • system and object privileges d Describe the creation of database links
Assessment
This unit will be assessed by an electronic examination set and externally marked by Oracle. Examinations take the form of multiple choice and multiple answer objective tests.
The examination is administered on a computer at an authorised Oracle Academy.
Notes for Tutors
Knowledge for this examination may be gained through the official Oracle Academy: Introduction to Computer Science and Business course: Database Design and Programming.
