26A. Java Basics for Selenium - Java Database Connectivity - Basic SQL Knowledge

In this video we will see some basic SQL knowledge required in Java Database Connectivity.
Basic SQL Knowledge for Database Testing:
You need to have minimum SQL knowledge to work with databases. So in this section I cover those sql statements.
First on the phpMyAdmin page click on the home button on the top left hand side.
Click on Databases link and enter the name of the database as “jdbctesting” and click on “Create” button.
Now the database should be created and you should see it in the left pane.
Basic SQL Queries:
We will be creating a table “employees” with no. of 6 columns and they are
1. id (auto increment)
2. firstname (for capturing employee first name)
3. lastname (for capturing employee last name)
4. age (for capturing the age of the employee)
5. company (for capturing the company name of the employee)
6. salary (for capturing the salary of employee)

Creating Tables and Data:
Now click on the SQL link.
In this page you can practice SQL queries.
Creating Table:
CREATE TABLE `jdbctesting`.`employees` (
`id` INT NOT NULL primary key AUTO_INCREMENT ,
`firstname` VARCHAR(50) NOT NULL ,
`lastname` VARCHAR(50) NOT NULL ,
`age` INT NOT NULL ,
`company` VARCHAR(50) NOT NULL ,
`salary` INT NOT NULL
) ENGINE = InnoDB;
Note: In phpMyAdmin, when executing the above query use backquotes(“ ` “) instead of single quotes (“ ‘ “ ). Otherwise the above query won’t work.
Inserting Data into Table:
Query 1:
VALUES('james', 'smith', 35, 'IBM', 1250000)
Query 2:
VALUES('michael', 'smith', 35, 'HONDA', 1800000)
Retrieving Data from Table:
If you want to retrieve all the data use select query.
SELECT * FROM employees
If you want to retrieve specific rows use select query with where condition.
SELECT * FROM employees WHERE id = 1
Updating Data in the Table:
UPDATE employees SET firstname = 'Robert' WHERE id = 1
Make sure that you use the where condition otherwise it will update the entire data.
Deleting Data from Table:
DELETE FROM employees WHERE id = 1
Make sure that you use the where condition otherwise it will delete the entire table.
The most important queries that you use are SELECT and UPDATE.