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: 
This might work for you (GNU sed):

```
sed -sE '/^\s*<(Name|SNPID|PoweredBy|RegKey)>(.*)<\/\1>\s*$/{s//\1:\2/;H}
         $!d;g;/PoweredBy:Kontakt/!d
         s/Name:([^\n]*)(.*)/\2\n\1/
         s/SNPID:([^\n]*)(.*)/\2|\1/
         s/RegKey:([^\n]*)(.*)/\2|\1/
         s/.*\n//' file ...

```

Use the options `-s` to treat each input file separately and `-E` to simplify regexps.

Using alternation, extract the required fields into the hold buffer.

At the end of the file if the `PoweredBy` field does not contain `Kontakt` no further processing is required.

Otherwise, format the fields in the hold buffer into the required format and print the result.
### 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")

```