SQL databases and language for nonIT people Training
- Participants / Prerequisite
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.
- 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
- 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.
Investigating the database by searching for tables, views, columns, and keys.
- 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.).
Querying multiple tables on different criteria.
- Concept of joins: Returning information from multiple tables.
- Internal join. External join.
- The “natural” join... and its difficulties.
- Assembly operators (UNION, INTERSECT...).
Creating queries with joins and assembly operators.
- 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.
Creating queries using simple and aggregate calculations. Subtotals and numbering.
- 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.
Using functions to improve the presentation of the query result.