SQL> select *from classroom;
BUILDING
ROOM_NU CAPACITY
--------------- ------- ----------
packard
101
500
painter
514
10
taylor
3128
70
watson
100
30
watson
120
50
SQL> select *from department;
DEPT_NAME BUILDING
---------- --------------- ----------
biology watson
90000
comp.sci taylor
100000
elec.eng. taylor
85000
finance painter
120000
history painter
50000
music
packard
80000
physics watson
70000
7 rows selected.
SQL> select *from course;
COURSE_I TITLE
DEPT_NAME
-------- -------------------------------------------------- ----------------
CREDITS
----------
bio-101 intro. to biology
biology
4
bio-399 computational biology
3
cs-101 intro. to computer science
comp.sci
4
COURSE_I TITLE
DEPT_NAME
-------- -------------------------------------------------- ----------------
CREDITS
----------
cs-190 game design
comp.sci
4
cs-315 robotics
3
cs-319 image processing
comp.sci
3
COURSE_I TITLE
DEPT_NAME
-------- -------------------------------------------------- ----------------
CREDITS
----------
cs-347 database system concepts
comp.sci
3
ee-181 intro. to digital systems
3
fin-201 investment banking
finance
3
COURSE_I TITLE
DEPT_NAME
-------- -------------------------------------------------- ----------------
CREDITS
----------
his-351 world history
history
3
mu-199 music video production
3
phy-101 physical principles
physics
4
COURSE_I TITLE
DEPT_NAME
-------- -------------------------------------------------- ----------------
CREDITS
----------
bio-301 genetics
biology
4
13 rows selected.
SQL> select *from instructor;
ID NAME
DEPT_NAME
SALARY
----- -------------------- -------------------- ----------
10101 srinivasan
comp.sci
65000
12121 wu
finance
90000
15151 mozart
music
40000
22222 einsten
physics
95000
32343 el said
history
60000
33456 gold
physics
87000
45565 katz
comp.sci
75000
58583 califieri
history
62000
76543 singh
finance
80000
76766 crick
biology
72000
83821 brandt
comp.sci
92000
ID NAME
DEPT_NAME
SALARY
----- -------------------- -------------------- ----------
98345 kim
elec.eng.
80000
12 rows selected.
SQL> select *from section;
COURSE_I SEC_ID SEMEST
YEAR BUILDING
-------- -------- ------ ---------- --------------- ------- ----
bio-101 1
summer
2009 painter
514 b
bio-301 1
summer
2010 painter
514 a
cs-101 1
fall
2009 packard
101 h
cs-101 1
spring
2010 packard
101 f
cs-190 1
spring
2009 taylor
3128 e
cs-190 2
spring
2009 taylor
3128 a
cs-315 1
spring
2010 watson
120 d
cs-319 1
spring
2010 watson
100 b
cs-319 2
spring
2010 taylor
3128 c
cs-347 1
fall
2009 taylor
3128 a
ee-181 1
spring
2009 taylor
3128 c
COURSE_I SEC_ID SEMEST
YEAR BUILDING
-------- -------- ------ ---------- --------------- ------- ----
fin-201 1
spring
2010 packard
101 b
his-351 1
spring
2010 painter
514 c
mu-199 1
spring
2010 packard
101 d
phy-101 1
fall
2009 watson
100 a
15 rows selected.
SQL> select *from teaches;
ID COURSE_I SEC_ID SEMEST
----- -------- -------- ------ ----------
10101 cs-101 1
fall
2009
10101 cs-315 1
spring
2010
10101 cs-347 1
fall
2009
12121 fin-201 1
spring
2010
15151 mu-199 1
spring
2010
22222 phy-101 1
fall
2009
32343 his-351 1
spring
2010
45565 cs-101 1
spring
2010
45565 cs-319 1
spring
2010
76766 bio-101 1
summer
2009
76766 bio-301 1
summer
2010
ID COURSE_I SEC_ID SEMEST
----- -------- -------- ------ ----------
83821 cs-190 1
spring
2009
83821 cs-190 2
spring
2009
83821 cs-319 2
spring
2010
98345 ee-181 1
spring
2009
15 rows selected.
SQL> select *from student;
ID NAME
DEPT_NAME
TOT_CRED
----- -------------------- -------------------- ----------
00128 zhang
comp.sci
102
12345 shankar
comp.sci
32
19991 brandt
history
80
23121 chavez
finance
110
44553 peltier
physics
56
45678 levy
physics
46
54321 williams
comp.sci
54
55739 sanchez
music
38
70557 snow
physics
0
76543 brown
comp.sci
58
76653 aoi
elec.eng.
60
ID NAME
DEPT_NAME
TOT_CRED
----- -------------------- -------------------- ----------
98765 bourikas
elec.eng.
98
98988 tanaka
biology
120
13 rows selected.
SQL> select *from takes;
ID COURSE_I SEC_ID SEMEST
----- -------- -------- ------ ---------- --
00128 cs-101 1
fall
2009 a
00128 cs-347 1
fall
2009 a-
12345 cs-101 1
fall
2009 c
12345 cs-190 2
spring
2009 a
12345 cs-315 1
spring
2010 a
12345 cs-347 1
fall
2009 a
19991 his-351 1
spring
2010 b
23121 fin-201 1
spring
2010 c+
44553 phy-101 1
fall
2009 b-
45678 cs-101 1
fall
2009 f
45678 cs-101 1
ID COURSE_I SEC_ID SEMEST
YEAR GR
----- -------- -------- ------ ---------- --
45678 cs-319 1
spring
2010 b
54321 cs-101 1
fall
2009 a-
54321 cs-190 2
spring
2009 b+
55739 mu-199 1
spring
2010 a-
76543 cs-101 1
fall
2009 a
76543 cs-319 2
spring
2010 a
76653 ee-181 1
spring
2009 c
98765 cs-101 1
fall
2009 c-
98765 cs-315 1
spring
2010 b
98988 bio-101 1
summer
2009 a
98988 bio-301 1
summer
2010
22 rows selected.
DML QUERIES
1) SQL> select distinct dept_name from instructor;
DEPT_NAME
--------------------
finance
biology
comp.sci
music
physics
elec.eng.
history
7 rows selected.
2)SQL> select all title from course;
TITLE
--------------------------------------------------
intro. to biology
computational biology
intro. to computer science
game design
robotics
image processing
database system concepts
intro. to digital systems
investment banking
world history
spring
2010 b+
music video production
TITLE
--------------------------------------------------
physical principles
genetics
13 rows selected.
3) SQL> select name from instructor where dept_name='comp.sci' and
salary>70000;
NAME
--------------------
katz
brandt
4) SQL> select name,instructor.dept_name,building from instructor,department
where instructor.dept_name=department.dept_name;
NAME
DEPT_NAME
BUILDING
-------------------- -------------------- ---------------
srinivasan
comp.sci
taylor
wu
finance
painter
mozart
music
packard
einsten
physics
watson
el said
history
painter
gold
physics
watson
katz
comp.sci
taylor
califieri
history
painter
singh
finance
painter
crick
biology
watson
brandt
comp.sci
taylor
NAME
DEPT_NAME
BUILDING
-------------------- -------------------- ---------------
kim
elec.eng.
taylor
12 rows selected.
5) SQL> select name,course_id from instructor,teaches where
instructor.ID=teaches.ID and instructor.dept_name='comp.sci';
NAME
COURSE_I
-------------------- --------
srinivasan
cs-101
srinivasan
cs-315
srinivasan
cs-347
katz
cs-101
katz
cs-319
brandt
cs-190
brandt
brandt
8 rows selected.
6) SQL> select name from student where name like '%an%';
NAME
--------------------
zhang
shankar
brandt
sanchez
tanaka
7) SQL> select name from student where tot_cred between 50 and 80;
NAME
--------------------
brandt
peltier
williams
brown
aoi
JOIN AND SET OPERATIONS
WHERE CLAUSE PREDICATES
1) SQL> select name,course_id from instructor,teaches where
instructor.ID=teaches.ID;
NAME
COURSE_I
-------------------- --------
srinivasan
cs-101
srinivasan
cs-315
srinivasan
cs-347
wu
fin-201
mozart
mu-199
einsten
phy-101
el said
his-351
katz
cs-101
katz
cs-319
crick
bio-101
crick
bio-301
NAME
COURSE_I
-------------------- --------
brandt
cs-190
brandt
cs-190
cs-190
cs-319
brandt
kim
15 rows selected.
NATURAL JOIN
2) SQL> select name, course_id from instructor natural join teaches;
NAME
COURSE_I
-------------------- --------
srinivasan
cs-101
srinivasan
cs-315
srinivasan
cs-347
wu
fin-201
mozart
mu-199
einsten
phy-101
el said
his-351
katz
cs-101
katz
cs-319
crick
bio-101
crick
bio-301
NAME
COURSE_I
-------------------- --------
brandt
cs-190
brandt
cs-190
brandt
cs-319
kim
ee-181
15 rows selected.
3) SQL> select name,title from instructor natural join teaches,course where
teaches.course_id=course.course_id;
NAME
TITLE
-------------------- --------------------------------------------------
srinivasan
intro. to computer science
srinivasan
robotics
srinivasan
database system concepts
wu
investment banking
mozart
music video production
einsten
physical principles
el said
world history
katz
intro. to computer science
katz
image processing
crick
intro. to biology
cs-319
ee-181
crick
genetics
15 rows selected.
RENAME
a)As
4) SQL> select name as instructor_name,course_id from instructor,teaches where
instructor.ID=teaches.ID;
INSTRUCTOR_NAME
COURSE_I
-------------------- --------
srinivasan
cs-101
srinivasan
cs-315
srinivasan
cs-347
wu
fin-201
mozart
mu-199
einsten
phy-101
el said
his-351
katz
cs-101
katz
cs-319
crick
bio-101
crick
bio-301
15 rows selected.
STRING OPERATIONS
a)Like
5) SQL> select dept_name from department where building like '%watson%';
DEPT_NAME
----------
biology
physics
ATTRIBUTE SPECIFICATION IN SELECT CLAUSE
6) SQL> select instructor .* from instructor,teaches where
instructor.ID=teaches.ID;
ID NAME
DEPT_NAME
SALARY
----- -------------------- -------------------- ----------
10101 srinivasan
comp.sci
65000
10101 srinivasan
comp.sci
65000
10101 srinivasan
comp.sci
65000
12121 wu
finance
90000
15151 mozart
music
40000
22222 einsten
physics
95000
32343 el said
history
60000
45565 katz
comp.sci
75000
45565 katz
comp.sci
75000
76766 crick
biology
72000
76766 crick
biology
72000
ID NAME
DEPT_NAME
SALARY
----- -------------------- -------------------- ----------
83821 brandt
comp.sci
92000
83821 brandt
comp.sci
92000
83821 brandt
comp.sci
92000
98345 kim
elec.eng.
80000
15 rows selected.
ORDERING THE DISPLAY OF TUPLES
7) SQL> select name from instructor where dept_name='physics' order by name;
NAME
--------------------
einsten
gold
8) SQL> select * from instructor order by salary desc,name asc;
ID NAME
DEPT_NAME
SALARY
----- -------------------- -------------------- ----------
22222 einsten
physics
95000
83821 brandt
comp.sci
92000
12121 wu
finance
90000
33456 gold
physics
87000
98345 kim
elec.eng.
80000
76543 singh
finance
80000
45565 katz
comp.sci
75000
76766 crick
biology
72000
10101 srinivasan
comp.sci
65000
58583 califieri
history
62000
32343 el said
history
60000
ID NAME
DEPT_NAME
SALARY
----- -------------------- -------------------- ----------
15151 mozart
music
40000
12 rows selected.
SET OPERATIONS
a)Union
9) SQL> (select course_id from section where semester='fall' and year=2009) union
(select course_id from section where semester='spring' and year=2010);
COURSE_I
--------
cs-101
cs-315
cs-319
cs-347
fin-201
his-351
mu-199
phy-101
8 rows selected.
b)Union all
10) SQL> (select course_id from section where semester='fall' and year=2009) union
all(select course_id from section where semester='spring' and year=2010);
COURSE_I
--------
cs-101
cs-101
cs-315
cs-319
cs-347
fin-201
his-351
mu-199
phy-101
c)Intersect
11) SQL> (select course_id from section where semester='fall' and year=2009)
intersect (select course_id from section where semester='spring' and year=2010);
COURSE_I
--------
cs-101
d)Intersect all
12) SQL> (select course_id from section where semester='fall' and year=2009)
intersect all(select course_id from section where semester='spring' and year=2010);
COURSE_I
--------
cs-101
cs-101
e)Except
13) SQL> (select course_id from section where semester='fall' and year=2009)
except(select course_id from section where semester='spring' and year=2010);
COURSE_I
Cs-347
Phy-101
f)Except all
14) SQL> (select course_id from section where semester='fall' and year=2009)
exceptall(select course_id from section where semester='spring' and year=2010);
COURSE_I
--------
Cs-347
Phy-101
NULL VALUES
14) SQL> select name from instructor where salary is null;
no rows selected
AGGREGATE FUNCTIONS
a)Basic aggregation
15) SQL> select avg(salary) as avg_salary from instructor where
dept_name='comp.sci';
AVG_SALARY
----------
77333.3333
16) SQL> select count(distinct ID) from teaches where semester='spring' and
year=2010;
COUNT(DISTINCTID)
-----------------
6
b)Aggregation with grouping
17) SQL> select dept_name,avg(salary) as avg_salary from instructor group by
dept_name;
DEPT_NAME
AVG_SALARY
-------------------- ----------
finance
85000
biology
72000
comp.sci
77333.3333
music
40000
physics
91000
elec.eng.
80000
history
61000
7 rows selected.
18) SQL> select dept_name,count(distinct ID) as instr_count from instructor
natural join teaches where semester='spring' and year=2010 group by dept_name;
DEPT_NAME
INSTR_COUNT
-------------------- -----------
comp.sci
3
finance
1
history
1
music
1
c)Having clause
19) SQL> select dept_name,avg(salary) as avg_salary from instructor group by
dept_name having avg(salary)>42000;
DEPT_NAME
AVG_SALARY
-------------------- ----------
finance
85000
biology
72000
comp.sci
77333.3333
physics
91000
elec.eng.
80000
history
61000
6 rows selected.
20) SQL> select course_id,semester,year,sec_id,avg(tot_cred) from takes natural
join student where year=2009 group by course_id,semester,year,sec_id having
count(ID)>=2;
COURSE_I SEMEST
YEAR SEC_ID AVG(TOT_CRED)
-------- ------ ---------- -------- -------------
cs-101 fall
2009 1
65
cs-347 fall
2009 1
67
cs-190 spring
2009 2
43
SET MEMBERSHIP
a)In
21) SQL> select distinct course_id from section where semester='fall' and year=2009
and course_id in(select course_id from section where semester='spring' and
year=2010);
COURSE_I
--------
cs-101
b)Not in
22) SQL> select distinct course_id from section where semester='fall' and year=2009
and course_id not in(select course_id from section where semester='spring' and
year=2010);
COURSE_I
--------
cs-347
phy-101
c)Distinct
23) SQL> select count(distinct ID) from takes
where(course_id,sec_id,semester,year) in(select course_id,sec_id,semester,year from
teaches where teaches.ID=10101);
COUNT(DISTINCTID)
-----------------
6
SET COMPARISON
a)>some
24) SQL> select name from instructor where salary>some(select salary from
instructor where dept_name='biology');
NAME
--------------------
einsten
brandt
wu
gold
kim
singh
katz
7 rows selected.
b)>all
25) SQL> select name from instructor where salary>all(select salary from instructor
where dept_name='biology');
NAME
--------------------
wu
einsten
gold
katz
singh
brandt
kim
7 rows selected.
c)>=all
26) SQL> select dept_name from instructor group by dept_name having avg(salary)
>=all(select avg(salary) from instructor group by dept_name);
DEPT_NAME
--------------------
Physics
BUILDING
ROOM_NU CAPACITY
--------------- ------- ----------
packard
101
500
painter
514
10
taylor
3128
70
watson
100
30
watson
120
50
SQL> select *from department;
DEPT_NAME BUILDING
---------- --------------- ----------
biology watson
90000
comp.sci taylor
100000
elec.eng. taylor
85000
finance painter
120000
history painter
50000
music
packard
80000
physics watson
70000
7 rows selected.
SQL> select *from course;
COURSE_I TITLE
DEPT_NAME
-------- -------------------------------------------------- ----------------
CREDITS
----------
bio-101 intro. to biology
biology
4
bio-399 computational biology
3
cs-101 intro. to computer science
comp.sci
4
COURSE_I TITLE
DEPT_NAME
-------- -------------------------------------------------- ----------------
CREDITS
----------
cs-190 game design
comp.sci
4
cs-315 robotics
3
cs-319 image processing
comp.sci
3
COURSE_I TITLE
DEPT_NAME
-------- -------------------------------------------------- ----------------
CREDITS
----------
cs-347 database system concepts
comp.sci
3
ee-181 intro. to digital systems
3
fin-201 investment banking
finance
3
COURSE_I TITLE
DEPT_NAME
-------- -------------------------------------------------- ----------------
CREDITS
----------
his-351 world history
history
3
mu-199 music video production
3
phy-101 physical principles
physics
4
COURSE_I TITLE
DEPT_NAME
-------- -------------------------------------------------- ----------------
CREDITS
----------
bio-301 genetics
biology
4
13 rows selected.
SQL> select *from instructor;
ID NAME
DEPT_NAME
SALARY
----- -------------------- -------------------- ----------
10101 srinivasan
comp.sci
65000
12121 wu
finance
90000
15151 mozart
music
40000
22222 einsten
physics
95000
32343 el said
history
60000
33456 gold
physics
87000
45565 katz
comp.sci
75000
58583 califieri
history
62000
76543 singh
finance
80000
76766 crick
biology
72000
83821 brandt
comp.sci
92000
ID NAME
DEPT_NAME
SALARY
----- -------------------- -------------------- ----------
98345 kim
elec.eng.
80000
12 rows selected.
SQL> select *from section;
COURSE_I SEC_ID SEMEST
YEAR BUILDING
-------- -------- ------ ---------- --------------- ------- ----
bio-101 1
summer
2009 painter
514 b
bio-301 1
summer
2010 painter
514 a
cs-101 1
fall
2009 packard
101 h
cs-101 1
spring
2010 packard
101 f
cs-190 1
spring
2009 taylor
3128 e
cs-190 2
spring
2009 taylor
3128 a
cs-315 1
spring
2010 watson
120 d
cs-319 1
spring
2010 watson
100 b
cs-319 2
spring
2010 taylor
3128 c
cs-347 1
fall
2009 taylor
3128 a
ee-181 1
spring
2009 taylor
3128 c
COURSE_I SEC_ID SEMEST
YEAR BUILDING
-------- -------- ------ ---------- --------------- ------- ----
fin-201 1
spring
2010 packard
101 b
his-351 1
spring
2010 painter
514 c
mu-199 1
spring
2010 packard
101 d
phy-101 1
fall
2009 watson
100 a
15 rows selected.
SQL> select *from teaches;
ID COURSE_I SEC_ID SEMEST
----- -------- -------- ------ ----------
10101 cs-101 1
fall
2009
10101 cs-315 1
spring
2010
10101 cs-347 1
fall
2009
12121 fin-201 1
spring
2010
15151 mu-199 1
spring
2010
22222 phy-101 1
fall
2009
32343 his-351 1
spring
2010
45565 cs-101 1
spring
2010
45565 cs-319 1
spring
2010
76766 bio-101 1
summer
2009
76766 bio-301 1
summer
2010
ID COURSE_I SEC_ID SEMEST
----- -------- -------- ------ ----------
83821 cs-190 1
spring
2009
83821 cs-190 2
spring
2009
83821 cs-319 2
spring
2010
98345 ee-181 1
spring
2009
15 rows selected.
SQL> select *from student;
ID NAME
DEPT_NAME
TOT_CRED
----- -------------------- -------------------- ----------
00128 zhang
comp.sci
102
12345 shankar
comp.sci
32
19991 brandt
history
80
23121 chavez
finance
110
44553 peltier
physics
56
45678 levy
physics
46
54321 williams
comp.sci
54
55739 sanchez
music
38
70557 snow
physics
0
76543 brown
comp.sci
58
76653 aoi
elec.eng.
60
ID NAME
DEPT_NAME
TOT_CRED
----- -------------------- -------------------- ----------
98765 bourikas
elec.eng.
98
98988 tanaka
biology
120
13 rows selected.
SQL> select *from takes;
ID COURSE_I SEC_ID SEMEST
----- -------- -------- ------ ---------- --
00128 cs-101 1
fall
2009 a
00128 cs-347 1
fall
2009 a-
12345 cs-101 1
fall
2009 c
12345 cs-190 2
spring
2009 a
12345 cs-315 1
spring
2010 a
12345 cs-347 1
fall
2009 a
19991 his-351 1
spring
2010 b
23121 fin-201 1
spring
2010 c+
44553 phy-101 1
fall
2009 b-
45678 cs-101 1
fall
2009 f
45678 cs-101 1
ID COURSE_I SEC_ID SEMEST
YEAR GR
----- -------- -------- ------ ---------- --
45678 cs-319 1
spring
2010 b
54321 cs-101 1
fall
2009 a-
54321 cs-190 2
spring
2009 b+
55739 mu-199 1
spring
2010 a-
76543 cs-101 1
fall
2009 a
76543 cs-319 2
spring
2010 a
76653 ee-181 1
spring
2009 c
98765 cs-101 1
fall
2009 c-
98765 cs-315 1
spring
2010 b
98988 bio-101 1
summer
2009 a
98988 bio-301 1
summer
2010
22 rows selected.
DML QUERIES
1) SQL> select distinct dept_name from instructor;
DEPT_NAME
--------------------
finance
biology
comp.sci
music
physics
elec.eng.
history
7 rows selected.
2)SQL> select all title from course;
TITLE
--------------------------------------------------
intro. to biology
computational biology
intro. to computer science
game design
robotics
image processing
database system concepts
intro. to digital systems
investment banking
world history
spring
2010 b+
music video production
TITLE
--------------------------------------------------
physical principles
genetics
13 rows selected.
3) SQL> select name from instructor where dept_name='comp.sci' and
salary>70000;
NAME
--------------------
katz
brandt
4) SQL> select name,instructor.dept_name,building from instructor,department
where instructor.dept_name=department.dept_name;
NAME
DEPT_NAME
BUILDING
-------------------- -------------------- ---------------
srinivasan
comp.sci
taylor
wu
finance
painter
mozart
music
packard
einsten
physics
watson
el said
history
painter
gold
physics
watson
katz
comp.sci
taylor
califieri
history
painter
singh
finance
painter
crick
biology
watson
brandt
comp.sci
taylor
NAME
DEPT_NAME
BUILDING
-------------------- -------------------- ---------------
kim
elec.eng.
taylor
12 rows selected.
5) SQL> select name,course_id from instructor,teaches where
instructor.ID=teaches.ID and instructor.dept_name='comp.sci';
NAME
COURSE_I
-------------------- --------
srinivasan
cs-101
srinivasan
cs-315
srinivasan
cs-347
katz
cs-101
katz
cs-319
brandt
cs-190
brandt
brandt
8 rows selected.
6) SQL> select name from student where name like '%an%';
NAME
--------------------
zhang
shankar
brandt
sanchez
tanaka
7) SQL> select name from student where tot_cred between 50 and 80;
NAME
--------------------
brandt
peltier
williams
brown
aoi
JOIN AND SET OPERATIONS
WHERE CLAUSE PREDICATES
1) SQL> select name,course_id from instructor,teaches where
instructor.ID=teaches.ID;
NAME
COURSE_I
-------------------- --------
srinivasan
cs-101
srinivasan
cs-315
srinivasan
cs-347
wu
fin-201
mozart
mu-199
einsten
phy-101
el said
his-351
katz
cs-101
katz
cs-319
crick
bio-101
crick
bio-301
NAME
COURSE_I
-------------------- --------
brandt
cs-190
brandt
cs-190
cs-190
cs-319
brandt
kim
15 rows selected.
NATURAL JOIN
2) SQL> select name, course_id from instructor natural join teaches;
NAME
COURSE_I
-------------------- --------
srinivasan
cs-101
srinivasan
cs-315
srinivasan
cs-347
wu
fin-201
mozart
mu-199
einsten
phy-101
el said
his-351
katz
cs-101
katz
cs-319
crick
bio-101
crick
bio-301
NAME
COURSE_I
-------------------- --------
brandt
cs-190
brandt
cs-190
brandt
cs-319
kim
ee-181
15 rows selected.
3) SQL> select name,title from instructor natural join teaches,course where
teaches.course_id=course.course_id;
NAME
TITLE
-------------------- --------------------------------------------------
srinivasan
intro. to computer science
srinivasan
robotics
srinivasan
database system concepts
wu
investment banking
mozart
music video production
einsten
physical principles
el said
world history
katz
intro. to computer science
katz
image processing
crick
intro. to biology
cs-319
ee-181
crick
genetics
15 rows selected.
RENAME
a)As
4) SQL> select name as instructor_name,course_id from instructor,teaches where
instructor.ID=teaches.ID;
INSTRUCTOR_NAME
COURSE_I
-------------------- --------
srinivasan
cs-101
srinivasan
cs-315
srinivasan
cs-347
wu
fin-201
mozart
mu-199
einsten
phy-101
el said
his-351
katz
cs-101
katz
cs-319
crick
bio-101
crick
bio-301
15 rows selected.
STRING OPERATIONS
a)Like
5) SQL> select dept_name from department where building like '%watson%';
DEPT_NAME
----------
biology
physics
ATTRIBUTE SPECIFICATION IN SELECT CLAUSE
6) SQL> select instructor .* from instructor,teaches where
instructor.ID=teaches.ID;
ID NAME
DEPT_NAME
SALARY
----- -------------------- -------------------- ----------
10101 srinivasan
comp.sci
65000
10101 srinivasan
comp.sci
65000
10101 srinivasan
comp.sci
65000
12121 wu
finance
90000
15151 mozart
music
40000
22222 einsten
physics
95000
32343 el said
history
60000
45565 katz
comp.sci
75000
45565 katz
comp.sci
75000
76766 crick
biology
72000
76766 crick
biology
72000
ID NAME
DEPT_NAME
SALARY
----- -------------------- -------------------- ----------
83821 brandt
comp.sci
92000
83821 brandt
comp.sci
92000
83821 brandt
comp.sci
92000
98345 kim
elec.eng.
80000
15 rows selected.
ORDERING THE DISPLAY OF TUPLES
7) SQL> select name from instructor where dept_name='physics' order by name;
NAME
--------------------
einsten
gold
8) SQL> select * from instructor order by salary desc,name asc;
ID NAME
DEPT_NAME
SALARY
----- -------------------- -------------------- ----------
22222 einsten
physics
95000
83821 brandt
comp.sci
92000
12121 wu
finance
90000
33456 gold
physics
87000
98345 kim
elec.eng.
80000
76543 singh
finance
80000
45565 katz
comp.sci
75000
76766 crick
biology
72000
10101 srinivasan
comp.sci
65000
58583 califieri
history
62000
32343 el said
history
60000
ID NAME
DEPT_NAME
SALARY
----- -------------------- -------------------- ----------
15151 mozart
music
40000
12 rows selected.
SET OPERATIONS
a)Union
9) SQL> (select course_id from section where semester='fall' and year=2009) union
(select course_id from section where semester='spring' and year=2010);
COURSE_I
--------
cs-101
cs-315
cs-319
cs-347
fin-201
his-351
mu-199
phy-101
8 rows selected.
b)Union all
10) SQL> (select course_id from section where semester='fall' and year=2009) union
all(select course_id from section where semester='spring' and year=2010);
COURSE_I
--------
cs-101
cs-101
cs-315
cs-319
cs-347
fin-201
his-351
mu-199
phy-101
c)Intersect
11) SQL> (select course_id from section where semester='fall' and year=2009)
intersect (select course_id from section where semester='spring' and year=2010);
COURSE_I
--------
cs-101
d)Intersect all
12) SQL> (select course_id from section where semester='fall' and year=2009)
intersect all(select course_id from section where semester='spring' and year=2010);
COURSE_I
--------
cs-101
cs-101
e)Except
13) SQL> (select course_id from section where semester='fall' and year=2009)
except(select course_id from section where semester='spring' and year=2010);
COURSE_I
Cs-347
Phy-101
f)Except all
14) SQL> (select course_id from section where semester='fall' and year=2009)
exceptall(select course_id from section where semester='spring' and year=2010);
COURSE_I
--------
Cs-347
Phy-101
NULL VALUES
14) SQL> select name from instructor where salary is null;
no rows selected
AGGREGATE FUNCTIONS
a)Basic aggregation
15) SQL> select avg(salary) as avg_salary from instructor where
dept_name='comp.sci';
AVG_SALARY
----------
77333.3333
16) SQL> select count(distinct ID) from teaches where semester='spring' and
year=2010;
COUNT(DISTINCTID)
-----------------
6
b)Aggregation with grouping
17) SQL> select dept_name,avg(salary) as avg_salary from instructor group by
dept_name;
DEPT_NAME
AVG_SALARY
-------------------- ----------
finance
85000
biology
72000
comp.sci
77333.3333
music
40000
physics
91000
elec.eng.
80000
history
61000
7 rows selected.
18) SQL> select dept_name,count(distinct ID) as instr_count from instructor
natural join teaches where semester='spring' and year=2010 group by dept_name;
DEPT_NAME
INSTR_COUNT
-------------------- -----------
comp.sci
3
finance
1
history
1
music
1
c)Having clause
19) SQL> select dept_name,avg(salary) as avg_salary from instructor group by
dept_name having avg(salary)>42000;
DEPT_NAME
AVG_SALARY
-------------------- ----------
finance
85000
biology
72000
comp.sci
77333.3333
physics
91000
elec.eng.
80000
history
61000
6 rows selected.
20) SQL> select course_id,semester,year,sec_id,avg(tot_cred) from takes natural
join student where year=2009 group by course_id,semester,year,sec_id having
count(ID)>=2;
COURSE_I SEMEST
YEAR SEC_ID AVG(TOT_CRED)
-------- ------ ---------- -------- -------------
cs-101 fall
2009 1
65
cs-347 fall
2009 1
67
cs-190 spring
2009 2
43
SET MEMBERSHIP
a)In
21) SQL> select distinct course_id from section where semester='fall' and year=2009
and course_id in(select course_id from section where semester='spring' and
year=2010);
COURSE_I
--------
cs-101
b)Not in
22) SQL> select distinct course_id from section where semester='fall' and year=2009
and course_id not in(select course_id from section where semester='spring' and
year=2010);
COURSE_I
--------
cs-347
phy-101
c)Distinct
23) SQL> select count(distinct ID) from takes
where(course_id,sec_id,semester,year) in(select course_id,sec_id,semester,year from
teaches where teaches.ID=10101);
COUNT(DISTINCTID)
-----------------
6
SET COMPARISON
a)>some
24) SQL> select name from instructor where salary>some(select salary from
instructor where dept_name='biology');
NAME
--------------------
einsten
brandt
wu
gold
kim
singh
katz
7 rows selected.
b)>all
25) SQL> select name from instructor where salary>all(select salary from instructor
where dept_name='biology');
NAME
--------------------
wu
einsten
gold
katz
singh
brandt
kim
7 rows selected.
c)>=all
26) SQL> select dept_name from instructor group by dept_name having avg(salary)
>=all(select avg(salary) from instructor group by dept_name);
DEPT_NAME
--------------------
Physics
No comments:
Post a Comment