SQL Study Guide for Online Shoe Store Database

Overview

• Comprehensive SQL practice guide based on Exam 2 Study Guide.
• Covers concepts like query types and ERD.

Database Structure

Tables

1. Customer
   • Fields: CustomerID, FirstName, LastName, StreetAddress, City, State, PostalCode, Country, Phone
2. Sale
   • Fields: SaleID, SaleDate, CustomerID, Tax, Shipping
3. SaleDetail
   • Fields: SaleDetailID, SaleID, ProductID, ItemSize, Quantity, SalePrice
4. Product
   • Fields: ProductID, ProductName, ManufacturerID, Material, ListPrice, Gender, Category, Color, Description
5. Manufacturer
   • Fields: ManufacturerID, ManufacturerName, Address, City, State, PostalCode, Contact, Phone

Section 1: Brief Answer Questions

1. Relational vs. Vector Databases

• Relational Databases
  • Structured in tables, use SQL, enforce schemas, efficient for structured data.
  • Relationships maintained through keys.
• Vector Databases
  • Store vector embeddings for AI/ML applications.
  • Use similarity searches and specialized distance metrics.
  • Handle unstructured data like text, images, audio.

2. Retrieval Augmented Generation (RAG)

• Enhances LLMs with a retrieval system for relevant data.
• Key Components: Vector database, retrieval mechanism, LLM.
• Applications: Customer service, knowledge management, legal, R&D, data analysis, content creation.
• Benefits: Reduces hallucinations, provides accurate info, maintains context.

Section 2: SQL Statements

CREATE TABLE Statements

• Define primary and foreign key constraints.

INSERT Statements

• Add new records to tables.

UPDATE Statements

• Modify existing records.

DELETE Statements

• Remove records based on conditions.

Section 3: SQL Queries

CREATE VIEW

• Create views for customer orders, product details, and sales analytics.

SELECT and FROM

• Basic select statements, calculations, and string concatenation.

JOIN

• Inner, left, right, and full outer joins to combine data from multiple tables.

WHERE Clauses

• Use of equality, comparison operators, and logical operators.

Functions

• Scalar and aggregate functions for strings, dates, math operations.

GROUP BY

• Aggregate data based on categories, manufacturers, and sales timelines.

HAVING

• Filter grouped data with conditions post-aggregation.

ORDER BY

• Sort data in ascending/descending order.

Practice Problems

• 10 queries to test SQL skills with joins, conditions, and aggregate functions.

Exam Preparation Tips

1. Practice query types and syntax.
2. Study ERD for table relationships.
3. Understand operator precedence.
4. Know join types: INNER, LEFT, RIGHT.
5. Differentiate WHERE vs. HAVING.
6. Learn scalar and aggregate functions.
7. Work on complex queries with joins.
8. Review CREATE TABLE syntax.
9. Understand CREATE VIEW applications.
10. Prepare for brief answer section on databases and RAG.