A field is an area within a record reserved for a specific piece of data.
2. What is a record in a database?
A record is the collection of values/fields of a specific entity.
3. What is a table in database?
A table is a collection of records of a specific type.
4. What is a database transaction?
Database transaction takes database from one state to another. At the end of the transaction the system must be in the prior state if the transaction fails or the status of the system reflect the successful completion if the transaction goes through.
5. What are properties of a transaction? (ACID properties)
(1) Atomicity
A transaction consists of many steps. When all the steps in a transaction get completed, it will get reflected in DB or if any step fails, all the transactions are rolled back.
(2) Consistency (* What is the difference between consistency and atomocity?)
The database will move from one consistent state to another, if the transaction succeeds and remainin the original state, if transaction fails.
(3) Isolation
Every transaction should operate as if it is the only transaction in the system.
(4) Durability
Once a transaction has been completed successfully, the update rows/records must be available for all the other transactions on a permanent basis.
5. What is a composite key?
A composite primary key is a type of candidate key, which represents a set of columns whose values uniquely identified every row in a table.
e.g. Combination of "employee id" and "employee name" in a table is combined to uniquely to identify a row its called a composite key.
6. What is a foreign key?
A foreign key is a column (or columns) that references a column (most often the primary key) of another table.
7. What is a unique key?
Unique key is same as primary with the difference being the existence of null.
8. SQL insert statement:
SQL INSERT statement is used to add rows to a table. For a full row insert, SQL query should start with "insert into" statement followed by name and value command. The insert can be used in several ways:
(1) to insert a single complete row
(2) to insert a single partial row
9. SQL update statement:
10. SQL delete statement:
11. What are wild cards used in database for pattern matching?
SQL like operator is used for pattern matching.
12. Define join and explain different type of joins:
In order to avoid data duplications, data is stored in related tables. Join keyword is used to fetch data from related tables. "join" return rows when there is at least one match in both table. Type of joins are:
(1) right join: return all rows from the right table, even if there are no matches in the left table
(2) left join: return all rows from the left table even if there are no matches in the right table
(3) full join: return rows when there is a match in one of the tables
13. What is self join?
Self join is query used to join a table itself.
(* When will self join be used?)
14. What is cross join?(*)
15. What is a view?
The views are virtual tables. Unlike tables that contain data, views simply contain queries that dynamically retrieve data when used.
16. What is a materialized view?
Materialized views are also a view but are disk based. Materialized views get updates on specific duration, base upon the interval specified in the query definition.
17. What are the advantages and disadvantages of views in a database?
Advantages:
(1) Views don't store data in a physical location
(2) The view can be used to hide some of the columns from the table
(3) Views can provide access restriction, since data insertion, update and deletion is not possible with the view.
Disadvantages:
(1) When a table is dropped, associate view become irrelevant.
(2) Since the view is created when a query requesting data from view is triggered, its a bit slow
(3) When viwes are created for large tables, it occupies memory
18. What is a stored procedure?
Stored procedure is a function which contains a collection of SQL queries. The procedure can take inputs, process tham and send back output.
19. What are the advantages of a stored procedure?
Stored procedure are precompiled and stored in the database. This enables the database to execute the queries much faster. Since many queries can be included in a stored procedure, round trip time to execute multiple queries from source code to database and back is avoided.
20. What is a trigger?
Database triggers are sets of commands that get executed when an event (before insert, after insert, on update, on delete of a row) occurs on a table, vies.
21. Explain the difference between delete, truncate and drop commands?
Once delete operation is performed, commit and rollback can be performed to retrieve data.
Once truncate statement is executed, commit and rollback statement cannot be performed. Where condition can be used along with delete statement but it can't be used with truncate statement.
Drop command is used to drop the table or keys like primary foreign key from a table.
22. What is the difference between cluster and non cluster index?
A cluster index reorders the way records in the table are physically stored. There can be only one clustered index per table. It makes data retrieval faster.
A non clustered index does not alter the way it was stored but creates a completely separate object within the table. As a result insert and update command will be faster.
23. What is union, minus and interact command?
minus operator is used to return rows from the first query but not from the second query.
intersect operator is used to return rows returned by both queries.
reference: http://a4academics.com/interview-questions/53-database-and-sql/411-sql-interview-questions-and-answers-database
24. 1NF/2NF/3NF
First normal form (1NF) is a property of a relation in a relational database. A relation is in first normal form if the domain of each attribute contains only atomic values, and the value of each attribute contains only a single value from that domain.
A table that is in first normal form (1NF) must meet additional criteria if it is to qualify for second normal form. Specifically: a table is in 2NF if and only if it is in 1NF and no non-prime attribute is dependent on any proper subset of any candidate key of the table. A non-prime attribute of a table is an attribute that is not a part of any candidate key of the table.
Put simply, a table is in 2NF if and only if it is in 1NF and every non-prime attribute of the table is dependent on the whole of every candidate key.
Third normal form(3NF) is a normal form used in normalizing a database design to reduce the duplication of data and ensure referential integrity by ensuring that the entity is in second normal form and all the attributes in a table are determined only by the candidate keys of that table and not by any non-prime attributes.
没有评论:
发表评论