create database DML_OPER_FUN use DML_OPER_FUNcreate table EMPLOYEE(EMP_NUM char(3) not null,EMP_LNAME varchar(15) not null,EMP_FNAME varchar(15) not null,EMP_INITIAL char(1) not null,EMP_HIREDATE date not null,JOB_CODE char(3) not null ,)create table JOB (JOB_CODE char(3) not null primary key clustered,JOB_DESCRIPTION VARCHAR(15) not null,JOB_CHG_HOUR float not null,JOG_LAST_UPDATE DATE not null,)alter table EMPLOYEEADD PRIMARY KEY(EMP_NUM)alter table EMPLOYEEadd foreign key (JOB_CODE) references JOB(JOB_CODE)create table ASSIGNMENT (ASSING_NUM INT not null,ASSING_DATE DATE not null,PROJ_NUM CHAR(3),EMP_NUM CHAR(3),ASSING_JOB CHAR(3),ASSING_CHG_HR float,ASSING_HOURS float,ASSING_CHARGE float,)ALTER TABLE ASSIGNMENTADD PRIMARY KEY(ASSING_NUM)ALTER TABLE ASSIGNMENTADD FOREIGN KEY(EMP_NUM) REFERENCES EMPLOYEE (EMP_NUM)create table PROJECT(PROJ_NUM char(3) not null,PROJ_NAME VARCHAR(15) NOT NULL,PROJ_VALUE MONEY,PROJ_BALANCE MONEY,EMP_NUM char(3),)alter table PROJECTadd primary key(PROJ_NUM)ALTER TABLE ASSIGNMENTADD FOREIGN KEY(PROJ_NUM ) REFERENCES PROJECT (PROJ_NUM)alter table PROJECTadd foreign key (EMP_NUM) references EMPLOYEE(EMP_NUM) insert into ASSIGNMENT(ASSING_NUM, ASSING_DATE, ASSING_JOB, ASSING_CHG_HR,ASSING_HOURS)VALUES ('1001', '22-Mar-10', '503', '84.50', '3.5')-- Problema 1. Write the SQL code that will create the table structure for a table named EMP_1.-- This table is a subset of the EMPLOYEE table. The basic EMP_1 table structure is -- summarized in the following table. (Note that the JOB_CODE is the FK to JOB.)create table EMP_1(EMP_NUM CHAR(3) NOT NULL,EMP_LNAME VARCHAR(15) NOT NULL,EMP_FNAME VARCHAR(15) NOT NULL,EMP_INITIAL CHAR(1) NOT NULL,EMP_HIREDATE DATE NOT NULL,JOB_CODE CHAR(3) NOT NULL,)--PROBLEMA 2. Having created the table structure in Problem 1, write the SQL code to enter the-- first two rows for the table shown in Figure P7.2.INSERT INTO EMP_1(EMP_NUM, EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_HIREDATE, JOB_CODE )VALUES ('101','News','John','G','08-Nov-00','502')INSERT INTO EMP_1(EMP_NUM, EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_HIREDATE, JOB_CODE )VALUES ('102','Senior','David','H','12-Jul-89','501')select *from EMP_1-- Problema 3. Assuming that the data shown in the EMP_1 table have been entered, write the SQL-- code that will list all attributes for a job code of 502.select *from EMP_1where JOB_CODE='502'-- 5. Write the SQL code to change the job code to 501 for the person whose employee number (EMP_NUM) is-- 107. After you have completed the task, examine the results, and then reset the job code to its original value. -- ANTES DE REALIZAR EL PROBLEMA SE NTRODUCEN LOS DATOS EN LA FILA:INSERT INTO EMP_1(EMP_NUM, EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_HIREDATE, JOB_CODE )VALUES ('107','Alonzo','Maria','D','10-Oct-93','500')-- SOLUCION DEL PROBLEMA: update EMP_1 SET JOB_CODE='501' WHERE EMP_NUM='107'select *from EMP_1 update EMP_1 SET JOB_CODE='500' WHERE EMP_NUM='107' -- PROBLEMA 6. Write the SQL code to delete the row for the person named William Smithfield, who was hired on June 22, 2004, -- and whose job code classification is 500. (Hint: Use logical operators to include all of the information given in -- this problem.)-- ANTES DE REALIZAR EL PROBLEMA SE INTRODUCEN LOS DATOS EN LA FILA: INSERT INTO EMP_1(EMP_NUM, EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_HIREDATE, JOB_CODE )VALUES ('106','Smithfield','William',' ','22-Jun-04','500')select *from EMP_1--SOLUCION DEL PROBLEMA:delete from EMP_1 WHERE EMP_FNAME='William' AND EMP_LNAME='Smithfield' AND EMP_HIREDATE='22-Jun-04' AND JOB_CODE='500'-- PROBLEMA 8. Write the SQL code to create a copy of EMP_1, naming the copy EMP_2. Then write the SQL code that will add the-- attributes EMP_PCT and PROJ_NUM to its structure. The EMP_PCT is the bonus percentage to be paid to each employee.-- The new attribute characteristics are: -- EMP_PCTNUMBER(4,2) -- PROJ_NUMCHAR(3)-- (Note: If your SQL implementation allows it, you may use DECIMAL(4,2) rather than NUMBER(4,2).)--PARA crear una copia de la tabla EMP_1 llamada EMP_2SELECT *INTO EMP_2 FROM EMP_1SELECT *FROM EMP_2alter table EMP_2add EMP_PCT DECIMAL(4,2)alter table EMP_2add PROJ_NUM CHAR(3) --PARA INTRODUCIR VALORES AL ATRIBUTO EMP_PCT INSERT INTO EMP_2(EMP_NUM, EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_HIREDATE, JOB_CODE,EMP_PCT )VALUES ('103','Arbough','June','E','01-Dec-96','500','5.00') update EMP_2 SET EMP_PCT='5.00' WHERE EMP_NUM='101' update EMP_2 SET EMP_PCT='8.00' WHERE EMP_NUM='102' update EMP_2 SET EMP_PCT='5.15' WHERE EMP_NUM='107'-- Problema 9.Write the SQL code to change the EMP_PCT value to 3.85 for the person whose employee number (EMP_NUM) is 103.-- Next, write the SQL command sequences to change the EMP_PCT values as shown in Figure P7.9. update EMP_2 SET EMP_PCT='3.85' WHERE EMP_NUM='103'-- Problema 10. Using a single command sequence, write the SQL code that will change the project number (PROJ_NUM) to 18 for all employees-- whose job classification (JOB_CODE) is 500.update EMP_2 SET PROJ_NUM='18'WHERE JOB_CODE='500'-- Problema 11. Using a single command sequence, write the SQL code that will change the project number (PROJ_NUM) to 25 for all employees whose job-- classification (JOB_CODE) is 502 or higher. When you finish Problems 10 and 11, the EMP_2 table will contain the data shown in Figure P7.11. -- (You may assume that the table has been saved again at this point.)update EMP_2 SET PROJ_NUM='25'WHERE JOB_CODE>='502'-- Problema 12. Write the SQL code that will change the PROJ_NUM to 14 for those employees who were hired before --January 1, 1994 and whose job code is at least 501. (You may assume that the table will be restored to its condition-- preceding this question.)select * from EMP_2update EMP_2set PROJ_NUM = '14' where EMP_HIREDATE = '501'-- Problema 13. Write the two SQL command sequences required to:-- a. Create a temporary table named TEMP_1 whose structure is composed of the EMP_2 attributes EMP_NUM-- and EMP_PCT.-- b. Copy the matching EMP_2 values into the TEMP_1 table.create table #TEMP_1(EMP_NUM CHAR(3) NOT NULL, EMP_PCT DECIMAL(4,2),)insert into #TEMP_1(EMP_NUM,EMP_PCT)select EMP_NUM, EMP_PCT from EMP_2select * from #TEMP_1-- Problema 14. Write the SQL command that will delete the newly created TEMP_1 table from the database.drop table #TEMP_1-- Problema15. Write the SQL code required to list all employees whose last names start with Smith. In other words, the rows-- for both Smith and Smithfield should be included in the listing. Assume case sensitivity.select * from EMP_1-- Creamos un Smith con caracteres en mayuscula para probar -- ANTES DE REALIZAR EL PROBLEMA SE INTRODUCEN LOS DATOS EN LA FILA:INSERT INTO EMP_1(EMP_NUM, EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_HIREDATE, JOB_CODE )VALUES ('108','SMithfield','William',' ','22-Jun-04','503')select * from EMP_1 select * from EMP_1 where EMP_LNAME COLLATE Latin1_General_CS_AS like 'Smith%'-- Eliminamos para dejar la tabla como antesdelete from EMP_1 WHERE EMP_FNAME='William' AND EMP_LNAME='SMithfield' AND EMP_HIREDATE='22-Jun-04' AND JOB_CODE='503'-- Problema 17. Write the SQL code that will produce a virtual table named REP_1. The virtual table should contain the same-- information that was shown in Problem 16.create table #REP_1(PROJ_NAME VARCHAR(15) NOT NULL,PROJ_VALUE MONEY,PROJ_BALANCE MONEY,EMP_LNAME varchar(15) not null,EMP_FNAME varchar(15) not null,EMP_INITIAL char(1) not null,JOB_CODE char(3) not null ,JOB_DESCRIPTION VARCHAR(15) not null,JOB_CHG_HOUR float not null)INSERT INTO #REP_1 (PROJ_NAME,PROJ_VALUE,PROJ_BALANCE, EMP_LNAME, EMP_FNAME, EMP_INITIAL, JOB_CODE,JOB_DESCRIPTION,JOB_CHG_HOUR )VALUES ('Rolling Tide','605000.00','500345.20','Senior', 'David', 'H', '501','System Analyst', '96.75')INSERT INTO #REP_1 (PROJ_NAME,PROJ_VALUE,PROJ_BALANCE, EMP_LNAME, EMP_FNAME, EMP_INITIAL, JOB_CODE,JOB_DESCRIPTION,JOB_CHG_HOUR )VALUES ('Evergreen','1453500.00','1002350.00','Arbough', 'June', 'E', '500','Programmer', '35.75')INSERT INTO #REP_1 (PROJ_NAME,PROJ_VALUE,PROJ_BALANCE, EMP_LNAME, EMP_FNAME, EMP_INITIAL, JOB_CODE,JOB_DESCRIPTION,JOB_CHG_HOUR )VALUES ('Starflight','2650500.00','2309880.00','Alonzo', 'Maria', 'D', '500','Programmer', '35.75')INSERT INTO #REP_1 (PROJ_NAME,PROJ_VALUE,PROJ_BALANCE, EMP_LNAME, EMP_FNAME, EMP_INITIAL, JOB_CODE,JOB_DESCRIPTION,JOB_CHG_HOUR )VALUES ('Amber Wave','3500500.00','2110346.00','Washintong', 'Ralph', 'B', '501','System Analyst', '96.75')-- Problema 18. Write the SQL code to find the average bonus percentage in the EMP_2 table you created in Problem 8.select * from EMP_2select AVG(EMP_PCT) as 'Average' from EMP_2-- Problema 19. Write the SQL code that will produce a listing for the data in the EMP_2 table in ascending order by the bonus--percentage.select EMP_PCT from EMP_2order by EMP_PCT ASC-- Problema 20. Write the SQL code that will list only the distinct project numbers found in the EMP_2 table.select distinct PROJ_NUM FROM EMP_2-- Problema 21. Write the SQL code to calculate the ASSIGN_CHARGE values in the ASSIGNMENT table in the Ch07_-- ConstructCo database. (See Figure P7.1.) Note that ASSIGN_CHARGE is a derived attribute that is calculated-- by multiplying ASSIGN_CHG_HR by ASSIGN_HOURS.select * from ASSIGNMENTselect ASSING_CHG_HR*ASSING_HOURS as 'ASSING_CHARGE' from ASSIGNMENT