SQL - Basics and database design

Description

Learning objectives

After the training, you will be able to submit database queries in the SQL query language against a Microsoft SQL Server or a MySQL server.
A theoretical introduction is followed by a workshop in which you create and then edit relational databases on the server. To do this, you will use command line tools of the SQL Server and MS Access or phpMyAdmin.
The writing of complex SQL queries and the handling of views, triggers, transactions and stored procedures round off the training.

Intended audience

• Administrators who maintain a database system based on SQL
• Developers who programme database applications for SQL Server or MySQL

Recommended previous knowledge

• Previous experience in configuring and operating Windows server systems is helpful but not essential

General information

Training details

• In-house training or in our training room (region Marburg, Hesse)
• Dates and duration: Flexible and depending on number of participants and previous knowledge
• Detailed training materials with a total of over 200 pages, incl. (German)
• Incl. access for the e-learning platform for the provision of electronic accompanying materials and e-learning content (German)

Training content (customisable).

Basics

• Databases and database terms
• Data redundancy, data integrity, data security, concurrent data access
• Designing Relational Databases
• The Entity Relationship Model
• Normalising databases and tables

SQL - Queries (Queries)

• Basic elements of the SQL language
• Predicates, aggregate functions and scalar functions, NULL values
• Integrity rules and value ranges, referential integrity, constraints
• Creating databases and tables, data types
• Insert, update, delete data
• Create data queries, group and sort results
• Using SQL functions in data queries

Key fields, indices and complex queries.

• Setting and editing key fields
• Index databases
• Sub-queries, linking two or more tables, intersection and difference sets, data correlation

More advanced SQL commands

• Insert, modify and delete data via views
• Work with cursors, read records sequentially with cursors
• Transactions: Ensuring consistent datasets
• Create, edit and execute stored procedures
• Trigger: Execute procedures automatically
• Integrate XML data into relational structures

Database security

• Security concepts of a database management system (DBMS)
• Managing access rights and users