Solve C 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.

Computer Networking: A Top-Down Approach (7th Edition)
7th Edition
ISBN:9780133594140
Author:James Kurose, Keith Ross
Publisher:James Kurose, Keith Ross
Chapter1: Computer Networks And The Internet
Section: Chapter Questions
Problem R1RQ: What is the difference between a host and an end system? List several different types of end...
icon
Related questions
Question

Solve C

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(StudentIDTextbookID, 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(SalesIDStudentIDTextbookID, 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.

Expert Solution
trending now

Trending now

This is a popular solution!

steps

Step by step

Solved in 4 steps with 2 images

Blurred answer
Recommended textbooks for you
Computer Networking: A Top-Down Approach (7th Edi…
Computer Networking: A Top-Down Approach (7th Edi…
Computer Engineering
ISBN:
9780133594140
Author:
James Kurose, Keith Ross
Publisher:
PEARSON
Computer Organization and Design MIPS Edition, Fi…
Computer Organization and Design MIPS Edition, Fi…
Computer Engineering
ISBN:
9780124077263
Author:
David A. Patterson, John L. Hennessy
Publisher:
Elsevier Science
Network+ Guide to Networks (MindTap Course List)
Network+ Guide to Networks (MindTap Course List)
Computer Engineering
ISBN:
9781337569330
Author:
Jill West, Tamara Dean, Jean Andrews
Publisher:
Cengage Learning
Concepts of Database Management
Concepts of Database Management
Computer Engineering
ISBN:
9781337093422
Author:
Joy L. Starks, Philip J. Pratt, Mary Z. Last
Publisher:
Cengage Learning
Prelude to Programming
Prelude to Programming
Computer Engineering
ISBN:
9780133750423
Author:
VENIT, Stewart
Publisher:
Pearson Education
Sc Business Data Communications and Networking, T…
Sc Business Data Communications and Networking, T…
Computer Engineering
ISBN:
9781119368830
Author:
FITZGERALD
Publisher:
WILEY