Introduction to SQL
Dates & Duration
See available dates for this course!
If there is no date and time available for a certain training you are interested in, please contact us.
This training can also be held at your place or as online training especially for your company. In this case the course agenda can also be adapted to suit your needs best.
Learn more about our customized training >>
This course takes 3 days with 8 hours each (on site) or 5 hours each (online).
The only difference is that there will be less practical exercises in an online course. However, we will hand them over to you and you can still do them on your own and ask our consultants for feedback or help if needed.
Audience
This workshop has been designed for people who want to get in touch with SQL and PostgreSQL. You will learn how to use PostgreSQL and how to write proper SQL statements.
Available Language
This course can be held in English, German, Spanish, Polish, Estonian, Ukrainian and Russian.
Content
Day 1
Installing PostgreSQL
- Connection to the database
- Usage of pgAdmin4 and similar tools
Relational data models
- Normalization
- Creating tables and using data types
- Primary Keys and Foreign Keys
- Check constraints
Basic SQL commands
- INSERT: Insert data
- SELECT: Simple queries
- UPDATE: Modify data
- DELETE and SELECT: Delete data
- Filter data
- NULL Handling
- Sort data with ORDER BY
- LIKE queries
- UNION ALL, UNION, EXCEPT
- DISTINCT and DISTINCT ON
- LIMIT and OFFSET
Day 2
Aggregate functions
- Aggregate data (count, sum, etc.)
- Minimum and maximum values
- Group data (GROUP BY … HAVING …)
- Use ordered sets (WITHIN GROUP …)
Subqueries and CTEs
- Nested queries
- Common Table Expressions (CTEs)
- Use IN and NOT IN
- EXISTS and NOT EXISTS
Joins
- Understand inner joins
- Use outer joins
- Semi joins, anti joins
Date
- Calculating with date and time
- Understanding intervals
- Include range types
Day 3
Windowing and Analytics
- OVER-clauses
- Partitioning
- Define sliding windows
- Important analytical functions (rank, lag, etc.)
- Time series analysis
Views and materialized views
- Basic views
- Dependency handling
- Using materialized views
Transactions
- BEGIN, ROLLBACK
- Row locking and table locking
- Transaction isolation levels (read committed, etc)
- SELECT … FOR UPDATE
- Deadlocks
Cursor
- Declare cursor
- WITH HOLD cursor
Indexing
- Creating and deleting indexes
- Identify missing indexes
- Avoiding unnecessary indexes
Other courses that might interest you
We offer these services
PostgreSQL Troubleshooting
POSTGRESQL TROUBLESHOOTING Are you fighting with corrupted database systems? Is your PostgreSQL database throwing strange error messages or do you have the feeling that something is not quite right? Is your database suddenly suffering from performance problems or are you facing problems with your replication setup? Our troubleshooting team is here to help. It’s not […]
PostgreSQL Health Check
The Health Check identifies errors and problems that may occur in your PostgreSQL database in terms of performance, high availability and security.
9/5 Basic Support
CYBERTEC 9/5 Basic Support includes all Product Support services and on top also performance tuning and extended troubleshooting. 9/5 means that we’re here for you from Monday to Friday from 9am to 5pm (CET/MEZ). Other timezones on request. CYBERTEC provides all bug fixes for the most used platforms, processors or architectures. This includes not only […]