Sophie's Data Orchard

I created a tableau report with the data sets from the  imf.org  about the World Economic Outlook that was issued in Jan 2023.  Report Link

Tableau supports different types, here I make the summary and mark if the file type is supported by Tableau Desktop Professional or Tableau Desktop Public. File Type File Extension Definition Professional Edition Public Edition Workbooks  .twb Workbooks hold one or more worksheets, plus zero or more dashboards and stories. ✓ x Bookmarks  .tbm Bookmarks contain a single worksheet and are an easy way to quickly share your work. ✓ x Packaged Workbooks  .twbx A packaged workbook is a single zip file that contains a workbook along with any supporting local file data and background images. This format is the best way to package your work for sharing with others who don’t have access to the original data.  ✓ ✓ Extract  .hyper Extract files are a local copy of a subset or entire data set that you can use to share data with others, when you need to work offline, and improve performance.  ✓ x Data Source .tds Data source file...

We could set up the rules or constraints when we create a new table, like we don't want the input too short or any unwanted characters, so 'check constraints' could help us on it. I'm using the Customers table again and go to 'Design' window. Click the 'Manage Check Constraints' icon or select any item in the Column Name and right click, then select 'Check Constraints' from the menu. Then I have this window : I edit the constraints in this window: use len() function to make the content of LastName column must be greater than or equal to 2 bytes. Then rename the '(Name)' of Identity, also add 'Description' and press 'Add' to save the editing. Go back to the Customers table, select 'Edit Top 200 Rows'. Go to the last row of the Customers table, then enter the new content to it. Input 'Roger' as FirstName, 'W' as LastName. Then skip the Address, City and State. Press enter, it returns the pop-up window...

 Establish a default value might be useful, it's fun to learn something new.  Let's assume that I'm going to add new records to the table which belongs to one single category, and I want it generate automatically each time when I key in the new record. I'm going to use the Products table this time. Open the design window and select Category from the Column Name, then move down to the Column Properties. Type 'eBooks' to 'Default Value or Binding', SQL server will automatically turn it into '(N'ebooks')'. The  N character  means that this will use the Unicode character set and  the data type for Category is nvarchar(50) which supports the Unicode.  Now save this change and let's test if it works. Go to Object Explorer, select table name, right click and press 'Edit Top 200 Rows'. Add a new record, fill the info to each column of table product, skip the Category column for now. Then press enter, and Voila!  SQL server fills out t...

We know that  social security number, employee ID or passport number are all unique numbers.  SQL Server has the capability to let  the database engine automatically assign new unique identifies when new records are added. Let's do it. First, select the table 'Customers' from my SQL server 2022's database 'Red30Tech', then right click the table name and select Design. Add a new column named 'CustomerID', set Data Type as 'int', scroll down to the Column Properties, update (Is Identity) from Identity Specification as 'Yes', set Identity Seed as '1000' which means the very first row loaded into the table starts from 1000. Set Identity Increment as '1' which means it will increase 1 from 1000 for the next row. Now, drag the little triangle icon on the left of CustomerID to the top, click the Primary key icon, this column will be assigned as column with unique value for each row.  Save the design window and hover over to the t...

Washington State's Gasoline Price Hit the Historical High Recently, I heard the news that the gas price of Washington hit a historical high in June 2023 , even unseated California, though the Gas prices are still lower than the inflation-plagued summer months last year. The average price in King County exceeded $5 a gallon on Jun 21st per The Seattle Times . No wonder when I fill up my gas at Costco, the price is almost 5 dollars per gallon. Normally, I'm not price sensitive, but this time, it surprised me. So I grabbed the dataset from U.S. Energy Information Administration and work out a Power BI dashboard.  The first chart which is the Line and clustered Column Chart to show the gas price tendency of Washington State together with other 4 states as California, Texas, New York and Florida. The gas price I'm using is All Grades All Formulations Retail Gasoline Prices(Dollars per Gallon). It looks like California always has the highest gas price in the past 5 ...

  How To Implement Dense_Rank() In Excel Sheet RANK(), ROW_NUMBER() and DENSE_RANK() are three useful functions in SQL query for ranking purpose. RANK () returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row in question. RANK provides the same numeric value for ties For example: 1,2,2,4, 5. The numbers in ranking are not consecutive, there is gap if there are ties. ROW_NUMBER and RANK are similar. ROW_NUMBER numbers all rows sequentially (for example 1, 2, 3, 4, 5). DENSE_RANK() returns the rank of each row within a result set partition, with no gaps in the ranking values. The rank of a specific row is one plus the number of distinct rank values that come before that specific row. By running the T-SQL script in SQL server as below, I got the results that 2 and 4 were skipped due to the ties with RANK(); ROW_NUMBER() gave the consecutive numbers for each row within the r...