DBMS Flashcards

Question

What is a View

Answer 1

A simple view can be thought of as a subset of a table. It can be used for retrieving data, as well as updating or deleting rows. Rows updated or deleted in the view are updated or deleted in the table the view was created with. It should also be noted that as data in the original table changes, so does data in the view, as views are the way to look at part of the original table. The results of using a view are not permanently stored in the database. The data accessed through a view is actually constructed using standard T-SQL select command and can come from one to many different base tables or even other views.

Answer 2

- -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. The users cannot see the indexes; they are just used to speed up queries. - -Effective indexes are one of the best ways to improve performance in a database application.

Answer 3

A table scan happens when there is no index available to help a query. In a table scan SQL Server examines every row in the table to satisfy the query results. Table scans are sometimes unavoidable, but on large tables, scans have a terrific impact on performance.

Answer 4

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

Answer 5

 SP-Must be executed explicitly with EXECUTE Statement whereas UDF-Must be called from SELECT Statement (Implicit calling)  SP may or may not return any value whereas UDF must return something (scalar/table-valued)  SP can have OUT parameters whereas UDF do not have any OUT parameters  SP cannot return 2D Data (must always be scalar INT) with RETURN statement whereas UDF can return 2D data along with scalar data  SP can CREATE temporary tables whereas UDF do not have any access to temporary tables  SP can include any DDL and DML statements whereas UDF cannot have DDL and cannot do DML with permanent tables/views.  UDF can do DML only with table variables created inside of it whereas SP can call UDF but UDF cannot call a SP

Answer 6

--In OLTP - online transaction processing systems, relational database design use the discipline of data modeling and generally follow the Codd rules of data normalization in order to ensure absolute data integrity. --Using these rules complex information is broken down into its most simple structures (a table) where all of the individual atomic level elements relate to each other and satisfy the normalization rules.

Answer 7

Delete command removes the rows from a table based on the condition that we provide with a WHERE clause. While Truncate will actually remove all the rows from a table and there will be no data in the table after we run the truncate command

Answer 8

 TRUNCATE is faster and uses fewer system and transaction log resources than DELETE.  TRUNCATE removes the data by deallocating the data pages used to store the table's data, and only the page deallocations are recorded in the transaction log.  TRUNCATE removes all rows from a table, but the table structure, its columns, constraints, indexes and so on, remains. The counter used by an identity for new rows is reset to the seed for the column.  You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint. Because TRUNCATE TABLE is not logged, it cannot activate a trigger.  TRUNCATE cannot be rolled back.  TRUNCATE is DDL Command.  TRUNCATE Resets identity of the table

Answer 9

 DELETE removes rows one at a time and records an entry in the transaction log for each deleted row.  If you want to retain the identity counter, use DELETE instead. If you want to remove table definition and its data, use the DROP TABLE statement.  DELETE Can be used with or without a WHERE clause  DELETE Activates Triggers.  DELETE can be rolled back.  DELETE is DML Command.  DELETE does not reset identity of the table.

Answer 10

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

Answer 11

 DDL (Data Definition Language, contains Create, Alter, Drop) --- Used for Table creation. E.g. create table student (StudentID Int, Name Varchar (50), Class Varchar (20);  DML (Data Manipulation Language, contains Insert, Update, Delete) --- Used for data management. E.g. Insert into student (StudentID, Name, Class) Values (1,’Alex’, ‘Graduation’); Update table Student Set Name=’Tom’ where StudentID= 111; Delete from Student where StudentID = 111;  DQL (Data Query Language, contain Select) --- Used data retrieval. E.g. Select Name from Student where StudentID =111; Select * from Student;  DCL (Data Control Language, contains Grant, Revoke, Deny) --- Used for grating and revoking permissions to users.