Queries

A Look at SQL Queries {Comments Off on A Look at SQL Queries}

by Andrew H
For this weeks blog post I read an academic journal by Mohammad Dadashzadeh called, “A Simpler Approach to Set Comparison Queries in SQL.” The article talks about set comparison queries which are queries that have results that can only be determined by comparing two sets of data. An example of a comparison query would be Which suppliers will be able to supply all the parts that they are currently shipping or which suppliers are shipping exactly the same parts as supplier SI? In contrast to these two queries a query such as “which suppliers are shipping at least one red part” can be obtained by matching or joining data from one table. The article explains how queries involving set comparison are very difficult to formulate in relational query languages because they must use the error prone EXISTS function. Their solution is to avoid the EXIST function and use the built in SET function as well as the COUNT function. read more...

Friendly SQL Queries {7}

by Claudia J
The peer review article that I read was called “Interactive SQL query suggestion: Making databases user-friendly” by Ju Fan talks about how SQL is a good tool for querying relational databases. The author mentions that it is hard for beginners with a lack of programming skills to make efficient SQL queries as they require being proficient in SQL syntax and having a good understanding of the underlying schema. The author proposes a SQLSUGG which is a user-friendly keyword-based method to help people create SQL queries. This method suggests SQL queries as users type the keyboard and also helps to save what the users type to avoid them having to type everything multiple times which avoids SQL debugging in a way. read more...

SQL Performance Tips You Can Use Now {1}

by Hieu H
We have all learned throughout the course that the database can affect our applications’ overall performances. As we progress in our knowledge of databases and SQL, we will undoubtedly be exposed to more advanced queries and techniques. However, there are some “basic” techniques that we can use to improve relational database performance now. Cullins points out that “up to 75% of poor relational performance” is caused by inefficient SQL queries and application code. By following some simple rules, we can eliminate some of these issues. The first step is to use JOIN queries instead of cursors for every table. JOINs will return only what we need from the database. The next step is to stop using SELECT * in our queries.  Instead of selecting every column in a table, we should select only the required columns. Another trick to improve performance is to use the WHERE clause. This helps to filter out and return only the desired information. read more...

Making SQL Queries Better {1}

by Andrew M
The article I read this week was entitled “Generating Test Data for Killing SQL Mutants: A Constraint-based Approach” by Shetal Shah, S. Sudarshan, Suhas Kajbaje, Sandeep Patidar, Bhanu Pratap Gupta and Devang Vira. This article talks about how extremely advanced and complicated SQL queries are being created all the time and how difficult it is to test these queries. While SQL queries are always tested before being used sometimes it is very hard to test these queries enough to make sure they will always give the desired results. The authors propose that a method called Mutant Testing be used. In effect, by applying Mutant Testing to test SQL queries testers will be able to show if the query will give the desired results. In Mutant Testing the algorithm of a specific query is changed slightly and the query is ran to test if the results come back the same. If the results are different, as they should be, then this in affect proves that the query is written correctly. While testing every possible mutation of a single query would be extremely time consuming. The authors propose making mutations that would test common programming errors. This would test more efficiently and would not just be testing every possible circumstance. read more...

7 performance tips for faster SQL queries {4}

by Toan T
Writing SQL queries is a simple task that everyone can do but how to write efficient query that does not affect performance is another issue. As a matter of fact, many developers from every platforms are struggling and seemingly stuck in using simple DO WHILE loops and many of them often have their own methods for writing codes that they are too reluctant to think outside the box. For effective query writing, there are seven tips that the article recommended using. read more...

SQL Intro {Comments Off on SQL Intro}

by Peter C
Summary:

SQL is responsible for querying and editing information that is stored on a database. It is a database management system that people use for programming. It was used by IBM in the 1970s and now it has become a big part of our database system. Oracle is the one that came up with a new way on using this system which is now call Oracle V2. SQL offers great flexibility to users by being able to run on several computer networks. The queries that produce by SQL will let the user search the database for any information it needed. read more...