29.05.2017: andmebaasirakenduste arendaja x3

2.1
1. select last_name, address from f_customers

2.

• select first_name from f_staffs;
• SELECT first_name||' '||last_name AS "DJs on Demand Clients" FROM d_clients;
•  
• SELECT order_number FROM f_orders;

3. SELECT '***'||first_name||'***'||first_name||'***' as "Super Star" FROM f_staffs WHERE first_name = 'Sue' or first_name = 'Bob' or first_name = 'Monique';

4. d

5. select last_name as "Employee last name", salary as "Current salary" ,salary*1.05 as "Salary with 5% raise" from f_staffs; 

6. desc employees, nullable = väärtus võib olla NULL

7. ma veel mäletan

8. true

9. false

10. SELECT employee_id, last_name sal x 12 ANNUAL SALARY FROM employees;
      vead: tulp sal ees puudub koma; korrutamine on x-iga; aliase eest puudub as keyword;  alias pole         jutumärkides;

11. korrutamine enne

12. d

13. a

14. c

15. c

16. c

17. c

18. d

19. d

20. oskan

2.2 

1. select first_name, last_name, address from f_customers where id = 456;

2.  select name, start_date, end_date from F_PROMOTIONAL_MENUS where code = 100;

3.

4. SELECT producer, title FROM d_cds WHERE title = 'carpe diem';

5. select title, year from d_cds where year < 2000;

6. a. ja b.

7. select studentno, fname, lname from students where sex = 'F';

8. select studentno as "Student Number" from students where major = 'PE';

9. select * from students where sex = 'M';

10. select title, year from d_cds where year != 2000;

11. select * from f_staffs where extract(year from birthdate) < 1980;

2.3

1. select * from f_staffs where salary >= 5 and salary <= 10;

2. select loc_type, comments from d_venues where loc_type = 'Private Home';

3. SELECT first_name, last_name FROM f_staffs WHERE salary >= 20.00 and salary <= 60.00;

4. select * from d_cds where substr(title, 2, 1) = 'a';

5. select * from d_partners where auth_expense_amt is null;

6. select * from employees where substr(last_name, -1, 1) = 's';

7. c

8. select * from d_songs where type_code = 77 or type_code = 12 or type_code = 1;

4.1

1. select 'Oracle '||'Internet '||'Academy' as "The Best Class" from dual;

2. select substr('Oracle Internet Academy', 13, 3) as "The Net" from dual;

3. select length('Oracle Internet Academy') as "length" from dual; (23)

4. select instr('Oracle Internet Academy', 'I', 1) as "location" from dual;

5. select rpad(lpad(replace('Oracle Internet Academy', ' ', '****'), length(replace('Oracle Internet Academy', ' ', '****'))+4, '*'), length('Oracle Internet Academy')+14, '*') from dual;

6. oskan

7. select replace('Oracle Internet Academy', 'Internet', '2013-2014') as "The Best Class" from dual;

8. select order_date||'$'||order_total as Total from f_orders

9. select upper(first_name||' '||last_name||' '||address||' '||city||', '||state||' '||zip) from f_orders

10. select substr(first_name,1,1)||last_name as "Name", salary, department_id from employees where department_id = 20;

select substr(first_name,1,1)||last_name as "Name", salary, department_id, :20  from employees where department_id = :20;

11.

12.

4.2

1. select last_name, salary, round(salary/1.55, 2) from employees where employee_id between 100 and 102;

2. select last_name, trunc(salary*1.05333,2) from employees where department_id = 80;

3. select mod(38873,2) from dual;

4. select round(845.553,1) from dual;
select round(30695.348,2) from dual;
select round(30695.348,-2) from dual;
select trunc(2.3454,1) from dual;

5. select first_name, last_name, salary, salary/3 from employees where mod(salary,3) = 0;

6. select mod(34,8) as "EXAMPLE" from dual;

7. 1000000*0.004 = sitaks ja peale

4.3

1. select name, event_date, round(months_between(sysdate, event_date)) from d_events where name = 'Vigil wedding';

6. select round(months_between(sysdate, birthdate)/12) as "Bob Miller's age" from f_staffs where first_name = 'Bob' and last_name = 'Miller';

7. select add_months(sysdate, 6) as "Appointment" from dual;

11. select add_months(sysdate, 120) as "Future" from dual;

12. sysdate