database	veere_db;
 
 --creating table
create	table employee
(
  emp_nm varchar(255),
  emp_dept varchar(255),
  salary int
);
 
 --inserting into table, teradata style
insert	into employee 
values	('&Mary&beth','Admin',5000);
insert	into employee 
values	('&raj&bhat','HR',10000);
insert	into employee 
values	('&Veena&Kumar','HR',15000);
insert	into employee 
values	('Sheela','Admin',5000);
insert	into employee 
values	('&Bibin&Joseph','Academy',20000);
insert	into employee 
values	('&royvivin','Academy',16000);
insert	into employee 
values	('&Arun&Kumar','HR',20000);
insert	into employee 
values	('Varun&Kumar','Academy',10000);
 
--veryfying table population 
select	* 
from	employee;
 
 
--useful Hint
select	
substr(emp_nm, (position('&' in emp_nm)+1)) as ename, 
trim(trailing '&' from substr(ename,1,position('&' in ename))) as fname, 
substr(ename,(position('&' in ename)+1)) as lname,
emp_dept, 
salary, 
rank() over (partition by emp_dept order by salary desc) as ranka
from employee 
qualify ranka=2
 
 
--Corrections Needed
/*
Use 'Case' where last name is missing to avoid getting first name in last name column.
Work on Selective Output, remove ename and ranka from output.
*/
 
 
 
 
 

CmRhdGFiYXNlCXZlZXJlX2RiOwogCiAtLWNyZWF0aW5nIHRhYmxlCmNyZWF0ZQl0YWJsZSBlbXBsb3llZQooCiAgZW1wX25tIHZhcmNoYXIoMjU1KSwKICBlbXBfZGVwdCB2YXJjaGFyKDI1NSksCiAgc2FsYXJ5IGludAopOwogCiAtLWluc2VydGluZyBpbnRvIHRhYmxlLCB0ZXJhZGF0YSBzdHlsZQppbnNlcnQJaW50byBlbXBsb3llZSAKdmFsdWVzCSgnJk1hcnkmYmV0aCcsJ0FkbWluJyw1MDAwKTsKaW5zZXJ0CWludG8gZW1wbG95ZWUgCnZhbHVlcwkoJyZyYWomYmhhdCcsJ0hSJywxMDAwMCk7Cmluc2VydAlpbnRvIGVtcGxveWVlIAp2YWx1ZXMJKCcmVmVlbmEmS3VtYXInLCdIUicsMTUwMDApOwppbnNlcnQJaW50byBlbXBsb3llZSAKdmFsdWVzCSgnU2hlZWxhJywnQWRtaW4nLDUwMDApOwppbnNlcnQJaW50byBlbXBsb3llZSAKdmFsdWVzCSgnJkJpYmluJkpvc2VwaCcsJ0FjYWRlbXknLDIwMDAwKTsKaW5zZXJ0CWludG8gZW1wbG95ZWUgCnZhbHVlcwkoJyZyb3l2aXZpbicsJ0FjYWRlbXknLDE2MDAwKTsKaW5zZXJ0CWludG8gZW1wbG95ZWUgCnZhbHVlcwkoJyZBcnVuJkt1bWFyJywnSFInLDIwMDAwKTsKaW5zZXJ0CWludG8gZW1wbG95ZWUgCnZhbHVlcwkoJ1ZhcnVuJkt1bWFyJywnQWNhZGVteScsMTAwMDApOwoKLS12ZXJ5ZnlpbmcgdGFibGUgcG9wdWxhdGlvbiAKc2VsZWN0CSogCmZyb20JZW1wbG95ZWU7CgoKLS11c2VmdWwgSGludApzZWxlY3QJCnN1YnN0cihlbXBfbm0sIChwb3NpdGlvbignJicgaW4gZW1wX25tKSsxKSkgYXMgZW5hbWUsIAp0cmltKHRyYWlsaW5nICcmJyBmcm9tIHN1YnN0cihlbmFtZSwxLHBvc2l0aW9uKCcmJyBpbiBlbmFtZSkpKSBhcyBmbmFtZSwgCnN1YnN0cihlbmFtZSwocG9zaXRpb24oJyYnIGluIGVuYW1lKSsxKSkgYXMgbG5hbWUsCmVtcF9kZXB0LCAKc2FsYXJ5LCAKcmFuaygpIG92ZXIgKHBhcnRpdGlvbiBieSBlbXBfZGVwdCBvcmRlciBieSBzYWxhcnkgZGVzYykgYXMgcmFua2EKZnJvbSBlbXBsb3llZSAKcXVhbGlmeSByYW5rYT0yCgoKLS1Db3JyZWN0aW9ucyBOZWVkZWQKLyoKVXNlICdDYXNlJyB3aGVyZSBsYXN0IG5hbWUgaXMgbWlzc2luZyB0byBhdm9pZCBnZXR0aW5nIGZpcnN0IG5hbWUgaW4gbGFzdCBuYW1lIGNvbHVtbi4KV29yayBvbiBTZWxlY3RpdmUgT3V0cHV0LCByZW1vdmUgZW5hbWUgYW5kIHJhbmthIGZyb20gb3V0cHV0LgoqLwoKCgoK