SQL Loader with XML DATA

1. conn hr/hr
2. create table load_test of xmltype;
3. exit fom user
4. create a control file test.ctl


LOAD DATA
INFILE *
TRUNCATE INTO TABLE load_test
XMLType(xmldata)
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(
xmldata
)
BEGINDATA
KING<  ./name><  ./  EMP>,
SCOTT<. /name><  ./EMP>,
SMITH< . /name><  ./EMP>

5. c:\> sqlldr hr/hr control= c:\test.ctl

6. sqlplus
   hr/hr
   select * from load_test;



Example to store xmltype data from sql*loader
1. conn hr/hr
2. create table load_test of xmltype;
3. create a data file c:\person.dat - contains XML data

4. create a control file c:\load.ctl
LOAD DATA
INFILE *
INTO TABLE test_load
APPEND XMLType(XMLDATA)
(
 lobfn FILLER CHAR TERMINATED BY ',',
 XMLDATA LOBFILE(lobfn) TERMINATED BY ''
 )
BEGINDATA
c:\person.dat

5. c:\> sqlldr hr/hr
control=c:\load.ctl
6.sqlplus
hr/hr
select * from load_test;

Export and Import Data from XML Schema Database

UserName :  sys as dba   passward:   oracle

SQL> show parameter db_name;
It will shows database name

SQL> exit;

Q> How to setup my SID?

A>  set oracle_sid <2nd DBname>



export from user oe to user scott
Export: Release 10.1.0.4.2 - Production on Fri Nov 11 17:41:39 2011
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Username: oe/oe
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Enter array fetch buffer size: 4096 >
Export file: EXPDAT.DMP > c:\ware.dmp
(2)U(sers), or (3)T(ables): (2)U > t
Export table data (yes/no): yes > Y
Compress extents (yes/no): yes > Y
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
TA
About to export specified tables via Conventional Path ...
Table(T) or Partition(T:P) to be exported: (RETURN to quit) > WAREHOUSES
. . exporting table                     WAREHOUSES          9 rows exported
Table(T) or Partition(T:P) to be exported: (RETURN to quit) >
Export terminated successfully without warnings.
C:\


***************************************************
TO IMPORT DATA FROM C:\WARE.DMP FILE TO USER SCOTT
C:\> IMP SCOTT/TIGER FILE=C:\WARE.DMP FULL=Y

XML Topics

Day 1:
 1. XML Introduction
2.  Introduction with Tags.
3.  DTD - Data Type Defination
4.  Namespaces
5.  Examples.
6.  XMLDB in Oracle 10G
7. Features and Usage of XMLDB
8. XMLType


XML -> Platform Independent , it can Store data and transfer the data

HTML --> Display the Data of XML

Root, Element and Attributes, Child and Subchild.


Day 2:

1. XQuery
2. XML SQL Functions
3. DML Operation Functions

XMLQuery() and XMLTable()

Day 3:

1. Schema Introduction

2. Schema Registration

3. Table Creation with Schema

4. Constraint in XMLType Table

5. To Check Validation of Schema

6. SQL Loader

7. Export and Import

8. XPath

DBMS_XMLSTORE SQL functions

How to Modify XML Data?

DBMS_XMLSTORE SQL functions is used for to replace, insert, and delete XML data without replacing the entire surrounding XML document. This means that you can perform piecewise manipulation operations on an XML document by using SQL functions.

You use the following SQL functions:
updateXML() to replace XML nodes of any kind insertChildXML(), insertXMLbefore(), and appendChildXML() to insert XML data deleteXML() to delete XML data

The SQL functions insertChildXML(), insertXMLbefore(), and appendChildXML() can perform updates that are more localized than with updateXML(), thereby greatly improving performance and making source code clearer and more concise.

Do not use the updateXML() function to insert or delete XML data by replacing a parent node in its entirety. Although this works, it is less efficient than using one of the other available functions, which perform more localized updates.

XQuery expressions in Oracle 10g

What are and How to use XQuery Expressions

XQuery expressions are case-sensitive. The expressions include the following:

• primary expression – literal, variable, or function application. A variable name starts with a dollar-sign ($) – for example, $foo. Literals include numerals, strings, and character or entity references.
• XPath expression – Any XPath expression. The developing XPath 2.0 standard will be a subset of XQuery. XPath 1.0 is currently a subset, although XQuery uses a richer type system.
• FLWOR expression – The most important XQuery expression, composed of the following, in order, from which FLWOR takes its name: for, let, where , order by, return.
• XQuery sequence – The comma (,) constructor creates sequences. Sequence-manipulating functions such as union and intersect are also available. All XQuery sequences are effectively flat: a nested sequence is treated as its flattened equivalent. Thus, for instance, (1, 2, (3, 4, (5), 6), 7) is treated as (1, 2, 3, 4, 5, 6, 7). A singleton sequence, such as (42), acts the same in most XQuery contexts as does its single item, 42. Remember that the result of any XQuery expression is a sequence.
• Direct (literal) constructions – XML element and attribute syntax automatically constructs elements and attributes: what you see is what you get. For example, the XQuery expression 33 constructs the XML element33.
• Computed (dynamic) constructions – You can construct XML data at runtime using computed values. For example, the following XQuery expression constructs this XML data: tata titi why? .

·                {attribute toto {2+3}, element bar {"tata", "titi"}, text {" why? "}

·               

In this example, element foo is a direct construction; the other constructions are computed. In practice, the arguments to computed constructors are not literals (such as toto and "tata"), but expressions to be evaluated (such as 2+3). Both the name and the value arguments of an element or attribute constructor can be computed. Braces ({, }) are used to mark off an XQuery expression to be evaluated.

• Conditional expression – As usual, but remember that each part of the expression is itself an arbitrary expression. For instance, in this conditional expression, each of these subexpressions can be any XQuery expression:something, somethingElse, expression1, and expression2.

·                if (something < somethingElse) then expression1 else expression2

·               

• Arithmetic, relational expression – As usual, but remember that each relational expression returns a (Boolean) value. Examples:

·                2 + 3

·                42 < $a + 5

·                (1, 4) = (1, 2)

·                5 > 3 eq true()

·               

• Quantifier expression – Universal (every) and existential (some) quantifier functions provide shortcuts to using a FLWOR expression in some cases. Examples:

·                every $foo in doc("bar.xml")//Whatever satisfies $foo/@bar > 42

·                some $toto in (42, 5), $titi in ("xyz12", "abc", 5) satisfies $toto = $titi

·               

• Regular expression – XQuery regexes are based on XML Schema 1.0 and Perl.
• Type expression – An XQuery expression that represents an XQuery type. Examples: item(), node(), attribute(), element(), document-node(), namespace(), text(), xs:integer, xs:string

Type expressions can have occurrence indicators: ? (optional: zero or one), * (zero or more), + (one or more). Examples: document-node(element())*, item()+, attribute()?.

XQuery also provides operators for working with types. These include cast as, castable as, treat as, instance of, typeswitch, and validate. For example, "42" cast as xs:integer is an expression whose value is the integer 2. (It is not, strictly speaking, a type expression, because its value does not represent a type.)

XMLForest SQL Function-- How to use it...

How to use XMLForest?

SQL function XMLForest produces a forest of XML elements from its arguments, which are expressions to be evaluated, with optional aliases.

Example 1:
 Generating Elements with Attribute and Child Elements
This example generates an Emp element for each employee, with a name attribute and elements with the employee hire date and department as the content.
SELECT XMLElement("Emp",
                  XMLAttributes(e.first_name ||' '|| e.last_name AS "name"),
                  XMLForest(e.hire_date, e.department AS "department"))
AS "RESULT"
FROM employees e WHERE e.department_id = 20;


Example 2:
Generating an Element from a User-Defined Datatype Instance
You can also use SQL function XMLForest to generate hierarchical XML from user-defined datatype instances.
SELECT XMLForest(
  dept_t(department_id,
         department_name,
         CAST (MULTISET (SELECT employee_id, last_name
                           FROM hr.employees e
                           WHERE e.department_id = d.department_id)
               AS emplist_t))
         AS "Department")
  AS deptxml
  FROM hr.departments d
  WHERE department_id=10;


 

XMLSEQUENCE SQL Function

How to use and what is XMLSEQUENCE SQL Function?

SQL function XMLSequence returns an XMLSequenceType value (a varray of XMLType instances). Because it returns a collection, this function can be used in the FROM clause of SQL queries.

Example 1:
SELECT value(T).getstringval() Attribute_Value
  FROM table(XMLSequence(extract(XMLType('V1V2V3'),
                                 '/A/B'))) T;

Example 2:
CREATE TABLE emp_xml_tab OF XMLType;

Table created.
INSERT INTO emp_xml_tab VALUES( XMLType('
                                           112Joe50000217
                                             Jane
                                             60000412Jack40000'));
1 row created.
COMMIT;


To create a new XML document containing only employees who earn $50,000 or more, you can use the following query:

SELECT sys_XMLAgg(value(em), XMLFormat('EMPLOYEES'))
  FROM emp_xml_tab doc, table(XMLSequence(extract(value(doc),
                                          '/EMPLOYEES/EMP'))) em
  WHERE extractValue(value(em), '/EMP/SALARY') >= 50000;


These are the steps involved in this query:
Function extract returns a fragment of EMP elements.
Function XMLSequence gathers a collection of these top-level elements into XMLType instances and returns that.
Function table makes a table value from the collection. The table value is then used in the query FROM clause.


Example 3:

XMLSEQUENCE: Generate a Document for Each Row of a Cursor
In this example, SQL function XMLSequence is used to create an XML document for each row of a cursor expression, and it returns an XMLSequenceType value (a varray of XMLType instances).
SELECT value(em).getClobVal() AS "XMLTYPE"
  FROM table(XMLSequence(Cursor(SELECT *
                                  FROM hr.employees
                                  WHERE employee_id = 104))) em;


Example 4:
XMLSEQUENCE: Unnesting Collections in XML Documents into SQL Rows

CREATE TABLE dept_xml_tab OF XMLType;

Table created.

INSERT INTO dept_xml_tab
  VALUES(
    XMLType('
               Sports
              
                 John33333
                
                 Jack333444
                
              
             '));

1 row created.

INSERT INTO dept_xml_tab
  VALUES (
    XMLType('
               Sports
              
                 Marlin20000
                
              
             '));

1 row created.

COMMIT;


SELECT extractValue(OBJECT_VALUE, '/Department/@deptno') AS deptno,
       extractValue(value(em), '/Employee/@empno') AS empno,
       extractValue(value(em), '/Employee/Ename') AS ename
  FROM dept_xml_tab,
       table(XMLSequence(extract(OBJECT_VALUE,
                                 '/Department/EmployeeList/Employee'))) em;

XMLAGG SQL Function-- How to use it...

How to use XMLAGG SQL Functions ..

SQL function XMLAgg is an aggregate function that produces a forest of XML elements from a collection of XML elements.

Example 1:
 Generating Department Elements with a List of Employee Elements
This example produces a Department element containing Employee elements with employee job ID and last name as the contents of the elements. It also orders the employee XML elements in the department by their last name. (The actual result is not pretty-printed.)
SELECT XMLElement("Department", XMLAgg(XMLElement("Employee",
                                                  e.job_id||' '||e.last_name)
                                       ORDER BY e.last_name))
  AS "Dept_list"    
  FROM hr.employees e
  WHERE e.department_id = 30 OR e.department_id = 40;

Example 2:
The result is a single row, because XMLAgg aggregates the rows. You can use the GROUP BY clause to group the returned set of rows into multiple groups. (The actual result of the following query is not pretty-printed.)
SELECT XMLElement("Department", XMLAttributes(department_id AS "deptno"),
                  XMLAgg(XMLElement("Employee", e.job_id||' '||e.last_name)))
   AS "Dept_list"
   FROM hr.employees e
   GROUP BY e.department_id;

Example 3:
 XMLAGG: Generating Nested Elements
Function XMLAgg can be used to reflect the hierarchical nature of some relationships that exist in tables. This example generates a department element for department 30. Within this element is a child element for each employee of the department. Within each employee element is a dependent element for each dependent of that employee.
First, this query shows the employees of department 30.
SELECT last_name, employee_id FROM employees WHERE department_id = 30;

LAST_NAME                 EMPLOYEE_ID
------------------------- -----------
Raphaely                          114
Khoo                              115
Baida                             116
Tobias                            117
Himuro                            118
Colmenares                        119

6 rows selected.

A dependents table is created, to hold the dependents of each employee.
CREATE TABLE hr.dependents (id NUMBER(4) PRIMARY KEY,
                            employee_id NUMBER(4),
                            name VARCHAR2(10));
Table created.
INSERT INTO dependents VALUES (1, 114, 'MARK');
1 row created.
INSERT INTO dependents VALUES (2, 114, 'JACK');
1 row created.
INSERT INTO dependents VALUES (3, 115, 'JANE');
1 row created.
INSERT INTO dependents VALUES (4, 116, 'HELEN');
1 row created.
INSERT INTO dependents VALUES (5, 116, 'FRANK');
1 row created.
COMMIT;
Commit complete.

This query generates the XML data for department that contains the information on dependents (the actual output is not pretty-printed):
SELECT
  XMLElement(
    "Department",
    XMLAttributes(d.department_name AS "name"),
    (SELECT
       XMLAgg(XMLElement("emp",
                         XMLAttributes(e.last_name AS name),
                         (SELECT XMLAgg(XMLElement("dependent",
                                        XMLAttributes(de.name AS "name")))
                            FROM dependents de
                            WHERE de.employee_id = e.employee_id)))
       FROM employees e
       WHERE e.department_id = d.department_id)) AS "dept_list"
  FROM departments d
  WHERE department_id = 30;

output
--------------------------------------------------------------------------------

 
   
    dept_list
--------------------------------------------------------------------------------
.


1 row selected in xml format it not showing in blog due to xml format ...

Function XMLElement

 XMLElement to construct XML instances from relational data. It takes as arguments an element name, an optional collection of attributes for the element, and zero or more additional arguments that make up the element content. It returns an XMLType instance.
Example 1:
Formatting a Date
This example shows how to produce an XML date with a format different from the XML Schema standard format.
-- With standard XML date format:
SELECT XMLElement("Date", hire_date)
  FROM hr.employees
  WHERE employee_id = 203;

Example 2:
-- With an alternative date format:
SELECT XMLElement("Date", to_char(hire_date))
  FROM hr.employees
  WHERE employee_id = 203;


Example 3:
 Generating an Element for Each Employee
This example produces an Emp element for each employee, with the employee name as its content:
SELECT e.employee_id,
       XMLELEMENT ("Emp", e.first_name ||' '|| e.last_name) AS "RESULT"
   FROM hr.employees e
   WHERE employee_id > 200;

Example 4:
 Generating Nested XML
To produce an Emp element for each employee, with elements that provide the employee name and hire date, do the following:
SELECT XMLElement("Emp",
                   XMLElement("name", e.first_name ||' '|| e.last_name),
                   XMLElement("hiredate", e.hire_date)) AS "RESULT"
FROM hr.employees e
WHERE employee_id > 200 ;

Example 5:
Generating Employee Elements with ID and Name Attributes
This example produces an Emp element for each employee, with an id and name attribute:
SELECT XMLElement("Emp", XMLAttributes(
                           e.employee_id as "ID",
                           e.first_name ||' ' || e.last_name AS "name"))
  AS "RESULT"
  FROM hr.employees e
  WHERE employee_id > 200;

Example 6:
As mentioned in "Escaping Characters in Generated XML Data", characters in the root-element name and the names of any attributes defined by AS clauses are not escaped. Characters in an identifier name are escaped only if the name is created from an evaluated expression (such as a column reference). The following query shows that the root-element name and the attribute name are not escaped. Invalid XML is produced because greater-than sign (>) and a comma (,) are not allowed in XML element and attribute names.
SELECT XMLElement("Emp->Special",
                  XMLAttributes(e.last_name || ', ' || e.first_name
                                AS "Last,First"))
   AS "RESULT"
   FROM hr.employees e
   WHERE employee_id = 201;

Example 7:
Using Namespaces to Create a Schema-Based XML Document
This example illustrates the use of namespaces to create an XML schema-based document. Assuming that an XML schema "http://www.oracle.com/Employee.xsd" exists and has no target namespace, then the following query creates an XMLType instance conforming to that schema:
SELECT XMLElement("Employee",
                  XMLAttributes('http://www.w3.org/2001/XMLSchema' AS
                                  "xmlns:xsi",
                                'http://www.oracle.com/Employee.xsd' AS
                                  "xsi:nonamespaceSchemaLocation"),
                  XMLForest(employee_id, last_name, salary)) AS "RESULT"
   FROM hr.employees
   WHERE department_id = 10;

Example 7:
Generating an Element from a User-Defined Datatype Instance
Example  shows an XML document with employee information. You can generate a hierarchical XML document with the employee and department information as follows:
CREATE OR REPLACE TYPE emp_t AS OBJECT ("@EMPNO" NUMBER(4),
                                         ENAME VARCHAR2(10));
/
Type created.

CREATE OR REPLACE TYPE emplist_t AS TABLE OF emp_t;
/
Type created.

CREATE OR REPLACE TYPE dept_t AS OBJECT ("@DEPTNO" NUMBER(2),
                                         DNAME VARCHAR2(14),
                                         EMP_LIST emplist_t);
/
Type created.

SELECT XMLElement("Department",
                  dept_t(department_id,
                         department_name,
                         CAST(MULTISET(SELECT employee_id, last_name
                                         FROM hr.employees e
                                         WHERE e.department_id = d.department_id)
                              AS emplist_t)))
  AS deptxml
  FROM hr.departments d
  WHERE d.department_id = 10;

 

Function XMLTable in Oracle 10g

The function XMLTABLE is used to translate an xml object into separated fields. But you
probably want to construct a table with xml content, which is different.
You can create a table with an extra column that contains xml content

1. CREATE TABLE mytable (my_id NUMBER PRIMARY KEY, my_xml XMLType);

2.
INSERT INTO mytable VALUES (1,xmltype(''));

3. SELECT my_id
      ,my_xml.extract('/myxml@id').getstringval()
from mytable
Finished.
---error


So you actually do want to use the XMLTABLE function, Your error indicates that you are not
getting the file at all. So you need to craft your url to load it correctly. A test case I
constructed with embedded xml is:
  SELECT seq
       , id
        , content
 FROM XMLTABLE('/xml/myrec'
        PASSING XMLType('

Output is:
 SEQ ID    CONTENT
---- ----- --------------------
   1 D12   hello1
   2 D13   hello2

SELECT person_id
FROM person_data a, XMLtable('for $root in $date
return $root/pdrecord/PDDOB/text()'
passing a.person_data as "date")t
/

 SELECT * FROM XMLTABLE(
     'ora:view("PERSON_DATA")/ROW/PERSON_ID,/ROW/PERSON_DATA')

SELECT XMLQuery(
     'ora:view("DEPARTMENTS")'
  RETURNING CONTENT) AS EMP_DEPARTMENTS
 FROM dual
/

Components of an XML Document

Components of an XML Document
In XML document comprises storage units containing parsed or unparsed data.
Parsed character data (PCDATA) is textual information comprising:
The markup that describes the data it contains. Markup includes:
Elements to describe the data it contains such as the root element (employees) and its child elements (employee, name)
Attributes, which are name and value pairs (id="100") included in the start tag of an element
The entities (&apos;) representing any character data substituted in place of their appearance
Character data described by the markup components, for example:
The value 100 assigned to the id attribute
The data Rachael O’Leary described by the element
The &apos; entity, which represents the apostrophe (') character
Note: The element tree in an XML document defines its layout and logical structure.
The unparsed data embedded in CDATA sections can be used in an XML document to contain textual data or encoded binary data such as graphic and sound files. A CDATA section starts with characters. The information contained inside the CDATA section is not parsed by an XML Parser and is taken as entered.

XML Elements :
A start tag (for example, ) includes:
The “<” character
A case-sensitive tag name (employee), without leading spaces
The “>” character
An end tag (for example ) includes:
The “<” character
A case-sensitive tag name that must be identical to the start tag name, but prefixed with a slash. Leading spaces are not permitted.
The “>” character
Data content: Can also contain elements such as in the slide example
In summary, an XML element includes a start tag, an end tag, and everything in between.
Empty elements have no content between the start and end tags. In this case, a shortened form can be used where the start tag name is followed by a slash (for example, ).
Tag names are a descriptive term for an XML element and its content (for example, employee). The tag name is known as the element type name.

XML Attributes:
Attributes are simple name-value pairs that are associated with a particular element. XML attributes must be specified after the start tag of an element or after the tag name of an empty element.
Example:
Attribute names are case-sensitive and follow the naming rules that apply to element names. In general, spaces are not used, but are allowed, on either side of the equal sign. Attribute names should be unique within the start tag.
The attribute values must be within matching quotation marks, either single or double. The example in the slide shows the employee id attribute value enclosed within double quotation marks and the name attribute value within single quotation marks. In the latter case, the &apos; entity must be used to include the apostrophe (single quotation mark) character in the name value.
Attributes provide additional information about the XML document’s content or other XML elements. Attributes can be used for the following purposes:
Describing how the XML document data is encoded or represented
Indicating where the links or external resources are located
Identifying and calling external processes such as applets and servlets
Specifying an element instance in the document for facilitating a rapid search
Note: Attributes always have a value. For example, name="" has an empty string value.

Structure of XML Document

1. Structure of an XML document:
An XML document contains the following parts:
1. Prologue
2. Root element
3. Epilogue



 ...


XML documents must start with an XML declaration.
An XML declaration:
Looks like a processing instruction with the xml name
Example: 

Is optional in XML 1.0 but mandatory in XML 1.1
Must contain the version attribute
May (optionally) include the following:
Encoding attribute
standalone attribute

An XML document contains the following parts:
The prologue, which may contain the following information:
XML declaration (optional in XML 1.0, mandatory in XML 1.1)
Document type definition (DTD), which is required only to validate the document structure
Processing instructions and comments, which are optional
The root element, which is also called the “document element” and contains all other elements
An epilogue, which contains processing instructions and comments
Processing instructions give commands or information to an application that processes the XML data.
Processing instructions have the format , where target is the name of the application that is expected to do the processing, and the instructions consist of a string of characters that embodies the information or commands for the application to process.
Processing instructions can be written in the prologue, epilogue, or root element of an XML document.

Introduction of XML

XML is Extensible Markup Language
Extensible Markup Language (XML):
 Extensible Markup Language (XML) has very simple, yet strict syntax rules. To use XML efficiently and create well-formed XML documents, you need to become familiar with the document structure and components. This lesson introduces the XML document and focuses on how to create well-formed documents.

XML is a markup language that provides a universal format for structured documents and data on the Web.
Although XML documents look similar to HTML documents, they are very different.
HTML is a markup language that is primarily used for formatting and displaying text and images in a browser.
XML is a markup language for structuring data rather than formatting information.
You use XML to create a document that contains structured data that can be used or interpreted by other applications. The format or structure is straightforward and can be used by any person or program that can read text.
Unlike HTML, the tags in XML are extensible, and so you can create your own tags as you need them. HTML has a set of predefined formatting tags that you can use, but you cannot create your own.
XML is part of the World Wide Web Consortium (W3C) standards.
Note: XHTML is a more refined version of HTML. XHTML stands for “Extensible Hypertext Markup Language.” For more information, see the section titled “Comparing XML and HTML.”
Example:



 
    120
    Weiss
    8000
 
 
    121
    Fripp
    8200
 

Example: A Simple XML Page
The slide example of a simple XML document uses nested elements to describe employee data. Elements are identified by tag names such as employee, employee_id, and last_name. Tag names are distinguishable as markup, rather than data, because they are surrounded by angle brackets (< and >).
Note: In XML, an element includes a start tag (), an end tag (), and all the markup and character data contained between those tags.


 

Oracle Practice SQL Queries 06:

 Oracle Practice SQL Queries 06:               

101) Display those employees who are working as manager?
                select e2.ename from emp e1,e2 where e1.mgr=e2.empno and e2.empno is not null
               
102) Count th number of employees who are working as managers (Using set opetrator)?
           
            SELECT d.dname
              FROM dept d
             WHERE LENGTH (d.dname) IN (SELECT   COUNT (*)
                                            FROM emp e
                                           WHERE e.deptno != d.deptno
                                        GROUP BY e.deptno)
                           
103) Display the name of the dept those employees who joined the company on the same date?
                select a.ename,b.ename from emp a,emp b where a.hiredate=b.hiredate and a.empno!=b.empno
               
104) Display those employees whose grade is equal to any number of sal but not equal to first number of sal?

                SELECT ename, sal, grade, SUBSTR (sal, grade, 1)
                  FROM emp, salgrade
                 WHERE grade != SUBSTR (sal, 1, 1)
                   AND grade = SUBSTR (sal, grade, 1)
                   AND sal BETWEEN losal AND hisal
               
105) Count the no of employees working as manager using set operation?

                SELECT COUNT (empno)
                  FROM emp
                 WHERE empno IN (SELECT a.empno
                                   FROM emp a
                                 INTERSECT
                                 SELECT b.mgr
                                   FROM emp b)        
                                         
106) Display the name of employees who joined the company on the same date?
                select a.ename,b.ename from emp a,emp b where a.hiredate=b.hiredate and a.empno!=b.empno;
               
107) Display the manager who is having maximum number of employees working under him?
               
                SELECT   e2.ename, COUNT (*)
                    FROM emp e1, e2
                   WHERE e1.mgr = e2.empno
                GROUP BY e2.ename
                  HAVING COUNT (*) = (SELECT   MAX (COUNT (*))
                                          FROM emp e1, e2
                                         WHERE e1.mgr = e2.empno
                                      GROUP BY e2.ename)

108) List out the employee name and salary increased by 15% and express as whole number of Dollars?
                select ename,sal,lpad(translate(sal,sal,((sal +(sal*0.15))/50)),5,'$') from emp
               
147) Produce the output of the emptable "EMPLOYEE_AND JOB" for ename and job ?
Ans:                 select ename"EMPLOYEE_AND",job"JOB" FROM EMP;

148) List of employees with hiredate in the format of 'June 4 1988'?
Ans:                 select ename,to_char(hiredate,'Month dd yyyy') from emp;

149) print list of employees displaying 'Just salary' if more than 1500 if exactly 1500 display 'on taget' if less than 1500 display below 1500?
Ans:
                    SELECT ename, sal,
                           (CASE
                               WHEN sal < 1500
                                  THEN 'Below_Target'
                               WHEN sal = 1500
                                  THEN 'On_Target'
                               WHEN sal > 1500
                                  THEN 'Above_Target'
                               ELSE 'kkkkk'
                            END
                           )
                      FROM emp
                     
150) Which query to calculate the length of time any employee has been with the company
Ans: select hiredate, to_char (hiredate,' HH:MI:SS') FROM emp

151) Given a string of the format 'nn/nn' . Verify that the first and last 2 characters are numbers. And that

the middle character is '/' Print the expressions 'Yes' IF valid ‘NO’ of not valid. Use the following values to

test your solution'12/54', 01/1a, '99/98'?
Ans:

152) Employes hire on OR Before 15th of any month are paid on the last friday of that month those hired after
15th are paid the last friday of th following month .print a list of employees .their hiredate and first pay date sort those who se salary contains first digit of their deptno?

Ans:
                    SELECT ename, hiredate, LAST_DAY (NEXT_DAY (hiredate, 'Friday')),
                           (CASE
                               WHEN TO_CHAR (hiredate, 'dd') <= ('15')
                                  THEN LAST_DAY (NEXT_DAY (hiredate, 'Friday'))
                               WHEN TO_CHAR (hiredate, 'dd') > ('15')
                                  THEN LAST_DAY (NEXT_DAY (ADD_MONTHS (hiredate, 1), 'Friday'))
                            END
                           )
                      FROM emp

153) Display those managers who are getting less than his employees salary?
Ans: select a.empno, a.ename, a.sal, b.sal, b.empno, b.ename from emp a, emp b where a.mgr=b.empno and a.sal>b.sal

154) Print the details of employees who are subordinates to BLAKE?
Ans: select a.empno,a.ename ,b.ename from emp a, emp b where a.mgr=b.empno and b.ename='BLAKE'

Oracle Practice SQL Queries 05:

 Oracle Practice SQL Queries 05:

71) Select count of employees in each department where count >3?

    Select count (*) from emp group by deptno having count (*)>3

72) Display dname where atleast three are working and display only deptname?

    select d.dname from dept d, emp e where e.deptno=d.deptno group by d.dname having count(*)>3


73) Display name of those managers name whose salary is more than average salary of Company?

            SELECT DISTINCT e1.ename, e1.sal
                       FROM emp e, e1, dept d
                      WHERE e.deptno = d.deptno
                        AND e.mgr = e1.empno
                        AND e1.sal > (SELECT AVG (sal)
                                        FROM emp);
                                       
74) Display those managers name whose salary is more than average salary of his employees?

                SELECT DISTINCT e1.ename, e1.sal
                           FROM emp e, e1, dept d
                          WHERE e.deptno = d.deptno
                            AND e.mgr = e1.empno
                            AND e1.sal > ANY (SELECT   AVG (sal)
                                                  FROM emp
                                              GROUP BY deptno);
                                             
75) Display employee name, sal, comm and net pay for those employees whose net pay is greater than or equal to any other employee salary of the company?

                SELECT ename, sal, NVL (comm, 0), sal + NVL (comm, 0)
                  FROM emp
                 WHERE sal + NVL (comm, 0) > ANY (SELECT e.sal
                                                    FROM emp e);
                                                   
76) Display those employees whose salary is less than his manager but more than salary of other managers?

            SELECT e.ename sub, e.sal
              FROM emp e, e1, dept d
             WHERE e.deptno = d.deptno
               AND e.mgr = e1.empno
               AND e.salary IN (SELECT e2.sal
                                  FROM emp e2, e, dept d1
                                 WHERE e.mgr = e2.empno AND d1.deptno = e.deptno);
                    
77) Display all employees’ names with total sal of company with each employee name?

78) Find the last 5(least) employees of company?

79) Find out the number of employees whose salary is greater than their managers salary?
       
            SELECT e.ename, e.sal, e1.ename, e1.sal
              FROM emp e, e1, dept d
             WHERE e.deptno = d.deptno AND e.mgr = e1.empno AND e.sal > e1.sal;
            
80) Display the manager who are not working under president but they are working under any other manager?

                SELECT e2.ename
                  FROM emp e1, emp e2, emp e3
                 WHERE e1.mgr = e2.empno AND e2.mgr = e3.empno AND e3.job ! = 'PRESIDENT';
                
81) Delete those department where no employee working?
        Delete from emp where empno is null;
       
82) Delete those records from emp table whose deptno not available in dept table?
        Delete from emp e where e.deptno not in (select deptno from dept)

83) Display those enames whose salary is out of grade available in salgrade table?
  
            SELECT empno, sal
              FROM emp
             WHERE sal < (SELECT MIN (losal)
                            FROM salgrade) OR sal > (SELECT MAX (hisal)
                                                       FROM salgrade)
                                                      
84) Display employee name, sal, comm and whose net pay is greater than any other in the company?

            SELECT ename, sal, comm, sal + comm
              FROM emp
             WHERE sal + comm > ANY (SELECT sal + comm
                                       FROM emp)
                                      
85) Display name of those employees who are going to retire 31-Dec-99 if maximum job period is 30 years?

                SELECT empno, hiredate, SYSDATE,
                       TO_CHAR (SYSDATE, 'yyyy') - TO_CHAR (hiredate, 'yyyy')
                  FROM emp
                 WHERE TO_CHAR (SYSDATE, 'yyyy') - TO_CHAR (hiredate, 'yyyy') = 30
                
86) Display those employees whose salary is odd value?
        Select ename, sal from emp where mod (sal, 2)! =0
       
87) Display those employees whose salary contains atleast 3 digits?
        Select ename, sal from emp where length (sal)=3

88) Display those employees who joined in the company in the month of Dec?
            Select empno, ename from emp where trim (to_char (hiredate,'Mon'))=trim ('DEC')
           
89) Display those employees whose name contains A?
            Select ename from emp where ename like ('%A%')

90) Display those employees whose deptno is available in salary?
            Select ename, sal from emp where deptno in (select distinct sal from emp);
           
91) Display those employees whose first 2 characters from hiredate - last 2 characters sal?
            Select empno, hiredate, sal from emp where trim (substr (hiredate, 1,2))=trim (substr (sal, -2,2));
            OR
            Select hiredate, sal from emp where to_Char (hiredate,'dd')=trim (substr (sal, -2,2))
           
92) Display those employees whose 10% of salary is equal to the year joining?
            Select ename, sal, 0.10*sal from emp where 0.10*sal=trim (to_char (hiredate,'yy'))
           
93) Display those employees who are working in sales or research?
            Select e.ename from emp e, dept d where e.deptno=d.deptno and d.dname in ('SALES','RESEARCH');

94) Display the grade of Jones?
            SELECT ename, grade
              FROM emp, salgrade
             WHERE (grade, sal) = (SELECT grade, sal
                                     FROM salgrade, emp
                                    WHERE sal BETWEEN losal AND hisal AND ename = 'JONES')

95) Display those employees who joined the company before 15th of the month?
            select ename ,hiredate from emp where hiredate<'15-Jul-02' and hiredate >='01-jul-02';

96) Display those employees who has joined before 15th of the month?
            select ename ,hiredate from emp where hiredate<'15-Jul-02'

97) Delete those records where no of employees in particular department is less than 3?
delete from emp where deptno in (select deptno from emp group by deptno having count(*) <3

98) Delete those employeewho joined the company 10 years back from today?
delete from emp where empno in (select empno from emp where to_char(sysdate,'yyyy')- to_char(hiredate,'yyyy')>=10)

99) Display the deptname the number of characters of which is equal to no of employee in any other department?

100) Display the deptname where no employee is working?
select deptno from emp where empno is null;


For More Quries  : Oracle Practice Sql-queries -06