artists.txt 2 Ames 3 Aserty 4 Baron 7 Blain 8 Blum 9 Budd 12 Chico 14 Cox 16 Cur
ID: 3737848 • Letter: A
Question
artists.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
=======
arts.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
First 4 are done
O You will need to use Snipping Tools to record the required steps to a file called yourName.docx." Also, check the last step before you start, you should do it concurrently with the whole process. Submit that sql file as well. Consider the first two records of the following two files O a. "artists.txt" Ames 3 Aserty The first field is "artistID" and the second one is "artistName". b. "arts.txt 1042 Coffee on the Trail 2 1013 Superstitions 3 The names of the fields for each record are: "artID", "artName", "artistID", and "artValue". 7544 78000 O Do and record the following operations: (Just record the successful operations in the order specified.) 1) Build a database called "myarts". 2) Construct two tables: one is called "artists" and the other is called "arts" i. The names of the fields should be the same as specified above. ii. You will choose the appropriate data type for each field ii. Since "arts" table will rely on "artists" table for the artist name, so make sure that to limit the deletion of artist entry only when that particular "artistID" no longer exists in the "arts" table 3) Display what tables are there in the database "myarts" and display the field information of each table Populate two tables using the sample records given above and see if there are any records in these two tables. Display all records from "arts" table, display the art Value of art ID number 1013 and display the total value of arts. (i.e., the entire "artValue" column.), the average value of arts. (i.e., the entire "artValue" column.), and artName for the art piece that has the maximum value. Then display all records from "arts" but have Artist Name display after Artist ID. For example, you correct output will print something similar to the following 4) 5) artID artName 1042 Coffee on the Trail 1013 Superstitions artistIDartistName artValue 7544 78000 Ames 3 Aserty Add 10% to the current value of all Art Values and add 5 more spaces to the "artName" so that it can hold a longer name Display "artistID", "artistName", and "artName" for the art piece that has the maximum value. 6) 7) Delete the record of artID of 1013 and try to delete Art ID 2 from "artists" table (Noe: "arts" table still has the entry with Artist ID 2. Delete all records from both tables, both tables from the database, and the database Write a SQL file called "buildmyarts.sql" and use it to build the database 'myarts', construct the two tables mentioned previously, and populate the tables with the two sample records for each table 8)Explanation / Answer
Hi,
Please find the below queries. As i was not able to attach file for question 8 i'm pasting content at the bottom itself.
1)
CREATE DATABASE myarts;
USE myarts;
2)
CREATE TABLE ARTISTS (ARTISTID INT PRIMARY KEY, ARTISTNAME VARCHAR(100));
CREATE TABLE ARTS (ARTID INT PRIMARY KEY, ARTNAME VARCHAR(200), ARTISTID INT, ARTVALUE INT , FOREIGN KEY FK_ART (ARTISTID) REFERENCES privacy_level (level) ON DELETE RESTRICT);
3)
SHOW TABLES;
SHOW COLUMNS FROM ARTISTS;
SHOW COLUMNS FROM ARTS;
4)
SELECT * FROM ARTISTS;
SELECT * FROM ARTS;
5)
SELECT * FROM ARTS;
SELECT ARTVALUE FROM ARTS WHERE ARTID = '1013';
SELECT SUM(ARTVALUE) SUM1,AVG(ARTVALUE) AVG1 FROM ARTS;
SELECT ARTNAME FROM ARTS WHERE ARTVALUE = (SELECT MAX(ARTVALUE) FROM ARTS);
SELECT AR.ARTID, AR.ARTNAME, AR.ARTISTID, ATT.ARTISTNAME, AR.ARTVALUE FROM ARTS AR, ARTISTS ATT WHERE ATT.ARTISTID = AR.ARTISTID;
update ARTS set ARTVALUE = ARTVALUE + (ARTVALUE * 10.0 / 100.0)
6)
SELECT AR.ARTISTID, ATT.ARTISTNAME, AR.ARTNAME FROM ARTS AR, ARTISTS ATT WHERE ATT.ARTISTID = AR.ARTISTID
AND AR.ARTVALUE = (SELECT MAX(ARTVALUE) FROM ARTS);
7)
DELETE FROM ARTS WHERE ARTID = '1013';
DELETE FROM ARTISTS WHERE ARTISTID = '2';
DELETE FROM ARTS;
DELETE FROM ARTISTS
DROP DATABASE IF EXISTS myarts;
8)
CREATE DATABASE myarts;
USE myarts;
CREATE TABLE ARTISTS (ARTISTID INT PRIMARY KEY, ARTISTNAME VARCHAR(100));
CREATE TABLE ARTS (ARTID INT PRIMARY KEY, ARTNAME VARCHAR(200), ARTISTID INT, ARTVALUE INT , FOREIGN KEY FK_ART (ARTISTID) REFERENCES privacy_level (level) ON DELETE RESTRICT);
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,'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', 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',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 ',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);
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.