PC TECHNICIAN QUESTIONS AND ANSWERS

A. Computer Hardware (1–25)

1. Which component is considered the “brain” of the computer?
   A. GPU
   B. CPU
   C. RAM
   D. SSD
   Answer: B

2. Which device is used to store the computer’s BIOS settings?
   A. ROM
   B. CMOS battery
   C. HDD
   D. PSU
   Answer: B

3. Which connector is used for modern internal hard drives?
   A. IDE
   B. SATA
   C. SCSI
   D. Parallel
   Answer: B

4. RAM stands for:
   A. Random Access Memory
   B. Ready Active Memory
   C. Rapid Access Module
   D. Read Active Memory
   Answer: A

5. Which slot is commonly used for graphics cards?
   A. PCI
   B. AGP
   C. PCIe x16
   D. ISA
   Answer: C

6. A CPU cooler is used to:
   A. Increase performance
   B. Prevent overheating
   C. Clean dust
   D. Increase power
   Answer: B

7. Which form factor is commonly used in desktop motherboards?
   A. ATX
   B. BTX
   C. NTX
   D. ITX
   Answer: A

8. Which voltage is used by SATA power connectors?
   A. 12V, 5V, 3.3V
   B. 9V only
   C. 24V only
   D. 6V only
   Answer: A

9. Which component stores temporary data while the computer is running?
   A. HDD
   B. RAM
   C. SSD
   D. NIC
   Answer: B

10. SSD stands for:
    A. Solid State Drive
    B. Super Storage Disk
    C. Static Storage Device
    D. Secure System Drive
    Answer: A

11. A Dual-Core processor has:
    A. 1 core
    B. 2 cores
    C. 4 cores
    D. 8 cores
    Answer: B

12. Thermal paste is applied between:
    A. RAM and motherboard
    B. CPU and heatsink
    C. GPU and PSU
    D. HDD and case
    Answer: B

13. Which device converts AC to DC power?
    A. GPU
    B. HDD
    C. PSU
    D. CPU
    Answer: C

14. POST stands for:
    A. Power On Self Test
    B. Program Output System Test
    C. Primary Operating System Task
    D. Power Output Standard Test
    Answer: A

15. Which port is used for older keyboards?
    A. USB
    B. PS/2
    C. HDMI
    D. VGA
    Answer: B

16. Which connector is used for monitors?
    A. HDMI
    B. VGA
    C. DisplayPort
    D. All of the above
    Answer: D

17. ECC RAM is used for:
    A. Gaming PCs
    B. Error correction
    C. Increasing heat
    D. Wireless connectivity
    Answer: B

18. CPU speed is measured in:
    A. GHz
    B. MB
    C. Volts
    D. RPM
    Answer: A

19. Which storage has no moving parts?
    A. HDD
    B. SSD
    C. Floppy disk
    D. CD-ROM
    Answer: B

20. A GPU is responsible for:
    A. Power supply
    B. Network control
    C. Graphics rendering
    D. Memory management
    Answer: C

21. Which cable is used for Ethernet?
    A. CAT6
    B. HDMI
    C. RCA
    D. USB
    Answer: A

22. A motherboard chipset controls:
    A. Power input
    B. Data flow between CPU and components
    C. Sound quality
    D. Cooling system
    Answer: B

23. Which type of memory is permanent?
    A. RAM
    B. Cache
    C. ROM
    D. Registers
    Answer: C

24. HDDs are measured in:
    A. GHz
    B. RPM
    C. MBps
    D. kWh
    Answer: B

25. Which pin count is used for DDR4 desktop RAM?
    A. 184
    B. 200
    C. 240
    D. 288
    Answer: D

B. Operating Systems (26–45)

26. Windows system files usually have extension:
    A. .exe
    B. .dll
    C. .sys
    D. All of the above
    Answer: D

27. Which key opens Task Manager quickly?
    A. Ctrl + S
    B. Ctrl + Alt + Del
    C. Alt + F4
    D. Win + R
    Answer: B

28. Which OS is open-source?
    A. Windows
    B. macOS
    C. Linux
    D. ChromeOS
    Answer: C

29. What does GUI stand for?
    A. General User Interaction
    B. Graphical User Interface
    C. Global User Internet
    D. Graphic Utility Item
    Answer: B

30. Device drivers are used to:
    A. Connect hardware to the OS
    B. Update internet
    C. Remove viruses
    D. Boot faster
    Answer: A

31. Windows command to check disk errors is:
    A. ipconfig
    B. chkdsk
    C. ping
    D. format
    Answer: B

32. Which Windows feature restores system settings?
    A. Disk Cleanup
    B. Update Manager
    C. System Restore
    D. Task Scheduler
    Answer: C

33. Which file system is best for Windows?
    A. NTFS
    B. FAT16
    C. EXT3
    D. HFS+
    Answer: A

34. Safe Mode loads Windows with:
    A. All drivers
    B. Minimum drivers
    C. High performance settings
    D. Overclock settings
    Answer: B

35. The Windows Run command shortcut:
    A. Win + R
    B. Win + X
    C. Win + S
    D. Win + Tab
    Answer: A

36. Which tool shows system performance?
    A. Control Panel
    B. Device Manager
    C. Task Manager
    D. Notepad
    Answer: C

37. Linux command to list files:
    A. copy
    B. ls
    C. cmd
    D. mkdir
    Answer: B

38. The Windows registry stores:
    A. Hardware settings
    B. Software settings
    C. User preferences
    D. All of the above
    Answer: D

39. Which OS update type includes security fixes only?
    A. Feature update
    B. Major update
    C. Patch
    D. Driver update
    Answer: C

40. Which folder stores 64-bit applications in Windows?
    A. Program Files
    B. Program Files (x86)
    C. System32
    D. SysWOW64
    Answer: A

41. The BIOS loads the OS by reading the:
    A. RAM
    B. Bootloader
    C. GPU
    D. USB port
    Answer: B

42. Linux command to restart:
    A. restart now
    B. reboot
    C. shutdown /f
    D. refresh
    Answer: B

43. Windows command to view IP address:
    A. ipconfig
    B. winip
    C. wmic
    D. ifconfig
    Answer: A

44. Defragmentation is useful for:
    A. SSD
    B. HDD
    C. CMOS
    D. RAM
    Answer: B

45. Which tool removes malware in Windows?
    A. Task Manager
    B. Windows Defender
    C. Disk Backup
    D. BIOS
    Answer: B

C. Networking (46–70)

46. LAN stands for:
    A. Local Access Network
    B. Local Area Network
    C. Large Area Network
    D. Link Access Node
    Answer: B

47. Common Wi-Fi standard:
    A. 802.11
    B. 909.11
    C. 505.20
    D. 702.10
    Answer: A

48. What device connects multiple networks?
    A. Router
    B. Switch
    C. Hub
    D. Repeater
    Answer: A

49. A switch works at which OSI layer?
    A. Layer 1
    B. Layer 2
    C. Layer 5
    D. Layer 7
    Answer: B

50. IPv4 length:
    A. 16 bit
    B. 32 bit
    C. 64 bit
    D. 128 bit
    Answer: B

51. Which command tests network connectivity?
    A. ipconfig
    B. ping
    C. netstat
    D. tracert
    Answer: B

52. Which device broadcasts all traffic?
    A. Router
    B. Switch
    C. Hub
    D. Firewall
    Answer: C

53. Default gateway refers to:
    A. Local router address
    B. MAC address
    C. DNS server
    D. IP lease
    Answer: A

54. DNS translates:
    A. IP → URL
    B. URL → IP
    C. MAC → IP
    D. IP → Hostname only
    Answer: B

55. Wi-Fi security type:
    A. WPA2
    B. HTTP
    C. FTP
    D. QRX
    Answer: A

56. Subnet mask of a typical home network:
    A. 255.0.0.0
    B. 255.255.255.0
    C. 255.255.0.0
    D. 255.255.255.255
    Answer: B

57. MAC address is:
    A. 32 bits
    B. 48 bits
    C. 64 bits
    D. 128 bits
    Answer: B

58. Which command shows active connections?
    A. ping
    B. netstat
    C. gpupdate
    D. cls
    Answer: B

59. CAT6 max speed:
    A. 100 Mbps
    B. 1 Gbps
    C. 10 Gbps
    D. 40 Gbps
    Answer: C

60. Port for HTTP:
    A. 25
    B. 443
    C. 80
    D. 21
    Answer: C

61. Port for FTP:
    A. 21
    B. 53
    C. 110
    D. 443
    Answer: A

62. Port for DNS:
    A. 25
    B. 53
    C. 110
    D. 23
    Answer: B

63. A repeater is used to:
    A. Filter malware
    B. Extend network signal
    C. Increase power supply
    D. Block websites
    Answer: B

64. Firewall function:
    A. Video rendering
    B. Blocks unauthorized traffic
    C. Cleans RAM
    D. Formats HDD
    Answer: B

65. Common wireless frequency:
    A. 1 GHz
    B. 2.4 GHz
    C. 10 GHz
    D. 0.5 GHz
    Answer: B

66. Network topology with a central hub:
    A. Ring
    B. Mesh
    C. Star
    D. Bus
    Answer: C

67. VPN is used for:
    A. Gaming
    B. Secure remote access
    C. Cooling
    D. Disk repair
    Answer: B

68. Loopback IP:
    A. 192.168.0.1
    B. 10.0.0.1
    C. 8.8.8.8
    D. 127.0.0.1
    Answer: D

69. Which tool checks path to a destination?
    A. ping
    B. tracert
    C. ls
    D. scanpath
    Answer: B

70. RJ45 connector is used for:
    A. Power
    B. Keyboard
    C. Ethernet
    D. USB
    Answer: C

---

D. Troubleshooting & Safety (71–100)

71. Which tool removes dust from a PC?
    A. Water
    B. Blower/compressed air
    C. Oil
    D. Paintbrush
    Answer: B

72. ESD stands for:
    A. Electronic Storage Data
    B. Electrostatic Discharge
    C. Emergency System Device
    D. Electrical System Detector
    Answer: B

73. Best surface to place a motherboard during repair:
    A. Metallic table
    B. Carpet
    C. Wood or non-conductive surface
    D. Wet cloth
    Answer: C

74. PC beeps during startup indicates:
    A. Successful boot
    B. Hardware error
    C. Virus
    D. Low battery only
    Answer: B

75. If PC does not power on, first check:
    A. GPU
    B. RAM
    C. Power cable & PSU
    D. Mouse
    Answer: C

76. Overheating is commonly caused by:
    A. Too much RAM
    B. Bad cooling system
    C. Fast internet
    D. Keyboard error
    Answer: B

77. Blue screen errors in Windows are called:
    A. BSA
    B. BSOD
    C. BSOS
    D. WSE
    Answer: B

78. If no display appears, check:
    A. Monitor power
    B. GPU connection
    C. RAM
    D. All of the above
    Answer: D

79. Which tool tests RAM?
    A. MemTest
    B. DiskPart
    C. WinZip
    D. BIOS updater
    Answer: A

80. If computer auto-restarts, likely cause:
    A. Bad PSU
    B. Malware
    C. Overheating
    D. All of the above
    Answer: D

81. CMOS battery type:
    A. AAA
    B. CR2032
    C. 9V
    D. AA
    Answer: B

82. If hard drive not detected:
    A. Check SATA cable
    B. Check BIOS
    C. Check power connection
    D. All of the above
    Answer: D

83. Which tool tests the PSU?
    A. Multimeter
    B. Screwdriver
    C. Flashlight
    D. HDMI cable
    Answer: A

84. Which key enters BIOS?
    A. F1
    B. F2
    C. DEL
    D. Any of the above depending on PC
    Answer: D

85. System freezing can be caused by:
    A. Bad RAM
    B. Overheating
    C. Malware
    D. All of the above
    Answer: D

86. S.M.A.R.T. is related to:
    A. RAM
    B. HDD health
    C. GPU
    D. CPU temperature
    Answer: B

87. If PC makes grinding noise:
    A. HDD failure
    B. GPU fan
    C. Case fan
    D. All possible
    Answer: D

88. If keyboard not responding:
    A. Check USB port
    B. Test with another PC
    C. Replace keyboard
    D. All of the above
    Answer: D

89. Which antivirus is built into Windows?
    A. Kaspersky
    B. Avast
    C. Windows Defender
    D. Bitdefender
    Answer: C

90. To remove stubborn malware use:
    A. Windows Firewall
    B. System Restore
    C. Safe Mode + antivirus
    D. Notepad
    Answer: C

91. SMART errors suggest:
    A. RAM failure
    B. HDD nearing failure
    C. Screen issue
    D. GPU crash
    Answer: B

92. Unexpected shutdowns may be caused by:
    A. Overheating
    B. PSU failure
    C. Motherboard failure
    D. All of the above
    Answer: D

93. To install OS from USB you must:
    A. Format USB
    B. Make it bootable
    C. Change boot priority
    D. All of the above
    Answer: D

94. A burning smell inside PC indicates:
    A. Software error
    B. Hardware damage
    C. Browser problem
    D. Driver issue
    Answer: B

95. If system time resets every boot:
    A. Virus
    B. CMOS battery dead
    C. PSU failure
    D. GPU problem
    Answer: B

96. STOP error in Windows refers to:
    A. Slow PC
    B. BSOD
    C. Safe Mode
    D. Low memory
    Answer: B

97. Tool to check memory usage:
    A. WinZip
    B. Task Manager
    C. Notepad
    D. CMD only
    Answer: B

98. Which command repairs Windows boot files?
    A. bootrec /fixmbr
    B. ipconfig
    C. net stop
    D. cmdrepair
    Answer: A

99. If PC powers but no display, try:
    A. Reseat RAM
    B. Reseat GPU
    C. Clear CMOS
    D. All of the above
    Answer: D

100. Best practice after assembling a PC:
     A. Power it on immediately
     B. Check all connections first
     C. Install games first
     D. Shake the case
     Answer: B

FOR ANY ENQUIRIES

👇

WhatsApp: +255 692 127 931

___________________________________________________________________________________

                                                                ***ALL THE BEST*** 

WE SHARE KNOWLEDGE, YOU SHARE SUPPORT BY SUBSCRIBING TO MY CHANNEL

                      👇

Salehe Njohole - YouTube    (🙏)

Read More

SQL TRIGGERS

 C:\Users\ponjo>mysql -u root -p

Enter password:

MariaDB [(none)]> create database uni;

Query OK, 1 row affected (0.003 sec)

MariaDB [(none)]> create table students (

    -> id INT PRIMARY KEY AUTO_INCREMENT,

    -> fname VARCHAR(100),

    -> age INT,

    -> gender ENUM('F','M'),

    -> ;

ERROR 1046 (3D000): No database selected

MariaDB [(none)]>

MariaDB [(none)]> use uni;

Database changed

MariaDB [uni]> CREATE TABLE courses (

    -> id INT PRIMARY KEY AUTO_INCREMENT,

    -> course_name VARCHAR(100)

    -> );

Query OK, 0 rows affected (0.013 sec)

MariaDB [uni]> CREATE TABLE department (

    -> id INT PRIMARY KEY AUTO_INCREMENT,

    -> dept_name VARCHAR(100)

    -> );

Query OK, 0 rows affected (0.018 sec)

MariaDB [uni]> CREATE TABLE students (

    -> id INT PRIMARY KEY AUTO_INCREMENT,

    -> fname VARCHAR(100),

    -> age INT,

    -> gender ENUM('F','M'),

    -> course_id INT,

    -> dept_id INT,

    -> FOREIGN KEY (course_id) REFERENCES courses(id),

    -> FOREIGN KEY (dept_id) REFERENCES department(id)

    -> );

Query OK, 0 rows affected (0.038 sec)

MariaDB [uni]> CREATE TABLE grades (

    -> id INT PRIMARY KEY AUTO_INCREMENT,

    -> student_id INT,

    -> course_id INT,

    -> grades VARCHAR(100),

    -> FOREIGN KEY (student_id) REFERENCES students(id),

    -> FOREIGN KEY (course_id) REFERENCES courses(id)

    -> );

Query OK, 0 rows affected (0.048 sec)

MariaDB [uni]> SHOW TABLES;

+---------------+

| Tables_in_uni |

+---------------+

| courses       |

| department    |

| grades        |

| students      |

+---------------+

4 rows in set (0.001 sec)

MariaDB [uni]> DESCRIBE courses;

+-------------+--------------+------+-----+---------+----------------+

| Field       | Type         | Null | Key | Default | Extra          |

+-------------+--------------+------+-----+---------+----------------+

| id          | int(11)      | NO   | PRI | NULL    | auto_increment |

| course_name | varchar(100) | YES  |     | NULL    |                |

+-------------+--------------+------+-----+---------+----------------+

2 rows in set (0.034 sec)

MariaDB [uni]> INSERT INTO courses(course_name) VALUES (

    -> 'BIT'),('ISM'),('SE'),('CS');

Query OK, 4 rows affected (0.063 sec)

Records: 4  Duplicates: 0  Warnings: 0

MariaDB [uni]> DESCRIBE departments;

ERROR 1146 (42S02): Table 'uni.departments' doesn't exist

MariaDB [uni]> DESCRIBE department;

+-----------+--------------+------+-----+---------+----------------+

| Field     | Type         | Null | Key | Default | Extra          |

+-----------+--------------+------+-----+---------+----------------+

| id        | int(11)      | NO   | PRI | NULL    | auto_increment |

| dept_name | varchar(100) | YES  |     | NULL    |                |

+-----------+--------------+------+-----+---------+----------------+

2 rows in set (0.031 sec)

MariaDB [uni]> INSERT INTO department(dept_name) VALUES (

    -> 'ICT'),('ACCOUNTS'),('HR'),('FINANCE');

Query OK, 4 rows affected (0.008 sec)

Records: 4  Duplicates: 0  Warnings: 0

MariaDB [uni]> DESCRIBE students;

+-----------+---------------+------+-----+---------+----------------+

| Field     | Type          | Null | Key | Default | Extra          |

+-----------+---------------+------+-----+---------+----------------+

| id        | int(11)       | NO   | PRI | NULL    | auto_increment |

| fname     | varchar(100)  | YES  |     | NULL    |                |

| age       | int(11)       | YES  |     | NULL    |                |

| gender    | enum('F','M') | YES  |     | NULL    |                |

| course_id | int(11)       | YES  | MUL | NULL    |                |

| dept_id   | int(11)       | YES  | MUL | NULL    |                |

+-----------+---------------+------+-----+---------+----------------+

6 rows in set (0.032 sec)

MariaDB [uni]> INSERT INTO students(fname,age,gender,course_id,dept_id)

    -> VALUES ('Mary',32,'F',2,1);

Query OK, 1 row affected (0.031 sec)

MariaDB [uni]> INSERT INTO students(fname,age,gender,course_id,dept_id)

    -> VALUES ('Jenny',28,'F',3,3),('Juma',40,'M',4,4);

Query OK, 2 rows affected (0.013 sec)

Records: 2  Duplicates: 0  Warnings: 0

MariaDB [uni]> DESCRIBE grades;

+------------+--------------+------+-----+---------+----------------+

| Field      | Type         | Null | Key | Default | Extra          |

+------------+--------------+------+-----+---------+----------------+

| id         | int(11)      | NO   | PRI | NULL    | auto_increment |

| student_id | int(11)      | YES  | MUL | NULL    |                |

| course_id  | int(11)      | YES  | MUL | NULL    |                |

| grades     | varchar(100) | YES  |     | NULL    |                |

+------------+--------------+------+-----+---------+----------------+

4 rows in set (0.025 sec)

MariaDB [uni]> INSERT INTO grades(student_id,course_id,grades) VALUES (

    -> 1,2,'A'),(2,3,'C'),(3,4,'B+');

Query OK, 3 rows affected (0.008 sec)

Records: 3  Duplicates: 0  Warnings: 0

MariaDB [uni]> SELECT * FROM courses;

+----+-------------+

| id | course_name |

+----+-------------+

|  1 | BIT         |

|  2 | ISM         |

|  3 | SE          |

|  4 | CS          |

+----+-------------+

4 rows in set (0.000 sec)

MariaDB [uni]> SELECT * FROM department;

+----+-----------+

| id | dept_name |

+----+-----------+

|  1 | ICT       |

|  2 | ACCOUNTS  |

|  3 | HR        |

|  4 | FINANCE   |

+----+-----------+

4 rows in set (0.000 sec)

MariaDB [uni]> SELECT * FROM students;

+----+-------+------+--------+-----------+---------+

| id | fname | age  | gender | course_id | dept_id |

+----+-------+------+--------+-----------+---------+

|  1 | Mary  |   32 | F      |         2 |       1 |

|  2 | Jenny |   28 | F      |         3 |       3 |

|  3 | Juma  |   40 | M      |         4 |       4 |

+----+-------+------+--------+-----------+---------+

3 rows in set (0.000 sec)

MariaDB [uni]> SELECT * FROM grades;

+----+------------+-----------+--------+

| id | student_id | course_id | grades |

+----+------------+-----------+--------+

|  1 |          1 |         2 | A      |

|  2 |          2 |         3 | C      |

|  3 |          3 |         4 | B+     |

+----+------------+-----------+--------+

3 rows in set (0.001 sec)

MariaDB [uni]> ALTER TABLE students ADD COLUMN created_at DATETIME;

Query OK, 0 rows affected (0.011 sec)

Records: 0  Duplicates: 0  Warnings: 0

MariaDB [uni]> SELECT * FROM students;

+----+-------+------+--------+-----------+---------+------------+

| id | fname | age  | gender | course_id | dept_id | created_at |

+----+-------+------+--------+-----------+---------+------------+

|  1 | Mary  |   32 | F      |         2 |       1 | NULL       |

|  2 | Jenny |   28 | F      |         3 |       3 | NULL       |

|  3 | Juma  |   40 | M      |         4 |       4 | NULL       |

+----+-------+------+--------+-----------+---------+------------+

3 rows in set (0.001 sec)

MariaDB [uni]> CREATE TRIGGER student_insert BEFORE INSERT ON students

    -> FOR EACH ROW SET NEW.created_at = NOW();

Query OK, 0 rows affected (0.011 sec)

MariaDB [uni]> INSERT INTO students(fname,age,gender,course_id,dept_id)

    -> VALUES ('Jackline',20,'F',3,3);

Query OK, 1 row affected (0.010 sec)

MariaDB [uni]> SELECT * FROM students;

+----+----------+------+--------+-----------+---------+---------------------+

| id | fname    | age  | gender | course_id | dept_id | created_at          |

+----+----------+------+--------+-----------+---------+---------------------+

|  1 | Mary     |   32 | F      |         2 |       1 | NULL                |

|  2 | Jenny    |   28 | F      |         3 |       3 | NULL                |

|  3 | Juma     |   40 | M      |         4 |       4 | NULL                |

|  4 | Jackline |   20 | F      |         3 |       3 | 2025-12-01 22:14:51 |

+----+----------+------+--------+-----------+---------+---------------------+

4 rows in set (0.001 sec)

MariaDB [uni]> SHOW TRIGGERS;

+----------------+--------+----------+----------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+

| Trigger        | Event  | Table    | Statement                  | Timing | Created                | sql_mode                                            | Definer        | character_set_client | collation_connection | Database Collation |

+----------------+--------+----------+----------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+

| student_insert | INSERT | students | SET NEW.created_at = NOW() | BEFORE | 2025-12-01 22:11:40.34 | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | root@localhost | cp850                | cp850_general_ci     | utf8mb4_general_ci |

+----------------+--------+----------+----------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+

1 row in set (0.024 sec)

MariaDB [uni]> CREATE TABLE student_backups (

    -> id INT PRIMARY KEY AUTO_INCREMENT,

    -> fname VARCHAR(100),

    -> course_id INT,

    -> dept_id INT

    -> );

Query OK, 0 rows affected (0.019 sec)

MariaDB [uni]> CREATE TRIGGER student_backup AFTER INSERT ON students

    -> FOR EACH ROW INSERT INTO student_backups(fname,course_id,dept_id)

    -> VALUES (NEW.fname,NEW.course_id,NEW.dept.id);

Query OK, 0 rows affected (0.015 sec)

MariaDB [uni]> INSERT INTO students(fname,age,gender,course_id,dept_id)

    -> VALUES ('John',30,'M',3,3);

ERROR 1054 (42S22): Unknown column 'NEW.dept.id' in 'field list'

MariaDB [uni]> DROP TRIGGER student_backup;

Query OK, 0 rows affected (0.012 sec)

MariaDB [uni]> CREATE TRIGGER student_backup AFTER INSERT ON students

    -> FOR EACH ROW INSERT INTO student_backups(fname,course_id,dept_id)

    -> VALUES (NEW.fname,NEW.course_id,NEW.dept_id);

Query OK, 0 rows affected (0.017 sec)

MariaDB [uni]> INSERT INTO students(fname,age,gender,course_id,dept_id)

    -> VALUES ('John',30,'M',3,3);

Query OK, 1 row affected (0.010 sec)

MariaDB [uni]> SELECT * FROM student_backups;

+----+-------+-----------+---------+

| id | fname | course_id | dept_id |

+----+-------+-----------+---------+

|  1 | John  |         3 |       3 |

+----+-------+-----------+---------+

1 row in set (0.000 sec)

MariaDB [uni]> SELECT * students;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'students' at line 1

MariaDB [uni]> SELECT * FROM students;

+----+----------+------+--------+-----------+---------+---------------------+

| id | fname    | age  | gender | course_id | dept_id | created_at          |

+----+----------+------+--------+-----------+---------+---------------------+

|  1 | Mary     |   32 | F      |         2 |       1 | NULL                |

|  2 | Jenny    |   28 | F      |         3 |       3 | NULL                |

|  3 | Juma     |   40 | M      |         4 |       4 | NULL                |

|  4 | Jackline |   20 | F      |         3 |       3 | 2025-12-01 22:14:51 |

|  6 | John     |   30 | M      |         3 |       3 | 2025-12-01 22:23:06 |

+----+----------+------+--------+-----------+---------+---------------------+

5 rows in set (0.000 sec)

MariaDB [uni]> ALTER TABLE students ADD COLUMN update_at DATETIME;

Query OK, 0 rows affected (0.015 sec)

Records: 0  Duplicates: 0  Warnings: 0

MariaDB [uni]> SELECT * FROM students;

+----+----------+------+--------+-----------+---------+---------------------+-----------+

| id | fname    | age  | gender | course_id | dept_id | created_at          | update_at |

+----+----------+------+--------+-----------+---------+---------------------+-----------+

|  1 | Mary     |   32 | F      |         2 |       1 | NULL                | NULL      |

|  2 | Jenny    |   28 | F      |         3 |       3 | NULL                | NULL      |

|  3 | Juma     |   40 | M      |         4 |       4 | NULL                | NULL      |

|  4 | Jackline |   20 | F      |         3 |       3 | 2025-12-01 22:14:51 | NULL      |

|  6 | John     |   30 | M      |         3 |       3 | 2025-12-01 22:23:06 | NULL      |

+----+----------+------+--------+-----------+---------+---------------------+-----------+

5 rows in set (0.001 sec)

MariaDB [uni]> CREATE TRIGGER update_student BEFORE UPDATE ON students

    -> FOR EACH ROW SET NEW.update_at = NOW();

Query OK, 0 rows affected (0.013 sec)

MariaDB [uni]> UPDATE students SET fname = 'Mariam' WHERE id=1;

Query OK, 1 row affected (0.011 sec)

Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [uni]> SELECT * FROM students;

+----+----------+------+--------+-----------+---------+---------------------+---------------------+

| id | fname    | age  | gender | course_id | dept_id | created_at          | update_at           |

+----+----------+------+--------+-----------+---------+---------------------+---------------------+

|  1 | Mariam   |   32 | F      |         2 |       1 | NULL                | 2025-12-01 22:32:25 |

|  2 | Jenny    |   28 | F      |         3 |       3 | NULL                | NULL                |

|  3 | Juma     |   40 | M      |         4 |       4 | NULL                | NULL                |

|  4 | Jackline |   20 | F      |         3 |       3 | 2025-12-01 22:14:51 | NULL                |

|  6 | John     |   30 | M      |         3 |       3 | 2025-12-01 22:23:06 | NULL                |

+----+----------+------+--------+-----------+---------+---------------------+---------------------+

5 rows in set (0.001 sec)

MariaDB [uni]> SELECT * FROM grades;

+----+------------+-----------+--------+

| id | student_id | course_id | grades |

+----+------------+-----------+--------+

|  1 |          1 |         2 | A      |

|  2 |          2 |         3 | C      |

|  3 |          3 |         4 | B+     |

+----+------------+-----------+--------+

3 rows in set (0.001 sec)

MariaDB [uni]> CREATE TABLE grade_backup (

    -> id INT PRIMARY KEY AUTO_INCREMENT,

    -> student_id INT,

    -> course_id INT,

    -> old_grade VARCHAR(100),

    -> new_grade VARCHAR(100),

    -> created_at DATETIME

    -> );

Query OK, 0 rows affected (0.015 sec)

MariaDB [uni]> CREATE TRIGGER student_update_backup AFTER UPDATE ON

    -> grades FOR EACH ROW INSERT INTO grade_backup(student_id,course_id,old_grade,new_grade,created_at)

    -> VALUES (OLD.student_id,OLD.course_id,OLD.grades,NEW.grades,NOW());

Query OK, 0 rows affected (0.017 sec)

MariaDB [uni]> UPDATE students SET fname = 'Mary' WHERE id=1;

Query OK, 1 row affected (0.002 sec)

Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [uni]> SELECT * FROM grades;

+----+------------+-----------+--------+

| id | student_id | course_id | grades |

+----+------------+-----------+--------+

|  1 |          1 |         2 | A      |

|  2 |          2 |         3 | C      |

|  3 |          3 |         4 | B+     |

+----+------------+-----------+--------+

3 rows in set (0.004 sec)

MariaDB [uni]> UPDATE grades SET grades='C' WHERE id=1;

Query OK, 1 row affected (0.002 sec)

Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [uni]> SELECT * FROM grades;

+----+------------+-----------+--------+

| id | student_id | course_id | grades |

+----+------------+-----------+--------+

|  1 |          1 |         2 | C      |

|  2 |          2 |         3 | C      |

|  3 |          3 |         4 | B+     |

+----+------------+-----------+--------+

3 rows in set (0.000 sec)

MariaDB [uni]> SELECT * FROM grade_backup;

+----+------------+-----------+-----------+-----------+---------------------+

| id | student_id | course_id | old_grade | new_grade | created_at          |

+----+------------+-----------+-----------+-----------+---------------------+

|  1 |          1 |         2 | A         | C         | 2025-12-01 22:48:15 |

+----+------------+-----------+-----------+-----------+---------------------+

1 row in set (0.000 sec)

MariaDB [uni]> CREATE TRIGGER delete_grades BEFORE DELETE ON grades

    -> FOR EACH ROW SIGNAL SQLSTATE'45000' SET MESSAGE_TEXT = 'You cant Delete Student Grade';

Query OK, 0 rows affected (0.008 sec)

MariaDB [uni]> DELETE FROM grade WHERE id=1;

ERROR 1146 (42S02): Table 'uni.grade' doesn't exist

MariaDB [uni]> DELETE FROM grades WHERE id=1;

ERROR 1644 (45000): You cant Delete Student Grade

MariaDB [uni]>

MariaDB [uni]>

MariaDB [uni]> CREATE TABLE deleted_grade(

    -> id INT PRIMARY KEY AUTO_INCREMENT,

    -> student_id INT,

    -> course_id INT,

    -> grade VARCHAR(100),

    -> deleted_at DATETIME

    -> );

Query OK, 0 rows affected (0.022 sec)

MariaDB [uni]> CREATE TRIGGER deleted_grade AFTER DELETE ON grades

    -> FOR EACH ROW INSERT INTO deleted_grade(student_id,course_id,grade,deleted_at)

    -> VALUES (OLD.student_id,OLD.course_id,OLD.grades,NOW());

Query OK, 0 rows affected (0.014 sec)

MariaDB [uni]> DROP TRIGGER delete_grades;

Query OK, 0 rows affected (0.016 sec)

MariaDB [uni]> DELETE FROM grade WHERE id=1;

ERROR 1146 (42S02): Table 'uni.grade' doesn't exist

MariaDB [uni]> DELETE FROM grades WHERE id=1;

Query OK, 1 row affected (0.004 sec)

MariaDB [uni]> select * FROM deleted_grade;

+----+------------+-----------+-------+---------------------+

| id | student_id | course_id | grade | deleted_at          |

+----+------------+-----------+-------+---------------------+

|  1 |          1 |         2 | C     | 2025-12-01 23:04:07 |

+----+------------+-----------+-------+---------------------+

1 row in set (0.001 sec)

Read More

SQL Server REST API

 

SQL Server REST API

 

In today's digital landscape, the demand for secure and efficient data access is paramount. One common approach to achieving this is through the implementation of a REST API, which allows for seamless communication between client applications and databases.

 

In this article, we will delve into the process of building a secure SQL Server REST API, covering essential concepts and providing detailed examples along the way.

 

Understanding REST APIs and SQL Server

 

REST API: REST (Representational State Transfer) is an architectural style for designing applications. REST APIs allow clients to interact with server resources using standard HTTP methods such as GET, POST, PUT, and DELETE. These APIs are stateless, meaning each request from a client contains all the information necessary for the server to fulfill the request.

 

 

SQL Server: Microsoft SQL Server is a relational database management system (RDBMS) widely used for storing and managing data. It supports various security features, including authentication, authorization, and encryption, which are crucial for building secure applications.

 

 

Designing the API

 

The first step in building a secure SQL Server REST API is designing the API endpoints and defining the data model. Let's consider a simple example of a user management system with the following endpoints

GET /users: Retrieve a list of all users.

 

GET /users/{id}: Retrieve information about a specific user.

 

POST /users: Create a new user.

 

PUT /users/{id}: Update information about a specific user.

 

DELETE /users/{id}: Delete a user.

 

 

Setting Up the Environment

 

Next, we need to set up our development environment. We'll use Node.js and Express.js for building the API, and npm packages such as express, body-parser, and mssql for handling HTTP requests, parsing request bodies, and interacting with the SQL Server database.

 

npm install express body-parser mssql

 

 

 

Implementing Authentication and Authorization

 

For securing an API we use Authentication and authorization. We'll use JSON Web Tokens (JWT) for authentication and role-based access control (RBAC) for authorization.

 

// Authentication middleware

 

const jwt = require('jsonwebtoken');

 

 

 

 

function authenticateToken(req, res, next) { const token = req.headers['authorization'];

if (!token) return res.status(401).json({ message:

 

'Unauthorized' });

 

 

 

 

jwt.verify(token, process.env.ACCESS_TOKEN_SECRET, (err, user)

 

=> {

 

if (err) return res.status(403).json({ message: 'Forbidden'

 

});

 

req.user = user;

 

next();

});

 

}

 

 

 

//  Authorization middleware function authorize(role) {

 

return (req, res, next) => {

 

if (req.user.role !== role) return res.status(403).json({

 

message: 'Forbidden' }); next();

 

};

 

}

 

 

 

Connecting to SQL Server

 

We'll establish a connection to the SQL Server database using the mssql package and execute SQL queries to interact with the database.

 

const sql = require('mssql');

 

 

const config = {

 

user: 'username',

 

password: 'password',

 

server: 'localhost',

 

database: 'dbname',

 

};

 

 

async function executeQuery(query) { try {

await sql.connect(config);

 

const result = await sql.query(query); return result.recordset;

 

} catch (err) { console.error(err); throw err;

 

} finally { sql.close();

}

 

}

 

 

 

Implementing API Endpoints

 

Now, let's implement the API endpoints using Express.js.

 

const express = require('express');

 

const bodyParser = require('body-parser');

 

 

 

 

const app = express();

 

app.use(bodyParser.json());

 

 

 

 

// GET /users

 

app.get('/users', authenticateToken, authorize('admin'), async (req, res) => {

 

try {

 

const users = await executeQuery('SELECT * FROM users'); res.json(users);

 

} catch (err) {

 

res.status(500).json({ message: 'Internal server error' });

 

}

 

});

 

 

 

Data Validation

 

Data validation is essential to ensure that the information provided by clients meets the expected criteria. This step helps prevent malformed requests and protects the integrity of the database.

 

// Data validation middleware

 

function validateData(req, res, next) {

 

//  Example: Validate user creation payload const { username, email, password } = req.body; if (!username || !email || !password) {

 

return res.status(400).json({ message: 'Missing required

 

fields' });

}

 

//  Additional validation logic...

 

next();

 

}

 

 

 

Error Handling

 

Proper error handling ensures that clients receive meaningful error messages and helps developers diagnose and resolve issues efficiently.

 

// Error handling middleware

 

function errorHandler(err, req, res, next) { console.error(err.stack);

 

res.status(500).json({ message: 'Internal server error' });

 

}

 

 

app.use(errorHandler);

 

 

 

Securing Sensitive Information

 

When interacting with databases, it's crucial to handle sensitive information securely. This includes encrypting passwords and limiting exposure to sensitive data.

 

//  Hashing passwords before storing them const bcrypt = require('bcrypt');

 

const saltRounds = 10;

 

 

async function hashPassword(password) {

 

return await bcrypt.hash(password, saltRounds);

 

}

 

 

// Example: POST /users

 

app.post('/users', authenticateToken, authorize('admin'),
validateData,	async (req, res) => {	Sign In
cts   Interview Questions	Quiz   DBMS   PostgreSQL   Django   ReactJS   Vue.js
try {
const	{ username, email, password } = req.body;
const	hashedPassword = await hashPassword(password);

const query = `INSERT INTO users (username, email, password) VALUES ('${username}', '${email}', '${hashedPassword}')`;

 

await executeQuery(query);

 

res.status(201).json({ message: 'User created successfully'

 

});

 

} catch (err) {

 

res.status(500).json({ message: 'Internal server error' });

 

}

 

});

 

 

 

Conclusion

 

we've covered the process of building a secure SQL Server REST API from scratch. We've discussed key concepts such as REST APIs, SQL Server, authentication, authorization, and implementation using Node.js and Express.js. By following best practices and incorporating security measures, you can ensure that your API is robust and protected against potential threats.

Read More