SmartExpert

(118)

$30/per page/Negotiable

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: 6 Days 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 15.00

Introduction to Relational Database Management Systems Unit 1 Challenges

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

 

Unit 1

Which SQL clause applies conditions to filter the dataset?

launch database

  • A.)

WHERE

  • B.)

FROM

  • C.)

SELECT

 

Which SQL clause identifies one or more tables as the source for a query?

launch database

  • A.)

WHERE

  • B.)

SELECT

  • C.)

FROM

Which SQL clause retrieves zero or more rows from one or more database columns?

launch database

  • A.)

FROM

  • B.)

SELECT

  • C.)

WHERE

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

·        A.)

SELECT first name, last name, customer
FROM customer

·        B.)

SELECT first_name, last_name, customer_id
FROM customer

·        C.)

SELECT first_name last_name customer_id
FROM customer

·        D.)

SELECT *
FROM customer

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

·        A.)

SELECT customer_id, invoice_id, invoice_date
FROM invoice

·        B.)

SELECT *
FROM invoice

·        C.)

SELECT invoice, total, billing
FROM invoice

·        D.)

SELECT customer_id total billing_address
FROM invoice

Which of these SELECT statements would successfully display exactly two columns of data from the album table?

·        A.)

SELECT artist_id, title
FROM album

·        B.)

SELECT artist, title
FROM album'

·        C.)

SELECT *
FROM album

·        D.)

SELECT artist_id and title
FROM album

Using the ORDER BY clause, sort the customer table in the database by the company name of the customer in ascending order. 

Identify the 4th company name in the list.

·        A.)

Wichterlová

·        B.)

Harris

·        C.)

Google Inc.

·        D.)

JetBrains s.r.o.

Using the ORDER BY clause, sort the employee table in the database by the first name of the employee in descending order. 

Identify the 5th first name in the list.

·        A.)

Steve

·        B.)

Margaret

·        C.)

Johnson

·        D.)

Park

Using the ORDER BY clause, sort the employee table in the database by the last name of the employee in descending order.

Identify the 6th last name in the list.

·        A.)

Edwards

·        B.)

Mitchell

·        C.)

Callahan

·        D.)

Laura

Using the WHERE clause, filter the album table to only include records with an artist ID greater than 10, then identify the 6th album title from among the answer options.

·        A.)

Caetano Veloso

·        B.)

Jagged Little Pill

·        C.)

Black Sabbath

·        D.)

Chemical Wedding

Using the WHERE clause, filter the customer table to include individuals that live in the country USA. 

Identify the first name of the 5th individual listed.

·        A.)

Tim

·        B.)

Jack

·        C.)

Dan

·        D.)

Frank

Using the WHERE clause, filter the customer table to include individuals that live in the state CA. 

Identify the last name of the 2nd individual listed.

·        A.)

Goyer

·        B.)

Dan

·        C.)

Miller

·        D.)

Harris

Using the LIKE operator in the WHERE clause, filter the employee table to list the individuals that have a postal code starting with T2P. 

Identify the 2nd individual's employee ID.

·        A.)

1

·        B.)

2

·        C.)

3

·        D.)

6

Using the LIKE operator in the WHERE clause, filter the employee table to list the individuals that have a title that starts with Sales. 

Identify the 4th individual's first name.

·        A.)

Johnson

·        B.)

Jane

·        C.)

Steve

·        D.)

Margaret

Using the LIKE operator in the WHERE clause, filter the employee table to list the individuals that have an address ending with SW. 

Identify the 2nd individual's first name.

·        A.)

Nancy

·        B.)

Margaret

·        C.)

Peacock

·        D.)

Jane

Using the LIKE operator in the WHERE clause, use the necessary wildcards to filter the customer table to find the individuals that have a city that contains the pair of letters ue. 

Identify the second individual's postal code.

·        A.)

1010

·        B.)

14700

·        C.)

14300

·        D.)

1106

Using the LIKE operator in the WHERE clause, use the necessary wildcards to filter the customer table to find the individuals that have an address that contains the word Street. 

Identify the 7th individual's city.

·        A.)

Madison

·        B.)

Toronto

·        C.)

Ottawa

·        D.)

Fort Worth

Using the LIKE operator in the WHERE clause, use the necessary wildcards to filter the customer table to find the individuals that have an email that contains the word apple. 

Identify the third individual's first name.

·        A.)

Tim

·        B.)

Mercier

·        C.)

Dan

·        D.)

Isabelle

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

Identify the first billing city of the invoices.

·        A.)

Dublin

·        B.)

Paris

·        C.)

Bordeaux

·        D.)

Berlin

Using the WHERE clause, filter the invoice table to find the invoices dated February 19, 2009. 

Identify the first customer ID of the invoices.

·        A.)

2

·        B.)

16

·        C.)

11

·        D.)

13

Using the WHERE clause, filter the invoice table to find the invoices dated February 1, 2009.

Identify the first customer ID of the invoices.

·        A.)

23

·        B.)

38

·        C.)

40

·        D.)

14

Using the AND or OR statement, filter the customer table for customers who live in the country Brazil and have a last name that starts with A. 

Identify the customer ID of the first record.

·        A.)

12

·        B.)

10

·        C.)

1

·        D.)

11

Using the AND or OR statement, filter the customer table for customers who have a gmail.com email address and live in the country Canada. 

Identify the last name of the second record.

·        A.)

Silk

·        B.)

Tremblay

·        C.)

Peterson

·        D.)

Philips

Using the AND or OR statement, filter the customer table for customers who live in the country Canada or the USA. 

Identify the first name of the fourth record.

·        A.)

Frank

·        B.)

Jack

·        C.)

Tim

·        D.)

Robert

Using the IN operator, filter the customer table to find the customers who live in the countries Brazil, Canada, or the USA. 

Identify the state of the fifth record.

·        A.)

DF

·        B.)

RJ

·        C.)

CA

·        D.)

ON

Using the IN operator, filter the customer table to find the customers who live in the states CA, WA, NY, or NV. 

Identify the city of the fourth record.

·        A.)

Cupertino

·        B.)

Mountain View

·        C.)

Reno

·        D.)

New York

Using the IN operator, filter the customer table to find the customers who live in the states FL, IL, WI, or AZ. 

Identify the city of the third record.

·        A.)

Chicago

·        B.)

Orlando

·        C.)

Madison

·        D.)

Tuscon

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

Identify the third customer ID.

·        A.)

21

·        B.)

7

·        C.)

22

·        D.)

57

Using the BETWEEN operator, filter the invoice table to find the invoices that have a total between $2 and $6. 

Identify the fifth customer ID.

·        A.)

8

·        B.)

42

·        C.)

40

·        D.)

5

Using the BETWEEN operator, filter the invoice table to find the invoices that have a total between $15 and $25. 

Identify the seventh customer ID.

·        A.)

4

·        B.)

25

·        C.)

7

·        D.)

26

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

CREATE TABLE user
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.

·        A.)

3

·        B.)

2

·        C.)

1

·        D.)

4

The following CREATE TABLE statement creates a table called 'company' that consists of the company_id as the primary key, the company_name, and the address.

CREATE TABLE company(
company_id INT PRIMARY KEY
company_name VARCHAR (100),
address VARCHAR (100)
);

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

·        A.)

1

·        B.)

2

·        C.)

3

·        D.)

4

The following CREATE TABLE statement creates a table called 'company' that consists of the company_id as the primary key, the company_name, and the address.

CREATE TABLE company(
company_id INT PRIMARY KEY,
company_name VARCHAR,
address VARCHAR (100)
);

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

·        A.)

3

·        B.)

4

·        C.)

2

·        D.)

1

Which of these constraints ensures that the column cannot be empty?

·        A.)

NOT EMPTY

·        B.)

FOREIGN KEY

·        C.)

NOT NULL

·        D.)

UNIQUE

Which of these constraints ensures that all values in a column are BOTH different and not empty?

·        A.)

UNIQUE

·        B.)

NOT NULL

·        C.)

PRIMARY KEY

·        D.)

FOREIGN KEY

Which of these constraints can be used to verify that the data is in a specific range?

·        A.)

UNIQUE

·        B.)

CHECK

·        C.)

FOREIGN KEY

·        D.)

PRIMARY KEY

The following CREATE TABLE statement should create a table called 'employee' that consists of the employee_id as the primary key that is auto-incremented, the first_name, and the last_name.

CREATE TABLE employee
employee_id serial PRIMARY KEY,
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. 

·        A.)

2

·        B.)

3

·        C.)

4

·        D.)

1

The following CREATE TABLE statement should create a table called 'users' that consists of the user_id as the primary key that is auto-incremented, 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 either generate a syntax, logical, or requirements error in this CREATE TABLE statement. 

·        A.)

1

·        B.)

3

·        C.)

2

·        D.)

4

The following CREATE TABLE statement should create a table called 'company' that consists of the company_id as the primary key that is auto-incremented, the company_name, and the address.

CREATE TABLE company(
company_id serial PRIMARY KEY,
company_name int,
address VARCHAR (100)
);

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

·        A.)

1

·        B.)

3

·        C.)

2

·        D.)

4

Select the correctly constructed CHECK constraint to validate the total column of type int, to ensure that values placed into it are greater than 0.

·        A.)

CHECK (total > 0)

·        B.)

CHECK (total > -1)

·        C.)

CHECK (total column > 0)

·        D.)

CHECK (total >= 0)

Select the correctly constructed CHECK constraint to validate the total column of type int, to ensure that values placed into it are greater than or equal to 0.

·        A.)

CHECK (total > -1)

·        B.)

CHECK (total > 0)

·        C.)

CHECK (total >= 0)

·        D.)

CHECK (total column > 0)

Select the correctly constructed CHECK constraint to validate the cost column of type int, to ensure that values placed into it are at least 0 and below or equal to 1000.

·        A.)

CHECK (cost >= 0 OR cost <= 1000)

·        B.)

CHECK (cost > 0 AND cost < 1000)

·        C.)

CHECK (cost >= 0 AND cost <= 1000)

·        D.)

CHECK (cost <= 0 AND cost >= 1000)

Identify the correctly constructed ALTER TABLE statement to add a UNIQUE constraint to the column phone_number with the constraint name phone_number_unique on the table called 'customer'.

·        A.)

ALTER TABLE customer ADD UNIQUE phone_number CONSTRAINT (phone_number_unique);

·        B.)

ALTER TABLE customer ADD CONSTRAINT phone_number UNIQUE (phone_number_unique);

·        C.)

ALTER TABLE customer ADD CONSTRAINT phone_number_unique UNIQUE (phone_number);

·        D.)

ALTER TABLE customer ADD CONSTRAINT phone_number UNIQUE (phone_number);

Identify the correctly constructed ALTER TABLE statement to add a UNIQUE constraint to the column customer_number with the constraint name customer_number_unique on the table called 'customer'.

·        A.)

ALTER TABLE customer ADD CONSTRAINT customer_number UNIQUE (customer_number);

·        B.)

ALTER TABLE customer ADD UNIQUE customer_number CONSTRAINT (customer_number_unique);

·        C.)

ALTER TABLE customer ADD CONSTRAINT customer_number UNIQUE (customer_number_unique);

·        D.)

ALTER TABLE customer ADD CONSTRAINT customer_number_unique UNIQUE (customer_number);

Identify the correctly constructed ALTER TABLE statement to add a UNIQUE constraint to the column user_number with the constraint name user_number_unique on the table called 'user'.

·        A.)

ALTER TABLE user ADD CONSTRAINT user_number UNIQUE (user_number_unique);

·        B.)

ALTER TABLE user ADD CONSTRAINT user_number_unique UNIQUE (user_number);

·        C.)

ALTER TABLE user ADD CONSTRAINT user_number UNIQUE (user_number);

·        D.)

ALTER TABLE user ADD UNIQUE user_number CONSTRAINT (user_number_unique);

Identify the correctly constructed ALTER TABLE statement that adds the column password as a VARCHAR (100) to the user table.

·        A.)

ALTER TABLE user DROP password VARCHAR (100);

·        B.)

ALTER TABLE user ADD pass VARCHAR (100);

·        C.)

ALTER TABLE user ADD password;

·        D.)

ALTER TABLE user ADD password VARCHAR (100);

Identify the correctly constructed ALTER TABLE statement that adds the column address as a VARCHAR (100) to the customer table.

·        A.)

ALTER TABLE customer ADD address;

·        B.)

ALTER TABLE customer DROP address VARCHAR (100);

·        C.)

ALTER TABLE customer ADD home_address VARCHAR (100);

·        D.)

ALTER TABLE customer ADD address VARCHAR (100);

Identify the correctly constructed ALTER TABLE statement that adds the column age as an int to the user table.

·        A.)

ALTER TABLE user DROP age int;

·        B.)

ALTER TABLE user ADD age;

·        C.)

ALTER TABLE user ADD age_now int;

·        D.)

ALTER TABLE user ADD age int;

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?

·        A.)

ALTER TABLE employee ALTER COLUMN postal_code TYPE TEXT;

·        B.)

ALTER TABLE customer ALTER COLUMN city TYPE INT;

·        C.)

ALTER TABLE employee ALTER COLUMN email VARCHAR (100);

·        D.)

ALTER TABLE employee ALTER COLUMN email TYPE INT;

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?

·        A.)

ALTER TABLE invoice ALTER COLUMN invoice_date TYPE INT

·        B.)

ALTER TABLE customer ALTER COLUMN billing_city VARCHAR (100);

·        C.)

ALTER TABLE invoice ALTER COLUMN billing_state int;

·        D.)

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

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?

·        A.)

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

·        B.)

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

·        C.)

ALTER TABLE customer ALTER COLUMN city TYPE INT;

·        D.)

ALTER TABLE customer ALTER COLUMN postal_code TYPE text;

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?

·        A.)

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

·        B.)

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

·        C.)

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

·        D.)

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

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?

·        A.)

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

·        B.)

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

·        C.)

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

·        D.)

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

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?

·        A.)

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

·        B.)

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

·        C.)

ALTER TABLE employee ALTER COLUMN state VARCHAR (50);

·        D.)

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

Given the tables provided, which of the following DROP TABLE series of statements would correctly remove the tables without causing an error?

·        A.)

DROP TABLE genre;
DROP TABLE album;
DROP TABLE artist;

·        B.)

DROP TABLE playlist_track;
DROP TABLE invoice_line;
DROP TABLE playlist;

·        C.)

DROP TABLE track;
DROP TABLE playlist_track;
DROP TABLE artist;

·        D.)

DROP TABLE playlist_track;
DROP TABLE playlist;
DROP TABLE genre;

Given the tables provided, which of the following DROP TABLE series of statements would correctly remove the tables without causing an error?

·        A.)

DROP TABLE track;
DROP TABLE playlist_track;
DROP TABLE artist;

·        B.)

DROP TABLE invoice_line;
DROP TABLE invoice;
DROP TABLE customer;

·        C.)

DROP TABLE invoice;
DROP TABLE invoice_line;
DROP TABLE customer;

·        D.)

DROP TABLE customer;
DROP TABLE invoice;
DROP TABLE invoice_line;

Given the tables provided, which of the following DROP TABLE series of statements would correctly remove the tables without causing an error?

·        A.)

DROP TABLE album;
DROP TABLE artist;
DROP TABLE playlist;

·        B.)

DROP TABLE playlist_track;
DROP TABLE playlist;
DROP TABLE invoice_line;

·        C.)

DROP TABLE playlist_track;
DROP TABLE artist;
DROP TABLE playlist;

·        D.)

DROP TABLE track;
DROP TABLE playlist_track;
DROP TABLE playlist;

Identify the SQL command that uses an aggregate function that could be used to find the lowest total in the invoice table.

·        A.)

SELECT total(min) FROM invoice;

·        B.)

SELECT min total FROM invoice;

·        C.)

SELECT lowest(total) FROM invoice;

·        D.)

SELECT min(total) FROM invoice;

Identify the SQL command that uses an aggregate function that could be used to find the highest total in the invoice table.

·        A.)

SELECT sum(total) FROM invoice;

·        B.)

SELECT max(total) FROM invoice;

·        C.)

SELECT HIGHEST(total) FROM invoice;

·        D.)

SELECT LARGE(total) FROM invoice;

Identify the SQL command that uses an aggregate function that could be used to find the average total in the invoice table.

·        A.)

SELECT sum(total) from invoice;

·        B.)

SELECT sum(total)/max(total) from invoice;

·        C.)

SELECT avg(total) from invoice;

·        D.)

SELECT average total FROM invoice;

USING the SELECT statement, query the customer table ordered by the customer_id. Set the LIMIT to 10 and OFFSET to 10. 

What is the last name of the last row returned?

·        A.)

Martins

·        B.)

Miller

·        C.)

Rocha

·        D.)

Goyer

USING the SELECT statement, query the customer table ordered by the customer_id. Set the LIMIT to 15 and OFFSET to 5. 

What is the first name of the last row returned?

·        A.)

Helena

·        B.)

Astrid

·        C.)

Tim

·        D.)

Dan

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

What is the country of the 5th row returned?

·        A.)

Prague

·        B.)

Brazil

·        C.)

Denmark

·        D.)

Belgium

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

·        A.)

26

·        B.)

14

·        C.)

12

·        D.)

24

Using the SELECT statement, query the invoice table to find the maximum total where the billing_country is not equal to USA.

·        A.)

24

·        B.)

23

·        C.)

1

·        D.)

26

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

·        A.)

26

·        B.)

23

·        C.)

1

·        D.)

24

Using the SELECT statement, query the track table to find the number of tracks that are in the genre_id not equal to 1.

·        A.)

3503

·        B.)

2206

·        C.)

1297

·        D.)

374

Using the SELECT statement, query the track table to find the number of tracks that are in the genre_id equal to 1.

·        A.)

2206

·        B.)

374

·        C.)

3503

·        D.)

1297

Using the SELECT statement, query the track table to find the number of tracks that are in the genre_id equal to 3.

·        A.)

2206

·        B.)

3503

·        C.)

3129

·        D.)

374

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

·        A.)

0

·        B.)

454

·        C.)

3486

·        D.)

1897

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

·        A.)

454

·        B.)

1411

·        C.)

486

·        D.)

3486

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

·        A.)

2351

·        B.)

19103

·        C.)

1823

·        D.)

528

Using the SELECT statement, query the track table to find the average length of a track that has the album_id equal to 4.

·        A.)

306657.3750 milliseconds

·        B.)

393798.2206 milliseconds

·        C.)

393599.2121 milliseconds

·        D.)

283910.0432 milliseconds

Using the SELECT statement, query the track table to find the average length of a track that has the genre_id equal to 1.

·        A.)

393599.2121 milliseconds

·        B.)

283910.0432 milliseconds

·        C.)

291755291755 milliseconds

·        D.)

458090.0789 milliseconds

Using the SELECT statement, query the track table to find the average length of a track that has the genre_id not equal to 1.

·        A.)

458090.0789 milliseconds

·        B.)

283910.0432 milliseconds

·        C.)

393599.2121 milliseconds

·        D.)

291755291755 milliseconds

Using the SELECT statement, query the invoice table to find the average total cost for all orders purchased by the customer_id equal to 1, rounded to the nearest cent.

·        A.)

5.71

·        B.)

5.7

·        C.)

5.62

·        D.)

5.70

Using the SELECT statement, query the invoice table to find the average total cost for all orders placed before 2011-01-01, rounded to the nearest dollar.

·        A.)

5.66

·        B.)

6

·        C.)

5.7

·        D.)

5

Using the SELECT statement, query the invoice table to find the average total cost for all orders purchased in the country USA, rounded to the nearest cent.

·        A.)

5.8

·        B.)

5.80

·        C.)

5.7

·        D.)

6

Using the GROUP BY clause and the count aggregate function, filter the customer table to group the customers based on country. 

How many customers are in the USA?

·        A.)

4

·        B.)

13

·        C.)

14

·        D.)

12

Using the GROUP BY clause and the count aggregate function, filter the customer table to group the customers based on country. 

How many customers are in Canada?

·        A.)

5

·        B.)

8

·        C.)

4

·        D.)

1

Using the GROUP BY clause and the count aggregate function, filter the customer table to group the customers based on country. 

How many customers are in France?

·        A.)

4

·        B.)

3

·        C.)

6

·        D.)

5

Using the GROUP BY and HAVING clauses, filter the track table by album_id. 

How many groups have more than 34 tracks?

·        A.)

0

·        B.)

1

·        C.)

2

·        D.)

3

Using the GROUP BY and HAVING clauses, filter the track table by album_id. 

How many groups have more than 30 tracks?

·        A.)

4

·        B.)

1

·        C.)

2

·        D.)

3

Using the WHERE and HAVING clauses, filter the invoice table to include customer_id values of 1, 2, or 3. Group by the customer_id having the maximum value of the orders greater than 10. Provide the list of customer_id's and the sum of the totals that fit these criteria. 

Which of the following queries would provide the correct results?

·        A.)

select customer_id, sum(total)
FROM invoice
HAVING max(total)>10
GROUP BY customer_id
WHERE customer_id in (1,2,3)

·        B.)

select customer_id, sum(total)
FROM invoice
WHERE customer_id in (1,2,3)
GROUP BY customer_id
HAVING max(total)>10

·        C.)

select customer_id, sum(total)
FROM invoice
WHERE max(total)>10
GROUP BY customer_id
HAVING customer_id in (1,2,3)

·        D.)

select customer_id, max(total)
FROM invoice
WHERE customer_id in (1,2,3)
GROUP BY customer_id
HAVING sum(total)>10

Using the WHERE and HAVING clauses, filter the invoice table to include the billing_country as either the USA, France, or Canada. Group the invoices by country, having the number of orders greater than 40. Provide the list of countries and their counts that fit these criteria. 

Which of the following queries would provide the correct results?

·        A.)

select billing_country, count(*)
FROM invoice
GROUP BY billing_country
WHERE billing_country in ('France','USA','Canada')
HAVING count(*) > 40

·        B.)

select billing_country, count(*)
FROM invoice
WHERE billing_country in ('France','USA','Canada')
GROUP BY billing_country
HAVING count(*) > 40

·        C.)

select billing_country, count(*)
FROM invoice
WHERE billing_country in ('France','USA','Canada')
GROUP BY billing_country
HAVING sum(*) > 40

·        D.)

select billing_country, count(*)
FROM invoice
WHERE count(*) > 40
GROUP BY billing_country
HAVING billing_country in ('France','USA','Canada')

Using the WHERE and HAVING clauses, filter the invoice table to include the billing_country as either India, Belgium, or Sweden. Group by country, having the sum of the total orders greater than 0. Provide the list of countries and the average of the totals that fit these criteria. 

Which of the following queries would provide the correct results?

·        A.)

select billing_country, avg(total)
FROM invoice
GROUP BY billing_country
WHERE billing_country in ('India','Belgium','Sweden')
HAVING sum(total)>0

·        B.)

select billing_country, avg(total)
FROM invoice
WHERE sum(total)>0
GROUP BY billing_country
HAVING billing_country in ('India','Belgium','Sweden')

·        C.)

select billing_country, sum(total)
FROM invoice
WHERE billing_country in ('India','Belgium','Sweden')
GROUP BY billing_country
HAVING avg(total)>0

·        D.)

select billing_country, avg(total)
FROM invoice
WHERE billing_country in ('India','Belgium','Sweden')
GROUP BY billing_country
HAVING sum(total)>0

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

·        A.)

insert into album (artist_id, title, album_id) values (1, 'Greatest Hits', 984)

·        B.)

insert into album (artist_id, title, album_id) values (1, 'Greatest Hits', 1)

·        C.)

insert into album (artist_id, title, album_id) values (1, Greatest Hits, 984)

·        D.)

insert into album (artist_id, title, album_id) values (984, 'Greatest Hits', 1)

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

·        A.)

insert into album (album_id, title, artist_id) values (5, 'New Album', 400)

·        B.)

insert into album (album_id, title, artist_id) values (400, 'New Album', 400)

·        C.)

insert into album (album_id, title, artist_id) values (400, New Album, 5)

·        D.)

insert into album (album_id, title, artist_id) values (400, 'New Album', 5)

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

·        A.)

insert into album (artist_id, title, album_id) values (1, 'My Album', 1)

·        B.)

insert into album (artist_id, title, album_id) values (1, 'My Album', 348)

·        C.)

insert into album (artist_id, title, album_id) values (348, 'My Album', 1)

·        D.)

insert into album (artist_id, title, album_id) values (1, My Album, 348)

Consider the following table:

CREATE TABLE book(
book_id SERIAL PRIMARY KEY,
book_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?

·        A.)

insert into book (book_name) values (Lord of the Rings)

·        B.)

insert into book (book_name) values ('Lord of the Rings' )

·        C.)

insert into book (book_id, book_name) values (nextval, 'Lord of the Rings')

·        D.)

insert into book (book_id, book_name) values (1, 'Lord of the Rings' )

Consider the following table:

CREATE TABLE product(
product_id SERIAL PRIMARY KEY,
product_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?

·        A.)

insert into product (product_name) values ('Desk' )

·        B.)

insert into product (product_id, product_name) values (nextval, 'Desk')

·        C.)

insert into product (product_id, product_name) values (1, 'Desk' )

·        D.)

insert into product (product_name) values (Desk)

Consider the following table:

CREATE TABLE movie(
movie_id SERIAL PRIMARY KEY,
movie_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?

·        A.)

insert into movie (movie_name) values (Star Wars)

·        B.)

insert into movie (movie_name) values ('Star Wars' )

·        C.)

insert into movie (movie_id, movie_name) values (1, 'Star Wars' )

·        D.)

insert into movie (movie_id, movie_name) values (nextval, 'Star Wars')

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

·        A.)

insert into playlist (name,playlist_id) values (30, 'New Age Playlist'), (31, 'Oldies'), (32, 'Road Trip')

·        B.)

insert into playlist (playlist_id, name) values (30, 'New Age Playlist'), (31, 'Oldies'), (32, 'Road Trip')

·        C.)

insert into playlist (playlist_id, name) values (30, 'New Age Playlist') (31, 'Oldies') (32, 'Road Trip')

·        D.)

insert into playlist (playlist_id, name) values (30, 'New Age Playlist') (31, 'Oldies') (31, 'Road Trip')

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

·        A.)

insert into artist (artist_id, name) values (400, 'Jonas Brother') (401, 'Adele') (402, 'Pink')

·        B.)

insert into artist (artist_id, name) values (400, 'Jonas Brother'), (401, 'Adele'), (402, 'Pink')

·        C.)

insert into artist (name, artist_id) values (400, 'Jonas Brother'), (401, 'Adele'), (402, 'Pink')

·        D.)

insert into artist (artist_id, name) values (400, 'Jonas Brother'), (401, 'Adele'), (401, 'Pink')

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

·        A.)

INSERT INTO genre (genre_id, name)
VALUES (30, 'Funk' )
(31,'Classical')
(32,'Pop Rock');

·        B.)

INSERT INTO genre (name,genre_id)
VALUES (30, 'Funk' ),(31,'Classical'),(32,'Pop Rock');

·        C.)

INSERT INTO genre (genre_id, name) VALUES (30, 'Funk' ),(31,'Classical','Rock'),(32,'Pop Rock');

·        D.)

INSERT INTO genre (genre_id, name)
VALUES (30, 'Funk' ),(31,'Classical'),(32,'Pop Rock');

Consider the following new table:

CREATE TABLE league(
league_id SERIAL PRIMARY KEY,
first_name VARCHAR NOT NULL,
last_name VARCHAR NOT NULL,
email VARCHAR
);

Given this new table, which INSERT statement would query from the employee table to insert the first name, last name, and email address of all employees in the right columns?

·        A.)

INSERT INTO league (email, last_name, first_name)
SELECT first_name, last_name, email FROM employee;

·        B.)

INSERT INTO league
SELECT first_name, last_name, email FROM employee;

·        C.)

INSERT INTO league (league_id, first_name, last_name, email)
SELECT first_name, last_name, email FROM employee;

·        D.)

INSERT INTO league (first_name, last_name, email)
SELECT first_name, last_name, email FROM employee;

Consider the following new table:

CREATE TABLE volunteer(
volunteer_id SERIAL PRIMARY KEY,
first_name VARCHAR NOT NULL,
last_name VARCHAR NOT NULL,
phone VARCHAR
);

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

·        A.)

INSERT INTO volunteer
SELECT first_name, last_name, phone FROM customer;

·        B.)

INSERT INTO volunteer (volunteer_id, first_name, last_name, phone)
SELECT first_name, last_name, phone FROM customer;

·        C.)

INSERT INTO volunteer (first_name, last_name, phone)
SELECT first_name, last_name, phone FROM customer;

·        D.)

INSERT INTO volunteer (phone, last_name, first_name)
SELECT first_name, last_name, phone FROM customer;

Consider the following new table:

CREATE TABLE mailinglist(
user_id SERIAL PRIMARY KEY,
email VARCHAR NOT NULL
);

Given this new table, which INSERT statement would query from the customer table to insert the email address of all customers that live in the country USA?

·        A.)

INSERT INTO mailinglist
SELECT email FROM customer
WHERE country = 'USA';

·        B.)

INSERT INTO mailinglist (email)
SELECT email FROM customer
WHERE country = 'USA';

·        C.)

INSERT INTO mailinglist (user_id, email)
SELECT email FROM customer
WHERE country = USA;

·        D.)

INSERT INTO mailinglist (email)
SELECT email FROM customer;

Which of the following UPDATE statements would successfully update the album table where the album_id is 11, to set the name to Out of Exile [Disc 1]?

·        A.)

UPDATE album
SET album_id = 11
WHERE title = 'Out of Exile [Disc 1]'

·        B.)

UPDATE album
SET title = Out of Exile [Disc 1]
WHERE album_id = 11

·        C.)

UPDATE album
WHERE album_id = 11
SET title = 'Out of Exile [Disc 1]'

·        D.)

UPDATE album
SET title = 'Out of Exile [Disc 1]'
WHERE album_id = 11

Which of the following UPDATE statements would successfully update the customer table where the first_name is Daan and last name is Peeters, to set the phone number to 9991112222 and set the email to daanpeeters@gmail.com?

·        A.)

UPDATE customer
SET phone='9991112222' AND email='daanpeeters@gmail.com'
WHERE first_name = 'Daan' AND last_name='Peeters'

·        B.)

UPDATE customer 'Daan' 'Peeters'
SET phone='9991112222', email='daanpeeters@gmail.com'

·        C.)

UPDATE customer
SET phone='9991112222', email='daanpeeters@gmail.com'
WHERE first_name = 'Daan' AND last_name='Peeters'

·        D.)

UPDATE customer
SET first_name = 'Daan', last_name='Peeters'
WHERE phone='9991112222' AND email='daanpeeters@gmail.com'

 

Which of the following UPDATE statements would successfully update the customer table where the customer_id is 9, to set the company to Telus and the support_rep_id to 6?

·        A.)


UPDATE customer
SET company=Telus, support_rep_id=6
WHERE customer_id = 9

·        B.)

UPDATE customer
SET company='Telus', support_rep_id=6

·        C.)

UPDATE customer
SET company='Telus' AND support_rep_id=6
WHERE customer_id = 9

·        D.)

UPDATE customer
SET company='Telus', support_rep_id=6
WHERE customer_id = 9

Which of the following UPDATE statements would update every employee's fax number to the centralized number + (780) 865-9992 and set the reports_to column to 1 for all employees that don't have 'Manager' in their title?

·        A.)

UPDATE employee
SET fax = '+ (780) 865-9992', reports_to = 1
WHERE title NOT LIKE 'Manager%'

·        B.)

UPDATE employee
SET fax = '+ (780) 865-9992', reports_to = 1
WHERE title LIKE '%Manager%'

·        C.)


UPDATE employee
SET fax = '+ (780) 865-9992' AND reports_to = 1
WHERE title NOT LIKE '%Manager%'

·        D.)

UPDATE employee
SET fax = '+ (780) 865-9992', reports_to = 1
WHERE title NOT LIKE '%Manager%'

Which of the following UPDATE statements would update every employee's phone number to a centralized number set to + (780) 444-5555, other than the employee who is the General Manager?

·        A.)

update employee
SET phone = '+ (780) 444-5555'
WHERE title <> 'General Manager'

·        B.)

update employee
SET phone = '+ (780) 444-5555'
WHERE title = 'General Manager'

·        C.)

update employee
SET phone '+ (780) 444-5555'
WHERE employee_id > 2

·        D.)

update employee
WHERE title <> 'General Manager'
SET phone = '+ (780) 444-5555'

Which of the following UPDATE statements would update the composer to Alfred Hitch for all tracks where the album_id is set to 5 or 6?

·        A.)

UPDATE track
SET composer = 'Alfred Hitch'
WHERE album_id 5 AND album_id 6

·        B.)

UPDATE track
SET composer = 'Alfred Hitch'
WHERE album_id = 5, 6

·        C.)

UPDATE track
SET composer = 'Alfred Hitch'
WHERE album_id = 5 OR album_id = 6

·        D.)

UPDATE track
WHERE album_id = 5 OR album_id = 6
SET composer = 'Alfred Hitch'

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

·        A.)

DELETE FROM invoice_line WHERE invoice_line_id = 5;
DELETE FROM invoice WHERE invoice_line_id = 5;

·        B.)

DELETE FROM invoice_line WHERE invoice_line_id = 5;

·        C.)

DELETE FROM invoice WHERE invoice_line_id = 5;
DELETE FROM invoice_line WHERE invoiceline__id = 5;

·        D.)

DELETE FROM invoice WHERE invoice_line_id = 5;

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

·        A.)

DELETE FROM playlist_id WHERE playlist_id_id = 16;
DELETE FROM playlist_track WHERE playlist_id_id = 16;

·        B.)

DELETE FROM playlist_track WHERE playlist_id_id = 16;

·        C.)

DELETE FROM playlist_id WHERE playlist_id_id = 16;

·        D.)

DELETE from playlist_track WHERE playlist_id = 16;
DELETE from playlist WHERE playlist_id = 16;

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

·        A.)

DELETE FROM invoice WHERE invoice_id = 353;

·        B.)

DELETE FROM invoice WHERE invoice_id = 353;
DELETE FROM invoice_line WHERE invoice_id = 353;

·        C.)

DELETE FROM invoice_line WHERE invoice_id = 353;
DELETE FROM invoice WHERE invoice_id = 353;

·        D.)

DELETE FROM invoice_line WHERE invoice_id = 353;

 

Answers

(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 1----------- Ch-----------all-----------eng-----------es------------sob-----------tel-----------l.c-----------om-----------

Attachments

file 1677206591-Unit 1 Challenges Introduction to Relational Database Management Systems.doc preview (5572 words )
RT-----------OPQ-----------A b-----------jbj----------- . -----------hh ----------- 22-----------22T-----------2T,-----------rSS-----------SSS-----------SSA-----------VXL-----------SSS-----------m2m-----------2m2-----------Sm2-----------Sm2-----------m28-----------IPS-----------kGY-----------QSS-----------0Tt-----------RCY-----------2(C-----------YPS-----------CYP-----------S0m-----------2SS-----------E2(-----------TCY----------- X ----------- DC-----------IS -----------111----------- SO-----------PHI-----------A-S-----------TRA-----------YER----------- In-----------tro-----------duc-----------tio-----------n t-----------o R-----------ela-----------tio-----------nal----------- Da-----------tab-----------ase----------- Ma-----------nag-----------eme-----------nt -----------Sys-----------tem-----------s U-----------nit----------- 1 -----------Cha-----------lle-----------nge-----------s-s-----------obt-----------ell-----------.co-----------m -----------Uni-----------t 1----------- Wh-----------ich----------- SQ-----------L c-----------lau-----------se -----------app-----------lie-----------s c-----------ond-----------iti-----------ons----------- to----------- fi-----------lte-----------r t-----------he -----------dat-----------ase-----------t -----------HYP-----------ERL-----------INK----------- ht-----------tps-----------//p-----------ost-----------gre-----------s.s-----------oph-----------ia.-----------org----------- t -----------_bl-----------ank----------- la-----------unc-----------h d-----------ata-----------bas-----------e A-----------.) -----------WHE-----------RE -----------B.)----------- FR-----------OM -----------C.)----------- SE-----------LEC-----------T -----------Whi-----------ch -----------SQL----------- cl-----------aus-----------e i-----------den-----------tif-----------ies----------- on-----------e o-----------r m-----------ore----------- ta-----------ble-----------s a-----------s t-----------he -----------sou-----------rce----------- fo-----------r
Not Rated(0)