From zero to SQL coder hero – SQL in practice

nieobowiązkowe

Course is dedicated for everyone who wants to learn how to write SQL queries in practice – students, data analysts future data engineers and everyone who uses SQL in daily work. Course covers both simple topics like SQL query syntax and advanced elements like correlated subqueries or recursive queries.

Course is based on free, relational database - SQLite, for management database program served DBeaver.

Course is prepared in a tutorial manner. After part of material there are tasks to be done by participant, divided into 4 groups:

- Basic * (19 tasks)

- Mid – advanced ** (28 tasks)

- Advanced *** (11 tasks)

- Expert **** (7 tasks)

1. INTRODUCTION

1.1 For who is this course dedicated

1.2 Several words about SQL

2. PREPARING ENVIRONMENT TO WORK

2.1 Loadin database otomoto_db

2.2 DBeaver

2.3 Properties of particular tables

3. SQL SYNTAX

3.1 Selecting data - SELECT FROM clause

- Unique values

- Limiting rows

- SELECT without FROM clause

3.2 Filtering data – WHERE clause

- Operators AND / OR

- Other operators used to compare values

- Operator IN

- Operator NOT

- Operator LIKE

- Other ambiguous signs

- NULL clause

3.3 Grouping data - GROUP BY clause

- Aggregating values

- Grouping values

- Grouping text

3.4 Filtering groups – HAVING clause

3.5 Sorting data – ORDER BY clause

4. SCALAR FUNCTIONS IN SQL

4.1 Text functions

4.2 Date functions

4.3 Math functions

4.4 Converting functions

- Data types

- Converting functions and function COALESCE

5. CONDITIONAL INSTRUCTIONS

5.1 Instruction IIF

5.2 Instruction SELECT … CASE

6. SUBQUERIES

6.1 Subqueries inside FROM clause

6.2 Correlated subqueries

6.3 CTE – Common Table Expressions

7. JOINING TABLES

7.1 LEFT OUTER JOIN

7.2 INNER JOIN

7.3 FULL OUTER JOIN

7.4 UNION and UNION ALL

7.5 Merging many tables

7.6 Alternative for FULL JOIN in SQLite

8. WINDOW FUNCTIONS

8.1 Syntax and types of window functions

8.2 Parameter PARTITION BY i ORDER BY

8.3 Difference between ROW_NUMBER a RANK

8.4 Parameter NULLS

8.5 Functions LEAD and LAG

8.6 Functions FIRST_VALUE and LAST_VALUE

8.7 Aggregating functions

8.8 Parameter BETWEEN … AND

9. RECURSIVE QUERIES

9.1 Recursion in theory and practice

10. CREATING AND MODIFICATIONG DATABASE AND ITS CONTENT

10.1 Creating new database in SQLite

10.2 Operations on tables

- Creating tables

- Modyfing tables

- Deleting tables

10.3 Operations on records in a table

- Inserting rows

- Modyfying rows

- Deleting rows

10.4 CONSTRAINTS

10.5 Primary key and foreign key

11.VIEWS IN SQL

12.INDEXES

13.FUNCTIONS UNAVAILABLE IN SQLITE

13.1 SQLFiddle

13.2 Scalar functions in other databases

13.3 Function ROLLUP

13.4 Function CUBE

13.5 Function GROUPING SETS

13.6 Function GROUPING

Course is prepared in a tutorial manner. After part of material there are tasks to be done by participant, divided into 4 groups:

- Basic * (19 tasks)

- Mid – advanced ** (28 tasks)

- Advanced *** (11 tasks)

- Expert **** (7 tasks)

Participand after completing course is able to construct both simple and advanced SQL queries, independently on DataBase version. Participants will be familiar with most important elements of databases like tables, views, relations or indexes. Thanks to practical dimension of course participants will be able to use gained knowledge at work or on job interviews.

2 test – 1st after 7 chapter, 2nd at the end of the course. 1st test – 60 points, 2nd test – 40 points. Pass threshold – 51 points.