SQL Logical Questions and answers

1) Query to combine two columns but second columns called Name and Price. 

Name max length will be 60. Then after 60 length price column will start. 

SELECT Name
	,Price
	,CONCAT (
		LEFT(CONCAT (
				Name
				,Replicate(' ', 60)
				), 45)
		,Price
		) AS ConcatedString
FROM [portfolio];


Here name coulmn values current lenght is different however when we combine using price it should show padding.
This can be achieved using the LEFT and Replicate function.




2)If there is multiple fields we need to follow the same

DECLARE @Table AS TABLE (Val VARCHAR(4000))

INSERT INTO @Table
SELECT CONCAT (
		'Name'
		,Replicate(' ', 36)
		,'Price'
		,Replicate(' ', 10)
		,'Price Change'
		)

INSERT INTO @Table
SELECT CONCAT (
		LEFT(CONCAT (
				Name
				,Replicate(' ', 60)
				), 40)
		,LEFT(CONCAT (
				Price
				,Replicate(' ', 15)
				), 15)
		,[Price Change]
		)
FROM [portfolio]

SELECT *
FROM @Table


The above query information can be achieved by using LPAD and RPAD functions.
unfortunately SQL server do not have LPAD and RPAD functionality. 
It be achieved by creating function by using LPAD and RPAD functions.

Import CSV Files into SQL Server using SSMS (SQL Server Management Studio)

 SSMS tool is used for writing queries. Apart from this, it is very helpful tool to import the CSV data into SQL Server.

Suppose we have a CSV file like this which is having double quotes and comma separated and it has headers also.

To Load this file into SQL Server, We can import using the below.

Database --> Tasks --> Import Data

It opensup the wizard.

Click next. Select Flat File as Data Source and select the file path,

Format :  Delimited. In the above CSV the delimiter is comma. 

Header Row Delimeter :  It is comma

Header rows to skip :  We have headers in the csv file so it should be zero

Select check box column names in the first row data

Click on columns to preview

If you want to change anything, use the advanced tab 

To preview rows clik on preview. 

Click next and select SQL native client and SQL server instance name. We windows or SQL based on the usage. Select database. 

Click next and sleect target name. We can rename the table also if it new table. Since this table not exist in db ,SQL will create automatically.

If we want to change anything in destination mapping we can use edit mapping. If we are sure it is not exist we can use create destination table or use other options based on usage.

Run immediately to run the package.

Click Finish to start the package.

The package run completed successfully.

We can see verify the table data by running the select in the target database.

Java Program to connect SQL server and fetch results from database using stored procedure

1. Program to fecth the Output from SQL server using the stored procedure without parameter

2. Program to fecth the Output from SQL server using the stored procedure with parameter

package SqlOutput;

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.Scanner;

public class SQLServerConnection {

public static void main(String[] args) {

// Connection URL

String connectionUrl = "jdbc:sqlserver://myserver;"

+ "trustServerCertificate=true;"

+ "IntegratedSecurity=true;"

+ "Database=Stocks;"

+ "loginTimeout=30;";

Scanner scanner = new Scanner(System.in);

// Prompt the user for input

System.out.print("Enter Stock Name: ");

String name = scanner.nextLine();

System.out.println("Stock Name you entered is : " + name +" ");

//System.out.print("Enter your age: ");

//int age = scanner.nextInt();

// Display the input back to the user

// System.out.println("Hello, " + name + "! You are " + age + " years old.");

// Establish the connection

try (Connection connection = DriverManager.getConnection(connectionUrl))

{

// System.out.println("Connected to the SQL Server database successfully.");

CallableStatement stmt = connection.prepareCall("{call sp_StockInfo(?)}");

stmt.setString(1, name);

// Execute the stored procedure

ResultSet rs = stmt.executeQuery();

// Process the result set

while (rs.next()) {

System.out.println("Name: " + rs.getString("Name")+ " Quantity: " + rs.getString("Quantity"));

}

}

catch (SQLException e)

{

e.printStackTrace();

}

}

}

3. Program to fecth the multi results Output from SQL server using the stored procedure with parameters

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.ResultSetMetaData;

import java.sql.SQLException;

import java.util.Scanner;

public class SQLServerConnection {

public static void main(String[] args) {

// Connection URL

String connectionUrl = "jdbc:sqlserver://DESKTOP-H8VE8AJ;"

+ "trustServerCertificate=true;"

+ "IntegratedSecurity=true;"

+ "Database=Stocks;"

+ "loginTimeout=30;";

try

(

Scanner scanner = new Scanner(System.in)

)

{

// Prompt the user for input

System.out.print("Enter Stock Name: ");

String name = scanner.nextLine();

System.out.println("Stock Name you entered is : " + name +" ");

// Establish the connection

try (Connection connection = DriverManager.getConnection(connectionUrl))

{

//System.out.println("Connected to the SQL Server database successfully.");

CallableStatement stmt = connection.prepareCall("{call sp_Stock_Details(?)}");

stmt.setString(1, name);

// Execute the stored procedure

Boolean results = stmt.execute();

while (results) {

ResultSet rs = stmt.getResultSet();

ResultSetMetaData rsmd = rs.getMetaData();

int columnCount = rsmd.getColumnCount();

while (rs.next()) {

for (int i = 1; i <= columnCount; i++) {

String columnName = rsmd.getColumnName(i);

Object columnValue = rs.getObject(i);

System.out.println(columnName + ": " + columnValue);

}

}

results = stmt.getMoreResults();

}

}

catch (SQLException e)

{

e.printStackTrace();

}

}

}

}

Java Program to Connect SQL Server

 First time I tried to connect SQL Server using Java

'

Faced below issue while trying to connect

This driver is not configured for integrated authentication. We need to download the jdbc libraries and place in this path.

Dowloaded file

Place file in jre path

"connection refused: getsockopt. verify the connection properties. make sure that an instance of sql server is running on the host and accepting tcp/ip connections at the port. make sure that tcp connections to the port are not blocked by a firewall.".

This is due to TCP/IP Disabled. Enabled TCP/IP and restarted services.

Add jdbc drivers to connect SQL server. Download latest libraries from SQL server

PowerShell SQLInvoke-Sqlcmd Scripts

1) PowerShell Command to display date by Connecting SQL Server 

Invoke-Sqlcmd -Query "SELECT GETDATE() AS TimeOfQuery" -ServerInstance "DESKTOP-H8VE8AJ"

2) PowerShell Command to execute SQL Satement and save output to a file

Invoke-Sqlcmd -InputFile "C:\Users\OneDrive\Resumes\SQL Server Management Studio\Profit.sql" | Out-File -FilePath "C:\Users\OneDrive\Resumes\SQL Server Management Studio\Profit.rpt"

3) PowerShell command to pass the vairables

$StringArray = "MYVAR1='Machinename'", "MYVAR2='InstanceName'","MYVAR3='ServerName'

Invoke-Sqlcmd -Query "SELECT SERVERPROPERTY(`$(MYVAR1)) AS ComputerName,

SERVERPROPERTY(`$(MYVAR2)) AS InstanceName,

SERVERPROPERTY(`$(MYVAR3)) AS ServerName;" -Variable $StringArray -ServerInstance "DESKTOP-H8VE8AJ"

4) 

Openssl command for certficates and key generation

 1) To connect secure port using SSL we can use the below command

openssl s_client -connect hostname/localhostip:portnumber

ex :  openssl s_client -connect 127.0.0.1:443

2) To show the certificate from website we can use the below command

openssl s_client -showcerts -connect 127.0.0.1:443

3) To Print Certificate we can use OpenSSL

echo | openssl s_client www.google.con -connectwww.google.con:443 2>dev/null | openssl x509 text

4) To covert pkcs12 to pem format we can use OpenSSL

openssl pkcs12 -in "certificate in pkcs12 format" -nodes -out "new_cert.pem"

5) to convert the certificate from crt to pem format

openssl x509 -in "cert.crt" -out "cert.pem"

6) to convert the certificate from cer to pem format

openssl x509 -in "cert.cer" -out "cert.pem"

7) to convert the certificate from pem to der format

openssl x509 -outform der -in "cert.pem" -out "cert.der"

8)  Openssl can used to generate public private key pair. Below uses pkcs8 format

openssl genrsa 4096 | openssl pkcs8 -topk8 -inform PEM -out rsa_key_4096.p8 -nocrypt

openssl rsa -in rsa_key_4096.p8 -pubout rsa_key_4096.pub

9)  Openssl can used to generate public private key pair. Below uses pkcs8 format

DB2 Commands

 1) db2level

This will show the DB2 product version.

2) db2 list node directory

This will show nodeDirectory list that contains hostname, service name, protocol, node name

3) db2 list database directory

This will show database name , database version, authetication type etc details

4)  db2 connect to "databasename" user "üsername" using "Password"

This will connect to database

Incident Repsonse Planning

 1. Network segregation and isolation procedures should be part of the CSIRT expertiences to:? Pick two reasons

a. Whether to reset password or rapidly recreate account

b. Procedures and criteria for when to clean vs. rebuild

c. Host OS (and Application) rebuild procedures

d. Isolate HVAs from other end points in the production environment (such as compromised workstations and servers), if feasible

e.Block attacker C2 channels at internet egress points

2. Of of five core functions defined in NIST CSF, where would security monitoring fall?

a.Detect

b.Identify

c.Protect

d.Respond

3. To successfully respond to incidents, you must: (Choose two)

a.Minimize risks

b.Notify your legal department

c.Fire the CISO

d.Minimize the number and severity of security incidents

4. When documenting a security incident it is recommended to: (Choose two)

a.Wait till the incident is being reviewed to document it

b.Write up the report by hand in a note book

c.move fast to stop the intruder

d.Make sure to include dates and times

5. If you do not have a robust incident response plan, you should what?

a.Set firm plans to update your incident response plan

b.Treat each event as an incident

c.Search the Internet for a plan you can use

d.Panic

6. An what is a system occurrence that could happen regularly or due to hardware or software malfunction, not necessarily caused by a security compromise

a.Bug

b.Event

c.Activity

d.Incident

7. Performing password resets and C2 channel blocking alone is ineffective without also detecting and removing attacker malware from hosts True or Flase?

a.True

b.False

8. It is possible to stop a hacker attack by removing your systems from the network. You have stopped the attack, but you have essentially done a denial of service attack on yourself. In this case you have taken the wrong steps. what prinicple have you violated?

a.Be Accurate

b.Do no harm

c.Keep calm

d.Implement the response plan

9. A successful CSIRT team consists of several key members Pick three

a.Incident Lead

b.Lead from Legal

c.Sales team

d.External partners

e.Departmental managers

10. In NIST SP 800-61 it recommends four caegories of Incident Serverity(Choose two)

a.Very Low

b.Low

c.Very High

d.None

11. Your companies security incident has been mitigated, to prevent it from happening again, you need to understand what actually happened.

a.The best process to investigate the how, what, when, and why of th eincident is what?

b.Implement Azrue Security Center

c.Post-incident review

d.Review the Pre-incident system status

e.Wait for the security consultants to share their report

12. It is NOT recommendedto try to determine who attacked. NIST in their Computer Security Incident Handling Guide states “Identifying and attacking host can be a time-consuming and futile process that can prevent a team from achieving its primary goal". What should be your primary goal?

a.Notify the government

b.Minimizing the business impact

c.Restoring from backups

d.Enabling two factor authentication

13. Two-thirds of survey respondents ranked cybersecurity as a top five risk management priority, but only x% expressed high confidence in their organization’s ability to manage and respond to a cyber event.What percentage of the surveyed companies had high confidence in their ability to response to a cyber event?

a.35

b.19

c.10

d.3

14. Of of five core functions defined in NIST CSF, where would managment of GDPR fall?

a.Respond

b.Protect

c.Detect

d.Identify