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.

If it is not already open , load the SQL Query Analyzer. Supply the logon connection information if requested .
Select the Master database from the database drop-down list box.

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.

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)     NULL,    city          varchar(20)     NULL,    state         char(2)         NULL,    country       varchar(30)     NULL  ) GO 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.

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

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.

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.

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)

Change an existing record by issuing the following UPDATE operation:

 UPDATE Employee     SET firstname = 'William'     WHERE firstname = 'Billy'

Remove an existing record using the following DELETE operation:
```
 DELETE FROM Employee     WHERE emp_id = 2 
```

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

The exercise is performed from the Query Analyzer with the TestData database selected from the database drop-down list box.
Attempt to remove an existing publisher that has Foreign Key records existing in another table:
```
 DELETE FROM Publishers     WHERE pub_id = 1 
```
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.

Review Questions

1:	How does a `DISTINCT` query provide for useful reporting to the user ?
A1:	A `DISTINCT` query eliminates duplicate rows from a resultset, providing only a single copy of each row. This is usually most useful when you want to retrieve results on a small number of columns (one or two). With the use of `DISTINCT` you can list all the states where customers come from without duplicating any state, or you may want a listing of the topics of all employee experience without duplicating a topic. With this type of information you may be able to find gaps (no customers from a particular state or no employees with a desired talent) without needlessly searching through duplications of information.
2:	What advantages can the use of a `WHERE` clause provide over the basic use of a `SELECT` or `DELETE` operation?
A2:	A `WHERE` clause defined on a `DELETE` operation can prevent the accidental removal of every record in a table. Similarly, its use in a `SELECT` operation can allow for the listing of only desired data while filtering out data that does not meet the desired criteria.
3:	How would data in the form of dates be compared against data stored as characters ?
A3:	There are many considerations for comparing date data with character data, including the actual format of the data storage, the native source and front-end application, as well as specifics of the required process. In general there are two approaches: to look at the comparison as a character comparison or a comparison between dates. Most applications will probably require date comparisons, and therefore a conversion of the character data into dates would be needed. Individual uses of a date and time that have been broken down into components is also common. If the application calls for other uses of the data in a character- related application, then turning the dates into characters may be performed. Some reporting applications have a requirement that all dates be received as character strings.
4:	What types of functions would be considered if the most important application issue centered around the use of network bandwidth?
A4:	Functions that control data flow could include those that minimize the amount of data through the selection of columns and rows of data. Other functions control security and system interactions to permit the appropriate security and compression properties for all data being transmitted.
5:	When do data deletions and modifications affect data stored in other tables?
A5:	When table definitions include constraints and rules that can interact with foreign data, there is a possibility that those interactions can effect data in those sources. The most common forms of interaction involve the maintaining of referential integrity definitions. Optional Foreign Key constraints and cascading referential integrity can restrict, remove, or alter data in a foreign data source.

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: `INSERT` `UPDATE` Change column name Increase column length Decrease column length
A1:	A, C, D. If you were to select `UPDATE` , the purpose of the command is exactly what you want to avoid. You should be able to increase the data storage size and alter a column name without affecting the internal data. However, decreasing the size for data storage results in data truncation or loss. `INSERT` used appropriately adds data but does not alter any existing values. For more information, see the section titled "Data Retrieval."
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. Provide friendly aliases for the table names . Provide friendly aliases for the column names. Create a `VIEW` with corresponding definition. Create a corresponding `DEFAULT` definition. Execute a corresponding query from the Analyzer. Create a front-end program to execute the required query.
A2:	B, E. The key to this question is that this operation is going to be performed as a "one-time" thing, so the creation of data objects would likely be avoided and views would not be warranted. However, a script that performs the activity could easily be saved if needed in the future. Table aliases may help in your development, but in this scenario column aliases provide the end-user with the necessary data definition. For more information, see the section titled "Data Retrieval."
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? SELECT * FROM Results WHERE ResultDate BETWEEN '01/01/2000' AND '01/01/2001' SELECT * FROM Results WHERE ResultDate BETWEEN '12/31/1999' AND '12/31/2000' SELECT * FROM Results WHERE ResultDate BETWEEN '12/31/1999' AND '01/01/2001' SELECT * FROM Results WHERE ResultDate BETWEEN '01/01/2000' AND '12/31/2000' SELECT * FROM Results WHERE ResultDate BETWEEN '12/31/2000' AND '01/01/2000'
A3:	D. Dates are inclusive with the `BETWEEN` function. For further details on the use of the `BETWEEN` function, see the "BETWEEN and IN" section.
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? SELECT * FROM Customers ORDER BY Country, State, City SELECT CustomerName, City, State, Country ORDER BY Country, State, City, CustomerName SELECT * FROM Customers ORDER BY City, State, Country SELECT CustomerName, City, State, Country ORDER BY City, State, Country, CustomerName SELECT CustomerName, City, State, Country ORDER BY CustomerName, City, State, Country
A4:	A. Assuming that all data from the table is needed, A is correct. It is not necessary to include all columns from a `SELECT` list when using `ORDER` . For appropriate use of `ORDER BY` , see the section "Ordering Data."
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? Correct the `FROM` clause Correct the `WHERE` clause Correct the `TOP` clause Correct the `ORDER BY` clause Correct the `DISTINCT` clause
A5:	E. Appropriate use of the `DISTINCT` clause could eliminate the recurring data being noted. To diagnose this problem, the correct syntax must be implemented. The following code represents a plausible solution: SELECT DISTINCT Country FROM Customers ORDER BY Country For further details on the use of `DISTINCT` , see the section titled "The SQL SELECT Statement."
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? The resultset has not been grouped. The data contains `NULL` values. There is an incorrect `ORDER` . Table aliases were used. Schema binding has been applied.
A6:	C. You are probably not ordering the data to achieve the desired results. Grouping of the resultset doesn't seem to be warranted because the question is asking for five rows. `NULL` values should not affect this query, though in some instances `Null` data can interfere with the results. See "Selecting Rows Based On NULL Values" for more information.
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? Data doesn't meet constraint. Referential integrity. Database is read-only. Permissions. Other applications are locking data. SQL Server Agent is not started.
A7:	F. Each of the reasons excluding the agent are very possibly a cause of the symptoms being described. The SQL Server Agent handles non-data activity on the server related to Operators, Jobs, and Events configured on the system. If the Agent is not running only these particular processes are interrupted , not the entire database. For more information, refer to "SQL Server Agent" in SQL Server Books OnLine.
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? ADO RDO DAO SQL DMO XML
A8:	A. An XML implementation may be more suited to an active server page Internet application than a LAN application. RDO and DAO represent older technologies that aren't as efficient and versatile as ADO. SQL-DMO is for development of system applications that interact with SQL Server on a non-data level. For more details about any of the technologies, see SQL Server Books OnLine.
9:	You have been chosen by your development team to provide a set of queries that will print out a variety of reports from the `Customer` table. After opening the Query Analyzer, you discover that the test database and table you just created do not appear. Which of the following is likely to solve the problem? There was an error in the script you used to create the objects, and you need to correct the error and rerun the scripts. The Query Analyzer needs to be refreshed, thus giving it access to all objects in the system. You need to create the database and table from the Enterprise Manager to ensure that temporary objects are not used. You must restart the SQL Server service so that it has access to all newly created objects.
A9:	B. With all versions of SQL Server, the interface doesn't always show newly created objects. A periodic refresh in the Enterprise Manager and the object browser within the Query Analyzer is needed to assure accuracy of the display (see the note "Analyzer Connections").
10:	You are developing a query that will look for invalid entries in a table before implementing a new `CHECK` constraint. The constraint that you are implementing will enforce data entry into the gender column of the table. Which of the following queries will seek out records that have no value or are not male or female ? (Select all that apply.) SELECT * FROM Employee WHERE Gndr = NULL or Gndr <> 'M' or Gndr <> 'F' or Gndr <> 'm' or Gndr <> 'f' SELECT * FROM Employee WHERE Gndr = NULL and Gndr <> 'M' and Gndr <> 'F' and Gndr <> 'm' and Gndr <> 'f' SELECT * FROM Employee WHERE Gndr IS NULL and Gndr <> 'M' and Gndr <> 'F' and Gndr <> 'm' and Gndr <> 'f' SELECT * FROM Employee WHERE Gndr IS NULL or Gndr <> 'M' or Gndr <> 'F' or Gndr <> 'm' or Gndr <> 'f' SELECT * FROM Employee WHERE Gndr IS NULL or (Gndr <> 'M' and Gndr <> 'F' and Gndr <> 'm' and Gndr <> 'f') SELECT * FROM Employee WHERE Gndr IS NULL or Gndr NOT IN ('M', 'F', 'm', 'f')
A10:	E, F. Both E and F produce the desired results, though in this instance the `IN` may be easier to read and use and somewhat more efficient. The exam may ask you to choose the best of several working queries, in which case F would be the best answer. For more information on the use of `IN` see the section titled "BETWEEN and IN."
11:	A local manufacturing company uses a SQL Server to receive statistical information from various points on an assembly line. The information is gathered into a table called `Production` . Date information is maintained in a standard `datetime` column called `Pdate` . You would like to prepare a query that would list the production information from the preceding day. Which of the following queries solves the problem? SELECT * FROM Production WHERE Pdate = GetDate() - 1 SELECT * FROM Production WHERE Pdate BETWEEN = GetDate() - 2 AND GetDate() SELECT * FROM Production WHERE datediff(dd, PDate, getdate()) = 1 SELECT * FROM Production WHERE datediff(dd, getdate(), PDate) = 1
A11:	C. Date data types also include information for time, which causes many comparisons to not provide the desired results. Use the `DATEDIFF` function to prevent this problem. In this case the current date would be greater then the production date on file, and therefore the first parameter must be `PDate` . For more information, see the "Date Functions" section of this chapter.
12:	You are preparing to move a test database that you have been working on over the last several months over onto a production system. You are planning to go live with the new database sometime over the next few days. All the current data will be removed and replaced with production information when the system does go live. You are performing some final tests when you notice that the column that is going to be used as the `ROWGUIDCOL` does not have any values. The ID is to be generated by the system and afterward used as a permanent value. How would you ensure that values are placed into this column as data is entered in the production system? Correct the current data by providing the missing values and then add a constraint so that this doesn't happen again. Correct the current data by providing the missing values and then add a `NEWID()` as a default for the column. Correct the current data by providing the missing values and provide a formula to generate the value of the column. Correct the current values by using an `UPDATE` operation and the `NEWID()` function and then add a `NEWID()` as a default for the column. Empty the current table into a temporary table, add a default as `NEWID()` , and load the data back in using an `INSERT INTO` operation, omitting the ID column.
A12:	E. The most efficient and quickest way of performing this task would be to get the data out before you create the default. When the data is placed back without the ID, the default will generate the missing values. See `DEFAULT` use in Chapter 3.
13:	A local car dealership maintains a list of the current inventory on a single SQL Server. The machine also takes on a number of other networking roles for name resolution. Data is going to be moved off the existing machine in a move to create a dedicated database server. You would like to execute a query that copies all the data to a test database where the table already exists. Which of the following solves the problem? INSERT INTO Test.dbo.Automobiles SELECT * FROM Inventory.dbo.Automobiles SELECT Test.dbo.Automobiles SELECT * FROM Inventory.dbo.Automobiles INSERT Test.dbo.Automobiles SELECT * FROM Inventory.dbo.Automobiles SELECT INTO Test.dbo.Automobiles SELECT * FROM Inventory.dbo.Automobiles INSERT INTO Inventory.dbo.Automobiles SELECT * FROM Test.dbo.Automobiles
A13:	C. The only query to use `INSERT` would work; `INSERT INTO` might have been appropriate if the table did not already exist. For more information, see the sections on " `INSERT` ," " `INSERT SELECT` ," and " `INSERT INTO` ."
14:	An accounting system that has been recording company financial information for the past three years is being upgraded and having a number of additional columns added to the structure of several of the existing tables. You need to ensure that all existing data remains intact during these operations. Select two. Set the database files to read-only. Set the table properties to read-only. Addition of columns shouldn't affect the database. Set permissions on the database to prevent malicious updates. Set column-level permissions to prevent updates to existing data.
A14:	C, D. You don't want to set unnecessary read-only properties that wouldn't permit any alterations to the database or the records contained therein.
15:	You administer the database server for a large lumber and building materials supplier. You want to query the materials used by a single site. Which of the following queries would suit your needs? SELECT Materials, Weight, Quantity FROM Inventory ORDER BY Site SELECT Materials, Weight, Quantity FROM Inventory WHERE Site = 4 SELECT Materials, Weight, Quantity FROM Inventory ORDER BY Quantity SELECT Materials, Weight, Quantity FROM Inventory ORDER BY Site, Quantity SELECT Materials, Weight, Quantity FROM Inventory where Materials = "Drywall"
A15:	B. The query needed is a simple `SELECT` query with a `WHERE` condition for the site. For more information, see the section titled "The SQL SELECT statement".