Solve B I. This Project asks you to submit a SQL text file (e.g. project2.sql) with all your answers to all the questions listed in this assignment. Your answers will be written in SQL format. All SQL statements will be tested in a MySQL database including: CREATE TABLE, ALTER TABLE, INSERT and SELECT. You must use the following table structures for your MySQL DDL and DML commands: STUDENT(StudentID, LastName, FirstName, Address, City, State, Zip, Phone) StudentID will be automatically increased integer identifier Choose VARCHAR or CHAR based on your personal understanding of the fields for the rest part of the relation/table STUDENT_TEXTBOOK(StudentID, TextbookID, Class, Semester, Year) Semester would be CHAR Year would be integer TEXTBOOK(TextbookID, Description, Price, Location, BookstoreID) TextbookID will be automatically increased integer identifier Price is a decimal with 2 decimal places after zero Description and Location can be VARCHAR or CHAR based on your personal understanding of the fields BOOKSTORE(BookstoreID, Name, Address, City, State, Phone) BookstoreID will be automatically increased integer identifier Choose VARCHAR or CHAR based on your personal understanding of the fields for the rest part of the relation/table SALES(SalesID, StudentID, TextbookID, NumberofPurchase, SalesDate) NumberofPurchase is number of copies purchased NOTE: - You must use MySQL. - Create tables with columns, data types and populate your own data - Underscore represents a part of a primary key - Underscore and italic represent both part of the primary key and foreign key. - Italic represent foreign key II. PLACE ANSWERS in a text file (e.g. project2.sql) NOT A WORD DOC A. List the 5 DDL "CREATE TABLE" statements including the primary keys. (10 points) B. List any DDL statements that add the foreign key(s) constraints using "ALTER TABLE". (10 points) C. List at least 3 DDL INSERT statements (per table) to populate data in all 5 tables. (10 points) D. Write a separate SQL query for each of the 10 questions below. (70 points) 1. List all Student LAST and FIRST NAMES who are from a city starting with a selected word with at least two letters long. 2. List all Student IDs who have textbook sales totals greater than a selected value. 3. List the last name, first name, and phone of the Students who made a purchase with three selected SalesIDs. Use a subquery. 4. Answer the previous question but use an inner join. 5. List all student names and phone numbers. If they had purchased textbooks in the year 2012, then list the textbook descriptions. Otherwise, allow the textbook description to be null. You can use the default data format DD-MON-YY. (Hint: you will use a left outer join.) 6. List last name, first name, and phone of students who have purchased a textbook that has a price greater than a given number/value. (Use subquery). 7. Rewrite the last questions using an inner join and alias for table names. 8. List last name, first name, and phone of Students who have purchased a Textbook that was supplied by a bookstore with a name that begins with a given English letter. (Use subquery). 9. Rewrite the previous question using an inner join of the tables and alias for table names. 10. List the total "sum" of textbook prices for each student in descending order by last name and first name, class, textbook description AND total purchases for a given year. Use an inner join for the tables. NOTE: The business rule for the STUDENT_TEXTBOOK table assumes that a student will only purchase one textbook at a time. Achha Thank You.

Solve B

I. This Project asks you to submit a SQL text file (e.g. project2.sql) with all your answers to all the questions listed in this assignment. Your answers will be written in SQL format. All SQL statements will be tested in a MySQL database including: CREATE TABLE, ALTER TABLE, INSERT and SELECT. You must use the following table structures for your MySQL DDL and DML commands:

STUDENT(StudentID, LastName, FirstName, Address, City, State, Zip, Phone)

• StudentID will be automatically increased integer identifier
• Choose VARCHAR or CHAR based on your personal understanding of the fields for the rest part of the relation/table

STUDENT_TEXTBOOK(StudentID, TextbookID, Class, Semester, Year)

• Semester would be CHAR
• Year would be integer

TEXTBOOK(TextbookID, Description, Price, Location, BookstoreID)

• TextbookID will be automatically increased integer identifier
• Price is a decimal with 2 decimal places after zero
• Description and Location can be VARCHAR or CHAR based on your personal understanding of the fields

BOOKSTORE(BookstoreID, Name, Address, City, State, Phone)

• BookstoreID will be automatically increased integer identifier
• Choose VARCHAR or CHAR based on your personal understanding of the fields for the rest part of the relation/table

SALES(SalesID, StudentID, TextbookID, NumberofPurchase, SalesDate)

• NumberofPurchase is number of copies purchased

NOTE:

- You must use MySQL.

- Create tables with columns, data types and populate your own data

- Underscore represents a part of a primary key

- Underscore and italic represent both part of the primary key and foreign key.

- Italic represent foreign key

II. PLACE ANSWERS in a text file (e.g. project2.sql) NOT A WORD DOC

A. List the 5 DDL "CREATE TABLE" statements including the primary keys. (10 points)

B. List any DDL statements that add the foreign key(s) constraints using "ALTER TABLE". (10 points)

C. List at least 3 DDL INSERT statements (per table) to populate data in all 5 tables. (10 points)

D. Write a separate SQL query for each of the 10 questions below. (70 points)

1. List all Student LAST and FIRST NAMES who are from a city starting with a selected word with at least two letters long.

2. List all Student IDs who have textbook sales totals greater than a selected value.

3. List the last name, first name, and phone of the Students who made a purchase with three selected SalesIDs. Use a subquery.

4. Answer the previous question but use an inner join.

5. List all student names and phone numbers. If they had purchased textbooks in the year 2012, then list the textbook descriptions. Otherwise, allow the textbook description to be null. You can use the default data format DD-MON-YY. (Hint: you will use a left outer join.)

6. List last name, first name, and phone of students who have purchased a textbook that has a price greater than a given number/value. (Use subquery).

7. Rewrite the last questions using an inner join and alias for table names.

8. List last name, first name, and phone of Students who have purchased a Textbook that was supplied by a bookstore with a name that begins with a given English letter. (Use subquery).

9. Rewrite the previous question using an inner join of the tables and alias for table names.

10. List the total "sum" of textbook prices for each student in descending order by last name and first name, class, textbook description AND total purchases for a given year. Use an inner join for the tables. NOTE: The business rule for the STUDENT_TEXTBOOK table assumes that a student will only purchase one textbook at a time.

Achha Thank You.