SQL/PSQL Fundamentals: Querying, Data Retrieval, and Manipulation

This assignment solution focuses on SQL/PSQL fundamentals, covering essential database operations. It includes creating and executing queries to display and manipulate data from single and multiple tables. The solution demonstrates how to limit and sort query outputs, interpret Entity Relationship Diagrams (ERDs), and resolve ambiguous column names. Key concepts such as JOIN queries, GROUP BY clauses, built-in functions, nested functions, and subqueries are explored. Furthermore, the assignment covers data manipulation techniques like inserting, updating, and deleting data within tables, as well as creating and altering views. Finally, it addresses database administration tasks such as creating tables, views, constraints (primary and foreign keys), and indexes, along with managing user access privileges using GRANT and REVOKE commands. Desklib provides this document and many other resources for students.

Running head: SQL/PSQL FUNDAMENTALS
SQL/PSQL Fundamentals
Name of the student:
Name of the University:
Author note:

1
SQL/PSQL FUNDAMENTALS
Table of Contents
Task 1...............................................................................................................................................2
Task 2...............................................................................................................................................4
Task 3...............................................................................................................................................8
Task 3.............................................................................................................................................12
Task 4.............................................................................................................................................17

2
SQL/PSQL FUNDAMENTALS
Task 1
1. Creating a query (Display all customer records)
SELECT * FROM CUSTOMER;
2. Limiting the number of outputs (Display only first 2 customer records)
SELECT * FROM CUSTOMER WHERE ROWNUM <= 2;

3
SQL/PSQL FUNDAMENTALS
3. Sorting a table
Ascending Order Sort
SELECT * FROM CUSTOMER
ORDER BY CUST_ID ASC;
Descending Order sort
SELECT * FROM CUSTOMER
ORDER BY CUST_ID DSC;

4
SQL/PSQL FUNDAMENTALS
Task 2
1. To understand an ERD
The ERD in consideration is presented above. This represents the entity relationship of a
simple Customer-Item purchase model. From the ERD it can be concluded that the customer has
a primary key Cust_ID that uniquely identifies each customer. Each item can be identified by
their ID as well. Each customer can buy many items and each item type can be bought by many
customers. The customer entity has therefore a many to many relationship. Hence, the
intermediate Purchase table has the customer Id and Item id as foreign keys and a quantity field.
Each row in the Purchase table will represent
3. Retrieve data from multiple tables
SELECT CUSTOMER.NAME, ITEM.NAME FROM CUSTOMER, ITEM;

5
SQL/PSQL FUNDAMENTALS
4. Using a JOIN query to retrieve data
SELECT CUSTOMER.NAME, PURCHASE.QUANTITY FROM CUSTOMER INNER JOID
PURCHASE ON PURCHASE.CUST_ID = CUSTOMER.CUST_ID;
5. Using GROUP BY query

6
SQL/PSQL FUNDAMENTALS
a) Single Column
SELECT CUST_ID, COUNT(*) FROM PURCHASE GROUP BY CUST_ID;
b) Multiple Column
SELECT CUST_ID, ITEM_ID, COUNT(*) FROM PURCHASE GROUP BY CUST_ID,
ITEM_ID;
6. Using a built-in function (Average)
SELECT AVG(PRICE) FROM ITEM;

7
SQL/PSQL FUNDAMENTALS
7. Using nested function (Floor value of average)
SELECT FLOOR(AVG(PRICE)) FROM ITEM;
8. Limit the rows retrieved by a query by use of the HAVING clause
SELECT CUST_ID FROM PURCHASE GROUP BY CUST_ID HAVING COUNT(CUST_ID)
> 2;

8
SQL/PSQL FUNDAMENTALS
9. Query with a sub-query.
SELECT NAME FROM CUSTOMER WHERE CUST_ID IN (SELECT CUST_ID FROM
PURCHASE WHERE ITEM_ID = 15);
Task 3
1. A)

9
SQL/PSQL FUNDAMENTALS
1. B) Copy from another table
INSERT INTO ITEM_2 SELECT * FROM ITEM;
2. Update table data
Update table based on a condition from same table
Update table based on a condition from another table

10
SQL/PSQL FUNDAMENTALS
3. Delete table data
Delete data based on a condition from same table
Delete data based on a condition from another table

11
SQL/PSQL FUNDAMENTALS
4. Create a script