My SQL Final Exam ch2-11
can use non-aggregate search conditions but can't use aggregate search conditions
(6) Expressions coded in the WHERE clause can use.....
SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY
(6) The six clauses of the SELECT statement must be coded in the following order:
with rollup
(6) When coding a query, you can add one or more summary rows to a result set that uses grouping and aggregates by coding the ______________ operator
COUNT(*)
(6) Which function will tell you how many rows were selected by the query (including null values)
AVG
(6) the average of non null values
MAX
(6) the highest non null values
MIN
(6) the lowest non null values
SUM
(6) the sum of non null values
COUNT
(6) this function is the number of non null values in the expression
SELECT COUNT(vendor_name) FROM vendors
(6)Write an aggregate expression for the number of entries in the vendor_name column.
subquery
(7) A _______ is a SELECT statement that's coded within another SQL statement.
subquery
(7) A _________ can return a list of values, a table of values or a single value.
NOT IN
(7) A query that uses the __________ operator with a subquery can typically be restated using an outer join.
WHERE, FROM, SELECT or HAVING clause.
(7) A subquery can be coded in what clauses? _______________
introduced
(7) A subquery can be coded, or ________ , anywhere a single value, a list of values, or a table is allowed.
a table of values or a single value
(7) A subquery can return a list of values, ________.
ad hoc
(7) A subquery tends to be more intuitive when it uses an _______ relationship between two tables.
Uncorrelated subquery
(7) A subquery that is executed only once.
always false
(7) If all of the rows returned by the subquery contain a null value, a comparison that uses the ALL keyword is ______ ______.
c. a table
(7) If introduced as follows, the subquery can return which of the values listed below? FROM (subquery) a. a single value b. a column of one or more rows c. a table d. a subquery can't be introduced in this way
d. a subquery can't be introduced in this way
(7) If introduced as follows, the subquery can return which of the values listed below? WHERE (subquery) a. a single value b. a column of one or more rows c. a table d. a subquery can't be introduced in this way
a. a single value
(7) If introduced as follows, the subquery can return which of the values listed below? WHERE 2 < (subquery) a. single value b. list of values c. table
b. a column of one or more rows
(7) If introduced as follows, the subquery can return which of the values listed below? WHERE invoice_total > ALL (subquery) a. a single value b. a column of one or more rows c. a table d. a subquery can't be introduced in this way
b. a column of one or more rows
(7) If introduced as follows, the subquery can return which of the values listed below? WHERE vendor_id NOT IN (subquery) a. a single value b. a column of one or more rows c. a table d. a subquery can't be introduced in this way
always true
(7) If no rows are returned by the subquery, a comparison that uses the ALL keyword is_____ _____.
ANY, SOME, ALL
(7) If you code a search condition without the ______, _______, and ________ keywords, the subquery must return a single value.
inline view
(7) When you code a subquery in a FROM clause, it returns a result set that can be referred to as an _____________.
table alias
(7) When you code a subquery in a FROM clause, you must assign a/an _______ to it.
correlated subquery
(7) When you code a subquery in the SELECT clause, you typically use a _______ ________.
IN
(7) When you use the _____ operator, the subquery must return a single column of values.
SELECT, asterisk (*)
(7) When you use these operators with a subquery, it doesn't matter what columns you specify in the _______ clause. As a result, you typically just code an _______.
WHERE
(7) You can use a comparison operator in a ____________ clause to compare an expression with the results of a subquery.
aggregate
(7) You can use a subquery to pass an ___________ value to the main query.
not exists
(7) You can use the ____ ______ operator to test that no rows are returned by the subquery.
ANY
(7) You can use the _______ keyword to test that a condition is true for one or more of the values returned by a subquery.
exists
(7) You can use the ________ operator to test that one or more rows are returned by the subquery.
ANY
(7) You can use the _________ keyword in a WHERE clause to test whether a condition is true for one or more of the values returned by the subquery
join which is faster and easier to read than a subquery.
(7)A query that includes a subquery in its SELECT clause can typically be restated using a _______ instead of the subquery.
d. 32492.05
(8) All of the following values can be stored in a column that's defined as DECIMAL(6,2), except a. -246.29 b. 0 c. 2479.95 d. 32492.05
a. integers
(8) Numbers that don't include a decimal point are known as ____________________. a. integers b. real c. abstract d. fraction
real numbers
(8) Numbers that include a decimal point are knows as ____________________.
c. mushrooms, sausage, peppers
(8) The ENUM data type stores values that are mutually exclusive. Which choice is not appropriate for the ENUM type? a. small, medium, large b. yes, no, maybe c. mushrooms, sausage, peppers d. cash, credit, debit
CHAR
(8) The _________ data type is used for fixed-length strings, which use the same amount of storage for each value regardless of the actual length of the string.
precision
(8) The ____________ of a real number indicates the total number of digits that can be stored in the data type
BLOB (Binary Large Object)
(8) The ____________________ data types can store large, unstructured data such as text, images, sound, and video.
CAST (CONVERT can also be used)
(8) The _____________________ function is an ANSI-standard function that you can use to perform an explicit conversion.
c. yyyy-mm-dd
(8) The default date format for MySQL is _________________. a. mm/dd/yy b. mon-dd-yyyy c. yyyy-mm-dd d. yy/dd/mm
ONE
(8) The latin1 character set uses how many bytes per character?
a. implicit
(8) When MySQL automatically converts one data type to another, it's known as a/an ______________________ conversion. a. implicit b. complex c. transmutation d. switch
D. they all use the same amount of storage
(8) Which uses the least amount of storage? A. 'example' stored in a column of type CHAR(20) B. 'exam' stored in a column of type CHAR(20) C. 'ex' stored in a column of type CHAR(20) D. they all use the same amount of storage
c. 'ex' stored in a column of type VARCHAR2(20)
(8) Which uses the least amount of storage? a. 'example' stored in a column of type VARCHAR2(20) b. 'exam' stored in a column of type VARCHAR2(20) c. 'ex' stored in a column of type VARCHAR2(20) d. they all use the same amount of storage
SELECT invoice_date CONVERT(invoice_date, DATETIME) AS FROM invoices
(8) Write the code for converting the DATE data type in a column named invoice_date to the DATETIME data type
-45
(9) If expiration_date contains a value that's equivalent to November 2, 2011 and the SYSDATE function returns a value that's equivalent to December 17, 2011, what will the exp_days column contain when this code is executed? expiration_date - SYSDATE() AS exp_days
ewilliam
(9) If first_name contains Edward and last_name contains Williams, what will the solution column contain when this code is executed? LOWER(LEFT(first_name,1) + LEFT(last_name,7)) AS solution
17
(9) If you TRUNCATE the number 17.99, you get a value of:
Null
(9) The COALESCE function provides one way to substitute other values for __________________________ values.
CONCAT_WS
(9) The _________ function concatenates a string with the specified separator string added in between.
CEILING
(9) The __________ function returns the smallest integer that is greater than or equal to the number.
IF
(9) The ______________ function lets you test an expression and return one value if the expression is true and another value if the expression is false.
LTrim
(9) The ______________ function returns the string with any leading spaces removed.
LOCATE()
(9) Use the _______________ function to find a character in a string
c. DATE_FORMAT('2015-04-20', '%W, %M %D, %Y')
(9) Which code returns the date in the format Friday, April 20th, 2015? a. DATE_FORMAT('2015-04-20', '%e-%b-%y') b. DATE_FORMAT('2015-04-20', '%m/%d/%y') c. DATE_FORMAT('2015-04-20', '%W, %M %D, %Y')
D) use the DatePart function to extract just the date from each datetime value
(9) Which of the following is not a valid way to avoid search problems when you want to search for rows that have a specific date in a column that's defined with the datetime data type and which might include time values? A) convert each datetime value to a date value B) search for datetime values that fall between the day before and the day after the date you want C) use the Day, Month, and Year functions to do the search D) use the DatePart function to extract just the date from each datetime value
d. all of the above
(9) Which of the following is the way to sort a VARCHAR column that contains numbers in numeric sequence? a. In the ORDER BY clause use the CAST function to convert the column values to numbers b. add zeros to the column values using + 0 c. use the LPAD function to pad the numbers with leading zeros d. all answers
ROUND(payment_total, 1)
(9) Write the code for a SELECT statement that uses the ROUND function to return the payment_total column with 1 decimal digit.
DATE_ADD
(9) You use the _________________ to add a specified number of date parts to a date.
c. DELETE
-i-am-a-dummy flag makes the MySQL engine refuse UPDATE and which other command? a. WHERE b. INSERT c. DELETE d. TRUNCATE
d. Data is present in the other table
On executing DELETE command, if you get an error "foreign key constraint"- what does it imply? a. Foreign key not defined b. Table is empty c. Connectivity issue d. Data is present in the other table
a. 49
Select ascii('100'); will return? a. 49 b. 50 c. 51 d. 52
True
Select now(), sleep(20),now(); will return same time. True False
b. 10
Select round(14.189,-1); a. 20 b. 10 c. 14.5 d. 15
A - select h.subject_name, l.department_name, h.department_head, l.city from honours_subject h full outer join location l on(h.subject_code = l.subject_code);
Select the right query for retrieving records from the tables HONOURS_SUBJECT and LOCATIONS with a full outer join A - select h.subject_name, l.department_name, h.department_head, l.city from honours_subject h full outer join location l on(h.subject_code = l.subject_code); B - select h.subject_name, l.department_name, h.department_head, l.city from honours_subject h full outer join location l on(subject_code); C - select h.subject_name, l.department_name, h.department_head, l.city from honours_subject h full outer join location l where (h.subject_code = l.subject_code); D - None of the above.
True
T or F The linking table doesn't usually have a pk.
temporal
The date/time data types are often referred to as the date/time or ______________data types.
a. The TRUNCATE clause deletes all rows in a database table, while the DELETE clause can have a WHERE condition and might or might not delete all rows in a table.
The difference between the DELETE and TRUNCATE SQL clauses is: a. The TRUNCATE clause deletes all rows in a database table, while the DELETE clause can have a WHERE condition and might or might not delete all rows in a table. b. The DELETE clause deletes all rows in a database table, while the TRUNCATE clause can have a WHERE condition and might or might not delete all rows in a table. c. The TRUNCATE clause is identical to the DELETE clause
tables
(1) A relational database consists of one or more
Select
(1) A/An ______ statement is used to retrieve selected columns and rows from a base table.
a result set
(1) The result of a SELECT statement is a. a stored procedure b. a calculated value c. a result set
EER model
(10) A/An ______________model is a representation of the entities, or objects, of the database including the tables, views, and stored programs.
reduces redundancy and makes maintenance easier
(10) To apply the second normal form, you move columns that don't depend on the entire primary key to another table and establish a relationship between the two tables. Why?
d. a single, scalar value
(10) To be in the first normal form, each cell in a table must contain a. a non-unique value b. a non-redundant value c. a unique value d. a single, scalar value
a. each non-key column must depend only on the primary key
(10) To be in the third normal form a. each non-key column must depend only on the primary key b. each non-key column must depend on the entire primary key c. the non-key columns must not contain repeating values d. all of the above
d. All of the above
(10) To enforce referential integrity for a delete operation, a MySQL database can a. return an error instead of deleting any rows b. set the foreign key values in the related rows to null c. delete the related rows in the foreign key tabled. d. All of the above
normal forms
(10) To normalize a data structure, you apply the ______________________ in sequence
third
(10) Typically, most database designers consider a database structure normalized if it's in the ________________________ normal form.
d. The column is frequently updated.
(10) Which of the following is not a good guideline for deciding when to create an index for a column? a. The column is frequently used in search conditions or joins. b. The column is a foreign key. c. The column contains a large number of unique values. d. The column is frequently updated.
INDEX
(10) You should create an _______________ when a column is used frequently in search conditions or joins.
enhanced entity relationship
(11) A/An ______________ diagram can be used to show how the tables in a database are defined and related.
case-insensitive
(11) In the collation, utf8_spanish_ci, the ci stands for:
InnoDB
(11) The default storage engine for MySQL 5.5 and later. This engine supports foreign keys and transactions.
network
(11) The three main hardware components of a client/server system are the clients, the server, and the
drop
(11) To delete a database, table, or index, use keyword _______
TRUNCATE TABLE
(11) To delete all data from a table without deleting the definition for the table, you use the ______________ statement.
you need to create the referred to tables before you create the tables that refer to them
(11) When you create a script for creating a database you must first create what?
the default value is used if another value isn't specified when a row is added to the database.
(11) When you define a column with the DEFAULT attribute:
foreign keys
(11) Which feature does the MyISAM storage engine not support?
c. All of the above.
(11) Which is true when you define a column as the primary key? a. An index is automatically created based on the column. b. The column is forced to be NOT NULL. c. All of the above. d. The column is forced to contain a unique value for each row.
d. All of the above
(11) Which of the following is true about creating indexes? a. MySQL automatically creates indexes for primary keys. b. MySQL automatically creates indexes for foreign keys. c. You can create an index that's based on more than one column. d. All of the above
d. function-based indexes
(11) You can't code which one of the following as part of a CREATE TABLE statement: a. not nullconstraints b. column-levelconstraints c. table-levelconstraints d. function-based indexes
-design independence (you can make changes to the view and others don't even need to know) -data security (restrict access to columns using the SELECT or WHERE clause) -simplified queries (views can hide the complexity of the retrieval operations) -updatability
(12) 4 benefits of views
d. statements
(2) A SQL script is a file that contains one or more ____________. a. folders b. databases c. tables d. statements e. connections
a. data access model communicate with the database
(2) A database driver is software that lets the a. data access model communicate with the database b. application program communicate with the database c. data access model communicate with the application program d. application program communicate with the data access model
navigator
(2) In MySQL Workbench, you can start or stop the MySQL database server by clicking the Start Server button in the __________ window
execute
(2) You can use the SQL Editor tab to enter and ______ one or more SQL statement
SELECT, FROM, WHERE, ORDER BY, LIMIT
(3) Five main clauses of the SELECT statement
c. WHERE
(3) If you want to filter the rows that are returned by a SELECT statement, you must include a/an ______________ clause. a. SELECT b. TABLE c. WHERE d. GOTO
expression
(3) Is a combination of one or more values, operators and SQL functions that evaluate to a value
FROM
(3) The ________ clause of the SELECT statement specifies the table that contains the data
c. Not, And, Or (NAO)
(3) The order of precedence for the logical operators in a WHERE clause is a. Or, And, Not b. And, Or, Not c. Not, And, Or d. Not, Or, And
b. CONCAT
(3) To concatenate character strings, you use the _________________ function in a string expression. a. WHERE b. CONCAT c. >> d. STRING
d. DISTINCT
(3) To prevent duplicate rows from being returned by a SELECT statement, you can code the _____________ keyword in the SELECT clause. a. DUPLICATE b. UNIQUE c. LAST d. DISTINCT
a. *
(3) To return all of the columns from the base table, you can code the _______________ operator in the SELECT clause. a. * b. TOTAL c. ALL d. ?
ORDER BY invoice_total DESC
(3) To sort the rows that are retrieved by a SELECT statement in descending sequence by invoice_total, you code this ORDER BY clause:
a. invoice_total IN (0, 1000)
(3) When coded in a WHERE clause, which of the following search conditions will not return a result set that includes all invoices with an invoice_total value of $1000 or less? a. invoice_total IN (0, 1000) b. invoice_total <= 1000 c. NOT (invoice_total > 1000) d. invoice_total BETWEEN 0 AND 1000
d. payment_date IS NOT NULL AND invoice_total >= 500
(3) 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. NOT (payment_date IS NULL AND invoice_total <= 500) b. payment_date IS NOT NULL OR invoice_total >= 500 c. payment_date IS NULL AND invoice_total > 500 d. payment_date IS NOT NULL AND invoice_total >= 500
d. column name, alias, expression, or column number
(3) When you code an ORDER BY clause, you can specify a a. column name or alias only b. column name, alias, or expression only c. column name or expression only d. column name, alias, expression, or column number
a. WHERE vendor_name < 'D'
(3) 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 < 'C' d. WHERE vendor_name = D
c. invoice_total - credit_total - payment_total / 10
(3) Which of the following expressions does not compute 10% of the balance due if balance due is the invoice total minus the credit total minus the payment total? a. (invoice_total - payment_total) - credit_total) / 10 b. (invoice_total - payment_total - credit_total) / 10 c. invoice_total - credit_total - payment_total / 10 d. (invoice_total - (payment_total + credit_total)) * 0.10
arithmetic expression
(3) a sequence of operands and operators that computes a value
a. unmatched rows from both the left and right tables
(4) A full outer join returns a. unmatched rows from both the left and right tables b. rows in the left table that don't satisfy the join condition c. rows in the right table that don't satisfy the join condition d. the Cartesian product of the two tables
a. alias
(4) A table _________________ can be used when you want to assign a temporary name to a table. a. alias b. widget c. module d. label
d. result sets
(4) A union combines the rows from two or more _______________________. a. SELECT statements b. result tables c. queries d. result sets
a. use that alias to refer to the table throughout your query
(4) 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 b. qualify all of the column names for that table c. assign them to all of the tables d. qualify every column name in the query
d. rows from the second table
(4) In a cross join, all of the rows in the first table are joined with all of the a. matched rows in the second table b. unmatched columns in the second table c. distinct rows in the second table d. rows from the second table
d. when the same column names exist in both tables
(4) In a join, column names need to be qualified only a. in inner joins b. when the code is confusing c. in outer joins d. when the same column names exist in both tables
foreign key
(4) In most cases, the join condition of an inner join compares the primary key of one table to the _____ of another table.
equal
(4) In most cases, the join condition of an inner join uses the _______________ operator to compare two keys.
b. combines the result sets of two or more SELECT statements
(4) Like a join, a union combines data from two or more tables. But, instead of combining columns from base tables, a union a. is coded in the ORDER BY clause b. combines the result sets of two or more SELECT statements c. by default, does not eliminate duplicate rows d. combines columns from the same table
inner join
(4) Refer to code example. This type of join is called a/an ___________. SELECT vendor_name, invoice_date FROM vendors v JOIN invoices i ON v.vendor_id = i.vendor_id;
d. The result sets must be derived from different tables.
(4) 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 corresponding columns in the result sets must have compatible data types. c. The result sets may or may not have any duplicate rows. d. The result sets must be derived from different tables.
c. the join must be based on a column or columns that have the same name in both tables
(4) When you use the USING keyword for a join, a. the join can't be an outer join b. the join can't be done on more than one column c. the join must be based on a column or columns that have the same name in both tables d. the join must be an inner join
a. you code a USING clause in addition to the ON clause
(4) Which is not true about the USING keyword? a. you code a USING clause in addition to the ON clause b. the join must be an equijoin, meaning the equals operator is used to compare the two columns c. it can be used with inner or outer joins d. you use it to simplify the syntax for joining tables
d. is an older syntax that works with legacy code
(4) Which of the following is not a reason for using the explicit syntax instead of the implicit syntax for joins? The explicit syntax a. lets you separate the join and search conditions b. lets you combine inner and outer joins c. is easier to read and understand d. is an older syntax that works with legacy code
a. select
(4) You can combine inner and outer joins within a single ________________ statement. a. select b. where c. using d. xor
b. a union
(4) You can simulate a full outer join by using a. a left outer join b. a union c. a self join d. the ON clause
The number of items in the column list doesn't match the number in the VALUES list.
(5) Assuming that all of the tables 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')
UPDATE vendor SET vendor_city='Los Angeles', vendor_zip_code='90025' WHERE vendor
(5) The vendor ASC Signs moved from Fresno to Los Angeles. Use an UPDATE statement to change the vendor_city to Los Angeles and vendor_zip_code to 90025 for this vendor.
subquery select
(5) 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.
c. null
(5) When you code a column list in an INSERT statement, you can omit columns with default values and columns that allow ____________________________ values. a. string b. open c. null d. quote
WHERE
(5) 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.
CREATE TABLE vendors_backup AS SELECT * FROM vendors;
(5) Write a SQL statement that creates a complete copy of the vendors table call vendors_backup.
INSERT INTO general_ledger (account_number, account_description) VALUES (82915, Main Street Books)
(5) Write an INSERT statement to insert a row in the general_ledger_accounts table using these values account_number: 82915 account_description: Main Street Books
DELETE FROM Invoices_Copy
(5) Write the code for a DELETE statement that deletes every row in the Invoices_Copy table.
SELECT (example: CREATE TABLE vendors_backup AS SELECT * FROM vendors;)
(5) You can use the AS keyword with the CREATE TABLE statement to create a copy of a table from a ____________________ statement.
Summary
(6) A SELECT statement that includes aggregate functions is often called a/an ____________ query.
COUNT(*)
(6) All of the aggregate functions ignore null values, except for the ______________________________ function. a. KEY b. HAVING c. ERR d. COUNT(*)
DISTINCT
(6) By default, all duplicate values are included in the aggregate calculation, unless you specify the ______________ keyword
either aggregate search conditions or non-aggregate search conditions
(6) Expressions coded in the HAVING clause can use....
1. SELECT list can't include a DISTINCT clause 2. SELECT list can't include aggregate functions 3. the SELECT statement can't include GROUP BY or HAVING 4. the view can't include the UNION operator
4 requirements for creating updatable views.
1. identify the elements 2. subdivide each element into its smallest useful components (ie full name should be broken into first name and last name) 3. identify tables (entities) and assign columns (elements) 4. identify pk and fk 5. review whether or not the data structure is normalized 6. identify indexes
6 basic steps for designing a data structure
view
A ______ is a SELECT statement that's stored in the database as a database object.
CTE (Common table expression)
A ________ is a SELECT statement that creates one or more named temporary result sets that can be used by the query that follows it.
updatable
A __________ view is a view that can be used in an INSERT, UPDATE, or DELETE statement to update the data in a base table.
frame
A ___________can be defined as a number of rows before and after the current row(s) or range of values based on the value of the current ROW range.
window
A ___________consists of all of the rows that are needed to calculate the aggregate value for the current row.
recursive
A _________query is a query that can loop through a result set and perform processing to return a final result set.
b. Collection of related tables
A database can be best described as a ? a. Collection of data b. Collection of related tables c. Collection of tables d. Collection of views
cumulative and moving averages
A frame is relative to the current row and can move within a partition as the current row changes. This makes it easier to calculate? (2 things)
c. Create database command
A new Database is created by ? a. Use database command b. Create table command c. Create database command d. Crete store command
corellated subquery
A subquery that executes once for each row in the main query.
False
A table can be created with out using a database? True False
3
A table has three rows and 5 columns the cardinality is?
5
A table has three rows and 5 columns the degree is?
base
A view does not store any date. It always refers back to its ________ tables.
c. Virtual
A view is nothing but a ________ table or a stored query a. Dymanic b. Real c. Virtual d. Static
Although similar in the sense that both statements will modify a table, these statements are quite different. The ALTER statement is used to modify columns. With ALTER, you can add columns, remove them, or even modify them. The UPDATE statement is used to modify rows. However, UPDATE can only update a row, and cannot remove or add rows.
ALTER vs UPDATE
False
An outer join requires each record in the two joined tables to have a matching record. True False
c. The number of items in the column list doesn't match the number in the VALUES list.
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. There are too few items in the VALUES list. b. The column names in the column list are in the wrong sequence. c. The number of items in the column list doesn't match the number in the VALUES list. d. There are too many items in the column list.
DELETE
By default, MySQL automatically commits changes to the database immediately after each INSERT, UPDATE, and ______________ statement that you issue.
Yes
Can DISTINCT command be used for more than one column?
Yes
Can I define multiple unique constraints on a table?
B - select honours_subject, avg(percentage_of_marks) from students having avg(percentage_of_marks) > 50.0 group by honours_subject
Consider the attached schema − Which of the following query will correctly list the average percentage of marks in each honours subject, when the average is more than 50 percent? A - select honours_subject, avg(percentage_of_marks) from students where avg(percentage_of_marks) > 50.0 group by honours_subject; B - select honours_subject, avg(percentage_of_marks) from students having avg(percentage_of_marks) > 50.0 group by honours_subject; C - select honours_subject, avg(percentage_of_marks) from students group by honours_subject having avg(percentage_of_marks) > 50.0; D - None of the above.
C - select first_name, last name, percentage_of_marks from students order by percentage_of_marks desc;
Consider the following schema − STUDENTS(student_code, first_name, last_name, email, phone_no, date_of_birth, honours_subject, percentage_of_marks); Which of the following query would display names of all students in descending order of percentage of marks? A - select first_name, last name, percentage_of_marks from students order by percentage_of_marks; B - select first_name, last name, percentage_of_marks order by percentage_of_marks desc from students; C - select first_name, last name, percentage_of_marks from students order by percentage_of_marks desc; D - select first_name, last name, percentage_of_marks from students order by percentage_of_marks descending;
used to enforce the integrity of the data by enforcing rules about the values that can be stored
Constraints are used for .......
c. Displays five columns for each invoice with the invoice total in one of the last three columns based on how many days have elapsed between the invoice date and the current date.
Describe what this SELECT statement does? SELECT invoice_number, invoice_date, CASE WHEN (SYSDATE() - invoice_date) >= 30 AND (SYSDATE() - invoice_date) < 60 THEN invoice_total ELSE 0 END AS "30-60", CASE WHEN (SYSDATE() - invoice_date) >= 60 AND (SYSDATE() - invoice_date) < 90 THEN invoice_total ELSE 0 END AS "60-90", CASE WHEN (SYSDATE() - invoice_date) > 90 THEN invoice_total ELSE 0 END AS "Over 90" FROM invoices a. Displays five columns for each invoice with a message in one of the last three columns based on how many days have elapsed between the invoice date and the current date. b. Displays three columns for each invoice with one of these messages in the third column: 30-60, 60-90, or Over 90. c. Displays five columns for each invoice with the invoice total in one of the last three columns based on how many days have elapsed between the invoice date and the current date. d. Displays three columns for each invoice with a value in the third column that indicates how many days have elapsed between the invoice date and the current date.
Primary Key
Each table in a database should have a _____________________________ that uniquely identifies each row.
True
Enum values are stored accodring to their index numbers True False
PRIMARY KEY constraint can be used to uniquely identify the row. UNIQUE columns have a different value for every row. NOT NULL columns must have a value. DEFAULT assigns a default value for the column when no value is specified.
Example of Constraints
B. SELECT city FROM weather WHERE humidity = 89;
Find all the cities whose humidity is 89 A. SELECT city WHERE humidity = 89; B. SELECT city FROM weather WHERE humidity = 89; C. SELECT humidity = 89 FROM weather; D. SELECT city FROM weather;
A. SELECT * FROM weather WHERE temperature > (SELECT temperature FROM weather WHERE city = 'Paris')
Find all the tuples having temperature greater than 'Paris'. A. SELECT * FROM weather WHERE temperature > (SELECT temperature FROM weather WHERE city = 'Paris') B. SELECT * FROM weather WHERE temperature > (SELECT * FROM weather WHERE city = 'Paris') C. SELECT * FROM weather WHERE temperature > (SELECT city FROM weather WHERE city = 'Paris') D. SELECT * FROM weather WHERE temperature > 'Paris' temperature
B. SELECT weather.city, temperature, humidity, country FROM weather, location;
Find the name of all cities with their temperature, humidity, and countries. A. SELECT city, temperature, humidity, country FROM location; B. SELECT weather.city, temperature, humidity, country FROM weather, location; C. SELECT weather.city, temperature, humidity, country FROM weather, location WHERE weather.city = location.city; D. SELECT weather.city, temperature, humidity FROM weather SELECT country FROM location WHERE weather.city = location.city;
B. SELECT country FROM location WHERE city IN (SELECT city FROM weather WHERE condition = sunny');
Find the names of the countries whose condition is sunny. A. SELECT country FROM location WHERE condition = 'sunny'; B. SELECT country FROM location WHERE city IN (SELECT city FROM weather WHERE condition = sunny'); C. SELECT country FROM location WHERE city NOT IN (SELECT city FROM weather WHERE condition = 'sunny'); D. SELECT country FROM location WHERE city UNION (SELECT city FROM weather WHERE condition = 'sunny');
A. SELECT city, temperature, condition FROM weather WHERE condition NOT IN ('sunny', 'cloudy');
Find the names of these cities with temperature and condition whose condition is neither sunny nor cloudy A. SELECT city, temperature, condition FROM weather WHERE condition NOT IN ('sunny', 'cloudy'); B. SELECT city, temperature, condition FROM weather WHERE condition NOT BETWEEN ('sunny', 'cloudy'); C. SELECT city, temperature, condition FROM weather WHERE condition IN ('sunny', 'cloudy'); D. SELECT city, temperature, condition FROM weather WHERE condition BETWEEN ('sunny', 'cloudy');
C. SELECT city, temperature FROM weather ORDER BY temperature;
Find the temperature in increasing order of all cities A. SELECT city FROM weather ORDER BY temperature; B. SELECT city, temperature FROM weather; C. SELECT city, temperature FROM weather ORDER BY temperature; D. SELECT city, temperature FROM weather ORDER BY city;
Data Definition Language
First letter D in DDL stands for?
d. SHOW DATABASES LIKE 'test%';
How do I find out all databases starting with 'test'? a. SHOW DATABASES LIKE '%test%'; b. SHOW DATABASES LIKE '%test; c. SHOW DATABASES LIKE ''test'%'; d. SHOW DATABASES LIKE 'test%';
use the Grouping function; it returns 1 if the expression is null because it's in a summary row. Otherwise, it returns 0.
How do you distinguish between an actual null value and a null due to a summary when using the WITH ROLLUP?
4 bytes per character example CHAR(10) = 10*4 bytes per character = 40 doesn't matter how many characters CHAR(10) 'CA' has only 2 characters but still uses the 40 bytes
How much storage does a CHAR data type use?
length of the string + 1 byte example VARCHAR(10) 'CA' = 2 characters + 1 = 3 bytes
How much storage does a VARCHAR data type use?
8
How much storage space does DATETIME require? 4 bytes 2 bytes 8 bytes 1 byte
A. NOT will be evaluated first; AND will be evaluated second; OR will be evaluated last.
If a query involves NOT, AND, OR with no parenthesis A. NOT will be evaluated first; AND will be evaluated second; OR will be evaluated last. B. NOT will be evaluated first; OR will be evaluated second; AND will be evaluated last. C. AND will be evaluated first; OR will be evaluated second; NOT will be evaluated last. D. The order of occurrence determines the order of evaluation.
a. True
If an ENUM column is declared NOT NULL, its default value is the first element of the list of allowed values. a. True b. False, ENUM column cannot be null c. False, its default value is the last element of the list of allowed values
the final summary row
If the GROUP BY clause specifies a single group, the WITH ROLLUP operator only adds __________________.
a summary row for each group in the group by clause and a summary row to the end of the result set_
If the GROUP BY clause specifies more than one group, the WITH ROLLUP operator adds _________________.
False
If the primary key constraint is dropped the column and the data is lost too. True False
ANY or Some
If the subquery doesn't return any values, or if it only returns null values, a comparison that uses the _______ keyword evaluates to false.
foreign key
If two tables have a one-to-many relationship, you need to add a _______________________ column to the table on the "many" side.
CREATE VIEW, SELECT
If you name the columns of a view in the __________clause, you have to name all of the columns. However, if you name the columns in a _________ clause, you can name just the columns you need to rename.
Manipulation
In DML M stands for?
d. create a database diagram
In a SQL Editor tab of MySQL Workbench, you can do all but one of the following. Which one is it? a. navigate through database objects b. code SQL statements c. run SQL scripts d. create a database diagram
a. when the same column names exist in both tables
In a join, column names need to be qualified only a. when the same column names exist in both tables b. in outer joins c. in inner joins d. when the code is confusing
if you don't use it a change you make in a view can cause the modified row to no longer be included in a view and if you specify a WITH CHECK OPTION when you create a view, an error will occur if you try to modify a row in such a way that it would no longer be included in a view ie. storing invalid data
List 2 things the WITH CHECK OPTION does?
a. the largest invoice total related to that row, but only if it's larger than the average for all invoices
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 When this query is executed, each row in the result table will show a. the largest invoice total related to that row, but only if it's larger than the average for all invoices b. the largest invoice total related to that row c. the average invoice total related to that row, but only if it's greater than 100 d. the average invoice total related to that row
c. each vendor in the table named ia
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 When this query is executed, the result table will contain a. one row for each vendor b. each invoice in the table named ia c. each vendor in the table named ia d. each invoice
c. for each vendor with an average invoice total that's greater than 100
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 When this query is executed, there will be one row a. for each vendor with a maximum invoice total that's greater than 100 b. for each invoice with an invoice total that's greater than the average invoice total for the vendor and also greater than 100 c. for each vendor with an average invoice total that's greater than 100 d. for each vendor
a. the number of rows in the Invoices table
SELECT vendor_name, invoice_number FROM invoices LEFT JOIN vendors ON invoices.vendor_id = vendors.vendor_id The total number of rows returned by this query must equal a. the number of rows in the Invoices table b. the number of rows in the Vendors table c. the number of rows in the Invoices table plus the number of rows in the Vendors table d. none of the above
b. the number of rows in the Vendors table
SELECT vendor_name, invoice_number FROM invoices LEFT JOIN vendors ON invoices.vendor_id = vendors.vendor_id If the LEFT keyword is replaced with the RIGHT keyword, the total number of rows that are returned must equal a. the number of rows in the Invoices table b. the number of rows in the Vendors table c. the number of rows in the Invoices table plus the number of rows in the Vendors table d. none of the above
d. 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
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_idWHERE 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 When this query is executed, the result set will contain a. one row for each invoice that has a larger balance due than the average balance due for all invoices b. one row for the invoice with the largest balance due for each vendor c. one row for each invoice for each vendor that has a larger balance due than the average balance due for all invoices d. 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
c. balance_due in descending sequence
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 When this query is executed, the rows will be sorted by a. vendor_id and then by balance_due in descending sequence b. invoice_id c. balance_due in descending sequence d. vendor_id
d. The number of vendors in each state that has more than one vendor
SELECT vendor_state, COUNT(*) AS column_2 FROM vendors GROUP BY vendor_state HAVING COUNT(*) > 1 a. The names of the vendors in each state b. The duplicate vendors from each state c. The number of vendors in each state d. The number of vendors in each state that has more than one vendor
d. each vendor with invoice totals over $500
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 When this summary query is executed, the result set will contain one summary row for a. each vendor with invoice average over $500 b. each city with invoice average over $500 c. each city with invoice totals over $500 d. each vendor with invoice totals over $500
b. The column name for the fifth column in the result set doesn't match the data. (it shows the sum of invoice_total, but the column is named invoice_average.)
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 Although this query runs as coded, it contains this logical error: a. The three columns in the ORDER BY clause should use the DESC keyword so the city totals will be in the right sequence. b. The column name for the fifth column in the result set doesn't match the data. c. The condition in the WHERE clause should be coded in the HAVING clause. d. The condition in the HAVING clause should be coded in the WHERE clause.
C. Acts like a WHERE clause but is used for groups rather than rows.
The HAVING clause does which of the following? A. Acts EXACTLY like a WHERE clause. B. Acts like a WHERE clause but is used for columns rather than groups. C. Acts like a WHERE clause but is used for groups rather than rows. D. Acts like a WHERE clause but is used for rows rather than columns.
D. DELETE FROM CUSTOMER WHERE ...
The command to remove rows from a table 'CUSTOMER' is: A. DROP FROM CUSTOMER ... B. UPDATE FROM CUSTOMER ... C. REMOVE FROM CUSTOMER ... D. DELETE FROM CUSTOMER WHERE ...
B. change the table structure.
The SQL ALTER statement can be used to: A. change the table data. B. change the table structure. C. delete rows from the table. D. add rows to the table.
A. limits the row data are returned.
The SQL WHERE clause: A. limits the row data are returned. B. limits the column data that are returned. C. Both A and B are correct. D. Neither A nor B are correct.
B. LIKE only
The SQL keyword(s) ________ is used with wildcards. A. NOT IN only B. LIKE only C. IN only D. IN and NOT IN
scale
The _____ indicates the number of digits that can be stored to the right of the decimal point.
UNSIGNED
The _______ attribute prevents negative values from being stored in a column. If a negative value is entered, it will be stored and display as "None" and an error occurs.
SOME
The ________ keyword works the same as the ANY keyword.
b. DEC c. NUMERIC d. FIXED
The following are synonyms for the DECIMAL data type? a. REAL b. DEC c. NUMERIC d. FIXED
a. REAL d. DOUBLE PRECISION
The following are synonyms for the DOUBLE data type? a. REAL b. DEC c. NUMERIC d. DOUBLE PRECISION
C) data access API
The interface between an application program and the DBMS is usually provided by the A) back end B) front end C) data access API D) programmer
b. back-end processing; back end
The processing that's done by the DBMS is typically referred to as ________, and the database server is referred to as the _____________ a) front-end processing b) back-end processing c) the file server a database d) management system
C. table
The result of a SQL SELECT statement is a ________ . A. file B. report C. table D. form
C. An exact match is not possible in a SELECT statement.
The wildcard in a WHERE clause is useful when? A. An exact match is necessary in a CREATE statement. B. An exact match is necessary in a SELECT statement. C. An exact match is not possible in a SELECT statement. D. match is not possible in a CREATE statement.
spacial data types (store geometric or geographical values (GPS))
These data types are referred to as geometry types.
partition
To treat an aggregate function as a window function, the OVER clause indicates how to _____________the rows in a result set.
are not collapsed to a single row
Unlike aggregate functions that use GROUP BY, the groups or partitions in an aggregate window function __________.
A. ALTER TABLE TableName ADD ColumnName
What SQL command can be used to add columns to a table? A. ALTER TABLE TableName ADD ColumnName B. ALTER TABLE TableName ADD COLUMN ColumnName C. MODIFY TABLE TableName ADD ColumnName D. MODIFY TABLE TableName ADD COLUMN ColumnName
D. ALTER TABLE TableName DROP COLUMN ColumnName
What SQL command can be used to delete columns from a table? A. MODIFY TABLE TableName DROP ColumnName B. MODIFY TABLE TableName DROP COLUMN ColumnName C. ALTER TABLE TableName DROP ColumnName D. ALTER TABLE TableName DROP COLUMN ColumnName
1. state the problem to be solved by the query in english 2. use pseudocode to outline the query 3. code the subquery and test them to be sure that they return the correct data. 4. code and test the final query
What are the steps in the procedure for building complex queries? 1-4
b. with
What clause stores the result of a query in a temporary table? a. union b. with c. cross join d. none of the above
JSON (stored internally so quick access and automatically validates documents)
What data type is used for storing JavaScript Object Notation documents?
DELETE FROM - deletes one or more rows from a table. To delete only a specific number of rows, you can utilize the LIMIT statement
What does DELETE FROM delete?
a. The SQL ALTER TABLE clause modifies a table definition by altering, adding, or deleting table columns and/or constraints.
What does the ALTER TABLE clause do? a. The SQL ALTER TABLE clause modifies a table definition by altering, adding, or deleting table columns and/or constraints. b. The SQL ALTER TABLE clause is used to insert data into database table c. The SQL ALTER TABLE deletes data from database table d. The SQL ALTER TABLE clause is used to delete a database table
a. Using COUNT
What function can gets the number of records or rows in a table? a. Using COUNT b. Using NUM c. Using NUMBER d. Both a and c
a. Used to uniquely identify a row
What is a candidate key? a. Used to uniquely identify a row b. Alias for primary key c. Used to identify a column d. Alias for foreign key
c. Used to identify a column
What is a candidate key? a. Used to uniquely identify a row b. Alias for primary key c. Used to identify a column d. Alias for foreign key
you must qualify a column name anywhere it appears in the statement if the same name occurs in both tables
When does a column in the SELECT statement need to be qualified?
B. A view is a virtual table which results of executing a pre-compiled query. A view is not part of the physical database schema, while the regular tables are.
What is a view? A. A view is a special stored procedure executed when certain event occurs. B. A view is a virtual table which results of executing a pre-compiled query. A view is not part of the physical database schema, while the regular tables are. C. A view is a database diagram. D. None of these
D - 10
What is returned by MOD(1000,30)? A - 33 B - 30 C - 3 D - 10
3306
What is the default port number the MySQL server listens on for client connections?
c. The WHERE SQL clause condition(s) is applied to all rows in the result set before the HAVING clause is applied (if present). The HAVING clause is used only with SELECT SQL statements and specifies a search condition for an aggregate or a group.
What is the difference between the WHERE and HAVING SQL clauses? a. The WHERE and the HAVING clauses are identical b. The HAVING SQL clause condition(s) is applied to all rows in the result set before the WHERE clause is applied (if present). The WHERE clause is used only with SELECT SQL statements and specifies a search condition for an aggregate or a group. c. The WHERE SQL clause condition(s) is applied to all rows in the result set before the HAVING clause is applied (if present). The HAVING clause is used only with SELECT SQL statements and specifies a search condition for an aggregate or a group.
ENUM can store exactly one value, but SET can store zero, one, or up to 64 different values.
What is the main difference between ENUM and SET?
D. Feature has two 0's in it, at any position
What is the meaning of LIKE '%0%0%' A. Feature begins with two 0's B. Feature ends with two 0's C. Feature has more than two 0's D. Feature has two 0's in it, at any position
//
What symbols are used to form a block comment within a SQL program?
c. vendor_city REGEXP 'NA$'
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 '^SA' c. vendor_city REGEXP 'NA$'
b. To limit the output of a query using an aggregate function only
When do we use a HAVING clause? a. To limit the output of a query b. To limit the output of a query using an aggregate function only c. When GROUP by is used d. both b and c
If you do include a column list, you must specify the column values in the same order and you can omit columns that have default values, accept null values, or are auto generated.
When inserting data you specify the values to be inserted. You can do this with a column list or without. If you don't include a column list you must specify the column values in the same order as in the table, and each must have a value.
D. Both IN and NOT IN
When three or more AND and OR conditions are combined, it is easier to use the SQL keyword(s): A. NOT IN only. B. LIKE only. C. IN only. D. Both IN and NOT IN.
C. rows can either be inserted into a table one at a time or in groups.
When using the SQL INSERT statement: A. rows cannot be copied in mass from one table to another only. B. rows can be modified according to criteria only. C. rows can either be inserted into a table one at a time or in groups. D. rows can be inserted into a table only one at a time only.
c. single value
When you code a subquery in the SELECT clause, the subquery must return a. list of values b. table c. single value d. none of the above
b. the smallest practical components
When you identify the data elements in a new database, you typically subdivide data elements into a. the largest practical components b. the smallest practical components c. components that can be easily parsed each time you use them
a. the likely cause of the error
When you run a SQL statement that contains a coding error, MySQL Workbench displays an error message that does not include: a. the likely cause of the error b. an error code c. brief description of the error
copied-column defs and data not come with - def of primary keys, fk, indexes, etc
When you use the CREATE TABLE AS statement to create a table, what is copied? What does not come with?
c. CREATE TABLE users_copy AS SELECT * FROM users;
Which SQL statement creates a complete copy of the users table call users_copy. a. SELECT * FROM users INTO users_copy; b. CREATE COPY users_copy FROM users; c. CREATE TABLE users_copy AS SELECT * FROM users; d. DUPLICATE users INTO users_copy;
a. union
Which clause is used to combine results that appear from multiple SELECT statements and filters duplicates? a. union b. with c. cross join d. none of the above
b. delete
Which command is used to delete all rows from a table? a. drop b. delete c. remove d. flush
c. Drop table
Which command is used to remove a table from a database? a. Delete b. Alter table c. Drop table d. Create table
c. drop table
Which command is used to remove table from a database? a. delete b. alter table c. drop table d. create table
NOW() SYSDATE() CURDATE() CURTIME()
Which date functions require parenthesis? (4)
NOW() SYSDATE() CURRENT_TIMESTAMP() in this format 2018-12-06 14:12:04
Which date functions return the current local date and time? (3)
-u
Which flag option is available to specify the username when using the MySQL command line client?
Ascending
Which is the default order of sort in ORDER BY clause? Ascending Descending
b. inner
Which join allows for the return of results from more than one table by joining them together with other results based on common column values and is the default join? a. outer b. inner c. cross join d. left join
a. outer
Which join combines rows from different tables even if the join condition is not met? a. outer b. inner c. left d. right
c. cross join
Which join is best for creating all possible combinations for records in two tables? a. union b. with c. cross join d. none of the above
c. cross join
Which join is used to combine each row from one table with each row from another? a. union b. with c. cross join d. none of the above
b. inner
Which join only returns results matching the condition specified by ON? a. union b. inner c. cross join d. none of the above
c. left
Which join returns every row in the left table and if a condition is not met, then null values are used to fill in the right table? a. outer b. inner c. left d. right
D. INSERT
Which of the following SQL commands can be used to add data to a database table? A. ADD B. UPDATE C. APPEND D. INSERT
b. SELECT COUNT(*) FROM Sales
Which of the following SQL statements selects the total number of orders from the Sales table? a. SELECT AVG(OrderNumber) FROM Sales b. SELECT COUNT(*) FROM Sales c. SELECT SUM(OrderNumber) FROM Sales
B - drop view all_marks_english;
Which of the following code will delete a view named all_marks_english? A - delete view all_marks_english; B - drop view all_marks_english; C - delete all_marks_english; D - drop all_marks_english;
b. DOUBLE and c. FLOAT
Which of the following data types is considered an "approximate numeric" data type and does not represent a value exactly. a. DECIMAL b. DOUBLE c. FLOAT d. none of the above
b. .myd
Which of the following file extension is valid MyISAM file extension? A. .ism B. .myd C. .my D. .mys
d. LTRIM
Which of the following functions cannot be used to parse data? a. SUBSTRING_INDEX b. SUBSTRING c. LOCATE d. LTRIM
D. All of the above (COUNT * does not ignore null values; COUNT alone does)
Which of the following group functions ignore null values? A. MAX B. COUNT C. SUM D. All of the above
B. ALTERNATE KEY
Which of the following is NOT a type of SQL constraint? A. PRIMARY KEY B. ALTERNATE KEY C. FOREIGN KEY D. UNIQUE
Select Insert Update Delete
You can refer to a view anywhere you would normally use a table. examples (4)
d. Forgetting to attach /connect to the required database
Which of the following is not a common error when entering and executing SQL statements? a. Forgetting to select the required database b. Misspelling the name of a table c. Misspelling a keyword d. Forgetting to attach /connect to the required database
B - It selects rows from the two tables having different values in the matched columns.
Which of the following is not true about Natural Joins? A - Natural join is based on all columns in two tables having same name B - It selects rows from the two tables having different values in the matched columns. C - If columns having same names have different data types, it returns error. D - None of the above.
b. You cannot insert rows with NULL values to a table.
Which of the following is not true about inserting news rows to a table? a. The INSERT INTO statement is used for inserting new rows to a table b. You cannot insert rows with NULL values to a table. c. You may insert a new row containing values for each column. d. All of the above
C - It returns the first value in the parameter list if it is null.
Which of the following is not true about the COALESCE function? A - It takes multiple alternate values. B - It returns the first non-null expression in the parameter list. C - It returns the first value in the parameter list if it is null. D - None of the above.
a. You cannot run all of the statements in the script at once.
Which of the following is not true when you're using MySQL Workbench to test a script that contains more than one SQL statement? a. You cannot run all of the statements in the script at once. b. You can run each statement in the script by itself. c. When you run a script, the results are displayed in the Results tab.
C - Group functions ignore null values.
Which of the following is true about a group function? A - Group functions operate on sets of rows to produce multiple results per group. B - DISTINCT keyword makes a group function consider duplicate values. C - Group functions ignore null values. D - None of the above.
D - All of the above.
Which of the following is true about deleting a table from the database? A - All the data in the table are deleted B - The table structure is removed C - The indexes in the table are deleted D - All of the above.
D - All of the above.
Which of the following is true about modifying rows in a table? A - You can update some rows in a table based on values from another table. B - If you try to update a record related to an integrity constraint, it raises an error. C - You can modify multiple columns. D - All of the above.
b. total sales
Which of the following isn't a valid column alias? a. total b. total sales c. total_sales d. "Total Sales"
B. Column Alias
Which of the following must be enclosed in double quotes. A. Dates B. Column Alias C. Strings D. All of the above
B. SELECT name, course_name FROM student WHERE age>50 and age <80;
Which of the following query is correct for using comparison operators in SQL? A. SELECT name, course_name FROM student WHERE age>50 and <80; B. SELECT name, course_name FROM student WHERE age>50 and age <80; C. SELECT name, course_name FROM student WHERE age>50 and WHERE age<80; D. None of these
A - select first_name from students where first_name like '_i%';
Which of the following query would display all the students where the second letter in the first name is 'i'? A - select first_name from students where first_name like '_i%'; B - select first_name from students where first_name like '%i_'; C - select first_name from students where first_name like '%i%'; D - select first_name from students where first_name like '_i_';
B) Use comments to describe what each statement does.
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.
b. SELECT SUM(Price) FROM Sales
Which of the following statements gets the total value of the column 'Price' in the 'Sales' table? a. SELECT ADD(Price) FROM Sales b. SELECT SUM(Price) FROM Sales c. SELECT TOTAL(Price) FROM Sales d. SELECT SUM(Price) WHERE Sales
a. By default, the changes are automatically committed to the database.
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. By default, the changes are rolled back when you exit MySQL Workbench unless you commit them. d. If autocommit mode is on you can commit the changes by clicking on the Commit button.
D) CreateTable its DDL (data definition language)
Which of the following types of SQL statements is not a DML statement? A) Insert B) Update C) Select D) CreateTable
A. SELECT
Which of the following types of statements does not modify the data in a table? A. SELECT B. INSERT C. UPDATE D. DELETE
d. All of the above
Which of the following ways are the correct way to get the current date? a. SELECT CURTIME(); b. SELECT CURDATE(); c. SELECT CURRENT_TIME(); d. All of the above
e. LIMIT 4, 7 (offset of 4 = so the offset starts on row 5 because it starts with zero)
Which of the following would return a maximum of 7 rows, starting with the 5th row? a. LIMIT 7 b. LIMIT 3, 7 c. LIMIT 7, 4 d. LIMIT 4 e. LIMIT 4, 7
d. The total unpaid balance due for each vendor_id
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 a. The total of paid invoices for each vendor_id b. The total amount invoiced by each vendor_id c. The total balance for each invoice d. The total unpaid balance due for each vendor_id e. The unpaid balance for each invoice
b. The total unpaid balance due for each vendor_id
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
b. LIKE
Which operator performs pattern matching? a. BETWEEN operator. b. LIKE operator c. EXISTS operator d. None of these
Output
Which tab within MySQL Workbench will display error messages for a failed SQL statement?
a. DELETE FROM Persons WHERE FirstName = 'Peter'
With SQL, how can you delete the records where the "FirstName" is "Peter" in the Persons Table? a. DELETE FROM Persons WHERE FirstName = 'Peter' b. DELETE FROM Persons WHERE FirstName = 'Peter' c. DELETE FirstName='Peter' FROM Persons
1. convert values to integers using the CAST function 2. add zero to the values and it will implicitly cast the characters to integers and then it will sort correctly 3. pad the numbers with leading zeros or spaces
You have a column that is defined as VARCHAR, but it has numbers in it. When you sort it will not sort correctly. What do you do?
2nd
__________ normal form helps remove redundant row data, which can save storage space, make maintenance easier, and reduce the chance of storing inconsistent data.
Normalization
_____________is a formal process you can use to separate the data in a data structure into related tables.
