MySQL

                                             MySQL 

                                                       Topic 1:-SQL STATEMENTS

Throughout our SQL programming session, we will be taking an example of the most popular database of ALUMNI MANAGEMENT SYSTEM

Alumni Management System

Refer the ER Diagram for Alumni Management System.

Create the following tables and write queries for the following DDL operations.

1:-DDL STATEMENTS-CREATE/ALTER/DROP
DDL-Data Definition Language

1.1 Create table role

Write a query to create role table. Sample Query: create table role (id BIGINT(20), name varchar(255) NOT NULL, primary key(id)); 

QUERY 1.1: 

1	create table role

2

(

3	id bigint(20),

4	name varchar(255) not null,

5	primary key(id));

1.2 Create table skill

1	create table skill(

2	id BIGINT(20) NOT NULL,

3	description VARCHAR(255),

4	name VARCHAR(255) not null,

5	primary key(id));

1.3 Create table post_type

1	create table post_type(

2	id bigint(20) not null,

3	name varchar(255) not null,

4	primary key(id));

1.4 Create table department

1	create table department(

2	id bigint(20) not null,

3	name varchar(255) not null,

4	primary key(id));

1.5 Create table degree

1	create table degree(

2	id bigint(20) not null,

3	department_id bigint(20) not null,

4	name varchar(255) not null,

5	primary key(id),

6	foreign key(department_id) references department(id));

1.6 Create table profile

1	create table profile(

2	id bigint(20) not null,

3	address varchar(255) not null,

4	batch varchar(255) not null,

5	degree_id bigint(20) not null,

6	designation varchar(255),

7	gender varchar(255) not null,

8	primary key(id),

9	foreign key(degree_id) references degree(id));

1.7 Create table higher_degree

1	create table higher_degree(

2	id bigint(20) not null,

3	degree_name varchar(255),

4	university_name varchar(255),

5	profile_id bigint(20) not null,

6	primary key(id),

7	foreign key(profile_id) references profile(id));

1.8 Create table experience

1	create table experience(

2	id bigint(20) not null,

3	company_name varchar(255) not null,

4	current bit(1) not null,

5	end datetime,

6	start datetime not null,

7	profile_id bigint(20) not null,

8	primary key(id),

9	foreign key (profile_id) references profile(id));

1.9 Create table project

1	create table project(

2	id bigint(20) not null,

3	name varchar(255) not null,

4	number_of_members int(11) not null,

5	profile_id bigint(20) not null,

6	short_description varchar(255),

7	primary key(id),

8	foreign key(profile_id) references profile(id));

1.10 Create table profile_skills

1	create table profile_skills(

2	skill_id bigint(20) not null,

3	profile_id bigint(20) not null,

4	constraint primary key(skill_id,profile_id),

5	foreign key(skill_id) references skill(id),

6	foreign key(profile_id) references profile(id));

1.11 Create table user

01	create table user(

02	id bigint(20) not null,

03	emailid varchar(255) not null,

04	name varchar(255) not null,

05	password varchar(255) not null,

06	phonenumber varchar(255) not null,

07	profile_id bigint(20),

08	role_id bigint(20) not null,

09	username varchar(255) not null,

10	primary key(id),

11	foreign key(role_id) references role(id));

1.12 Create table query

1	create table query(

2	id bigint(20) not null,

3	content varchar(255) not null,

4	date datetime not null,

5	parent_id bigint(20),

6	user_id bigint(20) not null,

7	primary key(id),

8	foreign key(parent_id) references query(id),

9	foreign key(user_id) references user(id));

1.13 Create table event

1	create table event(

2	id bigint(20) not null,

3	date datetime not null,

4	description varchar(255),

5	invitation varchar(255) not null,

6	name varchar(255) not null,

7	organiser_id bigint(20) not null,

8	primary key(id),

9	foreign key(organiser_id) references user(id));

1.14 Create table post

1	create table post(

2	id bigint(20) not null,

3	content varchar(255) not null,

4	date datetime not null,

5	type_id bigint(20) not null,

6	user_id bigint(20) not null,

7	primary key(id),

8	foreign key (type_id) references post_type(id),

9	foreign key (user_id) references user(id));

1.15 Add column to role  table 

1	alter table role

2	add description varchar(255);

1.16 Change column type in role table 

1	alter table role

2	modify column description varchar(500);

1.17 Remove column from role table 

1	alter table role

2	drop column description;

1.18 Change table name role 

1	alter table role

2	rename roles;

1.19 Change table name roles 

1	alter table roles

2	rename role;

1.20 Add column to department table

  alter table department
add user_id bigint(20) not null;

1.21 Add foreign key constraint to department table 

  alter table department
add constraint foreign key(user_id) references user(id);

1.22 Remove column from table department 

  alter table department
drop column user_id;

1.23 Change table name department

  alter table department

rename departments;

1.24 Delete table from profile_skills

drop table profile_skills;

1.25 Delete table post

drop table post;