Go on. Explore!

lvl 0 • 0 xp

lvl 1

Home Market Decks Interests

mySQL hard quiz

memorize.ai (lvl 286)

(0)

23 cards

()

Section 1

Preview this deck

Danny wants to know how much rows with ns=0 are in the table, but wants to limit the MySQL search to the first 50 matching rows only. The query was: SELECT COUNT(*) FROM myTable WHERE ns=0 limit 50; What answer will this query return if there are 450 rows with ns=0 in the table?

Front

1 / 23

SQL

0.0

0 reviews

5			0
4			0
3			0
2			0
1			0

Active users

All-time users

Favorites

Last updated

6 years ago

Date created

Mar 1, 2020

Cards (23)

Section 1

(23 cards)

Danny wants to know how much rows with ns=0 are in the table, but wants to limit the MySQL search to the first 50 matching rows only. The query was: SELECT COUNT(*) FROM myTable WHERE ns=0 limit 50; What answer will this query return if there are 450 rows with ns=0 in the table?

Front

450 Feedback: There is no error with that query. However, the limit will be on the output. Since the output of count(*) is always 1 row the limit 50 restriction will not take place.

Back

MySQL RENAME TABLE syntax can't be used to rename a TEMPORARY table.

Front

True You cannot use RENAME to rename a TEMPORARY table. However, you can use ALTER TABLE instead: "ALTER TABLE orig_name RENAME new_name;"

Back

What is the default for Packed indexes?

Front

Pack only strings fields in indexes.

Back

In All cases, a MySQL ALTER TABLE makes a temporary copy of the original table. MySQL waits for other operations that are modifying the table, then proceeds. It incorporates the alteration into the copy, deletes the original table, and renames the new one. While ALTER TABLE is executing, the original table is readable by other sessions. Please choose: True or False.

Front

False In most cases (not all), ALTER TABLE makes a temporary copy of the original table. HOWEVER, If you use ALTER TABLE tbl_name RENAME TO new_tbl_name without any other options, MySQL simply renames any files that correspond to the table tbl_name without making a copy.

Back

Question: Given the following MySQL query (that involve comparison operators with NULL), what will be the output? SELECT 0 = NULL, 1 <> NULL, 1 < NULL, 0 > NULL, NULL = NULL;

Front

NULL,NULL,NULL,NULL,NULL Feedback: NULL means "missing value". In MySQL, NULL is not a zero and it has a special meaning. To test for NULL, you cannot use the arithmetic comparison operators. Instead, you should use the IS NULL and IS NOT NULL operators. In MySQL, the NULL value is never true in comparison to any other value, even NULL. An expression that contains NULL always produces a NULL value unless otherwise indicated in the documentation for the operators and functions involved in the expression.

Back

By using User Defined Variables one can store a value and then refer to it later. This enables passing values from one statement to another. User Defined Variables is a powerful technique for writing efficient queries. Which of the following statements are true about User Defined Variables?

Front

- Undefined variables do not generate syntax errors. - They prevent query caching. - In some versions of MySQL they are Case-Sensitive. Feedback: User Defined Variables are temporary variables that live as long as the connection to the server lives. Hence, you can't use them for interconnection communication. MySQL's User Defined Variables typing is dynamic meaning that a variable's type changes when it's assigned to. You can't use User Defined Variables where identifier or literal is needed. However, despite their drawbacks they can give you some great procedural logic benefits.

Back

In MySQL, index columns are all sorted descending by default. Please choose: True or False

Front

False In MySQL, index columns are all sorted ascending by default.

Back

Question: Given that tables A and B have the same declaration and the column 'title' has FULLTEXT KEY, what error the following query will produce? SELECT SUM(c) as count FROM ( ( SELECT count() c FROM A WHERE MATCH (title) AGAINST ('computer') ) UNION ALL ( SELECT count() c FROM B WHERE MATCH (title) AGAINST ('computer') ) )

Front

Every derived table must have its own alias Feedback: Sub-queries (derived table) are legal in a SELECT statement's FROM clause. However, the alias name clause for each derived table is mandatory, because in MySQL every table in a FROM clause must have a name. To correct the query, put an alias at the end of the query.

Back

What will be the output of the following MySQL query (which involve Logical OR with NULL)? SELECT (1 || NULL) , (0 || NULL) , (NULL || 1) , (NULL || 0) , (NULL || NULL);

Front

1 , NULL , 1 , NULL , NULL When both operands are non-NULL, MySQL logical-OR, return 1 if any operand is non-zero, and 0 otherwise. However, with a NULL operand, the result is 1 if the other operand is non-zero, and NULL otherwise. If both operands are NULL, the result is NULL. See http://dev.mysql.com/doc/refman/5.0/en/logical-operators.html

Back

When declaring data types: INT(1) uses 1 bit while INT(20) uses 20 bits. Please choose: True or False.

Front

False The number in parentheses only specifies the number of characters MySQL will reserve for display purposes. For storage and computational purposes, INT(1) is identical to INT(20).

Back

Tables A and B have the same declaration and the column 'title' has FULLTEXT KEY. What error the following query will produce, given that the alias names for the UNION ALL sub queries column are different? SELECT SUM(c) as count FROM ( ( SELECT count() c FROM A WHERE MATCH (title) AGAINST ('computer') ) UNION ALL ( SELECT count() different_name FROM B WHERE MATCH (title) AGAINST ('computer') ) ) t

Front

There will be no error Feedback: In MySQL, UNION is used to combine the result from multiple SELECT statements into a single result set. The column names from the first SELECT statement are used as the column names for the results returned.

Back

Is it possible, in MySQL, to insert a zero or an empty string into a column defined as NOT NULL?

Front

True In MySQL, zero and empty string are values, whereas NULL means "not having a value".

Back

Question: What values the following table will have after executing the following statement: INSERT INTO test (a,b,c) VALUES (1,2,3),(4,5,6) ON DUPLICATE KEY UPDATE c=VALUES(c)+1; The table 'test' structure: CREATE TABLE test (a int, b int, c int, PRIMARY KEY (a,b)) ENGINE=MyISAM DEFAULT CHARSET=utf8; The table 'test' has the following values: a b c 1 2 1 4 5 4

Front

(1,2,4),(4,5,7) Feedback: In an INSERT ... ON DUPLICATE KEY UPDATE statement, you can use the VALUES(col_name) function in the UPDATE clause to refer to column values from the INSERT portion of the statement. In other words, VALUES(col_name) in the UPDATE clause refers to the value of col_name that would be inserted, had no duplicate-key conflict occurred. This function is especially useful in multiple-row inserts. The VALUES() function is meaningful only in the ON DUPLICATE KEY UPDATE clause of INSERT statements and returns NULL otherwise. See http://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html#func...

Back

Given the following MySQL query, what will be the output? SELECT 0 IS NULL, 0 IS NOT NULL, "" IS NULL, "" IS NOT NULL;

Front

0,1,0,1 Feedback: In MySQL, zero and empty string are values, whereas NULL means "not having a value".

Back

MySQL provides standard SQL pattern matching as well as a form of pattern matching based on extended regular expressions. Please choose True or False for the following statement: In MySQL, SQL patterns are case-insensitive by default.

Front

True In MySQL, SQL patterns are case-insensitive by default.

Back

What statements are TRUE about the MySQL "IN" Operator? i.e. "expr IN ( values )"

Front

- Returns 1 if expr is equal to any of the values in the "IN" list - If all values are constants, they are evaluated according to the type of expr. - If all values are constants they are sorted so the search for the item is done using a binary search. - The number of values in the IN list is only limited by the max_allowed_packet value. - "IN" returns NULL if the expression on the left hand side is NULL - "IN" returns NULL if no match is found in the list and one of the expressions in the list is NULL. - When used with a subquery, the word "IN" is an alias for "= ANY". - "IN" can also contain certain types of sub queries but it will not be efficient like if the values are constants.

Back

What should you do, if you want your GROUP BY results ordered by the grouped column?

Front

Nothing, as a default, the GROUP BY results are returned sorted by the grouped column. Feedback: The Group By return the result sorted by default. If you don't need them sorted add: ORDER BY NULL

Back

SELECT ... INTO OUTFILE writes the selected rows to a file. On which location? On the server file system or the client file system?

Front

On the server file system feedback: SELECT ... INTO OUTFILE writes the selected rows to a file. Column and line terminators can be specified to produce a specific output format. The SELECT ... INTO OUTFILE 'file_name' form of SELECT writes the selected rows to a file. The file is created on the server host, so you must have the FILE privilege to use this syntax. file_name cannot be an existing file, which among other things prevents files such as /etc/passwd and database tables from being destroyed. The SELECT ... INTO OUTFILE statement is intended primarily to let you very quickly dump a table to a text file on the server machine. If you want to create the resulting file on some other host than the server host, you normally cannot use SELECT ... INTO OUTFILE since there is no way to write a path to the file relative to the server host's file system. However, if the MySQL client software is installed on the remote machine, you can instead use a client command such as mysql -e "SELECT ..." > file_name to generate the file on the client host. It is also possible to create the resulting file on a different host other than the server host, if the location of the file on the remote host can be accessed using a network-mapped path on the server's file system. In this case, the presence of mysql (or some other MySQL client program) is not required on the target host.

Back

What is the difference between MySQL's DATETIME and TIMESTAMP types?

Front

- TIMESTAMP uses half DATETIME storage space. - TIMESTAMP has smaller range. - DATETIME and TIMESTAMP not stored as string and they both store date and time. DATETIME uses 8 bytes while TIMESTAMP uses only 4 bytes. The DATETIME range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59' while the TIMESTAMP range is '1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC.

Back

How can you force MySQL to full scan a table (meaning force MySQL to ignore all indexes and choose to full scan the table in order to execute the given query)?

Front

Adding "FORCE INDEX ()" clause (with empty parenthesis) Feedback: Sometimes using a full scan yields better performance than using an index. When you have a query that you don't want MySQL to use indexe, all you need to do is add "FORCE INDEX ()" to the query just after the table name.

Back

What is the result of trying to insert NULL as value to an "auto increment" column?

Front

New generated sequence number Feedback: If no value is specified for the AUTO INCREMENT column, MySQL will assign a new sequence number automatically. Explicit assign of NULL or 0 to an AUTO_INCREMENT column will generate sequence numbers.

Back

Given the following MySQL query (that involve arithmetic addition with NULL), what will be the output? SELECT NULL+0, NULL+1, 0+NULL, 1+NULL, NULL+NULL;

Front

NULL,NULL,NULL,NULL,NULL Feedback: In MySQL, NULL is not a zero. NULL means "missing value" and in MySQL NULL has a special meaning. An expression that contains NULL always produces a NULL value unless otherwise indicated in the documentation for the operators and functions involved in the expression.

Back

What is true about MySQL's BLOB and TEXT types?

Front

- At retrieval, MySQL pad BLOB data to the required length with '\0'. - BLOB store binary data with no collation or character set while TEXT types have a character set and a collation. - MySQL sorts BLOB and TEXT column with only the first max_sort_length bytes. - Feedback: BLOB and TEXT designed to store large amount of data. MySQL sorts BLOB and TEXT the same: it will not sort the full length of the data, it sorts only the first max_sort_length bytes of such columns. However, there are two main differences: a. BLOB store binary data with no collation or character set while TEXT types have a character set and a collation; b. At retrieval, MySQL pad BLOB data to the required length with '\0'.

Back