sert121/SpiderSQL huggingface dataset:
-- Language PostgreSQL
        -- Tables: 
             -- Table: rooms 
columns : [['room id', 'text'], ['room name', 'text'], ['beds', 'number'], ['bed type', 'text'], ['max occupancy', 'number'], ['base price', 'number'], ['decor', 'text']]

 -- Table: reservations 
columns : [['code', 'number'], ['room', 'text'], ['check in', 'text'], ['check out', 'text'], ['rate', 'number'], ['last name', 'text'], ['first name', 'text'], ['adults', 'number'], ['kids', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: Find the number of rooms for each bed type.
SQL: SELECT bedType ,  count(*) FROM Rooms GROUP BY bedType;
-- Language PostgreSQL
        -- Tables: 
             -- Table: rooms 
columns : [['room id', 'text'], ['room name', 'text'], ['beds', 'number'], ['bed type', 'text'], ['max occupancy', 'number'], ['base price', 'number'], ['decor', 'text']]

 -- Table: reservations 
columns : [['code', 'number'], ['room', 'text'], ['check in', 'text'], ['check out', 'text'], ['rate', 'number'], ['last name', 'text'], ['first name', 'text'], ['adults', 'number'], ['kids', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: What are the number of rooms for each bed type?
SQL: SELECT bedType ,  count(*) FROM Rooms GROUP BY bedType;
-- Language PostgreSQL
        -- Tables: 
             -- Table: rooms 
columns : [['room id', 'text'], ['room name', 'text'], ['beds', 'number'], ['bed type', 'text'], ['max occupancy', 'number'], ['base price', 'number'], ['decor', 'text']]

 -- Table: reservations 
columns : [['code', 'number'], ['room', 'text'], ['check in', 'text'], ['check out', 'text'], ['rate', 'number'], ['last name', 'text'], ['first name', 'text'], ['adults', 'number'], ['kids', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: Find the name of the room with the maximum occupancy.
SQL: SELECT roomName FROM Rooms ORDER BY maxOccupancy DESC LIMIT 1;
-- Language PostgreSQL
        -- Tables: 
             -- Table: rooms 
columns : [['room id', 'text'], ['room name', 'text'], ['beds', 'number'], ['bed type', 'text'], ['max occupancy', 'number'], ['base price', 'number'], ['decor', 'text']]

 -- Table: reservations 
columns : [['code', 'number'], ['room', 'text'], ['check in', 'text'], ['check out', 'text'], ['rate', 'number'], ['last name', 'text'], ['first name', 'text'], ['adults', 'number'], ['kids', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: What is the name of the room that can accommodate the most people?
SQL: SELECT roomName FROM Rooms ORDER BY maxOccupancy DESC LIMIT 1;
-- Language PostgreSQL
        -- Tables: 
             -- Table: rooms 
columns : [['room id', 'text'], ['room name', 'text'], ['beds', 'number'], ['bed type', 'text'], ['max occupancy', 'number'], ['base price', 'number'], ['decor', 'text']]

 -- Table: reservations 
columns : [['code', 'number'], ['room', 'text'], ['check in', 'text'], ['check out', 'text'], ['rate', 'number'], ['last name', 'text'], ['first name', 'text'], ['adults', 'number'], ['kids', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: Find the id and name of the most expensive base price room.
SQL: SELECT RoomId ,  roomName FROM Rooms ORDER BY basePrice DESC LIMIT 1;
-- Language PostgreSQL
        -- Tables: 
             -- Table: rooms 
columns : [['room id', 'text'], ['room name', 'text'], ['beds', 'number'], ['bed type', 'text'], ['max occupancy', 'number'], ['base price', 'number'], ['decor', 'text']]

 -- Table: reservations 
columns : [['code', 'number'], ['room', 'text'], ['check in', 'text'], ['check out', 'text'], ['rate', 'number'], ['last name', 'text'], ['first name', 'text'], ['adults', 'number'], ['kids', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: Which room has the highest base price?
SQL: SELECT RoomId ,  roomName FROM Rooms ORDER BY basePrice DESC LIMIT 1;
-- Language PostgreSQL
        -- Tables: 
             -- Table: rooms 
columns : [['room id', 'text'], ['room name', 'text'], ['beds', 'number'], ['bed type', 'text'], ['max occupancy', 'number'], ['base price', 'number'], ['decor', 'text']]

 -- Table: reservations 
columns : [['code', 'number'], ['room', 'text'], ['check in', 'text'], ['check out', 'text'], ['rate', 'number'], ['last name', 'text'], ['first name', 'text'], ['adults', 'number'], ['kids', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: List the type of bed and name of all traditional rooms.
SQL: SELECT roomName ,  bedType FROM Rooms WHERE decor = "traditional";
-- Language PostgreSQL
        -- Tables: 
             -- Table: rooms 
columns : [['room id', 'text'], ['room name', 'text'], ['beds', 'number'], ['bed type', 'text'], ['max occupancy', 'number'], ['base price', 'number'], ['decor', 'text']]

 -- Table: reservations 
columns : [['code', 'number'], ['room', 'text'], ['check in', 'text'], ['check out', 'text'], ['rate', 'number'], ['last name', 'text'], ['first name', 'text'], ['adults', 'number'], ['kids', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: What are the bed type and name of all the rooms with traditional decor?
SQL: SELECT roomName ,  bedType FROM Rooms WHERE decor = "traditional";
-- Language PostgreSQL
        -- Tables: 
             -- Table: rooms 
columns : [['room id', 'text'], ['room name', 'text'], ['beds', 'number'], ['bed type', 'text'], ['max occupancy', 'number'], ['base price', 'number'], ['decor', 'text']]

 -- Table: reservations 
columns : [['code', 'number'], ['room', 'text'], ['check in', 'text'], ['check out', 'text'], ['rate', 'number'], ['last name', 'text'], ['first name', 'text'], ['adults', 'number'], ['kids', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: Find the number of rooms with king bed for each decor type.
SQL: SELECT decor ,  count(*) FROM Rooms WHERE bedType = "King" GROUP BY decor;
-- Language PostgreSQL
        -- Tables: 
             -- Table: rooms 
columns : [['room id', 'text'], ['room name', 'text'], ['beds', 'number'], ['bed type', 'text'], ['max occupancy', 'number'], ['base price', 'number'], ['decor', 'text']]

 -- Table: reservations 
columns : [['code', 'number'], ['room', 'text'], ['check in', 'text'], ['check out', 'text'], ['rate', 'number'], ['last name', 'text'], ['first name', 'text'], ['adults', 'number'], ['kids', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: How many rooms have king beds? Report the number for each decor type.
SQL: SELECT decor ,  count(*) FROM Rooms WHERE bedType = "King" GROUP BY decor;
-- Language PostgreSQL
        -- Tables: 
             -- Table: rooms 
columns : [['room id', 'text'], ['room name', 'text'], ['beds', 'number'], ['bed type', 'text'], ['max occupancy', 'number'], ['base price', 'number'], ['decor', 'text']]

 -- Table: reservations 
columns : [['code', 'number'], ['room', 'text'], ['check in', 'text'], ['check out', 'text'], ['rate', 'number'], ['last name', 'text'], ['first name', 'text'], ['adults', 'number'], ['kids', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: Find the average and minimum price of the rooms in different decor.
SQL: SELECT decor , avg(basePrice) ,  min(basePrice) FROM Rooms GROUP BY decor;
-- Language PostgreSQL
        -- Tables: 
             -- Table: rooms 
columns : [['room id', 'text'], ['room name', 'text'], ['beds', 'number'], ['bed type', 'text'], ['max occupancy', 'number'], ['base price', 'number'], ['decor', 'text']]

 -- Table: reservations 
columns : [['code', 'number'], ['room', 'text'], ['check in', 'text'], ['check out', 'text'], ['rate', 'number'], ['last name', 'text'], ['first name', 'text'], ['adults', 'number'], ['kids', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: What is the average minimum and price of the rooms for each different decor.
SQL: SELECT decor , avg(basePrice) ,  min(basePrice) FROM Rooms GROUP BY decor;
-- Language PostgreSQL
        -- Tables: 
             -- Table: rooms 
columns : [['room id', 'text'], ['room name', 'text'], ['beds', 'number'], ['bed type', 'text'], ['max occupancy', 'number'], ['base price', 'number'], ['decor', 'text']]

 -- Table: reservations 
columns : [['code', 'number'], ['room', 'text'], ['check in', 'text'], ['check out', 'text'], ['rate', 'number'], ['last name', 'text'], ['first name', 'text'], ['adults', 'number'], ['kids', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: List the name of all rooms sorted by their prices.
SQL: SELECT roomName FROM Rooms ORDER BY basePrice;
-- Language PostgreSQL
        -- Tables: 
             -- Table: rooms 
columns : [['room id', 'text'], ['room name', 'text'], ['beds', 'number'], ['bed type', 'text'], ['max occupancy', 'number'], ['base price', 'number'], ['decor', 'text']]

 -- Table: reservations 
columns : [['code', 'number'], ['room', 'text'], ['check in', 'text'], ['check out', 'text'], ['rate', 'number'], ['last name', 'text'], ['first name', 'text'], ['adults', 'number'], ['kids', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: Sort all the rooms according to the price. Just report the room names.
SQL: SELECT roomName FROM Rooms ORDER BY basePrice;
-- Language PostgreSQL
        -- Tables: 
             -- Table: rooms 
columns : [['room id', 'text'], ['room name', 'text'], ['beds', 'number'], ['bed type', 'text'], ['max occupancy', 'number'], ['base price', 'number'], ['decor', 'text']]

 -- Table: reservations 
columns : [['code', 'number'], ['room', 'text'], ['check in', 'text'], ['check out', 'text'], ['rate', 'number'], ['last name', 'text'], ['first name', 'text'], ['adults', 'number'], ['kids', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: Find the number of rooms with price higher than 120 for different decor.
SQL: SELECT decor , count(*) FROM Rooms WHERE basePrice  >  120 GROUP BY decor;
-- Language PostgreSQL
        -- Tables: 
             -- Table: rooms 
columns : [['room id', 'text'], ['room name', 'text'], ['beds', 'number'], ['bed type', 'text'], ['max occupancy', 'number'], ['base price', 'number'], ['decor', 'text']]

 -- Table: reservations 
columns : [['code', 'number'], ['room', 'text'], ['check in', 'text'], ['check out', 'text'], ['rate', 'number'], ['last name', 'text'], ['first name', 'text'], ['adults', 'number'], ['kids', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: How many rooms cost more than 120, for each different decor?
SQL: SELECT decor , count(*) FROM Rooms WHERE basePrice  >  120 GROUP BY decor;
-- Language PostgreSQL
        -- Tables: 
             -- Table: rooms 
columns : [['room id', 'text'], ['room name', 'text'], ['beds', 'number'], ['bed type', 'text'], ['max occupancy', 'number'], ['base price', 'number'], ['decor', 'text']]

 -- Table: reservations 
columns : [['code', 'number'], ['room', 'text'], ['check in', 'text'], ['check out', 'text'], ['rate', 'number'], ['last name', 'text'], ['first name', 'text'], ['adults', 'number'], ['kids', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: For each bed type, find the average room price.
SQL: SELECT bedType ,  avg(basePrice) FROM Rooms GROUP BY bedType;
-- Language PostgreSQL
        -- Tables: 
             -- Table: rooms 
columns : [['room id', 'text'], ['room name', 'text'], ['beds', 'number'], ['bed type', 'text'], ['max occupancy', 'number'], ['base price', 'number'], ['decor', 'text']]

 -- Table: reservations 
columns : [['code', 'number'], ['room', 'text'], ['check in', 'text'], ['check out', 'text'], ['rate', 'number'], ['last name', 'text'], ['first name', 'text'], ['adults', 'number'], ['kids', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: What is the average base price of rooms, for each bed type?
SQL: SELECT bedType ,  avg(basePrice) FROM Rooms GROUP BY bedType;
-- Language PostgreSQL
        -- Tables: 
             -- Table: rooms 
columns : [['room id', 'text'], ['room name', 'text'], ['beds', 'number'], ['bed type', 'text'], ['max occupancy', 'number'], ['base price', 'number'], ['decor', 'text']]

 -- Table: reservations 
columns : [['code', 'number'], ['room', 'text'], ['check in', 'text'], ['check out', 'text'], ['rate', 'number'], ['last name', 'text'], ['first name', 'text'], ['adults', 'number'], ['kids', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: List the name of rooms with king or queen bed.
SQL: SELECT roomName FROM Rooms WHERE bedType  =  "King" OR bedType  =  "Queen";
-- Language PostgreSQL
        -- Tables: 
             -- Table: rooms 
columns : [['room id', 'text'], ['room name', 'text'], ['beds', 'number'], ['bed type', 'text'], ['max occupancy', 'number'], ['base price', 'number'], ['decor', 'text']]

 -- Table: reservations 
columns : [['code', 'number'], ['room', 'text'], ['check in', 'text'], ['check out', 'text'], ['rate', 'number'], ['last name', 'text'], ['first name', 'text'], ['adults', 'number'], ['kids', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: What are the names of rooms that have either king or queen bed?
SQL: SELECT roomName FROM Rooms WHERE bedType  =  "King" OR bedType  =  "Queen";
-- Language PostgreSQL
        -- Tables: 
             -- Table: rooms 
columns : [['room id', 'text'], ['room name', 'text'], ['beds', 'number'], ['bed type', 'text'], ['max occupancy', 'number'], ['base price', 'number'], ['decor', 'text']]

 -- Table: reservations 
columns : [['code', 'number'], ['room', 'text'], ['check in', 'text'], ['check out', 'text'], ['rate', 'number'], ['last name', 'text'], ['first name', 'text'], ['adults', 'number'], ['kids', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: How many different types of beds are there?
SQL: SELECT count(DISTINCT bedType) FROM Rooms;
-- Language PostgreSQL
        -- Tables: 
             -- Table: rooms 
columns : [['room id', 'text'], ['room name', 'text'], ['beds', 'number'], ['bed type', 'text'], ['max occupancy', 'number'], ['base price', 'number'], ['decor', 'text']]

 -- Table: reservations 
columns : [['code', 'number'], ['room', 'text'], ['check in', 'text'], ['check out', 'text'], ['rate', 'number'], ['last name', 'text'], ['first name', 'text'], ['adults', 'number'], ['kids', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: Find the number of distinct bed types available in this inn.
SQL: SELECT count(DISTINCT bedType) FROM Rooms;
-- Language PostgreSQL
        -- Tables: 
             -- Table: rooms 
columns : [['room id', 'text'], ['room name', 'text'], ['beds', 'number'], ['bed type', 'text'], ['max occupancy', 'number'], ['base price', 'number'], ['decor', 'text']]

 -- Table: reservations 
columns : [['code', 'number'], ['room', 'text'], ['check in', 'text'], ['check out', 'text'], ['rate', 'number'], ['last name', 'text'], ['first name', 'text'], ['adults', 'number'], ['kids', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: Find the name and id of the top 3 expensive rooms.
SQL: SELECT RoomId ,  roomName FROM Rooms ORDER BY basePrice DESC LIMIT 3;
-- Language PostgreSQL
        -- Tables: 
             -- Table: rooms 
columns : [['room id', 'text'], ['room name', 'text'], ['beds', 'number'], ['bed type', 'text'], ['max occupancy', 'number'], ['base price', 'number'], ['decor', 'text']]

 -- Table: reservations 
columns : [['code', 'number'], ['room', 'text'], ['check in', 'text'], ['check out', 'text'], ['rate', 'number'], ['last name', 'text'], ['first name', 'text'], ['adults', 'number'], ['kids', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: What are the name and id of the three highest priced rooms?
SQL: SELECT RoomId ,  roomName FROM Rooms ORDER BY basePrice DESC LIMIT 3;
-- Language PostgreSQL
        -- Tables: 
             -- Table: rooms 
columns : [['room id', 'text'], ['room name', 'text'], ['beds', 'number'], ['bed type', 'text'], ['max occupancy', 'number'], ['base price', 'number'], ['decor', 'text']]

 -- Table: reservations 
columns : [['code', 'number'], ['room', 'text'], ['check in', 'text'], ['check out', 'text'], ['rate', 'number'], ['last name', 'text'], ['first name', 'text'], ['adults', 'number'], ['kids', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: Find the name of rooms whose price is higher than the average price.
SQL: SELECT roomName FROM Rooms WHERE basePrice  >  ( SELECT avg(basePrice) FROM Rooms );
-- Language PostgreSQL
        -- Tables: 
             -- Table: rooms 
columns : [['room id', 'text'], ['room name', 'text'], ['beds', 'number'], ['bed type', 'text'], ['max occupancy', 'number'], ['base price', 'number'], ['decor', 'text']]

 -- Table: reservations 
columns : [['code', 'number'], ['room', 'text'], ['check in', 'text'], ['check out', 'text'], ['rate', 'number'], ['last name', 'text'], ['first name', 'text'], ['adults', 'number'], ['kids', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: What are the name of rooms that cost more than the average.
SQL: SELECT roomName FROM Rooms WHERE basePrice  >  ( SELECT avg(basePrice) FROM Rooms );
-- Language PostgreSQL
        -- Tables: 
             -- Table: rooms 
columns : [['room id', 'text'], ['room name', 'text'], ['beds', 'number'], ['bed type', 'text'], ['max occupancy', 'number'], ['base price', 'number'], ['decor', 'text']]

 -- Table: reservations 
columns : [['code', 'number'], ['room', 'text'], ['check in', 'text'], ['check out', 'text'], ['rate', 'number'], ['last name', 'text'], ['first name', 'text'], ['adults', 'number'], ['kids', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: Find the number of rooms that do not have any reservation.
SQL: SELECT count(*) FROM rooms WHERE roomid NOT IN (SELECT DISTINCT room FROM reservations)
-- Language PostgreSQL
        -- Tables: 
             -- Table: rooms 
columns : [['room id', 'text'], ['room name', 'text'], ['beds', 'number'], ['bed type', 'text'], ['max occupancy', 'number'], ['base price', 'number'], ['decor', 'text']]

 -- Table: reservations 
columns : [['code', 'number'], ['room', 'text'], ['check in', 'text'], ['check out', 'text'], ['rate', 'number'], ['last name', 'text'], ['first name', 'text'], ['adults', 'number'], ['kids', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: How many rooms have not had any reservation yet?
SQL: SELECT count(*) FROM rooms WHERE roomid NOT IN (SELECT DISTINCT room FROM reservations)
-- Language PostgreSQL
        -- Tables: 
             -- Table: rooms 
columns : [['room id', 'text'], ['room name', 'text'], ['beds', 'number'], ['bed type', 'text'], ['max occupancy', 'number'], ['base price', 'number'], ['decor', 'text']]

 -- Table: reservations 
columns : [['code', 'number'], ['room', 'text'], ['check in', 'text'], ['check out', 'text'], ['rate', 'number'], ['last name', 'text'], ['first name', 'text'], ['adults', 'number'], ['kids', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: Return the name and number of reservations made for each of the rooms.
SQL: SELECT T2.roomName ,  count(*) ,  T1.Room FROM Reservations AS T1 JOIN Rooms AS T2 ON T1.Room  =  T2.RoomId GROUP BY T1.Room
-- Language PostgreSQL
        -- Tables: 
             -- Table: rooms 
columns : [['room id', 'text'], ['room name', 'text'], ['beds', 'number'], ['bed type', 'text'], ['max occupancy', 'number'], ['base price', 'number'], ['decor', 'text']]

 -- Table: reservations 
columns : [['code', 'number'], ['room', 'text'], ['check in', 'text'], ['check out', 'text'], ['rate', 'number'], ['last name', 'text'], ['first name', 'text'], ['adults', 'number'], ['kids', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: For each room, find its name and the number of times reservations were made for it.
SQL: SELECT T2.roomName ,  count(*) ,  T1.Room FROM Reservations AS T1 JOIN Rooms AS T2 ON T1.Room  =  T2.RoomId GROUP BY T1.Room
-- Language PostgreSQL
        -- Tables: 
             -- Table: rooms 
columns : [['room id', 'text'], ['room name', 'text'], ['beds', 'number'], ['bed type', 'text'], ['max occupancy', 'number'], ['base price', 'number'], ['decor', 'text']]

 -- Table: reservations 
columns : [['code', 'number'], ['room', 'text'], ['check in', 'text'], ['check out', 'text'], ['rate', 'number'], ['last name', 'text'], ['first name', 'text'], ['adults', 'number'], ['kids', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: Find the names of rooms that have been reserved for more than 60 times.
SQL: SELECT T2.roomName FROM Reservations AS T1 JOIN Rooms AS T2 ON T1.Room  =  T2.RoomId GROUP BY T1.Room HAVING count(*)  >  60
-- Language PostgreSQL
        -- Tables: 
             -- Table: rooms 
columns : [['room id', 'text'], ['room name', 'text'], ['beds', 'number'], ['bed type', 'text'], ['max occupancy', 'number'], ['base price', 'number'], ['decor', 'text']]

 -- Table: reservations 
columns : [['code', 'number'], ['room', 'text'], ['check in', 'text'], ['check out', 'text'], ['rate', 'number'], ['last name', 'text'], ['first name', 'text'], ['adults', 'number'], ['kids', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: What are the names of rooms whose reservation frequency exceeds 60 times?
SQL: SELECT T2.roomName FROM Reservations AS T1 JOIN Rooms AS T2 ON T1.Room  =  T2.RoomId GROUP BY T1.Room HAVING count(*)  >  60
-- Language PostgreSQL
        -- Tables: 
             -- Table: rooms 
columns : [['room id', 'text'], ['room name', 'text'], ['beds', 'number'], ['bed type', 'text'], ['max occupancy', 'number'], ['base price', 'number'], ['decor', 'text']]

 -- Table: reservations 
columns : [['code', 'number'], ['room', 'text'], ['check in', 'text'], ['check out', 'text'], ['rate', 'number'], ['last name', 'text'], ['first name', 'text'], ['adults', 'number'], ['kids', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: Find the name of rooms whose base price is between 120 and 150.
SQL: SELECT roomname FROM rooms WHERE baseprice BETWEEN 120 AND 150
-- Language PostgreSQL
        -- Tables: 
             -- Table: rooms 
columns : [['room id', 'text'], ['room name', 'text'], ['beds', 'number'], ['bed type', 'text'], ['max occupancy', 'number'], ['base price', 'number'], ['decor', 'text']]

 -- Table: reservations 
columns : [['code', 'number'], ['room', 'text'], ['check in', 'text'], ['check out', 'text'], ['rate', 'number'], ['last name', 'text'], ['first name', 'text'], ['adults', 'number'], ['kids', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: Which rooms cost between 120 and 150? Give me the room names.
SQL: SELECT roomname FROM rooms WHERE baseprice BETWEEN 120 AND 150
-- Language PostgreSQL
        -- Tables: 
             -- Table: rooms 
columns : [['room id', 'text'], ['room name', 'text'], ['beds', 'number'], ['bed type', 'text'], ['max occupancy', 'number'], ['base price', 'number'], ['decor', 'text']]

 -- Table: reservations 
columns : [['code', 'number'], ['room', 'text'], ['check in', 'text'], ['check out', 'text'], ['rate', 'number'], ['last name', 'text'], ['first name', 'text'], ['adults', 'number'], ['kids', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: Find the name of rooms booked by some customers whose first name contains ROY.
SQL: SELECT T2.roomName FROM Reservations AS T1 JOIN Rooms AS T2 ON T1.Room  =  T2.RoomId WHERE firstname LIKE '%ROY%'
-- Language PostgreSQL
        -- Tables: 
             -- Table: rooms 
columns : [['room id', 'text'], ['room name', 'text'], ['beds', 'number'], ['bed type', 'text'], ['max occupancy', 'number'], ['base price', 'number'], ['decor', 'text']]

 -- Table: reservations 
columns : [['code', 'number'], ['room', 'text'], ['check in', 'text'], ['check out', 'text'], ['rate', 'number'], ['last name', 'text'], ['first name', 'text'], ['adults', 'number'], ['kids', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: What are the name of rooms booked by customers whose first name has "ROY" in part?
SQL: SELECT T2.roomName FROM Reservations AS T1 JOIN Rooms AS T2 ON T1.Room  =  T2.RoomId WHERE firstname LIKE '%ROY%'
-- Language PostgreSQL
        -- Tables: 
             -- Table: customer master index 
columns : [['master customer id', 'number'], ['cmi details', 'text']]

 -- Table: cmi cross references 
columns : [['cmi cross reference id', 'number'], ['master customer id', 'number'], ['source system code', 'text']]

 -- Table: council tax 
columns : [['council tax id', 'number'], ['cmi cross reference id', 'number']]

 -- Table: business rates 
columns : [['business rates id', 'number'], ['cmi cross reference id', 'number']]

 -- Table: benefits overpayments 
columns : [['council tax id', 'number'], ['cmi cross ref id', 'number']]

 -- Table: parking fines 
columns : [['council tax id', 'number'], ['cmi cross reference id', 'number']]

 -- Table: rent arrears 
columns : [['council tax id', 'number'], ['cmi cross reference id', 'number']]

 -- Table: electoral register 
columns : [['electoral register id', 'number'], ['cmi cross reference id', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: what are the details of the cmi masters that have the cross reference code 'Tax'?
SQL: SELECT T1.cmi_details FROM Customer_Master_Index AS T1 JOIN CMI_Cross_References AS T2 ON T1.master_customer_id  =  T2.master_customer_id WHERE T2.source_system_code  =  'Tax'
-- Language PostgreSQL
        -- Tables: 
             -- Table: customer master index 
columns : [['master customer id', 'number'], ['cmi details', 'text']]

 -- Table: cmi cross references 
columns : [['cmi cross reference id', 'number'], ['master customer id', 'number'], ['source system code', 'text']]

 -- Table: council tax 
columns : [['council tax id', 'number'], ['cmi cross reference id', 'number']]

 -- Table: business rates 
columns : [['business rates id', 'number'], ['cmi cross reference id', 'number']]

 -- Table: benefits overpayments 
columns : [['council tax id', 'number'], ['cmi cross ref id', 'number']]

 -- Table: parking fines 
columns : [['council tax id', 'number'], ['cmi cross reference id', 'number']]

 -- Table: rent arrears 
columns : [['council tax id', 'number'], ['cmi cross reference id', 'number']]

 -- Table: electoral register 
columns : [['electoral register id', 'number'], ['cmi cross reference id', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: What is the cmi cross reference id that is related to at least one council tax entry? List the cross reference id and source system code.
SQL: SELECT T1.cmi_cross_ref_id ,  T1.source_system_code FROM CMI_Cross_References AS T1 JOIN Council_Tax AS T2 ON T1.cmi_cross_ref_id  =  T2.cmi_cross_ref_id GROUP BY T1.cmi_cross_ref_id HAVING count(*)  >=  1
-- Language PostgreSQL
        -- Tables: 
             -- Table: customer master index 
columns : [['master customer id', 'number'], ['cmi details', 'text']]

 -- Table: cmi cross references 
columns : [['cmi cross reference id', 'number'], ['master customer id', 'number'], ['source system code', 'text']]

 -- Table: council tax 
columns : [['council tax id', 'number'], ['cmi cross reference id', 'number']]

 -- Table: business rates 
columns : [['business rates id', 'number'], ['cmi cross reference id', 'number']]

 -- Table: benefits overpayments 
columns : [['council tax id', 'number'], ['cmi cross ref id', 'number']]

 -- Table: parking fines 
columns : [['council tax id', 'number'], ['cmi cross reference id', 'number']]

 -- Table: rent arrears 
columns : [['council tax id', 'number'], ['cmi cross reference id', 'number']]

 -- Table: electoral register 
columns : [['electoral register id', 'number'], ['cmi cross reference id', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: How many business rates are related to each cmi cross reference? List cross reference id, master customer id and the n
SQL: SELECT T2.cmi_cross_ref_id ,  T2.master_customer_id ,  count(*) FROM Business_Rates AS T1 JOIN CMI_Cross_References AS T2 ON T1.cmi_cross_ref_id  =  T2.cmi_cross_ref_id GROUP BY T2.cmi_cross_ref_id
-- Language PostgreSQL
        -- Tables: 
             -- Table: customer master index 
columns : [['master customer id', 'number'], ['cmi details', 'text']]

 -- Table: cmi cross references 
columns : [['cmi cross reference id', 'number'], ['master customer id', 'number'], ['source system code', 'text']]

 -- Table: council tax 
columns : [['council tax id', 'number'], ['cmi cross reference id', 'number']]

 -- Table: business rates 
columns : [['business rates id', 'number'], ['cmi cross reference id', 'number']]

 -- Table: benefits overpayments 
columns : [['council tax id', 'number'], ['cmi cross ref id', 'number']]

 -- Table: parking fines 
columns : [['council tax id', 'number'], ['cmi cross reference id', 'number']]

 -- Table: rent arrears 
columns : [['council tax id', 'number'], ['cmi cross reference id', 'number']]

 -- Table: electoral register 
columns : [['electoral register id', 'number'], ['cmi cross reference id', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: What is the tax source system code related to the benefits and overpayments? List the code and the benifit id, order by benifit id.
SQL: SELECT T1.source_system_code ,  T2.council_tax_id FROM CMI_Cross_References AS T1 JOIN Benefits_Overpayments AS T2 ON T1.cmi_cross_ref_id  =  T2.cmi_cross_ref_id ORDER BY T2.council_tax_id
-- Language PostgreSQL
        -- Tables: 
             -- Table: customer master index 
columns : [['master customer id', 'number'], ['cmi details', 'text']]

 -- Table: cmi cross references 
columns : [['cmi cross reference id', 'number'], ['master customer id', 'number'], ['source system code', 'text']]

 -- Table: council tax 
columns : [['council tax id', 'number'], ['cmi cross reference id', 'number']]

 -- Table: business rates 
columns : [['business rates id', 'number'], ['cmi cross reference id', 'number']]

 -- Table: benefits overpayments 
columns : [['council tax id', 'number'], ['cmi cross ref id', 'number']]

 -- Table: parking fines 
columns : [['council tax id', 'number'], ['cmi cross reference id', 'number']]

 -- Table: rent arrears 
columns : [['council tax id', 'number'], ['cmi cross reference id', 'number']]

 -- Table: electoral register 
columns : [['electoral register id', 'number'], ['cmi cross reference id', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: Wat is the tax source system code and master customer id of the taxes related to each parking fine id?
SQL: SELECT T1.source_system_code ,  T1.master_customer_id ,  T2.council_tax_id FROM CMI_Cross_References AS T1 JOIN Parking_Fines AS T2 ON T1.cmi_cross_ref_id  =  T2.cmi_cross_ref_id
-- Language PostgreSQL
        -- Tables: 
             -- Table: customer master index 
columns : [['master customer id', 'number'], ['cmi details', 'text']]

 -- Table: cmi cross references 
columns : [['cmi cross reference id', 'number'], ['master customer id', 'number'], ['source system code', 'text']]

 -- Table: council tax 
columns : [['council tax id', 'number'], ['cmi cross reference id', 'number']]

 -- Table: business rates 
columns : [['business rates id', 'number'], ['cmi cross reference id', 'number']]

 -- Table: benefits overpayments 
columns : [['council tax id', 'number'], ['cmi cross ref id', 'number']]

 -- Table: parking fines 
columns : [['council tax id', 'number'], ['cmi cross reference id', 'number']]

 -- Table: rent arrears 
columns : [['council tax id', 'number'], ['cmi cross reference id', 'number']]

 -- Table: electoral register 
columns : [['electoral register id', 'number'], ['cmi cross reference id', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: What are the renting arrears tax ids related to the customer master index whose detail is not 'Schmidt, Kertzmann and Lubowitz'?
SQL: SELECT T1.council_tax_id FROM Rent_Arrears AS T1 JOIN CMI_Cross_References AS T2 ON T1.cmi_cross_ref_id  =  T2.cmi_cross_ref_id JOIN Customer_Master_Index AS T3 ON T3.master_customer_id  =  T2.master_customer_id WHERE T3.cmi_details != 'Schmidt ,  Kertzmann and Lubowitz'
-- Language PostgreSQL
        -- Tables: 
             -- Table: customer master index 
columns : [['master customer id', 'number'], ['cmi details', 'text']]

 -- Table: cmi cross references 
columns : [['cmi cross reference id', 'number'], ['master customer id', 'number'], ['source system code', 'text']]

 -- Table: council tax 
columns : [['council tax id', 'number'], ['cmi cross reference id', 'number']]

 -- Table: business rates 
columns : [['business rates id', 'number'], ['cmi cross reference id', 'number']]

 -- Table: benefits overpayments 
columns : [['council tax id', 'number'], ['cmi cross ref id', 'number']]

 -- Table: parking fines 
columns : [['council tax id', 'number'], ['cmi cross reference id', 'number']]

 -- Table: rent arrears 
columns : [['council tax id', 'number'], ['cmi cross reference id', 'number']]

 -- Table: electoral register 
columns : [['electoral register id', 'number'], ['cmi cross reference id', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: What are the register ids of electoral registries that have the cross reference source system code 'Electoral' or 'Tax'?
SQL: SELECT T1.electoral_register_id FROM Electoral_Register AS T1 JOIN CMI_Cross_References AS T2 ON T1.cmi_cross_ref_id  =  T2.cmi_cross_ref_id WHERE T2.source_system_code  =  'Electoral' OR T2.source_system_code  =  'Tax'
-- Language PostgreSQL
        -- Tables: 
             -- Table: customer master index 
columns : [['master customer id', 'number'], ['cmi details', 'text']]

 -- Table: cmi cross references 
columns : [['cmi cross reference id', 'number'], ['master customer id', 'number'], ['source system code', 'text']]

 -- Table: council tax 
columns : [['council tax id', 'number'], ['cmi cross reference id', 'number']]

 -- Table: business rates 
columns : [['business rates id', 'number'], ['cmi cross reference id', 'number']]

 -- Table: benefits overpayments 
columns : [['council tax id', 'number'], ['cmi cross ref id', 'number']]

 -- Table: parking fines 
columns : [['council tax id', 'number'], ['cmi cross reference id', 'number']]

 -- Table: rent arrears 
columns : [['council tax id', 'number'], ['cmi cross reference id', 'number']]

 -- Table: electoral register 
columns : [['electoral register id', 'number'], ['cmi cross reference id', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: How many different source system code for the cmi cross references are there?
SQL: SELECT count(DISTINCT source_system_code) FROM CMI_cross_references
-- Language PostgreSQL
        -- Tables: 
             -- Table: customer master index 
columns : [['master customer id', 'number'], ['cmi details', 'text']]

 -- Table: cmi cross references 
columns : [['cmi cross reference id', 'number'], ['master customer id', 'number'], ['source system code', 'text']]

 -- Table: council tax 
columns : [['council tax id', 'number'], ['cmi cross reference id', 'number']]

 -- Table: business rates 
columns : [['business rates id', 'number'], ['cmi cross reference id', 'number']]

 -- Table: benefits overpayments 
columns : [['council tax id', 'number'], ['cmi cross ref id', 'number']]

 -- Table: parking fines 
columns : [['council tax id', 'number'], ['cmi cross reference id', 'number']]

 -- Table: rent arrears 
columns : [['council tax id', 'number'], ['cmi cross reference id', 'number']]

 -- Table: electoral register 
columns : [['electoral register id', 'number'], ['cmi cross reference id', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: List all information about customer master index, and sort them by details in descending order.
SQL: SELECT * FROM customer_master_index ORDER BY cmi_details DESC