Learn IMS-TM-DB-SCRIPTING with Real Code Examples

INSERT INTO Shipments (ShipmentID, Origin, Destination, Status)
    VALUES (1001, 'New York', 'Chicago', 'Pending');

Insert a new shipment entry into the Shipments table.

UPDATE Shipments
    SET Status = 'In Transit'
    WHERE ShipmentID = 1001;

Update the status of a shipment to 'In Transit'.

DELETE FROM Shipments
    WHERE ShipmentID = 1001;

Remove a shipment from the database.

SELECT * FROM Shipments
    WHERE Status = 'Pending';

Query all shipments currently marked as Pending.

CREATE PROCEDURE AssignDriver (
    IN shipmentId INT,
    IN driverId INT
)
BEGIN
    UPDATE Shipments
        SET DriverID = driverId, Status = 'Assigned'
        WHERE ShipmentID = shipmentId;
END;

Procedure to assign a driver to a shipment.

INSERT INTO ShipmentLog (ShipmentID, OldStatus, NewStatus, ChangeDate)
    VALUES (1001, 'Pending', 'In Transit', CURRENT_TIMESTAMP);

Insert into a ShipmentLog whenever status changes.

SELECT CAST(ShipmentDate AS DATE) AS ShipDate, COUNT(*) AS TotalShipments
    FROM Shipments
    GROUP BY CAST(ShipmentDate AS DATE);

Count the number of shipments per day.

UPDATE Shipments
    SET DriverID = 501, Status = 'Assigned'
    WHERE Status = 'Pending' AND Destination = 'Chicago';

Update several shipments in a batch for a driver.

CREATE TRIGGER UpdateTimestamp
    BEFORE UPDATE ON Shipments
    FOR EACH ROW
BEGIN
    SET NEW.LastModified = CURRENT_TIMESTAMP;
END;

Automatically update LastModified when shipment status changes.

SELECT ShipmentID, ExpectedDelivery, Status
    FROM Shipments
    WHERE ExpectedDelivery < CURRENT_DATE AND Status != 'Delivered';

Query shipments whose expected delivery date is past today.