11g: SQL Fundamentals I 1Z0-051

Oracle Database 11g: SQL Fundamentals I 1Z0-051

https://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=5001&get_params=p_exam_id: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