SQL Part 1: DataBase Concept

 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.

Syntax/Example:

Delete from  <table>;

Delete from  <table> where ID=3;

• DROP – It deletes a table/Index.

Syntax: DROP TABLE table_name

Please refer below YouTube videos in detail: