Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
w7d1-sqlHW
# 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
You can’t perform that action at this time.