| shatlin's profileShatlin Denistan's BlogBlogListsGuestbook |  Tools  Help |
Shatlin Denistan's Blog |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
October 28 OOPS
Static Members of the classStatic members belong to the whole class rather than to individual object Static members are accessed with the name of class rather than reference to objects. Eg: class Test { public int rollNo; public int mathsMarks; public static int totalMathMarks; } class TestDemo { public static void main() { Test stud1 = new Test(); stud1.rollNo = 1; stud1.mathsMarks = 40; stud2.rollNo = 2; stud2.mathsMarks = 43; Test.totalMathsMarks = stud1.mathsMarks + stud2.mathsMarks; } }
As static method may be called without any reference to object, you can not use instance members inside static methods or properties, while you may call a static member from a non-static context. The reason for being able to call static members from non-static context is that static members belong to the class and are present irrespective of the existence of even a single object.
What is nested Classes ?
Nested classes are classes within classes. In sample below “ClsNested” class has a “ChildNested” class nested inside it. Public Class ClsNested Public Class ChildNested Public Sub ShowMessage() MessageBox.Show(“Hi this is nested class”) End Sub End Class End Class This is the way we can instantiate the nested class and make the method call. Dim pobjChildNested As New ClsNested.ChildNested() pobjChildNested.ShowMessage()217 Note:-In CD the above sample is provided in “WindowsNestedClasses”.
What is Operator Overloading in .NET?
It provides a way to define and use operators such as +, -, and / for user-defined classes or structs. It allows us to define/redefine the way operators work with our classes and structs. This allows programmers to make their custom types look and feel like simple types such as int and string. VB.NET till now does not support operator overloading. Operator overloading is done by using the “Operator” keyword. Note:- Operator overloading is supported in VB.NET 2005
Eg: namespace Wrox.ProCSharp.OOCSharp { struct Vector { public double x, y, z;
public Vector(double x, double y, double z) { this.x = x; this.y = y; this.z = z; }
public Vector(Vector rhs) { x = rhs.x; y = rhs.y; z = rhs.z; }
public override string ToString() { return "( " + x + " , " + y + " , " + z + " )"; } public static Vector operator + (Vector lhs, Vector rhs) { Vector result = new Vector(lhs); result.x += rhs.x; result.y += rhs.y; result.z += rhs.z; return result; } } static void Main() { Vector vect1, vect2, vect3; vect1 = new Vector(3.0, 3.0, 1.0); vect2 = new Vector(2.0, -4.0, -4.0); vect3 = vect1 + vect2; Console.WriteLine("vect1 = " + vect1.ToString()); Console.WriteLine("vect2 = " + vect2.ToString()); Console.WriteLine("vect3 = " + vect3.ToString()); } ScopeWhat are the different accessibility levels defined in .NET ?
Following are the five levels of access modifiers :- √ Private : Only members of class have access. √ Protected :-All members in current class and in derived classes can access the variables. √ Friend (internal in C#) :- Only members in current project have access to the elements. √ Protected friend (protected internal in C#) :- All members in current project and all members in derived class can access the variables. √ Public :- All members have access in all classes and projects.
Who is a protected class-level variable available to?
Describe the accessibility modifier “protected internal”.It is available to classes that are within the same assembly OR derived from the specified base class. OOPSTypes and ObjectsEnumerationEnumeration improves code readability. It also helps in avoiding typing mistake. Concept of Heap and StackThe Program Instruction and Global and Static variables are stored in a region known as permanent storage area and the local variables are stored in another area called stack. The memory space located between these two regions is available for dynamic memory allocation during execution of program. This free memory region is called heap. The size of heap keeps on changing when program is executed due to creation and death of variables that are local to functions and blocks. Therefore, it is possible to encounter memory “overflow” during dynamic allocation process. Value Type and Reference TypeA variable is value type or reference type is solely determined by its data type. Eg: int, float, char, decimal, bool, decimal, struct, etc are value types, while object type such as class, String, Array, etc are reference type. What are shared (VB.NET)/Static(C#) variables?
Static/Shared classes are used when a class provides functionality which is not specific to any instance. In short if you want an object to be shared between multiple instances you will use a static/Shared class.
Following are features of Static/Shared classes :-
They can not be instantiated. By default a object is created on the first method call to that object. Static/Shared classes can not be inherited. Static/Shared classes can have only static members. Static/Shared classes can have only static constructor.
ref keywordPassing variables by value is the default. However, we can force the value parameter to be passed by reference. Note: variable “must” be initialized before it is passed into a method. out keywordout keyword is used for passing a variable for output purpose. It has same concept as ref keyword, but passing a ref parameter needs variable to be initialized while out parameter is passed without initialized. It is useful when we want to return more than one value from the method. Note: You must assigned value to out parameter in method body, otherwise the method won’t be compiled.
Boxing and Un-BoxingBoxing: means converting value-type to reference-type. Eg: int I = 20; string s = I.ToSting(); UnBoxing: means converting reference-type to value-type. Eg: int I = 20; string s = I.ToString(); //Box the int int J = Convert.ToInt32(s); //UnBox it back to an int. Note: Performance Overheads due to boxing and unboxing as the boxing makes a copy of value type from stack and place it inside an object of type System.Object in the heap.
Classes, Structures
OOPs - Object Oriented Programming Systems Everything in the world is an object. The type of the object may vary. In OOPS, we get the power to create objects of our own, as & when required. OOPs is a programming methodology where each entity is an object. It is a method of computer programming where entities of related data together with routines associated with it are treated as one object in the program.
What is a class? What is a Base Class? A class is an organized store-house in object-oriented programming that gives coherent functional abilities to a group of related code. It is the definition of an object, made up of software code. Using classes, we may wrap data and behaviour together (Encapsulation). We may define classes in terms of classes (Inheritance). We can also override the behaviour of a class using an alternate behaviour (Polymorphism).
What’s a Class ?A class describes all the attributes of objects, as well as the methods that implement the behavior of member objects. It’s a comprehensive data type which represents a blue print of objects. It’s a template of object.
What’s an Object ?It is a basic unit of a system. An object is an entity that has attributes, behavior, and identity. Objects are members of a class. Attributes and behavior of an object are defined by the class definition.
What is the relation between Classes and Objects ?They look very much same but are not same. Class is a definition, while object is a instance of the class created. Class is a blue print while objects are actual objects existing in real world. Example we have class CAR which has attributes and methods like Speed, Brakes, Type of Car etc. Class CAR is just a prototype, now we can create real time objects which can be used to provide functionality. Example we can create a Maruti car object with 100 km speed and urgent brakes.
What is Encapsulation? Encapsulation - is the ability of an object to hide its data and methods from the rest of the world. It is one of the fundamental principles of OOPs. Say we create a class, named Calculations. This class may contain a few members in the form of properties, events, fields or methods. Once the class is created, we may instantiate the class by creating an object out of it. The object acts as an instance of this class, the members of the class are not exposed to the outer world directly; rather, they are encapsulated by the class. What is shadowing ?When two elements in a program have same name, one of them can hide and shadow the other one. So in such cases the element which shadowed the main element is referenced. Below is a sample code, there are two classes “ClsParent” and “ClsShadowedParent”. In “ClsParent” there is a variable “x” which is a integer. “ClsShadowedParent” overrides “ClsParent” and shadows the “x” variable to a string.
Public Class ClsParent Public x As Integer End Class Public Class ClsShadowedParent Inherits ClsParent Public Shadows x As String End Class
What is the difference between Shadowing and Overriding ?Following are the differences between shadowing and overriding :- √ Overriding redefines only the implementation while shadowing redefines the whole element. √ In overriding derived classes can refer the parent class element by using “ME” keyword, but in shadowing you can access it by “MYBASE”.
What is shared keyword used for? What is static? Shared and Static mean the same. Shared is used in VB.NET, while Static is used in C#.
What are similarities between Class and structure ?Following are the similarities between classes and structures :- √ Both can have constructors, methods, properties, fields, constants, enumerations, events, and event handlers. √ Structures and classes can implement interface. √ Both of them can have constructors with and without parameter. √ Both can have delegates and events.
What is the difference between Class and structure’s ?Following are the key differences between them :-
√ Structures are value types and classes are reference types. So structures use stack and classes use heap. √ Structures members can not be declared as protected, but class members can be. You can not do inheritance in structures. √ Structures do not require constructors while classes require. √ Objects created from classes are terminated using Garbage collector. Structures are not destroyed using GC. Struct “do not support” inheritance, while class supports inheritance. However struct can implements interface. Struct should be used when you want to use a small data structure, while Class is better choice for complex data structure.
What are different properties provided by Object-oriented systems ?Twist :- Can you explain different properties of Object Oriented Systems? Note:- Difference between abstraction and encapsulation is one of the favorite interview question and quiet confusing as both the terminology look alike. Best is if you can brainstorm with your friends or do a little reading. Following are characteristic’s of Object Oriented System’s :- Abstraction It allows complex real world to be represented in simplified manner. Example color is abstracted to RGB. By just making the combination of these three colors we can achieve any color in world. It’s a model of real world or concept.
Encapsulation It is a process of hiding all the internal details of an object from the outside world. Wrapping up of data and function into a single unit is known as Encapsulation.
Communication using messages When application wants to achieve certain task it can only be done using combination of objects. A single object can not do all the task. Example if we want to make order processing form.We will use Customer object, Order object, Product object and Payment object to achieve this functionality. In short these objects should communicate with each other. This is achieved when objects send messages to each other.
Object lifetime All objects have life time.Objects are created ,and initialized, necessary functionalities are done and later the object is destroyed. Every object have there own state and identity which differ from instance to instance.
Class hierarchies (Inheritance and aggregation)Twist :- What is difference between Association, Aggregation and Inheritance relationships?
In object oriented world objects have relation and hierarchies in between them. There are basically three kind of relationship in Object Oriented world :- Association This is the simplest relationship between objects. Example every customer has sales. So Customer object and sales object have an association relation between them.
Aggregation This is also called as composition model. Example in order to make a “Accounts” class it has use other objects example “Voucher”, “Journal” and “Cash” objects. So accounts class is aggregation of these three objects.
Inheritance Hierarchy is used to define more specialized classes based on a preexisting generalized class. Example we have VEHICLE class and we can inherit this class make more specialized class like CAR, which will add new attributes and use some existing qualities of the parent class. Its shows more of a parent-child relationship. This kind of hierarchy is called inheritance.
Polymorphism When inheritance is used to extend a generalized class to a more specialized class, it includes behavior of the top class(Generalized class). The inheriting class often implement a behavior that can be somewhat different than the generalized class, but the name of the behavior can be same. It is important that a given instance of an object use the correct behavior, and the property of polymorphism allows this to happen automatically. What is the difference between instantiating structures with and without using the new keyword?
Sql FaqsHow to import table using “INSERT” statement? Insert INTO Departments ( DepartmentID, DepartmentName, DepartmentHeadID ) SELECT * FROM Departments;
What is DML,DDL,DCL and TCL? DML DDL DCL TCL What is cross-join? Cross Joins produce results that consist of every combination of rows from two or more tables. That means if table A has 3 rows and table B has 2 rows, a CROSS JOIN will result in 6 rows. There is no relationship established between the two tables – you literally just produce every possible combination. Eg:SELECT S.Store, P.Product FROM Stores S CROSS JOIN Products P How will you select the first record in a given set of rows? select top 1 * from ratingdetail where ratingdetailid in(select ratingdetailid from ratingdetail where ratingdetailid>3) How do you sort in SQL? Using Order by ASC or DESC SELECT columns FROM tables WHERE predicates ORDER BY column ASC/DESC;
1)What is difference between Oracle and MS Access? What is database? What is cluster.cluster index and non cluster index ? How can i hide a particular table name of our schema? e.g) you can create a synonym y for table x create synonym y for x; What is difference between DBMS and RDBMS? RDBMS have Normalization. Normalization means to refining the redundant and maintain the stablization. What are the advantages and disadvantages of primary key and foreign key in SQL? Primary key Advantages 1) It is a unique key on which all the other candidate keys are functionally dependent Disadvantage 1) There can be more than one keys on which all the other attributes are dependent on. Foreign Key Advantage 1)It allows refrencing another table using the primary key for the other table Which date function is used to find the difference between two dates? for Eg: select datediff (dd,'2-06-2007','7-06-2007') output is 5 What is the use of Group by? In cases such as finding the average salary of each department What is “COMPUTE” clause in SQL? Generates totals that appear as additional summary columns at the end of the result set. When used with BY, the COMPUTE clause generates control-breaks and subtotals in the result set. You can specify COMPUTE BY and COMPUTE in the same query.
Eg: select * from achievements order by achievementid compute sum(appuserid) by achievementid produces
What is “WITH TIES” clause in SQL? 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. To see what this means in practice, execute the following query against the Pubs database in SQL Server 7.0: SELECT TOP 5 price, title FROM titles ORDER BY price DESC The last title (it should be "The Busy Executive's Database Guide") has a price tag of $19.99. The Titles table, however, contains two more books with the same price, but they are ignored by the TOP clause. To see them you must add the WITH TIES clause, as in: SELECT TOP 5 WITH TIES price, title FROM titles ORDER BY price DESC The same rule applies to the TOP N PERCENT clause. Try executing the following query with and without the WITH TIES clause: SELECT TOP 25 PERCENT WITH TIES price, title FROM titles ORDER BY price DESC
What is the SET ROWCOUNT?
SET ROWCOUNT stops processing after the specified number of rows. In the following example, note that 545 rows meet the criteria of Quantity less than 300. However, from the number of rows returned by the update, you can see that not all rows were processed. ROWCOUNT affects all Transact-SQL statements. GO
SELECT count(*) AS Count FROM Production.ProductInventory WHERE Quantity < 300; GO Here is the result set. Now, set ROWCOUNT to 4 and update all rows with a Quantity less than 300.
SET ROWCOUNT 4; UPDATE Production.ProductInventory SET Quantity = 400 WHERE Quantity < 300; GO (4 row(s) affected)
EXISTS
The EXISTS construct is a TSQL function that returns a Boolean value based upon the subquery inside of the parentheses. If the subquery returns any rows, the construct returns true; otherwise, it will return false. This construct is great for setting true or false variables that depend on the existence of records in a table. It is also good for determining whether to run an update or insert statement against tables SELECT * FROM SalesHistory WHERE EXISTS ( SELECT AVG(SalePrice) FROM SalesHistory shi HAVING AVG(SalePrice) > sh.SalePrice )
SELECT * FROM SalesHistory WHERE SalePrice <(SELECT AVG(SalePrice) FROM SalesHistory)
IF EXISTS ( SELECT * FROM SalesHistory WHERE SaleDate = '1/1/2001' AND Product = 'Computer' AND SalePrice = 1000 ) UPDATE TOP(1) SalesHistory SET SalePrice = 1100 WHERE SaleDate = '1/1/2001' AND Product = 'Computer' AND SalePrice = 1000 ELSE INSERT INTO SalesHistory (Product, SaleDate, SalePrice) VALUES ('Computer', '1/1/2001', 1100) Explain constraints? Using Microsoft's SQL Server CHECK, DEFAULT, NULL, and UNIQUE constraints to maintain database Domain, Referential, and Entity integrity. The primary job of a constraint is to enforce a rule in the database. Together, the constraints in a database maintain the integrity of the database. For instance, we have foreign key constraints to ensure all orders reference existing products. You cannot enter an order for a product the database does not know about.
What are the diffferent integrity options in sql? Entity IntegrityEntity integrity ensures each row in a table is a uniquely identifiable entity. You can apply entity integrity to a table by specifying a PRIMARY KEY constraint. For example, the ProductID column of the Products table is a primary key for the table. Referential IntegrityReferential integrity ensures the relationships between tables remain preserved as data is inserted, deleted, and modified. You can apply referential integrity using a FOREIGN KEY constraint. The ProductID column of the Order Details table has a foreign key constraint applied referencing the Orders table. The constraint prevents an Order Detail record from using a ProductID that does not exist in the database. Also, you cannot remove a row from the Products table if an order detail references the ProductID of the row. Entity and referential integrity together form key integrity. Domain IntegrityDomain integrity ensures the data values inside a database follow defined rules for values, range, and format. A database can enforce these rules using a variety of techniques, including CHECK constraints, UNIQUE constraints, and DEFAULT constraints.
Explain unique constraint?
a unique constraint uses an index to ensure a column (or set of columns) contains no duplicate values Unique constraint can be created in one of the following three ways 1. In field CREATE TABLE Products ( ProductID int PRIMARY KEY, ProductName nvarchar (40) Constraint IX_ProductName UNIQUE ) 2. In table CREATE TABLE Products ( ProductID int PRIMARY KEY, ProductName nvarchar (40), CONSTRAINT IX_ProductName UNIQUE(ProductName) ) 3. changing a table
ALTER TABLE Products_2 ADD CONSTRAINT IX_ProductName UNIQUE (ProductName)
Explain check constraints? Check constraints contain an expression the database will evaluate when you modify or insert a row. If the expression evaluates to false, the database will not save the row CREATE TABLE Products ( ProductID int PRIMARY KEY, UnitPrice money CHECK(UnitPrice > 0 AND UnitPrice < 100) ) Building a check constraint is similar to building a WHERE clause. You can use many of the same operators (>, <, <=, >=, <>, =) in additional to BETWEEN, IN, LIKE, and NULL. Restrictions On Check ConstraintsAlthough check constraints are by far the easiest way to enforce domain integrity in a database, they do have some limitations, namely: A check constraint cannot reference a different row in a table. A check constraint cannot reference a column in a different table. Explain NULL constraint? Using SQL you can use NULL or NOT NULL on a column definition to explicitly set the nullability of a column CREATE TABLE Employees_2 ( EmployeeID int PRIMARY KEY, FirstName varchar(50) NULL, LastName varchar(50) NOT NULL, ) Explain Default ConstraintsDefault constraints apply a value to a column when an INSERT statement does not specify the value for the column. CREATE TABLE Orders (
OrderDate datetime NULL DEFAULT(GETDATE()), Freight money NULL DEFAULT (0) CHECK(Freight >= 0), ShipAddress nvarchar (60) NULL DEFAULT('NO SHIPPING ADDRESS'), EnteredBy nvarchar (60) NOT NULL DEFAULT(SUSER_SNAME()) ) Sql FaqsWhy is a UNION ALL faster than a UNION? UNION ALL faster than a UNION because for union operation server needs to remove the duplicate values but for union all its not. Thats why the UNOIN ALL is fater than UNION Operation. It is recommended that if you know that the union set operation never returns duplicate values than you must use UNION ALL instead of UNION. How many types of data models are there? There are no standards in this area. Authors and theorists make it up as they go. The entity-relationship model (ER) has hundreds of derivitives (bachman, chen, ibm, IDEF1x etc.). the most popular of the OO models is Unified Modeling Language (UML). Actually UML and IDEF1x are closest to becoming a standard that can support software products. Rational already has products and IDEF1x is the language of ERwin. What is denormalization and when would you go for it? As the name indicates, denormalization is the reverse process of normalization. It's the controlled introduction of redundancy in to the database design. It helps improve the query performance as the number of joins could be reduced.
What's the difference between a primary key and a unique key? 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. Another major difference is that, primary key doesn't allow NULLs, but unique key allows one NULL only. 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. What are defaults? Is there a column to which a default can't be bound? Whar is an index? What are the types of indexes? How many clustered indexes can be created on a table? I create a separate index on each column of a table. what are the advantages and disadvantages of this approach? Indexes in SQL Server are similar to the indexes in books. They help SQL Server retrieve the data quicker. Indexes are of two types. Clustered indexes and non-clustered indexes. When you craete a clustered index on a table, all the rows in the table are stored in the order of the clustered index key. So, there can be only one clustered index per table. Non-clustered indexes have their own storage separate from the table data storage. Non-clustered indexes are stored as B-tree structures (so do clustered indexes), with the leaf level nodes having the index key and it's row locater. The row located could be the RID or the Clustered index key, depending up on the absence or presence of clustered index on the table. If you create an index on each column of a table, it improves the query performance, as the query optimizer can choose from all the existing indexes to come up with an efficient execution plan. At the same t ime, data modification operations (such as INSERT, UPDATE, DELETE) will become slow, as every time data changes in the table, all the indexes need to be updated. Another disadvantage is that, indexes need disk space, the more indexes you have, more disk space is used. What are cursors? Explain different types of cursors. What are the disadvantages of cursors? How can you avoid cursors? Cursors allow row-by-row prcessing of the resultsets. Types of cursors: Static, Dynamic, Forward-only, Keyset-driven. See books online for more information. Disadvantages of cursors: Each time you fetch a row from the cursor, it results in a network roundtrip, where as a normal SELECT query makes only one rowundtrip, however large the resultset is. Cursors are also costly because they require more resources and temporary storage (results in more IO operations). Furthere, there are restrictions on the SELECT statements that can be used with some types of cursors. Most of the times, set based operations can be used instead of cursors. Here is an example: If you have to give a flat hike to your employees using the following criteria: Salary between 30000 and 40000 -- 5000 hike In this situation many developers tend to use a cursor, determine each employee's salary and update his salary according to the above formula. But the same can be achieved by multiple update statements or can be combined in a single UPDATE statement as shown below: UPDATE tbl_emp SET salary =
Another situation in which developers tend to use cursors: You need to call a stored procedure when a column in a particular row meets certain condition. You don't have to use cursors for this. This can be achieved using WHILE loop, as long as there is a unique key to identify each row. For examples of using WHILE loop for row by row processing,
What is a join and explain different types of joins? Joins are used in queries to explain how different tables are related. Joins also let you select data from a table depending upon data from another table. Types of joins: INNER JOINs, OUTER JOINs, CROSS JOINs. OUTER JOINs are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS.
What is a Stored Procedure? Its nothing but a set of T-SQL statements combined to perform a single task of several tasks. Its basically like a Macro so when you invoke the Stored procedure, you actually run a set of statements.
What is the basic difference between clustered and a non-clustered index? The difference is that, Clustered index is unique for any given table and we can have only one clustered index on a table. The leaf level of a clustered index is the actual data and the data is resorted in case of clustered index. Whereas in case of non-clustered index the leaf level is actually a pointer to the data in rows so we can have as many non-clustered indexes as we can on the db. What are cursors? Well cursors help us to do an operation on a set of data that we retreive by commands such as Select columns from table. For example : If we have duplicate records in a table we can remove it by declaring a cursor which would check the records during retreival one by one and remove rows which have duplicate values.
What are local and global cursors?
LOCAL - specifies that cursor can be available only in the batch, stored procedure, or trigger in which the cursor was created. The LOCAL cursor will be implicitly deallocated when the batch, stored procedure, or trigger terminates.
Which TCP/IP port does SQL Server run on? SQL Server runs on port 1433 but we can also change it for better security. Can we use Truncate command on a table which is referenced by FOREIGN KEY? No. We cannot use Truncate command on a table with Foreign Key because of referential integrity. What is the use of DBCC commands? DBCC stands for database consistency checker. We use these commands to check the consistency of the databases, i.e., maintenance, validation task and status checks. What is the difference between a HAVING CLAUSE and a WHERE CLAUSE? Having Clause is basically used only with the GROUP BY function in a query. WHERE Clause is applied to each row before they are part of the GROUP BY function in a query. What is a Linked Server? Linked Servers is a concept in SQL Server by which we can add other SQL Server to a Group and query both the SQL Server dbs using T-SQL Statements. Can you link only other SQL Servers or any database servers such as Oracle? We can link any server provided we have the OLE-DB provider from Microsoft to allow a link. For Oracle we have a OLE-DB provider for oracle that microsoft provides to add it as a linked server to the sql server group. How do you troubleshoot SQL Server if its running very slow? First check the processor and memory usage to see that processor is not above 80% utilization and memory not above 40-45% utilization then check the disk utilization using Performance Monitor, Secondly, use SQL Profiler to check for the users and current SQL activities and jobs running which might be a problem. Third would be to run UPDATE_STATISTICS command to update the indexes. What is log shipping? Can we do logshipping with SQL Server 7.0 - Logshipping is a new feature of SQL Server 2000. We should have two SQL Server - Enterprise Editions. From Enterprise Manager we can configure the logshipping. In logshipping the transactional log file from one server is automatically updated into the backup database on the other server. If one server fails, the other server will have the same db and we can use this as the DR (disaster recovery) plan. Let us say the SQL Server crashed and you are rebuilding the databases including the master database what procedure to you follow? For restoring the master db we have to stop the SQL Server first and then from command line we can type SQLSERVER .m which will basically bring it into the maintenance mode after which we can restore the master db. What is BCP? When do we use it? BulkCopy is a tool used to copy huge amount of data from tables and views. But it won’t copy the structures of the same.
What is the difference between oracle,sql and sql server ? · Oracle is based on RDBMS. · SQL is Structured Query Language. · SQL Server is another tool for RDBMS provided by MicroSoft. why you need indexing ? where that is stroed and what you mean by schema object? For what purpose we are using view? We cant create an Index on Index.. Index is stoed in user_index table.Every object that has been created on Schema is Schema Object like Table,View etc.If we want to share the particular data to various users we have to use the virtual table for the Base table...So tht is a view. indexing is used for faster search or to retrieve data faster from various table. Schema containing set of tables, basically schema means logical separation of the database. View is crated for faster retrieval of data. It's customized virtual table. we can create a single view of multiple tables. Only the drawback is..view needs to be get refreshed for retrieving updated data. Difference between Store Procedure and Trigger? · we can call stored procedure explicitly. · but trigger is automatically invoked when the action defined in trigger is done. · this trigger invoked after we insert something on that table. · Stored procedure can't be inactive but trigger can be Inactive. · Triggers are used to initiate a particular activity after fulfilling certain condition.It need to define and can be enable and disable according to need.
What is the advantage to use trigger in your PL? Triggers are fired implicitly on the tables/views on which they are created. There are various advantages of using a trigger. Some of them are: · Suppose we need to validate a DML statement(insert/Update/Delete) that modifies a table then we can write a trigger on the table that gets fired implicitly whenever DML statement is executed on that table. · Another reason of using triggers can be for automatic updation of one or more tables whenever a DML/DDL statement is executed for the table on which the trigger is created. · Triggers can be used to enforce constraints. For eg : Any insert/update/ Delete statements should not be allowed on a particular table after office hours. For enforcing this constraint Triggers should be used. · Triggers can be used to publish information about database events to subscribers. Database event can be a system event like Database startup or shutdown or it can be a user even like User loggin in or user logoff. What the difference between UNION and UNIONALL? Union will remove the duplicate rows from the result set while Union all does'nt. What is the difference between TRUNCATE and DELETE commands? Both will result in deleting all the rows in the table .TRUNCATE call cannot be rolled back as it is a DDL command and all memory space for that table is released back to the server. TRUNCATE is much faster.Whereas DELETE call is an DML command and can be rolled back. Which system table contains information on constraints on all the tables created ? Explain normalization ? How to find out the database name from SQL*PLUS command prompt? What is the difference between SQL and SQL Server ? SQLServer is an RDBMS just like oracle,DB2 from Microsoft What is diffrence between Co-related sub query and nested sub query? Correlated subquery runs once for each row selected by the outer query. It contains a reference to a value from the row selected by the outer query. Nested subquery runs only once for the entire nesting (outer) query. It does not contain any reference to the outer query row. For example, Correlated Subquery: select e1.empname, e1.basicsal, e1.deptno from emp e1 where e1.basicsal = (select max(basicsal) from emp e2 where e2.deptno = e1.deptno) Nested Subquery: select empname, basicsal, deptno from emp where (deptno, basicsal) in (select deptno, max(basicsal) from emp group by deptno) WHAT OPERATOR PERFORMS PATTERN MATCHING? 1. % and 2. _ ( underscore ) % means matches zero or more characters and under score means mathing exactly one character Sql FaqsSQL SELECT statement - short summary
Sql FaqsWrite the SQL statement returning the duplicate records in a database A. SELECT Name, ID FROM TableName GROUP BY Name, ID HAVING COUNT (*) > 1 Eg: SELECT achievementid,sum(appuserid) FROM achievements GROUP BY achievementid HAVING COUNT (*) > 1 How do you select unique rows using SQL? Select distinct.
Can you name some aggregate function is SQL Server? Sum,avg,count,max,min What is the default “SORT” order for a SQL? Ascending Wild cards in SQL Server
What is the Difference between DBMS and RDBMS?
A DBMS has to be persistent, that is it should be accessible when the program created the data ceases to exist or even the application that created the data restarted. A DBMS also has to provide some uniform methods independent of a specific application for accessing the information that is stored. What are codd rules? Codd's 12 rules are a set of 12 rules proposed by Edgar F. Codd, a pioneer of the relational model for databases, designed to define what is required from a database management system in order for it to be considered relational, i.e., an RDBMS. Codd produced these rules as part of a personal campaign to prevent his vision of the relational database being diluted, as database vendors scrambled in the early 1980s to repackage existing products with a relational veneer. Rule 12 was particularly designed to counter such a positioning. In fact, the rules are so strict that all popular so-called "relational" DBMSs fail on many of the criteria.[citation needed]
Rule 000: The system must qualify as relational, as a database, and as a management system. For a system to qualify as a relational database management system (RDBMS), that system must use its relational facilities (exclusively) to manage the database. Rule 1: The information rule: All information in the database is to be represented in one and only one way, namely by values in column positions within rows of tables. Rule 2: The guaranteed access rule: All data must be accessible with no ambiguity. This rule is essentially a restatement of the fundamental requirement for primary keys. It says that every individual scalar value in the database must be logically addressable by specifying the name of the containing table, the name of the containing column and the primary key value of the containing row. Rule 3: Systematic treatment of null values: The DBMS must allow each field to remain null (or empty). Specifically, it must support a representation of "missing information and inapplicable information" that is systematic, distinct from all regular values (for example, "distinct from zero or any other number," in the case of numeric values), and independent of data type. It is also implied that such representations must be manipulated by the DBMS in a systematic way. Rule 4: Active online catalog based on the relational model: The system must support an online, inline, relational catalog that is accessible to authorized users by means of their regular query language. That is, users must be able to access the database's structure (catalog) using the same query language that they use to access the database's data. Rule 5: The comprehensive data sublanguage rule: The system must support at least one relational language that (a) Has a linear syntax (b) Can be used both interactively and within application programs, (c) Supports data definition operations (including view definitions), data manipulation operations (update as well as retrieval), security and integrity constraints, and transaction management operations (begin, commit, and rollback). Rule 6: The view updating rule: All views that are theoretically updatable must be updatable by the system.
Rule 7: High-level insert, update, and delete: The system must support set-at-a-time insert, update, and delete operators. This means that data can be retrieved from a relational database in sets constructed of data from multiple rows and/or multiple tables. This rule states that insert, update, and delete operations should be supported for any retrievable set rather than just for a single row in a single table. Rule 8: Physical data independence: Changes to the physical level (how the data is stored, whether in arrays or linked lists etc.) must not require a change to an application based on the structure. Rule 9: Logical data independence: Changes to the logical level (tables, columns, rows, and so on) must not require a change to an application based on the structure. Logical data independence is more difficult to achieve than physical data independence. Rule 10: Integrity independence: Integrity constraints must be specified separately from application programs and stored in the catalog. It must be possible to change such constraints as and when appropriate without unnecessarily affecting existing applications. Rule 11: Distribution independence: The distribution of portions of the database to various locations should be invisible to users of the database. Existing applications should continue to operate successfully : (a) when a distributed version of the DBMS is first introduced; and (b) when existing distributed data are redistributed around the system. Rule 12: The nonsubversion rule: If the system provides a low-level (record-at-a-time) interface, then that interface cannot be used to subvert the system, for example, bypassing a relational security or integrity constraint.
What is Entity-Relationship Diagram"?An entity-relationship (ER) diagram is a specialized graphic that illustrates the interrelationships between entities in a database. ER diagrams often use symbols to represent three different types of information. Boxes are commonly used to represent entities. Diamonds are normally used to represent relationships and ovals are used to represent attributes.
What are pages and extents? The fundamental unit of data storage in SQL Server is the page. The disk space allocated to a data file (.mdf or .ndf) in a database is logically divided into pages numbered contiguously from 0 to n. Disk I/O operations are performed at the page level. That is, SQL Server reads or writes whole data pages. Extents are a collection of eight physically contiguous pages and are used to efficiently manage the pages. All pages are stored in extents.
What are page splits? What are the four system databases in each instance of sql server? Each instance of SQL Server has four system databases (master, model, tempdb, and msdb) In which files does actually SQL Server store data? SQL Server databases have three types of files: * Primary data files The primary data file is the starting point of the database and points to the other files in the database. Every database has one primary data file. The recommended file name extension for primary data files is .mdf.
* Secondary data files Secondary data files comprise all of the data files other than the primary data file. Some databases may not have any secondary data files, while others have multiple secondary data files. The recommended file name extension for secondary data files is .ndf. * Log files Log files hold all of the log information used to recover the database. There must be at least one log file for each database, although there can be more than one. The recommended file name extension for log files is .ldf.
These are examples of the logical file names and physical file names of a database created on a default instance of SQL Server:
What is collation? Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case-sensitivity, accent marks, kana character types and character width.
What is grant and revoke? Since more than one user can access one database, there exists the need to restrict user from using entire database in any way. Setting up access rights can do this. Any right can be granted to or revoked from user with taken of the GRANT and REVOKE statements. Each right can be granted or revoked individually or in a group. Such group of rights is called ROLE and rights can be granted or revoked into in the same way as to the user Examples the CD catalog mainly contains albums of the rock'n'roll king Elvis Presley. He, as the king, should have adequate rights. Give them to him; his user name is Elvis. GRANT SELECT, INSERT, UPDATE, DELETE ON album, song TO Elvis WITH ADMIN OPTION Because the database may be accessed by a wide public, grant them only the SELECT statement. REVOKE INSERT, UPDATE, DELETE ON album, song TO PUBLIC Create a group of users, which can add and modify records, but not delete them. CREATE ROLE editors GRANT SELECT, INSERT UPDATE ON album, song TO editors You have got new guy in your team and you want to authorize him to delete records. His user name is JBlack. Take an advantage of the example #18. GRANT editors, DELETE ON album, song TO JBlack Once again you are alone to maintain the CD catalog, because all your co-workers are gone. Only the editors role is left and useless. Delete it. DROP ROLE editors What is Cascade and Restrict in DROP table SQL? The DROP TABLE Statement removes a previously created table and its description from the catalog. It has the following general format: DROP TABLE table-name {CASCADE|RESTRICT} table-name is the name of an existing base table in the current schema. The CASCADE and RESTRICT specifiers define the disposition of other objects dependent on the table. A base table may have two types of dependencies: * A view whose query specification references the drop table. * Another base table that references the drop table in a constraint - a CHECK constraint or REFERENCES constraint. RESTRICT specifies that the table not be dropped if any dependencies exist. If dependencies are found, an error is returned and the table isn't dropped. ADO .NET Faqs 2What is a command object?A command is, in its simplest form, a string of text containing SQL statements that is to be issued to the database. A command could also be a stored procedure, or the name of a table that will return all columns and all rows from that table (in other words, a SELECT *-style clause). string select = "SELECT ContactName,CompanyName FROM Customers";SqlConnection conn = new SqlConnection(source);conn.Open();SqlCommand cmd = new SqlCommand(select, conn); There are three types of Command Objects
TableDirect command type is only valid for the OleDb provider; other providers will throw an exception if you attempt to use this command type with them.
What important methods are available to execute commands?
What is the role of the DataReader class in ADO.NET connections?It returns a read-only, forward-only rowset from the data source. A DataReader provides fast access when a forward-only sequential read is needed. How is data stored in a datareader? As objects. They have to be explicitly cast to assign the values to some basic datatypes.
How to make the datareader return values of basic datatypes? Which is better?
GetInt32, GetFloat, GetGuid. Getting by the numeric value is faster, but getting by the “Get..” functions are more readable and maintainable.
What is the use of dataadapter class?
Used as a bridge between the database and dataset. It can populate dataset from database, and can update a database with the values in a dataset string select = "SELECT ContactName,CompanyName FROM Customers";SqlConnection conn = new SqlConnection(source);SqlDataAdapter da = new SqlDataAdapter(select , conn);DataSet ds = new DataSet();
da.Fill(ds , "Customers");
How will you use sqlscommand object and sqldataadapter to populate a dataset from a SP?
private static SqlCommand GenerateSelectCommand(SqlConnection conn ){return aCommand;}
This method generates the SqlCommand that calls the RegionSelect procedure when executed. All that remains is to hook up this command to a SqlDataAdapter class, and call the Fill() method: SqlCommand aCommand = new SqlCommand("RegionSelect" , conn);aCommand.CommandType = CommandType.StoredProcedure; DataSet ds = new DataSet();SqlDataAdapter da = new SqlDataAdapter();da.SelectCommand = aCommand;da.Fill(ds , "Region");
How do you populate a dataset from XML? DataSet ds = new DataSet();
ds.ReadXml(".\\MyData.xml");
How can you insert a new row in a datatable? 1. DataRow r = ds.Tables["Region"].NewRow();r["RegionID"]=999;r["RegionDescription"]="North West";ds.Tables["Region"].Rows.Add(r); DataRow r = ds.Tables["Region"].Rows.Add (new object [] { 999 , "North West" });
After the row is added, we can update the database using da.Update(ds , "Region"); which tells the adapter to update the Region table in the database with the updated values in the ds dataset. Suppose you have deleted a row in a datarow using r.Delete().Can we access this row before it is being updated in the database? No.
What are advantages and disadvantages of Microsoft-provided data provider classes in ADO.NET?SQLServer.NET data provider is high-speed and robust, but requires SQL Server license purchased from Microsoft. OLE-DB.NET is universal for accessing other sources, like Oracle, DB2, Microsoft Access and Informix. OLE-DB.NET is a .NET layer on top of the OLE layer, so it’s not as fastest and efficient as SqlServer.NET. What connections does Microsoft SQL Server support? Windows Authentication (via Active Directory) and SQL Server authentication (via Microsoft SQL Server username and password). Between Windows Authentication and SQL Server Authentication, which one is trusted and which one is untrusted?
1. What does the Initial Catalog parameter define in the connection string?
|
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|
