Lesson: Database

1. Creating Database

create database test;

2. Deleting Database

drop database test;

3. Create Table 

-> Creating person table without constraints

create table person (
    id int,
    first_name varchar(50),
    last_name varchar(50),
    gender varchar(7),
    date_of_birth date
)

-> Creating person table with constraints

create table person (
   id bigserial not null primary key,
   first_name VARCHAR(50) not null,
   last_name VARCHAR(50) not null,
   email VARCHAR(150),
   gender VARCHAR(50) not null,
   date_of_birth DATE not null,
   country_of_birth VARCHAR(50) not null
);

4. Inserting data into table

insert into person(first_name, last_name, gender, date_of_birth)
values ('Test', 'Testov', 'male', '2000-01-01', 'Azerbaijan');

5. Selecting record(s)

-> Select all information

select * from person;

-> Select specific column(s) from table

select first_name from person;

6. Order by

 Order by means sorting data

select country_of_birth from person order by country_of_birth;

7. Distinct

Distinct gives us unique (non repeating) data(record)

select distinct country_of_birth from person order by country_of_birth;

8. Where

Where allows us to filter the data based on conditions

select * from person where gender = 'Female';

9. Limit

select * from person limit 10;

10. Offset

select * from person offset 5;

11. In

In clause replaces many or statements

select * from person where country_of_birth
in ('China', 'Brazil', 'France');

12. Between

select * from person where date_of_birth
between '2018-01-01' and '2020-01-01';

13. Like

select * from person
where email like '%.ru';

14. Group by

Grouping multiple data together

select country_of_birth, count(*) from person
group by country_of_birth;

15. Max, Min, Average

select max(price) from car;

select min(price) from car;

select avg(price) from car;

16. Update

update person set email = 'test@gmail.com'
where id = 1;

17. Foreign key

Foreign key is a column references a primary key in another table. In order for this to work the types have to be the same.

Step 1: Now let's create from the scratch person and car table with relationship:

create table car (
    id bigserial not null primary key,
    make varchar(100) not null,
    model varchar(100) not null,
    price numeric(19, 2) not null
);

create table person (
    id bigserial not null primary key,
    first_name varchar(50) not null,
    last_name varchar(50) not null,
    gender varchar(7) not null,
    email varchar(100),
    date_of_birth date not null,
    country_of_birth varchar(50) not null,
    car_id bigint references car (id),
    unique (car_id)
);

insert into person(first_name, last_name, gender, email, date_of_birth, country_of_birth)
values ('Fernanda', 'Beardon', 'Female', 'fernandab@is.gd', '1953-10-28', 'Comoros');
insert into person(first_name, last_name, gender, email, date_of_birth, country_of_birth)
values ('Omar', 'Colmore', 'Male', null, '1921-04-03', 'Finland');
insert into person(first_name, last_name, gender, email, date_of_birth, country_of_birth)
values ('Adriana', 'Matuschek', 'Female', 'amatuschek2@feedburner.com', '1965-02-28', 'Cameroon');

insert into car(make, model, price) values ('Land Rover', 'Sterling', '87665.38');
insert into car(make, model, price) values ('GMC', 'Acadia', '17662.69');

 

*** Pay attention to unique constraint in foreign key!

Step 2: Updating foreign keys columns

update person set car_id = 2 where id = 1;
update person set car_id = 1 where id = 2;

18. Inner Join

Inner join simply links two tables where the primary key and the foreign key is found in both tables.

select * from person p
inner join car c on c.id = p.car_id;

19. Left Joins

select * from person p
left join car c on c.id = p.car_id;

20. Right Joins

select * from person p
right join car c on c.id = p.car_id;

21. One to Many relationship

create table person (
    id bigserial not null primary key,
    name varchar(100) not null,
    surname varchar(100) not null
);

create table car (
    id bigserial not null primary key,
    brand varchar(100) not null,
    model varchar(100) not null,
    person_id bigint references person(id)
);

insert into person(name, surname) values ('Parvin', 'Etibarli');
insert into person(name, surname) values ('Mahammad', 'Layicov');

insert into car(brand, model, person_id) VALUES ('Mercedes', 'W124', 1);
insert into car(brand, model, person_id) VALUES ('BMW', 'F30', 2);
insert into car(brand, model, person_id) VALUES ('Toyota', 'Camry', 1);

22. Many to many relationship

create table student (
    id bigserial not null primary key,
    name varchar not null,
    surname varchar not null,
    course int not null
);

create table teacher (
    id bigserial not null primary key,
    name varchar not null,
    surname varchar not null,
    subject varchar not null
);

create table student_teacher (
    student_id bigint references student (id) not null,
    teacher_id bigint references teacher (id) not null
);

insert into student(name, surname, course)
values ('Amil', 'Hasanov', 4);
insert into student(name, surname, course)
values ('Kamil', 'Harunov', 2);
insert into student(name, surname, course)
values ('Ruslan', 'Sadiqov', 3);
insert into student(name, surname, course)
values ('Mehemmed', 'Layicov', 1);

insert into teacher (name, surname, subject)
values ('Parvin', 'Etibarli', 'Java SE');
insert into teacher (name, surname, subject)
values ('Imran', 'Yusubov', 'Java MS');

insert into student_teacher(student_id, teacher_id) values (1, 1);
insert into student_teacher(student_id, teacher_id) values (2, 1);
insert into student_teacher(student_id, teacher_id) values (3, 1);
insert into student_teacher(student_id, teacher_id) values (4, 1);
insert into student_teacher(student_id, teacher_id) values (1, 2);
insert into student_teacher(student_id, teacher_id) values (2, 2);
insert into student_teacher(student_id, teacher_id) values (3, 2);
insert into student_teacher(student_id, teacher_id) values (4, 2);

How to select:

select * from student_teacher st
join student s on s.id = st.student_id
join teacher t on st.teacher_id = t.id;