[Link] table.
[Link] column to pt, parent phno.
3. alter stu tab to sphn_student?
[Link] data set into table?
ROLLNO NAME DOF CGPA MOBILENO
G PARENT_MOBILENO
---------- ---------------------------------------------------------------------------------------------------- -------- ----------
--------------- - ---------------
6E2 RANI 15-08-03 8.8 9077877747
F 9876543210
101 Raju 14-05-03 8.5 9876543210
M 9123456780
102 Sneha 20-12-02 9.1 9876501234
F 9123456781
103 Kiran 03-02-04 7.8 9845001111
M 9123456782
111 Raju 21-07-03 8.5 9876543210
M 9876500000
5. update 1 stu cgpa?
ROLLNO NAME
---------- ----------------------------------------------------------------------------------------------------
6E2 RANI
101 Raju
102 Sneha
111 Raju
6. name= raju cgpa?
select cgpa
from sphn_student
where name='Raju';
CGPA
----------
8.5
7. list out stu who as same cgpa?
select cgpa
from sphn_student
GROUP BY cgpa
HAVING COUNT(*) > 1
no rows selected
8. display stu name with 8 cgpa?
select name
from sphn_student
where cgpa=8.8;
NAME
----------------------------------------------------------------------------------------------------
RANI
Set operations:
[Link] dets of job where min salary>10000?
select *from jobs
where min_salary > 10000;
JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
AD_PRES President 20080 40000
AD_VP Administration Vice President 15000 30000
2. dis 1stname , join date of emp who joined btw 2002-2005?
SELECT first_name, hire_date
FROM employees
WHERE TO_CHAR(hire_date, 'YYYY') IN ('2002', '2005');
(oR)
SELECT first_name, hire_date
FROM employees
WHERE EXTRACT(YEAR FROM hire_date) IN (2002, 2005);
(or)
SELECT first_name, hire_date
FROM employees
WHERE hire_date BETWEEN '01-jan-2002' and '02-jan-2004';
IRST_NAME HIRE_DAT
-------------------- --------
Karen 05-01-05
Alberto 10-03-05
Peter 30-01-05
David 24-03-05
Peter 20-08-05
Lindsey 10-03-05
Louise 15-12-05
Clara 11-11-05
Lisa 11-03-05
Alyssa 19-03-05
Alexis 20-02-05
FIRST_NAME HIRE_DAT
-------------------- --------
Kelly 14-06-05
Jennifer 13-08-05
Britney 03-03-05
36 rows selected.
3. dis emp who joined after 1st jn 2008?
SELECT * FROM employees
WHERE hire_date > TO_DATE('01-01-2008', 'DD-MM-YYYY');
MPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER
HIRE_DAT JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- -------- ----------
---------- -------------- ---------- -------------
190 Timothy Gates TGATES 650.505.3876 11-07-06 SH_CLERK
2900 122 50
191 Randall Perkins RPERKINS 650.505.4876 19-12-07 SH_CLERK
2500 122 50
192 Sarah Bell SBELL 650.501.1876 04-02-04 SH_CLERK
4000 123 50
193 Britney Everett BEVERETT 650.501.2876 03-03-05 SH_CLERK
3900 123 50
194 Samuel McCain SMCCAIN 650.501.3876 01-07-06
SH_CLERK 3200 123 50
195 Vance Jones VJONES 650.501.4876 17-03-07 SH_CLERK
2800 123 50
196 Alana Walsh AWALSH 650.507.9811 24-04-06 SH_CLERK
3100 124 50
197 Kevin Feeney KFEENEY 650.507.9822 23-05-06 SH_CLERK
3000 124 50
96 rows selected.
4. dis f_n , j_d who is it progr or salesman?
SELECT first_name, hire_date
from employees
where job_id in('it_prog','salesman');
no rows selected
no rows selected
5. details of empl(150-160)?
SELECT *FROM employees
WHERE employee_id IN (150, 160);
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER
HIRE_DAT JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- -------- ----------
---------- -------------- ---------- -------------
150 Peter Tucker PTUCKER 011.44.1344.129268 30-01-05 SA_REP
10000 .3 145 80
160 Louise Doran LDORAN 011.44.1345.629268 15-12-05 SA_REP
7500 .3 146 80
6. display f_n, s,c_pct, h_d >1000?
SELECT first_name, salary, commission_pct, hire_date
FROM employees
WHERE salary > 10000;
IRST_NAME SALARY COMMISSION_PCT HIRE_DAT
-------------------- ---------- -------------- --------
Michael 13000 17-02-04
Shelley 12008 07-06-02
Steven 24000 17-06-03
Neena 17000 21-09-05
Lex 17000 13-01-01
Nancy 12008 17-08-02
Den 11000 07-12-02
John 14000 .4 01-10-04
Karen 13500 .3 05-01-05
Alberto 12000 .3 10-03-05
Gerald 11000 .3 15-10-07
FIRST_NAME SALARY COMMISSION_PCT HIRE_DAT
-------------------- ---------- -------------- --------
Eleni 10500 .2 29-01-08
Clara 10500 .25 11-11-05
Lisa 11500 .25 11-03-05
Ellen 11000 .3 11-05-04
15 rows selected.
8. display who joine in may month?
SELECT *FROM employees
WHERE TO_CHAR(hire_date, 'Month') = 'May ';
no rows selected
no rows selected
[Link] f_n,l_n with s%?
SELECT *
FROM employees
WHERE first_name LIKE 'S%' OR last_name LIKE 'S%';
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER
HIRE_DAT JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- -------- ----------
---------- -------------- ---------- -------------
159 Lindsey Smith LSMITH 011.44.1345.729268 10-03-05 SA_REP
8000 .3 146 80
161 Sarath Sewall SSEWALL 011.44.1345.529268 03-11-06 SA_REP
7000 .25 146 80
166 Sundar Ande SANDE 011.44.1346.629268 24-03-08 SA_REP
6400 .1 147 80
171 William Smith WSMITH 011.44.1343.629268 23-02-07 SA_REP
7400 .15 148 80
173 Sundita Kumar SKUMAR 011.44.1343.329268 21-04-08
SA_REP 6100 .1 148 80
182 Martha Sullivan MSULLIVA 650.507.9878 21-06-07 SH_CLERK
2500 120 50
184 Nandita Sarchand NSARCHAN 650.509.1876 27-01-04
SH_CLERK 4200 121 50
192 Sarah Bell SBELL 650.501.1876 04-02-04 SH_CLERK
4000 123 50
194 Samuel McCain SMCCAIN 650.501.3876 01-07-06
SH_CLERK 3200 123 50
20 rows selected.
Numeric functions:
1.