Question 1

What is Industry 4.0?

Accepted Answer

In short: The fourth industrial revolution: connecting machines, products, people and IT systems through IIoT, cloud/edge computing, data analytics and AI so factories become transparent, flexible and self-optimising. It builds on the automation of Industry 3.0 (PLCs, IT) by adding connectivity and data-driven decisions — e.g. real-time OEE, predictive maintenance, full traceability and digital twins.

Question 2

What is IIoT and how is it different from consumer IoT?

Accepted Answer

In short: Industrial IoT connects industrial equipment — sensors, PLCs, machines — to collect and use data. Compared with consumer IoT it demands higher reliability, determinism and safety, works with industrial protocols (OPC UA, Modbus, PROFINET), runs in harsh environments with long equipment lifetimes (20+ years, brownfield machines), and has stricter security and uptime requirements.

Question 3

What is OEE and how is it calculated?

Accepted Answer

In short: Overall Equipment Effectiveness = Availability × Performance × Quality. Availability = run time ÷ planned time; Performance = (ideal cycle time × total count) ÷ run time; Quality = good count ÷ total count. Example: 87.5 % × 90.5 % × 95 % ≈ 75 %. It shows how much of planned time is truly productive and which loss to attack first.

Question 4

What is an MES and how does it differ from ERP?

Accepted Answer

In short: MES executes and tracks production on the shop floor in real time — orders dispatched to lines, data collection, quality, traceability, OEE, work instructions. ERP manages the business — finance, purchasing, inventory, sales, planning over days/weeks. ISA-95 defines the integration: ERP sends orders and receives production confirmations and consumption from MES.

Question 5

What is MQTT and why is it popular in IIoT?

Accepted Answer

In short: A lightweight publish/subscribe protocol over TCP with a broker. Small overhead, works on unreliable networks, decouples producers from consumers, supports QoS levels, retained messages, last will (detect offline devices) and persistent sessions; easy to secure with TLS and certificates; all clouds support it. Sparkplug B adds a standard namespace, payload format and state management for industrial use.

Question 6

Explain MQTT QoS levels.

Accepted Answer

In short: QoS 0 — at most once, fire-and-forget, may be lost. QoS 1 — at least once, acknowledged, may be duplicated (consumers must be idempotent). QoS 2 — exactly once via a four-step handshake, slower. Telemetry usually uses 0 or 1; commands and important events use 1 with idempotency or 2.

Question 7

What is the difference between OPC UA and MQTT? When do you use which?

Accepted Answer

In short: OPC UA is a full industrial interoperability standard — rich information models, browse/read/write/subscribe/methods, built-in security — ideal for connecting to machines and controllers. MQTT is a lightweight transport for pub/sub distribution of data to many consumers and to the cloud. Common pattern: OPC UA at the machine/edge, MQTT (or OPC UA PubSub over MQTT) from the edge to the enterprise/cloud.

Question 8

What is edge computing and why is it needed in factories?

Accepted Answer

In short: Processing data close to the machines on gateways or industrial PCs. Needed for low-latency decisions, operation when the network or cloud is down (store-and-forward), reducing bandwidth by filtering/aggregating high-frequency data, protocol translation, and keeping sensitive OT networks closed while sending only outbound data.

Question 9

How does predictive maintenance work? What data do you need?

Accepted Answer

In short: Collect condition data (vibration, temperature, current, pressure, cycles) plus maintenance and failure history, engineer features (RMS, spectral peaks, trends), train anomaly-detection or remaining-useful-life models, deploy them at the edge or cloud, and turn predictions into alerts and CMMS work orders. Success needs domain experts, enough failure examples (or anomaly detection when rare), and a feedback loop measuring avoided downtime and false alarms.

Question 10

Design a real-time OEE dashboard for 20 production lines.

Accepted Answer

In short: Edge per line reads machine state, counts and reject signals via OPC UA/Modbus, timestamps in UTC and publishes state changes to MQTT/IoT Hub. A stream processor or .NET consumer maintains current state per line, accumulates run/down time by reason and counts, stores events in a time-series/SQL store and shift summaries in SQL. An ASP.NET Core API + SignalR pushes live updates to an Angular/React dashboard (andon view, OEE gauges, downtime Pareto). Operators enter downtime reasons on a tablet. Handle shift calendars, planned stops, ideal cycle times per product, and offline buffering.

Question 11

A plant has 30-year-old machines without network connectivity. How do you bring them into an Industry 4.0 project?

Accepted Answer

In short: Retrofit: add sensors (current clamps for run/stop, vibration sensors, photo-eyes or counters for parts), read existing PLC I/O through gateways or protocol converters (serial Modbus → TCP), or use IO-Link and small edge devices. Start with simple, high-value signals (running/stopped, count, reject) to get OEE and downtime, then add condition monitoring. Keep the machine s safety and control untouched; document and secure the new connections.

Question 12

OEE dashboards show 110 % performance on some lines. What is wrong?

Accepted Answer

In short: Performance above 100 % means the ideal cycle time is wrong (too slow) or counts are inflated — wrong ideal cycle time for the running product, product not switched in the system at changeover, double counting from sensor bounce (needs debounce/edge detection), counting rejects twice, or run time understated (downtime recorded incorrectly). Validate the master data per product and the signal logic at the edge.

Question 13

What is a PLC and how is it different from a microcontroller or PC?

Accepted Answer

In short: A PLC is a rugged industrial controller that reads inputs, runs a control program in a deterministic scan cycle and drives outputs. Compared to a PC it is built for harsh environments, real-time deterministic timing, direct industrial I/O and decades of 24/7 operation, programmed in IEC 61131-3 languages. A microcontroller is a chip that needs custom boards, protection and certification to do the same job.

Question 14

Explain the PLC scan cycle.

Accepted Answer

In short: Read all inputs into the input image table → execute the program top to bottom using that image → write the output image to the physical outputs → do communication and diagnostics → repeat. Scan time is usually milliseconds. Because inputs are sampled once per scan, very short pulses need high-speed inputs or interrupts.

Question 15

What are the IEC 61131-3 programming languages?

Accepted Answer

In short: Ladder Diagram (relay-style graphics), Function Block Diagram (blocks connected by signals), Structured Text (high-level text like Pascal), Sequential Function Chart (steps and transitions for sequences), and Instruction List (assembly-like, deprecated since the 2013 edition). Ladder for discrete logic, ST for maths and data, SFC for sequences, FBD for process control.

Question 16

What is the difference between SCADA, HMI and DCS?

Accepted Answer

In short: HMI is the operator interface for a machine or line. SCADA supervises many controllers over a plant or wide area — data acquisition, control, alarms, trends and reporting. A DCS is an integrated control system (controllers + HMI + engineering) for continuous processes like refineries and power plants, where control is distributed across controllers on a common platform.

Question 17

Why is 4–20 mA used instead of 0–20 mA or 0–10 V?

Accepted Answer

In short: Current signals are not affected by voltage drop over long cables and are less sensitive to electrical noise. The live zero at 4 mA lets you detect a broken wire (current near 0 mA) as a fault rather than reading it as 0 %, and the 4 mA can power two-wire loop-powered transmitters.

Question 18

Explain the start/stop seal-in (latching) circuit.

Accepted Answer

In short: Start (NO) in parallel with a contact of the motor output, in series with Stop (NC in the field) and overload. Pressing Start energises the motor coil; the motor s own contact keeps the rung true after Start is released (seal-in); pressing Stop or an overload breaks the rung. In ST: Motor := (Start OR Motor) AND NOT Stop AND NOT Overload; .

Question 19

Explain TON, TOF and counters with an example.

Accepted Answer

In short: TON (on-delay): output becomes true when the input has stayed true for the preset — e.g. start a conveyor 5 s after a horn. TOF (off-delay): output stays true for the preset after the input turns false — e.g. keep a cooling fan running 2 minutes after the motor stops. CTU counts rising edges to a preset — e.g. 100 parts fill a box, then reset.

Question 20

What is Modbus? Explain its data model and function codes.

Accepted Answer

In short: A simple open request/response protocol (RTU over RS-485 serial, TCP over Ethernet on port 502). Data tables: coils (read/write bits — FC01/05/15), discrete inputs (read-only bits — FC02), input registers (read-only 16-bit — FC04) and holding registers (read/write 16-bit — FC03/06/16). 32-bit values span two registers with device-specific word order. It has no security, so it belongs on segmented networks.

Question 21

What is OPC UA and why is it important for Industry 4.0?

Accepted Answer

In short: A vendor- and platform-independent standard (IEC 62541) for secure industrial data exchange. It provides a rich information model (typed objects, not just addresses), services for read/write/subscribe/methods/history/alarms, built-in security with certificates and encryption, and transports from client/server to PubSub over MQTT. It lets MES, cloud and analytics systems read machine data the same way from any vendor.

Question 22

How would you collect data from 50 PLCs of different brands into a .NET application?

Accepted Answer

In short: Use a gateway/OPC UA layer to normalise protocols (built-in OPC UA servers, or Kepware/Ignition for S7, EtherNet/IP, Modbus), then a .NET edge service (Worker Service) that subscribes to OPC UA monitored items or polls Modbus, adds source timestamps and quality, buffers locally (store-and-forward) and publishes to MQTT/Azure IoT Hub or writes to a time-series/SQL database. Use a tag configuration table, a consistent naming model (ISA-95 hierarchy), and keep OT and IT networks separated by a DMZ.

Question 23

How do you secure an OT network?

Accepted Answer

Defence in depth per IEC 62443: asset inventory, segmentation into zones and conduits with firewalls and an industrial DMZ, no direct internet access to controllers, secure protocols (OPC UA with signing/encryption), strict remote access (jump host, MFA, recording), least-privilege accounts, change control and backups of PLC programs, patching in maintenance windows, OT-aware monitoring, and incident response plans that consider safety.

Question 24

A motor sometimes doesn't start when the operator presses Start on the HMI. How do you troubleshoot?

Accepted Answer

In short: Check the PLC online: is the HMI command bit reaching the PLC (HMI write and tag address), is the command a momentary pulse shorter than the scan or overwritten elsewhere (duplicate coil, another rung/routine), are permissives/interlocks true (stop, overload, guard, upstream conditions), any faults on the drive/VFD, the output module and contactor (output LED, fuse, wiring). Use cross-references and a trend of the involved bits; check the alarm log for the time of failure.

Question 25

The MES shows wrong temperature values from a Modbus device. What could be wrong?

Accepted Answer

In short: Wrong register address (0- vs 1-based offset: 40001 is register 0), wrong function code (holding vs input registers), wrong data type or word/byte order for 32-bit floats, missing scaling factor (value ×10), wrong slave/unit id, or reading stale data after a communication error without checking quality. Compare with a tool like Modbus Poll and the device manual.

Question 26

What is REST? What are its constraints?

Accepted Answer

In short: REST is an architectural style for networked APIs (Roy Fielding, 2000). Constraints: client–server separation, statelessness (each request carries everything needed, e.g. a token), cacheability, a uniform interface (resources identified by URIs, standard methods, self-descriptive messages, optionally hypermedia), a layered system, and optional code-on-demand. In practice: resource-oriented URLs, HTTP verbs, status codes, and JSON representations.

Question 27

What is the difference between PUT, POST and PATCH?

Accepted Answer

In short: POST creates a resource (server assigns the ID) or triggers a process; not idempotent; returns 201 + Location. PUT replaces the whole resource at a known URI; idempotent (sending it twice gives the same state). PATCH partially updates (JSON Patch or JSON Merge Patch); not necessarily idempotent. Missing fields in PUT usually mean set to default/null , in PATCH they mean leave unchanged .

Question 28

What is the difference between 401 and 403?

Accepted Answer

In short: 401 Unauthorized really means unauthenticated : no valid credentials, so the client should log in or refresh its token (the response includes WWW-Authenticate ). 403 Forbidden means the identity is known but lacks permission, and re-authenticating won t help. In ASP.NET Core, Challenge() produces 401 and Forbid() produces 403.

Question 29

What does the [ApiController] attribute do?

Accepted Answer

In short: It enables API conventions: attribute routing is required, invalid ModelState automatically returns 400 with ValidationProblemDetails (no if (!ModelState.IsValid) needed), binding sources are inferred ( [FromBody] for complex types, route/query for simple ones, IFormFile from the form), and error status codes get ProblemDetails bodies. Apply it to the class or the whole assembly.

Question 30

What is middleware? How do you write custom middleware?

Accepted Answer

In short: Middleware are components in the request pipeline. Each receives HttpContext , can do work, call next to pass the request on, and do more work on the way back, or short-circuit. Write a class with a constructor taking RequestDelegate next and an InvokeAsync(HttpContext) method, then register it with app.UseMiddleware T () ; or use inline app.Use(async (ctx, next) = ...) . Run is terminal; Map branches. Order defines behaviour.

Question 31

What is the difference between IActionResult and ActionResult<T>?

Accepted Answer

In short: IActionResult can return any result but carries no type information. ActionResult T (2.1+) lets you return either a T (implicitly wrapped in a 200 OK) or any ActionResult ( NotFound() ), and lets OpenAPI tools infer the response type. Minimal APIs use TypedResults and Results Ok T , NotFound for the same purpose.

Question 32

Controllers vs Minimal APIs — which do you choose?

Accepted Answer

In short: Both are fully supported and equally fast. Controllers suit large APIs with many endpoints, teams familiar with MVC, and heavy use of MVC filters and conventions. Minimal APIs suit microservices and small services; they have less ceremony, route groups, endpoint filters, typed results and Native AOT support (controllers aren t AOT-compatible). They can coexist in one app. Organise Minimal APIs with extension methods per feature ( app.MapOrders() ).

Question 33

How does JWT authentication work in ASP.NET Core?

Accepted Answer

In short: The client authenticates with an identity provider (Microsoft Entra ID, Auth0, Duende IdentityServer, or your own login endpoint) and receives a signed JWT access token. It sends Authorization: Bearer token on each request. The JwtBearer handler validates the signature (via the authority s published keys or a configured key), issuer, audience and expiry, then builds HttpContext.User from the claims. [Authorize] with roles, scopes or policies decides access. Tokens are stateless, so revocation needs short expiry plus refresh tokens or a deny list.

Question 34

What is CORS and why do you get CORS errors?

Accepted Answer

In short: Browsers enforce the same-origin policy: a page from https://app.com can t read responses from https://api.com unless the API sends Access-Control-Allow-Origin (and related headers). For non-simple requests, the browser first sends a preflight OPTIONS . CORS errors mean the server didn t allow that origin, method or header, or UseCors is misplaced in the pipeline. Fix server-side with a named policy listing the exact origins. CORS doesn t protect the API from non-browser clients.

Question 35

Why should you use IHttpClientFactory?

Accepted Answer

In short: Creating and disposing HttpClient per request leaves sockets in TIME_WAIT and can exhaust ports. A single long-lived static instance doesn t respect DNS changes. IHttpClientFactory manages pooled handlers with a rotation lifetime, supports named and typed clients, central configuration, and delegating handlers for logging, auth headers and resilience ( AddStandardResilienceHandler ).

Question 36

How do you handle exceptions globally in a Web API?

Accepted Answer

In short: Use UseExceptionHandler() with IExceptionHandler implementations (.NET 8+) and AddProblemDetails() to return RFC 9457 ProblemDetails JSON. Map known exception types to statuses (NotFound → 404, Validation → 400, Conflict → 409), log unexpected ones as errors with a trace ID, and never return stack traces in production. For expected failures, consider the Result pattern instead of exceptions.

Question 37

How do you version an API?

Accepted Answer

In short: Use the Asp.Versioning packages. Strategies: URL segment ( /api/v2/orders , most common and cache-friendly), query string ( ?api-version=2.0 ), header ( api-version ), or media type. Keep old versions running for a deprecation period, report supported/deprecated versions in headers, document each version in OpenAPI, and introduce a new major version only for breaking changes (removing or renaming fields, changing types or semantics).

Question 38

How do you make a POST endpoint idempotent (e.g. payments)?

Accepted Answer

In short: Require an Idempotency-Key header (a client-generated UUID). On the first request, store the key with request hash, status and response in a table (unique constraint on key + client). Concurrent duplicates hit the constraint and wait or get 409. Later repeats return the stored response instead of re-executing. Expire keys after a period. Combine this with DB unique constraints on business identifiers. Stripe s API popularised the pattern.

Question 39

How would you secure a public Web API end to end?

Accepted Answer

In short: HTTPS only with HSTS; OAuth 2.0/OIDC with short-lived JWTs (Authorization Code + PKCE for user clients, client credentials for services); scope/role/policy authorisation plus resource ownership checks (IDOR); input validation and request size limits; parameterised data access; rate limiting and an API gateway/WAF (APIM, Front Door); strict CORS; secrets in Key Vault with managed identities; security headers; dependency scanning; logging and alerting on auth failures; no sensitive data in URLs or logs; and regular penetration tests or OWASP ZAP scans.

Question 40

Your API returns 200 OK with { "success": false, "error": "Not found" } for everything. How would you improve it?

Accepted Answer

In short: Use proper HTTP semantics. Return 404 for not found, 400/422 with ValidationProblemDetails for invalid input, 409 for conflicts, 401/403 for auth, 201 + Location for creates, 204 for deletes, and 500 ProblemDetails with a traceId for unexpected errors. Clients, proxies, caches and monitoring tools rely on status codes; with 200 for everything, error-rate dashboards and retries break. Roll it out in a new API version so existing clients keep working.

Question 41

An endpoint that lists orders is slow (3 s) for a customer with 50,000 orders. What do you do?

Accepted Answer

In short: Profile first (Application Insights, EF logging). Typical fixes: add pagination (keyset for deep lists), project only needed columns with Select into DTOs, use AsNoTracking , add a covering index on (CustomerId, CreatedAt DESC) INCLUDE (Total, Status) , avoid N+1 by not lazy loading, return totals separately or approximately, and cache hot results with HybridCache. If the client needs everything for export, make it an async job (202 Accepted + status URL) that streams a file.

Question 42

A downstream payment API is slow and occasionally down, and your API threads pile up. How do you protect your service?

Accepted Answer

In short: Add timeouts (per attempt and total), retries with exponential backoff and jitter only for idempotent calls (or with idempotency keys), a circuit breaker to fail fast while it s down, and a bulkhead/concurrency limit so payment calls can t consume all resources. AddStandardResilienceHandler() covers most of this. For non-interactive flows, put requests on a queue (Service Bus) and process them asynchronously with status callbacks or webhooks. Return a clear 503/202 to clients and alert on circuit-open events.

Question 43

What are microservices? What are their advantages and disadvantages?

Accepted Answer

In short: Small, independently deployable services, each owning one business capability and its own data, talking over HTTP/gRPC or messages. Advantages: independent deployment and scaling, team autonomy, fault isolation, technology freedom per service. Disadvantages: distributed-system complexity (network failures, latency), no cross-service transactions (eventual consistency, sagas), harder testing and debugging, much more DevOps and monitoring work, data duplication.

Question 44

Monolith vs microservices — which would you choose for a new project?

Accepted Answer

In short: Usually start with a modular monolith : one deployable with strict module boundaries and separate schemas. It is faster to build and easier to operate, and the boundaries let you extract services later. Choose microservices when there is a concrete need — independent scaling, separate teams with separate release cycles, very different technology or reliability needs — and the organisation can afford the DevOps cost.

Question 45

How do microservices communicate?

Accepted Answer

In short: Synchronously with REST over HTTP (public/browser APIs) or gRPC (fast internal calls, contracts in .proto ), usually through an API gateway; asynchronously with messages through a broker (RabbitMQ, Azure Service Bus, Kafka) using queues for commands and topics for events. Prefer async for anything that doesn t need an immediate reply — it decouples availability and absorbs load spikes.

Question 46

What is an API gateway and why use one?

Accepted Answer

In short: A single entry point in front of the services. It routes requests, terminates TLS, validates tokens, applies rate limiting and CORS, can aggregate several calls into one response and hides the internal topology from clients. .NET: YARP or Ocelot; Azure: API Management or Application Gateway. A Backend-for-Frontend is a gateway tailored to one client type.

Question 47

What is "database per service" and how do you query data across services?

Accepted Answer

In short: Each service owns its tables; nobody else reads or writes them directly. Cross-service data is obtained by calling the owner s API, by API composition (gateway/BFF calls several services and merges), or by keeping a local read-only copy updated via events (CQRS read model). Reporting usually goes to a separate data warehouse fed by events or CDC.

Question 48

Explain the saga pattern. Choreography vs orchestration?

Accepted Answer

In short: A saga is a sequence of local transactions across services, each with a compensating action to undo it if a later step fails (charge → refund). Choreography: each service listens for events and publishes the next one — simple, decentralised, but the overall flow is hard to see. Orchestration: a central coordinator (state machine) sends commands and tracks state — explicit and easier to monitor and change, but one more component.

Question 49

What is the outbox pattern and what problem does it solve?

Accepted Answer

In short: The dual-write problem: saving to the database and publishing a message are two systems, so a crash between them leaves them inconsistent. With an outbox, the business data and the message are written in one local transaction (message into an Outbox table); a separate relay publishes outbox rows to the broker and marks them sent. Delivery becomes at-least-once, so consumers must be idempotent (often an Inbox table of processed ids).

Question 50

How do you make a service resilient to failures of its dependencies?

Accepted Answer

In short: Timeouts on every call; retries with exponential backoff and jitter for transient, idempotent operations; circuit breaker to fail fast while a dependency is down; bulkheads to limit concurrency; fallbacks/cached data; async messaging so work is queued rather than lost; health checks and auto-restart. In .NET: AddStandardResilienceHandler() / Polly.

Question 51

What is idempotency and why is it important in distributed systems?

Accepted Answer

In short: An operation is idempotent if doing it twice has the same effect as once. Messages are delivered at least once and clients retry on timeouts, so duplicates are normal. Techniques: idempotency keys on POST (store key + result, return the same result on repeat), natural keys/unique constraints, processed-message tables, set operations instead of increment .

Question 52

How do you handle data consistency without distributed transactions?

Accepted Answer

In short: Accept eventual consistency and design for it: keep each aggregate s invariants inside one service and one local transaction; use sagas with compensations for multi-service workflows; outbox + idempotent consumers for reliable events; reservation patterns (reserve stock, confirm later, expire if unpaid); show pending states in the UI. Two-phase commit (MSDTC) is avoided — it blocks and doesn t work across most cloud services and brokers.

Question 53

Explain CQRS. When is it worth it?

Accepted Answer

In short: Separate models for writes (commands that enforce business rules) and reads (queries/views optimised for screens). Worth it when reads vastly outnumber writes, read shapes differ a lot from the write model, or reads and writes need to scale separately. Overkill for simple CRUD. It doesn t require event sourcing or separate databases; a common form is EF Core for commands and Dapper over views for queries.

Question 54

Design a URL shortener.

Accepted Answer

In short: Requirements: create short link, redirect fast, optional expiry and analytics; e.g. 100M links, read-heavy (100:1). API: POST /links → { code } , GET /{code} → 301/302. Key generation: base62 of a unique id (DB sequence or Snowflake-style id) or a random 7-char code with uniqueness check. Store code → URL in a key-value store or SQL with a unique index; cache hot codes in Redis; CDN/edge for redirects. Analytics: publish click events to a queue/stream and aggregate asynchronously so redirects stay fast. Discuss collisions, abuse (rate limits, malware checks) and custom aliases.

Question 55

An order service calls payment, which calls a slow fraud check; under load the whole site times out. What do you change?

Accepted Answer

In short: Cascading failure from synchronous chains. Add timeouts and a circuit breaker around the fraud call, a bulkhead so slow calls can t use every connection, and a fallback (accept low-value orders, flag for review). Better: make the flow asynchronous — accept the order as Pending, send a message for payment/fraud processing, update status by events, and notify the user. Scale the fraud service, and add tracing to prove where the time goes.

Question 56

Customers are charged twice when the client retries a payment request. Fix it.

Accepted Answer

In short: Make the payment endpoint idempotent: the client sends an Idempotency-Key header (GUID per checkout attempt); the server stores the key with the result in the same transaction as the charge; a repeat request with the same key returns the stored result instead of charging again. Also pass an idempotency key to the payment provider (Stripe and others support this) and use a unique constraint on (OrderId, PaymentAttempt).

Question 57

What is the difference between DELETE, TRUNCATE and DROP?

Accepted Answer

In short: DELETE (DML) removes rows one by one, supports WHERE, fires triggers, logs each row and keeps the identity counter. TRUNCATE (DDL) removes all rows by deallocating pages, is minimally logged and much faster, resets IDENTITY, doesn t fire triggers and isn t allowed on tables referenced by a foreign key. DROP removes the whole table definition, data, indexes and permissions. In SQL Server all three can be rolled back inside an explicit transaction.

Question 58

What are the different types of joins?

Accepted Answer

In short: INNER JOIN (matching rows in both), LEFT/RIGHT OUTER JOIN (all rows from one side + matches, NULLs otherwise), FULL OUTER JOIN (all rows from both), CROSS JOIN (Cartesian product), SELF JOIN (table joined to itself, e.g. employee–manager). T-SQL also has CROSS APPLY/OUTER APPLY for correlated table expressions. Physical algorithms: nested loops, merge, hash.

Question 59

What is the difference between WHERE and HAVING?

Accepted Answer

In short: WHERE filters individual rows before grouping and can t use aggregates. HAVING filters groups after GROUP BY and can use aggregates ( HAVING COUNT(*) 5 ). Put non-aggregate conditions in WHERE for performance — fewer rows get grouped.

Question 60

What is a primary key vs a unique key vs a foreign key?

Accepted Answer

In short: Primary key: uniquely identifies each row, NOT NULL, one per table, creates a clustered index by default. Unique key: enforces uniqueness on other columns, allows one NULL in SQL Server, many per table, creates a non-clustered index by default. Foreign key: a column referencing another table s primary/unique key to enforce referential integrity (with cascade options).

Industry 4.0 / IIoT Software Engineer — Interview Questions