SQL Basics

🌮 Learning SQL language basics

**🔑 Tool: PostgreSQL 🔑**

-- Task 1. Get TOP5 the most expensive units in our products table.

SELECT name AS product_name, price AS product_price
FROM products
ORDER BY price DESC
LIMIT 5

Output:

-- Task 2. Get unit in our products table with the longest product name.

SELECT name AS product_name, LENGTH(name) AS name_length, price AS product_price
FROM products
ORDER BY name_length DESC
LIMIT 1

Output:

-- Task 3. Use functions UPPER & SPLIT_PART with name column and get the first word from name column.

SELECT name, UPPER(SPLIT_PART(name, ' ', 1)) AS first_word, price
FROM products

Output:

-- Task 4. Changing data type from INT to VARCHAR with two diff ways to do it.

SELECT name, price, price :: VARCHAR AS price_char
FROM products

SELECT name, price, CAST(price AS VARCHAR) AS price_char
FROM products

Output:

-- Task 5. Using CONCAT function and converting data type into DATE.

SELECT CONCAT('Заказ № ', order_id, ' создан ', DATE(creation_time)) AS order_info
FROM orders
LIMIT 200

Output:

-- Task 6. Filter the table and get only year of couriers from date_of_birth column using DATE_PART().

SELECT courier_id, DATE_PART('year', birth_date) AS birth_year
FROM couriers
ORDER BY birth_year DESC, courier_id ASC

Output:

-- Task 7. USING COALESCE() function to populate empty rows.

SELECT courier_id, COALESCE(DATE_PART('year', birth_date)::VARCHAR, 'unknown') AS birth_year
FROM couriers
ORDER BY birth_year DESC, courier_id ASC

Output:

-- Task 8. Trying logical_expressions.

SELECT product_id, name, price AS old_price, 

COALESCE((CASE 
WHEN price > 100 AND name != 'икра' THEN price * 1.05 
END), price)  AS  new_price

FROM products

ORDER BY new_price DESC, product_id

Output:

-- Task 9. Making some TAX calculation with in our data.

SELECT product_id, name, price, ROUND((price / 1.2) * 0.2, 2) AS tax, ROUND(price - ((price / 1.2) * 0.2), 2) AS price_before_tax
FROM products
ORDER BY price_before_tax DESC, product_id

Output: