52 lines
1.7 KiB
SQL
52 lines
1.7 KiB
SQL
DROP TABLE test_customers;
|
|
|
|
CREATE TABLE test_customers (
|
|
CustomerID NUMBER(8),
|
|
CustName VARCHAR2(35) NOT NULL,
|
|
CustAge NUMBER(2),
|
|
CONSTRAINT pk_test_customers_CustomerID PRIMARY KEY(CustomerID),
|
|
CONSTRAINT uk_test_customers_CustName UNIQUE(CustName),
|
|
CONSTRAINT ck_test_customers_CustAge CHECK(CustAge>=16 AND CustAge<=70)
|
|
);
|
|
|
|
INSERT INTO test_customers VALUES(100, 'John Smith', null);
|
|
INSERT INTO test_customers VALUES(350, 'Mark Smith', 20);
|
|
|
|
SELECT * FROM test_customers;
|
|
|
|
CREATE TABLE test_orders (
|
|
OrderID NUMBER(8),
|
|
CustomerID NUMBER(8),
|
|
OrderDate Date,
|
|
OrderAmount NUMBER(8,2),
|
|
CONSTRAINT pk_test_orders_OrderID PRIMARY KEY(OrderID),
|
|
CONSTRAINT fk_test_orders_test_customers FOREIGN KEY(CustomerID) REFERENCES test_customers(CustomerID)
|
|
);
|
|
|
|
INSERT INTO test_orders VALUES(1, 100, '2023-01-15', 125.3);
|
|
INSERT INTO test_orders VALUES(2, 100, '2023-01-18', 300.3);
|
|
|
|
SELECT * FROM test_orders;
|
|
SELECT * FROM test_customers c JOIN test_orders o ON c.CustomerID = o.CustomerID;
|
|
|
|
|
|
|
|
|
|
SELECT constraint_name, constraint_type, search_condition, table_name, 'Levi McLean' AS "Created By"
|
|
FROM user_constraints
|
|
WHERE table_name IN('CATEGORIES','EMPLOYEES','CUSTOMERS', 'ORDERS','ORDER_DETAILS','PRODUCTS','VENDORS','PRODUCT_VENDORS')
|
|
AND constraint_name NOT LIKE 'SYS%'
|
|
ORDER BY 1;
|
|
|
|
|
|
|
|
SELECT 'Customers' AS "Levi McLean's Tables", COUNT(*) AS "Row Count" FROM CUSTOMERS
|
|
UNION
|
|
SELECT 'Employees', COUNT(*) FROM Employees UNION
|
|
SELECT 'Orders', COUNT(*) FROM Orders UNION
|
|
SELECT 'Orders Details', COUNT(*) FROM OrderDetails UNION
|
|
SELECT 'Products', COUNT(*) FROM Products UNION
|
|
SELECT 'Categories', COUNT(*) FROM Categories UNION
|
|
SELECT 'Shippers', COUNT(*) FROM Shippers UNION
|
|
SELECT 'Suppliers', COUNT(*) FROM Suppliers;
|