Web Hosting Java Blog

Chapter 4 QUERYING AND MODIFYING DATA 233 and, of course, allow for an employee discount (not implemented in the Northwind example). The query to perform this operation might look similar to the following: INSERT INTO Northwind.dbo.Customers SELECT EmployeeID, Northwind , FirstName + + LastName, Employee , Address, City, Region, PostalCode, Country, HomePhone, NULL FROM Northwind.dbo.Employees The SELECT list of the subquery must match the column list of the INSERT statement. If no column list is specified, the SELECT list must match the columns in the table or view being inserted into, as in the example. Note that NULL has been provided for a fax number at the end of the column list, because none is included in the employees table. The INSERT SELECT statement can be used to insert data from any viable source. This includes SQL Server tables, views, and sources outside SQL Server. Values can be inserted from any number of sources using a variety of options available to an INSERT operation. As you will see next, this is a very flexible operation and virtually any data source can be used. INSERT EXECUTE An EXECUTE statement that returns data with SELECT or READTEXT statements can be used to return the insert values to an INSERT operation. Each resultset must be compatible with the columns in the table or in the column list being used by the INSERT. In this manner a stored procedure can be executed and the data returned as input to a table. If an operation returns data with the READTEXT statement, each individual READTEXT statement can return a maximum of 1MB. The execute operation can also be used with extended procedures (not available in previous versions of SQL Server). Using an operation in this manner enables complex logic to be saved into a stored procedure, and the resulting output can be used for the insertion. For example, imagine you want to get a listing of top salespeople to establish year-end bonuses. You would like to place these records into a separate table. This operation would require four tables be queried on each of three servers and calculations be performed to determine each salesperson s ranking. If you create a procedure called pick_top_sales, the results of this procedure could be used to form the data for your table. The query would look similar to the following: INSERT Top_Sales EXECUTE pick_top_sales

If you looking for unlimited one inclusive web hosting plan please check cheap web hosting website.

232 Part I EXAM PREPARATION FIGURE 4.6 Insert of data. Alternatively, you can specify just the VALUES keyword, which is a more convenient method. Values is required unless you are doing INSERT, SELECT OR INSERT, or EXECUTE. The same results are produced with the following query: INSERT INTO TestTable Values(34, may , 2802695 , male , . black ) Data inserted must meet the parameters defined by the table structure. This means that NOT NULL columns must have data provided either through input or through the use of column definitions that provide for their own values. A column can obtain its input value through a DEFAULT, IDENTITY, formula, or default object. Data must also meet all rules and constraints that have been defined in the table schema. Data can be inserted into a table from the resultset of a SELECT query. When this is performed it enables a large amount of data to be extracted from a table or view and stored into another table. INSERT INTO SELECT A SELECT statement can be used within the INSERT statement to add values into a table from one or more other tables or views. Using a SELECT subquery is also a mechanism that enables more than one row to be inserted at one time. This type of INSERT statement is often used to insert data into a separate table from some other table or data source. In this manner the data can be copied or just separated off for handling of exceptions or specialty tasks. For example, imagine you would like to copy all your current employees into a customer table. This will enable your employees to make purchases

For reliable and cheap web hosting services please check javaweb hosting website.

Chapter 4 QUERYING AND MODIFYING DATA 231 MAKING DATA MODIFICATIONS As time passes, new data will no doubt need to be inserted, redundant data will need to be deleted, and existing data will need to be updated. Data may be modified with a few simple lines of T-SQL. Data modification is always necessary when working with real-life situations. Think, for example, of an employee changing houses. His address would need to be modified, which could be done using the UPDATE statement. Data modification is changing, deleting, or inserting data, and this section will teach you just that. Basically, there are three T-SQL statements that enable you to insert, delete, and update data; unsurprisingly, they are INSERT, DELETE, and UPDATE. Inserting Data into Tables Although there are many ways to insert data into an existing table, such as using the Enterprise Manager, this section deals with the primary coding method of using the INSERT statement. The syntax of the INSERT INTO statement can be summarized as follows: INSERT [INTO] table_or_view [(column_list)] VALUES .data_values The statement causes the data values to be inserted as one or more rows into the named table. Column list is a comma-separated list of column names that can be used to indicate the columns for which data is supplied. Remember, it s always a safe habit to practice modifying data on a test table. For these examples, use the table defined as follows: CREATE TABLE TestTable ( age int NULL, month varchar(8) NULL, phone varchar(15) NULL, gender varchar(7) NULL, haircolor varchar(14) NULL ) Now, to insert the data, enter the query as displayed in Figure 4.6.

For reliable and cheap web hosting services please check tomcat web hosting website.

Chapter 4 QUERYING AND MODIFYING DATA 231 MAKING DATA MODIFICATIONS As time passes, new data will no doubt need to be inserted, redundant data will need to be deleted, and existing data will need to be updated. Data may be modified with a few simple lines of T-SQL. Data modification is always necessary when working with real-life situations. Think, for example, of an employee changing houses. His address would need to be modified, which could be done using the UPDATE statement. Data modification is changing, deleting, or inserting data, and this section will teach you just that. Basically, there are three T-SQL statements that enable you to insert, delete, and update data; unsurprisingly, they are INSERT, DELETE, and UPDATE. Inserting Data into Tables Although there are many ways to insert data into an existing table, such as using the Enterprise Manager, this section deals with the primary coding method of using the INSERT statement. The syntax of the INSERT INTO statement can be summarized as follows: INSERT [INTO] table_or_view [(column_list)] VALUES .data_values The statement causes the data values to be inserted as one or more rows into the named table. Column list is a comma-separated list of column names that can be used to indicate the columns for which data is supplied. Remember, it s always a safe habit to practice modifying data on a test table. For these examples, use the table defined as follows: CREATE TABLE TestTable ( age int NULL, month varchar(8) NULL, phone varchar(15) NULL, gender varchar(7) NULL, haircolor varchar(14) NULL ) Now, to insert the data, enter the query as displayed in Figure 4.6.

For reliable and cheap web hosting services please check javaweb hosting website.

230 Part I EXAM PREPARATION Many system stored procedures can also be used to find information about server and database objects. With many of the procedures, however, you can also perform actions against the server, whereas information schema views are used solely to obtain meta data. System Stored Procedures Many administrative and informational activities in SQL Server can be accomplished through the use of SQL Server s many system stored procedures. System stored procedures are available to perform a variety of activities from obtaining information about server settings and objects to managing processes on the server to performing maintenance activities and much more. It is not possible to cover all the procedures in this blog, and SQL Server blogs Online has full definitions and examples for these procedures. At various points throughout the blog, references will be made to those procedures you are likely to find on the exam and others that will serve useful purposes in the future. REVIEW BREAK TIPExam Processes Typical situations that are tested on the exam are: Date conversion using appropriate date functions String concatenation, including building and parsing functions System application design, where system functions are used to programmatically control or manipulate the software functionality EXAM Data Summary Summarizing information requires that a variety of functions be applied against the data to produce useful information. Summarizing data will provide the end user with the best information source possible to meet the needs of a business. To master the use of functions, you need to work with as many different situations as possible. Each individual problem requires the use of the set of functions necessary to accomplish the task. With such a diverse set of functions available, SQL Server can be used in a multitude of different situations. To recognize the diversity, you will need experience in an equal number of diverse situations.

For reliable and cheap web hosting services please check tomcat web hosting website.

Chapter 4 QUERYING AND MODIFYING DATA 229 Although it is possible to obtain data through querying any of the system tables directly, the system tables may not provide the information required in the future. It is recommended that system stored procedures, system functions, and information schema views be used because the contents of the system tables may change in future releases. Information Schema Views Information schema views provide a method independent of the system tables to view meta data. These views enable applications to work properly even though significant changes may have been made to the system tables and more changes may be made in the future. An application that uses the views rather than a direct query against the system tables should function in the same manner in the future as it does in the current SQL Server release. The information schema views included in SQL Server conform to the SQL-92 Standard definition for the INFORMATION_SCHEMA. Names used in the SQL-92 standard for these views are different from those used by SQL Server, though the names from SQL Server can equivalently be mapped to those of the standard. The following list shows the SQL-92 names and the SQL Server equivalents: A SQL-92 Catalog is a SQL Server Database . Schema in SQL-92 is an Owner in SQL Server. Object is the same in both SQL-92 and in SQL Server. A Domain in SQL-92 is a user-defined data type in SQL Server. When retrieving meta data from the information schema views, you must use a qualified name that includes the INFORMATION_SCHEMA in the position where you usually specify the user name. For example: SELECT * FROM Northwind.INFORMATION_SCHEMA.TABLES For more information on the variety of meta data that can be obtained through the use of information schema views, use the Index tab of SQL Server blogs Online. When you type information schema, the index shows links to all the appropriate views.

For reliable and cheap web hosting services please check tomcat web hosting website.

228 Part I EXAM PREPARATION TABLE 4.10 continued DATABASE/SYSTEM FUNCTIONS IN T-SQL System Function Parameters Description NULLIF (expression1, This gives a NULL value only if the two expression2) expressions are equivalent. OBJECT_ID (object_name) The database object identification number. OBJECT_NAME (Object_ID) The database object name. STATS_DATE (Table_ID, The date that the statistics for a Index_ID) particular index were last updated. SUSER_ID (login name) This is only used for backward compatibility. Use SUSER_SID instead. SUSER_SID (login name) The user s login identification number. SUSER_NAME (server_user_id) This is only used for backward compatibility. Use SUSER_SNAME instead. SUSER_SNAME (server_user_id) The user s login identification name. USER_ID (user_name) The user s database identification number. USER_NAME (user_ID) The user s database username. ISNULL can be useful when you want to convert all NULL values to a particular value. For example, look at the following query. This query converts all NULL values into zeros: SELECT ISNULL (price, 0.0000), price FROM titles System functions, information schema views, or the system stored procedures can be used to gain access to system information without querying the system tables directly. System tables can change significantly between versions of SQL Server. SQL Server provides system stored procedures or information schema views for obtaining information about the properties of data, such as the type of data in a column (numeric, text, and so on) or the length of a column. This type of information is called meta data and is maintained by SQL Server for all server and database objects. Meta data can be used to find out information about the structure of data, the contents of a server, or information that specifies the design of objects.

For high quality website hosting services please check cheap web hosting website.

Chapter 4 QUERYING AND MODIFYING DATA 227 System Functions As you have seen in the previous two compilations, many functions are supplied to perform mathematical and character-manipulation operations. You can also use a third category of functions with the SELECT list known as system-specific functions. You can use system functions to retrieve special system or database information through T-SQL and the SELECT statement. Table 4.10 shows a compilation of the numerous system functions available in T-SQL. TABLE 4.10 DATABASE/SYSTEM FUNCTIONS IN T-SQL System Function Parameters Description COL_LENGTH (table name, The length of a column. column name) COL_NAME (table_id, The name of a column. Column_id) DATALENGTH (Expression) The length of any expression in bytes. DB_ID (database_name) The database s identification number. DB_NAME (database_ID) The database s name. GETANSINULL (database_name) Returns the default nullability for the database for this session. HOST_ID ( ) The identification number for the workstation. HOST_NAME ( ) The name of the workstation. IDENT_INCR (table or view) Returns the increment value specified during the creation of an identity column in a table or view that has an identity column. IDENT_SEED (table or view) The starting number for an identity column. INDEX_COL (table name, index_ The indexed column s name. id,key_id) ISNULL (expression, value) Changes NULL values in the expression to a value specified. continues

If you looking for unlimited one inclusive web hosting plan please check unlimited web hosting website.

226 Part I EXAM PREPARATION Where: Expression is a character string, binary string, text, image, a column, or an expression that includes a column. Start is a number denoting the initial position of the sub-string. Length is a number denoting how long the sub-string is. WARNING This example shows how SUBSTRING works: Avoid Using SUBSTRING The SUB SELECT Au_fname + + au_lname AS full name , STRING function, depending on how SUBSTRING (au_fname, 1,1) + SUBSTRING (au_lname, 1,1) AS it is used,as in a WHERE clause, . initials FROM authors may perform a table scan where an index was supposed to function The next section looks at using DATALENGTH to count the number of (if an index was implemented). bytes used to represent an expression. When the SUBSTRING function does not include the first letter of the column being searched, a DATALENGTH table scan is performed. You may need to know how many bytes long a string is. Of course, you could count the number of characters present in a string, but that would be a complete waste of time. You might also have problems differentiating standard one-byte strings and Unicode two-byte strings. For example, if you were creating an application with a first name column of a fixed length, you would need to observe previous tables with a first name column to get an estimate of the highest first name present to set the fixed length. An easy way to do this would be to use a function known as DATALENGTH in conjunction with MAX; in this way, you would certainly save time and get results. The DATALENGTH function returns the number of bytes used in an expression supplied. The syntax for DATALENGTH is DATALENGTH (expression) Where: Expression is the data you want to find the length of. Filtering and formatting data can be carried further with elements of grouping and computing results. These features are defined further in Chapter 5, Advanced Data Retrieval and Modification. The final area of SQL Server functionality left to discuss is a series of functions that allow for a variety of system-level interactions.

For reliable and cheap web hosting services please check javaweb hosting website.

226 Part I EXAM PREPARATION Where: Expression is a character string, binary string, text, image, a column, or an expression that includes a column. Start is a number denoting the initial position of the sub-string. Length is a number denoting how long the sub-string is. WARNING This example shows how SUBSTRING works: Avoid Using SUBSTRING The SUB SELECT Au_fname + + au_lname AS full name , STRING function, depending on how SUBSTRING (au_fname, 1,1) + SUBSTRING (au_lname, 1,1) AS it is used,as in a WHERE clause, . initials FROM authors may perform a table scan where an index was supposed to function The next section looks at using DATALENGTH to count the number of (if an index was implemented). bytes used to represent an expression. When the SUBSTRING function does not include the first letter of the column being searched, a DATALENGTH table scan is performed. You may need to know how many bytes long a string is. Of course, you could count the number of characters present in a string, but that would be a complete waste of time. You might also have problems differentiating standard one-byte strings and Unicode two-byte strings. For example, if you were creating an application with a first name column of a fixed length, you would need to observe previous tables with a first name column to get an estimate of the highest first name present to set the fixed length. An easy way to do this would be to use a function known as DATALENGTH in conjunction with MAX; in this way, you would certainly save time and get results. The DATALENGTH function returns the number of bytes used in an expression supplied. The syntax for DATALENGTH is DATALENGTH (expression) Where: Expression is the data you want to find the length of. Filtering and formatting data can be carried further with elements of grouping and computing results. These features are defined further in Chapter 5, Advanced Data Retrieval and Modification. The final area of SQL Server functionality left to discuss is a series of functions that allow for a variety of system-level interactions.

For high quality java hosting services please check java web hosting website.