CREATE TABLE STUDENT
       (ID_PRAC NUMERIC(8) IDENTITY(1,1) CONSTRAINT PK_STUD PRIMARY KEY,
	NAZWISKO CHARACTER VARYING(15),
	IMIE CHARACTER VARYING(15),
	ETAT CHARACTER VARYING(15) CONSTRAINT FK_ETAT_S REFERENCES ETATY(NAZWA),
	ID_SZEFA NUMERIC(8) CONSTRAINT FK_ID_SZEFA_S REFERENCES STUDENT(ID_PRAC), 
	ZATRUDNIONY DATE,
	PLACA_POD NUMERIC(8,2),
	PLACA_DOD NUMERIC(8,2),
	ID_ZESP NUMERIC(2) CONSTRAINT FK_ID_ZESP_S REFERENCES ZESPOLY(ID_ZESP),
	numer_indeksu CHARACTER VARYING(10));

USE [infSem3]
GO

INSERT INTO [dbo].[STUDENT]
           ([ID_PRAC]
           ,[NAZWISKO]
           ,[IMIE]
           ,[ETAT]
           ,[ID_SZEFA]
           ,[ZATRUDNIONY]
           ,[PLACA_POD]
           ,[PLACA_DOD]
           ,[ID_ZESP]
           ,[numer_indeksu])
     VALUES
           (2
           ,'KACZMAREK'
           ,'PATRYK'
           ,'DYREKTOR'
           ,NULL
           ,'2024-01-20'
           ,4999.99
           ,1999.99
           ,NULL
           ,'INFXXXXX')
GO


CREATE SEQUENCE student_seq
    START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;

CREATE TABLE STUDENT
    (id_prac numeric(8,0) NOT NULL DEFAULT nextval('student_seq'::regclass),
	NAZWISKO CHARACTER VARYING(15),
	IMIE CHARACTER VARYING(15),
	ETAT CHARACTER VARYING(15) CONSTRAINT FK_ETAT_S REFERENCES ETATY(NAZWA),
	ID_SZEFA NUMERIC(8),
    CONSTRAINT fk_id_szefa FOREIGN KEY (id_szefa) REFERENCES public.pracownicy (id_prac),
	ZATRUDNIONY DATE,
	PLACA_POD NUMERIC(8,2),
	PLACA_DOD NUMERIC(8,2),
	ID_ZESP NUMERIC(2) CONSTRAINT FK_ID_ZESP_S REFERENCES ZESPOLY(ID_ZESP),
	numer_indeksu CHARACTER VARYING(10));