Difference between revisions of "Database Queries"

From rbachwiki
Jump to navigation Jump to search
 
(5 intermediate revisions by the same user not shown)
Line 1: Line 1:
==Select Commands==
==Select Commands==
  SELECT * from tableName
  SELECT * from tableName
   - SELECT all columns FROM the table
   - SELECT all columns FROM the table
Line 13: Line 14:
:SELECT title AS "Title of Book", category FROM books;
: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
; 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 ==
==select table_name FROM user_tables ==
Line 23: Line 33:
  retail column is multiplied by cost and the result is displayed in the alias field profit
  retail column is multiplied by cost and the result is displayed in the alias field profit
== Using Concatenation ==
== Using Concatenation ==
== Creating a table based on another table (subquery) ==
CREATE TABLE cust_mkt AS (SELECT customers, city, state, zip FROM customers);




Line 33: Line 40:
  SELECT firstname || ' , ' | || lastname as "Customer Name" FROM customers;
  SELECT firstname || ' , ' | || lastname as "Customer Name" FROM customers;
  this will add the , between the names and give it an alias heading "Customer name"
  this will add the , between the names and give it an alias heading "Customer name"
== Adding Primary Key Constraints ==
'''This makes customer# the primary key  the constraint name is used so you can easily '''
'''identify an error if you try to enter a duplicate customer'''
'''If you don’t assign constraint names, the error message displays the'''
'''system-generated constraint name, which isn’t as helpful'''
ALTER TABLE customers ADD CONSTRAINT customers_customer#_pk PRIMARY KEY(customer#);
== Foreign Key Constraint ==
'''This command instructs Oracle 11g to add a FOREIGN KEY constraint on the'''
'''Customer# column of the ORDERS table. The name chosen for the constraint is'''
'''orders_customer#_fk. This constraint makes sure an entry for the Customer# column of'''
'''the ORDERS table matches a value stored in the Customer# column of the CUSTOMERS'''
'''table. When the command executes, a message indicates the table was altered successfully'''
ALTER TABLE orders ADD CONSTRAINT orders_customer#_fk FOREIGN KEY(customer#) REFERENCE customers (customer#)
== Unique Constraints ==
ALTER TABLE tablename ADD CONSTRAINT constraintname UNIQUE(columnname);


[[#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