SQL Lab Part 3: Create Your Own SQL

Sql Labpart 3part 3 Will Require You To Create Your Own Sql Statements

SQL Lab Part 3 Part 3 will require you to create your own SQL statements to satisfy the information requirements as outlined in this lab. Prior to completing this part of the lab; You should add additional test data to your database as needed in order to test your SQL statements. Select Queries Note: Do not use any foreign keys in the select list for any SQL statement.

  1. S2_8 a. Create a list of customer names and their respective credit card numbers, credit card expiry date, city and province in the result set. Sort the information by city within province.
  2. S2_9 a. Create a list of manufacturers names where the manufacturer name ends in ‘E’. Sort the information by manufacturers name in descending order.
  3. S2_10 a. Create a distinct list of customer names for customers that live in Manitoba or British Columbia.
  4. S2_11 a. Create a list of inventory items, MSRP price, price paid from the order details and calculate the difference between the two prices.
  5. S2_12 a. Create a list of ship-to-city, order dates and ship dates. Sort the information by Ship-to-city and order dates.

Paper For Above instruction

This assignment entails creating specific SQL SELECT statements to retrieve and organize data from a database, fulfilling designated information requests without utilizing foreign keys in the select list. Each task requires careful construction of queries that extract relevant information, apply appropriate sorting, filtering, and calculations, and ensure clarity and efficiency in data retrieval.

1. Listing Customer Details with Sorting by City and Province

The first query focuses on generating a list of customer names alongside their credit card details, including card numbers and expiry dates, in addition to location data such as city and province. To accomplish this, we select the customer_name, credit_card_number, credit_card_expiry, city, and province from the customers table. The results are sorted primarily by province and then by city to facilitate regional organization.

Sample SQL statement:

SELECT customer_name, credit_card_number, credit_card_expiry, city, province

FROM customers


ORDER BY province, city;

2. Identifying Manufacturers Ending with ‘E’

The second task involves filtering a list of manufacturers whose names conclude with the letter ‘E’. The query examines the manufacturer_name field from the manufacturers table, applying a condition with the LIKE operator to match names ending with ‘E’. Results are ordered in descending order for easier review of the list starting from ‘Z’ backwards.

Sample SQL statement:

SELECT manufacturer_name

FROM manufacturers


WHERE manufacturer_name LIKE '%E'


ORDER BY manufacturer_name DESC;

3. Listing Distinct Customer Names in Manitoba or British Columbia

For the third query, the goal is to retrieve a list of unique customer names residing in either Manitoba or British Columbia. Using the DISTINCT keyword ensures no duplicates, and filtering is done via the state or province column with conditions checking for these specific provinces.

Sample SQL statement:

SELECT DISTINCT customer_name

FROM customers


WHERE province IN ('Manitoba', 'British Columbia');

4. Inventory Items and Price Difference Calculation

The fourth task generates a list of inventory items along with their Manufacturer's Suggested Retail Price (MSRP), the actual price paid, and the difference between the two. This involves selecting item details from the inventory table, retrieving MSRP and order details using joins with the order details table based on item IDs, and calculating the price difference using an arithmetic expression.

Sample SQL statement:

SELECT i.item_name, i.msrp_price, od.price_paid, (i.msrp_price - od.price_paid) AS price_difference

FROM inventory i


JOIN order_details od ON i.item_id = od.item_id;

5. Ship-to City, Order, and Shipment Date List with Sorting

The final query creates a list of shipping destinations, including ship-to cities, order dates, and ship dates, sorted by city and order date. This involves selecting relevant columns from the orders table and applying order by clauses to achieve sorted output.

Sample SQL statement:

SELECT ship_to_city, order_date, ship_date

FROM orders


ORDER BY ship_to_city, order_date;

Conclusion

Constructing accurate SQL SELECT statements to meet specific data retrieval needs is fundamental in managing relational databases. These queries demonstrate core SQL skills, including filtering, sorting, joining, and calculating derived data, all essential in extracting meaningful insights from complex datasets. Ensuring queries are optimized and correctly structured allows for efficient database operation and insightful reporting, which is vital for business intelligence and decision-making processes.

References

  • Elmasri, R., & Navathe, S. B. (2015). Fundamentals of Database Systems (7th ed.). Pearson.
  • Date, C. J. (2004). An Introduction to Database Systems (8th ed.). Pearson.
  • Rob, P., & Coronel, C. (2018). Database Systems: Design, Implementation, & Management (13th ed.). Cengage Learning.
  • Hoffer, J. A., Venkataraman, R., & Topi, H. (2016). Modern Database Management (12th ed.). Pearson.
  • Silberschatz, A., Korth, H. F., & Sudarshan, S. (2020). Database System Concepts (7th ed.). McGraw-Hill.
  • Conallen, J., & Wills, S. (2016). SQL: The Complete Reference (3rd ed.). McGraw-Hill Education.
  • Abiteboul, S., Hull, R., & Vianu, V. (1995). Foundations of Databases. Addison-Wesley.
  • Ozsu, M. T., & Valduriez, P. (2011). Principles of Distributed Database Systems (3rd ed.). Springer.
  • Kingsley, T., & Geiger, S. (2017). SQL Performance Tuning. O'Reilly Media.
  • Connell, J. (2017). SQL for Data Analysis. O'Reilly Media.

Related Assignments