drop database if exists Activity_4_1 ;
create database if Not exists Activity_4_1;
use Activity_4_1;

## First, the table definitions.  Note that they must be defined in
## this order, because of the references.
####################################################################
create table candy (
   candy_id INT NOT NULL primary key auto_increment,
   name VARCHAR(40) not null,
   price DEC(5,2),
   calories SMALLINT
) type=InnoDB;

create table vegetables (
   veg_id INT NOT NULL primary key auto_increment,
   name VARCHAR(40) not null
) type=InnoDB;

create table temp_candy (
   candy_id INT NOT NULL primary key auto_increment,
   name VARCHAR(40) not null,
   price DEC(5,2),
   calories SMALLINT
) type=InnoDB;

## Now we insert some row data. You will do more later...

INSERT INTO candy
	(name, price, calories)
   VALUES
	("Payday", .79, 120),
	("Snickers", .79, 150),
	("Milky Way", .74, 150),
	("M&Ms", .99, 150);

INSERT INTO vegetables
	(name)
   VALUES
	("Asparagus");

INSERT INTO temp_candy
	(name, price, calories)
   VALUES
	("Smarties", .49, 60);

#######  DO NOT CHANGE THE CODE ABOVE. ###########
#######  START YOUR CODE BELOW... ###########

## 1. Show databases, tables, columns from all tables and records from all tables.

## 2. Delete all the records from the temp_candy table: 

## 3. Add a description column to the candy table: 

## 4. Add 7 records of your choice to the candy table. Be original and creative with all values for a higher grade: 

## 5. Use INSERT...SELECT to copy all candy records to the temp_candy table:

## 6. From the temp_candy table, delete the candy whose id is 5: 

## 7. Get rid of the entire vegetables table:

## 8. In one statement, delete only the first 2 records with 150 calories from the original candy table:

## 9. Show tables and records from all tables again.