Knoppie [2.1.1 - iOS products] SELECT DeviceID, DeviceName FROM tblDevices WHERE OperatingSystem = 'iOS'; Knoppie [2.1.2 - Category selected] SELECT DeviceName, Category, NumInStock FROM tblDevices WHERE Category LIKE '"%" + DeviceType + '"' Knoppie [2.1.3 - Online support] SELECT DeviceName, Category, OperatingSystem FROM tblDevices D, tblManufacturers M WHERE D.ManufacturerID = M.ManufacturerID AND OnlineSupport = True ORDER BY DeviceName Knoppie [2.1.4 - Profit per manufacturer] SELECT ManufacturerID, FORMAT(SUM(NumInStock * Price * 0.6), 'CURRENCY') AS Profit FROM tblDevices GROUP BY ManufacturerID Knoppie [2.1.5 - Remove devices] DELETE FROM tblDevices WHERE Category = 'Smart speaker' AND ManufacturerID = 'M104'; - NSC Information Technology - Question 2 - 2023 - Paper 1

To select devices based on a specific Category, employ this SQL query:

SELECT DeviceName, Category, NumInStock
FROM tblDevices
WHERE Category LIKE '"%" + DeviceType + '"';

This retrieves the DeviceName, Category, and Number in Stock for devices matching the specified device type.

For listing devices that offer online support, the query would be:

SELECT DeviceName, Category, OperatingSystem
FROM tblDevices D, tblManufacturers M
WHERE D.ManufacturerID = M.ManufacturerID
AND D.OnlineSupport = True
ORDER BY DeviceName;

This fetches the names, categories, and operating systems of devices that support online features.

To calculate the profit generated by each manufacturer, the following SQL statement can be executed:

SELECT ManufacturerID,
FORMAT(SUM(NumInStock * Price * 0.6), 'CURRENCY') AS Profit
FROM tblDevices
GROUP BY ManufacturerID;

This sums up the profit for each manufacturer by considering the stock and price, then formats it in currency.

To remove devices categorized as 'Smart speaker' from the database, execute:

DELETE FROM tblDevices
WHERE Category = 'Smart speaker' AND ManufacturerID = 'M104';

This command deletes all records in tblDevices where the Category is 'Smart speaker' and the Manufacturer ID is 'M104'.