PlacementDrive/create table.sql at master · WebDevSAAS/PlacementDrive · GitHub

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
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
create table student (
	`usn` VARCHAR(10) NOT NULL,
    `first_name` VARCHAR(50) NOT NULL,
    `last_name` VARCHAR(50),
	`branch` VARCHAR(50) NOT NULL,
	`gender` VARCHAR(6) NOT NULL,
	`dob` DATE NOT NULL,
	`email` VARCHAR(100) NOT NULL,
	`phone` VARCHAR(14) NOT NULL,
	`dateModified` TIMESTAMP,
	`password` VARCHAR(256) NOT NULL,
	`father_name` VARCHAR(80) DEFAULT NULL,
	`mother_name` VARCHAR(80) DEFAULT NULL,
	`cgpa_10th` DECIMAL(3,2) DEFAULT NULL,
	`g_state_10th` VARCHAR(50) DEFAULT NULL,
	`school_10th` VARCHAR(100) DEFAULT NULL,
	`board_10th` VARCHAR(70) DEFAULT NULL,
	`year_10th` DATE DEFAULT NULL,
	`cgpa_12th` DECIMAL(3,2) DEFAULT NULL,
	`g_state_12th` VARCHAR(50) DEFAULT NULL,
	`school_12th` VARCHAR(100) DEFAULT NULL,
	`board_12th` VARCHAR(70) DEFAULT NULL,
	`year_12th` DATE DEFAULT NULL,
	`result_sem1` DECIMAL(3,2) DEFAULT NULL,
	`result_sem2` DECIMAL(3,2) DEFAULT NULL,
	`result_sem3` DECIMAL(3,2) DEFAULT NULL,
	`result_sem4` DECIMAL(3,2) DEFAULT NULL,
	`result_sem5` DECIMAL(3,2) DEFAULT NULL,
	`result_sem6` DECIMAL(3,2) DEFAULT NULL,
	`result_sem7` DECIMAL(3,2) DEFAULT NULL,
	`result_sem8` DECIMAL(3,2) DEFAULT NULL,
	`cgpa_total` DECIMAL(3,2) DEFAULT NULL,
	`percentage_total` DECIMAL(3,2) DEFAULT NULL,
	`mobile` VARCHAR(14) DEFAULT NULL,
	`parents_mobile` VARCHAR(10) DEFAULT NULL,
	`parents_email` VARCHAR(100) DEFAULT NULL,
	`street` VARCHAR(50) DEFAULT NULL,
	`address_line2` VARCHAR(100) DEFAULT NULL,
	`city` VARCHAR(50) DEFAULT NULL,
	`state` VARCHAR(50) DEFAULT NULL,
	`country` VARCHAR(50) DEFAULT NULL,
	`postal_code` VARCHAR(6) DEFAULT NULL,
	`admission_quota` VARCHAR(20) DEFAULT NULL,
	`cet_rank` INT DEFAULT NULL,
	`comedk_rank` INT DEFAULT NULL,
	`backlogs` INT DEFAULT NULL,
	`edu_gap_10_12` INT DEFAULT NULL,
	`edu_gap_12_grad` INT DEFAULT NULL,
	`edu_gap_grad_sem` INT DEFAULT NULL,
	`citizenship` VARCHAR(20) DEFAULT NULL,
	`bank_acc` VARCHAR(20) DEFAULT NULL,
	`bank_name` VARCHAR(50) DEFAULT NULL,
	`passport_no` VARCHAR(8) DEFAULT NULL,
	`aadhar_no` VARCHAR(12) DEFAULT NULL,
	`pan_no` VARCHAR(10) DEFAULT NULL,
	`skypeid` VARCHAR(50) DEFAULT NULL,
	`githubid` VARCHAR(50) DEFAULT NULL,
 	`linkedinid` VARCHAR(50) DEFAULT NULL,
	`driving_license` VARCHAR(16) DEFAULT NULL,
	`voterid_no` VARCHAR(20) DEFAULT NULL,
	`awards` VARCHAR(300) DEFAULT NULL,
 	PRIMARY KEY(usn)
);

create table admins (
	`usn` VARCHAR(10) NOT NULL,
	`first_name` VARCHAR(50) not NULL,
	`email` VARCHAR(100) NOT NULL,
	`phone` VARCHAR(14) NOT NULL,
	`accountType` VARCHAR(8) NOT NULL,
	`lastModified` DATE NULL,
	`pendingWork` BOOLEAN DEFAULT 0 NULL,
	`extra` JSON DEFAULT NULL,
	`password` VARCHAR(256) NOT NULL,
	PRIMARY KEY(usn)
);

create table company(
	`c_id` INT(6) NOT NULL AUTO_INCREMENT,
	`c_name` VARCHAR(100) NOT NULL,
	`eg_marks_10th` DECIMAL(3,2) NULL,
	`eg_marks_12th` DECIMAL(3,2) NULL,
	`eg_cgpa` DECIMAL(3,2) NULL,
	`eg_branch` VARCHAR(50) NULL,
	`year_gap` INT NULL,
	`backlog_active` INT,
	`backlog_history` INT,
	`job_desc` VARCHAR(300) NULL,
	`pay_package` INT NULL,
	`internship` BOOLEAN DEFAULT FALSE,
	`duration` INT,
	`work_location` VARCHAR(100) NULL,
	`drive_date` DATE NULL,
	`app_end_date` DATE NULL,
	`sector` VARCHAR(50) NULL,
	`category` VARCHAR(50) NULL,
	 primary key(c_id)
);


insert into company (c_name,eg_marks_10th,eg_marks_12th,eg_cgpa,eg_branch,year_gap ,backlog_active,backlog_history,job_desc,pay_package,internship,duration,work_location,drive_date,app_end_date,sector,category) values('TATA',8.0,7.2,9.0,'CSE',2,0,0,'nothing',24000,TRUE,3,'bangalore','2002-09-15','2002-09-15','dhjshd','GM');


create table applied_selected (
	`s_usn` VARCHAR(10) NOT NULL,
	`cids` VARCHAR(100),
	`s_name` VARCHAR(50),
	`s_cname` VARCHAR(100),
	`selected` BOOLEAN DEFAULT FALSE,
	`created` TIMESTAMP,
	FOREIGN KEY (`s_usn`) REFERENCES student(`usn`) ON DELETE CASCADE 	ON UPDATE CASCADE,
	FOREIGN KEY (`s_cid`) REFERENCES company(`c_id`) ON DELETE CASCADE 	ON UPDATE CASCADE,
	primary key(`s_usn`)
);