My preparation notes for Wells Fargo interview-Oracle I

to connect to database:
conn

see user name:
show user (or)
select user from dual;

see objects of database

select * from tab;

see all tables:

select * from tab where tabtype = ‘TABLE’;

Single Table queries:

using variable in query
select s.name,s.gpa from students s where s.department = ‘ÇS’;

using distinct keyword, removes duplicates from results

AS clause for naming output columns

ORDER BY clause – sorts lexicographic order

select distinct s.name,s.gpa,s.age from students s where s.department = ‘ÇS’ order by s.gpa,s.name,s.age;

lexicographic order implies, sort by gpa, if there is tie then sort by name, and if there is tie again, then sort by age..
the data must be present in output to be used in order by clause
e.g. order by sqrt(s.gpa) s illegal in above statement

default is ascending. ASC – ascendng, DESC – descending

select distnct s.name,s.gpa,s.age from students s where s.department=’CS’ order by s.gpa ASC, s.age DESC;

distinct can be used inside count, to get count of distinct values

GROUP BY clause – can group a list of columns

select avg(s.gpa),s.department from students s group by s.department;

arithmatic built-in:
ceil,floor,sqrt,power,mod

string builtin:
lpad,rpad,ltrim,rtrim,substr’,replace,concat

conversion:
to_date,to_char

aggregate:
count,sum,min,max

Subquery:

subquery can be used in insert statement.
it can be a single scalar value if used in where clause directly or a column if used in IN operator or NOT IN operator

correlated qquery : depends on outer query
non correlated: this is independent query

avoid correlated query as they are slow

select m.name from markets m where m.ric in(select s.listed_on_exchange from stocks s where s.listed_on_exchange = m.ric);

Query to check no. of days between 2 dates:

select extract(day from(sysdate-to_date(’21-02-2015′,’DD-MM-YYYY’))day(9) to second) from dual;

day(9) to second :-> interval expression

truncate vs delete vs drop

truncate removes the data of the table by deallocating the space used by the table. this prevents the logging and locking of the table
It cannot fire trigger and also it is not possible when a column of the table to be deleted is used as a foreign key in another table
truncate is used in purging large data, if delete is given to purge such large data, it cause performance issue
truncate cannot be rolled back

to remove data, we can also drop table and recreate it.

delete logs the action and can help in deleting select records.

query to find 2nd largest salary:

select max(salary) from employee where salary !=(select max(salary) from employee); or
select e1.empname,e1.salary from employee e1 where 1=(select count(distinct(e2.salary)) from employee e2 where e2.salary>e1.salary);

Constraints:

unique : does not allow duplicates in the column
not null : dow not allow null value in the column
primary key : unique and not null constraints combined
used to uniquely refernce the records in table
simple primary : if only single primary key is present
composite primary: if more than one primary key is present

foreign key:
used to establish relationship between two or more tables.
if foreign key constraint is used, it checks with the parent table to see that the inserted values are fine in child table.
when the record is deleted in parent table, it should be reflected in child table also, either manually or automatically defined at the start as below
on delete cascade option : if record in parent table is deleted, it’s removed in child table as well
on delet set null option : if record in parent table is deleted, corresponding one is deleted and set to null in child table

usage:

create table students(
sid int not null,
name varchar(20) unique,
score float primary key,
branch varchar(30) );

create table employee(
eid int,
department varchar(40),
branch varchar(30) references students(branch) on delete cascade,
primary key(eid,department)
);

create table teacher(
sid int,
score float,
foreign key(sid,score) references students(sid,score) on delete set null);

default constraint: gives default value to the column
check constraint: gives user defined rules

e.g.
create table webform(
username varchar(20),
age int check(age>20));

Primary key is essential while joining the tables.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s