SQL FOR ORACLE

SQL for Oracle
RDBMS vs. DBMS

•   SQL – An Overview

§  The original version was developed by San Jose Research Labs.

§  SQL is an ANSI (American National Standards Institute) standard for accessing database systems.

§  It has several parts along with Object Relational Features:

•    Data Definition Language

•    Data Manipulation Language

•    Data Control Language

•    Transaction Control Language

•   Basic Data types

•   Data Manipulation Language

•These statements deal with the manipulation of data in the databases.

•    SELECT

•    INSERT

•    UPDATE

•    DELETE

•   Data Retrieval

•      ‘SELECT’  command is used to query data from the database.

•      Syntax: SELECT  [ALL | DISTINCT] scalar-expression (s)

•                              From Table ( s )

•                                       [ Where conditional-expression ]

•                                       [ Group By column (s)]

•                                       [ Having conditional-expression ]

•                                         [ Order By column (s)];

•   Data Retrieval                                                        Contd..

§  Simple Retrieval

•    Display the part numbers of all the parts supplied.

–  Example, SELECT PNO FROM SALES;

•    Display the different colors of the parts.

–  Example, SELECT COLOR FROM  PARTS;

•    Note: Notice the duplicates of the PNO and COLOR are also displayed in the  results.

•    What do we do to eliminate the duplicate?

§  Retrieval with Duplicate Elimination

•    Display the part numbers of all the parts supplied with redundant duplicates eliminated.

•    Example, Select DISTINCT PNO From Sales;

•    Note: ALL is assumed if neither DISTINCT nor ALL is specified explicitly.

§  Retrieval of Computed Values

•    Display the part number and the weight of that part in grams (part weights are given in the table parts in pounds).

•    Example, Select PNO, 'weight in grams =' , WEIGHT * 454  From Parts ;

–  Note: + (Addition), - (Subtraction), * (Multiplication), / (Division) can be used.

•   Data Retrieval                                                        Contd..

§  Simple Retrieval ( “Select * ” )

•    Display all details of all the Supplier.

–  Example, Select  *  From Supplier;

§  Result: A copy of the entire Supplier table. The above select is Equivalent to:

•    Example: Select SNO,SNAME,STATUS,CITY From Supplier;

§  Note:

•    The * is shorthand for a list of all column names

•    The * is convenient for interactive queries, since it saves keystrokes

•    It’s potentially dangerous in application program since the meaning of “ * ” may change if the SELECT statement is recompiled and some definitional change has occurred in the interim

•    It‘s possible to qualify the “ * ” with the table name

–  Example, Select Supplier.*  From Supplier;…… [ legal]

•   Data Retrieval                                                        Contd..

§  Qualified Retrieval

•    Display supplier numbers for suppliers in Paris;

–  Example, Select SNO From Supplier where CITY='PARIS';

•    In the above query WHERE consists of a simple comparison.

•    We can also have multiple comparisons with more conditional expressions.

•    Display supplier numbers for suppliers in Paris  with status > 20;

–  Example, Select SNO From Supplier where CITY='PARIS'  and STATUS>20;

§  Note:

•    Conditional expressions can be combined together using Boolean operators ( AND, OR and NOT)

•    Parentheses can be used to indicate desired order of evaluation

•   Data Retrieval                                                        Contd..

§  Retrieval with Ordering

•    Display the Supplier numbers and status for suppliers in Paris, in descending order of status.

–  Example: Select SNO, STATUS From Supplier Where CITY = 'PARIS‘ Order By STATUS DESC;

•    Note: With Order By can use either ASC or DESC, and ASC is the Default.

§  Retrieval Using BETWEEN

•    Display the parts whose weight is in the range 16 to 19 inclusive.

–  Example: Select PNO,PNAME,COLOR,WEIGHT,CITY From Parts Where WEIGHT  BETWEEN 16 AND 19;

•    Note: The BETWEEN and NOT BETWEEN condition is just shorthand for a condition involving two individual comparisons (“ANDed”)

•   Data Retrieval                                                        Contd..

§  Retrieval Using IN:

•    Display the Parts whose weight is any one of the following 12, 16, 17.

–  Example, Select PNO,PNAME,COLOR,WEIGHT,CITY From Parts Where WEIGHT IN (12,16,17);

•    Note: The IN and NOT IN condition is just shorthand for a condition involving two individual comparisons ( “ORed”)

§  Retrieval Involving NULL:

•    Display the suppliers where the STATUS is null;

–  Example, Select SNAME From Supplier Where STATUS is NULL;

§  Retrieval Using LIKE:

•    Display all parts whose name starts with a ‘C’.

–  Example, Select PNO,PNAME From Parts Where PNAME LIKE 'C%';

–  SELECT PNO,PNAME From Parts WHERE PNAME LIKE ‘M_LLER’;

–  SELECT * From friends  WHERE phone LIKE  '555-6_6_';

–  SELECT PNO,PNAME From Parts WHERE PNAME  NOT LIKE ‘P%’;

•    Note:

–  The Like operator is used to search for a particular pattern.

–  An underline character (_) represents one space or a character

–  A percent sign (%) represents any number of spaces or characters

•   Data Retrieval                                                        Contd..

§  Retrieval Using Concatenation:

•    Display the project name and the city from the project table.

–  Example: Select PROJNAME  || ' , '  || CITY From Project;

•    Note:

–  Concatenation operator can be added in Retrieval to give a cosmetic look to the result.

–  The strings can be either column names or literals.

§  Retrieval Using Dual:

•    Display the System date.

–  Example: SELECT SYSDATE FROM DUAL;

•    Display the arithmetic value of (10+8)/3.

–  Example: SELECT (10+8)/3 FROM DUAL;

•    Display the name of the current user.

–  Example: Select USER from DUAL;

•    Display the unique number assigned to each user

–  Example: Select UID from DUAL;

•   Data Retrieval                                                        Contd..

•      Note:

•    Dual is a table automatically created by Oracle. It has one column DUMMY     defined as VARCHAR2(1)and contains only one row with a value ‘X’.

•    Is used to compute a value for an expression with the SELECT statement.

•    User, UID, SYSTEM are implicitly defined, we also have ROWNUM which returns the sequence number in which a row was returned when selected from a table. It is used to traverse through a record set, ROWID which displays row’s address for each row in the database, NULL can be used to assign in place of missing values.

•   Data Query Language – Data Retrieval

§  The aggregate functions are applied to each group of rows and a single result row is returned for each group.

§  Major Aggregate functions are COUNT, SUM, MAX, MIN, AVG and      GROUPING: Used with GROUP BY

§  Use of GROUP BY:

•    The Group By clause groups the selected rows based on the value of   expression for each row and returns a single row of summary information   for each group.

§  Display the max quantity supplied by each part.

•    Example: select max (quantity), PNO from sales group by PNO;

§  Display the total number of parts supplied for quantity equal to 50

•    Example: select PNO, count(*) from sales where quantity= 50 group by PNO;

•   Data Query Language – Data Retrieval              Contd..

§  Display the total quantity supplied by each part.

•    Example: select sum (quantity), PNO from sales group by PNO;

§  Use of Having:

•    Display part numbers for all parts supplied by more than one supplier.

–  Example: Select PNO From Sales Group By PNO Having Count(*)>4;

•    Note: It works just like the ’WHERE’ clause except that its logic is only related to results of GROUP BY functions.

•   Data Query Language – Data Retrieval              Contd..

•      Joins:

•    A query in which data is retrieved from more than one table.

•    A join is a query that combines rows from two or more tables, Displays, or materialized Displays.

•    Oracle performs a join whenever multiple tables appear in the query's FROM clause. The query's select list can select any columns from any of these tables.

•    If any two of these tables have a column name in common, then you must qualify all references to these columns throughout the query with table names to avoid ambiguity.

•    Most join queries contain WHERE clause conditions that compare two columns, each from a different table. Such a condition is called a join condition.

•    In addition to join conditions, the WHERE clause of a join query can also contain other conditions that refer to columns of only one table. These conditions can further restrict the rows returned by the join query.

•   Data Retrieval

§  Simple Equijoin:

•    Display all combination of supplier and part information such that the supplier and part are located in the same city.

–  Example: Select Supplier.*,Parts.* From Supplier, Parts where Supplier. CITY = Parts. CITY;

•    Note:

–  An equijoin is a join with a join condition containing an equality operator

–  An equijoin combines rows that have equivalent values for the specified columns

•   Data Definition Language

•      Create Tables:

•    All tables within a database must be created at some point in time.

•      Syntax:   CREATE TABLE table_name

•                     (field1 datatype [ NOT NULL ],

•                      field2 datatype [ NOT NULL ],

•                      field3 datatype [ NOT NULL ],

•                      field4 datatype [NULL]……. )

•    Example: CREATE TABLE ORDERS

              (OWNERID INTEGER NOT NULL,

              ITEMDESIRED CHAR(40) NOT NULL);

•   Tables

§  Example: CREATE TABLE customer
              (cust_id NUMBER(5),
               cust_name VARCHAR2(20),
               cust_phone VARCHAR2(10));

§  The table name is ‘customer’

§  Three columns named ‘cust_id, cust_name, cust_phone’

§  Data types and length of columns have been defined

§  If we enter the above table creation script in SQL*Plus then the table will be created in our 'Default tablespace'

•   Tables                                                                         Contd..

§  The DESC command displays the structure of the table created

•           DESC customer;

•           Name       Null?                  Type
------------------------------- -------- ----
cust_id                            NUMBER(5,0)

•           cust_name                                   VARCHAR2(20)

•           cust_phone                       VARCHAR2(10)

§  Dropping table permanently from the Database.

•      Syntax: DROP TABLE table_name ;

§  Data Dictionary Lookup tables for Tables

•          USER_TABLES

•          USER_TAB_COLUMNS

•   Integrity Constraints

§  Constraints define conditions which Oracle uses to maintain Data Integrity.

§  Constraints

•    Reduce effort in maintaining data in applications

•    Slow down data insertion and updates

§  Constraints may be defined at the

•    Columns level as part of the column definition known as ‘column level’ constraint and / or

•    Table level as part of the CREATE TABLE statement at the end known as ‘table level’ constraint

•    Clauses that constrain multiple columns have to be table level constraints

•   Integrity Constraints                                          Contd..

§  Domain Integrity Constraints

•    NULL / NOT NULL

•    CHECK

§  Entity Integrity Constraints

•    UNIQUE

•    PRIMARY KEY

§  Referential Integrity Constraints

•    FOREIGN KEY

§  Null / Not Null

•    CREATE TABLE customer
(cust_id NUMBER(5) NOT NULL,
cust_name VARCHAR2(20) NOT NULL,
cust_phone VARCHAR2(10) NULL);

•    The NOT NULL in the create table statement is what is called a 'constraint'. The column must contain data or Oracle will not allow the row to be entered or updated.

•    These are column level constraints.

•    Columns with a NOT NULL constraint cannot hold NULL values.

•   Integrity Constraints                                          Contd..

•      Check

•    This constraint checks to see if incoming data meets certain criteria defined in the constraint.

•    A column level CHECK cannot reference other columns and cannot use pseudo-columns such as SYSDATE, UID, CurrVal, NextVal, Level or Rownum.

•    Multiple columns may be referred by Table level CHECK constraints.

•    CREATE TABLE student(

    student_id NUMBER,

    student_name VARCHAR2(35),

    age NUMBER CHECK( age BETWEEN 10 AND 50),

    sex VARCHAR2(1),

    address VARCHAR2(100),

    contact_no VARCHAR2(10)

   

•   Integrity Constraints                                          Contd..

•      Unique

•    A candidate key is a combination of one or more columns which uniquely identify every row of the table.

•    Candidate keys act as UNIQUE constraints.

•    Unique key doesn’t allow duplicates and may be NULL.

•    A table can have more than one unique keys.

•    A key comprising of more than one column known as composite key.

•    Create TABLE match_details (

    match_id NUMBER UNIQUE,

    match_date DATE,

    host_team NUMBER,

    against_team NUMBER,

    venue_played VARCHAR2(35),

    ……

    UNIQUE(match_date, host_team, against_team)

•   Integrity Constraints                                          Contd..

•      Primary Key

•    A primary key is also a candidate key with a few special characteristics

–  A primary key column cannot contain NULL values.

–  A key comprising of more than one column known as composite key.

•    CREATE TABLE  country(

    Country_id NUMBER PRIMARY KEY,

    Country_name VARCHAR2(35));

•    CREATE TABLE player(

    Player_id NUMBER,

    Country_id NUMBER,

    Player_name VARCHAR2(35),

    PRIMARY KEY(player_id, country_id));

•   Integrity Constraints                                          Contd..

•      Foreign Key

•    This is also known as Referential Integrity Constraint.

•    This is a combination of columns whose values are based on the primary key values of another table.

•    The referential integrity constraint can be defined on the same table as well.

•    Create TABLE match_details (

    match_id NUMBER UNIQUE,

    match_date DATE,

    host_team NUMBER REFERENCES COUNTRY(country_id),

    against_team NUMBER REFERENCES COUNTRY(country_id),

    ……  

•    The ON DELETE CASCADE clause tells Oracle to delete dependent rows when the parent row is deleted.

•   Data Insertion

§  The INSERT Statement is used to add new rows into the database.

•           Syntax: Insert into table (column1,column2…)

•                       values (Literal, Literal…..);

§  Single-Row Insert:

•    Inserts a part 7 (city ATHENS, weight 24, name and color at present unknown) to table Parts.

–  Example: INSERT INTO Parts (PNO,CITY,WEIGHT ) VALUES (07,’ATHENS’,24);

§  Single-Row Insert, with Column Names Omitted:

•    Insert a part 8 (name SPROCKET, color PINK, weight 14 and city NICE) to table Parts.

–  Example: INSERT INTO Parts VALUES(8, 'SPROCKET','PINK',14,'NICE');

•   Data Update

§  UPDATE: All the rows in the table that satisfy the condition are updated in accordance with the assignments in the set clause. Update is used to make changes to existing data in the table.

•           Syntax: UPDATE table

•                   SET column=scalar-expression [, column=scalar-expression            …….]

•                   [WHERE condition];

§  Single-Row UPDATE:

•    Change the color of part 2 to yellow, increase its weight by 5, and set its  city to  unknown.

–  Example: Update Parts SET COLOR = ‘YELLOW’, WEIGHT = WEIGHT+ 5, CITY =  NULL WHERE PNO = 2;

§  Multiple-Row UPDATE:

•    Double the status of all suppliers in London.

–  Example: Update Supplier SET STATUS = STATUS * 2  WHERE  CITY = LONDON';

•   Data Deletion

§  DELETE: All the rows in the table that satisfy the condition are deleted. Delete is used to remove the specified data from the table.

•           Syntax: DELETE FROM table [ WHERE condition ] ;

§  Single-Row DELETE:

•    Delete the Supplier with SNO 5

–  Example: DELETE  From Supplier WHERE SNO = 5;

§  Multiple-Row DELETE:

•    Delete all the shipments with quantity greater than 300

–  Example: DELETE From Sales WHERE QUANTITY > 300;

•    Delete all the rows from the table.       

–  Example: DELETE From Sales;

•    Note: Deletes all Shipments but it does not Drop the table, the table is empty.

•   Data Control Language

§  User access to the database is controlled in SQL by granting and / or revoking privileges. These privileges control access to the data as well as to the resources of the database

§  SQL allows two types of privileges

•    System privileges – Extend permission to execute

•    Object privileges – Extend permissions to operate on a named database  object

§  These statements deal with granting and revoking access and privileges to the data structures and resources.

•    GRANT

•    REVOKE

•           Syntax:

•           GRANT privileges {|ON object name}TO username {WITH GRANT OPTION}

•           REVOKE privileges {|ON object name}FROM username

•   Transaction Control Statements

•A transaction is a logical unit of work transactions can occur between any of the following events:

•    Committing changes to the database

•    Rollback

•   Commit

•A commit ends the current transaction and makes it permanent, if any changes made during that transaction.

•    Example:

    statement 1;

    .............;

    .....;

    Commit;

    ........;

    statement7.......;

•   Rollback

§  The Rollback statement does exactly the opposite of commit.

§  It ends the transaction, and undoes any changes made during the transaction.

•    Example:

    Statement1;

    ...................;

    ...................

    Rollback;

    ....................;

•   Savepoint

•Savepoint marks and saves the current point in the processing of a transaction. Used with the rollback statement. Savepoints can undo parts of a transaction.

•    Example:

    Rollback to savepoint ;

    .........;

    .............;

    savepoint1;

    ...........;

    savepoint2;

    Rollback to savepoint1;

•   Exercise

§  Create an Employee Address Table with SSN, First Name, Last Name, Address, City, State as columns. The table created should have the following constraints: SSN as a unique key constraint and the First Name, Last Name, Address, City, State with NOT NULL constraint.

§  Create an Employee Statistics table with EmpID, Salary, Benefits, Position as columns. The table created should have EmpId as the unique or primary key and the Salary, Benefits, Position with NOT NULL constraint.

§  Insert a new row containing values for each column: SSN, First Name, Last Name, Address, City and State.

§  Insert a row with Null values for any two columns.

§  Update any existing row with new values for First Name, Last Name, Address, City and State having a SSN as 10.

§  Update an existing row with new values for only City having SSN as 12.

•   Exercise                                                                     Contd..

§  Update the Salary in the Employee Statistics Table who have Benefits value more than 10000.

§  Update the Salary from the Employee Statistics Table who have Position value as Manager.

§  Delete all the employees who do not have position value as Manager.

§  Delete an employee from the Employee Address Table having a SSN as 5.

§  Display all the records in Employee Address Table.

§  Display the employee SSN with position of the employee.

§  Display the available positions.

§  Display the employee details of employees who are from ‘Ohio’ state.

§  Display First Name of employees who are from ‘Ohio’ state and Last Name is ‘Scott’.

•   Exercise                                                                     Contd..

§  Display the Emp details in the ascending order of their Benefits.

§  Display the Emp details in the descending order of their Salary.

§  Display Emp ID No, salary and position of employees whose salary lies in the range: 40000 - 60000.

§  Display Emp ID No, salary and position of employees whose Benefits is less than 12000.

§  Display Emp ID No, salary and position of employees whose Benefits is are not in the range: 10000 – 15000.

§  Display Emp SSN, First Name of employees who are from ‘Howard’ or ‘Paris’ city.

§  Display the Emp SSN and Last name of employees who do not have a First Name.

§  Display records of employees whose Last Name contains a ‘o’.

§  Display records of employees whose Last Name ends with a ‘t’.

§  Display records of employees whose Last Name has a ‘n’ in the 3rd position.

•   Exercise                                                                     Contd..

§  Display Emp ID No and position of employees in the following format: Employee 010 is a Manager.

§  Display Emp ID No and Benefits of employees who are not managers.

§  Display EMP ID of employees whose Benefits is more than 12000 and salary is more than 65000.

§  Display EMP ID of employees whose Benefits is more than 15000 or salary is less than 40000.

§  Display Emp ID No and Salary of employees in dollars (Assume 1$ = Rs.50)

§  Display the Employee ID No, Salary from the Employee Statistics Table with the Position ‘Manager’.

§  Display the Total salary from Employee Statistics Table for each specific Position.

•     Equijoin Exercise                                                                     Contd..

§  Create an Antique Owners Table with Owner ID, Owner Last Name, Owner First Name as columns. The table created should have the following constraints: Owner ID as a primary key. Insert data into the above created table.

§  Create an Antiques table with Seller ID, Buyer ID, item as columns. The table created should have Seller ID, Buyer ID as foreign Keys to the primary key of Antique Owners table (one has to be an Antique Owner before one can buy or sell any items). Insert data into this table.

§  Perform equi join on these 2 tables that have equivalent values for the specified columns.

•    Display the buyer names who have bought chairs

•    Display the buyer names who have sold chairs