Question

Q1: The following question are based on these database tables:

EMPLOYEE

FNAME	MIDINT	LNAME	SSN	BDATE	ADDRESS	SEX	SALARY	SUPER_SSN	Dept_No
James	A	Borg	123123123		Houston, TX	M	55000	Null	1
Franklin	S	Wong	234234234		Houston, TX	M	40000	123123123	5
John	Q	Smith	345345345		Houston, TX	M	30000	234234234	5
Jennifer	L	Wallace	456456456		Bellaire, TX	F	43000	123123123	4
Alicia	M	Zalaya	567567567		Spring, TX	F	25000	456456456	4
Ramesh	R	Narayan	678678678		Humble, TX	M	38000	234234234	5
Joyce	V	English	7897789789		Houston, TX	F	25000	234234234	5
Ahmed	B	Jabbar	890890890		Houston, TX	M	25000	456456456	1

DEPARTMENT

Dept_No	Dept_Name
1	Headquarter
4	Administration
5	Research

Write out the SQL queries pertaining to the following questions. You must write the SQL query such that if they was copied and pasted on a database, the query would run.

1. Give me everything you have on employee.

2. Give me only the social security numbers, last names and the first names of all employees.

3. Show me all the salaries, but do not show me duplicate salaries figures.

4. Show me the SSN of employees that are paid 25, 000 in salary.

5. Show me the last name of all male employees

6. Show me the department number, the first and last name of all employees. Make sure that the department’s number are displayed in order from low to high.

7. Give me a list of all those employees whose last name is English and whose sex is female

8. Give me the total salary paid to all the employees who work in department 5

1. Give me average salary for each department.

10. Show me the employees’ first name, last name along with the department name they work for.

Q2:      In Chapter 4, you can see the creation of the Tiny College database design. That design reflected such business rules as “a professor may advise many students” and “a professor may chair one department.”  The diagram is on the next page of this document for your convenience.

Modify the ERD design  shown in Figure 4.35 (pg. 147)  to include these business rules:

• An employee could be staff or a professor or an administrator.
• A professor may also be an administrator.
• Staff employees have a work level classification, such a Level I and Level II.
• Only professors can chair a department. A department is chaired by only one professor.
• Only professors can serve as the dean of a college. Each of the university’s colleges is served by one dean.
• A professor can teach many classes.
• Administrators have a position title, e.g. Director, Provost, Dean.

Given that information, create the complete ERD containing all primary keys, foreign keys, and main attributes.

FIGURE4.35  THECOMPLETEDTINYCOLLEGEERD

Answer 1

Q1.

Give me everything you have on employee.

select * from EMPLOYEE

Give me only the social security numbers, last names and the first names of all employees.

select SSN,LNAME,FNAME from EMPLOYEE

Show me all the salaries, but do not show me duplicate salaries figures.

select DISTINCT SALARY from EMPLOYEE

Show me the SSN of employees that are paid 25, 000 in salary.

select SSN from EMPLOYEE where SALARY = 25000

Show me the last name of all male employees

select LNAME from EMPLOYEE where SEX = "M"

Show me the department number, the first and last name of all employees. Make sure that the department’s number are displayed in order from low to high.

select Dept_No,LNAME,FNAME from EMPLOYEE order by Dept_No ASC

Give me a list of all those employees whose last name is English and whose sex is female

select * from EMPLOYEE where LNAME = "English" and SEX = "F"

Give me the total salary paid to all the employees who work in department 5

select sum(SALARY) from EMPLOYEE where Dept_No = 5

Give me average salary for each department.

select Dept_No,avg(salary) from EMPLOYEE group by Dept_No

Show me the employees’ first name, last name along with the department name they work for.

select e.FNAME,e.LNAME,d.Dept_Name from EMPLOYEE E INNER JOIN DEPARTMENT D ON E.Dept_No = D.Dept_No