Tag Archive Learn SQL

SQL tutorial | DECODE in ORACLE

SQL Basics tutorial on DECODE

In this SQL tutorial on DECODE, we are going to learn about Decode command in SQL (only applicable for Oracle). Every database system has some special and useful SQL commands. Oracle has an extremely powerful command known as DECODE. Please note that, using DECODE command is only possible in Oracle.

Implementing DECODE

How do you implement IF-THEN-ELSE in an SQL query in ORACLE? Let’s take an example to understand the problem first?

You have a table named “Employees”. The “Employees” table has the following fields:

  • Emp ID
  • Emp Name
  • Designation
  • Address
  • Contact No 1
  • Contact No 2
  • Basicsal

The company decided to pay the bonus as a multiple of base salary. This will be calculated as follows:

If Designation = “Project Manager” then

Bonus = 2.3 X Basic Salary

Else if Designation = “Program Manager” then

Bonus = 3.6 X Basic Salary

Else

Bonus = 1.2 X Basic Salary

End If

If you are going to use a programming language, you can use If-Else-Then statements to code but SQL queries don’t support these so How do you fetch the data using a single SQL query but still using the above conditions?

The solution is to use DECODE, here is the query?

SELECT EmpID, EmpName,

DECODE (Designation, ‘Project Manager’, 2.3 X Basic Salary, ‘Program Manager’, 3.6 X Basic Salary, 1.2 X Basic Salary)

FROM Employees;

Alternative to DECODE

Is there any other way to achieve the same result in ORACLE? Of course, yes, using CASE.

Using CASE:

SELECT EmpID, EmpName,

CASE Designation,

WHEN ‘Project Manager’ THEN 2.3 X Basic Salary

WHEN  ‘Program Manager’ THEN 3.6 X Basic Salary

ELSE 1.2 X Basic Salary

FROM Employees;

This article was in series of articles, we have been publishing under “SQL tutorial for beginners”. Here are links to a 3-part SQL tutorial series:

SQL tutorial for Beginners Part 1

SQL tutorial for Beginners Part 2

SQL tutorial for Beginners Part 3

 

Cheers

 

SQL Tutorial for Business Analysts Part 3

SQL tutorial for Business Analysts

This is the third and the concluding part of the SQL tutorial for business analysts. In the first part of this SQL tutorial for Business analysts, you learnt about the basics of a database system and SQL. In the second part of this SQL tutorial for business analysts, you learnt about INSERT, DELETE and CREATE TABLE commands.

In this concluding part, you are going to learn about SELECT command.

Introduction to SELECT Command

SELECT command is probably the most used command in SQL. It’s used primarily to get data from a single table or multiple tables. The usage of SELECT command are as follows:

a) If you are a SQL developer, you may use it for creating reports or display data

b) If you are a Business Analyst or a testing professional, you may use SELECT to validate the data stored in the table(s) (to check if correct data is getting saved).

c) Business Analysts may also use it to get data for analysis

Syntax of SELECT command

The syntax of SELECT command is as shown below:

SELECT <<col_name1>>, <<col_name2>>…..

FROM <<table_1>>, <<table_2>>

WHERE <<conditions>> AND <<Joins>>

We will explain the syntax shortly but first of all, let’s see one example. We have been referring to STUDENTS table in this tutorial and we added rows to it as well. Let’s see, how can we fetch the data from that table. Write the following command in the W3 SQL editor and hit “Run SQL” button:

SELECT * FROM STUDENTS;

The result of this command is as shown below:

SELECT command in SQL

You can see that it returns all the rows (I had only inserted one row). If you have completed the exercise in the previous tutorial, it should return all the rows you had inserted.

Please note “*” in SELECT command is meant for showing all the columns and not for all the rows. 

So what if there are multiple rows and we only want a specific row. Let’s take an example to understand that. There are multiple rows in the CUSTOMERS table in example database. As of now, if I run the following command on CUSTOMERS table:

SELECT * FROM CUSTOMERS;

It returns 91 rows as shown below:

Select command in SQL

What if I want only specific row or rows? What should I do?

Using conditions in SELECT

That’s where the WHERE <<condition>> section comes into picture. So, let’s say we would like to see the customers who belong to “Berlin” (city name). So how will we write this SELECT?

SELECT * FROM CUSTOMERS

WHERE city = “Berlin”;

This SELECT statement will return you only those customers, which belong to “Berlin”. It’s also possible to have multiple conditions in a single SELECT statement.

The table contains multiple Customers from the USA. As of now, there are 13 customers from the USA. But if we only want to know about the customers from the city “Seattle” in the USA, we can write the SELECT statement as shown below:

SELECT * FROM CUSTOMERS
WHERE country=”USA”
AND city = “Seattle”

This will get only those customers, which are from “Seattle” in the USA. You can have as many conditions in the SELECT statement.

Practice Exercises

You can use the tables provided in SQL editor to practice.

Joins in SELECT Command

In the last section of this three part SQL tutorial, we are going to discuss about JOINS. JOINS are used if we would like to get data from more than one table. In that case, we need to create the join on the columns, which are common between the tables.

We have already seen the syntax in this post. Let’s look at our example case. In the sample database given in W3 SQL editor, let’s look at PRODUCTS and CATEGORIES tables. If you see the data in the PRODUCTS table, you can see that products belong to various categories and these categories are stored in the PRODUCTS table as CategoryID.

SQL Tutorial image

This CategoryID is actually a column in the CATEGORIES table. If we try to get the data from CATEGORIES table, we will get the data with CATEGORYID as 1, 2 etc. But that’s not very easy to understand, what if we would like to see the proper Category name along with the product names and unit. Let’s see how to write the SELECT command in this case.

SELECT productname, categoryname, unit FROM PRODUCTS A, CATEGORIES B

WHERE A.categoryID = B.CategoryID

As a result, we will get the following result:

SQL Tutorial image

We have used A & B immediately after the table names for convenience. These are also known as aliases. The join is created using the following line:

A.categoryID = B.CategoryID

Since CategoryID is the common column name, we have equated them to avoid Cartesian product (Remember Set theory?). If there are more than one common columns between the tables, you should equate all of them, else you will get wrong results.

Summing up SQL Tutorial

You have learnt basic commands of SQL in this tutorial, there are many commands as discussed in Part-I of this SQL tutorial. To get a stronger grip on the SQL command, you need to practice a lot more . This is an important skill, expected from a Business Analyst in the job interviews.

We are coming up with a more comprehensive tutorial for learning Business Analysis skills . That tutorial will provide steps to learn the business analyst skills with specific resources including videos and tutorials.

You may also like to look at our other tutorials and videos, specially for professionals, who are looking to become a business analyst.

Business Analyst Tutorials

Finally, SQL is an important skill for Business Analysts specially if you are looking to become a business analyst. Techcanvass provides a Certified Business Analyst (ECBA) training program, to know more about it you can click on the following image.

Certified Business Analyst Training

SQL Tutorial for Beginners Part 2

 

SQL Tutorial for Beginners

This is the second part of SQL Tutorial for beginners. In this first part of this SQL tutorial for beginners, you learnt about the basics of a database system and SQL.

In this part, we are going to learn about CREATE TABLE & INSERT/DELETE commands. These commands are SQL commands and are used to interact with the database. We are going to use W3 School Online SQL editor in this tutorial, so keep it open in another tab in your browser.

CREATE TABLE COMMAND

Create table command is used to create a table. We have discussed STUDENTS table in the part I of this tutorial. Let’s re-produce that table for our reference.

Tables in database

Tables in database

To create this table using SQL, we use CREATE TABLE command. The Syntax of this command is as follows:

CREATE TABLE <<TABLE_NAME>>

(

Column_name1 datatype,

Column_name2 datatype,

….

)

The datatype is the type of data, which will be stored in the column like Text, number or date etc. So to create the STUDENTS table, we can write the following CREATE TABLE command:

CREATE TABLE STUDENTS
(

student_name varchar2(100),
date_of_joining date,
course_name varchar2(100),
contact_number number(11)

)

You can type the above command in the SQL statement box and click on “Run SQL” button. You can see that a new entry will be created at the bottom “Your database” section on the right, as indicated in the image below:

Create table command

Create table commandCreate table command

One important aspect, you must remember, the table & column names must be a single word. We cannot use “Student name” as column name and that’s the reason, we have used “student_name” as column name.

Note: All Database systems will not allow duplicate names for tables and other database components.

Practice Exercises

You can see a lot of tables in the right side section of the W3 SQL editor. Click on them and try and create tables with similar column names but different table names.

INSERT Command

INSERT Command is used to put data into the table. It’s a simple command and the syntax is as shown below:

INSERT INTO <<table_name>> (column names….) values (….);

To understand it well, let’s again refer to our STUDENTS table above. Let’s try and put the first row into the table STUDENTS, which we have created in the previous section. Let’s say that we have to put the first row in the table STUDENTS, the INSERT command will be written as follows:

INSERT INTO STUDENTS VALUES (“Ramesh Chandra”, “01-Jan-2014”, “.NET”, 999999999);

Please note that we have used ” ” for text and date values but we have not used ” ” for contact number, which is a number column.

Practice Exercises

Refer to STUDENTS table and INSERT the remaining rows in the STUDENTS table.

DELETE Command

DELETE command is used to delete the rows from the tables. It’s again a simple command and its syntax is as follows:

DELETE FROM <<table_name>> condition;

It’s important to note that if we don’t use the condition, the DELETE command will delete all the rows from the table. We will see how to specify the condition in the last part of this tutorial.

What’s next: Part-III of this SQL tutorial, where we will discuss SELECT statement.

We are coming up with a more comprehensive tutorial for learning Business Analysis skills . That tutorial will provide steps to learn the business analyst skills with specific resources including videos and tutorials.

You may also like to look at our other tutorials and videos.

Business Analyst Tutorials

Finally,  SQL is an important skill for Business Analysts specially if you are looking to become a business analyst.

Techcanvass has multiple levels of training program for business analysts.

IIBA ECBA Business Analyst Training

Certified Agile Business Analyst (CABA) Training

Business Analyst in Risk Management

 

SQL Tutorial for Business Analysts

SQL Tutorial for Business Analysts

SQL is an important skill for every entry level business analyst. In this SQL tutorial for business analysts, you will learn the basics of SQL including database fundamentals, SQL structure and SELECT command. This is a 3-part tutorial series.

Introduction to SQL

Structured Query Language (SQL) is used to interact with databases. A database is nothing but a software system to store data for later usage. If you don’t save the data, you can’t use it later. For example, when you register on a website as a user, the website saves your data (like name, user id and password etc) so that you can login anytime after your successful registration.

When you enter your userid and password in the login box, it checks that data with the saved information and if the entered data is found and matched, you are allowed to enter the website and use its features.

SQL is the language, which programmers and IT professionals use to interact with database systems.

SQL and Database

SQL and Database

Formally, a database is known as a Database management system or relational database management system (RDBMS). Oracle, SQL Server and mySQL are some of the popular database systems. A database system has many components, which are associated with storage and management of data. A table is specifically used for storage of data whereas other components are used for related purposes.

Components of a database

Components of a database

A table is a combination of rows and columns (think of Microsoft Excel worksheet). To identify columns in Excel, we give it a name, similarly in database systems, we give names to table columns to identify them. See the STUDENTS table below, it has named columns to identify “WHAT IS WHAT”, sounds logical?

Tables in Database

Tables in Database

The STUDENTS table has 4 columns and 5 rows. Each column representing a specific aspect of STUDENT like name, date of joining etc. This table stores the details of STUDENTS, who have joined courses at Techcanvass.

Database Operations

How do you save data into table? If you want to know when did “Ramesh Chandra” joined, how can you do it? This is where SQL or Structured Query language comes into picture. SQL provides mechanism (known as Commands) to accomplish the tasks of saving, querying and performing other operations with the tables and other components of a database.

The most important commands are SELECT, INSERT, UPDATE, DELETE. There are many other commands like CREATE TABLE, DROP TABLE etc. SQL provides commands to deal with each of the components of database. You can refer to the diagram above named “Components of a database”. If you are not planning to become a SQL developer, you should only focus on learning the following commands:

  • CREATE TABLE
  • INSERT/DELETE
  • SELECT

We are going to focus on these 3 commands in this tutorial. In Part II of this tutorial, we are going to look at CREATE TABLE & INSERT/DELETE, while Part III will deal with SELECT command.

We are coming up with a more comprehensive tutorial for learning Business Analysis skills . That tutorial will provide steps to learn the business analyst skills with specific resources including videos and tutorials.

You may also like to look at our other tutorials and videos, specially for professionals, who are looking to become a business analyst.

Business Analyst Tutorials

Business Analyst Tutorials

 

Finally, SQL is an important skill for Business Analysts specially if you are looking to become a business analyst. Techcanvass provides a Certified Business Analyst (ECBA) training program, to know more about it you can click on the following image

Certified Business Analyst Training

Cheers