· 4 years ago · Mar 31, 2021, 09:14 PM
1-- If database NOT exists create appdatabase
2IF NOT EXISTS(SELECT * FROM sys.databases WHERE name = 'appdatabase')
3 BEGIN
4 CREATE DATABASE [appdatabase]
5 COLLATE Croatian_CI_AS;
6 END
7 GO
8 USE [appdatabase]
9 GO
10-- If table NOT exists create table
11IF OBJECT_ID('employee', 'U') IS NULL
12BEGIN
13 CREATE TABLE dbo.employee
14 (
15 ID BIGINT NOT NULL UNIQUE,
16 name VARCHAR(100) NOT NULL,
17 surname VARCHAR(100) NOT NULL,
18 birthdate DATE,
19 employeesince DATE,
20 description VARCHAR(300)
21 CONSTRAINT PK_employee PRIMARY KEY (name, surname)
22 );
23 INSERT INTO dbo.employee (ID, name, surname, birthdate, employeesince, description) VALUES
24 ('10123456789123', 'Jonny', 'Jones', '1982-11-30', '2001-09-11', 'Office worker since'),
25 ('10021100110013', 'Leonardo', 'D Vincy', '1980-12-03', '2012-03-01', 'Good and hard working employee'),
26 ('15503300330033', 'Daniel', 'Viesta', '1990-11-01', '1998-07-21', 'Works since companny started'),
27 ('10203982102211', 'Leonardo', 'Turtle', '1978-03-07', '1999-07-12', 'Working all day long'),
28 ('11112982102110', 'Marine', 'Sunshine', '1991-07-15', '2002-02-16', 'Nothing to write here')
29
30END;
31IF OBJECT_ID('userlogin', 'U') IS NULL
32BEGIN
33 CREATE TABLE dbo.userlogin
34 (
35 ID BIGINT NOT NULL UNIQUE,
36 username VARCHAR(30) NOT NULL UNIQUE,
37 password VARCHAR(30) NOT NULL,
38 name VARCHAR(100) NOT NULL,
39 surname VARCHAR(100) NOT NULL,
40 flag CHAR(2) NOT NULL,
41 department VARCHAR(20) NOT NULL,
42 CONSTRAINT PK_userlogin PRIMARY KEY (name, surname),
43 CONSTRAINT FK_userlogin FOREIGN KEY (ID)
44 REFERENCES dbo.employee (ID)
45 ON DELETE CASCADE
46 ON UPDATE CASCADE
47
48 );
49 INSERT INTO dbo.userlogin (ID, username, password, name, surname, flag, department) VALUES
50 ('10123456789123', 'jonny', 'jonnypass', 'Jonny', 'Jones', 'A', 'Office'),
51 ('10021100110013', 'leonardo', 'leo123', 'Leonardo', 'D Vincy', 'U', 'Office'),
52 ('15503300330033', 'dviesta', '13522', 'Daniel', 'Viesta', 'U', 'Outourcing'),
53 ('10203982102211', 'leonardo1', 'leo898', 'Leonardo', 'Turtle', 'A', 'Main office'),
54 ('11112982102110', 'octopus', 'octopuspass', 'Marine', 'Sunshine', 'A', 'General building')
55END