SQL Interview Questions Part 2
1.) Does SQL back upwards programming?
No, SQL doesn't guide keep loop or Conditional statement. It is used similar commanding linguistic communication to access databases.
2.) What is information Definition language?
Data Definition linguistic communication (DDL) allows you lot to CREATE, ALTER as well as DELETE database objects such equally schema, tables, view, sequence etc.
3.) What is information manipulation language?
Data manipulation linguistic communication makes user able to access as well as manipulate data. It is used to perform next operations.
• Insert information into database
• Retrieve information from the database
• Update information inward the database
• Delete information from the database
4.) What is information command language?
Data command linguistic communication allows you lot to command access to the database. It includes 2 commands GRANT as well as REVOKE.
• GRANT: to grant specific user to perform specific task.
• REVOKE: to cancel previously denied or granted permissions.
5.) How exercise you lot implement one-to-one, one-to-many as well as many-to-many relationships spell designing tables?
• One-to-One human relationship tin mail away hold upwards implemented equally a unmarried tabular array as well as rarely equally 2 tables alongside primary as well as unusual cardinal relationships.
• One-to-Many relationships are implemented yesteryear splitting the information into 2 tables alongside primary cardinal as well as unusual cardinal relationships.
• Many-to-Many relationships are implemented using a junction tabular array alongside the keys from both the tables forming the composite primary cardinal of the junction table.
6.) What is a stored procedure?
Stored Procedure is a portion which contains a collection of SQL Queries. The physical care for tin mail away accept inputs, physical care for them as well as mail dorsum output.
7.) What are the advantages a stored procedure?
Stored Procedures are precompiled as well as stored inward the database. This enables the database to execute the queries much faster. Since many queries tin mail away hold upwards included inward a stored procedure, circular trip fourth dimension to execute multiple queries from source code to database as well as dorsum is avoided.
8.) What is recursive stored procedure?
A stored physical care for which calls yesteryear itself until it reaches approximately boundary condition. This recursive portion or physical care for helps programmers to role the same prepare of code whatever reveal of times.
9.) What is a trigger?
When database are inserted, deleted or updated, a physical care for is executed inward reply to that action. Such SQL procedures are called equally trigger.
Example: When a novel educatee is added to the educatee database, novel records should hold upwards created inward the related tables similar Exam, Score as well as Attendance tables.
10.) What is departure betwixt TRUNCATE, DELETE & DROP?
• TRUNCATE commits later on deleting entire tabular array i.e., cannot hold upwards rolled back. Database triggers exercise non burn downward on TRUNCATE
• DELETE allows the filtered deletion. Deleted records tin mail away hold upwards rolled dorsum or committed. Database triggers burn downward on DELETE.
• Drop command is used to drib the tabular array or keys similar primary, unusual from a table.
11.) What is Union, minus as well as Interact commands?
• UNION operator is used to combine the results of 2 tables, as well as it eliminates duplicate rows from the tables.
• MINUS operator is used to supply rows from the starting fourth dimension query only non from the minute query.
• INTERSECT operator is used to supply rows returned yesteryear both the queries.
12.) What is the departure betwixt WHERE clause as well as HAVING clause?
WHERE as well as HAVING both filters out records based on i or to a greater extent than conditions. The departure is, WHERE clause tin mail away entirely hold upwards applied on a static non-aggregated column whereas nosotros volition demand to role HAVING for aggregated columns.
13.) What is a Cursor?
A database Cursor is a command which enables traversal over the rows or records inward the table. This tin mail away hold upwards viewed equally a pointer to i row inward a prepare of rows. Cursor is real much useful for traversing such equally retrieval, add-on as well as removal of database records.
14.) What is a constraint?
Constraint tin mail away hold upwards used to specify the boundary on the information type of table. Constraint tin mail away hold upwards specified spell creating or altering the tabular array statement.
15.) What is an integrity constraint?
Integrity constraint is a dominion that restricts values to a column inward a table.
16.) What is referential integrity constraint?
Maintaining information integrity through a prepare of rules that throttle the values of i or to a greater extent than columns of the tables based on the values of primary cardinal or unique cardinal of the referenced table.
17.) Where the integrity constraints are stored inward information dictionary?
The integrity constraints are stored inward USER_CONSTRAINTS.
18.) How volition you lot activate/deactivate integrity constraints?
The integrity constraints tin mail away hold upwards enabled or disabled yesteryear ALTER TABLE ENABLE CONSTRAINT / DISABLE CONSTRAINT.
19.) If unique cardinal constraint on DATE column is created, volition it validate the rows that are inserted alongside SYSDATE? It won’t, Because SYSDATE format contains fourth dimension attached alongside it.
20.) What’s an SQL injection?
SQL Injection is when course of written report information contains an SQL escape sequence as well as injects a novel SQL query to hold upwards run.
21.) What is a transaction as well as what are ACID properties?
A transaction is a logical unit of measurement of locomote inward which, all the steps must hold upwards performed or none. ACID stands for Atomicity, Consistency, Isolation, as well as Durability. These are the properties of a transaction.
22.) What are properties of a transaction?
Properties of the transaction tin mail away hold upwards summarized equally ACID Properties.
1. Atomicity
A transaction consists of many steps. When all the steps inward a transaction gets completed, it volition popular off reflected inward DB or if whatever footstep fails, all the transactions are rolled back.
2. Consistency
The database volition movement from i consistent dry reason to another, if the transaction succeeds as well as stay inward the master copy state, if the transaction fails.
3. Isolation
Every transaction should operate equally if it is the entirely transaction inward the system.
4. Durability
Once a transaction has completed successfully, the updated rows/records must hold upwards available for all other transactions on a permanent basis.
23.) What is a Database Lock?
Database lock tells a transaction, if the information detail inward questions is currently beingness used yesteryear other transactions.
24.) What are the type of locks?
• Shared Lock: When a shared lock is applied on information item, other transactions tin mail away entirely read the item, only can't write into it.
• Exclusive Lock: When an exclusive lock is applied on information item, other transactions can't read or write into the information item.
25.) What is the sub-query?
Sub-query is a query whose supply values are used inward filtering weather condition of the nous query.
26.) What is correlated sub-query?
Correlated sub-query is a sub-query, which has reference to the nous query.
27.) What are the properties as well as dissimilar Types of Sub-Queries?
Properties of Sub-Query:
• Influenza A virus subtype H5N1 sub-query must hold upwards enclosed inward the parenthesis.
• Influenza A virus subtype H5N1 sub-query must hold upwards pose inward the correct paw of the comparing operator, and
• Influenza A virus subtype H5N1 sub-query cannot incorporate an ORDER-BY clause.
• Influenza A virus subtype H5N1 query tin mail away incorporate to a greater extent than than i sub-query.
Types of Sub-Query:
• Single-row sub-query, where the sub-query returns entirely i row.
• Multiple-row sub-query, where the sub-query returns multiple rows, and
• Multiple column sub-query, where the sub-query returns multiple columns
28.) What is a database link?
Database link is a named path through which a remote database tin mail away hold upwards accessed.
29.) What are the types of cursors?
The dissimilar types of cursors are, Static cursor, dynamic cursor, Forward entirely cursor as well as Keyset driven cursor.
30.) What is Collation?
Collation inward SQL is prepare of rules to create upwards one's hear information stored as well as compared. There are dissimilar types of collation similar Case sensitivity, Accent sensitivity as well as kana sensitivity.
31.) What are all dissimilar types of collation sensitivity?
Following are dissimilar types of collation sensitivity
Case Sensitivity – Influenza A virus subtype H5N1 as well as a as well as B as well as b.
Accent Sensitivity.
Kana Sensitivity – Japanese Kana characters.
Width Sensitivity – Single byte graphic symbol as well as double byte character.
32.) What is Online Transaction Processing (OLTP)?
Online Transaction Processing or OLTP manages transaction based applications which tin mail away hold upwards used for information entry as well as slow retrieval processing of data. This processing makes similar easier on simplicity as well as efficiency. It is faster, to a greater extent than accurate results as well as expenses alongside honour to OTLP.
Example: Bank Transactions on a daily basis.
--------------------------------
1.) Does SQL back upwards programming?
No, SQL doesn't guide keep loop or Conditional statement. It is used similar commanding linguistic communication to access databases.
2.) What is information Definition language?
Data Definition linguistic communication (DDL) allows you lot to CREATE, ALTER as well as DELETE database objects such equally schema, tables, view, sequence etc.
3.) What is information manipulation language?
Data manipulation linguistic communication makes user able to access as well as manipulate data. It is used to perform next operations.
• Insert information into database
• Retrieve information from the database
• Update information inward the database
• Delete information from the database
4.) What is information command language?
Data command linguistic communication allows you lot to command access to the database. It includes 2 commands GRANT as well as REVOKE.
• GRANT: to grant specific user to perform specific task.
• REVOKE: to cancel previously denied or granted permissions.
5.) How exercise you lot implement one-to-one, one-to-many as well as many-to-many relationships spell designing tables?
• One-to-One human relationship tin mail away hold upwards implemented equally a unmarried tabular array as well as rarely equally 2 tables alongside primary as well as unusual cardinal relationships.
• One-to-Many relationships are implemented yesteryear splitting the information into 2 tables alongside primary cardinal as well as unusual cardinal relationships.
• Many-to-Many relationships are implemented using a junction tabular array alongside the keys from both the tables forming the composite primary cardinal of the junction table.
6.) What is a stored procedure?
Stored Procedure is a portion which contains a collection of SQL Queries. The physical care for tin mail away accept inputs, physical care for them as well as mail dorsum output.
7.) What are the advantages a stored procedure?
Stored Procedures are precompiled as well as stored inward the database. This enables the database to execute the queries much faster. Since many queries tin mail away hold upwards included inward a stored procedure, circular trip fourth dimension to execute multiple queries from source code to database as well as dorsum is avoided.
8.) What is recursive stored procedure?
A stored physical care for which calls yesteryear itself until it reaches approximately boundary condition. This recursive portion or physical care for helps programmers to role the same prepare of code whatever reveal of times.
9.) What is a trigger?
When database are inserted, deleted or updated, a physical care for is executed inward reply to that action. Such SQL procedures are called equally trigger.
Example: When a novel educatee is added to the educatee database, novel records should hold upwards created inward the related tables similar Exam, Score as well as Attendance tables.
10.) What is departure betwixt TRUNCATE, DELETE & DROP?
• TRUNCATE commits later on deleting entire tabular array i.e., cannot hold upwards rolled back. Database triggers exercise non burn downward on TRUNCATE
• DELETE allows the filtered deletion. Deleted records tin mail away hold upwards rolled dorsum or committed. Database triggers burn downward on DELETE.
• Drop command is used to drib the tabular array or keys similar primary, unusual from a table.
11.) What is Union, minus as well as Interact commands?
• UNION operator is used to combine the results of 2 tables, as well as it eliminates duplicate rows from the tables.
• MINUS operator is used to supply rows from the starting fourth dimension query only non from the minute query.
• INTERSECT operator is used to supply rows returned yesteryear both the queries.
12.) What is the departure betwixt WHERE clause as well as HAVING clause?
WHERE as well as HAVING both filters out records based on i or to a greater extent than conditions. The departure is, WHERE clause tin mail away entirely hold upwards applied on a static non-aggregated column whereas nosotros volition demand to role HAVING for aggregated columns.
13.) What is a Cursor?
A database Cursor is a command which enables traversal over the rows or records inward the table. This tin mail away hold upwards viewed equally a pointer to i row inward a prepare of rows. Cursor is real much useful for traversing such equally retrieval, add-on as well as removal of database records.
14.) What is a constraint?
Constraint tin mail away hold upwards used to specify the boundary on the information type of table. Constraint tin mail away hold upwards specified spell creating or altering the tabular array statement.
15.) What is an integrity constraint?
Integrity constraint is a dominion that restricts values to a column inward a table.
16.) What is referential integrity constraint?
Maintaining information integrity through a prepare of rules that throttle the values of i or to a greater extent than columns of the tables based on the values of primary cardinal or unique cardinal of the referenced table.
17.) Where the integrity constraints are stored inward information dictionary?
The integrity constraints are stored inward USER_CONSTRAINTS.
18.) How volition you lot activate/deactivate integrity constraints?
The integrity constraints tin mail away hold upwards enabled or disabled yesteryear ALTER TABLE ENABLE CONSTRAINT / DISABLE CONSTRAINT.
19.) If unique cardinal constraint on DATE column is created, volition it validate the rows that are inserted alongside SYSDATE? It won’t, Because SYSDATE format contains fourth dimension attached alongside it.
20.) What’s an SQL injection?
SQL Injection is when course of written report information contains an SQL escape sequence as well as injects a novel SQL query to hold upwards run.
21.) What is a transaction as well as what are ACID properties?
A transaction is a logical unit of measurement of locomote inward which, all the steps must hold upwards performed or none. ACID stands for Atomicity, Consistency, Isolation, as well as Durability. These are the properties of a transaction.
22.) What are properties of a transaction?
Properties of the transaction tin mail away hold upwards summarized equally ACID Properties.
1. Atomicity
A transaction consists of many steps. When all the steps inward a transaction gets completed, it volition popular off reflected inward DB or if whatever footstep fails, all the transactions are rolled back.
2. Consistency
The database volition movement from i consistent dry reason to another, if the transaction succeeds as well as stay inward the master copy state, if the transaction fails.
3. Isolation
Every transaction should operate equally if it is the entirely transaction inward the system.
4. Durability
Once a transaction has completed successfully, the updated rows/records must hold upwards available for all other transactions on a permanent basis.
23.) What is a Database Lock?
Database lock tells a transaction, if the information detail inward questions is currently beingness used yesteryear other transactions.
24.) What are the type of locks?
• Shared Lock: When a shared lock is applied on information item, other transactions tin mail away entirely read the item, only can't write into it.
• Exclusive Lock: When an exclusive lock is applied on information item, other transactions can't read or write into the information item.
25.) What is the sub-query?
Sub-query is a query whose supply values are used inward filtering weather condition of the nous query.
26.) What is correlated sub-query?
Correlated sub-query is a sub-query, which has reference to the nous query.
27.) What are the properties as well as dissimilar Types of Sub-Queries?
Properties of Sub-Query:
• Influenza A virus subtype H5N1 sub-query must hold upwards enclosed inward the parenthesis.
• Influenza A virus subtype H5N1 sub-query must hold upwards pose inward the correct paw of the comparing operator, and
• Influenza A virus subtype H5N1 sub-query cannot incorporate an ORDER-BY clause.
• Influenza A virus subtype H5N1 query tin mail away incorporate to a greater extent than than i sub-query.
Types of Sub-Query:
• Single-row sub-query, where the sub-query returns entirely i row.
• Multiple-row sub-query, where the sub-query returns multiple rows, and
• Multiple column sub-query, where the sub-query returns multiple columns
28.) What is a database link?
Database link is a named path through which a remote database tin mail away hold upwards accessed.
29.) What are the types of cursors?
The dissimilar types of cursors are, Static cursor, dynamic cursor, Forward entirely cursor as well as Keyset driven cursor.
30.) What is Collation?
Collation inward SQL is prepare of rules to create upwards one's hear information stored as well as compared. There are dissimilar types of collation similar Case sensitivity, Accent sensitivity as well as kana sensitivity.
31.) What are all dissimilar types of collation sensitivity?
Following are dissimilar types of collation sensitivity
Case Sensitivity – Influenza A virus subtype H5N1 as well as a as well as B as well as b.
Accent Sensitivity.
Kana Sensitivity – Japanese Kana characters.
Width Sensitivity – Single byte graphic symbol as well as double byte character.
32.) What is Online Transaction Processing (OLTP)?
Online Transaction Processing or OLTP manages transaction based applications which tin mail away hold upwards used for information entry as well as slow retrieval processing of data. This processing makes similar easier on simplicity as well as efficiency. It is faster, to a greater extent than accurate results as well as expenses alongside honour to OTLP.
Example: Bank Transactions on a daily basis.
--------------------------------
Prepared by: V. Raga Malika