MILESTONE
Score 19/25
You passed this Milestone
19 questions were answered correctly.
6 questions were answered incorrectly.
1
In each milestone, you may want or need to use the database and query tool to answer so
...
MILESTONE
Score 19/25
You passed this Milestone
19 questions were answered correctly.
6 questions were answered incorrectly.
1
In each milestone, you may want or need to use the database and query tool to answer some of the questions. We
suggest you open the tool in another browser tab while you are working on this assessment.
https://postgres.sophia.org/
Which type of backup would require no more than 2 backup sets?
Differential and incremental backup
Incremental and full backup
Full backup and nightly backup
Differential and full backup
CONCEPT
Backup Methods
2
In each milestone, you may want or need to use the database and query tool to answer some of the questions. We
suggest you open the tool in another browser tab while you are working on this assessment.
https://postgres.sophia.org/
What is wrong with the following ERD meant to show the attributes of an entity?
The symbols used are swapped.
The attributes are not correctly linked to the entity.
The primary key is not indicated correctly.
There is nothing wrong with the ERD.
CONCEPT
Adding Attributes
3
In each milestone, you may want or need to use the database and query tool to answer some of the questions. We
suggest you open the tool in another browser tab while you are working on this assessment.
https://postgres.sophia.org/
In the following scenario, which of the following statements would be saved to the database assuming
these are all in a single transaction?
1. Update
2. Insert
3. Insert
4. Delete
5. Update
ROLLBACK
COMMIT
1
1, 2, 3, 4, 5
none
5
CONCEPT
COMMIT and ROLLBACK to Manage Changes
4
In each milestone, you may want or need to use the database and query tool to answer some of the questions. We
suggest you open the tool in another browser tab while you are working on this assessment.
https://postgres.sophia.org/
Using the IN operator, filter the invoice table to find the billing city being set to Paris, London, New York, or
Brussels.
Identify the billing postal code of the 4th record.
1000
SW1V 3EN
75009
75002
CONCEPT
IN to Filter Data
5
In each milestone, you may want or need to use the database and query tool to answer some of the questions. We
suggest you open the tool in another browser tab while you are working on this assessment.
https://postgres.sophia.org/
Given the tables provided, which of the following DROP TABLE series of statements would correctly
remove the tables without causing an error?
DROP TABLE customer;
DROP TABLE invoice;
DROP TABLE invoice_line;
DROP TABLE genre;
DROP TABLE album;
DROP TABLE artist;
DROP TABLE invoice_line;
DROP TABLE playlist_track;
DROP TABLE playlist;
DROP TABLE genre;
DROP TABLE album;
DROP TABLE artist;
CONCEPT
DROP TABLE to Remove Tables
6
In each milestone, you may want or need to use the database and query tool to answer some of the questions. We
suggest you open the tool in another browser tab while you are working on this assessment.
https://postgres.sophia.org/
In the context of a database, what is knowledge?
A body of information and facts about a specific topic
A process that focuses on data collection, storage, and retrieval
Raw facts of interest to the end user
A computer structure that holds a collection of related data
CONCEPT
Database Purpose
7
Which of the following is a key part of physical design in an entity relationship model?
This model uses column names instead of attributes.
In this model, the characteristics such as location, path, and format are described.
In this model, no primary keys are specified in the entities.
This model resolves the many-to-many relationships to multiple one-to-many relationships.
CONCEPT
Physical Design
8
Which of the following is a key part of logical design in an entity relationship model?
This model avoids any database model-specific details.
This model has normalization that occurs.
In this model, no attributes are specified.
This model converts entities into tables.
CONCEPT
Logical Design
9
In each milestone, you may want or need to use the database and query tool to answer some of the questions. We
suggest you open the tool in another browser tab while you are working on this assessment.
https://postgres.sophia.org/
Given the following view that has been created, how would you query the view to list the artist names in
ascending order and album titles in desc order?
CREATE VIEW album_artist_names
AS
SELECT album.title, artist.name
FROM album
INNER JOIN artist
ON album.artist_id = artist.artist_id;
SELECT *
FROM album_artist_names
ORDER BY name, title DESC;
SELECT *
FROM album_artist_names
ORDER BY name ASC, title DESC;
SELECT *
FROM album_artist_names
ORDER BY name DESC, title;
SELECT *
FROM album_artist_names
ORDER BY name DESC, title ASC;
CONCEPT
VIEW & Complex Queries
10
What type of scenario would help protect against a security issue in relation to database migration
challenges?
Testing for data loss
Converting schemas
Normalizing data
Encrypting the data
CONCEPT
Migrating Databases in an Organization
11
In each milestone, you may want or need to use the database and query tool to answer some of the questions. We
suggest you open the tool in another browser tab while you are working on this assessment.
https://postgres.sophia.org/
In trying to delete from the playlist table, what is the cause of this error?
"Query failed because of: error: update or delete on table "playlist" violates foreign key constraint
"playlist_track_playlist_id_fkey" on table "playlist_track"
The playlist_id doesn't exist in the playlist table.
The playlist_track table has a reference to the playlist_id that is being deleted.
The playlist_id doesn't exist in the playlist_track table.
The track has to be deleted first before the playlist is deleted.
CONCEPT
Foreign Key Errors
12
In each milestone, you may want or need to use the database and query tool to answer some of the questions. We
suggest you open the tool in another browser tab while you are working on this assessment.
https://postgres.sophia.org/
Given the following queries, which of these would be the most efficient?
1. SELECT *
FROM invoice
WHERE customer_id IN
(SELECT customer_id
FROM customer
WHERE country like 'U%');
2. SELECT invoice.*
FROM invoice
INNER JOIN customer
ON customer.customer_id = invoice.customer_id
WHERE country like 'U%';
Query #1 would be more efficient as it is based on primary and foreign keys.
Query #2 would be more efficient as it is not using indexed columns.
Query #2 would be more efficient as it is based on primary and foreign keys.
Both would be the same as both use the same indices for the join and filter.
CONCEPT
Subquery Performance
13
What is a benefit of using a lookup table?
They guarantee that there will be no data redundancies due to functional dependencies.
They model pure relationships rather than entities.
They resolve weak relationships.
They provide fast answers to some questions.
CONCEPT
Reference Tables
14
In each milestone, you may want or need to use the database and query tool to answer some of the questions. We
suggest you open the tool in another browser tab while you are working on this assessment.
https://postgres.sophia.org/
Identify the correctly constructed ALTER TABLE statement that removes the column phone from the
employee table.
ALTER TABLE employee DROP COLUMN phone;
ALTER TABLE employee DROP COLUMN phone VARCHAR (100);
ALTER TABLE employee DROP COLUMN home_phone;
ALTER TABLE employee ADD phone;
CONCEPT
ALTER TABLE to Change Columns: Add/Drop
15
In each milestone, you may want or need to use the database and query tool to answer some of the questions. We
suggest you open the tool in another browser tab while you are working on this assessment.
https://postgres.sophia.org/
Use the following data model for this question:
Recipe
recipe_id
title
Ingredient
ingredient_id
name
Recipe_Ingredient
recipe_ingredient_id
recipe_id
ingredient_id
Which of the following is a situation where an OUTER JOIN could be useful?
To view recipes that have ingredients in the ingredients table
To view only ingredients that are being utilized for particular recipes
To view all ingredients in the database even if they are not being used for a particular recipe
To view recipes that have the word "banana" in their title
CONCEPT
Outer Joins
16
In each milestone, you may want or need to use the database and query tool to answer some of the questions. We
suggest you open the tool in another browser tab while you are working on this assessment.
https://postgres.sophia.org/
Which of the following scenarios reflects the durability property?
1. In the flower database, there are 50 flowers available.
2. Reese has checked and there are 50 flowers.
3. Reese has attempted to take out 5 flowers.
4. Which trying to take them out, there was an error in trying to dispense.
5. While checking, there are still 50 flowers available in the system.
1. A user attempts to do a product transfer between companies.
2. The quantity of the product is moved from the first company.
3. Only once the product is verified to have been deducted, the quantity is moved to the second company.
4. Verification is done and identifies that the total amounts before and after the transactions are not
maintained.
5. The transaction is reverted.
1. Tiffany has updated a customer's address while on the phone with them.
2. The server restarted after Tiffany clicked on save.
3. When the server comes back up, Tiffany was able to verify that the address was updated.
1. In the library database, there are 50 books available.
2. Billy has checked and there are 50 books.
3. Sam has checked and there are 50 books.
4. Billy has taken out 5 books.
5. The library system informs Sam of the update and Sam now checks that there are 45 books.
6. Sam checks out 10 books.
7. There are now 35 books in the library database.
CONCEPT
Durability
17
In each milestone, you may want or need to use the database and query tool to answer some of the questions. We
suggest you open the tool in another browser tab while you are working on this assessment.
https://postgres.sophia.org/
Which of the following statement(s) would successfully delete the playlist_id 8 from the playlist table?
DELETE FROM playlist_id WHERE playlist_id_id = 8;
DELETE FROM playlist_track WHERE playlist_id_id = 8;
DELETE from playlist_track WHERE playlist_id = 8;
DELETE from playlist WHERE playlist_id = 8;
DELETE FROM playlist_id WHERE playlist_id_id = 8;
DELETE FROM playlist_track WHERE playlist_id_id = 8;
CONCEPT
DELETE FROM to Remove Row
18
In each milestone, you may want or need to use the database and query tool to answer some of the questions. We
suggest you open the tool in another browser tab while you are working on this assessment.
https://postgres.sophia.org/
What is wrong with the following ERD meant to show a relationship between two entities?
There is nothing wrong with the ERD.
One of the items is set up as an attribute.
There is no relationship between the entities.
Both of the objects are attributes.
CONCEPT
Tables as Entities
19
In each milestone, you may want or need to use the database and query tool to answer some of the questions. We
suggest you open the tool in another browser tab while you are working on this assessment.
https://postgres.sophia.org/
What will be the result of the query based on the following criteria?
< ALL ()
Returns true if the value is less than or equal to the smallest value returned by the subquery.
Returns true if the value is less than or equal to any of the values returned by the subquery.
Returns true if the value is less than any of the values returned by the subquery.
Returns true if the value is less than the smallest value returned by the subquery.
CONCEPT
ANY and ALL Operators
20
In each milestone, you may want or need to use the database and query tool to answer some of the questions. We
suggest you open the tool in another browser tab while you are working on this assessment.
https://postgres.sophia.org/
Identify the line of code that would either generate a syntax, logical, or requirements error in the
following CREATE TABLE statement.
The statement should create a table called 'users' that consists of the user_id as the primary key that is
auto-incremented, the username, and password.
CREATE TABLE user(
user_id serial PRIMARY KEY,
username VARCHAR (50)
password VARCHAR (50)
);
3 4 1 2
CONCEPT
Primary Key and Auto-increment
21
In each milestone, you may want or need to use the database and query tool to answer some of the questions. We
suggest you open the tool in another browser tab while you are working on this assessment.
https://postgres.sophia.org/
Using the WHERE clause, filter the employee table to include individuals that report to Andrew Adams, who
has an employee_id of 1.
Identify the title of the 2nd individual listed.
Sales Support Agent
IT Manager
Sales Manager
General Manager
CONCEPT
WHERE to Filter Data
22
In each milestone, you may want or need to use the database and query tool to answer some of the questions. We
suggest you open the tool in another browser tab while you are working on this assessment.
https://postgres.sophia.org/
Which is an advantage of both the command line and the GUI?
We can use any parameters without limitation.
We can backup the data, schema, or both at the same time.
We can compress the backup script.
We can back up a remote server.
CONCEPT
Backups: Command Line vs. GUI
23
In each milestone, you may want or need to use the database and query tool to answer some of the questions. We
suggest you open the tool in another browser tab while you are working on this assessment.
https://postgres.sophia.org/
Which of these SELECT statements would successfully display exactly five columns of data from the
customer table?
SELECT *
FROM customer;
SELECT customer_id, city, state, phone, company
FROM customer;
SELECT customer_id, city_id, state_id, phone_id, company_id
FROM customer;
SELECT customer_id + city + state + phone + company
FROM customer
CONCEPT
SELECT to Display Data
24
What would be introduced as a criterion of a table to be in second normal form?
Each cell of a table should have a single value.
There should be no repeating groups.
All attributes in a table are dependent on the primary key.
There should be no repeating groups.
CONCEPT
Second Normal Form
25
Which of the following is a key part of conceptual design in an entity relationship model?
This model resolves the many-to-many relationships to multiple one-to-many relationships.
This model does not depend on the database management software or the hardware used to implement the
model.
This model converts attributes into columns.
This model adds all of the attributes for each entity.
CONCEPT
Conceptual Design
About
Contact Us
Privacy Policy
Terms of Use
[Show More]
.png)
