RDBMS

 

		1.CREATE DEPARTMENT TABLE
		create table department(
		department_id number,
		department_name varchar(30),
		department_block_number number,
		constraint pk primary key(department_id)
		); There are more available on https://play.google.com/store/apps/details?id=com.master.askmastermaterial
		2.CREATE STUDENT TABLE
		create table student(
		student_id number,
		student_name varchar(30),
		address varchar(40),
		city varchar(30),
		department_id number,
		constraint pk primary key(student_id),
		constraint fk foreign key(department_id) references Department(department_id)
		);
		3.CREATE MARK TABLE
		create table mark(
		value number,
		subject_id number,
		student_id number,
		constraint pk primary key(subject_id,student_id),
		constraint fk foreign key(subject_id) references Subject(subject_id),
		foreign key(student_id) references student(student_id)
		);
		4.CREATE SUBJECT TABLE
		create table subject(
		subject_id number,
		subject_name varchar(30),
		subject_code varchar(10),
		staff_id number,
		constraint pk primary key(subject_id),
		foreign key(staff_id) references staff(staff_id)
		);
		5.ADD A COLUMN IN STUDENT TABLE
		alter table student add age number(2);
		6.ADD A CONSRAINT TO COURSE TABLE
		alter table course
		add constraint chk_fees check(fees>0);
		7.ADD A COLUMN IN REGISTRATION TABLE
		alter table registration
		add DOC DATE;
		alter table registration
		add constraint check_date check(doc>doj)
		8.DROP REGISTRATION TABLE
		drop table registration;
		9.DROP STUDENT TABLE
		drop table registration;
		drop table student;
		10.TEST YOUR UNDERSTANDING 1
		create TABLE product
		( prod_id number(4),
		prod_name varchar2(25),
		prod_expiry_date date not null,
		primary key(prod_id)
		);
		11.TEST YOUR UNDERSTANDING 2
		CREATE table department
		( dept_id number(4),
		prod_id number(4),
		dept_name varchar(25) unique,
		dept_head varchar2(25) NOT NULL,
		PRIMARY KEY(dept_id),
		foreign key(prod_id) REFERENCES product(prod_id)
		);
		#DATA MANIPULATION LANGUAGE
		1.INSERT RECORDS-DEPARTMENT
		insert into department values(1, 'CSE', 3);
		insert into department values(2,'IT', 3);
		insert into department values(3,'SE',3);
		2.INSERT RECORDS-STUDENT
		insert into student values(1,'Anandhi','LMC','Coimbatore',1);
		insert into student values(2,'Anitha','ABC','Salem',2);
		3.INSERT RECORDS-STAFF
		insert into staff values(3,'Senthil',2);
		insert into staff values(4,'Santhya',2);
		insert into staff values(5,'Geetha',3);
		4.INSERT RECORDS SUBJECT
		insert into subject values(1,'English','E1',2);
		insert into subject values(2,'Maths','M1',4);
		insert into subject values(3,'Physics','P1',1);

		5.INSERT RECORDS-MARKS
		insert into mark values(98,1,1);
		insert into mark values(88,2,1);
		insert into mark values(78,3,1);
		6.INSERT RECORDS INTO COURSE TABLE
		insert into course values(1001,'Java',4,5000);
		insert into course values(1002,'C++',2,4000);
		insert into course values(1003,'Linux and C',3,4000);
		insert into course values(1004,'Oracle',2,3000);
		insert into course values(1005,'CSharpe',6,10000);
		7.UPDATE FEES FOR SHORT COURSES
		update course set fees=fees+(-500)
		where duration<=3;
		8.QUPDATE ADDRESS OF A STUDENT
		update student set student.street='Andheri West', student.city='Mumbai'
		where student.firstname='Abdul' And student.lastname='Rahman';
		9.REMOVE REGISTRATION DETAILS
		delete from registration where
		DOJ='25-May-18';
		10.REMOVE COURSE DETAILS
		delete from registration;
		delete from course where
		duration <= 3;
		#SELECT STATEMENT
		1.LIST DEPARTMENT NAMES
		select department_name from department order by department_name;
		2.LIST OF STUDENT NAMES
		select student_name from student where student_id!=4 and student_id!=7 order by student_name;
		3.DEPARTMENT NAME BASED ON BLOCK NUMBER
		select department_name from department where department_block_number=3 order by
		department_name;
		4.DISPLAY STUDENT DETAILS
		select student_name from student order by student_name;
		5.STUDENTS NAME STARTS WITH A
		select student_name from student where student_name like 'A%' order by student_name;
		6.STUDENT NAME BASED ON START AND ENDING CHARACTER
		select student_name from student where student_name like 'A%a' order by student_name;
		7.STUDENT DETAILS BASED ON CITY
		select studid,firstname from student where
		city='Coimbatore' and dob='08-Oct-97' or
		city='Chennai' and dob='08-Oct-97'
		order by studid;
		8.DISPLAY STUDENT LOCATION
		select distinct city from student order by city;
		9.STUDENT NAME BASED ON CONDITION
		select firstname from student where firstname like 'S%n' order by firstname;
		10.STUDENT DETAILS BASED ON LOCATION AND DATE OF BIRTH
		select studid,firstname from student where
		city='Coimbatore' and dob='08-Oct-97' or
		city='Chennai' and dob='08-Oct-97'
		order by studid;
		11.STUDENT ID BASED ON COURSE
		select distinct studid from registration where courseid in (1001,1005) order by studid;
		#FUNCTION-SCALAR&AGGREGATE
		1.LIST DEPARTMENT NAME
		select lower(department_name) as dept_name from department
		order by dept_name;
		2.STUDENT ADDRESS
		3.CONCATINATING DETAILS
		select (address || ', ' || city) as Address from student
		order by Address desc;
		4.LENGTH OF STUDENT NAME
		select student_name from student where length(student_name)=6
		order by student_name;
		5.NUMBER OF DEPARTMENTS
		select department_block_number,count(department_id) as NO_OF_DEPT from department
		group by department_block_number
		order by NO_OF_DEPT desc;
		6.STUDENT COUNT
		select count(student_name) as STUD_COUNT from student;
		7.AVERAGE MARK
		1.select max(round(avg(value),2)) as avg_mark from mark
		group by student_id;

		2. select max(round(avg(value),2)) as avg_mark from mark
		group by student_id;
		8.MINIMUM MARK
		select student_id,min(value) as minimum_mark from mark
		group by student_id
		order by minimum_mark;
		9,NUMBER OF BUSES
		select type as bus_type,
		count(avail_seats) as bus_count
		from buses
		group by type
		order by bus_count desc;
		10.AVERAGE MARK GREATER THAN 80
		1.select student_id , round(avg(value),2) as avg_mark
		from mark
		group by student_id
		having avg(value) > 80
		order by avg_mark;
		2. select student_id , round(avg(value),2) as avg_mark
		from mark
		group by student_id
		having avg(value) > 80
		order by avg_mark;
		11.STUDENT NAME
		select (firstname || ' ' || lastname) as STUDENTNAME
		from student
		order by STUDENTNAME;
		12.STUDENT ID BASED ON JOINING MONTH
		13.DISPLAY STUDENT NAME ALONG WITH AGE
		select studid,firstname,trunc(months_between(sysdate,dob)/12) as age from student
		order by studid;
		14.LATEST DATE OF REGISTRATION
		select max(doj) as LATESTDATE from registration;
		15.NUMBER OF COURSES REGISTERD
		#JOINS&SUBQUERY
		1.SUBJECT WITH STAFF DETAILS
		select subject_name,subject_code,staff_name from subject s
		inner join staff f
		on s.staff_id=f.staff_id
		order by subject_code;
		2.STUDENT AND THEIR DEPARTMENT BASED ON CITY
		select student_name,department_name from department
		inner join student
		on student.department_id=department.department_id
		where lower(city)='coimbatore'
		order by student_name;
		3.STUDENT NAME BASED ON DEPARTMENT
		select student_name from student
		where department_id in(select department_id from department where lower(department_name)='it')
		order by student_name desc;
		4.DEPARTMENT WITH STUDENT COUNT
		select department_name,count(student_name) as student_count
		from department inner join student on student.department_id=department.department_id
		group by department_name
		order by department_name ;
		5.DEPARTMENT WITH STAFF COUNT
		select d.department_name,count(s.staff_id) as staff_count
		from department d ,staff s
		where d.department_id=s.department_id(+)
		group by department_name
		order by department_name;
		6.DEPARTMENT HAS MAXIMUM STAFF
		select department_name from department where department_id in
		(select department_id from staff
		having count(department_id) in
		(select max(count(department_id)) from staff
		group by department_id)
		group by department_id)
		order by department_name;
		7.DEPARTMENT HAS LEAST STUDENT COUNT
		select department_name from department where department_id in
		(select department_id from student having count(department_id) in
		(select min(count(department_id)) from student
		group by department_id)
		group by department_id)
		order by department_name;
		8.STUDENT WITH MINIMUM MARK
		select subject_name,min(value) as min_mark from subject
		join mark using(subject_id)
		where value in(select min(value) from subject )
		group by subject_name
		order by subject_name desc;
		9.STUDENT MARK IN PARTICULAR SUBJECT
		select student_name,value from mark

		join student using(student_id)
		join subject using(subject_id)
		where lower(subject_name)='theory of computation'
		order by student_name;
		10.STUDENT NAME WITH THEIR HIGHEST MARK
		select student_name,max(value) as max_mark from student
		join mark using(student_id)
		group by student_name
		order by student_name;
		11.MAXIMUM MARK IN SUBJECT WITH STAFF NAME
		select staff_name,subject_name,max(value) as max_mark from subject
		join staff using(staff_id)
		join mark using(subject_id)
		group by staff_name,subject_name
		order by max_mark desc;
		12.BLOCK NUMBER BASED ON MAXIMUM DEPARTMENT LOCATED
		select department_block_number from department
		where department_block_number in(select max(department_block_number) from department
		)
		group by department_block_number;
		13.STUDENT NAME LIST BASED ON DEPARTMENT NAME
		select student_name from student where department_id in
		(select department_id from department where lower(department_name)='cse')
		group by student_name
		order by student_name desc;
		14.DEPARTMENT NAME WITH MAXIMUM STUDENT COUNT
		select department_name from department natural join student
		group by department_name
		having count(student_id)=(
		select max(stu_count) from
		(
		select department_name, count(student_id) as stu_count
		from department natural join student
		group by department_name
		)
		);
		15.STAFF BASED ON DEPARTMENT
		select staff_name from staff where department_id in
		(select department_id from department where lower(department_name)='it')
		order by staff_name;
		16.STAFF DETAILS BASED ON SUBJECTS
		select staff_name from staff where staff_id in
		(select staff_id from subject)
		order by staff_name;
		17.STAFF DETAILS WITHOUT HANDLING ANY SUBJECTS
		select staff_name from staff where staff_id not in
		(select staff_id from subject)
		order by staff_name;
		18.STUDENT DETAILS BASED MARK SCORED
		select student_name from student
		join mark using(student_id)
		group by student_name
		having min(value)>50
		order by student_name;
		19.MAXIMUM SCORE IN THE PARTICULAR SUBJECT
		select student_name from student
		join mark using(student_id)
		join subject using(subject_id)
		where subject_name ='Software Engineering'
		and value=(select max(value)
		from mark join subject using (subject_id)
		where subject_name ='Software Engineering')
		order by student_name;
		20.STUDENT DETAILS WITH SUBJECT NAME
		select subject_name,student_name
		from student s
		inner join mark m on s.student_id=m.student_id
		inner join subject su on m.subject_id=su.subject_id
		inner join (select subject_id,
		max(value) as maximum
		from mark ma group by subject_id)
		highmarks on highmarks.subject_id=m.subject_id
		and highmarks.maximum=m.value
		order by subject_name, student_name;
		21.STUDENT NAME WITH COMPUTER PROGRAMMING MARKS
		select student_name,value as CP_MARKS from mark
		join student using(student_id)
		join subject using(subject_id)
		where lower(subject_name)='computer programming'
		order by value , student_name desc;
		22.TOP PERFORMER IN SOFTWARE ENGINNERING
		select student_name,department_name,max(value) as value
		from mark
		inner join student on student.student_id=mark.student_id
		inner join subject on subject.subject_id=mark.subject_id
		inner join department on department.department_id=student.department_id
		where value in(select max(value) from mark
		inner join subject on subject.subject_id=mark.subject_id

		where
		lower(subject_name)='software engineering') and
		lower(subject_name)='software engineering'
		group by student_name,department_name
		order by department_name ;
		23.SECOND MAXIMUM FEES
		24.STUDENT NAME AND COURSE NAME
		25.STUDENT NAME ALONE WITH DOJ AND DOC
		26.SUDENT ID WITH TOTAL FEES
		27.TOTAL FEE COLLECTED FEOM EACH COURSE
		28.COUSE NAME BASED ON NUMBER OF STUDENTS REGISTERED
		#DATA CONTROL LANGUAGE&DATABASE OBJECTS
		1.CREATE VIEW_MOBILE
		1.STUDENT ADDRESS
		select student_id,address
		from student
		where initcap(student_name)='David';
		2.STUDENT NAME AND COURSE NAME
		select firstname,coursename
		from student s
		inner join registration r on s.studid=r.studid
		inner join course c on r.courseid=c.courseid
		order by firstname asc,coursename asc;
		3.TOTAL FEE COLLECTED FOM EACH COURSE
		select c.courseid,c.coursename,(count(r.studid)*c.fees)as TOTALFEES
		from course c inner join registration r
		on c.courseid=r.courseid
		group by c.courseid,
		c.coursename,
		c.fees
		order by c.courseid;
		4.SECOND MAXIMUM FEES
		select courseid,coursename
		from course
		where fees=(select max(fees) from course where fees<(select max(fees) from course)
		)
		order by courseid;
		5.COURSE NAME BASED ON STUDENT REG
		select c.coursename
		from course c
		inner join registration r
		on r.courseid=c.courseid
		group by c.coursename
		having count(c.courseid)>=2
		order by coursename;
		6.STUDENT ID BASED ON JOINING MONTH
		select distinct studid
		from registration
		where to_char(doj,'MM')=6
		order by studid;
		7.STUDENT NAME ALONG WITH DOJ AND DOC
		select s.firstname,
		r.doj,
		add_months(r.doj,c.duration)as doc
		from registration r
		inner join student s on s.studid=r.studid
		inner join course c on c.courseid=r.courseid
		order by firstname,doj,doc;
		8.STUDENT DETAILS BASED ON CITY
		select student_id,student_name
		from student
		where (city='Coimbatore')
		order by student_name asc;
		9.NUMBER OF COURSES REGISTERED
		select s1.studid,count(c1.courseid) as NOOFCOURSES
		from student s1
		join registration r1
		on r1.studid = s1.studid
		join course c1
		on r1.courseid = c1.courseid
		group by s1.studid
		order by
		count(c1.courseid) desc,
		s1.studid asc;
		10.CREATE VIEW MOBILE
		create view customer_mobile_details
		as
		select c.customer_id,c.customer_name,c.mobile,s.salesid,s.net_amount,
		m.model_name,m.manufacturer
		from sales_info s
		inner join customer_info c
		on s.customer_id=c.customer_id
		inner join mobile_master m
		on s.ime_no=m.ime_no
		order by customer_id,customer_name,salesid;