SQL Query Clauses and Database Operations: Key Concepts for Beginners

What does the SELECT clause do in SQL?

Determines which columns appear in the result set.

What does the FROM clause do in SQL?

Identifies the table(s) used in the query.

What does the WHERE clause do?

Filters rows based on a condition.

What does the GROUP BY clause do?

Groups rows to be used with aggregate functions.

What does the HAVING clause do?

Filters groups after aggregation.

What does ORDER BY do?

Sorts results in ascending or descending order.

What is the rule of precedence in SQL?

Arithmetic → Comparison → NOT → AND → OR.

What does DISTINCT do in SQL?

Removes duplicate rows from a result.

What does BETWEEN do?

Tests whether a value is within a range (inclusive).

What does IN do?

Tests whether a value matches any value in a list.

What does LIKE do?

Performs pattern matching with wildcards.

What wildcard does LIKE use for multiple characters?

%

What wildcard does LIKE use for one character?

_

What does IS NULL check?

Whether a value is NULL.

What does SUM() do?

Adds numeric values in a group.

What does AVG() do?

Computes average of numeric values.

What does COUNT(*) do?

Counts all rows.

What does MIN() do?

Returns smallest value in a set.

What does MAX() do?

Returns largest value in a set.

What is an equijoin?

A join where tables are linked using equality =.

What is a natural join?

A join matching columns with the same name.

What is a self-join?

A table joined with itself.

What is a cross join?

Produces Cartesian product of two tables.

What is a left outer join?

Shows all rows from left table plus matches from right.

What is a right outer join?

Shows all rows from right table plus matches from left.

What is a view?

A virtual table defined by a query.

What does CREATE VIEW do?

Creates a view based on a SELECT query.

What does CREATE TABLE do?

Defines a new table structure.

What does ALTER TABLE do?

Modifies an existing table structure.

What does DROP TABLE do?

Removes a table and its data.

What does CREATE INDEX do?

Creates an index to speed up searching.

What does INSERT do?

Adds new rows to a table.

What does UPDATE do?

Modifies existing rows.

What does DELETE do?

Deletes rows.

What does COMMIT do?

Saves all database changes permanently.

What does ROLLBACK do?

Reverts database changes since last commit.

What is a recursive query?

A query that refers to itself to handle hierarchical data.

What is relational algebra?

A formal language defining operations on relations (tables).

What does UNION do?

Combines results of two queries and removes duplicates.

What does INTERSECT do?

Returns rows common to both queries.

What does EXISTS test?

Whether a subquery returns any rows.

What does a subquery do?

Runs a query inside another query.

What is a transaction?

A sequence of operations treated as a single unit.

What does ACID stand for?

Atomicity, Consistency, Isolation, Durability.

Define Atomicity.

All operations succeed or none do (all-or-nothing).

Define Consistency.

A transaction must leave the database in a valid state.

Define Isolation.

Transactions cannot interfere with each other.

Define Durability.

Committed changes persist even after failure.

What is a schedule?

An ordering of operations from multiple transactions.

What is a serial schedule?

Transactions executed one at a time.

What is a serializable schedule?

Concurrent execution that matches the result of a serial schedule.

What is a dirty read?

Reading uncommitted data.

What is a nonrepeatable read?

A value changes between two reads in the same transaction.

What is a phantom read?

New rows appear between two reads.

What is two-phase locking?

Growing phase (acquire locks) and shrinking phase (release locks).

What is strict two-phase locking?

Holds write locks until commit.

What is deadlock?

Two transactions wait for each other indefinitely.

What is recovery?

Restoring database consistency after failure.

What does a checkpoint do?

Saves database state to speed up recovery.

What is a DBMS architecture layer?

Tools → Query Processor → Storage Engine → File System.

What is a distributed database?

A database stored on multiple networked nodes.

What is replication?

Storing copies of data on multiple machines.

What is primary-secondary replication?

Writes go to primary; replicas receive updates later.

What is eventual consistency?

Replicas become consistent over time, not immediately.

What is a data warehouse?

A separate analytic database with historical, integrated data.

What is a data mart?

A smaller warehouse for a specific department.

What is ETL?

Extract, Transform, Load — prepares data for a warehouse.

What is a multi-tier architecture?

Separation of web browser, web server, app server, DB server.

What is sharding?

Splitting data across multiple machines.

What is a simple data type?

Integer, decimal, character, date, binary.

What is a complex data type?

Arrays, JSON, XML, spatial, object types.

What is a collection type?

A type storing multiple values (set, array, list).

What is a document type?

Stores semi-structured data like JSON or XML.

What is a spatial type?

Represents geometric shapes: POINT, LINESTRING, POLYGON.

What is an object type?

A type supporting attributes, methods, inheritance.

What is embedded SQL?

SQL statements written inside another programming language.

What is procedural SQL?

SQL with loops, variables, and control flow (stored procedures).

What is an API in DB programming?

A library allowing programs to interact with databases.

What is ODBC/JDBC?

Database APIs for C#/Java to connect and execute SQL.

What is a stored procedure?

A saved SQL program executed with CALL.

What is a trigger?

Code that runs automatically on INSERT/UPDATE/DELETE.

What are the 5 V's of Big Data?

Volume, Velocity, Variety, Veracity, Value.

What is NoSQL?

Non-relational databases designed for big data.

What is a key-value database?

Stores data as key → value pairs.

What is a wide column database?

Uses tables with flexible column families (HBase, Cassandra).

What is a document database?

Stores JSON-like documents (MongoDB).

What is a graph database?

Stores nodes, edges, and properties (Neo4j).

What is MongoDB?

A NoSQL document database using BSON and collections.

What does insertOne() do in MongoDB?

Inserts a single document.

What does insertMany() do?

Inserts multiple documents.

What does find() do?

Retrieves all matching documents.

What does updateOne() do?

Updates the first matching document.

What does deleteOne() do?

Deletes the first matching document.

What does show dbs do in Mongo Shell?

Lists all databases.

What is a shard in NoSQL?

A partition of data across servers.