Login or Sign Up to become a member!
LessThanDot Site Logo

LessThanDot

A Technical Community for IT Professionals

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Highly Rated Users

Forum
No Posts Rated

Top 50
Given
Received

Links

Wiki
Blog

Forum Statistics

Users
Members:
1878
Members Online:
2
Guests Online:
20

Total Post History
Posts:
81448
Topics:
18714

7-Day Post History
New Posts:
1
New Topics:
0
Active Topics:
1

Our newest member
kowilmar1990z

Other

FAQ
All times are UTC [ DST ]

Google Ads

Interesting SQL is Hard queries

Microsoft SQL Server
Please wait...

Interesting SQL is Hard queries

Postby tarwn on Wed Jun 19, 2013 5:57 pm

So since I have a log of every query that gets run against SQLisHard.com, I thought I might share some of the interesting ones I've collected thus far.

Note: These are cases where people are either actively trying to break the system or exploring well outside the bounds of the exercises. I don't plan on publishing queries where people were trying the exercises, that's part of learning.

  1. SELECT * FROM information_schema.COLUMNS
  2. SELECT * FROM information_schema.procedures
  3. SELECT * FROM information_schema.routines
  4. SELECT * FROM information_schema.views
  5. INSERT INTO #tmp(1); insert into #tmp(2); Select * from #tmp
  6. INSERT INTO #tmp(id) values(1); insert into #tmp(id) values(2); Select * from #tmp
  7. CREATE TABLE ##tmp(id int); insert into ##tmp(id) values(1); insert into ##tmp(id) values(2); Select * from ##tmp
  8. TRUNCATE TABLE customers
  9. \d Customers
  10. CREATE DATABASE ab
  11. CREATE TABLE T1 ( id integer )
  12. exec sp_help customers
  13. DELETE ALL FROM table_name
  14. DROP DATABASE sqlisHard_Exercises_0
  15. DROP SCHEMA ADO
  16. DROP *
  17. ALTER TABLE orders ADD penis number(4, 0)
  18. ALTER TABLE orders ADD penis varchar(4, 0)
  19. ALTER TABLE orders ADD penis varchar2(4, 0)
  20. checkpoint 1
  21. CREATE login [aa] WITH password 'aa'
  22. Exec ('Select Id, FirstName From Customers Order By Id Desc')
  23. EXPLAIN plan SELECT * FROM orders
  24. SELECT * FROM sys.logins
  25. SELECT @@version
  26. whoami
  27. UPDATE CUSTOMERS SET Id = 99
  28.  


With the original line breaks, thanks bobby tables:
  1. 'DROP TABLE Customers;--SELECT * --FirstName, Id
  2. --FROM customers
  3. --WHERE Id = 5
  4.  
  5.  
  6.  
  7.  


Another one with original line breaks:
  1. DROP TABLE Customers;--SELECT * --FirstName, Id
  2. --FROM customers
  3. --WHERE Id = 5
  4.  

Um, yeah...I'm going to need you to come in on Saturday -- Bill Lumbergh, Office Space
User avatar
tarwn
LTD Admin
LTD Admin
LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897
LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897
LTD Gold - Rating: 897
 
Posts: 3838
Joined: Fri Oct 12, 2007 11:10 am
Location: Raleigh, NC, USA
Unrated

Re: Interesting SQL is Hard queries

Postby SQLDenis on Wed Jun 19, 2013 5:58 pm

User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
 
Posts: 21784
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond
Unrated

Re: Interesting SQL is Hard queries

Postby tarwn on Wed Jun 19, 2013 5:59 pm

Also, there were several cases of "show tables", so I added an easter egg for that one :)

I also added filtering logic for empty queries and 'help' and it loks liek I may need to filter for things that look like temp tables or multiple statements.

Um, yeah...I'm going to need you to come in on Saturday -- Bill Lumbergh, Office Space
User avatar
tarwn
LTD Admin
LTD Admin
LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897
LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897
LTD Gold - Rating: 897
 
Posts: 3838
Joined: Fri Oct 12, 2007 11:10 am
Location: Raleigh, NC, USA
Unrated

Re: Interesting SQL is Hard queries

Postby tarwn on Wed Jun 19, 2013 6:05 pm

Whoops, looks like there are some sys views I should filter out somehow too...though it looks like the worst thing they expose is some usernames

Um, yeah...I'm going to need you to come in on Saturday -- Bill Lumbergh, Office Space
User avatar
tarwn
LTD Admin
LTD Admin
LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897
LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897
LTD Gold - Rating: 897
 
Posts: 3838
Joined: Fri Oct 12, 2007 11:10 am
Location: Raleigh, NC, USA
Unrated

Re: Interesting SQL is Hard queries

Postby SQLDenis on Wed Jun 19, 2013 6:21 pm

Can you give us some stats. How many people tried the exercises and how many unique queries?
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
 
Posts: 21784
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond
Unrated

Re: Interesting SQL is Hard queries

Postby tarwn on Wed Jun 19, 2013 6:42 pm

2197 people have tried the exercises (I'm sure some of those are me).
7115 distint SQL queries since last Thursday morning (17304 total + some errors)

Graphs!

Last 7 days (3.x added Sunday or Monday)
Last7Days.PNG


Last 24 hours
Last24Hours.PNG


You can tell some of the buzz died down, the numbers for past 24 hours are smaller than the 7 day one. The failure rate percentage on 1.2 has really improved too, it was at nearly 1:1 on Thursday and Friday, still need to do some work on 4.0.

I also need to see if I can do some queries to figure out the farthest point individual users made it so I can see if there are any obvious points that they give up on.
You do not have the required permissions to view the files attached to this post.

Um, yeah...I'm going to need you to come in on Saturday -- Bill Lumbergh, Office Space
User avatar
tarwn
LTD Admin
LTD Admin
LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897
LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897
LTD Gold - Rating: 897
 
Posts: 3838
Joined: Fri Oct 12, 2007 11:10 am
Location: Raleigh, NC, USA
Unrated

Re: Interesting SQL is Hard queries

Postby SQLDenis on Wed Jun 19, 2013 6:56 pm

Those stats would be a nice follow up blog as well
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
 
Posts: 21784
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond
Unrated

Re: Interesting SQL is Hard queries

Postby chrissie1 on Wed Jun 19, 2013 6:57 pm

It also needs a, I give up give me the solution.
pink fuzzy slippers
User avatar
chrissie1
Senior Guru
Senior Guru
LTD Gold - Rating: 2135LTD Gold - Rating: 2135LTD Gold - Rating: 2135LTD Gold - Rating: 2135LTD Gold - Rating: 2135
LTD Gold - Rating: 2135LTD Gold - Rating: 2135LTD Gold - Rating: 2135LTD Gold - Rating: 2135LTD Gold - Rating: 2135
LTD Gold - Rating: 2135LTD Gold - Rating: 2135
 
Posts: 9475
Joined: Wed Oct 10, 2007 7:18 pm
Location: Belgium
Unrated

Re: Interesting SQL is Hard queries

Postby tarwn on Wed Jun 19, 2013 10:29 pm

Chris: There's been a couple suggestions around offering additional tips, I'm trying to figure out how to do that. That's the other reason I'd like to see where people give up, so I can correlate with error rate and work on making the descriptions of those exercises better.

I had planned on doing a follow-up blog on the tech side, I can certainly do a 1 month follow-up or something on feedback and usage stats.

Um, yeah...I'm going to need you to come in on Saturday -- Bill Lumbergh, Office Space
User avatar
tarwn
LTD Admin
LTD Admin
LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897
LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897
LTD Gold - Rating: 897
 
Posts: 3838
Joined: Fri Oct 12, 2007 11:10 am
Location: Raleigh, NC, USA
Unrated