~/mysql >mysql --user=cs4010 --password=cs4010 --database=cs4010

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 124
Server version: 5.5.41-MariaDB MariaDB Server

Copyright (c) 2000, 2014, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [cs4010]> SET @name_test='Joe Jones';
Query OK, 0 rows affected (0.00 sec)

MariaDB [cs4010]> SELECT * FROM student WHERE  name=@name_test;
+------------+-----------+--------+------+
| student_id | name      | year   | test |
+------------+-----------+--------+------+
|          1 | Joe Jones | Senior |   85 |
+------------+-----------+--------+------+
1 row in set (0.00 sec)

MariaDB [cs4010]> source test3.sql
Query OK, 0 rows affected (0.00 sec)

+------------+-----------+--------+------+
| student_id | name      | year   | test |
+------------+-----------+--------+------+
|          3 | Sam Smith | Junior |   90 |
+------------+-----------+--------+------+
1 row in set (0.00 sec)

+------+
| test |
+------+
|   90 |
MariaDB [cs4010]> create table student_athlete as select * from student a,athlete b  where b.this_student=a.student_id; 

Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

MariaDB [cs4010]> select * from student_athlete;
+------------+-----------+--------+------+--------------+----------+
| student_id | name      | year   | test | this_student | sport    |
+------------+-----------+--------+------+--------------+----------+
|          3 | Sam Smith | Junior |   90 |            3 | Baseball |
+------------+-----------+--------+------+--------------+----------+
1 row in set (0.00 sec)

MariaDB [cs4010]> alter table student_athlete drop this_student;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

MariaDB [cs4010]> describe student_athlete;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| student_id | int(11)      | NO   |     | 0       |       |
| name       | varchar(255) | NO   |     | NULL    |       |
| year       | varchar(20)  | YES  |     | NULL    |       |
| test       | int(11)      | YES  |     | NULL    |       |
| sport      | varchar(255) | NO   |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

MariaDB [cs4010]> insert into student_athlete (student_id,name) values (3,"Joe Jones");
Query OK, 1 row affected, 1 warning (0.00 sec)

MariaDB [cs4010]> select * from student_athlete;
+------------+-----------+--------+------+----------+
| student_id | name      | year   | test | sport    |
+------------+-----------+--------+------+----------+
|          3 | Sam Smith | Junior |   90 | Baseball |
|          3 | Joe Jones | NULL   | NULL |          |
+------------+-----------+--------+------+----------+
2 rows in set (0.00 sec)

MariaDB [cs4010]> delete from student_athlete where name="Joe Jones";
Query OK, 1 row affected (0.00 sec)

MariaDB [cs4010]> select * from student_athlete;
+------------+-----------+--------+------+----------+
| student_id | name      | year   | test | sport    |
+------------+-----------+--------+------+----------+
|          3 | Sam Smith | Junior |   90 | Baseball |
+------------+-----------+--------+------+----------+
1 row in set (0.00 sec)

MariaDB [cs4010]> alter table student_athlete add primary key (student_id);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [cs4010]> describe student_athlete;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| student_id | int(11)      | NO   | PRI | 0       |       |
| name       | varchar(255) | NO   |     | NULL    |       |
| year       | varchar(20)  | YES  |     | NULL    |       |
| test       | int(11)      | YES  |     | NULL    |       |
| sport      | varchar(255) | NO   |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

MariaDB [cs4010]> insert into student_athlete (student_id,name) values (3,"Joe Jones");
ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'
MariaDB [cs4010]> insert into student_athlete (name) values ("Joe Jones");
Query OK, 1 row affected, 1 warning (0.00 sec)

MariaDB [cs4010]> select * from student_athlete;
+------------+-----------+--------+------+----------+
| student_id | name      | year   | test | sport    |
+------------+-----------+--------+------+----------+
|          0 | Joe Jones | NULL   | NULL |          |
|          3 | Sam Smith | Junior |   90 | Baseball |
+------------+-----------+--------+------+----------+
2 rows in set (0.00 sec)

MariaDB [cs4010]> alter table student_athlete modify student_id int NOT NULL AUTO_INCREMENT;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [cs4010]> describe student_athlete;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| student_id | int(11)      | NO   | PRI | NULL    | auto_increment |
| name       | varchar(255) | NO   |     | NULL    |                |
| year       | varchar(20)  | YES  |     | NULL    |                |
| test       | int(11)      | YES  |     | NULL    |                |
| sport      | varchar(255) | NO   |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

MariaDB [cs4010]> select * from student_athlete;
+------------+-----------+--------+------+----------+
| student_id | name      | year   | test | sport    |
+------------+-----------+--------+------+----------+
|          1 | Joe Jones | NULL   | NULL |          |
|          3 | Sam Smith | Junior |   90 | Baseball |
+------------+-----------+--------+------+----------+
2 rows in set (0.00 sec)

MariaDB [cs4010]> insert into student_athlete (name) values ("Joe Java");
Query OK, 1 row affected, 1 warning (0.00 sec)

MariaDB [cs4010]> select * from student_athlete;
+------------+-----------+--------+------+----------+
| student_id | name      | year   | test | sport    |
+------------+-----------+--------+------+----------+
|          1 | Joe Jones | NULL   | NULL |          |
|          3 | Sam Smith | Junior |   90 | Baseball |
|          4 | Joe Java  | NULL   | NULL |          |
+------------+-----------+--------+------+----------+
3 rows in set (0.00 sec)

MariaDB [cs4010]>