-
Notifications
You must be signed in to change notification settings - Fork 0
/
TEST_01.txt
132 lines (92 loc) · 4.79 KB
/
TEST_01.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
Link:https://github.com/oracle/dotnet-db-samples/blob/master/schemas/scott.sql
Copy hte rows from 35 to 103 from above link and execute them in SQL developers which creates 3 tables - (Emp, dept and salgrade) with data. All below SQL questions based these 3 tables only. Answer all below sql questions with in 1 hr and share with Ruth.
CREATE TABLE DEPT
(DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
DNAME VARCHAR2(14) ,
LOC VARCHAR2(13) ) ;
DROP TABLE EMP;
CREATE TABLE EMP
(EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);
INSERT INTO DEPT VALUES
(10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES
(30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES
(40,'OPERATIONS','BOSTON');
INSERT INTO EMP VALUES
(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO EMP VALUES
(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO EMP VALUES
(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMP VALUES
(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO EMP VALUES
(7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO EMP VALUES
(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO EMP VALUES
(7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO EMP VALUES
(7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87')-85,3000,NULL,20);
INSERT INTO EMP VALUES
(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO EMP VALUES
(7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO EMP VALUES
(7876,'ADAMS','CLERK',7788,to_date('13-JUL-87')-51,1100,NULL,20);
INSERT INTO EMP VALUES
(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO EMP VALUES
(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES
(7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
DROP TABLE BONUS;
CREATE TABLE BONUS
(
ENAME VARCHAR2(10) ,
JOB VARCHAR2(9) ,
SAL NUMBER,
COMM NUMBER
) ;
DROP TABLE SALGRADE;
CREATE TABLE SALGRADE
( GRADE NUMBER,
LOSAL NUMBER,
HISAL NUMBER );
INSERT INTO SALGRADE VALUES (1,700,1200);
INSERT INTO SALGRADE VALUES (2,1201,1400);
INSERT INTO SALGRADE VALUES (3,1401,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,9999);
COMMIT;
================================================================================
Questions:
1. CREATE A TABLE WITH YOUR OWN EXAMPLE which should have PRIMARY KEY, NOT NULL ,UNIQUE and FOREIGN KEY constraints.
2. display data from emp table whose DEPTNO is '20' and who joined in the year 1981'
3. displays the EMPNO ,ENAME,SALARY and SAL + COMMISSION as total salary from employee table whose commission is not null and only for dept 30.
4. write a SQL query to find the unique designations from employee table.
5. write a SQL query to list the employees’ name, increased their salary by 50% in descending order.
6. write a SQL query to find the minimum ,maximum and average salary BY department for those employees whose job is 'SALESMAN' and'CLERK'
7. write a SQL query to find total salary of employees who commission is null and joined in APRIL month.
8. write a SQL query to find those employees whose commission is more than their salary.
9. write a SQL query to find those employees whose salary exceeds 2000 after giving 10% increment.
10.write a SQL query to find which department total salary is grathan 10000.
11.write a SQL query to show each employee experience in months till current month.
12.write a SQL query to show total salary of all the emplyees from department number 10 and 30 and who's commisson is null and salary less than or equal to 1000
13.write a SQL query to show $ at the begining and end of each emplyee name.
14.display data from emp table whose joined in the month of may but not between year 82 to 88.
15. display data from emp table whose names starts with 'A' and joined in the year '87'.
16. write a query to show which job total salary is less than or equal to 5000;
17. write a query to display hire date in 'yyyy-mm-dd' format
18. write a query to replace null to '1' from comm filed .
19.write a query to display count of employees who's birthday fall in each month.
20. write a query to insert new record in emplyee table with your name.