Due Date: September 1, 2023
Now you need to provide me with your W3School SQL score.
Your file name should be PX_20230901_W3S_Score.png.
Be sure to drop it off into google classroom.
See example below:
Due Date: September 8, 2023
Employee Schema
Create Schema and execute commands below.
The file names will be:
PX_employee_lastname (The actual SQL code.)
PX_employee_lastname.mp4 (A video of the program running.)
PX_employee_lastname.png (A picture of program inside the IDE.)
Be sure to drop off your 3 files in google classroom.
Your instructions is:
A. To create a schema based on the information I
gave you below.
B. Add update / insert data using
SQL commands to populate the tables.
Review the partial schema I have below.
Use the the information below.
Schema without the data types set.
(You need to figure out what the data types values and keys are.)
departments (
department_id
department_name
);
CREATE TABLE employees (
employee_id
first_name
last_name
job_id
salary
department_id
FOREIGN KEY
);
Due Date: September 8, 2023
B. Execute commands below successfully commands below.
1. **Retrieve All Data from a Table:**
* Assuming you have a table named `employees`, fetch all columns and rows from it.
```SQL
SELECT * FROM employees;
```
2. **Retrieve Specific Columns:**
* Fetch only the `first_name` and `last_name` columns from the `employees` table.
```SQL
SELECT first_name, last_name FROM employees;
```
3. **Filter Using WHERE:**
* Get details of employees with a specific `job_id` (e.g., 'MANAGER').
```SQL
SELECT * FROM employees WHERE job_id = 'MANAGER';
```
4. **Sorting Results:**
* List employees in descending order of their `salary`.
```SQL
SELECT * FROM employees ORDER BY salary DESC;
```
5. **Count Rows:**
* Count the number of employees with a salary greater than 50000.
```SQL
SELECT COUNT(*) FROM employees WHERE salary > 50000;
```
6. **Sum, Average, Min, and Max:**
* Find the sum, average, minimum, and maximum salary from the `employees` table.
```SQL
SELECT SUM(salary), AVG(salary), MIN(salary), MAX(salary) FROM employees;
```
7. **Use of DISTINCT:**
* Find the distinct job IDs from the `employees` table.
```SQL
SELECT DISTINCT job_id FROM employees;
```
8. **Using LIKE Operator:**
* Fetch details of employees whose name starts with 'Jo'.
```SQL
SELECT * FROM employees WHERE first_name LIKE 'Jo%';
```
9. **Using BETWEEN:**
* Retrieve employees whose salary is between 30000 and 50000.
```SQL
SELECT * FROM employees WHERE salary BETWEEN 30000 AND 50000;
```
10. **Using IN Operator:**
* Get details of employees who are in one of the following departments: 'HR', 'Finance', 'Sales'.
```SQL
SELECT * FROM employees WHERE department IN ('HR', 'Finance', 'Sales');
```
11. **Combining Conditions with AND & OR:**
* Find employees in the 'IT' department with a salary greater than 40000.
```SQL
SELECT * FROM employees WHERE department = 'IT' AND salary > 40000;
```
12. **Limiting Results:**
* Retrieve the top 5 highest-earning employees.
```SQL
SELECT * FROM employees ORDER BY salary DESC LIMIT 5;
```
13. **Using Aggregate Functions with GROUP BY:**
* Count employees in each department.
```SQL
SELECT department, COUNT(*) FROM employees GROUP BY department;
```
14. **Filtering Aggregated Data with HAVING:**
* Find departments that have more than 5 employees.
```SQL
SELECT department, COUNT(*) AS num_employees FROM employees GROUP BY department HAVING num_employees > 5;
```
15. **Join Two Tables:**
* Assume you have another table named `departments` with a `department_id`
as a primary key. Fetch employee names along with their respective department names using a JOIN operation.
```SQL
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;
```
Remember to adjust table and column names according to your own database
schema. Also, practice is key. Keep experimenting with different queries,
and you'll improve rapidly.
