Lab Assignment 8 ----------------------------------------------------------------------------- Step1: mysql> describe students; +--------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------+--------------+------+-----+---------+-------+ | student_ID | varchar(30) | NO | | NULL | | | first_Name | varchar(30) | NO | | NULL | | | middle_Name | varchar(30) | YES | | NULL | | | last_Name | varchar(30) | NO | | NULL | | | email | varchar(100) | NO | | NULL | | | course_Name | varchar(30) | NO | | NULL | | | course_Description | varchar(255) | NO | | NULL | | | marks | double | NO | | NULL | | +--------------------+--------------+------+-----+---------+-------+ mysql> show tables; +-----------------+ | Tables_in_li992 | +-----------------+ | students | +-----------------+ -------------------------------------------------------------------------------- Step2: 1. mysql> select * from students where first_Name = "Ahmed"; +------------+------------+-------------+-----------+-------------------------+-------------+----------------------------------+-------+ | student_ID | first_Name | middle_Name | last_Name | email | course_Name | course_Description | marks | +------------+------------+-------------+-----------+-------------------------+-------------+----------------------------------+-------+ | 200266200 | Ahmed | Al | Abbad | alabba200cs.uregina.ca | CS100 | Introduction to Computers | 90.5 | | 200266200 | Ahmed | Al | Abbad | alabba200cs.uregina.ca | CS110 | Programming and Problem Solving | 85 | | 200266200 | Ahmed | Al | Abbad | alabba200@cs.uregina.ca | CS210 | Data Structures and Abstractions | 90 | +------------+------------+-------------+-----------+-------------------------+-------------+----------------------------------+-------+ mysql> update students set email = "alabba200@cs.uregina.ca" where student_ID = 200266200; Query OK, 2 rows affected (0.00 sec) Rows matched: 3 Changed: 2 Warnings: 0 mysql> select * from students where first_Name = "Ahmed"; +------------+------------+-------------+-----------+-------------------------+-------------+----------------------------------+-------+ | student_ID | first_Name | middle_Name | last_Name | email | course_Name | course_Description | marks | +------------+------------+-------------+-----------+-------------------------+-------------+----------------------------------+-------+ | 200266200 | Ahmed | Al | Abbad | alabba200@cs.uregina.ca | CS100 | Introduction to Computers | 90.5 | | 200266200 | Ahmed | Al | Abbad | alabba200@cs.uregina.ca | CS110 | Programming and Problem Solving | 85 | | 200266200 | Ahmed | Al | Abbad | alabba200@cs.uregina.ca | CS210 | Data Structures and Abstractions | 90 | +------------+------------+-------------+-----------+-------------------------+-------------+----------------------------------+-------+ 2. mysql> select * from students where first_Name = "Yupeng"; +------------+------------+-------------+-----------+--------------------+-------------+----------------------------------+-------+ | student_ID | first_Name | middle_Name | last_Name | email | course_Name | course_Description | marks | +------------+------------+-------------+-----------+--------------------+-------------+----------------------------------+-------+ | 200266210 | Yupeng | Xu | | x210@cs.uregina.ca | CS100 | Introduction to Computers | 89 | | 200266210 | Yupeng | Xu | | x210@cs.uregina.ca | CS210 | Data Structures and Abstractions | 92 | +------------+------------+-------------+-----------+--------------------+-------------+----------------------------------+-------+ mysql> update students set middle_Name="", last_Name="Xu" where student_ID = 200266210; Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0 mysql> select * from students where first_Name = "Yupeng"; +------------+------------+-------------+-----------+--------------------+-------------+----------------------------------+-------+ | student_ID | first_Name | middle_Name | last_Name | email | course_Name | course_Description | marks | +------------+------------+-------------+-----------+--------------------+-------------+----------------------------------+-------+ | 200266210 | Yupeng | | Xu | x210@cs.uregina.ca | CS100 | Introduction to Computers | 89 | | 200266210 | Yupeng | | Xu | x210@cs.uregina.ca | CS210 | Data Structures and Abstractions | 92 | +------------+------------+-------------+-----------+--------------------+-------------+----------------------------------+-------+ 3. mysql> select * from students where course_Name = "CS215"; +------------+------------+-------------+-------------+--------------------------+-------------+----------------------------------+-------+ | student_ID | first_Name | middle_Name | last_Name | email | course_Name | course_Description | marks | +------------+------------+-------------+-------------+--------------------------+-------------+----------------------------------+-------+ | 200266202 | Qaswar | | Rai | ali202@cs.uregina.ca | CS215 | Web Oriented Programming | 82.6 | | 200266203 | Nicholas | | Phongsavath | phosan203@cs.uregina.ca | CS215 | Web Oriented Programming | 100 | | 200266206 | John | | Erick | eric20j206@cs.uregina.ca | CS215 | Web Oriented Programming | 83 | | 200266207 | Jody-Lee | Van | DerVelden | vanervj207@cs.uregina.ca | CS215 | Web Oriented Programming | 85.6 | | 200266209 | Ke | | Tao | taoke209@cs.uregina.ca | CS215 | Data Structures and Abstractions | 86.5 | | 200266212 | Mandeep | | Ghotra | gho212@cs.uregina.ca | CS215 | Web Oriented Programming | 98 | +------------+------------+-------------+-------------+--------------------------+-------------+----------------------------------+-------+ mysql> update students set course_Description="Web and Database Programming" where course_Name ="CS215"; Query OK, 6 rows affected (0.00 sec) Rows matched: 6 Changed: 6 Warnings: 0 mysql> select * from students where course_Name = "CS215"; +------------+------------+-------------+-------------+--------------------------+-------------+------------------------------+-------+ | student_ID | first_Name | middle_Name | last_Name | email | course_Name | course_Description | marks | +------------+------------+-------------+-------------+--------------------------+-------------+------------------------------+-------+ | 200266202 | Qaswar | | Rai | ali202@cs.uregina.ca | CS215 | Web and Database Programming | 82.6 | | 200266203 | Nicholas | | Phongsavath | phosan203@cs.uregina.ca | CS215 | Web and Database Programming | 100 | | 200266206 | John | | Erick | eric20j206@cs.uregina.ca | CS215 | Web and Database Programming | 83 | | 200266207 | Jody-Lee | Van | DerVelden | vanervj207@cs.uregina.ca | CS215 | Web and Database Programming | 85.6 | | 200266209 | Ke | | Tao | taoke209@cs.uregina.ca | CS215 | Web and Database Programming | 86.5 | | 200266212 | Mandeep | | Ghotra | gho212@cs.uregina.ca | CS215 | Web and Database Programming | 98 | +------------+------------+-------------+-------------+--------------------------+-------------+------------------------------+-------+ 4. mysql> select * from students where course_Name = "CS000"; +------------+------------+-------------+-------------+--------------------------+-------------+------------------------------+-------+ | student_ID | first_Name | middle_Name | last_Name | email | course_Name | course_Description | marks | +------------+------------+-------------+-------------+--------------------------+-------------+------------------------------+-------+ | 200266209 | Ke | | Tao | taoke209@cs.uregina.ca | CS000 | 00000000000000000000000 | 0 | +------------+------------+-------------+-------------+--------------------------+-------------+------------------------------+-------+ mysql> delete from students where course_Name="CS000"; Query OK, 1 row affected (0.00 sec) mysql> select * from students where course_Name = "CS000"; Empty set (0.00 sec) --------------------------------------------------------------------------------------------------------------------------------------------------------- Step3: 1. mysql> create table Student_Info( student_ID varchar(30) NOT NULL, first_Name varchar(30) NOT NULL, middle_Name varchar(30), last_Name varchar(30) NOT NULL, email varchar(100) NOT NULL, PRIMARY KEY (student_ID)); mysql> show tables; +-----------------+ | Tables_in_li992 | +-----------------+ | Student_Info | | students | +-----------------+ mysql> describe Student_Info; +-------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+-------+ | student_ID | varchar(30) | NO | PRI | NULL | | | first_Name | varchar(30) | NO | | NULL | | | middle_Name | varchar(30) | YES | | NULL | | | last_Name | varchar(30) | NO | | NULL | | | email | varchar(100) | NO | | NULL | | +-------------+--------------+------+-----+---------+-------+ 2. mysql> create table Courses( course_Name varchar(30) NOT NULL, course_Description varchar(255) NOT NULL, PRIMARY KEY(course_Name)); Query OK, 0 rows affected (0.00 sec) mysql> show tables; +-----------------+ | Tables_in_li992 | +-----------------+ | Courses | | Student_Info | | students | +-----------------+ mysql> describe Courses; +--------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------+--------------+------+-----+---------+-------+ | course_Name | varchar(30) | NO | PRI | NULL | | | course_Description | varchar(255) | NO | | NULL | | +--------------------+--------------+------+-----+---------+-------+ 3. mysql> create table Marks( student_ID varchar(30) NOT NULL, course_Name varchar(30) NOT NULL, marks real NOT NULL, PRIMARY KEY(student_ID, course_Name),FOREIGN KEY(student_ID) REFERENCES Student_Info(student_ID), FOREIGN KEY(course_Name) REFERENCES Courses(course_Name)); Query OK, 0 rows affected (0.01 sec) mysql> show tables; +-----------------+ | Tables_in_li992 | +-----------------+ | Courses | | Marks | | Student_Info | | students | +-----------------+ mysql> describe Marks; +-------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+-------+ | student_ID | varchar(30) | NO | PRI | NULL | | | course_Name | varchar(30) | NO | PRI | NULL | | | marks | double | NO | | NULL | | +-------------+-------------+------+-----+---------+-------+ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Step4: mysql> insert into Student_Info(student_ID,first_Name,middle_Name,last_Name,email) select distinct student_ID, first_Name,middle_Name,last_Name,email from students; Query OK, 12 rows affected (0.00 sec) mysql> select * from Student_Info; +------------+------------+-------------+-------------+--------------------------+ | student_ID | first_Name | middle_Name | last_Name | email | +------------+------------+-------------+-------------+--------------------------+ | 200266200 | Ahmed | Al | Abbad | alabba200@cs.uregina.ca | | 200266202 | Qaswar | | Rai | ali202@cs.uregina.ca | | 200266203 | Nicholas | | Phongsavath | phosan203@cs.uregina.ca | | 200266204 | Nikki | | Pawlowski | pawlow204@cs.uregina.ca | | 200266205 | Nathan | | Ellis | elli205@cs.uregina.ca | | 200266206 | John | | Erick | eric20j206@cs.uregina.ca | | 200266207 | Jody-Lee | Van | DerVelden | vanervj207@cs.uregina.ca | | 200266208 | Sabrina | | Tram | tra200s208@cs.uregina.ca | | 200266209 | Ke | | Tao | taoke209@cs.uregina.ca | | 200266210 | Yupeng | | Xu | x210@cs.uregina.ca | | 200266211 | Chidinma | | Ukabam | ukaba211@cs.uregina.ca | | 200266212 | Mandeep | | Ghotra | gho212@cs.uregina.ca | +------------+------------+-------------+-------------+--------------------------+ mysql> insert into Courses(course_Name, course_Description) select distinct course_Name, course_Description from students; Query OK, 4 rows affected (0.00 sec) mysql> select * from Courses; +-------------+----------------------------------+ | course_Name | course_Description | +-------------+----------------------------------+ | CS100 | Introduction to Computers | | CS110 | Programming and Problem Solving | | CS210 | Data Structures and Abstractions | | CS215 | Web and Database Programming | +-------------+----------------------------------+ mysql> insert into Marks(student_ID, course_Name, marks) select distinct student_ID,course_Name,marks from students; Query OK, 30 rows affected (0.01 sec) mysql> select * from Marks; +------------+-------------+-------+ | student_ID | course_Name | marks | +------------+-------------+-------+ | 200266200 | CS100 | 90.5 | | 200266200 | CS110 | 85 | | 200266200 | CS210 | 90 | | 200266202 | CS100 | 72.5 | | 200266202 | CS110 | 59.2 | | 200266202 | CS215 | 82.6 | | 200266203 | CS100 | 100 | | 200266203 | CS215 | 100 | | 200266204 | CS100 | 65 | | 200266204 | CS210 | 92.5 | | 200266205 | CS110 | 92.5 | | 200266205 | CS210 | 70.2 | | 200266206 | CS100 | 86 | | 200266206 | CS110 | 87 | | 200266206 | CS210 | 81 | | 200266206 | CS215 | 83 | | 200266207 | CS100 | 59 | | 200266207 | CS110 | 50.2 | | 200266207 | CS210 | 67 | | 200266207 | CS215 | 85.6 | | 200266208 | CS100 | 70 | | 200266208 | CS110 | 80 | | 200266208 | CS210 | 88.5 | | 200266209 | CS110 | 66.5 | | 200266209 | CS210 | 77.5 | | 200266209 | CS215 | 86.5 | | 200266210 | CS100 | 89 | | 200266210 | CS210 | 92 | | 200266211 | CS100 | 99 | | 200266212 | CS215 | 98 | +------------+-------------+-------+