IT 117 SQL HW1&2

What are the advantages when you use a cloud server (which is a virtual machine server)?

(1) You only pay when you use it. (2) The configuration of the machine is easy to extend. (3) There is a specialized team to maintain the hardware system.

List two services you use and you can reasonably believe there is a database system behind to support the service.

Answer varies. For example, blackboard, the online banking, online game, etc.

Write down the SQL to delete the record with region_id=10 in the regions table.

DELETE FROM regions WHERE region_id=10;

Write down the SQL to insert a new record (region_id=10, region_name=moon) in the regions table

INSERT into regions (region_id, region_name) VALUES (10, 'moon');

What is SQL designed for?

It helps us to communicate with database system about what we want to achieve in one standard language. The language is independent from how our goal is achieved in different database systems.

Write down the SQL to show all the employee information for those whose department_id is 60. (Hint: 5 records)

SELECT * FROM employees WHERE department_id=60;

Write down the SQL to show the average salary of employees whose hire_date is between 01/01/2008 and 12/31/2008. (Hint: 1 record)

SELECT avg(salary) FROM employees where hire_date > to_date('01-01-2008', 'MM-DD-YYYY') AND hire_date < to_date('12-31-2008', 'MM-DD-YYYY');

Write down the SQL to show the average salary for each department together with its department_id. (Hint: 12 records)

SELECT department_id, avg(salary) FROM employees GROUP BY department_id;

For each department, show its department name and the country name of their depart ment. (hint: 27 records)

SELECT department_name, country_name FROM departments, locations, countries WHERE departments.location_id=locations.location_id AND locations.country_id= countries.country_id;

Although the departments table has 27 records, not every defined department has employees in it. Write down the SQL to show the unique department_id for all the departments which has employees in it. (Hint: 12 records)

SELECT distinct department_id FROM employees; You can also use the following: SELECT department_id FROM employees GROUP BY department_id;

Write down the SQL to show the first name and last name for those employees satisfying one of the following conditions (1) hire_date is between 01/01/2008 and 12/31 /2008, (2) salary is above 8000. (Hint: 43 records)

SELECT employees WHERE (hire_date > to_date('01-01-2008', 'MM-DD-YYYY') AND hire_date < to_date('12- 31-2008', 'MM-DD-YYYY')) OR salary>8000;

Write down the SQL to show the full name (in one attribute) of employees and their related department name. If an employee has no department, we will still have the record to show the employee name without a related department name. (hint: 107 records)

SELECT first_name || ' ' || last_name as FullName, department_name FROM employees, departments WHERE employees.department_id=departments.department_id(+);

Write down the SQL to show the full name (in one attribute) of employees an d their related department name. If an employee has no department, we will not show this employee record. (hint: 106 records)

SELECT first_name || ' ' || last_name as FullName, department_name FROM employees, departments WHERE employees.department_id=departments.department_id;

Write down the SQL to show the full name (in one attribute) and hire_date of employees, and sort records according to hire_date in a descending order. (Hint: 107 records)

SELECT first_name || ' ' || last_name as FullName, hire_date FROM employees ORDER BY hire_date desc;

Write down the SQL to update region_name to Pacific for the record with region_id=10 in the regions table.

UPDATE regions SET region_name='Pacific' WHERE region_id=10;