SQL Database

Database:

4. Display StaffID, StaffName, TotalTransaction (obtained from the total of transaction that is being held by the staff and add transaction(s)' in the end), and Quantity (obtained from the maximum quantity that staff handled) for every Female Staff and have a salary greater than 6000000. Then, combine it with a display of StaffID, StaffName, TotalTransaction (obtained from the total of transaction that is being held by the staff and add transaction(s)' in the end), and Quantity (obtained from the maximum quantity that staff handled) for every transaction that handled by female staff and has a transaction on the 30th day. (CAST, COUNT, MAX, GROUP BY, UNION, DATEPART, DAY) Staff StaffName Total Transaction Quantity ST002 Vinia Bactiar 4 transaction(s) 7 2 ST004 Valeria Jurniawan 1 transaction(s) 9 1 5 - 5 5 SECREATE TABLE MsCustomer CustomerID CHAR(5) PRIMARY KEY CHECK (Customer ID LIKE 'CU[0-9][0-9][0-9]'), CustomerName VARCHAR(38) 3 CustomerAddress VARCHAR(50), Customer Phone VARCHAR(15), CustomerEmail VARCHAR(25), CustomerGender VARCHAR (8), CustomerDOB DATE ); CREATE TABLE MsStaff StaffID CHAR(5) PRIMARY KEY CHECK(StaffId LIKE 'ST[0-9][0-9][0-9]'), StaffName VARCHAR(30) NOT NULL; StaffAddress VARCHAR(50), StaffEmail VARCHAR(25), StaffGender VARCHAR(8) NOT NULL, StaffSalary INT NOT NULL ); CREATE TABLE Header Transaction TransactionID CHAR(5) PRIMARY KEY CHECK(Transactionid LIKE TH[0-9][0-9][0-9]"}, Customer ID CHAR(5) NOT NULL, StaffID CHAR (5) NOT NULL, TransactionDate DATE, Payment Type VARCHAR(25), FOREIGN KEY (Customerid) REFERENCES MsCustomer CustomerId) ON UPDATE CASCADE ON DELETE CASCADE, FOREIGN KEY (Staffid) REFERENCES MsStaff Staffid) ON UPDATE CASCADE ON DELETE CASCADE, ); CREATE TABLE MsChairType ChairTypeID CHAR(5) PRIMARY KEY CHECK(ChairTypeID LIKE 'CT[0-9][0-9][0-9]'), ChairTypeName VARCHAR(20), 5 CREATE TABLE MsChair ChairID CHAR(5) PRIMARY KEY CHECK (ChairID LIKE CH[8-9][0-9][0-9]+), ChairTypeID CHAR(5) NOT NULL, ChairName VARCHAR(25) NOT NULL, ChairPrice INT NOT NULL, FOREIGN KEY (ChairTypeID) REFERENCES MsChairType (ChairTypeID) ON UPDATE CASCADE ON DELETE CASCADE ); CREATE TABLE DetailTransaction TransactionID CHAR(5) ChairID CHAR(5) ChairQuantity INTEGER, PRIMARY KEY(Transactionid, ChairID), FOREIGN KEY (Transactionid) REFERENCES Header Transaction(Transactionid) ON UPDATE CASCADE ON DELETE CASCADE, FOREIGN KEY (ChairID) REFERENCES MsChair (ChairID) ON UPDATE CASCADE ON DELETE CASCADE 5