본문 바로가기

Study Note/SQL12

With CTE I've been solving CodeKata questions and have already completed up to question number 76. While solving these questions, I wanted to practice using WITH CTE. Mastering WITH CTE seems like it will allow me to simplify complex code in various ways.  What is a CTE?A CTE (Common Table Expression) is a temporary result set defined within the execution scope of a single SQL statement. It improves the.. 2024. 5. 20.
Union & Union All UNION: The `UNION` operator in SQL is used to combine the results of two or more SELECT statements into a single result set. It removes duplicate rows from the result set, so each row appears only once.UNION ALL: On the other hand, the `UNION ALL` operator also combines the results of two or more SELECT statements into a single result set, but it includes all rows from all SELECT statements, inc.. 2024. 5. 1.
Difference between COALESCE and IFNULL COALESCE COALESCE function in SQL is used to return the first non-NULL value among its arguments. It accepts multiple arguments and returns the first non-NULL value encountered. If all arguments are NULL, `COALESCE` returns NULL. Example: Suppose we have a table named `sales` with columns `product_id`, `sale_date`, and `sale_amount`. Some records in the `sale_amount` column might have NULL value.. 2024. 4. 23.
How to add localhost at DBeaver (MY SQL) An error occurred during the process of converting text to numbers in DBeaver. When importing a CSV file into DBeaver, it failed to recognize text as numbers, resulting in this error. T o resolve this, MySQL Community was installed and localhost was configured. Now, I will explain how to set up localhost after installing MySQL Community. This is my first screen and I will create 'locahost_v2' Yo.. 2024. 4. 16.
Having The HAVING clause in SQL is used to filter the result set returned by a GROUP BY clause.  It allows you to apply conditions to groups of rows rather than individual rows.  This is particularly useful when you want to filter groups based on aggregate functions, such as SUM, AVG, MIN, MAX, etc. Here are some examples:  Using SUM with HAVING:SELECT department, SUM(sales) AS total_salesFROM sales_ta.. 2024. 4. 11.
7. Pivot, Window function (Rank & sum), Date Pivot Pivot is commonly used in Excel, and I've learned that it can also be applied in SQL. Through the following example, I was able to create the result values in a pivot table format. select restaurant_name, max(if(hh='15', cnt_order, 0)) "15", max(if(hh='16', cnt_order, 0)) "16", max(if(hh='17', cnt_order, 0)) "17", max(if(hh='18', cnt_order, 0)) "18", max(if(hh='19', cnt_order, 0)) "19", ma.. 2024. 3. 22.

티스토리툴바