Introduction - SQL\PLSQL

What is SQL?

SQL : Structured Query Language

     SQL is a standard language designed for accessing and managing data in Relational Database Management Systems (RDBMS).

    SQL is based on ANSI (American National Standards Institute) standard introduced in 1986 and there are many SQL database software’s available in the market which follows the ANSI standard like:

·         Oracle

·         Mysql

·         Sqlserver

·         DB2 ……etc

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

Using SQL we execute different commands on RDBMS software’s to perform different operations on the data available in the database.

 Whenever we execute a SQL command the RDBMS software translates our command into understandable format of the database and do the necessary operation on data

To make it simple SQL Commands are divided into five categories, depending upon what they do.

1.    Data Retrieval Language (DRL)

2.    Data Manipulation Language (DML)

3.    Data Definition language (DDL)

4.    Transaction Control Language (TCL)

5.    Data Control Language (DCL)

What is PLSQL?

PL\SQL : Procedural Language/Structured Query Language

                      SQL has a special version named PL/SQL, which is sometimes termed as a super set of SQL. It bridges the gap between database technology and procedural languages as programmers can use PL/SQL to create programs for validation and manipulation of table, something that was not possible with SQL. 

  Please refer upcoming posts for more explanation .

Query to find concurrent request set attached Responsibility

SELECT   FRT.RESPONSIBILITY_NAME
       , FRG.REQUEST_GROUP_NAME
       , FRGU.REQUEST_UNIT_TYPE
       , FRGU.REQUEST_UNIT_ID
       , FCPT.USER_REQUEST_SET_NAME
FROM     APPS.FND_RESPONSIBILITY FR
       , APPS.FND_RESPONSIBILITY_TL FRT
       , APPS.FND_REQUEST_GROUPS FRG
       , APPS.FND_REQUEST_GROUP_UNITS FRGU
       , APPS.FND_REQUEST_SETS_TL FCPT
WHERE    FRT.RESPONSIBILITY_ID = FR.RESPONSIBILITY_ID
AND      FRG.REQUEST_GROUP_ID = FR.REQUEST_GROUP_ID
AND      FRGU.REQUEST_GROUP_ID = FRG.REQUEST_GROUP_ID
AND      FCPT.REQUEST_SET_ID = FRGU.REQUEST_UNIT_ID
AND      FRT.LANGUAGE = USERENV ('LANG')
AND      FCPT.LANGUAGE = USERENV ('LANG')
AND      FCPT.USER_REQUEST_SET_NAME = :REQUEST_SET_NAME
ORDER BY 1, 2, 3, 4

Query to find concurrent program attached Responsibility

SELECT FRT.RESPONSIBILITY_NAME , FRG.REQUEST_GROUP_NAME , FRGU.REQUEST_UNIT_TYPE , FRGU.REQUEST_UNIT_ID , FCPT.USER_CONCURRENT_PROGRAM_NAME FROM FND_RESPONSIBILITY FR , FND_RESPONSIBILITY_TL FRT , FND_REQUEST_GROUPS FRG , FND_REQUEST_GROUP_UNITS FRGU , FND_CONCURRENT_PROGRAMS_TL FCPT WHERE FRT.RESPONSIBILITY_ID = FR.RESPONSIBILITY_ID AND FRG.REQUEST_GROUP_ID = FR.REQUEST_GROUP_ID AND FRGU.REQUEST_GROUP_ID = FRG.REQUEST_GROUP_ID AND FCPT.CONCURRENT_PROGRAM_ID = FRGU.REQUEST_UNIT_ID AND FRT.LANGUAGE = USERENV ('LANG') AND FCPT.LANGUAGE = USERENV ('LANG') AND FCPT.USER_CONCURRENT_PROGRAM_NAME = :CONC_PRG_NAME ORDER BY 1, 2, 3, 4