UNIT 3 — MILESTONE 3
Score 15/21
You passed this Milestone
15 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 t
...
UNIT 3 — MILESTONE 3
Score 15/21
You passed this Milestone
15 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 of the following statements would create a role named database_admin and gives them the ability
to create databases?
CREATE ROLE database_admin CREATEDB;
CREATE ROLE database_admin NOINHERIT;
CREATE ROLE database_admin CREATEROLE;
CREATE ROLE database_admin INHERIT;
Common mistakes when creating roles are using quotes around the role name, not setting the correct role
privileges, or not using the right syntax.
CONCEPT
CREATE ROLE to Create Groups
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/
Which of the following criterion is specific to the isolation property?
Once transaction changes are done and saved, they cannot be lost.
When a transaction is completed, the database must be in a consistent state.
Data used in one transaction cannot be used in another transaction until the first transaction is completed.
All SQL requests of a transaction must be completed, if not, the transaction must be aborted.
The isolation property ensures that the data that's used in a transaction cannot be used in another transaction until
the original transaction is complete with it. This is important when you have multiple users accessing and
updating data in the database at the same time.
CONCEPT
ACID Properties
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/
Which of the following cases would make the most sense to use a b-tree index?
SELECT *
FROM employee
WHERE address LIKE '%i%';
SELECT *
FROM album
WHERE artist_id < 5;
SELECT *
FROM customer
WHERE customer_id > 10 AND customer_id < 20;
SELECT *
FROM customer
WHERE fax LIKE '+55%';
The b-tree index makes the most sense to use when the data is even and balanced. It works best on data types
like text, numbers and timestamps when we compare ranges or content that start with a value. It does not do well
with wild cards at the start of a comparison or ranges.
CONCEPT
B-Tree Index
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/
Which of the following DROP INDEX statements would give a notice if the index does not exist but not an
error?
DROP INDEX myindex RESTRICT;
DROP INDEX CONCURRENTLY myindex;
DROP INDEX myindex;
DROP INDEX IF EXISTS myindex;
Common mistakes when it comes to dropping an index include not including the correct index name, not using
the right syntax, and not using the right parameters based on the requirements. Using IF EXISTS will not throw
an error if the index does not exist. CONCURRENTLY will drop the index without locking concurrent select,
insert, updates and deletes. CASCADE will automatically drop objects that depend on the index. RESTRICT
will refuse to drop the index if any objects depend on it. This is set by default.
CONCEPT
DROP INDEX to Remove Indexes
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/
Which of the following scenarios reflect the atomicity property?
1. User 1 has $500 in their online account.
2. User 2 has $300 in their online account.
3. User 1 sends user 2 $100.
4. User 1 has $400 in their account.
5. User 2 has $300 in their account.
6. The transaction is reverted.
1. A fairly large update to the products table.
2. The update is saved to the logs.
3. The update starts to then save to the disk.
4. The system fails midway through.
5. When the system starts back up, the committed transactions that haven't been written to disk are written.
1. Transaction 1 reads a product's cost which is set at $100.
2. Transaction 1 updates the product to set the price to $90.
3. Transaction 2 reads the same product's cost and sees $100.
4. Transaction 1 commits the cost.
5. Transaction 3 reads the same product's cost and sees $90.
1. The product quantity starts at 200.
2. Transaction 1 runs to read the quantity of the product to be updated.
3. Transaction 2 runs in parallel to read the quantity of the product to be updated.
4. Transaction 1 updates the product quantity to reduce it by 100.
5. Transaction 2 updates the product quantity to reduce it by 5.
6. The product quantity ends at 195.
Atomicity requires that all SQL requests in a transaction should be fully completed and if not, the entire
transaction should be aborted. The transaction should be viewed as a single logical unit of work that is
indivisible.
CONCEPT
Atomicity
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 following scenario, which of the following statements would be saved to the database assuming
these are all in a single transaction?
1. Update
ROLLBACK
2. Insert
3. Insert
4. Delete
ROLLBACK
5. Update
COMMIT
1, 2, 3, 4, 5
2, 3, 4, 5
5
None
In a transaction, if a ROLLBACK statement is run, all statements up to the prior COMMIT or ROLLBACK will
be reverted. If a COMMIT statement is run, all statements up to the prior ROLLBACK or COMMIT will be
saved.
CONCEPT
COMMIT and ROLLBACK to Manage Changes
7
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. The product quantity starts at 200.
2. Transaction 1 runs to read the quantity of the product to be updated.
3. Transaction 2 runs in parallel to read the quantity of the product to be updated.
4. Transaction 1 updates the product quantity to reduce it by 100.
5. Transaction 2 updates the product quantity to reduce it by 5.
6. The product quantity ends at 195.
1. Transaction 1 reads a product's cost which is set at $100.
2. Transaction 1 updates the product to set the price to $90.
3. Transaction 2 reads the same product's cost and sees $100.
4. Transaction 1 commits the cost.
5. Transaction 3 reads the same product's cost and sees $90.
1. User 1 has $500 in their online account.
2. User 2 has $300 in their online account.
3. User 1 sends user 2 $100.
4. User 1 has $400 in their account.
5. User 2 has $300 in their account.
5. The transaction is reverted.
1. A fairly large update is made to the products table.
2. The update is saved to the logs.
3. The update starts to then save to the disk.
4. The system fails midway through.
5. When the system starts back up, the committed transactions that haven't been written to disk are written.
The durability property ensures that once a transaction is done and committed, the changes cannot be undone or
lost even if there is system failure.
CONCEPT
Durability
8
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 statements represents a correctly structured transaction?
BEGIN
UPDATE customer
SET postal_code = '33433'
WHERE customer_id = 22
UPDATE customer
SET postal_code = '10789'
WHERE city = 'Berlin'
UPDATE customer
SET company = 'Humor Inc.'
WHERE city = 'Prague'
ROLLBACK
BEGIN;
UPDATE customer
SET postal_code = '33433'
WHERE customer_id = 22;
UPDATE customer
SET postal_code = '10789'
WHERE city = 'Berlin';
UPDATE customer
SET company = 'Humor Inc.'
WHERE city = 'Prague';
ROLLBACK;
BEGIN;
ROLLBACK;
UPDATE customer
SET postal_code = '33433'
WHERE customer_id = 22;
UPDATE customer
SET postal_code = '10789'
WHERE city = 'Berlin';
UPDATE customer
SET company = 'Humor Inc.'
WHERE city = 'Prague';
UPDATE customer
SET postal_code = '33433'
WHERE customer_id = 22;
UPDATE customer
SET postal_code = '10789'
WHERE city = 'Berlin';
UPDATE customer
SET company = 'Humor Inc.'
WHERE city = 'Prague';
ROLLBACK;
Common mistakes with a transaction include missing a ; after BEGIN as well as each statement including the
COMMIT or ROLLBACK statement, not having the COMMIT or ROLLBACK at the end of the statement, and
not including BEGIN at the start of the statement.
CONCEPT
Transactions
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/
Which of the following statements would restore just the table 'customer' from the mydatabase database
from backup.sql?
psql mydatabase customer < backup.sql
psql -t customer mydatabase < backup.sql
psql -a customer mydatabase < backup.sql
psql -t customer < backup.sql
Common mistakes when restoring a database using the command line include, using the wrong file redirect
operator, using the incorrect syntax, not using the right order of statements, or not including the command line
options.
CONCEPT
Restore from Backup
10
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 statements would clean the data in a data dump prior to outputting the commands
to create them of the mydatabase to backup.sql?
pg_dump mydatabase > backup.sql
pg_dump -c mydatabase > backup.sql
pg_dump -a mydatabase > backup.sql
pg_dump -b mydatabase > backup.sql
Common mistakes when backing up a database using the command line include, using the wrong file redirect
operator, using the incorrect syntax, not using the right order of statements or not including the command line
options.
CONCEPT
Create a Backup
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/
Which type of backup would result in the fastest restoration?
Nightly backup
Full backup
Incremental backup
Differential backup
Full backups are a full copy of the entire data set. Although they are the best protection, they are timeconsuming and quite large to store. Incremental backups only back up the data that has changed since the
previous backup even if it's another incremental backup. These file sizes are the smallest out of the lot.
Differential backups are similar to incremental backups but it starts with a full backup and includes data since
the prior full backup. At most, there are only 2 backups to restore from.
CONCEPT
Backup Methods
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/
Which of the following scenarios reflect the consistency property?
1. Transaction 1 reads a product's cost which is set at $100.
2. Transaction 1 updates the product to set the price to $90.
3. Transaction 2 reads the same product's cost and sees $100.
4. Transaction 1 commits the cost.
5. Transaction 3 reads the same product's cost and sees $90.
1. A fairly large update to the products table.
2. The update is saved to the logs.
3. The update starts to then save to the disk.
4. The system fails midway through.
5. When the system starts back up, the committed transactions that haven't been written to disk are written.
1. The product quantity starts at 200.
2. Transaction 1 runs to read the quantity of the product to be updated.
3. Transaction 2 runs in parallel to read the quantity of the product to be updated.
4. Transaction 1 updates the product quantity to reduce it by 100.
5. Transaction 2 updates the product quantity to reduce it by 5.
6. The product quantity ends at 95.
1. User 1 has $500 in their online account.
2. User 2 has $300 in their online account.
3. User 1 sends user 2 $100.
4. User 1 has $400 in their account.
5. User 2 has $300 in their account.
5. The transaction is reverted.
The consistency property ensures that a transaction takes the database from one consistent state to another
consistent state. If any transaction parts violates an integrity constraint, the entire transaction should be rolled
back.
CONCEPT
Consistency
13
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 statements would create a user named testuser1 with the password set as
testpassword1 that is valid until 2042-01-01?
CREATE USER testuser1
VALID UNTIL '2042-01-01'
WITH PASSWORD 'testpassword1'
CREATE USER testuser1
WITH PASSWORD testpassword1
VALID UNTIL 2042-01-01
CREATE USER testuser1
WITH PASSWORD 'testpassword1'
VALID UNTIL '2042-01-01'
CREATE USER testuser1
WITH PASSWORD 'testpassword1'
EXPIRES '2042-01-01'
Common mistakes when creating a user include adding quotes to the username, forgetting to add quotes to the
password, incorrectly setting the additional parameters, or using the incorrect syntax.
CONCEPT
CREATE USER/ROLE to Add Users
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/
Which is an advantage of both the command line and the GUI?
We can encode the backup file to another format.
We can use any parameters without limitation.
We can restore a remote database.
We can back up multiple databases at once.
With the command line, you have more flexibility with all of the parameters to backup and restore the database.
You're able to pipe together commands to be able to backup and restore data into different databases and have
more control. With the GUI, you have more options to select from but all of them are limited to the functionality
that has been implemented. Both types do allow us to continue to restore a database or stop if there's an error.
We're also able to encode the backup files or backup the data, schema, or both using either option.
CONCEPT
Backups: Command Line vs. GUI
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/
Using this type of process between the user input and dynamically created statements can help reduce the
number of SQL injection attacks.
Filtering
Posting
Coding
Construction
By filtering the user input, we can ensure that we're checking for any special characters that should not be
included.
CONCEPT
Application Security
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 cases would make the most sense to use a hash index?
SELECT *
FROM album
WHERE artist_id < 5;
SELECT *
FROM track
WHERE media_type_id = 1;
SELECT *
FROM customer
WHERE customer_id > 10 AND customer_id < 20;
SELECT *
FROM employee
WHERE address LIKE '%i%';
Hash indexes can only be used when we have equality comparisons. It does not do well with wild cards or any
ranges.
CONCEPT
Hash Index
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 scenarios reflect the isolation property?
1. User 1 has $500 in their online account.
2. User 2 has $300 in their online account.
3. User 1 sends user 2 $100.
4. User 1 has $400 in their account.
5. User 2 has $300 in their account.
6. The transaction is reverted.
1. A fairly large update to the products table.
2. The update is saved to the logs.
3. The update starts to then save to the disk.
4. The system fails midway through.
5. When the system starts back up, the committed transactions that haven't been written to disk are written.
1. The product quantity starts at 200.
2. Transaction 1 runs to read the quantity of the product to be updated.
3. Transaction 2 runs in parallel to read the quantity of the product to be updated.
4. Transaction 1 updates the product quantity to reduce it by 100.
5. Transaction 2 updates the product quantity to reduce it by 5.
6. The product quantity ends at 195.
1. Transaction 1 reads a product's cost which is set at $100.
2. Transaction 1 updates the product to set the price to $90.
3. Transaction 2 reads the same product's cost and sees $100.
4. Transaction 1 commits the cost.
5. Transaction 3 reads the same product's cost and sees $90.
The isolation property ensures that the data that's used in a transaction cannot be used in another transaction until
the original transaction is complete with it. This is important when you have multiple users accessing and
updating data in the database at the same time.
CONCEPT
Isolation
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/
Which of the following statements will grant insert permissions on the track table to all users?
GRANT INSERT ON track TO public;
GRANT INSERT ON track TO ALL;
GRANT INSERT ON 'track' TO 'public';
GRANT INSERT ON public to track;
Common mistakes when granting privileges to roles include using quotes around the table or role name, not
using commas between privileges, including the incorrect privileges, or not using the right syntax.
CONCEPT
GRANT to Assign Privileges
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/
Which of the following sequence of statements would ensure that Jimmy has the privileges of the roles of
health_department and manager?
CREATE ROLE manager NOINHERIT;
CREATE ROLE health_department INHERIT;
GRANT health_department to jimmy;
GRANT manager to health_department;
CREATE ROLE manager NOINHERIT;
CREATE ROLE health_department NOINHERIT;
GRANT manager to jimmy;
GRANT manager to health_department;
CREATE ROLE manager NOINHERIT;
CREATE ROLE health_department NOINHERIT;
GRANT health_department to jimmy;
GRANT manager to jimmy;
CREATE ROLE manager NOINHERIT;
CREATE ROLE health_department NOINHERIT;
GRANT health_department to jimmy;
GRANT manager to health_department;
Common mistakes when assigning roles include not identifying which roles have INHERIT or NOINHERIT
privileges, or not ensuring that the user has all of the roles allocated to the user.
CONCEPT
GRANT to Assign Users
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/
Which of the following operators will attempt to use a hash or b-tree index?
=
@>
&&
>>
By default, the b-tree index will be used if one of the following operators are used: <, <=, =, >= or >. The hash
index will only be used if it is involved in a = operator. GiST indexes are focused on multiple indexing strategies
being used and would be used in other operators.
CONCEPT
Index Overview
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/
Which of the following permissions are automatically granted to a user?
Any piece that touches the underlying system
Change items in the database
Enabling extensions
All permissions need to be granted to the user or role
All permissions need to be granted to the user explicitly for the user to have access. Only superusers have all
permissions directly available to their account or role.
CONCEPT
Superusers
About
Contact Us
Privacy Policy
Terms of Use
[Show More]
