Photo AI

4.1 The following tables have been designed for the database: tblDepartment Key FieldName DepartmentID DepName DepContactName DepExtensionNum Example of data in the tblDepartment table: DepartmentID DepName DepContactName DepExtensionNum D12 Afrikaans Mrs De Bruin X201 D15 History Mr J Pillay X206 D17 Civil Technology Mr K Mahlangu X214 Example of data in the tblStockItems table: ItemID ItemName ItemQuantity ItemCost DepartmentID A1 Desktop M703X 3 R8 000 D17 B2 Laptop HP 64 bit 3 R8 000 D17 B4 Laptop Acer 32 bit 2 R4 500 D06 C5 Printer HP F370M 2 R2 090 D12 D6 Scanner LG253 1 R1 000 D11 B5 Laptop Computer Lenovo i5 1 R6 300 D16 NOTE: The ItemQuantity field refers to the number of items for each item indicated in the ItemName field - NSC Information Technology - Question 4 - 2016 - Paper 2

Question icon

Question 4

4.1-The-following-tables-have-been-designed-for-the-database:---tblDepartment---Key---FieldName---DepartmentID---DepName---DepContactName---DepExtensionNum----Example-of-data-in-the-tblDepartment-table:--DepartmentID--DepName--DepContactName--DepExtensionNum---D12--Afrikaans--Mrs-De-Bruin--X201---D15--History--Mr-J-Pillay--X206---D17--Civil-Technology--Mr-K-Mahlangu--X214----Example-of-data-in-the-tblStockItems-table:--ItemID--ItemName--ItemQuantity--ItemCost--DepartmentID---A1--Desktop--M703X-3--R8-000--D17---B2--Laptop--HP-64-bit--3--R8-000--D17---B4--Laptop--Acer-32-bit--2--R4-500--D06---C5--Printer--HP-F370M-2--R2-090--D12---D6--Scanner--LG253-1--R1-000--D11---B5--Laptop-Computer-Lenovo-i5--1--R6-300--D16----NOTE:---The-ItemQuantity-field-refers-to-the-number-of-items-for-each-item-indicated-in-the-ItemName-field-NSC Information Technology-Question 4-2016-Paper 2.png

4.1 The following tables have been designed for the database: tblDepartment Key FieldName DepartmentID DepName DepContactName DepExtensionNum Exampl... show full transcript

Worked Solution & Example Answer:4.1 The following tables have been designed for the database: tblDepartment Key FieldName DepartmentID DepName DepContactName DepExtensionNum Example of data in the tblDepartment table: DepartmentID DepName DepContactName DepExtensionNum D12 Afrikaans Mrs De Bruin X201 D15 History Mr J Pillay X206 D17 Civil Technology Mr K Mahlangu X214 Example of data in the tblStockItems table: ItemID ItemName ItemQuantity ItemCost DepartmentID A1 Desktop M703X 3 R8 000 D17 B2 Laptop HP 64 bit 3 R8 000 D17 B4 Laptop Acer 32 bit 2 R4 500 D06 C5 Printer HP F370M 2 R2 090 D12 D6 Scanner LG253 1 R1 000 D11 B5 Laptop Computer Lenovo i5 1 R6 300 D16 NOTE: The ItemQuantity field refers to the number of items for each item indicated in the ItemName field - NSC Information Technology - Question 4 - 2016 - Paper 2

Step 1

Suggest a suitable data type for the ItemCost field.

96%

114 rated

Answer

The suitable data type for the ItemCost field would be Currency or Double as it is designed to hold monetary values, which require decimal precision to accurately represent amounts.

Step 2

Use the data in the tables (on the previous page), and give an example that explains the one-to-many relationship that exists between the tblDepartment and the tblStockItems tables.

99%

104 rated

Answer

In the given tables, the tblDepartment can have multiple stock items associated with it. For example, the department with ID D12 (Afrikaans) has a Printer HP F370M and multiple laptops. In contrast, each stock item belongs to only one department, illustrating a one-to-many relationship.

Step 3

Display all details from the tblStockItems table sorted in alphabetical order by ItemName.

96%

101 rated

Answer

SELECT * FROM tblStockItems
ORDER BY ItemName;

Step 4

An HP F370M printer of the Afrikaans department (D12) was left off the stock list and must be included. Change the existing number of printers for this specific department by updating the quantity.

98%

120 rated

Answer

UPDATE tblStockItems
SET ItemQuantity = ItemQuantity + 1
WHERE DepartmentID = 'D12' AND ItemName = 'Printer HP F370M';

Step 5

Display the department name and the total stock value for each department in a new field called TotalValue.

97%

117 rated

Answer

SELECT DepName, SUM(ItemQuantity * ItemCost) AS TotalValue
FROM tblStockItems, tblDepartment
WHERE tblStockItems.DepartmentID = tblDepartment.DepartmentID
GROUP BY DepName;

Step 6

Write only the missing code at (a) to complete the statement.

97%

121 rated

Answer

SELECT ItemName, ItemQuantity FROM tblStockItems WHERE ItemName LIKE '%Laptop%'

Join the NSC students using SimpleStudy...

97% of Students

Report Improved Results

98% of Students

Recommend to friends

100,000+

Students Supported

1 Million+

Questions answered

;