7/25/2019 Exercitii Recapitulative Exercitii recapitulative Test

http://slidepdf.com/reader/full/exercitii-recapitulative-exercitii-recapitulative-test 1/7

CREATE TABLE frme2

(codfrma NUMBER(2) CONSTRAINT pk_frme PRIMARY KEY

de!frma "ARC#AR2(2$) CONSTRAINT de!_frma NOT NULL

%oca%&'a'e "ARC#AR2(2$)o!a "ARC#AR2(*) CONSTRAINT ck_o!a C#ECK (o!a IN (+Mo%do,a+

+Ardea%++Ba!a'++M-!'e!&a++.o/ro0ea+)))1

CREATE TABLE a0e!'&2(

coda0e!' "ARC#AR2() CONSTRAINT pk_a0e!' PRIMARY KEY

!-mea0e!' "ARC#AR2(2*)CONSTRAINT !_a0e!' NOT NULL

da'aa!0 .ATE .E3AULT SYS.ATE

da'a!a4'er&& .ATE NOT NULL

5-!c'&a "ARC#AR2($) NOT NULL

codfrma NUMBER(2) CONSTRAINT 5k_a0e!'& RE3ERENCES frme2(codfrma))1

.ROP TABLE a0e!'&2 CASCA.E CONSTRAINTS PUR6E1

CREATE TABLE 5o4'&_a0e!'&

AS

SELECT coda0e!' !-mea0e!' 5-!c'&a codfrma 3ROM a0e!'&21

SELECT 7 3ROM 5o4'&_a0e!'&1

ALTER TABLE a0e!'&2 RENAME TO per4o!a%1

RENAME per4o!a%2 TO per4o!a%1

ALTER TABLE per4o!a%

A.. ( ema&% "ARC#AR(2$) ,ar4'a NUMBER(2))1

SELECT 7 3ROM per4o!a%1

ALTER TABLE per4o!a%

MO.I3Y (ema&% "ARC#AR2($))1

7/25/2019 Exercitii Recapitulative Exercitii recapitulative Test

http://slidepdf.com/reader/full/exercitii-recapitulative-exercitii-recapitulative-test 2/7

ALTER TABLE per4o!a%

.ROP COLUMN ema&%1

ALTER TABLE per4o!a%SET UNUSE. COLUMN 5-!c'&a1

ALTER TABLE per4o!a%

.ROP UNUSE. COLUMNS1

ALTER TABLE per4o!a%

A.. CONSTRAINT c8eck_,ar4'a C#ECK (,ar4'a9: a!d ,ar4'a;*$)1

ALTER TABLE per4o!a%

.ISABLE CONSTRAINT c8eck_,ar4'a1

ALTER TABLE per4o!a%

ENABLE CONSTRAINT c8eck_,ar4'a1

ALTER TABLE per4o!a%

.ROP CONSTRAINT c8eck_,ar4'a1

.ROP TABLE per4o!a% CASCA.E CONSTRAINTS1

3LAS#BACK TABLE per4o!a% TO BE3ORE .ROP1

 TRUNCATE TABLE per4o!a%1

<<"I=UALI=AREA TUTUROR TABELELOR UTILI=ATORULUI CURENT>

SELECT 7 3ROM USER_CONSTRAINTS ?#ERE TABLE_NAME@+per4o!a%+1

.ROP TABLE per4o!a% CASCA.E CONSTRAINTS1

.ROP TABLE frme2 CASCA.E CONSTRAINTS1

7/25/2019 Exercitii Recapitulative Exercitii recapitulative Test

http://slidepdf.com/reader/full/exercitii-recapitulative-exercitii-recapitulative-test 3/7

3LAS#BACK TABLE per4o!a% TO BE3ORE .ROP1

ALTER TABLE per4o!a%.ROP COLUMN ,ar4'a1

SELECT 7 3ROM per4o!a%1

CREATE TABLE 4a%ar&a'& AS SELECT 7 3ROM a!0aa'& ?#ERE 2@1

INSERT INTO 4a%ar&a'& (&d_a!0aa' !-me 4a%ar&-%) "ALUES(2$ +&ONESCU+ $$$)1

SELECT 7 3ROM SALARIATI1

INSERT INTO 4a%ar&a'& SELECT 7 3ROM a!0aa'&

?#ERE &d_depar'ame!' IN (2$$*$)1

SELECT 7 3ROM 4a%ar&a'&1

COMMIT1

<<.ATE INTRO.USE .E LA TASTATURA>

INSERT INTO 4a%ar&a'& (&d_a!0aa' !-me da'a_a!0aare 4a%ar&-%)

"ALUES (+D&d_a!0aa'+ +D!-me+ TO_.ATE (+Dda'a_a!0aare++mo! dd +)

+D4a%ar&-%+)1

UP.ATE 4a%ar&a'&

SET 4a%ar&-% @ 4a%ar&-% F$$

?#ERE 4a%ar&-% ; $$$ AN. com&4&o! IS NULL1

7/25/2019 Exercitii Recapitulative Exercitii recapitulative Test

http://slidepdf.com/reader/full/exercitii-recapitulative-exercitii-recapitulative-test 4/7

UP.ATE 4a%ar&a'&

SET (4a%ar&-% com&4&o!) @ (SELECT 4a%ar&-% com&4&o! 3ROM a!0aa'& ?#ERE

&d_a!0aa'@G)

?#ERE 4a%ar&-%;(SELECT 4a%ar&-% 3ROM a!0aa'& ?#ERE &d_a!0aa'@) AN.&d_depar'ame!'@*$1

.ELETE 3ROM 4a%ar&a'&

?#ERE &d_ma!a0er IN (222)1

.ELETE 3ROM 4a%ar&a'&

?#ERE da'a_a!0aare;TO_.ATE(+$<$<HHH++..<MM<YYYY+)1

.ELETE 3ROM SALARIATI1

ROLLBACK1

SELECT 7 3ROM SALARIATI1

 TRUNCATE TABLE 4a%ar&a'&1

SELECT 7 3ROM SALARIATI1

ROLLBACK1

.ELETE 3ROM SALARIATI1

SELECT 7 3ROM SALARIATI1

ROLLBACK1

.ROP TABLE SALARIATI1

CREATE TABLE 4a%ar&a'& AS SELECT 7 3ROM a!0aa'& ?#ERE 2@1

INSERT INTO 4a%ar&a'& SELECT 7 3ROM a!0aa'& ?#ERE &d_depar'ame!'

IN (2$$*$)1

MER6E INTO 4a%ar&a'& USIN6 a!0aa'&

ON(4a%ar&a'&&d_a!0aa'@a!0aa'&&d_a!0aa')

7/25/2019 Exercitii Recapitulative Exercitii recapitulative Test

http://slidepdf.com/reader/full/exercitii-recapitulative-exercitii-recapitulative-test 5/7

?#EN MATC#E. T#EN

UP.ATE SET 4a%ar&a'&4a%ar&-%@a!0aa'&4a%ar&-%

?#EN NOT MATC#E. T#EN

INSERT (&d_a!0aa' !-me 4a%ar&-%) "ALUES (a!0aa'&&d_a!0aa' a!0aa'&!-mea!0aa'&4a%ar&-%)1

SELECT COUNT (7) 3ROM 4a%ar&a'&1

SELECT COUNT (7) 3ROM 4a%ar&a'&1

COMMIT1

.ROP TABLE frme2 CASCA.E CONSTRAINTS1

SELECT 7 3ROM 4a%ar&a'&1

CREATE TABLE .ep(

I. NUMBER() CONSTRAINT pk_&d PRIMARY KEY

.e!-m&re "ARC#AR2(2*))1

INSERT INTO .ep SELECT &d_depar'ame!' de!-m&re_depar'ame!'

3ROM depar'ame!'e1

SELECT 7 3ROM .epar'ame!'e1

ALTER TABLE depar'ame!'e

A..( ,ar4'a NUMBER(2))1

ro%%/ack1

4e%ec' 7 5rom dep1

CREATE TABLE a!0(

I. NUMBER() CONSTRAINT pk_&da!0 PRIMARY KEY

pre!-me "ARC#AR2(2*)

!-me "ARC#AR2(2*)

7/25/2019 Exercitii Recapitulative Exercitii recapitulative Test

http://slidepdf.com/reader/full/exercitii-recapitulative-exercitii-recapitulative-test 6/7

dep_&d NUMBER() CONSTRAINT 5k_dep RE3ERENCES dep(&d)

)1

ALTER TABLE a!0A.. (,ar4'a NUMBER(2))1

4e%ec' 7 5rom AN61

ALTER TABLE a!0

A.. co!4'ra&!' ck_,ar4'a C#ECK (,ar4'a9: a!d ,ar4'a;G*)1

ro%%/ack1

ALTER TABLE a!0

A.. CONSTRAINT ,er&fca_,ar4'a C#ECK (,ar4'a9: a!d ,ar4'a;G*)1

ALTER TABLE a!0

.ISABLE CONSTRAINT "ERI3ICA_"ARSTA1

ALTER TABLE a!0

MO.I3Y NUME "ARC#AR2($)1

RENAME AN6 TO AN621

.ROP TABLE SALARIATI CASCA.E CONSTRAINTS1

CREATE TABLE SALARIATI AS SELECT 7 3ROM AN6AJATI1

SELECT 7 3ROM SALARIATI1

INSERT INTO 4a%ar&a'& (&d_a!0aa' !-me da'a_a!0aare) "ALUES

(2$ +&ONESCU+ TO_.ATE(+<$G<H:++..<MM<YYYY+))1

SELECT 7 3ROM SALARIATI1

7/25/2019 Exercitii Recapitulative Exercitii recapitulative Test

http://slidepdf.com/reader/full/exercitii-recapitulative-exercitii-recapitulative-test 7/7

UP.ATE 4a%ar&a'&

SET pre!-me @ +Jo8!+ ?#ERE &d_a!0aa' @ H:1

UP.ATE 4a%ar&a'&SET ema&% @ +J#AAN+ ?#ERE &d_a!0aa' @ H:1

SELECT 7 3ROM SALARIATI1

UP.ATE 4a%ar&a'&

SET SALARIUL@$7SALARIULFSALARIUL ?#ERE SALARIUL;2$$$$1

UP.ATE 4a%ar&a'&SET &d_5-!c'&e@+A._PRES+ 8ere &d_a!0aa'@HH1

SELECT 7 3ROM SALARIATI1

UP.ATE 4a%ar&a'&

SET com&4&o!@(SELECT com&4&o! 3rom a!0aa'& 8ere &d_a!0aa'@*)

8ere &d_a!0aa'@G1

SELECT 7 3ROM SALARIATI1

.ELETE 3ROM SALARIATI ?#ERE I._AN6AJAT@H:1