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
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';
7. select add_months(sysdate, 6) as "Appointment" from dual;
11. select add_months(sysdate, 120) as "Future" from dual;
12. sysdate
