代写MIS 385 SQL Queries and MySQL workbench Assignment代做SQL语言程序

MIS 385

SQL Queries and MySQL workbench Assignment

Part A - SQL queries

Submission instructions

What to submit:

● One PDF file with the SQL queries code.

Questions

Download the database bookstore.mdb. This database keeps track of the inventory of books, orders, and customers in a bookstore.

Create a SQL query for each of the questions below. First, run the code on Ms Access, then copy the code to a Word file, and finally, after you have all codes, convert the Word file to a PDF and submit it.

Note: Assign aliases in the queries whenever necessary (please use a short name that briefly describes the information on the corresponding column).

1. For every book authored by C.J. Cherryh, show the ISBN, title, and publication year. Sort the books by title in alphabetical order.

2. Show the orders placed by the customer with customer number 2. Sort the orders by date.

3. Show the total number of copies on hand (by summing number_on_hand) the bookstore has.

4. Show the total number of titles authored by Anne Rice offered by the bookstore.

5. Show the number of orders from customer 11 that have not yet been filled (order_filled is of Yes/No type, to compare simply do order_filled = Yes or order_filled=No).

Part B - Creating a database in MySQL Workbench

Submission instructions

What to submit:

- The .sql obtained after exporting the database;

- The pdf with the relationships diagram.

Questions

Consider the following database outline:

ZIPCODE (Zip, City, State)

STUDENT (StudentID, FirstName, LastName, Address, Zip, Phone, Email, ExpectedGradDate)

         Zip foreign key to ZIPCODE

EMPLOYER (EmployerID, Name, Address, Zip, MainPhone)

         Zip foreign key to ZIPCODE

RECRUITER (RecruiterID, FirstName, LastName, Address, Zip, Phone, Email, EmployerID)

         EmployerID foreign key to EMPLOYER

         Zip foreign key to ZIPCODE

INTERVIEW (InterviewID, ScheduledStart, ActualStart, ScheduledEnd, ActualEnd, RoomNumber, Comments, StudentID, RecruiterID)

         StudentID foreign key to STUDENT

         RecruiterID foreign key to RECRUITER

It is recommended that you watch the videos on MySQL workbench and work on this part of the assignment simultaneously.

When creating a database in MySQL it is important to follow the following sequence of steps in order to prevent issues: Create the database (schema) -> Create all tables and their columns -> Create the foreign keys -> Add data to the tables.

1. Start by creating a schema in MySQL workbench to hold the database above.

2. Create each table and respective columns. Make sure you assign a suitable data type for each column. Do not forget to assign the primary key for each table, and recall that the data type of a foreign key must be the same as the data type of the primary key it corresponds to.

Before proceeding to the next step, make sure the tables you created are correct. It may be helpful to close all tables you created, and open them again, to make sure everything is as you expect.

3. Create the foreign keys.

Before proceeding to the next step, make sure the modifications you made in the tables are correct. It may be helpful to close the tables you created, and open them again, to make sure everything is as you expect.

4. Enter some data (at least three records) in each table.

5. Export the database you created (follow the instructions I provided on this). A .sql file will be created. After you export the database, import it back to MySQL Workbench to make sure it works (again follow the instructions). Do not edit the .sql file. You will submit this file.

6. In MySQL Workbench, create the ER diagram of the database and export it to a PDF. You will submit this PDF.