Write SQL statements for given questions in order to get familiar to basic SQL data manipulation statements by(use MySQL application) using insert using update using alter create table 2 Problem description Questions are listed below. You will be using tables created by demo.sql script. 1. Insert a row into emp table using column names ename, empno and hiredate (EMPLOYEE1, 1234, CURRENT DATE()) 2. Insert a row into dept table without using column names (SOFTWARE, 50) 3. Insert commission employees into bonus table with all required values 4. Update ’SOFTWARE’ department’s location to ’IZMIR’ in dept table 5. Update salaries of all employees increasing by %10 in emp table 6. Update emp table such that set salary of employees with null values to average salary of salesmans in bonus table 7. Delete employee with empno 1234 in emp table 8. Delete departments that with no employees in dept table 9. Delete all records in bonus table 10. Create table named employees with columns empno, ename, dname and fill with records from emp table (use only one sql statement) 11. Drop table you created demo.sql DROP TABLE IF EXISTS EMP; CREATE TABLE EMP (EMPNO DECIMAL(4), ENAME VARCHAR(10), JOB VARCHAR(9), MGR DECIMAL(4), HIREDATE DATE, SAL DECIMAL(7, 2), COMM DECIMAL(7, 2), DEPTNO DECIMAL(2)); INSERT INTO emp VALUES ('7369','SMITH','CLERK','7902','1980-12-17','800.00',NULL,'20'); INSERT INTO emp VALUES ('7499','ALLEN','SALESMAN','7698','1981-02-20','1600.00','300.00','30'); INSERT INTO emp VALUES ('7521','WARD','SALESMAN','7698','1981-02-22','1250.00','500.00','30'); INSERT INTO emp VALUES ('7566','JONES','MANAGER','7839','1981-04-02','2975.00',NULL,'20'); INSERT INTO emp VALUES ('7654','MARTIN','SALESMAN','7698','1981-09-28','1250.00','1400.00','30'); INSERT INTO emp VALUES ('7698','BLAKE','MANAGER','7839','1981-05-01','2850.00',NULL,'30'); INSERT INTO emp VALUES ('7782','CLARK','MANAGER','7839','1981-06-09','2450.00',NULL,'10'); INSERT INTO emp VALUES ('7788','SCOTT','ANALYST','7566','1982-12-09','3000.00',NULL,'20'); INSERT INTO emp VALUES ('7839','KING','PRESIDENT',NULL,'1981-11-17','5000.00',NULL,'10'); INSERT INTO emp VALUES ('7844','TURNER','SALESMAN','7698','1981-09-08','1500.00','0.00','30'); INSERT INTO emp VALUES ('7876','ADAMS','CLERK','7788','1983-01-12','1100.00',NULL,'20'); INSERT INTO emp VALUES ('7900','JAMES','CLERK','7698','1981-12-03','950.00',NULL,'30'); INSERT INTO emp VALUES ('7902','FORD','ANALYST','7566','1981-12-03','3000.00',NULL,'20'); INSERT INTO emp VALUES ('7934','MILLER','CLERK','7782','1982-01-23','1300.00',NULL,'10'); DROP TABLE IF EXISTS DEPT; CREATE TABLE DEPT (DEPTNO DECIMAL(2), DNAME VARCHAR(14), LOC VARCHAR(13) ); INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK'); INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS'); INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO'); INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON'); DROP TABLE IF EXISTS BONUS; CREATE TABLE BONUS (ENAME VARCHAR(10), JOB VARCHAR(9), SAL DECIMAL, COMM DECIMAL); DROP TABLE IF EXISTS SALGRADE; CREATE TABLE SALGRADE (GRADE DECIMAL, LOSAL DECIMAL, HISAL DECIMAL); INSERT INTO SALGRADE VALUES (1, 700, 1200); INSERT INTO SALGRADE VALUES (2, 1201, 1400); INSERT INTO SALGRADE VALUES (3, 1401, 2000); INSERT INTO SALGRADE VALUES (4, 2001, 3000); INSERT INTO SALGRADE VALUES (5, 3001, 9999);

Write SQL statements for given questions in order to get familiar to basic SQL data manipulation statements by(use MySQL application) using insert using update using alter create table 2 Problem description Questions are listed below. You will be using tables created by demo.sql script. 1. Insert a row into emp table using column names ename, empno and hiredate (EMPLOYEE1, 1234, CURRENT DATE()) 2. Insert a row into dept table without using column names (SOFTWARE, 50) 3. Insert commission employees into bonus table with all required values 4. Update ’SOFTWARE’ department’s location to ’IZMIR’ in dept table 5. Update salaries of all employees increasing by %10 in emp table 6. Update emp table such that set salary of employees with null values to average salary of salesmans in bonus table 7. Delete employee with empno 1234 in emp table 8. Delete departments that with no employees in dept table 9. Delete all records in bonus table 10. Create table named employees with columns empno, ename, dname and fill with records from emp table (use only one sql statement) 11. Drop table you created demo.sql DROP TABLE IF EXISTS EMP; CREATE TABLE EMP (EMPNO DECIMAL(4), ENAME VARCHAR(10), JOB VARCHAR(9), MGR DECIMAL(4), HIREDATE DATE, SAL DECIMAL(7, 2), COMM DECIMAL(7, 2), DEPTNO DECIMAL(2)); INSERT INTO emp VALUES ('7369','SMITH','CLERK','7902','1980-12-17','800.00',NULL,'20'); INSERT INTO emp VALUES ('7499','ALLEN','SALESMAN','7698','1981-02-20','1600.00','300.00','30'); INSERT INTO emp VALUES ('7521','WARD','SALESMAN','7698','1981-02-22','1250.00','500.00','30'); INSERT INTO emp VALUES ('7566','JONES','MANAGER','7839','1981-04-02','2975.00',NULL,'20'); INSERT INTO emp VALUES ('7654','MARTIN','SALESMAN','7698','1981-09-28','1250.00','1400.00','30'); INSERT INTO emp VALUES ('7698','BLAKE','MANAGER','7839','1981-05-01','2850.00',NULL,'30'); INSERT INTO emp VALUES ('7782','CLARK','MANAGER','7839','1981-06-09','2450.00',NULL,'10'); INSERT INTO emp VALUES ('7788','SCOTT','ANALYST','7566','1982-12-09','3000.00',NULL,'20'); INSERT INTO emp VALUES ('7839','KING','PRESIDENT',NULL,'1981-11-17','5000.00',NULL,'10'); INSERT INTO emp VALUES ('7844','TURNER','SALESMAN','7698','1981-09-08','1500.00','0.00','30'); INSERT INTO emp VALUES ('7876','ADAMS','CLERK','7788','1983-01-12','1100.00',NULL,'20'); INSERT INTO emp VALUES ('7900','JAMES','CLERK','7698','1981-12-03','950.00',NULL,'30'); INSERT INTO emp VALUES ('7902','FORD','ANALYST','7566','1981-12-03','3000.00',NULL,'20'); INSERT INTO emp VALUES ('7934','MILLER','CLERK','7782','1982-01-23','1300.00',NULL,'10'); DROP TABLE IF EXISTS DEPT; CREATE TABLE DEPT (DEPTNO DECIMAL(2), DNAME VARCHAR(14), LOC VARCHAR(13) ); INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK'); INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS'); INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO'); INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON'); DROP TABLE IF EXISTS BONUS; CREATE TABLE BONUS (ENAME VARCHAR(10), JOB VARCHAR(9), SAL DECIMAL, COMM DECIMAL); DROP TABLE IF EXISTS SALGRADE; CREATE TABLE SALGRADE (GRADE DECIMAL, LOSAL DECIMAL, HISAL DECIMAL); INSERT INTO SALGRADE VALUES (1, 700, 1200); INSERT INTO SALGRADE VALUES (2, 1201, 1400); INSERT INTO SALGRADE VALUES (3, 1401, 2000); INSERT INTO SALGRADE VALUES (4, 2001, 3000); INSERT INTO SALGRADE VALUES (5, 3001, 9999);

Computer Networking: A Top-Down Approach (7th Edition)

7th Edition

ISBN:9780133594140

Author:James Kurose, Keith Ross

Publisher:James Kurose, Keith Ross

Chapter1: Computer Networks And The Internet

Section: Chapter Questions

Problem R1RQ: What is the difference between a host and an end system? List several different types of end...

See similar textbooks

Related questions

Question

1. Insert a row into emp table using column names ename, empno and hiredate (EMPLOYEE1, 1234,
CURRENT DATE())
2. Insert a row into dept table without using column names (SOFTWARE, 50)
3. Insert commission employees into bonus table with all required values
4. Update ’SOFTWARE’ department’s location to ’IZMIR’ in dept table
5. Update salaries of all employees increasing by %10 in emp table
6. Update emp table such that set salary of employees with null values to average salary of salesmans in
bonus table
7. Delete employee with empno 1234 in emp table
8. Delete departments that with no employees in dept table
9. Delete all records in bonus table
10. Create table named employees with columns empno, ename, dname and fill with records from emp
table (use only one sql statement)
11. Drop table you created

demo.sql

DROP TABLE IF EXISTS EMP; CREATE TABLE EMP (EMPNO DECIMAL(4), ENAME VARCHAR(10), JOB VARCHAR(9), MGR DECIMAL(4), HIREDATE DATE, SAL DECIMAL(7, 2), COMM DECIMAL(7, 2), DEPTNO DECIMAL(2)); INSERT INTO emp VALUES ('7369','SMITH','CLERK','7902','1980-12-17','800.00',NULL,'20'); INSERT INTO emp VALUES ('7499','ALLEN','SALESMAN','7698','1981-02-20','1600.00','300.00','30'); INSERT INTO emp VALUES ('7521','WARD','SALESMAN','7698','1981-02-22','1250.00','500.00','30'); INSERT INTO emp VALUES ('7566','JONES','MANAGER','7839','1981-04-02','2975.00',NULL,'20'); INSERT INTO emp VALUES ('7654','MARTIN','SALESMAN','7698','1981-09-28','1250.00','1400.00','30'); INSERT INTO emp VALUES ('7698','BLAKE','MANAGER','7839','1981-05-01','2850.00',NULL,'30'); INSERT INTO emp VALUES ('7782','CLARK','MANAGER','7839','1981-06-09','2450.00',NULL,'10'); INSERT INTO emp VALUES ('7788','SCOTT','ANALYST','7566','1982-12-09','3000.00',NULL,'20'); INSERT INTO emp VALUES ('7839','KING','PRESIDENT',NULL,'1981-11-17','5000.00',NULL,'10'); INSERT INTO emp VALUES ('7844','TURNER','SALESMAN','7698','1981-09-08','1500.00','0.00','30'); INSERT INTO emp VALUES ('7876','ADAMS','CLERK','7788','1983-01-12','1100.00',NULL,'20'); INSERT INTO emp VALUES ('7900','JAMES','CLERK','7698','1981-12-03','950.00',NULL,'30'); INSERT INTO emp VALUES ('7902','FORD','ANALYST','7566','1981-12-03','3000.00',NULL,'20'); INSERT INTO emp VALUES ('7934','MILLER','CLERK','7782','1982-01-23','1300.00',NULL,'10'); DROP TABLE IF EXISTS DEPT; CREATE TABLE DEPT (DEPTNO DECIMAL(2), DNAME VARCHAR(14), LOC VARCHAR(13) ); INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK'); INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS'); INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO'); INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON'); DROP TABLE IF EXISTS BONUS; CREATE TABLE BONUS (ENAME VARCHAR(10), JOB VARCHAR(9), SAL DECIMAL, COMM DECIMAL); DROP TABLE IF EXISTS SALGRADE; CREATE TABLE SALGRADE (GRADE DECIMAL, LOSAL DECIMAL, HISAL DECIMAL); INSERT INTO SALGRADE VALUES (1, 700, 1200); INSERT INTO SALGRADE VALUES (2, 1201, 1400); INSERT INTO SALGRADE VALUES (3, 1401, 2000); INSERT INTO SALGRADE VALUES (4, 2001, 3000); INSERT INTO SALGRADE VALUES (5, 3001, 9999);

Expert Solution