Structured Query Language (SQL) => Beginner's Tutorial

TUTORIAL 1: GETTING STARTED WITH SQL QUERIES

Hello buddies, here I am with next tutorial in the series. I am going to postpone normalization concept as of now so that I don't confuse you all. Let's start writing queries and then when an apt situation comes, I will introduce normalization.

A query is a set of statements that is used to perform various functionalities on a database.

Types of commands:

1) Data Definition Language (DDL) Commands:

DDL commands are used to define the structure of a particular relation in database. A quick reminder that relation means table in database. For example, defining the structure of a table i.e. creating a table, altering a table (adding a column, modifying a column or renaming a column) and dropping a table i.e. deleting a table's structure from the database are termed as DDL commands.

2) Data manipulation Language (DML) Commands:

DML commands are used to manipulate the data inside a table and not the definition of the table as it was the case with DDL commands. For example: Inserting rows into a table, modifying/updating rows and deleting a row from the table are termed as DML commands.

3) Data Query Language (DQL) Commands:

This is not exactly a separate classification but many times we do define it separately. DQL command is used for fetching data from a table. SELECT statement is an exaple.

4) Transaction Control Language (TCL) Commands:
TCL Commands are used for handling the transactions. For examples: Commiting (Saving) changes done to the database, rolling back in case of failures, granting or revoking privileges/permissions come under TCL commands.

More on these commands will be in the coming tutorials.

Writing a simple Query:

Let's look into how to write a simple query to fetch data from a table in a database (DQL). Any database (Oracle 9i, Oracle 10g, etc) comes with a predefined set of tables. emp, dept, etc are some examples. emp table is used to store the details of some employees. Suppose we have a table named employee having the following structure:



Syntax of a simple SELECT statement:

SELECT column_list
FROM <table_name>
WHERE <condition>
GROUP BY <column name(s)>
HAVING <condition>
ORDER BY column-name;

In the next few tutorials, we will learn in detail what each and every part of this syntax does. In this tutorial we will look into what SELECT, FROM AND WHERE does in a simple query. Considering the table structure above, suppose I want to display the complete table on my screen (keep in mind that table is persent in the database and is not visible in a structure shown above). For displaying everything, we use a wild card known as asterisk (*). Asterisk means everything. So, the query to display everything from the table will be as follows:

SELECT *
FROM employee;

In simple words, the above query means select everything i.e. select every column data from employee table. So the output of the above query will display the table on the screen.

Displaying specific columns:

We can display specific columns too according to our requirements via an SQl query. Suppose I want to display only the employee number and employee name, I can do it using the following query

SELECT empno, ename
FROM employee;

Filtering the records based on a condition:

If we want to display only those records/rows that satisfy a particular condition, then we can do it using WHERE clause. For example: Suppose I want to display all the details of those employees whose salary is more than 25000, then I can write the following query:

SELECT *
FROM employee
WHERE sal>25000;

Note: You should use the same column names in queries as it is defined in the table creation. To know the columns that were used during table creation, you can use the following commands:

desc <table_name>;

Similarly, if I want to display the employee number, employee name and salary of those employees who earn more than 25000, I can write the following query

SELECT empno, ename, sal
FROM employee
WHERE sal>25000;

So, in this way we write a simple query on a table in database. I will wind up this tutorial here. My next tutorial will contain other keywords that are used in a typical select statement plus we will look into the DDL commands. Till then keep practicing..

TUTORIAL 2: DATA DEFINITION LANGUAGE (DDL) COMMANDS

Hello guys, welcome to the next tutorial in this series of tutorials. I am going to pick up from where we left in the previous tutorial.
Let's go on with the select statement syntax and pick the next clause called order by.

Order By:

As the name suggests, order by clause is used to arrange the records in increasing or decreasing order based on a specified column.
Referring to the table given in previous tutorial, suppose I want to display the employee details in increasing order of their salaries. This can be done by using following query:

SELECT *
FROM employee
ORDER BY sal;

We can sort it in descending order as follows:

SELECT *
FROM employee
ORDER BY sal desc;

desc specifies descending order.


We will discuss about Group by and having a little later in the series as it requires a bit of understanding of other sql concepts. Let's move on to DDL commands.

DDL COMMANDS:

The DDL commands that are provided in SQl are CREATE TABLE, ALTER TABLE, DROP TABLE.

CREATE TABLE:

This DDL comands is used to create a relation or table in the database. The syntax for tis is as follows:

CREATE TABLE <table_name>
(
column 1 datatype(size),
column 2 datatype(size),
.
.
.
.
.
)

Basic Data types:

Data Types are used to specify the type of data that a particular column of a table is going to store. Some of the basic data types of in SQL are as follows:

NUMBER => it is used to store numerical data

CHAR => it is used to a character values

VARCHAR2 => it is also used to store textual values. The difference betweem char and varchar2 is that char is used to store fixed length string. For example: if we use data type as char(20) is stores space for 20 characters even if the text entered is less than 20 characters whereas varchar2 is variable length and it stores the space of the number of characters entered.

DATE => Used to store data values

Suppose I want to create a table named Student to store details of students who appllied for courses in college. It can be done as follows

CREATE TABLE student
(
roll number(4),
name varchar2(20),
address varchar2(50),
course varchaar2(10),
);

The above command will create a table named student with 4 columns.

ALTER TABLE:

This command is used to alter a table's structure i.e. adding a column or renaming a column. Suppose I want to add another column named phone to student table. It can be done as follows:

ALTER TABLE student
add(phone char(10));

Suppose I want to rename the column roll to id. It can be done as follows:

ALTER TABLE student
rename column roll to id;

DROP TABLE:

This command is used to drop/delete complete table's structure from the database. The syntax for dropping a table is as follows

DROP TABLE <table_name>

So, student table that we created above can be dropped as follows:

DROP TABLE student;

Note: Before dropping a table, make sure that it doesn't contain any rows.

This completes DDL commands and this tutorial. I will be explaining about DML commands in the next tutorial.