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>