MySQL If
IF function
The IF function is used to evaluate an expression and return a value.
The IF function consists of three parts.
1. condition to evaluate.
2. expression(can be another IF function) or value if the condition is evaluated to true.
3. expression(can be another IF function) or value if the condition is evaluated to false.
IF(condition, if_true_expr_or_value, if_false_expr_or_value)
Our example above shows ‘no sales rep’ if the condition(sales_rep_id IS NULL) is true and ‘sales rep’ if false.
IF function with aggregate functions
Here we are summing up the number of customers with and without reps.
MySQL Explain
MySQL Stored Procedure
Stored procedures are SQL statements that you can save into a database and reuse over and over again.
Syntax to create a stored procedure.
DELIMITER // CREATE PROCEDURE procedureName() BEGIN SELECT * FROM table_name; END // DELIMITER ;
To execute a store procedure, call its name.
CALL procedureName();
Advantages of Stored Procedures
- Fast as stored procedures can be cached and the big queries can be reduced to one line of call for a stored procedure. The gain is from network traffic.
- Portable as stored procedures can be migrated from SQL database to another.
Disadvantages of Stored Procedures
- Debugging. Developers might not be familiar with stored procedures or might not know what the store procedures are querying.
- Testing. Hard to test from an application developer point of view. The application developer needs to know stored procedures.
Stored Procedure Variables
Variables are local variables that hold values just for the execution of the stored procedure.
DECLARE variable_name datatype[INT,VARCHAR,DATETIME,...](size) DEFAULT default_value;
DECLARE total_count INT DEFAULT 0; SELECT COUNT(*) INTO total_count FROM table_name;
Variable scope
– variables that start with @ are session variables which go out of scope when the session end.
– variables go out of scope depending on where there are at.
Stored Procedure Parameters
Almost all stored procedures require parameters or input from the applications that call the stored procedures.
A stored procedure parameters have 3 modes – IN, OUT, INOUT
IN – parameters passed from the applications. IN parameters are protected which means that the stored procedure copies the values of the parameters and not modifies the real parameters.
DELIMITER // CREATE PROCEDURE prodecureName(IN parameterName VARCHAR(255)) BEGIN SELECT * FROM table_name WHERE column_name = parameterName; END // DELIMITER ;
CALL prodecureName('parameterName');
OUT – parameters passed as results to the applications.
DELIMITER $$ CREATE PROCEDURE procedureName( IN parameter1 VARCHAR(25), OUT parameterX INT) BEGIN SELECT count(*) INTO parameter1 FROM table_name WHERE parameter_name = parameter1; END$$ DELIMITER ;
CALL procedureName('parameter1',@total);
SELECT @total;
INOUT – parameters can be changed and passed back to the calling applications.
DELIMITER $$ CREATE PROCEDURE add_name(INOUT name VARCHAR(25),IN newName VARCHAR(25)) BEGIN SET name = CONCAT(name," ",newName); END$$ DELIMITER ;
SET @name = 'Folau'; - Folau CALL add_name(@name,'Lisa'); -- Folau Lisa CALL add_name(@name,'Kinga'); -- Folau Lisa Kinga CALL add_name(@name,'Fusi'); -- Folau Lisa Kinga Fusi SELECT @name; -- Folau Lisa Kinga Fusi
Return Multiple Values from Stored Procedures
DELIMITER $$
CREATE PROCEDURE getCounts(
IN userType INT,
OUT parameter1 INT,
OUT parameter2 INT)
BEGIN
-- new
SELECT
count(*) INTO parameter1
FROM
users
WHERE
user_type = userType
AND status = 'new';
-- deleted
SELECT
count(*) INTO parameter2
FROM
users
WHERE
user_type = userType
AND status = 'deleted';
END
CALL getCounts('soldier', @new, @deleted);
SELECT @new, @deleted;
Sometimes you want to run updates using a store precedure and then drop it right after
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sale_update`()
NO SQL
BEGIN
DECLARE newPrice INT DEFAULT FALSE;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SHOW ERRORS;
ROLLBACK;
END;
START TRANSACTION;
-- transaction start
INSERT INTO `sale`(`member_uuid`, `product_id`, `price`) VALUES ('mem_sadfljlweruoasd', 12, 250);
INSERT INTO `sale`(`member_uuid`, `product_id`, `price`) VALUES ('mem_sadfljlweruoasd', 23, 12);
-- transaction end
COMMIT WORK;
END$$
DELIMITER ;
-- RUN SP: sale_update
CALL sale_update();
-- DROP SP: sale_update
DROP PROCEDURE IF EXISTS sale_update;
MySQL Group By
The GROUP BY clause is used to group sets of rows into groups and returns only one row from each group. It is mostly used with aggregate functions but it does not have to.
SELECT
column_name1,column_name2,aggregate_function(column_name3),...
FROM
table_name
WHERE
conditions
GROUP BY column_name3;
sdfsd
GROUP BY with aggregate functions
AVG – average value of a column
SUM – sum value of a column
MAX- maximum value of a column
MIN – minimum value of a column
COUNT – count how many in the group
Aggregate functions perform some calculation on each group formed by GROUP BY and return a single value.
How to get number of vendors in each vendor group?
GROUP BY with expression
GROUP BY with HAVING
HAVING is used to filter the groups formed by GROUP BY. WHERE filters each row in the result set. HAVING filters each group in the groups formed by GROUP BY.
Applying HAVING with a criteria of average price of less than 1000.
If GROUP BY is omitted then HAVING is bahaving like the WHERE clause.
HAVING and GROUP BY can be used together to query statistical data about your data like finding the average order or the average number of dollars spent by a certain group of customers.
GROUP BY with ROLL UP
ROLL UP is used with GROUP BY to calculate subtotals and the grand total for groups form by GROUP BY.
Introduction
Snowflake’s Data Cloud is powered by an advanced data platform provided as Software-as-a-Service (SaaS). Snowflake enables data storage, processing, and analytic solutions that are faster, easier to use, and far more flexible than traditional offerings.
The Snowflake data platform is not built on any existing database technology or “big data” software platforms such as Hadoop. Instead, Snowflake combines a completely new SQL query engine with an innovative architecture natively designed for the cloud. To the user, Snowflake provides all of the functionality of an enterprise analytic database, along with many additional special features and unique capabilities.
Snowflake provides a single platform for data warehousing, data lakes, data engineering, data science, data application development, and secure sharing and consumption of real-time / shared data. Snowflake features out-of-the-box features like separation of storage and compute, on-the-fly scalable compute, data sharing, data cloning, and third-party tools support in order to handle the demanding needs of growing enterprises.
Benefits of Snowflake
- Instant, nearly unlimited scalability. Snowflake architecture uses a single elastic performance engine that delivers high speed and scalability. Snowflake supports as many concurrent users and workloads as you can throw at it, from interactive to batch. This powerful ability lies in its multi-cluster resource isolation. It’s high-performing and robust, giving enterprises the confidence they need that they’ll be able to handle every data workload. Snowflakes’ single engine powers everything from complex data pipelines, analytics, and feature engineering, to interactive applications across essential data workloads. With SQL query support and the Snowpark developer framework for Java and Scala access, Snowflake makes it easy for users with all skillset levels to leverage data.
- Automation made easy. Enterprises no longer have time for manual data management and maintenance; they must move fast and accurately. Automation makes this possible. Snowflake enables enterprises to automate data management, security, governance, availability, and data resiliency. This drives scalability, optimizes costs, reduces downtime, and helps improve operational efficiency. It’s built for high reliability and availability and it automates data replication for fast recovery.
- A single copy of data, shared securely, anywhere. Snowflake eliminates ETL and data silos, with seamless cross-cloud and cross-region connections and data sharing. Anyone who needs access to shared secure data can get a single copy via the data cloud, with the confidence that governance and compliance policies are in place. With a single shared data source, teams across the enterprise and the business’s ecosystem can be sure they are working from a single source of truth, making remote collaboration and decision-making fast and easy.
- Third-party data integrations. Additionally, the Snowflake Data Marketplace offers third-party data and lets you connect with Snowflake customers to extend workflows with data services and third-party applications.
- Performance and speed. The elastic nature of the cloud means if you want to load data faster, or run a high volume of queries, you can scale up your virtual warehouse to take advantage of extra compute resources. Afterward, you can scale down the virtual warehouse and pay for only the time you used.
- A data warehouse is a system designed to integrate big data sets from many sources, process it, and deliver analytical reports on demand. Business analysts and decision-makers can send queries and receive answers on the fly.
- Snowflake’s data warehouse is not built on an existing database or “big data” software platform such as Hadoop. Instead, it uses a new SQL database engine with a unique architecture designed for the cloud. Any software engineer with SQL experience can understand Snowflake and work with it.
- “Our goal was to deliver the analysis in less than 5 seconds. Thanks to Snowflake, we met it effortlessly. All the process – including sending the queries, downloading the data, and preparing visualizations – takes under 5 seconds.”
