All Questions

Tagged with
Filter by
Sorted by
Tagged with
5049votes
27answers
2.5mviews

What is the difference between "INNER JOIN" and "OUTER JOIN"?

Also how do LEFT JOIN, RIGHT JOIN and FULL JOIN fit in?
user avatar
2747votes
7answers
1.0mviews

How does database indexing work? [closed]

Given that indexing is so important as your data set increases in size, can someone explain how indexing works at a database-agnostic level? For information on queries to index a field, check out How ...
user avatar
  • 80.1k
1664votes
27answers
3.0mviews

Insert into ... values ( SELECT ... FROM ... )

I am trying to INSERT INTO a table using the input from another table. Although this is entirely feasible for many database engines, I always seem to struggle to remember the correct syntax for the ...
user avatar
  • 38.1k
1494votes
7answers
299kviews

What are the options for storing hierarchical data in a relational database?

Good Overviews Generally speaking, you're making a decision between fast read times (for example, nested set) or fast write times (adjacency list). Usually, you end up with a combination of the ...
1298votes
17answers
1.1mviews

How can I list the tables in a SQLite database file that was opened with ATTACH?

What SQL can be used to list the tables, and the rows within those tables in an SQLite database file - once I have attached it with the ATTACH command on the SQLite 3 command line tool?
user avatar
  • 47.5k
865votes
21answers
1.3mviews

Reset identity seed after deleting records in SQL Server

I have inserted records into a SQL Server database table. The table had a primary key defined and the auto increment identity seed is set to “Yes”. This is done primarily because in SQL Azure, each ...
user avatar
  • 16.5k
738votes
7answers
1.5mviews

'IF' in 'SELECT' statement - choose output value based on column values

SELECT id, amount FROM report I need amount to be amount if report.type='P' and -amount if report.type='N'. How do I add this to the above query?
user avatar
  • 13.2k
683votes
23answers
799kviews

Solutions for INSERT OR UPDATE on SQL Server

Assume a table structure of MyTable(KEY, datafield1, datafield2...). Often I want to either update an existing record, or insert a new record if it doesn't exist. Essentially: IF (key exists) run ...
user avatar
635votes
11answers
394kviews

Skip certain tables with mysqldump

Is there a way to restrict certain tables from the mysqldump command? For example, I'd use the following syntax to dump only table1 and table2: mysqldump -u username -p database table1 table2 > ...
618votes
18answers
924kviews

How do I restore a dump file from mysqldump?

I was given a MySQL database file that I need to restore as a database on my Windows Server 2008 machine. I tried using MySQL Administrator, but I got the following error: The selected file was ...
user avatar
580votes
9answers
392kviews

What is the difference between Left, Right, Outer and Inner Joins? [duplicate]

I am wondering how to differentiate all these different joins ...
user avatar
  • 21.1k
449votes
33answers
928kviews

How to select the nth row in a SQL database table?

I'm interested in learning some (ideally) database agnostic ways of selecting the nth row from a database table. It would also be interesting to see how this can be achieved using the native ...
user avatar
447votes
26answers
1.4mviews

MySQL Error: : 'Access denied for user 'root'@'localhost'

Consider: ./mysqladmin -u root -p** '_redacted_' Output (including typing the password): Enter password: mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'root'@'...
user avatar
396votes
10answers
286kviews

Best database field type for a URL

I need to store a url in a MySQL table. What's the best practice for defining a field that will hold a URL with an undetermined length?
user avatar
387votes
10answers
840kviews

How to replace a string in a SQL Server Table Column

I have a table (SQL Sever) which references paths (UNC or otherwise), but now the path is going to change. In the path column, I have many records and I need to change just a portion of the path, ...
user avatar
  • 6,954

15 30 50 per page
1
2 3 4 5
2847