Last active
March 11, 2019 23:31
-
-
Save TatyCat/26ca389b59b12b8dd98aee8d7327f091 to your computer and use it in GitHub Desktop.
w7d1-sqlHW
This file contains 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
# Select all columns for all employees | |
company_database> SELECT * FROM employees | |
+------+--------------+----------+------------+-------------------+------------- | |
| id | full_name | salary | position | phone_extension | part_time | |
|------+--------------+----------+------------+-------------------+------------- | |
| 1 | Jamie Smalls | 38293 | <null> | x334 | False | |
| 2 | Jake William | 3992 | Driver | x429 | True | |
| 3 | Casem Casey | 49292 | Accountant | x596 | False | |
+------+--------------+----------+------------+-------------------+------------- | |
SELECT 3 | |
# Select only the Full Name and Phone Extension for only Full Time employees | |
company_database> SELECT full_name,phone_extension FROM employees | |
+--------------+-------------------+ | |
| full_name | phone_extension | | |
|--------------+-------------------| | |
| Jamie Smalls | x334 | | |
| Jake William | x429 | | |
| Casem Casey | x596 | | |
+--------------+-------------------+ | |
SELECT 3 | |
Time: 0.016s | |
# Insert a new part time employee, as a software developer, part time, with a salary of 450 | |
company_database> INSERT INTO employees VALUES(4, 'Cales Poor', 450, 'Software D | |
eveloper', 'x006', true) | |
INSERT 0 1 | |
Time: 0.008s | |
company_database> | |
company_database> SELECT * FROM employees | |
+------+--------------+----------+--------------------+-------------------+----- | |
| id | full_name | salary | position | phone_extension | part | |
|------+--------------+----------+--------------------+-------------------+----- | |
| 1 | Jamie Smalls | 38293 | <null> | x334 | Fals | |
| 2 | Jake William | 3992 | Driver | x429 | True | |
| 3 | Casem Casey | 49292 | Accountant | x596 | Fals | |
| 4 | Cales Poor | 450 | Software Developer | x006 | True | |
+------+--------------+----------+--------------------+-------------------+----- | |
SELECT 4 | |
# Update all employees that are the cooks to have a salary of 500 | |
company_database> UPDATE employees SET salary= 500 WHERE position = 'Cook' | |
SELECT * FROM employees WHERE position = 'Cook' | |
+------+--------------+----------+------------+-------------------+-------------+ | |
| id | full_name | salary | position | phone_extension | part_time | | |
|------+--------------+----------+------------+-------------------+-------------| | |
| 5 | Apple Turner | 500 | Cook | x046 | True | | |
+------+--------------+----------+------------+-------------------+-------------+ | |
SELECT 1 | |
# Delete all employees that have the full name of "Lazy Lynn" | |
company_database> DELETE FROM employees WHERE full_name = 'Lazy Lynn' | |
You're about to run a destructive command. | |
Do you want to proceed? (y/n): y | |
Your call! | |
DELETE 1 | |
Time: 0.007s | |
# Update all employees to be full time | |
company_database> UPDATE employees SET part_time = false WHERE part_time = true | |
UPDATE 3 | |
Time: 0.007s | |
# Add a column to the table | |
company_database> ALTER TABLE employees ADD COLUMN soon_to_fire BOOLEAN | |
You're about to run a destructive command. | |
Do you want to proceed? (y/n): y | |
Your call! | |
ALTER TABLE | |
Time: 0.007s | |
# Bonus, Added values to the new column: | |
company_database> UPDATE employees SET soon_to_fire = False WHERE soon_to_fire = NULL | |
UPDATE 0 | |
Time: 0.001s | |
company_database> UPDATE employees SET soon_to_fire = False | |
UPDATE 6 | |
Time: 0.007s | |
company_database> SELECT * FROM employees | |
+------+--------------+----------+--------------------+-------------------+-------------+----------------+ | |
| id | full_name | salary | position | phone_extension | part_time | soon_to_fire | | |
|------+--------------+----------+--------------------+-------------------+-------------+----------------| | |
| 1 | Jamie Smalls | 38293 | <null> | x334 | False | False | | |
| 3 | Casem Casey | 49292 | Accountant | x596 | False | False | | |
| 6 | Will Turner | 9322 | Marketer | x033 | False | False | | |
| 2 | Jake William | 3992 | Driver | x429 | False | False | | |
| 4 | Cales Poor | 450 | Software Developer | x006 | False | False | | |
| 5 | Apple Turner | 500 | Cook | x046 | False | False | | |
+------+--------------+----------+--------------------+-------------------+-------------+----------------+ | |
SELECT 6 | |
Bonus, Fixing the weird side effect mistake that changing soon_to_fire to be all false: | |
company_database> UPDATE employees SET part_time = True WHERE full_name = 'Jake William' | |
UPDATE 1 | |
Time: 0.007s | |
company_database> UPDATE employees SET part_time = True WHERE full_name = 'Cales Poor' | |
UPDATE 1 | |
Time: 0.007s | |
company_database> UPDATE employees SET part_time = True WHERE full_name = 'Apple Turner' | |
UPDATE 1 | |
Time: 0.007s | |
company_database> SELECT * FROM employees | |
+------+--------------+----------+--------------------+-------------------+-------------+----------------+ | |
| id | full_name | salary | position | phone_extension | part_time | soon_to_fire | | |
|------+--------------+----------+--------------------+-------------------+-------------+----------------| | |
| 1 | Jamie Smalls | 38293 | <null> | x334 | False | False | | |
| 3 | Casem Casey | 49292 | Accountant | x596 | False | False | | |
| 6 | Will Turner | 9322 | Marketer | x033 | False | False | | |
| 2 | Jake William | 3992 | Driver | x429 | True | False | | |
| 4 | Cales Poor | 450 | Software Developer | x006 | True | False | | |
| 5 | Apple Turner | 500 | Cook | x046 | True | False | | |
+------+--------------+----------+--------------------+-------------------+-------------+----------------+ | |
SELECT 6 | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment