chabab/gemma-3-270m-text2sql-oracle-postgres
0530
1{2 "exact_match": 0.7833333333333333,3 "n": 60,4 "rows": [5 {6 "id": "ex_14b7cfd991612ffb",7 "dialect": "postgres",8 "difficulty": "medium",9 "question": "Write a PostgreSQL query for: Find staff with last_name beginning with s ignoring case.",10 "gold": "SELECT employee_id, first_name, last_name FROM employees WHERE last_name ILIKE 'S%';",11 "pred": "SELECT employee_id, first_name, last_name FROM employees WHERE last_name LIKE ' ' || first_name;",12 "match": false13 },14 {15 "id": "ex_c73896e0f574d7cf",16 "dialect": "oracle",17 "difficulty": "easy",18 "question": "Number of four-credit courses. Return only the SQL.",19 "gold": "SELECT COUNT(*) AS course_count FROM courses WHERE credits = 4;",20 "pred": "SELECT COUNT(*) AS course_count FROM courses WHERE credits = 4;",21 "match": true22 },23 {24 "id": "ex_a8b9ea352a33e377",25 "dialect": "oracle",26 "difficulty": "medium",27 "question": "is_available true. Return only the SQL.",28 "gold": "SELECT agent_id, agent_name, team FROM agents WHERE is_available = 1;",29 "pred": "SELECT agent_id, agent_name, team FROM agents WHERE is_available = 1;",30 "match": true31 },32 {33 "id": "ex_cda1fdc3f8926260",34 "dialect": "oracle",35 "difficulty": "easy",36 "question": "How many cancelled orders are there?",37 "gold": "SELECT COUNT(*) AS cancelled_count FROM orders WHERE status = 'Cancelled';",38 "pred": "SELECT COUNT(*) AS cancelled_count FROM orders WHERE status = 'Cancelled';",39 "match": true40 },41 {42 "id": "ex_805236d32a829662",43 "dialect": "oracle",44 "difficulty": "medium",45 "question": "Write a Oracle query for: Staff whose department_id is 10, 20 or 30.",46 "gold": "SELECT employee_id, first_name, last_name, department_id FROM employees WHERE department_id IN (10, 20, 30);",47 "pred": "SELECT employee_id, first_name, last_name, department_id FROM employees WHERE department_id IN (10, 20, 30);",48 "match": true49 },50 {51 "id": "ex_0f91611416b0b790",52 "dialect": "oracle",53 "difficulty": "medium",54 "question": "Return a full_name column. Return only the SQL.",55 "gold": "SELECT first_name || ' ' || last_name AS full_name FROM employees;",56 "pred": "SELECT first_name || ' ' || last_name AS full_name FROM employees;",57 "match": true58 },59 {60 "id": "ex_2080c243f8214291",61 "dialect": "oracle",62 "difficulty": "medium",63 "question": "Filter employees to those three departments. Return only the SQL.",64 "gold": "SELECT employee_id, first_name, last_name, department_id FROM employees WHERE department_id IN (10, 20, 30);",65 "pred": "SELECT employee_id, first_name, last_name, department_id FROM employees WHERE department_id IN (10, 20, 30);",66 "match": true67 },68 {69 "id": "ex_2bcfd4ac5124c8cf",70 "dialect": "postgres",71 "difficulty": "easy",72 "question": "Write a PostgreSQL query for: List suppliers from fastest to slowest lead_time_days.",73 "gold": "SELECT supplier_id, supplier_name, lead_time_days FROM suppliers ORDER BY lead_time_days ASC;",74 "pred": "SELECT supplier_id, supplier_name, lead_time_days FROM suppliers ORDER BY lead_time_days ASC LIMIT 10;",75 "match": false76 },77 {78 "id": "ex_67006ffcbb9dd990",79 "dialect": "postgres",80 "difficulty": "easy",81 "question": "Write a PostgreSQL query for: Show customer_name and country.",82 "gold": "SELECT customer_name, country FROM customers;",83 "pred": "SELECT customer_name, country FROM customers;",84 "match": true85 },86 {87 "id": "ex_8fcddf883ae71117",88 "dialect": "oracle",89 "difficulty": "medium",90 "question": "Write a Oracle query for: account_id having COUNT(*) > 20 in transactions.",91 "gold": "SELECT account_id, COUNT(*) AS txn_count FROM transactions GROUP BY account_id HAVING COUNT(*) > 20;",92 "pred": "SELECT account_id, COUNT(*) AS txn_count FROM transactions GROUP BY account_id HAVING COUNT(*) > 20;",93 "match": true94 },95 {96 "id": "ex_0e773b38db423d2b",97 "dialect": "oracle",98 "difficulty": "medium",99 "question": "List staff without a manager. Return only the SQL.",100 "gold": "SELECT employee_id, first_name, last_name FROM employees WHERE manager_id IS NULL;",101 "pred": "SELECT employee_id, first_name, last_name FROM employees WHERE manager_id IS NULL;",102 "match": true103 },104 {105 "id": "ex_c0b3b7c1bd7f1eb4",106 "dialect": "oracle",107 "difficulty": "medium",108 "question": "Write a Oracle query for: Set status DELIVERED and delivered_on to today for shipment 77.",109 "gold": "UPDATE shipments SET status = 'DELIVERED', delivered_on = TRUNC(SYSDATE) WHERE shipment_id = 77;",110 "pred": "UPDATE shipments SET status = 'DELIVERED', delivered_on = TRUNC(SYSDATE) WHERE shipment_id = 77;",111 "match": true112 },113 {114 "id": "ex_5c388e3163e58d6d",115 "dialect": "postgres",116 "difficulty": "medium",117 "question": "Write a PostgreSQL query for: updated_at on the current date.",118 "gold": "SELECT stock_id, warehouse_id, item_id, qty_on_hand FROM stock WHERE updated_at::date = CURRENT_DATE;",119 "pred": "SELECT txn_id, warehouse_id, item_id, created_at FROM transactions WHERE created_at >= NOW() - INTERVAL '24 hours';",120 "match": false121 },122 {123 "id": "ex_4f23cbbbfeffd6ed",124 "dialect": "oracle",125 "difficulty": "medium",126 "question": "Employees who have no manager.",127 "gold": "SELECT employee_id, first_name, last_name FROM employees WHERE manager_id IS NULL;",128 "pred": "SELECT employee_id, first_name, last_name FROM employees WHERE manager_id IS NULL;",129 "match": true130 },131 {132 "id": "ex_fd5c29ca20aca25c",133 "dialect": "oracle",134 "difficulty": "medium",135 "question": "Busy accounts, over 20 txns. Return only the SQL.",136 "gold": "SELECT account_id, COUNT(*) AS txn_count FROM transactions GROUP BY account_id HAVING COUNT(*) > 20;",137 "pred": "SELECT account_id, COUNT(*) AS txn_count FROM transactions GROUP BY account_id HAVING COUNT(*) > 20;",138 "match": true139 },140 {141 "id": "ex_b3743f6061f84998",142 "dialect": "oracle",143 "difficulty": "hard",144 "question": "Using a CTE, customers with more than 3 orders.",145 "gold": "WITH order_counts AS (SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id) SELECT c.customer_name, oc.order_count FROM order_counts oc JOIN customers c ON c.customer_id = oc.customer_id WHERE oc.order_count > 3;",146 "pred": "WITH order_counts AS (SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id) SELECT c.customer_name, oc.order_count FROM order_counts oc JOIN customers c ON c.customer_id = oc.customer_id WHERE oc.order_count > 3;",147 "match": true148 },149 {150 "id": "ex_94effceddcf7fcf8",151 "dialect": "oracle",152 "difficulty": "medium",153 "question": "Employees in departments 10, 20, or 30.",154 "gold": "SELECT employee_id, first_name, last_name, department_id FROM employees WHERE department_id IN (10, 20, 30);",155 "pred": "SELECT employee_id, first_name, last_name, department_id FROM employees WHERE department_id IN (10, 20, 30);",156 "match": true157 },158 {159 "id": "ex_969dc45c17fff70b",160 "dialect": "oracle",161 "difficulty": "medium",162 "question": "Stock rows updated today.",163 "gold": "SELECT stock_id, warehouse_id, item_id, qty_on_hand FROM stock WHERE TRUNC(updated_at) = TRUNC(SYSDATE);",164 "pred": "SELECT w.warehouse_id, w.warehouse_name FROM warehouses w WHERE EXISTS (SELECT 1 FROM stock s WHERE s.warehouse_id = w.warehouse_id AND s.item_id = 42);",165 "match": false166 },167 {168 "id": "ex_6c0dff5f6a58ac7c",169 "dialect": "postgres",170 "difficulty": "medium",171 "question": "Employees whose last name starts with S, case-insensitive.",172 "gold": "SELECT employee_id, first_name, last_name FROM employees WHERE last_name ILIKE 'S%';",173 "pred": "SELECT employee_id, first_name, last_name FROM employees WHERE last_name LIKE 'S%';",174 "match": false175 },176 {177 "id": "ex_53abe765235df5c7",178 "dialect": "oracle",179 "difficulty": "medium",180 "question": "Available agents.",181 "gold": "SELECT agent_id, agent_name, team FROM agents WHERE is_available = 1;",182 "pred": "SELECT agent_id, agent_name, team FROM agents WHERE is_available = 1;",183 "match": true184 },185 {186 "id": "ex_2f156ae4a64ebcc3",187 "dialect": "postgres",188 "difficulty": "easy",189 "question": "List all customer names and countries.",190 "gold": "SELECT customer_name, country FROM customers;",191 "pred": "SELECT customer_name, country FROM customers;",192 "match": true193 },194 {195 "id": "ex_59c91c5ff4fb68e5",196 "dialect": "postgres",197 "difficulty": "medium",198 "question": "Stock rows updated today.",199 "gold": "SELECT stock_id, warehouse_id, item_id, qty_on_hand FROM stock WHERE updated_at::date = CURRENT_DATE;",200 "pred": "SELECT w.warehouse_id, w.warehouse_name, s.item_id, s.qty_on_hand, t.warehouse_id, i.item_name, s.reorder_point FROM stock s JOIN items i ON s.item_id = i.item_id JOIN warehouses w ON s.warehouse_id = w.warehouse_id WHERE w.warehouse_name = 'AcmeWarehouses' AND i.item_name = 'Stock' AND s.qty_on_hand <= i.reorder_point;",201 "match": false202 },203 {204 "id": "ex_35881d0f2446ccb1",205 "dialect": "postgres",206 "difficulty": "easy",207 "question": "Sort suppliers by lead_time_days. Return only the SQL.",208 "gold": "SELECT supplier_id, supplier_name, lead_time_days FROM suppliers ORDER BY lead_time_days ASC;",209 "pred": "SELECT supplier_id, supplier_name, lead_time_days FROM suppliers ORDER BY lead_time_days ASC;",210 "match": true211 },212 {213 "id": "ex_71bcc72a8600e2e8",214 "dialect": "postgres",215 "difficulty": "medium",216 "question": "Last names that start with the letter S, any case. Return only the SQL.",217 "gold": "SELECT employee_id, first_name, last_name FROM employees WHERE last_name ILIKE 'S%';",218 "pred": "SELECT employee_id, SUBSTRING(last_name FROM 1 FOR 3) AS name_prefix FROM employees;",219 "match": false220 },221 {222 "id": "ex_7074e097cbb5dacc",223 "dialect": "oracle",224 "difficulty": "medium",225 "question": "Open ticket load per agent name.",226 "gold": "SELECT a.agent_name, COUNT(*) AS open_tickets FROM tickets t JOIN agents a ON t.agent_id = a.agent_id WHERE t.status = 'Open' GROUP BY a.agent_name;",227 "pred": "SELECT a.agent_name, COUNT(*) AS open_tickets FROM tickets t JOIN agents a ON t.agent_id = a.agent_id WHERE t.status = 'Open' GROUP BY a.agent_name;",228 "match": true229 },230 {231 "id": "ex_a173431eb1705ce1",232 "dialect": "postgres",233 "difficulty": "hard",234 "question": "Write a PostgreSQL query for: Customers missing from the orders table.",235 "gold": "SELECT c.customer_id, c.customer_name FROM customers c WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);",236 "pred": "SELECT c.customer_id, c.customer_name FROM customers c WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);",237 "match": true238 },239 {240 "id": "ex_c33dae39b2813158",241 "dialect": "oracle",242 "difficulty": "medium",243 "question": "Today's stock updates. Return only the SQL.",244 "gold": "SELECT stock_id, warehouse_id, item_id, qty_on_hand FROM stock WHERE TRUNC(updated_at) = TRUNC(SYSDATE);",245 "pred": "SELECT txn_id, warehouse_id, item_id, qty_on_hand, created_at FROM stock WHERE qty_on_hand <= TRUNC(SYSDATE) - 1;",246 "match": false247 },248 {249 "id": "ex_314a6b2149e2f1e8",250 "dialect": "oracle",251 "difficulty": "easy",252 "question": "The ten students with the highest GPA.",253 "gold": "SELECT full_name, major, gpa FROM students ORDER BY gpa DESC FETCH FIRST 10 ROWS ONLY;",254 "pred": "SELECT full_name, major, gpa FROM students ORDER BY gpa DESC FETCH FIRST 10 ROWS ONLY;",255 "match": true256 },257 {258 "id": "ex_ce4615861e7a7902",259 "dialect": "oracle",260 "difficulty": "medium",261 "question": "Write a Oracle query for: updated_at on the current date.",262 "gold": "SELECT stock_id, warehouse_id, item_id, qty_on_hand FROM stock WHERE TRUNC(updated_at) = TRUNC(SYSDATE);",263 "pred": "SELECT txn_id, warehouse_id, item_id, created_at FROM transactions WHERE created_at >= SYSDATE - 1;",264 "match": false265 },266 {267 "id": "ex_64a72b64a6631d74",268 "dialect": "oracle",269 "difficulty": "medium",270 "question": "Origins that begin with San. Return only the SQL.",271 "gold": "SELECT shipment_id, tracking_no, origin_city FROM shipments WHERE origin_city LIKE 'San%';",272 "pred": "SELECT shipment_id, tracking_no, origin_city FROM shipments WHERE origin_city LIKE 'San%';",273 "match": true274 },275 {276 "id": "ex_eb5014dbd237c58b",277 "dialect": "oracle",278 "difficulty": "medium",279 "question": "Write a Oracle query for: Concatenate first and last name.",280 "gold": "SELECT first_name || ' ' || last_name AS full_name FROM employees;",281 "pred": "SELECT first_name || ' ' || last_name AS full_name FROM employees;",282 "match": true283 },284 {285 "id": "ex_7872027d199a94d7",286 "dialect": "postgres",287 "difficulty": "hard",288 "question": "Customers who never placed an order.",289 "gold": "SELECT c.customer_id, c.customer_name FROM customers c WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);",290 "pred": "SELECT c.customer_id, c.customer_name FROM customers c WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);",291 "match": true292 },293 {294 "id": "ex_f59b7eb0d4cd4455",295 "dialect": "oracle",296 "difficulty": "medium",297 "question": "Employees whose last name starts with S, case-insensitive.",298 "gold": "SELECT employee_id, first_name, last_name FROM employees WHERE UPPER(last_name) LIKE 'S%';",299 "pred": "SELECT employee_id, first_name, last_name FROM employees WHERE last_name LIKE '%S%';",300 "match": false301 },302 {303 "id": "ex_ded04128a74afeef",304 "dialect": "oracle",305 "difficulty": "medium",306 "question": "Mark shipment 77 delivered today.",307 "gold": "UPDATE shipments SET status = 'DELIVERED', delivered_on = TRUNC(SYSDATE) WHERE shipment_id = 77;",308 "pred": "UPDATE shipments SET status = 'DELIVERED', delivered_on = TRUNC(SYSDATE) WHERE shipment_id = 77;",309 "match": true310 },311 {312 "id": "ex_bb6c014680d3ea3c",313 "dialect": "oracle",314 "difficulty": "medium",315 "question": "Complete shipment 77. Return only the SQL.",316 "gold": "UPDATE shipments SET status = 'DELIVERED', delivered_on = TRUNC(SYSDATE) WHERE shipment_id = 77;",317 "pred": "UPDATE shipments SET status = 'DELIVERED', delivered_on = TRUNC(SYSDATE) WHERE shipment_id = 77;",318 "match": true319 },320 {321 "id": "ex_9e0427dcec4e7666",322 "dialect": "postgres",323 "difficulty": "hard",324 "question": "customer_id in customers not in orders. Return only the SQL.",325 "gold": "SELECT c.customer_id, c.customer_name FROM customers c WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);",326 "pred": "SELECT c.customer_id, c.customer_name FROM customers c WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);",327 "match": true328 },329 {330 "id": "ex_ee9af559100ae7f3",331 "dialect": "oracle",332 "difficulty": "easy",333 "question": "Write a Oracle query for: Top 10 GPA list.",334 "gold": "SELECT full_name, major, gpa FROM students ORDER BY gpa DESC FETCH FIRST 10 ROWS ONLY;",335 "pred": "SELECT full_name, major, gpa FROM students ORDER BY gpa DESC FETCH FIRST 10 ROWS ONLY;",336 "match": true337 },338 {339 "id": "ex_181c0867e851761a",340 "dialect": "oracle",341 "difficulty": "medium",342 "question": "Write a Oracle query for: Find customers whose city matches %York%.",343 "gold": "SELECT customer_id, customer_name, city FROM customers WHERE city LIKE '%York%';",344 "pred": "SELECT customer_id, customer_name, city FROM customers WHERE city LIKE '%York%';",345 "match": true346 },347 {348 "id": "ex_5c1944bd992a4282",349 "dialect": "oracle",350 "difficulty": "medium",351 "question": "Write a Oracle query for: Count Open tickets grouped by agent_name.",352 "gold": "SELECT a.agent_name, COUNT(*) AS open_tickets FROM tickets t JOIN agents a ON t.agent_id = a.agent_id WHERE t.status = 'Open' GROUP BY a.agent_name;",353 "pred": "SELECT a.agent_name, COUNT(*) AS open_tickets FROM tickets t JOIN agents a ON t.agent_id = a.agent_id WHERE t.status = 'Open' GROUP BY a.agent_name;",354 "match": true355 },356 {357 "id": "ex_4b16a4d788c97596",358 "dialect": "oracle",359 "difficulty": "medium",360 "question": "Write a Oracle query for: Agents who are available.",361 "gold": "SELECT agent_id, agent_name, team FROM agents WHERE is_available = 1;",362 "pred": "SELECT agent_id, agent_name, team FROM agents WHERE is_available = 1;",363 "match": true364 },365 {366 "id": "ex_176cb0a393755a4d",367 "dialect": "oracle",368 "difficulty": "medium",369 "question": "Shipments originating in a city starting with San.",370 "gold": "SELECT shipment_id, tracking_no, origin_city FROM shipments WHERE origin_city LIKE 'San%';",371 "pred": "SELECT shipment_id, tracking_no, origin_city FROM shipments WHERE origin_city LIKE 'San%';",372 "match": true373 },374 {375 "id": "ex_ca3413a258dd3e4e",376 "dialect": "oracle",377 "difficulty": "hard",378 "question": "Write a Oracle query for: WITH clause counting orders per customer, keep count > 3.",379 "gold": "WITH order_counts AS (SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id) SELECT c.customer_name, oc.order_count FROM order_counts oc JOIN customers c ON c.customer_id = oc.customer_id WHERE oc.order_count > 3;",380 "pred": "WITH order_counts AS (SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id) SELECT c.customer_name, oc.order_count FROM order_counts oc JOIN customers c ON c.customer_id = oc.customer_id WHERE oc.order_count > 3;",381 "match": true382 },383 {384 "id": "ex_97741e5236913d36",385 "dialect": "oracle",386 "difficulty": "easy",387 "question": "Best ten gpa values. Return only the SQL.",388 "gold": "SELECT full_name, major, gpa FROM students ORDER BY gpa DESC FETCH FIRST 10 ROWS ONLY;",389 "pred": "SELECT full_name, major, gpa FROM students ORDER BY gpa DESC FETCH FIRST 10 ROWS ONLY;",390 "match": true391 },392 {393 "id": "ex_79eebf63d2b46a2b",394 "dialect": "oracle",395 "difficulty": "hard",396 "question": "Customers who ordered more than three times. Return only the SQL.",397 "gold": "WITH order_counts AS (SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id) SELECT c.customer_name, oc.order_count FROM order_counts oc JOIN customers c ON c.customer_id = oc.customer_id WHERE oc.order_count > 3;",398 "pred": "WITH order_counts AS (SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id) SELECT c.customer_name, oc.order_count FROM order_counts oc JOIN customers c ON c.customer_id = oc.customer_id WHERE oc.order_count > 3;",399 "match": true400 },401 {402 "id": "ex_adbaf24e74fb701a",403 "dialect": "oracle",404 "difficulty": "medium",405 "question": "Customers in cities containing 'York'.",406 "gold": "SELECT customer_id, customer_name, city FROM customers WHERE city LIKE '%York%';",407 "pred": "SELECT customer_id, customer_name, city FROM customers WHERE city LIKE '%York%';",408 "match": true409 },410 {411 "id": "ex_2d820ef2a5909d2a",412 "dialect": "postgres",413 "difficulty": "medium",414 "question": "Today's stock updates. Return only the SQL.",415 "gold": "SELECT stock_id, warehouse_id, item_id, qty_on_hand FROM stock WHERE updated_at::date = CURRENT_DATE;",416 "pred": "SELECT txn_id, warehouse_id, item_id, qty_on_hand, created_at FROM stock WHERE qty_on_hand <= CURRENT_DATE;",417 "match": false418 },419 {420 "id": "ex_d759eda0ae841fa7",421 "dialect": "postgres",422 "difficulty": "easy",423 "question": "Return every customer's name and country. Return only the SQL.",424 "gold": "SELECT customer_name, country FROM customers;",425 "pred": "SELECT customer_name, country FROM customers;",426 "match": true427 },428 {429 "id": "ex_03314b46941553c7",430 "dialect": "oracle",431 "difficulty": "easy",432 "question": "Number of cancelled orders. Return only the SQL.",433 "gold": "SELECT COUNT(*) AS cancelled_count FROM orders WHERE status = 'Cancelled';",434 "pred": "SELECT COUNT(*) AS cancelled_count FROM orders WHERE status = 'Cancelled';",435 "match": true436 },437 {438 "id": "ex_532b3d35b7593fb5",439 "dialect": "oracle",440 "difficulty": "medium",441 "question": "Full name as first_name space last_name.",442 "gold": "SELECT first_name || ' ' || last_name AS full_name FROM employees;",443 "pred": "SELECT first_name || ' ' || last_name AS full_name FROM employees;",444 "match": true445 },446 {447 "id": "ex_8cc46343db1bdc6f",448 "dialect": "oracle",449 "difficulty": "easy",450 "question": "How many 4-credit courses are there?",451 "gold": "SELECT COUNT(*) AS course_count FROM courses WHERE credits = 4;",452 "pred": "SELECT COUNT(*) AS course_count FROM courses WHERE credits = 4;",453 "match": true454 },455 {456 "id": "ex_0149a918a33d3f6e",457 "dialect": "oracle",458 "difficulty": "medium",459 "question": "Write a Oracle query for: origin_city LIKE San%.",460 "gold": "SELECT shipment_id, tracking_no, origin_city FROM shipments WHERE origin_city LIKE 'San%';",461 "pred": "SELECT shipment_id, tracking_no, origin_city FROM shipments WHERE origin_city LIKE 'San%';",462 "match": true463 },464 {465 "id": "ex_6b8663da56b88c3e",466 "dialect": "oracle",467 "difficulty": "easy",468 "question": "Write a Oracle query for: Count orders with status Cancelled.",469 "gold": "SELECT COUNT(*) AS cancelled_count FROM orders WHERE status = 'Cancelled';",470 "pred": "SELECT COUNT(*) AS cancelled_count FROM orders WHERE status = 'Cancelled';",471 "match": true472 },473 {474 "id": "ex_4fe32a3a1acc5588",475 "dialect": "oracle",476 "difficulty": "medium",477 "question": "City names that include York. Return only the SQL.",478 "gold": "SELECT customer_id, customer_name, city FROM customers WHERE city LIKE '%York%';",479 "pred": "SELECT customer_id, customer_name, city FROM customers WHERE city LIKE '%York%';",480 "match": true481 },482 {483 "id": "ex_f508bacbf1d5a900",484 "dialect": "oracle",485 "difficulty": "medium",486 "question": "Write a Oracle query for: Who has a null manager_id?",487 "gold": "SELECT employee_id, first_name, last_name FROM employees WHERE manager_id IS NULL;",488 "pred": "SELECT employee_id, first_name, last_name FROM employees WHERE manager_id IS NULL;",489 "match": true490 },491 {492 "id": "ex_fc5afbbf96202473",493 "dialect": "oracle",494 "difficulty": "medium",495 "question": "How many open tickets does each agent have? Return only the SQL.",496 "gold": "SELECT a.agent_name, COUNT(*) AS open_tickets FROM tickets t JOIN agents a ON t.agent_id = a.agent_id WHERE t.status = 'Open' GROUP BY a.agent_name;",497 "pred": "SELECT a.agent_name, COUNT(*) AS open_tickets FROM tickets t JOIN agents a ON t.agent_id = a.agent_id WHERE t.status = 'Open' GROUP BY a.agent_name;",498 "match": true499 },500 {501 "id": "ex_35082fab89e9b2b4",502 "dialect": "oracle",503 "difficulty": "medium",504 "question": "Write a Oracle query for: Find staff with last_name beginning with s ignoring case.",505 "gold": "SELECT employee_id, first_name, last_name FROM employees WHERE UPPER(last_name) LIKE 'S%';",506 "pred": "SELECT employee_id, first_name, last_name FROM employees WHERE last_name LIKE ' ' || first_name;",507 "match": false508 },509 {510 "id": "ex_aa62e801ddf83284",511 "dialect": "postgres",512 "difficulty": "easy",513 "question": "Suppliers ordered by lead time ascending.",514 "gold": "SELECT supplier_id, supplier_name, lead_time_days FROM suppliers ORDER BY lead_time_days ASC;",515 "pred": "SELECT supplier_id, supplier_name, lead_time_days FROM suppliers ORDER BY lead_time_days ASC;",516 "match": true517 },518 {519 "id": "ex_2b281aa43a7b6fb1",520 "dialect": "oracle",521 "difficulty": "easy",522 "question": "Write a Oracle query for: Count courses where credits = 4.",523 "gold": "SELECT COUNT(*) AS course_count FROM courses WHERE credits = 4;",524 "pred": "SELECT COUNT(*) AS course_count FROM courses WHERE credits = 4;",525 "match": true526 },527 {528 "id": "ex_c5783841911652e9",529 "dialect": "oracle",530 "difficulty": "medium",531 "question": "Accounts with more than 20 transactions.",532 "gold": "SELECT account_id, COUNT(*) AS txn_count FROM transactions GROUP BY account_id HAVING COUNT(*) > 20;",533 "pred": "SELECT account_id, COUNT(*) AS txn_count FROM transactions GROUP BY account_id HAVING COUNT(*) > 20;",534 "match": true535 },536 {537 "id": "ex_d2f17d616d77d8be",538 "dialect": "oracle",539 "difficulty": "medium",540 "question": "Last names that start with the letter S, any case. Return only the SQL.",541 "gold": "SELECT employee_id, first_name, last_name FROM employees WHERE UPPER(last_name) LIKE 'S%';",542 "pred": "SELECT employee_id, SUBSTR(last_name, 1, 3) AS name_prefix FROM employees;",543 "match": false544 }545 ]546}