--Name: Wyatt Drew --Student Number: 200225999 --================================================= --Part 1 --================================================= --Creating the tables --1 mysql> CREATE TABLE Users ( -> user_id INT NOT NULL AUTO_INCREMENT, -> email varchar(50) NOT NULL UNIQUE, -> username varchar(50) NOT NULL UNIQUE, -> avatar varchar(200) NOT NULL, -> password1 varchar(50) NOT NULL, -> PRIMARY KEY (user_id) -> ); Query OK, 0 rows affected (0.02 sec) -- 2 mysql> CREATE TABLE Polls ( -> poll_id INT NOT NULL AUTO_INCREMENT, -> user_id INT NOT NULL, -> creation_date DATETIME NOT NULL DEFAULT NOW(), -> start_date DATETIME NOT NULL, -> end_date DATETIME NOT NULL, -> last_updated DATETIME ON UPDATE NOW(), -> question varchar(100) NOT NULL, -> answer1 varchar(50) NOT NULL, -> answer2 varchar(50) NOT NULL, -> answer3 varchar(50) DEFAULT "", -> answer4 varchar(50) DEFAULT "", -> answer5 varchar(50) DEFAULT "", -> tally1 INT DEFAULT 0, -> tally2 INT DEFAULT 0, -> tally3 INT DEFAULT 0, -> tally4 INT DEFAULT 0, -> tally5 INT DEFAULT 0, -> PRIMARY KEY (poll_id), -> FOREIGN KEY (user_id) REFERENCES Users (user_id) -> ); Query OK, 0 rows affected (0.02 sec) --Quick display to verify success --3 mysql> DESCRIBE Users; +-----------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+--------------+------+-----+---------+----------------+ | user_id | int | NO | PRI | NULL | auto_increment | | email | varchar(50) | NO | UNI | NULL | | | username | varchar(50) | NO | UNI | NULL | | | avatar | varchar(200) | NO | | NULL | | | password1 | varchar(50) | NO | | NULL | | +-----------+--------------+------+-----+---------+----------------+ 5 rows in set (0.00 sec) --4 mysql> DESCRIBE Polls; +---------------+--------------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +---------------+--------------+------+-----+-------------------+-----------------------------+ | poll_id | int | NO | PRI | NULL | auto_increment | | user_id | int | NO | MUL | NULL | | | creation_date | datetime | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED | | start_date | datetime | NO | | NULL | | | end_date | datetime | NO | | NULL | | | last_updated | datetime | YES | | NULL | on update CURRENT_TIMESTAMP | | question | varchar(100) | NO | | NULL | | | answer1 | varchar(50) | NO | | NULL | | | answer2 | varchar(50) | NO | | NULL | | | answer3 | varchar(50) | YES | | | | | answer4 | varchar(50) | YES | | | | | answer5 | varchar(50) | YES | | | | | tally1 | int | YES | | 0 | | | tally2 | int | YES | | 0 | | | tally3 | int | YES | | 0 | | | tally4 | int | YES | | 0 | | | tally5 | int | YES | | 0 | | +---------------+--------------+------+-----+-------------------+-----------------------------+ 17 rows in set (0.01 sec) ================================================== Part 2 ================================================== --=============================================== --part A) signup form --=============================================== --Populating tables with data --Trying out variables because I hypothesize it may help with PHP later. Plus its good practice. --5 mysql> SET @t1 = "myEmail@hotmail.com", @t2 = "theRock", @t3 = "https://static.vecteezy.com/system/resources/thumbnails/004/511/281/small/default-avatar-photo-placeholder-profile-picture-vector.jpg", @t4 = "quaj1829"; Query OK, 0 rows affected (0.00 sec) --6 mysql> INSERT INTO Users (email, username, avatar, password1) values (@t1, @t2, @t3, @t4); Query OK, 1 row affected (0.00 sec) --7 mysql> SET @t1 = "myFakeEmail@hotmail.com", @t2 = "Ted", @t3 = "https://ichef.bbci.co.uk/news/976/cpsprodpb/D2D2/production/_118707935_untitled-1.jpg", @t4 = "12345678"; Query OK, 0 rows affected (0.00 sec) --8 mysql> INSERT INTO Users (email, username, avatar, password1) values (@t1, @t2, @t3, @t4); Query OK, 1 row affected (0.00 sec) --9 mysql> SET @t1 = "abc@hotmail.ca", @t2 = "Generic", @t3 = "https://cdn.pixabay.com/photo/2015/10/05/22/37/blank-profile-picture-973460_1280.png", @t4 = "qwer1234"; Query OK, 0 rows affected (0.00 sec) --10 mysql> INSERT INTO Users (email, username, avatar, password1) values (@t1, @t2, @t3, @t4); Query OK, 1 row affected (0.00 sec) --11 mysql> SET @t1 = "myName@gmail.com", @t2 = "MyUsername", @t3 = "https://www.cdbradshaw.com/wp-content/uploads/2021/07/generic-avatar-240x300.jpg", @t4 = "abcd1234"; Query OK, 0 rows affected (0.00 sec) --12 mysql> INSERT INTO Users (email, username, avatar, password1) values (@t1, @t2, @t3, @t4); Query OK, 1 row affected (0.00 sec) --Verifying success --13 mysql> select * from Users; +---------+-------------------------+------------+---------------------------------------------------------------------------------------------------------------------------------------+-----------+ | user_id | email | username | avatar | password1 | +---------+-------------------------+------------+---------------------------------------------------------------------------------------------------------------------------------------+-----------+ | 1 | myEmail@hotmail.com | theRock | https://static.vecteezy.com/system/resources/thumbnails/004/511/281/small/default-avatar-photo-placeholder-profile-picture-vector.jpg | quaj1829 | | 2 | myFakeEmail@hotmail.com | Ted | https://ichef.bbci.co.uk/news/976/cpsprodpb/D2D2/production/_118707935_untitled-1.jpg | 12345678 | | 3 | abc@hotmail.ca | Generic | https://cdn.pixabay.com/photo/2015/10/05/22/37/blank-profile-picture-973460_1280.png | qwer1234 | | 4 | myName@gmail.com | MyUsername | https://www.cdbradshaw.com/wp-content/uploads/2021/07/generic-avatar-240x300.jpg | abcd1234 | +---------+-------------------------+------------+---------------------------------------------------------------------------------------------------------------------------------------+-----------+ 4 rows in set (0.01 sec) --=================================== -- part B) Poll Creation Form --=================================== -- creating polls --14 mysql> SET @t1 = "2", @t2 = "2022-10-24 11:10:10", @t3 = "2022-11-24 12:12:12", @t4 = "Do you believe in Santa?", @t5 = "Yes", @t6 = "No", @t7 = "", @t8 = "", @t9 = ""; Query OK, 0 rows affected (0.00 sec) --15 mysql> INSERT INTO Polls (user_id, start_date, end_date, question, answer1, answer2, answer3, answer4, answer5) -> values (@t1, @t2, @t3, @t4, @t5, @t6, @t7, @t8, @t9); Query OK, 1 row affected (0.00 sec) --16 mysql> SET @t1 = "2", @t2 = "2022-06-01 11:10:10", @t3 = "2022-11-24 12:12:12", @t4 = "What is your favorite color?", @t5 = "Purple", @t6 = "Violet", @t7 = "Lavender", @t8 = "Lilac", @t9 = "Plum"; Query OK, 0 rows affected (0.01 sec) --17 mysql> INSERT INTO Polls (user_id, start_date, end_date, question, answer1, answer2, answer3, answer4, answer5) -> values (@t1, @t2, @t3, @t4, @t5, @t6, @t7, @t8, @t9); Query OK, 1 row affected (0.00 sec) --18 mysql> SET @t1 = "3", @t2 = "2022-07-01 11:10:10", @t3 = "2022-11-24 12:12:12", @t4 = "What is your favorite pet?", @t5 = "Dog", @t6 = "Cat", @t7 = "Turtle", @t8 = "Rabbit", @t9 = "Fish"; Query OK, 0 rows affected (0.00 sec) --19 mysql> INSERT INTO Polls (user_id, start_date, end_date, question, answer1, answer2, answer3, answer4, answer5) -> values (@t1, @t2, @t3, @t4, @t5, @t6, @t7, @t8, @t9); Query OK, 1 row affected (0.00 sec) --20 mysql> SET @t1 = "3", @t2 = "2022-08-01 11:10:10", @t3 = "2022-11-24 12:12:12", @t4 = "What is your favorite icecream flavor?", @t5 = "Mint", @t6 = "Rocky Road", @t7 = "Chocolate", @t8 = "Vanilla", @t9 = "Strawberry"; Query OK, 0 rows affected (0.00 sec) --21 mysql> INSERT INTO Polls (user_id, start_date, end_date, question, answer1, answer2, answer3, answer4, answer5) -> values (@t1, @t2, @t3, @t4, @t5, @t6, @t7, @t8, @t9); Query OK, 1 row affected (0.01 sec) --22 mysql> SET @t1 = "4", @t2 = "2022-08-02 11:10:10", @t3 = "2022-11-24 12:12:12", @t4 = "What is the airspeed velocity of an unlaiden swallow?", @t5 = "African or European?", @t6 = "I don't know.", @t7 = "", @t8 = "", @t9 = ""; Query OK, 0 rows affected (0.00 sec) -23 mysql> INSERT INTO Polls (user_id, start_date, end_date, question, answer1, answer2, answer3, answer4, answer5) -> values (@t1, @t2, @t3, @t4, @t5, @t6, @t7, @t8, @t9); Query OK, 1 row affected (0.01 sec) -24 mysql> SET @t1 = "4", @t2 = "2022-08-02 12:10:10", @t3 = "2022-11-24 12:12:12", @t4 = "Do you like cheese?", @t5 = "Yes", @t6 = "No", @t7 = "", @t8 = "", @t9 = ""; Query OK, 0 rows affected (0.00 sec) --25 mysql> INSERT INTO Polls (user_id, start_date, end_date, question, answer1, answer2, answer3, answer4, answer5) -> values (@t1, @t2, @t3, @t4, @t5, @t6, @t7, @t8, @t9); Query OK, 1 row affected (0.00 sec) --Verifying success --26 mysql> select * from Polls; +---------+---------+---------------------+---------------------+---------------------+--------------+-------------------------------------------------------+----------------------+---------------+-----------+---------+------------+--------+--------+--------+--------+--------+ | poll_id | user_id | creation_date | start_date | end_date | last_updated | question | answer1 | answer2 | answer3 | answer4 | answer5 | tally1 | tally2 | tally3 | tally4 | tally5 | +---------+---------+---------------------+---------------------+---------------------+--------------+-------------------------------------------------------+----------------------+---------------+-----------+---------+------------+--------+--------+--------+--------+--------+ | 1 | 2 | 2022-08-02 20:04:30 | 2022-10-24 11:10:10 | 2022-11-24 12:12:12 | NULL | Do you believe in Santa? | Yes | No | | | | 0 | 0 | 0 | 0 | 0 | | 2 | 2 | 2022-08-02 20:10:51 | 2022-06-01 11:10:10 | 2022-11-24 12:12:12 | NULL | What is your favorite color? | Purple | Violet | Lavender | Lilac | Plum | 0 | 0 | 0 | 0 | 0 | | 3 | 3 | 2022-08-02 20:13:05 | 2022-07-01 11:10:10 | 2022-11-24 12:12:12 | NULL | What is your favorite pet? | Dog | Cat | Turtle | Rabbit | Fish | 0 | 0 | 0 | 0 | 0 | | 4 | 3 | 2022-08-02 20:15:06 | 2022-08-01 11:10:10 | 2022-11-24 12:12:12 | NULL | What is your favorite icecream flavor? | Mint | Rocky Road | Chocolate | Vanilla | Strawberry | 0 | 0 | 0 | 0 | 0 | | 5 | 4 | 2022-08-02 20:16:41 | 2022-08-02 11:10:10 | 2022-11-24 12:12:12 | NULL | What is the airspeed velocity of an unlaiden swallow? | African or European? | I don't know. | | | | 0 | 0 | 0 | 0 | 0 | | 6 | 4 | 2022-08-02 20:18:45 | 2022-08-02 12:10:10 | 2022-11-24 12:12:12 | NULL | Do you like cheese? | Yes | No | | | | 0 | 0 | 0 | 0 | 0 | +---------+---------+---------------------+---------------------+---------------------+--------------+-------------------------------------------------------+----------------------+---------------+-----------+---------+------------+--------+--------+--------+--------+--------+ 6 rows in set (0.00 sec) --================================== -- Part C) Poll Vote Page --================================== --27 mysql> UPDATE Polls -> SET tally1 = tally1 + @t1, tally2 = tally2 + @t2, tally3 = tally3 + @t3, tally4 = tally4 + @t4, tally5 = tally5 + @t5 -> WHERE poll_id = 2; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 --28 mysql> Select * from Polls; +---------+---------+---------------------+---------------------+---------------------+---------------------+-------------------------------------------------------+----------------------+---------------+-----------+---------+------------+--------+--------+--------+--------+--------+ | poll_id | user_id | creation_date | start_date | end_date | last_updated | question | answer1 | answer2 | answer3 | answer4 | answer5 | tally1 | tally2 | tally3 | tally4 | tally5 | +---------+---------+---------------------+---------------------+---------------------+---------------------+-------------------------------------------------------+----------------------+---------------+-----------+---------+------------+--------+--------+--------+--------+--------+ | 1 | 2 | 2022-08-02 20:04:30 | 2022-10-24 11:10:10 | 2022-11-24 12:12:12 | NULL | Do you believe in Santa? | Yes | No | | | | 0 | 0 | 0 | 0 | 0 | | 2 | 2 | 2022-08-02 20:10:51 | 2022-06-01 11:10:10 | 2022-11-24 12:12:12 | 2022-08-02 20:22:48 | What is your favorite color? | Purple | Violet | Lavender | Lilac | Plum | 1 | 0 | 0 | 0 | 0 | | 3 | 3 | 2022-08-02 20:13:05 | 2022-07-01 11:10:10 | 2022-11-24 12:12:12 | NULL | What is your favorite pet? | Dog | Cat | Turtle | Rabbit | Fish | 0 | 0 | 0 | 0 | 0 | | 4 | 3 | 2022-08-02 20:15:06 | 2022-08-01 11:10:10 | 2022-11-24 12:12:12 | NULL | What is your favorite icecream flavor? | Mint | Rocky Road | Chocolate | Vanilla | Strawberry | 0 | 0 | 0 | 0 | 0 | | 5 | 4 | 2022-08-02 20:16:41 | 2022-08-02 11:10:10 | 2022-11-24 12:12:12 | NULL | What is the airspeed velocity of an unlaiden swallow? | African or European? | I don't know. | | | | 0 | 0 | 0 | 0 | 0 | | 6 | 4 | 2022-08-02 20:18:45 | 2022-08-02 12:10:10 | 2022-11-24 12:12:12 | NULL | Do you like cheese? | Yes | No | | | | 0 | 0 | 0 | 0 | 0 | +---------+---------+---------------------+---------------------+---------------------+---------------------+-------------------------------------------------------+----------------------+---------------+-----------+---------+------------+--------+--------+--------+--------+--------+ 6 rows in set (0.00 sec) --======= --29 Testing repeated voting and voting for option 5 --======= mysql> SET @t1 = 0, @t2 = 0, @t3 = 0, @t4 = 0, @t5 = 1; Query OK, 0 rows affected (0.00 sec) --30 mysql> UPDATE Polls -> SET tally1 = tally1 + @t1, tally2 = tally2 + @t2, tally3 = tally3 + @t3, tally4 = tally4 + @t4, tally5 = tally5 + @t5 -> WHERE poll_id = 2; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 --31 mysql> Select * from Polls; +---------+---------+---------------------+---------------------+---------------------+---------------------+-------------------------------------------------------+----------------------+---------------+-----------+---------+------------+--------+--------+--------+--------+--------+ | poll_id | user_id | creation_date | start_date | end_date | last_updated | question | answer1 | answer2 | answer3 | answer4 | answer5 | tally1 | tally2 | tally3 | tally4 | tally5 | +---------+---------+---------------------+---------------------+---------------------+---------------------+-------------------------------------------------------+----------------------+---------------+-----------+---------+------------+--------+--------+--------+--------+--------+ | 1 | 2 | 2022-08-02 20:04:30 | 2022-10-24 11:10:10 | 2022-11-24 12:12:12 | NULL | Do you believe in Santa? | Yes | No | | | | 0 | 0 | 0 | 0 | 0 | | 2 | 2 | 2022-08-02 20:10:51 | 2022-06-01 11:10:10 | 2022-11-24 12:12:12 | 2022-08-02 20:24:41 | What is your favorite color? | Purple | Violet | Lavender | Lilac | Plum | 1 | 0 | 0 | 0 | 1 | | 3 | 3 | 2022-08-02 20:13:05 | 2022-07-01 11:10:10 | 2022-11-24 12:12:12 | NULL | What is your favorite pet? | Dog | Cat | Turtle | Rabbit | Fish | 0 | 0 | 0 | 0 | 0 | | 4 | 3 | 2022-08-02 20:15:06 | 2022-08-01 11:10:10 | 2022-11-24 12:12:12 | NULL | What is your favorite icecream flavor? | Mint | Rocky Road | Chocolate | Vanilla | Strawberry | 0 | 0 | 0 | 0 | 0 | | 5 | 4 | 2022-08-02 20:16:41 | 2022-08-02 11:10:10 | 2022-11-24 12:12:12 | NULL | What is the airspeed velocity of an unlaiden swallow? | African or European? | I don't know. | | | | 0 | 0 | 0 | 0 | 0 | | 6 | 4 | 2022-08-02 20:18:45 | 2022-08-02 12:10:10 | 2022-11-24 12:12:12 | NULL | Do you like cheese? | Yes | No | | | | 0 | 0 | 0 | 0 | 0 | +---------+---------+---------------------+---------------------+---------------------+---------------------+-------------------------------------------------------+----------------------+---------------+-----------+---------+------------+--------+--------+--------+--------+--------+ 6 rows in set (0.00 sec) --====== --32 Testing voting for a variety of polls --====== mysql> SET @t1 = 0, @t2 = 0, @t3 = 1, @t4 = 0, @t5 = 0; Query OK, 0 rows affected (0.00 sec) --33 mysql> UPDATE Polls -> SET tally1 = tally1 + @t1, tally2 = tally2 + @t2, tally3 = tally3 + @t3, tally4 = tally4 + @t4, tally5 = tally5 + @t5 -> WHERE poll_id = 2; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 --34 mysql> SET @t1 = 0, @t2 = 0, @t3 = 0, @t4 = 1, @t5 = 0; Query OK, 0 rows affected (0.00 sec) --35 mysql> UPDATE Polls -> SET tally1 = tally1 + @t1, tally2 = tally2 + @t2, tally3 = tally3 + @t3, tally4 = tally4 + @t4, tally5 = tally5 + @t5 -> WHERE poll_id = 3; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 --36 mysql> SET @t1 = 1, @t2 = 0, @t3 = 0, @t4 = 0, @t5 = 0; Query OK, 0 rows affected (0.00 sec) --37 mysql> UPDATE Polls -> SET tally1 = tally1 + @t1, tally2 = tally2 + @t2, tally3 = tally3 + @t3, tally4 = tally4 + @t4, tally5 = tally5 + @t5 -> WHERE poll_id = 4; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 --38 mysql> SET @t1 = 0, @t2 = 1, @t3 = 0, @t4 = 0, @t5 = 0; Query OK, 0 rows affected (0.00 sec) --39 mysql> UPDATE Polls -> SET tally1 = tally1 + @t1, tally2 = tally2 + @t2, tally3 = tally3 + @t3, tally4 = tally4 + @t4, tally5 = tally5 + @t5 -> WHERE poll_id = 5; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 --40 mysql> SET @t1 = 0, @t2 = 1, @t3 = 0, @t4 = 0, @t5 = 0; Query OK, 0 rows affected (0.00 sec) --41 mysql> UPDATE Polls -> SET tally1 = tally1 + @t1, tally2 = tally2 + @t2, tally3 = tally3 + @t3, tally4 = tally4 + @t4, tally5 = tally5 + @t5 -> WHERE poll_id = 6; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 --======= --Verifying success --======= --42 mysql> SELECT * from Polls; +---------+---------+---------------------+---------------------+---------------------+---------------------+-------------------------------------------------------+----------------------+---------------+-----------+---------+------------+--------+--------+--------+--------+--------+ | poll_id | user_id | creation_date | start_date | end_date | last_updated | question | answer1 | answer2 | answer3 | answer4 | answer5 | tally1 | tally2 | tally3 | tally4 | tally5 | +---------+---------+---------------------+---------------------+---------------------+---------------------+-------------------------------------------------------+----------------------+---------------+-----------+---------+------------+--------+--------+--------+--------+--------+ | 1 | 2 | 2022-08-02 20:04:30 | 2022-10-24 11:10:10 | 2022-11-24 12:12:12 | NULL | Do you believe in Santa? | Yes | No | | | | 0 | 0 | 0 | 0 | 0 | | 2 | 2 | 2022-08-02 20:10:51 | 2022-06-01 11:10:10 | 2022-11-24 12:12:12 | 2022-08-02 20:36:28 | What is your favorite color? | Purple | Violet | Lavender | Lilac | Plum | 1 | 0 | 1 | 0 | 1 | | 3 | 3 | 2022-08-02 20:13:05 | 2022-07-01 11:10:10 | 2022-11-24 12:12:12 | 2022-08-02 20:37:05 | What is your favorite pet? | Dog | Cat | Turtle | Rabbit | Fish | 0 | 0 | 0 | 1 | 0 | | 4 | 3 | 2022-08-02 20:15:06 | 2022-08-01 11:10:10 | 2022-11-24 12:12:12 | 2022-08-02 20:37:47 | What is your favorite icecream flavor? | Mint | Rocky Road | Chocolate | Vanilla | Strawberry | 1 | 0 | 0 | 0 | 0 | | 5 | 4 | 2022-08-02 20:16:41 | 2022-08-02 11:10:10 | 2022-11-24 12:12:12 | 2022-08-02 20:38:22 | What is the airspeed velocity of an unlaiden swallow? | African or European? | I don't know. | | | | 0 | 1 | 0 | 0 | 0 | | 6 | 4 | 2022-08-02 20:18:45 | 2022-08-02 12:10:10 | 2022-11-24 12:12:12 | 2022-08-02 20:38:48 | Do you like cheese? | Yes | No | | | | 0 | 1 | 0 | 0 | 0 | +---------+---------+---------------------+---------------------+---------------------+---------------------+-------------------------------------------------------+----------------------+---------------+-----------+---------+------------+--------+--------+--------+--------+--------+ 6 rows in set (0.00 sec) ================================================================ Part 3 ================================================================ -- Part A) Main Page --43 mysql> SELECT question, answer1, answer2, answer3, answer4, answer5 FROM Polls -> ORDER BY last_updated DESC -> LIMIT 5; +-------------------------------------------------------+----------------------+---------------+-----------+---------+------------+ | question | answer1 | answer2 | answer3 | answer4 | answer5 | +-------------------------------------------------------+----------------------+---------------+-----------+---------+------------+ | Do you like cheese? | Yes | No | | | | | What is the airspeed velocity of an unlaiden swallow? | African or European? | I don't know. | | | | | What is your favorite icecream flavor? | Mint | Rocky Road | Chocolate | Vanilla | Strawberry | | What is your favorite pet? | Dog | Cat | Turtle | Rabbit | Fish | | What is your favorite color? | Purple | Violet | Lavender | Lilac | Plum | +-------------------------------------------------------+----------------------+---------------+-----------+---------+------------+ 5 rows in set (0.00 sec) --====================== --Part B) Login Form --====================== --**Testing valid email password --44 mysql> SET @supplied_email = 'myFakeEmail@hotmail.com', @supplied_password='12345678'; Query OK, 0 rows affected (0.00 sec) --45 mysql> SELECT user_id, username, avatar -> FROM Users -> WHERE (@supplied_email = email) AND (@supplied_password = BINARY password1); +---------+----------+---------------------------------------------------------------------------------------+ | user_id | username | avatar | +---------+----------+---------------------------------------------------------------------------------------+ | 2 | Ted | https://ichef.bbci.co.uk/news/976/cpsprodpb/D2D2/production/_118707935_untitled-1.jpg | +---------+----------+---------------------------------------------------------------------------------------+ 1 row in set, 1 warning (0.00 sec) -- ** Testing invalid email, valid password --46 mysql> SET @supplied_email = 'myFakeEmail2@hotmail.com', @supplied_password='12345678'; Query OK, 0 rows affected (0.00 sec) --47 mysql> SELECT user_id, username, avatar -> FROM Users -> WHERE (@supplied_email = email) AND (@supplied_password = BINARY password1); Empty set, 1 warning (0.00 sec) --** Testing valid email, invalid password --48 mysql> SET @supplied_email = 'myFakeEmail@hotmail.com', @supplied_password='123456789'; Query OK, 0 rows affected (0.00 sec) --49 mysql> SELECT user_id, username, avatar -> FROM Users -> WHERE (@supplied_email = email) AND (@supplied_password = BINARY password1); Empty set, 1 warning (0.00 sec) --**Testing case sensitivity for passwords (strings are by default not case sensitive so BINARY should fix that) --50 mysql> SET @supplied_email = 'myEmail@hotmail.com', @supplied_password='QUAj1829'; Query OK, 0 rows affected (0.00 sec) --51 mysql> SELECT user_id, username, avatar -> FROM Users -> WHERE (@supplied_email = email) AND (@supplied_password = BINARY password1); Empty set, 1 warning (0.00 sec) --========================================== --Part C) Poll Management Page --========================================== --**Testing out a few --52 mysql> SET @u_id = "4"; Query OK, 0 rows affected (0.00 sec) --52 mysql> SELECT question, answer1, answer2, answer3, answer4, answer5, tally1, tally2, tally3, tally4, tally5, last_updated FROM Polls -> WHERE user_id = @u_id -> ORDER BY creation_date; +-------------------------------------------------------+----------------------+---------------+---------+---------+---------+--------+--------+--------+--------+--------+---------------------+ | question | answer1 | answer2 | answer3 | answer4 | answer5 | tally1 | tally2 | tally3 | tally4 | tally5 | last_updated | +-------------------------------------------------------+----------------------+---------------+---------+---------+---------+--------+--------+--------+--------+--------+---------------------+ | What is the airspeed velocity of an unlaiden swallow? | African or European? | I don't know. | | | | 0 | 1 | 0 | 0 | 0 | 2022-08-02 20:38:22 | | Do you like cheese? | Yes | No | | | | 0 | 1 | 0 | 0 | 0 | 2022-08-02 20:38:48 | +-------------------------------------------------------+----------------------+---------------+---------+---------+---------+--------+--------+--------+--------+--------+---------------------+ 2 rows in set (0.00 sec) --53 mysql> SET @u_id = "3"; Query OK, 0 rows affected (0.00 sec) --54 mysql> SELECT question, answer1, answer2, answer3, answer4, answer5, tally1, tally2, tally3, tally4, tally5, last_updated FROM Polls -> WHERE user_id = @u_id -> ORDER BY creation_date; +----------------------------------------+---------+------------+-----------+---------+------------+--------+--------+--------+--------+--------+---------------------+ | question | answer1 | answer2 | answer3 | answer4 | answer5 | tally1 | tally2 | tally3 | tally4 | tally5 | last_updated | +----------------------------------------+---------+------------+-----------+---------+------------+--------+--------+--------+--------+--------+---------------------+ | What is your favorite pet? | Dog | Cat | Turtle | Rabbit | Fish | 0 | 0 | 0 | 1 | 0 | 2022-08-02 20:37:05 | | What is your favorite icecream flavor? | Mint | Rocky Road | Chocolate | Vanilla | Strawberry | 1 | 0 | 0 | 0 | 0 | 2022-08-02 20:37:47 | +----------------------------------------+---------+------------+-----------+---------+------------+--------+--------+--------+--------+--------+---------------------+ 2 rows in set (0.00 sec) --==================================== --Part D) Poll Vote Page --==================================== --55 mysql> SET @p_id = "1"; Query OK, 0 rows affected (0.00 sec) --56 mysql> SELECT question, answer1, answer2, answer3, answer4, answer5 -> FROM Polls -> WHERE poll_id = @p_id; +--------------------------+---------+---------+---------+---------+---------+ | question | answer1 | answer2 | answer3 | answer4 | answer5 | +--------------------------+---------+---------+---------+---------+---------+ | Do you believe in Santa? | Yes | No | | | | +--------------------------+---------+---------+---------+---------+---------+ 1 row in set (0.00 sec) --57 mysql> SET @p_id = "2"; Query OK, 0 rows affected (0.00 sec) --58 mysql> SELECT question, answer1, answer2, answer3, answer4, answer5 -> FROM Polls -> WHERE poll_id = @p_id; +------------------------------+---------+---------+----------+---------+---------+ | question | answer1 | answer2 | answer3 | answer4 | answer5 | +------------------------------+---------+---------+----------+---------+---------+ | What is your favorite color? | Purple | Violet | Lavender | Lilac | Plum | +------------------------------+---------+---------+----------+---------+---------+ 1 row in set (0.00 sec) --========================== --Part E) Poll Results Page --========================== --59 mysql> SET @p_id = "4"; Query OK, 0 rows affected (0.00 sec) --60 mysql> SELECT question, answer1, answer2, answer3, answer4, answer5, tally1, tally2, tally3, tally4, tally5, last_updated -> FROM Polls -> WHERE poll_id = @p_id; +----------------------------------------+---------+------------+-----------+---------+------------+--------+--------+--------+--------+--------+---------------------+ | question | answer1 | answer2 | answer3 | answer4 | answer5 | tally1 | tally2 | tally3 | tally4 | tally5 | last_updated | +----------------------------------------+---------+------------+-----------+---------+------------+--------+--------+--------+--------+--------+---------------------+ | What is your favorite icecream flavor? | Mint | Rocky Road | Chocolate | Vanilla | Strawberry | 1 | 0 | 0 | 0 | 0 | 2022-08-02 20:37:47 | +----------------------------------------+---------+------------+-----------+---------+------------+--------+--------+--------+--------+--------+---------------------+ 1 row in set (0.00 sec) --61 mysql> SET @p_id = "5"; Query OK, 0 rows affected (0.00 sec) --62 mysql> SELECT question, answer1, answer2, answer3, answer4, answer5, tally1, tally2, tally3, tally4, tally5, last_updated -> FROM Polls -> WHERE poll_id = @p_id; +-------------------------------------------------------+----------------------+---------------+---------+---------+---------+--------+--------+--------+--------+--------+---------------------+ | question | answer1 | answer2 | answer3 | answer4 | answer5 | tally1 | tally2 | tally3 | tally4 | tally5 | last_updated | +-------------------------------------------------------+----------------------+---------------+---------+---------+---------+--------+--------+--------+--------+--------+---------------------+ | What is the airspeed velocity of an unlaiden swallow? | African or European? | I don't know. | | | | 0 | 1 | 0 | 0 | 0 | 2022-08-02 20:38:22 | +-------------------------------------------------------+----------------------+---------------+---------+---------+---------+--------+--------+--------+--------+--------+---------------------+ 1 row in set (0.00 sec) --================================= --Extra: Final state of the data --================================= --63 mysql> SELECT * from Users; +---------+-------------------------+------------+---------------------------------------------------------------------------------------------------------------------------------------+-----------+ | user_id | email | username | avatar | password1 | +---------+-------------------------+------------+---------------------------------------------------------------------------------------------------------------------------------------+-----------+ | 1 | myEmail@hotmail.com | theRock | https://static.vecteezy.com/system/resources/thumbnails/004/511/281/small/default-avatar-photo-placeholder-profile-picture-vector.jpg | quaj1829 | | 2 | myFakeEmail@hotmail.com | Ted | https://ichef.bbci.co.uk/news/976/cpsprodpb/D2D2/production/_118707935_untitled-1.jpg | 12345678 | | 3 | abc@hotmail.ca | Generic | https://cdn.pixabay.com/photo/2015/10/05/22/37/blank-profile-picture-973460_1280.png | qwer1234 | | 4 | myName@gmail.com | MyUsername | https://www.cdbradshaw.com/wp-content/uploads/2021/07/generic-avatar-240x300.jpg | abcd1234 | +---------+-------------------------+------------+---------------------------------------------------------------------------------------------------------------------------------------+-----------+ 4 rows in set (0.00 sec) --64 mysql> SELECT * from Polls; +---------+---------+---------------------+---------------------+---------------------+---------------------+-------------------------------------------------------+----------------------+---------------+-----------+---------+------------+--------+--------+--------+--------+--------+ | poll_id | user_id | creation_date | start_date | end_date | last_updated | question | answer1 | answer2 | answer3 | answer4 | answer5 | tally1 | tally2 | tally3 | tally4 | tally5 | +---------+---------+---------------------+---------------------+---------------------+---------------------+-------------------------------------------------------+----------------------+---------------+-----------+---------+------------+--------+--------+--------+--------+--------+ | 1 | 2 | 2022-08-02 20:04:30 | 2022-10-24 11:10:10 | 2022-11-24 12:12:12 | NULL | Do you believe in Santa? | Yes | No | | | | 0 | 0 | 0 | 0 | 0 | | 2 | 2 | 2022-08-02 20:10:51 | 2022-06-01 11:10:10 | 2022-11-24 12:12:12 | 2022-08-02 20:36:28 | What is your favorite color? | Purple | Violet | Lavender | Lilac | Plum | 1 | 0 | 1 | 0 | 1 | | 3 | 3 | 2022-08-02 20:13:05 | 2022-07-01 11:10:10 | 2022-11-24 12:12:12 | 2022-08-02 20:37:05 | What is your favorite pet? | Dog | Cat | Turtle | Rabbit | Fish | 0 | 0 | 0 | 1 | 0 | | 4 | 3 | 2022-08-02 20:15:06 | 2022-08-01 11:10:10 | 2022-11-24 12:12:12 | 2022-08-02 20:37:47 | What is your favorite icecream flavor? | Mint | Rocky Road | Chocolate | Vanilla | Strawberry | 1 | 0 | 0 | 0 | 0 | | 5 | 4 | 2022-08-02 20:16:41 | 2022-08-02 11:10:10 | 2022-11-24 12:12:12 | 2022-08-02 20:38:22 | What is the airspeed velocity of an unlaiden swallow? | African or European? | I don't know. | | | | 0 | 1 | 0 | 0 | 0 | | 6 | 4 | 2022-08-02 20:18:45 | 2022-08-02 12:10:10 | 2022-11-24 12:12:12 | 2022-08-02 20:38:48 | Do you like cheese? | Yes | No | | | | 0 | 1 | 0 | 0 | 0 | +---------+---------+---------------------+---------------------+---------------------+---------------------+-------------------------------------------------------+----------------------+---------------+-----------+---------+------------+--------+--------+--------+--------+--------+ 6 rows in set (0.00 sec)