Web Hosting Java Blog

242 Part I EXAM PREPARATION APPLY YOUR KNOWLEDGE 4. You have been given the assignment of preparing a set of user-friendly queries to produce a series of reports for a building construction supply company. You would like a query that places customers in order by country, region, and then city. Which of the following queries would solve the problem? A. SELECT * FROM Customers ORDER BY Country, State, City B. SELECT CustomerName, City, State, Country ORDER BY Country, State, City, CustomerName C. SELECT * FROM Customers ORDER BY City, State, Country D. SELECT CustomerName, City, State, Country ORDER BY City, State, Country, CustomerName E. SELECT CustomerName, City, State, Country ORDER BY CustomerName, City, State, Country 5. You are working on a database view that will be used by a group of salespeople to query a database for sales statistics. The report that you are debugging is printing duplicates for some states, and in some instances there are three or more duplicates. How would you try to resolve the problem? A. Correct the FROM clause B. Correct the WHERE clause C. Correct the TOP clause D. Correct the ORDER BY clause E. Correct the DISTINCT clause 6. You have entered a query using a TOP function to limit the number of records being viewed to five. When you see the results of the query, the dates being viewed were not the first five in the data. What could be the source of the problem? A. The resultset has not been grouped. B. The data contains NULL values. C. There is an incorrect ORDER. D. Table aliases were used. E. Schema binding has been applied. 7. You work for a large manufacturing organization that maintains a large production database system on a single SQL Server 2000 machine. In attempting to enter a query to add a record to a table, you find that it is not possible. Which of the following is not a likely cause for the error? A. Data doesn t meet constraint. B. Referential integrity. C. Database is read-only. D. Permissions. E. Other applications are locking data. F. SQL Server Agent is not started. 8. A local branch of a large hotel chain maintains guest information on a single SQL Server 2000 computer. You are creating an application that will change the contents of a database programmatically through a Visual Basic interface on a local area network. Which technology would you utilize? A. ADO B. RDO C. DAO D. SQL DMO E. XML

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

Chapter 4 QUERYING AND MODIFYING DATA 241 A PPLY YOUR K NO WLEDGE Review Questions 1. How does a DISTINCT query provide for useful reporting to the user? 2. What advantages can the use of a WHERE clause provide over the basic use of a SELECT or DELETE operation? 3. How would data in the form of dates be compared against data stored as characters? 4. What types of functions would be considered if the most important application issue centered around the use of network bandwidth? 5. When do data deletions and modifications affect data stored in other tables? Exam Questions 1. In preparation for a major system upgrade, a large set of data changes are going to be made on a system. You would like to implement a number of changes without affecting any of the existing data. Which of the following operations do not affect any existing data values? Select all that apply: A. INSERT B. UPDATE C. Change column name D. Increase column length E. Decrease column length 2. You are creating a one-time report to supply the office staff with a revenue breakdown. The data source for the report contains cryptic column headings that cover several different categories. You must provide the report in a manner the users can easily understand. Which of the following would be the best solution? Select 2; each answer represents half of the correct solution. A. Provide friendly aliases for the table names. B. Provide friendly aliases for the column names. C. Create a VIEW with corresponding definition. D. Create a corresponding DEFAULT definition. E. Execute a corresponding query from the Analyzer. F. Create a front-end program to execute the required query. 3. You have implemented a database for an international research organization and are performing some test queries against the tables within the database. You would like to have a listing of the data from only the year 2000. Which of the following queries represents the best solution to the problem? A. SELECT * FROM Results WHERE ResultDate BETWEEN 01/01/2000 .AND 01/01/2001 B. SELECT * FROM Results WHERE ResultDate BETWEEN 12/31/1999 .AND 12/31/2000 C. SELECT * FROM Results WHERE ResultDate BETWEEN 12/31/1999 .AND 01/01/2001 D. SELECT * FROM Results WHERE ResultDate BETWEEN 01/01/2000 .AND 12/31/2000 E. SELECT * FROM Results WHERE ResultDate BETWEEN . 12/31/2000 AND 01/01/2000

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

Chapter 4 QUERYING AND MODIFYING DATA 241 A PPLY YOUR K NO WLEDGE Review Questions 1. How does a DISTINCT query provide for useful reporting to the user? 2. What advantages can the use of a WHERE clause provide over the basic use of a SELECT or DELETE operation? 3. How would data in the form of dates be compared against data stored as characters? 4. What types of functions would be considered if the most important application issue centered around the use of network bandwidth? 5. When do data deletions and modifications affect data stored in other tables? Exam Questions 1. In preparation for a major system upgrade, a large set of data changes are going to be made on a system. You would like to implement a number of changes without affecting any of the existing data. Which of the following operations do not affect any existing data values? Select all that apply: A. INSERT B. UPDATE C. Change column name D. Increase column length E. Decrease column length 2. You are creating a one-time report to supply the office staff with a revenue breakdown. The data source for the report contains cryptic column headings that cover several different categories. You must provide the report in a manner the users can easily understand. Which of the following would be the best solution? Select 2; each answer represents half of the correct solution. A. Provide friendly aliases for the table names. B. Provide friendly aliases for the column names. C. Create a VIEW with corresponding definition. D. Create a corresponding DEFAULT definition. E. Execute a corresponding query from the Analyzer. F. Create a front-end program to execute the required query. 3. You have implemented a database for an international research organization and are performing some test queries against the tables within the database. You would like to have a listing of the data from only the year 2000. Which of the following queries represents the best solution to the problem? A. SELECT * FROM Results WHERE ResultDate BETWEEN 01/01/2000 .AND 01/01/2001 B. SELECT * FROM Results WHERE ResultDate BETWEEN 12/31/1999 .AND 12/31/2000 C. SELECT * FROM Results WHERE ResultDate BETWEEN 12/31/1999 .AND 01/01/2001 D. SELECT * FROM Results WHERE ResultDate BETWEEN 01/01/2000 .AND 12/31/2000 E. SELECT * FROM Results WHERE ResultDate BETWEEN . 12/31/2000 AND 01/01/2000

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

240 Part I EXAM PREPARATION APPLY YOUR KNO WLEDGE 6. Ensure the data is correctly entered using a simple query: SELECT * FROM Publishers GO SELECT * FROM Employee GO 4.2 Database Interactions (ADD, CHANGE, DELETE) In Exercise 4.2 you will use the data previously created to perform a variety of procedures and queries commonly issued against a database. Standard INSERT, UPDATE, and DELETE operations will be performed. Estimated Time: 15 minutes. 1. You have already issued several INSERT statements in the previous exercise, so in this exercise you need add only a couple of additional rows. The exercise is performed from the Query Analyzer with the TestData database selected from the database drop-down list box. 2. Add an additional row to each of the tables by issuing the following queries: INSERT Publishers VALUES (3, WR Publishing , . Kingsville , CA , USA ) INSERT Employee VALUES (5, Kim , Jepsom , .30000, 03/05/2001 , 3) 3. Change an existing record by issuing the following UPDATE operation: UPDATE Employee SET firstname = William WHERE firstname = Billy 4. Remove an existing record using the following DELETE operation: DELETE FROM Employee WHERE emp_id = 2 5. Check your results by querying the two tables: SELECT * FROM Publishers SELECT * FROM Employee Try to determine what rules had to be followed to allow for input and removal of data. The next exercise illustrates conditions where errors might need to be trapped by a front-end application. 4.3 Error Conditions Exercise 4.3 illustrates error conditions by attempting to add and remove data improperly against the referential integrity settings of the database. Estimated Time: 15 minutes 1. The exercise is performed from the Query Analyzer with the TestData database selected from the database drop-down list box. 2. Attempt to remove an existing publisher that has Foreign Key records existing in another table: DELETE FROM Publishers WHERE pub_id = 1 3. Attempt to insert a new employee record that has an invalid publisher identifier: INSERT Employee VALUES (6, Joe , Smith , 20000, 06/04/2000, 9) Note the type of errors that occur and the reasons for them. Error handling is covered fully in Chapter 9. If referential integrity is defined, then a primary entry must exist for every Foreign Key element. If a primary entry has such elements, it can t be removed. Of course, this activity could be affected by the cascading settings in the relationship. For more information on referential integrity and cascading operations, see Chapters 2 and 3.

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

Chapter 4 QUERYING AND MODIFYING DATA 239 APPLY YOUR KNOWLEDGE Exercises The following set of exercises takes you through the creation of a table structure, insertion of data, modification of existing data, and deletion of data. 4.1 Creation of Test Data In Exercise 4.1 you initialize a table structure and insert some test data to be used by the remaining exercises in this chapter. Estimated Time: 15 minutes. 1. If it is not already open, load the SQL Query Analyzer. Supply the logon connection information if requested. 2. Select the Master database from the database drop-down list box. 3. Enter the following query to initialize the database: CREATE DATABASE TestData ON ( NAME = TestData_data, FILENAME = c:testdata.mdf , SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ) LOG ON ( NAME = TestData_log , FILENAME = c:testdata.ldf , SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB ) GO The database location could be altered if you wish. At the end of the chapter, you may want to go back and DROP the database, or leave it for future practice. 4. Enter the following query to initialize the two tables: USE TestData GO CREATE TABLE publishers ( pub_id char(4) NOT NULL CONSTRAINT .UPKCL_pubind PRIMARY KEY CLUSTERED, pub_name varchar(40) city varchar(20) state char(2) country varchar(30) ) GO NULL, NULL, NULL, NULL CREATE TABLE employee ( emp_id char(4) CONSTRAINT PK_emp_id .PRIMARY KEY NONCLUSTERED, firstname varchar(20) NOT NULL, lastname varchar(30) NOT NULL, salary money NOT NULL, datehired datetime NOT NULL, pub_id char(4) NOT NULL CONSTRAINT myname FOREIGN KEY .REFERENCES publishers(pub_id) ) GO Referential integrity has been set up on this definition. A later exercise illustrates the correct input procedure and then turns to the error conditions that may arise if data doesn t meet the requirements. 5. Enter the following query to enter some initial data. INSERT Publishers VALUES (1, IM Publishing , . Warsaw , PA , USA ) INSERT Publishers VALUES (2, UR Publishing , . Harmony , TX , USA ) GO INSERT Employee VALUES (1, John , . Stevenson , 45000, 01/02/2001 , 1) INSERT Employee VALUES (2, Tim , Johns , .40000, 04/06/2000 , 1) INSERT Employee VALUES (3, Susan , . Hilbury , 50000, 11/07/2000 , 2) INSERT Employee VALUES (4, Billy , Maxyor , .60000, 12/03/2000 , 2) GO

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

238 Part I EXAM PREPARATION CHAPTER SUMMARY Character functions can be used to manipulate the way strings look. LTRIM, for example, takes all leading blanks away. ASCII finds the ASCII code value for a string. You looked at inserting and deleting data. Data can be inserted using the INSERT INTO statement. If you want to delete data, you can use the DELETE keyword. A faster non-logged method of deleting data all the rows in a table is to use the TRUNCATE TABLE statement.

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

Chapter 4 QUERYING AND MODIFYING DATA 237 CASE STUDY: ADDRESS blog APPLICATION processed information. Now a user can, for example, order results by first name or last name and can perform partial word searches throughout the records. After altering the program code, you realize that the coding was not as difficult as you expected; however, this new change will save a great a great deal of time for the end user. search feature and an ordering feature, to make it easy to find a particular address or phone number. You look at the different data retrieval commands available in the T-SQL library and find the WHERE clause and LIKE predicate helpful. You alter your existing program to include this change by adding two components: first, searches that use the LIKE predicate, and second, the ordering of CHAPTER SUMMARY This chapter has examined a lot of aspects of performing queries and modifying data. It started with tools to query data. These include the BCP and OSQL utilities and the SQL Query Analyzer. The BCP utility is used to insert large amounts of data into a table, whereas the OSQL utility and the Query Analyzer enable you to interactively modify and access data using T-SQL. You also learned about the different parts of the whole SELECT statement. First is the SELECT list part, where you list the columns you want to access. Then the FROM clause determines from which table to access the data. After that is the WHERE clause, which can be used to limit or filter the data viewed. In this clause, you can connect various other functions to limit the data to your specifications. Some of these functions include LIKE, BETWEEN, IN, and IS NULL. You also saw how to return the highest/lowest values using TOP n and ROWCOUNT. And you looked at the SUBSTRING function as well as various DATE, arithmetic, and system functions, which enable you to manipulate data and work with the database system. The ORDER BY clause orders your data in either ascending or descending order. You looked at the mathematical functions allowed in SQL. Some of these are ABS, which finds the absolute value, and ROUND, which rounds a number off to a given precision. KEY TERMS SELECT statement column list FROM WHERE ORDER BY LIKE string concatenation CAST CONVERT TOP INSERT INSERT INTO DELETE

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

Chapter 4 QUERYING AND MODIFYING DATA 237 CASE STUDY: ADDRESS blog APPLICATION processed information. Now a user can, for example, order results by first name or last name and can perform partial word searches throughout the records. After altering the program code, you realize that the coding was not as difficult as you expected; however, this new change will save a great a great deal of time for the end user. search feature and an ordering feature, to make it easy to find a particular address or phone number. You look at the different data retrieval commands available in the T-SQL library and find the WHERE clause and LIKE predicate helpful. You alter your existing program to include this change by adding two components: first, searches that use the LIKE predicate, and second, the ordering of CHAPTER SUMMARY This chapter has examined a lot of aspects of performing queries and modifying data. It started with tools to query data. These include the BCP and OSQL utilities and the SQL Query Analyzer. The BCP utility is used to insert large amounts of data into a table, whereas the OSQL utility and the Query Analyzer enable you to interactively modify and access data using T-SQL. You also learned about the different parts of the whole SELECT statement. First is the SELECT list part, where you list the columns you want to access. Then the FROM clause determines from which table to access the data. After that is the WHERE clause, which can be used to limit or filter the data viewed. In this clause, you can connect various other functions to limit the data to your specifications. Some of these functions include LIKE, BETWEEN, IN, and IS NULL. You also saw how to return the highest/lowest values using TOP n and ROWCOUNT. And you looked at the SUBSTRING function as well as various DATE, arithmetic, and system functions, which enable you to manipulate data and work with the database system. The ORDER BY clause orders your data in either ascending or descending order. You looked at the mathematical functions allowed in SQL. Some of these are ABS, which finds the absolute value, and ROUND, which rounds a number off to a given precision. KEY TERMS SELECT statement column list FROM WHERE ORDER BY LIKE string concatenation CAST CONVERT TOP INSERT INSERT INTO DELETE

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

236 Part I EXAM PREPARATION FIGURE 4.7 UPDATE used against existing data. ESSENCE OF THE CASE . You created an address blog application in Visual Basic. . Beta testers commented that reporting of the results in the application was not properly performed. . You implement a better and more organized way to display results. CASE STUDY: ADDRESS blog APPLICATION SCENARIO You have been working lately on an address blog project in Visual Basic that uses SQL Server as the underlying database. You have created most of the program code and have already implemented the body of the project. So far, users can enter unlimited addresses and phone numbers through the easy-to-use graphical interface. After you have completed the project, you send it to a couple of beta testers to perfect the work. One strongly emphasized comment you receive is that the application lacks the proper reporting capabilities. The beta tester adds that retrieving a single address or phone number is a time-consuming process, because you have to scroll and manually find the address or phone number you want. He suggests that you program some sort of doodad into your application to allow easy address searching. ANALYSIS After reading the comment from one of the beta testers, you realize that reporting in your application is not functional. You decide to implement a

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

Chapter 4 QUERYING AND MODIFYING DATA 235 table structure and its columns, constraints, and indexes remain intact. The counter used by any identity columns is reset to the seed value for the column. If you want to retain the identity counter, use DELETE instead. The following is an example of a DELETE statement: DELETE from testTable WHERE phone=2802695 Removal of data may impact other tables if cascading deletions has been specified for a relationship. This could mean the removal of other records based on a single DELETE operation. Also, a relationship definition may prevent the deletion of data and return an error condition to the operation. Updating Data Data that already exists may need to be modified with newer values as time passes; this type of data modification is known as updating. Data can be updated with the UPDATE statement, very much as it is deleted and inserted. An UPDATE execution is actually an INSERT and DELETE operation. The DELETE operation occurs when the old value is removed, and the INSERT occurs when the new value is added, thus creating an UPDATE effect. The basic syntax for the UPDATE statement is as follows: UPDATE table_name SET column_name = expression WHERE condition Where: table_name is the name of the table to be updated. column_name = expression is the new value assigned to a column. condition is a specified WHERE condition. Take the query in Figure 4.7, for example, which updates the table created previously, TestTable. Just as with the input of data, any alterations made to the data are subject to the rules and constraints as defined in the table schema. Any data modifications made that do not meet these defined standards result in errors being generated that the front-end application may have to trap and control.

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