30 INSERT Statements

• ·         Someone doing “real work” with the computer, using it as a means rather than an end-User
• ·         Consists of a collection of DML statements that form a logical unit of work.-transaction
• ·         Fully and clearly expressed; leaving nothing implied-explicit
• ·         Adds a new row to a table-INSERT INTO

I should execute DESC tablename before doing INSERT to view the data types for each column. VARCHAR2 data-type entries need single quotation marks in the VALUES statement.

1.      Give two examples of why it is important to be able to alter the data in a database.

·         I am on a flight booking site. It shows available flights, say I try to book a ticket and still my transaction is not commenced anywhere, I will be in big trouble.

·         I am trying to create a login on a site, it takes my details and say never store my information in registration request table, I will keep on waiting for approval which will never happen.

2.  DJs on Demand just purchased four new CDs.  Use an explicit INSERT statement to add each CD to the copy_d_cds table. After completing the entries, execute a SELECT * statement to verify your work.

CD_Number	Title	Producer	Year
97	Celebrate the Day	R & B Inc.	2003
98	Holiday Tunes for All Ages	Tunes are Us	2004
99	Party Music	Old Town Records	2004
100	Best of Rock and Roll	Old Town Records	2004

Create copy of main table to play around:

CREATE TABLE copy_d_cds

AS ( SELECT * FROM d_cds);

see if copy worked well:

DESCRIBE copy_d_cds;

DESCRIBE d_cds;

(clone table lost its primary key constraint and cd_number became nullable.)

see how the copied content looks like.

SELECT * FROM copy_d_cds ;

I see that cd_number 98 will be repeated with my insert statements, but it won't give error, since primary key constrain is lost in copy.

The Explicit insert statements without even missing nullable columns:

INSERT INTO copy_d_cds(cd_number,title,producer,year)

VALUES(97,'Celebrate the Day','R & B Inc.','2003');

INSERT INTO copy_d_cds(cd_number,title,producer,year)

VALUES(98,'Holiday Tunes for All Ages','Tunes are Us','2004');

INSERT INTO copy_d_cds(cd_number,title,producer,year)

VALUES(99,'Party Music','Old Town Records','2004');

INSERT INTO copy_d_cds(cd_number,title,producer,year)

VALUES(100,'Best of Rock and Roll','Old Town Records','2004');

SELECT * FROM copy_d_cds ;

3.   DJs on Demand has two new events coming up. One event is a fall football party and the other event is a sixties theme party. The DJs on Demand clients requested the songs shown in the table for their events. Add these songs to the copy_d_songs table using an implicit INSERT statement.

ID	Title	Duration	Type_Code
52	Surfing Summer	Not known	12
53	Victory Victory	5 min	12

Create copy of main table to play around:

CREATE TABLE copy_d_songs

AS ( SELECT * FROM d_songs);

see if copy worked well:

DESCRIBE copy_d_songs;

DESCRIBE d_songs;

(Also, I see that duration & artist is nullable in source table. I could skip these column names in insert into / if I decide to include these columns, I will specify it as NULL in VALUES. I will prefer the later option to be symmetric and avoid missing something by mistake. One more thing the clone table lost its primary key constraint and id became nullable.)

The Explicit insert statements without even missing nullable columns:

INSERT INTO copy_d_songs (id,title,duration,artist,type_code)

VALUES(52,'Surfing Summer',NULL,NULL,12);

INSERT INTO copy_d_songs (id,title,duration,artist,type_code)

VALUES(53,'Victory Victory','5 min',NULL,12);

But, problem specifically says to use implicit insert statement:

INSERT INTO copy_d_songs

VALUES(52,'Surfing Summer',NULL,NULL,12);

INSERT INTO copy_d_songs

VALUES(53,'Victory Victory','5 min',NULL,12);

SELECT * FROM copy_d_songs ;

4.  Add the two new clients to the copy_d_clients table. Use either an implicit or an explicit INSERT.

Client_Number	First_Name	Last_Name	Phone	Email
6655	Ayako	Dahish	3608859030	dahisha@harbor.net
6689	Nick	Neuville	9048953049	nnicky@charter.net

a) Create copy of main table to play around:

CREATE TABLE copy_d_clients

AS ( SELECT * FROM d_clients);

b) see if copy worked well:

DESCRIBE copy_d_clients ;

DESCRIBE d_clients;

(clone table lost its primary key constraint and client_number became nullable.)

SELECT * FROM d_clients ;

SELECT * FROM copy_d_clients ;

c) The Explicit insert statements without even missing nullable columns:

INSERT INTO copy_d_clients(client_number,first_name,last_name,phone,email)

VALUES(6655,'Ayako','Dahish',3608859030,'dahisha@harbor.net');

INSERT INTO copy_d_clients(client_number,first_name,last_name,phone,email)

VALUES(6689,'Nick','Neuville',3608859030,'nnicky@charter.net');

d) verify data:

SELECT * FROM copy_d_clients ;

5.   Add the new client’s events to the copy_d_events table. The cost of each event has not been determined at this date.

ID	Name	Event_ Date	Description	Cost	Venue_ID	Package_ Code	Theme_ Code	Client_ Number
110	Ayako Anniversary	07-Jul-2004	Party for 50, sixties dress, decorations		245	79	240	6655
115	Neuville Sports Banquet	09-Sep-2004	Barbecue at residence, college alumni, 100 people		315	87	340	6689

a) Create copy of main table to play around:

CREATE TABLE copy_d_events

AS ( SELECT * FROM d_events);

b) see if copy worked well:

DESCRIBE copy_d_events ;

DESCRIBE d_events;

(

All the constraints are lost in this copy.e.g.:

·         clone table lost its primary key constraint and id became nullable.

·         245 and 315 venue_id are not there in d_venues, but it insert will still work. [This loss of foreign key constraint is not mentioned by DESCRIBE]

one more thing:

·         cost is not nullable and it needs to be a number. I could either make it nullable, or give some value like 0 to cost. I go with second choice - give value as zero.

)

SELECT * FROM d_events ;

SELECT * FROM copy_d_events ;

c) The Explicit insert statements:

INSERT INTO copy_d_events(id,name,event_date,description,cost,venue_id,package_code,theme_code,client_number)

VALUES(110,'Ayako Anniversary',TO_DATE('07-Jul-2004','dd-Mon-yyyy'),'Party for 50, sixties dress, decorations',0,245,79,240,6655);

INSERT INTO copy_d_events(id,name,event_date,description,cost,venue_id,package_code,theme_code,client_number)

VALUES(115,'Neuville Sports Banquet',TO_DATE('09-Sep-2004','dd-Mon-yyyy'),'Barbecue at residence, college alumni, 100 people',0,315,87,340,6689);

d)verify data:

SELECT * FROM copy_d_events ;

6.  Create a table called rep_email using the following statement:

CREATE TABLE rep_email (

 id NUMBER(2) CONSTRAINT rel_id_pk PRIMARY KEY,

first_name VARCHAR2(10),

 last_name VARCHAR2(10),

email_address VARCHAR2(10))

Populate this table by running a query on the employees table that includes only those employees who are REP’s.

Those employees could be Marketing Representative, or Sales Representative. There JOB_ID ends with '_REP'

DESCRIBE rep_email ;

DESCRIBE employees;

Please note, employee_id has precision 6 and scale 0. But id in problem statement has 2, 0

Similarly other fields also have differences.

Expected to see errors like ORA-01438: value larger than specified precision allowed for this column

Luckily, rest of the mismatches still work because data is ok, but for id, I will have alter it:

ALTER TABLE rep_email DROP column id;

ALTER TABLE rep_email ADD id NUMBER(6,0) CONSTRAINT rel_id_pk PRIMARY KEY;

DESCRIBE rep_email ;

SELECT employee_id, first_name, last_name, email

FROM employees

WHERE job_id LIKE '%\_REP' ESCAPE '\';

INSERT INTO rep_email(id, first_name, last_name, email_address)

SELECT employee_id, first_name, last_name, email

FROM employees

WHERE job_id LIKE '%\_REP' ESCAPE '\';

SELECT * FROM rep_email;

29 Correlated Subqueries

1.  Explain the main difference between correlated and non-correlated subqueries ? 

As explained in Multiple-Row Subqueries Practice > Problem 3:

Correlated subquery is executed multiple times once for each intermediate result row from outer query And for each row of the intermediate results inner correlated subquery is executed, if the inner correlated subquery is satisfied, that row becomes part of final results from the whole query. A correlated subquery will get a candidate row from an outer query, execute the inner query using candidate row value, and use values from the inner query to qualify or disqualify the candidate row.

Non-correlated subquery: result/s of inner query are calculated first and reused throughout the execution of outer query.

2.  Write a query that lists the highest earners for each department. Include the last_name, department_id, and the salary for each employee.

WITH highs AS ( SELECT NVL(department_id,-1), MAX(salary) FROM employees GROUP BY NVL(department_id,-1))

SELECT last_name, department_id, salary

FROM employees

WHERE (NVL(department_id,-1), salary) IN (SELECT * FROM highs );

or

SELECT oe.last_name, oe.department_id, oe.salary

FROM employees oe

WHERE oe.salary = (SELECT MAX(ie.salary) FROM employees ie WHERE NVL(ie.department_id,-1) = NVL(oe.department_id,-1));

LAST_NAME	DEPARTMENT_ID	SALARY
King	90	24000
Whalen	10	4400
Higgins	110	12000
Abel	80	11000
Grant		7000
Mourgos	50	5800
Hunold	60	9000
Hartstein	20	13000

3.  Examine the following select statement and finish it so that it will return the last_name, department_id, and salary of employees who have at least one person reporting to them. So we are effectively looking for managers only. In the partially written SELECT statement, the WHERE clause will work as it is. It is simply testing for the existence of a row in the subquery.

SELECT (enter columns here)

FROM (enter table name here) outer

WHERE 'x' IN (SELECT 'x'

FROM (enter table name here) inner

WHERE inner(enter column name here) = inner(enter column name here)

Finish off the statement by sorting the rows on the department_id column.

SELECT outer.last_name, outer.department_id, outer.salary

FROM employees outer

WHERE outer.employee_id  IN (SELECT DISTINCT inner.manager_id

FROM employees  inner

WHERE inner.manager_id = outer.employee_id)

ORDER BY outer.department_id;

OR

SELECT outer.last_name, outer.department_id, outer.salary

FROM employees outer

WHERE outer.employee_id IN (SELECT DISTINCT inner.manager_id

FROM employees  inner

WHERE inner.manager_id IS NOT NULL)

ORDER BY outer.department_id;

4.  Using a WITH clause, write a SELECT statement to list the job_title of those jobs whose maximum salary is more than half the maximum salary of the entire company. Name your subquery MAX_CALC_SAL. Name the columns in the result JOB_TITLE and JOB_TOTAL, and sort the result on JOB_TOTAL in descending order.

Hint: Examine the jobs table. You will need to join JOBS and EMPLOYEES to display the job_title.

If I ignore the hint and job_total, which is a very ambiguous requirement here:

WITH max_calc_sal as (SELECT MAX(max_salary)/2 FROM jobs)

SELECT job_title

FROM jobs

WHERE jobs.max_salary > (SELECT * FROM max_calc_sal );

JOB_TITLE

President

Administration Vice President

But if I consider hints, I think might be job_total is not a total, but should have been called job_actual_max. Now there is a possibility that a job is there in job table but no employee is there for that job. For such case actual max could be 0.

WITH max_calc_sal AS (SELECT jobs.job_id , jobs.job_title, MAX(NVL(employees.salary,0)) AS job_actual_max FROM employees RIGHT OUTER JOIN   jobs  ON  employees.job_id =  jobs.job_id    GROUP BY jobs.job_id,jobs.job_title)

SELECT job_title, job_actual_max AS  job_total

FROM max_calc_sal

WHERE job_actual_max > (SELECT MAX(job_actual_max)/2 FROM max_calc_sal)

ORDER BY job_total  DESC;

JOB_TITLE	JOB_TOTAL
President	24000
Administration Vice President	17000
Marketing Manager	13000