Install Mysql
Download link :https://dev.mysql.com/downloads/
Download MySQL Sample Database ( classicmodels)
Sample Database Download Link: https://www.mysqltutorial.org/mysql-sample-database.aspx

What is Database Testing?

Database Testing is a type of software testing that checks the schema, tables, triggers, etc. of
the Database under test.
The 3 types of Database Testing are

1. Structural Testing
2. Functional Testing
3. Non-functional Testing

Functional Database Testing

Functional Database Testing is a type of database testing that is used to validate the
functional requirements of a database from the end-user’s perspective. The main goal of
functional database testing is to test whether the transactions and operations performed by the
end-users which are related to the database works as expected or not.
Following are the basic conditions that need to be observed for database validations.
● Whether the field is mandatory while allowing NULL values on that field?
● Whether the length of each field is of sufficient size?
● Whether all similar fields have the same names across tables?
● Whether there are any computed fields present in the Database?

Non-functional testing

Non-functional testing in the context of database testing can be categorized into various
categories as required by the business requirements. These can be load testing, Stress Testing,
Security Testing, Usability Testing, and Compatibility Testing, and so on.

Stored Procedure Testing

What is stored procedure

● A stored procedure is a block of SQL statements.
● We can save stored procedures and can be reused multiple times.
● We can also pass parameters to a stored procedure.

Advantage

● Reduce network traffic
● Centralize business logic in the database
● Make database more secure

Checking data integrity and consistency

Following checks are important

• Whether the data is logically well organized?
  
• Whether the data stored in the tables is correct and as per the business requirements?
  
• Whether there are any unnecessary data present in the application under test?
  
• Whether the data has been stored as per as the requirement with respect to data which
  has been updated from the user interface?
  
• Whether the TRIM operations performed on the data before inserting data into the
  Database under test?
  
• Whether the transactions have been performed according to the business requirement
  specifications and whether the results are correct or not?
  
• Whether the data has been properly committed if the transaction has been successfully
  executed?
  
• Whether the data has been rolled backed successfully if the transaction has not been
  executed successfully by the end-user?
  
• Whether the data has been rolled backed if the transaction has not been executed
  successfully and multiple heterogeneous databases have been involved in the
  transaction in question?
  
• Whether all the transactions have been executed by using the required design
  procedures as specified by the system business requirements?

Login and User Security Example

The validations of the login and user security credentials need to take into consideration the
following things.

1. Whether the application prevents the user from proceeding further in the application in
   case of a
   ● invalid username but valid password
   ● valid username but invalid password.
   ● invalid username and invalid password.
2. Whether the user is allowed to perform only those specific operations which are
   specified by the business requirements?
3. Whether the data is secured from unauthorized access?
4. Whether there are different user roles created with different permissions?
5. Whether all the users have required levels of access on the specified Database as
   required by the business specifications?
6. Check that sensitive data like passwords, credit card numbers are encrypted and not
   stored as plain text in Database. It is a good practice to ensure all accounts should have
   passwords that are complex and not easily guessed

Here is some database testing checklist you might have used:

• Check if correct data is getting saved in database upon successful page submit
  
• Check values for columns which are not accepting null values
  
• Check for data integrity. Data should be stored in single or multiple tables based on
  design
  
• Index names should be given as per the standards e.g.
  
• IND_<Tablename><ColumnName>
  
• Tables should have primary key column
  
• Null values should not be allowed for Primary key column
  
• Table columns should have description information available (except for audit columns
  like created date, created by etc.)
  
• For every database add/update operation log should be added
  
• Required table indexes should be created
  
• Data should be rolled back in case of failed transactions
  
• Check if data is committed to database only when the operation is successfully
  completed
  
• Check if input data is not truncated while saving. Field length shown to user on page and
  in database schema should be same
  
• Check numeric fields with minimum, maximum, and float values
  
• Test stored procedures and triggers with sample input data
  
• Database name should be given as per the application type i.e. test, UAT, sandbox, live
  (though this is not a standard it is helpful for database maintenance)
  
• Database logical names should be given according to database name (again this is not
  standard but helpful for DB maintenance)
  
• Stored procedures should not be named with prefix “sp”
  
• Check values for table audit columns (like createddate, createdby, updatedate,
  updatedby, isdeleted, deleted date, deleted by etc.) are populated properly
  
• Check numeric fields with negative values (for both acceptance and non-acceptance)
  
• Check if radio button and dropdown list options are saved correctly in database
  
• Check if database fields are designed with correct data type and data length
  
• Check if all table constraints like Primary key, Foreign key etc. are implemented correctly
  
• Input field leading and trailing spaces should be truncated before committing data to
  database

practice

QUERY FOR ClassicModel

use classicmodels;

show tables;

select * from customers;

select count(*) as NumberOfColumns from information_schema.COLUMNS where TABLE_NAME = 'customers';

select COLUMN_NAME from information_schema.COLUMNS where TABLE_NAME='customers';

select COLUMN_NAME,DATA_TYPE from information_schema.COLUMNS where TABLE_NAME = 'customers';

select COLUMN_NAME,COLUMN_TYPE from information_schema.COLUMNS where TABLE_NAME = 'customers';

select COLUMN_NAME,IS_NULLABLE from information_schema.COLUMNS where TABLE_NAME = 'customers';

select COLUMN_NAME,COLUMN_KEY from information_schema.COLUMNS where TABLE_NAME = 'customers';

QUERY FOR Record Company - SQL IN 60 Min

create database record_company;
use record_company;
CREATE TABLE record(
   test_column INT
);

ALTER table record
ADD another_column varchar(255);

DROP TABLE record;

CREATE TABLE bands(
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  PRIMARY KEY(id) 
);

CREATE TABLE albums(
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  relaese_year INT,
  band_id INT NOT NULL,
  PRIMARY KEY(id),
  FOREIGN KEY(band_id) REFERENCES bands(id)
);

INSERT INTO bands(name) VALUES ('Radwan');

INSERT INTO bands(name) VALUES('Minhaj'),('Saif'),('Adnan');

SELECT * FROM  bands;

SELECT * FROM bands LIMIT 2;

SELECT name FROM bands;

SELECT id AS 'ID', name AS 'Band Name' FROM bands;

SELECT * FROM bands ORDER BY name DESC; 

INSERT INTO albums (name,relaese_year, band_id) 
VALUES ('The Number Of beasts',1985,1),
	   ('Power Slave',1984,1),
	   ('Nightmare',2018,2),
	   ('Infintiy',2022,3),
       ('Comming',NULL,4),
	   ('Infinity',2022,3);

SELECT * FROM albums;

SELECT name FROM albums;

SELECT DISTINCT name FROM albums;

UPDATE albums SET relaese_year = 2023 WHERE id=1;

SELECT * FROM albums WHERE relaese_year < 2000;

SELECT * FROM albums WHERE name LIKE '%er%' OR band_id=2;

SELECT * FROM albums WHERE relaese_year = 1984 AND band_id=1;

SELECT * FROM albums WHERE relaese_year  BETWEEN 2000 AND 2018;

SELECT *  FROM albums WHERE relaese_year IS NULL;

DELETE FROM albums WHERE id > 10;

SELECT * FROM albums;

SELECT * FROM bands JOIN albums ON bands.id = albums.band_id;

SELECT * FROM bands INNER JOIN albums ON bands.id = albums.band_id;

SELECT * FROM bands LEFT JOIN albums ON bands.id = albums.band_id;

SELECT * FROM albums RIGHT JOIN bands ON bands.id = albums.band_id;

SELECT AVG(relaese_year) FROM albums;

SELECT band_id, COUNT(band_id) FROM albums GROUP BY band_id;

SELECT b.name AS band_name, COUNT(a.id) AS num_albums
FROM bands AS b
LEFT JOIN albums AS a ON b.id =  a.band_id
GROUP BY b.id;

SELECT b.name AS band_name, COUNT(a.id) AS num_albums
FROM bands AS b
LEFT JOIN albums AS a ON b.id =  a.band_id
GROUP BY b.id
HAVING num_albums =1;