CREATE TABLE command:

CREATE TABLE contact_info (
	contact_id 	INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
	email 		VARCHAR(50),
	full_name	VARCHAR(50),
	Phone_number	VARCHAR(20),
	subject		VARCHAR(100),
	message		VARCHAR(250)
);

mysql> describe contact_info;
+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| email        | varchar(50)  | YES  |     | NULL    |       |
| full_name    | varchar(50)  | YES  |     | NULL    |       |
| Phone_number | varchar(20)  | YES  |     | NULL    |       |
| subject      | varchar(100) | YES  |     | NULL    |       |
| message      | varchar(250) | YES  |     | NULL    |       |
+--------------+--------------+------+-----+---------+-------+
5 rows in set (0.03 sec)

CREATE TABLE authors (authorid INTEGER PRIMARY KEY AUTO_INCREMENT, firstName VARCHAR(50), lastName VARCHAR(50));

mysql> desc authors;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| authorid  | int(11)     | NO   | PRI | NULL    | auto_increment |
| firstName | varchar(50) | YES  |     | NULL    |                |
| lastName  | varchar(50) | YES  |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
3 rows in set (0.02 sec)

CREATE TABLE authorISBN (authorID INTEGER REFERENCES authors(authorid), isbn VARCHAR(50));

mysql> DESCRIBE authorISBN;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| authorID | int(11)     | YES  |     | NULL    |       |
| isbn     | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

CREATE TABLE Employee (
    Number INT UNSIGNED PRIMARY KEY,
    Name VARCHAR(50),
    Department INT UNSIGNED,
    Salary INT UNSIGNED ,
    Location VARCHAR(50)
);

mysql> desc Employee;
+------------+------------------+------+-----+---------+-------+
| Field      | Type             | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+-------+
| Number     | int(10) unsigned | NO   | PRI | NULL    |       |
| Name       | varchar(50)      | YES  |     | NULL    |       |
| Department | int(10) unsigned | YES  |     | NULL    |       |
| Salary     | int(10) unsigned | YES  |     | NULL    |       |
| Location   | varchar(50)      | YES  |     | NULL    |       |
+------------+------------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

CREATE TABLE Department (
    Department INT UNSIGNED PRIMARY KEY,
    Location VARCHAR(50));

mysql> desc Department;
+------------+------------------+------+-----+---------+-------+
| Field      | Type             | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+-------+
| Department | int(10) unsigned | NO   | PRI | NULL    |       |
| Location   | varchar(50)      | YES  |     | NULL    |       |
+------------+------------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql>


CREATE TABLE titles (
    isbn VARCHAR(50) PRIMARY KEY,
    title VARCHAR(50),
    editionNumber INT UNSIGNED NOT NULL,
    copyright INT UNSIGNED NOT NULL);

mysql> describe titles;
+---------------+------------------+------+-----+---------+-------+
| Field         | Type             | Null | Key | Default | Extra |
+---------------+------------------+------+-----+---------+-------+
| isbn          | varchar(50)      | NO   | PRI | NULL    |       |
| title         | varchar(50)      | YES  |     | NULL    |       |
| editionNumber | int(10) unsigned | NO   |     | NULL    |       |
| copyright     | int(10) unsigned | NO   |     | NULL    |       |
+---------------+------------------+------+-----+---------+-------+
4 rows in set (0.01 sec)


INSERT INTO command:

INSERT INTO contact_info (email, full_name, phone_number, subject, message)
	VALUES ('jane_dow@yahoo.com', 'Jane Doe', '555-1234', 'Test', 'Test, test, test, 123 Test!');

INSERT INTO contact_info (email, full_name, phone_number, subject, message)
	VALUES ('bob@timlin.net', 'Bob Timlin', '555-1212', 'Happy Thanksgiving!', 'Don''t eat too much Turkey!');

INSERT INTO contact_info (email, full_name, phone_number, subject, message)
	VALUES ('aaa@yahoo.com', 'Adam Smith', '555-1212', 'Hello World!', 'Good Bye!');
INSERT INTO contact_info (email, full_name, phone_number, subject, message)
	VALUES ('chrissmith@gmail.com', 'Chris Smith', '333-4444', 'Hello World!', 'These are the times that try men''s souls.');


INSERT INTO authors (authorId, firstName, lastName) VALUES (1, 'Harvey', 'Deitel');
INSERT INTO authors (firstName, lastName) VALUES ('Paul', 'Deitel');
INSERT INTO authors (firstName, lastName) VALUES ('Andrew', 'Goldberg');
INSERT INTO authors (firstName, lastName) VALUES ('David', 'Choffines');

INSERT INTO authorISBN (authorId, isbn) VALUES (1, '0131869000');
INSERT INTO authorISBN (authorId, isbn) VALUES (2, '0131869000');
INSERT INTO authorISBN (authorId, isbn) VALUES (1, '0131483986');
INSERT INTO authorISBN (authorId, isbn) VALUES (2, '0131483986');
INSERT INTO authorISBN (authorId, isbn) VALUES (1, '0131450913');
INSERT INTO authorISBN (authorId, isbn) VALUES (2, '0131450913');
INSERT INTO authorISBN (authorId, isbn) VALUES (1, '0131828274');
INSERT INTO authorISBN (authorId, isbn) VALUES (2, '0131828274');
INSERT INTO authorISBN (authorId, isbn) VALUES (3, '0131450913');
INSERT INTO authorISBN (authorId, isbn) VALUES (4, '0131828274');

INSERT INTO Employee (Number, Name, Department, Salary, Location)
    VALUES (23603, 'Jones', 413, 1100, 'New Jersey');
INSERT INTO Employee (Number, Name, Department, Salary, Location)
    VALUES (24568, 'Kerwin', 413, 2000, 'New Jersey');
INSERT INTO Employee (Number, Name, Department, Salary, Location)
    VALUES (34589, 'Larson', 642, 1800, 'Los Angeles');
INSERT INTO Employee (Number, Name, Department, Salary, Location)
    VALUES (35761, 'Myers', 611, 1400, 'Orlando');
INSERT INTO Employee (Number, Name, Department, Salary, Location)
    VALUES (47132, 'Neumann', 413, 9000, 'New Jersey');
INSERT INTO Employee (Number, Name, Department, Salary, Location)
    VALUES (78321, 'Stephens', 611, 8500, 'Orlando');


INSERT INTO Department (Department, Location) VALUES (413, 'New Jersey');
INSERT INTO Department (Department, Location) VALUES (611, 'Orlando');
INSERT INTO Department (Department, Location) VALUES (642, 'Los Angeles');


INSERT INTO titles (isbn, title, editionNumber, copyright)
    VALUES ('0131869000', 'Visual Basic How to Program', 3, 2006);
INSERT INTO titles (isbn, title, editionNumber, copyright)
    VALUES ('0131525239', 'Visual C# How to Program', 2, 2006);
INSERT INTO titles (isbn, title, editionNumber, copyright)
    VALUES ('0132222205', 'Java How to Program', 7, 2007);
INSERT INTO titles (isbn, title, editionNumber, copyright)
    VALUES ('0131857576', 'C++ How to Program', 5, 2005);
INSERT INTO titles (isbn, title, editionNumber, copyright)
    VALUES ('0132404168', 'C How to Program', 5, 2007);
INSERT INTO titles (isbn, title, editionNumber, copyright)
    VALUES ('0131450913', 'Internet and World Wide Web How to Program', 3, 2004);

SELECT command:

SELECT * FROM contact_info;

SELECT * FROM contact_info ORDER BY full_name;

SELECT full_name, email FROM contact_info
	ORDER BY full_name DESC;

SELECT * FROM contact_info
	WHERE email = 'bob@timlin.net';

SELECT * FROM contact_info
	WHERE phone_number = '555-1212';

SELECT * FROM contact_info
	WHERE phone_number LIKE '555%';
SELECT * FROM contact_info
	WHERE full_name LIKE '%Smith';
SELECT firstName, lastName, isbn
FROM authors INNER JOIN authorISBN ON authors.authorID = authorISBN.authorID
ORDER BY lastName, firstName;

UPDATE command:

UPDATE contact_info SET phone_number = '415-555-1212';

UPDATE contact_info SET email = 'Adam_Smith@yahoo.com' WHERE email = 'aaa@yahoo.com'

DELETE command:

DELETE FROM contact_info;  -- This will delete all rows, VERY DANGEROUS!!!

DELETE FROM contact_info WHERE email = 'bob@timlin.net';

DROP command:

DROP TABLE contact_info;

DROP TABLE authors;

DROP TABLE authorISBN;