During Interview many interviewers asked different database or query related questions as there could be the requirement that QA should know the basic SQL queries. many times it happens that QA uses simple sql queries to test the entries in the database tables to make testing more effective. following are some top queries which most likely asked by interviewer
- How you will select the unique amount from the column? or If the value in the column is repeatable, how do you find out the unique values?
Answer: I will use DISTINCT in the query, for example : SELECT DISTINCT <ColumnName>FROM <TableName>; to extend to this questions you may also get question like ” Find the count of it?” then use SELECT COUNT (DISTINCT <ColumnName) FROM <TableName>;
- Find Second highest number from a table without using LIMIT function.
Answer:SELECT MAX(ColumnName from where you want to get 2nd highest number) FROM <TableName> WHERE <ColumnName><(SELECT MAX(<ColumnName>) FROM <TableName>)
- What are HEAP tables in MySQL?
Answer: HEAP tables are in-memory. They are usually used for high-speed temporary storage. No TEXT or BLOB fields are allowed within HEAP tables. You can only use the comparison operators = and <=>. HEAP tables do not support AUTO_INCREMENT. Indexes must be NOT NULL.
- What is difference between Unique key and Primary Key?
Answer: The not null constraint is by default added to primary key, it means, primary key attribute cannot accept null values, whereas, the attribute declared as unique can accept null values. It is the major difference between the two.
Secondly, we can have only one primary key in a relation, whereas, multiple attributes can be declared unique at the same time.
- Difference between TRUNCATE, DELETE and DROP commands?
Answer: The DELETE command is used to remove rows from a table, A WHERE clause can be used to only remove some rows. TRUNCATE removes all rows from a table. The DROP command removes a table from the database.
- Why would you use SQL Agent?
Answer: SQL Agent is the job scheduling mechanism in SQL Server. Jobs can be scheduled to run at a set time or when a specific event occurs. Jobs can also be executed on demand. SQL Agent is most often used to schedule administrative jobs such as backups.
- What happens on checkpoint?
Answer: Checkpoints, whether scheduled or manually executed, cause the transaction log to be truncated up to the beginning of the oldest open transaction (the active portion of the log). That is, the dirty pages from the buffer cache are written to disk. Storing committed transactions in the cache provides a performance gain for SQL Server. However, you do not want the transaction log to get too big because it might consume too many resources and, should your database fail, take too long to process to recover the database.
One important thing to note here is that SQL Server can only truncate up to the oldest open transaction. Therefore, if you are not seeing the expected relief from a checkpoint, it could very well be that someone forgot to commit or rollback their transaction. It is very important to finalize all transactions as soon as possible.
- What is the difference between a Local and a Global temporary table?
Answer:A local temporary table exists only for the duration of a connection or, if defined inside a compound statement, for the duration of the compound statement.
A global temporary table remains in the database permanently, but the rows exist only within a given connection. When connection is closed, the data in the global temporary table disappears. However, the table definition remains with the database for access when database is opened next time.
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”