ORSYS formation
CONTACT - +33 (0)1 49 07 73 73
CONTACT - 📞 +33 (0)1 49 07 73 73    drapeau francais   drapeau anglais
Consult our trainings :

SQL databases and language for nonIT people Training

Hands-on course
Best
Duration : 3 days
Ref : SIF
Price  2019 : Contact us
  • Program
  • Participants / Prerequisite
  • Intra/Tailored
Program

This "discovery" course will enable you to understand socalled relational databases and how they operate. You'll work with the SQL language to query the data in a base. You'll also become familiar with more advanced queries to analyze information.

Learning objectives

  • Understand the principle and contents of a relational database
  • Create queries to extract data based on different criteria
  • Produce queries with joins in order to get information from multiple tables
  • Use simple calculations and data aggregation
  • Combine results from multiple queries
  • Instructional methods

Hands-on work

Many sequential exercises for extracting data from an example database.
PROGRAM

Introduction to databases

  • What are a database and a database server?
  • Reading a relational model.
  • Creating a table. Notions of columns and types
  • Primary key and uniqueness
  • Links between tables and referential integrity.
  • Metadata of tables, columns, and keys.
  • Tool for querying a database.

Exercise
Investigating the database by searching for tables, views, columns, and keys.

Extracting data from a table

  • What is an extraction query?
  • List the values to be returned.
  • The WHERE clause for filtering data.
  • The absence of a value (NULL marker).
  • Returning unduplicated rows (DISTINCT).
  • Restriction operators (BETWEEN, IN, LIKE, etc.).

Exercise
Querying multiple tables on different criteria.

Querying data from multiple tables

  • Concept of joins: Returning information from multiple tables.
  • Internal join. External join.
  • The “natural” join... and its difficulties.
  • Assembly operators (UNION, INTERSECT...).

Exercise
Creating queries with joins and assembly operators.

Ranking and statistics

  • Finding aggregate values (MIN, MAX, AVG, SUM, etc.).
  • Calculating relative aggregates with GROUP BY.
  • Filtering aggregate values with HAVING.
  • Mixing aggregates and details with OVER.
  • Ranking results with RANK, ROW_NUMBER and NTILE.

Exercise
Creating queries using simple and aggregate calculations. Subtotals and numbering.

Presenting and sorting data

  • Presenting data from columns with aliases
  • Converting from one type to another.
  • Making choices using the CASE operator.
  • Sorting data with ORDER BY.
  • Operations on character strings and dates.

Exercise
Using functions to improve the presentation of the query result.

Using subqueries

  • What is a subquery?
  • Different types of results.
  • Subqueries of lists and IN, ANY/SOME and ALL operators.
  • Correlated subqueries.
  • Using CTE (Common Table Expressions) to factor subqueries.

Exercise
Writing queries that include subqueries of different forms. Creating views.

Participants / Prerequisite

» Participants

People in charge of reporting or analysis, assistants, anyone who needs to carry out simple queries or updates on a database with SQL language

» Prerequisite

No particular knowledge. Education common to all relational databases (Oracle, SQL Server, DB2, PostGreSQL, MySQL, Access, SQL Lite, etc.)
Intra/Tailored

Contact Informations

By checking this box, I certify that I have read and accepted the conditions for the use of my data regarding the General Data Protection Regulation (GDPR).
You can at any time modify the use of your data and exercise your rights by sending an email to rgpd@orsys.fr
By checking this box, I agree to receive commercial and promotional communications from ORSYS Training*. You can unsubscribe at any time by using the link included in our communications.

Book your place

Submit your request

Time schedule

Generally, courses take place from 9:00 to 12:30 and from 14:00 to 17:30.
However, on the first day attendees are welcomed from 8:45, and there is a presentation of the session between 9:15 and 9:30.
The course itself begins at 9:30. For the 4- or 5-day hands-on courses, the sessions finish at 15:30 on the last day
linkedin orsys
twitter orsys
it! orsys
instagram orsys
pinterest orsys
facebook orsys
youtube orsys