SQL DQL Course

DQL Course Overview

Table of Contents

• Content
• Standards
• Aspects
  • 👶 Introduction
  • 💪 Workout
  • 🦑 Deep
  • ✨ New
  • 🔮 Obscura

Content

Below you can find the ordered content of the topic, in a linear progression

The linear progression of content aims to cover all content, course by course, workout by workout as follows:

• first course is the only core one, denoted by its manifest
• the next course is denoted by the first item of the next array in each course manifest
• each course has its order of workouts designated by the sections field in the same aforementioned manifest

1. what-is-sql

Insights:

no	name	content	aspects	standards	PQ	RQ	Quiz	done
1	what-is-sql	✅	👶 introduction	read-single-table.0: 10	✅	✅	❌	✅
2	what-is-an-rdbms	✅	👶 introduction	connect-client.0: 10	✅	✅	❌	✅
3	what-is-a-database-server	✅	👶 introduction	❌	✅	✅	❌	❌
4	database-server-types	✅	👶 introduction	connect-client.2: 5	✅	✅	❌	✅
5	what-is-a-database-client	✅	👶 introduction	connect-client.0: 10 connect-client.1: 10	✅	✅	❌	✅

Exercises:

no	name	type	aspects	standards	done
1	sql-practice-what-is-sql	sqlfiddle	👶 introduction	read-single-table.2: 1000	✅

Game:

⚠️ There is no game in this workout.

2. basic-selects

Insights:

no	name	content	aspects	standards	PQ	RQ	Quiz	done
1	select-all	✅	👶 introduction	read-single-table.0: 10	✅	✅	❌	✅
2	select-cols	✅	👶 introduction	read-single-table.0: 10 read-single-table.2: 10	✅	❌	❌	❌
3	select-where	✅	👶 introduction	read-single-table.0: 10 read-single-table.1: 10	✅	✅	❌	✅

Exercises:

no	name	type	aspects	standards	done
1	sql-practice-select-1	sqlfiddle	👶 introduction	read-single-table.0: 1000	✅
2	sql-practice-select-2	sqlfiddle	👶 introduction	read-single-table.0: 1000 read-single-table.2: 1000	✅
3	sql-practice-where-comparison	sqlfiddle	👶 introduction	read-single-table.0: 1000 read-single-table.1: 1000	✅

Game:

⚠️ There is no game in this workout.

3. basic-queries

Insights:

no	name	content	aspects	standards	PQ	RQ	Quiz	done
1	select	✅	👶 introduction 💪 workout	read-single-table.0: 10 read-single-table.1: 10	✅	✅	✅	✅
2	where	✅	👶 introduction 💪 workout	read-single-table.0: 10 read-single-table.1: 10 read-single-table.2: 10	✅	✅	✅	✅
3	where-2	✅	👶 introduction 💪 workout	read-single-table.0: 10 read-single-table.1: 10 read-single-table.2: 10	✅	✅	✅	✅
4	where-3	✅	💪 workout	read-single-table.0: 10 read-single-table.1: 10 read-single-table.2: 10	✅	✅	✅	✅
5	order-by-clause	✅	👶 introduction 💪 workout	read-single-table.0: 10 read-single-table.3: 10	✅	✅	✅	✅

Exercises:

no	name	type	aspects	standards	done
1	sql-practice-select	sqlfiddle	👶 introduction 💪 workout	read-single-table.0: 1000 read-single-table.1: 1000	✅
2	sql-practice-order-results	sqlfiddle	👶 introduction 💪 workout	read-single-table.0: 1000 read-single-table.1: 1000 read-single-table.3: 1000	✅
3	sql-practice-string-matching	sqlfiddle	👶 introduction 💪 workout	read-single-table.0: 1000 read-single-table.1: 1000	✅

Game:

⚠️ There is no game in this workout.

4. write

Insights:

no	name	content	aspects	standards	PQ	RQ	Quiz	done
1	insert	✅	👶 introduction 💪 workout	write.0: 10	✅	✅	❌	✅
2	update-clause	✅	👶 introduction 💪 workout	write.3: 10	✅	✅	❌	✅
3	delete-clause	✅	👶 introduction 💪 workout	write.5: 10	✅	✅	❌	✅

Exercises:

no	name	type	aspects	standards	done
1	sql-practice-insert	sqlfiddle	👶 introduction	write.0: 1000	✅
2	sql-practice-update	sqlfiddle	👶 introduction 💪 workout	write.3: 1000	✅
3	sql-practice-delete	sqlfiddle	👶 introduction 💪 workout	write.5: 1000	✅

Game:

⚠️ There is no game in this workout.

5. aggregate-queries

Insights:

no	name	content	aspects	standards	PQ	RQ	Quiz	done
1	group-by-clause	✅	💪 workout	aggregate-single-table.3: 10	✅	✅	❌	✅
2	sum-clause	✅	💪 workout	aggregate-single-table.0: 10	✅	✅	❌	✅
3	count-clause	✅	💪 workout	aggregate-single-table.0: 10	✅	✅	❌	✅
4	min-and-max-clauses	✅	💪 workout	aggregate-single-table.1: 10	✅	✅	❌	✅
5	distinct-clause	✅	💪 workout	aggregate-single-table.0: 10	✅	✅	❌	✅
6	avg-clause	✅	💪 workout	aggregate-single-table.0: 10	✅	✅	❌	✅

Exercises:

no	name	type	aspects	standards	done
1	sql-practice-aggregate-single-table	sqlfiddle	👶 introduction 💪 workout 🦑 deep	read-single-table.2: 1000 aggregate-single-table.0: 1000	✅
2	sql-practice-aggregate-single-table-2	sqlfiddle	👶 introduction 💪 workout 🦑 deep	aggregate-single-table.0: 1000	✅
3	sql-practice-aggregate-table-min-and-max	sqlfiddle	👶 introduction 💪 workout 🦑 deep	aggregate-single-table.1: 1000	✅
4	sql-practice-aggregate-table-max-filter	sqlfiddle	👶 introduction 💪 workout 🦑 deep	aggregate-single-table.1: 1000	✅
5	sql-practice-aggregate-group	sqlfiddle	👶 introduction 💪 workout 🦑 deep	aggregate-single-table.0: 1000 aggregate-single-table.3: 1000	✅
6	sql-practice-aggregate-average	sqlfiddle	👶 introduction 💪 workout 🦑 deep	aggregate-single-table.1: 1000 aggregate-single-table.3: 1000	✅

Game:

⚠️ There is no game in this workout.

6. joins

Insights:

no	name	content	aspects	standards	PQ	RQ	Quiz	done
1	inner-join	✅	👶 introduction 💪 workout	read-multiple-tables.0: 10	✅	✅	✅	✅
2	left-and-right-joins	✅	👶 introduction 💪 workout	read-multiple-tables.0: 10	✅	✅	✅	✅
3	full-join	✅	🦑 deep 💪 workout	read-multiple-tables.0: 10	✅	✅	✅	✅
4	multiple-joins	✅	💪 workout 🦑 deep	read-multiple-tables.0: 10 read-multiple-tables.1: 10	✅	✅	✅	✅
5	union	✅	💪 workout 🦑 deep	read-multiple-tables.0: 10	✅	✅	✅	✅

Exercises:

no	name	type	aspects	standards	done
1	sql-practice-join-multiple-tables	sqlfiddle	💪 workout 🦑 deep	read-multiple-tables.0: 1000 aggregate-multiple-tables.0: 1000	✅
2	sql-practice-average-aggregate-multiple-tables	sqlfiddle	💪 workout 🦑 deep	read-multiple-tables.0: 1000 aggregate-multiple-tables.0: 1000	✅
3	sql-practice-max-aggregate-multiple-tables	sqlfiddle	💪 workout 🦑 deep	read-multiple-tables.0: 1000 aggregate-multiple-tables.0: 1000	✅
4	sql-practice-min-max-multiple-tables	sqlfiddle	💪 workout 🦑 deep	read-multiple-tables.0: 1000 aggregate-multiple-tables.0: 1000	✅

Game:

⚠️ There is no game in this workout.

7. database-clients

Insights:

no	name	content	aspects	standards	PQ	RQ	Quiz	done
1	connection-strings	✅	💪 workout	connect-client.0: 10 connect-client.1: 10 connect-client.2: 10 connect-client.3: 10	✅	✅	❌	✅
2	gui-client	✅	💪 workout	connect-client.0: 10 connect-client.1: 10	✅	✅	❌	✅
3	command-line-client	✅	💪 workout	connect-client.0: 10 connect-client.1: 10	✅	✅	❌	✅
4	python-database-packages	✅	💪 workout	connect-client.0: 10 connect-client.1: 10	✅	✅	❌	✅
5	node-database-packages	✅	💪 workout	connect-client.0: 10 connect-client.1: 10	✅	✅	❌	✅
6	java-database-packages	✅	💪 workout	connect-client.0: 10 connect-client.1: 10	✅	✅	❌	✅

Exercises:

⚠️ There are no insights in this workout.

Game:

⚠️ There is no game in this workout.

Standards

✅ - At least one insight covers this

❌ - Nothing covers this

🛠️ - This standard has no objectives yet

✅ Connect a database client to a database server

0. ✅ Connect any database client to a database server on a local host
1. ✅ Connect any database client to a database server on a remote host
2. ✅ Break down connection URIs into their component elements (protocol, hostname, credentials, port, database name, etc)
3. ✅ Assemble connection URIs from component elements

✅ Write queries to read data from single tables

0. ✅ Use the SELECT command to get data from a table
1. ✅ Use the WHERE clause to filter rows of data from a table
2. ✅ Filter the columns returned by a SELECT command
3. ✅ Use the ORDER BY command to sort a result set by the value of a column, or multiple columns

❌ Write queries to read data from multiple related tables

0. ✅ Use INNER, LEFT, RIGHT, OUTER, and JOIN to relate data across multiple tables
1. ✅ Write a query to relate two tables using a join table
2. ❌ Write a query to relate a table to itself using a join table

❌ Write queries to aggregate data from a single table

0. ✅ Use the COUNT, SUM, AVG functions to reduce a set to a single value
1. ✅ Use the MAX, MIN functions to search for the boundaries of set of data
2. ❌ Combine multiple columns' values in the aggregation of a set of data
3. ✅ Use GROUP BY to aggregate data in subsets of data

❌ Write queries to aggregate data from multiple related tables

0. ❌ Aggregate data using the GROUP BY statement and the COUNT, SUM, and AVG to collate information across related tables
1. ❌ Use MAX and MIN to look at the boundaries of a subset of data that is distributed across multiple tables
2. ❌ Use columns from related tables to aggregate a compound value

❌ Write queries to insert, update or delete data in a single table

0. ✅ Insert a single row of data using the INSERT command
1. ❌ Insert multiple rows of data using the INSERT command
2. ❌ Populate a table with the INSERT INTO command
3. ✅ Update 1 or more columns of data with the UPDATE command
4. ❌ Use a WHERE clause with the UPDATE command to conditionally update rows of data
5. ✅ Delete data with the DELETE command
6. ❌ Use a WHERE clause with the DELETE command to conditionally delete rows of data

Aspects

Given the insights are tagged with aspects, we can filter over the linear content progression and create learning sub-paths.

These sub-path progressions will most likely not cover all content, but they will ensure and enforce an unified learning experience, tailor for the user wish.

For example, a user might be interested in new additions and updates of a language, rather than introduction lessions. Note that these sub-paths don't take games into consideration

👶 Introduction

If you are being introduced to the topic for the first time

Insights:

1. what-is-sql
2. what-is-an-rdbms
3. what-is-a-database-server
4. database-server-types
5. what-is-a-database-client
6. select-all
7. select-cols
8. select-where
9. select
10. where
11. where-2
12. order-by-clause
13. insert
14. update-clause
15. delete-clause
16. inner-join
17. left-and-right-joins

Exercises:

1. sql-practice-what-is-sql
2. sql-practice-select-1
3. sql-practice-select-2
4. sql-practice-where-comparison
5. sql-practice-select
6. sql-practice-order-results
7. sql-practice-string-matching
8. sql-practice-insert
9. sql-practice-update
10. sql-practice-delete
11. sql-practice-aggregate-single-table
12. sql-practice-aggregate-single-table-2
13. sql-practice-aggregate-table-min-and-max
14. sql-practice-aggregate-table-max-filter
15. sql-practice-aggregate-group
16. sql-practice-aggregate-average

⚠️ There are no games tagged with this aspect in dql.

💪 Workout

Theory put into practice/that’s how you achieve X points

Insights:

1. select
2. where
3. where-2
4. where-3
5. order-by-clause
6. insert
7. update-clause
8. delete-clause
9. group-by-clause
10. sum-clause
11. count-clause
12. min-and-max-clauses
13. distinct-clause
14. avg-clause
15. inner-join
16. left-and-right-joins
17. full-join
18. multiple-joins
19. union
20. connection-strings
21. gui-client
22. command-line-client
23. python-database-packages
24. node-database-packages
25. java-database-packages

Exercises:

1. sql-practice-select
2. sql-practice-order-results
3. sql-practice-string-matching
4. sql-practice-update
5. sql-practice-delete
6. sql-practice-aggregate-single-table
7. sql-practice-aggregate-single-table-2
8. sql-practice-aggregate-table-min-and-max
9. sql-practice-aggregate-table-max-filter
10. sql-practice-aggregate-group
11. sql-practice-aggregate-average
12. sql-practice-join-multiple-tables
13. sql-practice-average-aggregate-multiple-tables
14. sql-practice-max-aggregate-multiple-tables
15. sql-practice-min-max-multiple-tables

⚠️ There are no games tagged with this aspect in dql.

🦑 Deep

Prerequisite knowledge consisting of 2 or more 👶/💪 workouts

Insights:

1. full-join
2. multiple-joins
3. union

Exercises:

1. sql-practice-aggregate-single-table
2. sql-practice-aggregate-single-table-2
3. sql-practice-aggregate-table-min-and-max
4. sql-practice-aggregate-table-max-filter
5. sql-practice-aggregate-group
6. sql-practice-aggregate-average
7. sql-practice-join-multiple-tables
8. sql-practice-average-aggregate-multiple-tables
9. sql-practice-max-aggregate-multiple-tables
10. sql-practice-min-max-multiple-tables

⚠️ There are no games tagged with this aspect in dql.

✨ New

Recently added/gained traction feature

⚠️ There are no insights tagged with this aspect in dql. :warning: There are no exercises tagged with this aspect in dql. :warning: There are no games tagged with this aspect in dql.

🔮 Obscura

Stories, obscure details that don’t specifically relate to a learning objective

⚠️ There are no insights tagged with this aspect in dql. :warning: There are no exercises tagged with this aspect in dql. :warning: There are no games tagged with this aspect in dql.

✅ All content has been tagged with aspects.