3i Infotech Placement: Sample Questions 320 - 321 of 1245

Glide to success with Doorsteptutor material for competitive exams : get questions, notes, tests, video lectures and more- for all subjects of your exam.

Question 320

Question

MCQ▾

LGWR process writes information into … .

Choices

Choice (4)

a.

Redo log files

b.

Database

c.

Control files

d.

None of the above

Answer

a.

Explanation

Structure Understanding of LGWR
  • LGWR process writes information into redo log files.
  • Redo log files are created upon database creation and addition one time added by the DBA.
  • When we start a database instance, LGWR is an oracle background process created.
  • Writes the redo log buffers to the on-line redo log files.
  • Redo log data is always written first to a buffer in memory, and then written to disk by the LGWR process.

The LGWR process is also responsible for:

  • When a transaction is committed, writing commit records to the log file
  • Multiple commits that is written in a single operation when database activity is high.
  • Recording a system change number to each committed transaction, for use in recovery operations when running Parallel Server.

Question 321

Edit

Describe in Detail

Essay▾

How to remove duplicate records from a table?

Explanation

  • Firstly, we will create a table, where we will insert some duplicate rows to understand the topic properly.
  • Create a table called ATTENDANCE by using the following code:

    CREATE TABLE [dbo] . [ATTENDANCE]

    (

    [EMPLOYEE_ID] [varchar] (50) NOT NULL,

    [ATTENDANCE_DATE] [date] NOT NULL

    )

    ON [PRIMARY]

    Now insert some data into a table.

    INSERT INTO dbo. ATTENDANCE (EMPLOYEE_ID, ATTENDANCE_DATE) VALUES

    ( ‘01’ , CONVERT (DATETIME, ‘01 - 01 - 17’ , 5) )

    INSERT INTO dbo. ATTENDANCE (EMPLOYEE_ID, ATTENDANCE_DATE) VALUES

    ( ‘01’ , CONVERT (DATETIME, ‘01 - 01 - 17’ , 5) )

    INSERT INTO dbo. ATTENDANCE (EMPLOYEE_ID, ATTENDANCE_DATE) VALUES

    ( ‘02’ , CONVERT (DATETIME, ‘01 - 01 - 17’ , 5) )

    INSERT INTO dbo. ATTENDANCE (EMPLOYEE_ID, ATTENDANCE_DATE) VALUES

    ( ‘02’ , CONVERT (DATETIME, ‘01 - 01 - 17’ , 5) )

    INSERT INTO dbo. ATTENDANCE (EMPLOYEE_ID, ATTENDANCE_DATE) VALUES

    ( ‘03’ , CONVERT (DATETIME, ‘01 - 01 - 17’ , 5) )

    INSERT INTO dbo. ATTENDANCE (EMPLOYEE_ID, ATTENDANCE_DATE) VALUES

    ( ‘03’ , CONVERT (DATETIME, ‘01 - 01 - 17’ , 5) )

After inserting the data, check the data of the below table. If we grouped the employee_id and attendance_date, then 01 and 02 become duplicates.

Table Shows the Employee Data
EMPLOYEE_IDATTENDANCE_DATE
012017 - 01 - 01
012017 - 01 - 01
022017 - 01 - 01
022017 - 01 - 01
022017 - 01 - 01
032017 - 01 - 01

Solution of how to delete those duplicate data?

  • First, insert an identity column in that table by using the following code:

    ALTER TABLE dbo. ATTENDANCE ADD AUTOID INT IDENTITY (1,1)

Now, the table data will be like the following table:

Table Shows the Employee Data
EMPLOYEE_IDATTENDANCE_DATEAUTOID
012017 - 01 - 011
012017 - 01 - 012
022017 - 01 - 013
022017 - 01 - 014
022017 - 01 - 015
032017 - 01 - 016
  • Now use the following code to find out the duplicate rows that exist in the table.

    SELECT ⚹ FROM dbo. ATTENDANCE WHERE AUTOID NOT IN (SELECT MIN (AUTOID) _

    FROM dbo. ATTENDANCE GROUP BY EMPLOYEE_ID, ATTENDANCE_DATE)

The above code will give us the following result

Table Shows the Employee Data
EMPLOYEE_IDATTENDANCE_DATEAUTOID
012017 - 01 - 012
022017 - 01 - 014
022017 - 01 - 015
  • There are the duplicate rows, which we want to delete to resolve the issue.
  • Use this code below.

    DELETE FROM dbo. ATTENDANCE WHERE AUTOID NOT IN (SELECT MIN (AUTOID) _

    FROM dbo. ATTENDANCE GROUP BY EMPLOYEE_ID, ATTENDANCE_DATE)

  • Therefore, in the table no duplicate rows exits.

Developed by: