What are the restrictions that views have to follow?
· Since a view is a virtual table – columns of the view cannot be renamed. To change anything in the view, the view must be dropped and create again.
· The select statement on the view cannot contain ORDER BY or INTO TEMP
· When a table or view is dropped, any views in the same database are also dropped.
· It is not possible to create an index on a view
· It is not possible to use DELETE to update a view that is defined as a join.
It defines how can data be stored efficiently. All the commands used to create, delete databases like CREATE or DELETE are a part of data definition language.
Create table: Used to create a table in sql. Tables store the data. Each row in a table has a unique identifier called as a primary key.
Syntax:
CREATE TABLE table_name (
Column_name1 data_type,
Column_name2 data_type,
PRIMARY KEY (Column_name1));
Example:
CREATE TABLE customer (
Id Integer(10)
First_name Varchar(200)
Last name varchar(200)
PRIMARY KEY(id));
CREATE TABLE table_name (
Column_name1 data_type,
Column_name2 data_type,
PRIMARY KEY (Column_name1));
Example:
CREATE TABLE customer (
Id Integer(10)
First_name Varchar(200)
Last name varchar(200)
PRIMARY KEY(id));
Data manipulation language is used for managing data. Managing data would mean retrieving, inserting, deleting or updating data. SQL is the most common data manipulation language. SELECT, INSERT, UPDATE keywords are a part of Data manipulation language. Data manipulation language can be procedural or declarative.
a. Select: Select statement is used to select data from a table.
Syntax:
Select column_name
From table_name
For selecting all columns
Select * from table_name
Select column_name
From table_name
For selecting all columns
Select * from table_name
Example:
Select * from customer;
Select * from customer;
b. Insert: Insert statement is used to insert data (row) in a table.
Syntax:
Insert into table_name
Values (value1, 2 ..)
Insert into table_name
Values (value1, 2 ..)
Example:
Insert into customer values (1,’steve’,’james’);
Insert into customer values (1,’steve’,’james’);
c. Update: Update statement is used to update existing data (row) in a table. It is used to update some column value by specifying a condition.
Syntax:
Update table_name Set column_name1= ‘value’, column_name2=’value2’ Where column_name3=’value3’
Update table_name Set column_name1= ‘value’, column_name2=’value2’ Where column_name3=’value3’
Example:
Update customer Set first_name=’john’ Where last_name=’james’;
Update customer Set first_name=’john’ Where last_name=’james’;
A data control language is a computer language used to control access data in a database. It is used to manipulate and control the data. Examples of commands that are a part of Data control language: SELECT, CONNECT, INSERT, UPDATE etc
a. Grant: Grant is used to grant or give access permission to a user. These permissions can be given to a user or a role. Grant can be used to give system privileges or object privileges.
Syntax:
Grant ALL | system_privelege on object_name to user_name
Grant ALL | system_privelege on object_name to user_name
Example:
Grant update on customer to steve
Grant update on customer to steve
b. Revoke: Removes or takes away a previously assigned or granted privilege.
Syntax:
Revoke ALL | privilege_name
On object_name
From user_name
Revoke ALL | privilege_name
On object_name
From user_name
Example:
Revoke update
On customer
From steve
Revoke update
On customer
From steve
c. Deny: Used to deny permissions to a user.
Syntax:
Deny ALL | permission_name
On object_name
TO user_name
Deny ALL | permission_name
On object_name
TO user_name
Example:
Deny update
On customer
To steve
Deny update
On customer
To steve
Explain the full syntax of Select Statement with examples.
SELECT is used to select a specific or ALL columns / rows from a table.
Syntax:
SELECT list_columns FROM table_name
SELECT list_columns FROM table_name
List_columns are the columns to be selected to retrieve the rows. While table_name is the table from which these columns needs to be selected. Many clauses can be added to this SELECT statement.
Example 1: Displays rows with customers balance between 10 and 10000
SELECT first_name FROM Customer WHERE cust_bal BETEEN 10 and 10000;
SELECT first_name FROM Customer WHERE cust_bal BETEEN 10 and 10000;
Example 2: Sort the names in an ascending order
SELECT first_name FROM Customer ORDER BY first_name
SELECT first_name FROM Customer ORDER BY first_name
Explain some of the keywords of Select Statement like Distinct Keyword, Top n Keyword with an example for each of them.
DISTINCT: Distinct clause helps to remove duplicates and returns a result set.
Syntax: DISTINCT (set_expression)
Example:
Select DISTINCT company_name FROM Company;
TOP N: returns the top “n” records from a table.
Syntax : TOP N
Example:
Select TOP 10 Cust_name from Customer;
Select TOP 10 Cust_name from Customer;
Describe the use of Into and From clause with examples.
INTO: The INTO keyword inserts data from one table into another. It is commonly used in SELECT statement. It is useful in creating back ups.
SYNTAX:
SELECT * INTO new_table FROM old_table
SELECT * INTO new_table FROM old_table
Example: Copy all columns from table customer into customer_backup
SELECT * INTO Customer_backup FROM customer
SELECT * INTO Customer_backup FROM customer
FROM: The FROM clause selects rows from the table specified.
Syntax:
SELECT * FROM table_name
SELECT * FROM table_name
Example: Select all columns from a table customer
SELECT * FROM Customer
Describe where, group by and having clause with examples for each of them.
WHERE: Where clause in a SELECT query is used to retrieve specific records that meet the specified criteria.
SYNTAX:
SELECT column_name FROM Table_name WHERE predicate
SELECT column_name FROM Table_name WHERE predicate
Example: to display customers with first name is john
Select first_name From customer Where first_name =’john’;
Select first_name From customer Where first_name =’john’;
GROUP BY: Group By clause in select statement is used to group or collect data of multiple records of one ore more columns.
SYNTAX: SELECT column_name FROM table_name WHERE predicate GROUP BY column1, column2..;
Example: To Group records by city SELECT city_name FROM Customer GROUP BY city;
HAVING: The HAVING clause is used to filter records that have been grouped using GROUP BY clause. Hence HAVING clause is used with GROUP BY.
Syntax: SELECT column_name FROM table_name WHERE predicates GROUP BY column1,.. HAVING condition1…
Example: To Group records by employee with salary > 10000 SELECT emp_name FROM employee GROUP BY emp_name HAVING salary > 1000;
Define Subqueries. Explain properties of sub-query.
A subquery is a query within a query. These sub queries are created with SQL statements. These subqueries are embedded within SELECT, FROM or the WHERE clause.
Properties:-
· The SELECT query of a subquery is always enclosed in parentheses
· View created by using a subquery cannot be updated.
· The ntext, text, and image data types cannot be used in the select list of sub queries
· If a table appears only in a subquery and not in the outer query, then columns from that table cannot be included in the output
Explain with examples for the Subqueries with IN and NOT IN.
Sub Query Example with IN: Displays employees from employee table with bonus > 1000. Using IN first all employees are selected and compared to each row of the subquery. Select first_name from employee
Where bonus_id IN (select id from bonus Where bonus_amt > 1000);
Sub Query Example with NOT IN: Displays employees from employee table with bonus < 1000. Using NOT IN first all employees are selected and compared to each row of the subquery.
Select first_name from employee Where bonus_id NOT IN (select id from bonus Where bonus_amt < 1000);
Explain the subqueries with comparison operators.
Comparison operators can be used (like <, >, =, !> etc). Sub queries used with comparison operators must return a single value rather than a list to avoid error. Hence the nature of the database must be knows before executing such sub queries.
Example: To display employees who have been referred by John whose id 276
SELECT employeeID FROM employee. employee_name WHERE referenceID = (SELECT referenceID FROM employee.firstname WHERE EmpID = 276)
Example: names of all employees whose salary is greater than the average salary
SELECT Employee_ID FROM Employee.Emp_name WHERE salary > (SELECT AVG (salary) FROM Employee.Emp_name)
Explain with examples for the Subqueries with Exists and NOT Exists.
A subquery with Exist does not really return any data; it returns TRUE or FALSE.
Example: This select statement will return all records from the sales table where there is at least one record in the orders table with the same sales _id.
SELECT * FROM sales WHERE EXISTS (select * from orders where sales.sales_id = orders.sales_id);
Example for NOT EXIST: This query will work exactly the opposite to above. I.e except for the sane sales_id all other records will be returned
SELECT * FROM sales WHERE NOT EXISTS (select * from orders where sales.sales_id = orders.sales_id);
Brief about Insert Statement with an example.
INSERT: The Insert statement is used to insert values as rows in a table.
Syntax:
INSERT INTO Table_name values (value1, value2,…);
INSERT INTO Table_name (column1, colum2…) values (value1, value2,…);
INSERT INTO Table_name values (value1, value2,…);
INSERT INTO Table_name (column1, colum2…) values (value1, value2,…);
Example
A table customer has fields customer id, customer name, customer salary.
INSERT INTO customer (1,John,20000);
A table customer has fields customer id, customer name, customer salary.
INSERT INTO customer (1,John,20000);
Brief about Select….into statement with an example.
Select into is used to create back up copies of tables. It selects data from one table and inserts into another.
Syntax:
Select * INTO dest_table_name FROM source_table_name
Select * INTO dest_table_name FROM source_table_name
Example: Select data from customer table into customer_backup
Select * INTO customer_backup FROM customer;
Select * INTO customer_backup FROM customer;
It can also be used with WHERE clause for a condition.
Select * INTO customer_backup FROM customer WHERE cust_salary > 1000;
Select * INTO customer_backup FROM customer WHERE cust_salary > 1000;
Define distributed queries.
Distributed queries access data from multiple heterogeneous sources. These data sources may or may not be stored on the same computer. Distributed queries are most useful when there are multiple databases of varied nature. Hence, distributed queries can be executed over these databases in a secure manner.
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’
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.
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
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
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
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
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
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
Parse query button is used to check the SQL Query Syntax
Tables are created in a ____________________ in SQL Server 2005.
resouce database(System Tables)
resouce database(System Tables)
What is usually the first word in a SQL query?
SELECT
SELECT
Does a SQL Server 2005 SELECT statement require a FROM?
NO
NO
Can a SELECT statement in SQL Server 2005 be used to make an assignment? Explain with examples.
Yes. Select @MyDate = GetDate()
Yes. Select @MyDate = GetDate()
What is the ORDER BY used for?
Order By clause is used for sorting records in Ascending or Descending order
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
Order By clause change only the output of the data
What is the default order of an ORDER BY clause?
Ascending Order
Ascending Order
What are four major operators that can be used to combine conditions on a WHERE clause?OR, AND, IN and BETWEEN 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)
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.
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
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
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
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.
* 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.
[@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.
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.
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.
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:
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
* 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 >
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
}
{
[ 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
Yes
Can a synonym of a table be used when you are trying to alter the definition of a table?
Not Sure will try
Not Sure will try
Can you type more than one query in the query editor screen at the same time?
Yes we can.
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
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
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
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
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
Yes it allows
What integer data types are available in SQL Server 2005?
Exact-number data types that use integer data.
What is the default value of an integer data type in SQL Server 2005?
NULL
NULL
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
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
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 indexes per table
250 indexes per table
What command would you use to create an index?
CREAT INDEX INDEXNAME ON TABLE(COLUMN NAME)
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 ] )
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 ] )
What does the NOT NULL constraint do?
Constrain will not allow NULL values in the column
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
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
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.
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
(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.
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 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.
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 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.
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) )
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
(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
ANS:- 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.
ANS:-NCHAR and NVARCHAR data types are both Unicode character data types with a maximum length of 4,000 characters. The main difference between these 2 data types is that an NCHAR data type is fixed-length while an NVARCHAR is variable-length. If the number of characters entered in an NCHAR data type column is less than the specified 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 NCHAR is two times n bytes while for NVARCHAR is two times the number of characters entered (in bytes).You should use NCHAR 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 NVARCHAR when the data values in a column are expected to vary considerably in size.
ANS:- CHAR and NCHAR data types are both character data types that are fixed-length. Below is the summary of the differences between these 2 data types:
CHAR(n)
NCHAR(n)
Character Data Type
Non-Unicode Data
Unicode Data
Maximum Length
8,000
4,000Character Size
1 byte
2 bytes
Storage Size
n bytes
2 times n bytes
You would use NCHAR data type for columns that store characters from more than one character set or when you will be using characters that require 2-byte characters, which are basically the Unicode characters such as the Japanese Kanji or Korean Hangul characters.
ANS:- VARCHAR and NVARCHAR data types are both character data types that are variable-length. Below is the summary of the differences between these 2 data types:
VARCHAR(n)
NVARCHAR(n)
Character Data Type
Non-Unicode Data
Unicode Data
Maximum Length
8,000
4,000
Character Size
1 byte
2 bytes
Storage Size
Actual Length (in bytes)
2 times Actual Length (in bytes)
You would use NVARCHAR data type for columns that store characters from more than one character set or when you will be using characters that require 2-byte characters,
TINYINT, SMALLINT, INT and BIGINT are all the same in the sense that they are all exact number data types that use integer data. The difference between these data types are in the minimum and maximum values that each can contain as well as the storage size required by each data type, as shown in the following table:
Data Type
Minimum Value
Maximum Value
Storage Size
tinyint
0
255
1 byte
smallint
-2^15 (-32,768)
2^15 - 1 (32,767)
2 bytes
int
-2^31 (-2,147,483,648)
2^31 - 1 (2,147,483,647)
4 bytes
bigint
-2^63 (-9,223,372,036,854,775,808)
2^63 - 1 (9,223,372,036,854,775,807)
8 bytes
Choosing which of these data types to use depends on the value you want to store for the column or variable. The rule of thumb is to always use the data type that will require the least storage size. Don't always use INT as your data type for whole numbers if you don't need to. If you simply need to store a value between 0 and 255 then you should define your column as TINYINT.
There is no difference between NUMERIC and DECIMAL data types. They are synonymous to each other and either one can be used. DECIMAL/NUMERIC data types are numeric data types with fixed precision and scale.
DECIMAL (p [, s ])
NUMERIC (p [, s ])
In declaring a DECIMAL or NUMERIC data type, p, which is the precision, specifies the maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision of 38. The s is the scale and it specifies the maximum number of decimal digits that can be stored to the right of the decimal point. Scale, which defaults to 0 if not specified, must be a value from 0 to the precision value.
The following table specifies the storage size required based on the precision specified for the NUMERIC or DECIMAL data type:
Precision
Storage Size
1 - 9
5 bytes
10- 19
9 bytes
20-28
13 bytes
29-38
17 bytes
FLOAT and REAL data types are both approximate number data types for use with floating point numeric data. Floating point data is approximate; not all values in the data type range can be precisely represented. The differences between these 2 data types are in the minimum and maximum values each can hold as well as the storage size required, as specified in the following table:
Data Type
n
Minimum Value
Maximum Value
Precision
Storage Size
float [(n)]
1-24
-1.79E + 308
1.79E + 308
7 digits
4 bytes
25-53
-1.79E + 308
1.79E + 308
15 digits
8 bytes
real
-3.40E + 38
3.40E + 38
7 digits
4 bytes
For FLOAT data type, the n is the number of bits used to store the mantissa in scientific notation and thus dictates the precision and storage size and it must be a value from 1 through 53. If not specified, this defaults to 53. In SQL Server, the synonym for REAL data type is FLOAT(24). If your data requires only a maximum of 7 digits precision, you can either use the REAL data type or FLOAT data type with 24 as the parameter (FLOAT(24)).
A datetime data type is date and time data from January 1, 1753 through December 31, 9999, to an accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds or 0.00333 seconds). Values are rounded to increments of .000, .003, or .007 seconds.
On the other hand, a smalldatetime data type is a date and time data from January 1, 1900, through June 6, 2079, with accuracy to the minute.Smalldatetime values with 29.998 seconds or lower are rounded down to the nearest minute; values with 29.999 seconds or higher are rounded up to the nearest minute.
Values with the datetime data type are stored internally by Microsoft SQL Server as two 4-byte integers. The first 4 bytes store the number of days before or after the base date, January 1, 1900. The base date is the system reference date. Values for datetime earlier than January 1, 1753, are not permitted. The other 4 bytes store the time of day represented as the number of milliseconds after midnight.
The smalldatetime data type stores dates and times of day with less precision than datetime. SQL Server stores smalldatetime values as two 2-byte integers.The first 2 bytes store the number of days after January 1, 1900. The other 2 bytes store the number of minutes since midnight. Dates range from January 1, 1900, through June 6, 2079, with accuracy to the minute.
Data Type,Minimum Value ,Maximum Value,Time Accuracy,Storage Size,smalldatetime,January 1, 1900
June 6, 2079
up to a minute
4 bytes
datetime
January 1, 1753
December 31, 9999
one three-hundredth of a second
8 bytes
smalldatetime is usually used when you don't need to store the time of the day such as in cases of effectivity dates and expiration dates.datetime is used if the time of the day is needed and up to the second accuracy is required.
How is the error handling in stored procedures of SQL Server 2005?
In previous versions of SQL Server you would handle exceptions by checking the @@error global variable immediately after an INSERT, UPDATE or DELETE, and then perform some corrective action if @@error did not equal zero.
SQL Server 2005 provides structured exception handing through TRY CATCH block as other programming language like JAVA, C# etc.
SQL Server 2005 provides structured exception handing through TRY CATCH block as other programming language like JAVA, C# etc.
BEGIN TRY
RAISERROR ('Yaa, I ma the problem', 16,1)
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() as ERROR_NUMBER,
ERROR_SEVERITY() as ERROR_SEVERITY,
ERROR_STATE() as ERROR_STATE,
ERROR_MESSAGE() as ERROR_MESSAGE
END CATCH
ERROR_NUMBER() returns the number of the error.
ERROR_SEVERITY() returns the severity.
ERROR_STATE() returns the error state number.
ERROR_PROCEDURE() returns the name of the stored procedure or trigger where the error occurred.
ERROR_LINE() returns the line number inside the routine that caused the error.
ERROR_MESSAGE() returns the complete text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names or times.
ERROR_SEVERITY() returns the severity.
ERROR_STATE() returns the error state number.
ERROR_PROCEDURE() returns the name of the stored procedure or trigger where the error occurred.
ERROR_LINE() returns the line number inside the routine that caused the error.
ERROR_MESSAGE() returns the complete text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names or times.
Print this post
No comments:
Post a Comment