Lecture 1: Basic SQL DML Statements
Preparation: Creating sample Oracle tables using scripts from script loader on ecourse.org and Lookup tables using dictionaries (user_tables, user_constraints, user_cons_columns)
Running an Oracle Script. Running a few scripts and then use dictionary tables to find out the database tables and constraints created by the scripts
select, delete, update, insert: understand what each does and memorize the basic syntax for each command
SELECT columns (separated by commas)
FROM tables (separated by commas)
WHERE conditions (apply to individual records)
UPDATE table_name
SET col1=exp1, col2=exp2,….coln=expn
WHERE conditions (apply to individual rows)
INSERT into table_name (col-1, col-2 ….., col-n)
VALUES (val-1, val-2, …..,val-n)
DELETE FROM table_name
WHERE conditions (apply to individual rows)
Examples: Basic Select Statements
Example 1: Find all employees whose salary is more than 800
Example 2: Find all employees who were hired after 1980
Example 3: Find all employees who never received commissions.
Example 4: Find those salesmen who were hired before 1983
Example 5: Find all distinct job titles
Example 6: Find those employees whose name starts with K
Example 7: Finds all managers whose salary is more than 200
Example 8: find all analysts whose name ends with T
Additional Exercises:
- The company just started a new marketing department in Chicago. The department ID is 50. Insert a new record into the database.
- Create a new employee for SARA with job title CLERK and salary 2500 in Department 20
- Delete salesmen who made less than 100 commissions
- Change Adams’ name to ADAM
- Find all analysts whose name ends with T
Homework:
Reading: Chapter 15
Design:
Correctness Questions (due on 10/21): Online at ecourse.org
Closeness Questions (due on 10/28): Write SQL statements for the following problems:
- Give all salesmen 100 dollars of commission if they never received one before
- Find those employees who was hired between January 1, 1980 and July 1, 1983
- Delete those managers who were hired before 1979
- Insert a record for a new saleswoman SUSAN who was hired on February 27, 1986. Her initial salary is 1400 dollars. She has been assigned with an employee number 8045
- The company is to create "Management" department in Akron. Use a sequence that starts from 40 and auto increment by 10 to generate the department.
- Find the total number of employees who have never received commissions
- Find the department that has less than 5 employees
- Find the years that company hired more than 5 employees
- Find the location that has more than 3 employees
- Find all the employees located in Dallas
|
