Q.1 what are NULL values? Why should we avoid permitting null values in database?
Ans. A NULL value in a column implies that the data value for the column is not available. It states that the corresponding value is either unknown or undefined. It is different from zero or "". They should be avoided to avoid the complexity in select & update queries and also because columns which have constraints like primary or foreign key constraints cannot contain a NULL value.
Q.2 Explain the use of the PIVOT and UNPIVOT clauses in SQL server
PIVOT
The pivot clause is used to transform a set of columns into values. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output. In addition, it performs aggregations on the remaining column values if required in the output. The syntax of the PIVOT operator is:
SELECT* from table_name
PIVOT(aggeration_function (value_column) FOR pivot_column IN (column_list)) table_alias
UNPIVOT
The UNPIVOT operator allows database user to normalize the data that has earlier been pivoted. This operator transforms the multiple column values of a record into multiples records with the same values in a single column.
Q.3 To summarize or group data in database, what kind of various clauses or operators can be used? Explain with definition.
SQL Server provides aggregate functions to generate summarized data.
The syntax of aggregate function is:
SELECT aggregate_function([ALL|DISTINCT] expression) FROM table_name
Following aggregate function are :
Avg(): returns the average of values in a numeric expression, all or distinct
Count(): returns the number of values in an expression.
Min(): returns the lowest value in the expression.
Max(): returns the highest value un the expression.
Sum(): returns the sum total of values in a numeric expression.
To view the summarized data in different in different groups based on specific criteria, you can group the data by using the GROUP BY clauses of the SELECT statement.
The GROUP BY clauses summarizes the result set into groups by using the aggregate functions. The HAVING clauses further restricts the result set to produce data based on a condition.The syntax of the GROUP BY clauses is:
SELECT column_list FROM table_name WHERE condition [GROUP BY [ALL] expression [, expression] [HAVING search_
condition]
Q.4 What are range operators in SQL server?
Range operators retrieve data based on a range. The syntax for using range operators in the SELECT statement is:
SELECT column_list FROM table_name WHERE expression1 range_operator expression2 AND expression3
Range operators are of the following types:
Between: specifies an inclusive range to search. The following SQL query retrieves records from the employee table where the number of hours that the employees can avail to go on a vacation is between 20 and 50:
SELECT EmployeeID, Vacationhours FROM Humanresources.Employee WHERE Vacationhours BETWEEN 20 AND 50
NOTBETWEEN: excludes the specified range from the result set. The following SQLquery retrieves records from the employee where the numbers of hours that the employees canavail to go on a vacation is not between 40 and 50:
SELECT Employee, Vacationhours FROM Humanresources.employee WHERE Vacationhours NOT BETWEEN 40 AND 50
Q.5 What is the need of DBCC commands in SQL Server?
The database console commands (DBCC) are a series of statements in transact-SQL programming language to check the physical and logical consistency of a Microsoft SQL Server. These commands are also used to fix existing issues. They are also used for administration and file management.
DBCC have a number of advantages. Their uses is extremely essential in some instances
Occasionally, there have been bad allocations of database pages.
Indexes could be destroyed and corrupted easily.
There could misunderstandings on the part of the SQL server engine.
There could be problems when a large number of updates need to be carried out.
Individual pages may lose their optimal storage footprint.
Q.6 what is the difference between truncate, drop, and delete commands?
Ans. TRUNCATE
TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUNCATE is faster and doesn't use as much undo space as a DELETE.
DROP
The DROP command removes a table from the database. All the tables' rows, indexes and privileges will also be removed. No DML triggers will be fired. The operation cannot be rolled back.
DELETE
The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it. Note that this operation will cause all DELETE triggers on the table to fire.
Q.7 what are the advantages of using a DBMS?
Ans. The main advantages of using DBMS are:
It designed to maintain large volumes of data.
It provides an efficient and easy way to store, update, and retrieve data from a database.
It manages information about the users who interacts with DBMS and the tasks that users can perform on the data.
It provides data security against unauthorized access.
Q.8 explain the various guidelines that need to be followed while creating views.
Ans while creating views, you should consider the following guidelines:
The name of a view must follow the rules for identifiers and must not be the same as that of the table on which it is based.
A view can be created only if there is a SELECT permission on its base table.
A view cannot derive its data from temporary tables.
In a view, ORDER BY cannot be used in SELECT statement.
Q.9 what are synonyms of SQL. Explain with syntax
Ans SQL server provides the concept of synonym that is a single-part alias name for a database object with a name, which has multiple parts.
Synonyms provides a layer of abstraction that protects SQL statements from changes made to the database object. you can create a synonym for a database object by using the CREATE SYNONYM statement.
The syntax of the CREATE SYNONYM statement is:
CREATE SYNONYM [schema_name_1.]
Synonym_name FOR <object>
<object>::=
{
[server_name[database_name].[schema_name_2].|database_name. [schema_name_2.] object_name
}
Q. 10 what are DDL and DML statement?
Ans Data Definition Language (DDL): it is used to define the database, data types, structures and constraints on the data.
Some of the DDL statements are:
CREATE: used to create a new database object, such as a table.
ALTER: used to modify the database objects.
DROP: used to delete the objects.
Data Manipulation Language (DML): it is used to manipulate the data in database objects.
Some of the DML statements are:
INSERT: used to insert a new data record in a table.
UPDATE: used to modify an existing record in a table.
DELETE: used to delete a record from a table.
10 marksQ.1 what are statistics? How it can be created and updated? Explain with syntax.
Ans statistics in SQL Server refers to information that the server collects about the distribution of data in columns and indexes. This data , in turn, is used by the query optimizer to determine the execution plan for returning results when you run a query. Statistics is created automatically when you create indexes. In addition, SQL Server creates statistics for columns that do not have indexes defined on them.
SQL Server can automatically create and update statistics. This feature is set to ON by default.
If you need to use statistics on columns that are not indexed in order to create more optimal execution plans, you can manually create and update statistics.
Creating statistics
Statistics can be created on specific columns of a table or view by using CREATE STATISTICS statement.
The syntax of the CREATE STATISTICS is:
CREATE STATISTICS statistics_name ON {table|view} (column [,…n]) [with[[FULLSCAN|SAMPLE NUMBER{PERCENT|ROWS}] [NORECOMPUTE]]]
Update statistics
Statistics can be updated by using the UPDATE STATISTICS statement. You can update the information about the distribution of keys values for one or more statistics groups in the specified table or indexed view.
The syntax of the UPDATE STATISTICS statement is:
UPDATE STATISTICS table|view
[{{index|statistics_name}|({index|statistics_name}[,…n])}]
[WITH[[FULLSCAN]|SAMPLE number {PERCENT|ROWS}]|RESAMPLE]
[[[,]] [ALL|COLOUMNS|INDEX][[,]NORECOMPUTE]]
Q.2 Define Data Manipulation Language (DML). Explain DML statements in SQL Server with syntax.
Ans DML is used to manipulate the data in database objects
Some DML statements are:
INSERT
The smallest unit of data that you can add in a table is a row. You can add a row by using the INSERT statement. The syntax of the INSERT statement is:
INSERT [INTO]{table_name}[(column_list)]
VALUES {DEFAULT|values_list|select_statement}
UPDATE
You need to modify the data in the database when the specification of a customer, a client, a transaction or any other data maintained by the organization undergo a change. You can use the UPDATE DML statement to make the changes. The syntax of the UPDATE statement is:
UPDATE table_name
SET column_name = value [, column_name=value]
[FROM table_name]
[WHERE condition]
DELETE
You need to delete data from a database when it is no longer required. Th smallest unit that can be deleted from a database from database is a row.
You can delete a row from a table by using the DELETE DML statement. The syntax of the DELETE statement is :
DELETE[FROM] table_name [WHERE condition]
Q. 3 what are user defined functions? What kind of user defined functions can be created in SQL Server?
Ans similar to stored procedures, you can also create functions to store a set of T-SQL statements permanently. These functions are also referred to as User-Defined Functions (UDFs). A UDF is a database object that contain a asset of T-SQL statements, accept parameters, performs an action, and returns the result of that action as a value. The return value can be either single scalar value or a result set
UDFs are of different types, scalar functions and table-valued functions.
Scalar functions
Scalar functions accept a single parameter and return a single data value of thw type specified in the RETURNS clause. A scalar function can return any data type except text, ntext, image, cursor and timestamp.
A function contains a series of T-SQL statements defined in a BEGIN…END block of the function body that returns a single value.
Table-valued fuctions
|
|
No comments:
Post a Comment