SQL PRACTICE
Write the SQL syntax based on the tables below.
Field Name
Constraint
Copyright By PowCoder代写 加微信 powcoder
varchar(15)
varchar(15)
varchar(30)
decimal(10,2)
Foreign (Ssn in Employee)
Foreign (Dnumber in Department)
Department
Field Name
Constraint
varchar(15)
not null, unique
Foreign (Ssn in Employee)
Mgr_start_date
Dept_Locations
Field Name
Constraint
foreign (Dnumber in Department)
varchar(15)
Field Name
Constraint
varchar(15)
not null, unique
varchar(15),
Foreign (Dnumber in Department)
Field Name
Constraint
Primary, Foreign (Ssn in Employee)
Primary, Foreign (Pnumber in Project)
decimal(3,1)
Field Name
Constraint
Dependent_name
varchar(15)
Relationship
varchar(8)
The Tables’ Data
1965-01-09
731 Fondren, Houston, TX
1955-12-08
638 Voss, Houston, TX
1972-07-31
5631 Rice, Houston, TX
1962-09-20
975 Fire Oak, Humble, TX
1937-11-10
450 Stone, Houston, TX
1941-06-20
291 Berry, Bellaire, TX
1969-03-29
980 Dallas, Houston, TX
1968-01-19
3321 Castle, Spring, TX
Department
Mgr_start_date
Headquarters
1981-06-19
Administration
1995-01-01
1988-05-22
Dept_Locations
Computerization
Reorganization
Newbenefits
Dependent_name
Relationship
1988-12-30
1967-05-05
2000-01-01
1988-01-04
1986-04-05
1958-05-03
1983-10-25
1942-02-28
1. List the complete records for all employees earning $35,000 or more per year.
2. List just the first name, middle initial and last name for employees earning $35,000 or more per year.
3. List the first and last names and social security numbers for everyone working for the supervisor with social security number 333-44-5555.
4. List the names of the departments with locations in Houston.
5. List the first and last names of the employees working on Product Y.
6. List the names of ‘s dependents and their relation to him.
7. List the full names and salaries for everyone working for .
8. List the full names and salaries for every in the Research Department.
9. List the average salaries for employees working for each of the departments.
10. List the total number of employees working on the ProductX project.
11. List the total number of employees working on each project.
12. List the first name, last name, Supervisor’s first and last name, Departmnt number and the average salary for everyone in Administration
13. For every the employee with a dependent, list the dependent’s first name, the employee’s last name, and relation to the employee.
14. List the name of employees with a dependent named “Alice” and the relationship to the employee.
15. List everyone who works in Research (name and salary).
16. For each project, list project name, location, department and the superior of everyone working on that project.
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com