Operational data: SQL Basics

Data Chapter Content

SQL Introduction
Using the SELECT statement to fetch data from a table
Create a new table
Field Properties
Add data to a table
deleting and modifying tables


To build an interactive site, you need to use a database to store information from visitors. For example, if you want to build a site for a career introduction service, you need to store information such as a resume, a job that interests you, and so on. Creating dynamic leaf blades also requires a database, and if you want to show the best work that meets the needs of your visitors, you need to get the job information out of the database. You will find that in many cases the database needs to be used.
In this chapter, you will learn how to use Structured Query Language (SQL)) to manipulate databases. The SQL language is the standard language for the database. In active Sever Pages, you need to use the SQL language whenever you want to access a database. Therefore, it is very important to have a good command of SQL for ASP programming.

Attention:
You can read "SQL" as a "sequel", or you can read it as a s-q-l by the pronunciation of a single letter. Both sounds are correct, with a large number of supporters in each of the pronunciations. In this book, Think of "SQL" read as "sequel".

Through this chapter, you will understand how to implement database queries using SQL, you will learn how to use this query to take out information from the datasheet, and finally, you will learn how to design and build your own database.

Attention:
With the introduction to SQL in the following chapters, you will have enough knowledge of SQL to effectively use active Sever Pages. However, SQL is a complex language and the book cannot include all of its details. To fully master the SQL language, you need to learn to use SQL in Microsoft SQL Sever. You can go to a nearby bookstore to buy a copy of Microsoft SQL Sever 6.5.

SQL Introduction:
This book assumes that you are a database that operates Microsoft SQL Sever in SQL. You can also use SQL to manipulate many other types of databases. SQL is the standard language for manipulating databases. (In fact, there is a specific ANSI standard on the SQL language)

Attention:
Do not attempt to use Microsoft Access in place of Microsoft SQL Sever on your site. SQL Sever can serve many users at the same time, if you want your site to have a high rate of access, MS access is not competent.

Before you learn the specifics of SQL, you need to understand its two main features. One feature is easy to master, the other is a bit difficult to master.
The first feature is that all the data in the SQL database is stored in the table. A table is made up of rows and columns. For example, the following simple table includes name and e-mail address:
Name Email Address
................................................................
Bill Gates billg@microsoft.com
President Clinton president@whitehouse.com
Stephen Walther swalther@somewhere.com
This table has two columns (also called fields, domain):name, and email address.) There are three rows, and each row contains a set of data. The data in a row is grouped together and called a record.
Whenever you add new data to a table, you add a new record. A data table can have dozens of records, or thousands of or billions of records. Although you may never need to store 1 billion email addresses, it's always good to know you can do this, and maybe someday you'll have to.
Your database is likely to contain dozens of of tables, and all the information stored in your database is stored in these tables. When you consider how to store information in a database, you should consider how to store it in a table.
The second feature of SQL is somewhat difficult to grasp. This language is designed to not allow you to retrieve records in a particular order, because doing so reduces the efficiency of SQL sever fetching records. With SQL, you can only read records by query criteria.
When you think about how to take records out of a table, you naturally think of reading them by record location. For example, you might try to scan a record by a loop, to pick a specific record. When using SQL, you have to train yourself not to have this idea.
If you want to pick all the names of the "Bill Gates" record, if you use a traditional programming language, you might construct a loop that looks at the records in the table, and see if the name field is "Bill Gates."
This method of selecting records is feasible, but not efficient. With SQL, you just say, "Select all the name fields equal to the records of Bill Gates," and SQL will pick up all the records that match your criteria. SQL determines the best way to implement a query.
Build you want to take out the top 10 records in the table. Using the traditional programming language, you can do a loop, take out the first 10 records and end the loop. However, it is not possible to use standard SQL queries. From a SQL perspective, the concept of the first 10 records does not exist in a table.
At first, you will be frustrated when you know you can't use SQL to implement something that you feel should be able to achieve. You may be banging your head against the wall and even trying to write malicious letters to the designers of SQL. But then you will realize that this feature of SQL is not just a limitation, but rather a strength. Because SQL does not read records based on location, it can read records quickly.
To sum up, SQL has two features: all data is stored in tables, and in the case of SQL, there is no order of records in the table. In the next section, you'll learn how to use SQL to select Special records from a table.

Use SQL to fetch records from a table.
One of the main functions of SQL is to implement database queries. If you are familiar with the Internet engine, then you are already familiar with the query. You use queries to obtain information that meets certain criteria. For example, if you want to find all the sites with ASP information, you can connect to Yahoo! and perform a search for active Sever pages. After you enter this query, you will receive a list of all sites whose descriptions contain search expressions.
Most Internet engines allow logical queries. In a logical query, you can include special operators such as and, or, and not, and you use these operators to select specific records. For example, you can use and to limit query results. If you perform a search on active Sever Pages and SQL. You will get a record of the active Sever Pages and SQL that are included in the description. When you need to limit the results of a query, you can use and.
If you need to extend the results of the query, you can use the logical operator or. For example, if you perform a search that includes sites with active Sever Pages or SQL in its description, the list you receive will include all sites whose descriptions contain two expressions or either of them.
If you want to exclude specific sites from the search results, you can use not. For example, querying active Sever pages and not SQL returns a list of sites in the list that contain Active Sever pages, but do not contain SQL. When a particular record must be excluded, you can use not.
Queries executed in SQL are very similar to searches performed on Internet search engines. When you execute an SQL query, you can get a list of records by using a query condition that includes a logical operator. The query results from one or more tables at this time.
The syntax for SQL queries is very simple. Suppose you have a table named Email_table with two fields of name and address, and to get the E_Mail address of Bill Gates, you can use the following query:

SELECT email from email_table WHERE name= "Bill Gates"

When this query executes, read Bill Gates's e_mail address from the table named Email_table. This simple statement consists of three parts:
The first part of the SELECT statement is named the column you want to select. In this case, only the email column is selected. When executed, only the value of the email column is displayed billg@microsoft.com.
The second part of the SELECTT statement indicates which table you want to query data from. In this case, the table name to query is email_table.
Finally, the WHERE clause of the SELECT statement indicates the record to choose what conditions to meet. In this case, the query condition is selected only if the value of the Name column is the record for Bill Gates.
Bill Gates is likely to have more than one email address. If the table contains multiple email addresses of Bill Gates. Use the above SELECT statement to read all of his email addresses. The SELECT statement removes from the table the value of the email field of all the Name field values for Bill Gates's records.
As I said earlier, queries can include logical operators in query criteria. If you want to read all the email addresses of Bill Gates or President Clinton, you can use the following query:

SELECT email from email_table WHERE name= "Bill Gates" OR
Name= "President Clinton"

The query condition in this example is a bit more complicated than the previous one. This statement selects all name listed in the table email_table as a record of Bill Gates or President Clinton. If the table contains multiple addresses of Bill Gates or President Clinton, all addresses are read.
The structure of the SELECT statement looks intuitive. If you ask a friend to choose a set of records for you from a table, you may present your request in a very similar way. In the SQL SELECT statement, you "Select a specific column from a table where some columns meet a specific condition".
The next section describes how to execute a SQL query to pick records. This will help you familiarize yourself with the different methods of fetching data from a table using a SELECT statement.

Executing a SELECT query using isql
When you install SQL Sever, you also install an application called isql/w. ISQL/W allows you to perform interactive SQL queries. It is useful to test your query with ISQL/W before you include it in your ASP page.

Attention:
In the first part of the book, you learned how to install and configure Microsoft SQL Sever. If you do not have SQL Sever installed or SQL Sever cannot be run, see chapter III, "Installing and Using SQL Sever."

Select Isql_w in the SQL Sever program group on the task to start the program. When the program starts, a dialog box appears, requiring you to enter server information and login information (see Figure 10.1). In the Sever box, enter the name of your SQL Server. If the server is running on the local computer, the server name is your computer's name. In the Login information box, enter a login account and password or choose to use trusted connections, and then click the Connect button.

Figure 10. 1



Attention:
If you configure SQL Sever to use full security or mixed security, you can use a trusted connection. If you use standard security, you will need to provide a user account and password. For more information, see Chapter Iii.

If everything works, a query window appears when you click the Connection button, as shown in Figure 10.2. (If there are exceptions, please refer to chapter III)


Figure 10.2


You need to select a database before executing the query. When you install SQL Sever you have created a database for yourself, and SQL Sever also has many system databases, such as MASTER,MODEL,MSDB, and tempdb.
Conveniently, SQL Sever has a special example database named pubs. The library pubs contain individual tables for use by a virtual publisher. All the example programs in the document are designed for this library. Many of the examples in this book also use this database.
Select the database pubs in the DB Drop-down box at the top of the query window so you can select the database. All of your queries will be executed against each of the tables in the library. Now you can execute your first query. It's so exciting!
Your first query will be for a table named Autrors that contains all the relevant data for the author who works for a virtual publisher. Click the Query window and enter the following statement:

SELECT phone from authors WHERE au_name= "Ringer"

When the input is complete, click the Execute Query button (a green triangle that looks like a VCR playback key). When you click this button, any statements that appear in the query window are executed. The query window automatically becomes the result display window, and you can see the results of the query (see Figure 10.3).
The query results you see may be different from the one shown in Figure 10.3. In different versions of SQL Sever, the data in the pubs in the library is different. For SQL Sever 6.5来 said, two records will be found. The result display window should display the following:

Phone
...................
801 826_0752
801 826_0752
(2 row (s) affected)



Figure 10.3



The SELECT statement you execute takes out the phone number of all authors whose names are ringer from the table authors. You limit the results of a query by using special selection criteria in the WHERE clause. You can also omit the selection criteria and remove all the author's phone numbers from the table. To do this, click the Query tab, return to the Query window, and enter the following SELECT statement:

SELECT Phone from Authors
After this query is executed, all phone numbers in the table authors are removed (no specific order). If the table authors contains 100 phone numbers, 100 records will be removed and 1 billion records will be taken out if there are 1 billion phone numbers in the table (this may take some time).
Table authrs fields include surname, first name, phone number, address, city, state, and zip code. You can remove any one of the fields from the table by specifying them in the first part of the SELECT statement. You can remove more than one field at a time in a SELECT statement, such as:

SELECT au_fname, au_lname, phone from authors

After this SELECT statement is executed, all values for these three columns are taken out. The following is an example of the result of this query (in order to save paper, only a portion of the query results is displayed and the rest of the records are replaced with ellipses):

au_fname au_lname Phone
.....................................................................................
Johnson White 408 496_7223
Marjorie Green 415 986_7020
Cheryl Carson 415 548_7723
Michael O ' Leary 408 286_2428
...
(Affected row (s))

In the SELECT statement, you need to list how many fields you want to list. Don't forget to separate the field names with commas. You can also use an asterisk (*) to remove all the fields from a table. Here is an example of using an asterisk:

SELECT * FROM Authors

After this SELECT statement is executed, the values of all fields in the table are removed. You will find that you will use asterisks frequently in SQL queries.

Skills:
You can use the asterisk to view the names of all the columns in a table. To do this, just look at the column headings for the query results after you finish executing the SELECT statement.

Manipulating multiple tables
So far, you've only tried to fetch data from a table with a SQL query. You can also use a SELECT statement to fetch data from multiple tables at the same time, simply by listing the name of the table from which you want to remove the data from the clause in the SELECT statement:

SELECT au_lname, title from authors, titles

When this SELECT statement executes, it extracts data from both table authors and table titles. Remove all the author names from the table authors and remove all the titles from the table titles. Execute the query in the ISQL/W program and look at the results of the query. You will find strange surprises: The author's name does not match the books they have, but it appears that all possible combinations of the author's name and title may not be what you would like to see.
What's wrong? The problem is that you don't specify the relationship between the two tables. You didn't tell SQL in any way how to associate a table with a table. Because you do not know how to associate two tables, the server can simply return all possible combinations of records from two tables.
To choose a meaningful combination of records from two tables, you need to correlate two tables by creating a relationship between the fields in the two tables. One way to do this is to create a third table that describes the relationship between the fields of the other two tables.
The table authors has a field named au_id that contains a unique identifier for each author. Table titles has a field named title_id that contains the unique identification of each title. If you can establish a relationship between the field au_id and the field title_id, you can associate the two tables. There is a table named titleauthor in the database pubs that is used to do this work. Each record in the table includes two fields that are used to associate the table titles with the table authors. The following SELECT statement uses these three tables to get the correct results:

SELECT Au_name,title from Authors,titles,titleauthor
WHERE authors.au_id=titleauthor.au_id
and titles.title_id=titleauthor.title_id

When this SELECT statement is executed, each author will match the correct title. Table titleauthor indicates the relationship between table authors and table titles, which is achieved by including one field from each of the two tables. The only purpose of the third table is to establish a relationship between the fields of the other two tables. It does not itself contain any additional data.
Notice how the field name is written in this example. To distinguish between table authors and the same field name au_id in table titles, precede each field name with a table name prefix and a period. Fields named author.au_id belong to table authors, and fields named titleauthor.au_id belong to table titleauthor and are not confused.
By using the third table, you can establish various types of relationships between the two-table fields. For example, an author may have written many different books, or a book may have been done by many different authors. When there is this "many-to-many" relationship between fields in two tables, you need to use a third table to indicate the relationship.
However, in many cases, the relationship between the two tables is not complex. For example, you need to specify the relationship between table titles and table publishers. Because a book title cannot match multiple publishers, you do not need to use a third table to indicate the relationship between the two tables. To indicate the relationship between table titles and table publishers, you just have to have a public field on both tables. In the database pubs, both table titles and table publishers have a field named pub_id. If you want a list of titles and their publishers, you can use the following statement:
SELECT Title,pub_name from Titles,publishers
WHERE titles.pub_id=publishers.pub_id

Of course, if a book is published jointly by two publishers, then you need a third table to represent the relationship.
Typically, when you have a "many-to-many" relationship between fields that first knows the two tables, you use the third table to associate the two tables. Conversely, if there are only "one-to-one" or "one-to-many" relationships between fields in two tables, you can use public fields to associate them with the door.

Action Fields
Typically, when you remove a field value from a table, the value is associated with the field name that was defined when the table was created. If you select all the author names from the table authors, all values will be associated with the field name au_lname. But in some cases, you need to manipulate the field names. In the SELECT statement, you can replace it with a new name only after the default field name. For example, you can replace the field name au_lname with a more intuitive and readable name author last name:

Select au_lname ' Author last Name ' from authors

When this SELECT statement executes, the value from the field au_lname is associated with "Author last Name". This may be the result of the query:

Author last Name
................................................................................
White
Green
Carson
O ' Leary
Straight
...
(Affected row (s))

Note that the field header is no longer au_lname, but is replaced by author last name.
You can also manipulate the value of a field returned from a table by performing an operation. For example, if you want to double the price of all the books in the table titles, you can use the following SELECT statement:

SELECT Price*2 from titles

When this query is executed, the price of each book is doubled when it is taken out of the table. However, manipulating a field in this way does not change the price of the book stored in the table. The operation on a field affects only the output of the SELECT statement, not the data in the table. To display the original price of the book and the new price after the increase, you can use the following query:

Select Price ' Original price ', price*2 ' New price ' from titles

When the data is taken out of the table titles, the original price is displayed under the title original price, and the doubled prices are displayed under the title new value. The result may be this:

Original Price New Price
.........................................................................
19.99 39.98
11.95 23.90
2.99 5.98
19.99 39.98
...
(s) affected)

You can use most of the standard mathematical operators to manipulate field values, such as add (+), subtract (-), multiply (*), and divide (/). You can also perform operations on multiple fields at once, such as:

Select Price*ytd_sales "Total revenue" from titles

In this example, the total sales of each type of book are calculated by multiplying the price with the sales volume. The result of this SELECT statement would be this:

Total revenue
.....................................................
81,859,05
46,318,20
55,978,78
81,859,05
40,619,68
...
(s) affected)

Finally, you can use the Join operator (which looks like a plus sign) to connect two-character fields:

SELECT au_fname+ "" +au_lname "author name" from authors

In this example, you paste the field au_fname and the field au_lname together, separated by a comma, and specify the title of the query result as author name. The execution result of this statement will be this:

Author names
..................................................................
Johnson White
Marjorie Green
Cheryl Carson
Michael O ' Leary
Dean Straight
...
(Affected row (s))

As you can see, SQL provides you with a lot of control over query results. You should make full use of these advantages in the ASP programming process. Using SQL to manipulate query results is almost always more efficient than using scripts that have the same effect.

Sort Query Results
The introduction of this chapter has emphasized that there is no intrinsic order for SQL tables. For example, it does not make sense to take a second record from a table. From the SQL point of view, none of the records are recorded before any other records.
However, you can manipulate the order of the results of an SQL query. By default, records do not appear in a particular order when records are taken out of a table. For example, when you remove a field au_lname from a table authors, the query results appear like this:

au_lname
...........................................
White
Green
Carson
O ' Leary
Straight
...
(Affected row (s))

It is inconvenient to look at a list of names that are not in a particular order. If you put these names in alphabetical order, it would be much easier to read them. By using the ORDER BY clause, you can force the results of a query to be sorted in ascending sequence, like this:

SELECT au_lname from authors order by au_lname

When this SELECT statement is executed, the display of the author's name is sorted alphabetically. The ORDER BY clause arranges the author's name in ascending sequence.
You can also use the ORDER BY clause for multiple columns at the same time. For example, if you want to display both field au_lname and field au_fname in ascending order, you need to sort all two fields:

SELECT Au_lname,au_fname from authors to au_lname, au_fname

The query first sorts the results by au_lname fields, and then sorts them by field au_fname. The records are taken out in the following order:

au_lname au_fname
...............................................................................
Bennet Abraham
Ringer Albert
Ringer Anne
Smith Meander
...
(Affected row (s))

Note that there are two authors with the same name ringer. The author, named Albert Ringer, appeared before the author named Anne Ringer, because Albert was in alphabetical order before the surname Anne.
If you want to put the query results in reverse order, you can use the keyword desc. Keyword desc the query results in descending order, as shown in the following example:

SELECT Au_lname,au_fname from Authors
WHERE au_lname= "Ringer" ORDER by au_lname, au_fname DESC

This query extracts all author records named Ringer from the table authors. The ORDER BY clause sorts the results of the query according to the author's first and last name. The result is this:

au_lname au_fname
....................................................................................................
Ringer Anne
Ringer Albert
(2 row (s) affectec)

Notice that in this table, the surname Anne appears before the surname Albert. The author's name appears in descending order.
You can also sort a query result by a numeric field. For example, if you want to remove the price of all books in descending order, you can use the following SQL query:

SELECT price from titles to price DESC

This SELECT statement takes out the price of all the books from the table, showing the results, the low price of the book first shows that the high price of the book after the display.

Warning:
Do not sort the query results when they are not specifically needed, because the server takes some effort to complete the work. This means that a SELECT statement with an ORDER BY clause takes longer to execute than a typical SELECT statement.

Take out a different record
It is possible for a table to have duplicate values in the same column. For example, the names of two authors in the table authors of the database pubs are ringer. If you take all the names out of this table, the name ringer will be displayed two times.
In certain situations, you may only be interested in taking a different value out of a table. If a field has duplicate values, you may want each value to be selected only once, and you can use the keyword distinct to do this:

Selcet DISTINCT au_lname from authors WHERE au_lname= "Ringer"

When this SELECT statement executes, only one record is returned. You can delete all duplicate values by including the keyword distinct in the SELECT statement. For example, if you have a table about newsgroup information publishing, and you want to take out all the names of people who have posted information in this newsgroup, you can use the keyword distinct. Each user's name is taken only once--although some users have published more than one piece of information.
Warning:
As with the ORDER BY clause, forcing the server to return distinct values increases the running cost. The blessing had to take some time to finish the work. Therefore, do not use the keyword distinct when necessary.

Create a new table
As I said earlier, all the data in the database is stored in the table. Data tables include rows and columns. The column determines the type of data in the table. The row contains the actual data.
For example, the table authors in pubs in the database has nine fields. One of these fields is named au_lname, which is used to store the author's name information. Each time a new author is added to the table, the author's name is added to the field, creating a new record.
By defining a field, you can create a new table. Each field has a name and a specific data type (the data type is described in the following "field type" section), for example, the field au_lname stores character data. A field can also store other types of data.
There are many ways to create a new table using SQL Sever. You can execute an SQL statement or use the SQL Transaction manager (SQL Enterprise Manager) to create a new table. In the next section, you'll learn how to create a new table with SQL statements.

Create a new table with SQL
Attention:
If you haven't set up your own database, now jump back to chapter three to create this library. You must not add data to master,tempdb or any other system database.

Start the ISQL/W program from the SQL Sever program group (in the taskbar). After the Query window appears, select the database you created in chapter three from the Drop-down list at the top of the window. Next, type the following SQL statement in the Query window, click the Execute Query button, and execute the statement:

CREATE TABLE Guestbook (visitor VARCHAR), comments Text,entrydate
DATETIME)

If everything works, you will see the following text in the results window (see chap. III If an exception is found):

This command dit is not return data, and it did not return any rows

Congratulations, you've built your first watch!
The table you create is named Guestbook, and you can use this table to store information about your site visitors. You created this table with the Reeate table statement, which has two parts: the first part specifies the name of the table, and the second part is the names and attributes of the fields enclosed in parentheses, separated by commas.
Table Guestbook has three fields: Visitor,comments and EntryDate. The visitor field stores the visitor's name, comments the field stores the visitor's comments on your site, and entrydate the field stores the date and time the visitor visited your site.
Note that each field name is followed by a special expression. For example, the field name comments followed by the expression text. This expression specifies the data type of the field. The data type determines what data a field can store. Because the field comments contains textual information, its data type is defined as a literal type.
There are many different data types for fields. The next section describes some of the important data types that SQL supports.

Field type
Different field types are used to store different types of data. When creating and using tables, you should understand five common types of fields: character, text, numeric, logical, and date.

Character-type data
Character-type data is useful. When you need to store short string information, you always have to use character data. For example, you can put information gathered in a text box from an HTML form in a character field.
To create a field that holds variable-length string information, you can use an expression VARCHAR. Consider the table guestbook you created earlier:

CREATE TABLE Guestbook (visitor VARCHAR), comments Text,entrydate
DATETIME)

In this example, the data type of the field visitor is varchar. Note the numbers in parentheses following the data type. This number specifies the maximum length of the string allowed for this field. In this example, the field visitor can hold a string that is up to 40 characters long. If the name is too long, the string is truncated and only 40 characters are retained.
The varchar type can store a string that is up to 255 characters long. To store longer string data, you can use text-type data (described in the next section).
Another type of character data is used to store fixed-length character data. Here is an example of using this data type:

CREATE TABLE Guestbook (visitor CHAR), comments Text,entrydate
DATETIME)

In this example, the field visitor is used to store a 40-character fixed-length string. The expression char specifies that the field should be a fixed-length string.
The difference between varchar and char data is subtle, but very important. If you enter data into a 40-character varchar field, Bill Gates. When you remove this data from this field later, the data you take out is 10 characters long-the length of the string, Bill Gates.
Now if you type a string into a char field of 40 characters, the length of the data you take out will be 40 characters. The string will be appended with extra spaces.
When you set up your own site, you'll find that using varchar fields is much more convenient than char-type fields. When using varchar fields, you don't have to worry about cutting out the extra space in your data.
Another notable benefit of the varchar field is that it takes up less memory and hard disk space than the Char type field. This saving of memory and disk space becomes very important when your database is large.

Text-type data
Character data limits the length of a string to no more than 255 characters. With text-type data, you can store more than 2 billion characters in a string. When you need to store large strings of characters, you should use text-type data.
Here is an example of using text-type data:

CREATE TABLE Guestbook (visitor VARCHAR), comments Text,entrydate
DATETIME)

In this example, the field comments is used to store the visitor's opinion of your site. Note that the literal data has no length, and the character data described in the previous section is of length. The data in a text field is usually either empty or large.
When you collect data from the HTML form's multiple-text edit box (TEXTAREA), you should store the information you collect in a literal field. However, whenever you can avoid using text-type fields, you should not be able to use them. Text-type fields are large, and misuse of text fields slows the server down. Text-type fields also consume a large amount of disk space.
Warning:
Once you have entered any data (even a null value) into a text field, 2K of space is automatically assigned to that data. You will not be able to reclaim this portion of the storage space unless you delete the record.

Numeric type data
The SQL sever supports many different numeric data types. You can store integers, decimals, and money.
Typically, when you need to store numbers in a table, you use int data. The number of tables in int data is integers ranging from 2,147,483,647 to 2,147,483,647. Here is an example of how to use INT-type data:

CREATE TABLE Visitlog (Visitor VARCHAR (), NumVisits INT)

This table can be used to record the number of times your site has been accessed. As long as no one visits your site more than 2,147,483,647 times, the Nubvisits field can store the number of visits.
In order to save memory space, you can use smallint type data. SMALLINT data can store integers from 32768 to 32768. This data type is used in exactly the same way as the int type.
Finally, if you really need to save space, you can use the tinyint type data. Similarly, this type of usage is the same as the int type, except that this type of field can only store integers from 0 to 255. Tinyint fields cannot be used to store negative numbers.
In general, in order to save space, you should use the smallest integer data as much as possible. One tinyint data occupies only one byte; an int data occupies four bytes. This may seem a little different, but in a larger table, the number of bytes grows fast. On the other hand, once you have created a field, it is difficult to modify it. Therefore, for security reasons, you should anticipate the maximum number of values a field needs to store, and then select the appropriate data type.
In order to have more control over the data stored in the field, you can use numeric data to represent both the integer and the decimal parts of a number. Numeric data allows you to represent a very large number-much larger than int-type data. A numeric field can store numbers from 1038 to 1038. Numeric data also allows you to represent numbers with a small number of parts. For example, you can store decimal 3.14 in the Numeric Type field.
When defining a numeric type field, you need to specify both the size of the integer part and the size of the decimal part. Here is an example of using this data type:

CREATE TABLE numeric_data (bignumber numeric (28,0),
Fraction NUMERIC (5,4))

When this statement executes, a table with two fields named Numeric_data is created. Field bignumber can store integers up to 28 bits. Field fraction can store decimal numbers with five-bit integer parts and four-bit decimal parts.
The integer portion of a numeric data can have a maximum of only 28 digits, and a fractional number must be less than or equal to the number of digits in the integer part, and the decimal part can be zero.
You can use INT-or numeric-type data to store money. However, there are two other types of data that are specifically used for this purpose. If you want your network to earn a lot of money, you can use the cash type data. If you are not ambitious, you can use smallmoney data. Money type data can store money from -922,337,203,685,477.5808 to 922,337,203,685,477.5807. If you need to store a larger amount than this, you can use the numeric type data.
SmallMoney data can only store money from -214,748.3648 to 214,748.3647. Also, if you can, you should use the SmallMoney type instead of the money type data to save space. The following example shows how to use both types of data that represent money:

CREATE TABLE Products (product VARCHAR), Price,
Discount_price smallmoney)

This table can be used to store discounts on goods and ordinary prices. The data type of the field price is money, and the field Discount_price data type is smallmoney.

Storing logical values
If you use a checkbox to collect information from a Web page, you can store this information in a bit type field. The bit Type field can take only two values: 0 or 1. Here is an example of how to use this field:

CREATE TABLE Opinion (visitor VARCHAR), good BIT)

This table can be used to store information about your site's opinion polls. Visitors can vote to indicate whether they like your network. If they vote yes, they deposit 1 in the bit field. Conversely, if they cast no, deposit 0 in the field (in the next chapter you will learn how to calculate the vote).
Be careful, after you create a table, you can't add bit fields to the table. If you plan to include bit fields in a table, you must do so when creating a table.

Store date and time
When you create a network, you may need to record the number of visitors over a period of time. To be able to store dates and times, you need to use datetime data, as shown in the following example:

CREATE tabl Visitorlog (visitor VARCHAR), ArrivalTime DATETIME,
Departuretime DATETIME)

This table can be used to record the time and date when visitors enter and leave your network. A datetime field can be stored with a date range from the first millisecond of January 1, 1753 to the last millisecond of December 31, 9999.
If you don't need to cover such a large range of dates and times, you can use smalldatetime data. It is used in the same way as datetime data, except that it can represent a smaller date and time range than datetime data, and is not as accurate as datetime data. A smalldatetime field can store dates from January 1, 1900 to June 6, 2079, and it can only be accurate to seconds.
It is important to recognize that DateTime fields do not contain actual data until you enter a date and time. In the next chapter, you will learn how to use a large number of SQL functions to read and manipulate dates and times (see the "Default Values" section below). You can also use date and time functions in VBScript and JScript to enter dates and times in a DateTime field.

Field Properties
The previous section describes how to build a table that contains different types of fields. In this section, you will learn how to use the three properties of a field. These properties allow you to control null values, default values, and identity values.

Allow and disallow null values
Most fields can accept null values (NULL). When a field accepts a null value, it remains empty if you do not change it. Null values (NULL) and 0 are different, strictly speaking, a null value means that there is no value.
To allow a field to accept null values, you use the expression null after the field definition. For example, both fields in the following table allow null values to be accepted:

CREATE TABLE Empty (empty1 CHAR () null,empty2 INT NULL (

Attention:
Bit type data cannot be null. A field of this type must take 0 or 1.

Sometimes you need to prevent a field from using null values. For example, suppose you have a table that stores credit card numbers and credit card validity dates, and you don't want someone to enter a credit card number without entering a valid date. To force two fields to enter data, you can create the table in the following ways:

CREATE TABLE creditcards (creditcard_number CHAR () not NULL,
Creditcard_expire DATETIME not NULL)
Note that the field definition is followed by the expression not NULL. By including the expression not NULL, you can prevent anyone from inserting data into one field without entering data from another field.
You will find that the ability to suppress null values is very useful in the process of building your own network. If you specify a field that cannot accept null values, you will have an error warning when you try to enter a null value. These error warnings can provide valuable clues to program debugging.

Default value
Suppose you have a table that stores address information, including streets, cities, states, postal codes, and countries. If you anticipate that most of the addresses are in the United States, you can use this value as the default value for the Country field.
To specify a default value when creating a table, you can use the expression default. Take a look at the following example of using default values when creating a table:

CREATE TABLE addresses (street VARCHAR) NULL,
City VARCHAR (=) NULL,
State VARCHAR (NULL)
Zip VARCHAR () NULL,
Country VARCHAR () DEFAULT ' USA ')

In this example, the default value of the field country is specified as the United States. Note the use of single quotes, which indicate that this is a character type data. To specify a default value for a field that is not a character type, do not enclose the value in quotation marks:

CREATE TABLE Orders (price money DEFAULT $38.00,