Basic Oracle-2

DDL

Data Definition Language (DDL) statements are used to define the database structure or schema.

• CREATE - to create objects in the database
• ALTER - alters the structure of the database
• DROP - delete objects from the database
• TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
• COMMENT - add comments to the data dictionary
• RENAME - rename an object

DML

Data Manipulation Language (DML) statements are used for managing data within schema objects.

• SELECT - retrieve data from the a database
• INSERT - insert data into a table
• UPDATE - updates existing data within a table
• DELETE - deletes all records from a table, the space for the records remain
• MERGE - UPSERT operation (insert or update)
• CALL - call a PL/SQL or Java subprogram
• EXPLAIN PLAN - explain access path to data
• LOCK TABLE - control concurrency

DCL

Data Control Language (DCL) statements.

• GRANT - gives user's access privileges to database
• REVOKE - withdraw access privileges given with the GRANT command

·         TCL

TCL is abbreviation of Transactional Control Language. It is used to manage different transactions occurring within a database.

·         Examples: COMMIT, ROLLBACK statements

How are Primary Key and Foreign key inter-related?

Primary Key is a column or combination of columns to have unique records in the table. There can only be one Primary Key in a table. A foreign key is a column or combination of columns to enforce a relation between the two tables. The Primary Key of table becomes the forign key in the second table. Such as if take an example there are two tables "Stud" and "result". Table 1 i.e. "stud" is having roll_number , name,class, as its fields. In this table the roll_number field is a Primary Key. Hence no two rows will have the same roll_number. Now in the second table i.e. "result" the fields are roll_number,sub1,sub2,sub3 and total. Here this table is related to "stud" table by making "roll_number " the foreign key.

What is ACID property?

ACID is the acronym for

1. Atomicity,

2.Consistency

3.,Isolation and

4. Durability.

 Whenever a transaction is executed in SQL server certain characteristics which are to be possessed by the transaction is maintained. ACID are those charactersistics. 


Atomicity:- By atomicity it is meant that group of T-sql statements will act in a batch. 


Consistency:- The transaction should act open consistent data and after operation should leave data in consistent form. 


Isolation:- By isolation it is meant that the running transaction should feel that it is the only one being performed. That is that if there are several transaction being executed then transaction isolation will ensure that each transaction will think that it has exclusive use of the system. 


Durability:- By transaction Durabity it is meant that any successful completion ofthe transaction should leave it in permanent state,i.e. ther should be no loss of information. Such as committed transactions should persist,even if there is any crash or power faliure.Basically it is the ability of DBMS to recover committed data even after power faliures or system crash.

 

SQL: GROUP BY Clause

---

The GROUP BY clause can be used in a SELECT statement to collect data across multiple records and group the results by one or more columns.

The syntax for the GROUP BY clause is:

SELECT column1, column2, ... column_n, aggregate_function (expression)
FROM tables
WHERE predicates
GROUP BY column1, column2, ... column_n;

aggregate_function can be a function such as SUM, COUNT, MIN, or MAX.

Example using the SUM function

For example, you could also use the SUM function to return the name of the department and the total sales (in the associated department).

SELECT department, SUM(sales) as "Total sales"
FROM order_details
GROUP BY department;

Because you have listed one column in your SELECT statement that is not encapsulated in the SUM function, you must use a GROUP BY clause. The department field must, therefore, be listed in the GROUP BY section.

Example using the COUNT function

For example, you could use the COUNT function to return the name of the department and the number of employees (in the associated department) that make over $25,000 / year.

SELECT department, COUNT(*) as "Number of employees"
FROM employees
WHERE salary > 25000
GROUP BY department;

Example using the MIN function

For example, you could also use the MIN function to return the name of each department and the minimum salary in the department.

SELECT department, MIN(salary) as "Lowest salary"
FROM employees
GROUP BY department;

Example using the MAX function

For example, you could also use the MAX function to return the name of each department and the maximum salary in the department.

SELECT department, MAX(salary) as "Highest salary"
FROM employees
GROUP BY department;

SQL: Joins

---

A join is used to combine rows from multiple tables. A join is performed whenever two or more tables is listed in the FROM clause of an SQL statement.

There are different kinds of joins. Let's take a look at a few examples.

Inner Join (simple join)

Chances are, you've already written an SQL statement that uses an inner join. It is the most common type of join. Inner joins return all rows from multiple tables where the join condition is met.

For example,

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers, orders
WHERE suppliers.supplier_id = orders.supplier_id;

This SQL statement would return all rows from the suppliers and orders tables where there is a matching supplier_id value in both the suppliers and orders tables.

Let's look at some data to explain how inner joins work:

We have a table called suppliers with two fields (supplier_id and supplier_ name).
It contains the following data:

supplier_id	supplier_name
10000	IBM
10001	Hewlett Packard
10002	Microsoft
10003	NVIDIA

We have another table called orders with three fields (order_id, supplier_id, and order_date).
It contains the following data:

order_id	supplier_id	order_date
500125	10000	2003/05/12
500126	10001	2003/05/13

If we run the SQL statement below:

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers, orders
WHERE suppliers.supplier_id = orders.supplier_id;

Our result set would look like this:

supplier_id	Name	order_date
10000	IBM	2003/05/12
10001	Hewlett Packard	2003/05/13

The rows for Microsoft and NVIDIA from the supplier table would be omitted, since the supplier_id's 10002 and 10003 do not exist in both tables.

Outer Join

Another type of join is called an outer join. This type of join returns all rows from one table and only those rows from a secondary table where the joined fields are equal (join condition is met).

For example,

select suppliers.supplier_id, suppliers.supplier_name, orders.order_date
from suppliers, orders
where suppliers.supplier_id = orders.supplier_id(+);

This SQL statement would return all rows from the suppliers table and only those rows from the orders table where the joined fields are equal.

The (+) after the orders.supplier_id field indicates that, if a supplier_id value in the suppliers table does not exist in the orders table, all fields in the orders table will display as <null> in the result set.

The above SQL statement could also be written as follows:

select suppliers.supplier_id, suppliers.supplier_name, orders.order_date
from suppliers, orders
where orders.supplier_id(+) = suppliers.supplier_id

Let's look at some data to explain how outer joins work:

We have a table called suppliers with two fields (supplier_id and name).
It contains the following data:

supplier_id	supplier_name
10000	IBM
10001	Hewlett Packard
10002	Microsoft
10003	NVIDIA

We have a second table called orders with three fields (order_id, supplier_id, and order_date).
It contains the following data:

order_id	supplier_id	order_date
500125	10000	2003/05/12
500126	10001	2003/05/13

If we run the SQL statement below:

select suppliers.supplier_id, suppliers.supplier_name, orders.order_date
from suppliers, orders
where suppliers.supplier_id = orders.supplier_id(+);

Our result set would look like this:

supplier_id	supplier_name	order_date
10000	IBM	2003/05/12
10001	Hewlett Packard	2003/05/13
10002	Microsoft	<null>
10003	NVIDIA	<null>

The rows for Microsoft and NVIDIA would be included because an outer join was used. However, you will notice that the order_date field for those records contains a <null> value.

SQL: UNION Query

---

The UNION query allows you to combine the result sets of 2 or more "select" queries. It removes duplicate rows between the various "select" statements.

Each SQL statement within the UNION query must have the same number of fields in the result sets with similar data types.

The syntax for a UNION query is:

select field1, field2, . field_n
from tables
UNION
select field1, field2, . field_n
from tables;

Example #1

The following is an example of a UNION query:

select supplier_id
from suppliers
UNION
select supplier_id
from orders;

In this example, if a supplier_id appeared in both the suppliers and orders table, it would appear once in your result set. The UNION removes duplicates.

Example #2 - With ORDER BY Clause

The following is a UNION query that uses an ORDER BY clause:

select supplier_id, supplier_name
from suppliers
where supplier_id > 2000
UNION
select company_id, company_name
from companies
where company_id > 1000
ORDER BY 2;

Since the column names are different between the two "select" statements, it is more advantageous to reference the columns in the ORDER BY clause by their position in the result set. In this example, we've sorted the results by supplier_name / company_name in ascending order, as denoted by the "ORDER BY 2".

The supplier_name / company_name fields are in position #2 in the result set.

Normal Join

SELECT a.`author_name` , a.`published_book` , a.`price` , b.`mobile`

FROM author01 a, author02 b

WHERE a.`author_name` = b.`author_name`

LEFT JOIN

SELECT a.`author_name` , a.`published_book` , a.`price` , b.`mobile`

FROM author02 b LEFT JOIN  author01 a

ON a.`author_name` = b.`author_name`

SELECT a.`author_name` , a.`published_book` , a.`price` , b.`mobile`

FROM    author01 a LEFT JOIN author02 b

ON a.`author_name` = b.`author_name`

RIGHT JOIN

SELECT a.`author_name` , a.`published_book` , a.`price` , b.`mobile`

FROM    author01 a LEFT JOIN author02 b

ON a.`author_name` = b.`author_name`

Groupby

SELECT price, author_name, SUM( price ) AS "Total Price"

FROM `author01`

GROUP BY author_name

HAVING SUM( price ) >450

SELECT author_name,COUNT(price) as "book number"

  FROM `author01`

    GROUP BY author_name

SQL: LIKE Condition

---

The LIKE condition allows you to use wildcards in the where clause of an SQL statement. This allows you to perform pattern matching. The LIKE condition can be used in any valid SQL statement - select, insert, update, or delete.

The patterns that you can choose from are:

% allows you to match any string of any length (including zero length)

_ allows you to match on a single character

Examples using % wildcard

The first example that we'll take a look at involves using % in the where clause of a select statement. We are going to try to find all of the suppliers whose name begins with 'Hew'.

SELECT * FROM suppliers
WHERE supplier_name like 'Hew%';

You can also using the wildcard multiple times within the same string. For example,

SELECT * FROM suppliers
WHERE supplier_name like '%bob%';

In this example, we are looking for all suppliers whose name contains the characters 'bob'.

You could also use the LIKE condition to find suppliers whose name does not start with 'T'. For example,

SELECT * FROM suppliers
WHERE supplier_name not like 'T%';

By placing the not keyword in front of the LIKE condition, you are able to retrieve all suppliers whose name does not start with 'T'.

Examples using _ wildcard

Next, let's explain how the _ wildcard works. Remember that the _ is looking for only one character.

For example,

SELECT * FROM suppliers
WHERE supplier_name like 'Sm_th';

This SQL statement would return all suppliers whose name is 5 characters long, where the first two characters is 'Sm' and the last two characters is 'th'. For example, it could return suppliers whose name is 'Smith', 'Smyth', 'Smath', 'Smeth', etc.

Here is another example,

SELECT * FROM suppliers
WHERE account_number like '12317_';

You might find that you are looking for an account number, but you only have 5 of the 6 digits. The example above, would retrieve potentially 10 records back (where the missing value could equal anything from 0 to 9). For example, it could return suppliers whose account numbers are:

123170
123171
123172
123173
123174
123175
123176
123177
123178
123179.

Examples using Escape Characters

Next, in Oracle, let's say you wanted to search for a % or a _ character in a LIKE condition. You can do this using an Escape character.

Please note that you can define an escape character as a single character (length of 1) ONLY.

For example,

SELECT * FROM suppliers
WHERE supplier_name LIKE '!%' escape '!';

This SQL statement identifies the ! character as an escape character. This statement will return all suppliers whose name is %.

Here is another more complicated example:

SELECT * FROM suppliers
WHERE supplier_name LIKE 'H%!%' escape '!';

This example returns all suppliers whose name starts with H and ends in %. For example, it would return a value such as 'Hello%'.

You can also use the Escape character with the _ character. For example,

SELECT * FROM suppliers
WHERE supplier_name LIKE 'H%!_' escape '!';

This example returns all suppliers whose name starts with H and ends in _. For example, it would return a value such as 'Hello_'.

Frequently Asked Questions

---

Question:  How do you incorporate the Oracle upper function with the LIKE condition? I'm trying to query against a free text field for all records containing the word "test". The problem is that it can be entered in the following ways: TEST, Test, or test.

Answer: To answer this question, let's take a look at an example.

Let's say that we have a suppliers table with a field called supplier_name that contains the values TEST, Test, or test.

If we wanted to find all records containing the word "test", regardless of whether it was stored as TEST, Test, or test, we could run either of the following SQL statements:

select * from suppliers
where upper(supplier_name) like ('TEST%');

or

select * from suppliers
where upper(supplier_name) like upper('test%')

These SQL statements use a combination of the upper function and the LIKE condition to return all of the records where the supplier_name field contains the word "test", regardless of whether it was stored as TEST, Test, or test.

Practice Exercise #1:

Based on the employees table populated with the following data, find all records whose employee_name ends with the letter "h".

CREATE TABLE employees
(	employee_number	number(10)	not null,
	employee_name	varchar2(50)	not null,
	salary	number(6),
	CONSTRAINT employees_pk PRIMARY KEY (employee_number)
);

INSERT INTO employees (employee_number, employee_name, salary)
VALUES (1001, 'John Smith', 62000);

INSERT INTO employees (employee_number, employee_name, salary)
VALUES (1002, 'Jane Anderson', 57500);

INSERT INTO employees (employee_number, employee_name, salary)
VALUES (1003, 'Brad Everest', 71000);

INSERT INTO employees (employee_number, employee_name, salary)
VALUES (1004, 'Jack Horvath', 42000);

Solution:

The following SQL statement would return the records whose employee_name ends with the letter "h".

SELECT *
FROM employees
WHERE employee_name LIKE '%h';

It would return the following result set:

EMPLOYEE_NUMBER	EMPLOYEE_NAME	SALARY
1001	John Smith	62000
1004	Jack Horvath	42000

Practice Exercise #2:

Based on the employees table populated with the following data, find all records whose employee_name contains the letter "s".

CREATE TABLE employees
(	employee_number	number(10)	not null,
	employee_name	varchar2(50)	not null,
	salary	number(6),
	CONSTRAINT employees_pk PRIMARY KEY (employee_number)
);

INSERT INTO employees (employee_number, employee_name, salary)
VALUES (1001, 'John Smith', 62000);

INSERT INTO employees (employee_number, employee_name, salary)
VALUES (1002, 'Jane Anderson', 57500);

INSERT INTO employees (employee_number, employee_name, salary)
VALUES (1003, 'Brad Everest', 71000);

INSERT INTO employees (employee_number, employee_name, salary)
VALUES (1004, 'Jack Horvath', 42000);

Solution:

The following SQL statement would return the records whose employee_name contains the letter "s".

SELECT *
FROM employees
WHERE employee_name LIKE '%s%';

It would return the following result set:

EMPLOYEE_NUMBER	EMPLOYEE_NAME	SALARY
1002	Jane Anderson	57500
1003	Brad Everest	71000

Practice Exercise #3:

Based on the suppliers table populated with the following data, find all records whose supplier_id is 4 digits and starts with "500".

CREATE TABLE suppliers
(	supplier_id	varchar2(10)	not null,
	supplier_name	varchar2(50)	not null,
	city	varchar2(50),
	CONSTRAINT suppliers_pk PRIMARY KEY (supplier_id)
);

INSERT INTO suppliers (supplier_id, supplier_name, city)
VALUES ('5008', 'Microsoft', 'New York');

INSERT INTO suppliers (supplier_id, supplier_name, city)
VALUES ('5009', 'IBM', 'Chicago');

INSERT INTO suppliers (supplier_id, supplier_name, city)
VALUES ('5010', 'Red Hat', 'Detroit');

INSERT INTO suppliers (supplier_id, supplier_name, city)
VALUES ('5011', 'NVIDIA', 'New York');

Solution:

The following SQL statement would return the records whose supplier_id is 4 digits and starts with "500".

select *
FROM suppliers
WHERE supplier_id LIKE '500_';

It would return the following result set:

SUPPLIER_ID	SUPPLIER_NAME	CITY
5008	Microsoft	New York
5009	IBM	Chicago