Basics of Data Operations (Insert/Update and the Relational Operations for Retrieval)
標準~25 min5 terms14 past questions
Goals of this unit
Tell apart the write operations that change a table's contents (insert and update) from the relational operations that build a result table from a table.
Distinguish selection from projection by checking whether the result table has fewer rows or fewer columns.
Explain how tables are joined through a common column and combined with conditions to pull out the data you need.
Insert (挿入) is the operation that writes a new row into a table.
Book No.
Title
Stock
Status
B01
Intro to Databases
3
Existing row
B02
Spreadsheet Basics
1
Existing row
B03
Intro to Networks
5
Row added by insert
A table in a relational database is made of columns (fields), which represent attributes, and rows (records), each holding the data for one entry. In SQL, the INSERT statement adds a row by giving a value for each column. The primary key (主キー) column, which identifies a single row, must get a value that no other row already has.
AnalogyLike adding a line for a first-time customer to a shop's visitor list.
How to rememberThe kanji 挿 in 挿入 means to slip something in, which matches the picture of slipping a new row into a table.
Exam points
Mix-upInsert (挿入) is a write operation that changes the contents of the table itself, a different job from a relational operation (関係演算), which builds a result table from the original one.
Update (更新) is the operation that replaces column values in a row already in the table with new values.
Book No.
Title
Stock
Status
B01
Intro to Databases
3
No change
B02
Spreadsheet Basics
Changed from 1 to 4
Row rewritten by update
B03
Intro to Networks
5
No change
In SQL, the UPDATE statement names the columns to rewrite and their new values, and the condition in the WHERE clause narrows down the target rows. Run it without a condition and the specified columns change in every row of the table.
AnalogyLike erasing and rewriting just the phone number of a contact whose number has changed.
How to rememberThe word update is built from up and date, which fits an operation that brings values up to date.
Exam points
Mix-upInsert (挿入) adds rows to the table, while update (更新) keeps the number of rows and changes the values in existing rows.
Selection (選択) is the operation that takes the rows meeting a condition out of a table to form a new table.
Book No.
Title
Stock
Condition: stock of 4 or more
B01
Intro to Databases
3
No match → dropped
B02
Spreadsheet Basics
4
Match → kept
B03
Intro to Networks
5
Match → kept
A relational operation (関係演算) is a retrieval operation that takes tables as input and returns a table as its result. A selection's result has the same columns as the original, with only the rows that meet the condition left (zero rows if none match). In SQL, the condition written in the WHERE clause plays the role of selection.
AnalogyLike copying out only the lines for people absent today from an attendance register.
How to rememberRelational algebra writes it as σ (sigma), the Greek letter that matches the s of selection.
Exam points
ExamConditions linked with AND keep only the rows that satisfy all of them, while conditions linked with OR keep rows that satisfy at least one. When AND and OR are mixed, parentheses decide which group is evaluated first.
ExamIn a string condition, % stands for any run of characters, including none, and _ stands for exactly one character. For example, "%山" matches values ending in 山, and "山%" matches values starting with 山.
Projection (射影) is the relational operation that keeps only the columns you need from a table to form a new table.
Book No. (dropped column)
Title (kept column)
Stock (kept column)
B01
Intro to Databases
3
B02
Spreadsheet Basics
4
B03
Intro to Networks
5
Projection puts no condition on the rows and takes the values of the specified columns from every row. In SQL, the column names listed right after SELECT play the role of projection.
AnalogyLike cutting just the name and total-score columns out of a printed grade sheet and keeping them.
How to rememberRelational algebra writes it as π (pi), the Greek letter that matches the p of projection.
Exam points
ExamRelational operations (関係演算) can be applied one after another. Applying a projection (射影) to a table whose rows were narrowed by a condition gives a result that lists only the items you need.
ExamApplying a projection (射影) after linking tables removes columns the result does not need, such as the common column used for matching.
Mix-upSelection (選択) uses a condition to cut the number of rows, and projection (射影) uses column names to cut the number of columns.
A join (結合) is a relational operation that links two or more tables by pairing rows whose values in a common column (共通の列) are equal, combining them into one table.
Loan table and book table (both have a book number column)
Match up rows that have the same book number
Loan list with the columns of both tables
The columns of both tables line up in one row, giving a table that shows the title for every loan.
3 / 3
The link is usually a pair made of one table's primary key (主キー) and the foreign key (外部キー) in the other table that holds those values. Because the result table carries the columns of both tables, items that exist in only one of them can be handled together.
AnalogyLike checking the product number on an order slip against the same number in a catalog and writing the product name beside it.
How to rememberRelational algebra writes it as ⋈, a bow-tie shape that looks like the left and right tables tied together in the middle.
Exam points
ExamTo bring in items from another table with a join (結合), this table also needs a common column (共通の列) whose values can be matched against that table, usually a foreign key (外部キー) that corresponds to the other table's primary key (主キー).
ExamIn a joined (結合) table, rows can be selected (選択) by the values of columns that originally belonged to separate tables.
Mix-upIntersection (積) collects the rows found in both of two tables with the same column structure, which works differently from a join (結合), which uses column values to link separate tables side by side.