Inserting, updating, and deleting data Flashcards
In column attributes what is the difference between VARCHAR and CHAR?
VARCHAR - variable chars * saves space
CHAR - will explicitly allocate specific number of characters like char(50).
PK describes what in column attributes?
The primary key uniquely identification on the table.
NN represents what in column attributes?
Determines if a column accepts null values, NOT NULL
Based on the column attribute pic what two columns on this customers table is optional?
the birth_date and phone columns are not marked NOT NULL, so they are optional values.
True or False
The primary key column is usually set to AI which is what?
Auto_Increment
When you don’t specify a value, MYSQL will provide a Default/Expression value of?
NULL
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)
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’
)
Write a query to insert multiple shippers in the shippers table
shipper1, shipper2, shipper3
INSERT INTO shippers (name)
VALUES
(‘shipper1’),
(‘shipper2’),
(‘shipper3’)
Exercise:
– Insert three rows into the products table
INSERT INTO products (
name,
quantity_in_stock,
unit_price
)
VALUES
(‘astroglide’, 10, 9.99),
(‘eatible panties’, 3, 14.89),
(‘dildo’, 16, 24.95)
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)
Inserting hierarchical data on multiple tables (orders table has a parent child relationship with the order_items table)
How to copy one table data to another? (use the order table)
CREATE TABLE orders_archived AS
SELECT * FROM orders
When copying a table what two important attributes will NOT be copied?
The primary-key (pk) and auto increment (AI) status
What part of this query would be known as the subquery?
CREATE TABLE orders_archived AS
SELECT * FROM orders
SELECT * FROM orders
Truncating a table in mysql will do what exactly?
deletes all data in the table
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’
inserting filtered data via the WHERE statement into the orders_archived table.
What is the primary query of this query?
INSERT INTO orders_archived
SELECT *
FROM orders
WHERE order_date < ‘2019-01-01’
INSERT INTO orders_archived
What database is this query accessing?
USE sql_invoicing;
SELECT *
FROM invoices i
JOIN clients c
USING(client_id)
sql_invoicing
What column are these table being joined on?
USE sql_invoicing;
SELECT *
FROM invoices i
JOIN clients c
USING(client_id)
client_id on both tables
What two alias are being used in this query?
USE sql_invoicing;
SELECT *
FROM invoices i
JOIN clients c
USING(client_id)
- *i** on invoices
- *c** on clients
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)
The name column on the clients table is being changed to client
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)
which table the column resides on
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
return only records where there’s been a payment made
payment_date is NOT null
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
CREATE TABLE invoices_archived AS
True or False
In mysql you have to refresh the panel to view all changes made to table and databases
True
True or False
In mysql you can create as many tables of indentical names as you want?
False
tables and databases names must be unique
What table is being quired here?
SELECT * FROM sql_invoicing.invoices;
invoices
What database is being quired here?
SELECT * FROM sql_invoicing.invoices;
sql_invoicing
What table is being updated in this query?
UPDATE invoices
SET payment_total = 10, payment_date = ‘2019-03-01’
WHERE invoice_id = 1
the invoices table
What two columns are being updated/changed?
UPDATE invoices
SET payment_total = 10, payment_date = ‘2019-03-01’
WHERE invoice_id = 1
the payment_total, payment_date columns
What specific row is being affected by this query?
UPDATE invoices
SET payment_total = 10, payment_date = ‘2019-03-01’
WHERE invoice_id = 1
the invoice_id 1 on the invoices table
Is this a valid query?
UPDATE invoices
SET payment_total = DEFAULT, payment_date = NULL
WHERE invoice_id = 1
only if the table itself has a default value set for the payment column and the payment_date accepts NULL in the table attributes
In this query how much is being paid?
UPDATE invoices
SET
payment_total = invoice_total * 0.5,
payment_date = due_date
WHERE invoice_id = 3
50% of the invoice_total and the payment_date is set to the due_date
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?
True
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
True
What is this query performing?
UPDATE customers
SET points = points + 50
WHERE birth_date < ‘1990-01-01’
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
True or False
A SELECT statement within another sql statement is considered a subquery.
True
The bold portion of this query is showing what?
UPDATE invoices
SET
payment_total = invoice_total * 0.5,
payment_date = due_date
WHERE client_id =
(SELECT client_id
FROM clients
WHERE name = ‘Myworks’)
Updating multiple rows based on the name col ‘Myworks’
How many rows are being affected by this query?
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’))
all rows that have state of NY or CA
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!
True
this is a good way to verify the data (rows) being affected.
Cite two ways to set the active database for querying in mysql.
- Use the USE database; statement
- Double-click the database in the mysql workbench schemas left pane.
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?
True
UPDATE orders
SET comments = ‘Gold customer’
WHERE customer_id IN
(SELECT customer_id
FROM customers
WHERE points > 3000)
What is the bold part of this query?
UPDATE orders
SET comments = ‘Gold customer’
WHERE customer_id IN
(SELECT customer_id
FROM customers
WHERE points > 3000)
subquery
Why is the IN statement required in this query?
UPDATE orders
SET comments = ‘Gold customer’
WHERE customer_id IN
(SELECT customer_id
FROM customers
WHERE points > 3000)
Because it affects multiple rows
What two tables are involved in this query?
UPDATE orders
SET comments = ‘Gold customer’
WHERE customer_id IN
(SELECT customer_id
FROM customers
WHERE points > 3000)
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
What is the significance of using the FROM statement in this query?
DELETE FROM invoices
It will delete the invoices table entirely!!!
What is being deleted in this query?
DELETE FROM invoices
WHERE client_id = (
SELECT *
FROM clients
WHERE name = ‘Myworks’)
Myworks invoices data
