First run this query to add 3 more records with unique values into Employee table:
INSERT INTO Employee (LastName, FirstName, Title, ReportsTo, BirthDate, HireDate, Address, City, State, Country, PostalCode, Phone, Fax, Email)
VALUES
('Balbakob', 'George', 'QA Engineer', 1, '1980-01-01', '2024-09-27', '123 Main St', 'Anytown', 'CA', 'USA', '12345', '555-1234', '555-5678', '[email protected]'),
('Smith', 'Emma', 'Software Developer', 2, '1992-05-15', '2024-10-01', '456 Oak Ave', 'Tech City', 'NY', 'USA', '67890', '555-2345', '555-6789', '[email protected]'),
('Garcia', 'Carlos', 'Data Analyst', 3, '1988-11-30', '2024-09-15', '789 Pine Rd', 'Data Valley', 'TX', 'USA', '54321', '555-3456', '555-7890', '[email protected]');
This query inserts three new records into the Employee table, each with unique values for all fields. Make sure to adjust the ReportsTo values (1, 2, 3) to match existing employee IDs in your table if necessary.
Here are 10 tasks to practice simple queries using SQLite on the Employee table:
List all employees: Write a query to display all columns for all employees.
SELECT * FROM Employee;
Find employees by country: Write a query to list all employees from a specific country (e.g., USA).
SELECT FirstName, LastName, Title
FROM Employee
WHERE Country = 'USA';
Count employees: Write a query to count the total number of employees in the table.
SELECT COUNT(*) AS TotalEmployees
FROM Employee;
Sort by hire date: Write a query to list employees sorted by their hire date, from newest to oldest.
SELECT FirstName, LastName, HireDate
FROM Employee
ORDER BY HireDate DESC;
Find employees without a manager: Write a query to find all employees who don't report to anyone (ReportsTo is NULL).
SELECT FirstName, LastName, Title
FROM Employee
WHERE ReportsTo IS NULL;
List unique job titles: Write a query to display all unique job titles in the company.
SELECT DISTINCT Title
FROM Employee
ORDER BY Title;
Find employees by partial name: Write a query to find all employees whose last name starts with 'S'.
SELECT FirstName, LastName
FROM Employee
WHERE LastName LIKE 'S%';
Calculate average age: Write a query to calculate the average age of employees based on their birth date.
SELECT AVG((JULIANDAY('now') - JULIANDAY(BirthDate))/365.25) AS AverageAge
FROM Employee;
Find the oldest employee: Write a query to find the oldest employee based on birth date.
SELECT FirstName, LastName, BirthDate
FROM Employee
ORDER BY BirthDate ASC
LIMIT 1;
List employees hired in a specific year: Write a query to list all employees hired in the year 2023.
SELECT FirstName, LastName, HireDate
FROM Employee
WHERE strftime('%Y', HireDate) = '2023';