Assignment-5 - Priya
docx
keyboard_arrow_up
School
Southeast Missouri State University *
*We aren’t endorsed by this school
Course
580
Subject
Information Systems
Date
Feb 20, 2024
Type
docx
Pages
8
Uploaded by GeneralExploration6683
Question 3 (20 points):
Check Appendix A first.
In MySQL and for the following DB
• Create scheme.
• Create the tables: 1) Customer 2) Invoice. For all tables, take a Print Screen of SQL
script
and paste it into the word document. Don’t crop/cut your images. I want to see the
complete screen including the time and date on the right lower corner. If any part of the
image is cut/cropped, you will receive a zero for this question.
1.
CREATE TABLE `test`.`customer` (
`CustomerId` INT NOT NULL,
`Name` VARCHAR(45) NULL,
`EmailAddress` VARCHAR(45) NULL,
`Gender` VARCHAR(45) NULL,
`EmailVerified` INT NULL,
PRIMARY KEY (`CustomerId`));
2.
CREATE TABLE `test`.`invoice` (
`InvoiceId` INT NOT NULL,
`CustomerId` VARCHAR(45) NULL,
`Amount` DECIMAL(20) NULL,
`DateCreated` DATETIME NULL,
PRIMARY KEY (`InvoiceId`));
• Insert data into table /fill table with data. For all tables, take a Print Screen of the tables
(with data) and paste it into the word document. Don’t crop/cut your images. I want to
see the complete screen including the time and date on the right lower corner. If any
part of the image is cut/cropped, you will receive a zero for this question.
1.
INSERT INTO `test`.`customer` (`CustomerId`, `Name`, `EmailAddress`, `Gender`, `EmailVerified`) VALUES (1, 'Jack Frost ', 'jfrost@winter.com', 'Male', 1);
2.
INSERT INTO `test`.`customer` (`CustomerId`, `Name`, `EmailAddress`, `Gender`, `EmailVerified`) VALUES (2, 'Miss Piggy', 'queen@muppets', 'Female', 1);
3.
INSERT INTO `test`.`customer` (`CustomerId`, `Name`, `EmailAddress`, `Gender`, `EmailVerified`) VALUES (3, 'Dr.Octopus', 'doc@octopus.net', 'Male', 0);
1.
INSERT INTO `test`.`invoice` (`InvoiceId`, `CustomerId`, `Amount`, `DateCreated`) VALUES (1, '1', 80, '2010-12-11 04:19:12');
2.
INSERT INTO `test`.`invoice` (`InvoiceId`, `CustomerId`, `Amount`, `DateCreated`) VALUES (2, '2', 24.95, '2011-01-05 16:35:56');
3.
INSERT INTO `test`.`invoice` (`InvoiceId`, `CustomerId`, `Amount`, `DateCreated`) VALUES (3, '1', 25, '2011-01-07 20:05:33');
4.
INSERT INTO `test`.`invoice` (`InvoiceId`, `CustomerId`, `Amount`, `DateCreated`) VALUES (4, '1', 45, '2011-02-20 08:09:42');
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
• Delete one row from the table. 1) Show a picture of the table before operation. 2) Show
SQL script. 3) Show a picture of the table after operation
This is before delete operation.
DELETE FROM `test`.`customer` WHERE 'CustomerId' = '1';
This is after delete operation.
• List the ID, name and email of customer information (use SELECT). Take a Print
Screen of
the output new table (with data) and paste it into the word document. Don’t crop/cut
your images. I want to see the complete screen including the time and date on the right
lower corner. If any part of the image is cut/cropped, you will receive a zero for this
question.
SELECT CustomerId,Name,EmailAddress FROM `test`.`customer`;
• Update the email address of Dr. Octopus to doc123@octopus.net. Do the following 1)
Show a picture of the table before operation. 2) Show SQL script. 3) Show a picture of
the table after operation
This is before update operation.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
UPDATE `test`.`customer` SET `EmailAddress` = 'doc123@octopus.net' WHERE 'CustomerId' = '3';
This is after update operation.