lab 8

DROP TABLE EXAMS
-----------------------------task1-2--------------------------
CREATE TABLE exams
(   exam_id INT IDENTITY(1,1)  PRIMARY KEY,
    subject_id INT  FOREIGN KEY REFERENCES  subjects(subject_id),
    tutor_id INT  FOREIGN KEY REFERENCES  tutors(tutor_id),
    student_id  INT  FOREIGN KEY REFERENCES  students(student_id),
    exam_date DATE,
    place_id INT  FOREIGN KEY REFERENCES  places(place_id)


    --CONSTRAINT exam_id PRIMARY KEY(exam_id)
    ON DELETE SET NULL
    ON UPDATE CASCADE
);
----task3----
ALTER TABLE places
ADD CONSTRAINT UC_places UNIQUE (building ,audience)
----------task4-----------

ALTER TABLE students
ADD CONSTRAINT zip CHECK (zip_code BETWEEN 10000 AND 9999)

 -----task5-6 -----------
 -----------task 7-----------

 CREATE TABLE new_places
 (place_id INT IDENTITY(1,1),
 building VARCHAR(10) NOT NULL,
 audience VARCHAR(10) NOT NULL
 );

INSERT INTO new_places(building,audience) VALUES ('EEIA', '121');
INSERT INTO new_places(building,audience) VALUES ('EEIA', '123');
INSERT INTO new_places(building,audience) VALUES ('FTIMS', '122');
INSERT INTO new_places(building,audience) VALUES ('FTIMS', '124');
INSERT INTO new_places(building,audience) VALUES ('EEIA', '114');

-----------------task8 ----------------------


UPDATE students SET name = 'john'
WHERE student_id BETWEEN 1 AND 4

------------task9  -------------
UPDATE students SET last_name= 'lenon' WHERE name = 'john' AND student_id BETWEEN 1 AND 3

--------task 10 --------------

UPDATE tutors SET name =  'huck finn'
WHERE tutor_id IN (SELECT tutor_id FROM exams WHERE exams.exam_date = '06-24-2010')

----task 11--------------
UPDATE students
SET name =
(
CASE
WHEN name ='james ' AND last_name = 'bond'

THEN 'donald '
ELSE  'micki'
END
)
-----task 12-----

DELETE FROM students
WHERE name = 'donald'
AND last_name = 'duck'


-----------task 13-------
TRUNCATE TABLE new_places