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

BEGINNERS'S TUTORIAL : Structured Query Language (SQL)

Hello buddies, I am here with a dedicated thread that will focus upon SQL for beginners. I am going to provide more of an information about the concepts of Structured Query Language and will pay less attention on the history and stuff. Just a brief prologue is of it is as follows:


Prologue

Structured Query Language was developed for managing the data in Relational Database management System. It helps one to create and operate on relational database systems. Now lets have a brief outlook of what a database management system and relational database management systems actually are.

A database is a basic record keeping system. In other words, database is a central trpository of data.

DBMS: Database management System is helpful in storing data. It stores the data in plain form. For example: a flat file

RDBMS: Relational database management system on the other hand also stores data but in the form of relations. Relation, here specifies a tabular format. So RDBMS stores data in the form of tables (rows and columns).

Each and every transaction of databases must satisfy ACID properties:

Atomicity: Atomicity specifies that each and every transaction in SQl must be atomic. Atomic means all-or-none i.e. each and every transaction in sql must be either complete or it should be back in a state that it never started at all.
For example: While working on a database, if you are working on inserting data in some tables, and suddenly power goes off, then the original state of the table must be retained. Incomplete insertion must be rolled back. All the unfinished operations must be rolled back in case of any failure

Consistency: Consistency specifies that a database must always be in a consistent state. Its not feasible that you have data in shipments table without having a corresponding record in orders table. This will make database inconsistent. So all the transactions must be complete and consistent.

Isolation: Isolation specifies that one executing transaction is isolated from another transaction i.e. if one transaction is modifying some data on a table and at the same time another transaction is modifying the data or querying the data then it will see the data in a state in which it was before the modification began. this means one transaction is isolated from another transaction unless it is complete.

Durability: Durability specifies that in case there is any modification on a table as a result of a transaction, a backup of the old state is kept in the database and a row is inserted in transaction log so that in case if some failures occur, the original state of the database can be recovered from the backup. This makes the data durable.

So guys this is it for the prologue. A brief idea of what a database is and how transactions are carried out.
In the next tutorial we will have a closer look on what is called normalizing a database. See you all in the next tutorial.

Please don't comment in this thread. You can comment your views and concerns here: #-Link-Snipped-#

#-Link-Snipped-#
Please moderate if any comments come on this thread

Thanks,
Nick

Replies

  • Neeraj Sharma
    Neeraj Sharma
    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:

    sql1

    Syntax of a simple SELECT statement:

    SELECT column_list
    FROM
    WHERE
    GROUP BY
    HAVING
    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 ;

    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..
  • Neeraj Sharma
    Neeraj Sharma
    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
    (
    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

    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.

You are reading an archived discussion.

Related Posts

Can somebody come out and give some ideas for final year project for physically challenged people.. I am not able to get any thing new. If any of u help...
Nowadays most of the vacuum cleaners , have two ports in them, one is the suction port and the other the blower. their working principle is similar to that of...
I'm going to apply for the jobs and looking forward to resume templates. Can anyone suggest the best resume format for resume?
What is the abbreviation of AD in ncc?
First i want to introduce myself ,Im sanchit and m also a crazy engineer ,Computer programming is my area of Interest ,i have started a project which im going accomplish...