L4 #

# 1. Which students have an ID number that is less than 50000?

select student_id, student_name from Student where student_id < 50000;
# 2. What is the name of the faculty member whose ID is 4756?
select faculity_id, faculity_name from Faculity where faculity_id = 4756;
# 3. Which faculty members have qualified to teach a course since 1993? List the faculty ID, course
and
date of qualification.
select faculity_id, course_id, date_qualified from Qualified where date_qualified >= '1993-01-01';
# 4. What is the smallest section number used in the first semester of 2008?
SELECT min(section_no) FROM Section WHERE semester = 'I-2008';
# 5. How many students are enrolled in section 2714 during semester I-2008?
select distinct count(student_id) as 'Number Students in 2714 in I-2008' from Registration where
section_no = 2714 and seme
ster = 'I-2008';
# 6. List all students in alphabetical order by student_name.
select student_name from Student order by student_name;
# 7. List the students who are enrolled in each course in semester I-2008. Group the students by the
sections in which they are enrolled.
SELECT STUDENT.student_name, SECTION.semester, COURSE.course_name FROM STUDENT,
REGISTRATION, SECTION,
COURSE WHERE SECTI
ON.section_no = REGISTRATION.section_no AND STUDENT.student_id = REGISTRATION.student_id AND
COURSE.course_ID = SECTION.course_ID
group BY SECTION.section_no, student_name HAVING semester = 'I-2008';
# 8. Display the course ID and course name for all courses with an ISM prefix
select course_id, course_name from Course where course_id like 'ISM%';
## 9. How many tutors have a status of Temp Stop?
select count(tutor_id) as 'Number tutors status Temp Stop' from Tutor where status = 'Temp Stop';
# 10. Which student has the highest read score?
SELECT score, student_id FROM student WHERE score IN (SELECT MAX(score) FROM student);
L5
##
# 1. Which instructors are qualified to teach ISM3113?
select faculity_name as 'Instructors qualified to teach ISM3113' from Faculity, Qualified where
Faculity.faculity_id = Qualified.faculity_id and Qualified.course_id = 'ISM 3113';
# 2. Is any instructor qualified to teach ISM3113 and not qualified to teach ISM4930?
mysql> select distinct faculity_name as 'Instructors Qualified in ISM3113 and not ISM4930' from
Faculity,
-> Qualified where Faculity.faculity_id = Qualified.faculity_id and
-> course_id = 'ISM 3113' and not course_id = 'ISM 4930';
# 3. How many students are enrolled in ISM3113 during semester I-2008?
mysql> select distinct count(student_id) as 'No. Students enrolled in ISM3113 in I-2008' from
registration
-> where section_no in (select section_no from section where course_id = 'ISM 3113' and semester =
'I-2008');
# 4. Which students were not enrolled in any courses during semester I-2008?
mysql> select student_name from Student where not exists (select * from Registration
-> where Student.student_id = Registration.student_id and semester = 'I-2008');
# 5. Display all courses for which Professor Berndts has been qualified.
mysql> select Course.course_id, Course.course_name as 'Prof. Berndt\'s Qualified Courses' from
Course, Qualified, Faculity
-> where Faculity.faculity_id= Qualified.faculity_id and Course.course_id=Qualified.course_id
-> and Faculity.faculity_name="Berndt";
# 6. Display the class roster, including student name, for all students enrolled in section 2714 of ISM
4212.
mysql> select Student.student_id, student_name, Section.course_id, Registration.section_no
-> from Student, Registration, Section
-> where Section.course_id = "ISM 4212"
-> and Section.section_no= Registration.section_no

-> and Student.student_id= Registration.student_id

-> and Registration.section_no=2714
-> order by student_name;
# 7. Create a view that displays instructors who are qualified to teach ISM 3113.
mysql> create view `Qualified in ISM 3113` as
-> select faculity_name, Faculity.faculity_id from Faculity, Qualified
-> where Faculity.faculity_id = Qualified.faculity_id
-> and Qualified.course_id = "ISM 3113";
mysql> select * from `Qualified in ISM 3113`;
# 8. Create a view that lists the courses available. Group them by course prefix.
mysql> create view Courses_Available as
-> select course_name ,substr(course_id,1,3) prefix from Course
-> group by Course.course_id;
mysql> select * from Courses_Available;
# 1. Find the names, street address, and cities of residence of all employees who work for First Bank
Corporation and earn
more than $10,000.
mysql> select * from employee
-> where person_name in (select person_name from works
-> where company_name = 'First Bank Corporation' and salary >10000);
# 2. Find all employees in the database who live in the same cities as the companies for which they
work.
mysql> ## NOTE: modified/updated 2 persons (John & Mary) addresses to New York and Boston.
mysql> update employee set city='New York' where person_name='John';
mysql> update employee set city='Boston' where person_name='Mary';
mysql> select employee.person_name, employee.city from employee, works, company
-> where employee.person_name = works.person_name
-> and employee.city = company.city
-> and works.company_name = company.company_name;
# 3. Find all employees in the database who do not work for First Bank Corporation. People may have
jobs with more than one company.
mysql> select person_name from employee
-> where person_name not in (select person_name
-> from works where company_name = 'First Bank Corporation');
# 4. Find all employees in the database who earn more than every employee of Small Bank
Corporation.
mysql> select person_name from works
-> where salary > all(select salary from works
-> where company_name = 'Small Bank Corporation');
# 5. Find all employees who earn more than the average salary of all employees of their company.
mysql> select person_name, company_name from works WPerson where salary > (select avg(salary)
from works WSalary
-> where WPerson.company_name = WSalary.company_name);
mysql> ## 6. Find the company that has the smallest payroll.
mysql> select company_name, sum(salary) as Total_Salary from works
-> group by company_name having sum(salary) <= all(select sum(salary) from works
-> group by company_name);
mysql> ## 7. Find the company that has the most employees.
mysql> select company_name from works
-> group by company_name having
-> count(distinct person_name) >= all (select count(distinct person_name)
-> from works group by company_name);
mysql> ## 1. Find the total number of people who owned cars that were involved in accidents in 1989.
mysql> select count(distinct name) as Total_Car_Owners_in_Accidents from accident, participated,
person
-> where accident.report_number = participated.report_number
-> and participated.driver_id = person.driver_id
-> and acc_date >= '1989-00-00'
-> and acc_date <= '1989-12-31';
mysql># 2. Find the number of accidents in which the cars belonging to John Smith were involved.
mysql> select count(*) as John_Smith_Cars_in_Accidents from person, accident, owns, participated
-> where accident.report_number = participated.report_number
-> and participated.license = owns.license
-> and owns.driver_id = person.driver_id

-> and person.name like "John Smith";