1) Databases are used to store different kinds of data such as names, currency, text, graphics, etc. Determining the appropriate data type for each attribute is important for database performance, storage size, and conducting accurate analysis on databases. As with other programming languages, SQL supports many data types. However, you will mostly use two categories while writing SQL queries: Characters, and numbers.

Consider the following standard SQL data types:

• Characters
• char
• varchar
•  
• Numbers
• integer
• decimal
• float

Different data types could be used for the same attribute. For example, both char and varchar could be used to store "names" in a database. Examine the attributes for the Characters and Numbers data types.

Using specific examples, compare data types for each category. For example, why you should choose char rather than varchar or opposite

 

2.What are the implementation problems of Dates and Times data types in database? Discuss solutions for these problems.

1. For Characters: VARCHAR is for parameters with variable length, meaning the length of the value contain in the variable can be shorter than the declared size. On the other hand, CHAR has fixed length. Putting on example, if a variable X is declared as varchar with size 2 bytes may assign either 'AA' or 'A'. If the variable X is declared as char and assigned only a value of 'A', the value will be padded with an additional space character to fill up all the 2 bytes. Having said, it is advisable to use CHAR for sure fixed-length variables (such as Flags fields) and VARCHAR for more length-varying variables (such as Name field).

For Numbers: INTEGER is considered a whole number, having no decimal point. Float and Decimal both have decimal point. The difference is that FLOAT cannot accurately represent all numbers in decimal format, unlike DECIMAL which can represent number within the precision of the decimal format. Integers are used for whole-numbered values, decimal for exact values (such as money) and float for approximate values (such as scientific measurements). Example, a variable Y is declared as integer and assigned with a value of '10'. If the variable is changed to decimal and assigned with value of '10.10', it is a precise value up to the last digit. If the variable is change to float and assigned with the value of '10.10', the value is just approximate meaning other numbers may still be present after the last digit and simply rounded off.

2. For Date and Time Implementation, one problem arose with DATETIME data type. In actual practice, the data type is used to store timestamps. The format of DATETIME is "yyyy'-'MM'-'dd'T'HH':'mm':'ss'.'fffffffK". The issue with this is it has only 3.33 accuracy leading to round values. This means that there might be an issue when it comes to copying the value to a variable with a similar data type, and end up rounding of the last millisecond. So when we do a search and compare values, we might end up not finding a match. The solution is to use DATETIME2 instead which has a precision lower than seconds, to keep the last millisecond accurate.