SQL: Start with the Employee table used in Lab 4. CREATE TABLE Employee ( EMPLOYEE_ID INT NOT NULL, FIRST_NAME CHAR(20), LAST_NAME CHAR(20), JOB_LEVEL INT, SALARY BIGINT, COMMISSION_PCT INT, CONSTRAINT PKEmployeeID PRIMARY KEY (EMPLOYEE_ID) ); INSERT INTO Employee VALUES (100,'Steven','King',1,50000,10), (101,'Neena','Kochhar',1,50000,10), (102,'Lex','De Haan',2,60000 ,15), (103,'Alexander','Hunold',2,60000,15), (104,'Bruce','Ernst',3,70000,25), (105,'Johann','Ernst',4,75000,30); Create another table. CREATE TABLE NumEmployeesByLastName ( LastName CHAR(20), NumEmployees INT ); a. Write a query to initialize the NumEmployeesByLastName table by inserting one row for each last name already in the Employee table, and having the accurate count of the number of employees having that last name. Assume that there are no null values in the Employee table. After your query, if you execute the following query, you would get the following output. SELECT * FROM NumEmployeesByLastName; LastName NumEmployees King 1 Kochhar 1 De Haan 1 Hunold 1 Ernst 2
SQL:
Start with the Employee table used in Lab 4.
CREATE TABLE Employee
(
EMPLOYEE_ID INT NOT NULL,
FIRST_NAME CHAR(20),
LAST_NAME CHAR(20),
JOB_LEVEL INT,
SALARY BIGINT,
COMMISSION_PCT INT,
CONSTRAINT PKEmployeeID PRIMARY KEY (EMPLOYEE_ID)
);
INSERT INTO Employee
VALUES
(100,'Steven','King',1,50000,10),
(101,'Neena','Kochhar',1,50000,10),
(102,'Lex','De Haan',2,60000 ,15),
(103,'Alexander','Hunold',2,60000,15),
(104,'Bruce','Ernst',3,70000,25),
(105,'Johann','Ernst',4,75000,30);
Create another table.
CREATE TABLE NumEmployeesByLastName
(
LastName CHAR(20),
NumEmployees INT
);
a. Write a query to initialize the NumEmployeesByLastName table by inserting one row for each last name already in the Employee table, and having the accurate count of the number of employees having that last name. Assume that there are no null values in the Employee table.
After your query, if you execute the following query, you would get the following output.
SELECT * FROM NumEmployeesByLastName;
|
LastName |
NumEmployees |
|
King |
1 |
|
Kochhar |
1 |
|
De Haan |
1 |
|
Hunold |
1 |
|
Ernst |
2 |
b. Write a trigger to update NumEmployeesByLastName whenever a new record is inserted into the Employee table.
After your trigger is created, if you execute the following queries, you will get the following table.
SET SQL_SAFE_UPDATES = 0;
INSERT INTO Employee VALUES (200, 'Ankit', 'Pattan', 3, 70000, 25);
INSERT INTO Employee VALUES (201, 'Amy', 'King', 3, 70000, 25);
SELECT * FROM NumEmployeesByLastName;
|
LastName |
NumEmployees |
|
King |
2 |
|
Kochhar |
1 |
|
De Haan |
1 |
|
Hunold |
1 |
|
Ernst |
2 |
|
Pattan |
1 |
c. Create a procedure FindCommissionDiff that takes 2 employee ids as input. The procedure returns the difference between the commission of the two employees as output. The output parameter is set to the commission of employee1 - the commission of employee2.
After you define the procedure, if you execute these queries, you get the output -5.
CALL FindCommissionDiff(101, 102, @df);
SELECT @df;
Trending now
This is a popular solution!
Step by step
Solved in 2 steps with 1 images
