4. SQL and DML Flashcards
What is a relational table?
A relational table has a unique name, is organized in rows and columns, with each row representing a unique instance of an entity type, each column having a unique name with values from the same domain, and each cell holding one value.
What are the two main operations in SQL for querying a table?
Selection retrieves specific rows using a WHERE clause, while Projection retrieves specific columns using the SELECT list.
What does the DISTINCT keyword do in SQL?
DISTINCT eliminates duplicate rows from the query result.
What are group functions in SQL?
Group functions, such as COUNT, MAX, AVG, MIN, and SUM, aggregate rows and return a single result.
What is the purpose of the GROUP BY clause?
GROUP BY groups rows based on specified columns so that aggregate functions can be applied to each group.
What is a column alias and why is it used?
A column alias renames a column in the result set, improving clarity especially when expressions are used.
How can expressions be used in SQL queries?
Expressions combine mathematical operators and functions to compute derived values in the SELECT clause.
How are NULL values represented and handled in SQL?
NULL represents missing or unknown data; functions like IFNULL can substitute values, and conditions use IS NULL or IS NOT NULL to check for NULLs.
How do you order query results in SQL?
The ORDER BY clause sorts the results by one or more columns, with options for ascending or descending order.
What is a join in SQL?
A join combines rows from two or more tables based on related columns between them.
What is an inner join?
An inner join returns rows that meet the join condition from both tables.
What is a natural join?
A natural join returns rows with matching values in columns that share the same name in both tables.
What is an outer join?
An outer join returns rows even when there is no matching row in the joined table, with missing values filled as NULL.
What is a self join?
A self join is when a table is joined with itself using aliases to distinguish the different instances.
What is SQL Data Manipulation Language (DML) used for?
SQL DML is used to query and modify data in a database, including operations like SELECT, INSERT, UPDATE, and DELETE.
What are the main clauses of a SELECT statement?
The main clauses are SELECT, FROM, WHERE, GROUP BY, HAVING, and ORDER BY.
What is the typical processing order of a SELECT statement?
The processing order is FROM, WHERE, GROUP BY, HAVING, SELECT, then ORDER BY.
How do Boolean operators function in SQL queries?
Boolean operators such as AND, OR, and NOT combine conditions in the WHERE clause, with NOT having the highest precedence, followed by AND and then OR, unless parentheses override the order.
What is the difference between the WHERE and HAVING clauses?
WHERE filters rows before grouping, while HAVING filters groups after aggregation.
What are common comparison operators in SQL?
Common operators include =, >, >=, <, <=, <> (or !=), and are used to compare values in conditions.
