Database Queries with Employee Tables

Hello Friends , today we will be creating and use the Employee Database Scheme to answer the given queries !


Here are the necessary details for the overall database scheme


Employee Scheme 

Field	Type	NULL	KEY	DEFAULT
Eno	Char(3)	NO	PRI	NIL
Ename	varchar(50)	NO		NIL
Job_type	varchar(50)	NO		NIL
Manager	Char(3)	Yes	FK	NIL
Hire_date	Date	NO		NIL
Dno	int	YES	FK	NIL
Commission	Decimal(10,2)	YES		NIL
Salary	Decimal(7,2)	NO		NIL

Employee State

Eno	Ename	Job_Type	Manager	Hire_Date	Dno	Commission	Salary
765	Martin	Sales_man	198	1981-04-22	30	1400	1250
756	Jones	Manager	783	1981-04-02	20	0	2300
752	Ward	Sales_man	769	1981-02-22	30	500	1300
749	Allan	Sales_man	769	1981-02-20	30	300	2000
736	Smith	Clerk	790	1980-12-17	20	0	1000
793	Miller	Clerk	788	1982-01-23	40	0	1300
792	Ford	Analyst	756	1981-12-03	20	0	2600
790	James	Clerk	769	1981-12-03	30	0	950
787	Adams	Clerk	778	1983-01-12	20	0	1150
784	Turner	Sales_man	769	1981-09-08	30	0	1450
783	King	President	NULL	1981-11-17	10	0	2950
788	Scott	Analyst	756	1982-12-09	20	0	2850
778	Clark	Manager	783	1981-06-09	10	0	2900
769	Blake	Manager	783	1981-05-01	30	0	2870

Department Scheme

Field	Type	NULL	KEY	DEFAULT
Dno	int	No	PRI	NULL
Dname	Varchar(50)	Yes		NULL
location	Varchar(50)	Yes		New Delhi

Department State

Dno	Dname	Location
10	Accounting	New York
20	Research	Dallas
30	Sales	Chicago
40	Operation	Boston
50	Marketing	New Delhi

Please Note : We have used Oracle 11g for implementing all our queries and also recommend you to do so for better understanding ! 

Now we will answer the given queries

QUERIES

1) Query to display Employee Name, Job, Hire Date, Employee Number; for each employee with the Employee Number appearing first. 

• select eno,ename,job_type,hire_date
  from employee

   

2) Query to display Unique Jobs from the Employee Table.

• select DISTINCT(job_type) From Employee

3) Query to display the Employee Name concatenated by a Job separated by a comma.

• select ename || ' , ' || job_type from employee

4) Query to display all the data from the Employee Table. Separate each Column by a comma and name the said column as THE_OUTPUT.

• select eno || ' , ' || ename ||  ' , ' || job_type ||  ' , ' || manager ||  ' , ' || hire_date ||  ' , ' || dno ||  ' , ' || commission ||  ' , ' || salary 
  as THE_OUTPUT
  from employee  

5) Query to display the Employee Name & Salary of all the employees earning more

than $2850.

• select ename,salary 
  from employee 
  where salary>'2850';

6) Query to display Employee Name & Department Number for the Employee No= 7900.

• select ename,dno 
  from employee 
  where eno='7900'

7) Query to display Employee Name & Salary for all employees whose salary is not in
the range of $1500 and $2850.

• select ename,salary 
  from employee 
  where salary<1500 and 2850 >salary 
  
  
  or 
  
  select ename,salary 
  from employee 
  where NOT EXISTS (select salary from employee 
  where salary <1500 AND 2850>salary )
  
  or
  
  select ename,salary 
  from employee 
  where NOT EXISTS (select salary from employee 
  where salary BETWEEN '1500' AND '2850' )

8) Query to display Employee Name, Job, and Hire Date of all the employees hired between Feb 20, 1981 and May 1, 1981. Order the query in ascending order of Start Date.

• select ename,job_type,hire_date
  from employee 
  where hire_date BETWEEN '20-Feb-1981' AND '1-May-1981'
  order by hire_date

9) Query to display Employee Name & Department No. of all the employees in Dept 10
and Dept 30 in the alphabetical order by name.

• select ename,dno
  from employee
  where dno in(10,30)
  order by ename

10) Query to display Employee Name & Salary of employees who earned more than
$1500 and are in Department 10 or 30.

• select ename,salary 
  from employee 
  where salary>1500 AND ((DNO=10)OR (DNO=30))

11) Query to display Name & Hire Date of every Employee who was hired in 1981.

• Select  Ename,HIRE_DATE
  from EMPLOYEE
  Where HIRE_Date like ‘%81’

12) Query to display Name & Job of all employees who don’t have a current Manager.

• SELECT ENAME,JOB_TYPE
  FROM EMPLOYEE
  WHERE NOT (JOB_TYPE='MANAGER')

13) Query to display the Name, Salary & Commission for all the employees who earn commission. Sort the data in descending order of Salary and Commission.

• SELECT ENAME,salary,commission
  from employee
  where NOT (commission=0)
  ORDER BY SALARY,COMMISSION DESC

14) Query to display Name of all the employees where the third letter of their name is ‘A’.

• SELECT ENAME
  FROM EMPLOYEE
  WHERE ENAME LIKE '__a%'

15) Query to display Name of all employees either have two ‘R’s or have two ‘A’s in their name & are either in Dept No = 30 or their Manger’s Employee No = 7788.

• SELECT ENAME 
  FROM EMPLOYEE
  WHERE (ENAME LIKE '%R%r%' OR ENAME LIKE '%A%a%') AND (DNO='30' OR MANAGER='7788')

16) Query to display Name, Job and Salary of all employees whose Job is Clerical or
Analyst & their salaries are not equal to 1000, 3000, or 5000.

• SELECT ENAME,JOB_TYPE,SALARY
  FROM EMPLOYEE
  WHERE JOB_TYPE IN('CLERK','ANALYST') AND SALARY NOT IN (1000,3000,5000)

17) Query to display Name, Salary and Commission for all employees whose
Commission Amount is greater than their Salary increased by 5 %.

• SELECT ENAME,SALARY,COMMISSION 
  FROM EMPLOYEE
  WHERE
  COMMISSION > (SALARY*0.05)

18) Query to display the Current Date.

• SELECT Sysdate
  FROM Dual

X to the power of Y using recursion in C ++

                                           

#include<iostream.h>

float powerec(float b, float r)
{
 if(r==0)
  return 1;
 else
  return(b*powerec(b,r-1));
}

void main()
{
 float b,r;
 b=0; r=0;
 cout<<"\nEnter The Base: ";
 cin>>b;
 cout<<"\nEnter The Power: ";
 cin>>r;
 float res=powerec(b,r);
 cout<<"\nThe Result Is: "<<res;
}

Sorted Linked List Program in C++

#include<iostream.h>
#include<process.h>

class node
{
public:
 int data;
 node*next;
 node (int n, node*p=NULL)
 {
  data=n;
  next=NULL;
 }
};

class SSLL
{
 node*head;
 node*tail;
public:
 SSLL()
 {
  head=tail=NULL;
 }
 void addtohead(int n);
 void addtotail(int n);
 int isempty();
 int deletefromhead();
 int deletefromtail();
 void deletelement(int n);
 void print();
 void insert(int n);
 void merge(SSLL sl2);
};

void SSLL::addtohead(int n)
{
 node*p=new node(n,head);
 if(isempty())
  head=tail=p;
 else
 {
  node*temp;
  temp=head;
  head=p;
  p->next=temp;
 }
}

void SSLL::addtotail(int n)
{
 node*p=new node(n);
 if(isempty())
  head=tail=p;
 else
 {
  tail->next=p;
  tail=p;
 }
}

int SSLL::isempty()
{
 if(head==NULL)
  return 1;
 else
  return 0;
}

int SSLL::deletefromhead()
{
 node*p;
 if(head==0)
  throw"EMPTY LIST";
 p=head;
 head=head->next;
 if(head==NULL)
  tail=NULL;
 int temp=p->data;
 delete p;
 return temp;
}

int SSLL::deletefromtail()
{
 node*p=0;
 if(head==0)
  throw"EMPTY LIST";
 p=head;
 if(head==tail)
  head=tail=NULL;
 else
 {
  while(p->next!=tail)
   p=p->next;
  tail=p;
  p=p->next;
  tail->next=NULL;
 }
int data1=p->data;
delete p;
return(data1);
}

void SSLL::deletelement(int n)
{
 node*p=head;
 node*prev=NULL;
 while(p!=NULL && p->data!=n)
 {
  prev=p;
  p=p->next;
 }
 if(p!=NULL)
 {
  if(prev==NULL)
  {
   deletefromhead();
  }
  else
   if(p->next==NULL)
    deletefromtail();
   else
   {
    node*temp;
    temp=p->next;
    prev->next=temp;
    cout<<"\nElement Deleted Successfully";
     delete p;
   }
 }
 else
 {
  throw"ELEMENT NOT PRESENT IN THE LIST";
 }
}

void SSLL::print()
{
 if(head==NULL)
  cout<<"LIST IS EMPTY";
 else
 {
  node*p=head;
  while(p!=NULL)
  {
   cout<<"\t"<<p->data;
   p=p->next;
  }
 }
}


void SSLL::insert(int n)
{
 node*pl;
 pl=head;
 if(pl==NULL)
 {
  node*p=new node(n);
  head=tail=p;
 }
 else
 {
  if(n<head->data)
   addtohead(n);
  else if(n>tail->data)
   addtotail(n);
  else
  {
   node*prev=NULL;
   while(pl->data<n)
   {
    prev=pl;
    pl=pl->next;
   }
   node*temp=NULL;
   temp=new node(n);
   temp->next=pl;
   prev->next=temp;
  }
 }
}

void SSLL::merge(SSLL sl2)
{
 SSLL sl3;
 node*p1;
 node*p2;
 p1=this->head;
 p2=sl2.head;
 while(p1!=0 && p2!=0)
 {
  if(p1->data<p2->data)
  {
   sl3.insert(p1->data);
   p1=p1->next;
  }
  else
   if(p1->data>p2->data)
   {
    sl3.insert(p2->data);
    p2=p2->next;
   }
   else
   {
    sl3.insert(p1->data);
    sl3.insert(p2->data);
    p1=p1->next;
    p2=p2->next;
   }
 }
 while(p1!=0)
 {
  sl3.insert(p1->data);
  p1=p1->next;
 }
 while(p2!=0)
 {
  sl3.insert(p2->data);
  p2=p2->next;
 }
 cout<<"\n";
 sl3.print();
}


void main()
{
 SSLL s1,s2;
 int ch,ele,ch1,ch2,ele1;
 do
 {
  cout<<"\n\nWhat Do You Want To Do";
  cout<<"\n1.)Insert An Element Into The List";
  cout<<"\n2.)View Sorted List";
  cout<<"\n3.)Delete An Element";
  cout<<"\n4.)Merge 2 Lists";
  cout<<"\n5.)Exit";
  cout<<"\nEnter Your Choice: ";
  cin>>ch;
  switch(ch)
  {
  case 1:cout<<"\nEnter The Elment You Want To Insert: ";
      cin>>ele;
      cout<<"\nEnter The Linked List You Want To Access: ";
      cin>>ch1;
      if(ch1==1)
       s1.insert(ele);
      if(ch1==2)
       s2.insert(ele);
      cout<<"\nElement Added Successfully";
      break;
  case 2:cout<<"\nEnter The List You Want To View: ";
      cin>>ch2;
      if(ch2==1)
      {
       cout<<"\n";
       s1.print();
      }
      if(ch2==2)
      {
       cout<<"\n";
       s2.print();
      }
      break;
  case 3:cout<<"\nEnter The Element You Want to Delete: ";
      cin>>ele1;
      cout<<"\nEnter The Linked List You Want To Access: ";
      cin>>ch1;
      if(ch1==1)
       s1.deletelement(ele1);
      else
       s1.deletelement(ele1);
               break;
  case 4:s1.merge(s2);
      break;
  case 5:exit(10);
      break;
  default:cout<<"\nWrong Choice!!!";
       break;
  }
 }while(ch!=5);
}

Binary Search Tree Program in C++

#include <iostream.h>
#include <process.h>

class tnode
{
public:
 int data;
 tnode *lchild;
 tnode *rchild;
 tnode(int n,tnode *l=0,tnode *r=0)
 {  
  data=n;
  lchild=l;
  rchild=r;
 }
};

class Queue
{
private:
    int front,rear,size;
 tnode **a;
public:
 Queue(int size=0)
 {
  front=rear=-1;
  if (size!=0)
   a=new tnode*[size];
  else
   a=0;
  this->size=size;
 }
 void enqueue(tnode* el);
 tnode* dequeue();
 bool isempty() const;
};

void Queue::enqueue(tnode* el)
{
 if(isempty())
 {
       front=rear=0;
    a[front]=el;
 }
 else
 {
  rear=(rear+1)%size;
  a[rear]=el;
 }
}
tnode* Queue::dequeue()
{
 tnode* i;
 if (front==-1)
  cout<<"Empty list. Deletion not possible";
 else if (front==rear)
 {
  i=a[front];
     front=rear=-1;
 }
 else
 {  
  i=a[front];
  front=(front+1)%size;
 }
    return i;
}

bool Queue::isempty() const
{
 return(front==-1);
}

class BST
{
private:
 tnode *root;
 void preorder(tnode *);
 void postorder(tnode *);
 void inorder(tnode *);
public:
 BST()
 {
  root=0;
 }

 void insert(int x);
 void insert(tnode* &p,int x);
 void delete_by_copying(int x);
 void delete_by_merging(int x);
 tnode* search(int x);
 tnode* search(tnode * p,int x);
 void preorder();
 void postorder();
 void inorder();
 void level_by_level_traversal();
 int count_leafnode();
 int count_leafnode(tnode *t);
 int count_nonleaf();
 int count_nonleaf(tnode *t);
 int height();
 int height(tnode *t);
 tnode* mirror();
 tnode* mirror(tnode *p);
};

// Inserting an element
void BST::insert(int x)
{
 treenode*t=new treenode(x);
 if(root==NULL)
 {
  root=t;
  return;
 }
 treenode*p,*fp;
 p=root;
 fp=0;
 while(p!=0)
 {
  fp=p;
  if(x==p->data)
   throw"DUPLICATE VALUE";
  else if(x<p->data)
   p=p->lchild;
  else
   p=p->rchild;
 }
 if(x<fp->data)
  fp->lchild=t;
 else
  fp->rchild=t;
}

// Deletion By Copying
void BST::delete_by_copying(int x)
{
 if (root==0)
  throw "Empty tree";
 tnode *p=root;
 tnode *fp=0;
 while(p!=0 && p->data!=x)
 {
  fp=p;
  if (x<p->data)
   p=p->lchild;
  else
   p=p->rchild;
 }
    if (p==0)
  throw "Element not present ";
 bool lchild;
 if (fp!=0)
   lchild=(x<fp->data)?true:false;
 // if deleted node is a leaf
 if ((p->lchild==0) && (p->rchild)==0)
 {
  if (fp==0)
  {
   root=0;return;
  }
  if (lchild)
   fp->lchild=0;
     else
     fp->rchild=0;
 }
 // if deleted node has lchild only
 else if (p->rchild==0)
 {  
  if (fp==0)
  {
   root=p->lchild;
   return;
  }
  if (lchild)
   fp->lchild=p->lchild;
     else
     fp->rchild=p->lchild;
 }
 // if deleted node has rchild only
 else if (p->lchild==0)
 {  
  if (fp==0)
  {
   root=p->rchild;
   return;
  }
  if (lchild)
   fp->lchild=p->rchild;
     else
      fp->rchild=p->rchild;
 }
 // if both the children exists
 else
 { tnode *q=p->rchild;
   tnode *fq=p;
   while (q->lchild!=0)
   {
    fq=q;
    q=q->lchild;
   }
      p->data=q->data;
      if (q->data<fq->data)
    fq->lchild=q->rchild;
   else
    fq->rchild =q->rchild;
   delete q;
 }
}

// Deletion by Merging
void BST::delete_by_merging(int x)
{
 if (root==0)
  throw "Empty tree";
 tnode *p=root;
 tnode *fp=0;
 while(p!=0 && p->data!=x)
 {
  fp=p;
  if (x<p->data)
   p=p->lchild;
  else
   p=p->rchild;
 }
    if (p==0)
  throw "Element not present ";
 bool lchild;
 if (fp!=0)
   lchild=(x<fp->data)?true:false;
 // if deleted node is a leaf
 if ((p->lchild==0) && (p->rchild)==0)
 {
  if (fp==0)
  {
   root=0;return;
  }
  if (lchild)
   fp->lchild=0;
     else
      fp->rchild=0;
 }
 // if deleted node has lchild only
 else if (p->rchild==0)
 {  
  if (fp==0)
  {
   root=p->lchild;
   return;
  }
  if (lchild)
   fp->lchild=p->lchild;
     else
      fp->rchild=p->lchild;
 }
 // if deleted node has rchild only
 else if (p->lchild==0)
 {
  if (fp==0)
  {
   root=p->rchild;
   return;
  }
  if (lchild)
   fp->lchild=p->rchild;
     else
      fp->rchild=p->rchild;
 }
 // if both the children exists
 else
 {
   tnode *q=p->lchild;
   tnode *rc=p->rchild;
   while (q->rchild!=0)
   {
    q=q->rchild;
   }
   q->rchild=rc;
      if (fp==0)
    root=p->lchild;
   else
   {  
    if (p->data<fp->data)
     fp->lchild=p->lchild;
    else
     fp->rchild=p->lchild;
   }
   delete p;
 }
 }

//Searching an element
tnode* BST::search(int x)
{
          if (root==0)
     return(0);
          if (root->data==x)
     return (root);
          if (x<root->data)
     return(search(root->lchild,x));
          else
     return(search(root->rchild,x));
}

tnode* BST::search(tnode * p,int x)
{
          if (p==0)
     return(0);
          if (p->data==x)
     return (p);
          if (x<p->data)
     return(search(p->lchild,x));
          else
     return(search(p->rchild,x));
}

// Inorder Traversal
void BST::inorder()
{
 if (root==0)
  return;
 inorder(root->lchild);
 cout<<root->data<<"  ";
 inorder(root->rchild);
}

void BST::inorder(tnode* r)
{
 if (r==0)
  return;
 inorder(r->lchild);
 cout<<r->data<<"  ";
 inorder(r->rchild);
}

// Preorder Traversal
void BST::preorder()
{
 if (root==0)
  return;
 cout<<root->data<<"  ";
 preorder(root->lchild);
 preorder(root->rchild);
}

void BST::preorder(tnode* r)
{
 if (r==0)
  return;
 cout<<r->data<<"  ";
 preorder(r->lchild);
 preorder(r->rchild);
}

//Postorder Traversal
void BST::postorder()
{
 if (root==0)
  return;
 postorder(root->lchild);
 postorder(root->rchild); 
 cout<<root->data<<"  ";
}

void BST::postorder(tnode* r)
{
 if (r==0)
  return;
 postorder(r->lchild);
 postorder(r->rchild);
 cout<<r->data<<"  ";
}

// Level by Level Traversal
void BST::level_by_level_traversal()
{
 if(root==0)
 {
  cout<<"Empty tree";
  return;
 }
 cout<<"\nLevel by level traversal is ";
 Queue q(10);
 q.enqueue(root);
 while(!q.isempty())
 {
  tnode *p=q.dequeue();
  cout<<p->data<<" ";
  if(p->lchild!=0)
   q.enqueue(p->lchild);
  if(p->rchild!=0)
   q.enqueue(p->rchild);
 }
}

//Count leaf nodes
int BST::count_leafnode()
{
 if(root==0)
  return 0;
 else if(root->lchild==0&&root->rchild==0)
  return 1;
 else
  return(count_leafnode(root->lchild)+count_leafnode(root->rchild));
}

int BST::count_leafnode(tnode *t)
{
 if(t==0)
  return 0;
 else if(t->lchild==0&&t->rchild==0)
  return 1;
 else
  return(count_leafnode(t->lchild)+count_leafnode(t->rchild));
}

//Count non leaf nodes
int BST::count_nonleaf()
{
 if(root==0)
  return 0;
 else if(root->lchild==0&&root->rchild==0)
  return 0;
 else
  return(1+count_nonleaf(root->lchild)+count_nonleaf(root->rchild));
}  

int BST::count_nonleaf(tnode *t)
{
 if(t==0)
  return 0;
 else if(t->lchild==0&&t->rchild==0)
  return 0;
 else
  return(1+count_nonleaf(t->lchild)+count_nonleaf(t->rchild));
}

//Calculate height of tree
int BST::height()
{
 if (root==0)
  return 0;
 else if (root->lchild==0 && root->rchild==0)
  return 1;
 else
 {
   int hl=height(root->lchild);
   int hr=height(root->rchild);
   int max=hl>hr?hl:hr;
   return(max+1);
 }
}

int BST::height(tnode *t)
{
 if(t==0)
  return 0;
 else if(t->lchild==0&&t->rchild==0)
  return 1;
 else
 {
  int x=height(t->lchild);
  int y=height(t->rchild);
  if(x>y)
   return(1+x);
  else
   return (1+y);
 }
}

//Mirror image of tree
tnode* BST::mirror()
{
 if(root == NULL)
  return root ;
 else
 {
  mirror(root->lchild);
  mirror(root->rchild);
  tnode *temp;
  temp=root->lchild;
  root->lchild=root->rchild;
  root->rchild=temp;
 }
 return root;
}
tnode* BST::mirror(tnode *p)
{
 if(p == NULL)
  return p ;
 else
 {
  mirror(p->lchild);
  mirror(p->rchild);
  tnode *temp;
  temp=p->lchild;
  p->lchild=p->rchild;
  p->rchild=temp;
 }
 return p;
}

void main()
{
 BST b;
 int i,n;
 tnode *p;
 int ch;
 do
 {
  cout<<"\n\nMAIN MENU";
  cout<<"\n1.)Insertion ";
  cout<<"\n2.)Deletion by copying ";
  cout<<"\n3.)Deletion by merging";
  cout<<"\n4.)Search a no in BST ";
  cout<<"\n5.)In-Order Traversal ";
  cout<<"\n6.)Pre-Order Traversal ";
  cout<<"\n7.)Post-Order Traversal ";
  cout<<"\n8.)Level-by-level Traversal ";
  cout<<"\n9.)Count no of non leaf nodes ";
  cout<<"\n10.)Count no of leaf nodes ";
  cout<<"\n11.)Height of tree ";
  cout<<"\n12.)Mirror image ";
  cout<<"\n13.)Exit ";
  cout<<"\n\n\t\t\t Enter your choice: ";
  cin>>ch;
  switch(ch)
  {
   case 1 : cout<<"\n Enter the node to be inserted : ";
     cin>>n;
     b.insert(n);
     cout<<"\n The tree in inorder traversal is: ";
     b.inorder();
     break;
   case 2 : cout<<"\n Enter the node to be deleted : ";
     cin>>n;
     b.delete_by_copying(n);
     cout<<"\n The tree in inorder traversal is: ";
     b.inorder();
     break;
                        case 3 : cout<<"\n Enter the node to be deleted : ";
     cin>>n;
     b.delete_by_merging(n);
     cout<<"\n The tree in inorder traversal is: ";
     b.inorder();
     break;
   case 4 : cout<<"\n Enter the node to be searched: ";
            cin>>n;
     p=b.search(n);
     if(p!=0)
       cout<<"\n Node "<<p<<" found";
     else
       cout<<"\n Node not present";
     break;
   case 5 : cout<<"\n Inorder traversal is: ";
            b.inorder();
     break;
   case 6 : cout<<"\n Preorder traversal is: ";
            b.preorder();
     break;
   case 7 : cout<<"\n Postorder traversal is: ";
            b.postorder();
     break;
   case 8 : b.level_by_level_traversal();
     break;
          case 9 : n=b.count_nonleaf();
     cout<<"No. of non leaf nodes= "<<n;
            break;
          case 10: n=b.count_leafnode();
     cout<<"No. of leaf nodes= "<<n;
            break;
   case 11: n=b.height();
     cout<<"Height of tree= "<<n;
     break;
   case 12: p=b.mirror();
     cout<<"\n The tree in inorder traversal is: ";
     b.inorder();
     break;
   default: exit(0);
  }
 }while(ch!=13);
}