Question

EMPLOYEE

Field Name

EMP_ID EMP_LNAME EMP_MI EMP_FNAME EMP_SEX EMP_AGE EMP_SALARY EMP_HIREDATE DEPT_CODE

Table: DEPARTMENT

DEPT_CODE

BIOL CPTR HIST MATH RELB

Data Type

Text

Text

Text

Text

Text Number Currency Date/Time Text

DEPT_TITLE

Biology

Computer Science History Mathematics Religion

  

Field Name

DEPT_CODE DEPT_TITLE

Data Type

Text Text

INSTRUCTIONS

Use SQL commands to create the tables and enter the data shown above using MS Access. Write the following SQL statement given below:

1.

2.

3.

4.

5.

6. 7.

8. 9. 10. 11. 12.

Write the SQL statement to create a new table called TEMP whose attributes should be similar to that of table EMPLOYEES. Save as Query1-TABLE TEMP

Write the SQL statement to Alter table TEMP and add the primary key. Save as Query2-ALTER & ADD PRIMARY KEY

Write the SQL statement to insert into TEMP, the values contained in the first row of the EMPLOYEES table. Save as Query3-INSERT INTO TEMP

Write the SQL statement to DELETE records from TEMP where EMP_HIREDATE is before 2010. Save as Query4-DELETE FROM TEMP

Write the SQL statement to INSERT all the rows of EMPLOYEES table into TEMP. Save as Query5- INSERT INTO & SELECT SUBQUERY

Write the SQL statement to delete table TEMP from the database. Save as Query6-DROP TEMP. Write the SQL statement to display all the rows for employees whose hired date was after January 1, 2018. Save as Query7-DATE & COMPARISON

Write the SQL statement to display all rows for employees who do not have a middle initial using the IS NULL special operator. Save as Query8-MIDDLE INITIAL IS NULL.

Write the SQL statement to display all rows for employees whose last name begins with ‘B’ using the LIKE special operator. Save as Query9 –EMPLOYEES WITH LASTNAME BEGIN WITH B.

Write the SQL statement to display all rows for employees whose last name is Adams. Save as Query-EMPLOYEES LASTNAME IS ADAMS.

Write the SQL statement to display all rows for employees in the Computer Science Department using the IN special operator. Save as Query11-EMPLOYEES IN MATH & COMPUTER SCIENCE. Write the SQL statement to display last name, first name and age of all employees in descending order. Save as Query12-ORDER BY LASTNAME.

13. Write the SQL statement to count the total number of employees using the alias TOTAL_EMPLOYEE. Save as Query13-TOTAL_EMPLOYEE.

14. Write the SQL statement to find the average age of all employees. Use the Alias AVERAGE_AGE. Save as Query14-AVERAGE AGE.

15. Write the SQL statement to display first name, last name and salary of the employee with the highest salary. Save as Query15-HIGHEST PAY.

16. Write the SQL statement to display first name, last name and sex of the employee grouped by sex. Save as Query16-GROUP BY SEX.

17. Write the SQL statement to display first name, last name and sex of all female employees Grouped by sex. Save as Query17-GROUP BY SEX FEMALES. Hint: include a HAVING CLAUSE.

18. Write the SQL statement to display first name, last name and department of all employees. Save as Query18- JOIN QUERY.

19. Write the SQL statement to display all rows for all employees that are older than 30 and are female. Save as Query19-USING AND.

20. Write the SQL statement to display all rows for all employees that are either from the Computer Science department. Save as Query20-USING OR

21. Using the three (3) Data Model types, show the tables above in their three different phases.

Answer 1

1.CREATE TABLE TEMP (
EMP_ID text,
EMP_LNAME text,
EMP_MI text,
EMP_FNAME text,
EMP_SEX text,
EMP_AGE number,
EMP_SALARY currency,
EMP_HIREDATE datetime,
DEPT_CODE text);
Save as :Query1-TABLE TEMP

2. ALTER TABLE TEMP
ADD PRIMARY KEY (EMP_ID);
Save as :Query2-ALTER & ADD PRIMARY KEY

3. INSERT INTO TEMP
SELECT TOP 3 * FROM EMPLOYEE;
Save as Query3-INSERT INTO TEMP

4. DELETE * FROM TEMP WHERE YEAR(EMP_HIREDATE)<2010;
Save as Query4-DELETE FROM TEMP

5. INSERT INTO TEMP
SELECT TOP * FROM EMPLOYEE;
Save as Query5- INSERT INTO & SELECT SUBQUERY

6. DROP TABLE TEMP;
Save as Query6-DROP TEMP

7. SELECT * FROM EMPLOYEE WHERE EMP_HIREDATE>'2018-1-1';
Save as Query7-DATE & COMPARISON

8. SELECT * FROM EMPLOYEE WHERE EMP_MI IS NULL;
Save as Query8-MIDDLE INITIAL IS NULL

9. SELECT * FROM EMPLOYEE WHERE EMP_LNAME LIKE 'B%';
Save as Query9 –EMPLOYEES WITH LASTNAME BEGIN WITH B

10. SELECT * FROM EMPLOYEE WHERE EMP_LNAME=ADAMS';
Save as Query-EMPLOYEES LASTNAME IS ADAMS

11. SELECT * FROM EMPLOYEE WHERE DEPT_CODE IN('CPTR','MATH');
Save as Query11-EMPLOYEES IN MATH & COMPUTER SCIENCE

12. SELECT EMP_LNAME,EMP_FNAME,EMP_AGE FROM EMPLOYEE ORDER BY DESC;
Save as Query12-ORDER BY LASTNAME.

13. SELECT COUNT(EMP_ID) AS TOTAL_EMPLOYEE FROM EMPLOYEE;
Save as Query13-TOTAL_EMPLOYEE

14. SELECT AVG(EMP_AGE) AS AVERAGE_AGE FROM EMPLOYEE;
Save as Query14-AVERAGE AGE

15. SELECT EMP_FNAME,EMP_LNAME,EMP_SALARY FROM EMPLOYEE WHERE EMP_SALARY=(select max(EMP_SALARY) from EMPLOYEE);
Save as Query15-HIGHEST PAY

16. SELECT EMP_FNAME,EMP_LNAME,EMP_SEX FROM EMPLOYEE GROUP BY EMP_SEX;
Save as Query16-GROUP BY SEX

17. SELECT EMP_FNAME,EMP_LNAME,EMP_SEX FROM EMPLOYEE GROUP BY EMP_SEX HAVING EMP_SEX='female';
ave as Query17-GROUP BY SEX FEMALES. Hint: include a HAVING CLAUSE

18. SELECT EMPLOYEE.EMP_FNAME, EMPLOYEE.EMP_LName,DEPARTMENT.DEPT_TITLE FROM EMPLOYEE
INNER JOIN DEPARTMENT ON EMPLOYEE.DEPT_CODE=DEPARTMENT.DEPT_CODE;
Save as Query18- JOIN QUERY

19. SELECT * FROM EMPLOYEE WHERE EMP_AGE>30 AND EMP_SEX='female';
Save as Query19-USING AND

20. SELECT * FROM EMPLOYEE WHERE DEPT_CODE IN('BIOL','HIST','MATH','RELB') OR DEPT_CODE='CPTR';
Save as Query20-USING OR