Not Null Constraint

1. This constraint doesn’t allow NULL values in a column.

2. It allows duplicate values.

3. A NULL value is appropriate when the actual value is unknown.

4. A NULL value is not equivalent to value of zero if the data type is number and is not equivalent to spaces if the data type is character.

5. A NULL value will evaluate to NULL in any expression.(e.g. NULL multiplied by 10 is NULL)

6. NULL value can be inserted into columns of any data type.

7. If a column has NULL value, DBMS software ignores UNIQUE, Foreign key, check constraint that may be attached to the column.

Create table branch_mstr

(

branch_no char(10),

Name char(25)

);

INSERT INTO branch_mstr(BRANCH_NO, NAME) VALUES(‘B1’, NULL);

INSERT INTO branch_mstr(BRANCH_NO, NAME) VALUES(‘B1’, ‘’);

Select * from Branch_Mstr where NAME=‘’;

Select * from Branch_Mstr where NAME IS NULL;

NOTE:-

1. Usual operands such as =, <,> and so on can’t be used on a null value. Instead, the IS NULL & IS NOT NULL conditions have to be used.

2. The NOT NULL constraint can only be applied to the column level.

Syntax:-

<Column Name> <Data type> (size) not null;

Q:- Write a DDL statements to create a table employee Having Not Null Constraints for columns ecode, ename & sex of employee table.

Ans:- at page 601

2. Primary Key Constraint:-

At Column Level :-

Syntax :- <Column Name><Data Type>(<size>) PRIMARY KEY

• Quest: - Create a table CUST_MSTR such that the contents of the column CUST_No is primary key?

CREATE TABLE Cust_Mstr

(

Cust_No Char (10) primary key,

Fname char (25),

Mname char (26),

Lname char (25),

Dob_Inc date,

Occupation char (25)

);

PRIMARY KEY CONSTRAINT AT TABLE LEVEL:-

SYNTAX:

PRIMARY KEY (<Column_name>,<Column_name>)

Quest:- Create a table student and create Name & Father’s Name (composite key) as primary key ?

CREATE TABLE Student

(

Name char(30),

f_name char(30),

Class number,

Stream char(3),

PRIMARY KEY(name, f_name)

);

UNIQUE KEY CONSTRAINT:-

At Column level:-

Syntax:-

<column_name> <Data type>(size) UNIQUE

1. Quest:- Quest :- Create a table CUST_MSTR such that the contents of the column CUST_No is unique key?

2. Quest:- Create a table Dept such that the contents of dept. no & name of dept in the table should be unique.

3. Quest:- Create a table employee ensures that no two rows have same ecode and ecode, ename, sex should always have a value?

Answers of Q.No. 3 are given at page no 601.

Unique Key constraint at table level :-

Syntax:-

CREATE TABLE <table name>

(

<columan_name1> <Data type> (<size>),

<columan_name2> <Data type> (<size>),

UNIQUE (< columan_name1>, <columan_name2>)

);

Quest:- Create a table student and create Name & Father’s Name as Unique key ?

Default Constraint:- A default value can be

Specified for a column using DEFAULT clause.

When a user does not enter a value for the column (having default value), automatically the defined default value is inserted in the field.

e.g. (given code working in oracle but not in ms access)

CREATE TABLE employee

(

ecode integer NOT NULL PRIMARY KEY,

ename char(20) NOT NULL,

Sex char(1) NOT NULL,

Grade char(2) DEFAULT ‘E1’,

Gross decimal

);

Check constraint: - This constraint limits values

That can be inserted into a column of a table.

Syntax: CHECK(<cond.>)

E.g. CHECK(age>20)

Check constraint defined at column level:-

Syntax:-

<column Name><Data Type>(<size>) CHECK(<logical expression>)

Check constraint defined at table level:-

Syntax:-

CHECK(<logical expressin>)

Restrictions on check constraint

A check constraint requires that a condition be true or unknown for the row to be processed. If an SQL statement causes the condition to evaluate to false. An appropriate error message displayed and processing stops.

e.g. Unknown value do not violate a check constraint condition. The following CHECK constraint for sell price column in the Product table.

CHECK (sell price>0)

If a row inserted with NULL sell price, the row doesn’t violet the CHECK constraint because the entire check condition is evaluated as unknown. In this case such violations can be prevented by placing NOT NULL integrity constraint, along with CHECK Constraint on the sell price.

(i) Data Values being inserted into cust_no must started with capital letter ‘C’

Ans:- CHECK(CUST_NO LIKE ‘C%’)

(ii) Data values being inserted into column FNAME, MNAME & LNAME should be in uppercase only.

Ans:- CHECK(FNAME=UPPER(FNAME))

(iii) When a check constraint involves more than one column from the same table , it is specified after all the columns have been specified.

e.g. CREATE TABLE items

(

icode char(5) primary key,

descp char(20) not null,

rol intgeger,

qoh integer,

CHECK (rol<qoh)

);

FOREIGN KEY

1. THIS CONSTRAINT follows the primary key of another table.

2. This constraint doesn’t allow any values other than the values present in the corresponding primary key to which if follows.

3. One table can have multiple foreign keys.

4. The foreign key can allow NULL value & Duplicate value.

• Foreign key constraint defined at the column level :-

SYNTAX:-

<Column Name> <Data type>(<size>) REFERENCES <Table_Name>[(<column Name>)]

At table Level:-

FOREIGN KEY (<column name>[,<column name>]) REFERENCES <Table Name>[(<column name>, <column name>)]

Quest :- Create the table Account_Master with following data?

Account_master(acc_no (primary key), name (not null), e_mail_id (unique), acc_type(c/s), open_bal(>=1000))

Ans:-

CREATE TABLE account_master

(

acc_no number PRIMARY KEY,

name char(20) NOT NULL,

email_id char(30) UNIQUE,

age number check(age>=18),

acc_type char check(acc_type in (‘c’,’s’)),

open_bal number check(open_bal>=1000)

);

Quest:-Create the table Account_tran with following details:-

Account_tran(date_of_tran (not null), acc_no (foreign key), tran_type(DR/CR), tran_amt, curr_bal(>=1000))

Ans:-

CREATE TABLE account_tran

(

date_of_tran DATE NOT NULL,

acc_no NUMBER REFERENCES account_master(acc_no) on delete cascade,

tran_type char(2) check (tran_type in(‘DR’,’CR’)),

tran_type char(2) check (tran_type in(‘DR’,’CR’)),

tran_amt number check(tran_amt<0),

cur_bal number check(current_bal>=1000)

);

ALTER TABLE COMMAND:-

TO add a column in a table:-

Syntax:- ALTER TABLE <table name> ADD <column name> <data type> <size>;

Quest :- write a command to add a column named ‘tel_number’ in the EMP table?

Ans:- ALTER TABLE emp ADD(tel_number integer);

To modify existing columns of a table:-

ALTER TABLE <table name> MODIFY (columname newdatatype (newsize));

Quest:- To modify column job of table Emp to have new width of 30 characters.

Ans:- ALTER TABLE emp MODIFY (job char(30));

To add a new columns in a table:-

Syntax:- ALTER TABLE <table_name> ADD <column name> <data type> <size>

Quest:- Add a new column tel_number of type integer in a table emp

ALTER TABLE emp ADD (tel_number integer);

INSERT COMMAND

Used for inserting a record in table.

Syntax:-

INSERT INTO <table name> [<column list>] VALUES (<value>, <value>)

1. Insert a row into emp table following command can be used.

INSERT INTO emp

VALUES(8000,’rohan’, ‘clerk’,7902,’25-Jan-08’, 5000, 500, 30);

2. The same can be done with alternate command as:

INSERT INTO emp(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

VALUES(8000,’rohan’, ‘clerk’,7902,’25-Jan-08’, 5000, 500, 30);

3. Data can be added only to some columns in a row by specifying the columns and their data.

INSERT INTO emp(EMPNO, ENAME, MGR, DEPTNO)

Values(8000,’rohan’, 7902, 30);

Note : In an insert statement, only those columns can be omitted that have either default value defined or they allow NULL values.

Data Manipulatin Language (DML)

Operators used :-

1. Arithmetic :- Oracle allows arithmetic operators to be used while viewing records from a table or while performing data manipulation operations such as Insert, updated & delete. These are :

+ Addition ** exponentiation

- Subtraction ( ) enclosed operations

/ Division

* Multiplication

2. Logical :- AND, OR, & NOT

3. Relational :- <, >, <=, >=, (< >, !=)

4. Special operator:- LIKE, IN, BETWEEN

IN :- This operator selects the values from a list of values.

Quest :- Select all the employees whose dept no is 10 or 20.

Ans :-

Select * from emp where deptno IN(10,20);

Above problem can also be solved in another way.

SELECT * FROM emp WHERE deptno=10 or deptno=20;

Quest:- Create a NEWSPAPER table

CREATE TABLE newspaper

(

feature char(15) NOT NULL,

section char(1),

page number

);

Insert the following records in the table newspaper:-

Feature section page

National news A 1

Sports D 1

Editorials A 12

Business E 1

Whether C 2

Television B 7

Births F 7

Classified F 8

Modern Life B 1

1.Quest:- Select all those records whose page is in the list(1,2,3).

2.Quest:- Select all those records whose page is not in the list (1,2,3).

3.Quest:- Select all those records whose page is equal to 6, 10 or anything in between.

4.Quest:- Select all those records whose page is below 6 or above 10.

5.Quest:- select all those records whose section is in the list (‘A’,’C’,’F’).

6.Quest:- select all those records whose section is not in the list(‘A’,’C’,’F’).

7.Quest:- select all those records whose section equal to ‘B’, ‘D’ or anything in between.

8.Quest:- select all those records whose record is below ‘B’ or above ‘D’.

1. PAGE IN (1,2,3)

2. PAGE NOT IN (1,2,3)

3. PAGE BETWEEN 6 AND 10

4. PAGE NOT BETWEEN 6 AND 10

5. SECTION IN(‘A’,’C’,’F’)

6. SECTION NOT IN(‘A’,’C’,’F’)

7. SECTION BETWEEN ‘B’ AND ‘D’

8. SECTION NOT BETWEEN ‘B’ AND ‘D’

LIKE :- This operator performs pattern matching. An underscore character ( _ ) represents exactly one character. A percent (%) sign represent any no of character, including zero characters.

Underscore(_):- Replace only one character.

% :- Replace zero or more than zero character.

e.g.

1.Feature LIKE ‘MO%’ :- Feature begin with letter MO.

2.Feature LIKE ‘_ _I%’ :- Feature has an I in the third position.

3.Feature LIKE ‘%o%o%’ :- Feature has two o’ s in it.

Quest :- Display name of employees starts with ‘S’ and ends with ‘H’.

Ans:- SELECT * FROM emp

Where ename like ‘S%H’;

Quest:- Display the employees whose name starts with ‘K’ and ends width ‘G’ or the employee name should start with ‘J’?

Ans :- SELECT * FROM emp

WHERE ename LIKE ‘K%G’ OR

ename LIKE ‘J%’;

GROUP Functions:-

salary

5000

3900

4000

7000

8000

Sum

These functions works on multiple records of a single column & returns a single vlaue.

Five types of Group function available :-

SUM, MAX, MIN, AVG, COUNT

e.g. select sum(sal), max(sal), min(sal), avg(sal), count(sal), from emp;

COUNT function:- Ignores the null values for any columns.

select count(comm), count(sal) from emp;

Count function doesn’t ignore the NULL records when it is containing the no of records using the *.

e.g. SELECT COUNT(*), COUNT(deptno)

FROM dept;

NOTE: count counts the no of rows where expression is non-null, which are then returned by the query. With DISTINCT, COUNT counts only the distinct non-null rows. With * it counts all rows whether NULL or not.

e.g. SELECT COUNT(DISTINCT job), COUNT(job) , COUNT(*) FROM emp;

SYNTAX for Select statement:-

SELECT <col name>, <col name>

FROM <table name>

WHERE <cond.>

GROUP BY <col name>

HAVING <cond>

ORDER BY <col name>

GROUP BY clause:-

SAL DEPTNO

5000 10

4000 10

1200 20

1900 10

2700 30

1300 20

GROUP BY is used to put the data into groups having the same value.

e.g. SELECT deptno, Max(sal)

From emp

Group By deptno;

HAVING:- This is used to do the filteration on the results of GROUP BY.

Quest:- Display deptwise total no of employee only for those departments having no of employees >= 5.

SELECT deptno, count(empno)

FROM emp

GROUP BY deptno

HAVING COUNT(empno)>=5;

Points To Remember:-

1. Having has to be always used with the GROUP BY clause.

2. You have to use a group function in the having clause.

3. You can’t use a group functions in the where condition.

4. If a query involves both where conditions & having conditions then the where condition has to be always mentioned before the GROUP BY clause & having should be after group by clause.

Quest :- Display deptwise total salary only for departments 10 & 30 and having no of employee > 4.

Ans:- SELECT deptno, sum(sal), count(empno)

FROM emp WHERE deptno IN(10,30)

GROUP BY deptno

HAVING COUNT(empno)>4;

NOTE :- HAVING clause works very much like a WHERE clause except that its logic is only related to the results of group functions, as opposed to columns or expressions for individual rows, which can still be selected by where clause.

ORDER BY clause: - This is used to display data in ascending or descending order. For ascending you can use ‘ASC’ (by default) & for descending ‘DESC’.

In case of ORDER BY the ‘NULL’ values for the column will come at the bottom if it is ascending & descending then the NULL values will appear on the top.

Following can be used with ORDER BY clause:

1. Column name of the select statement.

2. Serial no of the column in the select

statement.

3. Alias name of the column in the select

statement.

NOTE: - The alias name can be a part of the order by clause but it can’t be a part of having clause.

Example:-

SELECT deptno, empno, ename, sal monthly_sal FROM emp

ORDER BY deptno, monthly_sal desc, 3 ASC;

ORDER OF EXECUTION:-

1. Choose rows based on the WHERE clause.

2. Group those rows based on the group by clause.

3. Calculate the results of the Group functions for each group.

4. Choose & eliminate groups based on the having clause.

5. Order the Groups based on the results of the group function in the ‘order by’ clause must use either a group function or a column specified in the ‘GROUP BY’ clause.

Quest :- Display empno, ename, deptno, dname, location for each & every employee?

Ans:-

SELECT empno, ename, emp.deptno, dname, loc from emp, dept

Where emp.deptno=dept.deptno

Order by 1;

Note: - In a select statement if a column belongs to multiple tables involved in the from clause you need to address that column by means of tablename.columnname

Def: - Whenever we need to select data from multiple tables we need to write a join condition. Tables are joined on columns that have the same data type & data width in the tables.

Four types of JOIN condition :-

(i) Cartesian Join

(ii) Equi Join (Inner Join)

(iii) Outer Join

(iv) Self Join

Cartesian join:- in this case the select statement doesn’t involve any join condition. All data from all the tables will be selected which is used very less in real life.

E.g. A = {1,2,3}

B = {a,b}

A X B = {(1,a),(1,b),(2,a),(2,b),(3,a),(3,b)}

E.g. SELECT empno, ename, emp.deptno, dname, loc FROM EMP, dept

ORDER BY 1

EQUI JOIN (INNER JOIN):-

1. In this case we need to get the data from multiple tables by means of an ‘=‘operator.

2. You need to have at least one column common. In the tables you want to join having the same data type & value.

3. You need to write at least (N-1) join condition if you want to select the data from N no of tables.

4. This is the most useful join condition in the real life. e.g. In slide no 98.

Syntax:-

SELECT <colname1>,<colname2>,<colnameN>

FROM <Table_Name1>,<Table_Name2>

WHERE <Table_Name1>.<colname1> = <Table_name2.colname2>

AND <condition>

ORDER BY <colname1>,<colname2>,<colnameN>;

E.g. Page No 806 – example 1, page No 807 – example 2, and page no 808 example 3 in sumita arora book.

Outer join

• Not in Syllabus.

SELF JOIN

• Not in syllabus.

VIEWS

1. A View is a database object which is a logical table or virtual table & it is always based on select statement.

2. The objective of the view is to provide the data to the user depending on his requirement.

3. A view can be used in real life for the following purposes:-

• Granting select statement permission on some selected columns of a table to any user.

• Displaying data from multiple tables without writing a join condition every time.

• Displaying calculated data without using group functions every times.

NOTE: - The fact about view is that it is stored only as a definition in Data Dictionary. When a reference is made to a view, its definition is scanned, the base table is opened & the view is created on top of the base tables. Hence a view has no data at all, until a specific call to a view is made.

Reasons why view is created are:-

(i) When data security is required.

(ii) When data redundancy is to be kept to the minimum while maintaining data security.

(iii) If a view is used to only look at table data and nothing else the view is called the “Read Only View”.

(iv) A view that is used to look at the table data as well as insert, update and Delete table data is called updateable view.

(v) The ORDER BY clause can't be used while creating a view.

SYNTAX:-

CREATE VIEW <Name of View> AS <SELECT STMT>

e.g. CREATE VIEW v_new AS

SELECT empno, ename, sal, Deptno From Emp WHERE sal>1000;

If any column in the view is to be given a different name other than the name of the column from which it is derived, it is done by specifying the new column name as shown below.

CREATE VIEW v_new (empno, employee,

sal, deptno) AS

SELECT empno, ename, sal, Deptno

From Emp WHERE sal>1000;

If the view is based on a select statement having a group function then you must express the group function by means of an alias name without any special parameter.

e.g.

CREATE VIEW v_summary

AS SELECT deptno, SUM(sal) sum_sal, COUNT(emp_no) count_empno, AVG(sal)

avg_sal FROM emp

GROUP BY deptno;