--rounds of interview
300 --> appti
examveda --> app
indiabix
sanfoundary
carrier ride
1 apptitude test -------> 60% --> 80%
paper 2/3 papers :
RS Agarwal --> vol1 , vol2
1 hr appti
maths 1
logical reasoning 1
english 3
quantitative 1
verbal and non verbal reasoning 1
4 -->
2 tech mcq 2
3 tech coding test
data structure : 30 min
4 group discussion
10 min
topics :
pandamic
AI
globalization
population in india
woman empowerment
IOT
resce
attendance comp in edu
digital
5 hr interview
=================================================================
--display the all details other than smith , jones and allen
select * from emp
where ename not in ('smith', 'jones', 'allen')
--display the all details who are not working for deptno 10, 20
select * from emp
where deptno not in (10, 20)
===Dr. E. F. Codd's 12 rules
1. 6 rules
2. Information Rule
no pointer concept will be used
3. select ename , sal from emp
where empno=7369;
4. supports null values
5. desc emp; ---> catalog
catalog and description of the fields : meta data : data about the data
eg : desc emp;
col name , datatypes , pk, null, fk , default
6. support ddl, dml, dcl, tcl
7. view : logical table
rule : the view should be updatable
8. insert mutiple records at a time
9. physical location and access of the database.
we do not know location of the table , still we are inserting , updating the
record
10. Logical Data Independence:
emp : 8 cols--> add the col , delete col
11. Integrity Independence
pk, fk --> is stored separatly
12. Distribution Independence
server login table , inbox, outbox
|
-----------------------------
| | |
client1 clent2 client3
eg : gmail sever
login : priti
pass : 123
13. Nonsubversion Rule
bypass the integrity rules or constraints is not allowed
eg pk , fk;
====================
ename like '%m%'
--predicates
1 in : exact mathc
2 between : range
3 like : search for the pattern
--between
--eg display name of emp earning the sal >= 1000 and <= 3000
select ename, sal from emp
where sal >= 1000 and sal <=3000;
or
select ename, sal from emp
where sal between 1000 and 3000;
---------|----------|----------
1000 3000
--eg display name of emp earning the sal < 1000 and > 3000
select ename, sal from emp
where sal not between 1000 and 3000;
--eg display name of emp joined in yr 2012
select ename, sal from emp
where hiredate between '2012-1-1' and '2012-12-31'
--eg display name of emp joined in yr 2012, 2013
select ename, sal from emp
where hiredate between '2012-1-1' and '2013-12-31'
=====================================
Execution plan -->
select ename from emp
where ename ='smith';
plan 1 : 3s
plan 2 2.5s
plan 3 1 s
plan 4 5s
select ename from emp
where ename ='smith';
select ename from emp
where ename ='smith';
--
--like : pattern
--wild characters
% : replacement of 0 or more ch
_ : replacement of 1 ch
--display the name of employees whoes name start with A ch
select ename from emp
where ename like 'A%';
--ends with R
select ename from emp
where ename like '%R';
--anywhere A
select ename from emp
where ename like '%A%';
--ename should be 5 ch
select ename from emp
where ename like '_____';
--3rd ch R followed by any ch
select ename from emp
where ename like '__R%'
--3rd ch R and 6th ch R followed by ch
select ename from emp
where ename like '__R__R%'
--display the name of employees whoes name not starting with A ch
select ename from emp
where ename not like 'A%';
--display the name of employees whoes name starting with A ch or M ch
select ename from emp
where ename like 'A%' or ename like 'M%';
===========
--display first 3 records
select * from emp
limit 3;
--skip first 3 rec and will display the next 2 records
select * from emp
limit 3, 2;
select * from emp
limit 3, 5;
--clauses
from : table name
where : condition
group by : grouping
having : condition
order by : sorting
--order by :
used to sort the record
--display the ename and sal , sort by sal
select ename , sal
from emp
order by sal ;
--1 NAME of the col
select ename , sal
from emp
order by sal desc;
--display the ename and sal , sort by ename
select ename , sal
from emp
order by ename desc;
--2 col number
select ename , sal , hiredate
from emp
order by 1 desc;
--3 using alias
select ename name , sal + 5000 as newsal ,comm commission
from emp
order by newsal;
--4 expression
select ename name , sal + 5000 as newsal ,comm commission
from emp
order by sal + 5000;
===
select ename name , sal + 5000 as newsal ,comm commission
from emp
where sal > 2000
order by newsal;
select ename , deptno, sal
from emp
order by sal, deptno , ename ;
1 2 3
--sorting on multiple cols
select ename , deptno, sal
from emp
order by deptno, sal;
select ename , deptno, sal , empno
from emp
order by deptno, sal, ename desc, empno;
select ename , deptno, sal
from emp
order by sal, deptno;
select ename , deptno, sal
from emp
order by deptno desc, sal;
select ename , deptno, sal
from emp
order by deptno , sal desc;
select ename , deptno, sal
from emp
order by deptno desc, sal desc ;
select ename , deptno, sal
from emp
order by 2 desc, 3;
--1
select * from emp where
job ='clerk' ;
--2
select * from emp where
job in ('clerk','manager', 'analyst') ;
==aggregate functions
called as MRF --> multiple row functions
it is used to create the summary report
1 sum()
2 min()
3 max()
4 avg()
5 count()
--14
select count(*) from emp;
--4
select count(comm) from emp;
null values are not counted
== group by clause
it is used to group the records
select deptno, count(empno) totemp
from emp
group by deptno;
deptno totemp
10 4
20 6
30 5
--department wise calculat the min sal, max sal
wise --> use group by
select deptno, min(sal) , max(sal) totemp
from emp
group by deptno
order by deptno;
--display the following
select job, count(empno) totemp
from emp
group by job
order by totemp;
job totemp
clerk 3
manager 4
salesman 6
president 1
analyst 3
--grouping on multiple cols
display the department wise and job wise , total number of employees
select deptno, job , count(empno)
from emp
group by deptno, job
order by deptno;
deptno job totemp
10 clerk 2
10 manager 3
10 analyst 2
10 salesman 3
20 clerk 1
20 manager 2
20 analyst 2
20 salesman 3
30 clerk 3
30 manager 1
30 analyst 1
30 salesman 3
--tomo
having
data type
er models
database objects
==================
--changing the heading of the col
select ename , sal + 3000 newsal , hiredate
from emp |
|
alias
select ename , sal + 3000 as Newsal , hiredate
from emp;
select ename , sal + 3000 as 'New salary', hiredate
from emp;
--display the ename , sal, comm , change ename as name and comm as commission
select ename name , sal ,comm commission
from emp;
--display the ename , hiredate,change ename as name and hiredate as doj
select ename name , hiredate doj
from emp;
============
===========================
--using alias : +
--tomo
data types
ER diagrams
aggr fun
constraints
alter
===
