Skip to content

4 - aggregation-string-func.md

Latest commit

 

History

History
112 lines (103 loc) · 5.12 KB

4 - aggregation-string-func.md

File metadata and controls

112 lines (103 loc) · 5.12 KB

Lets learn how to aggregate and do some text manipulation

Use the previously imported database (wallets). Write a query that returns the total number of wallet_addr in the wallet_addr table.

SELECT COUNT(customer_id) AS total_wallet_addr FROM wallet_addr;

Write a query that returns the total number of wallet_addr that have a balance greater than 10000.

SELECT COUNT(customer_id) AS total_wallet_addr_with_balance FROM wallet_addr WHERE btc > 10000;

Write a query that returns the average balance of all wallet_addr.

SELECT AVG(btc) AS average_balance FROM wallet_addr;

Write a query that returns the total balance of all wallet_addr.

SELECT SUM(btc) AS total_balance FROM wallet_addr;

Write a query that returns the smallest balance of all wallet_addr.

SELECT MIN(btc) AS smallest_balance FROM wallet_addr;

Write a query that returns the largest balance of all wallet_addr.

SELECT MAX(btc) AS largest_balance FROM wallet_addr;

Write a query that returns the wallet_addr with the highest balance.

SELECT * FROM wallet_addr ORDER BY btc DESC LIMIT 1;

Write a query that returns the wallet_addr with the lowest balance.

SELECT * FROM wallet_addr ORDER BY btc ASC LIMIT 1;

Write a query that returns the total number of wallet_addr that have a balance greater than 10000 and less than 20000.

SELECT COUNT(customer_id) AS total_wallet_addr_between_10000_and_20000 FROM wallet_addr WHERE btc > 10000 AND btc < 20000;

Write a query that returns the first 3 characters of the first name of each wallet_addr in the wallet_addr table.

SELECT SUBSTRING(first_name, 1, 3) AS first_3_characters_of_first_name FROM wallet_addr;

CONCATENATION Write a query that returns the full name of each wallet_addr in the wallet_addr table.

SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM wallet_addr;

Write a query that returns the full name of each wallet_addr in the wallet_addr table, with a random string of 5 characters added to the end of each name.

SELECT CONCAT(first_name, ' ', last_name, ' ', SUBSTRING(MD5(RANDOM()::text), 1, 5)) AS full_name_with_random_string FROM wallet_addr;

  1. RANDOM():

    • This function generates a random floating-point number between 0 (inclusive) and 1 (exclusive). Each time you call RANDOM(), it produces a different random number.

  2. MD5():

    • This function takes a string as input and returns its MD5 hash. The MD5 hash is a 32-character hexadecimal number. In this case, the input to MD5() is the random number generated by RANDOM(). Since RANDOM() produces a different number each time, the MD5 hash will also be different each time.

  3. SUBSTRING():

    • This function extracts a substring from a given string. It takes three arguments: the string to extract from, the starting position, and the length of the substring. In this case, SUBSTRING(MD5(RANDOM()::text), 1, 5) extracts the first 5 characters of the MD5 hash.

Putting it all together:

  • RANDOM() generates a random number in PostgreSQL.
  • MD5(RANDOM()::text) converts that random number into a 32-character MD5 hash.
  • SUBSTRING(MD5(RANDOM()::text), 1, 5) extracts the first 5 characters of that MD5 hash.

This combination of functions is often used to generate a short, random string.

Dialect note:

  • MySQL/MariaDB: use SUBSTRING(MD5(RAND()), 1, 5).
  • Microsoft SQL Server: use SUBSTRING(CONVERT(varchar(32), HASHBYTES('MD5', CAST(RAND() AS varchar(50))), 2), 1, 5).

LEFT, RIGHT, and MID Write a query that returns the first 3 characters of the first name of each wallet_addr in the wallet_addr table.

SELECT LEFT(first_name, 3) AS first_3_characters_of_first_name FROM wallet_addr;

Write a query that returns the last 3 characters of the last name of each wallet_addr in the wallet_addr table.

SELECT RIGHT(last_name, 3) AS last_3_characters_of_last_name FROM wallet_addr;

Write a query that returns the 4th to 6th characters of the first name of each wallet_addr in the wallet_addr table.

SELECT SUBSTRING(first_name FROM 4 FOR 3) AS fourth_to_sixth_characters_of_first_name FROM wallet_addr;

Dialect note:

  • MySQL/MariaDB: SUBSTRING(first_name, 4, 3)
  • Microsoft SQL Server: SUBSTRING(first_name, 4, 3) Lowercase and Uppercase Write a query that returns the first name of each wallet_addr in lowercase.
SELECT LOWER(first_name) AS lowercase_first_name FROM wallet_addr;

Write a query that returns the last name of each wallet_addr in uppercase.

SELECT UPPER(last_name) AS uppercase_last_name FROM wallet_addr;

Length and Trim Write a query that returns the length of the first name of each wallet_addr in the wallet_addr table.

SELECT LENGTH(first_name) AS length_of_first_name FROM wallet_addr;

Write a query that returns the first name of each wallet_addr in the wallet_addr table, with leading and trailing spaces removed. (trim, ltrim and rtrim)

SELECT TRIM(address) AS address_without_leading_and_trailing_spaces FROM wallet_addr;

Write a query to remove spaces from the address column

SELECT REPLACE(address, ' ', '') AS address_without_spaces FROM wallet_addr;