|
Provided by: Serebra Learning Corporation Database Technologies Part 2: Introduction to SQLSQL |
 |
The second in a two-part series this course will teach students the fundamental skills and knowledge needed to acquire a foundation-level understanding of SQL. Building on the basic relational database skills and knowledge acquired in the first course this course teaches students the essential SQL statements and associated variations needed to perform fundamental data access and manipulation on a relational database. After taking this course students will have a solid foundation in two basic aspects of SQL: Data Definition Language (DDL - creating databases and tables) and Data Manipulation Language (DML - adding altering and querying data).
|
|
||||||||||||||
Audience
Associate database administrators technical support consultants and associate programmer/analysts are some of the types of people who would benefit from this course. This course is intended for people who are relatively new to the world of corporate IT and databases. They will most likely either be new to the workforce or have recently been assigned new duties that require them to learn basic SQL and relational database skills. Students should have at least limited exposure to databases and a basic familiarity with programming concepts. In addition learners should have taken the first part in this series Database Technologies Part 1: Relational Database Fundamentals (course 13494).
Objective
- Identify what SQL is why and where it is used.
- Identify how to retrieve data from a database.
- Identify how to order limit and group their query results using various SQL clauses keywords and operators.
- Identify how to use arithmetic operators single row functions and group functions to manipulate query data.
- Identify how to retrieve database information using subqueries and joins.
- Identify the guidelines for naming tables as well as the datatypes and constraints available for table creation.
- Identify how to create alter and delete database tables.
- Identify the data manipulation commands (INSERT UPDATE and DELETE) as well as the transaction control commands (COMMIT and ROLLBACK).
- Identify the general overview of views and indexes including how to create these database elements.
Topics Include
Unit 1: SQL: An Introduction
- Identify the statements that define SQL.
- Match the phase in the evolution of the SQL standard with its description.
- Match the SQL component with its definition.
- Match the organization with the description of how it uses SQL.
- Match the SQL environment with its description.
Unit 2: Querying Tables
- Query a database using a SELECT statement.
- Eliminate duplicate rows in a query result using the DISTINCT keyword in a SELECT statement.
- Assign a column alias in a query result using the AS keyword.
- Join two columns in a query using the concatenation operator.
- Sort query results using the ORDER BY clause.
- Reverse the default order in which rows are displayed using the ORDER BY clause and the DESC keyword.
- Restrict the rows returned from a query using the WHERE clause.
- Match the relationship operator with its function.
- Match the SQL operator with its function.
- Match the negative relationship operator with its description.
- Query data with multiple conditions using the AND operator.
- Group query results using the GROUP BY clause.
- Restrict data displayed in query results using the HAVING clause with the GROUP BY clause.
Unit 3: Performing Data Computations
- Identify the rules of precedence used for arithmetic operators in SQL.
- Perform a query using an arithmetic operator.
- Convert the case of an alpha character string using the UPPER function.
- Extract a portion of a string using the SUBSTR function.
- Round the numeric values in a column to a given decimal place using the ROUND function.
- Truncate the numeric values in a column to a given decimal place using the TRUNC function.
- Count the number of rows in a table including duplicate rows and rows containing null values using the COUNT function.
- Add the values for each row in a column using the SUM function.
- Compute the average value in a column using the AVG function.
- Return the minimum value in a column using the MIN function.
- Return the maximum value in a column using the MAX function.
Unit 4: Retrieving Data
- Perform a single row subquery using a comparison operator.
- Perform a multiple row subquery using a SQL operator.
- Display data from two related tables using an equijoin.
- Display data from the same table using a self join.
- Display data that does not satisfy a join condition using an outer join.
Unit 5: Creating Tables
- Identify the guidelines for naming database tables.
- Identify the common column datatype categories.
- Match each data integrity constraint with its description.
- Add a table to the database using the CREATE TABLE statement.
- Add a column to a table using the ALTER TABLE statement with the ADD clause.
- Modify a column in a table using the ALTER TABLE statement with the MODIFY clause.
- Delete the primary key from a table using the ALTER TABLE statement with the DROP clause.
- Drop a table from the database using the DROP TABLE statement.
Unit 6: Manipulating Data
- Add a row to a table using the INSERT statement and the VALUES keyword.
- Copy rows from an existing table to a new table using the INSERT statement with a SELECT statement subquery.
- Modify existing table rows using the UPDATE statement.
- Delete rows from a table using the DELETE statement.
- Identify the actions that end a transaction.
- End a transaction and save all pending data changes using the COMMIT command.
- End a transaction and discard all pending data changes using the ROLLBACK command.
Unit 7: Incorporating Views and Indexes
- Identify the characteristics of a view.
- Create a view using the CREATE VIEW statement.
- Query all columns in a view using a SELECT statement.
- Identify the statements that define an index.
- Create an index using the CREATE INDEX statement.
Duration
8
Minimum Requirements
The CDROM version of this course requires:
- At least a 486DX 33Mhz CPU.
- Microsoft Windows 3.1 or higher and a Microsoft compatible mouse.
- At least 8MB RAM.
- At least VGA graphics capability with a minimum 512K video RAM (1MB video RAM recommended).
- At least a double speed CDROM drive.
- An MPC compliant sound card with attached speakers or headphones is recommended (Currently only the CDROM version supports audio).
- At least a 486DX 33Mhz CPU.
- Microsoft Windows 3.1 or higher and a Microsoft compatible mouse.
- At least 8MB RAM and 14MB available hard disk space or file server space.
- At least VGA graphics capability with a minimum 512K video RAM (1MB video RAM recommended).
Media
Serebra Learning Corporation 119 - 7565 132nd Street Surrey BC V3W 1K5 Canada
