Sql statements for introductory programming

Write sql statements to perform the following tasks. When complete turn in your SQL file into blackboard assignment

1. Write SQL statements to create and use a database called Homework (3)

2. Write SQL statement needed to create the books table. (10 pts)
• Choose appropriate data types:
The ISBN will always be 17 characters longs
The price of the most expensive book will never be more than $999.99
The discount will always be something less than 100%. (Between 0.00–1.0)
• The ISBN is the Primary Key.
• The Title is a required field.
• The discount will have a default value of 0.00

3. Insert the following information into the Books table created in problem 1. Use quotes around text datatypes but not numeric data types (7 pts)

ISBN Title Description PubDate Price Discount
978–0–596–52684–9 SQL Book on SQL 9/1/2007 44.99 .15
978–1–305–57593–6 Excel 2013 Excel textbook 7/25/2013 125.25 .20
• After you have inserted the data type SELECT * from books and display your data.

4. Write SQL statements needed to create the following 2 tables. Use appropriate datatypes. NOTE: (For SS# in student table you can’t use # sign, you will have to type SSN) (10)
a. Make First Name and Last name required fields in STUDENT table
b. Make the GPA column in the STUDENT table DEFAULT to 0.00
c. Put a UNIQUE constraint on the Phone column of the INTERNSHIP and STUDENT table
d. Make each primary key (PK )an auto increment
e. Make the Category column in the INTERNSHIP table an ENUM data type with ‘Paid’, ‘Credit’, ‘NonProfit’ as the choices.
f. Research what datatype would be good for YEAR

5. Use a SHOW command to display all of the tables in the Homework database (not all databases) (2)

6. Alter the Student table and add a UNIQUE constraint to the email column. (3)

7. Alter the Student table to remove the Class column from the table. (3)

8. Alter the Student table to put the SSN column after Zip (3)

9. Write a SQL statement to add yourself to the Student table (using made up information). (3)

10. Write a SQL statement to add 3 more students with one INSERT INTO statement. Only add their first and last names and email address. (3)

11. Write an ALTER TABLE statement to change the ‘Phone’ column to ‘Mobile’ in the Student table (3)