0. Sign on to Microsoft SQL Server and get into your own database. Print the number of rows in the sys.objects table. Print the name, object_id, type, create_date, and type_desc columns (check the names!) for all rows in sys.objects which were created since January 1, 2011.
Label your output as HW5 step 0.
1a. Create a GIFTS table (give the CREATE TABLE statement you use) for information about gifts. The table will contain the name of a gift, the retail price of the gift, and the sale price for the gift. Assume that gift names are unique.
Include separate constraints which say each of the following: the sales price and the retail price must be at least 10; the sale price must be less than or equal to the retail price. (This is called a row constraint.) What is the PK for this table?
Label your output as HW5 step 1a.
1b. Enter at least 10 rows of (valid) data into the table. Show one of the INSERT statements you used to enter data (no need to show them all).
Label your output as HW5 step 1b.
1c. Print the table ordered by gift name (descending).
Label your output as HW5 step 1c.
1d. Print the table ordered by retail price (then descending order of name in case of ties).
Label your output as HW5 step 1d.
2. For each of the (non-PK) constraint rules that you set up, enter a value which violates that rule. Print the value that you entered and the error message.
Label your output as HW5 step 2.
3a. Determine how many gifts have a retail price below 20.
Label your output as HW5 step 3a.
3b. Determine how many gifts have a sale price above 10 and a retail price below 20.
Label your output as HW5 step 3b.
3c. Print the name and retail price for each gift where the sale price equals the retail price and both prices are below 20. Make sure you have at least two rows which satisfy part c.
Label your output as HW5 step 3c.
4a. Give the CREATE VIEW statement to create a view named BELOW20 consisting of all columns for each row for a gift whose retail price is below 20.
Label your output as HW5 step 4a.
4b. Print the rows in the view.
Label your output as HW5 step 4b.
4c. Using the view you just created (not the base table GIFTS), repeat all parts from step 3. What should your answers be the same as?
Label your output as HW5 step 4c.
4d. Using an UPDATE command on the view (not on the base table), change the retail price for one of the gifts which had been below 20. The new value should be above 20.
Label your output as HW5 step 4d.
4e. Print the view and the base table. By hand, circle or note the change in each one.
Label your output as HW5 step 4e.
5a. Create a PERSONS table (give the CREATE TABLE statement you use) for information about persons. The table will contain the name of a person, the person’s age, and the student’s hair color. Include constraints for the following rules: Assume that names are unique; the hair color can be …(where you pick exactly 3 possible hair colors to list); the age must be between 17 and 62. What is the PK for this table?
Label your output as HW5 step 5a.
5b. Enter at least 10 rows of (valid) data into the table. Make sure that each allowed hair color occurs at least two times.
Show one of the INSERT statements you used to enter data (no need to show them all).
Label your output as HW5 step 5b.
5c. Print the table ordered by person's name (descending).
Label your output as HW5 step 5c.
5d. Print the table ordered by hair color and then name.
Label your output as HW5 step 5d.
6. For each of the (non-PK) constraint rules that you set up, enter a value which violates the rule. Print the value that you entered and the error message.
Label your output as HW5 step 6.
7a. Give the CREATE VIEW statement to create a view named ABOVE20 consisting of all columns in PERSONS for each row that has a person whose age is above 20.
Label your output as HW5 step 7a.
7b. Print the rows in the view.
Label your output as HW5 step 7b.
7c. Count how many rows are in the view. Print this count.
Label your output as HW5 step 7c.
8. Using the sys.objects table, give the name, object_id, type, create_date, and type_desc columns for all rows which you created yourself.
By hand, circle any rows that were added in this assignment.
Label your output as HW5 step 8.