MySQL Chapter 3 (1)

What is the purpose of the SELECT statement in SQL?

To retrieve data from one or more tables.

What clauses are included in the basic syntax of a SELECT statement?

SELECT, FROM, WHERE, ORDER BY, LIMIT.

What does the DISTINCT keyword do in a SELECT statement?

It eliminates duplicate rows from the results.

What is the use of the CONCAT function?

To concatenate two or more strings together.

In SQL, what do comparison operators do?

They compare two expressions, returning true or false.

What does the statement SELECT * FROM table_name do?

It selects all columns and rows from the specified table.

How would you use the LIKE operator in a WHERE clause?

To find records that match a specified pattern.

What is the purpose of using parentheses in arithmetic expressions in SQL?

To control the order of operations.

What does the IN operator check for in a WHERE clause?

It checks if a value matches any value in a list or subquery.

What is the SQL command to retrieve all invoices between two dates?

SELECT invoice_number, invoice_date, invoice_total FROM invoices WHERE invoice_date BETWEEN 'start_date' AND 'end_date'.

What does the ORDER BY clause do in a SELECT statement?

It sorts the result set based on one or more columns.

What is the syntax of the ROUND function in SQL?

ROUND(number[, number_of_decimal_places]).

What is the purpose of using LIMIT in a SELECT statement?

To restrict the number of rows returned by the query.

Explain the use of the IS NULL clause in SQL.

It checks if a column value is NULL.

What is the SQL statement to concatenate city and state with a comma in between?

SELECT CONCAT(vendor_city, ', ', vendor_state) AS address FROM vendors.

How does the BETWEEN operator function in a WHERE clause?

It checks if a value falls within a range defined by two endpoints.

What does the ORDER BY clause do when applied to multiple columns?

Sorts the result set by the first column, followed by the second column, and so on.

What signifies the order of precedence for arithmetic operators in SQL?

The order in which operations are performed, e.g., multiplication and division before addition.

What does the acronym SQL stand for?

Structured Query Language.

How do you include wildcards in a LIKE clause?

By using '%' for zero or more characters and '_' for a single character.

What is the purpose of the GROUP BY clause in SQL?

To group rows that have the same values in specified columns into summary rows.

What does the COUNT function do in SQL?

It returns the number of rows that match a specified criterion.

How do you filter aggregated data in SQL?

By using the HAVING clause.

What is the role of an alias in SQL?

To provide a temporary name for a table or column.

What does the SQL command DELETE do?

It removes rows from a table based on a specified condition.

What is the functionality of the UPDATE statement in SQL?

To modify existing records in a table.

What is a primary key in SQL?

A unique identifier for a row within a table.

What does normalization refer to in database design?

The process of organizing a database to reduce redundancy and improve data integrity.

What is an outer join in SQL?

A type of join that returns all rows from one table and the matched rows from another table.

What is the purpose of the CREATE TABLE statement in SQL?

To define a new table and its columns in the database.

What is the role of the ALTER TABLE statement in SQL?

To modify an existing table structure, such as adding or deleting columns.

What does the SQL command TRUNCATE do?

It removes all rows from a table without logging individual row deletions.

What is the purpose of the JOIN clause in SQL?

To combine rows from two or more tables based on a related column.

What is a foreign key in SQL?

A field that links to the primary key of another table, enforcing referential integrity.

What does the GROUP_CONCAT function do in SQL?

It concatenates values from multiple rows into a single string.

What is the purpose of the SET clause in an UPDATE statement?

To specify the column(s) to be updated and their new values.

What purpose does a subquery serve in SQL?

To perform a query within another query, allowing for complex queries.

What does the SQL command DROP TABLE do?

It deletes a table and its data from the database.

What is an index in SQL?

A database object that improves the speed of data retrieval operations.

What are aggregate functions in SQL?

Functions that perform a calculation on a set of values and return a single value.

What is a database in SQL?

A structured collection of data stored electronically.

What is the purpose of the SELECT DISTINCT statement?

To retrieve unique values from a specified column or columns.

What does the SQL statement INSERT do?

It adds new rows to a table.

What is the difference between INNER JOIN and OUTER JOIN?

INNER JOIN returns only matching rows, while OUTER JOIN returns all rows from one table and matched rows from another.

What is a stored procedure in SQL?

A precompiled collection of one or more SQL statements that can be executed as a single unit.

What is the purpose of the ORDER BY clause?

To sort the result set in ascending or descending order.

What does the MAX function do in SQL?

It returns the maximum value from a specified column.

What is data integrity in the context of SQL databases?

The accuracy and consistency of data within a database.

What is the purpose of indexes in SQL?

To speed up the retrieval of rows from a database table.

What is normalization, and why is it important?

Normalization is the process of organizing a database to minimize redundancy and dependency.

What is the purpose of the HAVING clause in SQL?

To filter records after aggregate functions have been applied.

How do you perform a cross join in SQL?

By using the CROSS JOIN keyword between two tables to get the Cartesian product.

What is a composite key in SQL?

A combination of two or more columns in a table that can uniquely identify a row.

What does the SQL statement CREATE VIEW do?

It creates a virtual table based on the result of a SELECT query.

What is data normalization?

The process of organizing data to minimize redundancy and ensure data integrity.

What does the ROLLUP operator do in SQL?

It generates subtotals and grand totals for aggregated data.

What is a unique constraint in SQL?

A rule that ensures all values in a column are different.

What does the SQL command EXISTS do?

It checks if a subquery returns any rows, returning true or false.

What is the purpose of the ALTER VIEW statement in SQL?

To modify an existing view definition.

What is a trigger in SQL?

A set of instructions that automatically executes in response to certain events on a table.