Advance Queries INFSY 445 Fall 2005 Column Aliases

  • Slides: 14
Download presentation
Advance Queries INFSY 445 Fall 2005

Advance Queries INFSY 445 Fall 2005

Column Aliases n Used to make column names more meaningful n If alias contains

Column Aliases n Used to make column names more meaningful n If alias contains spaces, special symbols, or you want all upper case, must enclose in double quotes SELECT title AS “Title of Books”, category FROM books;

Data Alignment n Text data is left-aligned 4 Includes column headings n Numeric field

Data Alignment n Text data is left-aligned 4 Includes column headings n Numeric field data is right-aligned 4 Include column headings n Oracle will not display insignificant zeros

DISTINCT n Eliminates duplicate listings n Applies to all columns listed in the SELECT

DISTINCT n Eliminates duplicate listings n Applies to all columns listed in the SELECT clause SELECT DISTINCT state FROM customers;

Concatenation n Combine the contents of two or more columns n Use || n

Concatenation n Combine the contents of two or more columns n Use || n Readability || ‘ ‘ || which inserts blank space SELECT first. Name || ‘ ‘ || last. Name “CUSTOMER NAME” FROM customers;

Line Break SELECT customer# || CHR(10) || first. Name || ‘ ‘ || last.

Line Break SELECT customer# || CHR(10) || first. Name || ‘ ‘ || last. Name “CUSTOMER INFORMATION” FROM customers; n CHAR(10) code for line break in Oracle

ORDER BY n Display results of a query in a sorted order n Sort

ORDER BY n Display results of a query in a sorted order n Sort order is ascending by default 4 Specify desc for descending order n Sorting order 4 Numeric values 4 Character values 4 NULL values SELECT DISTINCT state FROM customers ORDER BY state;

ORDER BY SELECT last. Name, first. Name, city, state FROM customers ORDER BY state

ORDER BY SELECT last. Name, first. Name, city, state FROM customers ORDER BY state desc, city; n Secondary Sort 4 Additional field used to sort and break matches n Limit of 255 columns in sort n You may reference a column not listed in SELECT clause

Joining Tables n Join - creates a new table from two existing tables by

Joining Tables n Join - creates a new table from two existing tables by matching on a column common to both tables SELECT * FROM CUSTOMER, ORDER WHERE CUSTOMER. C_NUMBER = ORDER. C_NUMBER ORDER BY NAME;

Joining Tables n JOIN with multiple tables 4 Specify the tables to be joined

Joining Tables n JOIN with multiple tables 4 Specify the tables to be joined 4 Specify the matching criteria SELECT * FROM ORDER, PRODUCT, REQUEST WHERE ORDER. Order. Num = REQUEST. Order. Num AND PRODUCT. Product. Num = REQUEST. Product. Num;

Joining Tables n You may join tables on any column, not just key columns

Joining Tables n You may join tables on any column, not just key columns n Columns used in join must be of the same data type n Joins made on the basis of equality 4 Equi-joins 4 INNER JOIN

Joining Tables SELECT * FROM ORDER INNER JOIN REQUEST ON (ORDER. Order. Num =

Joining Tables SELECT * FROM ORDER INNER JOIN REQUEST ON (ORDER. Order. Num = REQUEST. Order. Num) INNER JOIN PRODUCT ON (REQUEST. Product. Num = PRODUCT. Product. Num);

Subquery or Nested Query SELECT AVG (shrpe) FROM shares; SELECT shrfirm, shrpe FROM shares

Subquery or Nested Query SELECT AVG (shrpe) FROM shares; SELECT shrfirm, shrpe FROM shares WHERE shrpe > x;

Subquery or Nested Query SELECT shrfirm, shrpe FROM shares WHERE shrpe > ANY (SELECT

Subquery or Nested Query SELECT shrfirm, shrpe FROM shares WHERE shrpe > ANY (SELECT AVG (shrpe) FROM shares);