SQL Interview Questions and Answers
SQL Interview Questions and Answers for beginners and experts. List of frequently asked SQL Interview Questions with answers by Besant Technologies.
We hope these SQL interview questions and answers are useful and will help you to get the best job in the networking industry. This SQL interview questions and answers are prepared by SQL Professionals based on MNC Companies expectation. Stay tuned we will update New SQL Interview questions with Answers Frequently. If you want to learn Practical SQL Training then please go through this SQL Training in Chennai
Besant Technologies supports the students by providing SQL interview questions and answers for the job placements and job purposes. SQL is the leading important course in the present situation because more job openings and the high salary pay for this SQL and more related jobs. We provide the SQL online training also for all students around the world through the Gangboard medium. These are top SQL interview questions and answers, prepared by our institute experienced trainers.
Best SQL Interview Questions and Answers
Here is the list of most frequently asked SQL Interview Questions and Answers in technical interviews. These SQL questions and answers are suitable for both freshers and experienced professionals at any level. The SQL questions are for intermediate to somewhat advanced SQL professionals, but even if you are just a beginner or fresher you should be able to understand the SQL answers and explanations here we give.
In this post, you will get the most important and top SQL Interview Questions and Answers, which will be very helpful and useful to those who are preparing for jobs.
SQL is a domain language used for managing and maintaining data which are done in a database system. Besant Technologies provides the best SQL training in Chennai. Our trainers are having more experience in working as a developer, designers, and debuggers in top companies. They have received many excellence awards in work and also for teaching and training. They have prepared this SQL interview questions and answers by doing complete analyze and research and also by integrating with other company SQL interview questions and answers.
Here is the best and top SQL interview questions and answers.
- Using rowi
SQL > delete from emp where rowid not in (select max (rowid) from emp group by empno);
This technique can be applied to almost scenarios. Group by operation should be on
The columns which identify the duplicates.
- using self-join
SQL > delete from emp e1 where rowid not in (select max (rowid) from emp e2 where e1.empno = e2.empno );
- Using row_number()
SQL > delete from emp where rowid in (select rid from ( select rowid rid, row_number() over(partition by empno order by empno) rn from emp )where rn > 1);
This is another efficient way to delete duplicates
- Using dense_rank ( )
SQL > delete from emp where rowid in (select rid from (select rowid rid, dense_rank() over(partition by empno order by rowid) rn from emp ) where rn > 1 );
Here you can use both rank ( ) and dens_rank()
since both will give unique records when order by rowid.
- Using group by
SQL > delete from emp where empno,empname,salary) in ( select max(empno),empname,salary from emp group by empname,salary );
This technique is only applicable in few scenarios.
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, also definition of the query will also store in the database .When we see the performance of Materialized view it is better than normal View because the data of materialized view will stored in table and table may be indexed so faster for joining also joining is done at the time of materialized views refresh time so no need to every time fire join statement as in case of 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. When we create view using any table, rowid of view is same as original table but in case of Materialized view rowid is different. In case of View we always get latest data but in case of Materialized view. we need to refresh the view for getting latest data. Performance of View is less than Materialized view. In case of view its only the logical view of table no separate copy of table but in case of Materialized view we get physically separate copy of table. 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.
WHERE ROWNUM <= 10
WHERE ROWNUM < 10;
WHERE ENAME= (SELECT ENAME
MINUS SELECT ENAME
Schema objects is Structures of database’s data. tables, views, sequences, synonyms, indexes are schema objects.
Data is collection of information or collection of attribute, example Ravi it’s a data. The information of Ravi is height, weight, education, age, etc..
A table is collection of data to storage in any database. Table data is stored in rows and columns.
A View is not a original table It’s called virtual table. It’s based on original table
There are three type of tables
- Simple view
- Complex view
- Inline view
An index is direct access to rows, used to increase the performance of data retrieval. Index can be created on one or more columns of a table.
UNIQUE constraints allowed NULLs values. PRIMARY KEY constraints cannot contain NULLs.
Truncate is after deleting entire table, Cannot be rolled back options. Delete allows the single row and multiple rows. Deleted records can be rolled back or committed
Join is retrieves data from related columns or rows from multiple tables.
- Cross Join
- Inner join
- equi join
- non equi join
- Outer join
- left outer join
- right outer join
- full outer join
- Self join
Subquery is a query embedded with another query called subquery its return values from mainquery
INSTR(str1, str2 [,starting,[howmuch]])
INSTR is returns the position of the character.
SUBSTR(stri1, starting, howmuch)
SUBSTR returns a single or multiple character string
UNION returns all unique rows .
UNION ALL returns all rows included all duplicates.
INTERSECT returns all common row and unique rows selected by both queries.
MINUS returns all unique rows selected by the first query but not by the second.
ROWID is a pseudo column attached to each row of a table. Its create table database assign the rowed in each rows
Primary key is a column or a combination of columns of a table which cab be used to uniquely identify a row of the table. PRIMARY KEY cannot be null.
UNIQUE KEY is a column or a combination of columns of a table, which can be used to identify a row of the table uniquely. UNIQUE KEY can be null.
FOREIGN KEY is a column or a combination of column which is the primary key or unique key of the referenced table. FOREIGN KEY can be null.
Database maintains referential integrity by automatically removing dependent foreign key values if a referenced primary or unique key value is removed.
A database is a collection of data. Representing some aspect of real world.
select decode(mod(2004,4),0,’leap year’, ‘not a leap year’) from dual
Date is used to store date and time values including month, day, year, century, hours, minutes and seconds. It fails to provide granularity and order of execution when finding difference between 2 instances (events) having a difference of less than a second between them. Time Stamp datatype stores everything that Date stores and additionally stores fractional seconds.
Maximum 254 in single table
To be created when table is queried for less in the table rows and need fastest Retrival
Foreign key is which refers to another table primary key. Reference key is the primary key of table referred by another table.
SELECT NAME FROM SYS.V_$DATAFILE;
SELECT NAME FROM SYS.V_$CONTROLFILE;
SELECT MEMBER FROM SYS.V_$LOGFILE;
SQL*Loader is a loader utility used for moving data from external files into the Oracle database in bulk. It is used for high-performance data loads.
SQL*Loader, External Tables
Subqueries, or nested queries, are used to bring back a set of rows to be used by the parent query. Depending on how the subquery is written, it can be executed once for the parent query or it can be executed once for each row returned by the parent query. If the subquery is executed for each row of the parent, this is called a correlated subquery.
SQL>select deptno, count(deptno) from emp group by deptno having
The output should contain only those jobs with more than three employees.
SQL>select job,count(empno) from emp group by job having count(job)>3
SQL>select ename from emp where job=’CLERK’ and sal>(select min(sal)
Q38) Display the names of employees from department number 10 with a salary greater than that of any employee working in other departments?
SQL>select ename from emp where deptno=10 and sal>any(select sal from
emp where deptno not in 10).
Q39) Can one rename a column in a table? No, this is listed as Enhancement Request 163519.Workarounds:
- Rename t1 to t1_base;
Create view t1 <column list with new name> as select * from t1_base;
- create table t2 <column list with new name> as select * from t1;
Drop table t1;
Rename t2 to t1;
- SELECT DISTINCT SAL FROM EMP1 WHERE SAL NOT IN (SELECT SAL FROM EMP1 WHERE SAL < ANY (SELECT SAL FROM EMP1))
2.SELECT SAL FROM EMP WHERE SAL >= ALL (SELECT SAL FROM EMP)
“NULL”, CONCAT (DATA_TYPE, DATA_LENGTH) TYPE FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = ‘DEPT’
Q42.SQL> Example for startwith, connect by and prior SELECT ENAME, JOB, LEVEL, EMPNO, MGR FROM EMP111 CONNECT BY PRIOR EMPNO=MGR START WITH ENAME = ‘RAJA’
SELECT EMPNO, LPAD (‘ ‘, 6*(LEVEL – 1)) || ENAME “EMPLOYEE NAME” FROM EMP START
WITH ENAME=’KING’ CONNECT BY PRIOR EMPNO = MGR
Entity – Relational diagram
Could be (a) one to one
(b) One to many (crowfoot style)
(c) Many to many
A rule defined on a column (or set of columns) in one table that allows the insert or update of a row only if the value for the column or set of columns matches a value in a column of a related table. It also specifies the type of data manipulation allowed on referenced data and the action to be performed on dependent data as a result of any action on referenced data.
UPDATE and DELETE Restrict – A referential integrity rule that disallows the update or deletion of referenced data.
DELETE CASCADE – when a referenced row is deleted, all associated dependent rows are deleted.
If a foreign key reference a parent key of the same table it is called self-referential integrity constraint.
The condition must be a Boolean expression evaluated using the values in the row being inserted or updated, and cannot contain subqueries, sequences, the SYSDATE, UID, USER, or USERENV SQL functions, or the pseudocolumns LEVEL or ROWNUM.
B. SELECT ROUND(enroll_date, ‘YEAR’) FROM student;
C. SELECT ROUND(enroll_date, ‘MONTH’) FROM student;
D. SELECT ROUND(TO_CHAR(enroll_date, ‘YYYY’)) FROM student;
A. The statement will achieve the desired results.
B. The statement will execute, but will NOT enable the PRIMARY KEY constraint.
C. The statement will execute, but will NOT verify that values in the ID column do NOT violate the constraint.
D. The statement will return a syntax error.
The synonym is another name of the table used for multiple links of a database. A view is created with many tables, and virtual columns and with conditions.
Q51. Display the names of employees who earn a salary more than that of Jones or that of salary greater than that of scott.?
SQL>select ename,sal from emp where sal>(select sal from emp where ename=’JONES’)and sal> (select sal from emp where ename=’SCOTT’);
- The locations, departments, and employees tables are empty.
- The departments table has one row. The locations and the employees tables are empty.
- The location table has one row. The departments and the employees tables are empty.
- The locations table and the departments table both have one row. The employees table is empty.
Q54.What is Auto Update stats and Auto create stats? How its going to affect the database performance?
Auto update stats is going to update the stats every time whenever a new stats are creating, which in turn leads optimizer to select the best execution plan and increases the query performance.
Main Objective of SQL OS ::::
Scheduler and IO completion. The SQLOS is responsible for scheduling threads for CPU consumption. Most threads in SQL Server are run in cooperative mode, which means the thread is responsible for yielding so that other threads can obtain CPU time. Most IO is asynchronous. The SQLOS is responsible for signalling threads when IO is completed.
Synchronization primitives: SQL server is a multi-threaded application, so SQLOS is responsible for managing thread synchronizations.
Memory management: Different components within SQL Server, example plan cache, CLR, lock manager etc request memory from the SQLOS. Therefore, the SQLOS can control how much memory a component within SQL Server is consuming.
Deadlock detection and management of the same.
Exception handling framework.
Hosting services for external components such as CLR and MDAC. SQL Server will run threads that are associated with external component in pre-emptive mode. Pre-emptive mode allows the SQLOS to prevents runaway threads (threads which will not yield and allow other threads to get CPU execution). Also the SQLOS can keep track of the memory these external components consume. For example, for CLR the SQLOS can invoke garbage collection if the CLR process is taking up too much memory.
Minimum memory is the amount of memory SQL server needed to start, generally SQL server database engine required only 20MB of memory to start the services. But once will allocate minimum memory in GB then what will happen is SQL server will not release that memory once it will approach that value.
Hardware and softwareís are not compatible.
MSI and MSP files are missing.
Donít have enough permission to perform write operation on installation path.
AgentSigning Certificate is not creating.
SQL service account is not having enough permission or it has not been configured in the proper way.
SQL server error logs.
Eventviewer at OS level.
Consistency guarantees that a transaction never leaves your database in a half-finished state, what all transactions are got committed that transaction will be available and if not the transaction will be rollback.
Isolation keeps transactions separated from each other until theyíre finished, again its depends on which isolation level the transaction is running.
Durability guarantees that the database will keep track of pending changes in such a way that the server can recover from an abnormal termination, its going to calculate the recovery phase based on last checkpoint.
A transaction must conform to the ACID propertiesóatomicity, consistency, isolation, and durabilityóin order to guarantee data consistency. Most relational database systems, such as Microsoft SQL Server, support transactions by providing locking, logging, and transaction management facilities whenever a client application performs an update, insert, or delete operation.
Pessimistic concurrency: Pessimistic concurrency control locks resources as needed, for the duration of a transaction. SQL Server Compact supports pessimistic concurrency control that locks resources as needed for the duration of a transaction.
Committed Read: Committed Read is SQL Server’s default isolation level. It ensures that an operation will never read data another application has changed but not yet committed. Because you can never read uncommitted data, if a transaction running with Committed Read isolation revisits data, that data might have changed, or new rows might appear that meet the criteria of the original query. Rows that appear in this way are called phantoms.
Repeatable Read: If you want the read operations to be repeatable, choose the third repeatable Read. The Repeatable Read isolation level adds to the properties of Committed Read by ensuring that if a transaction revisits data or if a query is reissued, the data will not have changed. In other words, issuing the same query twice within a transaction won’t pick up any changes to data values that another user’s transaction has made. No other user can modify the data that your transaction visits as long as you have not yet committed or rolled back your transaction.
Serializable: The Serializable isolation level ensures that if a query is reissued, no data will have changed and no new rows will appear in the interim. In other words, you won’t see phantoms if the same query is issued twice within a transaction. Rerun the example from the Repeatable Reads section, inserting a row with a col1 value of 35. But this time, set your isolation level to SERIALIZABLE. The second connection will block when you try to do the INSERT, and the first connection will read exactly the same rows each time.
SNAPSHOT: Specifies that data read by any statement in a transaction will be the transaction ally consistent version of the data that existed at the start of the transaction. The transaction can only recognize data modifications that were committed before the start of the transaction. Data modifications made by other transactions after the start of the current transaction are not visible to statements executing in the current transaction. The effect is as if the statements in a transaction get a snapshot of the committed data as it existed at the start of the transactions.
Cause in simple recovery mode, we are not having the transaction log backup.
WRK: This Extension Is Given to A File Which is Being Copied from Primary Backup Location to Secondary and Once Copy Process has been completed these files are renamed with .trn file.
RPO stands for Recovery Point Objective: Again, in simple terms, you can think of RPO as a measure of how much data or work itís acceptable to lose. Itís relatively easy to achieve very minimal or even zero data/work loss using backups but depending on the amount of damage the database suffered when the disaster hit, recovering might take a lot of time. For instance, if an entire database is destroyed, depending on the architecture of the database and the backups that exist, it may take a significant amount of time to recover the database up to the point of the disaster. Most RPOs are defined as the amount of time for which work may be lost.
Both RTO and RPO together called as SLA.
If the database is highly critical, we will schedule a full backup every day post business hour like 10PM followed by log backup every 1 hour or 30 mins.
Depending on the Recovery model and transactions will configure the log backup in range of 15mins to 60 mins.
Fill factor will play a significant role in database performance by minimising the affect of page split which in turns leads to the optimal performance at IO level.
But this is not recommended, as its not going to support the backward compatibility during mirroring failover.
To get the information for wait type we will use below mentioned DMV.
Page IO Latch ñ These are waits that occur when a needs to wait for a page due to physical I/O. Such as when a page needs to be made available in the buffer pool for reading or writing and SQL Server needs to retrieve it from disk or mount points.
To read a mirror database we need to create a snapshot of that database.
By configuring a high availability concepts like log shipping or mirroring.
Q79.Can we add a new data file on primary in log shipping? How its going to affect the log shipping setup?
If the primary and secondary servers is having the same disk configuration i.e. the data file locations on both the servers are exactly same in that case there will be no affect on the log shipping behaviour and if not then the changes made on the primary will not reflect on the secondary.
sys.dm_repl_schemas – Contains information about each table and column being published. It returns data from the database being published and returns one row for each column in each object being published
sys.dm_repl_traninfo – Contains information about each transaction in a transactional replication.
Like : SQL server service is dependent on shared disks, ip addresses, storage. May be the dependency is either OR or AND.
Q86.Is it possible to create additional indexes on read-only secondary replicas to improve query performance in AlwaysON?
Q87.Is it possible to create additional statistics on read-only secondaries to improve query performance in AlwaysON?
Q89.Does AlwaysOn Availability Groups repair the data page corruption as Database Mirroring? Explain
The default value for MAXDOP is 0 (zero) and can be set or viewed using (sp_configure). A value of 0 means that SQL Server will use all processors if a query runs in parallel.
Q93.Can checkpoint be used for crash recovery, if a checkpoint starts but doesnít finish before a crash occurs?
The purpose of a checkpoint is to bring the pages in the data files up-to-date with whatís in the transaction log. When a checkpoint ends, thereís a guarantee that as of the LSN of the LOP_BEGIN_CKPT log record, all changes from log records before that point are persisted in the data files on disk. Thereís no guarantee about logged changes after that point, only before it. In other words, all the log records before the LSN of the LOP_BEGIN_CKPT log record are no longer required for crash recovery, unless thereís a long running transaction that started before that LSN.
Q94.Can SQL Server guarantee that thereís always one complete checkpoint in the active portion of the log, in case a crash occurs?
When snapshot isolation is enabled, deletes from a heap are ghosted, as part of the overall versioning process, which can lead to some interesting side-effects. A versioned record has an extra 14-bytes tagged on the end, so a heap record that suddenly becomes versioned is 14-bytes longer ñ which may mean it doesnít fit on the page any longer. This could lead to it being moved, resulting in a forwarding/forwarded record pair ñ just because the record was deleted! Now, the page has to be full for this time happen, and the Storage Engine will take steps to avoid this happening for rows less than 32 bytes long.
Q96.If a table is truncated during a transaction, does the integrity of the tableís pages protected in case the transaction rolls back? Explain.
They canít be reused until theyíre really deallocated. If a transaction rollback happens, the pages are just marked as allocated again.
Use the DMV sys.dm_tran_locks.
Q97.A databaseís log grew so large that I had to delete it, but on restarting SQL Server the database was in RECOVERY_PENDING mode and inaccessible! Whatís gone wrong? Explain
However, if there is no mark indicating a clean shutdown, and this is never guaranteed, then SQL Server must run recovery during restart, which it canít do without the original log, and which is the reason why youíve ended up with a RECOVERY_PENDING database. It simply means there wasnít a clean shutdown and SQL Server canít start the recovery process because you whacked the log.
The recovery process uses the contents of the log to re-establish transactional consistency. During recovery SQL Server will redo the effects of any transaction that was not included in the last CHECKPOINT to run before the database went offline, and undo the effects of any uncommitted transaction that was included in the last CHECKPOINT.
Q98.After checking DBCC LOGINFO found that log had 2000 VLFs. Is it something to worry about? Explain.
Always try to shrink the log file, which will help us in reducing the VLF numbers.
Q100.I take regular log backups, but the log has still grown to a massive size and DBCC SHRINKFILE is having no effect at all Why? And what can I do about it?
In these recovery models, only a log backup will cause SQL Server to run the ìlog clearingî process, also referred to as log truncation. During log truncation, SQL Server will mark as inactive any VLFs that contain no part of the active log, i.e. VLFs that contain no log records that SQL Server still requires for recovery, or for log backup, or for any other process.
SQL Server will run log truncation after every log backup and, each time, any inactive VLFs that result will be available for reuse to store new log records, overwriting the existing log records that are no longer required. Therefore, when SQL Server reaches the end of the log.
However, itís possible that there will be periods where successive log truncations produce zero inactive VLFS, because there are other factors that can prevent SQL Server reusing space in the log. If there are no inactive VLFs available, then SQL Server has no other choice but to grow the log, adding more VLFs.
This is likely what is happening in this case; some other process is forcing SQL Server to retain log records in the active log, and so successive executions of the log truncation process arenít producing any more inactive VLFs. Attempts to shrink the log will have no effect in this situation as there is no free space to remove! DBCCSHRINKFILE can only physically remove unused or inactive VLFs that are at the end of the log file.
As well always mount the TempDB on separate disk for better performance.
This does not happen for tempdb. Tempdb is not recovered in the event of a crash, and so there is no need to force dirty tempdb pages to disk, except in the case where the lazy writer process (part of the buffer pool) has to make space for pages from other databases. Of course, when you issue a *manual* CHECKPOINT, all the dirty pages are flushed, but for automatic checkpoints theyíre not.
Tempdb, however, isn’t recovered after a crash ñ it’s recreated. This means that the time for recovery of tempdb isn’t an issue so there’s no need for automatic checkpoints. This means that the trigger of an automatic checkpoint for tempdb when it’s log file reaches 70% full. For this reason, on busy systems, it’s likely that the user databases are being checkpointed way more often than tempdb, and on a system where tempdb is used heavily too, there will likely be way more dirty pages from tempdb in the buffer pool at any time.
Which is going to help in point in time Recovery.
Q105.What is the cause, log_reuse_wait_desc is showing Replication and still the Replication has not been configured on database?
CDC uses the replication log scanning mechanism to harvest changes from the database, either backing on replicationís Log Reader Agent job or creating itís own capture job if replication isnít configured. If CDC is configured but the capture job isnít running, the log_reuse_wait_desc will show as REPLICATION, as the log manager doesnít have any way to know why the replication log scanner is configured, just that it is, and it hasnít run.
PL-SQL Interview Questions and Answers
i. Implicit cursors
ii. Explicit cursors