SQL10 SQL Excercise, Requirement solution, Practice, Query Logic

Data in Table 

order_Day	order_id	prod_id	quantity	price
01-jul-2011	o1	p1	5	5
01-jul-2011	o2	p2	2	10
01-jul-2011	o3	p3	10	25
01-jul-2011	o4	p1	20	5
02-jul-2011	o5	p3	5	25
02-jul-2011	o6	p4	6	20
02-jul-2011	o7	p1	2	5
02-jul-2011	o8	p5	1	50
02-jul-2011	o9	p6	2	50
02-jul-2011	o10	p2	4	10

Output Needed

order_Day	prod_id	Sum
01-jul-2011	p3	250
02-jul-2011	p3	125

SELECT *
INTO SQL10
FROM (
	SELECT '01-jul-2011' order_Day
		,'o1' order_id
		,'p1' prod_id
		,5 quantity
		,5 price
	
	UNION ALL
	
	SELECT '01-jul-2011'
		,'o2'
		,'p2'
		,2
		,10
	
	UNION ALL
	
	SELECT '01-jul-2011'
		,'o3'
		,'p3'
		,10
		,25
	
	UNION ALL
	
	SELECT '01-jul-2011'
		,'o4'
		,'p1'
		,20
		,5
	
	UNION ALL
	
	SELECT '02-jul-2011'
		,'o5'
		,'p3'
		,5
		,25
	
	UNION ALL
	
	SELECT '02-jul-2011'
		,'o6'
		,'p4'
		,6
		,20
	
	UNION ALL
	
	SELECT '02-jul-2011'
		,'o7'
		,'p1'
		,2
		,5
	
	UNION ALL
	
	SELECT '02-jul-2011'
		,'o8'
		,'p5'
		,1
		,50
	
	UNION ALL
	
	SELECT '02-jul-2011'
		,'o9'
		,'p6'
		,2
		,50
	
	UNION ALL
	
	SELECT '02-jul-2011'
		,'o10'
		,'p2'
		,4
		,10
	) t

Solution 1

SELECT *
FROM (
	SELECT ps.order_day
		,ps.prod_id
		,accum_sum
		,row_number() OVER (
			PARTITION BY ps.order_day ORDER BY accum_sum DESC
			) rn
	FROM SQL10 ps
	JOIN (
		SELECT quantity * price total_amount
			,order_day
			,order_id
			,sum(quantity * price) OVER (
				PARTITION BY order_day
				,prod_id ORDER BY order_day
					,prod_id
				) accum_sum
		FROM SQL10
		) ps1 ON ps.order_day = ps1.order_day
		AND ps.order_id = ps1.order_id
	) t
WHERE rn = 1

Solution 2

SELECT Order_Day

	,PrOd_id
	,SOLD_AMT
FROM (
	SELECT Order_Day
		,PrOd_id
		,SUM(quantity * Price) SOLD_AMT
		,DENSE_RANK() OVER (
			PARTITION BY Order_DaY ORDER BY SUM(quantity * Price) DESC
			) RNK
	FROM SQL10
	GROUP BY Order_Day
		,PrOd_id
	) t1
WHERE RNK = 1;

py0022 : 'python' is not recognized as an internal or external command,

C:\Users\******\AppData\Local\Programs\Python\Python37-32\Scripts>python --version

'python' is not recognized as an internal or external command,

operable program or batch file.

Above error doesnt mean Python is not installed on your system. Python may or may not installed on your system. You can check this manually. Go to C drive search for python folder.

Default installation can be under path "C:\Users\******\AppData\Local\Programs\Python"

Foe me this command worked under path 

C:\Users\*****\AppData\Local\Programs\Python\Python37-32>python --version

Python 3.7.2

py0021 : Make Python connection with SQLServer database

Below is the code which can be used for to make connection with SQLServer database. Make sure python have already imported with pyodbc driver. If you have missing driver, use below command to install driver.

>pip install pyodbc

If you faced below message in output that means driver already installed
"Requirement already satisfied: pyodbc in c:\users\******\appdata\local\programs\python\python37-32\lib\site-packages (4.0.25)"

Code : 
import pyodbc
conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=IPAddress;'
                      'Database=databaseName;'
                      'username=sa;'
                      'password=dbpASSWORD;'
                      )
cursor = conn.cursor()
cursor.execute('SELECT * FROM information_schema.tables')
for row in cursor:
print(row)

py0020 : Python PIP install Error

I was trying to install pyodbc driver for sqlserver, but faced below error .

C:\Users\******\AppData\Local\Programs\Python\Python37-32\Scripts>pip install pyodbc

Requirement already satisfied: pyodbc in c:\users\******\appdata\local\programs\python\python37-32\lib\site-packages (4.0.25)

You are using pip version 18.1, however version 20.3.3 is available.

You should consider upgrading via the 'python -m pip install --upgrade pip' command.

C:\Users\******\AppData\Local\Programs\Python\Python37-32\Scripts>python -m pip install --upgrade pip

'python' is not recognized as an internal or external command,

operable program or batch file.

C:\Users\******\AppData\Local\Programs\Python\Python37-32\Scripts>cd ..

C:\Users\******\AppData\Local\Programs\Python\Python37-32>python -m pip install --upgrade pip

Collecting pip

  Downloading https://files.pythonhosted.org/packages/54/eb/4a3642e971f404d69d4f6fa3885559d67562801b99d7592487f1ecc4e017/pip-20.3.3-py2.py3-none-any.whl (1.5MB)

    100% |████████████████████████████████| 1.5MB 1.5MB/s

Installing collected packages: pip

  Found existing installation: pip 18.1

    Uninstalling pip-18.1:

      Successfully uninstalled pip-18.1

Successfully installed pip-20.3.3

C:\Users\******\AppData\Local\Programs\Python\Python37-32>

Solution : You can upgrade PIP version, as command automatically suggested by the system.

It can be upgrade by using command "python -m pip install --upgrade pip", you might face error while installating this. Only need to take care we need to change the path on CMD where python.exe exists on file system

kyocera taskalfa 1800 "Add Toner" Error

• From Dial keypad, Enter Code, 10871087
• Press OK
• Dial/Enter 130 
• Press OK
• you can see on screen "No Action"
• Scroll dow using Arrow Key, until you will see "Execute" in place of "No Action"
• Press OK buttonm
• wait for upto 5 minutes (while execute you can see PLUS + sign will be on off on screen
• After completion of Execute, Press Reset or Stop button
• You can see "Maintenance" on screen
• Go to Option Print and Press "Print " Button
• machine will print the status of pages
• If above 4 step will not work shutdown machine and restart

How to Launch First Page using XAMPP on local machine

 How to Launch First Page using XAMPP on local machine

If you are new to XAMPP/PHP then this blog may help you. If you are not aware how to run php page on your local computer, read and follow below steps carefully

• Start XAMPP
• Make sure Apache/MySQL is running through XAMPP
• Go to installation folder of XAMPP
• Locate htdocs folder in installation directory "C:\xampp\htdocs\username"
• Create any Folder in "htdocs" folder
• Move in New folder created in "htdocs"
• create new file containing your PHP code
• Launch Chrome Browser or any other browser installed on your computer
• Enter URL "http://localhost/phpmyadmin/" to make sure XAMPP is up and running
• Enter url "http://localhost/username/first_.php" 

Note * - first_.php is the php page which is kept under htdocs folder, you must put your project under "htdocs" folder only

Create Search TextBox on Webpage

I have used this search textBox to search from the table, it can search any text (entered in text box) from the whole table and table will show only those rows where ever searched keyword found.

Once can modify the code as per their requirement.

Add below code in your header tab of html/xml code. Actual code is in "script" tag which is handling this implementation.

<html>
<head>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.0/jquery.min.js"></script>
<script>
$(document).ready(function(){
  $("#myInput").on("keyup", function() {
    var value = $(this).val().toLowerCase();
    $("#ListAllPost tr").filter(function() {
      $(this).toggle($(this).text().toLowerCase().indexOf(value) > -1)
    });
  });
});
</script>
<style>
table {
  font-family: arial, sans-serif;
  border-collapse: collapse;
  width: 100%;
}
td, th {
  border: 1px solid #dddddd;
  text-align: left;
  padding: 4px;
}
tr:nth-child(even) {
  background-color: #dddddd;
}
</style>
</head>
<body>
<h2>
Filter Posts</h2>
<input id="myInput" placeholder="Search.." type="text" />
<table><thead>
<tr>     <th style="width: 10%;">  Full Name  </th>     <th style="width: 45%;">Comment</th>     <th style="width: 45%;">Place</th>   </tr>
</thead>    <tbody id="ListAllPost">
<tr><td>Gurjeet Singh</td>     <td><a href="http://j4info.blogspot.com/p/blog-page_14.html">Enter any text in your text Box</td> <td>Click on the link to see the implementation</td> </tr>
<tr><td>J4Info</td>     <td><a href="http://j4info.blogspot.com/p/blog-page_14.html">Entered text will look from this table rows and accordingly show only that rows where ever searched keyword found.</a></td><td>Click on the link to see the implementation</td> </tr>
</tbody></table>
</body></html>
</div>

Use this code for

• To add search text button in your blog
• Search text in your website
• Add Search button in table
• Search keyword from tabular data

Macro to Change the Font/Color/Size/Alignment of Text Box content PowerPoint

Macro code to ease your manual work, if you have large number of slides in your presentation and globally want to change the property of text then below code is helpful.

You can change the Font Size, Font Color, Font Property like  Bold, Italic, underline, Font Type, Shadow and many more

Use below code in Power Point file and execute below macro code

Sub TextSize()
Dim oSl As Slide
Dim oSh As Shape
With ActivePresentation
For Each oSl In .Slides
For Each oSh In oSl.Shapes
With oSh
If .HasTextFrame Then
If .TextFrame.HasText Then
.TextFrame.TextRange.Font.Size = 48
.TextFrame.TextRange.Font.Color = RGB(112, 48, 160)
.TextFrame.TextRange.Font.Italic = msoTrue
.TextFrame.TextRange.Font.Name = "Brush Script MT"
.TextFrame.TextRange.Font.Shadow = msoTrue
.TextFrame.TextRange.ParagraphFormat.Alignment = ppAlignCenter
End If
End If
End With
Next
Next
End With
End Sub

Macro Code to Copy Content from Excel File to PowerPoint, Each Row to Each Slide

Requirement
1. Need to Copy data from Excel Column A, to Power Point
2. Row 1 data Go to Slide 1
3. Row 2 data will go to Slide 2
4. Row 3 data will go to slide 3

PreRequisite

• I have some content in Excel in Column "A"
• Column "A", have at least 1 record in cell A1 and can have any number of records in row
• Must have PowerPoint file with you 
• Decide your template before hand
• You must have slides ready in PowerPoint
• Number of Slides must be Equal to the number of Row in Excel , which need to Copy over
• Below Macro Code need to be execute in Excel

1. Click on Macro
2. Type any Name and Click on "Create" button
3. New Popup will be there and Copy Paste Below Macro Code (Replace existing Sub/End Sub) and Save 

• Run Code
• On Successful execution of code, PowerPoint file will Open and Close
• Only PowerPoint without any file stay Open, If yes Close it manually
• On VB window, you can see the dialog box "Done"
• If yes your code executed without any error

Sub ExcelRowtoPPT()
    Dim sht As Worksheet
    Dim PPApp As PowerPoint.Application
    Dim PPShape As PowerPoint.Shape
    Dim PPPresentation As PowerPoint.Presentation
    Dim PPTSlide As Slide
    Dim V_PPTPath As String
Dim V_PPTPath_New As String
    V_PPTPath = "C:\Users\UserName\Desktop\macro\Presentation1.pptx"
    V_PPTPath_New = "C:\Users\UserName\Desktop\macro\Presentation1.pptx"
    Set PPApp = CreateObject("PowerPoint.Application")
    PPApp.Visible = msoTrue
    Set PPPresentation = PPApp.Presentations.Open(V_PPTPath)
    For Each PPTSlide In PPPresentation.Slides
        i = PPTSlide.SlideNumber
Set sht = Worksheets("Sheet1")
        sht.Activate
        Dim S2 As String
        Dim Cellname As String
        Cellname = "A" & i
            sht.Range(Cellname).Copy
            PPTSlide.Shapes.Paste
             With PPApp
                .ActiveWindow.Selection.ShapeRange.Align msoAlignTops, True
                .ActiveWindow.Selection.ShapeRange.Top = 350
                .ActiveWindow.Selection.ShapeRange.Left = 650
                .ActiveWindow.Selection.ShapeRange.Width = 400
             End With
        Application.ScreenUpdating = True
    Next
    PPApp.Activate
    PPPresentation.SaveAs V_PPTPath_New
    PPPresentation.Close
    PPApp.Quit
    Set PPShape = Nothing
    Set PPPresentation = Nothing
    Set PPApp = Nothing
    MsgBox "Done", vbOKOnly + vbInformation
End Sub

You might face clipboard Error while executing the above code, if you are still facing the error then try to re-execute the code by 2 or 3 times.

VB : run time error 429: ActiveX component can't create object

Error

run time error 429:

ActiveX component can't create object

After Having the Change from the Original Post 

"Compile Error: User Defined Datatype not defined" (Click Here)

To resolve error in code i used "Late Binding", then i did a attempt after few days on same work then facing a new error related to "ActiveX"

Again did some research and people/developer suggest different type of solutions but issue is straight forward.

In code you are using component of library which is not selected in reference for VBA

Solution

Note * - Your code should not in execution mode
note * - Change your code to original if you also had used "late binding"
1. Open VB
2. Go to "Tools"
3. Reference > you can see the popup "References VBAProject"
4. Scroll Down and select the required Library
**In my case I haven't selected the PowerPoint Library
so select "Microsoft PowerPoint 16.0 Object Library"
5. Go back and execute your code

VB : Compile Error: User Defined Datatype not defined

I was executing macro Code and trying to access excel and powerpoint file from the macro code. While executing the macro Code getting below error :

Compile Error: 

User Defined Datatype not defined

Here are the few lines of the Macro Code:

Dim V_Excel As Excel.Application
Dim v_book As Excel.Workbook
Dim v_sheet As Excel.Worksheet
Dim v_ppt As Presentation
Dim v_slide As Slide

Solution

After doing the R&D, getting the result from google that this problem can be solved using late binding methods

Dim V_Excel As Object
Set V_Excel = CreateObject("Excel.Application")
Dim v_book As Object
Set v_book  = CreateObject("Excel.Workbook")
Dim v_sheet As Object
Set v_sheet  = CreateObject("Excel.Worksheet")
Dim v_ppt As Object
Set v_ppt  = CreateObject("Presentation")
Dim v_slide As Object
Set v_slide = CreateObject("Slide")

ORA-12899: value too large for column ColumnName

Error  :

. . imported "DatabaseName"."TableName"                   171.3 KB    5789 rows
ORA-02374: conversion error loading table "DatabaseName"."TableName"
ORA-12899: value too large for column ColumnName (actual: 31, maximum: 30)
ORA-02372: data for row: ColumnName : 0X'5467ASH567UJGFRT4VXH567HFGHK5EWHD57GQ5'

You are trying to restore the database with IMPDP command, sometimes you will receive the error in logs, ORA-12899: value too large for column Column Name

If you faced this error too, then solve as below:

1. Check the table name and column name from the logs, for which this error occur
2. After restore complete alter the table and increase the size of column (using alter command)
3. Again execute the IMPDP command for specific table only and include the below 3 parameter in your IMPDP command

Use below 3 parameter in your IMPDP command, see below parameter and description

• remap_Table - Specify which table need to map with existing table
• tables - Specify, which particular table need to restore
• table_exists_action - Specify, what should be the action if table already exists, these are of 4 type  {SKIP | APPEND | TRUNCATE | REPLACE}, for more information you can read official website

use below parameter along with the usage, see below:

• remap_Table = OldTableName_InBackup:NewdatabasetableName
• tables = TypeYourTableNameHere
• table_exists_action=Truncate

See full IMPDP command here (Click Here)

ORA-39151: Table "DatabaseName"."TableName" exists.

Error
ORA-39151: Table "DatabaseName"."TableName" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Problem :

1. you might be trying to restore database using IMPDP command and in IMPDP using existing database to restore and used "remap_Table" in your IMPDP command

2. You might try to restore single table using "remap_Table" paramter

Solution:

Use below 3 parameter in your IMPDP command, see below parameter and description

• remap_Table - Specify which table need to map with existing table
• tables - Specify, which particular table need to restore
• table_exists_action - Specify, what should be the action if table already exists, these are of 4 type. {SKIP | APPEND | TRUNCATE | REPLACE}, for more information you can read official website

use below parameter along with the usage, see below:

• remap_Table = OldTableName_InBackup:NewdatabasetableName
• tables = TypeYourTableNameHere
• table_exists_action=Truncate

Restore Table From Full Backup

Scenario is you have database/User already exists and received a new Database Backup and you want to restore only 1 table instead of full database restore. And you want to restore table in existing database/User. So you need to use 3 new parameters in your existing restore IMPDP command. These are as below:

• remap_Table
• tables
• table_exists_action

Full IMPDP command

impdp 
DIRECTORY=DirectoryName
DUMPFILE=Backupname.DMP 
LOGFILE=AnyFileName.log 
PARALLEL=8 
EXCLUDE=INDEX 
remap_schema=OldUser_Inbackup:NewUser
remap_tablespace=OldTablespace_Inbackup:NewTablespace
exclude=statistics
--New Paramaters
remap_Table = OldTableName_InBackup:NewdatabasetableName
tables = TypeYourTableNameHere
table_exists_action=Truncate

Talend Start Up Error : An error occurred while automatically activating bundle org.talend.designer.core (1557).

This error generated in log file of Talend during startup of talend job. 

!SESSION 2020-11-11 12:35:58.951 -----------------------------------------------
eclipse.buildId=unknown
java.version=1.8.0_271
java.vendor=Oracle Corporation
BootLoader constants: OS=win32, ARCH=x86_64, WS=win32, NL=en_US
Command-line arguments:  -os win32 -ws win32 -arch x86_64
 
This is a continuation of log file D:\Talend_Integration\TOS-Win32-r30698-V3.2.0\workspace1\.metadata\.bak_0.log
Created Time: 2020-11-11 12:35:59.910
!SESSION 2020-11-11 12:35:58.951 -----------------------------------------------
eclipse.buildId=unknown
java.version=1.8.0_271
java.vendor=Oracle Corporation
BootLoader constants: OS=win32, ARCH=x86_64, WS=win32, NL=en_US
Command-line arguments:  -os win32 -ws win32 -arch x86_64
 
!ENTRY org.eclipse.osgi 4 0 2020-11-11 12:35:59.911
!MESSAGE An error occurred while automatically activating bundle org.talend.designer.core (1557).
!STACK 0
org.osgi.framework.BundleException: The activator org.talend.designer.core.DesignerPlugin for bundle org.talend.designer.core is invalid

Issue
Previous Talend was working, suddenly after weekend restart, Talend stop working

Problem
Java got updated during restart

Solution
Start Talend UI by pointing to specific JDK installed previously. 

Use Command (run in CMD): 

• TOS_DI-win32-x86.exe -vm "C:\Program Files (x86)\Java\jdk1.7.0_65\bin"

Or See detailed (Click Here)

Semicolon, comma separated data to Rows

 If you have data in column in comma separated or semicolon separated and you would like to separate each one to a new row, it is pretty simple in SQL server. Use below query as per your requirement, right now this query is looking for semicolon as separator, and you modify the query as per requirement. Change the column names as per your table structure.

;WITH temp(ID, Newcolumn, Name) AS

(
    SELECT
        Id,
cast(LEFT(Name, CHARINDEX(';', Name + ';') - 1) as nvarchar(2000)),
        STUFF(Name, 1, CHARINDEX(';', Name + ';'), '')
    FROM 
YOURTABLENAME
where 
name like '%;%'
    UNION all
    SELECT
        Id,
cast(LEFT(Name, CHARINDEX(';', Name + ';') - 1) as nvarchar(2000)),
        STUFF(Name, 1, CHARINDEX(';', Name + ';'), '')
    FROM 
temp
    WHERE
        Name > ''
)
SELECT
    Newcolumn 
FROM 
temp 

Do not change the column name "Newcolumn" from query, as this column is key column for your query. will store all new data in this column.

how table data looks like for me:

YOURTABLENAME

id      Name

1       Andhra Pradesh; Arunachal Pradesh; Assam; Bihar; Chhattisgarh; Goa; Gujarat; Haryana; Himachal Pradesh; Jammu and Kashmir; Jharkhand; Karnataka; Kerala; Madhya Pradesh; Maharashtra; Manipur; Meghalaya; Mizoram; Nagaland; Odisha; Punjab; Rajasthan; Sikkim; Tamil Nadu; Telangana; Tripura; Uttar Pradesh; Uttarakhand; West Bengal; Andaman and Nicobar; Chandigarh; Dadra and Nagar Haveli; Daman and Diu; Lakshadweep; Delhi; Puducherry

2       Alabama;  Alaska;  American Samoa;  Arizona;  Arkansas;  California;  Colorado;  Connecticut;  Delaware;  District of Columbia;  Florida;  Georgia;  Guam;  Hawaii;  Idaho;  Illinois;  Indiana;  Iowa;  Kansas;  Kentucky;  Louisiana;  Maine;  Maryland;  Massachusetts;  Michigan;  Minnesota;  Minor Outlying Islands;  Mississippi;  Missouri;  Montana;  Nebraska;  Nevada;  New Hampshire;  New Jersey;  New Mexico;  New York;  North Carolina;  North Dakota;  Northern Mariana Islands;  Ohio;  Oklahoma;  Oregon;  Pennsylvania;  Puerto Rico;  Rhode Island;  South Carolina;  South Dakota;  Tennessee;  Texas;  U.S. Virgin Islands;  Utah;  Vermont;  Virginia;  Washington;  West Virginia;  Wisconsin;  Wyoming

How data will appear after executing query

1 Andhra Pradesh

1 Arunachal Pradesh

1 Assam

1 Bihar

1 Chhattisgarh

1 Goa

1 Gujarat

1 Haryana

1 Himachal Pradesh

1 Jammu and Kashmir

1 Jharkhand

1 Karnataka

1 Kerala

1 Madhya Pradesh

1 Maharashtra

1 Manipur

1 Meghalaya

1 Mizoram

1 Nagaland

1 Odisha

1 Punjab

1 Rajasthan

1 Sikkim

1 Tamil Nadu

1 Telangana

1 Tripura

1 Uttar Pradesh

1 Uttarakhand

1 West Bengal

1 Andaman and Nicobar

1 Chandigarh

1 Dadra and Nagar Haveli

1 Daman and Diu

1 Lakshadweep

1 Delhi

1 Puducherry

2 Alabama

2 Alaska

2 American Samoa

2 Arizona

2 Arkansas

2 California

2 Colorado

2 Connecticut

2 Delaware

2 District of Columbia

2 Florida

2 Georgia

2 Guam

2 Hawaii

2 Idaho

2 Illinois

2 Indiana

2 Iowa

2 Kansas

2 Kentucky

2 Louisiana

2 Maine

2 Maryland

2 Massachusetts

2 Michigan

2 Minnesota

2 Minor Outlying Islands

2 Mississippi

2 Missouri

2 Montana

2 Nebraska

2 Nevada

2 New Hampshire

2 New Jersey

2 New Mexico

2 New York

2 North Carolina

2 North Dakota

2 Northern Mariana Islands

2 Ohio

2 Oklahoma

2 Oregon

2 Pennsylvania

2 Puerto Rico

2 Rhode Island

2 South Carolina

2 South Dakota

2 Tennessee

2 Texas

2 U.S. Virgin Islands

2 Utah

2 Vermont

2 Virginia

2 Washington

2 West Virginia

2 Wisconsin

2 Wyoming

Concatenate Rows in SQL Server

In SQL Server if you would like to concatenate row to 1 single string, with using stuff, XML path aggregation, alternatively we can use T/SQL program.

I am preferring the TSQL cause of few reasons, first reason as before i was using XML Path aggregation and while using that method string truncation occurs at some limit. XML Path/stuff was not able to aggregate or concatenate all rows from table in single row.

Either we need to limit the number of rows while using above way.

in TSQL we can handle this situation, in that way we can play with length of charaters that need to concatenate.

How to use below TSQL:

first you guys need to populate your data in below table format, table name and column name should be exactly same.

Table Name - Table_Group

Column Name 1 - Column_id (will be using for group by )

Column Name 2 - column_text (that need to concatenate)

IF exists (select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'data' AND TABLE_SCHEMA = 'dbo')

Drop table dbo.data;

create table data (id int, content nvarchar(max))

Declare

@out1 VARCHAR(max),

@Query_Part1 VARCHAR(max), 

@Query_Part2 VARCHAR(max), 

@Query_Part3 VARCHAR(max),

@Query_Part4 VARCHAR(max),

@Query_Final VARCHAR(max),

@row_num int

Declare

@column_id VARCHAR(400), 

@column_text VARCHAR(max) ,

@n int

set @Query_Part1 = '';

set @Query_Part2 = '';

set @Query_Part3 = '';

set @Query_Part4 = '';

set @Query_Final = '';

set @n = 0;

set @row_num = 0;

DECLARE CURSOR_C1 CURSOR FOR SELECT distinct column_id  FROM Table_Group  order by 1

OPEN CURSOR_C1

FETCH NEXT FROM CURSOR_C1 INTO @column_id 

WHILE @@FETCH_STATUS=0

BEGIN

DECLARE CURSOR_C2 CURSOR FOR 

SELECT column_text, row_number() over (order by column_id ) rn FROM Table_Group where column_id  = @column_id order by 2

OPEN CURSOR_C2

FETCH NEXT FROM CURSOR_C2 INTO @column_text , @row_num

WHILE @@FETCH_STATUS=0

BEGIN

IF isnull(LEN(@Query_Part2),0) < 1 AND (isnull(LEN(@Query_Part1),0) + isnull(len(@column_text),0)  ) < 32767 

set @Query_Part1 = @Query_Part1 + char(10)+ '-Line  ' +cast(@row_num as nvarchar)+ ' - '+char(10) +@column_text 

ELSE IF isnull(LEN(@Query_Part3),0) < 1 AND (isnull(LEN(@Query_Part2),0) + isnull(len(@column_text),0) ) < 32767 

set @Query_Part2 = @Query_Part2 +char(10)+ '-Line  ' +cast(@row_num as nvarchar)+ ' - '+char(10)  +@column_text 

ELSE IF isnull(LEN(@Query_Part4),0) < 1 AND (isnull(LEN(@Query_Part3),0)  + isnull(len(@column_text),0)) < 32767 

set @Query_Part3 = @Query_Part3 + char(10)+ '-Line  ' +cast(@row_num as nvarchar)+ ' - '+char(10)  +@column_text 

ELSE

set @Query_Part4 = @Query_Part4 +char(10)+ '-Line ' +cast(@row_num as nvarchar)+ ' - ' +char(10) +@column_text 

set @Query_Final = isnull(@Query_Part1,'')+isnull(@Query_Part2,'')+isnull(@Query_Part3,'')+isnull(@Query_Part4,'')

--print @Query_Final

FETCH NEXT FROM CURSOR_C2 INTO @column_text , @row_num

END

CLOSE CURSOR_C2;

DEALLOCATE CURSOR_C2;

insert into data values ( @column_id , @Query_Final) ;

set @Query_Final = ''

set @Query_Part1 = ''

set @Query_Part2 = ''

set @Query_Part3 = ''

set @Query_Part4 = ''

FETCH NEXT FROM CURSOR_C1 INTO @column_id;

set @row_num = 0 ;

END

CLOSE CURSOR_C1;

DEALLOCATE CURSOR_C1;

select *from data

Window functions in SQL Server

we have 3 type of Window functions, many of the candidate is not aware about the window function during interview session

• Aggregate Window Functions
• SUM(), MAX(), MIN(), AVG(). COUNT()
• Ranking Window Functions
• RANK(), DENSE_RANK(), ROW_NUMBER(), NTILE()
• Value Window Functions
• LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE()

Working

    We all know the aggregate functions, SUM(), MAX(), MIN(), AVG(). COUNT(), if these are executed on whole table then we need to GROUP BY clause in a query. and It will return single result for a query.

These can also used with Over (partition by ) clause in a query. In this case no need to mention group by clause and aggregate function will be return result for each row.

Example of Query, aggregate function with group by clause

SELECT c1, SUM(c2) alias FROM table_name GROUP BY c1;

Example of query, aggregate function without group clause

SELECT c1, c2, c3, c4, c5 ,SUM(c6) OVER(PARTITION BY c2) as alias FROM table_name;

Note* - You can not include the ID (PRIMARY_KEY column of table) while using aggregate function with GROUP by clause.

But we can use ID column in query, when aggregate function using as window function.

This post is for intend to know only type of window function. 

ORA-01940: cannot drop a user that is currently connected

Cause : One is attempt to drop a user and user is currently connect to database, 

Resolution : we need to drop existing session of user, using below command :

ALTER SYSTEM KILL SESSION '*SID*, *SERIAL*';

See Full Post here, to find SID and SERIAL number of connected user (CLICK HERE)

Now you will be able to drop user using command 

Drop user username cascade;

 

Kill Connection of Connect Users Oracle

This post is going to help you if you would like to drop session of connected users of Oracle.

Find the list of connected users using below query :

SELECT 

vs.sid

, vs.serial#

, vs.status

, vp.spid 

FROM 

v$session vs

, v$process vp 

WHERE 

vp.addr(+) = vs.paddr;

If you would like to find the connected sessions for 1 specific user use below where condition :

and vs.username = '*****'

Note * Oracle is case sensitive, use CAPITAL letters only

Use below query to kill session of users, use input of SID/SERIAL from above query.

ALTER SYSTEM KILL SESSION '*SID*, *SERIAL*';

Kill EXPDP Job -Oracle

impdp/expdp give you facility to kill the job if any thing you have commanded wrong or written wrong parameter in impdp/expdp command.

How :

1. Assume you have already started expdp command
2. Press ctrl + C to exit prompt
3. enter "KILL JOB" or 
4. STOP_JOB=IMMEDIATE

ORA-02304: Invalid Object Identifier Literal

Was doing the impdp (import) on oracle, restore of database and observed new error populate in logs

ORA-02304: Invalid Object Identifier Literal

CREATE TYPE ***.**** OID "someNUMBERS" AS OBJECT
(
)
ORA-39083: Object type TYPE:  failed to create with error:
ORA-02304: invalid object identifier literal 

SOLUTION:

Use Parameter transform=oid:n in impdp command, it will be looks like

impdp ******* *** ***** transform=oid:n

Error While Restore Oracle Database - ORA-28365: wallet is not open

ORA-39083: Object type TABLE:"DBNAME" failed to create with error:
ORA-28365: wallet is not open

SQL Error: ORA-28367: wallet does not exist
28367. 0000 -  "wallet does not exist"
*Cause:    The Oracle wallet has not been created or the wallet location
           parameters in sqlnet.ora specifies an invalid wallet path.
*Action:   Verify that the WALLET_LOCATION or the ENCRYPTION_WALLET_LOCATION
           parameter is correct and that a valid wallet exists in the path
           specified

Problem : I was trying to restore the database on Oracle machine. Then it is giving above mention error. Since i resolved the problem months ago and not remember accurately, the sequence of error i faced.

Solution :

I performed below steps while fixing above problem

1. Please add below in sqlnet.ora and restarted the services

-------------------------

SQLNET.AUTHENTICATION_SERVICES= (NONE) 

--which was NTS earlier

WALLET_LOCATION =

   (SOURCE =

     (METHOD = FILE)

     (METHOD_DATA =

       (DIRECTORY = C:\app\oracle\admin\<sid>\wallet)

     )

   )

SQLNET.WALLET_OVERRIDE = False

SSL_CLIENT_AUTHENTICATION = FALSE

SSL_VERSION = 0

-----------------------------

2.Confirm the follwing content should be present in tnsname.ora 

NEAORACLE =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = **hostname**)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = <sid>)

    )

  )

  

3. Create folder wallet in this path (C:\app\oracle\admin\<sid>\)and give full access to ewallet file by right clicking on it properties>security

4. Execute following query in sys db

select * from v$encryption_wallet; --- here wallet_type is unknown and status  is not open

administer key management set keystore close; -- to make it wallet type as  password

administer key management set keystore open identified by admin_123;--it will create ewallet file

administer key management set key identified by admin_123 with backup;--change password then in import utility  use ENCRYPTION_PASSWORD=admin_123--use password which u set for key

5. open cmd ,set sid and execute following command to import table into existing db

   

   impdp sys/password@<sid> DIRECTORY=Source_dmp ENCRYPTION_PASSWORD=<pwd> DUMPFILE=<dumpname> TABLES=<tname> PARALLEL=8 EXCLUDE=INDEX remap_schema=<originalSchema>:<remapSchema> remap_tablespace=<originalTablespace>:<remapTablespace> exclude=statistics 

Minus 2 dates and calculate the difference in years/Months/Days separately

 If you are working in Excel and trying to write a formula to subtract 2 dates and if you need output in number of Years/Months/Days then use below formula. 

=INT((TODAY()-D3)/365.25) & " years , " & INT(MOD((TODAY()-D3)/365.25|1)*12) & " months and " & INT(MOD((TODAY()-D3)/30.4375|1)*30.4375) & " days"

It will give output like below format :

4 Years, 5 months and 12 days

You can change alias or comma as per your choice

Minus/Subtract 2 dates in Excel, Result is not showing in Number of Days

I was working in excel and trying to subtract 2 dates. Minus old date from today date to find the ages of something in number of days.

But when i apply formula "=Today()-A2" in excel it is showing output not as expected.

Problem : Column formatting is not set

Solution : Right click the column and > Format > Change formatting to "Text" or "General"

SQL Server : Find Out the List of Backups taken of Database

Like Restore Audit, we can also find the list of all backup taken, Since SQL Server store the backup history in system tables, using them we can find the information regarding same.

SELECT  bs.database_name
    , bs.backup_start_date
    , bs.compressed_backup_size
    , bs.expiration_date
    , bs.name Backup_Name
    , bs.recovery_model
    , bs.server_name
    , CASE bs.type 
        WHEN 'D' THEN 'Database' 
        WHEN 'L' THEN 'Log' 
        ELSE '[unknown]' END
    , bmf.logical_device_name
    , bmf.physical_device_name
FROM 
msdb.dbo.backupset bs
    INNER JOIN msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
WHERE 
     bs.type = 'D' and
bs.database_name = '**FillDatabaseName**'
ORDER BY 
bs.backup_start_date DESC;

You can copy and paste above query to Query Window and replace "**FillDatabaseName**" string with database name whose backup history need to populate.

Interview Question 2 - Count of all Occurrence of status/color until it changes to another

 Reproduce table script

select * from (
select '1' id ,'2020-06-01' reporting_month ,'RED' STATUS union all
select '1' id ,'2020-05-01' reporting_month ,'RED' STATUS union all
select '1' id ,'2020-04-01' reporting_month ,'RED' STATUS union all
select '1' id ,'2020-03-01' reporting_month ,'GREEN' STATUS union all
select '1' id ,'2020-02-01' reporting_month ,'RED' STATUS union all
select '1' id ,'2020-01-01' reporting_month ,'RED' STATUS union all
select '2' id ,'2020-06-01' reporting_month ,'RED' STATUS union all
select '2' id ,'2020-05-01' reporting_month ,'RED' STATUS union all
select '2' id ,'2020-04-01' reporting_month ,'RED' STATUS union all
select '2' id ,'2020-03-01' reporting_month ,'RED' STATUS union all
select '2' id ,'2020-02-01' reporting_month ,'RED' STATUS union all
select '2' id ,'2020-01-01' reporting_month ,'GREEN' STATUS union all
select '3' id ,'2020-06-01' reporting_month ,'RED' STATUS union all
select '3' id ,'2020-03-01' reporting_month ,'RED' STATUS union all
select '3' id ,'2020-02-01' reporting_month ,'RED' STATUS union all
select '3' id ,'2020-01-01' reporting_month ,'RED' STATUS union all
select '4' id ,'2020-06-01' reporting_month ,'RED' STATUS union all
select '4' id ,'2020-05-01' reporting_month ,'RED' STATUS union all
select '4' id ,'2020-04-01' reporting_month ,'GREEN' STATUS union all
select '4' id ,'2020-03-01' reporting_month ,'RED' STATUS union all
select '4' id ,'2020-02-01' reporting_month ,'RED' STATUS union all
select '4' id ,'2020-01-01' reporting_month ,'RED' STATUS union all
select '5' id ,'2020-06-01' reporting_month ,'GREEN' STATUS union all
select '5' id ,'2020-05-01' reporting_month ,'RED' STATUS union all
select '5' id ,'2020-04-01' reporting_month ,'GREEN' STATUS union all
select '5' id ,'2020-03-01' reporting_month ,'RED' STATUS union all
select '5' id ,'2020-02-01' reporting_month ,'RED' STATUS union all
select '5' id ,'2020-01-01' reporting_month ,'RED' STATUS union all
select '6' id ,'2020-06-01' reporting_month ,'RED' STATUS union all
select '6' id ,'2020-03-01' reporting_month ,'RED' STATUS union all
select '6' id ,'2020-02-01' reporting_month ,'GREEN' STATUS union all
select '6' id ,'2020-01-01' reporting_month ,'RED' STATUS  ) COMMULATIVE_SUM

Need in output , count of Status for first all occurrence of RED color (Occurrence will be end by GREEN color)

--Interview Question (SQL Query)
"Solution 1 "

select 
id
, min(NewRank)-1  
from
(
select 
id
, reporting_month
, STATUS
, case when STATUS = 'GREEN' then TRank else 99 end NewRank
, TRank
from
(
select 
DENSE_RANK() over (partition by id order by id, reporting_month desc) as TRank
, id
, reporting_month
, STATUS
from
COMMULATIVE_SUM
) d
) d2 
where 
NewRank <>99
group 
by id

"Solution 2 "
select 
id
,count(*)
from
(
select 
id,
sum(case when status = 'Red' then 0 else 1 end)
over(partition by id order by reporting_month desc) as r
from 
COMMULATIVE_SUM
)as t
where 
r=0
group by id

Interview Question 1 - Print only those ParentID which are having 1 Female Child and 1 Male Chile both

Create table in sqlserver database using below query

 

select * into test from (
select '1' p,'M' as g union all
 select '1','M' as g union all
 select '1','F' as g union all
 select '2','F' as g union all
 select '2','F' as g union all
 select '2','M' as g union all
 select '3','F' as g union all
 select '3','M' as g union all
 select '4','M' as g union all
 select '5','F' as g union all
 select '6','F' as g union all
 select '6','M' as g union all
 select '7','F' as g union all
 select '7','F') temp

Table Looks like as below:

Col1 Col2

1 M

1 M

1 F

2 F

2 F

2 M

3 F

3 M

4 M

5 F

6 F

6 M

7 F

7 F

Query 

select 

t1.p 

from (

select 

count(*) F_Count

, p 

from 

test 

where 

g = 'F' group by p

)t1 

join

(

select 

count(*) M_Count

, p 

from 

test 

where 

g = 'M' group by p

) t2 on t1.p = t2.p

where 

F_Count = 1 and M_Count =1 

Query 2

select distinct

    p

from

    (

    select

        p,

        g,

        count(*) over (partition by p) count,

        count(case when g='M' then 1 end)

            over (partition by p) male,

        count(case when g='F' then 1 end)

            over (partition by p) female

    from

        test

    )as t

where

    t.male=1

    and

    t.female=1

ORA-28040: No matching authentication protocol

Detail

Oracle installed - 12 C, General install on Developer machine

Error

ORA-28040: No matching authentication protocol

Reason could be

No authentication protocol defined for either oracle client or oracle server.

Solution

I have identified 2 solution as during fix of this error

One is we can set the parameter in sqlnet.ora file "ALLOWED_LOGON_VERSION_SERVER", where we can specify the minimum number of authentication protocol can connect to oracle database. you can set below parameter as per the requirement.

SQLNET.ALLOWED_LOGON_VERSION_SERVER=10

SQLNET.ALLOWED_LOGON_VERSION_CLIENT=10

2nd thing i observed, If oracle server is 12C and you are creating connection in any of tool using OJDBC6.jar file then i suggest to update your jar file to OJDBC7.jar  which can also able to resolve the problem.

TNS-12560: TNS:protocol adapter error/ TNS-00530: Protocol adapter error

Scenerio: 

Installed new oracle instance on machine and Setup listener and TNS. After  that i was able to connect the users either from cmd sqlplus or SQL Developer.

I deleted the current listener and TNS and cleared the file tns. Observed that for me TNS services is no longer in system. Then i recreated the lsnrctl and TNS. Probable you will able to create the listener successfully but might be not able to start using cmd or services.

CMD > LSNRCTL > start

You might see some error. 

***********************************************************************************

at that point you will not able to connect any database as well, it may throw below error :

 Unable to OpenSCManager: err=5

TNS-12560: TNS:protocol adapter error/ 

 TNS-00530: Protocol adapter error

***********************************************************************************

 

In order to solve the error i just open CMD as administrator then i re-execute the command LSNRCTL was now working for me.

Coming Back to point, after resolving the error related to LSNRCTL/TNS, i tried to connect user or database from SQL Developer from remote machine (in network) it was working fine for me.

Change Machine Password While Working on Remote Machine

Want to change the password of remote machine. Are you not able to able to change the Password for remote machine (remote machines - mstsc 1 > mstsc 2 >mstsc 3) no problem perform below steps:

1. Login remote machine (for which you want to change the Password)
2. Enter in Full Screen Mode ( maximize Window)
3. Open On-Screen-Keyboard on machine where need to change the Password
4. Press button CTRL + ALT ( from Keyboard) and DEL (using mouse, from on-screen-keyboard)
5. Choose Option "Change Password"
6. Change your Password !

ERROR While EXPDP (Oracle Restore) : ORA-39143: dump file "c:\**********\.dmp" may be an original export dump file

Error :

ORA-39001 : invalid argument value

ORA-39000 : bad dump file specification

ORA-39143: dump file "c:\**********\.dmp" may be an original export dump file

I faced this error while importing (impdp) oracle dump in database using impdp command, and there is no error in Command

impdp remap_schema=OLD:NEW exclude=statistics remap_tablespace=OLD:NEW DIRECTORY=MY_DIR_ABC DUMPFILE=expdpDumpFile.dmp LOGFILE=TodayLogs.log PARALLEL=8 EXCLUDE=INDEX

Cause :

We need to concentrate on error ORA-39143: dump file "c:\**********\.dmp" may be an original export dump file. Where we can able to find actual root cause of the issue. Here condition is we are not aware the the backup was taken using exp or expdp.

If above error is appear then the dump (backup) is taken using command "exp"

Solution:

Use IMP command to restore the database:

imp Sys as sysdba/Password FROMUSER=OLD TOUSER=NEW file=expdpDumpFile.dmp ignore=y indexes=n statistics=none constraints=n log=TodayLogs.log grants=n

Generate Multiple copy of Document/Files

Requirement :

Here i faced requirement, that i need multiple duplicate organization for R&D purpose. Where the Document name should have some sequence like 1,2,3,4

I was searching for open source tool to generate random files but those are giving random names to file.

So i used simple CMD command to generate files, it is quite easy:

We just need to pass the file name, i opt for file which is having same in size and extension of PDF.

Open CMD and run below command:

for /l %A in (1,1,2000) do copy "D:\Duplicate\file.PDF" "D:\Duplicate\file-%A.PDF"

Basic SQL interview Questions - Part 1

This post contain the very basic question of SQL, today onward i will write multiple post with interview questions. So that it will be help you guys the question i am going to post really crispy and tricky. If you would like to share the any interview question with me, comment on any post . I will definitely

share it on my blog. 

1. Difference Between Group by Clause and Having Clause 

2. Different type of Clause in SQL Query

3. I have below table and some sample data in it, create a query to show the department name and total number employees working in department.

ID	DepartmentName	NumberOfEmployees
1	Dept A	140
2	Dept A	100
3	Dept B	300
4	Dept B	400
5	Dept C	400
6	Dept C	700

Once you will be able to find the Department Name and Its total number of employees, then write query to populate the records for condition, "Whose number of employee count more than 700"

4. What is left join how it will work, write query of left join and what will be output, for below sample tables/data. (Below table are refer using TabA.CountryID = TabB.ID 

Tab-A

ID	Name	CountryID
1	A	21
2	B	31
3	C	31
4	D	11
5	E	11
6	F	31

Tab-B

ID	CountryName
11	India
21	United Status
31	Singapore

2- question - Find the total number of employee living in each country, Write down the syntax and query output.

SQLServer Function Replace Special Char

I have 1 field which contains special characters in the data, and i want to remove special character and need to keep on A to Z, a to z, 0-9 and few special character allowed. I tried to use simple SQL query so that i can update column using update query using regular expression. But i noticed this is not working in replace function.

Regular expression worked only in WHERE clause to filter the records. So i have only way to create function to remove special characters from the string.

Create below function in database and use it to remove special character from string. Feel free to modify this as per your requirement.

CREATE FUNCTION ReplaceInvalidChar (@In_String VARCHAR(2000))
RETURNS VARCHAR(2000)
BEGIN
    DECLARE @temp VARCHAR(2000) = @In_String;
    DECLARE @List_Of_Invalid_Char VARCHAR (40) = '%[^0-9a-zA-Z_.#]%';
    DECLARE @In_String_Len INT;
    SELECT @In_String_Len = LEN(@In_String); 
    WHILE @In_String_Len > 0 
    BEGIN
        SET @In_String_Len = @In_String_Len - 1;
        IF (PATINDEX(@List_Of_Invalid_Char,@temp) > 0)
            BEGIN
                SELECT @temp = STUFF(@temp, PATINDEX(@List_Of_Invalid_Char,@temp),1,'');    
            END
        ELSE
        BEGIN
            BREAK;
        END
    END     
    RETURN @temp
END

SYS.DBMS_DATAPUMP' must be declared

impdp DIRECTORY=DIR DUMPFILE=dump_file.DMP LOGFILE=dump_file_log.log PARALLEL=8 EXCLUDE=INDEX remap_schema=Old_User:New_User remap_tablespace=Old_Tablespace:New_Tablespace exclude=statistics 

SYS.DBMS_DATAPUMP' must be declared

When This Error Came:

I tried to restore the Oracle Database and it throws the error. I Used correct credentials to connect oracle database.

My machine has multiple oracle Instances installed.

Both Instances are installed on separate Path.

Solution:

To solve this error i executed below command on CMD window. (make sure you are not entered in SQLPLUS)

SET ORACLE_SID=ORCL

using this command we need to tell the machine, which instance we are plan to use, if you wan to switch your your oracle instance re-run same query after exiting SQLPLUS cmd.

Database '*********Database Name*******' is already open and can only have one user at a time

When i was trying to restore database on SQL Server, after some time database restore got failed cause of insufficient space in drive. When i changed my mind and decide not to restore database. I click on "New Query" button to start SQL query window. what i observed it got connect to master database instead of my database on which i want to write SQL query.

Second thing i observed, my database entered in single user mode, it appear on left pane window as "Database Name (Single User)"

How to Fix this:

Execute below query after connecting to SQL Server on any database 

ALTER DATABASE DatabaseName SET MULTI_USER WITH ROLLBACK IMMEDIATE;

Your problem will get resolved and you will be able to execute query on your database.

pg_dump Postgres Backup Restore Error

This is new error i faced while taking backup of database from Postgres.

setup-

1. Server A -> Location ABC -> Postgres Server version - 9.6.18

2. Machine B -> Location EDF -> Postgres Server version - 9.5.3

I created connection of Server A from machine B and initiate backup from machine B of Server A database. My intention was to bring down the server database to another machine. 

In Postgres it is possible to create copy of backup at your local machine (machine B), if database is installed on another machine (as compare to in SQLServer it is not possible).

When i started taking backup, it throw below error:

pg_dump: server version: 9.6.11; pg_dump version: 9.5.3
pg_dump: aborting because of server version mismatch

Process returned exit code 1.

So, here i do not have option to install or re-install postgres of version 9.6.11 on my machine.

So what i did, postgres is open source, i get the pg_dump.jar file and pg_restore.jar file of version 9.6.11 and replaced the actual file of version 9.5.3.

Note* - take backup of pg_dump/pg_restore of 9.5.3 postgres install.

It is available in C directory of  postgres/version /bin

Now take backup and restore it will work!!