SQL - The Shortest Route For Beginners (2015)

Appendix. Answers to Test Your Skills Questions

Answers: Chapter – 1

1 C 2 B 3 C 4 A 5 A 6 C 7 C 8 B:

9 A 10 B 11 C:

12: A=DCL D=DDL G=DML

B=DML E=DML H=DDL

C=DDL F=DCL

13:

Answers: Chapter – 2

1. Microsoft SQL Server, IBM DB2, and MySQL.

2. Location of Java.exe file.

3. SQL Command Line utility under Oracle XE program group.

4. Connection Name, Username, Password, Hostname, Port, and System Identifier (SID).

Answers: Chapter – 3

1. Select * from Locations;

2. Select first_name, salary from Employees;

3. Select salary, first_name from Employees;

4. Yes

5. Yes

6. The expression salary x 12 Annual Salary contains two errors. You must use the asterisk character (*) for multiplication and enclose the alias in quotes like this: “Annual Salary”. The column name is also not correct is should be salary. Finally, the search string used in the LIKE operator must be enclosed in single quotations like this: LIKE ‘%05’.

7. Select first_name, salary, commission_pct

From employees

Where commission_pct is not null;

8. Select first_name||’ ‘|| last_name Employees, department_id

From Employees

Where department_id=10 or department_id=20

Order by last_name;

9. Select * from employees where last_name like ‘%y%’;

10. Select first_name, salary, commission_pct

From employees

Where commission_pct is null and department_id=90;

11. Select first_name||' '||last_name Employee, hire_date

from employees

where hire_date between '01-JUL-03' and '30-SEP-03'

order by hire_date;

12. Select first_name||' '||last_name Employee, salary

from employees

where salary < 3000 or salary > 10000

order by salary;

13. Select first_name||' '||last_name Employee, salary, department_id

from employees

where department_id in (10,20,30) and salary > 3000

order by salary;

14. Select last_name,salary,commission_pct,salary*commission_pct*12 "ANNUAL COMMISSION"

from employees

where job_id in ('SA_MAN', 'SA_REP')

order by employee_id;

15. Select first_name||' '||last_name Employee, hire_date

from employees

where hire_date not like '%03'

order by hire_date;

16. Select distinct department_id from employees order by department_id desc;

17. Select last_name, department_id, salary

from employees

where (department_id=50 or department_id=90) and salary >= 5800;

Answers: Chapter – 4

1. Select employee_id, first_name, round(salary+(salary*10)/100) INCREMENTED_SALARY

from employees;

2. Select concat(concat(first_name,' '),last_name)||' ['||job_id||']' "Employee and Job ID"

from employees;

3. Select last_name, to_char(hire_date, 'fmDD "of" Month YYYY') HIRE_DATE

from employees

where hire_date like '%03';

4. Select department_name, length(department_name) CHARACTERS

from departments

order by department_id;

5. Select first_name Employee, nvl(to_char(manager_id),'No Manager') Manager

from employees;

6. Select first_name, substr(first_name,2,3) extracted

from employees;

7. Select first_name||' '||last_name employee,

to_char(hire_date,'yyyy.dd.mon') hired_on

from employees

where first_name='Jennifer';

8. Select employee_id, hire_date, months_between(sysdate,hire_date) tenure

from employees

where months_between(sysdate,hire_date) > 100;

9. Select department_id, job_id, count(*) NUMBER_OF_EMPLOYEES

from employees

group by department_id, job_id

order by department_id;

10. Select job_id, min(salary) minimum_salary

from employees

group by job_id

having min(salary) > 15000;

Answers: Chapter – 5

1. No. It can also be used in HAVING, INTO, and SET clauses.

2. a. A subquery must be enclosed in parentheses.

b. A subquery must not contain an ORDER BY clause.

Here is the corrected statement:

SELECT first_name, job_id, department_id

FROM employees

WHERE department_id = (SELECT department_id

FROM employees

WHERE first_name=’Adam’)

ORDER BY department_id;

3. IN or NOT IN

4. Select first_name,last_name,job_id,salary

from employees

where salary = (select salary

from employees

where first_name='Karen' and last_name='Colmenares');

5. Select first_name,department_id,job_id

from employees

where department_id in (select department_id

from departments

where location_id in (1700,2400));

6. Select employee_id,first_name,salary

from employees

where salary > (select avg(salary)

from employees);

7. Select first_name,last_name,department_id

from employees

where manager_id = (select employee_id

from employees

where first_name='Steven' and last_name='King');

Answers: Chapter – 6

1. Referential Integrity Constraints

2. No

3. No

4. To avoid ambiguity, you will use full qualified column names by prefixing table names to these columns.

5. 4 join conditions

6. Select e.first_name||' '||e.last_name EMPLOYEE,

d.department_name DEPARTMENT

from employees e, departments d

where e.department_id = d.department_id;

7. Select e.first_name||' '||e.last_name EMPLOYEE,

d.department_name DEPARTMENT

from employees e, departments d

where e.department_id = d.department_id and

e.first_name='John';

8. Select e.first_name||' '||e.last_name EMPLOYEE,

d.department_name DEPARTMENT, l.city

from employees e, departments d, locations l

where l.location_id=d.location_id and

e.department_id = d.department_id and

e.commission_pct is not null;

9. Select c.country_id, c.country_name, l.city

from countries c INNER JOIN locations l ON c.country_id=l.country_id(+)

order by c.country_id;

Answers: Chapter – 7

1. Column Name, Data Type, Default Value, and Constraint.

2. Column Name and Data Type.

3. 145.65

4. ‘Salary’, ’10,000’, and ’01-JAN-2015’

5. BLOB

6. NOT NULL

7. Prevent duplicate values.

8. CHECK(gender IN (‘M’,’F’))

9. Create parent before the child.

10.Drop child before the parent.

11.ALTER TABLE {table name} RENAME COLUMN {column name}

12.Both statements are incorrect. The first one should contain a comma between the last column name and the table constraint, as shown below:

Create Table Test_1

(id number(6), name varchar2(30), constraint pk_test_1 primary key(id));

The second one has two errors. First, the comma between the customer_id column and its NOT NULL constraint should be removed. A column constraint should never be separated like this from its corresponding column. Secondly, the table constraint (pk_ord_id) is missing the referenced primary key column i.e. order_id. Here is the correct statement:

Create Table Orders

(

order_id number(6),

customer_id number(6) constraint nn_ord_cust_id NOT NULL,

order_total number(12,2),

dispatched char(1) constraint ck_ord_dispatch CHECK(dispatched IN ('Y','N')),

constraint pk_ord_id primary key(order_id)

);

Answers: Chapter – 8

1. COMMIT or ROLLBACK

2. You must provide a list of column if you are not inserting values into all columns. In this statement you are not populating the LOCATION_ID column, therefore, the statement should be passed like this:

INSERT INTO Departments (department_id,department_name)

VALUES (99,’Manufacturing’);

3. a. The SET clause is missing.

b. Smith should be enclosed in single quotes.

c. Comma is missing between the two columns.

Here is the correct statement:

UPDATE Employees SET first_name=’Smith’,salary=10,000 WHERE employee_id=999;

4. No, it will return an error because the format mask in the TO_DATE function is missing. Here is the correct statement:

DELETE FROM Employees WHERE hire_date > to_date(’01-01-2015’,’DD-MM-YYYY’);

5. The insert statement is:

INSERT INTO Employees (employee_id,last_name,email,hire_date,job_id,department_id)

VALUES (210,'FRANKLIN','FR',TO_DATE('01-01-2015','MM-DD-YYYY'),'HR_REP',270);

6. In step 5, you used department number 270 for the new employee. The foreign key constraint (EMP_DEPT_FK), implemented in the Employees table, would not allow you to delete this department.

7. After deleting the employee’s record the department record was also deleted.

Answers: Chapter – 9

1. Views

2. Indexes

3. Stored Procedures

4. EXECUTE

5. There is no command to fire a trigger. It is fired automatically before or after the associated DML statement.