SQL Basics

Cards (24)

• 1
  Front

  What does the SELECT statement do in SQL?

  Back

  It retrieves data from one or more tables, specifying which columns to return in the result set.

• 2
  Front

  What is the purpose of SELECT DISTINCT?

  Back

  It removes duplicate rows from the result set, returning only unique values for the specified columns.

• 3
  Front

  What does the WHERE clause do in a SQL query?

  Back

  It filters rows returned by a query, including only rows that satisfy the specified condition.

• 4
  Front

  What is the difference between WHERE and HAVING in SQL?

  Back

  WHERE filters rows before grouping; HAVING filters groups after GROUP BY has been applied.

• 5
  Front

  What does an INNER JOIN return?

  Back

  Only the rows where there is a matching value in both joined tables.

• 6
  Front

  What does a LEFT JOIN (LEFT OUTER JOIN) return?

  Back

  All rows from the left table and matching rows from the right table; non-matching right-table columns return NULL.

• 7
  Front

  What does a RIGHT JOIN return?

  Back

  All rows from the right table and matching rows from the left table; non-matching left-table columns return NULL.

• 8
  Front

  What does a FULL OUTER JOIN return?

  Back

  All rows from both tables, with NULLs where there is no match on either side.

• 9
  Front

  What does a CROSS JOIN produce?

  Back

  The Cartesian product of two tables — every row of the first table combined with every row of the second table.

• 10
  Front

  What is the purpose of GROUP BY in SQL?

  Back

  It groups rows that share the same values in specified columns so aggregate functions can be applied to each group.

• 11
  Front

  What rule must columns follow when GROUP BY is used?

  Back

  Every column in the SELECT list must either appear in the GROUP BY clause or be inside an aggregate function.

• 12
  Front

  What does the COUNT() aggregate function return?

  Back

  The number of rows (or non-NULL values for a specific column) in a group or result set.

• 13
  Front

  What does the SUM() aggregate function do?

  Back

  It returns the total sum of all non-NULL numeric values in the specified column.

• 14
  Front

  What is the difference between AVG() and SUM()?

  Back

  SUM() returns the total of all values; AVG() returns the arithmetic mean (SUM divided by COUNT of non-NULL values).

• 15
  Front

  What do the MIN() and MAX() aggregate functions return?

  Back

  MIN() returns the smallest value; MAX() returns the largest value in the specified column.

• 16
  Front

  What is a subquery in SQL?

  Back

  A query nested inside another query, used in SELECT, FROM, or WHERE clauses to provide intermediate results.

• 17
  Front

  What is a correlated subquery?

  Back

  A subquery that references a column from the outer query, causing it to be evaluated once per row of the outer query.

• 18
  Front

  What is the difference between IN and EXISTS when using subqueries?

  Back

  IN checks whether a value matches any value in a list returned by the subquery; EXISTS checks whether the subquery returns at least one row.

• 19
  Front

  What is a SQL index?

  Back

  A database object that improves the speed of data retrieval by creating a sorted data structure on one or more columns.

• 20
  Front

  What is the trade-off of adding indexes to a table?

  Back

  Indexes speed up reads but slow down INSERT, UPDATE, and DELETE operations and consume additional storage space.

• 21
  Front

  What is database normalization?

  Back

  The process of organizing a database to reduce data redundancy and improve data integrity by dividing data into related tables.

• 22
  Front

  What are the requirements for First Normal Form (1NF)?

  Back

  Each column must contain atomic (indivisible) values, each column must hold values of a single type, and each row must be unique.

• 23
  Front

  What additional requirement does Second Normal Form (2NF) add over 1NF?

  Back

  The table must be in 1NF, and every non-key attribute must be fully functionally dependent on the entire primary key (no partial dependencies).

• 24
  Front

  What additional requirement does Third Normal Form (3NF) add over 2NF?

  Back

  The table must be in 2NF, and no non-key attribute should depend on another non-key attribute (no transitive dependencies).