Last active
April 1, 2020 23:10
-
-
Save IgorShayderov/efee7795ae069c6c4fe1611323558be4 to your computer and use it in GitHub Desktop.
Задание 3
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 1. | |
| CREATE DATABASE test_guru; | |
| CREATE TABLE categories( | |
| id serial PRIMARY_KEY | |
| title varchar(50) | |
| ); | |
| CREATE TABLE tests( | |
| id serial PRIMARY_KEY, | |
| title varchar(50), | |
| level int, | |
| category_id int REFERENCES categories(id) | |
| ); | |
| CREATE TABLE questions( | |
| id serial PRIMARY_KEY, | |
| body varchar(50), | |
| test_id int REFERENCES tests(id) | |
| ); | |
| 2. | |
| INSERT INTO categories(title) VALUES | |
| "Chemistry", | |
| "Mathematics", | |
| "Philosophy"; | |
| INSERT INTO tests(title, level, category_id) VALUES | |
| ("Addition", 1, 2), | |
| ("Substraction", 1, 2), | |
| ("Multiplication", 1, 2), | |
| ("Stoicism", 2, 3), | |
| ("Epistemology", 2, 3); | |
| INSERT INTO questions(body, test_id) VALUES | |
| ("How to add positive numbers?", 1), | |
| ("How to add negative numbers?", 1), | |
| ("What is the main concept of stoicism?", 4), | |
| ("What is epistemology?", 5), | |
| ("How to multiplicate float point numbers?", 3); | |
| SELECT * | |
| FROM tests | |
| WHERE level IN (2,3); | |
| SELECT * | |
| FROM questions | |
| WHERE test_id = 1; | |
| UPDATE tests | |
| SET title = "Logarithm", level = 3 | |
| WHERE id = 2; | |
| DELETE FROM questions | |
| WHERE test_id = 1; | |
| С помощью JOIN выберите названия всех тестов и названия их категорий | |
| SELECT tests.title AS TestTitle, categories.title AS CategoryTitle | |
| FROM tests | |
| JOIN categories tests.catogory_id ON categories.id; | |
| С помощью JOIN выберите содержание всех вопросов (атрибут body) и названия связанных с ними тестов | |
| SELECT questions.body, tests.title | |
| FROM questions | |
| JOIN tests questions.test_id ON tests.id; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
строки 50-53
в 51 строке
categories.body- в этой таблице нет такого поляесть поле
titleтогда будет вот так:
SELECT tests.title, categories.titleно этот запрос вернет две колонки
title, пользоваться результатом такого запроса может оказаться затруднительно (может возникнуть путаница)нужно как-то различать эти колонки, они же принадлежат к разным таблицам
открой для себя SQL алиасы