Our given task is:
Query a database.
Use the database employees on system1 and the appropriate SQL queries to answer the following question.
– How many current or past employees have first name Renny and have or had a title Technique Leader?
Connect to the MariaDB database as user luigi using mysql command:
mysql -u luigi -p
mysql : is a client to connect to the MariaDB database server.
-u : is option to specify the username for this connection.
luigi : username for this connection.
-p : is option to prompt for password.
User luigi has been set up as part of previous RHCE task Configure a database which is predecessor to this task.
Password for user luigi is wakennym.
Connect to the database employees:
A database employees has been configured in previous RHCE task Configure a database.
Red Hat will not provide you with any database model on your RHCE exam so you will not know the structure of the tables and relational model of a data but there will be only a few tables which will contain only a few rows so you will get the idea of a database model quickly.
Use describe command to get column definitions for tables employees and titles:
Desc is abbreviation of Describe.
We need to construct a query to answer the question:
How many current or past employees have first name Renny and have or had a title Technique Leader?
We can see first_name column in the employees table and title column in the titles table. To join these two tables we have to use emp_no column which is in both of tables.
To answer the number of employees with first name Renny and a title Technique Leader we will use the COUNT function.
SELECT COUNT(employees.emp_no) FROM employees
INNER JOIN titles ON employees.emp_no=titles.emp_no
WHERE employees.first_name='Renny' AND title='Technique Leader';
So the number of current or past employees who have first name Renny and have or had a title Technique Leader is 8.
In this RHCE exam task Red Hat is interested only in the final number which is 8 in our example. Red Hat does not mind if you don’t use JOIN or COUNT function to get the correct answer.
Please note that Red Hat will give you a database with few tables which have only a few rows so you don’t need to learn any MYSQL functions or JOIN syntax.
To get the correct answer you can simply query employees table for all employees with name Renny and then check their employees number in the titles table for their title.
SELECT * FROM employees WHERE first_name='Renny';
SELECT * FROM titles WHERE emp_no=498462;
In this case employee Renny does not have and did not have title Technique Leader.
Red Hat is not going to give you a database with such a number of data which I have here on my example. Red Hat database will be significantly smaller. I did not have Red Hat database so I used in my example data from Employees Sample Database from dev.mysql.com. Here is link to this database:
You can also put down on a paper all employees numbers with name Renny and then match them to employees with the title Technique Leader.
SELECT * FROM titles WHERE title='Technique Leader';
Please note once again – there will be much less rows in tables on your RHCE exam so it will be easy to match data from two tables without need to JOIN them.
Related RHCE task is to Configure a database.
Related Video Tutorials:
RHCE EX300 Exam:
Configure an iSCSI target on Red Hat Enterprise Linux