0345 4506120

Introduction to MySQL

Course Details

Name Introduction to MySQL
Description Introduction to MySQL Course Overview The Introduction to MySQL course covers the basics of the SQL language as implemented by MySQL. The course is designed to give delegates practical experience in writing SQL statements using the MySQL client program and MySQL Workbench. The basic SQL statements, including the use of SQL functions and the basic table and view handling statements are introduced. Skills Gained The delegates will practise: Using client programs to access a MySQL database Creating, altering and dropping a MySQL database Writing SQL statements to select data from a MySQL database Writing joins and subqueries Using SQL expressions Using SQL aggregate functions and scalar functions Inserting, updating, deleting and replacing rows Creating and altering tables, indexes and views Using transactions Granting and revoking access privileges on tables and views Exporting and importing data Who will the Course Benefit? Anyone who needs to access and work with a MySQL Database. Course Objectives To provide the skills needed to create and alter a MySQL database and query and modify data held within such a database. Examinations This Introduction to MySQL course covers the topics required for the 1Z0-870 MySQL 5 Certified Associate Exam (1Z0-870). Together with the MySQL for Developers course, this course covers the topics required for the MySQL 5 Developer Certified Professional Exam, Part I (1Z0-871) and MySQL 5 Developer Certified Professional Exam, Part II (1Z0-872). Together with the MySQL Database Administration course, this Introduction to MySQL course covers the topics required for the MySQL 5 Database Administrator Certified Professional Exam, Part I (1Z0-873) and MySQL 5 Database Administrator Certified Professional Exam, Part II (1Z0-874). Before taking any exam, ensure you have the recommended experience. The Oracle website lists all exam requirements and these are updated regularly. Exams are not included as part of the course. Requirements There are no formal pre-requisites, although an understanding of databases and exposure to information technology in general would be useful. Follow-On Courses MySQL for Developers MySQL Database Administration PHP Programming & MySQL for Web Development
URL
Location:
Manchester
Start Date:
Working Days:
Price:
£950.00 +vat
Availability:
Exam:
Residential:
Course ID:
495542

Overview

The Introduction to MySQL course covers the basics of the SQL language as implemented by MySQL.  The course is designed  to give delegates practical experience in writing SQL statements using the MySQL client program and MySQL Workbench.  The basic  SQL statements, including the use of SQL functions and the basic table and view handling statements are introduced.

Who will the Course Benefit?

Anyone who needs to access and work with a MySQL Database.

Learning Objectives

To provide the skills needed to create and alter a MySQL database and query and modify data held within such a database.

The delegate will learn and acquire skills as follows:

  • Using client programs to access a MySQL database
  • Creating, altering and dropping a MySQL database
  • Writing SQL statements to select data from a MySQL database
  • Writing joins and subqueries
  • Using SQL expressions
  • Using SQL aggregate functions and scalar functions
  • Inserting, updating, deleting and replacing rows
  • Creating and altering tables, indexes and views
  • Using transactions
  • Granting and revoking access privileges on tables and views
  • Exporting and importing data

Pre-Requisites

There are no formal pre-requisites, although an understanding of databases and exposure to information technology in general would be useful.

Follow-On Courses

  • MySQL for Developers
  • MySQL Database Administration
  • PHP Programming & MySQL for Web Development

Course Content

Course Contents - DAY 1

Course Introduction

  • Administration and course materials
  • Course structure and agenda
  • Delegate and trainer introductions

Session 1: DATABASE CONCEPTS

  • What is a database?
  • Database management systems
  • Tables, rows and columns
  • Indexes, primary keys, unique constraints and foreign keys
  • Client-Server architecture
  • Supported data types
  • Storage engines and table types
  • The Information_Schema and MySQL Databases
  • Exercise: Using a MySQL Database

Session 2: USING THE MySQL CLIENT

  • What is the MySQL client?
  • Getting started and Logging in
  • Selecting a database
  • Client commands
  • Entering and executing SQL statements
  • Retrieving previous commands
  • Creating, editing and executing SQL files
  • Redirecting output into a file
  • Command line execution of MySQL Scripts
  • Exercise: Using the MySQL Client

Session 3: BASIC SELECT

  • The SQL SELECT statement
  • Case sensitivity
  • Quotes
  • Statement terminator
  • Syntax conventions
  • The select clause
  • The FROM clause
  • Conditions and the WHERE clause
  • Logical operators
  • The ORDER BY clause
  • Column aliases
  • Arithmetic expressions
  • Precedence of operators
  • Exercises: Querying the sample database

Session 4: LIMIT, UNION AND AGGREGATE FUNCTIONS

  • The LIMIT clause
  • UNION
  • UNION ALL
  • Aggregate functions
  • The GROUP BY clause
  • Using Rollup with GROUP BY
  • The HAVING clause
  • Exercises: Using Limit, Union and Aggregate Functions

Course Contents - DAY 2

Session 5: SUBQUERIES AND JOINS

  • Subqueries
  • Cartesian products
  • Joins with original syntax
  • Table aliases
  • Natural joins
  • Join using
  • Join on
  • Multi-table joins
  • Exercises: Using Subqueries and Joins

Session 6: NUMERIC AND CHARACTER FUNCTIONS

  • Function types
  • Testing functions
  • Numeric functions
  • Character functions
  • Exercise: Using Numeric and Character Functions

Session 7: DATE, TIME AND OTHER FUNCTIONS

  • Date and time column types
  • Date and time formats
  • The date format function
  • Functions to return date time
  • Functions to extract components from date time
  • Date time arithmetic
  • Miscellaneous functions
  • Exercise: Using Date, Time and other functions

Course Contents - DAY 3

Session 8: DATABASES AND TABLES

  • Creating a database
  • Selecting a database
  • Obtaining database metadata
  • Table properties
  • Creating tables
  • Auto_increment
  • Show create table
  • Column operations
  • Constraint operations
  • Copying tables
  • Renaming tables
  • Changing engine for tables
  • Obtaining table metadata
  • Altering tables
  • Dropping tables
  • Temporary tables
  • Memory tables
  • Exercises: Maintaining databases and tables

Session 9: INDEXES AND VIEWS

  • What is an index?
  • Structure of an index
  • Creating an index
  • Reviewing indexes
  • Composite indexes
  • Altering a table to add an index
  • Dropping indexes
  • What is a view?
  • Creating views
  • View restrictions
  • Dropping views
  • Exercise: Maintaining indexes and views

Session 10: MANAGING DATA

  • Inserting rows
  • Replacing rows
  • Updating rows
  • Deleting rows
  • The truncate statement
  • The COMMIT and ROLLBACK commands
  • Savepoints
  • Implicit commits
  • Exercise: Managing data

Session 11: ACCESS CONTROL

  • Creating users
  • Renaming users
  • Dropping users
  • Granting privileges
  • Revoking privileges
  • Exercise: Creating users, granting and revoking

Session 12: IMPORT AND EXPORT

  • Exporting using SQL
  • Importing using SQL
  • Exporting from the command line
  • Importing from the command line
  • Exercise: Export and import data

Manchester - Nowgen Centre

Description:

Our training rooms are located in the NOWGEN building. The NOWGEN Centre is in a prime location and is situated between The University of Manchester and Central Manchester University Hospitals NHS Foundation Trust. The venue is easily accessible from Manchester City Centre, with buses running at 10 minute intervals from Piccadilly Station. From the station there is also a 15 minute rail service to Manchester International Airport which is eight miles away. Many buses run very frequently from Piccadilly Gardens to the University and hospital complex.

On arrival at the Nowgen reception which is located on the ground floor of the building shown above, please ask to sign in on the course registration sheet.

The training and breakout rooms are also situated on the ground floor.

Location:

Nowgen Centre
29 Grafton Street
Manchester
M13 9WU

 

Directions:

by Train /Bus

Manchester Piccadilly train station

A new bus service is now operating between Manchester Piccadilly train station and the Nowgen Centre. The service starts outside Piccadilly train station on Fairfield Street and stops on Grafton Street outside the Nowgen Centre.

To get to Fairfield Street from Piccadilly station concourse follow the signs for the taxi rank, which is located down the escalators. From the taxi rank, cross over to the far side of the main road outside the station complex and then turn left along this road. Now make your way towards the railway bridge which crosses this road. The 147 bus stop/shelter is located directly under the railway bridge.

The 147 service runs every 10 minutes during peak times and every 15 minutes at other times, Monday to Friday. At the time of writing these instructions (May 2014), the bus costs 80p for a single journey and £1.50 for unlimited travel on that route for a day. Travel time is approximately 20 minutes to the hospital complex - (Central Manchester University Hospital).

If you use the 147 bus you can wait whilst the bus travels around the hospital complex and finally stops in Grafton Street with just a short walk back to the Nowgen building.  Alternatively you can get off the bus just past the large sandstone church. This is located on your left hand side just after going past the old sandstone Manchester University building which is situated on your right.  The Church and the Medical School (Stopford Building) are at the corner of Ackers Street and the bus stop is located just after this street.

After getting off the bus continue along the road in the same direction as the bus, go past Sainsburys until you reach Grafton Street.  On the corner of Grafton Street there is Costa and Red Chilli restaurant. Walk about 100 yards up Grafton Street and Nowgen is situated on the right hand side. The entrance is down the right hand side of the building.

Alternatively, the following buses travel from Piccadilly Gardens to the Hospital complex:  11, 14, 15, 16, 41, 42, 43, 46, 48, 142, 143 and 157.

Taxis take about 15 minutes from Piccadilly station and 20 minutes from the airport.

Victoria train station

If you arrive at Victoria train station, catch a tram (cost included in the train ticket) to Piccadilly train station or Piccadilly Gardens and then catch one of the buses listed above. These buses can also be caught outside Oxford Road train station. Depending on which bus you catch will depend on which bus stop you will alight. Look for the landmarks mentioned above for the 147 bus especially Sainsburys, Costa and Red Chilli

Taxis take about 15 minutes from Piccadilly station and 20 minutes from the airport.

For further information on public transport links visit the Transport for Greater Manchester web site or telephone the Transport Executive on 0161 228 7811 (open 8am to 8pm).


By Car

Parking

A multi-storey car park is available on Grafton Street, next to the Nowgen Centre. This is situated at the Oxford Road end of Grafton Street. Spaces are limited and it is best to arrive before 9am. Parking costs are up to 30 minutes free and then on a sliding scale.  For the purposes of the course 6 to 24 hours cost £15.00.

Road Directions to the Nowgen Centre

From the West via M62

  • When on the M62 at Junction 12 continue straight on and join the M602 (Salford).
  • The end of the motorway will join the A57 (Manchester), becoming A57(M) after 1 mile.
  • Leave the Mancunian Way, A57(M) at the A34 Congleton sign, bearing left onto the A34.
  • Once on Upper Brook Street (A34) turn right at the 5th set of traffic lights into Grafton Street. This junction is after the fork in the road signposted Stockport and Longsight and has traffic lights at the fork. The next set of lights is your turning into Grafton Street.

From the North East via M62

  • Leave the M62 at Junction 18 and join the M60 (Manchester) (M62 - J21 to J18 can be very slow moving on a Monday morning).
  • Leave at the next Junction, to join the A576 (Manchester Central). After approximately 1 mile, bear left at the traffic lights into the A665 Bury Old Road.
  • At the 5th set of traffic lights turn right and go past the Phones 4U Arena - (originally the M.E.N Arena) into Trinity Way.
  • At the traffic lights at the bottom of the hill turn left into Great Ducie Street.
  • Drive under the railway bridge and follow the road round to the right to traffic  lights with a turning to the left.
  • Turn left at these lights passing hard landscaped area with large metal   sculptures on your left. The next set of lights turn right onto Victoria Street / Deansgate.
  • At the third set of lights (past House of Fraser on your right) turn left into John Dalton Street.
  • Go straight through the next set of traffic lights. Albert Square with the Town Hall will be on your right.
  • Travel through several sets of traffic lights for approximately 1½ miles (A34). At the fork in the road signposted Stockport and Longsight keeping right carry straight on through the lights and then turn right at next set of traffic lights into Grafton Street.

From the South via M6

  • Leave the M6 at Junction 19 onto the A556.
  • Then join the M56 at Junction 8 heading towards the Airport.
  • Continue along the M56, which will continue as the A5103 (Princess Road).
  • Turn right into the B5219 (Moss Lane East by the Brewery).
  • Continue to the end and at the T junction turn left into the B5117 which is Oxford Road.
  • Follow Oxford Road past the Hospitals and then turn right into Grafton Street. (Costa Coffee on the corner).

From the North via M6

  • Leave M6 via the M61 and continue to the M60.
  • Join the M60 and travel anti?clockwise to Junction 12 and join the M602 (Salford).
  • At the motorway end join the A57 Regent Road (Manchester) which becomes A57(M) after 1 mile.
  • Leave the Mancunian Way A57(M) at the A34 Congleton sign bearing sharp left, following signs for the Universities into Upper Brook Street (A34).
  • Turn right at the fifth set of traffic lights into Grafton Street. This turning is after the forked junction in the road to Stockport and Longsight.

From the South via M6

  • Leave the M6 at Junction 19 onto the A556.
  • Then join the M56 at Junction 8 heading towards the Airport.
  • Continue along the M56, which will continue as the A5103 (Princess Road).
  • Turn right into the B5219 (Moss Lane East by the Brewery).
  • Continue to the end and at the T junction turn left into the B5117 which is Oxford Road.
  • Follow Oxford Road past the Hospitals and then turn right into Grafton Street (Costa Coffee on the corner).

From M56

  • At Junction 3 the M56 exits off on a slip road. Do not exit here.
  • Keep going straight ahead onto A5103 to Manchester for about 4 miles.
  • Turn right at traffic lights signed Whitworth Art Gallery and Universities.
  • Go straight over the first set of lights and at the second set of lights, turn left.
  • Turn right at the next lights into Grafton Street.

Together with the MySQL for Developers course, this course covers the major topics required for the MySQL 5.6 Developer Certified Professional Exam(1Z0-882).

Together with theMySQL Database Administration course, this Introduction to MySQL course will teach the major topics required for the delegate to work towards achieving the MySQL 5.6 Database Administrator Certified Professional Exam(1Z0-883).

Before taking any exam, ensure you have the recommended experience. The Oracle website lists all exam requirements and these are updated regularly.

Exams are not included as part of the course.

Our Customers Include