Skip to content
New Workbook
Sign up
Functions

BUILT-IN FUNCTIONS CONCAT and PAD functions like RPAD and LPAD to add additional characters.

Take Notes

Add notes about the concepts you've learned and SQL cells with queries you want to keep.

Add your notes here

Spinner
DataFrameavailable as
df
variable
-- Concatenate the first_name and last_name 
SELECT 
	RPAD(first_name, LENGTH(first_name)+1) 
    || RPAD(last_name, LENGTH(last_name)+2, ' <') 
    || RPAD(email, LENGTH(email)+1, '>') AS full_email
FROM dvdrentals.customer;
Spinner
DataFrameavailable as
df1
variable
SELECT 
  UPPER(c.name) || ': ' || f.title AS film_category, 
  -- Truncate the description without cutting off a word
  LEFT(description, 50 - 
    -- Subtract the position of the first whitespace character
    POSITION(
      ' ' IN REVERSE(LEFT(description, 50))
    )
  ) 
FROM 
  film AS f 
  INNER JOIN film_category AS fc 
  	ON f.film_id = fc.film_id 
  INNER JOIN category AS c 
  	ON fc.category_id = c.category_id;
Spinner
DataFrameavailable as
df2
variable
-- Case insensitive and check 'elf' in title column by using tsvector and tsquery function
-- Select the title and description
SELECT title, description
FROM film
-- Convert the title to a tsvector and match it against the tsquery 
WHERE to_tsvector(title) @@ to_tsquery('elf');
Spinner
DataFrameavailable as
df3
variable
---ENUM is handy when the info is certain and never gonna change like below example


-- Create an enumerated data type, compass_position
CREATE TYPE compass_position AS ENUM (
  	-- Use the four cardinal directions
  	'North', 
  	'South',
  	'West', 
  	'East'
);

INITCAP FUNCTION------>>>>

Use the INITCAP function to replace the first letter of every word in a character string with an uppercase letter. The INITCAP function assumes a new word whenever the function encounters a letter that is preceded by any character other than a letter. The INITCAP function accepts an argument of a character data type and returns a value of the same data type as the argument you specify.

The following query uses the INITCAP function to convert the first letter of every word in a character string to an uppercase letter. Figure 1. Query SELECT INITCAP(description) FROM stock WHERE manu_code = "ANZ";

Figure 2. Query result (expression)

Tennis Racquet Tennis Ball Volleyball Volleyball Net Helmet Golf Shoes 3 Golf Balls Running Shoes Watch Kick Board Swim Cap

Spinner
DataFrameavailable as
df4
variable
SELECT ALTER substring(code, 3, 4) AS code_part
FROM accounts
ORDER BY user_id;

------SOLDAN 3. sıradan yazdırmaya başla ve 4 karakter yaz demek SUBSTRING fonksiyonu kullanarak bunu yapabiliriz.
Spinner
DataFrameavailable as
df5
variable
SELECT EXTRACT(month FROM timestamp '2020-11-30 09:30:20') AS month;
----EXTRACT fonksiyonu ile bir timestamp in istediğimiz kısmını alabiliriz bu query nin sonucu


-----OUTPUT
----month
----11

COALESCE function -operates row by row -returns first NON-NULL value

COALESCE(value_1, value_2,...)

Spinner
DataFrameavailable as
df6
variable
--- split_part(string_to_split, delimiter, part_number) aşağıdaki örnek için street kolonunda boşluk olan yerleri ayır ve 1. ------sıradakini göster diyoruz.
-- Select the first word of the street value
SELECT SPLIT_PART(street, ' ',1) AS street_name, 
       count(*)
  FROM evanston311
 GROUP BY street_name
 ORDER BY count DESC
 LIMIT 20;

To calculate a proportion for an indicator variable, compute the sum() of the variable divided by the count() of the total number of observations.