Cn069X0T

1#lets first just delete the database ... if it exists it gets deleted, if it doesnt exist. no big deal.
2Drop database tacoDatabase;
3#now lets create the database named tacoDatabase
4Create database tacoDatabase;
5#lets now select the database we want to work with (the one we just created 'tacoDatabase')
6use tacoDatabase;
7#lets create a table called tacos. we dont need to drop the table first because we just created the database from scratch so there are no tables!
8#so lets create a table with 2 fields, id (auto_increment) and taconame  you can research primary keys later.
9Create table tacos
10(id int not null AUTO_INCREMENT,
11 taconame varchar(50) null,
12 PRIMARY KEY (id));
13#opps, we forgot to add a price column.. lets alter the table to add the price column to the schema
14alter table tacos add price double;
15#now lets populate some sample data
16
17insert into tacos(taconame, price) 
18values 
19('soft taco', 1.99),
20('hard taco', 1.99),
21('crispy chicken taco', 2.49);
22
23#now if you do SELECT * FROM tacos you should see the 3 entries you just entered.
24
25#lets change the price of the crispy chicken taco
26update tacos set price=2.99 WHERE taconame = 'crispy chicken taco';
27
28#lets add a seasonal taco to the menu :)
29insert into tacos(taconame,price) values ('pumpkinspice taco', 2.89);
30
31#actually, lets make it the same price as the hard taco .. to do that we need to first grab the price of the hard taco..which we would normally nest into a subquery in mssql but mysql wont allow it so we declare a variable and stuff it in there.
32select @hardtacoprice:= price from tacos where taconame ='hard taco';
33#then we update tacos setting the price to the @hardtacoprice variable where the taconame = our super disgusting pumpkinspice taco.
34update tacos set price = @hardtacoprice where taconame = 'pumpkinspice taco';
35#acutally, this taco is disgusting lets just delete it
36delete from tacos where taconame='pumpkinspice taco';
37#now lets add a replacement taco in
38
39insert into tacos(taconame, price) values ('cherry taco', 2.99); 
40#you'll notice that this new item has an id of 5. not 4.. because 4 used to belong to the pumpkinspice taco.. which is no longer in existence.
41#lets have a look
42select * from tacos;
43
44#lets alphabetize them ascending a-z
45select * from tacos order by taconame asc;
46
47#lets reverse them so they are z to a in a DESCending fashion
48select * from tacos order by taconame desc;
49#lets add a bakers dozen 
50insert into tacos(taconame, price) values
51('apple taco',1.50),('banana taco', 1.45),('cupcake taco', 3.99),
52('duck taco' ,2.45),('elephant taco',5.99),('fat taco', 4.99),
53('giant taco',3.99),('herpes taco',0.59),('implant taco',4.25),
54('jelly taco', 1.99),('abacore taco', 2.00),('brown taco', 1.40),
55('creepy taco',2.25);
56#lets have a look
57select * from tacos;
58
59#lets now order them alphabetically again
60select * from tacos order by taconame asc;
61#lets now order them by price
62select * from tacos order by price asc;
63
64#lets order by alphabetical then by price
65select * from tacos order by taconame asc, price asc