Laboratory 3

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.

   1. 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.

   2. Create at least five tuples (rows in the table) with customer numbers 101 to 105 and balances between 200 to 2000.

   3. Display the table with a simple SELECT.

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

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

   6. 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.)

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.

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.

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.

   1. Display the resulting tables.

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

   1. Add some more data to the Cust1 table by using the INSERT INTO .. VALUES option.

      After each of the following, display the table.

   2. Set the date_opened value in all rows to '01-JAN-06'.

   3. Set all balances to zero.

   4. Set the date_opened value of one of your rows to '21-OCT-06'.

   5. 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.

   6. Try changing the type of balance to INTEGER. Does this work in SQL Server?

   7. Delete the date_opened column of the Cust1 table.

   8. When you are finished with the exercise (but be sure you are finished), delete the tables Cust, Cust1, and Cust2.