KEYS IN DBMS

 

 

Read More

DATABASE ADMINISTRATOR QUESTIONS AND ANSWERS

 DATABASE ADMINISTRATOR QUESTIONS AND ANSWERS

Section A: Database Fundamentals (1–15)

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

 

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

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

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

36. Which is a DBA responsibility?
    A. Data entry
    B. Database backup
    C. Writing applications
    D. Network cabling
    Answer: B
37. Backup ensures protection against:
    A. Virus only
    B. Power failure only
    C. Data loss
    D. Slow queries
    Answer: C
38. Which backup copies only changed data?
    A. Full
    B. Differential
    C. Incremental
    D. Mirror
    Answer: C
39. Which backup is fastest to restore?
    A. Incremental
    B. Differential
    C. Full
    D. Partial
    Answer: C
40. Which ensures authorized access?
    A. Indexing
    B. Normalization
    C. Authentication
    D. Replication
    Answer: C
41. Which command gives user permission?
    A. REVOKE
    B. ALLOW
    C. GRANT
    D. ASSIGN
    Answer: C
42. Which command removes permission?
    A. DENY
    B. DELETE
    C. DROP
    D. REVOKE
    Answer: D
43. Encryption protects data:
    A. At rest
    B. In transit
    C. Both
    D. None
    Answer: C
44. Which improves query speed?
    A. Backup
    B. Index
    C. Trigger
    D. View
    Answer: B
45. Which tool monitors database performance?
    A. Profiler
    B. Compiler
    C. Debugger
    D. Editor
    Answer: A
46. Deadlock occurs when:
    A. Server shuts down
    B. Queries run fast
    C. Transactions wait on each other
    D. Backup fails
    Answer: C
47. Which resolves deadlocks?
    A. Commit
    B. Rollback
    C. Index
    D. View
    Answer: B
48. Which isolation level prevents dirty reads?
    A. Read uncommitted
    B. Read committed
    C. Repeatable read
    D. Serializable
    Answer: B
49. Which isolation level is strictest?
    A. Read committed
    B. Repeatable read
    C. Serializable
    D. Snapshot
    Answer: C
50. 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)

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

 

Read More

DBMS - NORMALIZATION

Read More

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