IT Professional Training

Database Programming with PL/SQL

The course is designed for those students interested in both designing databases and then implementing them to a professional standard using MS SQL Server.






0 STUDENTS ENROLLED

     

    • Database Design: which covers the process of analysing multipart business scenarios and design of a data model, i.e. a theoretical illustration of an organisation’s information.
    • Programming in SQL: introduces the concepts of implementing a database design by creating a physical database using SQL, the industry-standard database programming language.

    By the end of the course students will be able to design databases based on real-world situations, create and populate databases using SQL Server Management Studio, create SQL queries to a professional level, understand the capabilities of SQL Server and its relationship with the .NET Framework and languages, and pursue independent study of the subject at second-year undergraduate level and beyond.

    You Will Learn How To:

    • Develop efficient PL/SQL programs to access Oracle databases
    • Create stored procedures and functions for maximum reuse and easy code maintenance
    • Design modular applications using packages
    • Manage data retrieval for front-end applications
    • Invoke native dynamic SQL to develop high-level abstract code
    • Improve performance with collections and bulk operations

    Hands-On Experience Includes:

    • Encapsulating data manipulation statements in stored procedures
    • Performing complex data manipulation with explicit cursors
    • Leveraging EXCEPTIONs for graceful handling of runtime errors
    • Creating triggers to handle data integrity and derivation
    • Utilising weak and strong cursor variables for dynamic SQL

    Prerequisites:

    Oracle Certified Exam

    1Z0-007=> Introduction to oracle 9i SQL

    1Z0-047=> Oracle database SQL expert

    1Z0-051=> Oracle 11g: SQL Fundamentals 1

    Introduction

    1. Data vs. information
    2. History of the database
    3. Major transformations in computing

    What is Data Modelling?

    1. Conceptual & physical models
    2. Entities, instances, attributes and identifiers
    3. Entity relationship modelling and ERDs

    Entity Relationship Diagramming

    1. Identifying relationships
    2. ER diagramming conventions
    3. Speaking ERD and drawing relationships
    4. Matrix diagrams

    Rev: OA_DB_Des_Prog_SQL_2.20.13Supertypes, Subtypes, and Business Rules

    1. Supertypes and subtypes
    2. Documenting business rules

    Working with Entity Relationships

    1. Relationship transferability
    2. Relationship types
    3. Resolving many-to-many relationships
    4. Understanding CRUD requirements

    Unique Identifiers and Normalization

    1. Artificial, composite and secondary UID
    2. Normalization and first normal form
    3. Second normal form
    4. Third normal form

    Arcs, Hierarchies, and Historical Data

    1. Arcs
    2. Hierarchies and recursive relationships
    3. Modelling historical data

    ERD Project Presentation

    1. Presentation of the ERD to the client
    2. Modelling change
    3. Modelling change time
    4. Modelling change price
    5. Adding the time element to an ERD

    Drawing Conventions and Generic Modelling

    1. Drawing conventions for readability
    2. Generic modelling

    Transforming From Conceptual Model to Physical Model

    1. Introduction to relational database concepts
    2. Basic mapping
    3. Relationship mapping
    4. Subtype mapping

    Introduction to SQL

    1. Introduction to Oracle Application Express
    2. SQL introduction: querying the database
    3. Basic modifications
    4. System development life cycle

    Project

    1. Project overview and getting started
    2. Presentation project management
    3. Final presentation components

    Presentation

    1. Creating tables for the final presentation
    2. Preparing written documentation
    3. Preparing visual materials
    4. Final presentations

    SELECT Statements and Relational Database Technology

    1. Anatomy of a SQL statement
    2. Oracle database environment
    3. Using applications
    4. Relational database technology Using the WHERE Clause
    5. Working with columns, characters, and rows
    6. Limit rows selected
    7. Comparison operators

    Restricting Rows and Introduction to Functions

    1. Logical comparisons and precedence rules
    2. Sorting rows
    3. Introduction to functions – single row functions

    Using Character, Number, and Date Functions

    1. Case and character manipulation
    2. Number functions
    3. Date functions

    Using Single Row Functions

    1. Conversion functions
    2. NULL functions
    3. Conditional expressions

    Executing Database Joins

    1. Cross joins and natural joins
    2. Join clauses
    3. Inner versus outer joins
    4. Self joins and hierarchical queries

    Working with Group Functions

    1. Review of joins
    2. GROUP functions
    3. COUNT, DISTINCT, NVL

    Using Complex SQL with Aggregated Data

    1. Using GROUP BY and HAVING clauses
    2. Using ROLLUP and CUBE operations, and GROUPING SETS
    3. Using SET operators

    Creating Subqueries

    1. Fundamentals of subqueries
    2. Single row subqueries
    3. Multiple-row subqueries
    4. Correlated subqueries

    Constructing DML Statements

    1. INSERT statements
    2. Updating column values and deleting rows
    3. DEFAULT values, MERGE, and multi-table inserts

    Working with DDL Statements

    1. Creating tables
    2. Using data types
    3. Modifying a table

    Ensuring Quality Query Results

    1. Ensuring quality query results
    2. Creating and Managing Constraints
    3. Defining NOT NULL and UNIQUE constraints
    4. PRIMARY KEY, FOREIGN KEY, and CHECK constraints
    5. Managing constraints Creating and Managing Views
    6. Creating views
    7. DML operations and views
    8. Managing views

    Working with Sequences

    1. Working with sequences
    2. Indexes and synonyms

    Fundamentals of Database Security

    1. Controlling user access
    2. Creating and revoking object privileges
    3. Regular expressions

    Understanding Database Transactions

    1. Database transactions
    2. Oracle Proprietary Join Syntax
    3. Cartesian product and the JOIN operations
    4. NONEQUIJOINS
    5. OUTER joins

    Project

    1. Testing
    2. Final project: database creation
    3. Final exam review

    Ensuring Quality Query Results – Advanced Techniques

    1. Ensuring quality query results – advanced techniques

     

    Funding Available:

    This course is FREE (SAAS part-time funding) to anyone who is on benefits or earning £25,000 or less, given that they have not used SAAS/ILA funding towards any other course/training in the last 12 months.

    Those in receipt of following benefits are automatically eligible:

    • Jobseekers allowance
    • Income support
    • Incapacity Benefit/Employment Support Allowance
    • Child Tax Credit (minimum rate)
    • Pension Credit

    Apply for course funding 

    Our Courses are fully funded by SAAS: Student Awards Agency for Scotland

    Please visit the SAAS website www.saas.gov.uk to see your eligibility.

    Download: SAAS Part Time Funding Guide, Part Time Forms

    Read: Part Time Students Eligibility Criteria, Disabled students’ allowance (DSA)


    For Students who don’t qualify for the above fundings:

    How much this course will cost?

    If for any reason you do not qualify for SAAS funding our fees are as follows for students living  in UK or the European Union:

    Database Programming

    2016-17

    Vender Neutral (can be paid in instalments)  £600+ VAT
    SQA Accredited (PDA) (can be paid in instalments)  £600

     Fees are payable in advance at the start of course unless otherwise agreed.

    We also provide flexible fee instalments to help students in paying their fee.

    There are a number of other funding opportunities available to UK and EU students to help them to pay for their studies. Contact the college for more details.

    Course Reviews

    No Reviews found for this course.

    COPYRIGHT © 15-17 IT PROFESSIONAL TRAINING ACADEMY IS A TRADING NAME OF IT PROFESSIONAL TRAINING LTD REGISTERED IN SCOTLAND UNDER REGISTRATION NUMBER SC423952. VAT REGISTRATION NO 137653694. REGISTERED ADDRESS: 263B ST. JOHN’S ROAD, CORSTORPHINE, EDINBURGH,SCOTLAND, EH12 7XD