SQL Questions & Answers

 1.What is commit?

A.An operation that saves all changes to databases, cubes, or dimensions made since the start of a transaction. A commit guarantees that all of the transaction's modifications are made a permanent part of the database, cube or dimension. A commit also frees resources, such as locks, used by the transaction.

2.What is clustered index?

A.An index in which the logical order of the key values determines the physical order of the corresponding rows in a table.

3.What is check constraint?

A.Defines which data values are acceptable in a column. You can apply CHECK constraints to multiple columns, and you can apply multiple CHECK constraints to a single column. When a table is dropped, CHECK constraints are also dropped.

4.What is constraint?

A.A property assigned to a table column that prevents certain types of invalid data values from being placed in the column. For example, a UNIQUE or PRIMARY KEY constraint prevents you from inserting a value that is a duplicate of an existing value, a CHECK constraint prevents you from inserting a value that does not match a search condition, and NOT NULL prevents you from inserting a NULL value.

5.What is correlated subquery?

A.A subquery that references a column in the outer statement. The inner query is executed for each candidate row in the outer statement.

6.What is cube?

A.A set of data that is organized and summarized into a multidimensional structure defined by a set of dimensions and measures.

7.What is alias?

A.An alternative name for a table or column in expressions that is often used to shorten the name for subsequent reference in code, prevent possible ambiguous references, or provide a more descriptive name in the query output. An alias can also be an alternative name for a server.

8.What is data definition language(DDL)?

A.A language, usually part of a database management system, that is used to define all attributes and properties of a database, especially row layouts, column definitions, key columns (and sometimes keying methodology), file locations, and storage strategy.

9.What is data dictionary?

A.A set of system tables, stored in a catalog, that includes definitions of database structures and related information, such as permissions.

10.What is data manipulation language(DML)?

A.The subset of SQL statements used to retrieve and manipulate data.

11.What is database?

A.A collection of information, tables, and other objects organized and presented to serve a specific purpose, such as searching, sorting, and recombining data. Databases are stored in files.

12.What is data control language(DCL)?

A.The subset of SQL statements used to control permissions on database objects. Permissions are controlled using the GRANT and REVOKE statements.

13.What is default constraint?

A.A property defined for a table column that specifies a constant to be used as the default value for the column. If any subsequent INSERT or UPDATE statement specifies a value of NULL for the column, or does not specify a value for the column, the constant value defined in the DEFAULT constraint is placed in the column.

14.What is deny?

A.Removes a permission from a user account and prevents the account from gaining permission through membership in groups or roles within the permission.

15.What is equijoin?

A.A join in which the values in the columns being joined are compared for equality, and all columns are included in the results.

16.What is foreign key?

A.The column or combination of columns whose values match the primary key (PK) or unique key in the same or another table. Also called the referencing key.

17.What is grant?

A.Applies permissions to a user account, which allows the account to perform an activity or work with data.

18.What is identity column?

A.A column in a table that has been assigned the identity property. The identity property generates unique numbers.

19.What is index?

A.In a relational database, a database object that provides fast access to data in the rows of a table, based on key values. Indexes can also enforce uniqueness on the rows in a table. SQL Server supports clustered and nonclustered indexes. The primary key of a table is automatically indexed. In full-text search, a full-text index stores information about significant words and their location within a given column.

20.What is inner join?

A.An operation that retrieves rows from multiple source tables by comparing the values from columns shared between the source tables. An inner join excludes rows from a source table that have no matching rows in the other source tables.

21.What is join?

A.As a verb, to combine the contents of two or more tables and produce a result set that incorporates rows and columns from each table. Tables are typically joined using data that they have in common. As a noun, the process or result of joining tables, as in the term "inner join" to indicate a particular method of joining tables.

22.What is left outer join?

A.A type of outer join in which all rows from the left-most table in the JOIN clause are included. When rows in the left table are not matched by rows in the right table, all result set columns that come from the right table are assigned a value of NULL.

23.What are logical operators?

A.The operators AND, OR, and NOT. Used to connect search conditions in WHERE clauses.

24.What is nonclustered index?

A.An index in which the logical order of the index is different than the physical, stored order of the rows on disk.

25.What is outer join?

A.A join that includes all the rows from the joined tables that have met the search conditions, even rows from one table for which there is no matching row in the other join table. For result set rows returned when a row in one table is not matched by a row from the other table, a value of NULL is supplied for all result set columns that are resolved to the table that had the missing row.

26.What is primary key?

A.A column or set of columns that uniquely identify all the rows in a table. Primary keys do not allow null values. No two rows can have the same primary key value; therefore, a primary key value always uniquely identifies a single row. More than one key can uniquely identify rows in a table, each of these keys is called a candidate key. Only one candidate can be chosen as the primary key of a table; all other candidate keys are known as alternate keys. Although tables are not required to have primary keys, it is good practice to define them. In a normalized table, all of the data values in each row are fully dependent on the primary key.

27.What is relational database management system(RDBMS)?

A.A system that organizes data into related rows and columns. SQL Server is a relational database management system (RDBMS).

28.What is right outer join?

A.A type of outer join in which all rows in the right-most table in the JOIN clause are included. When rows in the right table are not matched in the left table, all result set columns that come from the left table are assigned a value of NULL.

29.What is rollback?

A.To remove the updates performed by one or more partially completed transactions. Rollbacks are required to restore the integrity of a database after an application, database, or system failure.

30.What is savepoint?

A.A marker that allows an application to roll back part of a transaction if a minor error is encountered. The application must still commit or roll back the full transaction when it is complete.

31.What is self join?

A.A join in which records from a table are combined with other records from the same table when there are matching values in the joined fields. A self-join can be an inner join or an outer join. In database diagrams, a self-join is called a reflexive relationship.

32.What are store procedures?

A.A precompiled collection of Transact-SQL statements stored under a name and processed as a unit. SQL Server supplies stored procedures for managing SQL Server and displaying information about databases and users. SQL Server-supplied stored procedures are called system stored procedures.

33.What is subquery?

A.A SELECT statement nested inside another SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery.

34.What is transaction?

A.A group of database operations combined into a logical unit of work that is either wholly committed or rolled back. A transaction is atomic, consistent, isolated, and durable.

35.What is trigger?

A.A stored procedure that executes when data in a specified table is modified. Triggers are often created to enforce referential integrity or consistency among logically related data in different tables.

36.What is unique index?

A.An index in which no two rows are permitted to have the same index value, thus prohibiting duplicate index or key values. The system checks for duplicate key values when the index is created and checks each time data is added with an INSERT or UPDATE statement.

37.What are UNIQUE constraints?

A.Constraints that enforce entity integrity on a nonprimary key. UNIQUE constraints ensure that no duplicate values are entered and that an index is created to enhance performance.

38.What is view?

A.A database object that can be referenced the same way as a table in SQL statements. Views are defined using a SELECT statement and are analogous to an object that contains the result set of this statement.

39.What is Structured Query Language(SQL)?

A.A language used to insert, retrieve, modify, and delete data in a relational database. SQL also contains statements for defining and administering the objects in a database. SQL is the language supported by most relational databases, and is the subject of standards published by the International Standards Organization (ISO) and the American National Standards Institute (ANSI). SQL Server 2000 uses a version of the SQL language called Transact-SQL.

40.What is revoke?

A.Removes a previously granted or denied permission from a user account, role, or group in the current database.