SQLStatements Session 1 2 1

Topics: SQL, Foreign key, Articles with example SQL code Pages: 5 (899 words) Published: May 26, 2015
1.create table role(Id BIGINT(20), name varchar(255) NOT NULL,primary key(Id)); 2.create table skill(Id BIGINT(20)primary key, description varchar(255) NULL, name varchar(255) NOT NULL); 3.create table post_type(Id BIGINT(20)primary key,name varchar(255) NOT NULL); 4.create table department(Id BIGINT(20)primary key, name varchar(255) NOT NULL); 5.create table degree

(
Id BIGINT(20)primary key,
department_id BIGINT(20) NOT NULL,
name varchar(255) NOT NULL,
FOREIGN KEY(department_id) REFERENCES department(Id)
);

6. create table profile
(
Id BIGINT(20)primary key,
address varchar(255) NOT NULL,
batch varchar(255) NOT NULL,
degree_id BIGINT(20) NOT NULL,
designation varchar(255) NULL,
gender varchar(255) NOT NULL,
FOREIGN KEY(degree_id) REFERENCES degree(Id)
);

7. create table higher_degree
(
Id BIGINT(20)primary key,
degree_name varchar(255) NULL,
university_name varchar(255) NULL,
profile_id BIGINT(20) NOT NULL,
FOREIGN KEY(profile_id) REFERENCES profile(Id)
);

8. create table experience
(
Id BIGINT(20)primary key,
company_name varchar(255) NOT NULL,
current BIT(1) NOT NULL,
end DATETIME NULL,
start DATETIME NOT NULL,
profile_id BIGINT(20) NOT NULL,
FOREIGN KEY(profile_id) REFERENCES profile(Id)
);

9. create table project
(
Id BIGINT(20)primary key,
name varchar(255) NOT NULL,
number_of_members INT(11) NOT NULL,
profile_id BIGINT(20) NOT NULL,
short_description varchar(255) NULL,
FOREIGN KEY(profile_id) REFERENCES profile(Id)
);

10.create table profile_skills
(
skill_id BIGINT(20),
profile_id BIGINT(20),
PRIMARY KEY(skill_id,profile_id)
);

11. create table user
(
Id BIGINT(20) primary key,
emailid varchar(255) NOT NULL,
name varchar(255) NOT NULL,
password varchar(255) NOT NULL,
phonenumber varchar(255) NOT NULL,
profile_id BIGINT(20) NULL,
role_id BIGINT(20) NOT NULL,
username varchar(255) NOT NULL,
FOREIGN KEY(profile_id) REFERENCES profile(Id),
FOREIGN KEY(role_id) REFERENCES role(Id)
);

12. create table query
(
Id BIGINT(20)primary key,
content varchar(255) NOT NULL,
date DATETIME NOT NULL,
parent_id BIGINT(20) NULL,
user_id BIGINT(20)NOT NULL,
FOREIGN KEY(parent_id) REFERENCES query(Id),
FOREIGN KEY(user_id) REFERENCES user(Id)
);

13. create table event
(
Id BIGINT(20)primary key,
date DATETIME NOT NULL,
description varchar(255) NULL,
invitation varchar(255) NOT NULL,
name varchar(255) NOT NULL,
organiser_id BIGINT(20)NOT NULL,
FOREIGN KEY(organiser_id) REFERENCES user(Id)
);

14. create table post
(
Id BIGINT(20)primary key,
content varchar(255) NOT NULL,
date DATETIME NOT NULL,
type_id BIGINT(20) NOT NULL,
user_id BIGINT(20)NOT NULL,
FOREIGN KEY(type_id) REFERENCES post_type(Id),
FOREIGN KEY(user_id) REFERENCES user(Id)
);

15. alter table role add description varchar(255);

16.alter table role modify column description varchar(500);

17.alter table role drop column description;

18. alter table role rename roles;

19. alter table roles rename role;

20. alter table department add user_id BIGINT(20);

21. ALTER TABLE department ADD FOREIGN KEY(user_id) REFERENCES user(Id);

22. alter table department drop column user_id;

23. alter table department rename departments;

24. drop table profile_skills;

25. drop table post;

1. insert into role(id,name)
values(1,'designer'),
(2,'guide');
2. insert into skill(id,description,name)
values(1,'self learning','learn'),
(2,'technical skills','coding');
3. insert into post_type(id,name)
values(1,'business'),
(2,'business');
4. insert into department(id,name)
values(1,'bpo'),
(2,'IT');
5. insert into degree(id,department_id,name)
values(1,04,'EEE'),
(2,05,'ECE');
6. insert into profile(id,address,batch,degree_id,designation,gender) values(1,'London','En',044,'HR','Male'),...
Continue Reading

Please join StudyMode to read the full document

You May Also Find These Documents Helpful

  • Essay on EBT 1 Task 2
  • CU678 Units 1&2 Essay
  • 1 Research Paper
  • Week 1 2 Disappearing Languages Essay
  • Essay about unit 1 assignment 1
  • Assignment 1 Becoming an effective Leader Essay
  • Essay about Resume201404011104 1 1 2
  • 1 2 Essay

Become a StudyMode Member

Sign Up - It's Free