First Make Or Download A New Copy Of The Original Database
First Make Or Download A New Copy Of The Original Database You Can
First, make or download a new copy of the original database. Then use SQL to create, run, and save with the specified name each of the 15 queries described below. Submit your database with all 15 examples to the link on the Week 6 Assignments page with your name and the word Final as the filename (e.g., JonesFinal.accdb). (The number in the parenthesis is how many rows you should get, except for #7 where it is the answer.)
Produce a sorted list of the customer cities in ascending order. Include the cust_city, cust_name, and the cust_email columns. Save this query as 1-CustomerCitiesSorted.
Produce a list from the orderitems table where the quantity is greater than or equal to 25. Include all columns. Name it 2–Quantity25OrMore.
Produce a list of customers who are not in CA, IL, MA, NY, or FL. Save it as 3-CustomersNotIn5States.
Produce a list of products where the letters “sea” occur anywhere within their name. Save it as 4-ContainingSEA.
Prices will be going up 7%. Create a list of all product names and current prices, along with what will be the new prices. (Hint: To get a price that is 7% higher, multiply the existing price by 1.07.) Name the calculated column “NewPrice.” Save the query as 5-‘NewPrices’.
Extra Credit: For 2 extra points, format the NewPrice column with dollar signs, commas, and 2 decimal places.
Use the DAY() function to get a list of all orders that were placed on the first day of any month. Include all fields. Save it as 6–Day1Orders.
Obtain the average quantity of items ordered from the orderitems table. Save it as 7-AverageQuantity.
Count the number of vendors grouped by country. Name the column containing the count “CountryCount.” Save it as 8–VendorCountryCount.
Produce a list of the products that do not have the word “inch” anywhere in their description. Include the name and description fields. Save the query as 9-NotContainingInch.
Join vendors and products to get a list of all products supplied by USA only vendors. Include both names, the price, and the country. Sort by vendor name and then product name. Save it as 10-USAVendorProducts.
Join customers, orders, orderitems, and products. List customer name and city, order number and date, product name, quantity, and item price for all ordered items. Save as 11-4TableJoin.
Create an outer join with customers and orders to list all customers whether or not they have placed any orders. Include name and order number, sorted by customer name. Save as 12-AllCustomersWithOrWithoutOrders.
Create a UNION query to list in a single result the name of every city in customers and vendors tables in alphabetical order. Also include the state, zip, and country fields. Save as 13–AllCities.
List customers for whom there is no contact name. Include customer name, contact name, and email. Save as 14–NoContactName.
Produce a list of order items where the vendor and customer are from the same country. Include the six specified fields. Save as 15-CustomerSameCountryAsVendor.
Paper For Above instruction
This project involves executing SQL queries on a provided database to analyze various aspects of customer, product, order, and vendor data. The objectives include practicing SQL query creation for data retrieval, filtering, joins, aggregations, and formatting within a relational database environment, specifically using Microsoft Access or comparable systems.
The first step is to acquire or download a clean copy of the original database, ensuring the integrity and completeness of the data set. Each query serves a specific purpose, testing different SQL techniques such as sorting, filtering, grouping, joins, and calculations. For instance, listing customer cities in alphabetical order helps understand sorting functionalities, while deriving new prices with a 7% increase demonstrates data manipulation skills.
Some queries require filtering data based on conditions; for example, selecting customers outside specific states or products containing particular substrings. These tasks emphasize WHERE clauses and string pattern matching with LIKE operators. Others involve aggregations like calculating average quantities or count groupings, which deepen understanding of aggregate functions and GROUP BY clauses.
Join operations are fundamental, enabling the combination of tables to produce meaningful insights, such as listing products supplied by US vendors or all order details. Outer joins are tested by retrieving customers regardless of their order history, illustrating how to handle NULLs and optional relationships effectively. UNION queries demonstrate combining data from multiple tables to produce unified lists, such as cities from different sources.
Formatting outputs, especially monetary values, illustrates the importance of presentation in data reporting. Extra credit options enhance understanding of formatting capabilities within SQL, including currency display.
Throughout the project, attention to detail and correctness in query syntax is essential, ensuring results match specified expectations, including the correct number of rows or specific answers. This exercise aims to improve SQL proficiency, understanding of relational database design, and practical skills in retrieving, combining, and formatting data.
The final submission incorporates all 15 queries with their specified names, demonstrating comprehensive understanding and execution of SQL-based data analysis within Microsoft Access system or equivalent database environments.
References
- Elmasri, R., & Navathe, S. (2015). Fundamentals of Database Systems (7th ed.). Pearson.