Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

MYSQL > Inserting, updating, and deleting data > Flashcards

Inserting, updating, and deleting data Flashcards

1
Q

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

A

VARCHAR - variable chars * saves space

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

PK describes what in column attributes?

A

The primary key uniquely identification on the table.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

NN represents what in column attributes?

A

Determines if a column accepts null values, NOT NULL

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

True or False

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

A

Auto_Increment

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

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

A

NULL

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

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)

A

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’
)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

Write a query to insert multiple shippers in the shippers table

shipper1, shipper2, shipper3

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

Exercise:

– Insert three rows into the products table

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

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)

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

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

A

CREATE TABLE orders_archived AS
SELECT * FROM orders

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

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

CREATE TABLE orders_archived AS
SELECT * FROM orders

A

SELECT * FROM orders

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

Truncating a table in mysql will do what exactly?

A

deletes all data in the table

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

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’

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

What is the primary query of this query?

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

A

INSERT INTO orders_archived

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

What database is this query accessing?

USE sql_invoicing;

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

A

sql_invoicing

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

What column are these table being joined on?

USE sql_invoicing;

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

A

client_id on both tables

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

What two alias are being used in this query?

USE sql_invoicing;

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

A
  • *i** on invoices
  • *c** on clients
20
Q

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)

A

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

21
Q

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)

A

which table the column resides on

22
Q

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

A

return only records where there’s been a payment made

payment_date is NOT null

23
Q

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

A

CREATE TABLE invoices_archived AS

24
Q

True or False

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

A

True

25
Q

True or False

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

A

False

tables and databases names must be unique

26
Q

What table is being quired here?

SELECT * FROM sql_invoicing.invoices;

A

invoices

27
Q

What database is being quired here?

SELECT * FROM sql_invoicing.invoices;

A

sql_invoicing

28
Q

What table is being updated in this query?

UPDATE invoices
SET payment_total = 10, payment_date = ‘2019-03-01’
WHERE invoice_id = 1

A

the invoices table

29
Q

What two columns are being updated/changed?

UPDATE invoices
SET payment_total = 10, payment_date = ‘2019-03-01’
WHERE invoice_id = 1

A

the payment_total, payment_date columns

30
Q

What specific row is being affected by this query?

UPDATE invoices
SET payment_total = 10, payment_date = ‘2019-03-01’
WHERE invoice_id = 1

A

the invoice_id 1 on the invoices table

31
Q

Is this a valid query?

UPDATE invoices
SET payment_total = DEFAULT, payment_date = NULL
WHERE invoice_id = 1

A

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

32
Q

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

A

50% of the invoice_total and the payment_date is set to the due_date

33
Q

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?

A

True

34
Q

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

A

True

35
Q

What is this query performing?

UPDATE customers
SET points = points + 50
WHERE birth_date < ‘1990-01-01’

A

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

36
Q

True or False

A SELECT statement within another sql statement is considered a subquery.

A

True

37
Q

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’)

A

Updating multiple rows based on the name col ‘Myworks’

38
Q

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’))

A

all rows that have state of NY or CA

39
Q

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!

A

True

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

40
Q

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

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

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?

A

True

UPDATE orders
SET comments = ‘Gold customer’
WHERE customer_id IN
(SELECT customer_id
FROM customers
WHERE points > 3000)

42
Q

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)

A

subquery

43
Q

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)

A

Because it affects multiple rows

44
Q

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)

A

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

45
Q

What is the significance of using the FROM statement in this query?

DELETE FROM invoices

A

It will delete the invoices table entirely!!!

46
Q

What is being deleted in this query?

DELETE FROM invoices
WHERE client_id = (
SELECT *
FROM clients
WHERE name = ‘Myworks’)

A

Myworks invoices data

MYSQL (19 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions