(Solved) : Create Three Tables Populate Data Choice Create Table Dept Dept Id Int 2 Dname Varchar 14 Q39405201 . . .

Create these three tables, and populate with data of your choice: CREATE TABLE DEPT (DEPT ID INT(2), DNAME VARCHAR(14) LOC VACreate a procedure that takes in a DNAME (Department Name) value as an incoming parameter 1. and show the total salary for th

Create these three tables, and populate with data of your choice: CREATE TABLE DEPT (DEPT ID INT(2), DNAME VARCHAR(14) LOC VARCHAR(15), DATE_INSERTED DATE, DATE LASTUPDATED DATE, PRIMARY KEY(DEPT_ID)); CREATE TABLE SALGRADE (GRADE_ID INT(3), LOSAL NUMERIC(7,2), HISAL NIMERIC(7,2), PRIMARY KEY(GRAD E_ID)); CREATE TABLE EMP (EMP_ID INT(4), E LAST NAME VARCHAR(20), E FIRST_NAME VARCHAR(15), JOB VARCHAR(9), MGR_ID INT(4), HIREDATE DATE, SAL NUMERIC(7,2), GRADE_ID INT(3), COMM NUMERIC(7,2), DEPT_ID INT(2), DATE INSERTED DATE, DATE LASTUPDATED DATE, PRIMARY KEY (EMP ID), FOREIGN KEY (DEPT_ID) REFERENCES DEPT(DEPT_ID), FOREIGN KEY (GRADE ID) REFERENCES SALGRADE(GRADE ID)); Sample data load: INSERT INTO DEPT VALUES (10,’ACCOUNTING’,’NEW YORK current timestamp),gySNt timestamp)); INSERT INTO SALGRADE VALUES (1,20500.00,25600.00); INSERT INTO EMP VALUES (7934,’Miller’,’Kent’,’Clerk’,7782,2002/03/21′,21300.00,L.NULL.,10,current_timestamp(),current_timestamp(); URL for indexes: http://www.mysqltutorial.org/mysql-index/mysql-composite-index/ Create a procedure that takes in a DNAME (Department Name) value as an incoming parameter 1. and show the total salary for that department, include DEPT_ID, DNAME, and total salary as output 2. Create a procedure that takes in an EMP_ID value as in incoming parameter and show Employee’s full name, current salary, and the salary range (losal-hisal) for that Employee’s Create the best index (show create command) that would be helpful to both these queries, and prove the index is being used with the Explain command: 3. Select elast name, hiredate, salfrom employee where e last name = ‘Smith’; Create Index Employee Name On EmplE Last name jstuaae) Select elast name, e fist name, dept id from employee where e last name = ‘Smith’ and e fust awe= ‘ou’; Create a procedure that has one input parameter a value for MGR_D, and an out parameter to 4. return the employee id with the highest salary for that manager. You will have to do a little research on the output parameter and how to use it. 5. Which one of these should have an index? A query that runs every hour with DNAME in the PREDICATE clause, or a query that runs once a month with LOC in the WHERE clause? Show the create index command for the one you picked. Create a SELECT query that will utilize the index you created and prove it was used with the Explain command. Show transcribed image text Create these three tables, and populate with data of your choice: CREATE TABLE DEPT (DEPT ID INT(2), DNAME VARCHAR(14) LOC VARCHAR(15), DATE_INSERTED DATE, DATE LASTUPDATED DATE, PRIMARY KEY(DEPT_ID)); CREATE TABLE SALGRADE (GRADE_ID INT(3), LOSAL NUMERIC(7,2), HISAL NIMERIC(7,2), PRIMARY KEY(GRAD E_ID)); CREATE TABLE EMP (EMP_ID INT(4), E LAST NAME VARCHAR(20), E FIRST_NAME VARCHAR(15), JOB VARCHAR(9), MGR_ID INT(4), HIREDATE DATE, SAL NUMERIC(7,2), GRADE_ID INT(3), COMM NUMERIC(7,2), DEPT_ID INT(2), DATE INSERTED DATE, DATE LASTUPDATED DATE, PRIMARY KEY (EMP ID), FOREIGN KEY (DEPT_ID) REFERENCES DEPT(DEPT_ID), FOREIGN KEY (GRADE ID) REFERENCES SALGRADE(GRADE ID)); Sample data load: INSERT INTO DEPT VALUES (10,’ACCOUNTING’,’NEW YORK current timestamp),gySNt timestamp)); INSERT INTO SALGRADE VALUES (1,20500.00,25600.00); INSERT INTO EMP VALUES (7934,’Miller’,’Kent’,’Clerk’,7782,2002/03/21′,21300.00,L.NULL.,10,current_timestamp(),current_timestamp(); URL for indexes: http://www.mysqltutorial.org/mysql-index/mysql-composite-index/
Create a procedure that takes in a DNAME (Department Name) value as an incoming parameter 1. and show the total salary for that department, include DEPT_ID, DNAME, and total salary as output 2. Create a procedure that takes in an EMP_ID value as in incoming parameter and show Employee’s full name, current salary, and the salary range (losal-hisal) for that Employee’s Create the best index (show create command) that would be helpful to both these queries, and prove the index is being used with the Explain command: 3. Select elast name, hiredate, salfrom employee where e last name = ‘Smith’; Create Index Employee Name On EmplE Last name jstuaae) Select elast name, e fist name, dept id from employee where e last name = ‘Smith’ and e fust awe= ‘ou’; Create a procedure that has one input parameter a value for MGR_D, and an out parameter to 4. return the employee id with the highest salary for that manager. You will have to do a little research on the output parameter and how to use it. 5. Which one of these should have an index? A query that runs every hour with DNAME in the PREDICATE clause, or a query that runs once a month with LOC in the WHERE clause? Show the create index command for the one you picked. Create a SELECT query that will utilize the index you created and prove it was used with the Explain command.

Expert Answer


Answer to Create these three tables, and populate with data of your choice: CREATE TABLE DEPT (DEPT ID INT(2), DNAME VARCHAR(14) L…

Leave a Comment

About

We are the best freelance writing portal. Looking for online writing, editing or proofreading jobs? We have plenty of writing assignments to handle.

Quick Links

Browse Solutions

Place Order

About Us

× How can I help you?