Oracle SQL Interview Questions and Answers

SELECT subject_code, AVG (marks) FROM students HAVING AVG(marks) > 75 GROUP BY subject_code;

STUFF (String1, Position, Length, String2)
String1 - String to be overwritten
Position - Starting location for overwriting
Length - Length of substitute string
String2- String to overwrite.

Q : If you are a SQL Developer, how can you delete duplicate records in a table with no primary key?
A : Use the SET ROWCOUNT command. For instance,
if you have 2 duplicate rows, you would SET ROWCOUNT 1, execute DELETE command and then SET ROWCOUNT 0.

• BCP : The bulk copy (bcp) command of Microsoft SQL Server provides you with the ability to insert large numbers of records directly from the command line. In addition to being a great tool for command-line aficionados, bcp is a powerful tool for those seeking to insert data into a SQL Server database from within a batch file or other programmatic method.
• Bulk Insert: The BULK INSERT statement was introduced in SQL Server 7 and allows you to interact with bcp (bulk copy program) via a script.
• OpenRowSet : The OPENROWSET function can be referenced in the FROM clause of a query as if it were a table name. The OPENROWSET function can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement, subject to the capabilities of the OLE DB provider. Although the query might return multiple result sets, OPENROWSET returns only the first one.
• OPENDATASOURCE : Provides ad hoc connection information as part of a four-part object name without using a linked server name.
• OPENQUERY : Executes the specified pass-through query on the specified linked server. This server is an OLE DB data source. OPENQUERY can be referenced in the FROM clause of a query as if it were a table name.
• Linked Servers : Configure a linked server to enable the SQL Server Database Engine to execute commands against OLE DB data sources outside of the instance of SQL Server. Typically linked servers are configured to enable the Database Engine to execute a Transact-SQL statement that includes tables in another instance of SQL Server, or another database product such as Oracle.

• The sp_MSforeachdb system stored procedure accepts the @Command parameter which can be exetecuted against all databases. The ‘?’ is used as a placeholder for the database name to execute the same command.
• The alternative is to use a cursor to process specific commands against each database.

SELECT Employee_Name, CASE Location
WHEN 'alex' THEN Bonus * 2
WHEN 'robin' THEN Bonus *, 5
ELSE Bonus
END
"New Bonus"
FROM  Intellipaat_employee;

Q : What are the risks of storing a hibernate-managed object in cache? How do you overcome the problems?
A : The primary problem here is that the object will outlive the session it came from. Lazily loaded properties won’t get loaded if needed later. To overcome the problem, perform cache on the object’s id and class and then retrieve the object in the current session context.

Syntax

UPDATE STATISTICS table_or_indexed_view_name 
    [ 
        { 
            { index_or_statistics__name }
          | ( { index_or_statistics_name } [ ,...n ] ) 
                }
    ] 
    [    WITH 
        [ 
            FULLSCAN 
            | SAMPLE number { PERCENT | ROWS } 
            | RESAMPLE 
              [ ON PARTITIONS ( {  |  } [, …n] ) ]
            |  [ ,...n ]
        ] 
        [ [ , ] [ ALL | COLUMNS | INDEX ] 
        [ [ , ] NORECOMPUTE ] 
        [ [ , ] INCREMENTAL = { ON | OFF } ]
    ] ;

 ::=
    [ STATS_STREAM = stats_stream ]
    [ ROWCOUNT = numeric_constant ]
    [ PAGECOUNT = numeric_contant ]

Use SQL Profiler to monitor only the events in which you are interested.

If traces are becoming too large, you can filter them based on the information you want, so that only a subset of the event data is collected. Monitoring too many events adds overhead to the server and the monitoring process and can cause the trace file or trace table to grow very large, especially when the monitoring process takes place over a long period of time.

Syntax :-

STUFF ( character_expression , start , length , replaceWith_expression )

For example :-

Select Stuff ('Intellipaat', 3, 3, 'abc')
This query will return the string "Iabcllipaat". In this example, Stuff function replaces the string "Intellipaat" onwards the 3rd position('nte') with 'abc'.

Replace Function :– Replace function is used to replace all occurrence of a specified with the string passed as last argument.
Syntax :-

REPLACE ( string_expression , string_pattern , string_replacement )

For example :-

Select Replace ('Abcabcabc', 'bc', 'xy')
This query will return the string Axyaxyaxy. In this example, Replace function replaces the 
occurrence of each 'bc' string with 'xy'.

SELECT @RC = @@ROWCOUNT, @ER = @@ERROR

• Buffer Cache : Buffer cache is a memory pool in which data pages are read. The ideal performance of the buffer cache is indicated as: 95% indicates that pages that were found in the memory are 95% of time. Another 5% is need physical disk access.
  If the value falls below 90%, it is the indication of more physical memory requirement on the server.
• Log Caches : Log cache is a memory pool used to read and write the log pages. A set of cache pages are available in each log cache. The synchronization is reduced between log and data buffers by managing log cache separately from the buffer cache.

Following are the steps to use Linked Server for any OLE DB. You can refer this to use an MS-Excel workbook.

1. Open SQL Server Management Studio in SQL Server.
2. Expand Server Objects in Object Explorer.
3. Right-click on Linked Servers. Click on New Linked Server.
4. Select General page in the left pane and
   • Type any name for the linked server in the first text box.
   • Select the Other Data Source option.
   • Click on Microsoft Jet 4.0 OLE DB Provider from the Provider list.
   • Type the Excel as the name of the OLE DB data source.
   • Type the full path and file name of the Excel file in Data Source box.
   • Type the Excel version no. (7.0, 8.0 etc) in the Provider String. Use Excel 8.0 for Excel 2000, Excel 2002 or Excel 97.
   • To create a linked server click on OK.

Here is SQL query using Subquery :

Select MAX(Salary) from Intellipaat_emplyee WHERE Salary NOT IN ( select MAX(Salary) from Intellipaat_employee.

The following is the process for sending emails from database.

• Make sure that the SQL Server Mail account is configured correctly and enable Database Mail.
• Write a script to send an e-mail. The following is the script.

  USE [YourDB]
  EXEC msdb.dbo.sp_send_dbmail
  @recipients = 'xyz@intellipaat.com; abc@intellipaat.com;pqr@intellipaat.com’ 
  @body = ' A warm wish for your future endeavor',
  @subject = 'This mail was sent using Database Mail' ;
  GO

The following is a procedure for retrieving xml data:

DECLARE @index int
DECLARE @xmlString varchar(8000)
SET @xmlString ='

abc
9343463943/PhoneNo>


xyz
9342673212

'

EXEC sp_xml_preparedocument @index OUTPUT, @xmlString

SELECT * FROM OPENXML (@index, 'Persons/Person') WITH (id varchar(10), Name varchar(100) 'Name' , PhoneNo varchar(50) 'PhoneNo')

EXEC sp_xml_removedocument @index
The above code snippet results the following:
15201 abc 9343463943 
15202 xyz 9342673212

Or
Store them in a folder and establish the pointer to link them in the database.

CREATE PROCEDURE Abc
WITH ENCRYPTION
AS
<< SELECT statement>>
GO

WITH ENCRYPTION indicates that SQL Server will convert the original text of CREATE PROCEDURE statement to an encrypted format. Users that do not have no access to system tables or database files cannot retrieve the encrypted text. However, the text will be available to privileged users.

Example:

CREATE PROCEDURE salary_sum
WITH ENCRYTION
AS
SELECT sum(salary)
FROM employee
WHERE emp_dept LIKE Develop

• One machine provides the basic services and the second is available to run the service when the primary system fails.
• The primary system is monitored periodically to check if it works. This monitoring may be performed by the failover computer or an independent system also called as cluster controller. In an event of failure of primary computer, the failover system takes control.