Database Management Systems II

Structured Query Language ( tutorial: http://www.w3schools.com/sql/default.asp)

Elmasri/Navathe (3rd. ed.)	Kroenke Book (7th ed.)	McFadden (5th ed.)
Chapter 8	Chapter 9	Chapter 9

Structured Query Language

• SQL was first implemented in IBM's System R in the late 1970's.
• SQL is the de-facto standard query language for creating and manipulating data in relational databases.
• Some minor syntax differences, but the majority of SQL is standard across MS Access, Oracle, Sybase, Informaix, etc.
• SQL is either specified by a command-line tool or is embedded into a general purpose programming langauge such as Cobol, "C", Pascal, etc.
• SQL is a standardized language monitored by the American National Standards Institute (ANSI) as well as by National Institute of Standards (NIST).
• ANSI 1990 - SQL 1 standard
• ANSI 1992 - SQL 2 Standard (sometimes called SQL-92)
• SQL 3 is in the works - adds some Object oriented concepts
• SQL has two major parts:

1.      Data Definition Language (DDL) Used to create (define) data structures such as tables, indexes, clusters

2.      Data Manipulation Language (DML) is used to store, retrieve and update data from tables.

SQL Data Types

• Each implementation of SQL uses slightly different names for the data types.

Numeric Data Types

• Integers: INTEGER, INT or SMALLINT
• Real Numbers: FLOAT, REAL, DOUBLE, PRECISION
• Formatted Numbers: DECIMAL(i,j), NUMERIC(i,j)

Character Strings

• Two main types: Fixed length and variable length.
• Fixed length of n characters: CHAR(n) or CHARACTER(n)
• Variable length up to size n: VARCHAR(n)

Date and Time

• Note: Implementations vary widely for these data types.
• DATE
  Has 10 positions in the format: YYYY-MM-DD
• TIME
  Has 8 positions in the format: HH:MM:SS
• TIME(i)
  Defines the TIME data type with an additional i positions for fractions of a second. For example:
  HH:MM:SS:dd
• Offset from UTZ. +/- HH:MM
• TIMESTAMP
• INTERVAL
  Used to specify some span of time measured in days or minutes, etc.
• Other ways of expressing dates:
• Store as characters or integers with Year, Month Day:
  19972011
• Store as Julian date:
  1997283
• Both MS Access and Oracle store date and time information together in a DATE data type.

Comparing Data Types

	Character	Number	Date	BLOB
MS Access	TEXT	NUMBER	DATE	MEMO or OLE OBJECT
Oracle	VARCHAR2	NUMBER or INTEGER	DATE	LONG or LONG RAW
Sybase	char	float or int	date	blob
IBM DB2	VARCHAR	DECIMAL or SMALLINT	DATE	BLOB, CLOB or DBLOB
MS SQL Server 7	VARCHAR or TEXT	DECIMAL, REAL or INT	DATETIME	image

Many commercial databases now have provisions for User Defined Types (UDTs).

Data Definition Language

• DDL is used to define the schema of the database.
• Create a database schema
• Create a domain
• Create, Drop or Alter a table
• Create or Drop an Index
• Define Integrity constraints
• Define access privileges to users
• Define access privileges on objects
• SQL2 specification supports the creation of multiple schemas per database each with a distinct owner and authorizaed users.

Creating Domains

• Goal is to create a standard format for data type and size and give this a name.
• Good for standardization across all tables.
• CREATE DOMAIN d_last_name AS VARCHAR(30)
• CREATE DOMAIN d_gender AS VARCHAR(1)
• CREATE DOMAIN d_salary AS NUMBER(12,2)
• CREATE DOMAIN d_soc_sec AS VARCHAR(11)

Note that Oracle, MS Access and MS SQL Server do not support creating domains.

Creating a Schema

Note: To try out these SQL examples in MS Access, go to the Queries form and choose New, then choose Design View and then close the next dialog box. Under the View menu, choose SQL. From this point, you can type in any SQL statemnt and execute it (click on the Exclamation point icon). Note that MS Access's DDL syntax is extremely limited. Most of the DDL statments below (including domains, NOT NULL constraints and referential integrity constraints) are not supported.

• Creating a Table:

·                CREATE TABLE employee (

·                    Last_Name      d_last_name  NOT NULL,

·                    First_name     VARCHAR(18)  NOT NULL,

·                    Soc_Sec        d_soc_sec    NOT NULL,

·                    Date_of_Birth  DATE,

·                    Salary         d_salary

·                ) ;  

·                 

·                CREATE TABLE dependant (

·                    Last_Name        d_last_name  NOT NULL,

·                    First_name       VARCHAR(18)  NOT NULL,

·                    Soc_Sec          d_soc_sec    NOT NULL,

·                    Date_of_Birth    DATE,

·                    Employee_Soc_Sec d_soc_sec NOT NULL

·                );

·                 

• Note: When naming tables, columns and other database objects, do not include spaces or puncuation other than the underscore _ in the names. For example, do not call the last name column: Last Name
  If you wish to separate words in a name, use the underscore character.
• Specifying Primary and Foreign keys:

·                CREATE TABLE order_header (

·                   order_number    NUMBER(10,0) NOT NULL,

·                   order_date      DATE,

·                   sales_person    VARCHAR(25),

·                   bill_to         VARCHAR(35),

·                   bill_to_address VARCHAR(45),

·                   bill_to_city    VARCHAR(20),

·                   bill_to_state   VARCHAR(2),

·                   bill_to_zip     VARCHAR(10),

·                   PRIMARY KEY (order_number)

·                );

·                 

·                CREATE TABLE order_items (

·                   order_number     NUMBER(10,0) NOT NULL,

·                   line_item        NUMBER(4,0)  NOT NULL,

·                   part_number      VARCHAR(12)  NOT NULL,

·                   quantity         NUMBER(4,0),

·                   PRIMARY KEY (order_number, line_item),

·                   FORIEGN KEY (order_number)

·                      REFERENCES order_header (order_number),

·                   FOREIGN KEY (part_number)

·                      REFERENCES parts (part_number)

·                );

·                 

·                CREATE INDEX order_index

·                    ON order_header (order_number) ASC ;

·                 

·                CREATE INDEX items_index

·                    ON order_items (order_number, line_item) ASC ;

·                 

·                 

·                Example from MS Access:

·                CREATE TABLE employee (

·                   FirstName TEXT, 

·                   LastName  TEXT, 

·                   ssn       INTEGER 

·                     CONSTRAINT ssnConstraint PRIMARY KEY

·                );

·                 

·                CREATE INDEX employee_index

·                 ON employee (ssn) ;

·                 

Specifying Constraints on Columns and Tables

• Constraints on attributes:
• NOT NULL - Attribute may not take a NULL value
• DEFAULT - Store a given default value i no value is specified
• PRIMARY KEY - Indicate which attribute(s) form the primary key
• FOREIGN KEY - Indicate which attribute(s) form a foreign key.
  This enforces referential integrity
• UNIQUE - Indicates which attribute(s) must have unique values.
• Specify when constraint should be enforced:
• Immediate
• Deferrable until commit time
• Referential Integrity Constraint: Specify the behavior for child tuples when a parent tuple is modified.
• Action to take if referential integrity is violated:
• SET NULL - Child tuples foreign key is set to NULL - Orphans.
• SET DEFAULT - Set the value of the foreign key to some default value.
• CASCADE - Child tuples are updated (or deleted) accordiong to the action take on the parent tuple.
• Examples of ON DELETE and ON UPDATE

·                CREATE TABLE order_items (

·                   order_number     NUMBER(10,0) NOT NULL,

·                   line_item        NUMBER(4,0)  NOT NULL,

·                   part_number      VARCHAR(12)  NOT NULL,

·                   quantity         NUMBER(4,0),

·                   PRIMARY KEY (order_number, line_item),

·                   FORIEGN KEY (order_number) 

·                      REFERENCES order_header (order_number)

·                      ON DELETE SET DEFAULT 

·                      ON UPDATE CASCADE,

·                   FOREIGN KEY (part_number)

·                      REFERENCES parts (part_number)

·                );

• Constraints can also be given names so that they can later be modified or dropped easily.

·                CREATE TABLE order_header (

·                   order_number    NUMBER(10,0) NOT NULL,

·                   order_date      DATE,

·                   sales_person    VARCHAR(25),

·                   bill_to         VARCHAR(35),

·                   bill_to_address VARCHAR(45),

·                   bill_to_city    VARCHAR(20),

·                   bill_to_state   VARCHAR(2),

·                   bill_to_zip     VARCHAR(10),

·                   CONSTRAINT order_header_pk

·                      PRIMARY KEY (order_number)

·                );

·                 

·                CREATE TABLE order_items (

·                   order_number     NUMBER(10,0) NOT NULL,

·                   line_item        NUMBER(4,0)  NOT NULL,

·                   part_number      VARCHAR(12)  NOT NULL,

·                   quantity         NUMBER(4,0),

·                 

·                   CONSTRAINT order_items_pk

·                       PRIMARY KEY (order_number, line_item),

·                 

·                   CONSTRAINT order_items_fk1

·                      FORIEGN KEY (order_number)

·                         REFERENCES order_header (order_number)

·                         ON DELETE SET DEFAULT 

·                         ON UPDATE CASCADE,

·                 

·                   CONSTRAINT order_items_fk2

·                      FOREIGN KEY (part_number)

·                         REFERENCES parts (part_number)

·                         ON DELETE SET DEFAULT 

·                         ON UPDATE CASCADE

·                );

Note that once you have successfully issued a CREATE TABLE statement, the table definition is created in your schema. If you issue a similar CREATE TABLE statement with the same table name, you will get an error that "...the table already exists." In this case, you would need to first DROP TABLE, then create it again.

In Oracle SQL*Plus, to see the tables you have created in your schema, issue the following query:
SELECT * FROM TAB;

Removing Schema Components with DROP

• DROP SCHEMA schema_name CASCADE
  Drop the entire schema including all tables. CASCADE option deletes all data, all tables, indexes, domains, etc.
• DROP SCHEMA schma_name RESTRICT
  Removes the schema only if it is empty.
• DROP TABLE table_name
  Remove the table and all of its data.
• DROP TABLE table_name CASCADE
  Remove the table and all related tables as specified by FOREIGN KEY constraints.
• DROP TABLE table_name RESTRICT
  Remove the table only if it is not referenced (via a FORIEGN KEY constraint) by other tables.
• DROP INDEX index_name
  Removes an index.
• DROP CONSTRAINT table_name.constraint_name
  Removes a constraint from a table.

Changing Schema Components with ALTER

• Changing Attributes:
  ALTER TABLE student ALTER last_name VARCHAR(35);
  ALTER TABLE student ALTER gpa DROP DEFAULT
  ALTER TABLE student ALTER gpa SET DEFAULT 0.00;
• Adding Attributes:
  ALTER TABLE student ADD admission DATE;
• Removing Attributes (not widely implemented):
  ALTER TABLE student DROP home_phone;

Note that in Oracle, you can not drop a column from a table using SQL commands. You must copy the table to a temporary table (minus the unwanted column), drop the original table and then copy the temporary table back to the original name.

Data Manipulation Language

• DDL is used to create and specify the schema. DML is then used to manipulate (select, insert, update, delete) data.

Inserting Data into Tables

• General syntax:

·                INSERT INTO tablename (column1, column2, ... columnX)

·                       VALUES (val1, val2, ... valX);

·                 

·                INSERT INTO employee (first_name, last_name, street, city, state, zip)

·                       VALUES ("Buddy", "Rich", "123 Sticks Ln.", "Fillville", "TN", "31212");

·                 

·                INSERT INTO stocks (symbol, close_date, close_price)

·                       VALUES ("IBM", "03-JUN-94", 104.25);

·                 

·                INSERT INTO student_grades (student_id, test_name, score, grade)

·                       VALUES (101, "Quiz 1", 88, "B+");

 

• Quotes are placed around the data depending on the Data type and on the specific RDBMS being used:

RDBMS	Text Data Type	Dates
MS Access	TEXT: Either " or '	DATETIME: Either " or '
Oracle	VARCHAR: '	DATE: '
IBM DB2	'	DATE: '
Sybase	CHAR and VARCHAR: "	DATE: "

Retrieving Data from Tables with Select

• Main way of getting data out of tables is with the SELECT statement.
• Example syntax:

·                SELECT   column1, column2, ... columnN

·                FROM     tableA, tableB, ... tableZ

·                WHERE    condition1, condition2, ...conditionM

·                GROUP BY column1, ...

·                HAVING   condition

·                ORDER BY column1, column2, ... columnN

Assume an employees table:
employees(emplyee_id, first_name, last_name, street, city, state, zip)

Some example queries:

SELECT   employee_id, last_name, first_name

FROM     employees

WHERE    last_name = "Smith"

ORDER BY first_name DESC

SELECT   employee_id, last_name, first_name

FROM     employees

WHERE    salary > 40000

ORDER BY last_name, first_name DESC

SELECT   *

FROM     employees

ORDER BY 2;

SELECT   symbol, close_price

FROM     stocks

WHERE    close_date > "01-JAN-95" AND

         symbol = "IBM"

ORDER BY close_date

SELECT    symbol, close_date, close_price

FROM      stocks

WHERE     close_date >= "01-JAN-95"

ORDER BY  symbol, close_date

Relational Operators and SQL

• Relational operators each have implementations in SQL.
• _{employee_id, last_name, first_name} ( _{salary > 40000} (EMPLOYEE) )

·                        SELECT employee_id, last_name, first_name

·                        FROM   employee

·                        WHERE  salary > 40000 

• _{AVG (salary)} ( _{state = 'NJ'} (EMPLOYEE) )

·                        SELECT AVG(salary)

·                        FROM   employee

·                        WHERE  state = 'NJ'

• _{last_name = 'Smith' state = 'NY'} (EMPLOYEE)

·                        SELECT *

·                        FROM   employee

·                        WHERE  last_name = 'Smith' AND state = 'NY'

SQL Built-in Functions

Example Table GRADES:

Name    Major       Grade

Bill    CIS         95

Mary    CIS         98

Sue     Marketing   88

Tom     Finance     92

Alex    CIS         79

Sam     Marketing   89

Jane    Finance     83

...

Note: To try out these examples, create the table in MS Access and enter the data shown above. Go to the Queries form and choose New, then choose Design View and then close the next dialog box. Under the View menu, choose SQL.

• Average grade in the class:

·                  SELECT AVG(grade)

·                  FROM   students;

·                 

·                Results:

·                AVG(GRADE)

·                ----------

·                89.1428571

• Give the name of the student with the highest grade in the class:
  This is an example of a subquery

·                SELECT name, grade 

·                FROM   students

·                WHERE  grade = 

·                  (  SELECT MAX(grade) FROM students

·                  );

·                 

·                 

·                Results:

·                 

·                NAME           GRADE

·                -------------- -----

·                Mary           98

·                 

• Show the students with the highest grades in each major:

·                SELECT    name, major, grade

·                FROM      students s1

·                WHERE     grade = 

·                   (

·                     SELECT max(grade) 

·                     FROM   students s2 

·                     WHERE  s1.major = s2.major

·                   )

·                ORDER BY grade DESC;

·                 

·                 

·                Results:

·                 

·                NAME          MAJOR                GRADE

·                ------------- -------------------- -----

·                Mary          CIS                  98

·                Tom           Finance              92

·                Sam           Marketing            89

Note the two aliases given to the students table: s1 and s2. These allow us to refer to different views of the same table.

Selecting from 2 or More Tables

• In the FROM portion, list all tables separated by commas. Called a Join.
• The WHERE part becomes the Join Condition

·                Example table EMPLOYEE:

·                Name     Department    Salary

·                Joe      Finance       50000

·                Alice    Finance       52000

·                Jill     MIS           48000

·                Jack     MIS           32000

·                Fred     Accounting    33000

·                 

·                 

·                Example table DEPARTMENTS:

·                Department      Location

·                Finance         NJ

·                MIS             CA

·                Accounting      CA

·                Marketing       NY

• List all of the employees working in California:

·                SELECT  employee.name

·                FROM    employee, department

·                WHERE   employee.department = department.department

·                AND     department.location = 'CA';

·                 

·                Results:

·                NAME

·                --------------------------------

·                Jill

·                Jack

·                Fred

• List each employee name and what state (location) they work in. List them in order of location and name:

·                SELECT    employee.name, department.location

·                FROM      employee, department

·                WHERE     employee.department = department.department

·                ORDER BY  department.location, employee.name;

·                 

·                Results:

·                NAME             LOCATION

·                ---------------  -------------

·                Fred             CA

·                Jack             CA

·                Jill             CA

·                Alice            NJ

·                Joe              NJ

This is similar to a LEFT JOIN.

• List each department and all employees that work there. Show the department and location even if no employees work there.

·                SELECT    department.department, department.location,

·                          employee.name

·                FROM      employee RIGHT JOIN department

·                ON        employee.department = department.department

·                ORDER BY  department.location, employee.name;

·                 

·                 

·                Results:

·                DEPARTMENT         LOCATION           NAME

·                -------------      ----------------   ----------------

·                Accounting         CA                 Fred

·                MIS                CA                 Jack

·                MIS                CA                 Jill

·                Finance            NJ                 Alice 

·                Finance            NJ                 Joe

·                Marketing          NY                 NULL 

• What is the highest paid salary in California ?

·                SELECT MAX(employee.salary) 

·                FROM   employee, department

·                WHERE  employee.department = department.department

·                AND    department.location = 'CA';

·                 

·                Results:

·                    MAX(SALARY) 

·                    ------------

·                    48000

• Cartesian Product of the two tables:

·                SELECT *

·                FROM    employee, department;

·                 

·                 

·                Results:

·                Name       employee.Departmen Salary  Department.Dep Location 

·                 Joe       Finance            50000   Finance        NJ       

·                 Joe       Finance            50000   MIS            CA       

·                 Joe       Finance            50000   Accounting     CA       

·                 Joe       Finance            50000   Marketing      NY       

·                 Alice     Finance            52000   Finance        NJ       

·                 Alice     Finance            52000   MIS            CA       

·                 Alice     Finance            52000   Accounting     CA       

·                 Alice     Finance            52000   Marketing      NY       

·                 Jill      MIS                48000   Finance        NJ       

·                 Jill      MIS                48000   MIS            CA       

·                 Jill      MIS                48000   Accounting     CA       

·                 Jill      MIS                48000   Marketing      NY       

·                 Jack      MIS                32000   Finance        NJ       

·                 Jack      MIS                32000   MIS            CA       

·                 Jack      MIS                32000   Accounting     CA       

·                 Jack      MIS                32000   Marketing      NY       

·                 Fred      Accounting         33000   Finance        NJ       

·                 Fred      Accounting         33000   MIS            CA       

·                 Fred      Accounting         33000   Accounting     CA       

·                 Fred      Accounting         33000   Marketing      NY       

• In which states do our employees work ?

·                SELECT    DISTINCT location

·                FROM      department;

• From our Bank Accounts example.
  List the Customer name and their total account holdings:

·                SELECT   customers.LastName, Sum(Balance) 

·                FROM     customers, accounts

·                WHERE    customers.CustomerID = accounts.customerid

·                GROUP BY customers.LastName

·                 

·                Results:

·                 

·                LASTNAME    SUM(BALANCE)

·                ---------   ------------

·                Axe         $15,000.00 

·                Builder     $1,300.00 

·                Jones       $1,000.00 

·                Smith       $6,000.00 

• We can also use a Column Alias to change the title of the columns

·                SELECT   customers.LastName, Sum(Balance) AS TotalBalance

·                FROM     customers, accounts

·                WHERE    customers.CustomerID = accounts.customerid

·                GROUP BY customers.LastName

·                 

·                Results:

·                 

·                LASTNAME    TotalBalance

·                ---------   ------------

·                Axe         $15,000.00 

·                Builder     $1,300.00 

·                Jones       $1,000.00 

·                Smith       $6,000.00 

• Here is a combination of a function and a column alias:

·                SELECT name, department,

·                       salary  AS CurrentSalary,

·                       (salary * 1.03)  AS ProposedRaise

·                FROM   employee;

·                 

·                Results:

·                 

·                name      department    CurrentSalary  ProposedRaise

·                --------  ------------  -------------  -------------

·                Alice     Finance       52000          53560 

·                Fred      Accounting    33000          33990 

·                Jack      MIS           32000          32960 

·                Jill      MIS           48000          49440 

·                Joe       Finance       50000          51500 

·                 

·                 

Recursive Queries and Aliases

• Recall some of the E-R diagrams and relations we dealt with had a recursive relationship.
• For example: A student can tutor one or more other students. A student has only one tutor.
  STUDENTS (StudentID, Name, Student_TutorID)

StudentID	Name	Student_TutorID
S101	Bill	NULL
S102	Alex	S101
S103	Mary	S101
S104	Liz	S103
S105	Ed	S103
S106	Sue	S101
S107	Petra	S106

• Provide a listing of each student and the name of their tutor:

·                SELECT   s1.name AS Student,  tutors.name AS Tutor

·                FROM     students s1,  students tutors

·                WHERE    s1.student_tutorid = tutors.studentid;

·                 

·                 

·                Results:

·                 

·                Student       Tutor 

·                ----------    ----------

·                Alex          Bill 

·                Mary          Bill 

·                Sue           Bill 

·                Liz           Mary 

·                Ed            Mary 

·                Petra         Sue 

• The above is called a "recursive" query because it access the same table two times.
• We give the table two aliases called s1 and tutors so that we can compare different aspects of the same table.
• However, as is, the table is missing something: We don't see who is tutoring Bill Smith. Use LEFT JOIN:

·                SELECT   s1.name AS Student,  tutors.name AS Tutor

·                FROM     students s1   LEFT JOIN   students tutors

·                ON       s1.student_tutorid = tutors.studentid;

·                 

·                Results:

·                 

·                Student       Tutor 

·                ----------    ----------

·                Bill 

·                Alex          Bill 

·                Mary          Bill 

·                Sue           Bill 

·                Liz           Mary 

·                Ed            Mary 

·                Petra         Sue 

• Here is one more twist: Suppose we were interested in those students who do not tutor anyone? Use RIGHT JOIN
• How many students does each tutor work with ?

·                SELECT   s1.name AS TutorName,

·                         COUNT(tutors.student_tutorid) AS NumberTutored

·                FROM     students s1, students tutors

·                WHERE    s1.studentid = tutors.student_tutorid

·                GROUP BY s1.name;

·                 

·                Results:

·                 

·                TutorName   NumberTutored

·                ----------  -------------

·                Bill Smith              3

·                Mary Green              2

·                Sue Carr                1

WHERE Clause Expressions

• There are a number of expressions one can use in a WHERE clause.
• Typical Logic expressions:
  COLUMN = value
  Also:

·                   <    >    =   !=    <=    >=

Also consider BETWEEN

SELECT name, grade, "You Got a B"

FROM   students

WHERE  grade between 81 and 90

• Subqueries using = (equals):

·                SELECT name, grade 

·                FROM   students

·                WHERE  grade = 

·                  (  SELECT MAX(grade) FROM students

·                  );

This assumes the subquery returns only one tuple as a result.
Typically used for aggregate functions.

• Subqueries using IN:

·                SELECT    name

·                FROM      employee 

·                WHERE     department IN ('Finance', 'MIS');

·                 

·                 

·                SELECT    name

·                FROM      employee 

·                WHERE     department IN 

·                          (SELECT department

·                           FROM departments

·                           WHERE location = 'CA');

In the above case, the subquery returns a set of tuples. The IN clause returns true when a tuple matches a member of the set.

• Subqueries using EXISTS:

·                SELECT   name, salary

·                FROM     employee

·                WHERE    EXISTS

·                         (SELECT  name 

·                          FROM    EMPLOYEE e2

·                          WHERE   e2.salary > employee.salary)

·                     AND EXISTS

·                         (SELECT  name 

·                          FROM    EMPLOYEE e3

·                          WHERE   e3.salary < employee.salary)

·                 

·                Results:

·                name          salary

·                -----------   ---------- 

·                Joe           50000 

·                Jill          48000 

·                Fred          33000 

·                 

The above query shows all employees names and salaries where there is at least one person who makes more money (the first exists) and at least one person who makes less money (second exists).

• NOT EXISTS:

·                SELECT   name, salary

·                FROM     employee

·                WHERE    NOT EXISTS

·                         (SELECT  name 

·                          FROM    EMPLOYEE e2

·                          WHERE   e2.salary > employee.salary)

·                 

·                Results:

·                name        salary       

·                ---------   ----------

·                Alice       52000 

·                 

·                 

The above query shows all employees for whom there does not exist an employee who is paid less.

• LIKE operator:
  Use the LIKE operator to perform a partial string match. Generally, the % character is used as the wild card although in some DBMS, the * character is used.

Show all employees whose name starts with 'S'

SELECT   name, salary 

FROM     employee

WHERE    name LIKE  'S%';

Show all employees whose name contains the letters 'en'

SELECT   name, salary 

FROM     employee

WHERE    name LIKE  '%en%';

Note that chatacters within quotes are case sensitive.

Show all employees whose name contains the letter 'e' and the letter 'n' in that order:

SELECT   name, salary 

FROM     employee

WHERE    name LIKE  '%e%n%';

Show all employees whose name contains the letter 'e' and the letter 'n' in any order:

SELECT   name, salary 

FROM     employee

WHERE    name LIKE  '%e%n%' OR

         name LIKE  '%n%e%';

Deleting Tuples with DELETE

• DELETE is used to remove tuples from a table.
• With no WHERE clause, DELETE will remove all tuples from a table.
• Remove all employees:

·                  DELETE employee;

• Remove only employees making more than $50,000

·                  DELETE employee

·                  WHERE  salary  > 50000;

• Remove all employees working in California:

·                  DELETE employee

·                  WHERE  department IN

·                    (SELECT department

·                     FROM   department

·                     WHERE  location = 'CA');

• DELETE will not be succesful if a constraint would be violated.
  For exmaple, consider the department attribute in the Employee table as a Foreign Key.
  Removing a department would then be contingent upon no employees working in that department.
  This is what we call enforcing Referential Integrity

Change Values using UPDATE

• The UPDATE command is used to change attribute values in the database.
• UPDATE uses the SET clause to overwrite the value.
• Change the last name of an Employee:

·                UPDATE employee

·                SET    last_name = 'Smith'

·                WHERE  employee_id = 'E1001';

• Give an Employee a raise:

·                UPDATE employee

·                SET    salary = salary * 1.05

·                WHERE  employee_id = 'E1001';

Defining Views

• It is possible to define a particular view of a table (or tables).
• For example, if we commonly access just 2 or 3 columns in a table, we can define a view on that table and then use the view name when specifying queries.
  Assume an employees table:
  employees(emplyee_id, first_name, last_name, street, city, state, zip, department, salary)

·                CREATE VIEW emp_address AS

·                       SELECT first_name, last_name, street, 

·                              city, state, zip

·                       FROM   employee;

·                 

·                 

·                CREATE VIEW emp_salary AS

·                       SELECT first_name, last_name, salary

·                       FROM employee;

·                 

·                 

·                CREATE VIEW avg_sal_dept AS

·                       SELECT    department, AVG(salary)

·                       FROM      employee

·                       GROUP BY  department;

• One can then query these views as if they were tabes

·                SELECT   * 

·                FROM     emp_address

·                ORDER BY last_name;

·                 

·                SELECT  *

·                FROM    avg_sal_dept

·                WHERE   department = 'Finance';

·                 

 

How is Oracle implemented on Windows NT and 2000?

Oracle on Windows NT and 2000 is running as a service that is implemented as a single multi-threaded process:

Services: Oracle on Windows NT and 2000 doesn't run as typical Windows processes, but as a Windows service. A service is basically a background process that is independent of who is using the console or system at any particular time. Services can be started at system boot time and can be compared to Unix daemon processes. To check that the required Oracle services are up and running, go to CONTROL PANEL/ SERVICES (or type "net start | more" from a DOS console window).

Multi Threaded: Unlike on Unix, the Oracle instance on NT and 2000 is implemented as a single operating system process. This process include threads that implement each of the tasks required for the instance to function. Therefore, there is a thread for each of the background (lgwr, dbwr, arch, etc.) and server tasks, plus a two-thread overhead. Because each thread shares the same memory space, there is no need to implement the SGA in shared memory. All memory it is available to all threads within the process.

·  Back to top of file

Should one use NT Workstation, NT Server or Windows 2000 to run Oracle?

Windows NT comes in two flavours: Workstation and Server. These terms are highly misleading, suggesting that a machine with server functions should run NT Server. That is not the case at all. The distinction between Workstation and Server is only important to NT itself. The Server product includes the NT domain administration tools and services. If you have more than one NT machine in a network you will probably want one of them to be an NT Server machine to act as the domain controller, but you probably DON'T want to run the Oracle database server on that machine.

The Oracle database server and tools will run equally well on an NT "Workstation" machine as on an NT "Server" machine, unless the Server machine is the Domain Controller for a busy domain, in which case it would be a poor choice to host a busy database server as well.

The same applies to the different Windows 2000 releases.

·  Back to top of file

How does one use the Oracle supplied command line utilities?

Most oracle command line utilities are stored in the %ORACLE_HOME%\bin subdirectory. Some of the supplied command line utilities are: sqlplus, svrmgrl, imp, exp, sqlldr, etc.

For Oracle releases below 8i, these command will have a XX suffix (eg svrmgr30), where XX is the utility's version number.

Before using any of these utilities, set the required environment variables. Look at this example:

·  Back to top of file

How does one create a new database on NT/ 2000?

One can create a "starter" database as part of the installation process. If you need to create any additional databases, one can use the Oracle Database Configuration Assistant (DBAssist) GUI utility.

One can also create a new database from the Console Window. Start by generating some sample scripts using the Database Configuration Assistant. Modify them according to your standards, and run them to create your database.

·  Back to top of file

What Oracle services must be running on a NT/2000 box?

For Oracle 8i and above, one can start the database by starting the OracleServiceSID service. For Oracle versions below 8i one should see at least two services - OracleStartSID and OracleServiceSID.

Some of the other optional services are:

• Net8 listener service (OracleTNSListener)
• Intelligent Agent Service (OracleAgent)
• Oracle Data Gatherer (OracleDataGatherer)
• Oracle OEM Management Server (OracleManagementServer)
• etc.

·  Back to top of file

How can I automatically start Oracle on NT/2000?

To start a database automatically when your server boots up, ensure the following registry setting is enabled: ORA_sid_AUTOSTART.

One can also specify autostart when creating new database services. Verify if this was done by opening Control Panel -> Services (Control Panel -> Administrative Tools -> Services on Windows 2000) and check if it is set to AUTOMATIC.

You may need to recreate the services mentioned above using the "oradim" (Instance Manager) utility. Start by opening a DOS command console window. Look at this example (assume a SID of TEST):

·  Back to top of file

Can one start and stop services from a batch job?

Issue commands similar to these from a batch file:

To stop the services, use net stop instead of net start.

These commands are handy if you need to shut down the database for a cold (offline) backup. Do not put this batch file in your Windows startup group as the database will then only start when someone logs onto the server. The database will not start if the server is re-booted and waits on the log-on screen.

The following registry entries are also handy to prevent a SHUTDOWN ABORT when the OracleServiceSID service is stopped:

·  Back to top of file

How does one kill Oracle processes on NT/2000?

One normally kills Oracle processes using the "alter system kill session" command. One can also use the orakill.exe command that comes with Oracle for NT/2000 to kill a thread. You need to specify the thread ID in Hex. Try orakill /? or orakill /h for more details. Example:

·  Back to top of file

How does one take a hot database backup on NT/2000?

You need to use the Oracle OCOPY.EXE utility on Windows NT/2000 to backup open database files (e.g. do a hotbackup).

The NTBACKUP command will not backup open files. Also, OCOPYxx.exe (e.g. OCOPY80.EXE distributed with Oracle8.0) will not backup files to tape, only to another disk location. Once the hotbackup to disk is complete, you can use the NTBACKUP utility to backup the files to tape.

Note 1: If you perform a hot backup your databases must run in archivelog mode, otherwise shutdown and do a cold backup. Read the Oracle Backup and Recovery FAQ for more details.

·  Back to top of file

Can I schedule backups and maintenance jobs on NT/2000?

Yes, make sure the SCHEDULE service is running (control panel/ services/ schedule). You can now schedule jobs by issuing the AT command. Eg:

To get a list of scheduled jobs, execute the AT command without any parameters: Eg:

For more info, type AT /?

Also note that several alternatives exist to the AT command. For example, there is a GUI utility in the NT Resource Kit CD-ROM for scheduling jobs. This utility will (supposedly) become part of the OS with version 5.0. It's a lot more convenient to use than its command-line counterpart AT.

·  Back to top of file

How can I fool Oracle to think a remote database is local?

Some products, like Oracle WebDB, require a local Oracle database. You can direct all connect requests to a TNSNAMES.ORA connect descriptor by setting the LOCAL string in your Windows registry. Whenever you now connect to a database without a connect string, the remote database will be used.

• Run the RegEdt32 program
• Navigate to: HKEY_LOCAL_MACHINE/ SOFTWARE/ ORACLE
• Go to Edit/ Add value and type in LOCAL
• When prompted for a String, enter your TNSNAMES.ORA alias.

PS: In a BAT or CMD file, you can just do a "set LOCAL=alias_name".

·  Back to top of file

Any performance tuning tips for Oracle on NT/2000?

• Make sure you have enough memory. If real memory on your system is limited, the CPU will spend considerable time paging.
• If supported, change the machine configuration from the default "Maximize throughput for file sharing" to "Maximize throughput for network applications" (Right click on Network Neighborhood/ Services/ Server/ Properties).
• Remove any protocols not required from the installed network software list. Move the most frequently used protocol to the top.
• Stop all unnecessary services on your machine.
• Use proper file placement so I/O is spread evenly across disks. If possible use RAID devices.
• Filesystem overhead can be eliminated by using raw devices.
• Windows NT supports asynchronous I/O, thus, configuring multiple db writers is not required.
• You get the best performance when all threads runs with NORMAL priority (see the ORACLE_PRIORITY registry value).
• The default DB_BLOCK_SIZE is still 2K. Check and increase this to 4K or 8K if required. If you create your database with the Database Configuration Assistant, Oracle will choose more sensible block sizes (4K for OLTP, 8K for OLAP or mixed).
• Choose a simple screen saver. You might like the 3D effect, your database will NOT.
• If all else fails, consider upgrading to Unix or MVS. You can migrate an Oracle database to a different platform without impacting your end-users.

·  Back to top of file

What NT/2000 commands can be used when administrating my database?

You may find the following commands useful:

at	Schedule jobs to run
hostname	Your machine name. Listed in LISTENER.ORA.
ipconfig	Your machine's IP address. Listed in LISTENER.ORA.
netstat	Monitor network connections.

·  Back to top of file

Is there a decent scripting environment for Windows NT/2000?

Third-party scripting languages like Perl and Python can be downloaded and installed on Windows NT and 2000 systems. Perl (my favorite) can be downloaded from www.activeperl.com. Python can be downloaded from www.python.org.

Microsoft's commercial solution to the lack of a proper scripting language is called Microsoft SFS (Services for UNIX). SFS ships with a Unix borne shell, ActiveState Perl and other common Unix utilities like ls, crontab, grep, vi, top, ps, kill, etc. It also includes a Telnet Server to provide terminal access to your NT/2000 machines. This is extremely handy if you need to work from home.

More information about Windows Services for UNIX can be obtained from www.microsoft.com/windows/sfu.

·  Back to top of file

What is the difference between FailSafe and Oracle Parallel Server?

Both Oracle FailFave and Parallel Server runs on Clustered Hardware.

Oracle FailSafe provides a high-availability database solution for Windows NT/2000 clusters. In the event of system failure, FailSafe will recover and restart the database on an alternative node within the cluster.

Oracle Parallel Server clusters databases for providing high availability and scalability through the use of shared storage systems. It allows users on multiple nodes to access a common Oracle database simultaneously. It enables multiple Oracle Server instances to run on different nodes of a cluster while providing concurrent access to common data.

·  Back to top of file

Does Parallel Server use MS Clustering Services (MSCS)?

No. Oracle Parallel Server (OPS) and Real Application Clusters (RAC) contains its own clustering services for all Intel (Windows/ Linux) platforms. This means that an OPS/RAC cluster only provides high availability for the database environment.

·  Back to top of file

What are the Oracle Services for MTS?

The Oracle Services for MTS allow an Oracle database to act as a resource manager in a MTS (Microsoft Transaction Server)/DTC transaction environment. This is possible because the Oracle Database supports the XA standard.

Some of the benefits that MTS provides are:

• Database connection pooling,
• Allows an Oracle database to participate in Microsoft Transaction Server transactions,
• Etc.

·  Back to top of file

Where can one get more info about Windows NT/2000?

• The Oracle DBA's guide to NT
• "oerr" (Oracle Error Lookup) utility for Windows
• Microsoft's Windows NT Home Page
• Microsoft's Windows 2000 Home Page
• Windows NT and Windows 2000 FAQ
• Windows NT/ 2000 Scripting

 

---

1. Different Oracle versions must be installed into different Oracle Homes.
2. Sql*Net settings should be set for each home sparately. You may share that settings among several homes by seting TNS_ADMIN environment variable.

I have 3 different clients on my W2K PC: 8.0.6, 8.1.7 and 9.0.2 and each of them successfully works with the same set of databases from 8.0.5 to 9.2.0 installed on W2K, HP-UX, Solaris and Linux.

We have an issue where different applications require different versions of the oracle client to run properly(i.e. 8.05 & 8.16). Can you install both versions of the oracle client on a NT workstation (say in c:\ora805 and c:\ora816) and use the oracle home selector to toggle between the two? Has anyone tried this and if so, where there any issues?

With Oracle flexible architecture (OFA), you can install and maintain multiple Oracle homes (i.e., multiple versions) for both server and clients. By default with OFA, version 8.0.5 is installed in drv:\oracle\ora80 while version 8.1.6 is installed in drv:\oracle\ora81.