Introduction to Relational Databases MILESTONE 2 SOPHIA

Introduction
to
Relational
Databases
MILESTONE
2
SOPHIA
Which
of
the
following
data
models
appropriately
models
the
relationship
of
coordinators
and
their
email
addresses?
-
ANS
Coordinator
coordinator_id
coordinator_name
Email
email_id
coordinator_id
(FK)
email_type
email_address
Which
result
set
requires
a
JOIN?
-
ANS
Showing
customer
names
with
invoice_dates
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;
-
ANS
It
represents
every
single
animal
matches
with
every
single
adopter.
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?
-
ANS
SELECT
invoice_total,
invoice.customer_id,
billing_state
FROM
invoice
JOIN
customer
ON
invoice.customer_id
=
customer.customer_id; Which
of
the
following
statements
would
be
valid
DROP
VIEW
statements?
-
ANS
DROP
VIEW
invoice_verification
CASCADE;
What
is
incorrect
regarding
the
following
statement
intended
to
create
a
VIEW?
CREATE
VIEW
priority_invoices
AS
FROM
invoice
WHERE
total
>
100;
-
ANS
The
fields
that
should
belong
in
the
result
set
are
not
specified.
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');
-
ANS
Query
#1
would
be
more
efficient
as
it
is
index
indices.
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?
-
ANS
SELECT
composer,
COUNT(*)
FROM
track
GROUP
BY
composer
HAVING
COUNT(*)
>
1;
Which
of
the
following
query
does
NOT
correctly
use
aliases?
-
ANS
SELECT
c.customer_id,
c.total,
c.last_name
FROM
invoice
AS
i
JOIN
customer
AS
c
USING
(customer_id);
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?
-
ANS
SELECT
*
FROM
invoice
WHERE
total
>
10
UNION
SELECT
*
FROM
invoice