Questões 13/05/2013 1Z0-871

QUESTION 8
You work as a Database Administrator for Pass4sure.com. The company uses MySQL as its database.
You have created a new table named Employees, which keeps all the information of the employees. You
want to add a new row to the Employees table. Which of the following statements will you use to
accomplish the task?
A. INSERT (column1, column2, ...columnN) INTO <table_name> VALUES(value1, value2, ...v alueN);
B. INSERT INTO <table_name>(column1, column2, ...columnN) VALUES(value1 ,value2, ...va lueN);
C. INSERT <table_name>(column1, column2, ...columnN), VALUES(value1, value2, ...valueN );
D. INSERT INTO <table_name>(column1, column2,... columnN), VALUES(value1, value2, ...v alueN);

QUESTION 10
Which of the following are true about UPDATE statements?
Each correct answer represents a complete solution. Choose all that apply.
A. You can use the WHERE clause to have your update affects a specific set of rows.
B. You use the SET clause to update multiple columns of a table separated by commas.
C. You can use co-related sub query in UPDATE statements to update data from other tables.
D. If you don't use the WHERE clause then the UPDATE will not update any rows in the table.

QUESTION 12
Which of the following statements is correct for equijoin used to join two tables named Employees and
Department?
A. SELECT Dept_Name, Emp_Name
FROM Departments d1, Employees e1
ORDER BY Dept_Name, Emp_Name;
B. SELECT D.Dept_Name, E.Emp_Name
FROM Departments d1, Employees e1
HAVING Dept_Name, Emp_Name;
C. SELECT E.Emp_Name, D.Dept_Name
FROM Departments d1, Employees e1
WHERE Dept_No = Dept_No
ORDER BY Dept_Name, Emp_Name;
D. SELECT Dept_Name, Emp_Name
FROM Departments d1, Employees e1
WHERE d1.Dept_No = e1.Dept_No
ORDER BY Dept_Name, Emp_Name;

QUESTION 14
Which of the following are the types of numeric literals that can be used in arithmetic expressions?
Each correct answer represents a complete solution. Choose all that apply.
A. Numeric
B. Integer
C. Binary
D. Real

QUESTION 19
Adam works as a Database Administrator for Pass4sure.com. The company uses MySQL as its database.
Adam has created a table named Employees in the database. He wants to retrieve the information of those
employees who have at least one person reporting to them. Which of the following queries will Adam
execute to accomplish the task?
A. SELECT employee_id, last_name, job_id, department_id FROM Employees WHERE employee_id
EXISTS (SELECT manager_id WHERE manager_id is NULL);
B. SELECT employee_id, last_name, job_id, department_id FROM Employees HAVING employee_id
IN (SELECT manager_id FROM Employees WHERE manager_id is NOT NULL);
C. SELECT employee_id, last_name, job_id, department_id FROM Employees outer WHERE EXISTS
(SELECT 'x' FROM Employees WHERE manager_id = outer.employee_id);
D. SELECT employee_id, last_name, job_id, department_id FROM Employees WHERE employee_id IN
(SELECT manager_id WHERE manager_id is NOT NULL);

QUESTION 20
You work as a Database Administrator for Pass4sure.com. The company uses the MySQL database.
You have created a new table named Employees in the database and performed update operation but you
got an error because of the last transaction. Now, you want to terminate the last transaction. Which of the
following commands can you use to accomplish the task?
Each correct answer represents a complete solution. Choose all that apply.
A. ROLLBACK
B. REMOVE
C. DELETE
D. COMMIT

QUESTION 21
You work as a Database Administrator for Pass4sure.com. The company uses MySQL as its database.
The database contains a table named Employees. You want to remove an index named Emp_name_idx
from the Employees table. Which of the following statements should you use to accomplish the task?
A. DELETE INDEX Emp_name_idx;
B. CANCEL INDEX Emp_name_idx;
C. REMOVE INDEX Emp_name_idx;
D. DROP INDEX Emp_name_idx;


QUESTION 30
Which of the following functions can be performed by a view?
Each correct answer represents a complete solution. Choose all that apply.
A. Restrict a user to specific columns in a table.
B. Contain a sub query in the FROM clause.
C. Join columns from multiple tables, so that they look like a single table.
D. Restrict a user to specific rows in a table.


QUESTION 31
Mark works as a Database Administrator for Neon Inc. He issues the following query to insert rows in a
table named Emp:
SELECT * FROM Emp
SET AUTOCOMMIT=0
INSERT INTO Emp VALUES('XXX','John',04/02/1977)
INSERT INTO Emp VALUES('YYY','Richard',03/01/1977)
ROLLBACK
How many rows will be inserted in the Emp table after the ROLLBACK statement has been issued?
A. Both rows will be inserted.
B. No rows will be inserted.
C. Only the second row will be inserted.
D. Only the first row will be inserted.

Resultado do simulado 1Z0-871

Bom, de acordo com o link da nossa prova (http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=5001&get_params=p_exam_id:1Z0-871) precisamos de 60% para passar na prova, então, seguindo essa métrica entendemos que de 65 questões 60% seria 39 questões. Segue lista do resultado por ordem de maior score. Quem acertou 39 ou mais, praticamente está garantido na prova! lembrando que não podemos relaxar, quando mais estudamos mais garantimos a vitória!

Abraço,

Aluno: Filipe
Acertos: 56
Erros: 9

Aluno: André
Acertos: 45
Erros: 20

Aluno: Heron
Acertos: 36
Erros: 29

Aluno: Oromar
Acertos: 33
Erros: 32

Aluno: Ivan
Acertos: 32
Erros: 33

Aluno: Erick
Acertos: 32
Erros: 33


Aluno:Augusto
Acertos: 30
Erros:35


Aluno: Lizandra
Acertos: 20
Erros:45

Aluno:Marcelo
Acertos: 19
Erros:46

Aluno:Adonay
Acertos: 14
Erros:51

Aluno:Alex
Acertos: 14
Erros:51

Aluno:Marlon
Acertos: 14
Erros:51

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;

Aula 5 - Tables e Indexes - Capítulo 8

Segue apresentação de Ivan (DOWNLOAD APRESENTAÇÃO)

Pessoal segue exercício de hoje...

1. Is the following statement true or false?
InnoDB imposes no limit on the number of tables that can be held in the InnoDB tablespace.
A. Yes
B. No

2. Is the following statement true or false?
In a MySQL database, every table has an .frm file in the appropriate database directory, regardless of
the storage engine used
A. Yes
B. No

3. Consider the following table:
mysql> DESCRIBE t;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| i | int(11) | NO | PRI | | |
+-------+---------+------+-----+---------+-------+
Which statements can you use to drop a PRIMARY KEY?
 A. DROP PRIMARY KEY
 B. ALTER TABLE T DROP PRIMARY KEY
 C. DROP PRIMARY KEY TABLE T;

4. Name the different kinds of indexes that MySQL supports
A. PRIMARY KEY, UNIQUE, INDEX (non-unique), FULLTEXT, and SPATIAL
B. PRIMARY KEY, UNIQUE, INDEX (non-unique)
C. PRIMARY KEY, UNIQUE
D.PRIMARY KEY, UNIQUE, INDEX (non-unique), FULLTEXT, and SPACIAL

5. What must be true of the columns named in a UNIQUE index for the index to be functionally equivalent
to a PRIMARY KEY on the same columns?
A. NOT NULL
B. INDEX
C. UNSIGNED NOT NULL

6. When you use DROP TABLE to remove a table, how do you tell MySQL not to report an error if the table doesn't exist?
A.  DROP TABLE IF EXISTS tab
B. CROP TABLE CHECK tab
C. IF EXISTS DROP TABLE tab

7. Is the following statement true or false?
A table must contain at least one column.
 A. TRUE
 B. FALSE
 C. DEPENDS

8. Is the following statement true or false?
A table must contain at least one row.
 A. TRUE
 B. FALSE

9. Is the following statement true or false?
To create a table, you must first issue a statement to choose a default database in which to store the ta-ble.
 A. FALSE
 B. TRUE

10. Which clause can you add to a CREATE TABLE statement to ensure that no error occurs if the table
already exists?
A. IF NOT EXISTS
B. IF NOT EXIST
C. IF NOT EXISTS TABLE
D. NONE

11. Why does the following SQL statement fail?
CREATE TABLE cats (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
name CHAR(10)
);
 12. There are two ways using SQL statements to create a copy of a table (structure and data). Assume that
you want to create a copy of the world.City table in the test database. How would you accomplish
that, using those two ways?
A. CREATE TABLE test.City SELECT * FROM world.City
B. CREATE TABLE City SELECT * FROM City
C. CREATE TABLE test.City LIKE world.City
D. NONE

13. Is the following statement true or false?
You can add multiple columns to a table with a single ALTER TABLE statement.
A. TRUE
B. FALSE

14. Is the following statement true or false?
You can add one or more rows to a table with a single ALTER TABLE statement.
 A. TRUE
 B. FALSE  
15. Is the following statement true or false?
You can create multiple indexes at a time with a single ALTER TABLE statement.
 A. TRUE
B. FALSE
16. Is the following statement true or false?
You can drop all columns of a table (thus dropping the table itself) with a single ALTER TABLE state-ment.
 A. TRUE
B. FALSE
17. There are two ways to rename table tbl to tbl_new with SQL statements. What statements can you
use?
 A. ALTER TABLE tbl RENAME TO tbl_new;
 B. RENAME TABLE tbl TO tbl_new;
 C. RENAME TABLE t1 TO tmp, t2 TO t1, tmp TO t2;

18. Name the two most common reasons to create an index on a table
 A. Indexes can speed up retrievals, especially for large tables, and they can be used to place restrictions on
columns to ensure that a column or a set of columns may contain only unique-valued entries.
 B. Especially for large tables, and they can be used to place restrictions on
columns to ensure that a column or a set of columns may contain only unique-valued entries. 
 C. NONE

19. Table mytable has a composite PRIMARY KEY consisting of both col1 and col2. Is it possible to
declare one of the two columns as NULL, like this?
CREATE TABLE mytable (
col1 CHAR(5) NOT NULL,
col2 CHAR(5) NULL,
PRIMARY KEY (col1,col2)
);
A. YES
B. NO

20. You have a table mytable that looks like this:
mysql> DESCRIBE mytable;
+-------+---------+
| Field | Type |
+-------+---------+
| col1 | int(11) |
| col3 | int(11) |
+-------+---------+
You want to add three more columns: col0 as the first column in the table, col2 between col1 and
col3, and col4 as the last column. All new columns should be of type INT. What SQL statement do
you issue?
A. ALTER TABLE mytable
-> ADD col0 INT FIRST,
-> ADD col2 INT,
-> ADD col4 INT LAST;
B. ALTER TABLE mytable
-> ADD col0 INT FIRST,
-> ADD col2 INT AFTER col1,
-> ADD col4 INT;
C. NONE
 
21. You want to see what indexes you have in table tbl, but DESCRIBE tbl does not show sufficient in-formation. What other statement can you issue to obtain additional information about the table structure?
 A. SHOW CREATE TABLE tbl
 B. SHOW TABLE tbl
 C. SELECT CREATE TABLE tbl

22. To declare a primary key on only one column (col1, with data type INT) of table tbl at creation time,
you can use the following syntax:
mysql> CREATE TABLE tbl (col1 INT NOT NULL PRIMARY KEY);
What's the correct syntax if you want to declare a composite primary key for this table on two INT
columns col1 and col2?
A. CREATE TABLE tbl (
-> col1 INT NOT NULL,
-> col2 INT NOT NULL,
-> PRIMARY KEY (col1, col2)
-> );
B. CREATE TABLE tbl (
-> col1 INT NOT NULL,
-> col2 INT NOT NULL,
-> PRIMARY KEY (col1), PRIMARY KEY (col2) ); 
C. CREATE TABLE tbl (
-> col1 INT NOT NULL PRIMARY ,
-> col2 INT NOT NULL PRIMARY ,
-> KEY (col1, col2)
-> );

23. How can you empty a table? What's the best SQL statement for this?
A. TRUNCATE TABLE City
B. DELETE FROM City
C. DROP FROM City
D. DELETE TABLE City
E. ALL


25. What SHOW statement will retrieve a list of columns in the table test.mytest, where the column
names begin with id?
A. SHOW COLUMS FROM mytest FROM test LIKE 'id%';
B. SHOW COLUMN FROM mytest FROM test LIKE 'id%';
C. NONE
D. SHOW COLUMNS FROM mytest FROM test LIKE 'id%';
E. SHOW COLUMNS FROM mytest FROM test LIKE '_id%';

26. What SHOW statement will retrieve a statement that could be used to recreate the table test.mytest
in an arbitrary database? Assume that test is not the default database
A. SHOW CREATE test.mytest;
B. SELECT CREATE TABLE test.mytest;
C. SHOW CREATE TABLE test.mytest;

27. You know that MyISAM is the built-in default storage engine in MySQL. So, what is the explanation for
the Engine value in the following SHOW TABLE STATUS output? What could you do to make sure
that the table gets created as a MyISAM table?
mysql> CREATE TABLE defaulttype (id INT);
mysql> SHOW TABLE STATUS LIKE 'defaulttype';
+-------------+--------+-| Name | Engine | ...
+-------------+--------+-| defaulttype | InnoDB | ...
+-------------+--------+-
A. SET storage_engine = InnoDB;
B. SET SQL_MODE = InnoDB;
C. SET storage_enginer = InnoDB;

28. How can you change the server's default storage engine for new tables from MyISAM to InnoDB if the
server is already running?
A. SET GLOBAL storage_engine=InnoDB;
B. SET SQL_MODE storage_engine=InnoDB;
C. ALL

Questões 1Z0-870 e 1Z0-871

EXERCÍCIO 870 E 871 (DOWNLOAD | RESPOSTAS)

Aula 4 MySQL Developer, Certificação 1Z0-871

Questões de hoje (EXERCÍCIO 1) (EXERCÍCIO 2)