A database management system is required for a digital media company that specialises in creating and distributing online video content - NSC Information Technology - Question 2 - 2024 - Paper 1

To check for creators from South Africa whose email does not contain "@gmail.com", we can use the following SQL query:

SELECT CreatorName, Email, Country 
FROM tblCreators 
WHERE Email NOT LIKE '%@gmail.com%' 
AND Country = 'South Africa';

To find the top 3 latest uploaded videos, we can execute the following query:

SELECT TOP 3 UploadDate, VideoID, Title 
FROM tblVideos 
ORDER BY UploadDate DESC;

To count the number of videos uploaded by each creator, we can use this SQL statement:

SELECT CreatorID, COUNT(*) AS NumberUploaded 
FROM tblVideos 
GROUP BY CreatorID 
HAVING COUNT(*) > 5;

To add a new creator, we will use the INSERT statement as follows:

INSERT INTO tblCreators 
VALUES ('C001', 'TRISHKALOM', 'trisha@marketing.co.za', 'South Africa');

To remove a creator and all associated videos, we need to loop through the tblCreators table:

1. Start with the first record in tblCreators.
2. For each creator, check their CreatorName against the specified name.
3. Within that loop, check the tblVideos for any records with matching CreatorID.
4. If records are found, delete them using:

tblVideos.Delete;

5. Finally, delete the creator record from tblCreators once those associated videos are removed.
6. Continue this process until all creators have been checked.

To change the upload date, navigate to the tblVideos and edit the UploadDate as follows:

tblVideos.Edit;
tblVideos['UploadDate'] := Date;
tblVideos.Post;

We can also format the date using:

DateToStr(Date);
FormatDateTime('yyyy/mm/dd', Now);