Nia’s readings

Nia’s readings from Library,

  1. Ms. Leakey Is Freaky! – by Dan Gutman
  2. Ms. Hannah Is Bananas! – by Dan Gutman
  3. Nancy Drew and the Clue Crew- Case of the Sneaky Snowman by Carolyn Keene
  4. Diary of a Wimpy Kid Rodrick Rules by Jeff Kinney
  5. Help your Kids with Computer Coding 
  6. The phantom bully by Jeffrey Brown.
  7. Goosebump series Be careful what you wish for Samantha Byrd
  8. Goosebump series Night of the Living dummy Lindy Powell
  9. Goosebump series Night of the Living dummy Kris Powell
  10. Goosebump series Say Cheese and die Greg Banks
  11. Goosebump series Say Cheese and die Shari Walkar
  12. Goosebump series Be careful what you wish for – Samantha Byrd
  13. Goosebump series Stay out of the basement
  14. Carolines secret message
  15. Coach Hyatt is a Riot ! by Dan Gutman
  16. The Riddle of the Robin

Summary of Web services and API testing using UFT

Summary of Web services and API testing using UFT

 

Web services

 
Demand of Web services is already high in the new internet era since applications are built in different technology / databases and connected through internet. So communication between is only possible through a commonly accepted media which are Web services
 
Web services are XML format based services which use media as HTTP to communicate and it is loosely coupled where there no hard binding with client.
 

WSDL

 
WSDL is a document in XML format which describes a web service and defines which transactions are available and data should be structured to send to those transactions.

WSDL (Web services description language) helps client to initiate the correct web service

Webserver client needs WSDL to initiate Web services and Web server providers generates WSDL.
 

UDDI

UDDI is the online directory which has all the WDSL available and all web services client gets that from UDDI.

Web services has two implementations.
1. SOAP

2. REST

SOAP

 
SOAP is the XML based implementation for web service and it has 3 main components which are
a. Envelope – represents root elements
b. Header – stores authentication, routing id for web service transaction.
c. Body – stores all data which are intended to transfer to webservice client.

SOAP uses HTTP as media for transferring data. Format used in XML.

REST

 
REST is more flexible implementation as it uses text format and media other than HTTP as well.
 

API testing using UFT

 
UFT is a software automation tool. Apart from automation test scenarios it supports API testing. Following AP testing are supported.
 

Web service

REST

 
In UFT user can create a new API project by clicking File > New > Test..
Select the  API Test type
Then navigate to tool box in the left
Click on Network once it’s exploded,
Drag HTTP Request to Test flow
Right click and select the properties
Fill the URL, select HTTP method as GET
Also you can configure the checkpoints.

4 Dimensional Arrays in Java

Summary on Cloud, Machine learning, Artificial intelligence and other emerging technologies

Summary on Cloud, Machine learning, Artificial intelligence and other emerging technologies

Salesforce

Salesforce.com is an American cloud computing company.
Salesforce offers Software as a Service (SaaS) platform which helps in Customer Relationship Management.
It has a multi-tenant architecture and subscriptions.

 The following are the application clouds in Salesforce CRM.

  1. Sales Cloud
    2. Service Cloud
    3. Marketing Cloud
    4. Data cloud
    5. App Cloud
    6. Analytics Cloud
    7. Community Cloud

Salesforce also offers Platform as a Service (PaaS) using Force.com sites.

People involved in Salesforce Implementation
1. End User ( Customer)
2. Administrator
3. Developer
4. Consultant

The following are the list of Salesforce Certifications.

  1. Certified Administrator
    2. Certified Advanced Administrator
    3. Certified Sales Cloud Consultant
    4. Certified Service Cloud Consultant
    5. Certified Force.com Platform App Builder
    6. Certified Force.com Platform Developer I
    7. Certified Force.com Platform Developer II
    8. Certified Technical Architect

 

What is Apex?

  • Apex is a programming language for salesforce (only).
  • Object Oriented Program, in which the data types have to defined.
  • Allows developers for flow execution in force.com platforms.
  • Enables developers to add business logic to most system events including button clicks, related record updates and visualforce pages.

Datatypes in Apex

  • Primitives

Apex primitives include the following datatypes.

  1. Integer
  2. Boolean
  3. Decimal
  4. Double
  5. Date
  6. Date Time
  7. Time
  8. String
  9. Long
  10. ID- Any valid salesforce.com Id.
  • sObjects
  • Any  object that can be stored in force.com platform database.
  • sObject variable unlike primitive variable refers to row of data in salesforce. That is a complete record as a variable.

Hadoop MapReduce

Hadoop MapReduce is the main core components of Hadoop and is a programming model Hadoop MapReduce helps implementation for processing and generating large data sets, it uses parallel and distributed algorithms on a cluster. Hadoop MapReduce can handle large scale data: petabytes, exabytes.
Mapreduce framework converts each record of input into a key/value pair.

What is Blockchain and cryptocurrency

To understand more about Blockchain and crypto currency, lets explain about current Banking system works.

Current Banking systems : when a user does an online or ATM transaction , the centralized banking ledger verifies and confirm the authenticity of accounts. For that work, every bank or third party sites charges to user.

Blockchain is not like banking centralized ledger but this software uses a decentralized ledger across the thousands of computers and every transactions are updated in each and every ledger. That means everyone is aware of the transactions rather than a centralized bank stores all information and charges for that. There are volunteering systems who does this effort of maintaining all ledgers for block chain. 

Block chain uses cryptography mythology to protect the ledger information so that no one can modify or destroy this.

Block chain concept is utilized by Crypto currency , online voting system, signature system, agreement systems etc.

Top 20 cryptocurrency 2017

bitcoin BTC
ethereum ETH
bitcoincash BCH
ethereumclassic ETC
litecoin LTC
einsteinium EMC2
dash DASH
ripple XRP
bitcoingold BTG
zcash ZEC
eos EOS
qtum QTUM
syscoin SYS
neo NEO
monero XMR
vertcoin VTC
iota IOT
powerledger POWR
omisego OMG
santiment SAN

Summary about Jmeter and performance testing

Summary about Jmeter and performance testing

Jmeter recording

How to create Jmeter scripts?

Recording Jmeter scripts using Jmeter’s HTTP(S) Test Script recorder. There are two ways

1)      Manually adding scripts ( Test plan >> Tread Group >> Add>> Sampler >> HTTP Request )

2)      Recording using Jmeter’s HTTP(S) Test Script recorder

How to record Jmeter scripts using HTTP(S) Test Script recorder

Prior doing that we need to configure browser and learn about the Proxy setup

Jmeter to Application server communication need to be routed thru a browser Proxy for this recording purpose. For that you may need to select the Browser (here we can use Firefox browser”

1)      Navigate to Firefox >> Tools >> options >> Advanced >> Network >> Settings

2)      Select Manual Proxy configuration >> HTTP Proxy = localhost & Port = 8080

How to run Jmeter in Jenkins

steps:

Jenkins setup and performance plugin setup:
1) copy performance plugin into Jenkin’s home directry before you start the test.
2) to do that – you can download the performance plugin “performance.hpi” from the site https://wiki.jenkins-ci.org/display/JENKINS/Performance+Plugin
3) how to find the home directry of jenkins – to do that you can navigate to jenkins home page (localhost:8080) and then
4) click on “Manage Jenkins” and then click on “Configure system” the you will notice “Home directy” path in there.

Configure Jmeter and create a basic script in Jmeter:
5) download Jmeter Binary zip file from Jmeter site http://jmeter.apache.org/download_jmeter.cgi
6) unzip it and go to the “bin” directory of the Jmeter folder, wherre you will notice “user.properties” file.
7) open the file in notepad or notepad++ or any other text editor
8) add a line “jmeter.save.saveservice.output_format=xml” to that. Save and close the “user.properties” file
9) create a Jmeter script and make sure you can run it in non GUI mode i.e either in DOS mode or using shell scripts in MAC (note down the commands used in respective OS and it could be used in Jenkins job setup)
Note: for windows command line run, you can use the following commands

C:\[JMETER BIN directory]\jmeter.bat -Jjmeter.save.saveservice.output_format=xml -n -t C:\[JMETER script “.jmx” file] -l Jmeter_test_results.jtl

Note 1: For detailed topics on Jmeter scripts creation , please visit Jmeter , Jmeter recording 

setup Jenkin’s job to run Jmeter scripts
9) go to Jenkins home page (localhost:8080) and then click on “New Item” on “Freestyle project”
10) in the “New item” page, give some name in the item name field and go to below to section “Build” and add build step
11) add build step can be either a) “execute windows batch command” for windows or b) “Execute shell” for MAC
12) provide your command in there and save.
13) Click on “Build now” which would call the Jmeter (jmx) file and run the program
14) after the run, you can verify the Jenkins console to review the results.

Summary about Jenkins, Selenium and QTP automation frameworks

Summary about Jenkins, Selenium and QTP automation frameworks

How to run Jenkins locally

1) go to https://jenkins.io and download Jenkins.war file
2) place the war file in a folder (e.g. C:\Jenkins\Jenkins.war )
3) then open command prompt (windows start menu > run > cmd will open command prompt )
4) in command prompt, go to folder C:\Jenkins\ ( command : “CD C:\Jenkins\” )  and then run command ” java -jar Jenkins.war ”
5) this will exract a folder name “.jenkins” into your user folder ( the path is usually “C:\users\”your login”\.jenkins”
6) copy “.jenkins” folder and paste to folder C:\Jenkins\, so that you have “C:\Jenkins\.jenkins” as the folder path.
7) Now you setup a Enviroment variable (to set up that go to Control panel > system > Advanced System settings )
8) then add Enviroment variable as “JENKINS_HOME” and value as “C:\Jenkins\.jenkins”

You all set to run Jenkins on your machine!

Open a browser and type “localhost:8080” to see Jenkins session.

Selenium features

Selenium supports Cross Browser Testing and parallel testing. The Selenium tests can be run on multiple browsers and multiple machines and multiple Operating systems. Selenium offers open source tools and supports test automation for web applications

Supports several scripting languages like Java, Python, C#, and PHP

Reporting are very easy and extensible,

Selenium uses Browser native commands. It locates and actions applied on UI elements

TestNG quick summary:

 TestNG’s is Selenium + reporting features. It is easy to generate reports using Listeners and Reporters in TestNG

 Features of TestNG includes the following,

1)      Annotations – please refer my annotation blog Annotation – TestNG’s test case priority

2)      Supports parameterization/ data driven testing

3)      Test cases can be grouped and parallel testing is allowed.

Automaton testing using Cucumber – Behavior driven development (BDD)

Application behavior is the main focus on BDD. The requirements are written in English which gives more readability and understandability of the requirement.

So any roles in the company (Tester, developer, end user, and techno functional person) can read it and understand it. Also this will reduce the complication in terms of missing requirement or misrepresenting or misunderstanding requirements between different roles (Developer /tester / end user)

Gherkin is a business readable language – refer below for an example. Below feature file is integrated with Selenium and are having user stories as below.

Please refer below – “feature, Scenario, Given, When, And, Then “are the Keywords below to understand on how to write a feature file.

What is TestRunner in Cucumber

In order to link Cucumber with Selenium WebDriver, you need to start a Java project in eclipse IDE.

Then, add both Cucumber and Selenium jar files

Write test runner code and execute the same . Please refer BDD testing details on Selenium Cucumber

 

Selenium jenkins integration steps are as below:

 

Jenkin related steps:
1) start jenkins by running command in DOS prompt (detailed steps in here )
2) then make sure you are able to access url : localhost:8080
3) then you need to configure jenkins to run the selenium scripts – for that click on “Manage Jenkins”
4) then click on Configure systems
5) Find out the jdk files are installed (usually in C:\ program files … folder path) and copy paste the path in JDK section “JDK Installations” in ” Configure systems” which you are already in.
6) Give JDK name “JAVA_HOME” and provide Java path below in the next field.

Selenium related steps:
3) Open eclipse IDE and start new Java project
4) make sure you added selenium jars (Selenium server standalone jar file which you can download add to new project) to support selenium framework for your automation testing.
5) then create a simple selenium script – make sure it is running and completing successfully in Eclipse.
6) Also create a xml file give a name “Xml1.xml”  Make sure Run “Xml1.xml” as “TestNG Suite” is working fine in Eclipse. Also notedown the xml file path in selenium project. create a lib folder in that and add all jar files which are required.

Commmand prompt:
7) navigate to that project folder in command prompt and set all required classpath like bin and lib etc which are already in xml file folder in selenium project which you got from selenium project in eclipse.
8) then type command “Java org.testng.testNG Xml1.xml” which will display results in command prompt/DOS screen
9) now create a batch file “Batch1.bat” which contains the above command and also a command for compiling as e.g “java -cp bin;lib/* org.testng.testNG Xml1.xml”

Jenkins setup to build and run the file:
10) in Jenkins, create a “new item” with selection of “Freestyle project”
11) then select “Advance options” and put the Selenium project home directory path in there.
12) the build trigger section, select Batch file execution option in there, then put the batch file name “Batch1.bat”
13) click and save the new item
14) click on “Build Now” and verify the “Console output”

 

How to configure Hub & Node machines for selenium parallel test execution

Configuring Hub machine

1) Start the command prompt/DOS prompt

2) Navigate to the folder location where the Selenium server jar file is kept.

3) Type java –jar selenium-server-standalone-[VERSION].jar –role hub

Note: [VERSION] is the one your downloaded version of selenium-server-standalone Jar file.

Selenium Hub

4) Now type: http://localhost:4444 in browser

Your will see the below screen

5) Now type: http://localhost:4444/console in browser

Configuring Node machines

Follow the same steps 1 to 2 in node machines

Then type,

Java –jar selenium-server-standalone-[VERSION].jar –role node –hub http://yourHUBmachineIP:4444/grid/register

How to run selenium tests in Jenkins

Jenkins configuration:

To run selenium scripts in Jenkins, Jenkins needs to started.

To make Jenkins server start, please use the following command in the Jenkins folder of your machine when Jenkins.warfile is downloaded from internet…

  1. “java -jar jenkins.war” in DOS / command prompt mode.
  2. Then you need to bring the browser up and type “localhost:8080” to see jenkins web UI ready.
  3. Then navigate to Jenkins >> Manage Jenkins and click on “Configure system”
  4. Then scroll down to see the “JDK installations” section in the pag
  5. There you enter “JAVA_HOME” for “JDK name” field.
  6. Provide the Java-JDK file folder path for “JAVA_HOME” field.

Note:  Java-JDK file folder path is usually “C:\Program files\Java\JDK….”

Note: Disable “Install Automatically” check box else it will update with new Java versions and your selenium program might get conflicts

Selenium script development:

  1. create a package and class in Eclipse
  2. and then create a small test script – like print some message based on UI or Browser title name etc
  3. you could create a TestNG project
  4. Then TestNG.xml file will be created in Eclipse 

Note: to get the XML folder path in project home directory, you could go to Eclipse project and right click to see properties.

Note: you also need to put all the jar files required for selenium testing under the lib folder(you may need to create”lib” folder)

Just to test your TestNG scripts are working, you can navigate to Project home directly (XML located) thru DOS/Command prompt and then type as below:

Note: before that you need to set class path as bin directory of Project home folder. Command : “set classpath=C:\Selenium\project\bin;” as an example for binary file

Note1: similarly lib files, Command : “set classpath=C:\Selenium\project\lib\*” as an example

or command: set classpath=C:\Selenium\project\bin;C:\Selenium\project\lib\*;

Then execute testng.xml file thru command prompt

command : java org.testng.TestNG testng.xml

Batch file creation for Jenkin execution

Create a batch file using notepad – steps

  1. Type the below contents in Notepad without quotes.

‘ java -cp bi;lib/* org.testng.TestNG testng.xml ‘

2) save the notepad as “runSelenium.bat” which will create a bat file. Place it in project home directory.

Jenkins job creation:

  1. Open Jenkins, then click on “New item”
  2. Enter item name as “JenkinSeleniumTest”
  3. Select “Freestyle project” , click Ok
  4. Then click on “Advance” button under the “Advance projects options”
  5. Then select “Use custom workspace”, then give the project home directory path in “Directory” field.
  6. Then “Add build setup” under “Build” and select “Execute Windows batch Command”
  7. Then type “runSelenium.bat” in the command field, click apply and save it.
  8. Then in the “JenkinSeleniumTest”, then click on “Build Now” which will call the batch file and then Selenium script internally.
  9. Verify the “Console output” in “JenkinSeleniumTest” which will show the Selenium execution results.

How to send email at every build with Jenkins

Jenkins default configuration setting allows to send email notification for build failure, someone breaks the build etc.

  1. To send email for every build Install Email-ext plugin.
  2. Once it is installed, click on Configure System
  3. Then in “Jenkins Location” section & “Extended E-mail Notification” – enter your email ids
  4. In “E-mail Notification” section, enter the SMTP server name to “SMTP server”
  5. Click “Advanced”, Click “Use SMTP Authentication” to enter required information
  6. verify “Test configuration by sending test e-mail”
  7. Configure a project to send email at every build
  8. Click “Add post-build action” and then Click “Editable Email Notification”
  9. Go to  “Advanced Settings” to “Add Trigger”
  10. then Click “Always” , Save

Qtp script to close all open browsers ..

Call CloseAllBrowsers_ExcludingALM()

Function CloseAllBrowsers_ExcludingALM()
‘To ceclare Variables
Dim objDes, objList, objIndex

‘To Create Object Description for all opened browsers opened.
Set objDes=Description.Create
objDes.Add “micClass”,”Browser”

‘To get all the opened browsers objects from Desktop
Set ObjList=Desktop.ChildObjects(objDes)

‘Indexing always starts from “0”
For objIndex=0 to objList.count-1
‘To cverify the name of the browser is “HP Application Lifecycle Management”
If lcase(objList(objIndex).GetROproperty(“name”))<>”HP Application Lifecycle Management” then
‘Close the Browser
objList(objIndex).close
Exit For
End If
Next

Jenkins reset user password :

First stop the Jenkins service if you are already running jenkins

Go to the folder where jenkin’s config.xml file is stored (generally C:\jenkins\.jenkins\config.xml)

Open config.xml file using notepad++ or any text editor

Search for <useSecurity>true</useSecurity> and change that to <useSecurity>false</useSecurity>, Save the file.

re-start Jenkins service (using Java -jar jenkins.war in commandline)

You should not be seeing login prompt but directly accessing Jenkins home page when you type localhost:8080 in browser.

QTP integration with Jenkins

Create a “.vbs” file as below and save the file as “AOM.vbs” in C:\QTP\VBS folder.
In Jenkins create Job, Select ‘Execute Windows Batch Command’ in Build Step. Enter below command.
and then use that in Jenkins.
Note: To learn how to create a Jenkins job or how to start Jenkins – please refer create a Jenkins job and Run Jenkins locally in my site.
Below code is the “AOM.vbs” which is called by Jenkins Job.
Call QTP()
Public function QTP()
‘Create Quick test pro(QTP/UFT) object
Set objQTP = CreateObject(“QuickTest.Application”)
objQTP.Launch
objQTP.Visible = True
‘to open QTP Test
objQTP.Open “Test file path “, True
Set objResults= CreateObject(“QuickTest.RunResultsOptions”)
objResults.ResultsLocation = “Result path of qtp”  ‘Set the results location
‘to run qtp script results
objQTP.Test.Run objResults
‘to close QTP
objQTP.Test.Close
objQTP.Quit
End function

 

What is Thanksgiving?

What is Thanksgiving?

Thanksgiving is a national holiday of USA, Canada etc. In USA, it is celebrated on 4th Thursday of November. This day is to give thanks for the blessing of the harvest. Modern Thanksgiving has been changed a lot. Everyone are curious about the Thanksgiving deals 🙂 and it has been well celebrated as a holiday. Most of the organization gives holidays on 4th Thursday & Friday of November making it a long weekend. One can look for Thanksgiving recipes, deals, restaurants to make the holidays more enjoyable.

Top 5 sql interview questions and answers

Top 5 sql interview questions and answers

What is DDL, DML, DCL, TCL in SQL ?

DDL refers to Data Definition language. DDL is used to create/modify the structure of Database. e.g. CREATE, ALTER, DROP statement..

DML refers to Data Manipulation language. DML is used to retrieve, modify, delete, insert and update data in database. e.g. SELECT, UPDATE, INSERT statement

DCL refers to Data Control Language. DCL is used to create roles, permissions, and control access to database. e.g. GRANT, REVOKE statement.

TCL refers to Transactional Control Language. TCL is used to manage different transactions occurring within a database. e.g COMMIT, ROLLBACK statement

What is the difference between UNION vs UNION ALL?

          UNION and UNION ALL are used for merging two tables which has similar structure.

the difference between UNION and UNION ALL is that the UNION removes the duplicates while merging tables but UNION ALL do not and retain all duplicates.

UNION is slower in performance  as it has additional task of removing duplicates which causes more time in  execution.

What is the difference between query and subquery?

A Query is a code written to fetch information from the database where as subquery is a query within another query.  Basically subquery is an inner query. SubQuery is designed to executed first. The result of subquery is passed on to the main query which then process using the subquery output.

What is a stored procedure?

Stored Procedure is a function having several SQL statements. Stored procedure can be executed to process all SQL statements in it.

What is the difference between WHERE clause and HAVING clause?

Both WHERE and HAVING are used for filtering out records based conditions. WHERE clause can only be applied for static non-aggregated column whereas HAVING for aggregated columns.
SELECT * FROM Cricketbat WHERE batlenght > 2 feet
SELECT COUNT(Cricketbat)
FROM Bat
GROUP BY Team
HAVING COUNT(Cricketbat) > 2 feet;

What is DELETE and TRUNCATE commands?

DELETE command is used to remove selected rows from a table. A WHERE clause can be used to specify the rows to be deleted

 

What is difference between select and select distinct

Top 5 Salesforce Interview questions

Top 5 Salesforce Interview questions

1. What is object in salesforce?

Objects are database tables used to store specific data. There are two types of objects. 1. Standard objects 2. Custom objects.

2. What are different types of relationship in salesforce?

There are two different types of relationship in salesforce.
1. Master detail relationship
2. Lookup relationship

3. What is a trigger in salesforce ?

A Trigger is a code that is executed before or after a record is inserted or updated.

4. How many reports can be added to salesforce dashboard ?

20

5. What is validation rule ?

While creating or modifying a record, based on certain conditions validation rule helps to display error message.

6. What are escalation rules in sales force?

Escalation rules can be created on sales force case object and based on priority escalation emails can be sent.

Top 20 sql interview questions and answers

Top 20 sql interview questions and answers

What is DDL, DML, DCL, TCL in SQL ?

DDL refers to Data Definition language. DDL is used to create/modify the structure of Database. e.g. CREATE, ALTER, DROP statement..

DML refers to Data Manipulation language. DML is used to retrieve, modify, delete, insert and update data in database. e.g. SELECT, UPDATE, INSERT statement

DCL refers to Data Control Language. DCL is used to create roles, permissions, and control access to database. e.g. GRANT, REVOKE statement.
TCL refers to Transactional Control Language. TCL is used to manage different transactions occurring within a database. e.g COMMIT, ROLLBACK statement

What is the difference between UNION vs UNION ALL?

          UNION and UNION ALL are used for merging two tables which has similar structure.

the difference between UNION and UNION ALL is that the UNION removes the duplicates while merging tables but UNION ALL do not and retain all duplicates.

UNION is slower in performance  as it has additional task of removing duplicates which causes more time in  execution.

What is the difference between query and subquery?

A Query is a code written to fetch information from the database where as subquery is a query within another query.  Basically subquery is an inner query. SubQuery is designed to executed first. The result of subquery is passed on to the main query which then process using the subquery output.

What is a stored procedure?

Stored Procedure is a function having several SQL statements. Stored procedure can be executed to process all SQL statements in it.

What is the difference between WHERE clause and HAVING clause?

Both WHERE and HAVING are used for filtering out records based conditions. WHERE clause can only be applied for static non-aggregated column whereas HAVING for aggregated columns.
SELECT * FROM Cricketbat WHERE batlenght > 2 feet
SELECT COUNT(Cricketbat)
FROM Bat
GROUP BY Team
HAVING COUNT(Cricketbat) > 2 feet;

What is DELETE and TRUNCATE commands?

DELETE command is used to remove selected rows from a table. A WHERE clause can be used to specify the rows to be deleted

What is TRUNCATE commands?

TRUNCATE is used for removing all rows from the table. Truncate operation cannot be reversed but but delete can be reversed/rollback.

What is Log Caches of SQL Server?

Log cache is a memory pool allocated to read/write the log pages. A group of cache pages are available in each log cache.

What is Inner join?

Inner join is used for returning rows when there is at least one match of rows between the tables used in the inner join query.

What is Full Join?

Full join is used for returning all the rows from the left side table and all the rows from the right side table.

What is Right Join?

Right join return all rows of Right hand side table and matched rows in left hand table. Non matched rows will show null values for left had side.
Simply, it returns all the rows from the right hand side table even though there are no matches in the left hand side table.

What is Left Join?

Left join return all rows of Left hand side table and matched rows in right hand table. Non matched rows will show null values for right hand side.

What are the SQL MAX() and MIN() Functions?

The MAX() function returns the largest value in the selected column.

The MIN() function returns the smallest value in the selected column.

SELECT MAX(Weight) AS LargestWeight

FROM CricketBats;

SELECT MIN(Weight) AS SmallestWeight

FROM CricketBats;

What are the SQL AVG(), COUNT() and SUM() Functions

The SQL AVG() function returns the average value in a numeric values column.

e.g.

SELECT AVG(Weight)

FROM Cricketbats;

The SQL COUNT() function returns the number of rows count that matches with a specified criteria.

SELECT COUNT(ProductID)

FROM Cricketbats;

The SQL SUM() function returns the total sum of a numeric values in a specific column.

e.g

SELECT SUM(Weight)

FROM Cricketbats;

What is SQL UPDATE Statement?

The SQL UPDATE statement is used for modifying the existing records in the selected table.

e.g as below:

UPDATE Users

SET UserContactName=’First name Last name’, City=’City1′

WHERE UserID=1;

What is SQL ORDER BY Keyword?

The purpose of ORDER BY keyword is to sort the results in ascending or descending order.

example for ascending order

SELECT * FROM Users

ORDER BY State ASC;

or

SELECT * FROM Users

ORDER BY State;

example for descending order

SELECT * FROM Users

ORDER BY State DESC;

What is a NULL Value in SQL?

NULL values can not be compared using operators =, <, or <> so we will need to use the “IS NULL” or “IS NOT NULL” operators instead.

SELECT UserName, UserContactName, Address FROM Persons

WHERE Address IS NULL;

SELECT UserName, UserContactName, Address FROM Persons

WHERE Address IS NOT NULL;

What is SQL INSERT INTO Statement?

The INSERT INTO statement is used for inserting new records to a table.

e.g.

INSERT INTO Users (UserName, UserContactName, UserAddress, City, ZipCode, State, Country)

VALUES (‘Test1′,’Firstname last name’,’address 1, 2,3 ‘,’City namer’,’99999′,’Statename’,’Countryname’);

What is SQL INSERT INTO SELECT Statement?

The SQL INSERT INTO SELECT statement is used for copying data from one table and then to insert to someother table.

e.g. as below:

INSERT INTO Users (UserName, UserCity, Country)

SELECT VendorName, VendorCity, Country FROM Vendors;

What is the SQL DELETE Statement?

The SQL DELETE statement is used for deleting the existing records in the selected table.

e.g as below:

DELETE FROM Users

WHERE UserName=’Firstname1 Lastname1′;

What is AND, OR and NOT Operators in SQL?

AND, OR, and NOT operators are combined with the WHERE clause to get the desired filtered results.

e.g

SELECT * FROM Users

WHERE State =’illinois’ AND City=’Chicago’;

SELECT * FROM Users

WHERE City=’Chicago’ OR City=’New York’;

SELECT * FROM Users

WHERE NOT State =’Chicago’;

What is SQL WHERE Clause?

SQL WHERE clause is used to filter records and its added as below

e.g.

SELECT States FROM Users where Usersgroup = ‘1’;

What is the SQL SELECT TOP Clause ?

The SELECT TOP clause is used to specify the number of records to return.

e.g as below:

SELECT TOP 100 * FROM Users;