Aula 6 - Cap 9 Query For Data - 1Z0-871 MySQL

Apresentação de Marlon e Wagner (DOWNLOAD), Muito bom!

3. Here's an alphabetical list of some basic clauses for the SELECT statement:
FROM
GROUP BY
HAVING
LIMIT
ORDER BY
WHERE
These clauses must be used in a specific order. What's this order?
A. FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT
B. FROM, WHERE, GROUP BY, HAVING, LIMIT, ORDER BY
C.FROM, WHERE, GROUP BY, ORDER BY, HAVING, LIMIT

12. Consider this query:
mysql> SELECT DISTINCT CountryCode FROM CountryLanguage;
What's the corresponding GROUP BY query that would produce the same set of rows?
A. SELECT CountryCode FROM CountryLanguage HAVING count(CountryCode) > 1;
B. SELECT CountryCode FROM CountryLanguage GROUP BY CountryCode;
C. SELECT CountryCode FROM CountryLanguage GROUP BY ALL;

Question 13:
The table petbirth has the following structure and contents:
mysql> DESCRIBE petbirth;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name | char(20) | YES | | NULL | |
| birth | date | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
mysql> SELECT * FROM petbirth;
+----------+------------+
| name | birth |
+----------+------------+
| Fluffy | 1993-02-04 |
| Claws | 1994-03-17 |
| Buffy | 1989-05-13 |
| Fang | 1990-08-27 |
| Bowser | 1995-07-29 |
| Chirpy | 1998-09-11 |
| Whistler | 1997-12-09 |
| Slim | 1996-04-29 |
| Puffball | 1999-03-30 |
| Lucy | 1988-05-08 |
| Macie | 1997-05-08 |
| Myra | 1997-06-09 |
| Cheep | 1998-05-08 |
+----------+------------+
You want to display name and birthday of the oldest pet. What's the appropriate SQL statement?
A. SELECT name, birth FROM petbirth  ORDER BY birth ASC LIMIT 1;
B. SELECT * FROM petbirth  ORDER BY birth LIMIT 1;
C. SELECT name, birth FROM petbirth  ORDER BY birth DESC;
D. SELECT name, birth FROM petbirth  ORDER BY birth DESC LIMIT 1;

Question 14:
The table pet has the following structure and contents:
mysql> DESCRIBE pet;
+---------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| name | char(20) | YES | | NULL | |
| owner | char(20) | YES | | NULL | |
| species | char(20) | YES | | NULL | |
| gender | char(1) | YES | | NULL | |
+---------+----------+------+-----+---------+-------+
mysql> SELECT * FROM pet;
+----------+--------+---------+--------+
| name | owner | species | gender |
+----------+--------+---------+--------+
| Fluffy | Harold | cat | f |
| Claws | Gwen | cat | m |
| Buffy | Harold | dog | f |
| Fang | Benny | dog | m |
| Bowser | Diane | dog | m |
| Chirpy | Gwen | bird | f |
| Whistler | Gwen | bird | NULL |
| Slim | Benny | snake | m |
| Puffball | Diane | hamster | f |
+----------+--------+---------+--------+
What statements would you use to produce the following results?
a. The number of male and female pets (discarding the pets whose gender is unknown)
+--------+-------+
| Gender | Total |
+--------+-------+
| f | 4 |
| m | 4 |
+--------+-------+
b. The number of pets of each species, with the species having the highest number of individuals ap-pearing first
+---------+-------+
| Kind | Total |
+---------+-------+
| dog | 3 |
| cat | 2 |
| bird | 2 |
| snake | 1 |
| hamster | 1 |
+---------+-------+
c. The number of dogs and cats, with the species that has the highest number of individuals to appear
first, using a WHERE clause
+------+-------+
| Kind | Total |
+------+-------+
| dog | 3 |
| cat | 2 |
d. The number of dogs and cats, with the species which has the highest number of individuals to ap-pear first, using a HAVING clause
+------+-------+
| Kind | Total |
+------+-------+
| dog | 3 |
| cat | 2 |
- The column headings for the results should be Kind, Gender, and Total.

Question 15:
The table personnel has the following structure and contents:
mysql> DESCRIBE personnel;
+--------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------------------+------+-----+---------+-------+
| pid | smallint(5) unsigned | NO | PRI | | |
| unit | tinyint(3) unsigned | NO | | | |
| salary | decimal(9,2) | NO | | | |
+--------+----------------------+------+-----+---------+-------+
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 statements would you use to retrieve the following information?
a. Find the number of employees, the salary total, and the average salary per employee for the two
company units, with the highest total salary appearing first. The output should have column head-ings that should look like this:

+------+-----------+----------+-------------+
| Unit | Employees | Total | Average |
+------+-----------+----------+-------------+
| 42 | 7 | 14400.00 | 2057.142857 |
| 23 | 6 | 9370.00 | 1561.666667 |
+------+-----------+----------+-------------+
b. Identify the highest and the lowest salary per unit. The output should have column headings that
should look like this:
+------+---------+---------+
| Unit | High | Low |
+------+---------+---------+
| 23 | 2900.00 | 850.00 |
| 42 | 2850.00 | 1500.00 |
+------+---------+---------+

Question 18:
The table pet has the following structure and contents:
mysql> DESCRIBE pet;
+---------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| name | char(20) | YES | | NULL | |
| owner | char(20) | YES | | NULL | |
| species | char(20) | YES | | NULL | |
| gender | char(1) | YES | | NULL | |
+---------+----------+------+-----+---------+-------+
mysql> SELECT * FROM pet;
+----------+--------+---------+--------+
| name | owner | species | gender |
+----------+--------+---------+--------+
| Fluffy | Harold | cat | f |
| Claws | Gwen | cat | m |
| Buffy | Harold | dog | f |
| Fang | Benny | dog | m |
| Bowser | Diane | dog | m |
| Chirpy | Gwen | bird | f |
| Whistler | Gwen | bird | NULL |
| Slim | Benny | snake | m |
| Puffball | Diane | hamster | f |
+----------+--------+---------+--------+
What COUNT() values will the following statements return?
a. SELECT COUNT(*) FROM pet;
b. SELECT COUNT(gender) FROM pet;
c. SELECT COUNT(DISTINCT gender) FROM pet;
d. SELECT COUNT(DISTINCT species) FROM pet;


Question 22:
The table personnel has the following structure and contents:
mysql> DESCRIBE personnel;
+-------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------------+------+-----+---------+-------+
| pid | smallint(5) unsigned | NO | PRI | | |
| unit | tinyint(3) unsigned | YES | | NULL | |
| grade | tinyint(3) unsigned | YES | | NULL | |
+-------+----------------------+------+-----+---------+-------+
mysql> SELECT * FROM personnel;
+-----+------+-------+
| 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 23:
Refer to the structure and contents shown for the personnel table in the previous question. What res-ult will the following statement yield?
SELECT unit, SUM(grade) FROM personnel GROUP BY unit;

Question 24:
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 25:
The table personnel has the following structure and contents:
mysql> DESCRIBE personnel;
+-------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------------+------+-----+---------+-------+
| pid | smallint(5) unsigned | NO | PRI | | |
| unit | tinyint(3) unsigned | YES | | NULL | |
| grade | tinyint(3) unsigned | YES | | NULL | |
+-------+----------------------+------+-----+---------+-------+
mysql> SELECT * FROM personnel;
+-----+------+-------+
| 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 26:
Refer to the structure and contents shown for the personnel table in the previous question. What res-ult will the following statement yield?
SELECT unit, COUNT(DISTINCT grade) FROM personnel GROUP BY unit;