Question: Ques: From the following tables, write a SQL query to find those employees who work in a department where the employee’s first name contains the letter 'T'. Return employee ID, first name and last name.

link to the question <https://www.w3resource.com/sql-exercises/sql-subqueries-exercise-14.php>

First, one using SELECT

```
SELECT employee_id, first_name, last_name  
FROM employees  
WHERE department_id IN  
( SELECT department_id  
FROM employees  
WHERE first_name LIKE '%T%' );

```

Second one without using SELECT

```
SELECT employee_id, first_name, last_name
FROM employees
WHERE FIRST_NAME LIKE '%t%';

```

Answer: 
Highly recommeded: Don't try to parse xml with anything other than a proper xml parser. For that you can use either xmlstarlet [edited to reflect @Reino's comment below:

```
xml sel -T -t -m "//ProductHints//Product[PoweredBy="Kontakt"]" -v //SNPID/. --nl -v //Name/. --nl -v //RegKey/. --nl your_file.xml

```

or, even simpler, xidel:

```
xidel your_file.xml -e '//ProductHints//Product[PoweredBy="Kontakt"]//(Name,SNPID,RegKey)'

```

Output (based on the sample xml in your question) should be:

```
547
Mystica
Best Service - Mystica

```
### 1. Locating required files using `find` and `grep`

```
 grep -l "PoweredBy>Kontakt" $(find . -name "*.xml")

```

Validate you get the correct file list.

### 2. Extracting data, and formating data from required files using `awk`

This `awk` script, process only the required files one by one.

```
 awk -F"[><]" '
   $2 == "Name" {ret = $3 " | "}
   $2 == "SNPID" {ret = ret $3 " | "}
   $2 == "RegKey" {print ret $3}
 ' $(grep -l "PoweredBy>Kontakt" $(find . -name "*.xml"))

```

An alternative using `awk` without `grep` command.

```
 awk -F"[><]" '
   $2 == "PoweredBy" && $3 != "Kontakt" {nextfile} 
   $2 == "Name" {ret = $3 " | "}
   $2 == "SNPID" {ret = ret $3 " | "}
   $2 == "RegKey" {print ret $3}
 ' $(find . -name "*.xml")

```