NOTES FOR POSTGRESS SQL

-------------------------------------------- DDL
------------------------------------------------------------
-create
-alter
-drop
-truncate

--------------------------------------------- DML
-----------------------------------------------------------
-delete
-insert
-update update employee set emp_name = 'princeley' where emp_name = 'prince';
-add columns and drop columns
-rename

-------------------------------------------- DQL
------------------------------------------------------------
-select

-------------------------==============------------------aggregate functions
--------------------------------

-MIN
-MAX
-AVG
-SUM
-DISTINCT COUNT

-primary key constrain does not allow NULL values.

-you can have multiples unique keys iin a table but not primary keys
-unique key allows NULL values if not null constrain is not defines
-serial is used as an auto increment key word in postgress
-if specify a serial increment no need putting data [Link] wont work
if you want to enforce referencial intergrity on a table note that the table you
are referencingt in another table that field you are referencing mustmust be a
primary key or have a unique constrain on that field
-remember you must create the table you wish to reference first too else youll get
an erroe table dies not exist
e.g see below.
-delete from students, truncate table students do the same thing but with the first
it deletes line after line and
the other in bulk but leaves the table structure.

-------------------------------------------- Commands
------------------------------------------------------

-Alter e.g alter table students add foreign key(student_dept) references

departments(dept_name);

-create e.g delete from students where student_dept='Economics';

-drop e.g drop table students;

-insert into e.g insert into students (student_name,student_dept, student_age)
values
('Norman','Physics',20);

-select e.g select * from students;

-alter table Teachers add column teacher_city varchar(20) not null;

-alter table teachers drop column teacher_city;

-alter table teachers alter column teacher_name type varchar(15);

-alter table teachers rename column teacher_age to teacher_experience;

-select staff_position from staff; selecting columns from a table

-select staff_name,staff_position from staff ;

------------------------------------------ Constrains
-------------------------------------------------------
note DELETE * FROM table is not valid in PostgreSQL.
In PostgreSQL, you do not use * in a DELETE statement`.
DELETE FROM table_name WHERE condition;

-unique constrain
-check constrian e.g check(teacher_age>=30);
-not null constrain
-default constrain
-distinct constrian to fetch unique valuese.g select (distinct location) from
employees;
-count claus count unique valuese.g select count(distinct location) as
total_location from employees
-foreign key
-order by clause store date in a particular sequence ascending or descending order
e.g select fam_name from fam order by fam_name;
select fam_name from fam order by fam_name desc;
- sort clause
-limit clauese limits the number of data we wish to fetch from a query in a table
[Link] fam_children from fam order by fam_children limit 3;
-between clause e.g select count(*) as number_of_employees where salary between
23000 and 30999;
select fam_children from fam order by fam_children desc limit 3;
-offset e.g select * from fam order by fam_children asc limit 5 offset 3;
-order by clause e.g select emp_name, from employee order by emp_name; this will
select the names and order from A to Z
select emp_name from employee order by emp_name desc; will do the reverse;
select emp_name, salary from employee order by salary; will select names from table
and order by salary from smallest salary to biggest salary
select emp_name, from employee order by emp_name desc will do the reverse;
-update clause e.g update employee set location = 'douala' where name in ('kwa
adella','mouala');
-delete clause e.g. delete * from employee where id = 1;
-rename clause e,g alter table courses rename column course_code to code;

------------------------------------------------------------WHRER
CONDITION--------------------------------------------------------------------------
------
where condition works with rollable data not aggregate data ((aggregation means
grouping ))
where condition should be applied before grouping data or cannot work after a group
by(categorical clause) clause

e.g

select name, salary from employee group by name salary where salary > 5000 ; WONT
WORK

select name, salary from employee where salary > 5000 group by name salary,name ;
WILL WORK

-----------------------------------------------------------HAVING
CLAUSE-----------------------------------------------------------------------------
------
-having clause is used with aggregate data
-having clause is used after aggragation

e.g

---------------------------------------------------------- JOINS AND UNIONS

-------------------------------------------------------------------------------

combinding data across multiple tables to display as a single table. note there has
to be a commmon field in both tables to jion both tables else the tables are called
disjoint tables.

inner join----------matching data from both the tables based on the join clause
given. you can think of it as an intersection from both tables

full join-------------------------- displays all data in both tables can be

considered a unoin between the two tables

left join---------------------------displays all rows in the left table and

matching rows of data in the right column

right------------------------------- displays all rows in the right table and

matching rows in the left table

right only----------------------------displays data that is only availableon the

right table and not on the left table ((table 1 -table 2))

left only----------------------------- displays data that is available on the left

table only and not on the right table ((table 2 - table 1))

full outer join--------------------------displays only distinct data avaialable in

both tables what is in dable 1 and absecent from table two and
what is in table 2 and abscent in
table 1

------------------------------------------------------------------ EXAMPLES
--------------------------------------------------------------------------------

/* inner join */
select employees.first_name, employees.last_name, departments.department_name
from employees
inner join departments
on employees.employee_id = departments.department_id;

/* left join */
select employees.first_name, employees.last_name, departments.department_name
from employees
left join departments
on employees.employee_id = departments.department_id;

/* right join */
select employees.first_name, employees.last_name, departments.department_name
from employees
right join departments
on employees.employee_id = departments.department_id;

/* full join */
select *
from employees
full join departments
on employees.employee_id = departments.department_id;

------------------------------------------------------------- UNIONS
-------------------------------------------------------------------------------

rules corresponding tables must have the same number of columns else specify the
field you want to combine with the other field e.g

select first_name from employees union select department_name from departments;

out put

"first_name"
"Finance"
"David"
"Bob"
"HR"
"Marketing"
"Charlie"
"Eva"
"IT"
"Alice"

it out puted or combined the first names and the department names in a single table

------------------------------------------------ VIEWS AND TABLES IN UNIONS

------------------------------------------------------------------------------

when we join data on tables and save it as a view it works and is good bec with a
view it dosent occupy physical space as when we save it as a table e.g

create view innerjoinview as

select first_name, salary, department_name
from employees
inner join departments
on employees.employee_id = departments.department_id;

select * from innerjoinview;

will create a view

while-------------------------------------------------------
create table innerjoinstudents as
select employees.first_name, employees.last_name, departments.department_name
from employees
inner join departments
on employees.employee_id = departments.department_id;

select * from innerjoinstudents ;

more examples include----------------------------------------

create view union_names_and_dept as

select first_name from employees union select department_name from departments
order by first_name;

select * from union_names_and_dept;

create table union_namesand_dept as

select first_name from employees union select department_name from departments
order by first_name;

select * from union_namesand_dept;

when we create a table from a union, the table occupies memory space but when we
create it as a view,
it is seen as an instance and it does not occupy space

-------------------------------- COMBINDING DATA FROM THREE OR MORE TABLES IN

POSTGRESS ---------------------------------------------------

TABLE 1
TABLE 2
TABLE 3
TABLE 4

This is the concept if i have three tables and want to run an inner join between
say table 1 and 4, and then maybe use the results to run a join with another
table , see this.

select * from table1

inner join table2
on table1_id = table2_id;

this is a simple join between tables 1 and 2. now if we wish to run an inner join,
left join, right, full join with a third table, we sinply specify this [Link] do
this

select * from table1

inner join table2
on table1_id = table2_id;
left join table3 on table1_id = table3_id;

another......

select * from table4

right join table1
on table4_id = table1_id;
full join table3 on table4_id = table1_id; and so on. just reason the logic and
you'll be there.

.......................NOTE THE ABOVE WILL CONTINUE TO HAPPEN ONLY AS LONG AS THERE

IS A COMMONN FIELD BETWEEN THEM I MEAN THE TABLES......................

----------------------------------------------------------------SUB QUESRIES IN
POSTGRESS.................................................................

this is simply writting quesries inside of quesries e.g

the below quesry selects the salaries with the description as follows.

select first_name,salary from employees where salary >= 60000 and salary <= 73000;

We wish to do the same oposite thing using sub-queries we want to select data which
dosent exist within this range and remember with sub- queries it is excuted from
inside to out, e.g

select salary from employees where salary not in

(select first_name,salary from employees where salary >= 60000 and salary <=
73000);

but this will pose an error

ERROR: subquery has too many columns
LINE 1: select salary from employees where salary not in
hence you need to specify just the single column we needing in the subquerry.

select salary from employees where salary not in

(select salary from employees where salary >= 60000 and salary <= 73000);

NOTE...........................to add more columns its done to the outer querry

e.g.......................................................................

select first_name,salary from employees where salary not in

(select salary from employees where salary >= 60000 and salary <= 73000);

BUT IF THE FIRST NAME COME IN THE INNER QUERRY IT WONT WORK;

-------------------------------------------------------------how to select data

from multiple tables-----------------------------------------------------

select * from students

inner join books
on students.student_id = books.book_id
inner join librarians
on students.student_id = librarians.librarian_id
inner join transactions
on students.student_id = transactions.transaction_id;

-------------------------------------------------------- triggers and functions

---------------------------------------------------------------------

triggers are invoked when you inserting, deleting, or updating data from a table.
triggers are like scripts to be exercuted on command.

// lets write an example of a function we might want to call //

CREATE FUNCTION calculate_total_price()

RETURNS TRIGGER
AS $$
DECLARE // here we declare any local variables
e.g
total numeric;
BEGIN
total = [Link] * [Link];
new.tatal_price = tatal;
return new;
// where sales and quantity and tatal_price are fields of the table
we working on
END;
$$ langauge plpgsql;

// now lets write a trigger to initiate or invoke this function when ever
we may require it to work //

**************************************** NOTE WE USING THIS TABLE AM CREATING BELOW

***************************************************************

create table employee(

emp_id serial primary key,
emp_name varchar(225),
emp_firstinstallment int,
emp_secondinstallment int
);

insert into employee (emp_name,emp_firstinstallment,emp_secondinstallment)

values('Della',1200,1000),
('Marion',200,400),
('Maureen',9000,3400);

********** NOTE THAT HERE I HAVENT ADDED THE EXTRA COLUMN TO CALCULATE THE TOTAL
SALARY SO ILL ADD IT UP*********************************************

alter table employee add column emp_totalsalry int;

**************** LETS WRITE THE FUNCTION TO CALCULATE THE TOTAL SALARY AND THE
TRIGGER***************************************************************

create function totalsalary()

returns trigger
as $$
declare
total numeric;
begin
total = new.emp_firstinstallment + new.emp_secondinstallment;
new.emp_totalsalry = total;
return new;
end
$$ language plpgsql;

*************************** NEW KEYWORD EXPLAINED

***********************************************************************************
**************

The NEW key word is used to access a column or calls or references a column that
already exist in the table

drop function totalsalary(); *********** STATEMENT TO DROP THE FUNCTION

*******************************************************************************

create trigger totalsalary

before insert
on employee
for each row
execute procedure totalsalary();

drop trigger totalsalary on employee; ******************** STATEMENT TO DROP THE

TRIGGER *************************************************************

*****************************************************example 02 on
triggers***************************************************************************
**

lets enable a coulumn to automatically update the timestamp of data anytime its
updated so lets add a column
hence lets add a column call last_updated_time and reference it okey?

alter table employee add column last_updated_time timestamp;

create function update_to_current_time()

returns trigger
as $$
declare
begin
new.last_updated_time = current_timestamp;
return new;
end;
$$ language plpgsql;

drop funtion update_time();

create trigger update_time_trigger

before insert
on employee
for each row
execute procedure update_to_current_time();
******************************************** INDEXES IN POSTGRESS
***********************************************************************************
**

indexes are like the glossery of a book. it provides a quick way to locate specific
data. think of an index as a map that helps you locate navigate data in your tables
more [Link] you create an index on a column or a set of columns,postgress
builds a data structure that contains the values in those columns, aloong with a
pointer to the location of the corresponding rows in the table

when you declare or crete an index for column or group of columns, you make the
database get or reteieve the data faster than the normal time period it had to that
is if it has=d to search a column line by line creating the index lets it to go
just to that particular spot and get it taht the good news.

create index index_name on tablename(column name)

e.g create index employee_firstinstallment_index on employee(emp_firstinstallment);

how to drop an index on a column.

drop index indexname;

************************************************* DCL COMMANDS

***********************************************************************************
********

DATA CONTROL LANGUAGE

(((((((((( grant, revoke )))))))))

A) GRANT : it is used to add members or give permissions on a database.

*******create user Adella password '70705'; creates user Adella with password
70705;

and refereshing the side bar for login/grouproles you'll find the new user
inoerder to log in as new user check on title bar theres a drop down menu where
you'll click and create a new connection

update employee set emp_totalsalry = 600 where emp_name = 'marion';

cant update too unless given permission to update

**********grant update on employee to Adella;

grants the update permission only to user Adella

**********grant select on employee to Adella;

grants the select permission only to user Adella

*********grant insert on employee to Adella;

grants the insert permission only to user Adella

*********grant delete on employee to Adella;

grants the delete permission only to user Adella
*********************** INORDER TO REMOVE THE PERMISSION GIVEN TO PARTICULAR USER
FOR VARIOS REASON WE USE REVOKE ******************************************

revoke update on employee from Adella; *****************removes the ability to

update data from the table

revoke insert on employee from Adella; *****************removes the ability to

insert data from the table

revoke delete on employee from Adella; *****************removes the ability

to delete data from the table

revoke select on employee from Adella; *****************removes the

ability to select that is ability view data from the table

INORDER TO GRANT ALL PERMISSIONS ON A TABLE TO A PARTICULAR USER WE JUST DO THIS

grant all on employee to Adella;

REVOKING ALL WE JUST

revoke all on employee from Adella;

***************************************************** HOE TO DROP A USER

**************************************************************************

DROP USER USER_NAME

drop user Adella;

******************************************** BEGIN,,, ROLLBACK,,,,, COMMIT,,,,

*************************************************************************

the begin key word is used to tell the data base to begin keeping track of your
transactions to that incase you need to rollback it will have the data tracked for
you to replace it.
e.g

BEGIN;

the roll back keyword is used to recover data we must have deleted . you can also
view it as an undo botton in databases if and only if be assigned the data base to
begin the tractong of every transactions we are working on.e.g

ROLLBACK;

the commit key word is used to tell the data base we are don tracking so it can
stop thr tracking e.g

COMMIT;

******************************************************* CASE STATEMENTS

***********************************************************************************
*

select emp_name, emp_totalsalry,

case
when emp_totalsalry > 2200 then 'salary greater than 2200'
when emp_totalsalry < 2200 then 'salary less than 2200'
when emp_totalsalry = 2200 then 'salary is equal to 2200'

else 'Employee total salary not calculated'

end

from employee;
results

"emp_name" "emp_totalsalry" "case"

"Olyn" 100 "salary less than 2200"

"Della" 2200 "salary is equal to 2200"
"Marion" 600 "salary less than 2200"
"Maureen" 12400 "salary greater than 2200"
"princeley" null "Employee total salary not calculated"
"lovelyn" null "Employee total salary not calculated"

with the case note the syntax it acrually automatically creates a column to
describr this data according to your reading so, to give this case column an alias
name we go below the end case keyword and do this

select emp_name, emp_totalsalry,

case
when emp_totalsalry > 2200 then 'salary greater than 2200'
when emp_totalsalry < 2200 then 'salary less than 2200'
when emp_totalsalry = 2200 then 'salary is equal to 2200'

else 'Employee total salary not calculated'

end
as stock_details
from employee;

NOTE: (((but if you run this you'll get a syntax eror near from this is bec the as
before the alias name has to be in Caps hence)))

select emp_name, emp_totalsalry,

case
when emp_totalsalry > 2200 then 'salary greater than 2200'
when emp_totalsalry < 2200 then 'salary less than 2200'
when emp_totalsalry = 2200 then 'salary is equal to 2200'

else 'Employee total salary not calculated'

end
AS stock_details
from employee;

NOTE (( WE CAN ALWAYS STORE IT AS ANOTHER TABLE OR AS A VIEW))

CREATE VIEW VIEW_NAME on EMPLOYEE(

select emp_name, emp_totalsalry,
case
when emp_totalsalry > 2200 then 'salary greater than 2200'
when emp_totalsalry < 2200 then 'salary less than 2200'
when emp_totalsalry = 2200 then 'salary is equal to 2200'

else 'Employee total salary not calculated'

end
as stock_details
from employee);

CREATE TABLE TABLE_NAME ON EMPLOYEE(

select emp_name, emp_totalsalry,
case
when emp_totalsalry > 2200 then 'salary greater than 2200'
when emp_totalsalry < 2200 then 'salary less than 2200'
when emp_totalsalry = 2200 then 'salary is equal to 2200'

else 'Employee total salary not calculated'

end
as stock_details
from employee);

********************************************************************* DATA
VISUALISATION ******************************************************************

Data visualization is the process of using visual elements like charts, graphs, or
maps to represent data. It translates complex, high-volume, or numerical data into
a visual representation that is easier to process.

OR

Data visualization is the graphical representation of information and data. By

using visual elements like charts, graphs, and maps, data visualization tools
provide an accessible way to see and understand trends, outliers, and patterns in
data.

OR

Data visualization is the practice of translating information into a visual

context, such as a map or graph, to make data easier for the human brain to
understand and pull insights from. The main goal of data visualization is to make
it easier to identify patterns, trends and outliers in large data sets.

DATA VISUALISATION TOOLS INCLUDE Some popular data visualization tools that can be
used with databases include: Tableau, Google Charts, Power BI, QlikView, Grafana,
Datawrapper, Infogram, Sisense, Plotly, and Zoho Analytics; all of which allow
users to create charts, graphs, maps, and dashboards from database data.

**************************************** ADVANTAGES OF DATA VISUALISATION

***********************************************************
it helps people see, interact with, and better understand data. Whether simple or
complex, the right visualization can bring everyone on the same page, regardless of
their level of expertise.
Easily sharing information.
Interactively explore opportunities.
Visualize patterns and relationships.

**************************************** DISADVANTAGES OF DATA VISUALISATION

***********************************************************

Biased or inaccurate information.

Correlation doesn’t always mean causation.
Core messages can get lost in translation.

**************************************** TYPES DATA VISUALISATION

***********************************************************

Area Map: A form of geospatial visualization, area maps are used to show specific
values set over a map of a country, state, county, or any other geographic
location. Two common types of area maps are choropleths and isopleths. Learn more.

Bar Chart: Bar charts represent numerical values compared to each other. The length
of the bar represents the value of each variable. Learn more.
Box-and-whisker Plots: These show a selection of ranges (the box) across a set
measure (the bar). Learn more.

Bullet Graph: A bar marked against a background to show progress or performance

against a goal, denoted by a line on the graph. Learn more.

Gantt Chart: Typically used in project management, Gantt charts are a bar chart
depiction of timelines and tasks. Learn more.

Heat Map: A type of geospatial visualization in map form which displays specific
data values as different colors (this doesn’t need to be temperatures, but that is
a common use). Learn more.

Highlight Table: A form of table that uses color to categorize similar data,
allowing the viewer to read it more easily and intuitively. Learn more.

Histogram: A type of bar chart that split a continuous measure into different bins
to help analyze the distribution. Learn more.

Pie Chart: A circular chart with triangular segments that shows data as a
percentage of a whole. Learn more.

Treemap: A type of chart that shows different, related values in the form of
rectangles nested to
-------------------------------------------------------- GPT GENERATED
EXERCISES-------------------------------------------------------------------

database creation,table creation,primary key constrains, foreign key

constraint,unique ,check,not null default, distinct, constrains, count claus,order
by , select, limit claus, offset claues, commands such as create, alter, drop,
truncate,delete,insert,update,add columns, remane, aggregate functions such as
min,max,ang,sum, having, group by, i saw all of this in my training i want you to
give me instructions to create table data then from there give out plenty exercisee
to work on the table. exam standard for university and even add interview questions

Got it! Here’s a comprehensive plan:

---

### 1. **Instructions to Create Table Data**

#### Database Creation:

```sql
CREATE DATABASE university_db;
```

#### Connect to the Database:

```sql
\c university_db
```

#### Table Creation:

```sql
CREATE TABLE students (
student_id SERIAL PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
age INT CHECK (age >= 17),
email VARCHAR(100) UNIQUE NOT NULL,
enrollment_date DATE DEFAULT CURRENT_DATE
);

CREATE TABLE courses (

course_id SERIAL PRIMARY KEY,
course_name VARCHAR(100) NOT NULL,
course_code VARCHAR(10) UNIQUE NOT NULL,
credits INT NOT NULL CHECK (credits > 0)
);

CREATE TABLE enrollments (

enrollment_id SERIAL PRIMARY KEY,
student_id INT NOT NULL REFERENCES students(student_id) ON DELETE CASCADE,
course_id INT NOT NULL REFERENCES courses(course_id) ON DELETE CASCADE,
grade CHAR(1) CHECK (grade IN ('A', 'B', 'C', 'D', 'F')),
enrollment_date DATE DEFAULT CURRENT_DATE
);
```
#### Insert Sample Data:
```sql
-- Insert data into students
INSERT INTO students (first_name, last_name, age, email) VALUES
('John', 'Doe', 20, '[Link]@[Link]'),
('Jane', 'Smith', 22, '[Link]@[Link]'),
('Alice', 'Johnson', 19, '[Link]@[Link]');

-- Insert data into courses

INSERT INTO courses (course_name, course_code, credits) VALUES
('Database Systems', 'DB101', 3),
('Introduction to Programming', 'CS101', 4),
('Data Structures', 'CS102', 3);

-- Insert data into enrollments

INSERT INTO enrollments (student_id, course_id, grade) VALUES
(1, 1, 'A'),
(1, 2, 'B'),
(2, 1, 'A'),
(3, 3, 'C');
```

---

### 2. **Exercises**

#### **Basic Queries**

1. List all students and their details.
2. Display the course names and codes for all courses.
3. Show all enrollments, including student names and the courses they enrolled in.

#### **Intermediate Queries**

4. List students older than 20.
5. Find students who haven’t enrolled in any course.
6. Count how many students are enrolled in each course.

#### **Constraints and Modifications**

7. Add a `gender` column to the `students` table.
8. Rename the `course_code` column to `code`.
9. Delete the course with the lowest number of credits.

#### **Aggregate Functions**

10. Calculate the average age of students.
11. Find the maximum credits among all courses.
12. Show the total number of enrollments.

#### **Advanced Queries**

13. Display students who got an 'A' in any course.
14. List courses with more than one student enrolled.
15. Find the student who enrolled most recently.

#### **Complex Queries**

16. Show the student with the highest average grade.
17. List courses that have no enrollments.
18. Display the total credits each student is taking.

#### **Command-Based Exercises**

19. Drop the `enrollments` table and recreate it.
20. Truncate the `students` table and insert new data.
---

### 3. **Interview Questions**

1. Explain the difference between `TRUNCATE`, `DELETE`, and `DROP`.
2. What is the purpose of the `HAVING` clause, and how does it differ from `WHERE`?
3. How would you ensure referential integrity when designing a database?
4. What are the differences between `UNIQUE` and `PRIMARY KEY` constraints?
5. How do you optimize a query with large datasets in PostgreSQL?

---

Try these and let me know how they work for you! I can adjust or add more exercises
if needed. 😊

------------------------------------ Exercise 002

-------------------------------------------------------------------

Here’s an expanded and more engaging **Exercise 2**, with a variety of questions
designed to integrate all the concepts you’ve learned. It includes scenario-driven
questions and tasks requiring advanced thinking, as well as practical operations.

---

### **Scenario: University Database Management**

You're tasked with maintaining and analyzing a university database. Your goal is to
answer various questions and perform operations based on the data in the following
tables:

1. **Students**
Stores details about students.

```sql
CREATE TABLE students (
student_id SERIAL PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
date_of_birth DATE NOT NULL,
enrollment_date DATE DEFAULT CURRENT_DATE,
gender CHAR(1) CHECK (gender IN ('M', 'F'))
);
```

2. **Courses**
Stores information about courses.

```sql
CREATE TABLE courses (
course_id SERIAL PRIMARY KEY,
course_name VARCHAR(100) NOT NULL,
course_code VARCHAR(10) UNIQUE NOT NULL,
credits INT CHECK (credits > 0),
department VARCHAR(50) NOT NULL
);
 ```

3. **Enrollments**
Tracks which students are enrolled in which courses and their grades.

```sql
CREATE TABLE enrollments (
enrollment_id SERIAL PRIMARY KEY,
student_id INT NOT NULL REFERENCES students(student_id) ON DELETE CASCADE,
course_id INT NOT NULL REFERENCES courses(course_id) ON DELETE CASCADE,
grade CHAR(1) CHECK (grade IN ('A', 'B', 'C', 'D', 'F')),
enrollment_date DATE DEFAULT CURRENT_DATE
);
```

4. **Instructors**
Contains details about course instructors.

```sql
CREATE TABLE instructors (
instructor_id SERIAL PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
hire_date DATE NOT NULL,
department VARCHAR(50) NOT NULL
);
```

---

### **Expanded Questions**

#### **Basic Operations**

1. Create a new student named "Alex Taylor" (email: [Link]@[Link]) with a
date of birth of "2002-06-15".
2. Insert a new course titled "Artificial Intelligence" (course code: AI201) with 3
credits under the "Computer Science" department.

#### **Data Manipulation**

3. Enroll "Alex Taylor" in the "Artificial Intelligence" course.
4. Update the grade for "Alex Taylor" in the "Artificial Intelligence" course to
'A'.

#### **Constraints**
5. Add a constraint to ensure no two students can share the same email address.
6. Add a column `phone_number` to the `students` table with a constraint that it
must contain exactly 10 digits.

#### **Filtering Data**

7. List all students who are enrolled in at least two courses.
8. Find all courses offered by the "Computer Science" department.

#### **Aggregation**
9. Count the total number of students in each department.
10. Calculate the average grade of all students enrolled in the "Artificial
Intelligence" course.

#### **Joining Tables**

11. List all students along with the courses they are enrolled in and their grades.
12. Find the names of instructors teaching courses in which "Alex Taylor" is
enrolled.

#### **Advanced Queries**

13. Display all students who have not enrolled in any course.
14. Identify the course with the highest number of enrollments.
15. List the top three students (by grade average) and the number of courses they
are enrolled in.

#### **Using Functions**

16. Create a function that calculates the age of a student based on their date of
birth.
17. Write a query to display all students who are older than 21 years.

#### **Group By and Having**

18. Group students by gender and show the total number of enrollments for each
gender.
19. List departments with more than 5 students enrolled.

#### **Modifications**
20. Rename the `department` column in the `courses` table to `faculty`.
21. Delete all courses that have no students enrolled.

---

### **Challenge Section**

22. Write a query to calculate the GPA of each student, assuming:
- 'A' = 4 points, 'B' = 3 points, 'C' = 2 points, 'D' = 1 point, 'F' = 0 points.
23. Find the student who has the highest GPA.

24. Create a trigger to automatically update the `enrollment_date` column to the

current date whenever a new record is inserted into the `enrollments` table.

25. Write a query to find instructors who have been working for more than 10 years
in their department.

---

### **Interview-Style Questions**

1. Explain how foreign key constraints help maintain data integrity.
2. What is the difference between `DISTINCT` and `GROUP BY`?
3. How would you design a database to avoid data redundancy?
4. What are the pros and cons of using composite primary keys?
5. How does PostgreSQL handle transactions and rollbacks?

---

Let me know if you want solutions to any of these or further assistance! 😊

---------------------------------- Exerise 003

-----------------------------------------------------------------------------------
--------

Sure! Here's a **new engaging Exercise 3** that combines creativity, critical
thinking, and comprehensive PostgreSQL practice.

---
### **Exercise 3: Library Management System**

You're designing a database for a university library. The library system tracks
books, students who borrow books, librarians managing the library, and
borrow/return transactions.

---

### **Database Schema**

#### **1. Books**

Holds details of books in the library.
```sql
CREATE TABLE books (
book_id SERIAL PRIMARY KEY,
title VARCHAR(255) NOT NULL,
author VARCHAR(100) NOT NULL,
genre VARCHAR(50),
published_year INT CHECK (published_year >= 1800 AND published_year <=
EXTRACT(YEAR FROM CURRENT_DATE)),
copies_available INT CHECK (copies_available >= 0)
);
```

#### **2. Students**

Tracks library users.
```sql
CREATE TABLE students (
student_id SERIAL PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
phone_number VARCHAR(10) CHECK (phone_number ~ '^\d{10}$'),
enrollment_year INT NOT NULL CHECK (enrollment_year >= 2000 AND enrollment_year
<= EXTRACT(YEAR FROM CURRENT_DATE))
);
```

#### **3. Librarians**

Manages library operations.
```sql
CREATE TABLE librarians (
librarian_id SERIAL PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
hire_date DATE NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL
);
```

#### **4. Transactions**

Records book borrow and return events.
```sql
CREATE TABLE transactions (
transaction_id SERIAL PRIMARY KEY,
student_id INT NOT NULL REFERENCES students(student_id) ON DELETE CASCADE,
book_id INT NOT NULL REFERENCES books(book_id) ON DELETE CASCADE,
librarian_id INT NOT NULL REFERENCES librarians(librarian_id) ON DELETE SET
NULL,
 borrow_date DATE DEFAULT CURRENT_DATE,
return_date DATE,
status VARCHAR(10) CHECK (status IN ('Borrowed', 'Returned')) NOT NULL
);
```

---

### **Expanded Questions**

#### **Table and Data Manipulation**

1. Insert 5 sample records into each table (students, books, librarians, and
transactions) with appropriate data.
2. Add a column `isbn` to the `books` table with a unique constraint.

#### **Basic Queries**

3. List all books with their authors and genres.
4. Find all students who borrowed books in the current year.

#### **Filtering and Constraints**

5. Display books published after 2015 that have more than 2 copies available.
6. Find students whose emails are not correctly formatted (e.g., missing '@').

#### **Joining Tables**

7. Retrieve a list of books borrowed by a specific student (e.g., student_id = 2),
including the borrow date and status.
8. List all librarians who managed transactions for books authored by "George
Orwell".

#### **Aggregations**
9. Count the total number of books borrowed by each student.
10. Find the average, minimum, and maximum number of books available across all
genres.

#### **Group By and Having**

11. Group books by genre and show the total number of books available for each
genre.
12. Find students who borrowed more than 3 books.

#### **Advanced Queries**

13. Display all overdue transactions (borrowed more than 30 days ago and not
returned).
14. Find the top 3 students who borrowed the most books.

#### **Modifications**
15. Update the `copies_available` column in the `books` table to decrease by 1
whenever a book is borrowed.
16. Delete all transactions where the `return_date` is older than 5 years.

#### **Functions and Triggers**

17. Write a function to calculate the fine for overdue books at $1 per day after
the 30-day limit.
18. Create a trigger to automatically update the `status` of a transaction to
"Returned" when a `return_date` is added.

#### **Scenario-Based Queries**

19. A student wants to borrow a book but only if it's available. Write a query to
check availability and insert a new transaction for the student if the book is
available.
20. A librarian wants a report showing the most popular books (borrowed the most)
and the students who borrowed them.

#### **Backup and Recovery**

21. Write a query to back up all data from the `transactions` table into a new
table `transactions_backup`.
22. Restore data from the `transactions_backup` table to the `transactions` table
in case of accidental deletion.

---

### **Challenge Section**

23. Create a view `borrow_summary` to display a summary of all borrow transactions,
including student names, book titles, and transaction status.
24. Optimize a query to find all students who have borrowed books from multiple
genres.

---

This exercise combines real-world problem-solving with comprehensive use of

PostgreSQL features. Let me know if you'd like solutions or tweaks to any part! 😊