PostgreSQL (pgAdmin4) SQL Practice Dataset & Questions
Dataset
Tables:
1. departments(dept_id, dept_name, location)
2. employees(emp_id, emp_name, gender, age, salary, hire_date, dept_id, manager_id)
3. projects(project_id, project_name, budget, start_date)
4. employee_projects(emp_id, project_id, hours_worked)
Sample Departments:
HR, IT, Finance, Sales, Marketing
Sample Employees:
Amit, Priya, Rahul, Neha, Rohan, Anjali, Vikas, Sneha, Arjun, Pooja
Sample Projects:
ERP System, Mobile App, Marketing Campaign, Finance Audit, Sales Portal
CREATE TABLE
1. Create Student table
2. Create Product table
3. Create Customer table
4. Create Orders table
5. Create Books table
ALTER TABLE
1. Add Mobile column
2. Add Address column
3. Add Bonus column
4. Remove Email column
5. Add Joining Time column
UPDATE
1. Increase Amit salary
2. Increase IT department salary by 10%
3. Add bonus to female employees
4. Update employee age
5. Change employee department
DELETE
1. Delete employees older than 35
2. Delete Sales department employees
3. Delete salary below 30000
4. Delete Marketing department employees
5. Delete a specific employee
SELECT & WHERE
1. Show all employees
2. Show only employee names
3. Employees salary > 50000
4. Employees age < 30
5. Show female employees
BETWEEN
1. Age between 25 and 30
2. Salary between 30000 and 50000
3. Budget between 100000 and 300000
4. Hire date between 2022 and 2023
5. Hours worked between 50 and 150
LIKE / NOT LIKE
1. Names starting with A
2. Names ending with a
3. Names containing h
4. Names not starting with A
5. Names with exactly 5 characters
ORDER BY / LIMIT
1. Top 5 highest salary employees
2. Top 3 lowest salary employees
3. Second page using limit offset
4. Latest hired employees
5. Oldest hired employees
STRING FUNCTIONS
1. Convert names to upper case
2. Convert names to lower case
3. Concatenate name and salary
4. Extract first 3 letters of name
5. Find length of employee name
NUMERIC FUNCTIONS
1. Use ABS
2. Use ROUND
3. Use FLOOR
4. Use CEIL
5. Use MOD
AGGREGATE FUNCTIONS
1. Count employees
2. Total salary expense
3. Average salary
4. Maximum salary
5. Minimum salary
GROUP BY / HAVING
1. Employee count by department
2. Average salary by department
3. Departments with avg salary > 45000
4. Total salary by department
5. Departments with more than 2 employees
JOINS
1. Employee with department name
2. Employees and projects
3. Left join example
4. Right join example
5. Self join for manager details
UNION
1. Union employee and department names
2. Union all employee and department names
3. Union locations and project names
4. Union distinct values
5. Union all values
SUBQUERY
1. Employees earning above average salary
2. Highest salary employee
3. Employees in largest department
4. Projects above average budget
5. Employees with salary greater than department average
VIEWS & INDEXES
1. Create employee view
2. Create salary index
3. Create unique index on employee name
4. Create department view
5. Drop an index
CASE
1. Categorize salary as High/Medium/Low
2. Categorize age groups
3. Department category
4. Project budget category
5. Employee experience category
FUNCTION / PROCEDURE / TRIGGER
1. Create bonus calculation function
2. Create salary increment procedure
3. Create insert logging trigger
4. Create update trigger
5. Create audit procedure
DATE FUNCTIONS
1. Current date
2. Current time
3. Current timestamp
4. Extract year from current date
5. Find employee age from hire date
WINDOW FUNCTIONS
1. ROW_NUMBER by salary
2. RANK by salary
3. DENSE_RANK by salary
4. LAG salary
5. LEAD salary