Way2know: Business Analysis-Chapter 11: SQL (Structured Query Language)

Chapter 11: SQL (Structured Query Language)

Sometimes business analysts may need view and analyze the business data. Hence basic understanding of the database and SQL is required for the business analyst.

SQL (structured query language) is a database query language. So let us understand what is database before moving on to SQL.

Database is a structured set of data held in a computer, especially one that is accessible in various ways.

Database management system is classed as below:

DBMS –Database management System. DBMS Stores data as files

RDBMS – Relational database management system. RDMBS stores data in tabular form.

We will discuss here on RDBMS concepts as it will be used to store the business transactions data.

Below are the important basic concepts related to RDBMS.

Metadata: It is a set of data that describes and gives information about other data. It is data that provides information about the other data.

Table: It is a collection of related data. It consists of rows and columns.

Row: A record in a table is called row. It is horizontal entry.

Column: A column is a vertical entity in a table that contains all information associated with a specific field in a table.

Primary Key: It is a key that uniquely identifies a record or row in a table.

Foreign Key: It is a key that uniquely identifies a record or row in another database table.

Normalization: It is a process of removing redundancy in database tables to ensure that data is stored efficiently in a database table.

Joins: The SQL Join is used to combine records from two or more tables in a database.

Below are the few important queries, business analysts may need to use; these kinds of queries will help to query database tables & view/update/delete/insert business data.

Let us consider an example to explain the concepts

Consider the below table named: Employee

Table 11.1 Employee
EmpID	EmpName	DeptID
1001	Shyam	111
1002	Ram	111
1003	Bhim	222
1004	John	222
1005	Jane	222

In the above table

EmpID is called Primary Key.

DeptID is foreign key in employee table, but it is primary key in Department table.

Select/View Query: Used to select and view data in a database table.

Syntax: SELECT * from table_name;

Example: Select * from employee

Above query will select all records from employee table.

Select * from employee where empid=’1001’

Above query will select a record from employee table which has employee id column equal to 1001.

Update Query: Used for updating records in a database table.

Syntax: UPDATE table_name SET column_name= ‘value’

Example: Update employee set DeptID=’CS12’

Above query will change DeptID column value to CS12 for all rows in employee table.

Update employee set deptid=’CS12’ where Empid=’1001’

Above query will change DeptID column value to CS12 for employee with EMpID column value equals to 1001

Delete Query: Used for deleting one or more rows in a database table.

Syntax: DELETE FROM table_name

Example: Delete from employee ;

Above query will delete all records from employee table.

Delete from employee where EmpID=’1001’

Above query will delete a record in employee table where EmpID column value equals to 1001

Insert Query: Used for inserting one or more rows into database table.

Syntax: INSERT INTO table_name (column1, column2, column3,…..) Values (value1, value2, value3,……)

Example: Insert into employee(EmpID, EmpName, DeptID) values(‘1006’,’Raju’,’HR’)

Join: Joining tables based on condition(s):

Suppose we have one more table: Department, with two columns department ID and department name.

Table 11.2 Department
DeptID	DeptName
111	Admin
222	Computers
333	Sports
444	Teaching
555	Training
666	Science

In the above Department table, DeptID is primary key.

Now Let us say, we want to list out employee name and department name then we need to use both employee and department tables. We need write query as below.

Select a.EmpName, b.DeptName from Employee a, Department b Where a.DeptID = b.DeptID

Above query will display the result as below.