SQL

SQL dilinin yeteneklerinin sınırlı olması sebebiyle, SQL üzerine çeşitli iyileştirmeler ve eklemeler yapılmıştır. Microsoft kendi platformu için SQL üzerine yaptığı iyileştirmeleri standart haline getirmiş ve  T-SQL ismini vermiştir. Transact-SQL‘in kısaltması olan T-SQL günümüz veritabanı yönetim ihtiyaçlarının tamamını karşılayabilecek yeterliliğe sahiptir. Bu makalede ayrıca temel T-SQL komutları açıklanmış ve örneklere yer verilmiştir.

— SQL Injection Attack (SQLIA) is a method with help of attackers attack the data directly into the database in an unofficial way and accomplish the maximum important information for remove and modifying information from any corporation. In this paper, we examine the state of the separate types of attacks with explanation and illustration of how attacks of that type can be performed and describe their detection and prevention system.It is also describe the strength and weakness of SQL injection attack.It is well-known to all that SQL injection attacks can be easily prevented by applying more secure schemes in login phase and after login phase. Hence, we accomplish our proposed scheme through SQLENCP, the SQL injection prevention by cryptography using hashing technique, to handle the SQLIA and prevent them. Even though, the planned implemented system is unable to handle all the SQL injection attacks, but it can avoid tautology attacks, union based query attacks & illegal structured q...

We present the SQL Performance Analyzer, a novel approach in Oracle Database 11g to testing database changes, such as upgrades, parameter changes, schema changes, and gathering optimizer statistics. The SQL Performance Analyzer offers a comprehensive ...

Intelligent interface, to enhance efficient interactions between user and databases, is the need of the database applications. Databases must be intelligent enough to make the accessibility faster. However, not every user familiar with the Structured Query Language (SQL) queries as they may not aware of structure of the database and they thus require to learn SQL. So, non-expert users need a system to interact with relational databases in their natural language such as English. For this, Database Management System (DBMS) must have an ability to understand Natural Language (NL). In this research, an intelligent interface is developed using semantic matching technique which translates natural language query to SQL using set of production rules and data dictionary. The data dictionary consists of semantics sets for relations and attributes. A series of steps like lower case conversion, tokenization, speech tagging, database element and SQL element extraction is used to convert Natural Language Query (NLQ) to SQL Query. The transformed query is executed and the results are obtained by the user. Intelligent Interface is the need of database applications to enhance efficient interaction between user and DBMS.

With the ubiquity and pervasiveness of mobile computing, together with the increasing number of social networks, end-users have learned to live and share all kinds of information about themselves. As an example, Facebook reports that it has currently 500 million active users, 200 million of which access its services on mobile systems; moreover, users that access Facebook through mobile applications are twice as active as non-mobile users, and it is used by 200 mobile operators in 60 countries [1]. More specific mobile platforms such as Foursquare, which unlike Facebook only collects location information, reports 6.5 million users worldwide, and also has a mobile presence (both with a web application and iPhone / Android applications) [2]. Contextaware architectures intend to explore this increasing number of context information sources and provide richer, targeted services to end-users, while also taking into account arising privacy issues. While multiple context management platform...

Multi-engine analytics has been gaining an increasing amount of attention from both the academic and the industrial community as it can successfully cope with the heterogeneity and complexity that the plethora of frameworks, technologies and requirements have brought forth. It is now common for a data analyst to combine data that resides on multiple and totally independent engines and perform complex analytics queries. Multi-engine solutions based on SQL can facilitate such efforts, as SQL is a popular standard that the majority of data-scientists understands. Existing solutions propose a middleware that centrally optimizes query execution for multiple engines. Yet, this approach requires manual integration of every primitive engine operator along with its cost model, rendering the process of adding new operators or engines highly inextensible. To address this issue we present MuSQLE, a system for SQL-based analytics over multi-engine environments. MuSQLE can efficiently utilize external SQL engines allowing for both intra and inter engine optimizations. Our framework adopts a novel API-based strategy. Instead of manual integration, MuSQLE specifies a generic API, used for the cost estimation and query execution, that needs to be implemented for each SQL engine endpoint. Our engine API is integrated with a state-of-the-art query optimizer, adding support for location-based, multi-engine query optimization and letting individual runtimes perform sub-query physical optimization. The derived multi-engine plans are executed using the Spark distributed execution framework. Our detailed experimental evaluation, integrating PostgreSQL, MemSQL and SparkSQL under MuSQLE, demonstrates its ability to accurately decide on the most suitable execution engine. MuSQLE can provide speedups of up to 1 order of magnitude for TPCH queries, leveraging different engines for the execution of individual query parts.

This paper presents an approach to convert the Natural language processing (NLP) abbreviation to SQL. SQL is domain specific language which is used for storing, manipulating and retrieving data stored in relational database, but any ordinary person who don't have any knowledge about SQL are unable to retrieve the data from database. To overcome this problem we proposed a model conversion of NLP to SQL but in this system user can also use an abbreviations to retrieve the data from the database. Those who are expert in SQL language can access information from database but non-technical user cannot retrieve data from database. This system can be used everywhere because database is everywhere. If we want some data from database this system is very much useful. In this system user can also enter the query using speech.

Appunti del corso di basi di Dati 2 edizione 2014/2015 dell'Università degli Studi di Genova (corso di laurea in informatica).



DISCLAIMER: questi appunti li ho presi seguendo le lezioni del corso. Sicuramente contengono errori ed imprecisioni (e mi fareste un favore a segnalarmeli quando li trovate ;) ). Non sono assolutamente da considerarsi materiale fornito, controllato od approvato in alcun modo dall'Università degli Studi di Genova né dai suoi professori.

This document is provided "as‐is". Information and views expressed in this document, including URL and other Internet Web site references, may change without notice.
This document does not provide you with any legal rights to any intellectual property in any Microsoft product or product name. You may copy and use this document
for your internal, reference purposes. You may modify this document for your internal, reference purposes.© 2015 Microsoft. All rights reserved. Terms of Use
﴾https://technet.microsoft.com/cc300389.aspx﴿ | Trademarks ﴾http://www.microsoft.com/library/toolbar/3.0/trademarks/en‐us.mspx﴿

The aim of the project was to design a database in Oracle SQL Developer to provide the functional requirements of patient in Hospital management system.
The project requirements included:
• Describe data using an Entity Relationship Diagram (ERD) including primary and foreign keys, constraints, attributes, relationship cardinality and assumptions where necessary
• Using Oracle and SQL create the database entities required to support the data storage needs of the Hospital management system
• Populate each entity with records
• Create reports for management (using SQL) according to outlined requirements
• Describe major considerations  to securing patient data using Oracle

Intelligent interface, to enhance efficient interactions between user and databases, is the need of the database applications. Databases must be intelligent enough to make the accessibility faster. However, not every user familiar with the Structured Query Language (SQL) queries as they may not aware of structure of the database and they thus require to learn SQL. So, non-expert users need a system to interact with relational databases in their natural language such as English. For this, Database Management System (DBMS) must have an ability to understand Natural Language (NL). In this research, an intelligent interface is developed using semantic matching technique which translates natural language query to SQL using set of production rules and data dictionary. The data dictionary consists of semantics sets for relations and attributes. A series of steps like lower case conversion, tokenization, speech tagging, database element and SQL element extraction is used to convert Natural ...

We present oPESA, an online examination management system for exam-hall seating arrangement. oPESA is a platform built on PHP, HTML, CSS, JavaScript, jQuery and SQL. It is an open platform which can be accessed only by registered users, i.e., professors and students. Professors provide as input course details like title, examination date and room; additional input, like predetermined lists of students or list of constraints regarding the seating plan, can also be given. Students simply register for course examinations by providing their student ID as input. Whenever a student registers for an examination, oPESA immediately responds by automatically assigning an exam-hall seat to the student. Seat assignment is performed by an unbiased algorithm ensuring randomness and fairness. Results appear online but can also be downloaded and saved locally in .xls format. oPESA follows a responsive web design which allows it to automatically resize, hide, shrink or enlarge in order to look good on all devices (desktop computers, tablets, smartphones). oPESA suggests a very useful management tool for modern-highly-populated-higher education institutes which significantly facilitates the examination management process offering clarity and efficiency. Furthermore, oPESA could be also used for more general seat allocation purposes in the context of scientific, social and cultural events.

Every technology has strengths and weaknesses, and relational database technology is no different. At this point in time, however, relational database management systems are the predominant storage technology; they are used to store information which is then available for easy retrieval based on various call procedures. While a database management system (DMS) controls storage, retrieval, security, and control of data within a database, a relational database management system (RDBMS) stores the data in a specific way. Other technologies are in the process of developing, but in their infancy not enough information is available for the average company to decide whether or not their use would be efficacious. RDBMS will be around for many years, but alternatives are gradually becoming available as technologies develop and improve. Companies need to remain on top of new developments, but remain very aware of limitations and how they can affect the company before jumping to new database systems. This paper critically evaluates the strengths and weaknesses of relational database technology.

Resumo. A Internet após a WEB 2.0 vem gerando uma quantidade expressiva de dados e com isso vem a necessidade de agilizar o gerenciamento desses da-dos, bem como seu armazenamento de forma que fiquem disponíveis sempre que precisar. NoSQL (Not Only SQL) vem com a missão de resolver esse gargalo de forma eficaz, com isso o presente artigo visa apresentar os conceitos que envol-vem o termo NoSQL, a sua necessidade de implementação para com a escalabi-lidade e performance na WEB 2.0, relata comparações com o modelo relacional nas propriedades ACID e BASE e a sua relação com o Teorema CAP. No estudo de casó e mostrada uma implementação utilizando o MongoDB, demonstrando a abordagem de desenvolvimento junto com o seu driver e a tecnologia Java. Por fim, ele explana os resultados do estudo de caso e sugere como trabalhos futuros a implementação do algoritmo MapReduce. Palavras-chave: nosql, sgbd, sql. Abstract. The Internet after Web 2.0 has generated a significant amount of data and with that comes the need to streamline the management of these data and their storage so they are available whenever you need. NoSQL (it Not Only SQL) comes with the mission to solve this bottleneck effectively, thus this article is to present the concepts involving the term NoSQL, their need for implementation towards the scalability and performance on the Web 2.0 reports comparisons with the relational model in ACID and BASE and its relationship with the CAP theorem. In the case study is shown an implementation using MongoDB, demonstrating the development approach together with its it driver and Java technology. Finally, he explains the results of the case study and suggests future work as the implementation of the MapReduce algorithm.

Intelligent interface, to enhance efficient interactions between user and databases, is the need of the database applications. Databases must be intelligent enough to make the accessibility faster. However, not every user familiar with the Structured Query Language (SQL) queries as they may not aware of structure of the database and they thus require to learn SQL. So, non-expert users need a system to interact with relational databases in their natural language such as English. For this, Database Management System (DBMS) must have an ability to understand Natural Language (NL). In this research, an intelligent interface is developed using semantic matching technique which translates natural language query to SQL using set of production rules and data dictionary. The data dictionary consists of semantics sets for relations and attributes. A series of steps like lower case conversion, tokenization, speech tagging, database element and SQL element extraction is used to convert Natural ...

The paper presents a mapping-based and metadata-driven modular data transformation framework designed to solve extract-transform-load (ETL) automation, impact analysis, data quality and integration problems in data warehouse environments. We introduce a declarative mapping formalization technique, an abstract expression pattern concept and a related template engine technology for flexible ETL code generation and execution. The feasibility and efficiency of the approach is tested on the pattern detection and data lineage analysis using large real life SQL corpuses.

Waroeng PC Games adalah sebuah perusahaan yang didirikan dengan tujuan untuk menjalankan usaha-usaha di bidang komputer. Perusahaan ini berlokasi di Jalan Raya Tegal Wangi II Sesetan No. 8. Salah satu informasi yang penting dari suatu perusahaan ataupun instansi pemerintah adalah tentang kepegawaiannya yang terkait dengan apa saja yang termasuk dalam melengkapi data karyawan tersebut, mulai dari pangkat dan jabatan, cuti, penggajian dan lain-lain.
Dalam pengolahan data penggajian karyawan, Waroeng PC Games masih menggunakan Microsoft Excel dimana hal ini memiliki kelemahan dari segi waktu yang diperlukan cukup lama dalam proses pelaporan gaji tiap bulannya kepada pimpinan yang cenderung tidak tepat waktu. Oleh karena itu guna memperbaiki sistem yang ada pada Waroeng PC Games khususnya untuk pelaporan gaji perlu adanya sebuah sistem yang mampu menyimpan dan menampilkan data dalam jumlah banyak dan akurat serta memberikan dukungan informasi baik untuk petugas pencatatan, manajemen atau karyawan lainnya.
Dalam perkembangan teknologi informasi sekarang ini, Sistem Dashboard merupakan suatu sistem berbasis komputer yang dapat membantu seseorang untuk meningkatkan kinerjanya dalam pengambilan keputusan [1,2]. Sistem Dashboard dapat membantu user dalam membuat keputusan dan memecahkan masalah yang umumnya berada di wilayah dengan dimensi yang beragam. Dalam pengembangan Sistem Dashboard, dasar yang digunakan adalah konsep data warehouse.
Dalam suatu sistem data warehouse, data yang berasal data operasional diproses dan di-upload ke dalam database data warehouse untuk selanjutnya digunakan untuk proses reporting dan analisis. Dari penjelasan tersebut diatas, penulis merancang sebuah sistem informasi berbasis web yang berjudul “Rancang Bangun Dashboard Penggajian Karyawan Waroeng PC Games Berbasis Web” yang bisa menjadi salah satu alternatif pemecah permasalahan yang ada sekaligus mampu memberikan informasi bagi stakehokler secara efektif dan efisien dari segi waktu.

Apache Flink is a hybrid system for distributed stream and batch data. So the need for an SQL-like query specially advanced query is increased that helps the user to make a deep analysis of the dataset. SQL query has limited scalability in Flink, so more efficient SQL-to-Flink translator is developed to optimize complex SQL queries called, " An optimized SQL Query Translator in Flink " built on top of Flink without effect on the structure of Flink. The main phase of this system called " Optimization Query " which can rewrite the SQL query into equivalent SQL query that enhances the performance of queries. The proposed system evaluate by TP-C Benchmark and result show that the proposed system enhance the performance by 20%.

El objetivo de este documento es plantear métodos eficientes de estimación y previsión de un gran número de series temporales interrelacionadas de forma que, en un tiempo relativamente corto, permita desarrollar modelos estadísticos aceptables que expliquen el comportamiento de dichas series, acelerando los procesos de análisis y explotación.
Se plantean dos posibles líneas de mejora que son independientes y compatibles entre sí:
  • Escribir algoritmos de estimación de modelos en SQL para evitar el coste de tiempo y de espacio de disco y memoria que supone el acceso masivo a grandes bases de datos, tanto para lectura como para escritura.
  •Aprovechar el hecho de que en los problemas de estimación masiva suele haber un gran número de variables en común a muchos grupos de series.

Dashboards have become a popular way to display key success indicators at a glance to multiple users across an organization. This paper presents two possible methods for storing, retrieving, displaying and sharing data in a dashboard: 1) a MySQL database hosted on Amazon Web Services (AWS) for storing the data and Amazon QuickSight for creating and sharing the dashboard, and 2) local MySQL for storing the data, Microsoft Excel for creating the dashboard, and a Dropbox for sharing it. The two approaches are compared for cost, ease of creation and use, update speed, and shareability

This paper presents a novel use of SQL language to solve a practical optimization problem to find the portfolio size and the quantity of money for securities. This problem is known as the Portfolio Selection Problem (PSP). The approach was tested on 9 random instances of PSP. Each instance has up to 12 securities and 50 different choices of money. Each security follows a non-linear profit model. The limitations of our approach are bounded by the computer resources, given that potentially SQL constructs the Cartesian product of the investment options, but it has the advantages of not requiring complex structures and it is easy to understand.