本次数据库代写Database的主要内容是在提供的一个包含硬件商品信息和供应商信息的数据库中，完成17个问题的SQL查询语句，并且要求使用文件来测试结果的正确性。

The main content of this Database Assignment Writing Service is to complete the SQL query statement of 17 questions in a database containing hardware commodity information and supplier information, and require the use of file to test the correctness of the results.

For this assignment, you’ll be writing SQL statements against a schema containing information about hardware stores. We will provide a script that checks your answers.

To begin, download the database and the test script (they should both be in a folder called dist). For reference, here is the schema of the database:

CREATE TABLE customer ( id INTEGER PRIMARY KEY, customer_name TEXT, contact_number TEXT );

CREATE TABLE supplier ( id INTEGER PRIMARY KEY, supplier_name TEXT UNIQUE, contact_number TEXT );

CREATE TABLE product ( id INTEGER PRIMARY KEY, supplier_id INTEGER REFERENCES supplier(id), product_name TEXT, product_price INTEGER, UNIQUE(supplier_id, product_name) );

CREATE TABLE purchase ( id INTEGER PRIMARY KEY, customer_id INTEGER REFERENCES customer(id), purchase_date REAL, store_id INTEGER REFERENCES store(id) );

CREATE TABLE store ( id INTEGER PRIMARY KEY, store_name TEXT, store_address TEXT );

CREATE TABLE purchase_product (purchase_id INTEGER REFERENCES purchase(id), product_id INTEGER REFERENCES product(id), quantity INTEGER);

The database in the dist folder is a SQLite database. If you use a *NIX (UNIX or Linux) platform (like MacOS or Ubuntu), you should be able to cd into the dist directory and enter the SQLite prompt like this:

$ sqlite3 pa1.db SQLite version 3.26.0 2018-12-01 12:34:55 Enter ".help" for usage hints.

sqlite>

If you are on another platform (like Windows), you can either use one of the Mac computers in Vertica, SSH to one of the CS machines (e.g., diadem or tiara), or attempt to make things work in Windows (Ryan and the TAs can help you with all of these options but the last).

You can enter queries (terminated by a semicolon) and see the results, or you can type special SQLite commands (that begin with a period). For example, to see all the tables in the database, type .tables.

To practice, enter this query into the prompt and ensure you get the correct result:

sqlite> SELECT * FROM customer ORDER BY id LIMIT 10; 
1|Travis Matthews|999.607.0279 
2|Leon Kirby|951.476.9035 
3|Jessenia Bruce|336.918.2698 
4|Jarvis Compton|137-942-2079 
5|Aleen Trevino|1-219-541-7474 
6|Gary Burton|1-501-401-1323 
7|Reynaldo Fernandez|086.908.9278 
8|Perry Carney|(591) 264-5446 
9|Pearly William|(456) 054-1783 
10|Jere Foley|+1-(307)-819-7679

You should write your answer to each question in its own file, named after the question in the pattern q{question_number}.sql. For example, your answer to question 7 should go in a file called q7.sql. These SQL files must be placed in the dist directory.

You can then run grade.py script, which will print out whether or not each of your queries is generating the right answer. Keep in mind the script cares about the order of both the rows and columns of your answer, so read each problem carefully.

Questions

Use distinct when it semantically makes sense. If not specified, ordering should be ascending.

1. Find the contact number for customer Krysten Wallace
2. How many different types of screws does Metzger’s sell (you can check to see if the product name starts with “Screw”)?
3. When was customer Lezlie Clements’s most recent purchase (as a Julian day)?
4. List the names of all the products purchased by customer Reid Mathews and their corrosponding supplier name and supplier contact numbers, ordered by product name.
5. List the names and contact numbers of all customers who purchased a product from supplier MagnaSolution, ordered by customer name.
6. List the number of products that are supplied by at least two different suppliers. You may not use a GROUP BY clause.
7. List the top 10 product names by sold quantity, across all suppliers and all purchases, along with the quantity sold. Sort the result by the quantity with the highest quantity first.
8. List the top 10 product names by gross income (quantity * price), across all suppliers and all purchases, along with the gross income. Sort the result by the gross income with the highest income first.
9. List all ladders with multiple suppliers, along with the supplier that offers the lowest price for the given product.
10. List the names of all customers who have purchased a product that could have been purchased from a supplier with a lower price by at least 5 dollars (500 cents), ordered by customer_name
11. List all customers who have never made a purchase at the Diamond Drive store.
12. List the names of all customers who purchased Tool 1717 and then later purchased O-Ring 1736, ordered by customer name.
13. List the name and number of purchases made at each store, sorted by store name.
14. List the name and total number of (non-distinct) items purchased at each store, sorted by store name.
15. List the name and total number of (non-distinct) items purchased at each store, sorted by store name, after or on 2018-11-30.
16. List the name and total number of (non-distinct) items purchased at each store, sorted by store name, for the months of October (before 2018-11-01), November (between 2018-11-01 and 2018-12-01), and December (after 2018-12-01). Your output should look like this, with ??? replaced with correct values:
17. List each customer and the store where they have made the most purchases, sorted by customer name.