Mastering DDL in SQL A Comprehensive Guide to Creating and Managing Database Tables
Note: All SQL queries used in the Case study is tested on Oracle, Few changes is applicable for SQLServer,MySQL and PostgreSQL.
1.Create the Customers table that holds information for the customers. CustomerID must be the primary key Email should be unique and Name cannot be null.
Oracle
CREATE TABLE Customers (
CustomerID NUMBER PRIMARY KEY,
Name VARCHAR2(100) NOT NULL,
Email VARCHAR2(100) UNIQUE,
Phone VARCHAR2(15),
Address VARCHAR2(255),
IsActive NUMBER(1) DEFAULT 1
);
Explanation
The SQL statement appears to be creating a Customers table meant to hold the data of customers in the database. The column is called CustomerID defined as NUMBER. This is the primary key that would ensure that each customer had an id unique to that customer. The column Name is defined as VARCHAR2(100). That is to say it can contain characters up to 100 and is not null, so each customer would have had a name. The Email column has its data type as VARCHAR2 (100). Its uniqueness means that two customers will not have the same e-mail address. In the declaration of the Phone column as having a data type of VARCHAR2 (15) all forms of phones will be stored. In the Address column where customers' addresses are kept, it is of the type VARCHAR2 (255). Finally, we add an IsActive column with a NUMBER(1) definition, which serves as a boolean flag indicating whether the customer is active or not. It defaults to 1 for status. The table structure is defined in case while using Oracle databases since the VARCHAR2 and NUMBER are being used.
MySQL
CREATE TABLE Customers (
CustomerID INT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Email VARCHAR(100) UNIQUE,
Phone VARCHAR(15),
Address VARCHAR(255),
IsActive TINYINT(1) DEFAULT 1
);
SQLServer
CREATE TABLE Customers (
CustomerID INT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Email VARCHAR(100) UNIQUE,
Phone VARCHAR(15),
Address VARCHAR(255),
IsActive BIT DEFAULT 1
);
PostgreSQL
CREATE TABLE Customers (
CustomerID INT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Email VARCHAR(100) UNIQUE,
Phone VARCHAR(15),
Address VARCHAR(255),
IsActive BOOLEAN DEFAULT TRUE
);
2.Create the Accounts table to store customer account information. Set AccountID as a primary key, CustomerID as a foreign key referencing Customers and make Balance unable to be less than zero.
MySQL, Oracle, PostgreSQL and SQL Server
CREATE TABLE Accounts (
AccountID INT PRIMARY KEY,
CustomerID INT,
AccountType VARCHAR(50) NOT NULL,
AccountCreationDate DATE NOT NULL,
Balance DECIMAL(10, 2) DEFAULT 0 CHECK (Balance >= 0),
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID) ON DELETE CASCADE
);
Explanation
CREATE TABLE Accounts This statement creates an Accounts table defined to store customer account information. The AccountID column is declared as an integer type and as a practicing primary key to ensure that each account is assigned a unique identifier number. The CustomerID column is an integer referring to the CustomerID in the Customers table. It therefore forms an explicit foreign key relationship between those two tables. The AccountType column is a character string of length up to 50 characters and identifies the sort of account Savings or Checking for instance and cannot be null. The AccountCreationDate column stores the date that the account was created and must not be null. The Balance column is a decimal with two decimal places and it defaults to 0 with a check constraint ensuring that the balance cannot be negative. Last but not least the foreign key constraint includes the ON DELETE CASCADE rule which would ensure that deleting a customer would also delete all his accounts.
3.Create the Transactions table to track transactions associated with customer accounts. TransactionID should be the primary key, AccountID should be a foreign key and Amount should be strictly positive.
CREATE TABLE Transactions (
TransactionID NUMBER PRIMARY KEY,
AccountID NUMBER,
tDate DATE NOT NULL,
Amount NUMBER(10, 2) CHECK (Amount > 0),
TransactionType VARCHAR2(50) NOT NULL,
FOREIGN KEY (AccountID) REFERENCES Accounts(AccountID) ON DELETE CASCADE
);
Explanation
The CREATE TABLE Transactions statement is used for determining the structure for a table that holds transactional data. It consists of the following columns TransactionID this one would serve as an ID for a unique transaction and is the primary key. In this table the column AccountID is going to mapeped each transaction specific accounts using a foreign key relationship with the Accounts table. tDate the date of each transaction which will be a required field. Amount column this represents the amount of the transaction and needs to be positive and TransactionType this one would store the type of transaction be it deposit or withdrawal. The FOREIGN KEY constraint enforces referential integrity relating the AccountID field to the AccountID in the Accounts table by use of the ON DELETE CASCADE clause such that if the corresponding account is deleted so will delete the transactions. CHECK Amount only permits positive values to be inserted.
NOTE:
The query asked is not fully equivalent between all of the databases because.
Data types and constraints: The data type and constraint used is INT, VARCHAR(50)and DECIMAL(10, 2) and so on are generally compatible throughout databases but some databases differ to the extent of slightly differ in handling the constraint CHECK or default values.
For example
MySQL: On various versions CHECK constraint is not enforced (though it still allows the syntax).
SQL Server: field is declared as INT and DECIMAL(10, 2) is acceptable, however CHECK constraints behave slightly differently.
PostgreSQL: CHECK constraints are fully supported, DECIMAL(10, 2) gets used correctly; however, if you have any foreign keys defined with ON DELETE CASCADE you have to ensure they're set up similarly.
Oracle: It supports these data types: NUMBER, VARCHAR2 and DATE. CHECK constraints are supported, but only for certain types, as well as the handling of defaults is different.
