Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

BSYS602 Business Data Management

Workshop individual Assignment 1

Use the database schema below to work on your assignment. Before you start writing queries, you need to run the sql script file “Assign1_SQL_script.sql” to create tables and insert data into those tables. Please note that this is a long script so it will likely take a few minutes to complete.

Save your queries in a file (Yourname_assign1.sql). Submit the file to the dropbox under “Workshop Assessments” link.
(Due date: Midnight of Friday, 18 August 2023)

Write SQL commands for the following requirements.

(1) List customer details for those customers who have balance greater than or equal to 2000 and less than or equal to 5000. Customer first name and last name should be displayed in the same column with an appropriate heading. For example, a customer with the first name, “Marina” and the last name, “Hilton”, should be displayed as “Marina Hilton” in the result.

(2) List product details for those products with the product base “water” and product category is either “top coat” or “primer”. Please note that the spelling of the column values may have different combinations of letter case in the database but all records should appear in the result as long as they meet the requirements regardless of the use of uppercase or lowercase.

(3) List vendor details except those vendors who are in the following states: MI, OH, and PA. The result should be ordered by vendor state and vendor ID.

(4) Display the total number of employees who are entitled to have commissions.

(5) List employee details for those who have “manager” titles. This could include employees whose titles are “Associate Manager” or “Sales Manager”, for example. Please note that the spelling of the column values may have different combinations of letter case in the database but all records should appear in the result as long as they meet the requirements regardless of the use of uppercase or lowercase.

(6) List invoice number, line number, product SKU, line quantity, line price, and line amount. For example, if the line quantity is 2 and the line price is 7.49, the line amount is 2*7.49 = 14.98. The result should have an appropriate heading for the calculated column and should be ordered by invoice number and line number. Make sure to display the line amount with $ and two decimal places.

(7) Your manager asks you to create a query that allows her to display product SKU, product category, product price, product quantity on-hand, and product amount on-hand depending on the minimum amount of product amount on-hand that she specifies. Product amount on-hand can be calculated from product quantity on-hand and product price. For example, if she enters the value of 700, all products that have the amount of product on-hand greater than or equal to 700 should appear in the result. Make sure to display the product amount on-hand with $, comma, and two decimal places.

(8) Write a query to display the average, minimum and maximum customer balance. The column headings should show “Average balance”, “Minimum balance”, and “Maximum balance” respectively. The numbers should be displayed with $, comma and two decimal places.

(9) Write a query to display vendor details from those vendors whose first two letters of their names are “Ri” and have the word “Partners” as part of their names. Please note that the spelling of the column values may have different combinations of letter case in the database but all records should appear in the result as long as they meet the requirements regardless of the use of uppercase or lowercase.

(10) Write a query to display customer details from those customers who live in the state of Pennsylvania (PA) and not in the cities of Pittsburgh or Johnstown.

(11) Create your own SELECT statement that has two conditions in the WHERE clause and uses the ORDER BY clause. One of the conditions must use comparison operators (i.e., =, <. >, <=, >=, <>, !=).

(12) Create your own SELECT statement that has two conditions in the WHERE clause. One of the conditions must use the special operators IN, LIKE or DISTINCT.