--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=