Oracle Database 11g: SQL Fundamentals I 1Z0-051
Exam Topic
Retrieving Data Using the SQL SELECT Statement
- List the capabilities of SQL SELECT statements
A SELECT statement retrieves information from the database, you can use selection capability in SQL to choose the columns in a table that you want returned by your query. |
- Execute a basic SELECT statement
Restricting and Sorting Data
- Limit the rows that are retrieved by a query
1. Select top 10 * from table 2. Select * from table where rownum<=10 order by name 3. Need to clarify SELECT * FROM sometable ORDER BY name OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY; |
- Sort the rows that are retrieved by a query
- Use ampersand substitution to restrict and sort output at runtime
single-ampersand (&) and double-ampersand (&&) set define off; select * from customers where PHONE='&id'; set define on; -- single-ampersand is a variable select * from customers where FIRST_NAME='&fn' and LAST_NAME='&ln'; |
Using Single-Row Functions to Customize Output
- Describe various types of functions available in SQL
- Use character, number, and date functions in SELECT statements
http://dong-dong.iteye.com/blog/1459260
Using Conversion Functions and Conditional Expressions
- Describe various types of conversion functions that are available in SQL
- Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
- Apply conditional expressions in a SELECT statement
Reporting Aggregated Data Using the Group Functions
使用组函数报告汇总数据 |
- Identify the available group functions
- Describe the use of group functions
- Group data by using the GROUP BY clause
- Include or exclude grouped rows by using the HAVING clause
Displaying Data from Multiple Tables
- Write SELECT statements to access data from more than one table using equijoins and nonequijoins
- Join a table to itself by using a self-join
- View data that generally does not meet a join condition by using outer joins
- Generate a Cartesian product of all rows from two or more tables
Using Subqueries to Solve Queries
- Define subqueries
- Describe the types of problems that the subqueries can solve
- List the types of subqueries
1.single-row subquery
2.multiple-row subquery
exists,in
3.multiple-column
exists,in
4. correlate sub-query
5. sub-query's sub-query
- Write single-row and multiple-row subqueries
Using the Set Operators
- Describe set operators
SET OPERATORS(UNION, UNION ALL, MINUS, INTERSECT)
minus : return first_table records without second_talbe
intersect: 取交集
- Use a set operator to combine multiple queries into a single query
- Control the order of rows returned
Manipulating Data
- Describe each data manipulation language (DML) statement
- Insert rows into a table
- Update rows in a table
- Delete rows from a table
- Control transactions
Using DDL Statements to Create and Manage Tables
DDL(Data Definition Language) (create alter drop rename truncate comment)
DCL(Data Control Language) (grant revoke)
TCL(Transaction Control) (commit rollback savepoint)
- Categorize the main database objects
- Review the table structure
- List the data types that are available for columns
- Create a simple table
- Explain how constraints are created at the time of table creation
1.primary key
2. check
3. not null
4.reference - foreign key
create table order_status(
order_status_id INTEGER CONSTRAINT default_pk PRIMARY KEY,
status VARCHAR2(20) DEFAULT 'Order Placed' NOT NULL,
last_modified DATE DEFAULT SYSDATE,
last_upd_id INTEGER CONSTRAINT ck_upd_id CHECK (last_upd_id>10)
);
- Describe how schema objects work
Creating Other Schema Objects
- Create simple and complex views
- Retrieve data from views
- Create, maintain, and use sequences
drop sequence seq_depid; create sequence seq_depid increment by 1 start with 1 nomaxvalue -- not set max value nocycle -- nocycle or cycle - end at 10000 cache 10; -- cache or nocache, incase lose sequence, set it as nocache select seq_depid.currval,seq_depid.nextval from dual; alter sequence seq_depid increment by 1 -- start with 1 -- cannot alter starting sequence number, or need drop sequence and recreate sequence maxvalue 1000 cycle nocache; |
- Create and maintain indexes
Create table test_uid(name varchar2(10), constraint TEST_UID_PK PRIMARY KEY(NAME) USING INDEX (Create unique index uid_test_uid on test_uid(name) tablespace TABLESPACE2); ); or create table testone( name varchar2(10 char) ) TABLESPACE1; ALTER TABLE TESTONE ADD CONSTRAINT PK_TESTONE1 PRIMARY KEY(NAME) USING INDEX TABLESPACE TABLESPACE2; |
- Create private and public synonyms
create or replace public/private synonyms synonyms_name for schema.tablename |