SQL - E-commerce Schema

Amazon Q Developer는 ANSI 및 데이터베이스 관련 SQL DDL 및 DML 문을 생성할 수 있습니다. 데이터베이스 제공자가 특정 DLL 또는 DML을 구현한 경우 Amazon Q Developer가 특정 구현을 생성합니다.

주의 사항 일반적으로 Amazon Q Developer의 제안 사항이 두 개 이상인지 항상 확인하십시오.

---

예시 #1

e-commerce 스키마 생성

일반적인 프롬프트

given the following, create e-commerce database tables for an online store:

- Tables
-- Products
--- ProductID (primary key and sequence auto increment starting with value 1)
--- Name
--- Description
--- Price
--- CategoryID (foreign key to Categories table)
--- ImageURL
-- Categories
--- CategoryID (primary key and sequence number auto increment starting with value 1)
--- Name
-- Customers
--- CustomerID (primary key and sequence number auto increment starting with value 1)
--- FirstName
--- LastName
--- Email
--- Phone
--- Address
-- Orders
--- OrderID (primary key and sequence number auto increment starting with value 1)
--- CustomerID (foreign key to Customers table)
--- OrderDate
--- ShippingAddress
--- BillingAddress
--- TotalAmount
-- OrderItems
--- OrderItemID (primary key and sequence number auto increment starting with value 1)
--- OrderID (foreign key to Orders table)
--- ProductID (foreign key to Products table)
--- Quantity
--- UnitPrice
-- Shipping
--- ShippingID (primary key and sequence number auto increment starting with value 1)
--- OrderID (foreign key to Orders table)
--- Shipper (shipping company name)
--- TrackingNumber
-- Reviews
--- ReviewID (primary key and sequence number auto increment starting with value 1)
--- CustomerID (foreign key to Customers table)

create the tables for an oracle server if the tables do not exist for a  database called my-ecomm

ANSI SQL 프롬프트

VSCode에서 파일을 생성하고 이름을 create_table_ecomm_ansi.sql로 지정하고 다음 코드를 입력합니다.

/**

given the following, create e-commerce database tables for an online store:

- Tables
-- Products
--- ProductID (primary key and sequence auto increment starting with value 1)
--- Name
--- Description
--- Price
--- CategoryID (foreign key to Categories table)
--- ImageURL
-- Categories
--- CategoryID (primary key and sequence number auto increment starting with value 1)
--- Name
-- Customers
--- CustomerID (primary key and sequence number auto increment starting with value 1)
--- FirstName
--- LastName
--- Email
--- Phone
--- Address
-- Orders
--- OrderID (primary key and sequence number auto increment starting with value 1)
--- CustomerID (foreign key to Customers table)
--- OrderDate
--- ShippingAddress
--- BillingAddress
--- TotalAmount
-- OrderItems
--- OrderItemID (primary key and sequence number auto increment starting with value 1)
--- OrderID (foreign key to Orders table)
--- ProductID (foreign key to Products table)
--- Quantity
--- UnitPrice
-- Shipping
--- ShippingID (primary key and sequence number auto increment starting with value 1)
--- OrderID (foreign key to Orders table)
--- Shipper (shipping company name)
--- TrackingNumber
-- Reviews
--- ReviewID (primary key and sequence number auto increment starting with value 1)
--- CustomerID (foreign key to Customers table)

create the tables for ANSI database called my-ecomm if table does not exist

*/

Amazon Q Developer 제안

CREATE DATABASE IF NOT EXISTS my-ecomm;

USE my-ecomm;

CREATE TABLE IF NOT EXISTS products (
    productid INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,
    categoryid INT NOT NULL,
    imageurl VARCHAR(255) NOT NULL,
    FOREIGN KEY (categoryid) REFERENCES categories(categoryid)
);

CREATE TABLE IF NOT EXISTS categories (
    categoryid INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL

);

CREATE TABLE IF NOT EXISTS customers (
    customerid INT PRIMARY KEY AUTO_INCREMENT,
    firstname VARCHAR(255) NOT NULL,
    lastname VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    phone VARCHAR(255) NOT NULL,
    address VARCHAR(255) NOT NULL

);

CREATE TABLE IF NOT EXISTS orders (
    orderid INT PRIMARY KEY AUTO_INCREMENT,
    customerid INT NOT NULL,
    orderdate DATE NOT NULL,
    shippingaddress VARCHAR(255) NOT NULL,
    billingaddress VARCHAR(255) NOT NULL,
    totalamount DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (customerid) REFERENCES customers(customerid)
);

CREATE TABLE IF NOT EXISTS orderitems (
    orderitemid INT PRIMARY KEY AUTO_INCREMENT,
    orderid INT NOT NULL,
    productid INT NOT NULL,
    quantity INT NOT NULL,
    unitprice DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (orderid) REFERENCES orders(orderid),
    FOREIGN KEY (productid) REFERENCES products(productid)
);

CREATE TABLE IF NOT EXISTS shipping (
    shippingid INT PRIMARY KEY AUTO_INCREMENT,
    orderid INT NOT NULL,
    shipper VARCHAR(255) NOT NULL,
    trackingnumber VARCHAR(255) NOT NULL,
    FOREIGN KEY (orderid) REFERENCES orders(orderid)
);

CREATE TABLE IF NOT EXISTS reviews (
    reviewid INT PRIMARY KEY AUTO_INCREMENT,
    customerid INT NOT NULL,
    FOREIGN KEY (customerid) REFERENCES customers(customerid)
);