SQL/PSQL Fundamentals - Desklib

This article covers SQL/PSQL fundamentals for beginners. It includes step-by-step guidance on creating queries, understanding ERD, retrieving data from multiple tables, using built-in functions, creating constraints, and more. The article also includes solved assignments, essays, and dissertations. Course code, course name, and university/college are not mentioned.

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

12
SQL/PSQL FUNDAMENTALS
Task 3
1.
To create a table
CREATE TABLE STUDENT (ROLL NUMBER PRIMARY KEY, NAME VARCHAR2 (20));

13
SQL/PSQL FUNDAMENTALS
To create a view
CREATE VIEW V_STUD AS SELECT NAME FROM STUDENT WHERE ROLL>1;
2. Alter a view definition
CREATE OR REPLACE VIEW V_STUD AS SELECT ROLL FROM STUDENT WHERE
ROLL>1;

14
SQL/PSQL FUNDAMENTALS
3. Drop a table and a view
SQL> DROP TABLE ITEM_2;
SQL> DROP VIEW V_STUDENT;
4. Rename a table
SQL> ALTER TABLE STUDENT RENAME TO STUDENTS;

15
SQL/PSQL FUNDAMENTALS
Truncate a table
TRUNCATE TABLE STUDENTS;
4. Update a view data
UPDATE V_STUDENTS SET NAME = 'Rachel' WHERE ROLL=1;

16
SQL/PSQL FUNDAMENTALS
Retrieve data from a view.
SELECT ROLL FROM V_STUDENTS WHERE NAME = 'Chandler';
Delete data from a View
DELETE FROM V_STUDENTS WHERE ROLL = 1;
5.
Creating Primary Key Constraint
CREATE TABLE "RESULT" (
"RES_ID" NUMBER,
"ROLL" NUMBER,
"MARKS" NUMBER,
CONSTRAINT "RESULT_PK" PRIMARY KEY ("RES_ID")
);

17
SQL/PSQL FUNDAMENTALS
Foreign Key constraint
ALTER TABLE "RESULT" ADD CONSTRAINT "RESULT_FK"
FOREIGN KEY ("ROLL")
REFERENCES "STUDENTS" ("ROLL");
7. Create an index
CREATE INDEX "STUDENTS_IDX1"
ON "STUDENTS" ("ROLL","NAME");
Task 4
Access Commands
GRANT Command
GRANT privilege_name
ON object_name
TO {user_name |PUBLIC |role_name}
[WITH GRANT OPTION];
REVOKE Command
REVOKE privilege_name
ON object_name

18
SQL/PSQL FUNDAMENTALS
FROM {user_name |PUBLIC |role_name}