Logo of TVET
LEARNING MODULE Provider
TVET PROGRAM TITLE: Database Administration Level III
MODULE TITLE: Using Basic Structured Query Language
MODULE CODE : ICT DBA3 M04 0111
NOMINAL DURATION: 95 hrs
MODULE DESCRIPTION:
This module covers the knowledge, skills and attitude to use a basic structured query language (SQL)
to define, create and manipulate database structures and associated data in a relational database.
LEARNING OUTCOMES:
At the end of the module the trainee will be able to:
LO1 write an SQL statement to retrieve and sort data
LO2 write SQL statements that use functions
LO3 write SQL statements that use aggregation and filtering
LO4 write and execute SQL sub-queries
MODULE CONTENTS:
LO1 Write an SQL Statement to retrieve and sort data
1.1 Introduction to SQL
What is SQL?
Categories of SQL Application
1.2 Installing DBMS software
Introduction
Hardware Requirements
Operating System Requirements
1.3 Install DBMS (SQL Server)
1.4 Select statement
Literals and data types
Expressions
Comparison operators
Boolean operators
Arithmetical operators
Mathematical functions
Assigning names to result columns
The user variables and the set statement
Form clause
Where clause
Updating tables
Inserting new rows
Deleting new rows
Updating values in rows
1.5 Combining table Expressions
1.6 Combining with union
1.7 Rules for using union
1.8 Combining with intersect
1.9 Combining with Except
1.10 Keeping duplicate rows
1.11 Set operators and NULL values
1.12 Combining multiple set operators
1.13 Set operator and Theory
LO2 Write SQL statements that use functions
2.1 Introduction
2.2 Select clause and Aggregation function
2.3 Selecting all columns (*)
2.4 Expression in the select clause
2.5 Removing duplicate rows with DISTINCT when two rows are equal
2.6 Introduction to aggregation function
The count function
The MAX and MIN function
The SUM and AVERAGE function
The Variance and Standard Deviation function
LO3 Write SQL statements that use aggregation and filtering
3.1 Aggregating data by multiple columns using “group by”
3.2 Sorting aggregated data in the query output
3.3 Filtering aggregated data using the “having” clause
LO4 Write and execute SQL Queries
4.1 Single and nested queries
4.2 Sub queries
In a select list
In where clause
4.3 Operators in sub queries
Revising the IN operators
Using the ANY SOME and ALL operators
Using EXITE operator
Using having clause
4.4 Correlated sub queries
LEARNING STRATEGIES:
Lecture-discussion
Group work
Individual assignment
ASSESSMENT METHODS:
Interview/Written Test
Demonstration/Observation with Oral Questioning
ASSESSMENT CRITERIA:
LO1 Write an SQL statement to retrieve and sort data
All the data from a table is retrieved following work procedure.
Data from specific columns in a single table is retrieved.
'Order by' is used to sort query output.
Number of rows restricted is retrieved by placing criteria in the 'where' clause.
Number of rows restricted is retrieved by placing specific criteria in the select statement.
Comparison operators in the 'where' clause is used to compare numeric, character, string,
date and time data
Boolean operators is used with the correct precedence
Criteria in the 'where' clause is used to check for a range of values, to select values from a
list, and to check for values that match a pattern
SQL syntax is used to suppress duplicate values from query results
Action is taken to exclude null values from a query result
LO2 Write SQL statements that use functions
Arithmetical operators is used with the correct precedence
String functions and operators is used to obtain the required query output
Mathematical functions is used to obtain the required output, where required
Date functions are used to obtain the required output
SQL aggregate functions are used to obtain the required out put
LO3 Write SQL statements that use aggregation and filtering
'Group by' used to aggregate data by multiple columns
Aggregated data is sorted in the query output
Aggregated data is filtered using the 'having' clause
LO4 Write and execute SQL sub-queries
Single and nested sub-queries are constructed
Sub-queries are constructed that return a single row, and multiple rows
Correlated sub-queries are used to retrieve required data
Sub-queries are written that use aggregates
