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
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
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.
Trending now
This is a popular solution!
Step by step
Solved in 2 steps
