bigint = Range from -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)
int = Range from -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)
smallint = Range from -2^15 (-32,768) to 2^15-1 (32,767)
tinyint = Range from 0 to 255
bit = 0 and 1
decimal = Range from –10^38 +1 to 10^38 -1
numeric = Range from -10^38 +1 to 10^38 -1
money = Range from -922,337,203,685,477.5808 to +922,337,203,685,477.5807
small money = Range from -214,748.3648 to +214,748.3647
Approximate Numeric SQL Data Types:
float = Range from -1.79E + 308 to 1.79E + 308
real = Range from -3.40E + 38 to 3.40E + 38
Date and Time SQL Data Types:
datetime = From Jan 1, 1753 to Dec 31, 9999
smalldatetime = From Jan 1, 1900 to Jun 6, 2079
date = To store a date like March 27, 1986
time = To store a time of day like 12:00 A.M.
Character Strings SQL Data Types:
char = CHAR is fixed lengt. Maximum length of 8,000 characters
varchar = VARCHAR is variable length. Maximum of 8,000 characters
varchar(max) = Maximum length of 231 characters
char vs varchar
CHAR takes up 1 byte per character. So, a CHAR(100) field (or variable) takes up 100 bytes on disk, regardless of the string it holds. VARCHAR is a variable length string data type, so it holds only the characters you assign to it. VARCHAR takes up 1 byte per character, + 2 bytes to hold length information.
Unicode Character Strings SQL Data Types:
nchar = Maximum length of 4,000 characters
nvarchar = Maximum length of 4,000 characters
nvarchar(max) = Maximum length of 231 characters
ntext = Maximum length of 1,073,741,823 characters
Binary SQL Data Types:
binary = Maximum length of 8,000 bytes
varbinary = Maximum length of 8,000 bytes
varbinary(max) = Maximum length of 231 bytes
image = Maximum length of 2,147,483,647 bytes
SQL Operators:
There are are three types of Operators.
Arithmetic Operators:
Comparison Operators
Logical Operators
Arithmetic operators
OperatorDescription
+Add values of operands
–Subtract values of operands
*Multiply operand’s values
/Divide values of operands
%Modulus operation on operands
Comparison operators
In the table below, if condition gets satisfied then “True” Boolean value is returned.
OperatorDescription
=Determine if the values of operands are equal.
!=Check if the values of operands are not equal.
>Determine if the left operand is more than the right operand.
<Check if the right operand is more than the left operand.
>=Determine if the left operand is more than or equal to the right operand.
<=Check if the right operand is more than or equal to the left operand.
!>Determine if the right operand is not more than the left operand.
!<Check if the left operand is not more than the right operand.
Logical Operators
OperatorDescription
OR - Returns true if either operand is true. Else it returns false if both the operands are false.
AND - Returns true if both operands are true. Else it returns false if either or both the operands are false
NOT - Returns true if condition is false and returns false if the condition is true.
SQL Constraints:
Constraints are the rules enforced on data columns on table.
These are used to limit the type of data that can go into a table.
This ensures the accuracy and reliability of the data in the database.
Constraints could be column level or table level.
Column level constraints are applied only to one column,
whereas table level constraints are applied to the whole table.
Following are commonly used constraints available in SQL:
1. NOT NULL Constraint: Ensures that a column cannot have NULL value.
Example:
CREATE TABLE recipes (
recipe_id INT NOT NULL,
recipe_name VARCHAR(30) NOT NULL,
PRIMARY KEY (recipe_id),
UNIQUE (recipe_name)
);
2. DEFAULT Constraint: Provides a default value for a column when none is specified.
3. UNIQUE Constraint: Ensures that all values in a column are different.
4. PRIMARY Key: Uniquely identified each rows/records in a database table.
UNIQUE KEY vs PRIMARY KEY
The UNIQUE constraint ensures that all values in a column are different.
Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness for a column or set of columns.
A PRIMARY KEY constraint automatically has a UNIQUE constraint.
However, you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.
The primary key column cannot have null values while the Unique Key column can have one null value.
5. FOREIGN Key: Uniquely identified a rows/records in any another database table.
A FOREIGN KEY is a field (or collection of fields) in one table, that refers to the PRIMARY KEY in another table. The table with the foreign key is called the child table, and the table with the primary key is called the referenced or parent table.
Foreign key is used to maintain relationship between two tables.
Foreign Key in a table doesn't have to be unique in said table. BUT, as it is the Primary Key of another table, it must be unique in this table.
6. CHECK Constraint: The CHECK constraint ensures that all values in a column satisfy certain conditions.
SQL constraints are used to specify rules for the data in a table. Constraints are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the table. If there is any violation between the constraint and the data action, the action is aborted.
Syntax: ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK (column_name condition);
7. INDEX: Use to create and retrieve data from the database very quickly.
A SQL index is used to retrieve data from a database very fast. Indexing a table or view is, without a doubt, one of the best ways to improve the performance of queries and applications.
Syntax:CREATE INDEX <indexName>
ON <TableName>(LastName);
Please refer below YouTube video for more detail explanation:
In This section we are going to cover below topics:
SQL For Testers
What is Database?
What is DBMS?
What is RDBMS?
What is SQL?
SQL Command Types
Most Important SQL Commands
Why SQL needed for Testers
The ability to recognize the different types of databases, The ability to connect to the database using different SQL connection clients, Understanding of the relationship between database tables, keys, and indices. Ability to write a simple select or SQL statement along with more complex join queries.
As a software tester, you are required to perform database testing that requires the knowledge of different SQL and database concepts. In addition, you are required to write SQL queries to retrieve, update and insert data in the databases.
We will start with the database fundamentals. At the same time, we will move to the SQL concepts. Finally, we’ll check some of the widely used SQL commands
What is a Database?
A database is an organized and systematic collection of data that is stored and accessed in/from a computer system. A database is usually controlled by a database management system (DBMS).
For example, a company database may include tables for products, employees, and financial records. Each of these tables would have different fields that are relevant to the information stored in the table.
What is DBMS?
DBMS (Database Management System) is a software system that is designed to maintain and access the database. It allows the user to access, create, delete, and update data in a database.
DBMS defines the rules for manipulation and validation of this data. We use a database when there is a huge amount of data, the security of the data is important, or when multiple users have to have access to the data concurrently.
Some DBMS examples include MySQL, PostgreSQL, Microsoft Access, SQL Server, FileMaker, Oracle, RDBMS, dBASE, Clipper, and FoxPro
What is RDBMS?
RDBMS (Relational Database Management System) is an advanced version of the basic DBMS.
A relational database is a database that allows the user to store related data in the form of multiple tables, which are linked by establishing a relationship between the different tables, hence providing an efficient way to access the database.
Top Relational Databases Software
MS SQL
Oracle Database
MySQL
Db2
PostgreSQL
A RDBMS database uses tables for storing the data. A table is nothing but a collection of data that is related to one another.
Rows
A Row represents a collection of fields that end up making a record in the database.
Column
In a database, a column represents those values that are of the same type. A column is also called an attribute.
What is SQL?
SQL stands for Structured Query Language. It is a programming language that is used to request information from a database. SQL can be used to manage and share data in a relational database management system. Moreover, users can perform actions like insertion, deletion, selection, etc on the database.
SQL keywords are not case sensitive.
SQL commands are segregated into following types:
DDL – Data Definition Language –
SQL commands come under DDL are as follows: CREATE, ALTER, DROP, TRUNCATE
DML – Data Manipulation Language
SQL Commands come under DML are as follows: INSERT, UPDATE, DELETE
DQL – Data Query Language
SQL Commands come under DQL are as follows: SELECT
DCL – Data Control Language
SQL Commands come under DCL are as follows: GRANT, REVOKE, DENY
TCL – Transaction Control Language
SQL Commands come under TCL are as follows: COMMIT, ROLEBACK, SAVE
Some frequently used keywords/commands:
SELECT – It extracts data from a DataBase
INSERT INTO – It inserts new data into a DataBase
CREATE – It creates a new DataBase/Table/Index
UPDATE – It updates data in a DataBase
ALTER – It modifies a DataBase/Table
The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.
The ALTER TABLE statement is also used to add and drop various constraints on an existing table.
Syntax: ALTER TABLE table_name
ADD column_name datatype;
TRUNCATE - Truncate can be used to delete the entire data of the table. Table structure remain same.
Syntax:TRUNCATE TABLE table_name
DELETE – Delete statement can be used for deleting the specific data.
