Database Administrators Stack Exchange
Database Administrators Stack Exchange

Most Popular

more tags
1500 questions
106
votes
9 answers

Where are some useful SQL puzzles to teach SQL in a workplace?

I'm looking for beginner and intermediate level SQL puzzles, that I can point trainees at for practice. I'm aware of http://sqlzoo.net/ which is a great resource - is there anything else out there that you could suggest?
testerab
  • 477
  • 3
  • 8
  • 13
106
votes
4 answers

How to use sqlplus to connect to an Oracle Database located on another host without modifying my own tnsnames.ora

I want to connect to an oracle database located on another host using sqlplus. This page suggested adding an item on my tnsnames to connect to that database local_SID = (DESCRIPTION = (ADDRESS = (PROTOCOL= TCP)(Host= hostname.network)(Port=…
Louis Rhys
  • 1,255
  • 3
  • 11
  • 8
105
votes
2 answers

Difference between now() and current_timestamp

In PostgreSQL, I use the now() and current_timestamp function and I see no difference: # SELECT now(), current_timestamp; now | now …
JohnMerlino
  • 1,739
  • 5
  • 18
  • 21
104
votes
6 answers

Retrieving n rows per group

I often need to select a number of rows from each group in a result set. For example, I might want to list the 'n' highest or lowest recent order values per customer. In more complex cases, the number of rows to list might vary per group (defined by…
Paul White
  • 83,961
  • 28
  • 402
  • 634
103
votes
18 answers

Is adding the ‘tbl’ prefix to table names really a problem?

I’m watching some Brent Ozar videos (like this one, for instance) and he suggests not prefixing tables with ‘tbl’ or ‘TBL’. On the internet I found some blogs saying it adds nothing to documentation, and also that “it takes longer to read…
Racer SQL
  • 7,386
  • 14
  • 69
  • 128
103
votes
8 answers

Does SQL Server read all of a COALESCE function even if the first argument is not NULL?

I'm using a T-SQL COALESCE function where the first argument will not be null on about 95% of the times it is ran. If the first argument is NULL, the second argument is quite a lengthy process: SELECT COALESCE(c.FirstName ,(SELECT…
Curtis
  • 1,255
  • 2
  • 8
  • 9
101
votes
7 answers

Very slow DELETE in PostgreSQL, workaround?

I have a database on PostgreSQL 9.2 that has a main schema with around 70 tables and a variable number of identically structured per-client schemas of 30 tables each. The client schemas have foreign keys referencing the main schema and not the other…
jd.
  • 1,112
  • 2
  • 8
  • 6
100
votes
3 answers

Difference between On Delete Cascade & On Update Cascade in mysql

I have two tables in MySQL database- parent, child. I'm trying to add foreign key references to my child table based on the parent table. Is there any significant difference between ON UPDATE CASCADE and ON DELETE CASCADE My Parent Table CREATE…
Smokey
  • 1,111
  • 2
  • 8
  • 8
99
votes
4 answers

SQL server databases stuck in restoring state

I have a Sharepoint server. We had an issue with our backup tool and now some of my databases are stuck in restoring state! Is it possible to stop the restoring process? and also, How can I make sure the database integrity has not been compromised?…
Pooya Yazdani
  • 1,103
  • 1
  • 8
  • 7
98
votes
5 answers

When to use TINYINT over INT?

In general, I always use Ints. I know that in theory this is not the best practice, though, since you should use the smallest data type that will be guaranteed to store the data. For example, it's better to use tinyint when you know that the only…
Richard
  • 6,393
  • 8
  • 42
  • 62
98
votes
3 answers

How do I insert a row which contains a foreign key?

Using PostgreSQL v9.1. I have the following tables: CREATE TABLE foo ( id BIGSERIAL NOT NULL UNIQUE PRIMARY KEY, type VARCHAR(60) NOT NULL UNIQUE ); CREATE TABLE bar ( id BIGSERIAL NOT NULL UNIQUE PRIMARY KEY, description…
Stéphane
  • 5,485
  • 4
  • 18
  • 12
96
votes
8 answers

How to query last restore date in SQL Server?

Is there a T-SQL query that shows the last restore datetime for a certain database?
Kees C. Bakker
  • 1,243
  • 1
  • 8
  • 9
96
votes
4 answers

Authoritative source that <> and != are identical in performance in SQL Server

Consider this answer on SO that reassures the asker about the <> operator that: <> is ... the same as !=. But then a commenter pipes up and says: It's true that they are, functionally, the same. However, how the SQL optimizer uses them is very…
ErikE
  • 4,305
  • 4
  • 28
  • 39
95
votes
5 answers

Working of indexes in PostgreSQL

I have a couple of questions regarding working of indexes in PostgreSQL. I have a Friends table with the following index: Friends ( user_id1 ,user_id2) user_id1 and user_id2 are foreign keys to user table Are these equivalent? If not then why?…
codecool
  • 1,983
  • 2
  • 16
  • 21
95
votes
6 answers

SQL Server Cannot drop database because it is currently in use... but no sessions displayed

When I try to drop a database I get the error "Cannot drop database "dbname" because it is currently in use". However, when I run sp_who2, there are definitely no sessions connected to this database. I've also set the database to single_user mode…
tuseau
  • 1,885
  • 5
  • 18
  • 18
1 2 3
…
99 100