Database:Adding rows with new data - SQL INSERT

From Juneday education
Jump to: navigation, search

Meta

Expand using link to the right to see the meta section with instructor's notes.

Description

This chapter introduces the SQL INSERT statement for inserting rows of data into a table.

Prepare

Expect this lecture to take around 10 minutes. Again, if you require your students to see the video lecture in advance, you will be able to go faster and can spend more time on questions and answers, interacting with your students.

See the videos and presentation, and review the exercises and suggested solutions online.

Topics addressed or used in the online exercises

  • Creating a directory and a database with a simple (initially empty) table
  • Running a bash script to generate data with random parts, e.g. cars with a random license number, random color etc
  • Using output from a script to add data to a table in a database
    • Re-visiting changing permissions to make a script executable, e.g.:
      $ chmod u+x somescript.sh
    • Re-visting redirection of stdout and stdin in bash
  • Inserting data via the INSERT statement using the full syntax with explicit stating of the columns
  • Inserting data via the INSERT statement using the shorter syntax with the columns (and their order) implicit by only stating the actual data
  • Re-visiting the .schema command to investigate a table’s column names and “order” of the columns
  • The fact that inserting data for only a few of the columns results in NULL values in the colums for which no data was supplied
    • The .nullvalue command to make SQLite display e.g. NULL instead of
    • The IS operator for checking for NULL, e.g. ... WHERE color IS NULL
  • Re-visting SELECT COUNT(*)
  • “Check-your-progress” questions are included with suggested solutions on a different page

Heads-up

Think about:

  • NULL seems, in our experience, to be something some students find confusing. Note that SQLite3 displays nothing (an empty string) for null values by default, but it might help to change this using the .nullvalue command, to display e.g. NULL (similar to the default in for instance MariaDB)
  • INSERT is a good way to explain and introduce NULL (even if we have a dedicated lecture about NULL) since leaving out parts (columns) of a new row of data must mean that those columns will not contain any data (it was left out!).
  • We still use bash and the command line - expect that some questions regarding scripts and redirection etc

Full frontal - Code example up-front!

sqlite> INSERT INTO eu_member(country) VALUES('Croatia');

Introduction

SQlite syntax diagram

This chapter introduces the SQL INSERT statement for inserting rows of data into a table. Data is often produced very quickly, so databases tend to grow fast. Whatever we feel about this, we need to learn how to insert new data into tables.

Even if we have a slow-growing database, like a registry of employees, we need to learn the syntax for inserting new records, when for instance a new person is employed. This is what is shown and trained in this chapter and the exercises on the next page.

Watch the videos, read the slides, and follow the external links and read the resources there before you move on to the exercises on INSERT in the next chapter.

Links

Swedish videos

Note that the videos are in Swedish for now, but the slides are in English. We plan to add English videos in the future.

  • Databaser 06 - SQL - INSERT - Lägga till rader med data (Swe)
  • Databaser 06 - SQL - INSERT - Lägga till rader med data livekodning (Swe)
  • Presentation PDF (PDF)

English videos

Further reading

Where to go next

The next page has exercises for SQL INSERT.

« PreviousBook TOCNext »