ComputerEngineeringPrograms

We are the programmer of our life! Great things never comes from comfort zone! Failure, challenges, insults, past are steps leading to success. Catch the challenges from people and give them answer from your action by getting success from it! Thanks for visiting my blog. - Navin Waghwani Thanks Sonali Dhurjad for contributing your valuable time for sharing knowledge on this blog. I appreciate your hard work and dedication towards learing and sharing new things :)

Saturday, 9 December 2017

Principles of Modern Compiler Design Study Notes (for Reference)

Please follow below link.....

https://drive.google.com/open?id=1CuF95jxnzBjNVG96s6JBfyplgSPIZPVQ

Thursday, 26 October 2017

CORE JAVA CONCEPTUAL PROGRAMS (For Reference only)

****************************************CORE JAVA BASIC PROGRAMS**********************************

1.HELLO WORLD in Java - hello.java

public class hello {
public static void main(String[] args) {
System.out.println("Hello World"); //print Hello World
}
}

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2. PRIMITIVE DATATYPES - variables.java

public class variables {
public static void main(String[] args) {
int myNumber=12;
short myShort=234;
long myLong=78788;
double myDouble= 7.5656;
float myFloat=6878.7f;
char myChar='S';
boolean myBoolean1= false;
boolean myBoolean2=true;
byte myByte=127;

System.out.println(myNumber);
System.out.println(myShort);
System.out.println(myLong);
System.out.println(myDouble);
System.out.println(myFloat);
System.out.println(myChar);
System.out.println(myBoolean1);
System.out.println(myBoolean2);
System.out.println(myByte);
}
}

OUTPUT :
12
234
78788
7.5656
6878.7
S
false
true
127

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

3.STRING DATATYPE AND THEIR CONCATENATION -strings.java

public class strings {

public static void main(String[] args) {
String myString="Hello....";
System.out.println(myString);

String s1="Sonali";
String day="Monday";
System.out.println(myString+" My name is "+s1+" ...Today is "+day);
}
}

OUTPUT :

Hello....

Hello.... My name is Sonali ...Today is Monday

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

4.WHILE LOOP - loop.java

public class loop {

public static void main(String[] args) {
int i = 0;

while (i < 10) {
System.out.println("Loop " + i);
i++;
}
}
}

OUTPUT :

Loop 0
Loop 1
Loop 2
Loop 3
Loop 4
Loop 5
Loop 6
Loop 7
Loop 8

Loop 9

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

5.FOR LOOP

public class forLoop {

public static void main(String[] args) {
for (int i = 0; i < 10; i++) {
System.out.printf("This is %d th loop...\n", i);
}
}
}

OUTPUT :

This is 0 th loop...
This is 1 th loop...
This is 2 th loop...
This is 3 th loop...
This is 4 th loop...
This is 5 th loop...
This is 6 th loop...
This is 7 th loop..
This is 9 th loop...

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

6.IF LOOP : ifLoop.java

public class ifLoop {

public static void main(String[] args) {
if(2<5){
System.out.println("Second number is greater...");
}
}

}

OUTPUT :

Second number is greater...

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

7.GETTER USER INPUT : inputUser.java

import java.util.Scanner;

public class inputUser {

public static void main(String[] args) {
Scanner sc = new Scanner(System.in);

System.out.println("Enter a text input");
String text = sc.nextLine();
System.out.println("Entered value is : " + text);

System.out.println("Enter an integer value");
int intvalue = sc.nextInt();
System.out.println("Entered integer value is : " + intvalue);

System.out.println("Enter an float value");
float floatvalue = sc.nextFloat();
System.out.println("Entered float value is : " + floatvalue);

System.out.println("Enter an double value");
double doublevalue = sc.nextDouble();
System.out.println("Entered double value is : " + doublevalue);

}

}

OUTPUT :

Enter a text input
hello
Entered value is : hello
Enter an integer value
3
Entered integer value is : 3
Enter an float value
4.5
Entered float value is : 4.5
Enter an double value
2324.4

Entered double value is : 2324.4

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Saturday, 16 September 2017

Design and Analysis of Algorithms Study Notes

ASYMPTOTIC NOTATIONS
https://drive.google.com/open?id=0B9zJxhTsrja5ekg4akRBZ0F4RGM

SORTING METHODS
https://drive.google.com/open?id=0B9zJxhTsrja5YTRGajNXYTQ1VWc

Please Click following link

https://drive.google.com/open?id=1u7mNTxmt5D-Uxt88RMtAOOhOk6vd8tV-

Saturday, 9 September 2017

Ionic, Node.js, MongoDB and Loopback Installation Steps

STEP 1 : Node.js Installation
Download from following link and install Nodejs
https://nodejs.org/en/

STEP 2 : Ionic Installation
Open command terminal and run following command.
https://ionicframework.com/getting-started/

> npm install -g cordova ionic

To check ionic install or not.
> ionic

Create your project directory using mkdir and run the following command in the directory.
> ionic start myApp --type=ionic1 ...(ionic1 is the version and myApp is the application name)

STEP 3 : Mongodb Installation

Download Mongodb executable from below link.
https://www.mongodb.com/download-center?jmp=homepage#community

Refer this video link
https://youtu.be/1uFY60CESlM

Monday, 4 September 2017

Digital Signal Processing Notes

PLEASE FOLLOW THIS LINK

https://drive.google.com/open?id=0B9zJxhTsrja5bGpvbjliQzYxR2s

ORACLE SQL Reference Queries for beginners

/****************************DAY1: 5 JUNE 2017,MONDAY************************/
--INSERT INTO TABLES
INSERT
INTO CUSTOMER
    (
      CUSTOMER_ID,
      FIRST_NAME,
      LAST_NAME,
      MIDDLE_NAME,
      ADDRESS_LINE1,
      ADDRESS_LINE2,
      CITY,
      COUNTRY,
      DATE_ADDED,
      REGION
    )
    VALUES
    (
      10,
      'JOHN',
      'AMIRTHRAJ',
      'T',
      '2345 PETERDRIVE',
      NULL,
      'Atlanta',
      'USA',
      to_date('12-JAN-15','DD-MON-YY'),
      'SOUTH'
    );
INSERT
INTO CUSTOMER
    (
      CUSTOMER_ID,
      FIRST_NAME,
      LAST_NAME,
      MIDDLE_NAME,
      ADDRESS_LINE1,
      ADDRESS_LINE2,
      CITY,
      COUNTRY,
      DATE_ADDED,
      REGION
    )
    VALUES
    (
      11,
      'TOM',
      'JOSEPH',
      'A',
      '123SANDY DRIVE',
      NULL,
      'New York',
      'USA',
      to_date('12-JAN-15','DD-MON-YY'),
      'SOUTH'
    );
INSERT
INTO CUSTOMER
    (
      CUSTOMER_ID,
      FIRST_NAME,
      LAST_NAME,
      MIDDLE_NAME,
      ADDRESS_LINE1,
      ADDRESS_LINE2,
      CITY,
      COUNTRY,
      DATE_ADDED,
      REGION
    )
    VALUES
    (
      12,
      'PETER',
      'MANN',
      'J',
      '3456 GATES DRIVE',
      NULL,
      'Washington',
      'USA',
      to_date('13-JAN-15','DD-MON-YY'),
      'NORTH'
    );
COMMIT;   /*COMMIT SAVES CHANGES IN DATABASE FOR CURRENT SESSION*/
INSERT
INTO PRODUCT
    (
      PRODUCT_ID,
      PRODUCT_NAME,
      STANDARD_COST,
      COLOR,
      LIST_PRICE,
      PRODUCT_SIZE,
      WEIGHT,
      PRODUCT_CATEGORY
    )
    VALUES
    (
      100,
      'Mobile Cover',
      30,
      NULL,
      35,10,100,
      'Mobile'
    );
INSERT
INTO PRODUCT
    (
      PRODUCT_ID,
      PRODUCT_NAME,
      STANDARD_COST,
      COLOR,
      LIST_PRICE,
      PRODUCT_SIZE,
      WEIGHT,
      PRODUCT_CATEGORY
    )
    VALUES
    (
      200,
      'Samsung F7100',
      80,
      'BLACK',
      35,6,50,
      'Mobile'
    );
INSERT
INTO PRODUCT
    (
      PRODUCT_ID,
      PRODUCT_NAME,
      STANDARD_COST,
      COLOR,
      LIST_PRICE,
      PRODUCT_SIZE,
      WEIGHT,
      PRODUCT_CATEGORY
    )
    VALUES
    (
      105,
      'HTC 7800',
      70,
      'BLACK',
      80,6,40,
      'Mobile'
    );
INSERT
INTO PRODUCT
    (
      PRODUCT_ID,
      PRODUCT_NAME,
      STANDARD_COST,
      COLOR,
      LIST_PRICE,
      PRODUCT_SIZE,
      WEIGHT,
      PRODUCT_CATEGORY
    )
    VALUES
    (
      106,
      'Microsoft Keyboard 7865',
      50,
      'BLACK',
      60,3,20,
      'Computer'
    );
INSERT
INTO PRODUCT
    (
      PRODUCT_ID,
      PRODUCT_NAME,
      STANDARD_COST,
      COLOR,
      LIST_PRICE,
      PRODUCT_SIZE,
      WEIGHT,
      PRODUCT_CATEGORY
    )
    VALUES
    (
      501,
      'Microsoft Mouse 7863',
      30,
      'GREY',
      40,1,25,
      'Computer'
    );
INSERT
INTO PRODUCT
    (
      PRODUCT_ID,
      PRODUCT_NAME,
      STANDARD_COST,
      COLOR,
      LIST_PRICE,
      PRODUCT_SIZE,
      WEIGHT,
      PRODUCT_CATEGORY
    )
    VALUES
    (
      101,
      'iPhone',
      500,
      'GOLD',
      600,6,20,
      'Mobile'
    );
COMMIT;
INSERT
INTO SALES
    (
      SALES_DATE,
      ORDER_ID,
      PRODUCT_ID,
      CUSTOMER_ID,
      SALESPERSON_ID,
      QUANTITY,
      UNIT_PRICE,
      SALES_AMOUNT,
      TAX_AMOUNT,
      TOTAL_AMOUNT
    )
    VALUES
    (
      to_date('01-FEB-15','DD-MON-YY'),
      1269,200,12,1000,20,80,1600,160,1760
    );
INSERT
INTO SALES
    (
      SALES_DATE,
      ORDER_ID,
      PRODUCT_ID,
      CUSTOMER_ID,
      SALESPERSON_ID,
      QUANTITY,
      UNIT_PRICE,
      SALES_AMOUNT,
      TAX_AMOUNT,
      TOTAL_AMOUNT
    )
    VALUES
    (
      to_date('01-JAN-15','DD-MON-YY'),
      1267,100,10,1000,2,20,40,4,44
    );
INSERT
INTO SALES
    (
      SALES_DATE,
      ORDER_ID,
      PRODUCT_ID,
      CUSTOMER_ID,
      SALESPERSON_ID,
      QUANTITY,
      UNIT_PRICE,
      SALES_AMOUNT,
      TAX_AMOUNT,
      TOTAL_AMOUNT
    )
    VALUES
    (
      to_date('01-JAN-15','DD-MON-YY'),
      1267,101,10,1000,2,30,60,6,66
    );
INSERT
INTO SALES
    (
      SALES_DATE,
      ORDER_ID,
      PRODUCT_ID,
      CUSTOMER_ID,
      SALESPERSON_ID,
      QUANTITY,
      UNIT_PRICE,
      SALES_AMOUNT,
      TAX_AMOUNT,
      TOTAL_AMOUNT
    )
    VALUES
    (
      to_date('02-JAN-15','DD-MON-YY'),
      1268,100,11,2000,10,30,300,30,330
    );
INSERT
INTO SALES
    (
      SALES_DATE,
      ORDER_ID,
      PRODUCT_ID,
      CUSTOMER_ID,
      SALESPERSON_ID,
      QUANTITY,
      UNIT_PRICE,
      SALES_AMOUNT,
      TAX_AMOUNT,
      TOTAL_AMOUNT
    )
    VALUES
    (
      to_date('09-FEB-15','DD-MON-YY'),
      1270,105,10,3000,20,70,1400,140,1540
    );
INSERT
INTO SALES
    (
      SALES_DATE,
      ORDER_ID,
      PRODUCT_ID,
      CUSTOMER_ID,
      SALESPERSON_ID,
      QUANTITY,
      UNIT_PRICE,
      SALES_AMOUNT,
      TAX_AMOUNT,
      TOTAL_AMOUNT
    )
    VALUES
    (
      to_date('09-FEB-15','DD-MON-YY'),
      1270,106,10,3000,10,50,500,50,550
    );
INSERT
INTO SALES
    (
      SALES_DATE,
      ORDER_ID,
      PRODUCT_ID,
      CUSTOMER_ID,
      SALESPERSON_ID,
      QUANTITY,
      UNIT_PRICE,
      SALES_AMOUNT,
      TAX_AMOUNT,
      TOTAL_AMOUNT
    )
    VALUES
    (
      to_date('09-FEB-15','DD-MON-YY'),
      1270,101,10,3000,10,30,300,30,330
    );
COMMIT;
INSERT
INTO SALES_HISTORY
    (
      SALES_DATE,
      ORDER_ID,
      PRODUCT_ID,
      CUSTOMER_ID,
      SALESPERSON_ID,
      QUANTITY,
      UNIT_PRICE,
      SALES_AMOUNT,
      TAX_AMOUNT,
      TOTAL_AMOUNT
    )
    VALUES
    (
      to_date('01-JAN-15','DD-MON-YY'),
      1267,100,10,1000,2,20,40,4,44
    );
INSERT
INTO SALES_HISTORY
    (
      SALES_DATE,
      ORDER_ID,
      PRODUCT_ID,
      CUSTOMER_ID,
      SALESPERSON_ID,
      QUANTITY,
      UNIT_PRICE,
      SALES_AMOUNT,
      TAX_AMOUNT,
      TOTAL_AMOUNT
    )
    VALUES
    (
      to_date('01-JAN-15','DD-MON-YY'),
      1267,101,10,1000,2,30,60,6,66
    );
INSERT
INTO SALES_HISTORY
    (
      SALES_DATE,
      ORDER_ID,
      PRODUCT_ID,
      CUSTOMER_ID,
      SALESPERSON_ID,
      QUANTITY,
      UNIT_PRICE,
      SALES_AMOUNT,
      TAX_AMOUNT,
      TOTAL_AMOUNT
    )
    VALUES
    (
      to_date('07-JAN-15','DD-MON-YY'),
      1268,100,11,2000,10,30,300,30,330
    );
COMMIT;
INSERT
INTO SALESPERSON
    (
      SALESPERSON_ID,
      JOB_TITLE,
      FIRST_NAME,
      LAST_NAME,
      MIDDLE_NAME,
      ADDRESS_LINE1,
      ADDRESS_LINE2,
      CITY,
      COUNTRY,
      DATE_ADDED
    )
    VALUES
    (
      1000,
      'South Manager',
      'PETER',
      'MANN',
      'R',
      '4567 ASHFORDROAD',
      NULL,
      'CUMMING',
      'USA',
      to_date('12-JAN-14','DD-MON-YY')
    );
INSERT
INTO SALESPERSON
    (
      SALESPERSON_ID,
      JOB_TITLE,
      FIRST_NAME,
      LAST_NAME,
      MIDDLE_NAME,
      ADDRESS_LINE1,
      ADDRESS_LINE2,
      CITY,
      COUNTRY,
      DATE_ADDED
    )
    VALUES
    (
      2000,
      'North Manager',
      'JOHN',
      'KING',
      'A',
      '67 DUNWOODY ROAD',
      NULL,
      'LASVEGAS',
      'USA',
      to_date('14-JAN-14','DD-MON-YY')
    );
INSERT
INTO SALESPERSON
    (
      SALESPERSON_ID,
      JOB_TITLE,
      FIRST_NAME,
      LAST_NAME,
      MIDDLE_NAME,
      ADDRESS_LINE1,
      ADDRESS_LINE2,
      CITY,
      COUNTRY,
      DATE_ADDED
    )
    VALUES
    (
      3000,
      'East Manager',
      'BOB',
      'MORIS',
      'A',
      '679 GLENDRIGEDR',
      NULL,
      'FLORIDA',
      'USA',
      to_date('14-FEB-14','DD-MON-YY')
    );
COMMIT;
/******************************EXERCISE 1*************************************/
--1.PRODUCE A LIST OF ALL COLUMNS IN SALES TABLE
SELECT * FROM SALES;

--2.PRODUCE A LIST OF ALL COLUMNS IN PRODUCT TABLE
SELECT * FROM PRODUCT;

--3.PRODUCE A LIST OF ALL COLUMNS IN CUSTOMER TABLE
SELECT * FROM CUSTOMER;

--4.PRODUCE A LIST OF ROWS SHOWING SALES DATE,ORDER NUMBER,PRODUCT NUMBER,
--CUSTOMER NUMBER,QUANTITY,TOTAL AMOUNT OF SALES TABLE
SELECT SALES_DATE,ORDER_ID,PRODUCT_ID,CUSTOMER_ID,QUANTITY,TOTAL_AMOUNT FROM SALES;

--5.PRODUCE A LIST OF ROWS SHOWING PRODUCT NUMBER,PRODUCT NAME,COLOR FROM PRODUCT
SELECT PRODUCT_ID,PRODUCT_NAME,COLOR FROM PRODUCT;

/*****************************EXERCISE 2**************************************/
--1.PRODUCE A LIST OF ROWS SHOWING ALL COLUMNS FROM THE SALES TABLE WHERE TOTAL
--AMOUNT GREATER THAN 1000
SELECT * FROM SALES WHERE TOTAL_AMOUNT>1000;

--2.PRODUCE A LIST OF ROWS SHOWING ALL COLUMNS FROM SALES TABLE WHERE TOTAL AMOUNT
--IS NOT EQUAL TO 44
SELECT * FROM SALES WHERE TOTAL_AMOUNT!=44;

--3.PRODUCE A LIST OF ROWS SHOWING ALL COLUMNS FROM SALES TABLE WHERE QUANTITY
--IS LESS THAN OR EQUAL TO 10
SELECT * FROM SALES WHERE QUANTITY<=10;

--4.PRODUCE A LIST OF ROWS SHOWING ALL COLUMNS FROM SALES TABLE WHERE SALES DATA
--IS EQUAL TO 9TH FEBRUARY 2015
SELECT * FROM SALES WHERE SALES_DATE='09-02-15';

--5.PRODUCE A LIST OF ROWS SHOWING ALL COLUMNS FROM PRODUCT TABLE WHERE COLOR
--IS EQUAL TO RED
SELECT * FROM PRODUCT WHERE COLOR='RED';

--6.PRODUCE LIST OF ROWS SHOWING ALL COLUMNS FROM SALES TABLE WHERE TOTAL AMOUNT
--IS GREATER THAN OR EQUAL TO SALES AMOUNT
SELECT * FROM SALES WHERE TOTAL_AMOUNT>=SALES_AMOUNT;

/*********************PRACTICE****************************/
SELECT * FROM SALES WHERE TOTAL_AMOUNT>1000 AND TOTAL_AMOUNT<2000;

SELECT * FROM SALES WHERE TOTAL_AMOUNT<1000 OR TOTAL_AMOUNT>1500;

SELECT * FROM SALES WHERE TOTAL_AMOUNT BETWEEN 1000 AND 2000;

SELECT * FROM SALES WHERE TOTAL_AMOUNT NOT BETWEEN 1000 AND 2000;

SELECT * FROM SALESPERSON;

SELECT * FROM SALESPERSON WHERE CITY IN ('CUMMING','FLORIDA');

SELECT * FROM SALESPERSON WHERE CITY NOT IN ('CUMMING','FLORIDA');

SELECT * FROM SALES_HISTORY;

SELECT * FROM SALES_HISTORY WHERE ORDER_ID LIKE '%67';

SELECT * FROM SALES WHERE UNIT_PRICE > ALL(20,30,40);

SELECT * FROM SALES WHERE UNIT_PRICE > ANY(30,40);

/********************************EXERCISE 3*********************************/
--1.PRODUCE A LIST OF ROWS SHOWING ALL COLUMNS FROM SALES TABLE WHERE
--TOTAL AMOUNT IS NOT BETWEEN 1 AND 100
SELECT * FROM SALES WHERE TOTAL_AMOUNT NOT BETWEEN 1 AND 100;

--2.PRODUCE A LIST OF ROWS SHOWING ALL COLUMNS FROM SALES TABLE WHERE
--QUANTITY HAS VALUES 2 OR 2 0R 10
SELECT * FROM SALES WHERE QUANTITY = ANY(20,2,10);

--3.PRODUCE A LIST OF ROWS SHOWING ALL COLUMNS FROM PRODUCT TABLE WHERE
--PRODUCT NAME STARTS WITH 'Mob'
SELECT * FROM PRODUCT WHERE PRODUCT_NAME LIKE 'Mob%';

--4.PRODUCE A LIST OF ROWS SHOWING ALL COLUMNS FROM SALES TABLE WHERE
--TOTAL AMOUNT IS GREATER THAN 50,100 AND 200
SELECT * FROM SALES WHERE TOTAL_AMOUNT > ALL(50,100,200);

--5.PRODUCE A LIST OF ROWS SHOWING ALL COLUMNS FROM PRODUCT TABLE WHERE
--COLOR IS EQUAL TO NULL VALUE
SELECT * FROM PRODUCT WHERE COLOR IS NULL;

--6.PRODUCE A LIST OF ROWS SHOWING ALL COLUMNS FROM SALES TABLE WHERE
--TOTAL AMOUNT IS GREATER THAN 100 AND QUANTITY LESS THAN 20
SELECT * FROM SALES WHERE TOTAL_AMOUNT>100 AND QUANTITY<20;

--QUERY TO VIEW CURRENT DATE FORMAT
SELECT VALUE FROM V$NLS_PARAMETERS WHERE PARAMETER ='NLS_DATE_FORMAT';

--QUERY TO GET CPU, EXECUTION TIME OF A QUERY
SELECT CPU_TIME,EXECUTIONS FROM V$SQLAREA WHERE UPPER (SQL_TEXT) LIKE 'SELECT * FROM PRODUCT';

/***********************************DAY 2 : 6 JUNE 2017,TUESDAY**********************/

SELECT FIRST_NAME||' '||LAST_NAME FROM CUSTOMER;

SELECT FIRST_NAME||' '||MIDDLE_NAME||' '||LAST_NAME FROM CUSTOMER;

SELECT FIRST_NAME||' '||MIDDLE_NAME||' '||LAST_NAME AS FULL_NAME FROM CUSTOMER;

SELECT 'MY NAME IS '||FIRST_NAME||' '||MIDDLE_NAME||' '||LAST_NAME AS FULL_NAME FROM CUSTOMER ;

SELECT 'ADDRESS OF '||FIRST_NAME||' '||LAST_NAME ||' IS '||ADDRESS_LINE1 AS ADDRESS FROM CUSTOMER;

/*************************************************EXERCISE 4**********************************************/
--1.PRODUCE A LIST OF ROWS SHOWING SALES DATE,PRODUCT NUMBER,ORDER NUMBER,SALES AMOUNT,
--TAX AMOUNT AND A CALCULATED COLUMN(SUM OF SALES AMOUNT) FROM SALES TABLE
SELECT SALES_DATE,PRODUCT_ID,ORDER_ID,SALES_AMOUNT,TAX_AMOUNT,SALES_AMOUNT+TAX_AMOUNT
AS CALCULATED_AMOUNT FROM SALES;

--2.PRODUCE A LIST OF ROWS SHOWING ALL COLUMNS FROM THE SALES TABLE WHERE QUANTITY PLUS
--10 IS GREATER THAN OR EQUAL TO 20
SELECT * FROM SALES WHERE QUANTITY+10>=20;

--3.PRODUCE A LIST OF ROWS SHOWING SALES DATE,PRODUCT NUMBER,ORDER NUMBER,SALES AMOUNT,
--TAX AMOUNT AND CALCULATED COLUMN(TOTAL AMOUNT MINUS 30) FROM SALES TABLE
SELECT SALES_DATE,PRODUCT_ID,ORDER_ID,SALES_AMOUNT,TAX_AMOUNT,TOTAL_AMOUNT,TOTAL_AMOUNT-30
AS CALCULATED_AMOUNT FROM SALES ;

/**************ORDER BY STATEMENT*******************/
SELECT * FROM CUSTOMER ORDER BY CITY;

SELECT * FROM PRODUCT ORDER BY LIST_PRICE;

SELECT * FROM PRODUCT ORDER BY LIST_PRICE DESC;

SELECT LIST_PRICE,PRODUCT_NAME,COLOR FROM PRODUCT ORDER BY LIST_PRICE DESC;

SELECT PRODUCT_ID,PRODUCT_SIZE FROM PRODUCT ORDER BY PRODUCT_ID ASC;

SELECT PRODUCT_ID,PRODUCT_SIZE FROM PRODUCT ORDER BY PRODUCT_ID DESC;

/**************************EXERCISE 5**************************************/
--1.PRODUCE A LIST OF ALL ROWS SHOWING SALES DATE,PRODUCT NUMBER,ORDER NUMBER
--SALES AMOUNT,TAX AMOUNT FROM THE SALES TABLE AND SORT IT BY COLUMN TAX AMOUNT
--ASCENDING ORDER.
SELECT SALES_DATE,PRODUCT_ID,ORDER_ID,SALES_AMOUNT,TAX_AMOUNT FROM SALES ORDER BY TAX_AMOUNT ASC;

--2.PRODUCE A LIST OF ALL ROWS SHOWING SALES DATE,PRODUCT NUMBER,ORDER NUMBER
--SALES AMOUNT,TAX AMOUNT FROM THE SALES TABLE AND SORT IT BY COLUMN SALES AMOUNT
--AND TAX AMOUNT IN ASCENDING ORDER.
SELECT SALES_DATE,PRODUCT_ID,ORDER_ID,SALES_AMOUNT,TAX_AMOUNT FROM SALES ORDER BY SALES_AMOUNT,TAX_AMOUNT ASC;

--3.PRODUCE A LIST OF ALL ROWS SHOWING ORDER NUMBER,SALES DATE,PRODUCT NUMBER
--SALES AMOUNT,TAX AMOUNT FROM THE SALES TABLE AND SORT IT BY COLUMN ORDER NUMBER
-- IN DESCENDING ORDER.
SELECT ORDER_ID,SALES_DATE,PRODUCT_ID,SALES_AMOUNT,TAX_AMOUNT FROM SALES ORDER BY ORDER_ID DESC;

/*******************************UNION*************************/

SELECT SALES_DATE FROM SALES_HISTORY
UNION SELECT SALES_DATE FROM SALES
ORDER BY SALES_DATE;

SELECT SALES_DATE,CUSTOMER_ID FROM SALES_HISTORY
UNION SELECT SALES_DATE,CUSTOMER_ID FROM SALES
ORDER BY SALES_DATE;

SELECT UNIT_PRICE,QUANTITY FROM SALES_HISTORY     /*DUPLICATE VALUES ALSO DISPLAY*/
UNION ALL SELECT UNIT_PRICE,QUANTITY FROM SALES
ORDER BY QUANTITY;

SELECT UNIT_PRICE,QUANTITY,SALES_DATE FROM SALES_HISTORY
UNION ALL SELECT UNIT_PRICE,QUANTITY,SALES_DATE FROM SALES
ORDER BY UNIT_PRICE;

/**************************EXERCISE 6********************************/
--1.PRODUCE A LIST OF ALL ROWS SHOWING ORDER NO BY COMBINING DATA FROM SALES AND SALES HISTORY TABLE.
SELECT ORDER_ID FROM SALES
UNION ALL
SELECT ORDER_ID FROM SALES_HISTORY;

--2.PRODUCE A LIST OF DISTINCT ROWS FROM SALES & SALES HISTORY TABLE USING COLUMN ORDER NUMBER.
SELECT ORDER_ID FROM SALES
UNION
SELECT ORDER_ID FROM SALES_HISTORY;

--3.PRODUCE A LIST OF ROWS WHICH ARE PRESENT IN SALES AND SALES HISTORY TABLE USING COLUMN ORDER NO.
SELECT ORDER_ID FROM SALES
INTERSECT
SELECT ORDER_ID FROM SALES_HISTORY;

--4.PRODUCE A LIST OF ROWS WHICH ARE PRESENT IN SALES TABLE AND ARE NOT PRESENT IN SALES HISTORY TABLE
--USING COLUMN ORDER NO
SELECT ORDER_ID FROM SALES
MINUS
SELECT ORDER_ID FROM SALES_HISTORY;

/**************************AGGREGATE FUNCTIONS**************************/

SELECT * FROM SALES;

SELECT MIN(UNIT_PRICE) FROM SALES; --MIN FUNCTION

SELECT MAX(UNIT_PRICE) FROM SALES; --MAX FUNCTION

SELECT AVG(UNIT_PRICE) FROM SALES; --AVG FUNCTION

SELECT COUNT(UNIT_PRICE) FROM SALES;--COUNT FUNCTION

SELECT SUM(UNIT_PRICE) FROM SALES; --SUM FUNCTION

SELECT COUNT (*) FROM SALES ;       --COUNT OF ROWS IN SALES TABLE

SELECT VARIANCE (UNIT_PRICE) FROM SALES; --VARIANCE FUNCTION

SELECT STDDEV(UNIT_PRICE) FROM SALES;--STANDARD DEVIATION FUNCTION

SELECT COUNT(SALES_DATE)FROM SALES; --COUNT OF RECORDS IN SALES_DATE COLUMN

SELECT * FROM PRODUCT;        /*COUNT DOES NOT TAKE NULL VALUES*/
SELECT COUNT(COLOR)FROM PRODUCT;

/*--GROUP BY CLAUSE--*/
SELECT * FROM SALES;
SELECT AVG(SALES_AMOUNT) FROM SALES
GROUP BY SALES_DATE;

SELECT * FROM PRODUCT;
SELECT COLOR,MAX(LIST_PRICE) FROM PRODUCT
GROUP BY COLOR;

SELECT * FROM SALES;
SELECT SUM(QUANTITY) FROM SALES
GROUP BY SALES_DATE
ORDER BY SALES_DATE;

SELECT * FROM SALES;
SELECT SALES_DATE,AVG(QUANTITY) FROM SALES
GROUP BY SALES_DATE
ORDER BY SALES_DATE;

SELECT * FROM SALES;
SELECT SALES_DATE,MIN(QUANTITY) FROM SALES
GROUP BY SALES_DATE
ORDER BY SALES_DATE;

/*--HAVING CLAUSE--*/
SELECT * FROM SALES;                    /*SALES TABLE*/
SELECT SALES_DATE,PRODUCT_ID,SUM(SALES_AMOUNT)
FROM SALES
GROUP BY SALES_DATE,PRODUCT_ID
ORDER BY SALES_DATE;

SELECT * FROM PRODUCT;                  /*PRODUCT TABLE*/
SELECT COUNT(PRODUCT_CATEGORY) FROM PRODUCT;

SELECT COUNT(*)FROM PRODUCT;

SELECT COUNT(DISTINCT PRODUCT_CATEGORY) FROM PRODUCT;

SELECT COUNT(DISTINCT COLOR) FROM PRODUCT;

/**********************************EXERCISE 7***************************************/
--1.WRITE A QUERY THAT SHOWS THE NUMBER OF ROWS IN SALES TABLE.
SELECT COUNT(*)FROM SALES;

--2.PRODUCE A LIST WHICH SHOWS TOTAL SALES VOLUME BROKEN BY SALES DATE FROM SALES TABLE.
SELECT * FROM SALES;
SELECT SALES_DATE,SUM(TOTAL_AMOUNT)
FROM SALES
GROUP BY SALES_DATE;

--3.PRODUCE A LIST WHICH SHOWS MAXIMUM SALES VOLUME BROKEN BY SALES DATE AND ORDER NUMBER FROM SALES TABLE.
SELECT ORDER_ID,SALES_DATE,MAX(TOTAL_AMOUNT)
FROM SALES
GROUP BY ORDER_ID,SALES_DATE;

--4.PRODUCE A LIST WHICH SHOWS MINIMUM SALES VOLUME BROKEN BY SALES DATE FROM SALES TABLE
--AND MINIMUM SALES VOLUME IS LESS THAN 100.
SELECT SALES_DATE,MIN(TOTAL_AMOUNT)
FROM SALES
GROUP BY SALES_DATE
HAVING MIN(TOTAL_AMOUNT)<100;

/*********************JOINS***************************/
SELECT * FROM PRODUCT;
SELECT * FROM SALES;

SELECT SALES.PRODUCT_ID ,SALES.SALES_DATE,SALES_HISTORY.ORDER_ID FROM SALES
INNER JOIN SALES_HISTORY
ON SALES.PRODUCT_ID=SALES_HISTORY.ORDER_ID;

SELECT S.SALES_DATE,S.ORDER_ID,S.PRODUCT_ID,P.PRODUCT_NAME
FROM SALES S
JOIN PRODUCT P
ON S.PRODUCT_ID=P.PRODUCT_ID;

SELECT S.SALES_DATE,S.ORDER_ID,S.PRODUCT_ID,P.PRODUCT_NAME
FROM SALES S
INNER JOIN PRODUCT P
ON S.PRODUCT_ID=P.PRODUCT_ID;

SELECT S.SALESPERSON_ID,S.CUSTOMER_ID,P.CUSTOMER_ID,P.CITY
FROM SALES S
INNER JOIN CUSTOMER P
ON S.CUSTOMER_ID=P.CUSTOMER_ID;

SELECT K.CUSTOMER_ID,K.FIRST_NAME,S.CUSTOMER_ID,S.QUANTITY,S.PRODUCT_ID,P.PRODUCT_ID
FROM CUSTOMER K
JOIN SALES S
ON K.CUSTOMER_ID=S.CUSTOMER_ID
JOIN PRODUCT P
ON S.PRODUCT_ID=P.PRODUCT_ID;

SELECT S.SALES_DATE,S.ORDER_ID,S.PRODUCT_ID,P.PRODUCT_NAME
FROM SALES S
JOIN PRODUCT P
ON S.PRODUCT_ID=P.PRODUCT_ID;

SELECT S.SALES_DATE,S.ORDER_ID,S.PRODUCT_ID,P.PRODUCT_NAME
FROM SALES S
INNER JOIN PRODUCT P
ON S.PRODUCT_ID=P.PRODUCT_ID;

SELECT S.SALESPERSON_ID,S.CUSTOMER_ID,P.CUSTOMER_ID,P.CITY
FROM SALES S
INNER JOIN CUSTOMER P
ON S.CUSTOMER_ID=P.CUSTOMER_ID;

SELECT K.CUSTOMER_ID,K.FIRST_NAME,S.CUSTOMER_ID,S.QUANTITY,S.PRODUCT_ID,P.PRODUCT_ID
FROM CUSTOMER K                                           --MULTIPLE JOIN
JOIN SALES S
ON K.CUSTOMER_ID=S.CUSTOMER_ID
JOIN PRODUCT P
ON S.PRODUCT_ID=P.PRODUCT_ID;

/*--INNER JOIN EXAMPLES--*/
SELECT P.PRODUCT_ID,P.PRODUCT_NAME,S.PRODUCT_ID            --INNER JOIN
FROM PRODUCT P
INNER JOIN SALES S
ON P.PRODUCT_ID=S.PRODUCT_ID;

SELECT P.PRODUCT_ID,P.PRODUCT_NAME,S.PRODUCT_ID --INNER JOIN USING WHERE CLAUSE
FROM PRODUCT P
INNER JOIN SALES S
ON P.PRODUCT_ID=S.PRODUCT_ID
WHERE P.PRODUCT_ID>105;

SELECT P.PRODUCT_ID,P.PRODUCT_NAME,S.PRODUCT_ID --INNER JOIN USING ORDER BY CLAUSE
FROM PRODUCT P
INNER JOIN SALES S
ON P.PRODUCT_ID=S.PRODUCT_ID
ORDER BY P.PRODUCT_NAME;

SELECT P.PRODUCT_ID,P.PRODUCT_NAME,S.PRODUCT_ID --INNER JOIN USING ORDER BY AND WHERE CLAUSE
FROM PRODUCT P                                   --ANSI JOIN
INNER JOIN SALES S
ON P.PRODUCT_ID=S.PRODUCT_ID
WHERE P.PRODUCT_ID>105
ORDER BY P.PRODUCT_NAME;

SELECT P.PRODUCT_NAME,P.PRODUCT_ID,S.PRODUCT_ID   --NON ANSI JOIN
FROM PRODUCT P,SALES S
WHERE P.PRODUCT_ID=S.PRODUCT_ID;

SELECT P.PRODUCT_NAME,P.PRODUCT_ID,S.PRODUCT_ID   --ORDER BY
FROM PRODUCT P,SALES S
WHERE P.PRODUCT_ID=S.PRODUCT_ID
ORDER BY P.PRODUCT_NAME;

SELECT P.PRODUCT_ID,P.PRODUCT_NAME,S.PRODUCT_ID
FROM PRODUCT P
LEFT OUTER JOIN SALES S
ON P.PRODUCT_ID=S.PRODUCT_ID;

SELECT P.PRODUCT_ID,P.PRODUCT_NAME,S.PRODUCT_ID
FROM PRODUCT P
RIGHT OUTER JOIN SALES S
ON P.PRODUCT_ID=S.PRODUCT_ID;

/****************************EXERCISE 8******************************/
--1.PRODUCE A LIST WHICH RETURNS ALL ROWS FROM SALES AND PRODUCT TABLES WHERE THERE IS A
--MATCHING PRODUCT_ID VALUE IN BOTH THE SALES AND PRODUCT TABLES
SELECT * FROM SALES S
INNER JOIN PRODUCT P
ON S.PRODUCT_ID=P.PRODUCT_ID;

--2.PRODUCE A LIST WHICH RETURNS ALL ROWS FROM SALES AND ONLY THOSE ROWS FROM THE PRODUCT
--TABLE WHERE THE JOINED FIELD PRODUCT_ID IS EQUAL
SELECT * FROM SALES S
LEFT OUTER JOIN PRODUCT P
ON S.PRODUCT_ID=P.PRODUCT_ID;

--3.PRODUCE A LIST WHICH RETURNS ALL ROWS FROM PRODUCT AND ONLY THOSE ROWS FROM THE SALES
--TABLE WHERE THE JOINED FIELD PRODUCT_ID IS EQUAL
SELECT * FROM SALES S
RIGHT OUTER JOIN PRODUCT P
ON S.PRODUCT_ID=P.PRODUCT_ID;

--4.PRODUCE A LIST WHICH RETURNS ALL ROWS FROM SALES AND PRODUCT TABLE IRRESPECTIVE
--OF PRODUCT_ID MATCHED BETWEEN THESE TABLES(PRODUCT CAN MATCH BETWEEN BOTH THESE TABLES
--OR IT MIGHT NOT MATCH)
SELECT * FROM SALES S
FULL JOIN PRODUCT P
ON S.PRODUCT_ID=P.PRODUCT_ID;

/**************************NUMERIC FUNCTIONS******************************/
SELECT SIN(30 * 3.14159265359/180) --SINE FUNCTION
"SINE OF 30 DEGREE " FROM DUAL;

SELECT TAN(30 * 3.14159265359/180) --TANGENT FUNCTION
"TANGENT OF 30 DEGREE " FROM DUAL;

SELECT COS(30 * 3.14159265359/180) --COSINE FUNCTION
"COSINE OF 30 DEGREE " FROM DUAL;

SELECT ABS(-15) "ABSOLUTE" FROM DUAL; --ABSOLUTE FUNCTION

SELECT ACOS(.4)"ARC_COSINE" FROM DUAL; --RETURN ARC OF COSINE...N SHOULD BETWEEN -1 AND 1
SELECT ACOS(-0.4)"ARC_COSINE" FROM DUAL;

SELECT ASIN(.4)"ARC_SINE" FROM DUAL;      --RETURN ARC OF SINE
SELECT ASIN(-0.7)"ARC_SINE" FROM DUAL;

SELECT ATAN(.4)"ARC_TANGENT" FROM DUAL;    --RETURN ARC OF TANGENT

SELECT ATAN2(0.4,0.6)"ARC_TANGENT" FROM DUAL; --RETURN ARC OF TANGENT 0.4 AND 0.6

SELECT SQRT(36) "SQUARE ROOT" FROM DUAL;      --SQUARE ROOT

SELECT LOG(10,100) "LOG BASE 10 OF 100" FROM DUAL; --LOGARITHMIC FUNCTION

SELECT MOD(11,4) "MODULUS" FROM DUAL;       --MODULUS FUNCTION

/*********************CHARACTER FUNCTION*****************************/
SELECT * FROM CUSTOMER;

SELECT CHR(67)||CHR(65)||CHR(84) "DOG" FROM DUAL;

--C0NCATENATION--
SELECT CONCAT(CONCAT(FIRST_NAME, '''s ADDRESS IS '), ADDRESS_LINE1)
FROM CUSTOMER;

SELECT CONCAT(CONCAT(FIRST_NAME, '''s ADDRESS IS ') , ADDRESS_LINE1) AS DETAILS
FROM CUSTOMER ;

SELECT CUSTOMER_ID,CONCAT(CONCAT(FIRST_NAME, '''s ADDRESS IS ') , ADDRESS_LINE1) AS DETAILS
FROM CUSTOMER
ORDER BY CUSTOMER_ID;

SELECT CUSTOMER_ID,CONCAT(CONCAT(FIRST_NAME, '''s ADDRESS IS ') , ADDRESS_LINE1) AS DETAILS
FROM CUSTOMER
ORDER BY CUSTOMER_ID DESC;

--INIT CAP--
SELECT * FROM PRODUCT;
SELECT INITCAP('the soap') "Capitals" FROM DUAL;

/*DATE*/
SELECT SESSIONTIMEZONE, CURRENT_DATE FROM DUAL; --CURRENT DATE

SELECT SESSIONTIMEZONE, CURRENT_TIMESTAMP FROM DUAL;--CURRENT TIMESTAMP

SELECT TO_CHAR (SYSDATE, 'MM-DD-YYYY HH24:MI:SS') "NOW"FROM DUAL; --SYSTEM DATE

SELECT TO_CHAR (SYSDATE, 'DD-MM-YYYY HH24:MI:SS') "NOW"FROM DUAL;

SELECT DBTIMEZONE FROM DUAL; --DATABASE TIME ZONE

SELECT MONTHS_BETWEEN (TO_DATE('01-07-2017','DD-MM-YYYY'), --MONTHS BETWEEN 2 DATES
TO_DATE('01-01-2017','DD-MM-YYYY') ) "MONTHS"
FROM DUAL;

SELECT SYSDATE,           --DAYS LEFT FROM CURRENT DATE
LAST_DAY(SYSDATE) "LAST",
LAST_DAY(SYSDATE) - SYSDATE "DAYS LEFT"
FROM DUAL;

SELECT TO_CHAR(SYSDATE,'DAY') AS CURRENT_DAY FROM DUAL ; --CURRENT DAY

SELECT CURRENT_TIMESTAMP FROM DUAL;     --GET CURRENT TIMESTAMP

SELECT NEXT_DAY('06-07-2017','SUNDAY') "NEXT DAY" FROM DUAL; --WHEN IS NEXT SUNDAY

/**************NEW TABLE****************/
CREATE TABLE STUDENT          --CREATE TABLE
(
ROLL_NO       NUMBER,
STUDENT_NAME VARCHAR(30),
MARKS         NUMBER(10,2)
);

INSERT INTO STUDENT           --INSERT INTO TABLE
(
ROLL_NO,STUDENT_NAME,MARKS
)
VALUES
(
1,'SONALI',80
);

INSERT INTO STUDENT
(
ROLL_NO,STUDENT_NAME,MARKS
)
VALUES
(
2,'SHEETAL',85
);

INSERT INTO STUDENT
(
ROLL_NO,STUDENT_NAME,MARKS
)
VALUES
(
3,'PRIYANKA',70
);

INSERT INTO STUDENT
(
ROLL_NO,STUDENT_NAME,MARKS
)
VALUES
(
4,'NEHA',85
);

INSERT INTO STUDENT
(
ROLL_NO,STUDENT_NAME,MARKS
)
VALUES
(
5,'POOJA',75
);

INSERT INTO STUDENT
(
ROLL_NO,STUDENT_NAME,MARKS
)
VALUES
(
6,'RASHMI',82
);

SELECT * FROM STUDENT;    --DISPLAY TABLE

UPDATE STUDENT SET MARKS = 93 WHERE ROLL_NO=2; --UPDATE ROW IN TABLE

ALTER TABLE STUDENT ADD BRANCH VARCHAR2(30);    --ALTER TABLE ADD COLUMN BRANCH

UPDATE STUDENT SET BRANCH ='COMPUTERS';

ALTER TABLE STUDENT ADD ADMISSION_DATE DATE;    --ALTER TABLE ADD COLUMN DATE

ALTER TABLE STUDENT ADD ADDRESS VARCHAR2(30);    --ALTER TABLE ADD COLUMN ADDRESS

UPDATE STUDENT SET ADMISSION_DATE ='02-01-17' WHERE ROLL_N0<4;

COMMIT;

UPDATE STUDENT SET BRANCH='ENTC' WHERE ROLL_NO= 4;
UPDATE STUDENT SET BRANCH='IT' WHERE ROLL_NO>= 5;

SELECT * FROM STUDENT;

--NOT NULL CONSTRAINTS
CREATE TABLE DEMO
(
CODE NUMBER NOT NULL,
CITY VARCHAR(6) NOT NULL
);
SELECT * FROM DEMO;

--CHECK CONSTRAINT--
SELECT * FROM STUDENT;    --SET CONSTRAINT ON MARKS
ALTER TABLE STUDENT
ADD CHECK(MARKS<100);

UPDATE STUDENT SET MARKS=20 WHERE STUDENT_NAME='RASHMI';

ALTER TABLE STUDENT       --DEFINE CONSTRAINT
ADD CONSTRAINT MRK_CHECK CHECK (MARKS>50);

ALTER TABLE STUDENT       --DEFINE CONSTRAINT
DROP CONSTRAINT MRK_CHECK;

PLSQL Reference codes

--PLSQL

--DECLARING AND INITIALIZING VARIABLES
SET SERVEROUTPUT ON
DECLARE
Myname VARCHAR2(20);
BEGIN
DBMS_OUTPUT.PUT_LINE('My name is: '||Myname);
Myname := 'John';
DBMS_OUTPUT.PUT_LINE('My name is: '||Myname);
END;

SET SERVEROUTPUT ON
DECLARE
Myname VARCHAR2(20):= 'John';
BEGIN
Myname := 'Steven';
DBMS_OUTPUT.PUT_LINE('My name is: '||Myname);
END;

--DELIMETERS IN STRING LITERALS
SET SERVEROUTPUT ON
DECLARE
event VARCHAR2(15);
BEGIN
event := q'!Father's day!';
DBMS_OUTPUT.PUT_LINE('3rd Sunday in June is :
'||event);
event := q'[Mother's day]';
DBMS_OUTPUT.PUT_LINE('2nd Sunday in May is :
'||event);
END;

--BINARY FLOAT AND BINARY DOUBLE
SET SERVEROUTPUT ON
DECLARE
bf_var BINARY_FLOAT;
bd_var BINARY_DOUBLE;
BEGIN
bf_var := 270/35f;
bd_var := 140d/0.35;
DBMS_OUTPUT.PUT_LINE('bf: '|| bf_var);
DBMS_OUTPUT.PUT_LINE('bd: '|| bd_var);
END;

--DECLARING ANG INITIALIZING VARIABLES
DECLARE
flag BOOLEAN := FALSE;
BEGIN
flag := TRUE;
END;

--BIND VARIABLES
VARIABLE result NUMBER
BEGIN
SELECT (SALARY*12) + NVL(COMMISSION_PCT,0) INTO :result
FROM employees WHERE employee_id = 144;
END;

--PRINTING BIND VARIABLES
VARIABLE emp_salary NUMBER
BEGIN
SELECT salary INTO :emp_salary
FROM employees WHERE employee_id = 178;
END;

--USING SUBSTITUTION VARIABLE
VARIABLE emp_salary NUMBER
SET AUTOPRINT ON
DECLARE
empno NUMBER(6):=&empno;
BEGIN
SELECT salary INTO :emp_salary
FROM employees WHERE employee_id = empno;
END;

--PROMPT FOR SUBSTITUTION VARIABLE
SET VERIFY OFF
VARIABLE emp_salary NUMBER
ACCEPT empno PROMPT 'Please enter a valid employee
number: '
SET AUTOPRINT ON
DECLARE
empno NUMBER(6):= &empno;
BEGIN
SELECT salary INTO :emp_salary FROM employees
WHERE employee_id = empno;
END;

--USING DEFINE FOR USER VARIABLES
SET VERIFY OFF
DEFINE lname= Urman
DECLARE
fname VARCHAR2(25);
BEGIN
SELECT first_name INTO fname FROM employees
WHERE last_name='&lname';
END;

--DATA TYPE CONVERSION
DECLARE
salary NUMBER(6):=6000;
sal_hike VARCHAR2(5):='1000';
total_salary salary%TYPE;
BEGIN
total_salary:=salary+sal_hike;
END;

--NESTED BLOCKS
DECLARE
outer_variable VARCHAR2(20):='GLOBAL VARIABLE';
BEGIN
DECLARE
inner_variable VARCHAR2(20):='LOCAL VARIABLE';
BEGIN
DBMS_OUTPUT.PUT_LINE(inner_variable);
DBMS_OUTPUT.PUT_LINE(outer_variable);
END;
DBMS_OUTPUT.PUT_LINE(outer_variable);
END;

--VARIABLE SCOPE AND VISIBILITY
DECLARE
father_name VARCHAR2(20):='Patrick';
date_of_birth DATE:='20-Apr-1972';
BEGIN
DECLARE
child_name VARCHAR2(20):='Mike';
date_of_birth DATE:='12-Dec-2002';
BEGIN
DBMS_OUTPUT.PUT_LINE('Father''s Name: '||father_name);
DBMS_OUTPUT.PUT_LINE('Date of Birth: '||date_of_birth);
DBMS_OUTPUT.PUT_LINE('Child''s Name: '||child_name);
END;
DBMS_OUTPUT.PUT_LINE('Date of Birth: '||date_of_birth);
END;

--QUALIFY AN IDENTIFIER
BEGIN <<outer>>
DECLARE
father_name VARCHAR2(20):='Patrick';
date_of_birth DATE:='20-Apr-1972';
BEGIN
DECLARE
child_name VARCHAR2(20):='Mike';
date_of_birth DATE:='12-Dec-2002';
BEGIN
DBMS_OUTPUT.PUT_LINE('Father''s Name: '||father_name);
DBMS_OUTPUT.PUT_LINE('Date of Birth: '
||outer.date_of_birth);
DBMS_OUTPUT.PUT_LINE('Child''s Name: '||child_name);
END;
DBMS_OUTPUT.PUT_LINE('Date of Birth: '||date_of_birth);
END;
END outer;

--DETERMINING VARIABLE SCOPE
BEGIN <<outer>>
DECLARE
sal NUMBER(7,2) := 60000;
comm NUMBER(7,2) := sal * 0.20;
message VARCHAR2(255) := ' eligible for commission';
BEGIN
DECLARE
sal NUMBER(7,2) := 50000;
comm NUMBER(7,2) := 0;
total_comp NUMBER(7,2) := sal + comm;
BEGIN
message := 'CLERK not'||message;
outer.comm := sal * 0.30;
END;
message := 'SALESMAN'||message;
END;
END outer;

--SELECT STATEMENTS IN PLSQL
SET SERVEROUTPUT ON
DECLARE
fname VARCHAR2(25);
BEGIN
SELECT first_name INTO fname
FROM employees WHERE employee_id=200;
DBMS_OUTPUT.PUT_LINE(' First Name is : '||fname);
END;

--RETRIEVING DATA IN PLSQL
DECLARE
emp_hiredate employees.hire_date%TYPE;
emp_salary employees.salary%TYPE;
BEGIN
SELECT hire_date, salary
INTO emp_hiredate, emp_salary
FROM employees
WHERE employee_id = 100;
END;

SET SERVEROUTPUT ON
DECLARE
sum_sal NUMBER(10,2);
deptno NUMBER NOT NULL := 60;
BEGIN
SELECT SUM(salary) -- group function
INTO sum_sal FROM employees
WHERE department_id = deptno;
DBMS_OUTPUT.PUT_LINE ('The sum of salary is '
|| sum_sal);
END;

--INSERTING DATA
BEGIN
INSERT INTO employees
(employee_id, first_name, last_name, email,
hire_date, job_id, salary)
VALUES(employees_seq.NEXTVAL, 'Ruth', 'Cores',
'RCORES',sysdate, 'AD_ASST', 4000);
END;

--UPDATING DATA
DECLARE
sal_increase employees.salary%TYPE := 800;
BEGIN
UPDATE employees
SET salary = salary + sal_increase
WHERE job_id = 'ST_CLERK';
END;

--DELETING DATA
DECLARE
deptno employees.department_id%TYPE := 207;
BEGIN
DELETE FROM employees
WHERE department_id = deptno;
END;

--SIMPLE IF STATEMENT
DECLARE
myage number:=31;
BEGIN
IF myage < 11
THEN
DBMS_OUTPUT.PUT_LINE(' I am a child ');
END IF;
END;

--IF THEN ELSE STATEMENT
SET SERVEROUTPUT ON
DECLARE
myage number:=31;
BEGIN
IF myage < 11
THEN
DBMS_OUTPUT.PUT_LINE(' I am a child ');
ELSE
DBMS_OUTPUT.PUT_LINE(' I am not a child ');
END IF;
END;

--IF ELSEIF ELSE CLAUSE
DECLARE
myage number:=31;
BEGIN
IF myage < 11
THEN
DBMS_OUTPUT.PUT_LINE(' I am a child ');
ELSIF myage < 20
THEN
DBMS_OUTPUT.PUT_LINE(' I am young ');
ELSIF myage < 30
THEN
DBMS_OUTPUT.PUT_LINE(' I am in my twenties');
ELSIF myage < 40
THEN
DBMS_OUTPUT.PUT_LINE(' I am in my thirties');
ELSE
DBMS_OUTPUT.PUT_LINE(' I am always young ');
END IF;
END;

--NULL VALUES IN IF STATEMENT
DECLARE
myage number;
BEGIN
IF myage < 11
THEN
DBMS_OUTPUT.PUT_LINE(' I am a child ');
ELSE
DBMS_OUTPUT.PUT_LINE(' I am not a child ');
END IF;
END;

--CASE EXPRESSION
SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
grade CHAR(1) := UPPER('&grade');
appraisal VARCHAR2(20);
BEGIN
appraisal :=
CASE grade
WHEN 'A' THEN 'Excellent'
WHEN 'B' THEN 'Very Good'
WHEN 'C' THEN 'Good'
ELSE 'No such grade'
END;
DBMS_OUTPUT.PUT_LINE ('Grade: '|| grade || '
Appraisal ' || appraisal);
END;

--BASIC LOOPS
DECLARE
countryid locations.country_id%TYPE := 'CA';
loc_id locations.location_id%TYPE;
counter NUMBER(2) := 1;
new_city locations.city%TYPE := 'Montreal';
BEGIN
SELECT MAX(location_id) INTO loc_id FROM locations
WHERE country_id = countryid;
LOOP
INSERT INTO locations(location_id, city, country_id)
VALUES((loc_id + counter), new_city, countryid);
counter := counter + 1;
EXIT WHEN counter > 3;
END LOOP;
END;

--WHILE LOOPS
DECLARE
countryid locations.country_id%TYPE := 'CA';
loc_id locations.location_id%TYPE;
new_city locations.city%TYPE := 'Montreal';
counter NUMBER := 1;
BEGIN
SELECT MAX(location_id) INTO loc_id FROM locations
WHERE country_id = countryid;
WHILE counter <= 3 LOOP
INSERT INTO locations(location_id, city, country_id)
VALUES((loc_id + counter), new_city, countryid);
counter := counter + 1;
END LOOP;
END;

--FOR LOOP
DECLARE
countryid locations.country_id%TYPE := 'CA';
loc_id locations.location_id%TYPE;
new_city locations.city%TYPE := 'Montreal';
BEGIN
SELECT MAX(location_id) INTO loc_id
FROM locations
WHERE country_id = countryid;
FOR i IN 1..3 LOOP
INSERT INTO locations(location_id, city, country_id)
VALUES((loc_id + i), new_city, countryid );
END LOOP;
END;

--INDEX BY TABLE OF RECORDS
SET SERVEROUTPUT ON
DECLARE
TYPE emp_table_type IS TABLE OF
employees%ROWTYPE INDEX BY PLS_INTEGER;
my_emp_table emp_table_type;
max_count NUMBER(3):= 104;
BEGIN
FOR i IN 100..max_count
LOOP
SELECT * INTO my_emp_table(i) FROM employees
WHERE employee_id = i;
END LOOP;
FOR i IN my_emp_table.FIRST..my_emp_table.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(my_emp_table(i).last_name);
END LOOP;
END;

--FETCHING DATA FROM THE CURSOR
SET SERVEROUTPUT ON
DECLARE
CURSOR emp_cursor IS
SELECT employee_id, last_name FROM employees
WHERE department_id =30;
empno employees.employee_id%TYPE;
lname employees.last_name%TYPE;
BEGIN
OPEN emp_cursor;
FETCH emp_cursor INTO empno, lname;
DBMS_OUTPUT.PUT_LINE( empno ||' '||lname);
END;

--CURSOR FOR LOOPS
SET SERVEROUTPUT ON
DECLARE
CURSOR emp_cursor IS
SELECT employee_id, last_name FROM employees
WHERE department_id =30;
BEGIN
FOR emp_record IN emp_cursor
LOOP
DBMS_OUTPUT.PUT_LINE( emp_record.employee_id
||' ' ||emp_record.last_name);
END LOOP;
END;

--%ROWCOUNT AND %NOTFOUND
SET SERVEROUTPUT ON
DECLARE
empno employees.employee_id%TYPE;
ename employees.last_name%TYPE;
CURSOR emp_cursor IS SELECT employee_id,
last_name FROM employees;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO empno, ename;
EXIT WHEN emp_cursor%ROWCOUNT > 10 OR
emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(TO_CHAR(empno)
||' '|| ename);
END LOOP;
CLOSE emp_cursor;
END ;

--CURSOR FOR LOOPS USING SUBQUERIES
SET SERVEROUTPUT ON
BEGIN
FOR emp_record IN (SELECT employee_id, last_name
FROM employees WHERE department_id =30)
LOOP
DBMS_OUTPUT.PUT_LINE( emp_record.employee_id ||'
'||emp_record.last_name);
END LOOP;
END;

--EXCEPTION
SET SERVEROUTPUT ON
BEGIN
FOR emp_record IN (SELECT employee_id, last_name
FROM employees WHERE department_id =30)
LOOP
DBMS_OUTPUT.PUT_LINE( emp_record.employee_id ||'
'||emp_record.last_name);
END LOOP;
END;

SET SERVEROUTPUT ON
DECLARE
lname VARCHAR2(15);
BEGIN
SELECT last_name INTO lname FROM employees WHERE
first_name='John';
DBMS_OUTPUT.PUT_LINE ('John''s last name is : '
||lname);
EXCEPTION
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE (' Your select statement
retrieved multiple rows. Consider using a
cursor.');
END;

--NON PREDEFINED ERRORS
SET SERVEROUTPUT ON
DECLARE
insert_excep EXCEPTION;
PRAGMA EXCEPTION_INIT
(insert_excep, -01400);
BEGIN
INSERT INTO departments
(department_id, department_name) VALUES (280, NULL);
EXCEPTION
WHEN insert_excep THEN
DBMS_OUTPUT.PUT_LINE('INSERT OPERATION FAILED');
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;

--TRAPPING USER DEFINED EXCEPTION
ACCEPT deptno PROMPT 'Please enter the department number:'
ACCEPT name PROMPT 'Please enter the department name:'
DECLARE
invalid_department EXCEPTION;
name VARCHAR2(20):='&name';
deptno NUMBER :=&deptno;
BEGIN
UPDATE departments
SET department_name = name
WHERE department_id = deptno;
IF SQL%NOTFOUND THEN
RAISE invalid_department;
END IF;
COMMIT;
EXCEPTION
WHEN invalid_department THEN
DBMS_OUTPUT.PUT_LINE('No such department id.');
END;

--PROCEDURES
CREATE TABLE dept AS SELECT * FROM departments;
CREATE PROCEDURE add_dept1 IS
dept_id dept.department_id%TYPE;
dept_name dept.department_name%TYPE;
BEGIN
dept_id:=280;
dept_name:='ST-Curriculum';
INSERT INTO dept(department_id,department_name)
VALUES(dept_id,dept_name);
DBMS_OUTPUT.PUT_LINE(' Inserted '||
SQL%ROWCOUNT ||' row ');
END;

CREATE PROCEDURE getemp IS -- header
emp_id employees.employee_id%type;
lname employees.last_name%type;
BEGIN
emp_id := 100;
SELECT last_name INTO lname
FROM EMPLOYEES
WHERE employee_id = emp_id;
DBMS_OUTPUT.PUT_LINE('Last name: '||lname);
END;

--FUNCTIONS
CREATE FUNCTION check_sal RETURN Boolean IS
dept_id employees.department_id%TYPE;
empno employees.employee_id%TYPE;
sal employees.salary%TYPE;
avg_sal employees.salary%TYPE;
BEGIN
empno:=205;
SELECT salary,department_id INTO sal,dept_id
FROM employees WHERE employee_id= empno;
SELECT avg(salary) INTO avg_sal FROM employees
WHERE department_id=dept_id;
IF sal > avg_sal THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
END;

CREATE FUNCTION avg_salary RETURN NUMBER IS
avg_sal employees.salary%type;
BEGIN
SELECT AVG(salary) INTO avg_sal
FROM EMPLOYEES;
RETURN avg_sal;
END;

--INVOKING THE FUNCTION
SET SERVEROUTPUT ON
BEGIN
IF (check_sal IS NULL) THEN
DBMS_OUTPUT.PUT_LINE('The function returned
NULL due to exception');
ELSIF (check_sal) THEN
DBMS_OUTPUT.PUT_LINE('Salary > average');
ELSE
DBMS_OUTPUT.PUT_LINE('Salary < average');
END IF;
END;

--FETCHING
DECLARE
TYPE EmpCurTyp IS REF CURSOR;
emp_cv EmpCurTyp;
emp_rec employees%ROWTYPE;
sql_stmt VARCHAR2(200);
my_job VARCHAR2(10) := 'ST_CLERK';
BEGIN
sql_stmt := 'SELECT * FROM employees
WHERE job_id = :j';
OPEN emp_cv FOR sql_stmt USING my_job;
LOOP
FETCH emp_cv INTO emp_rec;
EXIT WHEN emp_cv%NOTFOUND;
-- process record
END LOOP;
CLOSE emp_cv;
END;

--PACKAGE

--PACKAGES
SELECT * FROM STUDENT;
INSERT INTO STUDENT(ROLL_NO, STUD_NAME, MARKS) VALUES(11, 'SONALI', 94);

--PACKAGE SPECIFICATION
CREATE OR REPLACE PACKAGE STUDENT_PACKAGE
AS
     PROCEDURE FIND_MARKS(roll_no1 STUDENT.ROLL_NO%TYPE);
END STUDENT_PACKAGE;

--PACKAGE BODY
CREATE OR REPLACE PACKAGE BODY STUDENT_PACKAGE
AS
      PROCEDURE FIND_MARKS(roll_no1 STUDENT.ROLL_NO%TYPE)
IS
   s_marks STUDENT.MARKS%TYPE;
BEGIN
      SELECT MARKS INTO s_marks
      FROM STUDENT
      WHERE ROLL_NO=roll_no1;
      DBMS_OUTPUT.PUT_LINE('MARKS '|| s_marks);
   END FIND_MARKS;
END STUDENT_PACKAGE;

--CALLING PACKAGE
DECLARE
   NUM STUDENT.ROLL_NO%TYPE := &mk;
BEGIN
   STUDENT_PACKAGE.FIND_MARKS(NUM);
END;

CREATE TRIGGER check_salary
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW
DECLARE
c_min constant number(8,2) := 1000.0;
c_max constant number(8,2) := 500000.0;
BEGIN
IF :new.salary > c_max OR
:new.salary < c_min THEN
RAISE_APPLICATION_ERROR(-20000,
'New salary is too small or large');
END IF;
END;

--USING IN PARAMETERS IN PROCEDURES
CREATE OR REPLACE PROCEDURE raise_salary
(id IN employees.employee_id%TYPE,
percent IN NUMBER)
IS
BEGIN
UPDATE employees
SET salary = salary * (1 + percent/100)
WHERE employee_id = id;
END raise_salary;

EXECUTE raise_salary(176,10)

--USING OUT PARAMETERS IN PROCEDURES
CREATE OR REPLACE PROCEDURE query_emp
(id IN employees.employee_id%TYPE,
name OUT employees.last_name%TYPE,
salary OUT employees.salary%TYPE) IS
BEGIN
SELECT last_name, salary INTO name, salary
FROM employees
WHERE employee_id = id;
END query_emp;

DECLARE
emp_name employees.last_name%TYPE;
emp_sal employees.salary%TYPE;
BEGIN
query_emp(171, emp_name, emp_sal);
END;

--PASSING PARAMETERS IN PROCEDURE
CREATE OR REPLACE PROCEDURE add_dept(
name IN departments.department_name%TYPE,
loc IN departments.location_id%TYPE) IS
BEGIN
INSERT INTO departments(department_id,
department_name, location_id)
VALUES (departments_seq.NEXTVAL, name, loc);
END add_dept;

EXECUTE add_dept ('TRAINING', 2500);

EXECUTE add_dept (loc=>2400, name=>'EDUCATION')

--EXCEPTION HANDLING
CREATE PROCEDURE add_department(
name VARCHAR2, mgr NUMBER, loc NUMBER) IS
BEGIN
INSERT INTO DEPARTMENTS (department_id,
department_name, manager_id, location_id)
VALUES (DEPARTMENTS_SEQ.NEXTVAL, name, mgr, loc);
DBMS_OUTPUT.PUT_LINE('Added Dept: '||name);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Err: adding dept: '||name);
END;

CREATE PROCEDURE create_departments IS
BEGIN
add_department('Media', 100, 1800);
add_department('Editing', 99, 1800);
add_department('Advertising', 101, 1800);
END;

--REMOVING FUNCTIONS
DROP FUNCTION avg_salary;

/***********************PACKAGES**************************
--PACKAGES
SELECT * FROM STUDENT;

--PACKAGE SPECIFICATION
CREATE OR REPLACE PACKAGE STUDENT_PACKAGE
AS
     PROCEDURE FIND_MARKS(roll_no1 STUDENT.ROLL_NO%TYPE);
END STUDENT_PACKAGE;

--PACKAGE BODY
CREATE OR REPLACE PACKAGE BODY STUDENT_PACKAGE
AS
      PROCEDURE FIND_MARKS(roll_no1 STUDENT.ROLL_NO%TYPE)
IS
   s_marks STUDENT.MARKS%TYPE;
BEGIN
      SELECT MARKS INTO s_marks
      FROM STUDENT
      WHERE ROLL_NO=roll_no1;
      DBMS_OUTPUT.PUT_LINE('MARKS '|| s_marks);
   END FIND_MARKS;
END STUDENT_PACKAGE;

--CALLING PACKAGE
DECLARE
   NUM STUDENT.ROLL_NO%TYPE := &mk;
BEGIN
   STUDENT_PACKAGE.FIND_MARKS(NUM);
END;

Tuesday, 18 April 2017

TE Comp : Computer Networks (Solved University Question Bank)

UNIT 1 , 2 , 3 ( IN SEMESTER + END SEMESTER QUESTIONS WITH ANSWERS)

CLICK HERE To download

https://drive.google.com/open?id=0B9zJxhTsrja5X0xkb2hsYmRDcEU

Saturday, 8 April 2017

SE Comp (2015 Course) Microprocessor Lab Assignments

CLICK HERE TO DOWNLOAD ASSIGNMENTS

https://drive.google.com/open?id=0B9zJxhTsrja5ZE9sNVdwUGVrZUU

__________________________________________________________________________________________

Suggested List of Laboratory Assignments (Any 12)
1.	Write X86/64 ALP to count number of positive and negative numbers from the array
2.	Write X86/64 ALP to perform non-overlapped and overlapped block transfer (with and without string specific instructions). Block containing data can be defined in the data segment.
3.	Write X86/64 ALP to convert 4-digit Hex number into its equivalent BCD number and 5-digit BCD number into its equivalent HEX number. Make your program user friendly to accept the choice from user for: (a) HEX to BCD b) BCD to HEX (c) EXIT. Display proper strings to prompt the user while accepting the input and displaying the result. (wherever necessary, use 64-bit registers)
4.	Write X86/64 ALP to perform multiplication of two 8-bit hexadecimal numbers. Use successive addition and add and shift method. (use of 64-bit registers is expected)
5.	Write X86 ALP to find, a) Number of Blank spaces b) Number of lines c) Occurrence of a particular character. Accept the data from the text file. The text file has to be accessed during Program_1 execution and write FAR PROCEDURES in Program_2 for the rest of the processing. Use of PUBLIC and EXTERN directives is mandatory.
6.	Write X86/64 ALP to switch from real mode to protected mode and display the values of GDTR, LDTR, IDTR, TR and MSW Registers.
7.	Write X86 program to sort the list of integers in ascending/descending order. Read the input from the text file and write the sorted data back to the same text file using bubble sort
8.	Write X86 menu driven Assembly Language Program (ALP) to implement OS (DOS) commands TYPE, COPY and DELETE using file operations. User is supposed to provide command line arguments in all cases.
9.	Write x86 ALP to find the factorial of a given integer number on a command line by using recursion. Explicit stack manipulation is expected in the code.
10.	Write 80387 ALP to find the roots of the quadratic equation. All the possible cases must be considered in calculating the roots.
11.	Write 80387 ALP to plot Sine Wave, Cosine Wave and Sinc function. Access video memory directly for plotting.
12.	Write 80387 ALP to obtain: i) Mean ii) Variance iii) Standard Deviation Also plot the histogram for the data set. The data elements are available in a text file.
13.	Write a Terminate but Stay Resident (TSR) program for a key-logger. The key-presses during the stipulated time need to be displayed at the center of the screen. OR Write a TSR to generate the pattern of the frequency tones by reading the Real Time Clock (RTC). The duration of the each tone is solely decided by the programmer.
14.	Write 80386 ALP to implement multitasking. Where each task is supposed to change the color of the text displayed at the center of the screen

Followers