Download SQL Tutorial: Database Creation, Data Insertion, and Control Structures and more Lab Reports Database Management Systems (DBMS) in PDF only on Docsity!
Aim: To Implement the Referential Integrity. Procedure:
ļ Creating a Master table (course) SQL> create table course 2 (cno number(5) primary key, 3 cname varchar2(20)); Table created.
SQL> desc course; Name Null? Type
CNO NOT NULL NUMBER(5) CNAME VARCHAR2(20)
SQL> insert into course values(&cno,'&cname'); Enter value for cno: 1001 Enter value for cname: BSC old 1: insert into course values(&cno,'&cname') new 1: insert into course values(1001,'BSC') 1 row created.
PRACTICAL NO: 1 IMPLEMENTATION OF REFERENTIAL INTEGRITY
SQL> /
Enter value for cno: 1002 Enter value for cname: BCOM old 1: insert into course values(&cno,'&cname') new 1: insert into course values(1002,'BCOM') 1 row created.
SQL> / Enter value for cno: 1003 Enter value for cname: BCA old 1: insert into course values(&cno,'&cname') new 1: insert into course values(1003,'BCA') 1 row created.
SQL> / Enter value for cno: 1004 Enter value for cname: BA old 1: insert into course values(&cno,'&cname') new 1: insert into course values(1004,'BA') 1 row created. SQL> commit; Commit complete.
SNAME VARCHAR2(30)
DOB DATE
CNO NUMBER(5)
SQL> insert into student values(&sno,'&sname','&dob',&cno); Enter value for sno: 1 Enter value for sname: Bhanu Enter value for dob: 10-jan- Enter value for cno: 1001 old 1: insert into student values(&sno,'&sname','&dob',&cno) new 1: insert into student values(1,'Bhanu','10-jan-1995',1001) 1 row created.
SQL> / Enter value for sno: 2 Enter value for sname: Swathi Enter value for dob: 26-Aug- Enter value for cno: 1002 old 1: insert into student values(&sno,'&sname','&dob',&cno) new 1: insert into student values(2,'Swathi','26-Aug-1998',1002) 1 row created.
SQL> /
Enter value for sno: 3 Enter value for sname: Mahesh Enter value for dob: 12-Apr- Enter value for cno: 1003 old 1: insert into student values(&sno,'&sname','&dob',&cno) new 1: insert into student values(3,'Mahesh','12-Apr-1999',1003) 1 row created.
SQL> / Enter value for sno: 4 Enter value for sname: Aahil Enter value for dob: 08-Oct- Enter value for cno: 1004 old 1: insert into student values(&sno,'&sname','&dob',&cno) new 1: insert into student values(4,'Aahil','08-Oct-2000',1004) 1 row created.
SQL> commit; Commit complete.
Aim: To Implement the aggregate functions. Procedure: SQL provides the various Built-in functions like shown below table:
Step 1: Now let us try to create the table EMP15 as Shown in below Fig.
SNO. BUILT-IN FUNCTION DESCRIPTION
1 COUNT (^) to count the number of rows of the relation 2 MAX to find the maximum value of the attribute (column) 3 MIN (^) to find the minimum value of the attribute 4 SUM (^) to find the sum of values of the attribute 5 AVG to find the average of n values, ignoring null values.
PRACTICAL NO: 2 IMPLEMENTATION OF AGGREGATE FUNCTIONS
Step 2: Now let us try to inserting the values of EMP15 as Shown in below Fig.
Step 3: Now let us try to Performing Aggregate functions as follows:
1. Count : to count the no.of rows of the relation. syntax: sql>select count(attribute_name) from table_name;
5. Avg: to find the average of n values,ignoring null values. syntax: sql>select avg(attribute_name) from table_name;
Aim: To Implement the joins concept. Procedure: Syntax for joining tables is: select table1.column,table2.column,......tablen.column, from table1,table2.....tablen where table1.column1=table2.column2; ļ Equi join: Consider the following tables: Table: EMP
Table: DEPT
Practical No: 3 DEMONSTRATION ON JOINS
ļ To Perform the Right outer join as follows:
Aim: To Implement the Control Structures.
Procedure: ļ Conditional Control IF-THEN SYNTAX: IF condition THEN Sequence of statements; END IF;
HINT: SQL> SET SERVEROUTPUT ON;
/Program to find a person is major or not using if statement/** declare age number; begin age:=&age; if age>=18 then dbms_output.put_line('You are a Major'); end if; end; /
PRACTICAL NO: 4 DEMONSTRATION ON CONTROL STRUCTURES
Output: Enter value for age: 20 old 4: age:=&age; new 4: age:=20; You are a Major PL/SQL procedure successfully completed.
ļ Iterative Control WHILE-LOOP
SYNTAX: WHILE condition LOOP
Sequence of statements; END LOOP;
/Program to demonstrate the use of while loop/**
DECLARE a number(2) := 10; BEGIN WHILE a < 20 LOOP dbms_output.put_line('value of a: ' || a); a := a + 1; END LOOP; END;
Output: value of a: 10 value of a: 11 value of a: 12 value of a: 13 value of a: 14 value of a: 15 value of a: 16 value of a: 17 value of a: 18 value of a: 19 PL/SQL procedure successfully completed.
FOR-LOOP SYNTAX: FOR counter IN lower limit.. Higher limit LOOP Sequence of statements; END LOOP;
/ Program to print the table of a given number using for loop/** DECLARE n number; i number; p number;
Aim:
To Create the Electricity Bill Calculation Using Cursors
Procedure:
ļ Creation of Table (ebill):
SQL> create table ebill(name varchar2(10), 2 address varchar2(20), 3 city varchar2(20), 4 unit number(10));
Table created.
SQL> insert into ebill values( '&name', '&address', '&city', '&unit');
Enter value for name: Madhu old 2: '&name', new 2: 'Madhu', Enter value for address: Iskon Road old 3: '&address', new 3: 'Iskon Road', Enter value for city: Tirupathi old 4: '&city', new 4: 'Tirupathi', Enter value for unit: 100 old 5: '&unit') new 5: '100')
1 row created.
Practical No: 5 DEMONSTRATION ON CURSORS
SQL> /
Enter value for name: Riyaz old 2: '&name', new 2: 'Riyaz', Enter value for address: VRC CENTRE old 3: '&address', new 3: 'VRC CENTRE', Enter value for city: NELLORE old 4: '&city', new 4: 'NELLORE', Enter value for unit: 200 old 5: '&unit') new 5: '200') 1 row created.
SQL> / Enter value for name: LAKSHMI old 2: '&name', new 2: 'LAKSHMI', Enter value for address: NEHRU STREET old 3: '&address', new 3: 'NEHRU STREET', Enter value for city: VIZAQ old 4: '&city', new 4: 'VIZAQ', Enter value for unit: 300 old 5: '&unit') new 5: '300') 1 row created.
SQL> / Enter value for name: RAMA RAO old 2: '&name', new 2: 'RAMA RAO', Enter value for address: BANJARA HILLS old 3: '&address', new 3: 'BANJARA HILLS', Enter value for city: HYDERABAD
