SQLearning(CREATE, CONSTRAINT, ALTER, etc)

/*
CREATE DATABASE Test03
GO
USE Test03
*/

/*
1. Create tables with the following descriptions :
(create table, like,  in)
Table’s Name  : MsStaff
Primary Key : StaffId

StaffID CHAR    5   Cannot be empty, must be started with ‘SF’ and followed by 3 characters of number. Example: SF001
StaffName   VARCHAR 20  Cannot be empty
StaffBOD    DATE    -   -
StaffGender VARCHAR 10  Must be ‘Male’ or ‘Female’
StaffAddress    VARCHAR 100 -
StaffEmail  VARCHAR 20  Format [email protected]
StaffSalary INT -   -
*/

CREATE TABLE MsStaff(
    StaffID CHAR(5) NOT NULL PRIMARY KEY,
    StaffName VARCHAR(20) NOT NULL,
    StaffBOD DATE,
    StaffGender VARCHAR(10),
    StaffAddress VARCHAR(100),
    StaffEmail VARCHAR(40),
    StaffSalary INT,
    CONSTRAINT MsStaffValidation CHECK(
        StaffID LIKE 'SF[0-9][0-9][0-9]' AND
        (StaffGender LIKE 'MALE' OR StaffGender LIKE 'FEMALE') AND
        StaffEmail LIKE'%@%.%'
    )
)


/*
Table’s Name  : MsPaymentType
Primary Key : PaymentTypeId
Field Name  Data Type   Length  Description
PaymentTypeId   CHAR    5   Cannot be empty, must be started with ‘PT’ and followed by 3 characters of number.
Example: PT001
PaymentTypeName VARCHAR 20  Cannot be empty
*/

CREATE TABLE MsPaymentType(
    PaymentTypeID CHAR(5) NOT NULL PRIMARY KEY,
    PaymentTypeName VARCHAR(20) NOT NULL,
    CONSTRAINT MsPaymentTypeValidation CHECK(
        PaymentTypeID LIKE 'PT[0-9][0-9][0-9]'
    )
)

/*
Table’s Name  : MsCity
Primary Key : CityId
Field Name  Data Type   Length  Description
CityId  CHAR    5   Cannot be empty, must be started with ‘CT’ and followed by 3 characters of number.
Example: CT001
CityName    VARCHAR 20  Cannot be empty
*/

CREATE TABLE MsCity(
    CityID CHAR(5) NOT NULL PRIMARY KEY,
    CityName VARCHAR(20) NOT NULL,
    CONSTRAINT MsCityValidation CHECK (
        CityID LIKE 'CT[0-9][0-9][0-9]'
    )
)

/*
Table’s Name  : MsDestination
Primary Key     : DestinationId
Field Name  Data Type   Length  Description
DestinationId   CHAR    5   Cannot be empty, must be started with ‘DT’ and followed by 3 characters of number.
Example: DT001
CityFromId  CHAR    5   Cannot be empty, references from MsCity.
CityToId    CHAR    5   Cannot be empty, references from MsCity.
Price   NUMERIC 11, 2   -
*/

CREATE TABLE MsDestination(
    DestinationID CHAR(5) NOT NULL PRIMARY KEY,
    CityFromID CHAR(5) REFERENCES MsCity ON UPDATE NO ACTION ON DELETE NO ACTION ,
    CityToID CHAR(5) REFERENCES MsCity ON UPDATE NO ACTION ON DELETE NO ACTION ,
    PRICE NUMERIC(11,2),
    CONSTRAINT MsDetinationValidation CHECK(
        DestinationID LIKE 'DT[0-9][0-9][0-9]'
    )
)

/*
Table’s Name  : MsShipmentType
Primary Key : ShipmentTypeId
Field Name  Data Type   Length  Description
ShipmentTypeId  CHAR    5   Cannot be empty, must be started with ‘ST’ and followed by 3 characters of number.
Example: ST001
ShipmentTypeName    VARCHAR 10  Cannot be empty
ShipmentCharge  INT 5   -
*/

CREATE TABLE MsShipmentType(
    ShipmentTypeID CHAR(5) NOT NULL PRIMARY KEY,
    ShipmentTypeName VARCHAR(10) NOT NULL,
    ShipmentCharge INT,
    CONSTRAINT MsShipmentTypeValidation CHECK (
        ShipmentTypeID LIKE 'ST[0-9][0-9][0-9]'
    )
)

/*
2.  Create a table with the following descriptions :
Table’s Name  : HeaderShipment
Field Name  Data Type   Length  Description
ShipmentID  CHAR    5   Cannot be empty, must be started with ‘SH’ and followed by 3 characters of number.
Example: SH001
StaffID CHAR    5   Cannot be empty, references from MsStaff and if the referenced data is changed then the StaffID data in HeaderShipment will also be changed.
DestinationID   CHAR    5   Cannot be empty, references from MsDestination and if the referenced data is changed then the DestinationID data in the HeaderShipment will also be changed.
PaymentTypeID   CHAR    5   Cannot be empty, references from MsPaymentType and if the referenced data is changed then the PaymentTypeID data in HeaderShipment will also be changed.
ShipmentTypeID  CHAR    5   Cannot be empty, references from MsShipmentType and if the referenced data is changed then the ShipmentTypeID data in HeaderShipment will also be changed.
ReceiverName    VARCHAR 20  -
ReceiverAddress VARCHAR 100 -
Weight  NUMERIC 11,2    -
ShipmentDate    DATE    -   -

Then, with alter syntax, change the ShipementID field to become primary key on that table.
(create table, alter table, primary key)
*/

CREATE TABLE HeaderShipment(
    ShipmentID CHAR(5) NOT NULL,
    StaffID CHAR(5) NOT NULL REFERENCES MsStaff ON DELETE CASCADE ON UPDATE CASCADE,
    DestinationID CHAR(5) NOT NULL REFERENCES MsDestination ON UPDATE CASCADE ON DELETE CASCADE,
    PaymentTypeID CHAR(5) NOT NULL REFERENCES MsPaymentType ON UPDATE CASCADE ON DELETE CASCADE,
    ShipmentTypeID CHAR(5) NOT NULL REFERENCES MsShipmentType ON UPDATE CASCADE ON DELETE CASCADE,
    ReceiverName VARCHAR(20),
    ReceiverAddress VARCHAR(100),
    [Weight] NUMERIC(11,2),
    ShipmentDate DATE,
    CONSTRAINT HeaderShipmentValidation CHECK(
        ShipmentID LIKE 'SH[0-9][0-9][0-9]'
    )
)

ALTER TABLE HeaderShipment ADD  PRIMARY KEY(ShipmentID)

/*
3.  Add a constraint for MsStaff table to validate that staff’s name must be between 5 and 20 characters
and the previous data is not necessary to be checked. Then, delete the constraint.
(alter table, with nocheck, add constraint, drop)
*/

ALTER TABLE MsStaff WITH NOCHECK
ADD CONSTRAINT MsStaffValidasi2 CHECK(
    LEN(StaffName) BETWEEN 5 AND 20
)

ALTER TABLE MsStaff DROP CONSTRAINT MsStaffValidasi2

/*
4.  Add ‘Description’ as a new column on MsShipmentType table with varchar(100) data type. Then, delete
the column.
(alter table, add, drop column)
*/

ALTER TABLE MsShipmentType ADD [Description] VARCHAR(100)
ALTER TABLE MsShipmentType DROP COLUMN [Description]

SELECT * FROM MsStaff
SELECT * FROM MsCity
SELECT * FROM MsDestination
SELECT * FROM MsPaymentType
SELECT * FROM MsShipmentType
SELECT * FROM HeaderShipment