Prepared Statement Example
Prepared statements use question marks (?), which are placeholders for where actual values that will be used in the SQL should be “plugged” .
In JDBC, when we call connection.prepareStatement,the prepared SQL template is sent to the Database with the placeholder values (the “?”) left blank. Then, the Database will parse, compile, and perform query optimization on the template. After that, the Database will store the optimized query plan.
What are the advantages of using prepared statements?
1. They provide better performance. Even though a prepared statement can be executed many times, it is is compiled and optimized only once by the database.
2. They can prevent SQL injection attacks.
This is because the query is first compiled and optimized before any user input is added, which makes it impossible for user input to change and therefore compromise the integrity of the SQL statement., there is no way the data input by a hacker can be interpreted as SQL.
http://a4academics.com/interview-questions/53-database-and-sql/411-sql-interview-questions-and-answers-database
What are properties of a transaction?
Properties of the transaction can be summarized as ACID Properties.
1. Atomicity
In this , a transaction consists of many steps. When all the steps in the transaction go completed it get reflected in DB or if any step fails, all the transactions are rolled back.
2. Consistency
The database will move from one consistent state to another if the transaction succeeds and remain in the original state if the transaction fails.
3. Isolation
Every transaction should operate as if it is the only transaction in the system
4. Durability
Once a transaction has completed successfully, the updated rows/records must be available for all other transactions on a permanent basis
What is a primary key?
A primary key is a column whose values uniquely identify every row in a table. Primary key values can never be reused. If a row is deleted from the table, its primary key may not be assigned to any new rows in the future.
No two rows can have the same primary key value.
Every row must have a primary key value
Primary key field cannot be null
Values in primary key columns can never be modified or updated
What are the different type of normalization?
1. First Normal Form (1NF)
A relation is said to be in first normal form if and only if all underlying domains contain atomic values only.
2. Second Normal Form (2NF)
A relation is said to be in 2NF if and only if it is in 1NF and every non key attribute is fully dependent on the primary key.
3. Third Normal Form (3NF)
A relation is said to be in 3NF if and only if it is in 2NF and every non key attribute is non-transitively dependent on the primary key.
What is a SQL Composite Primary Key?
A Composite primary key is a set of columns whose values uniquely identify every row in a table.
What is a Foreign Key?
What is a Unique Key?
Unique key is same as primary with difference being existence of null. Unique key field allows one value as NULL value
Define Join and explain different type of joins?
In order to avoid data duplication , data is stored in related tables .
Join keyword is used to fetch data from related table.
Join return rows when there is at least one match in both tables. Type of joins are
Right Join
Return all rows from the right table, even if there are no matches in the left table .
Outer Join
Left Join
Return all rows from the left table, even if there are no matches in the right table .
Full Join
Return rows when there is a match in one of the tables .
What is Self-Join?
Self-join is query used to join a table to itself. Aliases should be used for the same table comparison.
What is Cross Join?
Cross Join will return all records where each row from the first table is combined with each row from the second table.
What is a view?
Views are virtual tables. Unlike tables that contain data, views simply contain queries that dynamically retrieve data when used.
What is a materialized view?
Materialized views is also a view but are disk based . Materialized views get updated on specific duration, base upon the interval specified in the query definition. We can index materialized view.
What are the advantages and disadvantages of views in a database?
Advantages:
1. Views doesn't store data in a physical location.
2. View can be use to hide some of the columns from the table
3. Views can provide Access Restriction, since data insertion , update and deletion is not possible on the view.
Disadvantages:
1. When a table is dropped , associated view become irrelevant.
2. Since view are created when a query requesting data from view is triggered, its bit slow
3. When views are created for large tables, it occupy more memory
What is a stored procedure?
Stored Procedure is a function which contain collection of SQL Queries. Procedure can take inputs , process them and send back output.
What is a trigger?
Triggers are set of commands that get executed when an event(Before Insert,After Insert,On Update,On delete of a row) occurs on a table,views.
Explain the difference between DELETE , TRUNCATE and DROP commands?
Once delete operation is performed Commit and Rollback can be performed to retrieve data.
But after truncate statement, Commit and Rollback statement cant be performed.
Where condition can be used along with delete statement but it cant be used with truncate statement.
Drop command is used to drop the table or keys like primary,foreign from a table.
What is the difference between Cluster and Non cluster Index?
A clustered index reorders the way records in the table are physically stored.
There can be only one clustered index per table.
It make data retrieval faster.
A non clustered index does not alter the way it was stored but creates a complete separate object within the table. As a result insert and update command will be faster.
What is Union, minus and Interact commands?
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 the queries.
What are the type of locks?
1. Shared Lock
When a shared lock is applied on data item, other transactions can only read the item, but cant write into it.
2. Exclusive Lock
When a exclusive lock is applied on data item, other transactions cant read or write into the data item.
What is SQL ?
Structured Query Language(SQL) is a language designed specifically for communicating with databases. SQL is an ANSI (American National Standards Institute) standard .
What are the different type of SQL's?
1. DDL – Data Definition Language
DDL is used to define the structure that holds the data.
2. DML – Data Manipulation Language
DML is used for manipulation of the data itself. Typical operations are Insert,Delete,Update and retrieving the data from the table
3. DCL– Data Control Language DCL is used to control the visibility of data like granting database access and set privileges to create table etc.
How are transactions used?
Database transaction take database from one consistent state to another. At the end of the transaction the system must be in the prior state if transaction fails or the status of the system should reflect the successful completion if the transaction goes through.
Transactions allow you to group SQL commands into a single unit. The transaction begins with a certain task and ends when all tasks within it are complete. The transaction completes successfully only if all commands within it complete successfully. The whole thing fails if one command fails.
The BEGIN TRANSACTION, ROLLBACK TRANSACTION, and COMMIT TRANSACTION statements are used to work with transactions. A group of tasks starts with the begin statement. If any problems occur, the rollback command is executed to abort. If everything goes well, all commands are permanently executed via the commit statement.\
What is the difference between truncate and delete?
Truncate is a quick way to empty a table. It removes everything without logging each row. Truncate will fail if there are foreign key relationships on the table. Conversely, the delete command removes rows from a table, while logging each deletion and triggering any delete triggers that may be present.
http://www.tecmint.com/10-mysql-database-interview-questions-for-beginners-and-intermediates/
What is the difference between Primary Key and Unique Key?
Both Primary and Unique Key is implemented for Uniqueness of the column.
Primary Key creates a clustered index of column where as an Unique creates unclustered index of column.
Moreover, Primary Key doesn’t allow NULL value, however Unique Key does allows one NULL value.
What are indexes in a Database. What are the types of indexes?
Indexes are the quick references for fast data retrieval of data from a database. There are two different kinds of indexes.
Clustered Index
Only one per table.
Faster to read than non clustered as data is physically stored in index order.
Nonclustered Index
Can be used many times per table.
Quicker for insert and update operations than a clustered index.
What is Heap table?
Tables that are present in the memory are called as HEAP tables. These tables are commonly known as memory tables. These memory tables never have values with data type like “BLOB” or “TEXT”. They use indexes which make them faster.
What is the difference between a clustered and a nonclustered index?
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.
SQL Server
http://www.techrepublic.com/blog/software-engineer/sql-server-database-developer-interview-questions-and-answers
What is the default port number for SQL Server?
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.
What is a view? What is the WITH CHECK OPTION clause for a view?
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.
What is a query execution plan?
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.
What does the SQL Server Agent Windows service do?
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.
http://www.shuatiblog.com/blog/2015/02/09/intro-google-spanner/
NewSQL vs NoSQL
As these NoSQL products don’t provide Strong Consistency, they cannot be used where high-level data consistency is required.
NewSQL has as excellent scalability as NoSQL, and at the same time it guarantees ACID like RDBMS which is performed in a single node.
Spanner is a NewSQL created by Google. It is a distributed relational database that can distribute and store data in Google’s BigTable storage system in multiple data centers. Spanner meets ACID (of course, it supports transaction) and supports SQL.
References
http://a4academics.com/interview-questions/53-database-and-sql/411-sql-interview-questions-and-answers-database
http://www.tecmint.com/10-mysql-database-interview-questions-for-beginners-and-intermediates/
http://www.tecmint.com/basic-mysql-interview-questions-for-database-administrators/
http://www.techrepublic.com/blog/software-engineer/sql-server-database-developer-interview-questions-and-answers/
Prepared statements use question marks (?), which are placeholders for where actual values that will be used in the SQL should be “plugged” .
In JDBC, when we call connection.prepareStatement,the prepared SQL template is sent to the Database with the placeholder values (the “?”) left blank. Then, the Database will parse, compile, and perform query optimization on the template. After that, the Database will store the optimized query plan.
What are the advantages of using prepared statements?
1. They provide better performance. Even though a prepared statement can be executed many times, it is is compiled and optimized only once by the database.
2. They can prevent SQL injection attacks.
This is because the query is first compiled and optimized before any user input is added, which makes it impossible for user input to change and therefore compromise the integrity of the SQL statement., there is no way the data input by a hacker can be interpreted as SQL.
http://a4academics.com/interview-questions/53-database-and-sql/411-sql-interview-questions-and-answers-database
What are properties of a transaction?
Properties of the transaction can be summarized as ACID Properties.
1. Atomicity
In this , a transaction consists of many steps. When all the steps in the transaction go completed it get reflected in DB or if any step fails, all the transactions are rolled back.
2. Consistency
The database will move from one consistent state to another if the transaction succeeds and remain in the original state if the transaction fails.
3. Isolation
Every transaction should operate as if it is the only transaction in the system
4. Durability
Once a transaction has completed successfully, the updated rows/records must be available for all other transactions on a permanent basis
What is a primary key?
A primary key is a column whose values uniquely identify every row in a table. Primary key values can never be reused. If a row is deleted from the table, its primary key may not be assigned to any new rows in the future.
No two rows can have the same primary key value.
Every row must have a primary key value
Primary key field cannot be null
Values in primary key columns can never be modified or updated
What are the different type of normalization?
1. First Normal Form (1NF)
A relation is said to be in first normal form if and only if all underlying domains contain atomic values only.
2. Second Normal Form (2NF)
A relation is said to be in 2NF if and only if it is in 1NF and every non key attribute is fully dependent on the primary key.
3. Third Normal Form (3NF)
A relation is said to be in 3NF if and only if it is in 2NF and every non key attribute is non-transitively dependent on the primary key.
What is a SQL Composite Primary Key?
A Composite primary key is a set of columns whose values uniquely identify every row in a table.
What is a Foreign Key?
What is a Unique Key?
Unique key is same as primary with difference being existence of null. Unique key field allows one value as NULL value
Define Join and explain different type of joins?
In order to avoid data duplication , data is stored in related tables .
Join keyword is used to fetch data from related table.
Join return rows when there is at least one match in both tables. Type of joins are
Right Join
Return all rows from the right table, even if there are no matches in the left table .
Outer Join
Left Join
Return all rows from the left table, even if there are no matches in the right table .
Full Join
Return rows when there is a match in one of the tables .
What is Self-Join?
Self-join is query used to join a table to itself. Aliases should be used for the same table comparison.
What is Cross Join?
Cross Join will return all records where each row from the first table is combined with each row from the second table.
What is a view?
Views are virtual tables. Unlike tables that contain data, views simply contain queries that dynamically retrieve data when used.
What is a materialized view?
Materialized views is also a view but are disk based . Materialized views get updated on specific duration, base upon the interval specified in the query definition. We can index materialized view.
What are the advantages and disadvantages of views in a database?
Advantages:
1. Views doesn't store data in a physical location.
2. View can be use to hide some of the columns from the table
3. Views can provide Access Restriction, since data insertion , update and deletion is not possible on the view.
Disadvantages:
1. When a table is dropped , associated view become irrelevant.
2. Since view are created when a query requesting data from view is triggered, its bit slow
3. When views are created for large tables, it occupy more memory
What is a stored procedure?
Stored Procedure is a function which contain collection of SQL Queries. Procedure can take inputs , process them and send back output.
What is a trigger?
Triggers are set of commands that get executed when an event(Before Insert,After Insert,On Update,On delete of a row) occurs on a table,views.
Explain the difference between DELETE , TRUNCATE and DROP commands?
Once delete operation is performed Commit and Rollback can be performed to retrieve data.
But after truncate statement, Commit and Rollback statement cant be performed.
Where condition can be used along with delete statement but it cant be used with truncate statement.
Drop command is used to drop the table or keys like primary,foreign from a table.
What is the difference between Cluster and Non cluster Index?
A clustered index reorders the way records in the table are physically stored.
There can be only one clustered index per table.
It make data retrieval faster.
A non clustered index does not alter the way it was stored but creates a complete separate object within the table. As a result insert and update command will be faster.
What is Union, minus and Interact commands?
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 the queries.
What are the type of locks?
1. Shared Lock
When a shared lock is applied on data item, other transactions can only read the item, but cant write into it.
2. Exclusive Lock
When a exclusive lock is applied on data item, other transactions cant read or write into the data item.
What is SQL ?
Structured Query Language(SQL) is a language designed specifically for communicating with databases. SQL is an ANSI (American National Standards Institute) standard .
What are the different type of SQL's?
1. DDL – Data Definition Language
DDL is used to define the structure that holds the data.
2. DML – Data Manipulation Language
DML is used for manipulation of the data itself. Typical operations are Insert,Delete,Update and retrieving the data from the table
3. DCL– Data Control Language DCL is used to control the visibility of data like granting database access and set privileges to create table etc.
How are transactions used?
Database transaction take database from one consistent state to another. At the end of the transaction the system must be in the prior state if transaction fails or the status of the system should reflect the successful completion if the transaction goes through.
Transactions allow you to group SQL commands into a single unit. The transaction begins with a certain task and ends when all tasks within it are complete. The transaction completes successfully only if all commands within it complete successfully. The whole thing fails if one command fails.
The BEGIN TRANSACTION, ROLLBACK TRANSACTION, and COMMIT TRANSACTION statements are used to work with transactions. A group of tasks starts with the begin statement. If any problems occur, the rollback command is executed to abort. If everything goes well, all commands are permanently executed via the commit statement.\
What is the difference between truncate and delete?
Truncate is a quick way to empty a table. It removes everything without logging each row. Truncate will fail if there are foreign key relationships on the table. Conversely, the delete command removes rows from a table, while logging each deletion and triggering any delete triggers that may be present.
http://www.tecmint.com/10-mysql-database-interview-questions-for-beginners-and-intermediates/
What is the difference between Primary Key and Unique Key?
Both Primary and Unique Key is implemented for Uniqueness of the column.
Primary Key creates a clustered index of column where as an Unique creates unclustered index of column.
Moreover, Primary Key doesn’t allow NULL value, however Unique Key does allows one NULL value.
What are indexes in a Database. What are the types of indexes?
Indexes are the quick references for fast data retrieval of data from a database. There are two different kinds of indexes.
Clustered Index
Only one per table.
Faster to read than non clustered as data is physically stored in index order.
Nonclustered Index
Can be used many times per table.
Quicker for insert and update operations than a clustered index.
What is Heap table?
Tables that are present in the memory are called as HEAP tables. These tables are commonly known as memory tables. These memory tables never have values with data type like “BLOB” or “TEXT”. They use indexes which make them faster.
What is the difference between a clustered and a nonclustered index?
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.
SQL Server
http://www.techrepublic.com/blog/software-engineer/sql-server-database-developer-interview-questions-and-answers
What is the default port number for SQL Server?
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.
What is a view? What is the WITH CHECK OPTION clause for a view?
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.
What is a query execution plan?
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.
What does the SQL Server Agent Windows service do?
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.
http://www.shuatiblog.com/blog/2015/02/09/intro-google-spanner/
NewSQL vs NoSQL
As these NoSQL products don’t provide Strong Consistency, they cannot be used where high-level data consistency is required.
NewSQL has as excellent scalability as NoSQL, and at the same time it guarantees ACID like RDBMS which is performed in a single node.
Spanner is a NewSQL created by Google. It is a distributed relational database that can distribute and store data in Google’s BigTable storage system in multiple data centers. Spanner meets ACID (of course, it supports transaction) and supports SQL.
References
http://a4academics.com/interview-questions/53-database-and-sql/411-sql-interview-questions-and-answers-database
http://www.tecmint.com/10-mysql-database-interview-questions-for-beginners-and-intermediates/
http://www.tecmint.com/basic-mysql-interview-questions-for-database-administrators/
http://www.techrepublic.com/blog/software-engineer/sql-server-database-developer-interview-questions-and-answers/
