SELECT MIN(PRICE) FROM STOCKS;
Answer:
Which of the following query is correct which will display the same output as shown below?
SELECT MAX(LASTNAME||’,’||FIRSTNAME) AS NAME, MAX(SALARY)
FROM EMPLOYEES;
Choose the letter of the correct answer based on the table EMPLOYEES as shown below.
SELECT FIRSTNAME, LENGTH(FIRSTNAME) AS “NUMBER OF CHARACTER”
FROM EMPLOYEES
WHERE SALARY BETWEEN 8300 AND 4400;
This is use to find the numeric position of a named character starting at character position n.
INSTR
Which of the following is the correct query that counts the number of ST_CLERK job_id? Rename the
column as “Total no. of ST_CLERK”.
Select one:
a.
SELECT COUNT(JOB_ID), AS “TOTAL NO. OF ST_CLERK”
FROM EMPLOYEES
WHERE JOB_ID = ‘ST_CLERK’;
b.
SELECT COUNT(JOB_ID), AS “TOTAL NO. OF ST_CLERK”
FROM EMPLOYEES
WHERE JOB_ID != ‘ST_CLERK’;
c.
SELECT COUNT(JOB_ID), AS TOTAL NO. OF ST_CLERK
FROM EMPLOYEES
WHERE JOB_ID = ‘ST_CLERK’;
d.
SELECT COUNT * (JOB_ID), AS “TOTAL NO. OF ST_CLERK”
FROM EMPLOYEES
WHERE JOB_ID = ‘ST_CLERK’;
What query should be used in order toget theSUBSTR function that returns the job_id = ‘REP’.
Select one:
a.
SELECT JOB_ID FROM EMPLOYEES
WHERE JOB_ID LIKE ‘%REP%’;
b.
SELECT JOB_ID FROM EMPLOYEES
WHERE SUBSTR=’REP’;
c.
SELECT JOB_ID FROM EMPLOYEES
WHERE SUBSTR(JOB_ID,4)=’REP’;
d.
SELECT JOB_ID FROM EMPLOYEES
WHERE SUBSTR(JOB_ID,4)=’%REP%’;
Which of the following is the correct query that display the Minimum firstname concatenated to
lastname? Look for the maximum salary that is less than 10000 per department_id.
Select one:
a.
SELECT MIN(FIRSTNAME||LASTNAME)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
HAVING MAX(SALARY);
b.
SELECT MIN(FIRSTNAME||LASTNAME)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
HAVING MAX(SALARY)<10000;
c.
SELECT MIN(FIRSTNAME,LASTNAME)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
HAVING MAX(SALARY)<10000;
d.
SELECT MIN(FIRSTNAME||LASTNAME)
FROM EMPLOYEES
HAVING MAX(SALARY)<10000
GROUP BY DEPARTMENT_ID;
Which of the following query is correct which will display the same output as shown below?
Select one:
a.
SELECT COUNT *(MANAGER_ID) AS “NO. OF MANAGER_ID” FROM EMPLOYEES;
b.
SELECT COUNTDISTINCT(MANAGER_ID) AS “NO. OF MANAGER_ID” FROM EMPLOYEES;
c.
SELECT COUNT(MANAGER_ID) NO. OF MANAGER_ID FROM EMPLOYEES;
d.
SELECT COUNT(MANAGER_ID) AS “NO. OF MANAGER_ID” FROM EMPLOYEES;
Based on the given SELECT statement below what would be the possible output?
SELECT FIRSTNAME, SALARY, LPAD(SALARY * 0.10 + SALARY – 100, 8, ‘$’) AS BONUS
FROM EMPLOYEES
WHERE DEPARTMENT_ID NOT IN (10, 110, 50
AND SALARY=17000;
Select one:
a.
NENA 17000 $$$18600
LEX 17000 $$$18600
What query should be used in order todisplay the Firstname concatenated to employees original salary
plus concatenate again a new column salary that multiplies the original salary into three. Rename the
column as Dream Salaries.Note sort the salary in descending order.
Select one:
a.
SELECT (FIRSTNAME||’ EARNS ‘|| SALARY || ‘MONTHLY BUT WANTS’ || SALARY * 3)
AS “DREAM SALARIES”
FROM EMPLOYEES
ORDER BY SALARY DESC;
It is a table that is owned by the user SYS and can be accessed by all users.
Dual
Based on the given SELECT statement below what would be the possible output?
SELECT TRUNC(563.396,2)FROM DUAL;
Select one:
a.
563.39
What query should be used in order to display the employees lastname concatenated with firstname
and with a ‘, (comma)’ in between. A rename on the concatenated column as Complete Name. Note all
values in Complete Name column should be in lowercase plus display the length of employees lastname
for all employees whose lastname starts with letter M sort the lastname in its default order.
SELECT LOWER(LASTNAME||’,’||FIRSTNAME) AS “COMPLETE NAME”,
LENGTH(LASTNAME)
FROM EMPLOYEES
WHERE LASTNAME LIKE ‘M%’;
MAX(NAME) FROM STOCKS;
Answer:
PALMOLIVE
Based on the given SELECT statement below what would be the possible output?
SELECT TRUNC(563.396,-2) FROM DUAL;
Select one:
a.
500
SELECT COUNT(*) FROM STOCKS;
Answer:
Which of the following is the correct query that displays the lowest salary rename as “LOWEST SALARY”,
maximum salary rename as “HIGHEST SALARY” and department_id concatenated to job_id?
Select one:
SELECT CONCAT(DEPARTMENT_ID,JOB_ID),MIN(SALARY), MAX(SALARY)
FROM EMPLOYEES;
This is use to return one result per row.
Single row
What is the return value if the user try to do the following:
SELECT TRUNC (65.73,-2) FROM DUAL;
SELECT AVG(WAREHOUSE) FROM STOCKS;
Answer:
Which of the following is the correct query that display the maximum lastname concatenated to
firstname and rename the column as “Employees Name”, Job_id and apply the ff. functions:
Count the Job_id; Apply where condition whose lastname ends with letter ‘N’; Group the job_id; and
Apply having clause of employees having average salary that is greater than 10000.
SELECT MAX(LASTNAME||FIRSTNAME) AS “EMPLOYEES NAME”, JOB_ID, COUNT(JOB_ID)
FROM EMPLOYEES
WHERE LASTNAME LIKE ‘%N’
GROUP BY JOB_ID
HAVING AVG(SALARY)>10000;
This is use to accept character input and can return both character and number values
Character function
What query should be used in order to display the firstname concatenated to salary with additional
column salary that provides a computation of salary * 2. Rename the column as Increase of all
employees whose lastname ends with N.
Select one:
a.
SELECT (FIRSTNAME || ‘SALARY OF’ || SALARY || ‘IF MULITPLY BY TWO THEN HE/SHE WLL GOT A NEW
SALARY OF’ || SALARY * 2)AS INCREASE FROM EMPLOYEES WHERE LASTNAME LIKE ‘%N’;
SELECT COUNT (AVG(PRICE)) FROM STOCKS GROUP BY WAREHOUSE;
Answer:
27
SELECT MIN(ID) FROM STOCKS;
Answer:
Based on the given SELECT statement below what would be the possible output?
SELECT (LASTNAME||FIRSTNAME), JOB_ID
FROM EMPLOYEES
WHERE SUBSTR (JOB_ID,4)=’REP’;
ABELELLEN SA_REP
TALORJONATHAN SA_REP
GRANTKIMBERLY SA_REP
FAYPAT MK_REP
Which of the following is the correct query that displays the MINIMUM salary of employees per job_id?
Note job_id should be in lowercase.
SELECT MIN(SALARY) AS “LOWEST SALARY”, LOWER(JOB_ID)
FROM EMPLOYEES;
It is use to accept numeric input and return numeric values.
Number function
What query should be used in order to display the employees lastname concatenated to salary. Format
the salary column to 6 character long left padded with ‘*’ as special character for all employees whose
manager_id is null or salary between 4000 and 6000 Rename the column as employees and their
Salaries
Select one:
a.
SELECT (FIRSTNAME||(SALARY,6,’*’)) AS “EMPLOYEES AND THEIR SALARIES”
FROM EMPLOYEES
WHERE MANAGER_ID IS NULL
OR SALARY BETWEEN 4000 AND 6000;
Based on the given SELECT statement below what would be the possible output?
SELECT TRUNC(563.396,1)FROM DUAL;
563.3
SELECT SUM(QTY) FROM STOCKS WHERE WAREHOUSE IN (1,5) GROUP BY WAREHOUSE HAVING
MAX(WAREHOUSE) >=5;
Answer:
FROM INVENTORY;
Which of the following query is correct which will display the same output as shown below?
SELECT JOB_ID, COUNT(JOB_ID)”NO. OF JOB_ID”, SUM(SALARY), AVG(SALARY)
FROM EMPLOYEES;
Which of the following is the correct report that display the smallest (minimum) Surname.. and apply
the ff. functions:
Get the average salary; Group the data per job_id; Get only job_id with a keyword “REP”; and Apply
having clause, which the max salary per job_id is greater than 5000.
SELECT MIN(LASTNAME), AVG(SALARY)
FROM EMPLOYEES
GROUP BY JOB_ID
WHERE JOB_ID LIKE ‘%REP%’
HAVING MAX(SALARY)>500;
What query should be used in order todisplay a report that trim the letter ‘A’ from lastname of all
employees whose department_id between 60 and 90.
SELECT TRIM(LASTNAME,’A’)
FROM EMPLOYEES
WHERE DEPARTMENT_ID BETWEEN 60 AND 90;
Which of the following query is correct which will display the same output as shown below?
a.
SELECT COUNT(*) FROM EMPLOYEES;
Which of the following is the correct query that display the Job_id and add the ff: function:
Count the total number of job_id per distinct values; Compute for the summary of salary per job_id; and
Compute for the average salary per job_id
SELECT DISTINCT(JOB_ID), COUNT(JOB_ID) AS “NO. OF JOB_ID”, SUM(SALARY) AS “TOTAL SALARY”,
AVG(SALARY) AS “AVERAGE SALARY” FROM EMPLOYEES GROUP BY JOB_ID
Which of the following query is correct which will display the same output as shown below?
SELECT MIN(LASTNAME), JOB_ID, MAX(SALARY)
FROM EMPLOYEES
WHERE JOB_ID LIKE ‘%REP%’
GROUP BY JOB_ID;
Which of the following query is correct which will display the same output as shown below?
SELECT DISTINCT(JOB_ID), COUNT(JOB_ID) AS “NO. OF JOB_ID”, SUM(SALARY) AS “TOTAL SALARY”,
AVG(SALARY) AS “AVERAGE SALARY” FROM EMPLOYEES GROUP BY JOB_ID;
Which of the following query is correct which will display the same output as shown below?
SELECT COUNT(MANAGER_ID) AS “NO. OF MANAGER_ID” FROM EMPLOYEES;
Based on the given SELECT statement below what would be the possible output?
SELECT LOWER(LASTNAME||’with a salary of’||SALARY) as Record, LENGTH(LASTNAME) as Lname
FROM EMPLOYEES
WHERE MANAGER_ID IS NULL
OR SALARY IS NULL;
Kingwith a salary of 24000 4
Hunoldwith a salary of 2400 6
What query should be used in order to display the lastname and salary of all employees whose
department_id = 60 or job_id like ‘_T%’. Format the salary to be 15 character long, left padded with ‘$’
as special character. Label the column Salary.
Select one:
a.
SELECT LASTNAME, LPAD(SALARY,15,’$’)
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 60 OR JOB_ID LIKE ‘_T’;
Based on the given SELECT statement below what would be the possible output?
SELECT TRIM(‘K’ FROM LASTNAME)AS TRIM, LASTNAME, DEPARTMENT_ID, MANAGER_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 50
AND MANAGER_ID = 100;
MOURGOS MOURGOS 50 100
SELECT COUNT(DISTINCT QTY) FROM STOCKS;
Answer:
Which of the following query is correct which will display the same output as shown below?
SELECT MIN(LASTNAME),MAX(FIRSTNAME),SUM(SALARY),AVG(SALARY)
FROM EMPLOYEES
WHERE JOB_ID LIKE ‘%ST%’;
This is used to converts the first letter of each word to uppercase and the remaining letters to
lowercase.
INITCAT
Trims leading or trailing characters (or both) from a character string.
Trim
Which of the following query is correct which will display the same output as shown below?
SELECT MIN(initcap(FIRSTNAME||’ surnname is ‘||LASTNAME)) AS “COMPLETE NAME”,
MIN(MANAGER_ID) AS “LOWEST MANAGER_ID” FROM EMPLOYEES;
Based on the given SELECT statement below what would be the possible output?
SELECT INITCAP(LASTNAME||’,’||FIRSTNAME) AS NAME
FROM EMPLOYEES
WHERE JOB_ID LIKE ’%PR%’;
King,Steven
Hunold,Alexander
Ernst,Bruce
Lorentz,Diana
Based on the given SELECT statement below what would be the possible output?
SELECT MOD(SALARY,10) AS “EXCESS SALARY”
FROM EMPLOYEES
WHERE SALARY < 5000;
COUNT(PRICE) FROM STOCKS;
Answer:
3
SELECT AVG(NVL(QTY,0)) FROM STOCKS;
Answer:
8.22
