141405-DATABASE MANAGEMENT SYSTEMS
DATABASE MANAGEMENT SYSTEMS
LAB MANUAL
Relational Database Concepts
• Dr.E.F. Codd proposed the relational model for database systems in the year 1970.
• It is the basis for RDBMS.
• The Relational model consists of the following:
- Collection of objects or relations
- Set of operators to act on the relations
- Data integrity for accuracy and consistency
Data Models
Models are cornerstone of design. System designers develop models ideas and improve the understanding the database design do the following.
Purpose of Models
Models help communicate the concepts in peoples mind. They can be used to
• Communicate
• Categorize
• Describe
• Specify
• Investigate
• Evolve
• Analyze
• Imitate
ER Model
An Entity Relationship model is an illustration of various entities in a business and the relationships between them. An ER model is derived from business specifications and built during the analysis phase of the development life cycle.ER model separate the information required by a business from the activities performed within a business. Although business can change their activities, the type of information tends to remain constant.
Benefits of ER Modeling
• Documents information for the organization in a clear, precise format.
• Provides a clear picture of the scope of the information requirement.
• Provides an easily understood pictorial map for the database design.
• Offers an effective framework for integrating multiple applications.
E-R diagram for a university
[pic]
Definition of a Relational Database
A relational database is a collection of relations or two-dimensional tables.
Terminologies Used in a Relational Database
1. A single ROW or table representing all data required for a particular employee. Each row should be identified by a primary key which allows no duplicate rows.
2. A COLUMN or attribute containing the employee number which identifies a unique employee. Here Employee number is designated as a primary key ,must contain a value and must be unique.
3. A column may contain foreign key. Here Dept_ID is a foreign key in employee table and it is a primary key in Department table.
4. A Field can be found at the intersection of a row and column. There can be only one value in it. Also it may have no value. This is called a null value.
|EMP ID |FIRST NAME |LAST NAME |EMAIL |
|100 |King |Steven |Sking |
|101 |John |Smith |Jsmith |
|102 |Neena |Bai |Neenba |
|103 |Eex |De Haan |Ldehaan |
Relational Database Properties
A relational database :
• Can be accessed and modified by executing structured query language (SQL) statements.
• Contains a collection of tables with no physical pointers.
• Uses a set of operators
Communicating with a RDBMS using SQL
SQL statement is entered
Statement is sent to
Oracle server
|DEPT NAME |
|Admin |
|IT |
Relational Database Management Systems
RDBMS refers to a relational database plus supporting software for managing users and processing SQL queries, performing backups/restores and associated tasks.
(Relational Database Management System) Software for storing data using SQL (structured query language). A relational database uses SQL to store data in a series of tables that not only record existing relationships between data items, but which also permit the data to be joined in new relationships. SQL (pronounced 'sequel') is based on a system of algebra developed by E F Codd, an IBM scientist who first defined the relational model in 1970. Relational databases are optimized for storing transactional data, and the majority of modern business software applications therefore use an RDBMS as their data store. The leading RDBMS vendors are Oracle, IBM and Microsoft.
The first commercial RDBMS was the Multics Relational Data Store, first sold in 1978.
INGRES, Oracle, Sybase, Inc., Microsoft Access, and Microsoft SQL Server are well-known database products and companies.Others include PostgreSQL, SQL/DS, and RDB.
A relational database management system (RDBMS) is a program that lets you create, update, and administer a relational database. Most commercial RDBMS's use the Structured Query Language (SQL) to access the database, although SQL was invented after the development of the relational model and is not necessary for its use.
The leading RDBMS products are Oracle, IBM's DB2 and Microsoft's SQL Server. Despite repeated challenges by competing technologies, as well as the claim by some experts that no current RDBMS has fully implemented relational principles, the majority of new corporate databases are still being created and managed with an RDBMS.
SQL Statements
1. Data Retrieval(DR)
2. Data Manipulation Language(DML)
3. Data Definition Language(DDL)
4. Data Control Language(DCL)
5. Transaction Control Language(TCL)
|TYPE |STATEMENT |DESCRIPTION |
|DR |SELECT |Retrieves the data from the database |
|DML |1.INSERT |Enter new rows, changes existing rows, removes unwanted |
| |2.UPDATE |rows from tables in the database respectively. |
| |3.DELETE | |
| |4.MERGE | |
|DDL |1.CREATE |Sets up, changes and removes data structures from tables. |
| |2.ALTER | |
| |3.DROP | |
| |4.RENAME | |
| |5.TRUNCATE | |
|DCL |MIT |Manages the changes made by DML statements. Changes to the|
| |2.ROLLBACK |data can be grouped together into logical transactions. |
| |3.SAVEPOINT | |
|TCL |1.GRANT |Gives or removes access rights to both the oracle database|
| |2.RREVOKE |and the structures within it. |
DATA TYPES
1. Character Data types:
▪ Char – fixed length character string that can varies between 1-2000 bytes
▪ Varchar / Varchar2 – variable length character string, size ranges from 1-4000 bytes.it saves the disk space(only length of the entered value will be assigned as the size of column)
▪ Long - variable length character string, maximum size is 2 GB
2. Number Data types : Can store +ve,-ve,zero,fixed point, floating point with 38 precission.
▪ Number – {p=38,s=0}
▪ Number(p) - fixed point
▪ Number(p,s) –floating point (p=1 to 38,s= -84 to 127)
3. Date Time Data type: used to store date and time in the table.
▪ DB uses its own format of storing in fixed length of 7 bytes for century, date, month, year, hour, minutes, and seconds.
▪ Default data type is “dd-mon-yy”
▪ New Date time data types have been introduced. They are
TIMESTAMP-Date with fractional seconds
INTERVAL YEAR TO MONTH-stored as an interval of years and months
INTERVAL DAY TO SECOND-stored as o interval of days to hour’s minutes and seconds
4. Raw Data type: used to store byte oriented data like binary data and byte string.
5. Other :
▪ CLOB – stores character object with single byte character.
▪ BLOB – stores large binary objects such as graphics, video, sounds.
▪ BFILE – stores file pointers to the LOB’s.
EXERCISE-1
Creating and Managing Tables
OBJECTIVE
After the completion of this exercise, students should be able to do the following:
➢ Create tables
➢ Describing the data types that can be used when specifying column definition
➢ Alter table definitions
➢ Drop, rename, and truncate tables
NAMING RULES
Table names and column names:
✓ Must begin with a letter
✓ Must be 1-30 characters long
✓ Must contain only A-Z, a-z, 0-9, _, $, and #
✓ Must not duplicate the name of another object owned by the same user
✓ Must not be an oracle server reserve words
✓ 2 different tables should not have same name.
✓ Should specify a unique column name.
✓ Should specify proper data type along with width
✓ Can include “not null” condition when needed. By default it is ‘null’.
The CREATE TABLE Statement
Table: Basic unit of storage; composed of rows and columns
Syntax: 1 Create table table_name (column_name1 data_ type (size) column_name2 data_ type (size)….);
Syntax: 2 Create table table_name (column_name1 data_ type (size) constraints, column_name2 data_ type constraints …);
Example:
Create table employlees ( employee_id number(6), first_name varchar2(20), ..job_id varchar2(10), CONSTRAINT emp_emp_id_pk PRIMARY KEY (employlee_id));
Tables Used in this course
Creating a table by using a Sub query
SYNTAX
// CREATE TABLE table_name(column_name type(size)…);
Create table table_name as select column_name1,column_name2,……colmn_namen from
table_name where predicate;
AS Subquery
Subquery is the select statement that defines the set of rows to be inserted into the new table.
Example
Create table dept80 as select employee_id, last_name, salary*12 Annsal, hire_date
from employees where dept_id=80;
The ALTER TABLE Statement
The ALTER statement is used to
• Add a new column
• Modify an existing column
• Define a default value to the new column
• Drop a column
• To include or drop integrity constraint.
SYNTAX
ALTER TABLE table_name ADD /MODIFY(Column_name type(size));
ALTER TABLE table_name DROP COLUMN (Column_nname);
ALTER TABLE ADD CONSTRAINT Constraint_name PRIMARY KEY (Colum_Name);
Example:
Alter table dept80 add (jod_id varchar2(9));
Alter table dept80 modify (last_name varchar2(30));
Alter table dept80 drop column job_id;
NOTE: Once the column is dropped it cannot be recovered.
The SET UNUSED Option
To set and drop one or more columns as unused
SYNTAX
ALTER TABLE table_name SET UNUSED COLUMN (column_name);
ALTER TABLE table_name DROP UNUSED COLUMNS;
Example:
Alter table dept80 set unused (last_name);
Alter table dept80 drop unused columns;
DROPPING A TABLE
• All data and structure in the table is deleted.
• Any pending transactions are committed.
• All indexes are dropped.
• Cannot roll back the drop table statement.
Example:
Drop table dept80;
RENAMING A TABLE
To rename a table or view.
Syntax
RENAME old_name to new_name
Example:
Rename dept to detail_dept;
TRUNCATING A TABLE
Removes all rows from the table.
Releases the storage space used by that table.
Syntax
TRUNCATE TABLE table_name;
Example:
TRUNCATE TABLE copy_emp;
Find the Solution for the following:
Create the following tables with the given structure.
EMPLOYEES TABLE
|NAME |NULL? |TYPE |
|Employee_id |Not null |Number(6) |
|First_Name | |Varchar(20) |
|Last_Name |Not null |Varchar(25) |
|Email |Not null |Varchar(25) |
|Phone_Number | |Varchar(20) |
|Hire_date |Not null |Date |
|Job_id |Not null |Varchar(10) |
|Salary | |Number(8,2) |
|Commission_pct | |Number(2,2) |
|Manager_id | |Number(6) |
|Department_id | |Number(4) |
DEPARTMENT TABLE
|NAME |NULL? |TYPE |
|Dept_id |Not null |Number(6) |
|Dept_name |Not null |Varchar(20) |
|Manager_id | |Number(6) |
|Location_id | |Number(4) |
JOB_GRADE TABLE
|NAME |NULL? |TYPE |
|Grade_level | |Varchar(2) |
|Lowest_sal | |Number |
|Highest_sal | |Number |
LOCATION TABLE
|NAME |NULL? |TYPE |
|Location_id |Not null |Number(4) |
|St_addr | |Varchar(40) |
|Postal_code | |Varchar(12) |
|City |Not null |Varchar(30) |
|State_province | |Varchar(25) |
|Country_id | |Char(2) |
1. Create the DEPT table based on the following table instance chart. Confirm that the table is created.
|Column name |ID |NAME |
|Key Type | | |
|Nulls/Unique | | |
|FK table | | |
|FK column | | |
|Data Type |Number |Varchar2 |
|Length |7 |25 |
2. Populate the DEPT table with data from the DEPARTMENT table.Include only the columns that are needed.
3. Create the EMP table based on the following instance chart. Confirm that the table is created.
|Column name |ID |LAST_NAME |FIRST_NAME |DEPT_ID |
|Key Type | | | | |
|Nulls/Unique | | | | |
|FK table | | | | |
|FK column | | | | |
|Data Type |Number |Varchar2 |Varchar2 |Number |
|Length |7 |25 |25 |7 |
4. Modify the EMP table to allow for longer employee last names. Confirm the modification.(Hint: Increase the size to 50)
5. Create the EMPLOYEES2 table based on the structure of EMPLOYEES table. Include Only the Employee_id, First_name, Last_name, Salary and Dept_id coloumns. Name the columns Id, First_name, Last_name, salary and Dept_id respectively.
6. Drop the EMP table.
7. Rename the EMPLOYEES2 table as EMP.
8. Add a comment on DEPT and EMP tables. Confirm the modification by describing the table.
9. Drop the First_name column frim the EMP table and confirm it.
10. In the DEPT table mark the column Dept_id as UNUSED and confirm it.
11. Drop all the UNUSED columns from the EMP table and Confirm the modification.
EXERCISE-2
MANIPULATING DATA
OBJECTIVE
After, the completion of this exercise the students will be able to do the following
• Describe each DML statement
• Insert rows into tables
• Update rows into table
• Delete rows from table
• Control Transactions
A DML statement is executed when you:
• Add new rows to a table
• Modify existing rows
• Removing existing rows
A transaction consists of a collection of DML statements that form a logical unit of work.
To Add a New Row
INSERT Statement
Syntax
INSERT INTO table_name VALUES (list of values as in the order when created the table);
Example:
INSERT INTO department (70, ‘Public relations’, 100 1700);
Inserting rows with null values
Implicit Method: (Omit the column)
INSERT INTO department VALUES (30,’purchasing’);
Explicit Method: (Specify NULL keyword)
INSERT INTO department VALUES (100,’finance’, NULL, NULL);
Inserting Special Values
Example:
Using SYSDATE
INSERT INTO employees VALUES (113,’louis’, ‘popp’, ‘lpopp’,’5151244567’,SYSDATE, ‘ac_account’, 6900, NULL, 205, 100);
Inserting Specific Date Values
Example:
INSERT INTO employees VALUES ( 114,’den’, ‘raphealy’, ‘drapheal’, ‘5151274561’, TO_DATE(‘feb 3,1999’,’mon, dd ,yyyy’), ‘ac_account’, 11000,100,30);
To Insert Multiple Rows
& is the placeholder for the variable value
Example:
INSERT INTO department VALUES (&dept_id, &dept_name, &location);
Copying Rows from another table
➢ Using Subquery
Example:
INSER INTO sales_reps(id, name, salary, commission_pct)
SELECt employee_id, Last_name, salary, commission_pct
FROM employees
WHERE jod_id LIKE ‘%REP’);
CHANGING DATA IN A TABLE
UPDATE Statement
Syntax1: ( to update specific rows)
UPDATE table_name SET column=value WHERE condition;
Syntax 2: (To updae all rows)
UPDATE table_name SET column=value;
Updating columns with a subquery
UPDATE employees
SET job_id= (SELECT job_id
FROM employees
WHERE employee_id=205)
WHERE employee_id=114;
REMOVING A ROW FROM A TABLE
DELETE STATEMENT
Syntax
DELETE FROM table_name WHERE conditions;
Example:
DELETE FROM department WHERE dept_name=’finance’’;
Find the Solution for the following:
1. Create MY_EMPLOYEE table with the following structure
|NAME |NULL? |TYPE |
|ID |Not null |Number(4) |
|Last_name | |Varchar(25) |
|First_name | |Varchar(25) |
|Userid | |Varchar(25) |
|Salary | |Number(9,2) |
2. Add the first and second rows data to MY_EMPLOYEE table from the following sample data.
|ID |Last_name |First_name |Userid |salary |
|1 |Patel |Ralph |rpatel |895 |
|2 |Dancs |Betty |bdancs |860 |
|3 |Biri |Ben |bbiri |1100 |
|4 |Newman |Chad |Cnewman |750 |
|5 |Ropebur |Audrey |aropebur |1550 |
3. Display the table with values.
4. Populate the next two rows of data from the sample data. Concatenate the first letter of the first_name with the first seven characters of the last_name to produce Userid.
Soln: insert into my_employee values(&id,’last_name’,’&first_name’,lower(substr(‘&first_name,1,1)||substr(‘&last_name’,1,7)),&salary);
5. Make the data additions permanent.
Commit;
6. Change the last name of employee 3 to Drexler.
Update my_employee set last_name=’Drexler’ where id=3;
7. Change the salary to 1000 for all the employees with a salary less than 900.
Update my_employee set salary=1000 where salary 0));
Entity Integrity
Maintains uniqueness in a record. An entity represents a table and each row of a table represents an instance of that entity. To identify each row in a table uniquely we need to use this constraint. There are 2 entity constraints:
a) Unique key constraint
It is used to ensure that information in the column for each record is unique, as with telephone or driver’s license numbers. It prevents the duplication of value with rows of a specified column in a set of column. A column defined with the constraint can allow null value.
If unique key constraint is defined in more than one column i.e., combination of column cannot be specified. Maximum combination of columns that a composite unique key can contain is 16.
Example:
CREATE TABLE employees (employee_id number(6), last_name varchar2(25) NOT NULL,email varchar2(25), salary number(8,2), commission_pct number(2,2), hire_date date constraint emp_hire_date_nn NOT NULL’ COSTRAINT emp_email_uk UNIQUE(email));
PRIMARY KEY CONSTRAINT
A primary key avoids duplication of rows and does not allow null values. Can be defined on one or more columns in a table and is used to uniquely identify each row in a table. These values should never be changed and should never be null.
A table should have only one primary key. If a primary key constraint is assigned to more than one column or combination of column is said to be composite primary key, which can contain 16 columns
Example:
CREATE TABLE employees (employee_id number(6) , last_name varchar2(25) NOT NULL,email varchar2(25), salary number(8,2), commission_pct number(2,2), hire_date date constraint emp_hire_date_nn NOT NULL, Constraint emp_id pk PRIMARY KEY (employee_id),CONSTRAINT emp_email_uk UNIQUE(email));
c) Referential Integrity
It enforces relationship between tables. To establish parent-child relationship between 2 tables having a common column definition, we make use of this constraint. To implement this, we should define the column in the parent table as primary key and same column in the child table as foreign key referring to the corresponding parent entry.
Foreign key
A column or combination of column included in the definition of referential integrity, which would refer to a referenced key.
Referenced key
It is a unique or primary key upon which is defined on a column belonging to the parent table.
Keywords:
FOREIGN KEY: Defines the column in the child table at the table level constraint.
REFERENCES: Identifies the table and column in the parent table.
ON DELETE CASCADE: Deletes the dependent rows in the child table when a row in the parent table is deleted.
ON DELETE SET NULL: converts dependent foreign key values to null when the parent value is removed.
CREATE TABLE employees (employee_id number(6) , last_name varchar2(25) NOT NULL,email varchar2(25), salary number(8,2), commission_pct number(2,2), hire_date date constraint emp_hire_date_nn NOT NULL, Constraint emp_id pk PRIMARY KEY (employee_id),CONSTRAINT emp_email_uk UNIQUE(email),CONSTRAINT emp_dept_fk FOREIGN KEY (department_id) references deparments(dept_id));
ADDING A CONSTRAINT
Use the ALTER to
• Add or Drop a constraint, but not modify the structure
• Enable or Disable the constraints
• Add a not null constraint by using the Modify clause
Syntax
ALTER TABLE table name ADD CONSTRAINT Cons_name type(column name);
Example:
ALTER TABLE employees ADD CONSTRAINT emp_manager_fk FOREIGN KEY (manager_id) REFERENCES employees (employee_id);
DROPPING A CONSTRAINT
Example:
ALTER TABLE employees DROP CONSTRAINT emp_manager_fk;
CASCADE IN DROP
• The CASCADE option of the DROP clause causes any dependent constraints also to be dropped.
Syntax
ALTER TABLE departments DROP PRIMARY KEY|UNIQUE (column)| CONSTRAINT constraint _name CASCADE;
DISABLING CONSTRAINTS
• Execute the DISABLE clause of the ALTER TABLE statement to deactivate an integrity constraint
• Apply the CASCADE option to disable dependent integrity constraints.
Example
ALTER TABLE employees DISABLE CONSTRAINT emp_emp_id_pk CASCADE;
ENABLING CONSTRAINTS
• Activate an integrity constraint currently disabled in the table definition by using the ENABLE clause.
Example
ALTER TABLE employees ENABLE CONSTRAINT emp_emp_id_pk CASCADE;
CASCADING CONSTRAINTS
The CASCADE CONSTRAINTS clause is used along with the DROP column clause.
It drops all referential integrity constraints that refer to the primary and unique keys defined on the dropped Columns.
This clause also drops all multicolumn constraints defined on the dropped column.
Example:
Assume table TEST1 with the following structure
CREATE TABLE test1 ( pk number PRIMARY KEY, fk number, col1 number,col2 number, CONTRAINT fk_constraint FOREIGN KEY(fk) references test1, CONSTRAINT ck1 CHECK (pk>0 and col1>0), CONSTRAINT ck2 CHECK (col2>0));
An error is returned for the following statements
ALTER TABLE test1 DROP (pk);
ALTER TABLE test1 DROP (col1);
The above statement can be written with CASCADE CONSTRAINT
ALTER TABLE test 1 DROP(pk) CASCADE CONSTRAINTS;
(OR)
ALTER TABLE test 1 DROP(pk, fk, col1) CASCADE CONSTRAINTS;
VIEWING CONSTRAINTS
Query the USER_CONSTRAINTS table to view all the constraints definition and names.
Example:
SELECT constraint_name, constraint_type, search_condition FROM user_constraints
WHERE table_name=’employees’;
Viewing the columns associated with constraints
SELECT constraint_name, constraint_type, FROM user_cons_columns
WHERE table_name=’employees’;
Find the Solution for the following:
1. Add a table-level PRIMARY KEY constraint to the EMP table on the ID column.The constraint should be named at creation. Name the constraint my_emp_id_pk.
Alter table emp add constraint my_emp_id_pk primary key(id);
2. Create a PRIMAY KEY constraint to the DEPT table using the ID colum. The constraint should be named at creation. Name the constraint my_dept_id_pk.
Alter table dept add constraint my_dept_id_pk primary key(id);
3. Add a column DEPT_ID to the EMP table. Add a foreign key reference on the EMP table that ensures that the employee is not assigned to nonexistent deparment. Name the constraint my_emp_dept_id_fk.
Alter table emp add(dept_id number(7));
Alter table emp add constraint my_emp_dept_id_fk foreign key(dept_id) references dept(id);
4. Confirm that the constraints were added by querying the USER_CONSTRAINTS view.
Select constraint_name, constraint_type from user_constraints where table_name in(‘emp’,’dept’);
5. Modify the EMP table. Add a COMMISSION column of NUMBER data type, precision 2, scale 2. Add a constraint to the commission column that ensures that a commission value is greater than zero.
Alter table emp add commission float constraints ck check(commission>=0);
EXERCISE-4
Writing Basic SQL SELECT Statements
OBJECTIVES
After the completion of this exercise, the students will be able to do the following:
• List the capabilities of SQL SELECT Statement
• Execute a basic SELECT statement
Capabilities of SQL SELECT statement
A SELECT statement retrieves information from the database. Using a select statement we can perform
✓ Projection: To choose the columns in a table
✓ Selection: To choose the rows in a table
✓ Joining: To bring together the data that is stored in different tables
Basic SELECT Statement
Syntax
SELECT *|DISTINCT Column_ name| alias Clause
Statement
FROM table_name
NOTE:
DISTINCT—Suppress the duplicates.
Alias—gives selected columns different headings.
Example: 1
SELECT * FROM departments;
Example: 2
SELECT location_id,department_id FROM departments;
Writing SQL Statements
• SQL statements are not case sensitive
• SQL statements can be on one or more lines.
• Keywords cannot be abbreviated or split across lines
• Clauses are usually placed on separate lines
• Indents are sued to enhance readability
Using Arithmetic Expressions
Basic Arithmetic operators like *, /, +, -can be used
Example:1
SELECT last_name, salary, salary+300 FROM employees;
Example:2
SELECT last_name, salary, 12*salary+100 FROM employees;
The statement is not same as
SELECT last_name, salary, 12*(salary+100) FROM employees;
Example:3
SELECT last_name, job_id, salary, commission_pct FROM employees;
Example:4
SELECT last_name, job_id, salary, 12*salary*commission_pct FROM employees;
Using Column Alias
• To rename a column heading with or without AS keyword.
Example:1
SELECT last_name AS Name
FROM employees;
Example: 2
SELECT last_name “Name” salary*12 “Annual Salary “
FROM employees;
Concatenation Operator
• Concatenates columns or character strings to other columns
• Represented by two vertical bars (||)
• Creates a resultant column that is a character expression
Example:
SELECT last_name||job_id AS “EMPLOYEES JOB” FROM employees;
Using Literal Character String
• A literal is a character, a number, or a date included in the SELECT list.
• Date and character literal values must be enclosed within single quotation marks.
Example:
SELECT last_name||’is a’||job_id AS “EMPLOYEES JOB” FROM employees;
Eliminating Duplicate Rows
• Using DISTINCT keyword.
Example:
SELECT DISTINCT deparment_id FROM employees;
Displaying Table Structure
• Using DESC keyword.
Syntax
DESC table_name;
Example:
DESC employees;
Find the Solution for the following:
True OR False
1. The following statement executes successfully.
SELECT last_name, job_id, salry AS sal FROM employees;
2. SELECT * FROM job_grades;
Identify the Errors
3. SELECT employee_id, last_name
sal*12 ANNUAL SALARY
FROM employees;
Queries
4. Show the structure of departments the table. Select all the data from it.
Desc departments;
Select * from departments;
5. Show the structure of the employees the table. Create a query to display the last name, job code, hire date, and employee number for each employee, with employee number appearing first.
Desc employees;
6. Provide an alias STARTDATE for the hire date.
Select employee_id,last_name,job_id,hire_date start_date from employees;
7. Create a query to display unique job codes from the employee table.
Select distinct job_id from employees;
8. Display the last name concatenated with the job ID , separated by a comma and space, and name the column EMPLOYEE and TITLE.
Select last_name || ‘,’||job_id “employee and title” from employees;
9. Create a query to display all the data from the employees table. Separate each column by a comma. Name the column THE_OUTPUT.
Select employee_id || ‘,’ || first_name|| ‘,’ || last_name|| ‘,’ ||email|| ‘,’ ||phone_no|| ‘,’ ||job_id|| ‘,’ ||manger_id|| ‘,’ ||hire_date|| ‘,’ ||salary|| ‘,’ ||commission_pct|| ‘,’ ||department_id THE_OUTPUT;
EXERCISE-5
Restricting and Sorting data
After the completion of this exercise, the students will be able to do the following:
• Limit the rows retrieved by the queries
• Sort the rows retrieved by the queries
•
Limiting the Rows selected
• Using WHERE clause
• Alias cannot used in WHERE clause
Syntax
SELECT----------
FROM----------
WHERE condition;
Example:
SELECT employee_id,last_name, job_id, deparment_id FROM employees WHERE department_id=90;
Character strings and Dates
Character strings and date values are enclosed in single quotation marks.
Character values are case sensitive and date values are format sensitive.
Example:
SELECT employee_id,last_name, job_id, deparment_id FROM employees
WHERE last_name=’WHALEN”;
Comparison Conditions
All relational operators can be used. (=, >, >=, 15000;
Example:2
SELECT employee_id, last_name, salary , job_id
FROM employees
WHERE (job_id =’sa_rep’
OR job_id=’ad_pres’)
AND salary>15000;
Sorting the rows
Using ORDER BY Clause
ASC-Ascending Order,Default
DESC-Descending order
Example:1
SELECT last_name, salary , job_id,department_id,hire_date
FROM employees
ORDER BY hire_date;
Example:2
SELECT last_name, salary , job_id,department_id,hire_date
FROM employees
ORDER BY hire_date DESC;
Example:3
Sorting by column alias
SELECT last_name, salary*12 annsal , job_id,department_id,hire_date
FROM employees
ORDER BY annsal;
Example:4
Sorting by Multiple columns
SELECT last_name, salary , job_id,department_id,hire_date
FROM employees
ORDER BY department_id, salary DESC;
Find the Solution for the following:
1. Create a query to display the last name and salary of employees earning more than 12000.
2. Create a query to display the employee last name and department number for employee number 176.
3. Create a query to display the last name and salary of employees whose salary is not in the range of 5000 and 12000. (hints: not between )
4. Display the employee last name, job ID, and start date of employees hired between February 20,1998 and May 1,1998.order the query in ascending order by start date.
(hints: between)
5. Display the last name and department number of all employees in departments 20 and 50 in alphabetical order by name.
(hints: in, orderby)
6. Display the last name and salary of all employees who earn between 5000 and 12000 and are in departments 20 and 50 in alphabetical order by name. Label the columns EMPLOYEE, MONTHLY SALARY respectively.
(hints: between, in)
7. Display the last name and hire date of every employee who was hired in 1994.
(hints: like)
8. Display the last name and job title of all employees who do not have a manager.
(hints: is null
9. Display the last name, salary, and commission for all employees who earn commissions. Sort data in descending order of salary and commissions.
(hints: is not nul,orderby)
10. Display the last name of all employees where the third letter of the name is a.
(hints:like)
11. Display the last name of all employees who have an a and an e in their last name.
(hints: like)
12. Display the last name and job and salary for all employees whose job is sales representative or stock clerk and whose salary is not equal to 2500 ,3500 or 7000.
(hints:in,not in)
13. Display the last name, salary, and commission for all employees whose commission amount is 20%.
(hints:use predicate logic)
EXERCISE-6
Single Row Functions
Objective
After the completion of this exercise, the students will be able to do the following:
• Describe various types of functions available in SQL.
• Use character, number and date functions in SELECT statement.
• Describe the use of conversion functions.
Single row functions:
Manipulate data items.
Accept arguments and return one value.
Act on each row returned.
Return one result per row.
May modify the data type.
Can be nested.
Accept arguments which can be a column or an expression
Syntax
Function_name(arg1,…argn)
An argument can be one of the following
✓ User-supplied constant
✓ Variable value
✓ Column name
✓ Expression
• Character Functions: Accept character input and can return both character and number values.
• Number functions: Accept numeric input and return numeric values.
• Date Functions: Operate on values of the DATE data type.
• Conversion Functions: Convert a value from one type to another.
Character Functions
Character Functions
Case-manipulation functions Character-manipulation functions
1. Lower 1. Concat
2. Upper 2. Substr
3. Initcap 3. Length
4. Instr
5. Lpad/Rpad
6. Trim
7. Repalce
|Function |Purpose |
|lower(column/expr) |Converts alpha character values to lowercase |
|upper(column/expr) |Converts alpha character values to uppercase |
|initcap(column/expr) |Converts alpha character values the to uppercase for the first letter of each |
| |word, all other letters in lowercase |
|concat(column1/expr1, column2/expr2) |Concatenates the first character to the second character |
|substr(column/expr,m,n) |Returns specified characters from character value starting at character position|
| |m, n characters long |
|length(column/expr) |Returns the number of characters in the expression |
|instr(column/expr,’string’,m,n) |Returns the numeric position of a named string |
|lpad(column/expr, n,’string’) |Pads the character value right-justified to a total width of n character |
| |positions |
|rpad(column/expr,’string’,m,n) |Pads the character value left-justified to a total width of n character |
| |positions |
|trim(leading/trailing/both, trim_character FROM trim_source) |Enables you to trim heading or string. trailing or both from a character |
|replace(text, search_string, replacement_string) | |
Example:
lower(‘SQL Course’)(sql course
upper(‘SQL Course’)(SQL COURSE
initcap(‘SQL Course’)(Sql Course
SELECT ‘The job id for’|| upper(last_name||’is’||lower(job_id) AS “EMPLOYEE DETAILS” FROM employees;
SELECT employee_id, last_name, department_id
FROM employees
WHERE LOWER(last_name)=’higgins’;
|Function |Result |
|CONCAT(‘hello’, ‘world’) |helloworld |
|Substr(‘helloworld’,1,5) |hello |
|Length(‘helloworld’) |10 |
|Instr(‘helloworld’,’w’) |6 |
|Lpad(salary,10,’*’) |*****24000 |
|Rpad(salary,10,’*’) |24000***** |
|Trim(‘h’ FROM ‘helloworld’) |elloworld |
|Command |Query |Output |
|initcap(char); |select initcap(“hello”) from dual; |Hello |
|lower (char); |select lower (‘HELLO’) from dual; |hello |
|upper (char); |select upper (‘hello’) from dual; |HELLO |
|ltrim (char,[set]); |select ltrim (‘cseit’, ‘cse’) from dual; |it |
|rtrim (char,[set]); |select rtrim (‘cseit’, ‘it’) from dual; |cse |
|replace (char,search string, replace |select replace (‘jack and jue’, ‘j’, ‘bl’) from dual; |black and blue |
|string); | | |
|substr (char,m,n); |select substr (‘information’, 3, 4) from dual; |form |
Example:
SELECT employee_id, CONCAT (first_name,last_name) NAME , job_id,LENGTH(last_name), INSTR(last_name,’a’) “contains’a’?”
FROM employees WHERE SUBSTR(job_id,4)=’ERP’;
NUMBER FUNCTIONS
|Function |Purpose |
|round(column/expr, n) |Rounds the value to specified decimal |
|trunc(column/expr,n) |Truncates value to specified decimal |
|mod(m,n) |Returns remainder of division |
Example
|Function |Result |
|round(45.926,2) |45.93 |
|trunc(45.926,2) |45.92 |
|mod(1600,300) |100 |
SELECT ROUND(45.923,2), ROUND(45.923,0), ROUND(45.923,-1) FROM dual;
NOTE: Dual is a dummy table you can use to view results from functions and calculations.
SELECT TRUNC(45.923,2), TRUNC(45.923), TRUNC(45.923,-2) FROM dual;
SELECT last_name,salary,MOD(salary,5000) FROM employees WHERE job_id=’sa_rep’;
Working with Dates
The Oracle database stores dates in an internal numeric format: century, year, month, day, hours, minutes, and seconds.
• The default date display format is DD-MON-RR.
– Enables you to store 21st-century dates in the 20th century by specifying only the last
two digits of the year
– Enables you to store 20th-century dates in the 21st century in the same way
Example
SELECT last_name, hire_date FROM employees WHERE hire_date < '01-FEB-88;
Working with Dates
SYSDATE is a function that returns:
• Date
• Time
Example
Display the current date using the DUAL table.
SELECT SYSDATE FROM DUAL;
Arithmetic with Dates
• Add or subtract a number to or from a date for a resultant date value.
• Subtract two dates to find the number of days between those dates.
• Add hours to a date by dividing the number of hours by 24.
Arithmetic with Dates
Because the database stores dates as numbers, you can perform calculations using arithmetic
Operators such as addition and subtraction. You can add and subtract number constants as well as dates.
You can perform the following operations:
Operation Result Description
date + number Date Adds a number of days to a date
date – number Date Subtracts a number of days from a date
date – date Number of days Subtracts one date from another
date + number/24 Date Adds a number of hours to a date
Example
SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKS
FROM employees
WHERE department_id = 90;
Date Functions
[pic]
Date Functions
Date functions operate on Oracle dates. All date functions return a value of DATE data type
except MONTHS_BETWEEN, which returns a numeric value.
• MONTHS_BETWEEN(date1, date2)::: Finds the number of months between date1 and date2. The result can be positive or negative. If date1 is later than date2, the result is positive; if date1 is earlier than date2, the result is negative. The noninteger part of the result represents a portion of the month.
• ADD_MONTHS(date, n)::: Adds n number of calendar months to date. The value of n must be an integer and can be negative.
• NEXT_DAY(date, 'char')::: Finds the date of the next specified day of the week ('char') following date. The value of char may be a number representing a day or a character string.
• LAST_DAY(date)::: Finds the date of the last day of the month that contains date
• ROUND(date[,'fmt'])::: Returns date rounded to the unit that is specified by the format model fmt. If the format model fmt is omitted, date is rounded to the nearest day.
• TRUNC(date[, 'fmt'])::: Returns date with the time portion of the day truncated to the unit that is specified by the format model fmt. If the format model fmt is omitted, date is truncated to the nearest day.
Using Date Functions
[pic]
Example
Display the employee number, hire date, number of months employed, sixmonth review date, first Friday after hire date, and last day of the hire month for all employees who have been employed for fewer than 70 months.
SELECT employee_id, hire_date,MONTHS_BETWEEN (SYSDATE, hire_date) TENURE,ADD_MONTHS (hire_date, 6) REVIEW,NEXT_DAY (hire_date, 'FRIDAY'), LAST_DAY(hire_date)
FROM employees
WHERE MONTHS_BETWEEN (SYSDATE, hire_date) < 70;
Conversion Functions
This covers the following topics:
• Writing a query that displays the current date
• Creating queries that require the use of numeric, character, and date functions
• Performing calculations of years and months of service for an employee
[pic]
Implicit Data Type Conversion
For assignments, the Oracle server can automatically convert the following:
[pic]
For example, the expression hire_date > '01-JAN-90' results in the implicit conversion from the string '01-JAN-90' to a date.
For expression evaluation, the Oracle Server can automatically convert the following:
[pic]
Explicit Data Type Conversion
[pic]
SQL provides three functions to convert a value from one data type to another:
Example:
Using the TO_CHAR Function with Dates
TO_CHAR(date, 'format_model')
The format model:
• Must be enclosed by single quotation marks
• Is case-sensitive
• Can include any valid date format element
• Has an fm element to remove padded blanks or suppress leading zeros
• Is separated from the date value by a comma
SELECT employee_id, TO_CHAR(hire_date, 'MM/YY') Month_Hired
FROM employees WHERE last_name = 'Higgins';
Elements of the Date Format Model
[pic]
Sample Format Elements of Valid Date [pic]
Date Format Elements: Time Formats
Use the formats that are listed in the following tables to display time information and literals
and to change numerals to spelled numbers.
[pic]
[pic]
Example
SELECT last_name,
TO_CHAR(hire_date, 'fmDD Month YYYY') AS HIREDATE
FROM employees;
Modify example to display the dates in a format that appears as “Seventeenth of June 1987 12:00:00 AM.”
SELECT last_name,
TO_CHAR(hire_date, 'fmDdspth "of" Month YYYY fmHH:MI:SS AM') HIREDATE
FROM employees;
Using the TO_CHAR Function with Numbers
TO_CHAR(number, 'format_model')
These are some of the format elements that you can use with the TO_CHAR function to display a number value as a character:
[pic]
Number Format Elements
If you are converting a number to the character data type, you can use the following format elements:
[pic]
SELECT TO_CHAR(salary, '$99,999.00') SALARY
FROM employees
WHERE last_name = 'Ernst';
Using the TO_NUMBER and TO_DATE Functions
• Convert a character string to a number format using the TO_NUMBER function:
TO_NUMBER(char[, 'format_model']
• Convert a character string to a date format using the TO_DATE function:
TO_DATE(char[, 'format_model']
• These functions have an fx modifier. This modifier specifies the exact matching for the character
argument and date format model of a TO_DATE function.
The fx modifier specifies exact matching for the character argument and date format model of a TO_DATE function:
• Punctuation and quoted text in the character argument must exactly match (except for case) the corresponding parts of the format model.
• The character argument cannot have extra blanks. Without fx, Oracle ignores extra blanks.
• Numeric data in the character argument must have the same number of digits as the corresponding element in the format model. Without fx, numbers in the character argument can omit leading zeros.
SELECT last_name, hire_date
FROM employees
WHERE hire_date = TO_DATE('May 24, 1999', 'fxMonth DD, YYYY');
Find the Solution for the following:
1.Write a query to display the current date. Label the column Date.
Select sysdate “date” from dual;
2.The HR department needs a report to display the employee number, last name, salary, and
increased by 15.5% (expressed as a whole number) for each employee. Label the column New Salary.
Select employee_id,last_name,salary,round(salary*1.15,0) “new salary” from employees;
3.Modify your query lab_03_02.sql to add a column that subtracts the old salary from the new salary.
Label the column Increase.
Select employee_id,last_name,salary,round(salary*1.15,0) “New salary”,round(salary*1.15,0)-salary “Increase” from employees;
4.Write a query that displays the last name (with the first letter uppercase and all other letters lowercase)
and the length of the last name for all employees whose name starts with the letters J, A, or M. Give
each column an appropriate label. Sort the results by the employees’ last names.
Select initcap(last_name) “Name”,length(last_name) “Length” from employees where last_name like ‘j%’ or last_name like ‘m%’ or last_name like ‘A%’ order by last_name;
Rewrite the query so that the user is prompted to enter a letter that starts the last name. For example, if
the user enters H when prompted for a letter, then the output should show all employees whose last
name starts with the letter H.
5.The HR department wants to find the length of employment for each employee. For each employee,
display the last name and calculate the number of months between today and the date on which the
employee was hired. Label the column MONTHS_WORKED. Order your results by the number of
months employed. Round the number of months up to the closest whole number.
Note: Your results will differ.
Select last_name,round(MONTHS_BETWEEN(SYSDATE,hire_date)) MONTHS_WORKED from employees order by MONTHS_BETWEEN(SYSDATE,hire_date);
6. Create a report that produces the following for each employee:
earns monthly but wants . Label the column Dream
Salaries.
Select last_name||’earns’||to_char(salary,’fm$99,999.00)||’monthly but wants’||to_char(salary*3,’fm$99,999.00)||’.’ “Dream salaries” from employees;
7. Create a query to display the last name and salary for all employees. Format the salary to be 15
characters long, left-padded with the $ symbol. Label the column SALARY.
Select last_name,lpad(salary,15,’$’) salary from employees;
8. Display each employee’s last name, hire date, and salary review date, which is the first Monday after
six months of service. Label the column REVIEW. Format the dates to appear in the format similar to
“Monday, the Thirty-First of July, 2000.”
Select last_name,hire_date,to_char(next_day(add_months(hire_date,6),’monday’),’fmDay,’the” Ddspth “of” Month,YYYY’) review from employees;
9. Display the last name, hire date, and day of the week on which the employee started. Label the column
DAY. Order the results by the day of the week, starting with Monday.
Select last_name,hire_date,to_char(hire_date,’DAY’) DAY from employees order by to_char(hire_date-1,’d’);
EXERCISE-7
Displaying data from multiple tables
Objective
After the completion of this exercise, the students will be able to do the following:
• Write SELECT statements to access data from more than one table using equality and nonequality joins
• View data that generally does not meet a join condition by using outer joins
• Join a table to itself by using a self join
Sometimes you need to use data from more than one table.
Cartesian Products
• A Cartesian product is formed when:
– A join condition is omitted
– A join condition is invalid
– All rows in the first table are joined to all rows in the second table
• To avoid a Cartesian product, always include a valid join condition in a WHERE clause.
A Cartesian product tends to generate a large number of rows, and the result is rarely useful. You should always include a valid join condition in a WHERE clause, unless you have a specific need to combine all rows from all tables.
Cartesian products are useful for some tests when you need to generate a large number of rows to
simulate a reasonable amount of data.
Example:
To displays employee last name and department name from the EMPLOYEES and DEPARTMENTS tables.
SELECT last_name, department_name dept_name
FROM employees, departments;
Types of Joins
• Equijoin
• Non-equijoin
• Outer join
• Self join
• Cross joins
• Natural joins
• Using clause
• Full or two sided outer joins
• Arbitrary join conditions for outer joins
Joining Tables Using Oracle Syntax
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2;
Write the join condition in the WHERE clause.
• Prefix the column name with the table name when the same column name appears in more than one
table.
Guidelines
• When writing a SELECT statement that joins tables, precede the column name with the table name for clarity and to enhance database access.
• If the same column name appears in more than one table, the column name must be prefixed with the table name.
• To join n tables together, you need a minimum of n-1 join conditions. For example, to join four tables, a minimum of three joins is required. This rule may not apply if your table has a concatenated primary key, in which case more than one column is required to uniquely identify each row
What is an Equijoin?
To determine an employee’s department name, you compare the value in the DEPARTMENT_ID
column in the EMPLOYEES table with the DEPARTMENT_ID values in the DEPARTMENTS table.
The relationship between the EMPLOYEES and DEPARTMENTS tables is an equijoin—that is, values
in the DEPARTMENT_ID column on both tables must be equal. Frequently, this type of join involves
primary and foreign key complements.
Note: Equijoins are also called simple joins or inner joins
SELECTemployees.employee_id,employees.last_name,employees.department_id, departments.department_id,departments.location_id
FROM employees, departments
WHERE employees.department_id = departments.department_id;
Additional Search Conditions
Using the AND Operator
Example:
To display employee Matos’department number and department name, you need an additional condition in the WHERE clause.
SELECT last_name, employees.department_id,
department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id AND last_name = ’Matos’;
Qualifying Ambiguous
Column Names
• Use table prefixes to qualify column names that are in multiple tables.
• Improve performance by using table prefixes.
• Distinguish columns that have identical names but reside in different tables by using column aliases.
Using Table Aliases
• Simplify queries by using table aliases.
• Improve performance by using table prefixes
Example:
SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM employees e , departments d
WHERE e.department_id = d.department_id;
Joining More than Two Tables
To join n tables together, you need a minimum of n-1 join conditions. For example, to join three
tables, a minimum of two joins is required.
Example:
To display the last name, the department name, and the city for each employee, you have to join the EMPLOYEES, DEPARTMENTS, and LOCATIONS tables.
SELECT e.last_name, d.department_name, l.city
FROM employees e, departments d, locations l
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id;
Non-Equijoins
A non-equijoin is a join condition containing something other than an equality operator.The relationship between the EMPLOYEES table and the JOB_GRADES table has an example of a non-equijoin. A relationship between the two tables is that the SALARY column in the EMPLOYEES table must be between the values in the LOWEST_SALARY and HIGHEST_SALARY columns of the JOB_GRADES table. The relationship is obtained using an operator other than equals (=).
Example:
SELECT e.last_name, e.salary, j.grade_level
FROM employees e, job_grades j
WHERE e.salary
BETWEEN j.lowest_sal AND j.highest_sal;
Outer Joins
Syntax
• You use an outer join to also see rows that do not meet the join condition.
• The Outer join operator is the plus sign (+).
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column(+) = table2.column;
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column = table2.column(+);
The missing rows can be returned if an outer join operator is used in the join condition. The operator
is a plus sign enclosed in parentheses (+), and it is placed on the “side” of the join that is deficient in
information. This operator has the effect of creating one or more null rows, to which one or more rows
from the nondeficient table can be joined.
Example:
SELECT e.last_name, e.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id(+) = d.department_id ;
Outer Join Restrictions
• The outer join operator can appear on only one side of the expression—the side that has information missing. It returns those rows from one table that have no direct match in the other table.
• A condition involving an outer join cannot use the IN operator or be linked to another condition by the OR operator
Self Join
Sometimes you need to join a table to itself.
Example:
To find the name of each employee’s manager, you need to join the EMPLOYEES table to itself, or perform a self join.
SELECT worker.last_name || ’ works for ’
|| manager.last_name
FROM employees worker, employees manager
WHERE worker.manager_id = manager.employee_id ;
Use a join to query data from more than one table.
SELECT table1.column, table2.column
FROM table1
[CROSS JOIN table2] |
[NATURAL JOIN table2] |
[JOIN table2 USING (column_name)] |
[JOIN table2
ON(table1.column_name = table2.column_name)] |
[LEFT|RIGHT|FULL OUTER JOIN table2
ON (table1.column_name = table2.column_name)];
In the syntax:
table1.column Denotes the table and column from which data is retrieved
CROSS JOIN Returns a Cartesian product from the two tables
NATURAL JOIN Joins two tables based on the same column name
JOIN table USING column_name Performs an equijoin based on the column name
JOIN table ON table1.column_name Performs an equijoin based on the condition in the ON clause
= table2.column_name
LEFT/RIGHT/FULL OUTER
Creating Cross Joins
• The CROSS JOIN clause produces the crossproduct of two tables.
• This is the same as a Cartesian product between the two tables.
Example:
SELECT last_name, department_name
FROM employees
CROSS JOIN departments ;
SELECT last_name, department_name
FROM employees, departments;
Creating Natural Joins
• The NATURAL JOIN clause is based on all columns in the two tables that have the same name.
• It selects rows from the two tables that have equal values in all matched columns.
• If the columns having the same names have different data types, an error is returned.
Example:
SELECT department_id, department_name,
location_id, city
FROM departments
NATURAL JOIN locations ;
LOCATIONS table is joined to the DEPARTMENT table by the LOCATION_ID column, which is the only column of the same name in both tables. If other common columns were present, the join would have used them all.
Example:
SELECT department_id, department_name,
location_id, city
FROM departments
NATURAL JOIN locations
WHERE department_id IN (20, 50);
Creating Joins with the USING Clause
• If several columns have the same names but the data types do not match, the NATURAL JOIN
clause can be modified with the USING clause to specify the columns that should be used for an
equijoin.
• Use the USING clause to match only one column when more than one column matches.
• Do not use a table name or alias in the referenced columns.
• The NATURAL JOIN and USING clauses are mutually exclusive.
Example:
SELECT l.city, d.department_name
FROM locations l JOIN departments d USING (location_id)
WHERE location_id = 1400;
EXAMPLE:
SELECT e.employee_id, e.last_name, d.location_id
FROM employees e JOIN departments d
USING (department_id) ;
Creating Joins with the ON Clause
• The join condition for the natural join is basically an equijoin of all columns with the same name.
• To specify arbitrary conditions or specify columns to join, the ON clause is used.
• The join condition is separated from other searchconditions.
• The ON clause makes code easy to understand.
Example:
SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM employees e JOIN departments d
ON (e.department_id = d.department_id);
EXAMPLE:
SELECT e.last_name emp, m.last_name mgr
FROM employees e JOIN employees m
ON (e.manager_id = m.employee_id);
INNER Versus OUTER Joins
• A join between two tables that returns the results of the inner join as well as unmatched rows left (or
right) tables is a left (or right) outer join.
• A join between two tables that returns the results of an inner join as well as the results of a left and
right join is a full outer join.
LEFT OUTER JOIN
Example:
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
LEFT OUTER JOIN departments d
ON (e.department_id = d.department_id) ;
Example of LEFT OUTER JOIN
This query retrieves all rows in the EMPLOYEES table, which is the left table even if there is no
match in the DEPARTMENTS table.
This query was completed in earlier releases as follows:
SELECT e.last_name, e.department_id, d.department_name
FROM employees e, departments d
WHERE d.department_id (+) = e.department_id;
RIGHT OUTER JOIN
Example:
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
RIGHT OUTER JOIN departments d
ON (e.department_id = d.department_id) ;
This query retrieves all rows in the DEPARTMENTS table, which is the right table even if there is no
match in the EMPLOYEES table.
This query was completed in earlier releases as follows:
SELECT e.last_name, e.department_id, d.department_name
FROM employees e, departments d
WHERE d.department_id = e.department_id (+);
FULL OUTER JOIN
Example:
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
FULL OUTER JOIN departments d
ON (e.department_id = d.department_id) ;
This query retrieves all rows in the EMPLOYEES table, even if there is no match in the
DEPARTMENTS table. It alslso retrieves all rows in the DEPARTMENTS table, even if there is no match in the EMPLOYEES table.
Find the Solution for the following:
1. Write a query to display the last name, department number, and department name for all employees.
Select e.last_name,e.department_id,d.department_name from employees e,department d where e.department_id=d.department_id;
2. Create a unique listing of all jobs that are in department 80. Include the location of the department in the output.
Select distinct job_id,location_id from employees,departments where employees.department_id=departments.department_id and employees.department_id=80;
3. Write a query to display the employee last name, department name, location ID, and city of all employees who earn a commission
Select e.last_name,d.department_name,d.location_id,l.city from employees e,departments d,locations l where e.department_id=d.department_id and d.location_id=l.location_id and mission_pct is not null;
4. Display the employee last name and department name for all employees who have an a(lowercase) in their last names. P
Select last_name,department_name from employees,departments where employees.department_id=departments.department_id and last_anme like’%a%’”;
5. Write a query to display the last name, job, department number, and department name for all employees who work in Toronto.
Select e.last_name,e.job_id,e.department_id,d.department_name from employees e join departments d on(e.department_id=d.department_id) join location l on(d.location_id=l.location_id) where lower(l.city)=’toronto’;
6. Display the employee last name and employee number along with their manager’s last name and manager number. Label the columns Employee, Emp#, Manager, and Mgr#, Respectively
Select w.last_anme “Employee”, w.employee_id “EMP#”, m.last_anme “Manager”, m.employee_id “Mgr#” from employees w join employees m on(w.manager_id=m.employee_id);
7. Modify lab4_6.sql to display all employees including King, who has no manager. Order the results by the employee number.
Select w.last_name “Employee”, w.employee_id “EMP#”,m.last_name “Manager”, m.employee_id “Mgr#” from employees w left outer join employees m on(w.manger_id=m.employee_id);
8. Create a query that displays employee last names, department numbers, and all the employees who work in the same department as a given employee. Give each column an appropriate label
Select e.department_id department, e.last_name employee,c.last_name colleague from employees e join employees c on (e.department_id=c.department_id) where e.employee_id c.employee_id order by e.departmetn_id,e.last_name,c.last_name;
9. Show the structure of the JOB_GRADES table. Create a query that displays the name, job,
department name, salary, and grade for all employees
desc job_grades select e.last_name, e.job_id,d.department_name,e.salary,j.grade_level from employees e,departments d,job_grades j where e.department_id=d.departm,ent_id and e.salary between j.lowest_sal and j.highest_sal;
[or]
Select e.last_name,e.job_id,d.department_name,e.saslary,j.grade_level from employees e join departments d on(e.department_id=d.department_id) join job_grades j on(e.salary between j.lowest_sal and j.highetst_sal);
10. Create a query to display the name and hire date of any employee hired after employee Davies.
Select e.last_name,e.hire_date from employees e,employees davies where davies.last_name=’Davies’ and davies.hire_date 8000 GROUP BY department_id;
Restricting Group Results
With the HAVING Clause .When you use the HAVING clause, the Oracle server restricts groups as follows:
1. Rows are grouped.
2. The group function is applied.
3. Groups matching the HAVING clause are displayed.
Using the HAVING Clause
SELECT department_id, MAX(salary) FROM employees
GROUP BY department_idHAVING MAX(salary)>10000 ;
The following example displays the department numbers and average salaries for those
departments with a maximum salary that is greater than $10,000:
SELECT department_id, AVG(salary) FROM employees GROUP BY department_id
HAVING max(salary)>10000;
Example displays the job ID and total monthly salary for each job that has a total payroll exceeding $13,000. The example excludes sales representatives and sorts the list by the total monthly salary.
SELECT job_id, SUM(salary) PAYROLL FROM employees WHERE job_id NOT LIKE '%REP%'
GROUP BY job_id HAVING SUM(salary) > 13000 ORDER BY SUM(salary);
Nesting Group Functions
Display the maximum average salary:
Group functions can be nested to a depth of two. The slide example displays the maximum average salary.
SELECT MAX(AVG(salary)) FROM employees GROUP BY department_id;
Summary
In this exercise, students should have learned how to:
• Use the group functions COUNT, MAX, MIN, and AVG
• Write queries that use the GROUP BY clause
• Write queries that use the HAVING clause
SELECT column, group_function
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column];
Find the Solution for the following:
Determine the validity of the following three statements. Circle either True or False.
1. Group functions work across many rows to produce one result per group.
True/False
2. Group functions include nulls in calculations.
True/False
3. The WHERE clause restricts rows prior to inclusion in a group calculation.
True/False
The HR department needs the following reports:
4. Find the highest, lowest, sum, and average salary of all employees. Label the columns
Maximum, Minimum, Sum, and Average, respectively. Round your results to the nearest
whole number
5. Modify the above query to display the minimum, maximum, sum, and
average salary for each job type.
6.Write a query to display the number of people with the same job. Generalize the query so that the user in the HR department is prompted for a job title.
7. Determine the number of managers without listing them. Label the column Number
of Managers. Hint: Use the MANAGER_ID column to determine the number of
managers.
8. Find the difference between the highest and lowest salaries. Label the column
DIFFERENCE.
9. Create a report to display the manager number and the salary of the lowest-paid
employee for that manager. Exclude anyone whose manager is not known. Exclude any
groups where the minimum salary is $6,000 or less. Sort the output in descending order
of salary.
10. Create a query to display the total number of employees and, of that total, the number of
employees hired in 1995, 1996, 1997, and 1998. Create appropriate column headings.
11. Create a matrix query to display the job, the salary for that job based on department
number, and the total salary for that job, for departments 20, 50, 80, and 90, giving each
column an appropriate heading.
12.Write a query to display each department’s name, location, number of employees, and the average salary for all the employees in that department. Label the column name-Location, Number of people, and salary respectively. Round the average salary to two decimal places.
EXERCISE-9
Sub queries
Objectives
After completing this lesson, you should be able to do the following:
• Define subqueries
• Describe the types of problems that subqueries can solve
• List the types of subqueries
• Write single-row and multiple-row subqueries
Using a Subquery to Solve a Problem
Who has a salary greater than Abel’s?
Main query:
Which employees have salaries greater than Abel’s salary?
Subquery:
What is Abel’s salary?
Subquery Syntax
SELECT select_list FROM table WHERE expr operator (SELECT select_list FROM table);
• The subquery (inner query) executes once before the main query (outer query).
• The result of the subquery is used by the main query.
A subquery is a SELECT statement that is embedded in a clause of another SELECT statement. You can build powerful statements out of simple ones by using subqueries. They can be very useful when you need to select rows from a table with a condition that depends on the data in the table itself.
You can place the subquery in a number of SQL clauses, including the following:
• WHERE clause
• HAVING clause
• FROM clause
In the syntax:
operator includes a comparison condition such as >, =, or IN
Note: Comparison conditions fall into two classes: single-row operators
(>, =, >=, (SELECT salary FROM employees WHERE employee_id = 143);
Using Group Functions in a Subquery
Displays the employee last name, job ID, and salary of all employees whose salary is equal to the minimum salary. The MIN group function returns a single value (2500) to the outer query.
SELECT last_name, job_id, salary FROM employees WHERE salary = (SELECT MIN(salary)
FROM employees);
The HAVING Clause with Subqueries
• The Oracle server executes subqueries first.
• The Oracle server returns results into the HAVING clause of the main query.
Displays all the departments that have a minimum salary greater than that of department 50.
SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) >
(SELECT MIN(salary)
FROM employees
WHERE department_id = 50);
Example
Find the job with the lowest average salary.
SELECT job_id, AVG(salary)
FROM employees
GROUP BY job_id
HAVING AVG(salary) = (SELECT MIN(AVG(salary))
FROM employees
GROUP BY job_id);
What Is Wrong in this Statements?
SELECT employee_id, last_name
FROM employees
WHERE salary =(SELECT MIN(salary) FROM employees GROUP BY department_id);
Will This Statement Return Rows?
SELECT last_name, job_id
FROM employees
WHERE job_id =(SELECT job_id FROM employees WHERE last_name = 'Haas');
Multiple-Row Subqueries
• Return more than one row
• Use multiple-row comparison operators
[pic]
Example
Find the employees who earn the same salary as the minimum salary for each department.
SELECT last_name, salary, department_id FROM employees WHERE salary IN (SELECT MIN(salary)
FROM employees GROUP BY department_id);
Using the ANY Operator in Multiple-Row Subqueries
SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary < ANY
(SELECT salary FROM employees WHERE job_id = 'IT_PROG') AND job_id 'IT_PROG';
Displays employees who are not IT programmers and whose salary is less than that of any IT programmer. The maximum salary that a programmer earns is $9,000.
< ANY means less than the maximum. >ANY means more than the minimum. =ANY is equivalent to IN.
Using the ALL Operator in Multiple-Row Subqueries
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ALL (SELECT salary FROM employees WHERE job_id = 'IT_PROG')
AND job_id 'IT_PROG';
Displays employees whose salary is less than the salary of all employees with a job ID of IT_PROG and whose job is not IT_PROG.
➢ ALL means more than the maximum, and create table ititems(itemid number(3), actualprice number(5), ordid number(4), prodid number(4));
Table created.
SQL> insert into ititems values(101, 2000, 500, 201);
1 row created.
SQL> insert into ititems values(102, 3000, 1600, 202);
1 row created.
SQL> insert into ititems values(103, 4000, 600, 202);
1 row created.
SQL> select * from ititems;
ITEMID ACTUALPRICE ORDID PRODID
--------- ----------- -------- ---------
101 2000 500 201
102 3000 1600 202
103 4000 600 202
PROGRAM FOR GENERAL PROCEDURE – SELECTED RECORD’S PRICE IS INCREMENTED BY 500 , EXECUTING THE PROCEDURE CREATED AND DISPLAYING THE UPDATED TABLE
SQL> create procedure itsum(identity number, total number) is price number;
2 null_price exception;
3 begin
4 select actualprice into price from ititems where itemid=identity;
5 if price is null then
6 raise null_price;
7 else
8 update ititems set actualprice=actualprice+total where itemid=identity;
9 end if;
10 exception
11 when null_price then
12 dbms_output.put_line('price is null');
13 end;
14 /
Procedure created.
SQL> exec itsum(101, 500);
PL/SQL procedure successfully completed.
SQL> select * from ititems;
ITEMID ACTUALPRICE ORDID PRODID
--------- ----------- --------- ---------
101 2500 500 201
102 3000 1600 202
103 4000 600 202
PROCEDURE FOR ‘IN’ PARAMETER – CREATION, EXECUTION
SQL> set serveroutput on;
SQL> create procedure yyy (a IN number) is price number;
2 begin
3 select actualprice into price from ititems where itemid=a;
4 dbms_output.put_line('Actual price is ' || price);
5 if price is null then
6 dbms_output.put_line('price is null');
7 end if;
8 end;
9 /
Procedure created.
SQL> exec yyy(103);
Actual price is 4000
PL/SQL procedure successfully completed.
PROCEDURE FOR ‘OUT’ PARAMETER – CREATION, EXECUTION
SQL> set serveroutput on;
SQL> create procedure zzz (a in number, b out number) is identity number;
2 begin
3 select ordid into identity from ititems where itemid=a;
4 if identity declare
2 a number;
3 b number;
4 begin
5 zzz(101,b);
6 dbms_output.put_line('The value of b is '|| b);
7 end;
8 /
The value of b is 100
PL/SQL procedure successfully completed.
PROCEDURE FOR ‘INOUT’ PARAMETER – CREATION, EXECUTION
SQL> create procedure itit ( a in out number) is
2 begin
3 a:=a+1;
4 end;
5 /
Procedure created.
SQL> declare
2 a number:=7;
3 begin
4 itit(a);
5 dbms_output.put_line(‘The updated value is ‘||a);
6 end;
7 /
The updated value is 8
PL/SQL procedure successfully completed.
CREATE THE TABLE ‘ITTRAIN’ TO BE USED FOR FUNCTIONS
SQL>create table ittrain ( tno number(10), tfare number(10));
Table created.
SQL>insert into ittrain values (1001, 550);
1 row created.
SQL>insert into ittrain values (1002, 600);
1 row created.
SQL>select * from ittrain;
TNO TFARE
--------- ------------
1001 550
1002 600
PROGRAM FOR FUNCTION AND IT’S EXECUTION
SQL> create function aaa (trainnumber number) return number is
2 trainfunction ittrain.tfare % type;
3 begin
4 select tfare into trainfunction from ittrain where tno=trainnumber;
5 return(trainfunction);
6 end;
7 /
Function created.
SQL> set serveroutput on;
SQL> declare
2 total number;
3 begin
4 total:=aaa (1001);
5 dbms_output.put_line('Train fare is Rs. '||total);
6 end;
7 /
Train fare is Rs.550
PL/SQL procedure successfully completed.
FACTORIAL OF A NUMBER USING FUNCTION — PROGRAM AND EXECUTION
SQL> create function itfact (a number) return number is
2 fact number:=1;
3 b number;
4 begin
5 b:=a;
6 while b>0
7 loop
8 fact:=fact*b;
9 b:=b-1;
10 end loop;
11 return(fact);
12 end;
13 /
Function created.
SQL> set serveroutput on;
SQL> declare
2 a number:=7;
3 f number(10);
4 begin
5 f:=itfact(a);
6 dbms_output.put_line(‘The factorial of the given number is’||f);
7 end;
8 /
The factorial of the given number is 5040
PL/SQL procedure successfully completed.
EXERCISE-15
TRIGGER
DEFINITION
A trigger is a statement that is executed automatically by the system as a side effect of a modification to the database. The parts of a trigger are,
• Trigger statement: Specifies the DML statements and fires the trigger body. It also specifies the table to which the trigger is associated.
• Trigger body or trigger action: It is a PL/SQL block that is executed when the triggering statement is used.
• Trigger restriction: Restrictions on the trigger can be achieved
The different uses of triggers are as follows,
• To generate data automatically
• To enforce complex integrity constraints
• To customize complex securing authorizations
• To maintain the replicate table
• To audit data modifications
TYPES OF TRIGGERS
The various types of triggers are as follows,
• Before: It fires the trigger before executing the trigger statement.
• After: It fires the trigger after executing the trigger statement
• .
• For each row: It specifies that the trigger fires once per row
• .
• For each statement: This is the default trigger that is invoked. It specifies that the trigger fires once per statement.
VARIABLES USED IN TRIGGERS
• :new
• :old
These two variables retain the new and old values of the column updated in the database. The values in these variables can be used in the database triggers for data manipulation
SYNTAX
create or replace trigger triggername [before/after] {DML statements}
on [tablename] [for each row/statement]
begin
-------------------------
-------------------------
-------------------------
exception
end;
USER DEFINED ERROR MESSAGE
The package “raise_application_error” is used to issue the user defined error messages
Syntax: raise_application_error(error number,‘error message‘);
The error number can lie between -20000 and -20999.
The error message should be a character string.
TO CREATE THE TABLE ‘ITEMPLS’
SQL> create table itempls (ename varchar2(10), eid number(5), salary number(10));
Table created.
SQL> insert into itempls values('xxx',11,10000);
1 row created.
SQL> insert into itempls values('yyy',12,10500);
1 row created.
SQL> insert into itempls values('zzz',13,15500);
1 row created.
SQL> select * from itempls;
ENAME EID SALARY
---------- --------- ---------
xxx 11 10000
yyy 12 10500
zzz 13 15500
TO CREATE A SIMPLE TRIGGER THAT DOES NOT ALLOW INSERT UPDATE AND DELETE OPERATIONS ON THE TABLE
SQL> create trigger ittrigg before insert or update or delete on itempls for each row
2 begin
3 raise_application_error(-20010,'You cannot do manipulation');
4 end;
5
6 /
Trigger created.
SQL> insert into itempls values('aaa',14,34000);
insert into itempls values('aaa',14,34000)
*
ERROR at line 1:
ORA-20010: You cannot do manipulation
ORA-06512: at "STUDENT.ITTRIGG", line 2
ORA-04088: error during execution of trigger 'STUDENT.ITTRIGG'
SQL> delete from itempls where ename='xxx';
delete from itempls where ename='xxx'
*
ERROR at line 1:
ORA-20010: You cannot do manipulation
ORA-06512: at "STUDENT.ITTRIGG", line 2
ORA-04088: error during execution of trigger 'STUDENT.ITTRIGG'
SQL> update itempls set eid=15 where ename='yyy';
update itempls set eid=15 where ename='yyy'
*
ERROR at line 1:
ORA-20010: You cannot do manipulation
ORA-06512: at "STUDENT.ITTRIGG", line 2
ORA-04088: error during execution of trigger 'STUDENT.ITTRIGG'
TO DROP THE CREATED TRIGGER
SQL> drop trigger ittrigg;
Trigger dropped.
TO CREATE A TRIGGER THAT RAISES AN USER DEFINED ERROR MESSAGE AND DOES NOT ALLOW UPDATION AND INSERTION
SQL> create trigger ittriggs before insert or update of salary on itempls for each row
2 declare
3 triggsal itempls.salary%type;
4 begin
5 select salary into triggsal from itempls where eid=12;
6 if(:new.salary>triggsal or :new.salary insert into itempls values ('bbb',16,45000);
insert into itempls values ('bbb',16,45000)
*
ERROR at line 1:
ORA-04098: trigger 'STUDENT.ITTRIGGS' is invalid and failed re-validation
SQL> update itempls set eid=18 where ename='zzz';
update itempls set eid=18 where ename='zzz'
*
ERROR at line 1:
ORA-04298: trigger 'STUDENT.ITTRIGGS' is invalid and failed re-validation
Cursor for loop
• Explicit cursor
• Implicit cursor
TO CREATE THE TABLE ‘SSEMPP’
SQL> create table ssempp( eid number(10), ename varchar2(20), job varchar2(20), sal number (10),dnonumber(5));
Table created.
SQL> insert into ssempp values(1,'nala','lecturer',34000,11);
1 row created.
SQL> insert into ssempp values(2,'kala',' seniorlecturer',20000,12);
1 row created.
SQL> insert into ssempp values(5,'ajay','lecturer',30000,11);
1 row created.
SQL> insert into ssempp values(6,'vijay','lecturer',18000,11);
1 row created.
SQL> insert into ssempp values(3,'nila','professor',60000,12);
1 row created.
SQL> select * from ssempp;
EID ENAME JOB SAL DNO
--------- -------------------- -------------------- --------- ---------
1 nala lecturer 34000 11
2 kala seniorlecturer 20000 12
5 ajay lecturer 30000 11
6 vijay lecturer 18000 11
3 nila professor 60000 12
EXTRA PROGRAMS
TO WRITE A PL/SQL BLOCK TO DISPLAY THE EMPOYEE ID AND EMPLOYEE NAME USING CURSOR FOR LOOP
SQL> set serveroutput on;
SQL> declare
2 begin
3 for emy in (select eid,ename from ssempp)
4 loop
5 dbms_output.put_line('Employee id and employee name are '|| emy.eid ‘and’|| emy.ename);
6 end loop;
7 end;
8 /
Employee id and employee name are 1 and nala
Employee id and employee name are 2 and kala
Employee id and employee name are 5 and ajay
Employee id and employee name are 6 and vijay
Employee id and employee name are 3 and nila
PL/SQL procedure successfully completed.
TO WRITE A PL/SQL BLOCK TO UPDATE THE SALARY OF ALL EMPLOYEES WHERE DEPARTMENT NO IS 11 BY 5000 USING CURSOR FOR LOOP AND TO DISPLAY THE UPDATED TABLE
SQL> set serveroutput on;
SQL> declare
2 cursor cem is select eid,ename,sal,dno from ssempp where dno=11;
3 begin
4 --open cem;
5 for rem in cem
6 loop
7 update ssempp set sal=rem.sal+5000 where eid=rem.eid;
8 end loop;
9 --close cem;
10 end;
11 /
PL/SQL procedure successfully completed.
SQL> select * from ssempp;
EID ENAME JOB SAL DNO
--------- -------------------- -------------------- --------- ---------
1 nala lecturer 39000 11
2 kala seniorlecturer 20000 12
5 ajay lecturer 35000 11
6 vijay lecturer 23000 11
3 nila professor 60000 12
TO WRITE A PL/SQL BLOCK TO DISPLAY THE EMPLOYEE ID AND EMPLOYEE NAME WHERE DEPARTMENT NUMBER IS 11 USING EXPLICIT CURSORS
1 declare
2 cursor cenl is select eid,sal from ssempp where dno=11;
3 ecode ssempp.eid%type;
4 esal empp.sal%type;
5 begin
6 open cenl;
7 loop
8 fetch cenl into ecode,esal;
9 exit when cenl%notfound;
10 dbms_output.put_line(' Employee code and employee salary are' || ecode ‘and’|| esal);
11 end loop;
12 close cenl;
13* end;
SQL> /
Employee code and employee salary are 1 and 39000
Employee code and employee salary are 5 and 35000
Employee code and employee salary are 6 and 23000
PL/SQL procedure successfully completed.
TO WRITE A PL/SQL BLOCK TO UPDATE THE SALARY BY 5000 WHERE THE JOB IS LECTURER , TO CHECK IF UPDATES ARE MADE USING IMPLICIT CURSORS AND TO DISPLAY THE UPDATED TABLE
SQL> declare
2 county number;
3 begin
4 update ssempp set sal=sal+10000 where job='lecturer';
5 county:= sql%rowcount;
6 if county > 0 then
7 dbms_output.put_line('The number of rows are '|| county);
8 end if;
9 if sql %found then
10 dbms_output.put_line('Employee record modification successful');
11 else if sql%notfound then
12 dbms_output.put_line('Employee record is not found');
13 end if;
14 end if;
15 end;
16 /
The number of rows are 3
Employee record modification successful
PL/SQL procedure successfully completed.
SQL> select * from ssempp;
EID ENAME JOB SAL DNO
--------- -------------------- -------------------- --------- ---------
1 nala lecturer 44000 11
2 kala seniorlecturer 20000 12
5 ajay lecturer 40000 11
6 vijay lecturer 28000 11
3 nila professor 60000 12
PROGRAMS
TO DISPLAY HELLO MESSAGE
SQL> set serveroutput on;
SQL> declare
2 a varchar2(20);
3 begin
4 a:='Hello';
5 dbms_output.put_line(a);
6 end;
7 /
Hello
PL/SQL procedure successfully completed.
TO INPUT A VALUE FROM THE USER AND DISPLAY IT
SQL> set serveroutput on;
SQL> declare
2 a varchar2(20);
3 begin
4 a:=&a;
5 dbms_output.put_line(a);
6 end;
7 /
Enter value for a: 5
old 4: a:=&a;
new 4: a:=5;
5
PL/SQL procedure successfully completed.
GREATEST OF TWO NUMBERS
SQL> set serveroutput on;
SQL> declare
2 a number(7);
3 b number(7);
4 begin
5 a:=&a;
6 b:=&b;
7 if(a>b) then
8 dbms_output.put_line (' The grerater of the two is'|| a);
9 else
10 dbms_output.put_line (' The grerater of the two is'|| b);
11 end if;
12 end;
13 /
Enter value for a: 5
old 5: a:=&a;
new 5: a:=5;
Enter value for b: 9
old 6: b:=&b;
new 6: b:=9;
The grerater of the two is9
PL/SQL procedure successfully completed.
GREATEST OF THREE NUMBERS
SQL> set serveroutput on;
SQL> declare
2 a number(7);
3 b number(7);
4 c number(7);
5 begin
6 a:=&a;
7 b:=&b;
8 c:=&c;
9 if(a>b and a>c) then
10 dbms_output.put_line (' The greatest of the three is ' || a);
11 else if (b>c) then
12 dbms_output.put_line (' The greatest of the three is ' || b);
13 else
14 dbms_output.put_line (' The greatest of the three is ' || c);
15 end if;
16 end if;
17 end;
18 /
Enter value for a: 5
old 6: a:=&a;
new 6: a:=5;
Enter value for b: 7
old 7: b:=&b;
new 7: b:=7;
Enter value for c: 1
old 8: c:=&c;
new 8: c:=1;
The greatest of the three is 7
PL/SQL procedure successfully completed.
PRINT NUMBERS FROM 1 TO 5 USING SIMPLE LOOP
SQL> set serveroutput on;
SQL> declare
2 a number:=1;
3 begin
4 loop
5 dbms_output.put_line (a);
6 a:=a+1;
7 exit when a>5;
8 end loop;
9 end;
10 /
1
2
3
4
5
PL/SQL procedure successfully completed.
PRINT NUMBERS FROM 1 TO 4 USING WHILE LOOP
SQL> set serveroutput on;
SQL> declare
2 a number:=1;
3 begin
4 while(a set serveroutput on;
SQL> declare
2 a number:=1;
3 begin
4 for a in 1..5
5 loop
6 dbms_output.put_line (a);
7 end loop;
8 end;
9 /
1
2
3
4
5
PL/SQL procedure successfully completed.
PRINT NUMBERS FROM 1 TO 5 IN REVERSE ORDER USING FOR LOOP
SQL> set serveroutput on;
SQL> declare
2 a number:=1;
3 begin
4 for a in reverse 1..5
5 loop
6 dbms_output.put_line (a);
7 end loop;
8 end;
9 /
5
4
3
2
1
PL/SQL procedure successfully completed.
TO CALCULATE AREA OF CIRCLE
SQL> set serveroutput on;
SQL> declare
2 pi constant number(4,2):=3.14;
3 a number(20);
4 r number(20);
5 begin
6 r:=&r;
7 a:= pi* power(r,2);
8 dbms_output.put_line (' The area of circle is ' || a);
9 end;
10 /
Enter value for r: 2
old 6: r:=&r;
new 6: r:=2;
The area of circle is 13
PL/SQL procedure successfully completed.
TO CREATE SACCOUNT TABLE
SQL> create table saccount ( accno number(5), name varchar2(20), bal number(10));
Table created.
SQL> insert into saccount values ( 1,'mala',20000);
1 row created.
SQL> insert into saccount values (2,'kala',30000);
1 row created.
SQL> select * from saccount;
ACCNO NAME BAL
--------- -------------------- ---------
1 mala 20000
2 kala 30000
SQL> set serveroutput on;
SQL> declare
2 a_bal number(7);
3 a_no varchar2(20);
4 debit number(7):=2000;
5 minamt number(7):=500;
6 begin
7 a_no:=&a_no;
8 select bal into a_bal from saccount where accno= a_no;
9 a_bal:= a_bal-debit;
10 if (a_bal > minamt) then
11 update saccount set bal=bal-debit where accno=a_no;
12 end if;
13 end;
14
15 /
Enter value for a_no: 1
old 7: a_no:=&a_no;
new 7: a_no:=1;
PL/SQL procedure successfully completed.
SQL> select * from saccount;
ACCNO NAME BAL
--------- -------------------- ---------
1 mala 18000
2 kala 30000
TO CREATE TABLE SROUTES
SQL> create table sroutes ( rno number(5), origin varchar2(20), destination varchar2(20), fare numbe
r(10), distance number(10));
Table created.
SQL> insert into sroutes values ( 2, 'chennai', 'dindugal', 400,230);
1 row created.
SQL> insert into sroutes values ( 3, 'chennai', 'madurai', 250,300);
1 row created.
SQL> insert into sroutes values ( 6, 'thanjavur', 'palani', 350,370);
1 row created.
SQL> select * from sroutes;
RNO ORIGIN DESTINATION FARE DISTANCE
--------- -------------------- -------------------- --------- ---------
2 chennai dindugal 400 230
3 chennai madurai 250 300
6 thanjavur palani 350 370
SQL> set serveroutput on;
SQL> declare
2 route sroutes.rno % type;
3 fares sroutes.fare % type;
4 dist sroutes.distance % type;
5 begin
6 route:=&route;
7 select fare, distance into fares , dist from sroutes where rno=route;
8 if (dist < 250) then
9 update sroutes set fare=300 where rno=route;
10 else if dist between 250 and 370 then
11 update sroutes set fare=400 where rno=route;
12 else if (dist > 400) then
13 dbms_output.put_line('Sorry');
14 end if;
15 end if;
16 end if;
17 end;
18 /
Enter value for route: 3
old 6: route:=&route;
new 6: route:=3;
PL/SQL procedure successfully completed.
SQL> select * from sroutes;
RNO ORIGIN DESTINATION FARE DISTANCE
--------- -------------------- -------------------- --------- ---------
2 chennai dindugal 400 230
3 chennai madurai 400 300
6 thanjavur palani 350 370
TO CREATE SCA LCULATE TABLE
SQL> create table scalculate ( radius number(3), area number(5,2));
Table created.
SQL> desc scalculate;
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
RADIUS NUMBER(3)
AREA NUMBER(5,2)
SQL> set serveroutput on;
SQL> declare
2 pi constant number(4,2):=3.14;
3 area number(5,2);
4 radius number(3);
5 begin
6 radius:=3;
7 while (radius select * from scalculate;
RADIUS AREA
--------- ---------
3 28.26
4 50.24
5 78.5
6 113.04
7 153.86
TO CALCULATE FACTORIAL OF A GIVEN NUMBER
SQL> set serveroutput on;
SQL> declare
2 f number(4):=1;
3 i number(4);
4 begin
5 i:=&i;
6 while(i>=1)
7 loop
8 f:=f*i;
9 i:=i-1;
10 end loop;
11 dbms_output.put_line('The value is ' || f);
12 end;
13 /
Enter value for i: 5
old 5: i:=&i;
new 5: i:=5;
The value is 120
PL/SQL procedure successfully completed.
[pic]
-----------------------
Select dept name from dept;
Oracle server
SINGLE-ROW FUNCTIONS
CONVERSION
DATE
NUMBER
CHARACTER
GENERAL?
Þ
ù
ú
'
(
,
-
2
N
O
P
“
œ
¡
ðáϽϱ¦?”ˆ|?seZLA9A
h¿
JOJQJhß |ßh’5IOJQJh¿
JhK‰5?>*[pic]OJQJh’5I5?>*[pic]OJQJh¿
Jh’5I5?>*[pic]OJQJhß |ß5?OJQJhß |ßh9ó5?OJQJhß |ßhn>F5?OJQJhn>F5?OJQJh9ó5?OJQJhß |ßh}}OJQJhß |ßh}}5?OJQJ#hß |ßh9ó5?CJ,OJ
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related searches
- free client database management software
- nonprofit database management software
- database management best practices
- database management system textbook pdf
- database management system book pdf
- client database management free
- free school database management software
- database management system pdf books
- common database management systems
- access database management system
- database management pdf
- database management systems list