From zero to SQL coder hero – SQL in practice 2400-ZEWW877

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