PostgreSQL Practice Queries
- The development of a complete PostgreSQL practice queries guide with successful ones is an excellent method to upgrade your database management and PostgreSQL practice queries skills.
- Here you will find a set of PostgreSQL practice queries aimed at learning key topics along with their solutions, which range from the beginner level to the advanced one.
- This guide provides a variety of exercises for you to practice and become a master of SQL concepts through PostgreSQL practice queries.
- By learning how to improve performance by using the PostgreSQL practice queries, you can be a better and a more skilled data handler
- These PostgreSQL practice queries allow you to learn the essentials of database management and explore both introductory and advanced topics while you are at it.
- By the end of this ongoing PostgreSQL practice queries, you will inevitably gather the competency to manage real challenges in the scientific field.
- Engage yourself with these PostgreSQL practice queries to accelerate your knowledge of SQL acumen.
1. Create Table
- A basic query to create a table in PostgreSQL.
CREATE TABLE employees (
employee_id SERIAL PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(100) UNIQUE,
hire_date DATE
);
- Running this question will manifest an employee’s table with an employee_id that auto-increments, field names, and hiring dates.
2. Insert Data into Table
- Insert values into a PostgreSQL table.
INSERT INTO employees (first_name, last_name, email, hire_date)
VALUES (‘John’, ‘Doe’, ‘john.doe@example.com’, ‘2023-01-01’);
- It is useful to the employees table by inserting a new record with the given data under the fields of name and email.
3. Select All Records
- Select all records from a table.
SELECT * FROM employees;
- What we have here is that pressure stands for all the records from the employees table.html
4. Select Specific Columns
- Select specific columns from a table.
SELECT first_name, last_name, email FROM employees;
- This query only retrieves columns first_name, last_name, and email from the employees table.
5. Filtering Results with WHERE Clause
- Filter records based on specific conditions.
SELECT * FROM employees WHERE hire_date >’2022-01-01′;
- This request is about selecting workers who were hired after January 1, 2022.
6. Sorting Results with ORDER BY
- Sort the results after a given column in ascending or descending order.
SELECT * FROM employees ORDERBY hire_date DESC;
- This query arranges the seniority of employees according to their hire_date, taking the employees hired at the most recent time to the first place in the order.
7. Update Existing Records
- Update the fields that are already present in a record according to the need.
UPDATE employees
SET email = ‘john.doe@newdomain.com’
WHERE employee_id = 1;
- This changes the email address for employee with employee_id = 1.
8. Delete a Record
- Discard a specific record from the table.
DELETE FROM employees WHERE employee_id =1;
- This removes the employee with employee_id = 1.
9. Drop a Table
- Exclude a table from the database permanently.
DROP TABLE employees;
- This sentence is to be replaced with the employees sentence which specified the by employees table because its structure and the stored data would be deleted.
10. Alter Table to Add a Column
- Make an addition of the required column to an already existing table.
ALTER TABLE employees
ADD COLUMN phone_number VARCHAR(20);
- This modifies the employees table by adding the new phone_numer column.
11. Alter Table to Drop a Column
- Take out a column from the table that is already there.
ALTER TABLE employees
DROP COLUMN phone_number;
- This command removes the phone_number column from the employees table.
12. Alter Table to Rename a Column
- Change the name of a current column in a table.
ALTER TABLE employees
RENAME COLUMN last_name TO surname;
- This command changes the name of the last_name column to surname in the table.
13. Create Index
- Enter an index to increase search performance on some columns.
CREATE INDEX idx_email ON employees(email);
- This command makes an index in the email column in the employees table.
14. Drop an Index
- Delete an index from a table.
DROP INDEX idx_email;
- This command removes the idx_email index from the database.
15. Create a View
- Make an approach to simplify the complicated SQL query.
CREATE VIEW employee_view AS
SELECT first_name, last_name, email
FROM employees
WHERE hire_date > ‘2022-01-01’;
- This view will contain employees hired after January 1, 2022.
16. Drop a View
- Remove a view from the database.
DROP VIEW employee_view;
- This query deletes the employee_view from the database.
17. Create a Foreign Key Constraint
- Create a foreign key to enforce referential integrity between two tables.
ALTER TABLE employees
ADD CONSTRAINT fk_department
FOREIGN KEY (department_id) REFERENCES departments(department_id);
- This query links the employees table to the departments table using the FOREIGN KEY (department_id) REFERENCES departments(department_id) constraint.
18. Select Records Using JOIN
- Use a JOIN to combine data from multiple tables.
SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
JOIN departments ON employees.department_id = departments.department_id;
- This query retrieves employee name and department name from the employees and departments tables using INNER JOIN.
19. Left Join
- Use a LEFT JOIN to select all records from the left table, even if there is no match in the right table.
SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.department_id;
- This query brings back all employees with or without their assigned department if they are present.
20. Right Join
- Use a RIGHT JOIN to select all records from the right table, even if there is no match in the left table.
SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.department_id;
- This query returns all departments with no employees assigned to them.
21. Full Outer Join
- Use a full outer join and cross join together to select all records, whether there is a match in left or right table.
SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
FULL OUTER JOIN departments ON employees.department_id = departments.department_id;
- Here the query picks all records from the employees and departments tables regardless of whether there is a matching record in one of the tables.
22. Aggregate Function: COUNT
- Count certain rows of a table or a particular subset.
SELECT COUNT(*) FROM employees;
- This SQL “SELECT COUNT(*) FROM employees;” statement counts the number of the entire employee in the employees table.
23. Aggregate Function: SUM
- Add the numeric values in a specific column.
SELECT SUM(salary) FROM employees;
- This simple SQL “SELECT SUM(salary) FROM employees;” statement sums the total amount of salaries.
24. Aggregate Function: AVG
- Find the average value of a column.
SELECT AVG(salary) FROM employees;
- This query calculates the average salary of the employees.
25. Group By
- Divide information into groups that are set up by one or more columns.
SELECT department_id, COUNT(*)
FROM employees
GROUP BY department_id;
- This select statement is used to group employees by their department and counts how many employees belong to each department.
26. Having Clause
- Use the syntactic construct HAVING clause to filter results after the process of grouping using the GROUP BY.
SELECT department_id, COUNT(*)
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 10;
- This query groups employees by their department and only returns departments with more than 10 employees.
27. Select Top N Records
- Choose the first N records depending on certain criterion of the group of records.
SELECT * FROM employees
ORDER BY hire_date DESC
LIMIT 5;
- This query retrieves the top 5 most recent employees based on their hire_date.
28. Limit and Offset
- Select a subset of records with a specific range.
SELECT * FROM employees
ORDER BY hire_date DESC
LIMIT 10 OFFSET 5;
- This query retrieves records from the 6th to the 15th most recent employees.
29. Distinct Records
- Choose unique records from one column only.
SELECT DISTINCT department_id FROM employees;
- This query retrieves a list of unique department IDs from the employees table.
30. Case Statement
- Use a CASE statement to develop conditional logic in SQL queries.
SELECT first_name, last_name,
CASE
WHEN salary > 50000 THEN ‘High Salary’
WHEN salary <= 50000 THEN ‘Low Salary’
END AS salary_category
FROM employees;
- This query categorizes employees based on their salary.
31. Subquery in WHERE Clause
- The goal is to use a subquery in the WHERE clause to filter data.
SELECT * FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE department_name = ‘Sales’);
- This query is all about selecting all employees who work in the Sales department.
32. Subquery in FROM Clause
- Using a subquery in the FROM clause.
SELECT * FROM (SELECT first_name, last_name FROM employees) AS employee_names;
- Through this query, we get employees’ names by running a subquery in the FROM clause.
33. Subquery in SELECT Clause
- The aim is to use a subquery in the SELECT clause to return a value.
SELECT first_name, last_name,
(SELECT department_name FROM departments WHERE department_id = employees.department_id) AS department
FROM employees;
- This particular query is used to get both the employee names and the department names using the SELECT clause.
34. EXISTS Clause
- It can be seen that the EXISTS clause is utilized to test if rows exist in a subquery.
SELECT first_name, last_name
FROM employees
WHERE EXISTS (SELECT 1 FROM departments WHERE department_id = employees.department_id);
- This query is about finding the names of employees that are members of departments.
35. Not Exists Clause
- The NOT EXISTS clause is implemented to prevent the inclusion of the records based on the result of a subquery.
SELECT first_name, last_name
FROM employees
WHERE NOT EXISTS (SELECT 1 FROM departments WHERE department_id = employees.department_id);
- The names of employees not belonging to any department are returned by this query.
36. String Functions: LENGTH
- Get the length of strings by bringing in two strings with equal lengths through various string formatters.
SELECT LENGTH(first_name) FROM employees;
- This query is used to extract names from the column first_name and then convert them into prolix rows.
37. String Functions: CONCAT
- Concatenate more than or equal to one strings.
SELECT CONCAT(first_name, ‘ ‘, last_name) AS full_name FROM employees;
- This query combines the first_name and the last_name columns together in order to produce a full name for every employee.
38. String Functions: LOWER/UPPER
- Change the string to upper or lower case letter.
SELECT LOWER(first_name) FROM employees;
SELECT UPPER(last_name) FROM employees;
- In the first case, lower will be returned, while in the second case upper will.
39. Date Functions: NOW
- Receive the exact date and time.
SELECT NOW();
- This query provides the current date and time as per the server’s system clock.
40. Date Functions: DATE_PART
- Reserve a specific part of date or time.
SELECT DATE_PART(‘year’, hire_date) FROM employees;
- This query requests for the year from the date column that provides the calendar with the appropriate date and time of the hiring of an employee.
41. Date Functions: DATE_TRUNC
- To reduce a date or time stamp to a certain unit.
SELECT DATE_TRUNC(‘month’, hire_date) FROM employees;
- This query will cut the hire_date column at the beginning of a month and will still stay there.
42. Conditional Update with CASE
- Update records when certain conditions are met through CASE.
UPDATE employees
SET salary = CASE
WHEN department_id = 1 THEN salary * 1.1
ELSE salary * 1.05
END;
- This asynchronous function gives an employee’s salary raise predicated on the department of that employee.
43. Upsert (INSERT … ON CONFLICT)
- Insert if not exists or update if it already does because of a conflict of data.
INSERT INTO employees (employee_id, first_name, last_name)
VALUES (1, ‘Jane’, ‘Smith’)
ON CONFLICT (employee_id)
DO UPDATE SET first_name = EXCLUDED.first_name, last_name = EXCLUDED.last_name;
- This query either inserts an employee or updates a record if he/she already exists.
44. Using WITH (CTE – Common Table Expressions)
- Using CTE to make the complicated queries simpler, shorter and faster to execute.
WITH department_salaries AS (
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
)
SELECT e.first_name, e.last_name, d.avg_salary
FROM employees e
JOIN department_salaries d ON e.department_id = d.department_id;
- This is a statement that uses the Common Table Expressions to compute the average department salary by department departments.
45. Transactions: BEGIN, COMMIT, ROLLBACK
- Use the transactional principle in managing multiple queries
BEGIN;
UPDATE the employees SET salary = the salary * 1.1 WHERE the department_id = 1;
UPDATE the employees SET salary = the salary * 1.05 WHERE the department_id = 2;
COMMIT;
- This query updates the employee’s salary in two different departments as part of a transaction, thus ensuring that both updates will be executed at the same time.
46. Locking Tables
- Lock a table to prevent other transactions from doing any changes.
BEGIN;
LOCK TABLE employees IN EXCLUSIVE MODE;
— Perform operations
COMMIT;
- It stops every operation from any other transaction to modify the employees table while the session is going on.
47. Using Sequences
- Create a sequence that gets a unique number by using a sequence.
SELECT nextval(’employee_id_seq’);
- It is usual coding that extracts the serial value by a sequence, mainly used for customer_id.
48. Creating a Sequence
- To create a sequence that will automatically increment its values.
CREATE SEQUENCE employee_id_seq
START WITH 1
INCREMENT BY 1;
- This SQL statement is not going to increment duplicate values during the employee_id_seq assignment and incrementation process. The sequence will start at 1 and be incremented by 1.
49. Using EXPLAIN for Query Optimization
- Implement the query by using EXPLAIN to get the window data.
EXPLAIN SELECT * FROM employees WHERE department_id = 2;
- The result contains an execution plan which if its query is optimized better then its performance will be improved.
50. Backup a Table with COPY
- Backup a table to a CSV file by using a COPY command.
COPY employees TO’/path/to/backup/employees_backup.csv’ DELIMITER ‘,’ CSV HEADER;
- This command saves the employees table to a CSV file in order to make backups of it.
