General Concepts Flashcards

To delete data from a table, which SQL statement is used?

Answer: TRUNCATE

The `TRUNCATE TABLE` statement is used to quickly remove all rows from a table in SQL. It is a Data Definition Language (DDL) command, which makes it faster and more resource-efficient than `DELETE` for emptying an entire table, as it deallocates the data pages rather than logging individual row deletions. While `DELETE` can also remove data, `TRUNCATE` is specifically for mass deletion of all rows.

To update data in a database, which SQL statement is used?

Answer: UPDATE

The `UPDATE` statement in SQL is specifically designed to modify existing records within a database table. It allows you to change the values of one or more columns for rows that meet a specified condition. This is the standard and correct SQL command for performing data modifications.

How do you select all of the columns from the "Persons" table in SQL?

Answer: SELECT * FROM Persons

In SQL, the asterisk (`*`) serves as a wildcard character, signifying 'all columns.' Therefore, `SELECT * FROM Persons` is the correct syntax to retrieve every column and all rows of data from the table named 'Persons.' This command provides a complete view of the table's contents.

How do you get a column called "FirstName" from a table called "Persons" in SQL?

Answer: SELECT FirstName FROM Persons

To retrieve specific columns from a table in SQL, you list the desired column names directly after the `SELECT` keyword. `SELECT FirstName FROM Persons` explicitly requests only the data from the 'FirstName' column within the 'Persons' table. This allows for targeted data extraction.

To extract data from a database, which SQL statement is used?

Answer: SELECT

The `SELECT` statement is the fundamental SQL command used to retrieve or extract data from one or more tables in a database. It allows users to specify which columns and rows they want to query, making it the primary tool for data retrieval operations. Other options like `EXTRACT` or `GET` are not standard SQL commands for this purpose.

(T/F) When AUTOCOMMIT is enabled, updates are made at the end of each SQL query automatically.

Answer: B) True

When `AUTOCOMMIT` is enabled in a database system, every SQL data manipulation language (DML) statement, such as `INSERT`, `UPDATE`, or `DELETE`, is automatically treated as a complete transaction. This means that changes made by each query are immediately and permanently saved to the database without requiring an explicit `COMMIT` command. Therefore, updates are indeed made automatically at the end of each SQL query.

The AVG SQL function returns the following

Answer: average in the values in a group

The `AVG()` aggregate function in SQL is used to calculate the average (arithmetic mean) of a set of values in a specified column. When combined with a `GROUP BY` clause, it computes the average for each distinct group of rows. It does not return the sum or maximum value.

What does a TRUNCATE TABLE do?

Answer: deletes all rows from a table

The `TRUNCATE TABLE` statement is a Data Definition Language (DDL) command that removes all rows from a table, effectively emptying it. Unlike `DELETE`, it does not log individual row deletions, making it significantly faster and more efficient for large tables, but it also means the operation cannot typically be rolled back. It does not delete the table itself, only its contents.

The primary - foreign key relations are utilized to

Answer: cross-reference database tables

Primary and foreign keys are essential components for establishing relationships between tables in a relational database. A foreign key in one table references the primary key in another table, creating a logical link that allows data to be cross-referenced and ensures referential integrity across the database schema. This mechanism is crucial for maintaining consistent and related data.

Which of the following statements about system information in an RDBMS is correct?

Answer: All of the above.

Relational Database Management Systems (RDBMS) store metadata, which is information about the database structure (like table names, column types, constraints), in special system-created tables, often called a data dictionary or catalog. This system information is typically read-only for regular users but can be accessed and queried using standard SQL commands. Therefore, all the statements provided are correct regarding system information in an RDBMS.

Which of the following statements regarding embedded SQL is correct?

Answer: Hard—coded SQL statements in a program language such as Java.

Embedded SQL refers to SQL statements that are directly integrated or 'hard-coded' within a host programming language, such as Java, C, or Python. These SQL commands are processed by a precompiler or interpreter along with the host language code, allowing applications to interact with databases by executing predefined queries. It contrasts with dynamic SQL, where queries are constructed at runtime.

Table columns are also referred to as

Answer: Fields

In the context of database tables, columns are frequently referred to as fields. Each field represents a specific attribute or piece of information that is stored for every record (row) in the table. For example, in a 'Persons' table, 'FirstName' and 'Age' would be considered fields or columns.

Which of the SQL commands below deletes all rows in the SalesData table?

Answer: DELETE FROM SalesData

The standard SQL command to delete all rows from a table is `DELETE FROM TableName`. This statement removes all records from the specified table. While it can also be used with a `WHERE` clause to delete specific rows, omitting the `WHERE` clause results in the deletion of all entries.

Which of the following statements regarding the CASE SQL statement is correct?

Answer: A way to establish an IF-THEN-ELSE in SQL

The `CASE` statement in SQL provides conditional logic, functioning similarly to an `IF-THEN-ELSE` structure found in many programming languages. It allows you to define different outputs or actions based on various conditions, making queries more dynamic and enabling complex conditional expressions within SQL statements. It is not used for loops or data definition.

This form of database, which may be used to mine data for business patterns, does not require SQL queries and instead allows users to ask questions like "How many Aptivas have been sold in Nebraska this year?"

Answer: multidimensional database

A multidimensional database (MDDB), often used in Online Analytical Processing (OLAP) and data warehousing, is designed for fast analysis of data from multiple perspectives. It stores data in a cube-like structure, enabling intuitive, non-SQL queries that resemble natural language questions for business intelligence, making it ideal for pattern mining without complex SQL.

IBM's cross-platform relational database management system is part of this family of products.

Answer: DB2

DB2 is a family of relational database management system (RDBMS) products developed by IBM. It is designed to efficiently store, analyze, and retrieve data, and is widely used across various operating systems and platforms for enterprise-level applications. It is IBM's flagship database product.

Which of the following is a group of formally-described tables from which data can be accessed or reassembled in a variety of ways without having to restructure the database tables?

Answer: relational database

A relational database organizes data into one or more formally-described tables (relations) with predefined relationships between them. This structure allows data to be accessed, combined, and reassembled in a multitude of flexible ways using SQL queries, without requiring changes to the underlying table structures. This flexibility is a core principle of relational databases.

This is the process of organizing a database into tables in such a way that the database's results are always clear.

Answer: normalization

Normalization is a systematic process in database design that organizes the columns and tables of a relational database to minimize data redundancy and improve data integrity. It involves breaking down large tables into smaller, related tables and defining relationships between them, ensuring data consistency and clarity by reducing anomalies. This process makes the database structure logical and efficient.

This company was founded in 1977 with the goal of proving that the popular belief that relationship databases were not commercially viable was incorrect.

Answer: Oracle

Oracle Corporation was founded in 1977 by Larry Ellison, Bob Miner, and Ed Oates. Their primary goal was to develop and market relational database management systems, challenging the popular belief at the time that relational databases were not commercially viable. Oracle went on to become one of the world's largest and most influential database companies.

This phrase refers to the process of forecasting, or simply identifying patterns in data that can lead to future predictions.

Answer: data mining

Data mining is the process of discovering patterns, anomalies, and correlations within large datasets to predict future outcomes or identify trends. It involves using various analytical techniques and algorithms to extract valuable insights and build predictive models from existing data, effectively forecasting or identifying patterns that lead to predictions.