Name: Wyatt Drew Student Number: 200225999 ================================================= Step 1 ================================================= titan[5]% pwd /home/hercules/d/drew111w/php_web titan[6]% chmod 644 students.sql mysql> source students.sql Query OK, 0 rows affected, 1 warning (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 1 row affected (0.00 sec) Query OK, 1 row affected (0.00 sec) Query OK, 1 row affected (0.00 sec) Query OK, 1 row affected (0.00 sec) Query OK, 1 row affected (0.01 sec) Query OK, 1 row affected (0.00 sec) Query OK, 1 row affected (0.00 sec) Query OK, 1 row affected (0.00 sec) Query OK, 1 row affected (0.00 sec) Query OK, 1 row affected (0.00 sec) Query OK, 1 row affected (0.00 sec) Query OK, 1 row affected (0.00 sec) Query OK, 1 row affected (0.01 sec) Query OK, 1 row affected (0.00 sec) Query OK, 1 row affected (0.00 sec) Query OK, 1 row affected (0.00 sec) Query OK, 1 row affected (0.00 sec) Query OK, 1 row affected (0.00 sec) Query OK, 1 row affected (0.00 sec) Query OK, 1 row affected (0.00 sec) Query OK, 1 row affected (0.00 sec) Query OK, 1 row affected (0.00 sec) Query OK, 1 row affected (0.01 sec) Query OK, 1 row affected (0.00 sec) Query OK, 1 row affected (0.00 sec) Query OK, 1 row affected (0.00 sec) Query OK, 1 row affected (0.00 sec) Query OK, 1 row affected (0.00 sec) Query OK, 1 row affected (0.01 sec) Query OK, 1 row affected (0.00 sec) Query OK, 1 row affected (0.00 sec) 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 | | +--------------------+--------------+------+-----+---------+-------+ 8 rows in set (0.00 sec) mysql> select * from students; +------------+------------+-------------+-------------+--------------------------+-------------+----------------------------------+-------+ | 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 | | 200266202 | Qaswar | | Rai | ali202@cs.uregina.ca | CS100 | Introduction to Computers | 72.5 | | 200266202 | Qaswar | | Rai | ali202@cs.uregina.ca | CS110 | Programming and Problem Solving | 59.2 | | 200266202 | Qaswar | | Rai | ali202@cs.uregina.ca | CS215 | Web Oriented Programming | 82.6 | | 200266203 | Nicholas | | Phongsavath | phosan203@cs.uregina.ca | CS100 | Introduction to Computers | 100 | | 200266203 | Nicholas | | Phongsavath | phosan203@cs.uregina.ca | CS215 | Web Oriented Programming | 100 | | 200266204 | Nikki | | Pawlowski | pawlow204@cs.uregina.ca | CS100 | Introduction to Computers | 65 | | 200266204 | Nikki | | Pawlowski | pawlow204@cs.uregina.ca | CS210 | Data Structures and Abstractions | 92.5 | | 200266205 | Nathan | | Ellis | elli205@cs.uregina.ca | CS110 | Programming and Problem Solving | 92.5 | | 200266205 | Nathan | | Ellis | elli205@cs.uregina.ca | CS210 | Data Structures and Abstractions | 70.2 | | 200266206 | John | | Erick | eric20j206@cs.uregina.ca | CS100 | Introduction to Computers | 86 | | 200266206 | John | | Erick | eric20j206@cs.uregina.ca | CS110 | Programming and Problem Solving | 87 | | 200266206 | John | | Erick | eric20j206@cs.uregina.ca | CS210 | Data Structures and Abstractions | 81 | | 200266206 | John | | Erick | eric20j206@cs.uregina.ca | CS215 | Web Oriented Programming | 83 | | 200266207 | Jody-Lee | Van | DerVelden | vanervj207@cs.uregina.ca | CS100 | Introduction to Computers | 59 | | 200266207 | Jody-Lee | Van | DerVelden | vanervj207@cs.uregina.ca | CS110 | Programming and Problem Solving | 50.2 | | 200266207 | Jody-Lee | Van | DerVelden | vanervj207@cs.uregina.ca | CS210 | Data Structures and Abstractions | 67 | | 200266207 | Jody-Lee | Van | DerVelden | vanervj207@cs.uregina.ca | CS215 | Web Oriented Programming | 85.6 | | 200266208 | Sabrina | | Tram | tra200s208@cs.uregina.ca | CS100 | Introduction to Computers | 70 | | 200266208 | Sabrina | | Tram | tra200s208@cs.uregina.ca | CS110 | Programming and Problem Solving | 80 | | 200266208 | Sabrina | | Tram | tra200s208@cs.uregina.ca | CS210 | Data Structures and Abstractions | 88.5 | | 200266209 | Ke | | Tao | taoke209@cs.uregina.ca | CS110 | Programming and Problem Solving | 66.5 | | 200266209 | Ke | | Tao | taoke209@cs.uregina.ca | CS210 | Data Structures and Abstractions | 77.5 | | 200266209 | Ke | | Tao | taoke209@cs.uregina.ca | CS215 | Data Structures and Abstractions | 86.5 | | 200266209 | Ke | | Tao | taoke209@cs.uregina.ca | CS000 | 00000000000000000000000 | 0 | | 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 | | 200266211 | Chidinma | | Ukabam | ukaba211@cs.uregina.ca | CS100 | Introduction to Computers | 99 | | 200266212 | Mandeep | | Ghotra | gho212@cs.uregina.ca | CS215 | Web Oriented Programming | 98 | +------------+------------+-------------+-------------+--------------------------+-------------+----------------------------------+-------+ 31 rows in set (0.00 sec) ============================ Step 2: ============================ mysql> UPDATE students -> SET email = 'alabba200@cs.uregina.ca' -> WHERE last_Name = 'Abbad'; Query OK, 2 rows affected (0.00 sec) Rows matched: 3 Changed: 2 Warnings: 0 mysql> UPDATE students -> SET last_Name = 'Xu' -> WHERE middle_Name = 'Xu' -> ; Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0 mysql> UPDATE students -> SET middle_Name = ' ' -> WHERE last_Name = 'Xu'; Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0 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-> DELETE FROM students -> WHERE course_Name = 'CS000'; Query OK, 1 row affected (0.00 sec) mysql> select * from students; +------------+------------+-------------+-------------+--------------------------+-------------+----------------------------------+-------+ax to use near '' at line 1 | 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 | | 200266202 | Qaswar | | Rai | ali202@cs.uregina.ca | CS100 | Introduction to Computers | 72.5 | | 200266202 | Qaswar | | Rai | ali202@cs.uregina.ca | CS110 | Programming and Problem Solving | 59.2 | | 200266202 | Qaswar | | Rai | ali202@cs.uregina.ca | CS215 | Web and Database Programming | 82.6 | | 200266203 | Nicholas | | Phongsavath | phosan203@cs.uregina.ca | CS100 | Introduction to Computers | 100 | | 200266203 | Nicholas | | Phongsavath | phosan203@cs.uregina.ca | CS215 | Web and Database Programming | 100 | | 200266204 | Nikki | | Pawlowski | pawlow204@cs.uregina.ca | CS100 | Introduction to Computers | 65 | | 200266204 | Nikki | | Pawlowski | pawlow204@cs.uregina.ca | CS210 | Data Structures and Abstractions | 92.5 | | 200266205 | Nathan | | Ellis | elli205@cs.uregina.ca | CS110 | Programming and Problem Solving | 92.5 | | 200266205 | Nathan | | Ellis | elli205@cs.uregina.ca | CS210 | Data Structures and Abstractions | 70.2 | | 200266206 | John | | Erick | eric20j206@cs.uregina.ca | CS100 | Introduction to Computers | 86 | | 200266206 | John | | Erick | eric20j206@cs.uregina.ca | CS110 | Programming and Problem Solving | 87 | | 200266206 | John | | Erick | eric20j206@cs.uregina.ca | CS210 | Data Structures and Abstractions | 81 | | 200266206 | John | | Erick | eric20j206@cs.uregina.ca | CS215 | Web and Database Programming | 83 | | 200266207 | Jody-Lee | Van | DerVelden | vanervj207@cs.uregina.ca | CS100 | Introduction to Computers | 59 | | 200266207 | Jody-Lee | Van | DerVelden | vanervj207@cs.uregina.ca | CS110 | Programming and Problem Solving | 50.2 | | 200266207 | Jody-Lee | Van | DerVelden | vanervj207@cs.uregina.ca | CS210 | Data Structures and Abstractions | 67 | | 200266207 | Jody-Lee | Van | DerVelden | vanervj207@cs.uregina.ca | CS215 | Web and Database Programming | 85.6 | | 200266208 | Sabrina | | Tram | tra200s208@cs.uregina.ca | CS100 | Introduction to Computers | 70 | | 200266208 | Sabrina | | Tram | tra200s208@cs.uregina.ca | CS110 | Programming and Problem Solving | 80 | | 200266208 | Sabrina | | Tram | tra200s208@cs.uregina.ca | CS210 | Data Structures and Abstractions | 88.5 | | 200266209 | Ke | | Tao | taoke209@cs.uregina.ca | CS110 | Programming and Problem Solving | 66.5 | | 200266209 | Ke | | Tao | taoke209@cs.uregina.ca | CS210 | Data Structures and Abstractions | 77.5 | | 200266209 | Ke | | Tao | taoke209@cs.uregina.ca | CS215 | Web and Database Programming | 86.5 | | 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 | | 200266211 | Chidinma | | Ukabam | ukaba211@cs.uregina.ca | CS100 | Introduction to Computers | 99 | | 200266212 | Mandeep | | Ghotra | gho212@cs.uregina.ca | CS215 | Web and Database Programming | 98 | +------------+------------+-------------+-------------+--------------------------+-------------+----------------------------------+-------+ 30 rows in set (0.00 sec) ============================================================== Step 3 ============================================================== mysql> DESC 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 | | +--------------------+--------------+------+-----+---------+-------+ 8 rows in set (0.00 sec) 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) -> ); Query OK, 0 rows affected (0.01 sec) mysql> DESC 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 | | +-------------+--------------+------+-----+---------+-------+ 5 rows in set (0.00 sec) 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.01 sec) mysql> DESC Courses -> ; +--------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------+--------------+------+-----+---------+-------+ | course_Name | varchar(30) | NO | PRI | NULL | | | course_Description | varchar(255) | NO | | NULL | | +--------------------+--------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> CREATE TABLE Marks ( student_ID varchar(30) NOT NULL, course_Name varchar(30) NOT NULL, marks double NOT NULL, -> FOREIGN KEY (student_ID) REFERENCES Student_Info (student_ID), -> FOREIGN KEY (course_Name) REFERENCES Courses (course_Name) -> ); Query OK, 0 rows affected (0.02 sec) mysql> DESC Marks -> ; +-------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+-------+ | student_ID | varchar(30) | NO | MUL | NULL | | | course_Name | varchar(30) | NO | MUL | NULL | | | marks | double | NO | | NULL | | +-------------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) ====================================================================================== Step 4 ====================================================================================== 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) Records: 12 Duplicates: 0 Warnings: 0 mysql> INSERT INTO Courses (course_Name, course_Description) -> SELECT DISTINCT course_Name, course_Description from students; Query OK, 4 rows affected (0.01 sec) Records: 4 Duplicates: 0 Warnings: 0 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) Records: 30 Duplicates: 0 Warnings: 0 mysql> DESC 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 | | +-------------+--------------+------+-----+---------+-------+ 5 rows in set (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 | +------------+------------+-------------+-------------+--------------------------+ 12 rows in set (0.00 sec) mysql> DESC Courses; +--------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------+--------------+------+-----+---------+-------+ | course_Name | varchar(30) | NO | PRI | NULL | | | course_Description | varchar(255) | NO | | NULL | | +--------------------+--------------+------+-----+---------+-------+ 2 rows in set (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 | +-------------+----------------------------------+ 4 rows in set (0.00 sec) mysql> DESC Marks; +-------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+-------+ | student_ID | varchar(30) | NO | MUL | NULL | | | course_Name | varchar(30) | NO | MUL | NULL | | | marks | double | NO | | NULL | | +-------------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 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 | +------------+-------------+-------+ 30 rows in set (0.00 sec)