Querying Oracle Server with SQL
This course helps you write subqueries, combine multiple queries into a single query using SET operators and report aggregated data using group functions. Learn this and more through hands-on exercises.
- Understand the basic concepts of relational databases ensure refined code by developers.
- Create reports of sorted and restricted data.
- Run data manipulation statements (DML).
- Control database access to specific objects.
- Manage schema objects.
- Manage objects with data dictionary views.
- Retrieve row and column data from tables.
- Control privileges at the object and system level.
- Create indexes and constraints; alter existing schema objects.
- Create and query external tables
Application Developers, Business Analysts, Data Warehouse Administrator, Developer, Forms Developer, PL/SQL Developer, System Analysts
- Data processing
- Familiarity with data processing concepts and techniques
Introduction to SQL Querying – This module introduces SQL as the primary querying language of Oracle. It discusses the basic structure of SQL queries, the logical flow of a SELECT statement, and introduces concepts such as predicates and set-based operations.
- List the features of Oracle Database 11g
- Discuss the basic design, theoretical, and physical aspects of a relational database
- Categorize the different types of SQL statements (queries VS statements)
- Describe the data set used by the course
- Log on to the database using SQL Developer environment
Writing SELECT Queries – This module introduces the fundamentals of the SELECT statement, focusing on queries against a single table.
- Writing Simple SELECT Statements
- Eliminating Duplicates with DISTINCT
- Using Column and Table Aliases
- Calculated columns
Sorting and Filtering Data – This module explains how to enhance queries to limit the rows they return, and to control the order in which the rows are displayed. The module also discusses how to resolve missing and unknown results.
- Sorting Data (descending and ascending order)
- Filtering Data with a WHERE Clause
- Filter operators and logic (AND / OR operators)
Grouping and Aggregating Data – This module introduces methods for grouping data within a query, aggregating the grouped data and filtering groups with HAVING. The module is designed to help the student grasp why a SELECT clause has restrictions placed upon column naming in the GROUP BY clause as well as which columns may be listed in the SELECT clause.
- Using Aggregate Functions
- Using the GROUP BY Clause
- Filtering Groups with HAVING
Using Built-In Functions – This module introduces the use of functions that are built in to SQL Server Denali, and will discuss some common usages including data type conversion, testing for logical results and nullability.
- Manipulate strings with character functions
- Manipulate numbers with the ROUND, TRUNC, and MOD functions
- Perform arithmetic with date data
- Manipulate dates with the DATE functions
- Describe implicit and explicit data type conversion
- Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
Querying Multiple Tables – This module explains how to write queries which combine data from multiple sources in SQL Server. The module introduces the use of JOINs in T-SQL queries as a mechanism for retrieving data from multiple tables.
- Understanding Joins
- Querying with Inner Joins
- Querying with Outer Joins
- Querying with Cross Joins and Self Joins
Modifying existing data (DML) – This module explains how to update and delete existing rows, as well as insert new rows to an existing table.
- Create new tables
- Insert new rows
- Update existing rows
- Delete existing rows
- How transactions work and the COMMIT/ROLLBACK commands
- על פי דרישה מועד פתיחה
- 09:00-16:30ימים ושעות
- 24 שעות אקדמיות
- בסיסירמת הקורס
- עברית/Englishשפת הדרכה