SQL Interview Prep: Joins, Filtering, and Query Optimization Explained

·1 views

A structured SQL revision guide covers key concepts including JOIN types, WHERE vs HAVING clauses, and query optimization techniques for developers targeting mid-to-senior level interviews. The guide distinguishes between INNER, LEFT, RIGHT, FULL OUTER, and CROSS JOINs with concise definitions and row-count examples. It explains that WHERE filters individual rows before aggregation, while HAVING filters grouped results after GROUP BY is applied. Query optimization techniques discussed include resolving the N+1 problem, pagination, selective column fetching, and strategic use of indexes. The guide also highlights the use of EXPLAIN to analyze execution plans and identify bottlenecks such as full table scans or costly sort operations.

Read the full story at DEV Community

This is an AI-generated summary. ShortSingh links to the original source for the complete article.

Discussion (0)

Why bulk date conversion is harder than it looks: the 01/02/2024 problem

A developer essay on DEV Community explores why building a batch date converter is far more complex than simply applying a date-formatting library. The core challenge lies in ambiguity — inputs like 01/02/2024 can mean January 2 or February 1 depending on the data source, making triage essential. The Vue-based tool described handles CSV uploads with a custom parser that accounts for quoted commas, auto-detects which column most likely contains dates, and infers header rows from real data patterns. Rather than relying on JavaScript's native date parsing, the tool explicitly walks through format cases — ISO-like strings, month-name formats, and purely numeric forms — in a deliberate order to avoid locale-dependent errors. The article argues that good UX in data-cleanup tools means modeling ambiguity openly and auto-selecting sensible defaults while still allowing user overrides.

0 comments Read more at DEV Community

ProgrammingDEV Community ·

DEV Community Highlights Top 7 Developer Posts on AI, Security, and Web Tech

The DEV editorial team selected seven standout community posts published between Saturday and Friday of the previous week. Topics ranged from AI's impact on software development roles to Anthropic's new text watermarking feature in Claude and its implications under the EU AI Act. Other featured posts covered agent tool security, runtime authorization gaps in AI agent frameworks, and an experimental LLM-powered Liar's Dice game that builds a behavioral profile of each player. A post on OpenAI's 'Verified Defenders' access promise prompted one developer to design a formal test after repeatedly hitting AI refusals during legitimate defensive security work. The roundup reflects growing community focus on AI governance, agent architecture, and the evolving responsibilities of developers in an AI-augmented workflow.

0 comments Read more at DEV Community

ProgrammingDEV Community ·

MergePDF Processes PDFs Entirely in Your Browser With No Uploads or Sign-Up

A developer has built MergePDF, a free, open-source PDF tool that runs entirely client-side, meaning files never leave the user's browser. The tool supports merging, splitting, rotating, and rearranging PDF pages without requiring a backend server, account, or internet connection for processing. It was created as a privacy-focused alternative to services like iLovePDF, which upload user documents to remote servers. The project uses no database, API routes, analytics, or tracking, storing only theme preferences locally. The source code is publicly available on GitHub and can be deployed to Vercel with no configuration required.

0 comments Read more at DEV Community

ProgrammingDEV Community ·

Three-Phase AI Agent Workflow Cuts Hallucinations by Limiting Context

A structured three-phase workflow — Research, Plan, Implement — has been proposed to reduce AI agent errors caused by bloated context windows during long sessions. The approach, adapted from a HumanLayer talk, involves clearing the context between each phase and saving outputs as markdown files so the agent only needs the final conclusion, not the full history. Parallel subagents handle heavy reading tasks in isolated contexts and return only summaries, keeping the main agent's context usage below 40%. Cheaper models can be assigned to subagents while a more capable model orchestrates the overall task, balancing cost and performance. The author emphasizes that detailed requirements and explicit edge cases are critical, as agents tend to fill information gaps with assumptions that often miss the mark.

0 comments Read more at DEV Community