Posts

The statistics of competition results in Excel

Image
Recently, my son participated in a local academic competition alongside other kids of same grade from various schools. During the group contest, held in the same classroom, his team which has 4 kids from same class felt confident as they competed against other school teams. However, to their surprise, their school didn’t secure a spot in the top eight. The team they considered less capable outperformed them. As a data enthusiast mom, I’m eager to delve into the reasons behind this unexpected outcome. I'm going to divide the observants into 4 groups:  Group1: participants from school A, Group2: participants from school B,  Group3: participants from school C, Group4: All the participants. First, I'm going to study the Descriptive Statistics of each group's scores.  Descriptive statistics refer to the analysis, summary, and presentation of findings related to a data set derived from a sample or the entire population. These statistics help us understand and organize char...
Post a Comment
Read more

Schema and Security Control in SQL server

Image
dbo is always seen as a SQL server table's schema. dbo stands for database owner. The dbo schema is the default schema of every database. When user created with  CREATE USER   T-SQL command have dbo as their default schema. The dbo schema is owned by the dbo user account.  SQL server also provides other pre-defined schemas that have the same names as the built-in db users and roles: db_accessadmin, db_backupoperator, db_datareader, db_datawriter, db_ddladmin, db_denydatareader, db_denydatawriter, db_owner, db_securityadmin . These exist for backward compatibility. The recommendation is to not use them for user objects.  User can drop the schemas that have the same names as the fixed database roles - unless they're already in use, in which case the drop-command will simply return an error and block the drop of the used schema. For example: IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'db_accessadmin') DROP SCHEMA [db_accessadmin] GO If user drops the...
Post a Comment
Read more

World Economic Outlook Update Jan 2023 in Tableau

Image
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
Post a Comment
Read more

Tableau's File Types

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...
Post a Comment
Read more

SQL Server 2022 : Check constraints and unique constraints

Image
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...
Post a Comment
Read more

SQL Server 2022 : Establish a default value and a time stamp

Image
 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...
Post a Comment
Read more

SQL Server 2022 : Automatically Assign Record Identities

Image
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...
Post a Comment
Read more