Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

MySQL > Exam 1 > Flashcards

Exam 1 Flashcards

1
Q

The interface between an application program and the DBMS is usually provided by the

A

Data access API

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

The processing that’s done by the DBMS is typically referred to as

A

Back-end processing

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

Which of the following types of statements does not modify the data in a table?

a. Select
b. Insert
c. Update
d. Delete

A

Select

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

A database driver is software tat lets the

A

Data access model communicate with the database

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

The SQL statements that work wit the data in a database are called

A

Data manipulation language

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

Which of the following recommendations won’t improve the readability of your SQL statements?

a. Break long clauses into multiple lines
b. Use comments to describe what each statement does
c. Start each clause on a new line
d. Indent continued lines

A

Use comments to describe what each statement does

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

The type of operation that retrieves data from two or more tables is called a

A

Join

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

A single table in a relational database consists of

A

Rows and columns

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

Which of the following types of SQL statements is not a DML statement?

a. Insert
b. Update
c. Select
d. Create table

A

Create table

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

What determines that kind of data that can be stored in a column of a table?

A

The data type that’s assigned to the column

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

The online version of the MySQL Reference Manual lets you

a. Use the links in the left sidebar to drill down to the information you’re looking for
b. Search for a specific word or phrase
c. Return to the Home page
d. All of the above

A

All of the above

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

To start the server in MySQL Workbench, you can click the Start Server button in the

A

Tab that’s displayed when you select Startup/Shutdown from the Navigator window

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

In a SQL Editor tab of MySQL Workbench, you can do all but one of the following. Which one is it?

a. create a database diagram
b. display a SQL script
c. run a SQL script
d. code SQL statements

A

Create a database diagram

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

A file that contains one or more SQL statements is called a

A

Script

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

Which of the following is not a common error when entering and executing SQL statements?

a. misspelling a keyword
b. misspelling the name of a table
c. selecting the wrong database
d. forgetting to connect to a database server

A

Forgetting to connect to a database server

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

Which of the following is not a database object in MySQL?

a. table
b. view
c. EER diagram
d. function

A

EER diagram

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

Before you can run a SQL statement, you must

a. Open a SQL tab
b. Enter the statement in the SQL editor
c. Select the database that the statement will be executed against
d. All of the above

A

All of the above

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

To run SQL statements at the command line, you can use the

A

MySQL Command Line Client

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

To save your own SQL statement in MySQL Workbench so you don’t have to type it each time, you can create a new

A

Snippet

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

Which of the following can you do when working with a table in MySQL Workbench?

a. view the column definitions for the table
b. view the data for the table
c. edit the column definitions for the table
d. all of the above
e. a and b only

A

All of the above

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

Unlike a join, a union

A

Combines the result sets of two or more SELECT statements

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

To join a table in one database to a table in another database, you must

A

Prefix the name of the table in the other database with the name of that database

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

Which of the following is not a reason for using the explicit syntax instead of the implicit syntax for joins? The explicit syntax

a. Is easier to read and understand
b. Lets you separate the join and search conditions
c. Is an older syntax that works with legacy code
d. Lets you combine inner and outer joins

A

Is an older syntax that works with legacy code

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

When you code a union that combines two result sets, which of the following is not true?

a. Each result set must have the same number of columns
b. The result sets may or may not have any duplicate rows
c. The corresponding columns in the result sets must have compatible data types
d. The result sets must be delivered from different tables

A

The result sets must be delivered from different tables

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

When you use the USING keyword for a join,

A

The join must be based on a column or columns that have the same name in both tables

26
Q

To join each row from the first table with each row from the second table, you use a

A

Cross join

27
Q

You can simulate a full outer join by using

A

A union

28
Q

Which of the following can you not use in a join condition?

a. Logical operators
b. Comparison operators
c. Arithmetic operators

A

Arithmetic operators

29
Q

If you assign an alias to one table in a join, you have to

A

Use that alias to refer to the table throughout your query

30
Q

In a join, column names need to be qualified only

A

When the same column names exist in both tables

31
Q

How would you code a SELECT clause so it returns all columns from the base table?

A

SELECT *

32
Q

Which of the following would return a maximum of 7 rows, starting with the 4th row?

a. LIMIT 7, 3
b. LIMIT 3, 7
c. LIMIT 3
d. LIMIT 3, 6

A

LIMIT 3, 7

33
Q

When coded in a WHERE clause, which of the following would not return rows for vendors in the cities of San Diego and Santa Ana?

a. vendor_city LIKE ‘SAN%’
b. vendor_city REGEXP ‘NA$’
c. vendor_city REGEXP ‘^SA’

A

vendor_city REGEXP ‘NA$’

34
Q

The order of precedence for the logical operators in a WHERE clause is

A

Not, And, Or

35
Q

Which of the following is not a valid column alias?

a. total
b. total sales
c. total_sales
d. “Total Sales”

A

total sales

36
Q

When coded in a WHERE clause, which search condition will return invoices when payment_date isn’t null and invoice_total is greater than or equal to $500?

a. payment_date IS NULL AND invoice_total > 500
b. payment_date IS NOT NULL OR invoice_total >= 500
c. NOT (payment_date IS NULL AND invoice_total <= 500)
d. payment_date IS NOT NULL AND invoice_total >= 500

A

payment_date IS NOT NULL AND invoice_total >= 500

37
Q

Which of the following statements about sorting the rows in a result set is not true?

a. Null values always appear first in the sort sequence.
b. The sort order is determined by the character set you’re using.
c. A column that’s used for sorting must be included in the SELECT clause.
d. The rows can be sorted by any number of columns in either ascending or descending sequence

A

A column that’s used for sorting must be included in the SELECT clause.

38
Q

What is the result of the expression that follows if the value of last_name is “Smith” and the value of first_name is “Steve”?
CONCAT(last_name, first_name)

A

SmithSteve

39
Q

Which of the following WHERE clauses will return vendor names from A to C?

a. WHERE vendor_name = D
b. WHERE vendor_name < ‘D’
c. WHERE vendor_name = ‘D’
d. WHERE vendor_name < ‘C’

A

WHERE vendor_name < ‘D’

40
Q

Which clause of the SELECT statement specifies the table that contains the data to be retrieved?

a. SELECT
b. FROM
c. WHERE
d. ORDER BY

A

FROM

41
Q

Before you can delete a row that has related rows in another table, you should

A

Delete related rows

42
Q

When you use a subquery to insert rows from one table into another table, which of the following statements is not true?

a. The target table must already exist
b. You must omit the column list from the INSERT statement
c. You must omit the VALUES clause from the INSERT statement
d. You must include a SELECT statement that retrieves the rows to be inserted

A

You must omit the column list from the INSERT statement

43
Q

Code example 5-1

team_id INT NOT NULL
AUTO_INCREMENT,
team_name VARCHAR(50),
team_rank INT NOT NULL DEFAULT 0

(Refer to code example 5-1.) What are the values of the team_id, team_name, and team_rank columns after the following statement is executed, assuming that the Teams table has one row in it with a team_id of 1?
INSERT INTO teams
VALUES (DEFAULT, NULL, 3)

a. 0, NULL, 0
b. 2, NULL, 1
c. 0, NULL, 3
d. 2, NULL, 3

A

2, NULL, 3

44
Q

Which of the following statements about creating a table using the CREATE TABLE AS statement which a SELECT statement is not true?

a. The table must not exist
b. Each column name in the SELECT clause must be unique
c. You can include any of the clauses of the SELECT statement
d. The definitions of primary keys, foreign keys, and indexes are included in the new table

A

The definitions of primary keys, foreign keys, and indexes are included in the new table

45
Q

Which of the following statements would you use to change the invoice_total value in the Invoices table so it’s 1% less than the current invoice total for every vendor in the state of Arizona?

a.
UPDATE invoices
SET invoice_total = invoice_total - (invoice_total * .01)
WHERE vendor_id IN
   (SELECT vendor_id
    FROM vendors
    WHERE vendor_state = 'AZ')
b. 
UPDATE invoices
SET invoice_total = invoice_total * .01
WHERE vendor_id IN
   (SELECT vendor_id
    FROM vendors
    WHERE vendor_state = 'AZ')
c. 
UPDATE invoices
SET invoice_total = invoice_total - (invoice_total * .01)
WHERE vendor_id =
   (SELECT vendor_id
    FROM vendors
    WHERE vendor_state = 'AZ')
d. 
UPDATE invoices
SET invoice_total = invoice_total * .01
WHERE vendor_id =
   (SELECT vendor_id
    FROM vendors
    WHERE vendor_state = 'AZ')
A 
UPDATE invoices
SET invoice_total = invoice_total - (invoice_total * .01)
WHERE vendor_id IN
   (SELECT vendor_id
    FROM vendors
    WHERE vendor_state = 'AZ')
46
Q

Which of the following statements is true when you use MySQL Workbench to run INSERT, UPDATE, and DELETE statements?

a. By default, the changes are automatically committed to the database.
b. If autocommit mode is on, you can rollback the changes by clicking on the Rollback button.
c. If autocommit mode is on, you can commit the changes by clicking on the Commit button.
d. By default, the changes are rolled back when you exit MySQL Workbench unless you commit them.

A

By default, the changes are automatically committed to the database.

47
Q

Assuming that all of the table and column names are spelled correctly, what’s wrong with the INSERT statement that follows?
INSERT INTO invoices
(vendor_id, invoice_number, invoice_total, payment_total, credit_total,
terms_id, invoice_date, invoice_due_date)
VALUES
(97, ‘456789’, 8344.50, 0, 0, 1, ‘2012-08-31’)

A

The number of items in the column list doesn’t match the number in the VALUES list

48
Q

Which of the following statements best describes what this UPDATE statement does?
UPDATE invoices
SET credit_total = invoice_total - payment_total
WHERE vendor_id = 10
AND invoice_total - payment_total - credit_total > 0
a. Updates the credit_total column for all invoices with a balance due so they don’t have a balance due.
b. Updates the credit_total column for the first invoice with 10 in the vendor_id column and a balance so it doesn’t have a balance due.
c. Updates the credit_total column for all invoices with 10 in the vendor_id column and a balance due so they don’t have a balance due.
d. Updates the credit_total column for all invoices with a balance due so they don’t have a balance due.

A

Updates the credit_total column for all invoices with 10 in the vendor_id column and a balance due so they don’t have a balance due

49
Q

Which of the following statements best describes what this INSERT statement does?
INSERT INTO invoices_copy
SELECT *
FROM invoices
WHERE terms_id = 1
a. Adds one row from the Invoices table to the Invoices_Copy table.
b. Adds all of the rows in the Invoices table that have 1 in the terms_id column to the Invoices_Copy table.
c. Adds all of the rows in the Invoices table to the Invoices_Copy table and sets the terms_id column to 1 in each row.
d. Updates all of the rows in the Invoices_Copy table that have 1 in the terms_id column to the rows in the Invoices table.

A

Adds all of the rows in the Invoices table that have 1 in the terms_id column to the Invoices_Copy table.

50
Q

Code example 5-1

team_id INT NOT NULL AUTO_INCREMENT,
team_name VARCHAR(50),
team_rank INT NOT NULL DEFAULT 0

(Refer to code example 5-1.) What are the values of the team_id, team_name, and team_rank columns after the following statement is executed, assuming that the Teams table has one row in it with a team_id of 1?
INSERT INTO teams (team_name)
VALUES ('Angels')
a. 2, Angels, 0
b. 2, Angels, 1
c. 1, Angels, 0
d. 1, Angels, 1
A

2, Angels, 0

51
Q

Expressions coded in the HAVING clause

a. can use either aggregate search conditions or non-aggregate search conditions
b. Can use aggregate search conditions but can’t use non-aggregate search conditions
c. Can use non-aggregate search conditions but can’t use aggregate search conditions
d. can refer to any column in the base tables

A

can use either aggregate search conditions or non-aggregate search conditions

52
Q

Which of the statements below best describes the result set returned by this SELECT statement?
SELECT vendor_id,
SUM(invoice_total - payment_total - credit_total) AS column_2
FROM invoices
WHERE invoice_total - payment_total - credit_total > 0
GROUP BY vendor_id
ORDER BY vendor_id
a. The unpaid balance for each invoice
b. The total unpaid balance due for each vendor_id
c. The total amount invoiced by each vendor_id
d. The total of paid invoices for each vendor_id

A

The total unpaid balance due for each vendor_id

53
Q

When a SELECT statement includes a GROUP BY clause, the SELECT clause can include all but one of the following. Which one is it?

a. columns used for grouping
b. columns that are functionally dependent on a column used for grouping
c. columns that are not functionally dependent on a column used for grouping
d. aggregate functions

A

Columns that are not functionally dependent on a column used for grouping

54
Q

Which of the following statements is not true about a frame for an aggregate window function?

a. it defines a subset of the current partition
b. if you don’t specify the ending row for a frame, the ending row is the last row in the partition
c. it can be defined as the number of rows before and after the current row
d. it can be defined as a range of values based on the value of the current row

A

if you don’t specify the ending row for a frame, the ending row is the last row in the partition

55
Q

Code example 6-2

SELECT vendor_id, invoice_date, invoice_total,
SUM(invoice_total) OVER() AS total_invoices
SUM(invoice_total) OVER(PARTITION BY vendor_id
ORDER BY invoice_total) AS vendor_total
FROM invoices

(Refer to code example 6-2.) What rows make up the partitions for the second SUM function?

a. Each row is treated as a separate partition.
b. The rows for each invoice date are treated as a separate partition.
c. The rows for each vendor are treated as a separate partition.
d. All of the rows are treated as a single partition.

A

The rows for each vendor are treated as a separate partition.

56
Q

All but one of the following is true about the WITH ROLLUP operator. Which one is it?

a. It adds a summary row for each group specified in the GROUP BY clause.
b. It adds a summary row to the end of the result set.
c. It is part of standard SQL.
d. It is coded on the GROUP BY clause

A

It is part of standard SQL

57
Q

Which of the following is a common reason for using the IF function with the GROUPING function?

a. To replace the nulls that are generated by WITH ROLLUP with literal values.
b. To replace the nulls in grouped columns with literal values.
c. To add a summary row for each group only if the group has more than one row.
d. To add a summary row for the result set only if the result set has more than one row.

A

To replace the nulls that are generated by WITH ROLLUP with literal values.

58
Q

Expressions coded in the WHERE clause

a. can use either aggregate search conditions or non-aggregate search conditions
b. can use aggregate search conditions but can’t use non-aggregate search conditions
c. can use non-aggregate search conditions but can’t use aggregate search conditions
d. must refer to columns in the SELECT clause

A

can use non-aggregate search conditions but can’t use aggregate search conditions

59
Q

Which of the following is not true about calculating a moving average for an aggregate window function?

a. The frame must be defined using the ROWS clause.
b. The moving average always includes the current row.
c. The moving average for the first row in a partition can only include the value of the current row plus the values of any following rows.
d. To calculate the average, you must use both the SUM and AVG functions.

A

The frame must be defined using the ROWS clause.

60
Q

The six clauses of the SELECT statement must be coded in the following order:

a. SELECT, FROM, GROUP BY, HAVING, WHERE, ORDER BY
b. SELECT, FROM, WHERE, ORDER BY, GROUP BY, HAVING
c. SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY
d. SELECT, FROM, ORDER BY, WHERE, GROUP BY, HAVING

A

SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY

MySQL (1 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions