6.8 Exercises
| Question 1: | An SQL expression can consist of constants. What kind of constants are there? What else could an SQL expression consist of? | | Question 2: | Give an example of a SELECT statement that contains an expression using numeric constants. | | Question 3: | Give an example of a SELECT statement that contains an expression using string constants. | | Question 4: | Give an example of a SELECT statement that contains an expression using date constants. | | Question 5: | Give an example of a SELECT statement that contains an expression using time constants. | | Question 6: | Give an example of a SELECT statement that contains an expression using a column reference. | | Question 7: | Give an example of a SELECT statement that contains an expression using a function call. | | Question 8: | Give an example of a SELECT statement that contains an expression using both a temporal value and a function call. | | Question 9: | The table personnel has the following data for employees in two organizational units:
mysql> SELECT * FROM personnel;
+-----+------+---------+
| pid | unit | salary |
+-----+------+---------+
| 1 | 42 | 1500.00 |
| 2 | 42 | 1700.00 |
| 3 | 42 | 1950.00 |
| 4 | 42 | 2300.00 |
| 5 | 42 | 1900.00 |
| 6 | 23 | 850.00 |
| 7 | 23 | 1250.00 |
| 8 | 23 | 1450.00 |
| 9 | 23 | 1920.00 |
| 10 | 42 | 2200.00 |
| 11 | 23 | 2900.00 |
| 12 | 23 | 1000.00 |
| 13 | 42 | 2850.00 |
+-----+------+---------+
What SQL statement would you issue to retrieve a list showing tax deductions for each employee? Assume that the deduction is 40% of the salary. Gross salary, deduction, and net salary should be displayed with descriptive, instead of mathematical, headings (for example, use Deduction as a heading, not the expression used to calculate the deduction). | | Question 10: | Refer to the data shown for the personnel table in the previous question. What SQL statement would you issue to retrieve a list showing tax deductions for each unit (displayed with descriptive headings instead of mathematical headings)? Assume that the deduction is 40% of the salary. | | Question 11: | Refer to the data shown for the personnel table two questions earlier. What SQL statement would you issue to retrieve a list showing tax deductions for each employee? Assume that the deduction is 40% of the salary for employees with a salary of 2,000 or more, and 30% for those who earn less. The result should show descriptive headings. | | Question 12: | The table personnel has the following data for employees in two organizational units:
mysql> SELECT * FROM personnel;
+-----+------+---------+
| pid | unit | salary |
+-----+------+---------+
| 1 | 42 | 1500.00 |
| 2 | 42 | 1700.00 |
| 3 | 42 | 1950.00 |
| 4 | 42 | 2300.00 |
| 5 | 42 | 1900.00 |
| 6 | 23 | 850.00 |
| 7 | 23 | 1250.00 |
| 8 | 23 | 1450.00 |
| 9 | 23 | 1920.00 |
| 10 | 42 | 2200.00 |
| 11 | 23 | 2900.00 |
| 12 | 23 | 1000.00 |
| 13 | 42 | 2850.00 |
+-----+------+---------+
What SQL statement would you issue to retrieve a list showing the cost rise for the organization if employee salaries are increased by 10%? The result should show descriptive headings instead of mathematical headings (for example, use Cost Rise as a heading, not the expression used to calculate the cost rise). | | Question 13: | Refer to the data shown for the personnel table in the previous question. What SQL statement would you issue to retrieve a list showing the cost rise for the units if employee salaries are increased by 10% for unit 23 and by 5% for unit 42 (displayed with descriptive headings instead of mathematical headings)? | | Question 14: | The table leonardo has the following structure and contents:
mysql> DESCRIBE leonardo;
+-------+---------+
| Field | Type |
+-------+---------+
| name | char(7) |
+-------+---------+
mysql> SELECT * FROM leonardo;
+---------+
| name |
+---------+
| Lennart |
| lennart |
| LENNART |
| lEnNaRt |
+---------+
What output will the following statements yield?
SELECT DISTINCT name FROM leonardo;
SELECT name, COUNT(*) FROM leonardo GROUP BY name;
SELECT name, COUNT(*) FROM leonardo;
| | Question 15: | The table leonardo has the following structure and contents:
mysql> DESCRIBE leonardo;
+-------+----------------+
| Field | Type |
+-------+----------------+
| name | char(7) binary |
+-------+----------------+
mysql> SELECT * FROM leonardo;
+---------+
| name |
+---------+
| Lennart |
| lennart |
| LENNART |
| lEnNaRt |
+---------+
What output will the following statements yield?
SELECT DISTINCT name FROM leonardo;
SELECT name, COUNT(*) FROM leonardo GROUP BY name;
SELECT name, COUNT(*) FROM leonardo;
| | Question 16: | The SQL functions LENGTH() and CHAR_LENGTH() both return the length of a string. Why are there two functions for the same apparent functionality? | | Question 17: | What's the result of the following string comparison?
SELECT BINARY 'LENNART' = UPPER('Lennart');
| | Question 18: | What's the result of the following string comparison?
SELECT BINARY LOWER('LeNNaRT') = LOWER('lEnnArt');
| | Question 19: | What's the result of the following string comparison?
SELECT MD5('lennart') = MD5('LENNART');
| | Question 20: | The IF() function returns the second argument if the condition in the first argument evaluates to true; otherwise, it returns the third argument. Knowing this, what do you expect IF() to return for the following comparisons?
SELECT IF('ABC' = 'abc','TRUE','FALSE'); SELECT IF('ABC' = BINARY 'abc','TRUE','FALSE'); SELECT IF(BINARY 'ABC' = BINARY 'abc','TRUE','FALSE');
| | Question 21: | Name an SQL function that treats its argument as a binary string and thus operates in a case-sensitive manner. | | Question 22: | Will the following query evaluate to true (1), false (0), or NULL? (The pattern contains three underscores.)
SELECT 'abc' LIKE '%___%';
| | Question 23: | Will the following query evaluate to true (1), false (0), or NULL? (The pattern contains five underscores.)
SELECT 'abc' LIKE '%_____%';
| | Question 24: | Will the following query evaluate to true (1), false (0), or NULL?
SELECT '' LIKE '%';
| | Question 25: | Will the following query evaluate to true (1), false (0), or NULL?
SELECT '' LIKE ' % ';
| | Question 26: | Will the following query evaluate to true (1), false (0), or NULL?
SELECT '%' LIKE ' % ';
| | Question 27: | Will the following query evaluate to true (1), false (0), or NULL?
SELECT ' % ' LIKE '%';
| | Question 28: | Will the following query evaluate to true (1), false (0), or NULL?
SELECT 'Lennart' LIKE '_e%_t';
| | Question 29: | Will the following query evaluate to true (1), false (0), or NULL?
SELECT 'Lennart' LIKE '_e%';
| | Question 30: | Will the following query evaluate to true (1), false (0), or NULL?
SELECT NULL LIKE NULL;
| | Question 31: | Will the following query evaluate to true (1), false (0), or NULL?
SELECT NULL LIKE '%';
| | Question 32: | Will the following query evaluate to true (1), false (0), or NULL?
SELECT 'NULL' LIKE '%';
| | Question 33: | Will the following query evaluate to true (1), false (0), or NULL?
SELECT BINARY 'NULL' LIKE 'null';
| | Question 34: | Will the following query evaluate to true (1), false (0), or NULL?
SELECT '2002-02-08' LIKE '2002%';
| | Question 35: | Will the following query evaluate to true (1), false (0), or NULL?
SELECT 23.42 LIKE '2%2';
| | Question 36: | Will the following query evaluate to true (1), false (0), or NULL?
SELECT 023.420 LIKE '2%2';
| | Question 37: | The table CityList has the following structure:
mysql> DESCRIBE CityList;
+------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+----------------+
| ID | int(11) | | PRI | NULL | auto_increment |
| Name | char(35) | | | | |
| Country | char(3) | | | | |
| District | char(20) | | | | |
| Population | int(11) | | | 0 | |
+------------+----------+------+-----+---------+----------------+
You want to retrieve the list of cities in the United States of America (USA), Denmark (DNK), and Germany (DEU) that have a number of inhabitants between 400,000 and 500,000, sorted from largest to smallest. Use the IN() operator to perform this query. The list should look like this:
+----------------+---------+------------+
| City | Country | Population |
+----------------+---------+------------+
| Koebenhavn | DNK | 495699 |
| Leipzig | DEU | 489532 |
| Tucson | USA | 486699 |
| Nuernberg | DEU | 486628 |
| New Orleans | USA | 484674 |
| Las Vegas | USA | 478434 |
| Cleveland | USA | 478403 |
| Dresden | DEU | 476668 |
| Long Beach | USA | 461522 |
| Albuquerque | USA | 448607 |
| Kansas City | USA | 441545 |
| Fresno | USA | 427652 |
| Virginia Beach | USA | 425257 |
| Atlanta | USA | 416474 |
| Sacramento | USA | 407018 |
+----------------+---------+------------+
| | Question 38: | Using the client tool mysql, the following statements are issued:
mysql> CREATE DATABASE CaseTest;
Query OK, 1 row affected (0.00 sec)
mysql> USE casetest;
Database changed
Sometime later, the database is moved to another MySQL server. Trying to select the database as before results in an error message:
mysql> USE casetest;
ERROR 1049: Unknown database 'casetest'
What's the reason for this error message? How could this problem be solved? What could you do to prevent problems like this? | | Question 39: | The table personnel has the following structure:
mysql> DESCRIBE personnel;
+--------+----------------------+
| Field | Type |
+--------+----------------------+
| pid | smallint(5) unsigned |
| unit | tinyint(3) unsigned |
| salary | decimal(9,2) |
+--------+----------------------+
Which of the following statements will produce an error?
mysql> SELECT
-> pid AS 'Employee ID',
-> salary AS Salary
-> FROM personnel
-> WHERE Salary > 2000
-> ;
mysql> SELECT
-> p.pid,
-> p.salary
-> FROM personnel AS P
-> WHERE Salary > 2000
-> ;
mysql> SELECT
-> PERSONNEL.pid AS 'Employee ID',
-> PERSONNEL.salary
-> FROM personnel AS PERSONNEL
-> WHERE PID > 10
-> ;
| | Question 40: | Will the following SQL statement succeed or result in an error? Assume that the table to be created doesn't already exist.
CREATE TABLE select (id INT);
| | Question 41: | Will the following SQL statement succeed or result in an error? Assume that the table to be created doesn't already exist.
CREATE TABLE `select` (id INT);
| | Question 42: | Will the following SQL statement succeed or result in an error? Assume that the table to be created doesn't already exist.
CREATE TABLE 'select' (id INT);
| | Question 43: | Will the following SQL statement succeed or result in an error? Assume that the table to be created doesn't already exist.
CREATE TABLE `select-me, please!` (id INT);
| | Question 44: | Will the following SQL statement succeed or result in an error? Assume that the table to be created doesn't already exist.
CREATE TABLE `select.me.please` (id INT);
| | Question 45: | Will the following SQL statement succeed or result in an error? Assume that the table to be created doesn't already exist.
CREATE TABLE MD5 (id INT);
| | Question 46: | Will the following SQL statement succeed or result in an error? Assume that the table to be created doesn't already exist.
CREATE TABLE `MD5()` (id INT);
| | Question 47: | Will the following SQL statement succeed or result in an error? Assume that the table to be created doesn't already exist.
CREATE TABLE `MD5('Lennart')` (id INT);
| | Question 48: | Will the following SQL statement succeed or result in an error? Assume that the table to be created doesn't already exist.
CREATE TABLE `COUNT(*)` (id INT);
| | Question 49: | Will the following SQL statement succeed or result in an error? Assume that the table to be created doesn't already exist.
CREATE TABLE `0123456789` (id INT);
| | Question 50: | Will the following SQL statement succeed or result in an error? Assume that the database to be created doesn't already exist.
CREATE DATABASE `0123456789`;
| | Question 51: | Will the following statement work?
mysql> CREATE TABLE nulltest (
-> test1 INT NOT NULL,
-> test2 INT NULL,
-> test3 INT NOT NULL DEFAULT 123,
-> test4 INT NULL DEFAULT 456
-> );
| | Question 52: | What are the default values for the column of the following table?
mysql> CREATE TABLE nulltest (
-> test1 INT NOT NULL,
-> test2 INT NULL,
-> test3 INT NOT NULL DEFAULT 123
-> );
| | Question 53: | The table nulltest has the following structure:
mysql> DESCRIBE nulltest;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| test1 | int(11) | | | 0 | |
+-------+---------+------+-----+---------+-------+
What results will the following INSERT statements yield?
INSERT INTO nulltest VALUES (NULL);
INSERT INTO nulltest VALUES (NULL),(NULL),(NULL);
| | Question 54: | Will the following statement evaluate to true (1), false (0), NULL, or result in an error?
SELECT NULL == NULL;
| | Question 55: | Will the following statement evaluate to true (1), false (0), NULL, or result in an error?
SELECT NULL = NULL = NULL;
| | Question 56: | Will the following statement evaluate to true (1), false (0), NULL, or result in an error?
SELECT NULL = NULL = 0;
| | Question 57: | Will the following statement evaluate to true (1), false (0), NULL, or result in an error?
SELECT NULL IS NOT 0;
| | Question 58: | Will the following statement evaluate to true (1), false (0), NULL, or result in an error?
SELECT 0 IS NOT NULL;
| | Question 59: | Will the following statement evaluate to true (1), false (0), NULL, or result in an error?
SELECT 0 <=> 1;
| | Question 60: | The table personnel has the following structure and contents:
mysql> DESCRIBE personnel; SELECT * FROM personnel;
+-------+----------------------+------+-----+---------+
| Field | Type | Null | Key | Default |
+-------+----------------------+------+-----+---------+
| pid | smallint(5) unsigned | | PRI | NULL |
| unit | tinyint(3) unsigned | YES | | NULL |
| grade | tinyint(3) unsigned | YES | | NULL |
+-------+----------------------+------+-----+---------+
+-----+------+-------+
| pid | unit | grade |
+-----+------+-------+
| 1 | 42 | 1 |
| 2 | 42 | 2 |
| 3 | 42 | NULL |
| 4 | 42 | NULL |
| 5 | 42 | NULL |
| 6 | 23 | 1 |
| 7 | 23 | 1 |
| 8 | 23 | 1 |
| 9 | 23 | NULL |
| 10 | 42 | NULL |
| 11 | 23 | NULL |
| 12 | 23 | 1 |
| 13 | 42 | NULL |
+-----+------+-------+
What result will the following statement yield?
SELECT unit, COUNT(grade) FROM personnel GROUP BY unit;
| | Question 61: | Refer to the structure and contents shown for the personnel table in the previous question. What result will the following statement yield?
SELECT unit, SUM(grade) FROM personnel GROUP BY unit;
| | Question 62: | Refer to the structure and contents shown for the personnel table two questions earlier. What result will the following statement yield?
SELECT unit, AVG(grade) FROM personnel GROUP BY unit;
| | Question 63: | The table personnel has the following structure and contents:
mysql> DESCRIBE personnel; SELECT * FROM personnel;
+-------+----------------------+------+-----+---------+
| Field | Type | Null | Key | Default |
+-------+----------------------+------+-----+---------+
| pid | smallint(5) unsigned | | PRI | NULL |
| unit | tinyint(3) unsigned | YES | | NULL |
| grade | tinyint(3) unsigned | YES | | NULL |
+-------+----------------------+------+-----+---------+
+-----+------+-------+
| pid | unit | grade |
+-----+------+-------+
| 1 | 42 | 1 |
| 2 | 42 | 2 |
| 3 | 42 | NULL |
| 4 | 42 | NULL |
| 5 | 42 | NULL |
| 6 | 23 | 1 |
| 7 | 23 | 1 |
| 8 | 23 | 1 |
| 9 | 23 | NULL |
| 10 | 42 | NULL |
| 11 | 23 | NULL |
| 12 | 23 | 1 |
| 13 | 42 | NULL |
+-----+------+-------+
What result will the following statement yield?
SELECT unit, COUNT(*) FROM personnel GROUP BY unit;
| | Question 64: | Refer to the structure and contents shown for the personnel table in the previous question. What result will the following statement yield?
SELECT unit, COUNT(DISTINCT grade) FROM personnel GROUP BY unit;
| | Question 65: | Is the comment in the following statement legal in MySQL?
INSERT /*! DELAYED */ INTO mytable VALUES (5);
| | Question 66: | Is the comment in the following statement legal in MySQL?
SELECT * FROM mytable WHERE id < 100 /*!40000 FOR UPDATE */;
| | Question 67: | Is the comment in the following statement legal in MySQL?
CREATE /*32303 TEMPORARY */ TABLE tbl (col INT);
| | Question 68: | Is the comment in the following statement legal in MySQL?
SELECT * FROM mytable; // * is not good style, though
| | Question 69: | Is the comment in the following statement legal in MySQL?
SELECT a, b, c FROM tbl; --different column list needed?
|
Answers to Exercises | | | Answer 1: | Constants are either literal numbers, strings, or temporal values. An SQL expression can consist of constants, NULL values, references to table columns, and function calls. | | | | Answer 2: |
SELECT 1 + 1;
| | | | Answer 3: |
SELECT 'Hello ', 'world!';
| | | | Answer 4: |
SELECT '2002-02-08', '2003-02-08';
| | | | Answer 5: |
SELECT '21:39:00';
| | | | Answer 6: |
SELECT name, age FROM mytable;
| | | | Answer 7: |
SELECT NOW();
| | | | Answer 8: |
SELECT DATE_FORMAT(NOW(),'%m/%d/%Y');
| | | | Answer 9: |
mysql> SELECT
-> pid AS PID,
-> salary AS 'Gross Salary',
-> salary * 0.4 AS Deduction,
-> salary * 0.6 AS 'Net Salary'
-> FROM personnel
-> ;
+-----+--------------+-----------+------------+
| PID | Gross Salary | Deduction | Net Salary |
+-----+--------------+-----------+------------+
| 1 | 1500.00 | 600.00 | 900.00 |
| 2 | 1700.00 | 680.00 | 1020.00 |
| 3 | 1950.00 | 780.00 | 1170.00 |
| 4 | 2300.00 | 920.00 | 1380.00 |
| 5 | 1900.00 | 760.00 | 1140.00 |
| 6 | 850.00 | 340.00 | 510.00 |
| 7 | 1250.00 | 500.00 | 750.00 |
| 8 | 1450.00 | 580.00 | 870.00 |
| 9 | 1920.00 | 768.00 | 1152.00 |
| 10 | 2200.00 | 880.00 | 1320.00 |
| 11 | 2900.00 | 1160.00 | 1740.00 |
| 12 | 1000.00 | 400.00 | 600.00 |
| 13 | 2850.00 | 1140.00 | 1710.00 |
+-----+--------------+-----------+------------+
| | | | Answer 10: |
mysql> SELECT
-> unit AS Unit,
-> SUM(salary) AS 'Gross Salary',
-> SUM(salary) * 0.4 AS Deduction,
-> SUM(salary) * 0.6 AS 'Net Salary'
-> FROM personnel
-> GROUP BY unit
-> ;
+------+--------------+-----------+------------+
| Unit | Gross Salary | Deduction | Net Salary |
+------+--------------+-----------+------------+
| 23 | 9370.00 | 3748.00 | 5622.00 |
| 42 | 14400.00 | 5760.00 | 8640.00 |
+------+--------------+-----------+------------+
| | | | Answer 11: |
mysql> SELECT
-> pid AS PID,
-> salary as 'Gross Salary',
-> IF(salary < 2000, salary * 0.3, salary * 0.4) AS Deduction,
-> IF(salary < 2000, salary * 0.7, salary * 0.6) AS 'Net Salary'
-> FROM personnel
-> ;
+-----+--------------+-----------+------------+
| PID | Gross Salary | Deduction | Net Salary |
+-----+--------------+-----------+------------+
| 1 | 1500.00 | 450.00 | 1050.00 |
| 2 | 1700.00 | 510.00 | 1190.00 |
| 3 | 1950.00 | 585.00 | 1365.00 |
| 4 | 2300.00 | 920.00 | 1380.00 |
| 5 | 1900.00 | 570.00 | 1330.00 |
| 6 | 850.00 | 255.00 | 595.00 |
| 7 | 1250.00 | 375.00 | 875.00 |
| 8 | 1450.00 | 435.00 | 1015.00 |
| 9 | 1920.00 | 576.00 | 1344.00 |
| 10 | 2200.00 | 880.00 | 1320.00 |
| 11 | 2900.00 | 1160.00 | 1740.00 |
| 12 | 1000.00 | 300.00 | 700.00 |
| 13 | 2850.00 | 1140.00 | 1710.00 |
+-----+--------------+-----------+------------+
| | | | Answer 12: |
mysql> SELECT
-> SUM(salary) AS Salary,
-> SUM(salary) * 1.1 AS 'New Salary',
-> SUM(salary) * 0.1 AS 'Cost Rise'
-> FROM personnel
-> ;
+----------+------------+-----------+
| Salary | New Salary | Cost Rise |
+----------+------------+-----------+
| 23770.00 | 26147.00 | 2377.00 |
+----------+------------+-----------+
| | | | Answer 13: |
mysql> SELECT
-> SUM(salary) AS Salary,
-> SUM(salary) * IF(unit = 23, 1.1, 1.05) AS 'New Salary',
-> SUM(salary) * IF(unit = 23, 0.1, 0.05) AS 'Cost Rise'
-> FROM personnel
-> GROUP BY unit
-> ;
+----------+------------+-----------+
| Salary | New Salary | Cost Rise |
+----------+------------+-----------+
| 9370.00 | 10307.00 | 937.00 |
| 14400.00 | 15120.00 | 720.00 |
+----------+------------+-----------+
| | | | Answer 14: | Each statement yields output as follows:
mysql> SELECT DISTINCT name FROM leonardo;
+---------+
| name |
+---------+
| Lennart |
+---------+
Different lettercases aren't regarded as distinct in a nonbinary context.
mysql> SELECT name, COUNT(*) FROM leonardo GROUP BY name;
+----------+
| COUNT(*) |
+----------+
| 4 |
+----------+
Different lettercases aren't regarded as distinct in a nonbinary context.
mysql> SELECT name, COUNT(*) FROM leonardo;
ERROR 1140: Mixing of GROUP columns (MIN(),MAX(),COUNT()...)
with no GROUP columns is illegal if there is no GROUP BY clause
As the error message indicates, this isn't a legal SQL statement. | | | | Answer 15: | Each statement yields output as follows:
mysql> SELECT DISTINCT name FROM leonardo;
+---------+
| name |
+---------+
| Lennart |
| lennart |
| LENNART |
| lEnNaRt |
+---------+
Different lettercases are regarded as distinct in a binary context.
mysql> SELECT name, COUNT(*) FROM leonardo GROUP BY name;
+---------+----------+
| name | COUNT(*) |
+---------+----------+
| LENNART | 1 |
| Lennart | 1 |
| lEnNaRt | 1 |
| lennart | 1 |
+---------+----------+
Different lettercases are regarded as distinct in a binary context.
mysql> SELECT name, COUNT(*) FROM leonardo;
ERROR 1140: Mixing of GROUP columns (MIN(),MAX(),COUNT()...)
with no GROUP columns is illegal if there is no GROUP BY clause
As the error message indicates, this isn't a legal SQL statement. | | | | Answer 16: | LENGTH() counts string length in bytes, CHAR_LENGTH() counts string length in characters. For single-byte character sets, the two functions return identical results, but for multi-byte character sets, LENGTH() returns a different (higher) number than CHAR_LENGTH(). | | | | Answer 17: | The UPPER() function makes the second string equal to the first string, even when comparison is performed in a case-sensitive manner (BINARY):
+-------------------------------------+
| BINARY 'LENNART' = UPPER('Lennart') |
+-------------------------------------+
| 1 |
+-------------------------------------+
| | | | Answer 18: | The LOWER() function makes both strings equal, even when comparison is performed in a case-sensitive manner with BINARY:
+--------------------------------------------+
| BINARY LOWER('LeNNaRT') = LOWER('lEnnArt') |
+--------------------------------------------+
| 1 |
+--------------------------------------------+
| | | | Answer 19: | The MD5() function produces unequal values for the two strings:
+---------------------------------+
| MD5('lennart') = MD5('LENNART') |
+---------------------------------+
| 0 |
+---------------------------------+
However, this result can be guessed only if you're able to anticipate the results of the MD5() function calls in advance (which is rather improbable):
mysql> SELECT MD5('lennart'), MD5('LENNART');
+----------------------------------+----------------------------------+
| MD5('lennart') | MD5('LENNART') |
+----------------------------------+----------------------------------+
| a6894e0c24b247a33b0de7e3fcd2b53f | d63445ebdc53cabe60ef708beafb39f0 |
+----------------------------------+----------------------------------+
| | | | Answer 20: |
TRUE.
A regular string comparison is performed in a case-insensitive manner.
FALSE.
The keyword BINARY for one of the terms makes the string comparison case sensitive.
FALSE.
The keyword BINARY for one (or both) of the terms makes the string comparison case sensitive.
| | | | Answer 21: | MD5() is an example of a function that operates in a case-sensitive manner. Other examples include the encryption functions in addition to MD5(); for example, ENCRYPT(), AES_ENCRYPT(), DES_ENCODE(), SHA(), and PASSWORD(). (Note that the PASSWORD() function also performs encryption, but it's a special-purpose function for user authentication and shouldn't be used for your own applications.) | | | | Answer 22: | Three underscore metacharacters match any three characters, so they match the entire string 'abc'. That leaves nothing left to be matched, but because % also matches nothing, the expression evaluates to true.
+--------------------+
| 'abc' LIKE '%___%' |
+--------------------+
| 1 |
+--------------------+
| | | | Answer 23: | 'abc' doesn't match the five underscore metacharacters, so the expression evaluates to false.
+----------------------+
| 'abc' LIKE '%_____%' |
+----------------------+
| 0 |
+----------------------+
| | | | Answer 24: | The '%' metacharacter also matches the empty string, so the expression evaluates to true.
+-------------+
| '' LIKE '%' |
+-------------+
| 1 |
+-------------+
| | | | Answer 25: | The space characters surrounding '%' do not match the empty string, so the expression evaluates to false.
+---------------+
| '' LIKE ' % ' |
+---------------+
| 0 |
+---------------+
| | | | Answer 26: | The '%' character is matched by '%', but not by the space characters surrounding it, so the expression evaluates to false.
+----------------+
| '%' LIKE ' % ' |
+----------------+
| 0 |
+----------------+
| | | | Answer 27: | Any non-NULL string is matched by the '%' metacharacter, so the expression evaluates to true.
+----------------+
| ' % ' LIKE '%' |
+----------------+
| 1 |
+----------------+
| | | | Answer 28: | The expression evaluates to true. '_e' at the start of the pattern matches 'Le', '_t' at the end matches 'rt', and the rest of the string is matched by the '%' metacharacter.
+------------------------+
| 'Lennart' LIKE '_e%_t' |
+------------------------+
| 1 |
+------------------------+
| | | | Answer 29: | The expression evaluates to true. _e matches Le and the rest of the string is matched by the '%' metacharacter.
+----------------------+
| 'Lennart' LIKE '_e%' |
+----------------------+
| 1 |
+----------------------+
| | | | Answer 30: | If either operand is NULL, the expression evaluates to NULL.
+----------------+
| NULL LIKE NULL |
+----------------+
| NULL |
+----------------+
| | | | Answer 31: | If either operand is NULL, the expression evaluates to NULL. '%' matches anything (including the empty string) except NULL.
+---------------+
| NULL LIKE '%' |
+---------------+
| NULL |
+---------------+
| | | | Answer 32: | In this case, 'NULL' is just a string like any other string, not the NULL value, so the expression evaluates to true.
+-----------------+
| 'NULL' LIKE '%' |
+-----------------+
| 1 |
+-----------------+
| | | | Answer 33: | In both operands, 'NULL' is just a string (in different lettercases). The comparison is performed in a case-sensitive way due to the BINARY keyword, so the expression evaluates to false.
+---------------------------+
| BINARY 'NULL' LIKE 'null' |
+---------------------------+
| 0 |
+---------------------------+
| | | | Answer 34: | Although it appears that the pattern match is performed on a date value rather than a string, the expression evaluates to true. The reason for this is that the "date" is actually a string value in this context.
+---------------------------+
| '2002-02-08' LIKE '2002%' |
+---------------------------+
| 1 |
+---------------------------+
| | | | Answer 35: | Pattern-matching operations can be performed with numbers, too. Because 23.42 starts and ends with 2, it's matched by '2%2', so the expression evaluates to true.
+------------------+
| 23.42 LIKE '2%2' |
+------------------+
| 1 |
+------------------+
| | | | Answer 36: | Pattern-matching operations can be performed with numbers, too. However, because 023.420 does not start and end with 2, it isn't matched by '2%2'. The expression evaluates to false.
+--------------------+
| 023.420 LIKE '2%2' |
+--------------------+
| 0 |
+--------------------+
| | | | Answer 37: |
mysql> SELECT Name AS City, Country, Population
-> FROM CityList
-> WHERE Country IN('DEU','DNK','USA')
-> AND POPULATION BETWEEN 400000 AND 500000
-> ORDER BY Population DESC
-> ;
| | | | Answer 38: | The database CaseTest was created under an operating system that does not have case-sensitive filenames, such as Windows. The database was then moved to a machine running an operating system that has case-sensitive filenames, such as Linux. To solve this problem, the database should be selected as follows:
mysql> USE CaseTest;
Database changed
To prevent problems with database and table names under different operating systems, you could start the MySQL server on all operating systems you're using with the lower_case_table_names variable set. (However, you should do this before creating any databases or tables.) | | | | Answer 39: | Aliases are not case sensitive except table aliases. Only the second statement will result in an error because the table alias is P and it is referred to as p for the columns to be retrieved. | | | | Answer 40: | An error will result. It isn't possible to use a reserved word as an identifier without quoting the identifier.
mysql> CREATE TABLE select (id INT);
ERROR 1064: You have an error in your SQL syntax.
| | | | Answer 41: | The statement succeeds. When a reserved word is quoted, it can be used as an identifier. In this example, backticks are used; this works under all circumstances.
mysql> CREATE TABLE `select` (id INT);
Query OK, 0 rows affected
| | | | Answer 42: | An error will result. Single quotes can be used as quotes for aliases, but they cannot be used for identifiers such as table names.
mysql> CREATE TABLE 'select' (id INT);
ERROR 1064: You have an error in your SQL syntax.
| | | | Answer 43: | The statement succeeds. Almost every character, including dash, comma, space, and exclamation mark, is legal in a table identifier when the identifier is properly quoted.
mysql> CREATE TABLE `select-me, please!` (id INT);
Query OK, 0 rows affected
| | | | Answer 44: | An error will result. Periods cannot be used in a table identifier even when the identifier is quoted. Because periods are used to separate database names from table names, and table names from column names, they aren't acceptable within an identifier.
mysql> CREATE TABLE `select.me.please` (id INT);
ERROR 1103: Incorrect table name 'select.me.please'
| | | | Answer 45: | The statement results in an error if the MySQL server has been started in ANSI mode (with the option --ansi or --sql-mode=IGNORE_SPACE). In that case, the statement would return an error because MD5 is a function name, and in ANSI mode all function names become reserved words. If the MySQL server hasn't been started in ANSI mode, the statement succeeds.
mysql> CREATE TABLE MD5 (id INT);
Query OK, 0 rows affected
| | | | Answer 46: | The statement succeeds. Parentheses are legal characters in an identifier as long as it is quoted.
mysql> CREATE TABLE `MD5()` (id INT);
Query OK, 0 rows affected
| | | | Answer 47: | The statement succeeds. Single quotes are legal characters in an identifier as long as it is quoted.
mysql> CREATE TABLE `MD5('Lennart')` (id INT);
Query OK, 0 rows affected
| | | | Answer 48: | Whether this statement succeeds is dependent on the operating system under which the MySQL server is running. If the operating system doesn't allow certain characters in filenames, the characters cannot be used for database and table names even when the identifier is quoted.
mysql> CREATE TABLE `COUNT(*)` (id INT);
ERROR 1: Can't create/write to file '.\test\COUNT(*).frm'
(Errcode: 22)
| | | | Answer 49: | The statement succeeds. Identifiers consisting solely of numbers are accepted, provided they're quoted.
mysql> CREATE TABLE `0123456789` (id INT);
Query OK, 0 rows affected
| | | | Answer 50: | The statement succeeds. Quoted identifiers consisting solely of numbers are accepted for database names.
mysql> CREATE DATABASE `0123456789`;
Query OK, 1 row affected
| | | | Answer 51: | The statement will create the table nulltest. All the column specifications are legal:
mysql> CREATE TABLE nulltest (
-> test1 INT NOT NULL,
-> test2 INT NULL,
-> test3 INT NOT NULL DEFAULT 123,
-> test4 INT NULL DEFAULT 456
-> );
mysql> DESCRIBE nulltest;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| test1 | int(11) | | | 0 | |
| test2 | int(11) | YES | | NULL | |
| test3 | int(11) | | | 123 | |
| test4 | int(11) | YES | | 456 | |
+-------+---------+------+-----+---------+-------+
| | | | Answer 52: | The default values can be displayed with the DESCRIBE statement in mysql:
mysql> DESCRIBE nulltest;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| test1 | int(11) | | | 0 | |
| test2 | int(11) | YES | | NULL | |
| test3 | int(11) | | | 123 | |
+-------+---------+------+-----+---------+-------+
If no default value is explicitly assigned to a column in the CREATE TABLE statement, MySQL will assign its standard default value, depending on the datatype of the column. For integer column types, the standard default value is 0. | | | | Answer 53: | Single-row insert operations cannot insert NULL into a table column declared as NOT NULL; an error occurs, as the first of the following listings shows. For multiple-row insert operations, however, this doesn't hold true. Instead, MySQL inserts the standard default value (0 in the case of integer columns), as shown in the second of the following listings:
mysql> INSERT INTO nulltest VALUES (NULL);
ERROR 1048: Column 'test1' cannot be null
mysql> INSERT INTO nulltest VALUES (NULL),(NULL),(NULL);
Query OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 3
mysql> SELECT * FROM nulltest;
+-------+
| test1 |
+-------+
| 0 |
| 0 |
| 0 |
+-------+
3 rows in set
| | | | Answer 54: | The statement returns an error. It's a common error of programmers to mix up the == comparison operator used in many programming languages with the = comparison operator used in SQL.
mysql> SELECT NULL == NULL;
ERROR 1064: You have an error in your SQL syntax.
| | | | Answer 55: | The statement evaluates to NULL. If any operand in an equality comparison is NULL, the expression evaluates to NULL.
mysql> SELECT NULL = NULL = NULL;
+--------------------+
| NULL = NULL = NULL |
+--------------------+
| NULL |
+--------------------+
| | | | Answer 56: | The statement evaluates to NULL. If any operand in an equality comparison is NULL, the expression evaluates to NULL.
mysql> SELECT NULL = NULL = 0;
+-----------------+
| NULL = NULL = 0 |
+-----------------+
| NULL |
+-----------------+
| | | | Answer 57: | The statement returns an error. The IS NULL and IS NOT NULL operators must be followed by NULL.
mysql> SELECT NULL IS NOT 0;
ERROR 1064: You have an error in your SQL syntax.
| | | | Answer 58: | The statement evaluates to true (1) because 0 is not NULL.
mysql> SELECT 0 IS NOT NULL;
+---------------+
| 0 IS NOT NULL |
+---------------+
| 1 |
+---------------+
| | | | Answer 59: | The statement evaluates to false. The special <=> operator can be used for regular comparisons (exactly like the = operator). The only difference is that it's NULL-safe.
mysql> SELECT 0 <=> 1;
+---------+
| 0 <=> 1 |
+---------+
| 0 |
+---------+
| | | | Answer 60: | The statement provides the number of grades assigned for each unit. It counts only assigned grades; that is, grades that are not NULL:
mysql> SELECT unit, COUNT(grade) FROM personnel GROUP BY unit;
+------+--------------+
| unit | COUNT(grade) |
+------+--------------+
| 23 | 4 |
| 42 | 2 |
+------+--------------+
| | | | Answer 61: | For each unit, the statement sums up grades that aren't NULL:
mysql> SELECT unit, SUM(grade) FROM personnel GROUP BY unit;
+------+------------+
| unit | SUM(grade) |
+------+------------+
| 23 | 4 |
| 42 | 3 |
+------+------------+
| | | | Answer 62: | For each unit, the statement calculates the average value of grades that aren't NULL:
mysql> SELECT unit, AVG(grade) FROM personnel GROUP BY unit;
+------+------------+
| unit | AVG(grade) |
+------+------------+
| 23 | 1.0000 |
| 42 | 1.5000 |
+------+------------+
| | | | Answer 63: | The statement counts the number of rows for each unit:
mysql> SELECT unit, COUNT(*) FROM personnel GROUP BY unit;
+------+----------+
| unit | COUNT(*) |
+------+----------+
| 23 | 6 |
| 42 | 7 |
+------+----------+
| | | | Answer 64: | The statement counts how many different non-NULL grades there are for each unit:
mysql> SELECT unit, COUNT(DISTINCT grade) FROM personnel GROUP BY unit;
+------+-----------------------+
| unit | COUNT(DISTINCT grade) |
+------+-----------------------+
| 23 | 1 |
| 42 | 2 |
+------+-----------------------+
| | | | Answer 65: | This is a legal comment in MySQL. The /*! part of the comment ensures that MySQL will not treat this as a comment, but other database servers will. | | | | Answer 66: | This is a legal comment in MySQL. Other database servers will regard this as a comment; MySQL, however, will interpret the contents of the comment as part of the statement if the server version is 4.0.0 or higher. | | | | Answer 67: | This is a legal comment in MySQL, but it is not a version-specific comment because it begins with /*, not /*!. For that purpose, it should be written as /*!32303 TEMPORARY */. | | | | Answer 68: | This isn't a legal comment in MySQL. | | | | Answer 69: | This SQL Standard-style comment isn't legal in MySQL because -- must be followed by a space character. |
|
