SQL Flashcards
Define SQL?
Structured query language is the standard command set used to communicate with the relational database management system.
Define Dbms?
A Database Management system consists of a collection of interrelated data and set of programs to access that data.
What is the purpose of Database systems?
Database Management system provides a secure and survivable medium for the storage and retrieval of data.In the real world, the data is shared among several users and is persistent.
State the different between Security and Integrity?
A Database Management system provides a secure and survivable medium for the storage and retrieval of data.In the real world, the data is shared among several users and is persistent.
State the different between Security and Integrity?
Security is a protection from malicious attempts to steal or modify data.
Integrity constraints guard against accidental damage to the database, by ensuribg that authorized changes to the database do not result in a loss of data consistency.
Define Normalisation?
Normalisation is an essential part of database design. A good understanding of the semantic of data helps the designer to built efficient design using the concept of normalization.
What are the purpose of Normalisation?
■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.
Define Primary Key?
■The primary key is the columns used to uniquely identify each row of a table.
■A table can have only one primary key.
■No primary key value can appear in more than one row in the table.
Define Unique Key?
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.
Define Foreign Key?
A foreign Key is a combination of columns with value is based on the primary key values from another table. A foreign key constraint also known as Referential Integrity Constraint.
Define View?
■A View is a database object that is a logical representation of a table.
■It is derived from a table but has no longer of its own and often may be used in the same manner as a table.
■A view is a virtual table that has columns similar to a table.
■A view does not represent any physical data.
Compare and contrast TRUNCATE and DELETE for a table?
Both the truncate and delete command have the desired outcome of getting rid of all the rows in a table. The difference between the two is that the truncate command is a DDL operation and just moves the high water mark and produces a now rollback. The delete command, on the other hand, is a DML operation, which will produce a rollback and thus take longer to complete.
What is cursors?
Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time.
Define SubQuery?
■Nesting of Queries one within the other is called as a Subquery.
■A table can have only one primary key.
What are the different types of subquery?
■Single row subquery
■Multiple row subquery
■Correlated row subquery
What are the different types of replication?
The SQL Server 2000-supported replication types are as follows ■Transactional
■Snapshot
■Merge
What is User Defined Functions?
User-Defined Functions allow to define its own T-SQL functions that can accept 0 or more parameters and return a single scalar data value or a table data type.
Define Self Join?
Self join means joining one table with itself.
The self join can be viewed as a join of two copies of the same table.
Define Sequence?
A Sequence is a database object that can be used to provide very quick generation of unique numbers.
Define Joins?
A Join combines columns and data from two or more tables (and in rare cases, of one table with itself).
What are the types of Joins?
■Equi joins
■Cartesian Joins
■Outer Joins
■Self Joins.
Define Equi Joins?
A Equi Join is a join in which the join comparison operator is an equality. When two tables are joined together using equality or values in one or more columns, they make an Equi Join.
Define Cartesian Join?
Joining two tables without a whereclause produces a Cartesian join which combines every row in one table with every row in another table.
What are three SQL keywords used to change or set someone’s permissions?
GRANT, DENY, and REVOKE
What are primary keys and foreign keys?
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.
Define data model?
Underlying the structure of the database is called as data model.
What is an Entity?
It is a ‘thing’ in the real world with an independent existence
What is BCP? When does it used?
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.
Explain the use of the by GROUP BY and the HAVING clause?
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.
What is DataWarehousing?
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”.
What are the advantages of Database?
■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
at are the advantage of SQL?
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.
What is the difference between join and outer join?
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.
Define Boyce coded normal form?
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.
What are the transaction properties?
■Atomicity
■Consistency
■Isolation
■Durability
What is data mining?
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.
Compare DBMS versus object oriented DBMS?
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.
What are the types of SQL Commands?
■Data Definition Language (DDL)
■Data Manipulation Language (DML)
■Data Query Language (DQL)
■Data Control Language (DCL)
What is an attribute?
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
What are the different types of data models ?
■Entity relationship model ■Relational model ■Hierarchical model ■Network model ■Object oriented model ■Object relational model
What is an active database?
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.
What are ACID properties?
■Atomicity
■Consistency
■Isolation
■Durability
What is a tuple?
A tuple is an instance of data within a relational database.
What is meant by embedded SQL?
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).
What is Functional Dependency?
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.
What are the different phases of transaction?
The different phases of transaction are■Analysis phase
■Redo Phase
■Undo phase
What the difference between UNION and UNIONALL?
Union will remove the duplicate rows from the result set while Union all does’nt.
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.
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.
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.
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. 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.
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 are different type of Collation Sensitivity?
The different phases of transaction are ■Case sensitivity
■Accent sensitivity
■Kana Sensitivity
■Width sensitivity
What is 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 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.
What is the difference between Function and Stored Procedure?
■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.
What command do we use to rename a db?
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.
What is BCP?
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.
What is Cross Join?
A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table.
What is Storage Manager?
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.
What are stored-procedures? And what are the advantages of using them?
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.
What is database Trigger?
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.
What is OLTP?
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.
What is DDL (Data Definition Language)?
A data base schema is specifies by a set of definitions expressed by a special language called DDL.
What is Weak Entity set?
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.
What is a deadlock?
Two processes wating to update the rows of a table which are locked by the other process then deadlock arises.
What do you mean by flat file database?
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.
What is Storage Manager?
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.
What is Index?
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.
What is the difference between clustered and a non-clustered index?
A Clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.
A 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.
What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?
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.
What is log shipping?
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.
What are primary keys and foreign keys?
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.
What are check constraint?
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.
What are the type of Synonyms?
There are two types of Synonyms are :■Private
■Public
