Why do we learn SQL? — To get data from DB manually, we can apply this knowledge to backend manual test.

We still didn’t connect our intelliJ with database with help of Java, we didn’t make database as part of our Framework yet.

So far we just connected our intelliJ with our amazon server via IP address, port#, credentials(user, password).

Now, we want to implement that connection of Database into our Framework. How that’s gonna happen?

Connection via IP address, port# and credentials are still needed, but it is still not enough to connect database to our framework. We need library, called JDBC. Do we need to add JDBC dependences into our Maven? — No. It comes with Java Standard Edition and gives us ability to connect database to our Framework.

Is there anyway to handle all types of drivers for DB like Bony Garcia? — No. But JDBC provides DriverManager (Class) for establishing connection with various DBs with the help of related Driver.

• To connect our Driver with any browser we have this flow:

JAVA → Selenium → WebDriver(Bony Garcia) → Chrome Browser

To connect Oracle DB with Driver we have this flow:

JAVA → JDBC → Oracle Driver → Oracle DB

If you want to use Postgres, you need to add Postgress Driver. If you want to use MySQL, you need to add MySQL Driver:

JAVA → JDBC → Postgress Driver → Postgress DB

JAVA → JDBC → MySQL Driver → mySQL DB

• Which dependency we need to add? — We need to add Oracle Driver for Oracle DB.

JDBC: is an industry standard for DB independent connectivity between Java and wide range of DBs.

JDBC comes with 2 packages:

• java.sql* — basic DB Communication:

→ interfaces: Connection — Statement — ResultSet — ResultSetMetaData — DBMetaData..

Connection to connect Java with DB

Statement to write and execute queries

ResultSet for table itself without table information

ResultSetMetaData (Table data), for table information

→ classes: DriverManager will help to connect with DB with its getConnection() method.

• javax.sql* — advanced DB communication

STEPS TO CONNECT DB TO OUR FRAMEWORK:

1. At Java (green folder) level create new package:

2. We need to add one driver. What type of DB we’ve been using so far? — Oracle. So we need to add Oracle dependency into my Maven pom.xml.

As we mentioned before we need specific Driver to connect to specific DB. In our UI automation framework we used Bony Garcia to handle all types of drivers in one place in order to connect from our framework. However, for backend automation framework, there is no such manager like Bony Garcia, therefore we have to add each driver one by one.

3. Connection Stream: In order to test DB using JAVA, first we connect DB via Connecting String, which includes URL — Username — Password:

We will create String dbUrl, but where do we get it?

4. Now we can connect with the help of Connection interface and DriverClass, we use getConnection() method to establish this connection.

5. After we successfully created connection, next step is to create the Statement Object from Connection for query excecution by using createStatement() method coming from Statement interface.

6. Once we have Statement we can run our query and get result in ResultSet format (it will keep data from execution).

7. After we done we always have to close our DB connection:

MOVE ROWS UP/DOWN. Getting Data from DB— ResultSet

8. What does ResultSet do? It keeps only information from Table Data. Hold only data itself and doesn’t have any information about # of columns, column names or count.

In order to access data in ResultSet Object, we use cursor navigation methods. There are 7 methods available for navigation purposes:

==> next(); — move to the next row and return Boolean value based on row availability/existence

By default the pointer shows “before -first -row”, but we need next row which is actual 1st row, so we will use next() method to achieve this.

==> last(); — move to the last row from anywhere.

Note: as of now our Statement is setup for FORWARD only navigation, that means our cursor is not flexible. To be able to move it to the last row, we need to make small adjustments in our Statement constructor by passing two arguments: ResultSet.TYPE_SCROLL_INSENSITIVE (for making cursor flexible) & ResultSet.CONCUR_READ_ONLY (permit to read only).

==> absolute(int); — move to specified row, returns Boolean value based on row availability/existence. To make sure you are printing needed data, you can check table manually and print that specific row data and row number using methods above:

==> previous(); — move to previous row, returns Boolean value based on row availability.

==> beforeFirst(); — move to the top →before-first-row position

==> afterLast(); — move to after last location from anywhere

==> first(); — move to the first row position from anywhere

MOVE COLUMNS LEFT →RIGHT.

9. ResultSetMetaData. JDBC MetaData programming supports:

• DatabaseMeta (information about DB itself)
• ResultSetMetaData (information about the table: column names, # of columns etc)

We will focus on ResultSetMetaData. It is responsible for the top of the table, which is the table column information. RSMD is used to get column info.

How can we store these data?

We need to get each row into Map using columnNames as Key. Each Cell value will be the value of each related Key.

When do we use Set Operators and How those work?

For Set Operators to work we need:

• TWO Independent Queries*
• Same [# columns] in SELECT(clause)
• Same Data Type in the Same Order

*Note: Independent Queries must be different, both should have the same [# of columns] and the same data types in each corresponding column.

1. UNION(operator): the main job is to combine result of two or more SELECTs(clauses) into a single set. It basically merges two tables vertically. It removes all duplicate rows and sort them based on ascending order.

2. UNION ALL: do not remove duplicates and do not sort results.

3. MINUS: will only return data from 1st query that is not present/common in 2nd query, (it will subtract any duplicate values and return only unique ones for the 1st table). The order of tables does matter. So the focus is to extract unique data from the table you are looking to get info.

4. INTERSECT: will return data that are present/common in both query results. It will sort and remove duplicates (will return only matching data).

• What is the Inner Join?

INNER JOIN(clause) is a command that has only one job — to get matching data from two+ tables. This clause is placed between let’s say table1 & table2+, and it will check if there is any connection between them, it will go over all rows from all tables and as a result will return you only rows with matching data.

1. After SELECT(clause) we need to define TableName.ColumnName
2. After FROM(clause) we define table1 INNER JOIN(clause) table2. We want to get data from both tables.
3. After ON(clause) set condition to be matched from both tables in order to extract our data from: Table1.columnName = table2.columnName.

• LEFT OUTER JOIN

This clause will return matching part & unique for Left Table, therefore keep in mind that table order is important:

• LEFT OUTER JOIN with WHERE(clause) will look for unique data coming only from Left Table without looking for any matching data with Right Table. We perform the same Left Join, then with help of WHERE(clause) exclude data we don’t want from Right Table.

• RIGHT OUTER JOIN.

If we want to get info for matching portion, & unique data from Address table, and if we want to use Address as a Right side, we’d use RIGHT OUTER JOIN, if we want to use Address as a Left side, we’d use LEFT OUTER JOIN. Result would be the same in both cases.

• SELF-JOIN(clause) joins table to itself. Purpose - we use this clause when we want to combine one row with anther row within the same table. How to use — we must use table alias for referencing purposes, differing one row-to-be-table from another within the same table.

1. Find 3ed max salary from employees’ table (exclude any duplicates).

Note: If you don’t know where to start from, try to divide your task into small steps:

• First, we need to sort salary in descending order and get rid of all duplicates:

• Second part includes 2 steps to implement: one is to pass inside of the parentheses the query defined in step 1 after FROM(clause), two is to cut the row from line 3:

• Now we can easily get 3ed minimum salary by just simply adding MIN(SALARY) instead of (*) at SELECT level:

• Finally we can get all information of the employee who is getting 3ed highest salary:

2. Find 3ed minimum salary from employees table (do not include duplicates): Hey, guys, you can try now do this in 3 steps and remember that 3ed step has three actions like to distinct, sort and cut the row lines:)

DDL & DML (Interview Related Question — personal preparation)

What does DDL stand for? — Data Definition Language (its about Table manipulation).

Why we need it? — it helps us to define DATA Structures with using special keywords:

• CREATE
• DROP
• TRUNCATE
• ALTER

What does DML stand for? Data Manipulation Language (its about Data manipulation).

Why we need it? — it helps us to define Data itself by using special keywords:

• SELECT
• INSERT
• UPDATE
• DELETE

Note: as a QA Tester, you do not have any responsibilities in creating this data, just know it how it works and that’s by itself more than enough.

The Plan is to create a table, add couple of data, delete and update that data. So how it works? What’s the syntax and the flow?

1. First, type CREATE TABLE statement/clause to create a new SQL table:

2. Name your new table:

3. List the column name, its data type, and column constraint. You can have multiple columns in the table, each column has to be separated by a comma(,). The column constraint applies certain rules for the column e.g., NOT NULL.

Column Constraints rules:

• NOT NULL — the value of the column can not be NULL.
• UNIQUE — the value of the column must be unique across the whole table.
• PRIMARY KEY — combination of the both NOT NULL and UNIQUE(can not be duplicate) constraints.
• REFERENCES — Other table (PKColumn) — its a foreign key being used as a reference in other table as a result of being primary key in the referencing table.

NOTE: YOU CAN NOT HAVE FOREIGN KEY WITHOUT HAVING IT AS PRIMARY KEY FIRST IN RELATED TABLE. FOREIGN KEY IS BASICALLY THE PRIMARY KEY BUT USED IN OTHER TABLE AS REFERENCE.

• CHECK — adds extra layer of restriction, ensures all data entries satisfy precise conditions set upward. It will throw error message in case conditions are not met.

4. Insert/add new rows (one at a time) into your new table by INSERT — INTO tableName(column1, column2,..) — VALUES(value1, value2,..) clause

5. COMMIT(clause) after all insertion is done, otherwise it will not be entered in database: commit; or commit work;

6. UPDATE(clause) will help to save any changes in your table. Lets increase salary for all employees by 10K each:

7. WHERE(clasue) is used when you want to specify certain data you want to change. Lets change/UPDATE salary only for Testers by +25K for each Tester:

8. DELETE(clause) will remove data you specify FROM(clause) your table WHERE(clause) you need to specify certain data. You always want to DELETE data that MAKES SENSE. If you have multiple Mikes and you want to delete one of them, employeeID is your WHERE condition to be removed.

9. ALTER TABLE(clause) is coming from DDL(table related manipulations). You can do following manipulations with it:

• RENAME TO: will rename your table into newTableName. Lets rename our “scrumTeam” to “bestTeam”:

• ADD COLUMN: will add column to the table. Lets add column “Gender” to our just renamed table “bestTeam”:

• RENAME COLUMN: will rename column name. Lets rename “Salary” column to “Annual_Salary”:

• DROP COLUMN: will drop the column from the table. Lets drop “Gender” column from the table:

10. TRUNCATE(clause) will remove all data from table BUT NOT the table itself: