El Exam

Question 1

Why is 2 not considered the number of attributes present in the address fragment ‘Portland, OR 97212’?

Answer 1

2 is incorrect because a complete address fragment usually includes more than just two pieces of information, such as city, state, and zip code.

Question 2

Why is 3 considered the correct number of attributes present in the address fragment ‘Portland, OR 97212’?

Answer 2

3 is correct because the address fragment ‘Portland, OR 97212’ explicitly contains the city (Portland), state (OR), and zip code (97212), totaling three attributes.

Question 3

Why is ‘birthdate’ not designated as the primary key for the Patient table?

Answer 3

‘birthdate’ is incorrect because multiple patients can share the same birthdate, meaning it cannot uniquely identify each record.

Question 4

Why should ‘patient_id’ be designated as the primary key for the Patient table?

Answer 4

‘patient_id’ should be designated as the primary key because it is an integer column specifically designed to hold a unique identifier for each patient record, ensuring that each record can be uniquely identified.

Question 5

Why is ‘exam_id’ not designated as the foreign key for the Exam table?

Answer 5

‘exam_id’ is incorrect because it is explicitly labeled as PK exam_id in the diagram, indicating it is the primary key of the Exam table itself, not a foreign key referencing another table.

Question 6

Why should ‘patient_id’ be designated as the foreign key for the Exam table?

Answer 6

‘patient_id’ (labeled as patient_number in the column list) should be designated as the foreign key because it is explicitly labeled as FK patient_id in the diagram, indicating its role in linking the Exam table to the Patient table by referencing the patient’s primary key.

Question 7

Why is ‘Integer’ not the data type that represents numbers with fractional values?

Answer 7

‘Integer’ is incorrect because it is used for storing whole numbers without any decimal component.

Question 8

Why is ‘Binary’ not the data type that represents numbers with fractional values?

Answer 8

‘Binary’ is incorrect because it is used for storing binary data, not numerical values with fractions.

Question 9

Why is ‘Decimal’ the data type that represents numbers with fractional values?

Answer 9

‘Decimal’ is correct because the DECIMAL data type is specifically designed to store numbers with a fixed or specified number of decimal places, making it suitable for fractional values.

Question 10

Why is ‘SELECT’ not a DDL command?

Answer 10

‘SELECT’ is incorrect because it is a DML (Data Manipulation Language) command used for retrieving data from a database, not for defining or modifying database structure.

Question 11

Why is ‘UPDATE’ not a DDL command?

Answer 11

‘UPDATE’ is incorrect because it is a DML (Data Manipulation Language) command used for modifying existing data in a table, not for defining or modifying database structure.

Question 12

Why is ‘CREATE INDEX’ a DDL command?

Answer 12

‘CREATE INDEX’ is correct because CREATE INDEX is a DDL (Data Definition Language) command used to define or create new database objects, specifically an index, which is part of the database schema.

Question 13

Why is ‘CREATE VIEW’ not a DML command?

Answer 13

‘CREATE VIEW’ is incorrect because it is a DDL (Data Definition Language) command used for defining new database objects (views), not for manipulating data.

Question 14

Why is ‘CREATE TABLE’ not a DML command?

Answer 14

‘CREATE TABLE’ is incorrect because it is a DDL (Data Definition Language) command used for defining new database objects (tables), not for manipulating data.

Question 15

Why is ‘ALTER INDEX’ not a DML command?

Answer 15

‘ALTER INDEX’ is incorrect because it is a DDL (Data Definition Language) command used for modifying an existing database object (index), not for manipulating data.

Question 16

Why is ‘INSERT’ a DML command?

Answer 16

‘INSERT’ is correct because it is a DML (Data Manipulation Language) command used for manipulating data within schema objects, specifically for adding new rows of data into a table.

Question 17

Why would ‘Those exams would remain in the database.’ not happen if a patient linked to exams is deleted when ON DELETE CASCADE is specified?

Answer 17

‘Those exams would remain in the database.’ is incorrect because the ON DELETE CASCADE foreign key constraint explicitly means that if the parent record (patient) is deleted, the dependent child records (exams) will also be automatically deleted.

Question 18

Why would ‘The Patient ID for those exams would be changed to NULL.’ not happen if a patient linked to exams is deleted when ON DELETE CASCADE is specified?

Answer 18

‘The Patient ID for those exams would be changed to NULL.’ is incorrect because ON DELETE CASCADE results in the deletion of the entire dependent row, not just setting the foreign key to NULL.

Question 19

Why would ‘Those exams would be deleted also.’ happen if a patient linked to exams is deleted when ON DELETE CASCADE is specified?

Answer 19

‘Those exams would be deleted also.’ is correct because the ON DELETE CASCADE clause in the foreign key definition ensures that when a record in the referenced (parent) table (Patient) is deleted, all corresponding records in the referencing (child) table (Exam) are also deleted.

Question 20

Why would ‘Those invoices would remain in the database.’ not be the full outcome if a customer linked to invoices is deleted when ON DELETE RESTRICT is specified?

Answer 20

‘Those invoices would remain in the database.’ is incorrect because while the invoices themselves aren’t deleted, the deletion of the linked customer (patient) would be prevented if those invoices exist.

Question 21

Why would ‘Those invoices would be deleted also.’ not happen if a customer linked to invoices is deleted when ON DELETE RESTRICT is specified?

Answer 21

‘Those invoices would be deleted also.’ is incorrect because ON DELETE RESTRICT specifically prevents the deletion of the parent record if dependent child records exist, rather than deleting the child records.

Question 22

Why would ‘The Customer ID for those invoices would be changed to NULL.’ not happen if a customer linked to invoices is deleted when ON DELETE RESTRICT is specified?

Answer 22

‘The Customer ID for those invoices would be changed to NULL.’ is incorrect because ON DELETE RESTRICT does not modify the foreign key value; its purpose is to prevent the parent deletion.

Question 23

Why would ‘The delete of the Customer would not be allowed.’ happen if a customer linked to invoices is deleted when ON DELETE RESTRICT is specified?

Answer 23

‘The delete of the Customer would not be allowed.’ is correct because the ON DELETE RESTRICT clause prevents the deletion of a record in the parent table (Customer in the explanation, Patient in the table schema) if there are any related records in the child table (Invoice in the explanation, Exam in the table schema) that reference it.

Question 24

Why is ‘It must be refreshed whenever the base table changes.’ true about a materialized view?

Answer 24

A materialized view must be refreshed whenever the base table changes to ensure its data remains consistent and up-to-date with the underlying table.

Question 25

Why is 'The results are stored as a temporary table.' not true about a materialized view?

Answer 25

'The results are stored as a temporary table.' is incorrect because a materialized view stores its results persistently in the database, unlike temporary tables which are transient.

Question 26

Why is 'It is stored.' true about a materialized view?

Answer 26

'It is stored.' is correct because a materialized view creates and maintains a physical copy of the data on disk, unlike a regular view which is a virtual table defined by a query.

Question 27

Why is '`SELECT M.Title, A.Actor FROM Movie M INNER JOIN Actor A ON M.ActorID = A.ActorID`' not a query illustrating an outer join?

Answer 27

This is incorrect because*it explicitly uses* the `INNER JOIN` keyword, which is a type of join that returns only matching rows from both tables, not all rows from one table as in an outer join.

Question 28

Why is '`SELECT M.Title, A.Name FROM Movie M RIGHT JOIN Actor A ON M.ActorID = A.ActorID;`' the correct query illustrating an outer join?

Answer 28

Because a `RIGHT JOIN` is a type of outer join. It returns all rows from the right table (`Actor` in this case) and the matching rows from the left table (`Movie`). If there are actors without matching movies, their details will still appear with NULL for movie-related columns.

Question 29

Why will 'All rows in Table B' not always be included in the result set if Table A is inner joined with Table B?

Answer 29

'All rows in Table B' is incorrect because an I`NNER JOIN `only includes rows from Table B that have a corresponding match in Table A based on the join condition. Rows in Table B without a match in Table A will be excluded.

Question 30

Why will 'Only rows in Tables A and B that share the join condition' always be included in the result set if Table A is inner joined with Table B?

Answer 30

'Only rows in Tables A and B that share the join condition' is correct because an `INNER JOIN` returns only those rows where there is a match in both of the joined tables, based on the specified join condition.

Question 31

Why is `SELECT * FROM Book;` the correct SQL query to return all data from the Book table without directly referencing any column names?

Answer 31

1. `SELECT *` is the standard SQL wildcard that specifies all columns from the table should be included in the result set, thereby returning all data without requiring individual column names to be listed. 2. `FROM Book;` indicates the table from which to retrieve the data.

Question 32

Why would `SELECT * FROM Book WHERE Year = 2020`; not fully meet the requirements of the query?

Answer 32

`SELECT * FROM Book WHERE Year = 2020;` is incorrect because the requirement specifies retrieving only the Title and Genre values, whereas `SELECT *` returns all columns.

Question 33

Why is `SELECT Title, Genre FROM Book WHERE Year = 2020;` the correct SQL query to retrieve the specified data?

Answer 33

1. `SELECT Title, Genre` correctly specifies the exact columns to be retrieved in the required order. 2. `FROM Book` indicates the table to query. 3. `WHERE Year = 2020` precisely filters the results to include only those records where the Year column has a value of 2020.

Question 34

``` SELECT Book.Title, YearSales.TotalSales FROM Book INNER JOIN YearSales ON Book.Year = YearSales.Year; ``` Why would the code not fulfill the 'if available' requirement for all books?

Answer 34

This is incorrect because an INNER JOIN would only return books that have a matching Year in the YearSales table, thereby excluding books for which sales data is not available.

Question 35

``` SELECT Title, TotalSales FROM Book LEFT JOIN YearSales ON Book.Year = YearSales.Year; ``` Why is this code the correct SQL query to display both the Title and the TotalSales (if available) for all books?

Answer 35

1. SELECT Title, TotalSales correctly specifies the columns to be retrieved in the required order. FROM Book sets Book as the starting table. 2. LEFT JOIN YearSales ON Book.Year = YearSales.Year performs a LEFT JOIN, ensuring that all books from the Book table are included in the result set, and if a Year from Book has no match in YearSales, TotalSales will be NULL, thus satisfying the 'if available' condition.

Question 36

Why are '`UPDATE TABLE`' and '`COMMIT TABLE`' not used for changes to a table structure?

Answer 36

'`UPDATE TABLE`' is for data modification, and '`COMMIT TABLE`' is for transaction management, neither of which alters the schema or definition of the table.

Question 37

Why is '`ALTER` TABLE' the correct command for making all changes to a table structure?

Answer 37

'`ALTER TABLE`' is correct because it is the standard SQL DDL (Data Definition Language) command used to modify the structure of an existing table, allowing for operations such as adding, dropping, or modifying columns, or adding/removing constraints.

Question 38

Why is 'SUM' not the SQL aggregate function that gives the number of rows containing non-null values for a given column?

Answer 38

'SUM' is incorrect because it calculates the sum of all numeric values in a column, not the count of rows.

Question 39

Why is '`COUNT`' the SQL aggregate function that gives the number of rows containing non-null values for a given column?

Answer 39

'`COUNT`' is correct because the COUNT() aggregate function is specifically designed to count the number of rows. When given a column name `(COUNT(column_name))`, it counts only the rows where that column has a non-null value. `COUNT(*)` counts all rows.

Question 40

Why is 'Unary many-to-many' not the kind of relationship displayed in the diagram?

Answer 40

'Unary many-to-many' is incorrect because the relationship involves two distinct entities (SALESPERSON and CUSTOMER), making it a binary relationship, not a unary (self-referencing) one.

Question 41

Why is 'Binary one-to-many' the kind of relationship displayed in the entity-relationship diagram?

Answer 41

'Binary one-to-many' is correct because the diagram shows two entities (SALESPERSON and CUSTOMER), which is a binary relationship. The notation indicates that one SALESPERSON can 'Sells to' many CUSTOMERs, while each CUSTOMER 'Buys from' only one SALESPERSON, characteristic of a one-to-many relationship.

Question 42

Why is 'Ternary one-to-one' not the kind of relationship displayed in the diagram?

Answer 42

'Ternary one-to-one' is incorrect because the relationship involves only two entities (SALESPERSON and PRODUCT), making it a binary relationship, not a ternary (involving three entities) one.

Question 43

Why is '`Binary many-to-many`' the kind of relationship displayed in the entity-relationship diagram?

Answer 43

'Binary many-to-many' is correct because the diagram shows two entities (SALESPERSON and PRODUCT), making it a binary relationship. The crow's feet notation on both ends indicates that a SALESPERSON can 'Sells' many PRODUCTs, and a PRODUCT can be 'Sold by' many SALESPERSONs, characteristic of a many-to-many relationship.

Question 44

Why is '`INTEGER`' not a data type that allows for storage of dates?

Answer 44

'`INTEGER`' is incorrect because it stores whole numbers. While dates could technically be stored as integers (as epoch timestamps), it's not the dedicated or most practical data type for date representation in SQL.

Question 45

Why is '`TIMESTAMP/DATE`' a data type that can be designated to allow for storage of dates?

Answer 45

'`TIMESTAMP/DATE`' is correct because both `DATE` and `TIMESTAMP` are standard SQL data types specifically designed for storing temporal values. `DATE` stores only the date, while `TIMESTAMP` stores both date and time, providing appropriate formatting and functions for date/time operations.

Question 46

Why would `DELETE` Customer `WHERE First Name = 'Amy' AND Last Name = 'Lin';` be a problematic way to delete a specific customer?

Answer 46

`DELETE Customer WHERE First Name = 'Amy' AND Last Name = 'Lin'`; is incorrect because it uses non-unique attributes (names) for deletion. If multiple customers named 'Amy Lin' exist, all of them would be deleted, which is likely unintended. Additionally, column names with spaces might require special handling (quoting) depending on the SQL dialect.

Question 47

Why is `DELETE FROM Customer WHERE CustomerID = 101`; the correct query to delete Amy Lin from the Customer table?

Answer 47

`DELETE FROM Customer` specifies the table from which records are to be deleted. `WHERE CustomerID = 101 ` is the crucial WHERE clause that identifies the specific row to be deleted using the unique CustomerID (101 for Amy Lin), ensuring that only her record is removed.

Question 48

How would you write a SQL statement to update Blanca Garcia's phone number to `555-222-1234`?

Answer 48

`UPDATE Customer SET Mobile Phone ='555-222-1234' WHERE ID=104;`

Question 49

Why would SELECT CustomerID, City FROM Customer WHERE City = 'Seattle'; not fully retrieve 'all of the Customers' from Seattle?

Answer 49

SELECT CustomerID, City FROM Customer WHERE City = 'Seattle'; is incorrect because it only retrieves the CustomerID and City columns, whereas the request is to retrieve 'all of the Customers,' implying all columns for those customers.

Question 50

Why is `SELECT * FROM Customer WHERE City = 'Seattle';` the correct SQL statement to retrieve all of the Customers from Seattle?

Answer 50

1. `SELECT *` retrieves all columns for each matching row, fulfilling the 'all of the Customers' part of the request. 2. `FROM Customer` specifies the table to query. 3. `WHERE City = 'Seattle'` precisely filters the records.

Question 51

Why is `SELECT CustomerID, City FROM Customer WHERE City = 'Seattle';` incorrect?

Answer 51

It only retrieves the CustomerID and City columns, whereas the request is to retrieve 'all of the Customers,' implying all columns for those customers.

Question 52

Why is `SELECT * FROM Customer WHERE City = 'Seattle';` the correct SQL statement?

Answer 52

1.` SELECT *` retrieves all columns for each matching row, fulfilling the 'all of the Customers' part of the request. 2. `FROM` Customer specifies the table to query. 3. `WHERE City = 'Seattle' `filters the records to include only those customers whose City column value is 'Seattle'.

Question 53

How would you write a join statement to pull values from Customer, Invoice, and Invoice Item?

Answer 53

`SELECT Customer.CustomerID, Invoice.InvoiceID, Date, Invoice_Item.ProductID FROM Customer JOIN Invoice ON Customer.CustomerID=Invoice.CustomerID JOIN Invoice_Item ON Invoice.InvoiceID=Invoice_Item.InvoiceID;`