Two questions raised during the interview

What are the differences between the types of data that can be stored in the database for text (string? What are the connection methods for the two Relational Tables in the data warehouse? What is the difference between each method ?, A interviewee came to the company for an interview a few days ago. He said the database was familiar and the boss threw out these questions. I think this question is too basic. It is just a piece of cake for a familiar database, but the result is indeed beyond my imagination that he answered the previous question wrong, the answer to the next question is incomplete.

Now let's take a look at these two questions. We hope that students with unclear concepts will seize the time to lay a solid foundation. Don't even answer the basic questions during the interview, that's not fun.

First question: Text (String)Which data types can be stored in the database? What are the differences between different types?

1. varchar: variable-length non-Unicode data. It can contain a maximum of 8,000 characters.

2. nvarchar: a variable-length Unicode data with a maximum length of 4,000 characters.

3. Char: Non-UNICODE character data with a fixed length. It can contain a maximum of 8,000 characters.

4. nchar: Unicode data of a fixed length. It can contain a maximum of 4,000 characters.

fixed length or variable length
the so-called fixed length is fixed, when the length of the input data does not reach the specified length, it is automatically filled with English spaces to make the length reach the corresponding length. If there is a var prefix, it indicates that the actual storage space is longer. For example, varchar and nvarchar variable length data are not filled with spaces. The exception is that text storage is also variable length.
Unicode or non-Unicode
in the database, only one byte is required for English characters, however, Chinese characters and many other non-English characters must be stored in two bytes. If both English and Chinese characters exist, the occupied space may lead to confusion, leading to garbled characters. Unicode Character Set is generated to solve the incompatibility problem of character sets. All its characters are expressed in two sections, that is, English characters are also expressed in two bytes. The prefix n indicates Unicode characters, such as nchar and nvarchar. These two types Use the Unicode Character Set.

Char, Varchar, Nchar, NvarcharFeature comparison
Char
It is very convenient for Char to store fixed-length data, and the indexing efficiency of char fields is high. For example, if char (10) is defined, no matter whether the data you store reaches 10 bytes, it takes up 10 bytes of space.
Varchar
It stores variable-length data, but the storage efficiency is not as high as Char. If the possible value of a field is not fixed, we only know that it cannot exceed 10 characters, defining it as varchar (10) is the most cost-effective. The actual length of the varchar type is the actual length of its value plus 1. Why "+ 1? This byte is used to save the actual length.
From the perspective of space, it is appropriate to use varchar; from the perspective of efficiency, char is suitable, and the key is to find a trade-off point based on the actual situation.
Text
Text stores variable-length non-Unicode data. The maximum length is 2 ^ 31-1 (2,147,483,647) characters.
Nchar, nvarchar, ntext
The three names are named N more than the first three ". Compared with Char and varchar, nchar and nvarchar can store up to 4000 characters, whether in English or Chinese. Char and varchar can store up to 8000 English letters and 4000 Chinese characters. It can be seen that when using nchar and nvarchar data types, you do not have to worry about whether the entered characters are English or Chinese characters, which is more convenient, but there is some loss in the amount of stored English hours.
Therefore, in general, if it contains Chinese characters, use nchar/nvarchar. If it contains English letters and numbers, use Char/varchar.

Select
There is no clear specification for use, which can be determined based on personal circumstances and the environment in use,
If the data size is very large and can be 100% characters long and only ANSI characters are saved, char
The length cannot be ANSI characters, so nchar, such as gender, is used.
For ultra-large data, suchArticleContent, using ntext
Other general nvarchar

The second question: how many connection methods are there for the two Relational Tables in the data warehouse? What is the difference between each method?

In relational databases, the links between Relational Tables mainly include internal connections and external connections.

Internal join: query the data corresponding to the data in the two tables.

External join: query the corresponding data based on a table (full join is based on multiple tables), including left join and right join.

For example, the following two Relational Tables are available: Student and grade.

Student table

No	Name
1	A
2	B
3	C
4	D

Grade table

No	Grade
1	90
2	98
3	95
5	100

Join inner join (query corresponding data that meets the conditions in two tables ).

Syntax: Select * from student inner join grade on student. No = Grade. No
Result:

Student. No	Student. Name	Grade. No	Grade. Grade
1	A	1	90
2	B	2	98
3	C	3	95

Left join: contains all data in the left table, and corresponding data that meets the conditions in the right table.

Syntax: Select * from student left join grade on student. No = Grade. No

Result:

Student. No	Student. Name	Grade. No	Grade. Grade
1	A	1	90
2	B	2	98
3	C	3	95
4	D	Null	Null

 

Right join: contains all the data in the right table, and the corresponding data that meets the conditions in the left table.

Syntax: Select * from student right join grade on student. No = Grade. No

Result:

Student. No	Student. Name	Grade. No	Grade. Grade
1	A	1	90
2	B	2	98
3	C	3	95
Null	Null	5	100

 

Full join: All data in the left and right tables is queried.

Syntax: Select * from student full join grade on student. No = Grade. No

Result:

Student. No	Student. Name	Grade. Grade
1	A	90
2	B	98
3	C	95
4	D	Null
1	A	90
2	B	98
3	C	95
5	Null	100

The above content comes from blog posts from online users. Please forgive me for your kindness and move it to your own article. Finally, you must lay the foundation well. During the interview, do not be flustered. If you do not feel vague with yourself, do not write it on your resume, or do not say it at will. If you do not know it, you will not be able to say it. Otherwise, you will not be able to understand it. It's hard to continue talking to your interviewer.