DEV Community: Markme Dev

OLAP: CUBE, ROLLUP, AND GROUPING SETS

Markme Dev — Thu, 09 May 2024 13:23:00 +0000

In the realm of data analysis and business intelligence, OLAP (Online Analytical Processing) stands as a cornerstone technology, the facilitating multidimensional analysis of vast datasets. OLAP enables users to gain insights from data by analyzing it across multiple dimensions, providing a comprehensive view of business metrics and performance.

So by now we are going to discuss the three key features of OLAP which are the CUBE, ROLLUP, AND GROUPING SETS operators. That will play a pivotal roles in summarizing and aggregating data in various way.

Let's first discuss the first one which is:

CUBE

The cube is a powerful tool in OLAP, it can generate all possible combinations of dimension and their aggregate. It provides a comprehensive multidimensional view of the data by computing subtotals and totals for every combination of dimensions, enabling users to explore data from different perspectives and levels of granularity.

Okay it's kind of technical on first but it will surely you will understand it when we are going to use it in query.

Let's say we want to know the count of each person who are renting in a country, and what gender they are.

SELECT country, 
       gender,
       COUNT(*)
FROM rentals
GROUP BY CUBE(country, gender) -- This one will group them by its country and gender.

So the output of that will be this

| country    | gender   | COUNT(*) |
|------------|----------|----------|
| USA        | Male     | 100      |
| USA        | Female   | 150      |
| UK         | Male     | 120      |
| UK         | Female   | 130      |
| NULL       | Male     | 220      |  <-- Subtotal for Male across all countries
| NULL       | Female   | 280      |  <-- Subtotal for Female across all countries
| USA        | NULL     | 250      |  <-- Subtotal for USA across all genders
| UK         | NULL     | 250      |  <-- Subtotal for UK across all genders
| NULL       | NULL     | 500      |  <-- Grand total

See each row represents a unique combination of "country" and "gender". That's the uses of CUBE it will generate all possible combination base on what you aggregate. we have subtotal for each category and the grandtotal.

ROLLUP

ROLLUP operator is used to generate hierarchical roll-up data. It computes subtotals for a predefined hierarchy of dimensions, typically moving from the most detailed level of granularity to the least detailed. It generates subtotals for each level of the hierarchy, producing a result set that includes subtotals for each combination of the specified columns, as well as grand totals. Its like more organize then lets make an example again.

SELECT 
    country, 
    gender,
    COUNT(*) AS rental_count
FROM 
    rentals
GROUP BY 
    ROLLUP (country, gender);

Then the output will be this

| country  | gender  | rental_count |
|----------|---------|--------------|
| NULL     | NULL    | 8            |  <-- Grand total
| NULL     | Male    | 3            |  <-- Subtotal for Male across all countries
| NULL     | Female  | 5            |  <-- Subtotal for Female across all countries
| USA      | NULL    | 3            |  <-- Subtotal for USA across all genders
| USA      | Male    | 2            |
| USA      | Female  | 1            |
| UK       | NULL    | 3            |  <-- Subtotal for UK across all genders
| UK       | Male    | 1            |
| UK       | Female  | 2            |

you see its organize after the count of each category it will print the sub total of that then next will be the grand total. You may notice that the subtotals for each category often appear adjacent to the corresponding category rows. This is because the ROLLUP operator is designed to provide intuitive and readable output, placing subtotals in proximity to the data they summarize.

GROUPING SETS

We use GROUPING SETS operator to group the groupings by the arguments we put inside its like grouping but the difference is that we can put as many group inside of that like
GROUP BY GROUPING SET ((country, gender), (country), (gender)) We can group the data into combination of country and gender in the first argument and in second arguments we want to also group it in country only and so in the third argument.

It's like we are combining 3 groups in 1 query which is first one is (country, gender), then the second is (country), then the third is (gender). so if you want to achieve the same result of that without using the grouping sets you need 3 query for that.

Let's proceed to example of this grouping sets so we can understand it.

SELECT 
    country, 
    gender,
    COUNT(*) AS rental_count
FROM 
    rentals
GROUP BY 
    GROUPING SETS (country, gender, ());

Then the output would be

| country  | gender  | rental_count |
|----------|---------|--------------|
| NULL     | NULL    | 8            |  <-- Grand total
| NULL     | Male    | 3            |  <-- Subtotal for Male across all countries
| NULL     | Female  | 5            |  <-- Subtotal for Female across all countries
| USA      | NULL    | 3            |  <-- Subtotal for USA across all genders
| USA      | Male    | 2            |
| USA      | Female  | 1            |
| UK       | NULL    | 3            |  <-- Subtotal for UK across all genders
| UK       | Male    | 1            |
| UK       | Female  | 2            |

So the explanation here is each unique combination of country, and gender is shown in the table then you are wondering why there is () then that's a empty bracket meaning it will total all the count in the output but if we remove that it will only shown the sub total of each category.

You are wondering why the CUBE and GROUPING SETS have a similarity then you're right but the cube is getting all the possible combination but the grouping sets is we can group as many as we want in one query.

I know guys that's a lot to learn but if you keep using this in your query over the time you will understand each of this. Thanks a lot :)

SQL's LIKE, ILIKE, and NOT LIKE: Beginner's Breakdown

Markme Dev — Tue, 07 May 2024 13:21:59 +0000

Are you ready uncovering the power of SQL matching patterns?

In the realm of database querying, grasping the matching pattern will make our query more accurate. It's akin to having a finely tuned radar, enabling us to pinpoint exactly what we're looking for amidst vast seas of data.

In this blog you will learn how to use LIKE, ILIKE, and NOT LIKE. By understanding this this matching pattern we can transform our queries for searching into more precise tools, and more accurate extraction of what data we want to fetch.

Without further ado lets start now the familarization on this pattern.

Understanding LIKE and ILIKE: What Sets Them Apart?

At first glance, **LIKE **and **ILIKE **may seem interchangeable, both serving the purpose of pattern matching within SQL queries. However, a subtle yet significant distinction exists between these two operators.

LIKE : This matching pattern allow us to search within text data. It operates on a case-sensitive basis. meaning it distinguishes between uppercase and lowerchase characters. for example in query

SELECT * FROM Product WHERE name LIKE '%APPLE%';

The output will be all the name with APPLE word on it. its case sensitive so it will only select all the name where the APPLE is capital not included the small one.

ILIKE : This matching pattern allow us to search also within text data. but this one is case-insensitive This operator performs pattern matching without regard to case sensitivity, making it a versatile tool for scenarios where we want our search to encompass all possible variations of letter casing. for example in query

SELECT * FROM Product WHERE name ILIKE '%apple%'

The output will be all the name with apple word on it. with ILIKE would match not only 'apple' but also 'Apple' and 'APPLE.

By understanding this subtle difference, we can wield LIKE and ILIKE with precision, So let's take advantage of this of how we gonna use it.

The next is NOT LIKE which is the inverse of LIKE and ILIKE that excluding the rows that match the specified pattern. for example in query

SELECT * FROM product WHERE name NOT LIKE '%apple%'

It will exclude all the name with apple word on it. this is case-sensitive meaning it will only remove the 'apple' but not the 'Apple' and 'APPLE' word on it.

Very basic right? now that you know the LIKE, ILIKE and NOT LIKE matching pattern why not using it in your matching pattern query? to make your extracting data more precision than before?

That's it, and thanks for reading if you've made it this far!

Simplified SQL: Harnessing the Power of Aliases

Markme Dev — Fri, 03 May 2024 12:26:01 +0000

Hello there its me again,

So by now, you know what this is all about. But let me clarify once again that we are now going to discuss SQL aliasing for better readability and cleaner queries. It's helpful to use aliases if the names of your tables or columns are too long. We can use aliases for column names, tables, subqueries, and so on, but we'll focus on table and column names since this is more basic and easier to understand. and it also good to use alias if you are going to combining column.

Here's the example of query that no alias.

As you can see, there is a '?' column. This signifies that we combined the tables, but the SQL engine couldn't determine a name for it, hence the default '?column?' in the SQL column.

This one is the revise version

We add an alias to the combined column name so that it can be given a column name. This is just one example of how aliases are used. We use aliases in table names, subqueries, etc., as I mentioned earlier. However, we're not discussing those uses now because we're focusing on the basics. We may explore those topics later, but it's important to grasp the fundamentals first.

Now that you know what is alias is try to apply it in your query and see the improvement. I hope you learn from this blog , Thank you so much again and good day.

The data that I'm using here is just dummy data sourced from DataCamp, so there's no need to worry, guys. MARKME

SQL DATA TYPES

Markme Dev — Thu, 02 May 2024 13:16:26 +0000

Hello Everyone,

This is my Day 3 talking about SQL haha if you haven't read my other blogs you can view it in my profile then please read it also and dont forget to like it.

Now let's go back to our topic for today which is SQL DATA TYPES if you saw my previous post we discuss about queries and some theory about SQL now we're going to focus on sql data types. so first let's define what is data types.

SQL data types define the type of data that can be stored in the database table's columns. Each column have a data type, which determines the kind of values that can be inserted into that column. It's like were telling each column what only data we are accepting or to store in it.

Now that we have a glimpse of what is sql data types is now let's discuss the different types of data types.

NUMERIC TYPES

INTEGER/INT - Represents integer number whole numbers only no decimal points. there are range that integer are accepting it's like -2,147,483,648 to 2,147,483,647 number only. if you want to store big numbers you need to use BIGINT. if small numbers SMALLINT.

DECIMAL(n, p) - Represents fixed-points numbers with exact precision so if you're data have decimal you need to use decimal types to get also the decimal points of the data. if you use integer type the decimal point will not show.

CHARACTER STRING TYPES

CHAR: Fixed-length character strings like if you declare your column type is char(5) it will only limit it accepting 5 characters only. CHAR can be useful when you know that the length of the data will always be the same, as it can help save storage space and ensure consistent data length.

VARCHAR: Variable-length character strings. this type is good to store texts the maximum length of this varchar is 255 only.

TEXT: Variable-length character strings with larger maximum length. this text is same in varchar but the text type can occupy more length than varchar.

DATE AND TIME TYPES

DATE: Represents date values in the format 'YYYY-MM-DD'. Specifically for date only.
TIME: Represents time values in the format 'HH:MM:SS'. Opposite of the date.
DATETIME/TIMESTAMP: Represents date and time values in the format 'YYYY-MM-DD HH:MM:SS'. It's like combination of the two above.

Boolean Type:

BOOLEAN/BOOL: Represents boolean values (true/false).

There are some types commonly found when viewing a database table schema. If you want to dive deeper into the data types, you can find more information in the documentation of your chosen database server, whether it's MySQL or PostgreSQL.

If you find this blog useful please drop a like and if you have something like concern or feedback you can free to comment on this post and we can discuss it. Thank you so much :)

MARKME

SQL AGGREGATE FUNCTIONS

Markme Dev — Wed, 01 May 2024 12:45:36 +0000

DAY 2
Hello Everyone 👋,

It's me again markme today our topic is aggregate functions in SQL and we're going to walkthrough to some of it such as

AVG()
SUM()
COUNT()
MIN()
MAX()

and then we're going to use that into query after we discuss it one by one, so let's get start.

## AVG() FUNCTION

AVG() or average function is getting the average of all records in a selected column.take note that avg fuction is only accepting numbers not string and varchar.

Then this is how you use AVG() in a query

SELECT AVG(salary) FROM users

Explanation: it will get the average salary of all users in the table that's the output.

## SUM() FUNCTION

SUM() sum function is used to calculate the sum of values in a column.

Then this is how you use SUM() in a query

SELECT SUM(salary) FROM users;

Explanation: In essence, the query fetches the total sum of all salaries stored in the "salary" column of the "users".

## COUNT() FUNCTION

COUNT() count function is used to count the number of rows that meet a specified condition.

Then this is how you use COUNT() in a query

SELECT COUNT(salary) FROM users;

Explanation: In this sample query we're getting the total rows where the salary value is not null or absence. like if there is no value it will not count as 1 it will go to the next row then check the value.

## MIN() FUNCTION

MIN() function is used to retrieve the minimum value from a column.

Then this is how you use MIN() in a query

SELECT MIN(salary) FROM users;

Explanation: In this query it will get the minimum value in the salary column it will check all the row who's the lowest value then show it in the output.

## MAX() FUNCTION

MAX() function is used to retrieve the maximum value from a column.

Then this is how you use MAX() in a query

SELECT MAX(salary) FROM users;

Explanation: In this query it will get the maximum value in the salary column it will check all the row who's the highest value then show it in the output.

That's the some example of aggregate function when we are going to apply it in sql. its good to use this aggregate and combine it with group clause.

Now that you got a glimpse of the aggregate function try to apply that also in your end and explore much more deeper.

"Consistency is the key to mastery."

If you guys have comments or concern about this topic please comment down below. Thanks for reading.

MARKME

INTRODUCTION TO SQL

Markme Dev — Tue, 30 Apr 2024 13:07:25 +0000

Hello there 👋 ,

This is my DAY 1 creating blogs about sql and my journey in the world of data field. without further let's get started.

NOTE: All the topics that im gonna post here is base on what i've understanding and i've learned if you guys thinking that i'm lacking on some explanation in a certain topic feel free to comment down below.

Lesson content for today are:

What is SQL
SQL Coding Standards and Best Practices
Order of Execution of SQL

Now let's go with the first one.

WHAT IS SQL
SQL is a Structured Query Language. It's a programming language designed for manipulating relational database. So its totally different on NOSQL. it's also allow users to interact with databases by performing various operation such as retrieving data, save, edit, delete, in other term CRUD.

It's also good choice to choose this language when you are working with data role and you want to manipulate the data.

The SQL have many flavors or version but we are gonna discuss that in the other day.

Now that we know what is sql let's make an example how to write an sql.

Example
SELECT column_name FROM table_name

That's the example query of SQL
column_name: in this part where we gonna put the column name of the table that we want to select.
table_name: in this part is where we gonna call what's the table name we want to query.

It's like
SELECT id, name FROM Product

if you want to put more than 1 column name you need to put comma after the table name that you want to select.

Now that we are good to go and have some background knowledge what is sql. Were gonna discuss now the SQL Coding Standards and Best Practices

SQL Coding Standards and Best Practices
Now let's talk about SQL standards And Best Practices

Here's some best practices and standards

*Use consistent indention for readability *- meaning that we need to use indention when we're creating a SQL query to make more readable not one liner that if the query is long its hard to read it right?.

Example without indention:
select id, name, address, username, password, grade from user_info where status = 1;

Example with indention

select id
       name
       address
       username
       address
       password
       grade 
from user_info
where status = 1;

As you can see above it's good to use indention to make more the sql query easy to read and understand at glance compare to the first one without proper indention.

Capitalize SQL keywords for clarity - meaning that we need to capitalize the sql keywords to make it more clarity that that's a keyword and not a column name or what.

Example without Capitalize keywords:

select id
       name
       address
       username
       address
       password
       grade 
from user_info
where status = 1

Example with Capitalize keywords

SELECT id
       name
       address
       username
       address
       password
       grade 
FROM user_info
WHERE status = 1;

See the difference its good to use capitalization with combination of indention to make more the sql query looks readable.

Comments and Documentation - add comments in the sql query can make more the query understandable and well documented.

Example

SELECT id
       name  -- Name of the person
       address
       username
       address
       password
       grade 
FROM user_info
WHERE status = 1;  -- only fetch the user info with status of 1 or active

the -- is the comment for the sql language.

That are the some sample of best practices and standard coding when you are wring an SQL query if you what to know more or explore much deeper than this you can visit it in the SQL documentation.

Now that we know some of the standard and best practices in writing an sql query lets proceed now in the last topic which is:

Order of Execution of SQL

Order of execution refers to the sequence in which different part of an SQL query are processed by the database engine. Understanding this sequence can be crucial when writing efficient queries and comprehending how the database processes and returns results.

SELECT age, 
       COUNT(*) AS total_user
FROM user_account
WHERE status = 1
GROUP BY age,
ORDER BY total_user;

FROM: The query starts by identifying the table "user_account" from which data will be retrieved.
WHERE: The WHERE clause filters the rows from the "user_account" table where the status is equal to 1.
GROUP BY: The query then groups the filtered rows by the "age" column. This means that the rows with the same age will be aggregated together.
SELECT: Next, the SELECT clause is executed to select the "age" column and the count of rows for each age group. The COUNT(*) function calculates the total number of rows in each age group and aliases it as "total_user".
ORDER BY: Finally, the result set is sorted based on the "total_user" column in ascending order. This means that age groups with fewer users will appear first, and those with more users will appear later in the result set.

Now that you know all of this and have a glimpse of it you can more explore it on the documentation and try to apply it in your end.

If you have feedback or suggestion feel free to comment it below. see you in the next blog. THANKS

SQL DATA MANIPULATION

Markme Dev — Mon, 29 Apr 2024 07:38:33 +0000

Hi there 👋

You're wandering what's this all about so first let me enlightened you. i will be creating a blog everyday from Day 1 to Day 100 to share to you guys what i've learned for that day. this journey will focus on data specifically in sql so let's start.

Topic for today are:

SQL LEFT FUNCTION For efficient Data Extraction.
SUBSTRING or SUBSTR
trunc function

So now you're also wandering again that i am not start in the basics of the sql like the background or syntax i jump from the data manipulations so we will go there. This is Day 0 its like a cannon episode haha. enough with the chichat let's start.

SQL LEFT FUNCTION
left function is extracting character from a column there's a two argument that needed in performing an left function.

LEFT(column_name, number)

The first argument column_name specifies the column from which you want to extract characters.

The second argument which is number determines the number of characters you want to extract from the left side of the specified column's value.

For Example:
We have Product table
| id | name | description |
|----|------------|--------------------------|
| 1 | Product A | Description of Product A |
| 2 | Product B | Description of Product B |
| 3 | Product C | Description of Product C |

So if we want to limit the output of the description because its too long then we can use LEFT function to limit the characters of the description column. Lets limit the the fetch description in 5 characters only. this is the sample query for that.

SELECT name, LEFT(description, 5) AS description FROM Product

then the output of that is this
| name | description |
|------------|-------------|
| Product A | Descr |
| Product B | Descr |
| Product C | Descr |

you see the description value is cut in 5 characters so thats the uses of left function, We also have RIGHT its same as left but right counting element is start in the right value of the column name.

SUBSTRING() OR SUBSTR()
This function is same on the left function but the substring requires 3 argument.

SUBSTRING(column name, start(n), end(n));

column name: Column name or string that you want to extract.
start(n): The starting position from which you want to begin extracting characters.
end(n): The length of the substring you want to extract

It's easy right?

now let's make a query, Here's the sample Product Table.

id	name	description
1	Product A	Description of Product A
2	Product B	Description of Product B
3	Product C	Description of Product C

SELECT name, SUBSTRING(description, 11, 5) AS description FROM Product;

THE OUTPUT:
| name | description |
|------------|------------- |
| Product A | of Pr |
| Product B | of Pr |
| Product C | of Pr |

Explanation:
We start the extraction in the 11 char of the description column then the length of the extraction is 5 so we count five starting from element char we also included the spaces.

TRUNC()
The TRUNC() function is used to truncate a date or a number to specific unit or measure. its something similar for ROUND but the ROUND is rounding a number in TRUNC we're just eliminating the number or decimal.

TRUNC function accept two arguments first is the column name containing number or dates, but the second argument is like optional so we can exclude or includes that its depends on the situation.

Sample query
This is the 3 different example of trunc() function

TRUNC(salary) -- removing the decimal for salary column
ex: 123.00 -> 123

TRUNC(salary, 2) -- 123.18730 -> 100.18700

TRUNC(salary, -2) -- 123.18730 -> 100.00000

Utilizing the TRUNC() function to group numbers into specific ranges like thousands or hundreds can greatly facilitate data analysis and interpretation.

Nice, now that you learned how to use the LEFT(), SUBSTRING(), TRUNC try to apply it in your project and explore it a little bit more advance.

If you guys have questions or clarifications regarding on my blog post please do a comment and share your thoughts about it and then we can discuss it.

If you enjoy this type of content and would like to join me on my journey of personal growth in SQL, please hit the like button

*Thank you so much and have a nice day! *