3CaEqqp8

1--1 Connect to database UNIVERSITY
2-- Done
3
4--2 Create new tables: teams1 and emp1 meeting the following conditions:
5
6/*ï‚· table teams1 should consist of the following columns:
7name char(15)
8teamid smallint
9manid smallint,
10teamid is the primary key of the table team1.*/
11
12/*create table teams1(
13teamid smallint,
14name char(15),
15manid smallint,
16primary key (teamid)
17)*/
18
19/*ï‚· table emp1 should consist of the following columns:
20empid smallint
21gender char(1)
22birthdate timestamp
23name char(15)
24teamid smallint
25empid is the primary key of the table emp1. Additionally, columns: name and birthdate should
26not have null values.
27Index should be created on the column teamid.*/
28
29/*create table emp1(
30empid smallint,
31gender char(1),
32birthdate timestamp not null,
33name char(15) not null,
34teamid smallint,
35primary key (empid)
36)*/
37
38--create index teamid on emp1(teamid)
39
40--3 Inserting data to the tables:
41--ï‚· Insert all rows from table TEAMS into table teams1, using insert command.
42
43/*insert into teams1(teamid, name, manid) (
44	select t.TEAM_ID, t.TEAM_NAME, t.MANAGER_ID
45	from teams t
46)*/
47
48--ï‚· Insert all rows from table EMPLOYEES to table emp1 using insert command
49
50/*insert into emp1(empid, gender, birthdate, name, teamid)(
51	select e.EMPLOYEE_ID, e.GENDER, e.DATE_OF_BIRTH, e.EMP_NAME, e.TEAM_ID 
52	from employees e
53)*/
54
55--4. Modify tables as follows (mind the proper order):
56/*ï‚· In table team1 exists foreign key manid, which references to primary key in emp1 table. Define
57the necessary constraints that deny deletion of the primary key in emp1 table, if dependent rows
58are located in team1 table.*/
59
60/*alter table teams1 
61add foreign key (manid) 
62references emp1(empid) 
63on delete restrict*/
64
65/*ï‚· In table emp1 exists foreign key teamid, which references to primary key in team1 table.
66Define the necessary constraints that for any delete of the primary key from team1 table,
67matching values in the foreign key of emp1 table are set to null. */
68
69/*alter table emp1 
70add foreign key (teamid) 
71references teams1(teamid) 
72on delete set null*/