Basic SQL Interview questions and answers:
SOURCE: http://www.techinterviews.com/interview-questions/database/
QUESITON:Name few Data types in database?
ANSWER:
Max. columns in a table is 255. Max.
Char size is 255,
Long is 64K and
Number is 38 digits.Cannot Query on a long column.Char,
Varchar2 Max. size is 2000 and default is 1 byte.
Number(p,s) p is precision range 1 to 38, s is scale -84 to 127.
Long Character data of variable length upto 2GB.
Date Range from Jan 4712 BC to Dec 4712 AD.
Raw Stores Binary data (Graphics Image and Digitized Sound).
Max. is 255 bytes.
QUESTION: WHAT IS TABLE CONSTRAINTS, NAME FEW TABLE CONSTRAINTS.
ANSWER:
not null - value in a column must not be NULL
unique - value(s) in specified column(s) must be unique for each row in a table
primary key - value(s) in specified column(s) must be unique for each row in a table and not be NULL; normally each table in a database should have a primary key - it is used to identify individual records
foreign key - value(s) in specified column(s) must reference an existing record in another table (via it's primary key or some other unique constraint)
check - an expression is specified, which must evaluate to true for constraint to be satisfiedA constraint allows you to apply simple referential integrity checks to a table. There are four primary types of constraints that are currently supported by SQL Server:
PRIMARY/UNIQUE - enforces uniqueness of a particular table column.
DEFAULT - specifies a default value for a column in case an insert operation does not provide one. FOREIGN KEY - validates that every value in a column exists in a column of another table.
CHECK - checks that every value stored in a column is in some specified list. Each type of constraint performs a specific type of action. Default is not a constraint.
NOT NULL is one more constraint which does not allow values in the specific column to be null. And also it the only constraint which is not a table level constraint.
QUESTION: WHAT IS DATA INTEGRITY? HOW DO YOU ENSURE DATA INTEGRITY IS ENFORCED IN A TABLE?
ANSWER: NEED TO VALIDATE PROPER CONSTRAINT LIKE PRIMARY KEY, FORIGEN KEY IS DEFINED FOR THE TABLE.
QUESTION: WHAT IS referential integrity mean?
ANSWER: PRIMARY KEY, FORIGEN KEY, COMPOSITE KEY, SURROGATE KEY, CANDIDATE KEY.
QUESTION: NAME FEW SQL CLAUSES:
Where
HAVING
IN
ANY
TOP
ROWNUM
QUESTION: WHAT IS AGGREGATE FUNCTION, Name few aggregate function?
COUNT
SUM
AVG
MIN
MAX
QUESTION: What are the rules when you want to use aggregate functions?
ANSWER: BASIC RULE IS TO USE "GROUP BY" WHILE USING AGGREGATE FUNCTION IN A QUERY.
QUESTION: What is the difference between where and having clause?
ANSWER: in SQL Where filters data on lowest row level.
Having filters data after group by has been performed so it filters on "groups""Where" is a kind of restiriction statement.
You use where clause to restrict all the data from DB.Where clause is using before result retrieving.
But Having clause is using after retrieving the data.Having clause is a kind of filtering command.
QUESTION: IN WHICH SCENARIO WE WILL USE "IN" OPERATOR. CAN YOU EXPLAIN WHAT IS THE PURPOSE OF IT.
QUESTION: WHAT IS THE DIFFERENCE BETWEEN "IN" AND "EXISTS"
QUESTION: SAMPLE QUERY TO FIND SUM OF SALES FOR EACH DEPARTMENT. LETS SAY TABLE NAME IS SALES_ORDER
QUESTION: SAMPLE QUERY USING WHERE CLAUSE and HAVING CLAUSE?
SELECT department, SUM(sales) as "Total sales" FROM order_details GROUP BY department HAVING SUM(sales) > 1000;
SELECT department, COUNT(*) as "Number of employees" FROM employees WHERE salary > 25000 GROUP BY department HAVING COUNT(*) > 10;
QUESTION: WHAT IS UNION, WHAT IS THE DIFFERENCE BETWEEN UNION ALL
QUESTION: HOW DO YOU COMPARE DATA BETWEEN SOURCE and TARGET
ANSWER: MINUSQUESTIONS: How can I create a table from another table with copying DATA and STATS
ANSWER:CREATE TABLE new_table AS (SELECT * FROM old_table WHERE 1=2) WITH DATA AND STATS;
QUESTION: WHAT IS DIFFERENCE BETWEEN DELETE and TRUNCATE
QUESTION: What type of joins have you used?
ANSWER: Joins knowledge is MUST HAVE. This interview question is quite nice because most people used inner join and (left/right) outer join which is rather mandatory knowledge but those more experienced will also mention cross join and self-join. In SQL Server you can also get full outer join.
QUESTION: How can you combine two tables/views together? For instance one table contains 100 rows and the other one contains 200 rows, have exactly the same fields and you want to show a query with all data (300 rows). This sql interview question can get complicated.
ANSWER: You use UNION operator. You can drill down this question and ask what is the different between UNION and UNION ALL (the first one removes duplicates (not always desirable)… in other words shows only DISTINCT rows….Union ALL just combines so it is also faster). More tricky question are how to sort the view (you use order by at the last query), how to name fields so they appear in query results/view schema (first query field names are used). How to filter groups when you use union using SQL (you would create separate query or use common table expression (CTE) or use unions in from with ().
QUESTION: WHAT IS A DERIVED TABLE?
QUESTION: WHAT IS SUBQUERY, HOW TO WRITE SYNTAX.
QUESTION: How would apply date range filter?
ANSWER: This is tricky question. You can use simple condition >= and <= or similar or use between/and but the trick is to know your exact data type. Sometimes date fields contain time and that is where the query can go wrong so it is recommended to use some date related functions to remove the time issue. In SQL Server common function to do that is datediff function. You also have to be aware of different time zones and server time zone.
QUESTION: What type of wildcards have you used? This is usually one of mandatory sql interview question.
ANSWER: First question is what is a wildcard? Wildcards are special characters that allow matching string without having exact match. In simple word they work like contains or begins with. Wildcard characters are software specific and in SQL Server we have % which represent any groups of characters, _ that represent one character (any) and you also get [] where we can [ab] which means characters with letter a or b in a specific place.
QUESTION: How do you find orphans?
ANSWER: This is more comprehensive SQL and database interview question. First of all we test if the candidate knows what an orphan is. An Orphan is a foreign key value in "child table" which doesn’t exist in primary key column in parent table. To get it you can use left outer join (important: child table on left side) with join condition on primary/foreign key columns and with where clause where primary key is null. Adding distinct or count to select is common practise. In SQL Server you can also you except which will show all unique values from first query that don't exist in second query.
QUESTION: How would you solve the following sql queries using today's date? First day of previous month To return first day of previous month using T-SQL we can use the following statement: SELECT DATEADD(m,-1, Dateadd(d,1-DATEPART(d,getdate()),GETDATE())) AS [First Day of Previous Month]1) Our today's date is 20th of Feb. We substract number of days in the month from our date (20) and we get last day of previous month.2) We add one day to get first day of current month.3) We go one month back so first day of the month stays the same and Feb changes to Jan. First day of current monthLast day of previous monthLast day of current month
ANSWER: These tasks require good grasp of SQL functions but also logical thinking which is one of the primary skills involved in solving sql questions. In this case I provided links to actual answers with code samples. Experienced people should give correct answer almost immediately. People with less experience might need more time or would require some help (Google).
QUESTION: How do you find duplicate record in a table.
ANSWER: SELECT name, email COUNT(name) AS NumOccurName, COUNT(email) as NumOccurEmail FROM users GROUP BY Name, Email HAVING ( COUNT(name) > 1 ) AND ( COUNT(email) > 1)
QUESTION: How do you find 2nd maximum salary of an employee
QUESTION: What is "normalization"? "Denormalization"? Why do you sometimes want to denormalize?
ANSWER: Normalizing data means eliminating redundant information from a table and organizing the data so that future changes to the table are easier. Denormalization means allowing redundancy in a table. The main benefit of denormalization is improved performance with simplified data retrieval and manipulation. This is done by reduction in the number of joins needed for data processing.
QUESTION: What types of index data structures can you have? - An index helps to faster search values in tables. The three most commonly used index-types are: - B-Tree: builds a tree of possible values with a list of row IDs that have the leaf value. Needs a lot of space and is the default index type for most databases. - Bitmap: string of bits for each possible value of the column. Each bit string has one bit for each row. Needs only few space and is very fast.(however, domain of value cannot be large, e.g. SEX(m,f); degree(BS,MS,PHD) - Hash: A hashing algorithm is used to assign a set of characters to represent a text string such as a composite of keys or partial keys, and compresses the underlying data. Takes longer to build and is supported by relatively few databases.
QUESTION: WHAT IS TRIGGER?
ANSWER: Triggers are stored procedures created in order to enforce integrity rules in a database. A trigger is executed every time a data-modification operation occurs (i.e., insert, update or delete). Triggers are executed automatically on occurrence of one of the data-modification operations. A trigger is a database object directly associated with a particular table. It fires whenever a specific statement/type of statement is issued against that table. The types of statements are insert,update,delete and query statements. Basically, trigger is a set of SQL statements A trigger is a solution to the restrictions of a constraint. For instance: 1.A database column cannot carry PSEUDO columns as criteria where a trigger can. 2. A database constraint cannot refer old and new values for a row where a trigger can.WHAT IS INDEX?DIFFERENCE BETWEEN CLUSTERED INDEX / NON CLUSTERED INDEX.
QUESTION: What are the tradeoffs with having indexes?
ANSWER:
1. Faster selects, slower updates.
2. Extra storage space to store indexes. Updates are slower because in addition to updating the table you have to update the index.WHAT IS TABLE PARTITIONS?
QUESTION: Why can a "group by" or "order by" clause be expensive to process?
ANSWER: Processing of "group by" or "order by" clause often requires creation of Temporary tables to process the results of the query. Which depending of the result set can be very expensive.
QUESTION: What is a SQL view?
ANSWER: An output of a query can be stored as a view. View acts like small table which meets our criterion. View is a pre complied SQL query which is used to select data from one or more tables. A view is like a table but it doesn’t physically take any space. View is a good way to present data in a particular format if you use that query quite often.View can also be used to restrict users from accessing the tables directly.
QUESTION: WHAT IS MATERIALISTIC VIEW?
SOURCE: http://www.techinterviews.com/interview-questions/database/
QUESITON:Name few Data types in database?
ANSWER:
Max. columns in a table is 255. Max.
Char size is 255,
Long is 64K and
Number is 38 digits.Cannot Query on a long column.Char,
Varchar2 Max. size is 2000 and default is 1 byte.
Number(p,s) p is precision range 1 to 38, s is scale -84 to 127.
Long Character data of variable length upto 2GB.
Date Range from Jan 4712 BC to Dec 4712 AD.
Raw Stores Binary data (Graphics Image and Digitized Sound).
Max. is 255 bytes.
QUESTION: WHAT IS TABLE CONSTRAINTS, NAME FEW TABLE CONSTRAINTS.
ANSWER:
not null - value in a column must not be NULL
unique - value(s) in specified column(s) must be unique for each row in a table
primary key - value(s) in specified column(s) must be unique for each row in a table and not be NULL; normally each table in a database should have a primary key - it is used to identify individual records
foreign key - value(s) in specified column(s) must reference an existing record in another table (via it's primary key or some other unique constraint)
check - an expression is specified, which must evaluate to true for constraint to be satisfiedA constraint allows you to apply simple referential integrity checks to a table. There are four primary types of constraints that are currently supported by SQL Server:
PRIMARY/UNIQUE - enforces uniqueness of a particular table column.
DEFAULT - specifies a default value for a column in case an insert operation does not provide one. FOREIGN KEY - validates that every value in a column exists in a column of another table.
CHECK - checks that every value stored in a column is in some specified list. Each type of constraint performs a specific type of action. Default is not a constraint.
NOT NULL is one more constraint which does not allow values in the specific column to be null. And also it the only constraint which is not a table level constraint.
QUESTION: WHAT IS DATA INTEGRITY? HOW DO YOU ENSURE DATA INTEGRITY IS ENFORCED IN A TABLE?
ANSWER: NEED TO VALIDATE PROPER CONSTRAINT LIKE PRIMARY KEY, FORIGEN KEY IS DEFINED FOR THE TABLE.
QUESTION: WHAT IS referential integrity mean?
ANSWER: PRIMARY KEY, FORIGEN KEY, COMPOSITE KEY, SURROGATE KEY, CANDIDATE KEY.
QUESTION: NAME FEW SQL CLAUSES:
Where
HAVING
IN
ANY
TOP
ROWNUM
QUESTION: WHAT IS AGGREGATE FUNCTION, Name few aggregate function?
COUNT
SUM
AVG
MIN
MAX
QUESTION: What are the rules when you want to use aggregate functions?
ANSWER: BASIC RULE IS TO USE "GROUP BY" WHILE USING AGGREGATE FUNCTION IN A QUERY.
QUESTION: What is the difference between where and having clause?
ANSWER: in SQL Where filters data on lowest row level.
Having filters data after group by has been performed so it filters on "groups""Where" is a kind of restiriction statement.
You use where clause to restrict all the data from DB.Where clause is using before result retrieving.
But Having clause is using after retrieving the data.Having clause is a kind of filtering command.
QUESTION: IN WHICH SCENARIO WE WILL USE "IN" OPERATOR. CAN YOU EXPLAIN WHAT IS THE PURPOSE OF IT.
QUESTION: WHAT IS THE DIFFERENCE BETWEEN "IN" AND "EXISTS"
QUESTION: SAMPLE QUERY TO FIND SUM OF SALES FOR EACH DEPARTMENT. LETS SAY TABLE NAME IS SALES_ORDER
QUESTION: SAMPLE QUERY USING WHERE CLAUSE and HAVING CLAUSE?
SELECT department, SUM(sales) as "Total sales" FROM order_details GROUP BY department HAVING SUM(sales) > 1000;
SELECT department, COUNT(*) as "Number of employees" FROM employees WHERE salary > 25000 GROUP BY department HAVING COUNT(*) > 10;
QUESTION: WHAT IS UNION, WHAT IS THE DIFFERENCE BETWEEN UNION ALL
QUESTION: HOW DO YOU COMPARE DATA BETWEEN SOURCE and TARGET
ANSWER: MINUSQUESTIONS: How can I create a table from another table with copying DATA and STATS
ANSWER:CREATE TABLE new_table AS (SELECT * FROM old_table WHERE 1=2) WITH DATA AND STATS;
QUESTION: WHAT IS DIFFERENCE BETWEEN DELETE and TRUNCATE
QUESTION: What type of joins have you used?
ANSWER: Joins knowledge is MUST HAVE. This interview question is quite nice because most people used inner join and (left/right) outer join which is rather mandatory knowledge but those more experienced will also mention cross join and self-join. In SQL Server you can also get full outer join.
QUESTION: How can you combine two tables/views together? For instance one table contains 100 rows and the other one contains 200 rows, have exactly the same fields and you want to show a query with all data (300 rows). This sql interview question can get complicated.
ANSWER: You use UNION operator. You can drill down this question and ask what is the different between UNION and UNION ALL (the first one removes duplicates (not always desirable)… in other words shows only DISTINCT rows….Union ALL just combines so it is also faster). More tricky question are how to sort the view (you use order by at the last query), how to name fields so they appear in query results/view schema (first query field names are used). How to filter groups when you use union using SQL (you would create separate query or use common table expression (CTE) or use unions in from with ().
QUESTION: WHAT IS A DERIVED TABLE?
QUESTION: WHAT IS SUBQUERY, HOW TO WRITE SYNTAX.
QUESTION: How would apply date range filter?
ANSWER: This is tricky question. You can use simple condition >= and <= or similar or use between/and but the trick is to know your exact data type. Sometimes date fields contain time and that is where the query can go wrong so it is recommended to use some date related functions to remove the time issue. In SQL Server common function to do that is datediff function. You also have to be aware of different time zones and server time zone.
QUESTION: What type of wildcards have you used? This is usually one of mandatory sql interview question.
ANSWER: First question is what is a wildcard? Wildcards are special characters that allow matching string without having exact match. In simple word they work like contains or begins with. Wildcard characters are software specific and in SQL Server we have % which represent any groups of characters, _ that represent one character (any) and you also get [] where we can [ab] which means characters with letter a or b in a specific place.
QUESTION: How do you find orphans?
ANSWER: This is more comprehensive SQL and database interview question. First of all we test if the candidate knows what an orphan is. An Orphan is a foreign key value in "child table" which doesn’t exist in primary key column in parent table. To get it you can use left outer join (important: child table on left side) with join condition on primary/foreign key columns and with where clause where primary key is null. Adding distinct or count to select is common practise. In SQL Server you can also you except which will show all unique values from first query that don't exist in second query.
QUESTION: How would you solve the following sql queries using today's date? First day of previous month To return first day of previous month using T-SQL we can use the following statement: SELECT DATEADD(m,-1, Dateadd(d,1-DATEPART(d,getdate()),GETDATE())) AS [First Day of Previous Month]1) Our today's date is 20th of Feb. We substract number of days in the month from our date (20) and we get last day of previous month.2) We add one day to get first day of current month.3) We go one month back so first day of the month stays the same and Feb changes to Jan. First day of current monthLast day of previous monthLast day of current month
ANSWER: These tasks require good grasp of SQL functions but also logical thinking which is one of the primary skills involved in solving sql questions. In this case I provided links to actual answers with code samples. Experienced people should give correct answer almost immediately. People with less experience might need more time or would require some help (Google).
QUESTION: How do you find duplicate record in a table.
ANSWER: SELECT name, email COUNT(name) AS NumOccurName, COUNT(email) as NumOccurEmail FROM users GROUP BY Name, Email HAVING ( COUNT(name) > 1 ) AND ( COUNT(email) > 1)
QUESTION: How do you find 2nd maximum salary of an employee
QUESTION: What is "normalization"? "Denormalization"? Why do you sometimes want to denormalize?
ANSWER: Normalizing data means eliminating redundant information from a table and organizing the data so that future changes to the table are easier. Denormalization means allowing redundancy in a table. The main benefit of denormalization is improved performance with simplified data retrieval and manipulation. This is done by reduction in the number of joins needed for data processing.
QUESTION: What types of index data structures can you have? - An index helps to faster search values in tables. The three most commonly used index-types are: - B-Tree: builds a tree of possible values with a list of row IDs that have the leaf value. Needs a lot of space and is the default index type for most databases. - Bitmap: string of bits for each possible value of the column. Each bit string has one bit for each row. Needs only few space and is very fast.(however, domain of value cannot be large, e.g. SEX(m,f); degree(BS,MS,PHD) - Hash: A hashing algorithm is used to assign a set of characters to represent a text string such as a composite of keys or partial keys, and compresses the underlying data. Takes longer to build and is supported by relatively few databases.
QUESTION: WHAT IS TRIGGER?
ANSWER: Triggers are stored procedures created in order to enforce integrity rules in a database. A trigger is executed every time a data-modification operation occurs (i.e., insert, update or delete). Triggers are executed automatically on occurrence of one of the data-modification operations. A trigger is a database object directly associated with a particular table. It fires whenever a specific statement/type of statement is issued against that table. The types of statements are insert,update,delete and query statements. Basically, trigger is a set of SQL statements A trigger is a solution to the restrictions of a constraint. For instance: 1.A database column cannot carry PSEUDO columns as criteria where a trigger can. 2. A database constraint cannot refer old and new values for a row where a trigger can.WHAT IS INDEX?DIFFERENCE BETWEEN CLUSTERED INDEX / NON CLUSTERED INDEX.
QUESTION: What are the tradeoffs with having indexes?
ANSWER:
1. Faster selects, slower updates.
2. Extra storage space to store indexes. Updates are slower because in addition to updating the table you have to update the index.WHAT IS TABLE PARTITIONS?
QUESTION: Why can a "group by" or "order by" clause be expensive to process?
ANSWER: Processing of "group by" or "order by" clause often requires creation of Temporary tables to process the results of the query. Which depending of the result set can be very expensive.
QUESTION: What is a SQL view?
ANSWER: An output of a query can be stored as a view. View acts like small table which meets our criterion. View is a pre complied SQL query which is used to select data from one or more tables. A view is like a table but it doesn’t physically take any space. View is a good way to present data in a particular format if you use that query quite often.View can also be used to restrict users from accessing the tables directly.
QUESTION: WHAT IS MATERIALISTIC VIEW?
