Dhgf2Wp6

1#Please add your name and the purpose of this script in comments here
2/*
3*Name: Bhargav Prajapati
4*Date: June 6, 2018
5*Purpose:To create a database to store a table with drink information. To write UPDATE and DELETE statements to query the data.
6*/
7
8#Fill in all the comments on lines 7, 12, 17, 20, 30 and 47
9
10#Complete the update and delete statements given at the end of this script
11
12
13#drop any existing database; create a new one and use it
14DROP DATABASE IF EXISTS db_drinks;
15CREATE DATABASE db_drinks;
16USE db_drinks;
17
18#set the environment
19SET SQL_MODE = "STRICT_ALL_TABLES";
20SET SQL_SAFE_UPDATES = 0;
21
22
23#drop the table, if it exists
24DROP TABLE IF EXISTS tbl_drinks;
25
26#create a table to store drinks information
27CREATE TABLE tbl_drinks(
28	drink_name  VARCHAR(20) NOT NULL,
29	cost		DEC(4,2) UNSIGNED NOT NULL,
30	carbs		DEC(4,1) UNSIGNED NOT NULL,
31	color		VARCHAR(10) NOT NULL,
32	ice	        ENUM('Y','N') NOT NULL,
33	calories	TINYINT UNSIGNED NOT NULL
34);
35
36#inserting records into the table
37INSERT INTO tbl_drinks
38VALUES
39('Blackthorn', 3, 8.4, 'Yellow', 'Y', 33),
40('Blue Moon', 2.5, 3.2, 'Blue', 'Y', 12),
41('Oh My Gosh', 3.5, 8.61, 'Orange', 'Y', 35),
42('Lime Fizz', 2.5, 5.4, 'Green', 'Y', 24),
43('Kiss on the Lips', 5.5, 42.52, 'Purple', 'Y', 171),
44('Hot Gold', 3.2, 32.1, 'Orange', 'N', 135),
45('Lone Tree', 3.6, 4.2, 'Red', 'Y', 17),
46('Greyhound', 4, 14, 'Yellow', 'Y', 50),
47('Indian Summer', 2.8, 7.2, 'Brown', 'N', 30),
48('Bull Frog', 2.6, 21.5, 'Tan', 'Y', 80),
49('Soda and It', 3.8, 4.7, 'Red', 'N', 19),
50('P', 3.8, 4.7, 'Red', 'N', 19),
51('Pa', 3.8, 4.7, 'Red', 'N', 19);
52
53#select all the columns(*) and rows from the table
54SELECT * FROM tbl_drinks;
55
56########### UPDATES #######################
57
58
59#1 Update the cost of Blue Moon to $3.50
60SELECT * FROM tbl_drinks;
61
62UPDATE tbl_drinks
63SET cost = 3.50
64WHERE drink_name = 'Blue Moon';
65
66SELECT * FROM tbl_drinks;
67
68#2 Increase the cost by $1 for the drinks Oh My Gosh and Lime Fizz using a single update statement.
69SELECT * FROM tbl_drinks;
70
71UPDATE tbl_drinks
72SET cost = cost + 1
73WHERE drink_name = 'Oh My Gosh' OR drink_name = 'Lime Fizz';
74
75SELECT * FROM tbl_drinks;
76
77#3 Change the orange colored drinks to rust colored drinks 
78SELECT * FROM tbl_drinks;
79
80UPDATE tbl_drinks
81SET color = 'Rust'
82WHERE color = 'Orange';
83
84SELECT * FROM tbl_drinks;
85
86#4a Set the ice field to N where it is NULL
87SELECT * FROM tbl_drinks;
88
89UPDATE tbl_drinks
90SET ice  = 'N'
91WHERE ICE IS NULL;
92
93SELECT * FROM tbl_drinks;
94
95#4b Set the ice field for all the drinks to Y
96SELECT * FROM tbl_drinks;
97
98UPDATE tbl_drinks
99SET ice  = 'Y';
100
101SELECT * FROM tbl_drinks;
102
103#5 Update the cost to $4.50 for all those drinks that are less than or equal to $5
104SELECT * FROM tbl_drinks;
105
106UPDATE tbl_drinks
107SET cost = 4.50
108WHERE cost <= 5;
109
110SELECT * FROM tbl_drinks;
111
112
113#6 Update the cost to $6 for all the drinks where carbs are greater than 20 and calories at least 80
114SELECT * FROM tbl_drinks;
115
116UPDATE tbl_drinks
117SET cost = 6
118WHERE carbs > 20 AND calories >= 80;
119
120SELECT * FROM tbl_drinks;
121
122#7 Change the ice to N and color to red for Oh My Gosh
123SELECT * FROM tbl_drinks;
124
125UPDATE tbl_drinks
126SET ice  = 'N', color = 'Red'
127WHERE drink_name = 'Oh My Gosh';
128
129SELECT * FROM tbl_drinks;
130
131#8 Set the color of Oh My Gosh by appending(add at end) 'green' to its color
132SELECT * FROM tbl_drinks;
133
134UPDATE tbl_drinks
135SET color = concat(color, "Green")
136WHERE drink_name = 'Oh My Gosh';
137
138SELECT * FROM tbl_drinks;
139
140
141
142############## DELETES ###############
143
144#1 Delete all records with no ice
145SELECT * FROM tbl_drinks;
146
147DELETE FROM tbl_drinks
148WHERE ice = 'n';
149
150SELECT * FROM tbl_drinks;
151
152#2 Delete the drink 'p'
153SELECT * FROM tbl_drinks;
154
155DELETE FROM tbl_drinks
156WHERE drink_name = 'p';
157
158SELECT * FROM tbl_drinks;
159
160#3 Delete all rust and brown colored drinks
161SELECT * FROM tbl_drinks;
162
163DELETE FROM tbl_drinks
164WHERE color = 'Rust' OR color = 'Brown';
165
166SELECT * FROM tbl_drinks;
167
168#4 Delete all drinks with null calories
169SELECT * FROM tbl_drinks;
170
171DELETE FROM tbl_drinks
172WHERE calories IS NULL;
173
174SELECT * FROM tbl_drinks;
175
176#5 Delete all records 
177SELECT * FROM tbl_drinks;
178
179DELETE FROM tbl_drinks;
180
181SELECT * FROM tbl_drinks;