Sql lab experiments

INDEX S.No. Date Experiment Page No. 1 Create a table employee (empno,empname, address, deptno, salary) Write the queries for the following (i) Display all the records of the employee table (ii) Display empno ,empname, and salary of all the employees in the employee table (iii) Display all the records of the employees from department number 1. (iv) Display the empno and name of all the employees from deptno2 (v) Display empno,empname ,deptno and salary in the descending order of Salary (vi) Display the empno and name of employees whose salary is between 2000 and 5000 (vii) Change the salary of the employee to 25000 whose salary is 2000 (viii) Change the address of a particular employee (ix) Display the details of all the employee whose name starts with ‘S’. (x) Display the details of all the employees whose name ends with ‘a’ 2-4 2 Create two tables Student (rollno, sname, dno) Department (dno, dname) With primary key and foreign key relationships and check the integrity constraint. (i) Write a query to display the rollno, sname, dno, and dname for all students. 5-6 3 Demonstrate ALTER TABLE statement to add, delete, or modify columns in an existing table. (First, create a table ‘Products’ with pid, pname attributes – then, add price and company attributes and work with them) 7-8 4 Demonstrate DROP TABLE and TRUNCATE TABLE commands (First, create a table ‘Suppliers’ with sno,sname and location attributes) 9 5 Demonstrate the following constraints (i) not null (ii) unique (iii) check (iv) default 10-11 6 Write SQL queries to demonstrate aggregate functions 12-13 7 Write SQL queries to demonstrate set operations 14-15 8 Write a PL/SQL programme to find the biggest of two numbers (use ‘if’ ) 16 9 Write a PL/SQL programme to display all the even numbers between 1 and 20 16 10 Write a PL/SQL programme to demonstrate cursors 17-18 11 Write a PL/SQL programme to demonstrate procedures. 19-20 12 Write a PL/SQL programme to demonstrate functions 21 13 Write a PL/SQL programme to demonstrate triggers. 22-23

2 | P a g e Experiment No: 1 create table employee(empno number(2), empname varchar2(10), address varchar2(30), deptno number(2), salary number(6), PRIMARY KEY(empno)); Output: Table Created. insert into employee values(01,'Arun','16/a Chittoor',02,2000); 1 row(s) inserted. insert into employee values(02,'Ravi','12/b Chittoor',01,2500); 1 row(s) inserted. insert into employee values(03,'Asha','18/a Chittoor',02,5000); 1 row(s) inserted. (i) Display all the records of the employee table Select * from employee; E M PNO E MPNAME ADDRE S S DE PTNO S ALARY 1 Arun 16/a Chittoor 2 2000 2 Ravi 12/bChittoor 1 2500 3 Asha 18/a Chittoor 2 5000 (ii) Display empno ,empname, and salary of all the employees in the employee table select empno,empname,salary from employee; E MPNO E MP NAME S ALARY 1 Arun 2000 2 Ravi 2500 3 Asha 5000 (iii) Display all the records of the employees from department number 1. select * from employee where deptno = 01; E MPNO E MP NAME ADDRE S S DE P TNO S ALARY 2 Ravi 12/b Chittoor 1 2500 (iv) Display the empno and name of all the employees from deptno2 select empno,empname from employee where deptno = 02; E MPNO E MP NAME 1 Arun 3 Asha

3 | P a g e (v) Display empno,empname ,deptno and salary in the descending order of Salary select empno,empname,deptno,salary from employee order by salary desc; E MPNO E MP NAME DE P TNO S ALARY 3 Asha 2 5000 2 Ravi 1 2500 1 Arun 2 2000 (vi) Display the empno and name of employees whose salary is between 2000 and 5000 select empno,empname from employee where salary between 2000 and 5000; E MPNO E MP NAME 1 Arun 2 Ravi 3 Asha (vii) Change the salary of the employee to 25000 whose salary is 2000 update employee set salary = 25000 where salary = 2000; 1 row(s) updated. Select * from employee; E MPNO E MP NAME ADDRE S S DE P TNO S ALARY 1 Arun 16/a Chittoor 2 25000 2 Ravi 12/b Chittoor 1 2500 3 Asha 18/a Chittoor 2 5000 (viii) Change the address of a particular employee update employee set address = '15/c Chittoor' where address = '12/b Chittoor'; 1 row(s) updated. Select * from employee; E MPNO E MP NAME ADDRE S S DE P TNO S ALARY 1 Arun 16/a Chittoor 2 25000 2 Ravi 15/c Chittoor 1 2500 3 Asha 18/a Chittoor 2 5000

4 | P a g e insert into employee values(4,'Shami','11/a Chittoor',1,34000); 1 row(s) inserted. Select * from employee; E MPNO E MP NAME ADDRE S S DE P TNO S ALARY 1 Arun 16/a Chittoor 2 25000 2 Ravi 15/c Chittoor 1 2500 3 Asha 18/a Chittoor 2 5000 4 Shami 11/a Chittoor 1 34000 (ix) Display the details of all the employee whose name starts with ‘S’. select * from employee where empname like 'S%'; E MPNO E MP NAME ADDRE S S DE P TNO S ALARY 4 Shami 11/a Chittoor 1 34000 (x) Display the details of all the employees whose name ends with ‘a’ select * from employee where empname like '%a'; E MPNO E MP NAME ADDRE S S DE P TNO S ALARY 3 Asha 18/a Chittoor 2 5000

5 | P a g e Experiment No: 2 Create two tables Student (rollno, sname, dno) Department (dno, dname) With primary key and foreign key relationships and check the integrity constraint. (i) Write a query to display the rollno, sname, dno, and dname for all students. create table department(dno number(2), dname varchar2(20), PRIMARY KEY(dno)); Table created. Insertone by one insert into department values(1,'MATHS'); insert into department values(2,'English'); insert into department values(3,'Computers'); select * from department; DNO DNAME 1 MATHS 2 English 3 Computers create table student(rollno number(10), sname varchar2(20), dno number(2), PRIMARY KEY(rollno), FOREIGN KEY(dno) references department(dno)); Table created. Insertone by one insert into student values(1501,'Hari',1); insert into student values(1601,'Balu',2); insert into student values(1701,'Siraj',3); select * from student; ROLLNO S NAME DNO 1501 Hari 1 1601 Balu 2 1701 Siraj 3

6 | P a g e insert into student values(1801,'Saini',4); ORA-02291: integrity constraint (SYSTEM.SYS_C004050) violated - parent key not found (i) Write a query to display the rollno, sname, dno, and dname for all students. select student.*, department.dname from department, student where department.dno = student.dno; ROLLNO S NAME DNO DNAME 1501 Hari 1 MATHS 1601 Balu 2 English 1701 Siraj 3 Computers

7 | P a g e Experiment No: 3 Demonstrate ALTER TABLE statement to add, delete, or modify columns in an existing table. (First, create a table ‘Products’ with pid, pname attributes – then, add price and company attributes and work with them) Creating Table: create table products(pid number(2), pname varchar2(20)); Table created. Alter Command to ADD: alter table products ADD price number(10); Table altered. Description Command: desc products; Table Column Data Type Length Precisio n PRODUCTS PID Number - 2 PNAME Varchar2 20 - PRICE Number - 10 1 - 3 Alter Command to DROP Column: alter table products DROP COLUMN price; Table dropped. desc products; Table Column Data Type Length Precisio n PRODUCTS PID Number - 2 PNAME Varchar2 20 - 1 - 2

8 | P a g e Alter Command to modify Column: alter table products modify pid varchar2(10); Table altered. desc products; Table Column Data Type Length Precisio n PRODUCTS PID Varchar2 10 - PNAME Varchar2 20 - 1 - 2

9 | P a g e Experiment No: 4 Demonstrate DROP TABLE and TRUNCATE TABLE commands (First, create a table ‘Suppliers’ with sno,sname and location attributes) create table suppliers(sno number(6), sname varchar2(20), location varchar2(30)); Table created. Insertone by one insert into suppliers values(01,'Dany','Chennai'); insert into suppliers values(02,'Kane','Mumbai'); insert into suppliers values(03,'Rahul','Bangalore'); select * from suppliers; S NO S NAME LOCATION 1 Dany Chennai 2 Kane Mumbai 3 Rahul Bangalore desc suppliers; Ta ble Column Da ta Type Le ngth P r e c is ion SUPPLIERS SNO Number - 6 SNAME Varchar2 20 - LOCATION Varchar2 30 - truncate table suppliers; Table truncated. select * from suppliers; no data found desc suppliers; Ta ble Column Da ta Type Le ngth P r e c is ion SUPPLIERS SNO Number - 6 SNAME Varchar2 20 - LOCATION Varchar2 30 - drop table suppliers; Table dropped. select * from suppliers; ORA-00942: table or view does not exist

10 | P a g e Experiment No: 5 Demonstrate the following constraints 1. not null 2. unique 3. check 4. default Create Table Query: create table OFFICE(sno number(3) NOT NULL,id number(3) UNIQUE,name varchar2(20), age number CHECK(age>25), branch varchar2(10) DEFAULT 'CS'); Table created. insert into OFFICE values(1,101,'Siraj',26,'IT'); 1 row(s) inserted. (1) NOT NULL insert into OFFICE values(NULL,102,'Saran',26,'IT'); ORA-01400: cannot insert NULL into ("SYSTEM"."OFFICE"."SNO") (2) UNIQUE insert into OFFICE values(02,102,'Saran',26,'IT'); 1 row(s) inserted. insert into OFFICE values(03,102,'Samad',28,'IT'); ORA-00001: unique constraint (SYSTEM.SYS_C004053) violated select * from OFFICE; S NO I D NAME AGE BRANCH 1 101 Siraj 26 IT 2 102 Saran 26 IT (3) CHECK insert into OFFICE values(03,103,'Samad',24,'DS'); ORA-02290: check constraint (SYSTEM.SYS_C004052) violated

11 | P a g e (4) DEFAULT insert into OFFICE(sno,id,name,age) values(03,103,'Samad',27); 1 row(s) inserted. select * from OFFICE; S NO I D NAME AGE BRANCH 1 101 Siraj 26 IT 2 102 Saran 26 IT 3 103 Samad 27 CS

12 | P a g e Experiment No: 6 Write SQL queries to demonstrate aggregate functions Create Table Query: create table samples(product varchar2(20), price number(5)); Insertone by one insert into samples values('rice',56); insert into samples values('sugar',36); insert into samples values('Salt',18); select * from samples; P RODUCT P RICE rice 56 sugar 36 Salt 18 COUNT Function select COUNT(*) from samples; COUNT(* ) 3 select COUNT(product) from samples; COUNT(P RODUCT) 3 MAX Function select MAX(price) from samples; MAX (P RICE ) 56 MIN Function select MIN(price) from samples; MIN(P RICE ) 18 SUM Function select SUM(price) from samples; S UM(P RICE ) 110

13 | P a g e AVG Function select AVG(price) from samples; AV G(P RICE ) 36.6666666666666666666666666666666666667 STDDEV Function select STDDEV(price) from samples; S TDDE V (P RICE ) 19.0087699058443372313948904094805429921 VARIANCE Function select VARIANCE(price) from samples; V ARIANCE (P RICE ) 361.333333333333333333333333333333333334

14 | P a g e Experiment No: 7 Write SQL queries to demonstrate set operations Create Table Query: create table ibm(harddisk varchar2(20), speed varchar2(10), OS varchar2(30)); Table created. Insertone by one insert into ibm values('20gb','500Mhz','Linux'); 1 row(s) inserted. insert into ibm values('40gb','800Mhz','Windows'); 1 row(s) inserted. insert into ibm values('80gb','1Ghz','Windows'); 1 row(s) inserted. select * from ibm; HARDDIS K S P E E D OS 20gb 500Mhz Linux 40gb 800Mhz Windows 80gb 1Ghz Windows Create Table Query: create table dell(harddisk varchar2(20), speed varchar2(10), OS varchar2(30)); Table created. Insertone by one insert into dell values('20gb','500Mhz','Linux'); 1 row(s) inserted. insert into dell values('40gb','1Ghz','Windows'); 1 row(s) inserted.

15 | P a g e select * from dell; HARDDIS K S P E E D OS 20gb 500Mhz Linux 40gb 1Ghz Windows SET Operations (1)UNION select * from ibm UNION select * from dell; HARDDIS K S P E E D OS 20gb 500Mhz Linux 40gb 1Ghz Windows 40gb 800Mhz Windows 80gb 1Ghz Windows (2)INTERSECT select * from ibm INTERSECT select * from dell; HARDDIS K S P E E D OS 20gb 500Mhz Linux (3)MINUS select * from ibm MINUS select * from dell; HARDDIS K S P E E D OS 40gb 800Mhz Windows 80gb 1Ghz Windows

16 | P a g e Experiment No: 8 Write a PL/SQL programme to find the biggest of two numbers (use ‘if’ ) DECLARE a number; b number; BEGIN a := 100; b := 20; IF a>b THEN dbms_output.put_line(a||' is bigger'); ELSE dbms_output.put_line(b||' is bigger'); END IF; END; OUTPUT: 100 is bigger Statement processed. Experiment No: 9 Write a PL/SQL programme to display all the even numbers between 1 and 20 DECLARE a number; BEGIN a := 1; WHILE a<21 LOOP IF (mod(a,2) = 0) THEN dbms_output.put_line(a); END IF; a := a + 1; END LOOP; END; OUTPUT: 2 4 6 8 10 12 14 16 18 20 Statement processed.

17 | P a g e Experiment No: 10 A Cursor is a temporary work area created in the memory of the system when a DML statement is executed. For Example, if the user wants data from the database, we execute a query in SQL as SELECT eid, ename FROM Employees. A Cursor can hold more than one row. The set of all these rows that the cursor holds is called an active set. But... A Cursor can process only one row at a time. There are four attributes used with Cursors : 1) %FOUND 2) %NOTFOUND 3) %ROWCOUNT 4) %ISOPEN When these attributes are added to the cursor variable, they return some information about the execution of the SQL Statement. 1) %FOUND As the cursor processes one row at a time, if the cursor variable is appended with %FOUND if the row was fetched successfully---It returns True Otherwise ----It returns False Note : Before the first fetch from an open cursor, %FOUND returns NULL 2. %ROWCOUNT: It returns the number of records fetched from the cursor at that particular instance of time. Note : When a cursor is opened, %ROWCOUNT becomes zero . This means before the first fetch from an open cursor, %ROWCOUNT returns 0. 3. %ISOPEN: If the cursor is open-----It returns True Otherwise ----It returns False Two Types of Cursors : 1) Implicit Cursors : Implicit Cursors are created by default by the system when any DML statement gets executed. 2) Explicit Cursors : Explicit cursors must be created by us when we execute a SELECT statement that returns one or more rows. Uses of Cursors: 1. We can perform Row wise validation 2. Operations on each row Write PL/SQL program to display id, name and address of each customer using cursors DECLARE c_id customers.id%type; c_name customers.name%type; c_addr customers.address%type; CURSOR c_customers is SELECT id, name, address FROM customers; BEGIN OPEN c_customers;

18 | P a g e LOOP FETCH c_customers into c_id, c_name, c_addr; EXIT WHEN c_customers%notfound; dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr); END LOOP; CLOSE c_customers; END; / When the above code is executed at the SQL prompt, it produces the following result − 1 Ramesh Ahmedabad 2 Khilan Delhi 3 kaushik Kota 4 Chaitali Mumbai 5 Hardik Bhopal 6 Komal MP PL/SQL procedure successfully completed.

19 | P a g e Experiment No: 11 PROCEDURE A procedure is a module that consists of a group of PL/SQL statements performs a task; it does not return any value. But, values can be passed into the procedure or fetched from the procedure through parameters. Procedures cannot be called directly from SELECT statements. They can be called from another block or through EXECUTE keyword. Creating a Procedure Basic syntax of creating a procedure in PL/SQL: CREATE [OR REPLACE ] PROCEDURE procedure_name (parameter_list) [IS | AS] [declaration statements] BEGIN [execution statements] EXCEPTION [exception handler] END [procedure_name ]; Where, CREATE PROCEDURE instructs the compiler to create new procedure. Keyword 'OR REPLACE' instructs the compiler to replace the existing procedure (if any) with the current one. Procedure name should be unique. If the procedure is standalone then 'AS' will be used. Keyword 'IS' will be used, when the procedure is written in some other blocks. Each parameter can be in either IN, OUT, or INOUT mode. The parameter mode specifies whether a parameter can be read from or write to. o IN represents that value will be passed from outside into the procedure. It is a read-only parameter. It is the default mode of parameter passing. Parameters are passed by reference. o OUT represents that this parameter will be used to return a value outside of the procedure. It is a write-only parameter. One can change its value and reference the value after assigning it. The actual parameter must be variable and it is passed by value. o An INOUT parameter is both readable and writable. The procedure can read and modify it. Standalone or Stored Procedure The procedures that are stored in a database as database object and can be accessible throughout the database. Creating a Standalone Procedure The following example creates a simple procedure that displays the string ‘***Welcome to the AP CCE LMS***’ on the screen when executed. CREATE OR REPLACE PROCEDURE greetings AS BEGIN DBM S_OUTPUT.PUT_LINE(‘***Welcome to the AP CCE LMS***’); END; / When above code is executed using SQL prompt, it will produce the following result: Procedure created.

20 | P a g e Executing a Standalone Procedure A standalone procedure can be called in two ways: EXECUTE keyword The above procedure named 'greetings' can be called with the EXECUTE keyword as: EXECUTE greetings; The above call would display: ‘***Welcome to the AP CCE LMS***’ PL/SQL procedure successfully completed. Write a PL/SQL program to to create an Oracle procedure that takes the name as input and prints the welcome message as output. We are going to use EXECUTE command to call procedure. CREATE OR REPLACE PROCEDURE welcome_msg (p_name IN VARCHAR2) IS BEGIN dbms_output.put_line (‘Welcome '|| p_name); END; / EXECUTING PROCEDURE: EXECUTE welcome_msg (‘Guru99’); OUTPUT: Welcome Guru99

21 | P a g e Experiment No: 12 Functions is a standalone PL/SQL subprogram. Like PL/SQL procedure, functions have a unique name by which it can be referred. These are stored as PL/SQL database objects. Procedure Vs. Function: Key Differences Procedure Function  Used mainly to a execute certain process  Used mainly to perform some calculation  Cannot call in SELECT statement  A Function that contains no DML statements can be called in SELECT statement  Use OUT parameter to return the value  Use RETURN to return the value  It is not mandatory to return the value  It is mandatory to return the value  RETURN will simply exit the control from subprogram.  RETURN will exit the control from subprogram and also returns the value  Return datatype will not be specified at the time of creation  Return datatype is mandatory at the time of creation Write a PL/SQL programme to demonstrate functions. CREATE OR REPLACE FUNCTION totalcustomers RETURN NUMBER IS total NUMBER(2) := 0; BEGIN SELECT COUNT(*) INTO total FROM emp1; RETURN total; END; O/P: Function created. To use Function: DECLARE c NUMBER(2); BEGIN c := totalcustomers(); dbms_output.put_line('Total number of customers:'||c); END; O/P: Total number of customers:4 Statement processed.

22 | P a g e Experiment No: 13 Triggersinoracle are blocksof PL/SQL code whichoracle engine canexecute automaticallybasedon some actionor event. Triggersare automaticallyandrepeatedlycalleduponbyoracle engineonsatisfyingcertaincondition. If triggersare activatedthentheyare executedimplicitlybyoracle engine. Wheneveratriggeriscreated,itcontainsthe followingthreesequential parts: 1. TriggeringEventorStatement:The statementsdue towhichatriggeroccurs. 2. TriggerRestriction:The conditionoranylimitationappliedonthe trigger.If conditionisTRUE thentriggerfires.Notall triggershasconditions. 3. TriggerAction:The bodycontainingthe executablestatementsthatistobe executedwhen triggerfires. Typesof Triggers 1) Level Triggers a) ROW LEVEL TRIGGERS: It firesforeveryrecordthatgot affected.Italwaysuse a FOREACH ROW clause b) STATEMENT LEVEL TRIGGERS: It firesonce foreach statementthatisexecuted. 2) EventTriggers a) DDL EVENTTRIGGER: It fireswiththe executionof everyDDLstatement(CREATE,ALTER, DROP,TRUNCATE). b) DML EVENT TRIGGER: It fireswiththe executionof everyDMLstatement(INSERT,UPDATE, DELETE). c) DATABASEEVENTTRIGGER: It fireswiththe executionof everydatabase operationwhichcan be LOGON, LOGOFF,SHUTDOWN, SERVERERRORetc. 3) TimingTriggers a) BEFORE TRIGGER: It firesbefore executingDMLstatement. b) AFTER TRIGGER: ItfiresafterexecutingDMLstatement. Example :Create a triggerthat firesafterinsertof everynew row inStu table.The trigger automaticallyupdatestotal. create table Stu(id int, subj1 int,subj2 int,subj3 int,total int); Table created. Insert values insert into Stu(id,subj1,subj2,subj3) values(1,30,25,62); ID SUBJ1 SUBJ2 SUBJ3 1 30 25 62 Output: select * from Stu; ID SUBJ1 SUBJ2 SUBJ3 TOTAL 1 30 25 62

23 | P a g e Create Trigger Command: create or replace trigger stud_marks after INSERT on Stu for each row begin update Stu set total = subj1 + subj2 + subj3; end; / Output: select * from Stu; ID SUBJ1 SUBJ2 SUBJ3 TOTAL 1 30 25 62 117

Sql lab experiments

More Related Content

What's hot

Similar to Sql lab experiments

More from LakshmiSarvani6

Recently uploaded

Sql lab experiments