Inserting, updating, and deleting data

Question 1

In column attributes what is the difference between VARCHAR and CHAR?

Answer 1

VARCHAR - variable chars * saves space

CHAR - will explicitly allocate specific number of characters like char(50).

Question 2

PK describes what in column attributes?

Answer 2

The primary key uniquely identification on the table.

Question 3

NN represents what in column attributes?

Answer 3

Determines if a column accepts null values, NOT NULL

Question 4

Based on the column attribute pic what two columns on this customers table is optional?

Answer 4

the birth_date and phone columns are not marked NOT NULL, so they are optional values.

Question 5

True or False

The primary key column is usually set to AI which is what?

Answer 5

Auto_Increment

Question 6

When you don’t specify a value, MYSQL will provide a Default/Expression value of?

Answer 6

NULL

Question 7

Write a query to insert the following information into the customers table.

Kelvin Waters

DOB: 08/02/1967

3401 Sunrise Villas Ct S, Tampa FL

Points 0 (default)

Answer 7

INSERT INTO customers (
first_name,
last_name,
birth_date,
address,
city,
state
)
VALUES (
‘Kelvin’,
‘Waters’,
‘1967-08-02’,
‘3401 Sunrise Villas CT S’,
‘Tampa’,
‘FL’
)

Question 8

Write a query to insert multiple shippers in the shippers table

shipper1, shipper2, shipper3

Answer 8

INSERT INTO shippers (name)
VALUES
(‘shipper1’),
(‘shipper2’),
(‘shipper3’)

Question 9

Exercise:

– Insert three rows into the products table

Answer 9

INSERT INTO products (
name,
quantity_in_stock,
unit_price
)
VALUES
(‘astroglide’, 10, 9.99),
(‘eatible panties’, 3, 14.89),
(‘dildo’, 16, 24.95)

Question 10

What is this an example of?

INSERT INTO orders (customer_id, order_date, status)
VALUES (1, ‘2019-01-02’, 1);

INSERT INTO order_items
VALUES
(LAST_INSERT_ID(), 1, 1, 2.95),
(LAST_INSERT_ID(), 2, 1, 3.95)

Answer 10

Inserting hierarchical data on multiple tables (orders table has a parent child relationship with the order_items table)

Question 11

How to copy one table data to another? (use the order table)

Answer 11

CREATE TABLE orders_archived AS
SELECT * FROM orders

Question 12

When copying a table what two important attributes will NOT be copied?

Answer 12

The primary-key (pk) and auto increment (AI) status

Question 13

What part of this query would be known as the subquery?

CREATE TABLE orders_archived AS
SELECT * FROM orders

Answer 13

SELECT * FROM orders

Question 14

Truncating a table in mysql will do what exactly?

Answer 14

deletes all data in the table

Question 15

This code is performing what in the orders_archived table that was previously truncated?

INSERT INTO orders_archived
SELECT *
FROM orders
WHERE order_date < ‘2019-01-01’

Answer 15

inserting filtered data via the WHERE statement into the orders_archived table.

Question 16

What is the primary query of this query?

INSERT INTO orders_archived
SELECT *
FROM orders
WHERE order_date < ‘2019-01-01’

Answer 16

INSERT INTO orders_archived

Question 17

What database is this query accessing?

USE sql_invoicing;

SELECT *
FROM invoices i
JOIN clients c
USING(client_id)

Answer 17

sql_invoicing

Question 18

What column are these table being joined on?

USE sql_invoicing;

SELECT *
FROM invoices i
JOIN clients c
USING(client_id)

Answer 18

client_id on both tables

Question 19

What two alias are being used in this query?

USE sql_invoicing;

SELECT *
FROM invoices i
JOIN clients c
USING(client_id)

Answer 19

• *i** on invoices
• *c** on clients

Question 20

Describe whats being performed in the bold of this query?

USE sql_invoicing;

SELECT
i.invoice_id,
i.number,
c.name AS client,
i.invoice_total,
i.payment_date,
i.due_date
FROM invoices i
JOIN clients c
USING(client_id)

Answer 20

The name column on the clients table is being changed to client

Question 21

The preceding i and c’s on this query indicates what?

USE sql_invoicing;

SELECT
i.invoice_id,
i.number,
c.name AS client,
i.invoice_total,
i.payment_date,
i.due_date
FROM invoices i
JOIN clients c
USING(client_id)

Answer 21

which table the column resides on

Question 22

What’s being filtered in this query?

USE sql_invoicing;

SELECT
i.invoice_id,
i.number,
c.name AS client,
i.invoice_total,
i.payment_date,
i.due_date
FROM invoices i
JOIN clients c
USING(client_id)
WHERE payment_date IS NOT NULL

Answer 22

return only records where there’s been a payment made

payment_date is NOT null

Question 23

After executing all this code what creates another table of this quiry?

USE sql_invoicing;

CREATE TABLE invoices_archived AS
SELECT
i.invoice_id,
i.number,
c.name AS client,
i.invoice_total,
i.payment_date,
i.due_date
FROM invoices i
JOIN clients c
USING(client_id)
WHERE payment_date IS NOT NULL

Answer 23

CREATE TABLE invoices_archived AS

Question 24

True or False

In mysql you have to refresh the panel to view all changes made to table and databases

Answer 24

True

Question 25

True or False In mysql you can create as many tables of indentical names as you want?

Answer 25

False tables and databases names must be unique

Question 26

What table is being quired here? ## Footnote SELECT \* FROM sql\_invoicing.invoices;

Answer 26

invoices

Question 27

What database is being quired here? ## Footnote SELECT \* FROM sql\_invoicing.invoices;

Answer 27

sql\_invoicing

Question 28

What table is being updated in this query? ## Footnote UPDATE invoices SET payment\_total = 10, payment\_date = '2019-03-01' WHERE invoice\_id = 1

Answer 28

the invoices table

Question 29

What two columns are being updated/changed? ## Footnote UPDATE invoices SET payment\_total = 10, payment\_date = '2019-03-01' WHERE invoice\_id = 1

Answer 29

the payment\_total, payment\_date columns

Question 30

What specific row is being affected by this query? ## Footnote UPDATE invoices SET payment\_total = 10, payment\_date = '2019-03-01' WHERE invoice\_id = 1

Answer 30

the invoice\_id 1 on the invoices table

Question 31

Is this a valid query? ## Footnote UPDATE invoices SET payment\_total = **DEFAULT**, payment\_date = **NULL** WHERE invoice\_id = 1

Answer 31

only if the table itself has a default value set for the payment column and the payment\_date accepts NULL in the table attributes

Question 32

In this query how much is being paid? ## Footnote UPDATE invoices SET payment\_total = invoice\_total \* 0.5, payment\_date = due\_date WHERE invoice\_id = 3

Answer 32

50% of the invoice\_total and the payment\_date is set to the due\_date

Question 33

True or False In mysql one must disable Safe Updates(rejects UPDATEs and DELETESs with no restrictions) before executing a query which updates multiple rows?

Answer 33

True

Question 34

True or False This query is updating multiple row via the WHERE statement USE sql\_invoicing; UPDATE invoices SET payment\_total = invoice\_total \* 0.5, payment\_date = due\_date **WHERE** invoice\_id = 3

Answer 34

True

Question 35

What is this query performing? ## Footnote UPDATE customers SET points = points + 50 WHERE birth\_date \< '1990-01-01'

Answer 35

making changes on the customers table, adding 50 to the points value in the points column on all rows where the birth\_date is earlier than 01-01-1990

Question 36

True or False A SELECT statement within another sql statement is considered a subquery.

Answer 36

True

Question 37

The bold portion of this query is showing what? ## Footnote UPDATE invoices SET payment\_total = invoice\_total \* 0.5, payment\_date = due\_date WHERE client\_id = **(SELECT client\_id FROM clients WHERE name = 'Myworks')**

Answer 37

Updating multiple rows based on the name col 'Myworks'

Question 38

How many rows are being affected by this query? ## Footnote UPDATE invoices SET payment\_total = invoice\_total \* 0.5, payment\_date = due\_date WHERE client\_id **IN** (SELECT client\_id FROM clients WHERE state **IN** ('NY', 'CA'))

Answer 38

all rows that have state of NY or CA

Question 39

True or False In mysql is always a good idea to perform a micro query (shift+ctrl+enter) using a SELECT statement before updating multiple rows!

Answer 39

True this is a good way to verify the data (rows) being affected.

Question 40

Cite two ways to set the active database for querying in mysql.

Answer 40

1. Use the *USE database*; statement 2. Double-click the database in the mysql workbench schemas left pane.

Question 41

True or False It's totally possible to update a table in mysql based on data from a different table withouy an explict JOIN statement?

Answer 41

True ## Footnote UPDATE orders SET comments = 'Gold customer' WHERE customer\_id IN (SELECT customer\_id FROM customers WHERE points \> 3000)

Question 42

What is the bold part of this query? ## Footnote UPDATE orders SET comments = 'Gold customer' WHERE customer\_id IN **(SELECT customer\_id FROM customers WHERE points \> 3000)**

Answer 42

subquery

Question 43

Why is the IN statement required in this query? ## Footnote UPDATE orders SET comments = 'Gold customer' WHERE customer\_id **IN** (SELECT customer\_id FROM customers WHERE points \> 3000)

Answer 43

Because it affects multiple rows

Question 44

What two tables are involved in this query? ## Footnote UPDATE orders SET comments = 'Gold customer' WHERE customer\_id IN (SELECT customer\_id FROM customers WHERE points \> 3000)

Answer 44

the orders table is being updated with based on data from the customers table points greater than 3000 on the customer table will dictate who gets commented as a Gold customer on the orders table

Question 45

What is the significance of using the FROM statement in this query? ## Footnote DELETE FROM invoices

Answer 45

It will delete the invoices table entirely!!!

Question 46

What is being deleted in this query? ## Footnote DELETE FROM invoices WHERE client\_id = ( SELECT \* FROM clients WHERE name = 'Myworks')

Answer 46

Myworks invoices data