Skip to content

Instantly share code, notes, and snippets.

@wmurch

wmurch/sql_day1 Secret

Created June 10, 2019 21:39
Show Gist options
  • Save wmurch/ee96f81e3f69560f48a1096fb2a757de to your computer and use it in GitHub Desktop.
Save wmurch/ee96f81e3f69560f48a1096fb2a757de to your computer and use it in GitHub Desktop.
Download ZIP
Raw
sql_day1
Windows PowerShell
Copyright (C) Microsoft Corporation. All rights reserved.
PS C:\Users\wmurc> createdb -U postgres company_database
Password:
Password:
createdb: could not connect to database template1: FATAL: password authentication failed for user "postgres"
PS C:\Users\wmurc> createdb -U postgres company_database
Password:
PS C:\Users\wmurc> /d
/d : The term '/d' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the
spelling of the name, or if a path was included, verify that the path is correct and try again.
At line:1 char:1
+ /d
+ ~~
+ CategoryInfo : ObjectNotFound: (/d:String) [], CommandNotFoundException
+ FullyQualifiedErrorId : CommandNotFoundException
PS C:\Users\wmurc> pgcli -U postgres company_database
Password for postgres:
Server: PostgreSQL 11.3,
Version: 2.1.1
Chat: https://gitter.im/dbcli/pgcli
Mail: https://groups.google.com/forum/#!forum/pgcli
Home: http://pgcli.com
company_database>
company_database> CREATE TABLE employee(id int, full_name text, salary double, job_position text, phone_e
xtension text, part_time boolean)
type "double" does not exist
LINE 1: ...ATE TABLE employee(id int, full_name text, salary double, jo...
^
Time: 0.003s
company_database> SELECT * from employee
relation "employee" does not exist
LINE 1: SELECT * from employee
^
Time: 0.001s BETWEEN keyword
company_database>
CASE keyword
CHAR keyword
[F2] Smart Completion: ON [F3] Multiline: OFF [F4] Emacs-mode
company_database> SELECT * from employee
relation "employee" does not exist
LINE 1: SELECT * from employee
^
Time: 0.001s
company_database> INSERT INTO employee ('full_name', 'salary', 'job_position', 'phone_extension', 'part_time')
VALUES ('Peter Parker', 120000, 'Manager', '4005', no)
syntax error at or near "'full_name'"
LINE 1: INSERT INTO employee ('full_name', 'salary', 'job_position',...
^
Time: 0.001s
company_database> INSERT INTO employee (full_name, salary, job_position, phone_extension, part_time)
VALUES ('Bruce Wayne', 120000000, 'Partner', '4006', no);
INSERT INTO employee (full_name, salary, job_position, phone_extension, part_time)
VALUES ('Rob Stark', 1240000, 'Senior Manager', '4007', yes);
INSERT INTO employee (full_name, salary, job_position, phone_extension, part_time)
VALUES ('Tony stark', 40000, 'Software Developer', '4008', yes);
INSERT INTO employee (full_name, salary, job_position, phone_extension, part_time)
VALUES ('Jean Grey', 80000, 'Software Developer', '4009', no);
INSERT INTO employee (full_name, salary, job_position, phone_extension, part_time)
VALUES ('Charles Xavier', 90000, 'Manager', '4009', no);
INSERT INTO employee (full_name, salary, job_position, phone_extension, part_time)
VALUES ('Clark Kent', 100000, 'Cook', '4010', no);
INSERT INTO employee (full_name, salary, job_position, phone_extension, part_time)
VALUES ('Lazy Lynn', 100000, 'Cook', 4011, no);
relation "employee" does not exist
LINE 1: INSERT INTO employee (full_name, salary, job_position, phone...
^
Time: 0.005s
company_database> INSERT INTO employee (full_name, salary, job_position, phone_extension, part_time)
VALUES ('Bruce Wayne', 120000000, 'Partner', '4006', no);
INSERT INTO employee (full_name, salary, job_position, phone_extension, part_time)
VALUES ('Rob Stark', 1240000, 'Senior Manager', '4007', yes);
INSERT INTO employee (full_name, salary, job_position, phone_extension, part_time)
VALUES ('Tony stark', 40000, 'Software Developer', '4008', yes);
INSERT INTO employee (full_name, salary, job_position, phone_extension, part_time)
VALUES ('Jean Grey', 80000, 'Software Developer', '4009', no);
INSERT INTO employee (full_name, salary, job_position, phone_extension, part_time)
VALUES ('Charles Xavier', 90000, 'Manager', '4009', no);
INSERT INTO employee (full_name, salary, job_position, phone_extension, part_time)
VALUES ('Clark Kent', 100000, 'Cook', '4010', no);
INSERT INTO employee (full_name, salary, job_position, phone_extension, part_time)
VALUES ('Lazy Lynn', 100000, 'Cook', 4011, no);
relation "employee" does not exist
LINE 1: INSERT INTO employee (full_name, salary, job_position, phone...
^
Time: 0.005s
company_database> SELECT * from employee
relation "employee" does not exist
LINE 1: SELECT * from employee
^
Time: 0.001s
company_database> CREATE TABLE employee(id int, full_name text, salary double, job_position text, phone_extension text, part_time boolean)
type "double" does not exist
LINE 1: ...ATE TABLE employee(id int, full_name text, salary double, jo...
^
Time: 0.001s
company_database> CREATE TABLE employee(id int, full_name text, salary int, job_position text, phone_extension text, part_time boolean)
CREATE TABLE
Time: 0.075s
company_database> INSERT INTO employee ('full_name', 'salary', 'job_position', 'phone_extension', 'part_time')
VALUES ('Peter Parker', 120000, 'Manager', '4005', no)
syntax error at or near "'full_name'"
LINE 1: INSERT INTO employee ('full_name', 'salary', 'job_position',...
^
Time: 0.002s
company_database> select * from em
relation "em" does not exist
LINE 1: select * from em
^
Time: 0.001s
company_database> select * from employee
+------+-------------+----------+----------------+-------------------+-------------+
| id | full_name | salary | job_position | phone_extension | part_time |
|------+-------------+----------+----------------+-------------------+-------------|
+------+-------------+----------+----------------+-------------------+-------------+
SELECT 0
Time: 0.013s
company_database>
[F2] Smart Completion: ON [F3] Multiline: OFcompany_database> INSERT INTO employee (full_name, salary, job_position, phoncompany_database> INSERT INTO employee (full_name, salary, job_position, phone_extension, part_time)
VALUES ('Peter Parker', 120000, 'Manager', '4005', no)
column "no" does not exist
LINE 2: VALUES ('Peter Parker', 120000, 'Manager', '4005', no)
^
Time: 0.001s
company_database> dropdb company_database
syntax error at or near "dropdb"
LINE 1: dropdb company_database
^
Time: 0.001s
company_database> DROPDB company_database
syntax error at or near "DROPDB"
LINE 1: DROPDB company_database
^
Time: 0.001s
company_database> exit
Goodbye!
PS C:\Users\wmurc> dropdb -U postgres company_database
Password:
PS C:\Users\wmurc> createdb -U postgres company_database
Password:
PS C:\Users\wmurc> CREATE TABLE employee(id SERIAL PRIMARY KEY, full_name text, salary int, job_position text, phone_extension text, part_time boolean)
/usr/bin/id: extra operand 'PRIMARY'
Try '/usr/bin/id --help' for more information.
CREATE : The term 'CREATE' is not recognized as the name of a cmdlet, function, script file, or operable program. Check
the spelling of the name, or if a path was included, verify that the path is correct and try again.
At line:1 char:1
+ CREATE TABLE employee(id SERIAL PRIMARY KEY, full_name text, salary i ...
+ ~~~~~~
+ CategoryInfo : ObjectNotFound: (CREATE:String) [], CommandNotFoundException
+ FullyQualifiedErrorId : CommandNotFoundException
PS C:\Users\wmurc> pgcli -U postgres company_database
Password for postgres:
Server: PostgreSQL 11.3,
Version: 2.1.1
Chat: https://gitter.im/dbcli/pgcli
Mail: https://groups.google.com/forum/#!forum/pgcli
Home: http://pgcli.com
company_database> CREATE TABLE employee(id SERIAL PRIMARY KEY, full_name text, salary int, job_position text, phone_extens
ion text, part_time boolean)
CREATE TABLE
Time: 0.072s
company_database> INSERT INTO employee (full_name, salary, job_position, phone_extension, part_time)
VALUES ('Peter Parker', 120000, 'Manager', '4005', no)
column "no" does not exist
LINE 2: VALUES ('Peter Parker', 120000, 'Manager', '4005', no)
^
Time: 0.003s
company_database> INSERT INTO employee (full_name, salary, job_position, phone_extension, part_time)
VALUES ('Peter Parker', 120000, 'Manager', '4005', false)
INSERT 0 1
Time: 0.003s
company_database> INSERT INTO employee (full_name, salary, job_position, phone_extension, part_time)
VALUES ('Bruce Wayne', 120000000, 'Partner', '4006', false);
INSERT INTO employee (full_name, salary, job_position, phone_extension, part_time)
VALUES ('Rob Stark', 1240000, 'Senior Manager', '4007', true);
INSERT INTO employee (full_name, salary, job_position, phone_extension, part_time)
VALUES ('Tony stark', 40000, 'Software Developer', '4008', true);
INSERT INTO employee (full_name, salary, job_position, phone_extension, part_time)
VALUES ('Jean Grey', 80000, 'Software Developer', '4009', false);
INSERT INTO employee (full_name, salary, job_position, phone_extension, part_time)
VALUES ('Charles Xavier', 90000, 'Manager', '4009', false);
INSERT INTO employee (full_name, salary, job_position, phone_extension, part_time)
VALUES ('Clark Kent', 100000, 'Cook', '4010', false);
INSERT INTO employee (full_name, salary, job_position, phone_extension, part_time)
VALUES ('Lazy Lynn', 100000, 'Cook', 4011, false);
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
Time: 0.010s
company_database> select
SELECT 1
Time: 0.000s
company_database> INSERT INTO employee (full_name, salary, job_position, phone_extension, part_time)
VALUES ('Bruce Wayne', 120000000, 'Partner', '4006', false);
INSERT INTO employee (full_name, salary, job_position, phone_extension, part_time)
VALUES ('Rob Stark', 1240000, 'Senior Manager', '4007', true);
INSERT INTO employee (full_name, salary, job_position, phone_extension, part_time)
VALUES ('Tony stark', 40000, 'Software Developer', '4008', true);
INSERT INTO employee (full_name, salary, job_position, phone_extension, part_time)
VALUES ('Jean Grey', 80000, 'Software Developer', '4009', false);
INSERT INTO employee (full_name, salary, job_position, phone_extension, part_time)
VALUES ('Charles Xavier', 90000, 'Manager', '4009', false);
INSERT INTO employee (full_name, salary, job_position, phone_extension, part_time)
VALUES ('Clark Kent', 100000, 'Cook', '4010', false);
INSERT INTO employee (full_name, salary, job_position, phone_extension, part_time)
VALUES ('Lazy Lynn', 100000, 'Cook', 4011, false);
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
Time: 0.010s
company_database> select * from employee
+------+----------------+-----------+--------------------+-------------------+-------------+
| id | full_name | salary | job_position | phone_extension | part_time |
|------+----------------+-----------+--------------------+-------------------+-------------|
| 1 | Peter Parker | 120000 | Manager | 4005 | False |
| 2 | Bruce Wayne | 120000000 | Partner | 4006 | False |
| 3 | Rob Stark | 1240000 | Senior Manager | 4007 | True |
| 4 | Tony stark | 40000 | Software Developer | 4008 | True |
| 5 | Jean Grey | 80000 | Software Developer | 4009 | False |
| 6 | Charles Xavier | 90000 | Manager | 4009 | False |
| 7 | Clark Kent | 100000 | Cook | 4010 | False |
| 8 | Lazy Lynn | 100000 | Cook | 4011 | False |
| 9 | Bruce Wayne | 120000000 | Partner | 4006 | False |
| 10 | Rob Stark | 1240000 | Senior Manager | 4007 | True |
| 11 | Tony stark | 40000 | Software Developer | 4008 | True |
| 12 | Jean Grey | 80000 | Software Developer | 4009 | False |
| 13 | Charles Xavier | 90000 | Manager | 4009 | False |
| 14 | Clark Kent | 100000 | Cook | 4010 | False |
| 15 | Lazy Lynn | 100000 | Cook | 4011 | False |
+------+----------------+-----------+--------------------+-------------------+-------------+
SELECT 15
Time: 0.026s
company_database> SELECT full_name,phone_extension FROM employee where part_time = false
+----------------+-------------------+
| full_name | phone_extension |
|----------------+-------------------|
| Peter Parker | 4005 |
| Bruce Wayne | 4006 |
| Jean Grey | 4009 |
| Charles Xavier | 4009 |
| Clark Kent | 4010 |
| Lazy Lynn | 4011 |
| Bruce Wayne | 4006 |
| Jean Grey | 4009 |
| Charles Xavier | 4009 |
| Clark Kent | 4010 |
| Lazy Lynn | 4011 |
+----------------+-------------------+
SELECT 11
Time: 0.016s
company_database> INSERT INTO employee (full_name, salary, job_position, phone_extension, part_time)
VALUES ('Steve Rogers', 450, 'Software Developer', 4011, true);
INSERT 0 1
Time: 0.002s
company_database> select * from employee
+------+----------------+-----------+--------------------+-------------------+-------------+
| id | full_name | salary | job_position | phone_extension | part_time |
|------+----------------+-----------+--------------------+-------------------+-------------|
| 1 | Peter Parker | 120000 | Manager | 4005 | False |
| 2 | Bruce Wayne | 120000000 | Partner | 4006 | False |
| 3 | Rob Stark | 1240000 | Senior Manager | 4007 | True |
| 4 | Tony stark | 40000 | Software Developer | 4008 | True |
| 5 | Jean Grey | 80000 | Software Developer | 4009 | False |
| 6 | Charles Xavier | 90000 | Manager | 4009 | False |
| 7 | Clark Kent | 100000 | Cook | 4010 | False |
| 8 | Lazy Lynn | 100000 | Cook | 4011 | False |
| 9 | Bruce Wayne | 120000000 | Partner | 4006 | False |
| 10 | Rob Stark | 1240000 | Senior Manager | 4007 | True |
| 11 | Tony stark | 40000 | Software Developer | 4008 | True |
| 12 | Jean Grey | 80000 | Software Developer | 4009 | False |
| 13 | Charles Xavier | 90000 | Manager | 4009 | False |
| 14 | Clark Kent | 100000 | Cook | 4010 | False |
| 15 | Lazy Lynn | 100000 | Cook | 4011 | False |
| 16 | Steve Rogers | 450 | Software Developer | 4011 | True |
+------+----------------+-----------+--------------------+-------------------+-------------+
SELECT 16
Time: 0.017s
company_database> UPDATE books SET salary = 500 WHERE job_position = 'cook'
relation "books" does not exist
LINE 1: UPDATE books SET salary = 500 WHERE job_position = 'cook'
^
Time: 0.001s
company_database> select * from employee
+------+----------------+-----------+--------------------+-------------------+-------------+
| id | full_name | salary | job_position | phone_extension | part_time |
|------+----------------+-----------+--------------------+-------------------+-------------|
| 1 | Peter Parker | 120000 | Manager | 4005 | False |
| 2 | Bruce Wayne | 120000000 | Partner | 4006 | False |
| 3 | Rob Stark | 1240000 | Senior Manager | 4007 | True |
| 4 | Tony stark | 40000 | Software Developer | 4008 | True |
| 5 | Jean Grey | 80000 | Software Developer | 4009 | False |
| 6 | Charles Xavier | 90000 | Manager | 4009 | False |
| 7 | Clark Kent | 100000 | Cook | 4010 | False |
| 8 | Lazy Lynn | 100000 | Cook | 4011 | False |
| 9 | Bruce Wayne | 120000000 | Partner | 4006 | False |
| 10 | Rob Stark | 1240000 | Senior Manager | 4007 | True |
| 11 | Tony stark | 40000 | Software Developer | 4008 | True |
| 12 | Jean Grey | 80000 | Software Developer | 4009 | False |
| 13 | Charles Xavier | 90000 | Manager | 4009 | False |
| 14 | Clark Kent | 100000 | Cook | 4010 | False |
| 15 | Lazy Lynn | 100000 | Cook | 4011 | False |
| 16 | Steve Rogers | 450 | Software Developer | 4011 | True |
+------+----------------+-----------+--------------------+-------------------+-------------+
SELECT 16
Time: 0.018s
company_database> UPDATE employee SET salary = 500 WHERE job_position = 'cook'
UPDATE 0
Time: 0.001s
company_database> select * from employee
+------+----------------+-----------+--------------------+-------------------+-------------+
| id | full_name | salary | job_position | phone_extension | part_time |
|------+----------------+-----------+--------------------+-------------------+-------------|
| 1 | Peter Parker | 120000 | Manager | 4005 | False |
| 2 | Bruce Wayne | 120000000 | Partner | 4006 | False |
| 3 | Rob Stark | 1240000 | Senior Manager | 4007 | True |
| 4 | Tony stark | 40000 | Software Developer | 4008 | True |
| 5 | Jean Grey | 80000 | Software Developer | 4009 | False |
| 6 | Charles Xavier | 90000 | Manager | 4009 | False |
| 7 | Clark Kent | 100000 | Cook | 4010 | False |
| 8 | Lazy Lynn | 100000 | Cook | 4011 | False |
| 9 | Bruce Wayne | 120000000 | Partner | 4006 | False |
| 10 | Rob Stark | 1240000 | Senior Manager | 4007 | True |
| 11 | Tony stark | 40000 | Software Developer | 4008 | True |
| 12 | Jean Grey | 80000 | Software Developer | 4009 | False |
| 13 | Charles Xavier | 90000 | Manager | 4009 | False |
| 14 | Clark Kent | 100000 | Cook | 4010 | False |
| 15 | Lazy Lynn | 100000 | Cook | 4011 | False |
| 16 | Steve Rogers | 450 | Software Developer | 4011 | True |
+------+----------------+-----------+--------------------+-------------------+-------------+
SELECT 16
Time: 0.014s
company_database> UPDATE employee SET salary = 500 WHERE job_position = 'Cook'
UPDATE 4
Time: 0.002s
company_database> select * from employee
+------+----------------+-----------+--------------------+-------------------+-------------+
| id | full_name | salary | job_position | phone_extension | part_time |
|------+----------------+-----------+--------------------+-------------------+-------------|
| 1 | Peter Parker | 120000 | Manager | 4005 | False |
| 2 | Bruce Wayne | 120000000 | Partner | 4006 | False |
| 3 | Rob Stark | 1240000 | Senior Manager | 4007 | True |
| 4 | Tony stark | 40000 | Software Developer | 4008 | True |
| 5 | Jean Grey | 80000 | Software Developer | 4009 | False |
| 6 | Charles Xavier | 90000 | Manager | 4009 | False |
| 9 | Bruce Wayne | 120000000 | Partner | 4006 | False |
| 10 | Rob Stark | 1240000 | Senior Manager | 4007 | True |
| 11 | Tony stark | 40000 | Software Developer | 4008 | True |
| 12 | Jean Grey | 80000 | Software Developer | 4009 | False |
| 13 | Charles Xavier | 90000 | Manager | 4009 | False |
| 16 | Steve Rogers | 450 | Software Developer | 4011 | True |
| 7 | Clark Kent | 500 | Cook | 4010 | False |
| 8 | Lazy Lynn | 500 | Cook | 4011 | False |
| 14 | Clark Kent | 500 | Cook | 4010 | False |
| 15 | Lazy Lynn | 500 | Cook | 4011 | False |
+------+----------------+-----------+--------------------+-------------------+-------------+
SELECT 16
Time: 0.015s
company_database> DELETE FROM employee WHERE full_name = 'Lazy Lynn'
You're about to run a destructive command.
Do you want to proceed? (y/n): y
Your call!
DELETE 2
Time: 0.001s
company_database> UPDATE employee SET part_time = false WHERE part_time = true
UPDATE 5
Time: 0.001s
company_database> select * from employee
+------+----------------+-----------+--------------------+-------------------+-------------+
| id | full_name | salary | job_position | phone_extension | part_time |
|------+----------------+-----------+--------------------+-------------------+-------------|
| 1 | Peter Parker | 120000 | Manager | 4005 | False |
| 2 | Bruce Wayne | 120000000 | Partner | 4006 | False |
| 5 | Jean Grey | 80000 | Software Developer | 4009 | False |
| 6 | Charles Xavier | 90000 | Manager | 4009 | False |
| 9 | Bruce Wayne | 120000000 | Partner | 4006 | False |
| 12 | Jean Grey | 80000 | Software Developer | 4009 | False |
| 13 | Charles Xavier | 90000 | Manager | 4009 | False |
| 7 | Clark Kent | 500 | Cook | 4010 | False |
| 14 | Clark Kent | 500 | Cook | 4010 | False |
| 3 | Rob Stark | 1240000 | Senior Manager | 4007 | False |
| 4 | Tony stark | 40000 | Software Developer | 4008 | False |
| 10 | Rob Stark | 1240000 | Senior Manager | 4007 | False |
| 11 | Tony stark | 40000 | Software Developer | 4008 | False |
| 16 | Steve Rogers | 450 | Software Developer | 4011 | False |
+------+----------------+-----------+--------------------+-------------------+-------------+
SELECT 14
Time: 0.015s
company_database> ALTER TABLE employee ADD COLUMN parking_spot_number int
You're about to run a destructive command.
Do you want to proceed? (y/n): y
Your call!
ALTER TABLE
Time: 0.001s
company_database> select * from employee
+------+----------------+-----------+--------------------+-------------------+-------------+-----------------------+
| id | full_name | salary | job_position | phone_extension | part_time | parking_spot_number |
|------+----------------+-----------+--------------------+-------------------+-------------+-----------------------|
| 1 | Peter Parker | 120000 | Manager | 4005 | False | <null> |
| 2 | Bruce Wayne | 120000000 | Partner | 4006 | False | <null> |
| 5 | Jean Grey | 80000 | Software Developer | 4009 | False | <null> |
| 6 | Charles Xavier | 90000 | Manager | 4009 | False | <null> |
| 9 | Bruce Wayne | 120000000 | Partner | 4006 | False | <null> |
| 12 | Jean Grey | 80000 | Software Developer | 4009 | False | <null> |
| 13 | Charles Xavier | 90000 | Manager | 4009 | False | <null> |
| 7 | Clark Kent | 500 | Cook | 4010 | False | <null> |
| 14 | Clark Kent | 500 | Cook | 4010 | False | <null> |
| 3 | Rob Stark | 1240000 | Senior Manager | 4007 | False | <null> |
| 4 | Tony stark | 40000 | Software Developer | 4008 | False | <null> |
| 10 | Rob Stark | 1240000 | Senior Manager | 4007 | False | <null> |
| 11 | Tony stark | 40000 | Software Developer | 4008 | False | <null> |
| 16 | Steve Rogers | 450 | Software Developer | 4011 | False | <null> |
+------+----------------+-----------+--------------------+-------------------+-------------+-----------------------+
SELECT 14
Time: 0.019s
company_database>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment