Oracle SQL Tutorial -1 Introduction To Oracle SQL

Hi,

What is sql in this article? , What are the sql rules of writing? We will find answers to such questions .

WHAT IS SQL (STRUCTURED QUERY LANGUAGE) ?

SQL is not a programming language like C, JAVA, C ++ . ORACLE is a sub language used in databases such as MYSQL , SQL SERVER . SQL is a query language used to add , delete , access and update data in a database .

USAGE AREAS OF SQL

• Creating , modifying database objects ,

• Controlling access to database, objects ,

• Modeling , managing data sets ,

• Data query , add , delete operations are performed .

WHY CHOOSE SQL?

• Allows users to define and modify data .

• Allows users to set permissions on tables , views .

• Allows users to access data from relational database management systems .

• Allows modules to be embedded in different languages .

• Security, performance , data recovery features are the main features that are effective in choosing sql .

• Provides quick processing of data and easy querying .

CLASSIFICATION OF SQL COMMANDS

SQL commands are divided into 3 sections according to their functions :

• DML (Data Manipulation Language) : Data processing language . For managing data with in table or schema objects (SELECT , UPDATE , INSERT , MERGE , DELETE , CALL)

• DCL (Data Control Language) : Data control language . Used to set authorization or privileges . (GRANT,REVOKE)

• DDL (Data Definition Language) : Data definition language . Used to define tables, database structures, or schemas . (CREATE , TRUNCATE , ALTER , COMMENT , DROP , RENAME)

• TCL (Transaction Control) : The process is the control language . For managing changes made by DML . (COMMIT , SAVEPOINT , ROLLBACK)

SQL WRITING RULES

• SQL statements do not distinguish between upper and lower case .

• Keywords cannot be abbreviated or divided into lines (keywords : Select , From , etc.) .

• Expressions are usually in separate lines .

• Typically , key expressions are written in capital letters .

• Date and character type data is written to the left and numeric data is written adjacent to the right .

CREATING A SQL TABLE

CREATE TABLE [schema] table

 (column datatype [DEFAULT expr] [ , .....] );

CREATE TABLE ADMIN.ISCI(

EMPLOYEE_ID NUMBER(6),

FIRST_NAME VARCHAR2(20 BYTE),

LAST_NAME VARCHAR2(25 BYTE),

HIRE_DATE DATE,

JOB_ID VARCHAR2(10 BYTE),

SALARY NUMBER(8,2)

)

In the picture you see above , I added my own worker table and my columns to this table with this query .

ADDING COMMENT TO TABLE

• We can add comments to tables or columns separately .

• Comments can be queried from the USER_TAB_COMMENTS , USER_COL_COMMENTS views .

COMMENT ON TABLE ADMIN.ISCI 'ADMIN SEMASINDAKI ISCILER' ;

CONSTRAINTS

• They prevent deletion of interdependent data .

• They prevent incorrect data entry .

• They ensure that the data is singular .

• They can be at the table or column level .

NOT NULL CONSTRAINT

• Prevents specified columns from being NULL .

CREATE TABLE ADMIN.ISCIS(

EMPLOYEE_ID NUMBER(6),

FIRST_NAME VARCHAR2(20 BYTE)NOT NULL,

LAST_NAME VARCHAR2(25 BYTE),

HIRE_DATE DATE,

JOB_ID VARCHAR2(10 BYTE),

SALARY NUMBER(8,2));

UNIQUE CONSTRAINT

• They can be used for one or more columns .

• You can use more than one unique constraint in a table .

CREATE TABLE ADMIN.ISCIM(

EMPLOYEE_ID NUMBER(6)UNIQUE,

FIRST_NAME VARCHAR2(20 BYTE),

LAST_NAME VARCHAR2(25 BYTE)

);

PRIMARY KEY CONSTRAINT

• A primary key can be set for a column .

• There can only be one primary key in the table .

• The value in this field cannot be NULL .

• It also serves as an index .

CREATE TABLE ADMIN.ISCIZ(

EMPLOYEE_ID NUMBER(6)CONSTRAINT E_PK PRIMARY KEY,

FIRST_NAME VARCHAR2(20 BYTE),

LAST_NAME  VARCHAR2(25 BYTE)

);

FOREIGN KEY CONSTRAINT

• Specifies that the data in one column of a table must come from the column of another table .

CHECK CONSTRAINT

• Defines the rules that must be applied to each row .

• NEXTVAL , LEVEL , and ROWNUM aliases are not used .

• SYSDATE , USER , UID functions cannot be called .

• Queries that reference other values in other rows are not made .

CREATE TABLE ADMIN.ISC(

EMPLOYEE_ID NUMBER (6),

FIRST_NAME  VARCHAR2(20 BYTE),

LAST_NAME  VARCHAR2(25 BYTE),

SALARY NUMBER(8,2)CONSTARINT  S_MIN  CHECK(SALARY>1000)
);

CREATING TABLES WITH SUB QUESTIONS

• Data from the subquery is automatically passed when the table is created .

• All column names must be given from the subquery .

ALTER TABLE

• Changing the column name ,

ALTER TABLE ADMIN.ISC RENAME COLUMN EMPLOYEE_ID TO EMP_ID;

• Changing the name of the table ,

ALTER TABLE ADMIN.ISC RENAME TO ISCILER;

• Add primary key to the table ,

ALTER TABLE ADMIN.ISCILER ADD (CONSTRAINT EMP_EMP_ID_PK PRIMARY KEY (EMP_ID));

• To put the table in read-only mode ,

ALTER TABLE ADMIN.ISCILER READ ONLY;

• Enables adding partitions to the table .

ALTER TABLE ADMIN.ISCILER ADD PARTITION PART VALUES('G3_201407_07');

TABLE CONSTRAINT

When Constraint DISABLE , all contraint controls on the table are removed . When ENABLE , controls are restored .

ALTER TABLE ADMIN.ISCILER ENABLE CONSTRAINT DEPT_LOC_FK;

ALTER TABLE ADMIN.ISCILER DISABLE CONSTRAINT DEPT_LOC_FK;

ALTER TABLE – SET UNUSED

• If there are unused columns on the table , it can be marked as UNUSED instead of DROP .

• They are not physically removed from the table . In SQL , they don’t appear in Views .

• We can give these column names to a new column .

• Usage :

“ ALTER TABLE <TABLE_NAME> SET UNUSED (<COLUMN_NAME>);

 ALTER TABLE <TABLE_NAME> DROP UNUSED COLUMNS;

 USER_UNUSED_COL_TABS

 ALL_UNUSED_COL_TABS

 DBA_UNUSED_COL_TABS ”

See you in my next post .