Chapter Five

Create table students

( sid number(9),
sname varchar2(20),
sp varchar2(5),
GPA number(5,2));

Insert into students values(2019223, 'Ali', 'cs', 70);

Insert into students values(2018234, 'Fatma', 'ac', 25);
Insert into students values(2017344, 'Yousif', 'cs', 77);
Insert into students values(2017976, 'Ahmed', 'mis', 44);
Insert into students values(2015098, 'Salam', 'ac', 66);
Insert into students values(2016783, 'Wafa', 'cs', 32);
Insert into students values(2018643, 'Jamila', 'mis', 34);
Insert into students values(2015420, 'saif', 'cs', 90);
Select * from students;

Ex: Show the sid and sname from the table students for the students with gpa less
than 60.
Select sid, sname from students where gpa <60;

Ex: Show all the students table records.

Select * from students;

Ex: Show the sid and sname from the table students for the students with gpa
greater than 50.
Select sid, sname from students where gpa > 50;

Ex: Show sid and sname fields for all the students table records.

Select sid, sname from students;

Ex: Show sid and sname fields for all the students table records that owned by
201916020.

Select sid, sname from [Link];

Create table books
( book_id number(9),
book_q number(9),
book_price
number(9,3));

Insert into books values(1,10,5.2);

Insert into books values(2,5, 4.6);
Insert into books values(3,4,6.3);
Insert into books values(4,5,5.7);
Insert into books values(5,10,2.8);
Insert into books values(6,4,5.9);
Select * from books;

Ex: Show the output for the following command:

Select Book_q from Books;
Books
Book_i Book_q Book_price
d
1 10 5
2 5 4
3 4 6
4 5 5
5 10 2
6 4 5

The Output:
Book_q
10
5
4
5
10
4

Ex: Show the output for the following command:

Select DISTINCT Book_q from Books;
Ex: Show the output for the following command:
Select Book_q from Books where Book_q > 5;
Books
Book_i Book_q Book_price
d
1 10 5
2 5 4
3 4 6
4 5 5
5 10 2
6 4 5

The Output:
Book_q
10
10

Ex: Show the output for the following command:

Select DISTINCT Book_q from Books where book_q >5;

Books
Book_i Book_q Book_price
d
1 10 5
2 5 4
3 4 6
4 5 5
5 10 2
6 4 5
7 5 7

The Output:
Book_q
10
5
4
Ex: Given the following table:
Show the students id and students names for the students with GPA greater than
65.
S_id S_name GPA
2019200 Ali 65
9
2020863 Ahmed 60
2
2018563 Mohammed 76
8
2017097 Zaid 90
3
2016373 Fatma 83
3

Select s_id, s_name from students where GPA>65;

Ex: show the output for the above command.

S_id S_name
2018563 Mohammed
8
2017097 Zaid
3
2016373 Fatma
3

Ex: Show sid, sname, and GPA fields for all the students table records for students
which have GPA less than 60 and there sid greater than or equal to 2016000.

Select sid, sname, GPA from students where ((GPA<60) and (sid >= 2016000));

Create table students ( sid number(9), sname varchar2(20), sp varchar2(5), GPA

number(5,2));
Insert into students values(2015223, 'Ali', 'cs', 70);
Insert into students values(2014234, 'Fatma', 'ac', 25);
Insert into students values(2017344, 'Yousif', 'cs', 77);
Insert into students values(2017976, 'Ahmed', 'mis', 44);
Insert into students values(2015098, 'Salam', 'ac', 66);
Insert into students values(2015783, 'Wafa', 'cs', 32);
Insert into students values(2018643, 'Jamila', 'mis', 34);
Insert into students values(2015420, 'saif', 'cs', 90);
Select * from students;

Ex: Show the output for the following command:

Select sid, sname, GPA from students where ((GPA<60) and (sid >= 2016000));

students
sid sname GPA
201616087 Ahmed 65
201616232 Fatma 87
201616276 Ali 55
201616067 Wafa 57
201516034 Ahlam 55
201416067 Salim 78

The output:
sid sname GPA
20161627 Ali 55
6
20161606 Wafa 57
7

Ex: Show sid and sname fields for all the students table records for students which
have GPA greater than 60 or there sid less than or equal to 2013000.

Select sid, sname from students where ((GPA> 60) or (sid <= 2013000));

Ex: Show the output for the following command:

Select sid, sname from students where ((GPA> 60) or (sid <= 2013000));

students
sid sname GPA
201616087 Ahmed 65
201616232 Fatma 87
 201616276 Ali 55
201616067 Wafa 57
201316034 Ahlam 55
201412067 Salim 78

The output:
sid sname
20161608 Ahmed
7
20161623 Fatma
2
20141206 Salim
7

Ex: Show the output for the following command:

Select sid, sname from students where GPA is null;

Insert into students (sid ,sname ) values(2015711, 'Al Ahmed');

Insert into students (sid ,sname ) values(2018622, 'Naeima');
Insert into students (sid ,sname ) values(2015433, 'Maathir');
Select * from students;
students
sid sname GPA
201616087 Ahmed 65
201616232 Fatma
201616276 Ali
201616067 Wafa 57
201316034 Ahlam
201412067 Salim 78

The output:
Sid sname
20161623 Fatma
2
20161627 Ali
6
20131603 Ahlam
4
Ex: Show the output for the following command:
Select sid, sname from students where GPA is not null;
students
Sid Sname GPA
20161608 Ahmed 65
7
20161623 Fatma
2
20161627 Ali
6
20161606 Wafa 57
7
20131603 Ahlam
4
20141206 Salim 78
7
20171602 Tariq
3
20182345 Mahmmod 98
5
The output:
students
sid Sname
20161608 Ahmed
7
20161606 Wafa
7
20141206 Salim
7
20182345 Mahmmod
5
Create table students ( sid number(9), sname varchar2(20), sp varchar2(5), GPA
number(5,2));
Insert into students values(2015223, 'Ali', 'cs', 70);
Insert into students values(2014234, 'Fatma', 'ac', 25);
Insert into students values(2017344, 'Yousif', 'cs', 77);
Insert into students values(2017976, 'Ahmed', 'mis', 44);
Insert into students values(2015098, 'Salam', 'ac', 66);
Insert into students values(2015783, 'Wafa', 'cs', 32);
Insert into students values(2018643, 'Jamila', 'mis', 34);
Insert into students values(2015420, 'saif', 'cs', 90);
Select * from students;

Ex: Show the output for the following command:

Select sid, sname, Sp from students where Sp in ('cs', 'mis');
Students
sid sname Sp GPA
201922 Ali Cs 70
3
201823 Fatma Ac 25
4
201734 Yousif Cs 77
4
201797 Ahmed Mis 44
6
201509 Salam Ac 66
8
201678 Wafa Cs 32
3
201864 Jamila Mis 34
3
201542 saif Cs 90
0

Output:
Students
sid sname
201922 Ali
3
201734 Yousif
4
201797 Ahmed
6
201678 Wafa
3
201864 Jamila
3
201542 saif
 0

Ex: Show the output for the following command:

Select sid, sname, Sp from students where Sp not in ('cs', 'mis');
Students
sid sname Sp GPA
201922 Ali cs 70
3
201823 Fatma ac 25
4
201734 Yousif cs 77
4
201797 Ahmed mis 44
6
201509 Salam ac 66
8
201678 Wafa cs 32
3
201864 Jamila mis 34
3
201542 saif cs 90
0

Output:
Students
sid sname
2018234 Fatma
2015098 Salam

Ex: WHERE s_class LIKE ‘_R’; represent any string of two characters length,
the first any character the second one should be R.
Ex: WHERE s_name LIKE ‘J%’; represent any string of any length, the first one
should be J.

Ex: Write character string which represent all strings start with the letter ‘L’.

‘L%’
Ex: Write character string which represent all strings start with the letter ‘L’ of
length 5.
‘L_ _ _ _’

Ex: Show the output for the following command:

Select sid, sname from students where sname like 'A%';
Students
sid sname Sp GPA
201922 Ali cs 70
3
201823 Fatma ac 25
4
201734 Yousif cs 77
4
201797 Ahmed mis 44
6
201509 Salam ac 66
8
201678 Wafa cs 32
3
201864 Jamila mis 34
3
201542 saif cs 90
0
Output:
Students
sid sname
201922 Ali
3
201797 Ahmed
6

Ex: Show sid and sname fields for all the students table records for students there
Sp start with the letter ‘c’ and of length two.
Select sid, sname, sp from students where Sp like 'c_';
Ex: Show the output for the following command:
Select sid, sname from students ORDER BY sname;
Students
sid sname Sp GPA
201922 Ali cs 70
3
201823 Fatma ac 25
4
201734 Yousif cs 77
4
201797 Ahmed mis 44
6
201509 Salam ac 66
8
201678 Wafa cs 32
3
201864 Jamila mis 34
3
201542 saif cs 90
0
Output:
Students
sid sname
201797 Ahmed
6
201922 Ali
3
201823 Fatma
4
201864 Jamila
3
201542 saif
0
201509 Salam
8
201678 Wafa
3
201734 Yousif
4

Ex: Show the output for the following command:

Select sid, sname, gpa from students ORDER BY GPA;
Students
sid sname Sp GPA
201922 Ali cs 70
3
201823 Fatma ac 25
4
201734 Yousif cs 77
4
201797 Ahmed mis 44
6
201509 Salam ac 66
8
201678 Wafa cs 32
3
201864 Jamila mis 34
3
201542 saif cs 90
0

Output:
Students
sid sname
201823 Fatma
4
201678 Wafa
3
201864 Jamila
3
201797 Ahmed
6
201509 Salam
8
201922 Ali
3
201734 Yousif
4
201542 saif
0

Ex: Show the output for the following command:

Select sid, sname, gpa from students ORDER BY GPA DESC;
Students
sid sname Sp GPA
201922 Ali cs 70
3
201823 Fatma ac 25
4
201734 Yousif cs 77
4
201797 Ahmed mis 44
6
201509 Salam ac 66
8
201678 Wafa cs 32
3
201864 Jamila mis 34
3
201542 saif cs 90
0

Output:
Students
sid sname
201542 saif
0
201734 Yousif
4
201922 Ali
3
201509 Salam
8
201797 Ahmed
6
201864 Jamila
3
201678 Wafa
3
201823 Fatma
4

Ex: Show the output for the following command:

Select sid, sname, GPA+5 from students;
Students
sid sname Sp GPA
201922 Ali cs 70
3
201823 Fatma ac 25
4
201734 Yousif cs 77
4
201797 Ahmed mis 44
6
201509 Salam ac 66
8
201678 Wafa cs 32
3
201864 Jamila mis 34
3
201542 saif cs 90
0
Output:
sid sname GPA+5
201922 Ali 75
3
201823 Fatma 30
4
201734 Yousif 82
4
201797 Ahmed 49
6
201509 Salam 71
8
201678 Wafa 37
3
201864 Jamila 39
3
201542 saif 95
0

Ex: Show the output for the following command:

Select Book_id, Book_q*Book_price from Books Order by Book_q;
Books
Book_i Book_q Book_price
d
1 10 5
2 5 4
3 4 6
4 5 5
5 10 2
6 4 5
7 5 7
The Output:
Book_i Book_q* Book_price
d
3 24
6 20
2 20
4 25
7 35
1 50
5 20

Ex: Show the output for the following command:

SELECT Book_id, Book_q*Book_price FROM Books;
Books
Book_i Book_q Book_price
d
1 10 5
2 5 4
3 4 6

Output
Book_id Book_q*B ook_price
1 50
2 20
3 24

Ex: Show the output for the following command:

SELECT Book_id, Book_q*Book_price FROM Books;
Inventary
Item_id Item_ Item_price
q
1 -10 5
2 5 4
3 -4 6

 ABS - absolute value

Abs(-5) => 5
Ex: Show the output for the following command:
SELECT Item_id, Abs(Item_q) FROM Inventry;
SELECT book_id, Abs(book_q), book_price from books;
Inventary
Item_id Item_ Item_price
q
1 -10 5
2 5 4
3 -4 6

Output
Item_id Item_q
1 10
2 5
3 4

Create table books1 ( book_id number(9), book_q number(9), book_price

number(9,3));
Insert into books1 values(1,10,5.2);
Insert into books1 values(2,5, 4.6);
Insert into books1 values(3,-4,6.3);
Insert into books1 values(4,5,5.7);
Insert into books1 values(5,-10,2.8);
Insert into books1 values(6,4,5.9);
Select * from books1;
SELECT book_id, Abs(book_q) FROM books1;

 CEIL – rounds a number up to the next integer

Ceil (3.2) => 4
 FLOOR – rounds a number down to the previous integer
Floor(5.9) => 5
 MOD – returns the remainder of a number and a divisor
Mod(9,2) =>1, mod(15,4) =>3
 POWER - raises a number to an exponent
Power(3,2) =9 , power(2,4) =>16
 ROUND - rounds a number
Round(2.3) => 2 , round(2.5) =>3
 SQRT – returns the square root of a value
Sqrt(9) =>3, sqrt(16) =>4
 TRUNC - truncates a number to the nearest whole number
Trunk(3.9) =3

Ex: Show the output for the following command:

SELECT Book_id, round(book_price) FROM Books1;

Books
Book_i Book_q Book_price
d
1 10 5.6
2 5 4.4
3 4 6.3
4 7 8.5

Output
Book_id round(Book_price)
1 6
2 4
3 6
4 9

Ex: Show the output for the following command:

SELECT Book_id, ceil(Book_price) FROM Books;

Books
Book_i Book_q Book_price
d
1 10 5.6
2 5 4.4
3 4 6.3
4 7 8.5

Output
Book_id ceil(B ook_price)
1 6
2 5
3 7
4 9

Ex: Show the output for the following command:

SELECT Book_id, power(Book_q, 2) FROM Books;
Books
Book_i Book_q Book_price
d
1 10 5.6
2 5 4.4
3 4 6.3
4 7 8.5

Output
Book_id power(Book_q, 2)
1 100
2 25
3 16
4 49
Ex: Show the output for the following command:
SELECT Book_id, trunc(Book_price) FROM Books;
Books
Book_i Book_q Book_price
d
1 10 5.6
2 5 4.4
3 4 6.3
4 7 8.5

Output
Book_id round(Book_price)
1 5
2 4
3 6
4 8

Create table students ( sid number(9), sname varchar2(20), sp varchar2(5), GPA

number(5,2));
Insert into students values(2015223, 'Ali', 'cs', 70);
Insert into students values(2014234, 'Fatma', 'ac', 25);
Insert into students values(2017344, 'Yousif', 'cs', 77);
Insert into students values(2017976, 'Ahmed', 'mis', 44);
Insert into students values(2015098, 'Salam', 'ac', 66);
Insert into students values(2015783, 'Wafa', 'cs', 32);
Insert into students values(2018643, 'Jamila', 'mis', 34);
Insert into students values(2015420, 'saif', 'cs', 90);
Select * from students;

 CONCAT – joins 2 character strings.

CONCAT('Tech on', ' the Net')
Result: 'Tech on the Net'
CONCAT('a', 'b')
Result: 'ab'
Ex: Show the output for the following command:
Select sid, concat('The student name ',sname) from students;
Students
sid sname Sp GPA
201922 Ali Cs 70
3
 201823 Fatma Ac 25
4
201734 Yousif Cs 77
4
201797 Ahmed Mis 44
6
201509 Salam Ac 66
8
201678 Wafa Cs 32
3
201864 Jamila Mis 34
3
201542 saif Cs 90
0
Output:
Sid sname
201922 The student name Ali
3
201823 The student name Fatma
4
201734 The student name Yousif
4
201797 The student name Ahmed
6
201509 The student name Salam
8
201678 The student name Wafa
3
201864 The student name Jamila
3
201542 The student name saif
0

 INITCAP – returns a string with the initial letter only uppercase.

INITCAP('tech on the net');
Result: 'Tech On The Net'
INITCAP('GEORGE BURNS');
Result: 'George Burns'
Ex: Show the output for the following command:
Select sid, initcap(sname) from students;
Students
sid sname Sp GPA
201922 ali cs 70
3
201823 fatmA ac 25
4
201734 YOUSIF cs 77
4
Output:
sid sname
2019223 Ali
2018234 Fatma
2017344 Yousif

 LENGTH – returns the length of a string

LENGTH(NULL)
Result: 4
LENGTH('')
Result: 0
LENGTH(' ')
Result: 1
LENGTH('Tech on the Net')
Result: 15
LENGTH('Tech on the Net ')
Result: 16
Ex: Show the output for the following command:
Select sid,sname, length(sname) from students;
Students
sid sname Sp GPA
201922 ali cs 70
3
201823 fatmA ac 25
4
201734 YOUSIF cs 77
4
Output:
sid Length(sname)
201922 3
 3
201823 5
4
201734 6
4

LPAD( string1, padded length [, pad string] )

Parameters or Arguments
string1: The string to pad characters to (the left-hand side).
padded length: The number of characters to return. If the padded length is smaller
than the original string, the LPAD function will truncate the string to the size of
padded length.
pad string: Optional. This is the string that will be padded to the left-hand side of
string1. If this parameter is omitted, the LPAD function will pad spaces to the left-
side of string1.
Returns
The LPAD function returns a string value.

Examples:

LPAD('tech', 7);
Result: ' tech'

LPAD('tech', 2);
Result: 'te'

LPAD('tech', 8, '0');
Result: '0000tech'

LPAD('tech on the net', 15, 'z');

Result: 'tech on the net'

LPAD('tech on the net', 16, 'z');

Result: 'ztech on the net'

Ex: Show the result of:

LPAD('Example', 9, '#');
## Example
The Oracle/PLSQL RPAD function pads the right-side of a string with a specific
set of characters (when string1 is not null).

Syntax
The syntax for the RPAD function in Oracle/PLSQL is:

RPAD( string1, padded_length [, pad_string] )

RPAD('tech', 7)
Result: 'tech '

RPAD('tech', 2)
Result: 'te'

RPAD('tech', 8, '0')
Result: 'tech0000'

RPAD('tech on the net', 15, 'z')

Result: 'tech on the net'

RPAD('tech on the net', 16, 'z')

Result: 'tech on the netz'

Ex: Show the result of:

RPAD('Example', 9, '#');
Example##

Ex: Show the output for the following command:

Select sid, LPAD (sname,8,'!') from students;
Students
sid sname Sp GPA
201922 ali cs 70
3
201823 fatmA ac 25
4
201734 YOUSIF cs 77
4
Output:
sid LPAD (sname,8,’!’)
201922 !!!!!ali
3
201823 !!!fatmA
4
201734 !!YOUSIF
4

Select sid, RPAD (sname,8,'!') from students;

 LTRIM, RTRIM – returns a string with all instances of a specific character

trimmed from the left or right side
LTRIM(' tech')
Result: 'tech'
LTRIM(' tech', ' ')
Result: 'tech'
LTRIM('000123', '0')
Result: '123'
LTRIM('123123Tech', '123')
Result: 'Tech'
LTRIM('123123Tech123', '123')
Result: 'Tech123'
Rtrim(LTRIM('123123Tech123', '123'),’123’)
Result: 'Tech'

LTRIM('xyzxyzTech', 'xyz')
Result: 'Tech'
LTRIM('6372Tech', '0123456789')
Result: 'Tech'

RTRIM('tech ')
Result: 'tech'

RTRIM('tech ', ' ')

Result: 'tech'

RTRIM('123000', '0')
Result: '123'
RTRIM('Tech123123', '123')
Result: 'Tech'

RTRIM('123Tech123', '123')
Result: '123Tech'

RTRIM('Techxyxzyyy', 'xyz')
Result: 'Tech'

RTRIM('Tech6372', '0123456789')
Result: 'Tech'

Ex: Find the result of:

LTRIM('Example', 'E')
Xample

Select sid,sname, ltrim(sname,'A') from students;

Select sid,sname, rtrim(sname,'f') from students;

 REPLACE('222tech', '2', '3');

Result: '333tech'

REPLACE('0000123', '0');
Result: '123'

REPLACE('0000123', '0', ' ');

Result: ' 123'

Ex: Show the result of:

REPLACE(‘Example’,’e’,’#’)
Exampl#

Select sid,sname, replace(sname,'a','5') from students;

SID SNAME REPLACE(SNAME,'A','5')

2015223 Ali Ali
2014234 Fatma F5tm5
2017344 Yousif Yousif
2017976 Ahmed Ahmed
2015098 Salam S5l5m
2015783 Wafa W5f5
2018643 Jamila J5mil5
2015420 saif s5if

 UPPER('Tech on the Net')

Result: 'TECH ON THE NET'

UPPER('george burns 123 ')

Result: 'GEORGE BURNS 123 '

LOWER('Tech on the Net');

Result: 'tech on the net'

LOWER('GEORGE BURNS 123 ');

Result: 'george burns 123 '
Select sid, upper (sname) from students;
Ex: Show the output for the following command:
Select sid, LOWER (sname) from students;
Students
sid sname Sp GPA
201922 ali cs 70
3
201823 fatmA ac 25
4
201734 YOUSIF cs 77
4
Output:
sid LOWER (sname)
201922 ali
3
201823 fatma
4
201734 yousif
4

Ex: Show the output for the following command:

Select sid, INITCAP(LOWER (sname)) from students;
Students
sid sname Sp GPA
 201922 ali cs 70
3
201823 fatmA ac 25
4
201734 YOUSIF cs 77
4
Output:
sid LOWER (sname)
201922 Ali
3
201823 Fatma
4
201734 Yousif
4
Select sid,sname, lower(sname) from students;

Create table books

( b_id number(5), bname varchar2(20) , Author varchar2(20) , pdate date, ddate
date);
Insert into books values (1,'Oracle', 'Thomas', To_Date('19/08/2002',
'DD/MM/YYYY'), To_Date('19/09/2002', 'DD/MM/YYYY'));
Insert into books values (2,'DB', 'CJ Date', To_Date('20/10/2025',
'DD/MM/YYYY'), To_Date('20/12/2025', 'DD/MM/YYYY'));
Insert into books values (3,'AI', 'Luger', To_Date('5/2/2010', 'DD/MM/YYYY'),
To_Date('5/5/2010', 'DD/MM/YYYY'));
Insert into books values (1,'Compiler', 'Auho', To_Date('7/08/2015',
'DD/MM/YYYY'), To_Date('19/09/2015', 'DD/MM/YYYY'));
select * from books;

Ex: Show the output for the following command:

SELECT Bname, pdate + 30 FROM Books;
Books
Book_id Bname Author pdate
1 AI Luger 1-1-1999
2 DB CJdat 5-2-2010
3 compiler Auho 7-8-2015
4 organizatio Kim 4-6-2001
n
Output:
 Bname Pdate+30
AI 1-2-1999
DB 5-3-2010
compiler 7-9-2015
organization 4-7-2001
Ex: Show the output for the following command:
SELECT Bname, ddate-pdate FROM Books;
Books
Book_i Bname Author pdate ddate
d
1 AI Luger 1-1-1999 1-3-1999
2 DB CJdat 5-2-2010 28-2-2010
3 compiler Auho 7-8-2015 15-8-2015
4 organization Kim 4-6-2001 20-6-2002
Output:
Bname Ddate- pdate
AI 59
DB 23
compiler 8
organizatio 381
n

Ex: Show the output for the following command:

SELECT ADD_MONTHS(SYSDATE, 6) FROM dual;
Today date is 26/06/2024
Add 6 months => 26/12/2024

Ex: Show the output for the following command:

SELECT Bname, ADD_MONTHS(pdate,3) FROM Books;
Books
Book_id Bname Author pdate
1 AI Luger 1-1-1999
2 DB CJdat 5-2-2010
3 compiler Auho 7-8-2015
4 organizatio Kim 4-6-2001
n
Output:
Bname ADD_MONTHS(pdate,3)
AI 1-4-1999
DB 5-5-2010
compiler 7-11-2015
organizatio 4-9-2001
n

Ex: Show the output for the following command:

SELECT Bname, LAST_DATE(pdate) FROM Books;
Books
Book_id Bname Author pdate
1 AI Luger 1-1-1999
2 DB CJdat 5-2-2010
3 compiler Auho 7-8-2015
4 organizatio Kim 4-6-2001
n
Output:
Bname LAST_DATE(pdate)
AI 31-1-1999
DB 29-2-2010
compiler 31-8-2015
organizatio 30-6-2001
n
Ex: Show the output for the following command:
SELECT Bname, months_between( sysdate,pdate) FROM Books;
Books
Book_id Bname Author pdate
1 AI Luger 1-1-1999
2 DB CJdat 5-2-2010
3 compiler Auho 7-8-2015
4 organizatio Kim 4-6-2000
n
Today date 31/03/2021
The number of months between today and a specific date will be calculated as
follows:
Difference between years 2021-1999 = 22 years
Number of months in 22 years = 22*12= 264
The difference in months = 3-1 =2
So the total number of moths = 264+2= 266
Difference between years 2021-2015 = 6 years
Number of months in 22 years = 6*12= 72
The difference in months = 3-8 =-5
So the total number of moths = 72-5= 67

AI 266
DB 133
compiler 67
organization 249

Ex: Show the output for the following command:

SELECT Bname, months_between(pdate, ddate) FROM Books;
Books
Book_i Bname Author Pdate ddate
d
1 AI Luger 1-1-1999 1-3-1999
2 DB CJdat 5-2-2010 30-2-2010
3 compiler Auho 7-8-2015 15-8-2015
4 organization Kim 4-6-2001 20-6-2001
Output:
Bname months_between(pdate, ddate)
AI 2
DB 0
compiler 0
organization 0

(last year-firstyear)*12+(lastmnth-firstmonth)

Ex: Show the output for the following command:

SELECT ADD_MONTHS(SYSDATE, 6) FROM dual;
Today is 05-04-2021
05-10-2021

Create table st1

( sid number(5), name varchar2(20) , dob date, CONSTRAINT carscnopk1
PRIMARY KEY(sid));
Insert into st1 (sid, name, dob) values (2,'Musaab', To_Date('19/08/2002',
'DD/MM/YYYY'));
Insert into st1 (sid, name, dob) values (1,'Mohameed', To_Date('24/09/2006',
'DD/MM/YYYY'));
Insert into st1 (sid, name, dob) values (4,'Mannar', To_Date('16/02/2001',
'DD/MM/YYYY'));
Insert into st1 (sid, name, dob) values (5,'Osama', To_Date('01/01/2005',
'DD/MM/YYYY'));
Insert into st1 (sid, name, dob) values (6,'Bayan', To_Date('08/08/2002',
'DD/MM/YYYY'));
Insert into st1 (sid, name, dob) values (7,'Rayan', To_Date('15/11/2001',
'DD/MM/YYYY'));
Insert into st1 (sid, name, dob) values (7,'Louay', To_Date('24/09/1973',
'DD/MM/YYYY'));

Select * from st1;

SELECT name,dob, trunc((SYSDATE-dob)/365) FROM st1;
SELECT name,dob, LAST_DATE(dob) FROM st1;

SELECT name,dob, months_between( sysdate,dob) FROM st1;

Drop table students;

Create table students ( sid number(9), sname varchar2(20), sp varchar2(5), GPA
number(5,2));
Insert into students values(2015223, 'Ali', 'cs', 70);
Insert into students values(2014234, 'Fatma', 'ac', 25);
Insert into students values(2017344, 'Yousif', 'cs', 77);
Insert into students values(2017976, 'Ahmed', 'mis', 44);
Insert into students values(2015098, 'Salam', 'ac', 66);
Insert into students values(2015783, 'Wafa', 'cs', 32);
Insert into students values(2018643, 'Jamila', 'mis', 34);
Insert into students values(2015420, 'saif', 'cs', 90);
Select * from students;

Ex: Show the output for the following command:

Select sum(GPA) from students;

Students
sid Sname Sp GPA
201922 Ali Cs 70
3
 201823 fatmA Ac 25
4
201734 YOUSIF cs 77
4
Output:
172
Ex: Show the output for the following command:
Select Max(GPA) from students;
Students
sid Sname Sp GPA
201922 Ali cs 70
3
201823 fatmA ac 25
4
201734 YOUSIF cs 77
4
Output:
77

Ex: Show the output for the following command:

Select Min(GPA) from students;
Students
sid Sname Sp GPA
201922 Ali cs 70
3
201823 fatmA ac 25
4
201734 YOUSIF cs 77
4
Output:25
Ex: Show the output for the following command:
Select Avg(GPA) from students;
Students
sid Sname Sp GPA
201922 Ali cs 70
3
201823 fatmA ac 25
4
201734 YOUSIF cs 77
 4
Output:
57.3

Select count(GPA) from students where gpa >= 50;

Create table books ( book_id number(9), book_q number(9), book_price

number(9,3));
Insert into books values(1,10,5.2);
Insert into books values(2,5, 4.6);
Insert into books values(3,4,6.3);
Insert into books values(4,5,5.7);
Insert into books values(5,10,2.8);
Insert into books values(6,4,5.9);
Select * from books;
Ex: Show the output for the following command:
SELECT count (Book_q) FROM Books;
Books
Book_i Book_q Book_price
d
1 10 5.6
2 5 4.4
3 4 6.3
4 7 8.5
The output:
4
Ex: Show the output for the following command:
SELECT count(Book_q) FROM Books where Book_price > 6;
Books
Book_i Book_q Book_price
d
1 10 5.6
2 5 4.4
3 4 6.3
4 7 8.5
The output:
2
Ex: Show the output for the following command:
Select Avg(Book_q) from Books where Book_price<6;
Books
Book_i Book_q Book_price
d
1 10 5.6
2 5 4.4
3 4 6.3
4 7 8.5
Output:
(10+5)/2 = 7.5

SELECT SP, AVG(gpa) FROM students GROUP BY Sp;

SELECT SP, max(gpa) FROM students GROUP BY Sp;

Create table books ( book_id number(9), sp varchar2(20), book_q number(9),

book_price number(9,3));
Insert into books values(1, 'cs',10,5.6);
Insert into books values(2, 'ac',5, 4.4);
Insert into books values(3, 'cs',4,6.3);
Insert into books values(4, 'mis',7,8.5);
Insert into books values(5, 'ac',6,6.8);
Insert into books values(6, 'cs',2,7.4);
Insert into books values(7, 'mis',8,2.5);
Select * from books;

Ex: Show the output for the following command:

SELECT SP, AVG(Book_q) FROM Books GROUP BY Sp;
Books
Book_i Sp Book_q Book_price
d
1 cs 10 5.6
2 ac 5 4.4
3 cs 4 6.3
4 mis 7 8.5
5 ac 6 6.8
6 cs 2 7.4
7 mis 8 2.5
Output:
Sp Av(Book_q)
cs 5.3
ac 5.5
mis 7.5

Ex: Show the output for the following command:

SELECT SP, count(Book_q) FROM Books GROUP BY Sp;
Books
Book_i Sp Book_q Book_price
d
1 cs 10 5.6
2 ac 5 4.4
3 cs 4 6.3
4 mis 7 8.5
5 ac 6 6.8
6 cs 2 7.4
7 mis 8 2.5
Output:
Sp Count(Book_q)
cs 3
ac 2
mi 2
s

Ex: Show the output for the following command:

SELECT SP, max(Book_price) FROM Books GROUP BY Sp;
Books
Book_i Sp Book_q Book_price
d
1 cs 10 5.6
2 ac 5 4.4
3 cs 4 6.3
4 mis 7 8.5
5 ac 6 6.8
6 cs 2 7.4
7 mis 8 2.5
Output:
Sp max(Book_price)
cs 7.4
ac 6.8
mis 8.5
SELECT SP "Specilization", max(gpa) "Students with High GPA" FROM students
GROUP BY Sp;

SELECT Sp "Specilization", min(gpa) "Min GPA For each Specialization "

FROM students GROUP BY Sp;

Ex: Show the output for the following command:

SELECT Sp "Specilization", min(Book_q) “Min Book Quantity For each
Specialization” FROM Books GROUP BY Sp;
Books
Book_i Sp Book_q Book_price
d
1 cs 10 5.6
2 ac 5 4.4
3 cs 4 6.3
4 mis 7 8.5
5 ac 6 6.8
6 cs 2 7.4
7 mis 8 2.5
8 cs 3 6.1
The output:
Specilization Min Book Quantity For each Specialization
cs 2
ac 5
mis 7

Create table books

( b_id number(5), bname varchar2(20) , Author varchar2(20) , pdate date, ddate
date);
Insert into books values (1,'Oracle', 'Thomas', To_Date('19/08/2002',
'DD/MM/YYYY'), To_Date('19/09/2002', 'DD/MM/YYYY'));
Insert into books values (2,'DB', 'CJ Date', To_Date('20/10/2025',
'DD/MM/YYYY'), To_Date('20/12/2025', 'DD/MM/YYYY'));
Insert into books values (3,'AI', 'Luger', To_Date('5/2/2010', 'DD/MM/YYYY'),
To_Date('5/5/2010', 'DD/MM/YYYY'));
Insert into books values (1,'Compiler', 'Auho', To_Date('7/08/2015',
'DD/MM/YYYY'), To_Date('19/09/2015', 'DD/MM/YYYY'));
select * from books;
Ex: Show the output for the following command:
SELECT Bname "The Book Name" , Author "The Author Name" FROM
Books;
Books
Book_id Bname Author pdate
1 AI Luger 1-1-
1999
2 DB CJdat 5-2-
2010
3 compiler Auho 7-8-
2015
4 organizatio Kim 4-6-
n 2001
The output:
The Book Name The Author Name
AI Luger
DB CJdat
compiler Auho
organization Kim

SELECT
sp AS specialization,
MIN(gpa) "Min GPA For each Specialization "
FROM
students
GROUP BY
sp;

Ex: Show the output for the following command:

SELECT Sp AS specialization, min(Book_q) “Min Book Quantity For each
Specialization” FROM Books GROUP BY specialization;
Books
Book_i Sp Book_q Book_price
d
1 cs 10 5.6
2 ac 5 4.4
3 cs 4 6.3
4 mis 7 8.5
 5 ac 6 6.8
6 cs 2 7.4
7 mis 8 2.5
8 cs 3 6.1
The output:
specialization Min Book Quantity For each Specialization
cs 2
ac 5
mis 7

SELECT Sp ,max(gpa) FROM students where (SP=&SP) group by Sp;

SELECT sname, gpa FROM students where (gpa<&p);

Ex: Show the output for the following command:

SELECT Sp ,max(Book_q) FROM Books where (SP = &p) group by Sp;
Books
Book_i Sp Book_q Book_price
d
1 cs 10 5.6
2 ac 5 4.4
3 cs 4 6.3
4 mis 7 8.5
5 ac 6 6.8
6 cs 2 7.4
7 mis 8 2.5
8 cs 3 6.1
The output:
Enter value for p: mis
Sp max(Book_q)
mis 8

The output:
Enter value for p: cs
Sp max(Book_q)
mis 10
Ex: Write queries to define a variable called x1 with initial value 5; then use that
variable to show the book_id and book_q for all the books with quantity greater
than or equal to x1.

Define x1 = 5;
SELECT Book_id , Book_q FROM Books1 where (Book_q >= &x1);

Ex: Show the output for the following command:

Define x2 = 50;
SELECT sname, gpa FROM students where (gpa<&x2);

SELECT Book_id , Book_q, FROM Books where (Book_q >= &x1);

Books
Book_i Sp Book_q Book_price
d
1 cs 10 5.6
2 ac 5 4.4
3 cs 4 6.3
4 mis 7 8.5
5 ac 6 6.8
6 cs 2 7.4
7 mis 8 2.5
8 cs 3 6.1
Old 1: where (Book_q >= &x1)
New 1: where (Book_q >= 5)
The output:
Book_id Book_q
1 10
2 5
4 7
5 6
7 8

Ex: Write query to show the book_id, book_q, and print_date for all the books with
quantity greater than or equal to 7; show Print_date in the format ‘MM:DD:YY’.

SELECT Book_id , Book_q, TO_CHAR(Print_date, ‘MM:DD:YY’)

FROM Books where (Book_q >= 7);

Ex: Show the output for the above query.

Books
Book_id Sp Book_q Book_pric Print_date
e
1 cs 10 5.6 1/1/2009
2 ac 5 4.4 2/3/2010
3 cs 4 6.3 22/9/2015
4 mis 7 8.5 12/7/1999
5 ac 6 6.8 30/1/2000
6 cs 2 7.4 5/8/1996
7 mis 8 2.5 7/1/2016
8 cs 3 6.1 6/3/2017

Book_id Book_q Print_date

1 10 01:01:09
4 7 07:12:99
7 8 01:07:16

Drop table st1;

Create table st1 ( sid number(5) primary key, name varchar2(20) , dob date);
Insert into st1 (sid, name, dob) values (2,'Musab', To_Date('19/08/2002',
'DD/MM/YYYY'));
Insert into st1 (sid, name, dob) values (1,'Mohameed', To_Date('24/09/2006',
'DD/MM/YYYY'));
Insert into st1 (sid, name, dob) values (4,'Manar', To_Date('16/02/2001',
'DD/MM/YYYY'));
Insert into st1 (sid, name, dob) values (5,'Sara', To_Date('02/09/2001',
'DD/MM/YYYY'));
Select * from st1;

SELECT sid , name, TO_CHAR(dob,'MM:DD:YY') FROM st1;

SELECT
sid,
name,
to_char(dob, 'MON-DAY YYYY')
FROM
st1;