What is Replication? Replication is the process of distributing data from one database to another on the same server or servers connected through LAN or the internet................ What are the types of Replication? Snapshot Replication Define the terms used in Replication. Publisher is the database that transmits its data to another database. Describe the replication agents that SQL Server supports. The Snapshot Agent creates snapshot files and stores on the distribution database. It also keeps track of synchronization status in the distribution database. This is used in all kinds of replication.............. Describe in brief working of Replication. At first data and object is synchronized between publisher and subscribers. |
What does the COLLATE keyword in SQL signify?
What is COMPUTE and WITH TIES clause in SQL?
SQL - Posted on August 29, 2008 at 18:00 PM by Amit Satpute
What does the COLLATE keyword in SQL signify?
Collation is the order that SQL Server uses for sorting or comparing textual data. The temporary database is a system database that inherits it’s collation order from the master. If this temp table is compared to a real one, SQL Server will inform that it cannot resolve collation conflict on = operation.
To solve this, the tables should be created using the COLLATE keyword.
What is COMPUTE and WITH TIES clause in SQL?
The COMPUTE clause is placed at the end of a query to place the result of an aggregate function at the end of a listing.
e.g.: COMPUTE SUM (TOTAL)
The SELECT TOP N query always return exactly N records, and arbitrarily drops any record that have the same value as the last record in the group. The WITH TIES clause can do away with this. It returns the records that have the same value even if the number declared after the TOP has been exceeded.
Explain the SELECT DISTINCT statement. Write SQL syntax for the SELECT DISTINCT statement along with an example.
SELECT DISTINCT: It is used to select unique records from a table. Some tables may have duplicate values for a column. Distinct will eliminate these duplications.
Syntax:
SELECT DISTINCT column_name(s) FROM table_name
Example:
SELECT DISTINCT mobile_num FROM Employees
SQL GROUP BY
GROUP BY: It is used with aggregate functions to group the result-set by one or more columns................
SQL EXISTS
SQL EXISTS: If the subquery returns at least one row the EXISTS condition is considered "to be met"...............
SQL CONCATENATE
SQL CONCATENATE: This clause combine together (concatenate) the results from several different fields...............
SQL EXISTS
SQL EXISTS: If the subquery returns at least one row the EXISTS condition is considered "to be met"...............
SQL MINUS
SQL MINUS returns all rows in the first query that are not returned in the second query..................
Explain the SQL WHERE statement. Write SQL syntax for the SQL WHERE statement along with an example.
WHERE: It is used to fetch records that fulfill a specified condition.
Syntax:
SELECT column_name(s) FROM table_name WHERE column_name operator value
Example:
SELECT balance FROM Account WHERE balance > 1000
Explain the SQL ORDER BY statement. Write SQL syntax for the SQL ORDER BY statement along with an example.
ORDER BY: It is used to sort columns of a result set. It sorts columns in ascending order by default. DESC can be used to sort in a descending order.
Syntax:
SELECT column_name(s) FROM table_name ORDER BY column_name(s) ASC|DESC
Example:
SELECT balance FROM Account ORDER BY balance DESC
Explain the SQL WHERE statement. Write SQL syntax for the SQL WHERE statement along with an example.
WHERE: It is used to fetch records that fulfill a specified condition.................
SQL SELECT INTO
SQL SELECT INTO: The SELECT INTO statement selects data from one table and inserts it into a different table. Used often for creating back up’s............
SQL NOT NULL
SQL NOT NULL: The NOT NULL constraint enforces a column to NOT accept NULL values..............
SQL LIKE
LIKE clause is used for pattern matching. % is used to match any string of any length where as _ allows you to match on a single character................
SQL IN
The IN operator allows you to specify multiple values in a WHERE clause................
Explain the SQL GROUP BY statement. Write SQL syntax for the SQL GROUP BY statement along with an example.
GROUP BY: It is used with aggregate functions to group the result-set by one or more columns.
Syntax:
SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name
Example:
SELECT Emp_id,joiningdt,SUM(salary) FROM employee GROUP BY emp_id,joiningdt
SQL CONCATENATE
SQL CONCATENATE: This clause combine together (concatenate) the results from several different fields...............
SQL EXISTS
SQL EXISTS: If the subquery returns at least one row the EXISTS condition is considered "to be met"...............
SQL SELECT INTO
SQL SELECT INTO: The SELECT INTO statement selects data from one table and inserts it into a different table. Used often for creating back up’s............
Explain the SQL WHERE statement. Write SQL syntax for the SQL WHERE statement along with an example.
WHERE: It is used to fetch records that fulfill a specified condition.................
Explain the SQL UNION statement. Write SQL syntax for the SQL UNION statement along with an example.
SQL UNION: The UNION operator is used to combine the result-set of two or more SELECT statements Tables of both the select statement................
SQL - Dec 03, 2008 at 18:00 PM by Rajmeet Ghai
Explain the SQL HAVING statement. Write SQL syntax for the SQL HAVING statement along with an example.
HAVING clause is used to specify some condition along with aggregate functions.
Syntax:
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value
Example:
SELECT emp_id,SUM(salary) FROM employee
WHERE employee_name='tom’
GROUP BY emp_id
HAVING SUM(salary)>1500
Explain the SQL UNION ALL statement. Write SQL syntax for the SQL UNION ALL statement along with an example.
SQL UNION ALL: The UNION ALL operator is used to combine the result-set of two or more SELECT statements Tables of both the select statement ................
SELECT DISTINCT
SELECT DISTINCT: It is used to select unique records from a table. Some tables may have duplicate values for a column. Distinct will eliminate these duplications...........
SQL GROUP BY
GROUP BY: It is used with aggregate functions to group the result-set by one or more columns................
SQL CREATE VIEW
SQL CREATE VIEW: A view is a virtual table. A view contains rows and columns, just like a real table..............
SQL CONCATENATE
SQL CONCATENATE: This clause combine together (concatenate) the results from several different fields...............
SQL BETWEEN
SQL BETWEEN: The BETWEEN operator is used in a WHERE clause to select a range of data between two values. The values can be numbers, text, or dates..............
Explain the SQL TOP statement. Write SQL syntax for the SQL TOP statement along with an example.
TOP clause is used to specify the number of records to return. Usually used for large tables.
Syntax:
SELECT TOP number|percent column_name(s) FROM table_name
Example:
SELECT TOP 2 * from employee
Explain the SQL LIKE statement. Write SQL syntax for the SQL LIKE statement along with an example.
LIKE clause is used for pattern matching. % is used to match any string of any length where as _ allows you to match on a single character.
Syntax:
SELECT * FROM table_name WHERE column_name like 'pattern%';
Example:
SELECT * FROM employee WHERE emp_name like 'ma%';
SQL EXISTS
SQL EXISTS: If the subquery returns at least one row the EXISTS condition is considered "to be met"...............
SQL CONCATENATE
SQL CONCATENATE: This clause combine together (concatenate) the results from several different fields...............
SQL INTERSECT
SQL INTERSECT allows combining results of two or more select queries. If a record exists in one query and not in the other, it will be omitted from the INTERSECT results..............
SELECT DISTINCT
SELECT DISTINCT: It is used to select unique records from a table. Some tables may have duplicate values for a column. Distinct will eliminate these duplications...........
Explain the SQL UNION ALL statement. Write SQL syntax for the SQL UNION ALL statement along with an example.
SQL UNION ALL: The UNION ALL operator is used to combine the result-set of two or more SELECT statements Tables of both the select statement ................
Explain the SQL IN statement. Write SQL syntax for the SQL IN statement along with an example.
The IN operator allows you to specify multiple values in a WHERE clause.
Syntax:
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...)
Example:
SELECT * FROM employee
WHERE emp_LastName IN ('james','jones')
SQL BETWEEN
SQL BETWEEN: The BETWEEN operator is used in a WHERE clause to select a range of data between two values. The values can be numbers, text, or dates..............
SQL CREATE VIEW
SQL CREATE VIEW: A view is a virtual table. A view contains rows and columns, just like a real table..............
SQL LIKE
LIKE clause is used for pattern matching. % is used to match any string of any length where as _ allows you to match on a single character................
SQL NOT NULL
SQL NOT NULL: The NOT NULL constraint enforces a column to NOT accept NULL values..............
Explain the SQL UNION ALL statement. Write SQL syntax for the SQL UNION ALL statement along with an example.
SQL UNION ALL: The UNION ALL operator is used to combine the result-set of two or more SELECT statements Tables of both the select statement ................
Explain the SQL BETWEEN statement. Write SQL syntax for the SQL BETWEEN statement along with an example.
SQL BETWEEN: The BETWEEN operator is used in a WHERE clause to select a range of data between two values. The values can be numbers, text, or dates.
Syntax:
SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2
SQL CREATE VIEW
SQL CREATE VIEW: A view is a virtual table. A view contains rows and columns, just like a real table..............
SQL GROUP BY
GROUP BY: It is used with aggregate functions to group the result-set by one or more columns................
SQL IN
The IN operator allows you to specify multiple values in a WHERE clause................
SQL LIKE
LIKE clause is used for pattern matching. % is used to match any string of any length where as _ allows you to match on a single character................
SQL NOT NULL
SQL NOT NULL: The NOT NULL constraint enforces a column to NOT accept NULL values..............
Explain the SQL UNION statement. Write SQL syntax for the SQL UNION statement along with an example.
SQL UNION: The UNION operator is used to combine the result-set of two or more SELECT statements Tables of both the select statement must have the same number of columns with similar data types. It eliminates duplicates.
Syntax:
SELECT column_name(s) FROM table_name1 UNION SELECT column_name(s) FROM table_name2
Example:
SELECT emp_Name FROM Employees_india UNION SELECT emp_Name FROM Employees_USA
SQL Union - May 18, 2009 at 10:00 AM by Rajmeet Ghai
What is the difference between UNION and UNION ALL?
UNION command selects distinct and related information from two tables. On the other hand, UNION ALL selects all the values from both the tables.
Explain the SQL UNION ALL statement. Write SQL syntax for the SQL UNION ALL statement along with an example.
SQL UNION ALL: The UNION ALL operator is used to combine the result-set of two or more SELECT statements Tables of both the select statement must have the same number of columns with similar data types. It lists ALL records.
Syntax:
SELECT column_name(s) FROM table_name1 UNION ALL SELECT column_name(s) FROM table_name2
Example:
SELECT emp_Name FROM Employees_india UNION ALL SELECT emp_Name FROM Employees_USA
SQL Union - May 18, 2009 at 10:00 AM by Rajmeet Ghai
What is the difference between UNION and UNION ALL?
UNION command selects distinct and related information from two tables. On the other hand, UNION ALL selects all the values from both the tables.
Explain the SQL SELECT INTO statement. Write SQL syntax for the SQL SELECT INTO statement along with an example.
SQL SELECT INTO: The SELECT INTO statement selects data from one table and inserts it into a different table. Used often for creating back up’s.
Syntax:
SELECT * INTO new_table_name [IN externaldatabase] FROM old_tablename
Example:
SELECT * INTO employee_Backup FROM employee
SQL MINUS
SQL MINUS returns all rows in the first query that are not returned in the second query..................
SQL HAVING
HAVING clause is used to specify some condition along with aggregate functions............
SQL CONCATENATE
SQL CONCATENATE: This clause combine together (concatenate) the results from several different fields...............
SQL INTERSECT
SQL INTERSECT allows combining results of two or more select queries. If a record exists in one query and not in the other, it will be omitted from the INTERSECT results..............
SQL GROUP BY
GROUP BY: It is used with aggregate functions to group the result-set by one or more columns................
Explain the SQL NOT NULL statement. Write SQL syntax for the SQL NOT NULL statement along with an example.
SQL NOT NULL: The NOT NULL constraint enforces a column to NOT accept NULL values. This means a column with not null always needs to have a value. This means that you cannot insert a new record, or update a record without adding a value to this field
Example:
CREATE TABLE employee
(
Emp_id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
SQL BETWEEN
SQL BETWEEN: The BETWEEN operator is used in a WHERE clause to select a range of data between two values. The values can be numbers, text, or dates..............
SQL CREATE VIEW
SQL CREATE VIEW: A view is a virtual table. A view contains rows and columns, just like a real table..............
SQL HAVING
HAVING clause is used to specify some condition along with aggregate functions............
SQL MINUS
SQL MINUS returns all rows in the first query that are not returned in the second query..................
Explain the SQL UNION ALL statement. Write SQL syntax for the SQL UNION ALL statement along with an example.
SQL UNION ALL: The UNION ALL operator is used to combine the result-set of two or more SELECT statements Tables of both the select statement ................
Explain the SQL CREATE VIEW statement. Write SQL syntax for the SQL CREATE VIEW statement along with an example.
SQL CREATE VIEW: A view is a virtual table. A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.
Syntax:
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
Example:
CREATE VIEW [sample] AS
SELECT employeeID,employeeName
FROM employee
WHERE salary > 10000
Explain the SQL UNION ALL statement. Write SQL syntax for the SQL UNION ALL statement along with an example.
SQL UNION ALL: The UNION ALL operator is used to combine the result-set of two or more SELECT statements Tables of both the select statement ................
SQL Server method to insert data
Brief about Insert Statement with an example.
Brief about Select….into statement with an example.
Brief about Bulk copy with an example.
Describe how bcp command prompt utility is used to import and export data.
Describe how bulk insert statement is used to import data..........
SQL ORDER BY
ORDER BY: It is used to sort columns of a result set. It sorts columns in ascending order by default. DESC can be used to sort in a descending order..............
SQL MINUS
SQL MINUS returns all rows in the first query that are not returned in the second query..................
SQL INTERSECT
SQL INTERSECT allows combining results of two or more select queries. If a record exists in one query and not in the other, it will be omitted from the INTERSECT results..............
Explain the SQL CONCATENATE statement. Write SQL syntax for the SQL CONCATENATE statement along with an example.
SQL CONCATENATE: This clause combine together (concatenate) the results from several different fields.
Syntax:
CONCAT(str1, str2, str3, ...):
Example:
SELECT CONCAT(first_name, last_name) FROM employee WHERE salary > 1000
SQL CREATE VIEW
SQL CREATE VIEW: A view is a virtual table. A view contains rows and columns, just like a real table..............
SQL HAVING
HAVING clause is used to specify some condition along with aggregate functions............
SQL LIKE
LIKE clause is used for pattern matching. % is used to match any string of any length where as _ allows you to match on a single character................
SQL ORDER BY
ORDER BY: It is used to sort columns of a result set. It sorts columns in ascending order by default. DESC can be used to sort in a descending order..............
Explain the SQL UNION statement. Write SQL syntax for the SQL UNION statement along with an example.
SQL UNION: The UNION operator is used to combine the result-set of two or more SELECT statements Tables of both the select statement................
Explain the SQL SUBSTRING statement. Write SQL syntax for the SQL SUBSTRING statement along with an example.
SQL SUBSTRING: The Substring function in SQL is used to capture a portion of the stored data
Syntax:
SUBSTR(str,pos,len): Starting with the
Example:
SELECT SUBSTR(company_name, 3) FROM Company WHERE company_name = ‘tata’;
Explain the SQL INTERSECT statement. Write SQL syntax for the SQL INTERSECT statement along with an example.
SQL INTERSECT allows combining results of two or more select queries. If a record exists in one query and not in the other, it will be omitted from the INTERSECT results.
Syntax:
Select field1, field2, . field_n from tables INTERSECT select field1, field2, . field_n from tables;
Example:
Select salary from employee INTERSECT select salary from manager;
Explain the SQL WHERE statement. Write SQL syntax for the SQL WHERE statement along with an example.
WHERE: It is used to fetch records that fulfill a specified condition.................
Explain the SQL TOP statement. Write SQL syntax for the SQL TOP statement along with an example.
TOP clause is used to specify the number of records to return. Usually used for large tables.................
SQL Server summarizing data
We have CUBE or ROLLUP operators to generate summary reports. Both are part of the GROUP BY clause of the SELECT statement.............
SQL MINUS
SQL MINUS returns all rows in the first query that are not returned in the second query..................
SQL LIKE
LIKE clause is used for pattern matching. % is used to match any string of any length where as _ allows you to match on a single character................
SQL INTERSECT
SQL INTERSECT allows combining results of two or more select queries. If a record exists in one query and not in the other, it will be omitted from the INTERSECT results..............
Explain the SQL MINUS statement. Write SQL syntax for the SQL MINUS statement along with an example.
SQL MINUS returns all rows in the first query that are not returned in the second query. Each statement must have the same number of fields in the result sets with similar data types.
Syntax:
Select field1, field2, . field_n from tables MINUS select field1, field2, . field_n from tables;
Example:
Select salary from employee MINUS select salary from manager
Explain the SQL UNION statement. Write SQL syntax for the SQL UNION statement along with an example.
SQL UNION: The UNION operator is used to combine the result-set of two or more SELECT statements Tables of both the select statement................
SQL NOT NULL
SQL NOT NULL: The NOT NULL constraint enforces a column to NOT accept NULL values..............
SQL INTERSECT
SQL INTERSECT allows combining results of two or more select queries. If a record exists in one query and not in the other, it will be omitted from the INTERSECT results..............
SQL GROUP BY
GROUP BY: It is used with aggregate functions to group the result-set by one or more columns................
SQL CREATE VIEW
SQL CREATE VIEW: A view is a virtual table. A view contains rows and columns, just like a real table..............
Explain the SQL EXISTS statement. Write SQL syntax for the SQL EXISTS statement along with an example.
SQL EXISTS: If the subquery returns at least one row the EXISTS condition is considered "to be met".
Syntax:
SELECT columns FROM tables WHERE EXISTS ( subquery );
Example:
SELECT * FROM employee WHERE EXISTS (select * from appraisal where employee.employee_id = appraisal.employee_id);
SQL BETWEEN
SQL BETWEEN: The BETWEEN operator is used in a WHERE clause to select a range of data between two values. The values can be numbers, text, or dates..............
SQL CREATE VIEW
SQL CREATE VIEW: A view is a virtual table. A view contains rows and columns, just like a real table..............
SQL IN
The IN operator allows you to specify multiple values in a WHERE clause................
SQL MINUS
SQL MINUS returns all rows in the first query that are not returned in the second query..................
SELECT DISTINCT
SELECT DISTINCT: It is used to select unique records from a table. Some tables may have duplicate values for a column. Distinct will eliminate these duplications...........
The answers to following questions will be made available soon. Keep visiting.
Explain the SQL CASE statement. Write SQL syntax for the SQL CASE statement along with an example.
GROUP BY: It is used with aggregate functions to group the result-set by one or more columns................
LIKE clause is used for pattern matching. % is used to match any string of any length where as _ allows you to match on a single character................
SQL NOT NULL: The NOT NULL constraint enforces a column to NOT accept NULL values..............
SQL SELECT INTO: The SELECT INTO statement selects data from one table and inserts it into a different table. Used often for creating back up’s............
What is RAID and what are different types of RAID configurations?
RAID stands for Redundant Array of Independent Disks. RAID defines data storage schemes to divide and replicate data................
Read answer
What are Page Splits?
When there is not enough room on a page for a new row, a Server splits the page..............
Read answer
Describe in brief database architecture
Database architecture describes the design of the database. It explains how the data is stored. The data of the server is stored in databases....................
Read answer
Explain logical database components
The logical components are usually used to connect to the database. Any object that a user can use to access or connect................
Read answer
What are the database objects? Explain them in brief
Database objects such as tables, primary key, and foreign key describe the structure of the content of a database. These objects also represent the properties of a server...............
Read answer
Illustrate physical database architecture in brief
The physical database architecture describes how the database and files are organized in a SQL server..............
Read answer
What are pages and Extents?
A page is a unit of data storage in SQL. The size of a page is 8Kb. A page has a header and a body. Different types of pages are: ..............
Read answer
What are constraints in SQL Server?
|
What are Global and Local cursors?
SQL Server cursors - August 29, 2008 at 18:00 PM by Amit Satpute
What are Global and Local cursors?
Local cursors are available only in the batch, stored procedure, or trigger in which they were created. They are implicitly deallocated when the batch, stored procedure, or trigger terminates.
Global cursors are global to the connection.They are implicitly deallocated at disconnect.
Explain the storage models of OLAP.
MOLAP Multidimensional Online Analytical processing - In MOLAP data is stored in form of multidimensional cubes and not in relational databases....................
Differentiate between Data Mining and Data warehousing.
Data warehousing is merely extracting data from different sources, cleaning the data and storing it in the warehouse. Where as data mining aims to examine or explore the data using queries...................
What is Data purging?
The process of cleaning junk data is termed as data purging. Purging data would mean getting rid of unnecessary NULL values of columns.................
What are CUBES?
A data cube stores data in a summarized version which helps in a faster analysis of data. The data is stored in such a way that it allows reporting easily..................
What are OLAP and OLTP?
OLTP: Online Transaction and Processing helps and manages applications based on transactions involving high volume of data..................
What are the different problems that “Data mining” can solve?
Data mining helps analysts in making faster business decisions which increases revenue with lower costs..............
What are different stages of “Data mining”?
Exploration: This stage involves preparation and collection of data. it also involves data cleaning, transformation. Based on size of data, different tools to analyze the data may be required. This stage helps to determine different variables of the data to determine their behavior...................
What is Discrete and Continuous data in Data mining world?
Discreet data can be considered as defined or finite data. E.g. Mobile numbers, gender. Continuous data can be considered as...............
What is MODEL in Data mining world?
Models in Data mining help the different algorithms in decision making or pattern matching. The second stage of data mining....................
How does the data mining and data warehousing work together?
Data warehousing can be used for analyzing the business needs by storing data in a meaningful form. Using Data mining,...............
What is a Decision Tree Algorithm?
A decision tree is a tree in which every node is either a leaf node or a decision node. This tree takes an input an object and outputs some decision................
What is Naïve Bayes Algorithm?
Naïve Bayes Algorithm is used to generate mining models. These models help to identify relationships between input columns and the predictable columns................
Explain clustering algorithm.
Clustering algorithm is used to group sets of data with similar characteristics also called as clusters......................
What is Time Series algorithm in data mining?
Time series algorithm can be used to predict continuous values of data. Once the algorithm is skilled to predict a series of data, it can predict the outcome of other series....................
Explain Association algorithm in Data mining
Association algorithm is used for recommendation engine that is based on a market based analysis. This engine suggests products to customers based on what they bought earlier. The model is built on a dataset containing identifiers......................
What is Sequence clustering algorithm?
Sequence clustering algorithm collects similar or related paths, sequences of data containing events.......................
Explain the concepts and capabilities of data mining.
Data mining is used to examine or explore the data using queries. These queries can be fired on the data warehouse. Explore the data in data mining helps in reporting,....................
Explain how to work with the data mining algorithms included in SQL Server data mining.
SQL Server data mining offers Data Mining Add-ins for office 2007 that allows discovering the patterns and relationships of the data.................
Explain how to use DMX-the data mining query language.
Data mining extension is based on the syntax of SQL. It is based on relational concepts and mainly used to create and manage the data mining models.............................
Explain how to mine an OLAP cube.
A data mining extension can be used to slice the data the source cube in the order as discovered by data mining........................
What are the different ways of moving data/databases between servers and databases in SQL Server?
There are several ways of doing this. One can use any of the following options:...................
Data Transformation Services is a set of tools available in SQL server that helps to extract, transform and consolidate data. This data can be from different sources into a single or multiple destinations.................... What is built-in function? Explain its type i.e. Rowset, Aggregate and scalar. A built in function of sql is used for performing calculations. These are standard functions provided by sql. Aggregate functions: these functions perform calculation on a column and return a single value. Example: AVG(), SUM(), MIN(), MAX().........................
a. Can be used in a number of places without restrictions as compared to stored procedures. b. Code can be made less complex and easier to write....................
|
| |
|
Explain the various types of concurrency problem. I.e. Lost or buried updates, uncommitted dependency, inconsistent analysis, phantom read.
Types of concurrency problem:- Lost or buried updates: - When the same row is selected for updates by two or more transactions and updates the row based on the value originally selected. Here, each transaction is unaware of the other transactions. The last update overwrites updates made by the other transactions, which results in lost data......................
Describe optimistic and pessimistic concurrency.
Optimistic concurrency: - Assumes that a resource is likely to be available at all times. This means that resource locking is very unlikely. If a conflict occurs, the application must read the data and attempt the change again...................
What are the differences between lost updates and uncommitted dependencies?
In lost update, the data is lost. Here, the last update overwrites updates made by other transactions..................
Explain the isolation level does SQL Server support.
Isolation levels:- READ UNCOMMITTED: - Reads data that has been modified but not committed yet. READ UNCOMMITTED: - Cannot Read data that has been modified but not committed yet ................
What guidelines should be followed to help minimize deadlocks?
Guidelines to minimize deadlocks:- Avoid user interaction in the transactions. The transaction must not rely on any inputs from the user.................
Describe in brief SQL Server locking.
SQL server has a locking mechanism which locks the resources to be used by transactions..............
What are the different types of lock modes in SQL Server 2000?
Different lock modes: Shared (S): Mostly used for Read only operations like SELECT statements. It allows concurrent transactions to read data. No other transaction can modify the data until the lock is present. The lock is released as soon as the read is over................
What is lock escalation? What is its purpose?
Lock escalation is when the system combines multiple locks into a higher level one. This is done to recover resources taken by the other finer granular locks...................
What is a live lock?
A live lock is similar to a deadlock that the states of the processes involved in the live lock constantly change with regard to one another, none progressing......................
What is SAND BOX in SQL server?
Sandbox is a safe place to run semi-trusted programs or scripts..................
Overview of SQL Server Integration Services (SSIS)
SQL Server Integration Services is used for building applications with data integration and workflow. SSIS is most commonly used in ETL jobs of data warehousing................
How can we drop an assembly from SQL SERVER?
An assembly can be dropped using DROP ASSEMBLY command. DROP ASSEMBLY assembly_name.................
Why do we need to drop assembly for updating changes?
Assemblies are stored in the database. Because of this, when the assembly is modified and recompiled, it must be first DROPPED...............
How to see assemblies loaded in SQL SERVER?
Sys.assemblies can be used to see the assemblies loaded in SQL SERVER. Example: Select * from sys.assemblies......................
How is .NET Appdomain allocated in SQL SERVER 2005?
A separate Appdomian is created for each database in order to run the database code. The appdomain may be allocated based on identity of the user who owns the assembly..................
What is the syntax for creating a new assembly in SQL Server 2005?
Assemblies can be created using CREATE ASSEMBLY command. It takes the path of the DLL as the parameter.................
CLR Integration vs. Extended Stored Procedures
CLR Integration provides a more convenient and robust solution to Extended stored procedures for implementing server side logic....................
How to configure CLR for SQL Server?
CLR by default is not configured in SQL server. It needs to be explicit ally configured using sp_configure syntax. This is shown as below:.....................
How to create functions in SQL Server using .NET?
Functions in SQL server can be created using the .NET common language interface or CLR. The functions code is written and then complied into a .NET assembly...................
What are the steps you will take, if you are tasked with securing an SQL Server?
What is a deadlock and what is a live lock? How will you go about resolving deadlocks?
Explain 'Hostprotectionattribute' in SQL server 2005.
|
What are the advantages and disadvantages of merge replication?
Advantages
- It provides built-in and custom conflict resolution capabilities.
- It allows for the synchronization of data from multiple tables at one time.
- It provides rich data replication options like selection of article types and filtering and identity range management.
Disadvantages
- Merge replication requires more configuration and maintenance at the server
SQL Server replication - May 19, 2009 at 18:00 AM by Rajmeet Ghai
What is Replication and Database Mirroring?
Database Mirroring allows two copies of a database on different computers. Clients can get access to only one database called as”principal” database. Any changes applied to principal database, are applied on the copy also called as the mirror. This is achieved by applying the transaction log for every insert, update, or deletion made on the primary database. This increases the availability of a database incase of a failure.
Explain the architecture of SQL Reporting service.
Reporting Services runs as a middle-tier server, as part of your existing server architecture.
SQL Server 2000 should be installed for the database server, and Internet Information Services 6.0 as a Web server.
The report server engine takes in report definitions, locates the corresponding data, and produces the reports.
Interaction with the engine can be done through the Web-based Report Manager, which also lets you manage refresh schedules and notifications.
End users view the report in a Web browser, and can export it to PDF, XML, or Excel.
SQL Server reporting service - March 10, 2009 at 19:00 PM by Rajmeet Ghai
What is Reporting Services?
SQL Server’s Reporting services offer a variety of interactive and printed reports managed by a web interface. Reporting services is a server based environment.
How does the report manager work in SSRS?
Report manager is a web application. In SSRS it is accessed by a URL. The interface of this Report manager depends on the permissions of the user. This means to access any functionality or perform any task, the user must be assigned a role. A user with a role of full permissions can entire all the features and menus of the report. To configure the report manager, a URL needs to be defined.
What are the Reporting Services components?
Reporting services components assist in development. These processing components include some tools that are used to create, manage and view reports. A report designer is used to create the reports. a report sever is used to execute and distribute reports. a report manager is used to manage the report server.
SQL Server Reporting Services vs Crystal Reports.
Crystal reports are processed by IIS while SSSR have a report server. Caching in Crystal reports is available through cache server. On the other hand, caching in SSSR is available for Report history snapshots. Crystal reports have standards and user defined field labels. SSSR allows only user defined field labels.
Explain the components of SQL server service broker.
Below are the Service Broker infrastructure components:
Endpoint
Message Type
Contract
Route
Queue
Service
Remote Binding Service
SQL Server service broker - March 10, 2009 at 19:00 PM by Rajmeet Ghai
What do we need Queues?
A queue holds the incoming message before they are processed for a service.
One queue per service is created.
A queue can be created using:
CREATE QUEUE [QueueName]
What is “Asynchronous” communication?
In Asynchronous communication, messages can be queued until the service which was down is up and running. Asynchronous communication is loosely coupled. This means that there can be another component waiting to acknowledge the message. In this communication, the sender need not wait for a response but can continue to work.
What is SQL Server Service broker?
SQL service broker provides a mechanism to queue provide reliable messaging for SQL server. Using SQL server, the service broker assists in passing messages between applications. It also rejects messages with invalid format and performs retries. Service broker helps to make scalable and secure database applications.
What are the essential components of SQL Server Service broker?
The components in SQL server Service broker are represented by Server objects used in the messaging. Queue is an object that holds the messages for processing. Dialogues exchanged between two endpoints are grouped by conversation groups. There are some components used at design time called as service definition components used to specify the basic design of the application. Routing and security components are used to define the communications.
What is the main purpose of having Conversation Group?
Any application using service broker communicates by exchanging conversations that is nothing but asynchronous and reliable messages. Conversation group is a group of conversations used to accomplish the same task. These groups help in concurrency. They can also be used to manage the states.
How to implement Service Broker?
Using Transact SQL statements service broker can be used. An application can be implemented as a program running outside of SQL Server or as a stored procedure. Service broker uses a number of components to perform a task. A program that starts a conversation creates and sends a message to a service that may or may not respond. Any application that intends to use Service broker does not need any special object model or library. Transact-SQL commands to SQL Server can be sent for processing.
How do we encrypt data between Dialogs?
Using Dialog Security the dialogues can be encrypted at the initiator and then decrypted at the target. Dialogue security can be implemented by implemented as full dialogue security or anonymous dialog security. In full dialogue, both the database that initiates the service and database that calls the service to be implemented needs to have two users. Each service in the conversation is bound by a remotes service. A certificate is associated with each user, so that messages can be encrypted using the recipient's public key and decrypted using the corresponding private key. If anonymous dialog security is used, target service need not explicitly permit access to a remote user. A guest account can be used to generate a session key and encrypt the message.
SQL Server service broker - May 18, 2009 at 10:00 AM by Rajmeet Ghai
What is Service Broker?
Catalog views serve as a repository to store static metadara like server side and database specific objects. This includes logins, tables, stored procedures etc. It is always better to use catalogue views to view system data rather than using system tables.
Define stored procedure.
Answer
Stored procedure is a set of SQL commands that have been complied and stored on the database sever. They can be used in the code as and when required since hey stored. They need not be complied over and over again. They can be invoked by CALL procedure (..) or EXECUTE procedure(..)
What are the purposes and advantages stored procedure?
Answer
Purposes and advantages of stored procedures:
- Manage, control and validate data
- It can also be used for access mechanisms
- Large queries can be avoided
- Reduces network traffic since they need not be recompiled
- Even though the stored procedure itself may be a complex piece of code, we need not write it over and over again. Hence stored procedures increases reusability of code
- Permissions can be granted for stored procedures. Hence, increases security.
Determine when to use stored procedure to complete SQL Server tasks.
Answer
- If a large piece of code needs to be performed repeatedly, stored procedures are ideal
- When hundreds of lines of SQL code need to be sent; it is better to use stored procedure through a single statement that executes the code in a procedure, rather than by sending hundreds of lines of code over the network.
- When security is required.
Define Table. SQL Server database stores information in a two dimensional objects of rows and columns called table. Define Local temporary table and global temporary table. Local temporary table is created by prefixing name with pound sign like (#table_name). Global temporary table is created by prefixing name with Double pound sign like (##table_name). Local temporary table is dropped when the stored procedure completes. Global temporary tables are dropped when session that created the table ends. |
SQL Server table - August 29, 2008 at 18:00 PM by Amit Satpute What is Cascade and restrict in Drop table SQL? RESTRICT indicates that the table should not be dropped if any dependencies exist. If dependencies are found, an error is returned and the table isn't dropped. CASCADE specifies that the dependencies be removed before the drop is performed SQL Server table - Nov 18, 2008 at 18:00 PM by Rajmeet Ghai Define SQL Server Tables. Explain Create Table syntax with an example. Answer Syntax: Example: Explain how to determine column nullability of a table. Answer a. source table column is nullable: If any of the source columns are nullable, the result column is nullable b. Source table column is not nullable; the column in the new table is defined as not null: If all the source columns in the expression are not nullable, the result column is not nullable. Explain how to reference a table from another database in the same server. Answer Explain how to rename a table. Answer Explain how to change the owner of a table. Answer Explain autonumbering and identifier column of a table. Answer Explain how to determine if a table has a primary key? Answer What is globally unique identifier? Answer How can we determine if a column is an identity column? Answer How can we determine if a column is a primary column? Answer What are the purposes of table variables? Answer b. Table variables can be used in stored procedures, user defined functions. c. Table variables give a better performance d. Table variables can be used in a SELECT, INSERT, UPDATE statements. e. Unlike temporary variables, they don’t require a declaration or cleaning up What are some of the drawbacks of table variables? Answer b. Table definition of a table variable cannot be changed after a DECLARE statement. Hence, NO ALTER statement will work. c. Table variables cannot be used In ROLLBACK of transactions |
Explain the characteristics of a transaction. i.e. Atomicity, Consistency, Isolation, Durability.
Characteristics of a transaction:-
Atomicity
This characteristic of a transaction means that a transaction is performed completely not performed at all. I.e. all the tasks in a transaction are completed or none are completed. E.g. transferring money from one account to another involves credit from one account and debit to another.
Consistency
This characteristic means that the database should be consistent before and after the transaction. For a successful transaction database can move from one state to another. Both the states should abide by the same rules. For an unsuccessful transaction, if the transaction fails to abide by the rule and leads to an inconsistent state, the transaction should be rolled back.
Isolation
A transaction should be isolated. This means that that no other operation should be allowed to access or see the intermediate state data.
Durability
A transaction on completion must persist. It should withstand system failures and should not be undone.
Explain various types of SQL Server transactions. I.e. Explicit, Autocommit and Implicit Transactions.
Explicit transaction
A transaction which has START and END defined explicitly. The explicit transaction mode lasts until the transaction is over. When the transaction is over, the transaction is reverted back to the mode in which it started. This mode could either be implicit or autocommit.
Autocommit transactions
This is the default mode of transaction. In this mode, if the transaction is completed successfully, it is committed. If the transaction faces any error, it is rolled back.
Implicit transactions
In this mode, a new transaction is started when the current is committed or rolled back. Nothing is done to begin a transaction. One only needs to commit or roll back a transaction. Implicit transactions have a chain of continuous transactions.
What is distributed transactions in SQL Server? When are they used?
Distributed transactions involve two or more databases within a SQL server. The management of such transactions is done by a component called as transaction manager. Distributed transactions must be used when real time updates are required simultaneously on multiple databases.
SQL Server transactions - Dec 1, 2008 at 18:10 pm by Rajmeet Ghai
Describe the characteristics of transactions i.e. atomicity, consistency, isolation.
Atomicity: This property of a transaction ensures that a transaction either completely or does not happen at all. E.g. transferring money from one account to another.
Consistency: This property ensures the data is consistent before the transaction and left in a consistent state after the transaction. If the transaction violates the rules, it must be rolled back.
Isolation: This property means that the transaction should be isolated. I.e. until the transaction is over other data or operations cannot access the transaction. This is to maintain the performance.
Explain the types of transaction i.e. explicit, autocommit and implicit
Explicit transaction: transactions that have a START and END explicitly written are called as an explicit transaction. They last only for the duration of the transaction. When the transaction ends, the connection returns to the transaction mode it was in before the explicit transaction was started
Auto commit: This is the default management mode. Every SQL statement is either committed or rolled back when complete. If it completes successfully it is committed else it is rolled back. Auto commit is default mode.
Implicit: when the transaction is in implicit mode, a new transaction starts automatically after the current transaction is committed or rolled back. Nothing needs to be done to define the start of the transaction. It generates continues chain of transactions.
What is Distributed transaction?
A distributed transaction is one in which it updates data present on two or more systems. They are useful in updating data that is distributed. They must be robust because they are subjected to failures very often. Failures like client server failure etc. interactions between these computers that are distributed is with the help of transaction managers.
What is nested transaction? Explain with an example.
A nested transaction is one in which a new transaction is started by an instruction that is already inside another transaction. This new transaction is said to be nested. The isolation property of transaction is obeyed here because the changes made by the nested transaction are not seen or interrupted by the host transaction.
BEGIN TRANSACTION trans_1
INSERT INTO TestTrans1 VALUES (1,’mark’)
COMMIT TRANSACTION trans_1;
GO
/* Start a transaction and execute trans_1. */
BEGIN TRANSACTION trans_2;
GO
EXEC trans_1 1, 'aaa'; //execute some procedure
GO
ROLLBACK TRANSACTION trans_2;
GO
EXECUTE TransProc 3,'bbb';
GO
What operations do SQL Server transaction logs support?
Operations supported by transaction Logs:-
Recovery of individual transactions
In case an operation fails, a transaction can be rolled back
Recovery of all Incomplete Transactions When SQL Server Is started
In case the SQL server fails, all transactions that were in a state of execution can be recovered. This operation helps to maintain integrity of the database.
Rolling a Restored Database, File, File group, or Page Forward to the Point of Failure
In case of a hardware or disk failure, if any database file is affected; it can be restored to the point after which the failure occurred.
Supporting Transactional Replication
Transactions can be replicated from the transaction log into the distribution database
Supporting Standby-Server Solutions
A primary server sends the active transaction log of the primary database to one or more destinations. This destination is a standby server.
Explain the purpose of check points in a transaction log.
Checkpoints are used for backup of transactions. When a checkpoint is introduced in a state and the next state is achieved; if something goes wrong in the new state, the transaction can be returned to the checkpoint. This means that checkpoints allow the transactions to be rolled back.
What is write-ahead transaction log?
A write-ahead transaction log on the disk where modifications are written first before the associated log record is written to disk. This is to maintain the ACID properties. Data modifications are not made to the disk directly. They are made to the buffer cache first. Because log records are always written ahead of the associated data pages, the log is called a write-ahead log.
Define Triggers. A trigger is a special type of event driven stored procedure. It gets initiated when Insert, Delete or Update event occurs. It can be used to maintain referential integrity. A trigger can call stored procedure. |
| |
Define Views.
- View can be created to retrieve data from one or more tables.
- Query used to create view can include other views of the database.
- We can also access remote data using distributed query in a view.
Views - October 24, 2008 at 18:10 pm by Rajmeet Ghai
What is Indexed view? How to create it?
In an indexed view, the data is already computed and stored. Data can be accessed by a unique index. This index is a clustered index. In order to create an index the syntax is
CREATE [UNIQUE], [CLUSTERED | NONCLUSTERED] INDEX index_name
ON {view}
[WITH
[ON filegrp]
What are partitioned views and distributed partitioned views?
Partitioned views allow data in a large table to be split into smaller tables. These small tables are called as member tables. The split is done based on range of data values in one of the columns.
In a distributed portioned view, each member table is on a separate member server. This means that the member tables are distributed. To locate these tables easily, the database name on each server should be same.
What functions can a view be used to performed?
Functions of View:-
- Subset data of a table
- Can join multiple tables values into one
- They can act as aggregated tables. i.e. a view can be used to store Sum, average of values
- Views can be nested and can be used for abstraction
Define views.
Answer
A view can be considered as a virtual table. It does not physically exist. It is based on the result set of a SQL statement. A view contains rows and tables just like a real table.
Describe the functionalities that views support.
Answer
- Views can subset data in a table
- They can join multiple tables into one virtual table
- Views can provide security and decrease complexity
- They save space because only their definition is stored.
- They can also be used to create abstraction
- Materialized views are commonly used in data warehousing. They represent a snapshot of the data from remote sources.
Views can create other calculated fields based on values in the real underlying tables
Explain Indexed views and partitioned view with their syntax.
Answer
Indexed view:
An index view has a unique clustered index created on it. They exist as rows on the disk. Because they are saved on the disk, the response time to a query is fast at the cost of space consumption. They are more commonly used in scenarios when data modification is less.
Syntax:
Create Index CREATE [UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name ON table_name
The view is created using the CREATE VIEW synatx
Partitioned view:
Partitioned view joins the horizontally portioned data. This data may belong to one ore more servers. It makes the data appear as one table. A portioned view can either be local or distributed. A local portioned view resides on the same instance of the SQL server while the distributed may reside on a different server.
Syntax:
The view is then created by UNIONing all the tables and an updateable partitioned View results
Server 1 :
CREATE TABLE Customer1 (CustomerID INTEGER PRIMARY KEY CHECK (CustomerID BETWEEN 1 AND 32999), ... -- Additional column definitions)
Similar tables created for Server 2 and 3
Partitioned view for server 1
CREATE VIEW Customers AS
SELECT * FROM CompanyDatabase.TableOwner.Customer1
UNION ALL
SELECT * FROM Server2.CompanyDatabase.TableOwner.Customer2
UNION ALL
SELECT * FROM Server3.CompanyDatabase.TableOwner.Customer3
What are the restrictions that views have to follow?
Answer
- 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.
What is Xquery?
Xquery is used to query XML data. This may be either a database or a file. It used xpath expressions that can be used to extract elements and attributes from XML documents.
What are XML indexes?
XML indexes are created on XML data type columns. Having XML indexes will reduce cost with respect to maintenance and data modification. Use of XML indexes avoids the need to parse the complete data at runtime thereby increasing query processing efficiency.
What are secondary XML indexes?
XML indexes can either be primary or secondary. When the XML type column has its first index, it is called as a primary index. Using this primary index, secondary indexes like PATH, VALUE and PROPERTY are supported. Depending on the nature of queries, they help in query optimization.
Path secondary index if used speeds up queries if the query specifies a path.
Path secondary index if used speeds up queries are value based.
Path secondary index if used speeds up queries if the query retrieves one or more values from individual XML instances.
What is FOR XML in SQL Server?
FOR XML allows data to be retrieved in an XML format. The FOR XML clause needs to be mentioned at the end of SELECT statement. There are three modes of FOR XML:-
RAW mode: - An XML element is formed for each row in the query results.
AUTO mode: - The query results are returned as nested XML elements.
Explicit mode: - the format of the XML document returned by the query can be completely controlled by the query.
What is the OPENXML statement in SQL Server?
OPENXML is a row set provider, in which a row set view over an XML documented is provided. It is used to parse the complex XML function.
Syntax:
OPENXML(idoc int [in],rowpattern nvarchar[in],[flags byte[in]]) [WITH (SchemaDeclaration | TableName)]
Idoc is the document handle of an XML document which is created by calling sp_xml_preparedocument
Xpath is the pattern used to identify the nodes.
Flag is for mapping between the XML data and the relational rowset
WITH clause is used to provide a rowset format.
How to call stored procedure using HTTP SOAP?
Stored procedures can be called using HTTP SOAP by creating endpoints. A SOAP endpoint is identified by a URL. Each endpoint supports a protocol, either HTTP OR TCP.
Syntax for Endpoint:
CREATE ENDPOINT name_of_end_point
STATE = STARTED
AS HTTP
(
PATH = specifies path of the service end point.
AUTHENTICATION = (INTEGRATED),
PORTS = (CLEAR),
SITE = name of the host computer
)
FOR SOAP
(
WEBMETHOD – name of the web method to be used.
BATCHES = DISABLED,
WSDL = DEFAULT,
DATABASE = name of the database
NAMESPACE = specifies a name space for end point.
)
What is XMLA?
XML for Analysis facilitates communication of client applications with OLAP data sources. Communication is done via XML, HTTP, or SOAP. XMLA open standards support data access to data sources on the WWW.
Define Data definition language.
Answer
A Data Definition language is a computer language. 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.
Explain the command Create Table, Alter Table and Drop Table with syntax.
Answer
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));
Define Data manipulation language.
Answer
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.
Explain the command Select, Insert, Update and Delete with syntax.
Answer
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
Example:
Select * from customer;
b. Insert: Insert statement is used to insert data (row) in a table.
Syntax:
Insert into table_name
Values (value1, 2 ..)
Example:
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’
Example:
Update customer Set first_name=’john’ Where last_name=’james’;
Define Data control language.
Explain the command Grant, Revoke and Deny with syntax.
SQL Server Server Data control - Nov 18, 2008 at 15:30 pm by Rajmeet Ghai
Define Data control language.
Answer
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
Explain the command Grant, Revoke and Deny with syntax.
Answer
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
Example:
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
Example:
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
Example:
Deny update
On customer
To steve
What is SQL Server Identifier?
Everything in a SQL server can have an identifier. Be it server, view, trigger, column etc. any database object name is an identifier. Identifiers may or may not be required for all objects...................
What are classes of identifier? Explain each class with an example i.e. regular and delimited identifier.
There are two classes of identifier. These identifiers must contain 1 to 128 characters. Regular identifiers: they follow the rules for the format of identifiers. They are not delimited when used in transact SQL statements.................
What are the Rules for Regular Identifiers?
Rules for Regular Identifiers: These rules are dependant on database compatibility level. For a compatibility level 90, following rules may apply:....................
What are ways to ensure integrity of data?
Data integrity ensures quality of data. It helps keep the data unchanged and unique. Data types ensure that the data accepted by the column is restricted to the type specified. For e.g. an integer data type cannot accept strings....................
Explain the types of Data integrity.
a. Entity Integrity: It means that the row is kept as a unique entity. It ensures that the integrity of the identifier is maintained through PRIMARY KEYS, IDENTITY property etc...............
What are integrity constraints? Explain their types. i.e. column and table constraints.
Integrity constraints: Integrity constraints ensure data consistency. This is achieved by making sure that changes made to the database by authorized users keep the data consistent. These constraints allow data to be inserted /modified only if it meets specified criteria....................
Explain the classes of constraints. i.e. Primary key, Unique, Foreign Key and Check Constraints.
Primary Key: This constraint when applied to a column ensures that the row is unique. This means that only ONE row with this ID can exist. For e.g. a table SALES has its primary key as sales_id. The column is specified as primary key by :sales_id PRIMARY KEY..................
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...............
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..............
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..................
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.................
Define Subqueries.
A subquery is a query within a query. These sub queries are created with SQL statements....................
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..............
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................
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................
Explain how to use Cube operator to summarize data.
SQL Server Cube operator - Nov 20, 2008 at 18:00 PM by Rajmeet Ghai
Explain how to use Cube operator to summarize data.
Answer
A Cube operator summarizes data of group by. It helps in determining subtotals and grand totals.
Example: This will display a summary of employees with same first name.
Select first_name, last_name, AVG(salary) FROM employee GROUP BY first_name WITH CUBE
Brief about Insert Statement with an example.
INSERT: The Insert statement is used to insert values as rows in a table..................
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....................
Brief about Bulk copy with an example.
Bulk copy utility of SQL allows data to be copied from one data file to another. The data is first exported from the source data file..................
Describe how bcp command prompt utility is used to import and export data.
The bcp utility is accessed from the command prompt. Syntax:
bcp {dbtable | query} {in | out | queryout | format} datafile [-n native type] [-c character type] [-S server name] [-U username] [-P password] [-T trusted connection]..................
Describe how bulk insert statement is used to import data.
Bulk Insert is used to copy data from a file into a table or view in a format as specified by the user................
Define distributed queries.
Distributed queries access data from multiple heterogeneous sources. These data sources may or may not be stored on the same computer.....................
Describe how Linked server is used to excess external data.
A linked server can be considered as another SQL server database running elsewhere. It can be a OLEDB or ODBC data source. They help in addressing distributed queries................
Describe how OPENQUERY function is used to excess external data.
OPENQUERY is used to execute the specified query on the linked sever. OPENQUERY can be referenced in from the FROM clause just like a table name..............
Describe how OPENROWSET and OPENDATASOURCE function is used to access external data.
OPENROWSET: Includes all connection information that is required to access remote data from an OLE DB data source.....................
Also Read
Define Distributed Query and Linked Server.
Ad doc computer Name
What is Distributed Queries?
What is a linked server?
Explain OPENQUERY function and OPENROWSET function.
What is “Index Tuning Wizard”?
Index Tuning Wizard is a software application that identifies tables which have inefficient indexes. It makes recommendations on how indexes...................
Explain how partitioning an important part of database optimization is.
Partitioning distributes database tables over different database which may reside on a different server.................
0 comments:
Post a Comment