Basic Computer Science - I (Data Structure and SQL) 3630002 Practical Solution

Applications of Data Structures

1. Write a program to implement STACK using array. Perform the following operations on the STACK:
   a. PUSH
   b. POP
   c. CHANGE
   d. PEEP
   e. TOP_OF_STACK
   f. IS_EMPTY
   g. IS_FULL
   

   
   

   
   #include<stdio.h>
   #include<conio.h>
   #define MAX 5
   struct stack
   {
    int top;
    int value[MAX];
   };
   void push(struct stack *s)
   {
         int temp;
         if(s->top == MAX-1)
         {
          printf("Stack is FULL");
         }
         else
         {
          printf("\nEnter Element : ");
          scanf("%d",&temp);
          s->top++;
       s->value[s->top] = temp;
         }
   }
   void display(struct stack *s)
   {
         int i;
         if(s->top == -1)
          printf("Stack is EMPTY");
         for(i=0;i<=s->top;i++)
         {
              printf("%d ", s->value[i]);
         }
   }
   void pop(struct stack *s)
   {
         int t;
         if(s->top > -1)
         {       
          t = s->value[s->top];
          s->top --;
       printf("POPed element is : %d",t);
          //return t;
         }
         else
         {
          printf("Stack underflow.");
          //return -1;
         }
   
   
   }
   void isEmpty(struct stack s)
   {
          if(s.top == -1)
           printf("Stack is EMPTY");
          else
           printf("Stack is NOT EMPTY");
   }
   void isFull(struct stack s)
   {
          if(s.top == MAX-1)
           printf("Stack is FULL");
          else
           printf("Stack is NOT FULL");
   }
   void main()
   {
    struct stack s;
    int ch;
    //clrscr();
    s.top = -1;
   
    while(1)
    {
   
    printf("\n\n=================\n");
    printf("1. PUSH Element\n");
    printf("2. Display Stack Elements\n");
    printf("3. POP Element\n");
    printf("4. Is Stack Empty ? \n");
    printf("5. Is Stack Full ?\n");
    printf("0. Exit\n");
    printf("=================\n");
    printf("Enter your choice : ");
    scanf("%d", &ch);
    switch(ch)
    {
     case 1:
      push(&s);
      break;
     case 2:
      display(&s);
      break;
     case 3:
      pop(&s);
      break;
     case 4:
      isEmpty(s);
      break;
     case 5:
      isFull(s);
      break;
     case 0:
      exit(1);
      break;
     default:
      exit(1);
    }
    }
    getch();
   }
   
   



2. Write a program to convert an infix arithmetic expression (parenthesize/unparenthesized) into postfix notation.
   

   
   

   /* Program for conversion of infix to postfix and evaluation of postfix.
   It will take only single digit in expression */
   
   #include <stdio.h>
   #include <string.h>
   #include <conio.h>
   #include <stdlib.h>
   #include <math.h>
   #define Blank ' '
   #define Tab '\t'
   #define MAX 50
   
   long int pop ();
   long int eval_post();
   void infix_to_postfix();
   int prec(char symbol );
   void push(long int symbol);
   int white_space(char symbol);
   char infix[MAX], postfix[MAX];
   long int stack[MAX];
   int top;
   int main()
   {
    long int value;
    
    top = 0;
    printf("Enter infix : ");
    fflush(stdin);
    gets(infix);
    infix_to_postfix();
    printf("Postfix : %s\n",postfix);
    value=eval_post();
    printf("Value of expression : %ld\n",value);
     
    return 1;
   }/*End of main()*/
   
   void infix_to_postfix()
   {
    int i,p=0,type,precedence,len;
    char next ;
   
    stack[top]='#';
    len=strlen(infix);
    infix[len]='#';
    for(i=0; infix[i]!='#';i++)
    {
   
     if( !white_space(infix[i]))
     {
      switch(infix[i])
      {
      case '(':
       push(infix[i]);
       break;
   
      case ')':
       while((next = pop()) != '(')
        postfix[p++] = next;
   
       break;
      case '+':
      case '-':
      case '*':
      case '/':
      case '%':
      case '^':
       precedence = prec(infix[i]);
       while(stack[top]!='#' && precedence <= prec(stack[top]))
        postfix[p++] = pop();
       push(infix[i]);
       break;
      default: /*if an operand comes */
           postfix[p++] = infix[i];
      }/*End of switch */
     }/*End of if */
    }/*End of for */
    while(stack[top]!='#')
     postfix[p++] = pop();
    postfix[p] = '\0' ; /*End postfix with'\0' to make it a string*/
   }/*End of infix_to_postfix()*/
   
   /* This function returns the precedence of the operator */
   int prec(char symbol )
   {
    switch(symbol)
    {
    case '(':
     return 0;
    case '+':
    case '-':
     return 1;
    case '*':
    case '/':
    case '%':
     return 2;
    case '^':
     return 3;
    }/*End of switch*/
   }/*End of prec()*/
   
   void push(long int symbol)
   {
    if(top > MAX)
    {
     printf("Stack overflow\n");
     exit(1);
    }
    else
    {
     top=top+1;
     stack[top] = symbol;
    }
   }/*End of push()*/
   
   long int pop()
   {
    if (top == -1 )
    {
     printf("Stack underflow \n");
     exit(2);
    }
    else
     return (stack[top--]);
   }/*End of pop()*/
   
   int white_space(char symbol)
   {
    if( symbol == Blank || symbol == Tab || symbol == '\0')
     return 1;
    else
     return 0;
   }/*End of white_space()*/
   
   long int eval_post()
   {
    long int a,b,temp,result,len;
    int i;
    len=strlen(postfix);
    postfix[len]='#';
   
    for(i=0;postfix[i]!='#';i++)
    {
     if(postfix[i] <= '9' && postfix[i] >= '0')
      push( postfix[i]-48 );
     else
     {
      a=pop();
      b=pop();
   
      switch(postfix[i])
      {
      case '+':
       temp=b+a; break;
      case '-':
       temp=b-a;break;
      case '*':
       temp=b*a;break;
      case '/':
       temp=b/a;break;
      case '%':
       temp=b%a;break;
      case '^':
       temp=pow(b,a);
      }/*End of switch */
      push(temp);
     }/*End of else*/
    }/*End of for */
    result=pop();
    return result;
   }/*End of eval_post */
   
   



3. Write a program to convert an infix arithmetic expression (parenthesize/unparenthesized) into postfix notation.
   

   
   

   
   Same as above
   
   



4. Write a program to implement QUEUE using array. Perform the following operations on the QUEUE:
   a. Insert an element in the QUEUE. (Enqueue)
   b. Delete an element from the QUEUE. Dequeue
   

   
   

   /*Program of queue using array*/
   # include <stdio.h>
   # include <conio.h>
   # define MAX 5
   
   int queue_arr[MAX];
   int rear = 0;
   int front = 0;
   
   main()
   {
    int choice;
    while(1)
    {
     printf("1.Insert\n");
     printf("2.Delete\n");
     printf("3.Display\n");
     printf("4.Quit\n");
     printf("Enter your choice : ");
     scanf("%d",&choice);
   
     switch(choice)
     {
     case 1 :
      insert();
      break;
     case 2 :
      del();
      break;
     case 3:
      display();
      break;
     case 4:
      exit(1);
     default:
      printf("Wrong choice\n");
     }/*End of switch*/
    }/*End of while*/ 
    getch();
   }/*End of main()*/
   
   insert()
   {
    int added_item;
    if (rear==MAX)
     printf("Queue Overflow\n");
    else
    {
     printf("Input the element for adding in queue : ");
     scanf("%d", &added_item);
     queue_arr[rear] = added_item ;
     rear=rear+1;
    }
   }/*End of insert()*/
   
   del()
   {
    if (front >= rear)
    {
     printf("Queue Underflow\n");
     return ;
    }
    else
    {
     printf("Element deleted from queue is : %d\n",  queue_arr[front]);
     front=front+1;
    }
   }/*End of del() */
   
   display()
   {
    int i;
    if (front == rear)
     printf("Queue is empty\n");
    else
    {
     printf("Queue is :\n");
     for(i=front; i < rear;i++)
      printf("%d  ",queue_arr[i]);
     printf("\n");
    }
   }/*End of display() */
   
   



5. Write a program to perform the Bubble sort.
   

   
   

   // Bubble sort program
   
   #include <stdio.h>
   #include <conio.h>
   #define n 5
   
   void printArray(int arr[])
   {
    int i;
    for (i=0; i < n; i++)
     printf("%d ", arr[i]);
    printf("\n");
   }
   
   
   
   // A function to implement bubble sort
   void bubbleSort(int arr[])
   {
       int i, j, temp;
   
       for (i = 0; i < n-1; i++)  
       {
        // Last i elements are already in place 
        for (j = 0; j < n-i-1; j++) 
        {
         if (arr[j] > arr[j+1])
         {
          temp = arr[j];
              arr[j] = arr[j+1];
              arr[j+1] = temp;
         }
        }
        printf("\nPass %d :\n",(i+1));
        printArray(arr);
       }
   }
   
   void main()
   {
    int arr[] = {19, 14, 18, 11, 12};
    
    printf("input array: ");
    printArray(arr);
    
    bubbleSort(arr);
    
    printf("\nsorted array: ");
    printArray(arr);
    
    getch();
   }
   
   



6. Write a program to perform the Selection sort.
   

   
   

   // selection sort program
   
   #include <stdio.h>
   #include <conio.h>
   
   #define n 5
   
   
   void selectionSort(int arr[])
   {
    int i, j, small, temp;
   
    // One by one move boundary of unsorted sub array
    for (i = 0; i < n-1; i++)
    {
     // Find the minimum element in unsorted array
     small = i;
     for (j = i+1; j < n; j++)
     if (arr[j] < arr[small])
      small = j;
   
     // Swap the found minimum element with the first element
     temp = arr[small];
     arr[small] = arr[i];
     arr[i] = temp;
    }
   }
   
   void printArray(int arr[])
   {
    int i;
    for (i=0; i < n; i++)
     printf("%d ", arr[i]);
    printf("\n");
   }
   
   void main()
   {
    int arr[] = {8, 13, 22, 17, 11};
     
    printf("input array: ");
    printArray(arr);
    
    selectionSort(arr);
    
    printf("Sorted array: ");
    printArray(arr);
    getch();
   }
   
   



7. Write a program to perform the Insertion sort.
   

   
   

   // insertion sort program
   
   #include <stdio.h>
   #include <conio.h>
   #include <math.h>
   #define n 5
   
   void insertionSort(int arr[])
   {
       int i, key, j;
       for (i = 1; i < n; i++)
       {
        key = arr[i];
        j = i-1;
        
        while (j >= 0 && arr[j] > key)
        {
         arr[j+1] = arr[j];
         j = j-1;
        }
        arr[j+1] = key;
       }
   }
   
   void printArray(int arr[])
   {
       int i;
       for (i=0; i < n; i++)
        printf("%d ", arr[i]);
       printf("\n");
   }
   
   void  main()
   {
    int arr[] = {8, 13, 22, 17, 11};
         
    printf("input data : ");
    printArray(arr);
    
    insertionSort(arr);
    
    printf("\nsorted data : ");
    printArray(arr);
   
    getch();
   }
   
   



8. Write a program to perform the heap sort.
   

   
   

   // Heap Sort program
   
   #include <stdio.h>
   #include <conio.h>
   
   #define size 6
   
   void swap(int *x, int *y)
   {
    int t = *x;
    *x = *y;
    *y = t;
   }
   // To heapify a subtree rooted with node i which is
   // an index in arr[]. n is size of heap
   void heapify(int arr[], int n, int i)
   {
    int largest = i;  // Initialize largest as root
    int l = 2*i + 1;  // left = 2*i + 1
    int r = 2*i + 2;  // right = 2*i + 2
   
    // If left child is larger than root
    if (l < n && arr[l] > arr[largest])
      largest = l;
   
    // If right child is larger than largest so far
    if (r < n && arr[r] > arr[largest])
      largest = r;
   
    // If largest is not root
    if (largest != i)
    {
     swap(&arr[i], &arr[largest]);
   
     // Recursively heapify the affected sub-tree
     heapify(arr, n, largest);
    }
   }
    
   // main function to do heap sort
   void heapSort(int arr[], int n)
   {
    // Build heap (rearrange array)
    for (int i = n / 2 - 1; i >= 0; i--)
     heapify(arr, n, i);
   
    // One by one extract an element from heap
    for (int i=n-1; i >= 0; i--)
    {
     // Move current root to end
     swap(&arr[0], &arr[i]);
   
     // call max heapify on the reduced heap
     heapify(arr, i, 0);
    }
   }
    
   /* A utility function to print array of size n */
   void printArray(int arr[])
   {
    for (int i=0; i < size; ++i)
      printf("%d ", arr[i]);
    printf("\n");
   }
    
   // Driver program
   void main()
   {
    int arr[] = {12, 11, 13, 5, 6, 7};
   
    heapSort(arr,size);
   
    printf("Sorted array is \n");
    printArray(arr);
   
    getch();
   }
   
   



9. Write a program to perform the Quick sort.
   

   
   

   /* C implementation QuickSort */
   
   #include <stdio.h>
   #include <conio.h>
    
   // function to swap two elements
   void swap(int* a, int* b)
   {
    int t = *a;
    *a = *b;
    *b = t;
   }
    
   /*  This function takes last element as pivot, places the pivot element at its correct   position in sorted array, and places all smaller (smaller than pivot) to left of pivot  and all greater elements to right of pivot */
   int partition (int arr[], int low, int high)
   {
    int pivot = arr[high];    // pivot
    int i = (low - 1);  // Index of smaller element
   
    for (int j = low; j <= high- 1; j++)
    {
      // If current element is smaller than or equal to pivot
      if (arr[j] <= pivot)
      {
       i++;    // increment index of smaller element
       swap(&arr[i], &arr[j]);
      }
    }
    swap(&arr[i + 1], &arr[high]);
    return (i + 1);
   }
    
   /* The main function that implements QuickSort
     arr[] --> Array to be sorted,
     low  --> Starting index,
     high  --> Ending index */
   void quickSort(int arr[], int low, int high)
   {
    if (low < high)
    {
     /* pi is partitioning index, arr[p] is now at right place */
     int pi = partition(arr, low, high);
   
     // Separately sort elements before partition and after partition
     quickSort(arr, low, pi - 1);
     quickSort(arr, pi + 1, high);
    }
   }
    
   /* Function to print an array */
   void printArray(int arr[], int size)
   {
    int i;
    for (i=0; i < size; i++)
      printf("%d ", arr[i]);
    printf("\n");
   }
    
   // Driver program to test above functions
   int main()
   {
    int arr[] = {5, 2, 4, 7, 1, 4, 3, 8, 9, 4, 6, 7, 10, 12, 2, 15};
    int n = sizeof(arr)/sizeof(arr[0]);
    quickSort(arr, 0, n-1);
    printf("Sorted array: \n");
    printArray(arr, n);
    getch();
    return 0;
   }
   
   



10. Write a program to perform the Merge Sort.
    

    
    

    // Merge Sort program
    
    #include <stdlib.h>
    #include <stdio.h>
    #include <conio.h>
    #define n 6
    
    void merge(int arr[], int l, int m, int r)
    {
     int i, j, k;
     int n1 = m - l + 1;
     int n2 = r - m;
    
     /* create temp arrays */
     int L[n1], R[n2];
    
     /* Copy data to temp arrays L[] and R[] */
     for (i = 0; i < n1; i++)
      L[i] = arr[l + i];
     for (j = 0; j < n2; j++)
      R[j] = arr[m + 1+ j];
    
     /* Merge the temp arrays back into arr[l..r]*/
     i = 0;   // Initial index of first subarray
     j = 0;   // Initial index of second subarray
     k = l;   // Initial index of merged subarray
     while (i < n1 && j < n2)
     {
      if (L[i] <= R[j])
      {
       arr[k] = L[i];
       i++;
      }
      else
      {
       arr[k] = R[j];
       j++;
      }
      k++;
     }
    
     /* Copy the remaining elements of L[], if there are any */
     while (i < n1)
     {
      arr[k] = L[i];
      i++;
      k++;
     }
     /* Copy the remaining elements of R[], if there are any */
     while (j < n2)
     {
      arr[k] = R[j];
      j++;
      k++;
     }
    }
    
    /* l is for left index and r is right index of the sub-array of arr to be sorted */
    void mergeSort(int arr[], int l, int r)
    {
     if (l < r)
     {
      // Same as (l+r)/2, but avoids overflow for large l and h
      int m = l+(r-l)/2;
    
      // Sort first and second halves
      mergeSort(arr, l, m);
      mergeSort(arr, m+1, r);
    
      merge(arr, l, m, r);
     }
    }
    
    void printArray(int A[])
    {
     int i;
     for (i=0; i < n; i++)
      printf("%d ", A[i]);
     printf("\n");
    }
    
    void main()
    {
     int arr[] = {33, 14, 27, 11, 22, 29};
    
     printf("Initial array is \n");
     printArray(arr);
    
     mergeSort(arr, 0, n - 1);
    
     printf("\nSorted array is \n");
     printArray(arr);
     
     getch();
    }
    
    



11. Write a program to perform the following types of Search:
    a. Linear Search
    b. Binary Search
    

    
    

    /* Linear Search */
    
    #include <stdio.h>
    #include <conio.h>
    
    // Linearly search x in arr[]. If x is present then return its 
    // location, otherwise return -1
    int search(int arr[], int n, int x)
    {
     int i;
     for (i=0; i < n; i++)
     {
      if (arr[i] == x)
       return i;
     }
     return -1;
    }
    
    int main()
    {
        int arr[]={8,44,64,22,34};
        int result, s=22;
        result = search(arr,5,s);
        if(result != -1)
            printf("%d found in array",s);
        else
            printf("%d not found in array",s);
     getch();
        return 1;
    }
    
    
    /* Binary Search*/
    
    #include <stdio.h>
    #include <conio.h>
    
    // function return index of element if present, otherwise -1
    int binarySearch(int arr[], int l, int r, int x)
    {
        if (r >= l)
        {
          int mid = l + (r - l)/2;
        
          // If the element is present at the middle itself
          if (arr[mid] == x) return mid;
        
          // If element is smaller than mid, then it can only be present in left subarray
          if (arr[mid] > x) return binarySearch(arr, l, mid-1, x);
        
          // Else the element can only be present in right subarray
          return binarySearch(arr, mid+1, r, x);
        }
        
        // We reach here when element is not present in array
        return -1;
    }
    
    int main(void)
    {
        int arr[] = {2, 3, 4, 10, 40};
        int n = sizeof(arr) / sizeof(arr[0]);
        int x = 10;
        int result = binarySearch(arr, 0, n-1, x);
        if(result != -1)
            printf("%d found in array", x);
        else
            printf("%d not found in array", x);
        getch();
        return 0;
    }
    
    
    



12. Write a program to implement a Singly Linked List. Perform the following operations on Singly Linked List:
    a. Insert an element
    b. Delete an element
    c. Find the sum of elements of the list
    d. Count number of the nodes in the linked list
    e. Search a given elements in the linked list.
    f. Reverse the linked list.
    g. Make a copy of the given linked list
    h. Concatenate two linked list
    i. Merge two linked
    

    
    

    /* Program of single linked list*/
    // Note : this program not contains all functionality as they asked, add the remaining functionality as per your requirement.
    
    # include <stdio.h>
    # include <malloc.h>
    
    struct node
    {
     int info;
     struct node *link;
    }*start;
    
    
    
    void create_list(int data)
    {
     struct node *q,*tmp;
     tmp= (struct node*)malloc(sizeof(struct node));
     tmp->info=data;
     tmp->link=NULL;
    
     if(start==NULL) /*If list is empty */
      start=tmp;
     else
     {       /*Element inserted at the end */
      q=start;
      while(q->link!=NULL)
       q=q->link;
      q->link=tmp;
     }
    }/*End of create_list()*/
    
    void addatbeg(int data)
    {
     struct node *tmp;
     tmp=(struct node *)malloc(sizeof(struct node));
     tmp->info=data;
     tmp->link=start;
     start=tmp;
    }/*End of addatbeg()*/
    
    void addafter(int data,int pos)
    {
     struct node *tmp,*q;
     int i;
     q=start;
     for(i=0;i < pos-1;i++)
     {
      q=q->link;
      if(q==NULL)
      {
       printf("There are less than %d elements",pos);
       return;
      }
     }/*End of for*/
    
     tmp=(struct node *)malloc(sizeof(struct node) );
     tmp->link=q->link;
     tmp->info=data;
     q->link=tmp;
    }/*End of addafter()*/
    
    void del(int data)
    {
     struct node *tmp,*q;
     if(start->info == data)
     {
      tmp=start;
      start=start->link;  /*First element deleted*/
      free(tmp);
      return;
     }
     q=start;
     while(q->link->link != NULL)
     {
      if(q->link->info==data)     /*Element deleted in between*/
      {
       tmp=q->link;
       q->link=tmp->link;
       free(tmp);
       return;
      }
      q=q->link;
     }/*End of while */
     if(q->link->info==data)    /*Last element deleted*/
     {
      tmp=q->link;
      free(tmp);
      q->link=NULL;
      return;
     }
     printf("Element %d not found\n",data);
    }/*End of del()*/
    
    void display()
    {
     struct node *q;
     if(start == NULL)
     {
      printf("List is empty\n");
      return;
     }
     q=start;
     printf("List is :\n");
     while(q!=NULL)
     {
      printf("%d ", q->info);
      q=q->link;
     }
     printf("\n");
    }/*End of display() */
    
    void count()
    {
     struct node *q=start;
     int cnt=0;
     while(q!=NULL)
     {
      q=q->link;
      cnt++;
     }
     printf("Number of elements are %d\n",cnt);
    }/*End of count() */
    
    void rev()
    {
     struct node *p1,*p2,*p3;
     if(start->link==NULL)    /*only one element*/
      return;
     p1=start;
     p2=p1->link;
     p3=p2->link;
     p1->link=NULL;
     p2->link=p1;
     while(p3!=NULL)
     {
      p1=p2;
      p2=p3;
      p3=p3->link;
      p2->link=p1;
     }
     start=p2;
    }/*End of rev()*/
    
    void search(int data)
    {
     struct node *ptr = start;
     int pos = 1;
     while(ptr!=NULL)
     {
      if(ptr->info==data)
      {
       printf("Item %d found at position %d\n",data,pos);
       return;
      }
      ptr = ptr->link;
      pos++;
     }
     if(ptr == NULL)
      printf("Item %d not found in list\n",data);
    }/*End of search()*/
    
    int main()
    {
     int choice,n,m,position,i;
     start=NULL;
     choice=1;
     
     while(choice !=0)
     {
      printf("1.Create List\n");
      printf("2.Add at begining\n");
      printf("3.Add after \n");
      printf("4.Delete\n");
      printf("5.Display\n");
      printf("6.Count\n");
      printf("7.Reverse\n");
      printf("8.Search\n");
      printf("9.Quit\n");
      printf("Enter your choice : ");
      scanf("%d",&choice);
      switch(choice)
      {
       case 1:
       printf("How many nodes you want : ");
       scanf("%d",&n);
       for(i=0;i < n;i++)
       {
        printf("Enter the element : ");
        scanf("%d",&m);
        create_list(m);
       }
       break;
       case 2:
       printf("Enter the element : ");
       scanf("%d",&m);
       addatbeg(m);
       break;
       case 3:
       printf("Enter the element : ");
       scanf("%d",&m);
       printf("Enter the position after which this element is inserted : ");
       scanf("%d",&position);
       addafter(m,position);
       break;
       case 4:
       if(start==NULL)
       {
        printf("List is empty\n");
        continue;
       }
       printf("Enter the element for deletion : ");
       scanf("%d",&m);
       del(m);
       break;
       case 5:
       display();
       break;
       case 6:
       count();
       break;
       case 7:
       rev();
       break;
       case 8:
       printf("Enter the element to be searched : ");
       scanf("%d",&m);
       search(m);
       break;
       case 9:
       return 1;
       default:
       printf("Wrong choice\n");
      }/*End of switch */ 
     }/*End of while */
     return 1;
    }/*End of main()*/
    
    

SQL Practical List

1. EMPLOYEE( emp_id, emp_name, birth_date, gender, dept_no, address, designation, salary, experience, email)
   DEPARTMENT( dept_no, dept_name, total_employees, location )
   

   
   

   
   Prepared By : Dhodakiya Kajal B. & Khimji Karangiya
   
   (1) 1. Create the above given tables with all necessary constraints such as In EMP TABLE : Employee id should be primary key, Department no should be Foreign key, employee age (birth_date) should be greater than 18 years, salary should be greater than zero, email should have (@ and dot) sign in address, designation of employee can be “manager”, “clerk”, “leader”, “analyst”, “designer”, “coder”, “tester”. 
   ANS.
   create table EMPLOYEE_SET_1
   (
   emp_id int primary key, 
   emp_name varchar2(30), 
   birth_date date, 
   gender varchar2(6), 
   dept_no int references DEPARTMENT_SET_1, 
   address varchar2(200), 
   designation varchar2(20) CHECK(designation='manager' OR designation='clerk' OR designation='leader' OR designation='analyst' OR designation='designer' OR designation='coder' OR designation='tester'), 
   salary number(10,2)CHECK(salary>0),
   experience varchar2(15), 
   email varchar2(255) CHECK((instr(email,'@')!=0) AND (instr(email,'.')!=0)),
   sdate date default sysdate
   );
   select *from  EMPLOYEE_SET_1
   desc  employee_SET_1
   drop table EMPLOYEE_SET_1
   Alter table EMPLOYEE_SET_1  add constraint CKbirth_date CHECK((sdate-birth_date)>18*365);
   In DEPT TABLE: Department no should be primary key, department name should be unique.
   ANS.
   create table DEPARTMENT_SET_1
   ( 
   dept_no int primary key, 
   dept_name varchar2(30) unique, 
   total_employees int, 
   location varchar2(100)
   );
   ===============================
   select  * from  DEPARTMENT_SET_1
   desc DEPARTMENT_SET_1
   drop table DEPARTMENT_SET_1
   =======================================
   (2)  After creation of above tables, modify Employee table by adding the constraints as ‘Male’ or ‘Female’ in gender field and display the structure .
   ANS.
   Alter table EMPLOYEE_SET_1 add constraints  CKgender CHECK(gender='male' OR gender='female');
   ----------------------------------------------------------------------------------------------
   (3) Insert proper data (at least 5 appropriate records) in all the tables.
   ANS.
   insert into EMPLOYEE_SET_1 values(1001,'Ajay','14-jan-95','male',1,'vajasura para street no 10 jasdan','tester',3000, 3,'ajay11@email.com','9-jan-17');
   insert into EMPLOYEE_SET_1 values(1002,'kishan','06-aug-93','male',3,'vajasura para street no 10 jasdan','coder',7000, 5,'kishan21@email.com','19-jan-17');
   insert into EMPLOYEE_SET_1 values(1004,'sanjay','21-sep-98','male',2,'sakadi street gadhada','manager',8000, 7,'sanjay17@email.com','9-sep-17');
   insert into EMPLOYEE_SET_1 values(1003,'kajal','20-sep-95','female',4,'vajasura para street no 10 jasdan','designer',9000, 8,'kajal22@email.com','9-sep-17');
   insert into EMPLOYEE_SET_1 values(1005,'Alisha','28-sep-91','female',5,'sakadi street gadhada','leader',6000, 2,'ela143@email.com','9-may-17');
   -----------------------------------------------------------------------------------------------
   ***************************************************************************************
   insert into DEPARTMENT_SET_1 values(1,'MCA',30,'sunshine college Rajkot');
   insert into DEPARTMENT_SET_1 values(2,'MBA',20,'sunshine college Rajkot');
   insert into DEPARTMENT_SET_1 values(3,'IMBA',35,'sunshine college Rajkot');
   insert into DEPARTMENT_SET_1 values(4,'BWOK',33,'sunshine college Rajkot');
   insert into DEPARTMENT_SET_1 values(5,'MCOM',18,'sunshine college Rajkot');
   ************************************************************************************
   (4) List all records of each table in ascending order.
   ANS.   select * from EMPLOYEE_SET_1 order by emp_name ASC;
         ------------------------------------------------------------------------------------------
   (5) Delete the department whose total number of employees less than 1.
          ANS.   delete from employee_SET_1  where dept_no in(select dept_no from department_SET_1  where    total_employees < 1);
       ---------------------------------------------------------------------------------------------
   (6) Find the names of the employee who has salary less than 5000 and greater than 2000.
   ANS.   select  *  from employee_SET_1  where  salary < 50000 and salary > 20000;
          ------------------------------------------------------------------------------------------
   
   (7) Display the names and the designation of all female employee in descending order.
   ANS.   Select  emp_name, designation from employee_SET_1  order by 1 desc;
   -------------------------------------------------------------------------------------------------
   
   
   (8)  Display the names of all the employees who names starts with ‘A’ ends with ‘A’.
   ANS.   select * from employee_SET_1  where  emp_name like 'A%a';
   -------------------------------------------------------------------------------------------------
   (9) Find the name of employee and salary for those who had obtain minimum salary.
   ANS.   select emp_name, salary from employee_SET_1  where  salary = (select  min(salary) from employee_SET_1);
   --------------------------------------------------------------------------------------------------
   (10)  Add 10% raise in salary of all employees whose department is ‘IT’.
   ANS.  update employee_SET_1  set  salary = salary + (salary * 10/100)  where  dept_no = 1;
   --------------------------------------------------------------------------------------------------
   (11) Count total number of employees of ‘MCA’ department.
   
   ANS.  select total_employees from department_SET_1 where dept_name='MCA';
   ---------------------------------------------------------------------------------------------------
   (12) List all employees who born in the current month.
   ANS.  select * from employee _SET_1 where extract(month from sysdate) = extract (month from birth_date);
        -----------------------------------------------------------------------------------------------
   (13)  Print the record of employee and dept table as “Employee works in department ‘MBA’.
   
   ANS. 
   select  employee_SET_1 .emp_name || ' is ' ||department_SET_1 .dept_name from employee_SET_1  inner join department_SET_1  on employee_SET_1.dept_no=department_SET_1.dept_no where employee_SET_1.gender='male';
   -----------------------------------------------------------------------------------------------------
   (14) List names of employees who are fresher’s ( less than 1 year of experience).
   
   ANS.  select emp_name from employee_SET_1  where experience  <  1;
   -----------------------------------------------------------------------------------------------------
   (15) List department wise names of employees who has more than 5 years of experience.
   
   ANS.  select emp_name from employee_SET_1 where experience  >  5;
   -----------------------------------------------------------------------------------------------------