--creating teradata database
create	database veere_db 
from	tduser
as
permanent = 50000,
spool = 30000,
temporary = 50000;
 
 --selecting database to use
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.
*/
 
 
 
 
 

CgotLWNyZWF0aW5nIHRlcmFkYXRhIGRhdGFiYXNlCmNyZWF0ZQlkYXRhYmFzZSB2ZWVyZV9kYiAKZnJvbQl0ZHVzZXIKYXMKcGVybWFuZW50ID0gNTAwMDAsCnNwb29sID0gMzAwMDAsCnRlbXBvcmFyeSA9IDUwMDAwOwogCiAtLXNlbGVjdGluZyBkYXRhYmFzZSB0byB1c2UKZGF0YWJhc2UJdmVlcmVfZGI7CiAKIC0tY3JlYXRpbmcgdGFibGUKY3JlYXRlCXRhYmxlIGVtcGxveWVlCigKICBlbXBfbm0gdmFyY2hhcigyNTUpLAogIGVtcF9kZXB0IHZhcmNoYXIoMjU1KSwKICBzYWxhcnkgaW50Cik7CiAKIC0taW5zZXJ0aW5nIGludG8gdGFibGUsIHRlcmFkYXRhIHN0eWxlCmluc2VydAlpbnRvIGVtcGxveWVlIAp2YWx1ZXMJKCcmTWFyeSZiZXRoJywnQWRtaW4nLDUwMDApOwppbnNlcnQJaW50byBlbXBsb3llZSAKdmFsdWVzCSgnJnJhaiZiaGF0JywnSFInLDEwMDAwKTsKaW5zZXJ0CWludG8gZW1wbG95ZWUgCnZhbHVlcwkoJyZWZWVuYSZLdW1hcicsJ0hSJywxNTAwMCk7Cmluc2VydAlpbnRvIGVtcGxveWVlIAp2YWx1ZXMJKCdTaGVlbGEnLCdBZG1pbicsNTAwMCk7Cmluc2VydAlpbnRvIGVtcGxveWVlIAp2YWx1ZXMJKCcmQmliaW4mSm9zZXBoJywnQWNhZGVteScsMjAwMDApOwppbnNlcnQJaW50byBlbXBsb3llZSAKdmFsdWVzCSgnJnJveXZpdmluJywnQWNhZGVteScsMTYwMDApOwppbnNlcnQJaW50byBlbXBsb3llZSAKdmFsdWVzCSgnJkFydW4mS3VtYXInLCdIUicsMjAwMDApOwppbnNlcnQJaW50byBlbXBsb3llZSAKdmFsdWVzCSgnVmFydW4mS3VtYXInLCdBY2FkZW15JywxMDAwMCk7CgotLXZlcnlmeWluZyB0YWJsZSBwb3B1bGF0aW9uIApzZWxlY3QJKiAKZnJvbQllbXBsb3llZTsKCgotLXVzZWZ1bCBIaW50CnNlbGVjdAkKc3Vic3RyKGVtcF9ubSwgKHBvc2l0aW9uKCcmJyBpbiBlbXBfbm0pKzEpKSBhcyBlbmFtZSwgCnRyaW0odHJhaWxpbmcgJyYnIGZyb20gc3Vic3RyKGVuYW1lLDEscG9zaXRpb24oJyYnIGluIGVuYW1lKSkpIGFzIGZuYW1lLCAKc3Vic3RyKGVuYW1lLChwb3NpdGlvbignJicgaW4gZW5hbWUpKzEpKSBhcyBsbmFtZSwKZW1wX2RlcHQsIApzYWxhcnksIApyYW5rKCkgb3ZlciAocGFydGl0aW9uIGJ5IGVtcF9kZXB0IG9yZGVyIGJ5IHNhbGFyeSBkZXNjKSBhcyByYW5rYQpmcm9tIGVtcGxveWVlIApxdWFsaWZ5IHJhbmthPTIKCgotLUNvcnJlY3Rpb25zIE5lZWRlZAovKgpVc2UgJ0Nhc2UnIHdoZXJlIGxhc3QgbmFtZSBpcyBtaXNzaW5nIHRvIGF2b2lkIGdldHRpbmcgZmlyc3QgbmFtZSBpbiBsYXN0IG5hbWUgY29sdW1uLgpXb3JrIG9uIFNlbGVjdGl2ZSBPdXRwdXQsIHJlbW92ZSBlbmFtZSBhbmQgcmFua2EgZnJvbSBvdXRwdXQuCiovCgoKCgo=