GetPost - ESSIEN.NET

Jay Man What is the difference between having and where clause?

Ans: HAVING is used to specify a condition for a group or an aggregate function used in select statement. The WHERE clause selects before grouping. The HAVING clause selects rows after grouping. Unlike HAVING clause, the WHERE clause cannot contain aggregate functions. See Having vs Where Clause? for more details

Peter Agyekum What is the difference between primary key and unique constraints?

Ans: Primary key cannot have NULL value, the unique constraints can have NULL values. There is only one primary key in a table, but there can be multiple unique constrains.

Peter Agyekum What is database normalization?

Ans: It is a process of analyzing the given relation schemas based on their functional dependencies and primary keys to achieve the following desirable properties:

Peter Agyekum What is SQL?

SQL is Structured Query Language designed for inserting and modifying in a relational database system.

Peter Agyekum What are the differences between DDL, DML and DCL in SQL?

Ans: Following are some details of three. DDL stands for Data Definition Language. SQL queries like CREATE, ALTER, DROP and RENAME come under this. DML stands for Data Manipulation Language. SQL queries like SELECT, INSERT and UPDATE come under this. DCL stands for Data Control Language. SQL queries like GRANT and REVOKE come under this.

Peter Agyekum What is Join?

Ans: An SQL Join is used to combine data from two or more tables, based on a common field between them. For example, consider the following two tables. SELECT StudentCourse.CourseID, Student.StudentName

Peter Agyekum What is Identity?

Ans: Identity (or AutoNumber) is a column that automatically generates numeric values. A start and increment value can be set, but most DBA leave these at 1. A GUID column also generates numbers; the value of this cannot be controlled. Identity/GUID columns do not need to be indexed.

Peter Agyekum What is a view in SQL? How to create one

Ans: A view is a virtual table based on the result-set of an SQL statement. We can create using create view syntax.SELECT column_name(s) FROM table_name WHERE condition

Peter Agyekum What are the uses of view?

1. Views can represent a subset of the data contained in a table; consequently, a view can limit the degree of exposure of the underlying tables to the outer world: a given user may have permission to query the view, while denied access to the rest of the base table. 2. Views can join and simplify multiple tables into a single virtual table 3. Views can act as aggregated tables, where the database engine aggregates data (sum, average etc.) and presents the calculated results as part of the data 4. Views can hide the complexity of data; for example a view could appear as Sales2000 or Sales2001, transparently partitioning the actual underlying table 5. Views take very little space to store; the database contains only the definition of a view, not a copy of all the data which it presents. 6. Depending on the SQL engine used, views can provide extra security

Peter Agyekum What is a Trigger?

Ans: A Trigger is a code that associated with insert, update or delete operations. The code is executed automatically whenever the associated query is executed on a table. Triggers can be useful to maintain integrity in database.

Peter Agyekum What is a stored procedure?

Ans: A stored procedure is like a function that contains a set of operations compiled together. It contains a set of operations that are commonly used in an application to do some common database tasks.

Peter Agyekum What is the difference between Trigger and Stored Procedure?

Ans: Unlike Stored Procedures, Triggers cannot be called directly. They can only be associated with queries.

Peter Agyekum What is a transaction? What are ACID properties?

Ans: A Database Transaction is a set of database operations that must be treated as whole, means either all operations are executed or none of them. An example can be bank transaction from one account to another account. Either both debit and credit operations must be executed or none of them. ACID (Atomicity, Consistency, Isolation, Durability) is a set of properties that guarantee that database transactions are processed reliably.

Peter Agyekum What are indexes?

Ans: A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and the use of more storage space to maintain the extra copy of data. Data can be stored only in one order on disk. To support faster access according to different values, faster search like binary search for different values is desired. For this purpose, indexes are created on tables. These indexes need extra space on disk, but they allow faster search according to different frequently searched values.

Peter Agyekum What are clustered and non-clustered Indexes?

Ans: Clustered indexes is the index according to which data is physically stored on disk. Therefore, only one clustered index can be created on a given database table.

Peter Agyekum What are the main steps in Data Modeling?

Logical – Planning, Analysis and Design. 2. Physical – Design, Implementation and Maintenance

James Say What are the advantages of having an index on the SQL Server?

Index supports the mechanism of having faster data retrieval from the database. This forms a data structure in a way that helps in minimizing data comparisons. This improves the performance of the retrieval of the data from the database.

Raster Bigman What are the advantages of using Stored Procedures?

• Stored procedure can reduced network traffic and latency, boosting application performance
• Stored procedure execution plans can be reused, staying cached in SQL Server’s memory, reducing server overhead.
• Stored procedures can encapsulate logic. You can change stored procedure code without affecting clients
• Stored procedures provide better security to your data.

Raster Bigman WHAT IS VIEWS IN SQL SERVER

WHAT IS VIEWS IN SQL SERVER :

1• A view is a virtual table that consists of columns from one or more tables. Though it is similar to a table, it is stored in the database. It is a query stored as an object. Hence, a view is an object that derives its data from one or more tables. These tables are referred to as base or underlying tables. Once you have defined a view, you can reference it like any other table in a database.

2 • A view serves as a security mechanism. This ensures that users are able to retrieve and modify only the data seen by them. Users cannot see or access the remaining data in the underlying tables. A view also serves as a mechanism to simplify query execution. Complex queries can be stored in the form as a view, and data from the view can be extracted using simple queries.

3 • A view consists of a SELECT statement that stored with a database. Because views are stored as part of the database, they can be managed independently of the applications that use them.

4 • A view behaves like a virtual table. Since you can code a view name anywhere you can code a table name. A view is sometimes called a viewed table.

5 • Views can be used to restrict the data that a user is allowed to access or to present data in a form that is easy for the user to understand. In some database users may be allowed to access data only through views. Example:

• CREATE VIEW VendorData AS SELECT * FROM Vendor --you can execute your view like this;
• in this example VendorData is my View name.
• GO SELECT * FROM VendorData WHERE VendorState = 'PA' ORDER BY PostedDate GO

Raster Bigman WHAT'S THE DIFFERENCE BETWEEN A PRIMARY KEY AND A UNIQUE KEY?

1. Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. 2. Another major difference is that, primary key doesn't allow NULLs, but unique key allows one NULL only.

Raster Bigman Cross Join

A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table. The common example is when company wants to combine each product with a pricing table to analyze each product at each price.

Raster Bigman What are the TYPES OF JOIN

Inner Join:
• A join that displays only the rows that have a match in both joined tables is known as inner Join. This is the default type of join in the Query and View Designer.
Outer Join:
• A join that includes rows even if they do not have related rows in the joined table is an Outer Join. You can create three different outer join to specify the unmatched rows to be included:
• Left Outer Join: In Left Outer Join all rows in the first-named table i.e. “left” table, which appears leftmost in the JOIN clause are included. Unmatched rows in the right table do not appear.
• JOIN clause are included. Unmatched rows in the right table do not appear
• Right Outer Join: In Right Outer Join all rows in the second-named table i.e. “right” table, which appears rightmost in the JOIN clause are included. Unmatched rows in the left table are not included.
• Full Outer Join: In Full Outer Join all rows in all joined tables are included, whether they are matched or not.
Self Join :
• This is a particular case when one table joins to itself, with one or two aliases to avoid confusion.
• A self join can be of any type, as long as the joined tables are the same.
• A self join is rather unique in that it involves a relationship with only one table. The common example is when company has a hierarchal reporting structure whereby one member of staff reports to another
• Self Join can be Outer Join or Inner Join.

Raster Bigman Difference between Trigger and Stored Procedure:

Difference between Trigger and Stored Procedure::
• Stored procedure normally used for performing tasks. But the Trigger normally used for tracing and auditing logs.
• Stored procedures should be called explicitly by the user in order to execute. But the Trigger should be called implicitly based on the events defined in the table.
•Stored Procedure can run independently But the Trigger should be part of any DML events on the table.
• Stored procedure can be executed from the Trigger But the Trigger cannot be executed from the Stored procedures.
• Stored Procedures can have parameters. But the Trigger cannot have any parameters.

Raster Bigman DIFFERENCE BETWEEN UNION ALL STATEMENT AND UNION

UNION: :
• The UNION command is used to select related information from two tables, much like the JOIN command. However, when using the UNION command all selected columns need to be of the same data type. With UNION, only distinct values are selected.
UNION ALL :

Peter Agyekum HOW MANY CLUSTURED INDEX CAN HAVE A TABLE

A clustered index is a special type of index that reorders the way the records in the table are physically stored . therefore the table can have only one clustered index.

Peter Agyekum HOW MANY TYPES OF LOCAL TABLES IN SQL SERVER

There are 2 types of temporary tables, local and global. Local temporary tables are created using a single pound (#) sign and are visible to a single connection and automatically dropped when that connection ends. Global temporary tables are created using a double pound (##) sign and are visible across multiple connections and users and are automatically dropped when all SQL sessions stop referencing the global temporary table.

Peter Agyekum DIFFERENCE BETWEEN DELETE AND TRUNCATE

TRUNCATE :
Delete command removes the rows from a table based on the condition that we provide with a WHERE clause. Truncate will actually remove all the rows from a table and there will be no data in the table after we run the truncate command.
TRUNCATE :
• TRUNCATE is faster and uses fewer system and transaction log resources than DELETE.
• TRUNCATE removes the data by deallocating the data pages used to store the table’s data, and only the page deallocations are recorded in the transaction log.
• TRUNCATE removes all rows from a table, but the table structure, its columns, constraints, indexes and so on, remains. The counter used by an identity for new rows is reset to the seed for the column.
• You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint. Because TRUNCATE TABLE is not logged, it cannot activate a trigger.
• You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint. Because TRUNCATE TABLE is not logged, it cannot activate a trigger.
• TRUNCATE cannot be rolled back.
• TRUNCATE is DDL Command.
• TRUNCATE Resets identity of the table
DELETE :
• DELETE removes rows one at a time and records an entry in the transaction log for each deleted row.
• If you want to retain the identity counter, use DELETE instead. If you want to remove table definition and its data, use the DROP TABLE statement.
• DELETE Can be used with or without a WHERE clause
• DELETE Activates Triggers.
• . DELETE Activates Triggers.
• DELETE Activates Triggers.
•DELETE can be rolled back.
•DELETE is DML Command.
•DELETE does not reset identity of the table.

Antonita Seed WHAT IS NOLOCK?

Using the NOLOCK query optimizer hint is generally considered good practice in order to improve concurrency on a busy system. When the NOLOCK hint is included in a SELECT statement, no locks are taken when data is read. The result is a Dirty Read, which means that another process could be updating the data at the exact time you are reading it. There are no guarantees that your query will retrieve the most recent data. The advantage to performance is that your reading of data will not block updates from taking place, and updates will not block your reading of data. SELECT statements take Shared (Read) locks. This means that multiple SELECT statements are allowed simultaneous access, but other processes are blocked from modifying the data. The updates will queue until all the reads have completed, and reads requested after the update will wait for the updates to complete. The result to your system is delay (blocking).

Antonita Seed WHAT IS CTE?

CTE is an abbreviation Common Table Expression. A Common Table Expression (CTE) is an expression that can be thought of as a temporary result set which is defined within the execution of a single SQL statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query.

Antonita Seed WHAT ARE THE ADVANTAGES OF USING CTE?

Using CTE improves the readability and makes maintenance of complex queries easy. The query can be divided into separate, simple, logical building blocks which can be then used to build more complex CTEs until final result set is generated. CTE can be defined in functions, stored procedures, triggers or even views. After a CTE is defined, it can be used as a Table or a View and can SELECT, INSERT, UPDATE or DELETE Data.

Antonita Seed WHAT IS THE STUFF FUNCTION AND HOW DOES IT DIFFER FROM THE REPLACE FUNCTION?

STUFF function is used to overwrite existing characters. Using this syntax, STUFF (string_expression, start, length, replacement_characters), string_expression is the string that will have characters substituted, start is the starting position, length is the number of characters in the string that are substituted, and replacement_characters are the new characters interjected into the string. REPLACE function to replace existing characters of all occurrences. Using the syntax REPLACE (string_expression, search_string, replacement_string), where every incidence of search_string found in the string_expression will be replaced with replacement_string.

Antonita Seed WHAT ARE THE DIFFERENCE BETWEEN CLUSTERED AND A NON-CLUSTERED INDEX?

A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages. A non clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a non clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.

Antonita Seed WHAT ARE THE DIFFERENT INDEX CONFIGURATIONS A TABLE CAN HAVE?

A table can have one of the following index configurations: No indexes A clustered index A clustered index and many non-clustered indexes A non-clustered index Many non-clustered indexes

Antonita Seed DIFFERENCE BETWEEN A HAVING CLAUSE AND A WHERE CLAUSE?

They specify a search condition for a group or an aggregate. But the difference is that HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause. Having Clause is basically used only with the GROUP BY function in a query whereas WHERE Clause is applied to each row before they are part of the GROUP BY function in a query.

Antonita Seed Difference between Set and Select:

1. SET is the ANSI standard way of assigning values to variables, and SELECT is NON-ANSI standard . 2. SELECT can assign more than one variable at a time but SET allows you to assign data to only one variable at a time. 3. When using a query to populate a variable, SET will fail with an error, if the query returns more than one value. But SELECT will assign one of the returned rows and mask the fact that the query returned more than one row. As a result, bugs in your code could go unnoticed with SELECT, and this type of bugs are hard to track down too.

Antonita Seed DIFFERENCE BETWEEN CHAR AND VARCHAR DATA TYPES

The char is a fixed-length character data type, the varchar is a variable-length character data type. Because char is a fixed-length data type, the storage size of the char value is equal to the maximum size for this column. Because varchar is a variable-length data type, the storage size of the varchar value is the actual length of the data entered, not the maximum size for this column. You can use char when the data entries in a column are expected to be the same size. You can use varchar when the data entries in a column are expected to vary considerably in size.

Antonita Seed DEFINE CANDIDATE KEY, ALTERNATE KEY, COMPOSITE KEY.

A candidate key is one that can identify each row of a table uniquely. Generally a candidate key becomes the primary key of the table. If the table has more than one candidate key, one of them will become the primary key, and the rest are called alternate keys. A key formed by combining at least two or more columns is called composite key.

Antonita Seed WHAT ARE CONSTRAINTS? EXPLAIN DIFFERENT TYPES OF CONSTRAINTS.

Constraints enable the RDBMS enforce the integrity of the database automatically, without needing you to create triggers, rule or defaults. Types of constraints: · PRIMARY KEY UNIQUE · FOREIGN KEY · CHECK · NOT NULL A PRIMARY KEY constraint is a unique identifier for a row within a database table. Every table should have a primary key constraint to uniquely identify each row and only one primary key constraint can be created for each table. The primary key constraints are used to enforce entity integrity. A UNIQUE constraint enforces the uniqueness of the values in a set of columns, so no duplicate values are entered. The unique key constraints are used to enforce entity integrity as the primary key constraints. A FOREIGN KEY constraint prevents any actions that would destroy link between tables with the corresponding data values. A foreign key in one table points to a primary key in another table. Foreign keys prevent actions that would leave rows with foreign key values when there are no primary keys with that value. The foreign key constraints are used to enforce referential integrity. A CHECK constraint is used to limit the values that can be placed in a column. The check constraints are used to enforce domain integrity. A NOT NULL constraint enforces that the column will not accept null values. The not null constraints are used to enforce domain integrity, as the check constraints. You can create constraints when the table is created, as part of the table definition by using the CREATE TABLE statement.

Peter Agyekum SQL Query to find second highest salary of Employee

select MAX(Salary) from Employee WHERE Salary NOT IN (select MAX(Salary) from Employee);

Peter Agyekum SQL Query to find Max Salary from each department.

SELECT DeptID, MAX(Salary) FROM Employee GROUP BY DeptID

Peter Agyekum Write SQL Query to display the current date.

SELECT GetDate();

Peter Agyekum Write an SQL Query to check whether date passed to Query is the date of given format or not.

SELECT ISDATE('1/08/13') AS "MM/DD/YY";

Peter Agyekum Write an SQL Query to print the name of the distinct employee whose DOB is between 01/01/1960 to 31/12/1975.

ELECT DISTINCT EmpName FROM Employees WHERE DOB BETWEEN ‘01/01/1960’ AND ‘31/12/1975

Peter Agyekum Write an SQL Query find number of employees according to gender whose DOB is between 01/01/1960 to 31/12/1975.

SELECT COUNT(*), sex from Employees WHERE DOB BETWEEN '01/01/1960' AND '31/12/1975' GROUP BY sex;

Peter Agyekum Write an SQL Query to find an employee whose Salary is equal or greater than

SELECT EmpName FROM Employees WHERE Salary>=10000;

Peter Agyekum Write an SQL Query to find name of employee whose name Start with ‘M’

SELECT * FROM Employees WHERE EmpName like 'M%';

Peter Agyekum find all Employee records containing the word "Joe", regardless of whether it was stored as JOE, Joe, or joe.

SELECT * from Employees WHERE UPPER(EmpName) like '%JOE%';

Peter Agyekum Write an SQL Query to find the year from date.

SELECT YEAR(GETDATE()) as "Year";

Peter Agyekum Write SQL Query to find duplicate rows in a database? and then write SQL query to delete them?

SELECT * FROM emp a WHERE rowid = (SELECT MAX(rowid) FROM EMP b WHERE a.empno=b.empno) to Delete: DELETE FROM emp a WHERE rowid != (SELECT MAX(rowid) FROM emp b WHERE a.empno=b.empno);

Peter Agyekum There is a table which contains two column Student and Marks, you need to find all the students, whose marks are greater than average marks i.e. list of above average students.

SELECT student, marks from table where marks > SELECT AVG(marks) from table)

Peter Agyekum You need to know about self-join to solve this problem. In Self Join, you can join two instances of the same table

SELECT e.name, m.name FROM Employee e, Employee m WHERE e.mgr_id = m.emp_id;

Peter Agyekum What is a Cursor?

A cursor is a database object that allows to manipulate data on a row-by-row basis.

Peter Agyekum What is a Linked Server?

Linked Server option of SQL Server allows querying data from several SQL Server instances. Using Linked Server, we can connect to other database instances on the same server or on another machine or remote servers.

Peter Agyekum What is SQL Profiler?

SQL Profiler is an analysis tool that allows system administrator to monitor events. It saves and captures data about each event of a table for analysis.

Peter Agyekum Explain the concept of recursive stored procedure.

Recursive stored procedure uses a recursive algorithm wherein the solution is arrived by repeatedly invoking itself.

Peter Agyekum Differentiate between a HAVING CLAUSE and a WHERE CLAUSE.

HAVING CLAUSE - HAVING CLAUSE is used only with the SELECT statement. - It is generally used in a GROUP BY clause in a query. - If GROUP BY is not used, HAVING works like a WHERE clause. WHERE Clause - It is applied to each row before they become a part of the GROUP BY function in a query.

Peter Agyekum What do you understand by a view? What does the WITH CHECK OPTION clause for a view do?

A view is a virtual table that consists of fields from one or more real tables. - It is usually used to join multiple tables and get the data. - The WITH CHECK OPTION for a view prevents any modification to the data that does not confirm to the WHERE clause of the view definition. - This allows the data belonging to the view to be updated through the view.

Peter Agyekum Explain query execution plan?

The optimizer available in SQL Server optimizes the code to be effectively executed. - A query execution plan shows how this optimizer would run the query. - Query execution plan can be viewed by : - Using the Show Execution Plan option available in Query Analyzer, - Displaying Estimated Execution Plan on the query dropdown menu, - Use the SET SHOWPLAN_TEXT ON command before running a query and capturing the execution plan event in a SQL Server Profiler trace.

Peter Agyekum What is the function of SQL Server Agent Windows service?

- It is a Windows service which handles the tasks scheduled within the SQL Server environment. These tasks are also called as job and are stored with in SQL server. The jobs may run through a trigger, a predefined schedule or on demand. - This service is very useful in determining why a particular job did not run as intended.

Peter Agyekum Differentiate between a primary key and a unique key.

- By default, clustered index on the column are created by the primary key whereas nonclustered index are created by unique key. - Primary key doesn't allow NULLs, but unique key allows one NULL.

Peter Agyekum What is recursion? Is it possible for a stored procedure to call itself or recursive stored procedure? How many levels of SP nesting are possible?

- Recursion is method of problem solving where the solution is arrived at by repetitively applying the logic and solution to the subsets of the problem. - Transact-SQL supports recursion. So, yes it is possible for a stored procedure to call itself. - Stored procedures and managed code references can be nested up to 32 levels.

Peter Agyekum What are the advantages of using Stored Procedures?

Following are the advantages of Stored Procedures: - They help in reducing the network traffic and latency which in turn boosts application performance. - They help in promoting code reuse. - They provide better security to data. - It is possible to encapsulate the logic using stored procedures. This allows to change stored procedure code without affecting clients. - It is possible to reuse stored procedure execution plans, which are cached in SQL Server's memory. This reduces server overhead.

Peter Agyekum You want to implement the following relationships while designing tables. How would you do it? a.) One-to-one b.) One-to-many c.) Many-to-many

a.) One-to-One relationship - It can be implemented as a single table and rarely as two tables with primary and foreign key relationships. b.) One-to-Many relationships - By splitting the data into two tables with primary key and foreign key relationships. c.) Many-to-Many relationships - By using a junction table with the keys from both the tables forming the composite primary key of the junction table.

Peter Agyekum Differentiate between DELETE and TRUNCATE.

- Truncate can not be rolled back while Delete can be. - Truncate keeps the lock on table while Delete keeps the lock on each row. - Truncate resets the counter of the Identity column while Delete doesn't do so. - Trigger is not fired in Truncate while it happens in Delete.

Peter Agyekum What are the properties of the Relational tables?

Values are atomic. 2. Column values are of the same kind. 3. Each row is unique. 4. The sequence of columns is insignificant. 5. The sequence of rows is insignificant. 6. Each column must have a unique name.

Peter Agyekum Explain the following.

Collation is a type of sort order. There are mainly three types of sort orders, namely: i.) Dictionary case sensitive ii.)Dictionary - case insensitive iii.)Binary.

Peter Agyekum Explain the following:

These are the buffer pages that contain modifications which have not been written to disk.

Peter Agyekum b.) ETL (Extraction, Transformation, and Loading)

- It is the process of copying and cleaning data from heterogeneous sources. - It is an important part of development projects for data warehousing and business intelligence.

Peter Agyekum Differentiate between a Local and a Global temporary table?

Local Temporary Table - A local temporary table exists only for the duration of a connection or, if defined inside a compound statement, for the duration of the compound statement. Global Temporary Table - Global temporary tables (created with a double “##”) are visible to all sessions. - Global temporary tables are dropped when the session that created it ends, and all other sessions have stopped referencing it.

Peter Agyekum Explain different types of Locks in SQL Server.

There are 3 kinds of locks in SQL Server: i.) Shared locks: They are used for operations which do not allow any change or update of data. For e.g. SELECT ii.) Update locks: They are used when SQL Server wants to modify a page. The update page lock is then promoted to an exclusive page lock before actually making the changes. iii.) Exclusive locks: They are used for the data modification operations. For e.g. UPDATE, INSERT, or DELETE.

Peter Agyekum Explain various data region available in SSRS with their use.

Data regions are report items used to display data from a single dataset. You can perform grouping, sorting and various aggregate functions on data in data region. In SSRS 2005, there were 4 data regions: 1. Table 2. Matrix 3. List 4. Chart While in SSRS 2008, there are one additional data region namely Gauge.

Peter Agyekum What are various aggregate functions that are available?

The following are various aggregate functions available: 1. SUM 2. AVG 3. COUNT 4. COUNTDISTINCT 5. MAX 6. MIN 7. STDEV 8. STDEVP 9. VAR 10. VARP

Peter Agyekum How do you integrate the SSRS reports in your application?

There are 3 ways in which you can integrate reports into your application: 1. Navigating to URL i.e. https:\\servername\reportservername\reportname: This is simplest and most popular way. A separate login might be required since we are directly calling the report from report server. Address of report server gets expose to user. 2. Using IFrame, Browser control or Report Viewer Control: In this approach, we embed the URL of report server in our application, hence address of reportserver is not exposed. No separate window opens. A user does not come to know that he has moved to different server. 3. Programmatically sending a web request using SOAP to report server.

Peter Agyekum Explain use of Expression builder.

Expressions provide us with flexibility to customize our report. It is written in Visual basic and is used throughout the report to to retrieve, calculate, display, group, sort, filter, parameterize, and format the data in a report. They start with equal sign (=).

Peter Agyekum What’s the use of custom fields in report?

Custom fields can be defined as alias column of the report since the operation is performed on report server rather than on database server. The custom field is very useful for the data manipulation like adding some fields whose value can be calculated based on expression, text e.g. instead of CName fetched from database, I want the dataset to display Customer Name etc. We can add custom fields as right click on dataset, select add in Dataset window. The New field dialog box will open, we can add name of custom field and also mention whether it is database field or calculated one. If it is calculated, then we can mention the computation in this window.

Peter Agyekum How does your SSRS maintain security?

Reporting services maintain role based security. When a user logs into reporting services, a Report Manager (whose duty is to maintain security of Reporting Services) first checks the identity of user and then determine what rights he have to perform on report. Report Manager manages the security at 2 levels – 1. System-level– Administers the report server globally 2. Item-level– Security at report and dataset level System-level roles are: 1. System Administrator – can manage report server and report manager security 2. Site User- view basic information like report properties and schedules. Item-level roles: User can use any of predefined item-level roles or create their own roles by using combination of predefined item-level roles. Pre-defined Item-level roles are: 1. Browser – can navigate to report and run them. 2. My Reports – these users’ rights is restricted to reports present in their MyReports folder. However, they can create, view and manage reports in their folder. 3. Publisher – As name suggest, publisher user has rights to publish reports to Reporting Server database. 4. Content Manager – has all permission at item-level.

Peter Agyekum What is SQL Injection?

- SQL Injection is an attack in which attacker take the advantage of insecure application over internet by running the SQL command against the database and to steal information from it that too using GUI of the website. - This attack can happen with the applications in which SQL queries are generated in the code. - The attacker tries to inject their own SQL into the statement that the application will use to query the database. - For example:Suppose the below query string is used for a search feature in a website and a user may have the inserted “Arpit” as the keyword to search. If in the code behind the keyword is directly used into the SQL statement, it will be like. String sql = “Select EmpName, City from EmployeeMaster where EmpName like ‘%” + txtSearch.Text + “%’”; But the attacker might enter the keyword like ‘ UNION SELECT name, type, id from sysobjects;-- This way attacker can get details of all the objects of the application database and using that attacker can steal further information.

Peter Agyekum What is DBCC? Give few examples.

DBCC stands for Database Consistency Checker.These commands are used to check the consistency of the database like validation tasks, maintenance and status checks. For example – 1. DBCC CHECKALLOC – It is used to check that all pages are correctly allocated in database. 2. DBCC CHECKDB – It is used to check the integrity & allocation of specific objects in a database. 3. DBCC SQLPERF – It is used to show transaction log statistics. 4. DBCC SQLFILEGROUP – It used to check all the tables file group for any design.

Peter Agyekum What is difference between View and Materialized view?

60 Sql Server Interview Questions and Answers Sql Server Developer & DBA >> 60 Sql Server Interview Questions and Answers Next Page » Dear Readers, Welcome to SQL Server interview questions with answers and explanation. These 60 solved SQL Server questions will help you prepare for technical interviews and online selection tests conducted during campus placement for freshers and job interviews for professionals. After reading these tricky SQL Server questions, you can easily attempt the objective type and multiple choice type questions on SQL. Updated on Feb 13, 2018 What is a Cursor? A cursor is a database object that allows to manipulate data on a row-by-row basis. What is a Linked Server? Linked Server option of SQL Server allows querying data from several SQL Server instances. Using Linked Server, we can connect to other database instances on the same server or on another machine or remote servers. What is SQL Profiler? SQL Profiler is an analysis tool that allows system administrator to monitor events. It saves and captures data about each event of a table for analysis. Video: SQL Server interview questions for freshers Explain the concept of recursive stored procedure. Recursive stored procedure uses a recursive algorithm wherein the solution is arrived by repeatedly invoking itself. Example CREATE PROCEDURE [Factorial] ( @Number Integer, @rValue Integer OUTPUT ) AS DECLARE @In Integer DECLARE @Out Integer IF @Number != 1 BEGIN SELECT @In = @Number – 1 EXEC Factorial @In, @Out OUTPUT ''' here stored procedure is getting invoked recursively SELECT @rValue = @Number * @Out END ELSE BEGIN SELECT @rValue = 1 END RETURN What is Collation? Collation specifies the sort order in a table. It determines how data is sorted and compared. Types of sort order Case sensitive Case Insensitive Binary When is the UPDATE_STATISTICS command used? - When the processing of large data is done, this command is used. - Whenever large number of deletions, modification or copy takes place into the tables, the indexes need to be updated to take care of these changes. UPDATE_STATISTICS performs this job. Differentiate between a HAVING CLAUSE and a WHERE CLAUSE. HAVING CLAUSE - HAVING CLAUSE is used only with the SELECT statement. - It is generally used in a GROUP BY clause in a query. - If GROUP BY is not used, HAVING works like a WHERE clause. . - The WITH CHECK OPTION for a view prevents any modification to the data that does not confirm to the WHERE clause of the view definition. - This allows the data belonging to the view to be updated through the view. Explain query execution plan? - The optimizer available in SQL Server

Peter Agyekum What is CTE (Common Table Expression)?

When a complex SQL statement has number of joins then it can be made easier by using Common Table Expression. Consider the following SQL statement. SELECT * FROM ( SELECT emp.EmpName,dept.Department,emp.Gender FROM Employee emp left join Department dept on emp.DeptID = dept.DeptID) E WHERE E.Gender = ‘Male’ ORDER BY T.EmpName

Peter Agyekum What are Sparse columns?

- Sparse column is a type of column which is used to optimize storage for null values. - When a column there is big number of null then by defining that column as spars column we can save a large amount of disk space. - The drawback of sparse column is that it requires more space for the non null values. When we define a column as sparse it requires additional 4 Byte for not null values. - For example, a DATETIME column in a non-sparse column required 8 bytes of storage whereas if we define that column as a sparse column then it will require 12 bytes. - It is not possible to set ROWGUIDCOL and IDENTITY properties in sparse column

Peter Agyekum What is RANK function?

-RANK is one of the Ranking functions which are used to give rank to each row in the result set of a SELECT statement. -For using this function first specify the function name, followed by the empty parentheses. -Then specify the OVER function. For this function, you have to pass an ORDER BY clause as an argument. The clause specifies the column(s) that you are going to rank.

Peter Agyekum What are cursors and when they are useful?

-When we execute any SQL operations, SQL Server opens a work area in memory which is called Cursor. -When it is required to perform the row by row operations which are not possible with the set-based operations then Cursor is used. -There are two of cursors: 1. Implicate Cursor: SQL Server automatically manages cursors for all data manipulation statements. These cursors are called implicit cursors. 2. Explicit Cursor: When the programmer wants to perform the row by row operations for the result set containing more than one row, then he explicitly declare a cursor with a name.They are managed by OPEN, FETCH and CLOSE. %FOUND, %NOFOUND, %ROWCOUNT and %ISOPEN attributes are used in both types of cursors.

Peter Agyekum What is log shipping?

-SQL has introduced Log Shipping feature to synchronize the Distributed Database Server. Synchronize the database by copying Transaction logs, Backing up, Restoring data. SQL Server Job Agents is used to make these processes automatic. -In the case of failure the Log Shipping will not transfer the server. That means it will not redirect your application to other server. This has to be done manually. -Log shipping synchronizes the database only. The main functions of Log Shipping are as follows: 1. To Back up the transaction log of the primary database 2. To Copy the transaction log backup to every secondary server 3. To Restore the transaction log backup on the secondary database

Peter Agyekum What is the difference between Locking and multi-versioning?

Locking is a means of not allowing any other transaction to take place when one is already in progress. In this the data is locked and there won’t be any modification taking place till the transaction either gets successful or it fails. The lock has to be put up before the processing of the data whereas Multi-versioning is an alternate to locking to control the concurrency. It provides easy way to view and modify the data. It allows two users to view and read the data till the transaction is in progress.

Peter Agyekum What is bit data type and what's the information that can be stored inside a bit column?

- Bit data type is the smallest type used in a language. It is used to store the boolean information of the form 1 (true) or 0 (false). The former versions of SQL server doesn’t support NULL type in this but recent version such as SQL server 7.0 onwards it supports NULL state as well.

Cara Brown What is a database schema?

Schema is a logical representation or structure of the entire database. It defines how the data is organized, associated and stored in the database.

Cara Brown What is RDBMS?

Relational Database Management System (RDBMS) is a set of programs that helps a developer to interact with the database for creating, updating or deleting data. This is done through queries (SQL). For example, each data element can be a row in the table.

Peter Agyekum What is Join?

JOINS are used to retrieve data from multiple tables. A SQL Server JOIN is performed whenever two or more tables are joined in a SQL statement.

Peter Agyekum How many Clustered Index may table have?

There are two types of Indexes in SQL Server: Clustered Index. Non-Clustered Index.

Peter Agyekum What is INSERT Query?

The SQL INSERT INTO Statement is used to add new rows of data to a table in the database.

Peter Agyekum What is SQL Joins?

The SQL Joins clause is used to combine records from two or more tables in a database. A JOIN is a means for combining fields from two tables by using values common to each

Peter Agyekum What is Where in SQL?

The SQL WHERE clause is used to specify a condition while fetching the data from a single table or by joining with multiple tables. If the given condition is satisfied, then only it returns a specific value from the table. You should use the WHERE clause to filter the records and fetching only the necessary records.

Peter Agyekum What is SELECT Query?

SELECT Query The SQL SELECT statement is used to fetch the data from a database table which returns this data in the form of a result table. These result tables are called result sets. SELECT column1, column2, column N FROM table_name; If you want to fetch all the fields available in the field, then you can use the following syntax. SELECT * FROM table_name;

Peter Agyekum What is Database?

A database is a collection of information that is organized so that it can be easily accessed, managed, and updated.

Peter Agyekum What is Relational Database

A relational database is a type of database. It uses a structure that allows us to identify and access data in relation to another piece of data in the database. Often, data in a relational database is organized into tables.

Peter Agyekum What is Tables : Rows and Columns?

Tables: Rows and Columns Tables can have hundreds, thousands, sometimes even millions of rows of data. These rows are often called records. Tables can also have many columns of data. These columns are fields. Columns are labeled with a descriptive name (say, the age for example) and have a specific data type.

Peter Agyekum The Relational Database Management System?

A relational database management system (RDBMS) is a program that allows you to create, update, and administer a relational database. Most relational database management systems use the SQL language to access the database.

Peter Agyekum What is SQL - Structured Query Language?

SQL - Structured Query Language SQL (Structured Query Language) is a programming language used to communicate with data stored in a relational database management system. SQL syntax is similar to the English language, which makes it relatively easy to write, read, and interpret. Many RDBMSs use SQL (and variations of SQL) to access the data in tables. For example, SQLite is a relational database management system. SQLite contains a minimal set of SQL commands (which are the same across all RDBMSs). Other RDBMSs may use other variants.

Peter Agyekum Why do we need SQL?

Why do we need SQL? Allows users to access data in the relational database management systems. Allows users to describe the data. Allows users to define the data in a database and manipulate that data. Allows to embed within other languages using SQL modules, libraries & pre-compilers. Allows users to create and drop databases and tables. Allows users to create view, stored procedure, functions in a database. Allows users to set permissions on tables, procedures and views.

Peter Agyekum What is Primary Key?

A primary key is a field in a table that uniquely identifies each row/record in a database table. Primary keys must contain unique values. A primary key column cannot have NULL values. A table can have only one primary key, which may consist of single or multiple fields. When multiple fields are used as a primary key, they are called a composite key. If a table has a primary key defined on any field(s), then you cannot have two records having the same value of that field(s).

Peter Agyekum What is Foreign Key?

A foreign key is a key used to link two tables together. This is sometimes also called as a referencing key. A Foreign Key is a column or a combination of columns whose values match a Primary Key in a different table. The relationship between the 2 tables matches the Primary Key in one of the tables with a Foreign Key in the second table.

Peter Agyekum What is HTML?

HTML is the standard markup language for creating Web pages. HTML stands for Hyper Text Markup Language HTML describes the structure of Web pages using markup HTML elements are the building blocks of HTML pages HTML elements are represented by tags HTML tags label pieces of content such as "heading", "paragraph", "table", and so on Browsers do not display the HTML tags, but use them to render the content of the page

Peter Agyekum What is trigger?

Trigger: A trigger is a stored procedure in a database that automatically invokes whenever a special event in the database occurs. For example, a trigger can be invoked when a row is inserted into a specified table or when certain table columns are being updated.

Peter Agyekum What is difference between full join and outer join?

Inner join returns only the matching rows between both the tables, non-matching rows are eliminated. Full Join or Full Outer Join returns all rows from both the tables (left & right tables), including non-matching rows from both the tables

Peter Agyekum What is use of left join in SQL?

The LEFT JOIN keyword returns all records from the left table (table1), and the matching records from the right table (table2).

Peter Agyekum What is a cross join used for?

The CROSS JOIN is used to generate a paired combination of each row of the first table with each row of the second table. This join type is also known as cartesian join.

Peter Agyekum What is self join and its example?

A self-join is a join that can be used to join a table with itself. Hence, it is a unary relation. In a self-join, each row of the table is joined with itself and all the other rows of the same table. Thus, a self-join is mainly used to combine and compare the rows of the same table in the database.

Peter Agyekum What is SQL TRIM?

The TRIM function in SQL is used to remove a specified prefix or suffix from a string. The most common pattern being removed is the white space. This function is called differently in different databases:

Peter Agyekum What is Common Table Expression?

A Common Table Expression, also called as CTE in short form, is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement.

Back to HomePage

Explore Programming Skills?

Explore Home Features?

Explore Home Features?

Explore Home Features?

Explore Home Features?

Explore Home Features?

Explore Home Features?

Explore Home Features?