I need help with checking the sql query

velbonax · June 20, 2025, 9:08am

Hello , I would be happy if someone could check my sql queries. I currently don't have an environment where I can run them, but I have a database schema.

SELECT COUNT HR.EMPLOYEES
FROM HR.DEPARTMENS
JOIN HR.EMPLOYEES
ON HR.EMPLOYEES.DEPARTMENT_ID = HR.DEPARTMENS.DEPARTMENT_ID
JOIN HR.DEPARTMENTS
ON HR.DEPARTMENTS.LOCATIONS_ID = HR.LOCATIONS.LOCATION_ID
JOIN HR.LOCATIONS
ON HR.LOCATIONS.COUNTRY_ID = HR.COUNTRIES.COUNTRY_ID
WHERE HR.COUNTRIES.COUNTRY_NAME IS NOT US

SELECT HR.EMPLOYEES.FIRSTNAME, HR.EMPLOYEES.LASTNAME
FROM HR.EMPLOYEES
JOIN HR.JOBS
ON HR.JOBS.JOB_ID = HR.EMPLOYEES.JOB_ID
ORDER BY HR.JOBS.MAX_SALARY DESC
LIMIT 1

SELECT HR.EMPLOYEES.FIRSTNAME, HR.EMPLOYEES.LASTNAME
FROM HR.EMPLOYEES
JOIN HR.JOBS_HISTORY
ON HR.EMPLOYEES.JOB_ID = HR.JOBS_HISTORY.JOB.ID
WHERE HR.JOBS_HISTORY.START_DATE > 2003 AND HR.JOBS_HISTORY.END_DATE < 2007

Thank you so much

harishgg1 · June 20, 2025, 3:44pm

hi

hope this helps

-- 1. How many employees work outside of the US?

SELECT COUNT(*) AS employees_outside_us
FROM HR_EMPLOYEES e
JOIN HR_DEPARTMENTS d ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
JOIN HR_LOCATIONS l ON d.LOCATION_ID = l.LOCATION_ID
WHERE l.COUNTRY_ID <> 'US';


-- 2. Who is getting the highest commission?

SELECT FIRST_NAME, LAST_NAME, COMMISSION_PCT
FROM HR_EMPLOYEES
WHERE COMMISSION_PCT = (SELECT MAX(COMMISSION_PCT) FROM HR_EMPLOYEES);


-- 3. Get list of employees hired between 2004 and 2006.

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, HIRE_DATE
FROM HR_EMPLOYEES
WHERE HIRE_DATE BETWEEN cast('2004-01-01' as date) AND cast('2006-12-31' as date);


-- 4. Find number of employees and total and average salary for each job_id.

SELECT JOB_ID, 
       COUNT(*) AS num_employees,
       SUM(SALARY) AS total_salary,
       AVG(SALARY) AS average_salary
FROM HR_EMPLOYEES
GROUP BY JOB_ID;


-- 5. Create list of employees – first name, last name, job title – and their manager’s first name and last name.

SELECT e.FIRST_NAME AS employee_first_name,
       e.LAST_NAME AS employee_last_name,
       j.JOB_TITLE,
       m.FIRST_NAME AS manager_first_name,
       m.LAST_NAME AS manager_last_name
FROM HR_EMPLOYEES e
LEFT JOIN HR_EMPLOYEES m ON e.MANAGER_ID = m.EMPLOYEE_ID
LEFT JOIN HR_JOBS j ON e.JOB_ID = j.JOB_ID

harishgg1 · June 20, 2025, 5:13pm

(post deleted by author)

harishgg1 · June 20, 2025, 5:45pm

hi

hope this helps

create tables insert sample data RE Usable Script


ALTER TABLE [dbo].[HR_EMPLOYEES] DROP CONSTRAINT [FK_EMP_MANAGER];
ALTER TABLE [dbo].[HR_COUNTRIES] DROP CONSTRAINT [FK_COUNTRY_REGION];
ALTER TABLE [dbo].[HR_LOCATIONS] DROP CONSTRAINT [FK_LOCATION_COUNTRY];
ALTER TABLE [dbo].[HR_DEPARTMENTS] DROP CONSTRAINT [FK_DEPT_LOCATION];
ALTER TABLE [dbo].[HR_EMPLOYEES] DROP CONSTRAINT [FK_EMP_JOB];
ALTER TABLE [dbo].[HR_EMPLOYEES] DROP CONSTRAINT [FK_EMP_DEPT];


drop  TABLE if exists HR_REGIONS
drop  TABLE if exists HR_COUNTRIES
drop  TABLE if exists HR_LOCATIONS
drop  TABLE if exists HR_DEPARTMENTS 
drop  TABLE if exists HR_JOBS 
drop  TABLE if exists HR_EMPLOYEES 
drop  TABLE if exists HR_JOB_HISTORY 


-- Create tables

CREATE TABLE HR_REGIONS (
    REGION_ID int PRIMARY KEY,
    REGION_NAME VARCHAR(25) NOT NULL
);

CREATE TABLE HR_COUNTRIES (
    COUNTRY_ID varchar(2 ) PRIMARY KEY,
    COUNTRY_NAME varchar(40 ) NOT NULL,
    REGION_ID int  NOT NULL,
    CONSTRAINT FK_COUNTRY_REGION FOREIGN KEY (REGION_ID) REFERENCES HR_REGIONS(REGION_ID)
);

CREATE TABLE HR_LOCATIONS (
    LOCATION_ID int PRIMARY KEY,
    STREET_ADDRESS varchar(40 ),
    POSTAL_CODE varchar(12 ),
    CITY varchar(30 ) NOT NULL,
    STATE_PROVINCE varchar(25 ),
    COUNTRY_ID varchar(2 ),
    CONSTRAINT FK_LOCATION_COUNTRY FOREIGN KEY (COUNTRY_ID) REFERENCES HR_COUNTRIES(COUNTRY_ID)
);

CREATE TABLE HR_DEPARTMENTS (
    DEPARTMENT_ID int PRIMARY KEY,
    DEPARTMENT_NAME varchar(30 ) NOT NULL,
    MANAGER_ID int,
    LOCATION_ID int,
    CONSTRAINT FK_DEPT_LOCATION FOREIGN KEY (LOCATION_ID) REFERENCES HR_LOCATIONS(LOCATION_ID)
);

CREATE TABLE HR_JOBS (
    JOB_ID varchar(10 ) PRIMARY KEY,
    JOB_TITLE varchar(35 ) NOT NULL,
    MIN_SALARY int,
    MAX_SALARY int
);

CREATE TABLE HR_EMPLOYEES (
    EMPLOYEE_ID int PRIMARY KEY,
    FIRST_NAME varchar(20 ),
    LAST_NAME varchar(25 ) NOT NULL,
    EMAIL varchar(25 ),
    PHONE_NUMBER varchar(20 ),
    HIRE_DATE DATE NOT NULL,
    JOB_ID varchar(10 ) NOT NULL,
    SALARY numeric(8,2),
    COMMISSION_PCT numeric(2,2),
    MANAGER_ID int,
    DEPARTMENT_ID int,
    CONSTRAINT FK_EMP_JOB FOREIGN KEY (JOB_ID) REFERENCES HR_JOBS(JOB_ID),
    CONSTRAINT FK_EMP_DEPT FOREIGN KEY (DEPARTMENT_ID) REFERENCES HR_DEPARTMENTS(DEPARTMENT_ID),
    CONSTRAINT FK_EMP_MANAGER FOREIGN KEY (MANAGER_ID) REFERENCES HR_EMPLOYEES(EMPLOYEE_ID)
);

CREATE TABLE HR_JOB_HISTORY (
    EMPLOYEE_ID int,
    START_DATE DATE,
    END_DATE DATE,
    JOB_ID varchar(10 ),
    DEPARTMENT_ID int,
    PRIMARY KEY (EMPLOYEE_ID, START_DATE),
    CONSTRAINT FK_JH_EMP FOREIGN KEY (EMPLOYEE_ID) REFERENCES HR_EMPLOYEES(EMPLOYEE_ID),
    CONSTRAINT FK_JH_JOB FOREIGN KEY (JOB_ID) REFERENCES HR_JOBS(JOB_ID),
    CONSTRAINT FK_JH_DEPT FOREIGN KEY (DEPARTMENT_ID) REFERENCES HR_DEPARTMENTS(DEPARTMENT_ID)
);

-- Insert sample data

-- HR_REGIONS
INSERT INTO HR_REGIONS (REGION_ID, REGION_NAME) VALUES (1, 'Europe');
INSERT INTO HR_REGIONS (REGION_ID, REGION_NAME) VALUES (2, 'Americas');

-- HR_COUNTRIES
INSERT INTO HR_COUNTRIES (COUNTRY_ID, COUNTRY_NAME, REGION_ID) VALUES ('UK', 'United Kingdom', 1);
INSERT INTO HR_COUNTRIES (COUNTRY_ID, COUNTRY_NAME, REGION_ID) VALUES ('US', 'United States', 2);

-- HR_LOCATIONS
INSERT INTO HR_LOCATIONS (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE, CITY, STATE_PROVINCE, COUNTRY_ID) 
VALUES (1000, '10 Downing Street', 'SW1A 2AA', 'London', 'Greater London', 'UK');
INSERT INTO HR_LOCATIONS (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE, CITY, STATE_PROVINCE, COUNTRY_ID) 
VALUES (2000, '1600 Pennsylvania Ave', '20500', 'Washington', 'DC', 'US');

-- HR_DEPARTMENTS
INSERT INTO HR_DEPARTMENTS (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID) VALUES (10, 'Administration', NULL, 1000);
INSERT INTO HR_DEPARTMENTS (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID) VALUES (20, 'Marketing', NULL, 2000);

-- HR_JOBS
INSERT INTO HR_JOBS (JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY) VALUES ('AD_PRES', 'President', 20000, 40000);
INSERT INTO HR_JOBS (JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY) VALUES ('AD_VP', 'Administration Vice President', 15000, 30000);
INSERT INTO HR_JOBS (JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY) VALUES ('MK_MAN', 'Marketing Manager', 10000, 20000);
INSERT INTO HR_JOBS (JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY) VALUES ('IT_PROG', 'Programmer', 4000, 10000);

-- HR_EMPLOYEES
INSERT INTO HR_EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID) VALUES
(1, 'John', 'Doe', '[email protected]', '555-1234', cast('2020-01-01'as date), 'AD_PRES', 35000, NULL, NULL, 10),
(2, 'Jane', 'Smith', '[email protected]', '555-2345', cast('2021-03-15'as date), 'AD_VP', 25000, NULL, 1, 10),
(3, 'Mike', 'Brown', '[email protected]', '555-3456', cast('2022-06-20'as date), 'MK_MAN', 15000, 0.05, 2, 20),
(4, 'Alice', 'Green', '[email protected]', '555-4567', cast('2023-01-10'as date), 'IT_PROG', 7000, NULL, 3, 20);

-- HR_JOB_HISTORY
INSERT INTO HR_JOB_HISTORY (EMPLOYEE_ID, START_DATE, END_DATE, JOB_ID, DEPARTMENT_ID) VALUES
(2, cast('2020-01-01'as date), cast('2021-03-14'as date), 'IT_PROG', 20),
(2, cast('2021-03-15'as date), NULL, 'AD_VP', 10),
(3, cast('2021-06-01'as date), cast('2022-06-19'as date), 'IT_PROG', 20),
(3, cast('2022-06-20'as date), NULL, 'MK_MAN', 20);

select * from HR_REGIONS
select * from HR_COUNTRIES
select * from HR_LOCATIONS
select * from HR_DEPARTMENTS 
select * from HR_JOBS 
select * from HR_EMPLOYEES 
select * from HR_JOB_HISTORY

khtan · June 21, 2025, 8:14am

If you are using SQL Server, download and install the Developer or Express Edition

Alternatively you can use one the online fiddle like

It is for your own benefit that you tried out creating the tables and work out the query for the assignment