Exercises:

Use the commands above to complete the following tasks, and submit the SQL statements. Real-world examples must be distinct from those used in the text.

1. List the commands for adding, updating, and deleting data.
    Adding: INSERT INTO
    Updating: UPDATE
    Deleting: DELETE
    Alter Table: ALTER TABLE

2. Explain the structure for each type of command.
    Adding: INSERT INTO (name of table) VALUES (values to be inserted)
    Updating: UPDATE (name of table) SET (the column and values to be updated) WHERE (what table row)
    Deleting: DELETE FROM (name of table) WHERE (where/what values should be removed)
    Alter Table: ALTER TABLE (name of table) DROP COLUMN (what column to drop)
                 ALTER TABLE (name of table) ADD COLUMN (what column to add)

 3. What are some of the data types that can be used in tables? Give a real-world example of each type.
      An inventory list for a bar/restaurant that tracks the food items (text), how many of each item (integer), when they were delivered/how long they've been on the shelf (date/time) and how much they cost (number)

 4. Decide how to create a new table to hold a list of people invited to a wedding dinner. The table needs to have first and last names, whether they sent in their RSVP, the number of guests they are bringing, and the number of meals (1 for adults and 1/2 for children).
    a) Which data type would you use to store each of the following pieces of information?
          First and last name. => text
          Whether they sent in their RSVP. => text
          Number of guests. => integer
          Number of meals. => numeric

    b) Write a command that creates the table to track the wedding dinner.
          CREATE TABLE dinner (
          name text,
          rsvp text,
          guests integer,
          meals numeric
          );

     c) Write a command that adds a column to track whether the guest sent a thank you card.
          ALTER TABLE dinner ADD COLUMN thankyou_cards text;

     d) You have decided to move the data about the meals to another table, so write a command to remove the column storing the number meals from the wedding table.
          ALTER TABLE dinner DROP COLUMN meals;

     e) The guests will need a place to sit at the reception, so write a command that adds a column for table number.
          ALTER TABLE dinner ADD COLUMN table_number integer;

     f) The wedding is over and we do not need to keep this information, so write a command that deletes the table numbers from the database.
          ALTER TABLE dinner DROP COLUMN table_number;

  5. Write a command to create a new table to hold the books in a library with the columns ISBN, title, author, genre, publishing date, number of copies, and available copies.
          CREATE TABLE books(
          ISBN bigint,
          title text,
          genre text,
          publish_date date,
          copies integer
          copies_available integer
          );

      a) Find three books and add their information to the table.
            INSERT INTO books(ISBN, title, genre, publish_date, copies, copies_available) VALUES
              (9780465050659, 'The Design of Everyday Things', 'Non-Fiction', '2013-01-01', 5, 2),
              (9780141033570, 'Thinking, Fast and Slow', 'Non-Fiction', '2011-01-01' 6, 3),
              (9780385480017, 'Bird By Bird', 'Non-Fiction', '1994-01-01' 3, 3);

      b) Someone has just checked out one of the books. Change the number of available copies to 1 fewer.
          UPDATE books SET copies_available=2 WHERE title='The Design of Everyday Things';

      c) Now one of the books has been added to the banned books list. Remove it from the table.
          DELETE FROM books WHERE title='Thinking, Fast and Slow';

  6. Write a command to make a new table to hold spacecrafts. Information should include id, name, year launched, country of origin, a brief description of the mission, orbiting body, if it is currently operating, and its approximate miles from Earth. In addition to the table creation, provide commands that perform the following operations:
          CREATE TABLE spacecrafts(
          id integer,
          name text,
          launch_year integer,
          origin text,
          description text,
          orbiting_body text,
          operational text,
          miles float
          );

       a) Add three non-Earth-orbiting satellites to the table.
            INSERT INTO spacecrafts VALUES
              (111,'Starship 1', 1991,'USA', 'Life Mission', 'Saturn', 'yes', 2000000000.55),
              (222,'Starship 2', 1992,'Canada', 'Life Mission', 'Pluto', 'no', 3330000000000.55),
              (333,'Starship 3', 1993,'Russia', 'Life Mission', 'Moon', 'yes', 4440000000.55);

        b) Remove one of the satellites from the table since it has just crashed into the planet.
            DELETE FROM spacecrafts WHERE id=222;

        c) Edit another satellite because it is no longer operating and change the value to reflect that.
             UPDATE spacecrafts SET value='no' WHERE id=111;

  7. Write a command to create a new table to hold the emails in your inbox. This table should include an id, the subject line, the sender, any additional recipients, the body of the email, the timestamp, whether or not you have read the email, and the id of the email chain it's in. Also provide commands that perform the following operations:
      CREATE TABLE inbox(
      id integer,
      subject text,
      sender text,
      recipients text,
      body text,
      timestamp timestamp,
      read text,
      id_email integer
       );

     a) Add three new emails to the inbox.
          INSERT INTO inbox VALUES
          (111, 'Yo!', 'Bob', 'Diana', 'Hey What's Up?', '2019-01-17 15:30:00', 'yes', 1111),
          (222, 'Hey!', 'Mary', 'Diana', 'Group Project', '2019-01-20 09:15:00', 'yes', 2222),
          (333, 'Hi', 'Sam', 'Diana', 'Ok sounds good', '2019-01-22 14:00:00', 'no', 3333);

     b) You deleted one of the emails, so write a command to remove the row from the inbox table.
          DELETE from inbox WHERE id=111;

     c)  You started reading an email but just heard a crash in another room. Mark the email as unread before investigating the crash, so you can come back and read it later.
            UPDATE inbox SET read='no' WHERE id=333;