# /opt/lampp/bin/mysqladmin -u root create example

# mysql -u root
# > connect example;



CREATE TABLE `vat` (
 `vatID` TINYINT NOT NULL AUTO_INCREMENT ,
 `rate` DECIMAL NOT NULL ,
 PRIMARY KEY ( `vatID` ) 
) COMMENT = 'The table containing VAT rates';


CREATE TABLE `article` (
 `articleID` INT NOT NULL AUTO_INCREMENT ,
 `name` VARCHAR( 100 ) NOT NULL ,
 `vatID` TINYINT NOT NULL ,
 `categoryID` INT NOT NULL ,
 `Price` DECIMAL NOT NULL ,
 PRIMARY KEY ( `articleID` ) 
);

CREATE TABLE `category` (
 `name` VARCHAR( 100 ) NOT NULL ,
 `categoryID` TINYINT NOT NULL AUTO_INCREMENT ,
 PRIMARY KEY ( `categoryID` ) 
);


ALTER TABLE `article` ADD `description` TEXT NOT NULL ;

ALTER TABLE `article` CHANGE `Price` `price` DECIMAL( 10, 2 ) DEFAULT '0' NOT NULL;

ALTER TABLE `vat` CHANGE `rate` `rate` DECIMAL( 10, 2 ) DEFAULT '0' NOT NULL;

INSERT INTO `article` ( `articleID` , `name` , `vatID` , `categoryID` , `price` , `description` ) 
VALUES (
 '', 'Pencil', '0', '0', '1.50', ''
);

INSERT INTO article values ('','Mercedes Class E','0','0','100000','The same Mercedes Lady Diana has used');

select * from article;

UPDATE `article` SET `description` = 'A very nice black pencil with white stripes' WHERE `articleID` = '1' LIMIT 1 ;



show tables;

show columns from vat;


ALTER table vat change `rate`  `rate` DECIMAL(10,2) DEFAULT '0' NOT NULL;

insert into vat values ('', '8.00');

update article set vatID = 1 where 1;

select name, vatID from article;

select article.name, vat.rate, article.price from article, vat where article.vatID= vat.vatID;

select article.name, vat.rate, article.price, article.price*(1+vat.rate/100) as priceWithVAT from article, vat where article.vatID= vat.vatID;

select article.name, vat.rate, article.price, article.price*(1+vat.rate/100) as priceWithVAT from article inner JOIN vat on article.vatID= vat.vatID;

INSERT INTO `article` ( `articleID` , `name` , `vatID` , `categoryID` , `price` , `description` ) 
VALUES (
 '', 'Pencil', '0', '0', '1.50', ''
);


select article.name  from article LEFT JOIN vat on article.vatID= vat.vatID where vat.rate is null;

ALTER TABLE t2 DROP COLUMN c, DROP COLUMN d;


create temporary table valueVAT (select vat.rate, article.name from vat,article where vat.vatID=article.vatID);

select * from valueVAT;

drop table IF EXISTS valueVAT;

select article.name, test.rate from article, (select vat.rate, article.name from vat,article where vat.vatID=article.vatID) as test where article.name = test.name;



select name, price from article order by price desc;

select vatID, count(vatID) from article GROUP BY vatID;

insert into article values(0,'Nesquik',1,1,'10.90','Super chocolat for the kids');

insert into article values(0,'Nesquik',1,1,'10.90','Super chocolat for the kids');

delete from article where articleID=3;