Create table:

[Link] table employees (empid number(2), name varchar2(14),

salary number(5), depid number);

Alter Table:

[Link] table employees add(job_id varchar2(25));

3. Alter table employees modify(name varchar2(25));

We can change a column’s data type, size and default value.

A change to the default value affects only subsequent insertions to the
table.

4. Alter table employees drop column job_id;

1
Dropping a Table:

All data and structure in the table is deleted.

Any pending transactions are commited.
All indexes are dropped.
You cannot roll back the drop table statement.

5. Drop table dept80;

Truncating a Table:
The truncate table statement
Removes all rows from a table.
Releases the storage space used by that table.
You cannot roll back removal when using truncate.

6. Truncate table dept20;

2
Inserting new rows:
7. Insert into employees(empid , name, salary, depid) values(100,
‘kocher’, 50000,1);
Inserting rows with null values:

Implicit method: Omit the column from the column list.

8. Insert into employees(empid, name) values(30,’purchase’);

Explicit Method:
Specify the null keyword in the value clause.
9. Insert into employees values(100, ‘Higgins’, null,null);

Update :

[Link] employees set salary=60000 where name =‘higgins’;

3
Constraints:
Constraints enforce rules at the table level.

It prevent the deletion of a table if there are dependencies.

TYPES:
NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
CHECK
Constraints can be created at the same time as the table is created.
Or After the table has been created.
PRIMARY KEY:
It is a column or set of columns that uniquely identifies each row in a
table.
4
[Link] table department(depid number, depname
varchar2(25),locid number, constraint dep_id_pk primary key(depid);

FOREIGN KEY
is a key used to link two tables together. A FOREIGN KEY is a field in one
table that refers to the PRIMARY KEY in another table.

12. Create table employees(empid number, name varchar2(25), salary

number, depid number, constraint emp_dept_fk foreign key(dep_id)
references dep(depid));

Adding a constraint:
Alter table employees add constraint emp_empid_pk primary
key(empid);

5
Definition of Composite key:
A key that has more than one attributes known as composite key. It is
also known as compound key.
Note: Any key such as super key, primary key, candidate key etc. can be
called composite key if it has more than one attributes.

Composite key Example

Lets consider a table Sales. This table has four columns (attributes) –
cust_Id, order_Id, product_code & product_count.
Table – Sales

6
st_Id order_Id product_code product_count
-------- -------- ------------ -------------
C01 O001 P007 23
CO2 O123 P007 19
C02 O123 P230 82
C01 O001 P890 42
None of these columns alone can play a role of key in this table.
Column cust_Id alone cannot become a key as a same customer can place
multiple orders, thus the same customer can have multiple entires.
Column order_Id alone cannot be a primary key as a same order can contain the
order of multiple products, thus same order_Id can be present multiple times.
Column product_code cannot be a primary key as more than one customers can
place order for the same product.
Column product_count alone cannot be a primary key because two orders can
be placed for the same product count.
Based on this, it is safe to assume that the key should be having more than one
attributes:
Key in above table: {cust_id, product_code}
7
This is a composite key as it is made up of more than one attributes.
A superkey is a combination of columns that uniquely
identifies any row within a relational database management
system (RDBMS) table. A candidate key is a closely related
concept where the superkey is reduced to the minimum
number of columns required to uniquely identify each row.

Candidate keys are selected from the set of super keys, the
only thing we take care while selecting candidate key is: It
should not have any redundant attribute. That’s the reason
they are also termed as minimal super key.

8
Emp_SSN Emp_Number Emp_Name
--------- ---------- --------
123456789 226 Steve
999999321 227 Ajeet
888997212 228 Chaitanya
777778888 229 Rohit
Super keys: The above table has following super keys. All of
the following sets of super key are able to uniquely identify a
row of the employee table.
{Emp_SSN}
{Emp_Number}
{Emp_SSN, Emp_Number}
{Emp_SSN, Emp_Name}
{Emp_SSN, Emp_Number, Emp_Name}
9
Candidate Keys: As I mentioned in the beginning, a candidate
key is a minimal super key with no redundant attributes. The
following two set of super keys are chosen from the above
sets as there are no redundant attributes in these sets.
{Emp_SSN}
{Emp_Number}
Only these two sets are candidate keys as all other sets are
having redundant attributes that are not necessary for unique
identification.
Primary Key:
A Primary key is selected from a set of candidate keys. This is
done by database admin or database designer. We can say
that either {Emp_SSN} or {Emp_Number} can be chosen as a
primary key for the table Employee.
10
Capabilities of SQL Select Statement:
Projection: To select the columns.
Selection: To select the rows.
Joining: To join two or more tables.

Select all columns:

1. Select * from employees;
Selecting specific columns:

2. Select empid, name from employees;

Arithmetic Expressions:
Create expressions with number and date data by using arithmetic
operators.
3. Select name,salary, salary +100
11
From employees;
Operator precedence:

*, / , +, - same priority operators are evaluated from left to right.

Parentheses are used to force the priority.
4. Select name,salary, 10*(salary+100)
From employees;
Null Values:
A null is a value that is unavailable, unassigned, unknown or inappli
cable.
A null is not the same as zero or a blank space.
Arithmetic expressions containing a null value evaluate to null.

Defining a column Alias:

Renames a column heading.

It is useful with the calculations.
12
5. Select name as empname, commpct comm
from employees;

6. Select name, salary, salary+500 “Bonus” from employees;

Concatenation operator:
And using character literals:

Concatenates columns or character strings to other columns.

Is represented by two vertical bars ||.
Creates a resultant column that is a character expression.

7. Select name|| ‘ salary is’||salary as “Employee salary” from employees;

Eliminating duplicate rows:

8. Select distinct depid from employees;

Displaying the table structure: 13

Limiting the Rows of selection: - Using the where clause:

9. Select empid, name, salary from employees where salary<=4000;

Character Strings and date:

Character strings and date values are enclosed within single quotation
mark.
Character values are case sensitive and date values are format
sensitive.

The default date format is DD_MON_RR.

10. Select empid, name, salary from employees where

name=‘kocher’;
Comparison Conditions:
=, <,>, >=, <=, <>, BETWEEN….AND…. , IN, LIKE, IS NULL
14
11. Select empid,salary from employees where salary between 40000
and 50000;

12. Select empid,name, salary from employee where empid in

(100,101,200,201);

Like Operator:

13. Select name from employees where name like ‘k%’;

14. Select name from employees where name like ‘_i%’;

15. Select name from employees where name like ‘k_%’;

Logical Conditions:
AND, OR, NOT 15
16. Select empid, name, salary from emp salary >=30000 and jobid like
‘%manager’;
Order by Clause:
ASC: ascending order, default.
DESC:descending order.
17. Select empid, name, salary from employees order by salary;

16
Types of Join:
Oracle Proprietary Join
Equijoin
Non-equijoin
Outer Join
Self join

SQL Join:
Cross join
Natural Join
Using clause
Full or two sided outer join

17
Equijoin:
17. Select [Link], [Link], [Link], [Link], [Link] from
employee e, department d where [Link]=[Link];

Non-equijoin:

Select [Link], [Link], j.grade_level from employee e, job_grades j

Where [Link] between [Link] and j.highest_sal;

Outer join:

Select [Link], [Link],[Link] from employee e, department d

where e. depid(+)=[Link];

18
EMPLOYEES
Empid Name salary depid Job_id
100 Kocher 40000 1 Mgr
101 Higgins 50000 1 Exe
200 Abel 45000 2 Acc
300 Ram 38000 2 Exe
400 Rohit 60000 3 Sa_rep

19
Dep
Depid Depname Locid
1 CSE 1000
2 IT 2000
3 ACC 2000
4 ADMIN 3000
5 SALES 4000

20