Answer to Chapter 3 of O'Reilly Learning Sql on SQL Server 2005

===============================================================================
3.9. Review Questions
1. The INSERT INTO .. VALUES option will insert rows into the ___end______ of a table.

2. While you are inserting values into a table with the INSERT INTO .. VALUES option,

does the order of the columns in the INSERT statement have to be the same as the order of the

columns in the table?
A: If data has to be entered with different order of the table, the columns must be

specified in the INSERT INTO statement.

3. While you are inserting values into a table with the INSERT INTO .. SELECT option,

does the order of the columns in the INSERT statement have to be the same as the order of the

columns in the table?
A: If data has to be entered with different order of the table, the columns must be

specified in the INSERT INTO statement or in the SELECT statement.

4. When would you use an INSERT INTO .. SELECT option versus an INSERT INTO .. VALUES

option? Give an example of each.
INSET INTO inserts only one row each time. INSERT INTO .. SELECT could be used insert many

rows once.

5. What does the UPDATE command do?

Change the value in the table.

6. Can you change the data type of a column in a table after the table has been created?

If so, which command would you use?

ALTER TABLE table name ALTER column_name column_type

7. Will SQL Server 2005 allow you to reduce the size of a column?
Yes, when the exsiting data are not large than the new column size.

8. What integer data types are available in SQL Server 2005?
INT, SMALLINT, TINYINT, MONEY, SMALLMONEY, BIGINT

9. What is the default value of an integer data type in SQL Server 2005?

NULL

10. What decimal data types are available in SQL Server 2005?

REAL, FLOAT, DECIMAL

11. What is the difference between a CHAR and a VARCHAR datatype?

CHAR always occupy the space specified by Data type. VARCHAR only occupy the space according

to the real content.

12. Does Server SQL treat CHAR as a variable-length or fixed-length column? Do other SQL

implementations treat it in the same way?

Yes.

13. If you are going to have too many nulls in a column, what would be the best data type

to use?

VARCHAR

14. When columns are added to existing tables, what do they initially contain?

NULL or the default value defined in schema

15. What command would you use to add a column to a table in SQL Server?

ALTER TABLE ... ADD COLUMN ...

16. In SQL Server, which data type is used to store large object data types?

IMAGE

17. If I do not need to store decimal places, what would be a good numeric data type to

use?

INT

18. If I need to store decimal places, but am not worried about rounding errors, what

would be a good data type to use?

FLOAT or REAL

19. Should a column be defined as a FLOAT if it is going to be used as a primary key?

NO.
-------------------------------------------------------
3.10. Exercises

Unless specified otherwise, use the Student_course database to answer the following questions.

Also, use appropriate column headings when displaying your output.
1. Create a table called Cust with a customer number as a fixed-length character string

of 3, an address with a variable-length character string of up to 20, and a numeric balance.

CREATE TABLE Cust
( cust_no CHAR(3),
addr VARCHAR(20),
balan MONEY)

a. Insert values into the table with INSERT INTO .. VALUES option. Use the form of INSERT

INTO .. VALUES option that requires you to have a value for each column; therefore, if you

have a customer number, address, and balance, you must insert three values with INSERT INTO ..

VALUES option.

INSERT INTO Cust
VALUES ('001', 'Beijing', 12)

b. Create at least five tuples (rows in the table) with customer numbers 101 to 105 and

balances between 200 to 2000.

INSERT INTO Cust
VALUES ('101', 'Beijing', 200)
INSERT INTO Cust
VALUES ('102', 'shanghai ', 2000)
INSERT INTO Cust
VALUES ('103', 'guangdong ', 1000)
INSERT INTO Cust
VALUES ('104', 'jiangxi', 1100)
INSERT INTO Cust
VALUES ('105', 'nanning', 566)

c. Display the table with a simple SELECT.

SELECT * FROM Cust

d. Show the balances for customers with customer numbers 103 and 104.

SELECT cust_no as [Customer number], balan as Balance
FROM Cust
WHERE cust_no = '103' OR cust_no = '104'

SELECT cust_no as [Customer number], balan as Balance
FROM Cust
WHERE cust_no in ('103', '104')

e. Add a customer number 90 to your Cust table.

INSERT INTO Cust (cust_no) Values('90')

f. Show a listing of the customers in balance order (high to low), using ORDER BY in your

SELECT. (Result: Five tuples, or however many you created.)

SELECT *
FROM Cust
ORDER BY balan DESC

2. From the Student table (from our Student_course database), display the student names,

classes, and majors for freshmen or sophomores (class <= 2) in descending order of class.

SELECT sname as [Student Name], class, major
FROM Student
WHERE class <= 2
ORDER BY class DESC

3. From your Cust table, show a listing of only the customer balances in ascending order

where balance > 400. (You can choose some other constant or relation if you want, such as

balance <= 600.) The results will depend on your data.

SELECT *
FROM Cust
WHERE balan > 400
ORDER BY balan

 

4. Create another two tables with the same data types as Cust but without the customer

addresses. Call one table Cust1 and the other Cust2. Use column names cnum for customer number

and bal for balance. Load the table with the data you have in the Cust table with one less

tuple. Use an INSERT INTO .. SELECT with appropriate columns and an appropriate WHERE clause.

a. Display the resulting tables.

CREATE TABLE Cust1
( cnum CHAR(3),
bal MONEY)

CREATE TABLE Cust2
( cnum CHAR(3),
bal MONEY)

INSERT INTO Cust1 (cnum, bal)
SELECT cust_no, balan
FROM Cust
WHERE balan < 2000

5. Alter the Cust1 table by adding a date_opened column of type DATETIME. View the table

definition of Cust1.

ALTER TABLE Cust1
ADD date_opened DATETIME

a. Add some more data to the Cust1 table by using the INSERT INTO .. VALUES option.
After each of the following, display the table.

INSERT INTO Cust1
VALUES ('201', 500, '2010-10-10')
SELECT * FROM Cust1

b. Set the date_opened value in all rows to '01-JAN-06'.
UPDATE Cust1
SET date_opened = '01-JAN-06'
SELECT * FROM Cust1

c. Set all balances to zero.
UPDATE Cust1
SET bal = 0
SELECT * FROM Cust1

d. Set the date_opened value of one of your rows to '21-OCT-06'.
UPDATE Cust1
SET date_opened = '21-OCT-06'
WHERE cnum = '101'
SELECT * FROM Cust1

e. Change the type of the balance column in the Cust1 table to FLOAT. Display the table

definition. Set the balance for one row to 888.88 and display the table data.
ALTER TABLE Cust1
ALTER COLUMN bal FLOAT
UPDATE Cust1
SET bal = 888.88
WHERE cnum = '101'
SELECT * FROM Cust1

f. Try changing the type of balance to INTEGER. Does this work in SQL Server?
ALTER TABLE Cust1
ALTER COLUMN bal INTEGER

g. Delete the date_opened column of the Cust1 table.
ALTER TABLE Cust1
DROP COLUMN date_opened 

h. When you are finished with the exercise (but be sure you are finished), delete the

tables Cust, Cust1, and Cust2.
DROP TABLE Cust
DROP TABLE Cust1
DROP TABLE Cust2