About SmartExpert

Levels Tought:
Elementary,Middle School,High School,College,University,PHD

Expertise:

Accounting,Business & Finance See all

Accounting,Business & Finance,Economics,English,HR Management,Math Hide all

Teaching Since:	Apr 2017
Last Sign in:	4 Weeks Ago
Questions Answered:	7559
Tutorials Posted:	7341

Education

BS,MBA, PHD
Adelphi University/Devry
Apr-2000 - Mar-2005

Experience

HOD ,Professor
Adelphi University
Sep-2007 - Apr-2017

Category > Programming Posted 24 Feb 2023 My Price 55.00

Introduction to Relational Database Management Systems Unit 5 Final Exam

CIS 111 SOPHIA-STRAYER Introduction to Relational Database Management Systems Unit 5 Final Exam -sobtell.com

Click link for Answers All Correct

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 is a correctly formatted INSERT statement that will insert three records into the playlist table?

•

insert into playlist (name,playlist_id) values (40, 'Top 40'), (41, 'Top 40'), (42, 'Top 40')

•

insert into playlist (playlist_id, name) values (40, 'Top 40'), (41, 'Top 40'), (42, 'Top 40')

•

insert into playlist (playlist_id, name) values (40, 'Top 40'), (40, 'Top 41'), (40, 'Top 42')

•

insert into playlist (playlist_id, name) values (40, 'Top 40') (41, 'Top 40') (42, 'Top 40')

https://postgres.sophia.org/

Using the WHERE clause, filter the employee table to include individuals that live in Edmonton.

Identify the title of the individual(s) listed.

•

IT Sales Manager

•

Sales Support Agent

•

Andrew

•

General Manager

https://postgres.sophia.org/

Which of the following is a correctly formatted INSERT statement that will successfully add a record into the genre table?

•

insert into genre (genre_id, name) values (30, 'Funk' )

•

insert into genre (genre_id, name) values ('Funk',35)

•

insert into genre (genre_id, name) values (40, Funk)

•

insert into genre (genre_id, name) values (19, 'Funk' )

https://postgres.sophia.org/

Identify the correctly constructed ALTER TABLE statement that removes the address column from the customer table.

•

ALTER TABLE customer DROP home_address;

•

ALTER TABLE customer DROP address VARCHAR (100);

•

ALTER TABLE customer DROP address;

•

ALTER TABLE customer ADD address;

https://postgres.sophia.org/

Which of the following would set the postal_code of the customer with the customer_id equal to 22, to 33433.

•

UPDATE customer

IN postal_code = '33433'

WHERE customer_id = 22

•

UPDATE customer

SET postal_code

WHERE customer_id = '33433'

•

UPDATE customer

WHERE customer_id = 22

SET postal_code = '33433'

•

UPDATE customer

SET postal_code = '33433'

WHERE customer_id = 22

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 invoice;

DROP TABLE invoice_line;

DROP TABLE customer;

•

DROP TABLE playlist_track;

DROP TABLE invoice_line;

DROP TABLE invoice;

•

DROP TABLE playlist_track

DROP TABLE playlist;

DROP TABLE genre;

•

DROP TABLE genre;

DROP TABLE album;

DROP TABLE artist;

https://postgres.sophia.org/

Consider the following new table:

CREATE TABLE surveylist(

surveylist_id SERIAL PRIMARY KEY,

email VARCHAR NOT NULL,

phone VARCHAR

);

Given this new table, which INSERT statement would query from the customer table to insert the email and phone of all customers in the right columns?

•

INSERT INTO surveylist (surveylist_id, email, phone)

SELECT email, phone FROM customer;

•

INSERT INTO surveylist (email, phone)

SELECT phone, email FROM customer;

•

INSERT INTO surveylist (email, phone)

SELECT email, phone FROM customer;

•

INSERT INTO surveylist

SELECT email, phone FROM customer;

https://postgres.sophia.org/

Using the SELECT statement, query the track table to find the average length of a track that has the genre_id equal to 5, rounded to the nearest millisecond.

•

394489

•

134644

•

134643.5

•

134643

https://postgres.sophia.org/

Identify the correctly constructed ALTER TABLE statement to add a UNIQUE constraint to the column student_number with the constraint name student_number_unique on the table called 'student'.

•

ALTER TABLE student ADD UNIQUE student_number CONSTRAINT (student_number_unique);

•

ALTER TABLE student ADD CONSTRAINT student_number_unique UNIQUE (student_number);

•

ALTER TABLE student ADD CONSTRAINT student_number UNIQUE (student_number_unique);

•

ALTER TABLE student ADD CONSTRAINT student_number UNIQUE (student_number);

https://postgres.sophia.org/

Using the WHERE and HAVING clauses, filter the track table for the tracks having the milliseconds greater than 100,000, grouped by the album_id having the number of tracks greater than 20. Provide the list of album_id's and the count of tracks that fit these criteria.

Which of the following queries would provide the correct results?

•

SELECT album_id, count(track_id)

FROM track

WHERE count(track_id) > 20

GROUP BY album_id

HAVING milliseconds > 100000

•

SELECT album_id, sum(track_id)

FROM track

WHERE milliseconds > 100000

GROUP BY album_id

HAVING sum(track_id) > 20

•

SELECT album_id, count(track_id)

FROM track

WHERE milliseconds > 100000

GROUP BY album_id

HAVING count(track_id) > 20

•

SELECT album_id, count(track_id)

FROM track

GROUP BY album_id

HAVING count(track_id) > 20

WHERE milliseconds > 100000

https://postgres.sophia.org/

Which of these SELECT statements would successfully display exactly five columns of data from the customer table?

•

SELECT customer_id, city_id, state_id, phone_id, company_id

FROM customer;

•

SELECT customer_id + city + state + phone + company

FROM customer

•

SELECT *

FROM customer;

•

SELECT customer_id, city, state, phone, company

FROM customer;

https://postgres.sophia.org/

Using the SELECT statement, query the invoice table to find the average total cost for all orders placed between 2010-01-01 and 2011-01-01.

•

5.85542168674698

•

5.66265060240963

•

5.7063106796116505

https://postgres.sophia.org/

Using the GROUP BY clause and the count aggregate function, filter the track table to group the tracks based on album_id.

How many tracks are in album 99?

•

https://postgres.sophia.org/

Using the SELECT statement, query the invoice table to find the maximum invoice_date where the billing_country is equal to Canada.

•

2009-01-01

•

2013-12-22

•

2013-12-21

•

2013-12-06

https://postgres.sophia.org/

Select the correctly constructed CHECK constraint to validate the date_of_birth column of type data, to ensure that values placed into it are greater than 1850-01-01

•

CHECK (date_of_birth < '1850-01-01')

•

CHECK (date_of_birth >= '1850-01-01')

•

CHECK (date_of_birth > '1850-01-01')

•

CHECK (date_of_birth > 1850-01-01)

https://postgres.sophia.org/

Using the IN operator, filter the album table to find those with the artist ID set to 8, 17, 22, or 3.

Identify the title of the 4th record.

•

Out of Exile

•

Minha Historia

•

Big Ones

•

Coda

https://postgres.sophia.org/

Consider the following table:

CREATE TABLE video(

video_id SERIAL PRIMARY KEY,

video_name VARCHAR NOT NULL

);

Which of the following is a correctly formatted INSERT statement that will successfully add a new record that uses the auto-incremented primary key into this table?

•

insert into video (video_id, video_name) values (1, 'home video - first day of school' )

•

insert into video (video_name) values ('home video - first day of school' )

•

insert into video (video_name) values (home video - first day of school)

•

insert into video (video_id, video_name) values (nextval, 'home video - first day of school')

https://postgres.sophia.org/

Using the SELECT statement, query the customer table to find the number of customers that live in the country Canada.

•

https://postgres.sophia.org/

Using the LIKE operator in the WHERE clause, use the necessary wildcards to filter the album table to find the albums that have a year starting with 19 in the title.

Identify the 4th album ID.

•

196

•

104

•

123

•

146

https://postgres.sophia.org/

Which of the following statement(s) would successfully delete the invoice_id 280 from the invoice table?

•

DELETE FROM invoice WHERE invoice_id = 280;

DELETE FROM invoice_line WHERE invoice_id = 280;

•

DELETE FROM invoice WHERE invoice_id = 280;

•

DELETE FROM invoice_line WHERE invoice_id = 280;

DELETE FROM invoice WHERE invoice_id = 280;

•

DELETE FROM invoice_line WHERE invoice_id = 280;

https://postgres.sophia.org/

Using the SELECT statement, query the track table to find the total price for all tracks that have the genre_id not equal to 1.

•

1284.03

•

3552.27

•

0.99

•

2396.94

https://postgres.sophia.org/

Which of these constraints uses multiple fields as a primary key?

•

COMPOSITE KEY

•

CHECK

•

FOREIGN KEY

•

UNIQUE

https://postgres.sophia.org/

What is the main function of the FROM clause in SQL?

•

To identify one or more tables as the source for a query

•

To apply conditions to filter the dataset

•

To retrieve zero or more rows from one or more database columns

https://postgres.sophia.org/

Using the ORDER BY clause, sort the customer table by the company name of the customer in ascending order and identify the 10th company name in the list from among the answer options.

•

Murray

•

Woodstock Discos

•

Martins

•

null

https://postgres.sophia.org/

Given the customer table and the data that it contains, and assuming that you have the rights to modify the table, which of the following ALTER TABLE statements would create an error?

•

ALTER TABLE customer ALTER COLUMN postal_code TYPE VARCHAR (100);

•

ALTER TABLE customer ALTER COLUMN state TYPE VARCHAR (100);

•

ALTER TABLE customer ALTER COLUMN city TYPE VARCHAR (100);

•

ALTER TABLE customer ALTER COLUMN state VARCHAR (100);

https://postgres.sophia.org/

The following CREATE TABLE statement creates a table called 'users' that consists of the user_id as the primary key, the username, and the password.

CREATE TABLE users(

user_id int PRIMARY KEY,

username VARCHAR 50,

password VARCHAR (50)

);

Identify the line of code that would generate an error in this CREATE TABLE statement.

•

https://postgres.sophia.org/

Identify the SQL command that uses an aggregate function that could be used to find the youngest employee in the employee table.

•

SELECT min(birth_date) FROM employee;

•

SELECT max(birth_date) FROM employee;

•

SELECT count(birth_date) FROM employee;

•

SELECT max birth_date FROM employee;

https://postgres.sophia.org/

Using the BETWEEN operator, filter the album table to find the albums with an artist ID between 5 and 10.

Identify the 5th album ID.

•

https://postgres.sophia.org/

Using the LIKE operator in the WHERE clause, filter the customer table to list the individuals located in a country that ends with the lowercase 'a.'

Identify the 5th individual's country.

•

Australia

•

Canada

•

Austria

•

USA

https://postgres.sophia.org/

Using the WHERE clause, filter the invoice table to find the invoices that were dated after March 1st, 2009.

Identify the first customer ID of the invoice.

•

https://postgres.sophia.org/

USING the SELECT statement, query the track table ordered by the track_id. Set the LIMIT to 5 and OFFSET to 18.

What is the name of the last row returned?

•

Problem Child

•

Love In An Elevator

•

Whole Lotta Rosie

•

Walk on Water

https://postgres.sophia.org/

Given the employee table and the data that it contains, and assuming that you have the rights to modify the table, which of the following ALTER TABLE statements would work without errors?

•

ALTER TABLE employee ALTER employee_id TYPE VARCHAR (100);

•

ALTER TABLE employee ALTER COLUMN email TYPE int;

•

ALTER TABLE employee ALTER COLUMN email TYPE TEXT;

•

ALTER TABLE customer ALTER COLUMN city TYPE int;

https://postgres.sophia.org/

Which of the following would set the company of the customers that live in the city of Prague to Humor Inc.?

•

UPDATE customer

SET company = 'Humor Inc.'

WHERE city = 'Prague'

•

UPDATE customer

SET company = Humor Inc.

WHERE city = 'Prague'

•

UPDATE customer

SET company = 'Humor Inc.'

AND city = 'Prague'

•

UPDATE customer

WHERE city = 'Prague'

SET company = 'Humor Inc.'

https://postgres.sophia.org/

The following CREATE TABLE statement creates a table called 'artist' that consists of the artist_id as the primary key that is auto-incremented, the first_name, and the last_name.

CREATE TABLE artists(

artist_id serial INT,

first_name VARCHAR (50),

last_name VARCHAR (50)

);

Identify the line of code that would either generate a syntax, logical, or requirements error in this CREATE TABLE statement.

•

https://postgres.sophia.org/

Using the AND or OR statement, filter the employee table for employees who have a title starting with Sales and an address containing Ave.

Identify the last name of the 3rd record.

•

Margaret

•

Peacock

•

Steve

•

Johnson

https://postgres.sophia.org/

Using the GROUP BY and HAVING clauses, filter the customer table by country.

How many countries have less than 5 customers?

•

https://postgres.sophia.org/

Which of the following would set the track name to "A New Road" for the track with a track_id equal to 5?

•

UPDATE track

set name = A New Road

where track_id = 5

•

UPDATE track

set name in 'A New Road'

where track_id = 5

•

UPDATE track

set name = 'A New Road'

where track_id = 5

•

UPDATE track

set name = 'A New Road'

where album_id = 5

https://postgres.sophia.org/

Consider the following new table:

CREATE TABLE locationlist(

locationlist_id SERIAL PRIMARY KEY,

city VARCHAR NOT NULL,

country VARCHAR NOT NULL,

postal_code VARCHAR

);

Given this new table, which INSERT statement would query from the customer table to insert the city, country, and postal_code of all customers in the right columns?

•

INSERT INTO locationlist (city, country, postal_code)

SELECT city, country, postal_code FROM customer;

•

INSERT INTO locationlist (postal_code, country, city)

SELECT city, country, postal_code FROM customer;

•

INSERT INTO locationlist

SELECT city, country, postal_code FROM customer;

•

INSERT INTO locationlist (locationlist_id, city, country, postal_code)

SELECT city, country, postal_code FROM customer;

https://postgres.sophia.org/

Using the GROUP BY clause and the count aggregate function, filter the track table to group the tracks based on album_id.

How many tracks are in album 8?

•

https://postgres.sophia.org/

Which of the following is a correctly formatted INSERT statement that will successfully add a record into the playlist table?

•

insert into playlist (playlist_id, name) values (30, 'New Age Playlist' )

•

insert into playlist (playlist_id, name) values (40, New Age Playlist)

•

insert into playlist (playlist_id, name) values ('New Age Playlist', 35)

•

insert into playlist (playlist_id, name) values ('New Age Playlist' )

https://postgres.sophia.org/

Using the GROUP BY and HAVING clauses, filter the customer table by country.

How many countries have less than 2 customers?

•

https://postgres.sophia.org/

The following CREATE TABLE statement creates a table called 'album' that consists of the album_id as the primary key, the title, and the artist_id.

CREATE TABLE album(

album_id int PRIMARY KEY

title VARCHAR (160),

artist_id int

);

Identify the missing item that would generate an error in this CREATE TABLE statement.

•

Missing comma

•

Missing column name

•

Missing data type

•

Missing parentheses

https://postgres.sophia.org/

Consider the following table:

CREATE TABLE artwork(

artwork_id SERIAL PRIMARY KEY,

artwork_name VARCHAR NOT NULL

);

Which of the following is a correctly formatted INSERT statement that will successfully add a new record that uses the auto-incremented primary key into this table?

•

insert into artwork (artwork_id, artwork_name) values (nextval, 'Mona Lisa')

•

insert into artwork (artwork_id, artwork_name) values (1, 'Mona Lisa' )

•

insert into artwork (artwork_name) values ('Mona Lisa' )

•

insert into artwork (artwork_name) values (Mona Lisa)

https://postgres.sophia.org/

Which of these constraints verifies that data in a column is based on columns in other tables?

•

PRIMARY KEY

•

CHECK

•

FOREIGN KEY

•

UNIQUE

https://postgres.sophia.org/

Which of the following would set the media_type_id to 1 on all tracks where the album_id is set to 3?

•

UPDATE track

set media_type_id = 1

where album_id = 3

•

UPDATE track

where album_id = 3

set media_type_id = 1

•

UPDATE track

set media_type_id = 1

where track_id = 3

•

UPDATE track

set media_type_id = 3

where album_id = 1

https://postgres.sophia.org/

Using the LIKE operator in the WHERE clause, use the necessary wildcards to filter the album table to find the albums that have Disc 1 in the title.

Identify the 6th album ID.

•

https://postgres.sophia.org/

Using the ORDER BY clause, sort the customer table by the last name of the customer in descending order and identify the 14th name in the list from among the answer options.

•

Michelle

•

Sampaio

•

Goyer

•

Brooks

https://postgres.sophia.org/

Given the invoice table and the data that it contains, and assuming that you have the rights to modify the table, which of the following ALTER TABLE statements would work without errors?

•

ALTER TABLE invoice ALTER COLUMN billing_state TYPE BOOLEAN;

•

ALTER TABLE customer ALTER COLUMN billing_city TYPE VARCHAR (1);

•

ALTER TABLE invoice ALTER COLUMN total TYPE VARCHAR (100);

•

ALTER TABLE invoice ALTER total TYPE VARCHAR (1);

https://postgres.sophia.org/

Identify the correctly constructed ALTER TABLE statement that removes the age column from the user table.

•

ALTER TABLE user DROP age int;

•

ALTER TABLE user ADD age int;

•

ALTER TABLE user DROP age;

•

ALTER TABLE user DROP age_now;

https://postgres.sophia.org/

Using the SELECT statement, query the track table to find the total cost of the tracks on album_id 10, rounded to the nearest cent.

•

13.9

•

13.86

https://postgres.sophia.org/

Using the WHERE and HAVING clauses, filter the track table for the tracks with the media_type_id set to 1, grouped by the genre_id having the number of tracks greater than 10. Provide the list of genre_id's and the count of tracks that fit these criteria.

Which of the following queries would provide the correct results?

•

SELECT genre_id, count(track_id)

FROM track

WHERE media_type_id = 1

HAVING count(track_id) > 10

GROUP BY genre_id

•

SELECT genre_id, count(track_id)

FROM track

WHERE media_type_id = 1

GROUP BY genre_id

HAVING count(track_id) > 10

•

SELECT genre_id, sum(track_id)

FROM track

WHERE media_type_id = 1

GROUP BY genre_id

HAVING sum(track_id) > 10

•

SELECT genre_id, count(track_id)

FROM track

WHERE count(track_id) > 10

GROUP BY genre_id

HAVING media_type_id = 1

https://postgres.sophia.org/

Identify the correctly constructed ALTER TABLE statement to add a UNIQUE constraint to the column driver_license with the constraint name driver_license_unique on the table called 'identification'.

•

ALTER TABLE identification ADD CONSTRAINT driver_license UNIQUE (driver_license_unique);

•

ALTER TABLE identification ADD CONSTRAINT driver_license UNIQUE (driver_license);

•

ALTER TABLE identification ADD UNIQUE driver_license CONSTRAINT (driver_license_unique);

•

ALTER TABLE identification ADD CONSTRAINT driver_license_unique UNIQUE (driver_license);

https://postgres.sophia.org/

Using the SELECT statement, query the invoice table to find the average total cost for all orders placed between 2011-01-01 and 2012-01-01.

•

5.7063106796116505

•

5.7357723577235772

•

5.8095238095238095

•

https://postgres.sophia.org/

Using the LIKE operator in the WHERE clause, filter the customer table to list the individuals that have a phone number ending with 88.

Identify the 2nd individual's country.

•

Canada

•

Toronto

•

India

•

USA

https://postgres.sophia.org/

Using the IN operator, filter the invoice table to find those with the billing city set to Paris, London, New York, or Brussels.

Identify the billing postal code of the 4th record.

•

75009

•

SW1V 3EN

•

1000

•

75002

https://postgres.sophia.org/

Using the SELECT statement, query the invoice table to find the total cost for all orders placed by the customer_id that is equal to 2.

•

2313

•

https://postgres.sophia.org/

Using the AND or OR statement, filter the album table for an album that has Disc 1 in the title or Disc 2 in the title.

Identify the album ID of the 4th record.

•

https://postgres.sophia.org/

Select the correctly constructed CHECK constraint to validate the signup_date column of type data, to ensure that values placed into it are between 2020-01-01 and 2025-01-01.

•

CHECK (signup_date BETWEEN 2020-01-01 AND 2025-01-01)

•

CHECK (signup_date BETWEEN '2020-01-01' OR '2025-01-01')

•

CHECK (signup_date BETWEEN '2020-01-01' AND '2025-01-01')

•

CHECK (signup_date IN '2020-01-01' AND '2025-01-01')

https://postgres.sophia.org/

Given the invoice table and the data that it contains, and assuming that you have the rights to modify the table, which of the following ALTER TABLE statements would create an error?

•

ALTER TABLE invoice ALTER COLUMN billing_state TYPE VARCHAR (100);

•

ALTER TABLE invoice ALTER COLUMN billing_city TYPE VARCHAR (100);

•

ALTER TABLE invoice ALTER COLUMN invoice_id TYPE VARCHAR (50);

•

ALTER TABLE invoice ALTER COLUMN total TYPE VARCHAR (100);

https://postgres.sophia.org/

USING the SELECT statement, query the track table ordered by the bytes. Set the LIMIT to 5 and OFFSET to 5.

What is the name of the last row returned?

•

Bossa

•

Deixa Entrar

•

Snowballed

•

Evil Walks

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 genre;

DROP TABLE album;

DROP TABLE artist;

•

DROP TABLE genre;

DROP TABLE album;

DROP TABLE artist;

•

DROP TABLE customer;

DROP TABLE invoice;

DROP TABLE invoice_line;

•

DROP TABLE invoice_line;

DROP TABLE playlist_track;

DROP TABLE playlist;

https://postgres.sophia.org/

Using the WHERE clause, filter the invoice table to find the invoices dated January 15th, 2011.

Identify the 2nd customer ID of the invoices.

•

https://postgres.sophia.org/

Using the SELECT statement, query the customer table to find the number of customers that live in the country USA.

•

https://postgres.sophia.org/

Which of these SELECT statements would successfully display exactly three columns of data from the employee table?

•

SELECT *

FROM employee;

•

SELECT hiredate, postalcode, employeeid

FROM employee;

•

SELECT hire_date, postal_code, employee_id

FROM employee;

•

SELECT hire_date;

postal_code;

employee_id

FROM employee;

https://postgres.sophia.org/

What is the main function of the SELECT clause in SQL?

•

To apply conditions to filter the dataset

•

To retrieve rows from one or more table columns

•

To identify one or more tables as the source for a query

https://postgres.sophia.org/

Which of the following is a correctly formatted INSERT statement that will insert three records into the album table?

•

insert into album (artist_id, title, album_id) values (1, 'Genesis', 450), (1, 'Self-Titled', 451), (1, 'Lyrics', 452)

•

insert into album (album_id, title, artist_id) values (1, 'Genesis', 450), (1, 'Self-Titled', 451), (1, 'Lyrics', 452)

•

insert into album (artist_id, title, album_id) values (1, 'Genesis', 450), (1, 'Self-Titled', 451), (1, 'Lyrics', 450)

•

insert into album (artist_id, title, album_id) values (1, 'Genesis', 450) (1, 'Self-Titled', 451) (1, 'Lyrics', 452)

https://postgres.sophia.org/

Which of the following statement(s) would successfully delete the invoice_line_id 5 from the invoice_line table?

•

DELETE FROM invoice_line WHERE invoice_line_id = 5;

DELETE FROM invoice WHERE invoice_line_id = 5;

•

DELETE FROM invoice_line WHERE invoice_line_id = 5;

•

DELETE FROM invoice WHERE invoice_line_id = 353;

•

DELETE FROM invoice WHERE invoice_line_id = 5;

DELETE FROM invoice_line WHERE invoiceline__id = 5;

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

•

General Manager

•

Sales Manager

https://postgres.sophia.org/

Using the BETWEEN operator, filter the album table to find the albums with an artist ID between 18 and 30.

Identify the 7th album ID.

•

127

https://postgres.sophia.org/

Identify the SQL command that uses an aggregate function that could be used to find the employee in the employee table who has been at the company the longest.

•

SELECT max(hire_date) FROM employee;

•

SELECT large(hire_date) FROM employee;

•

SELECT small(hire_date) FROM employee;

•

SELECT min(hire_date) FROM employee;

https://postgres.sophia.org/

The following CREATE TABLE statement creates a table called 'album' that consists of the album_id as the primary key that is auto-incremented, the title, and the artist_id.

CREATE TABLE albums(

album_id serial PRIMARY KEY,

title serial VARCHAR,

artist_id int

);

Identify the line of code that would either generate a syntax, logical, or requirements error in this CREATE TABLE statement.

•

https://postgres.sophia.org/

Using the SELECT statement, query the track table to find the maximum bytes where the milliseconds are greater than 11650.

•

1059546140

•

387360

•

319888

•

38747

https://postgres.sophia.org/

Using the AND or OR statement, filter the employee table for employees who have a title starting with Sales and an address containing Ave.

Identify the last name of the 3rd record.

•

Peacock

•

Johnson

•

Margaret

•

Steve

https://postgres.sophia.org/

Select the correctly constructed CHECK constraint to validate the phone_type column of type string, to ensure that values placed into it are either work, home, or mobile.

•

CHECK (phone_type IN ('work', 'home', 'mobile'))

•

CHECK (phone_type = 'work' OR 'home' OR 'mobile')

•

CHECK (phone_type IN 'work', 'home', 'mobile')

•

CHECK (phone_type IN (work, home, mobile))

https://postgres.sophia.org/

Which of the following would set the company of the customer with the customer_id equal to 24, to Apple Inc.?

•

UPDATE customer

SET company = 'Apple Inc.'

AND customer_id = 24

•

UPDATE customer

WHERE customer_id = 24

SET company = 'Apple Inc.'

•

UPDATE customer

SET company = 'Apple Inc.'

WHERE customer_id = 24

•

UPDATE customer

SET company = Apple Inc.

WHERE customer_id = 24

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 Manager

•

IT Manager

•

Sales Support Agent

•

General Manager

https://postgres.sophia.org/

Using the SELECT statement, query the track table to find the average length of a track that has the genre_id equal to 5, rounded to the nearest millisecond.

•

134643

•

394489

•

134644

•

134643.5

https://postgres.sophia.org/

Using the SELECT statement, query the customer table to find the number of customers that live in the country Canada.

•

https://postgres.sophia.org/

Given the invoice table and the data that it contains, and assuming that you have the rights to modify the table, which of the following ALTER TABLE statements would work without errors?

•

ALTER TABLE invoice ALTER COLUMN total TYPE VARCHAR (100);

•

ALTER TABLE invoice ALTER total TYPE VARCHAR (1);

•

ALTER TABLE customer ALTER COLUMN billing_city TYPE VARCHAR (1);

•

ALTER TABLE invoice ALTER COLUMN billing_state TYPE BOOLEAN;

https://postgres.sophia.org/

Which of the following queries would provide the correct results?

•

SELECT genre_id, sum(track_id)

FROM track

WHERE media_type_id = 1

GROUP BY genre_id

HAVING sum(track_id) > 10

•

SELECT genre_id, count(track_id)

FROM track

WHERE count(track_id) > 10

GROUP BY genre_id

HAVING media_type_id = 1

•

SELECT genre_id, count(track_id)

FROM track

WHERE media_type_id = 1

GROUP BY genre_id

HAVING count(track_id) > 10

•

SELECT genre_id, count(track_id)

FROM track

WHERE media_type_id = 1

HAVING count(track_id) > 10

GROUP BY genre_id

https://postgres.sophia.org/

Using the SELECT statement, query the track table to find the maximum bytes where the milliseconds are less than 11650.

•

38747

•

319888

•

1059546140

•

387360

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 genre;

DROP TABLE album;

DROP TABLE artist;

•

DROP TABLE genre;

DROP TABLE album;

DROP TABLE artist;

•

DROP TABLE customer;

DROP TABLE invoice;

DROP TABLE invoice_line;

•

DROP TABLE invoice_line;

DROP TABLE playlist_track;

DROP TABLE playlist;

https://postgres.sophia.org/

The following CREATE TABLE statement creates a table called 'users' that consists of the user_id as the primary key, the username, and the password.

CREATE TABLE users(

user_id int PRIMARY KEY,

username VARCHAR 50,

password VARCHAR (50)

);

Identify the line of code that would generate an error in this CREATE TABLE statement.

•

https://postgres.sophia.org/

Which of the following is a correctly formatted INSERT statement that will insert three records into the artist table?

•

insert into artist (name, artist_id) values (550, 'Lady Gaga'), (551, 'Ed Sheeran'), (552, 'Taylor Swift')

•

insert into artist (artist_id, name) values (550, 'Lady Gaga') (551, 'Ed Sheeran') (552, 'Taylor Swift')

•

insert into artist (artist_id, name) values (550, 'Lady Gaga'), (551, 'Ed Sheeran'), (552, 'Taylor Swift')

•

insert into artist (artist_id, name) values (550, 'Lady Gaga'), (551, 'Ed Sheeran'), (551, 'Taylor Swift')

https://postgres.sophia.org/

What is the main function of the FROM clause in SQL?

•

To retrieve zero or more rows from one or more database columns

•

To apply conditions to filter the dataset

•

To identify one or more tables as the source for a query

https://postgres.sophia.org/

Using the GROUP BY clause and the count aggregate function, filter the track table to group the tracks based on album_id.

How many tracks are in album 179?

•

https://postgres.sophia.org/

Using the LIKE operator in the WHERE clause, use the necessary wildcards to filter the album table to find the albums that have Disc 1 in the title.

Identify the 6th album ID.

•

https://postgres.sophia.org/

Which of the following statement(s) would successfully delete the playlist_id 8 from the playlist table?

•

DELETE FROM playlist_track WHERE playlist_id_id = 8;

•

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;

https://postgres.sophia.org/

Using the BETWEEN operator, filter the album table to find the albums with an artist ID between 5 and 10.

Identify the 5th album ID.

•

https://postgres.sophia.org/

Consider the following table:

CREATE TABLE video(

video_id SERIAL PRIMARY KEY,

video_name VARCHAR NOT NULL

);

Which of the following is a correctly formatted INSERT statement that will successfully add a new record that uses the auto-incremented primary key into this table?

•

insert into video (video_id, video_name) values (1, 'home video - first day of school' )

•

insert into video (video_name) values (home video - first day of school)

•

insert into video (video_name) values ('home video - first day of school' )

•

insert into video (video_id, video_name) values (nextval, 'home video - first day of school')

https://postgres.sophia.org/

Identify the SQL command that uses an aggregate function that could be used to find the newest employee in the employee table.

•

SELECT large(hire_date) FROM employee;

•

SELECT max(hire_date) FROM employee;

•

SELECT small(hire_date) FROM employee;

•

SELECT min(hire_date) FROM employee;

https://postgres.sophia.org/

Which of these SELECT statements would successfully display exactly four columns of data from the track table?

•

SELECT *

FROM track;

•

SELECT track_id, name, album_id, milliseconds

FROM track;

•

SELECT Track_id and name and album_id and milliseconds

FROM track;

•

SELECT track_id

name

album_id

milliseconds

FROM track;

https://postgres.sophia.org/

Using the GROUP BY and HAVING clauses, filter the customer table by country.

How many countries have less than 5 customers?

•

https://postgres.sophia.org/

Identify the correctly constructed ALTER TABLE statement to add a UNIQUE constraint to the column reference_number with the constraint name reference_number_unique on the table called 'document'.

•

ALTER TABLE document ADD CONSTRAINT reference_number_unique UNIQUE (reference_number);

•

ALTER TABLE document ADD UNIQUE reference_number CONSTRAINT (reference_number_unique);

•

ALTER TABLE document ADD CONSTRAINT reference_number UNIQUE (reference_number_unique);

•

ALTER TABLE document ADD CONSTRAINT reference_number UNIQUE (reference_number);

https://postgres.sophia.org/

The following CREATE TABLE statement creates a table called 'album' that consists of the album_id as the primary key that is auto-incremented, the title, and the artist_id.

CREATE TABLE albums(

album_id serial PRIMARY KEY,

title serial VARCHAR,

artist_id int

);

Identify the line of code that would either generate a syntax, logical, or requirements error in this CREATE TABLE statement.

•

https://postgres.sophia.org/

Using the ORDER BY clause, sort the customer table by the first name of the customer in descending order and identify the 12th name in the list from among the answer options.

•

Patrick

•

Ellie

•

Gray

•

Sullivan

https://postgres.sophia.org/

Using the SELECT statement, query the track table to find the total price for all tracks that have the genre_id not equal to 1.

•

0.99

•

2396.94

•

1284.03

•

3552.27

https://postgres.sophia.org/

Using the IN operator, filter the album table to find those with an artist ID of 11, 18, 9, or 4.

Identify the title of the 1st record.

•

BackBeat Soundtrack

•

Jagged Little Pill

•

Afrociberdelia

•

Alcohol Fueled Brewtality Live! [Disc 1]

https://postgres.sophia.org/

Using the SELECT statement, query the invoice table to find the average total cost for all orders placed in the country France.

•

5.6285714285714286

•

5.8021978021978022

•

5.7135278514588859

•

5.64

https://postgres.sophia.org/

Which of the following would set the company of the customers that live in the city of Prague to Humor Inc.?

•

UPDATE customer

WHERE city = 'Prague'

SET company = 'Humor Inc.'

•

UPDATE customer

SET company = Humor Inc.

WHERE city = 'Prague'

•

UPDATE customer

SET company = 'Humor Inc.'

WHERE city = 'Prague'

•

UPDATE customer

SET company = 'Humor Inc.'

AND city = 'Prague'

https://postgres.sophia.org/

Consider the following new table:

CREATE TABLE locationlist(

locationlist_id SERIAL PRIMARY KEY,

city VARCHAR NOT NULL,

country VARCHAR NOT NULL,

postal_code VARCHAR

);

Given this new table, which INSERT statement would query from the customer table to insert the city, country, and postal_code of all customers in the right columns?

•

INSERT INTO locationlist (postal_code, country, city)

SELECT city, country, postal_code FROM customer;

•

INSERT INTO locationlist (city, country, postal_code)

SELECT city, country, postal_code FROM customer;

•

INSERT INTO locationlist

SELECT city, country, postal_code FROM customer;

•

INSERT INTO locationlist (locationlist_id, city, country, postal_code)

SELECT city, country, postal_code FROM customer;

https://postgres.sophia.org/

Using the WHERE clause, filter the invoice table to find the invoices that were dated after March 1st, 2009.

Identify the first customer ID of the invoice.

•

https://postgres.sophia.org/

USING the SELECT statement, query the track table ordered by the track_id. Set the LIMIT to 8 and OFFSET to 40.

What is the name of the last row returned?

•

Hand In My Pocket

•

Not The Doctor

•

Right Through You

•

Ironic

https://postgres.sophia.org/

Which of the following is a correctly formatted INSERT statement that will successfully add a record into the playlist table?

•

insert into playlist (playlist_id, name) values (30, 'New Age Playlist' )

•

insert into playlist (playlist_id, name) values ('New Age Playlist', 35)

•

insert into playlist (playlist_id, name) values (40, New Age Playlist)

•

insert into playlist (playlist_id, name) values ('New Age Playlist' )

https://postgres.sophia.org/

Given the employee table and the data that it contains, and assuming that you have the rights to modify the table, which of the following ALTER TABLE statements would create an error?

•

ALTER TABLE employee ALTER COLUMN city TYPE VARCHAR (100);

•

ALTER TABLE employee ALTER COLUMN email TYPE VARCHAR (100);

•

ALTER TABLE employee ALTER COLUMN employee_id TYPE VARCHAR (50);

•

ALTER TABLE employee ALTER COLUMN postal_code TYPE VARCHAR (100);

https://postgres.sophia.org/

Which of these constraints uses multiple fields as a primary key?

•

UNIQUE

•

COMPOSITE KEY

•

CHECK

•

FOREIGN KEY

https://postgres.sophia.org/

Using the LIKE operator in the WHERE clause, filter the customer table to list the individuals who have an email ending with gmail.com.

Identify the 4th individual's city.

•

Chicago

•

Orlando

•

Prague

•

Salt Lake City

https://postgres.sophia.org/

Identify the correctly constructed ALTER TABLE statement that removes the address column from the customer table.

•

ALTER TABLE customer DROP home_address;

•

ALTER TABLE customer DROP address;

•

ALTER TABLE customer ADD address;

•

ALTER TABLE customer DROP address VARCHAR (100);

https://postgres.sophia.org/

Which of the following is a correctly formatted INSERT statement that will successfully add a record into the artist table?

•

insert into artist (artist_id, name) values (900, Special Artist)

•

insert into artist (artist_id, name) values ('Special Artist',900 )

•

insert into artist (artist_id, name) values (900, 'Special Artist' )

•

insert into artist (artist_id, name) values (19, 'Special Artist' )

https://postgres.sophia.org/

Using the BETWEEN operator, filter the invoice table to find the invoices dated between 2010-01-01 and 2010-01-15.

Identify the 3rd customer ID.

•

https://postgres.sophia.org/

Which of the following UPDATE statements would set the postal_code of any customer who lives in the city of Berlin to 10789?

•

UPDATE customer

WHERE city = 'Berlin'

SET postal_code = '10789'

•

UPDATE customer

SET postal_code

WHERE city = '10789'

•

UPDATE customer

SET postal_code = '10789'

WHERE city = 'Berlin'

•

UPDATE customer

IN postal_code = '10789'

WHERE city = 'Berlin'

https://postgres.sophia.org/

Using the SELECT statement, query the track table to find the total cost of the tracks on album_id 5, rounded to the nearest dollar.

•

14.9

•

14.85

•

https://postgres.sophia.org/

Using the LIKE operator in the WHERE clause, use the necessary wildcards to filter the tracks table to find the tracks that have at least three composers by checking for two / in the composer field.

Identify the 3rd track's song.

•

Please Mr. Postman

•

My Time After Awhile

•

Girassol

•

Long Tall Sally

https://postgres.sophia.org/

Consider the following new table:

CREATE TABLE contact(

user_id SERIAL PRIMARY KEY,

phone VARCHAR NOT NULL

);

Given this new table, which INSERT statement would query from the customer table to insert the phone number of all customers that have an email address that contains the word "apple" in it?

•

INSERT INTO contact (phone)

SELECT phone FROM customer

WHERE email LIKE '%apple%';

•

INSERT INTO contact (user_id, phone)

SELECT phone FROM customer

WHERE email LIKE %apple%;

•

INSERT INTO contact (phone)

SELECT phone FROM customer;

•

INSERT INTO contact

SELECT phone FROM customer

WHERE email LIKE '%apple%';

https://postgres.sophia.org/

Using the WHERE clause, filter the invoice table to find the invoices dated prior to January 19th, 2009.

Identify the invoice date closest to that invoice.

•

2009-01-19

•

2009-02-01

•

2009-01-01

•

2009-01-11

https://postgres.sophia.org/

Consider the following table:

CREATE TABLE event(

event_id SERIAL PRIMARY KEY,

event_name VARCHAR NOT NULL

);

Which of the following is a correctly formatted INSERT statement that will successfully add a new record that uses the auto-incremented primary key into this table?

•

insert into event (event_id, event_name) values (1, 'Homework Due' )

•

insert into event (event_id, event_name) values (nextval, 'Homework Due')

•

insert into event (event_name) values (Homework Due)

•

insert into event (event_name) values ('Homework Due' )

https://postgres.sophia.org/

Given the customer table and the data that it contains, and assuming that you have the rights to modify the table, which of the following ALTER TABLE statements would work without errors?

•

ALTER TABLE customer ALTER COLUMN company TYPE TEXT;

•

ALTER TABLE customer ALTER postal_code TYPE VARCHAR (1);

•

ALTER TABLE customer ALTER COLUMN state TYPE int;

•

ALTER TABLE customer ALTER COLUMN city VARCHAR (100);

https://postgres.sophia.org/

Using the GROUP BY and HAVING clauses, filter the customer table by country.

How many countries have more than 3 customers?

•

https://postgres.sophia.org/

Using the SELECT statement, query the track table to find the total price for all tracks that have the genre_id equal to 2.

•

128.7

•

0.99

•

3552.27

•

130

https://postgres.sophia.org/

Using the WHERE and HAVING clauses, filter the track table for the tracks having the genre_id set to 1 or 2, grouped by the album_id with a number of tracks greater than 15. Provide the list of album_id's and the sum of unit_price that fit these criteria.

Which of the following queries would provide the correct results?

•

SELECT album_id, sum(unit_price)

FROM track

WHERE count(track_id) > 15

GROUP BY album_id

HAVING genre_id in (1,2)

•

SELECT album_id, sum(unit_price)

FROM track

HAVING count(track_id) > 15

WHERE genre_id in (1,2)

GROUP BY album_id

•

SELECT album_id, count(unit_price)

FROM track

WHERE genre_id in (1,2)

GROUP BY album_id

HAVING sum(track_id) > 15

•

SELECT album_id, sum(unit_price)

FROM track

WHERE genre_id in (1,2)

GROUP BY album_id

HAVING count(track_id) > 15

https://postgres.sophia.org/

What is the main function of the WHERE clause in SQL?

•

To apply conditions to filter the dataset

•

To retrieve zero or more rows from one or more database columns

•

To identify one or more tables as the source for a query

https://postgres.sophia.org/

Which of the following queries will use a subquery to find all of the rows in the track table that has the genre_id equal to 2 and has the length of the song in milliseconds longer than the maximum track length of all songs where the genre_id = 3?

•

SELECT *

FROM TRACK

WHERE milliseconds >

(SELECT MAX(milliseconds)

FROM track

WHERE genre_id = 3)

AND genre_id = 2;

•

SELECT *

FROM TRACK

WHERE milliseconds >

SELECT MAX(milliseconds)

FROM track

WHERE genre_id = 3

AND genre_id = 2;

•

SELECT *

FROM TRACK

WHERE milliseconds >

(SELECT MAX(milliseconds)

FROM track

WHERE genre_id = 2)

AND genre_id = 3;

•

SELECT *

FROM TRACK

WHERE milliseconds >

(SELECT MIN(milliseconds)

FROM track

WHERE genre_id = 3)

AND genre_id = 2;

https://postgres.sophia.org/

Which of the following query does NOT correctly use aliases?

•

SELECT i.customer_id, total, last_name

FROM invoice AS i

JOIN customer AS c

USING (customer_id);

•

SELECT i.customer_id, i.total, c.last_name

FROM invoice AS i

JOIN customer AS c

USING (customer_id);

•

SELECT c.customer_id, c.total, c.last_name

FROM invoice AS i

JOIN customer AS c

USING (customer_id);

•

SELECT c.customer_id, i.total, c.last_name

FROM invoice AS i

JOIN customer AS c

USING (customer_id);

https://postgres.sophia.org/

Use the following data model for this question:

Outfit

outfit_id

name

ClothingPiece

piece_id

name

OutfitPiece

outfit_piece_id

outfit_id

piece_id

Which of the following is a situation where an OUTER JOIN could be useful?

•

To view all the clothing pieces, even if they haven't been associated with an outfit in the outfits table

•

To view clothing pieces that are assigned to multiple outfits

•

To view outfits with just one clothing piece

•

To view clothing pieces that have already been assigned to outfits

https://postgres.sophia.org/

Given the following view that has been created, how would you query it to find the customers that have ordered more than $30 over their lifetime as a customer?

CREATE VIEW customer_order

SELECT invoice.customer_id, first_name, last_name, SUM(total) as total

FROM invoice

INNER JOIN customer

ON invoice.customer_id = customer.customer_id

GROUP BY invoice.customer_id, first_name, last_name;

•

SELECT *

FROM order

WHERE SUM(total) > 30;

•

SELECT *

FROM customer_order

WHERE total < 30;

•

SELECT *

FROM customer_order

WHERE total > 30;

•

SELECT *

FROM customer_order

WHERE lifetime_total > 30;

https://postgres.sophia.org/

Genre

genre_id name

1 Broadway

2 Rock

3 Classical

4 Salsa

Track

track_id name genre_id

1 Highway to Hell 2

2 Everlong 2

3 Smells Like Teen Spirit 2

Given the above genres and tracks, how many results will be returned for the following query?

SELECT genre.name, track.name

FROM track

RIGHT JOIN genre

USING (genre_id);

•

https://postgres.sophia.org/

Given the tables have been created without foreign keys added, which of the following ALTER TABLE statements would create a foreign key on the coordinator_id in the email table to reference the coordinator_id in the coordinator table?

•

ALTER TABLE email

ADD CONSTRAINT fk_email

FOREIGN KEY coordinator (coordinator_id)

REFERENCES coordinator_id;

•

ALTER TABLE coordinator

ADD CONSTRAINT fk_email

FOREIGN KEY (coordinator_id)

REFERENCES email (coordinator_id);

•

ALTER TABLE email

ADD CONSTRAINT

FOREIGN KEY (coordinator_id)

REFERENCES coordinator (coordinator_id);

•

ALTER TABLE email

ADD CONSTRAINT fk_email

FOREIGN KEY (coordinator_id)

REFERENCES coordinator (coordinator_id);

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%';

•

Both would be the same as both use the same indices for the join and filter.

•

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.

Check other also

https://postgres.sophia.org/

Why is the following query NOT valid as a NATURAL JOIN?

SELECT customer.first_name, customer.last_name, employee.first_name, employee.last_name

FROM customer

NATURAL JOIN employee;

•

The tables have a shared column, but their column names are not identical, so a JOIN... ON is needed.

•

The syntax used for NATURAL JOIN is not correct.

•

The tables do not have a foreign key relationship.

•

These two tables do not share any identical columns/fields.

https://postgres.sophia.org/

Which of the following statements would create a UNION between all of the columns of invoice where the total of the invoice is greater than 10, combined with the invoices that have an invoice_date greater or equal to 2013-08-01, and combined with the billing country in the USA with the billing state in FL?

•

SELECT *

FROM invoice

WHERE total > 10

SELECT *

FROM invoice

WHERE invoice_date >= '2013-08-01'

UNION

SELECT *

FROM invoice

WHERE billing_country = 'USA' AND billing_state = 'FL';

•

SELECT *

FROM invoice

UNION

WHERE total > 10

SELECT *

FROM invoice

UNION

WHERE invoice_date >= '2013-08-01'

SELECT *

FROM invoice

WHERE billing_country = 'USA' AND billing_state = 'FL';

•

SELECT *

FROM invoice

WHERE total > 10

UNION

SELECT *

FROM invoice

WHERE invoice_date >= '2013-08-01'

UNION

SELECT *

FROM invoice

WHERE billing_country = 'USA' AND billing_state = 'FL';

•

SELECT *

FROM invoice

WHERE total > 10

SELECT *

FROM invoice

WHERE invoice_date >= '2013-08-01'

SELECT *

FROM invoice

WHERE billing_country = 'USA' AND billing_state = 'FL';

https://postgres.sophia.org/

In trying to insert into the playlist_track table, what is the cause of this error?

Query failed because of: error: insert or update on table "playlist_track" violates foreign key constraint "playlist_track_playlist_id_fkey"

•

The playlist_id needs to be added to the playlist table first.

•

The playlist_track_id is not unique.

•

The playlist_id being referenced doesn't exist in the playlist table.

•

The playlist_id in the playlist_track table doesn't exist yet.

https://postgres.sophia.org/

Which results would show if the employee table LEFT JOINed the customer table?

•

Only customers that have employees associated with them and vice-versa

•

All rows from the customer table even if they don't have an employee supporting them

•

All employees, even those that aren't supporting customers

•

Only employees that have customers that they support

Which of the following statements would be a valid DROP VIEW statement for an invoice_verification table that would prevent the removal of a view if there are any objects depending on it?

•

DROP VIEW invoice_verification RESTRICT;

•

DROP VIEW IF EXISTS invoice_verification;

•

DROP VIEW CASCADE invoice_verification;

•

DROP VIEW invoice_verification;

https://postgres.sophia.org/

Which of the following statements would query the invoice_line table to sum up the totals by multiplying the unit_price with the quantity grouped by track_id?

•

SELECT track_id, SUM(quantity * unit_price) AS total

FROM invoice_line

GROUP BY track_id

ORDER BY total DESC;

•

SELECT track_id, (quantity * unit_price) AS total

FROM invoice_line

GROUP BY track_id

ORDER BY total DESC;

•

SELECT track_id, SUM(quantity * unit_price) AS total

FROM invoice_line

ORDER BY total DESC;

•

SELECT track_id, (quantity / unit_price) AS total

FROM invoice_line

GROUP BY track_id

ORDER BY total DESC;

https://postgres.sophia.org/

Animal

animal_id

name

adopter_id

Adopter

adopter_id

name

Given the above data for an adoption agency, what does the result set for the following query represent?

SELECT adopter.name, animal.name

FROM Animal

CROSS JOIN Adopter;

•

It represents every single Animal in the animal table regardless of whether they have been adopted or not.

•

It represents all adopters regardless of whether they have claimed an animal in the animal table.

•

It represents each animal, with the name of their adopter if that has been specified via a Foreign Key.

•

It represents every single animal matches with every single adopter.

https://postgres.sophia.org/

Which of the following data models appropriately models the relationship of recipes and their ingredients?

•

recipe

recipe_id

recipe_name

ingredient_id (FK)

ingredient

ingredient_id

ingredient_name

ingredient_amount

•

recipe

recipe_id

recipe_name

ingredient_name_1

ingredient_amount_1

ingredient_name_2

ingredient_amount_2

•

recipe

recipe_id

recipe_name

ingredient

ingredient_id

recipe_id (FK)

ingredient_name

ingredient_amount

•

ingredient

ingredient_id

recipe_name

ingredient_name

ingredient_amount

https://postgres.sophia.org/

What will be the result of the query based on the following criteria?

<columnname> <= ANY (<subquery>)

•

Returns true if the value is less than the smallest value returned by the subquery.

•

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.

https://postgres.sophia.org/

Which of the following is true of foreign keys?

•

A foreign key can be linked to a NOT NULL column.

•

A foreign key may be linked to a unique column that establishes a 1 to 1 relationship.

•

A foreign key is not needed if the data type is different.

•

Foreign keys are not needed when we require referential integrity.

https://postgres.sophia.org/

Which of the following queries would check for the duplicate of reports_to WITHOUT returning the count of each?

•

SELECT reports_to, count(*)

FROM employee

GROUP BY reports_to

HAVING COUNT(*) > 1;

•

SELECT reports_to

FROM employee

GROUP BY reports_to

HAVING COUNT(*) > 1;

•

SELECT reports_to, count(*)

FROM employee

GROUP BY reports_to

HAVING > 1;

•

SELECT reports_to

FROM employee

HAVING COUNT(*) > 1;

Find Duplicate Rows

Report an issue with this question

https://postgres.sophia.org/

Which of the following statements will show the following result set?

•

SELECT track_id, name

FROM track

JOIN playlist_track

USING (track.name = playlist.name);

•

SELECT track_id, name

FROM track

JOIN playlist_track

WHERE track_id != NULL;

•

SELECT track_id, name

FROM track

JOIN playlist_track

USING (track_id);

•

SELECT track_id, name

FROM track, playlist_track;

https://postgres.sophia.org/

Which of the following is a correctly formatted SELECT statement to show the following result set with the media type's name and the track's name?

•

SELECT media_type.name, track.name

FROM track

JOIN media_type

ON media_type.media_type_id = track.media_type_id;

•

SELECT media_type.name, track.name

FROM track

JOIN media_type

ON mediatype.media_type_id = track.media_type_id;

•

SELECT media_type.name, track.name

FROM track

JOIN media_type

ON media_type.media_type_id = track.track_id;

•

SELECT media_type.name, track.name

FROM track

JOIN media_type

ON media_type.media_type.id = track.media_type.id;

https://postgres.sophia.org/

Which result set requires a JOIN?

•

Showing invoice_dates with invoice totals

•

Showing invoice totals, invoice_dates, and customer_id's

•

Showing customer names with invoice_dates

•

Showing invoice_dates with customer_id's

CONCEPT

Joins

Report an issue with this question

https://postgres.sophia.org/

Which of the following is the valid syntax for creating a VIEW to view a subset of a table?

•

CREATE VIEW album_cost

AS track

GROUP BY album_id;

•

CREATE VIEW album_cost

SELECT album_id, SUM(unit_price)

FROM track

GROUP BY album_id;

•

CREATE VIEW album_cost

SELECT album_id, SUM(unit_price)

FROM track

GROUP BY album_id;

•

CREATE VIEW album_cost

SELECT album_id, SUM(unit_price)

GROUP BY album_id;

https://postgres.sophia.org/

Given the initial tables in our example database, if we wanted to delete artists, tracks, and albums that HAVE NOT been purchased before, in what order do we need to delete data from our tables?

•

track

album

artist

•

artist

album

track

media_type

genre

•

invoice_line

invoice

track

album

artist

•

artist

album

track

https://postgres.sophia.org/

What type of situation would you need to create or replace a view?

•

The view is no longer being used.

•

Data has been imported from other databases.

•

The underlying query is not efficient and needs to be updated.

•

On a daily basis so that the data is refreshed.

https://postgres.sophia.org/

Which of the following is the valid syntax for creating a VIEW to view data from multiple tables?

•

CREATE VIEW customer_order

SELECT invoice.customer_id, first_name, last_name, SUM(total) as total

FROM invoice

INNER JOIN customer

ON invoice.customer_id = customer.customer_id

GROUP BY invoice.customer_id, first_name, last_name;

•

CREATE VIEW customer_order

SELECT invoice.customer_id, first_name, last_name, SUM(total) as total

FROM invoice

INNER JOIN customer

ON invoice.customer_id = customer.customer_id

GROUP BY invoice.customer_id, first_name, last_name;

•

CREATE VIEW customer_order

SELECT invoice.customer_id, first_name, last_name, SUM(total) as total

ON invoice.customer_id = customer.customer_id

GROUP BY invoice.customer_id, first_name, last_name;

•

CREATE VIEW customer order

SELECT invoice.customer_id, first_name, last_name, SUM(total) as total

FROM invoice

INNER JOIN customer

ON invoice.customer_id = customer.customer_id

GROUP BY invoice.customer_id, first_name, last_name;

https://postgres.sophia.org/

Which of the following statements would create a UNION between all of the countries we have customers, employees, or invoices in?

•

SELECT billing_country

FROM invoice

UNION

SELECT country

FROM customer

UNION

SELECT country

FROM employee;

•

SELECT billing_country

FROM invoice

SELECT country

FROM customer

SELECT country

FROM employee

UNION;

•

SELECT country

FROM invoice

UNION

SELECT country

FROM customer

UNION

SELECT country

FROM employee;

•

SELECT billing_country

FROM invoice

SELECT country

FROM customer

SELECT country

FROM employee;

https://postgres.sophia.org/

When is a natural join possible between two tables?

•

When columns in two separate tables contain the same data

•

When the tables being joined both contain a column with the same name and data type

•

When two tables have a foreign key relationship

•

When the tables being joined have only one column each other than the primary key

https://postgres.sophia.org/

Which of the following statements would calculate the average bytes per millisecond grouped by the media_type_id in the track table?

•

SELECT media_type_id, AVG(bytes/milliseconds)

FROM track;

•

SELECT media_type_id, (bytes/milliseconds)

FROM track

GROUP BY media_type_id;

•

SELECT media_type_id, AVG(bytes/milliseconds)

FROM track

GROUP BY media_type_id;

•

SELECT media_type_id, AVG(milliseconds/bytes)

FROM track

GROUP BY media_type_id;

https://postgres.sophia.org/

Which of the following statements will be able to show the following result set?

•

SELECT name, title

FROM album

JOIN track

WHERE album_id != NULL;

•

SELECT name, title

FROM album

JOIN track

USING (track_id);

•

SELECT name, title

FROM album

JOIN track

USING (album_id);

•

SELECT name, title

FROM album, track;

https://postgres.sophia.org/

Genre

genre_id name

1 Broadway

2 Rock

3 Classical

4 Salsa

Track

track_id name genre_id

1 Highway to Hell 2

2 Everlong 2

3 Smells Like Teen Spirit 2

Given the above genres and tracks, how many results will be returned for the following query?

SELECT genre.name, track.name

FROM genre

RIGHT JOIN track

USING (genre_id);

•

https://postgres.sophia.org/

Which of the following data models appropriately models the relationship of coordinators and their email addresses?

•

email_id

coordinator_name

email_type

email_address

•

Coordinator

coordinator_id

coordinator_name

email_id

coordinator_id (FK)

email_type

email_address

•

Coordinator

coordinator_id

coordinator_name

email_type_1

email_address_1

email_type_2

email_address_2

•

Coordinator

coordinator_id

coordinator_name

email_id (FK)

email_id

email_type

email_address

https://postgres.sophia.org/

Which result set requires a JOIN?

•

Showing track name with track ID

•

Showing media type name with track name

•

Showing media type ID with track name

•

Showing track ID, media type ID, and track name

https://postgres.sophia.org/

Which of the following is the valid syntax for creating a VIEW to view data from multiple tables?

•

CREATE VIEW album_artist_names

SELECT album.title, artist.name

FROM album

INNER JOIN artist

ON album.artist_id = artist.artist_id;

•

CREATE VIEW album artist names

SELECT album.title, artist.name

FROM album

INNER JOIN artist

ON album.artist_id = artist.artist_id;

•

CREATE VIEW album_artist_names

SELECT album.title, artist.name

FROM album

INNER JOIN artist

ON album.artist_id = artist.artist_id;

•

CREATE VIEW album_artist_names

SELECT album.title, artist.name

FROM album

ON album.artist_id = artist.artist_id;

https://postgres.sophia.org/

Given the initial tables in our example database, if we wanted to delete tracks, albums, and artists that HAVE been purchased before, in what order do we need to delete data from our tables?

•

invoice_line

invoice

track

album

artist

•

artist

album

track

media_type

genre

•

track

album

artist

•

artist

album

track

https://postgres.sophia.org/

Given the tables have been created without foreign keys added, which of the following ALTER TABLE statements would create a foreign key on the customer_id in the department table to reference the customer_id in the customer table?

•

ALTER TABLE department

ADD CONSTRAINT

FOREIGN KEY (customer_id)

REFERENCES customer (customer_id);

•

ALTER TABLE department

ADD CONSTRAINT fk_department

FOREIGN KEY (customer_id)

REFERENCES customer (customer_id);

•

ALTER TABLE customer

ADD CONSTRAINT fk_department

FOREIGN KEY (customer_id)

REFERENCES department (customer_id);

•

ALTER TABLE department

ADD CONSTRAINT fk_department

FOREIGN KEY customer (customer_id)

REFERENCES customer_id;

https://postgres.sophia.org/

Which of the following statements would be a valid DROP VIEW statement to remove the two views, which will also display an error if either view doesn't exist?

•

DROP VIEW album_cost, album_artist_names CASCADE;

•

DROP VIEW album_cost, album_artist_names;

•

DROP VIEW album_cost AND album_artist_names;

•

DROP VIEW IF EXISTS album_cost, album_artist_names;

https://postgres.sophia.org/

Select the query that properly uses table aliases to show the genres of each track in the Album with the id of 6.

•

SELECT g.name, t.name

FROM t AS track

JOIN g AS genre

USING (genre_id)

WHERE t.album_id=6;

•

SELECT g(name), t(name)

FROM track AS t

JOIN genre AS g

USING (genre_id)

WHERE t(album_id)=6;

•

SELECT g.name, t.name

FROM track AS t

JOIN genre AS g

USING (genre_id)

WHERE t.album_id=6;

•

SELECT genre.name, track.name

FROM track

JOIN genre

USING (genre_id)

WHERE track.album_id=6;

https://postgres.sophia.org/

Which of the following queries will use a subquery to find all of the rows in the track table that has a unit_price of 0.99 and has the length of the song in milliseconds that is longer than the AVG track length of all tracks in the album_id between 5 and 10?

•

SELECT *

FROM TRACK

WHERE milliseconds >

SELECT AVG(milliseconds)

FROM track

WHERE album_id BETWEEN 5 AND 10

AND unit_price = 0.99;

•

SELECT *

FROM TRACK

WHERE milliseconds >

(SELECT AVG(milliseconds)

FROM track

WHERE album_id BETWEEN 5 AND 10)

AND unit_price = 0.99;

•

SELECT AVG(milliseconds)

FROM TRACK

WHERE milliseconds >

(SELECT *

FROM track

WHERE album_id BETWEEN 5 AND 10)

AND unit_price = 0.99;

•

SELECT *

FROM TRACK

WHERE milliseconds >

(SELECT AVG(milliseconds)

FROM track

WHERE unit_price = 0.99)

AND album_id BETWEEN 5 AND 10;

https://postgres.sophia.org/

Which of the following is a correctly formatted SELECT statement to show the following result set with the invoice total, customer's customer_id, and the invoice's billing_state?

•

SELECT total, invoice.customer_id, billing_state

FROM invoice

JOIN customer

ON invoice.customer_id = customer.customer_id;

•

SELECT total, invoice.customer_id, billing_state

FROM invoice

JOIN customer

ON invoice.customer_id = consumer.customer_id;

•

SELECT invoice_total, invoice.customer_id, billing_state

FROM invoice

JOIN customer

ON invoice.invoice_id = customer.customer_id;

•

SELECT invoice_total, invoice.customer_id, billing_state

FROM invoice

JOIN customer

ON invoice.id = customer.id;

https://postgres.sophia.org/

What is incorrect regarding the following statement intended to create a VIEW?

CREATE VIEW priority_invoices AS

FROM invoice

WHERE total > 100;

•

The name "priority_invoices" is not a table that exists in the database.

•

The name of the VIEW belongs after the word "AS".

•

It's not possible to create a view that only shows a subset of the data from a table.

•

The fields that should belong in the result set are not specified.

https://postgres.sophia.org/

Which results would show if the genre table LEFT JOINed the track table?

•

All rows from the track table, even those that have NULL genre_id's

•

Only genres that don't have tracks in the track table

•

All genres, even those with no tracks in the track table

•

Only genres with tracks in the track table

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 only ingredients that are being utilized for particular recipes

•

To view recipes that have ingredients in the ingredients table

•

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

https://postgres.sophia.org/

Given the following queries, which of these would be the most efficient?

1. SELECT customer.*

FROM invoice

INNER JOIN customer

ON customer.city = invoice.billing_city

WHERE COUNTRY like '%m';

2. SELECT *

FROM customer

WHERE city IN

(SELECT billing_city

FROM invoice

WHERE COUNTRY like '%m');

•

Query #2 would be more efficient as it is based on primary and foreign keys.

•

Query #1 would be more efficient as it is based on primary and foreign keys.

•

Query #1 would be more efficient as it is index indices.

•

Both would be the same as both use the same indices for the join and filter.

Check other also

https://postgres.sophia.org/

Which of the following queries would check for duplicates of first names in the customer table and how many there are of each?

•

SELECT first_name, COUNT(*)

FROM customer

WHERE COUNT(*) > 1;

•

SELECT first_name

FROM customer

GROUP BY first_name

HAVING COUNT(*) > 0;

•

SELECT first_name

FROM customer

GROUP BY first_name

HAVING COUNT(*) > 1;

•

SELECT first_name, COUNT(*)

FROM customer

GROUP BY first_name

HAVING COUNT(*) > 1;

https://postgres.sophia.org/

What will be the result of the query based on the following criteria?

<columnname> = ALL (<subquery>)

•

Returns true if the value is equal to every value returned by the subquery.

•

Returns true if the value is not equal to any values returned by the subquery.

•

Returns true if the value is not equal to any value returned by the subquery.

•

Returns true if the value is equal to any value returned by the subquery.

https://postgres.sophia.org/

What type of situation would you need to create or replace a view?

•

On a daily basis so that the data is refreshed.

•

A view has already been created with the same name but needs to be changed.

•

The view is no longer being used.

•

The view needs to have update, insert, and delete statements allowed.

https://postgres.sophia.org/

Outfit

ID: 1, Name: Rainy day outfit

ID: 2, Name: Important meeting outfit

ID: 3, Name: Fancy event outfit

ID: 4, Name: Beach outfit

Piece

ID: 1, Name: Gray button-up shirt

ID: 2, Name: Rainboots

ID: 3, Name: Velvet pants

Given the above data for an outfit generator, how many records would be included in the result set for the following query?

SELECT Outfit.name, Piece.name

FROM Outfit

CROSS JOIN Piece;

•

https://postgres.sophia.org/

Which of the following is true of foreign keys?

•

A foreign key could be linked to a candidate key of a table.

•

A foreign key can be linked to any foreign key.

•

A foreign key should always be linked to a primary key of another table.

•

A foreign key is not needed if the data type is different.

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

SELECT album.title, artist.name

FROM album

INNER JOIN artist

ON album.artist_id = artist.artist_id;

•

SELECT *

FROM album, artist

ORDER BY name ASC, title DESC;

•

SELECT *

FROM album_artist_names

ORDER BY name DESC, title;

•

SELECT *

FROM album_artist_names

ORDER BY name, title DESC;

•

SELECT *

FROM album_artist_names

ORDER BY name DESC, title ASC;

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 track has to be deleted first before the playlist is deleted.

•

The playlist_id doesn't exist in the playlist_track table.

•

The playlist_track table has a reference to the playlist_id that is being deleted.

https://postgres.sophia.org/

Which of the following is true of foreign keys?

•

A foreign key should always be linked to a primary key of another table.

•

A foreign key can be linked to any foreign key.

•

Tables could be created first to avoid having to create foreign keys in order.

•

A foreign key is not needed if the data type is different.

https://postgres.sophia.org/

Which of the following statements would create a UNION between all of the countries we have customers, employees, or invoices in?

•

SELECT country

FROM invoice

UNION

SELECT country

FROM customer

UNION

SELECT country

FROM employee;

•

SELECT billing_country

FROM invoice

SELECT country

FROM customer

SELECT country

FROM employee;

•

SELECT billing_country

FROM invoice

SELECT country

FROM customer

SELECT country

FROM employee

UNION;

•

SELECT billing_country

FROM invoice

UNION

SELECT country

FROM customer

UNION

SELECT country

FROM employee;

https://postgres.sophia.org/

•

ALTER TABLE customer

ADD CONSTRAINT fk_department

FOREIGN KEY (customer_id)

REFERENCES department (customer_id);

•

ALTER TABLE department

ADD CONSTRAINT

FOREIGN KEY (customer_id)

REFERENCES customer (customer_id);

•

ALTER TABLE department

ADD CONSTRAINT fk_department

FOREIGN KEY (customer_id)

REFERENCES customer (customer_id);

•

ALTER TABLE department

ADD CONSTRAINT fk_department

FOREIGN KEY customer (customer_id)

REFERENCES customer_id;

https://postgres.sophia.org/

Given the initial tables in our example database, if we wanted to delete tracks, albums, and artists that HAVE been purchased before, in what order do we need to delete data from our tables?

•

track

album

artist

•

artist

album

track

•

invoice_line

invoice

track

album

artist

•

artist

album

track

media_type

genre

https://postgres.sophia.org/

Which of the following query does NOT correctly use aliases?

•

SELECT i.customer_id, i.total, c.last_name

FROM invoice AS i

JOIN customer AS c

USING (customer_id);

•

SELECT c.customer_id, i.total, c.last_name

FROM invoice AS i

JOIN customer AS c

USING (customer_id);

•

SELECT i.customer_id, total, last_name

FROM invoice AS i

JOIN customer AS c

USING (customer_id);

•

SELECT c.customer_id, c.total, c.last_name

FROM invoice AS i

JOIN customer AS c

USING (customer_id);

https://postgres.sophia.org/

Which of the following statements will show the following result set?

•

SELECT track_id, name

FROM track

JOIN playlist_track

WHERE track_id != NULL;

•

SELECT track_id, name

FROM track

JOIN playlist_track

USING (track.name = playlist.name);

•

SELECT track_id, name

FROM track

JOIN playlist_track

USING (track_id);

•

SELECT track_id, name

FROM track, playlist_track;

https://postgres.sophia.org/

Which result set requires a JOIN?

•

Showing all album titles with both artist ID and album ID

•

Showing artist_id with album title

•

Showing all album_id's with artist names

•

Showing artist names with artist_id's

https://postgres.sophia.org/

Which results would show if the artist table LEFT JOINed the album table?

•

All artists, even those with no albums in the album table

•

All rows from the album table, even those that have NULL artist_id's

•

Only artists from the artist table that have albums

•

Only rows from the artist table that do not have related albums in the album table

https://postgres.sophia.org/

Which of the following is the valid syntax for creating a VIEW to view a subset of a table?

•

CREATE VIEW USA_customers

AS customer

SELECT *

WHERE country = 'USA';

•

CREATE VIEW USA_customers

SELECT *

FROM customer

WHERE country = 'USA';

•

CREATE VIEW USA_customers

SELECT *

FROM customer

WHERE country = 'USA';

•

CREATE VIEW USA_customers

AS customer

WHERE country = 'USA';

Which of the following statements would be a valid DROP VIEW statement for an invoice_verification table that would prevent the removal of a view if there are any objects depending on it?

•

DROP VIEW CASCADE invoice_verification;

•

DROP VIEW invoice_verification RESTRICT;

•

DROP VIEW IF EXISTS invoice_verification;

•

DROP VIEW invoice_verification;

https://postgres.sophia.org/

Why is the following query valid for a NATURAL join?

SELECT album.title, artist.name

FROM album

NATURAL JOIN artist;

•

The two tables share identical columns.

•

The tables do not have a foreign key relationship.

•

The tables have a shared column, but their column names are not identical which makes it a candidate for a NATURAL JOIN.

•

The query is not a valid NATURAL JOIN query and must use a JOIN... ON instead.

https://postgres.sophia.org/

Given the following view that has been created, how would you query the playlist names and track names both in descending order?

CREATE VIEW playlist_track_names

SELECT playlist.name as playlist_name, track.name as track_name

FROM playlist

INNER JOIN playlist_track

ON playlist.playlist_id = playlist_track.playlist_id

INNER JOIN track

ON playlist_track.track_id = track.track_id;

•

SELECT *

FROM playlist_track_names

ORDER BY track_name, playlist_name;

•

SELECT *

FROM playlist_track_names

ORDER BY playlist_name, track_name DESC;

•

SELECT *

FROM playlist_track_names

ORDER BY playlist_name DESC, track_name DESC;

•

SELECT *

FROM playlist_track_names

ORDER BY playlist_name ASC, track_name ASC;

https://postgres.sophia.org/

•

SELECT *

FROM TRACK

WHERE milliseconds >

(SELECT MAX(milliseconds)

FROM track

WHERE genre_id = 2)

AND genre_id = 3;

•

SELECT *

FROM TRACK

WHERE milliseconds >

SELECT MAX(milliseconds)

FROM track

WHERE genre_id = 3

AND genre_id = 2;

•

SELECT *

FROM TRACK

WHERE milliseconds >

(SELECT MIN(milliseconds)

FROM track

WHERE genre_id = 3)

AND genre_id = 2;

•

SELECT *

FROM TRACK

WHERE milliseconds >

(SELECT MAX(milliseconds)

FROM track

WHERE genre_id = 3)

AND genre_id = 2;

https://postgres.sophia.org/

Use the following data model for this question:

Outfit

outfit_id

name

ClothingPiece

piece_id

name

OutfitPiece

outfit_piece_id

outfit_id

piece_id

Which of the following is a situation where an OUTER JOIN could be useful?

•

To view outfits with just one clothing piece

•

To view all the clothing pieces, even if they haven't been associated with an outfit in the outfits table

•

To view clothing pieces that are assigned to multiple outfits

•

To view clothing pieces that have already been assigned to outfits

https://postgres.sophia.org/

Which of the following queries would check for duplicates of a track's composer in the track table and how many there are of each?

•

SELECT composer, COUNT(*)

FROM track

GROUP BY composer

HAVING COUNT(*) > 1;

•

SELECT composer, COUNT(*)

FROM track

HAVING COUNT(*) > 1;

•

SELECT composer

FROM track

GROUP BY composer

HAVING COUNT(*) > 1;

•

SELECT track_id, COUNT(*)

FROM track

GROUP BY track_id

HAVING COUNT(*) > 1;

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%';

•

Both would be the same as both use the same indices for the join and filter.

•

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.

Check other also

https://postgres.sophia.org/

Which of the following is the valid syntax for creating a VIEW to view data from multiple tables?

•

CREATE VIEW album_artist_names

SELECT album.title, artist.name

FROM album

ON album.artist_id = artist.artist_id;

•

CREATE VIEW album_artist_names

SELECT album.title, artist.name

FROM album

INNER JOIN artist

ON album.artist_id = artist.artist_id;

•

CREATE VIEW album artist names

SELECT album.title, artist.name

FROM album

INNER JOIN artist

ON album.artist_id = artist.artist_id;

•

CREATE VIEW album_artist_names

SELECT album.title, artist.name

FROM album

INNER JOIN artist

ON album.artist_id = artist.artist_id;

https://postgres.sophia.org/

In trying to insert into the playlist_track table, what is the cause of this error?

Query failed because of: error: insert or update on table "playlist_track" violates foreign key constraint "playlist_track_playlist_id_fkey"

•

The playlist_id needs to be added to the playlist table first.

•

The playlist_id being referenced doesn't exist in the playlist table.

•

The playlist_track_id is not unique.

•

The playlist_id in the playlist_track table doesn't exist yet.

https://postgres.sophia.org/

Which of the following data models appropriately models the relationship of companies and their phone numbers?

•

Company

company_id

company_name

phone_id (FK)

Phone

phone_id

phone_type

phone_number

•

Company

company_id

company_name

Phone

phone_id

company_id (FK)

phone_type

phone_number

•

Phone

phone_id

company_name

phone_type

phone_number

•

Company

company_id

company_name

phone_type_1

phone_number_1

phone_type_2

phone_number_2

https://postgres.sophia.org/

Genre

genre_id name

1 Broadway

2 Rock

3 Classical

4 Salsa

Track

track_id name genre_id

1 Highway to Hell 2

2 Everlong 2

3 Smells Like Teen Spirit 2

Given the above genres and tracks, how many results will be returned for the following query?

SELECT genre.name, track.name

FROM track

RIGHT JOIN genre

USING (genre_id);

•

https://postgres.sophia.org/

What will be the result of the query based on the following criteria?

<columnname> <= ANY (<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 or equal to the smallest value returned by the subquery.

•

Returns true if the value is less than the smallest value returned by the subquery.

https://postgres.sophia.org/

Which of the following statements would query the invoice_line table to sum up the totals by multiplying the unit_price with the quantity grouped by track_id?

•

SELECT track_id, (quantity * unit_price) AS total

FROM invoice_line

GROUP BY track_id

ORDER BY total DESC;

•

SELECT track_id, SUM(quantity * unit_price) AS total

FROM invoice_line

ORDER BY total DESC;

•

SELECT track_id, SUM(quantity * unit_price) AS total

FROM invoice_line

GROUP BY track_id

ORDER BY total DESC;

•

SELECT track_id, (quantity / unit_price) AS total

FROM invoice_line

GROUP BY track_id

ORDER BY total DESC;

https://postgres.sophia.org/

What type of situation would you need to create or replace a view?

•

On a daily basis so that the data is refreshed.

•

The view needs to have update, insert, and delete statements allowed.

•

The view is no longer being used.

•

A view has already been created with the same name but needs to be changed.

https://postgres.sophia.org/

Animal

animal_id

name

adopter_id

Adopter

adopter_id

name

Given the above data for an adoption agency, what does the result set for the following query represent?

SELECT adopter.name, animal.name

FROM Animal

CROSS JOIN Adopter;

•

It represents all adopters regardless of whether they have claimed an animal in the animal table.

•

It represents each animal, with the name of their adopter if that has been specified via a Foreign Key.

•

It represents every single Animal in the animal table regardless of whether they have been adopted or not.

•

It represents every single animal matches with every single adopter.

https://postgres.sophia.org/

Which of the following is a correctly formatted SELECT statement to show the following result set with the media type's name and the track's name?

•

SELECT media_type.name, track.name

FROM track

JOIN media_type

ON media_type.media_type_id = track.track_id;

•

SELECT media_type.name, track.name

FROM track

JOIN media_type

ON media_type.media_type.id = track.media_type.id;

•

SELECT media_type.name, track.name

FROM track

JOIN media_type

ON mediatype.media_type_id = track.media_type_id;

•

SELECT media_type.name, track.name

FROM track

JOIN media_type

ON media_type.media_type_id = track.media_type_id;

https://postgres.sophia.org/

Which of the following statements will be able to show the following result set?

•

SELECT invoice_id, billing_state, state

FROM invoice, customer;

•

SELECT invoice_id, billing_state, state

FROM inuoice

JOIN consumer

USING (customer_id);

•

SELECT invoice_id, billing_state, state

FROM invoice

WHERE customer_id != NULL;

•

SELECT invoice_id, billing_state, state

FROM invoice

JOIN customer

USING (customer_id);

https://postgres.sophia.org/

Which of the following statements would create a UNION between all of the cities we have customers, employees, or invoices in?

•

SELECT billing_city

FROM invoice

SELECT city

FROM customer

SELECT city

FROM employee

UNION;

•

SELECT city

FROM invoice

UNION

SELECT city

FROM customer

UNION

SELECT city

FROM employee;

•

SELECT billing_city

FROM invoice

UNION

SELECT city

FROM customer

UNION

SELECT city

FROM employee;

•

SELECT billing_city

FROM invoice

SELECT city

FROM customer

SELECT city

FROM employee;

https://postgres.sophia.org/

Which of the following is a correctly formatted SELECT statement to show the following result set with the track's name, the track's millisecond, and the playlist_track's playlist_id?

•

SELECT name, milliseconds, playlist_id

FROM track

JOIN playlist_track

ON track_id;

•

SELECT name, milliseconds, playlist_id

FROM track

JOIN playlist_track

ON track.trackid = playlisttrack.trackid;

•

SELECT name, milliseconds, playlist_id

FROM track

JOIN playlist_track

ON track.playlist_id = playlist_track.playlist_id;

•

SELECT name, milliseconds, playlist_id

FROM track

JOIN playlist_track

ON track.track_id = playlist_track.track_id;

https://postgres.sophia.org/

Which of the following statements would calculate the max bytes per millisecond grouped by the album_id in the track table?

•

SELECT album_id, MAX(bytes/milliseconds)

FROM track

GROUP BY album_id;

•

SELECT album_id, MAX(milliseconds/bytes)

FROM track

GROUP BY album_id;

•

SELECT album_id, MIN(bytes/milliseconds) FROM track

GROUP BY album_id;

•

SELECT album_id, SUM(bytes/milliseconds)

FROM track

GROUP BY album_id;

https://postgres.sophia.org/

What type of situation would you need to create or replace a view?

•

On a daily basis so that the data is refreshed.

•

The view's underlying data has to be changed to other tables.

•

Data has been imported from other databases.

•

The data in the tables have changed.

https://postgres.sophia.org/

Given the tables have been created without foreign keys added, which of the following ALTER TABLE statements would create a foreign key on the organization_id in the donor table to reference the organization_id in the organization table?

•

ALTER TABLE donor

ADD CONSTRAINT

FOREIGN KEY (organization_id)

REFERENCES organization (organization_id);

•

ALTER TABLE donor

ADD CONSTRAINT fk_donor

FOREIGN KEY organization (organization_id)

REFERENCES organization_id;

•

ALTER TABLE organization

ADD CONSTRAINT fk_donor

FOREIGN KEY (organization_id)

REFERENCES donor (organization_id);

•

ALTER TABLE donor

ADD CONSTRAINT fk_donor

FOREIGN KEY (organization_id)

REFERENCES organization (organization_id);

https://postgres.sophia.org/

•

SELECT *

FROM TRACK

WHERE milliseconds >

(SELECT AVG(milliseconds)

FROM track

WHERE unit_price = 0.99)

AND album_id BETWEEN 5 AND 10;

•

SELECT AVG(milliseconds)

FROM TRACK

WHERE milliseconds >

(SELECT *

FROM track

WHERE album_id BETWEEN 5 AND 10)

AND unit_price = 0.99;

•

SELECT *

FROM TRACK

WHERE milliseconds >

SELECT AVG(milliseconds)

FROM track

WHERE album_id BETWEEN 5 AND 10

AND unit_price = 0.99;

•

SELECT *

FROM TRACK

WHERE milliseconds >

(SELECT AVG(milliseconds)

FROM track

WHERE album_id BETWEEN 5 AND 10)

AND unit_price = 0.99;

https://postgres.sophia.org/

Genre

genre_id name

1 Broadway

2 Rock

3 Classical

4 Salsa

Track

track_id name genre_id

1 Highway to Hell 2

2 Everlong 2

3 Smells Like Teen Spirit 2

Given the above genres and tracks, how many results will be returned for the following query?

SELECT genre.name, track.name

FROM genre

RIGHT JOIN track

USING (genre_id);

•

https://postgres.sophia.org/

Which of the following operators will attempt to use a hash or b-tree index?

•

https://postgres.sophia.org/

Which of the following statements would create a role named regular_user with no added features?

•

CREATE ROLE regular_user;

•

CREATE ROLE regular_user CREATEROLE;

•

CREATE ROLE regular_user INHERIT;

•

CREATE ROLE regular_user NOINHERIT;

https://postgres.sophia.org/

Which of the following scenarios reflects the durability property?

•

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. 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.

•

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 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.

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 restore a remote database.

•

We can use any parameters without limitation.

•

We can backup multiple databases at once.

https://postgres.sophia.org/

Which of the following statements would restore only the table 'customer' from the mydatabase database from backup.sql?

•

psql -t customer mydatabase < backup.sql

•

psql -a customer mydatabase < backup.sql

•

psql -t customer < backup.sql

•

psql mydatabase customer < backup.sql

https://postgres.sophia.org/

Which of the following criterion is specific to the isolation property?

•

All SQL requests of a transaction must be completed; if not, the transaction must be aborted.

•

Data used in one transaction cannot be used in another transaction until the first transaction is completed.

•

When a transaction is completed, the database must be in a consistent state.

•

Once transaction changes are done and saved, they cannot be lost.

https://postgres.sophia.org/

Using which type of process between the user input and dynamically created statements can help reduce the number of SQL injection attacks?

•

Coding

•

Posting

•

Filtering

•

Construction

https://postgres.sophia.org/

Which of the following statements will grant update permissions on the customer table to all users?

•

GRANT UPDATE ON 'customer' TO 'public';

•

GRANT UPDATE ON customer TO public;

•

GRANT UPDATE ON customer TO ALL;

•

GRANT UPDATE ON public to customer;

https://postgres.sophia.org/

Which of the following scenarios reflects the atomicity property?

•

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 came back up, Tiffany was able to verify that the address was updated.

•

1. A user attempts to do a product transfer between companies.

2. The quantity of the product is moved from the first company.

3. The quantity is moved to the second company without verifying that it has been deducted from the first company.

4. The transaction is accepted.

•

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. While 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. 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.

https://postgres.sophia.org/

Which of the following sequences of statements would ensure that Jennifer has the privileges of the roles of finance and executive?

•

CREATE ROLE executive NOINHERIT;

CREATE ROLE finance NOINHERIT;

GRANT executive to jennifer;

GRANT executive to finance;

•

CREATE ROLE executive INHERIT;

CREATE ROLE finance INHERIT;

GRANT finance to jennifer;

GRANT executive to jennifer;

•

CREATE ROLE executive NOINHERIT;

CREATE ROLE finance INHERIT;

GRANT finance to jennifer;

GRANT executive to finance;

•

CREATE ROLE executive NOINHERIT;

CREATE ROLE finance NOINHERIT;

GRANT finance to jennifer;

GRANT executive to finance;

https://postgres.sophia.org/

Which of the following statements represents a correctly structured transaction?

•

BEGIN;

COMMIT;

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';

•

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';

COMMIT;

•

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';

COMMIT;

•

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'

COMMIT

https://postgres.sophia.org/

Which type of backup would require no more than 2 backup sets?

•

Full backup and nightly backup

•

Incremental and full backup

•

Differential and incremental backup

•

Differential and full backup

https://postgres.sophia.org/

Which of the following scenarios reflect the consistency property?

•

1. Jeff checks his user profile and sees his email address is set to jeff@gmail.com.

2. Jeff changes his email to set it to jeff@outlook.com but does not click on save yet.

3. A customer rep checks into Jeff's account and sees jeff@gmail.com.

4. Jeff clicks on save.

5. Another customer rep checks Jeff's account and sees jeff@outlook.com.

•

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 transaction is run to import new customers.

2. The import is saved to the logs.

3. The import starts to then save to the disk.

4. The disk fails midway through the update.

5. A new disk replaces the failed disk.

6. The backup is applied and the saving of the data is run from the logs.

•

1. A vendor has 5 orders available.

2. A customer attempts to purchase all 5.

3. The system deducts it from the vendor and adds it to the customer.

4. The vendor now has 0 orders available.

5. The customer has 0 orders purchased.

5. The transaction is saved.

https://postgres.sophia.org/

Which of the following statements would create a user login role named testuser1 with the password set as testpassword1 that is valid until 2042-01-01?

•

CREATE ROLE 'testuser1'

PASSWORD 'testpassword1'

EXPIRES '2042-01-01'

•

CREATE ROLE testuser1

VALID UNTIL '2042-01-01'

PASSWORD 'testpassword1'

•

CREATE ROLE testuser1

PASSWORD 'testpassword1'

VALID UNTIL '2042-01-01'

•

CREATE USER testuser1

PASSWORD testpassword1

VALID UNTIL 2042-01-01

https://postgres.sophia.org/

Which of the following scenarios reflects 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. 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. 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.

https://postgres.sophia.org/

Which of the following permissions are automatically granted to a user?

•

All permissions need to be granted to the user or role.

•

Access to any piece that touches the underlying system

•

Permission to change items in the database

•

Access to enabling extensions

https://postgres.sophia.org/

Consider the following scenario:

1. Update

ROLLBACK

2. Insert

3. Insert

COMMIT

4. Delete

5. Update

COMMIT

In this scenario, which statements would be saved to the database, assuming these are all in a single transaction?

•

1, 2, 3, 4, 5

•

2, 3, 4, 5

•

4, 5

•

2, 3

https://postgres.sophia.org/

Which of the following statements would clean the database objects in a data dump of the mydatabase to backup.sql, prior to outputting the commands to create them?

•

pg_dump mydatabase > backup.sql

•

pg_dump -c mydatabase > backup.sql

•

pg_dump -a mydatabase > backup.sql

•

pg_dump -b mydatabase > backup.sql

https://postgres.sophia.org/

In which of the following cases would it make the most sense to use a b-tree index?

•

SELECT *

FROM customer

WHERE fax LIKE '+55%';

•

SELECT *

FROM album

WHERE artist_id < 5;

•

SELECT *

FROM employee

WHERE address LIKE '%i%';

•

SELECT *

FROM customer

WHERE customer_id > 10 AND customer_id < 20;

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 IF EXISTS myindex;

•

DROP INDEX CONCURRENTLY myindex;

•

DROP INDEX myindex;

https://postgres.sophia.org/

In which of the following cases would it make the most sense to use a hash index?

•

SELECT *

FROM customer

WHERE email LIKE '%gmail%';

•

SELECT *

FROM employee

WHERE employee_id > 2;

•

SELECT *

FROM invoice

WHERE invoice_id > 10 AND invoice_id < 200;

•

SELECT *

FROM track

WHERE album_id = 10;

https://postgres.sophia.org/

Which of the following operators will attempt to use a GiST index?

•

https://postgres.sophia.org/

Which of the following statements represents a correctly structured transaction?

•

UPDATE track

set media_type_id = 1

where album_id = 3;

DELETE FROM playlist

WHERE playlist_id = 1;

INSERT INTO genre (genre_id, name)

VALUES (30, 'Funk' ),(31,'Classical'),(32,'Pop Rock');

COMMIT;

•

BEGIN

UPDATE track

set media_type_id = 1

where album_id = 3

DELETE FROM playlist

WHERE playlist_id = 1

INSERT INTO genre (genre_id, name)

VALUES (30, 'Funk' ),(31,'Classical'),(32,'Pop Rock')

COMMIT

•

BEGIN;

UPDATE track

set media_type_id = 1

where album_id = 3;

DELETE FROM playlist

WHERE playlist_id = 1;

INSERT INTO genre (genre_id, name)

VALUES (30, 'Funk' ),(31,'Classical'),(32,'Pop Rock');

COMMIT;

•

BEGIN;

COMMIT;

UPDATE track

set media_type_id = 1

where album_id = 3;

DELETE FROM playlist

WHERE playlist_id = 1;

INSERT INTO genre (genre_id, name)

VALUES (30, 'Funk' ),(31,'Classical'),(32,'Pop Rock');

https://postgres.sophia.org/

Which of the following scenarios reflect the consistency 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. 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.

•

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.

https://postgres.sophia.org/

Which of the following scenarios reflects the atomicity property?

•

1. Jeff checks his user profile and sees his email address is set to jeff@gmail.com.

2. Jeff changes his email to set it to jeff@outlook.com but does not click on save yet.

3. A customer rep checks into Jeff's account and sees jeff@gmail.com.

4. Jeff clicks on save.

5. Another customer rep checks Jeff's account and sees jeff@outlook.com.

•

1. User 1 has $800 in their online account.

2. User 2 has $600 in their online account.

3. User 1 sends user 2 $100.

4. User 1 has $700 in their account.

5. User 2 has $600 in their account.

6. The transaction is saved.

•

1. A vendor has 5 orders available.

2. A customer attempts to purchase all 5.

3. The system deducts it from the vendor and adds it to the customer.

4. The vendor now has 0 orders available.

5. The customer has 5 orders purchased.

6. The transaction is saved.

•

1. A fairly large transaction is run to import new customers.

2. The import is saved to the logs.

3. The import starts to then save to the disk.

4. The disk fails midway through the update.

5. A new disk replaces the failed disk.

6. The backup is applied and the saving of the data is run from the logs.

https://postgres.sophia.org/

Which of the following scenarios reflects the durability property?

•

1. Jeff checks his user profile and sees his email address is set to jeff@gmail.com.

2. Jeff changes his email to set it to jeff@outlook.com but does not click on save yet.

3. A customer rep checks into Jeff's account and sees jeff@gmail.com.

4. Jeff clicks on save.

5. Another customer rep checks Jeff's account and sees jeff@outlook.com.

•

1. A fairly large transaction is run to import new customers.

2. The import is saved to the logs.

3. The import starts to then save to the disk.

4. The disk fails midway through the update before being committed.

5. A new disk replaces the failed disk.

6. The backup is applied and the saving of the data is run from the logs.

•

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 vendor has 5 orders available.

2. A customer attempts to purchase all 5.

3. The system deducts it from the vendor and adds it to the customer.

4. The vendor now has 0 orders available.

5. The customer has 5 orders purchased.

6. The transaction is saved.

https://postgres.sophia.org/

Which of the following statements would create a role named database_admin which gives them the ability to create databases?

•

CREATE ROLE database_admin INHERIT;

•

CREATE ROLE database_admin NOINHERIT;

•

CREATE ROLE database_admin CREATEROLE;

•

CREATE ROLE database_admin CREATEDB;

https://postgres.sophia.org/

Which type of backup would result in the largest storage space?

•

Nightly backup

•

Full backup

•

Incremental backup

•

Differential backup

https://postgres.sophia.org/

Which of the following statements would only restore the schema, but not the data, of the database mydatabase from backup.sql?

•

pg_restore mydatabase < backup.sql

•

pg_restore -s mydatabase < backup.sql

•

pg_restore -a mydatabase < backup.sql

•

pg_restore -d mydatabase < backup.sql

https://postgres.sophia.org/

In which of the following cases would it make the most sense to use a hash index?

•

SELECT *

FROM employee

WHERE address LIKE '%i%';

•

SELECT *

FROM customer

WHERE customer_id > 10 AND customer_id < 20;

•

SELECT *

FROM album

WHERE artist_id < 5;

•

SELECT *

FROM track

WHERE media_type_id = 1;

https://postgres.sophia.org/

Which of the following properties are specific to the durability property?

•

In the event of system failure, no transactions that were done can be undone.

•

Data used in one transaction cannot be used in another transaction until the first transaction is completed.

•

A transaction should be treated as a single logical unit of work that is indivisible.

•

If any of the transaction parts violates an integrity constraint, the entire transaction must be aborted.

https://postgres.sophia.org/

Which type of attack is often used to get passwords or other information using iterative trial and error?

•

Conditional

•

Row set

•

Schematic

•

Brute force

https://postgres.sophia.org/

In which of the following cases would it make the most sense to use a b-tree index?

•

SELECT *

FROM invoice

WHERE invoice_id > 10 AND invoice_id < 200;

•

SELECT *

FROM employee

WHERE title LIKE '%IT%';

•

SELECT *

FROM customer

WHERE email LIKE '%gmail%';

•

SELECT *

FROM customer

WHERE fax LIKE '%7070';

https://postgres.sophia.org/

Which of the following statements would dump ONLY the table customer from the mydatabase database to backup.sql?

•

pg_dump -a customer mydatabase > backup.sql

•

pg_dump mydatabase customer > backup.sql

•

pg_dump -t customer > backup.sql

•

pg_dump -t customer mydatabase > backup.sql

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 back up a remote server.

•

We can backup the data, schema, or both at the same time.

•

We can compress the backup script.

https://postgres.sophia.org/

Consider the following scenario:

1. Update

2. Insert

3. Insert

4. Delete

5. Update

ROLLBACK

COMMIT

In this scenario, which statements would be saved to the database, assuming these are all in a single transaction?

•

1, 2, 3, 4, 5

•

none

•

https://postgres.sophia.org/

Which of the following DROP INDEX statement parameters are set by default?

•

DROP INDEX IF EXISTS myindex;

•

DROP INDEX myindex CASCADE;

•

DROP INDEX CONCURRENTLY myindex;

•

DROP INDEX myindex RESTRICT;

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 ALL;

•

GRANT INSERT ON track TO public;

•

GRANT INSERT ON 'track' TO 'public';

•

GRANT INSERT ON public to track;

https://postgres.sophia.org/

Which of the following permissions are not automatically granted to a superuser?

•

Permission to change items in the database

•

Access to operating system user accounts

•

Access to any piece that touches the underlying system

•

Access to enabling extensions

https://postgres.sophia.org/

Which of the following statements would create a user login role named temporary_user with the password set as changeme that is valid until 2025-05-01?

•

CREATE ROLE temporary_user

PASSWORD changeme

VALID UNTIL 2025-05-01

•

CREATE ROLE temporary_user

VALID UNTIL '2025-05-01'

PASSWORD 'changeme'

•

CREATE ROLE temporary_user

PASSWORD 'changeme'

EXPIRES '2025-05-01'

•

CREATE ROLE temporary_user

PASSWORD 'changeme'

VALID UNTIL '2025-05-01'

https://postgres.sophia.org/

Which of the following sequences of statements would ensure that Jimmy has the privileges of the roles of health_department and manager?

•

CREATE ROLE manager INHERIT;

CREATE ROLE health_department INHERIT;

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;

•

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 INHERIT;

GRANT health_department to jimmy;

GRANT manager to health_department;

https://postgres.sophia.org/

Which of the following scenarios reflects the isolation property?

•

1. Jeff checks his user profile and sees his email address is set to jeff@gmail.com.

2. Jeff changes his email to set it to jeff@outlook.com but does not click on save yet.

3. A customer rep checks into Jeff's account and sees jeff@gmail.com.

4. Jeff clicks on save.

5. Another customer rep checks Jeff's account and sees jeff@outlook.com.

•

1. A fairly large transaction is run to import new customers.

2. The import is saved to the logs.

3. The import starts to then save to the disk.

4. The disk fails midway through the update.

5. A new disk replaces the failed disk.

6. The backup is applied and the saving of the data is run from the logs.

•

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 vendor has 5 orders available.

2. A customer attempts to purchase all 5.

3. The system deducts it from the vendor and adds it to the customer.

4. The vendor now has 0 orders available.

5. The customer has 5 orders purchased.

6. The transaction is saved.

https://postgres.sophia.org/

Which of the following scenarios reflects 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. 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.

Which of the following statements would only dump the data definitions and not the data of the mydatabase to backup.sql?

•

pg_dump mydatabase > backup.sql

•

pg_dump -d mydatabase > backup.sql

•

pg_dump -s mydatabase > backup.sql

•

pg_dump -a mydatabase > backup.sql

Which of the following statements would create a role named database_admin which gives them the ability to create databases?

•

CREATE ROLE database_admin NOINHERIT;

•

CREATE ROLE database_admin CREATEROLE;

•

CREATE ROLE database_admin CREATEDB;

•

CREATE ROLE database_admin INHERIT;

https://postgres.sophia.org/

In which of the following cases would it make the most sense to use a hash index?

•

SELECT *

FROM album

WHERE email LIKE '%t%';

•

SELECT *

FROM artist

WHERE artist_id < 10;

•

SELECT *

FROM track

WHERE track_id > 1 AND track_id < 15;

•

SELECT *

FROM playlist

WHERE playlist_id = 4;

https://postgres.sophia.org/

Which type of backup would result in the fastest restoration?

•

Differential backup

•

Nightly backup

•

Incremental backup

•

Full backup

https://postgres.sophia.org/

Which of the following statements represents a correctly structured transaction?

•

BEGIN

UPDATE track

set name = 'A New Road'

where track_id = 5;

insert into artist (artist_id, name) values (900, 'Special Artist' );

insert into genre (genre_id, name) values (30, 'Funk' );

COMMIT;

•

BEGIN;

COMMIT;

UPDATE track

set name = 'A New Road'

where track_id = 5;

insert into artist (artist_id, name) values (900, 'Special Artist' );

insert into genre (genre_id, name) values (30, 'Funk' );

•

BEGIN

UPDATE track

set name = 'A New Road'

where track_id = 5

insert into artist (artist_id, name) values (900, 'Special Artist' )

insert into genre (genre_id, name) values (30, 'Funk' )

COMMIT

•

BEGIN;

UPDATE track

set name = 'A New Road'

where track_id = 5;

insert into artist (artist_id, name) values (900, 'Special Artist' );

insert into genre (genre_id, name) values (30, 'Funk' );

COMMIT;

https://postgres.sophia.org/

Which is an advantage of both the command line and the GUI?

•

We can backup from one database and immediately send the result to restore another database.

•

We can continue to restore a database even if there are errors in the file.

•

We can back up a remote server.

•

We can restore a remote database.

https://postgres.sophia.org/

Which of the following scenarios reflects the atomicity property?

•

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.

•

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. While 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. 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 came back up, Tiffany was able to verify that the address was updated.

•

1. A user attempts to do a product transfer between companies.

2. The quantity of the product is moved from the first company.

3. The quantity is moved to the second company without verifying that it has been deducted from the first company.

4. The transaction is accepted.

https://postgres.sophia.org/

What is unique about a superuser compared to a user?

•

A superuser is the same as a regular user but with all permissions granted.

•

A superuser can bypass all permission checks.

•

A superuser has administrative privileges that users can't have.

•

A superuser has their permissions checked but passes all tests.

https://postgres.sophia.org/

Which of the following statements will grant update, delete and insert permissions on the employee table to all users?

•

GRANT UPDATE, INSERT, DELETE ON employee TO ALL;

•

GRANT UPDATE and INSERT AND DELETE ON employee TO ALL;

•

GRANT UPDATE, INSERT, DELETE ON ALL to employee;

•

GRANT UPDATE, INSERT, DELETE ON 'employee' TO 'ALL';

https://postgres.sophia.org/

Consider the following scenario:

1. Update

ROLLBACK

2. Insert

3. Insert

4. Delete

ROLLBACK

5. Update

COMMIT

In this scenario, which statements would be saved to the database, assuming these are all in a single transaction?

•

1, 2, 3, 4, 5

•

2, 3, 4, 5

•

None

https://postgres.sophia.org/

Which of the following statements would create a user login role named master_user with the password set as k489dJs9l that is valid until 2032-12-31?

•

CREATE ROLE master_user

WITH PASSWORD 'k489dJs9l'

EXPIRES '2032-12-31'

•

CREATE ROLE master_user

VALID UNTIL '2032-12-31'

PASSWORD 'k489dJs9l'

•

CREATE ROLE master_user

PASSWORD 'k489dJs9l'

VALID UNTIL '2032-12-31'

•

CREATE ROLE master_user

PASSWORD k489dJs9l

VALID UNTIL 2032-12-31

https://postgres.sophia.org/

Which type of application that acts as an interface to the database is used to perform most SQL injections?

•

Image

•

Text

•

System

•

Web

https://postgres.sophia.org/

Which of the following statements would create a role named database_test which gives them the ability login as a user?

•

CREATE ROLE database_test LOGIN;

•

CREATE ROLE database_test INHERIT;

•

CREATE ROLE database_test CREATEROLE;

•

CREATE ROLE database_test NOINHERIT;

https://postgres.sophia.org/

Which of the following scenarios reflect the consistency 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 95.

•

1. User 1 has $50 in their online account.

2. User 2 has $60 in their online account.

3. User 1 sends user 2 $10.

4. User 1 has $40 in their account.

5. User 2 has $60 in their account.

6. The transaction is saved.

•

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.

https://postgres.sophia.org/

In which of the following cases would it make the most sense to use a b-tree index?

•

SELECT *

FROM invoice

WHERE invoice_id > 10 AND invoice_id < 200;

•

SELECT *

FROM customer

WHERE fax LIKE '%7070';

•

SELECT *

FROM employee

WHERE title LIKE '%IT%';

•

SELECT *

FROM customer

WHERE email LIKE '%gmail%';

https://postgres.sophia.org/

Which of the following sequences 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 NOINHERIT;

GRANT manager to jimmy;

GRANT manager to health_department;

•

CREATE ROLE manager INHERIT;

CREATE ROLE health_department INHERIT;

GRANT health_department to jimmy;

GRANT manager to jimmy;

•

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 health_department to jimmy;

GRANT manager to health_department;

https://postgres.sophia.org/

Which of the following users architects the database?

•

Database administrator

•

System programmer

•

Database designer

•

System administrator

Which of the following would help protect data security during database migrations?

•

Converting schemas

•

Normalizing data

•

Testing for data loss

•

Removing personally identifiable information

https://postgres.sophia.org/

What is the best example of a scenario in which a new database should be created to support the business?

•

An organization would like to create an application to help manage appointments for pet-related services. The mobile application should allow pet owners to schedule services by viewing the availability of pet groomers within the app.

•

A local pet shop would like to offer pet services. They have six pet groomers on staff and use MS Outlook to help with tracking their appointments. The pet owner receives calls from customers and then schedules them on the company computer through Outlook.

•

An organization would like to add pet services as an option to their existing pet store sales database. Services and products should be able to be paid for at the same time.

•

An organization has found an application that can be used to manage pet services for pet owners and pet groomers. Both have to log into the existing site to set up appointments.

Which of these databases is limited in terms of database size?

•

PostgreSQL

•

Oracle

•

Teradata

•

MySQL

Consider the following command:

UPDATE customer

SET customer_id = customer_id --1;

What happens if we run this command in MySQL?

•

The customer table has every customer_id set to the same value as it previously had.

•

An error is thrown due to the --.

•

The customer_id in the customer table for all records is incremented by 1.

•

The customer_id in the customer table for all records is decremented by 1.

Which of the following criteria was a key milestone for the object-relational model created in the mid-1980s?

•

The use of this data model on IBM mainframe systems

•

The creation of a single structure that contained both data and its relationships

•

The existence of support for unstructured data

•

Identification of all of the data to be stored in a collection of tables with each table being independent and each of the rows in a table being related by common values

Which data model(s) introduced the use of a data manipulation language?

•

Network

•

Hierarchical

•

Both hierarchical and network

•

Neither

Which of the following is a key feature of a data warehouse?

•

This type of database has processing that is immediate to when the user makes a request.

•

This type of database is often denormalized with a focus on performance.

•

This type of database focuses on reducing data redundancy.

•

This type of database uses insert, update, and delete statements quickly.

What are the emerging major data models created between the early 2000s to the current day?

•

File system

•

XML Hybrid

•

Key-value store and wide-column store

•

Relational

What is the term related to a specific implementation of ANSI SQL by a vendor?

•

Implementation

•

Version

•

Dialect

•

T-SQL

Which of the following is a key part of conceptual design in an entity relationship model?

•

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.

•

This model resolves the many-to-many relationships to multiple one-to-many relationships.

Which of the following is a key part of logical design in an entity relationship model?

•

In this model, no attributes are specified.

•

This model avoids any database model-specific details.

•

This model converts entities into tables.

•

This model has normalization that occurs.

Which of the following is a key feature of a relational database?

•

It has a broad umbrella of a variety of approaches with data storage and manipulation.

•

It is designed for highly available systems that should provide a consistent, high-quality experience for all users globally.

•

It has flexible data models that make it easy to store and combine data of any structure and allow dynamic changes to the schema.

•

It ensures strong consistency such that applications should be able to read what has been written to the database immediately.

Which of the following ALTER TABLE features is available in SQLite?

•

ADD CONSTRAINT

•

DROP COLUMN

•

ADD COLUMN

•

ALTER COLUMN

https://postgres.sophia.org/

As compared to databases, flat files have __________.

•

more consistency

•

less flexibility

•

more security

•

less redundancy

Which of the following creates one of the most common types of bottlenecks?

•

Transactions competing to access different tables

•

Transactions competing for different data rows in the same table

•

Transactions competing for the same data rows

•

Transactions running in sequence with one another

Which of the following is not a reason to identify and document business rules?

•

It can create a database that will work in any situation.

•

It can help standardize the company's view of the data.

•

It can allow developers to create an accurate data model.

•

It can allow the designer to create appropriate relationships.

Which of the following is a key part of physical design in an entity relationship model?

•

In this model, the characteristics such as location, path, and format are described.

•

This model uses column names instead of attributes.

•

This model resolves the many-to-many relationships to multiple one-to-many relationships.

•

In this model, no primary keys are specified in the entities.

https://postgres.sophia.org/

In the context of a database, what do we get when we process data?

•

Information

•

Raw facts

•

Constraints

•

Knowledge

What notation and relationship does this refer to?

•

Crow's Foot notation showing a 1:1 relationship

•

Chen notation showing an M:N relationship

•

Chen notation showing a 1:M relationship

•

Crow's Foot notation showing a 1:M relationship

Which of the following is not a common commercial database option?

•

SQLite

•

PostgreSQL

•

MariaDB

•

ANSI SQL

Which of the following criteria was a key milestone for the relational model created in the mid-1970s?

•

The identification of all of the data to be stored in a collection of tables with each table being independent and each of the rows in a table being related by common values

•

The creation of a single structure that contained both data and its relationships

•

The existence of support for unstructured data

•

The use of this data model on IBM mainframe systems

Which of the following creates one of the most common types of bottlenecks?

•

Sequential transactions

•

Data cache

•

Shared buffers and locks

•

Unused memory resources

Which of the following criteria was a key milestone for the object-relational model created in the mid-1980s?

•

A focus on high performance and fault tolerance

•

The use of inheritance to have a structure inherit attributes and methods of classes above it

•

The ability to have very large storage in petabytes

•

The creation of a relation or a table as a two-dimensional structure composed of rows and columns

Which of the following is not a reason to identify and document business rules?

•

It can allow the designer to understand business processes.

•

It can allow the designer to create appropriate relationships.

•

It can allow the designer to create appropriate constraints.

•

It can solve issues that are enforced by the application software.

Which of the following is a key feature of a relational database?

•

It is designed for highly available systems that should provide a consistent, high-quality experience for all users globally.

•

It uses primary keys and foreign keys as a means to provide efficient access to data and is supported directly in the database rather than maintained in the application code.

•

It has flexible data models that make it easy to store and combine data of any structure and allow dynamic changes to the schema.

•

It has high scalability and performance that can enable almost unlimited growth.

https://postgres.sophia.org/

Which term is the part of the database system that is the collection of stored facts?

•

Hardware

•

Software

•

Procedures

•

Data

https://postgres.sophia.org/

As compared to databases, flat files have worse __________. This means they have issues with data correctness, reliability, and accuracy.

•

redundancy

•

security

•

flexibility

•

integrity

What is a data type that is implemented differently by database vendors quite frequently?

•

Int

•

Date and time

•

String

•

Char

Which of the following is a key feature of a data warehouse?

•

This type of database uses insert, update and delete statements quickly.

•

This type of database is optimized for a large number of concurrent users making changes or querying the database.

•

This type of database is used to process day-to-day operations in real time.

•

This type of database generally only has data loads with extremely rare updates or deletes to the data.

Which data model(s) introduced the use of a data definition language?

•

Network

•

Hierarchical

•

Both hierarchical and network

•

Neither

Which of the following types of outer joins is implemented in SQLite?

•

LEFT OUTER JOIN

•

RIGHT OUTER JOIN

•

FULL OUTER JOIN

•

All of the above

What is true about table sizes with databases?

•

Databases may have limitations based on the number of table rows or specific storage size, while others are unlimited.

•

There is no maximum size regardless of the database.

•

There is a constant set maximum size due to compliance with ANSI SQL.

•

The maximum size in many cases can be unlimited depending on the amount of disk space allocated for the database.

Which of the following is a key part of conceptual design in an entity relationship model?

•

In this model, all table structures including column names, data types, column constraints, primary keys, foreign keys, and relationships are included.

•

In this model, entities are specified.

•

This model converts entities into tables.

•

This model has normalization that occurs.

Consider the following command:

UPDATE product

SET sale_price = price --5;

What happens if we run this command in MySQL?

•

The sale_price in the product table for all records is decremented by 5 from the price.

•

The sale_price in the product table for all records is incremented by 5 from the price.

•

The product table has every sale_price set to the same value as the price.

•

An error is thrown due to the --.

https://postgres.sophia.org/

In the context of a database, what is knowledge?

•

Raw facts of interest to the end user

•

A process that focuses on data collection, storage, and retrieval

•

A computer structure that holds a collection of related data

•

A body of information and facts about a specific topic

What notation and relationship does this refer to?

•

Crow's Foot notation showing a 1:1 relationship

•

UML notation showing a 1:1 relationship

•

Chen notation showing a 1:1 relationship

•

UML notation showing a 1:M relationship

https://postgres.sophia.org/

What is the best example of a scenario in which a new database should be created to support the business?

•

An organization would like to display the results of an external book review database in real time on their website.

•

An organization would like to add a review system to its existing book purchase database.

•

An organization would like to create a book review application allowing users to identify what books they have purchased, what they have read, and how much they liked a book.

•

An organization would like to pull the current reviews of books from different sites to display on their own website.

Which of the following is not a common commercial database option?

•

SQL Express

•

SQL Server

•

MariaDB

•

MS Access

Which of the following would help protect data security during database migrations?

•

Normalizing data

•

Converting schemas

•

Encrypting the data

•

Testing for data loss

Which of the following is a key part of logical design in an entity relationship model?

•

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 data model includes all table structures including column names, data types, column constraints, primary keys, foreign keys, and relationships.

•

This model resolves the many-to-many relationships to multiple one-to-many relationships.

Which of the following criteria was a key milestone for the relational model created in the mid-1970s?

•

The ability to have very large storage in petabytes

•

The use of inheritance to have a structure inherit attributes and methods of classes above it

•

The creation of a relation or a table as a two-dimensional structure composed of rows and columns

•

A focus on high performance and fault tolerance

What was the fifth generation of major data models created during the mid-1990s?

•

XML Hybrid

•

Object-oriented

•

Relational

•

Key-value store and wide-column store

Which of the following is a key part of physical design in an entity relationship model?

•

This model does not depend on the database management software or the hardware used to implement the model.

•

This model avoids any database model-specific details.

•

This model resolves the many-to-many relationships to multiple one-to-many relationships.

•

This model converts attributes into columns.

Which of the following ALTER TABLE features is NOT available in SQLite?

•

RENAME COLUMN

•

RENAME TABLE

•

ADD CONSTRAINT

•

ADD COLUMN

Which of the following creates one of the most common types of bottlenecks?

•

Data cache

•

Unused memory resources

•

Sequential transactions

•

Shared buffers and locks

Which of the following is not a common commercial database option?

•

MySQL

•

Teradata

•

SQLite

•

Oracle

https://postgres.sophia.org/

What is the best example of a scenario in which a new database should be created to support the business?

•

An organization would like to build a support ticketing system for its custom ticketing application. None of the existing ticketing applications fit the workflow of the organization.

•

An organization would like to create a support ticketing tracking tool that anyone can access and change. There does not need to be any versioning or tracking of who changed items as long as they are being addressed.

•

An organization would like to build a support ticketing system. An open-source solution has the majority of the features that the organization needs. There are some improvements that still need to be made.

•

An organization has found a support ticketing tool that they could use. It is missing some features that they require in which the third party can build and add on.

https://postgres.sophia.org/

__________ is/are the instructions and rules that determine the design and use of the database.

•

Procedures

•

People

•

Hardware

•

Software

Consider the following command:

UPDATE product

SET sale_price = price --5;

What happens if we run this command in MySQL?

•

The product table has every sale_price set to the same value as the price.

•

The sale_price in the product table for all records is decremented by 5 from the price.

•

The sale_price in the product table for all records is incremented by 5 from the price.

•

An error is thrown due to the --.

Which of the following criteria was a key milestone for the relational model created in the mid-1970s?

•

The creation of a single structure that contained both data and its relationships

•

The use of this data model on IBM mainframe systems

•

The identification of all of the data to be stored in a collection of tables with each table being independent and each of the rows in a table being related by common values

•

The existence of support for unstructured data

Which of the following is a key part of logical design in an entity relationship model?

•

This model converts entities into tables.

•

This model has normalization that occurs.

•

This model avoids any database model-specific details.

•

In this model, no attributes are specified.

Which of the following is a key part of conceptual design in an entity relationship model?

•

This model has normalization that occurs.

•

This model converts entities into tables.

•

In this model, entities are specified.

•

In this model, all table structures including column names, data types, column constraints, primary keys, foreign keys, and relationships are included.

https://postgres.sophia.org/

As compared to databases, flat files have __________.

•

less flexibility

•

more consistency

•

less redundancy

•

more security

https://postgres.sophia.org/

In the context of a database, how is data different from information?

•

Data is the total body of knowledge.

•

Data consists of metadata.

•

Data is processed information that has meaning.

•

Data consists of raw facts and has no meaning.

Which of the following is not a reason to identify and document business rules?

•

It can allow developers to create an accurate data model.

•

It can allow the designer to understand business processes.

•

It can be used as a communication tool between users and designers.

•

It can ensure that the rules are based on the interpretation of senior management.

Which of the following is a key part of physical design in an entity relationship model?

•

This model avoids any database model-specific details.

•

This model does not depend on the database management software or the hardware used to implement the model.

•

This model resolves the many-to-many relationships to multiple one-to-many relationships.

•

This model converts attributes into columns.

Which data model(s) introduced the use of a data definition language?

•

Network

•

Hierarchical

•

Both hierarchical and network

•

Neither

What notation and relationship does this refer to?

•

Crow's Foot notation showing a 1:M relationship

•

UML notation showing a 1:M relationship

•

Crow's Foot notation showing a 1:1 relationship

•

Chen notation showing a 1:1 relationship

Which of these databases can be unlimited in terms of database size?

•

Oracle

•

SQL Server

•

PostgreSQL

•

SQLite

Which of the following would help protect data security during database migrations?

•

Testing for data loss

•

Normalizing data

•

Converting schemas

•

Encrypting the data

Which of the following is a key feature of a data warehouse?

•

This type of database is often denormalized with a focus on performance.

•

This type of database uses insert, update, and delete statements quickly.

•

This type of database focuses on reducing data redundancy.

•

This type of database has processing that is immediate to when the user makes a request.

What is the term related to a specific implementation of ANSI SQL by a vendor?

•

T-SQL

•

Dialect

•

Implementation

•

Version

What are the emerging major data models created between the early 2000s to the current day?

•

XML Hybrid

•

Relational

•

Key-value store and wide-column store

•

File system

Which of the following criteria was a key milestone for the object relational model created in the mid-1980s?

•

The existence of support for unstructured data

•

The ability to have very large storage in petabytes

•

The ability to hide the complexities of the model from the user through the use of the database management system

•

The collection of similar structures that consisted of shared attributes and behaviors

Which of the following is a key feature of a relational database?

•

They have a broad umbrella of a variety of approaches with data storage and manipulation.

•

They make sorting, filtering, and computing various calculations with expressive query languages easy.

•

They are designed to be highly available systems that provide a consistent high-quality experience for all users globally.

•

They offer high scalability and performance that can enable almost unlimited growth.

Which of the following is a key feature of a data warehouse?

•

This type of database is used to process day-to-day operations in real time.

•

This type of database focuses on reducing data redundancy.

•

This type of database is optimized to execute a small number of complex queries.

•

This type of database is optimized for a large number of concurrent users making changes or querying the database.

Which of the following is a key part of logical 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.

•

Different database management systems may have different options for this type of model.

•

In this model, no primary keys are specified in the entities.

Which of the following represents a security issue related to database migrations?

•

Having data be read from a third party during the transfer

•

Having databases in different departments

•

Having the physical drives get damaged during migration

•

Having databases in different geographies

Which of the following is a key part of physical design in an entity relationship model?

•

In this model, no attributes are specified.

•

This model adds all of the attributes for each entity.

•

This model has normalization that occurs.

•

This model converts entities into tables.

https://postgres.sophia.org/

As compared to databases, file sizes in flat files can be much larger due to __________.

•

flexibility

•

security

•

redundancy

•

data integrity

Which data model do databases like Oracle, MS SQL Server, Postgres, and MySQL fall under?

•

Object-oriented

•

XML Hybrid

•

Hierarchical and network

•

Relational

Which statement is true of the network data model, but not true of the hierarchical data model?

•

It introduced the use of data commands like SELECT and INSERT.

•

It depicts a set of one-to-many relationships.

•

In this data model, each parent can have multiple children, but a child can only have one parent.

•

It was the first of the widely used data models in the 1970s.

What is a benefit of using a lookup table?

•

It resolves weak entities.

•

There is data modification flexibility.

•

It transforms an M:N relationship into two 1:M relationships.

•

It guarantees that there will be no data redundancies due to functional dependencies.

Is this dataset in 2NF or not? If not, why not?

•

It is already in 2NF.

•

No, there are repeating groups.

•

No, there are no transitive functional dependencies.

•

No, there are columns that are not dependent on the primary key.

https://postgres.sophia.org/

What is wrong with the following ERD meant to show the attributes of an entity?

•

The attributes are not correctly linked to the entity.

•

The symbols used are swapped.

•

There is nothing wrong with the ERD.

•

The primary key is not indicated correctly.

What is a key feature of fifth normal form?

•

This normal form resolves any multivalued dependencies.

•

This normal form has the tables broken down into as many tables as possible to avoid redundancy.

•

Foreign keys are created in this normal form.

•

A key step in this normal form has every determinant in a table as a candidate key.

Review the sample movie ratings ERD after 3NF.

What is the relationship between tables Movie and User?

•

1:1

•

M:N

•

1:M

•

N:1

What type of connection trap could be created due to a convergence of two one-to-many relationships on a single table?

•

Fan trap

•

Chasm trap

•

Design trap

•

System trap

What is true about normalization?

•

It should take into account business rules and data constraints.

•

It reduces the number of joins.

•

Each normal form provides faster performance.

•

It adds layers of data redundancy.

What is the purpose of an associative entity?

•

To combine multiple 1:M relationships into a single M:N relationship

•

To break down an M:N relationship into multiple 1:M relationships

•

To ensure that all relationships in the database are 1:1

•

To eliminate any 1:1 relationships from the database design

Is this dataset in 3NF? If not, why not?

•

Yes, it is in 3NF.

•

No, it is not yet in 2NF.

•

No, there is an issue with the functional dependency.

•

No, there are repeating groups.

https://postgres.sophia.org/

What type of cardinality is demonstrated by the following ERD?

•

A department has multiple professors. A professor is part of multiple departments.

•

A department has a professor. A professor is part of a department.

•

A department has a professor. A professor is part of multiple departments.

•

A department has multiple professors. A professor is part of a department.

Review the sample eCommerce ERD after 3NF.

What is the relationship between tables Product and Order?

•

N:1

•

1:1

•

M:N

•

1:M

https://postgres.sophia.org/

What is used to indicate a weak entity in an ERD?

•

Double diamond

•

Single rectangular box

•

Single diamond

•

Double rectangular box

What is a reason to denormalize a database?

•

To decrease redundancy in the database

•

To remove multivalued dependencies from the database

•

To reduce the database’s processing needs

•

To prioritize data anomalies rather than performance

What is a criterion of a table being in first normal form?

•

For every functional dependency where X is functionally dependent on Y, X should be the super key of the table.

•

No non-prime attributes dependent on the candidate key should be included.

•

All transitive functional dependencies of a non-prime attribute of a super key should be removed.

•

Every cell of a table should have a single value.

Identify the normal form in which we have no independent multivalued dependencies.

•

None of the first three normal forms

•

2NF

•

3NF

•

1NF

What is a disadvantage associated with a simpler ERD design?

•

Simpler application code

•

Slower application development

•

Simpler queries

•

More redundancy

https://postgres.sophia.org/

What is wrong with the following ERD meant to show a relationship between two entities?

•

There is no relationship between the entities.

•

One of the items is set up as an attribute.

•

There is nothing wrong with the ERD.

•

Both of the objects are attributes.

What could be a reason to denormalize a database?

•

We want to eliminate repeating groups.

•

We want to reduce redundancy.

•

We want to remove multivalued dependencies.

•

We want to store pre-aggregated or derived data.

Is this dataset in 1NF? If not, why not?

•

This data set is in 1NF already.

•

There are repeating groups with the actor_names and possibly the genre_names.

•

There are no foreign keys.

•

There is no primary key.

Review the sample eCommerce ERD after 3NF.

What is the relationship between tables Customer and Order?

•

M:N

•

N:1

•

1:1

•

1:M

What is true about normalization?

•

It adds layers of data redundancy.

•

It is typically performed between conceptual and logical data modeling.

•

It increases the chance of data anomalies.

•

Each normal form provides faster performance.

Identify the normal form in which all of the attributes are set up to be dependent on the primary key.

•

3NF

•

None of the first three normal forms

•

2NF

•

1NF

Is this dataset in 3NF? If not, why not?

•

No, it is not yet in 2NF.

•

No, there are repeating groups.

•

No, there is an issue with the functional dependency.

•

Yes, it is in 3NF.

https://postgres.sophia.org/

What type of cardinality is demonstrated by the following ERD?

•

A song can belong to multiple playlists. A playlist can contain multiple songs.

•

A song can belong to a playlist. A playlist can contain multiple songs.

•

A song can belong to multiple playlists. A playlist can contain a song.

•

A song can belong to a playlist. A playlist can contain a song.

What is NOT another name for an associative entity?

•

Strong entity

•

Composite entity

•

Linking table

•

Bridge entity

https://postgres.sophia.org/

What is used to indicate a weak relationship in an ERD in Crow’s Foot notation?

•

Single rectangular box

•

Solid line between entities

•

Dashed line between entities

•

Double rectangular box

Review the sample movie ratings ERD after 3NF.

What is the relationship between tables Movie and Genre?

•

1:M

•

N:1

•

M:N

•

1:1

What is a benefit of using a lookup table?

•

It models pure relationships rather than entities.

•

It transforms an M:N relationship into two 1:M relationships.

•

It resolves weak entities.

•

It centralizes data about the key.

What is introduced as a criterion of a table in second normal form?

•

There should be no repeating groups.

•

All attributes in a table are dependent on the primary key.

•

Each cell of a table should have a single value.

•

There should be repeating groups.

What would need to be done to fix an issue with a chasm trap?

•

Create a foreign key for each primary key in the relationships.

•

Remove the middle table and connect the tables directly.

•

Create a direct link between the two tables rather than depend on the middle relationship.

•

The entire database design needs to be re-evaluated.

https://postgres.sophia.org/

What is wrong with the following ERD meant to show a relationship between two entities?

•

One of the items is set up as an attribute.

•

There is no relationship between the entities.

•

There is nothing wrong with the ERD.

•

Both of the objects are attributes.

What is an advantage associated with a simpler ERD design?

•

Increased storage needs

•

Slower application development

•

More complex application code

•

Simpler SQL queries

What is a key feature of fourth normal form?

•

It is focused on eliminating join dependencies.

•

It should already satisfy the properties of BCNF.

•

It breaks tables down into as many tables as possible to avoid redundancy.

•

It creates multivalued dependencies.

https://postgres.sophia.org/

What is wrong with the following ERD meant to show the attributes of an entity?

•

The primary key is not indicated correctly.

•

The symbols used are swapped.

•

The attributes are not correctly linked to the entity.

•

There is nothing wrong with the ERD.

Why is 5NF less practical than 3NF or 4NF in a real-world setting?

•

It contains very little redundancy but sacrifices performance.

•

It eliminates multivalued dependencies.

•

Each table contains more than one candidate key.

•

It creates too much data redundancy.

https://postgres.sophia.org/

What is wrong with the following ERD meant to show a relationship between two entities?

•

Both of the objects are attributes.

•

There is no relationship between the entities.

•

One of the items is set up as an attribute.

•

There is nothing wrong with the ERD.

Is this dataset in 3NF? If not, why not?

• No, there are still repeating groups.

•

No, there are not enough attributes for the entity.

•

No, it is not yet in 2NF.

•

Yes, it could be considered to be in 3NF.

What could be a reason to denormalize a database?

•

The database may require us to value performance over storage and redundancy issues.

•

We want to eliminate repeating groups.

•

We want to increase performance for inserts, updates, and deletes.

•

We want to increase the number of joins between tables to optimize performance.

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 primary key is not indicated correctly.

•

There is nothing wrong with the ERD.

•

The attributes are not correctly linked to the entity.

https://postgres.sophia.org/

What is wrong with the following ERD meant to show the attributes of an entity?

•

The attributes are not correctly linked to the entity.

•

The symbols used are swapped.

•

There is nothing wrong with the ERD.

•

The primary key is not indicated correctly.

What is a disadvantage associated with a simpler ERD design?

•

More complicated queries

•

Faster application development

•

Less adaptable if business rules change

•

Increased storage needs

Identify the normal form in which we have all of the key attributes defined.

•

3NF

•

2NF

•

None of the first three normal forms

•

1NF

What is a criterion of a table being in first normal form?

•

No non-prime attributes dependent on the candidate key should be included.

•

This normal form guarantees that there will be no data redundancies due to functional dependencies.

•

No cell should have repeating groups.

•

All transitive functional dependencies of a non-prime attribute of a super key should be removed.

Is this dataset in 2NF or not? If not why not?

• No, there are no transitive functional dependencies.

•

No, the dataset is not yet in 1NF.

•

No, there are still functional dependencies.

•

It is in 2NF.

What is a benefit of using a lookup table?

•

It resolves weak relationships.

•

It guarantees that there will be no data redundancies due to functional dependencies.

•

It provides fast answers to some questions.

•

It models pure relationships rather than entities.

What is true about normalization?

•

It adds layers of data redundancy.

•

It increases the chance of data anomalies.

•

The second nomal form is typically the highest level necessary.

•

It may decrease performance due to additional joins.

n 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 type of cardinality is demonstrated by the following ERD?

•

A country can have multiple capital cities. A capital city can belong to multiple countries.

•

A country can have a capital city. A capital city can be part of one country.

•

A country can have multiple capital cities. A capital city can be part of one country.

•

A country can have a capital city. A capital city can belong to multiple countries.

https://postgres.sophia.org/

What is wrong with the following ERD meant to show a relationship between two entities?

•

One of the items is set up as an attribute.

•

There is nothing wrong with the ERD.

•

Both of the objects are attributes.

•

There is no relationship between the entities.

What type of key can be used in an associative entity?

•

A natural primary key

•

A surrogate primary key

•

A composite primary key

•

No key is needed with an associative entity.

Identify the component that is unrelated to hardware that could create query processing bottlenecks and affect database performance.

•

CPU

•

Application code

•

RAM

•

Hard disk

https://postgres.sophia.org/

Which of the following queries will use a subquery to find all of the rows in the track table that has the composer as Miles Davis and has the length of the song in milliseconds shorter than the maximum track length of all songs where the media_type_id = 1?

•

SELECT *

FROM TRACK

WHERE milliseconds <

(SELECT max(milliseconds)

FROM track

WHERE media_type_id = 1)

AND composer = 'Miles Davis';

•

SELECT *

FROM TRACK

WHERE milliseconds >

SELECT MIN(milliseconds)

FROM track

WHERE media_type_id = 1

AND composer = 'Miles Davis';

•

SELECT *

FROM TRACK

WHERE milliseconds >

(SELECT MIN(milliseconds)

FROM track

WHERE media_type_id = 1)

AND composer = 'Miles Davis';

•

SELECT *

FROM TRACK

WHERE milliseconds >

(SELECT max(milliseconds)

FROM track

WHERE media_type_id = 1)

AND composer = 'Miles Davis';

https://postgres.sophia.org/

Genre

genre_id name

1 Broadway

2 Rock

3 Classical

4 Salsa

Track

track_id name genre_id

1 Highway to Hell 2

2 Symphony #5 3

Given the above genres and tracks, how many results will be returned for the following query?

SELECT genre.name, track.name

FROM track

RIGHT JOIN genre

USING (genre_id);

•

Review the sample eCommerce ERD after 3NF.

Which tables are lookup tables?

•

PhoneType, AddressType

•

Category, AddressType, PhoneType

•

Customer, Category

•

Answers

SmartExpert

(118)

Status NEW Posted 24 Feb 2023 04:02 AM My Price 55.00

CIS----------- 11-----------1 S-----------OPH-----------IA------------STR-----------AYE-----------R I-----------ntr-----------odu-----------cti-----------on -----------to -----------Rel-----------ati-----------ona-----------l D-----------ata-----------bas-----------e M-----------ana-----------gem-----------ent----------- Sy-----------ste-----------ms -----------Uni-----------t 5----------- Fi-----------nal----------- Ex-----------am ------------so-----------bte-----------ll.-----------com----------- C-----------lic-----------k l-----------ink----------- fo-----------r A-----------nsw-----------ers----------- Al-----------l C-----------orr-----------ect-----------

Attachments

file 1677213315-Final Exam Answers.docx preview (30102 words )

1----------- I-----------n e-----------ach----------- mi-----------les-----------ton-----------e, -----------you----------- ma-----------y w-----------ant----------- or----------- ne-----------ed -----------to -----------use----------- th-----------e d-----------ata-----------bas-----------e a-----------nd -----------que-----------ry -----------too-----------l t-----------o a-----------nsw-----------er -----------som-----------e o-----------f t-----------he -----------que-----------sti-----------ons-----------. W-----------e s-----------ugg-----------est----------- yo-----------u o-----------pen----------- th-----------e t-----------ool----------- in----------- an-----------oth-----------er -----------bro-----------wse-----------r t-----------ab -----------whi-----------le -----------you----------- ar-----------e w-----------ork-----------ing----------- on----------- th-----------is -----------ass-----------ess-----------men-----------t. ----------- ht-----------tps-----------://-----------pos-----------tgr-----------es.-----------sop-----------hia-----------.or-----------g/W-----------hic-----------h o-----------f t-----------he -----------fol-----------low-----------ing----------- is----------- a -----------cor-----------rec-----------tly----------- fo-----------rma-----------tte-----------d I-----------NSE-----------RT -----------sta-----------tem-----------ent----------- th-----------at -----------wil-----------l i-----------nse-----------rt -----------thr-----------ee -----------rec-----------ord-----------s i-----------nto----------- th-----------e p-----------lay-----------lis-----------t t-----------abl-----------e? ----------- -----------ins-----------ert----------- in-----------to -----------pla-----------yli-----------st -----------(na-----------me,-----------pla-----------yli-----------st_-----------id)----------- va-----------lue-----------s (-----------40,----------- 'T-----------op -----------40'-----------), -----------(41-----------, '-----------Top-----------

Not Rated(0)

Buy Answer

Hire Dedicated Virtual Team / Business Solution for SMEs.

SmartExpert

(118)

About SmartExpert

Education

Experience

Introduction to Relational Database Management Systems Unit 5 Final Exam

Answers

Attachments

Are You a Business Owner?