WGU D427 Objective Assessment final exam and

• | P a g e

WGU D427 Objective Assessment final exam and practice exam complete 300 questions covering data modeling, SQL queries, database design, normalization, and relational database management systems.

What are the basic components of a table in a relational database? - ANSWER-- A table has a name, a fixed sequence of columns, and a varying set of rows. - A column has a name and a data type. - A row is an unnamed sequence of values. Each value corresponds to a column and belongs to the column's data type. - A cell is a single column of a single row. - A table must have at least one column but any number of rows. A table without rows is called an empty table.

Refer to the Employee table. Identify a column name, a cell value, and the components of a column. \nEmployee\nID Name Salary\n2538 Lisa Ellison 45000\n5384 Sam Snead 30500\n6381 Maria Rodriguez 92300 - ANSWER-- Column name: Salary - Cell value: 30500 - Components of a column: Name and data type 1 / 4

• | P a g e

What are the four main rules that tables must obey in a relational database? - ANSWER-1. Exactly one value per cell. A cell may not contain multiple values. Unknown data is represented with a special NULL value. 2. No duplicate column names. Duplicate column names are allowed in different tables, but not in the same table. 3. No duplicate rows. No two rows may have identical values in all columns. 4. No row order. Rows are not ordered. The organization of rows on a storage device, such as a disk drive, never affects query results.

What is the principle of data independence? - ANSWER-The principle of data independence states that the result of a database query is not affected by the physical organization of data on storage devices. This allows database administrators to improve query performance by changing the organization of data on storage devices without affecting query results.

How do most databases handle tables with duplicate rows, and why might they allow duplicates temporarily? - ANSWER-Most databases allow tables with duplicate rows in violation of the no duplicate rows rule, but these tables are usually temporary. For example, external data with duplicate rows might be loaded into a temporary table, and the duplicate rows are normally eliminated as data is moved to a permanent table. 2 / 4

• | P a g e

Write a SQL statement to create an Employee table with columns ID (integer), Name (variable-length string with max 60 characters), BirthDate (date), and Salary (decimal with 7 digits and 2 decimal places). - ANSWER-CREATE TABLE Employee

• ID INT, Name VARCHAR(60), BirthDate DATE, Salary

DECIMAL(7,2) );

Write a SQL statement to delete the Employee table. - ANSWER-DROP TABLE Employee;

True or False: The statement `CREATE TABLE Employee (ID

INT, Name VARCHAR(60), BirthDate DATE, Salary DECIMAL(7,2));` creates an Employee table with 4 columns. - ANSWER-True

True or False: The Name column in the Employee table contains

character string values. - ANSWER-True

True or False: The statement `DROP TABLE Employee;` deletes

the Employee table. - ANSWER-True

• / 4

• | P a g e

True or False: The DROP TABLE statement does not delete the

table unless the table is empty. - ANSWER-False. The table is deleted regardless of how many rows are in the table.

Write a SQL statement to create a Product table with columns:

ID (integer), Name (variable-length string with max 40 characters), ProductType (variable-length string with max 3 characters), OriginDate (date), and Weight (decimal with 6 significant digits and 1 digit after the decimal point). - ANSWER-CREATE TABLE Product ( ID INT, Name VARCHAR(40), ProductType VARCHAR(3), OriginDate DATE, Weight DECIMAL(6,1) );

What does the ALTER TABLE statement do, and what are the three main clauses used with it? - ANSWER-The ALTER TABLE statement adds, deletes, or modifies columns in an existing table. The three main clauses are: 1. **ADD:** Adds a column. ALTER TABLE TableName ADD ColumnName

DataType; 2. **CHANGE:** Modifies a column. ALTER

TABLE TableName CHANGE CurrentColumnName

NewColumnName NewDataType; 3. **DROP:** Deletes a

column. ALTER TABLE TableName DROP ColumnName;

Write a SQL statement to add a column called Description (variable-length string with max 50 characters) to the

• / 4