What is Database Testing?
Why DB Testing is Necessary?
SQL Overview
SQL CREATE Database
SQL CREATE Table
SQL SELECT Query
SQL WHERE Clause
SQL SELECT DISTINCT
SQL GROUP BY
SQL HAVING
SQL ORDER BY Keyword
SQL LIKE Operator
SQL IN Operator
SQL BETWEEN Operator
SQL AND & OR Operators
SQL Aggregate Functions
SQL INSERT
SQL UPDATE
SQL DELETE
SQL TRUNCATE
SQL ALTER
SQL DROP
SQL JOINS
SQL SUB QUERIES

DATA:-

Data is anything that can be represented as text, numbers or multimedia(image,sound,video etc)

Ex: ‘Technology’, 243589, ‘2024-08-05’

DATA SET:-

Dataset is a structured colletion of data ed colletion of data tha contains information of an entity

Ex:-

cust_id	cust_name	Age	email	phone
1	Vishwa	29	jdsv@gmail.com	+91

DATABASE:-

Database is a system that allow users to store,organize and analyze data, it is an organized collection of data stored in multiple related datasets,

EX:-

RDBMS:- (Relational Data base Management System)

A relational Database is data base that stores that in a table form,containing columns and rows.

RDBMS is a system use to store, manage,query, and retrieve data stored in a relational databases.

The RDBMS provides an interface between users&applications and the database.

LIST OF RDBMS SYSTEMS:-

Oracle

MySQL

Teradata

SQL Server

PostgreSQL

Vertica

IBM DB2

Snowflake(Cloud Database)

WHAT IS TABLE :-

Table is a database object that is a collection of related data entries, where data is organized in rows and columns

Rows are also knw as records

Columns are also known as Fields

Example: Customer table

Metadata : Data about data it gives the info that what data is stored in a table.

HOW TO ACCES THE DATA FROM DATABASE :

SQL -Structured Query Language

v We can access and manipulate the data from relational database by using SQL

v Sql is a programming laguage used to communicate with these RDBMS systems like Oracle,Teradata,MySQL,SQL server etc.

v SQL is an ANSI/ISO standard. All database support ANSI SQL so the syntaxes,keywords and funcions or almost similar in all databases.

v We can build SQL queries and procedures too operate with the data present in Database.

v Queries can be written by using Operators,keywords,Expressions,Fuctions..

DATA TYPES :-

Data types specify the type of the data we storing or querying

There are many data types to store

¨ Text(Characters,strings)

¨ Numeric values

¨ Date and time values

¨ Boolean (True or false)

¨ Images,audio,video data

SQL

What is SQL?

SQL stands for Structured Query Language
SQL lets you access and manipulate databases
SQL became a standard of the American National Standards Institute (ANSI) in 1986, and of the International Organization for Standardization (ISO) in 1987

What Can SQL do?

SQL can execute queries against a database
SQL can retrieve data from a database
SQL can insert records in a database
SQL can update records in a database
SQL can delete records from a database
SQL can create new databases
SQL can create new tables in a database
SQL can create stored procedures in a database
SQL can create views in a database
SQL can set permissions on tables, procedures, and views

Database Tables

A database most often contains one or more tables. Each table is identified by a name (e.g. "Customers" or "Orders"). Tables contain records (rows) with data.

SQL Statements

Most of the actions you need to perform on a database are done with SQL statements.

Some database systems require a semicolon at the end of each SQL statement.

Semicolon is the standard way to separate each SQL statement in database systems that allow more than one SQL statement to be executed in the same call to the server.

Some of The Most Important SQL Commands

SELECT - extracts data from a database
UPDATE - updates data in a database
DELETE - deletes data from a database
INSERT INTO - inserts new data into a database
CREATE DATABASE - creates a new database
ALTER DATABASE - modifies a database
CREATE TABLE - creates a new table
ALTER TABLE - modifies a table
DROP TABLE - deletes a table
CREATE INDEX - creates an index (search key)
DROP INDEX - deletes an index

The SQL SELECT Statement

The SELECT statement is used to select data from a database.

The data returned is stored in a result table, called the result-set.

SELECT Syntax

SELECT column1, column2, ...FROM table_name;

Here, column1, column2, ... are the field names of the table you want to select data from. If you want to select all the fields available in the table, use the following syntax:

SELECT * FROM table_name;

The SQL SELECT DISTINCT Statement

The SELECT DISTINCT statement is used to return only distinct (different) values.

Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values.

SELECT DISTINCT Syntax

SELECT DISTINCT column1, column2, ...FROM table_name;

The SQL WHERE Clause

The WHERE clause is used to filter records.

The WHERE clause is used to extract only those records that fulfill a specified condition.

WHERE Syntax

SELECT column1, column2, ...FROM table_name WHERE condition;

Note: The WHERE clause is not only used in SELECT statement, it is also used in UPDATE, DELETE statement, etc.!

Operators in The WHERE Clause

The following operators can be used in the WHERE clause:

Operator	Description
=	Equal	Try it
>	Greater than	Try it
<	Less than	Try it
>=	Greater than or equal	Try it
<=	Less than or equal	Try it
<>	Not equal. Note: In some versions of SQL this operator may be written as !=	Try it
BETWEEN	Between a certain range	Try it
LIKE	Search for a pattern	Try it
IN	To specify multiple possible values for a column

The SQL AND, OR and NOT Operators

The WHERE clause can be combined with AND, OR, and NOT operators.

The AND and OR operators are used to filter records based on more than one condition:

The AND operator displays a record if all the conditions separated by AND are TRUE.
The OR operator displays a record if any of the conditions separated by OR is TRUE.

The NOT operator displays a record if the condition(s) is NOT TRUE.

AND Syntax

SELECT column1, column2, ...FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;

OR Syntax

SELECT column1, column2, ...FROM table_name
WHERE condition1 OR condition2 OR condition3 ...;

NOT Syntax

SELECT column1, column2, ...FROM table_name WHERE NOT condition;

The SQL ORDER BY Keyword

The ORDER BY keyword is used to sort the result-set in ascending or descending order.

The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.

ORDER BY Syntax

SELECT column1, column2, ...FROM table_name ORDER BY column1, column2, ... ASC|DESC;

The SQL INSERT INTO Statement

The INSERT INTO statement is used to insert new records in a table.

INSERT INTO Syntax

It is possible to write the INSERT INTO statement in two ways.

The first way specifies both the column names and the values to be inserted:

INSERT INTO table_name VALUES (value1, value2, value3, ...);

What is a NULL Value?

A field with a NULL value is a field with no value.

If a field in a table is optional, it is possible to insert a new record or update a record without adding a value to this field. Then, the field will be saved with a NULL value.

Note: A NULL value is different from a zero value or a field that contains spaces. A field with a NULL value is one that has been left blank during record creation!

How to Test for NULL Values?

It is not possible to test for NULL values with comparison operators, such as =, <, or <>.

We will have to use the IS NULL and IS NOT NULL operators instead.

IS NULL Syntax

SELECT column_names FROM table_name WHERE column_name IS NULL;

IS NOT NULL Syntax

SELECT column_names FROM table_name WHERE column_name IS NOT NULL;

The SQL UPDATE Statement

The UPDATE statement is used to modify the existing records in a table.

UPDATE Syntax

UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;

Note: Be careful when updating records in a table! Notice the WHERE clause in the UPDATE statement. The WHERE clause specifies which record(s) that should be updated. If you omit the WHERE clause, all records in the table will be updated!

The SQL DELETE Statement

The DELETE statement is used to delete existing records in a table.

DELETE Syntax

DELETE FROM table_name WHERE condition;

Note: Be careful when deleting records in a table! Notice the WHERE clause in the DELETE statement. The WHERE clause specifies which record(s) should be deleted. If you omit the WHERE clause, all records in the table will be deleted!

SQL Aliases

SQL aliases are used to give a table, or a column in a table, a temporary name.

Aliases are often used to make column names more readable.

An alias only exists for the duration of the query.

Alias Column Syntax

SELECT column_name AS alias_name FROM table_name;

Alias Table Syntax

SELECT column_name(s) FROM table_name AS alias_name;

SQL JOIN

A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

Different Types of SQL JOINs

Here are the different types of the JOINs in SQL:

(INNER) JOIN:
LEFT (OUTER) JOIN:
RIGHT (OUTER) JOIN:
FULL (OUTER) JOIN:

SQL INNER JOIN Keyword

The INNER JOIN keyword selects records that have matching values in both tables.

INNER JOIN Syntax

SELECT column_name(s)FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;

SQL LEFT JOIN Keyword

The LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2).

The result is NULL from the right side, if there is no match.

LEFT JOIN Syntax

SELECT column_name(s)FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;

Note: In some databases LEFT JOIN is called LEFT OUTER JOIN.

SQL RIGHT JOIN Keyword

The RIGHT JOIN keyword returns all records from the right table (table2), and the matched records from the left table (table1). The result is NULL from the left side, when there is no match.

RIGHT JOIN Syntax

SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name;

Note: In some databases RIGHT JOIN is called RIGHT OUTER JOIN.

SQL FULL OUTER JOIN Keyword

The FULL OUTER JOIN keyword returns all records when there is a match in left (table1) or right (table2) table records.

Note: FULL OUTER JOIN can potentially return very large result-sets!

Tip: FULL OUTER JOIN and FULL JOIN are the same.

FULL OUTER JOIN Syntax

SELECT column_name(s) FROM table1 FULL OUTER JOIN table2 ON table1.column_name = table2.column_name WHERE condition;

SQL Self JOIN

A Self-JOIN is a regular join, but the table is joined with itself.

Self JOIN Syntax

SELECT column_name(s) FROM table1 T1, table1 T2 WHERE condition;

T1 and T2 are different table aliases for the same table.

What is a Stored Procedure?

A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again.

So if you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it.

You can also pass parameters to a stored procedure, so that the stored procedure can act based on the parameter value(s) that is passed.

Stored Procedure Syntax

CREATE PROCEDURE procedure_name

AS sql_statement
GO;

SQL Comments

Comments are used to explain sections of SQL statements, or to prevent execution of SQL statements.

Single Line Comments

Single line comments start with --.

Any text between -- and the end of the line will be ignored (will not be executed).

The following example uses a single-line comment as an explanation:

Example

--Select all:
SELECT * FROM Customers;

SQL For Database Testing

SQL