Friday, 18 October 2013


SQL is a standard language for accessing and manipulating databases.
Abbreviation of structured query language, and pronounced either see-kwellor as separate letters. SQL is a standardized query language for requesting information from a database. The original version called SEQUEL (structuredEnglish query language) was designed by an IBM research center in 1974 and 1975. SQL was first introduced as a commercial database system in 1979 by Oracle Corporation.
Historically, SQL has been the favorite query language for database management systems running on minicomputers and mainframes. Increasingly, however, SQL is being supported by PC database systems because it supports distributed databases (databases that are spread out over several computer systems). This enables several users on a local-area network to access the same database simultaneously.
Although there are different dialects of SQL, it is nevertheless the closest thing to a standard query language that currently exists. In 1986, ANSI approved a rudimentary version of SQL as the official standard, but most versions of SQL since then have included many extensions to the ANSI standard. In 1991, ANSI updated the standard. The new standard is known as SAG SQL.

    What is SQL?

    • SQL stands for Structured Query Language
    • SQL lets you access and manipulate databases
    • SQL is an ANSI (American National Standards Institute) standard

    What Can SQL do?

    • SQL can execute queries against a database
    • SQL can retrieve data from a database
    • SQL can insert records in a database
    • SQL can update records in a database
    • SQL can delete records from a database
    • SQL can create new databases
    • SQL can create new tables in a database
    • SQL can create stored procedures in a database
    • SQL can create views in a database
    • SQL can set permissions on tables, procedures, and views

    SQL is a Standard - BUT....

    Although SQL is an ANSI (American National Standards Institute) standard, there are different versions of the SQL language.
    However, to be compliant with the ANSI standard, they all support at least the major commands (such as SELECT, UPDATE, DELETE, INSERT, WHERE) in a similar manner.


    RDBMS stands for Relational Database Management System.
    RDBMS is the basis for SQL, and for all modern database systems such as MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access.
    The data in RDBMS is stored in database objects called tables.
    A table is a collection of related data entries and it consists of columns and rows.

    SQL Commands


    A database consists of one or more tables. A table is identified by its name. A table is made up of columns and rows. Columns contain the column name and data type. Rows contain the records or data for the columns.

    Basic SQL

    Each record has a unique identifier or primary key. SQL, which stands for Structured Query Language, is used to communicate with a database. Through SQL one can create and delete tables. Here are some commands:
    • CREATE TABLE - creates a new database table
    • ALTER TABLE - alters a database table
    • DROP TABLE - deletes a database table
    • CREATE INDEX - creates an index (search key)
    • DROP INDEX - deletes an index
    SQL also has syntax to update, insert, and delete records.
    • SELECT - get data from a database table
    • UPDATE - change data in a database table
    • DELETE - remove data from a database table
    • INSERT INTO - insert new data in a database table


    The SELECT is used to query the database and retrieve selected data that match the specific criteria that you specify:

    SELECT column1 [, column2, ...]
    FROM tablename
    WHERE condition

    The conditional clause can include these operators
    • = Equal
    • > Greater than
    • < Less than
    • >= Greater than or equal
    • <= Less than or equal
    • <> Not equal to
    • LIKE pattern matching operator
    SELECT * FROM tablename 

    returns all the data from the table.

    Use single quotes around text values (most database systems will also accept double quotes). Numerical values should not be enclosed in quotes.
    LIKE matches a pattern. The wildcard % is used to denote 0 or more characters.
    • 'A%' : matches all strings that start with A
    • '%a' : matches all strings that end with a
    • '%a%' : matches all strings that contain an a


    The CREATE TABLE statement is used to create a new table. The format is:

    CREATE TABLE tablename
    (column1 data type,
    column2 data type,
    column3 data type);
    • char(size): Fixed length character string.
    • varchar(size): Variable-length character string. Max size is specified in parenthesis.
    • number(size): Number value with a max number of columns specified in parenthesis
    • date: Date value
    • number(size,d): A number with a maximum number of digits of "size" and a maximum number of "d" digits to the right of the decimal


    Once a table has been created data can be inserted using INSERT INTO command.

    INSERT INTO tablename
    (col1, ... , coln)
    VALUES (val1, ... , valn)


    To change the data values in a pre existing table, the UPDATE command can be used.

    UPDATE tablename
    SET colX = valX [, colY = valY, ...]
    WHERE condition


    The DELETE command can be used to remove a record(s) from a table.

    DELETE FROM tablename
    WHERE condition

    To delete all the records from a table without deleting the table do

    DELETE * FROM tablename


    To remove an entire table from the database use the DROP command.

    DROP TABLE tablename


    ORDER BY clause can order column name in either ascending (ASC) or descending (DESC) order.

    ORDER BY col_name ASC

    AND / OR

    AND and OR can join two or more conditions in a WHERE clause. AND will return data when all the conditions are true. OR will return data when any one of the conditions is true.


    IN operator is used when you know the exact value you want to return for at least one of the columns

    SELECT * FROM table_name WHERE col_name IN (val1val2, ...)


    The BETWEEN ... AND operator selects a range of data between two values. These values can be numbers, text, or dates.

    SELECT * FROM table_name WHERE col_name BETWEEN val1 AND val2


    There are times when we need to collate data from two or more tables. That is called a join. Tables in a database are related to each other through their keys. We can associate data in various tables without repeating them. For example we could have a table called Customers which could have information about customers like their name, address, phone numbers. We could have another table called Products that has information regarding the products like part number, product name, manufacturer, number in stock, unit price. A third table called Orders could have information regarding what product was ordered, by whom, the date the order was placed, and quantity. Here are the tables:
    01MickeyMouse123 Gouda St.456-7890
    02DonaldDuck325 Eider Ln.786-2365
    2005-2721-680231 Oct 20052
    2005-3420-450102 Nov 20053
    We can obtain information on who has ordered what:

    SELECT Customers.FirstName, Customers.LastName, Products.Name
    FROM Customers, Products, Orders
    WHERE Customers.Cust_ID = Orders.Cust_ID AND Products.Part_No = Orders.Part_No
    We can select data from two tables with INNER JOIN. The INNER JOIN returns all rows from both tables where there is a match. If there are rows in Customers that do not have matches in Orders, those rows will not be listed.
    SELECT Customers.FirstName, Customers.LastName, Orders.Date
    FROM Customers
    INNER JOIN Orders
    ON Customers.Cust_ID = Orders.Cust_ID
    The LEFT JOIN returns all the rows from the first table (Customers), even if there are no matches in the second table (Orders). If there are rows in Customers that do not have matches in Orders, those rows also will be listed.
    SELECT Customers.FirstName, Customers.LastName, Orders.Date
    FROM Customers
    LEFT JOIN Orders
    ON Customers.Cust_ID = Orders.Cust_ID
    The RIGHT JOIN returns all the rows from the second table (Orders), even if there are no matches in the first table (Customers). If there had been any rows in Orders that did not have matches Customers, those rows also would have been listed.
    SELECT Customers.FirstName, Customers.LastName, Orders.Date
    FROM Customers
    RIGHT JOIN Orders
    ON Customers.Cust_ID = Orders.Cust_ID


    With ALTER TABLE you can add or delete columns in an existing table. When you add a column you must specify a data type.
    ALTER TABLE table_name
    ADD col_name datatype

    ALTER TABLE table_name
    DROP COLUMN col_name


    The UNION command is used to select data from two tables very similar to the JOIN command. But the UNION command can be used only with columns having the same datatype. With UNION only distinct values are selected, i.e. if there are common data in the two tables only one instance of that data is returned.

    SELECT Name FROM Customers_USA
    SELECT Name FROM Customers_Asia

    This will select all the customers from USA and Asia but if there is a name that occurs in both the tables it will return only one such name. To get all the names use UNION ALL instead.

    SQL Functions

    There are several built-in functins in SQL. The basic function types are:
    • Aggregate Functions: These are functions that operate against a collection of values, but return a single value.
    • Scalar Functions: These functions operate against a single value, and return a single value.
    To use a built-in function the syntax is: 

    SELECT function (col_name) FROM table_name 


    The GROUP BY was added to SQL so that aggregate functions could return a result grouped by column values.

    SELECT col_name, function (col_name) FROM table_name GROUP BY col_name
    HAVING keyword was introduced because the WHERE keyword could not be used. HAVING states a condition.

    SELECT clo_name, function (col_name) FROM table_name
    GROUP BY col_name
    HAVING function (col_name) condition value


    A view is a virtual table that is a result of SQL SELECT statement. A view contains fields from one or more real tables in the database. This virtual table can then be queried as if it were a real table.

    CREATE VIEW view_name AS
    SELECT col_name(s)
    FROM table_name
    WHERE condition

    A view could be used from inside a query, a stored procedure, or from inside another view. You can add functions and joins to a view and present the data you want to the user.

    Shailesh kr shukla

        No comments:

        Post a Comment