RPA
글쓴이 보관물: BIG DATA_Eunice Park
1. Excel Functions_Basic
EXCEL FUNCTIONS_Basic
https://www.w3schools.com/excel/
AND
returns “TRUE” or “FALSE” based on two or more conditions.
=AND(B2=”Fire”;C2>70)
=IF(AND(B10=”Fire”;C10>70);”Yes”;”No”)
AVERAGE
calculates the average
=AVERAGE(B2:E2)
AVERAGEIF
calculates the average of a range based on a true or false condition.
=AVERAGEIF(range, criteria, [average_range])
![](https://www.w3schools.com/excel/img_excel_averageif3.png)
https://www.w3schools.com/excel/excel_averageif.php
AVERAGEIFS
the average of a range based on one or more true or false condition.
=AVERAGEIFS(average_range, criteria_range1, criteria1, …)
![](https://www.w3schools.com/excel/img_excel_averageifs4.png)
https://www.w3schools.com/excel/excel_averageifs.php
CONCAT
to link something together.
=CONCAT(A2,” “,A3)
![](https://www.w3schools.com/excel/img_excel_concat3.png)
https://www.w3schools.com/excel/excel_concat.php
=CONCAT(A2,”and”,A3)
![](https://www.w3schools.com/excel/img_excel_concat5.png)
https://www.w3schools.com/excel/excel_concat.php
COUNT
only counts cells with numbers, not cells with letters.
=COUNT(D2:D21)
![](https://www.w3schools.com/excel/count4.png)
https://www.w3schools.com/excel/excel_count.php
COUNTA
counts all cells in a range that has values, both numbers and letters.
=COUNTA(A2:A21)
![](https://www.w3schools.com/excel/counta5.png)
https://www.w3schools.com/excel/excel_counta.php
COUNTBLANK
counts blank cells in a range.
![](https://www.w3schools.com/excel/countblank3.png)
https://www.w3schools.com/excel/excel_countblank.php
COUNTIF
counts cells as specified.
COUNTIFS
counts cells in a range based on one or more true or false condition.
![](https://www.w3schools.com/excel/img_excel_countifs3.png)
https://www.w3schools.com/excel/excel_countifs.php
IF
returns values based on a true or false condition
![](https://www.w3schools.com/excel/img_excel_if_grass4.png)
https://www.w3schools.com/excel/excel_if.php
IFS
returns values based on one or more true or false conditions.
![](https://www.w3schools.com/excel/img_excel_ifs4.png)
https://www.w3schools.com/excel/excel_ifs.php
![](https://www.w3schools.com/excel/img_excel_ifs5.png)
https://www.w3schools.com/excel/excel_ifs.php
XOR
returns TRUE or FALSE based on two or more conditions.
![](https://www.w3schools.com/excel/img_excel_if_xor4.png)
https://www.w3schools.com/excel/excel_xor.php
![](https://www.w3schools.com/excel/img_excel_if_xor5.png)
https://www.w3schools.com/excel/excel_xor.php
RIGHT
use the RIGHT function with a defined length of characters
=RIGHT(A2,3)
![](https://www.w3schools.com/excel/img_excel_right5.png)
https://www.w3schools.com/excel/excel_right.php
STDEV.P 표준편차
calculates the Standard Deviation (Std) for the entire population.
=STDEV.P(E2:E755)
![](https://www.w3schools.com/excel/stdevp4.png)
https://www.w3schools.com/excel/excel_stdevp.php
STDEV.S
calculates the Standard Deviation (Std) for a sample.
=STDEV.P(E2:E21) range of the sample
![](https://www.w3schools.com/excel/stddevs4.png)
https://www.w3schools.com/excel/excel_stdevs.php
SUMIF
the sum of values in a range based on a true or false condition.
![](https://www.w3schools.com/excel/img_excel_sumif4.png)
https://www.w3schools.com/excel/excel_sumif.php
SUMIFS
calculates the sum of a range based on one or more true or false condition.
![](https://www.w3schools.com/excel/img_excel_sumifs4.png)
https://www.w3schools.com/excel/excel_sumifs.php
TRIM
remove irregular text spacing and keep single spaces between words.
=TRIM(A2:C21)
![](https://www.w3schools.com/excel/img_excel_trim3.png)
https://www.w3schools.com/excel/excel_trim.php
VLOOKUP
allows searches across columns.
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
=VLOOKUP(H3;A2:E21;2;1)
for selecting col_index_number 2
.
next – 1
(True) is entered as range_lookup. This is because the most left column has numbers only. If it was text, 0
(False) would have been used.
![](https://www.w3schools.com/excel/vlookup9.png)
https://www.w3schools.com/excel/excel_vlookup.php
2. DATABASE _ CREATE
DATABASE _ CREATE
https://www.w3schools.com/mysql/default.asp
Create a new database
Write the correct SQL statement to create a new database called testDB.
CREATE DATABASE testDB;
Delete a database
Write the correct SQL statement to delete a database named testDB.
DROP DATABASE testDB;
Create a new table
Write the correct SQL statement to create a new table called Persons.
CREATE TABLE Persons
(PersonID int,LastName varchar(255),FirstName varchar(255),Address varchar(255),City varchar(255) );
Delete a table
Write the correct SQL statement to delete a table called Persons.
DROP TABLE Persons;
Truncate
Use the TRUNCATE statement to delete all data inside a table.
TRUNCATE TABLE Persons;
Alter add column
Add a column of type DATE called Birthday.
ALTER TABLE Persons ADD Birthday DATE;
Alter delete column
Delete the column Birthday from the Persons table.
ALTER TABLE Persons DROP COLUMN Birthday;
DATABASE_Constraints
NOT NULL
– Ensures that a column cannot have a NULL valueUNIQUE
– Ensures that all values in a column are differentPRIMARY KEY
– A combination of aNOT NULL
andUNIQUE
. Uniquely identifies each row in a tableFOREIGN KEY
– Prevents actions that would destroy links between tablesCHECK
– Ensures that the values in a column satisfies a specific conditionDEFAULT
– Sets a default value for a column if no value is specifiedCREATE INDEX
– Used to create and retrieve data from the database very quickly
NOT NULL on CREATE TABLE
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255) NOT NULL,
Age int
);
UNIQUE
The UNIQUE
constraint ensures that all values in a column are different.
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
UNIQUE (ID)
);
PRIMARY KEY
The PRIMARY KEY
constraint uniquely identifies each record in a table.
Primary keys must contain UNIQUE values, and cannot contain NULL values.
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
PRIMARY KEY (ID)
);
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CONSTRAINT PK_Person PRIMARY KEY (ID,LastName)
);
FOREIGN KEY
The FOREIGN KEY
constraint is used to prevent actions that would destroy links between tables.
A FOREIGN KEY
is a field (or collection of fields) in one table, that refers to the PRIMARY KEY
in another table.
CREATE TABLE Orders (
OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
CREATE TABLE Orders (
OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID)
REFERENCES Persons(PersonID)
);
CHECK
The CHECK
constraint is used to limit the value range that can be placed in a column.
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CHECK (Age>=18)
);
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
City varchar(255),
CONSTRAINT CHK_Person CHECK (Age>=18 AND City=‘Sandnes’)
);
DEFAULT
The DEFAULT
constraint is used to set a default value for a column.
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
City varchar(255) DEFAULT ‘Sandnes’
);
CREATE TABLE Orders (
ID int NOT NULL,
OrderNumber int NOT NULL,
OrderDate date DEFAULT CURRENT_DATE()
);
AUTO INCREMENT
CREATE TABLE Persons (
Personid int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
PRIMARY KEY (Personid)
);
ALTER TABLE Persons AUTO_INCREMENT=100;
CREATE INDEX
Indexes are used to retrieve data from the database more quickly than otherwise. The users cannot see the indexes, they are just used to speed up searches/queries.
CREATE INDEX idx_pname ON Persons (LastName, FirstName);
CREATE VIEW
a view is a virtual table based on the result-set of an SQL statement.
CREATE VIEW [Brazil Customers] AS
SELECT CustomerName, ContactName
FROM Customers
WHERE Country = ‘Brazil’;
WORKING WITH DATES
DATE
– format YYYY-MM-DDDATETIME
– format: YYYY-MM-DD HH:MI:SSTIMESTAMP
– format: YYYY-MM-DD HH:MI:SSYEAR
– format YYYY or YY
1. SQL GRAMMER _ BASIC
SQL Grammer _ BASIC
https://www.w3schools.com/mysql/default.asp
SELECT
– extracts data from a databaseUPDATE
– updates data in a databaseDELETE
– deletes data from a databaseINSERT INTO
– inserts new data into a databaseCREATE DATABASE
– creates a new databaseALTER DATABASE
– modifies a databaseCREATE TABLE
– creates a new tableALTER TABLE
– modifies a tableDROP TABLE
– deletes a tableCREATE INDEX
– creates an index (search key)DROP INDEX
– deletes an index
Distinct
Select all the different values from the Country column in the Customers table.
select distinct country from customers
Not
Use the NOT
keyword to select all records where City
is NOT “Berlin”.
select * from customers where not city=’berlin’;
Or
Select all records where the City column has the value ‘Berlin’ or ‘London’.
select * FROM Customers where City = ‘Berlin’ or city = ‘london’;
Order by
Select all records from the Customers table, sort the result alphabetically, first by the column Country, then, by the column City.
SELECT * FROM Customers ORDER BY Country, City;
Insert into values
Insert a new record in the Customers table.
INSERT INTO Customers (CustomerName,Address,City,PostalCode,Country)
VALUES(‘Hekkan Burger’,’Gateveien 15′,’Sandnes’,’4306′,’Norway’);
Is null / Is not null
Select all records from the Customers where the PostalCode column is empty.
SELECT * FROM Customers WHERE PostalCode IS NULL;
Update set
Update the City column of all records in the Customers table.
UPDATE Customers SET City = ‘Oslo’;
Update the City value and the Country value.
update Customers set City = ‘Oslo’, country= ‘Norway’ WHERE CustomerID = 32;
Delete from
Delete all the records from the Customers table.
DELETE FROM Customers;
Min / Max / Avg / Sum
Use the MIN function to select the record with the smallest value of the Price column.
SELECT MIN(Price) FROM Products;
Count
Use the correct function to return the number of records that have the Price value set to 18.
SELECT COUNT(*) FROM Products WHERE Price = 18;
Like / not like
Select all records where the value of the City column starts with the letter “a”.
SELECT * FROM Customers WHERE City LIKE ‘a%’;
‘%a’
‘%a%’
‘a%b’
‘_a%’ > the second letter of the City is an “a”.
In / not in
Use the IN operator to select all the records where Country is either “Norway” or “France”
SELECT * FROM Customers WHERE Country IN (‘Norway’,’France’);
Between / not between
Use the BETWEEN operator to select all the records where the value of the Price column is between 10 and 20.
SELECT * FROM Products WHERE Price BETWEEN 10 AND 20;
BETWEEN ‘Geitost’ AND ‘Pavlova‘; > alphabetically
Left Join
SELECT * FROM Orders
LEFT JOIN Customers ON Orders.CustomerID = Customers.CustomerID;
Inner join
Choose the correct JOIN clause to select all records from the two tables where there is a match in both tables.
SELECT * FROM Orders INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;
Right join
Choose the correct JOIN clause to select all the records from the Customers table plus all the matches in the Orders table.
SELECT * FROM Orders RIGHT JOIN Customers ON Orders.CustomerID=Customers.CustomerID;
Order by Count
List the number of customers in each country, ordered by the country with the most customers first.
SELECT COUNT(CustomerID),Country FROM Customers GROUP BY Country
ORDER BY COUNT(CustomerID) DESC;
LIMIT
SELECT * FROM Customers LIMIT 3;
JOIN
INNER JOIN: Returns records that have matching values in both tables
LEFT JOIN: Returns all records from the left table, and the matched records from the right table
RIGHT JOIN: Returns all records from the right table, and the matched records from the left table
CROSS JOIN: Returns all records from both tables
SELF JOIN
T1 and T2 are different table aliases for the same table.
SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2, A.City
FROM Customers A, Customers B
WHERE A.CustomerID <> B.CustomerID AND A.City = B.City ORDER BY A.City;
Join : columns
union : rows
UNION
The UNION
operator selects only distinct values by default. To allow duplicate values, use UNION ALL
:
SELECT City FROM Customers UNION SELECT City FROM Suppliers ORDER BY City;
SELECT City FROM Customers UNION ALL SELECT City FROM Suppliers ORDER BY City;
HAVING
SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country
HAVING COUNT(CustomerID) > 5 ORDER BY COUNT(CustomerID) DESC;
EXISTS
The EXISTS
operator returns TRUE if the subquery returns one or more records.
SELECT SupplierName FROM Suppliers WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.SupplierID = Suppliers.supplierID AND Price < 20);
ANY (or) / ALL (and)
ANY
means that the condition will be true if the operation is true for any of the values in the range.
ALL
means that the condition will be true only if the operation is true for all values in the range.
SELECT ProductName FROM Products
WHERE ProductID = ANY (SELECT ProductID FROM OrderDetails WHERE Quantity = 10);
SELECT ProductName FROM Products
WHERE ProductID = ALL(SELECT ProductID FROM OrderDetails WHERE Quantity = 10);
CASE
The CASE
statement goes through conditions
SELECT OrderID, Quantity,
CASE
WHEN Quantity > 30 THEN ‘The quantity is greater than 30’
WHEN Quantity = 30 THEN ‘The quantity is 30’
ELSE ‘The quantity is under 30’
END AS QuantityText
FROM OrderDetails;
IFNULL() / COALESCE()
The MySQL IFNULL()
function lets you return an alternative value if an expression is NULL.
The example below returns 0 if the value is NULL:
SELECT ProductName, UnitPrice * (UnitsInStock + IFNULL(UnitsOnOrder, 0)) FROM Products;
SELECT ProductName, UnitPrice * (UnitsInStock + COALESCE(UnitsOnOrder, 0)) FROM Products;
COMMENTS
SELECT * FROM Customers — WHERE City=’Berlin’;
/*Select all the columns
of all the records
in the Customers table:*/
SELECT * FROM Customers;
SQL Tutorial II (INTERMEDIATE)