• No results found

Baze de Date - Oracle

N/A
N/A
Protected

Academic year: 2021

Share "Baze de Date - Oracle"

Copied!
12
0
0

Loading.... (view fulltext now)

Full text

(1)

REZOLVARI SUBIECTE ORACLE – ATESTAT 2008 1.1. select avg(salary)from employees where department_id=80; 1.2. select last_name,first_name from employees where

salary>(select salary from employees where last_name='Mourgos'and first_name='Kevin');

1.3. select last_name, first_name from employees where

department_id=(select department_id from departments where department_name='Sales');

1.4. select e.last_name||' lucreaza pentru ' || m.last_name from employees e, employees m where e.manager_id=m.employee_id; 2.1. select last_name,first_name from employees where

manager_id is null;

2.2. select last_name,first_name from employees where manager_id=(select manager_id from employees where last_name='Mourgos' and first_name='Kevin');

2.3. select e.last_name,e.first_name,d.department_name from employees e, departments d where

e.department_id=d.department_id and commission_pct is not null; d. select d.department_name from employees e, departments d where 2.4.department_id=d.department_id;

3.1. select last_name, first_name, salary,hire_date from employees where department_id=80;

3.2. select last_name, first_name, salary from employees where department_id=(select department_id from employees where last_name='Mourgos' and first_name='Kevin');

3.3. select j.job_title from employees e, jobs j where e.job_id=j.job_id and last_name='Whalen' and

first_name='Jennifer';

3.4. select e.last_name, e.first_name, d.department_name from employees e, departments d where

e.department_id=d.department_id (+);

(2)

as "Producator", year as "Anul aparitiei"

from D_CDs where mod(length(title),2)<>0 order by year desc 4.2. select title,producer from d_cds where year in (select min(year) from d_cds)

4.3. select title,artist from d_songs where id in (select song_id from d_track_listings where track=1)

4.4. select c.title, s.title, s.artist from d_cds c join d_track_listings t on (c.cd_number=t.cd_number)

join d_songs s on (t.song_id=s.id) where c.year =2000

5.1. select loc_type as "Tip locatie",address as "Adresa",comments as "Observatii"

from d_venues where ID between 100 and 999 and address like '%o%'

5.2. select * from d_venues where length(comments)=(select max(length(comments)) from d_venues)

5.3. select count(*) from d_events where id not in(select id from d_venues where loc_type like '%Private%')

5.4. select e.name,e.cost,v.address from d_events e join d_venues v on (e.venue_id=v.id)

where v.loc_type='Private Home'

6.1. select last_name||' '||first_name as "Nume si Prenume",phone as "Numar telefon", email as "Adresa de email" from d_clients where email like '%yahoo%'

6.2. select first_name,last_name from d_partners where id=(select id from d_partners where

partner_type='Manager')

6.3. select name,event_date from d_events

where client_number=(select client_number from d_clients where first_name='Lauren')

6.4. select s.title,s.artist,t.description from d_songs s join d_types t on (s.type_code=t.code)

(3)

7.1. select round(avg(salary)) as “Salariu mediu” from f_staffs where manager_id is not null

7.2. select code,name from f_promotional_menus

where end_date-start_date=(select min(end_date-start_date) from f_promotional_menus)

7.3. select quantity from f_order_lines

where food_item_number in (select food_item_number from f_food_items where regular_code is not null)

7.4. select c.first_name,c.last_name,o.order_date,o.order_total from f_customers c left outer join f_orders o on(c.id=o.cust_id) 8.1. select phone_number,email from employees

where commission_pct is not null and substr(first_name,1,1) IN ('A','E','I','O','U')

8.2. select first_name,last_name from employees

where hire_date=(select max(hire_date) from employees where department_id=50)

8.3. select count(*)as "numar

angajati",trunc(avg(salary*(1+commission_pct)/100),3)as "salariu mediu" from employees

where department_id in (select department_id from departments where department_name='Sales')

8.4. select employee_id,first_name,last_name,sum(end_date-start_date)as "numar zile"

from job_history join employees using(employee_id) group by employee_id,first_name,last_name

9.1. select street_address as "Adresa",postal_code as "Cod postal",city as "Nume oras" from locations

where country_id='US' and city like '%e'

9.2. select count(*) as "numar locatii" from locations

where country_id not in (select country_id from locations where city='Seattle')

9.3. select country_id,country_name from countries where region_id in(select region_id from regions where

(4)

region_name='Europe')

9.4. select department_name,street_address from departments join locations using(location_id)

10.1. select department_id,department_name from departments where

upper(substr(department_name,1,1))=upper(substr(department_na me,-1))

10.2. select first_name,last_name from employees

where commission_pct in(select max(commission_pct) from employees where commission_pct is not null)

10.3. select department_name,department_id from departments where department_id not in(select department_id from employees where department_id is not null)

10.4. select first_name,last_name,salary,department_name from employees join departments using(department_id) where salary between 10000 and 15000

11.1. select lower('Atestat')||' '||upper('Oracle')||' '||

lower(substr('Subiecte',1,7))||upper(substr('Subiecte',8,1)) from dual

11.2. select first_name,last_name,birthdate from f_staffs where manager_id=19 and salary<50

and birthdate between to_date('30-01-1969','DD-MM-YYYY') and to_date('31-12-1979','DD-MM-YYYY')

11.3. select salary,salary*1.03 as "salariu indexat" from f_staffs where first_name='Bob'

12.1. select 'Astazi '||to_char(sysdate,'DD-MON-YY')||' plec la munte!!!' from dual

12.2. select e.first_name||' '||e.last_name||' '||e.hire_date as "Angajat",

m.first_name||' '||m.last_name||' '||m.hire_date as "Manager" from employees e,employees m

(5)

12.3. select department_id,round(avg(salary),3)from employees group by department_id

13.1. select substr('Informatica',2,3) from dual

13.2. select artist,title,duration from d_songs where title like '%r' 13.3. select s.artist,s.title,p.comments from d_songs

s,d_play_list_items p

where s.id=p.song_id order by s.artist

14.1. select replace('Astazi sustinem atestatul profesional',' ','*') from dual

14.2. select first_name,last_name from d_clients where client_number

in(select client_number from d_events where

substr(to_char(event_date,'DD-MM-YYYY'),4,2)='04')

14.3. select first_name,last_name,phone from d_clients where email like'%yahoo.com'

15.1. select replace ('afara ploua','a','') from dual

15.2. select * from f_promotional_menus order by start_date desc 15.3. - insert into f_customers

values(1,'Prenume','Nume','Adresa','Oras','Tara','1','1') - select * from f_customers

16.1. select first_name,last_name from employees where mod(salary,3)=0

16.2. select first_name,last_name,trunc(salary*1.0420,2)as"Salariu indexat" from employees

where department_id=80

16.3. select first_name,last_name,salary from employees

where department_id in(select department_id from departments where location_id=1700)

17.1. select department_id from employees group by department_id

(6)

department_id=60)

17.2. - insert into employees

values(90,'Alin','Popescu','oracle',0744123456,to_date('30-01-2008','DD-MM-YYYY'),'AD_VP',12000,null,101,90)

- select * from employees 17.3. select

e.first_name,e.last_name,d.department_id,d.department_name from employees e left outer join departments d on

(e.department_id=d.department_id)

18.1. select j.job_title,h.start_date,h.end_date from jobs j,job_history h

where j.job_id=h.job_id and min_salary between 2000 and 10000 18.2. select count(job_id) from jobs

where (min_salary between 3000 and 20000) and (max_salary between 3000 and 200000)

18.3. select department_id,max(end_date-start_date) from job_history group by department_id

19.1. select artist,title from d_songs where artist like '%o%'

19.2. select title from d_songs where type_code in (12,77) order by title

19.3. select artist,title,duration,description from d_songs,d_types where type_code=code

20.1. select first_name,last_name,salary*1.25 from employees where department_id in(50,90,110) and hire_date>to_date('07-06-1994','DD-MM-YYYY')

20.2. select first_name,salary*1.33 from employees where department_id=50 and first_name like '%e%'

20.3. select first_name,last_name from employees where salary=(select max(salary) from employees)

21.1. select 'Mai sunt aproximativ '||

trunc(months_between(to_date('25-12-2008','DD-MM-YYYY'),sysdate))||' luni pana la Craciun' from dual

(7)

21.2. - create table copy_employees as select * from employees - select first_name,last_name,salary,department_id from copy_employees

where salary between 10000 and 20000

21.3. select first_name,last_name,salary*1.1 from copy_employees order by 3

21.4. - alter table copy_employees add adresa varchar2(50) - alter table copy_employees drop column hire_date

- alter table copy_employees drop column commission_pct - select * from copy_employees order by last_name

22.1. select 'Peste 10 luni vom fi in data de '|| add_months(sysdate,10) from dual

22.2. - create table copy_d_cds as select * from d_cds

- select title,producer from copy_d_cds where year>=2000 22.3. - alter table copy_d_cds rename column cd_number to nr_cd - alter table copy_d_cds rename column title to titlu

- alter table copy_d_cds rename column producer to producator

- alter table copy_d_cds rename column year to an - select * from copy_d_cds order by an

22.4. - insert into copy_d_cds values(99,'Morandi Nrxt','Universal Music',2006)

- select * from copy_d_cds order by title 23.1. select concat('Prima zi a lunii urmatoare este ',last_day(sysdate)+1) from dual

23.2. create table copy1_employees as select first_name,last_name,hire_date from employees

select * from copy1_employees order by last_name

23.3. select first_name from copy1_employees where first_name like '%a%'

23.4. select min(salary),max(salary),trunc(avg(salary),3) from employees

(8)

24.1. select concat('In acest an luna februarie are

',last_day(to_date('01-02-2008','DD-MM-YYYY'))+1-to_date('01-02-2008','DD-MM-YYYY'))||' zile' from dual

24.2. - create table copy_d_songs as select * from d_songs - select * from copy_d_songs order by artist

24.3. select title from copy_d_songs where duration>'5'

24.4. - insert into copy_d_songs values(51,'Right Here Waiting','6 min','Richard Marx',60)

- select id,title,duration,artist,type_code+1 from copy_d_songs

25.1. select 'Azi este a '||trunc(sysdate+1-to_date('01-01-2008','DD-MM-YYYY'))||' -a zi a anului' from dual

25.2. - create table copy_d_events as select * from d_events - select * from copy_d_events order by event_date 25.3. - insert into copy_d_events

values(1,'Nume',sysdate,'Descriere',1,1,1,1,1) - insert into copy_d_events

values(2,'Nume1',sysdate+1,'Descriere1',1,1,1,1,1)

- select name,description,cost*1.19 from copy_d_events 25.4. - alter table copy_d_events drop column package_code - alter table copy_d_events drop column theme_code - select * from copy_d_events

26. select upper('Examen de atestat la informatica') from dual 27. select 'Textul "Examen de atestat la informatica" are '||

length('Examen de atestat la informatica')||' caractere' from dual 28. select replace('E bine bine e foarte bine','bine','rau') from dual 29. select replace(lower('Doua rate fac doua oua in doua

zile'),'doua','') from dual

30. select rpad('Examen',15,'+')||' si '||lpad('Informatica',20,'-') from dual

(9)

31.1. select concat('azi suntem in ',to_char(sysdate,'DD month YYYY')) from dual

31.2. select last_name,department_id from employees where department_id in (20,50) order by last_name

31.3. select first_name,last_name from employees where job_id=(select job_id from jobs where job_title='Programmer')

31.4. select first_name,last_name,department_name from employees e,departments d

where e.department_id=d.department_id(+) 32.1. select next_day(sysdate,'friday') from dual

32.2. select first_name,last_name,salary*12 as "Yearly Salary" from employees

order by last_name,first_name

32.3. select first_name,last_name from employees

where employee_id in(select manager_id from departments) 32.4. select last_name,department_name from employees

e,departments d

where e.department_id(+)=d.department_id 33.1. select replace('Mississippi','i','&') from dual

33.2. select first_name,last_name,salary*1.1 from employees where last_name like '%s' order by salary desc

33.3. select title,year from d_cds

where year in((select max(year)from d_cds),(select min(year) from d_cds))

33.4. select e.first_name,e.last_name,j.job_id from employees e join job_history j on e.employee_id=j.employee_id

34.1. select next_day(add_months(sysdate,6),'friday') from dual 34.2. select first_name,last_name,salary from employees

(10)

34.3. select title,duration from d_songs where to_number(substr(duration,1,2))<=5

34.4. select last_name,salary,grade_level from employees natural join job_grades

where salary between lowest_sal and highest_sal 35.1. select upper(replace('It is a small world','It is a small','Wonderful')) from dual

35.2. select title,producer from d_cds where title like '%a%' 35.3. select first_name,last_name from employees where commission_pct is null

35.4. select name,cost,code from d_packages natural join d_events where cost between low_range and high_range

36.1. select replace('caracatita','ca','ta') from dual

36.2. select last_name,trunc(months_between(sysdate,hire_date)) from employees

36.3. select department_id,trunc(avg(salary),2) from employees where department_id is not null group by department_id 36.4. select song_id,event_id,cd_number from d_play_list_items join d_track_listings using(song_id)

37.1. select instr('caracatita','c',-1,1) from dual 37.2. select

first_name,last_name,salary,salary+salary*commission_pct from employees

where commission_pct is not null

37.3. select first_name,last_name,birthdate from f_staffs where birthdate>(select birthdate from f_staffs where lower(first_name)='monique')

37.4. select first_name,last_name,department_name from employees natural join departments

38.1. select next_day(to_date('01-01-2008','DD-MM-YYYY'),'friday') from dual

(11)

first_name,last_name,substr(to_char(hire_date,'DD-MM-YYYY'),4,2)as "luna angajarii" from employees

38.3. select first_name,last_name from employees

where department_id in (select department_id from departments where lower(last_name)='king')

38.4. select e.first_name||' '||e.last_name as angajat,m.first_name||' '||m.last_name manager,

d.department_name from employees e join employees m on e.manager_id=m.employee_id

join departments d on m.department_id=d.department_id

39.1. select to_char(sysdate,'"ziua" DD,"luna" MM,"anul "YYYY') from dual

39.2. select

first_name,last_name,expertise,nvl(specialty,0),auth_expense_amt, manager_id,partner_type

from d_partners

39.3. select first_name,last_name from employees where department_id=

(select department_id from departments where lower(department_name)='marketing')

39.4. select

e.first_name,e.last_name,d.department_id,d.department_name from employees e full outer join departments d on

e.department_id=d.department_id

40.1. select to_date('mai 15, 2008','month DD, YYYY')from dual 40.2. - create table copy_d_clients as select * from d_clients - update copy_d_clients set

phone=substr(phone,4,length(phone)) - select * from copy_d_clients

40.3. select first_name,last_name,salary from employees where salary>(select avg(salary) from employees) 40.4. select

(12)

from employees e,departments d where e.department_id=d.department_id(+)

References

Related documents

The financial statement volatility associated with variable annuity blocks has led several life insurance companies to discontinue new sales of variable annuity products and

The aims of such a system are to provide more scalability of MMOGs based on hybrid P2P system, dynamic AoIM and dynamic load balancing taking into consideration

Following the drawing-in of the pipe string or with short pipe lining the remaining annular space must be plugged with a suitable free-flowing special mortar, in particular with

Bill, I don't know what kind of alife vou'llhavc with this catamount.. - thicker than porkerpine

If you're stuck on playing it on a guitar, I recommend capoing on the 12th fret and playing the following (you could play this without the capo--the riff would just be an

Bias establishes the operating point ( Q-point ) of a transistor amplifier; the ac signal.. moves above

Purdue University, Krannert School of Management, Strategic Management Seminar (2005) SMU, Cox School of Business, Strategy and Entrepreneurship Seminar (2004). Tilburg

The Fair Work Commission assists in resolving disputes involving employers, employees, unions and employer associations who are covered by the national workplace relations