/****************************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;
