Duration
3 days
OVERVIEW
One of the most important inventions in Information System is “Database”. A database management system stores, organizes and manages information within a single software application called Database. A database management system is important because it manages data efficiently and allows users to perform multiple tasks with ease. Today every other application in the market is running on some kind of Database. Even the media player software also using some kind of database to keep information about album and media files in the album.
Database management systems (DBMS) are important to businesses and organizations because they provide a highly efficient method for handling multiple types of data. Some of the information stored in database includes employee records, student information, payroll, accounting and inventory. Without database management, tasks have to be done manually and take more time. Multiple users can use the system at the same time in different ways. Applications use Structured Query Language (SQL) provided by DBMS for defining, protecting and accessing data. SQL (Structured Query Language) is a standardized programming language used for managing data in relational databases and performing various operations on the data in them. The uses of SQL include modifying database table and index structures; adding, updating and deleting rows of data; and retrieving subsets of information from within a database for transaction processing and analytics applications.
This course takes participants from zero to DBA, taking a very practice-orientated route. Participants will be creating and managing databases, learning the basics of table, fields and data. The program takes two open source databases such as MySQL and PostgreSQL to demonstrate the concepts of Database and SQL. Participants learn how to design and implement databases and how to work with foreign keys, how to develop SQL statements, transactions, views, stored programs and triggers for their databases in MYSQL and PostgreSQL. In addition, fundamentals of database administration are covered, including logging, configuration, security, backup, and recovery.
OBJECTIVES
After completing this program students will acquire the following learning competencies.
- Understand the principles of RDBMS and their languages
- Construct simple and moderately advanced database queries using SQL
- Understand the DBMS concepts such as database security, privilege system and database optimization
- Design and implement their own databases
- Work with any open source databases such as MySQL or PostgreSQL
- Use any SQL client to work with a database and run SQL statements
- Retrieve, add, update, and delete data from a database
- Work with summary queries, sub queries, data types, functions, stored procedures, triggers, events, manage transactions and locking
- Perform administrative tasks, including monitoring, configuring, logging, adding users, removing users, secure, back up, and restore a database
WHO SHOULD ATTEND
This course is aimed at an audience of Software Developers, Database Analysts, Data Architects, and System Architects.
PREREQUISITE
Experience in basic computer literacy with previous experience with command-line programs and some knowledge of database concepts is required. Some knowledge of data retrieval and reporting would be beneficial.
Introduction
- An Introduction to Relational Databases and SQL
- An Introduction to Client/Server Systems
- An Introduction to the Relational Database Model
- An Introduction to SQL and SQL-Based Systems
- The SQL Statements
- How to Use SQL From An Application Program
- SQL Client
- An Introduction to SQL Client
- SQL Client connect with Database
- SQL Client to Run SQL Statements
- Viewing Results
- Command Line Client
- How to Retrieve Data From A Single Table
- An Introduction to the SELECT Statement
- The SELECT Clause
- The WHERE Clause
- The ORDER BY Clause
- The LIMIT Clause
- How to Retrieve Data From Two Or More Tables
- Inner Joins
- Outer Joins
- Other Skills For Working With Joins
- How to Work With Unions
- How to Insert, Update, and Delete Data
- Creating Test Tables
- Inserting New Rows
- Updating Existing Rows
- Deleting Existing Rows
- Coding Summary Queries
- Aggregate Functions
- How to Group and Summarize Data
Day 2
- Coding Subqueries
- An Introduction to Subqueries
- How to Code Subqueries In the WHERE Clause
- How to Code Subqueries In Other Clauses
- Working With Complex Queries
- The Data Types
- Converting Data
- Working With Data Types
- How to Use Functions
- String Data
- Numeric Data
- Date/Time Data
- Other Functions You Should Know About
- How to Design A Database
- Design A Data Structure
- Normalize A Data Structure
- Using SQL Client For Database Design
- Creating Databases, Tables, and Indexes
- Databases
- Tables
- Indexes
- A Script That Creates A Database
- Character Sets and Collations
- Storage Engines
- How to Create Views
- An Introduction to Views
- How to Work With Views
- Language Skills For Writing Stored Programs
- An Introduction to Stored Programs
- How to Write Procedural Code
Day 3
- Transactions and Locking
- How to Work With Transactions
- How to Work With Concurrency and Locking
- How to Create Stored Procedures and Functions
- Coding Stored Procedures
- Coding Stored Functions
- How to Use Workbench With Procedures and Functions
- How to Create Triggers and Events
- An Introduction to Database Administration
- Database Administration Concepts
- Monitoring the Server
- Configuring the Server
- Working With Logging
- How to Secure a Database
- An Introduction to User Accounts
- Working With Users and Privileges
- How to use SQL Clients
- How to Backup and Restore a Database
- Strategies for Backing Up and Restoring a Database
- How to Back Up a Database
- Restoring a Database
- Importing and Exporting Data
- How to Check and Repair Tables
- Conclusion
Programme scope, delivery arrangements and suitability are confirmed during enquiry.