Create and manage a table "Weber's products must be a boutique"

1. Create a table

   Must have:

   1. Permissions for CREATE TABLE

   Sql> Conn/ asSYSDBA is connected. SQL> Create User Testdefaulttablespace users identified by a; user created. SQL> Conn test/Aerror:ora-01045: User TEST lacks CREATE SESSION privilege; logon denied warning: You are no longer connected to ORACLE. SQL> Conn/ asSYSDBA is connected. SQL>Grant Create session to test, authorization succeeded. SQL> Conn test/A is connected. SQL> CREATE table t (ID number,name VARCHAR2 (Ten) ); CREATE table T (ID number,name varchar2 (Ten))*Section1line error: ORA-01031: Insufficient permissions for SQL> Conn/ asSYSDBA is connected. SQL>grant CREATE table to test, authorization succeeded. SQL> Conn test/A is connected. SQL> CREATE table t (ID number,name VARCHAR2 (Ten) ); CREATE table T (ID number,name varchar2 (Ten))*Section1line error: ORA-01950: To Table Space'USERS'No permissions

2. Have storage space

   Sql> Conn/ asSYSDBA is connected. SQL>ALTER user test quota 10M on users, user has changed. SQL> Conn test/A is connected. SQL> CREATE table t (ID number,name VARCHAR2 (Ten) ; The table is created. Create a Table dcreate table D (deptno number (2), Dname varchar2 (Ten), loc varchar2 (Ten) ; The table is created. SQL>describe D; name is empty?type----------------------------------------- -------- ----------------------------DEPTNO Number (2) dname VARCHAR2 (Ten) LOC VARCHAR2 (Ten) SQL>desc D; Name is empty?type----------------------------------------- -------- ----------------------------DEPTNO Number (2) dname VARCHAR2 (Ten) LOC VARCHAR2 (Ten)

3. Tables that reference other users
   

   Schema: is a collection of users ' next set of objects, and the schema name is consistent with the user name

   Sql> Conn hr/Hrsql> GrantSelectOn employees to scott;conn Scott/Tigersql>SelectCOUNT (*) fromhr.employees; COUNT (*)----------107SQL>Show Useruser for"SCOTT"SQL> Alter SESSIONSetCurrent_schema=HR; The session has changed. SQL>SelectCOUNT (*) fromemployees; COUNT (*)----------107SQL>Show Useruser for"SCOTT"

4. Default option
   1. You can specify a default value during insert

   Sql> CREATE TABLE E (empno number (4), ename varchar2 (Ten), HireDate Datedefaultsysdate); The table was created. SQL>whether the desc e name is empty?type----------------------------------------- -------- ----------------------------EMPNO Number (4) ename VARCHAR2 (Ten) HireDate Datesql> INSERT into E values (7788,'SCOTT',default); created1line. SQL>Commit , Commit, complete. SQL>Select* frome; EMPNO ename hiredate----- ------ --------------7788SCOTT --August- -

6. Literal value, expression, or SQL function is a valid value
8. The names or pseudo-columns of other columns are illegal
9. Pseudo-column: Not a real column, such as: The row ID of an Excel table.

   Sql>SelectRownum,ename,sal fromEMP; ROWNUM ename SAL---------- ------ -----1SMITH -         2ALLEN the         3WARD1250         4JONES2975         5MARTIN1250         6BLAKE2850         7CLARK2450         8SCOTT4000         9KING the        TenTURNER the         OneAdams1100         AJAMES950         -FORD the         -MILLER1300

11. The default data type must match the data type of the column
13. Tables in the Oracle database
15. User Table

    Collection of a series of tables created and maintained by the user
    Contains information about the user
    
    Data dictionary
    Collection of a series of tables created and maintained by the Oracle server
    Contains information about the database

16. Data type
    

    The data type that represents the character: CHAR (n), VARCHA2 (n), Lang,clob

    A data type that represents a number: numbers (p,s): P is precision, S is scale

    Represents the binary data type: raw or long raw, BLOB, BFILE

    Data type that represents the date: date, TIMESTAMP, interval: year to month, day to second

    Sql> CREATE TABLE t (da1 date,da2 timestamp)'  2SQL>SQL>SQL>CREATE TABLE t (da1 date,da2 timestamp); SQL>INSERT into T values (sysdate,sysdate);1line. SQL>Commit , Commit, complete. SQL> Col da2 forA45sql>Select* fromt;da1 DA2-------------- --------------------------------------------- --August- -      --August- - 09.46.51.000000Morning

17. INTERVAL year to month data type is stored for a period of time using the yearly and monthly date fields

    INTERVAL Day to SECOND data types use days, hours, minutes, seconds to store a period of time

    Sql>drop table e purge; table is deleted. SQL> CREATE TABLE E as Select* fromemp; The table is created. SQL>ALTER TABLE E add JG interval year to month; SQL>whether the desc e name is empty?type----------------------------------------- -------- ----------------------------EMPNO Number (4) ename VARCHAR2 (Ten) JOB VARCHAR2 (9) MGR Number (4) HireDate DATE SAL Number ( c0>7,2) COMM Number (7,2) DEPTNO Number (2) JG INTERVAL year (2) to Monthsql>SelectEmpno,ename,hiredate,hiredate+jg frome; EMPNO ename hiredate HireDate+JG----- ------ -------------- --------------7369SMITH --December- the      --May- the 7499ALLEN --February-Bayi      --July- the 7521WARD A-February-Bayi      A-July- the 7566JONES Geneva-April-Bayi      Geneva-September- the 7654MARTIN --September-Bayi      --February- the 7698BLAKE on-May-Bayi      on-October- the 7782CLARK the-June-Bayi      the-November- the 7788SCOTT +-April- the      +-September- the 7839KING --November-Bayi      --April- the 7844TURNER ,-September-Bayi      ,-February- the 7876Adams at-May- the      at-October- the 7900JAMESGeneva-December-Bayi     Geneva-May- the 7902FORDGeneva-December-Bayi     Geneva-May- the 7934MILLER at-January- the      at-June- the

19. Constraints

    A constraint is a rule that is imposed on a column in a table

    Five types of constraints are available in Oracle:

    Not NULL
    UNIQUE
    PRIMARY KEY
    FOREIGN KEY
    CHECK

    
    Constraint rules:

    When you create a constraint, you can name the constraint, or you can use the format of the SYS_CN to customize the naming

    You can create constraints at the following times:

    Create a constraint at the same time as the table is created
    Create a constraint after a table is created

    Create a constraint at the table or column level

    View constraints in a data dictionary

    
    To define a constraint:

    Column level: