CIS 111 SOPHIA-STRAYER Introduction to Relational Database Management Systems Unit 2 Challenges-sobtell.com

Click link for Answers All Correct

 

Unit 2

 

Which result set requires a JOIN?

·        A.)

Showing all track names with both genre ID and track ID

·        B.)

Showing all track names with genre IDs

·        C.)

Showing all track names with genre names

·        D.)

Showing all genre names with genre IDs

 

Which result set requires a JOIN?

·        A.)

Showing all album titles with artists' names

·        B.)

Showing all album titles with artist IDs

·        C.)

Showing all artist names with artist IDs

·        D.)

Showing all album titles with both artist ID and album ID

Which result set requires a JOIN?

·        A.)

Showing all invoice totals with customer IDs

·        B.)

Showing all invoice totals with both customer ID and invoice ID

·        C.)

Showing all invoice totals with customer last names

·        D.)

Showing all customer names with customer IDs

 

Which of the following data models is eligible for a NATURAL JOIN?

·        A.)

 

album
  album_id *
  title
  artist_id (FK)

artist
 artist_code *
 name

 

·        B.)

 

album
  album_id *
  title
  artist (FK)

artist
 artist_id *
 name

·        C.)

 

album
  album_id *
  title
  artist_code (FK)

artist
 artist_id *
 name

·        D.)

 

album
  album_id *
  title
  artist_code (FK)

artist
 artist_code *
 name

 

Which of the following data models is eligible for a NATURAL JOIN?

·        A.)

invoice
  invoice_id *
  total
  id (FK)

customer
  customer_id *
  name

·        B.)

invoice
  invoice_id *
  total
  customer_id (FK)

customer
  id *
  name

·        C.)

invoice
  invoice_id *
  total
  cid (FK)

customer
  cid *
  name

·        D.)

invoice
  invoice_id *
  total
  customer_id (FK)

customer
  support_rep_id *
  name

 

Consider the following two tables:

album
  album_id *
  title
  artist_id (FK)

artist
  artist_code *
  name

Why is a natural join NOT possible between these two tables?

·        A.)

It's not possible to naturally join two tables that have a foreign key relationship.

·        B.)

In order for a natural join to be possible, the two tables must be able to be joined using more than one column.

·        C.)

The column names must both contain "id" in their names.

·        D.)

The column names for the columns representing the artist ID are not identical in name.

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

• A.)

SELECT name, title
FROM artist
JOIN album
USING (album_id);

• B.)

SELECT name, title
FROM artist
JOIN album
WHERE artist_id != NULL;

• C.)

SELECT name, title
FROM artist, album;

• D.)

SELECT name, title
FROM artist
JOIN album
USING (artist_id);

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

• A.)

SELECT invoice_id, invoice_date, email
FROM invoice
USING (invoice.customer_id = customer.customer_id);

• B.)

SELECT invoice_id, invoice_date, email
FROM invoice
WHERE customer_if != NULL;

• C.)

SELECT invoice_id, invoice_date, email
FROM invoice
JOIN customer
USING (customer_id);

• D.)

SELECT invoice_id, invoice_date, email
FROM invoice, customer;

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

• A.)

SELECT invoice_id, total, last_name
FROM invoice, customer;

• B.)

SELECT invoice_id, total, last_name
FROM invoice
JOIN customer
USING (customer_id);

• C.)

SELECT invoice_id, total, last_name
FROM invoice
WHERE customer_id != NULL;

• D.)

SELECT invoice_id, total, last_name
FROM invoice
JOIN customer
USING (invoice.customer_id = customer.customer_id);

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

·        A.)

SELECT genre.name, track_id
FROM genre
JOIN track
ON track.id=genre.genre_id

·        B.)

SELECT genre.name, track_id
FROM genre
JOIN track
ON track.genre_id=genre.genre_id

·        C.)

SELECT name, track_id
FROM genre
JOIN track
ON track.track_id=genre.genre_id

·        D.)

SELECT genre.name, track_id
FROM genre
JOIN track
ON track.track_id=genre.track_id

Which of the following is a correctly formatted SELECT statement to show the result set that would display the artist's name and the album's title?

·        A.)

SELECT name, title
FROM artist
JOIN album
ON (album.artist_id=artist.artist_id);

·        B.)

SELECT name, title
FROM artist
JOIN album
ON artist=album;

·        C.)

SELECT name, title
FROM artist
JOIN album
ON (album.artis_id=artis.id);

·        D.)

SELECT name, title
FROM artist
JOIN album
ON artist_id;

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

·        A.)

SELECT first_name, last_name,
total
FROM customer
JOIN invoice
ON customer.customer_id=invoice.customer_id

·        B.)

SELECT first_name, last_name,
total
FROM customer
JOIN invoice
ON customer_id=customer_id;

·        C.)

SELECT first_name, last_name,
total
FROM customer
JOIN invoice
ON customer.cust_id=invoice.customer_id

·        D.)

SELECT first_name, last_name,
total
FROM customer
JOIN invoice
ON customer_id;

Identify the statement that correctly utilizes aliases.

·        A.)

SELECT first_name as employee_first_name, last_name as employee_last_name, customer_id as id
FROM employee as emp
JOIN customer as cus
ON emp.employee_id = cus.support_rep_id;

·        B.)

SELECT emp.first_name, emp.last_name, customer.customer_id
FROM employee as emp
JOIN customer as cus
ON emp.employee_id = cus.support_rep_id;

·        C.)

SELECT first_name, last_name, customer_id
FROM employee as emp
JOIN customer as cus
ON emp.employee_id = cus.support_rep_id;

·        D.)

SELECT emp.first_name, emp.last_name, cus.customer_id
FROM employee as emp
JOIN customer as cus
ON emp.employee_id = cus.support_rep_id;

Identify the statement that correctly utilizes aliases.

·        A.)

SELECT artist_id
FROM artist AS a
JOIN album AS alb
USING (artist_id);

·        B.)

SELECT artist_id AS a, artist_id AS alb
FROM artist
JOIN album
USING (company_id);

·        C.)

SELECT album.artist_id
FROM artist AS a
JOIN album AS alb
USING (artist_id);

·        D.)

SELECT alb.artist_id
FROM artist AS a
JOIN album AS alb
USING (artist_id);

Identify the statement that correctly utilizes aliases.

·        A.)

SELECT t.track_id, name as track_name, name as genre_name
FROM track AS t
JOIN genre AS g
USING (genre_id);

·        B.)

SELECT t.track_id, t.name, g.name
FROM track AS t
JOIN genre AS g
USING (genre_id);

·        C.)

SELECT t.track_id, track.name, genre.name
FROM track AS t
JOIN genre AS g
USING (genre_id);

·        D.)

SELECT t.track_id, name
FROM track AS t
JOIN genre AS g
USING (genre_id);

Use the following data model for this question:

Animal
animal_id
name

Adopter
adopter_id
name

Animal_Adopter
animal_adopter_id
animal_id
adopter_id

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

·        A.)

When a pet adoption agency wants to see all animals, including those that have been adopted and those that haven't

·        B.)

When a pet adoption agency wants to see only animals that have NOT been adopted

·        C.)

When a pet adoption agency wants to see all people who have adopted animals from the agency

·        D.)

When a pet adoption agency wants to see only animals that have been adopted

Use the following data model for this question:

Part
part_id
part_name

Bike
bike_id

Bike_Part
bike_part_id
bike_id
part_id

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

·        A.)

When a bike mechanic wants to see only bike models that require parts that are currently in the inventory

·        B.)

When a bike mechanic wants to see all bike parts in their inventory, whether they are being utilized for a particular bike model or not

·        C.)

When a bike mechanic wants to see only bike parts in their inventory that are being utilized for a particular bike model

·        D.)

When a bike mechanic wants to see only bike parts in their inventory that are not being utilized for a particular bike model

Use the following data model for this question:

Department
department_id
name

Employee
employee_id
name

Employee_Department
employee_dept_id
employee_id
department_id

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

·        A.)

When a manager wants to see only departments that have employees, but not departments that DON'T have employees

·        B.)

When a manager wants to see only employees that have been assigned to a department

·        C.)

When a manager wants to see all employees in the company, regardless of whether they are associated with a particular department

·        D.)

When a manager wants to see any departments that do not have employees

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

·        A.)

All rows in the customer table, even those without a matching row in the invoice table

·        B.)

Only rows in the customer table that have a matching row in the invoice table

·        C.)

All rows from the invoice table, even if they have NULL customer_id's

·        D.)

Only rows in the customer table that don't have a matching row in the invoice table

Which results would show if an employee table were LEFT JOINed to a department table?

·        A.)

All employees, even employees that do not have department_id's

·        B.)

Only employees that do not have departments id's

·        C.)

All departments, regardless of whether there are employees in the department

·        D.)

Departments with no employees, and employees with no departments

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

·        A.)

Only rows from the invoice table that have NULL customer_id's

·        B.)

All rows from the invoice table, even those with NULL for the customer_id column

·        C.)

All rows from the customer table, regardless of whether they have invoices

·        D.)

Only rows in the customer table that have a matching row in the invoice table, and vice versa

Genre

Track

Consider the following query:

SELECT genre.name, track.name
FROM genre
RIGHT JOIN track
USING (genre_id);

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

• A.)

1

• B.)

2

• C.)

5

• D.)

3

Playlist

Playlist_Track

Consider the following query:

SELECT playlist_track.track_id, playlist.name
FROM playlist
RIGHT JOIN playlist_track
USING (playlist_id);

Given the above playlists and tracks, how many results will be returned for this query?

• A.)

4

• B.)

1

• C.)

3

• D.)

2

Genre

Track

Consider the following query:

SELECT genre.name, track.name
FROM track
RIGHT JOIN genre
USING (genre_id);

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

• A.)

2

• B.)

1

• C.)

3

• D.)

5

genre
genre_id
name

genre_instrument
genre_instrument_id
genre_id
instrument_id

instrument
instrument_id
name

Given the data model above, what would a CROSS JOIN joining the genre table and the instrument table represent?

·        A.)

Every instrument in the instrument table, regardless of whether it has been associated with a genre in the genre table

·        B.)

Every combination of each genre with each instrument, even those that are not represented in the genre_instrument table

·        C.)

Every genre in the genre table, regardless of whether it has been associated with an instrument in the instrument table

·        D.)

Only combinations of genres and instruments that are represented in the genre_instrument table

Genre

Track

Consider the following query:

SELECT genre.name, track.name
FROM track
CROSS JOIN genre;

Given the above data for the track and genre tables, how many records would be included in the result set for this query?

• A.)

3

• B.)

5

• C.)

2

• D.)

6

Genre

Track

Consider the following query:

SELECT genre.name, track.name
FROM track
CROSS JOIN genre;

Given the above data model, what do the results from this query represent?

• A.)

It represents every record in the track table, regardless of whether they have a genre_id.

• B.)

It represents every track paired with every genre in the genre table.

• C.)

It represents tracks that exist in the track table combined with their genre name as represented by the "genre_id" foreign key.

• D.)

It represents every record in the genre table, regardless of whether there are tracks that belong to that genre in the track table.

In trying to delete from the employee table, what is the cause of this error: "Query failed because of: error: update or delete on table "employee"
violates foreign key constraint "customer_support_rep_id_fkey" on table
"customer"?

·        A.)

The support_rep_id in the employee table doesn't exist.

·        B.)

The customer_id in the customer table has to match the employee_id table.

·        C.)

The report_to column links to the employee_id in the employee table that has the value that exists.

·        D.)

The customer table data has to be deleted first or have the support_rep_id updated to a different rep.

In trying to delete from the employee table, what is the cause of this error: "Query failed because of: error: update or delete on table "employee" violates foreign key constraint "employee_reports_to_fkey" on table "employee"?

·        A.)

The reports_to in the employee table doesn't exist.

·        B.)

The reports_to column links to the employee_id in the employee table that has the value that exists.

·        C.)

The reports_to value of the row being deleted must be updated first.

·        D.)

The customer table references the employee table, so the customer table data has to be removed first.

In trying to update the track table, what is the cause of this error: Query failed because of: error: insert or update on table "track" violates foreign key constraint "track_album_id_fkey"?

·        A.)

The album_id that we are trying to update needs to be deleted first from the album table.

·        B.)

The album_id that we are updating doesn't exist.

·        C.)

Any tables that reference the track_id of the row that we're trying to update need to be updated first.

·        D.)

The track_id in the track table doesn't exist.

Which of the following data models appropriately models the relationship between products and their categories?

·        A.)

Category
category_id
name
product_name
cost

·        B.)

Product
product_id
name
cost
category_id (FK)

Category
category_id
name

·        C.)

Product
product_id
name
cost
category_id (FK)

Category
category_id
name
product_id (FK)

·        D.)

Product
product_id
name
cost
category_1_name
category_2_name
...

Which of the following data models appropriately models the relationship between nonprofits and their donors?

·        A.)

Organization
org_id
name

Donors
donor_id
name
org_id (FK)
amount_donated

·        B.)

Organization
org_id
name
donor_id (FK)

Donor
donor_id
name
org_id (FK)
amount_donated

·        C.)

Organization
org_id
name
donor_1_name
donor_1_amount
donor_2_name
donor_2_amount
...

·        D.)

Donor
donor_id
name
organization_name
amount

Which of the following data models appropriately models the relationship between employees and their departments?

·        A.)

Employee
employee_id
name
email
department_id (FK)

Department
department_id
name

·        B.)

Department
department_id
name
employee_name
email

·        C.)

Employee
employee_id
name
cost
department_id (FK)

Department
department_id
name
employee_id (FK)

·        D.)

Employee
employee_id
name
email
department_1_name
department_2_name
...

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

·        A.)

ALTER TABLE department
ADD CONSTRAINT fk_employee
FOREIGN KEY (department_id)
REFERENCES employee (department_id);

·        B.)

ALTER TABLE employee
ADD CONSTRAINT fk_employee
FOREIGN KEY department (department_id)
REFERENCES department_id;

·        C.)

ALTER TABLE employee
ADD CONSTRAINT fk_employee
FOREIGN KEY (department_id)
REFERENCES department (department_id);

·        D.)

ALTER TABLE employee
ADD CONSTRAINT
FOREIGN KEY (department_id)
REFERENCES department (department_id);

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

·        A.)

ALTER TABLE department
ADD CONSTRAINT fk_manager
FOREIGN KEY representative (representative_id)
REFERENCES manager_id;

·        B.)

ALTER TABLE department
ADD CONSTRAINT fk_manager
FOREIGN KEY (manager_id)
REFERENCES representative (representative_id);

·        C.)

ALTER TABLE representative
ADD CONSTRAINT fk_manager
FOREIGN KEY (representative_id)
REFERENCES department (manager_id);

·        D.)

ALTER TABLE department
ADD CONSTRAINT
FOREIGN KEY (manager_id)
REFERENCES representative (representative_id);

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

·        A.)

ALTER TABLE product
ADD CONSTRAINT
FOREIGN KEY (category_id)
REFERENCES category (category_id);

·        B.)

ALTER TABLE product
ADD CONSTRAINT fk_product
FOREIGN KEY (category_id)
REFERENCES category (category_id);

·        C.)

ALTER TABLE product
ADD CONSTRAINT fk_product
FOREIGN KEY category (category_id)
REFERENCES category_id;

·        D.)

ALTER TABLE category
ADD CONSTRAINT fk_product
FOREIGN KEY (category_id)
REFERENCES product (category_id);

Which of the following is true of foreign keys?

·        A.)

Foreign keys may be temporarily disabled.

·        B.)

Foreign keys are not needed when we require referential integrity.

·        C.)

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

·        D.)

A foreign key cannot be linked to a candidate key.

Which of the following is true of foreign keys?

·        A.)

A foreign key is used to enforce referential integrity.

·        B.)

A foreign key can only consist of one column.

·        C.)

A foreign key cannot contain null values.

·        D.)

A foreign key cannot be linked to a candidate key.

Which of the following is true of foreign keys?

·        A.)

A foreign key can be added after tables have been created.

·        B.)

Foreign keys are not needed when we require referential integrity.

·        C.)

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

·        D.)

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

Given the tables in our example database in Section 1 of the tutorial, if an existing employee wanted to add a new customer and put in an invoice, in what order should the table data be inserted?

·        A.)

invoice_line
invoice
customer
employee

·        B.)

customer
invoice
invoice_line

·        C.)

invoice_line
invoice
customer

·        D.)

employee
customer
invoice
invoice_line

Given the tables in our example database in Section 1 of the tutorial, the organization would like to create a new playlist with a few tracks that already exist.

In what order should the table data be inserted?

·        A.)

track
playlist_track
playlist

·        B.)

playlist
playlist_track
track

·        C.)

playlist_track
playlist

·        D.)

playlist
playlist_track

Given the tables in our example database in Section 1 of the tutorial, the organization would like to add a new artist's album with their tracks. Their tracks are based on existing genres and media types.

In what order should the table data be inserted?

·        A.)

artist
album
track

·        B.)

artist
album
track
media_type
genre

·        C.)

album
track

·        D.)

track
album
artist

Which of the following queries will use a subquery to find the rows in the invoice table in the country Italy, that have the invoice total larger than the minimum total of invoices of those in the country Belgium?

·        A.)

SELECT * FROM invoice
WHERE total >
SELECT MIN(total) FROM invoice
WHERE billing_country = 'Belgium'
AND billing_country = 'Italy';

·        B.)

SELECT * FROM invoice
WHERE total >
(SELECT MIN(total) FROM invoice
WHERE billing_country = 'Belgium')
AND billing_country = 'Italy';

·        C.)

SELECT * FROM invoice
WHERE total >
(SELECT MAX(total) FROM invoice
WHERE billing_country = 'Belgium')
AND billing_country = 'Italy';

·        D.)

SELECT * FROM invoice
WHERE total >
(SELECT MIN(total) FROM invoice
WHERE billing_country = 'Italy')
AND billing_country = 'Belgium';

Which of the following queries will use a subquery to find the rows in the invoice table in the country USA that have the invoice total larger than the average of invoices of those in the country Canada?

·        A.)

SELECT * FROM invoice
WHERE total >
SELECT AVG(total) FROM invoice
WHERE billing_country = 'Canada'
AND billing_country = 'USA';

·        B.)

SELECT AVG(total) FROM invoice
WHERE total >
(SELECT * FROM invoice
WHERE billing_country = 'Canada')
AND billing_country = 'USA';

·        C.)

SELECT * FROM invoice
WHERE total >
(SELECT AVG(total) FROM invoice
WHERE billing_country = 'Canada')
AND billing_country = 'USA';

·        D.)

SELECT * FROM invoice
WHERE total >
(SELECT AVG(total) FROM invoice
WHERE billing_country = 'USA')
AND billing_country = 'Canada';

Which of the following queries will use a subquery to find the rows in the invoice table that have been invoiced after 2013-01-01 and that have an invoice total larger than the maximum total of invoices of those in the country Belgium?

·        A.)

SELECT * FROM invoice
WHERE total >
(SELECT MIN(total) FROM invoice
WHERE billing_country = 'Belgium')
AND invoice_date > '2013-01-01';

·        B.)

SELECT * FROM invoice
WHERE total >
SELECT MAX(total) FROM invoice
WHERE billing_country = 'Belgium'
AND invoice_date > '2013-01-01';

·        C.)

SELECT * FROM invoice
WHERE total >
(SELECT MAX(total) FROM invoice
WHERE invoice_date > '2013-01-01')
AND billing_country = 'Belgium';

·        D.)

SELECT * FROM invoice
WHERE total >
(SELECT MAX(total) FROM invoice
WHERE billing_country = 'Belgium')
AND invoice_date > '2013-01-01';

Consider the following queries:

1. SELECT * FROM customer
WHERE support_rep_id IN
(SELECT employee_id FROM employee
WHERE reports_to = 1);

2. SELECT customer.* FROM customer
INNER JOIN employee
ON customer.support_rep_id = employee.employee_id
WHERE reports_to = 1;

Which would be more efficient?

·        A.)

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

·        B.)

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

·        C.)

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

·        D.)

Query #1 would be more efficient, as it is not using indexed columns.

Consider the following queries:

1. SELECT * FROM customer
WHERE support_rep_id IN
(SELECT employee_id FROM employee
WHERE first_name = 'Steve');

2. SELECT customer.* FROM customer
INNER JOIN employee
ON customer.support_rep_id = employee.employee_id
WHERE employee.first_name = 'Steve';

Which of these would be the most efficient?

·        A.)

Query #1 would be more efficient, as it is not using indexed columns.

·        B.)

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

·        C.)

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

·        D.)

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

Which of the following queries would check for duplicates of a combined country and city in the customer table and how many there are of each?

·        A.)

SELECT country, city
FROM customer
GROUP BY country, city
HAVING COUNT(*) > 0;

·        B.)

SELECT country, city, COUNT(*)
FROM customer
WHERE COUNT(*) > 1;

·        C.)

SELECT country, city
FROM customer
GROUP BY country, city
HAVING COUNT(*) > 1;

·        D.)

SELECT country, city, COUNT(*)
FROM customer
GROUP BY country, city
HAVING COUNT(*) > 1;

Which of the following queries would check for duplicates of a customer_id in the invoice table and how many there are of each?

·        A.)

SELECT customer_id, COUNT(*)
FROM customer
GROUP BY customer_id
HAVING COUNT(*) > 1;

·        B.)

SELECT customer_id
FROM invoice
GROUP BY invoice_id
HAVING COUNT(*) > 1;

·        C.)

SELECT customer_id, COUNT(*)
FROM invoice
GROUP BY customer_id
HAVING COUNT(*) > 1;

·        D.)

SELECT customer_id
FROM invoice
GROUP BY customer_id
HAVING COUNT(*) > 1;

Which of the following queries would check for duplicates of a customer_id, their complete addresses and totals in the invoice table, and how many there are of each?

·        A.)

SELECT *, COUNT(*)
FROM invoice
GROUP BY *
HAVING COUNT(*) > 1;

·        B.)

SELECT customer_id, COUNT(*)
FROM invoice
GROUP BY customer_id
HAVING COUNT(*) > 1;

·        C.)

SELECT billing_address, billing_city, billing_state, billing_country, billing_postal_code, total, customer_id
FROM invoice
GROUP BY billing_address, billing_city, billing_state, billing_country, billing_postal_code, total, customer_id
HAVING COUNT(*) > 1;

·        D.)

SELECT billing_address, billing_city, billing_state, billing_country, billing_postal_code, total, customer_id, COUNT(*)
FROM invoice
GROUP BY billing_address, billing_city, billing_state, billing_country, billing_postal_code, total, customer_id
HAVING COUNT(*) > 1;

Which of the following statements would create a UNION between the first name and last name from the customer table along with the first name and last name from the employee table?

·        A.)

SELECT last_name, first_name
FROM customer
UNION
SELECT first_name, last_name
FROM employee;

·        B.)

SELECT first_name
FROM customer
UNION
SELECT last_name
FROM employee;

·        C.)

SELECT first_name, last_name
FROM customer
UNION
SELECT first_name, last_name
FROM employee;

·        D.)

SELECT first_name, last_name
FROM customer
SELECT first_name, last_name
FROM employee
UNION;

Which of the following statements would create a UNION between the city and country from the customer table along with the city and country from the employee table?

·        A.)

SELECT country, city
FROM customer
UNION
SELECT city, country
FROM employee;

·        B.)

SELECT city, country
FROM customer
UNION
SELECT city, country
FROM employee;

·        C.)

SELECT city, country
FROM customer
SELECT city, country
FROM employee
UNION;

·        D.)

SELECT city
FROM customer
UNION
SELECT country
FROM employee;

Which of the following statements would create a UNION between the name of the tracks that have the genre_id equal to 1 and the media_type_id equal to 2, with the names of the tracks that are on album_id equal to 8, and with the names of tracks where the composer is not empty?

·        A.)

SELECT name
FROM track
WHERE genre_id = 1 and media_type_id = 2
UNION
SELECT name
FROM track
WHERE album_id = 8
UNION
SELECT name
FROM track
WHERE composer IS NOT NULL;

·        B.)

SELECT genre_id, media_type_id
FROM track
WHERE genre_id = 1 and media_type_id = 2
UNION
SELECT album_id
FROM track
WHERE album_id = 8
UNION
SELECT composer
FROM track
WHERE composer IS NOT NULL;

·        C.)

SELECT name
FROM track
WHERE genre_id = 1 and media_type_id = 2
SELECT name
FROM track
WHERE album_id = 8
SELECT name
FROM track
WHERE composer IS NOT NULL
UNION;

·        D.)

SELECT name
FROM track
WHERE genre_id = 1 and media_type_id = 2
AND album_id = 8
AND composer IS NOT NULL
UNION;

Consider the following criteria:

<columnname>  >  ALL (<subquery>)

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

·        A.)

It returns true if a value is greater than or equal to the biggest value returned by the subquery.

·        B.)

It returns true if a value is greater than any of the values returned by the subquery.

·        C.)

It returns true if a value is greater than the biggest value returned by the subquery.

·        D.)

It returns true if a value is greater than or equal to any of the values returned by the subquery.

Consider the following criteria:

<columnname>  >  ANY (<subquery>)

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

·        A.)

It returns true if a value is greater than any of the values returned by the subquery.

·        B.)

It returns true if a value is greater than or equal to any of the values returned by the subquery.

·        C.)

It returns true if a value is greater than the biggest value returned by the subquery.

·        D.)

It returns true if a value is greater than or equal to the biggest value returned by the subquery.

Consider the following criteria:

<columnname>  >=  ALL(<subquery>)

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

·        A.)

It returns true if a value is greater than the biggest value returned by the subquery.

·        B.)

It returns true if a value is greater than or equal to any of the values returned by the subquery.

·        C.)

It returns true if a value is greater than or equal to the biggest value returned by the subquery.

·        D.)

It returns true if a value is greater than any of the values returned by the subquery.

 

The organization would like to see how much money they would make if they increased the total by 20% for those who live in Canada. 

Which of the following statements would query the sum of the total of the invoices increased by 20% for those who live in Canada?

·        A.)

SELECT SUM(total)*1.20 AS possible_total FROM invoice
WHERE billing_country = 'Canada';

·        B.)

SELECT SUM(total)*.20 AS possible_total FROM invoice
WHERE billing_country = 'Canada';

·        C.)

SELECT total*1.20 AS possible_total FROM invoice
WHERE billing_country = 'Canada';

·        D.)

SELECT SUM(total)*.8 AS possible_total FROM invoice
WHERE billing_country = 'Canada';

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

·        A.)

SELECT invoice_id, SUM(quantity * unit_price) AS total FROM invoice_line
ORDER BY total DESC;

·        B.)

SELECT invoice_id, SUM(quantity + unit_price) AS total FROM invoice_line
GROUP BY invoice_id
ORDER BY total DESC;

·        C.)

SELECT invoice_id, SUM(quantity * unit_price) AS total FROM invoice_line
GROUP BY invoice_id
ORDER BY total DESC;

·        D.)

SELECT invoice_id, quantity * unit_price AS total FROM invoice_line
GROUP BY invoice_id
ORDER BY total DESC;

All invoices from USA will require a fee paid to an external organization for 10% of the total. 

Which of the following statements would query the sum of the total of the invoices in the country USA and calculate 10% of it?

·        A.)

SELECT SUM(total)*.10 AS calculated_fee FROM invoice
WHERE billing_country = 'USA';

·        B.)

SELECT total(SUM)*.10 AS calculated_fee FROM invoice
WHERE billing_country = 'USA';

·        C.)

SELECT SUM(total)*.90 AS calculated_fee FROM invoice
WHERE billing_country = 'USA';

·        D.)

SELECT AVG(total*.10) AS calculated_fee FROM invoice
WHERE billing_country = 'USA';

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

·        A.)

CREATE VIEW long_tracks
AS track
WHERE milliseconds > 4000;

·        B.)

CREATE VIEW long_tracks
AS
SELECT name, milliseconds, composer
FROM track
WHERE milliseconds > 4000;

·        C.)

CREATE VIEW long_tracks
AS track
SELECT name, milliseconds, composer
WHERE milliseconds > 4000;

·        D.)

CREATE VIEW long_tracks
SELECT name, milliseconds, composer
FROM track
WHERE milliseconds > 4000;

Which of the following is a true statement about VIEWs?

·        A.)

VIEWs are not persisted in the database; they are intended to be used for temporary reporting.

·        B.)

VIEWs persist in the database after they are created.

·        C.)

VIEWs persist only for the duration of the session during which they were created.

·        D.)

VIEWs can persist beyond the duration of the session, but this is not the default behavior.

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

·        A.)

CREATE VIEW mississippi_customers
AS customer
WHERE state = 'MS';

·        B.)

CREATE VIEW mississippi_customers
AS
SELECT *
FROM customer
WHERE state = 'MS';

·        C.)

CREATE VIEW mississippi_customers
SELECT *
FROM customer
WHERE state = 'MS';

·        D.)

CREATE VIEW mississippi_customers
AS customer
SELECT *
WHERE state = 'MS';

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

·        A.)

CREATE VIEW album_cost
AS track
INNER JOIN album
ON album.album_id = track.album_id
GROUP BY album.album_id, album.title;

·        B.)

CREATE VIEW album_cost
SELECT album.album_id, album.title, SUM(unit_price)
FROM track
INNER JOIN album
ON album.album_id = track.album_id
GROUP BY album.album_id, album.title;

·        C.)

CREATE VIEW album_cost
AS
SELECT album.album_id, album.title, SUM(unit_price)
FROM track
INNER JOIN album
ON album.album_id = track.album_id
GROUP BY album.album_id, album.title;

·        D.)

CREATE VIEW album_cost
AS
SELECT album.album_id, album.title, SUM(unit_price)
ON album.album_id = track.album_id
GROUP BY album.album_id, album.title;

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

·        A.)

CREATE VIEW
AS
SELECT employee.first_name, employee.last_name, count(*)
FROM employee
INNER JOIN customer
ON employee.employee_id = customer.support_rep_id
GROUP BY employee.first_name, employee.last_name

·        B.)

CREATE VIEW employee_support
SELECT employee.first_name, employee.last_name, count(*)
FROM employee
INNER JOIN customer
ON employee.employee_id = customer.support_rep_id
GROUP BY employee.first_name, employee.last_name

·        C.)

CREATE VIEW employee_support
AS
SELECT employee.first_name, employee.last_name, count(*)
FROM employee
INNER JOIN customer
ON employee.employee_id = customer.support_rep_id
GROUP BY employee.first_name, employee.last_name

·        D.)

CREATE VIEW employee_support
AS
SELECT employee.first_name, employee.last_name, count(*)
ON employee.employee_id = customer.support_rep_id
GROUP BY employee.first_name, employee.last_name

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

·        A.)

CREATE VIEW customer_support
SELECT customer.first_name AS cust_first_name, customer.last_name AS cust_last_name,
employee.first_name AS emp_first_name, employee.last_name AS emp_last_name
FROM employee
INNER JOIN customer
ON employee.employee_id = customer.support_rep_id;

·        B.)

CREATE VIEW customer_support
AS
SELECT customer.first_name AS cust_first_name, customer.last_name AS cust_last_name,
employee.first_name AS emp_first_name, employee.last_name AS emp_last_name
customer
ON employee.employee_id = customer.support_rep_id;

·        C.)

CREATE VIEW
AS
SELECT customer.first_name AS cust_first_name, customer.last_name AS cust_last_name,
employee.first_name AS emp_first_name, employee.last_name AS emp_last_name
FROM employee
INNER JOIN customer
ON employee.employee_id = customer.support_rep_id;

·        D.)

CREATE VIEW customer_support
AS
SELECT customer.first_name AS cust_first_name, customer.last_name AS cust_last_name,
employee.first_name AS emp_first_name, employee.last_name AS emp_last_name
FROM employee
INNER JOIN customer
ON employee.employee_id = customer.support_rep_id;

Consider the following view that has been created:

CREATE VIEW customer_support
AS
SELECT customer.first_name AS cust_first_name, customer.last_name AS cust_last_name,
employee.first_name AS emp_first_name, employee.last_name AS emp_last_name
FROM employee
INNER JOIN customer
ON employee.employee_id = customer.support_rep_id;

How would you query the view to order the names by the employee's last name followed by the customer's last name, in alphabetical order?

·        A.)

SELECT * FROM customer_support
ORDER BY emp_last_name DESC, cust_last_name DESC;

·        B.)

SELECT * FROM customer_support
ORDER BY cust_last_name, emp_last_name;

·        C.)

SELECT * FROM customer_support
ORDER BY emp_last_name, cust_last_name;

·        D.)

SELECT * FROM customer_support
ORDER BY cust_last_name ASC, emp_last_name ASC;

Consider the following view that has been created:

CREATE VIEW employee_support
AS
SELECT employee.first_name, employee.last_name, count(*) as customer_count
FROM employee
INNER JOIN customer
ON employee.employee_id = customer.support_rep_id
GROUP BY employee.first_name, employee.last_name;

How would you query the view to display the results in ascending order of the customer_count?

·        A.)

SELECT COUNT(*) FROM employee_support;

·        B.)

SELECT * FROM customer_support
ORDER BY employee_count DESC;

·        C.)

SELECT * FROM employee_support
ORDER BY customer_count;

·        D.)

SELECT * FROM employee_support
ORDER BY customer_count DESC;

Consider the following view that has been created:

CREATE VIEW album_cost
AS
SELECT album.album_id, album.title, SUM(unit_price) as album_cost
FROM track
INNER JOIN album
ON album.album_id = track.album_id
GROUP BY album.album_id, album.title;

How would you query it to get the albums that cost more than $10?

·        A.)

SELECT * FROM album_cost
WHERE cost > 10;

·        B.)

You cannot, as the table and column have the same names.

·        C.)

SELECT * FROM album_cost
WHERE album_cost > 10;

·        D.)

SELECT * FROM album
WHERE album_cost > 10;

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

·        A.)

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

·        B.)

Permissions of the view would need to be changed.

·        C.)

The view is no longer being used.

·        D.)

One of the tables that the view references has added a column that has the same name as another column in another table.

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

·        A.)

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

·        B.)

The view cannot be replaced.

·        C.)

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

·        D.)

Permissions of the view would need to be changed.

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

·        A.)

Permissions of the view would need to be changed.

·        B.)

The data in the tables have changed.

·        C.)

A view needs to have additional columns added.

·        D.)

The view cannot be replaced.

Which of the following is a valid DROP VIEW statement that will remove the two views but will display an error if either view doesn't exist?

·        A.)

DROP VIEW customer_support AND employee_support;

·        B.)

DROP VIEW IF EXISTS customer_support, employee_support;

·        C.)

DROP VIEW IF EXISTS customer_support, employee_support CASCADE;

·        D.)

DROP VIEW customer_support, employee_support;

Which of the following statements would be a valid DROP VIEW statement that will automatically drop any objects that depend on the view?

·        A.)

DROP VIEW CASCADE customer_order;

·        B.)

DROP VIEW customer_order;

·        C.)

DROP VIEW customer_order CASCADE;

·        D.)

DROP VIEW IF EXISTS customer_order;

Which of the following statements would be a valid DROP VIEW statement that won't throw an error if there is no view with the name in the database?

·        A.)

DROP VIEW album_artist_names CASCADE;

·        B.)

DROP VIEW album_artist_names;

·        C.)

DROP VIEW album_artist_names IF EXISTS;

·        D.)

DROP VIEW IF EXISTS album_artist_names;

 

 

 

SmartExpert

(118)

Status NEW Posted 24 Feb 2023 02:02 AM My Price 15.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 2----------- Ch-----------all-----------eng-----------es------------sob-----------tel-----------l.c-----------om ----------- Cl-----------ick----------- li-----------nk -----------for----------- An-----------swe-----------rs -----------All----------- Co-----------rre-----------ct-----------

Attachments

file 1677207001-Unit 2 Challenges Introduction to Relational Database Management Systems.doc preview (5675 words )

----------- A ----------- bj-----------bj -----------.Dh-----------h ----------- d,-----------FL ----------- ..-----------...-----------.qG-----------MJw-----------40d-----------.4.----------- .-----------d X----------- D-----------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 2----------- Ch-----------all-----------eng-----------es------------sob-----------tel-----------l.c-----------om -----------Cli-----------ck -----------lin-----------k f-----------or -----------Ans-----------wer-----------s A-----------ll -----------Cor-----------rec-----------t -----------Uni-----------t 2----------- W-----------hic-----------h r-----------esu-----------lt -----------set----------- re-----------qui-----------res----------- a -----------JOI-----------N A-----------.) -----------Sho-----------win-----------g a-----------ll -----------tra-----------ck -----------nam-----------es -----------wit-----------h b-----------oth----------- ge-----------nre----------- ID----------- an-----------d t-----------rac-----------k I-----------D B-----------.) -----------Sho-----------win-----------g a-----------ll -----------tra-----------ck -----------nam-----------es -----------wit-----------h g-----------enr-----------e I-----------Ds -----------C.)----------- Sh-----------owi-----------ng -----------all----------- tr-----------ack----------- na-----------mes----------- wi-----------th -----------gen-----------re -----------nam-----------es -----------D.)----------- Sh-----------owi-----------ng -----------all----------- ge-----------nre----------- na-----------mes----------- wi-----------th -----------gen-----------re -----------IDs----------- W-----------hic-----------h

Not Rated(0)

• Buy Answer