[DB3 - Database creation and multiple queries] Database Example #university

db3.sql

--- run mysql XAAMP >> mysql -u root mysql
------------------------------------------------------------------------
--                               DB 3
------------------------------------------------------------------------

create database db3 charset latin1 collate latin1_swedish_ci;

CREATE TABLE IF NOT EXISTS products (
productID 		INT UNSIGNED 		NOT NULL AUTO_INCREMENT,
productCode 	CHAR(3) 			NOT NULL DEFAULT '',
name 			VARCHAR(30) 		NOT NULL DEFAULT '',
quantity 		INT UNSIGNED 		NOT NULL DEFAULT 0,
price 			DECIMAL(7,2) 		NOT NULL DEFAULT 99.99,
PRIMARY KEY 	(productID)
);

INSERT INTO products VALUES ( 1001, 'PEN', 'Pen Red', 5000, 1.23);

INSERT INTO products VALUES
(NULL, 'PEN', 'Pen Blue', 8000, 1.25),
(NULL, 'PEN', 'Pen Black', 2000, 1.25);

INSERT INTO products (productCode, name, quantity, price)
VALUES
( 'PEC' , 'Pencil 2B' , 10000, 0.48),
( 'PEC' , 'Pencil 2H' , 8000, 0.49);

INSERT INTO products (productCode, name) VALUES ('PEC', 'Pencil HB');

-- Will generate errors
--INSERT INTO products values (NULL, NULL, NULL, NULL, NULL);

DELETE FROM products WHERE productID = 1006;

--Miscellaneous:  -- maria db only
--SELECT User, Host FROM mysql.user; 
--SELECT User, Host, Password, password_expired FROM mysql.user; 
--SELECT NOW(); 
--SELECT NOW() time; 
--SELECT NOW() time, sqrt(2) square_root; 

------------------------------------------------------------------------
-- Example Queries
------------------------------------------------------------------------
-- Query 1
select * from products;
-- Query 2
SELECT name, price FROM products WHERE price < 1.0; 
-- Query 3
SELECT name, quantity FROM products WHERE quantity <= 2000; 
-- Query 4
SELECT name, price FROM products WHERE productCode = 'PEN'; 
-- Query 5
SELECT name, price FROM products WHERE productCode > 'PEN'; 
-- Query 6
SELECT name, price FROM products WHERE productCode < 'PEN'; 
-- Query 7 - "name" begins with 'PENCIL' 
SELECT name, price FROM products WHERE name LIKE 'PENCIL%'; 
-- Query 8 - name" begins with 'P', followed by any two characters, followed by space, followed by zero or more characters: 
SELECT name, price FROM products WHERE name LIKE 'P__ %'; 

-- MariaDB/MySQL also support 'regular expressions' ('regexp') matching via the REGEXP operator: 
SELECT 'Pencil' REGEXP 'Pencil'; 
SELECT 'Pencil' REGEXP 'pencil'; 
SELECT BINARY 'Pencil' REGEXP 'pencil'; 
SELECT 'Pencil' REGEXP 'Penci'; 
SELECT 'Penci' REGEXP 'Pencil'; 
-- Note that the word being matched must match the whole pattern. Check it: 
SELECT 'Pencil' REGEXP 'Book|Pencil'; 
SELECT 'Pencil' REGEXP 'Book|Pen'; 
SELECT 'Pen' REGEXP 'Book|Pencil'; 

-- Special Characters:
-- The above examples introduce the syntax, but are not very useful.
-- It's the special characters that give regular expressions their power.
-- ^ matches the beginning of a string (inside square brackets it can also mean NOT. 
SELECT 'Pencil' REGEXP '^Pe'; 
-- ^ matches the beginning of a string (inside square brackets it can also mean NOT. 
SELECT 'Pencil' REGEXP 'il$'; 
-- A dot '.' matches any single character: 
SELECT 'Pencil' REGEXP 'Pen.il'; 
SELECT 'Pencil' REGEXP 'Pe .. il'; 
-- x* matches zero or more of a character 'x'. In the examples below, it's the 'n' 
SELECT 'Pencil' REGEXP 'Pe*cil'; 
SELECT 'Pencil' REGEXP 'Pen*cil'; 
SELECT 'Pennnncil' REGEXP 'Pen*cil'; 
-- x? matches zero or one of a character 'x'. In the examples below, it's the 'n' character.
SELECT 'Pencil' REGEXP 'Pen?cil'; 

SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen%';
SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen%'; 
SELECT * FROM products WHERE quantity >= 5000 AND NOT price < 1.24;
SELECT * FROM products WHERE (quantity >= 5000 AND name LIKE 'Pen%');
SELECT * FROM products WHERE (quantity >= 5000 AND name LIKE 'Pen %');
SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %');
SELECT * FROM products WHERE NOT((quantity >= 5000) AND (name LIKE 'Pencil %'));
SELECT * FROM products WHERE quantity>= 5000 XOR name LIKE 'Pen %';

-- IN, NOT IN
-- You can select from members of a set with IN (or NOT IN) operator.
-- This is easier and clearer than the equivalent AND-OR expression. 
SELECT * FROM products WHERE name IN ('Pen Red', 'Pen Black');
SELECT * FROM products WHERE name Not IN ('Pen Red', 'Pen Black');


-- BETWEEN, NOT BETWEEN
-- To check if the value is within a range, use the BETWEEN ... AND ... operator.
-- Note: This is easier and clearer than the equivalent AND-OR expression. 
SELECT * FROM products WHERE (price BETWEEN 1.0 AND 2.0);
SELECT * FROM products WHERE (price NOT BETWEEN 1.0 AND 2.0);

SELECT * FROM products WHERE NOT (price BETWEEN 1.0 AND 2.0);
SELECT * FROM products WHERE (price NOT BETWEEN 1.0 AND 2.0);

SELECT* FROM products WHERE (price BETWEEN 1.0 AND 2.0) AND (quantity BETWEEN 1000 AND 2000);
SELECT* FROM products WHERE (price NOT BETWEEN 1.0 AND 2.0) AND (quantity BETWEEN 1000 AND 2000);
SELECT* FROM products WHERE (price NOT BETWEEN 1.0 AND 2.0) AND (quantity NOT BETWEEN 1000 AND 2000);
SELECT* FROM products WHERE NOT (price BETWEEN 1.0 AND 2.0) AND NOT (quantity BETWEEN 1000 AND 2000);

-- IS NULL, IS NOT NULL 
SELECT* FROM products WHERE productCode IS NULL; 
SELECT* FROM products WHERE productCode IS NOT NULL; 

-- ORDER BY Clause 
-- Order the results by price in descending order: 
SELECT* FROM products WHERE name LIKE 'Pen %' ORDER BY price DESC;
-- Order by price in descending order, followed by quantity in ascending (default) order:
SELECT* FROM products WHERE name LIKE 'Pen %' ORDER BY price DESC, quantity; 

SELECT productID, productCode, quantity FROM products ORDER BY productCode asc, quantity asc ; -- TPC
SELECT productID, name, quantity, price, (price * quantity) FROM products ORDER BY total asc; -- TPC

-- LIMIT Clause 

Display the first two rows of a select statement: 
SELECT * FROM products ORDER BY price LIMIT 2;

SELECT * FROM products ORDER BY price LIMIT 2, 2 ;
SELECT * FROM products ORDER BY quantity LIMIT 5;

SELECT * FROM products ORDER BY quantity LIMIT 3, 1 ;


-- Alias
-- The keyword 'AS' to define an alias, which is an identifier (such as column name, table name) .
-- The alias will be used in displaying the name. It can also be used as reference.
SELECT productID AS ID, productCode AS Code, name AS Description, price AS 'Unit Price' FROM products ORDER BY ID;
SELECT productID, name, quantity, price, (price * quantity) as total FROM products ORDER BY total asc; -- TPC


-- CONCAT()
-- To concatenate a few columns as one (e.g., joining the last name and first name) by using function CONCAT().
-- Note: It is possible to use an ordinal to refers the attribute or column. That means, the ordinal refers to the position of the attribute after the SELECT.
SELECT CONCAT(productCode,' - ',name) AS 'Product Description', price as Price FROM products;
SELECT CONCAT(productCode,' - ',name) AS 'Product Description', price as Price FROM products order by price;
SELECT CONCAT(productCode,' - ',name) AS 'Product Description', price as Price FROM products ORDER BY 'Product Description';
SELECT CONCAT(productCode,' - ',name) AS 'Product Description', price as Price FROM products order by 2;
SELECT CONCAT(productCode,' - ',name) AS 'Product Descriptian', price as Price FROM products order by 2 desc;
SELECT CONCAT(productCode,' - ',name) AS 'Product Descriptian', price as Price FROM products order by 1;
SELECT CONCAT(name, '	', (( price * 0.90 ) * 10 ), '$') AS 'Product 10-pack 10% discount' FROM products;


-- DISTINCT
-- A column may have duplicate values, so use keyword DISTINCT to select only distinct values.
-- Apply also apply DISTINCT to several columns to select distinct combinations of these columns. For example:
SELECT price FROM products;
SELECT DISTINCT price FROM products;
SELECT DISTINCT price AS 'Distinct Prices' FROM products;
SELECT DISTINCT price, name FROM products;  -- this will show all because price and name are not the same on both cols

-- GROUP BY Clause
-- The GROUP BY clause allows you to collapse multiple records with a common value into groups.
SELECT * FROM products ORDER BY productCode, productID;
SELECT * FROM products GROUP BY productCode; -- Only first record's name in each group is shown.
SELECT * FROM products GROUP BY productCode ORDER BY 1 ; -- '1' is an ordinal and refers to the first column.
Select productCode Code, count(*) as "Lines" from products group by productCode;
Select productCode Code, count(*) as "Qty" from products group by productCode;


-- GROUP BY Aggregate Functions: COUNT, MAX, MIN, AVG, SUM, STD, GROUP_CONCAT:
-- GROUP BY used together with GROUP BY aggregate functions (such as COUNT(), AVG(), SUM()) to produce group summary.
-- The function COUNT(*) returns the rows selected; and
-- COUNT(columnName) counts only the non-NULL values of the given column.
SELECT COUNT(*) AS 'Nr. of codes' FROM products;

INSERT INTO products (productCode, name, quantity, price) VALUES
('NTB', 'Notebook A4L90p', 9000, 3.30),
('NTB', 'Notebook B5L 60p', 8000, 2.80),
('NTB', 'Notebook A5L 50p', 70000, 2.20),
('NTB', 'Notebook B6L 40p', 70000, 2.00);

-- Check number of products by productCode:
SELECT productCode AS Code, COUNT(*) AS 'Items by Code' FROM products WHERE productCode='NTB';
-- Setting and using runtime variables:
SET @productCode='NTB';
SELECT productCode AS Code, COUNT(*) AS 'Items by Code' FROM products WHERE productCode=@productCode;

INSERT INTO products (productCode, name, quantity, price) VALUES
('RUB', 'Rubber White Soft', 1200, 1.99),
('RUB', 'Rubber colar Medium', 1500, 0.99);
select * from products; -- see all products that were added

SELECT productCode AS Item, COUNT(*) AS 'Items by Code' FROM products GROUP BY productCode;
SELECT productCode AS Item, COUNT(*) AS 'Items by Code' FROM products GROUP BY productCode ORDER BY 2;
SELECT productCode AS Item, COUNT(productCode) AS 'Items by Code' FROM products GROUP BY productCode;

-- GROUP BY aggregate functions such as AVG(), MAX(), MIN() and SUM().
SELECT MAX(price), MIN(price), AVG(price), STD(price), SUM(quantity) FROM products; -- (Without GROUP BY - All rows)
SELECT productCode AS Code, MAX(price) AS 'Highest Price', MIN(price) AS 'Lowest Price' FROM products GROUP BY productCode;

Select productCode as Code, Sum(quantity) as "Qty" FROM products Group by productCode order by 2;

-- CAST( ... AS ... ) function
-- The CAST function is used for converting a value from one datatype to another specific datatype, or to format floating-point numbers.
-- Set the variables:
set @a=5, @b=3;
-- Then, work it out:
select @a+@b;

select @a+@b "a+b";
select @a a, @b b, @a+@b "a+b";
select @a a, @b b, @a*@b "a*b";
select @a a, @b b, sqrt(@a)*sqrt(@b) "sqrt(a)*sqrt(b)";
SELECT @a a, @b b, CAST(sqrt(@a)*sqrt(@b) AS DECIMAL(7,2)) "sqrt(a)*sqrt(b)";
SELECT productCode, MAX(price), MIN(price), AVG(price), STD(price), SUM(quantity) FROM products GROUP BY 1 ORDER BY 6 DESC;

-- HAVING clause
-- HAVING is similar to WHERE, but it can operate on the GROUP BY aggregate functions; whereas WHERE operates only on columns.
SELECT productCode AS 'Product Code', COUNT(*) AS 'Count', CAST(AVG(price) AS DECIMAL(7,2)) AS 'Average' FROM products GROUP BY productCode;

SELECT productCode AS 'Product Code', COUNT(*) AS 'Count', CAST(AVG(price) AS DECIMAL(7,2)) AS 'Average' FROM products GROUP BY productCode HAVING Count >=3; 

SELECT productCode, MAX(price), MIN(price), CAST(AVG(price) as DECIMAL(7,2)) as 'Average', 
CAST(STD(price) as decimal(7,2)) as 'Stf. Dev.', 
SUM(quantity) 
FROM products GROUP BY 1 ORDER BY 6 DESC;


SELECT productCode AS 'Product Code', 
COUNT(*) AS 'Count', CAST(AVG(price) AS DECIMAL(7,2)) AS 'Average' 
FROM products 
GROUP BY productCode 
HAVING Count >=2;
--+--------------+-------+---------+
--| Product Code | Count | Average |
--+--------------+-------+---------+
--| NTB          |     4 |    2.83 |
--| PAP          |     2 |    5.45 |
--| PEC          |     2 |    0.54 |
--| PEN          |     3 |    1.35 |
--| RUB          |     2 |    1.64 |
--+--------------+-------+---------+

SELECT productCode AS 'Product Code', 
COUNT(*) AS 'Count', 
CAST(AVG(price) AS DECIMAL(7,2)) AS 'Average' 
FROM products 
GROUP BY productCode 
HAVING Count >=4; 
--+--------------+-------+---------+
--| Product Code | Count | Average |
--+--------------+-------+---------+
--| NTB          |     4 |    2.83 |
--+--------------+-------+---------+

SELECT productCode AS 'Product Code', 
COUNT(*) AS 'Count', 
CAST(AVG(price) AS DECIMAL(7,2)) AS 'Average' 
FROM products 
GROUP BY productCode 
HAVING Average >=4; 
--+--------------+-------+---------+
--| Product Code | Count | Average |
--+--------------+-------+---------+
--| DIG          |     1 |  449.99 |
--| PAP          |     2 |    5.45 |
--+--------------+-------+---------+

-- WITH ROLLUP
-- The WITH ROLLUP clause shows the summary of group values. For example:
SELECT productCode, MAX(price), MIN(price),
CAST(AVG(price) AS DECIMAL(7,2)) AS 'Average',
SUM(quantity)
FROM products
GROUP BY 1
WITH ROLLUP; 
--+-------------+------------+------------+---------+---------------+
--| productCode | MAX(price) | MIN(price) | Average | SUM(quantity) |
--+-------------+------------+------------+---------+---------------+
--| DIG         |     449.99 |     449.99 |  449.99 |             4 |
--| NTB         |       3.63 |       2.20 |    2.83 |        154000 |
--| PAP         |       5.50 |       5.39 |    5.45 |          2100 |
--| PEC         |       0.54 |       0.53 |    0.54 |         18000 |
--| PEN         |       1.38 |       1.30 |    1.35 |         14950 |
--| RUB         |       2.19 |       1.09 |    1.64 |          2700 |
--| NULL        |     449.99 |       0.53 |   34.33 |        191754 |
--+-------------+------------+------------+---------+---------------+

SELECT productCode, MAX(price), MIN(price),
SUM(quantity) AS 'STOCK'
FROM products
GROUP BY 1
WITH ROLLUP; 
--+-------------+------------+------------+--------+
--| productCode | MAX(price) | MIN(price) | STOCK  |
--+-------------+------------+------------+--------+
--| DIG         |     449.99 |     449.99 |      4 |
--| NTB         |       3.63 |       2.20 | 154000 |
--| PAP         |       5.50 |       5.39 |   2100 |
--| PEC         |       0.54 |       0.53 |  18000 |
--| PEN         |       1.38 |       1.30 |  14950 |
--| RUB         |       2.19 |       1.09 |   2700 |
--| NULL        |     449.99 |       0.53 | 191754 |
--+-------------+------------+------------+--------+

-- The aggregate functions were applied to all groups.
-- The line starting with 'NULL' is inappropriate?
-- Then, use COALESCE.
-- COALESCE ( .... , ... )
-- The SQL Coalesce (and ISNULL) function(s) is (are) used to handle NULL values.
-- During the expression evaluation process the NULL values are replaced with the user-defined value.
-- COALESCE evaluates the arguments in order and always returns first non-null value from the defined argument list.
-- Note: In SQL NULL is a STATE, not a value.
-- Example:
SELECT COALESCE(NULL, NULL, 'Frist non-null argument', NULL, 'Second non-null
argument'); 
--+-----------------------------------------------------------------------------------+
--| COALESCE(NULL, NULL, 'Frist non-null argument', NULL, 'Second non-null argument') |
--+-----------------------------------------------------------------------------------+
--| Frist non-null argument                                                           |
--+-----------------------------------------------------------------------------------+

SELECT COALESCE(productCode, 'Total') Code, MAX(price), MIN(price),
CAST(AVG(price) AS DECIMAL(7,2)) AS 'Average',
SUM(quantity)
FROM products
GROUP BY productCode
WITH ROLLUP;
--+-------+------------+------------+---------+---------------+
--| Code  | MAX(price) | MIN(price) | Average | SUM(quantity) |
--+-------+------------+------------+---------+---------------+
--| DIG   |     449.99 |     449.99 |  449.99 |             4 |
--| NTB   |       3.63 |       2.20 |    2.83 |        154000 |
--| PAP   |       5.50 |       5.39 |    5.45 |          2100 |
--| PEC   |       0.54 |       0.53 |    0.54 |         18000 |
--| PEN   |       1.38 |       1.30 |    1.35 |         14950 |
--| RUB   |       2.19 |       1.09 |    1.64 |          2700 |
--| Total |     449.99 |       0.53 |   34.33 |        191754 |
--+-------+------------+------------+---------+---------------+

SELECT COALESCE(productID, 'Total') Code,
Avg(quantity) as 'Quantidade', AVG(price) as "Price"
FROM products
GROUP BY productID
WITH ROLLUP;
--+-------+--------+
--| Code  | Stock  |
--+-------+--------+
--| DIG   |      4 |
--| NTB   | 154000 |
--| PAP   |   2100 |
--| PEC   |  18000 |
--| PEN   |  14950 |
--| RUB   |   2700 |
--| Total | 191754 |
--+-------+--------+

-- UPDATE - Modifying Data
-- To modify existing data, use UPDATE ... SET command, with the following syntax:
-- UPDATE tableName SET columnName = {valuelNULLIDEFAULT}, ... WHERE criteria
-- For example, increase the prices by 10% for all products
UPDATE products SET price= price* 1.10;
-- Check the changes:
select * from products; 

Update products set quantity = quantity - 100 where name = "Pen Red";
-- |      1001 | PEN         | Pen Red               |     4850 |   1.43 |


UPDATE products SET quantity = quantity + 50, price= 1.23 WHERE name = 'Pen Red';
--+-----------+-------------+---------+----------+-------+
--| productID | productCode | name    | quantity | price |
--+-----------+-------------+---------+----------+-------+
--|      1001 | PEN         | Pen Red |     4900 |  1.23 |
--+-----------+-------------+---------+----------+-------+
select * from products where name = 'Pen Red'; 

valor atual do stock
Insert into products ("Dig", 'iPad mini-4-Wi-Fi-128', 4, 449.00);
-- TPC -- PAG 22
-- Insert a new record into products:
-- 1. The productID should be the set by the system.
-- 2. The productCode is 'DIG' for 'digital'.
-- 3. The name is 'iPad mini-4-Wi-Fi-128'.
-- 4. Quantity in stock: 4.
-- 5. Unit price: 449.00 
-- Note: Don't forget, that the first attribute productID is been insert by the system. It's necessary to declare the attribute name for the values to be inserted (2-5). 
update products set quantity = quantity - 100 where productID <;

select productId, productCode, name, quantity, price, (price * quantity) as "Balance" from products 

-- The 'mysqldump' console utility is used to export databases to SQL text files.
-- For Linux (use the 'root' account):
-- $ sudo su
-- [sudo] password for ck:
-- # mysqldump -u root -p db3 > db3-dump.sql
-- Enter password:
-- # ls db3-dump.sql 

SELECT* FROM products INTO OUTFILE 'C:/Users/danie/Documents/Universidade/university/Databases/db3-dmup.csv'
COLUMNS TERMINATED BY ','
LINES TERMINATED BY '\r\n'; 

SELECT* FROM products INTO OUTFILE 'd:/myProject/db3-dmup.csv'
COLUMNS TERMINATED BY ','
LINES TERMINATED BY '\r\n';