Name:- Daksh Goswami
Roll No:-BCA22053
Class:-Division 1
ASSIGNMENT:-1
1. Consider the employee data. Give an expression in SQL for the
12 following query. Teacher (Tech_code, tname, join_date,
salary, city) Subject (Sub_Code, Sub_name, Tech_code).
Que 1:- Create tables and give proper constraints.
Ans:-
create table teacher (t_code int primary key,t_name
varchar2(20) not null,join_date date,salary int not null,city
varchar2(20) not null);
1. insert into teacher (t_code,t_name,join_date,salary,city)
values(101,'Kavita',to_date('01/01/2022','dd/mm/yyyy'),25000,'Surat');
2. insert into teacher (t_code,t_name,join_date,salary,city)
values(102,'Daksh',to_date('29/06/2022','dd/mm/yyyy'),45000,'Vapi');
3. insert into teacher (t_code,t_name,join_date,salary,city)
values(103,'Yashvi',to_date('30/06/2022','dd/mm/yyyy'),35000,'Valsad');
4. insert into teacher (t_code,t_name,join_date,salary,city)
values(104,'Micheal',to_date('01/01/2023','dd/mm/yyyy'),15000,'Navsari');
5. insert into teacher (t_code,t_name,join_date,salary,city)
values(105,'Amanda',to_date('21/01/2023','dd/mm/yyyy'),10000,'Dahod');
select * from teacher;
Ans:-
create table subject (sub_code int ,sub_name
varchar2(20) ,t_code int not null);
1. insert into subject (sub_code,sub_name,t_code) values(01,'ETIT',101);
2. insert into subject (sub_code,sub_name,t_code) values(02,'Computer
Graphics',102);
3. insert into subject (sub_code,sub_name,t_code) values(03,'RDBMS',103);
4. insert into subject (sub_code,sub_name,t_code) values(04,'Operating
System',103);
5. insert into subject (t_code) values(104);
6. insert into subject (sub_code,sub_name,t_code) values(06,'Programming
Skills',105);
select * from subject;
Que 2:- Find the name of teachers who are taking more than two
subjects.
Ans:-
select teacher.t_name,
teacher.t_code,subject.sub_name,subject.sub_code from teacher
inner join subject on teacher.t_code=subject.t_code where
t_name='Yashvi' ;
Que 3:- Display the name of teacher who get salary more than
35000 and teach "Computer graphics" subject.
Ans:-
select
teacher.t_name,teacher.t_code,teacher.salary,subject.sub_name
From teacher inner join subject on teacher.t_code=subject.t_code
where sub_name='Computer Graphics' and salary>35000;
Que 4:- Display the name of teacher whose name start with 'K'
and end with 'A'.
Ans:-
select * from teacher where t_name like 'K%a';
Que 5:- Count the teacher who is not taking any subject.
Ans:-
SELECT COUNT(t_code),t_name FROM teacher WHERE t_code =
105 GROUP BY t_name;
6 Display the name of teacher who is situated in 'Valsad' and
taking 'RDBMS' and 'Operating system' subjects.
Ans:-
select teacher.t_name,
teacher.t_code,subject.sub_name,teacher.city from teacher inner
join subject on teacher.t_code=subject.t_code where city='Valsad'
and sub_name in('RDBMS','Operating System');
