RBMS
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 marks
Q.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
Q.4 what are join? Explain different type of join?
Ans
Cross join is also known as the Cartesian product.
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 marks
Q.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
Q.4 what are join? Explain different type of join?
Ans
Cross join is also known as the Cartesian product.
SQL LAB at Home 1
4. You have been hired by a regional hospital to create a database that will be used to store the information of the patients. Implementing the database will make the data easily accessible. The following ER diagram represents the relationship between the patient and the doctor.
The following list bescribes the entities,attributes,and relationship of the
preceding ER diagram:
* Entities-patient and Doctor
* Attributes of patient_Name,Patient_Id,and Date_admitted
* Attributes of Doctor-Doc_ID,Doc_Name, and Specialization
* Type of Relationship_ Many-to-many
5.Tom is working in an organization as a database administrator. Based on the hierarchy, the organization has multiple departments and each department has multiple employees. The following ER diagram represents the relationship between the departments and the employees.
The following listdescribes the entition,attributes,and relationship of the preceding ER diagram:
* Entities - Department and Employee
* Attributes of Department - DepID,DepName,and DepHead
* Attributes of Employee -EmpID,EmpName,and DepID
* Type of Relationship- Many-to-one
* Entities - Department and Employee
* Attributes of Department - DepID,DepName,and DepHead
* Attributes of Employee -EmpID,EmpName,and DepID
* Type of Relationship- Many-to-one
6. Lee Wong is the newly appointed database administrator at Standard bank. The management of this bank wants to computerize the process of banking. According to the bank's policy, one customer can have many accounts but one account cannot be shared by many customers. The following ER diagram represents the relationship between a customer and the accounts owned by a particular customer. In
The following list describes the entities, the attibues, and relationship of the
preceding ER diagram.
* Entities- Customer and Account
* Attributes of Customer- Customer Name, SSN Number,and Customer Address
* Attributes of Account- Account Number and Balance
* Type of Relationship - One-and-Many
7. Consider the following scenario: An author can write multiple books and a book can be written by more than one author. Identify the relationship between the author entity and the book entity and represent it with an ER Diagram.
8. Consider the following scenario: In a college, there are multiple departments. Each department has various faculty members. Only one of these faculty members holds the position of the Head of the Department. Identify the relationship between the faculty and the department and represent it with an ER Diagram.
Subscribe to:
Posts (Atom)