-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathDatabase.sql
More file actions
49 lines (49 loc) · 11.8 KB
/
Database.sql
File metadata and controls
49 lines (49 loc) · 11.8 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
select* from rest_api.user;
UPDATE `rest_api`.`user` SET `username` = 'heather', `address` = 'New York' WHERE (`id` = '9');
drop table user;
CREATE TABLE `user` (`id` int(11) NOT NULL AUTO_INCREMENT,`username` varchar(100) NOT NULL,`address` varchar(40) NOT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
show databases;
create database client_service;
use client_service;
CREATE TABLE `country` (`id` int(11) NOT NULL AUTO_INCREMENT,`country` varchar(10) NOT NULL,`country_code` int(3) NOT NULL,`abbreviation` varchar(2) NOT NULL,`prefix` varchar(3) NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `client` (`id` int(11) NOT NULL AUTO_INCREMENT,`country_id` int(11) NOT NULL,`client_code` int(5) NOT NULL,`client_name` varchar(50) NOT NULL,`description` text NOT NULL,`email` varchar(50) NOT NULL,`phone` int(12) NOT NULL,`msisdn` varchar(50) NOT NULL,`address` text NOT NULL,`active` boolean NOT NULL, PRIMARY KEY (`id`), foreign key country_id(country_id) references country(id) on delete cascade on update restrict ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `service` (`id` int(11) NOT NULL AUTO_INCREMENT,`service_name` varchar(10) NOT NULL,`service_description` text NOT NULL,`active` boolean NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `client_service` (`id` int(11) NOT NULL AUTO_INCREMENT,`client_id` int(11) NOT NULL,`service_id` int(11) NOT NULL,`active` boolean NOT NULL, PRIMARY KEY (`id`), foreign key client_id(client_id) references client(id) on delete cascade on update restrict, foreign key service_id(service_id) references service(id) on delete cascade on update restrict ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `user` (`id` int(11) NOT NULL AUTO_INCREMENT,`username` varchar(100) NULL,`address` varchar(40) NULL,`user_client_id` int(11) NOT NULL,PRIMARY KEY (`id`), foreign key user_client_id(user_client_id) references client(id) on delete cascade on update restrict) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
drop database client_service;
drop table client_service;
insert into country (country,country_code,abbreviation,prefix) values ("Kenya","254","KE","K");
insert into country (country,country_code,abbreviation,prefix) values ("Uganda","256","UG","U");
insert into country (country,country_code,abbreviation,prefix) values ("Tanzania","255","TZ","T");
select * from client_service.country;
insert into client (country_id,client_code,client_name,description,email,phone,msisdn,address,active) values ("1","1503","Sameer Holdings","Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua. At vero eos et accusam et justo duo dolores et ea rebum. Stet clita kasd gubergren, no sea takimata sanctus est Lorem ipsum dolor sit amet. Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua. At vero eos et accusam et justo duo dolores et ea rebum. Stet clita kasd gubergren, no sea takimata sanctus est Lorem ipsum dolor sit amet. Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua. At vero eos et accusam et justo duo dolores et ea rebum. Stet clita kasd gubergren, no sea takimata sanctus est Lorem ipsum dolor sit amet.","info@host.com","0700365248","447912345678","Nairobi","1");
insert into client (country_id,client_code,client_name,description,email,phone,msisdn,address,active) values ("3","1947","MTN Holdings","Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua. At vero eos et accusam et justo duo dolores et ea rebum. Stet clita kasd gubergren, no sea takimata sanctus est Lorem ipsum dolor sit amet. Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua. At vero eos et accusam et justo duo dolores et ea rebum. Stet clita kasd gubergren, no sea takimata sanctus est Lorem ipsum dolor sit amet. Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua. At vero eos et accusam et justo duo dolores et ea rebum. Stet clita kasd gubergren, no sea takimata sanctus est Lorem ipsum dolor sit amet.","kainfo@host.com","0701234248","447912345678","Kampala","1");
insert into client (country_id,client_code,client_name,description,email,phone,msisdn,address,active) values ("2","0032","Wazalendo Holdings","Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua. At vero eos et accusam et justo duo dolores et ea rebum. Stet clita kasd gubergren, no sea takimata sanctus est Lorem ipsum dolor sit amet. Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua. At vero eos et accusam et justo duo dolores et ea rebum. Stet clita kasd gubergren, no sea takimata sanctus est Lorem ipsum dolor sit amet. Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua. At vero eos et accusam et justo duo dolores et ea rebum. Stet clita kasd gubergren, no sea takimata sanctus est Lorem ipsum dolor sit amet.","darinfo@host.com","0732415248","447912345678","Dar es salam","1");
insert into client (country_id,client_code,client_name,description,email,phone,msisdn,address,active) values ("2","4657","Stima Holdings","Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua. At vero eos et accusam et justo duo dolores et ea rebum. Stet clita kasd gubergren, no sea takimata sanctus est Lorem ipsum dolor sit amet. Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua. At vero eos et accusam et justo duo dolores et ea rebum. Stet clita kasd gubergren, no sea takimata sanctus est Lorem ipsum dolor sit amet. Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua. At vero eos et accusam et justo duo dolores et ea rebum. Stet clita kasd gubergren, no sea takimata sanctus est Lorem ipsum dolor sit amet.","aruinfo@host.com","0706352248","447912345678","Arusha","1");
insert into client (country_id,client_code,client_name,description,email,phone,msisdn,address,active) values ("3","7236","Umeme Holdings","Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua. At vero eos et accusam et justo duo dolores et ea rebum. Stet clita kasd gubergren, no sea takimata sanctus est Lorem ipsum dolor sit amet. Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua. At vero eos et accusam et justo duo dolores et ea rebum. Stet clita kasd gubergren, no sea takimata sanctus est Lorem ipsum dolor sit amet. Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua. At vero eos et accusam et justo duo dolores et ea rebum. Stet clita kasd gubergren, no sea takimata sanctus est Lorem ipsum dolor sit amet.","ent@host.com","0702589248","447912345678","Entebe","1");
insert into client (country_id,client_code,client_name,description,email,phone,msisdn,address,active) values ("1","589","Bamburi Holdings","Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua. At vero eos et accusam et justo duo dolores et ea rebum. Stet clita kasd gubergren, no sea takimata sanctus est Lorem ipsum dolor sit amet. Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua. At vero eos et accusam et justo duo dolores et ea rebum. Stet clita kasd gubergren, no sea takimata sanctus est Lorem ipsum dolor sit amet. Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua. At vero eos et accusam et justo duo dolores et ea rebum. Stet clita kasd gubergren, no sea takimata sanctus est Lorem ipsum dolor sit amet.","bamburi@host.com","0798655248","447912345678","Mombasa","1");
select * from client_service.client;
describe client;
insert into service (service_name, service_description,active) values ("RTGS","Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua.","1");
insert into service (service_name, service_description,active) values ("EFT","Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua.","1");
insert into service (service_name, service_description,active) values ("IFT","Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua.","1");
insert into service (service_name, service_description,active) values ("Pesalink","Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua.","1");
insert into service (service_name, service_description,active) values ("MPESA","Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua.","1");
insert into service (service_name, service_description,active) values ("BATCH","Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua.","1");
insert into service (service_name, service_description,active) values ("BULK","Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua.","1");
select * from client_service.service;
insert into client_service (client_id, service_id, active) values ("1","7","1");
insert into client_service (client_id, service_id, active) values ("1","6","1");
insert into client_service (client_id, service_id, active) values ("2","2","1");
insert into client_service (client_id, service_id, active) values ("2","3","1");
insert into client_service (client_id, service_id, active) values ("3","4","1");
insert into client_service (client_id, service_id, active) values ("3","7","1");
insert into client_service (client_id, service_id, active) values ("4","7","1");
insert into client_service (client_id, service_id, active) values ("4","2","1");
insert into client_service (client_id, service_id, active) values ("4","3","1");
insert into client_service (client_id, service_id, active) values ("5","4","1");
insert into client_service (client_id, service_id, active) values ("6","5","1");
insert into client_service (client_id, service_id, active) values ("6","6","1");
select * from client_service.client_service;
insert into user (username, address, user_client_id) values ("John","Sameer","1");
select * from user;