Download SQL Database Management: Mastering Table Relationships and Queries and more Exams Database Programming in PDF only on Docsity!
lOMoARcPSD|43502630lOMoARcPSD|
D427 Practice Test 1 Data Management - Applications with
Answer Key
D427 Practice Test
- Seattle, WA 98111 USA How many attributes are present in the address fragment? a. 1 b. 2 c. 3 d. 4
- The Book table has the following columns: genre – varchar (20) pages – integer author_id – char(3) isbn _ number – varchar (20) Which column should be designated at the primary key for the Book table? a. genre b. pages c. author_id d. isbn_number
- The Book table has the following columns: genre – varchar (20) pages – integer author_id – char(3) isbn_number – varchar(20) Which column should be designated at the foreign key for the Book table? a. genre b. pages
c. author_id d. isbn_number
- Which data type represents numbers with fractional values: a. Integer b. Decimal c. Character d. Binary
Customer ID (PK)
Customer Last Name
Customer First
Name Street
Address
City
Stat
Invoice ID
(PK) Date
Customer ID (FK)
Customer ID (PK)
Customer Last Name
Customer First
Name Street
Address
City
Stat
e Zip
Invoice ID
(PK) Date
Customer ID (FK)
CREATE TABLE Invoice ( invoice_id INT NOT NULL AUTO_INCREMENT, date DATE NOT NULL, customer_id INT NOT NULL, PRIMARY KEY (invoice_id), FOREIGN KEY (customer_id) REFERENCES Customer (customer_id) ON DELETE RESTRICT ); Looking at the Customer and Invoice tables and the CREATE TABLE for the Invoice table with foreign key reference statement above, what would happen to invoices in the Invoice table that are linked to a customer if that customer is deleted. a. Those invoices would remain in the database. b. Those invoices would be deleted also. c. The Customer ID for those invoices would be changed to NULL. d. The delete of the Customer would not be allowed.
Customer ID (PK)
Customer Last Name
Customer First
Name Street
Address
City
Stat
e Zip
Invoice ID
(PK) Date
Customer ID (FK)
FOREIGN KEY (customer_id) REFERENCES Customer (customer_id) ON DELETE SET TO NULL ); Looking at the Customer and Invoice tables and the CREATE TABLE for the Invoice table with foreign key reference statement above, what would happen to invoices in the Invoice table that are linked to a customer if that customer is deleted. e. Those invoices would remain in the database. f. Those invoices would be deleted also. g. The Customer ID for those invoices would be changed to NULL. h. The delete of the Customer would not be allowed.
Which of the following are true about materialized view (Choose 2)? a. It is a base table. b. It is stored. c. It must be refreshed whenever the base table changes. d. The results are stored as a temporary table.
The Customer table will have the following columns: Customer ID—positive integer First Name—variable-length string with up to 50 characters Middle Initial—fixed-length string with 1 character
A new column must be added to the Automobile table: Column name: Safety Rating Data type: decimal (3,1) Write a SQL statement to add the Safety Rating column to the Automobile table.
The Book table has the following columns: ID—integer, primary key Title—variable-length string Genre—variable- length string Year— integer Write a SQL statement to create a view named MyBooks that contains the Title, Genre, and Year columns for all movies. Ensure your result set returns the columns in the order indicated.
A database has a view named Book View. Write a SQL statement to delete the view named Book View from the database.
The Book table has the following columns: ID—integer, primary key Title—variable-length string Genre—variable- length string Year— integer Write a SQL statement to modify the Book table to make the ID column the primary key.
The Year Sales table has the following columns: Year—integer Total Sales—bigint unsigned Releases—integer Write a SQL statement to designate the Year column in the Book table as a foreign key to the Year column in the Year Sales table.
The Book table has the following columns: ID—integer, primary key Title—variable-length string Genre—variable- length string Year— integer Write a SQL statement to create an index named idx_year on the Year column of the Book table.
The Book table has the following columns: ID—integer, primary key, auto _increment Title—variable-length string Genre—variable-length string Year—integer The following data needs to be added to the Book table: Title Genre Year The Joy Luck Club Fiction 1989 Write a SQL statement to insert the indicated data into the Book table.
The Book table has the following columns: ID—integer, primary key, auto_increment Title—variable- length string Genre—variable- length string Year—integer Write a SQL statement to delete the row with the ID value of 3 from the Book table.
The Book table has the following columns: ID—integer, primary key, auto_increment Title—variable- length string Genre—variable- length string Year—integer Write a SQL statement to update the Year value to be 2022 for all books with a Year value of 2020.
- Which query illustrates performing an outer join of the Movie table with a different table? a. SELECT B.Title, A.Author FROM Book B, Author A WHERE B.AuthorID = A.AuthorID; b. SELECT B.Title, A.Author FROM Book B LEFT JOIN Book MB ON B.ID = MB.IF, Author A c. SELECT Book.Title, A.Author FROM Book
Assume there are two tables, A and B. Which rows will always be included in the result set if Table A is inner joined with Table B? a. Only rows in Tables A and B that share the join condition b. All rows in Table B c. All rows in Table A d. Only rows in Tables A and B that do not share the join condition.
The database contains a table named Book. Write a SQL query to return all data from the Book table without directly referencing any column names.
The Book table has the following columns: ID—integer, primary key, auto_increment Title—variable- length string Genre—variable- length string Year—integer Write a SQL query to retrieve the Title and Genre values for all records in the Book table with a Year value of 2020. Ensure your result set returns the columns in the order indicated.
The Book table has the following columns: ID—integer, primary key, auto_increment Title—variable- length string Genre—variable- length string Year—integer Write a SQL query to display all Title values in alphabetical order A–Z.
Continued below
The Book table has the following columns: ID—integer, primary key, auto_increment Title—variable- length string Genre—variable- length string Year—integer Write a SQL query to return how many books have a Year value of 2019.
D427 Practice Test ANSWER KEY
- Seattle, WA 98111 USA How many attributes are present in the address fragment? e. 1 f. 2 g. 3 h. 4
- The Book table has the following columns: genre – varchar(20) pages – integer author_id – char(3) isbn_number – varchar(20) Which column should be designated at the primary key for the Book table? e. genre f. pages g. author_id h. isbn_number
- The Book table has the following columns: genre – varchar(20) pages – integer author_id – char(3) isbn_number – varchar(20) Which column should be designated as the foreign key for the Book table? e. genre f. pages g. author_id h. isbn_number
