Eugenics - SQL

(1)

(2)

--- <<<<<<<<< SESSION 1 >>>>>>>>>>>

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

CREATE TABLE role (

Id BIGINT(20),

name VARCHAR(255) NOT NULL, PRIMARY KEY(Id)

);

2. Write a query to create skill table. Please note that the field description can be NULL and the default value is NULL.

CREATE TABLE skill (

Id BIGINT(20) PRIMARY KEY, descriptiON VARCHAR(255) NULL, name VARCHAR(255) NOT NULL );

3. Write a query to create post_type table. CREATE TABLE post_type

(

Id BIGINT(20) PRIMARY KEY, name VARCHAR(255) NOT NULL );

4. Write a query to create department table. CREATE TABLE department

(

Id BIGINT(20) PRIMARY KEY, name VARCHAR(255) NOT NULL );

5. Write a query to create degree table. 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. Write a query to create profile table. Please note that designation field can be NULL and the default value is NULL.

CREATE TABLE profile (

Id BIGINT(20) PRIMARY KEY, address VARCHAR(255) NOT NULL, batch VARCHAR(255) NOT NULL, degree_id BIGINT(20) NOT NULL, designaton VARCHAR(255) NULL, gender VARCHAR(255) NOT NULL,

FOREIGN KEY(degree_id) REFERENCES degree(Id) );

7. Write a query to create higher_degree table. Please note that fields degree_name and university_name can be NULL and the default value is NULL.

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. Write a query to create experience table. Please note that filed end can be NULL and Default value is NULL.

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,

9. Write a query to create project table. Please note that field short_description can be NULL and the default value is NULL.

CREATE TABLE project (

Id BIGINT(20) PRIMARY KEY, name VARCHAR(255) NOT NULL,

(3)

number_of_members INT(11) NOT NULL, profile_id BIGINT(20) NOT NULL, short_descriptiON VARCHAR(255) NULL,

10. Write a query to create profile_skills table. CREATE TABLE profile_skills

(

skill_id BIGINT(20), profile_id BIGINT(20),

PRIMARY KEY(skill_id,profile_id) );

11. Write a query to create user table. Please note that the field profile_id can be NULL and the default value is NULL.

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. Write a query to create query table. Please note that field parent_id can be NULL and the default value is NULL.

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. Write a query to create event table. Please note that field description can be NULL and the default value is NULL.

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. Write a query to create post table. 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. Write a query to add a new column named description of type varchar(255) to role table.

ALTER TABLE role

ADD descriptiON VARCHAR(255);

16. Write a query to change the type of field description in the role table to varchar(500); ALTER TABLE role

MODIFY COLUMN descriptiON VARCHAR(500);

17. Write a query to remove the column description from the role table. ALTER TABLE role

DROP COLUMN descriptiON;

18. Write a query to rename table role to roles. ALTER TABLE role

RENAME roles;

19. Write a query to rename table roles to role. ALTER TABLE roles

(4)

20. Write a query to add a new column named user_id of type BIGINT(20) to department table.

ALTER TABLE department ADD user_id BIGINT(20);

21. Write a query to add a constraint to the department table. user_id is a foreign key and it references id in the user table.

ALTER TABLE department

ADD FOREIGN KEY(user_id) REFERENCES user(Id);

22. Write a query to drop the column user_id from the department table. ALTER TABLE department

DROP COLUMN user_id;

23. Write a query to rename table department to departments. ALTER TABLE department

RENAME departments;

24. Write a query to delete the table profile_skills. DROP TABLE profile_skills;

25. Write a query to delete post table. DROP TABLE post;

--- <<<<<<<<< SESSION 2 >>>>>>>>>>>

--- 1. Write a query to insert any 2 records into the role table.

INSERT INTO role(id,name) VALUES(1,'designer'), (2,'guide');

2. Write a query to insert any 2 records into the skill table. INSERT INTO skill(id,descriptiON,name)

VALUES(1,'self learning','learn'), (2,'technical skills','coding');

3. Write a query to insert any 2 records into the post_type table.

INSERT INTO post_type(id,name) VALUES(1,'business'),

(2,'business');

4. Write a query to insert any 2 records into the department table. INSERT INTO department(id,name)

VALUES(1,'bpo'), (2,'IT');

5. Write a query to insert any 2 records into the degree table. INSERT INTO degree(id,department_id,name)

VALUES(1,04,'EEE'), (2,05,'ECE');

6. Write a query to insert any 2 records into the profile table. INSERT INTO profile(id,address,batch,degree_id,designaton,gender) VALUES(1,'LONdON','En',044,'HR','Male'),

(2,'Chennai','For',045,'Manager','Female');

7. Write a query to insert any 2 records into the higher_degree table. INSERT INTO higher_degree(id,degree_name,university_name,profile_id) VALUES(1,'BE','Anna University',017),

(2,'BE','Anna University',016);

8. Write a query to insert any 2 records into the experience table. INSERT INTO experience(id,company_name,current,end,start,profile_id) VALUES(1,'CTS',1,'2008-11-11 13:23:44','2015-11-11 13:23:44',017), (2,'CTS',1,'2008-11-11 13:23:44','2015-11-11 13:23:44',016); 9. Write a query to insert any 2 records into the project table.

INSERT INTO project(id,name,number_of_members,profile_id,short_descriptiON) VALUES(1,'Alumini management',10,017,'for school AND college'),

(2,'Library management',11,016,'for school AND college'); 10. Write a query to insert any 2 records into the user table.

INSERT INTO user(id,emailid,name,password,phONenumber,profile_id,role_id,username) VALUES(1,'[email protected]','abcd','efgh',9884642005,017,46,'abcdefgh'),

(2,'[email protected]','ijkl','mnop',9845621745,016,47,'ijklmnop'); 11. Write a query to insert any 2 records into the query table. INSERT INTO query(id,cONtent,date,parent_id,user_id)

(5)

VALUES(1,'Alumini management','2008-11-11 13:23:44',78,45), (2,'Library management','2008-11-11 13:23:44',79,46); 12. Write a query to insert any 2 records into the event table. INSERT INTO event(id,date,descriptiON,invitatiON,name,organiser_id) VALUES(1,'2008-11-11 13:23:44','Alumni meet','OBA','ManikANDan',123), (2,'2008-11-11 13:23:44','Library opening','member','Narayanan',456); 13. Write a query to insert any 2 records into the post table.

INSERT INTO post(id,cONtent,date,type_id,user_id) VALUES(1,'Alumni meet','2008-11-11 13:23:44',789,123), (2,'Library opening','2008-11-11 13:23:44',987,456);

14. Write a query to change the role name 'Admin' to 'Administrator' UPDATE role

SET name='Administrator' WHERE name='Admin'; 15 Delete the role 'Student'. DELETE FROM role

WHERE name='Student';

16. Write a query to change the skill name 'CAD' to 'CADCAM'. UPDATE skill

SET name='CADCAM' WHERE name='CAD';

17 Write a query to delete the skill 'Web Design'. DELETE FROM skill

WHERE name='Web Design';

18 Write a query to change the post_type 'Tech' to 'Technology'. UPDATE post_type

SET name='Technology' WHERE name='Tech';

19 Write a query to delete the post_type 'Technology'. DELETE FROM post_type

WHERE name='Technology';

20 Write a query to change the batch name from 98 to 1998.

UPDATE profile SET batch='1998' WHERE batch='98';

21 Write a query to update the year in the date entry in the query table from 2012 to 2013. UPDATE query SET date=DATE_FORMAT(date,'2013-%m-%d') WHERE date=DATE_FORMAT(date,'2012-%m-%d') ; UPDATE role SET name='Administrator' WHERE name='Admin';

22) Accidentally the server date was set to a wrong value for 1 day. You have correctly set the date now. But you want to change all the date entries made on that day. Write a query to change the day by 1 of all dates in the query table on 31st Jan 2013.

UPDATE query

SET date=DATE_ADD(date,INTERVAL 1 DAY) WHERE DATE(date)='2013-01-31';

23) Write a query to delete all queries from the query table posted before year 2012. DELETE FROM query

WHERE date

BETWEEN DATE_FORMAT(date,'0000-%m-%d') AND DATE_FORMAT(date,'2011-%m-%d'); 24) Write a query such that the experience table contains only the details regarding the current experience of the alumni.

DELETE FROM experience WHERE end is not null;

25) The event 'ALUMNI MEET' has been postponed by 3 hours. Write a query to change the event time.

UPDATE event

SET date=DATE_ADD(date,INTERVAL 3 HOUR) WHERE name='ALUMNI MEET';

--- <<<<<<<<< SESSION 3 >>>>>>>>>>>

--- 1. Write a query to display the entire contents of the role table, sorted by name in ascending order.

(6)

FROM role ORDER BY name;

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

SELECT * FROM skill ORDER BY name;

3. 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;

4. 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;

5. Write a query to display all role names, sorted in ascending order. SELECT name

FROM role ORDER BY name;

6. Write a query to display the names and descriptions of all skills, sorted by skill name. SELECT name,descriptiON

FROM skill ORDER BY name;

7. Write a query to display the names of all departments, sorted in ascending order. SELECT name

FROM department ORDER BY name;

8. Write a query to display the names of all post types, sorted in ascending order. SELECT name

FROM post_type ORDER BY name;

9. Write a query to display the names of all universities in which the alumni from this college have done or are doing their higher studies, sorted in ascending order.

SELECT DISTINCT university_name FROM higher_degree

ORDER BY university_name;

10. Write a query to display all unique designations from the profile table sorted in ascending order.

SELECT DISTINCT designaton FROM profile

ORDER BY designaton;

11. Write a query to display the batch details of all alumni who are currently project managers, sorted in ascending order.

SELECT DISTINCT batch FROM profile,experience

WHERE designaton='PROJECT MANAGER' AND current=1 ORDER BY batch;

12. Write a query to display all designations of Male students/alumni from the profile table sorted in ascending order.

WHERE gender='MALE' ORDER BY designaton;

13. Write a query to display all designations of Female students/alumni from batch 2008 from the profile table sorted in ascending order.

WHERE batch=2008 AND gender='FEMALE' ORDER BY designaton;

14. Write a query to display the address details of Male alumni from 2008 batch, sorted in ascending order based on address.

SELECT address FROM profile

WHERE gender='MALE' AND batch=2008 ORDER BY address;

15. Write a query to display the names of companies in which the college alumni have been or are employees, sorted in ascending order based on name.

(7)

SELECT DISTINCT company_name FROM experience

ORDER BY company_name;

16. Write a query to display the names of companies in which the college alumni are employees at present, sorted in descending order based on name.

SELECT DISTINCT company_name FROM experience

WHERE current=1

ORDER BY company_name DESC;

17. Write a query to display the names and phone numbers of all users, sorted in descending order by name.

SELECT name,phONenumber FROM user

ORDER BY name DESC;

18. Write a query to display the names, emailids and phone numbers of all users, sorted in ascending order by name.

SELECT name,emailid,phONenumber FROM user

ORDER BY name;

19. Write a query to display the user name and password of Ram. SELECT username,password

FROM user

WHERE name='RAM';

20. Write a query to display the names and descriptions of projects with more than 10 members, sorrted in ascending order by name.

SELECT name,short_descriptiON FROM project

WHERE number_of_members>10 ORDER BY name;

21. Write a query to display the names and descriptions of all events scheduled on 27th January, 2014, sorted in ascending order by name.

SELECT name,descriptiON FROM event

WHERE date>'2014-01-27' ORDER BY name;

22. Write a query to display the content of all posts, sorted in descending order by date.

SELECT cONtent FROM post

ORDER BY date DESC;

23. Write a query to display the content of all posts posted in January, 2014, sorted in descending order by date.

SELECT cONtent FROM post

WHERE date>'2014-01' ORDER BY date DESC;

24. Write a query to display the contents of all queries posted in year 2013, sorted in descending order by date.

SELECT cONtent FROM query

WHERE YEAR(date)='2013' ORDER BY date DESC;

25. Write a query to display the names of all events scheduled in January 27, 2014 at 3 p.m, sorted by name in ascending order.

SELECT name FROM event WHERE date='2014-01-27 15:00:00.0' ORDER BY name; --- <<<<<<<<< SESSION 4 >>>>>>>>>>> --- 1) Write a query to display the number of roles in the role table. Give an alias to the number of roles as role_count.

SELECT COUNT(name) AS role_count FROM role;

2) Write a query to display the number of skills in the skill table. Give an alias to the number of skills as skill_count.

SELECT COUNT(name) AS skill_count FROM skill;

3) Write a query to display the number of users who have not filled their profile yet. Give an alias to the number of users as user_count.

(8)

FROM user

WHERE profile_id IS NULL;

4) Write a query to display the number of alumni from 2008 batch who have registered in the system. Give an alias as alumni_2008_count.

SELECT COUNT(batch) AS alumni_2008_count FROM profile

WHERE batch='2008';

5) Write a query to display the number of male alumni from 2008 batch who have registered in the system. Give an alias as alumni_male_2008_count.

SELECT COUNT(id) AS alumni_male_2008_count FROM profile

WHERE batch='2008' AND gender='MALE';

6) Write a query to display the batch name and the number of alumni from each batch who have registered in the system, sorted by batch name. Give an alias to the number of alumni from each batch as alumni_count.

SELECT batch, COUNT(id) AS alumni_count FROM profile

GROUP BY batch;

7) Write a query to display the batch name and the number of male alumni from each batch who have registered in the system,sorted by batch name. Give an alias to the number of male alumni from each batch as male_alumni_count.

SELECT batch,COUNT(id) AS male_alumni_count FROM profile

WHERE gender='MALE' GROUP BY batch;

8) Write a query to display the batch name and the number of female alumni from each batch who have registered in the system, sorted by batch name. Give an alias to the number of female alumni from each batch as female_alumni_count.

SELECT batch,COUNT(id) AS female_alumni_count FROM profile

WHERE gender='FEMALE' GROUP BY batch;

9) Write a query to display the number of unique designatons in the profile table. Give an alias as designaton_count.

SELECT COUNT(DISTINCT(designaton)) AS designaton_count FROM profile;

10. Write a query to display the number of users who are currently working as 'Project Manager's. Give an alias as PM_Count.

SELECT COUNT(designaton) AS PM_count FROM profile

WHERE designaton='PROJECT MANAGER';

11. Write a query to display the batch of the seniormost alumni woking as 'Project Manager'. Give an alias as Senior_PM_Batch.

SELECT MIN(BATCH) AS Senior_PM_Batch FROM profile

12. Write a query to display the batch of the juniormost alumni woking as 'Project Manager'. Give an alias as Junior_PM_Batch.

SELECT MAX(BATCH) AS Junior_PM_Batch FROM profile

13) Write a query to display the designaton and the number of users who are currently working in each designaton, sorted by designaton. Give an alias as designaton_count. SELECT designaton,COUNT(designaton) AS designaton_count

FROM profile

GROUP BY designaton ;

14) Write a query to display the number of companies in which the college alumni are currently working in. Give an alias as company_count.

SELECT COUNT(*) AS company_count FROM experience

WHERE current=1;

15) Write a query to display the name of the company and the number of alumni who are currently working in each company. Give an alias to the number of alumni as alumni_count.

SELECT company_name, COUNT(*) AS alumni_count FROM experience

WHERE current=1

GROUP BY company_name;

16) Write a query to display the number of projects in which the team size is more than 10. Give an alias as project_count.

SELECT COUNT(*) AS project_count FROM project

(9)

WHERE number_of_members>10;

17) Write a query to display the number of events that were scheduled for the year 2013. Give an alias as number_of_events.

SELECT COUNT(*) AS number_of_events FROM event

WHERE year(date)=2013;

18) Write a query to display the year and the number of events scheduled in each year, sorted by year. Give an alias as number_of_events.

SELECT year(date) ,COUNT(year(date)) AS number_of_events FROM event

GROUP BY year(date);

19) Write a query to display the number of posts posted in January 2014. Give an alias as number_of_posts.

SELECT COUNT(*) AS number_of_posts FROM post

WHERE date>'2014-01';

20) Write a query to display the number of queries raised / answered between 1 a.m and 5 a.m (both inclusive). Give an alias as number_of_queries.

SELECT COUNT(*) AS number_of_queries FROM query

WHERE TIME(date) BETWEEN '01:00' AND '05:00';

21) Write a query to display the number of events that were scheduled on 27th January, 2014. Give an alias as number_of_events.

SELECT COUNT(*) AS number_of_events FROM event

WHERE DATE(date)='2014-01-27' ;

22) Write a query to display the number of posts posted before 1st December, 2013. Give an alias as number_of_posts.

WHERE DATE(date)<'2013-12-01';

23) Write a query to display the number of posts posted after 1st December, 2013. Give an alias as number_of_posts.

WHERE DATE(date)>'2013-12-01';

24) Write a query to display the year and the number of events scheduled in each year, sorted by year. Give an alias as number_of_events.

SELECT YEAR(date),COUNT(*) AS number_of_events FROM event

GROUP BY YEAR(date) ;

25) Write a query to display the name of the month and the number of events scheduled in each month in the year 2013, sorted by month. Give an alias to the month name as month_name and the to the number of events scheduled as number_of_events. Name of the month must be displayed as January, February ...

SELECT MONTHNAME(date) AS month_name,COUNT(*) as number_of_events FROM event WHERE YEAR(date)='2013' GROUP BY MONTH(date); --- <<<<<<<<< SESSION 5 >>>>>>>>>>> --- 1) Write a query to display the absolute value of 2.

SELECT abs(2);

2) Write a query to display the absolute value of -32. Give an alias as absolute. SELECT abs(-32) as absolute;

3) Write a query to display the smallest integer value not less than 1.23. Give an alias as int_value.

SELECT ceil(1.23) as int_value;

4) Write a query to display the smallest integer value not less than -1.23. Give an alias as int_value.

SELECT ceil(-1.23) as int_value;

5) Write a query to convert a base 16 number 'F' to a base-2 number. Give an alias as base2.

SELECT cONv('f',16,2) as base2;

6) Write a query to display the largest integer value not greater than 1.23. Give an alias as int_value.

SELECT floor(1.23) as int_value;

(10)

int_value.

SELECT floor(-1.23) as int_value;

8) Write a query to round off 1.298 to one decimal place. Give an alias as round_value. SELECT round(1.298,1) as round_value;

9) Write a query to return the weekday index of the date 1st March, 2014. Give an alias as weekday_index. Hint : Use the function weekday.

SELECT weekday('2014-03-01') AS weekday_index;

10) Write a query to return the day name of the date 1st March, 2014. Give an alias as weekday_name.

SELECT dayname('2014-03-01') AS weekday_name;

--- <<<<<<<<< SESSION 6 >>>>>>>>>>>

--- 1. Write a query to display the name of the users and the length of the names of the users, sorted by the name of the user. Give an alias to the length of the name as length. Use the inbuilt function length().

SELECT name,length(name) AS length FROM user ORDER BY name;

2. Write a query to display the name of the user(s) having the longest name, sorted by the name of the user.

SELECT name From user WHERE length(name) IN (SELECT MAX(length(name )) FROM user ); 3. Write a query to display the name of the user(s) who have set a password of less than 3 characters, sorted by the name of the user.

SELECT name FROM user WHERE length(password)<3 ORDER BY name;

4. Write a query to display the names of the users and the first 2 characters of the names of all users, sorted by the name of the user. Give an alias to the first 2 characers as start_2_chars.

SELECT name,LEFT(name,2) AS start_2_chars FROM user ORDER BY name;

5. Write a query to display the names of the users and the last 2 characters of the names of all users, sorted by the name of the user. Give an alias to the first 2 characers as last_2_chars.

SELECT name,RIGHT(name,2) AS last_2_chars FROM user ORDER BY name;

6. Write a query to display the names of all users in lowercase, sorted by the name of the user. Give an alias as lower_case.

SELECT LOWER(name) AS lower_case FROM user ORDER BY name;

7. Write a query to display the names of all users in uppercase, sorted by the name of the user. Give an alias as upper_case.

SELECT UPPER(name) AS upper_case FROM user ORDER BY name;

8. Write a query to display the names of users in a 30 character length field by left padding with ?, sorted by the name of the user. Give an alias as modified_name.

SELECT LPAD(name,30,'?') AS modified_name FROM user ORDER BY name;

9. Write a query to display the names of users in a 30 character length field by right padding with ?, sorted by the name of the user. Give an alias as modified_name.

SELECT RPAD(name,30,'?') AS modified_name FROM user ORDER BY name;

10. Write a query to display the names of the users and the names of users with leading space characters removed, sorted by the name of the user. Give an alias as modified_name.

SELECT name,LTRIM(name) AS modified_name FROM user ORDER BY name;

11. Write a query to display the names of the users and the names of users with trailing space characters removed, sorted by the name of the user. Give an alias as modified_name.

SELECT name,RTRIM(name) AS modified_name FROM user ORDER BY name;

12. Write a query to display the names of the users and the names of users with leading and trailing space characters removed, sorted by the name of the user. Give an alias as modified_name.

SELECT name,RTRIM(LTRIM(name)) AS modified_name FROM user ORDER BY name; 13. Write a query to display the names of all users that start and end with letter a. SELECT name FROM user WHERE name LIKE 'a%a';

14. Write a query to display the names of users that end with aa, sorted by name. SELECT name FROM user WHERE name LIKE '%aa' ORDER BY name;

(11)

SELECT name FROM user WHERE name LIKE '%aa%' ORDER BY name;

16. Write a query to display all 6-letter names that start with P and end with a, sorted by name.

SELECT name FROM user WHERE name LIKE 'P%' AND LENGTH(name)=6 ORDER BY name; ---

<<<<<<<<< SESSION 7 >>>>>>>>>>>

--- 1) Write a query to display the entire contents of the table after performing an inner join on role table and user table, sorted by role name and then by name of the user.

SELECT role.*,user.* FROM role

INNER JOIN user ON role.id=user.role_id

ORDER BY role.name ASC, user.name ASC;

2) Write a query to display the entire contents of the table after performing a left join on role table and user table, sorted by role name and then by name of the user.

SELECT role.*,user.* FROM role

LEFT JOIN user ON role.id=user.role_id

ORDER BY role.name ASC, user.name ASC;

3) Write a query to display the user name and role of all users, sorted by name of the user. SELECT user.name,role.name

FROM user INNER JOIN role ON user.role_id=role.id ORDER BY user.name ASC;

4) Write a query to display the phone number, emailid, batch, department, designaton and the currently working company name of 'Ram'.

SELECT user.phonenumber, user.emailid,profile.batch,department.name,profile.designaton, experience.company_name FROM user JOIN profile ON user.profile_id=profile.id JOIN degree ON profile.degree_id=degree.id JOIN department ON degree.department_id=department.id JOIN experience ON profile.id=experience.profile_id

WHERE user.name='Ram' AND experience.current=1;

5) Write a query to display the name and skillset name of all alumni users (role - 'Alumni'), sorted by name and then by skillset name.

SELECT user.name,skill.name FROM user JOIN role ON user.role_id=role.id JOIN profile ON user.profile_id=profile.id JOIN profile_skills ON profile.id=profile_skills.profile_id JOIN skill ON profile_skills.skill_id=skill.id WHERE role.name='Alumni' ORDER BY user.name,skill.name;

6) Write a query to display the name and all company names in which they have worked of all alumni users(role - 'Alumni') who have been employed or are employed, sorted by name and then by company name.

SELECT user.name,experience.company_name FROM user JOIN role ON user.role_id=role.id JOIN profile ON user.profile_id=profile.id JOIN experience ON profile.id=experience.profile_id WHERE role.name='Alumni' ORDER BY user.name,experience.company_name;

7) Write a query to display the name and all company names in which they have worked of all alumni users(role - 'Alumni') , sorted by name and then by company name. (Include users who have never been employed also).

SELECT user.name,experience.company_name FROM user

left JOIN role

ON user.role_id=role.id left JOIN profile

ON user.profile_id=profile.id left JOIN experience

ON profile.id=experience.profile_id WHERE role.name='Alumni'

(12)

8) Write a query to display the name and the company name in which they are working of all alumni users(role - 'Alumni'), sorted by name of the user.(Include alumni users who are currently working only).

SELECT user.name,experience.company_name FROM user JOIN role ON user.role_id=role.id JOIN profile ON user.profile_id=profile.id JOIN experience ON profile.id=experience.profile_id

WHERE role.name='Alumni' AND experience.current=1 ORDER BY user.name

9) Write a query to display the name and the company name in which they are currently working of all alumni users(role - 'Alumni') from 2008, sorted by name. [Include users who are currently working only]

SELECT user.name,experience.company_name FROM user JOIN role ON user.role_id=role.id JOIN profile ON user.profile_id=profile.id JOIN experience ON profile.id=experience.profile_id

WHERE role.name='Alumni' AND experience.current=1 AND profile.batch='2008' ORDER BY user.name

10) Write a query to display the name, email id, phone number and address of all alumni(role - 'Alumni') users who have filled in their profile, sorted by name.

SELECT user.name,user.emailid,user.phonenumber,profile.address FROM user JOIN role ON user.role_id=role.id JOIN profile ON user.profile_id=profile.id

WHERE role.name='Alumni' AND user.profile_id is not null ORDER BY user.name

11) Write a query to display the name, email id, phone number and address of all alumni(role - 'Alumni') users from 2008 batch, sorted by name.

SELECT user.name,user.emailid,user.phonenumber,profile.address FROM user JOIN role ON user.role_id=role.id JOIN profile ON user.profile_id=profile.id

WHERE role.name='Alumni' AND profile.batch='2008' ORDER BY user.name

12) Write a query to display the name, email id, phone number and address of all alumni(role - 'Alumni') users from 'BSC_CT', sorted by name.

SELECT user.name,user.emailid,user.phonenumber,profile.address FROM user JOIN role ON user.role_id=role.id JOIN profile ON user.profile_id=profile.id JOIN degree ON profile.degree_id=degree.id

WHERE role.name='Alumni' AND degree.name='BSC_CT' ORDER BY user.name

13) Write a query to display the name, email id, phone number and address of all alumni(role - 'Alumni') users from 'CSE' department, sorted by name.

SELECT user.name,user.emailid,user.phonenumber,profile.address FROM user JOIN role ON user.role_id=role.id JOIN profile ON user.profile_id=profile.id JOIN degree ON profile.degree_id=degree.id JOIN department ON degree.department_id=department.id

WHERE role.name='Alumni' AND department.name='CSE' ORDER BY user.name

14) Write a query to display the name of the alumni users(role - 'Alumni') who have done higher studies in other colleges / universities and the University(s) in which the user has done higher studies, sorted by name and then by University.

SELECT user.name,higher_degree.university_name FROM user

INNER JOIN role ON user.role_id=role.id INNER JOIN profile

ON user.profile_id=profile.id INNER JOIN higher_degree

ON profile.id=higher_degree.profile_id

(13)

GROUP BY user.name,higher_degree.university_name ;

15) Write a query to display the name of the alumni users(role - 'Alumni') from 'CSE' department who have done higher studies in other colleges / universities and the University(s) in which the user has done higher studies, sorted by name and then by University.

SELECT user.name,higher_degree.university_name FROM user

INNER JOIN role ON user.role_id=role.id INNER JOIN profile

ON user.profile_id=profile.id INNER JOIN higher_degree

ON profile.id=higher_degree.profile_id INNER JOIN degree

ON profile.degree_id=degree.id JOIN department

ON degree.department_id=department.id

WHERE role.name='Alumni' AND department.name='CSE' AND higher_degree.degree_name is not null

GROUP BY user.name,higher_degree.university_name ;

16) Write a query to display the names, email id, phone number and address of all alumni users (role - 'Alumni') who have done their higher studies in 'Texas University', sorted in order. SELECT user.name,user.emailid,user.phonenumber,profile.address FROM user JOIN role ON user.role_id=role.id JOIN profile ON user.profile_id=profile.id JOIN degree ON profile.degree_id=degree.id JOIN department ON degree.department_id=department.id INNER JOIN higher_degree

ON profile.id=higher_degree.profile_id

WHERE role.name='Alumni' AND higher_degree.university_name='Texas University' ORDER BY user.name

17) Write a query to display the name and batch of all female alumni users(role - 'Alumni'), sorted by name. SELECT user.name,profile.batch FROM user JOIN role ON user.role_id=role.id JOIN profile ON user.profile_id=profile.id

WHERE role.name='Alumni' AND profile.gender='female' ORDER BY user.name

18) Write a query to display the name, batch and degree of all female alumni users(role - 'Alumni'), sorted by name of the user.

SELECT user.name,profile.batch,degree.name FROM user JOIN role ON user.role_id=role.id JOIN profile ON user.profile_id=profile.id JOIN degree ON profile.degree_id=degree.id

19) Write a query to display the name, batch, degree and department of all female alumni users(role - 'Alumni'), sorted by name.

SELECT user.name,profile.batch,degree.name,department.name FROM user JOIN role ON user.role_id=role.id JOIN profile ON user.profile_id=profile.id JOIN degree ON profile.degree_id=degree.id JOIN department ON degree.department_id=department.id

20) Write a query to display all degree names and the department offering the degree, sorted by degree name.

SELECT degree.name,department.name FROM degree

INNER JOIN department

ON degree.department_id=department.id ORDER BY degree.name;

21) Write a query to display the name and designaton of all male alumni users(role - 'Alumni'), sorted by name.

SELECT user.name,profile.designaton FROM user

(14)

JOIN role

ON user.role_id=role.id JOIN profile

ON user.profile_id=profile.id

WHERE role.name='Alumni' AND profile.gender='male' ORDER BY user.name

22) Write a query to display the name and designaton of all alumni users(role - 'Alumni') who are currently working in TCS, sorted by name.

SELECT user.name,profile.designaton FROM user JOIN role ON user.role_id=role.id JOIN profile ON user.profile_id=profile.id JOIN experience ON profile.id=experience.profile_id

WHERE role.name='Alumni' AND experience.current=1 AND

experience.company_name='TCS' ORDER BY user.name

23) Write a query to display the names of the users who have raised or answered queries and the content of the queries, sorted by name and then by date.

SELECT user.name,query.content FROM user

INNER JOIN query ON user.id=query.user_id ORDER BY name,date(date);

24) Write a query to display the names of the users who have answered queries and the content of the queries, sorted by name and then by date.

SELECT user.name,query.content FROM user

INNER JOIN query ON user.id=query.user_id WHERE parent_id is not null ORDER BY name,date(date);

25) Write a query to display the names of the users who have posted posts and the contents of posts, sorted by name and then by post date.

SELECT user.name,post.content FROM user

INNER JOIN post ON user.id=post.user_id

ORDER BY user.name,date(date);

26) Write a query to display the names of the users who have posted 'Technology' related posts and the contents of posts, sorted by name, post date and then by post content. SELECT user.name,post.content

FROM user INNER JOIN post ON user.id=post.user_id INNER JOIN post_type ON post.type_id=post_type.id WHERE post_type.name='Technology' ORDER BY user.name,post.date,post.content;

27) Write a query to display the names of the users who have posted posts in the year 2013 and the contents of posts, sorted by name and then by post date and then by content.

SELECT user.name,post.content FROM user

INNER JOIN post ON user.id=post.user_id WHERE year(post.date)='2013'

ORDER BY user.name,post.date,post.content;

28) Write a query to display the name and department of users who have good 'Programming' skills, sorted by name of the user.

SELECT user.name,department.name FROM user JOIN role ON user.role_id=role.id JOIN profile ON user.profile_id=profile.id JOIN degree ON profile.degree_id=degree.id JOIN department ON degree.department_id=department.id JOIN profile_skills ON profile.id=profile_skills.profile_id JOIN skill ON profile_skills.skill_id=skill.id WHERE skill.name='Programming' ORDER BY user.name

29) Write a query to display the names of the users who have organized events and the name of the events, sorted by name of the user and then by event date.

SELECT user.name,event.name FROM user

(15)

INNER JOIN event

ON user.id=event.organiser_id ORDER BY user.name,event.date;

30) Write a query to display the names of the alumni users(role - 'Alumni') who have organized events and the name of the events, sorted by name and then by event date. SELECT user.name,event.name

FROM user INNER JOIN event

ON user.id=event.organiser_id JOIN role ON user.role_id=role.id WHERE role.name='Alumni' ORDER BY user.name,event.date; --- <<<<<<<<< SESSION 8 >>>>>>>>>>> --- 1. Write a query to display the role name and the count of users in each role(Give an alias as role_count), sorted by name. Display only the roles in which there is atleast 1 user. SELECT role.name,COUNT(*) as role_count

FROM user INNER JOIN role ON user.role_id=role.id WHERE user.role_id is not null GROUP BY user.role_id ORDER BY role.name;

2. Write a query to display the role name and the count of users in each role(Give an alias as role_count), sorted by name. Display all roles.

SELECT r.name, COUNT(u.id) as role_count FROM role as r

LEFT OUTER JOIN user as u ON u.role_id = r.id GROUP BY r.name ORDER BY r.name;

3. Write a query to display the name of all alumni user(s) (Role-'Alumni') and the number of companies in which each user has worked in (Give an alias as company_count), sorted by user name.

SELECT u.name, (SELECT COUNT(*) FROM role as r, profile as p, experience as e

WHERE u.role_id = r.id AND r.name ='Alumni' AND u.profile_id = p.id AND p.id = e.profile_id) company_count

FROM user u,role r

WHERE u.role_id = r.id AND r.name = 'Alumni' GROUP BY u.name

ORDER BY u.name;

4. Write a query to display the name of the alumni user(s) (Role-'Alumni') who has/have worked in maximum number of companies, sorted by name.

SELECT u.name

FROM role as r, profile as p, user as u, experience as e

WHERE u.role_id = r.id AND r.name = 'Alumni' AND u.profile_id = p.id AND p.id = e.profile_id GROUP BY u.name

ORDER BY COUNT(e.profile_id) DESC LIMIT 1;

5. Write a query to display the name of all the alumni user(s) (Role-'Alumni') and the number of skillsets they possess (Give an alias as skill_count), sorted by name.

SELECT u.name, (SELECT COUNT(*)

FROM role as r, profile as p, profile_skills as ps, skill as s

WHERE u.role_id = r.id AND r.name = 'Alumni' AND u.profile_id = p.id AND p.id = ps.profile_id AND ps.skill_id = s.id) skill_count

FROM user u,role r WHERE u.role_id = r.id AND r.name = 'Alumni' GROUP BY u.name

ORDER BY u.name;

6. Write a query to display the name of the alumni user(s) (Role-'Alumni') who has/have more than 3 skillsets, sorted by name.

SELECT u.name

FROM user as u, role as r, profile as p, profile_skills as ps, skill as s

WHERE u.role_id = r.id AND r.name = 'Alumni' AND u.profile_id = p.id AND p.id = ps.profile_id AND ps.skill_id = s.id

GROUP BY u.name HAVING COUNT(*)>3 ORDER BY u.name;

7. Write a query to display the department name and the number of degrees offered by each department (Give an alias as degree_count), sorted by department name. Display only the departments which offer atleast 1 degree.

SELECT dm.name, COUNT(*) as degree_count FROM degree as d, department as dm WHERE d.department_id = dm.id

GROUP BY dm.name HAVING COUNT(*) >=1 ORDER BY dm.name;

8. Write a query to display the department name and the number of degrees offered by each department (Give an alias as degree_count), sorted by department name. Display all departments.

(16)

FROM department as dm LEFT OUTER JOIN degree as d ON d.department_id = dm.id GROUP BY dm.name ORDER BY dm.name;

9. Write a query to display the name of the department(s) offering the maximum number of degrees, sorted by department name.

SELECT dm.name

FROM department as dm, degree as d WHERE d.department_id = dm.id

GROUP BY dm.name HAVING COUNT(d.id) = 3 ORDER BY dm.name ;

10. Write a query to display the department name and the number of alumni users registered in the system from each department (Give an alias as alumni_count), sorted by department name. Display only departments from which atleast 1 alumni user has registered.

SELECT dm.name, COUNT(u.id) as alumni_count

FROM department as dm, degree as d, profile as p, user as u, role as r WHERE u.role_id = r.id AND r.name = 'Alumni' AND u.profile_id = p.id AND p.degree_id = d.id AND d.department_id = dm.id GROUP BY dm.name HAVING COUNT(u.id) >= 1

ORDER BY dm.name;

11. Write a query to display the department name and the number of alumni users registered in the system from each department (Give an alias as alumni_count), sorted by department name. Display only departments in which atleast 3 alumni users have registered.

SELECT dm.name, COUNT(u.id) as alumni_count

FROM department as dm, degree as d, profile as p, user as u, role as r WHERE u.role_id = r.id AND r.name = 'Alumni' AND u.profile_id = p.id AND p.degree_id = d.id AND d.department_id = dm.id GROUP BY dm.name HAVING COUNT(u.id) >= 3

ORDER BY dm.name;

12. Write a query to display the post_type and the number of posts posted under each type (Give an alias as post_count), sorted by post_type. Include only post_types under which there is atleast 1 post.

SELECT pt.name, COUNT(p.id) as post_count FROM post as p, post_type as pt

WHERE pt.id = p.type_id

GROUP BY pt.id HAVING COUNT(p.id) >= 1 ORDER BY pt.name;

13. Write a query to display the names of the user and the number of posts each user has posted (Give an alias as post_count), sorted by name of the user. Display only the names of users who have posted atleast one post.

SELECT u.name, COUNT(p.id) as post_count FROM user as u, post as p

WHERE u.id = p.user_id

GROUP BY u.name HAVING COUNT(p.id) >= 1 ORDER BY u.name;

14. Write a query to display the names of the user and the number of posts each user has posted (Give an alias as post_count), sorted by name of the user. Display the names of all users.

SELECT u.name, COUNT(p.id) as post_count FROM user as u

LEFT OUTER JOIN post as p ON u.id = p.user_id GROUP BY u.name ORDER BY u.name;

15. Write a query to display the name(s) of the user who has/have posted the maximum number of posts, sorted by name.

SELECT u.name FROM user AS u JOIN post AS p ON u.id = p.user_id WHERE u.id=p.user_id GROUP BY u.name

HAVING COUNT(p.id) = (SELECT MAX(ct)

FROM (SELECT COUNT(p.id) AS ct FROM user AS u,post AS p WHERE u.id=p.user_id GROUP BY u.name ) AS c

) ORDER BY u.name;

16. Write a query to display the name(s) of the alumni user (Role-'Alumni') from 2008 batch who has/have posted the maximum number of posts, sorted by name.

SELECT u.name

FROM user as u,post as p,profile as pf,role as r

WHERE u.role_id=r.id AND r.name='Alumni' AND p.user_id=u.id AND u.profile_id=pf.id AND pf.batch='2008' GROUP BY u.name HAVING COUNT(p.id)>=3

(17)

17. Write a query to display the name(s) of the alumni user (Role-'Alumni') from 2008 batch CSE department who has/have posted the maximum number of posts, sorted by name.

SELECT u.name

FROM user as u, profile as p, post as ps, degree as d, department as dm,role as r

WHERE u.role_id = r.id AND r.name = 'Alumni' AND u.profile_id = p.id AND p.batch = '2008' AND p.degree_id = d.id AND d.department_id = dm.id

AND dm.name = 'CSE' AND u.id = ps.user_id GROUP BY u.id HAVING COUNT(ps.user_id)>= 3

ORDER BY u.name;

18. Write a query to display the name(s) of the user who has/have organised the maximum number of events, sorted by name.

SELECT u.name

FROM user as u, event as e WHERE u.id = e.organiser_id

GROUP BY u.name HAVING COUNT(e.organiser_id) = 5 ORDER BY u.name;

19. Write a query to display the names of the user and the number of events each user has organized (Give an alias as event_count), sorted by name of the user. Display only the names of users who have organized atleast one event.

SELECT u.name, COUNT(e.organiser_id) as event_count FROM user as u, event as e

WHERE u.id = e.organiser_id

GROUP BY u.name HAVING COUNT(e.organiser_id) >= 1 ORDER BY u.name;

20. Write a query to display the names of the user and the number of events each user has organized (Give an alias as event_count), sorted by name of the user. Display the names of all users.

SELECT u.name, COUNT(e.organiser_id) as event_count FROM user as u

LEFT OUTER JOIN event as e ON u.id = e.organiser_id GROUP BY u.name ORDER BY u.name;

21. Write a query to display the batch and the number of users who have been or are a part of 'TCS' (Give an alias as TCS_count), sorted by batch. Display only batches in which TCS_count is atleast 1.

SELECT p.batch, COUNT(e.profile_id) as TCS_count FROM profile as p, experience as e

WHERE p.id = e.profile_id AND e.company_name = 'TCS'

GROUP BY p.batch HAVING COUNT(e.profile_id) >= 1 ORDER BY p.batch;

22. Write a query to display the name(s) of the user who has/have raised/answered most number of queries, sorted by name.

SELECT u.name

FROM user as u, query as q WHERE u.id = q.user_id

GROUP BY u.name HAVING COUNT(q.user_id) = 6 ORDER BY u.name;

23. Write a query to display the name(s) of the user who has/have answered most number of queries, sorted by name.

SELECT u.name

FROM user as u, query as q WHERE u.id = q.user_id

GROUP BY u.name HAVING COUNT(q.user_id) = 2 ORDER BY u.name;

24. Write a query to display the name(s) of the user and the batch of the user who has/have raised/answered most number of queries, sorted by name.

SELECT u.name, p.batch

FROM user as u, query as q, profile as p WHERE u.id = q.user_id AND u.profile_id = p.id GROUP BY u.name HAVING COUNT(q.user_id) = 6 ORDER BY u.name;

25. Write a query to display the name(s) of the user and the department of the user who has/have raised/answered most number of queries, sorted by name. Use column alias name as USER_NAME and DEPT_NAME

SELECT user_name, dept_name FROM

(SELECT name as user_name, profile_id,id FROM user) as u JOIN profile ON u.profile_id=profile.id

JOIN degree ON profile.degree_id=degree.id

JOIN (SELECT name as dept_name,id FROM department)as d ON d.id=degree.department_id JOIN query ON query.user_id=u.id

GROUP BY user_name

having COUNT(query.content)=6 ORDER BY user_name;

(18)

--- <<<<<<<<< SESSION 9 >>>>>>>>>>>

--- 1) Write a query to display the names of all administrators (role Admin) sorted by name in Ascending order.

SELECT user.name FROM user, role

WHERE role.id=user.role_id AND role.name='admin' ORDER BY user.name ASC;

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

SELECT user.name FROM user,role

WHERE role.id=user.role_id AND role.name='alumni' ORDER BY user.name DESC;

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

SELECT degree.name FROM degree, department

WHERE degree.department_id = department.id AND department.name='CSE' ORDER BY degree.name ASC;

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

FROM degree, department

WHERE degree.department_id = department.id AND degree.name='BSC_CT';

5) Write a query to display the names of all female alumni (role Alumni), sorted in order. SELECT user.name FROM user,role,profile

WHERE role.id=user.role_id AND user.profile_id=profile.id AND role.name='alumni' AND profile.gender='female' ORDER BY user.name ASC;

6) Write a query to display the department of user Ram in the college. SELECT department.name

FROM user, degree, department, profile

WHERE profile.degree_id = degree.id AND degree.department_id = department.id AND user.profile_id = profile.id AND user.name='Ram'

7) Write a query to display the university name(s) in which Ram has done his higher studies, sorted in Ascending order.

SELECT higher_degree.university_name FROM user,higher_degree,profile

WHERE profile.id=higher_degree.profile_id AND user.profile_id=profile.id AND user.name='Ram'

8) Write a query to display the designaton of Ram. SELECT profile.designaton

FROM user,profile

WHERE user.profile_id=profile.id AND user.name='Ram';

9) Write a query to display the name of the skillsets of Ram, sorted by name in Ascending order.

SELECT skill.name

FROM user,profile,profile_skills,skill

WHERE user.profile_id=profile.id AND profile_skills.profile_id=profile.id AND profile_skills.skill_id=skill.id AND user.name='ram' ORDER BY skill.name ASC

10) Write a query to display the work experience details of Anithaa (company name, start date and end date in order), sorted by start date.

SELECT e.company_name,e.start,e.end FROM user as u,profile as p,experience as e

WHERE u.profile_id=p.id AND e.profile_id=p.id AND u.name='Anithaa' ORDER BY e.start

11) Write a query to display the names of projects in which Ram has been a part of, sorted in order.

SELECT pj.name

FROM user as u,profile as p,project as pj

WHERE u.profile_id=p.id AND pj.profile_id=p.id AND u.name='Ram' ORDER BY pj.name

12) Write a query to display the contents of queries raised or answered by Ram, sorted by date.

SELECT query.content

FROM user,query WHERE query.user_id=user.id AND user.name='Ram' ORDER BY query.date

13) Write a query to display the contents of posts posted by Ram, sorted by date. SELECT post.content

(19)

FROM user,post

WHERE post.user_id=user.id AND user.name='Ram' ORDER BY post.date

14) Write a query to display the contents of posts related to Technology posted by Ram, sorted by date.

SELECT post.content FROM user,post,post_type

WHERE post.user_id=user.id AND post.type_id=post_type.id AND user.name='Ram' AND post_type.name='Technology'

ORDER BY post.date

15) Write a query to display the contents of posts posted by Ram in the year 2012, sorted by date.

SELECT post.content FROM user,post

WHERE post.user_id=user.id AND user.name='Ram' AND YEAR(post.date)='2012' ORDER BY post.date

16) Write a query to display the name and phone numbers of all Alumni users from 2008 batch, sorted in Ascending order based on name.

SELECT u.name,u.phonenumber FROM user as u,role,profile

WHERE u.profile_id=profile.id AND u.role_id=role.id AND profile.batch=2008 AND role.name='Alumni'

ORDER BY u.name ASC;

17) Write a query to display the name of all male Alumni from 2008 batch, sorted in order. SELECT u.name

FROM user as u,role,profile

WHERE u.profile_id=profile.id AND u.role_id=role.id AND profile.batch=2008 AND role.name='Alumni' AND profile.gender='male'

18) Write a query to display the name of all Alumni from BSC_CT 2008 batch, sorted by name.

SELECT u.name

FROM user as u,role,profile,degree WHERE u.profile_id=profile.id AND u.role_id=role.id AND profile.degree_id=degree.id AND degree.name ='BSC_CT'AND

profile.batch=2008

AND role.name='Alumni' AND profile.gender='male' ORDER BY u.name ASC;

19) Write a query to display the name of all Alumni of 2008 batch from CSE department, sorted by name.

SELECT u.name

FROM user as u,role,profile,degree,department

WHERE u.profile_id=profile.id AND u.role_id=role.id AND profile.degree_id=degree.id AND degree.department_id=department.id AND department.name ='CSE' AND profile.batch=2008 AND role.name='Alumni'

20) Write a query to display the name and email details of all users from 2008 batch sorted by name.

SELECT user.name,user.emailid FROM user,profile

WHERE user.profile_id=profile.id AND profile.batch=2008 ORDER BY user.name;

21) Write a query to display the names of users who have good 'Web Design' skills, sorted by name.

SELECT user.name

FROM user,profile,profile_skills, skill

WHERE user.profile_id=profile.id AND profile_skills.profile_id=profile.id AND profile_skills.skill_id=skill.id AND skill.name='Web Design' ORDER BY user.name;

22) Write a query to display the names of all alumni users who have been or are a part of TCS, sorted by name.

SELECT user.name

FROM user,role,profile,experience

WHERE user.profile_id=profile.id AND user.role_id=role.id

AND experience.profile_id=profile.id AND role.name='alumni' AND experience.company_name='TCS'

ORDER BY user.name;

23) Write a query to display the names and email ids of all alumni users who are currently working in TCS, sorted by name.

SELECT user.name,user.emailid FROM user,role,profile,experience

WHERE user.profile_id=profile.id AND user.role_id=role.id AND experience.profile_id=profile.id

AND role.name='alumni' AND experience.company_name='TCS' AND experience.current='1' ORDER BY user.name;

24) Write a query to display the name and role of all users, sorted by name of the user. SELECT user.name,role.name

(20)

WHERE user.role_id=role.id ORDER BY user.name ASC;

25) Write a query to display the name and role details of all users who have filled in the profile details, sorted by name in Ascending order.

SELECT user.name,role.name FROM user,role,profile

WHERE user.role_id=role.id AND user.profile_id=profile.id AND profile.id IS NOT NULL ORDER BY user.name ASC;

--- <<<<<<<<< SESSION 10 >>>>>>>>>>>

--- 1) Write a query to display the names of users who have done their higher studies in 'Texas University', sorted in order.

SELECT name FROM user

WHERE profile_id IN (SELECT profile_id FROM higher_degree

WHERE university_name='Texas University') ORDER BY name;

2) Write a query to display the names of users from 2008 batch who have done their higher studies in 'Texas University', sorted in order.

WHERE university_name='Texas University') AND profile_id IN (SELECT id

FROM profile WHERE batch=2008) ORDER BY name;

3) Write a query to display the names of users from 2008 batch CSE department who have done their higher studies in 'Texas University', sorted in order.

WHERE university_name='Texas University') AND profile_id IN (SELECT id

FROM profile WHERE batch=2008 AND degree_id IN (SELECT id

FROM degree

WHERE department_id in (SELECT id

FROM department WHERE name='CSE') )

) ORDER BY name;

4) Write a query to display the names of users from 'MECH' department who have 'Programming' skills, sorted in order.

SELECT name

FROM user WHERE profile_id in (SELECT profile_id FROM profile_skills

WHERE profile_id IN (SELECT id FROM profile

WHERE degree_id in (SELECT id FROM degree

WHERE department_id IN (SELECT id FROM department

WHERE name='MECH') )

)

AND skill_id in (SELECT id FROM skill

WHERE name='Programming') )

ORDER BY name;

5) Write a query to display the names of users from 'MECH' department who are currently working in 'TCS', sorted in order.

WHERE profile_id in (SELECT profile_id FROM experience

WHERE profile_id IN (SELECT id FROM profile

WHERE degree_id IN (SELECT id FROM degree WHERE department_id IN (SELECT id FROM department WHERE name='MECH') ))

AND company_name='TCS' AND current=1) ORDER BY name;

6) Write a query to display the names of users who have been part of projects with more than 10 members, sorted in order.

(21)

WHERE profile_id IN (SELECT profile_id FROM project

WHERE number_of_members>10) ORDER BY name;

7) Write a query to display the names of users from 2008 batch who have raised or answered queries, sorted in order.

WHERE profile_id IN (SELECT id FROM profile WHERE batch=2008) AND id IN (SELECT user_id FROM query )

ORDER BY name;

8) Write a query to display the names of users who have raised queries, sorted in order. SELECT name FROM user

WHERE id in (SELECT user_id FROM query WHERE parent_id is null) ORDER BY name;

9) Write a query to display the names of users who have answered queries, sorted in order. SELECT name FROM user

WHERE id in (SELECT user_id FROM query WHERE parent_id is NOT NULL) ORDER BY name;

10) Write a query to display the names of users from 2008 batch who have raised queries, sorted in order.

WHERE id in (SELECT user_id FROM query WHERE parent_id is null ) AND profile_id in (SELECT id FROM profile WHERE batch=2008)

ORDER BY name;

11) Write a query to display the names of users from 2008 who have answered queries, sorted in order.

SELECT name FROM user WHERE id in (SELECT user_id FROM query WHERE parent_id is not null) AND profile_id in (SELECT id FROM profile WHERE batch=2008) ORDER BY name; 12) Write a query to display the names of users who have organised events, sorted in order.

WHERE id in (SELECT organiser_id FROM event) ORDER BY name;

13) Write a query to display the names of 'Alumni' users who have organised events, sorted in order.

WHERE id in (SELECT organiser_id FROM event)

AND role_id in (SELECT id FROM role WHERE name='Alumni') ORDER BY name;

14) Write a query to display the names of users who have organized events in the year 2013, sorted in order.

WHERE id in (SELECT organiser_id FROM event WHERE year(date)='2013') ORDER BY name;

15) Write a query to display the name, phone number and email id of organizer of 'ICSIP2008'.

SELECT name,phONenumber,emailid FROM user

WHERE id in (SELECT organiser_id FROM event WHERE name='ICSIP 2008');

16) Write a query to display the names of all users who have posted 'Technology' related posts in the year 2013, sorted by name in order.

WHERE id in (SELECT user_id FROM post WHERE type_id in (SELECT id FROM post_type WHERE name='Technology') AND year(date)='2013') ORDER BY name;

17) Write a query to display the names of users who are currently working in TCS as 'Project Manager's, sorted in order.

WHERE profile_id in (SELECT profile_id FROM experience

WHERE profile_id in (SELECT id FROM profile

WHERE designaton='Project Manager') AND company_name='TCS' AND current=1) ORDER BY name;

18) Write a query to display the names of users who have done their higher studies in other colleges or universities, sorted in order. [Hint : Details of higher studies done in other colleges / universities is specified in the higher_degree table].

WHERE profile_id IN (SELECT profile_id FROM higher_degree WHERE university_name IS NOT NULL) ORDER BY name

19) Write a query to display the names of users who have been working in TCS in the year 2012, sorted in order.