Query Syntax

51K

Solutions

20K

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

CA Premier Accounting Package
Copying and pasting of files will continue, at least for now. The front end MS Access 2016 and SQL Server 2016 back-end can be zipped together and emailed to clients like any other file without problems, I have thoroughly tested the detailed method below and can confirm that it works very well.
0
Cloud Class® Course: SQL Server Core 2016
LVL 12
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

CA Premier Accounting Package
Migrating data tables from MS Access to SQL Server is not for faint-hearted people, you have to draw all your programming knowledge to a higher assumed expertise level. Be prepared to sort out programming issues at a higher level.
0
The process of ML is comparable to data processing. Both systems search through information to appear for patterns. However, rather than extracting information for human comprehension as just in case of knowledge mining, ML uses that information to discover trends in data and alter program actions.
0
This tutorial is a great guide for newbies in SQL Server who never backup the database and for people with some experience, because you will learn some tips that you may not know.
1
SQL Backup Repair
Know how to repair corrupted backup files using SysTools SQL backup Recovery, Also understand the reasons behind .bak file corruption in SQL Server.
0
An introductory discussion about Oracle Analytic Functions which are used to calculate or compute Aggregate values, based on a group of rows.
0
In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and consists of using a SQL Recovery Software.
2
LVL 54

Expert Comment

by:Mark Wills
Comment Utility
Good Article,

Even as a SQL Server "expert", always good to know about tools - especially when it can make life much easier.

Stellar Phoenix SQL Database Repair looks the goods.

Thanks for sharing :)

Cheers,
Mark Wills
3
This article will show a step by step guide on how to mask column values in Oracle 12c using DBMS_REDACT full redaction option. This option is available on licensed Oracle Enterprise edition as part of Oracle's Advanced Security.
0
Attach SQL Database
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
1

Expert Comment

by:Rejin James
Comment Utility
Thanks for such an informative article! It was of great help to me. Had already tried the 1st two methods which you had mentioned so I directly skipped to trying the software you recommended. Initially the software took about 35 minutes to scan my file, but the best part is that it showed all the records of my database. That moment was a huge sigh of relief to me since I was able to see the crucial data after spending almost 2 days repairing the .mdf file. Now that I have recovered my data, I can finally sleep in peace. Stellar SQL Database Repair is definitely a brilliant software!
0
MT DV MySQL
MySQLTuner is a script written in Perl that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability. The current configuration variables and status data is retrieved and presented in a brief format along with some basic performance suggestions.
0
Cloud Class® Course: C++ 11 Fundamentals
LVL 12
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, this article addresses the 'improvement'...
0
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
0

Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began appearing in 2006-7 and as of MySQL version 8.0 and MariaDB version 10.2.2. 


Common Table Expression ( the WITH RECURSIVE … clause )

Recursive SQL is achieved by using Common Table Expression or CTE which is a temporary named resultset, derived from a simple query and defined within the execution scope of SELECT, INSERT, UPDATE or DELETE statements. CTE is often used to improve readability of a SQL code by simplifying and/or prise the code into more readable sections. One “feature” of CTE is the ability to reference recursively the named resultset, hence recursive SQL. 


With recursive SQL queries you can achieve things you would not have imagined being possible with this type of SQL and at the speed it executes. You can solve many types of business problems and even rewrite some complex SQL/application logic down to a simple recursiveSQL-call to the database. 

Some example of useful usage of recursive CTE is that you can use it to find gaps in data, create organization charts and create test data.

What I like most of using recursive SQL query is the ability to produce large amount of test-data in amount of seconds or few minutes, depending on complexity of the data and the available database/systems resources. Using recursive CTE we can produce many hundreds, thousands or millions of records in very short period of time which is then only limited to available database memory and other database/system resources. Recursive query produces test data way faster compared to other test-data procedures that I have seen and experienced.
Also, it has been proven that recursive queries outperforms other queries that take days to execute on huge amount of data by running in several minutes.


The word recursive says it all. You have a query that repeatedly calls itself with some starting point and that which is EXTREMELY IMPORTANT an ending point (a fail-safe exit as I call it). If you don't have a fail-safe exit or your recursive formula goes beyond it you are in deep trouble. Your query will go into aninfinite loop resulting in very high CPU and very high LOG utilization which will lead to memory and/or storage exhaustion. If your query goes haywire you must think very fast and stop it. If you are unable to do so then alert your DBA immediately so he/she can prevent the database system of choking by killing the runnaway thread. 


Examples

Let's look at some simple examples that shows how easy and effective it can be. 


with RECURSIVE mydates (level,nextdate) as (
                                        select 1 level, FROM_UNIXTIME(RAND()*2147483647) nextdate from DUAL
                                        union all 
                                        select level+1, FROM_UNIXTIME(RAND()*2147483647) nextdate
                                        from mydates
                                        where level < 100000
                                        )
                                        SELECT nextdate from mydates
                                        );


Here we produce 100k of  random dates. The level is our counter and fail-safe exit to safely exit the recursive query. The  line ( no 2 ) is our starting point and the line (no 4-5 ) is the recursive call with the ending point in the where clause ( no 6) . Then the last lines ( no 8 - 9 ) is the call to execute the recursive query and retrieve the data. 

The above query executed in 0.84 sec and outputted 100k of rows. Note that even though you put a LIMIT 10   on the query  as shown below it will first produce the 100k rows and then limit the result to the first 10 rows in the same amount of time or 0.84 sec


with RECURSIVE mydates (level,nextdate) as (
                                        select 1 level, FROM_UNIXTIME(RAND()*2147483647) nextdate from DUAL
                                        union all 
                                        select level+1, FROM_UNIXTIME(RAND()*2147483647) nextdate
                                        from mydates
                                        where level < 100000
                                        )
                                        SELECT nextdate from mydates
                                        ) LIMIT 10;


You can also create a table and by that physically store the data by surrounding the CTE query like this. 


create table somedates as (
                                        with RECURSIVE mynumbers (level,nextdate) as (
                                        select 1 level, FROM_UNIXTIME(RAND()*2147483647) nextdate from DUAL
                                        union all 
                                        select level+1, FROM_UNIXTIME(RAND()*2147483647) nextdate
                                        from mynumbers
                                        where level < 100000
                                        )
                                        SELECT nextdate from mynumbers
                                        );

This query ran on my laptop in 2.93 seconds storing 100k rows of dates in a table called somedates.


Another simple example, a “gaps in data” solution,  which produces last 1000 dates to current date and compares it to the order table to find all dates that don't have any orders showing the newest date first.

For this example I created 1 million rows with random orderdates using recursive SQL in 32.95 seconds. Created index on (orderdate,orderid) on the the table in 9.12 seconds, deleted thousands of random rows to create a gap and ran the below query


with RECURSIVE mydates (level,nextdate) as (
                                        select 1 level, ADDDATE(CURRENT_DATE,INTERVAL -1000 DAY) nextdate from DUAL
                                        union all 
                                        select level+1, ADDDATE(nextdate,INTERVAL 1 DAY) nextdate
                                        from mydates
                                        where level < 1000
                                        )
                                        SELECT md.nextdate from mydates md left join myorders od on md.nextdate = od.order_date
                                        where od.order_date is null order by md.nextdate desc;


This query ran on my test data and looked for gaps in the last 1000 days from current date and returned 68 dates in 0.05 seconds


Conclusion

Here I have talked briefly about recursive queries and showed how useful and powerful it can be to solve problems in very short and effective way. However, when writing the recursive query be VERY careful NOT to forget the fail-safe exit and make sure you double check twice that the calculations used in the fail-safe exit produces a stop signal. 

0
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
7
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
3

Expert Comment

by:Hari Shankar
Comment Utility
Great Article. Thank u Vitor
1
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this solution, while refreshing my recursive CTE skills.
0
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

Expert Comment

by:Gregorio Méndez
Comment Utility
Mr. Strauss, what a good tip, thanks a lot for your contribution
1
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
Keep up with what's happening at Experts Exchange!
LVL 12
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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
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 67

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
By Mark Wills

Formatting dates and times has aways presented a few challenges in T-SQL. Seems most other languages support a FORMAT command.

Well, time to rejoice because (finally) MS SQL 2012 (and more recent) now supports the FORMAT function. One little "gotcha" is you need .Net Framework because it leverages CLR. Which also means that it cannot be remoted (which is nothing new for CLR runtime).

So, with that out of the way, and for a large majority of T-SQL requirements, it is like any other system function. Probably easiest to show you :)

First, some dummy data. Create an order table with date and price columns, then populate :-
CREATE TABLE EE_tbl_Orders (CUSTOMER varchar(20),ORDER_DATE datetime, ORDER_PRICE decimal(10,4));


INSERT INTO EE_tbl_Orders (CUSTOMER, ORDER_DATE, ORDER_PRICE) VALUES ('Fred','2015-08-20 20:30:40',1234.5678);

Open in new window


The structre of the command is FORMAT ( value, format_str [], culture ] ) and can be found at MSDN Now, [culture] is optional and I tend to leave it out so it can default to the way my server / systems are set up.

So, with our Order Table, let us experiment with our new command...

SELECT Customer, FORMAT(Order_Date,'yyyy MMM dd'), Order_Price
FROM EE_tbl_Orders


-- Results

-- Fred      2015 Aug 20      1234.5678

Open in new window


Now, we can also do something with that price as well (and note the rounding)...

SELECT Customer, FORMAT(Order_Date,'yyyy MMM dd'), FORMAT(Order_Price,'C')
FROM EE_tbl_Orders
 
-- Results
-- Fred      2015 Aug 20      $1,234.57

Open in new window

8
LVL 67

Expert Comment

by:Jim Horn
Comment Utility
Darn handy, especially for those that are stuck supporting 2008R2 databases.  Voted Yes.
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 67

Expert Comment

by:Jim Horn
Comment Utility
I ran the code blocks and the result was excellent.  Nice job.
0

Query Syntax

51K

Solutions

20K

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.