1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Basic Queries of SQL and PLSQL

Discussion in 'SQL PL/SQL' started by sameer, Aug 30, 2008.

  1. sameer

    sameer Forum Advisor

    Messages:
    105
    Likes Received:
    6
    Trophy Points:
    240
    Basic Queries of SQL and PLSQL

    Structured Query Language(SQL) is a language used for manipulating database, access control management of database object, database schema creation, database schema modification, of a Relational Database Management System(RDBMS).

    We use SQL for modifying and querying databases. You can retrieve, insert, update and delete data along with performing various administrative and management functions.

    PL/SQL is a procedural language which is an extension to SQL. It was introduced to overcome the drawback of SQL language of its inability to execute procedural code. In other words we can say that, SQL does not allow conditional, sequential and iterative statements with procedural code. Both SQL and PL/SQL are used for data manipulation in Oracle databases. The only difference is that PL/SQL is a programming language that supports SQL queries and executes complete code at once unlike SQL which executes code line by line.

    Some of the BASIC queries of Structured Query Language(SQL) are as follows:

    SELECT QUERY
    Code (Text):

    SELECT * FROM < TableName >;
     
    This query would select and display all data in a specific table.

    However, we can also filter the data using WHERE conditional statement.
    Code (Text):

    SELECT * FROM< TableName >
    WHERE <Condition1> AND <Condition 2>;
    For example,
    SELECT * FROM < TableName >
    WHERE Marks>55;
     
    You may also want the data ordered by specified field. For this, we use ORDER BY condition.

    Code (Text):

    SELECT * FROM <TableName>
    WHERE Marks>55
    ORDER BY Name;
     
    You may also select specific fields from a table instead of displaying all data in the table.
    Code (Text):

    SELECT Rollno, Name, Marks
    FROM <TableName>
    WHERE Marks>55
    ORDER BY Name;
     
    JOIN QUERY

    Join query is basically of four types, inner join, outer join, left join and right join. It is used with select query to select data from more than one table. For example, there is a table Det1 with fields rollno, name and marks along with another table Det2 that has rollno and subject. If we want to display name, marks and subject of students obtaining marks>55 then the query would be:
    Code (Text):

    SELECT Det1.rollno, Det1.name, Det1.marks, Det2.subject
    FROM Det1 JOIN Det2
    ON Det1.rollno = Det2.rollno
    WHERE WHERE Marks>55;
     
    INSERT DATA MANIPULATION QUERY

    This query is used to insert rows in an existing database table.
    Code (Text):

    INSERT INTO <TableName> (field1, field2, field3) VALUES (value1, value2, value3);
     
    For example,
    Code (Text):

    INSERT INTO Det2(rollno, subject) VALUES (24, ‘MCA’);
     
    UPDATE DATA MANIPULATION QUERY

    This query is used to update an existing row in database table.
    Code (Text):

    UPDATE <TableName> SET fieldname=’new value’ WHERE fieldname=’value’;
     
    For example,
    Code (Text):

    UPDATE  Det2 SET subject = ’MBA’ WHERE rollno=24;
     
    DELETE DATA MANIPULATION QUERY

    This query is used to delete rows from an existing database table.
    Code (Text):

    DELETE <Field Name> FROM <TableName>;
     
    For example,
    Code (Text):

    DELETE FROM Det2;
     
    This would delete all information stored in the rows from Det2.

    We may use it with WHERE condition to refine our search.
    Code (Text):

    DELETE FROM Det2 WHERE Marks<55;