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

  INSERT INTO tasks (id, responsible, description, time)
  VALUES
  (1001, ‘Donald Duck’, ‘Draft Scope Document’, 2)
  (1002, ‘Mickey Mouse’, ’Design Front End’, 3)
  (1003, ‘Donald Duck’, ‘Database Requirements’, 5)
  (1004, ‘Minnie Mouse’, ‘Customer Requirement’, 2)
  (1005, ‘Goofy’, ‘Agile & Project Mgt’, 5)
  (1006, ‘Goofy’, ‘Javascript program’, 2)
  (1007, ‘Mickey Mouse’, ‘Alpha testing’, 3)
  (1008, ‘Minnie Mouse’, ‘Beta Testing’, 6)

UPDATE tasks SET time=4 WHERE	id=1001;

DELETE FROM tasks WHERE id=1007;


Explain the structure for each type of command.

Adding:

INSERT INTO: Fundamental SQL for inserting a row of data
tasks: the table that will have the inserted data
(id, responsible, description, time): The columns that are being inserted into the table
VALUES: describe the rows that will be entered that match the columns that where previously defined.


Updating
UPDATE tasks: Indicates what table will be updated using the SQL command
SET defines the column – time – that will be updated, and what the value of those selected columns will be
 SET time=4
WHERE id=1001; defines the scope of the changes – in this case, only the records where the id is equal to 1001. Since that is a single record, only that will be updated


Deleting
DELETE FROM tasks: The SQL command the indicate which table will undergo the DELETE function.
WHERE id=1007: defines the scope of the records to be deleted – in this case, only the records where the id is equal to 1007. Since that is a single record, only that will be deleted

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

Numbers, Text, Date & Time
Numbers: Inventory Counts, Reorder Points, Profits, nanometers, light-years

Text: Names, Quotes, tweets

Date & Time: Date of Birth, Atomic Clock Time, Mean Time Between Failures



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 - test
Whether they sent in their RSVP. boolean
Number of guests. number
Number of meals. number
Write a command that creates the table to track the wedding dinner.


CREATE TABLE guests (
      id integer,
      name text,
      rsvpstatus boolean,
 guest integer,
 meals number(2,1)
  );


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

ALTER TABLE guests ADD COLUMN thankyou boolean;


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 guests DROP COLUMN meals;

The guests will need a place to sit at the reception, so write a command that adds a column for table number.

ALTER TABLE guests ADD COLUMN table integer;


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 guests DROP COLUMN table;

************

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 integer,
      title text,
      author text,
	 genre text,
	 pubdate date,
	 inventory integer,
	 available integer,
  );


Find three books and add their information to the table.

INSERT INTO books (isbn, title, author, genre, pudate, inventory, available)
VALUES
(1974617041, ‘Addicted to Hopium’, ‘Kevin Kohls’, ‘Business’, September 5, 2017, 5, 2),
(9780884271956, ‘The Goal: A Process of Ongoing Improvement’, ‘Eliyahu M. Goldratt’, ‘Business & Money’, June 1, 2014, 3, 3),
(1594484805, ‘Drive: The Surprising Truth About What Motivates Us’, ‘Daniel H. Pink’, ‘Motivation & Self-Improvement’, April 5, 2011, 4, 1);




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

UPDATE books SET available=1 WHERE isbn=1974617041;

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

DELETE FROM books WHERE isbn=9780884271956;


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,
      year date,
	 country text,
	 missiondescript text,
	 orbitingbody text,
	 operating boolean,
	 distance numeric(6,1));


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

INSERT INTO spacecrafts
VALUES (id, name, year, country, missiondescript, orbitingbody, operating, distance)
(26734 ‘2001 Mars Odyssey’, 7 April 2001, 15:02:22 UTC, ‘United States’, Use spectrometers and a thermal imager to detect evidence of past or present water and ice’, ‘Mars’, TRUE, 33.9)

(10321 ‘Voyager 1’, September 5, 1977, 12:56:00 UTC, ‘United States’, ‘Flybys of Jupiter, Saturn, and Saturn's largest moon, Titan, ‘None’, TRUE, 13229.0)

(43592 ‘Parker Solar Probe, August 12, 2018, 07:31 UTC, ‘United States’, ‘Probe the outer corona of the Sun’, ‘Sun’, TRUE, 93.0)

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

DELETE FROM spacecrafts WHERE id=43592;


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

UPDATE spacecrafts SET operating = false WHERE id=43592;



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.


  CREATE TABLE emails (
      id integer,
      subject text,
      sender text,
	 ccs text,
	 body text,
	 timestamp date,
	 read boolean,
	 chain integer);


Also provide commands that perform the following operations:


Add three new emails to the inbox.

INSERT INTO emails
VALUES (id, subject, sender, ccs, body, timestamp, read, chain)
(1001, ‘SQL Assignment’, ‘kevinkohls@gmail.com’, null, ‘Are you still up working on this SQL stuff?’, 12/13/2018 @ 4:25am (UTC), true, null)

(1002, ‘FWD:SQL Assignment’, ‘kevinkohs@gmail.com’, hsteinfeld@gmail.com, ‘Because Hailee and I are, and we are lost. Text pls’, 12/13/2018 @ 4:28am (UTC), true, 1001)

(1003, ‘Never Mind!!!’, ‘kevinkohls@gmail.com’, ‘Jen@gmail.com’, ‘Never mind. Jenifer Lawrence and I are going out for pizza. Your loss, dude.’, 12/13/2018 @ 4:31am (UTC), false, 1002)



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

DELETE FROM emails WHERE id=1001;


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 emails SET read =false WHERE id=1002;