Query Syntax

48K

Solutions

19K

Contributors

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.

Share tech news, updates, or what's on your mind.

Sign up to Post

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
0
Independent Software Vendors: We Want Your Opinion
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
2
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
0
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return different results based on different supplied parameters.
0
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on.

Put bluntly: Do NOT use "between ... and" (see "Beware of Between" too)

Instead: prescribe what you what using combinations of Greater Than, Less Than and Equals

ABSOLUTELY do NOT: try to fiddle with any upper boundaries, just use Less Than - it's that simple. Do NOT subtract a minute, or a second, or a millisecond. Do NOT subtract anything from the boundary, it isn't needed. Use " less than the next time unit" (next day, next month, next year).

Right, now I've got into all sorts of twists here, especially for sql server related folks, because they have a such myriad of favourite convert, datediff, dateadd combinations and approaches to this.

Stop. Why the complexity? First principle, don't apply functions to data unless it's absolutely needed. Well, for the purposes of evaluating a date(time) field: convert isn't needed, dateadd isn't needed, datediff isn't needed. DON'T do this ON THE DATA just to decide if the data is less than some other date(time) measure.

OK, some of this off my chest.

For the curious, here are some variants of deciding if some datetime values is in June or July. Not rocket science, but…
1
 
LVL 49

Author Comment

by:PortletPaul
Comment Utility
this was a blog, when we had those things, I made it into a draft article so I didn't lose it completely. I like the blog capability btw, still think it was a silly idea to remove it. It was drafted then and I really have no time for articles at the moment.

I'm comfortable the way it is. If others aren't I'm also OK with just making into a draft I can refer to at some waayyyyy distant time when I feel EE articles are something I will revisit.

Congo reference is due to some unwelcome changes in the profile, I may change it from time to time. Changed.
0
Tableau
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
1
 
LVL 16

Author Comment

by:Brian Pringle
Comment Utility
Tableau does not have a good set of business days.  It will calculate today and yesterday or a range of dates relative to today, but calculating based on another anchor date is not intuitive.  There are numerous questions on the Tableau Web site asking for this.  The reason that we are not using an actual table to store the dates is that we may not always have business on the weekend and other times might.  The intention of this procedure is to let the system figure out when the last business date was from the data rather than a set calendar.

The data set has a large amount of data that gets imported during each business day.  I can query the database to get the distinct list of dates and then calculate from there.  The LOD functions in Tableau are the closest thing to getting that information.
0
 
LVL 66

Expert Comment

by:Jim Horn
Comment Utility
Very well written.  Voting Yes.
0
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
1
 

Expert Comment

by:alsatham hussain
Comment Utility
i can easily understand.

Thanks
0
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within REPLACE functions). Over time this can be quite unreadable. It can also become a maintenance chore where this is not a single one-off effort.

This happened in a recent question here on EE. I'd like to share the solution that came out of that thread. I have written a function called udf_CleanDataFn. It can be invoke within any query as follows:
 
SELECT dbo.udf_CleanDataFn(YourColumn, @Options)

@Options is a varchar(255) field, which can accept any of the following "options":
 
  • ltrim - This will left-trim the value in YourColumn.
  • rtrim - This will right-trim the value in YourColumn.
  • trim - This will both left- and right-trim the value.
  • nocomma - This will remove any and all comma's.
  • nospace - This will remove any and all spaces.
  • nopunc - This will remove all standard punctuations (!@#$% etc)
  • nonum - This will remove all numbers (0-9).
  • noalpha - This will remove all alpha characters (A-Z and a-z).
  • alphaonly - This will remove everything except alpha characters.
  • numonly - This will remove everything except numbers.

Following are a few working examples that will demonstrate the different results . This first example takes out only the commas:
 
SELECT dbo.udf_CleanDataFn('This !  is *  a  13,21  @  test', 'nocomma')
Result:
1
 

Expert Comment

by:Nick Sawe
Comment Utility
ok thanks, i will give it a go. thanks for sharing this by the way !
0
 
LVL 20

Author Comment

by:dsacker
Comment Utility
You're welcome. If it proves helpful, please click the "Helpful" button. :)
0
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:
 

The problem is that RFC822 date formats do not always fit within SQL Server's valid date formats. The CONVERT/CAST functions within SQL Server will only convert a limited variety of date formats. When trying to convert RFC822 date formats, some of the RFC822 accepted formats will result in the well-known T-SQL error:
 
Msg 241, Level 16, State 1, Line 1 
Conversion failed when converting date and/or time from character string.

According to the RFC822: Standard for ARPA Internet Text Messages, as long as a date adheres to the constraints in section 5 of the RFC822 protocols, it is a valid RFC822 date. Many RFC822 Dates fail to convert into a T-SQL datetime format.

For instance, the following is a valid RFC822 date:
 
Wed, 27 Sep 2006 21:49:19 GMT

This format (and a number of other RFC822 formats) will not convert to a datetime type. In addition SQL Server does not inherently recognize nor convert time zone codes. (See footnote 1 at the end of this article for a list of valid RFC822 date formats.)

I have written a function called …
1
 
LVL 20

Author Comment

by:dsacker
Comment Utility
Appreciate that. And thanks to Eric. He gave me some good pointers along the way.
0
 
LVL 66

Expert Comment

by:Jim Horn
Comment Utility
I ran the code blocks and the result was excellent.  Nice job.
0
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops along with their Java loop equivalents to make creating useful PL/SQL loops much easier.
 

While

Under many circumstances a while(true) loop isn't the most useful tool but a basic while loop in PL/SQL can be very useful for performing repeating actions such as multiple simple insert statements.

Java


while (true)
{
   // do something
   if (exit_condition)
      break;
}

Open in new window


PL/SQL


DECLARE
BEGIN
   LOOP
      // do something
      IF exit_condition THEN
         EXIT;
      END IF;
   END LOOP;
END;
/

Open in new window



A more common while loop with a given breaking condition can be a cleaner way to perform similar tasks.

Java


while (condition)
{
   // do something
}

Open in new window


PL/SQL


DECLARE
BEGIN
   WHILE condition LOOP
      // do something
   END LOOP;
END;
/

Open in new window



For

A basic for loop can more clearly perform a distinct number of iterations of your statements.  I've often used this to test code-enforced thresholds by inserting the maximum number of rows into a table to ensure my code reacts correctly.

Java


for (int i=0; i>length; i++)
{
   // do something
}

Open in new window


PL/SQL


DECLARE
BEGIN
   FOR i IN 1..length LOOP
      // do something
   END LOOP;
END;
/

Open in new window


Java


for (int i=length; i<0; i--)
{
   // do something
}

Open in new window


PL/SQL


DECLARE
BEGIN
   FOR i IN REVERSE 1..length LOOP
      // do something
   END LOOP;
END;
/

Open in new window



Possibly the most useful loop is a for loop performed on a list of objects. This will allow you to do things like performing similar insert statements using different source data, allowing you to have more dynamic statements.

Java


for (object o : objects)
{
   // do something
}

Open in new window


PL/SQL

int objects:


Open in new window

2
What does it mean to be "Always On"?
LVL 4
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Confronted with some SQL you don't know can be a daunting task.
It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as:
(+)     as used in Oracle;     *=     =*    as used in Sybase & SQL Server.

This bright new age uses ANSI standard syntax for joins, such as INNER JOIN or LEFT OUTER JOIN.
So how does one get rid of those awful old secret codes and use the far better, more standard, ANSI join syntax?

The following is my attempt to explain step by step how I do it. Please note that while this article uses an Oracle example, the process applies to Ye Olde SQL regardless of vendor.

We will start with the following SQL. This will be new and unfamiliar to you; Well it was new and unfamiliar to me also (as I didn't invent it) but for this article that is perfect. You do not need to understand the tables/fields for this exercise, Also, we care only about the FROM and WHERE clauses here.
select *
from proj.busrepts busr, proj.pbase pb, proj.WQuota wq, finance.pedproj pedp, finance.qiiview qii 
where busr.projectid = pedp.projectid(+) and 
busr.program = pedp.pedprogram  and 
busr.patnum   =  pb.fileno(+) and 
busr.curtype like '%Self%' and 
busr.wq_statusid = wq.statusid(+) and 
busr.DOS >= TO_DATE('01-JUL-2010','DD-MON-YYYY') and 
busr.balance >5  and 
(qii.address not in ('1 Pudding Lane', 'Diagon Alley') and qii.address not like '%Home%' 
and qii.address not like '%HOME%' and qii.address not like 

Open in new window

2
 
LVL 34

Expert Comment

by:Mike Eghtebas
Comment Utility
As time goes on, the information discussed here will be used more often because the SQL syntaxes has to be updated. Thank you for this article. I will come back to this article over and over again because this is not for one sitting.

Mike
0
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you are getting still have “duplicates” then you are expecting something else that SELECT DISTINCT does not do. In truth, “select distinct” is a rather dull animal with minimal decision making powers.
 
The most common solution to this “I still got duplicates” is to use GROUP BY instead, but please read on. (or, cheat and click this.)
The first thing to recognize about SELECT DISTINCT is that overall it’s a bit boring. It’s isn’t a function so if you are using a query like this:
 
SELECT DISTINCT (column1), column2, ... column40
You will be disappointed to learn that those parentheses around column1 do NOT work to “make column1 unique, then by magic decide how to simplify the other columns”.  

And: With or without the parentheses, SELECT DISTINCT does not know that you really want it to concentrate on column1 or (column1 and column2) ignoring the other columns.

SELECT DISTINCT is a “row operator”, not a function, and not magic. It will evaluate ALL of the columns that you have listed and remove rows that have exactly the same values in all …
4
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
thanks for the link/reference to my article :)
0
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common with most definitions is that BETWEEN equates to:

[expression] >= [low boundary] and [expression] <= [high boundary]

Note there are 2 equal signs! This means the [low boundary] and [high boundary] are BOTH included. Also note there is a definite order, [low boundary] must be given first or there will not be any matches at all.

This 'inclusive' definition may not agree with your generally held interpretation of "between". For example if I drive my car between 2 trees I don't want to include the trees (else I have tree damage and/or car damage). But if I catch a jet between New York and London, both cities are included (to catch the jet and to land the jet). So a "car between trees" is different to a "jet between cities"; in SQL 'between' is used in the 'inclusive' sense of "jet between cities".

Below we examine BETWEEN in more depth using a set of queries, and of course we need some data for this:sample data, 10 rows, "raw_data" = 10.1, Category  is 1 char (A to Z) and some datesThis first query is deliberately 'odd', it asks for category between ‘A’ and ‘A’ and this may not seem logical (if you hang on to a "car between trees" viewpoint). Let’s test it:
select
id, category, raw_data
from sample
where 
   category BETWEEN 'A' AND 

Open in new window

22
 
LVL 51

Expert Comment

by:Vitor Montalvão
Comment Utility
Excellent article. Gave my vote of course.
Cheers
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
There's also the fun and hellacious problem of how dates are displayed in Access.
You can make a field up as DateTime and format it as "dd-mmm-yyyy"
It all looks good.  But it is stored as a Double with a Long integer value based on the number of days elapsed since 30-Dec-1899 and the decimal as a fraction of the day as the time.
Now, add some values to it using Date() and others using Now() and watch the fun ensue as you use BETWEEN...AND and get some records in at the  'boundary' and others out.
Those entered via Date() will be dd-mmm-yyyy 12:00:00 AM and will be in.
Those enter via Now() will be dd-mmm-yyyy and some time component and be out.

That trips up a lot of folks!
0

Introduction

Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations being Parenthesis (or brackets), Exponents (or roots which can be expressed as a fractional exponent 1/n), Multiplication/Division and Addition/Subtraction.

If you have ever doubted how Math can be used in everyday life, then hopefully this is a good example. I think most forget when dealing with technology or challenges in general, it is all about approaching the situation with a definitive list of known truths and methodologies how to solve issues. You don't have to have the answers going in, just keep it simple and apply what you know like Math rules. SQL syntax is no different and so we will explore in this article how PEMDAS can aid programmers in avoiding some pitfalls or ensuring proper results through being very explicit about the order in which code is evaluated.

If you do this successfully, it should make even complex formulas in your SQL syntax easy to produce.

So let's get started...

During the editing of an article some months back, the topic of order of operations came up for discussion and there the data looked as similar to the below:

Open in new window

6
 
LVL 60

Author Comment

by:Kevin Cross
Comment Utility
Yes, true.  I guess I am trained to think Exponents instead of Orders | Indices as well as my use of parenthesis, but BODMAS or BIDMAS works just fine! *smile*

Thanks for reading!
0
 
LVL 18

Expert Comment

by:Simon
Comment Utility
Thanks Kevin. Good article. I think that when I went to school they weren't much into mnemonics. I like both PEMDAS and BODMAS, but the precedence is just second nature. I very rarely get it wrong, but I don't consciously think about it.
0
As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time!

1. Introduction

By the end of this Article it is my intention to bring the meaning and value of the above quote to those who chose to read this whether as a beginning SQL programmer or an intermediate one inexperienced with this little tidbit of SQL syntax: conditional aggregation.

Conditional aggregation is simply the use of aggregates under one or more conditions, thus, potentially altering your results of functions like SUM(), COUNT(), etcetera in a particular column while allowing you to analyze your record set as a whole.

In other words, consider the following data:
----------------+-------+------------
expert          |isMale |primaryZone
----------------+-------+------------
acperkins       |   1   | MSSQL2K5
aneeshattingal  |   1   | MSSQL2K5
angeliii        |   1   | MSSQLSVR
Helen_Feddema   |   0   | MSACCESS
matthewspatrick |   1   | SQLSYNTX
mbizup          |   0   | MSACCESS
mwvisa1         |   1   | SQLSYNTX
ScottPletcher   |   1   | MSSQLSVR
sdstuber        |   1   | ORACLEDB
slightwv        |   1   | ORACLEDB
----------------+-------+------------

Open in new window


««setup»»
To reproduce the above data, you can execute SQL similar to the below T-SQL example created for MS SQL Server.
(table structure -- create statement)
create table SQLExperts(
   expert varchar(50) primary key, 
   isMale bit, 
   primaryZone char(8)
);

Open in new window


(sample data -- insert statement)
insert into SQLExperts(expert, isMale, primaryZone)
select 'mbizup', 0, 'MSACCESS' union 
select 'Helen_Feddema', 0, 'MSACCESS' union
select 'matthewspatrick', 1, 'SQLSYNTX' union
select 'mwvisa1', 1, 'SQLSYNTX' union
select 'angeliii', 1, 'MSSQLSVR' union
select 'ScottPletcher', 1, 'MSSQLSVR' union
select 'acperkins', 1, 'MSSQL2K5' union
select 'aneeshattingal', 1, 'MSSQL2K5' union
select 'sdstuber', 1, 'ORACLEDB' union
select 'slightwv', 1, 'ORACLEDB'
;

Open in new window


To start, if we wanted to know how many Experts are on the list we would simply use:
select count(*) as cnt from SQLExperts;

Open in new window


Now, what if we wanted to know how many female Experts are on the list?
Then we would add a WHERE conditional clause to the query:
select count(*) as cnt 
from SQLExperts
where isMale = 0;

Open in new window

This works, but what is the percent of female Experts to the total?

2. Conditional Aggregates: Basics

Without conditional aggregates, to answer the previous question you would need a secondary query to get the total to do the percentage calculation.

This code look familiar?

Open in new window

20
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
Kevin,

Great article!  

A few suggestions:

1. In general, it is a good practice to avoid embedding VBA functions in your Access queries, and it usually can be done.  They slow things down (Domain Aggregate functions are the most notorious for this).

I haven't actually benchmarked  this query using iif (which I don't think would have nearly the same performance hit as a domain aggregate function):

select sum(iif(isMale=0, 1, 0)) * 100.0 / count(*)
from SQLExperts;

Open in new window



But it can be written equivalently without embedded VBA as:

SELECT Sum(-1 * Not IsMale)*100/Count(*) AS [Female Experts]
FROM SQLExperts;

Open in new window



2. "NULLIF function if available on your SQL platform to generate nulls ..."
Do you want to include an alternative for other platforms as a sidenote?

select sum(-1 * (primaryZone <> 'MSACCESS')) as cnt
from SQLExperts;

Open in new window



3.  Under the Pivot section, this is an option for systems (such as Access) that don't support CASE:
select PrimaryZone
     , Sum(-1 * Not [IsMale]) as Female
     , Sum(-1 * [IsMale]) as Male
from SQLExperts
group by primaryZone;
 

Open in new window

0
 
LVL 60

Author Comment

by:Kevin Cross
Comment Utility
Thanks, Miriam, that is great feedback!
0
In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE.

Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as numbers instead of character string which causes '9' to sort after '10').  I won't get into the debate on whether this is an appropriate way to store data in this article, but will presume we can't change it and so must deal with how to sort correctly.  

With that in mind, the following article was written to show how important an IsNumeric() function can be in solving this problem and further how to specifically to do this in MySQL server.

Since this can be useful to MySQL users of all experience levels, it goes through details for beginners; therefore, for intermediate/advanced users who simply want to cut to the solution, I have organized the article into the following sections to make it easy to jump around:

The Data.
What's wrong with the natural sort order?
Custom Sorting in MS SQL Server: IsNumeric() Primer.
MySQL IsNumeric() Workaround.
MySQL IsNumeric() User Defined Function (UDF).
Custom Sorting in MySQL: Using IsNumeric() UDF.

Before we get started.
Please note that, although experience can be varied, all will need to have MySQL Server, a tool such as MySQL Query Browser, and a basic knowledge of how to execute SQL statements as pre-requisites.

Download MySQL Community Server and GUI Tools
6
 
LVL 15

Expert Comment

by:dbbishop
Comment Utility
Something you need to be aware of, in SQL Server, the value '1234E32' will pass the ISNUMERIC() check (returns 1), but will fail the CAST() if casting to any data type other than FLOAT. Any string of numbers with either a single 'E' or 'D' in the string will be interpreted as scientific notation.
0
 
LVL 27

Expert Comment

by:skullnobrains
Comment Utility
likewise in mysql

select 3e2 REGEXP '^300$';
+--------------------+
| 3e2 REGEXP '^300$' |
+--------------------+
|                  1 |
+--------------------+
1 row in set (0.00 sec)

... but if you quote the '3e2', it won't happen.
0

Query Syntax

48K

Solutions

19K

Contributors

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.