Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- test_guru=# CREATE TABLE categories (
- test_guru(# id serial PRIMARY KEY,
- test_guru(# title varchar(25) NOT null
- test_guru(# );
- CREATE TABLE
- test_guru=# SELECT *
- test_guru-# FROM categories;
- id | title
- ----+-------
- (0 rows)
- test_guru=# CREATE TABLE tests (
- test_guru(# id serial PRIMARY KEY,
- test_guru(# title varchar(50) NOT null,
- test_guru(# level int NOT null,
- test_guru(# category_id int NOT null
- test_guru(# );
- CREATE TABLE
- test_guru=# SELECT *
- test_guru-# FROM tests
- test_guru-# ;
- id | title | level | category_id
- ----+-------+-------+-------------
- (0 rows)
- test_guru=# CREATE TABLE questions (
- test_guru(# body varchar(250) NOT null,
- test_guru(# test_id int NOT null
- test_guru(# );
- CREATE TABLE
- test_guru=# SELECT *
- test_guru-# FROM questions;
- body | test_id
- ------+---------
- (0 rows)
- test_guru=# INSERT INTO categories(title) VALUES
- test_guru-# ('Frontend'),
- test_guru-# ('Backend'),
- test_guru-# ('Machine Learning');
- INSERT 0 3
- test_guru=# SELECT *
- test_guru-# FROM categories;
- id | title
- ----+------------------
- 1 | Frontend
- 2 | Backend
- 3 | Machine Learning
- (3 rows)
- test_guru=# INSERT INTO tests(title, level, category_id) VALUES
- test_guru-# ('HTML', 1, 1),
- test_guru-# ('Python', 2, 2),
- test_guru-# ('R', 2, 3),
- test_guru-# ('Ruby', 2, 2),
- test_guru-# ('JavaScript', 3, 1);
- INSERT 0 5
- test_guru=# SELECT *
- test_guru-# FROM tests;
- id | title | level | category_id
- ----+------------+-------+-------------
- 1 | HTML | 1 | 1
- 2 | Python | 2 | 2
- 3 | R | 2 | 3
- 4 | Ruby | 2 | 2
- 5 | JavaScript | 3 | 1
- (5 rows)
- test_guru=# INSERT INTO questions(body, test_id) VALUES
- test_guru-# ('Which of these is not a Python datatype?', 2),
- test_guru-# ('Which of the following method make a vector of repeated values?', 3),
- test_guru-# ('What does HTML stand for?', 1),
- test_guru-# ('What is the name of the element that is used to define what an object looks like after it is created?', 4),
- test_guru-# ('Where is the correct place to insert a JavaScript?', 5);
- INSERT 0 5
- test_guru=# SELECT *
- test_guru-# FROM questions;
- body | test_id
- -------------------------------------------------------------------------------------------------------+---------
- Which of these is not a Python datatype? | 2
- Which of the following method make a vector of repeated values? | 3
- What does HTML stand for? | 1
- What is the name of the element that is used to define what an object looks like after it is created? | 4
- Where is the correct place to insert a JavaScript? | 5
- (5 rows)
- test_guru=# SELECT *
- test_guru-# FROM tests
- test_guru-# WHERE NOT level = 1;
- id | title | level | category_id
- ----+------------+-------+-------------
- 2 | Python | 2 | 2
- 3 | R | 2 | 3
- 4 | Ruby | 2 | 2
- 5 | JavaScript | 3 | 1
- (4 rows)
- test_guru=# SELECT body, test_id
- test_guru-# FROM questions
- test_guru-# WHERE test_id = 4;
- body | test_id
- -------------------------------------------------------------------------------------------------------+---------
- What is the name of the element that is used to define what an object looks like after it is created? | 4
- (1 row)
- test_guru=# UPDATE tests
- test_guru-# SET title = 'Ruby Language', level = 3
- test_guru-# WHERE title = 'Ruby';
- UPDATE 1
- test_guru=# SELECT *
- test_guru-# FROM tests;
- id | title | level | category_id
- ----+---------------+-------+-------------
- 1 | HTML | 1 | 1
- 2 | Python | 2 | 2
- 3 | R | 2 | 3
- 5 | JavaScript | 3 | 1
- 4 | Ruby Language | 3 | 2
- (5 rows)
- test_guru=# DELETE
- test_guru-# FROM questions
- test_guru-# WHERE test_id = 5;
- DELETE 1
- test_guru=# SELECT *
- test_guru-# FROM questions
- test_guru-# ;
- body | test_id
- -------------------------------------------------------------------------------------------------------+---------
- Which of these is not a Python datatype? | 2
- Which of the following method make a vector of repeated values? | 3
- What does HTML stand for? | 1
- What is the name of the element that is used to define what an object looks like after it is created? | 4
- (4 rows)
- test_guru=# SELECT *
- test_guru-# FROM tests
- test_guru-# JOIN categories
- test_guru-# ON tests.category_id = categories.id;
- id | title | level | category_id | id | title
- ----+---------------+-------+-------------+----+------------------
- 1 | HTML | 1 | 1 | 1 | Frontend
- 2 | Python | 2 | 2 | 2 | Backend
- 3 | R | 2 | 3 | 3 | Machine Learning
- 5 | JavaScript | 3 | 1 | 1 | Frontend
- 4 | Ruby Language | 3 | 2 | 2 | Backend
- (5 rows)
- test_guru=# SELECT title, body
- test_guru-# FROM tests
- test_guru-# JOIN questions
- test_guru-# ON tests.id = questions.test_id;
- title | body
- ---------------+-------------------------------------------------------------------------------------------------------
- HTML | What does HTML stand for?
- Python | Which of these is not a Python datatype?
- R | Which of the following method make a vector of repeated values?
- Ruby Language | What is the name of the element that is used to define what an object looks like after it is created?
- (4 rows)
- ______________REMARKS_______________
- ### FIX column name duplicate for title in categories and assignment request
- test_guru=# ALTER TABLE
- test_guru-# categories
- test_guru-# RENAME COLUMN title to category_title;
- ALTER TABLE
- test_guru=# SELECT *
- test_guru-# FROM categories;
- id | category_title
- ----+------------------
- 1 | Frontend
- 2 | Backend
- 3 | Machine Learning
- (3 rows)
- test_guru=# SELECT title, category_title
- test_guru-# FROM tests
- test_guru-# JOIN categories
- test_guru-# ON tests.category_id = categories.id;
- title | category_title
- ---------------+------------------
- HTML | Frontend
- Python | Backend
- R | Machine Learning
- JavaScript | Frontend
- Ruby Language | Backend
- (5 rows)
- ### FIX FK for category_id
- test_guru=# ALTER TABLE tests
- test_guru-# ADD FOREIGN KEY (category_id) REFERENCES categories(id);
- ALTER TABLE
- test_guru=# SELECT *
- test_guru-# FROM tests;
- id | title | level | category_id
- ----+---------------+-------+-------------
- 1 | HTML | 1 | 1
- 2 | Python | 2 | 2
- 3 | R | 2 | 3
- 5 | JavaScript | 3 | 1
- 4 | Ruby Language | 3 | 2
- (5 rows)
- test_guru=# ALTER TABLE questions
- test_guru-# ADD FOREIGN KEY (test_id) REFERENCES tests(id);
- ALTER TABLE
- test_guru=# SELECT *
- test_guru-# FROM questions;
- body | test_id
- -------------------------------------------------------------------------------------------------------+---------
- Which of these is not a Python datatype? | 2
- Which of the following method make a vector of repeated values? | 3
- What does HTML stand for? | 1
- What is the name of the element that is used to define what an object looks like after it is created? | 4
- (4 rows)
- ### FIX selection request
- test_guru=# SELECT *
- test_guru-# FROM tests
- test_guru-# WHERE level = 2 OR level = 3;
- id | title | level | category_id
- ----+---------------+-------+-------------
- 2 | Python | 2 | 2
- 3 | R | 2 | 3
- 5 | JavaScript | 3 | 1
- 4 | Ruby Language | 3 | 2
- (4 rows)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement