In this project, you will create a database on SALARY MANAGEMENT SYSTEM using MySQL.-

• Employee list should be kept with id, name, designation, and experience

• pay information with employee id and current pay

• Salary in hand information include employee id, CTC salary, pf deduction or any other deduction, and net pay to be delivered, as well as total employee savings.

• If there is a salary increase, it will be delivered by next year, depending on the limits.

• Deduction from monthly salary, if applicable, based on work difference and amount to be deducted.

Objectives: At the end of this MYSQL DBA Training module, you will be able to:

• Understand MySQL products, professional services and support for your operating system.

• Define a relational database management system (RDBMS) and describe its structure.

• Explain the use of SQL and MySQL with relational databases

• Define data definition language (DDL) and data manipulation language (DML)

• Understand MySQL client/server model and work with MySQL connectors.

• Install the MySQL server on Linux, start and stop MySQL server

• Interact with the server using the MySQL command-line client. 

• Log your MySQL command-line client session to a text file.

• Use MySQL Workbench to interact with the server.

Topics:

• MySQL Overview

• Basics of Relational Databases

• Entities and Relationships

• SQL Language and MySQL

• SQL data definition language and data manipulation language

• MySQL Client/Server Model

• MySQL Connectors

• Installation of the MySQL server

• Starting and stopping MySQL server

• Using the MySQL client

• MySQL Workbench

Hands On

• Install and Configure MySQL Server, Client and MySQL workbench

• Using MySQL Workbench

• Creating the ‘world’ Database & Explore the Schema

Objectives: At the end of this module, you will be able to:

• Describe database modeling

• Use primary keys and foreign keys to achieve referential integrity

• Normalize your data model

• Design your new database

• Model a database using MySQL workbench and view the structure.

• Evaluate a database design

• Explain the use of different data types in database design

• Choose the correct data types while modeling data

• Describe the considerations for selecting data types

Topics:

• Database Modeling

• Keys and Normalization

• Database Design

• Viewing and Evaluating a Database

• Data Types and Database Design

• Numeric, Temporal and String Data Types

• Character Set and Collation Support

• Data Type Considerations

• Meaning of NULL

Hands On:

• Explore an Existing Database

• Designing a New Database

• Selecting Data Types for your New Database

Objectives: At the end of this module, you will be able to:

• Create a database and add tables

• Use the SHOW CREATE TABLE statement

• Set column and table options

• Create indexes, keys, and constraints

• Delete a database.

• Create and delete a table.

• Add and remove table columns

• Modify table columns.

Topics:

• Creating a Database

• Creating a Table

• Showing How a Table Was Created

• Table Options

• Column Options

• Indexes, Keys, and Constraints

• Deleting database and tables

• Creating New Table Using an Existing Table

• Creating a Temporary Table

• Copying an Existing Table Structure

• Adding, removing and modifying table columns and indexes

Hands On:

• Creating a Database

• Deleting a Database

• Creating and Deleting a Table

• Altering Table Columns

Objectives: At the end of this module, you will be able to:

• Retrieve database data using the SELECT statement from your database.

• Use the SELECT statement clauses: FROM, DISTINCT, WHERE, ORDER BY, and LIMIT

• Create views

• Query data from application code

• Export and import a delimited file

Topics:

• The SELECT Statement

• Creating Views

• Querying Data from an Application

• Exporting and Importing a Delimited File

• Troubleshooting: Authorization Levels

• Manipulating Data and inserting records

• Replacing and updating existing records

• Deleting records

• Exporting and importing a script

• Multi Statement Transactions

• Modifying Data from an Application

Hands On:

• Executing Basic Queries

• Importing a Delimited File

• Performing Basic Queries on Imported Data

• Performing Basic Queries from a PHP Application

• Exporting Table Data for MySQL Database

• Inserting, Modifying and Deleting Data

• Exporting a Script

Objectives: At the end of this module, you will be able to:

• Explain the concept of a join

• Use the JOIN keyword to query multiple tables

• Execute inner and outer joins

• Use the UNION keyword to combine multiple queries

• Use built-in functions in MySQL

• Describe and use: string functions, Date and time functions, Numeric functions.

• Use aggregate functions with the SELECT statement

Topics:

• Querying Multiple Tables

• Joining Tables with SELECT

• Inner Joins

• Outer Joins

• Table Name Aliases

• Functions in MySQL Expressions

• Using Functions

• String Functions

• Date and time functions

• Numeric Functions

• Aggregate Functions

• Spaces in Function Names

Hands On:

• Using Joins in Queries

• Using Unions in Queries

• Using Built-In, String, and Date and Time Functions

• Using Numeric Functions

• Using Aggregate Functions