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' ;
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));
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 .
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 .
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');
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 .
“ 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 .
34 views today, 1 views in last 3 hour