SQL Review

Table: What is the difference between a table and a view?

A TABLE contains actual data made up of rows and columns; A view is created from these tables known as the base table;A table does not provide security to the stored data.

View: What is the difference between a table and a view?

A VIEW on the other hand is a virtual table consisting of columns from one or more tables. It is a query stored as an object in the database and serves as a security mechanism by restricting access.

What is the difference between a temporary and variable table?

Both table variables and temp tables are stored in tempdb. But table variables (since 2005) default to the collation of the current database versus temp tables which take the default collation of tempdb.

Left Join

Is the same as Left Outer Join and it is when the result set contains all values in the left table matching values in the right table and if non matches in the right table, the values are null.

Right Join

Is the same as Right Outer Join and it is when the result set contains all values in the right table matching values in the left table and if non matches in the left table, the values are null.

What is the difference between a temporary and variable table?

Table variables don't participate in transactions, logging or locking. Temp tables might result in stored procedures being recompiled but Table variables will not.

What is the difference between a temporary and variable table?

A temporary tables is real table where you can do things like CREATE INDEXES while variable table can have indexes by using PRIMARY KEY or UNIQUE constraints

Inner Join

The result set contains values existing in both tables

What is the difference between a temporary and variable table?

Using Table Variables within user-defined functions enables those functions to be used more widely

Transact-Structured Query Language

What does the acronym T-SQL stand for?

Insert Into

What keyword in a SQL query do you use to add data from a database table?

Select

What keyword in a SQL query do you use to extract data from a database table?

Alter Table

What keyword in a SQL query do you use to modify data from a database table?

What is the difference between a temporary and variable table?

You can create a temp table using SELECT INTO, which can be quicker to write