If you’ve seen my Java Interview Questions blog, this blog is essentially the same idea but for Database/SQL related questions. These are questions that I feel could be a little tricky if you’re asked it and can’t think of the right terms and keywords to respond with.
- What does SQL stand for?
- Structured Query Language
- Example of horizontal expansion
- Adding more servers rather than a bigger server
- What is a schema?
- skeleton/blueprint describing the database structure
- What are the two kinds of query language?
- Database query and information retrieval query
- What relationship does an entity have with an attribute?
- An entity represents a real-world object and an attribute represents a trait of that object. For example, an entity could be a dog and the attribute can be the dog’s name, dog’s fur color or dog’s markings.
- Give a real world example of a many-to-many relationship.
- A student can study many subjects and a subject can be studied by many students.
- What is a relation modeled by in a relational model view?
- A table
- Name the three components of the relational model.
- Tuples, Attributes, Relational
- What is the purpose of normalization?
- Reduce redundancy
- Describe 1NF, 2NF, 3NF.
- In 1NF, you make the table have only atomic-values; that is every column should hold only one value. In 2NF you remove any partial dependencies from the table and move them into a new table with the primary key. In 3NF you remove any transitive dependencies from the main table and make them their own table.
- Which SQL statement type deals with managing data in the schema object?
- DML-data manpulation language
- Name the three kinds of SQL Statements and describe what they each do.
- Data Definition Language is used to define the database schema.
- Data Manipulation Language is used to manage data in the schema.
- Data Control Language is used to secure the database by granting and revoking permissions.
- What is SQL Injection?
- A code-injection technique in which malicious SQL statements can be inserted for execution.
Q2. What are the different subsets of SQL?
- Data Definition Language allows you to create, alter and delete objects in your database, mainly tables; you’re defining the schema when you utilize these commands
- Data Manipulation Language allows you to access and play with the actual data stored in objects
- Data Control Language allows you to control who has access to your database and how much they can access the data within; think multiple users with separate permissions
Websites:
https://www.coursehero.com/file/p3ph8t9/Subsets-of-SQL-There-are-three-main-subsets-of-the-SQL-language-Data-Control/
https://www.geeksforgeeks.org/sql-ddl-dml-dcl-tcl-commands/
Q11. What is a Foreign key?
Consider two tables. A foreign key in Table B references the primary key of Table A. The foreign key is a link between the data stored in those rows. The FOREIGN KEY keyword helps to prevent actions that would dismantle the link. You can think of it as a fail-safe.
Websites:
https://www.w3schools.com/sql/sql_foreignkey.asp
http://www.sqlservertutorial.net/sql-server-basics/sql-server-foreign-key/
Q18. What is an Index?
An index is a database object which allows a query to access data faster and more efficiently; it’s power is especially noticeable in larger databases.
Websites:
https://www.geeksforgeeks.org/indexing-in-databases-set-1
https://www.essentialsql.com/what-is-a-database-index/
Q19. Explain different types of index.
- Unique Index doesn’t let the field have duplicate values; every row in the table or view affected will be unique in some way. A unique index can be automatically applied on the primary key if it exists.
- Clustered Index reorders the physical order to search a table or view based on the index key values. Each table can have only one clustered index.
- Non-Clustered Index on the other hand doesn’t reorder the data rows; it maintains it’s original order of data. Each table can have as many non clustered indexes as needed.
Websites:
https://docs.microsoft.com/en-us/sql/relational-databases/indexes/clustered-and-nonclustered-indexes-described?view=sql-server-2017
https://stackoverflow.com/questions/91688/what-are-the-differences-between-a-clustered-and-a-non-clustered-index
Q21. What is the difference between DROP and TRUNCATE commands?
- The DROP command essentially deletes the whole database or table specified. Your database or table won’t exist anymore after using the DROP command on it.
- TRUNCATE, on the other hand, removes all of the existing rows from a table all at once. This means you can create new rows without needing to regard the ‘old’ ones because you’ve removed them. It’s also more efficient than using the DELETE command to delete rows because it is logged as one action instead of deleting row A, deleting row B, and so on.
Websites:
https://www.geeksforgeeks.org/sql-drop-truncate/
https://www.c-sharpcorner.com/blogs/difference-between-truncate-delete-and-drop-in-sql-server
https://stackoverflow.com/questions/135653/difference-between-drop-table-and-truncate-table
Q23. What is ACID property in a database?
- Atomicity ensures that a transaction(an operation on your data) must either completely pass or completely fail; you won’t get a transaction that worked halfway but still changed data. For any data to be affected by the transaction, it must work entirely.
- Consistency makes sure that all data entered meets validation rules. Only data that follows these defined rules can be written to the database.
- Isolation makes sure that multiple transactions can run at the same time independent of each other: that is, accessing or updating a record in a table won’t affect another transaction of a different record in that table. It also means that a transaction cannot read from an incomplete transaction until it is finished.
- Durability makes sure that if a transaction is committed, it will go through the system no matter what outside force(power loss, crash or errors) might occur.
Websites:
https://www.clustrix.com/bettersql/acid-compliance-means-care/
https://database.guide/what-is-acid-in-databases/
Q24. What do you mean by “Trigger” in SQL?
A Trigger is a special stored procedure written to execute in place of or following after certain special events, such as insert or update queries.
Websites:
http://www.sqlservertutorial.net/sql-server-triggers/
https://www.geeksforgeeks.org/sql-trigger-student-database/
Q27. What is the difference between cross join and natural join?
A natural join is a join which occurs with a WHERE clause where it is based on the tables having columns which contain the same name and data types in both tables. A cross join is a basic join without a WHERE clause; it gives you the cross product of the two tables.
Websites:
https://www.dummies.com/programming/sql/how-to-use-cross-join-natural-join-and-condition-join-in-sql/
https://www.slideshare.net/yaqinov/cross-join-and-natural-join
Q29. What are the different types of a subquery?
There are two general kinds of subqueries; correlated and non-correlated. In a correlated subquery, it executes one time for each row of the outer query—it’s dependent on the outer query. On the other hand, non-correlated subqueries exist where the query is independent; the output of subquery is substituted in the outer query.
Websites:
https://www.w3resource.com/sql/subqueries/understanding-sql-subqueries.php
https://www.geeksforgeeks.org/sql-correlated-subqueries/
Q38. What is the need of MERGE statement?
The MERGE statement is the combination of the INSERT, DELETE and UPDATE statements all in one; it can make changes in a table based on values that match another table.
Websites:
https://www.geeksforgeeks.org/merge-statement-sql-explained/ https://www.essentialsql.com/introduction-merge-statement/
Q42. List the ways in which Dynamic SQL can be executed?
The common ways are to use the EXEC method, using the sp_executeSQL stored procedure and simply writing a query with parameters.
Websites:
http://www.sommarskog.se/dynamic_sql.html#FirstEncounter
https://www.mssqltips.com/sqlservertip/1160/execute-dynam
Q48. What are aggregate and scalar functions?
Scalar functions return a single value based solely on the input value, aggregate functions also return a single value but only after performing some calculations on a set of values.
Websites:
https://www.learndb.com/questions/49/whats-the-difference-between-scalar-and-aggregate-functions
https://www.studytonight.com/dbms/sql-function.php
Q53. What is the main difference between SQL and PL/SQL?
SQL is a query language that will execute a single query at a time; PL/SQL is basically a souped up extension of SQL that is a procedural language which can excute an entire block of queries at once.
Websites:
https://www.geeksforgeeks.org/difference-between-sql-and-plsql/
https://techdifferences.com/difference-between-sql-and-pl-sql.html
Q58. List all the types of user-defined functions?
- Scalar functions which return a single value of the type defined in the RETURNS clause
- Inline table-valued functions which return a table consisting of a result set from a single SELECT statement
- Multi-statement valued functions which returns a similar tabled-result set like inline, but multiple select-statements can be used
Websites:
https://www.tutorialgateway.org/user-defined-functions-in-sql/
https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/user-defined-functions?view=sql-server-2017
Q59. What do you mean by Collation?
Collation means a set of rules applied to the database how data should be handled on the server, database or column level.
Websites:
https://www.databasejournal.com/features/mssql/article.php/3302341/SQL-Server-and-Collation.htm
https://www.red-gate.com/simple-talk/sql/sql-development/questions-sql-server-collations-shy-ask/#post-70501-_Toc479001442
Q63. What is a Data warehouse?
A data warehouse is a core repository that resources data from many different sources of information. The main reasoning for a data warehouse is to make reporting and analysis of the data more manageable.
Websites:
https://panoply.io/data-warehouse-guide/the-difference-between-a-database-and-a-data-warehouse/
https://searchdatamanagement.techtarget.com/definition/data-warehouse
Source for Some Questions:
https://www.edureka.co/blog/interview-questions/sql-interview-questions
Rebecca Hull's Wordpress 