Select Your Style

Choose your layout

Color scheme

MySQL- A Practical Approach

MySQL- A Practical Approach

INR₹4,237.00 + GST

Please login to purchase the course.

Clear
SKU: cid_11984 Category: Tags: ,
About the course

This course helps you in learning MySQL, which is one of the most widely used databases in the industry. In this course, you will learn to deal with databases which are the backbone of any personal or commercial software. This course covers the fundamental concepts of a relational database management system along with the steps to create database and database tables. Then it introduces queries to insert data, update, delete and fetch the data from the tables. Merging of tables, using aggregate functions, clauses to filter and sort the data, has been covered in detail. The course mainly focuses on the practical approach and expects you to try hands-on with the exercises to get completely proficient in working with.

Credit Score  = 6+4+3+(12+4+25) = 54(Formula to calculate credit score is available here)
Learning Outcomes

After completing this course, you will be able to:

  • Understand basic concepts of how a database stores information via tables.
  • Understand SQL syntax used with MySQL.
  • Learn how to retrieve and manipulate data from one or more tables.
  • Learn how to filter data based upon multiple conditions.
  • Update and insert data into the existing tables.
  • Understand how the relationships between tables will affect the SQL.
  • Understand the advantages of stored procedures along with storing data using variables and functions.
  • Boost your hireability through innovative and independent learning.
  • Get a certificate on successful completion of the course.
Target Audience

The course can be taken by:

Students: All students who are pursuing professional graduate/post-graduate courses related to computer science or Information Technology.

Teachers/Faculties: All computer science and engineering teachers/faculties.

Professionals: All IT professionals in application development domain.

Why learn MySQL?

MySQL provides an implementation of a SQL database very well suited for small to medium web pages. The database is a free and open source with a commercial license available (MySQL is now owned by Oracle after they bought Sun). Common applications for MySQL include PHP and Java-based web applications that require a DB storage backend, e.g. Dokuwiki, Joomla, XWiki etc. Many applications that use MySQL are geared towards the LAMP stack (Linux, Apache, MySQL, PHP).

MySQL DBA skills are highly sought after in the marketplace. Prospects are bright for candidates with knowledge of Database. A survey of IT managers conducted by Information Week in December 2000 showed that the top two database management execution issues faced by companies are: ease of administration and availability of qualified administrators. So, there is a huge demand for database managers and administrators in the industry. So, it’s worth learning MySQL, if you are looking for a bright career in Database management/database administration.

Course Features
  • 24X7 Access: You can view lectures as per your own convenience.
  • Online lectures: 8 hours of online lectures with high-quality videos.
  • Hands-on practice: Includes source code files for hands-on practice.
  • Updated Quality content: Content is latest and gets updated regularly to meet the current industry demands.
Test & Evaluation

Each lecture will have a quiz containing a set of multiple choice questions. Apart from that, there will be a final test based on multiple choice questions.

Your evaluation will include the overall scores achieved in each lecture quiz and the final test.

Note:
  1. The access to the course can be extended 3 months at a time (for upto 4 times) just by sending a mail requesting for an extension to the email id in the footer.
  2. The hard copy of the certificate shall be shipped to your registered address or your college
  3. There is no soft copy of the certificate.
  4. To get access to the certificate - you need to take the online exam at the end of the course

No prerequisites

Topics to be covered
  1. What is a Database

    This chapter we will talk about what a database actually is. When people talk about a database, they mean many things. So we will define what a database really is.

  2. MySQL overview

    Learning Objectives: This chapter we re going to focus on the features of MySQL.

  3. Using MySQL Queries

    You can download the source code files by clicking on the button "Download Source"

  4. Datatypes in MySQL

    This chapter we will talk about datatypes in MySQL. Any data that is added to the database table has to have a certain datatype.

  5. Designing Databases Using MySQL

    This chapter covers the introduction to MySQL. MySQL is a very popular and robust relational database management system. This database is most widely used in big and small businesses. MySQL was developed by a Swedish company named MYSQL AB. You’ll also learn about the concepts of the database, a database is an organized collection of data and information which can be easily accessed and maintained. The chapter also covers the details of datatypes in MySQL. A datatype is defined as the type of data in which a variable can hold. In MySQL, the datatypes can be numeric, strings, date and time, bit, Boolean and enumeration. Apart from this the chapter also attempts to cover the installation instructions of MySQL including XAMPP configuring and setting up of MySQL workbench, PHPMYADMIN, normalization and principles.

    • Principles and Normalization
    • Creating Database
  6. Installation

    This chapter we will cover Setting up XAMPP, Introduction to phpmyadmin, Setting up MySQL Workbench.

    • Setting up XAMPP
    • Introduction to phpmyadmin
  7. SQL Queries

    This chapter covers the details of SQL Joins. A SQL Join clause combines the columns from two or more tables in a relational database. This creates a set which can be used as a table or as it is. There are various types of joins in MySQL, such as Inner Join, Left Join, Right Join, Full Join, Self-Join etc. The chapter also includes the aggregate functions which are there in MySQL.

    • Show Use and Describe
    • Importing data in MySQL database
    • Select Single Column
    • Select Multiple Column
    • DISTINCT and LIMIT
    • ORDER BY
    • WHERE and BETWEEN
    • AND and OR
    • IN, NOT IN and LIKE
    • AND (&&) and OR (||) again
    • Regular Expressions
    • String Functions
    • Aggregate Functions
    • GROUP BY
    • SUB Queries
    • JOINS
    • Full Text
    • INSERT INTO
    • UPDATE and DELETE
    • ALTER, RENAME and DROP
    • VIEWS
    • Concat and Count
    • Using the IN clause
    • Math and SubQueries
    • Using Group By
  8. Database Structure

    The chapter covers the details of the database structure which includes some important terms such as Applications, End user, Data Definition Language, DDL compiler, DML Compiler, Query Optimizer, Stored data manager, Data files, Compiled DML and Data Dictionary.

  9. Managing Users and Privileges

    This chapter covers the details of how to create and manage users in MySQL database, understanding users and privileges.

  10. MySQL Performance Management

    This Chapter we will learn about the performance related issues in MySQL. We will learn about monitoring the server and the system variables and viewing the logs. Then we will learn about performance tuning and denormalization.

  11. Backing up and Restoring Database

    This chapter covers the details of creating a backup and restoring the data from the database with the help of SQL queries.

  12. MySQL-Final Quiz

As a part of this program, you are required to do project work. The project specifications have been drawn in consultation with the industry. The project work has to meet these specifications and can be done in group of upto 5 members.

Write a SQL statement to: (~20 minutes each)

    • Create a simple table countries including columns country_id,country_name and region_id
    • Create a simple table countries including columns country_id,country_name and region_id which is already exists
    • Create the structure of a table dup_countries similar to countries
    • Create a duplicate copy of countries table including structure and data by name dup_countries
    • Create a table countries set a constraint NULL
    • Create a table named jobs including columns job_id, job_title, min_salary, max_salary and check whether the max_salary amount exceeding the upper limit 25000
    • Create a table named countries including columns country_id, country_name and region_id and make sure that no countries except Italy, India and China will be entered in the table
    • Create a table named job_histry including columns employee_id, start_date, end_date, job_id and department_id and make sure that the value against column end_date will be entered at the time of insertion to the format like '--/--/----'
    • Create a table named countries including columns country_id,country_name and region_id and make sure that no duplicate data against column country_id will be allowed at the time of insertion
    • Create a table named jobs including columns job_id, job_title, min_salary and max_salary, and make sure that the default value for job_title is blank and min_salary is 8000 and max_salary is NULL will be entered automatically at the time of insertion if no value assigned for the specified columns
    • Create a table named countries including columns country_id, country_name and region_id and make sure that the country_id column will be a key field which will not contain any duplicate data at the time of insertion
    • Create a table countries including columns country_id, country_name and region_id and make sure that the column country_id will be unique and store an auto incremented value
    • Create a table countries including columns country_id, country_name and region_id and make sure that the combination of columns country_id and region_id will be unique
    • Create a table job_history including columns employee_id, start_date, end_date, job_id and department_id and make sure that the employee_id column does not contain any duplicate value at the time of insertion and the foreign key column job_id contain only those values which are exists in the jobs table
    • Create a table employees including columns employee_id, first_name, last_name, email, phone_number hire_date, job_id, salary, commission, manager_id and department_id and make sure that the employee_id column does not contain any duplicate value at the time of insertion and the foreign key columns combined by department_id and manager_id columns contain only those unique combination values, which combinations are exists in the departments table.
    • Create a table employees including columns employee_id, first_name, last_name, email, phone_number hire_date, job_id, salary, commission, manager_id and department_id and make sure that the employee_id column does not contain any duplicate value at the time of insertion, and the foreign key column department_id, reference by the column department_id of departments table, can contain only those values which are exists in the departments table and another foreign key column job_id, referenced by the column job_id of jobs table, can contain only those values which are exists in the jobs table. The InnoDB Engine have been used to create the tables.
    • Create a table employees including columns employee_id, first_name, last_name, job_id, salary and make sure that the employee_id column does not contain any duplicate value at the time of insertion, and the foreign key column job_id, referenced by the column job_id of jobs table, can contain only those values which are exists in the jobs table. The InnoDB Engine have been used to create the tables. The specialty of the statement is that, The ON UPDATE CASCADE action allows you to perform cross-table update and ON DELETE RESTRICT action reject the deletion. The default action is ON DELETE RESTRICT.
    • Create a table employees including columns employee_id, first_name, last_name, job_id, salary and make sure that the employee_id column does not contain any duplicate value at the time of insertion, and the foreign key column job_id, referenced by the column job_id of jobs table, can contain only those values which are exists in the jobs table. The InnoDB Engine have been used to create the tables. The specialty of the statement is that, The ON DELETE CASCADE that lets you allow to delete records in the employees(child) table that refer to a record in the jobs(parent) table when the record in the parent table is deleted and the ON UPDATE RESTRICT actions reject any updates.
    • Create a table employees including columns employee_id, first_name, last_name, job_id, salary and make sure that the employee_id column does not contain any duplicate value at the time of insertion, and the foreign key column job_id, referenced by the column job_id of jobs table, can contain only those values which are exists in the jobs table. The InnoDB Engine have been used to create the tables. The specialty of the statement is that, The ON DELETE SET NULL action will set the foreign key column values in the child table(employees) to NULL when the record in the parent table(jobs) is deleted, with a condition that the foreign key column in the child table must accept NULL values and the ON UPDATE SET NULL action resets the values in the rows in the child table(employees) to NULL values when the rows in the parent table(jobs) are updated.
    • Create a table employees including columns employee_id, first_name, last_name, job_id, salary and make sure that the employee_id column does not contain any duplicate value at the time of insertion, and the foreign key column job_id, referenced by the column job_id of jobs table, can contain only those values which are exists in the jobs table. The InnoDB Engine have been used to create the tables. The specialty of the statement is that, The ON DELETE NO ACTION and the ON UPDATE NO ACTION actions will reject the deletion and any updates.
    • Insert a record with your own value into the table countries against each column
    • Insert one row into the table countries against the column country_id and country_name
    • Create duplicate of countries table named country_new with all structure and data
    • Insert NULL values against region_id column for a row of countries table
    • Insert rows from country_new table to countries table
    • Insert one row in jobs table to ensure that no duplicate value will be entered in the job_id column
    • Insert one row in jobs table to ensure that no duplicate value will be entered in the job_id column
    • Insert a record into the table countries to ensure that, a country_id and region_id combination will be entered once in the table
    • Insert rows into the table countries in which the value of country_id column will be unique and auto incremented
    • Insert records into the table countries to ensure that the country_id column will not contain any duplicate data and this will be automatically incremented and the column country_name will be filled up by 'N/A' if no value assigned for that column
    • Insert rows in the job_history table in which one column job_id is containing those values which are exists in job_id column of jobs table
    • Insert rows into the table employees in which a set of columns department_id and manager_id contains a unique value and that combined values must have exists into the table departments
    • Insert rows into the table employees in which a set of columns department_id and job_id contains the values which must have exists into the table departments and jobs
    • Change the email column of the employees table with 'not available' for all employees
    • Change the email and commission_pct column of the employees table with 'not available' and 0.10 for all employees
    • Change the email and commission_pct column of the employees table with 'not available' and 0.10 for those employees whose department_id is 110
    • Change the email column of the employees table with 'not available' for those employees whose department_id is 80 and gets a commission is less than .20%
    • Change the email column of the employees table with 'not available' for those employees who belongs to the 'Accounting' department
    • Change salary of employee to 8000 whose ID is 105, if the existing salary is less than 5000
    • Change job ID of employee which ID is 118, to SH_CLERK if the employee belongs to a department, which ID is 30 and the existing job ID does not start with SH
    • Increase the salary of employees under the department 40, 90 and 110 according to the company rules that, salary will be increased by 25% for the department 40, 15% for department 90 and 10% for the department 110 and the rest of the departments will remain the same
    • Increase the minimum and maximum salary of PU_CLERK by 2000 as well as the salary for those employees by 20% and commission percent by .10
    • Rename the table countries to country_new
    • Add a column region_id to the table locations.
    • Add a columns ID as the first column of the table locations.
    • Add a column region_id after state_province to the table locations.
    • Change the data type of the column country_id to integer in the table locations.
    • Drop the column city from the table locations.
    • Change the name of the column state_province to state, keeping the data type and size.
    • Add a primary key for the columns location_id in the locations table.
    • Add a primary key for a combination of columns location_id and country_id.
    • Add a foreign key on job_id column of job_history table referencing to the primary key job_id of jobs table.
    • Add a foreign key constraint named fk_job_id on job_id column of job_history table referencing to the primary key job_id of jobs table.
    • Drop the existing foreign key fk_job_id from job_history table on job_id column which is referencing to the job_id of jobs table.
    • Add an index named indx_job_id on job_id column in the table job_history.
    • Drop the index indx_job_id from job_history table.
    • Display the name (first_name, last_name) and salary for all employees whose salary is not in the range $10,000 through $15,000.
    • Display the name (first_name, last_name) and department ID of all employees in departments 30 or 100 in ascending order.
    • Display the name (first_name, last_name) and salary for all employees whose salary is not in the range $10,000 through $15,000 and are in department 30 or 100.
    • Display the name (first_name, last_name) and hire date for all employees who were hired in 1987
    • List the number of jobs available in the employees table
    • Get the total salaries payable to employees
    • Get the minimum salary from employees table
    • Get the maximum salary of an employee working as a Programmer
    • Get the highest, lowest, sum, and average salary of all employees

Please make sure that these slides are not copied from any source and it should be based on your own research / learning from the course.