Question

Question 1: Part 1

Write SQL statements for the following queries from the ‘EMPLOYEE’ table in the WPC Database in MySQL:

1. Display all records from the Employee table for employees working in the “Marketing” department.
2. Display all records from the Employee table for employees working in the “Marketing” department OR “Finance” Department.
3. Display the Last Names of all employees such that each last name appears only once.
4. Display all the attributes for employees whose employee number is less than 10.
5. Display the first name, last name, phone, and department fields for all employees who do NOT work in “InfoSystems” department.

Answer 1

ANSWERS -

1) To list all details of employee working for Marketing department. We use here WHERE condition to specify the department and to list all details, we use asterisk (*) operator.

Table name - EMPLOYEE

The query will be,

SELECT * FROM EMPLOYEE WHERE Department='Marketing' ;  

Resultant output will display a table with all details of employee who works in Marketing department.

Employee Number

FirstName

LastName

Department

Phone

Email

2) To list all details of employee working for Marketing or in Finance department. Here, we use WHERE condition and OR operator to filter the details of employees.

Table name - EMPLOYEE

The query to formed will be,

SELECT * FROM EMPLOYEE WHERE Department='Marketing' OR Department='Finance' ;

Resultant output will display a table with all details of employee who works in Marketing or Finance department.

Employee Number

FirstName

LastName

Department

Phone

Email

3) To list unique last name of all the employees we use DISTINCT keyword to eliminate the duplicate values that is it will list only the value once.

Table name - EMPLOYEE

Column name - LastName

The query to formed will be,

SELECT DISTINCT LastName FROM EMPLOYEE  ;

This will list the unique lastname of all the employees,

LastName

4) To list all the details of employees having employee number less than 10, we use the WHERE condition along with the comparison operator. Here '<' operator.

Table name - EMPLOYEE

The query to formed will be,

SELECT * FROM EMPLOYEE WHERE 'Employee Number'<10 ;

This will list all the details of employee whose employee number is less than 10.

Employee Number

FirstName

LastName

Department

Phone

Email

5) To list the first, last name, phone and department of all employees except the employee who works on InfoSystems department. We use WHERE condition along with NOT operator.

Table name - EMPLOYEE

Column name - FirstName, LastName, Phone, Department

The query to formed will be,

SELECT FirstName, LastName, Phone, Department FROM EMPLOYEE WHERE NOT Department='InfoSystems' ;

The output will list the details of all employees except those working on InfoSystems department.

FirstName

LastName

Phone

Department

=================================END================================

Please comment if u need any other info and DO LEAVE A LIKE, it would mean a lot. Thanks :)