Menu
This seems like a fairly obvious question, but I haven't been able to think of the proper term for what I am trying to ask, so coming up with reference material for this has been tricky. The answers seem obvious, though.
When examining the Pluralsight training material for SQL Server, they recommended always referring to tables in both "regular" queries (something you might write for a basic web service) and for a SQL query in perhaps a stored procedure, like the following:
[databasename].[dbo].[some_table].[sometimesacolumngoeshere]
Though I have found it very common to come across stored procs, etc., that simply use:
my_column or [my_column]
The difference here is obviously that one provides an absolute "address" explicitly, whereas the other is implicit.
How do you know when it is appropriate to use one over the other, and also what do you call this, addressing?
My preference would be to always be explicit, and if you need to save space and/or make things more clear, you could alias to an explicit full "address", correct?
277
Syntax. CREATE TABLE table_name( column1 datatype, column2 datatype, column3 datatype, ..... columnN datatype, PRIMARY KEY( one or more columns ) ); CREATE TABLE is the keyword telling the database system what you want to do.
All the SQL statements start with any of the keywords like SELECT, INSERT, UPDATE, DELETE, ALTER, DROP, CREATE, USE, SHOW and all the statements end with a semicolon (;). The most important point to be noted here is that SQL is case insensitive, which means SELECT and select have same meaning in SQL statements.
1 Answer. The correct answer to the question “Which of the SQL statements is correct” is option (b). SELECT Username, Password FROM Users. And all the other options are incorrect.
Formatting: Carefully use Indentation & White spaces Even though it's a basic principle, it's a quick win to make your code more readable. As you would do with python, you should ident your SQL code. Ident after a keyword, and when you use a subquery or a derived table.
You are correct. Basically SQL will attempt to find the field you are looking for "my_column" in all of the tables in your FROM and JOIN sections. If however you happen to have a "my_column" in table A and in table B then you need to explicitly tell it which "my_column" you are looking for by including the table name. This goes on up the chain to dbo and databasename if you have collisions there as well.
Most of the time you will find that people don't explicitly call out the tables a column is in unless they are joining multiple tables.
For example I write my queries like this:
SELECT a.field1, b.field2
FROM tableA AS a
INNER JOIN tableB AS b ON a.id = b.a_id
WHERE a.id = 123
Here I am using the AS to alias tableA and tableB down to more readable a and b. I could have just as easily written my query like this:
SELECT tableA.field1, tableB.field2
FROM tableA
INNER JOIN tableB ON tableA.id = tableB.a_id
WHERE tableA.id = 123
Or like this, if field1 and field2 are unique to there tables, but this gets a bit confusing as to where each piece of data is coming from.
SELECT field1, field2
FROM tableA
INNER JOIN tableB ON tableA.id = tableB.a_id
WHERE tableA.id = 123
SQL Server has four part names:
Most often you reference an object by name
SELECT * FROM MyTable
Then you can specify the owner or schema of the object:
SELECT * FROM dbo.MyTable
Then you can reference the database that the object lives in:
SELECT * FROM master.dbo.MyTble
Finally you can reference the table on a different server
SELECT * FROM test1.master.dbo.MyTable
It's explained better than I can on MSDN
30
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
