mysql> select * from Students limit 5;
+----+------------------+--------------+
| Id | Name             | Major        |
+----+------------------+--------------+
|  1 | Rudolph Attia    | Mathematics  |
|  2 | Golda Singer     | Software Eng |
|  3 | Francina Dorie   | Mathematics  |
|  4 | Carola Davis     | English      |
|  5 | Kathaleen Copass | English      |
+----+------------------+--------------+
5 rows in set (0.00 sec)

mysql> select * from Courses;
+----------+--------------------------+
| Id       | Name                     |
+----------+--------------------------+
| BIO 161  | Intro Molecular Biology  |
| CSC 307  | Software Engineering     |
| CSC 357  | Systems Programming      |
| CSC 365  | Intro to Databases       |
| CSC 453  | Operating Systems        |
| MATH 206 | Linear Algebra           |
| MATH 248 | Theory of Proofs         |
| STAT 305 | Probability Theory       |
| STAT 312 | Statistics for Engineers |
+----------+--------------------------+
9 rows in set (0.01 sec)

mysql> select * from Rosters limit 5;
+---------+----------+---------+------+-------+
| Student | Course   | Quarter | Year | Grade |
+---------+----------+---------+------+-------+
|       1 | BIO 161  | Winter  | 2016 | C     |
|       1 | CSC 307  | Fall    | 2016 | C     |
|       1 | CSC 365  | Fall    | 2016 | B     |
|       1 | CSC 453  | Fall    | 2016 | C     |
|       1 | MATH 206 | Fall    | 2016 | B     |
+---------+----------+---------+------+-------+
5 rows in set (0.00 sec)

mysql> select count(*) from Rosters;
+----------+
| count(*) |
+----------+
|      222 |
+----------+
1 row in set (0.00 sec)

mysql> select major, count(*) from Students group by major;
+------------------+----------+
| major            | count(*) |
+------------------+----------+
| Biology          |        6 |
| Comp Eng         |        4 |
| Computer Science |        4 |
| English          |        8 |
| Mathematics      |        5 |
| Software Eng     |        6 |
| Statistics       |        8 |
+------------------+----------+
7 rows in set (0.00 sec)

mysql> create table majors as select major, count(*) from Students group by major;
Query OK, 7 rows affected (0.01 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> select * from maojrs;
ERROR 1146 (42S02): Table 'viewdemo.maojrs' doesn't exist
mysql> select * from majors;
+------------------+----------+
| major            | count(*) |
+------------------+----------+
| Biology          |        6 |
| Comp Eng         |        4 |
| Computer Science |        4 |
| English          |        8 |
| Mathematics      |        5 |
| Software Eng     |        6 |
| Statistics       |        8 |
+------------------+----------+
7 rows in set (0.00 sec)

mysql> select "count(*)" from majors; 
+----------+
| count(*) |
+----------+
| count(*) |
| count(*) |
| count(*) |
| count(*) |
| count(*) |
| count(*) |
| count(*) |
+----------+
7 rows in set (0.01 sec)

mysql> select `count(*)` from majors; 
+----------+
| count(*) |
+----------+
|        6 |
|        4 |
|        4 |
|        8 |
|        5 |
|        6 |
|        8 |
+----------+
7 rows in set (0.00 sec)

mysql> select * from majors;
+------------------+----------+
| major            | count(*) |
+------------------+----------+
| Biology          |        6 |
| Comp Eng         |        4 |
| Computer Science |        4 |
| English          |        8 |
| Mathematics      |        5 |
| Software Eng     |        6 |
| Statistics       |        8 |
+------------------+----------+
7 rows in set (0.01 sec)

mysql> insert into Students Values(50,'Joe Smith', 'Statistics');
Query OK, 1 row affected (0.01 sec)

mysql> select major, count(*) from Students group by major;
+------------------+----------+
| major            | count(*) |
+------------------+----------+
| Biology          |        6 |
| Comp Eng         |        4 |
| Computer Science |        4 |
| English          |        8 |
| Mathematics      |        5 |
| Software Eng     |        6 |
| Statistics       |        9 |
+------------------+----------+
7 rows in set (0.00 sec)

mysql> select * from majors;
+------------------+----------+
| major            | count(*) |
+------------------+----------+
| Biology          |        6 |
| Comp Eng         |        4 |
| Computer Science |        4 |
| English          |        8 |
| Mathematics      |        5 |
| Software Eng     |        6 |
| Statistics       |        8 |
+------------------+----------+
7 rows in set (0.00 sec)

mysql> create view majview as select major, count(*) from Students group by major;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+--------------------+
| Tables_in_viewdemo |
+--------------------+
| Courses            |
| Rosters            |
| Students           |
| cs                 |
| csc365             |
| majors             |
| majview            |
| mimiSchedule       |
+--------------------+
8 rows in set (0.00 sec)

mysql> describe majview;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| major    | varchar(16) | YES  |     | NULL    |       |
| count(*) | bigint(21)  | NO   |     | 0       |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> select * from majview;
+------------------+----------+
| major            | count(*) |
+------------------+----------+
| Biology          |        6 |
| Comp Eng         |        4 |
| Computer Science |        4 |
| English          |        8 |
| Mathematics      |        5 |
| Software Eng     |        6 |
| Statistics       |        9 |
+------------------+----------+
7 rows in set (0.01 sec)

mysql> insert into Students values(51, 'Bill Nye','English');
Query OK, 1 row affected (0.02 sec)

mysql> select count(*) from Students;
+----------+
| count(*) |
+----------+
|       43 |
+----------+
1 row in set (0.00 sec)

mysql> select * from majview;
+------------------+----------+
| major            | count(*) |
+------------------+----------+
| Biology          |        6 |
| Comp Eng         |        4 |
| Computer Science |        4 |
| English          |        9 |
| Mathematics      |        5 |
| Software Eng     |        6 |
| Statistics       |        9 |
+------------------+----------+
7 rows in set (0.01 sec)

mysql> select major, count(*) from Students group by major;
+------------------+----------+
| major            | count(*) |
+------------------+----------+
| Biology          |        6 |
| Comp Eng         |        4 |
| Computer Science |        4 |
| English          |        9 |
| Mathematics      |        5 |
| Software Eng     |        6 |
| Statistics       |        9 |
+------------------+----------+
7 rows in set (0.00 sec)

mysql> update majors set major = 'Physics' where major ='Biology';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> drop view majorview;
ERROR 1051 (42S02): Unknown table 'majorview'
mysql> drop view majview;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+--------------------+
| Tables_in_viewdemo |
+--------------------+
| Courses            |
| Rosters            |
| Students           |
| cs                 |
| csc365             |
| majors             |
| mimiSchedule       |
+--------------------+
7 rows in set (0.00 sec)

mysql> select * from csc365;
+------------------+------------------+---------+------+-------+
| Name             | Major            | quarter | year | grade |
+------------------+------------------+---------+------+-------+
| Rudolph Attia    | Mathematics      | Fall    | 2016 | B     |
| Golda Singer     | Software Eng     | Fall    | 2016 | C     |
| Francina Dorie   | Mathematics      | Winter  | 2016 | B     |
| Carola Davis     | English          | Winter  | 2016 | D     |
| Kathaleen Copass | English          | Fall    | 2016 | A     |
| Philomena Knotek | English          | Winter  | 2016 | B     |
| Caridad Ellerbe  | Comp Eng         | Fall    | 2016 | A     |
| Mimi Pallazzo    | Statistics       | Winter  | 2016 | C     |
| Valda Czekaj     | Statistics       | Winter  | 2016 | B     |
| Moises Degeston  | English          | Winter  | 2016 | D     |
| Claire Goulding  | Software Eng     | Fall    | 2016 | A     |
| Donetta Latigo   | Statistics       | Fall    | 2016 | D     |
| Belkis Buchs     | English          | Fall    | 2016 | C     |
| Boyd Constantino | English          | Winter  | 2016 | C     |
| Ella Pazderski   | Comp Eng         | Winter  | 2016 | F     |
| Kurt Vanasse     | Software Eng     | Fall    | 2016 | D     |
| Manual Barn      | Statistics       | Winter  | 2016 | A     |
| Irwin Griess     | Comp Eng         | Spring  | 2016 | C     |
| Matha Kimball    | Computer Science | Winter  | 2016 | A     |
| Ileana Parara    | Software Eng     | Spring  | 2016 | A     |
| Shawana Thruman  | Software Eng     | Spring  | 2016 | B     |
| Lee Granfield    | Biology          | Fall    | 2016 | F     |
| Jeanie Kleekamp  | Computer Science | Winter  | 2016 | B     |
| Otis Colins      | Comp Eng         | Winter  | 2016 | F     |
| Chadwick Dalley  | Statistics       | Winter  | 2016 | F     |
| Dante Hermosilla | Biology          | Spring  | 2016 | C     |
| Lorraine Ehrhard | Software Eng     | Winter  | 2016 | B     |
+------------------+------------------+---------+------+-------+
27 rows in set (0.00 sec)

mysql> show create view csc365;
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View   | Create View                                                                                                                                                                                                                                                                                                              | character_set_client | collation_connection |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| csc365 | CREATE ALGORITHM=UNDEFINED DEFINER=`alex`@`%` SQL SECURITY DEFINER VIEW `csc365` AS select `s`.`Name` AS `Name`,`s`.`Major` AS `Major`,`r`.`Quarter` AS `quarter`,`r`.`Year` AS `year`,`r`.`Grade` AS `grade` from (`Students` `s` join `Rosters` `r`) where ((`r`.`Student` = `s`.`Id`) and (`r`.`Course` = 'CSC 365')) | latin1               | latin1_swedish_ci    |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.00 sec)

mysql> 
mysql> 
mysql> 
mysql> show tables;
+--------------------+
| Tables_in_viewdemo |
+--------------------+
| Courses            |
| Rosters            |
| Students           |
| cs                 |
| csc365             |
| majors             |
| mimiSchedule       |
+--------------------+
7 rows in set (0.01 sec)

mysql> select * from mimiSchedule;
+----------+---------+------+-------+
| Course   | Quarter | Year | Grade |
+----------+---------+------+-------+
| CSC 365  | Winter  | 2016 | C     |
| CSC 453  | Spring  | 2016 | B     |
| CSC 453  | Winter  | 2016 | D     |
| MATH 206 | Fall    | 2016 | B     |
| MATH 248 | Winter  | 2016 | B     |
+----------+---------+------+-------+
5 rows in set (0.00 sec)

mysql> select * from Students where Id=5;
+----+------------------+---------+
| Id | Name             | Major   |
+----+------------------+---------+
|  5 | Kathaleen Copass | English |
+----+------------------+---------+
1 row in set (0.00 sec)

mysql> create view kschedule as
    -> select course, quarter, year,grade 
    -> from Rosters
    -> where Student = (select id from Students where name = 'Kathaleen Copass');
Query OK, 0 rows affected (0.01 sec)

mysql> select * from kschedule;
+----------+---------+------+-------+
| course   | quarter | year | grade |
+----------+---------+------+-------+
| BIO 161  | Spring  | 2016 | D     |
| CSC 307  | Spring  | 2016 | D     |
| CSC 365  | Fall    | 2016 | A     |
| STAT 305 | Spring  | 2016 | D     |
+----------+---------+------+-------+
4 rows in set (0.00 sec)

mysql> insert into kschedule values('STAT 305', 'Spring', 2016, 'B');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`viewdemo`.`Rosters`, CONSTRAINT `Rosters_ibfk_1` FOREIGN KEY (`Student`) REFERENCES `Students` (`Id`))
mysql> select * from Rosters where Student = 5;
+---------+----------+---------+------+-------+
| Student | Course   | Quarter | Year | Grade |
+---------+----------+---------+------+-------+
|       5 | BIO 161  | Spring  | 2016 | D     |
|       5 | CSC 307  | Spring  | 2016 | D     |
|       5 | CSC 365  | Fall    | 2016 | A     |
|       5 | STAT 305 | Spring  | 2016 | D     |
+---------+----------+---------+------+-------+
4 rows in set (0.01 sec)

mysql> show tables;
+--------------------+
| Tables_in_viewdemo |
+--------------------+
| Courses            |
| Rosters            |
| Students           |
| cs                 |
| csc365             |
| kschedule          |
| majors             |
| mimiSchedule       |
+--------------------+
8 rows in set (0.00 sec)

mysql> select * from cs;
+----+------------------+------------------+
| Id | Name             | Major            |
+----+------------------+------------------+
| 24 | Matha Kimball    | Computer Science |
| 25 | Cinderella Bread | Computer Science |
| 32 | Jeanie Kleekamp  | Computer Science |
| 42 | Grant Hoffman    | Computer Science |
+----+------------------+------------------+
4 rows in set (0.00 sec)

mysql> insert into cs values(55,'Bob Jones','Computer Science');
Query OK, 1 row affected (0.01 sec)

mysql> select * from cs;
+----+------------------+------------------+
| Id | Name             | Major            |
+----+------------------+------------------+
| 24 | Matha Kimball    | Computer Science |
| 25 | Cinderella Bread | Computer Science |
| 32 | Jeanie Kleekamp  | Computer Science |
| 42 | Grant Hoffman    | Computer Science |
| 55 | Bob Jones        | Computer Science |
+----+------------------+------------------+
5 rows in set (0.00 sec)

mysql> select * from Students;
+----+--------------------+------------------+
| Id | Name               | Major            |
+----+--------------------+------------------+
|  1 | Rudolph Attia      | Mathematics      |
|  2 | Golda Singer       | Software Eng     |
|  3 | Francina Dorie     | Mathematics      |
|  4 | Carola Davis       | English          |
|  5 | Kathaleen Copass   | English          |
|  6 | Rodger Frazell     | Biology          |
|  7 | Philomena Knotek   | English          |
|  8 | Caridad Ellerbe    | Comp Eng         |
|  9 | Mimi Pallazzo      | Statistics       |
| 10 | Louann Elmblad     | Mathematics      |
| 11 | Valda Czekaj       | Statistics       |
| 12 | Moises Degeston    | English          |
| 13 | Claire Goulding    | Software Eng     |
| 14 | Donetta Latigo     | Statistics       |
| 15 | Dwana Ahlquist     | Biology          |
| 16 | Ezequiel Swieca    | Biology          |
| 17 | Belkis Buchs       | English          |
| 18 | Boyd Constantino   | English          |
| 19 | Ella Pazderski     | Comp Eng         |
| 20 | Kurt Vanasse       | Software Eng     |
| 21 | Manual Barn        | Statistics       |
| 22 | Genaro Shimada     | Mathematics      |
| 23 | Irwin Griess       | Comp Eng         |
| 24 | Matha Kimball      | Computer Science |
| 25 | Cinderella Bread   | Computer Science |
| 26 | Millard Escoe      | Statistics       |
| 27 | Ileana Parara      | Software Eng     |
| 28 | Shawana Thruman    | Software Eng     |
| 29 | Lee Granfield      | Biology          |
| 30 | Kylie Simpon       | Mathematics      |
| 31 | Shelton Studyvance | English          |
| 32 | Jeanie Kleekamp    | Computer Science |
| 33 | Otis Colins        | Comp Eng         |
| 34 | Chadwick Dalley    | Statistics       |
| 35 | Truman Ebberts     | Statistics       |
| 36 | Floria Fornicola   | English          |
| 37 | Pablo Carrisalez   | Statistics       |
| 38 | Dante Hermosilla   | Biology          |
| 39 | Lorraine Ehrhard   | Software Eng     |
| 40 | Hal Beisser        | Biology          |
| 42 | Grant Hoffman      | Computer Science |
| 50 | Joe Smith          | Statistics       |
| 51 | Bill Nye           | English          |
| 55 | Bob Jones          | Computer Science |
+----+--------------------+------------------+
44 rows in set (0.01 sec)

mysql>