e1artists.txt 2 Ames 3 Aserty 4 Baron 7 Blain 8 Blum 9 Budd 12 Chico 14 Cox 16 C

Question

e1artists.txt

2 Ames

3 Aserty

4 Baron

7 Blain

8 Blum

9 Budd

12 Chico

14 Cox

16 Curtis

17 Dawson

19 Dill

22 Fratt

23 Garber

24 Garin

25 Giama

28 Guys

29 Hamend

32 Ibe

35 Irvin

38 Kritz

40 Long

43 Lutes

48 Metz

49 Miller

50 Mogan

52 Novarre

54 Ortega

55 Parker

56 Penn

59 Quiroz

60 Rath

--------------------

e1arts.txt

1042 Coffee on the Trail 2 7544

1013 Superstitions 3 78000

1021 Bead Wall 3 14000

1034 Beaver Pole Jumble 3 28000

1063 Asleep in the Garden 3 110000

1070 Beginnings 4 27500

1049 Buttercup with Red Lip 7 400

1018 Mountain Scene 8 2500

1055 Starlit Evening 9 9500

1003 Spring Flowers 12 2400

1039 Treachery 14 20000

1102 Crying Hats 14 10000

1052 American Rodeo 16 3500

1059 Dwelling 17 16000

1011 Eve 19 975

1109 Friends 22 16000

1084 Crossing the Platt River 23 2200

1072 Funnel 24 4500

1115 Starry Night 25 8500

1009 Amen 28 3000

1030 Ash Bench 28 13000

1043 Creosote Bushes 28 18000

1078 Chuckwagon 28 32000

1041 Night Version 29 3800

1082 Spring Flowers 29 20000

1006 House Remembered 32 700

1107 Striking It Rich 35 1750

1045 Leaf Patterns 38 2100

1100 Hungry Cowboys 38 750

1106 Horse Corral 40 12500

1044 Mexican Fiesta 43 14000

1024 Spirit and Nature 48 592

1067 Owl in Flight 49 7000

1001 Red Rock Mountain 50 18000

1028 Tired Cowboy 50 4700

1054 Snake Charmer 50 4500

1068 Moonlight 50 9750

1069 Renaissance 50 5500

1113 Shadow House 50 5500

1114 Storytelling at the Campfire 50 18000

1002 Offerings 52 10000

1091 Stone Palette 54 11500

1074 Storm on the Rise 55 8000

1098 Sweet Project 56 592

1080 The Dust Behind 59 18000

1058 The Gathering 60 250

Exam 2 (Date: 03/06/2018) The purpose of this exam is to test your understanding of the following concepts: 1. Use of MySQL 2. Create a database. 3. Create Tables. 4. Populate tables. 5. Data manipulation using MySQL SQL language. . Note 1. This exam only tests your ability to use MySQL However, since the entire question set depends on your successful construction of database and tables, so failure of these basic operations will imply your incapability of taking this exam and render you a 0 grade for the exam. The next exam will be based upon the basic database operations tested in this exam, so the better grade of the next exam will replace this one. Therefore, do not be disheartened if you do it poorly this time. Nevertheless, the next exam will be more involved than this one, so do not take this exam lightly because you may not do it better next time As usual, no make-up exam without valid documents. 2 3. How the exam is graded: 1. 2. We start with the full 50 points. After your database and tables are constructed, 5 points will be taken off for failure of completing an operation, and 2 points will be taken off for every answer that does not conform to the specification. Specifications: You will need to use Snipping Tools to record the required steps to a file called "exam2 yourName.docx" under "exam2" sub-folder under "wwwlcsc264". Also, check the last step before you start, you should do it concurrently with the whole process. Consider the first two records of the following two files a. "e01artists.txt 2 Ames 3 Aserty The first field is "artistID and the second one is "artistName

Explanation / Answer

-- 1)

CREATE DATABASE myarts;

-- 2)

CREATE TABLE artists(

artistID int PRIMARY KEY,

artistName VARCHAR(255)

);

CREATE TABLE arts(

artID int PRIMARY KEY,

artName VARCHAR(255),

artistID int,

artValue int,

FOREIGN KEY(artistID) artists(artistID) ON UPDATE CASCADE ON DELETE CASCADE

);

-- 3)

USE myarts;

SHOW TABLES;

-- 4)

DESCRIBE artists;

DESCRIBE arts;

-- 5)

INSERT INTO artists VALUES(2, 'Ames');

INSERT INTO artists VALUES(3, 'Aserty');

INSERT INTO artists VALUES(4, 'Baron');

INSERT INTO artists VALUES(7, 'Blain');

INSERT INTO artists VALUES(8, 'Blum');

INSERT INTO artists VALUES(9, 'Budd');

INSERT INTO artists VALUES(12, 'Chico');

INSERT INTO artists VALUES(14, 'Cox');

INSERT INTO artists VALUES(16, 'Curtis');

INSERT INTO artists VALUES(17, 'Dawson');

INSERT INTO artists VALUES(19, 'Dill');

INSERT INTO artists VALUES(22, 'Fratt');

INSERT INTO artists VALUES(23, 'Garber');

INSERT INTO artists VALUES(24, 'Garin');

INSERT INTO artists VALUES(25, 'Giama');

INSERT INTO artists VALUES(28, 'Guys');

INSERT INTO artists VALUES(29, 'Hamend');

INSERT INTO artists VALUES(32, 'Ibe');

INSERT INTO artists VALUES(35, 'Irvin');

INSERT INTO artists VALUES(38, 'Kritz');

INSERT INTO artists VALUES(40, 'Long');

INSERT INTO artists VALUES(43, 'Lutes');

INSERT INTO artists VALUES(48, 'Metz');

INSERT INTO artists VALUES(49, 'Miller');

INSERT INTO artists VALUES(50, 'Mogan');

INSERT INTO artists VALUES(52, 'Novarre');

INSERT INTO artists VALUES(54, 'Ortega');

INSERT INTO artists VALUES(55, 'Parker');

INSERT INTO artists VALUES(56, 'Penn');

INSERT INTO artists VALUES(59, 'Quiroz');

INSERT INTO artists VALUES(60, 'Rath');

INSERT INTO arts VALUES(1042, 'Coffe on the Trail', 2, 7544);

INSERT INTO arts VALUES(1013, 'Superstitions', 3, 78000);

INSERT INTO arts VALUES(1042, 'Coffee on the Trail', 2, 7544);

INSERT INTO arts VALUES(1013, 'Superstitions', 3, 78000);

INSERT INTO arts VALUES(1021, 'Bead Wall', 3, 14000);

INSERT INTO arts VALUES(1034, 'Beaver Pole Jumble', 3, 28000);

INSERT INTO arts VALUES(1063, 'Asleep in the Garden', 3, 110000);

INSERT INTO arts VALUES(1070, 'Beginnings', 4, 27500);

INSERT INTO arts VALUES(1049, 'Buttercup with Red Lip', 7, 400);

INSERT INTO arts VALUES(1018, 'Mountain Scene', 8, 2500);

INSERT INTO arts VALUES(1055, 'Starlit Evening', 9, 9500);

INSERT INTO arts VALUES(1003, 'Spring Flowers', 12, 2400);

INSERT INTO arts VALUES(1039, 'Treachery', 14, 20000);

INSERT INTO arts VALUES(1102, 'Crying Hats', 14, 10000);

INSERT INTO arts VALUES(1052, 'American Rodeo', 16, 3500);

INSERT INTO arts VALUES(1059, 'Dwelling', 17, 16000);

INSERT INTO arts VALUES(1011, 'Eve', 19, 975);

INSERT INTO arts VALUES(1109, 'Friends', 22, 16000);

INSERT INTO arts VALUES(1084, 'Crossing the Platt River', 23, 2200);

INSERT INTO arts VALUES(1072, 'Funnel', 24, 4500);

INSERT INTO arts VALUES(1115, 'Starry Night', 25, 8500);

INSERT INTO arts VALUES(1009, 'Amen', 28, 3000);

INSERT INTO arts VALUES(1030, 'Ash Bench', 28, 13000);

INSERT INTO arts VALUES(1043, 'Creosote Bushes', 28, 18000);

INSERT INTO arts VALUES(1078, 'Chuckwagon', 28, 32000);

INSERT INTO arts VALUES(1041, 'Night Version', 29, 3800);

INSERT INTO arts VALUES(1082, 'Spring Flowers', 29, 20000);

INSERT INTO arts VALUES(1006, 'House Remembered', 32, 700);

INSERT INTO arts VALUES(1107, 'Striking It Rich', 35, 1750);

INSERT INTO arts VALUES(1045, 'Leaf Patterns', 38, 2100);

INSERT INTO arts VALUES(1100, 'Hungry Cowboys', 38, 750);

INSERT INTO arts VALUES(1106, 'Horse Corral', 40, 12500);

INSERT INTO arts VALUES(1044, 'Mexican Fiesta', 43, 14000);

INSERT INTO arts VALUES(1024, 'Spirit and Nature', 48, 592);

INSERT INTO arts VALUES(1067, 'Owl in Flight', 49, 7000);

INSERT INTO arts VALUES(1001, 'Red Rock Mountain', 50, 18000);

INSERT INTO arts VALUES(1028, 'Tired Cowboy', 50, 4700);

INSERT INTO arts VALUES(1054, 'Snake Charmer', 50, 4500);

INSERT INTO arts VALUES(1068, 'Moonlight', 50, 9750);

INSERT INTO arts VALUES(1069, 'Renaissance', 50, 5500);

INSERT INTO arts VALUES(1113, 'Shadow House', 50, 5500);

INSERT INTO arts VALUES(1114, 'Storytelling at the Campfire', 50, 18000);

INSERT INTO arts VALUES(1002, 'Offerings', 52, 10000);

INSERT INTO arts VALUES(1091, 'Stone Palette', 54, 11500);

INSERT INTO arts VALUES(1074, 'Storm on the Rise', 55, 8000);

INSERT INTO arts VALUES(1098, 'Sweet Project', 56, 592);

INSERT INTO arts VALUES(1080, 'The Dust Behind', 59, 18000);

INSERT INTO arts VALUES(1058, 'The Gathering', 60, 250);

-- 6)

SELECT * FROM artists;

SELECT * FROM arts;

-- 7)

SELECT * FROM arts;

-- 8)

SELECT artValue FROM arts WHERE artID = 1013;

-- 9)

SELECT SUM(artValue) FROM arts;

-- 10)

SELECT AVG(artValue) FROM arts;

-- 11)

SELECT artName FROM arts WHERE artValue = (SELECT MAX(artValue) FROm arts);

-- 12)

SELECT artID, artName, arts.artistID, artistName, artValue FROM arts LEFT JOIN artists ON artists.artistID = arts.artistID;

-- 13)

UPDATE arts SET artValue = artValue+(artValue*0.10);

-- 14)

UPDATE arts SET artName = CONCAT(' ', artName);

-- 15)

SELECT artistID, artistName, artName FROM arts NATURAL JOIN artists WHERE artValue = (SELECT MAX(artValue) FROM arts);

-- 16)

DELETE FROM arts WHERE artID = 1013;

-- 17)

DELETE FROM arts WHERE artID=2;

-- It will fail, because of foreign key constraint...

-- 18)

DELETE FROM arts;

DELETE FROM artists;

-- 19)

DROP TABLE arts;

DROP TABLE artists;

-- 20)

DROP DATABASE myarts;

-- 21)

-- save this file with name of buidmyarts.sql

Related Questions

Navigate

• Browse (All)
• Browse E
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.