sql and databases

What is a structured collection of data called?

A database.

What piece of software is typically used to manage a database?

A Database Management System (DBMS).

What are two key functions of a Database Management System (DBMS)?

It allows applications to access and modify data, and it ensures the data structure adheres to the specified data model.

What is a major advantage of databases over Excel in terms of data volume?

Databases can store and process enormous amounts of data, well beyond Excel's capabilities.

What is the most common type of database?

The relational database.

A relational database consists of one or more _.

tables

In a relational database table, what is a record?

A single entry or row in the table.

In a relational database table, what are fields?

The columns of the table, for which each record has values.

What is required for every record in a relational database table for unique identification?

A primary key.

The values in the column designated as the _ must be unique for each record.

primary key

What term describes the situation where the same information is stored multiple times in a database?

Redundancy.

What are two major problems caused by data redundancy?

It costs unnecessary storage space and can lead to inconsistent or contradictory data.

What is the standard solution to avoid redundancy in relational databases?

Using multiple tables and referring to data using primary keys.

When one table refers to the primary key of another table, what is this reference column called?

A foreign key.

A _ establishes a relationship between two tables in a database.

foreign key

What is the definition of how data should be stored in a relational database, including tables, columns, and data types?

The database schema.

What is the industry standard language for processing queries in relational databases?

Structured Query Language (SQL).

What are the three main categories of tasks that SQL can be used for?

Defining the database schema, inserting data, and getting data out of the database.

What is the first step that must be taken before data can be stored in or extracted from a database?

The data model or schema of the database must be defined.

Which SQL command is used to define the schema of a new table?

The CREATE TABLE query.

In a CREATE TABLE statement, what two things must be specified for each column?

The name of the column and its data type (e.g., INTEGER, TEXT).

Which SQL command is used to add new records of data into a database table?

The INSERT INTO query.

In an INSERT INTO statement, how should text values be enclosed?

They should be enclosed by single quotes (accents), for example, 'hello'.

Which SQL command is used to get data out of a database?

The SELECT query.

In a SELECT query, what does the FROM clause specify?

The name of the table from which to extract data.

What is the meaning of the asterisk (*) in the query SELECT * FROM Person;?

It means to select all columns from the table.

Which SQL clause is used to filter the results of a SELECT query based on a condition?

The WHERE clause.

Which SQL clause is used to sort the data returned by a SELECT query?

The ORDER BY clause.

What type of functions, such as COUNT() or AVG(), can be used to compute a value over multiple records?

Aggregate functions.

Which SQL aggregate function computes the number of records?

COUNT(*)

Which SQL aggregate function computes the average of the values in a column?

AVG()

Which SQL aggregate function computes the sum of the values in a column?

SUM()

Which SQL clause is used with aggregate functions to compute values on a 'per group' basis?

The GROUP BY clause.

What is the most powerful SQL operation used to combine rows from multiple related tables into one result table?

The JOIN operation.

What are some drawbacks of using Excel for data management compared to a database?

It has row limits, combining sheets is complex (VLOOKUPs), and it's prone to inconsistent or messy data.

For what tasks does Excel typically shine?

Analyzing and formatting numeric data, creating quick summary tables and charts, and easy, restriction-free editing.

What is the maximum number of rows that Excel can analyze in a dataset?

1,048,576 rows.

In which environment are databases typically used to collect and manage data?

In a 'production' environment.

Databases are often used as the back-end for real-time analytics tools like _.

dashboards

What problem can arise when combining multiple related sheets in Excel?

It may result in a lot of complex VLOOKUP usage.