Go on. Explore!

lvl 0 • 0 xp

lvl 1

Home Market Decks Interests

MySQL: Action Queries

memorize.ai (lvl 286)

(0)

30 cards

()

Section 1

Preview this deck

Updates rows in a table using Case function

Front

1 / 30

SQL

0.0

0 reviews

5			0
4			0
3			0
2			0
1			0

Active users

All-time users

Favorites

Last updated

6 years ago

Date created

Mar 14, 2020

Cards (30)

Section 1

(30 cards)

Updates rows in a table using Case function

Front

update ac_emp set salary = salary * ( 1 + case when salary < 40000 then 0.25 when salary < 50000 then 0.10 else 0.05 end ) ;

Back

Deleting rows with subquery test example

Front

delete from ac_emp where d_id in ( select d_id from ac_dept where d_state = 'NV');

Back

Syntax for updating rows

Front

update table_name set col_name = val or expression where condition;

Back

Syntax to delete existing rows

Front

delete from table_name where condition;

Back

A commit occurs when one of the following occurs:

Front

2) The user issues a COMMIT command- this is an explicit commit 1) The user changes a schema level object- this does an implicit commit.

Back

The user can issue the ROLLBACK command to ___.

Front

The user can issue the ROLLBACK command to undo all changes in the buffer, all changes since the last commit.

Back

A commit occurs when one of the following occurs:

Front

Back

When using a VIEW to update data,use this option.

Front

"WITH CHECK OPTION;" example: Creating a view with a check constraint CREATE OR REPLACE VIEW emp_dept_80 AS select emp_id, name_last , dept_id from a_emp.employees where dept_id = 80

Back

Syntax to insert row from one table into another.

Front

insert into my_table (col1_name, col2_name,...) select val_1,val_2,...val_n from ...;

Back

What is an undo also called? What is the system variable that allows you to do this?

Front

Rollback @@autocommitt

Back

What does an UPDATE statement do?

Front

Replacing current version of the row with a new version

Back

Insert... on duplicate value syntax`

Front

insert into tblName (col_id,Col2,Col3,...) values(val_id,Val2,Val3,...) on duplicate key update set col_2=val2, col_3=val3,...

Back

Multi-row insert syntax

Front

insert into mytable(col_1,col_2,...col_n) values (val_1,val_2,...val_n) ,(val_1,val_2,...val_n) ,(val_1,val_2,...val_n) ... (val_1,val_2,...val_n);

Back

INSERT INT--SET syntax

Front

insert into my_table SET col_1= val_1 , col_2=val_2 , .... , col_n=val_n;

Back

What does DML stand for?

Front

Data Manipulation Language

Back

Example inserting on duplicate values

Front

insert into ac_projects values (20, 'Icing', 450000) on duplicate key update prj_name = 'Icing' , prj_budget = 450000; Query OK, 2 rows affected (0.05 sec)

Back

INSERT INTO INSERT INTO -- SELECT UPDATE DELETE SELECT INTO SELECT INTO REPLACE

Front

INSERT INTO- queries to append single rows to a table INSERT INTO -- SELECT queries to append multiple rows from one table to another UPDATE - queries to change the data in one or more rows DELETE - queries to remove rows from a table SELECT INTO- queries to create new tables with rows from an existing table SELECT INTO - queries to create new empty tables that have the same design as an existing table REPLACE - queries to do inserts and replace in a single statement

Back

Turning @@autocommit on/off

Front

0-Means off, do not haave auto committ of action statements 1-Do have an auto committ of action statements

Back

Statements that end a transaction

Front

Committ Rollback'Statements that change the schema

Back

Note about DEFAULT

Front

When the table has default values defined, you can use the word DEFAULT instead of a value, or use a column list that omits that column.

Back

How to make changes to table in memory permanent?

Front

Issuing a COMMITT statement or by working in autocommit mode.

Back

Statements that change data whiileatransaction is in effect

Front

insert update delete select-some versions

Back

Insert rows from table example

Front

insert into ac_emp (e_id, e_name, d_id, salary, hiredate, e_status) select e_id, e_name, d_id, salary, hiredate, e_status from ac_potentialhire where hiredate is not null;

Back

What does truncating a table do? How do you truncate a table?

Front

Removeds all rows, but not table structure. truncate table mytable;

Back

Example that uses a view to update table

Front

CREATE OR REPLACE VIEW vw_emp AS select emp_id, name_last, name_first from a_emp.employees; select * from vw_emp where emp_id = 1995; +--------+-----------+------------+ | emp_id | name_last | name_first | +--------+-----------+------------+ | 1995 | Zahn | Joe | +--------+-----------+------------+

Back

What do select into queries do?

Front

Create new table from an existing table

Back

Syntax to insert single row

Front

insert into my_table (col_1, col_2,...col_n) values (val1_exp, val2_exp,...valn_exp)l

Back

Select into syntax

Front

CREATE TABLE NewTableName AS SELECT ColExpressions FROM ExistingTableName WHERE Condition

Back

This is a view that shows summary data

Front

CREATE OR REPLACE VIEW vw_high_price_by_category AS select catg_id, MAX(prod_list_price) As HighPrice from a_prd.products group by catg_id; +---------+-----------+ | Catg_id | HighPrice | +---------+-----------+ | APL | 850.00 | | GFD | 12.50 | | HD | 45.00 | | HW | 149.99 | | MUS | 15.95 | | PET | 549.99 | | SPG | 349.95 | +---------+-----------+

Back

Statements that start a transaction

Front

Begin Start Transasction

Back