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
-- 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;
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;
-- 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');
---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
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.
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,...)
--- 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.