| 1. | Consider the following SQL statement:
SELECT manufacturer_id, COUNT(*), order_date
FROM ITEM
WHERE price > 14.00
GROUP BY order_date, manufacturer_id
HAVING COUNT(*) > 10
Which clause specifies the rows that will be returned from the ITEM table? |
| a. | | WHERE price > 14.00 |
| b. | | HAVING COUNT(*) > 10 |
| c. | | GROUP BY order_date, manufacturer_id |
| d. | | SELECT manufacturer_id, COUNT(*), order_date
|
|
|
|
|
|
|
| 2. | The BOOK table was created with the following statement:
1. CREATE TABLE BOOK
2. (book_id NUMBER(9)
3. CONSTRAINT book_id_pk PRIMARY KEY,
4. name VARCHAR2(25),
5. title VARCHAR2(25),
6. CONSTRAINT booktitle_nn NOT NULL);
Which line in the statement causes a syntax error? |
| a. | | 1st |
| b. | | 6th |
| c. | | 3rd |
| d. | | 2nd
|
|
|
|
|
|
|
| 3. | The ITEM table has six columns. Since the table is often queried with a join to the SALE table, an index was created on five of the columns in the ITEM table. What will be the result? |
| a. | | Inserts to the table will be slower |
| b. | | The size of the ITEM table will be increased |
| c. | | All queries on the ITEM table will be faster if it does not contain a large number of NULL values |
| d. | | The speed of deletes will be increased
|
|
|
|
|
|
|
| 4. | Which of the following relationships exist for patient and doctor if a patient can have many doctors, a doctor can have many patients, and a doctor can be a patient? |
| a. | | Recursive |
| b. | | One-to-one |
| c. | | One-to-many |
| d. | | Many-to-one |
| e. | | Many-to-many
|
|
|
|
|
|
|
| 5. | Each employees salary must be increased by 10% and their commission by 20% of their newly increased salary. Display the new salary and commission plus each employee's total compensation.
Evaluate the following SQL statements:
1. SELECT sal * 1.1 "Salary", sal * 1.1 * .2 "Commission", sal * 1.1 + sal * 1.1 * .2 "Compensation"
FROM emp;
2. SELECT sal * 1.1 "Salary", sal * 1.1 * .2 "Commission", (sal * 1.1) + (sal * .2) "Compensation"
FROM emp;
3. SELECT sal * 1.1 "Salary", sal * .2 "Commission", sal * 1.1 + sal * 1.1 * .2 "Compensation"
FROM emp;
What are the results of these statements? |
| a. | | Two statements return the desired result |
| b. | | Only statement one returns the desired result |
| c. | | Only statement three returns the desired result |
| d. | | None of the statements return the desired result
|
|
|
|
|
|
|
| 6. | The EMP table contains the following columns:
EMPNO NUMBER(6)
HIREDATE DATE
Review the following SELECT statement:
SELECT empno, TO_DATE('31-DEC-05') - hiredate
FROM emp
WHERE hiredate BETWEEN '31-DEC-04' AND '31-DEC-05';
What are the results of this statement? |
| a. | | Only numeric values are returned |
| b. | | The employee numbers of any employees hired on January 1, 2004 are displayed |
| c. | | Date and numeric values are returned |
| d. | | The employee numbers of all employees hired before the year 2000 are displayed |
| e. | | Only the employee numbers of all employees are displayed because the BETWEEN operator used in the WHERE clause condition is a number function
|
|
|
|
|
|
|
| 7. | Which construct can be used to return data based on an unknown condition? |
| a. | | An ORDER BY clause |
| b. | | An GROUP BY clause |
| c. | | A WHERE clause with an OR condition |
| d. | | A Subquery
|
|
|
|
|
|
|
| 8. | How many values can a subquery use with the <> operator return? |
| a. | | Only 1 |
| b. | | Up to 2 |
| c. | | Unlimited |
| d. | | Up to 10
|
|
|
|
|
|
|
| 9. | Which SQL SELECT statement clause is an example of the selection capability, but not the joining capability? |
| a. | | WHERE d.deptno = 10 |
| b. | | SELECT * |
| c. | | WHERE d.deptno = e.deptno |
| d. | | SELECT id_number, description, cost
|
|
|
|
|
|
|
| 10. | Select can be used in FROM clause of SQL select. |
| a. | | True |
| b. | | False
|
|
|
|
|
|
|
| 11. | Which component is a literal in the following select statement?
Select 'Emp name :'||ename from emp where deptno=20; |
| a. | | ename. |
| b. | | 20. |
| c. | | emp name. |
| d. | | ||.
|
|
|
|
|
|
|
| 12. | Which of the following SQL statements defines a FOREIGN KEY constraint on the DEPT NO column of the EMP table? |
| a. | | CREATE TABLE EMP (empno NUMBER(4), ename VARCHAR2(35), deptno NUMBER(7,2) NOT NULL, CONSTRAINT emp_deptno_fk FOREIGN KEY deptno REFERENCES dept deptno); |
| b. | | CREATE TABLE EMP (empno NUMBER(4), ename VARCHAR2(35), deptno NUMBER(7,2) CONSTRAINT emp_deptno_fk REFERENCES dept (deptno)); |
| c. | | CRETE TABLE EM (empno NUMBER(4), ename VARCHAR2(35) deptno NUMBER (7,2) NOT NULL, CONSTRAINT em_deptno_fk REFERENCES dept (deptno) FOREIGN KEY (deptno)); |
| d. | | CREATE TABLE EMP (empno NUMBER (4), ename VARCHAR2(35), deptno NUMBER(7,2) FOREIGN KEY CONSTRAINT emp deptno fk REFERENCES dept (deptno));
|
|
|
|
|
|
|
