Difference between revisions of "Database Queries"

From rbachwiki
Jump to navigation Jump to search
Line 24: Line 24:
== Using Concatenation ==
== Using Concatenation ==


== Creating a table based on another table (subquery) ==
CREATE TABLE cust_mkt AS (SELECT customers, city, state, zip FROM customers);
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"
== 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);
== Check Constraint ==
ALTER TABLE orders ADD CONSTRAINT orders_shipdate_ck CHECK (orderdate <= shipdate);
== Not Null Constraint ==
ALTER TABLE orders MODIFY (customer# CONSTRAINT orders_customer#_nn NOT NULL);


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

Revision as of 00:35, 13 October 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

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

Back To Top- Home - Category

Retrieved from "https://www.r512.com/index.php?title=Database_Queries&oldid=899"