Operating Systems
Process Management
Q: What is a process? A: A program in execution with its own memory space, containing code, data, stack, and heap segments.
Q: Process vs Thread? A: Process = independent execution unit with separate memory. Thread = lightweight process sharing memory within a process. Threads are faster to create and switch.
Q: What are process states? A: New → Ready → Running → Waiting → Terminated. Running can go to Ready (preemption) or Waiting (I/O).
Q: What is a context switch? A: Saving current process state and loading another process’s state. Involves saving registers, program counter, and memory management info.
Q: What is Process Control Block (PCB)? A: Data structure containing process state, PID, program counter, registers, memory limits, open files, and CPU scheduling info.
Q: What is Inter-Process Communication (IPC)? A: Mechanisms for processes to communicate: Shared Memory, Message Passing, Pipes, Sockets, Signals.
Q: Zombie vs Orphan process? A: Zombie = terminated but parent hasn’t read exit status. Orphan = parent terminated before child; adopted by init/systemd.
CPU Scheduling
Q: What is scheduling? A: Selecting which process runs next on CPU to maximize utilization and fairness.
Q: FCFS (First Come First Serve)? A: Non-preemptive. Processes executed in arrival order. Simple but causes convoy effect (short processes wait for long ones).
Q: SJF (Shortest Job First)? A: Executes shortest process first. Optimal average waiting time but can cause starvation and requires knowing execution time.
Q: Round Robin? A: Preemptive. Each process gets fixed time quantum. Fair but high context switch overhead if quantum too small.
Q: Priority Scheduling? A: Processes assigned priorities. Can be preemptive or non-preemptive. Problem: starvation of low-priority processes. Solution: aging.
Q: Multilevel Queue Scheduling? A: Multiple queues with different priorities (foreground/background). Each queue can have different algorithms.
Q: What is starvation? A: Process never gets CPU time due to continuous arrival of higher priority processes. Solved by aging (gradually increasing priority).
Synchronization
Q: What is a race condition? A: Multiple processes access shared data concurrently and outcome depends on execution order, causing inconsistent results.
Q: What is a critical section? A: Code segment where shared resources are accessed. Must ensure mutual exclusion, progress, and bounded waiting.
Q: What is mutual exclusion? A: Only one process can execute in critical section at a time.
Q: What is a semaphore? A: Synchronization tool using integer variable. Binary (0/1) for mutual exclusion, Counting for multiple resources. Operations: wait()/P() and signal()/V().
Q: What is a mutex? A: Binary lock for mutual exclusion. Only the thread that locks it can unlock it. Simpler than semaphore.
Q: Mutex vs Semaphore? A: Mutex = locking mechanism (ownership). Semaphore = signaling mechanism (no ownership). Mutex only binary, semaphore can be counting.
Q: What is a monitor? A: High-level synchronization construct with condition variables. Automatically handles mutual exclusion.
Q: What is a deadlock? A: Set of processes blocked forever, each waiting for resources held by others in circular chain.
Q: Four conditions for deadlock? A: 1) Mutual Exclusion 2) Hold and Wait 3) No Preemption 4) Circular Wait. All four must be present.
Q: Deadlock prevention vs avoidance vs detection? A: Prevention = eliminate one of four conditions. Avoidance = use algorithms like Banker’s algorithm. Detection = allow but detect and recover.
Q: What is Banker’s Algorithm? A: Deadlock avoidance algorithm that checks if resource allocation leads to safe state before granting request.
Q: What is a safe state? A: System can allocate resources to each process in some order and avoid deadlock.
Q: What is priority inversion? A: Low-priority process holds resource needed by high-priority process, while medium-priority processes run. Solved by priority inheritance.
Memory Management
Q: What is logical vs physical address? A: Logical = generated by CPU (virtual). Physical = actual RAM location. MMU translates logical to physical.
Q: What is paging? A: Dividing process memory into fixed-size pages and physical memory into frames of same size. Eliminates external fragmentation.
Q: What is a page table? A: Data structure mapping logical pages to physical frames. Contains page number and frame number.
Q: What is segmentation? A: Dividing memory into variable-sized segments based on logical divisions (code, data, stack). Supports user view better.
Q: What is virtual memory? A: Technique allowing execution of processes not completely in memory using demand paging. Provides illusion of large memory.
Q: What is demand paging? A: Loading pages into memory only when needed (on page fault), not in advance.
Q: What is a page fault? A: Exception when process accesses page not in physical memory. OS must fetch from disk.
Q: What is thrashing? A: Excessive paging activity where system spends more time swapping than executing. Caused by insufficient memory.
Q: What is TLB (Translation Lookaside Buffer)? A: Cache for page table entries. Speeds up address translation by avoiding page table access.
Q: Page replacement algorithms? A: FIFO (replace oldest), LRU (replace least recently used), Optimal (replace page not used for longest), LFU (least frequently used).
Q: What is Belady’s Anomaly? A: In FIFO, more frames can lead to more page faults. Doesn’t occur in LRU or Optimal.
Q: Internal vs External fragmentation? A: Internal = wasted space within allocated block. External = free memory scattered in small blocks. Paging has internal, segmentation has external.
Q: What is compaction? A: Shuffling memory contents to place all free memory together. Solves external fragmentation but expensive.
Q: What is swapping? A: Moving entire process between main memory and disk to free memory for other processes.
File Systems
Q: What is a file? A: Named collection of related information stored on secondary storage with attributes like name, type, size, permissions.
Q: What is an inode? A: Data structure storing file metadata (permissions, timestamps, size, pointers to data blocks) except filename.
Q: What are file allocation methods? A: Contiguous (sequential blocks, fast but fragmentation), Linked (each block points to next, slow random access), Indexed (index block with pointers).
Q: What is a directory? A: Special file containing mappings from filenames to file metadata or inode numbers.
Q: Hard link vs Soft link? A: Hard link = multiple names for same inode (same file). Soft link = pointer to filename (separate file, can cross filesystems).
Q: What is a mount point? A: Directory where another filesystem is attached to the current filesystem tree.
Q: What is disk scheduling? A: Ordering disk I/O requests to minimize seek time. Algorithms: FCFS, SSTF, SCAN, C-SCAN, LOOK.
Q: What is RAID? A: Redundant Array of Independent Disks. Combines multiple disks for performance (striping) or reliability (mirroring/parity).
Deadlocks & Concurrency
Q: What is a spinlock? A: Lock that causes thread to busy-wait (loop) until lock is available. Efficient for short wait times.
Q: What is reader-writer problem? A: Multiple readers can read simultaneously, but writers need exclusive access. Solutions prioritize readers or writers.
Q: What is producer-consumer problem? A: Producer creates items, consumer uses them with bounded buffer. Solved using semaphores or monitors.
Q: What is dining philosophers problem? A: Five philosophers need two forks to eat. Demonstrates deadlock and starvation. Solutions: asymmetry, resource ordering, or limit concurrent philosophers.
System Calls & I/O
Q: What is a system call? A: Interface for user programs to request OS services. Involves mode switch from user to kernel mode.
Q: User mode vs Kernel mode? A: User mode = restricted access, cannot execute privileged instructions. Kernel mode = full access to hardware and memory.
Q: Types of system calls? A: Process control (fork, exec, exit), File management (open, read, write), Device management, Information maintenance, Communication.
Q: What is DMA (Direct Memory Access)? A: Hardware controller transfers data between I/O device and memory without CPU intervention, freeing CPU for other tasks.
Q: What is interrupt? A: Signal to CPU indicating event needs immediate attention. CPU suspends current execution, saves context, handles interrupt.
Q: Interrupt vs Polling? A: Interrupt = device notifies CPU (efficient). Polling = CPU repeatedly checks device status (wastes CPU cycles).
Q: What is buffering? A: Temporary storage area for data during transfer between devices with different speeds. Single, double, or circular buffer.
Q: What is spooling? A: Simultaneous Peripheral Operations OnLine. Queues jobs to disk for devices like printers that can’t handle concurrent access.
Advanced Concepts
Q: What is microkernel vs monolithic kernel? A: Microkernel = minimal kernel (IPC, scheduling), services in user space (modular, stable). Monolithic = all services in kernel (faster but less stable).
Q: What is a kernel panic? A: Unrecoverable error detected by kernel, causing system halt. Linux equivalent of Windows BSOD.
Q: What is copy-on-write (COW)? A: Optimization where forked processes share memory pages until one writes, then page is copied. Saves memory and time.
Q: What is memory-mapped I/O? A: File or device mapped into process address space. Access file like memory array without read/write system calls.
Q: What is a real-time operating system (RTOS)? A: OS guaranteeing response within strict time constraints. Types: hard real-time (must meet deadline) and soft real-time (best effort).
Q: What is locality of reference? A: Programs tend to access nearby memory locations (spatial) or recently accessed locations (temporal). Key to caching effectiveness.
Q: What is working set? A: Set of pages process actively uses. Keeping working set in memory prevents thrashing.
Q: What is the fork() system call? A: Creates child process that’s duplicate of parent. Returns 0 to child, child PID to parent.
Q: What is exec() system call? A: Replaces current process image with new program. Used after fork() to run different program.
Q: What is fragmentation in memory? A: Wasted memory that can’t be allocated. Internal = within allocation, External = between allocations.
Q: What are different types of kernels? A: Monolithic (Linux), Microkernel (Minix), Hybrid (Windows, macOS), Exokernel (minimal abstraction).
Q: What is the difference between preemptive and non-preemptive scheduling? A: Preemptive = OS can forcibly remove running process. Non-preemptive = process runs until completion or voluntary yield.
Q: What is a system daemon? A: Background process providing services (e.g., sshd, httpd). Runs continuously and typically starts at boot.
This covers the most important OS concepts asked in technical interviews. Focus on understanding the “why” behind each concept for deeper discussions.
Computer Networks
OSI Model & TCP/IP
Q: What is the OSI model? A: 7-layer reference model for network communication: Physical, Data Link, Network, Transport, Session, Presentation, Application (mnemonic: Please Do Not Throw Sausage Pizza Away).
Q: What is TCP/IP model? A: 4-layer model: Network Access, Internet, Transport, Application. Practical implementation vs OSI’s theoretical framework.
Q: OSI vs TCP/IP model? A: OSI has 7 layers (theoretical), TCP/IP has 4 layers (practical). TCP/IP combines OSI’s top 3 layers into Application layer.
Q: Functions of each OSI layer? A: Physical (bits, cables), Data Link (frames, MAC, switches), Network (packets, routing, IP), Transport (segments, reliability, TCP/UDP), Session (connections), Presentation (encryption, compression), Application (user services, HTTP/FTP).
Q: What happens at each layer during data transmission? A: Encapsulation going down (add headers), Decapsulation going up (remove headers). Each layer adds its header to data from above.
Network Protocols
Q: TCP vs UDP? A: TCP = connection-oriented, reliable, ordered, slower (HTTP, FTP, SSH). UDP = connectionless, unreliable, fast, no ordering (DNS, streaming, gaming).
Q: What is a three-way handshake? A: TCP connection establishment: 1) SYN from client, 2) SYN-ACK from server, 3) ACK from client. Ensures both sides ready.
Q: What is a four-way handshake (connection termination)? A: 1) FIN from initiator, 2) ACK from receiver, 3) FIN from receiver, 4) ACK from initiator. Graceful connection close.
Q: What is HTTP? A: HyperText Transfer Protocol. Application layer protocol for web communication. Stateless, uses port 80 (443 for HTTPS).
Q: HTTP vs HTTPS? A: HTTPS = HTTP + SSL/TLS encryption. Secure, authenticated, uses port 443. Protects data in transit.
Q: GET vs POST? A: GET = retrieve data, parameters in URL, cacheable, idempotent. POST = submit data, parameters in body, not cacheable, not idempotent.
Q: What is DNS? A: Domain Name System. Translates domain names to IP addresses. Uses port 53, UDP for queries, TCP for zone transfers.
Q: DNS resolution process? A: 1) Browser cache, 2) OS cache, 3) Router cache, 4) ISP DNS, 5) Root server, 6) TLD server, 7) Authoritative server.
Q: What is DHCP? A: Dynamic Host Configuration Protocol. Automatically assigns IP addresses and network configuration to devices. Uses ports 67 (server) and 68 (client).
Q: What is ARP? A: Address Resolution Protocol. Maps IP addresses to MAC addresses in local network. Broadcasts “Who has this IP?” to get MAC.
Q: What is ICMP? A: Internet Control Message Protocol. Used for error messages and diagnostics (ping, traceroute). Part of IP layer.
Q: What is FTP? A: File Transfer Protocol. Transfers files between client and server. Uses ports 20 (data) and 21 (control).
Q: What is SMTP, POP3, IMAP? A: SMTP = sending emails (port 25). POP3 = downloading emails, deletes from server (port 110). IMAP = syncs emails across devices (port 143).
IP Addressing & Subnetting
Q: What is an IP address? A: Unique identifier for devices on network. IPv4 = 32 bits (4 octets), IPv6 = 128 bits (8 groups of 4 hex digits).
Q: IPv4 vs IPv6? A: IPv4 = 32-bit, 4.3 billion addresses, dot-decimal notation. IPv6 = 128-bit, 340 undecillion addresses, hexadecimal, built-in security.
Q: Classes of IPv4 addresses? A: Class A (1-126, /8), Class B (128-191, /16), Class C (192-223, /24), Class D (multicast), Class E (experimental).
Q: What is a subnet mask? A: Divides IP address into network and host portions. Determines which part identifies network and which identifies host.
Q: What is CIDR notation? A: Classless Inter-Domain Routing. Uses /prefix (e.g., 192.168.1.0/24) to specify network size instead of classes.
Q: Public vs Private IP? A: Public = routable on internet, unique globally. Private = non-routable (10.0.0.0/8, 172.16.0.0/12, 192.168.0.0/16), used internally with NAT.
Q: What is NAT? A: Network Address Translation. Translates private IPs to public IP for internet access. Conserves public IPs, adds security.
Q: Static vs Dynamic IP? A: Static = manually configured, permanent, used for servers. Dynamic = assigned by DHCP, changes periodically, used for clients.
Q: What is loopback address? A: 127.0.0.1 (localhost). Used to test network software without physical network. Traffic doesn’t leave the machine.
Q: What is a broadcast address? A: Address to send data to all devices in network. Last address in subnet (e.g., 192.168.1.255 for /24 network).
Routing & Switching
Q: Router vs Switch? A: Router = Layer 3, routes packets between networks using IP. Switch = Layer 2, forwards frames within network using MAC addresses.
Q: Hub vs Switch? A: Hub = broadcasts to all ports, half-duplex, collisions. Switch = forwards to specific port, full-duplex, no collisions. Switches are intelligent.
Q: What is a routing table? A: Table containing network destinations and next hop information. Router uses it to determine where to forward packets.
Q: Static vs Dynamic routing? A: Static = manually configured routes, simple but inflexible. Dynamic = automatic using routing protocols (RIP, OSPF, BGP), adapts to changes.
Q: What is RIP? A: Routing Information Protocol. Distance-vector protocol using hop count (max 15). Simple but slow convergence.
Q: What is OSPF? A: Open Shortest Path First. Link-state protocol using Dijkstra’s algorithm. Fast convergence, scalable, uses cost metric.
Q: What is BGP? A: Border Gateway Protocol. Path-vector protocol for internet routing between autonomous systems. Highly scalable, policy-based.
Q: What is a default gateway? A: Router IP address used when destination is outside local network. Device sends packets to gateway for routing.
Q: What is VLAN? A: Virtual LAN. Logically segments network at Layer 2 for security and performance. Devices in same VLAN can communicate directly.
Q: What is a MAC address? A: Media Access Control address. 48-bit physical address burned into network interface. Format: 6 pairs of hex digits (AA:BB:CC:DD:EE:FF).
Network Devices & Topologies
Q: What is a firewall? A: Security device filtering traffic based on rules. Can be hardware or software. Blocks unauthorized access, allows legitimate traffic.
Q: What is a proxy server? A: Intermediary server between client and internet. Provides caching, filtering, anonymity, and security.
Q: What is a load balancer? A: Distributes network traffic across multiple servers. Improves availability, reliability, and performance. Types: round-robin, least connections, IP hash.
Q: Network topologies? A: Bus (single cable), Star (central hub), Ring (circular), Mesh (fully connected), Tree (hierarchical), Hybrid (combination).
Q: What is a DMZ? A: Demilitarized Zone. Isolated network segment for public-facing services. Separates internal network from internet for security.
Q: What is a gateway? A: Device connecting networks with different protocols. Operates at all OSI layers. Translates between incompatible networks.
Network Security
Q: What is SSL/TLS? A: Secure Sockets Layer/Transport Layer Security. Cryptographic protocols for secure communication. TLS is successor to SSL.
Q: What is a VPN? A: Virtual Private Network. Creates encrypted tunnel over public network. Provides privacy, security, and remote access.
Q: What is IPSec? A: IP Security. Protocol suite for secure IP communications through authentication and encryption. Used in VPNs.
Q: What is a DDoS attack? A: Distributed Denial of Service. Overwhelms target with traffic from multiple sources. Makes service unavailable to legitimate users.
Q: What is port scanning? A: Probing ports to discover open services. Used by attackers for reconnaissance and by admins for security auditing.
Q: What is a man-in-the-middle attack? A: Attacker intercepts communication between two parties. Can eavesdrop or modify data. Prevented by encryption and authentication.
Q: What is WPA2/WPA3? A: Wi-Fi Protected Access. Security protocols for wireless networks. WPA3 is latest with improved encryption and protection.
Application Layer Protocols
Q: What is REST? A: REpresentational State Transfer. Architectural style for APIs using HTTP methods (GET, POST, PUT, DELETE). Stateless, cacheable.
Q: What is WebSocket? A: Full-duplex communication protocol over TCP. Enables real-time bidirectional data flow between client and server.
Q: What is SSH? A: Secure Shell. Protocol for secure remote login and command execution. Uses encryption, port 22.
Q: What is Telnet? A: Remote terminal protocol. Unencrypted, insecure, port 23. Replaced by SSH in practice.
Q: What is SNMP? A: Simple Network Management Protocol. Monitors and manages network devices. Uses ports 161 (agent) and 162 (trap).
Q: What is NTP? A: Network Time Protocol. Synchronizes clocks across network. Uses UDP port 123. Critical for distributed systems.
Performance & Troubleshooting
Q: What is bandwidth? A: Maximum data transfer rate of network path. Measured in bits per second (bps, Mbps, Gbps).
Q: What is latency? A: Time delay for data to travel from source to destination. Includes propagation, transmission, processing, and queuing delays.
Q: Bandwidth vs Throughput? A: Bandwidth = maximum capacity. Throughput = actual data transferred. Throughput ≤ Bandwidth due to overhead and congestion.
Q: What is jitter? A: Variation in packet arrival times. Causes issues in real-time applications (VoIP, video). Solved by buffering.
Q: What is packet loss? A: Packets dropped during transmission due to congestion, errors, or network failures. Affects quality and requires retransmission.
Q: What is MTU? A: Maximum Transmission Unit. Largest packet size that can be transmitted without fragmentation. Typically 1500 bytes for Ethernet.
Q: What is ping? A: Network diagnostic tool using ICMP echo requests. Tests connectivity and measures round-trip time.
Q: What is traceroute? A: Shows path packets take to destination. Lists all routers (hops) along the way. Helps identify routing issues.
Q: What is netstat? A: Network statistics tool. Displays active connections, listening ports, routing tables, and network interface statistics.
Q: What is a bottleneck? A: Point in network where capacity is exceeded, causing congestion. Limits overall performance.
TCP/IP Details
Q: TCP header fields? A: Source/destination port, sequence number, acknowledgment number, flags (SYN, ACK, FIN, RST), window size, checksum.
Q: What is sequence number in TCP? A: Identifies position of data in stream. Ensures correct ordering of segments and detects missing data.
Q: What is a TCP window size? A: Amount of data sender can transmit before receiving acknowledgment. Controls flow control and prevents overflow.
Q: What is TCP congestion control? A: Mechanisms to prevent network congestion: slow start, congestion avoidance, fast retransmit, fast recovery.
Q: What is TIME_WAIT state? A: TCP state after connection close. Waits 2xMSL to ensure all packets cleared. Prevents old packets interfering with new connections.
Q: What is UDP header? A: Simple: source port, destination port, length, checksum. Only 8 bytes vs TCP’s 20+ bytes.
Q: What is a socket? A: Endpoint for network communication. Combination of IP address and port number (e.g., 192.168.1.1:8080).
Q: What are well-known ports? A: 0-1023 reserved for standard services: 20/21 (FTP), 22 (SSH), 23 (Telnet), 25 (SMTP), 53 (DNS), 80 (HTTP), 443 (HTTPS).
Wireless Networks
Q: What is Wi-Fi? A: Wireless networking technology using radio waves. Based on IEEE 802.11 standards. Common standards: 802.11n, 802.11ac, 802.11ax (Wi-Fi 6).
Q: What is SSID? A: Service Set Identifier. Network name for Wi-Fi. Identifies wireless network to users.
Q: What is a wireless access point? A: Device that creates wireless network by connecting to wired network. Allows wireless devices to connect.
Q: 2.4GHz vs 5GHz Wi-Fi? A: 2.4GHz = longer range, more interference, slower. 5GHz = shorter range, less interference, faster. 5GHz has more channels.
Q: What is WEP, WPA, WPA2, WPA3? A: Wi-Fi security protocols. WEP (broken), WPA (deprecated), WPA2 (current standard), WPA3 (latest, most secure).
CDN & Modern Networks
Q: What is a CDN? A: Content Delivery Network. Distributed servers delivering content from location nearest to user. Reduces latency, improves performance.
Q: What is DNS caching? A: Temporary storage of DNS query results. Reduces lookup time and DNS server load. Has TTL (Time To Live).
Q: What is QoS? A: Quality of Service. Prioritizes certain traffic (VoIP, video) over others. Ensures critical applications get bandwidth.
Q: What is multicast? A: One-to-many transmission. Single packet sent to multiple recipients simultaneously. Used for streaming, video conferencing.
Q: Unicast vs Broadcast vs Multicast? A: Unicast = one-to-one. Broadcast = one-to-all. Multicast = one-to-many (specific group). Anycast = one-to-nearest.
Advanced Concepts
Q: What is SDN? A: Software-Defined Networking. Separates control plane from data plane. Centralized network management and programmability.
Q: What is a subnet? A: Logical subdivision of IP network. Improves performance and security by segmenting network into smaller parts.
Q: What is supernetting? A: Combining multiple networks into larger one. Opposite of subnetting. Reduces routing table size (route aggregation).
Q: What is TTL in networking? A: Time To Live. Hop count limit preventing packets from circulating forever. Decremented at each router; dropped at zero.
Q: What is encapsulation? A: Wrapping data with protocol information at each layer. Each layer adds header (and sometimes trailer) to data from above.
Q: What is tunneling? A: Encapsulating one protocol within another. Used in VPNs, IPv6 over IPv4, etc. Creates virtual point-to-point connection.
Q: What is half-duplex vs full-duplex? A: Half-duplex = one direction at a time (walkie-talkie). Full-duplex = both directions simultaneously (phone call). Switches use full-duplex.
Q: What is network segmentation? A: Dividing network into separate subnetworks for security, performance, and management. Limits broadcast domains and isolates issues.
Q: What is a collision domain? A: Network segment where collisions can occur. Hub has one collision domain. Switch has separate collision domain per port.
Q: What is a broadcast domain? A: Network segment where broadcast is received by all devices. Router separates broadcast domains. Switch and hub share broadcast domain.
Q: What is the difference between connection-oriented and connectionless? A: Connection-oriented = establishes connection before data transfer (TCP). Connectionless = sends data without connection (UDP).
Q: What is port forwarding? A: Redirects traffic from one port to another. Allows external access to internal services. Configured on routers/firewalls.
Q: What is network latency components? A: Propagation delay (distance/speed), Transmission delay (packet size/bandwidth), Processing delay (router processing), Queuing delay (buffer wait time).
Q: What is the sliding window protocol? A: Flow control mechanism allowing multiple packets in transit before ACK. Improves efficiency. Window size determines number of unacknowledged packets.
This comprehensive list covers core networking concepts essential for technical interviews. Understanding the “why” and “when” behind each concept is crucial for deeper discussions.
Computer Organization & Architecture
Basic Concepts
Q: Computer Organization vs Computer Architecture? A: Architecture = instruction set, addressing modes, data types (programmer’s view). Organization = hardware implementation, control signals, memory technology (how architecture is realized).
Q: Von Neumann vs Harvard Architecture? A: Von Neumann = single memory for instructions and data, one bus (simpler, cheaper). Harvard = separate memory and buses for instructions and data (faster, no bottleneck).
Q: What is stored program concept? A: Instructions and data stored in same memory. Programs can be modified like data. Foundation of modern computers (Von Neumann).
Q: What is instruction set architecture (ISA)? A: Interface between hardware and software. Defines instructions, registers, addressing modes, data types, and memory architecture.
Q: RISC vs CISC? A: RISC = simple instructions, fixed length, load-store, more registers (ARM, MIPS). CISC = complex instructions, variable length, memory operations (x86). RISC is faster per instruction.
CPU Components
Q: What is the CPU? A: Central Processing Unit. Contains ALU (arithmetic/logic), Control Unit (instruction execution), and Registers (fast storage).
Q: What is ALU? A: Arithmetic Logic Unit. Performs arithmetic (add, subtract) and logical operations (AND, OR, NOT, XOR).
Q: What is Control Unit? A: Directs operation of processor. Fetches instructions, decodes them, controls data flow between CPU, memory, and I/O.
Q: Types of registers? A: Program Counter (PC), Instruction Register (IR), Accumulator (ACC), Memory Address Register (MAR), Memory Data Register (MDR), General Purpose Registers, Status/Flag Register.
Q: What is Program Counter (PC)? A: Holds address of next instruction to execute. Automatically incremented after fetch. Modified by jumps/branches.
Q: What is Instruction Register (IR)? A: Holds current instruction being executed. Loaded during fetch phase.
Q: What is accumulator? A: Register storing intermediate arithmetic and logic results. Primary register for ALU operations.
Q: What is a flag register? A: Contains status bits: Zero, Carry, Sign, Overflow, Parity. Set by ALU operations, used for conditional branching.
Instruction Cycle
Q: What is the instruction cycle? A: Fetch → Decode → Execute → Store. Repeated cycle for each instruction.
Q: Explain fetch-decode-execute cycle? A: Fetch: load instruction from memory (PC→MAR, Memory→MDR→IR, PC++). Decode: interpret instruction. Execute: perform operation. Store: write result.
Q: What is instruction format? A: Structure of instruction: Opcode (operation) + Operands (data/addresses). Types: 0-address, 1-address, 2-address, 3-address.
Q: What are addressing modes? A: Immediate (value in instruction), Direct (address in instruction), Indirect (address points to address), Register (operand in register), Indexed (base + offset), Relative (PC + offset).
Q: What is pipelining? A: Overlapping execution of multiple instructions. Stages work on different instructions simultaneously. Increases throughput, not latency.
Q: Pipeline stages? A: IF (Instruction Fetch), ID (Instruction Decode), EX (Execute), MEM (Memory Access), WB (Write Back).
Q: What are pipeline hazards? A: Structural (resource conflict), Data (dependency between instructions), Control (branches). Reduce pipeline efficiency.
Q: How to resolve data hazards? A: Forwarding/Bypassing (pass result directly), Stalling (insert NOPs), Compiler reordering (rearrange instructions).
Q: What is branch prediction? A: Guessing branch outcome to reduce control hazards. Static (always taken/not taken) or Dynamic (history-based). Misprediction flushes pipeline.
Memory Hierarchy
Q: What is memory hierarchy? A: Registers → Cache (L1, L2, L3) → Main Memory (RAM) → Secondary Storage (SSD/HDD). Smaller, faster, costlier at top.
Q: Why memory hierarchy? A: Balance speed, cost, and capacity. Exploit locality of reference (temporal and spatial) for performance.
Q: What is cache memory? A: Small, fast memory between CPU and RAM. Stores frequently accessed data. Reduces average access time.
Q: Cache levels? A: L1 = smallest, fastest, per core (split I-cache/D-cache). L2 = larger, per core. L3 = largest, shared across cores.
Q: What is cache hit and miss? A: Hit = data found in cache (fast). Miss = data not in cache, fetch from RAM (slow). Hit ratio = hits/(hits + misses).
Q: Cache mapping techniques? A: Direct Mapped (one possible location), Fully Associative (any location), Set Associative (limited locations in set). Trade-off: complexity vs hit rate.
Q: Explain direct mapping? A: Cache line = Memory block mod Number of cache lines. Simple, fast, high conflict misses. Each block maps to exactly one line.
Q: Explain associative mapping? A: Block can go anywhere in cache. Needs tag comparison for all lines. Flexible but slow and expensive.
Q: Explain set-associative mapping? A: N-way set associative. Cache divided into sets, block maps to set, can go in any of N ways within set. Compromise between direct and fully associative.
Q: Cache replacement policies? A: LRU (Least Recently Used), FIFO (First In First Out), LFU (Least Frequently Used), Random. LRU most common, best performance.
Q: Write policies in cache? A: Write-through (write to cache and memory simultaneously, consistent but slow). Write-back (write to cache only, write to memory on eviction, fast but complex).
Q: What is locality of reference? A: Temporal = recently accessed data likely accessed again. Spatial = nearby data likely accessed. Cache exploits both.
Q: What is cache coherence? A: Maintaining consistency across multiple caches in multiprocessor systems. Protocols: MESI, MOESI.
Main Memory
Q: RAM vs ROM? A: RAM = volatile, read/write, faster, main memory. ROM = non-volatile, read-only (or slow write), stores BIOS/firmware.
Q: SRAM vs DRAM? A: SRAM = static, faster, expensive, no refresh, used for cache (flip-flops). DRAM = dynamic, slower, cheap, needs refresh, used for RAM (capacitors).
Q: What is memory interleaving? A: Dividing memory into banks accessed simultaneously. Increases bandwidth. Types: low-order (sequential addresses in different banks) vs high-order interleaving.
Q: What is virtual memory? A: Gives illusion of large memory using disk. Allows programs larger than physical RAM. Uses paging or segmentation.
Q: What is a page table? A: Maps virtual pages to physical frames. Contains page number, frame number, valid bit, protection bits.
Q: What is TLB? A: Translation Lookaside Buffer. Cache for page table entries. Speeds up virtual-to-physical address translation.
Q: What is page fault? A: Accessing page not in physical memory. OS loads page from disk (slow). Context switch occurs.
Q: What is thrashing? A: Excessive paging where system spends more time swapping than executing. Caused by insufficient physical memory.
I/O Organization
Q: What is I/O interface? A: Hardware between CPU/memory and external devices. Handles speed matching, data buffering, and protocol conversion.
Q: I/O communication methods? A: Programmed I/O (polling), Interrupt-driven I/O, DMA (Direct Memory Access).
Q: What is programmed I/O? A: CPU polls device status repeatedly. Simple but wastes CPU cycles. CPU transfers data.
Q: What is interrupt-driven I/O? A: Device interrupts CPU when ready. CPU handles interrupt, transfers data. More efficient than polling.
Q: What is DMA? A: Direct Memory Access. Device controller transfers data directly to/from memory without CPU. CPU only initiates and completes transfer.
Q: What is an interrupt? A: Signal to CPU indicating event needs attention. CPU suspends current task, saves context, executes ISR (Interrupt Service Routine), restores context.
Q: What is interrupt vector? A: Table containing addresses of ISRs. Each interrupt type has entry. CPU uses interrupt number as index.
Q: Maskable vs Non-maskable interrupt? A: Maskable = can be disabled by CPU (normal I/O). Non-maskable = cannot be disabled (critical errors, power failure).
Q: What is an I/O channel? A: Specialized processor for I/O operations. Executes channel programs independently. Offloads I/O work from CPU.
Performance Metrics
Q: What is CPU time? A: Time CPU spends executing program. CPU Time = Instruction Count × CPI × Clock Cycle Time.
Q: What is CPI? A: Cycles Per Instruction. Average number of clock cycles per instruction. Lower is better.
Q: What is IPC? A: Instructions Per Cycle. Inverse of CPI. Measure of parallelism. Higher is better.
Q: What is MIPS? A: Million Instructions Per Second. MIPS = Clock Rate / (CPI × 10^6). Not reliable for comparing different ISAs.
Q: What is throughput? A: Number of instructions completed per unit time. Improved by pipelining and parallelism.
Q: What is latency? A: Time to complete single instruction/operation. Start to finish time.
Q: What is Amdahl’s Law? A: Speedup = 1 / [(1-P) + P/S]. P = parallelizable fraction, S = speedup of parallel part. Limits of parallel speedup.
Q: What is clock rate? A: Number of clock cycles per second (Hz, MHz, GHz). Higher clock rate ≠ always faster (depends on CPI and architecture).
Parallelism & Multiprocessing
Q: Flynn’s Taxonomy? A: SISD (single instruction, single data - uniprocessor), SIMD (vector processors), MISD (rare), MIMD (multiprocessors).
Q: What is pipelining? A: Instruction-level parallelism. Multiple instructions in different stages simultaneously. Increases throughput.
Q: What is superscalar? A: Multiple instructions issued per cycle. Multiple execution units. Dynamic scheduling. Modern CPUs are superscalar.
Q: What is VLIW? A: Very Long Instruction Word. Compiler schedules multiple operations in single instruction. Static scheduling vs dynamic (superscalar).
Q: What is multithreading? A: Multiple threads share CPU resources. Interleaved (switch on long latency), Simultaneous (SMT/Hyper-Threading - execute multiple threads same cycle).
Q: What is multicore? A: Multiple complete processors on single chip. Each core can execute independently. True parallel execution.
Q: Symmetric vs Asymmetric multiprocessing? A: SMP = all processors equal, share memory and I/O, any processor can run any task. AMP = master-slave, specialized processors.
Q: What is cache coherence? A: Ensuring consistent view of memory across multiple caches. Problem when multiple cores cache same data.
Q: MESI protocol? A: Cache coherence protocol. States: Modified, Exclusive, Shared, Invalid. Maintains consistency through state transitions.
Number Representation
Q: Fixed-point vs Floating-point? A: Fixed-point = fixed decimal position, limited range, simple hardware. Floating-point = scientific notation, large range, complex hardware (IEEE 754).
Q: IEEE 754 format? A: Single precision (32-bit): 1 sign + 8 exponent + 23 mantissa. Double precision (64-bit): 1 sign + 11 exponent + 52 mantissa.
Q: What is normalized form? A: Floating-point with leading 1 before decimal (1.xxxx × 2^exp). Maximizes precision. Hidden bit saves storage.
Q: What is bias in exponent? A: Offset added to actual exponent for storage. Single: bias = 127, Double: bias = 1023. Allows positive storage of negative exponents.
Q: How are negative numbers represented? A: Sign-magnitude, 1’s complement, 2’s complement. 2’s complement most common: flip bits and add 1. No +0/-0 issue.
Q: Why 2’s complement? A: Addition/subtraction use same hardware. Single zero. Easy overflow detection. Range: -2^(n-1) to 2^(n-1)-1.
Instruction Set
Q: Types of instructions? A: Data transfer (MOV, LOAD, STORE), Arithmetic (ADD, SUB, MUL, DIV), Logical (AND, OR, NOT, XOR), Control (JMP, CALL, RET), Shift/Rotate.
Q: Load-store architecture? A: Only LOAD/STORE access memory. All operations on registers. RISC characteristic. Simplifies pipeline and hardware.
Q: What is a stack architecture? A: Operands implicitly on stack. 0-address instructions. Push, Pop operations. Simple but limited parallelism (e.g., Java bytecode).
Q: What is microcode? A: Low-level instructions implementing machine instructions. Complex instructions broken into micro-operations. Used in CISC processors.
Control Unit Design
Q: Hardwired vs Microprogrammed control? A: Hardwired = logic circuits, faster, difficult to modify (RISC). Microprogrammed = ROM-based, flexible, slower (CISC).
Q: What is a micro-operation? A: Elementary operation performed in one clock cycle on registers. Instructions decomposed into sequence of micro-ops.
Q: What is horizontal vs vertical microcode? A: Horizontal = long, multiple operations per instruction, fast, large ROM. Vertical = short, encoded, slower, compact.
Bus Architecture
Q: What is a bus? A: Shared communication pathway between components. Contains data, address, and control lines.
Q: Types of buses? A: Data bus (transfers data, bidirectional), Address bus (specifies location, unidirectional), Control bus (timing and control signals).
Q: What is bus arbitration? A: Resolving conflicts when multiple devices request bus. Methods: Daisy chain, Polling, Independent requests.
Q: Synchronous vs Asynchronous bus? A: Synchronous = clock-driven, fixed timing, faster for short distances. Asynchronous = handshake protocol, flexible timing, works over distance.
Q: What is bus bandwidth? A: Maximum data transfer rate. Bandwidth = Bus Width × Clock Frequency. Measured in MB/s or GB/s.
Advanced Topics
Q: What is out-of-order execution? A: Instructions executed as resources available, not program order. Resolves data hazards. Results committed in order. Increases IPC.
Q: What is speculative execution? A: Execute instructions before knowing if needed (after predicted branch). Discard if wrong. Improves performance but can have security issues (Spectre/Meltdown).
Q: What is register renaming? A: Maps architectural registers to physical registers. Eliminates false dependencies (WAR, WAW). Enables out-of-order execution.
Q: What is Tomasulo’s algorithm? A: Dynamic scheduling algorithm using reservation stations. Handles data hazards without stalling. Enables out-of-order execution.
Q: What is scoreboarding? A: Dynamic scheduling technique tracking instruction status. Detects hazards, allows out-of-order when safe. Simpler than Tomasulo’s.
Q: What is a reservation station? A: Buffer holding instructions waiting for operands. Part of Tomasulo’s algorithm. Decentralized hazard detection.
Q: What is memory consistency? A: Order in which memory operations appear to execute. Models: Sequential consistency, Relaxed consistency. Critical for multiprocessors.
Q: What is a memory barrier/fence? A: Instruction forcing ordering of memory operations. Ensures operations before barrier complete before those after. Used in concurrent programming.
Q: What is prefetching? A: Loading data into cache before actually needed. Exploits spatial locality. Hardware or software prefetching.
Q: What is loop unrolling? A: Compiler optimization replicating loop body. Reduces branch overhead, exposes parallelism, increases code size.
Q: What is instruction fusion? A: Combining multiple instructions into single micro-op. Reduces pressure on execution units. Common in modern processors.
Q: What is Hyper-Threading? A: Intel’s SMT implementation. Two logical processors per physical core. Share execution resources, duplicate architectural state.
Q: Little Endian vs Big Endian? A: Byte ordering in multi-byte data. Little: LSB at lowest address (x86). Big: MSB at lowest address (network protocols). Both valid.
Q: What is a coprocessor? A: Specialized processor for specific tasks (FPU, GPU). Works alongside main CPU. Offloads specialized computations.
Q: What is memory-mapped I/O? A: I/O devices assigned memory addresses. Access like memory (LOAD/STORE). Unified addressing. Alternative: Port-mapped I/O (separate IN/OUT instructions).
This comprehensive list covers fundamental and advanced computer architecture concepts crucial for technical interviews. Understanding implementation trade-offs is key to deeper discussions.
Firmware & BIOS (AMI Focus) AMI Checkpoints
BIOS Fundamentals
Q: What is BIOS? A: Basic Input/Output System. Firmware that initializes hardware during boot, performs POST, and transfers control to bootloader. Stored in non-volatile memory (ROM/Flash).
Q: What is firmware? A: Low-level software permanently stored in hardware (ROM/Flash). Provides control and monitoring of device. Between hardware and OS/applications.
Q: BIOS vs Firmware? A: BIOS is type of firmware specific to PC boot process. Firmware is broader term covering all embedded software (BIOS, UEFI, embedded controllers, device firmware).
Q: What is POST? A: Power-On Self-Test. Hardware diagnostics run by BIOS at startup. Tests CPU, RAM, storage, peripherals. Beep codes indicate failures.
Q: What happens during boot process? A: 1) Power on → 2) BIOS POST → 3) Hardware initialization → 4) Boot device detection → 5) Load MBR/bootloader → 6) OS loads → 7) Control to OS.
Q: What is CMOS? A: Complementary Metal-Oxide-Semiconductor. Battery-backed volatile memory storing BIOS settings (date/time, boot order, hardware config). Cleared when battery dies.
Q: BIOS vs CMOS? A: BIOS = firmware program in ROM. CMOS = volatile RAM storing BIOS settings. BIOS reads/writes CMOS for configuration.
Q: What is BIOS beep codes? A: Audio signals indicating POST errors. Pattern identifies problem (e.g., 1 long + 2 short = video error). Vendor-specific (AMI, Award, Phoenix differ).
Q: Common AMI beep codes? A: 1 beep = POST successful, 2 beeps = POST error (check screen), 3 beeps = memory error, 5 beeps = CPU error, 1 long 3 short = video error.
Q: What is BIOS ROM? A: Read-Only Memory chip storing BIOS firmware. Modern systems use Flash memory (rewritable) instead of true ROM for updates.
UEFI Vs Legacy BIOS
Q: What is UEFI? A: Unified Extensible Firmware Interface. Modern replacement for BIOS. 32/64-bit, modular, supports GPT, secure boot, mouse support, networking, larger drives (>2TB).
Q: UEFI vs Legacy BIOS? A: UEFI = modern, 32/64-bit, GPT, >2TB drives, secure boot, faster boot, GUI. Legacy BIOS = 16-bit, MBR, ≤2TB, text interface, slower.
Q: What is CSM? A: Compatibility Support Module. UEFI component providing legacy BIOS compatibility. Allows booting MBR-based OSes on UEFI systems.
Q: What is Secure Boot? A: UEFI feature verifying bootloader signatures before execution. Prevents unsigned/malicious code. Uses digital certificates. Requires signed OS.
Q: What is GPT? A: GUID Partition Table. Modern partitioning scheme replacing MBR. Supports >2TB drives, 128 partitions, redundant headers, CRC protection. Required for UEFI boot on large drives.
Q: MBR vs GPT? A: MBR = legacy, 2TB max, 4 primary partitions, single copy at sector 0. GPT = modern, 9.4ZB max, 128 partitions, redundant copies, CRC checks.
Q: What is EFI System Partition (ESP)? A: FAT32 partition storing UEFI bootloaders, drivers, and utilities. Required for UEFI boot. Contains /EFI directory structure.
Q: What is UEFI boot manager? A: Manages boot entries and order. Stored in NVRAM. Lists bootable devices/files. User can select boot option. Updates via OS or firmware setup.
Boot Process Details
Q: What is bootloader? A: Small program loaded by BIOS/UEFI to start OS. Examples: GRUB (Linux), Windows Boot Manager, NTLDR. Resides in MBR or ESP.
Q: What is MBR? A: Master Boot Record. First sector (512 bytes) of drive. Contains bootloader code (446 bytes), partition table (64 bytes), signature (2 bytes).
Q: Boot sequence stages? A: 1) Power → 2) BIOS/UEFI loads → 3) POST → 4) Initialize hardware → 5) Find boot device → 6) Load bootloader → 7) Bootloader loads OS kernel → 8) OS initializes.
Q: What is boot order/priority? A: Sequence BIOS checks devices for bootloader (e.g., USB → HDD → Network). Configurable in BIOS setup. First bootable device is used.
Q: What is INT 19h? A: BIOS interrupt for bootstrap loader. Called after POST to load boot sector from boot device. Legacy BIOS mechanism.
Q: What is Option ROM? A: Firmware on add-in cards (video, RAID, NIC). Executed during POST. Provides device-specific initialization and boot services (e.g., network boot).
Q: What is PXE boot? A: Preboot eXecution Environment. Network booting using DHCP and TFTP. NIC Option ROM provides PXE capability. Used for diskless clients, deployment.
AMI Aptio & Products
Q: What is AMI Aptio? A: American Megatrends’ UEFI firmware platform. Modular architecture, supports latest hardware, secure boot, remote management. Industry-leading UEFI BIOS.
Q: What is Aptio V? A: Latest AMI Aptio generation. Enhanced security, performance optimizations, better hardware support, improved management features.
Q: What are UEFI modules? A: Self-contained code units in UEFI. Types: DXE drivers, protocols, applications. Provides modularity and extensibility. AMI Aptio heavily modular.
Q: What is DXE? A: Driver Execution Environment. UEFI phase after memory initialization. Loads drivers, constructs protocol database, prepares for OS handoff.
Q: UEFI boot phases? A: SEC (Security) → PEI (Pre-EFI Init) → DXE (Driver Execution) → BDS (Boot Device Selection) → TSL (Transient System Load) → RT (Runtime).
Q: What is PEI? A: Pre-EFI Initialization. Early UEFI phase. Initializes CPU, chipset, memory. Minimal services. Executes from cache/temporary memory (CAR).
Q: What is BDS? A: Boot Device Selection. UEFI phase presenting boot options, loading bootloader, handling user input. Implements boot manager functionality.
Q: What is UEFI Runtime Services? A: Services available to OS after boot. Variable storage (NVRAM), time/date, reset/shutdown. Unlike boot services (available only during boot).
Q: What is UEFI Boot Services? A: Services during boot phase. Memory management, protocol handling, device access. Terminated when OS calls ExitBootServices().
Firmware Updates & Flash
Q: What is BIOS flashing? A: Updating BIOS firmware by rewriting Flash memory. Methods: DOS utility, Windows utility, UEFI built-in, USB stick. Can brick system if interrupted.
Q: What is dual BIOS? A: Two BIOS chips - main and backup. If main corrupted, automatically switches to backup. Recovery feature. Found on higher-end motherboards.
Q: What is BIOS recovery? A: Restoring corrupted BIOS. Methods: dual BIOS, special key combination, USB recovery, dedicated recovery chip, programmer.
Q: What is Flash descriptor? A: Data structure defining Flash layout regions (BIOS, ME, GbE, etc.). Used by Intel chipsets. Controls access permissions.
Q: What is Intel ME? A: Management Engine. Separate processor in chipset with own firmware. Provides remote management, security features. Runs independently of main CPU.
Q: What is SPI Flash? A: Serial Peripheral Interface Flash. Non-volatile memory storing BIOS/UEFI. Fast, small, cheap. Common sizes: 8MB, 16MB, 32MB.
Q: What is BIOS chip types? A: EEPROM (electrically erasable), Flash EEPROM (modern), ROM (rare, obsolete). Flash allows in-system updates.
Q: Risks of BIOS update? A: Power loss during update = bricked system, incompatible version = instability, wrong firmware = brick. Always use UPS, verify version, read instructions.
Hardware Initialization
Q: What does BIOS initialize? A: CPU (microcode), chipset, memory (SPD, training), PCI/PCIe devices, storage controllers, USB, display, integrated peripherals, ACPI tables.
Q: What is SPD? A: Serial Presence Detect. EEPROM on RAM module storing timing/voltage specifications. BIOS reads SPD to configure memory controller.
Q: What is memory training? A: BIOS process calibrating memory timings for stability. Tests different delays, voltages. Ensures reliable operation. Time-consuming during first boot.
Q: What is PCIe enumeration? A: BIOS discovering and configuring PCIe devices. Assigns resources (memory, I/O, IRQs), configures bridge hierarchy. Creates device tree.
Q: What is ACPI? A: Advanced Configuration and Power Interface. Standard for hardware discovery, power management, configuration. BIOS provides ACPI tables to OS.
Q: Common ACPI tables? A: DSDT (Differentiated System Description), FADT (Fixed ACPI Description), MADT (Multiple APIC Description), SRAT (System Resource Affinity), SLIT (System Locality Information).
Q: What is SMBIOS? A: System Management BIOS. Standard for system information tables. Provides hardware inventory to OS (CPU, memory, motherboard info). Type structures (0-127).
Q: What is device tree? A: Hierarchical structure describing hardware topology. Used by firmware and OS. Contains device addresses, properties, relationships.
Embedded Controllers
Q: What is EC? A: Embedded Controller. Microcontroller managing keyboard, touchpad, battery, thermal, LED control. Runs independent firmware. Common in laptops.
Q: What is EC firmware? A: Software running on embedded controller. Handles low-level hardware control, sensors, power sequencing. Updated separately from BIOS.
Q: What is BMC? A: Baseboard Management Controller. Server-grade embedded controller. Provides remote management (IPMI), monitoring, KVM-over-IP. Independent of main system.
Q: What is IPMI? A: Intelligent Platform Management Interface. Standard for out-of-band server management via BMC. Remote monitoring, control, recovery.
Security Features
Q: What is TPM? A: Trusted Platform Module. Hardware security chip storing encryption keys, passwords, certificates. Enables BitLocker, measured boot. Discrete or firmware-based.
Q: What is measured boot? A: TPM feature creating hash chain of boot components. Detects unauthorized modifications. BIOS measures bootloader, bootloader measures OS.
Q: What is BIOS password? A: Security feature restricting access. Types: Setup password (BIOS config), User password (boot), HDD password. Stored in NVRAM or CMOS.
Q: How to clear BIOS password? A: Reset CMOS (jumper/battery), use backdoor password (vendor-specific), chip programming. Depends on implementation.
Q: What is BootGuard? A: Intel hardware-based boot integrity. Verifies IBB (Initial Boot Block) signature using burned-in key. Prevents BIOS-level malware.
Q: What is Intel BIOS Guard? A: Protects BIOS Flash from unauthorized writes. CPU enforces Flash descriptor rules. Prevents BIOS malware.
Q: What is firmware rootkit? A: Malware in BIOS/UEFI. Persists across OS reinstalls, difficult to detect. Secure Boot and BootGuard help prevent.
Power Management
Q: What is ACPI states? A: G0 (Working), G1 (Sleeping: S1-S4), G2 (Soft Off: S5), G3 (Mechanical Off). S states: S0 (on), S1 (CPU stopped), S3 (RAM active, suspend), S4 (hibernate), S5 (soft off).
Q: S3 vs S4 vs S5? A: S3 = suspend to RAM, fast resume, power to RAM. S4 = hibernate to disk, no power, slower resume. S5 = soft off, OS not running.
Q: What is C-states? A: CPU power states. C0 (active), C1 (halt), C2 (stop clock), C3+ (deeper sleep). Lower power, higher wake latency with deeper states.
Q: What is P-states? A: CPU performance states. Operating frequencies/voltages. P0 = maximum performance, higher P = lower frequency/power. Dynamic frequency scaling.
Q: What is Wake-on-LAN? A: Feature allowing network packet to wake sleeping system. Requires NIC support, BIOS setting, power to NIC in sleep.
Debugging & Development
Q: What is POST code? A: Hex value output to port 80h indicating boot progress. Displayed on debug card. Helps identify where boot fails.
Q: What is a debug card/POST card? A: PCIe/ISA card displaying POST codes. Shows last code before failure. Essential for troubleshooting boot issues.
Q: What is serial debug output? A: BIOS/UEFI debug messages sent to serial port. Captured on another PC. Detailed boot log for development/debugging.
Q: What is JTAG? A: Joint Test Action Group. Hardware debugging interface. Allows chip-level debugging, boundary scan, Flash programming. Professional tool.
Q: What is SPI Flash programmer? A: Hardware tool for reading/writing BIOS Flash chip. Used for recovery, development, analysis. Examples: CH341A, Dediprog.
Q: What is UEFITool? A: Popular tool for parsing, extracting, modifying UEFI firmware images. Open-source. Essential for firmware analysis.
Q: What is IFR? A: Internal Forms Representation. Binary format for BIOS setup menus. Can be extracted and analyzed to find hidden settings.
Q: What is AMIBCP? A: AMI BIOS Configuration Program. Tool for modifying AMI BIOS settings visibility and defaults. Used by OEMs and enthusiasts.
Storage & RAID
Q: What is AHCI? A: Advanced Host Controller Interface. Standard for SATA controllers. Enables hot-swap, NCQ, link power management. Modern standard.
Q: AHCI vs IDE mode? A: AHCI = modern, full SATA features, better performance. IDE/Legacy = compatibility mode, limited features, for old OSes.
Q: What is NVMe? A: Non-Volatile Memory Express. Protocol for PCIe SSDs. Much faster than SATA. Low latency, high IOPS. Modern systems boot from NVMe.
Q: What is RAID? A: Redundant Array of Independent Disks. Multiple drives for performance/redundancy. Common levels: 0 (stripe), 1 (mirror), 5 (parity), 10 (stripe+mirror).
Q: What is firmware RAID? A: RAID implemented in BIOS/UEFI Option ROM. Software RAID with BIOS assistance. Cheaper than hardware RAID, but uses CPU.
Q: What is OROM? A: Option ROM on controller (RAID, NIC, video). Provides boot-time services. Integrated into UEFI as modules.
Chipset & Platform
Q: What is chipset? A: Integrated circuits managing data flow between CPU, memory, peripherals. Typically Northbridge (high-speed: RAM, PCIe) + Southbridge (low-speed: USB, SATA, LPC).
Q: What is PCH? A: Platform Controller Hub. Intel’s modern single-chip chipset. Replaces separate North/South bridges. Connected to CPU via DMI.
Q: What is LPC bus? A: Low Pin Count bus. Connects legacy devices (Super I/O, EC, BIOS Flash). Replacement for ISA bus.
Q: What is Super I/O? A: Chip providing legacy I/O (serial, parallel, PS/2, floppy). Configured by BIOS. Connected via LPC.
Q: What is SMBus? A: System Management Bus. I2C-based for low-speed communication. BIOS uses for SPD reading, thermal monitoring, voltage regulation.
Modern Features
Q: What is Capsule Update? A: UEFI mechanism for firmware updates from OS. Update package placed in ESP, system reboots, BIOS updates itself. Safer than traditional methods.
Q: What is GOP? A: Graphics Output Protocol. UEFI interface for display. Replaces legacy VGA/VESA. Enables high-resolution UEFI setup screens.
Q: What is UEFI Shell? A: Command-line environment in UEFI. Run scripts, manage files, configure system, test. Similar to DOS prompt.
Q: What is EDID? A: Extended Display Identification Data. Display capabilities stored in monitor. BIOS reads via I2C to configure optimal resolution.
Q: What is Thunderbolt? A: High-speed I/O by Intel. Uses PCIe/DisplayPort over USB-C. Requires firmware in controller. BIOS provides security configuration.
Q: What is USB-C PD? A: Power Delivery. USB-C feature negotiating power delivery. BIOS/EC manages power contracts. Up to 100W charging.
This comprehensive list covers BIOS/UEFI fundamentals essential for AMI interviews. Understanding the boot flow, UEFI architecture, and AMI Aptio platform is crucial. Good luck with your interview!
ANSI SQL (MySQL)
SQL Basics
Q: What is SQL? A: Structured Query Language. Standard language for managing relational databases. Supports data querying, manipulation, definition, and control.
Q: Types of SQL commands? A: DDL (CREATE, ALTER, DROP, TRUNCATE), DML (SELECT, INSERT, UPDATE, DELETE), DCL (GRANT, REVOKE), TCL (COMMIT, ROLLBACK, SAVEPOINT).
Q: What is a database? A: Organized collection of structured data stored electronically. Managed by DBMS (Database Management System).
Q: What is a table? A: Collection of related data in rows (records) and columns (fields). Basic storage unit in relational databases.
Q: What is a row/record? A: Single entry in table. Horizontal entity containing data for all columns.
Q: What is a column/field? A: Attribute of table. Vertical entity with specific data type. All rows have same columns.
Q: What is a primary key? A: Column(s) uniquely identifying each row. Cannot be NULL. Only one per table. Automatically creates index.
Q: What is a foreign key? A: Column referencing primary key of another table. Establishes relationships. Enforces referential integrity.
Q: What is a unique key? A: Ensures all values in column are different. Can be NULL (unless specified NOT NULL). Multiple unique keys allowed per table.
Q: Primary key vs Unique key? A: Primary = one per table, NOT NULL, clustered index. Unique = multiple allowed, can be NULL, non-clustered index.
Q: What is a composite key? A: Primary/unique key consisting of multiple columns. Combination must be unique.
Q: What is NULL? A: Absence of value. Not zero or empty string. Represents unknown/missing data. Use IS NULL, not = NULL.
Data Types
Q: MySQL numeric types? A: INT, TINYINT, SMALLINT, MEDIUMINT, BIGINT (integers). DECIMAL/NUMERIC (exact), FLOAT, DOUBLE (approximate). BIT.
Q: INT vs BIGINT? A: INT = 4 bytes, -2B to 2B. BIGINT = 8 bytes, -9 quintillion to 9 quintillion. Use INT for most cases, BIGINT for very large numbers.
Q: FLOAT vs DECIMAL? A: FLOAT = approximate, faster, 4/8 bytes, scientific calculations. DECIMAL = exact, slower, variable size, financial data.
Q: MySQL string types? A: CHAR (fixed length, 0-255), VARCHAR (variable, 0-65535), TEXT (large text: TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT), BLOB (binary).
Q: CHAR vs VARCHAR? A: CHAR = fixed length, padded with spaces, faster for fixed-size data. VARCHAR = variable length, saves space, better for varying sizes.
Q: MySQL date/time types? A: DATE (YYYY-MM-DD), TIME (HH:MM:SS), DATETIME (YYYY-MM-DD HH:MM:SS), TIMESTAMP (auto-update), YEAR.
Q: DATETIME vs TIMESTAMP? A: DATETIME = 8 bytes, 1000-9999 range, no timezone. TIMESTAMP = 4 bytes, 1970-2038 range, timezone aware, auto-update on row change.
SELECT Queries
Q: Basic SELECT syntax? A: SELECT column1, column2 FROM table WHERE condition ORDER BY column LIMIT n;
Q: SELECT all columns? A: SELECT * FROM table; (asterisk means all columns, but avoid in production - specify columns for performance)
Q: DISTINCT keyword? A: Removes duplicate rows from result. SELECT DISTINCT column FROM table;
Q: WHERE clause? A: Filters rows based on condition. SELECT * FROM users WHERE age > 18;
Q: Common WHERE operators? A: = (equal), != or <> (not equal), < > ⇐ >= (comparison), BETWEEN, IN, LIKE, IS NULL, AND, OR, NOT.
Q: LIKE operator wildcards? A: % (any number of characters), _ (single character). Example: WHERE name LIKE 'J%' (starts with J).
Q: IN operator? A: Tests if value matches any in list. WHERE city IN ('NYC', 'LA', 'Chicago'). More readable than multiple ORs.
Q: BETWEEN operator? A: Range check (inclusive). WHERE age BETWEEN 18 AND 65 equivalent to age >= 18 AND age <= 65.
Q: ORDER BY clause? A: Sorts results. ORDER BY column ASC (ascending, default) or DESC (descending). Multiple columns: ORDER BY col1, col2.
Q: LIMIT clause? A: Restricts number of rows returned. LIMIT 10 (first 10), LIMIT 10 OFFSET 20 (skip 20, return 10). Used for pagination.
Aggregate Functions
Q: What are aggregate functions? A: Functions performing calculation on set of values, returning single value. Used with GROUP BY.
Q: Common aggregate functions? A: COUNT() (count rows), SUM() (total), AVG() (average), MAX() (maximum), MIN() (minimum).
Q: COUNT(*) vs COUNT(column)? A: COUNT(*) = all rows including NULLs. COUNT(column) = non-NULL values only.
Q: What is GROUP BY? A: Groups rows with same values. Used with aggregate functions. SELECT city, COUNT(*) FROM users GROUP BY city;
Q: What is HAVING? A: Filters groups after GROUP BY. Like WHERE but for aggregates. HAVING COUNT(*) > 5. WHERE filters before grouping.
Q: WHERE vs HAVING? A: WHERE = filters rows before grouping, can’t use aggregates. HAVING = filters groups after grouping, uses aggregates.
Q: GROUP BY with multiple columns? A: Creates groups for each unique combination. GROUP BY dept, city groups by department within each city.
JOIN Operations
Q: What is a JOIN? A: Combines rows from two or more tables based on related column. Creates result set from multiple tables.
Q: Types of JOINs? A: INNER JOIN (matching rows), LEFT JOIN (all left + matching right), RIGHT JOIN (all right + matching left), FULL JOIN (all rows), CROSS JOIN (cartesian product).
Q: INNER JOIN? A: Returns only matching rows from both tables. SELECT * FROM A INNER JOIN B ON A.id = B.a_id;
Q: LEFT JOIN (LEFT OUTER JOIN)? A: All rows from left table + matching rows from right. NULL for non-matching right rows. LEFT JOIN B ON A.id = B.a_id;
Q: RIGHT JOIN (RIGHT OUTER JOIN)? A: All rows from right table + matching rows from left. NULL for non-matching left rows. Mirror of LEFT JOIN.
Q: FULL OUTER JOIN? A: All rows from both tables. NULLs where no match. MySQL doesn’t support directly - use UNION of LEFT and RIGHT.
Q: CROSS JOIN? A: Cartesian product. Every row from first table with every row from second. SELECT * FROM A CROSS JOIN B; No ON clause.
Q: Self JOIN? A: Table joined with itself. Requires table aliases. Example: employee-manager relationship in same table.
Q: Natural JOIN? A: Automatically joins on columns with same name. SELECT * FROM A NATURAL JOIN B; Avoid - implicit, risky.
Q: JOIN with multiple conditions? A: JOIN B ON A.id = B.a_id AND A.status = B.status. Use AND/OR in ON clause.
Subqueries
Q: What is a subquery? A: Query nested inside another query. Can be in SELECT, FROM, WHERE, HAVING. Enclosed in parentheses.
Q: Types of subqueries? A: Scalar (single value), Column (single column), Row (single row multiple columns), Table (multiple rows/columns).
Q: Subquery in WHERE? A: SELECT * FROM users WHERE age > (SELECT AVG(age) FROM users); Inner query executes first.
Q: Subquery with IN? A: SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE country = 'USA');
Q: Correlated subquery? A: Subquery referencing outer query column. Executes for each outer row. Slower. WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.dept = e1.dept);
Q: EXISTS operator? A: Tests if subquery returns any rows. Returns TRUE/FALSE. WHERE EXISTS (SELECT 1 FROM orders WHERE user_id = users.id); More efficient than IN for large datasets.
Q: Subquery vs JOIN? A: JOIN generally faster for multiple columns. Subquery more readable for single value. EXISTS better than IN for large sets. Test performance.
INSERT, UPDATE, DELETE
Q: INSERT syntax? A: INSERT INTO table (col1, col2) VALUES (val1, val2); or INSERT INTO table VALUES (val1, val2, val3); (all columns).
Q: Insert multiple rows? A: INSERT INTO table (col1, col2) VALUES (v1, v2), (v3, v4), (v5, v6); Faster than separate INSERTs.
Q: INSERT with SELECT? A: INSERT INTO table1 (col1, col2) SELECT col3, col4 FROM table2 WHERE condition; Copy data between tables.
Q: UPDATE syntax? A: UPDATE table SET col1 = val1, col2 = val2 WHERE condition; ALWAYS use WHERE to avoid updating all rows.
Q: DELETE syntax? A: DELETE FROM table WHERE condition; ALWAYS use WHERE. Without WHERE, deletes all rows.
Q: TRUNCATE vs DELETE? A: TRUNCATE = removes all rows, faster, resets auto-increment, can’t rollback (DDL). DELETE = can use WHERE, slower, can rollback (DML).
Q: DELETE vs DROP? A: DELETE = removes rows, keeps table structure. DROP = removes entire table including structure.
Q: What is REPLACE? A: MySQL extension. INSERT if not exists, else DELETE and INSERT. REPLACE INTO table (id, name) VALUES (1, 'John');
Q: What is INSERT IGNORE? A: MySQL extension. Ignores errors (like duplicate key). Continues with remaining inserts. INSERT IGNORE INTO table VALUES (…);
Q: ON DUPLICATE KEY UPDATE? A: MySQL extension. UPDATE if key exists, else INSERT. INSERT INTO table (id, count) VALUES (1, 1) ON DUPLICATE KEY UPDATE count = count + 1;
DDL Commands
Q: CREATE TABLE syntax? A: CREATE TABLE table_name (col1 datatype constraints, col2 datatype, PRIMARY KEY (col1));
Q: Common column constraints? A: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT, AUTO_INCREMENT.
Q: AUTO_INCREMENT? A: Automatically generates unique number for new row. Used for primary keys. MySQL-specific (IDENTITY in SQL Server).
Q: DEFAULT constraint? A: Sets default value if none provided. created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP.
Q: CHECK constraint? A: Validates column values. age INT CHECK (age >= 18). Supported in MySQL 8.0.16+.
Q: FOREIGN KEY constraint? A: FOREIGN KEY (dept_id) REFERENCES departments(id) ON DELETE CASCADE ON UPDATE CASCADE;
Q: ON DELETE CASCADE? A: Automatically deletes child rows when parent deleted. Options: CASCADE, SET NULL, SET DEFAULT, NO ACTION, RESTRICT.
Q: ALTER TABLE syntax? A: Add: ALTER TABLE table ADD column datatype; Modify: ALTER TABLE table MODIFY column datatype; Drop: ALTER TABLE table DROP COLUMN column;
Q: DROP TABLE? A: Removes table completely. DROP TABLE table_name; Add IF EXISTS to prevent error: DROP TABLE IF EXISTS table_name;
Q: RENAME TABLE? A: RENAME TABLE old_name TO new_name; or ALTER TABLE old_name RENAME TO new_name;
Q: CREATE INDEX? A: CREATE INDEX index_name ON table (column); Speeds up queries but slows inserts/updates.
Q: DROP INDEX? A: DROP INDEX index_name ON table; MySQL syntax (differs from ANSI).
Advanced SQL
Q: What is a VIEW? A: Virtual table based on query. Doesn’t store data. CREATE VIEW view_name AS SELECT …; Simplifies complex queries, provides security.
Q: Updatable vs Non-updatable VIEW? A: Updatable = simple SELECT, no aggregates/DISTINCT/GROUP BY/JOINs. Non-updatable = complex queries. INSERT/UPDATE/DELETE work on updatable views.
Q: What is a UNION? A: Combines results of multiple SELECT statements. Removes duplicates. Same number/type of columns required.
Q: UNION vs UNION ALL? A: UNION = removes duplicates, slower. UNION ALL = keeps duplicates, faster. Use UNION ALL when duplicates impossible or acceptable.
Q: What is CASE? A: Conditional logic in SQL. CASE WHEN condition THEN result WHEN condition2 THEN result2 ELSE default END AS alias;
Q: CASE in SELECT? A: SELECT name, CASE WHEN age < 18 THEN 'Minor' WHEN age < 65 THEN 'Adult' ELSE 'Senior' END AS category FROM users;
Q: What is COALESCE? A: Returns first non-NULL value. COALESCE(phone, email, 'No contact'). Useful for handling NULLs.
Q: What is IFNULL? A: MySQL function. Returns second value if first is NULL. IFNULL(phone, 'Not provided'). Similar to COALESCE with 2 args.
Q: What is NULLIF? A: Returns NULL if two values equal, else first value. NULLIF(value, 0) converts 0 to NULL. Prevents division by zero.
String Functions
Q: Common string functions? A: CONCAT, SUBSTRING, LENGTH, UPPER, LOWER, TRIM, REPLACE, LEFT, RIGHT, REVERSE.
Q: CONCAT function? A: Joins strings. CONCAT(first_name, ' ', last_name) AS full_name. Returns NULL if any arg is NULL (use CONCAT_WS).
Q: CONCAT_WS function? A: Concat With Separator. Ignores NULLs. CONCAT_WS('-', year, month, day) produces ‘2024-03-15’.
Q: SUBSTRING function? A: Extracts substring. SUBSTRING(text, start, length). 1-indexed. SUBSTRING('Hello', 2, 3) = ‘ell’.
Q: LENGTH vs CHAR_LENGTH? A: LENGTH = bytes. CHAR_LENGTH = characters. Differ for multi-byte characters (UTF-8). Use CHAR_LENGTH for character count.
Q: TRIM function? A: Removes leading/trailing spaces. TRIM(' text ') = ‘text’. LTRIM (left), RTRIM (right).
Q: REPLACE function? A: Replaces substring. REPLACE(text, 'old', 'new'). Case-sensitive. Replaces all occurrences.
Q: UPPER and LOWER? A: Convert case. UPPER('hello') = ‘HELLO’, LOWER('WORLD') = ‘world’. Useful for case-insensitive comparisons.
Date/Time Functions
Q: NOW() vs CURRENT_TIMESTAMP? A: Both return current date and time. NOW() is function, CURRENT_TIMESTAMP is SQL standard. Identical in MySQL.
Q: CURDATE() and CURTIME()? A: CURDATE() = current date (YYYY-MM-DD). CURTIME() = current time (HH:MM:SS).
Q: DATE_FORMAT function? A: Formats date. DATE_FORMAT(date, '%Y-%m-%d'). %Y=year, %m=month, %d=day, %H=hour, %i=minute, %s=second.
Q: DATE_ADD and DATE_SUB? A: Add/subtract intervals. DATE_ADD(date, INTERVAL 7 DAY), DATE_SUB(date, INTERVAL 1 MONTH).
Q: DATEDIFF function? A: Days between dates. DATEDIFF(date1, date2). Returns date1 - date2 in days.
Q: EXTRACT function? A: Extract part of date. EXTRACT(YEAR FROM date). Parts: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND.
Q: YEAR, MONTH, DAY functions? A: Extract specific parts. YEAR(date), MONTH(date), DAY(date). Shorthand for EXTRACT.
Numeric Functions
Q: ROUND function? A: Rounds to decimal places. ROUND(123.456, 2) = 123.46. ROUND(123.456, 0) = 123. ROUND(123.456, -1) = 120.
Q: CEIL and FLOOR? A: CEIL = rounds up. FLOOR = rounds down. CEIL(4.3) = 5, FLOOR(4.9) = 4.
Q: ABS function? A: Absolute value. ABS(-5) = 5. Removes negative sign.
Q: MOD function? A: Modulo (remainder). MOD(10, 3) = 1. Also: 10 % 3 = 1.
Q: POWER function? A: Exponentiation. POWER(2, 3) = 8 (2³). Also: POW(2, 3).
Q: SQRT function? A: Square root. SQRT(16) = 4.
Window Functions (MySQL 8.0+)
Q: What are window functions? A: Perform calculations across set of rows related to current row. Don’t collapse rows like GROUP BY. Use OVER clause.
Q: ROW_NUMBER function? A: Assigns unique sequential number to rows. ROW_NUMBER() OVER (ORDER BY salary DESC) ranks by salary.
Q: RANK vs DENSE_RANK? A: RANK = gaps in sequence (1,2,2,4). DENSE_RANK = no gaps (1,2,2,3). Both handle ties.
Q: PARTITION BY in window functions? A: Divides rows into partitions. Function resets for each partition. ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) ranks within department.
Q: LAG and LEAD functions? A: Access previous/next row. LAG(salary, 1) OVER (ORDER BY date) gets previous salary. LEAD gets next. Useful for comparisons.
Q: Common window functions? A: ROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG, LEAD, FIRST_VALUE, LAST_VALUE, SUM() OVER, AVG() OVER.
Transactions
Q: What is a transaction? A: Group of SQL statements executed as single unit. Either all succeed (COMMIT) or all fail (ROLLBACK). ACID properties.
Q: ACID properties? A: Atomicity (all or nothing), Consistency (valid state), Isolation (concurrent transactions don’t interfere), Durability (permanent after commit).
Q: START TRANSACTION? A: Begins transaction. START TRANSACTION; or BEGIN;. Followed by SQL statements, then COMMIT or ROLLBACK.
Q: COMMIT? A: Saves all changes permanently. Ends transaction. COMMIT; Makes changes visible to other transactions.
Q: ROLLBACK? A: Undoes all changes since START TRANSACTION. Ends transaction. ROLLBACK; Returns to pre-transaction state.
Q: SAVEPOINT? A: Checkpoint within transaction. SAVEPOINT savepoint_name; Can rollback to savepoint: ROLLBACK TO savepoint_name;
Q: What is autocommit? A: MySQL default mode. Each statement automatically committed. Disable: SET autocommit = 0; Enable: SET autocommit = 1;
Q: Transaction isolation levels? A: READ UNCOMMITTED (dirty reads), READ COMMITTED (no dirty reads), REPEATABLE READ (default in MySQL), SERIALIZABLE (strictest).
Indexes
Q: What is an index? A: Data structure improving query speed. Like book index. Speeds SELECT but slows INSERT/UPDATE/DELETE.
Q: Types of indexes? A: PRIMARY KEY (unique, not null), UNIQUE (unique values), INDEX/KEY (regular), FULLTEXT (text search), SPATIAL (geographic data).
Q: Clustered vs Non-clustered index? A: Clustered = physically orders data, one per table (primary key in InnoDB). Non-clustered = separate structure with pointers, multiple allowed.
Q: When to use indexes? A: Columns in WHERE, JOIN, ORDER BY. Foreign keys. Frequently searched columns. Don’t over-index small tables or frequently updated columns.
Q: Composite index? A: Index on multiple columns. CREATE INDEX idx ON table (col1, col2); Order matters - use leftmost prefix.
Q: EXPLAIN statement? A: Shows query execution plan. EXPLAIN SELECT …; Reveals if indexes used, rows scanned, join types. Essential for optimization.
Q: What slows down indexes? A: Functions on columns WHERE YEAR(date) = 2024, type mismatches WHERE id = '123', leading wildcards WHERE name LIKE '%son'.
Normalization
Q: What is normalization? A: Organizing database to reduce redundancy and dependency. Divides large tables into smaller, related tables.
Q: 1NF (First Normal Form)? A: Atomic values (no repeating groups/arrays). Each column contains single value. Each row unique (primary key).
Q: 2NF (Second Normal Form)? A: 1NF + no partial dependency. Non-key attributes fully dependent on entire primary key (not just part of composite key).
Q: 3NF (Third Normal Form)? A: 2NF + no transitive dependency. Non-key attributes depend only on primary key, not on other non-key attributes.
Q: BCNF (Boyce-Codd Normal Form)? A: Stricter 3NF. For every dependency X→Y, X must be super key. Eliminates all redundancy based on functional dependencies.
Q: Denormalization? A: Intentionally introducing redundancy for performance. Trade-off: faster reads, slower writes, more storage, complexity.
Performance & Optimization
Q: How to optimize slow queries? A: Use EXPLAIN, add indexes, avoid SELECT *, limit results, optimize JOINs, use appropriate data types, cache results, partition tables.
Q: N+1 query problem? A: One query for parent, N queries for children. Solution: Use JOIN or IN to fetch all data in single/few queries.
Q: Query vs Subquery vs JOIN? A: JOIN generally fastest for multiple columns. Subquery readable for single values. EXISTS better than IN. Always test with real data.
Q: SELECT * issues? A: Fetches unnecessary data, increases network traffic, breaks when columns added, prevents covering indexes. Specify needed columns.
Q: Covering index? A: Index containing all columns needed by query. Query served entirely from index without accessing table. Very fast.
Q: Pagination best practices? A: Use indexed columns in ORDER BY. LIMIT 10 OFFSET 1000 slow for large offsets. Use keyset pagination: WHERE id > last_id ORDER BY id LIMIT 10.
MySQL-Specific Features
Q: Storage engines in MySQL? A: InnoDB (default, ACID, foreign keys, row-level locking), MyISAM (fast reads, table-level locking, no transactions), MEMORY (in-memory, temporary).
Q: InnoDB vs MyISAM? A: InnoDB = transactions, foreign keys, crash recovery, row locking, slower. MyISAM = no transactions, table locking, faster for read-heavy, simpler.
Q: What is SHOW TABLES? A: Lists all tables in current database. MySQL command: SHOW TABLES; Also: SHOW DATABASES;, SHOW COLUMNS FROM table;
Q: DESCRIBE command? A: Shows table structure. DESCRIBE table_name; or DESC table_name; Displays columns, types, keys, defaults.
Q: LIMIT with OFFSET? A: LIMIT count OFFSET skip or LIMIT skip, count. Skip first N rows, return next M. Used for pagination.
Q: IF function? A: MySQL conditional. IF(condition, true_value, false_value). Single condition. Use CASE for multiple.
Q: MySQL wildcards in LIKE? A: % = zero or more characters. _ = exactly one character. Escape: LIKE '50\%' ESCAPE '\' for literal %.
This comprehensive guide covers essential SQL concepts for MySQL interviews. Practice writing queries and understand execution plans for optimization discussions. Good luck!