Skip to main content

Posts

Showing posts from February, 2021

Solving Data Type Conflicts in Power BI and SQL

Whether we like it or not, error messages are part of our lives. The other day, I faced one of those little horrid yellow messages. For me, it's impossible to stay chill when I see that message. For a second, I feel alarmed and panicked. Let me show you what I'm talking about. The first column is the customer code, quite self-explanatory. The Registration Date column shows the date when the customer registered to my shop. The First Sale Date column shows the date when the customer bought something for the first time. And this is what I'm trying to see in the Sales Day column: If Registration Date and First Sale Date are the same, meaning customer bought something at the same day customer registered to my shop, print  "First Day", otherwise show the data of First Sale Date column. But computer says no! It says "Expressions that yield variant data-type cannot be used to define calculated columns." If I need to translate it in simplified English, it means: ...

Creating Relational Database In SQL

Recently I have created a relational DB in SQL and decided to share how I've done it. It's a snowflake design. Creating a relational DB is not difficult, but it's very difficult to change things after creating them. That's why we need to be very neat in the beginning, First I'd like to show you how it looks. For representation, I've used EXCEL so that I can visualize the tables by using color codes. Every color represents one relation. While ID columns are primary keys ...ID columns are foreign keys at other tables. And below you can see how the tables look after entering data. My first rule while I create a database is adding an ID column for every table and making it primary key and identity(1,1) so that it will increase automatically. The first thing we need for a DB table is a column that defines every row uniquely.  The second thing I try not to do is using dedicated words like 'PASSWORD' or 'USERNAME' for column names. I simply add undersco...

Creating Fake Data In SQL

This is the first time I've created fake data using SQL, and some steps were quite challenging. That's why I've decided to write a post about it. The mission is to create a table with fake data, using random values. Below is the empty Customers table that I need to fill. Of course, other than the ID column, which is the primary key. First of all, I need names with gender info and surnames to fill the table. But don't get me wrong, I'm not going to insert those names directly to this table, because I need names and surnames to match randomly. For that reason, I've downloaded a name list with genders and a surname list from  www.randomlists.com . I also needed a list of USA states. I created one more table for that. This is how those tables look. In Names table I have 300 female names and 300 male names. In Surnames Table I have 600 surnames. You might be wondering why I put them to different tables. They have to be at different tables because if they are at the s...

Statistics In SQL

If you know nothing about Statistics, you've come to the right place. I'm not going to bore you with long technical definitions that make no sense to people who don't know anything about it. I'm going to show everything with examples and visuals. You just sit back and relax. I have a Customers table. When I search for 'Abigail Parks' I get 5 records. And I have 3 indexes for this table. First is the clustered index, which is created automatically for the primary key column. The second and third ones are non-clustered indexes, one for the NameSurname column and the other one is for the Birthdate column. Now I'm wondering which index SQL used for my query. Was it able to find NameSurname index? When I click on Display Estimated Execution Plan button I get this: SQL was able to find the right index. In my next query, I try to find how many customers were born on the same day with the first 'Abigail Parks' on my list, which is 1973-06-05. I have 512 peop...