VVuCUcRP

1CREATE DATABASE IF NOT EXISTS lesson_5;
2
3USE lesson_5;
4
5CREATE TABLE users (
6username VARCHAR(50) PRIMARY KEY,
7password VARCHAR(50) NOT NULL,
8status VARCHAR(10) NOT NULL);
9
10CREATE TABLE users_profile (
11username VARCHAR(50) PRIMARY KEY,
12name VARCHAR(50) NOT NULL,
13address VARCHAR(50) NOT NULL,
14email VARCHAR(50) NOT NULL,
15FOREIGN KEY (username) REFERENCES users(username) ON DELETE CASCADE);
16
17INSERT INTO users values
18('admin' , '7856', 'Active'),
19('staff' , '90802', 'Active'),
20('manager' , '35462', 'Inactive');
21
22INSERT INTO users_profile values
23('admin', 'Administrator' , 'Dhanmondi', 'admin@test.com' ) ,
24('staff', 'Jakir Nayek' , 'Mirpur', 'zakir@test.com' ),
25('manager', 'Mehr Afroz' , 'Eskaton', 'mehr@test.com' );
26
27CREATE TABLE banks (
28TB VARCHAR(5),
29ID_CLIENT INT,
30ID_DOG INT,
31OSZ INT,
32PROCENT_RATE INT,
33RATING INT,
34SEGMENT VARCHAR(20));
35
36
37INSERT INTO banks
38VALUES
39('A', 1, 111, 100, 6, 10, 'SREDN'),
40('A', 1, 222, 150, 6, 10, 'SREDN'),
41('B', 2, 333, 50, 9, 15, 'MMB'),
42('B', 1, 444, 200, 7, 10, 'SREDN'),
43('B', 3, 555, 1000, 5, 16, 'CIB'),
44('B', 4, 666, 500, 10, 20, 'CIB'),
45('B', 4, 777, 10, 12, 17, 'MMB'),
46('C', 5, 888, 20, 11, 21, 'MMB'),
47('C', 5, 999, 200, 9, 13, 'SREDN');
48
49CREATE TABLE revisions (
50TB VARCHAR(5),
51DEP VARCHAR(10),
52Count_Revisions INT);
53
54INSERT INTO revisions
55VALUES
56('A', 'Corp', 100),
57('A', 'Rozn', 47),
58('A', 'IT', 86),
59('B', 'Corp', 70),
60('B', 'Rozn', 65),
61('B', 'IT', 58),
62('C', 'Corp', 42),
63('C', 'Rozn', 40),
64('C', 'IT', 63),
65('D', 'Corp', 95),
66('D', 'Rozn', 120),
67('D', 'IT', 85),
68('E', 'Corp', 70),
69('E', 'Rozn', 72),
70('E', 'IT', 80),
71('F', 'Corp', 66),
72('F', 'Rozn', 111),
73('F', 'IT', 33);