Alumni Management System Sql Queries

 

Alumni Management System

SQL Queries: 

@MRProgrammer89 

1) Write a query to create role table.

create table role(id number(10),name varchar(255));

2) Write a query to add a new column named description of type varchar(255) to role table.

alter table role add description varchar(255);

3) Write a query to change the type of field description in the role table to varchar(500);

alter table role modify description varchar(500);

4) Write a query to remove the column description from the role table.

alter table role drop column description;

5) Write a query to display the batch of the seniormost alumni with designation as 'Project Manager'. Give an alias as Senior_PM_Batch.

Hint:
Use min()

QUERY

select min(batch) as Senior_PM_Batch

from profile where designation = 'Project Manager';

6) Write a query to display the batch of the juniormost alumni with designation as 'Project Manager'. Give an alias as Junior_PM_Batch.

Hint:
Use max()

select max(batch) as Junior_PM_Batch

from profile where designation = 'Project Manager';

7) Write a query to display the designation and the number of users who are currently working in each designation. Give an alias as designation_count. Display the records sorted in ascending order based on designation.

select designation, count(designation) as

Designation_Count from profile group by (designation)

order by designation;

8) Write a query to display all the fields present in the role table. Display the recods sorted in ascending order based on name.

select id,name from role order by name;

9) Write a query to display the entire contents of the skill table, sorted by name in ascending order.

select * from skill order by name asc;

10) Write a query to display the entire contents of the department table, sorted by name in descending order.

select * from department order by name desc;

11) Write a query to display the entire contents of the post_type table, sorted by name in descending order.

select * from post_type order by name desc;

12) Write a query to display all role names, sorted in ascending order.

select name from ROLE order by name asc;

13) Write a query to display all the name and description present in the skill table. Display the records sorted in ascending order based on name.

select name,description from skill

order by name asc;

@MRProgrammer89 

14) Write a query to insert any 2 records into the skill table.

insert into skill values (1,'pune','jayesh');

insert into skill values (2,'Dhule','Jp');

15) Write a query to change the skill name 'CAD' to 'CADCAM'.

update skill set name= 'CADCAM' where name = 'CAD';

16) Write a query to delete the skill 'Web Design'.

delete from skill where name = 'Web Design';

17) Write a query to display the names of all administrators (role Admin) sorted in ascending order based on name.

select name from users 

where role_id in (select id from role where name='Admin') 

order by name;

@MRProgrammer89 

18) Write a query to display the names of all alumni (role Alumni) sorted by name in descending order.

select name from users where role_id

in (select id from role where name='Alumni') 

order by name desc;

19) Write a query to display the names of all degrees offered by 'CSE' department, sorted in ascending order.

select name from degree where department_id 

in(select id from department where name='CSE') 

order by name ;

20) Write a query to display the name of the department offering the degree 'BSC_CT'.

select name from department where id

in (select department_id from degree where name='BSC_CT');

21) Write a query to display the department of user Ram in the college.

select name from department where id 

in (select department_id from degree where id in

    (select degree_id from profile where id in

     (select profile_id from users where name='Ram')));

@MRProgrammer89 

22) Write a query to display the university name(s) in which Ram has done his higher studies. Display the records sorted in ascending order university_name.

Select university_name from higher_degree where profile_id 

in (select id from profile where id in

    (select profile_id from users where name='Ram' ));

@MRProgrammer89