Personal Playground Database System: Codio Platform

Running head: PERSONAL PLAYGROUND DATABASE SYSTEM
Personal Playground Database System: Codio Platform
Name of the Student
Name of the University

1PERSONAL PLAYGROUND DATABASE SYSTEM
Table of Contents
Task 1: Insert Record to the Person Table.......................................................................................2
Task 2: Alter the Person Table........................................................................................................2
Task 3: Update Records in the Person Table...................................................................................3
Task 4: Delete Records from Person Table.....................................................................................3
Task 5: Alter the Contact List Table................................................................................................3
Task 6: Update Records in the Contact List Table..........................................................................4
Task 7: Update Records in the Contact List Table..........................................................................4
Task 8: Insert Records to Contact List Table..................................................................................5
Task 9: Create the Image Table.......................................................................................................6
Task 10: Create the Message-Image Intersection Table..................................................................6
Task 11: Insert Records to Image Table..........................................................................................7
Task 12: Insert Records to Message-Image Table...........................................................................8
Task 13: Find All of the Messages that Michael Phelps Sent.........................................................9
Task 14: Find the Number of Messages Sent for Every Person....................................................10
Task 15: Messages that Have At Least One Image Attached........................................................11
Bibliography:.................................................................................................................................12

2PERSONAL PLAYGROUND DATABASE SYSTEM
Task 1: Insert Record to the Person Table
insert into Person (first_name, last_name) Values ('Zayda', 'Bajana');
The MySQL allows the user to inert data into the table after creating the table. The syntax
for inserting a value in the table is insert into [Table Name] (column1, clumn2, ...) Values
(value1, value2, ...); The person_id is the primary key of the Person table. The person_id is set
as autoincrement. That is why the person_id value will not be inserted. The database will insert
the values automatically. As not all the values will be inserted using insert into query, it is
essential to specify the column name such as (first_name, last_name). Otherwise, (column1,
clumn2, ...) syntax can be omitted.
Task 2: Alter the Person Table
alter table Person ADD address varchar(25) NOT NULL;
Altering a table is an essential part of database. Alter tables are used for various reasons
like adding column, modifying column and many more. The syntax for using alter table query is
alter table [Table Name] Add/Modify [Column Name] [Data Type]; Alter table is also used for
dropping a column with syntax ALTER TABLE [Table Name] DROP COLUMN [Column
Name]; In this case, the alter table query has been used for adding a new column, address with
varchar(25) datatype, to the Person table.

3PERSONAL PLAYGROUND DATABASE SYSTEM
Task 3: Update Records in the Person Table
Update Person Set address = '12/1 Main Street' Where first_name='Zayda' and
last_name='Bajana';
In the domain of MySQL, ‘update’ query is used mostly because of updating a value in
the column. The task is to update the newly added value for user with first name Zayada and last
name Bajana. The syntax for updating a value is Update [Table Name] Set [Column Name] =
[Value] Where [Condition]; In this case, the address value is updated to ‘12/1 Main Street’. The
query checks whether the row with with first name Zayada and last name Bajana exists or not.
Task 4: Delete Records from Person Table
Delete From Person Where first_name='Diana' and last_name='Taurasi';
The delete statement has bee used to delete the row with first name Diana and last name
Taurasi. The syntax is DELETE FROM [Table Name] WHERE [Condition]; The MySQL
database terminal first checks the table Person exists or not and then checks whether the
condition is valid or not. If the condition is valid, the database terminal deletes the row. The
database also checks the referential integrity. If the deletion of a row affects the child column
then, based on delete option, the child value is set.
Task 5: Alter the Contact List Table
alter table contact_list ADD favorite varchar(10) NULL;