Database specialist (Excel, Access). Database courses Database training

From the author: Did they call you a teapot? Well, this can be fixed! Every samovar was once a teapot! Or was every professional once a samovar? No, something is wrong again! In general, MySQL is for beginners.

Why do dummies need MySQL?

If you are seriously planning to connect your life with the Internet, then right from the very first steps in the “web” you will come across this DBMS. MySQL can easily be called the “all-Internet” database management system. Not a single more or less serious resource can do without it; it is present in the admin panel of every hosting. And most of all popular CMS and even “homemade” engines are built with its participation.

In general, you cannot do without this platform. But to study it you will also need the right approach, the right tools, and most importantly desire and patience. I hope you have enough of the last ingredients. And be prepared for your brains to boil and steam to pour out of your head, like from a real kettle

But MySQL is so difficult for dummies only if you start learning it incorrectly. You and I will not make such a mistake, and we will begin our acquaintance with this technology from the very beginning.

Basic Concepts

First, let's go over the basic concepts that we will mention in this publication:

Database (DB) is the main component unit of a DBMS. The database includes tables that consist of columns and records (rows). The cells formed at the intersection contain structured data of a certain type.

DBMS (database management system) is a set of all software modules for database administration.

SQL is a structured query language with which the developer “communicates” with the DBMS core (server). Like any programming language, SQL has its own syntax, set of commands and operators, and supported data types.

I think that theoretical knowledge is enough for us to start with. We will “color in” the missing gaps in theory with practice. Now all that's left is to choose the right software tool.

Selecting the right tool

Having rummaged through the entire range of MySQL shells for beginners, I realized that they simply do not exist. All software products To administer a DBMS, you already need to have installed server DB. In general, I decided once again not to invent the “scooter”, and opted for the domestic Denwer package. You can download it on the official website.

It already includes all the components of a DBMS, allowing a beginner to begin a practical acquaintance with MySQL immediately after a simple and straightforward installation. In addition, Denwer includes several more tools necessary for a novice developer: local server,PHP.

First steps

I will not describe the installation process of the “gentleman’s” set, since everything happens automatically there. After launching the installation, only have time to press the necessary keys. Just what you need in the MySQL for dummies version.

When the installation process is completed, start the local server, wait a couple of seconds. After that, type localhost in the address bar of your browser.

On the page “Hurray, it’s working!” follow one of the links shown in the picture. After which you will be taken to phpMyAdmin - a shell for administering databases.

By following the link http://downloads.mysql.com/docs/world.sql.zip, you will download an example test database from the official MySQL website. Go to phpMyAdmin again, in the main menu at the top, go to the “Import” tab. In the Import to Current window, in the first section (File to Import), set the value to Browse your computer.

In the Explorer window, select the archive with the downloaded example database. Don't forget to click "Ok" at the bottom of the main window.

I advise you not to change the specified parameter values ​​for now. This may result in incorrect display of the imported source data. If the phpMyAdmin system generates an error that it cannot recognize the database compression algorithm, then unzip it and repeat the entire import process from the beginning.

If everything went well, then a program message will appear at the top that the import was successful, and on the left in the list of databases there will be another one (word).

Let's look at its structure from the inside so that you can more clearly imagine what you have to deal with.

Click on the name of the MySQL database for beginners. A list of tables that it consists of will be displayed below it. Click on one of them. Then go to the top menu item “Structure”. The main work area will display the table structure: names of all columns, data types and all attributes.

Today it is impossible to imagine the work of the largest companies, banks or government organizations without the use of databases and tools. Databases allow us to store and access large volumes of information, and a database management system (DBMS) allows us to manage available information repositories. Today, the largest DBMS manufacturers are transnational corporations such as Microsoft and Oracle.

The tools are used for business analysis and modeling of various processes. They allow you to cope with redundant information, focusing users' attention on key performance factors, modeling the outcome of various options for action, and tracking the results of making certain decisions.

You can learn about databases and Business Intelligence tools at coursesB.I.() Center "Specialist" at MSTU. N.E. Bauman - presenter Microsoft Authorized Training Center in Russia.

Also at the Specialist Center there are training courses onSQL Server, which describes the fundamentals of relational databases, starting with the basic objects that are accessed using the SQL language. In progress trainingSQL Server Strategies for accessing and retrieving data, modifying data using instructions are discussed in detail; tables, types and declarative data integrity; complex queries, programming in Microsoft SQL Server in T-SQL language custom stored procedures, functions, triggers and views; methods for improving query performance, extension functionality Microsoft SQL Server at the expense using XML, SQLCLR and Filestream, as well as using spatial types, full-text search and Service Broker. Classes are taught by certified Microsoft trainers. Prestigious international certificates, state training programs, the largest Prometric testing center in Russia are waiting for you!

In addition, the Specialist Center conducts courses onOracle, which clearly and comprehensively reveal the main features of the Oracle DBMS architecture, which fundamentally distinguish it from other popular database management systems. And on courses onOracle exactly those capabilities, means and features are described in detail and illustrated with many examples Oracle, which ensure the development of effective applications for this DBMS and its successful operation.

Also at the Specialist Center there are courses on MySQL. With us you can go from the level of a novice developer to the level of an expert developer MySQL DB.

Advantages of training to work with databases at the Specialist Center:

• Center "Specialist" - the best training center Microsoft in Russia, Eastern and Central Europe!
• The Specialist Center has the highest partner status Microsoft Gold Learning in the field of training, as well as competence Silver Data Platform, confirming the highest qualifications of the Center's teachers in the field of administration, development and business analytics based SQL Server 2012.
• The Specialist Center has a prestigious status Oracle Academy.
• We have the best teachers. Bauman traditions of quality .
• The highest level of training for administrators, database developers and analytical solutions based on SQL Server. More than 80% DBMS administrators and developers, as well as business analytics (BI) specialists in Russia, completed courses at the Specialist Center.
• Prestigious

Start dates

Access and Excel database course program

Excel

1. Basic elements of the Excel program interface

• 1.1. Main menu, toolbars, work area
• 1.2. Cell name panel, formula bar, scroll bars
• 1.3. System status bar
• 1.4. Data entry and editing
• 1.5. Inserting and deleting cells, rows, and columns

2. Visual design of the project

• 2.1. Formatting cell contents, font settings.
• 2.2. Various styles of drawing and design of tables
• 2.3. Ways to align text in cells
• 2.4. Built-in formats Excel data
• 2.5. Tools for creating and designing projects

3. Forms for entering data into Excel tables.

• 3.1. Creating a Database in Excel
• 3.2. Managing the Data Form Window
• 3.3. Changing an entry
• 3.4. Enter new entry
• 3.5. Deleting entries
• 3.6. Search records
• 3.7. Sorting a list

4. Formulas and functions.

• 4.1. The concept of formulas and functions in Excel.
• 4.2. Standard Excel functions.
• 4.3. Moving and copying formulas in cells.
• 4.4. Fixed and floating cell names in spreadsheet formulas.
• 4.5. Function Wizard.
• 4.6. User-defined names.
• 4.7. A practical example of creating a complete project for calculating shareholder income.

5. Charts and graphs.

• 5.1. Chart Wizard.
• 5.2. Designing a diagram. Automatic chart format.
• 5.3. Editing a diagram.
• 5.5. Creating, editing, grouping and linking graphic objects to Excel sheets

6. Macros in Excel

• 6.1. Creating a macro using an autorecoder
• 6.2. Editing a Macro Using the Visual Basic Editor

7. Pivot tables.

• 7.1. Pivot Table Wizard.
• 7.2. Data consolidation.
• 7.3. Methods for changing the structure of a spreadsheet sheet.

Access

1. Access tables

• 1.1. Table structure
• 1.2. Creating tables. General information about field types
• 1.3. Data entry, editing, highlighting, copying and pasting in a table
• 1.4. Long text fields and MEMO fields. Input area.
• 1.5. OLE object field.
• 1.6. Hyperlink field.
• 1.7. Field order. Counter field. Field labels.
• 1.8. Numeric fields.
• 1.9. Input masks and value conditions.

2. Learn more about Access tables

• 2.1. Search Operations
• 2.2. Search and replace
• 2.3. Deleting data
• 2.4. Other data formats. Importing and joining tables.
• 2.5. Import and connection Excel sheets and text files.
• 2.6. Working with a large table image.
• 2.7. Applying filters.
• 2.9. Keys.

• 3.1. General views. Query builder.
• 3.2. Writing selection conditions.
• 3.3. Creating calculated fields. Expression Builder.
• 3.4. Select query with group operations.
• 3.5. Request to create a table.
• 3.6. Request for update.
• 3.7. Request to add.
• 3.8. Removal request. Cross request

4. SQL queries

• 4.1. Entering SQL Expressions
• 4.2. Select statement
• 4.3. Statistical functions Count, First, Last
• 4.4. Statistical functions Min, Max, Avg
• 4.5. Sum function
• 4.6. Request to add
• 4.7. Request for update
• 4.8. Removal request
• 4.9. Query to create a table

5. Basics of multi-table databases

• 5.1. Splitting data into separate tables. Connecting fields.
• 5.2. The concept of database normalization
• 5.3. Multi-table select query. Data schema. Subtables.
• 5.4. Working with subtables. Data integrity issues.
• 5.5. Data integrity support. Merging options.
• 5.6. Data integrity support. Substitution.
• 5.7. Practical examples substitutions
• 5.8. Other types of relationships between tables.

6. Shapes

• 6.1. Form Wizard
• 6.2. Forms. Autoforms. Basic elements of the Form Builder.
• 6.3. Actions in the form designer.
• 6.4. Form areas. Entering labels. Formatting fields and labels.
• 6.5. Entering free, calculated fields.
• 6.6. Links to form elements. Form modes. Form properties.
• 6.7. Related forms. Creating a main and subform.
• 6.8. Methods for linking forms.

7. Reports

• 7.1. Print tables and forms. Saving the form as a report.
• 7.2. Report designer. Report zones. Tabular report.
• 7.3. Changing the record source, record numbering and summary functions.
• 7.4. Sorting and grouping the report.
• 7.5. Pagination. Expanded inscriptions.
• 7.6. Report in the form of a letter. Address labels.
• 7.7. Selecting the output mode and data for the report from the form.
• 7.8. Report printing options. Snapshots of reports.
• 7.9. Export reports to various formats

Ak.ch.	Base price	Discount	Final cost	Pay
60 academic hours 52 ac. hour.- classroom training 8 ac. hour.- independent studies	18500 rub.		13,000 rub.

The course introduces students to the basic principles of working with structured data in the relational model, teaches how to design data, describe database objects in terms of a real DBMS, write queries in SQL, use views, procedures, functions and triggers, create indexes, manage concurrent access to data and manipulate the transaction mechanism

About the course

The basis of the course is the study and application of the SQL language for creating, modifying database objects and managing data in an arbitrary relational database.
Completing practical tasks within the course involves using the My SQL DBMS.

The course examines the stages of designing relational databases, rules for constructing queries, and basic methods of data indexing. The course will explore the use of transactions and data access rights.

The course also provides an overview of current trends in the field of data science in connection with the emergence of Big Data. At the end of the course, the areas of application of NoSQL databases will be shown and modern approaches to big data processing will be indicated.

Format

The form of study is correspondence (distance). Weekly classes will include watching thematic video lectures, reading electronic notes and completing test tasks with automated verification of results, testing on the material covered. To receive a certificate, you must complete all assignments, tests and write the final exam.

Requirements

To successfully complete the course, you need knowledge of the basics of programming, the basic concepts of set theory, the basics of linear algebra and the basics of mathematical statistics. The course is designed for undergraduate students. The course can be useful not only for students majoring in Computer and Information Sciences, but also for other specialists who need to manipulate large volumes of data.

Course program

Week 1. Introductory lecture, which describes the main content of the course, the sections of the course, and the place of databases for various information systems.
Week 2. Database design. Entity-relationship model.
Week 3. Relational algebra. Normalization of relational relations.
Week 4. Basic database objects and their description in SQL.
Week 5. Queries in SQL language.
Week 6. Views, procedures, functions, triggers.
Week 7: Data Indexing.
Week 8. Optimizing query execution.
Week 9. Managing concurrency.
Week 10. Directions and trends in database development. Modern approaches to Big Data processing.

Learning outcomes

The outcome of the course will be the ability of students to master terminology and basic objects databases, SQL basics, ability to design data and create database objects in terms of the relational model, write queries in SQL language, create indexes to speed up query execution, construct views, functions, procedures and triggers, manage access to a database for many users, use transaction mechanisms, as well as developing the ability to apply database methods in various fields.

Formed competencies

Upon completion of the course, users will know:

1. Principles for describing the conceptual infological (infological) data model
2. Principles of data design in terms of the relational model
3. Basic SQL operators
4. Current trends in the development of data science
5. Main types of NoSQL databases
6. Basic methods of data analysis

Upon completion of the course, users will be able to:

1. Describe data structures in terms of the “Entity-Relationship” conceptual model
2. Create database objects
3. Describe integrity constraints for data
4. Build additional structures to speed up data searches
5. Write queries in SQL language
6. Create views, functions, procedures and triggers
7. Create new users and give them access rights to the database
8. Use a transaction mechanism for concurrent data access

Upon completion of the course, users will understand:

1. Basic principles of data engineering
2. How indexes are structured
3. How the query optimizer works
4. How the transaction manager works
5. What are the challenges when processing Big Data?