SQL Flashcards

Question

What are primary keys and foreign keys?

Answer 1

Primary keys are the unique identifiers for each row. They must contain unique values and cannot be null. Due to their importance in relational databases, Primary keys are the most fundamental of all keys and constraints. A table can have only one Primary key. Foreign keys are both a method of ensuring data integrity and a manifestation of the relationship between tables.

Answer 2

Underlying the structure of the database is called as data model.

Answer 3

It is a 'thing' in the real world with an independent existence

Answer 4

BulkCopy is a tool used to copy huge amount of data from tables and views. BCP does not copy the structures same as source to destination.

Answer 5

The GROUP BY partitions the selected rows on the distinct values of the column on which the group by has been done. The HAVING selects groups which match the criteria specified.

Answer 6

According to Bill Inmon, known as father of Data warehousing. “A Data warehouse is a subject oriented, integrated ,time variant, non volatile collection of data in support of management’s decision making process”.

Answer 7

``` ■Redundancy can be reduced ■Inconsistence can be avoided ■The data can be shared ■Standards can be enforced ■Security can be enforced ■Integrity can be maintained ```

Answer 8

The advantages of SQL are ■SQL is a high level language that provides a greater degree of abstraction than procedural languages. ■SQL enables the end users and system personnel to deal with a number of Database management systems where it is available. ■Application written in SQL can be easily ported across systems.

Answer 9

Outer joins return all rows from at least one of the tables or views mentioned in the FROM clause, as long as those rows meet any WHERE or HAVING search conditions. A join combines columns and data from two are more tables.

Answer 10

A relation is said to be in Boyce coded normal form if it is already in the third normal form and every determine is a candidate key.

Answer 11

■Atomicity ■Consistency ■Isolation ■Durability

Answer 12

Data mining refers to using variety of techniques to identify nuggests of information or decision making knowledge in bodies of data and extracting these in such a way that they can be put in the use in the areas such as decision support, predication, forecasting and estimation.

Answer 13

DBMS consists of a collection of interrelated data and a set of programs to access that data. The object oriented DBMS is one of the type of dbms in which information is stored in the form of objects.

Answer 14

■Data Definition Language (DDL) ■Data Manipulation Language (DML) ■Data Query Language (DQL) ■Data Control Language (DCL)

Answer 15

An entity is represented by a set of attributes. Attributes are descriptive properties possessed by each member of an entity set. There are different types of attributes. ■Simple ■Composite ■Single-valued ■Derived

Answer 16

``` ■Entity relationship model ■Relational model ■Hierarchical model ■Network model ■Object oriented model ■Object relational model ```

Answer 17

Active database is a database that includes active rules, mostly in the form of ECA rules(Event Condition rules). Active database systems enhance traditional database functionality with powerful rule processing cabalities, providing a uniform and efficient mechanism for database system applications.

Answer 18

■Atomicity ■Consistency ■Isolation ■Durability

Answer 19

A tuple is an instance of data within a relational database.

Answer 20

They are SQL statements that are embedded with in application program and are prepared during the program preparation process before the program is executed. After it is prepared, the statement itself does not change(although values of host variables specified within the statement might change).

Answer 21

A Functional dependency is denoted by X Y between two sets of attributes X and Y that are subsets of R specifies a constraint on the possible tuple that can form a relation state r of R. The constraint is for any two tuples t1 and t2 in r if t1[X] = t2[X] then they have t1[Y] = t2[Y]. This means the value of X component of a tuple uniquely determines the value of component Y.

Answer 22

The different phases of transaction are■Analysis phase ■Redo Phase ■Undo phase

Answer 23

Union will remove the duplicate rows from the result set while Union all does’nt.

Answer 24

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.

Answer 25

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. Basically, the Database Consistency Checker (DBCC) provides a set of commands (many of which are undocumented) to maintain databases — maintenance, validation, and status checks. The syntax is DBCC followed by the command name. Here are three examples: DBCC CHECKALLOC — Check disk allocation consistency. DBCC OPENTRAN — Display information about recent transactions. DBCC HELP — Display Help for DBCC commands.

Answer 26

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. With a linked server, you can create very clean, easy to follow, SQL statements that allow remote data to be retrieved, joined and combined with local data.

Answer 27

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.

Answer 28

The different phases of transaction are ■Case sensitivity ■Accent sensitivity ■Kana Sensitivity ■Width sensitivity

Answer 29

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 What is the difference between a clustered and a nonclustered index? index by default. Another major difference is that, primary key doesn’t allow NULLs, but unique key allows one NULL only.

Answer 30

■UDF can be used in the SQL statements anywhere in the WHERE / HAVING / SELECT section where as Stored procedures cannot be. ■UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables. ■Inline UDF’s can be though of as views that take parameters and can be used in JOINs and other Rowset operations.

Answer 31

sp_renamedb “oldname” , “newname” If someone is using db it will not accept sp_renmaedb. In that case first bring db to single user using sp_dboptions. Use sp_renamedb to rename database. Use sp_dboptions to bring database to multi user mode.

Answer 32

BulkCopy is a tool used to copy huge amount of data from tables and views. BCP does not copy the structures same as source to destination.

Answer 33

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.

Answer 34

It is a program module that provides the interface between the low-level data stored in database, application programs and queries submitted to the system.

Answer 35

Stored procedures are database objects that perform a user defined operation. A stored procedure can have a set of compound SQL statements. A stored procedure executes the SQL commands and returns the result to the client. Stored procedures are used to reduce network traffic.

Answer 36

A database trigger is a PL/SQL block that can defined to automatically execute for insert, update, and delete statements against a table. The trigger can e defined to execute once for the entire statement or once for every row that is inserted, updated, or deleted.

Answer 37

Online Transaction Processing (OLTP) relational databases are optimal for managing changing data. When several users are performing transactions at the same time, OLTP databases are designed to let transactional applications write only the data needed to handle a single transaction as quickly as possible.

Answer 38

A data base schema is specifies by a set of definitions expressed by a special language called DDL.

Answer 39

An entity set may not have sufficient attributes to form a primary key, and its primary key compromises of its partial key and primary key of its parent entity, then it is said to be Weak Entity set.

Answer 40

Two processes wating to update the rows of a table which are locked by the other process then deadlock arises.

Answer 41

It is a database in which there are no programs or user access languages. It has no cross-file capabilities but is user-friendly and provides user-interface management.

Answer 42

It is a program module that provides the interface between the low-level data stored in database, application programs and queries submitted to the system.

Answer 43

An index is a physical structure containing pointers to the data. Indices are created in an existing table to locate rows more quickly and efficiently. It is possible to create an index on one or more columns of a table, and each index is given a name.

Answer 44

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 Nonclustered 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 nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.

Answer 45

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. WHERE Clause is applied to each row before they are part of the GROUP BY function in a query.

Answer 46

Log shipping is the process of automating the backup of database and transaction log files on a production SQL server, and then restoring them onto a standby server. Enterprise Editions only supports log shipping. In log shipping the transactional log file from one server is automatically updated into the backup database on the other server.

Answer 47

Primary keys are the unique identifiers for each row. They must contain unique values and cannot be null. Due to their importance in relational databases, Primary keys are the most fundamental of all keys and constraints. A table can have only one Primary key. Foreign keys are both a method of ensuring data integrity and a manifestation of the relationship between tables.

Answer 48

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.

Answer 49

There are two types of Synonyms are :■Private | ■Public

Answer 50

An integrity constraint is a declarative way to define a business rule for a column of a table.

Answer 51

A table is the basic unit of data storage in an ORACLE database. The tables of a database hold all of the user accessible data. Table data is stored in rows and columns.

Answer 52

A synonym is an alias for a table, view, sequence or program unit

Answer 53

A Database contains one or more Rollback Segments to temporarily store "undo" information.

Answer 54

A Commit makes permanent the changes resulting from all SQL statements in the transaction. The changes made by the SQL statements of a transaction become visible to other user sessions transactions that start only after transaction is committed.

Answer 55

A database instance (Server) is a set of memory structure and background processes that access a set of database files.

Answer 56

Roles are named groups of related privileges that are granted to users or other roles.

Answer 57

SQLPlus is an application that recognizes & executes SQL commands & specialized SQL*Plus commands that can customize reports, provide help & edit facility & maintain system variables.

Answer 58

Normalizing data means eliminating redundant information from a table and organizing the data so that future changes to the table are easier. Denormalization means allowing redundancy in a table. The main benefit of denormalization is improved performance with simplified data retrieval and manipulation.

Answer 59

Triggers are stored procedures created in order to enforce integrity rules in a database. A trigger is executed every time a data-modification operation occurs (i.e., insert, update or delete). Triggers are executed automatically on occurance of one of the data-modification operations.

Answer 60

Static SQL is hard-coded in a program when the programmer knows the statements to be executed. Dynamic SQL the program must dynamically allocate memory to receive the query results.

Answer 61

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.

Answer 62

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.

Answer 63

The phase that identifies an efficient execution plan for evaluating a query that has the least estimated cost is referred to as query optimization.

Answer 64

They are SQL statements that are embedded with in application program and are prepared during the program preparation process before the program is executed.

Answer 65

It is a program module, which manages the allocation of space on disk storage and data structure used to represent information stored on a disk.

Answer 66

A collection of operations that fom a single logical unit of works are called transaction.

Answer 67

Constraints is a rule or restriction concerning a piece of data that is enforced at the data level. A Constraint clause can constrain a single column or group of columns in a table. There are five types of Constraint namely ■ Null / Not Null ■ Primary Key ■ Unique ■Check or Validation ■Foreign Key or References Key

Answer 68

■Single-row subquery, where the subquery returns only one row. ■Multiple-row subquery, where the subquery returns multiple rows. ■ Multiple column subquery, where the subquery returns multiple columns.

Answer 69

SQL Profiler is a graphical tool that allows system administrators to monitor events in an instance of Microsoft SQL Server. You can capture and save data about each event to a file or SQL Server table to analyze later.

Answer 70

Clustering is a method of storing tables that are intimately related and often joined together into the same area on disk. A cluster contains one or more tables, which have one or more column in common among them.

Answer 71

Index is a general term for an Oracle/SQL features used to primarily to speed execution and imposes uniqueness upon certain data. The most important of an index is to ensure uniqueness of rows and help in speedy retrieval of data.

Answer 72

Data integrity is an important feature in SQL Server. When used properly, it ensures that data is accurate, correct, and valid. It also acts as a trap for otherwise undetectable bugs within applications.

Answer 73

De-normalization is the process of attempting to optimize the performance of a database by adding redundant data. De-normalization is a technique to move from higher to lower normal forms of database modeling in order to speed up database access.

Answer 74

Referential integrity refers to the consistency that must be maintained between primary and foreign keys, i.e. every foreign key value must have a corresponding primary key value.

Answer 75

Static SQL is hard-coded in a program when the programmer knows the statements to be executed. For dynamic SQL the program must dynamically allocate memory to receive the query results.

Answer 76

Unique key is a one or more column that must be unique for each row of the table. It is similar to primary key. Primary key column will not accept a null. Whereas the unique key column will accept a null values. `

Answer 77

Synonym is an alternative method to creating a view that includes the entire table or view from another user it to create a synonym. A synonym is a name assigned to a table or view that may thereafter be used to refer to it.

Answer 78

major purpose of a database system is to provide users with an abstract view of the data.There are three levels of data abstraction The are three levels of abstraction: Physical level: The lowest level of abstraction describes how data are stored. Logical level: The next higher level of abstraction, describes what data are stored in database and what relationship among those data. View level: The highest level of abstraction describes only part of entire database.

Answer 79

It is a program module, which ensures that database, remains in a consistent state despite system failures and concurrent transaction execution proceeds without conflicting.

Answer 80

There are three types of User-Defined functions in SQL Server 2000 and they are Scalar, Inline Table-Valued and Multi-statement Table-valued.

Answer 81

A default is a value that will be used by a column, if no value is supplied to that column while inserting data. IDENTITY columns and timestamp columns can't have defaults bound to them. See CREATE DEFUALT in books online.

Answer 82

8060 bytes. Don't be surprised with questions like what is the maximum number of columns per table. Check out SQL Server books online for the page titled: “Maximum Capacity Specifications”.

Answer 83

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. A Global temporary table remains in the database permanently, but the rows exist only within a given connection. When connection are closed, the data in the global temporary table disappears. However, the table definition remains with the database for access when database is opened next time.

Answer 84

A query with respect to DBMS relates to user commands that are used to interact with a data base. The query language can be classified into data definition language and data manipulation language.

Answer 85

It is procedural query language. It consists of a set of operations that take one or two relations as input and produce a new relation.

Answer 86

There are three levels of abstraction : ■Physical level : The lowest level of abstraction describes how data are stored. ■ Logical level: The next higher level of abstraction, describes what data are stored in database and what relationship among those data. ■View level: The highest level of abstraction describes only part of entire database.

Answer 87

Microsoft SQL Server 2000 Data Transformation Services (DTS) is a set of graphical tools and programmable objects that lets user extract, transform, and consolidate data from disparate sources into single or multiple destinations.

Answer 88

DESC has two purposes. | It is used to describe a schema as well as to retrieve rows from table in descending order.

Answer 89

The related columns of the tables are called the cluster key. The cluster key is indexed using a cluster index and its value is stored only once for multiple tables in the cluster.

Answer 90

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.

Answer 91

■Minimize redundancy in data. ■Remove insert, delete and update anamoly during the database activities. ■Reduce the need to reorganize data it is modified or enhanced. ■Normalisation reduces a complex user view to a set of small and stable subgroups of fields or relations.

Answer 92

RAID, an acronym for Redundant Array of Independent Disks (sometimes incorrectly referred to as Redundant Array of Inexpensive Disks), is a technology that provides increased storage functions and reliability through redundancy.

Answer 93

Replication is the process of copying / moving data between databases on the same or different servers.

Answer 94

Cursors allow row-by-row prcessing of the result sets.

Answer 95

The entity types that do not have key attributes of their own are called weak entity types. Rests are called strong entity types .The entity that gives identity to a weak entity is called owner entity. And the relationship is called identifying relationship. A weak entity type always has a total participation constraint with respect to its identifying relationship.

Answer 96

default is a value that will be used by a column, if no value is supplied to that column while inserting data. IDENTITY columns and timestamp columns can’t have defaults bound to them.

Answer 97

It is the process of defining a set of subclasses of an entity type where each subclass contain all the attributes and relationships of the parent entity and may have additional attributes and relationships which are specific to itself.

Answer 98

Types of cursors : ■Static ■Dynamic ■Forward-only ■Keyset-driven

Answer 99

A catalog is a table that contain the information such as structure of each file ,the type and storage format of each data item and various constraints on the data .The information stored in the catalog is called Metadata . Whenever a request is made to access a particular data, the DBMS s/w refers to the catalog to determine the structure of the file.

Answer 100

A view may be a subset of the database or it may contain virtual data that is derived from the database files but is not explicitly stored. A view may be thought of as a virtual table, that is, a table that does not really exist in its own right but is instead derived from one or more underlying base table. In other words, there is no stored file that direct represents the view instead a definition of view is stored in data dictionary. Growth and restructuring of base tables is not reflected in views. Thus the view can insulate users from the effects of restructuring and growth in the database. Hence accounts for logical data independence.

Answer 101

■Casual end-users ■Casual end-users ■Sophisticated end users ■Stand alone users

Answer 102

It is a collection of concepts that can be used to describe the structure of a database. It provides necessary means to achieve this abstraction. By structure of a database we mean the data types, relations, and constraints that should hold on the data.

Answer 103

■Internal schema ■Conceptual schema ■External schema

Answer 104

``` ■DBMS ■RDBMS (Relational) ■ORDBMS (Object Relational) ■DDBMS (Distributed) ■FDBMS (Federated) ■FDBMS (Federated) ■HDBMS (Hierarchical) ■NDBMS (Networked) ```

Answer 105

A lock is a variable associated with a data item that describes the status of the item with respect to the possible operations that can be applied to it.

Answer 106

Entity Integrity: States that "Primary key cannot have NULL value" Referential Integrity: States that "Foreign Key can be either a NULL value or should be Primary Key value of other relation.

Answer 107

Extension: It is the number of tuples present in a table at any instance. This is time dependent. Intension: It is a constant value that gives the name, structure of table and the constraints laid on it.

Answer 108

Data independence means that "the application is independent of the storage structure and access strategy of data". In other words, The ability to modify the schema definition in one level should not affect the schema definition in the next higher level. Two types of Data Independence: Physical Data Independence: Modification in physical level should not affect the logical level. Logical Data Independence: Modification in logical level should affect the view level. NOTE: Logical Data Independence is more difficult to achieve

Answer 109

This data model is based on real world that consists of basic objects called entities and of relationship among these objects. Entities are described in a database by a set of attributes.

Answer 110

This model is based on collection of objects. An object contains values stored in instance variables with in the object. An object also contains bodies of code that operate on the object. These bodies of code are called methods. Objects that contain same types of values and the same methods are grouped together into classes.

Answer 111

It is a collection (set) of entities that have same attributes.

Answer 112

It is a collection of all entities of particular entity type in the database.

Answer 113

The collections of entities of a particular entity type are grouped together into an entity set.

Answer 114

It is a particular property, which describes the entity.

Answer 115

A relation Schema denoted by R(A1, A2, ..., An) is made up of the relation name R and the list of attributes Ai that it contains. A relation is defined as a set of tuples. Let r be the relation which contains set tuples (t1, t2, t3, ..., tn). Each tuple is an ordered list of n-values t=(v1,v2, ..., vn).

Answer 116

It is an association among two or more entities.

Answer 117

The domain of attribute must include only atomic (simple, indivisible) values.

Answer 118

A relation schema R is in 2NF if it is in 1NF and every non-prime attribute A in R is fully functionally dependent on primary key.

Answer 119

A relation schema R is in 3NF if it is in 2NF and for every FD X A either of the following is true 1. X is a Super-key of R. 2. A is a prime attribute of R. In other words, if every non prime attribute is non-transitively dependent on primary key.

Answer 120

A relation schema R is in BCNF if it is in 3NF and satisfies an additional constraint that for every FD X A, X must be a candidate key.

Answer 121

A relation schema R is said to be in 4NF if for every Multivalued dependency X Y that holds over R, one of following is true. 1. ) X is subset or equal to (or) XY = R. 2. ) X is a super key.

Answer 122

A Relation schema R is said to be 5NF if for every join dependency {R1, R2, ..., Rn} that holds R, one the following is true 1. ) Ri = R for some i. 2. ) The join dependency is implied by the set of FD, over R in which the left side is key of R.

Answer 123

Indexing is a technique for determining how quickly specific data can be found. Types: 1. Binary search style indexing 2. B-Tree indexing 3. Inverted list indexing 4. Memory resident table 5. Table indexing

Answer 124

A RDBMS maintains a description of all the data that it contains, information about every relation and index that it contains. This information is stored in a collection of relations maintained by the system called metadata. It is also called data dictionary.

Answer 125

Once the DBMS informs the user that a transaction has successfully completed, its effects should persist even if the system crashes before all its changes are reflected on disk. This property is called durability.

Answer 126

1. Atomicity: Either all actions are carried out or none are. Users should not have to worry about the effect of incomplete transactions. DBMS ensures this by undoing the actions of incomplete transactions. 2. Aggregation: A concept which is used to model a relationship between a collection of entities and relationships. It is used when we need to express a relationship among relationships.

Answer 127

In distributed deadlock detection, the delay in propagating local information might cause the deadlock detection algorithms to identify deadlocks that do not really exist. Such situations are called phantom deadlocks and they lead to unnecessary aborts.

Answer 128

A Checkpoint is like a snapshot of the DBMS state. By taking checkpoints, the DBMS can reduce the amount of work to be done during restart in the event of subsequent crashes.

Answer 129

Different phases are 1. ) Analysis phase, 2. ) Redo Phase, 3. ) Undo phase.

Answer 130

Subqueries, or nested queries, are used to bring back a set of rows to be used by the parent query. Depending on how the subquery is written, it can be executed once for the parent query or it can be executed once for each row returned by the parent query. If the subquery is executed for each row of the parent, this is called a correlated subquery. A correlated subquery can be easily identified if it contains any references to the parent subquery columns in its WHERE clause. Columns from the subquery cannot be referenced anywhere else in the parent query. The following example demonstrates a non-correlated subquery. Example: Select * From CUST Where '10/03/1990' IN (Select ODATE From ORDER Where CUST.CNUM = ORDER.CNUM)

Answer 131

Addition, deletion and modification.

Answer 132

PROJECTION and SELECTION.

Answer 133

Data dictionary is a set of tables and database objects that is stored in a special area of the database and maintained exclusively by the kernel.

Answer 134

The information in the data dictionary validates the existence of the objects, provides access to them, and maps the actual physical storage location.

Answer 135

It is a program module, which is responsible for fetching data from disk storage into main memory and deciding what data to be cache in memory.

Answer 136

It is a program module, which ensures that database, remains in a consistent state despite system failures and concurrent transaction execution proceeds without conflicting.

Answer 137

It is a program module, which manages the allocation of space on disk storage and data structure used to represent information stored on a disk.

Answer 138

Dynamic management views (DMVs) and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance; that is, they let you see what is going on inside SQL Server. They were introduced in SQL Server 2005 as an alternative to system tables. One example is viewing operating system wait statistics via this query: SELECT * FROM sys.dm_os_wait_stats;Another example is examining current sessions, much like the sp_who2 command: SELECT * FROM sys.dm_exec_sessions;

Answer 139

Temporary tables are temporary storage structures. You may use temporary tables as buckets to store data that you will manipulate before arriving at a final format. The hash (#) character is used to declare a temporary table as it is prepended to the table name. A single hash (#) specifies a local temporary table. CREATE TABLE #tempLocal ( nameid int, fname varchar(50), lname varchar(50) )Local temporary tables are available to the current connection for the user, so they disappear when the user disconnects. Global temporary tables may be created with double hashes (##). These are available to all users via all connections, and they are deleted only when all connections are closed. CREATE TABLE ##tempGlobal ( nameid int, fname varchar(50), lname varchar(50) )Once created, these tables are used just like permanent tables; they should be deleted when you are finished with them. Within SQL Server, temporary tables are stored in the Temporary Tables folder of the tempdb database.

Answer 140

A clustered index affects the way the rows of data in a table are stored on disk. When a clustered index is used, rows are stored in sequential order according to the index column value; for this reason, a table can contain only one clustered index, which is usually used on the primary index value. A nonclustered index does not affect the way data is physically stored; it creates a new object for the index and stores the column(s) designated for indexing with a pointer back to the row containing the indexed values. You can think of a clustered index as a dictionary in alphabetical order, and a nonclustered index as a book’s index.

Answer 141

Table hints allow you to override the default behavior of the query optimizer for statements. They are specified in the FROM clause of the statement. While overriding the query optimizer is not always suggested, it can be useful when many users or processes are touching data. The NOLOCK query hint is a good example because it allows you to read data regardless of who else is working with the data; that is, it allows a dirty read of data — you read data no matter if other users are manipulating it. A hint like NOLOCK increases concurrency with large data stores. SELECT * FROM table_name (NOLOCK)Microsoft advises against using NOLOCK, as it is being replaced by the READUNCOMMITTED query hint.

Answer 142

A common table expression (CTE) is a temporary named result set that can be used within other statements like SELECT, INSERT, UPDATE, and DELETE. It is not stored as an object and its lifetime is limited to the query. It is defined using the WITH statement as the following example shows: WITH ExampleCTE (id, fname, lname) AS ( SELECT id, firstname, lastname FROM table ) SELECT * FROM ExampleCTEA CTE can be used in place of a view in some instances.

Answer 143

A view is a virtual table that consists of fields from one or more real tables. Views are often used to join multiple tables or to control access to the underlying tables. The WITH CHECK OPTION for a view prevents data modifications (to the data) that do not confirm to the WHERE clause of the view definition. This allows data to be updated via the view, but only if it belongs in the view.

Answer 144

SQL Server has an optimizer that usually does a great job of optimizing code for the most effective execution. A query execution plan is the breakdown of how the optimizer will run (or ran) a query. There are several ways to view a query execution plan. This includes using the Show Execution Plan option within Query Analyzer; Display Estimated Execution Plan on the query dropdown menu; or use the SET SHOWPLAN_TEXT ON command before running a query and capturing the execution plan event in a SQL Server Profiler trace.

Answer 145

SQL Server Agent is a Windows service that handles scheduled tasks within the SQL Server environment (aka jobs). The jobs are stored/defined within SQL Server, and they contain one or more steps that define what happens when the job runs. These jobs may run on demand, as well as via a trigger or predefined schedule. This service is very important when determining why a certain job did not run as planned — often it is as simple as the SQL Server Agent service not running.

Answer 146

If enabled, the default instance of Microsoft SQL Server listens on TCP port 1433. Named instances are configured for dynamic ports, so an available port is chosen when SQL Server starts. When connecting to a named instance through a firewall, configure the Database Engine to listen on a specific port, so that the appropriate port can be opened in the firewall. The list of possible questions is endless. I am sure these questions will spawn debate and discussion.

Answer 147

You are looking for the applicant to make some reference regarding data manipulations. The more indexes on a table, the more time it takes for the database engine to update, insert, or delete data, as the indexes all have to be maintained as the data manipulation occurs.

Answer 148

This question can be answered a couple of different ways, but only one answer is a "good" one. The answer you want to hear is a Check constraint, which is defined on a database table that limits the values entered into that column. These constraints are relatively easy to create, and they are the recommended type for enforcing domain integrity in SQL Server. Triggers can also be used to restrict the values accepted in a field in a database table, but this solution requires the trigger to be defined on the table, which can hinder performance in certain situations. For this reason, Microsoft recommends Check constraints over all other methods for restricting domain integrity.

Answer 149

If the applicant is able to answer this question correctly, the odds are good that they have some experience working with stored procedures. A return parameter is always returned by a stored procedure, and it is meant to indicate the success or failure of the stored procedure. The return parameter is always an INT data type. An OUTPUT parameter is designated specifically by the developer, and it can return other types of data, such as characters and numeric values. (There are some limitations on the data types that can be used as output parameters.) You can use multiple OUTPUT parameters in a stored procedure, whereas you can only use one return parameter.

Answer 150

The more seasoned developer will be able to accurately describe this type of query. A correlated sub-query is a special type of query containing a sub-query. The sub-query contained in the query actually requests values from the outside query, creating a situation similar to a loop. You can find a more detailed description as to how these special types of queries work in this article.

Answer 151

SELECT c.FirstName , c.LastName , p.ProductName, s.SalePrice FROM Sales s INNER JOIN Customers c ON s.CustomerID = c.CustomerID INNER JOIN Products p ON s.ProductID = p.ProductID WHERE s.SaleDate >= '10/1/2005' AND s.SaleDate < '11/1/2005'

Answer 152

``` SELECT c.CustomerID , c.FirstName , c.LastName FROM Sales s RIGHT OUTER JOIN Customers c ON s.CustomerID = c.CustomerID WHERE s.CustomerID IS NULL ```

Answer 153

SELECT c.FirstName , c.LastName , s.SalePrice, p.RecommendedPrice , ABS(s.SalePrice - p.RecommendedPrice) AS AbsoluteSalePriceDifference FROM Sales s INNER JOIN Customers c ON s.CustomerID = c.CustomerID INNER JOIN Products p ON s.ProductID = p.ProductID

Answer 154

SELECT p.Category, AVG(s.SalePrice) AS AverageSalePrice | FROM Sales s INNER JOIN Products p ON s.ProductID = p.ProductIDGROUP BY p.Category

Answer 155

INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('Chris', 'Kringle', 'Henryville', 'IN', '47126') INSERT INTO Sales(CustomerID, ProductID, SalePrice, SaleDate)VALUES(SCOPE_IDENTITY(), 3, 205, '12/31/2005')

Answer 156

DELETE s FROM Sales s JOIN Customers c ON s.CustomerID = c.CustomerIDWHERE c.State = 'ME' DELETE c FROM Customers c WHERE c.State = 'ME'

Answer 157

UPDATE s SET SalePrice = p.RecommendedPrice FROM Sales s INNER JOIN Products p ON s.ProductID = s.ProductIDWHERE SaleDate >= '6/10/2005' AND SaleDate < '6/21/2005'

Answer 158

``` SELECT p.Category, COUNT(*) AS NumberOfSales FROM Sales s INNER JOIN Products p ON s.ProductID = p.ProductIDGROUP BY p.Category HAVING AVG(p.RecommendedPrice) >= AVG(s.SalePrice)+10 ```

Answer 159

SELECT c.FirstName, c.LastName, p.ProductName, s.SalePrice FROM Sales s INNER JOIN Customers c ON s.CustomerID = c.CustomerID INNER JOIN Products p ON s.ProductID = p.ProductID WHERE s.SaleDate >= '10/1/2005' AND s.SaleDate < '11/1/2005'

Answer 160

SELECT c.FirstName, c.LastName, p.ProductName, s.SalePrice FROM Sales s INNER JOIN Customers c ON s.CustomerID = c.CustomerID INNER JOIN Products p ON s.ProductID = p.ProductID WHERE s.SaleDate >= '10/1/2005' AND s.SaleDate