Difference between revisions of "Database Queries"

From rbachwiki
Jump to navigation Jump to search
 
(24 intermediate revisions by the same user not shown)
Line 1: Line 1:
=Select Command=
==Select Commands==
; select * from tableName
 
: Select all columns from the table
SELECT * from tableName
; select column1, column2 from tableName
  - SELECT all columns FROM the table
: Select 2 columns for the tableName table
 
; select distinct column form tableName
; SELECT column1, column2 FROM tableName
: SELECT 2 columns FROM the tableName table
; SELECT DISTINCT column FROM tableName
: will display only unique results (no duplicates)
: will display only unique results (no duplicates)
; select firstname || ' ' || lastname from tablename
; SELECT firstname || ' ' || lastname FROM tablename
: will concatenate first and last name into one field (the ' ' between the || inserts a space)
: will concatenate first and last name into one field (the ' ' between the || inserts a space)
; select fristname || ' ' || lastname "Customer Name" from table name
; SELECT fristname || ' ' || lastname "Customer Name" FROM table name
: this will add a alias for the column heading called Customer Name
: this will add a alias for the column heading called Customer Name
:SELECT title AS "Title of Book", category FROM books;
; This adds the column alias "Title of Book" to the results instead of using just the title as the column heading
== DISTINCT VALUES ==
SELECT DISTINCT SALES_DATE, ORDER_ID FROM SALES
== Select all tables owned by the user ==
  SELECT * FROM CAT;
SELECT * FROM CAT
WHERE TABLE_NAME LIKE '%ACCT%';
==select table_name FROM user_tables ==
that exact statement will list all tables
== selecting column names, data type, and default values ==
select column_name, data_type, data_default from user_tab_columns where table_name = 'ACCTMANAGER'
== Creating Calculated Fields in a Query ==
SELECT title, retail*cost as profit FROM books;
retail column is multiplied by cost and the result is displayed in the alias field profit
== Using Concatenation ==


SELECT firstname || lastname FROM customers;
this will display the info but last and first names will be together eg. firstlast
to add a space between the names :
SELECT firstname || ' , ' | || lastname as "Customer Name" FROM customers;
this will add the , between the names and give it an alias heading "Customer name"


[[#Select Command|Back To Top]]-[[Main_Page| Home]] - [[Oracle_SQL|Category]]
[[#Select Command|Back To Top]]-[[Main_Page| Home]] - [[Oracle_SQL|Category]]

Latest revision as of 20:06, 5 November 2017

Select Commands

SELECT * from tableName
 - SELECT all columns FROM the table
SELECT column1, column2 FROM tableName
SELECT 2 columns FROM the tableName table
SELECT DISTINCT column FROM tableName
will display only unique results (no duplicates)
SELECT firstname || ' ' || lastname FROM tablename
will concatenate first and last name into one field (the ' ' between the || inserts a space)
SELECT fristname || ' ' || lastname "Customer Name" FROM table name
this will add a alias for the column heading called Customer Name
SELECT title AS "Title of Book", category FROM books;
This adds the column alias "Title of Book" to the results instead of using just the title as the column heading

DISTINCT VALUES

SELECT DISTINCT SALES_DATE, ORDER_ID FROM SALES

Select all tables owned by the user

 SELECT * FROM CAT;
SELECT * FROM CAT
WHERE TABLE_NAME LIKE '%ACCT%';

select table_name FROM user_tables

that exact statement will list all tables

selecting column names, data type, and default values

select column_name, data_type, data_default from user_tab_columns where table_name = 'ACCTMANAGER'

Creating Calculated Fields in a Query

SELECT title, retail*cost as profit FROM books;
retail column is multiplied by cost and the result is displayed in the alias field profit

Using Concatenation

SELECT firstname || lastname FROM customers;
this will display the info but last and first names will be together eg. firstlast
to add a space between the names :
SELECT firstname || ' , ' | || lastname as "Customer Name" FROM customers;
this will add the , between the names and give it an alias heading "Customer name"

Back To Top- Home - Category