Text-to-SQL on Qwen3 8B: fine-tuning report

Schema: CREATE TABLE `runs` ( `run_at` TEXT NOT NULL, `max_rowid` INTEGER NOT NULL, `reviews_added` INTEGER NOT NULL ); CREATE TABLE `podcasts` ( `podcast_id` TEXT PRIMARY KEY, `itunes_id` INTEGER NOT NULL, `slug` TEXT NOT NULL, `itunes_url` TEXT NOT NULL, `title` TEXT NOT NULL ); CREATE TABLE `reviews` ( `podcast_id` TEXT NOT NULL CONSTRAINT reviews_podcasts_podcast_id_fk REFERENCES podcasts, `title` TEXT NOT NULL, `content` TEXT NOT NULL, `rating` INTEGER NOT NULL, `author_id` TEXT NOT NULL, `created_at` TEXT NOT NULL ); CREATE TABLE `categories` ( `podcast_id` TEXT NOT NULL CONSTRAINT categories_podcasts_podcast_id_fk REFERENCES podcasts, `category` TEXT NOT NULL, CONSTRAINT `PRIMARY` PRIMARY KEY (podcast_id, category) ); Question: What is the rating and category of the podcast entitled Sitcomadon? Hint: entitled refers to title; 'Sitcomadon' is the title of podcast

SELECT DISTINCT T3.rating, T1.category FROM categories AS T1 INNER JOIN podcasts AS T2 ON T2.podcast_id = T1.podcast_id INNER JOIN reviews AS T3 ON T3.podcast_id = T2.podcast_id WHERE T2.title = 'Sitcomadon'

SELECT r.rating, c.category FROM reviews r JOIN podcasts p ON r.podcast_id = p.podcast_id JOIN categories c ON r.podcast_id = c.podcast_id WHERE p.title = 'Sitcomadon';

SELECT T2.rating, T1.category FROM categories AS T1 INNER JOIN reviews AS T2 ON T1.podcast_id = T2.podcast_id WHERE T2.title = 'Sitcomadon'

Schema: CREATE TABLE `country` ( `origin` INTEGER PRIMARY KEY, `country` TEXT ); CREATE TABLE `price` ( `ID` INTEGER PRIMARY KEY, `price` REAL ); CREATE TABLE `data` ( `ID` INTEGER PRIMARY KEY, `mpg` REAL, `cylinders` INTEGER, `displacement` REAL, `horsepower` INTEGER, `weight` INTEGER, `acceleration` REAL, `model` INTEGER, `car_name` TEXT, FOREIGN KEY (ID) REFERENCES price(ID) ); CREATE TABLE `production` ( `ID` INTEGER, `model_year` INTEGER, `country` INTEGER, PRIMARY KEY (ID, model_year), FOREIGN KEY (country) REFERENCES country(origin), FOREIGN KEY (ID) REFERENCES data(ID), FOREIGN KEY (ID) REFERENCES price(ID) ); Question: Which is the origin country of the $44274.40748 car? Hint: origin country refers to country; the $44274.40748 car refers to price = 44274.40748

SELECT T3.country FROM price AS T1 INNER JOIN production AS T2 ON T1.ID = T2.ID INNER JOIN country AS T3 ON T3.origin = T2.country WHERE T1.price = 44274.40748

SELECT country.country FROM price JOIN data ON price.ID = data.ID JOIN production ON data.ID = production.ID JOIN country ON production.country = country.origin WHERE price.price = 44274.40748;

SELECT T3.country FROM price AS T1 INNER JOIN production AS T2 ON T1.ID = T2.ID INNER JOIN country AS T3 ON T3.origin = T2.country WHERE T1.price = 44274.40748

Schema: CREATE TABLE `offices` ( `officeCode` TEXT NOT NULL PRIMARY KEY, `city` TEXT NOT NULL, `phone` TEXT NOT NULL, `addressLine1` TEXT NOT NULL, `addressLine2` TEXT, `state` TEXT, `country` TEXT NOT NULL, `postalCode` TEXT NOT NULL, `territory` TEXT NOT NULL ); CREATE TABLE `employees` ( `employeeNumber` INTEGER NOT NULL PRIMARY KEY, `lastName` TEXT NOT NULL, `firstName` TEXT NOT NULL, `extension` TEXT NOT NULL, `email` TEXT NOT NULL, `officeCode` TEXT NOT NULL, `reportsTo` INTEGER, `jobTitle` TEXT NOT NULL, FOREIGN KEY (officeCode) REFERENCES offices(officeCode), FOREIGN KEY (reportsTo) REFERENCES employees(employeeNumber) ); CREATE TABLE `customers` ( `customerNumber` INTEGER NOT NULL PRIMARY KEY, `customerName` TEXT NOT NULL, `contactLastName` TEXT NOT NULL, `contactFirstName` TEXT NOT NULL, `phone` TEXT NOT NULL, `addressLine1` TEXT NOT NULL, `addressLine2` TEXT, `city` TEXT NOT NULL, `state` TEXT, `postalCode` TEXT, `country` TEXT NOT NULL, `salesRepEmployeeNumber` INTEGER, `creditLimit` REAL, FOREIGN KEY (salesRepEmployeeNumber) REFERENCES employees(employeeNumber) ); CREATE TABLE `orders` ( `orderNumber` INTEGER NOT NULL PRIMARY KEY, `orderDate` DATE NOT NULL, `requiredDate` DATE NOT NULL, `shippedDate` DATE, `status` TEXT NOT NULL, `comments` TEXT, `customerNumber` INTEGER NOT NULL, FOREIGN KEY (customerNumber) REFERENCES customers(customerNumber) ); CREATE TABLE `payments` ( `customerNumber` INTEGER NOT NULL, `checkNumber` TEXT NOT NULL, `paymentDate` DATE NOT NULL, `amount` REAL NOT NULL, PRIMARY KEY (customerNumber, checkNumber), FOREIGN KEY (customerNumber) REFERENCES customers(customerNumber) ); CREATE TABLE `productlines` ( `productLine` TEXT NOT NULL PRIMARY KEY, `textDescription` TEXT, `htmlDescription` TEXT, `image` BLOB ); CREATE TABLE `products` ( `productCode` TEXT NOT NULL PRIMARY KEY, `productName` TEXT NOT NULL, `productLine` TEXT NOT NULL, `productScale` TEXT NOT NULL, `productVendor` TEXT NOT NULL, `productDescription` TEXT NOT NULL, `quantityInStock` INTEGER NOT NULL, `buyPrice` REAL NOT NULL, `MSRP` REAL NOT NULL, FOREIGN KEY (productLine) REFERENCES productlines(productLine) ); CREATE TABLE `orderdetails` ( `orderNumber` INTEGER NOT NULL REFERENCES orders, `productCode` TEXT NOT NULL REFERENCES products, `quantityOrdered` INTEGER NOT NULL, `priceEach` REAL NOT NULL, `orderLineNumber` INTEGER NOT NULL, PRIMARY KEY (orderNumber, productCode) ); Question: State the email of those who are staff of Murphy Diane whose number is 1002 and living in San Francisco Hint: staff of refers to reportsTO; San Francisco is a city;

SELECT T1.email FROM employees AS T1 INNER JOIN offices AS T2 ON T1.officeCode = T2.officeCode WHERE T1.reportsTo = 1002 AND T2.city = 'San Francisco'

SELECT email FROM employees WHERE reportsTo = 1002 AND officeCode IN ( SELECT officeCode FROM offices WHERE city = 'San Francisco' );

SELECT T1.email FROM employees AS T1 INNER JOIN offices AS T2 ON T1.officeCode = T2.officeCode WHERE T1.reportsTo = 1002 AND T2.city = 'San Francisco'

Schema: CREATE TABLE `divisions` ( `division` TEXT NOT NULL PRIMARY KEY, `name` TEXT, `country` TEXT ); CREATE TABLE `matchs` ( `Div` TEXT, `Date` DATE, `HomeTeam` TEXT, `AwayTeam` TEXT, `FTHG` INTEGER, `FTAG` INTEGER, `FTR` TEXT, `season` INTEGER, FOREIGN KEY (Div) REFERENCES divisions(division) ); Question: How many teams that played in the 2012 season belong to any of the English divisions and what percentage play in each of the divisions? Hint: matches = Div

SELECT ( SELECT COUNT(T1.Div) AS total FROM matchs T1 INNER JOIN divisions T2 ON T2.division = T1.Div WHERE T2.country = 'England' AND T1.season = 2012 ) AS num , CASE WHEN 1 THEN T.result END AS percentage FROM ( SELECT 100.0 * COUNT(T1.Div) / ( SELECT COUNT(T1.Div) FROM matchs T1 INNER JOIN divisions T2 ON T2.division = T1.Div WHERE T2.country = 'England' AND T1.season = 2012 ) AS result FROM matchs T1 INNER JOIN divisions T2 ON T2.division = T1.Div WHERE T2.country = 'England' AND T1.season = 2012 GROUP BY T2.division ) AS T

SELECT divisions.division, COUNT(DISTINCT matchs.HomeTeam) + COUNT(DISTINCT matchs.AwayTeam) AS total_teams, ROUND(100.0 * (COUNT(DISTINCT matchs.HomeTeam) + COUNT(DISTINCT matchs.AwayTeam)) / (SELECT COUNT(DISTINCT HomeTeam) + COUNT(DISTINCT AwayTeam) FROM matchs WHERE season = 2012), 2) AS percentage FROM divisions JOIN matchs ON divisions.division = matchs.Div WHERE matchs.season = 2012 GROUP BY divisions.division;

SELECT COUNT(T1.HomeTeam) AS num_teams, CAST(SUM(CASE WHEN T2.division = 'EPL' THEN 1 ELSE 0 END) AS REAL) * 100 / COUNT(T1.HomeTeam) AS percentage FROM matchs AS T1 INNER JOIN divisions AS T2 ON T1.Div = T2.division WHERE T1.season = 2012

Schema: CREATE TABLE `address_status` ( `status_id` INTEGER PRIMARY KEY, `address_status` TEXT ); CREATE TABLE `author` ( `author_id` INTEGER PRIMARY KEY, `author_name` TEXT ); CREATE TABLE `book_language` ( `language_id` INTEGER PRIMARY KEY, `language_code` TEXT, `language_name` TEXT ); CREATE TABLE `country` ( `country_id` INTEGER PRIMARY KEY, `country_name` TEXT ); CREATE TABLE `address` ( `address_id` INTEGER PRIMARY KEY, `street_number` TEXT, `street_name` TEXT, `city` TEXT, `country_id` INTEGER, FOREIGN KEY (country_id) REFERENCES country(country_id) ); CREATE TABLE `customer` ( `customer_id` INTEGER PRIMARY KEY, `first_name` TEXT, `last_name` TEXT, `email` TEXT ); CREATE TABLE `customer_address` ( `customer_id` INTEGER, `address_id` INTEGER, `status_id` INTEGER, PRIMARY KEY (customer_id, address_id), FOREIGN KEY (address_id) REFERENCES address(address_id), FOREIGN KEY (customer_id) REFERENCES customer(customer_id) ); CREATE TABLE `order_status` ( `status_id` INTEGER PRIMARY KEY, `status_value` TEXT ); CREATE TABLE `publisher` ( `publisher_id` INTEGER PRIMARY KEY, `publisher_name` TEXT ); CREATE TABLE `book` ( `book_id` INTEGER PRIMARY KEY, `title` TEXT, `isbn13` TEXT, `language_id` INTEGER, `num_pages` INTEGER, `publication_date` DATE, `publisher_id` INTEGER, FOREIGN KEY (language_id) REFERENCES book_language(language_id), FOREIGN KEY (publisher_id) REFERENCES publisher(publisher_id) ); CREATE TABLE `book_author` ( `book_id` INTEGER, `author_id` INTEGER, PRIMARY KEY (book_id, author_id), FOREIGN KEY (author_id) REFERENCES author(author_id), FOREIGN KEY (book_id) REFERENCES book(book_id) ); CREATE TABLE `shipping_method` ( `method_id` INTEGER PRIMARY KEY, `method_name` TEXT, `cost` REAL ); CREATE TABLE `cust_order` ( `order_id` INTEGER PRIMARY KEY AUTOINCREMENT, `order_date` DATETIME, `customer_id` INTEGER REFERENCES customer, `shipping_method_id` INTEGER REFERENCES shipping_method, `dest_address_id` INTEGER REFERENCES address ); CREATE TABLE `order_history` ( `history_id` INTEGER PRIMARY KEY AUTOINCREMENT, `order_id` INTEGER REFERENCES cust_order, `status_id` INTEGER REFERENCES order_status, `status_date` DATETIME ); CREATE TABLE `order_line` ( `line_id` INTEGER PRIMARY KEY AUTOINCREMENT, `order_id` INTEGER REFERENCES cust_order, `book_id` INTEGER REFERENCES book, `price` REAL ); Question: How many books did A.R. Braunmuller write? Hint: "A.R. Braunmuller" is the author_name

SELECT COUNT(*) FROM author AS T1 INNER JOIN book_author AS T2 ON T1.author_id = T2.author_id WHERE T1.author_name = 'A.R. Braunmuller'

SELECT COUNT(*) FROM book JOIN book_author ON book.book_id = book_author.book_id JOIN author ON book_author.author_id = author.author_id WHERE author.author_name = 'A.R. Braunmuller';

SELECT COUNT(*) FROM book AS T1 INNER JOIN book_author AS T2 ON T1.book_id = T2.book_id INNER JOIN author AS T3 ON T3.author_id = T2.author_id WHERE T3.author_name = 'A.R. Braunmuller'

Schema: CREATE TABLE `City` ( `ID` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, `Name` TEXT NOT NULL DEFAULT '', `CountryCode` TEXT NOT NULL DEFAULT '', `District` TEXT NOT NULL DEFAULT '', `Population` INTEGER NOT NULL DEFAULT 0, FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`) ); CREATE TABLE `Country` ( `Code` TEXT NOT NULL DEFAULT '', `Name` TEXT NOT NULL DEFAULT '', `Continent` TEXT NOT NULL DEFAULT 'Asia', `Region` TEXT NOT NULL DEFAULT '', `SurfaceArea` REAL NOT NULL DEFAULT 0.00, `IndepYear` INTEGER DEFAULT NULL, `Population` INTEGER NOT NULL DEFAULT 0, `LifeExpectancy` REAL DEFAULT NULL, `GNP` REAL DEFAULT NULL, `GNPOld` REAL DEFAULT NULL, `LocalName` TEXT NOT NULL DEFAULT '', `GovernmentForm` TEXT NOT NULL DEFAULT '', `HeadOfState` TEXT DEFAULT NULL, `Capital` INTEGER DEFAULT NULL, `Code2` TEXT NOT NULL DEFAULT '', PRIMARY KEY (`Code`) ); CREATE TABLE `CountryLanguage` ( `CountryCode` TEXT NOT NULL DEFAULT '', `Language` TEXT NOT NULL DEFAULT '', `IsOfficial` TEXT NOT NULL DEFAULT 'F', `Percentage` REAL NOT NULL DEFAULT 0.0, PRIMARY KEY (`CountryCode`,`Language`), FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`) ); Question: What are the districts that belong to the country with the lowest surface area? Hint: lowest surface area refers to MIN(SurfaceArea);

SELECT T1.District FROM City AS T1 INNER JOIN Country AS T2 ON T1.CountryCode = T2.Code ORDER BY T2.SurfaceArea ASC LIMIT 1

SELECT District FROM City WHERE CountryCode = ( SELECT Code FROM Country ORDER BY SurfaceArea ASC LIMIT 1 );

SELECT T1.District FROM City AS T1 INNER JOIN Country AS T2 ON T1.CountryCode = T2.Code WHERE T2.SurfaceArea = ( SELECT MIN(SurfaceArea) FROM Country )

Schema: CREATE TABLE `Country` ( `CountryCode` TEXT NOT NULL PRIMARY KEY, `ShortName` TEXT, `TableName` TEXT, `LongName` TEXT, `Alpha2Code` TEXT, `CurrencyUnit` TEXT, `SpecialNotes` TEXT, `Region` TEXT, `IncomeGroup` TEXT, `Wb2Code` TEXT, `NationalAccountsBaseYear` TEXT, `NationalAccountsReferenceYear` TEXT, `SnaPriceValuation` TEXT, `LendingCategory` TEXT, `OtherGroups` TEXT, `SystemOfNationalAccounts` TEXT, `AlternativeConversionFactor` TEXT, `PppSurveyYear` TEXT, `BalanceOfPaymentsManualInUse` TEXT, `ExternalDebtReportingStatus` TEXT, `SystemOfTrade` TEXT, `GovernmentAccountingConcept` TEXT, `ImfDataDisseminationStandard` TEXT, `LatestPopulationCensus` TEXT, `LatestHouseholdSurvey` TEXT, `SourceOfMostRecentIncomeAndExpenditureData` TEXT, `VitalRegistrationComplete` TEXT, `LatestAgriculturalCensus` TEXT, `LatestIndustrialData` INTEGER, `LatestTradeData` INTEGER, `LatestWaterWithdrawalData` INTEGER ); CREATE TABLE `Series` ( `SeriesCode` TEXT NOT NULL PRIMARY KEY, `Topic` TEXT, `IndicatorName` TEXT, `ShortDefinition` TEXT, `LongDefinition` TEXT, `UnitOfMeasure` TEXT, `Periodicity` TEXT, `BasePeriod` TEXT, `OtherNotes` INTEGER, `AggregationMethod` TEXT, `LimitationsAndExceptions` TEXT, `NotesFromOriginalSource` TEXT, `GeneralComments` TEXT, `Source` TEXT, `StatisticalConceptAndMethodology` TEXT, `DevelopmentRelevance` TEXT, `RelatedSourceLinks` TEXT, `OtherWebLinks` INTEGER, `RelatedIndicators` INTEGER, `LicenseType` TEXT ); CREATE TABLE `CountryNotes` ( `Countrycode` TEXT NOT NULL, `Seriescode` TEXT NOT NULL, `Description` TEXT, PRIMARY KEY (Countrycode, Seriescode), FOREIGN KEY (Seriescode) REFERENCES Series(SeriesCode), FOREIGN KEY (Countrycode) REFERENCES Country(CountryCode) ); CREATE TABLE `Footnotes` ( `Countrycode` TEXT NOT NULL, `Seriescode` TEXT NOT NULL, `Year` TEXT, `Description` TEXT, PRIMARY KEY (Countrycode, Seriescode, Year), FOREIGN KEY (Seriescode) REFERENCES Series(SeriesCode), FOREIGN KEY (Countrycode) REFERENCES Country(CountryCode) ); CREATE TABLE `Indicators` ( `CountryName` TEXT, `CountryCode` TEXT NOT NULL, `IndicatorName` TEXT, `IndicatorCode` TEXT NOT NULL, `Year` INTEGER NOT NULL, `Value` INTEGER, PRIMARY KEY (CountryCode, IndicatorCode, Year), FOREIGN KEY (CountryCode) REFERENCES Country(CountryCode) ); CREATE TABLE `SeriesNotes` ( `Seriescode` TEXT NOT NULL, `Year` TEXT NOT NULL, `Description` TEXT, PRIMARY KEY (Seriescode, Year), FOREIGN KEY (Seriescode) REFERENCES Series(SeriesCode) ); Question: What are the indicator codes for the Republic of Albania in the year 1960? Hint: the Republic of Albania refers to LongName = 'Republic of Albania'; in the year 1960 refers to Year = '1960'

SELECT DISTINCT T1.IndicatorCode FROM Indicators AS T1 INNER JOIN Country AS T2 ON T1.CountryCode = T2.CountryCode WHERE T1.Year = 1960 AND T2.LongName = 'Republic of Albania'

SELECT IndicatorCode FROM Indicators WHERE CountryCode = ( SELECT CountryCode FROM Country WHERE LongName = 'Republic of Albania' ) AND Year = '1960';

SELECT DISTINCT T2.IndicatorCode FROM Country AS T1 INNER JOIN Indicators AS T2 ON T1.CountryCode = T2.CountryCode WHERE T1.LongName = 'Republic of Albania' AND T2.Year = 1960

Schema: CREATE TABLE `Customers` ( `CustomerID` INTEGER CONSTRAINT Customers_pk PRIMARY KEY, `Customer Names` TEXT ); CREATE TABLE `Products` ( `ProductID` INTEGER CONSTRAINT Products_pk PRIMARY KEY, `Product Name` TEXT ); CREATE TABLE `Regions` ( `StateCode` TEXT CONSTRAINT Regions_pk PRIMARY KEY, `State` TEXT, `Region` TEXT ); CREATE TABLE `Sales Team` ( `SalesTeamID` INTEGER CONSTRAINT `Sales Team_pk` PRIMARY KEY, `Sales Team` TEXT, `Region` TEXT ); CREATE TABLE `Store Locations` ( `StoreID` INTEGER CONSTRAINT `Store Locations_pk` PRIMARY KEY, `City Name` TEXT, `County` TEXT, `StateCode` TEXT CONSTRAINT `Store Locations_Regions_StateCode_fk` REFERENCES Regions(StateCode), `State` TEXT, `Type` TEXT, `Latitude` REAL, `Longitude` REAL, `AreaCode` INTEGER, `Population` INTEGER, `Household Income` INTEGER, `Median Income` INTEGER, `Land Area` INTEGER, `Water Area` INTEGER, `Time Zone` TEXT ); CREATE TABLE `Sales Orders` ( `OrderNumber` TEXT CONSTRAINT `Sales Orders_pk` PRIMARY KEY, `Sales Channel` TEXT, `WarehouseCode` TEXT, `ProcuredDate` TEXT, `OrderDate` TEXT, `ShipDate` TEXT, `DeliveryDate` TEXT, `CurrencyCode` TEXT, `_SalesTeamID` INTEGER CONSTRAINT `Sales Orders_Sales Team_SalesTeamID_fk` REFERENCES `Sales Team`(SalesTeamID), `_CustomerID` INTEGER CONSTRAINT `Sales Orders_Customers_CustomerID_fk` REFERENCES Customers(CustomerID), `_StoreID` INTEGER CONSTRAINT `Sales Orders_Store Locations_StoreID_fk` REFERENCES `Store Locations`(StoreID), `_ProductID` INTEGER CONSTRAINT `Sales Orders_Products_ProductID_fk` REFERENCES Products(ProductID), `Order Quantity` INTEGER, `Discount Applied` REAL, `Unit Price` TEXT, `Unit Cost` TEXT ); Question: Write down the store IDs and region of the state "Michigan". Hint: "Michigan" is the State

SELECT DISTINCT T2.StoreID, T1.Region FROM Regions AS T1 INNER JOIN `Store Locations` AS T2 ON T2.StateCode = T1.StateCode WHERE T2.State = 'Michigan'

SELECT `StoreID`, `StateCode` FROM `Store Locations` WHERE `State` = 'Michigan';

SELECT T1.StoreID, T2.Region FROM `Store Locations` AS T1 INNER JOIN Regions AS T2 ON T1.StateCode = T2.StateCode WHERE T2.State = 'Michigan'

Schema: CREATE TABLE `course` ( `course_id` INTEGER CONSTRAINT course_pk PRIMARY KEY, `name` TEXT, `credit` INTEGER, `diff` INTEGER ); CREATE TABLE `prof` ( `prof_id` INTEGER CONSTRAINT prof_pk PRIMARY KEY, `gender` TEXT, `first_name` TEXT, `last_name` TEXT, `email` TEXT, `popularity` INTEGER, `teachingability` INTEGER, `graduate_from` TEXT ); CREATE TABLE `RA` ( `student_id` INTEGER, `capability` INTEGER, `prof_id` INTEGER, `salary` TEXT, PRIMARY KEY (student_id, prof_id), FOREIGN KEY (prof_id) REFERENCES prof(prof_id), FOREIGN KEY (student_id) REFERENCES student(student_id) ); CREATE TABLE `registration` ( `course_id` INTEGER, `student_id` INTEGER, `grade` TEXT, `sat` INTEGER, PRIMARY KEY (course_id, student_id), FOREIGN KEY (course_id) REFERENCES course(course_id), FOREIGN KEY (student_id) REFERENCES student(student_id) ); CREATE TABLE `student` ( `student_id` INTEGER PRIMARY KEY, `f_name` TEXT, `l_name` TEXT, `phone_number` TEXT, `email` TEXT, `intelligence` INTEGER, `gpa` REAL, `type` TEXT ); Question: Among the easiest courses, what is the name of the course where most students got an A? Hint: diff refers to difficulty; the easiest courses refers to diff = 1; A refers to an excellent grade in which grade = 'A' for the course;

SELECT T2.name FROM registration AS T1 INNER JOIN course AS T2 ON T1.course_id = T2.course_id WHERE T1.grade = 'A' AND T2.diff = 1 GROUP BY T2.name ORDER BY COUNT(T1.student_id) DESC LIMIT 1

SELECT name FROM course WHERE diff = 1 AND course_id IN ( SELECT course_id FROM registration WHERE grade = 'A' GROUP BY course_id ORDER BY COUNT(student_id) DESC LIMIT 1 );

SELECT T1.name FROM course AS T1 INNER JOIN registration AS T2 ON T1.course_id = T2.course_id WHERE T1.diff = 1 AND T2.grade = 'A' GROUP BY T1.name ORDER BY COUNT(T2.student_id) DESC LIMIT 1

Schema: CREATE TABLE `Episode` ( `episode_id` TEXT CONSTRAINT Episode_pk PRIMARY KEY, `season` INTEGER, `episode` INTEGER, `number_in_series` INTEGER, `title` TEXT, `summary` TEXT, `air_date` TEXT, `episode_image` TEXT, `rating` REAL, `votes` INTEGER ); CREATE TABLE `Person` ( `name` TEXT CONSTRAINT Person_pk PRIMARY KEY, `birthdate` TEXT, `birth_name` TEXT, `birth_place` TEXT, `birth_region` TEXT, `birth_country` TEXT, `height_meters` REAL, `nickname` TEXT ); CREATE TABLE `Award` ( `award_id` INTEGER PRIMARY KEY, `organization` TEXT, `year` INTEGER, `award_category` TEXT, `award` TEXT, `person` TEXT, `role` TEXT, `episode_id` TEXT, `season` TEXT, `song` TEXT, `result` TEXT, FOREIGN KEY (person) REFERENCES Person(name), FOREIGN KEY (episode_id) REFERENCES Episode(episode_id) ); CREATE TABLE `Character_Award` ( `award_id` INTEGER, `character` TEXT, FOREIGN KEY (award_id) REFERENCES Award(award_id) ); CREATE TABLE `Credit` ( `episode_id` TEXT, `category` TEXT, `person` TEXT, `role` TEXT, `credited` TEXT, FOREIGN KEY (episode_id) REFERENCES Episode(episode_id), FOREIGN KEY (person) REFERENCES Person(name) ); CREATE TABLE `Keyword` ( `episode_id` TEXT, `keyword` TEXT, PRIMARY KEY (episode_id, keyword), FOREIGN KEY (episode_id) REFERENCES Episode(episode_id) ); CREATE TABLE `Vote` ( `episode_id` TEXT, `stars` INTEGER, `votes` INTEGER, `percent` REAL, FOREIGN KEY (episode_id) REFERENCES Episode(episode_id) ); Question: What is the summary of the episode in which Emily Blunt is featured in?

SELECT T1.summary FROM Episode AS T1 INNER JOIN Credit AS T2 ON T1.episode_id = T2.episode_id WHERE T2.person = 'Emily Blunt';

SELECT summary FROM Episode JOIN Credit ON Episode.episode_id = Credit.episode_id WHERE Credit.person = 'Emily Blunt';

SELECT T1.summary FROM Episode AS T1 INNER JOIN Credit AS T2 ON T1.episode_id = T2.episode_id WHERE T2.person = 'Emily Blunt'

Schema: CREATE TABLE `essays` ( `projectid` TEXT, `teacher_acctid` TEXT, `title` TEXT, `short_description` TEXT, `need_statement` TEXT, `essay` TEXT ); CREATE TABLE `projects` ( `projectid` TEXT NOT NULL PRIMARY KEY, `teacher_acctid` TEXT, `schoolid` TEXT, `school_ncesid` TEXT, `school_latitude` REAL, `school_longitude` REAL, `school_city` TEXT, `school_state` TEXT, `school_zip` INTEGER, `school_metro` TEXT, `school_district` TEXT, `school_county` TEXT, `school_charter` TEXT, `school_magnet` TEXT, `school_year_round` TEXT, `school_nlns` TEXT, `school_kipp` TEXT, `school_charter_ready_promise` TEXT, `teacher_prefix` TEXT, `teacher_teach_for_america` TEXT, `teacher_ny_teaching_fellow` TEXT, `primary_focus_subject` TEXT, `primary_focus_area` TEXT, `secondary_focus_subject` TEXT, `secondary_focus_area` TEXT, `resource_type` TEXT, `poverty_level` TEXT, `grade_level` TEXT, `fulfillment_labor_materials` REAL, `total_price_excluding_optional_support` REAL, `total_price_including_optional_support` REAL, `students_reached` INTEGER, `eligible_double_your_impact_match` TEXT, `eligible_almost_home_match` TEXT, `date_posted` DATE ); CREATE TABLE `donations` ( `donationid` TEXT NOT NULL PRIMARY KEY, `projectid` TEXT, `donor_acctid` TEXT, `donor_city` TEXT, `donor_state` TEXT, `donor_zip` TEXT, `is_teacher_acct` TEXT, `donation_timestamp` DATETIME, `donation_to_project` REAL, `donation_optional_support` REAL, `donation_total` REAL, `dollar_amount` TEXT, `donation_included_optional_support` TEXT, `payment_method` TEXT, `payment_included_acct_credit` TEXT, `payment_included_campaign_gift_card` TEXT, `payment_included_web_purchased_gift_card` TEXT, `payment_was_promo_matched` TEXT, `via_giving_page` TEXT, `for_honoree` TEXT, `donation_message` TEXT, FOREIGN KEY (projectid) REFERENCES projects(projectid) ); CREATE TABLE `resources` ( `resourceid` TEXT NOT NULL PRIMARY KEY, `projectid` TEXT, `vendorid` INTEGER, `vendor_name` TEXT, `project_resource_type` TEXT, `item_name` TEXT, `item_number` TEXT, `item_unit_price` REAL, `item_quantity` INTEGER, FOREIGN KEY (projectid) REFERENCES projects(projectid) ); Question: For each donation not from a teacher, state the donor account id and calculate the percentage of donation given for optional support. Hint: not from a teacher refers to is_teacher_acct = 'f'; donor account id refers to donor_acctid; percentage of donation = divide(donation_optional_support, donation_total)*100%

SELECT donor_acctid, donation_optional_support / donation_total FROM donations WHERE is_teacher_acct = 'f'

SELECT donor_acctid, (donation_optional_support / donation_total) * 100 AS percentage_optional_support FROM donations WHERE is_teacher_acct = 'f';

SELECT T1.donor_acctid, CAST(SUM(T1.donation_optional_support) AS REAL) * 100 / SUM(T1.donation_total) FROM donations AS T1 INNER JOIN essays AS T2 ON T1.projectid = T2.projectid WHERE T1.is_teacher_acct = 'f'

Schema: CREATE TABLE `country` ( `origin` INTEGER PRIMARY KEY, `country` TEXT ); CREATE TABLE `price` ( `ID` INTEGER PRIMARY KEY, `price` REAL ); CREATE TABLE `data` ( `ID` INTEGER PRIMARY KEY, `mpg` REAL, `cylinders` INTEGER, `displacement` REAL, `horsepower` INTEGER, `weight` INTEGER, `acceleration` REAL, `model` INTEGER, `car_name` TEXT, FOREIGN KEY (ID) REFERENCES price(ID) ); CREATE TABLE `production` ( `ID` INTEGER, `model_year` INTEGER, `country` INTEGER, PRIMARY KEY (ID, model_year), FOREIGN KEY (country) REFERENCES country(origin), FOREIGN KEY (ID) REFERENCES data(ID), FOREIGN KEY (ID) REFERENCES price(ID) ); Question: List the name of the cars with model year 1975. Hint: name of the car refers to car_name; model year 1975 refers to model_year = 1975

SELECT T1.car_name FROM data AS T1 INNER JOIN production AS T2 ON T1.ID = T2.ID WHERE T2.model_year = 1975

SELECT car_name FROM data JOIN production ON data.ID = production.ID WHERE production.model_year = 1975;

SELECT T1.car_name FROM data AS T1 INNER JOIN production AS T2 ON T1.ID = T2.ID WHERE T2.model_year = 1975

Schema: CREATE TABLE `Ingredient` ( `ingredient_id` INTEGER PRIMARY KEY, `category` TEXT, `name` TEXT, `plural` TEXT ); CREATE TABLE `Recipe` ( `recipe_id` INTEGER PRIMARY KEY, `title` TEXT, `subtitle` TEXT, `servings` INTEGER, `yield_unit` TEXT, `prep_min` INTEGER, `cook_min` INTEGER, `stnd_min` INTEGER, `source` TEXT, `intro` TEXT, `directions` TEXT ); CREATE TABLE `Nutrition` ( `recipe_id` INTEGER PRIMARY KEY, `protein` REAL, `carbo` REAL, `alcohol` REAL, `total_fat` REAL, `sat_fat` REAL, `cholestrl` REAL, `sodium` REAL, `iron` REAL, `vitamin_c` REAL, `vitamin_a` REAL, `fiber` REAL, `pcnt_cal_carb` REAL, `pcnt_cal_fat` REAL, `pcnt_cal_prot` REAL, `calories` REAL, FOREIGN KEY (recipe_id) REFERENCES Recipe(recipe_id) ); CREATE TABLE `Quantity` ( `quantity_id` INTEGER PRIMARY KEY, `recipe_id` INTEGER, `ingredient_id` INTEGER, `max_qty` REAL, `min_qty` REAL, `unit` TEXT, `preparation` TEXT, `optional` TEXT, FOREIGN KEY (recipe_id) REFERENCES Recipe(recipe_id), FOREIGN KEY (ingredient_id) REFERENCES Ingredient(ingredient_id), FOREIGN KEY (recipe_id) REFERENCES Nutrition(recipe_id) ); Question: What are the names of the recipes that will cause stomach pain? Hint: cause stomach pain refers to iron > 20

SELECT T1.title FROM Recipe AS T1 INNER JOIN Nutrition AS T2 ON T1.recipe_id = T2.recipe_id WHERE T2.iron > 20

SELECT DISTINCT R.title FROM Recipe R JOIN Nutrition N ON R.recipe_id = N.recipe_id WHERE N.iron > 20;

SELECT T1.title FROM Recipe AS T1 INNER JOIN Nutrition AS T2 ON T1.recipe_id = T2.recipe_id WHERE T2.iron > 20

Schema: CREATE TABLE `app_all` ( `app_id` INTEGER NOT NULL, PRIMARY KEY (`app_id`) ); CREATE TABLE `app_events` ( `event_id` INTEGER NOT NULL, `app_id` INTEGER NOT NULL, `is_installed` INTEGER NOT NULL, `is_active` INTEGER NOT NULL, PRIMARY KEY (`event_id`,`app_id`), FOREIGN KEY (`event_id`) REFERENCES `events` (`event_id`) ON DELETE CASCADE ON UPDATE CASCADE ); CREATE TABLE `app_events_relevant` ( `event_id` INTEGER NOT NULL, `app_id` INTEGER NOT NULL, `is_installed` INTEGER DEFAULT NULL, `is_active` INTEGER DEFAULT NULL, PRIMARY KEY (`event_id`,`app_id`), FOREIGN KEY (`event_id`) REFERENCES `events_relevant` (`event_id`) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (`app_id`) REFERENCES `app_all` (`app_id`) ON DELETE CASCADE ON UPDATE CASCADE ); CREATE TABLE `app_labels` ( `app_id` INTEGER NOT NULL, `label_id` INTEGER NOT NULL, FOREIGN KEY (`label_id`) REFERENCES `label_categories` (`label_id`) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (`app_id`) REFERENCES `app_all` (`app_id`) ON DELETE CASCADE ON UPDATE CASCADE ); CREATE TABLE `events` ( `event_id` INTEGER NOT NULL, `device_id` INTEGER DEFAULT NULL, `timestamp` DATETIME DEFAULT NULL, `longitude` REAL DEFAULT NULL, `latitude` REAL DEFAULT NULL, PRIMARY KEY (`event_id`) ); CREATE TABLE `events_relevant` ( `event_id` INTEGER NOT NULL, `device_id` INTEGER DEFAULT NULL, `timestamp` DATETIME NOT NULL, `longitude` REAL NOT NULL, `latitude` REAL NOT NULL, PRIMARY KEY (`event_id`), FOREIGN KEY (`device_id`) REFERENCES `gender_age` (`device_id`) ON DELETE CASCADE ON UPDATE CASCADE ); CREATE TABLE `gender_age` ( `device_id` INTEGER NOT NULL, `gender` TEXT DEFAULT NULL, `age` INTEGER DEFAULT NULL, `group` TEXT DEFAULT NULL, PRIMARY KEY (`device_id`), FOREIGN KEY (`device_id`) REFERENCES `phone_brand_device_model2` (`device_id`) ON DELETE CASCADE ON UPDATE CASCADE ); CREATE TABLE `gender_age_test` ( `device_id` INTEGER NOT NULL, PRIMARY KEY (`device_id`) ); CREATE TABLE `gender_age_train` ( `device_id` INTEGER NOT NULL, `gender` TEXT DEFAULT NULL, `age` INTEGER DEFAULT NULL, `group` TEXT DEFAULT NULL, PRIMARY KEY (`device_id`) ); CREATE TABLE `label_categories` ( `label_id` INTEGER NOT NULL, `category` TEXT DEFAULT NULL, PRIMARY KEY (`label_id`) ); CREATE TABLE `phone_brand_device_model2` ( `device_id` INTEGER NOT NULL, `phone_brand` TEXT NOT NULL, `device_model` TEXT NOT NULL, PRIMARY KEY (`device_id`,`phone_brand`,`device_model`) ); CREATE TABLE `sample_submission` ( `device_id` INTEGER NOT NULL, `F23-` REAL DEFAULT NULL, `F24-26` REAL DEFAULT NULL, `F27-28` REAL DEFAULT NULL, `F29-32` REAL DEFAULT NULL, `F33-42` REAL DEFAULT NULL, `F43+` REAL DEFAULT NULL, `M22-` REAL DEFAULT NULL, `M23-26` REAL DEFAULT NULL, `M27-28` REAL DEFAULT NULL, `M29-31` REAL DEFAULT NULL, `M32-38` REAL DEFAULT NULL, `M39+` REAL DEFAULT NULL, PRIMARY KEY (`device_id`) ); Question: Provide the total number of the male users that use OPPO as their phone brand. Hint: male refers to gender = 'Male';

SELECT COUNT(T1.device_id) FROM gender_age AS T1 INNER JOIN phone_brand_device_model2 AS T2 ON T1.device_id = T2.device_id WHERE T2.phone_brand = 'OPPO' AND T1.gender = 'M'

SELECT COUNT(DISTINCT gb.device_id) FROM gender_age_train gb JOIN phone_brand_device_model2 pbdm ON gb.device_id = pbdm.device_id WHERE gb.gender = 'Male' AND pbdm.phone_brand = 'OPPO';

SELECT COUNT(T1.device_id) FROM gender_age AS T1 INNER JOIN phone_brand_device_model2 AS T2 ON T1.device_id = T2.device_id WHERE T1.gender = 'Male' AND T2.phone_brand = 'OPPO'

Schema: CREATE TABLE `lists` ( `user_id` INTEGER REFERENCES lists_users (user_id), `list_id` INTEGER NOT NULL PRIMARY KEY, `list_title` TEXT, `list_movie_number` INTEGER, `list_update_timestamp_utc` TEXT, `list_creation_timestamp_utc` TEXT, `list_followers` INTEGER, `list_url` TEXT, `list_comments` INTEGER, `list_description` TEXT, `list_cover_image_url` TEXT, `list_first_image_url` TEXT, `list_second_image_url` TEXT, `list_third_image_url` TEXT ); CREATE TABLE `movies` ( `movie_id` INTEGER NOT NULL PRIMARY KEY, `movie_title` TEXT, `movie_release_year` INTEGER, `movie_url` TEXT, `movie_title_language` TEXT, `movie_popularity` INTEGER, `movie_image_url` TEXT, `director_id` TEXT, `director_name` TEXT, `director_url` TEXT ); CREATE TABLE `ratings_users` ( `user_id` INTEGER REFERENCES lists_users (user_id), `rating_date_utc` TEXT, `user_trialist` INTEGER, `user_subscriber` INTEGER, `user_avatar_image_url` TEXT, `user_cover_image_url` TEXT, `user_eligible_for_trial` INTEGER, `user_has_payment_method` INTEGER ); CREATE TABLE `lists_users` ( `user_id` INTEGER NOT NULL, `list_id` INTEGER NOT NULL, `list_update_date_utc` TEXT, `list_creation_date_utc` TEXT, `user_trialist` INTEGER, `user_subscriber` INTEGER, `user_avatar_image_url` TEXT, `user_cover_image_url` TEXT, `user_eligible_for_trial` TEXT, `user_has_payment_method` TEXT, PRIMARY KEY (user_id, list_id), FOREIGN KEY (list_id) REFERENCES lists(list_id), FOREIGN KEY (user_id) REFERENCES lists(user_id) ); CREATE TABLE `ratings` ( `movie_id` INTEGER, `rating_id` INTEGER, `rating_url` TEXT, `rating_score` INTEGER, `rating_timestamp_utc` TEXT, `critic` TEXT, `critic_likes` INTEGER, `critic_comments` INTEGER, `user_id` INTEGER, `user_trialist` INTEGER, `user_subscriber` INTEGER, `user_eligible_for_trial` INTEGER, `user_has_payment_method` INTEGER, FOREIGN KEY (movie_id) REFERENCES movies(movie_id), FOREIGN KEY (user_id) REFERENCES lists_users(user_id), FOREIGN KEY (rating_id) REFERENCES ratings(rating_id), FOREIGN KEY (user_id) REFERENCES ratings_users(user_id) ); Question: How many movie lists were still updated 10 years after it was created? Hint: updated 10 years after it was created refers to list_update_timestamp_utc > (list_creation_timestamp_utc+10);

SELECT COUNT(*) FROM lists WHERE SUBSTR(list_update_timestamp_utc, 1, 4) - SUBSTR(list_creation_timestamp_utc, 1, 4) > 10

SELECT COUNT(*) FROM lists WHERE list_update_timestamp_utc > date(strftime('%Y', list_creation_timestamp_utc) || '-01-01', '+10 years');

SELECT COUNT(list_id) FROM lists WHERE strftime('%Y', list_update_timestamp_utc) - strftime('%Y', list_creation_timestamp_utc) > 10

Schema: CREATE TABLE `characters` ( `movie_title` TEXT PRIMARY KEY, `release_date` TEXT, `hero` TEXT, `villian` TEXT, `song` TEXT, FOREIGN KEY (hero) REFERENCES `voice-actors`(character) ); CREATE TABLE `director` ( `name` TEXT PRIMARY KEY, `director` TEXT, FOREIGN KEY (name) REFERENCES characters(movie_title) ); CREATE TABLE `movies_total_gross` ( `movie_title` TEXT, `release_date` TEXT, `genre` TEXT, `MPAA_rating` TEXT, `total_gross` TEXT, `inflation_adjusted_gross` TEXT, PRIMARY KEY (movie_title, release_date), FOREIGN KEY (movie_title) REFERENCES characters(movie_title) ); CREATE TABLE `revenue` ( `Year` INTEGER PRIMARY KEY, `Studio Entertainment[NI 1]` REAL, `Disney Consumer Products[NI 2]` REAL, `Disney Interactive[NI 3][Rev 1]` INTEGER, `Walt Disney Parks and Resorts` REAL, `Disney Media Networks` TEXT, `Total` INTEGER ); CREATE TABLE `voice-actors` ( `character` TEXT PRIMARY KEY, `voice-actor` TEXT, `movie` TEXT, FOREIGN KEY (movie) REFERENCES characters(movie_title) ); Question: What are the characters in the PG movies? Hint: PG is an abbreviation for parental guidance and refers to MPAA_rating = 'PG';

SELECT DISTINCT T2.character FROM movies_total_gross AS T1 INNER JOIN `voice-actors` AS T2 ON T1.movie_title = T2.movie WHERE T1.MPAA_rating = 'PG'

SELECT DISTINCT characters.hero, characters.villian FROM characters JOIN movies_total_gross ON characters.movie_title = movies_total_gross.movie_title WHERE movies_total_gross.MPAA_rating = 'PG';

SELECT T1.character FROM `voice-actors` AS T1 INNER JOIN movies_total_gross AS T2 ON T1.movie = T2.movie_title WHERE T2.MPAA_rating = 'PG'

Schema: CREATE TABLE `Air Carriers` ( `Code` INTEGER PRIMARY KEY, `Description` TEXT ); CREATE TABLE `Airports` ( `Code` TEXT PRIMARY KEY, `Description` TEXT ); CREATE TABLE `Airlines` ( `FL_DATE` TEXT, `OP_CARRIER_AIRLINE_ID` INTEGER, `TAIL_NUM` TEXT, `OP_CARRIER_FL_NUM` INTEGER, `ORIGIN_AIRPORT_ID` INTEGER, `ORIGIN_AIRPORT_SEQ_ID` INTEGER, `ORIGIN_CITY_MARKET_ID` INTEGER, `ORIGIN` TEXT, `DEST_AIRPORT_ID` INTEGER, `DEST_AIRPORT_SEQ_ID` INTEGER, `DEST_CITY_MARKET_ID` INTEGER, `DEST` TEXT, `CRS_DEP_TIME` INTEGER, `DEP_TIME` INTEGER, `DEP_DELAY` INTEGER, `DEP_DELAY_NEW` INTEGER, `ARR_TIME` INTEGER, `ARR_DELAY` INTEGER, `ARR_DELAY_NEW` INTEGER, `CANCELLED` INTEGER, `CANCELLATION_CODE` TEXT, `CRS_ELAPSED_TIME` INTEGER, `ACTUAL_ELAPSED_TIME` INTEGER, `CARRIER_DELAY` INTEGER, `WEATHER_DELAY` INTEGER, `NAS_DELAY` INTEGER, `SECURITY_DELAY` INTEGER, `LATE_AIRCRAFT_DELAY` INTEGER, FOREIGN KEY (ORIGIN) REFERENCES Airports(Code), FOREIGN KEY (DEST) REFERENCES Airports(Code), FOREIGN KEY (OP_CARRIER_AIRLINE_ID) REFERENCES `Air Carriers`(Code) ); Question: Please list the destination cities of all the flights that were cancelled on 2018/8/1. Hint: destination cities refers to DEST; cancelled refers to CANCELLED = 1; on 2018/8/1 refers to FL_DATE = '2018/8/1';

SELECT DEST FROM Airlines WHERE FL_DATE = '2018/8/1' AND CANCELLED = 1 GROUP BY DEST

SELECT DISTINCT DEST FROM Airlines WHERE FL_DATE = '2018/8/1' AND CANCELLED = 1;

SELECT DISTINCT T2.Description FROM Airlines AS T1 INNER JOIN Airports AS T2 ON T1.DEST = T2.Code WHERE T1.FL_DATE = '2018/8/1' AND T1.CANCELLED = 1

Schema: CREATE TABLE `authors` ( `au_id` TEXT PRIMARY KEY, `au_lname` TEXT NOT NULL, `au_fname` TEXT NOT NULL, `phone` TEXT NOT NULL, `address` TEXT, `city` TEXT, `state` TEXT, `zip` TEXT, `contract` TEXT NOT NULL ); CREATE TABLE `jobs` ( `job_id` INTEGER PRIMARY KEY, `job_desc` TEXT NOT NULL, `min_lvl` INTEGER NOT NULL, `max_lvl` INTEGER NOT NULL ); CREATE TABLE `publishers` ( `pub_id` TEXT PRIMARY KEY, `pub_name` TEXT, `city` TEXT, `state` TEXT, `country` TEXT ); CREATE TABLE `employee` ( `emp_id` TEXT PRIMARY KEY, `fname` TEXT NOT NULL, `minit` TEXT, `lname` TEXT NOT NULL, `job_id` INTEGER NOT NULL, `job_lvl` INTEGER, `pub_id` TEXT NOT NULL, `hire_date` DATETIME NOT NULL, FOREIGN KEY (job_id) REFERENCES jobs(job_id) ON UPDATE CASCADE ON DELETE CASCADE, FOREIGN KEY (pub_id) REFERENCES publishers(pub_id) ON UPDATE CASCADE ON DELETE CASCADE ); CREATE TABLE `pub_info` ( `pub_id` TEXT PRIMARY KEY, `logo` BLOB, `pr_info` TEXT, FOREIGN KEY (pub_id) REFERENCES publishers(pub_id) ON UPDATE CASCADE ON DELETE CASCADE ); CREATE TABLE `stores` ( `stor_id` TEXT PRIMARY KEY, `stor_name` TEXT, `stor_address` TEXT, `city` TEXT, `state` TEXT, `zip` TEXT ); CREATE TABLE `discounts` ( `discounttype` TEXT NOT NULL, `stor_id` TEXT, `lowqty` INTEGER, `highqty` INTEGER, `discount` REAL NOT NULL, FOREIGN KEY (stor_id) REFERENCES stores(stor_id) ON UPDATE CASCADE ON DELETE CASCADE ); CREATE TABLE `titles` ( `title_id` TEXT PRIMARY KEY, `title` TEXT NOT NULL, `type` TEXT NOT NULL, `pub_id` TEXT, `price` REAL, `advance` REAL, `royalty` INTEGER, `ytd_sales` INTEGER, `notes` TEXT, `pubdate` DATETIME NOT NULL, FOREIGN KEY (pub_id) REFERENCES publishers(pub_id) ON UPDATE CASCADE ON DELETE CASCADE ); CREATE TABLE `roysched` ( `title_id` TEXT NOT NULL, `lorange` INTEGER, `hirange` INTEGER, `royalty` INTEGER, FOREIGN KEY (title_id) REFERENCES titles(title_id) ON UPDATE CASCADE ON DELETE CASCADE ); CREATE TABLE `sales` ( `stor_id` TEXT NOT NULL, `ord_num` TEXT NOT NULL, `ord_date` DATETIME NOT NULL, `qty` INTEGER NOT NULL, `payterms` TEXT NOT NULL, `title_id` TEXT NOT NULL, PRIMARY KEY (stor_id, ord_num, title_id), FOREIGN KEY (stor_id) REFERENCES stores(stor_id) ON UPDATE CASCADE ON DELETE CASCADE, FOREIGN KEY (title_id) REFERENCES titles(title_id) ON UPDATE CASCADE ON DELETE CASCADE ); CREATE TABLE `titleauthor` ( `au_id` TEXT NOT NULL, `title_id` TEXT NOT NULL, `au_ord` INTEGER, `royaltyper` INTEGER, PRIMARY KEY (au_id, title_id), FOREIGN KEY (au_id) REFERENCES authors(au_id) ON UPDATE CASCADE ON DELETE CASCADE, FOREIGN KEY (title_id) REFERENCES titles (title_id) ON UPDATE CASCADE ON DELETE CASCADE ); Question: Which city did Victoria P Ashworth work in?

SELECT T2.city FROM employee AS T1 INNER JOIN publishers AS T2 ON T1.pub_id = T2.pub_id WHERE T1.fname = 'Victoria' AND T1.minit = 'P' AND T1.lname = 'Ashworth'

SELECT city FROM employee WHERE fname = 'Victoria' AND minit = 'P' AND lname = 'Ashworth';

SELECT T2.city FROM employee AS T1 INNER JOIN publishers AS T2 ON T1.pub_id = T2.pub_id WHERE T1.lname = 'Ashworth' AND T1.fname = 'Victoria'

Schema: CREATE TABLE `genre` ( `id` INTEGER NOT NULL PRIMARY KEY, `genre_name` TEXT DEFAULT NULL ); CREATE TABLE `game` ( `id` INTEGER NOT NULL PRIMARY KEY, `genre_id` INTEGER DEFAULT NULL, `game_name` TEXT DEFAULT NULL, FOREIGN KEY (genre_id) REFERENCES genre(id) ); CREATE TABLE `platform` ( `id` INTEGER NOT NULL PRIMARY KEY, `platform_name` TEXT DEFAULT NULL ); CREATE TABLE `publisher` ( `id` INTEGER NOT NULL PRIMARY KEY, `publisher_name` TEXT DEFAULT NULL ); CREATE TABLE `game_publisher` ( `id` INTEGER NOT NULL PRIMARY KEY, `game_id` INTEGER DEFAULT NULL, `publisher_id` INTEGER DEFAULT NULL, FOREIGN KEY (game_id) REFERENCES game(id), FOREIGN KEY (publisher_id) REFERENCES publisher(id) ); CREATE TABLE `game_platform` ( `id` INTEGER NOT NULL PRIMARY KEY, `game_publisher_id` INTEGER DEFAULT NULL, `platform_id` INTEGER DEFAULT NULL, `release_year` INTEGER DEFAULT NULL, FOREIGN KEY (game_publisher_id) REFERENCES game_publisher(id), FOREIGN KEY (platform_id) REFERENCES platform(id) ); CREATE TABLE `region` ( `id` INTEGER NOT NULL PRIMARY KEY, `region_name` TEXT DEFAULT NULL ); CREATE TABLE `region_sales` ( `region_id` INTEGER DEFAULT NULL, `game_platform_id` INTEGER DEFAULT NULL, `num_sales` REAL DEFAULT NULL, FOREIGN KEY (game_platform_id) REFERENCES game_platform(id), FOREIGN KEY (region_id) REFERENCES region(id) ); Question: What are the sales made by the games in Japan region? Hint: sales = SUM(num_sales); Japan region refers to region_name = 'Japan';

SELECT SUM(CASE WHEN T2.region_name = 'Japan' THEN T1.num_sales ELSE 0 END) AS nums FROM region_sales AS T1 INNER JOIN region AS T2 ON T1.region_id = T2.id

SELECT SUM(region_sales.num_sales) AS total_sales FROM region_sales JOIN region ON region_sales.region_id = region.id WHERE region.region_name = 'Japan';

SELECT SUM(T2.num_sales) FROM region AS T1 INNER JOIN region_sales AS T2 ON T1.id = T2.region_id WHERE T1.region_name = 'Japan'

Schema: CREATE TABLE `Method` ( `Id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, `Name` TEXT, `FullComment` TEXT, `Summary` TEXT, `ApiCalls` TEXT, `CommentIsXml` INTEGER, `SampledAt` INTEGER, `SolutionId` INTEGER, `Lang` TEXT, `NameTokenized` TEXT ); CREATE TABLE `MethodParameter` ( `Id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, `MethodId` TEXT, `Type` TEXT, `Name` TEXT ); CREATE TABLE `Repo` ( `Id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, `Url` TEXT, `Stars` INTEGER, `Forks` INTEGER, `Watchers` INTEGER, `ProcessedTime` INTEGER ); CREATE TABLE `Solution` ( `Id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, `RepoId` INTEGER, `Path` TEXT, `ProcessedTime` INTEGER, `WasCompiled` INTEGER ); Question: For the method which got the tokenized name as 'interp parser expr', what is the processed time for its solution? Hint: tokenized name refers to NameTokenized; NameTokenized = 'interp parser expr'; processed time for its solution refers to ProcessedTime;

SELECT T1.ProcessedTime FROM Solution AS T1 INNER JOIN Method AS T2 ON T1.Id = T2.SolutionId WHERE T2.NameTokenized = 'interp parser expr'

SELECT Solution.ProcessedTime FROM Method JOIN Solution ON Method.SolutionId = Solution.Id WHERE Method.NameTokenized = 'interp parser expr';

SELECT T2.ProcessedTime FROM Solution AS T1 INNER JOIN Method AS T2 ON T1.Id = T2.SolutionId WHERE T2.NameTokenized = 'interp parser expr'