• If I want to see what fields a
table is made of, and what the sizes of the
fields are, what option do I have to look for?
Sp_Columns ‘TableName'
• What is a query?
A request for information from a database. There are three general methods for
posing queries:
# Choosing parameters from a menu: In this method, the database system presents
a list of parameters from which you can choose. This is perhaps the easiest way
to pose a query because the menus guide you, but it is also the least flexible.
# Query by example (QBE): In this method, the system presents a blank record
and lets you specify the fields and values that define the query.
# Query language: Many database systems require you to make requests for
information in the form of a stylized query that must be written in a special
query language. This is the most complex method because it forces you to learn
a specialized language, but it is also the most powerful.
• What is the purpose of the model
database?
It works as Template Database for the Create Database Syntax
• What is the purpose of the master
database?
Master database keeps the information about sql server configuration, databases
users etc
• What is the purpose of the tempdb
database?
Tempdb database keeps the information about the temporary objects (#TableName,
#Procedure). Also the sorting, DBCC operations are performed in the TempDB
• What is the purpose of the USE
command?
Use command is used for to select the database. For i.e Use Database Name
• If you delete a table in the
database, will the data in the table be deleted too?
Yes
• What is the Parse Query button
used for? How does this help you?
Parse query button is used to check the SQL Query Syntax
• Tables are created in a
____________________ in SQL Server 2005.
resouce database(System Tables)
• What is usually the first word in
a SQL query?
SELECT
• Does a SQL Server 2005 SELECT
statement require a FROM?
NO
• Can a SELECT statement in SQL
Server 2005 be used to make an assignment? Explain with examples.
Yes. Select @MyDate = GetDate()
• What is the ORDER BY used for?
Order By clause is used for sorting records in Ascending or Descending order
• Does ORDER BY actually change the
order of the data in the tables or does it just
change the output?
Order By clause change only the output of the data
• What is the default order of an
ORDER BY clause?
Ascending Order
• What kind of comparison operators
can be used in a WHERE clause?
• What are four major operators that
can be used to combine conditions on a WHERE
clause?
OR, AND, IN and BETWEEN
• What are the logical operators?
|
Operator
|
Meaning
|
|
ALL
|
TRUE if all of a set of
comparisons are TRUE.
|
|
AND
|
TRUE if both Boolean expressions
are TRUE.
|
|
ANY
|
TRUE if any one of a set of
comparisons are TRUE.
|
|
BETWEEN
|
TRUE if the operand is within a
range.
|
|
EXISTS
|
TRUE if a subquery contains any
rows.
|
|
IN
|
TRUE if the operand is equal to
one of a list of expressions.
|
|
LIKE
|
TRUE if the operand matches a
pattern.
|
|
NOT
|
Reverses the value of any other
Boolean operator.
|
|
OR
|
TRUE if either Boolean expression
is TRUE.
|
|
SOME
|
TRUE if some of a set of
comparisons are TRUE.
|
•In a WHERE clause, do you need to
enclose a text column in quotes? Do you need to enclose a numeric column in
quotes?
Enclose Text in Quotes (Yes)
Enclose Number in Quotes (NO)
• Is a null value equal to anything?
Can a space in a column be considered a null value? Why or why not?
No NULL value means nothing. We can't consider space as NULL value.
• Will COUNT(column) include columns
with null values in its count?
Yes, it will include the null column in count
• What are column aliases? Why would
you want to use column aliases? How can you embed blanks in column aliases?
You can create aliases for column names to make it easier to work with column
names, calculations, and summary values. For example, you can create a column
alias to:
* Create a column name, such as “Total Amount,” for an expression such as
(quantity * unit_price) or for an aggregate function.
* Create a shortened form of a column name, such as “d_id” for
“discounts.stor_id.”
After you have defined a column alias, you can use the alias in a Select query
to specify query output
• What are table aliases?
Aliases can make it easier to work with table names. Using aliases is helpful
when:
* You want to make the statement in the SQL Pane shorter and easier to read.
* You refer to the table name often in your query — such as in qualifying
column names — and want to be sure you stay within a specific character-length
limit for your query. (Some databases impose a maximum
length for queries.)
* You are working with multiple instances of the same table (such as in a
self-join) and need a way to refer to one instance or the other.
• What are table qualifiers? When
should table qualifiers be used?
[@table_qualifier =] qualifier
Is the name of the table or view qualifier. qualifier is sysname, with a
default of NULL. Various DBMS products support three-part naming for tables
(qualifier.owner.name). In SQL Server, this column represents the database
name. In some products, it represents the server name of the table's database
environment.
• Are semicolons required at the end
of SQL statements in SQL Server 2005?
No it is not required
• Do comments need to go in a special place in SQL Server 2005?
No its not necessary
• When would you use the ROWCOUNT function versus using the WHERE clause?
Returns the number of rows affected by the last statement. If the number of
rows is more than 2 billion, use ROWCOUNT_BIG.
Transact-SQL statements can set the value in @@ROWCOUNT in the following ways:
* Set @@ROWCOUNT to the number of rows affected or read. Rows may or may not be
sent to the client.
* Preserve @@ROWCOUNT from the previous statement execution.
* Reset @@ROWCOUNT to 0 but do not return the value to the client.
Statements that make a simple assignment always set the @@ROWCOUNT value to 1.
• Is SQL case-sensitive? Is SQL Server 2005 case-sensitive?
No both are not case-sensitive. Case sensitivity depends on the collation you
choose.
If you installed SQL Server with the default collation options, you might find
that the following queries return the same results:
CREATE TABLE mytable
(
mycolumn VARCHAR(10)
)
GO
SET NOCOUNT ON
INSERT mytable VALUES(‘Case')
GO
SELECT mycolumn FROM mytable WHERE
mycolumn='Case'
SELECT mycolumn FROM mytable WHERE mycolumn='caSE'
SELECT mycolumn FROM mytable WHERE mycolumn='case'
You can alter your query by forcing
collation at the column level:
SELECT myColumn FROM myTable
WHERE myColumn COLLATE Latin1_General_CS_AS = ‘caSE'
SELECT myColumn FROM myTable
WHERE myColumn COLLATE Latin1_General_CS_AS = ‘case'
SELECT myColumn FROM myTable
WHERE myColumn COLLATE Latin1_General_CS_AS = ‘Case'
– if myColumn has an index, you will
likely benefit by adding
– AND myColumn = ‘case'
• What is a synonym? Why would you want to create a synonym?
SYNONYM is a single-part name that can replace a two, three or four-part name
in many SQL statements. Using SYNONYMS in RDBMS cuts down on typing.
SYNONYMs can be created for the following objects:
* Table
* View
* Assembly (CLR) Stored Procedure
* Assembly (CLR) Table-valued Function
* Assembly (CLR) Scalar Function
* Assembly Aggregate (CLR) Aggregate Functions
* Replication-filter-procedure
* Extended Stored Procedure
* SQL Scalar Function
* SQL Table-valued Function
* SQL Inline-table-valued Function
* SQL Stored Procedure
Syntax
CREATE SYNONYM [ schema_name_1. ] synonym_name FOR < object >
< object > :: =
{
[ server_name.[ database_name ] . [ schema_name_2 ].| database_name . [
schema_name_2 ].| schema_name_2. ] object_name
}
• Can a synonym name of a table be
used instead of a table name in a SELECT statement?
Yes
• Can a synonym of a table be used
when you are trying to alter the definition of a table?
Not Sure will try
• Can you type more than one query
in the query editor screen at the same time?
Yes we can.
• While you are inserting values
into a table with the INSERT INTO .. VALUES option, does the order of the
columns in the INSERT statement have to be the same as the order of the columns
in the table?
Not Necessary
• While you are inserting values
into a table with the INSERT INTO .. SELECT option, does the order of the
columns in the INSERT statement have to be the same as the order of the columns
in the table?
Yes if you are not specifying the column names in the insert clause, you need
to maintain the column order in SELECT statement
• When would you use an INSERT INTO
.. SELECT option versus an INSERT INTO .. VALUES option? Give an example of
each.
INSERT INTO .. SELECT is used insert data in to table from diffrent tables or
condition based insert
INSERT INTO .. VALUES you have to specify the insert values
• What does the UPDATE command do?
Update command will modify the existing record
• Can you change the data type of a column in a table after the table has been
created? If so,which command would you use?
Yes we can. Alter Table Modify Column
• Will SQL Server 2005 allow you to
reduce the size of a column?
Yes it allows
• What integer data types are
available in SQL Server 2005?
Exact-number data types that use
integer data.
|
Data
type
|
Range
|
Storage
|
|
bigint
|
-2^63 (-9,223,372,036,854,775,808)
to 2^63-1 (9,223,372,036,854,775,807)
|
8 Bytes
|
|
int
|
-2^31 (-2,147,483,648) to 2^31-1
(2,147,483,647)
|
4 Bytes
|
|
smallint
|
-2^15 (-32,768) to 2^15-1 (32,767)
|
2 Bytes
|
|
tinyint
|
0 to 255
|
1 Byte
|
• What is the default value of an integer data type in SQL Server 2005?
NULL
• What is the difference between a CHAR and a VARCHAR datatype?
CHAR and VARCHAR data types are both non-Unicode
character data types with a maximum length of 8,000 characters. The main
difference between these 2 data types is that a CHAR data type is
fixed-length while a VARCHAR is variable-length. If the number of
characters entered in a CHAR data type column is less than the declared
column length, spaces are appended to it to fill up the whole length.
Another difference is in the storage
size wherein the storage size for CHAR is n bytes while for VARCHAR
is the actual length in bytes of the data entered (and not n bytes).
You should use CHAR data type
when the data values in a column are expected to be consistently close to the
same size. On the other hand, you should use VARCHAR when the data
values in a column are expected to vary considerably in size.
• Does Server SQL treat CHAR as a
variable-length or fixed-length column?
SQL Server treats CHAR as fixed length column
• If you are going to have too many
nulls in a column, what would be the best data type to use?
Variable length columns only use a very small amount of space to store a NULL
so VARCHAR datatype is the good option for null values
• When columns are added to existing tables, what do they initially contain?
The column initially contains the NULL values
• What command would you use to add a column to a table in SQL Server?
ALTER TABLE tablename ADD column_name DATATYPE
• Does an index slow down updates on
indexed columns?
Yes
• What is a constraint?
Constraints in Microsoft SQL Server 2000/2005 allow us to define the ways in
which we can automatically enforce the integrity of a database. Constraints
define rules regarding permissible values allowed in columns and are the
standard mechanism for enforcing integrity. Using constraints is preferred to
using triggers, stored procedures, rules, and defaults, as a method of
implementing data integrity rules. The query optimizer also uses constraint
definitions to build high-performance query execution plans.
• How many indexes does SQL Server
2005 allow you to have on a table?
250 indices per table
• What command would you use to
create an index?
CREAT INDEX INDEXNAME ON TABLE(COLUMN NAME)
• What is the default ordering that
will be created by an index (ascending or descending)?
Clustered indexes can be created in SQL Server databases. In such cases the
logical order of the index key values will be the same as the physical order of
rows in the table.
By default it is ascending order, we can also specify the index order while
index creation.
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
ON { table | view } ( column [ ASC | DESC ] [ ,...n ] )
• How do you delete an index?
DROP INDEX authors.au_id_ind
• What does the NOT NULL constraint
do?
Constrain will not allow NULL values in the column
• What command must you use to
include the NOT NULL constraint after a table has already been created?
DEFAULT, WITH CHECK or WITH NOCHECK
• When a PRIMARY KEY constraint is
included in a table, what other constraints does this imply?
Unique + NOT NULL
• What is a concatenated primary key?
Each table has one and only one primary key, which can consist of one or many
columns. A concatenated primary key comprises two or more columns. In a single
table, you might find several columns, or groups of columns, that might serve
as a primary key and are called candidate keys. A table can have more than one
candidate key, but only one candidate key can become the primary key for that
table
• How are the UNIQUE and PRIMARY KEY
constraints different?
A UNIQUE constraint is similar to PRIMARY key, but you can have more than one
UNIQUE constraint per table.
When you declare a UNIQUE
constraint, SQL Server creates a UNIQUE index to speed up the process of
searching for duplicates. In this case the index defaults to NONCLUSTERED
index, because you can have only one CLUSTERED index per table.
* The number of UNIQUE constraints
per table is limited by the number of indexes on the table i.e 249 NONCLUSTERED
index and one possible CLUSTERED index.
Contrary to PRIMARY key UNIQUE
constraints can accept NULL but just once. If the constraint is defined in a
combination of fields, then every field can accept NULL and can have some
values on them, as long as the combination values is unique.
• What is a referential integrity constraint? What two keys does the
referential integrity constraint usually include?
Referential integrity in a relational database is consistency between coupled
tables. Referential integrity is usually enforced by the combination of a primary
key or candidate key (alternate key) and a foreign key. For referential
integrity to hold, any field in a table that is declared a foreign key can
contain only values from a parent table's primary key or a candidate key. For
instance, deleting a record that contains a value referred to by a foreign key
in another table would break referential integrity. The relational database
management system (RDBMS) enforces referential integrity, normally either by
deleting the foreign key rows as well to maintain integrity, or by returning an
error and not performing the delete. Which method is used would be determined
by the referential integrity constraint, as defined in the data dictionary.
• What is a foreign key?
FOREIGN KEY constraints identify the relationships between tables.
A foreign key in one table points to a candidate key in another table. Foreign
keys prevent actions that would leave rows with foreign key values when there
are no candidate keys with that value. In the following sample, the order_part
table establishes a foreign key referencing the part_sample table defined
earlier. Usually, order_part would also have a foreign key against an order
table, but this is a simple example.
CREATE TABLE order_part
(order_nmbr int,
part_nmbr int
FOREIGN KEY REFERENCES part_sample(part_nmbr)
ON DELETE NO ACTION,
qty_ordered int)
GO
You cannot insert a row with a
foreign key value (except NULL) if there is no candidate key with that value.
The ON DELETE clause controls what actions are taken if you attempt to delete a
row to which existing foreign keys point. The ON DELETE clause has two options:
NO ACTION specifies that the
deletion fails with an error.
CASCADE specifies that all the rows
with foreign keys pointing to the deleted row are also deleted.
The ON UPDATE clause defines the actions that are taken if you attempt to
update a candidate key value to which existing foreign keys point. It also
supports the NO ACTION and CASCADE options.
• What does the ON DELETE CASCADE option do?
ON DELETE CASCADE
Specifies that if an attempt is made to delete a row with a key referenced by
foreign keys in existing rows in other tables, all rows containing those
foreign keys are also deleted. If cascading referential actions have also been
defined on the target tables, the specified cascading actions are also taken
for the rows deleted from those tables.
ON UPDATE CASCADE
Specifies that if an attempt is made to update a key value in a row, where the
key value is referenced by foreign keys in existing rows in other tables, all
of the foreign key values are also updated to the new value specified for the
key. If cascading referential actions have also been defined on the target
tables, the specified cascading actions are also taken for the key values
updated in those tables.
• What does the ON UPDATE NO ACTION do?
ON DELETE NO ACTION
Specifies that if an attempt is made to delete a row with a key referenced by
foreign keys in existing rows in other tables, an error is raised and the
DELETE is rolled back.
ON UPDATE NO ACTION
Specifies that if an attempt is made to update a key value in a row whose key
is referenced by foreign keys in existing rows in other tables, an error is
raised and the UPDATE is rolled back.
• Can you use the ON DELETE and ON
UPDATE in the same constraint?
Yes we can.
CREATE TABLE part_sample
(part_nmbr int PRIMARY KEY,
part_name char(30),
part_weight decimal(6,2),
part_color char(15) )
CREATE TABLE order_part
(order_nmbr int,
part_nmbr int
FOREIGN KEY REFERENCES part_sample(part_nmbr)
ON DELETE NO ACTION ON UPDATE NO ACTION,
qty_ordered int)
GO