SQL Practice exercises

Exercise chapter 2

2-1. a. Create a table called Cust with a customer number field as a fixed-length character string of 3, an address field with a variable character string of up to 20, and a numeric balance of five digits.

b. Insert values in to the table with INSERT INTO .. VALUES. Use the form of INSERT INTO .. VALUES that requires that you to have a value for each attribute; therefore, if you have a customer number, address, and balance, you must insert three values with INSERT INTO .. VALUES.

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

d. Display the table with a simple SELECT.

2-2. Show a listing of the customers from Exercise 2-1 in balance order (high to low) and use ORDER BY in your SELECT.

2-3. From the Student-Course database, use the Student table to display the student names, classes, and majors for freshmen or sophomores (class <= 2) in descending order of class (note: if you havent created the synonyms of all the tables in the Student-Course database as per Chapter ones exercises, you will need to use rearp.Student instead of Student in the query).

From your Cust table, show a listing of only the customer balances in ascending order where balance > 400. (You can choose some other constant if you want, for example, balance <= 600, and so on. The results will depend on your data.)

2-5. a.

Create another table with the same types as Cust but without the customer address. Call this table Cust1. Use attribute names cnum for customer number and bal for balance. Load the table with the data you have in the Cust table with one less row. Use an INSERT INTO .. SELECT with appropriate attributes and an appropriate WHERE clause.

b. Display the resulting table. If it appears ok, COMMIT your work.

c. Assuming that you have COMMITted in step b, delete about half of your rows from Cust1 (DELETE FROM Cust1 WHERE bal < some value [or bal > some value, etc.]).

d. Show the table after you have deleted the rows. e. Undelete the rows with ROLLBACK. f. Display the table with the reinstated rows.

g. Delete one row from the Cust1 table and SAVEPOINT point1. Display the table.

h. Delete another row from the table and SAVEPOINT point2. Display the table.

i. ROLLBACK to SAVEPOINT point1, display the table, and explain what is happening. j. Try to ROLLBACK to SAVEPOINT point2 and see what happens and explain it.

Using the Cust1 table from the Exercise 2-5, COMMIT the table as it exists.

2-6. a. b. Alter the table by adding a date_opened column of type date.

After each of the following, display the table.

c. Set the date_opened value in all rows to 01-JAN-19 and COMMIT.

d. Set all balances to zero, display the table, then ROLLBACK the action and display again.

e. Set the date_opened value of one of your rows to 21-OCT-20 and display.

f. Change the data-type of the balance attribute in Cust1 to number (8,2). Display the table. Set the balance for one row to 888.88 and display the table again.

g. Try changing the data-type of balance to NUMBER (3,2). What happens? Why does this happen?

h. Change the values of all dates in the table to the system date using SYSDATE. i. When you have finished the exercises (but be sure you are finished), DROP TABLE Cust1 to

delete the table. Use SELECT * FROM tab to be sure that you dropped Cust1.

For the next three problems, use the Student table from our Student-Course database.

2-7. Using the Student table, list the snames, majors, and class of all students who are ART major and juniors.

2-8. Using the Student table, list the snames, majors, and class of all students who are ART majors or juniors.

2-9. From the Student table, list the snames, majors and class of all sophomores, juniors and seniors. Use the BETWEEN operator for this.