Chapter - 5

Structured Query Language SQL

Overview of SQL
It is a 4 GL Oracle9i in specific DML DDL Triggers and integrity constraints Embedded and Dynamic SQL Client-Server Execution and remote database access Transaction management Security OODBMS features, Data mining, spatial data, and XML data management
BN Shankar Gowda, BIT
2

Basic SQL Commands

Statement SELECT INSERT UPDATE DELETE CREATE ALTER DROP RENAME COMMIT ROLLBACK SAVEPOINT GRANT REVOKE Description Data retrieval statement. Data Manipulation Language (DML). Add rows, change data, and delete few rows. Create new tables/views, remove tables/ views, and change the schema. Modified values of database are permanently written into disk, rollback the changes made. Access control can be assigned or changed.

BN Shankar Gowda, BIT

Basic data types in Oracle

Data type CHAR (size) VARCHAR2(size DATE BLOB CLOB BFILE LONG LONG RAW NUMBER(size) NUMBER(size,d) DECIMAL FLOAT INTEGER SMALLINT Description Fixed length character. Max = 2000. Variable length character string. Max = 4000. Date. Vaild range is from Jan 1, 4712 B.C. to Dec 31, 4712 A.D. Binary large object. Max = 4GB. Character large object. Max = 4GB. Pointer to binary OS file. Character data of variable size. Max = 2GB. Raw binary data. Rest is same as LONG. Numbers. Max. size = 40 digits. Numbers. Range = 1.0E-130 to 9.9E125. Same as NUMBER. size/d can't be specified. Same as NUMBER. Same as NUMBER. size/d can't be specified. Same as NUMBER.
BN Shankar Gowda, BIT
4

Example tables
Employee
SSN 1111 2222 3333 4444 5555 Name Deepak Nandagopal Pooja Prasad Reena BDate 5-Jan-62 10-Dec-60 22-Jan-65 11-Jan-57 15-Jan-85 Salary 22000 30000 18000 32000 8000 MgrSSN 4444 4444 2222 Null 4444 DNo 1 3 2 3 3

Department
DNo 1 2 3 DName Admin Research Accounts Loc Chennai Bangalore Bangalore

BN Shankar Gowda, BIT

DDL
CREATE TABLE Department( DNo number(3) not null, DName varchar2(10) not null, Loc varchar2(15), primary key (DNo)); CREATE TABLE Employee( SSN number(4) not null, Name varchar2(20) not null, BDate date, Salary number(10,2), MgrSSN number(4), DNo number(2) not null, primary key (SSN), foreign key (MgrSSN) references Employee(SSN), foreign key (DNo) references Department(DNo));
BN Shankar Gowda, BIT
6

Data Retrieval Statement (SELECT)

Syntax
SELECT *|{[DISTINCT] column | expression} FROM table(s);

The basic SELECT statement must include the following: - A SELECT clause. - A FROM clause.
BN Shankar Gowda, BIT
7

Example-1
SELECT * FROM Employee; The * indicates that it should retrieve all the columns from Employee table. The output of this query is shown below: Output-1
SSN NAME BDATE SALARY MGRSSN DNO ---- -------------------- --------- --------- --------- --------4444 Prasad 11-JAN-57 32000 3 5555 Reena 15-JAN-85 8000 4444 3 1111 Deepak 05-JAN-62 22000 4444 1 2222 Nandagopal 10-DEC-60 30000 4444 3 3333 Pooja 22-JAN-65 18000 2222 2

BN Shankar Gowda, BIT

Example-2 SELECT * FROM Employee ORDER BY SSN; Output-2

SSN NAME BDATE SALARY MGRSSN DNO ----- -------------------- --------- --------- --------- --------------------------------1111 Deepak 05-JAN-62 22000 4444 1 2222 Nandagopal 10-DEC-60 30000 4444 3 3333 Pooja 22-JAN-65 18000 2222 2 4444 Prasad 11-JAN-57 32000 3 5555 Reena 15-JAN-85 8000 4444

BN Shankar Gowda, BIT

Using arithmetic operators

SELECT Name, Salary, Salary * 12 FROM Employee;

Using aliases An alias when used for a column: Renames a column heading It is useful in arithmetic calculations. AS keyword can optionally be used between column name and alias name. Example-3
SELECT FROM OR SELECT FROM Name, Salary, Salary * 12 AS YRLY_SALARY Employee; Name, Salary, Salary * 12 "YRLY_SALARY" Employee;

BN Shankar Gowda, BIT

10

 Example-4

DESCRIBE Employee;
OR

DESC Employee;
Output-4

Name ------------------------------SSN NAME BDATE SALARY MGRSSN DNO

Null? -------NOT NULL NOT NULL

Type ---NUMBER(4) VARCHAR2(20) DATE NUMBER(10,2) NUMBER(4) NOT NULL NUMBER(2)

BN Shankar Gowda, BIT

11

Select Statement with Where

Example-5 SELECT Name, Salary FROM Employee WHERE Salary > 25000; Example-6 SELECT DName, Loc FROM Department WHERE Loc = 'Bangalore'; Example-7 SELECT Name, BDate FROM Employee WHERE BDate = '11-Jan-57';
BN Shankar Gowda, BIT
12

Example-8 SELECT Name, BDate FROM Employee WHERE Salary BETWEEN 25000 AND 30000; Example-9 SELECT SSN, Name FROM Employee WHERE DNo IN (1, 2); Example-10 SELECT Name FROM Employee WHERE Name LIKE 'P%'; Example-11 SELECT Name, DNo FROM Employee WHERE BDate LIKE '__-JAN-__';
BN Shankar Gowda, BIT
13

Example-12 SELECT Name FROM Employee WHERE MgrSSN IS NULL; Example-13 SELECT Name, Salary, DNo FROM Employee WHERE Salary > 30000 AND DNo = 3; Example-14 SELECT Name, Salary FROM Employee WHERE Name LIKE 'P%' OR Salary <= 20000; Example-15 SELECT Name, Salary, DNo FROM Employee ORDER BY DNo DESC, Name;
BN Shankar Gowda, BIT
14

SQL Functions
ROUND(column | expr, n) TRUNC(column | expr, n) MOD(m, n) ABS(n) CEIL(n) FLOOR(n) EXP(n) POWER(n, m) SQRT(n) SIGN(n) LN(n) LOG(n) SIN(n) COS(n) TAN(n) ASIN(n) ACOS(n) ATAN(n) SINH(n) COSH(n) TANH(n) NVL(n, m) VSIZE(n) Rounds to n decimal places. If n is negative, numbers to the left are rounded. Truncates to n decimal places. Returns the remainder of m/n. Absolute value of n. Smallest integer larger than n. Largest integer smaller than n. en nm Square root of n. 1 if n is positive, -1 if negative, 0 if zero. Natural log of n (lg n) log10 n Sine of n. Cosine of n. Tangent of n. Arc sine of n (in radians). Arc cosine of n (in radians). Arc tangent of n (in radians). Hyperbolic sine value of n. Hyperbolic cosine value of n. Hyperbolic tan value of n. Null Value Substitute m for n if n = null. Storage size of n.
BN Shankar Gowda, BIT
15

Working with Dates

Century Year Month Day Hour Minute Second 19 99 07 23 4 10 53

SELECT SYSDATE FROM DUAL;

BN Shankar Gowda, BIT

16

Example-16 (MONTHS_BETWEEN) SELECT MONTHS_BETWEEN(SYSDATE, '09-JAN-1983') "Experience" FROM DUAL; Output-16 Experience --------------247.73471 Example-17 (GREATEST & LEAST) The function GREATEST finds the earliest date and LEAST finds the oldest date in the list. SELECT GREATEST('10-JAN-93', '10-JAN-98'), LEAST('10-JAN-93', '10-JAN-98') FROM DUAL; Output-17 GREATEST( LEAST('10 --------- --------10-JAN-98 10-JAN-93
BN Shankar Gowda, BIT
17

Conversion Functions
VARCHAR2 or CHAR is converted to NUMBER

VARCHAR2 or CHAR is converted to DATE NUMBER is converted to VARCHAR2

DATE is converted to VARCHAR2

BN Shankar Gowda, BIT

18

Use of TO_CHAR
TO_CHAR function converts a date format to a character string Example-18 SELECT Name, Bdate, TO_CHAR(BDate, 'DD/MM/YY') AS "Formatted" FROM Employee WHERE SSN = 2222; Example-19
SELECT FROM WHERE Name, Bdate, TO_CHAR(BDate, 'Month, ddth, YYYY') AS "Formatted" Employee Name = 'Pooja';

Output-19
NAME BDATE Formatted -------------------- --------- --------------------Pooja 22-JAN-65 January , 22nd, 1965
BN Shankar Gowda, BIT
19

Example-20 SELECT Name, Bdate, TO_CHAR(BDate, 'fmMonth, fmddth, YYYY') AS

"Formatted"

FROM Employee; Output Prasad 11-JAN-57 January, 11th, 1957 Example-21 SELECT Name, Bdate, TO_CHAR(BDate, 'fmMonth, fmddth, YYYY
"at" HH:MI P.M.')AS "Formatted"

FROM Employee WHERE SSN = 2222; Output

Nandagopal 10-DEC-60 December, 10th, 1960 at 12:00 A.M.
Note: fm suppresses unnecessary blanks Use HH12 for 12 hrs clock and HH24 for 24 hrs clock
BN Shankar Gowda, BIT
20

Use of TO_DATE
TO_DATE function is to convert any character literal string into a valid date format. Example-22 SELECT TO_DATE('1-Sep-2003', 'DD/MM/YYYY') FROM DUAL; Output-22
TO_DATE(' --------01-SEP-03

Example-23 SELECT TO_DATE('08/30/2003', 'DD/MM/YYYY') FROM DUAL; Output-23 ERROR at line 1: ORA-01843: not a valid month
BN Shankar Gowda, BIT
21

Program SELECT LOWER('Bangalore') FROM DUAL; SELECT UPPER('Bangalore') FROM DUAL;

Character Functions
Output bangalore BANGALORE Bangalore Institute Of Technology

SELECT INITCAP('bangalore institute of technology') FROM DUAL; SELECT CONCAT('Database ', 'Management') FROM DUAL; SELECT SUBSTR('Database', 5, 4) FROM DUAL; SELECT LENGTH('Database') FROM DUAL; SELECT FROM SELECT FROM INSTR('Database', 'b') DUAL; INSTR('Database', 'x') DUAL;

Database Management

base 8

5 0

SELECT LPAD(Salary, 8, '*') FROM Employee WHERE SSN = 1111; SELECT FROM WHERE SELECT RPAD(Salary, 8, '*') Employee SSN = 1111; LTRIM(' Database', ' ')

***22000

22000***

Database

BN Shankar Gowda, BIT

22

Aggregate Functions
COUNT AVG MAX MIN STDDEV SUM VARIANCE
BN Shankar Gowda, BIT
23

Example-24
SELECT COUNT(*) AS "No. of Employees" FROM Employee;

Example-25
SELECT SUM(Salary) AS Total FROM Employee;

Example-26
SELECT Name, MAX(Salary), MIN(Salary) FROM Employee;

BN Shankar Gowda, BIT

24

GROUP BY Clause
The rules to be followed while using GROUP BY clause are given below: You can't have non-group function or column in SELECT clause. Group functions ignore nulls. By default the result of GROUP BY clause sort the data in ascending order. Example:
SELECT DNo, SUM(Salary), COUNT(*), AVG(Salary) FROM Employee GROUP BY DNo;
BN Shankar Gowda, BIT
25

Example-27 SELECT DNo, SUM(Salary), COUNT(*), AVG(Salary) FROM Employee GROUP BY DNo, MgrSSN; HAVING clause
SELECT FROM GROUP BY HAVING DNo, AVG(Salary) Employee DNo DNo = 3;
BN Shankar Gowda, BIT
26

The order of evaluation when all the clauses are specified is given below: 1. First all rows matching the WHERE conditions are retrieved. 2. These rows are grouped using the column(s) in GROUP BY clause. 3. Finally, groups matching the HAVING clause condition are retained. SELECT DNo, AVG(Salary) FROM Employee WHERE BDate LIKE '__-JAN-__' GROUP BY DNo HAVING MAX(Salary) > 10000;
BN Shankar Gowda, BIT
27

MULTITABLE QUERIES
Simple Equi-Joins : guidelines
Table names in the FROM clause is separated with commas. Use appropriate joining condition. This means that the foreign key of table1 will be made equal to the primary key of table2. When the attributes or columns have the same names, tag them with table names using dot notation. Without proper joining condition or attributes the SQL will display the Cartesian product of the tables in the FROM clause.
BN Shankar Gowda, BIT
28

Example-28: Display the employee names and the department names for which they work. SELECT Name, DName FROM Employee, Department WHERE Employee.DNo = Department.DNo; Example-29 : Display only employees working for Accounts department. SELECT Name, Salary, DName FROM Employee, Department WHERE (Employee.DNo = Department.DNo) AND (DName = 'Accounts');
BN Shankar Gowda, BIT
29

Self-Join and Table Aliases

Example-30 : Find the employee who earns more than Nandagopal. SELECT e1.Name, e1.Salary FROM Employee e1, Employee e2 WHERE (e1.Salary > e2.Salary) AND (e2.Name = 'Nandagopal');

BN Shankar Gowda, BIT

30

Right-Outer Join
Example-31 : SELECT Name, DName FROM Employee E, Department D WHERE E.Name = D.DName(+); Output-31 NAME DNAME -------------------- ---------Deepak Nandagopal Pooja Prasad Reena
BN Shankar Gowda, BIT
31

Left-Outer Join
Example-32 SELECT Name, DName FROM Employee E, Department D WHERE E.Name(+) = D.DName; Output-32 NAME DNAME -------------------- ---------Accounts Admin Research
BN Shankar Gowda, BIT
32

NESTED QUERIES or SUB QUERIES

SELECT <column(s)> FROM table WHERE <condn operator> (SELECT <column> FROM table);

Outer query uses the result of the inner query. If the inner query returns only one row it is called as single row sub queries. Alternatively if the inner query returns a set of rows (more than one row) it is classified as multiple-row sub queries. The comparison condition may be a single row operator like >, =, >=, <, <=, <>) or multiple row operators like IN, ANY, ALL.
BN Shankar Gowda, BIT
33

Single-Row Nested Queries

Display the names of the employees working for Accounts department. SELECT Name FROM Employee WHERE DNo = (SELECT DNO FROM Department WHERE DName = 'Accounts');

Display names of employees whose salary is greater than the employee SSN=1111. SELECT Name FROM Employee WHERE Salary > (SELECT Salary FROM Employee WHERE Name = 1111);
BN Shankar Gowda, BIT
34

Example-33 Display all the employees drawing more than or equal to the average salary of department number 3. SELECT Name, Salary FROM Employee WHERE Salary >= (SELECT AVG(Salary) FROM Employee GROUP BY DNO HAVING DNo = 3);
BN Shankar Gowda, BIT
35

Multiple-Row Nested Queries

IN: Equal to any member in the list.
ANY: Compare value to each value returned by the subquery. ALL: Compare value to all the values returned by the subquery.

BN Shankar Gowda, BIT

36

Display the name of the highest paid employee.

SELECT Name, Salary FROM Employee WHERE Salary = (SELECT MAX(Salary) FROM Employee);

SELECT Name, Salary FROM Employee WHERE Salary IN (SELECT MAX(Salary) FROM Employee);
Both '=' and 'IN' works, because the inner query produces a single tuple.
BN Shankar Gowda, BIT
37

Find the Name and Salary of people who draw in the range Rs. 20,000 to Rs. 40,000. Select Name, Salary from Employee where Salary = (Select Salary from Employee where Salary between 20000 and 40000);
Error: ORA-01427: single-row subquery returns more than one row

Correct Query:

Select Name, Salary from Employee where Salary IN (Select Salary from Employee where Salary between 20000 and 40000);
BN Shankar Gowda, BIT
38

ANY and ALL

Operator Meaning Example Less than the e < ANY (5,3,8): e is less than any <ANY maximum. single item in the list (5,3,8). Even 7 qualifies, because 7 < 8. More than >ANY e > ANY (5,3,8): e is less than any the minimum. single item in the list (5,3,8). Even 4 qualifies, because 4 > 3. Same as IN. e = ANY(5,3,8). All values in the =ANY list qualify. Less than the e < ALL (5,3,8): Anything below 3 <ALL maximum. qualifies. More than >ALL e > ALL (5,3,8): Anything greater the minimum. than 8 qualifies. Not equal to e != (5,3,8): Anything other than !=ALL anything. 5,3, and 8 qualifies.
BN Shankar Gowda, BIT
39

Example-34: SELECT Name, Salary FROM Employee WHERE Salary < ANY (SELECT FROM WHERE Example-35: SELECT Name, Salary FROM Employee WHERE Salary > ANY (SELECT FROM WHERE

Salary Employee DNo = 3);

Salary Employee DNo = 3);

40

BN Shankar Gowda, BIT

Example-35: SELECT Name, Salary FROM Employee WHERE Salary < ALL (SELECT Salary FROM Employee WHERE DNo = 3); Example-36: SELECT Name, Salary FROM Employee WHERE Salary > ALL (SELECT Salary FROM Employee WHERE DNo = 3); >ALL means greater than the greatest and <ALL means less than the lowest value.
BN Shankar Gowda, BIT
41

CREATING and ALTERING DATABASE OBJECTS

Table: A tabular structure that stores data. View: A tabular structure similar to a table but it is a collection of one or more tables.

Sequence: Automatically generates a sequence of numbers.

Index: Provides an efficient access structure.
BN Shankar Gowda, BIT
42

CREATE TABLE Employee( SSN Number(4) not null, Name Varchar2(20) not null, BDate Date, Salary Number(10,2), MgrSSN Number(4), DNo Number(2) not null, Primary Key (SSN), Foreign Key (MgrSSN) references Employee(SSN), Foreign Key (DNo) references Department(DNo));

BN Shankar Gowda, BIT

43

Names of the tables/views SELECT * FROM TAB; Schema details of a table DESC Employee;
Name Null? Type ------------------------------- -------- ---SSN NOT NULL NUMBER(4) NAME NOT NULL VARCHAR2(20) BDATE DATE SALARY NUMBER(10,2) MGRSSN NUMBER(4) DNO NOT NULL NUMBER(2)
BN Shankar Gowda, BIT
44

ON DELETE constraint CREATE TABLE Department( DNo Number(3) not null, DName Varchar2(10) not null, Loc Varchar2(15), Primary Key (DNo), Manager Number(4) references
Employee(SSN) ON DELETE CASCADE);

Creating tables with a Subquery CREATE TABLE Emp AS SELECT SSN, Name, Salary FROM Employee;
BN Shankar Gowda, BIT
45

ALTER TABLE Statement

ALTER TABLE Employee ADD Phone Number(7) not null;
(Note: to add not null constraint, the column must be empty)

ALTER TABLE Employee MODIFY Phone Varchar2(10);

(Note: to modify data type, the column must be empty)

ALTER TABLE Employee DROP COLUMN Phone;

BN Shankar Gowda, BIT

46

Disabling and Enabling Constraints

CREATE TABLE Employee( Salary Number(10,2) CONSTRAINT Ch_Sal CHECK (Salary > 0)); ALTER TABLE Employee DISABLE CONSTRAINT Ch_Sal;

ALTER TABLE Employee ENABLE CONSTRAINT Ch_Sal;

BN Shankar Gowda, BIT
47

CREATE VIEW Statement

The advantages of using views are: It restricts data access. Reduces joining of more tables often. Many users can access a particular view with proper privileges. Example
CREATE VIEW AS SELECT FROM WHERE View created.
BN Shankar Gowda, BIT
48

Emp_Dept SSN, Name, DName, Salary Employee E, Department D E.DNo = D.DNo;

Restrictions on Views
There are few restrictions on views: You can not insert new rows nor update the view table.
(Error Message: ORA-01776: cannot modify more than one base table through a join view)

You can not alter the constraints or data types of the columns. If any changes are made to the base table(s), view table will get updated automatically.
BN Shankar Gowda, BIT
49

CREATE SEQUENCE Statement

How to use a Sequence?
Step-1: CREATE SEQUENCE Dept_Seq START WITH 10; Step-2: INSERT INTO Department values(Dept_Seq.NEXTVAL, 'Sales', 'Belgaum');

Dropping a sequence DROP SEQUENCE Dept_Seq; Creating an index for Employee table on Name CREATE INDEX IDXSSN ON Employee (Name); Dropping an index DROP INDEX IDXSSN;
BN Shankar Gowda, BIT
50

Rights
SQL Server 2000 GRANT { ALL | statement [ ,...n ] } TO security_account [ ,...n ] Object permissions: GRANT { ALL [ PRIVILEGES ] | permission [ ,...n ] } { [ ( column [ ,...n ] ) ] ON { table | view } | ON { table | view } [ ( column [ ,...n ] ) ] | ON { stored_procedure | extended_procedure } | ON { user_defined_function } } TO security_account [ ,...n ] [ WITH GRANT OPTION ] [ AS { group | role } ]
BN Shankar Gowda, BIT
51

Rights (Contd)
Oracle 9i:

GRANT {objectprivileges | ALL} [(columnname), {objectprivileges(columnname)] ON objectname TO {username | rolename | PUBLIC} [WITH GRANT OPTION]
where, objectprivileges: INSERT or UPDATE objectname : table/view/sequence

Example: GRANT UPDATE (ssn) ON employee TO bit;

BN Shankar Gowda, BIT
52

Creating Users
1) 2) 3)
4)

CONNECT SYSTEM/MANAGER; CREATE USER Guest IDENTIFIED BY bit; GRANT CONNECT, RESOURCE TO Guest;
REVOKE Select ON Employee FROM Guest;

Commit, Rollback, etc.

BN Shankar Gowda, BIT

53

Examples
Objective 1
Create a user account called sng with password apple. create user sng identified by apple; Format for Grant command grant {system privilege} to user [with admin options] system privilege: select/delete/insert/update and instead of user you can specify public as well. Format for Revoke command: revoke {system privilege} from user [with admin options] Revoking does not delete a user from Oracle or remove any tables created by him. It simply prohibits him from accessing the database.
BN Shankar Gowda, BIT
54

Examples (contd)
Objective 2 Create another user deepak identified by rock. create user deepak identified by rock; At this point of time you must provide grant permission for connect and resource. Therefore execute the following commands: grant connect, resource to sng; grant connect, resource to deepak; connect sng/apple;
BN Shankar Gowda, BIT
55

Examples (contd)
Objective 3 This objective shows how to grant permission to all. This can be done by using the keyword public. For example, grant select on EMPLOYEE to public;
This statement grants access to Employee table to all and the following command removes this privilege from all. revoke select on EMPLOYEE from public; Provide select permission to deepak for the table EMPLOYEE,assuming that this table is owned by sng. grant select on EMPLOYEE to deepak;
BN Shankar Gowda, BIT
56

Objective 5
Display the contents of EMPLOYEE table.
connect deepak/rock; // deepak gets connected select * from sng.EMPLOYEE; Unless a synonym is used, the table name must be preceded by the username of the owner of the table. Otherwise you will get an error. The user deepak can create a view to access the EMPLOYEE table. create view EMPLOYEE as select * from sng.EMPLOYEE; Creating a synonym. create synonym EMPLOYEE for sng.EMPLOYEE; If the table/view is not owned by a particular user, then he can not pass the granting of that table/view to other users. ERROR: grant select on sng.EMPLOYEE to reena;

Here, reena is some other user.

BN Shankar Gowda, BIT
57

DML Statements
INSERT Statement INSERT INTO Employee VALUES (1111, 'Deeapk', '5-Jan-62', 22000, 4444, 1); Inserting through Keyboard INSERT INTO Employee
VALUES (&SSN, &Name, &BDate, &Salary, &MgrSSN, &DNo);

Inserting dates INSERT INTO Employee VALUES (6666, 'John', TO_DATE('5-Jan-2003 3:40', 'DD-MM-YYYY HH24:SS'), 22000, 4444, 1);
BN Shankar Gowda, BIT
58

Inserting rows from an existing table INSERT INTO EMP2 SELECT * FROM Employee; DELETE Statement DELETE Employee WHERE SSN = 1111; UPDATE Statement UPDATE Employee SET DNo = 1 WHERE Name = 'Nandagopal'; To hike the salary of all employees by 10%. UPDATE Employee SET Salary = Salary * 1.05;
BN Shankar Gowda, BIT
59

ADDITIONAL EXAMPLES
Company Database Example Employee(SSN Char(9), Name Varchar2(20), Bdate Date, Address Varchar2(30), Sex Char(1),Salary Number(10,2), SuperSSN Char(9), DNo Number(2)); Department(Dnumber Number(2), DName Varchar2(10), MgrSSN Char(9), MgrStartDate Date); Project(PNumber Number(2), PName Varchar(10), Plocation Varchar2(15), Dnum Number(2)); Dependent(ESSN Char(9), Dependent_Name Varchar2(15), Sex Char, Bdate Date, Relationship Varchar2(10)); Dept_Locations(DNumber Number(2), Dlocation Varchar2(15)); Works_On(ESSN Char(9), PNo Number(2), Hours Number(3,1));

BN Shankar Gowda, BIT

60

Example-1: Find all employees who were born during 1980.

SELECT Name FROM EMPLOYEE WHERE BDate LIKE '__-___-80';

Example-2: Calculate the wages earned by each employee, assuming the remuneration for each hour is Rs. 250.00.

SELECT Essn, Sum(Hours),Sum(Hours) * 250 FROM Works_On GROUP BY Essn;

Example-3: Retrieve the department number, number of employees in each department and their average salary.

SELECT DNo, Count(*), Avg(Salary) FROM Employee, Department WHERE DNo = DNumber GROUP BY DNo;
BN Shankar Gowda, BIT
61

Example-4: For each project, retrieve the project number, the project name, and the number of employees who work on that project. SELECT PNumber, PName, Count(*) FROM Project, Works_On WHERE PNumber = PNo GROUP BY PNumber, PName; Example-5: For each project on which more than 3 employees work, retrieve the project number, the project name, and the number of employees who work on that project. SELECT PNumber, PName, Count(*) FROM Project, Works_On WHERE PNumber = PNo GROUP BY PNumber, PName HAVING Count(*) > 3; Example-6: Print the number of employees whose salaries exceed more than Rs. 25,000/- in each department. Display the department name also. SELECT DName, Count(*) FROM Department, Employee WHERE DNumber = DNo AND Salary > 25000 GROUP BY DName;
BN Shankar Gowda, BIT
62

STUDENT Database Example

Student(Student_id char(10), SName varchar2(10), Major varchar2(5), GPA number(3,1)) Faculty(Faculty_id char(4), FName char(4), Dept varchar2(5), Desig varchar2(5), Salary number(10,2)) Course(Course_id char(5), CName varchar2(10), Faculty_id char(4)) Enrol(Course_id char(5), Student_id char(10), Grade char(1))
BN Shankar Gowda, BIT
63

Example-1: 'IS6T1'. SELECT FROM WHERE

List the names of all students enrolled for the course SName Student Student_id In( SELECT Student_id FROM Enrol WHERE Course_id = 'IS6T1');

Example-2: List the names of students enrolled for the course 'IS6T1' and have received 'A' grade. SELECT SName FROM Student WHERE Student_id In( SELECT Student_id FROM Enrol WHERE Course_id = 'IS6T1' AND Grade = 'A'); Example-3: List all the departments having an average salary of above Rs. 10000. SELECT Dept FROM Faculty GROUP BY Dept HAVING AVG(Salary) BN > Shankar 10000; Gowda, BIT

64

Example-5: List the names of all faculty members beginning with 'P' and ending with letter 'A'.
SELECT FROM WHERE FName Faculty
SUBSTR(FName,1,1) LIKE 'P' AND SUBSTR(FName,-2,1) LIKE 'A';

BN Shankar Gowda, BIT

65

More Examples
Book Dealer Database
AUTHOR (Authorid : Int, Name : String, City : String, Country : String) PUBLISHER (Publisherid : Int, Name : String, City : String, Country : String) CATALOG (Bookid : Int, Title : String, Authorid : Int, Publisherid : Int, Categoryid : Int, ear : Int, Price : Int) CATEGORY (Categorid : Int, Description : String) ORDER_DETAILS (OrderNo : Int, Bookid : Int, Quantity : Int)

BN Shankar Gowda, BIT

66

Queries
Give the details of the authors who have 2 or more books in the catalog and the price of the books is greater than the average price of the books in the catalog and the year of publication is after 2000. Query
select C.Authorid, A.AName from Catalog C, Author A where A.Authorid = C.Authorid and C.Year > 2000 and C.Price > (Select Avg(Price) from Catalog) group by C.Authorid, A.AName having count(C.Authorid) >= 2;

BN Shankar Gowda, BIT

67

Find the number of the book which has maximum sales.

Create View SalesDetails as ( Select OD.Bookid as Book#, C.Price as Cost, Sum(OD.Quantity) as Qty, Sum(OD.Quantity * C.Price) as Sales from Order_Details OD, Catalog C, Author A where OD.Bookid = C.Bookid and C.Authorid = A.Authorid group by OD.Bookid, C.Price); select A.Authorid, A.AName,S.book#, S.Sales from Author A, catalog C, Salesdetails S where A.Authorid = C.Authorid and S.Book# = C.Bookid and sales = (select Max(Sales) from salesdetails);
BN Shankar Gowda, BIT
68

Student Enrollment Database

STUDENT (RegNo : String, Name : String, Major : String, BDate : date) COURSE (Course# : Int, CName : String, Dept : String) ENROLL (RegNo : String, Course# : Int, Sem : Int, Marks : Int) BOOK_ADOPTION (Course# : Int, Sem : Int, ISBN : Int) TEXT (ISBN : Int, BookTitle : String, Publisher : String, Author : String)

BN Shankar Gowda, BIT

69

Produce a list of text books (include Course#, ISBN, BookTitle) in the alphabetical order for courses offered by the 'CS' department that use more than two books Select C.Course#, T.ISBN, T.BookTitle from Course C,Book_Adoption BA, Text T where C.Course# = BA.Course# and BA.ISBN = T.ISBN and C.Dept = 'CSE' group by C.Course#, T.ISBN, T.BookTitle;

BN Shankar Gowda, BIT

70

End of

Chapter 5

BN Shankar Gowda, BIT

71