Logo

Go on. Explore!

lvl 0 • 0 xp

lvl 1

HomeMarketDecksInterests
Creating/altering/updating objects

Creating/altering/updating objects

memorize.aimemorize.ai (lvl 286)
Star 0%
Star 0%
Star 0%
Star 0%
Star 0%
(0)
(0)
(0)
25 cards
()
Section 1

Preview this deck

how to view metadata for all tables

Front

1 / 25

SQL
Star 0%
Star 0%
Star 0%
Star 0%
Star 0%

0.0

0 reviews

5
0
4
0
3
0
2
0
1
0

Active users

0

All-time users

0

Favorites

0

Last updated

7 years ago

Date created

Mar 1, 2020

Cards (25)

Section 1

(25 cards)

how to view metadata for all tables

Front

select * from all_tables

Back

rename a specific column

Front

ALTER TABLE tablename RENAME COLUMN old_column TO new_name

Back

how to create an index

Front

CREATE [UNIQUE] INDEX index_name ON table_name (col1, col2...) [COMPUTE STATISTICS]

Back

how to create view

Front

CREATE VIEW managers_v AS SELECT * FROM employees WHERE job='MANAGER';

Back

how to edit a view

Front

create or replace view as

Back

how to use a view

Front

SELECT* FROM viewname

Back

what is a view

Front

named query

Back

how to assign a number to every row

Front

select * from table where rownum<10

Back

how to view metadata for all columns

Front

select * from all_tab_COLUMNS

Back

how to add column to existing table

Front

ALTER TABLE tablename ADD columname datatype constraint

Back

how to delete all duplicates

Front

delete stores where rowid not in ( select min(rowid) from stores group by store_id )

Back

How to create another table similar to existing one

Front

CREATE TABLE tablename AS SELECT oldcol1, oldcol2, oldcol3 FROM oldtable;

Back

why shouldn't you index every column of table?

Front

indices take up space in db

Back

How is an index stored?

Front

as an object

Back

can you add not null when adding a column if table already has rows?

Front

no. Table must be empty to add mandatory not null

Back

delete an index

Front

DROP INDEX index_name

Back

add COMPUTE STATISTICS to an already existing index

Front

ALTER INDEX index_name REBUILD COMPUTE STATISTICS

Back

what does an index do?

Front

groups commonly queried columns together for fast searching/access

Back

How to update row data

Front

UPDATE your_table SET column = value WHERE some criteria

Back

convention for naming indices

Front

postfix _idx

Back

what does a UNIQUE index do?

Front

can only be created if all vals in column are unique

Back

update constraint on existing column

Front

ALTER TABLE tablename MODIFY columname datatype constraint

Back

how to modify table

Front

ALTER TABLE table_name MODIFY (column_name1 datatype constraint, ...column_name_n datatype constraint );

Back

difference between truncate table and drop table

Front

truncate removes all data from table, but drop deletes the table altogether

Back

COMPUTE STATISTICS keyword

Front

collects data about combo of included columns and stores info in data that is read by Oracle optimizer. This creates smarter, faster queries

Back