SQL Quetions:
---------------
1) What is View in database?
Views are logical virtual table created by “select query” but the result is not stored anywhere in the disk and
every time we need to fire the query when we need data, so always we get updated or latest data from original tables.
-->index based columns are faster than non index based column
What is Materialized View in database?
Materialized views are also logical view of our data driven by select query but the result of the query will get stored in the table or disk.
View vs Materialized View?
1) In Views query result is not stored in the disk or database but Materialized view allow to store query result in disk or table.
2) when we create view using any table, rowid of view is same as original table but in case of Materialized view rowid is different.
3) View we always get latest data but in case of Materialized view we need to refresh the view for getting latest data.
4) Performance of View is less than Materialized view.
5) In case of Materialized view we need extra trigger or some automatic method so that we can keep MV refreshed, this is not required for views in database.
2) Difference between primary key and unique key in SQL
1) Unique key in a table can be null, at-least one but primary key can not be null in any table in relation database like MySQL.
2) Primary key can be combination of more than one unique keys in same table.
3) There can be only one primary key per table in relation database.
3) Delete and truncate command in SQL?
1. truncate is fast delete is slow.
2. truncate doesn't do logging delete logs on per row basis.
3. rollback is possible with delete not with truncate until specifically supported by vendor.
4. truncate doesn't fire trigger, delete does.
5. Don't delete, truncate it when it comes to purge tables.
6. truncate reset identity column in table if any, delete doesn't.
7. truncate is DDL while delete is DML (use this when you are writing exam)
8. truncate doesn't support where clause, delete does.
4) SQL query to copy, duplicate or backup table in MySQL, Oracle and PostgreSQL database?
-- showing list of table before creating backup.
SHOW TABLES;
-- creating backup of aircraft table by selecting all data.
CREATE TABLE aircraft_backup AS SELECT * FROM aircraft;
How to create table from another table in SQL?
create table destination_table as select * from source_table;
--copying data from one table to another
insert into ORDERS_TMP select * from ORDERS
--Dropping columns from the table
ALTER TABLE `database`.`ORDERS` DROP COLUMN `BRAND`;
5) Difference between WHERE vs HAVING clause in SQL - GROUP BY Comparison with Example?
1) Apart from SELECT queries, you can use WHERE clause with UPDATE and DELETE clause but HAVING clause can only be used with SELECT query. For example following query, which involve WHERE clause will work but other which uses HAVING clause will not work :
2) WHERE clause is used for filtering rows and it applies on each and every row, while HAVING clause is used to filter groups in SQL.
3) When WHERE and HAVING clause are used together in a SELECT query with aggregate function.
6) How to find name of all tables in MySQL database?
SELECT table_name FROM information_schema.tables WHERE table_type = 'base table' AND table_schema='test';
7) How to find second highest or maximum salary of Employee in SQL - Interview question?
SELECT max(salary) FROM Employee WHERE salary NOT IN (SELECT max(salary) FROM Employee);
8) how to create or call a Stored Procedure in sql ?
Create and Call MySQL stored procedure with IN Parameters.
mysql> DELIMITER //
mysql> create procedure usp_totalEmployeeByDeparment(IN id INT)
-> begin
-> select count(*) as total from employee where dept_id = id;
-> end//
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;
mysql> call usp_totalEmployeeByDeparment(2);
mysql> call usp_GetEmployeeName(103, @name);
9) How to find duplicate records in a table on database - SQL tips?
Duplicate Records by Name ---> select name, count(name) from contacts group by name.
Duplicate Records by Name,phone --> select name, count(name) from contacts group by name, phone;
10) What is transaction in database?
Database transaction tutorial example in sqlDatabase transaction is collection of SQL queries which forms a logical one task.
commit and rollback.
ACID Properties of database transaction
Atomicity --> Database transaction has to be atomic means either all steps of transaction completes or none of them.
Consistency --> Transaction must leave database in consistent state even if it succeed or rollback.
Isolation --> Two database transactions happening at same time should not affect each other and has consistent view of database.
This is achieved by using isolation levels in database.
Durability --> Data has to be persisted successfully in database once transaction completed successfully.
Isolation level Dirty reads Non-repeatable reads Phantoms
Read Uncommitted may occur may occur may occur
Read Committed - may occur may occur
Repeatable Read - - may occur
Serializable - - -
What is the best isolation level I can use?
the ultimate level is SERIALIZABLE.
11) What is Normalization?
Normalization is a method to remove all these anomalies and bring database to consistent state and free from any kinds of anomalies.
First Normal Form (1NF):Each field in a table contains different information. For example, in an employee list, each table would contain only one birthdate field.
Second Normal Form (2NF):Each field in a table that is not a determiner of the contents of another field must itself be a function of the other fields in the table.
Third Normal Form (3NF): No duplicate information is permitted. So, for example, if two tables both require a birthdate field, the birthdate information would be separated into a separate table, and the two other tables would then access the birthdate information via an index field in the birthdate table. Any change to a birthdate would automatically be reflect in all tables that link to the birthdate table.
There are additional normalization levels, such as Boyce Codd Normal Form (BCNF), fourth normal form (4NF) and fifth normal form (5NF). While normalization makes databases more efficient to maintain, they can also make them more complex because data is separated into so many different tables.
12) Functions vs Procedures
Basic Difference
1) Function must return a value but in Stored Procedure it is optional( Procedure can return zero or n values).
2) Functions can have only input parameters for it whereas Procedures can have input/output parameters .
3) Functions can be called from Procedure whereas Procedures cannot be called from Function.
Advance Difference
1) Procedure allows SELECT as well as DML(INSERT/UPDATE/DELETE) statement in it whereas Function allows only SELECT statement in it.
2) Procedures can not be utilized in a SELECT statement whereas Function can be embedded in a SELECT statement.
3) Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section whereas Function can be.
4) The most important feature of stored procedures over function is to retention and reuse the execution plan while in case of function it will be compiled every time.
5) Functions that return tables can be treated as another rowset. This can be used in JOINs with other tables.
6) Inline Function can be though of as views that take parameters and can be used in JOINs and other Rowset operations.
7) Exception can be handled by try-catch block in a Procedure whereas try-catch block cannot be used in a Function.
8) We can go for Transaction Management in Procedure whereas we can't go in Function.
13) Query to get nth(3rd) Highest Salary?
Select TOP 1 Salary as '3rd Highest Salary'
from (SELECT DISTINCT TOP 3 Salary from Employee ORDER BY Salary DESC)
a ORDER BY Salary ASC
14) Remove duplicate records from the table.
#1. DELETE FROM table WHERE col1 IN ( SELECT id FROM table GROUP BY id HAVING ( COUNT(col1) > 1 )).
#2. SELECT the DISTINCT records into a New Table
TRUNCATE the Old Table
MERGE the New Table back into the Old Table
15) What are the indexes and their types?
Index can be thought as index of the book that is used for fast retrieval of information.
Index uses one or more column index keys and pointers to the record to locate record.
Index is used to speed up query performance.
Both exist as B-tree structure.
Kind of the indexes are clustered and non-clustered.
Clustered index
Clustered index exists as sorted row on disk.
Clustered index re-orders the table record.
Clustered index contains record in the leaf level of the B-tree.
There can be only one Clustered index possible in a table.
Non-clustered
Non-clustered index is the index in which logical order doesn’t match with physical order of stored data on disk.
Non-clustered index contains index key to the table records in the leaf level.
There can be one or more Non-clustered indexes in a table.
---------------
1) What is View in database?
Views are logical virtual table created by “select query” but the result is not stored anywhere in the disk and
every time we need to fire the query when we need data, so always we get updated or latest data from original tables.
-->index based columns are faster than non index based column
What is Materialized View in database?
Materialized views are also logical view of our data driven by select query but the result of the query will get stored in the table or disk.
View vs Materialized View?
1) In Views query result is not stored in the disk or database but Materialized view allow to store query result in disk or table.
2) when we create view using any table, rowid of view is same as original table but in case of Materialized view rowid is different.
3) View we always get latest data but in case of Materialized view we need to refresh the view for getting latest data.
4) Performance of View is less than Materialized view.
5) In case of Materialized view we need extra trigger or some automatic method so that we can keep MV refreshed, this is not required for views in database.
2) Difference between primary key and unique key in SQL
1) Unique key in a table can be null, at-least one but primary key can not be null in any table in relation database like MySQL.
2) Primary key can be combination of more than one unique keys in same table.
3) There can be only one primary key per table in relation database.
3) Delete and truncate command in SQL?
1. truncate is fast delete is slow.
2. truncate doesn't do logging delete logs on per row basis.
3. rollback is possible with delete not with truncate until specifically supported by vendor.
4. truncate doesn't fire trigger, delete does.
5. Don't delete, truncate it when it comes to purge tables.
6. truncate reset identity column in table if any, delete doesn't.
7. truncate is DDL while delete is DML (use this when you are writing exam)
8. truncate doesn't support where clause, delete does.
4) SQL query to copy, duplicate or backup table in MySQL, Oracle and PostgreSQL database?
-- showing list of table before creating backup.
SHOW TABLES;
-- creating backup of aircraft table by selecting all data.
CREATE TABLE aircraft_backup AS SELECT * FROM aircraft;
How to create table from another table in SQL?
create table destination_table as select * from source_table;
--copying data from one table to another
insert into ORDERS_TMP select * from ORDERS
--Dropping columns from the table
ALTER TABLE `database`.`ORDERS` DROP COLUMN `BRAND`;
5) Difference between WHERE vs HAVING clause in SQL - GROUP BY Comparison with Example?
1) Apart from SELECT queries, you can use WHERE clause with UPDATE and DELETE clause but HAVING clause can only be used with SELECT query. For example following query, which involve WHERE clause will work but other which uses HAVING clause will not work :
2) WHERE clause is used for filtering rows and it applies on each and every row, while HAVING clause is used to filter groups in SQL.
3) When WHERE and HAVING clause are used together in a SELECT query with aggregate function.
6) How to find name of all tables in MySQL database?
SELECT table_name FROM information_schema.tables WHERE table_type = 'base table' AND table_schema='test';
7) How to find second highest or maximum salary of Employee in SQL - Interview question?
SELECT max(salary) FROM Employee WHERE salary NOT IN (SELECT max(salary) FROM Employee);
8) how to create or call a Stored Procedure in sql ?
Create and Call MySQL stored procedure with IN Parameters.
mysql> DELIMITER //
mysql> create procedure usp_totalEmployeeByDeparment(IN id INT)
-> begin
-> select count(*) as total from employee where dept_id = id;
-> end//
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;
mysql> call usp_totalEmployeeByDeparment(2);
mysql> call usp_GetEmployeeName(103, @name);
9) How to find duplicate records in a table on database - SQL tips?
Duplicate Records by Name ---> select name, count(name) from contacts group by name.
Duplicate Records by Name,phone --> select name, count(name) from contacts group by name, phone;
10) What is transaction in database?
Database transaction tutorial example in sqlDatabase transaction is collection of SQL queries which forms a logical one task.
commit and rollback.
ACID Properties of database transaction
Atomicity --> Database transaction has to be atomic means either all steps of transaction completes or none of them.
Consistency --> Transaction must leave database in consistent state even if it succeed or rollback.
Isolation --> Two database transactions happening at same time should not affect each other and has consistent view of database.
This is achieved by using isolation levels in database.
Durability --> Data has to be persisted successfully in database once transaction completed successfully.
Isolation level Dirty reads Non-repeatable reads Phantoms
Read Uncommitted may occur may occur may occur
Read Committed - may occur may occur
Repeatable Read - - may occur
Serializable - - -
What is the best isolation level I can use?
the ultimate level is SERIALIZABLE.
11) What is Normalization?
Normalization is a method to remove all these anomalies and bring database to consistent state and free from any kinds of anomalies.
First Normal Form (1NF):Each field in a table contains different information. For example, in an employee list, each table would contain only one birthdate field.
Second Normal Form (2NF):Each field in a table that is not a determiner of the contents of another field must itself be a function of the other fields in the table.
Third Normal Form (3NF): No duplicate information is permitted. So, for example, if two tables both require a birthdate field, the birthdate information would be separated into a separate table, and the two other tables would then access the birthdate information via an index field in the birthdate table. Any change to a birthdate would automatically be reflect in all tables that link to the birthdate table.
There are additional normalization levels, such as Boyce Codd Normal Form (BCNF), fourth normal form (4NF) and fifth normal form (5NF). While normalization makes databases more efficient to maintain, they can also make them more complex because data is separated into so many different tables.
12) Functions vs Procedures
Basic Difference
1) Function must return a value but in Stored Procedure it is optional( Procedure can return zero or n values).
2) Functions can have only input parameters for it whereas Procedures can have input/output parameters .
3) Functions can be called from Procedure whereas Procedures cannot be called from Function.
Advance Difference
1) Procedure allows SELECT as well as DML(INSERT/UPDATE/DELETE) statement in it whereas Function allows only SELECT statement in it.
2) Procedures can not be utilized in a SELECT statement whereas Function can be embedded in a SELECT statement.
3) Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section whereas Function can be.
4) The most important feature of stored procedures over function is to retention and reuse the execution plan while in case of function it will be compiled every time.
5) Functions that return tables can be treated as another rowset. This can be used in JOINs with other tables.
6) Inline Function can be though of as views that take parameters and can be used in JOINs and other Rowset operations.
7) Exception can be handled by try-catch block in a Procedure whereas try-catch block cannot be used in a Function.
8) We can go for Transaction Management in Procedure whereas we can't go in Function.
13) Query to get nth(3rd) Highest Salary?
Select TOP 1 Salary as '3rd Highest Salary'
from (SELECT DISTINCT TOP 3 Salary from Employee ORDER BY Salary DESC)
a ORDER BY Salary ASC
14) Remove duplicate records from the table.
#1. DELETE FROM table WHERE col1 IN ( SELECT id FROM table GROUP BY id HAVING ( COUNT(col1) > 1 )).
#2. SELECT the DISTINCT records into a New Table
TRUNCATE the Old Table
MERGE the New Table back into the Old Table
15) What are the indexes and their types?
Index can be thought as index of the book that is used for fast retrieval of information.
Index uses one or more column index keys and pointers to the record to locate record.
Index is used to speed up query performance.
Both exist as B-tree structure.
Kind of the indexes are clustered and non-clustered.
Clustered index
Clustered index exists as sorted row on disk.
Clustered index re-orders the table record.
Clustered index contains record in the leaf level of the B-tree.
There can be only one Clustered index possible in a table.
Non-clustered
Non-clustered index is the index in which logical order doesn’t match with physical order of stored data on disk.
Non-clustered index contains index key to the table records in the leaf level.
There can be one or more Non-clustered indexes in a table.
No comments:
Post a Comment