Database OperationsCST 159
Prerequisites: Prerequisites: CST 133 or CST 134.
Covers the concepts of relational databases and the powerful SQL programming language. Provides the essential SQL skills that allow database operators to write queries against single and multiple tables, manipulate data in tables, and create database objects. Explains how to use single row functions to customize output, use conversion functions and conditional expressions and use group functions to report aggregated data. (30-0)
Outcomes and Objectives
Retrieve data using the SQL SELECT statement.
- Generate a report of data from the output of a basic SELECT statement.
- Use arithmetic expressions and NULL values in a SELECT statement.
- Use column aliases.
- Display the table structure.
Restrict and sort data.
- Write queries with a WHERE clause to limit the output retrieved.
- Use comparison and logical operators.
- Use character string literals in the WHERE clause.
- Sort the output retrieved from queries.
- Use substitution variables.
Use single-row functions to customize output.
- Differentiate between single row and multiple row functions.
- Manipulate strings using character functions.
- Manipulate numbers with functions.
- Manipulate dates with date functions.
- Perform arithmetic with date data.
Use conversion functions and conditional expressions.
- Use conversion functions to convert data types.
- Nest multiple functions.
- Apply functions to NULL data.
- Use conditional decision/branching logic in a SELECT statement.
Report aggregated data using group functions.
- Use aggregation functions in SELECT statements to produce meaningful reports.
- Use arithmetic functions.
- Create queries that divide the data in groups by using a GROUP BY clause.
- Create queries that exclude groups of data by using a HAVING clause.
Display data from multiple tables.
- Write SELECT statements to access data from more than one table.
- View data that does not meet a join condition by using outer joins.
- Join a table by using a self-join.
- Create cross joins.
Use sub-queries to solve queries.
- Use a sub-query to solve a problem.
- Execute single-row sub queries.
- Aggregate data results with group functions in a sub-query.
- Use multiple-row sub-queries.