2. Inserting into a Table

The INSERT statement is used to insert or add a row of data into a table. To insert records into a table, use the following syntax:

INSERT INTO tablename (first_column,...last_column)
VALUES (first_value,...last_value);

The column names must match the values you provide in order. Strings should be enclosed in single quotes, while numbers should not.

Example

The following example shows how to insert a record into the employee table:

INSERT INTO employee (first, last, age, address, city, state)
VALUES ('Luke', 'Duke', 45, '2130 Boars Nest', 'Hazard Co', 'Georgia');

2.2. Insert Statement Exercises

Insert the following employees into your table:

INSERT INTO myemployees (firstname, lastname, title, age, salary)
VALUES ('Jonie', 'Weber', 'Secretary', 28, 19500.00);

INSERT INTO myemployees (firstname, lastname, title, age, salary)
VALUES ('Potsy', 'Weber', 'Programmer', 32, 45300.00);

INSERT INTO myemployees (firstname, lastname, title, age, salary)
VALUES ('Dirk', 'Smith', 'Programmer II', 45, 75020.00);

Add at least 5 more employees of your own choosing.

-- Example insert statements
INSERT INTO myemployees (firstname, lastname, title, age, salary)
VALUES ('Jenny', 'Smith', 'Programmer', 28, 45000.00),
       ('John', 'Doe', 'Manager', 35, 60000.00),
       ('Jane', 'Doe', 'Secretary', 25, 25000.00),
       ('Jack', 'Black', 'Programmer', 30, 50000.00),
       ('Jill', 'Hill', 'Programmer', 35, 55000.00);

2.3. Select Statements

After inserting data, use the following SELECT statements to retrieve specific information:

2.3.1. Select all columns for everyone in your employee table

SELECT * FROM myemployees;

2.3.2. Select all columns for everyone with a salary over 30000

SELECT * FROM myemployees
WHERE salary > 30000;

2.3.3. Select first and last names for everyone that's under 30 years old

SELECT firstname, lastname FROM myemployees
WHERE age < 30;

2.3.4. Select first name, last name, and salary for anyone with "Programmer" in their title

SELECT firstname, lastname, salary FROM myemployees
WHERE title LIKE '%Programmer%';

2.3.5. Select all columns for everyone whose last name contains "ebe"

SELECT * FROM myemployees
WHERE lastname LIKE '%ebe%';

2.3.6. Select the first name for everyone whose first name equals "Potsy"

SELECT firstname FROM myemployees
WHERE firstname = 'Potsy';

2.3.7. Select all columns for everyone over 80 years old

SELECT * FROM myemployees
WHERE age > 80;

2.3.8. Select all columns for everyone whose last name ends in "ith"

SELECT * FROM myemployees
WHERE lastname LIKE '%ith';

2.4. Custom Select Statements

Create at least 5 of your own SELECT statements to retrieve specific information.

-- Example custom select statements
-- Select all columns for everyone whose first name starts with 'J'
SELECT * FROM myemployees
WHERE firstname LIKE 'J%';

-- Select the first name and salary for everyone whose salary is less than 40000
SELECT firstname, salary FROM myemployees
WHERE salary < 40000;

-- Select all columns for everyone who has 'Secretary' in their title
SELECT * FROM myemployees
WHERE title = 'Secretary';

-- Select the last name and age for everyone older than 40
SELECT lastname, age FROM myemployees
WHERE age > 40;

-- Select all columns for everyone whose city is 'Hazard Co'
SELECT * FROM myemployees
WHERE city = 'Hazard Co';