Week 4

Things to know & do in week 4.

  • The total workload for this course is 140 hours. In week 4, we expect you to spend 4 hours on lectures, 4 hours in the lab, 7 hours on a mix of things (quiz, required readings, peer reviews, additional lab work, etc.),**and3 hours** on the preparation for the midterm exam next week.
  • This week we offer an additional werkcollege on server-side JavaScript (node.js); time & location: Tuesday at 15.45 in lecture hall Boole (length: 90 minutes)
  • Get started on this week's lab assignment (A4) and quiz.
  • Groups in cluster C1 are assessed this week on Assignments A2 & A3. Make sure you reserve yourself an assessment spot and don't forget to upload your solution via the "Lab Submissions" link here on Blackboard. We use Doodle to arrange assessment spots (use your group name as participant name).

Lecture 7

  • sql-1.pdf

The PDF slides cover the introduction to database programming and interaction with the SQL language. The slides cover a good amount of topics; possibly, we will also take some time in Lecture 8. Slides will be modified according to the actual schedule.

Required reading

In Chapter 4 of the Database book:

  • 4.3 Basic Retrieval Queries in SQL
  • 5.1.1 Comparison involving NULL and three-valued logic
  • 5.1.5 Explicit Sets and Renaming of Attributes in SQL
  • 5.1.6 Joined Tables in SQL and Outer Joins

Recommended activities

Perform the following SQL Zoo Tutorials

  • SELECT basics [HTML]
  • SELECT from World [HTML]
  • SELECT from Nobel [HTML]
  • JOIN [HTML]
  • More JOIN operations [HTML]
  • Using NULL [HTML]
  • Selft Join [HTML]

Recommended Readings

The followings are a set of online resources that can be useful to better understand the SQL syntax and semantics.

  • SQL For Web Nerds. Section 3: Simple Queries [HTML]
  • A Visual Explanation of SQL Joins [HTML]

Lecture 8

  • sql-8.pdf

In this lecture we continue our exploration of the SQL language. We will cover again JOIN queries, and then look at aggregation functions. If time allows, we will also see how SQL can be used as a DDL.

Required reading

In Chapter 4 of the Database book:

  • 5.1.6 Joined Tables in SQL and Outer Joins
  • 5.1.7 Aggregated Functions in SQL
  • 5.1.8 Grouping: the GROUP BY and HAVING Clauses
  • 5.4 Schema Change Statements in SQL

Recommended activities

Perform the following SQL Zoo Tutorials

  • JOIN [HTML]
  • More JOIN operations [HTML]
  • Selft Join [HTML]
  • SUM and COUNT [HTML]

Recommended Readings

The followings are a set of online resources that can be useful to better understand the SQL syntax and semantics.

  • MySQL SQL Syntax Reference
  • SQL For Web Nerds. Section 3: Simple Queries [HTML]
  • SQL For Web Nerds. Section 4: Complex Queries [HTML]
  • A Visual Explanation of SQL Joins [HTML]

Quiz W4

The quiz can be attempted once. Its content covers the lectures of week 4.

Quizzes are individual work!

Deadline: December 7, 2pm.

Lab assignment A4

Labs are team work. Make sure both team members can answer the questions of the lab assignment(s) during the assessment session - it is not the team that passes/fails the assessment, but individual team members!

Video Recording. Lecture 7 and 8: Introduction to SQL

To help you studying, we pre-recorded the core lecture content; the recordings slightly differ from the actual lecture material presented in class. Make sure to check the actual lecture slides of the class, do not only rely on the video recordings!

Introduction to SQL. The SELECT Statement.

ORDER BY, JOIN, Aggregation

SQL as DDL and DML language

Werkcollege: exercises and solution

  • questions_with_solutions_Node_Express_AJAX.pdf
  • questions_with_solutions_SQL.pdf