[DB21] New Database - Import from DUMP

db21.sql

create database db21 charset latin1 collate latin1_swedish_ci;

create database db23 charset latin1 collate latin1_swedish_ci;

mysql -u root db22 < C:/Users/danie/Documents/Universidade/university/Databases/MariaDbDatabases/db4.sql
mysql -u root db22 > C:/Users/danie/Documents/Universidade/university/Databases/MariaDbDatabases/db55.sql

C:/Users/danie/Documents/Universidade/university/Databases/MariaDbDatabases/db-hp-calc-input.csv

-- insert products CSV
-- create a file called db-hp-calc-input.csv and paste the following values
-- \N,DIG,HP Prime,20,159.00
LOAD DATA LOCAL INFILE 'C:/Users/danie/Documents/Universidade/university/Databases/MariaDbDatabases/db-hp-calc-input.csv' INTO TABLE products COLUMNS
TERMINATED BY ',';

-- insert products with tab
-- create a file called db-input-pencil.tsv and paste the following values
--\N	PEC	Pencil	3B	500	0.52
--\N	PEC	Pencil	4B	200	0.62
--\N	PEC	Pencil	5B	100	0.73
--\N	PEC	Pencil	6B	500	0.47

LOAD DATA LOCAL INFILE 'C:/Users/danie/Documents/Universidade/university/Databases/MariaDbDatabases/db-input-pencil.tsv' INTO TABLE products COLUMNS
TERMINATED BY '\t';

--+-----------+-------------+-----------------------+----------+--------+
--| productID | productCode | name                  | quantity | price  |
--+-----------+-------------+-----------------------+----------+--------+
--|      1001 | PEN         | Pen Red               |     4900 |   1.23 |
--|      1002 | PEN         | Pen Blue              |     8000 |   1.52 |
--|      1003 | PEN         | Pen Black             |     2000 |   1.52 |
--|      1004 | PEC         | Pencil 2B             |    10000 |   0.58 |
--|      1005 | PEC         | Pencil 2H             |     8000 |   0.59 |
--|      1006 | NTB         | Notebook A4L90p       |     9000 |   3.99 |
--|      1007 | NTB         | Notebook B5L60p       |     8000 |   3.39 |
--|      1008 | NTB         | Notebook A5L50p       |    68500 |   2.66 |
--|      1009 | NTB         | Notebook B6L40p       |    68500 |   2.42 |
--|      1010 | PAP         | Paper A4W80/500       |     1000 |   6.05 |
--|      1011 | PAP         | Paper A4E80/500       |     1100 |   5.93 |
--|      1012 | RUB         | Rubber white Soft     |     1200 |   2.41 |
--|      1013 | RUB         | Rubber color Medium   |     1500 |   1.20 |
--|      1014 | DIG         | iPad mini-4-Wi-Fi-128 |        4 | 494.99 |
--|      1015 | DIG         | HP Prime              |       20 | 159.00 |
--|      1016 | PEC         | Pencil                |        3 | 500.00 |
--|      1017 | PEC         | Pencil                |        4 | 200.00 |
--|      1018 | PEC         | Pencil                |        5 | 100.00 |
--|      1019 | PEC         | Pencil                |        6 | 500.00 |
--+-----------+-------------+-----------------------+----------+--------+


-- reset auto increment
------------tablename-----------------value
ALTER TABLE products AUTO_INCREMENT = 1016;

UPDATE products set quantity = quantity - 100 where name = 'Pen Red';

UPDATE products set quantity = quantity + 50, price = 1.30 where name = 'Pen Red';

UPDATE products set quantity = quantity - 100 where productCode = 'PAP';

UPDATE products set quantity = quantity - 1500 where (name = 'Notebook A5L50p' OR name = 'Notebook B6L40p');
UPDATE products set quantity = quantity - 1500 where like '%A5L%' or name = '%B6L%';

SELECT productCode as Code, name, price * quantity as Balance from products Order by 1,3 DESC;

SELECT COALESCE(productCode, 'Total') as Code, (quantity * price) as Balance from products group by productCode WITH ROLLUP;

mysqldump -u root db22 > C:/Users/danie/Documents/Universidade/university/Databases/MariaDbDatabases/db-bkup-%date:~0,2%%date:~3,2%%date:~6,4%.sql

mysql -u root db23 < C:/Users/danie/Documents/Universidade/university/Databases/MariaDbDatabases/db-bkup-19062020.sql

CREATE table if not exists suppliers (
    suppID INT(4) unsigned not null AUTO_INCREMENT,
    name char(30) not null DEFAULT '',
    phone char(9) not null DEFAULT '',
    primary key (suppID)
    );

INSERT INTO suppliers VALUES 
(501, 'Paper & Office Supplies Co.', 123456789),
(\N, 'Books & School Ltd', 234567890),  
(\N, 'Electronics Corp.', 345678901);

ALTER TABLE products ADD COLUMN suppID INT UNSIGNED NOT NULL;

INSERT INTO products VALUES 
(\N, 'PEC', 'Pencil 3B','500','0.52',501),
(\N, 'PEC', 'Pencil 4B','200','0.62',501), 
(\N, 'PEC', 'Pencil 5B','100','0.73',501),
(\N, 'PEC', 'Pencil 6B','500','0.47',501);


UPDATE products SET suppID = 502 WHERE productID= '1022'; 

select products.name, price, suppliers.name from products 
join suppliers on products.suppID = suppliers.suppID where price > 0.4


SELECT products.name, price, suppliers.name  
FROM products, suppliers  
WHERE products.suppID = suppliers.suppID AND price < 0.6;


SELECT products.name AS 'Product Name', price AS Price, 
suppliers.name AS 'Supplier Name' 
FROM products 
JOIN suppliers ON products.suppID = suppliers.suppID
WHERE price < 0.6;


CREATE TABLE products_suppliers (
productID   INT UNSIGNED  NOT NULL,
suppID  INT UNSIGNED  NOT NULL,
PRIMARY KEY (productID, suppID),
FOREIGN KEY (productID)  REFERENCES products  (productID),
FOREIGN KEY (suppID) REFERENCES suppliers (suppID)
);

INSERT INTO suppliers VALUES 
(501, 'Paper & Office Supplies Co.', 123456789),
(\N, 'Books & School Ltd', 234567890),  
(\N, 'Electronics Corp.', 345678901);

INSERT INTO products_suppliers VALUES
(1001, 501), 
(1002, 501),
(1003, 501), 
(1004, 502),
(1005, 502),
(1006, 502),
(1001, 503),
(1005, 503);


ALTER TABLE products DROP FOREIGN KEY products_ibfk_1;


Alter table products drop suppID;

DELETE FROM suppliers WHERE suppID = 501;


SELECT products.name AS 'Product Name', price, 
suppliers.name AS 'Supplier Name'
FROM products_suppliers 
JOIN products  ON products_suppliers.productID = products.productID
JOIN suppliers ON products_suppliers.suppID = suppliers.suppID
WHERE price < 0.6;


SELECT products.name AS 'Product Name', price, 
suppliers.name AS 'Supplier Name'
FROM products, suppliers, products_suppliers
WHERE products_suppliers.productID = products.productID
AND products_suppliers.suppID = suppliers.suppID
AND price < 0.6;


SELECT p.name AS 'Product Name', s.name AS 'Supplier Name'
FROM products AS p, products_suppliers AS ps, suppliers AS s
WHERE p.name = 'Pencil 3B'
AND ps.productID = p.productID 
AND ps.suppID = s.suppID;

SELECT p.name AS 'Product Name', s.name AS 'Supplier Name'
FROM products AS p, products_suppliers AS ps, suppliers AS s
WHERE p.productID = ps.productID
AND ps.suppID = s.suppID
AND s.name = 'Paper & Office Supplies Co.';



LOAD DATA LOCAL INFILE 'C:/Users/Hugo/Desktop/product_details.csv' 
INTO TABLE product_details COLUMNS TERMINATED BY ',';



-- Comando para mostrar codigo de criacao de tabela
show create table products;


CREATE table if not exists suppliers (
    suppID INT(4) unsigned not null AUTO_INCREMENT,
    name char(30) not null DEFAULT '',
    phone char(9) not null DEFAULT '',
    primary key (suppID)
    );

-- Backslash ANY - Numeracao automatica -> incremento
INSERT INTO suppliers VALUES 
(501, 'Paper & Office Supplies Co.', 123456789),
(\N, 'Books & School Ltd', 234567890),  
(\N, 'Electronics Corp.', 345678901);

ALTER TABLE products ADD COLUMN suppID INT UNSIGNED NOT NULL;

UPDATE products SET suppID = 502;

-- INSERT INTO products VALUES 
-- (\N, 'PEC', 'Pencil 3B','500','0.52',501),
-- (\N, 'PEC', 'Pencil 4B','200','0.62',501), 
-- (\N, 'PEC', 'Pencil 5B','100','0.73',501),
-- (\N, 'PEC', 'Pencil 6B','500','0.47',501);


-- UPDATE products SET suppID = 502 WHERE productID= '1022'; 

select products.name, price, suppliers.name from products 
join suppliers on products.suppID = suppliers.suppID where price > 0.4;


SELECT products.name, price, suppliers.name  
FROM products, suppliers  
WHERE products.suppID = suppliers.suppID AND price < 0.6;


SELECT products.name AS 'Product Name', price AS Price, 
suppliers.name AS 'Supplier Name' 
FROM products 
JOIN suppliers ON products.suppID = suppliers.suppID
WHERE price < 0.6;

-- All integer types can have an optional (nonstandard) attribute UNSIGNED. 
-- Unsigned type can be used to permit only nonnegative numbers in a column 
-- or when you need a larger upper numeric range for the column.
-- For example, if an INT column is UNSIGNED, the size of the column's range 
-- is the same but its endpoints shift from 
-- -2147483648 and 2147483647 up to 0 and 4294967295.

CREATE TABLE products_suppliers (
productID INT UNSIGNED  NOT NULL,
suppID  INT UNSIGNED  NOT NULL,
PRIMARY KEY (productID, suppID),
FOREIGN KEY (productID)  REFERENCES products  (productID),
FOREIGN KEY (suppID) REFERENCES suppliers (suppID)
);

INSERT INTO suppliers VALUES 
(501, 'Paper & Office Supplies Co.', 123456789),
(\N, 'Books & School Ltd', 234567890),  
(\N, 'Electronics Corp.', 345678901);

INSERT INTO products_suppliers VALUES
(2001, 501), 
(2002, 501),
(2003, 501), 
(2004, 502),
(2005, 502),
(2006, 502),
(2001, 503),
(2005, 503);


-- Alter Table --> mudar syntax da estrutura da tabela
-- Update Table --> muda os valores do campo/coluna
ALTER TABLE products DROP FOREIGN KEY products_ibfk_1;


Alter table products drop suppID;

DELETE FROM suppliers WHERE suppID = 501;


SELECT products.name AS 'Product Name', price, 
suppliers.name AS 'Supplier Name'
FROM products_suppliers 
JOIN products  ON products_suppliers.productID = products.productID
JOIN suppliers ON products_suppliers.suppID = suppliers.suppID
WHERE price < 0.6;


SELECT products.name AS 'Product Name', price, 
suppliers.name AS 'Supplier Name'
FROM products, suppliers, products_suppliers
WHERE products_suppliers.productID = products.productID
AND products_suppliers.suppID = suppliers.suppID
AND price < 0.6;


SELECT p.name AS 'Product Name', s.name AS 'Supplier Name'
FROM products AS p, products_suppliers AS ps, suppliers AS s
WHERE p.name = 'Pencil 3B'
AND ps.productID = p.productID 
AND ps.suppID = s.suppID;

SELECT p.name AS 'Product Name', s.name AS 'Supplier Name'
FROM products AS p, products_suppliers AS ps, suppliers AS s
WHERE p.productID = ps.productID
AND ps.suppID = s.suppID
AND s.name = 'Paper & Office Supplies Co.';



LOAD DATA LOCAL INFILE 'C:/Users/Hugo/Desktop/product_details.csv' 
INTO TABLE product_details COLUMNS TERMINATED BY ',';