5. Basic Structure of SQL Queries.pdf

Basic Structure of SQL Queries

1. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 1

5. Basic Structure of SQL Queries
SQL is based on set and relational operations with certain modifications and enhancements. A typical SQL
query has the form:
select A1, A2, ..., An
from r1, r2, ..., rm
where P
– Ai represents an attribute,
– Ri represents a relation and
– P is a predicate.

The SELECT Clause
The select clause list the attributes desired in the result of a query. SQL allows duplicates in relations as well
as in query results. To force the elimination of duplicates, insert the keyword distinct after select. The
keyword all specifies that duplicates not be removed. An asterisk in the select clause denotes “all
attributes”. The select clause can contain arithmetic expressions involving the operation, +, –, *, and /, and
operating on constants or attributes of tuples.

The WHERE Clause
The where clause specifies conditions that the result must satisfy. Comparison results can be combined
using the logical connectives and, or, and not. Comparisons can be applied to results of arithmetic
expressions. SQL includes a between comparison operator.

The FROM Clause
The from clause lists the relations involved in the query.

Queries on a Single Relation

Find the names of all instructors.

SQL> select * from instructor;

ID NAME DEPT_NAME SALARY
----- -------------------- -------------------- ----------
10101 Srinivasan Comp.Sci. 65000
12121 Wu Finance 90000
15151 Mozart Music 40000
22222 Einstein Physics 95000
32343 El Said History 60000
33456 Gold Physics 87000
45565 Katz Comp.Sci. 75000
58583 Califieri History 62000
76543 Singh Finance 80000

2. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 2

76766 Crick Biology 72000
83821 Brandt Comp.Sci. 92000

ID NAME DEPT_NAME SALARY
----- -------------------- -------------------- ----------
98345 Kim Elec.Eng. 80000

12 rows selected.

SQL> select name from instructor;

NAME
--------------------
Srinivasan
Wu
Mozart
Einstein
El Said
Gold
Katz
Califieri
Singh
Crick
Brandt

NAME
--------------------
Kim

12 rows selected.

Find the department names of all instructors

SQL> select * from instructor;

ID NAME DEPT_NAME SALARY
----- -------------------- -------------------- ----------
10101 Srinivasan Comp.Sci. 65000
12121 Wu Finance 90000
15151 Mozart Music 40000
22222 Einstein Physics 95000
32343 El Said History 60000
33456 Gold Physics 87000
45565 Katz Comp.Sci. 75000

3. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 3

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.

Find the department names of all instructors (including duplicates)

SQL> select dept_name from instructor;

DEPT_NAME
--------------------
Comp.Sci.
Finance
Music
Physics
History
Physics
Comp.Sci.
History
Finance
Biology
Comp.Sci.

SQL> select all dept_name from instructor;

DEPT_NAME
--------------------
Comp.Sci.
Finance
Music
Physics
History
Physics
Comp.Sci.
History
Finance
Biology
Comp.Sci.

4. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 4


DEPT_NAME
--------------------
Elec.Eng.

12 rows selected.

Find the distinct department names of all instructors (including duplicates)

SQL> select distinct dept_name from instructor;

DEPT_NAME
--------------------
Physics
Finance
Elec.Eng.
Comp.Sci.
Biology
Music
History

7 rows selected.

Give 10% rise in salary of every instructor

SQL> select * from instructor;

ID NAME DEPT_NAME SALARY
----- -------------------- -------------------- ----------
10101 Srinivasan Comp.Sci. 65000
12121 Wu Finance 90000
15151 Mozart Music 40000
22222 Einstein 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

5. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 5

12 rows selected.

SQL> select ID, name, dept_name, salary * 1.1 from instructor;

ID NAME DEPT_NAME SALARY*1.1
----- -------------------- -------------------- ----------
10101 Srinivasan Comp.Sci. 71500
12121 Wu Finance 99000
15151 Mozart Music 44000
22222 Einstein Physics 104500
32343 El Said History 66000
33456 Gold Physics 95700
45565 Katz Comp.Sci. 82500
58583 Califieri History 68200
76543 Singh Finance 88000
76766 Crick Biology 79200
83821 Brandt Comp.Sci. 101200

ID NAME DEPT_NAME SALARY*1.1
----- -------------------- -------------------- ----------
98345 Kim Elec.Eng. 88000

12 rows selected.

Find the names of all instructors in the Computer Science department who have salary greater than
$70,000

SQL> select * from instructor;

ID NAME DEPT_NAME SALARY
----- -------------------- -------------------- ----------
10101 Srinivasan Comp.Sci. 65000
12121 Wu Finance 90000
15151 Mozart Music 40000
22222 Einstein 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

6. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 6

ID NAME DEPT_NAME SALARY
----- -------------------- -------------------- ----------
98345 Kim Elec.Eng. 80000

12 rows selected.

SQL> select name
2 from instructor where dept_name = 'Comp.Sci.' and salary > 70000;

NAME
--------------------
Katz
Brandt

7. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 7

Queries on Multiple Relations
Queries often need to access information from multiple relations

Retrieve the names of all instructors, along with their department names and department building
name.

SQL> select * from instructor;

ID NAME DEPT_NAME SALARY
----- -------------------- -------------------- ----------
10101 Srinivasan Comp.Sci. 65000
12121 Wu Finance 90000
15151 Mozart Music 40000
22222 Einstein 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 department;

DEPT_NAME BUILDING BUDGET
-------------------- -------------------- ----------
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 name, instructor.dept_name, building
2 from instructor, department

8. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 8

3 where instructor.dept_name= department.dept_name;

NAME DEPT_NAME BUILDING
-------------------- -------------------- --------------------
Srinivasan Comp.Sci. Taylor
Wu Finance Painter
Mozart Music Packard
Einstein 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.

For all instructors in the university who have taught some course, find their names and the course ID
of all courses they taught.

SQL> select * from instructor;

ID NAME DEPT_NAME SALARY
----- -------------------- -------------------- ----------
10101 Srinivasan Comp.Sci. 65000
12121 Wu Finance 90000
15151 Mozart Music 40000
22222 Einstein 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

9. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 9


12 rows selected.

SQL> select * from teaches;

ID COURSE_I SEC_ID SEMEST YEAR
----- -------- -------- ------ ----------
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 YEAR
----- -------- -------- ------ ----------
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.

Cartesian product of the relation instructor and teaches

SQL> select * from instructor, teaches;

ID NAME DEPT_NAME SALARY ID COURSE_I
----- -------------------- -------------------- ---------- ----- --------
SEC_ID SEMEST YEAR
-------- ------ ----------
10101 Srinivasan Comp.Sci. 65000 10101 CS-101
1 Fall 2009

10101 Srinivasan Comp.Sci. 65000 10101 CS-315
1 Spring 2010

10101 Srinivasan Comp.Sci. 65000 10101 CS-347
1 Fall 2009

10. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 10



ID NAME DEPT_NAME SALARY ID COURSE_I
----- -------------------- -------------------- ---------- ----- --------
SEC_ID SEMEST YEAR
-------- ------ ----------
10101 Srinivasan Comp.Sci. 65000 12121 FIN-201
1 Spring 2010

10101 Srinivasan Comp.Sci. 65000 15151 MU-199
1 Spring 2010

10101 Srinivasan Comp.Sci. 65000 22222 PHY-101
1 Fall 2009


ID NAME DEPT_NAME SALARY ID COURSE_I
----- -------------------- -------------------- ---------- ----- --------
SEC_ID SEMEST YEAR
-------- ------ ----------
10101 Srinivasan Comp.Sci. 65000 32343 HIS-351
1 Spring 2010

10101 Srinivasan Comp.Sci. 65000 45565 CS-101
1 Spring 2010

10101 Srinivasan Comp.Sci. 65000 45565 CS-319
1 Spring 2010


ID NAME DEPT_NAME SALARY ID COURSE_I
----- -------------------- -------------------- ---------- ----- --------
SEC_ID SEMEST YEAR
-------- ------ ----------
10101 Srinivasan Comp.Sci. 65000 76766 BIO-101
1 Summer 2009

10101 Srinivasan Comp.Sci. 65000 76766 BIO-301
1 Summer 2010

10101 Srinivasan Comp.Sci. 65000 83821 CS-190
1 Spring 2009

11. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 11

ID NAME DEPT_NAME SALARY ID COURSE_I
----- -------------------- -------------------- ---------- ----- --------
SEC_ID SEMEST YEAR
-------- ------ ----------
10101 Srinivasan Comp.Sci. 65000 83821 CS-190
2 Spring 2009

10101 Srinivasan Comp.Sci. 65000 83821 CS-319
2 Spring 2010

10101 Srinivasan Comp.Sci. 65000 98345 EE-181
1 Spring 2009


ID NAME DEPT_NAME SALARY ID COURSE_I
----- -------------------- -------------------- ---------- ----- --------
SEC_ID SEMEST YEAR
-------- ------ ----------
12121 Wu Finance 90000 10101 CS-101
1 Fall 2009

12121 Wu Finance 90000 10101 CS-315
1 Spring 2010

12121 Wu Finance 90000 10101 CS-347
1 Fall 2009


ID NAME DEPT_NAME SALARY ID COURSE_I
----- -------------------- -------------------- ---------- ----- --------
SEC_ID SEMEST YEAR
-------- ------ ----------
12121 Wu Finance 90000 12121 FIN-201
1 Spring 2010

12121 Wu Finance 90000 15151 MU-199
1 Spring 2010

12121 Wu Finance 90000 22222 PHY-101
1 Fall 2009


ID NAME DEPT_NAME SALARY ID COURSE_I
----- -------------------- -------------------- ---------- ----- --------

12. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 12

SEC_ID SEMEST YEAR
-------- ------ ----------
12121 Wu Finance 90000 32343 HIS-351
1 Spring 2010

12121 Wu Finance 90000 45565 CS-101
1 Spring 2010

12121 Wu Finance 90000 45565 CS-319
1 Spring 2010


ID NAME DEPT_NAME SALARY ID COURSE_I
----- -------------------- -------------------- ---------- ----- --------
SEC_ID SEMEST YEAR
-------- ------ ----------
12121 Wu Finance 90000 76766 BIO-101
1 Summer 2009

12121 Wu Finance 90000 76766 BIO-301
1 Summer 2010

12121 Wu Finance 90000 83821 CS-190
1 Spring 2009


ID NAME DEPT_NAME SALARY ID COURSE_I
----- -------------------- -------------------- ---------- ----- --------
SEC_ID SEMEST YEAR
-------- ------ ----------
12121 Wu Finance 90000 83821 CS-190
2 Spring 2009

12121 Wu Finance 90000 83821 CS-319
2 Spring 2010

12121 Wu Finance 90000 98345 EE-181
1 Spring 2009


ID NAME DEPT_NAME SALARY ID COURSE_I
----- -------------------- -------------------- ---------- ----- --------
SEC_ID SEMEST YEAR
-------- ------ ----------

13. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 13

15151 Mozart Music 40000 10101 CS-101
1 Fall 2009

15151 Mozart Music 40000 10101 CS-315
1 Spring 2010

15151 Mozart Music 40000 10101 CS-347
1 Fall 2009


ID NAME DEPT_NAME SALARY ID COURSE_I
----- -------------------- -------------------- ---------- ----- --------
SEC_ID SEMEST YEAR
-------- ------ ----------
15151 Mozart Music 40000 12121 FIN-201
1 Spring 2010

15151 Mozart Music 40000 15151 MU-199
1 Spring 2010

15151 Mozart Music 40000 22222 PHY-101
1 Fall 2009


ID NAME DEPT_NAME SALARY ID COURSE_I
----- -------------------- -------------------- ---------- ----- --------
SEC_ID SEMEST YEAR
-------- ------ ----------
15151 Mozart Music 40000 32343 HIS-351
1 Spring 2010

15151 Mozart Music 40000 45565 CS-101
1 Spring 2010

15151 Mozart Music 40000 45565 CS-319
1 Spring 2010


ID NAME DEPT_NAME SALARY ID COURSE_I
----- -------------------- -------------------- ---------- ----- --------
SEC_ID SEMEST YEAR
-------- ------ ----------
15151 Mozart Music 40000 76766 BIO-101
1 Summer 2009

14. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 14


15151 Mozart Music 40000 76766 BIO-301
1 Summer 2010

15151 Mozart Music 40000 83821 CS-190
1 Spring 2009


ID NAME DEPT_NAME SALARY ID COURSE_I
----- -------------------- -------------------- ---------- ----- --------
SEC_ID SEMEST YEAR
-------- ------ ----------
15151 Mozart Music 40000 83821 CS-190
2 Spring 2009

15151 Mozart Music 40000 83821 CS-319
2 Spring 2010

15151 Mozart Music 40000 98345 EE-181
1 Spring 2009


ID NAME DEPT_NAME SALARY ID COURSE_I
----- -------------------- -------------------- ---------- ----- --------
SEC_ID SEMEST YEAR
-------- ------ ----------
22222 Einstein Physics 95000 10101 CS-101
1 Fall 2009

22222 Einstein Physics 95000 10101 CS-315
1 Spring 2010

22222 Einstein Physics 95000 10101 CS-347
1 Fall 2009


ID NAME DEPT_NAME SALARY ID COURSE_I
----- -------------------- -------------------- ---------- ----- --------
SEC_ID SEMEST YEAR
-------- ------ ----------
22222 Einstein Physics 95000 12121 FIN-201
1 Spring 2010

22222 Einstein Physics 95000 15151 MU-199

15. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 15

1 Spring 2010

22222 Einstein Physics 95000 22222 PHY-101
1 Fall 2009


ID NAME DEPT_NAME SALARY ID COURSE_I
----- -------------------- -------------------- ---------- ----- --------
SEC_ID SEMEST YEAR
-------- ------ ----------
22222 Einstein Physics 95000 32343 HIS-351
1 Spring 2010

22222 Einstein Physics 95000 45565 CS-101
1 Spring 2010

22222 Einstein Physics 95000 45565 CS-319
1 Spring 2010


ID NAME DEPT_NAME SALARY ID COURSE_I
----- -------------------- -------------------- ---------- ----- --------
SEC_ID SEMEST YEAR
-------- ------ ----------
22222 Einstein Physics 95000 76766 BIO-101
1 Summer 2009

22222 Einstein Physics 95000 76766 BIO-301
1 Summer 2010

22222 Einstein Physics 95000 83821 CS-190
1 Spring 2009


ID NAME DEPT_NAME SALARY ID COURSE_I
----- -------------------- -------------------- ---------- ----- --------
SEC_ID SEMEST YEAR
-------- ------ ----------
22222 Einstein Physics 95000 83821 CS-190
2 Spring 2009

22222 Einstein Physics 95000 83821 CS-319
2 Spring 2010

16. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 16

22222 Einstein Physics 95000 98345 EE-181
1 Spring 2009


ID NAME DEPT_NAME SALARY ID COURSE_I
----- -------------------- -------------------- ---------- ----- --------
SEC_ID SEMEST YEAR
-------- ------ ----------
32343 El Said History 60000 10101 CS-101
1 Fall 2009

32343 El Said History 60000 10101 CS-315
1 Spring 2010

32343 El Said History 60000 10101 CS-347
1 Fall 2009


ID NAME DEPT_NAME SALARY ID COURSE_I
----- -------------------- -------------------- ---------- ----- --------
SEC_ID SEMEST YEAR
-------- ------ ----------
32343 El Said History 60000 12121 FIN-201
1 Spring 2010

32343 El Said History 60000 15151 MU-199
1 Spring 2010

32343 El Said History 60000 22222 PHY-101
1 Fall 2009


ID NAME DEPT_NAME SALARY ID COURSE_I
----- -------------------- -------------------- ---------- ----- --------
SEC_ID SEMEST YEAR
-------- ------ ----------
32343 El Said History 60000 32343 HIS-351
1 Spring 2010

32343 El Said History 60000 45565 CS-101
1 Spring 2010

32343 El Said History 60000 45565 CS-319
1 Spring 2010

17. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 17



ID NAME DEPT_NAME SALARY ID COURSE_I
----- -------------------- -------------------- ---------- ----- --------
SEC_ID SEMEST YEAR
-------- ------ ----------
32343 El Said History 60000 76766 BIO-101
1 Summer 2009

32343 El Said History 60000 76766 BIO-301
1 Summer 2010

32343 El Said History 60000 83821 CS-190
1 Spring 2009


ID NAME DEPT_NAME SALARY ID COURSE_I
----- -------------------- -------------------- ---------- ----- --------
SEC_ID SEMEST YEAR
-------- ------ ----------
32343 El Said History 60000 83821 CS-190
2 Spring 2009

32343 El Said History 60000 83821 CS-319
2 Spring 2010

32343 El Said History 60000 98345 EE-181
1 Spring 2009


ID NAME DEPT_NAME SALARY ID COURSE_I
----- -------------------- -------------------- ---------- ----- --------
SEC_ID SEMEST YEAR
-------- ------ ----------
33456 Gold Physics 87000 10101 CS-101
1 Fall 2009

33456 Gold Physics 87000 10101 CS-315
1 Spring 2010

33456 Gold Physics 87000 10101 CS-347
1 Fall 2009

18. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 18

ID NAME DEPT_NAME SALARY ID COURSE_I
----- -------------------- -------------------- ---------- ----- --------
SEC_ID SEMEST YEAR
-------- ------ ----------
33456 Gold Physics 87000 12121 FIN-201
1 Spring 2010

33456 Gold Physics 87000 15151 MU-199
1 Spring 2010

33456 Gold Physics 87000 22222 PHY-101
1 Fall 2009


ID NAME DEPT_NAME SALARY ID COURSE_I
----- -------------------- -------------------- ---------- ----- --------
SEC_ID SEMEST YEAR
-------- ------ ----------
33456 Gold Physics 87000 32343 HIS-351
1 Spring 2010

33456 Gold Physics 87000 45565 CS-101
1 Spring 2010

33456 Gold Physics 87000 45565 CS-319
1 Spring 2010


ID NAME DEPT_NAME SALARY ID COURSE_I
----- -------------------- -------------------- ---------- ----- --------
SEC_ID SEMEST YEAR
-------- ------ ----------
33456 Gold Physics 87000 76766 BIO-101
1 Summer 2009

33456 Gold Physics 87000 76766 BIO-301
1 Summer 2010

33456 Gold Physics 87000 83821 CS-190
1 Spring 2009


ID NAME DEPT_NAME SALARY ID COURSE_I
----- -------------------- -------------------- ---------- ----- --------

19. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 19

SEC_ID SEMEST YEAR
-------- ------ ----------
33456 Gold Physics 87000 83821 CS-190
2 Spring 2009

33456 Gold Physics 87000 83821 CS-319
2 Spring 2010

33456 Gold Physics 87000 98345 EE-181
1 Spring 2009


ID NAME DEPT_NAME SALARY ID COURSE_I
----- -------------------- -------------------- ---------- ----- --------
SEC_ID SEMEST YEAR
-------- ------ ----------
45565 Katz Comp.Sci. 75000 10101 CS-101
1 Fall 2009

45565 Katz Comp.Sci. 75000 10101 CS-315
1 Spring 2010

45565 Katz Comp.Sci. 75000 10101 CS-347
1 Fall 2009


ID NAME DEPT_NAME SALARY ID COURSE_I
----- -------------------- -------------------- ---------- ----- --------
SEC_ID SEMEST YEAR
-------- ------ ----------
45565 Katz Comp.Sci. 75000 12121 FIN-201
1 Spring 2010

45565 Katz Comp.Sci. 75000 15151 MU-199
1 Spring 2010

45565 Katz Comp.Sci. 75000 22222 PHY-101
1 Fall 2009


ID NAME DEPT_NAME SALARY ID COURSE_I
----- -------------------- -------------------- ---------- ----- --------
SEC_ID SEMEST YEAR
-------- ------ ----------

20. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 20

45565 Katz Comp.Sci. 75000 32343 HIS-351
1 Spring 2010

45565 Katz Comp.Sci. 75000 45565 CS-101
1 Spring 2010

45565 Katz Comp.Sci. 75000 45565 CS-319
1 Spring 2010


ID NAME DEPT_NAME SALARY ID COURSE_I
----- -------------------- -------------------- ---------- ----- --------
SEC_ID SEMEST YEAR
-------- ------ ----------
45565 Katz Comp.Sci. 75000 76766 BIO-101
1 Summer 2009

45565 Katz Comp.Sci. 75000 76766 BIO-301
1 Summer 2010

45565 Katz Comp.Sci. 75000 83821 CS-190
1 Spring 2009


ID NAME DEPT_NAME SALARY ID COURSE_I
----- -------------------- -------------------- ---------- ----- --------
SEC_ID SEMEST YEAR
-------- ------ ----------
45565 Katz Comp.Sci. 75000 83821 CS-190
2 Spring 2009

45565 Katz Comp.Sci. 75000 83821 CS-319
2 Spring 2010

45565 Katz Comp.Sci. 75000 98345 EE-181
1 Spring 2009


ID NAME DEPT_NAME SALARY ID COURSE_I
----- -------------------- -------------------- ---------- ----- --------
SEC_ID SEMEST YEAR
-------- ------ ----------
58583 Califieri History 62000 10101 CS-101
1 Fall 2009

21. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 21


58583 Califieri History 62000 10101 CS-315
1 Spring 2010

58583 Califieri History 62000 10101 CS-347
1 Fall 2009


ID NAME DEPT_NAME SALARY ID COURSE_I
----- -------------------- -------------------- ---------- ----- --------
SEC_ID SEMEST YEAR
-------- ------ ----------
58583 Califieri History 62000 12121 FIN-201
1 Spring 2010

58583 Califieri History 62000 15151 MU-199
1 Spring 2010

58583 Califieri History 62000 22222 PHY-101
1 Fall 2009


ID NAME DEPT_NAME SALARY ID COURSE_I
----- -------------------- -------------------- ---------- ----- --------
SEC_ID SEMEST YEAR
-------- ------ ----------
58583 Califieri History 62000 32343 HIS-351
1 Spring 2010

58583 Califieri History 62000 45565 CS-101
1 Spring 2010

58583 Califieri History 62000 45565 CS-319
1 Spring 2010


ID NAME DEPT_NAME SALARY ID COURSE_I
----- -------------------- -------------------- ---------- ----- --------
SEC_ID SEMEST YEAR
-------- ------ ----------
58583 Califieri History 62000 76766 BIO-101
1 Summer 2009

58583 Califieri History 62000 76766 BIO-301

22. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 22

1 Summer 2010

58583 Califieri History 62000 83821 CS-190
1 Spring 2009


ID NAME DEPT_NAME SALARY ID COURSE_I
----- -------------------- -------------------- ---------- ----- --------
SEC_ID SEMEST YEAR
-------- ------ ----------
58583 Califieri History 62000 83821 CS-190
2 Spring 2009

58583 Califieri History 62000 83821 CS-319
2 Spring 2010

58583 Califieri History 62000 98345 EE-181
1 Spring 2009


ID NAME DEPT_NAME SALARY ID COURSE_I
----- -------------------- -------------------- ---------- ----- --------
SEC_ID SEMEST YEAR
-------- ------ ----------
76543 Singh Finance 80000 10101 CS-101
1 Fall 2009

76543 Singh Finance 80000 10101 CS-315
1 Spring 2010

76543 Singh Finance 80000 10101 CS-347
1 Fall 2009


ID NAME DEPT_NAME SALARY ID COURSE_I
----- -------------------- -------------------- ---------- ----- --------
SEC_ID SEMEST YEAR
-------- ------ ----------
76543 Singh Finance 80000 12121 FIN-201
1 Spring 2010

76543 Singh Finance 80000 15151 MU-199
1 Spring 2010

23. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 23

76543 Singh Finance 80000 22222 PHY-101
1 Fall 2009


ID NAME DEPT_NAME SALARY ID COURSE_I
----- -------------------- -------------------- ---------- ----- --------
SEC_ID SEMEST YEAR
-------- ------ ----------
76543 Singh Finance 80000 32343 HIS-351
1 Spring 2010

76543 Singh Finance 80000 45565 CS-101
1 Spring 2010

76543 Singh Finance 80000 45565 CS-319
1 Spring 2010


ID NAME DEPT_NAME SALARY ID COURSE_I
----- -------------------- -------------------- ---------- ----- --------
SEC_ID SEMEST YEAR
-------- ------ ----------
76543 Singh Finance 80000 76766 BIO-101
1 Summer 2009

76543 Singh Finance 80000 76766 BIO-301
1 Summer 2010

76543 Singh Finance 80000 83821 CS-190
1 Spring 2009


ID NAME DEPT_NAME SALARY ID COURSE_I
----- -------------------- -------------------- ---------- ----- --------
SEC_ID SEMEST YEAR
-------- ------ ----------
76543 Singh Finance 80000 83821 CS-190
2 Spring 2009

76543 Singh Finance 80000 83821 CS-319
2 Spring 2010

76543 Singh Finance 80000 98345 EE-181
1 Spring 2009

24. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 24



ID NAME DEPT_NAME SALARY ID COURSE_I
----- -------------------- -------------------- ---------- ----- --------
SEC_ID SEMEST YEAR
-------- ------ ----------
76766 Crick Biology 72000 10101 CS-101
1 Fall 2009

76766 Crick Biology 72000 10101 CS-315
1 Spring 2010

76766 Crick Biology 72000 10101 CS-347
1 Fall 2009


ID NAME DEPT_NAME SALARY ID COURSE_I
----- -------------------- -------------------- ---------- ----- --------
SEC_ID SEMEST YEAR
-------- ------ ----------
76766 Crick Biology 72000 12121 FIN-201
1 Spring 2010

76766 Crick Biology 72000 15151 MU-199
1 Spring 2010

76766 Crick Biology 72000 22222 PHY-101
1 Fall 2009


ID NAME DEPT_NAME SALARY ID COURSE_I
----- -------------------- -------------------- ---------- ----- --------
SEC_ID SEMEST YEAR
-------- ------ ----------
76766 Crick Biology 72000 32343 HIS-351
1 Spring 2010

76766 Crick Biology 72000 45565 CS-101
1 Spring 2010

76766 Crick Biology 72000 45565 CS-319
1 Spring 2010

25. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 25

ID NAME DEPT_NAME SALARY ID COURSE_I
----- -------------------- -------------------- ---------- ----- --------
SEC_ID SEMEST YEAR
-------- ------ ----------
76766 Crick Biology 72000 76766 BIO-101
1 Summer 2009

76766 Crick Biology 72000 76766 BIO-301
1 Summer 2010

76766 Crick Biology 72000 83821 CS-190
1 Spring 2009


ID NAME DEPT_NAME SALARY ID COURSE_I
----- -------------------- -------------------- ---------- ----- --------
SEC_ID SEMEST YEAR
-------- ------ ----------
76766 Crick Biology 72000 83821 CS-190
2 Spring 2009

76766 Crick Biology 72000 83821 CS-319
2 Spring 2010

76766 Crick Biology 72000 98345 EE-181
1 Spring 2009


ID NAME DEPT_NAME SALARY ID COURSE_I
----- -------------------- -------------------- ---------- ----- --------
SEC_ID SEMEST YEAR
-------- ------ ----------
83821 Brandt Comp.Sci. 92000 10101 CS-101
1 Fall 2009

83821 Brandt Comp.Sci. 92000 10101 CS-315
1 Spring 2010

83821 Brandt Comp.Sci. 92000 10101 CS-347
1 Fall 2009


ID NAME DEPT_NAME SALARY ID COURSE_I
----- -------------------- -------------------- ---------- ----- --------

26. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 26

SEC_ID SEMEST YEAR
-------- ------ ----------
83821 Brandt Comp.Sci. 92000 12121 FIN-201
1 Spring 2010

83821 Brandt Comp.Sci. 92000 15151 MU-199
1 Spring 2010

83821 Brandt Comp.Sci. 92000 22222 PHY-101
1 Fall 2009


ID NAME DEPT_NAME SALARY ID COURSE_I
----- -------------------- -------------------- ---------- ----- --------
SEC_ID SEMEST YEAR
-------- ------ ----------
83821 Brandt Comp.Sci. 92000 32343 HIS-351
1 Spring 2010

83821 Brandt Comp.Sci. 92000 45565 CS-101
1 Spring 2010

83821 Brandt Comp.Sci. 92000 45565 CS-319
1 Spring 2010


ID NAME DEPT_NAME SALARY ID COURSE_I
----- -------------------- -------------------- ---------- ----- --------
SEC_ID SEMEST YEAR
-------- ------ ----------
83821 Brandt Comp.Sci. 92000 76766 BIO-101
1 Summer 2009

83821 Brandt Comp.Sci. 92000 76766 BIO-301
1 Summer 2010

83821 Brandt Comp.Sci. 92000 83821 CS-190
1 Spring 2009


ID NAME DEPT_NAME SALARY ID COURSE_I
----- -------------------- -------------------- ---------- ----- --------
SEC_ID SEMEST YEAR
-------- ------ ----------

27. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 27

83821 Brandt Comp.Sci. 92000 83821 CS-190
2 Spring 2009

83821 Brandt Comp.Sci. 92000 83821 CS-319
2 Spring 2010

83821 Brandt Comp.Sci. 92000 98345 EE-181
1 Spring 2009


ID NAME DEPT_NAME SALARY ID COURSE_I
----- -------------------- -------------------- ---------- ----- --------
SEC_ID SEMEST YEAR
-------- ------ ----------
98345 Kim Elec.Eng. 80000 10101 CS-101
1 Fall 2009

98345 Kim Elec.Eng. 80000 10101 CS-315
1 Spring 2010

98345 Kim Elec.Eng. 80000 10101 CS-347
1 Fall 2009


ID NAME DEPT_NAME SALARY ID COURSE_I
----- -------------------- -------------------- ---------- ----- --------
SEC_ID SEMEST YEAR
-------- ------ ----------
98345 Kim Elec.Eng. 80000 12121 FIN-201
1 Spring 2010

98345 Kim Elec.Eng. 80000 15151 MU-199
1 Spring 2010

98345 Kim Elec.Eng. 80000 22222 PHY-101
1 Fall 2009


ID NAME DEPT_NAME SALARY ID COURSE_I
----- -------------------- -------------------- ---------- ----- --------
SEC_ID SEMEST YEAR
-------- ------ ----------
98345 Kim Elec.Eng. 80000 32343 HIS-351
1 Spring 2010

28. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 28


98345 Kim Elec.Eng. 80000 45565 CS-101
1 Spring 2010

98345 Kim Elec.Eng. 80000 45565 CS-319
1 Spring 2010


ID NAME DEPT_NAME SALARY ID COURSE_I
----- -------------------- -------------------- ---------- ----- --------
SEC_ID SEMEST YEAR
-------- ------ ----------
98345 Kim Elec.Eng. 80000 76766 BIO-101
1 Summer 2009

98345 Kim Elec.Eng. 80000 76766 BIO-301
1 Summer 2010

98345 Kim Elec.Eng. 80000 83821 CS-190
1 Spring 2009


ID NAME DEPT_NAME SALARY ID COURSE_I
----- -------------------- -------------------- ---------- ----- --------
SEC_ID SEMEST YEAR
-------- ------ ----------
98345 Kim Elec.Eng. 80000 83821 CS-190
2 Spring 2009

98345 Kim Elec.Eng. 80000 83821 CS-319
2 Spring 2010

98345 Kim Elec.Eng. 80000 98345 EE-181
1 Spring 2009


180 rows selected.

SQL> select name, course_id
2 from instructor, teaches
3 where instructor.ID= teaches.ID;

NAME COURSE_I
-------------------- --------

29. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 29

Srinivasan CS-101
Srinivasan CS-315
Srinivasan CS-347
Wu FIN-201
Mozart MU-199
Einstein 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.

Find instructor names and course identifiers for instructors in the Computer Science department

SQL> select * from instructor;

ID NAME DEPT_NAME SALARY
----- -------------------- -------------------- ----------
10101 Srinivasan Comp.Sci. 65000
12121 Wu Finance 90000
15151 Mozart Music 40000
22222 Einstein 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.

30. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 30


SQL> select * from teaches;

ID COURSE_I SEC_ID SEMEST YEAR
----- -------- -------- ------ ----------
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 YEAR
----- -------- -------- ------ ----------
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 name, course_id
2 from instructor, teaches
3 where instructor.ID= teaches.ID;

NAME COURSE_I
-------------------- --------
Srinivasan CS-101
Srinivasan CS-315
Srinivasan CS-347
Wu FIN-201
Mozart MU-199
Einstein PHY-101
El Said HIS-351
Katz CS-101
Katz CS-319
Crick BIO-101
Crick BIO-301

31. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 31

NAME COURSE_I
-------------------- --------
Brandt CS-190
Brandt CS-190
Brandt CS-319
Kim EE-181

15 rows selected.

SQL> select name, course_id
2 from instructor, teaches
3 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 CS-190
Brandt CS-319

8 rows selected.

32. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 32

The Natural Join
The natural join operation operates on two relations and produces a relation as the result. Unlike the
Cartesian product of two relations, which concatenates each tuple of the first relation with every tuple of the
second, natural join considers only those pairs of tuples with the same value on those attributes that appear
in the schemas of both relations.

For all instructors in the university who have taught some course, find their names and the course ID
of all courses they taught

Using Cartesian product
SQL> select name, course_id
2 from instructor, teaches
3 where instructor.ID= teaches.ID;

NAME COURSE_I
-------------------- --------
Srinivasan CS-101
Srinivasan CS-315
Srinivasan CS-347
Wu FIN-201
Mozart MU-199
Einstein 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.

Using Natural Join

SQL> select name, course_id
2 from instructor natural join teaches;

NAME COURSE_I
-------------------- --------

33. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 33

Srinivasan CS-101
Srinivasan CS-315
Srinivasan CS-347
Wu FIN-201
Mozart MU-199
Einstein 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.

List the names of instructors along with the titles of courses that they teach.

SQL> select * from instructor;

ID NAME DEPT_NAME SALARY
----- -------------------- -------------------- ----------
10101 Srinivasan Comp.Sci. 65000
12121 Wu Finance 90000
15151 Mozart Music 40000
22222 Einstein 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.

34. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 34


SQL> select * from teaches;

ID COURSE_I SEC_ID SEMEST YEAR
----- -------- -------- ------ ----------
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 YEAR
----- -------- -------- ------ ----------
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 course;

COURSE_ TITLE DEPT_NAME
------- -------------------------------------------------- --------------------
CREDITS
----------
BIO-101 Intro. to Biology Biology
4

BIO-301 Genetics Biology
4

BIO-399 Computational Biology Biology
3


COURSE_ TITLE DEPT_NAME
------- -------------------------------------------------- --------------------

35. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 35

CREDITS
----------
CS-101 Intro. to Computer Science Comp.Sci.
4

CS-190 Game Design Comp.Sci.
4

CS-315 Robotics Comp.Sci.
3


COURSE_ TITLE DEPT_NAME
------- -------------------------------------------------- --------------------
CREDITS
----------
CS-319 Image Processing Comp.Sci.
3

CS-347 Database System Concepts Comp.Sci.
3

EE-181 Intro. to Digital Systems Elec.Eng.
3


COURSE_ TITLE DEPT_NAME
------- -------------------------------------------------- --------------------
CREDITS
----------
FIN-201 Investment Banking Finance
3

HIS-351 World History History
3

MU-199 Music Video Production Music
3


COURSE_ TITLE DEPT_NAME
------- -------------------------------------------------- --------------------
CREDITS
----------

36. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 36

PHY-101 Physical Principles Physics
4


13 rows selected.

SQL> select name, title
2 from instructor natural join teaches, course
3 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
Einstein Physical Principles
El Said World History
Katz Intro. to Computer Science
Katz Image Processing
Crick Intro. to Biology
Crick Genetics

NAME TITLE
-------------------- --------------------------------------------------
Brandt Game Design
Brandt Game Design
Brandt Image Processing
Kim Intro. to Digital Systems

15 rows selected.

Above query using only Natural Join

SQL> select name, title
2 from instructor natural join teaches natural join course;

NAME TITLE
-------------------- --------------------------------------------------
Srinivasan Intro. to Biology
Srinivasan Genetics
Srinivasan Computational Biology
Srinivasan Intro. to Computer Science

37. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 37

Srinivasan Game Design
Srinivasan Robotics
Srinivasan Image Processing
Srinivasan Database System Concepts
Srinivasan Intro. to Digital Systems
Srinivasan Investment Banking
Srinivasan World History

NAME TITLE
-------------------- --------------------------------------------------
Srinivasan Music Video Production
Srinivasan Physical Principles
Srinivasan Intro. to Biology
Srinivasan Genetics
Srinivasan Computational Biology
Srinivasan Intro. to Computer Science
Srinivasan Game Design
Srinivasan Robotics
Srinivasan Image Processing
Srinivasan Database System Concepts
Srinivasan Intro. to Digital Systems

NAME TITLE
-------------------- --------------------------------------------------
Srinivasan Investment Banking
Srinivasan World History
Srinivasan Music Video Production
Srinivasan Physical Principles
Srinivasan Intro. to Biology
Srinivasan Genetics
Srinivasan Computational Biology
Srinivasan Intro. to Computer Science
Srinivasan Game Design
Srinivasan Robotics
Srinivasan Image Processing

NAME TITLE
-------------------- --------------------------------------------------
Srinivasan Database System Concepts
Srinivasan Intro. to Digital Systems
Srinivasan Investment Banking
Srinivasan World History
Srinivasan Music Video Production
Srinivasan Physical Principles

38. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 38

Wu Intro. to Biology
Wu Genetics
Wu Computational Biology
Wu Intro. to Computer Science
Wu Game Design

NAME TITLE
-------------------- --------------------------------------------------
Wu Robotics
Wu Image Processing
Wu Database System Concepts
Wu Intro. to Digital Systems
Wu Investment Banking
Wu World History
Wu Music Video Production
Wu Physical Principles
Mozart Intro. to Biology
Mozart Genetics
Mozart Computational Biology

NAME TITLE
-------------------- --------------------------------------------------
Mozart Intro. to Computer Science
Mozart Game Design
Mozart Robotics
Mozart Image Processing
Mozart Database System Concepts
Mozart Intro. to Digital Systems
Mozart Investment Banking
Mozart World History
Mozart Music Video Production
Mozart Physical Principles
Einstein Intro. to Biology

NAME TITLE
-------------------- --------------------------------------------------
Einstein Genetics
Einstein Computational Biology
Einstein Intro. to Computer Science
Einstein Game Design
Einstein Robotics
Einstein Image Processing
Einstein Database System Concepts
Einstein Intro. to Digital Systems

39. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 39

Einstein Investment Banking
Einstein World History
Einstein Music Video Production

NAME TITLE
-------------------- --------------------------------------------------
Einstein Physical Principles
El Said Intro. to Biology
El Said Genetics
El Said Computational Biology
El Said Intro. to Computer Science
El Said Game Design
El Said Robotics
El Said Image Processing
El Said Database System Concepts
El Said Intro. to Digital Systems
El Said Investment Banking

NAME TITLE
-------------------- --------------------------------------------------
El Said World History
El Said Music Video Production
El Said Physical Principles
Katz Intro. to Biology
Katz Genetics
Katz Computational Biology
Katz Intro. to Computer Science
Katz Game Design
Katz Robotics
Katz Image Processing
Katz Database System Concepts

NAME TITLE
-------------------- --------------------------------------------------
Katz Intro. to Digital Systems
Katz Investment Banking
Katz World History
Katz Music Video Production
Katz Physical Principles
Katz Intro. to Biology
Katz Genetics
Katz Computational Biology
Katz Intro. to Computer Science
Katz Game Design

40. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 40

Katz Robotics

NAME TITLE
-------------------- --------------------------------------------------
Katz Image Processing
Katz Database System Concepts
Katz Intro. to Digital Systems
Katz Investment Banking
Katz World History
Katz Music Video Production
Katz Physical Principles
Crick Intro. to Biology
Crick Genetics
Crick Computational Biology
Crick Intro. to Computer Science

NAME TITLE
-------------------- --------------------------------------------------
Crick Game Design
Crick Robotics
Crick Image Processing
Crick Database System Concepts
Crick Intro. to Digital Systems
Crick Investment Banking
Crick World History
Crick Music Video Production
Crick Physical Principles
Crick Intro. to Biology
Crick Genetics

NAME TITLE
-------------------- --------------------------------------------------
Crick Computational Biology
Crick Intro. to Computer Science
Crick Game Design
Crick Robotics
Crick Image Processing
Crick Database System Concepts
Crick Intro. to Digital Systems
Crick Investment Banking
Crick World History
Crick Music Video Production
Crick Physical Principles

41. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 41

NAME TITLE
-------------------- --------------------------------------------------
Brandt Intro. to Biology
Brandt Genetics
Brandt Computational Biology
Brandt Intro. to Computer Science
Brandt Game Design
Brandt Robotics
Brandt Image Processing
Brandt Database System Concepts
Brandt Intro. to Digital Systems
Brandt Investment Banking
Brandt World History

NAME TITLE
-------------------- --------------------------------------------------
Brandt Music Video Production
Brandt Physical Principles
Brandt Intro. to Biology
Brandt Genetics
Brandt Computational Biology
Brandt Intro. to Computer Science
Brandt Game Design
Brandt Robotics
Brandt Image Processing
Brandt Database System Concepts
Brandt Intro. to Digital Systems

NAME TITLE
-------------------- --------------------------------------------------
Brandt Investment Banking
Brandt World History
Brandt Music Video Production
Brandt Physical Principles
Brandt Intro. to Biology
Brandt Genetics
Brandt Computational Biology
Brandt Intro. to Computer Science
Brandt Game Design
Brandt Robotics
Brandt Image Processing

NAME TITLE
-------------------- --------------------------------------------------

42. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 42

Brandt Database System Concepts
Brandt Intro. to Digital Systems
Brandt Investment Banking
Brandt World History
Brandt Music Video Production
Brandt Physical Principles
Kim Intro. to Biology
Kim Genetics
Kim Computational Biology
Kim Intro. to Computer Science
Kim Game Design

NAME TITLE
-------------------- --------------------------------------------------
Kim Robotics
Kim Image Processing
Kim Database System Concepts
Kim Intro. to Digital Systems
Kim Investment Banking
Kim World History
Kim Music Video Production
Kim Physical Principles

195 rows selected.

Same query using Natural Join and Join on common attributes

SQL> select name, title
2 from (instructor natural join teaches) join course using (course_id);

NAME TITLE
-------------------- --------------------------------------------------
Srinivasan Intro. to Computer Science
Srinivasan Robotics
Srinivasan Database System Concepts
Wu Investment Banking
Mozart Music Video Production
Einstein Physical Principles
El Said World History
Katz Intro. to Computer Science
Katz Image Processing
Crick Intro. to Biology
Crick Genetics

43. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 43

NAME TITLE
-------------------- --------------------------------------------------
Brandt Game Design
Brandt Game Design
Brandt Image Processing
Kim Intro. to Digital Systems

15 rows selected.

44. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 44

The RENAME Operation
The SQL allows renaming relations and attributes using the as clause:
old-name as new-name

The attribute name to be replaced with the name instructor_name of instructor relation

SQL> select * from instructor;

ID NAME DEPT_NAME SALARY
----- -------------------- -------------------- ----------
10101 Srinivasan Comp.Sci. 65000
12121 Wu Finance 90000
15151 Mozart Music 40000
22222 Einstein 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 teaches;

ID COURSE_I SEC_ID SEMEST YEAR
----- -------- -------- ------ ----------
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

45. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 45


ID COURSE_I SEC_ID SEMEST YEAR
----- -------- -------- ------ ----------
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 name as instructor_name, course_id
2 from instructor, teaches
3 where instructor.ID= teaches.ID;

INSTRUCTOR_NAME COURSE_I
-------------------- --------
Srinivasan CS-101
Srinivasan CS-315
Srinivasan CS-347
Wu FIN-201
Mozart MU-199
Einstein PHY-101
El Said HIS-351
Katz CS-101
Katz CS-319
Crick BIO-101
Crick BIO-301

INSTRUCTOR_NAME COURSE_I
-------------------- --------
Brandt CS-190
Brandt CS-190
Brandt CS-319
Kim EE-181

15 rows selected.

46. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 46

Tuple Variables
Tuple variables are defined in the from clause via the use of the as clause. Keyword as is optional and may
be omitted
borrower as T ≡ borrower T


For all instructors in the university who have taught some course, find their names and the course ID
of all courses they taught

SQL> select * from instructor;

ID NAME DEPT_NAME SALARY
----- -------------------- -------------------- ----------
10101 Srinivasan Comp.Sci. 65000
12121 Wu Finance 90000
15151 Mozart Music 40000
22222 Einstein 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 teaches;

ID COURSE_I SEC_ID SEMEST YEAR
----- -------- -------- ------ ----------
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

47. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 47

76766 BIO-101 1 Summer 2009
76766 BIO-301 1 Summer 2010

ID COURSE_I SEC_ID SEMEST YEAR
----- -------- -------- ------ ----------
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 T.name, S.course_id
2 from instructor as T, teaches as S
3 where T.ID= S.ID;
from instructor as T, teaches as S
*
ERROR at line 2:
ORA-00933: SQL command not properly ended

SQL> select T.name, S.course_id
2 from instructor T, teaches S
3 where T.ID= S.ID;

NAME COURSE_I
-------------------- --------
Srinivasan CS-101
Srinivasan CS-315
Srinivasan CS-347
Wu FIN-201
Mozart MU-199
Einstein 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

48. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 48


15 rows selected.

Find the names of all instructors whose salary is greater than at least one instructor in the Biology
department

SQL> select * from instructor;

ID NAME DEPT_NAME SALARY
----- -------------------- -------------------- ----------
10101 Srinivasan Comp.Sci. 65000
12121 Wu Finance 90000
15151 Mozart Music 40000
22222 Einstein 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 distinct T.name
2 from instructor T, instructor S
3 where T.salary > S.salary and S.dept_name = 'Biology';

NAME
--------------------
Einstein
Gold
Brandt
Wu
Kim
Singh
Katz

7 rows selected.

49. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 49

String Operations
SQL includes a string-matching operator for comparisons on character strings. The operator “like” uses
patterns that are described using two special characters:
 percent (%). The % character matches any substring.
 underscore (_). The _ character matches any character.

SQL supports a variety of string operations such as
 concatenation (using “||”)
 converting from upper to lower case (and vice versa)
 Finding string length, extracting substrings, etc.

Find the names of all departments whose building name includes the substring ‘Watson'

SQL> select * from department;

DEPT_NAME BUILDING BUDGET
-------------------- -------------------- ----------
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 dept_name
2 from department
3 where building like '%Watson%';

DEPT_NAME
--------------------
Biology
Physics

50. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 50

Attribute Specification in Select Clause
The asterisk symbol “ * ” can be usedin the select clause to denote “all attributes.”

All attributes of instructor are to be selected from the relation instructor and teaches.

SQL> select * from instructor;

ID NAME DEPT_NAME SALARY
----- -------------------- -------------------- ----------
10101 Srinivasan Comp.Sci. 65000
12121 Wu Finance 90000
15151 Mozart Music 40000
22222 Einstein 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 teaches;

ID COURSE_I SEC_ID SEMEST YEAR
----- -------- -------- ------ ----------
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 YEAR

51. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 51

----- -------- -------- ------ ----------
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 instructor.*
2 from instructor, teaches
3 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 Einstein 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.

52. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 52

Ordering the display of tuples:
SQL offers the user some control over the order in which tuples in a relation are displayed. The order by
clause causes the tuples in the result of a query to appear in sorted order. We may specify desc for
descending order or asc for ascending order, for each attribute; ascending order is the default.

List in alphabetic order all instructors in the Physics department

SQL> select * from instructor;

ID NAME DEPT_NAME SALARY
----- -------------------- -------------------- ----------
10101 Srinivasan Comp.Sci. 65000
12121 Wu Finance 90000
15151 Mozart Music 40000
22222 Einstein 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 name
2 from instructor
3 where dept_name = 'Physics'
4 order by name;

NAME
--------------------
Einstein
Gold

List the entire instructor relation in descending order of salary. If several instructors have the same
salary, we order them in ascending order by name.

SQL> select * from instructor;

53. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 53

ID NAME DEPT_NAME SALARY
----- -------------------- -------------------- ----------
10101 Srinivasan Comp.Sci. 65000
12121 Wu Finance 90000
15151 Mozart Music 40000
22222 Einstein 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 *
2 from instructor
3 order by salary desc, name asc;

ID NAME DEPT_NAME SALARY
----- -------------------- -------------------- ----------
22222 Einstein 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.

54. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 54

Where Clause Predicates
SQL includes a between comparison operator to simplify where clauses that specify that a value be less
than or equal to some value and greater than or equal to some other value.

Find the names of instructors with salary amounts between $90,000 and $100,000

SQL> select * from instructor;

ID NAME DEPT_NAME SALARY
----- -------------------- -------------------- ----------
10101 Srinivasan Comp.Sci. 65000
12121 Wu Finance 90000
15151 Mozart Music 40000
22222 Einstein 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 name
2 from instructor
3 where salary between 90000 and 100000;

NAME
--------------------
Wu
Einstein
Brandt

SQL> select name
2 from instructor
3 where salary <= 100000 and salary >= 90000;

NAME
--------------------

55. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 55

Wu
Einstein
Brandt

Find the instructor names and the courses they taught for all instructors in the Biology department
who have taught some course.

SQL> select * from instructor;

ID NAME DEPT_NAME SALARY
----- -------------------- -------------------- ----------
10101 Srinivasan Comp.Sci. 65000
12121 Wu Finance 90000
15151 Mozart Music 40000
22222 Einstein 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 teaches;

ID COURSE_I SEC_ID SEMEST YEAR
----- -------- -------- ------ ----------
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

56. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 56


ID COURSE_I SEC_ID SEMEST YEAR
----- -------- -------- ------ ----------
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 name, course_id
2 from instructor, teaches
3 where instructor.ID= teaches.ID and dept_name = 'Biology';

NAME COURSE_I
-------------------- --------
Crick BIO-101
Crick BIO-301

57. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 57

Set Operations:
The SQL operations union, intersect, and except operate on relations and correspond to the relational-
algebra operations ∪, ∩, and −. Like union, intersection, and set difference in relational algebra, the
relations participating in the operations must be compatible; that is, they must have the same set of
attributes.
 In union operation, the number of duplicate tuples in the result is equal to the total number of
duplicates that appear in both d and b. Thus, if Jones has three accounts and two loans at the bank,
then there will be five tuples with the name Jones in the result.
 In intersection operation, the number of duplicate tuples that appear in the result is equal to the
minimum number of duplicates in both d and b. Thus, if Jones has three accounts and two loans at
the bank, then there will be two tuples with the name Jones in the result.
 In except operation, the number of duplicate copies of a tuple in the result is equal to the number of
duplicate copies of the tuple in d minus the number of duplicate copies of the tuple in b, provided
that the difference is positive. Thus, if Jones has three accounts and one loan at the bank, then there
will be two tuples with the name Jones in the result. If, instead, this customer has two accounts and
three loans at the bank, there will be no tuple with the name Jones in the result.


Union Operation without duplicates

Find the set of all courses taught either in Fall 2009 or in Spring 2010, or both
SQL> select * from section;

COURSE_I SEC_ID SEMEST YEAR BUILDING ROOM_NU TIME
-------- -------- ------ ---------- --------------- ------- ----
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 ROOM_NU TIME
-------- -------- ------ ---------- --------------- ------- ----
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

58. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 58


15 rows selected.

1) The set of all courses taught in the Fall 2009 semester

SQL> select course_id
2 from section
3 where semester = 'Fall' and year= 2009;

COURSE_I
--------
CS-101
CS-347
PHY-101


2) The set of all courses taught in the Spring 2010 semester

SQL> select course_id
2 from section
3 where semester = 'Spring' and year= 2010;

COURSE_I
--------
CS-101
CS-315
CS-319
CS-319
FIN-201
HIS-351
MU-199

7 rows selected.

SQL> (select course_id
2 from section
3 where semester = 'Fall' and year= 2009)
4 union
5 (select course_id
6 from section
7 where semester = 'Spring' and year= 2010);

COURSE_I
--------

59. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 59

CS-101
CS-315
CS-319
CS-347
FIN-201
HIS-351
MU-199
PHY-101

8 rows selected.

Union Operation with all duplicates

SQL> (select course_id
2 from section
3 where semester = 'Fall' and year= 2009)
4 union all
5 (select course_id
6 from section
7 where semester = 'Spring' and year= 2010);

COURSE_I
--------
CS-101
CS-347
PHY-101
CS-101
CS-315
CS-319
CS-319
FIN-201
HIS-351
MU-199

10 rows selected.

The Intersect Operation

Find the set of all courses taught in the Fall 2009 as well as in Spring 2010

SQL> select * from section;

COURSE_I SEC_ID SEMEST YEAR BUILDING ROOM_NU TIME
-------- -------- ------ ---------- --------------- ------- ----

60. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 60

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 ROOM_NU TIME
-------- -------- ------ ---------- --------------- ------- ----
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.

1) The set of all courses taught in the Fall 2009 semester

SQL> select course_id
2 from section
3 where semester = 'Fall' and year= 2009;

COURSE_I
--------
CS-101
CS-347
PHY-101


2) The set of all courses taught in the Spring 2010 semester

SQL> select course_id
2 from section
3 where semester = 'Spring' and year= 2010;

COURSE_I
--------
CS-101
CS-315

61. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 61

CS-319
CS-319
FIN-201
HIS-351
MU-199

7 rows selected.

SQL> (select course_id
2 from section
3 where semester = 'Fall' and year= 2009)
4 intersect
5 (select course_id
6 from section
7 where semester = 'Spring' and year= 2010);

COURSE_I
--------
CS-101

Does not work ‘intersect all’ with SQL

SQL> (select course_id
2 from section
3 where semester = 'Fall' and year= 2009)
4 intersect all
5 (select course_id
6 from section
7 where semester = 'Spring' and year= 2010);
intersect all
*
ERROR at line 4:
ORA-00928: missing SELECT keyword

The except and except all Operation does not work with SQL

Find all courses taught in the Fall 2009 semester but not in the Spring 2010 semester

SQL> (select course_id
2 from section
3 where semester = 'Fall' and year= 2009)
4 except
5 (select course_id
6 from section

62. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 62

7 where semester = 'Spring' and year= 2010);
except
*
ERROR at line 4:
ORA-00933: SQL command not properly ended


SQL> (select course_id
2 from section
3 where semester = 'Fall' and year= 2009)
4 except all
5 (select course_id
6 from section
7 where semester = 'Spring' and year= 2010);
except all
*
ERROR at line 4:
ORA-00933: SQL command not properly ended

63. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 63

Null Values
Null values present special problems in relational operations, including arithmetic operations, comparison operations, and
set operations.
The result of an arithmetic expression (involving, for example +, −, ∗, or /) is null if any of the input values is null.
Comparisons involving nulls are more of a problem. For example, consider the comparison “1 < null”. It would be wrong
to say this is true since we do not know what the null value represents. But it would likewise bewrong to claim this
expression is false; if we did, “not (1 < null)” would evaluate to true, which does not make sense. SQL therefore treats as
unknown the result of any comparison involving a null value

Find all instructors who appear in the instructor relation with null values for salary.

SQL> select * from instructor;

ID NAME DEPT_NAME SALARY
----- -------------------- -------------------- ----------
10101 Srinivasan Comp.Sci. 65000
12121 Wu Finance 90000
15151 Mozart Music 40000
22222 Einstein 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 name
2 from instructor
3 where salary is null;

no rows selected

64. Mrs. Sunita M Dol, CSE Department

WIT, Solapur Page 64

Practice Problem Statements
 Check the added tuples in the patient relation
 Select all workers who are all wardboys in the hospital.
 Display the names of the department which starts with “Gen”
 Display the department names in the ascending oder in the department realtion
 Obtain the names of nurses who have attended the emergency.
 Count the number of nurses, wardboys, ambulance drivers
 Make union of doctor names and worker name


References:
 Database system concepts by Abraham Silberschatz, Henry F. Korth, S. Sudarshan (McGraw Hill
International Edition) sixth edition.
 Database system concepts by Abraham Silberschatz, Henry F. Korth, S. Sudarshan (McGraw Hill
International Edition) fifth edition.
 http://codex.cs.yale.edu/avi/db-book/db4/slide-dir/
 http://codex.cs.yale.edu/avi/db-book/db5/slide-dir/
 http://codex.cs.yale.edu/avi/db-book/db6/slide-dir/
 MOOCs: Database Management System:
https://onlinecourses.nptel.ac.in/noc18_cs15