SET-1
1] Create the Simple DEPARTMENT Table.
CREATE TABLE DEPARTMENT ( dept_no INT PRIMARY KEY, dept_name varchar(10), location varchar(10) ); OUTPUT:-
2] Display structure of department table.
describe department; OUTPUT:-
3] Insert below records into Department Table
1.INSERT INTO department values(10,'account','NY'); 2.INSERT INTO department values(20,'HR','NY'); 3.INSERT INTO department values(30,'Production','DL'); 4.INSERT INTO department values(40,'Sales','NY'); 5.INSERT INTO department values(50,'EDP','MU'); 6.INSERT INTO department values (60,'TRG',' '); 7.INSERT INTO department values(110,'RND','AH');
4] Display all records of Department table
select * from department; OUTPUT:-
5] Display all department belonging to location 'NY'
select * from department where location='NY'; OUTPUT:-
6] Display details of Department 10
select * from department where dept_no=10; OUTPUT:-
7] List all department names starting with ‘a’
select * from department where dept_name like 'a%';
8]List all departments whose number is between 1 and 100
select * from department where dept_no between 1 and 100; OUTPUT:-
9] Delete 'TRG' department
delete from department where dept_name='TRG'; OUTPUT:-
10] Change department name 'EDP' to 'IT
update department set dept_name='IT' where dept_name='EDP'; OUTPUT:-
SET-2
11] Create the EMP Table with all necessary constraints such as In EMP TABLE: Employee id should be primary key, Department no should be Foreign key, employee age (birth_date) should be greater than 18 years, salary should be greater than zero, email should have (@ and dot) sign in address, designation of employee can be “manager”, “clerk”, “leader”, “analyst”, “designer”, “coder”, “tester”. create table employee ( emp_id int primary key, emp_name varchar2(20), birth_date date, gender varchar2(6), DEPT_NO number(38) REFERENCES department(DEPT_NO), address varchar2(100), designation varchar2(15), salary decimal(9,2), experience varchar2(5), email varchar2(255) CONSTRAINT chk_emp_email CHECK (REGEXP_LIKE (email,'^[A-Za-z0-9._%+]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}$')) );
1) Create DEPT table with neccessary constraint such as Department no should be primary key, department name should be unique
alter table employee add constraint ck_designation check (designation in ('manager', 'clerk', 'leader', 'analyst', 'designer', 'coder', 'tester')); OUTPUT:-
12]After creation of above tables, modify Employee table by adding the constraints as ‘Male’ or ‘Female’ in gender field and display the structure.
ALTER TABLE employee ADD CONSTRAINT gender_check CHECK(gender IN('male','female')); OUTPUT:-
13] Insert proper data (at least 5 appropriate records) in all the tables.
1. INSERT INTO employee values(1,pc,'29-SEP97','male',10,AMRELI,'manager',20000,3,'pc@gmail.com');
2. INSERT INTO employee values(2,'naresh','31-
oct98','male',20,'ahemdabad','clerk',20000,1,'vj@gmail.com');
3. INSERT INTO employee values(3,'jay','15-jan98','male',30,'vyara','leader',5000,1,'df9j@gmail.com');
4. INSERT INTO employee values(4,'annu','10-jan95','female',40,'vyara','analyst',50000,3,'annulf9j@gmail.com');
5. INSERT INTO employee values(5,'prem','10-jan96','male',50,'amrely','designer',4000,3,'prem@gmail.com');
6. INSERT INTO employee values(6,'jay','15-mar99','male',60,'songadh','coder',34000,5,'jay@gmail.com');
7. INSERT INTO employee values(7,'anil','30-mar87','male',110,'bardoli','tester',54000,6,'anil@gmail.com');
14] Describe the structure of table created
describe employee;
15] List all records of each table in ascending order.
select * from employee order by emp_name asc; OUTPUT:-
16]Delete the department whose location is Ahmedabad.
delete from department where location='AH';
17] Display female employee list
select * from employee where gender='female'; OUTPUT:-
18] Display Depart name wise employee Names
select d.dept_name, e.emp_name from employee e, department d where e.dept_no=d.dept_no order by d.dept_name; OUTPUT:-
19] Find the names of the employee who has salary less than 5000 and greater than 2000.
select * from employee where salary <5000 and salary >2000; OUTPUT:-
20]Display the names and the designation of all female employee in descending order.
select emp_name,designation,gender from employee where gender='female'order by emp_name desc; OUTPUT:-
21] Display the names of all the employees who names starts with ‘A’ ends with ‘A’.
select * from employee where emp_name LIKE 'A%a'; OUTPUT:-
22] Find the name of employee and salary for those who had obtain minimum salary.
-> select emp_name, salary from employee where salary=(select min(salary)from employee); OUTPUT:-
23] Add 10% raise in salary of all employees whose department is ‘IT’.
update employee set salary=salary+(salary*0.10)where dept_no=(select dept_no from department where dept_name='IT'); OUTPUT:-
24] Count total number of employees of ‘IT’ department.
select count(dept_no) from employee where dept_no=(select dept_no from department where dept_name='IT'); OUTPUT:-
25] List all employees who born in the current month. select emp_name from employee where to_char(birth_date,'MON')=to_char(sysdate,'MON'); OUTPUT:-
26] Print the record of employee and dept table as “Employee works in department ‘MBA’.
select emp_name||'works in department', dept_name from employee, department; OUTPUT:-
27] List names of employees who are fresher’s (less than 1 year of experience).
select emp_name from employee where experience <1; OUTPUT:-
28] List department wise names of employees who has more than 5 years of experience.
select d.dept_name, e.emp_name from employee e, department d where experience >5 ORDER BY d.dept_name ASC; OUTPUT:-
29] Crete Sequence to generate department ID CREATE SEQUENCE sequence_1
start with 1 increment by 1 minvalue 0 maxvalue 100 cycle; OUTPUT:-
30] List department having no employees select d.dept_name from department d WHERE NOT EXISTS(select * from employee e where e.dept_no=d.dept_no); OUTPUT:-
SET-3
1] Create the above three tables along with key constraints. create table stud ( rollno number(10) primary key, name varchar2(10) NOT NULL, class varchar2(10) NOT NULL, bod date ); create table course ( courseno number(10) primary key, coursename varchar(10) NOT NULL, max_marks number(4) NOT NULL, pass_marks number(4) NOT NULL ); create table sc ( rollno number(10) references stud(rollno), courseno number(10) references course(courseno), marks varchar(10) NOT NULL ); OUTPUT:-
2] Write an Insert script for insertion of rows with substitution variables and insertappropriate data.
1. insert into stud values(&rollno,'&name','&class','&bod');
2. insert into course values(&courseno,'&coursename',&max_mark,&min_max);
3. insert into sc values(&rollno,&courseno,'&marks');
3] Add a constraint that the marks entered should strictly be between 0 and 100.
alter table sc add constraint chk_mrsk check(marks between 0 and 100);
4] While creating SC table, composite key constraint was forgotten. Add the composite keynow. alter table sc add primary key(rollno,courseno);
5] Display details of student who takes ‘Database Management System’ course.
1. select a.*,b.*,c.* from stud a , course b , sc c where b.coursename='dbms' and c.courseno=b.courseno and c.rollno=a.rollno;
2. select a.name,b.*,c.* from stud a , course b , sc c where b.coursename='dbms' and c.courseno=b.courseno and c.rollno=a.rollno;
6] Display the names of students who have scored more than 70% in Computer Networksand have not failed in any subject.
select a.*,b.*,c.* from stud a , course b,sc c where c.marks>70 and b.coursename='dbms'and c.rollno in(select rollno from sc where rollno not in(select rollno from sc,course where marks<course.pass_marks))and a.rollno=c.rollno;
7]Display the average marks obtained by each student. select avg(marks) from stud a,sc c where c.rollno=a.rollno group by c.rollno;
8]Select all courses where passing marks are more than 30% of average maximum mark.
DBMS LABMANUAL
Created BY :- MR.PC
Good Job..
ReplyDeleteSuch a Timely taken content. I really feel that it is the best Content for your knowledge, If you want to more learn React Native Traininig in noida.
ReplyDelete