2025 ~ SALEHE NJOHOLE

Saturday, December 20, 2025

DATABASE ADMINISTRATOR QUESTIONS AND ANSWERS

By njohole3:49 AM No comments:

DATABASE ADMINISTRATOR QUESTIONS AND ANSWERS

Section A: Database Fundamentals (1–15)

A database is best described as:
A. A collection of programs
B. A collection of related data
C. A programming language
D. A storage device
Answer: B
DBMS stands for:
A. Data Backup Management System
B. Database Management System
C. Digital Base Management System
D. Data Binary Management System
Answer: B
Which of the following is NOT a DBMS?
A. MySQL
B. Oracle
C. PostgreSQL
D. Python
Answer: D
The person responsible for managing databases is called:
A. Programmer
B. System Analyst
C. Database Administrator
D. Network Engineer
Answer: C
Which level of database architecture describes how data is stored?
A. External
B. Conceptual
C. Internal
D. Logical
Answer: C
The logical structure of a database is known as:
A. Schema
B. Table
C. Index
D. Instance
Answer: A
A table row is also known as:
A. Attribute
B. Record
C. Field
D. Key
Answer: B
A table column is also known as:
A. Tuple
B. Record
C. Attribute
D. Row
Answer: C
Which key uniquely identifies a record?
A. Foreign key
B. Candidate key
C. Primary key
D. Composite key
Answer: C
A foreign key is used to:
A. Uniquely identify records
B. Improve performance
C. Link tables
D. Encrypt data
Answer: C
Which model uses tables to store data?
A. Hierarchical
B. Network
C. Relational
D. Object-oriented
Answer: C
SQL is used to:
A. Design networks
B. Manage databases
C. Write operating systems
D. Create hardware
Answer: B
Which command is used to retrieve data?
A. INSERT
B. UPDATE
C. DELETE
D. SELECT
Answer: D
Which command removes a table permanently?
A. DELETE
B. DROP
C. TRUNCATE
D. REMOVE
Answer: B
Which normal form removes transitive dependency?
A. 1NF
B. 2NF
C. 3NF
D. BCNF
Answer: C

Section B: SQL & Query Management (16–35)

Which SQL clause filters records?
A. ORDER BY
B. GROUP BY
C. WHERE
D. HAVING
Answer: C
Which function returns the number of rows?
A. SUM()
B. COUNT()
C. AVG()
D. MAX()
Answer: B
Which JOIN returns matching records only?
A. LEFT JOIN
B. RIGHT JOIN
C. FULL JOIN
D. INNER JOIN
Answer: D
Which JOIN returns all records from the left table?
A. RIGHT JOIN
B. LEFT JOIN
C. FULL JOIN
D. INNER JOIN
Answer: B
Which keyword sorts results?
A. SORT
B. ORDER BY
C. GROUP BY
D. FILTER
Answer: B
Which command changes table structure?
A. UPDATE
B. ALTER
C. MODIFY
D. CHANGE
Answer: B
Which constraint prevents NULL values?
A. UNIQUE
B. PRIMARY KEY
C. NOT NULL
D. CHECK
Answer: C
Which constraint enforces data uniqueness?
A. CHECK
B. NOT NULL
C. UNIQUE
D. DEFAULT
Answer: C
A view is:
A. A physical table
B. A virtual table
C. An index
D. A trigger
Answer: B
Which command removes all rows but keeps table structure?
A. DELETE
B. DROP
C. TRUNCATE
D. REMOVE
Answer: C
Which clause is used with aggregate functions?
A. WHERE
B. GROUP BY
C. ORDER BY
D. DISTINCT
Answer: B
Which operator checks for a range of values?
A. LIKE
B. BETWEEN
C. IN
D. EXISTS
Answer: B
Which operator checks pattern matching?
A. LIKE
B. IN
C. BETWEEN
D. IS
Answer: A
Which SQL command is DDL?
A. SELECT
B. INSERT
C. CREATE
D. UPDATE
Answer: C
Which SQL command is DML?
A. DROP
B. CREATE
C. INSERT
D. ALTER
Answer: C
Which SQL command is TCL?
A. COMMIT
B. SELECT
C. INSERT
D. CREATE
Answer: A
Which SQL command is DCL?
A. GRANT
B. SELECT
C. INSERT
D. UPDATE
Answer: A
Which index improves query performance?
A. Clustered
B. Non-clustered
C. Primary
D. All of the above
Answer: D
Which statement removes duplicate rows?
A. UNIQUE
B. DISTINCT
C. GROUP BY
D. HAVING
Answer: B
Which SQL feature ensures data consistency?
A. Index
B. Constraint
C. Trigger
D. View
Answer: B

Section C: DBA Roles, Security & Performance (36–70)

Which is a DBA responsibility?
A. Data entry
B. Database backup
C. Writing applications
D. Network cabling
Answer: B
Backup ensures protection against:
A. Virus only
B. Power failure only
C. Data loss
D. Slow queries
Answer: C
Which backup copies only changed data?
A. Full
B. Differential
C. Incremental
D. Mirror
Answer: C
Which backup is fastest to restore?
A. Incremental
B. Differential
C. Full
D. Partial
Answer: C
Which ensures authorized access?
A. Indexing
B. Normalization
C. Authentication
D. Replication
Answer: C
Which command gives user permission?
A. REVOKE
B. ALLOW
C. GRANT
D. ASSIGN
Answer: C
Which command removes permission?
A. DENY
B. DELETE
C. DROP
D. REVOKE
Answer: D
Encryption protects data:
A. At rest
B. In transit
C. Both
D. None
Answer: C
Which improves query speed?
A. Backup
B. Index
C. Trigger
D. View
Answer: B
Which tool monitors database performance?
A. Profiler
B. Compiler
C. Debugger
D. Editor
Answer: A
Deadlock occurs when:
A. Server shuts down
B. Queries run fast
C. Transactions wait on each other
D. Backup fails
Answer: C
Which resolves deadlocks?
A. Commit
B. Rollback
C. Index
D. View
Answer: B
Which isolation level prevents dirty reads?
A. Read uncommitted
B. Read committed
C. Repeatable read
D. Serializable
Answer: B
Which isolation level is strictest?
A. Read committed
B. Repeatable read
C. Serializable
D. Snapshot
Answer: C
ACID stands for:
A. Atomicity, Consistency, Isolation, Durability
B. Accuracy, Control, Integrity, Data
C. Access, Control, Index, Data
D. Atomic, Consistent, Internal, Durable
Answer: A

Section D: Advanced DBA Concepts (71–100)

Replication is used for:
A. Backup only
B. Data synchronization
C. Indexing
D. Security
Answer: B
Sharding improves:
A. Security
B. Scalability
C. Backup
D. Normalization
Answer: B
Which database is NoSQL?
A. Oracle
B. MySQL
C. MongoDB
D. SQL Server
Answer: C
Which NoSQL database is document-based?
A. Redis
B. Cassandra
C. MongoDB
D. Neo4j
Answer: C
Which NoSQL database is graph-based?
A. MongoDB
B. Redis
C. Neo4j
D. Cassandra
Answer: C
Which command optimizes tables?
A. ANALYZE
B. OPTIMIZE
C. TUNE
D. FIX
Answer: B
Which log records all changes?
A. Error log
B. Binary log
C. Query log
D. Access log
Answer: B
Which ensures high availability?
A. Backup
B. Replication
C. Normalization
D. Indexing
Answer: B
Which detects slow queries?
A. Profiler
B. Slow query log
C. Error log
D. Trigger
Answer: B
Which storage ensures durability?
A. RAM
B. Cache
C. Disk
D. Register
Answer: C
Which feature automates actions on events?
A. View
B. Trigger
C. Index
D. Cursor
Answer: B
Which cursor processes rows one by one?
A. Static
B. Dynamic
C. Implicit
D. Explicit
Answer: D
Which tool restores data?
A. Backup
B. Index
C. View
D. Query
Answer: A
Which database supports stored procedures?
A. MySQL
B. Oracle
C. SQL Server
D. All of the above
Answer: D
Which ensures minimal redundancy?
A. Indexing
B. Normalization
C. Encryption
D. Replication
Answer: B
Which process checks data accuracy?
A. Validation
B. Indexing
C. Backup
D. Recovery
Answer: A
Which DBA task involves planning storage?
A. Capacity planning
B. Debugging
C. Coding
D. Designing UI
Answer: A
Which improves fault tolerance?
A. Replication
B. Normalization
C. Constraints
D. Indexing
Answer: A
Which ensures consistency after failure?
A. Backup
B. Durability
C. Atomicity
D. Isolation
Answer: B
Which process restores database after crash?
A. Recovery
B. Replication
C. Normalization
D. Indexing
Answer: A
Which command saves a transaction permanently?
A. ROLLBACK
B. COMMIT
C. SAVEPOINT
D. END
Answer: B
Which command undoes changes?
A. COMMIT
B. SAVE
C. ROLLBACK
D. END
Answer: C
Which allows partial rollback?
A. COMMIT
B. SAVEPOINT
C. END
D. STOP
Answer: B
Which is NOT a DBA tool?
A. pgAdmin
B. SQL*Plus
C. Photoshop
D. SSMS
Answer: C
Which handles concurrent access?
A. Locking
B. Backup
C. Replication
D. Indexing
Answer: A
Which lock allows read-only access?
A. Exclusive
B. Shared
C. Deadlock
D. Full
Answer: B
Which ensures data integrity across tables?
A. Primary key
B. Foreign key
C. Index
D. View
Answer: B
Which improves read-heavy workloads?
A. Indexing
B. Encryption
C. Backup
D. Logging
Answer: A
Which DBA task ensures compliance?
A. Auditing
B. Coding
C. Designing
D. Formatting
Answer: A
A DBA must prioritize:
A. Speed only
B. Cost only
C. Security, performance, and availability
D. UI design
Answer: C

PC TECHNICIAN QUESTIONS AND ANSWERS

By njohole11:04 PM No comments:

A. Computer Hardware (1–25)

Which component is considered the “brain” of the computer?
A. GPU
B. CPU
C. RAM
D. SSD
Answer: B
Which device is used to store the computer’s BIOS settings?
A. ROM
B. CMOS battery
C. HDD
D. PSU
Answer: B
Which connector is used for modern internal hard drives?
A. IDE
B. SATA
C. SCSI
D. Parallel
Answer: B
RAM stands for:
A. Random Access Memory
B. Ready Active Memory
C. Rapid Access Module
D. Read Active Memory
Answer: A
Which slot is commonly used for graphics cards?
A. PCI
B. AGP
C. PCIe x16
D. ISA
Answer: C
A CPU cooler is used to:
A. Increase performance
B. Prevent overheating
C. Clean dust
D. Increase power
Answer: B
Which form factor is commonly used in desktop motherboards?
A. ATX
B. BTX
C. NTX
D. ITX
Answer: A
Which voltage is used by SATA power connectors?
A. 12V, 5V, 3.3V
B. 9V only
C. 24V only
D. 6V only
Answer: A
Which component stores temporary data while the computer is running?
A. HDD
B. RAM
C. SSD
D. NIC
Answer: B
SSD stands for:
A. Solid State Drive
B. Super Storage Disk
C. Static Storage Device
D. Secure System Drive
Answer: A
A Dual-Core processor has:
A. 1 core
B. 2 cores
C. 4 cores
D. 8 cores
Answer: B
Thermal paste is applied between:
A. RAM and motherboard
B. CPU and heatsink
C. GPU and PSU
D. HDD and case
Answer: B
Which device converts AC to DC power?
A. GPU
B. HDD
C. PSU
D. CPU
Answer: C
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
Which port is used for older keyboards?
A. USB
B. PS/2
C. HDMI
D. VGA
Answer: B
Which connector is used for monitors?
A. HDMI
B. VGA
C. DisplayPort
D. All of the above
Answer: D
ECC RAM is used for:
A. Gaming PCs
B. Error correction
C. Increasing heat
D. Wireless connectivity
Answer: B
CPU speed is measured in:
A. GHz
B. MB
C. Volts
D. RPM
Answer: A
Which storage has no moving parts?
A. HDD
B. SSD
C. Floppy disk
D. CD-ROM
Answer: B
A GPU is responsible for:
A. Power supply
B. Network control
C. Graphics rendering
D. Memory management
Answer: C
Which cable is used for Ethernet?
A. CAT6
B. HDMI
C. RCA
D. USB
Answer: A
A motherboard chipset controls:
A. Power input
B. Data flow between CPU and components
C. Sound quality
D. Cooling system
Answer: B
Which type of memory is permanent?
A. RAM
B. Cache
C. ROM
D. Registers
Answer: C
HDDs are measured in:
A. GHz
B. RPM
C. MBps
D. kWh
Answer: B
Which pin count is used for DDR4 desktop RAM?
A. 184
B. 200
C. 240
D. 288
Answer: D

B. Operating Systems (26–45)

Windows system files usually have extension:
A. .exe
B. .dll
C. .sys
D. All of the above
Answer: D
Which key opens Task Manager quickly?
A. Ctrl + S
B. Ctrl + Alt + Del
C. Alt + F4
D. Win + R
Answer: B
Which OS is open-source?
A. Windows
B. macOS
C. Linux
D. ChromeOS
Answer: C
What does GUI stand for?
A. General User Interaction
B. Graphical User Interface
C. Global User Internet
D. Graphic Utility Item
Answer: B
Device drivers are used to:
A. Connect hardware to the OS
B. Update internet
C. Remove viruses
D. Boot faster
Answer: A
Windows command to check disk errors is:
A. ipconfig
B. chkdsk
C. ping
D. format
Answer: B
Which Windows feature restores system settings?
A. Disk Cleanup
B. Update Manager
C. System Restore
D. Task Scheduler
Answer: C
Which file system is best for Windows?
A. NTFS
B. FAT16
C. EXT3
D. HFS+
Answer: A
Safe Mode loads Windows with:
A. All drivers
B. Minimum drivers
C. High performance settings
D. Overclock settings
Answer: B
The Windows Run command shortcut:
A. Win + R
B. Win + X
C. Win + S
D. Win + Tab
Answer: A
Which tool shows system performance?
A. Control Panel
B. Device Manager
C. Task Manager
D. Notepad
Answer: C
Linux command to list files:
A. copy
B. ls
C. cmd
D. mkdir
Answer: B
The Windows registry stores:
A. Hardware settings
B. Software settings
C. User preferences
D. All of the above
Answer: D
Which OS update type includes security fixes only?
A. Feature update
B. Major update
C. Patch
D. Driver update
Answer: C
Which folder stores 64-bit applications in Windows?
A. Program Files
B. Program Files (x86)
C. System32
D. SysWOW64
Answer: A
The BIOS loads the OS by reading the:
A. RAM
B. Bootloader
C. GPU
D. USB port
Answer: B
Linux command to restart:
A. restart now
B. reboot
C. shutdown /f
D. refresh
Answer: B
Windows command to view IP address:
A. ipconfig
B. winip
C. wmic
D. ifconfig
Answer: A
Defragmentation is useful for:
A. SSD
B. HDD
C. CMOS
D. RAM
Answer: B
Which tool removes malware in Windows?
A. Task Manager
B. Windows Defender
C. Disk Backup
D. BIOS
Answer: B

C. Networking (46–70)

LAN stands for:
A. Local Access Network
B. Local Area Network
C. Large Area Network
D. Link Access Node
Answer: B
Common Wi-Fi standard:
A. 802.11
B. 909.11
C. 505.20
D. 702.10
Answer: A
What device connects multiple networks?
A. Router
B. Switch
C. Hub
D. Repeater
Answer: A
A switch works at which OSI layer?
A. Layer 1
B. Layer 2
C. Layer 5
D. Layer 7
Answer: B
IPv4 length:
A. 16 bit
B. 32 bit
C. 64 bit
D. 128 bit
Answer: B
Which command tests network connectivity?
A. ipconfig
B. ping
C. netstat
D. tracert
Answer: B
Which device broadcasts all traffic?
A. Router
B. Switch
C. Hub
D. Firewall
Answer: C
Default gateway refers to:
A. Local router address
B. MAC address
C. DNS server
D. IP lease
Answer: A
DNS translates:
A. IP → URL
B. URL → IP
C. MAC → IP
D. IP → Hostname only
Answer: B
Wi-Fi security type:
A. WPA2
B. HTTP
C. FTP
D. QRX
Answer: A
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
MAC address is:
A. 32 bits
B. 48 bits
C. 64 bits
D. 128 bits
Answer: B
Which command shows active connections?
A. ping
B. netstat
C. gpupdate
D. cls
Answer: B
CAT6 max speed:
A. 100 Mbps
B. 1 Gbps
C. 10 Gbps
D. 40 Gbps
Answer: C
Port for HTTP:
A. 25
B. 443
C. 80
D. 21
Answer: C
Port for FTP:
A. 21
B. 53
C. 110
D. 443
Answer: A
Port for DNS:
A. 25
B. 53
C. 110
D. 23
Answer: B
A repeater is used to:
A. Filter malware
B. Extend network signal
C. Increase power supply
D. Block websites
Answer: B
Firewall function:
A. Video rendering
B. Blocks unauthorized traffic
C. Cleans RAM
D. Formats HDD
Answer: B
Common wireless frequency:
A. 1 GHz
B. 2.4 GHz
C. 10 GHz
D. 0.5 GHz
Answer: B
Network topology with a central hub:
A. Ring
B. Mesh
C. Star
D. Bus
Answer: C
VPN is used for:
A. Gaming
B. Secure remote access
C. Cooling
D. Disk repair
Answer: B
Loopback IP:
A. 192.168.0.1
B. 10.0.0.1
C. 8.8.8.8
D. 127.0.0.1
Answer: D
Which tool checks path to a destination?
A. ping
B. tracert
C. ls
D. scanpath
Answer: B
RJ45 connector is used for:
A. Power
B. Keyboard
C. Ethernet
D. USB
Answer: C

D. Troubleshooting & Safety (71–100)

Which tool removes dust from a PC?
A. Water
B. Blower/compressed air
C. Oil
D. Paintbrush
Answer: B
ESD stands for:
A. Electronic Storage Data
B. Electrostatic Discharge
C. Emergency System Device
D. Electrical System Detector
Answer: B
Best surface to place a motherboard during repair:
A. Metallic table
B. Carpet
C. Wood or non-conductive surface
D. Wet cloth
Answer: C
PC beeps during startup indicates:
A. Successful boot
B. Hardware error
C. Virus
D. Low battery only
Answer: B
If PC does not power on, first check:
A. GPU
B. RAM
C. Power cable & PSU
D. Mouse
Answer: C
Overheating is commonly caused by:
A. Too much RAM
B. Bad cooling system
C. Fast internet
D. Keyboard error
Answer: B
Blue screen errors in Windows are called:
A. BSA
B. BSOD
C. BSOS
D. WSE
Answer: B
If no display appears, check:
A. Monitor power
B. GPU connection
C. RAM
D. All of the above
Answer: D
Which tool tests RAM?
A. MemTest
B. DiskPart
C. WinZip
D. BIOS updater
Answer: A
If computer auto-restarts, likely cause:
A. Bad PSU
B. Malware
C. Overheating
D. All of the above
Answer: D
CMOS battery type:
A. AAA
B. CR2032
C. 9V
D. AA
Answer: B
If hard drive not detected:
A. Check SATA cable
B. Check BIOS
C. Check power connection
D. All of the above
Answer: D
Which tool tests the PSU?
A. Multimeter
B. Screwdriver
C. Flashlight
D. HDMI cable
Answer: A
Which key enters BIOS?
A. F1
B. F2
C. DEL
D. Any of the above depending on PC
Answer: D
System freezing can be caused by:
A. Bad RAM
B. Overheating
C. Malware
D. All of the above
Answer: D
S.M.A.R.T. is related to:
A. RAM
B. HDD health
C. GPU
D. CPU temperature
Answer: B
If PC makes grinding noise:
A. HDD failure
B. GPU fan
C. Case fan
D. All possible
Answer: D
If keyboard not responding:
A. Check USB port
B. Test with another PC
C. Replace keyboard
D. All of the above
Answer: D
Which antivirus is built into Windows?
A. Kaspersky
B. Avast
C. Windows Defender
D. Bitdefender
Answer: C
To remove stubborn malware use:
A. Windows Firewall
B. System Restore
C. Safe Mode + antivirus
D. Notepad
Answer: C
SMART errors suggest:
A. RAM failure
B. HDD nearing failure
C. Screen issue
D. GPU crash
Answer: B
Unexpected shutdowns may be caused by:
A. Overheating
B. PSU failure
C. Motherboard failure
D. All of the above
Answer: D
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
A burning smell inside PC indicates:
A. Software error
B. Hardware damage
C. Browser problem
D. Driver issue
Answer: B
If system time resets every boot:
A. Virus
B. CMOS battery dead
C. PSU failure
D. GPU problem
Answer: B
STOP error in Windows refers to:
A. Slow PC
B. BSOD
C. Safe Mode
D. Low memory
Answer: B
Tool to check memory usage:
A. WinZip
B. Task Manager
C. Notepad
D. CMD only
Answer: B
Which command repairs Windows boot files?
A. bootrec /fixmbr
B. ipconfig
C. net stop
D. cmdrepair
Answer: A
If PC powers but no display, try:
A. Reseat RAM
B. Reseat GPU
C. Clear CMOS
D. All of the above
Answer: D
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 (🙏)

SQL TRIGGERS

By njohole4:32 AM No comments:

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;

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

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

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

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

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;

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

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

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

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

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;

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

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

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

| age | int(11) | 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;

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

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

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

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

| course_id | int(11) | YES | MUL | 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;

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

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

| 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;

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

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

| 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;

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

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

| 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;

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

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

| 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;

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

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;

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

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

| 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;

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

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

| 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;

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

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

| 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;

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

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

| 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;

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

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

| 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;

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

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

| 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;

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

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

| 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;

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

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

| 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]> 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;

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

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

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

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

1 row in set (0.001 sec)

SQL Server REST API

By njohole5:13 AM No comments:

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	Sign In
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.

Advertise here ✔️

SALEHE NJOHOLE

Saleh Njohole

My Life

Brave

Saturday, December 20, 2025

KEYS IN DBMS

Monday, December 15, 2025

DATABASE ADMINISTRATOR QUESTIONS AND ANSWERS

Friday, December 12, 2025

DBMS - NORMALIZATION

Wednesday, December 3, 2025

PC TECHNICIAN QUESTIONS AND ANSWERS

A. Computer Hardware (1–25)

B. Operating Systems (26–45)

C. Networking (46–70)

D. Troubleshooting & Safety (71–100)

Tuesday, December 2, 2025

SQL TRIGGERS

Friday, November 28, 2025

SQL Server REST API

Contact Us

Blog Archive

Visitors