Šiais laikais, kai praktiškai visų sričių „popieriniai“ duomenys jau baigia išnykti, didelę dalį IT visuomenėje sudaro vienokio ar kitokio tipo duomenų bazės. Paprastam vartotojui, kuris naudojasi tik vartojamojo tipo programomis, SQL užklausų mokėjimas nėra reikalingas, tačiau bet kuris programuotojas, kuris dirba su duomenimis, savo darbo ko gero nebeįsivaizduoja be duomenų bazių ir SQL užklausų. SQL užklausa (SQL Query) – tai užklausa, atliekama SQL kalbos komandų pagalba. SQL (Structured Query Language) kalba – struktūrizuota užklausų kalba, skirta duomenų, esančių duomenų bazėje apdorojimui. SQL kalba dirba tik su reliacinėmis DB. Vartotojas SQL pagalba kreipiasi į DBVS, kuri apdoroja užklausą, randa reikalingus duomenis ir pateikia juos vartotojui. SQL nėra nei DBVS, nei atskiras programinis produktas, tai yra neatsiejama DBVS dalis, instrumentas, kurio pagalba realizuojamas vartotojo ryšys su DBVS. SQL kalbos lankstumas ir nepriklausomumas nuo kompiuterinių technologijų specifikos, o taip pat jos palaikymas pagrindiniais lyderiais reliacinių duomenų bazių technologijų srityje padarė SQL kalbą pagrindine standartine duomenų bazių programavimo kalba [1]. SQL užklausos yra naudojamos visose dabartinėse duomenų bazėse, tokiose kaip MsSQL, MySQL, Firebird ir kitose. SQL sakiniai gali būti įterpiami į programas, sudaromas bazine programavimo kalba. Taigi SQL užklausos yra naudojamos visur, kur yra naudojamos ir duomenų bazės. Tą pačią SQL užklausą... [toliau žr. visą tekstą] / Nowadays, when “paper” data practically from all the scopes is on the edge of extinction, big part of IT society is made of one or another type of databases. SQL queries are used to work with database information. SQL (Structured Query Language) is designed for database information processing. For fast systems, related with databases operation, it is necessary to correctly write and optimize SQL queries. To learn write SQL queries correctly and optimize them, only the theoretical knowledge is not enough, it is necessary to do a lot of practical tasks. When learning to write SQL queries, basic problem is poor choice of practical tasks, and non-existence of good testing system, which allows writing SQL queries easily. On the job the existent SQL learning systems and learning materials analysis were performed, all found systems has its own problems, full freedom to write SQL queries for user is not granted. During designing, methods allowing for learner easily write various types of queries, view executed queries results and get the result if a written query is correct, were designed. Designed methods were used in remote SQL queries learning system design and implementation. Aim: The algorithms which are developed and implemented allows to test sql queries based on simple syntax and allows to execute queries of the different types. The object of research: Execution of various SQL queries.

This dissertation presents a novel methodology based on test summaries, which characterize desired tests as constraints written in a mixed imperative and declarative notation, for automated systematic testing of relational applications, such as relational database engines. The methodology has at its basis two novel techniques for effective and efficient testing: (1) mixed-constraint solving, which provides systematic generation of inputs characterized by mixed-constraints using translations among different data domains; and (2) clustered test execution, which optimizes execution of test suites by leveraging similarities in execution traces of different tests using abstract-level undo operations, which allow common segments of partial traces to be executed only once and the execution results to be shared across those tests. A prototype embodiment of the methodology enables a novel approach for systematic testing of commonly used database engines, where test summaries describe (1) input SQL queries, (2) input database tables, and (3) expected output of query execution. An experimental evaluation using the prototype demonstrates its efficacy in systematic testing of relational applications, including Oracle 11g, and finding bugs in them. / text

Dans cette thèse, nous nous intéressons aux proportions analogiques dans le contexte des bases de données relationnelles. Les proportions analogiques permettent de lier quatre éléments dans une relation du type ''A est à B ce que C est à D''. Par exemple, « Paris est à la France ce que Rome est à l'Italie ». Nous avons étudié le problème de la prédiction de valeurs manquantes dans une base de données en utilisant les proportions analogiques. Un algorithme de classification fondé sur les proportions analogiques a été modifié afin de résoudre ce problème. Puis, nous avons étudié les propriétés des éléments appartenant à l'ensemble d'apprentissage des classificateurs analogiques fréquemment exploités pour calculer la prédiction. Ceci nous a permis de réduire considérablement la taille de cet ensemble par élimination des éléments peu pertinents et par conséquent, de diminuer les temps d'exécution de ces classificateurs. La deuxième partie de la thèse a pour objectif de découvrir de nouveaux patrons basés sur la relation d'analogie, i.e., des parallèles, dans les bases de données. Nous avons montré qu'il est possible d'extraire ces patrons en s'appuyant sur des approches de clustering. Les clusters produits par de telles techniques présentent aussi un intérêt pour l'évaluation de requêtes recherchant des patrons d'analogie dans les bases de données. Dans cette perspective, nous avons proposé d'étendre le langage de requêtes SQL pour pouvoir trouver des quadruplets d'une base de données satisfaisant une proportion analogique. Nous avons proposé différentes stratégies d'évaluation pour de telles requêtes, et avons comparé expérimentalementleurs performances. / In this thesis, we are interested in the notion of analogical proportions in a relational database context. An analogical proportion is a statement of the form “A is to B as C is to D”, expressing that the relation beween A and B is the same as the relation between C and D. For instance, one may say that “Paris is to France as Rome is to Italy”. We studied the problem of imputing missing values in a relational database by means of analogical proportions. A classification algorithm based on analogical proportions has been modified in order to impute missing values. Then, we studied how analogical classifiers work in order to see if their processing could be simplified. We showed how some typeof analogical proportions is more useful than the others when performing classification. We then proposed an algorithm using this information, which allowed us to considerably reduce the size of the training set used by the analogical classificationalgorithm, and hence to reduce its execution time. In the second part of this thesis, we payed a particular attention to the mining of combinations of four tuples bound by an analogical relationship. For doing so, we used several clustering algorithms, and we proposed some modifications to them, in order tomake each obtained cluster represent a set of analogical proportions. Using the results of the clustering algorithms, we studied how to efficiently retrieve the analogical proportions in a database by means of queries. For doing so, we proposed to extend the SQL query language in order to retrieve from a database the quadruples of tuples satisfying an analogical proportion. We proposed severalquery evaluation strategies and experimentally compared their performances.

Distributed Smart Camera (DSC) networks are power constrained real-time distributed embedded systems that perform computer vision using multiple cameras. Providing data aggregation techniques that is criti-cal for running complex image processing algorithms on DSCs is a challenging task due to complexity of video and image data. Providing highly desirable SQL APIs for sophisticated query processing in DSC networks is also challenging for similar reasons. Research on DSCs to date have not addressed the above two problems. In this thesis, we develop a novel SOA based middleware framework on a DSC network that uses Distributed OSGi to expose DSC network services as web services. We also develop a novel web service composition scheme that aid in data aggregation and a SQL query interface for DSC net-works that allow sophisticated query processing. We validate our service orchestration concept for data aggregation by providing query primitive for face detection in smart camera network.

This master's thesis deals with optimization of structured queries on large databases. Principles of these optimizations are used during creation of application, which allows finding over one specific large database. At the same time this thesis compares efficiency between the new designed SQL constructions and the not optimized SQL constructions.

Stream processing systems have been widely adopted in applications such as recommendation systems, anomaly detection, and system monitoring due to their real-time capabilities. Improving observability in stream processing systems can further expand their application scenarios, including the implementation of stateful serverless applications. Stateful serverless applications are an emerging model in serverless computing that focuses on addressing the challenges of state management, enabling developers to build distributed applications in a simpler way. One possible implementation of stateful serverless applications is based on stream processing engines. However, the current approaches for observability in stream processing engines suffer from issues such as efficiency, consistency, and functionality, resulting in limited practical use cases. To address these challenges, we propose Queryable Workflow, an extension to stream processing engines. This extension allows users to access or modify the state within stream processing engines with transactional semantics using a SQL interface, enabling use cases such as ad-hoc querying, serializable updates, or even stateful serverless applications. We implemented our system on stream processing engines such as Portals and Apache Flink, and evaluated their performance. The result showed that our system has achieved 4.33x throughput improvement and 30% latency reduction compared to a baseline implemented with Apache Flink and Apache Kafka. With hand-crafted optimizations, our system achieved to process over 29,000 queries per second with a 99th percentile latency of 8.58 ms under a single-threaded runtime. Our proposed system provides a viable option for implementing stateful serverless applications that require transactional guarantees, while also expanding the potential application scenarios for stream processing engines. / Strömbehandlingssystem har på grund av sina realtidsegenskaper fått stor spridning i tillämpningar som rekommendationssystem, anomalidetektering och systemövervakning. Förbättrad observerbarhet i stream processing-system kan ytterligare utöka deras tillämpningsscenarier, inklusive implementeringen av stateful serverless-applikationer. Stateful serverless-applikationer är en framväxande modell inom serverless computing som fokuserar på att hantera utmaningarna med tillståndshantering, vilket gör det möjligt för utvecklare att bygga distribuerade applikationer på ett enklare sätt. En möjlig implementering av stateful serverless-applikationer är baserad på stream processing-motorer. De nuvarande metoderna för observerbarhet i strömbehandlingsmotorer lider dock av problem som effektivitet, konsistens och funktionalitet, vilket resulterar i begränsade praktiska användningsfall. För att ta itu med dessa utmaningar föreslog vi Queryable Workflow, ett tillägg till stream processing-motorer. Med detta tillägg kan användare komma åt eller ändra tillståndet i strömbehandlingsmotorer med transaktionssemantik med hjälp av ett SQL-gränssnitt, vilket möjliggör användningsfall som ad hoc-förfrågningar, serialiserbara uppdateringar eller till och med serverlösa applikationer med tillstånd. Vi implementerade vårt system på stream processing-motorer som Portals och Apache Flink, och utvärderade deras prestanda. Resultatet visade att vårt system har förbättrat genomströmningen 4,33 gånger och minskat latensen med 30% jämfört med en baslinje som implementerats med Apache Flink och Apache Kafka. Med handgjorda optimeringar lyckades vårt system bearbeta över 29 000 frågor per sekund med en 99:e percentil latens på 8,58 ms under en enkeltrådad körtid. Vårt föreslagna system har gett ett hållbart alternativ för att implementera stateful serverless-applikationer som kräver transaktionsgarantier, samtidigt som det också utökat de potentiella applikationsscenarierna för stream processing-motorer.

En la actualidad, el uso de las tecnologías ha sido primordial para el avance de las sociedades, estas han permitido que personas sin conocimientos informáticos o usuarios llamados “no expertos” se interesen en su uso, razón por la cual los investigadores científicos se han visto en la necesidad de producir estudios que permitan la adaptación de sistemas, a la problemática existente dentro del ámbito informático. Una necesidad recurrente de todo usuario de un sistema es la gestión de la información, la cual se puede administrar por medio de una base de datos y lenguaje específico, como lo es el SQL (Structured Query Language), pero esto obliga al usuario sin conocimientos a acudir a un especialista para su diseño y construcción, lo cual se ve reflejado en costos y métodos complejos, entonces se plantea una pregunta ¿qué hacer cuando los proyectos son pequeñas y los recursos y procesos son limitados? Teniendo como base la investigación realizada por la universidad de Washington[39], donde sintetizan sentencias SQL a partir de ejemplos de entrada y salida, se pretende con esta memoria automatizar el proceso y aplicar una técnica diferente de aprendizaje, para lo cual utiliza una aproximación evolucionista, donde la aplicación de un algoritmo genético adaptado origina sentencias SQL válidas que responden a las condiciones establecidas por los ejemplos de entrada y salida dados por el usuario. Se obtuvo como resultado de la aproximación, una herramienta denominada EvoSQL que fue validada en este estudio. Sobre los 28 ejercicios empleados por la investigación [39], 23 de los cuales se obtuvieron resultados perfectos y 5 ejercicios sin éxito, esto representa un 82.1% de efectividad. Esta efectividad es superior en un 10.7% al establecido por la herramienta desarrollada en [39] SQLSynthesizer y 75% más alto que la herramienta siguiente más próxima Query by Output QBO[31]. El promedio obtenido en la ejecución de cada ejercicio fue de 3 minutos y 11 segundos, este tiempo es superior al establecido por SQLSynthesizer; sin embargo, en la medida un algoritmo genético supone la existencia de fases que amplían los rangos de tiempos, por lo cual el tiempo obtenido es aceptable con relación a las aplicaciones de este tipo. En conclusión y según lo anteriormente expuesto, se obtuvo una herramienta automática con una aproximación evolucionista, con buenos resultados y un proceso simple para el usuario “no experto”. / Actuellement l'usage des technologies est primordial pour l'avance de la société, celles-ci ont permis que des personnes sans connaissances informatiques ou des utilisateurs appelés "non expert" s'intéressent à son usage. C'est la raison pour laquelle les enquêteurs scientifiques se sont vus dans la nécessité de produire les études qui permettent l'adaptation des systèmes à la problématique existante à l'intérieur du domaine informatique. Une nécessité récurrente pour tout utilisateur d'un système est la gestion de l'information, que l’on peut administrer au moyen d'une base de données et de langage spécifique pour celles-ci comme est le SQL (Structured Query Language), mais qui oblige à l'utilisateur à chercher un spécialiste pour sa conception et sa construction, et qui représente des prix et des méthodes complexes. Une question se pose alors, quoi faire quand les projets sont petites et les ressources et les processus limités ? Ayant pour base la recherche de l'université de Washington [39], ce mémoire automatise le processus et applique une différente technique d'apprentissage qui utilise une approche évolutionniste, où l'application d'un algorithme génétique adapté génère des requêtes SQL valides répondant aux conditions établies par les exemples d'entrée et de sortie donnés par l'utilisateur. On a obtenu comme résultat de l’approche un outil dénommé EvoSQL qui a été validé dans cette étude. Sur les 28 exercices employés par la recherche [39], 23 exercices ont été obtenus avec des résultats parfaits et 5 exercices sans succès, ce qui représente 82.1 % d'effectivité. Cette effectivité est supérieure de 10.7 % à celle établie par l'outil développé dans [32] SQLSynthesizer et 75% plus haute que l'outil suivant le plus proche Query by Output QBO [31]. La moyenne obtenue dans l'exécution de chaque exercice a été de 3 min et 11sec, ce qui est supérieur au temps établi par SQlSynthesizer, cependant dans la mesure où un algorithme génétique suppose que l'existence de phases augmente les rangs des temps, le temps obtenu est acceptable par rapport aux applications de ce type. Dans une conclusion et selon ce qui a été antérieurement exposé nous avons obtenu un outil automatique, avec une approche évolutionniste, avec de bons résultats et un processus simple pour l'utilisateur « non expert ». / At present the use of the technologies is basic for the advance of the society; these have allowed that persons without knowledge or so called "non expert" users are interested in this use, is for it that the researchers have seen the need to produce studies that allow the adjustment of the systems the existing at the problematic inside the area of the technology. A need of every user of a system is the management of the information, which can be manage by a database and specific language for these as the SQL (Structured Query Language), which forces the user to come to a specialist for the design and construction of this one, which represents costs and complex methods, but what to do when they are small investigations where the resources and processes are limited? Taking as a base the research of the university of Washington [32], this report automates the process and applies a different learning technique, for which uses an evolutionary approach, where the application of a genetic adapted algorithm generates query SQL valid that answer to the conditions established by the given examples of entry and exit given by the user. There was obtained as a result of the approach a tool named EvoSQL that was validated in the same 28 exercises used by the investigation [32], of which 23 exercises were obtained by ideal results and 5 not successful exercises, which represents 82.1 % of efficiency, superior in 10.7 % to the established one for the tool developed in [32] SQLSynthesizer and 75% higher than the following near tool Query by Output QBO [26]. The average obtained in the execution of every exercise was of 3 min and 11seg that is superior to the time established by SQlSynthesizer, Nevertheless, being a genetic algorithm where the steps existence makes that the ranges of times are extended, the obtained one is acceptable with relation to the applications of this type. In conclusion et according to previously exposed, we have obtained an automatic tool, with an evolutionary approach, with good results and a simple process for the « not expert » user.