Practice

Writing SQL Statements

SQL statements are not case sensitive. SQL statements can be on one or more lines. Keywords cannot be abbreviated. Clauses are usually placed on separate lines. Indents are used to enhance readability. The statement ends with semicolon.

Practice

Show the structure of the DEPT table. Select all data

from the table.

Show the structure of the EMP table. Create a query

to display the name, job, hire date, and employee number for each employee.

Create a query to display unique jobs from the EMP

table.

Practice

Create a query to display the name and salary of

employees earning more than $2000.

Create a query to display the name and salary for all

employees whose salary is not in the range of $1000 and $2000.

Display the name and department number of all

employees in departments 20 and 30.

Display the name and hire date of every employee

who was hired in 1981. Sort the results by the most recently hired employee.
4

Practice

Display the name and job title of all employees who do

not have a manager.

For each employee, display the employee name, and

calculate the number of months between today and the date the employee was hired. Label the column MONTHS_WORKED. Round the number of months up to the closest whole number. Order your results by the number of months employed.

Practice

Display the highest, lowest, sum, and average salary of all employees. Display the minimum, maximum, sum, and average salary for each job type. Determine the number of managers without listing them. Label the column Number of Managers. Write a query that displays the difference between the highest and lowest salaries. Label the column DIFFERENCE.

Practice

Write a query to display the employee name,

department number, and department name for all employees who earn a commission and who have an A (uppercase) in their names.

Display the employee name and employee number

along with their managers name and manager number. Label the columns Employee, Emp#, Manager, and Mgr#, respectively.
7

Practice

Which clause will cause an error?

SELECT FROM WHERE id_number, description, price inventory manufacturer_id IN (SELECT FROM WHERE ORDER BY
8

manufacturer_id inventory price > 8.00 order_date);

Practice

What would cause this statement to fail?

SELECT FROM WHERE

*
student id = (SELECT id FROM AND student UPPER (last_name) = 'HENRY'); WHERE UPPER (first_name) = 'KATE'

Practice

Select the employees names with salaries less than the average salary. Display the name and job of employees working in the Accounting or Sales department.

10

Practice

Add one row of data to the yourname_employee table. 11, Jon, Adams, 2000 12, Alan, Robin, 800

Confirm your addition to the table.

11

Practice

Change the last name of employee 12 to James. Change the salary to 1000 for all employees with a
salary less than 900.

Delete all rows in yourname_employee table. Verify your changes to the table. Drop the yourname_employee table.

12

Practice

Create the dept1 table based on the following table

instance chart. Confirm that the table is created.
Column Name Data type Length id NUMBER 7 name VARCHAR2 25

Include only columns that you need.

Populate the dept1 table with data from the dept table.

13

Practice

Create the emp1 table based on emp table. Include empno as id, ename as employeename, deptno as dept_id. Confirm that the table is created.

14

Practice

Add a PRIMARY KEY constraint to the dept1 table on

the id column. The constraint should be named at creation. Name the constraint my_dept_id_pk.

Add a foreign key reference on the emp1 table on the

dept_id column that ensures that the employee is not assigned to a nonexistent department. Name the constraint my_emp_dept_id_fk.

15

Practice

Modify the emp1 table. Add a commission column of

NUMBER data type with precision 2.

Add a constraint to the commission column that

ensures that a commission value is greater than zero.

Drop the emp1 table.

16

Practice

Create a view named dept30_vu that contains the

employees numbers, names, and department numbers for all employees in department 30. Label the view columns empno, employee, and deptno.

Display the contents of the dept30_vu view.

17

Practice

Which SELECT statement would display the next value of the PARTS_ID sequence?

SELECT parts_id NEXTVAL FROM

SELECT FROM SELECT FROM SELECT FROM SELECT FROM

inventory;
NEXTVAL(parts_id) inventory; parts_id.NEXTVAL [Link]; parts_id.NEXTVAL inventory; NEXTVAL(parts_id) [Link];
18

Practice

Create a sequence named sequence1 to be used for

the primary key of the emp table. table.

Use the sequence1 sequence to insert one row in the

19