MySQL "экзамен"

[Morty]

Просьба к сообществу, напишите вопросы чтобы вы спрашивали, если бы вам пришлось
проходить/проводить экзамен по MySQL.
Где-то в таком формате:
1. Как создать базу ?
2. Как добавить таблицу?
3. Как обновить поле ?
4. Есть табличка , нужно отфильровать по опред ключу...
и т.д.
ЗЫ: спс

[Хостинг HostFood.ru]

Тарифы на хостинг в России, от 12 рублей: https://www.host-food.ru/tariffs/hosting/
Тарифы на виртуальные сервера (VPS/VDS/KVM) в РФ, от 189 руб.: https://www.host-food.ru/tariffs/virtualny-server-vps/
Выделенные сервера, Россия, Москва, от 2000 рублей (HP Proliant G5, Intel Xeon E5430 (2.66GHz, Quad-Core, 12Mb), 8Gb RAM, 2x300Gb SAS HDD, P400i, 512Mb, BBU):
https://www.host-food.ru/tariffs/vydelennyi-server-ds/
Недорогие домены в популярных зонах: https://www.host-food.ru/domains/

[zingel]

Выборка по-условию, без этого - никуда.

[hizel]

создать в одной табличке иеархическую структура отношений папа-мама-сын-дочь
и сделать выборку например все родственники женского рода по материнской линии такого то чувака
=]

[zingel]

шведская семья

[zg]

Просьба к сообществу, напишите вопросы чтобы вы спрашивали, если бы вам пришлось
проходить/проводить экзамен по MySQL.

в данный момент готовлюсь к сертификации по мускулю могу из книги впоросов накидать... правда на инглише )))

[Morty]

Просьба к сообществу, напишите вопросы чтобы вы спрашивали, если бы вам пришлось
проходить/проводить экзамен по MySQL.

в данный момент готовлюсь к сертификации по мускулю могу из книги впоросов накидать... правда на инглише )))

кидай

[MAK]

первым делом в общем о реляционных бд. хотя бы первые 3 формы нормализации.
ну а потом уже всякие мелочи типа селект по двум таблицам, типы данных, типы самих таблиц, джоин...

[zg]

По администрированию

Код: Выделить всё

MySQL Architecture
Question 1:
Name a utility program that accesses database tables directly, without communicating with the server.
Why do you have to use this type of program with extra care?

Question 2:
Name two character-based programs and two graphical programs that access tables by communicating
with the server.

Question 3:
Consider the following list of ways to connect to the MySQL server. Which are operating system-dependent?
Which will work only for connections to the local server, and which will also work for remote
connections to a remote server?
a. TCP/IP
b. ODBC
c. Shared memory
d. Named pipe
e. Unix socket file

Question 4:
MySQL uses a two-tier processing model. What are those two tiers?

Question 5:
Which of the following are implications that arise out of MySQL's two-tier model?
a. Some index types are available only for particular storage engines.
b. Transactions that consist of more than one SQL statement are available only for particular storage
engines.

Question 6:
For what kinds of files and directories does MySQL use disk space?

Question 7:
For what kinds of information does the MySQL server allocate memory?

Starting, Stopping, and Configuring MySQL
Question 1:
How would you start the server so that it logs errors? How do you access the information in the error
log?

Question 2:
What's the explanation for the following error?
ERROR 1251: Client does not support authentication protocol
requested by server; consider upgrading MySQL client

Question 3:
How would you start the server so that it logs two things: queries that take longer than 10 seconds to
perform and queries that use no indexes? How can you access the information logged this way?

Question 4:
How would you start the server so that it logs all operations that change data in tables? How can you access
the information logged this way?

Question 5:
As the root login user on a Linux host, how can you start the MySQL server so that it doesn't run as
root, without having to log in as another user? How can you make sure that the server will always run
as a user different from root?

Question 6:
Suppose that you used RPM files on a Linux host to install the MySQL server and clients. Can you start
working with MySQL right away, or are there any further steps you must perform first?

Question 7:
After installing MySQL on Linux from a tar file, the server cannot be started. Looking in the error log,
you find that the server wrote this error message before terminating: Can't find file:
'./mysql/host.frm' (errno: 13). What is the reason for this error, and how could you solve
the problem?

Question 8:
Name some reasons why you would compile MySQL from source, rather than using a binary distribution
provided by MySQL AB.

Question 9:
You want to see the errors reported by the server at startup. Under Windows, how would you direct that output to your terminal rather than to the error log?

Question 10:
You want to see the errors reported by the server at startup. Under Unix, how would you direct that output
to your terminal rather than to the error log?

Question 11:
Suppose that you want to use an option file for the server. The file is named my_config.ini and is
located in the C:\Programme\MySQL\MySQL Server 5.0 directory.
a. How would you instruct the server to read that option file when you start it from the command line?
b. How would you instruct the server to read that option file when you install a Windows service that
starts the server?

Question 12:
To enable support for named time zones, what do you have to do?

Question 13:
What must you do to make the server behave more like other “traditional” database servers? What is the
effect of altering the server's behavior that way?

Question 14:
After installing MySQL, the server fails to start properly. How can you find out what prevented the server
from starting?

Question 15:
What options are mandatory for the server to be able to start? How could you specify these options?

Question 16:
Suppose that the MySQL installation directory is C:\Programme\MySQL\MySQL Server 5.0.
How do you let the server know this when it starts? Is it necessary to specify that directory at all?

Ещё

Код: Выделить всё

Question 58:
Is the following statement about the InnoDB storage engine true or false?
SELECT ... LIMIT is a MySQL extension to standard SQL that cannot be used with the InnoDB
storage engine.

Question 59:
What is the effect of setting the innodb_flush_log_at_trx_commit option to 1 or 0?

Question 60:
By default, an InnoDB shared tablespace is stored in a single regular file named ibdata1 in the
MySQL data directory, but you can configure the tablespace to have more than one component by setting
the innodb_data_file_path option in the [mysqld] section of a MySQL option file. What
other kind of file can you use in a tablespace, and what would be the advantage of not using a regular
file?

Question 61:
When creating an InnoDB table, how do you control which tablespace file InnoDB will use for storing
that table's contents?

Question 62:
For an auto-extending shared tablespace file, how can you prevent it from expanding to use all available
space on the filesystem where it is located?

Question 63:
With InnoDB, you can create tables that are larger than filesystem limits on maximum file size. Why is
that so? Give an example.

Question 64:
Can an InnoDB shared tablespace be distributed across different filesystems?

Question 65:
How can you view status information about InnoDB?

Question 66:
Is the following statement about the InnoDB rollback mechanism true or false?
InnoDB uses information in its log files to perform rollbacks.

Question 67:
Is the following comment about the InnoDB rollback mechanism true or false?
InnoDB uses a data structure called the rollback segment in the InnoDB shared tablespace to store
transaction undo information. If your operations will require large transactions, you must ensure that the
tablespace is large enough to store that information.

По работе с базой

Код: Выделить всё

Question 1:
What kind of join can find matches (values that are present in both tables involved in the join)?

Question 2:
What kind of join or joins find mismatches (values that are present in only one of the tables involved in
the join)?

Question 3:
Write an inner join using the comma operator that retrieves the names of countries and the names of languages
that are the official languages in that country.

Question 4:
Write an inner join using INNER JOIN that retrieves the names of countries and the names of languages
that are the official languages in that country. Can you use either the ON clause or the USING
clause?

Question 5:
Write an inner join that displays country names, city names, and city population of all countries that are
not independent (where the IndepYear column is NULL).

Question 6:
Write an inner join that shows the city population as a total per country, for countries that are not independent
(where the IndepYear column is NULL).

Question 7:
What do you call a join that combines all rows in one table with all rows in another table? What's the
syntax for joining the City and the Country table that way? Why would you typically want to avoid
such joins, and how can you accomplish that?

Question 8:
When querying two tables with an outer join, is it always possible to rewrite a LEFT JOIN as a RIGHT
JOIN, or are there exceptions? If there aren't any exceptions, what is it good for to have two kinds of
outer joins?

Question 9:
In which cases is it necessary to qualify column names with the appropriate table name, and why would
you want to qualify column names with table names even if that's not necessary?

Question 10:
In which cases do you have to qualify table names with database names?

Question 12:
Which of the following statements are true?
1. Joins are restricted to SELECT statements.
2. Joins work for INSERT, UPDATE, and DELETE statements.
3. Joins work for SELECT, UPDATE, and DELETE statements.
4. Joins work for UPDATE statements, but it's not possible to change data in more than one table within
a single join.
5. Joins work for DELETE statements, but it's not possible to delete data in more than one table within
a single join.

Question 13:
Consider the following record from the Country table:
mysql> SELECT
-> Name, Region, Continent, SurfaceArea, Population
-> FROM Country
-> WHERE Name = 'Paraguay'
-> ;
+----------+---------------+---------------+-------------+------------+
| Name | Region | Continent | SurfaceArea | Population |
+----------+---------------+---------------+-------------+------------+
| Paraguay | South America | South America | 406752.00 | 5496000 |
+----------+---------------+---------------+-------------+------------+
What statement would you issue to retrieve a list of countries whose surface area is larger than that of
Paraguay, when you consider countries on the same continent (South America) only? The column headings
of the result should look like this:
+----------+-----------------+---------------+--------------+
| Country | Other Countries | Continent | Surface Area |
+----------+-----------------+---------------+--------------+

Question 14:
Consider the following record from the Country table:
mysql> SELECT
-> Name, Region, Continent, SurfaceArea, Population
-> FROM Country
-> WHERE Name = 'Germany'
-> ;
+---------+----------------+-----------+-------------+------------+
| Name | Region | Continent | SurfaceArea | Population |
+---------+----------------+-----------+-------------+------------+
| Germany | Western Europe | Europe | 357022.00 | 82164700 |
+---------+----------------+-----------+-------------+------------+
What statement would you issue to retrieve a list of the countries worldwide with a population at least as
large as that of Germany? Germany should be included in the list, which should be sorted by descending
population. The column headings of the result should look like this:
+---------+--------------------+------------+
| Country | Other Countries | Population |
+---------+--------------------+------------+

В общем там такого 4 книги ))) могу конкретно по темам накидать

[Morty]

zg BiG SPS
какраз выложил то что нада ...
мож еще напулять если не тяжело в таком же формате
Администраирование, Работа с базой

[ev]

В общем там такого 4 книги ))) могу конкретно по темам накидать

эх... еще бы по русски и с ответами
можно было фак сделать

[zg]

мож еще напулять если не тяжело в таком же формате
Администраирование, Работа с базой

ээээ... я ж говорю, там четыре приложения для четырёх экзаменов ))) кинь в личку мыло, я тебе их сборшу в формате pdf. Просто публиковать не сильно хочется, вроде как авторсоке право и всё такое, а так не жалко ))

эх... еще бы по русски и с ответами

ответы там есть, а "фак" расписан в книге http://anton-pribora.ru/reklama/books/i ... tudy-guide ))) язык правда не русский, но после прочтения трёх четырёх таких книг уже без разницы )))

[wnd]

Код: Выделить всё

http://www.mysql.com/certification/selftest/core/index.php

Associate Test Exam
Your score was: 9 of 10

а если еще книжку прочесть

[wnd]

Просьба к сообществу, напишите вопросы чтобы вы спрашивали, если бы вам пришлось
проходить/проводить экзамен по MySQL.
Где-то в таком формате:
1. Как создать базу ?
2. Как добавить таблицу?
3. Как обновить поле ?
4. Есть табличка , нужно отфильровать по опред ключу...
и т.д.
ЗЫ: спс

попробуй поскать всякие Pass4sure, testking по Mysql сертификации, только там ответы не всегда корректные
тут можно демку скачать, может там 10-15 вопросов можно выдернуть

Код: Выделить всё

http://www.pass4sure.com/006-002.html
http://www.pass4sure.com/005-002.html

а вообще надо искать "народные" версии

P.S. Хотя zg, достаточно вопросов дал, IMHO