advanced database traditional 1

In a relational database, data is stored in one or more ______ which consists of rows and columns. Tables The columns of a relational database table are referred to as . Fields The rows of a relational database table are referred to as __. Records The column uniquely identifies each row in the table. Primary key A ____ primary key consists of two or more columns. Composite A(n) ______ is automatically created for each key you define. Index Like a key, an index can include one or more ______. Columns When one row in a table is related to one or more rows in another table, this is called a one-to-many relationship Typical relationships exist between the primary key in one table and the key in another table. Foreign One-to-one relationships are frequently used. True Which type of relationship is the most common? One-to-many relationship If two tables have a one-to-one relationship, the data in the two tables could really be stored in a single table. True A many-to-many relationship is usually implemented by using a(n) table. Intermediate A many-to-many relationship can usually be broken down into two one-to-many relationships. True Study Figure 1-5. What type of relationship is shown here? One-to-many relationship To define a column in a table, you assign what are called . Properties The most critical column property is its . Data type When choosing a data type, try to assign the type that minimizes disk storage use in order to improve performance of the __ later. Queries Null represents a value that is . Not applicable, unknown, unavailable The NN column property represents . Not Null If you don't allow null values for a column, you . Must provide a value for that column A is a value assigned to a column when another value isn't provided. Default value An auto column is a numeric column whose value is generated automatically when a row is added to the table. Increment Two types of diagrams: ER diagram and an ____ entity-relationship (EER) diagram. Enhanced The EER diagram uses the __ icon to show a primary key column. Key The vendor_state column is assigned what data type? CHAR The __ table begins with two primary key columns. Invoice_line_items The OneSymbol.png symbol refers to the part of a one-to-many relationship. One The Crow’s Feet symbol refers to the part of a one-to-many relationship. Many SQL programmers typically work with statements. DML The SELECT clause names the _____ to be retrieved. Columns The FROM clause names the ______ where columns come from. Tables A calculated column only exists in the results of a query. In the SELECT statement, the WHERE clause gives criteria for which are selected. rows In a SELECT statement, the clause returns sorted rows. ORDER BY The SELECT statement returns what is called a result set, also called the result table. This result is created within the database. temporarily The SELECT statement is commonly referred to as a ____. query When you retrieve data from two tables, this type of operation is called a ________ because the data from two tables results in a single result set. join This is the most common type of join where the rows from the tables are only included in the result set if their related columns match. INNER Use the ______ clause on the SELECT statement to retrieve data from two tables. FROM An INSERT statement adds a to a table, as demonstrated in Figure 1-14. row What statement is used to change the data in one or more rows of a table? UPDATE Use the statement to delete rows from a table. DELETE Because inserting, updating, and deleting causes permanent changes to a database, the textbook recommends that you do not perform any of these commands until you understand the effect that they can have on the database. True SQL is a freeform language. That means that you can include without affecting the way the database interprets the code. spaces indentation line breaks SQL case-sensitive. is not Coding recommendations include: Capitalize keywords, Start each clause on a new line, Separate words with underscores, Break long clauses into multiple lines and indent continued lines Use /* and */ to code a comment. block Use -- to code a comment. single-line Before you can work with a database, you need to connect to the database . server If a password appears when making a connection, an option can be checked to save the password in a . vault To clear the password from the vault, the connection, select Edit Connection, and click Clear. right-click To connect to a MySQL server on another computer, the connection and select Edit Connections. right-click To add a new connection, click the icon to the right of MySQL Connections. plus The database server is also referred to as the database _______. engine After you connect to a database server, use the category of the Navigator window to navigate objects. SCHEMAS Database objects include: tables, functions, stored procedures, views To work with a node or object, right-click to display a . context-sensitive menu Double-click the node for a database to select it and the objects it contains. view To navigate through objects, click the to the left of each node. arrows To view table data, the table name and select “Select Rows – Limit 1000.” right-click The table data is displayed in the . Result Grid To insert, edit, and delete rows, use the buttons at the of the Result Grid. top To apply changes, click Apply at the of the Result Grid. bottom Use the button to cancel changes. Revert Column information includes . the column’s data type, the column name Use the tab to add, delete, or modify a column. Columns Add a new column by . entering it at the bottom Delete a column by and selecting Delete. right-clicking on the column Change the name of a column by and editing it. selecting the column Later you’ll learn how to use to edit column definitions. scripts To view column definitions, right-click the table name and select . Alter A SQL tab automatically opens when you connect to a server. Editor Keywords in the editor are displayed in . blue New SQL tab button: B SQL editor area: D Execute current SQL statement: C Result grid area: E Window to select a database: A If an error occurs, Workbench displays: a brief description, the error number Errors can be caused by: Misspelling a keyword, Selecting the wrong database, Misspelling a table/column name, Omitting a closing quote Error messages appear in the tab. Output In MySQL, ____ is a file containing SQL statements. script Script files use the extension. .sql If file extensions aren’t showing, open to change settings. File Explorer To switch between open scripts, . select the appropriate tab The Open SQL Script File button is in the part of Workbench. upper left A SQL script usually contains . multiple SQL statements End each statement with a . ; (semicolon) To execute one statement, . move the insertion point into it and click Execute Current Statement To execute all statements, press Ctrl+Shift+Enter or click . Execute SQL Script You can also execute selected statements only. True It’s important to be aware of the MySQL Command Line Client. True The MySQL Command Line Client prompt looks like: mysql> To stop the Command Line Client, enter exit or quit followed by a . ; (semicolon) Move on to pages 60 and 61. If you enter the show databases statement into the MySQL Command Line and this appears on the screen: mysql> show databases -> This means: you forgot to type a semicolon at the end of the show databases statement To list the names of the databases stored on a server, use the statement. show databases After displaying the database names, you can select a database to use. What full SQL statement would you type to select the database named ex? use ex; SQL statements __ case sensitive. are not

. A scroll bar indicates columns than displayed. more Without ORDER BY, rows appear in . primary key sequence The symbol means retrieve . all columns If an element can be coded multiple times, it's followed by a . comma Using returns columns in table order. True Retrieving more data affects performance. True SELECT CONCAT(first_name, ' ', last_name) AS full_name: function SELECT vendor_name, vendor_city, vendor_state: specific columns SELECT : all columns SELECT invoice_total - payment_total - credit_total AS balance_due: calculated column Best option to list last two columns: SELECT emp_first_name, emp_last_name In Figure 3-5, the alias used is . AS "Invoice Number" appears in quotes because . it contains spaces Use the keyword to assign a column alias. AS A different name in the result set is called a column . alias AS is optional but recommended. True Literal values are . literal In first - ( second + third ) fourth, second operation is multiplication In first + second - third, first operation is addition In first - second + third fourth, first operation is multiplication In first - ( second + third ) fourth, first operation is addition CONCAT joins . strings Functions use around arguments. parentheses Multiple arguments are separated by a . comma CONCAT(vendor_state, vendor_city) returns WIMadison CONCAT(vendor_city, vendor_state) returns MadisonWI CONCAT(vendor_city, ', ', vendor_state, ' ', vendor_zip_code) returns Madison, WI , 53707 The literal ' ' causes a __. comma and a space CONCAT(vendor_name, '''s Address: ') returns US Postal Service''s Address: Study the third SELECT statement in Figure 3-6 that includes apostrophes in literal values. What does CONCAT(vendor_name, '''s Address: ') return? US Postal Service''s Address: The default MySQL date format is __. yyyy-mm-dd The ROUND function can have either one or two arguments. True ROUND works with: numbers LEFT works with: strings DATE_FORMAT works with: dates To remove duplicate rows, use the ______ keyword. DISTINCT The ALL keyword is usually omitted. True Code ALL or DISTINCT immediately ______ SELECT. after Display only the rows you need using WHERE. True If a comparison is true, the row __ included. is Literal dates must be in YYYY-MM-DD and enclosed in quotes. WHERE vendor_state = 'IA' WHERE invoice_total - payment_total - credit_total > 0 WHERE vendor_name < 'F' WHERE invoice_date < '2024-07-01' WHERE invoice_date >= '2024-07-01' Not-equal operators: != and <> MySQL implicitly converting data types can yield unexpected results. False AND, OR, NOT are operators. logical Combining conditions creates a ____ condition. compound Use __ to negate a condition. NOT The logical operator to avoid for readability: NOT In (A OR B) AND C, which is first? OR Precedence order: Parentheses first, NOT second, AND third, OR fourth In A OR (B AND C), which is first? AND WHERE terms_id IN (1,3,4) — does it show 2? No Does it show 4? Yes IN (1,3,4) is the same as =1 OR =3 OR =4. True WHERE vendor_state NOT IN ('CA','NV','OR') — will it show CA? No Will it show NE? Yes IN uses parentheses with comma separated expressions. Order of expressions doesn’t affect result order. BETWEEN returns rows that fall in the range. is BETWEEN '2022 06 01' AND '2022 06 30' includes both dates. True Calculations be used with BETWEEN. can BETWEEN must list lower limit first. NOT be used with BETWEEN. can LIKE and REGEXP match a string pattern called a ___. mask LIKE uses a ________ symbol. wildcard WHERE vendor_city LIKE 'SAN%' matches: Santa Ana, San Diego % wildcard matches: any string of zero or more characters wildcard matches: any single character WHERE vendorstate LIKE 'N' matches: NE, NJ NOT be used with LIKE. can LIKE can degrade performance; use only when necessary. Null means . value not known Testing for nulls is important. True Correct NOT NULL syntax: WHERE invoice_total IS NOT NULL Sorting by multiple columns is called a ________ sort. nested ASC is required to sort ascending. False DESC is required to sort descending. DESC The contents of a table can include letters, numbers, and special characters. In ascending order: special characters appear first, numbers appear second, letters appear third Null values when sorting: In ascending order, null values appear first; in descending order, null values appear first Before you can sort by a column, you must list the column name in the SELECT clause. False Using column numbers in ORDER BY is harder to read; avoid this technique. True Column alias used in Figure 3 17 sort: CONCAT Appropriate times to use LIMIT: when using ascending sort and you want to display all rows; when using descending sort and you want to display all rows LIMIT is typically used with ORDER BY to show largest or smallest groups A _____ lets you combine columns from two or more tables. join The most common type of join is an inner join Creating an inner join: include an ON phrase, code both table names in FROM, separate table names with JOIN Join condition compares primary key of first table to foreign key of second table Column names must be qualified because the join column has the same name in both tables SELECT invoice_number, vendor_name FROM vendors INNER JOIN invoices ON vendors.vendor_id = invoices.vendor_id ORDER BY invoice_number; Optional and seldom used: INNER Primary key column in first table: vendor_id First table in join: vendors.vendor_id Fields to display: invoice_number, vendor_name Foreign key column in second table: invoices.vendor_id Used to retrieve rows with matching columns: = Second table in join: invoices For an inner join, only rows satisfying the join condition are included. True Table aliases make qualifying easier. True Typical table alias size: a letter or two If you assign a table alias, you cannot use the original table name. False v is alias for vendors; i is alias for invoices In ON v.vendor_id = i.vendor_id, first refers to v table, second refers to i table All tables are organized into three databases called schemas Vendors and Invoices are in AP; orders in OM; examples in EX To use a table in another database, prefix with the database name Separate database and table name with a period Column alias for vendor_state: state Column alias for vendor_city: city Table alias for vendors: v customers table is in the om database Table alias for customers: c JOIN condition matches vendor and customer zip codes A JOIN condition can include AND and OR operators In Figure 4 4: displays customer_first_name and customer_last_name Table alias for customers: c Table alias for employees: e ON compares customer_first_name in customers to first_name in employees; also compares last names using JOIN A __ joins a table with itself. self join Self joins are rarely used. True Self joins use the same table twice, so aliases are required v1 represents vendors table; v2 also represents vendors table Three table join: i = invoices; li = invoice_line_items; gl = general_ledger_accounts Best practice: use explicit inner join syntax Older syntax is implicit and deprecated Most common join: inner join Unlike an inner join, an outer join returns all rows from one table regardless of join condition OUTER keyword is optional and typically omitted Left outer join returns all rows from first (left) table; right outer join returns all rows from second (right) table Avoid right joins; substitute left joins by reversing table order in FROM and using LEFT instead of RIGHT In this example, d.department_number is displayed from the departments table. Like a join, a union combines data from two or more tables; instead of combining columns, a union combines rows. the base table The clause filters rows. WHERE If Boolean expressions result in , the row is included. true The WHERE clause consists of Boolean expressions. Default, NULL, and auto increment rules: NULL inserts null; DEFAULT inserts default; DEFAULT triggers auto increment; column list may omit these columns. Columns you may omit: NULL, DEFAULT. Subquery in INSERT: do not use parentheses around SELECT. Subquery in WHERE (UPDATE/INSERT): do use parentheses. Test subqueries with SELECT first. True. INSERT adds rows; UPDATE modifies rows. UPDATE clause = table; SET clause = columns/values; WHERE clause = condition. Safe update mode requires WHERE to reference a primary or foreign key. DEFAULT and NULL cannot be used in UPDATE. True. Test UPDATE with SELECT first. True. Turn off safe update mode in Preferences → SQL Editor → Safe Updates. True. WARNING: Omitting WHERE in UPDATE updates all rows. Subquery in WHERE identifies rows to update. To run the second UPDATE in Figure 5 6, safe update mode must be off. DELETE removes rows. WHERE identifies rows to delete. Test DELETE with SELECT first. MySQL returns an error if deleting a vendor with related invoices. To delete such a row: delete all child rows first. WARNING: Omitting WHERE in DELETE deletes all rows. Chapter 3 introduced scalar functions. Chapter 5 introduces aggregate functions, also called column functions, used in a summary query. An expression is typically just a column name. The default keyword for aggregate functions is ALL. DISTINCT is most often used with the COUNT function. Null values (except in COUNT()) are treated as not included in the calculation. MIN returns lowest non null; SUM returns total; MAX returns highest; AVG returns average; COUNT returns number of non null rows; COUNT() returns number of all rows. MIN, MAX, COUNT can work with numeric and date types. To omit duplicates, use DISTINCT. Aggregate functions summarize data in each group. The HAVING clause determines which groups appear. The GROUP BY clause determines how rows are grouped. GROUP BY and HAVING are coded after WHERE and before ORDER BY. WHERE is applied before grouping; ORDER BY is applied after grouping. GROUP BY list items are separated by a comma. A column is functionally dependent if the grouping column is a primary key or is unambiguous and not ambiguous. Correct SELECT order: SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT. First summary query: aggregate function = ROUND(AVG); grouped by vendor_id; HAVING clause filters groups where AVG(invoice_total) > 2000; number of vendors = 8; sorted in descending order. HAVING clause uses the COUNT function to require two or more invoices. GROUP BY syntax change: older version allowed ASC/DESC; newer version does not; newer MySQL sorts grouped rows ascending by default. HAVING ≠ WHERE. HAVING is applied after grouping; WHERE is applied before grouping. WHERE can reference any base column; HAVING can only reference SELECT columns; WHERE cannot contain aggregates; HAVING can. Compound conditions can be used in HAVING. True. WITH ROLLUP is coded on the GROUP BY clause. A subquery is a SELECT statement coded inside another statement. Subqueries can be nested. Subqueries can appear in WHERE as a search condition, in HAVING as a search condition, in FROM as a table specification, and in SELECT as a column specification. A subquery must be enclosed in parentheses. A subquery can replace a single value, a list of values, or a list of values. Coding recommendation: use the technique that produces the most readable code. Joins advantage: SELECT clause can include columns from both tables; joins are intuitive when using a primary key to foreign key relationship. Subquery advantage: intuitive when using a primary to foreign key relationship. A join is more intuitive when a relationship exists between tables. Advantages of joins: can pass an aggregate value to the main query; intuitive with ad hoc relationships. Advantages of subqueries: use a subquery to pass an aggregate value; intuitive with an ad hoc relationship; complex queries can be easier with subqueries. Most subqueries can be restated as joins, and most joins can be restated as subqueries. IN operator subquery can only return a single column. NOT IN with a subquery can be restated using an outer join. A subquery in WHERE can be included in the final result set. Without ANY/SOME/ALL, a comparison operator subquery can only return a single value. Uncorrelated subquery executes once for the entire query. Correlated subquery executes once for each row.

Correlated subqueries behave like loops. True. EXISTS tests whether a subquery returns any rows. EXISTS subquery does not return a result set; it returns whether rows satisfy the condition; executes quickly. EXISTS is typically used with a correlated subquery. In Figure 7 8, the correlated subquery selects invoices with the same vendor_id as the current vendor in the outer query; SELECT clause columns don’t matter, so an asterisk is used. EXISTS tests if at least one row is returned. NOT EXISTS tests if no rows are returned. For EXISTS/NOT EXISTS, SELECT clause typically uses an asterisk. Most correlated subqueries are coded in the SELECT clause; subqueries in SELECT are difficult to read and you shouldn’t use them if another solution exists. A query with a subquery in SELECT can usually be restated as a join; joins are faster and easier to read; subqueries are seldom coded in the SELECT clause. Use UNION to connect two or more SELECT statements. Requirements for a union: compatible data types AND same number of columns. To sort a union, use column names from the first SELECT statement. To keep duplicates in a union, use ALL. The second union example combines result sets after they have been joined. A full outer join returns unmatched rows from both tables. MySQL has no full outer join keyword; simulate it with a left join UNION a right join. Before experimenting with INSERT, UPDATE, DELETE, making a copy of tables is a good idea: False. CREATE TABLE AS with SELECT copies column definitions. A copied table does not always behave exactly like the original. Full copy example: CREATE TABLE invoices_copy AS SELECT * FROM invoices; You can create partial copies using WHERE. True. You can create summary copies. True. CREATE TABLE AS does not overwrite existing tables. False. INSERT with VALUES adds one or more rows. Values must match column data types: strings in quotes, dates in quotes. To insert multiple rows, separate value lists with a comma. Column list and INTO are optional. If you omit the column list, you must supply values in table order and must include all columns. False. If you include a column list, values must match that order. True. The syntax of the SELECT statement uses conventions similar to other programming languages. True In the SELECT statement's basic syntax, words are keywords that you have to type exactly as shown. uppercase In the SELECT statement's basic syntax, you need to provide replacements for the words. lowercase In the expanded syntax of the SELECT clause, [ALL|DISTINCT] means it . can be omitted Clauses enclosed in can be omitted. brackets [ ] The | symbol is called a and means choose between items. pipe Most SELECT statements contain which four clauses? FROM, WHERE, ORDER BY, LIMIT The SELECT clause specifies . the columns you want to retrieve DEFAULT means default element; KEYWORD means capitalized; ... means more elements can be added; element_name means replace with actual element; {A|B|C} means choose one; [A] means optional Correct SELECT clause order: SELECT, FROM, WHERE, ORDER BY, LIMIT The simplified syntax includes: SELECT, FROM, WHERE, ORDER BY, LIMIT Which clause is required and always first? SELECT The FROM clause specifies __.