DataCamp课程：Functions for Manipulating Data in SQL Server

1. Choosing the appropriate data type

</> Working with different types of data

Select information from the ratings table for the Belgian companies that received a rating higher than 3.5.

SELECT 
	company, 
	company_location, 
	bean_origin, 
	cocoa_percent, 
	rating
FROM ratings
WHERE company_location = 'Belgium'
	AND rating > 3.5;

company	company_location	bean_origin		cocoa_percent	rating
Callebaut					Belgium	Baking	0.7000			3.7500
Neuhaus (Callebaut)			Belgium	Dark	0.7300			4.0000
Pierre Marcolini			Belgium	Cabosse	0.7000			4.0000
...

Query the voters table where birthdate is greater than ‘1990-01-01’ and the total_votes is between 100 and 200.

SELECT 
	first_name,
	last_name,
	birthdate,
	gender,
	email,
	country,
	total_votes
FROM voters
WHERE birthdate > '1990-01-01'
  AND total_votes > 100
  AND total_votes < 200;

first_name	last_name	birthdate	gender	email				country		total_votes
Julie		Becker		1996-04-21	F 		julie22@yahoo.com	Belgium		189
Jessica		Gonzales	1993-04-11	F 		jessica41@yahoo.com	Belgium		171
Willie		She			1991-05-05	M 		willie20@yahoo.com	Switzerland	125
...

</> Storing dates in a database

Add a new column with the correct data type, for storing the last date a person voted (“2018-01-17”).

ALTER TABLE voters
ADD last_vote_date date;

Add a new column called last_vote_time, to keep track of the most recent time when a person voted (“16:55:00”).

ALTER TABLE voters
ADD last_vote_time time;

Add a new column,last_login, storing the most recent time a person accessed the application (“2019-02-02 13:44:00”).

ALTER TABLE voters
ADD last_login datetime2;

</> Types of character strings

To what data category does the nvarchar type belong?

• Exact numerics
• Date and time
• Unicode character strings
• Other data types

</> Implicit conversion between data types

Restrict the query to show only the rows where total_votes is higher than the character string ‘120’.

SELECT 
	first_name,
	last_name,     
	total_votes
FROM voters
WHERE total_votes > '120';

first_name	last_name	total_votes
Carol		Rai			136
Ana			Price		124
Melissa		Richardson	145
...

What kind of implicit conversion is performed by SQL Server to make this query execute successfully?

• The values from the total_votes column are converted to a character type.
• The values from the total_votes column and the character ‘120’ are converted to an intermediary data type, because comparing data works only when the data types are identical.
• The character ‘120’ is converted to a numeric type.

</> Data type precedence

Select information about all the ratings that were higher than 3.

SELECT 
	bean_type,
	rating
FROM ratings
WHERE rating > 3;

bean_type	rating
Unknown		3.2500
Trinitario	3.2500
Unknown		3.2500
...

Taking into account that the rating column is a decimal, which statement is true about the execution of this query?

• The values from the rating column are converted to int, because int has higher precedence than decimal.
• The integer value is converted to decimal because decimal has higher precedence than int.
• No conversion is performed because decimal and integer numbers can be compared as they are.

</> CASTing data

Write a query that will show a message like the following, for each voter: Carol Rai was born in 1989.

SELECT 
	first_name + ' ' + last_name + ' was born in ' + CAST(YEAR(birthdate) AS nvarchar) + '.' 
FROM voters;

Carol Rai was born in 1989.
Ana Price was born in 1987.
Melissa Richardson was born in 1989.
...

Divide the total votes by 5.5. Transform the result to an integer.

SELECT 
	CAST(total_votes/5.5 AS integer) AS DividedVotes
FROM voters;

DividedVotes
24
22
26
...

Select the voters whose total number of votes starts with 5.

SELECT 
	first_name,
	last_name,
	total_votes
FROM voters
WHERE CAST(total_votes AS varchar) LIKE '5%';

first_name	last_name	total_votes
Angela		Griffin		52
Kaylee		Cook		58
Gabrielle	Cox			55
...

</> CONVERTing data

Retrieve the birth date from voters, in this format: Mon dd,yyyy.

SELECT 
	email,
    CONVERT(varchar, birthdate, 107) AS birthdate
FROM voters;

email					birthdate
carol8@yahoo.com		Jan 15, 1989
ana0@gmail.com			Feb 17, 1987
melissa31@yahoo.com		Apr 25, 1989
...

Select the company, bean origin and the rating from the ratings table. The rating should be converted to a whole number.

SELECT 
	company,
    bean_origin,
    CONVERT(int, rating) AS rating
FROM ratings;

company		bean_origin		rating
Amedei		Toscano Black	2
Amedei		Toscano Black	2
Bonnat		Trinite			2
...

Select the company, bean origin and the rating from the ratings table where the whole part of the rating equals 3.

SELECT 
	company,
    bean_origin,
    rating
FROM ratings
-- Convert the rating to an integer before comparison
WHERE CONVERT(int, rating) = 3;

company		bean_origin				rating
Bonnat		Maracaibo- El Rosario	3.0000
Bonnat		Madagascar				3.0000
Bonnat		One Hundred				3.0000
...

</> Working with the correct data types

Restrict the query to retrieve only the female voters who voted more than 20 times.

SELECT 
	first_name,
	last_name,
	gender,
	country
FROM voters
WHERE country = 'Belgium'
	AND gender = 'F'
    AND total_votes > 20;

first_name	last_name	gender	country
Angela		Griffin		F 		Belgium
Julie		Becker		F 		Belgium
Jessica		Gonzales	F 		Belgium
...

Now that we have the data set prepared, let’s make it more user-friendly. Perform an explicit conversion from datetime to varchar(10), to show the dates as yy/mm/dd.

SELECT 
	first_name,
    last_name,
	CONVERT(varchar(10), birthdate, 11) AS birthdate,
    gender,
    country
FROM voters
WHERE country = 'Belgium' 
	AND gender = 'F'
    AND total_votes > 20;

first_name	last_name	birthdate	gender	country
Angela		Griffin		93/03/08	F 		Belgium
Julie		Becker		96/04/21	F 		Belgium
Jessica		Gonzales	93/04/11	F 		Belgium
...

Let’s now create a comments column that will show the number of votes performed by each person, in the following form: “Voted “x” times.”

SELECT
	first_name,
    last_name,
	CONVERT(varchar(10), birthdate, 11) AS birthdate,
    gender,
    country,
    'Voted ' + CAST(total_votes AS varchar) + ' times.' AS comments
FROM voters
WHERE country = 'Belgium'
	AND gender = 'F'
    AND total_votes > 20;

first_name	last_name	birthdate	gender	country	comments
Angela		Griffin		93/03/08	F 		Belgium	Voted 52 times.
Julie		Becker		96/04/21	F 		Belgium	Voted 189 times.
Jessica		Gonzales	93/04/11	F 		Belgium	Voted 171 times.
...

2. Manipulating time

</> Get the know the system date and time functions

Use the most common date function for retrieving the current date.

SELECT 
	SYSDATETIME() AS CurrentDate;

CurrentDate
2020-07-14 11:43:05.434873

Select the current date in UTC time (Universal Time Coordinate) using two different functions.

SELECT 
	SYSUTCDATETIME() AS UTC_HighPrecision,
	SYSDATETIMEOFFSET() AS UTC_LowPrecision;

UTC_HighPrecision			UTC_LowPrecision
2020-07-14 11:44:07.147463	2020-07-14 11:44:07.1474639 +00:00

Select the local system’s date, including the timezone information.

SELECT 
	GETDATE() AS Timezone;

Timezone
2020-07-14 11:44:59.400000

</> Selecting parts of the system’s date and time

Use two functions to query the system’s local date, without timezone information. Show the dates in different formats.

SELECT 
	CONVERT(VARCHAR(24), SYSUTCDATETIME(), 107) AS HighPrecision,
	CONVERT(VARCHAR(24), SYSDATETIMEOFFSET(), 102) AS LowPrecision;

HighPrecision	LowPrecision
Jul 14, 2020	2020.07.14

Use two functions to retrieve the current time, in Universal Time Coordinate.

SELECT 
	CAST(SYSUTCDATETIME() AS time) AS HighPrecision,
	CAST(SYSDATETIMEOFFSET() AS time) AS LowPrecision;

HighPrecision	LowPrecision
11:48:01.953926	11:48:01.953926

</> Extracting parts from a date

Extract the year, month and day of the first vote.

SELECT 
	first_name,
	last_name,
	YEAR(first_vote_date)  AS first_vote_year,
	MONTH(first_vote_date) AS first_vote_month,
	DAY(first_vote_date) AS first_vote_day
FROM voters;

first_name	last_name	first_vote_year	first_vote_month	first_vote_day
Carol		Rai			2015			3					9
Ana			Price		2015			1					17
Melissa		Richardson	2015			4					9
...

Restrict the query to show only the voters who started to vote after 2015.

SELECT 
	first_name,
	last_name,
	YEAR(first_vote_date)  AS first_vote_year,
	MONTH(first_vote_date) AS first_vote_month,
	DAY(first_vote_date)   AS first_vote_day
FROM voters
WHERE YEAR(first_vote_date) > 2015;

first_name	last_name	first_vote_year	first_vote_month	first_vote_day
Sebastian	Rogers		2016			1					6
Barbara		Zhao		2016			1					21
Haley		Sanchez		2016			1					5
...

Restrict the query to show only the voters did not vote on the first day of the month.

SELECT 
	first_name,
	last_name,
	YEAR(first_vote_date)  AS first_vote_year,
	MONTH(first_vote_date) AS first_vote_month,
	DAY(first_vote_date)   AS first_vote_day
FROM voters
WHERE YEAR(first_vote_date) > 2015
  AND DAY(first_vote_date) <> 1;

first_name	last_name	first_vote_year	first_vote_month	first_vote_day
Sebastian	Rogers		2016			1					6
Barbara		Zhao		2016			1					21
Haley		Sanchez		2016			1					5
...

</> Generating descriptive date parts

Select information from the voters table, including the name of the month when they first voted.

SELECT 
	first_name,
	last_name,
	first_vote_date,
	DATENAME(month, first_vote_date) AS first_vote_month
FROM voters;

first_name	last_name	first_vote_date	first_vote_month
Carol		Rai			2015-03-09		March
Ana			Price		2015-01-17		January
Melissa		Richardson	2015-04-09		April
...

Select information from the voters table, including the day of the year when they first voted.

SELECT 
	first_name,
	last_name,
	first_vote_date,
	DATENAME(dayofyear, first_vote_date) AS first_vote_dayofyear
FROM voters;

first_name	last_name	first_vote_date	first_vote_dayofyear
Carol		Rai			2015-03-09		68
Ana			Price		2015-01-17		17
Melissa		Richardson	2015-04-09		99
...

Select information from the voters table, including the day of the week when they first voted.

SELECT 
	first_name,
	last_name,
	first_vote_date,
	DATENAME(weekday, first_vote_date) AS first_vote_dayofweek
FROM voters;

first_name	last_name	first_vote_date	first_vote_dayofweek
Carol		Rai			2015-03-09		Monday
Ana			Price		2015-01-17		Saturday
Melissa		Richardson	2015-04-09		Thursday
...

</> Presenting parts of a date

Extract the month number of the first vote.

Extract the month name of the first vote.

Extract the weekday number of the first vote.

Extract the weekday name of the first vote.

SELECT 
	first_name,
	last_name,
	DATEPART(MONTH,first_vote_date) AS first_vote_month1,
    DATENAME(MONTH,first_vote_date) AS first_vote_month2,
	DATEPART(WEEKDAY,first_vote_date) AS first_vote_weekday1,
	DATENAME(WEEKDAY,first_vote_date) AS first_vote_weekday2
FROM voters;

first_name	last_name	first_vote_month1	first_vote_month2	first_vote_weekday1	first_vote_weekday2
Carol		Rai			3					March				2					Monday
Ana			Price		1					January				7					Saturday
Melissa		Richardson	4					April				5					Thursday
...

</> Creating a date from parts

Select the year of the first vote.

Select the month of the first vote date.

Create a date as the start of the month of the first vote.

SELECT 
	first_name,
	last_name,
   	YEAR(first_vote_date) AS first_vote_year, 
	MONTH(first_vote_date) AS first_vote_month,
	DATEFROMPARTS(YEAR(first_vote_date), MONTH(first_vote_date), 1) AS first_vote_starting_month
FROM voters;

first_name	last_name	first_vote_year	first_vote_month	first_vote_starting_month
Carol		Rai			2015			3					2015-03-01
Ana			Price		2015			1					2015-01-01
Melissa		Richardson	2015			4					2015-04-01
...

</> Arithmetic operations with dates

What is the result returned by the DATEDIFF() function?

• 0 years
• 238 years
• 261 years
• 23 years

DECLARE @date1 datetime = '2018-12-01';
DECLARE @date2 datetime = '2030-03-03';

select
@date2 - @date1 as sub1,
@date1 + @date2 as add1,
DATEDIFF(year, @date2 - @date1, @date1 + @date2)

sub1				add1	
1911-04-04 00:00:00	2149-01-31 00:00:00	238

</> Modifying the value of a date

Retrieve the date when each voter had their 18th birthday.

SELECT 
	first_name,
	birthdate,
	DATEADD(YEAR, 18, birthdate) AS eighteenth_birthday
  FROM voters;

first_name	birthdate	eighteenth_birthday
Carol		1989-01-15	2007-01-15
Ana			1987-02-17	2005-02-17
Melissa		1989-04-25	2007-04-25
...

Add five days to the first_vote_date, to calculate the date when the vote was processed.

SELECT 
	first_name,
	first_vote_date,
	DATEADD(DAY, 5, first_vote_date) AS processing_vote_date
  FROM voters;

first_name	first_vote_date	processing_vote_date
Carol		2015-03-09		2015-03-14
Ana			2015-01-17		2015-01-22
Melissa		2015-04-09		2015-04-14
...

Calculate what day it was 476 days ago.

SELECT
	getdate() - 476 AS date_476days_ago;

date_476days_ago
2019-03-27 11:25:59.870000

</> Calculating the difference between dates

Calculate the number of years since a participant celebrated their 18th birthday until the first time they voted.

SELECT
	first_name,
	birthdate,
	first_vote_date,
    -- Select the diff between the 18th birthday and first vote
	DATEDIFF(year, DATEADD(YEAR, 18, birthdate), first_vote_date) AS adult_years_until_vote
FROM voters;

first_name	birthdate	first_vote_date	adult_years_until_vote
Carol		1989-01-15	2015-03-09		8
Ana			1987-02-17	2015-01-17		10
Melissa		1989-04-25	2015-04-09		8
...

How many weeks have passed since the beginning of 2019 until now?

SELECT 
	DATEDIFF(week, '2019-01-01', getdate()) AS weeks_passed;

weeks_passed
80

</> Changing the date format

Set the correct date format so that the variable @date1 is interpreted as a valid date.

DECLARE @date1 NVARCHAR(20) = '2018-30-12';
SET DATEFORMAT ydm;
SELECT ISDATE(@date1) AS result;

result
1

Set the correct date format so that the variable @date1 is interpreted as a valid date.

DECLARE @date1 NVARCHAR(20) = '15/2019/4';
SET DATEFORMAT dym;
SELECT ISDATE(@date1) AS result;

result
1

Set the correct date format so that the variable @date1 is interpreted as a valid date.

DECLARE @date1 NVARCHAR(20) = '10.13.2019';
SET DATEFORMAT mdy;
SELECT ISDATE(@date1) AS result;

result
1

Set the correct date format so that the variable @date1 is interpreted as a valid date.

DECLARE @date1 NVARCHAR(20) = '18.4.2019';
SET DATEFORMAT dmy;
SELECT ISDATE(@date1) AS result;

result
1

</> Changing the default language

Language	Date format
English	mdy
Croatian	ymd
Dutch	dmy

Change the language, so that ‘30.03.2019’ is considered a valid date. Select the name of the month.

DECLARE @date1 NVARCHAR(20) = '30.03.2019';
SET LANGUAGE Dutch;
SELECT
	@date1 AS initial_date,
	ISDATE(@date1) AS is_valid,
	DATENAME(MONTH, @date1) AS month_name;

initial_date	is_valid	month_name
30.03.2019		1			maart

Change the language, so that ‘32/12/13’ is interpreted as a valid date. Select the name of the month. Select the year.

DECLARE @date1 NVARCHAR(20) = '32/12/13';
SET LANGUAGE Croatian;
SELECT
	@date1 AS initial_date,
	ISDATE(@date1) AS is_valid,
	DATENAME(MONTH, @date1) AS month_name,
	YEAR(@date1) AS year_name;

initial_date	is_valid	month_name	year_name
32/12/13		1			prosinac	2032

Change the language, so that ‘12/18/55’ is interpreted as a valid date. Select the day of week. Select the year.

DECLARE @date1 NVARCHAR(20) = '12/18/55';
SET LANGUAGE English;
SELECT
	@date1 AS initial_date,
	ISDATE(@date1) AS is_valid,
	DATENAME(weekday, @date1) AS week_day,
	YEAR(@date1) AS year_name;

initial_date	is_valid	week_day	year_name
12/18/55		1			Sunday		1955

</> Correctly applying different date functions

Extract the weekday from the first_vote_date.

SELECT
	first_name,
    last_name,
    birthdate,
	first_vote_date,
	DATENAME(weekday, first_vote_date) AS first_vote_weekday
FROM voters;

first_name	last_name	birthdate	first_vote_date	first_vote_weekday
Carol		Rai			1989-01-15	2015-03-09		Monday
Ana			Price		1987-02-17	2015-01-17		Saturday
Melissa		Richardson	1989-04-25	2015-04-09		Thursday
...

Find out the year when each person voted for the first time.

SELECT
	first_name,
    last_name,
    birthdate,
	first_vote_date,
	DATENAME(weekday, first_vote_date) AS first_vote_weekday,
	YEAR(first_vote_date) AS first_vote_year	
FROM voters;

first_name	last_name	birthdate	first_vote_date	first_vote_weekday	first_vote_year
Carol		Rai			1989-01-15	2015-03-09		Monday				2015
Ana			Price		1987-02-17	2015-01-17		Saturday			2015
Melissa		Richardson	1989-04-25	2015-04-09		Thursday			2015
...

Calculate the age of each participant when they first joined the voting contest.

SELECT
	first_name,
    last_name,
    birthdate,
	first_vote_date,
	DATENAME(weekday, first_vote_date) AS first_vote_weekday,
	YEAR(first_vote_date) AS first_vote_year,
	DATEDIFF(year, birthdate, first_vote_date) AS age_at_first_vote	
FROM voters;

first_name	last_name	birthdate	first_vote_date	first_vote_weekday	first_vote_year	age_at_first_vote
Carol		Rai			1989-01-15	2015-03-09		Monday				2015			26
Ana			Price		1987-02-17	2015-01-17		Saturday			2015			28
Melissa		Richardson	1989-04-25	2015-04-09		Thursday			2015			26
...

Calculate the current age of each participant. Remember that you can use functions as parameters for other functions.

SELECT
	first_name,
    last_name,
    birthdate,
	first_vote_date,
	DATENAME(weekday, first_vote_date) AS first_vote_weekday,
	YEAR(first_vote_date) AS first_vote_year,
	DATEDIFF(YEAR, birthdate, first_vote_date) AS age_at_first_vote,	
	DATEDIFF(YEAR, birthdate, getdate()) AS current_age
FROM voters;

first_name	last_name	birthdate	first_vote_date	first_vote_weekday	first_vote_year	age_at_first_vote	current_age
Carol		Rai			1989-01-15	2015-03-09		Monday				2015			26					31
Ana			Price		1987-02-17	2015-01-17		Saturday			2015			28					33
Melissa		Richardson	1989-04-25	2015-04-09		Thursday			2015			26					31
...

3. Working With Strings

</> Calculating the length of a string

Calculate the length of each broad_bean_origin.

Order the results from the longest to shortest.

SELECT TOP 10 
	company, 
	broad_bean_origin,
	LEN(broad_bean_origin) AS length
FROM ratings
ORDER BY LEN(broad_bean_origin) DESC;

company	broad_bean_origin				length
Raaka	Peru(SMartin-Pangoa-nacional)	30
Soma	Guat.- D.R.- Peru- Mad.- PNG	29
Bonnat	Venez-Africa-Brasil-Peru-Mex	29
...

</> Looking for a string within a string

Restrict the query to select only the voters whose first name contains the expression “dan”.

SELECT 
	first_name,
	last_name,
	email 
FROM voters
WHERE CHARINDEX('dan', first_name) > 0;

first_name	last_name	email
Daniel		Martin		daniel17@yahoo.com
Daniel		Martinez	daniel15@yahoo.com
Dana		Vazquez		dana7@yahoo.com
...

Restrict the query to select the voters with “dan” in the first_name and “z” in the last_name.

SELECT 
	first_name,
	last_name,
	email 
FROM voters
WHERE CHARINDEX('dan', first_name) > 0 
	AND CHARINDEX('z', last_name) > 0;

first_name	last_name	email
Daniel		Martinez	daniel15@yahoo.com
Dana		Vazquez		dana7@yahoo.com
Danny		Ruiz		danny2@yahoo.com

Restrict the query to select the voters with “dan” in the first_name and DO NOT have the letter “z” in the last_name.

SELECT 
	first_name,
	last_name,
	email 
FROM voters
WHERE CHARINDEX('dan', first_name) > 0 
	AND CHARINDEX('z', last_name) = 0;

first_name	last_name	email
Daniel		Martin		daniel17@yahoo.com
Danny		Martin		danny0@yahoo.com

</> Looking for a pattern within a string

pattern	match
%	any string of zero or more characters
_	any single character
[]	any single character within the range specified in brackets

Write a query to select the voters whose first name contains the letters “rr”.

SELECT 
	first_name,
	last_name,
	email 
FROM voters
WHERE PATINDEX('%rr%', first_name) > 0;

first_name	last_name	email
Darryl		Liu			darryl4@yahoo.com
Terrence	Andersen	terrence13@yahoo.com
Darrell		Shan		darrell18@yahoo.com

Write a query to select the voters whose first name starts with “C” and has “r” as the third letter.

SELECT 
	first_name,
	last_name,
	email 
FROM voters
WHERE PATINDEX('C_r%', first_name) > 0;

first_name	last_name	email
Carol		Rai			carol8@yahoo.com
Caroline	Griffin		caroline22@yahoo.com
Christine	Chander		christine11@yahoo.com
...

Select the voters whose first name contains an “a” followed by other letters, then a “w”, followed by other letters.

SELECT 
	first_name,
	last_name,
	email 
FROM voters
WHERE PATINDEX('%a%w%', first_name) > 0;

first_name	last_name	email
Mathew		Sanz		mathew16@yahoo.com
Dawn		Pal			dawn36@yahoo.com
Andrew		Clark		andrew26@gmail.com

Write a query to select the voters whose first name contains one of these letters: “x”, “w” or “q”.

SELECT 
	first_name,
	last_name,
	email 
FROM voters
WHERE PATINDEX('%[xwq]%', first_name) > 0;

first_name	last_name	email
Mathew		Sanz		mathew16@yahoo.com
Wesley		Yang		wesley5@yahoo.com
Alexandra	Hill		alexandra63@gmail.com
...

</> Changing to lowercase and uppercase

Select information from the ratings table, excluding the unknown broad_bean_origins.

Convert the broad_bean_origins to lowercase when comparing it to the ‘%unknown%’ expression.

SELECT 
	company,
	bean_type,
	broad_bean_origin,
	'The company ' +  company + ' uses beans of type "' + bean_type + '", originating from ' + broad_bean_origin + '.'
FROM ratings
WHERE
	LOWER(broad_bean_origin) NOT LIKE '%unknown%';

company	bean_type	broad_bean_origin	
Bonnat	Trinitario	Carribean			The company Bonnat uses beans of type "Trinitario", originating from Carribean.
Bonnat	Unknown		Venezuela			The company Bonnat uses beans of type "Unknown", originating from Venezuela.
Bonnat	Criollo		Venezuela			The company Bonnat uses beans of type "Criollo", originating from Venezuela.
...

Restrict the query to make sure that the bean_type is not unknown.

Convert the bean_type to lowercase and compare it with an expression that contains the ‘%unknown%’ word.

SELECT 
	company,
	bean_type,
	broad_bean_origin,
	'The company ' +  company + ' uses beans of type "' + bean_type + '", originating from ' + broad_bean_origin + '.'
FROM ratings
WHERE 
	LOWER(broad_bean_origin) NOT LIKE '%unknown%'
    AND LOWER(bean_type) NOT LIKE '%unknown%';

company	bean_type	broad_bean_origin	
Bonnat	Trinitario	Carribean			The company Bonnat uses beans of type "Trinitario", originating from Carribean.
Bonnat	Criollo		Venezuela			The company Bonnat uses beans of type "Criollo", originating from Venezuela.
Bonnat	Trinitario	Madagascar			The company Bonnat uses beans of type "Trinitario", originating from Madagascar.
...

Format the message so that company and broad_bean_origin are uppercase.

SELECT 
	company,
	bean_type,
	broad_bean_origin,
	'The company ' +  UPPER(company) + ' uses beans of type "' + bean_type + '", originating from ' + UPPER(broad_bean_origin) + '.'
FROM ratings
WHERE 
	LOWER(broad_bean_origin) NOT LIKE '%unknown%'
    AND LOWER(bean_type) NOT LIKE '%unknown%';

company	bean_type	broad_bean_origin	
Bonnat	Trinitario	Carribean			The company BONNAT uses beans of type "Trinitario", originating from CARRIBEAN.
Bonnat	Criollo		Venezuela			The company BONNAT uses beans of type "Criollo", originating from VENEZUELA.
Bonnat	Trinitario	Madagascar			The company BONNAT uses beans of type "Trinitario", originating from MADAGASCAR.
...

</> Using the beginning or end of a string

Select information from the voters table, including a new column called part1, containing only the first 3 letters from the first name.

SELECT 
	first_name,
	last_name,
	country,
	LEFT(first_name, 3) AS part1
FROM voters;

first_name	last_name	country		part1
Carol		Rai			Denmark		Car
Ana			Price		France		Ana
Melissa		Richardson	Denmark		Mel
...

Add a new column to the previous query, containing the last 3 letters from the last name.

SELECT 
	first_name,
	last_name,
	country,
	LEFT(first_name, 3) AS part1,
    RIGHT(last_name, 3) AS part2
FROM voters;

first_name	last_name	country		part1	part2
Carol		Rai			Denmark		Car		Rai
Ana			Price		France		Ana		ice
Melissa		Richardson	Denmark		Mel		son
...

Add another column to the previous query, containing the last 2 digits from the birth date.

SELECT 
	first_name,
	last_name,
	country,
	LEFT(first_name, 3) AS part1,
    RIGHT(last_name, 3) AS part2,
    RIGHT(birthdate, 2) AS part3
FROM voters;

first_name	last_name	country		part1	part2	part3
Carol		Rai			Denmark		Car		Rai		15
Ana			Price		France		Ana		ice		17
Melissa		Richardson	Denmark		Mel		son		25
...

Create an alias for each voter with the following parts: the first 3 letters from the first name concatenated with the last 3 letters from the last name, followed by the _ character and the last 2 digits from the birth date.

SELECT 
	first_name,
	last_name,
	country,
	LEFT(first_name, 3) AS part1,
    RIGHT(last_name, 3) AS part2,
    RIGHT(birthdate, 2) AS part3,
    LEFT(first_name, 3) + RIGHT(last_name, 3) + '_' + RIGHT(birthdate, 2) 
FROM voters;

first_name	last_name	country		part1	part2	part3	
Carol		Rai			Denmark		Car		Rai		15		CarRai_15
Ana			Price		France		Ana		ice		17		Anaice_17
Melissa		Richardson	Denmark		Mel		son		25		Melson_25
...

</> Extracting a substring

Select 5 characters from the email address, starting with position 3.

SELECT 
	email,
	SUBSTRING(email, 3, 5) AS some_letters
FROM voters;

email				some_letters
carol8@yahoo.com	rol8@
ana0@gmail.com		a0@gm
melissa31@yahoo.com	lissa
...

Extract the fruit names from the following sentence: “Apples are neither oranges nor potatoes”.

DECLARE @sentence NVARCHAR(200) = 'Apples are neither oranges nor potatoes.'
SELECT
	SUBSTRING(@sentence, 1, 6) AS fruit1,
	SUBSTRING(@sentence, 20, 7) AS fruit2;

fruit1	fruit2
Apples	oranges

</> Replacing parts of a string

Add a new column in the query in which you replace the “yahoo.com” in all email addresses with “live.com”.

SELECT 
	first_name,
	last_name,
	email,
	REPLACE(email, 'yahoo.com', 'live.com') AS new_email
FROM voters;

first_name	last_name	email				new_email
Carol		Rai			carol8@yahoo.com	carol8@live.com
Ana			Price		ana0@gmail.com		ana0@gmail.com
Melissa		Richardson	melissa31@yahoo.com	melissa31@live.com
...

Replace the character “&” from the company name with “and”.

SELECT 
	company AS initial_name,
	REPLACE(company, '&', 'and') AS new_name 
FROM ratings
WHERE CHARINDEX('&', company) > 0;

initial_name						new_name
Green & Black's (ICAM)				Green and Black's (ICAM)
Lindt & Sprungli					Lindt and Sprungli
Debauve & Gallais (Michel Cluizel)	Debauve and Gallais (Michel Cluizel)
...

Remove the string “(Valrhona)” from the company name “La Maison du Chocolat (Valrhona)”.

SELECT 
	company AS old_company,
	REPLACE(company, '(Valrhona)', '') AS new_company,
	bean_type,
	broad_bean_origin
FROM ratings
WHERE company = 'La Maison du Chocolat (Valrhona)';

old_company							new_company				bean_type			broad_bean_origin
La Maison du Chocolat (Valrhona)	La Maison du Chocolat 	Blend				Ven.- Indonesia- Ecuad.
La Maison du Chocolat (Valrhona)	La Maison du Chocolat 	Forastero			Ghana
La Maison du Chocolat (Valrhona)	La Maison du Chocolat 	Criollo (Porcelana)	Venezuela
...

</> Concatenating data

Create a message similar to this one: “Chocolate with beans from Belize has a cocoa percentage of 0.6400” for each result of the query.

Use the + operator to concatenate data and the ’ ’ character as a separator.

DECLARE @string1 NVARCHAR(100) = 'Chocolate with beans from';
DECLARE @string2 NVARCHAR(100) = 'has a cocoa percentage of';

SELECT 
	bean_type,
	bean_origin,
	cocoa_percent,
	@string1 + ' ' + bean_origin + ' ' + @string2 + ' ' + CAST(cocoa_percent AS nvarchar) AS message1
FROM ratings
WHERE 
	company = 'Ambrosia' 
	AND bean_type <> 'Unknown';

bean_type	bean_origin	cocoa_percent	message1
Trinitario	Belize		0.6400			Chocolate with beans from Belize has a cocoa percentage of 0.6400
Trinitario	Madagascar	0.6600			Chocolate with beans from Madagascar has a cocoa percentage of 0.6600

Create the same message, using the CONCAT() function.

DECLARE @string1 NVARCHAR(100) = 'Chocolate with beans from';
DECLARE @string2 NVARCHAR(100) = 'has a cocoa percentage of';

SELECT 
	bean_type,
	bean_origin,
	cocoa_percent,
	@string1 + ' ' + bean_origin + ' ' + @string2 + ' ' + CAST(cocoa_percent AS nvarchar) AS message1,
	CONCAT(@string1, ' ', bean_origin, ' ', @string2, ' ', CAST(cocoa_percent AS nvarchar)) AS message2
FROM ratings
WHERE 
	company = 'Ambrosia' 
	AND bean_type <> 'Unknown';

bean_type	bean_origin	cocoa_percent	message1																message2
Trinitario	Belize		0.6400			Chocolate with beans from Belize has a cocoa percentage of 0.6400		Chocolate with beans from Belize has a cocoa percentage of 0.6400
Trinitario	Madagascar	0.6600			Chocolate with beans from Madagascar has a cocoa percentage of 0.6600	Chocolate with beans from Madagascar has a cocoa percentage of 0.6600

Create the same message, using the CONCAT_WS() function. Evaluate the difference between this method and the previous ones.

DECLARE @string1 NVARCHAR(100) = 'Chocolate with beans from';
DECLARE @string2 NVARCHAR(100) = 'has a cocoa percentage of';

SELECT 
	bean_type,
	bean_origin,
	cocoa_percent,
	@string1 + ' ' + bean_origin + ' ' + @string2 + ' ' + CAST(cocoa_percent AS nvarchar) AS message1,
	CONCAT(@string1, ' ', bean_origin, ' ', @string2, ' ', cocoa_percent) AS message2,
	CONCAT_WS(' ', @string1, bean_origin, @string2, cocoa_percent) AS message3
FROM ratings
WHERE 
	company = 'Ambrosia' 
	AND bean_type <> 'Unknown';

bean_type	bean_origin	cocoa_percent	message1																message2																message3
Trinitario	Belize		0.6400			Chocolate with beans from Belize has a cocoa percentage of 0.6400		Chocolate with beans from Belize has a cocoa percentage of 0.6400		Chocolate with beans from Belize has a cocoa percentage of 0.6400
Trinitario	Madagascar	0.6600			Chocolate with beans from Madagascar has a cocoa percentage of 0.6600	Chocolate with beans from Madagascar has a cocoa percentage of 0.6600	Chocolate with beans from Madagascar has a cocoa percentage of 0.6600

</> Aggregating strings

Create a list with all the values found in the bean_origin column for the companies: ‘Bar Au Chocolat’, ‘Chocolate Con Amor’, ‘East Van Roasters’. The values should be separated by commas (,).

SELECT
	STRING_AGG(bean_origin, ',') AS bean_origins
FROM ratings
WHERE company IN ('Bar Au Chocolat', 'Chocolate Con Amor', 'East Van Roasters');

bean_origins
Duarte Province,Chiapas,Sambirano,Maranon Canyon,Peru,Madagascar,Dominican Republic,Bahia,Wild Bolivian,Uganda,Dominican Republic,Dominican Republic,Ocumare,Ecuador,Nicaragua

Create a list with the values found in the bean_origin column for each of the companies: ‘Bar Au Chocolat’, ‘Chocolate Con Amor’, ‘East Van Roasters’. The values should be separated by commas (,).

SELECT 
	company,
	STRING_AGG(bean_origin, ',') AS bean_origins
FROM ratings
WHERE company IN ('Bar Au Chocolat', 'Chocolate Con Amor', 'East Van Roasters')
GROUP BY company;

company				bean_origins
Bar Au Chocolat		Bahia,Duarte Province,Chiapas,Sambirano,Maranon Canyon
Chocolate Con Amor	Wild Bolivian,Uganda,Dominican Republic,Dominican Republic,Ocumare,Ecuador,Nicaragua
East Van Roasters	Peru,Madagascar,Dominican Republic

Arrange the values from the list in alphabetical order.

SELECT 
	company,
	STRING_AGG(bean_origin, ',') WITHIN GROUP (ORDER BY bean_origin) AS bean_origins
FROM ratings
WHERE company IN ('Bar Au Chocolat', 'Chocolate Con Amor', 'East Van Roasters')
GROUP BY company;

company				bean_origins
Bar Au Chocolat		Bahia,Chiapas,Duarte Province,Maranon Canyon,Sambirano
Chocolate Con Amor	Dominican Republic,Dominican Republic,Ecuador,Nicaragua,Ocumare,Uganda,Wild Bolivian
East Van Roasters	Dominican Republic,Madagascar,Peru

</> Splitting a string into pieces

Split the phrase declared in the variable @phrase into sentences (using the . separator).

DECLARE @phrase NVARCHAR(MAX) = 'In the morning I brush my teeth. In the afternoon I take a nap. In the evening I watch TV.'

SELECT value
FROM STRING_SPLIT(@phrase, '.');

value
In the morning I brush my teeth
In the afternoon I take a nap
In the evening I watch TV

Split the phrase declared in the variable @phrase into individual words.

DECLARE @phrase NVARCHAR(MAX) = 'In the morning I brush my teeth. In the afternoon I take a nap. In the evening I watch TV.'

SELECT value
FROM STRING_SPLIT(@phrase, ' ');

value
In
the
morning
...

</> Applying various string functions on data

Select only the voters whose first name has fewer than 5 characters and email address meets these conditions in the same time: (1) starts with the letter “j”, (2) the third letter is “a” and (3) is created at yahoo.com.

SELECT
	first_name,
    last_name,
	birthdate,
	email,
	country
FROM voters
WHERE LEN(first_name) < 5
	AND PATINDEX('j_a%@yahoo.com', email) > 0;

first_name	last_name	birthdate	email				country
Juan		Romero		1978-03-15	juan1@yahoo.com		Germany
Joan		Ross		1992-06-22	joan8@yahoo.com		Switzerland
Joan		Martin		1985-03-26	joan19@yahoo.com	Spain

Concatenate the first name and last name in the same column and present it in this format: " *** Firstname LASTNAME *** ".

SELECT
	CONCAT('***' , first_name, ' ', UPPER(last_name), '***') AS name,
    last_name,
	birthdate,
	email,
	country
FROM voters
WHERE LEN(first_name) < 5
	AND PATINDEX('j_a%@yahoo.com', email) > 0; 

name				last_name	birthdate	email				country
***Juan ROMERO***	Romero		1978-03-15	juan1@yahoo.com		Germany
***Joan ROSS***		Ross		1992-06-22	joan8@yahoo.com		Switzerland
***Joan MARTIN***	Martin		1985-03-26	joan19@yahoo.com	Spain

Mask the year part from the birthdate column, by replacing the last two digits with “XX” (1986-03-26 becomes 19XX-03-26).

SELECT
	CONCAT('***' , first_name, ' ', UPPER(last_name), '***') AS name,
    REPLACE(birthdate, SUBSTRING(CAST(birthdate AS varchar), 3, 2), 'XX') AS birthdate,
	email,
	country
FROM voters
WHERE LEN(first_name) < 5
	AND PATINDEX('j_a%@yahoo.com', email) > 0;    

name				birthdate	email				country
***Juan ROMERO***	19XX-03-15	juan1@yahoo.com		Germany
***Joan ROSS***		19XX-06-22	joan8@yahoo.com		Switzerland
***Joan MARTIN***	19XX-03-26	joan19@yahoo.com	Spain

4. Recognizing Numeric Data Properties

</> Learning how to count and add

Count the number of voters for each group.

Calculate the total number of votes per group.

SELECT 
	gender, 
	COUNT(customer_id) AS voters,
	SUM(total_votes) AS total_votes
FROM voters
GROUP BY gender;

gender	voters	total_votes
F 		91		9193
M 		105		10708

</> MINimizing and MAXimizing some results

Calculate the average percentage of cocoa used by each company.

SELECT 
	company,
	AVG(cocoa_percent) AS avg_cocoa
FROM ratings
GROUP BY company;

company		avg_cocoa
A. Morin	0.690869
Acalli		0.700000
Adi			0.750000
...

Calculate the minimum rating received by each company.

SELECT 
	company,
	AVG(cocoa_percent) AS avg_cocoa,
	MIN(rating) AS min_rating	
FROM ratings
GROUP BY company;

company		avg_cocoa	min_rating
A. Morin	0.690869	2.5000
Acalli		0.700000	3.5000
Adi			0.750000	2.7500
...

Calculate the maximum rating received by each company.

SELECT 
	company,
	AVG(cocoa_percent) AS avg_cocoa,
	MIN(rating) AS min_rating,
	MAX(rating) AS max_rating
FROM ratings
GROUP BY company;

company		avg_cocoa	min_rating	max_rating
A. Morin	0.690869	2.5000		4.0000
Acalli		0.700000	3.5000		3.5000
Adi			0.750000	2.7500		3.2500
...

Use an aggregate function to order the results of the query by the maximum rating, in descending order.

SELECT 
	company,
	AVG(cocoa_percent) AS avg_cocoa,
	MIN(rating) AS min_rating,
	MAX(rating) AS max_rating
FROM ratings
GROUP BY company
ORDER BY MAX(rating) DESC;

company			avg_cocoa	min_rating	max_rating
Askinosie		0.720000	3.0000		5.0000
Bonnat			0.755555	2.7500		5.0000
Black Mountain	0.700000	2.7500		4.0000
...

</> Accessing values from the next row

Create a new column, showing the number of votes recorded for the next person in the list.

Create a new column with the difference between the current voter’s total_votes and the votes of the next person.

SELECT 
	first_name,
	last_name,
	total_votes AS votes,
	LEAD(total_votes) OVER (ORDER BY total_votes) AS votes_next_voter,
	LEAD(total_votes) OVER (ORDER BY total_votes) - total_votes AS votes_diff
FROM voters
WHERE country = 'France'
ORDER BY total_votes;

first_name	last_name	votes	votes_next_voter	votes_diff
Roger		Rai			49		56					7
Meredith	Vance		56		73					17
Max			Ruiz		73		90					17
...

</> Accessing values from the previous row

Create a new column, showing the cocoa percentage of the chocolate bar that received a lower score, with cocoa coming from the same location (broad_bean_origin is the same).

Create a new column with the difference between the current bar’s cocoa percentage and the percentage of the previous bar.

SELECT 
	broad_bean_origin AS bean_origin,
	rating,
	cocoa_percent,
	LAG(cocoa_percent) 
		OVER(PARTITION BY broad_bean_origin ORDER BY rating) AS percent_lower_rating
FROM ratings
WHERE company = 'Fruition'
ORDER BY broad_bean_origin, rating ASC;

bean_origin	rating	cocoa_percent	percent_lower_rating
Bolivia		3.7500	0.7400			null
Costa Rica	1.5000	0.6600			null
Costa Rica	3.0000	0.7000			0.6600
...

</> Getting the first and last value

Boundary	Description
UNBOUNDED PRECEDING	first row in the partition
UNBOUNDED FOLLOWING	last row in the partition
CURRENT ROW	current row
PRECEDING	previous row
FOLLOWING	next row

Retrieve the birth date of the oldest voter from each country.

Retrieve the birth date of the youngest voter from each country.

SELECT 
	first_name + ' ' + last_name AS name,
	country,
	birthdate,
	FIRST_VALUE(birthdate) 
	OVER (PARTITION BY country ORDER BY birthdate) AS oldest_voter,
	LAST_VALUE(birthdate) 
		OVER (PARTITION BY country ORDER BY birthdate ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
				) AS youngest_voter
FROM voters
WHERE country IN ('Spain', 'USA');

name				country	birthdate	oldest_voter	youngest_voter
Caroline Griffin	Spain	1981-03-20	1981-03-20		1988-03-21
Christopher Jackson	Spain	1981-04-15	1981-03-20		1988-03-21
Raul Raji			Spain	1981-04-25	1981-03-20		1988-03-21
...

</> Extracting the sign and the absolute value

Calculate the absolute value of the result of the expression.

DECLARE @number1 DECIMAL(18,2) = -5.4;
DECLARE @number2 DECIMAL(18,2) = 7.89;
DECLARE @number3 DECIMAL(18,2) = 13.2;
DECLARE @number4 DECIMAL(18,2) = 0.003;

DECLARE @result DECIMAL(18,2) = @number1 * @number2 - @number3 - @number4;
SELECT 
	@result AS result,
	ABS(@result) AS abs_result;

result	abs_result
-55.81	55.81

Find out the sign of the result (positive or negative).

DECLARE @number1 DECIMAL(18,2) = -5.4;
DECLARE @number2 DECIMAL(18,2) = 7.89;
DECLARE @number3 DECIMAL(18,2) = 13.2;
DECLARE @number4 DECIMAL(18,2) = 0.003;

DECLARE @result DECIMAL(18,2) = @number1 * @number2 - @number3 - @number4;
SELECT 
	@result AS result,
	ABS(@result) AS abs_result,
	SIGN(@result) AS sign_result;

result	abs_result	sign_result
-55.81	55.81		-1.00

</> Rounding numbers

Round up the ratings to the nearest integer value.

SELECT
	rating,
	CEILING(rating) AS round_up
FROM ratings;

rating	round_up
2.7500	3
2.7500	3
2.7500	3
...

Round down the ratings to the nearest integer value.

SELECT
	rating,
	CEILING(rating) AS round_up,
	FLOOR(rating) AS round_down
FROM ratings;

rating		round_up	round_down
2.7500		3			2
2.7500		3			2
2.7500		3			2
...

Round the ratings to a decimal number with only 1 decimal.

SELECT
	rating,
	CEILING(rating) AS round_up,
	FLOOR(rating) AS round_down,
	ROUND(rating, 1) AS round_onedec
FROM ratings;

rating	round_up	round_down	round_onedec
2.7500	3			2			2.8000
2.7500	3			2			2.8000
2.7500	3			2			2.8000
...

Round the ratings to a decimal number with 2 decimals.

SELECT
	rating,
	CEILING(rating) AS round_up,
	FLOOR(rating) AS round_down,
	ROUND(rating, 1) AS round_onedec,
	ROUND(rating, 2) AS round_twodec
FROM ratings;

rating	round_up	round_down	round_onedec	round_twodec
2.7500	3			2			2.8000			2.7500
2.7500	3			2			2.8000			2.7500
2.7500	3			2			2.8000			2.7500
...

</> Working with exponential functions

Raise the number stored in the @number variable to the power from the @power variable.

Calculate the square of the @number variable (square means the power of 2).

Calculate the square root of the number stored in the @number variable.

DECLARE @number DECIMAL(4, 2) = 4.5;
DECLARE @power INT = 4;

SELECT
	@number AS number,
	@power AS power,
	POWER(@number, @power) AS number_to_power,
	SQUARE(@number) num_squared,
	SQRT(@number) num_square_root;

number	power	number_to_power	num_squared	num_square_root
4.50	4		410.06			20.25		2.1213203435596424

</> Manipulating numeric data

Select the number of cocoa flavors the company was rated on.

Select the lowest, highest and the average rating that each company received.

SELECT 
	company, 
	COUNT(*) AS flavors,
	MIN(rating) AS lowest_score,
	MAX(rating) AS highest_score,
	AVG(rating) AS avg_score	  
FROM ratings
GROUP BY company
ORDER BY flavors DESC;

company	flavors	lowest_score	highest_score	avg_score
Soma	47		2.0000			4.0000			3.015957
Bonnat	27		2.7500			5.0000			3.277777
Fresco	26		2.0000			4.0000			3.423076
...

Round the average rating to 1 decimal and show it as a different column.

SELECT 
	company, 
	COUNT(*) AS flavors,
	MIN(rating) AS lowest_score,  
	MAX(rating) AS highest_score,   
	AVG(rating) AS avg_score,
    ROUND(AVG(rating), 1) AS round_avg_score	
FROM ratings
GROUP BY company
ORDER BY flavors DESC;

company	flavors	lowest_score	highest_score	avg_score	round_avg_score
Soma	47		2.0000			4.0000			3.015957	3.000000
Bonnat	27		2.7500			5.0000			3.277777	3.300000
Fresco	26		2.0000			4.0000			3.423076	3.400000
...

Calculate the average rating received by each company and perform the following approximations:

a. round-up to the next integer value

b. round-down to the previous integer value.

SELECT 
	company, 
	COUNT(*) AS flavors,
	MIN(rating) AS lowest_score,   
	MAX(rating) AS highest_score,   
	AVG(rating) AS avg_score,
    ROUND(AVG(rating), 1) AS round_avg_score,
    CEILING(AVG(rating)) AS round_up_avg_score,   
	FLOOR(AVG(rating)) AS round_down_avg_score
FROM ratings
GROUP BY company
ORDER BY flavors DESC;

company	flavors	lowest_score	highest_score	avg_score	round_avg_score	round_up_avg_score	round_down_avg_score
Soma	47		2.0000			4.0000			3.015957	3.000000		4					3
Bonnat	27		2.7500			5.0000			3.277777	3.300000		4					3
Fresco	26		2.0000			4.0000			3.423076	3.400000		4					3
...