Azamat
216 lines
2.8 KiB
SQL
216 lines
2.8 KiB
SQL
CREATE TABLE IF NOT EXISTS amaksatov.users (
|
||
id SERIAL PRIMARY KEY,
|
||
name VARCHAR(100),
|
||
age INT,
|
||
email VARCHAR(255),
|
||
registration_date DATE
|
||
);
|
||
|
||
CREATE TABLE IF NOT EXISTS amaksatov.courses (
|
||
id SERIAL PRIMARY KEY,
|
||
title VARCHAR(255),
|
||
category VARCHAR(100),
|
||
created_at DATE
|
||
);
|
||
|
||
CREATE TABLE IF NOT EXISTS amaksatov.lessons (
|
||
id SERIAL PRIMARY KEY,
|
||
course_id INT,
|
||
title VARCHAR(255),
|
||
duration_min INT,
|
||
FOREIGN KEY (course_id) REFERENCES amaksatov.courses(id)
|
||
);
|
||
|
||
CREATE TABLE IF NOT EXISTS amaksatov.enrollments (
|
||
id SERIAL PRIMARY KEY,
|
||
user_id INT,
|
||
course_id INT,
|
||
enrolled_at date,
|
||
FOREIGN KEY (user_id) REFERENCES amaksatov.users(id),
|
||
FOREIGN KEY (course_id) REFERENCES amaksatov.courses(id)
|
||
);
|
||
|
||
CREATE TABLE IF NOT EXISTS amaksatov.lesson_views (
|
||
id SERIAL PRIMARY KEY,
|
||
user_id INT,
|
||
lesson_id INT,
|
||
viewed_at TIMESTAMP,
|
||
FOREIGN KEY (user_id) REFERENCES amaksatov.users(id),
|
||
FOREIGN KEY (lesson_id) REFERENCES amaksatov.lessons(id)
|
||
);
|
||
|
||
|
||
INSERT
|
||
INTO
|
||
amaksatov.users (
|
||
name,
|
||
age,
|
||
email,
|
||
registration_date
|
||
)
|
||
VALUES (
|
||
'Alice',
|
||
25,
|
||
'alice@mail.com',
|
||
'2023-01-10'
|
||
),
|
||
(
|
||
'Bob',
|
||
30,
|
||
'bob@gmail.com',
|
||
'2023-02-05'
|
||
),
|
||
(
|
||
'Charlie',
|
||
22,
|
||
'charlie@mail.com',
|
||
'2023-02-20'
|
||
),
|
||
(
|
||
'Diana',
|
||
28,
|
||
'diana@mail.com',
|
||
'2023-03-01'
|
||
),
|
||
(
|
||
'Ethan',
|
||
35,
|
||
'ethan@gmail.com',
|
||
'2023-03-10'
|
||
);
|
||
|
||
SELECT * FROM amaksatov.users;
|
||
|
||
|
||
INSERT
|
||
INTO
|
||
amaksatov.courses (
|
||
title,
|
||
category,
|
||
created_at
|
||
)
|
||
VALUES
|
||
(
|
||
'SQL для начинающих',
|
||
'data',
|
||
'2023-01-01'
|
||
),
|
||
(
|
||
'Python для анализа данных',
|
||
'programming',
|
||
'2023-01-15'
|
||
),
|
||
(
|
||
'BI с нуля',
|
||
'business',
|
||
'2023-03-01'
|
||
);
|
||
|
||
INSERT
|
||
INTO
|
||
amaksatov.lessons (
|
||
course_id,
|
||
title,
|
||
duration_min
|
||
)
|
||
VALUES
|
||
(
|
||
1,
|
||
'SELECT и FROM',
|
||
10
|
||
),
|
||
(
|
||
1,
|
||
'JOIN',
|
||
15
|
||
),
|
||
(
|
||
2,
|
||
'Pandas',
|
||
20
|
||
),
|
||
(
|
||
3,
|
||
'Основы BI',
|
||
12
|
||
),
|
||
(
|
||
3,
|
||
'Метрики и дашборды',
|
||
18
|
||
);
|
||
|
||
INSERT
|
||
INTO
|
||
amaksatov.enrollments (
|
||
user_id,
|
||
course_id,
|
||
enrolled_at
|
||
)
|
||
VALUES
|
||
(
|
||
1,
|
||
1,
|
||
'2023-01-15'
|
||
),
|
||
(
|
||
1,
|
||
2,
|
||
'2023-02-01'
|
||
),
|
||
(
|
||
2,
|
||
1,
|
||
'2023-01-20'
|
||
),
|
||
(
|
||
3,
|
||
2,
|
||
'2023-03-05'
|
||
),
|
||
(
|
||
4,
|
||
3,
|
||
'2023-04-01'
|
||
);
|
||
|
||
INSERT
|
||
INTO
|
||
amaksatov.lesson_views (
|
||
user_id,
|
||
lesson_id,
|
||
viewed_at
|
||
)
|
||
VALUES
|
||
(
|
||
1,
|
||
1,
|
||
'2023-01-16 10:00:00'
|
||
),
|
||
(
|
||
1,
|
||
2,
|
||
'2023-01-16 10:15:00'
|
||
),
|
||
(
|
||
2,
|
||
1,
|
||
'2023-01-21 09:00:00'
|
||
),
|
||
(
|
||
3,
|
||
3,
|
||
'2023-03-06 12:00:00'
|
||
),
|
||
(
|
||
4,
|
||
4,
|
||
'2023-04-02 10:00:00'
|
||
),
|
||
(
|
||
4,
|
||
5,
|
||
'2023-04-03 10:00:00'
|
||
);
|
||
|