Scribd
Upload
8 views6 pages

SQL Exp4

The document outlines various SQL operations including table creation, data insertion, and queries using ORDER BY and GROUP BY clauses. It demonstrates how to calculate total and average sales amounts, update employee net pay, and retrieve information based on specific conditions. Additionally, it includes examples of joining tables and filtering results based on salary and department criteria.

Uploaded by

abhijith22ra124
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
8 views6 pages

SQL Exp4

The document outlines various SQL operations including table creation, data insertion, and queries using ORDER BY and GROUP BY clauses. It demonstrates how to calculate total and average sales amounts, update employee net pay, and retrieve information based on specific conditions. Additionally, it includes examples of joining tables and filtering results based on salary and department criteria.

Uploaded by

abhijith22ra124
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

**********************************************************************

NAME : Vivek Ram P EXPERIMENT NO :4


ROLL NO : 58 DATE : 31/07/2025

FAMILIARIZATION OF ORDER BY AND GROUP BY CLAUSE


**********************************************************************

CREATE TABLE SALES(SALES_NO NUMBER(20),SALES_NAME VARCHAR(20),BRANCH


VARCHAR(20),SALES_AMOUNT NUMBER(20),DOB DATE);
DESC SALES;

A)
INSERT INTO SALES VALUES(:SALES_NO,:SALES_NAME,:BRANCH,:SALES_AMOUNT,:DOB);
SELECT * FROM SALES;

SALES_NO SALES_NAME BRANCH SALES_AMOUNT DOB


120 ALICE CHENNAI 59000 27-DEC-05
121 BOB MADRAS 41000 24-JUN-15
122 VIKRU COCHIN 52000 13-DEC-19
123 SAM BOMBAY 60000 22-MAY-19
124 CHARLIE DELHI 55000 11-APR-23

B)
SELECT BRANCH,SUM(SALES_AMOUNT) AS TOTAL_SALESAMOUNT
FROM SALES
GROUP BY BRANCH;

BRANCH TOTAL_SALESAMOUNT
DELHI 55000
COCHIN 52000
MADRAS 41000
CHENNAI 59000
BOMBAY 60000

C)
SELECT BRANCH,AVG(SALES_AMOUNT) AS AVG_SALESAMOUNT
FROM SALES
GROUP BY BRANCH;

BRANCH AVG_SALESAMOUNT
DELHI 55000
COCHIN 52000
MADRAS 41000
CHENNAI 59000
BOMBAY 60000

D)
SELECT SALES_NAME,DOB
FROM SALES
WHERE TO_CHAR(DOB,'MM') LIKE '%12%';

SALES_NAME DOB
ALICE 27-DEC-05
VIKRU 13-DEC-19

E)SELECT SALES_NAME,DOB
FROM SALES
ORDER BY TO_CHAR(DOB,'MM');
SALES_NAME DOB
CHARLIE 11-APR-23
SAM 22-MAY-19
BOB 24-JUN-15
ALICE 27-DEC-05
VIKRU 13-DEC-19

F)
SELECT BRANCH,COUNT(*) AS NUM_SALESMEN
FROM SALES
WHERE SALES_AMOUNT>50000
GROUP BY BRANCH;

BRANCH NUM_SALESMEN
DELHI 1
COCHIN 1
CHENNAI 1
BOMBAY 1

2)

CREATE TABLE EMP_2(EMP_NO NUMBER(20),


EMP_NAME VARCHAR(20),
JOB VARCHAR(20),
BASIC NUMBER(20),
DA NUMBER(20),
HRA NUMBER(20),
PF NUMBER(20),
GROSSPAY NUMBER(20),
NETPAY NUMBER(20));

DESC EMP_2;

2:a)

INSERT INTO EMP_2


VALUES(101,'ALICE','MANAGER',10000,0.30*10000,0.4*10000,0.12*10000,
(10000+(0.3*10000)+(0.4*10000)),(10000+(0.3*10000)+(0.4*10000))-(0.12*10000));

INSERT INTO EMP_2 VALUES(102,'BOB','CLERK',8000,0.30*8000,0.4*8000,0.12*8000,


(8000+(0.3*8000)+(0.4*8000)),(8000+(0.3*8000)+(0.4*8000))-(0.12*8000));

INSERT INTO EMP_2


VALUES(103,'CHARLIE','ANALYST',12000,0.30*12000,0.4*12000,0.12*12000,
(12000+(0.3*12000)+(0.4*12000)),(12000+(0.3*12000)+(0.4*12000))-(0.12*12000));

INSERT INTO EMP_2


VALUES(104,'DAVID','SALESMAN',20000,0.30*20000,0.4*20000,0.12*20000,
(20000+(0.3*20000)+(0.4*20000)),(20000+(0.3*20000)+(0.4*20000))-(0.12*20000));

INSERT INTO EMP_2 VALUES(105,'EVA','ACCOUNTANT',8500,0.30*8500,0.4*8500,0.12*8500,


(8500+(0.3*8500)+(0.4*8500)),(8500+(0.3*8500)+(0.4*8500))-(0.12*8500));

INSERT INTO EMP_2 VALUES(106,'TEENA','CLERK',6000,0.30*6000,0.4*6000,0.12*6000,


(6000+(0.3*6000)+(0.4*6000)),(6000+(0.3*6000)+(0.4*6000))-(0.12*6000));

SELECT * FROM EMP_2;

OUTPUT
EMP_NO EMP_NAME JOB BASIC DA HRA PF GROSSPAY NETPAY
101 ALICE MANAGER 10000 3000 4000 1200 17000 15800
102 BOB CLERK 8000 2400 3200 960 13600 12640
103 CHARLIE ANALYST 12000 3600 4800 1440 20400 18960
104 DAVID SALESMAN 20000 6000 8000 2400 34000 31600
105 EVA ACCOUNTANT 8500 2550 3400 1020 14450 13430
106 TEENA CLERK 6000 1800 2400 720 10200 9480

**********************************************************************
2:b)

SELECT * FROM EMP_2


WHERE(JOB,BASIC) IN
(SELECT JOB,MIN(BASIC)
FROM EMPLOYEE
GROUP BY JOB);

EMP_NO EMP_NAME JOB BASIC DA HRA PF GROSSPAY NETPAY


102 BOB CLERK 8000 2400 3200 960 13600 12640
106 TEENA CLERK 6000 1800 2400 720 10200 9480

**********************************************************************
2:c)

UPDATE EMP_2
SET NETPAY=NETPAY+12000
WHERE NETPAY<10000;

SELECT * FROM EMP_2;

EMP_NO EMP_NAME JOB BASIC DA HRA PF GROSSPAY NETPAY


101 ALICE MANAGER 10000 3000 4000 1200 17000 15800
102 BOB CLERK 8000 2400 3200 960 13600 12640
103 CHARLIE ANALYST 12000 3600 4800 1440 20400 18960
104 DAVID SALESMAN 20000 6000 8000 2400 34000 31600
105 EVA ACCOUNTANT 8500 2550 3400 1020 14450 13430
106 TEENA CLERK 6000 1800 2400 720 10200 21480

**********************************************************************
2:d)

SELECT * FROM EMP_2


WHERE GROSSPAY BETWEEN 10000 AND 20000;

EMP_NO EMP_NAME JOB BASIC DA HRA PF GROSSPAY NETPAY


101 ALICE MANAGER 10000 3000 4000 1200 17000 15800
102 BOB CLERK 8000 2400 3200 960 13600 12640
105 EVA ACCOUNTANT 8500 2550 3400 1020 14450 13430
106 TEENA CLERK 6000 1800 2400 720 10200 21480
**********************************************************************
2:e)

SELECT * FROM EMP_2


WHERE GROSSPAY=(SELECT MAX(GROSSPAY) FROM EMP_2);

EMP_NO EMP_NAME JOB BASIC DA HRA PF GROSSPAY NETPAY


104 DAVID SALESMAN 20000 6000 8000 2400 34000 31600

**********************************************************************
3)

CREATE TABLE DEP_1(DEPT_NO NUMBER(20) PRIMARY KEY,


DNAME VARCHAR(20),
LOC VARCHAR(20));
DESC DEP_1;

CREATE TABLE EMP_3(


EMP_NO NUMBER(20) PRIMARY KEY,
ENAME VARCHAR(20),
JOB VARCHAR(20),
MGNR_EMPNO NUMBER(20),
HIREDATE DATE,
SALARY NUMBER(20),
DEPT_NO NUMBER(20),
FOREIGN KEY(DEPT_NO) REFERENCES DEP_1(DEPT_NO),
FOREIGN KEY(MGNR_EMPNO) REFERENCES EMP_3(EMP_NO)
);

DESC EMP_3;

INSERT INTO DEP_1 VALUES(101,'MATHEMATICS','DELHI');


INSERT INTO DEP_1 VALUES(102,'COMMERCE','MUMBAI');
INSERT INTO DEP_1 VALUES(103,'PHYSICS','DELHI');
INSERT INTO DEP_1 VALUES(104,'MATHEMATICS','CHENNAI');
INSERT INTO DEP_1 VALUES(105,'COMMERCE','BANGLORE');
INSERT INTO DEP_1 VALUES(106,'HUMANITIES','MADRAS');

SELECT * FROM DEP_1;

DEPT_NO DNAME LOC


101 MATHEMATICS DELHI
102 COMMERCE MUMBAI
103 PHYSICS DELHI
104 MATHEMATICS CHENNAI
105 COMMERCE BANGLORE
106 HUMANITIES MADRAS

INSERT INTO EMP_3 VALUES(1,'ALICE','PROFESSOR',NULL, TO_DATE('15-JUL-2025', 'DD-


MON-YYYY'),50000,101);
INSERT INTO EMP_3 VALUES(2,'BOB','LECTURAR',1, TO_DATE('12-MAY-2021', 'DD-MON-
YYYY'),55200,102);
INSERT INTO EMP_3 VALUES(3,'CHARLIE','ASSISTANT',1, TO_DATE('23-AUG-2020', 'DD-MON-
YYYY'),51000,103);
INSERT INTO EMP_3 VALUES(4,'DAVID','PROFESSOR',1, TO_DATE('19-APR-2019', 'DD-MON-
YYYY'),52000,104);
INSERT INTO EMP_3 VALUES(5,'EVA','LECTURAR',1, TO_DATE('15-JAN-2023', 'DD-MON-
YYYY'),56350,105);
INSERT INTO EMP_3 VALUES(6,'EWAN','LECTURAR',2, TO_DATE('18-JAN-2023', 'DD-MON-
YYYY'),56500,106);

SELECT * FROM EMP_3;


EMP_NO ENAME JOB MGNR_EMPNO HIREDATE SALARY DEPT_NO
1 ALICE PROFESSOR - 15-JUL-25 50000 101
2 BOB LECTURAR 1 12-MAY-21 55200 102
3 CHARLIE ASSISTANT 1 23-AUG-20 51000 103
4 DAVID PROFESSOR 1 19-APR-19 52000 104
5 EVA LECTURAR 1 15-JAN-23 56350 105
6 EWAN LECTURAR 2 18-JAN-23 56500 106
**********************************************************************

3:a)

SELECT [Link] AS EMPLOYEE_NAME,[Link] AS EMP_SALARY


FROM EMP_3 E,EMP_3 M
WHERE E.MGNR_EMPNO=M.EMP_NO AND [Link]>[Link];

EMPLOYEE_NAME EMP_SALARY
EVA 56350
DAVID 52000
CHARLIE 51000
BOB 55200
EWAN 56500
**********************************************************************

3:b)

SELECT ENAME,SALARY,DEPT_NO
FROM EMP_3 E
WHERE SALARY=(
SELECT MAX(SALARY) FROM EMP_3 E1
WHERE E.DEPT_NO=E1.DEPT_NO);

OUTPUT

ENAME SALARY DEPT_NO


ALICE 50000 101
BOB 55200 102
CHARLIE 51000 103
DAVID 52000 104
EVA 56350 105
EWAN 56500 106

**********************************************************************
3:c)

SELECT DISTINCT [Link],[Link],[Link]


FROM DEP_1 D
JOIN EMP_3 E ON D.DEPT_NO=E.DEPT_NO;

OUTPUT

ENAME DNAME LOC


EVA COMMERCE BANGLORE
CHARLIE PHYSICS DELHI
DAVID MATHEMATICS CHENNAI
BOB COMMERCE MUMBAI
ALICE MATHEMATICS DELHI
EWAN HUMANITIES MADRAS

**********************************************************************
3:d)

SELECT ENAME,SALARY
FROM EMP_3
WHERE SALARY=(
SELECT MIN(SALARY) FROM EMP_3);

ENAME SALARY
ALICE 50000

**********************************************************************
3:e)

SELECT DNAME FROM DEP_1 D


WHERE NOT EXISTS(
SELECT 1 FROM EMP_3 E WHERE E.DEPT_NO=D.DEPT_NO);

DNAME
SCIENCE
**********************************************************************

You might also like