1. List the commands for adding, updating, and deleting data.

INSERT INTO…VALUES…, UPDATE...SET..., DELETE FROM...WHERE


2. Explain the structure for each type of command.

INSERT INTO populates the table with values
UPDATE updates the values in the table, both SET and WHERE can take multiple clauses.
DELETE FROM removes data from the table, this action is irreversible.


3. What are some of the data types that can be used in tables? Give a real-world example of each type.

There are several of data types used in tables for example numbers (decimal, numeric, float, money, date...), text (character, text, char...), date & time (date, timestamp...)


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).

- 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. - boolean
   - Number of guests. - numeric
   - Number of meals. - numeric

- Write a command that creates the table to track the wedding dinner.

CREATE TABLE dinner (
   name text,
   rsvp boolean,
   guests_num int,
   meals_num int
);

- Write a command that adds a column to track whether the guest sent a thank you card.

ALTER TABLE dinner ADD COLUMN thank_card boolean SET DEFAULT ‘no’;

- 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_num;

- 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_num int;

- 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_num;


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 library_inventory (
   isbn int,
   title text,
   author text,
   genre text,
   publishing_date date,
   book_quantity int,
   book_available int,
);

- Find three books and add their information to the table.

INSERT INTO library_inventory (isbn, title, author, genre, publishing_date, book_quantity, book_available) VALUES
(15196, ‘Harry Potter’, ‘J. K. Rowling’, 2010/06/10, 120, 5),
(29849, ‘Where The Crawdads Sing’, ‘Delia Owens’, 2006/08/11, 13, 10),
(38726, ‘Alaskan Holiday’, ‘Debbie Macomber’, 2002/01/20, 10, 2);

- Someone has just checked out one of the books. Change the number of available copies to 1 fewer.

UPDATE library_inventory SET book_available=9 WHERE isbn=29849;

- Now one of the books has been added to the banned books list. Remove it from the table.

DELETE FROM library_inventory WHERE isbn=38726;


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 spacecraft (
   id int,
   name text,
   year date,
   country text,
   mission text,
   orbiting_body text,
   is_operating boolean,
   distance_earth float
);

- Add three non-Earth-orbiting satellites to the table.

INSERT INTO spacecraft (id, name, year, country, mission, orbiting_body, is_operating, distance_earth) VALUES
  (256,'Apollo 1', 1991,'USA', 'Satellite communication mission', 'Saturn', 'yes', 2000000000.55),
  (398,'Apollo 2', 1992,'Canada', 'Life research mission', 'Uranus', 'no', 3330000000.55),
  (593,'Apollo 3', 1993,'Russia', 'Water research mission', 'Moon', 'yes', 4440000000.55);

- Remove one of the satellites from the table since it has just crashed into the planet.

DELETE FROM spacecraft WHERE id=398;

- Edit another satellite because it is no longer operating and change the value to reflect that.

UPDATE spacecraft SET is_operating=’no’ WHERE id=256


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 int,
   subject text,
   sender text,
   recipients text,
   body text,
   timestamp timestamp,
   is_read boolean,
   chain_id int
);

- Add three new emails to the inbox.

INSERT INTO inbox (id, subject, sender, recipients, body, timestamp, is_read, chain_id) VALUES
 (100, 'Hello', 'Matt','Valery', 'Hello Evgheni!', '2018-10-03 09:20:05', 'no',1000),
 (101, 'Hey', 'Chris','Brad', 'Hello Evgheni!', '2018-10-04 11:30:13', 'no',200),
 (102, 'What’s up', 'Sarah','Kevin', 'Hello Evgheni!', '2018-10-05 01:48:06', 'yes',658);

- You deleted one of the emails, so write a command to remove the row from the inbox table.

DELETE FROM inbox WHERE id=100;

- 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 is_read=’no’ WHERE id=102;