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 .

  • Provides quick processing of data and 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 .

  • We cannot abbreviate keywords or divide them 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 have seen 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 a table or column 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 placed for one or more columns .

  • A table can have more than one unique constraint .

 

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 .