postgreSQL

Provide the SQL statement that creates a table named "products" with the * following attributes: * * - "sku" : fixed length character string of length 10 * - "name" : variable length character string of length 50 * - "category" : variable length character string of length 30 * - "price" : fixed point number with 10 digits, 2 decimals * - "description" : variable length character string of maximum length * - "ship_weight" : fixed point number with 6 digits, 1 decimal (stored in ounces) * - "date_listed" : timestamp without time zone * - "last_update" : timestamp without time zone

QUESTION 2 (1 point) * -------------------- * * Provide the SQL statement that populates the "products" table with values as * shown in the following table. (Note: try to run this in one statement, not * multiple INSERT statements) * * +-----------+-----------------------------------------+---------------------------+-------+-----------------------------------------------+-------------+---------------------+---------------------+ * | sku | name | category | price | description | ship_weight | date_listed | last update | * +-----------+-----------------------------------------+---------------------------+-------+-----------------------------------------------+-------------+---------------------+---------------------+ * | | | | | The Philips Norelco Shaver 4500 lets you | 14.4 | 2016-02-20 09:12:00 | 2018-01-12 15:23:00 | * | | | | | shave any way you want. The AquaTec seal | | | | * | TSD992456 | Philips Norelco Shaver 4500 | Personal Care | 54.99 | gives you the option of either a comfortable | | | | * | | | | | dry shave or a refreshing wet shave with or | | | | * | | | | | without shaving cream or gel. | | | | * +-----------+-----------------------------------------+---------------------------+-------+-----------------------------------------------+-------------+---------------------+---------------------+ * | | | | | Ticonderoga has a tradition of fine quality | 5.8 | 2015-12-01 07:34:50 | 2015-12-01 07:34:50 | * | | | | | writing instruments in the US for over 100 | | | | * | SPE237105 | Ticonderoga Pencils #2 HB Soft, 30-Pack | Office Products | 4.99 | years. The exclusive graphite core features | | | | * | | | | | a proprietary formula focusing on graphite | | | | * | | | | | mined from carefully controlled sources to | | | | * | | | | | deliver extra smooth performance. | | | | * +-----------+-----------------------------------------+---------------------------+-------+-----------------------------------------------+-------------+---------------------+---------------------+ * | KLQ509364 | Anker PowerCore 10000 PD | Cell Phones & Accessories | 34.99 | | 5.6 | 2019-04-15 15:05:23 | | * +-----------+-----------------------------------------+---------------------------+-------+-----------------------------------------------+-------------+---------------------+---------------------+ * */

* QUESTION 3 (1 point) * -------------------- * * Provide the SQL statement that returns all attributes and tuples from table * "products", of category "Office Products".

QEUSTION 4

Remember that the "ship_weight" attribute stores values in ounces. Provide * the SQL statement that returns the sku, name, category, prince and lastly * ship_weight in grams (note: 1 ounce = 28.3495 grams). Rename ship_weight * to ship_weight_grams. *

QUESTION 5 (1 point) * -------------------- * * Provide the SQL statement that returns the "name" and "price" attributes * of all "Personal Care" products that contain the word "shave" anywhere in * their description.

QUESTION 6 (1 point) * -------------------- * * Provide the SQL statement that creates a relation named "vendors" with * attributes "vendor_id" (integer), "name" (variable length character string * of length 60), "country" (variable length character string of length 30). * Do not implement integrity constraints.

QUESTION 7 (1 point) * -------------------- * * Provide the SQL statement that alters relation "products" adding an attribute * called "vendor_id" (integer).

QUESTION 8 (1 point) * -------------------- * * Provide the SQL statement that returns the product name with the maximum * ship_weight of "Cell Phones & Accessories" products. (note: for this and the * following questions, it would be good do insert additional data in order to * test your answers)

QUESTION 9 (1 point) * -------------------- * * Provide the SQL statement that returns the category and average price per * product category, of all products listed after January 1st, 2018. * (hint: use GROUP BY)

QUESTION 10 (1 point) * --------------------- * * Provide the SQL statement that returns the sku, name, category, price and * vendor name of all products supplied by a vendor from the "USA". * (Hint: we are not implementing joins yet, use the cartesian product. Also, * assume that the "vendor_id" in the "vendors" relation can correlate with the * new "vendor_id" attribute of the "products" relation). *

QUESTION 11 (1 point) * --------------------- * * Provide the SQL statement that returns the product name, category, price, * ship_weight and vendor country of all products. Display the results by * descending ship_weight and date_listed. (Hint: same as Q10)

QUESTION 12 (1 point) * --------------------- * * Provide the SQL statement that returns the category, vendor_id and total * number of products for each category/vendor_id combination.

*

QUESTION 13 (1 point) * --------------------- * * Provide the SQL statement that returns the unique combination of category and * ship_weight from the products relation.

QUESTION 14 (1 point) * --------------------- * * Provide the SQL statement that deletes all tuples from relation "products" * of products listed prior to 2017.

QUESTION 15 (1 point) * --------------------- * * Provide the SQL statement that removes the "vendors" relation from the * database.

* * Do not implement integrity constraints.