Advanced Database Management: Multiple Tables SQL I Turn In

Advanced Database Managementmultiple Table Sql1 Turn Ina Sql Comman

Use the provided database schema and data to write SQL queries for specific questions involving multiple tables. Save your SQL commands in a file and submit through Canvas. Each question is worth 5 points.

Questions include:

  • Using EXISTS to find sales representatives for a specific customer.
  • Listing orders based on customer or product criteria.
  • Retrieving purchase details for a specific store.
  • Calculating total revenue per customer.
  • Identifying orders excluding certain items.
  • Finding sales reps without customers.
  • Using subqueries to find who ordered a specific item.

Paper For Above instruction

This paper presents the SQL queries corresponding to the provided database schema and data, focusing on extracting specific business insights as per the assigned questions. The database comprises five tables: REP, CUSTOMER, ORDERS, ITEM, and ORDER_LINE, with their respective relationships and data entries. Using this structure, the following queries are constructed to meet the outlined analytical needs:

a. Find the rep number and name for the rep that represented customer 824.

To identify the sales representative for customer 824, the query joins the CUSTOMER and REP tables via the REP_NUM foreign key in CUSTOMER. The WHERE clause filters for customer number 824, and the SELECT retrieves the relevant representative details.

SELECT R.REP_NUM, R.LAST_NAME, R.FIRST_NAME

FROM REP R


JOIN CUSTOMER C ON R.REP_NUM = C.REP_NUM


WHERE C.CUSTOMER_NUM = '824';

b. List order number and order date for orders placed by 'Toys Galore' or containing 'Rocking Horse.'

This query combines data from ORDERS, CUSTOMER, and ORDER_LINE. It retrieves orders either linked to the customer 'Toys Galore' or containing an order line with an item description of 'Rocking Horse.' A subquery or JOIN with ITEM ensures filtering by item description.

SELECT O.ORDER_NUM, O.ORDER_DATE

FROM ORDERS O


JOIN CUSTOMER C ON O.CUSTOMER_NUM = C.CUSTOMER_NUM


LEFT JOIN ORDER_LINE OL ON O.ORDER_NUM = OL.ORDER_NUM


LEFT JOIN ITEM I ON OL.ITEM_NUM = I.ITEM_NUM


WHERE C.CUSTOMER_NAME = 'Toys Galore'


OR I.DESCRIPTION = 'Rocking Horse';

c. Retrieve what 'The Everything Shop' bought: item number, description, quoted price, and quantity ordered.

This requires joining CUSTOMER, ORDERS, ORDER_LINE, and ITEM tables, filtering for customer name 'The Everything Shop.' The selected columns include item details and quoted prices, as well as the quantity ordered.

SELECT I.ITEM_NUM, I.DESCRIPTION, OL.QUOTED_PRICE, OL.NUM_ORDERED

FROM CUSTOMER C


JOIN ORDERS O ON C.CUSTOMER_NUM = O.CUSTOMER_NUM


JOIN ORDER_LINE OL ON O.ORDER_NUM = OL.ORDER_NUM


JOIN ITEM I ON OL.ITEM_NUM = I.ITEM_NUM


WHERE C.CUSTOMER_NAME = 'The Everything Shop';

d. Compute total revenue per customer, sorted descending.

The revenue for each order line is calculated as QUOTED_PRICE multiplied by NUM_ORDERED. Summing this across all orders per customer yields total revenue, which is ordered from highest to lowest.

SELECT C.CUSTOMER_NAME, SUM(OL.QUOTED_PRICE * OL.NUM_ORDERED) AS TOTAL_REVENUE

FROM CUSTOMER C


JOIN ORDERS O ON C.CUSTOMER_NUM = O.CUSTOMER_NUM


JOIN ORDER_LINE OL ON O.ORDER_NUM = OL.ORDER_NUM


GROUP BY C.CUSTOMER_NAME


ORDER BY TOTAL_REVENUE DESC;

e. List order number and date for orders placed by 'Almondton General Store' excluding those containing 'Fire Engine.'

This query joins ORDERS, CUSTOMER, and ORDER_LINE, filtering for the specified customer and excluding orders with an item description of 'Fire Engine.'

SELECT O.ORDER_NUM, O.ORDER_DATE

FROM ORDERS O


JOIN CUSTOMER C ON O.CUSTOMER_NUM = C.CUSTOMER_NUM


LEFT JOIN ORDER_LINE OL ON O.ORDER_NUM = OL.ORDER_NUM


LEFT JOIN ITEM I ON OL.ITEM_NUM = I.ITEM_NUM


WHERE C.CUSTOMER_NAME = 'Almondton General Store'


AND (I.DESCRIPTION IS NULL OR I.DESCRIPTION  'Fire Engine');

f. Find sales reps with no customers currently assigned.

This uses a LEFT JOIN between REP and CUSTOMER. Reps with NULL CUSTOMER_NUM in CUSTOMER means they currently do not have any customers.

SELECT R.REP_NUM, R.LAST_NAME, R.FIRST_NAME

FROM REP R


LEFT JOIN CUSTOMER C ON R.REP_NUM = C.REP_NUM


WHERE C.CUSTOMER_NUM IS NULL;

g. Using a subquery, find customers who ordered a 'brain teaser.'

A subquery identifies order numbers containing 'brain teaser,' then main query retrieves customer info and item description by joining CUSTOMER, ORDERS, ORDER_LINE, and ITEM.

SELECT C.CUSTOMER_NUM, C.CUSTOMER_NAME, I.DESCRIPTION

FROM CUSTOMER C


JOIN ORDERS O ON C.CUSTOMER_NUM = O.CUSTOMER_NUM


JOIN ORDER_LINE OL ON O.ORDER_NUM = OL.ORDER_NUM


JOIN ITEM I ON OL.ITEM_NUM = I.ITEM_NUM


WHERE OL.ITEM_NUM IN (


SELECT ITEM_NUM


FROM ITEM


WHERE DESCRIPTION LIKE '%brain teaser%'


);

Research question and abstract

The research question investigates the impact of violent video games on children's behavior. The literature review examines studies linking violent games to aggression, desensitization, and behavioral issues. The results suggest a correlation between exposure to violent content and increased aggression, though some studies indicate moderating effects when moral values are taught. Conclusions highlight the importance of parental guidance and content regulation. The abstract summarizes key aspects: research question, materials and methods, findings, conclusions, and keywords such as \"violent video games,\" \"child behavior,\" and \"aggression.\"\n

Conclusion

The examination of multiple SQL queries demonstrates the practical application of relational database concepts to extract meaningful insights. These queries enable businesses to analyze customer and sales data, identify patterns, and support decision-making processes. The integration of subqueries and joins illustrates the power of SQL in handling complex data retrieval tasks. Moreover, understanding these techniques is essential for database management professionals aiming to optimize data utilization and improve organizational strategies.

References

  • Becker, A. (2014). The Effects of Violent Video Games on Children. Journal of Child Psychology, 45(3), 250-262.
  • Gentile, D. A. (2009). The Impact of Violent Video Games on Children and Adolescents. Journal of Youth Studies, 12(5), 573-585.
  • Huesmann, L. R. (2003). Video Games and Aggressive Behavior. Journal of Youth and Adolescence, 32(3), 115-125.
  • Anderson, C. A., & Bushman, B. J. (2001). Effects of Violent Video Games on Aggressive Behavior. Psychological Science, 12(5), 353-359.
  • Morin, A. (2021). Are Your Child's Video Games Too Violent? Verywell Family.
  • American Psychological Association. (2015). Resolution on Violent Video Games and Aggression.
  • Hamar, K., & Gronlund, S. (2017). Content Analysis of Video Game Violence. Journal of Media Psychology, 29(2), 89-98.
  • Ferguson, C. J. (2015). Do Video Games Cause Aggressive Behavior? Child Development Perspectives, 9(3), 173-178.
  • Granic, I., Lobel, A., & Engels, R. (2014). The Benefits of Playing Video Games. American Psychologist, 69(1), 66-78.
  • Williams, D., & Skoric, M. (2005). Internet Café Violence, Aggression, and Gaming. Computers in Human Behavior, 21(5), 603-623.

Related Assignments