Example 1 use sample; insert into employee values (25348, 'Matthew', 'Smith','d3'); insert into employee values (10102, 'Ann', 'Jones','d3'); insert into employee values (18316, 'John', 'Barrimore', 'd1'); insert into employee values




Дата канвертавання16.04.2016
Памер15.56 Kb.
Example 7.1

USE sample;


INSERT INTO employee VALUES (25348, 'Matthew', 'Smith','d3');
INSERT INTO employee VALUES (10102, 'Ann', 'Jones','d3');
INSERT INTO employee VALUES (18316, 'John', 'Barrimore', 'd1');
INSERT INTO employee VALUES (29346, 'James', 'James', 'd2');
INSERT INTO employee VALUES (9031, 'Elsa', 'Bertoni', 'd2');
INSERT INTO employee VALUES (2581, 'Elke', 'Hansel', 'd2');
INSERT INTO employee VALUES (28559, 'Sybill', 'Moser', 'd1');

Example 7.2

USE sample;


INSERT INTO department VALUES ('d1', 'Research', 'Dallas');
INSERT INTO department VALUES ('d2', 'Accounting', 'Seattle');
INSERT INTO department VALUES ('d3', 'Marketing', 'Dallas');

Example 7.3

USE sample;


INSERT INTO project VALUES ('p1', 'Apollo', 120000.00);
INSERT INTO project VALUES ('p2', 'Gemini', 95000.00);
INSERT INTO project VALUES ('p3', 'Mercury', 186500.00);

Example 7.4

USE sample;


INSERT INTO works_on VALUES (10102,'p1', 'Analyst', '2006.10.1');
INSERT INTO works_on VALUES (10102, 'p3', 'Manager', '2008.1.1');
INSERT INTO works_on VALUES (25348, 'p2', 'Clerk', '2007.2.15');
INSERT INTO works_on VALUES (18316, 'p2', NULL, '2007.6.1');
INSERT INTO works_on VALUES (29346, 'p2', NULL, '2006.12.15');
INSERT INTO works_on VALUES (2581, 'p3', 'Analyst', '2007.10.15');
INSERT INTO works_on VALUES (9031, 'p1', 'Manager', '2007.4.15');
INSERT INTO works_on VALUES (28559, 'p1', 'NULL', '2007.8.1');
INSERT INTO works_on VALUES (28559, 'p2', 'Clerk', '2008.2.1');
INSERT INTO works_on VALUES (9031, 'p3', 'Clerk', '2006.11.15');
INSERT INTO works_on VALUES (29346, 'p1','Clerk', '2007.1.4');

Example 7.5

USE sample;


INSERT INTO employee VALUES (15201, 'Dave', 'Davis', NULL);

Example 7.6

USE sample;


INSERT INTO employee (emp_no, emp_fname, emp_lname)
            VALUES (15201, 'Dave', 'Davis');

Example 7.7

USE sample;


INSERT INTO employee (emp_lname, emp_fname, dept_no, emp_no)
            VALUES ('Davis', 'Dave', 'd1', 15201);

Example 7.8

USE sample;


CREATE TABLE dallas_dept
        (dept_no CHAR(4) NOT NULL,
         dept_name CHAR(20) NOT NULL);

INSERT INTO dallas_dept (dept_no, dept_name)


   SELECT dept_no, dept_name
        FROM department
       WHERE location = 'Dallas';

Example 7.9

USE sample;


CREATE TABLE clerk_t
    (emp_no INT NOT NULL,
     project_no CHAR(4),
     enter_date DATE);

INSERT INTO clerk_t (emp_no, project_no, enter_date)


   SELECT emp_no, project_no, enter_date
      FROM works_on
      WHERE job = 'Clerk'
      AND project_no = 'p2';

Example 7.10

USE sample;


INSERT INTO department VALUES ('d4', 'Human Resources', 'Chicago'),
('d5', 'Distribution', 'New Orleans'),
('d6', 'Sales', 'Chicago');

Example 7.11

USE sample;


UPDATE works_on
  SET job = 'Manager'
  WHERE emp_no = 18316
  AND project_no = 'p2';

Example 7.12

USE sample;


UPDATE project
   SET budget = budget*0.51;

Example 7.13

USE sample;


UPDATE works_on
     SET job = NULL
     WHERE emp_no IN
     (SELECT emp_no
         FROM employee
        WHERE emp_lname = 'Jones');

Example 7.14

USE sample;


UPDATE works_on
 SET job = NULL
  FROM works_on, employee
  WHERE emp_lname = 'Jones'
  AND works_on.emp_no = employee.emp_no;

Example 7.15

USE sample;


UPDATE project
 SET budget = CASE
         WHEN budget >0 and budget < 100000  THEN budget*1.2
         WHEN budget >= 100000 and budget < 200000  THEN budget*1.1
         ELSE budget*1.05
         END

Example 7.16

USE sample;


DELETE FROM works_on
  WHERE job = 'Manager';

Example 7.17

USE sample;


DELETE FROM works_on
   WHERE emp_no IN
   (SELECT emp_no
        FROM employee
       WHERE emp_lname = 'Moser');

DELETE FROM employee


   WHERE emp_lname = 'Moser';

Example 7.18

USE sample;


DELETE works_on
   FROM works_on, employee
   WHERE works_on.emp_no = employee.emp_no
   AND emp_lname = 'Moser';

DELETE FROM employee


   WHERE emp_lname = 'Moser';

Example 7.19

USE sample;


DELETE FROM works_on;

Example 7.20

USE sample;


DECLARE @del_table TABLE (emp_no INT, emp_lname CHAR(20));
DELETE employee
OUTPUT DELETED.emp_no, DELETED.emp_lname INTO @del_table
WHERE emp_no > 15000
SELECT * FROM @del_table

Example 7.21

USE sample;


DECLARE @update_table TABLE
(emp_no INT, project_no CHAR(20),old_job CHAR(20),new_job CHAR(20));
UPDATE works_on
SET job = NULL
OUTPUT DELETED.emp_no, DELETED.project_no,
DELETED.job, INSERTED.job INTO @update_table
WHERE job = 'Clerk'
SELECT * FROM @update_table

Example 7.22

USE sample;


CREATE TABLE bonus
(pr_no CHAR(4),
bonus SMALLINT DEFAULT 100);
INSERT INTO bonus (pr_no) VALUES ('p1');

Example 7.23

MERGE INTO bonus B


USING (SELECT project_no, budget
FROM project) E
ON (B.pr_no = E.project_no)
WHEN MATCHED THEN
UPDATE SET B.bonus = E.budget * 0.1
WHEN NOT MATCHED THEN
INSERT (B.pr_no, B.bonus)
VALUES (E.project_no, E.budget * 0.05);


База данных защищена авторским правом ©shkola.of.by 2016
звярнуцца да адміністрацыі

    Галоўная старонка