Module 4 - Character, Number, and Miscellaneous Functions

Assignments

Put your answers to the following assignment questions in a Notepad++ document, and save the docu- ment as module4 firstname lastname.txt; submit it to the Assignment 4 Dropbox in Momentum. In your document, you dont need to type the original questions, but must give question numbers and their corresponding answers. You need to test your SQL statements to make sure they have no syntax errors and work as expected. As a way of verifying your statements, you may include more information than specified. However, you must remove the non-specified information and include only the specified information in your answers for the SQL statement questions. You lose points if you include non-specified information in your statements.

NO RESUBMISSION IS ACCEPTED ONCE YOUR ASSIGNMENT IS GRADED, SO DOUBLE CHECK YOUR ANSWERS BEFORE SUBMISSION.

1. Write a SQL statement to list cities and states with city first letter capitalized and state in uppercase. 2. Write a SQL statement to remove all the zeros from this number: 0008954210000. 3. Write a SQL statement to list the first four characters of the first name of each instructor.

4. Write a SQL statement to list student names: there must be a letter z or Z in each first name; order the result by the length of last name. 5. Write a SQL statement to show only the smallest integer from this arithmetic operation: 19/7. 6. Write a SQL statement to list instructor last names, where a last name contains no more than seven characters. 7. Write a SQL statement to list student names this way: last name, followed by a comma and a space, followed by first name. 8. Write a SQL statement to change the phrase Today is Monday to Today is Tuesday. 9. What is the DUAL table? Does it belong to the STUDENT schema? 10. A calculation with a null always yields another null; which function can be used to avoid such a case?