490

Developing SQL Queries for Oracle Database 11g

This course introduces students in the fundamentals of the language SQL using Oracle technology. Students will learn the concepts of relational databases and the SQL language to strong. The structure of the course will give students the ability to write queries over one or more tables to manipulate data in tables and create objects in the database.
In addition, students will learn how to use the function "single-row" to customize the output from a query, the results of a query to conditioneze and how to use the group function for reporting.
Participants in this course should be familiar with the use of a graphical interface (GUI) and be familiar with the concepts of data processing. The tools used will be: Oracle SQL Developer and SQL command line utility * Plus.

This course will provide participants with the ability to:

• Return data (rows and columns) of tables using the SELECT command
• Create reports with data sorted and sorted in terms of necessity
• Display data from multiple tables
• Use the DML (data manipulation language) to work on the data in the tables
• Use the DML (data manipulation language) to work on objects in the database

Course syllabus:

1. introduction
What is a database? What is the SQL Language?
Presentation of the architecture of an ORACLE database.
The physical and theoretical aspects of a relational database

Description of SQL Developer tools and structure exercises
The description of the data set used for this course

2. Return data using the SELECT command
SELECT order capabilities
Generating a report from the output of a SELECT STATEMENT
The use of mathematical expressions and NULL in a SELECT STATEMENT
Use aliases for columns
Use string concatenation operator and keyword DISTINCT
Displaying the structure of a table using the command DESCRIBE

3. limitation and sort data queried
Writing queries that contain the WHERE clause to limit the data returned
Using logical operators and comparison
The description of the previous rules for logical and comparison operators
Use of data type string in the WHERE clause
Writing queries using ORDER BY clause to sort the output of a query
Use replacement variables

4. the use of "Single-Row" to customize queries-tul an outpu
The difference between "single-row functions" and the functions of the "multiple-row"
Manipulating strings of characters using character functions
Manipulating numbers using functions: ROUND, TRUNC and
Mathematical operations with DATABASE data type
Handling DATA type using the function DATA type

5. Use the conversion functions and conditional expressions
Description of data conversion and explicit default
Use TO_NUMBER TO_CHAR functions, conversion and TO_DATE
Multiple functions (nesting functions)
Using NVL function, NULLIF and COALESCE on the data
The use of conditional expressions of type IF-THEN-ELSE proposition in a SELECT STATEMENT

6. Reporting of aggregated data using Group functions
The use of aggregation functions in select sentences to create comprehensible reports
The use of AVG, SUM, MIN, and MAX
Handling NULL values in Group functions
Creating queries that share data in groups using the GROUP BY clause
Creating queries that exclude groups using the HAVING clause

7. Display data from multiple tables
Writing sentences SELECT to access data from multiple tables
JOIN tables using standard SQL: 1999
Display data why not fulfill a condition JOIN using OUTER JOINS
Using a SELF JOIN
Creating CROSS JOIN

8. the use of GIS to solve complex queries
The use of GIS to solve a problem
Use of group function in a query
Using sub-interogarilor to return more lines
The use of operators and ALL in ABR sub queries image returns multiple lines

9. Use operators on crowd (SET operators)
Description of the operators on the crowd
Using operators to combine crowds on multiple queries in one
Using operators UNION, UNION ALL, INTERSECT and MINUS
Using the ORDER BY clause in working with crowd

10. Data Manipulation (DML)
Adding new line a table using the INSERT command
Changing the data in a database table using the UPDATE command
Use the DELETE commands and TRUNCATED
Saving or canceling changes using the COMMIT and ROLLBACK commands
Using the FOR UPDATE clause

11. Use data definition language (DDL) to create and manipulate tables and objects
Categories of objects in Oracle databases
Creating new tables using the CREATE command TABBLE
Description of data types
Constraint description
Creating a new tables using a sub-query
Altering the structure of a table. Deleting a table

12. Creating other types of objects
Creating, modifying and extracting data from a VIEW
Using data manipulation language on a VIEW
Delete a VIEW
Creating, using and modifying a sequences
Creating and dropping indexes
Creating and deleting synonyms

13. Manipulating objects using data dictionary
Data dictionary
How to use the VIEW-data dictionary of
USER_OBJECTS views and ALL_OBJECTS
Information about tables and columns
The dictionary database query with regard to any
The dictionary database query with respect to other objects. Ex: views, indexes, sequences, synonyms
Adding a comment to a table
The dictionary database query regarding guest related tables

Program: Monday-Wednesday: 09.00-17.00.