strugling with awk script need help to done this just need your suggestion or logic

Question

I have a sql file to filter the data

-- Edit this file by adding your SQL below each question.
-------------------------------------------------------------------------------

-------------------------------------------------------------
-- The following queries are based on the 1994 census data.
-------------------------------------------------------------

.read 1994
-census-summary-1.sql

-- 4. what is the average age of people from China?
select avg(age)
from census
where native_country ='China';

-- 5. what is the average age of people from Taiwan?
select avg(age)
from census
where native_country ='Taiwan';

-- 6. which native countries have "land" in their name?
select distinct(native_country)
from census
where native_country like '%land%';

--------------------------------------------------------------------------------------
-- The following queries are based on the courses-ddl.sql and courses-small.sql data
--------------------------------------------------------------------------------------

drop table census;
.read courses-ddl.sql
.read courses-small-1.sql

-- 11. what are the names of all students who have taken some course? Don't show duplicates.
select distinct(name)
from student
where tot_cred > 0;

-- 12. what are the names of departments that offer 4-credit courses? Don't list duplicates.
select distinct(dept_name)
from course
where credits=4;

-- 13. What are the names and IDs of all students who have received an A in a computer science class?
select distinct(name), id
from student natural join takes natural join course  
where dept_name="Comp. Sci." and grade="A";

if I run

./script.awk -v ID=6 file.sql

Note that the problem id is passed to the awk script as variable ID on the command line, like this: -v ID=6

How Can I get the result like Result :

select distinct(native_country) from census where native_country like '%land%';

Answer 1

With your shown samples and in GNU awk, please try following GNU awk code using its match function. Where id is an awk variable has value which you want to make sure should be checked in lines of your Input_file. Also I have used exit to get/print the very first match and get out of program to save some time/cycle, in case you have more than one matches then simply remove it from following code.

awk -v RS= -v id="6" '
match($0,/(\n|^)-- ([0-9]+)\.[^\n]*\n(select[^;]*;)/,arr) && arr[2]==id{
  gsub(/\n/,"",arr[3])
  print arr[3]
  exit
}
'  Input_file

Answer 2

One option with awk could be matching the start of the line with -- 6. where 6 is the ID.

Then move to the next line, and set a variable that the start of the part that you want to match is seen

Then print all lines that do not start with a space and are seen.

Set seen to 0 when encountering an "empty" line

Concatenate the lines that you want in the output as a single line, and at the end remove the trailing space.

gawk -v ID=6 '
match($0, "^-- "ID"\\.") {
  seen=1
  next
}
/^[[:space:]]*$/ {
  seen=0
}
seen {
  a = a $0 " "
}
END {
  sub(/ $/, "", a)
  print a
}
' file.sql

Or as a single line

gawk -v ID=6 'match($0,"^-- "ID"\\."){seen=1;next};/^[[:space:]]*$/{seen=0};seen{a=a$0" "};END{sub(/ $/,"",a);print a}' file.sql

Output

select distinct(native_country) from census where native_country like '%land%';

---

Another option with gnu awk setting the row separator to an "empty" line and using a regex with a capture group to match all lines after the initial -- ID match that do not start with a space

gawk -v ID=6 '
match($0, "\\n-- "ID"\\.[^\\n]*\\n(([^[:space:]][^\\n]*(\\n|$))*)", m) {
  gsub(/\n/, " ", m[1])
  print m[1]
}
' RS='^[[:space:]]*$' file