1)Examine the structure of the EMPLOYEES table:
EMPLOYEE_ID NUMBER Primary Key
FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(25)
Which three statements inserts a row into the table? (Choose three)
A. INSERT INTO employees VALUES (NULL, 'JOHN','Smith');
B. INSERT INTO employees( first_name, last_name) VALUES ('JOHN','Smith');
C. INSERT INTO employees
VALUES ('1000','JOHN','NULL');
D. INSERT INTO employees(first_name,last_name, employee_id) VALUES ('1000, 'john','Smith');
E. INSERT INTO employees (employee_id)
VALUES (1000);
F. INSERT INTO employees (employee_id, first_name, last_name)
VALUES ( 1000, 'john',");
2)Which two statements about views are true? (Choose two.)
A. A view can be created as read only.
B. A view can be created as a join on two or more tables.
C. A view cannot have an ORDER BY clause in the SELECT statement.
D. A view cannot be created with a GROUP BY clause in the SELECT statement.
E. A view must have aliases defined for the column names in the SELECT statement.
3)xamine the description of the EMPLOYEES table: EMP_ID NUMBER(4) NOT NULL LAST_NAME VARCHAR2(30) NOT NULL FIRST_NAME VARCHAR2(30)
DEPT_ID NUMBER(2) JOB_CAT VARCHARD2(30) SALARY NUMBER(8,2)
Which statement shows the maximum salary paid in each job category of each department?
A. SELECT dept_id, job_cat, MAX(salary) FROM employees
WHERE salary > MAX (salary);
B. SELECT dept_id, job_cat, MAX(salary) FROM employees
GROUP BY dept_id,job_cat;
C. SELECT dept_id, job_cat, MAX(salary) FROM employees;
D. SELECT dept_id, job_cat, MAX(salary) FROM employees
GROUP BY dept_id;
E. SELECT dept_id, job_cat, MAX(salary)
FROM employees
GROUP BY dept _ id job _ cat salary;
4)
Management has asked you to calculate the value 12*salary* commission_pct for all the employees in the EMP table. The EMP table contains these columns:
LAST NAME VARCNAR2(35) NOT NULL SALARY NUMBER(9,2) NOT NULL COMMISION_PCT NUMBER(4,2)
Which statement ensures that a value is displayed in the calculated columns for all employees?
A. SELECT last_name, 12*salary* commission_pct FROM emp;
B. SELECT last_name, 12*salary* (commission_pct,0) FROM emp;
C. SELECT last_name, 12*salary*(nvl(commission_pct,0)) FROM emp;
D. SELECT last_name, 12*salary*(decode(commission_pct,0)) FROM emp;
5)Examine the description of the STUDENTS table:
STD_ID NUMBER(4)
COURSE_ID VARCHARD2(10)
START_DATE DATE
END_DATE DATE
Which two aggregate functions are valid on the START_DATE column? (Choose two)
A. SUM(start_date)
B. AVG(start_date)
C. COUNT(start_date)
D. AVG(start_date, end_date)
E. MIN(start_date)
F. MAXIMUM(start_date)
6)The EMP table contains these columns:
LAST NAME VARCHAR2(25)
SALARY NUMBER(6,2)
DEPARTMENT_ID NUMBER(6)
You need to display the employees who have not been assigned to any department.
You write the SELECT statement:
SELECT LAST_NAME, SALARY, DEPARTMENT_ID
FROM EMP
WHERE DEPARMENT_ID = NULL;
What is true about this SQL statement?
A. The SQL statement displays the desired results.
B. The column in the WHERE clause should be changed to display the desired results.
C. The operator in the WHERE clause should be changed to display the desired results.
D. The WHERE clause should be changed to use an outer join to display the desired results.
7)
Which two are true about aggregate functions? (Choose two.)
A. You can use aggregate functions in any clause of a SELECT statement.
B. You can use aggregate functions only in the column list of the SELECT clause and in the WHERE clause of a SELECT statement.
C. You can mix single row columns with aggregate functions in the column list of a SELECT statement by grouping on the single row columns.
D. You can pass column names, expressions, constants, or functions as parameters to an aggregate function.
E. You can use aggregate functions on a table, only by grouping the whole table as one single group.
F. You cannot group the rows of a table by more than one column while using aggregate functions.
8)
In a SELECT statement that includes a WHERE clause, where is the GROUP BY clause placed in the SELECT statement?
A. Immediately after the SELECT clause
B. Before the WHERE clause
C. Before the FROM clause
D. After the ORDER BY clause
E. After the WHERE clause
9)
The STUDENT_GRADES table has these columns:
STUDENT_ID NUMBER(12) SEMESTER_END DATE GPA NUMBER(4,3)
The register has requested a report listing the students' grade point averages (GPA), sorted from highest grade point average to lowest within each semester, starting from the earliest date. Which statement accomplishes this?
A. SELECT student_id, semester_end, gpa FROM student_grades
ORDER BY semester_end DESC, gpa DESC;
B. SELECT student_id, semester_end, gpa FROM student_grades
ORDER BY semester _end, ASC,gpa ASC;
C. SELECT student_id, semester_end, gpa FROM student_grades
ORDER BY semester _end, gpa DESC;
D. SELECT student_id, semester_end, gpa FROM student_grades
ORDER BY gpa DESC,semester_end DESC;
E. SELECT student_id, semester_end, gpa FROM student_grades
10)You added a PHONE_NUMBER column of NUMBER data type to an existing EMPLOYEES table. The EMPLOYEES table already contains records of 100 employees. Now, you want to enter the phone numbers of each of the 100 employees into the table.
Some of the employees may not have a phone number available. Which data manipulation operation do you perform?
A. MERGE
B. INSERT
C. UPDATE
D. ADD
E. ENTER
F. You cannot enter the phone numbers for the existing employee records.
11)You would like to display the system date in the format "Monday, 01 June, 2001".
Which SELECT statement should you use?
A. SELECT TO_DATE(SYSDATE, 'FMDAY, DD Month, YYYY') FROM dual;
B. SELECT TO_CHAR(SYSDATE, 'FMDD, DY Month, 'YYYY') FROM dual;
C. SELECT TO_CHAR(SYSDATE, 'FMDay, DD Month, YYYY') FROM dual;
D. SELECT TO_CHAR(SYSDATE, 'FMDY, DDD Month, YYYY') FROM dual;
E. SELECT TO_DATE(SYSDATE, 'FMDY, DDD Month, YYYY') FROM dual;
