EXPERIMENT :- 1

INTRODUCTION ABOUT SQL SQL Is Used To Communicate With A Data Base. According To ANSI, it Is The Standard Language For Relational Database Management System.

COMPONENTS OF SQL 1. 2. 3. 4. Data Definition Language(DDL) Data Manipulation Language(DML) Transactional Control language(TCL) Embedded DML(Is Used With In C, Java).

DATA TYPES IN SQL i. ii. iii. iv. v. vi. vii. Char(n) Varchar(n) Int Numeric(p,d) Real Date Time

EXPERIMENT:- 2
WORKING WITH DDL The DDL Commands Are i. ii. iii. iv. v. Create Alter Drop Rename Truncate

CREATE COMMAND:TO CREATE THE STUDENT TABLE 1. Create Table Students(S_Id Varchar2(5), S_Name Char(10), Dept_Name Char(5), Add Varchar2(20));

2. To Duplicate Students Table As Students1 Create Table Students1 As Select * From Students; 3. Create A Table With Few Columns From Another Table Create Table Stdents1 As Select S_Id, S_Name From Students; 4. Create A Table Employee From Student Select All Field , Rename S_Id With Emp_Id And S_Name With Employee_Name. Create Table Employee (Emp_Id ,Emp_Name, Address, City) As Select S_Id ,S_Name , Address, City

From Student; 2. THE ALTER COMMAND I. Adding New Columns Alter Table Employee Add(Pincode Number(5));

Ii. Modifying Existing Columns Alter Table Employee Modify(Pincodde Number(6)); Iii. The Drop Command 1 Drop Table Employee; 2 Drop Database Company; Iv. The Rename Command Rename<Table>To<Newtablename> Example:Rename Employee To Employees; V .The Truncate Command Truncate Table Employee;

EXPERIMENT:- 3
1. Select Command a. Select Name,Branch From Student; b. Select * From Student Where City =Jaipur; 2. Insert Command a. Insert Into Student(Student_Id,Name,City) Values(Sool,Rohit,Jaipur); Output:1 Row Inserted. b. Inserted Into Student Values(Sool,Null,Jaiput); Output:1 Row Inserted. 3. Delete a. Delete From Student; b. Delete From Student Where Name =Sumit; c. Delete From Employee Where Salary<2000; 4. Update a. Employee Pankaj Update Employee Set Dept_Id=5 Where Name=Pankaj;

EXPERIMENT:- 4
Working on TCL 1. Commit Insert Into Employee(EID,Name) Values(Elol,John); Commit; Output:Commit completed. 2. Rollback Insert Into Employee (EID,Name) Values(Mlol,John); Rollback; 3. Savepoint Insert Into Employee (EID,Name) Values(Mlol,John); Savepoint Emp1; Insert Into Employee (EID,Name) Values(Mlol,Adam); ROLLBACK TO SAVEPOINT Emp1;

EXPERIMENT:- 5
Working On Queries & Sub Sueries Q.1 Find All The Name Of Employee Where Employee Id =100. Sol. Select Name From Employee Where EID=100 Output:Name Sanjay Tushar Sudhir Q.2 Retrive All Information Of Employee Whose Salary Is Greater Than 30,000. Sol. Select * From Employee Where Salary>30,000. Output:Name Tushar Dept Ee Address Rewari

Q.3 Retrive Name And Salary Of Employees Whose Salary Between 40,000&80,000. Sol. Select Name,Salary From Employee Where Salary Between 40,000and 80,000; Output:Name Shakti Salary 50,0000

Q.4 Retrive A List Of Employee And The Projects They Are Working On Order The Result By Projects Name Within Each Project ,Order By Employee,S Name Sol. Select E.Name,P.Name From Employee E,Project P,Join J Where Code=Pcode Ande.Eid=J.Lld Order By P.Name,E.Name; Q.5 Find The Number Of Employee Works For Department Human Resource. Sol. Select Count(*) From Employee E, Department D Where E.Deptid=D.Deptid And Dname=Hr;

EXPERIMENT :- 6
CONDITIONAL Q.1 Find All Employee With Null Values For Address Sol. Select Name Form Emplouee Where Adress Is Null ; Q.2 Retrive The Identification number ,name ,address of employee who works for department number(1,2,3); Sol. Select eId ,name ,address From employee Where deptidin(1,2,3) Output:EID 101 name address

tushar rewari

Q. 3 find subject id all subject being take by tushar Sol. Select subject_id Form takes Where student_id in (select student_id From student Where student_name=tushar); Output:Subject_id 10

20 30 Q.4 COUNT THE NUMBER OF DISTRICT SALARY VALUES IN THE DATABASE Sol. Select count(DISTINCT salary) From employee;

EXPERIMENT:- 7
Q.1 Retrive The Identification number ,name of employee who have the higest salary in company. Sol. Select E_ID,name From employee Where salary>=all(select salary from employee); Output:E_id name 101 shakti

Q.2 find the student_id of students enrolled in java01 or unix02 or both Sol. Select student_id From enrolment Where subject_id=java01 UNION Select student_id From enrolment Where subject_id=unix02. Q.3 find the identification number of employee who works in project number1 but not in project 2. Sol. (select Eid from employee e,john j Where e.id =j.eid and pcode=1) Except (select e_id,from employee e,john j Where e.Eid=j.Eid and P code=2);

EXPERIMENT:- 8
CONSIDER THE EMPLOYEE DATABASE Employee(employee_name,street,city) Works(employee_name,company_name,salary) Company(company_name,city) Manages(employee_name,manager.name)

Q.1 find the names ,street address and cities of employees Who works for the tcs and eran more than 20,000; Sol.select * From employee Where employee_name in (select employee_name From works Where company_name=tcs And Salary=20,000

Q.2 find those companies whose employee earn a higher salary on average salary at tcs. Sol. Select company_name From works Group by company_name Having avg(salary)>(select AVG(salary) From works Where company name=TCS);

EXPERIMENT:- 9
CONSIDER THE FOLLOWING SCHEME. Supplier(supplier_id, s_name,s_address) Parts(parts_id,Parts_name,color) Catalog(supplier_id,part_id,cost)

Q.1 find the name of all the suppliers who supply yellow parts. Sol. Select supplier_name From supplier Where supplier_id IN (select supplier_id From catalog Where part_id IN Select part_id From Parts Where color=yellow)); Output:Supplier _name John Smith

Q.2 find the name of the supplier who supply all parts. Sol. Select supplier_name Fromsupplier

Where supplier_id IN ( select DISINCT supplier_id From catalog Where part_id IN (select part_id From parts)); Output:Supplier _name Shakti Tushar.

EXPERIMENT :- 10
WORKINGON VIEW View is a virtual table it is a database object. Q.1 create view Create view emp_view As Select * from omp Where dept no=30; Output:View created.

Q.2 select only the employee name,salary and the commission of all the employee from the emp table. Sol. Create view emp_salview(emp_name,salary,commission) As Select ename,sal,comm From emp; Deleting views Syntay DROP VIEW<view_name>; DROP VIEW emp_view;

EXPERIMENT:- 11
WORKING ON INDEX CREATE INDEX emp_last_name_idx On empolyees(last_name); Output:Index created

FUNCTION BASED INDEX Great index upper_dept_name_idx On departments(UPPER(DEPARTMENT_NAME)); OUTPUT:index created

select * from departments where upper(department_name)=sales;

REMOVING INDEX DROP INDEX upper_last_name_idx; Output:Index droped. Conforming index We can confirm oxistence of index from the USER INDEX data dictionary view we can also check the column involve in an index by owerying USER_IND_COLUMNS view.

EXPERIMENT:- 12
WORKING ON SYNONYMS Creating a synontm Syntax Create [PUBLIC]synonym synonym For object;

Example:Create synonym dept For dept_detail_information;

Output:Sunonym created

Drop/remove a system Deop synonym dept; Output:Synonym deopped

EXPERIMENT:- 13
WORKING ON SEQUENCE A sequence is a user created database object that can be shared by multiple user to generate unique numbers A sequence automatically generates unique numbers. Q.1 define the sequence to generate sequential numbers automatically. Sol. Create sequence sequence [INCREMENT BY n] [START WITH n] [{MAXVALUE n / NOMAXVALUE}] [{MINVALUE n / NOMINVALUE}] [{CYCLE/NOCYCLE}] [{CACHE N/NOCACHE}];

Q.2 creating a sequence Sol. CREATE SEQUENCE dept_dep_id_seq INCREMENT BY 10 START WITH 120 VALUE 9999 NOCACHE NOCYCLE; Output:Sequence created Q.3 conforming sequence

Sol. SELECT seaquence_name,min_value,Max_value, Increment_by,lasr_number From user_ seaquences;

NOTE:USER_SEQUENCESis a data dictionary table. Since a sequence is a database object, we can identify it in the USER_OBJECTS data dictionary table

A LAB REPORT FILE SUBMITTED To

Rajasthan Technical University

ACADEMIC SESSION 2011 2012 Submitted To: Submitted By:

DEPARTMENT OF ELECTRICAL ENGINEERING

LAXMI DEVI INSTITUTE OF ENGINEERING & TECHNOLOGY