Scribd
Upload
22 views10 pages

DBMS Session 1 (5)

Uploaded by

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

DBMS Session 1 (5)

Uploaded by

Sbas Nagar
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 10

SESSION 1-Oracle Application Express

Table Name-EMPLOYEES

Query-1:
SELECT first_name, last_name, job_id
FROM employees
WHERE job_id = 'SA REP';

Query-2
SELECT country_id, country_name, region id
FROM countries;

Query-3
SELECT location id, city, state province
FROM locations;

Query-4
SELECT last name, salary,
salary + 300
FROM employees;
Query-5
SELECT last name, salary,
12*salary +100
FROM employees;

Query-6
SELECT last name, salary,
12* (salary +100)
FROM employees;

Query-7
SELECT last_name, job_id, salary, commission_pct,
salary*commission_pct
FROM employees;
Query-8
SELECT last name "Name",
salary*12 "Annual Salary"
FROM employees;

Query-9
SELECT department_id | |
department name
FROM departments;

Query-10
SELECT first name | |' ' |l
last name AS "Employee Name"
FROM employees;
SESSION-2
Query-11
SELECT last name | | ' has a monthly
salary of ' | | salary | | '
dollars.' AS Pay
FROM employees;

Query-12
SELECT employee_id, first_name,
last name
FROM employees
WHERE employee_id = 101;

Query-13
SELECT employee_id, last_name, department_id
FROM employees
WHERE department_id = 90;

Query-14
SELECT last name, salary
FROM employees
WHERE salary BETWEEN 9000 AND 11000;

Query-15
SELECT city, state_province,
country id
FROM locations
WHERE country_id IN ('UK' , 'CA') ;

Query-16
SELECT last name
FROM employees
WHERE last name LIKE ' 0%' ;

Query-17
SELECT last_name, commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;

SESSION-3
Query-18
SELECT last name, department_id, salary
FROM employees
WHERE department_id > 50 AND salary > 12000;

Query-19
SELECT department_name, manager_id, location id
FROM departments
WHERE location id = 2500 OR manager_id=124;

Query-20
SELECT department name, location id
FROM departments
WHERE location id NOT IN (1700,1800);

Query-21
SELECT last name, hire date
FROM employees
ORDER BY hire date;

Query-22
SELECT last name, hire date
FROM employees
ORDER BY hire date DESC;

Query-23
SELECT department_id,
last name
FROM employees
WHERE department id <= 50
ORDER BY department_id,
last name;
Query-24
SELECT (319/29) + 12
FROM DUAL;

SESSION-4
Query-25
SELECT last name
FROM employees
WHERE LOWER (last name) = 'abel';

Query-26
SELECT LOWER (last name) | I
LOWER (SUBSTR (first_name, 1,1) )
AS "User Name"
FROM employees;

Query-27
SELECT country_name, MOD (airports, 2)
AS "Mod Demo"
FROM wf countries;

Query-28
SELECT SYSDATE
FROM dual;

Query-29
SELECT employee_id, hire_date,
ROUND (MONTHS BETWEEN (SYSDATE, hire date) ) AS TENURE,
ADD MONTHS (hire date, 6) AS REVIEW,
NEXT DAY (hire date, 'FRIDAY'), LAST DAY (hire date)
FROM employees
WHERE MONTHS BETWEEN (SYSDATE, hire_date) > 36;

SESSION-5
Query-30
SELECT TO CHAR (salary,
'$99,999') AS "Salary"
FROM employees;
Query-31
SELECT TO DATE ('27-Oct-95', 'DD-Mon-YY' )
AS "Date"
FROM dual;

Query-31
SELECT last_name, TO_CHAR(hire_date, 'DD-Mon-YY' )
FROM employees
WHERE hire date < TO DATE('01-Jan-90' , 'DD-Mon-YY' ) ;

Query-32
SELECT TO CHAR (NEXT DAY (ADD MONTHS (hire_date, 6) , 'FRIDAY') ,
'fmDay, Month ddth, YYYY') AS "Next Evaluation"
FROM employees
WHERE employee_id = 100;

Query-33
SELECT country_name, NVL (internet_extension, 'None')
AS "Internet extn"
FROM wf countries
WHERE location = 'Southern Africa'
ORDER BY internet extension DESC;

Query-34
SELECT last name,
NVL (commission_pct, 0) *250
AS "Commission"
FROM employees
WHERE department_id IN(80,90);

Query-35
SELECT last_name, salary,
NVL2 (commission_pct, salary + (salary * commission_pct) ,
salary)
AS income
FROM employees
WHERE department_id IN(80,90);
Query-36
SELECT last name,
COALESCE (commission_pct, salary, 10)
AS "Comm"
FROM employees
ORDER BY commission_pct;

Query-37
SELECT last name,
CASE department id
WHEN 90 THEN 'Management'
WHEN 80 THEN 'Sales'
WHEN 60 THEN 'It'
ELSE 'Other dept.'
END AS "Department"
FROM employees;

Query-38
SELECT last_name,
DECODE (department_id,
90, 'Management' ,
80, 'Sales',
60, 'It',
'Other dept.')
AS "Department"
FROM employees;

SECTION-9
Query-39
SELECT department_id, AVG (salary)
FROM employees
GROUP BY department id
ORDER BY department_id;

Query-40
SELECT MAX (salary)
FROM employees
GROUP BY department_id;

Query-41
SELECT department_id, MAX (salary)
FROM employees
GROUP BY department_id;

Query-42
SELECT COUNT (country_name), region id
FROM wf countries
GROUP BY region_id
ORDER BY region_id;

Query-43
SELECT department_id, MAX (salary)
FROM employees
WHERE last name != 'King'
GROUP BY department_id;

Query-44
SELECT region_id, ROUND (AVG (population) ) AS population
FROM wf countries
GROUP BY region_id
ORDER BY region_id;

Query-45
SELECT country_id, COUNT (language_id) AS "Number of
languages"
FROM wf_spoken_languages
GROUP BY country_id;

Query-46
SELECT department_id, job_id,
count (*)
FROM employees
WHERE department_id > 40
GROUP BY department_id, job_id;
Query-47
SELECT max (avg (salary) )
FROM employees
GROUP by department_id;

Query-48

You might also like