Survey and Introduction to AND, UPDATE, and JOIN

First, let’s spend 10 minutes to complete a short survey and learn about AND, UPDATE, and JOIN in SQL!

Generally I have felt secure about attempting computer programming problems.
• 1. Strongly disagree
• 2. Disagree
• 3. Neither agree nor disagree
• 4. Agree
• 5. Strongly agree

I am sure I could do advanced work in computer science.
• 1. Strongly disagree
• 2. Disagree
• 3. Neither agree nor disagree
• 4. Agree
• 5. Strongly agree

I am sure that I can learn programming.
• 1. Strongly disagree
• 2. Disagree
• 3. Neither agree nor disagree
• 4. Agree
• 5. Strongly agree

I think I could handle more difficult programming problems.
• 1. Strongly disagree
• 2. Disagree
• 3. Neither agree nor disagree
• 4. Agree
• 5. Strongly agree

I can get good grades in computer science.
• 1. Strongly disagree
• 2. Disagree
• 3. Neither agree nor disagree
• 4. Agree
• 5. Strongly agree

I have a lot of self-confidence when it comes to programming.
• 1. Strongly disagree
• 2. Disagree
• 3. Neither agree nor disagree
• 4. Agree
• 5. Strongly agree

AND Keyword

Here are Alice’s bookkeeping entries in the table bookkeeping:

Sometimes we want to select data with multiple constraints, and that can be done using AND.

DROP TABLE IF EXISTS bookkeeping; create table "bookkeeping" ("date" DATE, "category" TEXT, "item" TEXT, "price" FLOAT); INSERT INTO bookkeeping (date,category,item,price) VALUES ('2022-10-27', 'electronics', 'keyboard', '98.8'), ('2022-10-30', 'food_drink', 'boba', '5.2'), ('2022-11-06', 'food_drink', 'ramen', '15'), ('2022-11-09', 'food_drink', 'boba', '5.5'); ^^^^ -- The following statement selects the "date", "item", and "price" of entries -- that are in the "food_drink" "category", and "price" is over 10. SELECT date, item, price FROM bookkeeping WHERE category = "food_drink" AND price > 10

UPDATE Keyword

With the same table bookkeeping:

Sometimes we want to update certain records.

DROP TABLE IF EXISTS bookkeeping; create table "bookkeeping" ("date" DATE, "category" TEXT, "item" TEXT, "price" FLOAT); INSERT INTO bookkeeping (date,category,item,price) VALUES ('2022-10-27', 'electronics', 'keyboard', '98.8'), ('2022-10-30', 'food_drink', 'boba', '5.2'), ('2022-11-06', 'food_drink', 'ramen', '15'), ('2022-11-09', 'food_drink', 'boba', '5.5'); ^^^^ -- The following statement updates the entry which "item" is "keyboard", -- and sets its price to 100. UPDATE bookkeeping SET price = 100 WHERE item = "keyboard"; -- The following statement updates all entries which "item" are "boba", -- and add 2 to their current price. UPDATE bookkeeping SET price = price + 2 WHERE item = "boba"; -- The following line selects all data to display the change. SELECT * FROM bookkeeping

JOIN Keyword

Consider as a publishing company, we have two table authors and books:

Sometimes we want to merge the information from different tables.

DROP TABLE IF EXISTS authors; create table "authors" ("id" INTEGER, "name" TEXT, "nationality" TEXT); INSERT INTO authors (id,name,nationality) VALUES ('37', 'Ellen Writer', 'Americans'), ('15', 'Mu Li', 'Chinese'), ('24', 'Frank Schmidt', 'Germans'); DROP TABLE IF EXISTS books; create table "books" ("id" INTEGER, "title" TEXT, "author_id" INTEGER); INSERT INTO books (id,title,author_id) VALUES ('1', 'Introduction to Python', '37'), ('2', 'European Fairy Tales', '24'), ('3', 'Healthy Food at Home', '15'), ('4', 'Modern AI Applications', '37'); ^^^^ -- The following statement will join information of the two tables by matching the "author_id" -- column of the "books" table and the "id" column of the "authors" table. SELECT books.title, authors.name, authors.nationality FROM books JOIN authors ON books.author_id = authors.id

What to do next

Click on the following link to go the practice problems: Practice Problems

You have attempted of activities on this page