CREATE TABLE sailors(sid number(5) primary key, sname varchar2(30),rating integer, age real);

desc Sailors;

CREATE TABLE boats (bid integer primary key, bname varchar2(20), color varchar2(20));
desc boats;

CREATE TABLE reserves(sid number, bid number, day date,

CONSTRAINT reserves_pk PRIMARY KEY (sid, bid, day),
FOREIGN KEY (sid) REFERENCES sailors(sid),
FOREIGN KEY (bid) REFERENCES boats(bid));

desc reserves;
Sailors Table Reserves Table

Boats Table
• Insert into sailors values(&sid, '&sname', &rating, &age);
• commit;
• select * from sailors;
• Insert into boats values(&bid, '&bname', '&color');
• commit;
• select * from boats;
• Insert into reserves values(&sid, &bid, '&day’);
• commit;
• select * from reserves;
The basic form of an SQL query:
SELECT [DISTINCT] select-list FROM from-list WHERE qualification

Example1: Find the names and ages of all sailors.

• select sname, age from sailors;
OR
• select s.sname, s.age from sailors s;

Distinct: It is used to prevent the duplicate values. The distinct keyword is optional.
• select distinct s.sname, s.age from sailors s;

Example2: Find all sailors with a rating above 7

• select s.sid, s.sname, s.rating, s.age from sailors s where s.rating>7;
OR
• select * from sailors where rating>7;
Example3: Find the names of sailors who have reserved boat number 103.
• Select s.sname from sailors s, reserves r where s.sid=r.sid and r.bid=103;
• Select sname from sailors, reserves where sailors.sid=reserves.sid and
bid=103;

Example4: Find the sids of sailors who have reserved a red boat.
• select r.sid from boats b, reserves r where b.bid=r.bid and b.color='red’;

Example5: Find the names of sailors who have reserved a red boat .
• Select s.sname from sailors s, reserves r, boats b where s.sid=r.sid and
r.bid=b.bid and b.color='red';
Example6: Find the colors of boats reserved by Lubber.
• select b.color from sailors s, reserves r, boats b where s.sid=r.sid and
r.bid=b.bid and s.sname='Lubber’;

Example7: Find the names of sailors who have reserved atleast one boat.
• select s.sname from sailors s, reserves r where s.sid=r.sid;
Note: The join of sailors and reserves ensures that for each selected
sname, the sailor has made some reservation.
• Expressions and Strings in the SELECT Command

Example:
Compute increments for the ratings of person who have sailed two differen
boats on the same day.

SELECT S.sname, S.rating+1 AS rating from Sailors S, Reserves R1, Reserves R2

where S.sid=R1.sid and S.sid=R2.sid and R1.day=R2.day and R1.bid<>R2.bid
Example
sid bid day
1 101 2003
1 102 2003
Department of CSE EID 301 & Database Management Systems
2 101 2003
April 2, 2024
:
7
Like operator: The Like operator is used to match specific pattern for
values in a table.
% ----- It represents all characters.

_ ----- Represents each character.

These symbols are called wildcard characters.

Syntax:
Select * from tablename where colname like ‘pattern’;
Display the sailorid and names of all the people whose names contains 5
letters.
• select sid, sname from sailors where sname like ‘_____';

Find the ages of sailors whose name begins and ends with B and has atleast
three characters.
• Select s.age from sailors s where s.sname LIKE ‘B_%B';
Display the sailorid and names of all the people whose names start with
letter H.
• select sid, sname from sailors where sname like 'H%’;

Display the sailorid and names of all the people whose names end with
letter a.
• select sid, sname from sailors where sname like '%a’;

Display the sailorid and names of all the people whose names start with
letter H and ends with letter o.
• select sid,sname from sailors where sname like 'H%o';
Find the names of sailors who have reserved a
red or a green boat
• select S.sname from Sailors S, Reserves R, Boats B where S.sid=R.sid and
R.bid=B.bid and (B.color=‘red’ or B.color=‘green’);

using UNION( the above query can be rewritten as )

Select S.sname from Sailors S, Reserves R, Boats B where S.sid=R.sid and

R.bid=B.bid and B.color=‘red’
UNION
select S2.sname from Sailors S2, Reserves R2, Boats B2 where S2.sid=R2.sid
and R2.bid=B2.bid and B2.color=‘green’
Department of CSE EID 301 &Database Management
April 2, 2024 Systems: 11
Union, Intersect and Except:
Find the sid of sailors who have reserved a red or a green boat.
Union :
select r.sid
from reserves r, boats b
where r.bid=b.bid and b.color='red’
union
select r2.sid
from reserves r2, boats b2
where r2.bid=b2.bid and b2.color='green';
Find all sids of sailors who have a rating of 10 or reserved boat 104.
• Select s.sid from sailors s where s.rating=10
union
select r.sid from reserves r where r.bid=104;

Union all:
Find the names of sailors who have reserved a red or a green boat.
• select s.sname from sailors s, reserves r, boats b where s.sid=r.sid and
r.bid=b.bid and b.color='red’
union all
select s2.sname from sailors s2, reserves r2, boats b2 where
s2.sid=r2.sid and r2.bid=b2.bid and b2.color='green';
Intersect
Find the names of sailors who have reserved both a red and a green boat.
• select s.sname from sailors s, reserves r, boats b where s.sid=r.sid
and r.bid=b.bid and b.color='red’
intersect
select s2.sname from sailors s2, reserves r2, boats b2 where
s2.sid=r2.sid and r2.bid=b2.bid and b2.color='green' ;
Except
Find the sids of all sailors who have reserved red boats but not green boats.
• select r.sid from boats b, reserves r where r.bid=b.bid and b.color='red’
except
select r2.sid from boats b2, reserves r2 where r2.bid=b2.bid and
b2.color='green';
Nested Queries:
A nested query is a query that has another query embedded within it, the embedded
query is called a subquery.
In a nested subquery, the inner query runs first and its result is used by the outer
query while executing.
The inner subquery has been completely independent of the outer query.

Find the names of sailors who have reserved boat 103.

Select s.sname from sailors s, reserves r where s.sid=r.sid and r.bid=103;

• Select s.sname from sailors s where s.sid in(select r.sid from reserves r where r.bid=103);
• IN : The in operator allows us to test whether a value is in a given set of elements.

Find the names of sailors who have reserved a red boat.

• Select s.sname from sailors s where s.sid in(select r.sid from reserves r where r.bid in
(select b.bid from boats b where b.color=‘red’));
Find the names of sailors who have not reserved a red boat.
• Select s.sname from sailors s where s.sid not in(select r.sid from reserves r
where r.bid in(select b.bid from boats b where b.color=‘red’));

Find the names of sailors who have reserved both a red and a green boat.
• Select s.sname from sailors s, reserves r, boats b where s.sid=r.sid and
r.bid=b.bid and b.color=‘red’ and s.sid in(select s2.sid from sailors s2,
boats b2, reserves r2 where s2.sid=r2.sid and r2.bid=b2.bid and
b2.color=‘green’);
 Difference between Sub query and Correlated sub query
Sub query : The inner query is executed only once. The inner query will get
executed first and the output of the inner query used by the outer query.
The inner query is not dependent on outer query.

Correlated sub query: The outer query will get executed first and for every
row of outer query, inner query will get executed. So the inner query will get
executed as many times as number of rows in the result of the outer query.
The outer query output can use the inner query output for comparison. This
means inner query and outer query dependent on each other
Corelated Nested Queries:
If the inner query is dependent on the outer query, we will have a correlated
sub query.
The inner query depends on the row that is currently being
examined in the outer query.

Find the names of sailors who have reserved boat number 103.
• Select s.sname from sailors s where exists(select * from reserves r where
r.bid=103 and r.sid=s.sid);

• Thus, for each sailor row s, we test whether the set of reserves rows r such that r.bid=103 and
s.sid = r.sid is nonempty.
• If so, sailor s has reserved boat 103, and we retrieve the name.
• The subquery clearly depends on the current row s and must be re-evaluated for each row in
sailors.
• The occurrence of s in the subquery(in the form of the literal s.sid) is called a correlation, and
such queries are called correlated queries.
Find the names of sailors who have not reserved boat number 103.

• Select s.sname from sailors s where not exists(select * from reserves r

where r.bid=103 and r.sid=s.sid);
Set-Comparison Operators:
• IN: The in operator allows us to test whether a value is in a given set of
elements.
• Exists: The exists operator is used to test for the existence of any record
in a subquery. The exists operator returns true if the subquery returns
one or more records.
• Any:
• All:
Find sailors whose rating is better than some sailor called Horatio.
• Select s.sid from sailors s where s.rating >any( select s2.rating from
sailors s2 where s2.sname=‘Horatio’);

Find the sailors with the highest rating.

• Select s.sid from sailors s where s.rating >= all(select s2.rating from
sailors s2);

Note: IN and NOT IN are equivalent to = ANY and <> ALL, respectively.
EMP Table:
Dept Table: salgrade Table:
• create table emp (empno number(7) constraint emp_pk primary key, ename
varchar2(20), job varchar2(15), mgr number(10),
hiredate date, sal number(7,2), comm number(7,2),deptno number(5));
• desc emp;

• create table dept(deptno number(7) constraint dept_pk primary key,

dname varchar2(20), loc varchar2(15));
• desc dept;

• create table salgrade(grade number constraint grade_pk primary key, losal number,
hisal number);
• desc salgrade;
• insert into emp values(&empno, '&ename', '&job', &mgr, '&hiredate',
&sal, &comm, & deptno);
• commit;
• insert into dept values(&deptno, '&dname', '&loc');
• commit;
• insert into salgrade values( &grade, &losal, &hisal);
• Commit;
Count the number of sailors.
• Select count(*) from sailors s;
Count the number of different sailor names.
• Select count(distinct s.sname) from sailors s;

Find the average age of all sailors

• Select avg(s.age) from sailors s;
Find the average age of sailors with a rating of 10.
• Select avg(s.age) from sailors s where s.rating=10;
Find the name and age of the oldest sailor.
• Select s.sname, s.age from sailors s where s.age=(select max(s2.age)
from sailors s2);

Find the name and age of the youngest sailor.

• Select s.sname, s.age from sailors s where s.age=(select min(s2.age)
from sailors s2);
Group By and having clauses:

Find the age of the youngest sailor for each rating level.
• Select s.rating, min(s.age) from sailors s group by s.rating;

Find the age of the youngest sailor who is eligible to vote(i.e at least 18
years old) for each rating level with atleast two such sailors.
• Select s.rating, min(s.age) as minage from sailors s where s.age>=18
group by s.rating having count(*)>1;
Aggregate functions:

• Count(): select count(*) from emp;

• Avg(): select avg(sal) from emp;
• Max(): select max(sal) from emp;
• Min(): select min(sal) from emp;
• Sum(): select sum(sal) from emp;
Display maximum and minimum salaries of employees whose deptno is 10
• Select max(sal), min(sal) from emp where deptno=10;

Group by:
Group by is used to divide the table into subgroups so that from each
subgroup, some summary result can be obtained.
Syntax:
Select [distinct] select-list
FROM from-list
WHERE qualification
GROUP BY grouping-list
HAVING group-qualification
Display for each department what is the average salary.
• Select deptno, avg(sal) from emp group by deptno;

Display for each job type, display the average salary.

• Select job, avg(sal) from emp group by job;

In each department for each job type display the average salary.
• Select deptno, job, avg(sal) from emp group by deptno, job;

Having:
A where clause is used to restrict records, Having clause is used to
restrict subgroups.
Display the deptno and average salaries of all departments provided that
the average salary of the department is greater than or equal to 2000.
• Select deptno, avg(sal) from emp group by deptno having
avg(sal)>=2000;

Display the deptno, average salary for each department provided the dept
has min 3 people working.
• Select deptno, avg(sal) from emp group by deptno having
count(*)>=3;
ORDER BY : The SQL ORDER BY clause is used to sort the data in
ascending or descending order, based on one or more columns. Some
databases sort the query results in an ascending order by default.

Syntax The basic syntax of the ORDER BY clause is as follows −

• SELECT column-list FROM table_name [WHERE condition] [ORDER BY
column1, column2, .. columnN] [ASC | DESC];
• We can use more than one column in the ORDER BY clause.
Sorting the rows on a single column.
Display empno, ename, deptno according to department order.
• Select empno, ename, deptno from emp order by deptno;
If sorting to be done in descending order
• Select empno, ename, deptno from emp order by deptno desc;

Sorting on more than one field.

Display empno, ename and salary of all the employee's department wise in
the descending order of the salary.
• Select deptno, empno, ename, sal from emp order by deptno, sal desc;
Equijoin:
• Joining 2 or more tables on a particular column can lead to equijoin. If the
corresponding data is matching, then it is equijoin.
• The INNER JOIN keyword selects records that have matching values in both tables.
Display the employee no, ename, deptname and salary.
• select e.empno,e.ename,d.dname, e.sal from emp e, dept d where
e.deptno=d.deptno;
Syntax:
SELECT column_name(s) FROM table1 INNER JOIN table2 ON
table1.column_name = table2.column_name;
Example:
• select e.empno, e.ename, d.dname, e.sal from emp e INNER JOIN dept d on
e.deptno=d.deptno;
Non equi join:
when two or more tables have to be joined on a range of values then it
is an example of non equi join.
• select e.empno, e.ename, e.sal, s.grade from emp e, salgrade s where
e.sal between s.losal and s.hisal;
Outer Join:
Right Outer Join:
The RIGHT JOIN keyword returns all records from the right table
(table2), and the matched records from the left table (table1). The result is
NULL from the left side, when there is no match.
• select e.empno,e.ename,d.deptno,d.dname from emp e, dept d
where e.deptno(+)=d.deptno;
Syntax:
• SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON
table1.column_name = table2.column_name;
Example:
• select e.empno, e.ename, d.deptno, d.dname from emp e RIGHT JOIN
dept d on e.deptno=d.deptno;
Left Outer Join:
The LEFT JOIN keyword returns all records from the left table (table1), and
the matched records from the right table (table2). The result is NULL from
the right side, if there is no match.
• select e.empno,e.ename,d.deptno,d.dname from emp e, dept d
where e.deptno=d.deptno(+);
Syntax:
• SELECT column_name(s) FROM table1 LEFT JOIN table2 ON
table1.column_name = table2.column_name;
Example:
• select e.empno,e.ename,d.deptno,d.dname from emp e LEFT JOIN
dept d on e.deptno=d.deptno;
Full Outer Join: The FULL OUTER JOIN keyword return all records when
there is a match in either left (table1) or right (table2) table records.
Syntax:
SELECT column_name(s) FROM table1 FULL OUTER JOIN table2 ON
table1.column_name = table2.column_name;
Example:
• select e.empno, e.ename, d.deptno, d.dname from emp e FULL
OUTER JOIN dept d on e.deptno=d.deptno order by d.deptno;
Self Join: Joining a table to itself is called selfjoin.
A self join is a regular join, but the table is joined with itself.
Syntax:
SELECT column_name(s) FROM table1 T1, table1 T2 WHERE condition;
Example:
• select e.empno employee, e.ename name, m.empno manager,
m.ename mgrname from emp e, emp m where e.mgr=m.empno;
 SQL Functions

1. Number Functions
2. Character Functions
3. Date Functions
4. Group Functions(Aggregate Functions)
select * from dual;
dual is a dummy table. It contains only one row and one column.

Number Functions:
1. Round():
• select round(15.143,1) from dual;
• select round(15.193,1) from dual;
• select round(15.193,2) from dual;
• select round(15.193) from dual;
2. Truncate():
• select trunc(32.567,2) from dual;
• select trunc(32.567,1) from dual;
• select trunc(32.53) from dual;
• select trunc(123.45,-1) from dual;
• select trunc(123.45,-2) from dual;
• select trunc(123.45,-3) from dual;

3. power():
• select power(2,4) from dual;

4. mod():
• select mod(5,2) from dual;
5. Sqrt():
• select sqrt(25) from dual;
6. abs():
• select abs(-15) from dual;
7. sign():
Syntax: sign(n)
It returns the sign specification of a number.
If n<0, return -1
If n=0, return 0
If n>0, return 1
• select sign(35) from dual;
• select sign(0) from dual;
• select sign(-3) from dual;
8. Ceil():
Returns the smallest integer>=n
The adjustment is done to the highest nearest decimal value.
Syntax: ceil(n)
• select ceil(14.27) from dual;
• select ceil(14) from dual;

9. floor():
Returns the largest number<=n
The adjustment is done to the lowest nearest decimal value.
Syntax: floor(n)
• select floor(14.27) from dual;
• select floor(15.7) from dual;
Character Functions:
1. lower():
• select lower(ename) from emp;
• select lower('ORACLE') from dual;
2. upper():
• select upper(ename) from emp;
• select upper('oracle') from dual;
3. initcap():
• select initcap(ename) from emp;
• select initcap('read') from dual;
4. concat():
• select concat(empno,ename) from emp;
• select concat('orcl','corp') from dual;
5. length():
• select length(ename) from emp;
• select length('oracle') from dual;
6. substr():
• select substr('hello',2,4) from dual;
• select substr('helloworld',6,3) from dual;
7. lpad():
• select lpad(ename,10,'*') from emp;
8. rpad():
• select rpad(ename,10,'*') from emp;
9. replace():
• select replace(ename,'H','Z') from emp;
• select replace('software','soft','hard') from dual;
10. trim():
• It enables to trim heading or trailing characters or both from a
character string.
• select trim('S' from 'MTHSS') from dual;
• select trim('S' from ‘SMITH') from dual;
• select trim('S' from ‘SMITHS') from dual;
Date Functions:
• Display the current date.
• select sysdate from dual;

• select empno, ename, months_between(sysdate,hiredate) from emp;