Logo

Go on. Explore!

lvl 0 • 0 xp

lvl 1

HomeMarketDecksInterests
SQL programming Chap. 5-8

SQL programming Chap. 5-8

memorize.aimemorize.ai (lvl 286)
Star 0%
Star 0%
Star 0%
Star 0%
Star 0%
(0)
(0)
(0)
47 cards
()
Section 1

Preview this deck

If introduced as follows, the subquery can return which of the values listed below? WHERE (subquery)

Front

1 / 47

SQL
Star 0%
Star 0%
Star 0%
Star 0%
Star 0%

0.0

0 reviews

5
0
4
0
3
0
2
0
1
0

Active users

0

All-time users

0

Favorites

0

Last updated

5 years ago

Date created

Mar 1, 2020

Cards (47)

Section 1

(47 cards)

If introduced as follows, the subquery can return which of the values listed below? WHERE (subquery)

Front

a subquery can't be introduced in this way

Back

To insert rows selected from one or more tables into another table with an INSERT statement, you code a/an ___________________________ in place of the VALUES clause.

Front

subquery select

Back

Inline View

Front

Subquery that's coded in the FROM clause returns a result set that can be referred to as this

Back

Expressions coded in the HAVING clause

Front

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

Back

All of the aggregate functions ignore null values, except for the ______________________________ function.

Front

COUNT(*)

Back

The DATETIME data type includes not only the date, but also a ________________________.

Front

time

Back

Which of the statements below best describes the result set returned by this SELECT statement? SELECT vendor_state, COUNT(*) AS column_2 FROM vendors GROUP BY vendor_state HAVING COUNT(*) > 1

Front

The number of vendors in each state that has more than one vendor

Back

Scalar Function

Front

Operates on a single value and returns a single value

Back

When MySQL automatically converts one data type to another, it's known as a/an ______________________ conversion.

Front

implicit

Back

Expressions coded in the WHERE clause

Front

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

Back

Implicit conversion

Front

automatic conversion

Back

If introduced as follows, the subquery can return which of the values listed below? WHERE vendor_id NOT IN (subquery)

Front

a column of one or more rows

Back

The _____________________ function is an ANSI-standard function that you can use to perform an explicit conversion.

Front

CAST

Back

Four ways to introduce a subquery

Front

In a Where clause as a search condition In a HAVING clause as a search condition In the FROM clause as a table specification In the SELECT clause as a column specification

Back

If introduced as follows, the subquery can return which of the values listed below? WHERE invoice_total > ALL (subquery)

Front

a column of one or more rows

Back

The DATE data type can store

Front

dates only

Back

You can use the AS keyword with the CREATE TABLE statement to create a copy of a table from a ____________________ statement.

Front

SELECT

Back

If introduced as follows, the subquery can return which of the values listed below? WHERE 2 < (subquery)

Front

a single value

Back

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

Front

The total unpaid balance due for each vendor_id

Back

Code example SELECT vendor_name, COUNT(*) AS number_of_invoices, MAX(invoice_total - payment_total - credit_total) AS balance_due FROM vendors v JOIN invoices i ON v.vendor_id = i.vendor_id WHERE invoice_total - payment_total - credit_total > (SELECT AVG(invoice_total - payment_total - credit_total) FROM invoices) GROUP BY vendor_name ORDER BY balance_due DESC (Please refer to code example 7-1.) When this query is executed, the result set will contain

Front

one row for each vendor that shows the largest balance due for any of the vendor's invoices, but only if that balance due is larger than the average balance due for all invoices

Back

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

Front

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

Back

If introduced as follows, the subquery can return which of the values listed below? FROM (subquery)

Front

a table

Back

When you code an UPDATE statement for one or more rows, the SET clause names the columns to be updated and the values to be assigned to those columns, and the ______________________ clause specifies the conditions a row must meet to be updated.

Front

WHERE

Back

Comment

Front

double dash "--"

Back

The ENUM data type stores values that are mutually exclusive. Which choice is not appropriate for the ENUM type?

Front

mushrooms, sausage, peppers

Back

By default, MySQL automatically commits changes to the database immediately after each INSERT, UPDATE, and ______________ statement that you issue.

Front

DELETE

Back

pseudocode

Front

not real code but like a rough draft

Back

subquery

Front

A SELECT statement that's coded within another SQL statement.

Back

summary query

Front

A query that contains one or more aggregate functions

Back

Nested Subquery

Front

Subquery in a Subquery

Back

The default date format for MySQL is _________________.

Front

yyyy-mm-dd

Back

If introduced as follows, the subquery can return which of the values listed below? SELECT (subquery)

Front

a single value

Back

All of the following values can be stored in a column that's defined as DECIMAL(6,2), except

Front

32492.05

Back

Correlated subquery

Front

A subquery that is executed once for each row in the main query. In contrase, an uncorrelated subquery is executed only once.

Back

When you code a column list in an INSERT statement, you can omit columns with default values and columns that allow ____________________________ values.

Front

null

Back

Which uses the least amount of storage?

Front

they all use the same amount of storage (Just know that 'VARCHAR' saves the amount of characters plus one. The others save a fixed amount.)

Back

Numbers that don't include a decimal point are known as ____________________.

Front

integers

Back

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

Front

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

Back

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

Front

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

Back

Column Functions

Front

Aggregate functions typically operate on the values in columns, they are sometimes referred to as this

Back

Code example SELECT vendor_name, COUNT(*) AS number_of_invoices, MAX(invoice_total - payment_total - credit_total) AS balance_due FROM vendors v JOIN invoices i ON v.vendor_id = i.vendor_id WHERE invoice_total - payment_total - credit_total > (SELECT AVG(invoice_total - payment_total - credit_total) FROM invoices) GROUP BY vendor_name ORDER BY balance_due DESC (Please refer to code example 7-1.) When this query is executed, the number_of_invoices for each row will show the number

Front

of invoices for each vendor that have a larger balance due than the average balance due for all invoices

Back

Code example SELECT vendor_name, COUNT(*) AS number_of_invoices, MAX(invoice_total - payment_total - credit_total) AS balance_due FROM vendors v JOIN invoices i ON v.vendor_id = i.vendor_id WHERE invoice_total - payment_total - credit_total > (SELECT AVG(invoice_total - payment_total - credit_total) FROM invoices) GROUP BY vendor_name ORDER BY balance_due DESC (Please refer to code example 7-1.) When this query is executed, the rows will be sorted by

Front

balance_due in descending sequence

Back

Code example SELECT i.vendor_id, MAX(i.invoice_total) AS largest_invoice FROM invoices i JOIN (SELECT vendor_id, AVG(invoice_total) AS average_invoice FROM invoices GROUP BY vendor_id HAVING AVG(invoice_total) > 100 ORDER BY average_invoice DESC) ia ON i.vendor_id = ia.vendor_id GROUP BY i.vendor_id ORDER BY largest_invoice DESC (Please refer to code example 7-2.) When this query is executed, the result table will contain one row for

Front

each vendor in the table named ia

Back

Uncorrelated subquery

Front

executed only once

Back

Aggregate Function

Front

Allow you to do jobs like calculate averages, summarize totals, or find the highest value for a given column, and you'll use them in summary queries

Back

explicit conversion

Front

if you want to control how a conversion is performed you use this

Back

Code example SELECT vendor_state, vendor_city, vendor_name, COUNT(*) AS invoice_qty, SUM(invoice_total) AS invoice_average FROM invoices JOIN vendors ON invoices.vendor_id = vendors.vendor_id WHERE vendor_state < 'e' GROUP BY vendor_state, vendor_city, vendor_name HAVING SUM(invoice_total) > 500 ORDER BY vendor_state, vendor_city, vendor_name (Please refer to code example) When this summary query is executed, the result set will contain one summary row for

Front

each vendor with invoice totals over $500

Back