Karteikarten
dbs last test
Karten in diesem Stapel (43)
- 1tableBasic unit of storage; composed of rows
- 2viewLogically represents subsets of data from one or
more tables - 3sequenceGenerates numeric values
- 4sequenceImproves the performance of some queries
- 5table naming rulesBegin with a letter
• Be 1–30 characters long
• Contain only A–Z, a–z, 0–9, _, $, and #
• Not duplicate the name of another object owned by the
same user
• Not be an Oracle server–reserved word - 6create table syntaxCREATE TABLE hire_dates
(id NUMBER(8),
hire_date DATE DEFAULT SYSDATE); - 7timestamp interval year to month interval day to second-2019-10-30 13:57:25.0000
-interval year to month 14-5(banks)
-interval month to year 3 6:32:12 - 8constraintsprimary key, foreign key, unique, check, composite primary key, table constraint vs column constraint
- 9what ur not allowed to use in checkcurval,nextval,level,rownum,sysdate,uid,user,usernv,putting another constraint
- 10insert syntaxINSERT INTO departments(department_id,
department_name, manager_id, location_id)
VALUES (70, 'Public Relations', 100, 1700); - 11to_dateTO_DATE('FEB 3, 1999', 'MON DD, YYYY')
- 12update syntaxUPDATE employees
SET department_id = 50
WHERE employee_id = 113; - 13delete syntaxDELETE FROM departments
WHERE department_name = 'Finance'; - 14TRUNCATE TABLETRUNCATE TABLE copy_emp;
-removes all rows from table
-cannot be undone - 15COMMIT ROLLBACK TO SAVEPOINTknow
- 16dcl vs dmlgrant, revoke
vs
everything else - 17read only/ read write tableALTER TABLE employees READ ONLY;
ALTER TABLE employees READ WRITE; - 18drop table and purgebruh
- 19alter table thingsadd
modify
drop - 20alter table disable column drop disabledSET UNUSED
DROP UNUSED COLUMNS - 21adding constriant to tableALTER TABLE emp2
ADD CONSTRAINT emp_mgr_fk
FOREIGN KEY(manager_id)
REFERENCES emp2(employee_id); - 22retrieve table from recycle binflashback table tablename
- 23creating viewcreate view viewname as subquery
- 24modifying a viewcreate or replace view viewname (column aliases) as subquery
- 25cannot remove row if view contains the following-Group functions
– A GROUP BY clause
– The DISTINCT keyword
– The pseudocolumn ROWNUM keyword - 26adding with read onlydoes not allow adding data
- 27drop view viewnamehaha yes
- 28data dict for viewset long 200
select viewname, text
from user_views - 29looking in recycle binselect original_name, droptime
from recyclebin - 30creating a sequenceCREATE SEQUENCE sequence
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}]; - 31NEXTVAL vs CURRVALthe next sequence val
vs
returns current sequence val - 32how to get currvalselect currval from dual;
- 33when does chaching values create gaps-rollback
-system crash
-seq used in another table - 34modify sequenceALTER SEQUENCE seq
/..redo seq statement - 35why are sequences usedthey sort values so that they can be accessed faster in queries
- 36when are indexes automatically createdprimary key, unique
- 37low cardinality vs high cardinalitycolumn w few unique values
vs
very uncommon/unique val - 38dont index when table updated freqthey make things slower
cannot update index val only delete and insert - 39getting name of seq and next valSELECT sequence_name, last_number
FROM user_sequences ; - 40create indexCREATE [UNIQUE][BITMAP]INDEX indexname
ON tablename (column[, column]...) REVERSE; - 41why create synonyms-easier ref to table owned by another user
-shorten lengthy obj names - 42create insex for accessing more recent stuffuse REVERSE at the end
- 43creating a synonymcreate synonym name
for obj;
Informationen
- Beschreibung
- Karteikarten am dbs last test, erstellt von hasdfhfsd gsdhhgsdh am 30/11/2019.
- Erstellt von
- hasdfhfsd gsdhhgsdh
- Aufrufe
- 0
- Bewertung