After completing this session, you will be able to:
- Write SQL Statements
- Create tables
- Maintain tables
- INSERT into table
- UPDATE rows from table
- DELETE rows from table
- Grant rights to Oracle database
- Revoke rights to Oracle database
Understand the features of SQL
What is SQL?
The Structured Query Language (SQL) is a set of statements with which all programs and users access data in an Oracle Database.
Who Developed SQL and When?
IBM developed the SQL, popularly pronounced as “SEQUEL” and implemented it in 1979. Actually, in doing so, IBM implemented the model on Relational Database Management Systems (RDBMS) developed by Dr. E.F. Codd. Today, SQL is the most popular RDBMS language.
What can SQL do?
With SQL*Plus, you can execute SQL commands and PL/SQL blocks, additionally you can perform the following tasks:
- Enter, edit, store, retrieve and run SQL commands and PL/SQL blocks
- Format, perform calculations on, store, print and create web output of query results
- List column definitions for any table
- Access and copy data between SQL databases
- Send messages to and accept responses from an end user
- Perform database administration
SQL statements can be classified into following categories:
|SELECT||Retrieves data from the database.|
|Enter new rows, changes existing rows, delete unwanted rows. Collectively known as data manipulation language (DML)|
|Set up, change, and remove data structure from the tables. Collectively known as data definition language (DDL)|
|Manage the change made by DML statements. Collectively known as transaction control language(TCL)|
|Grant or remove access to both database and its objects. Collectively Known as data control language (DCL)|
Data Definition Language (DDL)
Data Definition Language is a set of SQL commands used to create, modify and delete database structures.
- The CREATE TABLE is used to create and define a new relational table
- The DROP TABLE is used to delete a table and all data within the specified table
- The ALTER TABLE is used to change an existing table definition
- o Cannot delete an existing column
- o Cannot change an existing table constraint definition
- The TRUNCATE TABLE is used to delete all the rows within the specified table
- Rename table is used to change the name of an existing tables to the specified name.
CREATE TABLE: To create a table the user must have Create table privileges and a storage area in which to create the table
Rules for naming a table:
- The name must begin with a letter, A-Z or a-z.
- May contain letters, numeric and special character _(underscore). The $ and # are also legal but discouraged;
- The name is the same whether upper or lower case letters are used
- It may be up to 30 char length
- The name must not duplicate the name of any other object in your account.
- The name must not be a sequel reserved word
CREATE TABLE [SCHEMA.]Tablename (column_name data type [DEFAULT expr], column_name data type [default expr], …)
- SCHEMA is same as the owner’s name.
- Table name is the name of the table.
- DEFAULT expr specifies a default value
- Column_name is the name of the column.
- Data type is the column’s data type and its length.
CREATE TABLE product_dim (product_id NUMBER(5), product_name VARCHAR2(10))
Create table with constraints:
Constraint is a rule that restricts the values for a column on which it is defined. Constraints check data as it is entered or updated in the database and prevent data that does not conform to the constraint's rule from being entered.
Constraints are classified into two types:
- Column level constraints.
- Table level constraints.
NOT NULL: NOT NULL specifies that a column must have some value.
NULL: NULL (default) allows NULL values in the column.
DEFAULT: Specifies some default value if no value entered during INSERT
UNIQUE: Specifies that column(s) must have unique values
PRIMARY KEY: Specifies that column(s) must have unique values. Index is automatically generated for column
FOREIGN KEY : Specifies that column(s) are defined primary key in another table. Used for referential uniqueness of parent table. Index is automatically generated for column.
CHECK : Applies a condition to an input column value
DISABLE: Suffix DISABLE to any other constraint to make Oracle ignore the constraint, the constraint will still be available to
applications / tools and you can enable the constraint later if required
CREATE TABLE DAILY_PIPELINE_LOANS_FACT (LOANNO NUMBER(4) CONSTTRAINT DLY_PRIM PRIMARY KEY, LOAN_STATUS VARCHAR2(10) CONSTRAINT LOAN_STATUS_CONS CHECK(LOAN_STATUS=UPPER(LOAN_STATUS)), LOAN_AMOUNT NUMBER(10) AMOUNT_CONS NOT NULL, UPDATE_DATE DATE DEFAULT SYSDATE, PRODUCT_KEY VARCHAR2(5) CONSTRAINT DLY_PRODUCT FOREIGN KEY(PRODUCT_KEY) REFERENCES PRODUCT_DIM(PRODUCT_KEY))
ALTER TABLE: Alter table statement can be used to perform the followings task.
- Add a new column.
- Modify an existing Column.
- Define a default value for the new column.
- Drop a column
Add a new column lien_code to existing table.
ALTER TABLE DAILY_PIPELINE_LOAN_FACT ADD (lien_code varchar2(5));
ALTER TABLE DAILY_PIPELINE_LOAN_FACT MODIFY (lien_code Varchar2 (10));
ALTER TABLE DAILY_PIPELINE_LOAN_FACT DROP COLUMN lien_code
SET UNUSED OPTION: The set unused option marks one or more column as unused so that they can be dropped when required.
ALTER TABLE DAILY_PIPELINE_LOAN_FACT SET UNUSED (lien_code)
Dropping a table: While we dropped a table
- All data and structure in the table deleted.
- Any pending transactions are committed.
- All indexes are dropped.
- You can not rollback the drop table statement.
- Only the creator of the table or the user with drop any table privilege can drop a table
DROP TABLE DAILY_PIPELINE_LOAN_FACT
Rename table: You can use RENAME TABLE keyword to change the name of the table. To change the name of the table, you must be owner of the table.
RENAME TABLE Daily_pipeline_loan_fact to Dly_pipeline_loan_fct
- Remove all rows from the table.
- Release the storage space used by the table.
- You can not rollback rows removal when using TRUNCATE.
TRUNCATE TABLE daily_pipeline_loan_fact
Data Manipulation Language (DML)
A DML statement is executed When You
Add new rows to a table. Modifying existing rows in a table. Removing existing rows from a table.
Adding a new row to a table: You can insert new rows in a table by using INSERT statement.
INSERT INTO table_name [(Column),(column)….] VALUES (value [, value]…);
- Table_name is the name of the table
- Column is the name of the column in the table to populate
- Value is the corresponding value for the column.
INSERT INTO DAILY_PIPELINE_LOAN_FACT (LOANNO, LOAN_STATUS, LOAN_AMOUNT) VALUES (1001, ‘FUNDED’, 1000)
If you are inserting a new row that contains values for each column, the column list is not required in the INSERT clause .However, if you do not use the column list, the value must be listed according to the default orders of the columns in the table, and a value must be provided for each column.
Use and substitution in a SQL statement to prompt for values
INSERT INTO DAILY_PIPELINE_LOAN_FACT (LOANNO, LOAN_STATUS, LOAN_AMOUNT) VALUES (&LOANNO, ‘& LOAN_STATUS’, & LOAN_AMOUNT)
INSERT INTO COPY_DLY_PIPELINE_LOAN_FACT SELECT * FROM DAILY_PIPELINE_LOAN_FACT
DELETE Statement: You can remove existing rows from a table by using the delete statement.
DELETE [FROM] Table [WHERE condition]
Condition identifies the rows to be deleted.
DELETE FROM daily_pipeline_loan_fact WHERE loan_no=‘1001’ ;
Using the MERGE statements you can update or insert a row conditionally into a table, thus avoiding multiple Update statements. The decision whether to insert or update into the target table is based on a condition in the ON clause.
Because the MERGE command include both INSERT and UPDATE commands , you need to have both INSERT and UPDATE privilege on the target table and SELECT privilege on the source table .
MERGE INTO Table_name AS table_alias USING (table|view|subqueries) AS alias ON (Join condition) WHEN MATCHED THEN UPDATE SET col1=val1 col2=val2 WHEN NOT MATCHED THEN INSERT (column_list) VALUES (Column_values); In
- INTO clause specifies the target table you are updating or inserting.
- USING clause identifies the source of the data to be updated or inserted; can be a table, view, or subquery.
- ON clause the condition upon which the MERGE operation either updates or inserts
- WHEN MATCHED
- WHEN NOT MATCHED Instruct the server how to respond to the result of the join condition.
MERGE INTO copy_dly_pipeline_loan_fact AS c USING daily_pipeline_loan_fact d ON (d.loanno=c.loanno) WHEN MATCHED THEN UPDATE SET c.laon_status=d.loan_status c.loan_amount=d.loan_amount c.update_date=d.update_date c.product_key=d.product_key WHEN NOT MATCHED THEN INSERT VALUES(d. laon_status, d.loan_amount, d.update_date, d.product_key)
Transaction control language (TCL) manages the change made by DML statements.
With commit or rollback statements, you can do the following:
- Ensure data consistency
- Preview data changes before making changes permanent
- Group logical related transaction.
- An automatic COMMIT occurs when:
- o DDL statement is issued
- o DCL statement is issued
- o Normal exit from SQL*PLUS without issuing explicit COMMIT or ROLLBACK
- An automatic ROLLBACK occurs under an abnormal termination of SQL *PLUS or due to system failure
State of data before commit or Rollback
- The previous state of data can be recovered.
- The current user can review the results of the DML operations by using the SELECT statement.
- Other users cannot view the results of the DML statements by the current user.
- The affected rows are locked; other users cannot change the data within the affected row.
State of data after commit
- Data changes are made permanent in the database.
- The previous state of data is permanently lost.
- All users can view the results.
- All save points are erased.
State of data after Rollback
- Discard all pending changes by suing the ROLLBACK statement.
- Data changes are undone
- Previous state of the data is released
You can use GRANT or REVOKE to Grant or remove access to both database and its objects. Collectively known as data control language (DCL) .
Granting system privileges
GRANT create table, create view, Create sequence TO Scott;
Role: A role is a named group of related privileges that can de grant to a user.
Create a Role
CREATE ROLE manager;
Grant privileges to a role
GRANT create table, create view, create sequence TO manager;
Grant a role to users
GRANT manager to Scott, scott1;
Granting object privileges
GRANT SELECT ON daily_pipeline_loan_fact TO scott1;
REVOKE SELECT ON daily_pipeline_loan_fact TO scott1;
The SELECT statement is an SQL statement that specifies which rows and columns to fetch from one or more tables or views. The SELECT statement is very helpful in finding filtered records from a database. If the database happens to be very large, the SELECT statement, used with the right parameters, can work wonders in finding the right information.
The following are the features of a SELECT statement:
- It is used for retrieving data from the database either selectively or collectively.
- Column names can be specified or * may be specified for displaying all columns.
- Calculated columns can be displayed.
- Duplication can be removed.
- Sorting of rows is possible.
- Summary information can be displayed
- SQL statement includes a:
o SELECT clause, which lists the columns to be displayed
o FROM clause, which specifies the table involved
o [WHERE] clause, which limits the number of rows returned
o [GROUP BY] clause, which groups the rows based on specified columns
o [HAVING] clause, which specifies the table involved
o [ORDER BY] clause, which sorts the rows based on specified columns
To select all the columns:
SELECT * FROM daily_pipeline_loan_fact;
To select specific columns:
SELECT loanno, loan_amount FROM daily_pipeline_loan_fact;
The DISTINCT clause:
The default output of a SELECT query is all rows including duplicate rows. The DISTINCT keyword is used to eliminate duplicate rows from the output.
SELECT DISTINCT loan_status FROM daily_pipeline_loan_fact;
- Applies conditions to filter rows
- Appears immediately after the SELECT and FROM clause
- Does not allow alias names
- Works on row levels
SELECT <COLUMN NAME(S)> FROM <Table name> WHERE <CONDITION>
SELECT loanno FROM daily_pipeline_loan_fact WHERE loan amount >10000
- SELECT statement is used to retrieve data from the database either selectively or collectively.
- The WHERE clause is used to apply conditions to filter rows.
- Oracle provides DDL statements for creating, altering, dropping, and truncating tables.
- Oracle provides DML statements to insert, update, and delete rows.
- Constraint is a rule that restricts the values for a column on which it is defined.
- Oracle provides TCL statements to ensure data consistency.
- Grant and revoke privileges and role using DCL statement
Test Your Understanding
- What are the different types of SQL statement?
- What is a constraint?
- What is the difference between unique key and primary key?
- What is the use of MERGE statement?
- What is save point?
- What is a role?
- Which statements are auto commit?
- When there will be an automatic rollback?