DEV Community: David

14 examples of ready-to-use SQL statements

David — Wed, 25 Oct 2023 02:39:44 +0000

SQL 1:

Query all rows from the "ns_active_ip" table in the "idc_evaluating" database where the province code is 110000.

select
 *
from
 idc_evaluating.ns_active_ip
where
 province_code = '110000';

SQL 2:

Query all rows from the "ns_active_ip_udp" table in the "idc_evaluating" database where the destination IP column value contains the specified IP addresses (IP_1, IP_2, IP_3).

select
 *
from
 idc_evaluating.ns_active_ip_udp
where
 dest_ip in ('IP_1', 'IP_2', 'IP_3');

SQL 3:

Query all rows from the "ns_active_ip_udp_record" table in the "idc_evaluating" database where the destination IP column value contains the specified IP addresses (IP_1, IP_2, IP_3, IP_4, IP_5).

select
 *
from
 idc_evaluating.ns_active_ip_udp_record
where
 dest_ip in ('IP_1', 'IP_2', 'IP_3', 'IP_4', 'IP_5');

SQL 4:

Query the total number of active IP addresses that satisfy the condition from the "ns_active_ip" table in the "idc_evaluating" database, where the province code is 110000 and the facility code is 1024. Rename the result column header as "Total Active IP".

select
 count(*) as Total_Active_IP
from
 idc_evaluating.ns_active_ip
where
 province_code = '110000'
 and house_code = '1024';

SQL 5:

Delete all active IP address data from the "ns_active_ip" table in the "idc_evaluating" database that matches the province code 110000 and facility code 1024.

delete
from
 idc_evaluating.ns_active_ip
where
 province_code = '110000'
 and house_code = '1024';

SQL 6:

Retrieve the table structure for the "ns_active_ip_udp" table in the "idc_evaluating" database.

describe idc_evaluating.ns_active_ip_udp;

show columns
from
idc_evaluating.ns_active_ip_udp;

SQL 7:

Query the count of rows that satisfy the conditions of the verify_id (task ID) and status fields from the "ns_active_ip_udp" table in the "idc_evaluating" database. Rename the result column header as "Count".

select
 count(*) as Count
from
 idc_evaluating.ns_active_ip_udp
where
 verify_id = '1024'
 and status = '0';

SQL 8:

Retrieve all rows from the "ns_active_ip_udp" table in the "idc_evaluating" database that satisfy the conditions of a single verify_id (task ID).

select
 *
from
 idc_evaluating.ns_active_ip_udp
where
 verify_id = '1024';

SQL 9:

Retrieve all rows from the "ns_active_ip_udp" table in the "idc_evaluating" database that satisfy the conditions of multiple verify_id (task ID).

select
 *
from
 idc_evaluating.ns_active_ip_udp
where
 verify_id in ('1024', '2048');

SQL 10:

Query the count that satisfies the condition of a single verify_id (task ID) from the "ns_active_ip_udp_record" table in the "idc_evaluating" database. Rename the result column header as "Total Attacks".

select
 count(*) as Total_Attacks
from
 idc_evaluating.ns_active_ip_udp_record naiur
where
 verify_id = '1024';

SQL 11:

Query the count that satisfies the condition of multiple verify_id (task ID) from the "ns_active_ip_udp_record" table in the "idc_evaluating" database. Rename the result column header as "Total Attacks".

select
 count(*) as Total_Attacks
from
 idc_evaluating.ns_active_ip_udp_record naiur
where
 verify_id in ('1024', '2048');

SQL 12:

Retrieve data from two tables using an inner join and return unique values of Instruction ID, Destination IP, Number of Attacks, and Attack Status that satisfy specific conditions. These conditions include the Instruction ID being within a specified range and the request_id matching in both tables.

select
 distinct ncl.command_id as Cmd_id,
 naiu.dest_ip as Dest_IP,
 naiu.attacks_count as Count_Attacks,
 naiu.status as Attack_Status
from
 idc_evaluating.ns_active_ip_udp as naiu
inner join idc_evaluating.ns_command_log as ncl
on
 naiu.request_id = ncl.request_id
where
 ncl.command_id between '1024' and '2048';

SQL 13:

The purpose of this SQL statement is to get half the value of the total number of attacks within a specified command_id range as the total number of attacks.

select
 distinct SUM(naiu.attacks_count) / 2 as Total_Attacks
from
 idc_evaluating.ns_active_ip_udp as naiu
inner join idc_evaluating.ns_command_log as ncl
on
 naiu.request_id = ncl.request_id
where
 ncl.command_id between '1024' and '2048';

SQL 14:

The purpose of this SQL statement is to retrieve a series of records within a specific command_id range and calculate the number of attacks multiplied by 0.9, then round it to the nearest integer and add 1. Finally, return these processed records along with their Instruction ID, Issued Time, Destination IP, Number of Attacks, Attack Time, Attack Status, and Number of Log Data.

select
 distinct
    ncl.command_id as Cmd_id,
 naiu.create_time as Cmd_create_time,
 naiu.dest_ip as dest_ip,
 naiu.attacks_count as Count_Attacks,
 DATE_ADD(naiu.create_time, interval 10 minute) as Count_Attacks,
 naiu.status as Attack_Status,
 ROUND(
    case
      when naiu.attacks_count is not null then naiu.attacks_count * 0.9
      else null
    end,
    0
  ) + 1 as log_rows
from
 idc_evaluating.ns_active_ip_udp as naiu
inner join idc_evaluating.ns_command_log as ncl
on
 naiu.request_id = ncl.request_id
where
 ncl.command_id between '1024' and '2048';

The Current State and Evaluation of Text-to-SQL

David — Tue, 24 Oct 2023 07:13:39 +0000

The role of Data Analysts and Business Intelligence Analysts often involves translating business questions into SQL queries, acting as intermediaries between humans and computers. However, advancements in Natural Language Processing (NLP) and Large Language Models (LLMs) could potentially replace analysts with language models. This would eliminate the need for human manpower and allow access to databases without requiring expert SQL knowledge.

The latest Text-to-SQL models have achieved impressive accuracy rates. For example, a state-of-the-art model achieved a 79.1% execution accuracy and a 97.8% valid SQL ratio when evaluated on the Spider development set[¹]. In comparison, OpenAI's Codex davinci, even without fine-tuning, achieved a 67.0% execution accuracy and a 91.6% valid SQL ratio. If a language model can generate accurate SQL queries without any corrections, its performance may even surpass that of a human. This highlights the potential advantage of using language models, as they can quickly provide valid SQL queries and desired statistics, which might not always be achievable for humans on their first attempt.

With multiple approaches and solutions flooding the market, we are left with the problem of evaluation. Which approach is most efficient? Which one more reliably produces accurate answers? Which one adapts to different datasets best? To help answer these questions, the open-source industry and academia put forth several benchmarks, but the three most used today are:

WikiSQL
Spider
BIRD (BIg Bench for LaRge-scale Database Grounded Text-to-SQL Evaluation)

WikiSQL

Introduced by Salesforce in late 2017, WikiSQL was the first truly large compendium of data built for the text-to-SQL use case. However, it has one major drawback: simplicity.

All of the provided SQL queries are exceedingly simple, with only SELECT, FROM, and WHERE clauses. Furthermore, the tables in the dataset have no linkages to other tables. Although models trained on WikiSQL can still work on new databases, they can only answer simple natural language questions that then translate into simple SQL queries.

For this reason, most of the recent research in the world of text-to-SQL focuses on more complex benchmarks. In fact, the WikiSQL leaderboard only has submissions from 2021 or earlier. With multiple submissions achieving a test accuracy of over 90% (with the best-performing submission reaching 93%), practitioners are now shifting focus to much more complex query generation, for which WikiSQL falls woefully short.

Spider

The Spider dataset aims to cover some of the shortcomings of the WikiSQL dataset. Developed through the efforts of 11 Yale students spending over 1,000 man hours, the Spider dataset introduces two critical elements: complexity and cross-domainality.

Complexity: The SQL queries go beyond the straightforward SELECT and WHERE clauses that WikiSQL is limited to, covering the more complex GROUP BY, ORDER BY, and HAVING clauses along with nested queries. Furthermore, all databases have multiple tables linked through foreign keys, allowing for complicated queries that join across tables.
Cross-domainality: With 200 complex databases across a high number of domains, Spider is able to include unseen databases in the test set, allowing us to test the model’s generalizability.

Evaluation of different submissions incorporates the following:

Component Matching: Do the individual components of the SELECT, WHERE, GROUP BY, and ORDER BY clauses match? Are the extracted KEYWORDS correct?
Exact Matching: Do all of the above components match exactly?
Execution Accuracy: Is the answer correct?
SQL Hardness: Queries are divided into four levels (easy, medium, hard, and extra hard) and weighted accordingly for the final evaluation.

There are a few variations of the Spider dataset that are used to evaluate the robustness and generalizability of models under different perturbations, such as Spider-Syn (used to test how well text-to-SQL models adapt to synonym substitution) and Spider-DK (tests how well text-to-SQL models incorporate added domain knowledge).

BIRD

This dataset was compiled by researchers from multiple global institutions to be more realistic than WikiSQL and Spider.

Because the data was collected from real-world scenarios, they retain their original, “dirty” format.
It also provides external knowledge, similar to how real-world developers may have external knowledge from metadata, docs, or other existing context stores.

The BIRD dataset also underscores the importance of efficient queries. The evaluation method for BIRD is the first to include a Valid Efficiency Score (VES), a new metric designed to measure the efficiency along with the usual execution correctness of a provided SQL query.

Conclusion

Text-to-SQL is an intriguing field that holds significant promise for both human-computer interaction research and practical business applications. While advancements in Large Language Models (LLMs) have provided some progress, particularly in handling simple questions, they have only scratched the surface when it comes to complex problems like text-to-SQL. Currently, no existing solution in the market can rival human performance, even when dealing with slightly more intricate queries. However, despite this limitation, the future of text-to-SQL remains exciting, with ample opportunities for further exploration and development.

Text2SQL: Converting Natural Language to SQL

David — Wed, 11 Oct 2023 06:36:55 +0000

Abstract | Text2SQL is a natural language processing technique aimed at converting natural language expressions into structured query language (SQL) for interaction and querying with databases. This article presents the historical development of Text2SQL, the latest advancements in the era of large language models (LLMs), discusses the major challenges currently faced, and introduces some outstanding products in this field.

History of Text2SQL

The development of Text2SQL can be traced back to the early 1960s when research primarily focused on rule-based approaches. These approaches relied on manually crafted grammar rules and templates to convert natural language queries into SQL queries. However, these methods had limited scalability and adaptability, requiring a large number of rules and templates for complex queries, making them difficult to maintain and expand.

With the advancement of machine learning and natural language processing, statistical and machine learning-based methods emerged. Researchers began using corpus data and machine learning algorithms to build Text2SQL models. These models automatically convert natural language queries into SQL queries by learning the correspondence between language and databases. However, early methods were still limited by data size and model complexity, resulting in limited performance.

The Latest Advancements of Text2SQL in the LLM Era

In the era of large language models (LLMs), Text2SQL has made significant progress. The emergence of large pre-trained language models like BERT and GPT has brought new possibilities to Text2SQL. These models, trained on massive corpora, can understand more complex language structures and contexts and possess powerful representation capabilities.

The latest Text2SQL methods utilize LLM models for end-to-end training and inference. These models learn the mapping between natural language queries and their corresponding SQL queries by taking them as input and output pairs. The representation and contextual understanding abilities of LLM models significantly enhance the performance of Text2SQL, enabling the handling of more complex queries and achieving excellent results on multiple benchmark datasets.

Major Challenges of Text2SQL at Present

Despite the significant progress made in Text2SQL, there are still challenges and issues that need to be addressed. Some of these challenges include:

Data scarcity: Text2SQL models typically require a large amount of annotated data for training, which can be expensive and time-consuming to obtain.
Query diversity: Real-world natural language queries exhibit high diversity, and Text2SQL models may struggle with handling diverse queries.
Complex queries: Some complex queries require models to possess stronger reasoning and inference capabilities, and current models still have limitations in handling such queries.

Prominent Products in the Field

Currently, there are several remarkable products and systems in the Text2SQL field, including:

Microsoft LayoutLM: LayoutLM is a pre-trained model-based Text2SQL system that focuses on handling documents containing tables and structured information. It has achieved excellent results in various document layout understanding and query transformation tasks.
Google TAPAS: TAPAS is a pre-trained model-based Text2SQL system that specializes in working with tabular data. It can take natural language questions and convert them into SQL queries to search for answers within tables. TAPAS excels in tasks involving natural language interaction with tables and demonstrates leading performance on multiple benchmark datasets.
Stanford Spider: Spider is a pre-trained model-based Text2SQL system with an end-to-end training and inference framework. It performs well in handling complex and diverse queries and has achieved outstanding results in the Text2SQL challenge.
GuruSQL: GuruSQL is a Text2SQL tool leveraging the capabilities of OpenAI / Google Vertex’s large language models. It is currently available for free and can generate complex SQL queries, save them, and establish table structures necessary for query generation. It supports ANSI SQL, MySQL, PostgreSQL, ClickHouse, BigQuery, and other databases. It’s completely FREE and revolutionizes your SQL experience. Say goodbye to manual query building!

Conclusion:
As a cross-disciplinary field between natural language processing and database querying, Text2SQL has undergone development from rule-based to statistical and machine learning-based approaches and has made significant progress in the era of LLMs. Despite some remaining challenges, with continued technological advancements and improvements, Text2SQL has the potential to play a larger role in practical applications, providing users with more convenient and intelligent database querying experiences.